<?php


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

include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');

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

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

$querysetup="select DecimalBase, DecimalBase1, DecimalBase2,report_color1, report_color2, 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);
} 


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

$fromdate 	= $_GET['fromdate'];
$todate 	= $_GET['todate'];
$fromclient = $_GET['fromclient'];
$toclient 	= $_GET['toclient'];
$fromitem 	= $_GET['fromitem'];
$toitem 	= $_GET['toitem'];
$grpbyval 	= $_GET['grpbyval'];
$qtytypeval	= $_GET['qtytypeval'];

$groupbyArr = array('1' => 'Items','2'=> 'Client & Item');
$QtyTypeArr = array('1' => 'Booked','2'=> 'Available');

$dispfromdate = $fromdate;
$disptodate = $todate;

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

$query = "select date_format('".$fromdate."','%Y'), date_format('".$todate."','%Y'), date_format('".$fromdate."','%m'), date_format('".$todate."','%m'), 
		datediff('".$todate."','".$fromdate."'), date_format('".$fromdate."','%d'), date_format('".$todate."','%d') ";
$result = mysql_query($query, $connection);
$row = mysql_fetch_row($result);



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

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

//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List Of Booked Items');
$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,
  ));
  
//Elie Format

$regularleftFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormatNum1 =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
  
  

$regularleftFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormatNum1->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

 

// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  '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
  )); 
$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
  ));  
$summaryFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right'  ,
  'bold'=>1
  ));   

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

 if ($dispfromdate != '')      $SelectedOptions .= " Date From/To: ". $dispfromdate . " - ". $disptodate ." \n ";	
 if ($fromclient != '')      $SelectedOptions .= " Client From/To: ". $fromclient . " - ". $toclient ." \n ";	
 if ($fromitem != '')      $SelectedOptions .= " Item From/To: ". $fromitem . " - ". $toitem ." \n ";	
 
$SelectedOptions .= " Group By: ". $groupbyArr[$grpbyval] ." \n ";	
$SelectedOptions .= " Show Items: ". $QtyTypeArr[$qtytypeval] ." \n ";	

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,4,"List Of Booked Items", $TitleFormat);
               
$worksheet->write(1,9,"Printed Date:", $boldleftFormat);
$worksheet->write(1,10,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,9,"Time:", $boldleftFormat);		
$worksheet->write(2,10,$dt[1]." ".$dt[2], $regularleftFormat);

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
if($Module_expiredate==1 && $edatechk == 1)
	$worksheet->setMerge(3, 0, 6, 11);
else $worksheet->setMerge(3, 0, 5, 11);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat); 

if ($fromclient == '') $fromclient = "NULL";
else $fromclient = "'".$fromclient."'";
if ($toclient == '') $toclient = "NULL";
else $toclient = "'".$toclient."'";

if ($fromitem == '') $fromitem = "NULL";
else $fromitem = "'".$fromitem."'";
if ($toitem == '') $toitem = "NULL";
else $toitem = "'".$toitem."'";



$RowNum    = 10;
$ColumnNum = 0;	

if ($grpbyval == 2) {
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Client Code", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"Client Name", $boldcenterFormat);
	$ColumnNum += 1;
}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Item Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Item Description", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Qty Exist", $boldcenterFormat);
$ColumnNum += 1;

