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

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


include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

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

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist   = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);	


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

if ($option == 1)
	$ReportTitle = 'List of Confirmed Sales Order';
else if ($option == 2)
	$ReportTitle = 'List of Unconfirmed Sales Order';	
else if ($option == 3)
	$ReportTitle = 'List of Cancelled Sales Order';	
else if ($option == 4)
	$ReportTitle = 'List of Sales Order';	
	
	
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListofSalesOrder.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of SalesOrder');
$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
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  )); 
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormat1Num->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

if ($cur == 'currency1'){
   $cur='null';  
   $displaycur = '';
}   
else {
   $displaycur = $cur;
   $cur="'".$cur."'";
}

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromref != '')       $SelectedOptions .= "-> Reference From: ". $fromref . "  To: ". $toref ."\n";
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "\n";



if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate);  

$fromproject = "".$fromproject;

$fromref      = GetSQLValueString($fromref, 'text');
$toref        = GetSQLValueString($toref, 'text');
$fromproject  = GetSQLValueString($fromproject, 'text');
$fromclient   = GetSQLValueString($fromclient, 'text');
$toclient     = GetSQLValueString($toclient, 'text');
$fromdate	  = GetSQLValueString($fromdate, 'date');
$todate       = GetSQLValueString($todate, 'date');   

$sordertypelen = 0;
if (!empty($sordertype))
   $sordertypelen = strlen($sordertype);
   
if (empty($sordertype))
   $sordertype = 'null'; 



//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,7,"Printed Date:", $boldleftFormat);
$worksheet->write(1,8,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,7,"Time:", $boldleftFormat);		
$worksheet->write(2,8,$dt[1]." ".$dt[2], $regularleftFormat);

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;	   

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"D. Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Client", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Project", $boldcenterFormat);
$ColumnNum += 1;
if ($showProjCostName == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Project Name", $boldcenterFormat);
	$ColumnNum += 1;

}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Costcenter", $boldcenterFormat);
$ColumnNum += 1;
if ($showProjCostName == 1){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Costcenter Name", $boldcenterFormat);
	$ColumnNum += 1;

}

$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Net Total ".$displaycur, $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Status", $boldcenterFormat);
$ColumnNum += 1;

if ($showdetail == 1) {
	$RowNum += 1;
	$ColumnNum = 0;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
	$worksheet->write($RowNum,$ColumnNum,"Item Code/Description", $boldleftFormat);
	$ColumnNum += 4;
	$worksheet->write($RowNum,$ColumnNum,"Warehouse", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Qty.", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Unit Price", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Disc. %", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Total", $boldcenterFormat);
	$ColumnNum += 1;

}	
 
    

   
$querysalesorder= " select Sorder_id, date_format(Sorder_date,'%d/%m/%Y') Sorder_date, Sorder_reference, Journal_Type, ".
                  " sorder.Client_code, clients.Ledger_name , sorder.project_code, project_name, sorder.costcent_code, sorder.currency_code, ".
				  " ( ifnull(if(upper($cur)='".$Base1."',Sorder_TaxableBase1,if(upper($cur)='".$Base2."',Sorder_TaxableBase2,Sorder_Taxable)),0) + ".
  				  "   ifnull(if(upper($cur)='".$Base1."',Sorder_NoneTaxableBase1,if(upper($cur)='".$Base2."',Sorder_NoneTaxableBase2,Sorder_NoneTaxable)),0) + ".
 				  "   ifnull(if(upper($cur)='".$Base1."',Sorder_VATBase1,if(upper($cur)='".$Base2."',Sorder_VATBase2,Sorder_VAT)),0) - ".
				  "   ifnull(if(upper($cur)='".$Base1."',Sorder_DiscountBase1,if(upper($cur)='".$Base2."',Sorder_DiscountBase2,Sorder_DiscountAmount)),0)) net_total, ".
				  " if('".$date."' = 'date', date_format(Sorder_date,'%Y-%m-%d'), Sorder_reference) ord, ".
				  " if(ifNull(Sorder_disapproved,0)=1, 'Cancelled', if(ifNull(Sorder_confirmed,0)=1, 'Confirmed', 'Unconfirmed')) As Sorder_Status, ".
				  " sorder.Sorder_Remark, date_format(sorder_requestdate,'%d/%m/%Y') sorder_requestdate, Costcent_name ".
 				  " from sorder ".
				  " left join clients on  clients.ledger_number = sorder.Client_code ".
				  " left join project on project.project_code = sorder.project_code ".
				  " left join costcent on costcent.Costcent_code = sorder.Costcent_code ";
				
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $querysalesorder= $querysalesorder." left join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
								     "			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
									 "             and (project_code is not null and project_code != '') ".
								     "	         ) Q1 on sorder.project_code like concat(Q1.project_code,'%') ";
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) 
		$querysalesorder = $querysalesorder." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
									  "			     where loginstk.user_account='".$_SESSION['useraccount']."' ".
									  "              and (costcent_code is not null and costcent_code != '') ".
								  	  "			 ) Q2 on sorder.costcent_code like concat(Q2.Costcent_Code,'%')";
									  
