<?php
   $start_Date="1397-01-01";
   $endDate="1400-09-30";
include("db.php");

           $findingCustomer_sql="select * from  customers ";
	       $findingCustomer_Query= $db ->query($findingCustomer_sql);
		   $ii=$countId;
   		  while($findingCustomer_Row=$findingCustomer_Query ->fetch_array()){
             $ii++;			  
		   $customerid=$findingCustomer_Row['id'];
		   $customerName=$findingCustomer_Row['name'];
		   
		    
		   
		   //===========usd====================================
			$cr_customerAmount_usd_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='1' and (date between '$start_Date' and '$endDate') and currency_id='2' ";
			$cr_customerAmount_usd_query= $db ->query($cr_customerAmount_usd_sql);
            $cr_customerAmount_usd_row=$cr_customerAmount_usd_query ->fetch_array();
            $cr_customerAmount_usd=$cr_customerAmount_usd_row['amount'];
                             
			$dr_customerAmount_usd_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='2' and (date between '$start_Date' and '$endDate') and currency_id='2' ";
		    $dr_customerAmount_usd_query= $db ->query($dr_customerAmount_usd_sql);
            $dr_customerAmount_usd_row=$dr_customerAmount_usd_query ->fetch_array();
            $dr_customerAmount_usd=$dr_customerAmount_usd_row['amount'];

            $findingCurrency=$cr_customerAmount_usd-$dr_customerAmount_usd;
                            			
           //=============usd=======================================
		  /*  $first_findingCurrency_sql="select * from customerbalance where currencyId='2' and customerId='$findingcustomer_id' and amount!='0'";
	       $first_findingCurrency_Query= $db ->query($first_findingCurrency_sql);
		   $first_findingCurrency_Row=$first_findingCurrency_Query ->fetch_array(); 
		   $findingCurrency=$first_findingCurrency_Row['amount'];
		   $customerId=$first_findingCurrency_Row['customerId']; */
	       
		   $select_Currency_dollar=1;
		   //Change to dollar
		   $dollarDollar=$findingCurrency/$select_Currency_dollar;
		   
		   //==========================================??????
		   //===========afn====================================
			$cr_customerAmount_afn_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='1' and (date between '$start_Date' and '$endDate') and currency_id='1' ";
			$cr_customerAmount_afn_query= $db ->query($cr_customerAmount_afn_sql);
            $cr_customerAmount_afn_row=$cr_customerAmount_afn_query ->fetch_array();
            $cr_customerAmount_afn=$cr_customerAmount_afn_row['amount'];
                            
			$dr_customerAmount_afn_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='2' and (date between '$start_Date' and '$endDate') and currency_id='1' ";
			$dr_customerAmount_afn_query= $db ->query($dr_customerAmount_afn_sql);
            $dr_customerAmount_afn_row=$dr_customerAmount_afn_query ->fetch_array();
            $dr_customerAmount_afn=$dr_customerAmount_afn_row['amount'];

           $findingCurrency_2=$cr_customerAmount_afn-$dr_customerAmount_afn;
                             							
            //=============afn=======================================
		  /*  
		   $first_findingCurrency_sql_2="select * from customerbalance where currencyId='1' and customerId='$findingcustomer_id' and amount!='0'";
	       $first_findingCurrency_Query_2= $db ->query($first_findingCurrency_sql_2);
		   $first_findingCurrency_Row_2=$first_findingCurrency_Query_2 ->fetch_array(); 
		   $findingCurrency_2=$first_findingCurrency_Row_2['amount'];  */
		   
		   $select_Currency_afghani_sql="select * from crrencyrate  where counterCurrency='2' and baseCurrency='1' order by id desc limit 1 ";
		   $select_Currency_afghani_query= $db ->query($select_Currency_afghani_sql);
		   $select_Currency_afghani_row=$select_Currency_afghani_query ->fetch_array();
		   $select_Currency_afghani=$select_Currency_afghani_row['rate'];
		   
		 
		   //Change to dollar
		   $dollarAfghani=$findingCurrency_2/$select_Currency_afghani;
		   
			if($findingCurrency_2==""){
			  $findingCurrency_2="0.00"; 
		   }
		   //============================================?????? ??????
		   //============================================?????
		   //===========IRR====================================
			$cr_customerAmount_irr_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='1' and (date between '$start_Date' and '$endDate') and currency_id='5' ";
			$cr_customerAmount_irr_query= $db ->query($cr_customerAmount_irr_sql);
            $cr_customerAmount_irr_row=$cr_customerAmount_irr_query ->fetch_array();
            $cr_customerAmount_irr=$cr_customerAmount_irr_row['amount'];
                            
			$dr_customerAmount_irr_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='2' and (date between '$start_Date' and '$endDate') and currency_id='5' ";
			$dr_customerAmount_irr_query= $db ->query($dr_customerAmount_irr_sql);
            $dr_customerAmount_irr_row=$dr_customerAmount_irr_query ->fetch_array();
            $dr_customerAmount_irr=$dr_customerAmount_irr_row['amount'];

            $findingCurrency_3=$cr_customerAmount_irr-$dr_customerAmount_irr;	
           //=============irr=======================================
		   /* $first_findingCurrency_sql_3="select * from customerbalance where currencyId='5' and customerId='$findingcustomer_id' and amount!='0'";
	       $first_findingCurrency_Query_3= $db ->query($first_findingCurrency_sql_3);
		   $first_findingCurrency_Row_3=$first_findingCurrency_Query_3 ->fetch_array(); 
		   $findingCurrency_3=$first_findingCurrency_Row_3['amount'];  */
		   
		   $select_Currency_toman_sql="select * from crrencyrate  where counterCurrency='2' and baseCurrency='5'  order by id desc limit 1  ";
		   $select_Currency_toaman_query= $db ->query($select_Currency_toman_sql);
		   $select_Currency_toman_row=$select_Currency_toaman_query ->fetch_array();
		   $select_Currency_toman=$select_Currency_toman_row['rate'];
		   
		   
		   $dollarToman=$findingCurrency_3/$select_Currency_toman;
		    
		    if($findingCurrency_3==""){
			  $findingCurrency_3="0.00"; 
		   }
		   //================================================?????? ?????
		   //================================================ ??????
		   //===========pkk====================================
	        $cr_customerAmount_pk_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='1' and (date between '$start_Date' and '$endDate') and currency_id='4' ";
			$cr_customerAmount_pk_query= $db ->query($cr_customerAmount_pk_sql);
            $cr_customerAmount_pk_row=$cr_customerAmount_pk_query ->fetch_array();
            $cr_customerAmount_pk=$cr_customerAmount_pk_row['amount'];
                            
			$dr_customerAmount_pk_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='2' and (date between '$start_Date' and '$endDate') and currency_id='4' ";
			$dr_customerAmount_pk_query= $db ->query($dr_customerAmount_pk_sql);
            $dr_customerAmount_pk_row=$dr_customerAmount_pk_query ->fetch_array();
            $dr_customerAmount_pk=$dr_customerAmount_pk_row['amount'];

            $findingCurrency_4=$cr_customerAmount_pk-$dr_customerAmount_pk;	
            //=============pkk=======================================
		   
		   /* $first_findingCurrency_sql_4="select * from customerbalance where currencyId='4' and customerId='$findingcustomer_id' and amount!='0'";
	       $first_findingCurrency_Query_4= $db ->query($first_findingCurrency_sql_4);
		   $first_findingCurrency_Row_4=$first_findingCurrency_Query_4 ->fetch_array(); 
		   $findingCurrency_4=$first_findingCurrency_Row_4['amount']; */
		   
		   $select_Currency_caldar_sql="select * from crrencyrate  where counterCurrency='2' and baseCurrency='4'  order by id desc limit 1 ";
		   $select_Currency_caldar_query= $db ->query($select_Currency_caldar_sql);
		   $select_Currency_caldar_row=$select_Currency_caldar_query ->fetch_array();
		   $select_Currency_caldar=$select_Currency_caldar_row['rate'];
		   
		    
		    //Change to dollar
		     $dollarCaldar=$findingCurrency_4/$select_Currency_caldar;
		    
		   if($findingCurrency_4==""){
			  $findingCurrency_4="0.00"; 
		   }
		   //=========================================================?????? ??????
		   //=========================================================????
		   //===========Euro====================================
		   $cr_customerAmount_euro_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='1' and (date between '$start_Date' and '$endDate') and currency_id='3' ";
		   $cr_customerAmount_euro_query= $db ->query($cr_customerAmount_euro_sql);
           $cr_customerAmount_euro_row=$cr_customerAmount_euro_query ->fetch_array();
           $cr_customerAmount_euro=$cr_customerAmount_euro_row['amount'];
                            
		   $dr_customerAmount_euro_sql="select sum(amount) as amount from customer_statement where customerId='$customerid' and transaction_type='2' and (date between '$start_Date' and '$endDate') and currency_id='3' ";
		   $dr_customerAmount_euro_query= $db ->query($dr_customerAmount_euro_sql);
           $dr_customerAmount_euro_row=$dr_customerAmount_euro_query ->fetch_array();
           $dr_customerAmount_euro=$dr_customerAmount_euro_row['amount'];

           $findingCurrency_5=$cr_customerAmount_euro-$dr_customerAmount_euro;	
           //=============Euro=======================================
		   /* $first_findingCurrency_sql_5="select * from customerbalance where currencyId='3' and customerId='$findingcustomer_id' and amount!='0'";
	       $first_findingCurrency_Query_5= $db ->query($first_findingCurrency_sql_5);
		   $first_findingCurrency_Row_5=$first_findingCurrency_Query_5 ->fetch_array(); 
		   $findingCurrency_5=$first_findingCurrency_Row_5['amount']; */
		    
		   $select_Currency_euro_sql="select * from crrencyrate  where counterCurrency='3' and baseCurrency='2'  order by id desc limit 1  ";
		   $select_Currency_euro_query= $db ->query($select_Currency_euro_sql);
		   $select_Currency_euro_row=$select_Currency_euro_query ->fetch_array();
		   $select_Currency_euro=$select_Currency_euro_row['rate'];
		   //Change to dollar
		    $dollarEuro=$findingCurrency_5*$select_Currency_euro;
			
		    $totalChangeDollar=($dollarEuro+ $dollarCaldar+$dollarToman+$dollarAfghani+$dollarDollar);
			if($totalChangeDollar<0){
			   $sum_dollarManfi+=$totalChangeDollar;
			}
			if($totalChangeDollar>0){
			   $sum_dollarMosbat+=$totalChangeDollar;
			}
			
		    
			//echo "<br>==========================</br>"; 
		   if($findingCurrency_5==""){
			  $findingCurrency_5="0.00"; 
		   }
		   //===================================================
		   if($findingCurrency_5!=0 or $findingCurrency_4!=0 or $findingCurrency_3!=0 or $findingCurrency_2!=0 or  $findingCurrency!=0){ 
			 $i++;  
			echo "<td style='width: 5px'>$ii</td> ";
		    echo "<td style='width: 200px'>$customerName</td> ";
		     
	       if($findingCurrency<0){ echo "<td style='font-size: 22px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=2&customerID=$customerid' target='_'><span style='color: red'>".number_format($findingCurrency)."</span></a></td> ";} 
			else{  echo "<td style='font-size: 22px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=2&customerID=$customerid' target='_'>".number_format($findingCurrency)."</a></td> ";}
			
		   if($findingCurrency_2<0){ echo "<td style='font-size: 22px;color: red;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=1&customerID=$customerid' target='_'><span style='color: red'>".number_format($findingCurrency_2)."</span></a></td> ";} 
			else{  echo "<td style='font-size: 22px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=1&customerID=$customerid' target='_'>".number_format($findingCurrency_2)."</a></td> ";}
			
			
	        if($findingCurrency_3<0){ echo "<td style='font-size: 22px;color: red;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=5&customerID=$customerid' target='_'><span style='color: red'>".number_format($findingCurrency_3)."</span></a></td> ";}
	        else{ echo "<td style='font-size: 22px ;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=5&customerID=$customerid' target='_'>".number_format($findingCurrency_3)."</a></td> ";}
			
	        if($findingCurrency_4<0){echo "<td style='font-size: 22px;color: red;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=4&customerID=$customerid' target='_'><span style='color: red'>".number_format($findingCurrency_4)."</span></a></td>";}
	        else{echo "<td style='font-size: 22px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=4&customerID=$customerid' target='_'>".number_format($findingCurrency_4)."</a></td>";}
	       
		    if($findingCurrency_5<0){echo "<td style='font-size: 22px;color: red;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=3&customerID=$customerid' target='_'><span style='color: red;'>".number_format($findingCurrency_5)."</span></a></td>";}
		    else{echo "<td style='font-size: 22px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=3&customerID=$customerid' target='_'>".number_format($findingCurrency_5)."</a></td>";}
			
	        echo "<td style='background-color: #e6e6ff;width: 120px;text-align: right' dir='ltr'><a href='report/customer_statement.php?currency_id=3&customerID=$customerid' target='_'>".number_format($totalChangeDollar)."</td></tr>";
		   
		      
			   
		   }
		   
		   
		   }
		   
		   
		   ?>