<?php

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

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

$detail = $_GET['details'];

if ($exp == 1) {
    $filename = "Rep_PendingDeliveryReport" . date('Ymdhis') . ".xls";
    header("Content-Disposition:attachement;filename=\"$filename\"");
    header("Content-Type:application/vnd.ms-excel");
}
$querysetup = " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, Setup_VatNo, " .
    " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage, " .
    " Company_Name as CompanyName, Company_NameF, company_memo, COMPANY_CITY, Company_Capital, Company_CapitalF,company_phone2,company_phone3,COMPANY_FAX " .
    " from setup ";
$resultsetup = mysql_query($querysetup, $connection) or die(mysql_error());
while ($rowsetup = mysql_fetch_array($resultsetup)) {
    extract($rowsetup);
}

$querylog = " select ifNull(login_belongstogrp,0) As FI_BelongsToGrp, ifNull(FI_FrmRestrictWarehouse,0) As FI_FrmRestrictWarehouse " .
    " 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);
}

//Read From Module Table
$querymodule = " Select ifNull(Module_Project,0) As Module_Project, ifNull(Module_Costcenter,0) As Module_CostCenter, " .
    " ifNull(Module_ProjectParent,0) As Module_ProjectParent, ifNull(Module_CostCenterParent,0) As Module_CostCenterParent " .
    " From Module";
$resultmodule = mysql_query($querymodule, $connection) or die(mysql_error());
while ($rowmodule = mysql_fetch_array($resultmodule)) {
    extract($rowmodule);
}


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

$MainCompanyName = explode('_', $_SESSION['dbName']);

$querylog = "SELECT print as printaccess 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);
}

$queryproject = " select distinct project.project_code, project_name " .
    " from project left join " . $_SESSION['dbLabel'] . ".loginstk on (($Module_ProjectParent=1 and project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%')) or ($Module_ProjectParent=0 and project.project_code = loginstk.project_code )) " .
    " where user_account='" . $_SESSION['useraccount'] . "'  and Upper(loginstk.company_name) = Upper('" . $_SESSION['dbName'] . "') " .
    " 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 (($Module_CostCenterParent=1 and costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%')) or ($Module_CostCenterParent=0 and costcent.costcent_code = loginstk.costcent_code )) " .
    " where user_account='" . $_SESSION['useraccount'] . "'  and Upper(loginstk.company_name) = Upper('" . $_SESSION['dbName'] . "') " .
    " 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 inner join " . $_SESSION['dbLabel'] . ".loginstk on warehouse.warehouse_code = loginstk.warehouse_code " .
    " where user_account='" . $_SESSION['useraccount'] . "'  and Upper(loginstk.company_name) = Upper('" . $_SESSION['dbName'] . "') " .
    " and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '')  " .
    " order by warehouse.warehouse_code";
$whresult = mysql_query($querywh, $connection) or die(mysql_error());
$whexist  = mysql_num_rows($whresult);

$queryledger = " select distinct ledger_number, ledger_include " .
    " from " . $_SESSION['dbLabel'] . ".ledgerstk  " .
    " where user_account='" . $_SESSION['useraccount'] . "' " .
    " and (ledger_number is not null and ledger_number != '') " .
    " order by ledger_number";
$ledgerresult = mysql_query($queryledger, $connection) or die(mysql_error());
$ledgerexist  = mysql_num_rows($ledgerresult);
$rows = mysql_fetch_array($ledgerresult);
$ledgerinclude = $rows[1];

$queryperiod = " Select ifNull(LockViewPeriod,0) As LockViewPeriod, ViewPeriodDate, ifNull(ViewLockDaily,0) As ViewLockDaily, ifnull(ViewNumberOfDays,0) as ViewNumberOfDays " .
    " From " . $_SESSION['dbLabel'] . ".ListUserDir  " .
    " Where Usercode='" . $_SESSION['useraccount'] . "' " .
    " And Upper(CompanyName) = Upper('" . $MainCompanyName[0] . "') " .
    " And Upper(Diryear) = Upper('" . $MainCompanyName[1] . "') ";

