<?php
require_once('Spreadsheet/Excel/Writer.php');
session_start();
if($_SESSION['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($_SESSION['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('".$_SESSION['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, report_color1, report_color2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}


//User Access
$querylog=" Select ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice ".
          " From ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')  ";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	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']."' ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist   = mysql_num_rows($projectresult);



//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListSalesItemsByClients.xls');

//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List Sales');
$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);  


$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');   


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

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,6,'List Sales', $TitleFormat);
               
$worksheet->write(1,8,"Printed Date:", $boldleftFormat);
$worksheet->write(1,9,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,8,"Time:", $boldleftFormat);		
$worksheet->write(2,9,$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,"Item", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 25);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$query_warehouse = " select Warehouse_code, Warehouse_name from warehouse ";
				// " where ledger_number between ifNull(".$fromclient.",ledger_number) and ifNull(". $toclient.",ledger_number) ";

$warehousear = array();
$i=0;
$resultwarehouse = mysql_query($query_warehouse,$connection);
while($rwwarehouse = mysql_fetch_row($resultwarehouse)){   
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->writestring($RowNum,$ColumnNum,$rwwarehouse[1], $boldcenterFormat);
	$ColumnNum += 1;
	$warehousear[$i] = $rwwarehouse[0];
	$i++;
}
	
$querydelivery = "select deliverydt.Warehouse_code,date_format(deliveries.delivery_date,'%d/%m/%Y') delivery_date, deliverydt.item_code, item_description, deliverydt.currency_code,
				if(upper($cur)='".$Base1."',round(sum(deliverydt_quantity*SaleDt_PriceBase1),2),if(upper($cur)='".$Base2."',round(sum(deliverydt_quantity*SaleDt_PriceBase2),2),0)) as totalprice
				from deliverydt
				left join deliveries on deliveries.delivery_id = deliverydt.delivery_id
				left join warehouse on warehouse.Warehouse_code = deliverydt.Warehouse_Code
				left join items on items.item_code = deliverydt.item_code
				left join project on project.project_code = deliveries.project_code
				left join costcent on costcent.costcent_code = deliveries.costcent_code";
			
if ($projectexist >	0)
  $querydelivery= $querydelivery." 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 project.project_code like concat(Q1.project_code,'%') ";

				


$querydelivery= $querydelivery.  				
				" where deliveries.client_code between ifNull(".$fromclient.",deliveries.client_code) and ifNull(". $toclient.",deliveries.client_code) ".
				" and delivery_reference between ifNull(". $fromref .",delivery_reference) and ifNull(". $toref.",delivery_reference) ".
				" and upper(ifNull(deliveries.project_code,-1)) like ifNull(upper(".$fromproject."), upper(ifNull(deliveries.project_code,-1))) ".
				" and date_format(deliveries.delivery_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(deliveries.delivery_date,'%Y-%m-%d')) 
				and ifNull(". $todate.",date_format(deliveries.delivery_date,'%Y-%m-%d')) ";
if ($_GET['salesman'] !='') {
	$querydelivery= $querydelivery."and salesman_code='$salesman'";
}
$querydelivery= $querydelivery."group by deliverydt.item_code, deliverydt.Warehouse_code
								order by deliverydt.item_code, deliverydt.Warehouse_code ";
								//echo $querydelivery;
				

$masteritem  = '';


  $resultdelivery=mysql_query($querydelivery,$connection) or die(mysql_error());

$i=0;
$ColumnNum = 0; 
$RowNum    = $RowNum+1;

while($rowdelivery=mysql_fetch_row($resultdelivery)){


	if($masteritem != $rowdelivery[2]){
		$RowNum    = $RowNum+1;	$i=0;	$ColumnNum = 0; 	
		$worksheet->write($RowNum, $ColumnNum, $rowdelivery[2], $regularcenterFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum,$rowdelivery[3], $regularcenterFormat);
		$ColumnNum += 1;
	}
	
	 while($warehousear[$i]){
	  if($warehousear[$i] == $rowdelivery[0]){
		  $worksheet->writeNumber($RowNum, $ColumnNum, $rowdelivery[5], $regularcenterFormat);
		  $ColumnNum += 1;
		  $i++;break;
	  }else {
		//$worksheet->write($RowNum, $ColumnNum, '', $regularcenterFormat);
		$ColumnNum += 1;
		$i++;
		}
	 }
	  if($masteritem != $rowdelivery[2]){
	
		}
	 
	   $masteritem = $rowdelivery[2];
	   
	}


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();

?>