if ($_POST['StockCat'] != 'All') {
$CategorySQL = "SELECT categorydescription FROM stockcategory WHERE categoryid='" . $_POST['StockCat'] . "'";
$CategoryResult = DB_query($CategorySQL, $db);
$CategoryRow = DB_fetch_array($CategoryResult);
$CategoryDescription = $CategoryRow['categorydescription'];
$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');
$ViewTopic = 'Inventory';
$BookMark = 'FulfilRequest';
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/inventory.png" title="' . _('Contract') . '" alt="" />' . _('Fulfil Stock Requests') . '</p>';
if (isset($_POST['UpdateAll'])) {
foreach ($_POST as $key => $value) {
if (mb_strpos($key, 'Qty')) {
$RequestID = mb_substr($key, 0, mb_strpos($key, 'Qty'));
$LineID = mb_substr($key, mb_strpos($key, 'Qty') + 3);
$Quantity = filter_number_format($_POST[$RequestID . 'Qty' . $LineID]);
$StockID = $_POST[$RequestID . 'StockID' . $LineID];
$Location = $_POST[$RequestID . 'Location' . $LineID];
$Department = $_POST[$RequestID . 'Department' . $LineID];
$Tag = $_POST[$RequestID . 'Tag' . $LineID];
$RequestedQuantity = filter_number_format($_POST[$RequestID . 'RequestedQuantity' . $LineID]);
if (isset($_POST[$RequestID . 'Completed' . $LineID])) {
$Completed = True;
} else {
$Completed = False;
}
$sql = "SELECT materialcost, labourcost, overheadcost, decimalplaces FROM stockmaster WHERE stockid='" . $StockID . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_array($result);
$StandardCost = $myrow['materialcost'] + $myrow['labourcost'] + $myrow['overheadcost'];
$DecimalPlaces = $myrow['decimalplaces'];
$Narrative = _('Issue') . ' ' . $Quantity . ' ' . _('of') . ' ' . $StockID . ' ' . _('to department') . ' ' . $Department . ' ' . _('from') . ' ' . $Location;
$AdjustmentNumber = GetNextTransNo(17, $db);
$PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
$SQLAdjustmentDate = FormatDateForSQL(Date($_SESSION['DefaultDateFormat']));
$Result = DB_Txn_Begin($db);
if ($DescriptionLanguage != '') {
$result = DB_query("INSERT INTO stockdescriptiontranslations VALUES('" . $_POST['StockID'] . "','" . $DescriptionLanguage . "', '" . $_POST['Description_' . str_replace('.', '_', $DescriptionLanguage)] . "')", $db, $ErrMsg, $DbgMsg, true);
}
}
}
//now insert any item properties
for ($i = 0; $i < $_POST['PropertyCounter']; $i++) {
if ($_POST['PropType' . $i] == 2) {
if ($_POST['PropValue' . $i] == 'on') {
$_POST['PropValue' . $i] = 1;
} else {
$_POST['PropValue' . $i] = 0;
}
}
if ($_POST['PropNumeric' . $i] == 1) {
$_POST['PropValue' . $i] = filter_number_format($_POST['PropValue' . $i]);
} else {
$_POST['PropValue' . $i] = $_POST['PropValue' . $i];
}
$result = DB_query("INSERT INTO stockitemproperties (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcatpropid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tvalue)\n\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES ('" . $_POST['StockID'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PropID' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PropValue' . $i] . "')", $db, $ErrMsg, $DbgMsg, true);
}
//end of loop around properties defined for the category
//Add data to locstock
$sql = "INSERT INTO locstock (loccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid)\n\t\t\t\t\t\t\t\t\t\tSELECT locations.loccode,\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tFROM locations";
$ErrMsg = _('The locations for the item') . ' ' . $_POST['StockID'] . ' ' . _('could not be added because');
$DbgMsg = _('NB Locations records can be added by opening the utility page') . ' <i>Z_MakeStockLocns.php</i> ' . _('The SQL that was used to add the location records that failed was');
$InsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
DB_Txn_Commit($db);
//check for any purchase data
$sql = "SELECT purchdata.supplierno,\n suppliers.suppname,\n purchdata.price,\n suppliers.currcode,\n purchdata.effectivefrom,\n purchdata.suppliersuom,\n purchdata.supplierdescription,\n purchdata.leadtime,\n purchdata.suppliers_partno,\n purchdata.minorderqty,\n purchdata.preferred,\n purchdata.conversionfactor,\n currencies.decimalplaces AS currdecimalplaces\n FROM purchdata INNER JOIN suppliers\n ON purchdata.supplierno=suppliers.supplierid\n INNER JOIN currencies\n ON suppliers.currcode=currencies.currabrev\n WHERE purchdata.stockid = '" . $_POST['OldStockID'] . "'\n ORDER BY purchdata.effectivefrom DESC";
$ErrMsg = _('The supplier purchasing details for the selected part could not be retrieved because');
foreach ($_SESSION['SuppTrans']->Taxes as $Tax) {
/*Set the tax rate to what was entered */
if (isset($_POST['TaxRate' . $Tax->TaxCalculationOrder])) {
$_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate = filter_number_format($_POST['TaxRate' . $Tax->TaxCalculationOrder]) / 100;
}
if ($_POST['OverRideTax'] == 'Auto' or !isset($_POST['OverRideTax'])) {
/*Now recaluclate the tax depending on the method */
if ($Tax->TaxOnTax == 1) {
$_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * ($_SESSION['SuppTrans']->OvAmount + $TaxTotal);
} else {
/*Calculate tax without the tax on tax */
$_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxRate * $_SESSION['SuppTrans']->OvAmount;
}
} else {
/*Tax being entered manually accept the taxamount entered as is*/
$_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount = filter_number_format($_POST['TaxAmount' . $Tax->TaxCalculationOrder]);
}
$TaxTotal += $_SESSION['SuppTrans']->Taxes[$Tax->TaxCalculationOrder]->TaxOvAmount;
}
$InputError = False;
if ($TaxTotal + $_SESSION['SuppTrans']->OvAmount <= 0) {
$InputError = True;
prnMsg(_('The credit note as entered cannot be processed because the total amount of the credit note is less than or equal to 0') . '. ' . _('Credit notes are expected to be entered as positive amounts to credit'), 'warn');
} elseif (mb_strlen($_SESSION['SuppTrans']->SuppReference) < 1) {
$InputError = True;
prnMsg(_('The credit note as entered cannot be processed because the there is no suppliers credit note number or reference entered') . '. ' . _('The supplier credit note number must be entered'), 'error');
} elseif (!Is_Date($_SESSION['SuppTrans']->TranDate)) {
$InputError = True;
prnMsg(_('The credit note as entered cannot be processed because the date entered is not in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
} elseif (DateDiff(Date($_SESSION['DefaultDateFormat']), $_SESSION['SuppTrans']->TranDate, 'd') < 0) {
$InputError = True;
} else {
$_POST['PropNumeric' . $i] = 0;
}
if (!isset($_POST['PropMinimum' . $i]) or $_POST['PropMinimum' . $i] === '') {
$_POST['PropMinimum' . $i] = '-999999999';
}
if (!isset($_POST['PropMaximum' . $i]) or $_POST['PropMaximum' . $i] === '') {
$_POST['PropMaximum' . $i] = '999999999';
}
if ($_POST['PropID' . $i] == 'NewProperty' and mb_strlen($_POST['PropLabel' . $i]) > 0) {
$sql = "INSERT INTO stockcatproperties (categoryid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tlabel,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tcontroltype,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tdefaultvalue,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tminimumvalue,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tmaximumvalue,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tnumericvalue,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\treqatsalesorder)\n\t\t\t\t\t\t\t\t\t\t\tVALUES ('" . $SelectedCategory . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PropLabel' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t" . $_POST['PropControlType' . $i] . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PropDefault' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['PropMinimum' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['PropMaximum' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['PropNumeric' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t" . $_POST['PropReqSO' . $i] . ')';
$ErrMsg = _('Could not insert a new category property for') . $_POST['PropLabel' . $i];
$result = DB_query($sql, $ErrMsg);
} elseif ($_POST['PropID' . $i] != 'NewProperty') {
//we could be amending existing properties
$sql = "UPDATE stockcatproperties SET label ='" . $_POST['PropLabel' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t controltype = " . $_POST['PropControlType' . $i] . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t defaultvalue = '" . $_POST['PropDefault' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t minimumvalue = '" . filter_number_format($_POST['PropMinimum' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t maximumvalue = '" . filter_number_format($_POST['PropMaximum' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t numericvalue = '" . $_POST['PropNumeric' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t reqatsalesorder = " . $_POST['PropReqSO' . $i] . "\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE stkcatpropid =" . $_POST['PropID' . $i];
$ErrMsg = _('Updated the stock category property for') . ' ' . $_POST['PropLabel' . $i];
$result = DB_query($sql, $ErrMsg);
}
}
//end of loop round properties
prnMsg(_('Updated the stock category record for') . ' ' . $_POST['CategoryDescription'], 'success');
} elseif ($InputError != 1) {
/*Selected category is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new stock category form */
$sql = "INSERT INTO stockcategory (categoryid,\n\t\t\t\t\t\t\t\t\t\t\tstocktype,\n\t\t\t\t\t\t\t\t\t\t\tcategorydescription,\n\t\t\t\t\t\t\t\t\t\t\tdefaulttaxcatid,\n\t\t\t\t\t\t\t\t\t\t\tstockact,\n\t\t\t\t\t\t\t\t\t\t\tadjglact,\n\t\t\t\t\t\t\t\t\t\t\tissueglact,\n\t\t\t\t\t\t\t\t\t\t\tpurchpricevaract,\n\t\t\t\t\t\t\t\t\t\t\tmaterialuseagevarac,\n\t\t\t\t\t\t\t\t\t\t\twipact)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $_POST['CategoryID'] . "','" . $_POST['StockType'] . "','" . $_POST['CategoryDescription'] . "','" . $_POST['DefaultTaxCatID'] . "','" . $_POST['StockAct'] . "','" . $_POST['AdjGLAct'] . "','" . $_POST['IssueGLAct'] . "','" . $_POST['PurchPriceVarAct'] . "','" . $_POST['MaterialUseageVarAc'] . "','" . $_POST['WIPAct'] . "')";
$ErrMsg = _('Could not insert the new stock category') . $_POST['CategoryDescription'] . _('because');
$result = DB_query($sql, $ErrMsg);
prnMsg(_('A new stock category record has been added for') . ' ' . $_POST['CategoryDescription'], 'success');
}
//run the SQL from either of the above possibilites
unset($_POST['StockType']);
*/
$DeliveryDate = DateAdd(Date($_SESSION['DefaultDateFormat']), 'd', $LeadTime);
if (!Date1GreaterThanDate2($DeliveryDate, $_SESSION['PO' . $identifier]->DeliveryDate)) {
$DeliveryDate = $_SESSION['PO' . $identifier]->DeliveryDate;
}
} else {
// no purchasing data setup
$PurchPrice = 0;
$ConversionFactor = 1;
$SupplierDescription = $ItemRow['description'];
$SuppliersUnitOfMeasure = $ItemRow['units'];
$SuppliersPartNo = '';
$LeadTime = 1;
$DeliveryDate = $_SESSION['PO' . $identifier]->DeliveryDate;
}
$_SESSION['PO' . $identifier]->add_to_order($_SESSION['PO' . $identifier]->LinesOnOrder + 1, $ItemCode, 0, 0, filter_number_format($Quantity) * $ConversionFactor, $SupplierDescription, $PurchPrice, $ItemRow['units'], $ItemRow['stockact'], $DeliveryDate, 0, 0, 0, 0, 0, $ItemRow['accountname'], $ItemRow['decimalplaces'], $SuppliersUnitOfMeasure, $ConversionFactor, $LeadTime, $SuppliersPartNo);
} else {
//no rows returned by the SQL to get the item
prnMsg(_('The item code') . ' ' . $ItemCode . ' ' . _('does not exist in the database and therefore cannot be added to the order'), 'error');
if ($debug == 1) {
echo '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
}
/* end of if not already on the order */
}
/* end if the $_POST has NewQty in the variable name */
}
/* end loop around the $_POST array */
}
include 'includes/session.inc';
$Title = _('Tax Rates');
include 'includes/header.inc';
echo '<p class="page_title_text noPrint" >
<img src="' . $RootPath . '/css/' . $Theme . '/images/maintenance.png" title="' . _('Supplier Types') . '" alt="" />' . $Title . '
</p>';
if (!isset($TaxAuthority)) {
prnMsg(_('This page can only be called after selecting the tax authority to edit the rates for') . '. ' . _('Please select the Rates link from the tax authority page') . '<br /><a href="' . $RootPath . '/TaxAuthorities.php">' . _('click here') . '</a> ' . _('to go to the Tax Authority page'), 'error');
include 'includes/footer.inc';
exit;
}
if (isset($_POST['UpdateRates'])) {
$TaxRatesResult = DB_query("SELECT taxauthrates.taxcatid,\n\t\t\t\t\t\t\t\t\t\ttaxauthrates.taxrate,\n\t\t\t\t\t\t\t\t\t\ttaxauthrates.dispatchtaxprovince\n\t\t\t\t\t\t\t\tFROM taxauthrates\n\t\t\t\t\t\t\t\tWHERE taxauthrates.taxauthority='" . $TaxAuthority . "'", $db);
while ($myrow = DB_fetch_array($TaxRatesResult)) {
$sql = "UPDATE taxauthrates SET taxrate=" . filter_number_format($_POST[$myrow['dispatchtaxprovince'] . '_' . $myrow['taxcatid']]) / 100 . "\n\t\t\t\t\t\tWHERE taxcatid = '" . $myrow['taxcatid'] . "'\n\t\t\t\t\t\tAND dispatchtaxprovince = '" . $myrow['dispatchtaxprovince'] . "'\n\t\t\t\t\t\tAND taxauthority = '" . $TaxAuthority . "'";
DB_query($sql, $db);
}
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'])) {
if (filter_number_format($_POST['Amt' . $AllocCounter]) < 0) {
prnMsg(_('Amount entered was negative') . '. ' . _('Only positive amounts are allowed') . '.', 'warn');
$_POST['Amt' . $AllocCounter] = 0;
}
if (isset($_POST['All' . $AllocCounter]) and $_POST['All' . $AllocCounter] == True) {
$_POST['Amt' . $AllocCounter] = $_POST['YetToAlloc' . $AllocCounter];
}
if (filter_number_format($_POST['Amt' . $AllocCounter]) > $_POST['YetToAlloc' . $AllocCounter]) {
$_POST['Amt' . $AllocCounter] = locale_number_format($_POST['YetToAlloc' . $AllocCounter], $_SESSION['Alloc']->CurrDecimalPlaces);
// Amount entered must be smaller than unallocated amount
}
$_SESSION['Alloc']->Allocs[$_POST['AllocID' . $AllocCounter]]->AllocAmt = filter_number_format($_POST['Amt' . $AllocCounter]);
// recalcuate the new difference on exchange (a +positive amount is a gain -ve a loss)
$_SESSION['Alloc']->Allocs[$_POST['AllocID' . $AllocCounter]]->DiffOnExch = filter_number_format($_POST['Amt' . $AllocCounter]) / $_SESSION['Alloc']->TransExRate - filter_number_format($_POST['Amt' . $AllocCounter]) / $_SESSION['Alloc']->Allocs[$_POST['AllocID' . $AllocCounter]]->ExRate;
$TotalDiffOnExch += $_SESSION['Alloc']->Allocs[$_POST['AllocID' . $AllocCounter]]->DiffOnExch;
$TotalAllocated += filter_number_format($_POST['Amt' . $AllocCounter]);
}
}
if ($TotalAllocated + $_SESSION['Alloc']->TransAmt > 0.008) {
prnMsg(_('Allocation could not be processed because the amount allocated is more than the') . ' ' . $_SESSION['Alloc']->TransTypeName . ' ' . _('being allocated') . '<br />' . _('Total allocated') . ' = ' . $TotalAllocated . ' ' . _('and the total amount of the') . ' ' . $_SESSION['Alloc']->TransTypeName . ' ' . _('was') . ' ' . -$_SESSION['Alloc']->TransAmt, 'error');
$InputError = 1;
}
}
if (isset($_POST['UpdateDatabase'])) {
if ($InputError == 0) {
//
//========[ START TRANSACTION ]===========
//
$Error = '';
$Result = DB_Txn_Begin($db);
$AllAllocations = 0;
echo '<p class="page_title_text" align="center"><strong>' . _('No Sales Items') . '</strong></p>';
echo '<form action="PDFNoSalesItems.php" method="GET">
<table class="selection">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
$TableHeader = '<tr>
<th>' . _('No') . '</th>
<th>' . _('Location') . '</th>
<th>' . _('Code') . '</th>
<th>' . _('Description') . '</th>
<th>' . _('Location QOH') . '</th>
<th>' . _('Total QOH') . '</th>
<th>' . _('Units') . '</th>
</tr>';
echo $TableHeader;
echo '<input type="hidden" value="' . $_POST['Location'] . '" name="Location" />
<input type="hidden" value="' . filter_number_format($_POST['NumberOfDays']) . '" name="NumberOfDays" />
<input type="hidden" value="' . $_POST['Customers'] . '" name="Customers" />';
$k = 0;
//row colour counter
$i = 1;
while ($myrow = DB_fetch_array($result)) {
if ($k == 1) {
echo '<tr class="EvenTableRows">';
$k = 0;
} else {
echo '<tr class="OddTableRows">';
$k = 1;
}
$QOHResult = DB_query("SELECT sum(quantity)\n\t\t\t\tFROM locstock\n\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=locstock.loccode AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\tWHERE stockid = '" . $myrow['stockid'] . "'" . $WhereLocation);
$QOHRow = DB_fetch_row($QOHResult);
$QOH = $QOHRow[0];
function LevelNetting(&$db, $part, $eoq, $PanSize, $ShrinkFactor, $LeadTime)
{
// Create an array of mrprequirements and an array of mrpsupplies, then read through
// them seeing if all requirements are covered by supplies. Create a planned order
// for any unmet requirements. Change dates if necessary for the supplies.
//echo '<br />Part is ' . "$part" . '<br />';
// Get decimal places from stockmaster for rounding of shrinkage factor
$sql = "SELECT decimalplaces FROM stockmaster WHERE stockid = '" . $part . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$DecimalPlaces = $myrow[0];
// Load mrprequirements into $Requirements array
$sql = "SELECT * FROM mrprequirements WHERE part = '" . $part . "' ORDER BY daterequired";
$result = DB_query($sql, $db);
$Requirements = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($Requirements, $myrow);
$i++;
}
//end of while loop
// Load mrpsupplies into $Supplies array
$sql = "SELECT * FROM mrpsupplies WHERE part = '" . $part . "' ORDER BY duedate";
$result = DB_query($sql, $db);
$Supplies = array();
$i = 0;
while ($myrow = DB_fetch_array($result)) {
array_push($Supplies, $myrow);
$i++;
}
//end of while loop
// Go through all requirements and check if have supplies to cover them
$RequirementCount = count($Requirements);
$SupplyCount = count($Supplies);
$reqi = 0;
//Index for requirements
$supi = 0;
// index for supplies
$TotalRequirement = 0;
$TotalSupply = 0;
if ($RequirementCount > 0 && $SupplyCount > 0) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
$TotalSupply += $Supplies[$supi]['supplyquantity'];
while ($TotalRequirement > 0 && $TotalSupply > 0) {
$Supplies[$supi]['updateflag'] = 1;
// ******** Put leeway calculation in here ********
$DueDate = ConvertSQLDate($Supplies[$supi]['duedate']);
$ReqDate = ConvertSQLDate($Requirements[$reqi]['daterequired']);
$DateDiff = DateDiff($DueDate, $ReqDate, 'd');
//if ($Supplies[$supi]['duedate'] > $Requirements[$reqi]['daterequired']) {
if ($DateDiff > abs(filter_number_format($_POST['Leeway']))) {
$sql = "UPDATE mrpsupplies SET mrpdate = '" . $Requirements[$reqi]['daterequired'] . "' WHERE id = '" . $Supplies[$supi]['id'] . "' AND duedate = mrpdate";
$result = DB_query($sql, $db);
}
if ($TotalRequirement > $TotalSupply) {
$TotalRequirement -= $TotalSupply;
$Requirements[$reqi]['quantity'] -= $TotalSupply;
$TotalSupply = 0;
$Supplies[$supi]['supplyquantity'] = 0;
$supi++;
if ($SupplyCount > $supi) {
$TotalSupply += $Supplies[$supi]['supplyquantity'];
}
} elseif ($TotalRequirement < $TotalSupply) {
$TotalSupply -= $TotalRequirement;
$Supplies[$supi]['supplyquantity'] -= $TotalRequirement;
$TotalRequirement = 0;
$Requirements[$reqi]['quantity'] = 0;
$reqi++;
if ($RequirementCount > $reqi) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
}
} else {
$TotalSupply -= $TotalRequirement;
$Supplies[$supi]['supplyquantity'] -= $TotalRequirement;
$TotalRequirement = 0;
$Requirements[$reqi]['quantity'] = 0;
$reqi++;
if ($RequirementCount > $reqi) {
$TotalRequirement += $Requirements[$reqi]['quantity'];
}
$TotalRequirement -= $TotalSupply;
$Requirements[$reqi]['quantity'] -= $TotalSupply;
$TotalSupply = 0;
$Supplies[$supi]['supplyquantity'] = 0;
$supi++;
if ($SupplyCount > $supi) {
$TotalSupply += $Supplies[$supi]['supplyquantity'];
}
}
// End of if $TotalRequirement > $TotalSupply
}
// End of while
}
// End of if
// When get to this part of code, have gone through all requirements, If there is any
// unmet requirements, create an mrpplannedorder to cover it. Also call the
// CreateLowerLevelRequirement() function to create gross requirements for lower level parts.
// There is an excess quantity if the eoq is higher than the actual required amount.
// If there is a subsuquent requirement, the excess quantity is subtracted from that
// quantity. For instance, if the first requirement was for 2 and the eoq was 5, there
// would be an excess of 3; if there was another requirement for 3 or less, the excess
// would cover it, so no planned order would have to be created for the second requirement.
$ExcessQty = 0;
foreach ($Requirements as $key => $row) {
$DateRequired[$key] = $row['daterequired'];
}
if (count($Requirements)) {
array_multisort($DateRequired, SORT_ASC, $Requirements);
}
foreach ($Requirements as $Requirement) {
// First, inflate requirement if there is a shrinkage factor
// Should the quantity be rounded?
if ($_POST['ShrinkageFlag'] == 'y' and $ShrinkFactor > 0) {
$Requirement['quantity'] = $Requirement['quantity'] * 100 / (100 - $ShrinkFactor);
$Requirement['quantity'] = round($Requirement['quantity'], $DecimalPlaces);
}
if ($ExcessQty >= $Requirement['quantity']) {
$PlannedQty = 0;
$ExcessQty -= $Requirement['quantity'];
} else {
$PlannedQty = $Requirement['quantity'] - $ExcessQty;
$ExcessQty = 0;
}
if ($PlannedQty > 0) {
if ($_POST['EOQFlag'] == 'y' and $eoq > $PlannedQty) {
$ExcessQty = $eoq - $PlannedQty;
$PlannedQty = $eoq;
}
// Pansize calculation here
// if $PlannedQty not evenly divisible by $PanSize, calculate as $PlannedQty
// divided by $PanSize and rounded up to the next highest integer and then
// multiplied by the pansize. For instance, with a planned qty of 17 with a pansize
// of 5, divide 17 by 5 to get 3 with a remainder of 2, which is rounded up to 4
// and then multiplied by 5 - the pansize - to get 20
if ($_POST['PanSizeFlag'] == 'y' and $PanSize != 0 and $PlannedQty != 0) {
$PlannedQty = ceil($PlannedQty / $PanSize) * $PanSize;
}
// Calculate required date by subtracting leadtime from top part's required date
$PartRequiredDate = $Requirement['daterequired'];
if ((int) $LeadTime > 0) {
$CalendarSQL = "SELECT COUNT(*),cal2.calendardate\n\t\t\t\t\t\t FROM mrpcalendar\n\t\t\t\t\t\t\tLEFT JOIN mrpcalendar as cal2\n\t\t\t\t\t\t\t ON (mrpcalendar.daynumber - '" . $LeadTime . "') = cal2.daynumber\n\t\t\t\t\t\t WHERE mrpcalendar.calendardate = '" . $PartRequiredDate . "'\n\t\t\t\t\t\t\tAND cal2.manufacturingflag='1'\n\t\t\t\t\t\t\tGROUP BY cal2.calendardate";
$ResultDate = DB_query($CalendarSQL, $db);
$myrowdate = DB_fetch_array($ResultDate);
$NewDate = $myrowdate[1];
// If can't find date based on manufacturing calendar, use $PartRequiredDate
} else {
// Convert $PartRequiredDate from mysql format to system date format, use that to subtract leadtime
// from it using DateAdd, convert that date back to mysql format
$ConvertDate = ConvertSQLDate($PartRequiredDate);
$DateAdd = DateAdd($ConvertDate, 'd', $LeadTime * -1);
$NewDate = FormatDateForSQL($DateAdd);
}
$sql = "INSERT INTO mrpplannedorders (id,\n\t\t\t\t\t\t\t\t\t\t\t\tpart,\n\t\t\t\t\t\t\t\t\t\t\t\tduedate,\n\t\t\t\t\t\t\t\t\t\t\t\tsupplyquantity,\n\t\t\t\t\t\t\t\t\t\t\t\tordertype,\n\t\t\t\t\t\t\t\t\t\t\t\torderno,\n\t\t\t\t\t\t\t\t\t\t\t\tmrpdate,\n\t\t\t\t\t\t\t\t\t\t\t\tupdateflag)\n\t\t\t\t\t\t\t\t\t\t\tVALUES (NULL,\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['part'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $NewDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $PlannedQty . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['mrpdemandtype'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $Requirement['orderno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $NewDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'0')";
$result = DB_query($sql, $db);
// If part has lower level components, create requirements for them
$sql = "SELECT COUNT(*) FROM bom\n\t\t\t\t\t WHERE parent ='" . $Requirement['part'] . "'\n\t\t\t\t\t GROUP BY parent";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
if ($myrow[0] > 0) {
CreateLowerLevelRequirement($db, $Requirement['part'], $Requirement['daterequired'], $PlannedQty, $Requirement['mrpdemandtype'], $Requirement['orderno'], $Requirement['whererequired']);
}
}
// End of if $PlannedQty > 0
}
// End of foreach $Requirements
// If there are any supplies not used and updateflag is zero, those supplies are not
// necessary, so change date
foreach ($Supplies as $supply) {
if ($supply['supplyquantity'] > 0 && $supply['updateflag'] == 0) {
$id = $supply['id'];
$sql = "UPDATE mrpsupplies SET mrpdate ='2050-12-31' WHERE id = '" . $id . "'\n\t\t\t\t\t AND ordertype <> 'QOH'";
$result = DB_query($sql, $db);
}
}
}
prnMsg(_('A recurring order cannot be made from a quotation'), 'error');
}
if ($InputErrors == 0) {
/*Error checks above all passed ok so lets go*/
if ($NewRecurringOrder == 'Yes') {
/* finally write the recurring order header to the database and then the line details*/
$DelDate = FormatDateforSQL($_SESSION['Items' . $identifier]->DeliveryDate);
$HeaderSQL = "INSERT INTO recurringsalesorders (\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\tcustomerref,\n\t\t\t\t\t\t\t\t\t\tcomments,\n\t\t\t\t\t\t\t\t\t\torddate,\n\t\t\t\t\t\t\t\t\t\tordertype,\n\t\t\t\t\t\t\t\t\t\tdeliverto,\n\t\t\t\t\t\t\t\t\t\tdeladd1,\n\t\t\t\t\t\t\t\t\t\tdeladd2,\n\t\t\t\t\t\t\t\t\t\tdeladd3,\n\t\t\t\t\t\t\t\t\t\tdeladd4,\n\t\t\t\t\t\t\t\t\t\tdeladd5,\n\t\t\t\t\t\t\t\t\t\tdeladd6,\n\t\t\t\t\t\t\t\t\t\tcontactphone,\n\t\t\t\t\t\t\t\t\t\tcontactemail,\n\t\t\t\t\t\t\t\t\t\tfreightcost,\n\t\t\t\t\t\t\t\t\t\tfromstkloc,\n\t\t\t\t\t\t\t\t\t\tshipvia,\n\t\t\t\t\t\t\t\t\t\tlastrecurrence,\n\t\t\t\t\t\t\t\t\t\tstopdate,\n\t\t\t\t\t\t\t\t\t\tfrequency,\n\t\t\t\t\t\t\t\t\t\tautoinvoice)\n\t\t\t\t\t\t\t\t\tvalues (\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->Branch . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->CustRef . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->Comments . "',\n\t\t\t\t\t\t\t\t\t\t'" . Date('Y-m-d H:i') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DefaultSalesType . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DeliverTo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd1 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd2 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd3 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd4 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd5 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->DelAdd6 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->PhoneNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->Email . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->FreightCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->Location . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Items' . $identifier]->ShipVia . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateforSQL($_POST['StartDate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateforSQL($_POST['StopDate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Frequency'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['AutoInvoice'] . "')";
$ErrMsg = _('The recurring order cannot be added because');
$DbgMsg = _('The SQL that failed was');
$InsertQryResult = DB_query($HeaderSQL, $ErrMsg, $DbgMsg, true);
$RecurrOrderNo = DB_Last_Insert_ID($db, 'recurringsalesorders', 'recurrorderno');
echo 'xxx' . $RecurrOrderNo;
$StartOf_LineItemsSQL = "INSERT INTO recurrsalesorderdetails (recurrorderno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tstkcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tnarrative)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES ('";
foreach ($_SESSION['Items' . $identifier]->LineItems as $StockItem) {
$LineItemsSQL = $StartOf_LineItemsSQL . $RecurrOrderNo . "',\n\t\t\t\t\t\t\t\t'" . $StockItem->StockID . "',\n\t\t\t\t\t\t\t\t'" . filter_number_format($StockItem->Price) . "',\n\t\t\t\t\t\t\t\t'" . filter_number_format($StockItem->Quantity) . "',\n\t\t\t\t\t\t\t\t'" . filter_number_format($StockItem->DiscountPercent) . "',\n\t\t\t\t\t\t\t\t'" . $StockItem->Narrative . "')";
$Ins_LineItemResult = DB_query($LineItemsSQL, $ErrMsg, $DbgMsg, true);
}
/* inserted line items into sales order details */
$result = DB_Txn_Commit();
prnmsg(_('The new recurring order template has been added'), 'success');
} else {
/* must be updating an existing recurring order */
$HeaderSQL = "UPDATE recurringsalesorders SET\n\t\t\t\t\t\tstopdate = '" . FormatDateforSQL($_POST['StopDate']) . "',\n\t\t\t\t\t\tfrequency = '" . $_POST['Frequency'] . "',\n\t\t\t\t\t\tautoinvoice = '" . $_POST['AutoInvoice'] . "'\n\t\t\t\t\tWHERE recurrorderno = '" . $_POST['ExistingRecurrOrderNo'] . "'";
$ErrMsg = _('The recurring order cannot be updated because');
$UpdateQryResult = DB_query($HeaderSQL, $ErrMsg);
prnmsg(_('The recurring order template has been updated'), 'success');
}
echo '<p><a href="' . $RootPath . '/SelectOrderItems.php?NewOrder=Yes">' . _('Enter New Sales Order') . '</a>';
echo '<p><a href="' . $RootPath . '/SelectRecurringSalesOrder.php">' . _('Select A Recurring Sales Order Template') . '</a>';
unset($_SESSION['Items' . $identifier]->LineItems);
function InvoiceSalesOrder($OrderNo, $User, $Password)
{
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyOrderHeaderExists($OrderNo, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) != 0) {
return $Errors;
}
/*Does not deal with assembly items or serialise/lot track items - for use by POS */
/*Get Company Defaults */
$ReadCoyResult = api_DB_query("SELECT debtorsact,\n\t\t\t\t\t\t\t\t\t\t\t\tfreightact,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_debtors,\n\t\t\t\t\t\t\t\t\t\t\t\tgllink_stock\n\t\t\t\t\t\t\t\t\t\tFROM companies\n\t\t\t\t\t\t\t\t\t\tWHERE coycode=1", $db);
$CompanyRecord = DB_fetch_array($ReadCoyResult);
if (DB_error_no($db) != 0) {
$Errors[] = NoCompanyRecord;
}
$OrderHeaderSQL = "SELECT salesorders.debtorno,\n\t\t\t\t \t\t\t\t debtorsmaster.name,\n\t\t\t\t\t\t\t\t salesorders.branchcode,\n\t\t\t\t\t\t\t\t salesorders.customerref,\n\t\t\t\t\t\t\t\t salesorders.orddate,\n\t\t\t\t\t\t\t\t salesorders.ordertype,\n\t\t\t\t\t\t\t\t salesorders.shipvia,\n\t\t\t\t\t\t\t\t custbranch.area,\n\t\t\t\t\t\t\t\t custbranch.taxgroupid,\n\t\t\t\t\t\t\t\t debtorsmaster.currcode,\n\t\t\t\t\t\t\t\t currencies.rate,\n\t\t\t\t\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t\t\t\t\t custbranch.salesman\n\t\t\t\t\t\t\tFROM salesorders\n\t\t\t\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\t\t\tON salesorders.debtorno = debtorsmaster.debtorno\n\t\t\t\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\t\t\tON salesorders.debtorno = custbranch.debtorno\n\t\t\t\t\t\t\tAND salesorders.branchcode = custbranch.branchcode\n\t\t\t\t\t\t\tINNER JOIN locations\n\t\t\t\t\t\t\tON locations.loccode=salesorders.fromstkloc\n\t\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\t\t\t\t\tWHERE salesorders.orderno = '" . $OrderNo . "'";
$OrderHeaderResult = api_DB_query($OrderHeaderSQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoReadOrder;
}
$OrderHeader = DB_fetch_array($OrderHeaderResult);
$TaxProvResult = api_DB_query("SELECT taxprovinceid FROM locations WHERE loccode='" . $OrderHeader['fromstkloc'] . "'", $db);
if (DB_error_no($db) != 0) {
$Errors[] = NoTaxProvince;
}
$myrow = DB_fetch_row($TaxProvResult);
$DispTaxProvinceID = $myrow[0];
$LineItemsSQL = "SELECT stkcode,\n\t\t\t\t\t\t\t\tunitprice,\n\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\ttaxcatid,\n\t\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\t\tmaterialcost+labourcost+overheadcost AS standardcost\n\t\t\t\t\t\tFROM salesorderdetails INNER JOIN stockmaster\n\t\t\t\t\t\tON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t\t\tWHERE orderno ='" . $OrderNo . "'\n\t\t\t\t\t\tAND completed=0";
$LineItemsResult = api_DB_query($LineItemsSQL, $db);
if (DB_error_no($db) != 0 or DB_num_rows($LineItemsResult) == 0) {
$Errors[] = NoReadOrderLines;
return $Errors;
}
/*Start an SQL transaction */
$result = DB_Txn_Begin($db);
/*Now Get the next invoice number - function in SQL_CommonFunctions*/
$InvoiceNo = GetNextTransNo(10, $db);
$PeriodNo = GetCurrentPeriod($db);
$TaxTotals = array();
$TotalFXNetInvoice = 0;
$TotalFXTax = 0;
$LineCounter = 0;
while ($OrderLineRow = DB_fetch_array($LineItemsResult)) {
$StandardCost = $OrderLineRow['standardcost'];
$LocalCurrencyPrice = $OrderLineRow['unitprice'] * (1 - floatval($OrderLineRow['discountpercent'])) / $OrderHeader['rate'];
$LineNetAmount = $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * (1 - floatval($OrderLineRow['discountpercent']));
/*Gets the Taxes and rates applicable to this line from the TaxGroup of the branch and TaxCategory of the item
and the taxprovince of the dispatch location */
$SQL = "SELECT taxgrouptaxes.calculationorder,\n\t\t\t\t\t\t\ttaxauthorities.description,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxauthid,\n\t\t\t\t\t\t\ttaxauthorities.taxglcode,\n\t\t\t\t\t\t\ttaxgrouptaxes.taxontax,\n\t\t\t\t\t\t\ttaxauthrates.taxrate\n\t\t\t\t\tFROM taxauthrates INNER JOIN taxgrouptaxes ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxgrouptaxes.taxauthid\n\t\t\t\t\t\tINNER JOIN taxauthorities ON\n\t\t\t\t\t\ttaxauthrates.taxauthority=taxauthorities.taxid\n\t\t\t\t\tWHERE taxgrouptaxes.taxgroupid='" . $OrderHeader['taxgroupid'] . "'\n\t\t\t\t\tAND taxauthrates.dispatchtaxprovince='" . $DispTaxProvinceID . "'\n\t\t\t\t\tAND taxauthrates.taxcatid = '" . $OrderLineRow['taxcatid'] . "'\n\t\t\t\t\tORDER BY taxgrouptaxes.calculationorder";
$GetTaxRatesResult = api_DB_query($SQL, $db);
if (DB_error_no($db) != 0) {
$Errors[] = TaxRatesFailed;
}
$LineTaxAmount = 0;
while ($myrow = DB_fetch_array($GetTaxRatesResult)) {
if (!isset($TaxTotals[$myrow['taxauthid']]['FXAmount'])) {
$TaxTotals[$myrow['taxauthid']]['FXAmount'] = 0;
}
$TaxAuthID = $myrow['taxauthid'];
$TaxTotals[$myrow['taxauthid']]['GLCode'] = $myrow['taxglcode'];
$TaxTotals[$myrow['taxauthid']]['TaxRate'] = $myrow['taxrate'];
$TaxTotals[$myrow['taxauthid']]['TaxAuthDescription'] = $myrow['description'];
if ($myrow['taxontax'] == 1) {
$TaxAuthAmount = ($LineNetAmount + $LineTaxAmount) * $myrow['taxrate'];
} else {
$TaxAuthAmount = $LineNetAmount * $myrow['taxrate'];
}
$TaxTotals[$myrow['taxauthid']]['FXAmount'] += $TaxAuthAmount;
/*Make an array of the taxes and amounts including GLcodes for later posting - need debtortransid
so can only post once the debtor trans is posted - can only post debtor trans when all tax is calculated */
$LineTaxes[$LineCounter][$myrow['calculationorder']] = array('TaxCalculationOrder' => $myrow['calculationorder'], 'TaxAuthID' => $myrow['taxauthid'], 'TaxAuthDescription' => $myrow['description'], 'TaxRate' => $myrow['taxrate'], 'TaxOnTax' => $myrow['taxontax'], 'TaxAuthAmount' => $TaxAuthAmount);
$LineTaxAmount += $TaxAuthAmount;
}
//end loop around Taxes
$TotalFXNetInvoice += $LineNetAmount;
$TotalFXTax += $LineTaxAmount;
/*Now update SalesOrderDetails for the quantity invoiced and the actual dispatch dates. */
$SQL = "UPDATE salesorderdetails\n\t\t\t\t\tSET qtyinvoiced = qtyinvoiced + " . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tactualdispatchdate = '" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\tcompleted='1'\n\t\t\t\t\tWHERE orderno = '" . $OrderNo . "'\n\t\t\t\t\tAND stkcode = '" . $OrderLineRow['stkcode'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['mbflag'] == 'B' or $OrderLineRow['mbflag'] == 'M') {
$Assembly = False;
/* Need to get the current location quantity
will need it later for the stock movement */
$SQL = "SELECT locstock.quantity\n\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\tWHERE locstock.stockid='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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;
}
$SQL = "UPDATE locstock\n\t\t\t\t\t\tSET quantity = locstock.quantity - " . $OrderLineRow['quantity'] . "\n\t\t\t\t\t\tWHERE locstock.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $OrderLineRow['quantity']) . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
} else {
if ($OrderLineRow['mbflag'] == 'A') {
/* its an assembly */
/*Need to get the BOM for this part and make
stock moves for the components then update the Location stock balances */
$Assembly = True;
$StandardCost = 0;
/*To start with - accumulate the cost of the comoponents for use in journals later on */
$SQL = "SELECT bom.component,\n\t\t\t\t\t\t\t\tbom.quantity,\n\t\t\t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standard\n\t\t\t\t\t\t\tFROM bom INNER JOIN stockmaster\n\t\t\t\t\t\t\tON bom.component=stockmaster.stockid\n\t\t\t\t\t\t\tWHERE bom.parent='" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\tAND bom.effectiveto >= '" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\tAND bom.effectiveafter < '" . Date('Y-m-d') . "'";
$AssResult = api_DB_query($SQL, $db);
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\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE locstock.stockid='" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode= '" . $OrderHeader['fromstkloc'] . "'";
$Result = api_DB_query($SQL, $db);
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 (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tshow_on_inv_crds,\n\t\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $AssParts['component'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 10,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $DefaultDispatchDate . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . _('Assembly') . ': ' . $OrderLineRow['stkcode'] . ' ' . _('Order') . ': ' . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . -$AssParts['quantity'] * $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $AssParts['standard'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t 0,\n\t\t\t\t\t\t\t\t\t\t\t\t '" . ($QtyOnHandPrior - $AssParts['quantity'] * $OrderLineRow['quantity']) . "'\t)";
$Result = DB_query($SQL, $db, '', '', true);
$SQL = "UPDATE locstock\n\t\t\t\t\t\t\tSET quantity = locstock.quantity - " . $AssParts['quantity'] * $OrderLineRow['quantity'] . "\n\t\t\t\t\t\t\tWHERE locstock.stockid = '" . $AssParts['component'] . "'\n\t\t\t\t\t\t\tAND loccode = '" . $OrderHeader['fromlocstk'] . "'";
$Result = DB_query($SQL, $db, '', '', true);
}
/* end of assembly explosion and updates */
}
}
/* end of its an assembly */
if ($OrderLineRow['mbflag'] == 'A' or $OrderLineRow['mbflag'] == 'D') {
/*it's a Dummy/Service item or an Assembly item - still need stock movement record
* but quantites on hand are always nil */
$SQL = "INSERT INTO stockmoves (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\tdiscountpercent,\n\t\t\t\t\t\t\t\t\t\t\t\tstandardcost,\n\t\t\t\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\tVALUES ('" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t'10',\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['fromstkloc'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t'" . $LocalCurrencyPrice . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t'" . -$OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] . "',\n\t\t\t\t\t\t\t\t'" . $StandardCost . "',\n\t\t\t\t\t\t\t\t'0' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/*Get the ID of the StockMove... */
$StkMoveNo = DB_Last_Insert_ID($db, 'stockmoves', 'stkmoveno');
/*Insert the taxes that applied to this line */
foreach ($LineTaxes[$LineCounter] as $Tax) {
$SQL = "INSERT INTO stockmovestaxes (stkmoveno,\n\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\ttaxrate,\n\t\t\t\t\t\t\t\t\ttaxcalculationorder,\n\t\t\t\t\t\t\t\t\ttaxontax)\n\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxAuthID'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxRate'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxCalculationOrder'] . "',\n\t\t\t\t\t\t\t'" . $Tax['TaxOnTax'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*Insert Sales Analysis records */
$SQL = "SELECT COUNT(*),\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.salesperson,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.stockid\n\t\t\t\t\tFROM salesanalysis,\n\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\tstockmaster\n\t\t\t\t\tWHERE salesanalysis.stkcategory=stockmaster.categoryid\n\t\t\t\t\tAND salesanalysis.stockid=stockmaster.stockid\n\t\t\t\t\tAND salesanalysis.cust=custbranch.debtorno\n\t\t\t\t\tAND salesanalysis.custbranch=custbranch.branchcode\n\t\t\t\t\tAND salesanalysis.area=custbranch.area\n\t\t\t\t\tAND salesanalysis.salesperson=custbranch.salesman\n\t\t\t\t\tAND salesanalysis.typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\tAND salesanalysis.periodno='" . $PeriodNo . "'\n\t\t\t\t\tAND salesanalysis.cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\tAND salesanalysis.custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\tAND salesanalysis.stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\tAND salesanalysis.budgetoractual='1'\n\t\t\t\t\tGROUP BY salesanalysis.stockid,\n\t\t\t\t\t\tsalesanalysis.stkcategory,\n\t\t\t\t\t\tsalesanalysis.cust,\n\t\t\t\t\t\tsalesanalysis.custbranch,\n\t\t\t\t\t\tsalesanalysis.area,\n\t\t\t\t\t\tsalesanalysis.periodno,\n\t\t\t\t\t\tsalesanalysis.typeabbrev,\n\t\t\t\t\t\tsalesanalysis.salesperson";
$ErrMsg = _('The count of existing Sales analysis records could not run because');
$DbgMsg = _('SQL to count the no of sales analysis records');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$myrow = DB_fetch_row($Result);
if ($myrow[0] > 0) {
/*Update the existing record that already exists */
$SQL = "UPDATE salesanalysis\n\t\t\t\t\t\tSET amt=amt+" . filter_number_format($OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . ",\n\t\t\t\t\t\tqty=qty +" . $OrderLineRow['quantity'] . ",\n\t\t\t\t\t\tdisc=disc+" . filter_number_format($OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate']) . "\n\t\t\t\t\t\tWHERE salesanalysis.area='" . $myrow[2] . "'\n\t\t\t\t\t\tAND salesanalysis.salesperson='" . $myrow[3] . "'\n\t\t\t\t\t\tAND typeabbrev ='" . $OrderHeader['ordertype'] . "'\n\t\t\t\t\t\tAND periodno = '" . $PeriodNo . "'\n\t\t\t\t\t\tAND cust " . LIKE . " '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\tAND custbranch " . LIKE . " '" . $OrderHeader['branchcode'] . "'\n\t\t\t\t\t\tAND stockid " . LIKE . " '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\tAND salesanalysis.stkcategory ='" . $myrow[1] . "'\n\t\t\t\t\t\tAND budgetoractual='1'";
} else {
/* insert a new sales analysis record */
$SQL = "INSERT INTO salesanalysis (\ttypeabbrev,\n\t\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcust,\n\t\t\t\t\t\t\t\t\t\t\t\t\tcustbranch,\n\t\t\t\t\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdisc,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\tarea,\n\t\t\t\t\t\t\t\t\t\t\t\t\tbudgetoractual,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsalesperson,\n\t\t\t\t\t\t\t\t\t\t\t\t\tstkcategory )\n\t\t\t\t\t\t\t\tSELECT '" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['quantity'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['discountpercent'] * $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $OrderLineRow['stkcode'] . "',\n\t\t\t\t\t\t\t\t\tcustbranch.area,\n\t\t\t\t\t\t\t\t\t1,\n\t\t\t\t\t\t\t\t\tcustbranch.salesman,\n\t\t\t\t\t\t\t\t\tstockmaster.categoryid\n\t\t\t\t\t\t\t\tFROM stockmaster, custbranch\n\t\t\t\t\t\t\t\tWHERE stockmaster.stockid = '" . $OrderLineRow['stkcode'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.debtorno = '" . $OrderHeader['debtorno'] . "'\n\t\t\t\t\t\t\t\tAND custbranch.branchcode='" . $OrderHeader['branchcode'] . "'";
}
$Result = api_DB_query($SQL, $db, '', '', true);
if ($CompanyRecord['gllink_stock'] == 1 and $StandardCost != 0) {
/*first the cost of sales entry - GL accounts are retrieved using the function GetCOGSGLAccount from includes/GetSalesTransGLCodes.inc */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . GetCOGSGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
/*now the stock entry - this is set to the cost act in the case of a fixed asset disposal */
$StockGLCode = GetStockGLCode($OrderLineRow['stkcode'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $StockGLCode['stockact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $StandardCost . "',\n\t\t\t\t\t\t\t\t\t\t'" . -$StandardCost * $OrderLineRow['quantity'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
/* end of if GL and stock integrated and standard cost !=0 and not an asset */
if ($CompanyRecord['gllink_debtors'] == 1 and $OrderLineRow['unitprice'] != 0) {
//Post sales transaction to GL credit sales
$SalesGLAccounts = GetSalesGLAccount($OrderHeader['area'], $OrderLineRow['stkcode'], $OrderHeader['ordertype'], $db);
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $SalesGLAccounts['salesglcode'] . "',\n\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " x " . $OrderLineRow['quantity'] . " @ " . $OrderLineRow['unitprice'] . "',\n\t\t\t\t\t\t'" . -$OrderLineRow['unitprice'] * $OrderLineRow['quantity'] / $OrderHeader['rate'] . "'\n\t\t\t\t\t)";
$Result = api_DB_query($SQL, $db, '', '', true);
if ($OrderLineRow['discountpercent'] != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $SalesGLAccounts['discountglcode'] . "',\n\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . " - " . $OrderLineRow['stkcode'] . " @ " . $OrderLineRow['discountpercent'] * 100 . "%',\n\t\t\t\t\t\t\t\t'" . $OrderLineRow['unitprice'] * $OrderLineRow['quantity'] * $OrderLineRow['discountpercent'] / $OrderHeader['rate'] . "')";
$Result = DB_query($SQL, $db, '', '', true);
}
/*end of if discount !=0 */
}
/*end of if sales integrated with gl */
$LineCounter++;
//needed for the array of taxes by line
}
/*end of OrderLine loop */
$TotalInvLocalCurr = ($TotalFXNetInvoice + $TotalFXTax) / $OrderHeader['rate'];
if ($CompanyRecord['gllink_debtors'] == 1) {
/*Now post the tax to the GL at local currency equivalent */
if ($CompanyRecord['gllink_debtors'] == 1 and $TaxAuthAmount != 0) {
/*Loop through the tax authorities array to post each total to the taxauth glcode */
foreach ($TaxTotals as $Tax) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\t\tamount )\n\t\t\t\t\t\t\t\t\t\t\tVALUES (10,\n\t\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Tax['GLCode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "-" . $Tax['TaxAuthDescription'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$Tax['FXAmount'] / $OrderHeader['rate'] . "' )";
$Result = api_DB_query($SQL, $db, '', '', true);
}
}
/*Post debtors transaction to GL debit debtors, credit freight re-charged and credit sales */
if ($TotalInvLocalCurr != 0) {
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ('10',\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $CompanyRecord['debtorsact'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalInvLocalCurr . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
EnsureGLEntriesBalance(10, $InvoiceNo, $db);
}
/*end of if Sales and GL integrated */
/*Update order header for invoice charged on */
$SQL = "UPDATE salesorders SET comments = CONCAT(comments,' Inv ','" . $InvoiceNo . "') WHERE orderno= '" . $OrderNo . "'";
$Result = api_DB_query($SQL, $db, '', '', true);
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\t\torder_,\n\t\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\tshipvia)\n\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t'" . $InvoiceNo . "',\n\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['debtorno'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['branchcode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['orddate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . date('Y-m-d H-i-s') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['customerref'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['ordertype'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXNetInvoice . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TotalFXTax . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['rate'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OrderHeader['shipvia'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/*for each Tax - need to insert into debtortranstaxes */
foreach ($TaxTotals as $TaxAuthID => $Tax) {
$SQL = "INSERT INTO debtortranstaxes (debtortransid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\t\t\t\t\t\ttaxamount)\n\t\t\t\t\t\t\t\tVALUES ('" . $DebtorTransID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Tax['FXAmount'] / $OrderHeader['rate'] . "')";
$Result = api_DB_query($SQL, $db, '', '', true);
}
if (sizeof($Errors) == 0) {
$Result = DB_Txn_Commit($db);
$Errors[0] = 0;
$Errors[1] = $InvoiceNo;
} else {
$Result = DB_Txn_Rollback($db);
}
return $Errors;
}
$result = DB_query("SELECT assetid FROM fixedassets WHERE assetid='" . $_POST['AssetID'] . "'", $db);
if (DB_num_rows($result) == 0) {
prnMsg(_('The asset ID entered manually is not a valid fixed asset. If you do not know the asset reference, select it from the list'), 'error');
$InputError = True;
unset($_POST['AssetID']);
}
//DB_num_rows($result) == 0
}
if (!is_numeric(filter_number_format($_POST['Amount']))) {
prnMsg(_('The amount entered is not numeric. This fixed asset cannot be added to the invoice'), 'error');
$InputError = True;
unset($_POST['Amount']);
}
//!is_numeric(filter_number_format($_POST['Amount']))
if ($InputError == False) {
$_SESSION['SuppTrans']->Add_Asset_To_Trans($_POST['AssetID'], filter_number_format($_POST['Amount']));
unset($_POST['AssetID']);
unset($_POST['Amount']);
}
//$InputError == False
}
//isset($_POST['AddAssetToInvoice'])
if (isset($_GET['Delete'])) {
$_SESSION['SuppTrans']->Remove_Asset_From_Trans($_GET['Delete']);
}
//isset($_GET['Delete'])
/*Show all the selected ShiptRefs so far from the SESSION['SuppInv']->Shipts array */
if ($_SESSION['SuppTrans']->InvoiceOrCredit == 'Invoice') {
echo '<div class="centre"><p class="page_title_text noPrint" >' . _('Fixed Assets on Invoice') . ' ';
} else {
echo '<div class="centre"><p class="page_title_text noPrint" >' . _('Fixed Asset credits on Credit Note') . ' ';
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
if (isset($_POST['SelectedTabs'])) {
$SelectedTabs = mb_strtoupper($_POST['SelectedTabs']);
} elseif (isset($_GET['SelectedTabs'])) {
$SelectedTabs = mb_strtoupper($_GET['SelectedTabs']);
}
if (isset($_POST['SelectedIndex'])) {
$SelectedIndex = $_POST['SelectedIndex'];
} elseif (isset($_GET['SelectedIndex'])) {
$SelectedIndex = $_GET['SelectedIndex'];
}
if (isset($_POST['Days'])) {
$Days = filter_number_format($_POST['Days']);
} elseif (isset($_GET['Days'])) {
$Days = filter_number_format($_GET['Days']);
}
if (isset($_POST['Process'])) {
if ($SelectedTabs == '') {
prnMsg(_('You Must First Select a Petty Cash Tab To Authorise'), 'error');
unset($SelectedTabs);
}
}
if (isset($_POST['Go'])) {
if ($Days <= 0) {
prnMsg(_('The number of days must be a positive number'), 'error');
$Days = 30;
}
}
if (isset($SelectedTabs)) {
echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/magnifier.png" title="' . _('Petty Cash') . '" alt="" />' . _('Authorisation Of Petty Cash Expenses ') . '' . $SelectedTabs . '</p>';
<td><select name="Currency">';
$sql = "SELECT currency, currabrev FROM currencies";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
if ($myrow['currabrev'] == $_SESSION['CompanyRecord']['currencydefault']) {
echo '<option selected="selected" value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
} else {
echo '<option value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
}
}
echo '</select></td>
</tr>';
if (!isset($_POST['ExRate']) or !is_numeric(filter_number_format($_POST['ExRate']))) {
$DefaultExRate = '1';
} else {
$DefaultExRate = filter_number_format($_POST['ExRate']);
}
echo '<tr>
<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";
} else {
$_POST['ShiptRef'] = $_POST['ShiptSelection'];
}
} else {
$result = DB_query("SELECT shiptref FROM shipments WHERE shiptref='" . $_POST['ShiptRef'] . "'", $db);
if (DB_num_rows($result) == 0) {
prnMsg(_('The shipment entered manually is not a valid shipment reference. If you do not know the shipment reference, select it from the list'), 'error');
$InputError = True;
}
}
if (!is_numeric(filter_number_format($_POST['Amount']))) {
prnMsg(_('The amount entered is not numeric') . '. ' . _('This shipment charge cannot be added to the invoice'), 'error');
$InputError = True;
}
if ($InputError == False) {
$_SESSION['SuppTrans']->Add_Shipt_To_Trans($_POST['ShiptRef'], filter_number_format($_POST['Amount']));
unset($_POST['ShiptRef']);
unset($_POST['Amount']);
}
}
if (isset($_GET['Delete'])) {
$_SESSION['SuppTrans']->Remove_Shipt_From_Trans($_GET['Delete']);
}
/*Show all the selected ShiptRefs so far from the SESSION['SuppInv']->Shipts array */
if ($_SESSION['SuppTrans']->InvoiceOrCredit == 'Invoice') {
echo '<p class="page_title_text">' . _('Shipment charges on Invoice') . ' ';
} else {
echo '<p class="page_title_text">' . _('Shipment credits on Credit Note') . ' ';
}
echo $_SESSION['SuppTrans']->SuppReference . ' ' . _('From') . ' ' . $_SESSION['SuppTrans']->SupplierName;
echo '</p>';
foreach ($LineItem->Taxes as $TaxLine) {
if (isset($_POST[$LineItem->LineNumber . $TaxLine->TaxCalculationOrder . '_TaxRate'])) {
$_SESSION['CreditItems' . $identifier]->LineItems[$LineItem->LineNumber]->Taxes[$TaxLine->TaxCalculationOrder]->TaxRate = filter_number_format($_POST[$LineItem->LineNumber . $TaxLine->TaxCalculationOrder . '_TaxRate']) / 100;
}
}
}
if ($Quantity < 0 or $Price < 0 or $DiscountPercentage > 100 or $DiscountPercentage < 0) {
prnMsg(_('The item could not be updated because you are attempting to set the quantity credited to less than 0 or the price less than 0 or the discount more than 100% or less than 0%'), 'warn');
} elseif (isset($_POST['Quantity_' . $LineItem->LineNumber])) {
$_SESSION['CreditItems' . $identifier]->update_cart_item($LineItem->LineNumber, $Quantity, $Price, $DiscountPercentage / 100, $Narrative, 'No', $LineItem->ItemDue, $LineItem->POLine, 0, $identifier);
}
}
}
foreach ($_SESSION['CreditItems' . $identifier]->FreightTaxes as $FreightTaxLine) {
if (isset($_POST['FreightTaxRate' . $FreightTaxLine->TaxCalculationOrder])) {
$_SESSION['CreditItems' . $identifier]->FreightTaxes[$FreightTaxLine->TaxCalculationOrder]->TaxRate = filter_number_format($_POST['FreightTaxRate' . $FreightTaxLine->TaxCalculationOrder]) / 100;
}
}
if (isset($_POST['NewItem'])) {
/* get the item details from the database and hold them in the cart object make the quantity 1 by default then add it to the cart */
$AlreadyOnThisCredit = 0;
foreach ($_SESSION['CreditItems' . $identifier]->LineItems as $OrderItem) {
/* do a loop round the items on the credit note to see that the item
is not already on this credit note */
if ($_SESSION['SO_AllowSameItemMultipleTimes'] == 0 and strcasecmp($OrderItem->StockID, $_POST['NewItem']) == 0) {
$AlreadyOnThisCredit = 1;
prnMsg(_('The item selected is already on this credit the system will not allow the same item on the credit note more than once. However you can change the quantity credited of the existing line if necessary.'), 'warn');
}
}
/* end of the foreach loop to look for preexisting items of the same code */
if ($AlreadyOnThisCredit != 1) {
if ($_POST['CostType'] != 'OtherPriceList') {
$RoundedPrice = round(($Cost * (1 + $IncrementPercentage) * $CurrencyRate + $_POST['RoundingFactor'] / 2) / $_POST['RoundingFactor']) * $_POST['RoundingFactor'];
if ($RoundedPrice <= 0) {
$RoundedPrice = $_POST['RoundingFactor'];
}
} else {
$RoundedPrice = round(($Cost * (1 + $IncrementPercentage) + $_POST['RoundingFactor'] / 2) / $_POST['RoundingFactor']) * $_POST['RoundingFactor'];
if ($RoundedPrice <= 0) {
$RoundedPrice = $_POST['RoundingFactor'];
}
}
if ($Cost > 0) {
$CurrentPriceResult = DB_query("SELECT price,\n\t\t\t\t\t\t\t\t\t\t\t \t\t startdate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t enddate\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM prices\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE typeabbrev= '" . $_POST['PriceList'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND debtorno =''\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $_POST['CurrCode'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND startdate <='" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND (enddate>='" . Date('Y-m-d') . "' OR enddate='0000-00-00')\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND stockid='" . $myrow['stockid'] . "'");
if (DB_num_rows($CurrentPriceResult) == 1) {
$DayPriorToNewPrice = DateAdd($_POST['PriceStartDate'], 'd', -1);
$CurrentPriceRow = DB_fetch_array($CurrentPriceResult);
$UpdateSQL = "UPDATE prices SET enddate='" . FormatDateForSQL($DayPriorToNewPrice) . "'\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE typeabbrev='" . $_POST['PriceList'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $_POST['CurrCode'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND debtorno=''\n\t\t\t\t\t\t\t\t\t\t\t\tAND startdate ='" . $CurrentPriceRow['startdate'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND enddate ='" . $CurrentPriceRow['enddate'] . "'\n\t\t\t\t\t\t\t\t\t\t\t\tAND stockid='" . $myrow['stockid'] . "'";
$ErrMsg = _('Error updating prices for') . ' ' . $myrow['stockid'] . ' ' . _('because');
$result = DB_query($UpdateSQL, $ErrMsg);
}
$sql = "INSERT INTO prices (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\ttypeabbrev,\n\t\t\t\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\t\t\t\t\t\t\tenddate,\n\t\t\t\t\t\t\t\t\t\t\t\tprice)\n\t\t\t\t\t\t\t\tVALUES ('" . $myrow['stockid'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['PriceList'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['CurrCode'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_POST['PriceStartDate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $SQLEndDate . "',\n\t\t\t\t\t\t\t\t \t\t'" . filter_number_format($RoundedPrice) . "')";
$ErrMsg = _('Error inserting new price for') . ' ' . $myrow['stockid'] . ' ' . _('because');
$result = DB_query($sql, $ErrMsg);
prnMsg(_('Inserting new price for') . ' ' . $myrow['stockid'] . ' ' . _('to') . ' ' . $RoundedPrice, 'info');
}
// end if cost > 0
}
//end while loop around items in the category
}
}
include 'includes/footer.inc';
$_POST['OldLabourCost'] = 0;
$_POST['OldOverheadCost'] = 0;
$_POST['LabourCost'] = 0;
$_POST['OverheadCost'] = 0;
}
DB_free_result($OldResult);
$OldCost = $_POST['OldMaterialCost'] + $_POST['OldLabourCost'] + $_POST['OldOverheadCost'];
$NewCost = filter_number_format($_POST['MaterialCost']) + filter_number_format($_POST['LabourCost']) + filter_number_format($_POST['OverheadCost']);
$result = DB_query("SELECT * FROM stockmaster WHERE stockid='" . $StockID . "'");
$myrow = DB_fetch_row($result);
if (DB_num_rows($result) == 0) {
prnMsg(_('The entered item code does not exist'), 'error', _('Non-existent Item'));
} elseif ($OldCost != $NewCost) {
$Result = DB_Txn_Begin();
ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $_POST['QOH']);
$SQL = "UPDATE stockmaster SET\tmaterialcost='" . filter_number_format($_POST['MaterialCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlabourcost='" . filter_number_format($_POST['LabourCost']) . "',\n\t\t\t\t\t\t\t\t\t\toverheadcost='" . filter_number_format($_POST['OverheadCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlastcost='" . $OldCost . "',\n\t\t\t\t\t\t\t\t\t\tlastcostupdate ='" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\t\tWHERE stockid='" . $StockID . "'";
$ErrMsg = _('The cost details for the stock item could not be updated because');
$DbgMsg = _('The SQL that failed was');
$Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
$Result = DB_Txn_Commit();
UpdateCost($db, $StockID);
//Update any affected BOMs
}
}
$ErrMsg = _('The cost details for the stock item could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$result = DB_query("SELECT description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype,\n\t\t\t\t\t\t\tlastcostupdate,\n\t\t\t\t\t\t\tsum(quantity) as totalqoh\n\t\t\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\t\t\t\tINNER JOIN stockcategory\n\t\t\t\t\t\t\tON stockmaster.categoryid = stockcategory.categoryid\n\t\t\t\t\t\tWHERE stockmaster.stockid='" . $StockID . "'\n\t\t\t\t\t\tGROUP BY description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype", $ErrMsg, $DbgMsg);
$myrow = DB_fetch_array($result);
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
<div>
<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
if (isset($_POST['QualityText' . $i])) {
$QualityText = $_POST['QualityText' . $i];
} else {
$QualityText = '';
}
if ($AlreadyExistsRow[0] > 0) {
$SQL = "UPDATE stockserialitems SET quantity = quantity + " . filter_number_format($_POST['Qty' . $i]) . ",\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tqualitytext = '" . $QualityText . "'\n\t\t\t\t\t\t\t\t\t\tWHERE stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tAND loccode = '" . $_POST['IntoLocation'] . "'\n\t\t\t\t\t\t\t\t\t\tAND serialno = '" . $_POST['BatchRef' . $i] . "'";
} else {
$SQL = "INSERT INTO stockserialitems (stockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tserialno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tqualitytext)\n\t\t\t\t\t\t\t\t\t\tVALUES ('" . $_POST['StockID'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['IntoLocation'] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['BatchRef' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['Qty' . $i]) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['QualityText'] . "')";
}
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock item record could not be inserted because');
$DbgMsg = _('The following SQL to insert the serial stock item records was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/** end of handle stockserialitems records */
/** now insert the serial stock movement **/
$SQL = "INSERT INTO stockserialmoves (stockmoveno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tserialno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tmoveqty)\n\t\t\t\t\t\t\t\t\tVALUES ('" . $StkMoveNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['StockID'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['BatchRef' . $i] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['Qty' . $i]) . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The serial stock movement record could not be inserted because');
$DbgMsg = _('The following SQL to insert the serial stock movement records was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
if ($_SESSION['DefineControlledOnWOEntry'] == 1) {
//check how many of the batch/bundle/lot has been received
$SQL = "SELECT sum(moveqty) FROM stockserialmoves\n\t\t\t\t\t\t\t\t\t\tINNER JOIN stockmoves ON stockserialmoves.stockmoveno=stockmoves.stkmoveno\n\t\t\t\t\t\t\t\t\t\tWHERE stockmoves.type=26\n\t\t\t\t\t\t\t\t\t\tAND stockserialmoves.stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tAND stockserialmoves.serialno='" . $_POST['BatchRef' . $i] . "'";
$BatchTotQtyResult = DB_query($SQL, $db);
$BatchTotQtyRow = DB_fetch_row($BatchTotQtyResult);
if ($BatchTotQtyRow[0] >= $_POST['QtyReqd' . $i]) {
//need to delete the item from woserialnos
$SQL = "DELETE FROM\twoserialnos\n\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $_POST['WO'] . "'\n\t\t\t\t\t\t\t\t\t\tAND stockid='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\t\tAND serialno='" . $_POST['BatchRef' . $i] . "'";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The predefined batch/lot/bundle record could not be deleted because');
$DbgMsg = _('The following SQL to delete the predefined work order batch/bundle/lot record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
$_SESSION['Request']->LineItems[$_POST['LineNumber']]->Quantity = $_POST['Quantity'];
}
if (isset($_GET['Delete'])) {
unset($_SESSION['Request']->LineItems[$_GET['Delete']]);
echo '<br />';
prnMsg(_('The line was successfully deleted'), 'success');
echo '<br />';
}
foreach ($_POST as $key => $value) {
if (mb_strstr($key, 'StockID')) {
$Index = mb_substr($key, 7);
if (filter_number_format($_POST['Quantity' . $Index]) > 0) {
$StockID = $value;
$ItemDescription = $_POST['ItemDescription' . $Index];
$DecimalPlaces = $_POST['DecimalPlaces' . $Index];
$NewItem_array[$StockID] = filter_number_format($_POST['Quantity' . $Index]);
$_POST['Units' . $StockID] = $_POST['Units' . $Index];
$_SESSION['Request']->AddLine($StockID, $ItemDescription, $NewItem_array[$StockID], $_POST['Units' . $StockID], $DecimalPlaces);
}
}
}
if (isset($_POST['Submit'])) {
DB_Txn_Begin($db);
$InputError = 0;
if ($_SESSION['Request']->Department == '') {
prnMsg(_('You must select a Department for the request'), 'error');
$InputError = 1;
}
if ($_SESSION['Request']->Location == '') {
prnMsg(_('You must select a Location to request the items from'), 'error');
$InputError = 1;
echo '<div class="page_help_text"><b>' . _('Geocode Notice') . ':</b> ' . _('Address') . ': ' . $address . ' ' . _('failed to geocode');
echo _('Received status') . ' ' . $Status . '</div>';
}
}
}
if (isset($SelectedBranch) and $InputError != 1) {
/*SelectedBranch could also exist if submit had not been clicked this code would not run in this case cos submit is false of course see the delete code below*/
$SQL = "UPDATE custbranch SET brname = '" . $_POST['BrName'] . "',\n\t\t\t\t\t\tbraddress1 = '" . $_POST['BrAddress1'] . "',\n\t\t\t\t\t\tbraddress2 = '" . $_POST['BrAddress2'] . "',\n\t\t\t\t\t\tbraddress3 = '" . $_POST['BrAddress3'] . "',\n\t\t\t\t\t\tbraddress4 = '" . $_POST['BrAddress4'] . "',\n\t\t\t\t\t\tbraddress5 = '" . $_POST['BrAddress5'] . "',\n\t\t\t\t\t\tbraddress6 = '" . $_POST['BrAddress6'] . "',\n\t\t\t\t\t\tlat = '" . $Latitude . "',\n\t\t\t\t\t\tlng = '" . $Longitude . "',\n\t\t\t\t\t\tspecialinstructions = '" . $_POST['SpecialInstructions'] . "',\n\t\t\t\t\t\tphoneno='" . $_POST['PhoneNo'] . "',\n\t\t\t\t\t\tfaxno='" . $_POST['FaxNo'] . "',\n\t\t\t\t\t\tfwddate= '" . $_POST['FwdDate'] . "',\n\t\t\t\t\t\tcontactname='" . $_POST['ContactName'] . "',\n\t\t\t\t\t\tsalesman= '" . $_POST['Salesman'] . "',\n\t\t\t\t\t\tarea='" . $_POST['Area'] . "',\n\t\t\t\t\t\testdeliverydays ='" . filter_number_format($_POST['EstDeliveryDays']) . "',\n\t\t\t\t\t\temail='" . $_POST['Email'] . "',\n\t\t\t\t\t\ttaxgroupid='" . $_POST['TaxGroup'] . "',\n\t\t\t\t\t\tdefaultlocation='" . $_POST['DefaultLocation'] . "',\n\t\t\t\t\t\tbrpostaddr1 = '" . $_POST['BrPostAddr1'] . "',\n\t\t\t\t\t\tbrpostaddr2 = '" . $_POST['BrPostAddr2'] . "',\n\t\t\t\t\t\tbrpostaddr3 = '" . $_POST['BrPostAddr3'] . "',\n\t\t\t\t\t\tbrpostaddr4 = '" . $_POST['BrPostAddr4'] . "',\n\t\t\t\t\t\tbrpostaddr5 = '" . $_POST['BrPostAddr5'] . "',\n\t\t\t\t\t\tdisabletrans='" . $_POST['DisableTrans'] . "',\n\t\t\t\t\t\tdefaultshipvia='" . $_POST['DefaultShipVia'] . "',\n\t\t\t\t\t\tcustbranchcode='" . $_POST['CustBranchCode'] . "',\n\t\t\t\t\t\tdeliverblind='" . $_POST['DeliverBlind'] . "'\n\t\t\t\t\tWHERE branchcode = '" . $SelectedBranch . "' AND debtorno='" . $DebtorNo . "'";
if ($_SESSION['SalesmanLogin'] != '') {
$SQL .= " AND custbranch.salesman='" . $_SESSION['SalesmanLogin'] . "'";
}
$msg = $_POST['BrName'] . ' ' . _('branch has been updated.');
} else {
if ($InputError != 1) {
/*Selected branch is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new Customer Branches form */
$SQL = "INSERT INTO custbranch (branchcode,\n\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\tbrname,\n\t\t\t\t\t\tbraddress1,\n\t\t\t\t\t\tbraddress2,\n\t\t\t\t\t\tbraddress3,\n\t\t\t\t\t\tbraddress4,\n\t\t\t\t\t\tbraddress5,\n\t\t\t\t\t\tbraddress6,\n\t\t\t\t\t\tlat,\n\t\t\t\t\t\tlng,\n \t\t\t\t\t\tspecialinstructions,\n\t\t\t\t\t\testdeliverydays,\n\t\t\t\t\t\tfwddate,\n\t\t\t\t\t\tsalesman,\n\t\t\t\t\t\tphoneno,\n\t\t\t\t\t\tfaxno,\n\t\t\t\t\t\tcontactname,\n\t\t\t\t\t\tarea,\n\t\t\t\t\t\temail,\n\t\t\t\t\t\ttaxgroupid,\n\t\t\t\t\t\tdefaultlocation,\n\t\t\t\t\t\tbrpostaddr1,\n\t\t\t\t\t\tbrpostaddr2,\n\t\t\t\t\t\tbrpostaddr3,\n\t\t\t\t\t\tbrpostaddr4,\n\t\t\t\t\t\tbrpostaddr5,\n\t\t\t\t\t\tdisabletrans,\n\t\t\t\t\t\tdefaultshipvia,\n\t\t\t\t\t\tcustbranchcode,\n\t\t\t\t\t\tdeliverblind)\n\t\t\t\tVALUES ('" . $_POST['BranchCode'] . "',\n\t\t\t\t\t'" . $DebtorNo . "',\n\t\t\t\t\t'" . $_POST['BrName'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress1'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress2'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress3'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress4'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress5'] . "',\n\t\t\t\t\t'" . $_POST['BrAddress6'] . "',\n\t\t\t\t\t'" . $Latitude . "',\n\t\t\t\t\t'" . $Longitude . "',\n\t\t\t\t\t'" . $_POST['SpecialInstructions'] . "',\n\t\t\t\t\t'" . filter_number_format($_POST['EstDeliveryDays']) . "',\n\t\t\t\t\t'" . $_POST['FwdDate'] . "',\n\t\t\t\t\t'" . $_POST['Salesman'] . "',\n\t\t\t\t\t'" . $_POST['PhoneNo'] . "',\n\t\t\t\t\t'" . $_POST['FaxNo'] . "',\n\t\t\t\t\t'" . $_POST['ContactName'] . "',\n\t\t\t\t\t'" . $_POST['Area'] . "',\n\t\t\t\t\t'" . $_POST['Email'] . "',\n\t\t\t\t\t'" . $_POST['TaxGroup'] . "',\n\t\t\t\t\t'" . $_POST['DefaultLocation'] . "',\n\t\t\t\t\t'" . $_POST['BrPostAddr1'] . "',\n\t\t\t\t\t'" . $_POST['BrPostAddr2'] . "',\n\t\t\t\t\t'" . $_POST['BrPostAddr3'] . "',\n\t\t\t\t\t'" . $_POST['BrPostAddr4'] . "',\n\t\t\t\t\t'" . $_POST['BrPostAddr5'] . "',\n\t\t\t\t\t'" . $_POST['DisableTrans'] . "',\n\t\t\t\t\t'" . $_POST['DefaultShipVia'] . "',\n\t\t\t\t\t'" . $_POST['CustBranchCode'] . "',\n\t\t\t\t\t'" . $_POST['DeliverBlind'] . "')";
}
}
echo '<br />';
$msg = _('Customer branch') . '<b> ' . $_POST['BranchCode'] . ': ' . $_POST['BrName'] . ' </b>' . _('has been added, add another branch, or return to the') . ' <a href="index.php">' . _('Main Menu') . '</a>';
//run the SQL from either of the above possibilites
$ErrMsg = _('The branch record could not be inserted or updated because');
if ($InputError == 0) {
$result = DB_query($SQL, $ErrMsg);
}
if (DB_error_no() == 0 and $InputError == 0) {
prnMsg($msg, 'success');
unset($_POST['BranchCode']);
unset($_POST['BrName']);
unset($_POST['BrAddress1']);
unset($_POST['BrAddress2']);
unset($_POST['GroupByData3']);
unset($_POST['GroupByData4']);
unset($_POST['NewPageAfter1']);
unset($_POST['NewPageAfter2']);
unset($_POST['NewPageAfter3']);
unset($_POST['Lower1']);
unset($_POST['Upper1']);
unset($_POST['Lower2']);
unset($_POST['Upper2']);
unset($_POST['Lower3']);
unset($_POST['Upper3']);
unset($_POST['Lower4']);
unset($_POST['Upper4']);
} elseif ($InputError != 1) {
/*SelectedReport is null cos no item selected on first time round so must be adding a new report */
$sql = "INSERT INTO reportheaders (\n\t\t\t\t\t\treportheading,\n\t\t\t\t\t\tgroupbydata1,\n\t\t\t\t\t\tgroupbydata2,\n\t\t\t\t\t\tgroupbydata3,\n\t\t\t\t\t\tgroupbydata4,\n\t\t\t\t\t\tnewpageafter1,\n\t\t\t\t\t\tnewpageafter2,\n\t\t\t\t\t\tnewpageafter3,\n\t\t\t\t\t\tlower1,\n\t\t\t\t\t\tupper1,\n\t\t\t\t\t\tlower2,\n\t\t\t\t\t\tupper2,\n\t\t\t\t\t\tlower3,\n\t\t\t\t\t\tupper3,\n\t\t\t\t\t\tlower4,\n\t\t\t\t\t\tupper4 )\n\t\t\t\tVALUES (\n\t\t\t\t\t'" . $_POST['ReportHeading'] . "',\n\t\t\t\t\t'" . $_POST['GroupByData1'] . "',\n\t\t\t\t\t'" . $_POST['GroupByData2'] . "',\n\t\t\t\t\t'" . $_POST['GroupByData3'] . "',\n\t\t\t\t\t'" . $_POST['GroupByData4'] . "',\n\t\t\t\t\t'" . $_POST['NewPageAfter1'] . "',\n\t\t\t\t\t'" . $_POST['NewPageAfter2'] . "',\n\t\t\t\t\t'" . $_POST['NewPageAfter3'] . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Lower1']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Upper1']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Lower2']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Upper2']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Lower3']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Upper3']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Lower4']) . "',\n\t\t\t\t\t'" . filter_number_format($_POST['Upper4']) . "'\n\t\t\t\t\t)";
$ErrMsg = _('The report could not be added because');
$DbgMsg = _('The SQL used to add the report header was');
$result = DB_query($sql, $ErrMsg, $DbgMsg);
prnMsg(_('The') . ' ' . $_POST['ReportHeading'] . ' ' . _('report has been added to the database'), 'success', 'Report Added');
unset($SelectedReport);
unset($_POST['ReportHeading']);
unset($_POST['GroupByData1']);
unset($_POST['GroupByData2']);
unset($_POST['GroupByData3']);
unset($_POST['GroupByData4']);
unset($_POST['NewPageAfter1']);
unset($_POST['NewPageAfter2']);
unset($_POST['NewPageAfter3']);
unset($_POST['Lower1']);
unset($_POST['Upper1']);
$result = DB_query("DROP TABLE IF EXISTS passbom2", $db);
$sql = "CREATE TEMPORARY TABLE passbom (\n\t\t\t\tpart char(20),\n\t\t\t\textendedqpa double,\n\t\t\t\tsortpart text) DEFAULT CHARSET=utf8";
$ErrMsg = _('The SQL to create passbom failed with the message');
$result = DB_query($sql, $db, $ErrMsg);
$sql = "CREATE TEMPORARY TABLE tempbom (\n\t\t\t\tparent char(20),\n\t\t\t\tcomponent char(20),\n\t\t\t\tsortpart text,\n\t\t\t\tlevel int,\n\t\t\t\tworkcentreadded char(5),\n\t\t\t\tloccode char(5),\n\t\t\t\teffectiveafter date,\n\t\t\t\teffectiveto date,\n\t\t\t\tquantity double) DEFAULT CHARSET=utf8";
$result = DB_query($sql, $db, _('Create of tempbom failed because'));
// First, find first level of components below requested assembly
// Put those first level parts in passbom, use COMPONENT in passbom
// to link to PARENT in bom to find next lower level and accumulate
// those parts into tempbom
// This finds the top level
$sql = "INSERT INTO passbom (part, extendedqpa, sortpart)\n\t\t\t SELECT bom.component AS part,\n\t\t\t\t\t (" . filter_number_format($_POST['Quantity']) . " * bom.quantity) as extendedqpa,\n\t\t\t\t\t CONCAT(bom.parent,bom.component) AS sortpart\n\t\t\t\t\t FROM bom\n\t\t\t WHERE bom.parent ='" . $_POST['Part'] . "'\n\t\t\t AND bom.effectiveto >= '" . date('Y-m-d') . "'\n\t\t\t AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
$result = DB_query($sql, $db);
$LevelCounter = 2;
// $LevelCounter is the level counter
$sql = "INSERT INTO tempbom (\n\t\t\t\tparent,\n\t\t\t\tcomponent,\n\t\t\t\tsortpart,\n\t\t\t\tlevel,\n\t\t\t\tworkcentreadded,\n\t\t\t\tloccode,\n\t\t\t\teffectiveafter,\n\t\t\t\teffectiveto,\n\t\t\t\tquantity)\n\t\t\tSELECT bom.parent,\n\t\t\t\t\t bom.component,\n\t\t\t\t\t CONCAT(bom.parent,bom.component) AS sortpart," . $LevelCounter . " as level,\n\t\t\t\t\t bom.workcentreadded,\n\t\t\t\t\t bom.loccode,\n\t\t\t\t\t bom.effectiveafter,\n\t\t\t\t\t bom.effectiveto,\n\t\t\t\t\t (" . filter_number_format($_POST['Quantity']) . " * bom.quantity) as extendedqpa\n\t\t\tFROM bom\n\t\t\tWHERE bom.parent ='" . $_POST['Part'] . "'\n\t\t\tAND bom.effectiveto >= '" . date('Y-m-d') . "'\n\t\t\tAND bom.effectiveafter <= '" . date('Y-m-d') . "'";
$result = DB_query($sql, $db);
//echo "<br />sql is $sql<br />";
// This while routine finds the other levels as long as $ComponentCounter - the
// component counter finds there are more components that are used as
// assemblies at lower levels
$ComponentCounter = 1;
while ($ComponentCounter > 0) {
$LevelCounter++;
$sql = "INSERT INTO tempbom (\n\t\t\t\tparent,\n\t\t\t\tcomponent,\n\t\t\t\tsortpart,\n\t\t\t\tlevel,\n\t\t\t\tworkcentreadded,\n\t\t\t\tloccode,\n\t\t\t\teffectiveafter,\n\t\t\t\teffectiveto,\n\t\t\t\tquantity)\n\t\t\t SELECT bom.parent,\n\t\t\t\t\t bom.component,\n\t\t\t\t\t CONCAT(passbom.sortpart,bom.component) AS sortpart,\n\t\t\t\t\t " . $LevelCounter . " as level,\n\t\t\t\t\t bom.workcentreadded,\n\t\t\t\t\t bom.loccode,\n\t\t\t\t\t bom.effectiveafter,\n\t\t\t\t\t bom.effectiveto,\n\t\t\t\t\t (bom.quantity * passbom.extendedqpa)\n\t\t\t FROM bom,passbom\n\t\t\t WHERE bom.parent = passbom.part\n\t\t\t AND bom.effectiveto >= '" . date('Y-m-d') . "'\n\t\t\t AND bom.effectiveafter <= '" . date('Y-m-d') . "'";
$result = DB_query($sql, $db);
$result = DB_query("DROP TABLE IF EXISTS passbom2", $db);
$result = DB_query("ALTER TABLE passbom RENAME AS passbom2", $db);
$result = DB_query("DROP TABLE IF EXISTS passbom", $db);
$sql = "CREATE TEMPORARY TABLE passbom (part char(20),\n\t\t\t\t\t\t\t\t\t\t\t\textendedqpa decimal(10,3),\n\t\t\t\t\t\t\t\t\t\t\t\tsortpart text) DEFAULT CHARSET=utf8";
$result = DB_query($sql, $db);
if (!is_numeric(filter_number_format($_POST['DiscountRate']))) {
prnMsg(_('The discount rate must be entered as a positive number'), 'warn');
$InputError = 1;
$Errors[$i] = 'DiscountRate';
$i++;
}
if (filter_number_format($_POST['DiscountRate']) <= 0 or filter_number_format($_POST['DiscountRate']) >= 70) {
prnMsg(_('The discount rate applicable for this record is either less than 0% or greater than 70%') . '. ' . _('Numbers between 1 and 69 are expected'), 'warn');
$InputError = 1;
$Errors[$i] = 'DiscountRate';
$i++;
}
/* actions to take once the user has clicked the submit button
ie the page has called itself with some user input */
if ($InputError != 1) {
$sql = "INSERT INTO discountmatrix (salestype,\n\t\t\t\t\t\t\tdiscountcategory,\n\t\t\t\t\t\t\tquantitybreak,\n\t\t\t\t\t\t\tdiscountrate)\n\t\t\t\t\tVALUES('" . $_POST['SalesType'] . "',\n\t\t\t\t\t\t'" . $_POST['DiscountCategory'] . "',\n\t\t\t\t\t\t'" . filter_number_format($_POST['QuantityBreak']) . "',\n\t\t\t\t\t\t'" . filter_number_format($_POST['DiscountRate']) / 100 . "')";
$result = DB_query($sql, $db);
prnMsg(_('The discount matrix record has been added'), 'success');
echo '<br />';
unset($_POST['DiscountCategory']);
unset($_POST['SalesType']);
unset($_POST['QuantityBreak']);
unset($_POST['DiscountRate']);
}
} elseif (isset($_GET['Delete']) and $_GET['Delete'] == 'yes') {
/*the link to delete a selected record was clicked instead of the submit button */
$sql = "DELETE FROM discountmatrix\n\t\tWHERE discountcategory='" . $_GET['DiscountCategory'] . "'\n\t\tAND salestype='" . $_GET['SalesType'] . "'\n\t\tAND quantitybreak='" . $_GET['QuantityBreak'] . "'";
$result = DB_query($sql, $db);
prnMsg(_('The discount matrix record has been deleted'), 'success');
echo '<br />';
}
if (isset($_POST['Update']) and $_POST['RowCounter'] > 1) {
for ($Counter = 1; $Counter <= $_POST['RowCounter']; $Counter++) {
if (isset($_POST['Clear_' . $Counter]) and $_POST['Clear_' . $Counter] == True) {
/*Get amount to be cleared */
$sql = "SELECT amount,\n\t\t\t\t\t\t\texrate\n\t\t\t\t\t\tFROM banktrans\n\t\t\t\t\t\tWHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'";
$ErrMsg = _('Could not retrieve transaction information');
$result = DB_query($sql, $db, $ErrMsg);
$myrow = DB_fetch_array($result);
$AmountCleared = round($myrow[0] / $myrow[1], 2);
/*Update the banktrans recoord to match it off */
$sql = "UPDATE banktrans SET amountcleared= " . $AmountCleared . "\n\t\t\t\t\t\t\t\t\tWHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'";
$ErrMsg = _('Could not match off this payment because');
$result = DB_query($sql, $db, $ErrMsg);
} elseif (isset($_POST['AmtClear_' . $Counter]) and filter_number_format($_POST['AmtClear_' . $Counter]) < 0 and $Type == 'Payments' or $Type == 'Receipts' and isset($_POST['AmtClear_' . $Counter]) and filter_number_format($_POST['AmtClear_' . $Counter]) > 0) {
/*if the amount entered was numeric and negative for a payment or positive for a receipt */
$sql = "UPDATE banktrans SET amountcleared=" . filter_number_format($_POST['AmtClear_' . $Counter]) . "\n\t\t\t\t\t WHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'";
$ErrMsg = _('Could not update the amount matched off this bank transaction because');
$result = DB_query($sql, $db, $ErrMsg);
} elseif (isset($_POST['Unclear_' . $Counter]) and $_POST['Unclear_' . $Counter] == True) {
$sql = "UPDATE banktrans SET amountcleared = 0\n\t\t\t\t\t WHERE banktransid='" . $_POST['BankTrans_' . $Counter] . "'";
$ErrMsg = _('Could not unclear this bank transaction because');
$result = DB_query($sql, $db, $ErrMsg);
}
}
/*Show the updated position with the same criteria as previously entered*/
$_POST['ShowTransactions'] = True;
}
echo '<div class="page_help_text">' . _('Use this screen to match webERP Receipts and Payments to your Bank Statement. Check your bank statement and click the check-box when you find the matching transaction.') . '</div><br />';
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">';
echo '<div>';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
// until the remainder number is used up. Then create an mrpdemands records for everything
// in the array
if (filter_number_format($_POST['Multiplier']) < 1) {
$Multiplier = 1;
} else {
$Multiplier = filter_number_format($_POST['Multiplier']);
}
if ($_POST['ExcludeQuantity'] < 1) {
$ExcludeQty = 1;
} else {
$ExcludeQty = filter_number_format($_POST['ExcludeQuantity']);
}
if ($_POST['ExcludeAmount'] < 1) {
$ExcludeAmount = 0;
} else {
$ExcludeAmount = filter_number_format($_POST['ExcludeAmount']);
}
// Create array of dates based on DistDate and adding either weeks or months
$FormatedDistdate = FormatDateForSQL($_POST['DistDate']);
if (mb_strpos($FormatedDistdate, "/")) {
list($yyyy, $mm, $dd) = explode("/", $FormatedDistdate);
} else {
if (mb_strpos($FormatedDistdate, "-")) {
list($yyyy, $mm, $dd) = explode("-", $FormatedDistdate);
} else {
if (mb_strpos($FormatedDistdate, ".")) {
list($yyyy, $mm, $dd) = explode(".", $FormatedDistdate);
}
}
}
$datearray[0] = $FormatedDistdate;
$j++;
if ($j == 12) {
$j = 1;
echo $TableHeader;
}
//end of page full new headings if
}
//end of while loop
echo '</table>';
} else {
$FromDate = FormatDateForSQL($_POST['FromDate']);
$ToDate = FormatDateForSQL($_POST['ToDate']);
if (isset($LotNumber) and $LotNumber != '') {
$SQL = "SELECT sampleid,\n\t\t\t\t\t\t\tprodspeckey,\n\t\t\t\t\t\t\tdescription,\n\t\t\t\t\t\t\tlotkey,\n\t\t\t\t\t\t\tidentifier,\n\t\t\t\t\t\t\tcreatedby,\n\t\t\t\t\t\t\tsampledate,\n\t\t\t\t\t\t\tcert\n\t\t\t\t\t\tFROM qasamples\n\t\t\t\t\t\tLEFT OUTER JOIN stockmaster on stockmaster.stockid=qasamples.prodspeckey\n\t\t\t\t\t\tWHERE lotkey='" . filter_number_format($LotNumber) . "'";
} elseif (isset($SampleID) and $SampleID != '') {
$SQL = "SELECT sampleid,\n\t\t\t\t\t\t\tprodspeckey,\n\t\t\t\t\t\t\tdescription,\n\t\t\t\t\t\t\tlotkey,\n\t\t\t\t\t\t\tidentifier,\n\t\t\t\t\t\t\tcreatedby,\n\t\t\t\t\t\t\tsampledate,\n\t\t\t\t\t\t\tcert\n\t\t\t\t\t\tFROM qasamples\n\t\t\t\t\t\tLEFT OUTER JOIN stockmaster on stockmaster.stockid=qasamples.prodspeckey\n\t\t\t\t\t\tWHERE sampleid='" . filter_number_format($SampleID) . "'";
} else {
if (isset($SelectedStockItem)) {
$SQL = "SELECT sampleid,\n\t\t\t\t\t\t\tprodspeckey,\n\t\t\t\t\t\t\tdescription,\n\t\t\t\t\t\t\tlotkey,\n\t\t\t\t\t\t\tidentifier,\n\t\t\t\t\t\t\tcreatedby,\n\t\t\t\t\t\t\tsampledate,\n\t\t\t\t\t\t\tcert\n\t\t\t\t\t\tFROM qasamples\n\t\t\t\t\t\tINNER JOIN stockmaster on stockmaster.stockid=qasamples.prodspeckey\n\t\t\t\t\t\tWHERE stockid='" . $SelectedStockItem . "'\n\t\t\t\t\t\tAND sampledate>='" . $FromDate . "'\n\t\t\t\t\t\tAND sampledate <='" . $ToDate . "'";
} else {
$SQL = "SELECT sampleid,\n\t\t\t\t\t\t\tprodspeckey,\n\t\t\t\t\t\t\tdescription,\n\t\t\t\t\t\t\tlotkey,\n\t\t\t\t\t\t\tidentifier,\n\t\t\t\t\t\t\tcreatedby,\n\t\t\t\t\t\t\tsampledate,\n\t\t\t\t\t\t\tcomments,\n\t\t\t\t\t\t\tcert\n\t\t\t\t\t\tFROM qasamples\n\t\t\t\t\t\tLEFT OUTER JOIN stockmaster on stockmaster.stockid=qasamples.prodspeckey\n\t\t\t\t\t\tWHERE sampledate>='" . $FromDate . "'\n\t\t\t\t\t\tAND sampledate <='" . $ToDate . "'";
}
//no stock item selected
}
//end no sample id selected
$ErrMsg = _('No QA samples were returned by the SQL because');
$SampleResult = DB_query($SQL, $ErrMsg);
if (DB_num_rows($SampleResult) > 0) {
echo '<table cellpadding="2" width="90%" class="selection">';
$TableHeader = '<tr>
<th class="ascending">' . _('Enter Results') . '</th>
$myrow = DB_fetch_row($result);
if ($myrow[0] != 0 and !isset($_POST['OldTypeAbbrev']) and !isset($_POST['OldCurrAbrev'])) {
prnMsg(_('This price has already been entered. To change it you should edit it'), 'warn');
$InputError = 1;
}
if (isset($_POST['OldTypeAbbrev']) and isset($_POST['OldCurrAbrev']) and mb_strlen($StockID) > 1 and $InputError != 1) {
/* Update existing prices */
$sql = "UPDATE pricematrix SET \r\n\t\t\t\t\tsalestype='" . $_POST['SalesType'] . "',\r\n\t\t\t\t\tcurrabrev='" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\tprice='" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\tstartdate='" . $SQLStartDate . "',\r\n\t\t\t\t\tenddate='" . $SQLEndDate . "',\r\n\t\t\t\t\tquantitybreak='" . filter_number_format($_POST['QuantityBreak']) . "'\r\n\t\t\t\tWHERE stockid='" . $StockID . "'\r\n\t\t\t\tAND startdate='" . $_POST['OldStartDate'] . "'\r\n\t\t\t\tAND enddate='" . $_POST['OldEndDate'] . "'\r\n\t\t\t\tAND salestype='" . $_POST['OldTypeAbbrev'] . "'\r\n\t\t\t\tAND currabrev='" . $_POST['OldCurrAbrev'] . "'\r\n\t\t\t\tAND quantitybreak='" . filter_number_format($_POST['OldQuantityBreak']) . "'";
$ErrMsg = _('Could not be update the existing prices');
$result = DB_query($sql, $ErrMsg);
ReSequenceEffectiveDates($StockID, $_POST['SalesType'], $_POST['CurrAbrev'], $_POST['QuantityBreak'], $db);
prnMsg(_('The price has been updated'), 'success');
} elseif ($InputError != 1) {
/* actions to take once the user has clicked the submit button
ie the page has called itself with some user input */
$sql = "INSERT INTO pricematrix (salestype,\r\n\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\tquantitybreak,\r\n\t\t\t\t\t\t\tprice,\r\n\t\t\t\t\t\t\tcurrabrev,\r\n\t\t\t\t\t\t\tstartdate,\r\n\t\t\t\t\t\t\tenddate)\r\n\t\t\t\t\tVALUES('" . $_POST['SalesType'] . "',\r\n\t\t\t\t\t\t'" . $_POST['StockID'] . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['QuantityBreak']) . "',\r\n\t\t\t\t\t\t'" . filter_number_format($_POST['Price']) . "',\r\n\t\t\t\t\t\t'" . $_POST['CurrAbrev'] . "',\r\n\t\t\t\t\t\t'" . $SQLStartDate . "',\r\n\t\t\t\t\t\t'" . $SQLEndDate . "')";
$ErrMsg = _('Failed to insert price data');
$result = DB_query($sql, $ErrMsg);
prnMsg(_('The price matrix record has been added'), 'success');
echo '<br />';
unset($_POST['StockID']);
unset($_POST['SalesType']);
unset($_POST['QuantityBreak']);
unset($_POST['Price']);
unset($_POST['CurrAbrev']);
unset($_POST['StartDate']);
unset($_POST['EndDate']);
unset($SQLEndDate);
unset($SQLStartDate);
}
} elseif (isset($_GET['Delete']) and $_GET['Delete'] == 'yes') {
$_SESSION['Adjustment' . $identifier]->StockLocation = key(reset($LocationList));
} else {
$_SESSION['Adjustment' . $identifier]->StockLocation = $_SESSION['UserStockLocation'];
}
}
}
if (isset($_POST['Quantity'])) {
if ($_POST['Quantity'] == '' or !is_numeric(filter_number_format($_POST['Quantity']))) {
$_POST['Quantity'] = 0;
}
} else {
$_POST['Quantity'] = 0;
}
if ($_POST['Quantity'] != 0) {
//To prevent from serilised quantity changing to zero
$_SESSION['Adjustment' . $identifier]->Quantity = filter_number_format($_POST['Quantity']);
if (count($_SESSION['Adjustment' . $identifier]->SerialItems) == 0 and $_SESSION['Adjustment' . $identifier]->Controlled == 1) {
/* There is no quantity available for controlled items */
$_SESSION['Adjustment' . $identifier]->Quantity = 0;
}
}
if (isset($_GET['OldIdentifier'])) {
$_SESSION['Adjustment' . $identifier]->StockLocation = $_SESSION['Adjustment' . $_GET['OldIdentifier']]->StockLocation;
}
echo '<p class="page_title_text"><img src="' . $RootPath . '/css/' . $Theme . '/images/supplier.png" title="' . _('Inventory Adjustment') . '" alt="" />' . ' ' . _('Inventory Adjustment') . '</p>';
if (isset($_POST['CheckCode'])) {
echo '<p class="page_title_text"><img src="' . $RootPath . '/css/' . $Theme . '/images/magnifier.png" title="' . _('Dispatch') . '" alt="" />' . ' ' . _('Select Item to Adjust') . '</p>';
if (mb_strlen($_POST['StockText']) > 0) {
$sql = "SELECT stockid,\n\t\t\t\t\tdescription\n\t\t\t\tFROM stockmaster\n\t\t\t\tWHERE description " . LIKE . " '%" . $_POST['StockText'] . "%'";
} else {
$sql = "SELECT stockid,\n\t\t\t\t\tdescription\n\t\t\t\tFROM stockmaster\n\t\t\t\tWHERE stockid " . LIKE . " '%" . $_POST['StockCode'] . "%'";
