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

include("config.inc.php");
include("function.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");

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

$title = "List of Booked Items";

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

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

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


?>
<!DOCTYPE html>
<head><title><?php echo $title ?></title>
<style>
span.span1{
	font-size: 12px;
    font-weight: bold;
    font-family: arial,sans-serif;
}

tr.headstyle > th {
	font-size: 14px;
    font-weight: bold;
    font-family: Times-New-Roman;
	border-bottom: 1px solid black;
}

tr.detailstyle > td {
	font-size: 12px;
	font-family: Times-New-Roman;
}

tr.laststyle > td {
	font-size: 14px;
	 font-weight: bold;
	font-family: Times-New-Roman;
	border-top: 1px solid black;
}
</style>
</head>
<body>

<table width='100%' border=0 cellpadding=0 cellspacing=0><col width='30%'><col width='20%'><col width='20%'><col width='30%'>
	<tr style='height: 35px'>
	<th style='text-align: center'><img src='<?php echo "LogoFile.php"?>' width='120' height='70'></th>
	<th style='font-size: 24px;' colspan='2'><?php echo $title ?></th>
	<th style='text-align: right; font-size: 12px'><b><u>Printed Date:</u></b> 
			<?php echo $printdate[2]."/".$printdate[1]."/".$printdate[0];?><br>
			<b><u>Printed Time:</u></b> 
			<?php echo " ".$dt[1]." ".$dt[2];
			?>
	</th>
	</tr>

	<tr><td colspan='4'><hr></td></tr>
	<tr><td colspan='4'><span class='span1'>Filter By:</span></td></tr>
	<tr>
		<td colspan='2'><span class='span1'>&#8608; Date From-To: <?php echo $dispfromdate." - ".$disptodate ?></span></td>
		<td colspan='2'><span class='span1'>&#8608; Client From-To: <?php echo ($fromclient!= '')?$fromclient." - ".$toclient:'' ?></span></td>
	</tr>
	<tr>
		<td colspan='2'><span class='span1'>&#8608; Item From-To: <?php echo ($fromitem != '')?$fromitem." - ".$toitem:'' ?></span></td>
		<td colspan='2'><span class='span1'></span></td>
	</tr>
	<tr><td colspan='4'><span class='span1'>Group By: <?php echo $groupbyArr[$grpbyval] ?></span></td></tr>
	<tr><td colspan='4'><span class='span1'>Show Items: <?php echo $QtyTypeArr[$qtytypeval] ?></span></td></tr>
	<tr height='30px'><td colspan='4'></td></tr>
</table>

<?php
	
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."'";


	
?>
<table  width='100%' border=0 cellpadding=0 cellspacing=0>
	<tr>
	<?php if ($grpbyval == 2) { ?>	
	<tr><th colspan='2'></th>
	<?php } ?>
	<th colspan='3'></th>
	<?php 
		$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;
					$colspan=$colspan+$day3;	
					
		?>
				
				<th colspan='<?php echo $colspan ?>'><?php echo $month."-".$year?></th>
		<?php	
			}
			$month1 = 1;
		}
	?>
	</tr>
	<tr class='headstyle'>
	<?php if ($grpbyval == 2) { ?>	
		<th align='left'>Client Code</th><th align='left'>Client Name</th>
	<?php } ?>
	<th align='left'>Item Code</th><th align='left'>Item Description</th><th>Qty Exists</th>
	<?php 
		$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++){		
		?>
				
				<th><?php echo str_pad($day,2,'0',STR_PAD_LEFT) ?></th>
		<?php	
					} // end of days loop
				$day1 = 1;	
			} // end of months loop
			$month1 = 1;
		} // end of years loop
	?>
	</tr>
<?php
	 $col = 0;
	 $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 as item_code,item_description,
	QtyExistsWSorder(items.item_code, TransferrequestDt_WarehouseFrom, '".$todate." 23:59:59', 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 = '*#';	
	$results = mysql_query($querys, $connection);		
	while ($rows = mysql_fetch_array($results)){ 
?>
	<?php 
		$col++;
		if($col%2==0){ 
			$color = "bgcolor = '".$report_color1."'";
		}else{
			$color = "bgcolor='".$report_color2."'";
		}
    ?>
	<tr class='detailstyle' <?php echo $color ?>>
		<?php if ($grpbyval == 2) { if ($tempClient != $rows[0]) { ?>
			<td><?php echo $rows[0] ?></td>
			<td><?php echo $rows[1] ?></td>
		<?php } else { ?>
			<td></td>
			<td></td>	
		<?php 	} }?>
		<td ><?php echo $rows[2] ?></td>
		<td ><?php echo $rows[3] ?></td>
		<td align='center'><?php echo $rows[4] ?></td>
		<?php 
			$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++){	

		?>
			<td align='center' ><?php echo $rows[$tt] ?></td>

		<?php 
					$tt++;
						} // end of days loop
						$day1 = 1;	
					} // end of months loop
					$month1 = 1;
				} // end of years loop
		?>
	</tr>
<?php 
	$tempClient = $rows[0];
	} 	
?>

<tr class='laststyle'><td align='right' colspan='5'></td><td align='center' colspan='<?php echo $colspan ?>'></td></tr>
</table>

<script>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</body>
</html>
	
<?php	
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;
}
?>