<?php

require_once('Spreadsheet/Excel/Writer.php');

 
  
  

	

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}



include("config.inc.php");



$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass'])
		or die("Could not connect to DB");

mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

//maroun 30.03.2011
$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')	";
	$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
	while($rows=mysql_fetch_array($resultlog)){
		extract($rows);
	}



						
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Company_Name As CompanyName, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}
	//User Access
$querylog=" select ifNull(login_belongstogrp,0) As FI_BelongsToGrp from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);



//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
 
//Sending HTTP headers
$workbook->send('Exp_ListOfPurchasesJV.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Export');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;


//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularFormat =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
// Regular Format
$squareborderFormat =& $workbook->addFormat(array('size'=>9,
  'top'=>1,  
  'bottom'=>1,
  'right'=>1,
  'left'=>1,
  ));  
  
$lrBorderFormat =& $workbook->addFormat(array('size'=>9,
  'right'=>1,
  'left'=>1,
  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'bold'=>1
  ));  

			$Title='List Of Purchases JV';			
                $worksheet->write(1,0,$CompanyName, $TitleFormat);
				$worksheet->write(1,5,"$Title", $TitleFormat);
                $worksheet->write(1,11,"Printed Date:", $boldFormat);
                $worksheet->write(1,13,$printdate[2]."/".$printdate[1]."/".$printdate[0], $boldFormat);
				$worksheet->write(2,11,"Time:", $boldFormat);		
				$worksheet->write(2,13,$dt[1]." ".$dt[2], $boldFormat);  
	


    $dispfromdate = $fromdate;
	$disptodate = $todate;
	$dispfromcostcent = $fromcostcent;
	$dispfromproject = $fromproject;
	
	if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
	if ($fromclient=='')
		$fromclient = 'null';
	if ($toclient=='')
		$toclient = 'null';
	
		
	if (!empty($fromdate))
	   $fromdate = explodeword($fromdate);
	if (!empty($todate))
	   $todate = explodeword($todate);  

	if (empty($fromdate))   
	   $fromdate = 'null';
	else
	   $fromdate = "'".$fromdate."'"; 
	   
	if (empty($todate))   
	   $todate = 'null';
	else
	   $todate = "'".$todate."'";	
	   
	
	if (empty($fromproject))   
	   $fromproject = 'null';
	else
	   $fromproject = "'".$fromproject."'";    
	   
    if (empty($fromcostcent))   
	   $fromcostcent = 'null';
	else
	   $fromcostcent = "'".$fromcostcent."'";   

	   
	   
			
	$query = " select Date_Format(PurchaseJV_Date,'%d/%m/%Y'), PurchaseJV_Reference, ".
             " Journal_refgovernment, PurchasesJV.Supplier_Code, Suppliers.Ledger_name, PurchasesJV.Currency_Code, ".
			 " round(PurchaseJV_VAT ,".$DecimalBase."), round(PurchaseJV_VATBase1 ,".$DecimalBase1."), round(PurchaseJV_VATBase2 ,".$DecimalBase2."),".
			 " round(PurchaseJV_Total,".$DecimalBase."),round(PurchaseJV_TotalBase1,".$DecimalBase1."),round(PurchaseJV_TotalBase2,".$DecimalBase2."), PurchaseJV_Id ".
			 " from PurchasesJV ".
			 " inner join suppliers on suppliers.Ledger_number= PurchasesJV.Supplier_Code ".
			 " left join journal on journal.Journal_id=PurchasesJV.Journal_Id ".
			 " where ( ($option = 2 and  PurchaseJV_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999)) or ".
			 " ($option = 1 and date_format(journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(journal.Journal_date,'%Y-%m-%d')) ))".
			 " and ( PurchasesJV.Supplier_Code between ifnull(".$fromclient.",'0') and ifnull(".$toclient.",'9999999999999'))".
			 //" and (($varcurrency = 2 and PurchasesJV.Currency_Code= '".$base1."') or ($varcurrency = 3 and PurchasesJV.Currency_Code = '".$base2."') or ($varcurrency = 1 and 1=1)) ".
			 " and upper(ifNull(PurchasesJV.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(PurchasesJV.project_code,'-1'))) ".
			 " and upper(ifNull(PurchasesJV.Costcenter_Code,'-1')) like ifNull(upper(".$fromcostcent."), upper(ifNull(PurchasesJV.Costcenter_Code,'-1')))   ".
			 " order by if($option=2, PurchaseJV_Reference, Null), if($option=1, PurchaseJV_Date, Null )";
		

