function GetReports($GroupID)
{
global $db, $rootpath;
$Title = array(_('Custom Reports'), _('Default Reports'));
$RptForm = '<form name="ReportList" method="post" action="' . $rootpath . '/reportwriter/ReportMaker.php?action=go">';
$RptForm .= '<table align="center" border="0" cellspacing="1" cellpadding="1" class="table_index">';
for ($Def = 1; $Def >= 0; $Def--) {
$RptForm .= '<tr><td colspan="2"><div align="center">' . $Title[$Def] . '</div></td></tr>';
$sql = "SELECT id, reportname FROM reports \n\t\t\tWHERE defaultreport='" . $Def . "' AND groupname='" . $GroupID . "' \n\t\t\tORDER BY reportname";
$Result = DB_query($sql, $db, '', '', false, true);
if (DB_num_rows($Result) > 0) {
$RptForm .= '<tr><td><select name="ReportID" size="10" onChange="submit()">';
while ($Temp = DB_fetch_array($Result)) {
$RptForm .= '<option value="' . $Temp['id'] . '">' . $Temp['reportname'] . '</option>';
}
$RptForm .= '</select></td></tr>';
} else {
$RptForm .= '<tr><td colspan="2">' . _('There are no reports to show!') . '</td></tr>';
}
}
$RptForm .= '</table></form>';
return $RptForm;
}
function db($user, $password)
{
$_SESSION['UserID'] = $user;
$sql = "SELECT userid,\n\t\t\t\t\t\taccesslevel\n\t\t\t\tFROM www_users\n\t\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\t\tAND (password='******'\n\t\t\t\tOR password='******')";
$Auth_Result = DB_query($sql, $_SESSION['db']);
$myrow = DB_fetch_row($Auth_Result);
if (DB_num_rows($Auth_Result) > 0) {
$sql = 'SELECT tokenid FROM securitygroups
WHERE secroleid = ' . $_SESSION['AccessLevel'];
$Sec_Result = DB_query($sql, $db);
$_SESSION['AllowedPageSecurityTokens'] = array();
if (DB_num_rows($Sec_Result) == 0) {
return NoAuthorisation;
} else {
$i = 0;
while ($myrow = DB_fetch_row($Sec_Result)) {
$_SESSION['AllowedPageSecurityTokens'][$i] = $myrow[0];
$i++;
}
}
return $_SESSION['db'];
} else {
return NoAuthorisation;
}
}
function checkSupplierExist($codeSupplier)
{
global $db;
$result = DB_query("SELECT supplierid FROM suppliers WHERE supplierid='" . $codeSupplier . "'", $db);
if (DB_num_rows($result) == 0) {
return false;
}
return true;
}
function backup_tables($host, $user, $pass, $tables = '*', $db)
{
//get all of the tables
if ($tables == '*') {
$tables = array();
$result = DB_query('SHOW TABLES', $db);
while ($row = DB_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
//cycle through
foreach ($tables as $table) {
$result = DB_query('SELECT * FROM ' . $table, $db);
$num_fields = DB_num_fields($result);
$num_rows = DB_num_rows($result);
$return .= 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = DB_fetch_row(DB_query('SHOW CREATE TABLE ' . $table, $db));
$return .= "\n\n" . $row2[1] . ";\n\n";
$return .= 'INSERT INTO ' . $table . ' VALUES';
for ($i = 0; $i < $num_fields; $i++) {
$last = 0;
while ($row = DB_fetch_row($result)) {
$last = $last + 1;
$return .= '(';
for ($j = 0; $j < $num_fields; $j++) {
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace("\n", "\\n", $row[$j]);
if (isset($row[$j])) {
$return .= '"' . $row[$j] . '"';
} else {
$return .= '""';
}
if ($j < $num_fields - 1 and isset($row[$j])) {
$return .= ',';
}
}
if ($last == $num_rows) {
$return .= ");\n";
} else {
$return .= "),";
}
}
}
$return .= "\n\n\n";
}
//save file
$handle = fopen('db-backup-' . time() . '-' . md5(implode(',', $tables)) . '.sql', 'w+');
fwrite($handle, $return);
fclose($handle);
prnMsg(_(' back up successful'), 'success');
}
function GetExpiryDate($StockID, $LocCode, $BundleRef)
{
global $db;
$SQL = "SELECT expirationdate \n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid = '" . $StockID . "'\n\t\t\t\tAND loccode = '" . $LocCode . "'\n\t\t\t\tAND serialno = '" . $BundleRef . "'";
$Result = DB_query($SQL);
if (DB_num_rows($Result) == 0) {
return '0000-00-00';
} else {
$myrow = DB_fetch_row($Result);
return ConvertSQLDate($myrow[0]);
}
}
function db($user, $password)
{
$_SESSION['UserID'] = $user;
$sql = "SELECT userid\n\t\t\tFROM www_users\n\t\t\tWHERE userid='" . DB_escape_string($user) . "'\n\t\t\tAND (password='******'\n\t\t\tOR password='******')";
$Auth_Result = DB_query($sql, $_SESSION['db']);
$myrow = DB_fetch_row($Auth_Result);
if (DB_num_rows($Auth_Result) > 0) {
return $_SESSION['db'];
} else {
return NoAuthorisation;
}
}
function ValidBundleRef($StockID, $LocCode, $BundleRef)
{
global $db;
$SQL = "SELECT quantity\n\t\t\t\tFROM stockserialitems\n\t\t\t\tWHERE stockid='" . $StockID . "'\n\t\t\t\tAND loccode ='" . $LocCode . "'\n\t\t\t\tAND serialno='" . $BundleRef . "'";
$Result = DB_query($SQL, $db);
if (DB_num_rows($Result) == 0) {
return 0;
} else {
$myrow = DB_fetch_row($Result);
return $myrow[0];
/*The quantity in the bundle */
}
}
function Receipt($Amt, $Cust, $Disc, $Narr, $id, $GLCode, $PayeeBankDetail, $CustomerName, $Tag)
{
global $db;
/* Constructor function to add a new Receipt object with passed params */
$this->Amount = $Amt;
$this->Customer = $Cust;
$this->CustomerName = $CustomerName;
$this->Discount = $Disc;
$this->Narrative = $Narr;
$this->GLCode = $GLCode;
$this->PayeeBankDetail = $PayeeBankDetail;
$this->ID = $id;
$this->tag = $Tag;
$result = DB_query("SELECT tagdescription FROM tags WHERE tagref='" . $Tag . "'");
if (DB_num_rows($result) == 1) {
$TagRow = DB_fetch_array($result);
$this->TagName = $TagRow['tagdescription'];
}
}
function GetRptLinks($GroupID)
{
global $db, $RootPath;
$Title = array(_('Custom Reports'), _('Standard Reports'));
$RptLinks = '';
for ($Def = 1; $Def >= 0; $Def--) {
$RptLinks .= '<tr><td class="menu_group_headers"><div align="center">' . $Title[$Def] . '</div></td></tr>';
$sql = "SELECT id, reportname FROM reports\n\t\t\tWHERE defaultreport='" . $Def . "' AND groupname='" . $GroupID . "'\n\t\t\tORDER BY reportname";
$Result = DB_query($sql, $db, '', '', false, true);
if (DB_num_rows($Result) > 0) {
while ($Temp = DB_fetch_array($Result)) {
$RptLinks .= '<tr><td class="menu_group_item">';
$RptLinks .= '<a href="' . $RootPath . '/reportwriter/ReportMaker.php?action=go&reportid=' . $Temp['id'] . '"><li>' . _($Temp['reportname']) . '</li></a>';
$RptLinks .= '</td></tr>';
}
} else {
$RptLinks .= '<tr><td class="menu_group_item">' . _('There are no reports to show!') . '</td></tr>';
}
}
return $RptLinks;
}
function CheckForRecursiveBOM($UltimateParent, $ComponentToCheck, $db)
{
/* returns true ie 1 if the BOM contains the parent part as a component
ie the BOM is recursive otherwise false ie 0 */
$sql = "SELECT component FROM bom WHERE parent='" . $ComponentToCheck . "'";
$ErrMsg = _('An error occurred in retrieving the components of the BOM during the check for recursion');
$DbgMsg = _('The SQL that was used to retrieve the components of the BOM and that failed in the process was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
if (DB_num_rows($result) != 0) {
while ($myrow = DB_fetch_array($result)) {
if ($myrow['component'] == $UltimateParent) {
return 1;
}
if (CheckForRecursiveBOM($UltimateParent, $myrow['component'], $db)) {
return 1;
}
}
//(while loop)
}
//end if $result is true
return 0;
}
<td><input type="text" class="number" name="Pansize" size="6" maxlength="6" value="' . locale_number_format($_POST['Pansize'], 0) . '" /></td>
</tr>
<tr>
<td>' . _('Shrinkage Factor') . ':</td>
<td><input type="text" class="number" name="ShrinkFactor" size="6" maxlength="6" value="' . locale_number_format($_POST['ShrinkFactor'], 0) . '" /></td>
</tr>';
echo '</table>
<div class="centre">';
if (!isset($_POST['CategoryID'])) {
$_POST['CategoryID'] = '';
}
$sql = "SELECT stkcatpropid,\n label,\n controltype,\n defaultvalue,\n numericvalue,\n minimumvalue,\n maximumvalue\n FROM stockcatproperties\n WHERE categoryid ='" . $_POST['CategoryID'] . "'\n AND reqatsalesorder =0\n ORDER BY stkcatpropid";
$PropertiesResult = DB_query($sql, $db);
$PropertyCounter = 0;
$PropertyWidth = array();
if (DB_num_rows($PropertiesResult) > 0) {
echo '<br />
<table class="selection">';
echo '<tr>
<th colspan="2">' . _('Item Category Properties') . '</th>
</tr>';
while ($PropertyRow = DB_fetch_array($PropertiesResult)) {
if (isset($_POST['StockID']) && !empty($_POST['StockID'])) {
$PropValResult = DB_query("SELECT value FROM\n stockitemproperties\n WHERE stockid='" . $_POST['StockID'] . "'\n AND stkcatpropid ='" . $PropertyRow['stkcatpropid'] . "'", $db);
$PropValRow = DB_fetch_row($PropValResult);
$PropertyValue = $PropValRow[0];
} else {
$PropertyValue = '';
}
echo '<tr>
<td>';
<?php
/* $Id$*/
/**
If the User has selected Keyed Entry, show them this special select list...
it is just in the way if they are doing file imports
it also would not be applicable in a PO and possible other situations...
**/
if ($_POST['EntryType'] == 'KEYED') {
/*Also a multi select box for adding bundles to the dispatch without keying */
$sql = "SELECT serialno, quantity\n\t\t\tFROM stockserialitems\n\t\t\tWHERE stockid='" . $StockID . "' \n\t\t\tAND loccode ='" . $LocationOut . "' \n\t\t\tAND quantity > 0";
$ErrMsg = '<br />' . _('Could not retrieve the items for') . ' ' . $StockID;
$Bundles = DB_query($sql, $db, $ErrMsg);
echo '<table class="selection"><tr>';
if (DB_num_rows($Bundles) > 0) {
$AllSerials = array();
foreach ($LineItem->SerialItems as $Itm) {
$AllSerials[$Itm->BundleRef] = $Itm->BundleQty;
}
echo '<td valign="top"><b>' . _('Select Existing Items') . '</b><br />';
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '?identifier=' . $identifier . '" method="post">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<input type="hidden" name="LineNo" value="' . $LineNo . '">
<input type="hidden" name="StockID" value="' . $StockID . '">
<input type="hidden" name="EntryType" value="KEYED">
<input type="hidden" name="identifier" value="' . $identifier . '">
<input type="hidden" name="EditControlled" value="true">
<select name=Bundles[] multiple="multiple">';
$id = 0;
$ItemsAvailable = 0;
while ($myrow = DB_fetch_array($Bundles, $db)) {
locations
WHERE recurringsalesorders.ordertype=salestypes.typeabbrev
AND recurringsalesorders.debtorno = debtorsmaster.debtorno
AND recurringsalesorders.debtorno = custbranch.debtorno
AND recurringsalesorders.branchcode = custbranch.branchcode
AND recurringsalesorders.fromstkloc=locations.loccode
AND recurringsalesorders.ordertype=salestypes.typeabbrev
AND (TO_DAYS(NOW()) - TO_DAYS(recurringsalesorders.lastrecurrence)) > (365/recurringsalesorders.frequency)
AND DATE_ADD(recurringsalesorders.lastrecurrence, ' . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ') <= recurringsalesorders.stopdate';
$RecurrOrdersDueResult = DB_query($sql, $db, _('There was a problem retrieving the recurring sales order templates. The database reported:'));
if (DB_num_rows($RecurrOrdersDueResult) == 0) {
prnMsg(_('There are no recurring order templates that are due to have another recurring order created'), 'warn');
include 'includes/footer.inc';
exit;
}
echo '<BR>The number of recurring orders to process is : ' . DB_num_rows($RecurrOrdersDueResult);
while ($RecurrOrderRow = DB_fetch_array($RecurrOrdersDueResult)) {
$EmailText = '';
echo '<BR>' . _('Recurring order') . ' ' . $RecurrOrderRow['recurrorderno'] . ' ' . _('for') . ' ' . $RecurrOrderRow['debtorno'] . ' - ' . $RecurrOrderRow['branchcode'] . ' ' . _('is being processed');
$result = DB_Txn_Begin($db);
/*the last recurrence was the date of the last time the order recurred
the frequency is the number of times per annum that the order should recurr
so 365 / frequency gives the number of days between recurrences */
$DelDate = FormatDateforSQL(DateAdd(ConvertSQLDate($RecurrOrderRow['lastrecurrence']), 'd', 365 / $RecurrOrderRow['frequency']));
echo '<BR>Date calculated for the next recurrence was: ' . $DelDate;
$HeaderSQL = "INSERT INTO salesorders (\n\t\t\t\tdebtorno,\n\t\t\t\tbranchcode,\n\t\t\t\tcustomerref,\n\t\t\t\tcomments,\n\t\t\t\torddate,\n\t\t\t\tordertype,\n\t\t\t\tshipvia,\n\t\t\t\tdeliverto,\n\t\t\t\tdeladd1,\n\t\t\t\tdeladd2,\n\t\t\t\tdeladd3,\n\t\t\t\tdeladd4,\n\t\t\t\tdeladd5,\n\t\t\t\tdeladd6,\n\t\t\t\tcontactphone,\n\t\t\t\tcontactemail,\n\t\t\t\tfreightcost,\n\t\t\t\tfromstkloc,\n\t\t\t\tdeliverydate )\n\t\t\tVALUES (\n\t\t\t\t'" . $RecurrOrderRow['debtorno'] . "',\n\t\t\t\t'" . $RecurrOrderRow['branchcode'] . "',\n\t\t\t\t'" . $RecurrOrderRow['customerref'] . "',\n\t\t\t\t'" . $RecurrOrderRow['comments'] . "',\n\t\t\t\t'" . $DelDate . "',\n\t\t\t\t'" . $RecurrOrderRow['ordertype'] . "',\n\t\t\t\t" . $RecurrOrderRow['shipvia'] . ",\n\t\t\t\t'" . $RecurrOrderRow['deliverto'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd1'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd2'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd3'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd4'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd5'] . "',\n\t\t\t\t'" . $RecurrOrderRow['deladd6'] . "',\n\t\t\t\t'" . $RecurrOrderRow['contactphone'] . "',\n\t\t\t\t'" . $RecurrOrderRow['contactemail'] . "',\n\t\t\t\t" . $RecurrOrderRow['freightcost'] . ",\n\t\t\t\t'" . $RecurrOrderRow['fromstkloc'] . "',\n\t\t\t\t'" . $DelDate . "')";
$ErrMsg = _('The order cannot be added because');
$InsertQryResult = DB_query($HeaderSQL, $db, $ErrMsg, true);
$OrderNo = GetNextTransNo(30, $db);
$EmailText = _('A new order has been created from a recurring order template for customer') . ' ' . $RecurrOrderRow['debtorno'] . ' ' . $RecurrOrderRow['branchcode'] . "\n" . _('The order number is:') . ' ' . $OrderNo;
/*need to look up RecurringOrder from the template and populate the line RecurringOrder array with the sales order details records */
// Javier: I added this must be called before Add Page
$pdf->AddPage();
// $this->SetLineWidth(1); Javier: It was ok for FPDF but now is too gross with TCPDF. TCPDF defaults to 0'57 pt (0'2 mm) which is ok.
$pdf->cMargin = 0;
// 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;
$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;
$LYCheckTotal = 0;
$CheckTotal = 0;
$ActGrp = '';
$Level = 0;
$ParentGroups = array();
$ParentGroups[$Level] = '';
$GroupTotal = array(0);
$LYGroupTotal = array(0);
$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;
}
include 'includes/PDFDebtorBalsPageHeader.inc';
$TotBal = 0;
while ($DebtorBalances = DB_fetch_array($CustomerResult, $db)) {
$Balance = $DebtorBalances['balance'] - $DebtorBalances['afterdatetrans'] + $DebtorBalances['afterdatediffonexch'];
$FXBalance = $DebtorBalances['fxbalance'] - $DebtorBalances['fxafterdatetrans'];
if (abs($Balance) > 0.008999999999999999 or ABS($FXBalance) > 0.008999999999999999) {
$DisplayBalance = locale_number_format($DebtorBalances['balance'] - $DebtorBalances['afterdatetrans'], $DebtorBalances['decimalplaces']);
$DisplayFXBalance = locale_number_format($DebtorBalances['fxbalance'] - $DebtorBalances['fxafterdatetrans'], $DebtorBalances['decimalplaces']);
<br />
</div>';
}
echo '<table cellpadding="2">';
echo '<tr>
<th>' . _('Code') . '</th>
<th>' . _('Description') . '</th>
<th>' . _('Total Qty On Hand') . '</th>
<th>' . _('Units') . '</th>
<th>' . _('Stock Status') . '</th>
</tr>';
$j = 1;
$k = 0;
//row counter to determine background colour
$RowIndex = 0;
if (DB_num_rows($searchresult) != 0) {
DB_data_seek($searchresult, ($_POST['PageOffset'] - 1) * $_SESSION['DisplayRecordsMax']);
}
while ($myrow = DB_fetch_array($searchresult) and $RowIndex != $_SESSION['DisplayRecordsMax']) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k++;
}
if ($myrow['mbflag'] == 'D') {
$qoh = _('N/A');
} else {
$qoh = locale_number_format($myrow['qoh'], $myrow['decimalplaces']);
}
style="width: 100%"></td>';
echo '<td><select name="TaxOnTax_' . $TaxAuthRow[$i]['taxauthid'] . '" style="width: 100%">';
if ($TaxAuthRow[$i]['taxontax'] == 1) {
echo '<option selected value=1>' . _('Yes');
echo '<option value=0>' . _('No');
} else {
echo '<option value=1>' . _('Yes');
echo '<option selected value=0>' . _('No');
}
echo '</select></td></tr>';
}
echo '</table>';
echo '<br><div class="centre"><input type="submit" name="UpdateOrder" value="' . _('Update Order') . '"></div>';
}
echo '</form>';
if (DB_num_rows($Result) > 0) {
echo '<br>';
echo '<table><tr>';
echo "<th colspan=4>" . _('Assigned Taxes') . "</th>";
echo '<th></th>';
echo "<th colspan=2>" . _('Available Taxes') . "</th>";
echo '</tr>';
echo '<tr>';
echo "<th>" . _('Tax Auth ID') . '</th>';
echo "<th>" . _('Tax Authority Name') . '</th>';
echo "<th>" . _('Calculation Order') . '</th>';
echo "<th>" . _('Tax on Prior Tax(es)') . '</th>';
echo '<th></th>';
echo "<th>" . _('Tax Auth ID') . '</th>';
echo "<th>" . _('Tax Authority Name') . '</th>';
echo '</tr>';
// $_SESSION['SuppID'] = new SupplierID;
// $_SESSION['InvID'] = new InvoiceID;
$SQL = "SELECT supptrans.supplierno,\n\t\tsupptrans.suppreference,\n\t\tsupptrans.trandate,\n\t\tsupptrans.alloc,\n\t\tsuppliers.currcode\n\tFROM supptrans\n\tLEFT JOIN suppliers\n\tON supptrans.supplierno=suppliers.supplierid\n\tWHERE supptrans.id IN (SELECT suppallocs.transid_allocfrom\n\t\t\t\tFROM supptrans, suppallocs\n\t\t\t\tWHERE supptrans.supplierno = '" . $SuppID . "'\n\t\t\t\tAND supptrans.suppreference = '" . $InvID . "'\n\t\t\t\tAND supptrans.id = suppallocs.transid_allocto)";
/*
Might be a way of doing this query without a subquery
$SQL= "SELECT supptrans.supplierno,
supptrans.suppreference,
supptrans.trandate,
supptrans.alloc
FROM supptrans INNER JOIN suppallocs ON supptrans.id=suppallocs.transid_allocfrom
WHERE supptrans.supplierno = '$SuppID'
AND supptrans.suppreference = '$InvID'
*/
$Result = DB_query($SQL, $db);
if (DB_num_rows($Result) == 0) {
prnMsg(_('There may be a problem retrieving the information. No data is returned'), 'warn');
echo '<br /><a HREF ="javascript:history.back()">' . _('Go back') . '</a>';
include 'includes/foooter.inc';
exit;
}
echo '<table cellpadding="2" width="80%" class="selection">';
$TableHeader = '<tr>
<th>' . _('Supplier Number') . '<br />' . _('Reference') . '</th>
<th>' . _('Payment') . '<br />' . _('Reference') . '</th>
<th>' . _('Payment') . '<br />' . _('Date') . '</th>
<th>' . _('Total Payment') . '<br />' . _('Amount') . '</th></tr>';
echo $TableHeader;
$j = 1;
$k = 0;
//row colour counter
$DemandQty += $DemandRow['woqtydemo'];
}
if ($Its_A_KitSet_Assembly_Or_Dummy == False) {
$QOOSQL = "SELECT SUM((purchorderdetails.quantityord*purchorderdetails.conversionfactor) -\n\t\t\t\t\t\t\t\t\t(purchorderdetails.quantityrecd*purchorderdetails.conversionfactor)) AS totalonorder\n\t\t\t\t\t\t\t\tFROM purchorders\n\t\t\t\t\t\t\t\tLEFT JOIN purchorderdetails\n\t\t\t\t\t\t\t\t\tON purchorders.orderno=purchorderdetails.orderno\n\t\t\t\t\t\t\t\tWHERE purchorderdetails.itemcode='" . $StockID . "'\n\t\t\t\t\t\t\t\t\tAND purchorderdetails.completed =0\n\t\t\t\t\t\t\t\t\tAND purchorders.intostocklocation='" . $myrow['loccode'] . "'\n\t\t\t\t\t\t\t\t\tAND purchorders.status<>'Cancelled'\n\t\t\t\t\t\t\t\t\tAND purchorders.status<>'Pending'\n\t\t\t\t\t\t\t\t\tAND purchorders.status<>'Rejected'";
$QOOResult = DB_query($QOOSQL, $db);
if (DB_num_rows($QOOResult) == 0) {
$QOO = 0;
} else {
$QOORow = DB_fetch_array($QOOResult);
$QOO = $QOORow['totalonorder'];
}
//Also the on work order quantities
$sql = "SELECT SUM(woitems.qtyreqd-woitems.qtyrecd) AS qtywo\n\t\t\t\tFROM woitems INNER JOIN workorders\n\t\t\t\tON woitems.wo=workorders.wo\n\t\t\t\tWHERE workorders.closed=0\n\t\t\t\tAND workorders.loccode='" . $myrow['loccode'] . "'\n\t\t\t\tAND woitems.stockid='" . $StockID . "'";
$ErrMsg = _('The quantity on work orders for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
$QOOResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
if (DB_num_rows($QOOResult) == 1) {
$QOORow = DB_fetch_array($QOOResult);
$QOO += $QOORow['qtywo'];
}
echo '<td>' . $myrow['locationname'] . '</td>';
printf('<td class="number">%s</td>
<td class="number">%s</td>
<td class="number">%s</td>
<td class="number">%s</td>
<td class="number">%s</td></tr>', locale_number_format($myrow['quantity'], $DecimalPlaces), locale_number_format($myrow['reorderlevel'], $DecimalPlaces), locale_number_format($DemandQty, $DecimalPlaces), locale_number_format($myrow['quantity'] - $DemandQty, $DecimalPlaces), locale_number_format($QOO, $DecimalPlaces));
if ($Serialised == 1) {
/*The line is a serialised item*/
echo '<td><a target="_blank" href="' . $rootpath . '/StockSerialItems.php?Serialised=Yes&Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Serial Numbers') . '</a></td></tr>';
} elseif ($Controlled == 1) {
echo '<td><a target="_blank" href="' . $rootpath . '/StockSerialItems.php?Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Batches') . '</a></td></tr>';
}
$_SESSION['SuppTrans']->SupplierName = $myrow['suppname'];
$_SESSION['SuppTrans']->TermsDescription = $myrow['terms'];
$_SESSION['SuppTrans']->CurrCode = $myrow['currcode'];
$_SESSION['SuppTrans']->ExRate = $myrow['exrate'];
$_SESSION['SuppTrans']->TaxGroup = $myrow['taxgroupid'];
$_SESSION['SuppTrans']->TaxGroupDescription = $myrow['taxgroupdescription'];
$_SESSION['SuppTrans']->SupplierID = $myrow['supplierid'];
$_SESSION['SuppTrans']->CurrDecimalPlaces = $myrow['currdecimalplaces'];
if ($myrow['daysbeforedue'] == 0) {
$_SESSION['SuppTrans']->Terms = '1' . $myrow['dayinfollowingmonth'];
} else {
$_SESSION['SuppTrans']->Terms = '0' . $myrow['daysbeforedue'];
}
$_SESSION['SuppTrans']->SupplierID = $_GET['SupplierID'];
$LocalTaxProvinceResult = DB_query("SELECT taxprovinceid\n\t\t\t\t\t\t\t\t\t\tFROM locations\n\t\t\t\t\t\t\t\t\t\tWHERE loccode = '" . $_SESSION['UserStockLocation'] . "'", $db);
if (DB_num_rows($LocalTaxProvinceResult) == 0) {
prnMsg(_('The tax province associated with your user account has not been set up in this database. Tax calculations are based on the tax group of the supplier and the tax province of the user entering the invoice. The system administrator should redefine your account with a valid default stocking location and this location should refer to a valid tax province'), 'error');
include 'includes/footer.inc';
exit;
}
$LocalTaxProvinceRow = DB_fetch_row($LocalTaxProvinceResult);
$_SESSION['SuppTrans']->LocalTaxProvince = $LocalTaxProvinceRow[0];
$_SESSION['SuppTrans']->GetTaxes();
$_SESSION['SuppTrans']->GLLink_Creditors = $_SESSION['CompanyRecord']['gllink_creditors'];
$_SESSION['SuppTrans']->GRNAct = $_SESSION['CompanyRecord']['grnact'];
$_SESSION['SuppTrans']->CreditorsAct = $_SESSION['CompanyRecord']['creditorsact'];
$_SESSION['SuppTrans']->InvoiceOrCredit = 'Credit Note';
//note no gettext going on here
} elseif (!isset($_SESSION['SuppTrans'])) {
prnMsg(_('To enter a supplier credit note the supplier must first be selected from the supplier selection screen'), 'warn');
echo '<br /><a href="' . $rootpath . '/SelectSupplier.php">' . _('Select A Supplier to Enter an Credit Note For') . '</a>';
prnMsg(_('All rates updated successfully'), 'info');
}
/* end of update code
*/
/*Display updated rates
*/
$TaxAuthDetail = DB_query("SELECT description\n\t\t\t\t\t\t\tFROM taxauthorities WHERE taxid='" . $TaxAuthority . "'", $db);
$myrow = DB_fetch_row($TaxAuthDetail);
echo '<form onSubmit="return VerifyForm(this);" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" class="noPrint">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<input type="hidden" name="TaxAuthority" value="' . $TaxAuthority . '" />';
$TaxRatesResult = DB_query("SELECT taxauthrates.taxcatid,\n\t\t\t\t\t\t\t\t\ttaxcategories.taxcatname,\n\t\t\t\t\t\t\t\t\ttaxauthrates.taxrate,\n\t\t\t\t\t\t\t\t\ttaxauthrates.dispatchtaxprovince,\n\t\t\t\t\t\t\t\t\ttaxprovinces.taxprovincename\n\t\t\t\t\t\t\tFROM taxauthrates INNER JOIN taxauthorities\n\t\t\t\t\t\t\tON taxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\t\t\tINNER JOIN taxprovinces\n\t\t\t\t\t\t\tON taxauthrates.dispatchtaxprovince= taxprovinces.taxprovinceid\n\t\t\t\t\t\t\tINNER JOIN taxcategories\n\t\t\t\t\t\t\tON taxauthrates.taxcatid=taxcategories.taxcatid\n\t\t\t\t\t\t\tWHERE taxauthrates.taxauthority='" . $TaxAuthority . "'\n\t\t\t\t\t\t\tORDER BY taxauthrates.dispatchtaxprovince,\n\t\t\t\t\t\t\ttaxauthrates.taxcatid", $db);
if (isset($_SESSION['FirstStart'])) {
echo '<div class="page_help_text">' . _('As this is the first time that the system has been used, you must first create a tax authority.') . '<br />' . _('For help, click on the help icon in the top right') . '<br />' . _('Once you have filled in all the details, click on the button at the bottom of the screen') . '</div>';
}
if (DB_num_rows($TaxRatesResult) > 0) {
echo '<table class="selection">';
echo '<tr>
<th colspan="3"><h3>' . _('Update') . ' ' . $myrow[0] . ' ' . _('Rates') . '</h3></th>
</tr>
<tr>
<th class="SortableColumn">' . _('Deliveries From') . '<br />' . _('Tax Province') . '</th>
<th class="SortableColumn">' . _('Tax Category') . '</th>
<th>' . _('Tax Rate') . ' %</th>
</tr>';
$k = 0;
//row counter to determine background colour
$OldProvince = '';
while ($myrow = DB_fetch_array($TaxRatesResult)) {
if ($OldProvince != $myrow['dispatchtaxprovince'] and $OldProvince != '') {
echo '<tr style="background-color:#555555"><td colspan="3"></td></tr>';
include 'includes/footer.inc';
exit;
}
if (ContainsIllegalCharacters($_POST['NewStockID'])) {
prnMsg(_('The new stock code to change the old code to contains illegal characters - no changes will be made'), 'error');
include 'includes/footer.inc';
exit;
}
if ($_POST['NewStockID'] == '') {
prnMsg(_('The new stock code to change the old code to must be entered as well'), 'error');
include 'includes/footer.inc';
exit;
}
/*Now check that the new code doesn't already exist */
$result = DB_query("SELECT stockid FROM stockmaster WHERE stockid='" . $_POST['NewStockID'] . "'", $db);
if (DB_num_rows($result) != 0) {
echo '<br><br>';
prnMsg(_('The replacement stock code') . ': ' . $_POST['NewStockID'] . ' ' . _('already exists as a stock code in the system') . ' - ' . _('a unique stock code must be entered for the new code'), 'error');
include 'includes/footer.inc';
exit;
}
$result = DB_Txn_Begin($db);
echo '<br>' . _('Adding the new stock master record');
$sql = "INSERT INTO stockmaster (stockid,\n\t\t\t\t\tcategoryid,\n\t\t\t\t\tdescription,\n\t\t\t\t\tlongdescription,\n\t\t\t\t\tunits,\n\t\t\t\t\tmbflag,\n\t\t\t\t\tlastcurcostdate,\n\t\t\t\t\tactualcost,\n\t\t\t\t\tlastcost,\n\t\t\t\t\tmaterialcost,\n\t\t\t\t\tlabourcost,\n\t\t\t\t\toverheadcost,\n\t\t\t\t\tlowestlevel,\n\t\t\t\t\tdiscontinued,\n\t\t\t\t\tcontrolled,\n\t\t\t\t\teoq,\n\t\t\t\t\tvolume,\n\t\t\t\t\tkgs,\n\t\t\t\t\tbarcode,\n\t\t\t\t\tdiscountcategory,\n\t\t\t\t\ttaxcatid)\n\t\t\tSELECT '" . $_POST['NewStockID'] . "',\n\t\t\t\tcategoryid,\n\t\t\t\tdescription,\n\t\t\t\tlongdescription,\n\t\t\t\tunits,\n\t\t\t\tmbflag,\n\t\t\t\tlastcurcostdate,\n\t\t\t\tactualcost,\n\t\t\t\tlastcost,\n\t\t\t\tmaterialcost,\n\t\t\t\tlabourcost,\n\t\t\t\toverheadcost,\n\t\t\t\tlowestlevel,\n\t\t\t\tdiscontinued,\n\t\t\t\tcontrolled,\n\t\t\t\teoq,\n\t\t\t\tvolume,\n\t\t\t\tkgs,\n\t\t\t\tbarcode,\n\t\t\t\tdiscountcategory,\n\t\t\t\ttaxcatid\n\t\t\tFROM stockmaster\n\t\t\tWHERE stockid='" . $_POST['OldStockID'] . "'";
$DbgMsg = _('The SQL statement that failed was');
$ErrMsg = _('The SQL to insert the new stock master record failed');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
echo ' ... ' . _('completed');
echo '<br>' . _('Changing stock location records');
$sql = "UPDATE locstock SET stockid='" . $_POST['NewStockID'] . "' WHERE stockid='" . $_POST['OldStockID'] . "'";
$ErrMsg = _('The SQL to update stock location records failed');
$SQL = "SELECT accountgroups.groupname,\n\t\t\taccountgroups.parentgroupname,\n\t\t\taccountgroups.pandl,\n\t\t\tchartdetails.accountcode ,\n\t\t\tchartmaster.accountname,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['FromPeriod'] . "' THEN chartdetails.bfwd ELSE 0 END) AS firstprdbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['FromPeriod'] . "' THEN chartdetails.bfwdbudget ELSE 0 END) AS firstprdbudgetbfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.bfwd + chartdetails.actual ELSE 0 END) AS lastprdcfwd,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.actual ELSE 0 END) AS monthactual,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.budget ELSE 0 END) AS monthbudget,\n\t\t\tSum(CASE WHEN chartdetails.period='" . $_POST['ToPeriod'] . "' THEN chartdetails.bfwdbudget + chartdetails.budget ELSE 0 END) AS lastprdbudgetcfwd\n\t\tFROM chartmaster INNER JOIN accountgroups ON chartmaster.group_ = accountgroups.groupname\n\t\t\tINNER JOIN chartdetails ON chartmaster.accountcode= chartdetails.accountcode\n\t\tGROUP BY accountgroups.groupname,\n\t\t\t\taccountgroups.parentgroupname,\n\t\t\t\taccountgroups.pandl,\n\t\t\t\taccountgroups.sequenceintb,\n\t\t\t\tchartdetails.accountcode,\n\t\t\t\tchartmaster.accountname\n\t\tORDER BY accountgroups.pandl desc,\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 = _('Trial Balance') . ' - ' . _('Problem Report') . '....';
$ViewTopic = 'GeneralLedger';
$BookMark = 'TrialBalance';
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;
}
if (DB_num_rows($AccountsResult) == 0) {
$Title = _('Print Trial Balance Error');
$ViewTopic = 'GeneralLedger';
$BookMark = 'TrialBalance';
include 'includes/header.inc';
echo '<p>';
prnMsg(_('There were no entries to print out for the selections specified'));
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
include 'includes/PDFTrialBalancePageHeader.inc';
$j = 1;
$Level = 1;
$ActGrp = '';
$ParentGroups = array();
echo '<table class="selection">';
$sql = "SELECT typeabbrev,\n\t\tsales_type\n\t\tFROM salestypes";
$result = DB_query($sql, $db);
echo '<tr><td>' . _('Customer Price List') . ' (' . _('Sales Type') . '):</td><td>';
echo '<select tabindex="1" name="SalesType">';
while ($myrow = DB_fetch_array($result)) {
if (isset($_POST['SalesType']) and $myrow['typeabbrev'] == $_POST['SalesType']) {
echo '<option selected="selected" value="' . $myrow['typeabbrev'] . '">' . $myrow['sales_type'] . '</option>';
} else {
echo '<option value="' . $myrow['typeabbrev'] . '">' . $myrow['sales_type'] . '</option>';
}
}
echo '</select></td></tr>';
$sql = "SELECT DISTINCT discountcategory FROM stockmaster WHERE discountcategory <>''";
$result = DB_query($sql, $db);
if (DB_num_rows($result) > 0) {
echo '<tr>
<td>' . _('Discount Category Code') . ': </td>
<td><select name="DiscountCategory">';
while ($myrow = DB_fetch_array($result)) {
if ($myrow['discountcategory'] == $_POST['DiscCat']) {
echo '<option selected="selected" value="' . $myrow['discountcategory'] . '">' . $myrow['discountcategory'] . '</option>';
} else {
echo '<option value="' . $myrow['discountcategory'] . '">' . $myrow['discountcategory'] . '</option>';
}
}
echo '</select></td></tr>';
} else {
echo '<tr><td><input type="hidden" name="DiscountCategory" value="" /></td></tr>';
}
echo '<tr>
//initialise no input errors assumed initially before we test
$InputError = 0;
$i = 1;
/* actions to take once the user has clicked the submit button
ie the page has called itself with some user input */
//first off validate inputs sensible
$_POST['AreaCode'] = mb_strtoupper($_POST['AreaCode']);
$sql = "SELECT areacode FROM areas WHERE areacode='" . $_POST['AreaCode'] . "'";
$result = DB_query($sql, $db);
// mod to handle 3 char area codes
if (mb_strlen($_POST['AreaCode']) > 3) {
$InputError = 1;
prnMsg(_('The area code must be three characters or less long'), 'error');
$Errors[$i] = 'AreaCode';
$i++;
} elseif (DB_num_rows($result) > 0 and !isset($SelectedArea)) {
$InputError = 1;
prnMsg(_('The area code entered already exists'), 'error');
$Errors[$i] = 'AreaCode';
$i++;
} elseif (mb_strlen($_POST['AreaDescription']) > 25) {
$InputError = 1;
prnMsg(_('The area description must be twenty five characters or less long'), 'error');
$Errors[$i] = 'AreaDescription';
$i++;
} elseif (trim($_POST['AreaCode']) == '') {
$InputError = 1;
prnMsg(_('The area code may not be empty'), 'error');
$Errors[$i] = 'AreaCode';
$i++;
} elseif (trim($_POST['AreaDescription']) == '') {
echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
}
}
echo '</select></td></tr>';
echo '</table>';
echo '<div class="centre"><input type="submit" name="EnterAdjustment" value="' . _('Show Requests') . '" /></div>';
echo '</form>';
include 'includes/footer.inc';
exit;
}
/* Retrieve the requisition header information
*/
if (isset($_POST['Location'])) {
$sql = "SELECT stockrequest.dispatchid,\n\t\t\tlocations.locationname,\n\t\t\tstockrequest.despatchdate,\n\t\t\tstockrequest.narrative,\n\t\t\tdepartments.description,\n\t\t\twww_users.realname,\n\t\t\twww_users.email\n\t\tFROM stockrequest\n\t\tLEFT JOIN departments\n\t\t\tON stockrequest.departmentid=departments.departmentid\n\t\tLEFT JOIN locations\n\t\t\tON stockrequest.loccode=locations.loccode\n\t\tLEFT JOIN www_users\n\t\t\tON www_users.userid=departments.authoriser\n\tWHERE stockrequest.authorised=1\n\t\tAND stockrequest.closed=0\n\t\tAND stockrequest.loccode='" . $_POST['Location'] . "'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) == 0) {
prnMsg(_('There are no outstanding authorised requests for this location'), 'info');
echo '<br />';
echo '<div class="centre"><a href="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">' . _('Select another location') . '</a></div>';
include 'includes/footer.inc';
exit;
}
echo '<form onSubmit="return VerifyForm(this);" method="post" class="noPrint" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
echo '<table class="selection">
<tr>
<th>' . _('Request Number') . '</th>
<th>' . _('Department') . '</th>
<th>' . _('Location Of Stock') . '</th>
<th>' . _('Requested Date') . '</th>
echo '<tr><td>' . _('Select Location Receiving Into') . ':</td><td>';
echo '<select name = "RecLocation" onChange=ReloadForm(form1.RefreshTransferList)>';
if (!isset($_POST['RecLocation'])) {
$_POST['RecLocation'] = $_SESSION['UserStockLocation'];
}
while ($myrow = DB_fetch_array($LocResult)) {
if ($myrow['loccode'] == $_POST['RecLocation']) {
echo '<option selected="True" value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
} else {
echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
}
}
echo '</select><button type="submit" name="RefreshTransferList">' . _('Refresh Transfer List') . '</button></td></tr></table><br />';
$sql = "SELECT DISTINCT reference,\n\t\t\t\tlocations.locationname as trffromloc,\n\t\t\t\tshipdate\n\t\t\tFROM loctransfers INNER JOIN locations\n\t\t\t\tON loctransfers.shiploc=locations.loccode\n\t\t\tWHERE recloc='" . $_POST['RecLocation'] . "'\n\t\t\tAND recqty < shipqty";
$TrfResult = DB_query($sql, $db);
if (DB_num_rows($TrfResult) > 0) {
$LocSql = "SELECT locationname FROM locations WHERE loccode='" . $_POST['RecLocation'] . "'";
$LocResult = DB_query($LocSql, $db);
$LocRow = DB_fetch_array($LocResult);
echo '<table class="selection">';
echo '<tr><th colspan="4" class="header">' . _('Pending Transfers Into') . ' ' . $LocRow['locationname'] . '</th></tr>';
echo '<tr>
<th>' . _('Transfer Ref') . '</th>
<th>' . _('Transfer From') . '</th>
<th>' . _('Dispatch Date') . '</th>
</tr>';
$k = 0;
while ($myrow = DB_fetch_array($TrfResult)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
$Assembly = True;
$StandardCost = 0;
/*To start with - accumulate the cost of the comoponents for use in journals later on */
$SQL = "SELECT bom.component,\r\n\t\t\t\t\t\tbom.quantity,\r\n\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\r\n\t\t\t\t\tFROM bom,\r\n\t\t\t\t\t\tstockmaster\r\n\t\t\t\t\tWHERE bom.component=stockmaster.stockid\r\n\t\t\t\t\tAND bom.parent='" . $OrderLine->StockID . "'\r\n\t\t\t\t\tAND bom.effectiveto > '" . Date("Y-m-d") . "'\r\n\t\t\t\t\tAND bom.effectiveafter < '" . Date("Y-m-d") . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Could not retrieve assembly components from the database for') . ' ' . $OrderLine->StockID . _('because') . ' ';
$DbgMsg = _('The SQL that failed was');
$AssResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
while ($AssParts = DB_fetch_array($AssResult, $db)) {
$StandardCost += $AssParts['standard'] * $AssParts['quantity'];
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\r\n\t\t\t\t\t\tFROM locstock\r\n\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\r\n\t\t\t\t\t\tAND loccode= '" . $_SESSION['Items']->Location . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Can not retrieve assembly components location stock quantities because ');
$DbgMsg = _('The SQL that failed was');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
/*There must be some error this should never happen */
$QtyOnHandPrior = 0;
}
if (empty($AssParts['standard'])) {
$AssParts['standard'] = 0;
}
$SQL = "INSERT INTO stockmoves (\r\n\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\t\ttransno,\r\n\t\t\t\t\t\t\tloccode,\r\n\t\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\t\tdebtorno,\r\n\t\t\t\t\t\t\tbranchcode,\r\n\t\t\t\t\t\t\tprd,\r\n\t\t\t\t\t\t\treference,\r\n\t\t\t\t\t\t\tqty,\r\n\t\t\t\t\t\t\tstandardcost,\r\n\t\t\t\t\t\t\tshow_on_inv_crds,\r\n\t\t\t\t\t\t\tnewqoh\r\n\t\t\t\t\t\t) VALUES (\r\n\t\t\t\t\t\t\t'" . $AssParts['component'] . "',\r\n\t\t\t\t\t\t\t 10,\r\n\t\t\t\t\t\t\t " . $InvoiceNo . ",\r\n\t\t\t\t\t\t\t '" . $_SESSION['Items']->Location . "',\r\n\t\t\t\t\t\t\t '" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t\t '" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t\t '" . $_SESSION['Items']->Branch . "',\r\n\t\t\t\t\t\t\t " . $PeriodNo . ",\r\n\t\t\t\t\t\t\t '" . _('Assembly') . ': ' . $OrderLine->StockID . ' ' . _('Order') . ': ' . $_SESSION['ProcessingOrder'] . "',\r\n\t\t\t\t\t\t\t " . -$AssParts['quantity'] * $OrderLine->QtyDispatched . ",\r\n\t\t\t\t\t\t\t " . $AssParts['standard'] . ",\r\n\t\t\t\t\t\t\t 0,\r\n\t\t\t\t\t\t\t " . ($QtyOnHandPrior - $AssParts['quantity'] * $OrderLine->QtyDispatched) . "\r\n\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Stock movement records for the assembly components of') . ' ' . $OrderLine->StockID . ' ' . _('could not be inserted because');
$DbgMsg = _('The following SQL to insert the assembly components stock movement records was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$SQL = "UPDATE locstock\r\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $OrderLine->QtyDispatched . "\r\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\r\n\t\t\t\t\t\tAND loccode = '" . $_SESSION['Items']->Location . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('Location stock record could not be updated for an assembly component because');
prnMsg(_('The sales quantities 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;
}
$SalesRow = DB_fetch_array($SalesResult);
if ($_POST['Location'] == 'All') {
$SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand\n\t\t\t\tFROM salesorderdetails INNER JOIN salesorders\n\t\t\t\tON salesorderdetails.orderno=salesorders.orderno\n\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.stkcode = '" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND salesorderdetails.completed = 0\n\t\t\t\tAND salesorders.quotation=0";
} else {
$SQL = "SELECT SUM(salesorderdetails.quantity - salesorderdetails.qtyinvoiced) AS qtydemand\n\t\t\t\tFROM salesorderdetails INNER JOIN salesorders\n\t\t\t\tON salesorderdetails.orderno=salesorders.orderno\n\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 salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\t\t\tAND salesorderdetails.stkcode = '" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND salesorderdetails.completed = 0\n\t\t\t\tAND salesorders.quotation=0";
}
$DemandResult = DB_query($SQL, '', '', false, false);
$ListCount = DB_num_rows($DemandResult);
if (DB_error_no() != 0) {
$Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The sales order demand quantities 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;
}
// 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 {