$querysalesorder= $querysalesorder.  
				" where Sorder_reference between ifNull(". $fromref .",Sorder_reference) and ifNull(". $toref.",Sorder_reference) ".
				" and (($grpclient=1 and sorder.Client_code between ifNull(".$fromclient.",sorder.Client_code) and ifNull(". $toclient.",sorder.Client_code)) or ($grpclient=0 and 1 = 1)) ".
				//" and upper(sorder.currency_code) = ifNull(upper(".$cur."), upper(sorder.currency_code)) ".
				" and upper(ifNull(sorder.project_code,-1)) like Concat(ifNull(upper(".$fromproject."), upper(ifNull(sorder.project_code,-1))),'%') ".
				" and date_format(Sorder_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sorder_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sorder_date,'%Y-%m-%d'))  ".
				//" and sorder.journal_type='".$transaction."' ".
				" and sorder.journal_type='SO' ".
				" and (($sordertypelen = 0 and 1 = 1) or ($sordertypelen > 0 and sorder.sorder_type in ($sordertype)))  ".
				" and (($option = 1 and ifNull(Sorder_confirmed,0)=1 and ifNull(Sorder_disapproved,0)=0) or ($option = 2 and ifNull(Sorder_confirmed,0)=0 and ifNull(Sorder_disapproved,0)=0) or ".
				"      ($option = 3 and ifNull(Sorder_disapproved,0)=1) or ($option = 4 and 1=1) ) ";

$querysalesorder= $querysalesorder.  
				 " order by if($grpclient=1, sorder.Client_code, if($grpproject=1, sorder.project_code, if($grpstatus=1, Sorder_Status, null)) ), ord, Sorder_reference ";
				


				
$SumNetTot      = 0;
$SumGroupNetTot = 0;
$masterproject  = '';
$masterclient = '';
$masterstatus = '';
	
//echo $querysalesorder;

$RowNum    = 13;
$ColumnNum = 0;

