<?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 Most Selling Items </TITLE>

<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: 14px; 
                  font-family:Times-New-Roman;
                 }
				 
table.master1 th { font-size: 14px; 
                   font-weight: bold;
                   font-family:Times-New-Roman;
                 }				 
table.master1 td { font-size: 13px; 
				   font-weight: bold;
                   font-family:Times-New-Roman;
                 }	
				 
table.group td { font-size: 14px; 
                 font-weight: bold;
				 font-style: italic;
                 font-family:Times-New-Roman;
               }	

table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }				 			   
table.detail td { font-size: 12px; 
				  font-weight: normal;
                  font-family:Times-New-Roman;
                }		
table.detail td.sr  { font-size: 11px; 
					  font-style: italic; 
                      font-family:Times-New-Roman;
                    }				
				 
.totals { font-size: 12px; 
          font-weight: bold;
          font-family:Times-New-Roman;
        }					  
				 
h4 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 2px;
   }
h5 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }   
   
</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);
}

$querylog="SELECT print as printaccess, ifnull(Fi_POS,0) as Fi_POS 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);
}



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

$querymodule  = "select ifnull(Module_Delivery,0) as Module_Delivery from module ";
$resultmodule = mysql_query($querymodule,$connection) or die(mysql_error());
while($rows = mysql_fetch_array($resultmodule)){
  extract($rows);
}

$queryCashRegister = " Select ifNull(CashRegisterTTC,0) As CashRegisterTTC, ifNull(retailserialno,0) As RetailSerialNo  ".
					 " From CashRegister Inner Join ".$_SESSION['dbLabel'].".logindt on logindt.Cash_Register = CashRegister.CashRegister_Name ".
					 " Where logindt.User_Account = '".$_SESSION['useraccount']."' ";
$resultCashRegister=mysql_query($queryCashRegister,$connection) or die(mysql_error());
while($rowCashRegister=mysql_fetch_array($resultCashRegister)){
	extract($rowCashRegister);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	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);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$_SESSION['dbLabel'].".loginstk ".
		 "                                          where user_account='".$_SESSION['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);

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

$RepTitle = 'List of Most Selling Items';

$dispfromgroup = $fromgroup;
$disptogroup = $togroup;
$dispfromsubgroup = $fromsubgroup;
$disptosubgroup = $tosubgroup;
$dispfromcategory = $fromcategory;
$disptocategory = $tocategory;

if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';

print <<< HERE
<table width="100%" border="0" align="center">
<tr><td>
<table width='840px' border='0' align='left' style='table-layout:fixed'>
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>$RepTitle</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];

$dispfromdate = $fromdate;
$disptodate   = $todate;
$dispfromitem = $fromitem;
$disptoitem = $toitem;

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

if (empty($fromgroup))  $fromgroup = "null";
else   $fromgroup = "'".$fromgroup."'";    
if (empty($togroup))  $togroup = "null";
else  $togroup = "'".$togroup."'";   

if (empty($fromsubgroup))  $fromsubgroup = "null";
else  $fromsubgroup = "'".$fromsubgroup."'";    
if (empty($tosubgroup))  $tosubgroup = "null";
else  $tosubgroup = "'".$tosubgroup."'";      


if (empty($fromcategory))  $fromcategory = "null";
else  $fromcategory = "'".$fromcategory."'";    
if (empty($tocategory))  $tocategory = "null";
else  $tocategory = "'".$tocategory."'";      


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

   
   
   
if (empty($fromdate))  $fromdate = "null";
else  $fromdate = "'".$fromdate." 00:00:00'"; 
if (empty($todate))  $todate = "null";
else  $todate = "'".$todate." 23:59:59 '";


print <<<HERE
</font></td>
</tr>
<tr height='5'><td colspan=4><h5></td></tr>
</table>
</td></tr>

<tr><td>
<table class='params' width='840px' border='0' align='left' style='table-layout:fixed' >
<tr>
  <td width="12%"><b>Date From/To:</b></td>
  <td width="18%">$dispfromdate - $disptodate </td>
  <td width="10%" valign='top'><b>Item From/To</b></td>
  <td width="60%" colspan=4>$dispfromitem - $disptoitem </td>
</tr>
<tr>
  <td width="15%" valign='top'><b>Group From/To</b></td>
  <td width="35%">$dispfromgroup - $disptogroup </td>
  <td width="15%" colspan=2 valign='top'><b>SubGroup From/To</b></td>
  <td width="35%">$dispfromsubgroup - $disptosubgroup </td>
</tr>  

<tr>
  <td width="15%" valign='top'><b>Category From/To</b></td>
  <td width="35%">$dispfromcategory - $disptocategory </td> 