//echo $query;



	$worksheet->write(4,0,"Date", $boldFormat); 
	$worksheet->write(4,1,"From", $boldFormat); 
    $worksheet->write(4,2,"$dispfromdate", $boldFormat); 	
	$worksheet->write(4,3,"To", $boldFormat);
	$worksheet->write(4,4,"$disptodate", $boldFormat);
    $worksheet->write(4,5,"CostCenter", $boldFormat); 
	$worksheet->write(4,6,"$dispfromcostcen", $boldFormat); 
	$worksheet->write(4,7,"Project", $boldFormat); 
	$worksheet->write(4,8,"$dispfromproject", $boldFormat); 
	
		
	
	$worksheet->write(6,0,"Date", $boldFormat); 		
	$worksheet->write(6,1,"Ref.", $boldFormat);
    $worksheet->write(6,2,"JV Ref.", $boldFormat); 
	$worksheet->write(6,3,"Ledger No.", $boldFormat); 
	$worksheet->write(6,4,"Name", $boldFormat); 
	$worksheet->write(6,5,"Cur.", $boldFormat); 
	$worksheet->write(6,6,"VAT", $boldFormat); 
	$worksheet->write(6,7,"Amount", $boldFormat); 
		
		

	if ($varDetail==1) {
		
	$worksheet->write(7,0,"Ledger No.", $boldFormat);
    $worksheet->write(7,1,"Name", $boldFormat); 
	$worksheet->write(7,2,"Description", $boldFormat); 
	$worksheet->write(7,3,"Qty", $boldFormat); 
	$worksheet->write(7,4,"Price", $boldFormat); 
	$worksheet->write(7,5,"Total", $boldFormat); 

		
		}
	$rownum	=7;
