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

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


 $querylog="SELECT RI_expirydate as loginaccess ,ExporttoExcel 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);
}
if ($loginaccess=='' or $loginaccess==0)
	header("location:menu.php");


$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');
	$itemcode=$_GET['itemcode'];
	$suppliercode=$_GET['suppliercode'];
	
	$sql1="
	start transaction;";
	$sql2="
	set @PrvItem_Code:='';";
	$sql3="
	set @bal:=0;";
	$sql4="
	select item_code,item_description,expiredate,qtyin,qtyexit, suppliercode from (
	select exp_report.item_code,item_description,expiredate,qtyin,qtyout,
    if(@PrvItem_Code is null,@PrvItem_Code:='',@PrvItem_Code),
    if(@bal is null,@bal:=0,@bal),
    if(exp_report.item_code<>@PrvItem_Code ,@bal:=qtyin-qtyout,@bal:=@bal+qtyin),
    if(exp_report.item_code<>@PrvItem_Code,@PrvItem_Code:=exp_report.item_code,@PrvItem_Code),
    @PrvItem_Code,
    @bal as qtyexit, suppliercode from (
	select purchasesdt.item_code,expiredate,sum(qtyin) as qtyin,
    sum(ifnull(saledt_quantity,0)*if(salesdt.journal_type='SI',1,-1))
    +sum(ifnull(productiondt_quantity,0))
    +sum(ifnull(preturn.purchasedt_quantity,0))
    +sum(ifnull(adjustmentdt.adjustmentdt_quantity,0))
    as qtyout, suppliercode
    from
    (select item_code,Production_ExpireDate as expiredate,sum(Production_quantity) as qtyin, null as suppliercode from production where
    (production_expiredate is not null) and (production_expiredate>'1990-01-01')
    group by item_code,Production_ExpireDate
    union
    select purchasesdt.item_code,purchasedt_expiredate as expiredate,sum(purchasedt_quantity) as qtyin, purchasesdt.Supplier_Code as suppliercode
    from purchasesdt
    where purchasesdt.journal_type='PI' and (purchasedt_expiredate is not null) and (purchasedt_expiredate>'1990-01-01')
    group by purchasesdt.item_code,purchasedt_expiredate
    union
    select adjustmentdt.item_code,adjustmentdt_expiredate as expiredate,sum(adjustmentdt_quantity) as qtyin, null as suppliercode
    from adjustmentdt
    where adjustmentdt.journal_type='AI' and (adjustmentdt_expiredate is not null) and (adjustmentdt_expiredate>'1990-01-01')
    group by adjustmentdt.item_code,adjustmentdt_expiredate
    order by item_code,expiredate asc

	)
    as purchasesdt
	left join salesdt on salesdt.item_code=purchasesdt.item_code
	left join productiondt on productiondt.item_code=purchasesdt.item_code
	left join purchasesdt as preturn on preturn.item_code=purchasesdt.item_code and preturn.journal_type='PR'
	left join adjustmentdt  on adjustmentdt.item_code=purchasesdt.item_code and adjustmentdt.journal_type='AR'
	left join suppliers on suppliers.ledger_number = purchasesdt.suppliercode
	group by item_code,expiredate ) as exp_report
	inner join items on items.item_code=exp_report.item_code
	) as exp_report
	where qtyexit>0";
 
	
	
	$fromdateisnull =false;$todateisnull = false;
	
	if ($_GET['fromdate'] == null ) $fromdateisnull = true;
    if ($_GET['todate'] == null )   $todateisnull = true;
	 
	 
	 
	 if($fromdateisnull  == false && $todateisnull ==false)
	  $sql4 = $sql4." and expiredate  between $fromdate and $todate ";
 
	   
  
	 if($fromdateisnull  == false && $todateisnull ==true)
	  $sql4 = $sql4." and expiredate  > $fromdate   ";

	 if($fromdateisnull  == true && $todateisnull ==false)
	  $sql4 = $sql4." and expiredate < $todate ";  
	 
		
		 
		
    
 
 
	 
	
	
	
	
	if($itemcode != null){
	$sql4.=" AND item_code ='".$itemcode."'";
	}
	if($suppliercode != null){
	$sql4.=" AND  suppliercode ='".$suppliercode."' ";
	}
	if(1 == 1 ){
	$sql4.=";";
	}
	$sql5="select COMPANY_NAME From setup;";
	$sql6="select COMPANY_memo From setup;";	  
	
	$result5 =  mysql_query($sql6,$connection) or die(mysql_error());
	$result4 =  mysql_query($sql5,$connection) or die(mysql_error());
	$result1 =  mysql_query($sql1,$connection) or die(mysql_error());
	$result2 =  mysql_query($sql2,$connection) or die(mysql_error());
	$result3 =  mysql_query($sql3,$connection) or die(mysql_error());
	
 
 
 
	$result  =  mysql_query($sql4,$connection) or die(mysql_error());
	
	
 
	?>