$resultperiod = mysql_query($queryperiod, $connection) or die(mysql_error());
$rowsperiod   = mysql_num_rows($resultperiod);

while ($rows = mysql_fetch_array($resultperiod)) {
    extract($rows);
}


if (!empty($fromdate))   $fromdate = explodeword($fromdate, 'F');
if (!empty($todate))     $todate = explodeword($todate, 'T');

$querysearch = " Select SQL_CALC_FOUND_ROWS Distinct Deliveries.Delivery_ID, Deliveries.Delivery_Reference, Date_Format(Deliveries.Delivery_Date,'%d-%m-%Y') As Delivery_Date, " .
    " Deliveries.Client_Code As ClientCode, Clients.Ledger_name As ClientName, Deliveries.Currency_code, " .
    " Project.Project_Name As ProjectName, Salesmen.Ledger_Name As SalesmanName, " .
    " Round((ifNull(Deliveries.Delivery_Taxable,0) + ifNull(Deliveries.Delivery_NoneTaxable,0) + ifNull(Deliveries.Delivery_VAT,0) - ifNull(Deliveries.Delivery_TransportAmount,0) - " .
    " ifNull(Deliveries.Delivery_DiscountAmount,0) + (ifNull(Deliveries.Delivery_Tax1,0) + ifNull(Deliveries.Delivery_Tax2,0) + ifNull(Deliveries.Delivery_Tax3,0) + ifNull(Deliveries.Delivery_Tax4,0) + ifNull(Deliveries.Delivery_Tax5,0))), " . $DecimalBase . ") As NetTotal,  " .
    " ifNull(Temp_Deliveries.Delivery_Id, -1) As TempDeliveryId, Deliveries.Sorder_Reference As Sorder_Reference, Q.QuantitySoldTemp " .
    " From Deliveries Left Join Clients on Deliveries.Client_Code = Clients.Ledger_Number " .
    " Left Join Project on Deliveries.Project_Code = Project.Project_Code " .
    " Left Join Salesmen on Deliveries.Salesman_Code = Salesmen.Ledger_Number " .
    " Left Join Temp_Deliveries on Temp_Deliveries.Delivery_Id = Deliveries.Delivery_Id " .
    " Inner Join ( Select Delivery_ID, Sum(QuantityDelivered - QuantitySold) as QuantityAvail, Sum(QuantitySoldTemp) as QuantitySoldTemp " .
    "			    From (           " .
    "                     Select DeliveryDt.Delivery_ID, DeliveryDt.Item_Code, " .
    "                     Sum(ifNull(SalesDt.SaleDt_Quantity,0) + ifNull(DR.DeliveryDt_Quantity,0)) As QuantitySold, " .
    "					   Sum(ifNull(Temp_SalesDt.SaleDt_Quantity,0)) As QuantitySoldTemp,  " .
    "                     ifNull(DeliveryDt.DeliveryDt_Quantity,0) As QuantityDelivered " .
    "             		   From DeliveryDt " .
    "         		       Left Join SalesDt on (SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_id And SalesDt.Journal_Type = 'SI' And SalesDt.Sale_Id != '" . $PSaId . "') " .
    "		    		   Left Join Temp_SalesDt on (Temp_SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_id And Temp_SalesDt.Journal_Type = 'SI') " .
    "       		       Left Outer Join DeliveryDt DR on DeliveryDt.DeliveryDt_ID = DR.RelatedDt_ID  and  DR.Journal_Type = 'DR' " .
    "	            	   Where ifNull(DeliveryDt.DeliveryDt_OldData,0) = 0  " .
    //"             		   And (('" . $PSaClientCode . "' != '' and DeliveryDt.Client_Code = '" . $PSaClientCode . "') or ('" . $PSaClientCode . "' = '' and 1 = 1)) " .
    //"             		   And (('" . $lstItems . "' != '' and DeliveryDt.Item_Code = '" . $lstItems . "') or ('" . $lstItems . "' = '' and 1 = 1)) " .
    "              	   Group by item_code,DeliveryDt.Delivery_ID, SalesDt.RelatedDt_ID, Temp_SalesDt.RelatedDt_ID " .
    "              	) as A Group By Delivery_ID " .
    "	                  Having QuantityAvail > 0 " .
    "	          ) As Q on Q.Delivery_ID = Deliveries.Delivery_ID ";

