<?php

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


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

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

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


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



if($adtype==1)	
	$ReportTitle = 'List of Adjustment\In\Out';
else if($adtype==2)		
	$ReportTitle = 'List of Stock In';
else if($adtype==3)		
	$ReportTitle = 'List of Stock Out';
else if($adtype==4)		
	$ReportTitle = 'List of Adjustment';
		

if($adtype==2)
	$adjustmenttype='i';
else if($adtype==3)
	$adjustmenttype='o';
else if($adtype==4)
	$adjustmenttype='a';
else $adjustmenttype='';

if ($availgroupby == '2'){
	$dispgroupby = 'Project';
	$groupby 	 = 'adjustmentdt.project_code';
	$groupbyname = 'project_name';
} 
else if ($availgroupby == '3'){
	$dispgroupby = 'Costcenter';
	$groupby 	 = 'adjustmentdt.Costcent_code';
	$groupbyname = 'Costcent_name';
}
else if ($availgroupby == '4'){
	$dispgroupby = 'Warehouse';
	$groupby 	 = 'adjustmentdt.Warehouse_Code';
	$groupbyname = 'Warehouse_name';
}
else if ($availgroupby == '5'){
	$dispgroupby = 'Group';
	$groupby 	 = 'items.group_code';
	$groupbyname = 'group_name';
}
else if ($availgroupby == '6'){
	$dispgroupby = 'Subgroup';
	$groupby 	 = 'items.subgroup_code';
	$groupbyname = 'subgroup_name';
}
else if ($availgroupby == '7'){
	$dispgroupby = 'Category';
	$groupby 	 = 'items.category_code';
	$groupbyname = 'category_name';
}
else if ($availgroupby == '10'){
	$dispgroupby = 'Supplier';
	$groupby 	 = 'items.ledger_number';
	$groupbyname = 'ledger_name';
}
else if ($availgroupby == '11'){
	$dispgroupby = 'Item Code';
	$groupby 	 = 'adjustmentdt.item_code';
	$groupbyname = 'item_description';
}


$SelectedOptions = "Filter by: \n";

if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($curr != '')    	  $SelectedOptions .= "-> Currency: ". $curr ."\n";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."\n";

if($Module_Heatno==1 and $vShowHeatno==1)
if ($fromheatno != '')      $SelectedOptions .= "-> HeatNo From: ". $fromheatno . "  To: ". $toheatno ."\n";

if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."\n";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."\n";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\n";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "\n";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "\n";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier . "\n";

if ($fromledger != ''){
if ($dbcd == '1') 
	$SelectedOptions .= "-> Credit Accounts From: ". $fromledger . "  To: ". $toledger . "\n";
else if ($dbcd == '2') 
	$SelectedOptions .= "-> Debit Accounts From: ". $fromledger . "  To: ". $toledger . "\n";
else if ($dbcd == '3') 	
	$SelectedOptions .= "-> Credit/Debit Accounts From: ". $fromledger . "  To: ". $toledger . "\n";
}
if ($dispgroupby != '') 
	$SelectedOptions .= "-> Group by: $dispgroupby \n";
	

$RowNum    = 10;
$ColumnNum = 0;	   

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

$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');

$queryadjustment = "select Adjustment_Reference, date_format(adjustment.Adjustment_Date, '%d-%m-%Y'), Adjustment_Remak,
					round(if('$curr'='$base1', Adjustment_AmountBase1, Adjustment_AmountBase2),$DecimalBase),
					adjustmentdt.item_code, item_description, adjustmentdt.Warehouse_Code, AdjustmentDt_Quantity, round(if('$curr'='$base1', AdjustmentDt_PriceBase1,AdjustmentDt_PriceBase2),$DecimalBase), ";
if ($availgroupby == '' || $availgroupby == '1')
	$queryadjustment .= " '' as groupby, '' as groupby ";
else 
	$queryadjustment .= " $groupby as groupby, $groupbyname as groupby ";
$queryadjustment .= "  , adjustment.project_code, adjustment.Costcent_code, adjustmentdt.project_code, adjustmentdt.Costcent_code ";
$queryadjustment .= " from adjustmentdt
					inner join adjustment on adjustment.Adjustment_Id=adjustmentdt.Adjustment_Id
					inner join items on items.item_code = adjustmentdt.item_code
					left join grouping on grouping.group_code = items.group_code
					left join subgroup on subgroup.subgroup_code = items.subgroup_code
					left join category on category.category_code = items.category_code
					left join warehouse on warehouse.Warehouse_Code = adjustmentdt.Warehouse_Code
					left join suppliers on suppliers.Ledger_Number = items.Ledger_Number
					left join project on project.project_code = adjustmentdt.project_code
					left join costcent on costcent.costcent_code = adjustmentdt.Costcent_code
					where (date_format(adjustment.Adjustment_Date, '%Y-%m-%d') between $fromdate and $todate) 
					and if ('$fromitem'='', 1=1, adjustmentdt.item_Code between '$fromitem' and '$toitem')
					and if('$fromgroup'='', 1=1, items.group_code between '$fromgroup' and '$togroup')
					and if('$fromsubgroup'='', 1=1, items.subgroup_code between '$fromsubgroup' and '$tosubgroup')
					and if('$fromcategory'='', 1=1, items.category_code between '$fromcategory' and '$tocategory')
					and if('$fromsupplier'='', 1=1, items.Ledger_Number between '$fromsupplier' and '$tosupplier') 
					and if('$fromproject'='', 1=1, adjustmentdt.project_code between '$fromproject' and '$toproject') 
					and if('$fromcostcent'='', 1=1, adjustmentdt.Costcent_code between '$fromcostcent' and '$tocostcent') 
					and if('$fromwarehouse'='', 1=1, adjustmentdt.Warehouse_Code between '$fromwarehouse' and '$towarehouse') 
					and Adjustment.Adjustment_EntryType = if('".$adjustmenttype."'='',Adjustment.Adjustment_EntryType,'".$adjustmenttype."') ";
if ($dbcd == '1') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, adjustmentdt.Invcreditaccount between '$fromledger' and 'toledger' ) ";
else if ($dbcd == '2') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, adjustmentdt.InvDEBITaccount between '$fromledger' and 'toledger' ) ";
else if ($dbcd == '3') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, (adjustmentdt.Invcreditaccount between '$fromledger' and 'toledger') or (adjustmentdt.InvDEBITaccount between '$fromledger' and 'toledger') ) ";
	
if ($whexist>0)	{
	$queryadjustment .= " and (adjustmentdt.Warehouse_Code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  )";
}
if ($availgroupby == '' || $availgroupby == '1')				
	$queryadjustment .=	" order by Adjustment_Reference ";
else 
	$queryadjustment .=	" order by $groupby ";
echo $queryadjustment;
$resadjustment = mysql_query($queryadjustment, $connection);

$tempref = '';
$tempgrpby = '**';

$ct = 0;		 		
$totalAmount = 0;
$totalQtyByGrp = 0;
$totalAmntByGrp = 0;



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



?>