<HTML>
<HEAD>
<TITLE> Expiry Date Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<link rel="stylesheet" type="text/css" media="all" href="css-v2/style.css">
<style type="text/css">
/*
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                      }				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
				 
table.master td.sr  { font-size: 10px; 
					  font-style: italic; 
                      font-family:Times-New-Roman;
                    }

table.master td.grouplevel1 { font-size: 14px; 
							  font-weight: bold;
                              font-family:Times-New-Roman;
						      color: #0000FF;
							  background-color: #F0F0F0;
                            }
table.master td.grouplevel2 { font-size: 14px; 
                              font-family:Times-New-Roman;
						      color: #FF0000;
							  background-color: #F0F0F0;
                            }	
table.master td.grouplevel3 { font-size: 13px; 
							  font-style: italic;
                              font-family:Times-New-Roman;
						      color: #336600;
							  background-color: #F0F0F0;
                            }					
					
table.totals tr.summary { font-size: 13px; 
                          font-weight: bold;
                          font-family:Times-New-Roman;
						  background-color: #F0F0F0;
                        }					   

table.totals tr.grouplevel1total { font-size: 14px; 
								   font-weight: bold;
								   font-family:Times-New-Roman;
								   color: #0000FF;
								   background-color: #F0F0F0;
								 }				 
table.totals tr.grouplevel2total { font-size: 14px; 
							       font-family:Times-New-Roman;
							       color: #FF0000;
							       background-color: #F0F0F0;
						         }										 
table.totals tr.grouplevel3total { font-size: 13px; 
							       font-style: italic;
							       font-family:Times-New-Roman;
							       color: #336600;
							       background-color: #F0F0F0;
						          }						   				 
*/
</style>
</HEAD>

