<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> List of Purchases </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<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.group td { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
               }				 
				 
table.detail td { font-size: 10px; 
                  font-family:Times-New-Roman;
                 }				 
table.headerdetail td { font-size: 10px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 
table.totals td { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }					  
				 

</style>


</HEAD>


HERE;

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

if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}

//maroun 30.03.2011
$querylog="SELECT print as printaccess, ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter
		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 ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';



$querysetup="select grid_color1,grid_color2, 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);
}


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

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>List of Purchases</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
//echo DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];

$dispfromref  = $fromref;
$disptoref = $toref;
$dispfromsupp = $fromsupp;
$disptosupp = $tosupp;
$dispfromdate = $fromdate;
$disptodate = $todate;

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

if (empty($fromsupp))   
   $fromsupp = 'null';
else
   $fromsupp = "'".$fromsupp."'";   
   
if (empty($tosupp))   
   $tosupp = 'null';
else
   $tosupp = "'".$tosupp."'";      

if (empty($fromproject))   
   $fromproject = 'null';
else
   $fromproject = "'".$fromproject."%'";    

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

if (empty($fromdate))   
   $fromdate = 'null';
else
   $fromdate = "'".$fromdate."'"; 
   
if (empty($todate))   
   $todate = 'null';
else
   $todate = "'".$todate."'";
   
$showboth = $purchaseboth;   

if (empty($purtype)) $purtype = 'PI';

print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" cellpadding='0' cellspacing='0' >
<tr>
  <td width="10%" valign='top'><b>Supplier</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="15%">$dispfromsupp <br> $disptosupp</td>
  <td width="7%" valign='top'><b>Reference</b></td>
  <td width="5%"><b>From <br>To </b></td>
  <td width="50%" colspan='2'>$dispfromref <br> $disptoref </td>
</tr>
<tr>
  <td width="10%"><b>Date From/To</b></td>
  <td width="20%" colspan='2'>$dispfromdate - $disptodate </td>
  <td width="7%"><b>Currency</b></td>
  <td width="5%">$displaycur </td>
  <td width="10%" align='center'><b>Project:</b></td>
  <td width="40%" align='left'>$dispfromproject </td>
</tr>
</table>


<table width="100%" border="0" align="center" class='master' cellpadding='0' cellspacing='0' style='table-layout:fixed;'>
<col width=70>
<col width=80>
<col width=40>
<col width=90>
<col width=250>
<col width=70>
<col width=70>

HERE;

if ($showProjCostName == 1)
	echo "<col width=120px>";

echo "<col width=90>";
if ($showProjCostName == 1)
	echo "<col width=120px>";
echo "<col width=50>
	<col width=110>
	<col width=80>
	<thead>
	<tr>
		<th align='left'>Date</th>
		<th>Ref.</th>
		<th>Type </th>
		<th>Supplier</th>
		<th>Description</th>
		<th>Invoice No.</th>
		<th>Project</th>";
if ($showProjCostName == 1)		
echo "	<th>Project Name</th>";	
echo "	<th>Costcenter</th>";
if ($showProjCostName == 1)		
echo "	<th>Costcenter Name</th>";	
	
if ($purtype == 'PI') {
	echo" <th>Cur.</th>
		  <th>Net Total $displaycur</th>
		  <th>Charges.</th>";
} else {
	echo"<th colspan='3'>&nbsp;</th>";
}
	  
echo"</tr>
<tr><td colspan='13'><hr width='100%' color='black' size='2'></td></tr>
</thead><tdetail>";


if ($showdetail == 1) {
	echo "<tr>";
	echo "  <td colspan='10'><table class='headerdetail' width='100%' border='0' align='left' cellpadding='0' cellspacing='0' style='table-layout:fixed;'>
			<col width=100>
			<col width=330>
			<col width=100>
			<col width=50>
			<col width=50>
			<col width=40>
			<col width=80>
			<col width=20>";
	echo "  <td align='center'> Item Code/Description </td>";
	echo "  <td>&nbsp;</td>";
	echo "  <td> Warehouse </td>";
	echo "  <td align='right'> Qty. </td>";
	
	if ($purtype == 'PI') {
		echo "  <td align='center'> Unit Price </td>";
		echo "  <td align='center'> Disc. % </td>";
		echo "  <td align='center'> Total </td>";
	} else {
		echo "  <td colspan='3'>&nbsp;</td>";
	}	
	echo "  <td>&nbsp;</td>";
	echo " </tr></table></td>";
	echo " </tr>";
		
}	
print <<<HERE