if ($projectexist >    0)
    $querysearch = $querysearch . " Inner join ( select distinct project_code from " . $_SESSION['dbLabel'] . ".loginstk " .
        "			   where loginstk.user_account='" . $_SESSION['useraccount'] . "' " .
        "             and (project_code is not null and project_code != '') " .
        "			 ) Q1 on (($Module_ProjectParent=1 and Deliveries.project_code like concat(Q1.project_code,'%')) or ($Module_ProjectParent=0 and Deliveries.project_code = Q1.project_code )) ";

if ($costcentexist > 0)
    $querysearch = $querysearch . " 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 (($Module_CostCenterParent=1 and Deliveries.costcent_code like concat(Q2.costcent_code,'%')) or ($Module_CostCenterParent=0 and Deliveries.costcent_code = Q2.costcent_code )) ";

if ($ledgerexist > 0)
    $querysearch = $querysearch . " Inner join ( select distinct ledger_number from " . $_SESSION['dbLabel'] . ".ledgerstk " .
        "			    where user_account='" . $_SESSION['useraccount'] . "' " .
        "              and (ledger_number is not null and ledger_number != '') " .
        "			 )  Q3 on (($ledgerinclude = 1 and Deliveries.Client_Code like concat(Q3.ledger_number,'%') ) or ($ledgerinclude = 0 and Deliveries.Client_Code Not like concat(Q3.ledger_number,'%') ) or Deliveries.Client_Code is null) ";

if ($whexist > 0 && $FI_FrmRestrictWarehouse == 1)
    $querysearch = $querysearch . " Inner join ( select distinct deliverydt.Delivery_Id " .
        " 				from deliverydt inner join " . $_SESSION['dbLabel'] . ".loginstk on deliverydt.warehouse_code = loginstk.warehouse_code " .
        " 				where loginstk.user_account='" . $_SESSION['useraccount'] . "' " .
        " 				and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '')  " .
        "			 ) Q4 on Deliveries.Delivery_Id = Q4.Delivery_Id ";

$querysearch = $querysearch .
    " where  Deliveries.Journal_Type = 'DI' " .
//    " And (($rowsperiod = 0 and 1 = 1) Or ($rowsperiod > 0 and (($LockViewPeriod = 1 and  Date_Format(Deliveries.Delivery_Date,'%Y-%m-%d') >= Date_Format(ifNull('" . $ViewPeriodDate . "',Deliveries.Delivery_Date),'%Y-%m-%d')) Or ($LockViewPeriod = 0 and 1=1 ))   " .
 //   "                                                      and (($ViewLockDaily = 1 and DateDiff(Date_Format(Now(),'%Y-%m-%d'),Date_Format(Deliveries.Delivery_Date,'%Y-%m-%d')) <= $ViewNumberOfDays) Or ($ViewLockDaily = 0 and  1 = 1 ))) ) " .
    " And ifNull(Deliveries.Delivery_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) And Deliveries.Delivery_Date between '$fromdate' and '$todate'   ";



/*if ($ledgerexist > 0)
  $querysearch= $querysearch." And (($ledgerinclude = 1 and Deliveries.Client_Code In (select distinct ledger_number from ".$_SESSION['dbLabel'].".ledgerstk where user_account='".$_SESSION['useraccount']."') ) Or ".
                             "      ($ledgerinclude = 0 and Deliveries.Client_Code Not In (select distinct ledger_number from ".$_SESSION['dbLabel'].".ledgerstk where user_account='".$_SESSION['useraccount']."') ) ) ";      
*/
$querysearch = $querysearch . " Order By Deliveries.Delivery_Reference Desc ";
//echo"<br>".$querysearch;exit;
$resultsearch = mysql_query($querysearch, $connection);