</tr>  
<tr height='5'><td colspan=8><h4></td></tr>
</table>
</td></tr>

<tr><td>
HERE;


echo "<table width='840' border='0' align='left' class='master' style='table-layout:fixed'>
			  <col width=120>
			  <col width=250>
			  <col width=90>
			  <col width=100>
			  <col width=100>
			  <col width=100>
			  <col width=80>
			  <tr>
				<th >Item Code </th>
				<th >Description</th>
				<th >Quantity</th>
				<th >Last Sales Date</th>
				<th >Last Pur. Date</th>
				<th ></th>
				<th ></th>
			  </tr>
			  <tr height='5'><td colspan=7><h4></td></tr>";

			  
$query = " select item_code, item_description, totalQty from ( ";

if ($Fi_POS == 1){
	$module = 1;
	$query .=  " select retaildt.item_code as item_code, item_description, sum(SaleDt_Quantity) as totalQty
			from retaildt inner join items on items.item_code = retaildt.item_code ".
		  "	where Sale_Date between $fromdate and $todate ".
		  " and Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.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')) ".
		  " And ifNull(Items.category_code,'-1') between ifNull(".$fromcategory.",ifNull(Items.category_code,'-1')) and ifNull(". $tocategory.",ifNull(Items.category_code,'-1')) ".
		  "	group by retaildt.item_code ";
}else {
	if ($Module_Delivery == 0){ 
	$module = 2;
		$query .=  " select deliverydt.item_code as item_code, item_description, sum(DeliveryDt_Quantity) as totalQty
					from deliverydt inner join items on items.item_code = deliverydt.item_code ".
				  "	where Delivery_Date between $fromdate and $todate ".
				  " and Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.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')) ".
		  		  " And ifNull(Items.category_code,'-1') between ifNull(".$fromcategory.",ifNull(Items.category_code,'-1')) and ifNull(". $tocategory.",ifNull(Items.category_code,'-1')) ".

				  "	group by deliverydt.item_code ";
	}else if ($Module_Delivery == 1){
		$module = 3;
		$query .=  " select salesdt.item_code as item_code, item_description, sum(SaleDt_Quantity) as totalQty
					from salesdt inner join items on items.item_code = salesdt.item_code ".
				  "	where Sale_Date between $fromdate and $todate ".
				  " and Items.Item_Code Between ifNull(".$fromitem.", Items.Item_Code) and ifNull(". $toitem.", Items.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')) ".
				  " And ifNull(Items.category_code,'-1') between ifNull(".$fromcategory.",ifNull(Items.category_code,'-1')) and ifNull(". $tocategory.",ifNull(Items.category_code,'-1')) ".
				  "	group by salesdt.item_code ";
	}
}
$query .= " ) as subQ
			order by totalQty desc limit $nbritems ";

//echo $query;

$result=mysql_query($query,$connection) or die(mysql_error());

$TotalInvoices = mysql_num_rows($result);	

while($rows=mysql_fetch_row($result)){
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
echo "<tr $color>
		<td align='left'>$rows[0]</td>
		<td align='left'>$rows[1]</td>
		<td align='left'>$rows[2]</td>
		<td align='left'>".queryDate('Sales',$module,$rows[0],$connection)."</td>
		<td align='left'>".queryDate('Purchase',$module,$rows[0],$connection)."</td>

	</tr>";
}

echo"<tr height='5'><td colspan=7><h4></td></tr>";

echo "</table></td></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;
}

function explodewordtime($date)
{

$trimtime = explode(' ',$date);
$date = $trimtime[0];
$time = $trimtime[1];

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim." ".$time;
}

// lastpurchse_date and last_selling_Date
function queryDate($Trtype,$module,$item_code,$connection){
	
	if($Trtype == 'Purchase')
		$queryDate= "select date_format(max(purchase_date),'%d/%m/%Y') from purchasesdt where item_code = '$item_code';";
	if($Trtype == 'Sales' && $module==2)
		$queryDate= "select date_format(max(delivery_date),'%d/%m/%Y') from deliverydt where item_code = '$item_code';";
	else if($Trtype == 'Sales' && $module==3)
		$queryDate= "select date_format(max(sale_date),'%d/%m/%Y') from salesdt where item_code = '$item_code';";
	else if($Trtype == 'Sales' && $module==1)
		$queryDate= "select date_format(max(sale_date),'%d/%m/%Y') from retaildt where item_code = '$item_code';";
	//echo "<BR>".$queryDate;
	$resDate = mysql_query($queryDate,$connection);
	$rowDate = mysql_fetch_array($resDate);
	return $rowDate[0];
}


if ($printaccess==1 && $_GET['print'] == 1)
print <<< HERE

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

?>