HERE;
 
    

   
$querypurchases= " select Purchase_id, date_format(Purchase_Date,'%d/%m/%Y') Purchase_Date, Purchase_Reference, Journal_Type, ".
                 " purchases.Supplier_code, suppliers.Ledger_name , purchases.project_code, project_name, purchases.costcent_code, purchases.currency_code, ".
				 " ( ifnull(if(upper($cur)='".$Base1."',Purchase_TaxableBase1,if(upper($cur)='".$Base2."',Purchase_TaxableBase2,Purchase_Taxable)),0) + ".
  				 "   ifnull(if(upper($cur)='".$Base1."',Purchase_NoneTaxableBase1,if(upper($cur)='".$Base2."',Purchase_NoneTaxableBase2,Purchase_NoneTaxable)),0) + ".
 				 "   ifnull(if(upper($cur)='".$Base1."',Purchase_VATBase1,if(upper($cur)='".$Base2."',Purchase_VATBase2,Purchase_VAT)),0) - ".
				 "   ifnull(if(upper($cur)='".$Base1."',Purchase_DiscountBase1,if(upper($cur)='".$Base2."',Purchase_DiscountBase2,Purchase_DiscountAmount)),0)) net_total, ".
				 " ifnull(if(upper($cur)='".$Base1."',Purchase_ChargesBase1,if(upper($cur)='".$Base2."',Purchase_ChargesBase2, Purchase_Charges)),0) PurchaseCharges, ".
				 " if('".$date."' = 'date', date_format(Purchase_Date,'%Y-%m-%d'), Purchase_Reference) ord, Purchase_InvoiceNo, costcent_name ".
 				 " from purchases ".
				 " left join suppliers on purchases.Supplier_code = suppliers.ledger_number ".
				 " left join project on purchases.project_code = project.project_code ".
				 " left join costcent on costcent.Costcent_code = purchases.Costcent_code ";
				
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $querypurchases= $querypurchases." left join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
								   "			 where loginstk.user_account='".$_SESSION['useraccount']."' ".
								   "             and (loginstk.project_code is not null and loginstk.project_code != '') ".
								   "	       ) Q1 on purchases.project_code like concat(Q1.project_code,'%') ";
if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1) 
		$querypurchases = $querypurchases." 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 purchases.costcent_code like concat(Q2.Costcent_Code,'%')";
								  
$querypurchases= $querypurchases.  
				" where Purchase_Reference between ifNull(". $fromref .",Purchase_Reference) and ifNull(". $toref.",Purchase_Reference) ".
				" and (($grpsupplier=1 and purchases.Supplier_code between ifNull(".$fromsupp.",purchases.Supplier_code) and ifNull(". $tosupp.",purchases.Supplier_code)) or ($grpsupplier=0 and 1 = 1)) ".
				//" and upper(purchases.currency_code) = ifNull(upper(".$cur."), upper(purchases.currency_code)) ".
				" and upper(ifNull(purchases.project_code,-1)) like ifNull(upper(".$fromproject."), upper(ifNull(purchases.project_code,-1))) ".
				" and date_format(Purchase_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Purchase_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Purchase_Date,'%Y-%m-%d'))  ".
				" and (($showboth = 1 and 1 = 1) or ($showboth =0 and purchases.journal_type='".$transaction."')) ";
				//" and ifNull(purchases.purchase_type,'PI') In ('".$purtype."') ";

$querypurchases= $querypurchases.  
				" order by if($grpsupplier=1, purchases.Supplier_code, if($grpproject=1, purchases.project_code, null) ), ord, Purchase_Reference ";
				


				
$SumNetTot      = 0;
$SumGroupNetTot = 0;
$masterproject  = '';
$mastersupplier = '';
	
//echo $querypurchases;

$resultpurchases=mysql_query($querypurchases,$connection) or die(mysql_error());