$year1	= $row[0];
		$year2	= $row[1];
		$yearDiff = ($year2 - $year1)+1;
		
		$month1 = $row[2];
		$month2 = $row[3];

		$day1 = $row[5];
		$day2 = $row[6];

		for($year=$year1;$year<=$year2;$year++){
			if($year != $year2){
				$month3 = 12;
			}
			else {
				$month3 = $month2;
			}
			for($month=$month1;$month<=$month3;$month++){
				if($month != $month3)
					$day3 = getDaysInMonth($month,$year);	
				else 
					$day3 = $day2;	
					for($day=$day1;$day<=$day3;$day++){	
					
						$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
						$worksheet->write($RowNum,$ColumnNum,str_pad($day,2,'0',STR_PAD_LEFT).'-'.str_pad($month,2,'0',STR_PAD_LEFT).'-'.$year, $boldcenterFormat);
						$ColumnNum += 1;

						} // end of days loop
				$day1 = 1;	
			} // end of months loop
			$month1 = 1;
		} // end of years loop

		
 $year1	= $row[0];
 $year2	= $row[1];
 $yearDiff = ($year2 - $year1)+1;
 
 $month1 = $row[2];
 $month2 = $row[3];

 $day1 = $row[5];
 $day2 = $row[6];
 
 $querys = "select client_code,ledger_name,items.item_code,item_description,
	QtyExistsWSorder(items.item_code, TransferrequestDt_WarehouseFrom, transferrequest.Transferrequest_Date, 0, 'TR', '', '') as Qty_Exist  ";

	for($year=$year1;$year<=$year2;$year++){
		if($year != $year2){
			$month3 = 12;
		}
		else {
			$month3 = $month2;
		}
		for($month=$month1;$month<=$month3;$month++){
			if($month != $month3)
				$day3 = getDaysInMonth($month,$year);	
			else 
				$day3 = $day2;	
				for($day=$day1;$day<=$day3;$day++){	
					$qDate = $year."-".str_pad($month,2,'0',STR_PAD_LEFT)."-".str_pad($day,2,'0',STR_PAD_LEFT);
					$querys .= " ,if('$qtytypeval'='1',sum(if('$qDate'>=datefrom and dateTo>='$qDate',transferrequestdt_quantity,0)),
									QtyExistsWSorder(items.item_code, TransferrequestDt_WarehouseFrom, '".$todate." 23:59:59', 0, 'TR', '', '') 
									- sum(if('$qDate'>=datefrom and dateTo>='$qDate',transferrequestdt_quantity,0))
								)  as 'Date$year$month$day' ";	

				} // end of days loop
				$day1 = 1;	
			} // end of months loop
			$month1 = 1;
		} // end of years loop

	$querys .= " from transferrequest
	inner join transferrequestdt on transferrequest.transferrequest_id=transferrequestdt.transferrequest_id
	inner join items on items.item_code=transferrequestdt.item_code
	inner join clients on clients.ledger_number=transferrequest.client_code
	where ifnull(client_code,'') between ifnull(".$fromclient.",ifnull(client_code,'')) and ifnull(".$toclient.",ifnull(client_code,''))
	and transferrequest.Transferrequest_Date between '".$fromdate." 00:00:00' and '".$todate." 23:59:59'
	and ifnull(transferrequestdt.item_code,'') between ifnull(".$fromitem.",ifnull(transferrequestdt.item_code,'')) and ifnull(".$toitem.",ifnull(transferrequestdt.item_code,''))
	 "; 
if ($grpbyval == 1)	
	$querys .= " group by item_code 
				order by item_code ";
else if ($grpbyval == 2)		
	$querys .= " group by client_code, item_code 
				order by client_code, item_code "; 
$tempClient = '*#';		
$ColumnNum=0;
$RowNum += 1;
$results=mysql_query($querys,$connection) or die(mysql_error()); 

while($rows=mysql_fetch_row($results)){
		
	if ($grpbyval == 2) { 
		if ($tempClient != $rows[0]) {
		$worksheet->write($RowNum, $ColumnNum, $rows[0], $regularleftFormat);
		$ColumnNum += 1;	
		$worksheet->write($RowNum, $ColumnNum, $rows[1], $regularleftFormat);
		$ColumnNum += 1;	
		}
		else $ColumnNum += 3;	
	}
		$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rows[3], $regularleftFormat);
		$ColumnNum += 1;	
		$worksheet->write($RowNum, $ColumnNum, $rows[4], $regularrightFormatNum1);
		$ColumnNum += 1;	
	
	 $tt = 5;
	 $year1	= $row[0];
	 $year2	= $row[1];
	 $yearDiff = ($year2 - $year1)+1;
	 
	 $month1 = $row[2];
	 $month2 = $row[3];

	 $day1 = $row[5];
	 $day2 = $row[6];

	 for($year=$year1;$year<=$year2;$year++){
		if($year != $year2){
			$month3 = 12;
		}
		else {
			$month3 = $month2;
		}
		for($month=$month1;$month<=$month3;$month++){
			if($month != $month3)
				$day3 = getDaysInMonth($month,$year);	
			else 
				$day3 = $day2;	
				for($day=$day1;$day<=$day3;$day++){	

					$worksheet->writeNumber($RowNum, $ColumnNum, $rows[$tt], $regularrightFormatNum1);
					$ColumnNum += 1;	
					$tt++;
				} // end of days loop
				$day1 = 1;	
			} // end of months loop
			$month1 = 1;
		} // end of years loop
	$RowNum += 1;
	$ColumnNum = 0;
	$tempClient = $rows[0];
} 




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


/**
*	Return Number of Days in Each Month
*	Done by Nadine
**/

function getDaysInMonth($monthNbr,$year){
	if($monthNbr >= 1 && $monthNbr <= 12){
		if (in_array($monthNbr, array(1,3,5,7,8,10,12)))
			return 31;
		else if (in_array($monthNbr, array(4,6,9,11) ))
			return 30;
		else if ($monthNbr == 2){
			if((($year % 4) == 0) && ((($year % 100) != 0) || (($year % 400) == 0)))
				return 29;
			return 28;
		} 
	}
	return 0;
}
$workbook->close();

?>