echo "<html>";
echo "<head>";
echo "<title> Pending Deliveries</title>";
echo "<style>
table th { font-size: 14px; 
    font-weight: bold;
    font-family:Times-New-Roman;
        }				 
table td { font-size: 12px; 
    font-family:Times-New-Roman;
   } 
body,td,th {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 14px;
}
.style1 {
	color: #333333;
	font-weight: bold;
    font-size: 1rem;
}
.style2 {
	color: #999999;
	font-weight: bold;
}
.style3 {color: #999999}
 
    </style>";
echo "</head>";
if ($printaccess == 0 or $printaccess == '')
    echo '<BODY oncontextmenu="return false;">';
else
    echo '<BODY>';
echo "<center><h1> Pending Deliveries</h1></center>";
echo "<table cellpadding=0 cellspacing=0 >";

$header = "<br> Date &nbsp;&nbsp;&nbsp;&nbsp From : " . $fromdate . "  <br>&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  To : " . $todate;
echo "<thead>";
echo "<tr>";
echo "	<th colspan=19 style='font-family: Arial; font-size: 24px;'></th>";
echo "</tr>";
echo "<tr>";
echo "	<th   colspan=19 align ='left'> $header </th>";
echo "</tr>";
echo "<tr>";
echo "	<th height='25px'  colspan=19> </th>";
echo "</tr>
</thead>
<tr>
<table style='width:70%'>
<tr>
<th  height='25' align='center'><span class='style4'>&nbsp;</span></th>
<th  height='25' align='center'><span class='style4'>&nbsp;</span></th>
<th  height='25' align='left'><span class='style4'>Reference </span></th>
<th  height='25' align='center'><span class='style4'>Date </span></th>
<th  height='25' align='center'><span class='style4'>Sorder Ref </span></th>
<th  height='25' align='center'><span class='style4'>Client</span></th>
<th  height='25' align='center'><span class='style4'>&nbsp;</span></th>
<th  height='25' align='center'><span class='style4'>Project</span></th>
<th  height='25' align='center'><span class='style4'>Salesman</span></th>
<th  height='25' align='center'><span class='style4'>Currency</span></th>
<th  height='25' align='center'><span class='style4'>Total</span></th>
</tr>";
$ct = 0;
$i = 0;
while ($rows = mysql_fetch_array($resultsearch)) {

    extract($rows);
    $ct += 1;
    $i = $i + 1;

    if ($ct % 2 == 0)
        $Color = "style='background-color:" . $grid_color1 . "'";
    else
        $Color = "style='background-color:" . $grid_color2 . "'";


    echo "<tr " . $Color . ">";
    echo " <td  height='25' align='center'><span class='style4'>&nbsp;</span></td>
    <td  height='25' align='center'><span class='style4'>&nbsp;</span></td>";
    echo '<td align="left"><span class="style5">' . $Delivery_Reference . '</span></td>
			 <td align="center"><span class="style5">' . $Delivery_Date . '</span></td>
			 <td align="left"><span class="style5">' . $Sorder_Reference . '</span></td>
			 <td align="left"><span class="style5">' . $ClientCode . '</span></td>
			 <td align="left"><span class="style5">' . $ClientName . '</span></td>
			 <td align="left"><span class="style5">' . $ProjectName . '</span></td>
			 <td align="left"><span class="style5">' . $SalesmanName . '</span></td>
			 <td align="center"><span class="style5">' . $Currency_code . '</span></td>
			 <td align="right"><span class="style5">' . number_format($NetTotal, $DecimalBase) . '</span></td>';

    echo "</tr>";

    if ($detail != 0) {

        echo " <tr><td colspan='11'><table border='0' style='table-layout:fixed; margin-left:20px;'>
				   <col width=50>
				   <col width=123>
				   <col width=110>
				   <col width=347>
				   <col width=90>
				   <col width=50>
				   ";

        $querydt = " Select DeliveryDt.DeliveryDt_id, DeliveryDt.Item_CodePacked, DeliveryDt.Item_code, Item_Description, DeliveryDt.Warehouse_Code, " .
            " (ifNull(DeliveryDt.DeliveryDt_Quantity,0) - Sum(ifNull(SalesDt.SaleDt_Quantity,0) + ifNull(Temp_SalesDt.SaleDt_Quantity,0) + ifNull(DR.DeliveryDt_Quantity,0))) As QuantityRemaining  " .
            " From DeliveryDt " .
            " Left Join Items on DeliveryDt.Item_code = Items.Item_code " .
            " Left Join SalesDt on (SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_id And SalesDt.Journal_Type = 'SI' And SalesDt.Sale_Id != '" . $PSaId . "') " .
            " Left Join Temp_SalesDt on (Temp_SalesDt.RelatedDt_ID = DeliveryDt.DeliveryDt_id And Temp_SalesDt.Journal_Type = 'SI') " .
            " Left Outer Join DeliveryDt DR on DeliveryDt.DeliveryDt_ID = DR.RelatedDt_ID  and  DR.Journal_Type = 'DR' " .
            " Where DeliveryDt.Delivery_ID = " . $Delivery_ID .
            " And ifNull(DeliveryDt.DeliveryDt_OldData,0) = 0  " .
            " And (('" . $lstItems . "' != '' and DeliveryDt.Item_Code = '" . $lstItems . "') or ('" . $lstItems . "' = '' and 1 = 1)) " .
            " Group By DeliveryDt.DeliveryDt_id, DeliveryDt.Item_code " .
            " Having QuantityRemaining > 0 " .
            " Order By DeliveryDt.DeliveryDt_id, DeliveryDt.Item_CodePacked, DeliveryDt.Item_code ";

        //echo "<br>".$querydt;
        echo "<tr " . $Color . " >
              <th colspan='2'><span class='style5'><b> Item code </b></span> </th>
              <th colspan='2'><span class='style5'><b> Description </b> </span></th>
              <th colspan='3'><span class='style5'><b> Warehouse </b> </span></th>
              <th colspan='2'><span class='style5'><b> Qty </b> </span></th>
         </tr>";
        $j = 0;
        $resultdt = mysql_query($querydt, $connection) or die(mysql_error());
        while ($rowsdt = mysql_fetch_array($resultdt)) {


            $j = $j + 1;

            if ($ct % 2 == 0)
                $Color = "style='background-color:" . $grid_color1 . "';";
            else
                $Color = "style='background-color:" . $grid_color2 . "'";



            echo "<tr " . $Color . " >";


            echo "	<td align='left' colspan='2'><span class='style5'>" . $rowsdt[2] . "</span></td>";
            echo "	<td align='left' colspan='2'><span class='style5'>" . $rowsdt[3] . "</span></td>";
            echo "	<td align='left' colspan='3'><span class='style5'>" . $rowsdt[4] . "</span></td>";
            echo "	<td align='right' colspan='2'><span class='style5'>" . $rowsdt[5] . "</span></td>";
            echo "</tr>";
        }
        echo "</table></td></tr>";
    }
}
echo "</table></tr></table>";
function number_formatX($Num, $DecimalBase)
{
    if ($Num < 0) $Num = "(" . number_format(abs($Num), 0) . ")";
    else if ($Num > 0) $Num = number_format($Num, 0);
    else $Num = ' ';
    return $Num;
}



function explodeword($date, $ForT)
{

    $trim = explode('/', $date);
    $trim = "$trim[2]" . "-" . "$trim[1]" . "-" . "$trim[0]";
    if ($ForT == 'F') $trim .= ' 00:00:00';
    else if ($ForT == 'T') $trim .= ' 23:59:59';
    return $trim;
}

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

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

HERE;
