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

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

if($export ==1){
	$filename = "Rep_SalesProductionReport" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 

$top='border-top:1 px solid';
$bottom='border-top:1 px solid';
$left='border-top:1 px solid';
$right='border-top:1 px solid';

print <<< HERE
<HTML>
<HEAD>
<TITLE> Inventory Comparison Report </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.master tr.group { font-size: 14px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }					 
				 
table.master td.sr  { font-size: 10px; 
					  font-style: italic; 
                      font-family:Times-New-Roman;
					   
                    }	
					
table.group td { font-size: 12px; 
                 font-weight: bold;
				 font-style: italic;
                 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']);
set_time_limit(0);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");

$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['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>';

$querylog="select fi_showcostprice,FI_ItemSelling1,FI_ItemSelling2,FI_ItemSelling3,FI_ItemSelling4,FI_ItemSelling5,FI_ItemSelling6 from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
  extract($rows);
}


$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, ifNull(InventoryRMaskqty,0) As InventoryRMaskqty, 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);
}

$querysalessetup="select SalesTTC from salessetup";
$resultsalessetup=mysql_query($querysalessetup,$connection) or die(mysql_error());
while($rowssalessetup=mysql_fetch_array($resultsalessetup)){
  extract($rowssalessetup);
}

$queryPurchasesetup="select PurchaseTTC from PurchaseSetup";
$resultPurchasesetup=mysql_query($queryPurchasesetup,$connection) or die(mysql_error());
while($rowsPurchasesetup=mysql_fetch_array($resultPurchasesetup)){
  extract($rowsPurchasesetup);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery, ifNull(Module_Grn,0) As Module_Grn From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['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);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
		 "                                          where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist  = mysql_num_rows($resultgroup);

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


$ReportTitle = 'Sales Production Report';


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>$ReportTitle</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];

if ($curr == ''){
   $curr='';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}  

//If Option Daily, and Days differnce between Start and End dates > 30
//Then Set the EndDate equal to StartDate plus 30 days.
if ($period == 1 ) {
	$Days = dateDiff(explodeword($fromdate), explodeword($todate));
	if ($Days >= 30) $todate = addDays(explodeword($fromdate), 29, 'd/m/Y'); 
}	


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."<br>";	
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."<br>";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier ."<br>";
if ($fromsalesman != '')  $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."<br>";
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."<br>";
if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."<br>";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."<br>";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."<br>";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "<br>";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "<br>";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "<br>";
if ($frombustype != '')   $SelectedOptions .= "-> Client Business Type From: ". $frombustype . "  To: ". $tobustype ."<br>";


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

$vMonthFrom = intval(substr($fromdate, 5, 2));
$vMonthTo   = intval(substr($todate, 5, 2));
$vYearFrom  = substr($fromdate, 0, 4);
$vYearTo    = substr($todate, 0, 4);    

$fromitem      = GetSQLValueString($fromitem, 'text');
$toitem        = GetSQLValueString($toitem, 'text');
$fromproject   = GetSQLValueString($fromproject, 'text');
$toproject     = GetSQLValueString($toproject, 'text');
$fromcostcent  = GetSQLValueString($fromcostcent, 'text');
$tocostcent    = GetSQLValueString($tocostcent, 'text');
$fromgroup     = GetSQLValueString($fromgroup, 'text');
$togroup       = GetSQLValueString($togroup, 'text');
$fromsubgroup  = GetSQLValueString($fromsubgroup, 'text');
$tosubgroup    = GetSQLValueString($tosubgroup, 'text');
$fromcategory  = GetSQLValueString($fromcategory, 'text');
$tocategory    = GetSQLValueString($tocategory, 'text');
$fromwarehouse = GetSQLValueString($fromwarehouse, 'text');
$towarehouse   = GetSQLValueString($towarehouse, 'text');
//$fromdate	   = GetSQLValueString($fromdate, 'date');
//$todate        = GetSQLValueString($todate, 'date');   
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
$fromsupplier  = GetSQLValueString($fromsupplier, 'text');
$tosupplier    = GetSQLValueString($tosupplier, 'text');
$fromsalesman  = GetSQLValueString($fromsalesman, 'text');
$tosalesman    = GetSQLValueString($tosalesman, 'text'); 
$frombustype   = GetSQLValueString($frombustype, 'text');
$tobustype     = GetSQLValueString($tobustype, 'text'); 

  
if (empty($transtype)) 	  $transtype = 'SI';	
if (empty($period)) 	  $period = 2;	
if (empty($showdetail))   $showdetail = 0;
if (empty($prtprice)) 	  $prtprice = 0;
if (empty($prtcost)) 	  $prtcost = 0;
if (empty($prtamt)) 	  $prtamt = 0;
if (empty($prtqty))	 	  $prtqty = 0;
if (empty($prtqtyexist))  $prtqtyexist = 0;
if (empty($prtqtySO))	  $prtqtySO = 0;
if (empty($prtqtyPO))	  $prtqtyPO = 0;
if (empty($prtavg))	 	  $prtavg = 0;
if (empty($prtshortage))  $prtshortage = 0;
if (empty($showallitems)) $showallitems = 0;
if (empty($prtgroup)) 	  $prtgroup = 0;
if (empty($prtsubgroup))  $prtsubgroup = 0;
if (empty($prtcategory))  $prtcategory = 0;
if (empty($prtcolor)) 	  $prtcolor = 0;
if (empty($prtsize))	  $prtsize = 0;
if (empty($prtcharges))	  $prtcharges = 0;
if(empty($promprint))     $promprint = 3;   


$MasterByLabel = '';
if ($groupby == 2 )       $MasterByLabel  = 'Group';
else if ($groupby == 3 )  $MasterByLabel  = 'SubGroup';
else if ($groupby == 4 )  $MasterByLabel  = 'Category';
else if ($groupby == 5 )  $MasterByLabel  = 'Warehouse';
else if ($groupby == 6 )  $MasterByLabel  = 'Project';
else if ($groupby == 7 )  $MasterByLabel  = 'CostCenter';
else if ($groupby == 8 )  $MasterByLabel  = 'Client';
else if ($groupby == 9 )  $MasterByLabel  = 'Supplier';
else if ($groupby == 10 ) $MasterByLabel  = 'Client Group';

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" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr height='5'><td><h5></td></tr>
</table>

HERE;
if($qtyamount==1 or $qtyamount==2)
	$colspan=1;
else $colspan=2;

echo "<table width='1500' border='1' class='master' align='left' cellpadding='0' cellspacing='0'>";



$query='';
$query1='';
$query1 = $query1." select distinct date_format(Transaction_Date,'%b-%Y')".
			" From stktransaction Stk,Items  ".
			" where (Transaction_Type = 'PD' or Transaction_Type = 'SI') ".
			" And items.item_code=Stk.item_code ".
			" And Date_format(Stk.Transaction_Date,'%Y-%m-%d') between ifNull('". $fromdate."',date_format(Stk.Transaction_Date,'%Y-%m-%d')) and ifNull('". $todate."',date_format(Stk.Transaction_Date,'%Y-%m-%d') )  ".
			" And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
			" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
			" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
			" group by Items.item_code,date_format(Transaction_Date,'%Y%m') ".
			" order by Items.item_code,date_format(Transaction_Date,'%Y-%m'),Transaction_Type ";
			
$query = $query." select Stk.item_code,Items.Item_Description,sum(ifnull(QuantitySold,0)),sum(ifnull(QuantityProduceIn,0)),date_format(Transaction_Date,'%b-%Y') ".
			",sum(ifnull(QuantitySold,0) * ifnull(pricebase1,0)),sum(ifnull(QuantityProduceIn,0) * ifnull(pricebase1,0)),Transaction_Type".
			" From stktransaction Stk,Items  ".
			" where (Transaction_Type = 'PD' or Transaction_Type = 'SI') ".
			" And items.item_code=Stk.item_code ".
			" And Date_format(Stk.Transaction_Date,'%Y-%m-%d') between ifNull('". $fromdate."',date_format(Stk.Transaction_Date,'%Y-%m-%d')) and ifNull('". $todate."',date_format(Stk.Transaction_Date,'%Y-%m-%d') ) ".
			" And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
			" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
			" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
			" group by Items.item_code,date_format(Transaction_Date,'%Y%m') ".
			" order by Items.item_code,date_format(Transaction_Date,'%Y-%m'),Transaction_Type ";


$query4 = $query4." select Distinct Stk.item_code,Items.Item_description ".
			" From stktransaction Stk,Items  ".
			" where (Transaction_Type = 'PD' or Transaction_Type = 'SI') ".
			" And items.item_code=Stk.item_code ".
			" And Date_format(Stk.Transaction_Date,'%Y-%m-%d') between ifNull('". $fromdate."',date_format(Stk.Transaction_Date,'%Y-%m-%d')) and ifNull('". $todate."',date_format(Stk.Transaction_Date,'%Y-%m-%d') ) ".
			" And Stk.item_code between ifNull(".$fromitem.",Stk.item_code) and ifNull(". $toitem.",Stk.item_code) ".
			" And ifNull(Items.Group_Code,'-1') between ifNull(". $fromgroup .",ifNull(Items.Group_Code,'-1')) and ifNull(". $togroup.",ifNull(Items.Group_Code,'-1'))  ".
			" And ifNull(Items.SubGroup_Code,'-1') between ifNull(". $fromsubgroup .",ifNull(Items.SubGroup_Code,'-1')) and ifNull(". $tosubgroup.",ifNull(Items.SubGroup_Code,'-1'))  ".
			" group by Items.item_code,date_format(Transaction_Date,'%Y%m') ".
			" order by Items.item_code,date_format(Transaction_Date,'%Y-%m'),Transaction_Type ";




$result3=mysql_query($query,$connection) or die(mysql_error());
$result=mysql_query($query,$connection) or die(mysql_error());
$result1=mysql_query($query1,$connection) or die(mysql_error());
//$result2=mysql_query($query1,$connection) or die(mysql_error());
$result4=mysql_query($query4,$connection) or die(mysql_error());


echo"<tr>";
echo"<th></th>";
echo"<th></th>";
$j=0;
$colspanmonth=$colspan*2;

while($rows=mysql_fetch_row($result1)){

echo"<th colspan=".$colspanmonth.">".$rows[0]."</th>";
$arr_m[$j]=$rows[0];
$j+=1;
}

echo"</tr>";
echo"<tr>";
echo"<th></th>";
echo"<th></th>";

$ss=1;
while($ss<=$j){

	echo"<th colspan=$colspan>Sales</th>";
	echo"<th colspan=$colspan>Production</th>";

	
$ss+=1;
}
echo"<tr>";
echo"<th>Item Code</th>";
echo"<th>Item Desc.</th>";

$ss=1;
while($ss<=$j){

if($qtyamount==1 or $qtyamount==3)
	echo"<th>Qty.</th>";

if($qtyamount==2 or $qtyamount==3)
	echo"<th>Amt.</th>";
	
if($qtyamount==1 or $qtyamount==3)
	echo"<th>Qty.</th>";
if($qtyamount==2 or $qtyamount==3)
	echo"<th>Amt.</th>";

$ss+=1;
}
echo"</tr>";

while($rows=mysql_fetch_row($result)){
 //echo $rows[3]."<br>";
	//if($rows[7]=='SI'){
	$arr[$rows[0]][$rows[4]] = $rows[2];
	$arr1[$rows[0]][$rows[4]] = $rows[5];
	//}
	//else if($rows[7]=='PD'){
	$arrpd[$rows[0]][$rows[4]] = $rows[3];
	$arr1pd[$rows[0]][$rows[4]] = $rows[6];
	//}
	
}
 
$k=1;
while($rows=mysql_fetch_row($result4)){
 
 if($itemcode!=$rows[0] or $k==1)
	echo"<tr><td>$rows[0]</tD><td>$rows[1]</tD>";  

	
	$arrlength = count($arr_m);
 
	for($x = 0; $x < $arrlength; $x++) {
		
		if($qtyamount==1 or $qtyamount==3)
			echo"<td align=center>&nbsp;".$arr[$rows[0]][$arr_m[$x]]."</td>";
		if($qtyamount==2 or $qtyamount==3)	
			echo"<td align=center>&nbsp;".number_format($arr1[$rows[0]][$arr_m[$x]],$DecimalBase)."</td>";
		if($qtyamount==1 or $qtyamount==3)	 
			echo"<td align=center>&nbsp;".$arrpd[$rows[0]][$arr_m[$x]]."</td>";
			
		if($qtyamount==2 or $qtyamount==3)	
			echo"<td align=center>&nbsp;".number_format($arr1pd[$rows[0]][$arr_m[$x]],$DecimalBase)."</td>";
			 
		
		
	}
	

$itemcode=$rows[0];
$k+=1;
}





function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 


function getMonth($month){
     return date('M', mktime(0,0,0,$month,1));
}

function showMonthYear($month, $year){
	$vMonthYear = date('M', mktime(0,0,0,$month,1)).'-'.$year;
	
    return $vMonthYear;
}

function showDays($startdate, $numdays, $format='d-M-Y') {
	return date($format, strtotime($startdate) + $numdays*60*60*24);
}

function getMonthDisp($month){
     return date('F', mktime(0,0,0,$month,1));
}
function dateDiff($start, $end) {
  $start_ts = strtotime($start);
  $end_ts = strtotime($end);
  $diff = $end_ts - $start_ts;
  return round($diff / 86400);
}
function addDays($startdate, $numdays, $format='dMY') {
	return date($format, strtotime($startdate) + $numdays*60*60*24 + 10000);
}

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



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

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

?>