<?php
include("db.php");
include('../pdate.php');
$today=pdate("Y-m-d");
require_once 'excel_library/PHPExcel/IOFactory.php';

 
  $objPHPExcel = PHPExcel_IOFactory::load("excel_templates/student_template.xlsx");
  
  $objPHPExcel -> setActiveSheetIndex(0)
			 
			
			-> setCellValue("D4",$today)
			-> setCellValue("F6","نام مشتری")
			-> setCellValue("F6","الباقی دالر")
			-> setCellValue("E6","الباقی افغانی")
			-> setCellValue("D6","الباقی تومان")
			-> setCellValue("C6","الباقی کالدار")
			-> setCellValue("B6","الباقی یورو") 
			-> setCellValue("A6","مجموع به دالر");
			  
$k = 7; 
$j = 7; 
$i = 7; 
$h = 7; 
$g = 7; 
$f = 7;
$e = 7;					 
$d = 7;
$c = 7; 
$b = 7;
$a = 7;   			

    $findingCustomer_sql="select distinct  customerId  from customerbalance  ";
	$findingCustomer_Query= $db ->query($findingCustomer_sql);
    $ii=0;
   	while($findingCustomer_Row=$findingCustomer_Query ->fetch_array()){
		
		    $findingcustomer_id=$findingCustomer_Row['customerId'];
		   //===================customerName========================
		    $customerName_sql="select name from customers where id='$findingcustomer_id'";
		    $customerName_query= $db ->query($customerName_sql);
			$customerName_Row=$customerName_query ->fetch_array();
			$customerName=$customerName_Row['name'];
		   
		   //===================customerName========================
		   
		   //=======================دالر
		   $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_sql="select * from crrencyrate  where counterCurrency='2' and baseCurrency='2'  and date='$today' ORDER BY id desc limit 1  ";
		   $select_Currency_dollar_query= $db ->query($select_Currency_dollar_sql);
		   $select_Currency_dollar_row=$select_Currency_dollar_query ->fetch_array();
		   $select_Currency_dollar=$select_Currency_dollar_row['rate'];
		   $select_Currency_dollar=1;
		   
 
		   //Change to dollar
		   $dollarDollar=$findingCurrency/$select_Currency_dollar;
		    if($findingCurrency==""){
			  $findingCurrency="0.00"; 
		   }
		   //==========================================انتهای دالر
		   //==========================================افغانی
		   $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'  and date='$today' 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"; 
		   }
		   //============================================انتهای افغانی
		   //============================================تومان
		   $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'  and date='$today' 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'];
		   
		   if(!isset($select_Currency_toman)){
		   
		       echo "<span style='color: red'>لطفا قیمت تومان به دالر را مشخص کنید</span>";
			   die;
		   }			   
		   $dollarToman=$findingCurrency_3/$select_Currency_toman;
		    if($findingCurrency_3==""){
			  $findingCurrency_3="0.00"; 
		   }
		   //================================================انتهای تومان
		   //================================================ کالدار
		   $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'  and date='$today' 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"; 
		   }
		   //=========================================================انتهای کالدار
		   //=========================================================یورو
		   $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'  and date='$today'  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=number_format(($dollarEuro+ $dollarCaldar+$dollarToman+$dollarAfghani+$dollarDollar),2);
		   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){
			 
			$ii++;  
					$H  = 'H'.$h;
				    $h++;
					$G  = 'G'.$g;
				    $g++;
					$F  = 'F'.$f;
					$f++;
					$E  = 'E'.$e;
					$e++;
					$D  = 'D'.$d;
				    $d++;
					$C  = 'C'.$c;
					$c++;
					$B  = 'B'.$b;
					$b++; 
					$A  = 'A'.$a;
					$a++; 		

					
				    $objPHPExcel-> setActiveSheetIndex(0)
    				 
    				-> setCellValue($H,$ii)
    				-> setCellValue($G,$customerName)
    				-> setCellValue($F,$findingCurrency)
    				-> setCellValue($E,$findingCurrency_2)
    				-> setCellValue($D,$findingCurrency_3)
    				-> setCellValue($C,$findingCurrency_4)
    				-> setCellValue($B,$findingCurrency_5) 
    				-> setCellValue($A,$totalChangeDollar);
    				 
    	$i++;
    }
	 
	}  

  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');						
ob_end_clean(); // Added by me
ob_start(); // Added by me

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="حسابات مشتریان-'.pdate("Y-m-d").'.xlsx"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output'); 
	 
?>


 