$col=0;
while($rowpurchases=mysql_fetch_row($resultpurchases)){
	
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
    
	$querypurchasesdt= " select purchasesdt.item_code, items.item_description, purchasesdt.warehouse_code, PurchaseDt_Quantity, ".
				  	   " ifNull(if(upper($cur)='".$Base1."',PurchaseDt_PriceBase1,if(upper($cur)='".$Base2."',PurchaseDt_PriceBase2, PurchaseDt_Price)),0) PurchaseDtPrice, ".
					   " ifNull(PurchaseDt_DiscountPer,0) PurchaseDtDiscountPer, ".
					   " ((ifNull(PurchaseDt_Quantity,0) * ifNull(if(upper($cur)='".$Base1."',PurchaseDt_PriceBase1,if(upper($cur)='".$Base2."',PurchaseDt_PriceBase2,PurchaseDt_Price)),0)) - ".
					   " ((ifNull(PurchaseDt_Quantity,0) * ifNull(if(upper($cur)='".$Base1."',PurchaseDt_PriceBase1,if(upper($cur)='".$Base2."',PurchaseDt_PriceBase2,PurchaseDt_Price)),0) * ifNull(PurchaseDt_DiscountPer,0)) / 100) ) total ".
					   " from purchasesdt ".
					   " left join items on purchasesdt.item_code = items.item_code ".
					   " where purchasesdt.Purchase_id = ". $rowpurchases[0];				
					
	//echo $querypurchasesdt;
	
	if (($grpproject == 1 && $grpsupplier == 0) && ($masterproject == '' || $masterproject!= $rowpurchases[6])) {
	  
	  echo "<tr>"; 
	  echo "<td colspan='10'><table class='group' width='100%' border='0' align='center' cellpadding='0' cellspacing='0'>";
	  
	  if (($cur != 'null') && ($masterproject != '' && $masterproject!= $rowpurchases[6]) && $purtype == 'PI'){
	    echo " <tr>";
	    echo "  <td width='77%' colspan='3' align='right'> Total by group $displaycur </td>";
	    echo "  <td width='15%' align='right'> ".number_format($SumGroupNetTot, $DecimalBase)." </td>";
	    echo "  <td width='8%'  align='right'>&nbsp;</td>";
	    echo " </tr>";
	   
		$SumGroupNetTot = 0;
	  }	
	  
	  echo "<tr>";
	  echo "   <td width='15%'> Group by Project</td>";
	  echo "   <td width='10%'>". $rowpurchases[6] ."</td>";
	  echo "   <td width='52%'>". $rowpurchases[7] ."</td>";
	  echo "   <td width='23%' colspan='2'>&nbsp;</td>";
	  echo "</tr>"; 
	  
	  
	  echo "<tr><td colspan='5'>&nbsp;</td></tr></table></td></tr>";
	  $masterproject= $rowpurchases[6];
	
	}
	
	if (($grpsupplier == 1 ) && ($mastersupplier == '' || $mastersupplier!= $rowpurchases[4])) {
	  
	  echo "<tr>"; 
	  echo "<td colspan='10'><table class='group' width='100%' border='0' align='center' cellpadding='0' cellspacing='0'>";
	  
	  if (($cur != 'null') && ($mastersupplier != '' && $mastersupplier!= $rowpurchases[4]) && $purtype == 'PI'){

        echo " <tr>";
	    echo "  <td width='77%' colspan='3' align='right'> Total by group $displaycur </td>";
	    echo "  <td width='15%' align='right'> ".number_format($SumGroupNetTot, $DecimalBase)." </td>";
	    echo "  <td width='8%'  align='right'>&nbsp;</td>";
	    echo " </tr>";
		
		$SumGroupNetTot = 0;
	  }	
	  
	  echo "<tr>";
	  echo "   <td width='15%'> Group by Supplier</td>";
	  echo "   <td width='10%'>". $rowpurchases[4] ."</td>";
	  echo "   <td width='52%'>". $rowpurchases[5] ."</td>";
	  echo "   <td width='23%' colspan='2'>&nbsp;</td>";
	  echo "</tr>"; 
	  
	  
	  echo "<tr><td colspan='5'>&nbsp;</td></tr></table></td></tr>";
	  $mastersupplier= $rowpurchases[4];
	
	}
	
	if ($showdetail == 1){
		echo "<tr>";
	}else{
		echo"<tr $color>";
	}
	echo "<td align='center'>". $rowpurchases[1] ."</td>";
	echo "<td align='left'  >". $rowpurchases[2] ."</td>";
    echo "<td align='center'>". $rowpurchases[3] ."</td>"; 	
	echo "<td align='center'>". $rowpurchases[4] ."</td>";
	echo "<td align='left'  >". $rowpurchases[5] ."</td>";
	echo "<td align='left'  >". $rowpurchases[13] ."</td>";
	echo "<td align='center'>". $rowpurchases[6] ."</td>";
	if ($showProjCostName == 1)	
		echo "<td align='center'>". $rowpurchases[7] ."</td>";
	echo "<td align='center'>". $rowpurchases[8] ."</td>";
	if ($showProjCostName == 1)	
		echo "<td align='center'>". $rowpurchases[14] ."</td>";
	
	if ($purtype == 'PI') {
		echo "<td align='center'>". $rowpurchases[9] ."</td>";
		echo "<td align='right' >". number_format($rowpurchases[10], $DecimalBase) ."</td>";
		echo "<td align='right' >". number_format($rowpurchases[11], $DecimalBase) ."</td>";
	} else {
		echo "<td align='center' colspan='3'>&nbsp;</td>";
	}

	echo "</tr>";
	
	if ($rowpurchases[3] == 'PI' || $showboth == 0) {
	  $SumNetTot       = $SumNetTot  + $rowpurchases[10];
	  $SumGroupNetTot  = $SumGroupNetTot  + $rowpurchases[10];
	} else {
	  $SumNetTot       = $SumNetTot  - $rowpurchases[10];
	  $SumGroupNetTot  = $SumGroupNetTot  - $rowpurchases[10];
	}

	if ($showdetail == 1) {
	  $resultpurchasesdt=mysql_query($querypurchasesdt,$connection) or die(mysql_error());
	  $col1=0;
      while($rowpurchasesdt=mysql_fetch_row($resultpurchasesdt)){
		
		if($col1%2==0){
			$color1 = "style='background-color:".$report_color1."'";
		}else{
			$color1 = "style='background-color:".$report_color2."'";
		}
		
		echo "<tr $color1>";
		echo "  <td colspan='10'><table class='detail' width='100%' border='0' align='center' cellpadding='0' cellspacing='0' style='table-layout:fixed;' >
				<col width=20>
				<col width=80>
				<col width=330>
				<col width=100>
				<col width=50>
				<col width=50>
				<col width=40>
				<col width=80>
				<col width=20>";
		echo " <tr>";
		echo "  <td>&nbsp;</td>";
		echo "  <td style='border-right:1px solid black;'>". $rowpurchasesdt[0] ."</td>";
		echo "  <td style='border-right:1px solid black; padding-left:2px;'>". $rowpurchasesdt[1] ."</td>";
		echo "  <td style='border-right:1px solid black; padding-left:2px;'>". $rowpurchasesdt[2] ."</td>";
		echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowpurchasesdt[3], $DecimalBase) ."</td>";
			
		if ($purtype == 'PI') {
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowpurchasesdt[4], $DecimalBase) ."</td>";
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowpurchasesdt[5], $DecimalBase) ."%</td>";
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowpurchasesdt[6], $DecimalBase) ."</td>";
		} else {
			echo "  <td colspan='3'>&nbsp;</td>";
		}
		echo "  <td>&nbsp;</td>";
		echo " </tr></table></td></tr>";
		$col1+=1;
   	  }
	}	
	
	echo "<tr height='8' >";
	echo "  <td colspan='13'><hr width='100%' color='black' size='1'></td>";
	echo "</tr>";
	$col+=1;
}