<BODY>
<table width="100%" border="0" align="left" > 
<table border='0' cellspacing=0 cellpadding=0  style='table-layout:fixed;' width='700px'>
<form name="form" id ="form">
<col width=127>
		  <col width=345>
		  <col width=50>
		  <col width=80>
		  <col width=130><tr height='25px'>	<td align='left' rowspan='3'>
		  <img src='LogoFile.php' style='width:110px; height:120px;' /></td>	
		  <td align='left' colspan='2'>
		  <span style='font-size:14px; font-weight:bold; font-style:italic;'>
		  <?php 
		  while($row5=mysql_fetch_array($result4)){
		  echo $row5['COMPANY_NAME'];
		  }
		  ?>
		  </span>
		  </td>	<td align='center' rowspan='3' colspan='2' valign='top'>
		  <Table  cellspacing=0 cellpadding=0 style='border:1px solid #000000;'>
		  <tr height='25px'><td style='border-bottom:1px solid #000000;' valign='middle'>
		  <span style='font-size:11px; font-weight:bold;font-family:arial;'>&nbsp; Printed Date: &nbsp;</span>
		  </td>	
		  <td align='right' style='border-bottom:1px solid #000000;padding-right: 2px;' valign='middle'>
		  <p  style='font-size:12px;font-family:arial;'><?php echo date("d/m/Y");?></p></td></tr>
		
			<tr height='25px'>
			<td align='right'  style='border-bottom:1px solid #000000;' valign='middle'>
			<span style='font-size:11px; font-weight:bold;font-family:arial;'>Time: &nbsp;</span></td>
			<td align='right'  style='border-bottom:1px solid #000000;padding-right: 2px; ' valign='middle'>
			<span style='font-size:12px;font-family:arial;'><?php echo date("h:i:sa");?></span></td></tr>
			<tr height='25px'><td align='right' valign='middle' >
			<span style='font-size:11px; font-weight:bold;font-family:arial;'>User: &nbsp;</span></td>
			<td align='right' valign='middle' style='padding-right: 2px;' >
			<span style='font-size:12px;font-family:arial;'><?php echo $_SESSION['username'] ;?></span></td></tr></table></td></tr>
			<tr height='10'>	<td align='left' rowspan='2' colspan='2'>
			<span style='font-size:10px;'><pre>
			<?php 
			while($row6=mysql_fetch_array($result5)){
			echo $row6['COMPANY_memo'];
		    }
			?>
			</pre>
			</span>
			</td></tr>
			<tr height='25'></tr>
			<tr height='25'>	<td align='center' colspan='5'>
			<span style='font-size:18px; font-weight:bold;'>Items</span></td></tr>
			<tr height='5px'><td colspan='15'></td></tr></table>
			<tr><td>
			<table class="report-master"  border="0" align="left" style='table-layout:fixed;clear:left;'>
			<col width=100>
			<tr><td>&nbsp;</td>
		  <td  valign='top' width=100%>
		  <?php
		  if($itemcode != null){
		  echo "<b>item code:'".$itemcode."'<br>";}
		  if($suppliercode != null){
		  echo "<b>supplier code:'".$suppliercode."'";
		  }
		  if($_GET['fromdate'] != null && $_GET['todate'] != null){
		  ?>
		  <br>Date:<?php echo $fromdate?>--><?php 
		  echo $todate;
		  }?></tr>
<tr height='5'><td><h5></td></tr>
</table></td></tr>
<tr><td><table  border='0' width='' class='report-master' style='clear:left;' align='left' cellpadding='0' cellspacing='0'><thead>
<tr>
<td>
<table  border='0'  align='left' class='master' style='table-layout:fixed' cellspacing=0>
				<col width=120>
				 <col width=400>
				 <col width=140>
				 <col width=100>
				 <col width=100>
				 <col width=130>
				 
				 
				 <tr><th >Item Code</th>
				 <th >Item Description</th>
				 <th >Expire Date</th>
				 <th >Qty in</th>
				 <th >Qty out</th>
				 <th >Qty exact</th>
				</tr></table></td></tr>
				 </thead>
				 <tr>
				 <td colspan=2>
				 <table  border='0'  align='left' class='master' >
				 <?php
				  while($row=mysql_fetch_array($result)){
				  ?>
				 <col width=120>
				 <col width=400>
				 <col width=140>
				 <col width=100>
				 <col width=100>
				 <col width=130>
				  <tr class='group' style='background-color:#ABD4F1'>
				  <td align='left' valign='top' > <?php echo $row['item_code']?></td>
				  <td align='left' valign='top' ><?php echo $row['item_description']?></td>
				  <td align='left' valign='top' >
				  <?php 
				  $date = date_create('"'.$row['expiredate'].'"'); 
				  echo date_format($date, 'd-m-Y');
				  ?>
				  </td>
				  <td align='center' valign='top' ><?php echo $row['qtyin']?></td>
				  <td align='center' valign='top' ><?php echo $row['qtyexit']?></td>
				  <?php
					$balance = $row['qtyin']-$row['qtyexit'];
				  ?>
				  <td align='center' valign='top' ><?php echo $balance?></td>
				  </tr>
				  <?php
				  }
				  ?>
				  </table></td></tr>
				  <tr>
				  <td><hr width='100%' color='black' size='1'></td></tr>
				  <tr>
				  <td>
				  <table  border='0' width='100%' align='left' class='totals'>
		  <col width=120><col width=200><col width=50><col width=35> <col width=90>
				  <col width=120>
				  <col width=120>
				  <col width=120><col width=40><col width=120><col width=130>
				  <col width=130
				  <col width=130>
				  <col width=100>
				  </table></td></tr></table></form>

</BODY>
</HTML>