function getAgencyShipment($conn)
{
global $gTEXT;
$lan = $_POST['lan'];
if ($lan == 'en-GB') {
$GroupName = 'GroupName';
$OwnerTypeName = 'OwnerTypeName';
} else {
$GroupName = 'GroupNameFrench';
$OwnerTypeName = 'OwnerTypeNameFrench';
}
$CountryId = $_POST['ACountryId'];
$AFundingSourceId = $_POST['AFundingSourceId'];
$ASStatusId = $_POST['ASStatusId'];
$ItemGroup = $_POST['ItemGroup'];
$OwnerTypeId = $_POST['OwnerType'];
if ($CountryId) {
$CountryId = " WHERE a.CountryId = '" . $CountryId . "' ";
}
if ($AFundingSourceId) {
$AFundingSourceId = " AND a.FundingSourceId = '" . $AFundingSourceId . "' ";
}
if ($ASStatusId) {
$ASStatusId = " AND a.ShipmentStatusId = '" . $ASStatusId . "' ";
}
if ($ItemGroup) {
$ItemGroup = " AND a.ItemGroupId = '" . $ItemGroup . "' ";
}
if ($OwnerTypeId) {
$OwnerTypeId = " AND a.OwnerTypeId = '" . $OwnerTypeId . "' ";
}
$sLimit = "";
if (isset($_POST['iDisplayStart'])) {
$sLimit = " LIMIT " . mysql_real_escape_string($_POST['iDisplayStart']) . ", " . mysql_real_escape_string($_POST['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_agencyShipment(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " AND (a.ShipmentDate LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "a.Qty LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "{$GroupName} LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "d.FundingSourceName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "ItemName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "ShipmentStatusDesc LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . "g.{$OwnerTypeName} LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS AgencyShipmentId, a.FundingSourceId, d.FundingSourceName, a.ShipmentStatusId, c.ShipmentStatusDesc, a.CountryId, \n b.CountryName, a.ItemNo, e.ItemName, a.ShipmentDate, a.Qty, f.{$GroupName} GroupName,a.ItemGroupId,a.OwnerTypeId, g.{$OwnerTypeName} OwnerTypeName \n\t\t\tFROM t_agencyshipment as a\n INNER JOIN t_country b ON a.CountryId = b.CountryId\n INNER JOIN t_shipmentstatus c ON a.ShipmentStatusId = c.ShipmentStatusId\n INNER JOIN t_fundingsource d ON a.FundingSourceId= d.FundingSourceId\n INNER JOIN t_itemlist e ON a.ItemNo = e.ItemNo \n\t\t\tINNER JOIN t_itemgroup f ON a.ItemGroupId = f.ItemGroupId \n INNER JOIN t_owner_type g ON a.OwnerTypeId = g.OwnerTypeId\n " . $CountryId . " " . $AFundingSourceId . " " . $ASStatusId . " " . $ItemGroup . " " . $OwnerTypeId . "\n \n\t\t\t{$sWhere} {$sOrder} {$sLimit} ";
//ORDER BY d.FundingSourceName asc
//echo $sql;
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = mysql_query($sQuery);
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
$sOutput = '{';
$sOutput .= '"sEcho": ' . intval($_POST['sEcho']) . ', ';
$sOutput .= '"iTotalRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"aaData": [ ';
$serial = $_POST['iDisplayStart'] + 1;
$y = "<a class='task-del itmEdit' href='javascript:void(0);'><span class='label label-info'>" . $gTEXT['Edit'] . "</span></a>";
$z = "<a class='task-del itmDrop' style='margin-left:4px' href='javascript:void(0);'><span class='label label-danger'>" . $gTEXT['Delete'] . "</span></a>";
$f = 0;
while ($aRow = mysql_fetch_array($result)) {
$ItemName = crnl2br($aRow['ItemName']);
$OwnerTypeName = crnl2br($aRow['OwnerTypeName']);
$date = strtotime($aRow['ShipmentDate']);
$newdate = date('d/m/Y', $date);
if ($f++) {
$sOutput .= ',';
}
$sOutput .= "[";
$sOutput .= '"' . $aRow['AgencyShipmentId'] . '",';
$sOutput .= '"' . $serial++ . '",';
$sOutput .= '"' . $aRow['GroupName'] . '",';
$sOutput .= '"' . $ItemName . '",';
$sOutput .= '"' . $aRow['ShipmentStatusDesc'] . '",';
$sOutput .= '"' . $newdate . '",';
$sOutput .= '"' . $aRow['OwnerTypeName'] . '",';
$sOutput .= '"' . number_format($aRow['Qty']) . '",';
//$sOutput .= '"' . addslashes($aRow['OwnerTypeName']) . '",';
$sOutput .= '"' . $y . $z . " " . '",';
$sOutput .= '"' . $aRow['FundingSourceName'] . '",';
$sOutput .= '"' . $aRow['FundingSourceId'] . '",';
$sOutput .= '"' . $aRow['ShipmentStatusId'] . '",';
$sOutput .= '"' . $aRow['CountryId'] . '",';
$sOutput .= '"' . $aRow['ItemNo'] . '",';
$sOutput .= '"' . $aRow['ItemGroupId'] . '",';
$sOutput .= '"' . $aRow['OwnerTypeId'] . '"';
$sOutput .= "]";
}
$sOutput .= '] }';
echo $sOutput;
}
function generateShipmentReport($conn)
{
global $gTEXT;
require_once 'tcpdf/tcpdf.php';
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
//=====================================================National Inventory Table=======================================================
/* $months = $_POST['MonthNumber'];
$monthIndex = date("n");
$yearIndex = date("Y");
settype($yearIndex, "integer");
if ($monthIndex == 1){
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
}else{
$monthIndex = $monthIndex - 1;
}
$months = $months - 1;
$currentYearMonth = $yearIndex."-0".$monthIndex."-"."01";
$currentYearMonth = date('Y-m-d', strtotime($currentYearMonth));
$lastYearMonth = date("Y-m-d", strtotime(date("Y-m-d", strtotime($currentYearMonth)) . "-".$months." month"));
*/
$StartMonthId = $_POST['StartMonthId'];
$StartYearId = $_POST['StartYearId'];
$EndMonthId = $_POST['EndMonthId'];
$EndYearId = $_POST['EndYearId'];
if ($_POST['MonthNumber'] != 0) {
$months = $_POST['MonthNumber'];
$monthIndex = date("m");
$yearIndex = date("Y");
settype($yearIndex, "integer");
$startDate = $yearIndex . "-" . $monthIndex . "-" . "01";
$startDate = date('Y-m-d', strtotime($startDate));
$months--;
$endDate = date("Y-m-d", strtotime(date("Y-m-d", strtotime($startDate)) . "+" . $months . " month"));
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$startDate = date('Y-m-d', strtotime($startDate));
$d = cal_days_in_month(CAL_GREGORIAN, $EndMonthId, $EndYearId);
$endDate = $EndYearId . "-" . $EndMonthId . "-" . $d;
$endDate = date('Y-m-d', strtotime($endDate));
}
$CountryId = $_POST['ACountryId'];
$AFundingSourceId = $_POST['AFundingSourceId'];
$ASStatusId = $_POST['ASStatusId'];
$ItemGroup = $_POST['ItemGroup'];
$OwnerTypeId = $_POST['OwnerType'];
if ($AFundingSourceId) {
$AFundingSourceId = " AND a.FundingSourceId = '" . $AFundingSourceId . "' ";
}
if ($ASStatusId) {
$ASStatusId = " AND a.ShipmentStatusId = '" . $ASStatusId . "' ";
}
if ($ItemGroup) {
$ItemGroup = " AND e.ItemGroupId = '" . $ItemGroup . "' ";
}
if ($OwnerTypeId) {
$OwnerTypeId = " AND f.OwnerTypeId = '" . $OwnerTypeId . "' ";
}
$sLimit = "";
if (isset($_POST['iDisplayStart'])) {
$sLimit = " LIMIT " . mysql_real_escape_string($_POST['iDisplayStart']) . ", " . mysql_real_escape_string($_POST['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_agencyShipment(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " AND (a.ItemNo LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " e.ItemName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' OR " . " c.ShipmentStatusDesc LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS AgencyShipmentId, a.FundingSourceId, d.FundingSourceName, a.ShipmentStatusId, c.ShipmentStatusDesc, a.CountryId, \n b.CountryName, a.ItemNo, e.ItemName, a.ShipmentDate, a.Qty, a.OwnerTypeId, f.OwnerTypeName \n\t\t\tFROM t_agencyshipment as a\n INNER JOIN t_country b ON a.CountryId = b.CountryId\n INNER JOIN t_shipmentstatus c ON a.ShipmentStatusId = c.ShipmentStatusId\n INNER JOIN t_fundingsource d ON a.FundingSourceId= d.FundingSourceId\n INNER JOIN t_itemlist e ON a.ItemNo = e.ItemNo\n INNER JOIN t_owner_type f ON a.OwnerTypeId = f.OwnerTypeId \n WHERE CAST(a.ShipmentDate AS DATETIME) BETWEEN CAST('{$startDate}' AS DATETIME) AND CAST('{$endDate}' AS DATETIME) \n AND (a.CountryId = " . $CountryId . " OR " . $CountryId . " = 0) \n " . $AFundingSourceId . " " . $ASStatusId . " " . $ItemGroup . " " . $OwnerTypeId . "\n\t\t\t{$sWhere} {$sOrder} {$sLimit} ";
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
$i = 0;
$f = 0;
$GrandtotalQty = 0;
$SubtotalQty = 0;
$OldCountry = ' ';
$NewCountry = ' ';
$serial = 1;
$tblHTML = '';
if ($total > 0) {
/* $data=array();
$f=0;
$tblHTML='';
$tempGroupId='';*/
while ($rec = mysql_fetch_array($result)) {
$ItemName = trim(preg_replace('/\\s+/', ' ', addslashes($rec['ItemName'])));
$date = strtotime($rec['ShipmentDate']);
$newdate = date('d/m/Y', $date);
if ($OldCountry == ' ') {
$OldCountry = addslashes($rec['CountryName']);
}
$NewCountry = addslashes($rec['CountryName']);
if ($OldCountry != $NewCountry) {
$tblHTML .= '<tr >
<td style="background-color:#FE9929;border-radius:2px;align:center; font-size:12px;"colspan="5">Sub Total</td>
<td style="background-color:#FE9929;border-radius:2px;text-align:right; font-size:12px;">' . number_format($SubtotalQty) . '</td>
</tr>';
$tblHTML .= '<tr >
<td style="background-color:#DAEF62;border-radius:2px;align:center; font-size:12px;"colspan="6">' . $NewCountry . '</td>
</tr>';
$tempGroupId = $rec['CountryName'];
$OldCountry = $NewCountry;
$SubtotalQty = $rec['Qty'];
} else {
$SubtotalQty += $rec['Qty'];
}
if ($tempGroupId != $rec['CountryName']) {
$tblHTML .= '<tr >
<td style="background-color:#DAEF62;border-radius:2px; align:center; font-size:12px;" colspan="6">' . $rec['CountryName'] . '</td>
</tr>';
$tempGroupId = $rec['CountryName'];
}
$tblHTML .= '<tr>
<td style="text-align: center;">' . $serial++ . '</td>
<td style="text-align:left;">' . $ItemName . '</td>
<td style="text-align:left;">' . addslashes($rec['FundingSourceName']) . '</td>
<td style="text-align:left;">' . addslashes($rec['ShipmentStatusDesc']) . '</td>
<td style="text-align:right;">' . $newdate . '</td>
<td style="text-align:right;">' . number_format(addslashes($rec['Qty'])) . '</td>
</tr>';
$GrandtotalQty += $rec['Qty'];
$tblHTML .= '';
if ($total == $i + 1) {
$tblHTML .= '<tr >
<td style="background-color:#FE9929;border-radius:2px; align:center; font-size:12px;" colspan="5">Sub Total</td>
<td style="background-color:#FE9929;border-radius:2px; text-align:right; font-size:12px;" ">' . number_format($SubtotalQty) . '</td>
</tr>';
$tblHTML .= '<tr >
<td style="background-color:#FE9929;border-radius:2px; align:center; font-size:12px;" colspan="5">Sub Total</td>
<td style="background-color:#FE9929;border-radius:2px; text-align:right; font-size:12px;" ">' . number_format($SubtotalQty) . '</td>
</tr>';
$tblHTML .= '<tr >
<td style="background-color:#50ABED;border-radius:2px; align:center; font-size:12px;" colspan="5">Grand Total</td>
<td style="background-color:#50ABED;border-radius:2px; text-align:right; font-size:12px;" ">' . number_format($GrandtotalQty) . '</td>
</tr>';
$tblHTML .= '<tr >
<td style="background-color:#50ABED;border-radius:2px; align:center; font-size:12px;" colspan="5">Grand Total</td>
<td style="background-color:#50ABED;border-radius:2px; text-align:right; font-size:12px;" ">' . number_format($GrandtotalQty) . '</td>
</tr>';
}
$i++;
}
//$months = $_POST['MonthNumber'];
$months = $_POST['MonthNumber'];
$CountryName = $_POST['CountryName'];
$FundingSourceName = $_POST['FundingSourceName'];
$ItemGroupName = $_POST['ItemGroupName'];
$ASStatusName = $_POST['ASStatusName'];
$OwnerTypeName = $_POST['OwnerTypeName'];
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
p {
line-height: 0.5px;
}
</style>
<body>
<p style="text-align:center;"><h4 ><b>' . $gTEXT['Shipment Reports'] . ' of ' . $CountryName . ' ' . $gTEXT['from'] . ' ' . date('M,Y', strtotime($startDate)) . ' ' . $gTEXT['to'] . ' ' . date('M,Y', strtotime($endDate)) . '</b></h4></p>
<p style="text-align:center;"><h5><b>' . $FundingSourceName . ' - ' . $ASStatusName . ' - ' . $ItemGroupName . ' - ' . $OwnerTypeName . '</b><h5></p>
</body>';
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 8, 10, $html, '', 0, 0, false, 'C', true);
//date('M,Y', strtotime($StartYearMonth)), date('M,Y', strtotime($EndYearMonth))
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
th{
height:20;
font-size:10px;
}
</style>
<body>
<table width="600px" border="0.5" style="margin:0 auto;">
<tr>
<th width="30" align="center"><b>SL#</b></th>
<th width="250" align="left"><b>' . $gTEXT['Product Name'] . '</b></th>
<th width="100" align="left"><b>' . $gTEXT['Funding Source'] . '</b></th>
<th width="90" align="left"><b>' . $gTEXT['Shipment Status'] . '</b></th>
<th width="100" align="right"><b>' . $gTEXT['Shipment Date'] . '</b></th>
<th width="90" align="right"><b>' . $gTEXT['Quantity'] . '</b></th>
</tr>' . $tblHTML . '</table></body>';
//echo $tblHTML;
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, 10, 40, $html, '', 1, 1, false, 'L', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/ShipmentReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/ShipmentReport.pdf', 'F');
echo 'ShipmentReport.pdf';
} else {
echo 'Processing Error';
}
}
}
if ($ItemGroup) {
$ItemGroup = " AND e.ItemGroupId = '" . $ItemGroup . "' ";
}
if ($OwnerTypeId) {
$OwnerTypeId = " AND f.OwnerTypeId = '" . $OwnerTypeId . "' ";
}
$sLimit = "";
if (isset($_GET['iDisplayStart'])) {
$sLimit = " LIMIT " . mysql_real_escape_string($_GET['iDisplayStart']) . ", " . mysql_real_escape_string($_GET['iDisplayLength']);
}
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_agencyShipment(mysql_real_escape_string($_GET['iSortCol_' . $i])) . "" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_GET['sSearch'] != "") {
$sWhere = " AND (a.ItemNo LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . " e.ItemName LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . " c.ShipmentStatusDesc LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%') ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS AgencyShipmentId, a.FundingSourceId, d.FundingSourceName, a.ShipmentStatusId, c.ShipmentStatusDesc, a.CountryId, \n b.CountryName, a.ItemNo, e.ItemName, a.ShipmentDate, a.Qty, a.OwnerTypeId, f.OwnerTypeName \n\t\t\tFROM t_agencyshipment as a\n INNER JOIN t_country b ON a.CountryId = b.CountryId\n INNER JOIN t_shipmentstatus c ON a.ShipmentStatusId = c.ShipmentStatusId\n INNER JOIN t_fundingsource d ON a.FundingSourceId= d.FundingSourceId\n INNER JOIN t_itemlist e ON a.ItemNo = e.ItemNo\n INNER JOIN t_owner_type f ON a.OwnerTypeId = f.OwnerTypeId \n WHERE CAST(a.ShipmentDate AS DATETIME) BETWEEN CAST('{$startDate}' AS DATETIME) AND CAST('{$endDate}' AS DATETIME) \n AND (a.CountryId = " . $CountryId . " OR " . $CountryId . " = 0) \n " . $AFundingSourceId . " " . $ASStatusId . " " . $ItemGroup . " " . $OwnerTypeId . "\n\t\t\t{$sWhere} {$sOrder} {$sLimit} ";
mysql_query("SET character_set_results=utf8");
$r = mysql_query($sql);
$total = mysql_num_rows($r);
$i = 0;
$f = 0;
$GrandtotalQty = 0;
$SubtotalQty = 0;