if (($grpproject == 1  && $grpsupplier == 0) && ($masterproject == '' || $masterproject!= $rowpurchases[6])) {
	  
    echo "<tr>"; 
    echo "<td colspan='10'><table class='group' width='100%' border='0' align='center' >";
	  
	if (($cur != 'null') && ($masterproject != '' ) && $purtype == 'PI'){
	   echo "   <tr>";
	   echo "  <td width='77%' align='right'> Total by group $displaycur </td>";
	   echo "  <td width='15%' align='right'> ".number_format($SumGroupNetTot, $DecimalBase)." </td>";
	   echo "  <td width='8%'  align='right'>&nbsp;</td>";
	   echo "   </tr>";
		
	   $SumGroupNetTot = 0;
	}	
	  
  
     echo "<tr><td colspan='3'>&nbsp;</td></tr></table></td></tr>";
	
}
if (($grpsupplier == 1) && ($mastersupplier == '' || $mastersupplier!= $rowpurchases[4])) {
	  
    echo "<tr>"; 
    echo "<td colspan='10'><table class='group' width='100%' border='0' align='center' >";
	  
	if (($cur != 'null') && ($mastersupplier != '' ) && $purtype == 'PI'){
	   echo "   <tr>";
	   echo "  <td width='77%' align='right'> Total by group $displaycur </td>";
	   echo "  <td width='15%' align='right'> ".number_format($SumGroupNetTot, $DecimalBase)." </td>";
	   echo "  <td width='8%'  align='right'>&nbsp;</td>";
	   echo "   </tr>";
		
	   $SumGroupNetTot = 0;
	}	
	    
     echo "<tr><td colspan='3'>&nbsp;</td></tr></table></td></tr>";
	
}
	
if ($cur != 'null' && $purtype == 'PI'){
	if ($showProjCostName == 1)	
		$colspn = 13;
	else $colspn = 11;
	echo "<tr>";
	echo "  <td colspan='$colspn'><table class='totals' width='100%' border='0' align='center' ><tr> ";
	echo "  <td width='77%' align='right'> Total $displaycur </td>";
	echo "  <td width='15%' align='right'> ".number_format($SumNetTot, $DecimalBase)." </td>";
	echo "  <td width='8%' align='right'>&nbsp;</td>";
	echo " </tr></table></td>";
	echo "</tr>";
}

echo "</table>";

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


if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>