$resultsalesorder=mysql_query($querysalesorder,$connection) or die(mysql_error());
while($rowsalesorder=mysql_fetch_row($resultsalesorder)){

    
	$querysalesorderdt= " select sorderdt.item_code, items.item_description, sorderdt.warehouse_code, SorderDt_Quantity, ".
				  	    " ifNull(if(upper($cur)='".$Base1."',SorderDt_PriceBase1,if(upper($cur)='".$Base2."',SroderDt_PriceBase2, Sorderdt_price)),0) SorderDtPrice, ".
					    " ifNull(Sorderdt_discount,0) SorderDtDiscountPer, ".
					    " ((ifNull(SorderDt_Quantity,0) * ifNull(if(upper($cur)='".$Base1."',SorderDt_PriceBase1,if(upper($cur)='".$Base2."',SroderDt_PriceBase2,Sorderdt_price)),0)) - ".
					    " ((ifNull(SorderDt_Quantity,0) * ifNull(if(upper($cur)='".$Base1."',SorderDt_PriceBase1,if(upper($cur)='".$Base2."',SroderDt_PriceBase2,Sorderdt_price)),0) * ifNull(Sorderdt_discount,0)) / 100) ) total ".
				 	    " from sorderdt ".
				  	    " left join items on sorderdt.item_code = items.item_code ".
					    " where sorderdt.Sorder_id = ". $rowsalesorder[0];				
					
	//echo $querysalesorderdt;
	
	if (($grpproject == 1 && $grpclient == 0) && ($masterproject == '' || $masterproject!= $rowsalesorder[6])) {
	  
		if (($cur != 'null') && ($masterproject != '' && $masterproject!= $rowsalesorder[6])){
			//$RowNum += 1;
			$ColumnNum = 5;
		
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
			$ColumnNum += 1;
			
			$SumGroupNetTot = 0;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
		}	
	  
		$worksheet->write($RowNum, $ColumnNum, "Group by Project", $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[6]." - ".$rowsalesorder[7], $grouplevel1Format);
		$ColumnNum += 1;
			
		$masterproject= $rowsalesorder[6];
	  
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
	
	}
	
	if (($grpclient == 1 ) && ($masterclient == '' || $masterclient!= $rowsalesorder[4])) {
	  
	 	if (($cur != 'null') && ($masterclient != '' && $masterclient!= $rowsalesorder[4])){
			
			//$RowNum += 1;
			$ColumnNum = 5;
		
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
			$ColumnNum += 1;
			
			$SumGroupNetTot = 0;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
			
		}	
		
		$worksheet->write($RowNum, $ColumnNum, "Group by Client", $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[4]." - ".$rowsalesorder[5], $grouplevel1Format);
		$ColumnNum += 1;
			
		$masterclient= $rowsalesorder[4];
	  
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
		
	  	
	}
	
	if (($grpstatus == 1 && $grpclient == 0 && $grpproject == 0) && ($masterstatus == '' || $masterstatus!= $rowsalesorder[12])) {
	  
		if (($cur != 'null') && ($masterstatus != '' && $masterstatus!= $rowsalesorder[12])){	
			//$RowNum += 1;
			$ColumnNum = 5;
		
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
			$ColumnNum += 2;
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
			$ColumnNum += 1;
			
			$SumGroupNetTot = 0;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 1;
			$ColumnNum = 0;
			
	  }	
	  
		$worksheet->write($RowNum, $ColumnNum, "Group by Status", $grouplevel1Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[12], $grouplevel1Format);
		$ColumnNum += 1;
			
		$masterstatus= $rowsalesorder[12];
	  
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
	
	}
	
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[14], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[4], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[5], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $rowsalesorder[6], $regularcenterFormat);
	$ColumnNum += 1;
	if ($showProjCostName == 1){
		$worksheet->writestring($RowNum, $ColumnNum, $rowsalesorder[7], $regularcenterFormat);
		$ColumnNum += 1;
	}
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[8], $regularcenterFormat);
	$ColumnNum += 1;
	if ($showProjCostName == 1){
		$worksheet->writestring($RowNum, $ColumnNum, $rowsalesorder[15], $regularcenterFormat);
		$ColumnNum += 1;
	}
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[9], $regularcenterFormat);
	$ColumnNum += 1;
	if ($withprice == 1){
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsalesorder[10], $regularrightFormatNum);
	} else {
		$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormatNum);
	}
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[12], $regularcenterFormat);
	$ColumnNum += 1;
	
	if ($rowsalesorder[13] != "") {
		$RowNum += 1;
		$ColumnNum = 1;
		
		$worksheet->write($RowNum, $ColumnNum, $rowsalesorder[13], $regularleftFormat1);
		$ColumnNum += 1;
			
	}
	
	
    $SumNetTot       = $SumNetTot  		+ $rowsalesorder[10];
	$SumGroupNetTot  = $SumGroupNetTot  + $rowsalesorder[10];

	
	if ($showdetail == 1) {
		$resultsalesorderdt=mysql_query($querysalesorderdt,$connection) or die(mysql_error());
		while($rowsalesorderdt=mysql_fetch_row($resultsalesorderdt)){
		
			$RowNum += 1;
			$ColumnNum = 0;
			
			//$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, $rowsalesorderdt[0], $regularleftFormat1);
			$ColumnNum += 2;
			//$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, $rowsalesorderdt[1], $regularleftFormat1);
			$ColumnNum += 2;
			$worksheet->write($RowNum, $ColumnNum, $rowsalesorderdt[2], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsalesorderdt[3], $regularrightFormat1Num);
			$ColumnNum += 1;
			if ($withprice == 1) {
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsalesorderdt[4], $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsalesorderdt[5], $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, $rowsalesorderdt[6], $regularrightFormat1Num);
				$ColumnNum += 1;
			} else {
				$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormat1Num);
				$ColumnNum += 1;
				$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormat1Num);
				$ColumnNum += 1;				
			}
		}
	}	
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;

}

if (($grpproject == 1  && $grpclient == 0) && ($masterproject == '' || $masterproject!= $rowsalesorder[6])) {
	  
    if (($cur != 'null') && ($masterproject != '' ) ){
		//$RowNum += 1;
		$ColumnNum = 5;
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
		$ColumnNum += 1;
			
		$SumGroupNetTot = 0;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
			
	}	
	
}
if (($grpclient == 1) && ($masterclient == '' || $masterclient!= $rowsalesorder[4])) {
	  
    if (($cur != 'null') && ($masterclient != '' ) ){
		//$RowNum += 1;
		$ColumnNum = 5;
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
		$ColumnNum += 1;
			
		$SumGroupNetTot = 0;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
	}	
}

if (($grpstatus == 1 && $grpclient == 0 && $grpproject == 0) && ($masterstatus == '' || $masterstatus!= $rowsalesorder[12])) {
	  
	if (($cur != 'null') && ($masterstatus != '' ) ){
		//$RowNum += 1;
		$ColumnNum = 5;
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum, $ColumnNum, "Total by group $displaycur", $grouplevel1Format);
		$ColumnNum += 2;
		$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupNetTot, $grouplevel1totalFormat);
		$ColumnNum += 1;
			
		$SumGroupNetTot = 0;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
	}	
}
	
if ($cur != 'null' && $withprice == 1){
	$RowNum += 1;
	$ColumnNum = 6;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
	$worksheet->write($RowNum, $ColumnNum, "Total ".$displaycur, $boldrightFormat);
	$ColumnNum += 2;
	$worksheet->writeNumber($RowNum, $ColumnNum, $SumNetTot, $boldrightFormatNum);
	$ColumnNum += 1;
	
}


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;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}



$workbook->close();


?>