$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
		
	
	$rownum = $rownum + 1;
	
	$worksheet->write($rownum,0,$rows[0], $regularFormat); 
	$worksheet->write($rownum,1,$rows[1], $regularFormat); 
	$worksheet->write($rownum,2,$rows[2], $regularFormat); 
	$worksheet->write($rownum,3,$rows[3], $regularFormat); 
	$worksheet->write($rownum,4,$rows[4], $regularFormat); 
	
	

  if ($varcurrency == 1){
  $worksheet->write($rownum,5,$rows[5], $regularFormat); 
  $worksheet->write($rownum,6,$rows[6], $regularFormat); 
  $worksheet->write($rownum,7,$rows[9], $regularFormat); 

	}
	  if ($varcurrency == 2){
  $worksheet->write($rownum,5,$base1, $regularFormat); 
  $worksheet->write($rownum,6,$rows[7], $regularFormat); 
  $worksheet->write($rownum,7,$rows[10], $regularFormat);
	
	}	

	  if ($varcurrency == 3){
   $worksheet->write($rownum,5,$base2, $regularFormat); 
   $worksheet->write($rownum,6,$rows[8], $regularFormat); 
   $worksheet->write($rownum,7,$rows[11], $regularFormat);

	}	 

	
	if ($varDetail==1) {
		
		 $queryDetail= " select PurchasesJVdt.Ledger_Number, Ledger.Ledger_Name, PurchaseJVDt_Description, purchasejvdt_quantity, ".
			   " round(purchasejvdt_price,".$DecimalBase."), purchasejvdt_discper, ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_Total,  ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_TotalBase1,  ".
			   " (ifNull(purchasejvdt_quantity,0) * ifNull(purchasejvdt_price,0) * (1 - ifNull(purchasejvdt_discper,0))) As PurchaseJVDt_TotalBase2  ".
			   " from PurchasesJVdt Inner Join Ledger on Ledger.Ledger_Number = PurchasesJVdt.Ledger_Number ".
			   " where PurchasesJVdt.PurchaseJV_Id = '".$rows[12]."'";

		//echo $queryDetail;              
		$resultDetail=mysql_query($queryDetail,$connection) or die(mysql_error());
		while($rowsDetail=mysql_fetch_row($resultDetail)){
			
			$rownum = $rownum + 1;
			
    $worksheet->write($rownum,0,$rowsDetail[0], $regularFormat); 
	$worksheet->write($rownum,1,$rowsDetail[1], $regularFormat); 
	$worksheet->write($rownum,2,$rowsDetail[2], $regularFormat); 
	$worksheet->write($rownum,3,$rowsDetail[3], $regularFormat); 
	
	
	
			if ($varcurrency==1){
			$worksheet->write($rownum,4,$rowsDetail[4], $regularFormat); 
			$worksheet->write($rownum,5,$rowsDetail[6], $regularFormat); 
			}
			
			if ($varcurrency==2){
			$worksheet->write($rownum,4,$rowsDetail[9], $regularFormat); 
			$worksheet->write($rownum,5,$rowsDetail[7], $regularFormat); 
			}
			
			if ($varcurrency==3){
			$worksheet->write($rownum,4,$rowsDetail[10], $regularFormat); 
			$worksheet->write($rownum,5,$rowsDetail[8], $regularFormat); 
			}
			
			$rownum = $rownum + 1;
	
		}
	}
 
 }
  
 $queryTotal = "select sum(PurchaseJV_Total), sum(PurchaseJV_TotalBase1), sum(PurchaseJV_TotalBase2),sum(PurchaseJV_Vat), sum(PurchaseJV_VatBase1), sum(PurchaseJV_VatBase2), PurchasesJV.Currency_Code ".
			 " from PurchasesJV ".
			 " inner join suppliers on suppliers.Ledger_number= PurchasesJV.Supplier_Code ".
			 " left join journal on journal.Journal_id=PurchasesJV.Journal_Id ".
			 " where ( ($option = 2 and  PurchaseJV_Reference between ifnull($fromref,0) and ifnull($toref,9999999999999)) or ".
			 " ($option = 1 and date_format(journal.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(journal.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(journal.Journal_date,'%Y-%m-%d')) ))".
			 " and ( PurchasesJV.Supplier_Code between ifnull(".$fromclient.",'0') and ifnull(".$toclient.",'9999999999999'))".
			 //" and (($varcurrency = 2 and PurchasesJV.Currency_Code= '".$base1."') or ($varcurrency = 3 and PurchasesJV.Currency_Code = '".$base2."') or ($varcurrency = 1 and 1=1)) ".
			 " and upper(ifNull(PurchasesJV.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(PurchasesJV.project_code,'-1'))) ".
			 " and upper(ifNull(PurchasesJV.Costcenter_Code,'-1')) like ifNull(upper(".$fromcostcent."), upper(ifNull(PurchasesJV.Costcenter_Code,'-1')))   ".
			 " group by if($varcurrency=1, PurchasesJV.Currency_Code, Null ) ";


			// echo $queryTotal;
			
if ($varcurrency == 1){
$rownum = $rownum + 2;
$worksheet->write($rownum,0,"Summary", $boldFormat);
}

$resultTotal=mysql_query($queryTotal,$connection) or die(mysql_error());
while($rowsTotal=mysql_fetch_row($resultTotal)){

 $rownum = $rownum + 1;

 if ($varcurrency == 1){

  $worksheet->write($rownum,0,"Total $rowsTotal[6]", $boldFormat);
  $worksheet->write($rownum,1,"$rowsTotal[3]", $boldFormat);
  $worksheet->write($rownum,2," $rowsTotal[0]", $boldFormat);

	
}
  
 if ($varcurrency == 2){
   $worksheet->write($rownum,5,"Total $base1", $boldFormat);
   $worksheet->write($rownum,6,"$rowsTotal[4]", $boldFormat);
   $worksheet->write($rownum,7,"$rowsTotal[1]", $boldFormat);
 
}
 if ($varcurrency == 3){
   $worksheet->write($rownum,5,"Total $base2", $boldFormat);
   $worksheet->write($rownum,6,"$rowsTotal[5]", $boldFormat);
   $worksheet->write($rownum,7,"$rowsTotal[2]", $boldFormat);
 
}
 
}
 
function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

	$trim = explode('/',$date);
	$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
	return $trim;
}
$workbook->close(); 

?>