function DB_query_oc($SQL, $ErrorMessage = '', $DebugMessage = '', $Transaction = false, $TrapErrors = true)
{
global $db_oc;
global $PathPrefix;
$Result = mysqli_query($db_oc, $SQL);
if ($DebugMessage == '') {
$DebugMessage = _('The SQL that failed was');
}
if (DB_error_no($db_oc) != 0 and $TrapErrors == true) {
if ($TrapErrors) {
require_once $PathPrefix . 'includes/header.inc';
}
prnMsg($ErrorMessage . '<br />' . DB_error_msg($db_oc), 'error', _('Database Error') . ' ' . DB_error_no($db_oc));
if ($Debug == 1) {
prnMsg($DebugMessage . '<br />' . $SQL . '<br />', 'error', _('Database SQL Failure'));
}
if ($Transaction) {
$SQL = 'rollback';
$Result = DB_query_oc($SQL);
if (DB_error_no() != 0) {
prnMsg(_('Error Rolling Back Transaction'), 'error', _('Database Rollback Error') . ' ' . DB_error_no($db_oc));
} else {
prnMsg(_('Rolling Back Transaction OK'), 'error', _('Database Rollback Due to Error Above'));
}
}
if ($TrapErrors) {
include $PathPrefix . 'includes/footer.inc';
exit;
}
}
return $Result;
}
$LeftOvers = $pdf->addTextWrap($Left_Margin + 15, $YPos, 260 - $Left_Margin, $FontSize, $CheckItemRow['categoryid'] . ' - ' . $CheckItemRow['categorydescription'], 'left');
$Category = $CheckItemRow['categoryid'];
$YPos -= $line_height;
}
$YPos -= $line_height;
$FontSize = 8;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 120, $FontSize, $CheckItemRow['stockid'], 'left');
$LeftOvers = $pdf->addTextWrap(135, $YPos, 180, $FontSize, $CheckItemRow['description'], 'left');
$LeftOvers = $pdf->addTextWrap(315, $YPos, 60, $FontSize, $CheckItemRow['qoh'], 'right');
$SQL = "SELECT qtycounted, reference FROM stockcounts WHERE loccode ='" . $Location . "' AND stockid = '" . $CheckItemRow['stockid'] . "'";
$Counts = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$title = _('Inventory Comparison') . ' - ' . _('Problem Report') . '.... ';
include 'includes/header.inc';
echo '<br>';
prnMsg(_('The inventory counts could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db), 'error');
echo '<br><a href="' . $rootpath . '/index.php?' . SID . '">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br>' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($Counts) == 0) {
$LeftOvers = $pdf->addTextWrap(380, $YPos, 160, $FontSize, _('No counts entered'), 'left');
if ($_POST['ZeroCounts'] == 'Adjust') {
$LeftOvers = $pdf->addTextWrap(485, $YPos, 60, $FontSize, -$CheckItemRow['qoh'], 'right');
}
} else {
$TotalCount = 0;
while ($CountRow = DB_fetch_array($Counts, $db)) {
// Javier: needs check.
/* END Brought from class.pdf.php constructor */
$PageNumber = 1;
$line_height = 12;
/*Now figure out the inventory data to report for the category range under review */
if ($Location == 'All') {
$SQL = "SELECT stockmaster.categoryid,\n\t\t\tstockcategory.categorydescription,\n\t\t\tstockmaster.stockid,\n\t\t\tstockmaster.description,\n\t\t\tstockmaster.decimalplaces,\n\t\t\tSUM(locstock.quantity) as qtyonhand,\n\t\t\tstockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS unitcost,\n\t\t\tSUM(locstock.quantity) *(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS itemtotal\n\t\tFROM stockmaster,\n\t\t\tstockcategory,\n\t\t\tlocstock\n\t\tWHERE stockmaster.stockid=locstock.stockid\n\t\tAND stockmaster.categoryid=stockcategory.categoryid\n\t\tGROUP BY stockmaster.categoryid,\n\t\t\tstockcategory.categorydescription,\n\t\t\tunitcost,\n\t\t\tstockmaster.stockid,\n\t\t\tstockmaster.description\n\t\tHAVING SUM(locstock.quantity)!=0\n\t\tAND stockmaster.categoryid >= '" . $FromCriteria . "'\n\t\tAND stockmaster.categoryid <= '" . $ToCriteria . "'\n\t\tORDER BY stockmaster.categoryid,\n\t\t\tstockmaster.stockid";
} else {
$SQL = "SELECT stockmaster.categoryid,\n\t\t\tstockcategory.categorydescription,\n\t\t\tstockmaster.stockid,\n\t\t\tstockmaster.description,\n\t\t\tstockmaster.decimalplaces,\n\t\t\tlocstock.quantity as qtyonhand,\n\t\t\tstockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS unitcost,\n\t\t\tlocstock.quantity *(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS itemtotal\n\t\tFROM stockmaster,\n\t\t\tstockcategory,\n\t\t\tlocstock\n\t\tWHERE stockmaster.stockid=locstock.stockid\n\t\tAND stockmaster.categoryid=stockcategory.categoryid\n\t\tAND locstock.quantity!=0\n\t\tAND stockmaster.categoryid >= '" . $FromCriteria . "'\n\t\tAND stockmaster.categoryid <= '" . $ToCriteria . "'\n\t\tAND locstock.loccode = '" . $Location . "'\n\t\tORDER BY stockmaster.categoryid,\n\t\t\tstockmaster.stockid";
}
$InventoryResult = DB_query($SQL, $db, '', '', false, true);
$ListCount = DB_num_rows($InventoryResult);
if (DB_error_no($db) != 0) {
$title = _('Inventory Valuation') . ' - ' . _('Problem Report');
include 'includes/header.inc';
echo _('The inventory valuation could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db);
echo '<br /><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
include 'includes/PDFInventoryValnPageHeader.inc';
$Tot_Val = 0;
$Category = '';
$CatTot_Val = 0;
while ($InventoryValn = DB_fetch_array($InventoryResult, $db)) {
if ($Category != $InventoryValn['categoryid']) {
$FontSize = 10;
if ($Category != '') {
$pdf->addInfo('Subject', _('Customer Balances'));
$FontSize = 12;
$PageNumber = 0;
$line_height = 12;
/*Get the date of the last day in the period selected */
$SQL = "SELECT lastdate_in_period FROM periods WHERE periodno = '" . $_POST['PeriodEnd'] . "'";
$PeriodEndResult = DB_query($SQL, $db, _('Could not get the date of the last day in the period selected'));
$PeriodRow = DB_fetch_row($PeriodEndResult);
$PeriodEndDate = ConvertSQLDate($PeriodRow[0]);
/*Now figure out the aged analysis for the customer range under review */
$SQL = "SELECT debtorsmaster.debtorno,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t \t\t\tcurrencies.currency,\n\t\t \t\t\tcurrencies.decimalplaces,\n\t\t\t\t\tSUM((debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)/debtortrans.rate) AS balance,\n\t\t\t\t\tSUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS fxbalance,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\t(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount)/debtortrans.rate ELSE 0 END) AS afterdatetrans,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "'\n\t\t\t\t\t\tAND (debtortrans.type=11 OR debtortrans.type=12) THEN\n\t\t\t\t\t\tdebtortrans.diffonexch ELSE 0 END) AS afterdatediffonexch,\n\t\t\t\t\tSUM(CASE WHEN debtortrans.prd > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\tdebtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount ELSE 0 END\n\t\t\t\t\t) AS fxafterdatetrans\n\t\t\tFROM debtorsmaster INNER JOIN currencies\n\t\t\tON debtorsmaster.currcode = currencies.currabrev\n\t\t\tINNER JOIN debtortrans\n\t\t\tON debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\tWHERE debtorsmaster.debtorno >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND debtorsmaster.debtorno <= '" . $_POST['ToCriteria'] . "'\n\t\t\tGROUP BY debtorsmaster.debtorno,\n\t\t\t\tdebtorsmaster.name,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.decimalplaces";
$CustomerResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Customer Balances') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The customer details could not be retrieved by the SQL because') . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($CustomerResult) == 0) {
$Title = _('Customer Balances') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The customer details listing has no clients to report on'), 'warn');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
//editing an existing price
$sql = "UPDATE prices SET typeabbrev='" . $SalesType . "',\n\t\t currabrev='" . $CurrCode . "',\n\t\t\t\t\t\t\t\tprice='" . filter_currency_input($_POST['Price']) . "',\n\t\t\t\t\t\t\t\tunits='" . $_POST['Units'] . "',\n\t\t\t\t\t\t\t\tconversionfactor='" . filter_number_input($_POST['ConversionFactor']) . "',\n\t\t\t\t\t\t\t\tdecimalplaces='" . round($_POST['DecimalPlaces'], 0) . "',\n\t\t\t\t\t\t\t\tbranchcode='" . $_POST['Branch'] . "',\n\t\t\t\t\t\t\t\tstartdate='" . FormatDateForSQL($_POST['StartDate']) . "',\n\t\t\t\t\t\t\t\tenddate='" . FormatDateForSQL($_POST['EndDate']) . "'\n\t\t\t\tWHERE prices.stockid='" . $Item . "'\n\t\t\t\tAND prices.typeabbrev='" . $SalesType . "'\n\t\t\t\tAND prices.currabrev='" . $CurrCode . "'\n\t\t\t\tAND prices.startdate='" . $_POST['OldStartDate'] . "'\n\t\t\t\tAND prices.enddate='" . $_POST['OldEndDate'] . "'\n\t\t\t\tAND prices.debtorno='" . $_SESSION['CustomerID'] . "'";
$msg = _('Price Updated');
} elseif ($InputError != 1) {
/*Selected price is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new price form */
$sql = "INSERT INTO prices (stockid,\n\t\t typeabbrev,\n\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\t\t\tconversionfactor,\n\t\t\t\t\t\t\t\t\tdecimalplaces,\n\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\t\t\t\tenddate)\n\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t'" . $Item . "',\n\t\t\t\t\t\t\t\t\t'" . $SalesType . "',\n\t\t\t\t\t\t\t\t\t'" . $CurrCode . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CustomerID'] . "',\n\t\t\t\t\t\t\t\t\t'" . filter_currency_input($_POST['Price']) . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['Units'] . "',\n\t\t\t\t\t\t\t\t\t'" . filter_number_input($_POST['ConversionFactor']) . "',\n\t\t\t\t\t\t\t\t\t'" . round($_POST['DecimalPlaces'], 0) . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['Branch'] . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['StartDate']) . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['EndDate']) . "'\n\t\t\t\t\t\t\t\t\t)";
$msg = _('Price added') . '.';
}
//run the SQL from either of the above possibilites
if ($InputError != 1) {
$result = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
if ($msg == _('Price Updated')) {
$msg = _('The price could not be updated because') . ' - ' . DB_error_msg($db);
} else {
$msg = _('The price could not be added because') . ' - ' . DB_error_msg($db);
}
} else {
ReSequenceEffectiveDates($Item, $SalesType, $CurrCode, $_SESSION['CustomerID'], $db);
unset($_POST['EndDate']);
unset($_POST['StartDate']);
unset($_POST['Price']);
unset($_POST['Units']);
unset($_POST['ConversionFactor']);
unset($_POST['DecimalPlaces']);
}
}
prnMsg($msg);
} elseif (isset($_GET['delete'])) {
//the link to delete a selected record was clicked instead of the submit button
$sql = "DELETE FROM prices\n\t\t\tWHERE prices.stockid = '" . $Item . "'\n\t\t\tAND prices.typeabbrev='" . $SalesType . "'\n\t\t\tAND prices.currabrev ='" . $CurrCode . "'\n\t\t\tAND prices.debtorno='" . $_SESSION['CustomerID'] . "'\n\t\t\tAND prices.branchcode='" . $_GET['Branch'] . "'\n\t\t\tAND prices.startdate='" . $_GET['StartDate'] . "'\n\t\t\tAND prices.enddate='" . $_GET['EndDate'] . "'";
/* $Id: SupplierBalsAtPeriodEnd.php 6310 2013-08-29 10:42:50Z daintree $*/
include 'includes/session.inc';
if (isset($_POST['PrintPDF']) and isset($_POST['FromCriteria']) and mb_strlen($_POST['FromCriteria']) >= 1 and isset($_POST['ToCriteria']) and mb_strlen($_POST['ToCriteria']) >= 1) {
include 'includes/PDFStarter.php';
$pdf->addInfo('Title', _('Supplier Balance Listing'));
$pdf->addInfo('Subject', _('Supplier Balances'));
$FontSize = 12;
$PageNumber = 0;
$line_height = 12;
/*Now figure out the aged analysis for the Supplier range under review */
$SQL = "SELECT suppliers.supplierid,\n\t\t\t\t\tsuppliers.suppname,\n\t\t \t\t\tcurrencies.currency,\n\t\t \t\t\tcurrencies.decimalplaces AS currdecimalplaces,\n\t\t\t\t\tSUM((supptrans.ovamount + supptrans.ovgst - supptrans.alloc)/supptrans.rate) AS balance,\n\t\t\t\t\tSUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS fxbalance,\n\t\t\t\t\tSUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t(supptrans.ovamount + supptrans.ovgst)/supptrans.rate ELSE 0 END) AS afterdatetrans,\n\t\t\t\t\tSUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "'\n\t\t\t\t\t\tAND (supptrans.type=22 OR supptrans.type=21) THEN\n\t\t\t\t\t\tsupptrans.diffonexch ELSE 0 END) AS afterdatediffonexch,\n\t\t\t\t\tSUM(CASE WHEN supptrans.trandate > '" . $_POST['PeriodEnd'] . "' THEN\n\t\t\t\t\t\tsupptrans.ovamount + supptrans.ovgst ELSE 0 END) AS fxafterdatetrans\n\t\t\tFROM suppliers INNER JOIN currencies\n\t\t\tON suppliers.currcode = currencies.currabrev\n\t\t\tINNER JOIN supptrans\n\t\t\tON suppliers.supplierid = supptrans.supplierno\n\t\t\tWHERE suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'\n\t\t\tGROUP BY suppliers.supplierid,\n\t\t\t\tsuppliers.suppname,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.decimalplaces";
$SupplierResult = DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Title = _('Supplier Balances - Problem Report');
include 'includes/header.inc';
prnMsg(_('The Supplier details could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($SupplierResult) == 0) {
$Title = _('Supplier Balances - Problem Report');
include 'includes/header.inc';
prnMsg(_('There are no supplier balances to list'), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
$pdf->addInfo('Title', _('Inventory Valuation Report'));
$pdf->addInfo('Subject', _('Inventory Valuation'));
$FontSize = 9;
$PageNumber = 1;
$line_height = 12;
/*Now figure out the inventory data to report for the category range under review */
if ($_POST['Location'] == 'All') {
$SQL = "SELECT stockmaster.categoryid,\n\t\t\t\tstockcategory.categorydescription,\n\t\t\t\tstockmaster.stockid,\n\t\t\t\tstockmaster.description,\n\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\tSUM(locstock.quantity) AS qtyonhand,\n\t\t\t\tstockmaster.units,\n\t\t\t\tstockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS unitcost,\n\t\t\t\tSUM(locstock.quantity) *(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS itemtotal\n\t\t\tFROM stockmaster,\n\t\t\t\tstockcategory,\n\t\t\t\tlocstock\n\t\t\tWHERE stockmaster.stockid=locstock.stockid\n\t\t\tAND stockmaster.categoryid=stockcategory.categoryid\n\t\t\tGROUP BY stockmaster.categoryid,\n\t\t\t\tstockcategory.categorydescription,\n\t\t\t\tunitcost,\n\t\t\t\tstockmaster.units,\n\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\tstockmaster.materialcost,\n\t\t\t\tstockmaster.labourcost,\n\t\t\t\tstockmaster.overheadcost,\n\t\t\t\tstockmaster.stockid,\n\t\t\t\tstockmaster.description\n\t\t\tHAVING SUM(locstock.quantity)!=0\n\t\t\tAND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n\t\t\tORDER BY stockmaster.categoryid,\n\t\t\t\tstockmaster.stockid";
} else {
$SQL = "SELECT stockmaster.categoryid,\n\t\t\t\tstockcategory.categorydescription,\n\t\t\t\tstockmaster.stockid,\n\t\t\t\tstockmaster.description,\n\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\tstockmaster.units,\n\t\t\t\tlocstock.quantity AS qtyonhand,\n\t\t\t\tstockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost AS unitcost,\n\t\t\t\tlocstock.quantity *(stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS itemtotal\n\t\t\tFROM stockmaster,\n\t\t\t\tstockcategory,\n\t\t\t\tlocstock\n\t\t\tWHERE stockmaster.stockid=locstock.stockid\n\t\t\tAND stockmaster.categoryid=stockcategory.categoryid\n\t\t\tAND locstock.quantity!=0\n\t\t\tAND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n\t\t\tAND locstock.loccode = '" . $_POST['Location'] . "'\n\t\t\tORDER BY stockmaster.categoryid,\n\t\t\t\tstockmaster.stockid";
}
$InventoryResult = DB_query($SQL, $db, '', '', false, true);
if (DB_error_no($db) != 0) {
$title = _('Inventory Valuation') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The inventory valuation could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br /> ' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($InventoryResult) == 0) {
$title = _('Print Inventory Valuation Error');
include 'includes/header.inc';
prnMsg(_('There were no items with any value to print out for the location specified'), 'info');
echo '<br /><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
$CustomerName = $CustNameRow[0];
$SalesType = $CustNameRow[1];
$SQL = "SELECT prices.typeabbrev,\n \t\t\t\t\t\tprices.stockid,\n \t\t\t\t\t\tstockmaster.description,\n \t\t\t\t\t\tstockmaster.longdescription,\n \t\t\t\t\t\tprices.currabrev,\n \t\t\t\t\t\tprices.startdate,\n \t\t\t\t\t\tprices.enddate,\n \t\t\t\t\t\tprices.price,\n \t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,\n \t\t\t\t\t\tstockmaster.categoryid,\n \t\t\t\t\t\tstockcategory.categorydescription,\n \t\t\t\t\t\tprices.debtorno,\n \t\t\t\t\t\tprices.branchcode,\n \t\t\t\t\t\tcustbranch.brname,\n \t\t\t\t\t\tcurrencies.decimalplaces\n\t\t\t\t\t\tFROM stockmaster INNER JOIN\tstockcategory\n\t\t\t\t\t\tON stockmaster.categoryid=stockcategory.categoryid\n\t\t\t\t\t\tINNER JOIN prices\n\t\t\t\t\t\tON stockmaster.stockid=prices.stockid\n\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\tON prices.currabrev=currencies.currabrev\n LEFT JOIN custbranch\n\t\t\t\t\t\tON prices.debtorno=custbranch.debtorno\n\t\t\t\t\t\tAND prices.branchcode=custbranch.branchcode\n\t\t\t\t\t\tWHERE prices.typeabbrev = '" . $SalesType . "'\n\t\t\t\t\t\tAND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n\t\t\t\t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n\t\t\t\t\t\tAND prices.debtorno='" . $_SESSION['CustomerID'] . "'\n\t\t\t\t\t\tAND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'\n\t\t\t\t\t\tAND (prices.enddate='0000-00-00' OR prices.enddate >'" . FormatDateForSQL($_POST['EffectiveDate']) . "')\n\t\t\t\t\t\tORDER BY prices.currabrev,\n\t\t\t\t\t\t\tstockmaster.categoryid,\n\t\t\t\t\t\t\tstockmaster.stockid,\n\t\t\t\t\t\t\tprices.startdate";
} else {
/* the sales type list only */
$SQL = "SELECT sales_type FROM salestypes WHERE typeabbrev='" . $_POST['SalesType'] . "'";
$SalesTypeResult = DB_query($SQL, $db);
$SalesTypeRow = DB_fetch_row($SalesTypeResult);
$SalesTypeName = $SalesTypeRow[0];
$SQL = "SELECT prices.typeabbrev,\n \t\t\t\tprices.stockid,\n \t\t\t\tprices.startdate,\n \t\t\t\tprices.enddate,\n \t\t\t\tstockmaster.description,\n \t\t\t\tstockmaster.longdescription,\n \t\t\t\tprices.currabrev,\n \t\t\t\tprices.price,\n \t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost as standardcost,\n \t\t\t\tstockmaster.categoryid,\n \t\t\t\tstockcategory.categorydescription,\n \t\t\t\tcurrencies.decimalplaces\n\t\t\t\tFROM stockmaster INNER JOIN\tstockcategory\n\t \t\t\t ON stockmaster.categoryid=stockcategory.categoryid\n\t\t\t\tINNER JOIN prices\n \t\t\t\tON stockmaster.stockid=prices.stockid\n\t\t\t\tINNER JOIN currencies\n\t\t\t\t\tON prices.currabrev=currencies.currabrev\n WHERE stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n \t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n \t\t\tAND prices.typeabbrev='" . $_POST['SalesType'] . "'\n \t\t\tAND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'\n \t\t\tAND (prices.enddate='0000-00-00' OR prices.enddate>'" . FormatDateForSQL($_POST['EffectiveDate']) . "')\n \t\t\tAND prices.debtorno=''\n \t\t\tORDER BY prices.currabrev,\n \t\t\t\tstockmaster.categoryid,\n \t\t\t\tstockmaster.stockid,\n \t\t\t\tprices.startdate";
}
$PricesResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$title = _('Price List') . ' - ' . _('Problem Report....');
include 'includes/header.inc';
prnMsg(_('The Price List could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
prnMsg(_('For debugging purposes the SQL used was:') . $SQL, 'error');
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($PricesResult) == 0) {
$title = _('Print Price List Error');
include 'includes/header.inc';
prnMsg(_('There were no price details to print out for the customer or category specified'), 'warn');
echo '<br /><a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">' . _('Back') . '</a>';
include 'includes/footer.inc';
exit;
}
}
header('Content-type: application/csv');
header('Content-Length: ' . mb_strlen($CSVContent));
header('Content-Disposition: inline; filename=SecGroupList.csv');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
echo $CSVContent;
exit;
} elseif (isset($_POST['secuserlist'])) {
$SQL = "SELECT userid,\n\t\t\tpassword,\n\t\t\trealname,\n\t\t\tcustomerid,\n\t\t\tphone,\n\t\t\temail,\n\t\t\tdefaultlocation,\n\t\t\tfullaccess,\n\t\t\tlastvisitdate,\n\t\t\tbranchcode,\n\t\t\tpagesize,\n\t\t\tmodulesallowed,\n\t\t\tblocked,\n\t\t\tdisplayrecordsmax,\n\t\t\ttheme,\n\t\t\tlanguage\n\t\tFROM www_users\n\t\tWHERE (customerid <> '') OR\n\t\t\t(NOT customerid IS NULL)";
$SecUserResult = DB_query($SQL, '', '', false, false);
if (DB_error_no() != 0) {
$Title = _('Security User List Export Problem ....');
include 'includes/header.inc';
prnMsg(_('The Security User List could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
$CSVContent = stripcomma('userid') . ',' . stripcomma('password') . ',' . stripcomma('realname') . ',' . stripcomma('customerid') . ',' . stripcomma('phone') . ',' . stripcomma('email') . ',' . stripcomma('defaultlocation') . ',' . stripcomma('fullaccess') . ',' . stripcomma('lastvisitdate') . ',' . stripcomma('branchcode') . ',' . stripcomma('pagesize') . ',' . stripcomma('modulesallowed') . ',' . stripcomma('blocked') . ',' . stripcomma('displayrecordsmax') . ',' . stripcomma('theme') . ',' . stripcomma('language') . ',' . stripcomma('pinno') . ',' . stripcomma('swipecard') . "\n";
while ($SecUserList = DB_fetch_array($SecUserResult, $db)) {
$CSVContent .= stripcomma($SecUserList['userid']) . ',' . stripcomma($SecUserList['password']) . ',' . stripcomma($SecUserList['realname']) . ',' . stripcomma($SecUserList['customerid']) . ',' . stripcomma($SecUserList['phone']) . ',' . stripcomma($SecUserList['email']) . ',' . stripcomma($SecUserList['defaultlocation']) . ',' . stripcomma($SecUserList['fullaccess']) . ',' . stripcomma($SecUserList['lastvisitdate']) . ',' . stripcomma($SecUserList['branchcode']) . ',' . stripcomma($SecUserList['pagesize']) . ',' . stripcomma($SecUserList['modulesallowed']) . ',' . stripcomma($SecUserList['blocked']) . ',' . stripcomma($SecUserList['displayrecordsmax']) . ',' . stripcomma($SecUserList['theme']) . ',' . stripcomma($SecUserList['language']) . ',' . stripcomma($SecUserList['pinno']) . ',' . stripcomma($SecUserList['swipecard']) . "\n";
}
header('Content-type: application/csv');
header('Content-Length: ' . mb_strlen($CSVContent));
header('Content-Disposition: inline; filename=SecUserList.csv');
header('Expires: 0');
$WhereCategory = " AND stockmaster.categoryid ='" . $_POST['StockCat'] . "' ";
} else {
$CategoryDescription = _('All');
$WhereCategory = " ";
}
// If Strategy is "Items needed at TO location with overstock at FROM" we need to control the "needed at TO" part
// The "overstock at FROM" part is controlled in any case with AND (fromlocstock.quantity - fromlocstock.reorderlevel) > 0
if ($_POST['Strategy'] == 'All') {
$WhereCategory = $WhereCategory . " AND locstock.reorderlevel > locstock.quantity ";
}
$sql = "SELECT locstock.stockid,\n\t\t\t\tstockmaster.description,\n\t\t\t\tlocstock.loccode,\n\t\t\t\tlocstock.quantity,\n\t\t\t\tlocstock.reorderlevel,\n\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\tstockmaster.serialised,\n\t\t\t\tstockmaster.controlled,\n\t\t\t\tstockmaster.discountcategory,\n\t\t\t\tROUND((locstock.reorderlevel - locstock.quantity) *\n\t\t\t\t (1 + (" . filter_number_format($_POST['Percent']) . "/100)))\n\t\t\t\tas neededqty,\n\t\t\t (fromlocstock.quantity - fromlocstock.reorderlevel) as available,\n\t\t\t fromlocstock.reorderlevel as fromreorderlevel,\n\t\t\t fromlocstock.quantity as fromquantity\n\t\t\tFROM stockmaster\n\t\t\tLEFT JOIN stockcategory\n\t\t\t\tON stockmaster.categoryid=stockcategory.categoryid,\n\t\t\tlocstock\n\t\t\tLEFT JOIN locstock AS fromlocstock ON\n\t\t\t locstock.stockid = fromlocstock.stockid\n\t\t\t AND fromlocstock.loccode = '" . $_POST['FromLocation'] . "'\n\t\t\tWHERE locstock.stockid=stockmaster.stockid\n\t\t\tAND locstock.loccode ='" . $_POST['ToLocation'] . "'\n\t\t\tAND (fromlocstock.quantity - fromlocstock.reorderlevel) > 0\n\t\t\tAND stockcategory.stocktype<>'A'\n\t\t\tAND (stockmaster.mbflag='B' OR stockmaster.mbflag='M') " . $WhereCategory . " ORDER BY locstock.loccode,locstock.stockid";
$result = DB_query($sql, $db, '', '', false, true);
if (DB_error_no($db) != 0) {
$Title = _('Stock Dispatch - Problem Report');
include 'includes/header.inc';
prnMsg(_('The Stock Dispatch report could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo '<br />
<a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($result) == 0) {
$Title = _('Stock Dispatch - Problem Report');
include 'includes/header.inc';
echo '<br />';
prnMsg(_('The stock dispatch did not have any items to list'), 'warn');
echo '<br />
<a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
$SupplierID = '';
$RemittanceAdviceCounter = 0;
while ($SuppliersPaid = DB_fetch_array($SuppliersResult)) {
$PageNumber = 1;
PageHeader();
$RemittanceAdviceCounter++;
$SupplierID = $SuppliersPaid['supplierid'];
$SupplierName = $SuppliersPaid['suppname'];
$AccumBalance = 0;
/* Now get the transactions and amounts that the payment was allocated to */
$sql = "SELECT systypes.typename,\n\t\t\t\t\t\tsupptrans.suppreference,\n\t\t\t\t\t\tsupptrans.trandate,\n\t\t\t\t\t\tsupptrans.transno,\n\t\t\t\t\t\tsuppallocs.amt,\n\t\t\t\t\t\t(supptrans.ovamount + supptrans.ovgst ) AS trantotal\n\t\t\t\tFROM supptrans\n\t\t\t\tINNER JOIN systypes ON systypes.typeid = supptrans.type\n\t\t\t\tINNER JOIN suppallocs ON suppallocs.transid_allocto=supptrans.id\n\t\t\t\tWHERE suppallocs.transid_allocfrom='" . $SuppliersPaid['id'] . "'\n\t\t\t\tORDER BY supptrans.type,\n\t\t\t\t\t\t supptrans.transno";
$TransResult = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Remittance Advice Problem Report');
include 'includes/header.inc';
prnMsg(_('The details of the payment to the supplier could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . ' ' . $sql;
}
include 'includes/footer.inc';
exit;
}
while ($DetailTrans = DB_fetch_array($TransResult)) {
$DisplayTranDate = ConvertSQLDate($DetailTrans['trandate']);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 5, $YPos, 80, $FontSize, $DetailTrans['typename'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 95, $YPos, 80, $FontSize, $DisplayTranDate, 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 175, $YPos, 80, $FontSize, $DetailTrans['suppreference'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 255, $YPos, 80, $FontSize, locale_number_format($DetailTrans['trantotal'], $SuppliersPaid['currdecimalplaces']), 'right');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 355, $YPos, 80, $FontSize, locale_number_format($DetailTrans['amt'], $SuppliersPaid['currdecimalplaces']), 'right');
$AccumBalance += $DetailTrans['amt'];
include 'includes/footer.inc';
exit;
}
if ($_POST['Location'] == 'All') {
$SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) as qtyonorder\n\t\t\t\tFROM purchorderdetails,\n\t\t\t\t\tpurchorders\n\t\t\t\tWHERE purchorderdetails.orderno = purchorders.orderno\n\t\t\t\tAND purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND purchorderdetails.completed = 0";
} else {
$SQL = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) AS qtyonorder\n\t\t\t\tFROM purchorderdetails,\n\t\t\t\t\tpurchorders\n\t\t\t\tWHERE purchorderdetails.orderno = purchorders.orderno\n\t\t\t\tAND purchorderdetails.itemcode = '" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND purchorderdetails.completed = 0\n\t\t\t\tAND purchorders.intostocklocation= '" . $_POST['Location'] . "'";
}
$DemandRow = DB_fetch_array($DemandResult);
$BOMDemandRow = DB_fetch_array($BOMDemandResult);
$TotalDemand = $DemandRow['qtydemand'] + $BOMDemandRow['dem'];
$OnOrdResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The purchase order quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db), 'error');
echo "<br><a href='" . $rootpath . "/index.php?" . SID . "'>" . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo "<br>{$SQL}";
}
include 'includes/footer.inc';
exit;
}
$OnOrdRow = DB_fetch_array($OnOrdResult);
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 110, $FontSize, $InventoryPlan['stockid'], 'left');
$LeftOvers = $pdf->addTextWrap(130, $YPos, 120, 6, $InventoryPlan['description'], 'left');
$LeftOvers = $pdf->addTextWrap(251, $YPos, 40, $FontSize, number_format($SalesRow['prd5'], 0), 'right');
$LeftOvers = $pdf->addTextWrap(292, $YPos, 40, $FontSize, number_format($SalesRow['prd4'], 0), 'right');
$LeftOvers = $pdf->addTextWrap(333, $YPos, 40, $FontSize, number_format($SalesRow['prd3'], 0), 'right');
$LeftOvers = $pdf->addTextWrap(374, $YPos, 40, $FontSize, number_format($SalesRow['prd2'], 0), 'right');
$LeftOvers = $pdf->addTextWrap(415, $YPos, 40, $FontSize, number_format($SalesRow['prd1'], 0), 'right');
case 8:
case 9:
$QuarterStartMonth = 7;
break;
default:
$QuarterStartMonth = 10;
}
$FromDate = date('Y-m-d', mktime(0, 0, 0, $QuarterStartMonth, 1, date('Y')));
$ToDate = date('Y-m-d');
break;
case 'Custom':
$FromDate = FormatDateForSQL($_POST['FromDate']);
$ToDate = FormatDateForSQL($_POST['ToDate']);
}
$sql = "SELECT stockmaster.categoryid,\n\t\t\t\t\tstockcategory.categorydescription,\n\t\t\t\t\tSUM(CASE WHEN stockmoves.type=10 THEN\n\t\t\t\t\t\t\tprice*(1-discountpercent)* -qty\n\t\t\t\t\t\t\tELSE 0 END) as salesvalue,\n\t\t\t\t\tSUM(CASE WHEN stockmoves.type=11 THEN\n\t\t\t\t\t\t\tprice*(1-discountpercent)* (-qty)\n\t\t\t\t\t\t\tELSE 0 END) as returnvalue,\n\t\t\t\t\tSUM(CASE WHEN stockmoves.type=11\n\t\t\t\t\t\t\t\tOR stockmoves.type=10 THEN\n\t\t\t\t\t\t\tprice*(1-discountpercent)* (-qty)\n\t\t\t\t\t\t\tELSE 0 END) as netsalesvalue,\n\t\t\t\t\tSUM((standardcost * -qty)) as cost\n\t\t\tFROM stockmoves INNER JOIN stockmaster\n\t\t\tON stockmoves.stockid=stockmaster.stockid\n\t\t\tINNER JOIN stockcategory\n\t\t\tON stockmaster.categoryid=stockcategory.categoryid\n\t\t\tWHERE (stockmoves.type=10 or stockmoves.type=11)\n\t\t\tAND show_on_inv_crds =1\n\t\t\tAND trandate>='" . $FromDate . "'\n\t\t\tAND trandate<='" . $ToDate . "'\n\t\t\tGROUP BY stockmaster.categoryid\n\t\t\tORDER BY netsalesvalue DESC";
$ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg($db);
$SalesResult = DB_query($sql, $db, $ErrMsg);
echo '<table cellpadding="2" class="selection">
<tr>
<th colspan="9">
<h3>' . _('Show Sales') . ' ' . _('Between') . ' ' . ConvertSQLDate($FromDate) . ' ' . _('and') . ' ' . ConvertSQLDate($ToDate) . '
<img src="' . $RootPath . '/css/' . $Theme . '/images/printer.png" class="PrintIcon noPrint" title="' . _('Print') . '" alt="" onclick="window.print();" />
</h3>
</th>
</tr>';
echo '<tr>
<th>' . _('Category') . '</th>
<th>' . _('Total Sales') . '</th>
<th>' . _('Refunds') . '</th>
<th>' . _('Net Sales') . '</th>
<th>' . _('Cost of Sales') . '</th>
$FontSize = 10;
$PageNumber = 1;
$line_height = 12;
$Title = _('Sell Through Support Claim') . ' - ' . _('Problem Report');
if (!Is_Date($_POST['FromDate']) or !Is_Date($_POST['ToDate'])) {
include 'includes/header.inc';
prnMsg(_('The dates entered must be in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
include 'includes/footer.inc';
exit;
}
/*Now figure out the data to report for the category range under review */
$SQL = "SELECT sellthroughsupport.supplierno,\n\t\t\t\t\tsuppliers.suppname,\n\t\t\t\t\tsuppliers.currcode,\n\t\t\t\t\tcurrencies.decimalplaces as currdecimalplaces,\n\t\t\t\t\tstockmaster.stockid,\n\t\t\t\t\tstockmaster.decimalplaces,\n\t\t\t\t\tstockmaster.description,\n\t\t\t\t\tstockmoves.transno,\n\t\t\t\t\tstockmoves.trandate,\n\t\t\t\t\tsystypes.typename,\n\t\t\t\t\tstockmoves.qty,\n\t\t\t\t\tstockmoves.debtorno,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t\t\t\tstockmoves.price*(1-stockmoves.discountpercent) as sellingprice,\n\t\t\t\t\tpurchdata.price as fxcost,\n\t\t\t\t\tsellthroughsupport.rebatepercent,\n\t\t\t\t\tsellthroughsupport.rebateamount\n\t\t\t\tFROM stockmaster INNER JOIN stockmoves\n\t\t\t\t\tON stockmaster.stockid=stockmoves.stockid\n\t\t\t\tINNER JOIN systypes\n\t\t\t\t\tON stockmoves.type=systypes.typeid\n\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\tON stockmoves.debtorno=debtorsmaster.debtorno\n\t\t\t\tINNER JOIN purchdata\n\t\t\t\t\tON purchdata.stockid = stockmaster.stockid\n\t\t\t\tINNER JOIN suppliers\n\t\t\t\t\tON suppliers.supplierid = purchdata.supplierno\n\t\t\t\tINNER JOIN sellthroughsupport\n\t\t\t\t\tON sellthroughsupport.supplierno=suppliers.supplierid\n\t\t\t\tINNER JOIN currencies\n\t\t\t\t\tON currencies.currabrev=suppliers.currcode\n\t\t\t\tWHERE stockmoves.trandate >= '" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\tAND stockmoves.trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'\n\t\t\t\tAND sellthroughsupport.effectivefrom <= stockmoves.trandate\n\t\t\t\tAND sellthroughsupport.effectiveto >= stockmoves.trandate\n\t\t\t\tAND (stockmoves.type=10 OR stockmoves.type=11)\n\t\t\t\tAND (sellthroughsupport.stockid=stockmoves.stockid OR sellthroughsupport.categoryid=stockmaster.categoryid)\n\t\t\t\tAND (sellthroughsupport.debtorno=stockmoves.debtorno OR sellthroughsupport.debtorno='')\n\t\t\t\tORDER BY sellthroughsupport.supplierno,\n\t\t\t\t\tstockmaster.stockid";
$ClaimsResult = DB_query($SQL, '', '', false, false);
if (DB_error_no() != 0) {
include 'includes/header.inc';
prnMsg(_('The sell through support items to claim could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($ClaimsResult) == 0) {
include 'includes/header.inc';
prnMsg(_('No sell through support items retrieved'), 'warn');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
$QOH = $QOHRow[0];
if ($_SESSION['CompanyRecord']['gllink_stock'] == 1) {
$CostUpdateNo = GetNextTransNo(35, $db);
$PeriodNo = GetPeriod(Date("d/m/Y"), $db);
$ValueOfChange = $QOH * ($ItemShipmentCost - $StdCostUnit);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES (35,\n\t\t\t\t\t\t\t" . $CostUpdateNo . ",\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t" . $StockGLCodes['adjglact'] . ",\n\t\t\t\t\t\t\t'" . _('Shipment of') . ' ' . $myrow['itemcode'] . " " . _('cost was') . ' ' . $StdCostUnit . ' ' . _('changed to') . ' ' . number_format($ItemShipmentCost, 2) . ' x ' . _('QOH of') . ' ' . $QOH . "', " . -$ValueOfChange . ")";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL credit for the shipment stock cost adjustment posting could not be inserted because') . ' ' . DB_error_msg($db);
$Result = DB_query($SQL, $db, $ErrMsg, '', TRUE);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES (35,\n\t\t\t\t\t\t\t" . $CostUpdateNo . ",\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t" . $StockGLCodes['stockact'] . ",\n\t\t\t\t\t\t\t'" . _('Shipment of') . ' ' . $myrow['itemcode'] . ' ' . _('cost was') . ' ' . $StdCostUnit . ' ' . _('changed to') . ' ' . number_format($ItemShipmentCost, 2) . ' x ' . _('QOH of') . ' ' . $QOH . "',\n " . $ValueOfChange . ")";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL debit for stock cost adjustment posting could not be inserted because') . ' ' . DB_error_msg($db);
$Result = DB_query($SQL, $db, $ErrMsg, '', TRUE);
}
/*end of GL entries for a standard cost update */
/* Only the material cost is important for imported items */
$sql = "UPDATE stockmaster SET materialcost=" . $ItemShipmentCost . ",\n\t\t\t\t\t\t\t\tlabourcost=0,\n\t\t\t\t\t\t\t\toverheadcost=0,\n\t\t\t\t\t\t\t\tlastcost=" . $StdCostUnit . "\n\t\t\t\t\t\tWHERE stockid='" . $myrow['itemcode'] . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The shipment cost details for the stock item could not be updated because') . ': ' . DB_error_msg($db);
$result = DB_query($sql, $db, $ErrMsg, '', TRUE);
}
// end of update cost code
}
// end of Close shipment item updates
/* Item / Qty Inv/ FX price/ Local Val/ Portion of chgs/ Shipt Cost/ Std Cost/ Variance/ Var % */
echo '<TD>' . $myrow['itemcode'] . ' - ' . $myrow['itemdescription'] . '</TD>
<TD ALIGN=RIGHT>' . number_format($myrow['totqtyinvoiced']) . '</TD>
<TD ALIGN=RIGHT>' . number_format($myrow['totqtyrecd']) . '</TD>
<TD ALIGN=RIGHT>' . number_format($ItemCharges) . '</TD>
<TD ALIGN=RIGHT>' . number_format($PortionOfCharges) . '</TD>
<TD ALIGN=RIGHT>' . number_format($ItemShipmentCost, 2) . '</TD>
<TD ALIGN=RIGHT>' . number_format($StdCostUnit, 2) . '</TD>
<TD ALIGN=RIGHT>' . number_format($Variance, 2) . '</TD>
<TD ALIGN=RIGHT>' . $VariancePercentage . '%</TD></TR>';
if ($_SESSION['DefaultDateFormat'] == 'd/m/Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[1] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
} elseif ($_SESSION['DefaultDateFormat'] == 'm/d/Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[0] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
} elseif ($_SESSION['DefaultDateFormat'] == 'Y/m/d') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[2] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[1]));
} elseif ($_SESSION['DefaultDateFormat'] == 'd.m.Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[1] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
}
$SQL = "SELECT supptrans.type,\n\t\t\tsupptrans.suppreference,\n\t\t\tsystypes.typename,\n\t\t\tsupptrans.trandate,\n\t\t\tsuppliers.suppname,\n \t\t\tsupptrans.ovamount/supptrans.rate AS netamount,\n\t\t\tsupptranstaxes.taxamount/supptrans.rate AS taxamt\n\t\tFROM supptrans\n\t\tINNER JOIN suppliers ON supptrans.supplierno=suppliers.supplierid\n\t\tINNER JOIN systypes ON supptrans.type=systypes.typeid\n\t\tINNER JOIN supptranstaxes ON supptrans.id = supptranstaxes.supptransid\n\t\tWHERE supptrans.trandate >= '" . $StartDateSQL . "'\n\t\tAND supptrans.trandate <= '" . FormatDateForSQL($PeriodEnd) . "'\n\t\tAND (supptrans.type=20 OR supptrans.type=21)\n\t\tAND supptranstaxes.taxauthid = '" . $_POST['TaxAuthority'] . "'\n\t\tORDER BY supptrans.trandate";
$SuppTransResult = DB_query($SQL, $db, '', '', false, false);
//doint trap errors in DB_query
if (DB_error_no($db) != 0) {
$title = _('Taxation Reporting Error');
include 'includes/header.inc';
echo _('The accounts payable transaction details could not be retrieved because') . ' ' . DB_error_msg($db);
echo '<br /><a href="' . $rootpath . '/index.php?">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if ($_POST['DetailOrSummary'] == 'Detail') {
include 'includes/PDFTaxPageHeader.inc';
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 120, $FontSize + 2, _('Tax On Purchases'), 'left');
$YPos -= $line_height;
}
while ($SuppTransRow = DB_fetch_array($SuppTransResult, $db)) {
$ListCount++;
if ($_POST['DetailOrSummary'] == 'Detail') {
<td>' . _('Exchange Rate') . ':</td>
<td><input type="text" class="number" title="' . _('The input must be number') . '" name="ExRate" maxlength="11" size="12" value="' . locale_number_format($DefaultExRate, 'Variable') . '" /></td>
</tr>';
if (!isset($_POST['AmountsDueBy'])) {
$DefaultDate = Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m') + 1, 0, Date('y')));
} else {
$DefaultDate = $_POST['AmountsDueBy'];
}
echo '<tr>
<td>' . _('Payments Due To') . ':</td>
<td><input type="text" class="date" alt="' . $_SESSION['DefaultDateFormat'] . '" name="AmountsDueBy" maxlength="11" size="12" value="' . $DefaultDate . '" /></td>
</tr>';
$SQL = "SELECT bankaccountname, accountcode FROM bankaccounts";
$AccountsResults = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
echo '<br />' . _('The bank accounts could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db);
if ($debug == 1) {
echo '<br />' . _('The SQL used to retrieve the bank accounts was') . ':<br />' . $SQL;
}
exit;
}
echo '<tr>
<td>' . _('Pay From Account') . ':</td>
<td><select name="BankAccount">';
if (DB_num_rows($AccountsResults) == 0) {
echo '</select></td>
</tr>
</table>
<p>' . _('Bank Accounts have not yet been defined. You must first') . ' <a href="' . $RootPath . '/BankAccounts.php">' . _('define the bank accounts') . '</a> ' . _('and general ledger accounts to be affected') . '.
</p>';
include 'includes/footer.inc';
$pdf->addInfo('Title', _('MRP Reschedule Report'));
$pdf->addInfo('Subject', _('MRP Reschedules'));
$FontSize = 9;
$PageNumber = 1;
$line_height = 12;
/*Find mrpsupplies records where the duedate is not the same as the mrpdate */
$selecttype = " ";
if ($_POST['Selection'] != 'All') {
$selecttype = " AND ordertype = '" . $_POST['Selection'] . "'";
}
$sql = "SELECT mrpsupplies.*,\n\t\t\t\t stockmaster.description,\n\t\t\t\t stockmaster.decimalplaces\n\t\t\t FROM mrpsupplies,stockmaster\n\t\t\t WHERE mrpsupplies.part = stockmaster.stockid AND duedate <> mrpdate\n\t\t\t\t {$selecttype}\n\t\t\t ORDER BY mrpsupplies.part";
$result = DB_query($sql, $db, '', '', false, true);
if (DB_error_no($db) != 0) {
$Title = _('MRP Reschedules') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The MRP reschedules could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($result) == 0) {
$Title = _('MRP Reschedules') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('No MRP reschedule retrieved'), 'warn');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $sql;
}
if ($i > 0) {
$SQL .= " OR ";
}
$i++;
$SQL .= "custbranch.salesman='" . $Salesperson . "'";
}
$SQL .= ") ORDER BY custbranch.area,\n\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\tdebtorsmaster.debtorno,\n\t\t\t\t\tcustbranch.branchcode";
}
/*end if Salesfolk =='All' */
}
/* end if not all sales areas was selected */
$CustomersResult = DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Title = _('Customer List') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The customer List could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db));
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($CustomersResult) == 0) {
$Title = _('Customer List') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('This report has no output because there were no customers retrieved'), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
include 'includes/header.inc';
prnMsg(_('The sales order demand quantities could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo '<br><a href="' . $rootpath . '/index.php?' . SID . '">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br>' . $SQL;
}
echo '</body</html>';
exit;
}
$DemandRow = DB_fetch_array($DemandResult);
$DemandQty = $DemandRow['qtydemand'];
//Also need to add in the demand for components of assembly items
$sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity)\n \t\t AS dem\n\t\t FROM salesorderdetails,\n \t\t salesorders,\n\t\t bom,\n \t\t stockmaster\n\t\t WHERE salesorderdetails.stkcode=bom.parent AND\n \t\t salesorders.orderno = salesorderdetails.orderno AND\n\t\t salesorders.fromstkloc='" . $myrow['loccode'] . "' AND\n \t\t salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0 AND\n\t\t bom.component='" . $StockID . "' AND\n \t\t stockmaster.stockid=bom.parent AND\n\t\t stockmaster.mbflag='A'";
$DemandResult = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
prnMsg(_('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because') . ' - ' . DB_error_msg($db), 'error');
if ($debug == 1) {
echo '<br>' . _('The SQL that failed was') . ' ' . $sql;
}
exit;
}
if (DB_num_rows($DemandResult) == 1) {
$DemandRow = DB_fetch_row($DemandResult);
$DemandQty += $DemandRow[0];
}
$LeftOvers = $pdf->addTextWrap(350, $YPos, 60, $FontSize, $InventoryPlan['qoh'], 'right');
$LeftOvers = $pdf->addTextWrap(410, $YPos, 60, $FontSize, number_format($DemandQty, 0), 'right');
$LeftOvers = $pdf->addTextWrap(470, $YPos, 60, $FontSize, number_format($InventoryPlan['qoh'] - $DemandQty, 0), 'right');
}
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 150, $FontSize, $InventoryPlan['stockid'], 'left');
$LeftOvers = $pdf->addTextWrap(150, $YPos, 200, $FontSize, $InventoryPlan['description'], 'left');
prnMsg(_('Cannot Enter this order line') . '<br />' . _('The price entered must be numeric'), 'error');
}
if (!Is_Date($_POST['ReqDelDate'])) {
$AllowUpdate = False;
prnMsg(_('Cannot Enter this order line') . '</b><br />' . _('The date entered must be in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
}
/*It's not a stock item */
/*need to check GL Code is valid if GLLink is active */
if ($_SESSION['PO' . $identifier]->GLLink == 1) {
$sql = "SELECT accountname\n\t\t\t\tFROM chartmaster\n\t\t\t\tWHERE accountcode ='" . $_POST['GLCode'] . "'";
$ErrMsg = _('The account details for') . ' ' . $_POST['GLCode'] . ' ' . _('could not be retrieved because');
$DbgMsg = _('The SQL used to retrieve the details of the account, but failed was');
$GLValidResult = DB_query($sql, $db, $ErrMsg, $DbgMsg, false, false);
if (DB_error_no($db) != 0) {
$AllowUpdate = false;
prnMsg(_('The validation process for the GL Code entered could not be executed because') . ' ' . DB_error_msg($db), 'error');
if ($debug == 1) {
prnMsg(_('The SQL used to validate the code entered was') . ' ' . $sql, 'error');
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($GLValidResult) == 0) {
/*The GLCode entered does not exist */
$AllowUpdate = false;
prnMsg(_('Cannot enter this order line') . ':<br />' . _('The general ledger code') . ' - ' . $_POST['GLCode'] . ' ' . _('is not a general ledger code that is defined in the chart of accounts') . ' . ' . _('Please use a code that is already defined') . '. ' . _('See the Chart list from the link below'), 'error');
} else {
$myrow = DB_fetch_row($GLValidResult);
$GLAccountName = $myrow[0];
}
} else {
$FontSize = 10;
$PageNumber = 1;
$line_height = 12;
$Title = _('Low GP sales') . ' - ' . _('Problem Report');
if (!Is_Date($_POST['FromDate']) or !Is_Date($_POST['ToDate'])) {
include 'includes/header.inc';
prnMsg(_('The dates entered must be in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
include 'includes/footer.inc';
exit;
}
/*Now figure out the data to report for the category range under review */
$SQL = "SELECT stockmaster.categoryid,\n\t\t\t\t\tstockmaster.stockid,\n\t\t\t\t\tstockmoves.transno,\n\t\t\t\t\tstockmoves.trandate,\n\t\t\t\t\tsystypes.typename,\n\t\t\t\t\tstockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost as unitcost,\n\t\t\t\t\tstockmoves.qty,\n\t\t\t\t\tstockmoves.debtorno,\n\t\t\t\t\tstockmoves.branchcode,\n\t\t\t\t\tstockmoves.price*(1-stockmoves.discountpercent) as sellingprice,\n\t\t\t\t\t(stockmoves.price*(1-stockmoves.discountpercent)) - (stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost) AS gp,\n\t\t\t\t\tdebtorsmaster.name\n\t\t\t\tFROM stockmaster INNER JOIN stockmoves\n\t\t\t\t\tON stockmaster.stockid=stockmoves.stockid\n\t\t\t\tINNER JOIN systypes\n\t\t\t\t\tON stockmoves.type=systypes.typeid\n\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\tON stockmoves.debtorno=debtorsmaster.debtorno\n\t\t\t\tWHERE stockmoves.trandate >= '" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\tAND stockmoves.trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'\n\t\t\t\tAND ((stockmoves.price*(1-stockmoves.discountpercent)) - (stockmaster.materialcost + stockmaster.labourcost + stockmaster.overheadcost))/(stockmoves.price*(1-stockmoves.discountpercent)) <=" . $_POST['GPMin'] / 100 . "\n\t\t\t\tORDER BY stockmaster.stockid";
$LowGPSalesResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
include 'includes/header.inc';
prnMsg(_('The low GP items could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($LowGPSalesResult) == 0) {
include 'includes/header.inc';
prnMsg(_('No low GP items retrieved'), 'warn');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
$LeftOvers = $pdf->addTextWrap(460, $YPos, 60, $FontSize, $DisplayOverdue2, 'right');
$YPos -= $line_height;
if ($YPos < $Bottom_Margin + $line_height) {
include 'includes/PDFAgedSuppliersPageHeader.inc';
}
if ($_POST['DetailedReport'] == 'Yes') {
$FontSize = 6;
/*draw a line under the Supplier aged analysis*/
$pdf->line($Page_Width - $Right_Margin, $YPos + 10, $Left_Margin, $YPos + 10);
$sql = "SELECT systypes.typename,\n\t\t\t\t\t\t\tsupptrans.suppreference,\n\t\t\t\t\t\t\tsupptrans.trandate,\n\t\t\t\t\t\t\t(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) as balance,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS due,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS overdue1,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS overdue2\n\t\t\t\t\t\tFROM suppliers\n\t\t\t\t\t\tLEFT JOIN paymentterms\n\t\t\t\t\t\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t\t\t\t\t\tLEFT JOIN supptrans\n\t\t\t\t\t\t\tON suppliers.supplierid = supptrans.supplierno\n\t\t\t\t\t\tLEFT JOIN systypes\n\t\t\t\t\t\t\tON systypes.typeid = supptrans.type\n\t\t\t\t\t\tWHERE ABS(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) >0.009\n\t\t\t\t\t\t\tAND supptrans.settled = 0\n\t\t\t\t\t\t\tAND supptrans.supplierno = '" . $AgedAnalysis["supplierid"] . "'";
$DetailResult = DB_query($sql, $db, '', '', False, False);
/*dont trap errors - trapped below*/
if (DB_error_no($db) != 0) {
$Title = _('Aged Supplier Account Analysis - Problem Report');
include 'includes/header.inc';
prnMsg(_('The details of outstanding transactions for Supplier') . ' - ' . $AgedAnalysis['supplierid'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
while ($DetailTrans = DB_fetch_array($DetailResult)) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 5, $YPos, 60, $FontSize, $DetailTrans['typename'], 'left');
$LeftOvers = $pdf->addTextWrap($Left_Margin + 65, $YPos, 50, $FontSize, $DetailTrans['suppreference'], 'left');
$DisplayTranDate = ConvertSQLDate($DetailTrans['trandate']);
$LeftOvers = $pdf->addTextWrap($Left_Margin + 105, $YPos, 70, $FontSize, $DisplayTranDate, 'left');
$DisplayDue = locale_number_format($DetailTrans['due'] - $DetailTrans['overdue1'], $CurrDecimalPlaces);
$DisplayCurrent = locale_number_format($DetailTrans['balance'] - $DetailTrans['due'], $CurrDecimalPlaces);
$DisplayBalance = locale_number_format($DetailTrans['balance'], $CurrDecimalPlaces);
stockmaster.stockid,
stockmaster.description,
stockmaster.mbflag,
stockmaster.decimalplaces,
stockmaster.actualcost,
stockmaster.materialcost,
stockmaster.labourcost,
stockmaster.overheadcost,
computedcost
ORDER BY mrpplannedorders.part,yearmonth ';
}
$result = DB_query($sql, $db, '', '', false, true);
if (DB_error_no($db) != 0) {
$title = _('MRP Planned Purchase Orders') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The MRP planned purchase orders could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo "<br><a href='" . $rootpath . '/index.php?' . SID . "'>" . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo "<br>{$sql}";
}
include 'includes/footer.inc';
exit;
}
//include ('includes/MRPPlannedPurchaseOrdersPageHeader.inc');
PrintHeader($pdf, $YPos, $PageNumber, $Page_Height, $Top_Margin, $Left_Margin, $Page_Width, $Right_Margin, $_POST['Consolidation'], $reportdate);
$Total_Shortage = 0;
$Partctr = 0;
$fill = false;
$pdf->SetFillColor(224, 235, 255);
// Defines color to make alternating lines highlighted
$FontSize = 8;
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
// Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
if ($_POST['Location'] == 'All') {
$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem\n\t\t\t\tFROM salesorderdetails INNER JOIN bom\n\t\t\t\t\tON salesorderdetails.stkcode=bom.parent\n\t\t\t\t\tINNER JOIN\tstockmaster\n\t\t\t\t\tON stockmaster.stockid=bom.parent\n\t\t\t\t\tINNER JOIN salesorders\n\t\t\t\t\tON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\tWHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND bom.component='" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND stockmaster.mbflag='A'\n\t\t\t\tAND salesorderdetails.completed=0\n\t\t\t\tAND salesorders.quotation=0";
} else {
$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem\n\t\t\t\tFROM salesorderdetails INNER JOIN bom\n\t\t\t\t\tON salesorderdetails.stkcode=bom.parent\n\t\t\t\t\tINNER JOIN\tstockmaster\n\t\t\t\t\tON stockmaster.stockid=bom.parent\n\t\t\t\t\tINNER JOIN salesorders\n\t\t\t\t\tON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\tWHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND bom.component='" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND stockmaster.stockid=bom.parent\n\t\t\t\tAND salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\t\t\tAND stockmaster.mbflag='A'\n\t\t\t\tAND salesorderdetails.completed=0\n\t\t\t\tAND salesorders.quotation=0";
}
$BOMDemandResult = DB_query($SQL, '', '', false, false);
if (DB_error_no() != 0) {
$Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The sales order demand quantities from parent assemblies could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
// Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
// Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
if ($_POST['Location'] == 'All') {
$QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], '');
$QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], '');
} else {
$QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], $_POST['Location']);
$QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], $_POST['Location']);
echo " <INPUT TYPE=SUBMIT NAME='ShowUsage' VALUE='" . _('Show Stock Usage') . "'>";
echo " <INPUT TYPE=SUBMIT NAME='ShowGraphUsage' VALUE='" . _('Show Graph Of Stock Usage') . "'>";
echo '<HR>';
/* $_SESSION['NumberOfPeriodsOfStockUsage'] is defined in config.php as a user definable variable
config.php is loaded by header.inc */
/*HideMovt ==1 if the movement was only created for the purpose of a transaction but is not a physical movement eg. A price credit will create a movement record for the purposes of display on a credit note
but there is no physical stock movement - it makes sense honest ??? */
if (isset($_POST['ShowUsage'])) {
if ($_POST['StockLocation'] == 'All') {
$sql = "SELECT periods.periodno, \n\t\t\t\tperiods.lastdate_in_period, \n\t\t\t\tSUM(-stockmoves.qty) AS qtyused \n\t\t\tFROM stockmoves INNER JOIN periods \n\t\t\t\tON stockmoves.prd=periods.periodno \n\t\t\tWHERE (stockmoves.type=10 OR stockmoves.type=11 OR stockmoves.type=28) \n\t\t\tAND stockmoves.hidemovt=0 \n\t\t\tAND stockmoves.stockid = '" . $StockID . "' \n\t\t\tGROUP BY periods.periodno, \n\t\t\t\tperiods.lastdate_in_period \n\t\t\tORDER BY periodno DESC LIMIT " . $_SESSION['NumberOfPeriodsOfStockUsage'];
} else {
$sql = "SELECT periods.periodno, \n\t\t\t\tperiods.lastdate_in_period, \n\t\t\t\tSUM(-stockmoves.qty) AS qtyused \n\t\t\tFROM stockmoves INNER JOIN periods \n\t\t\t\tON stockmoves.prd=periods.periodno \n\t\t\tWHERE (stockmoves.type=10 Or stockmoves.type=11 OR stockmoves.type=28) \n\t\t\tAND stockmoves.hidemovt=0 \n\t\t\tAND stockmoves.loccode='" . $_POST['StockLocation'] . "' \n\t\t\tAND stockmoves.stockid = '" . $StockID . "' \n\t\t\tGROUP BY periods.periodno, \n\t\t\t\tperiods.lastdate_in_period \n\t\t\tORDER BY periodno DESC LIMIT " . $_SESSION['NumberOfPeriodsOfStockUsage'];
}
$MovtsResult = DB_query($sql, $db);
if (DB_error_no($db) != 0) {
echo _('The stock usage for the selected criteria could not be retrieved because') . ' - ' . DB_error_msg($db);
if ($debug == 1) {
echo '<BR>' . _('The SQL that failed was') . $sql;
}
exit;
}
echo '<TABLE CELLPADDING=2 BORDER=0>';
$tableheader = "<TR><TH>" . _('Month') . "</TH><TH>" . _('Usage') . '</TH></TR>';
echo $tableheader;
$j = 1;
$k = 0;
//row colour counter
$TotalUsage = 0;
$PeriodsCounter = 0;
while ($myrow = DB_fetch_array($MovtsResult)) {
if ($k == 1) {
echo '<br />
<a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
$AccumProfitRow = DB_fetch_array($AccumProfitResult);
/*should only be one row returned */
$SQL = "SELECT accountgroups.sectioninaccounts,\n\t\t\taccountgroups.groupname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\tchartdetails.accountcode ,\n\t\t\tchartmaster.accountname,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['BalancePeriodEnd'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS balancecfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . ($_POST['BalancePeriodEnd'] - 12) . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lybalancecfwd\n\t\tFROM chartmaster INNER JOIN accountgroups\n\t\tON chartmaster.group_ = accountgroups.groupname INNER JOIN chartdetails\n\t\tON chartmaster.accountcode= chartdetails.accountcode\n\t\tWHERE accountgroups.pandl=0\n\t\tGROUP BY accountgroups.groupname,\n\t\t\tchartdetails.accountcode,\n\t\t\tchartmaster.accountname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.sectioninaccounts\n\t\tORDER BY accountgroups.sectioninaccounts,\n\t\t\taccountgroups.sequenceintb,\n\t\t\taccountgroups.groupname,\n\t\t\tchartdetails.accountcode";
$AccountsResult = DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Title = _('Balance Sheet') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('No general ledger accounts were returned by the SQL because') . ' - ' . DB_error_msg($db));
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
$ListCount = DB_num_rows($AccountsResult);
// UldisN
include 'includes/PDFBalanceSheetPageHeader.inc';
$k = 0;
//row colour counter
$Section = '';
$SectionBalance = 0;
$SectionBalanceLY = 0;
$SQL = "SELECT salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverto, \n\t\t\t\t\t\tsalesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue \n\t\t\t\t\tFROM salesorders, \n\t\t\t\t\t\tsalesorderdetails, \n\t\t\t\t\t\tdebtorsmaster, \n\t\t\t\t\t\tcustbranch \n\t\t\t\t\tWHERE salesorders.orderno = salesorderdetails.orderno \n\t\t\t\t\tAND salesorders.debtorno = debtorsmaster.debtorno \n\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode \n\t\t\t\t\tAND debtorsmaster.debtorno = custbranch.debtorno \n\t\t\t\t\tAND salesorders.debtorno='" . $SelectedCustomer . "' \n\t\t\t\t\tAND salesorders.orddate >= '" . $DateAfterCriteria . "' \n\t\t\t\t\tAND salesorders.quotation=0 \n\t\t\t\t\tAND salesorderdetails.completed" . $completed . " \n\t\t\t\t\tGROUP BY salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverydate, \n\t\t\t\t\t\tsalesorders.deliverto\n\t\t\t\t\tORDER BY salesorders.orderno";
}
} else {
//no customer selected
if (isset($SelectedStockItem)) {
$SQL = "SELECT salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverto, \n\t\t\t\t\t\tsalesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue \n\t\t\t\t\tFROM salesorders, \n\t\t\t\t\t\tsalesorderdetails, \n\t\t\t\t\t\tdebtorsmaster, \n\t\t\t\t\t\tcustbranch \n\t\t\t\t\tWHERE salesorders.orderno = salesorderdetails.orderno \n\t\t\t\t\tAND salesorders.debtorno = debtorsmaster.debtorno \n\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode \n\t\t\t\t\tAND debtorsmaster.debtorno = custbranch.debtorno \n\t\t\t\t\tAND salesorderdetails.stkcode='" . $SelectedStockItem . "' \n\t\t\t\t\tAND salesorders.orddate >= '" . $DateAfterCriteria . "' \n\t\t\t\t\tAND salesorders.quotation=0 \n\t\t\t\t\tAND salesorderdetails.completed" . $completed . " \n\t\t\t\t\tGROUP BY salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverydate, \n\t\t\t\t\t\tsalesorders.deliverto\n\t\t\t\t\tORDER BY salesorders.orderno";
} else {
$SQL = "SELECT salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverto, \n\t\t\t\t\t\tsalesorders.deliverydate, SUM(salesorderdetails.unitprice*salesorderdetails.quantity*(1-salesorderdetails.discountpercent)) AS ordervalue \n\t\t\t\t\tFROM salesorders, \n\t\t\t\t\t\tsalesorderdetails, \n\t\t\t\t\t\tdebtorsmaster, \n\t\t\t\t\t\tcustbranch \n\t\t\t\t\tWHERE salesorders.orderno = salesorderdetails.orderno \n\t\t\t\t\tAND salesorders.debtorno = debtorsmaster.debtorno \n\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode \n\t\t\t\t\tAND debtorsmaster.debtorno = custbranch.debtorno \n\t\t\t\t\tAND salesorders.orddate >= '{$DateAfterCriteria}' \n\t\t\t\t\tAND salesorders.quotation=0 \n\t\t\t\t\tAND salesorderdetails.completed" . $completed . " \n\t\t\t\t\tGROUP BY salesorders.orderno, \n\t\t\t\t\t\tdebtorsmaster.name, \n\t\t\t\t\t\tcustbranch.brname, \n\t\t\t\t\t\tsalesorders.customerref, \n\t\t\t\t\t\tsalesorders.orddate, \n\t\t\t\t\t\tsalesorders.deliverydate, \n\t\t\t\t\t\tsalesorders.deliverto\n\t\t\t\t\tORDER BY salesorders.orderno";
}
}
//end selected customer
}
//end not order number selected
$SalesOrdersResult = DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
echo '<br>' . _('No orders were returned by the SQL because') . ' ' . DB_error_msg($db);
echo "<br>{$SQL}";
}
}
}
//end of which button clicked options
if (!isset($_POST['OrdersAfterDate']) or $_POST['OrdersAfterDate'] == '' or !Is_Date($_POST['OrdersAfterDate'])) {
$_POST['OrdersAfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m') - 2, Date('d'), Date('Y')));
}
echo "<table>";
if (!isset($OrderNumber) or $OrderNumber == '') {
echo '<tr><td>' . _('Order Number') . ':</td><td>' . "<input type='text' name='OrderNumber' maxlength =8 size=9></td><td>" . _('for all orders placed after') . ": </td><td><input type='text' name='OrdersAfterDate' maxlength =10 size=11 value=" . $_POST['OrdersAfterDate'] . "></td><td>" . "<input type='submit' name='SearchOrders' value='" . _('Search Orders') . "'></td></tr>";
echo '<tr><td>' . _('Customer Ref') . ':</td><td>' . "<input type='text' name='CustomerRef' maxlength =8 size=9></td>\n\t\t\t<td></td><td colspan=2><input type='checkbox' " . $ShowChecked . " name='completed' />" . _('Show Completed orders only') . "</td></tr>";
}
echo '</table>';
if (!isset($SelectedStockItem)) {
sortpart text)';
$result = DB_query($sql, $db);
$sql = "INSERT INTO passbom (part, sortpart)\n\t\t\t\t\t SELECT bom.component AS part,\n\t\t\t\t\t\t\t CONCAT(passbom2.sortpart,bom.component) AS sortpart\n\t\t\t\t\t FROM bom,passbom2 \n\t\t\t\t\t WHERE bom.parent = passbom2.part\n\t\t\t\t\t\tAND bom.effectiveto >= NOW() AND bom.effectiveafter <= NOW()";
$result = DB_query($sql, $db);
$sql = 'SELECT COUNT(*) FROM bom,passbom WHERE bom.parent = passbom.part';
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$componentctr = $myrow[0];
}
// End of while $componentctr > 0
}
// End of if $_POST['Levels']
if (DB_error_no($db) != 0) {
$title = _('Indented BOM Listing') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The Indented BOM Listing could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo "<br><a href='" . $rootpath . '/index.php?' . SID . "'>" . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo "<br>{$sql}";
}
include 'includes/footer.inc';
exit;
}
$sql = 'SELECT stockmaster.stockid,
stockmaster.description
FROM stockmaster
WHERE stockid = ' . "'" . $_POST['Part'] . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_array($result, $db);
$assembly = $_POST['Part'];
$assemblydesc = $myrow['description'];
$i = 0;
$SearchString = '%';
while (strpos($_POST['Keywords'], ' ', $i)) {
$wrdlen = strpos($_POST['Keywords'], ' ', $i) - $i;
$SearchString = $SearchString . substr($_POST['Keywords'], $i, $wrdlen) . '%';
$i = strpos($_POST['Keywords'], ' ', $i) + 1;
}
$SearchString = $SearchString . substr($_POST['Keywords'], $i) . '%';
$SQL .= " WHERE purchorderdetails.shiptref IS NOT NULL\n\t\t\tAND purchorderdetails.shiptref<>0\n\t\t\tAND stockmaster.description " . LIKE . " '{$SearchString}'\n\t\t\tAND categoryid='" . $_POST['StockCat'] . "'";
} elseif ($_POST['StockCode']) {
$SQL .= " WHERE purchorderdetails.shiptref IS NOT NULL\n\t\t\tAND purchorderdetails.shiptref<>0\n\t\t\tAND stockmaster.stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'\n\t\t\tAND categoryid='" . $_POST['StockCat'];
} elseif (!$_POST['StockCode'] and !$_POST['Keywords']) {
$SQL .= " WHERE purchorderdetails.shiptref IS NOT NULL\n\t\t\tAND purchorderdetails.shiptref<>0\n\t\t\tAND stockmaster.categoryid='" . $_POST['StockCat'] . "'";
}
$SQL .= " GROUP BY stockmaster.stockid,\n\t\t\t\tstockmaster.description,\n\t\t\t\tstockmaster.units\n\t\t \tORDER BY stockmaster.stockid";
$ErrMsg = _('No Stock Items were returned from the database because') . ' - ' . DB_error_msg($db);
$StockItemsResult = DB_query($SQL, $db, $ErrMsg);
}
if (!isset($ShiptRef) or $ShiptRef == "") {
echo '<div class="centre">';
echo _('Shipment Number') . ': <input type=text name="ShiptRef" MAXLENGTH =10 size=10> ' . _('Into Stock Location') . ' :<select name="StockLocation"> ';
$sql = "SELECT loccode, locationname FROM locations";
$resultStkLocs = DB_query($sql, $db);
while ($myrow = DB_fetch_array($resultStkLocs)) {
if (isset($_POST['StockLocation'])) {
if ($myrow['loccode'] == $_POST['StockLocation']) {
echo '<option selected Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
} else {
echo '<option Value="' . $myrow['loccode'] . '">' . $myrow['locationname'];
}
} elseif ($myrow['loccode'] == $_SESSION['UserStockLocation']) {
