function Save()
{
global $db;
if ($this->OrderNumber == 0) {
$this->OrderNumber = GetNextTransNo(40, $db);
$sql = "INSERT INTO workorders (wo,\n\t\t\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\t\t\trequiredby,\n\t\t\t\t\t\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\t\t\t\t\t\tcostissued)\n\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t'" . $this->OrderNumber . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $this->LocationCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($this->RequiredBy) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($this->StartDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $this->CostIssued . "'\n\t\t\t\t\t\t\t\t\t\t)";
} else {
$sql = "UPDATE workorders SET loccode='" . $this->LocationCode . "',\n\t\t\t\t\t\t\t\t\t\t\trequiredby='" . FormatDateForSQL($this->RequiredBy) . "',\n\t\t\t\t\t\t\t\t\t\t\tstartdate='" . FormatDateForSQL($this->StartDate) . "',\n\t\t\t\t\t\t\t\t\t\t\tcostissued='" . $this->CostIssued . "'\n\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $this->OrderNumber . "'";
}
$UpdateWOResult = DB_query($sql, $db);
foreach ($this->Items as $i => $Item) {
$Item->Save($this->OrderNumber);
}
}
function Save($db, $Update = '')
{
if ($Update == '') {
foreach ($this->LineItems as $LineItems) {
if ($LineItems->Deleted == False) {
$sql = "INSERT INTO offers (\tsupplierid,\n\t\t\t\t\t\t\t\t\t\t\t\ttenderid,\n\t\t\t\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\tuom,\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\texpirydate,\n\t\t\t\t\t\t\t\t\t\t\t\tcurrcode)\n\t\t\t\t\t\tVALUES ('" . $this->SupplierID . "',\n\t\t\t\t\t\t\t\t'" . $this->TenderID . "',\n\t\t\t\t\t\t\t\t'" . $LineItems->StockID . "',\n\t\t\t\t\t\t\t\t'" . $LineItems->Quantity . "',\n\t\t\t\t\t\t\t\t'" . $LineItems->Units . "',\n\t\t\t\t\t\t\t\t'" . $LineItems->Price . "',\n\t\t\t\t\t\t\t\t'" . FormatDateForSQL($LineItems->ExpiryDate) . "',\n\t\t\t\t\t\t\t\t'" . $this->CurrCode . "')";
$ErrMsg = _('The suppliers offer could not be inserted into the database because');
$DbgMsg = _('The SQL statement used to insert the suppliers offer record and failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
if (DB_error_no($db) == 0) {
prnMsg(_('The offer for') . ' ' . $LineItems->StockID . ' ' . _('has been inserted into the database'), 'success');
$this->OfferMailText .= $LineItems->Quantity . ' ' . $LineItems->Units . ' ' . _('of') . ' ' . $LineItems->StockID . ' ' . _('at a price of') . ' ' . $this->CurrCode . number_format($LineItems->Price, 2) . "\n";
} else {
prnMsg(_('The offer for') . ' ' . $LineItems->StockID . ' ' . _('could not be inserted into the database'), 'error');
include 'includes/footer.inc';
exit;
}
}
}
} else {
foreach ($this->LineItems as $LineItem) {
if ($LineItem->Deleted == false) {
//Update only the LineItems which is not flagged as deleted
$sql = "UPDATE offers SET\n\t\t\t\t\t\tquantity='" . $LineItem->Quantity . "',\n\t\t\t\t\t\tprice='" . $LineItem->Price . "',\n\t\t\t\t\t\texpirydate='" . FormatDateForSQL($LineItem->ExpiryDate) . "'\n\t\t\t\t\tWHERE offerid='" . $LineItem->LineNo . "'";
$ErrMsg = _('The suppliers offer could not be updated on the database because');
$DbgMsg = _('The SQL statement used to update the suppliers offer record and failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
if (DB_error_no($db) == 0) {
prnMsg(_('The offer for') . ' ' . $LineItem->StockID . ' ' . _('has been updated in the database'), 'success');
$this->OfferMailText .= $LineItem->Quantity . ' ' . $LineItem->Units . ' ' . _('of') . ' ' . $LineItem->StockID . ' ' . _('at a price of') . ' ' . $this->CurrCode . $LineItem->Price . "\n";
} else {
prnMsg(_('The offer for') . ' ' . $LineItem->StockID . ' ' . _('could not be updated in the database'), 'error');
include 'includes/footer.inc';
exit;
}
} else {
// the LineItem is Deleted flag is true so delete it
$sql = "DELETE from offers WHERE offerid='" . $LineItem->LineNo . "'";
$ErrMsg = _('The supplier offer could not be deleted on the database because');
$DbgMsg = _('The SQL statement used to delete the suppliers offer record are failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
if (DB_error_no($db) == 0) {
prnMsg(_('The offer for') . ' ' . $LineItem->StockID . ' ' . _('has been deleted in the database'), 'info');
$this->OfferMailText .= $LineItem->Quantity . ' ' . $LineItem->Units . ' ' . _('of') . ' ' . $LineItem->StockID . ' ' . _('at a price of') . ' ' . $this->CurrCode . $LineItem->Price . ' ' . _('has been deleted') . "\n";
}
}
}
}
}
function save($db)
{
/* Does record exist for this tender
*/
if ($this->TenderId == '') {
$this->TenderId = GetNextTransNo(37, $db);
$HeaderSQL = "INSERT INTO tenders (tenderid,\n\t\t\t\t\t\t\t\t\t\t\tlocation,\n\t\t\t\t\t\t\t\t\t\t\taddress1,\n\t\t\t\t\t\t\t\t\t\t\taddress2,\n\t\t\t\t\t\t\t\t\t\t\taddress3,\n\t\t\t\t\t\t\t\t\t\t\taddress4,\n\t\t\t\t\t\t\t\t\t\t\taddress5,\n\t\t\t\t\t\t\t\t\t\t\taddress6,\n\t\t\t\t\t\t\t\t\t\t\ttelephone,\n\t\t\t\t\t\t\t\t\t\t\trequiredbydate)\n\t\t\t\t\t\t\t\tVALUES ('" . $this->TenderId . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->Location . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd1 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd2 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd3 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd4 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd5 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->DelAdd6 . "',\n\t\t\t\t\t\t\t\t\t\t'" . $this->Telephone . "',\n\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($this->RequiredByDate) . "')";
foreach ($this->Suppliers as $Supplier) {
$SuppliersSQL[] = "INSERT INTO tendersuppliers (tenderid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsupplierid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\temail)\n\t\t\t\t\t\t\t\tVALUES ('" . $this->TenderId . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Supplier->SupplierCode . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Supplier->EmailAddress . "')";
}
foreach ($this->LineItems as $LineItem) {
$ItemsSQL[] = "INSERT INTO tenderitems (tenderid,\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\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tunits)\n\t\t\t\t\t\t\t\t\t\t\tVALUES ('" . $this->TenderId . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItem->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItem->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItem->Units . "')";
}
} else {
$HeaderSQL = "UPDATE tenders SET location='" . $this->Location . "',\n\t\t\t\t\t\t\t\t\t\t\taddress1='" . $this->DelAdd1 . "',\n\t\t\t\t\t\t\t\t\t\t\taddress2='" . $this->DelAdd2 . "',\n\t\t\t\t\t\t\t\t\t\t\taddress3='" . $this->DelAdd3 . "',\n\t\t\t\t\t\t\t\t\t\t\taddress4='" . $this->DelAdd4 . "',\n\t\t\t\t\t\t\t\t\t\t\taddress5='" . $this->DelAdd5 . "',\n\t\t\t\t\t\t\t\t\t\t\taddress6='" . $this->DelAdd6 . "',\n\t\t\t\t\t\t\t\t\t\t\ttelephone='" . $this->Telephone . "',\n\t\t\t\t\t\t\t\t\t\t\trequiredbydate='" . FormatDateForSQL($this->RequiredByDate) . "'\n\t\t\t\t\t\tWHERE tenderid = '" . $this->TenderId . "'";
foreach ($this->Suppliers as $Supplier) {
$sql = "DELETE FROM tendersuppliers\n\t\t\t\t\tWHERE tenderid='" . $this->TenderId . "'";
$result = DB_query($sql, $db);
$SuppliersSQL[] = "INSERT INTO tendersuppliers (\n\t\t\t\t\t\t\t\t\ttenderid,\n\t\t\t\t\t\t\t\t\tsupplierid,\n\t\t\t\t\t\t\t\t\temail)\n\t\t\t\t\t\t\t\tVALUES ('" . $this->TenderId . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Supplier->SupplierCode . "',\n\t\t\t\t\t\t\t\t\t\t'" . $Supplier->EmailAddress . "')";
}
foreach ($this->LineItems as $LineItem) {
$sql = "DELETE FROM tenderitems\n\t\t\t\t\t\tWHERE tenderid='" . $this->TenderId . "'";
$result = DB_query($sql, $db);
$ItemsSQL[] = "INSERT INTO tenderitems (tenderid,\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\tquantity,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tunits)\n\t\t\t\t\t\t\t\tVALUES ('" . $this->TenderId . "',\n\t\t\t\t\t\t\t\t\t\t'" . $LineItem->StockID . "',\n\t\t\t\t\t\t\t\t\t\t'" . $LineItem->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t'" . $LineItem->Units . "')";
}
}
DB_Txn_Begin($db);
$result = DB_query($HeaderSQL, $db, '', '', True);
foreach ($SuppliersSQL as $sql) {
$result = DB_query($sql, $db, '', '', True);
}
foreach ($ItemsSQL as $sql) {
$result = DB_query($sql, $db, '', '', True);
}
DB_Txn_Commit($db);
}
function ReSequenceEffectiveDates($Item, $PriceList, $CurrAbbrev, $QuantityBreak, $db)
{
/*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where one price falls inside another effective date range except in the case of a blank end date - ie no end date - the default price for the currency/salestype.
I first thought that we would need to update the previous default price (blank end date), when a new default price is entered, to have an end date of the startdate of this new default price less 1 day - but this is converting a default price into a special price which could result in having two special prices over the same date range - best to leave it unchanged and use logic in the GetPrice.inc to ensure the correct default price is returned
*
* After further discussion (Ricard) if the new price has a blank end date - i.e. no end then the pre-existing price with no end date should be changed to have an end date just prior to the new default (no end date) price commencing
*/
//this is just the case where debtorno='' - see the Prices_Customer.php script for customer special prices
$SQL = "SELECT price,\r\n\t\t\t\t\t\tstartdate,\r\n\t\t\t\t\t\tenddate\r\n\t\t\t\tFROM pricematrix\r\n\t\t\t\tWHERE stockid='" . $Item . "'\r\n\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\r\n\t\t\t\tAND salestype='" . $PriceList . "'\r\n\t\t\t\tAND quantitybreak='" . $QuantityBreak . "'\r\n\t\t\t\tORDER BY startdate, enddate";
$result = DB_query($SQL);
while ($myrow = DB_fetch_array($result)) {
if (isset($NextStartDate)) {
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']), $NextStartDate)) {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
//Only if the previous enddate is after the new start date do we need to look at updates
if (Date1GreaterThanDate2(ConvertSQLDate($EndDate), ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE pricematrix SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate, 'd', -1)) . "'\r\n\t\t\t\t\t\t\t\t\t\tWHERE stockid ='" . $Item . "'\r\n\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\r\n\t\t\t\t\t\t\t\t\t\tAND salestype='" . $PriceList . "'\r\n\t\t\t\t\t\t\t\t\t\tAND startdate ='" . $StartDate . "'\r\n\t\t\t\t\t\t\t\t\t\tAND enddate = '" . $EndDate . "'\r\n\t\t\t\t\t\t\t\t\t\tAND quantitybreak ='" . $QuantityBreak . "'";
$UpdateResult = DB_query($SQL);
}
}
//end of if startdate after NextStartDate - we have a new NextStartDate
} else {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
}
$StartDate = $myrow['startdate'];
$EndDate = $myrow['enddate'];
$Price = $myrow['price'];
}
// end of loop around all prices
}
$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);
// Need to get the current location quantity will need it later for the stock movement
$SQL = "SELECT locstock.quantity\n\t\t\t\t\tFROM locstock\n\t\t\t\t\tWHERE locstock.stockid='" . $StockID . "'\n\t\t\t\t\t\tAND loccode= '" . $Location . "'";
$Result = DB_query($SQL, $db);
if (DB_num_rows($Result) == 1) {
$LocQtyRow = DB_fetch_row($Result);
$QtyOnHandPrior = $LocQtyRow[0];
} else {
// There must actually be some error this should never happen
$QtyOnHandPrior = 0;
}
if ($_SESSION['ProhibitNegativeStock'] == 0 or $_SESSION['ProhibitNegativeStock'] == 1 and $QtyOnHandPrior >= $Quantity) {
$SQL = "INSERT INTO stockmoves (\n\t\t\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\t\t\ttype,\n\t\t\t\t\t\t\t\t\ttransno,\n\t\t\t\t\t\t\t\t\tloccode,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\treference,\n\t\t\t\t\t\t\t\t\tqty,\n\t\t\t\t\t\t\t\t\tnewqoh)\n\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t'" . $StockID . "',\n\t\t\t\t\t\t\t\t\t17,\n\t\t\t\t\t\t\t\t\t'" . $AdjustmentNumber . "',\n\t\t\t\t\t\t\t\t\t'" . $Location . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLAdjustmentDate . "',\n\t\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $Narrative . "',\n\t\t\t\t\t\t\t\t\t'" . -$Quantity . "',\n\t\t\t\t\t\t\t\t\t'" . ($QtyOnHandPrior - $Quantity) . "'\n\t\t\t\t\t\t\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The stock movement record cannot be inserted because');
$DbgMsg = _('The following SQL to insert the stock movement record was used');
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the tax could not be added because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
/* if the tax is not 0 */
}
/*end of loop to post the tax */
/* Now the control account */
$SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\tamount)\n\t\t\t\t\t VALUES (21,\n\t\t\t\t\t \t'" . $CreditNoteNo . "',\n\t\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->CreditorsAct . "',\n\t\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . ' - ' . _('Credit Note') . ' ' . $_SESSION['SuppTrans']->SuppReference . ' ' . $_SESSION['SuppTrans']->CurrCode . locale_number_format($_SESSION['SuppTrans']->OvAmount + $_SESSION['SuppTrans']->OvGST, $_SESSION['SuppTrans']->CurrDecimalPlaces) . ' @ ' . _('a rate of') . ' ' . $_SESSION['SuppTrans']->ExRate . "',\n\t\t\t\t\t\t'" . ($LocalTotal + $TaxTotal / $_SESSION['SuppTrans']->ExRate) . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The general ledger transaction for the control total could not be added because');
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
/*Thats the end of the GL postings */
/*Now insert the credit note into the SuppTrans table*/
$SQL = "INSERT INTO supptrans (transno,\n\t\t\t\t\t\ttype,\n\t\t\t\t\t\tsupplierno,\n\t\t\t\t\t\tsuppreference,\n\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\tduedate,\n\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\tovamount,\n\t\t\t\t\t\tovgst,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\ttranstext)\n\t\t\t\tVALUES (\n\t\t\t\t\t'" . $CreditNoteNo . "',\n\t\t\t\t\t21,\n\t\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . "',\n\t\t\t\t\t'" . $_SESSION['SuppTrans']->SuppReference . "',\n\t\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t\t'" . FormatDateForSQL($_SESSION['SuppTrans']->DueDate) . "',\n\t\t\t\t\t'" . Date('Y-m-d H-i-s') . "',\n\t\t\t\t\t'" . -$_SESSION['SuppTrans']->OvAmount . "',\n\t\t\t\t\t'" . -$TaxTotal . "',\n\t\t\t\t\t'" . $_SESSION['SuppTrans']->ExRate . "',\n\t\t\t\t\t'" . $_SESSION['SuppTrans']->Comments . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier credit note transaction could not be added to the database because');
$DbgMsg = _('The following SQL to insert the supplier credit note was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
$SuppTransID = DB_Last_Insert_ID($db, 'supptrans', 'id');
/* Insert the tax totals for each tax authority where tax was charged on the invoice */
foreach ($_SESSION['SuppTrans']->Taxes as $TaxTotals) {
$SQL = "INSERT INTO supptranstaxes (supptransid,\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\t\tVALUES ('" . $SuppTransID . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TaxTotals->TaxAuthID . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$TaxTotals->TaxOvAmount . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier transaction taxes records could not be inserted because');
$DbgMsg = _('The following SQL to insert the supplier transaction taxes record was used:');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
/* Now update the GRN and PurchOrderDetails records for amounts invoiced
* can't use the previous loop around GRNs as this was only for where the creditors->GL link was active*/
foreach ($_SESSION['SuppTrans']->GRNs as $EnteredGRN) {
$SQL = "UPDATE purchorderdetails SET qtyinvoiced = qtyinvoiced - " . $EnteredGRN->This_QuantityInv . " \n\t\t\t\t\tWHERE podetailitem = '" . $EnteredGRN->PODetailItem . "'";
}
}
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;
}
if ($InputError == 0) {
$RequestNo = GetNextTransNo(38, $db);
$HeaderSQL = "INSERT INTO stockrequest (dispatchid,\r\n\t\t\t\t\t\t\t\t\t\t\tloccode,\r\n\t\t\t\t\t\t\t\t\t\t\tdepartmentid,\r\n\t\t\t\t\t\t\t\t\t\t\tdespatchdate,\r\n\t\t\t\t\t\t\t\t\t\t\tnarrative)\r\n\t\t\t\t\t\t\t\t\t\tVALUES(\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $RequestNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Request']->Location . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Request']->Department . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($_SESSION['Request']->DispatchDate) . "',\r\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['Request']->Narrative . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The request header record could not be inserted because');
$DbgMsg = _('The following SQL to insert the request header record was used');
$Result = DB_query($HeaderSQL, $db, $ErrMsg, $DbgMsg, true);
foreach ($_SESSION['Request']->LineItems as $LineItems) {
$LineSQL = "INSERT INTO stockrequestitems (dispatchitemsid,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tdispatchid,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tstockid,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tquantity,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tdecimalplaces,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tuom)\r\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES(\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItems->LineNumber . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $RequestNo . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItems->StockID . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItems->Quantity . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItems->DecimalPlaces . "',\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $LineItems->UOM . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The request line record could not be inserted because');
$DbgMsg = _('The following SQL to insert the request header record was used');
$Result = DB_query($LineSQL, $db, $ErrMsg, $DbgMsg, true);
}
$EmailSQL = "SELECT email\r\n\t\t\t\t\tFROM www_users, departments\r\n\t\t\t\t\tWHERE departments.authoriser = www_users.userid\r\n\t\t\t\t\t\tAND departments.departmentid = '" . $_SESSION['Request']->Department . "'";
$EmailResult = DB_query($EmailSQL, $db);
if ($myEmail = DB_fetch_array($EmailResult)) {
$ConfirmationText = _('An internal stock request has been created and is waiting for your authoritation');
$EmailSubject = _('Internal Stock Request needs your authoritation');
mail($myEmail['email'], $EmailSubject, $ConfirmationText);
$InsertQryResult = DB_query($HeaderSQL, $db, $ErrMsg, $DbgMsg, true);
foreach ($_SESSION['Items' . $identifier]->LineItems as $StockItem) {
/* Check to see if the quantity reduced to the same quantity
as already invoiced - so should set the line to completed */
if ($StockItem->Quantity == $StockItem->QtyInv) {
$Completed = 1;
} else {
/* order line is not complete */
$Completed = 0;
}
$LineItemsSQL = 'UPDATE salesorderdetails SET unitprice=' . $StockItem->Price . ',
quantity=' . $StockItem->Quantity . ',
discountpercent=' . floatval($StockItem->DiscountPercent) . ',
completed=' . $Completed . ',
poline=' . "'" . $StockItem->POLine . "'" . ',
itemdue=' . "'" . FormatDateForSQL($StockItem->ItemDue) . "'" . '
WHERE salesorderdetails.orderno=' . $_SESSION['ExistingOrder'] . '
AND salesorderdetails.orderlineno=' . "'" . $StockItem->LineNumber . "'";
$DbgMsg = _('The SQL that was used to modify the order line and failed was');
$ErrMsg = _('The updated order line cannot be modified because');
$Upd_LineItemResult = DB_query($LineItemsSQL, $db, $ErrMsg, $DbgMsg, true);
}
/* updated line items into sales order details */
$Result = DB_Txn_Commit($db);
unset($_SESSION['Items' . $identifier]->LineItems);
unset($_SESSION['Items' . $identifier]);
prnMsg(_('Order number') . ' ' . $_SESSION['ExistingOrder'] . ' ' . _('has been updated'), 'success');
echo '<br><a href="' . $rootpath . '/PrintCustOrder.php?' . SID . 'identifier=' . $identifier . '&TransNo=' . $_SESSION['ExistingOrder'] . '">' . _('Print packing slip - pre-printed stationery') . '</a>';
echo '<p><a href="' . $rootpath . '/ConfirmDispatch_Invoice.php?' . SID . 'identifier=' . $identifier . '&OrderNumber=' . $_SESSION['ExistingOrder'] . '">' . _('Confirm Order Delivery Quantities and Produce Invoice') . '</a>';
echo '<br><a target="_blank" href="' . $rootpath . '/PrintCustOrder_generic.php?' . SID . 'identifier=' . $identifier . '&TransNo=' . $_SESSION['ExistingOrder'] . '">' . _('Print packing slip') . ' (' . _('Laser') . ')' . '</a>';
echo '<p><a href="' . $rootpath . '/SelectSalesOrder.php?' . SID . 'identifier=' . $identifier . '">' . _('Select A Different Order') . '</a>';
$LastPrice = 0;
while ($myrow = DB_fetch_array($MovtsResult)) {
if ($LastPrice != $myrow['price'] or $LastDiscount != $myrow['discountpercent']) {
/* consolidate price history for records with same price/discount */
if (isset($qty)) {
$DateRange = ConvertSQLDate($FromDate);
if ($FromDate != $ToDate) {
$DateRange .= ' - ' . ConvertSQLDate($ToDate);
}
$PriceHistory[] = array($DateRange, $qty, $LastPrice, $LastDiscount);
$k++;
if ($k > 9) {
break;
/* 10 price records is enough to display */
}
if ($myrow['trandate'] < FormatDateForSQL(DateAdd(date($_SESSION['DefaultDateFormat']), 'y', -1))) {
break;
/* stop displaying pirce history more than a year old once we have at least one to display */
}
}
$LastPrice = $myrow['price'];
$LastDiscount = $myrow['discountpercent'];
$ToDate = $myrow['trandate'];
$qty = 0;
}
$qty += $myrow['qty'];
$FromDate = $myrow['trandate'];
}
if (isset($qty)) {
$DateRange = ConvertSQLDate($FromDate);
if ($FromDate != $ToDate) {
$PageNumber++;
include 'includes/PDFDeliveryDifferencesPageHeader.inc';
}
/*end of new page header */
}
/* end of while there are delivery differences to print */
$YPos -= $line_height;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 200, $FontSize, _('Total number of differences') . ' ' . locale_number_format($TotalDiffs), 'left');
if ($_POST['CategoryID'] == 'All' and $_POST['Location'] == 'All') {
$sql = "SELECT COUNT(salesorderdetails.orderno)\n\t\t\tFROM salesorderdetails INNER JOIN debtortrans\n\t\t\t\tON salesorderdetails.orderno=debtortrans.order_\n\t\t\tWHERE debtortrans.trandate>='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\tAND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
} elseif ($_POST['CategoryID'] != 'All' and $_POST['Location'] == 'All') {
$sql = "SELECT COUNT(salesorderdetails.orderno)\n\t\tFROM salesorderdetails INNER JOIN debtortrans\n\t\t\tON salesorderdetails.orderno=debtortrans.order_ INNER JOIN stockmaster\n\t\t\tON salesorderdetails.stkcode=stockmaster.stockid\n\t\tWHERE debtortrans.trandate>='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\tAND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'\n\t\tAND stockmaster.categoryid='" . $_POST['CategoryID'] . "'";
} elseif ($_POST['CategoryID'] == 'All' and $_POST['Location'] != 'All') {
$sql = "SELECT COUNT(salesorderdetails.orderno)\n\t\tFROM salesorderdetails INNER JOIN debtortrans\n\t\t\tON salesorderdetails.orderno=debtortrans.order_ INNER JOIN salesorders\n\t\t\tON salesorderdetails.orderno = salesorders.orderno\n\t\tWHERE debtortrans.trandate>='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\tAND debtortrans.trandate <='" . FormatDateForSQL($_POST['ToDate']) . "'\n\t\tAND salesorders.fromstkloc='" . $_POST['Location'] . "'";
} elseif ($_POST['CategoryID'] != 'All' and $_POST['Location'] != 'All') {
$sql = "SELECT COUNT(salesorderdetails.orderno)\n\t\tFROM salesorderdetails INNER JOIN debtortrans ON salesorderdetails.orderno=debtortrans.order_\n\t\t\tINNER JOIN salesorders ON salesorderdetails.orderno = salesorders.orderno\n\t\t\tINNER JOIN stockmaster ON salesorderdetails.stkcode = stockmaster.stockid\n\t\tWHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\tAND categoryid='" . $_POST['CategoryID'] . "'\n\t\tAND trandate >='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\tAND trandate <= '" . FormatDateForSQL($_POST['ToDate']) . "'";
}
if ($_SESSION['SalesmanLogin'] != '') {
$sql .= " AND debtortrans.salesperson='" . $_SESSION['SalesmanLogin'] . "'";
}
$ErrMsg = _('Could not retrieve the count of sales order lines in the period under review');
$result = DB_query($sql, $db, $ErrMsg);
$myrow = DB_fetch_row($result);
$YPos -= $line_height;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 200, $FontSize, _('Total number of order lines') . ' ' . locale_number_format($myrow[0]), 'left');
$YPos -= $line_height;
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 200, $FontSize, _('DIFOT') . ' ' . locale_number_format((1 - $TotalDiffs / $myrow[0]) * 100, 2) . '%', 'left');
$ReportFileName = $_SESSION['DatabaseName'] . '_DeliveryDifferences_' . date('Y-m-d') . '.pdf';
$pdf->OutputD($ReportFileName);
if ($_POST['Email'] == 'Yes') {
if (file_exists($_SESSION['reports_dir'] . '/' . $ReportFileName)) {
if (!Is_Date($DatePurchased)) {
$InputError = true;
prnMsg(_('The date purchased must be entered in the format:') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
echo '<br />' . _('Row:') . $Row . ' - ' . _('Invalid date format:') . ' ' . $DatePurchased;
}
if ($DepnType == 'DV') {
$DepnType = 1;
} else {
$DepnType = 0;
}
if ($InputError == false) {
//no errors
$TransNo = GetNextTransNo(49, $db);
$PeriodNo = GetPeriod(ConvertSQLDate($_POST['DateToEnter']), $db);
//attempt to insert the stock item
$sql = "INSERT INTO fixedassets (description,\n\t\t\t\t\t\t\t\t\t\t\tlongdescription,\n\t\t\t\t\t\t\t\t\t\t\tassetcategoryid,\n\t\t\t\t\t\t\t\t\t\t\tserialno,\n\t\t\t\t\t\t\t\t\t\t\tbarcode,\n\t\t\t\t\t\t\t\t\t\t\tassetlocation,\n\t\t\t\t\t\t\t\t\t\t\tcost,\n\t\t\t\t\t\t\t\t\t\t\taccumdepn,\n\t\t\t\t\t\t\t\t\t\t\tdepntype,\n\t\t\t\t\t\t\t\t\t\t\tdepnrate,\n\t\t\t\t\t\t\t\t\t\t\tdatepurchased)\n\t\t\t\t\t\t\tVALUES ('" . $Description . "',\n\t\t\t\t\t\t\t\t\t'" . $LongDescription . "',\n\t\t\t\t\t\t\t\t\t'" . $AssetCategoryID . "',\n\t\t\t\t\t\t\t\t\t'" . $SerialNo . "',\n\t\t\t\t\t\t\t\t\t'" . $BarCode . "',\n\t\t\t\t\t\t\t\t\t'" . $AssetLocationCode . "',\n\t\t\t\t\t\t\t\t\t'" . $Cost . "',\n\t\t\t\t\t\t\t\t\t'" . $AccumDepn . "',\n\t\t\t\t\t\t\t\t\t'" . $DepnType . "',\n\t\t\t\t\t\t\t\t\t'" . $DepnRate . "',\n\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($DatePurchased) . "')";
$ErrMsg = _('The asset could not be added because');
$DbgMsg = _('The SQL that was used to add the asset and failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
if (DB_error_no($db) == 0) {
//the insert of the new code worked so bang in the fixedassettrans records too
$AssetID = DB_Last_Insert_ID($db, 'fixedassets', 'assetid');
$sql = "INSERT INTO fixedassettrans ( assetid,\n\t\t\t\t\t\t\t\t\t\t\t\ttranstype,\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\ttransdate,\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\tinputdate,\n\t\t\t\t\t\t\t\t\t\t\t\tfixedassettranstype,\n\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ( '" . $AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t'49',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['DateToEnter'] . "',\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'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t\t'cost',\n\t\t\t\t\t\t\t\t\t\t\t'" . $Cost . "')";
$ErrMsg = _('The transaction for the cost of the asset could not be added because');
$DbgMsg = _('The SQL that was used to add the fixedasset trans record that failed was');
$InsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
$sql = "INSERT INTO fixedassettrans ( assetid,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttranstype,\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\ttransdate,\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\tinputdate,\n\t\t\t\t\t\t\t\t\t\t\t\t\tfixedassettranstype,\n\t\t\t\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\t\t\tVALUES ( '" . $AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t'49',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_POST['DateToEnter'] . "',\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'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t\t'depn',\n\t\t\t\t\t\t\t\t\t\t\t'" . $AccumDepn . "')";
$ErrMsg = _('The transaction for the cost of the asset could not be added because');
$DbgMsg = _('The SQL that was used to add the fixedasset trans record that failed was');
$InsResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);
if (DB_error_no($db) == 0) {
<option value="11">' . _('Credit Notes') . '</option>
<option value="12">' . _('Receipts') . '</option>';
echo '</select></td></tr>
</table>
<div class="centre">
<br />
<input type="submit" name="Go" value="' . _('Create PDF') . '" />
</div>
</form>
</div>';
include 'includes/footer.inc';
exit;
} else {
include 'includes/ConnectDB.inc';
}
$sql = "SELECT type,\n\t\t\tdebtortrans.debtorno,\n\t\t\ttransno,\n\t\t\ttrandate,\n\t\t\tovamount,\n\t\t\tovgst,\n\t\t\tinvtext,\n\t\t\tdebtortrans.rate,\n\t\t\tdecimalplaces\n\t\tFROM debtortrans INNER JOIN debtorsmaster\n\t\tON debtortrans.debtorno=debtorsmaster.debtorno\n\t\tINNER JOIN currencies\n\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\tWHERE type='" . $_POST['TransType'] . "'\n\t\tAND date_format(inputdate, '%Y-%m-%d')='" . FormatDateForSQL($_POST['Date']) . "'";
$result = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Payment Listing');
include 'includes/header.inc';
prnMsg(_('An error occurred getting the transactions'), 'error');
if ($debug == 1) {
prnMsg(_('The SQL used to get the transaction information that failed was') . ':<br />' . $sql, 'error');
}
include 'includes/footer.inc';
exit;
} elseif (DB_num_rows($result) == 0) {
$Title = _('Payment Listing');
include 'includes/header.inc';
echo '<br />';
prnMsg(_('There were no transactions found in the database for the date') . ' ' . $_POST['Date'] . '. ' . _('Please try again selecting a different date'), 'info');
$i++;
}
if (mb_strlen($_POST['BankRef']) > 12) {
$InputError = 1;
prnMsg(_('The bank reference text must be less than 12 characters long'), 'error');
$Errors[$i] = 'BankRef';
$i++;
}
if (!Is_Date($_POST['SupplierSince'])) {
$InputError = 1;
prnMsg(_('The supplier since field must be a date in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error');
$Errors[$i] = 'SupplierSince';
$i++;
}
if ($InputError != 1) {
$SQL_SupplierSince = FormatDateForSQL($_POST['SupplierSince']);
//first off validate inputs sensible
$sql = "SELECT COUNT(supplierid) FROM suppliers WHERE supplierid='" . $SupplierID . "'";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$SuppExists = DB_num_rows($result) > 0;
if ($SuppExists and $_POST['UpdateIfExists'] != 1) {
$UpdatedNum++;
} elseif ($SuppExists) {
$UpdatedNum++;
$supptranssql = "SELECT supplierno\n\t\t\t\t\t\t\t\tFROM supptrans\n\t\t\t\t\t\t\t\tWHERE supplierno='" . $SupplierID . "'";
$suppresult = DB_query($supptranssql, $db);
$supptrans = DB_num_rows($suppresult);
$suppcurrssql = "SELECT currcode\n\t\t\t\t\t\t\t\tFROM suppliers\n\t\t\t\t\t\t\t\tWHERE supplierid='" . $SupplierID . "'";
$currresult = DB_query($suppcurrssql, $db);
$suppcurr = DB_fetch_row($currresult);
function submit(&$db, &$StockID, &$DemandID)
{
// In this section if hit submit button. Do edit checks. If all checks pass, see if record already
// exists for StockID/Duedate/MRPDemandtype combo; that means do an Update, otherwise, do INSERT.
//initialise no input errors assumed initially before we test
// echo "<br/>Submit - DemandID = $DemandID<br/>";
$FormatedDuedate = FormatDateForSQL($_POST['Duedate']);
$InputError = 0;
/* actions to take once the user has clicked the submit button
ie the page has called itself with some user input */
//first off validate inputs sensible
if (!is_numeric($_POST['Quantity'])) {
$InputError = 1;
prnMsg(_('Quantity must be numeric'), 'error');
}
if ($_POST['Quantity'] <= 0) {
$InputError = 1;
prnMsg(_('Quantity must be greater than 0'), 'error');
}
if (!Is_Date($_POST['Duedate'])) {
$InputError = 1;
prnMsg(_('Invalid due date'), 'error');
}
$sql = "SELECT mrpdemandtype FROM mrpdemandtypes\n\t\t\t\tWHERE mrpdemandtype='" . $_POST['MRPDemandtype'] . "'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) == 0) {
$InputError = 1;
prnMsg(_('Invalid demand type'), 'error');
}
// Check if valid part number - Had done a Select Count(*), but that returned a 1 in DB_num_rows
// even if there was no record.
$sql = "SELECT stockid FROM stockmaster\n\t\t\t\tWHERE stockid='" . $StockID . "'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) == 0) {
$InputError = 1;
prnMsg($StockID . ' ' . _('is not a valid item code'), 'error');
unset($_POST['StockID']);
unset($StockID);
}
// Check if part number/demand type/due date combination already exists
$sql = "SELECT stockid FROM mrpdemands\n\t\t\tWHERE stockid='" . $StockID . "'\n\t\t\t\tAND mrpdemandtype='" . $_POST['MRPDemandtype'] . "'\n\t\t\t\tAND duedate='" . $FormatedDuedate . "'\n\t\t\t\tAND demandid <> '" . $DemandID . "'";
$result = DB_query($sql, $db);
if (DB_num_rows($result) > 0) {
$InputError = 1;
prnMsg(_('Record already exists for part number/demand type/date'), 'error');
}
if ($InputError != 1) {
$sql = "SELECT COUNT(*) FROM mrpdemands\n\t\t\t\t WHERE demandid='" . $DemandID . "'\n\t\t\t\t GROUP BY demandid";
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
if ($myrow[0] > 0) {
//If $myrow[0] > 0, it means this is an edit, so do an update
$sql = "UPDATE mrpdemands SET quantity = '" . $_POST['Quantity'] . "',\n\t\t\t\t\t\t\tmrpdemandtype = '" . trim(mb_strtoupper($_POST['MRPDemandtype'])) . "',\n\t\t\t\t\t\t\tduedate = '" . $FormatedDuedate . "'\n\t\t\t\t\tWHERE demandid = '" . $DemandID . "'";
$msg = _("The MRP demand record has been updated for") . ' ' . $StockID;
} else {
// If $myrow[0] from SELECT count(*) is zero, this is an entry of a new record
$sql = "INSERT INTO mrpdemands (stockid,\n\t\t\t\t\t\t\tmrpdemandtype,\n\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\tduedate)\n\t\t\t\t\t\tVALUES ('" . $StockID . "',\n\t\t\t\t\t\t\t'" . trim(mb_strtoupper($_POST['MRPDemandtype'])) . "',\n\t\t\t\t\t\t\t'" . $_POST['Quantity'] . "',\n\t\t\t\t\t\t\t'" . $FormatedDuedate . "'\n\t\t\t\t\t\t)";
$msg = _('A new MRP demand record has been added to the database for') . ' ' . $StockID;
}
$result = DB_query($sql, $db, _('The update/addition of the MRP demand record failed because'));
prnMsg($msg, 'success');
echo '<br />';
unset($_POST['MRPDemandtype']);
unset($_POST['Quantity']);
unset($_POST['StockID']);
unset($_POST['Duedate']);
unset($StockID);
unset($DemandID);
}
// End of else where DB_num_rows showed there was a valid stockmaster record
display($db, $StockID, $DemandID);
}
echo '<tr><td colspan="2"></td>';
echo '<td>' . _('Total For') . ' ' . $DayNames[DayOfWeekFromSQLDate(DateAdd($LastDate, 'd', -1))] . ' ' . DateAdd(ConvertSQLDate($LastDate), 'd', 0) . '</td>';
echo '<td class="number">' . $SubTotalQuantity . '</td>';
echo '<td class="number">' . number_format($SubTotalValue, $_SESSION['Currencies'][$_SESSION['CompanyRecord']['currencydefault']]['DecimalPlaces']) . '</td></tr>';
echo '<tr><th colspan="5"></th></tr>';
echo '<tr><th colspan="5"></th></tr>';
echo '<tr><td colspan="2"></td>';
echo '<td><b>' . 'Total' . '</b></td>';
echo '<td class="number"><b>' . $TotalQuantity . '</b></td>';
echo '<td class="number"><b>' . number_format($TotalValue, $_SESSION['Currencies'][$_SESSION['CompanyRecord']['currencydefault']]['DecimalPlaces']) . '</b></td></tr>';
echo '<tr><th colspan="5"></th></tr>';
echo '</table>';
echo '</td>';
echo '<td width="33%" style="text-align: left;vertical-align: top;">';
echo '<table width="100%" class="selection">';
$sql = "SELECT stockmoves.stockid,\n\t\t\t\tstockmaster.description,\n\t\t\t\tsum(-qty*price) AS value\n\t\t\tFROM stockmoves\n\t\t\tLEFT JOIN stockmaster\n\t\t\tON stockmaster.stockid=stockmoves.stockid\n\t\t\tLEFT JOIN stockcategory\n\t\t\tON stockmaster.categoryid=stockcategory.categoryid\n\t\t\tWHERE stockcategory.stocktype='X'\n\t\t\t\tAND stockmoves.trandate='" . FormatDateForSQL($_POST['ReportDate']) . "'\n\t\t\tGROUP BY stockmoves.stockid";
$result = DB_query($sql, $db);
while ($myrow = DB_fetch_array($result)) {
$Point1[] = $myrow['value'];
$Point2[] = $myrow['stockid'];
}
// Dataset definition
$DataSet = new pData();
$DataSet->AddPoint($Point1, "Serie1");
$DataSet->AddPoint($Point2, "Serie2");
$DataSet->AddAllSeries();
$DataSet->SetAbsciseLabelSerie("Serie2");
// Initialise the graph
$Test = new pChart(420, 250);
$Test->drawFilledRoundedRectangle(7, 7, 413, 243, 5, 240, 240, 240);
$Test->drawRoundedRectangle(5, 5, 415, 245, 5, 230, 230, 230);
$ErrMsg = _('The purchase order detail line could not be deleted because');
$DbgMsg = _('The SQL statement used to delete the purchase order detail record, that failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
}
} else {
if ($POLine->PODetailRec == '') {
/*When the purchase order line is an existing record the auto-increment
* field PODetailRec is given to the session for that POLine
* So it will only be a new POLine if PODetailRec is empty
*/
$sql = "INSERT INTO purchorderdetails ( orderno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\titemcode,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode,\n\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\tquantityord,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor)\n\t\t\t\t\t\t\t\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['PO' . $identifier]->OrderNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->Suppliers_PartNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t'" . $POLine->ConversionFactor . "')";
} else {
if ($POLine->Quantity == $POLine->QtyReceived) {
$sql = "UPDATE purchorderdetails SET itemcode='" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate ='" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription='" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode='" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice='" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantityord='" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref='" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref='" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit='" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno='" . DB_escape_string($POLine->Suppliers_PartNo) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcompleted=1,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid='" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor = '" . $POLine->ConversionFactor . "' \n\t\t\t\t\t\t\t\tWHERE podetailitem='" . $POLine->PODetailRec . "'";
} else {
$sql = "UPDATE purchorderdetails SET itemcode='" . $POLine->StockID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdeliverydate ='" . FormatDateForSQL($POLine->ReqDelDate) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\titemdescription='" . DB_escape_string($POLine->ItemDescription) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tglcode='" . $POLine->GLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tunitprice='" . $POLine->Price . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tquantityord='" . $POLine->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tshiptref='" . $POLine->ShiptRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tjobref='" . $POLine->JobRef . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliersunit='" . $POLine->SuppliersUnit . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tsuppliers_partno='" . $POLine->Suppliers_PartNo . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tassetid='" . $POLine->AssetID . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tconversionfactor = '" . $POLine->ConversionFactor . "'\n\t\t\t\t\t\t\t\tWHERE podetailitem='" . $POLine->PODetailRec . "'";
}
}
}
$ErrMsg = _('One of the purchase order detail records could not be updated because');
$DbgMsg = _('The SQL statement used to update the purchase order detail record that failed was');
$result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
}
/* end of the loop round the detail line items on the order */
echo '<br /><br />';
prnMsg(_('Purchase Order') . ' ' . $_SESSION['PO' . $identifier]->OrderNo . ' ' . _('has been updated'), 'success');
if ($_SESSION['PO' . $identifier]->AllowPrintPO == 1 and ($_SESSION['PO' . $identifier]->Status == 'Authorised' or $_SESSION['PO' . $identifier]->Status == 'Printed')) {
echo '<br /><div class="centre"><a target="_blank" href="' . $rootpath . '/PO_PDFPurchOrder.php?OrderNo=' . $_SESSION['PO' . $identifier]->OrderNo . '">' . _('Print Purchase Order') . '</a></div>';
}
}
/*end of if its a new order or an existing one */
} else {
include 'includes/PDFStarter.php';
$pdf->addInfo('Title', _('Orders Invoiced Report'));
$pdf->addInfo('Subject', _('Orders from') . ' ' . $_POST['FromDate'] . ' ' . _('to') . ' ' . $_POST['ToDate']);
$line_height = 12;
$PageNumber = 1;
$TotalDiffs = 0;
}
if ($_POST['CategoryID'] == 'All' and $_POST['Location'] == 'All') {
$sql = "SELECT salesorders.orderno,\n\t\t\t\t salesorders.debtorno,\n\t\t\t\t salesorders.branchcode,\n\t\t\t\t salesorders.customerref,\n\t\t\t\t salesorders.orddate,\n\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t salesorders.printedpackingslip,\n\t\t\t\t salesorders.datepackingslipprinted,\n\t\t\t\t salesorderdetails.stkcode,\n\t\t\t\t stockmaster.description,\n\t\t\t\t stockmaster.units,\n\t\t\t\t stockmaster.decimalplaces,\n\t\t\t\t debtorsmaster.name,\n\t\t\t\t custbranch.brname,\n\t\t\t\t locations.locationname,\n\t\t\t\t SUM(salesorderdetails.quantity) AS totqty,\n\t\t\t\t SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced\n\t\t\t FROM salesorders\n\t\t\t\t INNER JOIN salesorderdetails\n\t\t\t\t ON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t INNER JOIN stockmaster\n\t\t\t\t ON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t INNER JOIN debtorsmaster\n\t\t\t\t ON salesorders.debtorno=debtorsmaster.debtorno\n\t\t\t\t INNER JOIN custbranch\n\t\t\t\t ON custbranch.debtorno=salesorders.debtorno\n\t\t\t\t AND custbranch.branchcode=salesorders.branchcode\n\t\t\t\t INNER JOIN locations\n\t\t\t\t ON salesorders.fromstkloc=locations.loccode\n\t\t\t WHERE orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\tAND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
} elseif ($_POST['CategoryID'] != 'All' and $_POST['Location'] == 'All') {
$sql = "SELECT salesorders.orderno,\n\t\t\t\t salesorders.debtorno,\n\t\t\t\t salesorders.branchcode,\n\t\t\t\t salesorders.customerref,\n\t\t\t\t salesorders.orddate,\n\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t salesorders.printedpackingslip,\n\t\t\t\t salesorders.datepackingslipprinted,\n\t\t\t\t salesorderdetails.stkcode,\n\t\t\t\t stockmaster.description,\n\t\t\t\t stockmaster.units,\n\t\t\t\t stockmaster.decimalplaces,\n\t\t\t\t debtorsmaster.name,\n\t\t\t\t custbranch.brname,\n\t\t\t\t locations.locationname,\n\t\t\t\t SUM(salesorderdetails.quantity) AS totqty,\n\t\t\t\t SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced\n\t\t\t FROM salesorders\n\t\t\t\t INNER JOIN salesorderdetails\n\t\t\t\t ON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t INNER JOIN stockmaster\n\t\t\t\t ON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t INNER JOIN debtorsmaster\n\t\t\t\t ON salesorders.debtorno=debtorsmaster.debtorno\n\t\t\t\t INNER JOIN custbranch\n\t\t\t\t ON custbranch.debtorno=salesorders.debtorno\n\t\t\t\t AND custbranch.branchcode=salesorders.branchcode\n\t\t\t\t INNER JOIN locations\n\t\t\t\t ON salesorders.fromstkloc=locations.loccode\n\t\t\t WHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'\n\t\t\t\t AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\t AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
} elseif ($_POST['CategoryID'] == 'All' and $_POST['Location'] != 'All') {
$sql = "SELECT salesorders.orderno,\n\t\t\t\t salesorders.debtorno,\n\t\t\t\t salesorders.branchcode,\n\t\t\t\t salesorders.customerref,\n\t\t\t\t salesorders.orddate,\n\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t salesorders.printedpackingslip,\n\t\t\t\t salesorders.datepackingslipprinted,\n\t\t\t\t salesorderdetails.stkcode,\n\t\t\t\t stockmaster.description,\n\t\t\t\t stockmaster.units,\n\t\t\t\t stockmaster.decimalplaces,\n\t\t\t\t debtorsmaster.name,\n\t\t\t\t custbranch.brname,\n\t\t\t\t locations.locationname,\n\t\t\t\t SUM(salesorderdetails.quantity) AS totqty,\n\t\t\t\t SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced\n\t\t\t FROM salesorders\n\t\t\t\t INNER JOIN salesorderdetails\n\t\t\t\t ON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t INNER JOIN stockmaster\n\t\t\t\t ON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t INNER JOIN debtorsmaster\n\t\t\t\t ON salesorders.debtorno=debtorsmaster.debtorno\n\t\t\t\t INNER JOIN custbranch\n\t\t\t\t ON custbranch.debtorno=salesorders.debtorno\n\t\t\t\t AND custbranch.branchcode=salesorders.branchcode\n\t\t\t\t INNER JOIN locations\n\t\t\t\t ON salesorders.fromstkloc=locations.loccode\n\t\t\t WHERE salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\t\t\t AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\t AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
} elseif ($_POST['CategoryID'] != 'All' and $_POST['location'] != 'All') {
$sql = "SELECT salesorders.orderno,\n\t\t\t\t salesorders.debtorno,\n\t\t\t\t salesorders.branchcode,\n\t\t\t\t salesorders.customerref,\n\t\t\t\t salesorders.orddate,\n\t\t\t\t salesorders.fromstkloc,\n\t\t\t\t salesorderdetails.stkcode,\n\t\t\t\t stockmaster.description,\n\t\t\t\t stockmaster.units,\n\t\t\t\t stockmaster.decimalplaces,\n\t\t\t\t debtorsmaster.name,\n\t\t\t\t custbranch.brname,\n\t\t\t\t locations.locationname,\n\t\t\t\t SUM(salesorderdetails.quantity) AS totqty,\n\t\t\t\t SUM(salesorderdetails.qtyinvoiced) AS totqtyinvoiced\n\t\t\t\t INNER JOIN locations\n\t\t\t\t ON salesorders.fromstkloc=locations.loccode\n\t\t\tFROM salesorders\n\t\t\t\t INNER JOIN salesorderdetails\n\t\t\t\t ON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t INNER JOIN stockmaster\n\t\t\t\t ON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t INNER JOIN debtorsmaster\n\t\t\t\t ON salesorders.debtorno=debtorsmaster.debtorno\n\t\t\t\t INNER JOIN custbranch\n\t\t\t\t ON custbranch.debtorno=salesorders.debtorno\n\t\t\t\t AND custbranch.branchcode=salesorders.branchcode\n\t\t\tWHERE stockmaster.categoryid ='" . $_POST['CategoryID'] . "'\n\t\t\t\t AND salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\t\t\t AND orddate >='" . FormatDateForSQL($_POST['FromDate']) . "'\n\t\t\t\t AND orddate <='" . FormatDateForSQL($_POST['ToDate']) . "'";
}
if ($_SESSION['SalesmanLogin'] != '') {
$sql .= " AND salesorders.salesperson='" . $_SESSION['SalesmanLogin'] . "'";
}
$sql .= " GROUP BY salesorders.orderno,\n\t\t\t\t\tsalesorders.debtorno,\n\t\t\t\t\tsalesorders.branchcode,\n\t\t\t\t\tsalesorders.customerref,\n\t\t\t\t\tsalesorders.orddate,\n\t\t\t\t\tsalesorders.fromstkloc,\n\t\t\t\t\tsalesorderdetails.stkcode,\n\t\t\t\t\tstockmaster.description,\n\t\t\t\t\tstockmaster.units,\n\t\t\t\t\tstockmaster.decimalplaces\n\t\t\tORDER BY salesorders.orderno";
$Result = DB_query($sql, $db, '', '', false, false);
//dont trap errors here
if (DB_error_no($db) != 0) {
include 'includes/header.inc';
prnMsg(_('An error occurred getting the orders details'), '', _('Database Error'));
if ($debug == 1) {
prnMsg(_('The SQL used to get the orders that failed was') . '<br />' . $sql, '', _('Database Error'));
}
include 'includes/footer.inc';
exit;
<td>' . $DisplayVolume . '</td>
</tr></table></td><td class="number" colspan="4"><b>' . _('TOTAL Excl Tax/Freight') . '</b></td>
<td colspan="' . $ColSpanNumber . '" class="number">' . $DisplayTotal . '</td>
<td colspan="3" style="text-align: right;"><button type="submit" name="Recalculate">' . _('Re-Calculate') . '</button></td></tr></table>';
echo '<br /><div class="centre">
<button type="submit" name="DeliveryDetails">' . _('Enter Delivery Details and Confirm Order') . '</button></div>';
}
# end of if lines
/* Now show the stock item selection search stuff below */
if (!isset($_POST['QuickEntry']) and !isset($_POST['SelectAsset'])) {
echo '<input type="hidden" name="PartSearch" value="' . _('Yes Please') . '" />';
if ($_SESSION['FrequentlyOrderedItems'] > 0) {
//show the Frequently Order Items selection where configured to do so
// Select the most recently ordered items for quick select
$SixMonthsAgo = DateAdd(Date($_SESSION['DefaultDateFormat']), 'm', -6);
$SQL = "SELECT stockmaster.units,\n\t\t\t\t\t\tstockmaster.description,\n\t\t\t\t\t\tstockmaster.stockid,\n\t\t\t\t\t\tsalesorderdetails.stkcode,\n\t\t\t\t\t\tSUM(qtyinvoiced) salesqty\n\t\t\t\t\tFROM `salesorderdetails`\n\t\t\t\t\tINNER JOIN `stockmaster`\n\t\t\t\t\t\tON salesorderdetails.stkcode = stockmaster.stockid\n\t\t\t\t\tWHERE ActualDispatchDate >= '" . FormatDateForSQL($SixMonthsAgo) . "'\n\t\t\t\t\tGROUP BY stkcode\n\t\t\t\t\tORDER BY salesqty DESC\n\t\t\t\t\tLIMIT " . $_SESSION['FrequentlyOrderedItems'];
$result2 = DB_query($SQL, $db);
echo '<p class="page_title_text"><img src="' . $rootpath . '/css/' . $theme . '/images/magnifier.png" title="' . _('Search') . '" alt="" />' . ' ';
echo _('Frequently Ordered Items') . '</p><br />';
echo '<div class="page_help_text">' . _('Frequently Ordered Items') . _(', shows the most frequently ordered items in the last 6 months. You can choose from this list, or search further for other items') . '.</div><br />';
echo '<table class="selection">';
$TableHeader = '<tr><th>' . _('Code') . '</th>
<th>' . _('Description') . '</th>
<th>' . _('Units') . '</th>
<th>' . _('On Hand') . '</th>
<th>' . _('On Demand') . '</th>
<th>' . _('On Order') . '</th>
<th>' . _('Available') . '</th>
<th>' . _('Quantity') . '</th>
<th>' . _('Price') . '</th>
</tr>';
function ReSequenceEffectiveDates($Item, $PriceList, $CurrAbbrev, $CustomerID, $db)
{
/*This is quite complicated - the idea is that prices set up should be unique and there is no way two prices could be returned as valid - when getting a price in includes/GetPrice.inc the logic is to first look for a price of the salestype/currency within the effective start and end dates - then if not get the price with a start date prior but a blank end date (the default price). We would not want two prices where the effective dates fall between an existing price so it is necessary to update enddates of prices - with me - I am just hanging on here myself
Prices with no end date are default prices and need to be ignored in this resquence*/
$SQL = "SELECT branchcode,\n\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\tenddate\n\t\t\t\t\t\tFROM prices\n\t\t\t\t\t\tWHERE debtorno='" . $CustomerID . "'\n\t\t\t\t\t\tAND stockid='" . $Item . "'\n\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\n\t\t\t\t\t\tAND typeabbrev='" . $PriceList . "'\n\t\t\t\t\t\tAND enddate<>''\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\tstartdate,\n\t\t\t\t\t\tenddate";
$result = DB_query($SQL, $db);
unset($BranchCode);
while ($myrow = DB_fetch_array($result)) {
if (!isset($BranchCode)) {
unset($NextDefaultStartDate);
//a price with a blank end date
unset($NextStartDate);
unset($EndDate);
unset($StartDate);
$BranchCode = $myrow['branchcode'];
}
if (isset($NextStartDate)) {
if (Date1GreaterThanDate2(ConvertSQLDate($myrow['startdate']), $NextStartDate)) {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
if (Date1GreaterThanDate2(ConvertSQLDate($EndDate), ConvertSQLDate($myrow['startdate']))) {
/*Need to make the end date the new start date less 1 day */
$SQL = "UPDATE prices SET enddate = '" . FormatDateForSQL(DateAdd($NextStartDate, 'd', -1)) . "'\n\t\t\t\t\t\t\t\t\t\tWHERE stockid ='" . $Item . "'\n\t\t\t\t\t\t\t\t\t\tAND currabrev='" . $CurrAbbrev . "'\n\t\t\t\t\t\t\t\t\t\tAND typeabbrev='" . $PriceList . "'\n\t\t\t\t\t\t\t\t\t\tAND startdate ='" . $StartDate . "'\n\t\t\t\t\t\t\t\t\t\tAND enddate = '" . $EndDate . "'\n\t\t\t\t\t\t\t\t\t\tAND debtorno ='" . $CustomerID . "'";
$UpdateResult = DB_query($SQL, $db);
}
}
//end of if startdate after NextStartDate - we have a new NextStartDate
} else {
$NextStartDate = ConvertSQLDate($myrow['startdate']);
}
$StartDate = $myrow['startdate'];
$EndDate = $myrow['enddate'];
}
}
function ShowDays(&$db)
{
//####LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_LISTALL_####
// List all records in date range
$FromDate = FormatDateForSQL($_POST['FromDate']);
$ToDate = FormatDateForSQL($_POST['ToDate']);
$sql = "SELECT calendardate,\n\t\t\t\t daynumber,\n\t\t\t\t manufacturingflag,\n\t\t\t\t DAYNAME(calendardate) as dayname\n\t\t\tFROM mrpcalendar\n\t\t\tWHERE calendardate >='" . $FromDate . "'\n\t\t\tAND calendardate <='" . $ToDate . "'";
$ErrMsg = _('The SQL to find the parts selected failed with the message');
$result = DB_query($sql, $ErrMsg);
echo '<br />
<table class="selection">
<tr>
<th>' . _('Date') . '</th>
<th>' . _('Manufacturing Date') . '</th>
</tr>';
$ctr = 0;
while ($myrow = DB_fetch_array($result)) {
$flag = _('Yes');
if ($myrow['manufacturingflag'] == 0) {
$flag = _('No');
}
printf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
</tr>', ConvertSQLDate($myrow[0]), _($myrow[3]), $flag);
}
//END WHILE LIST LOOP
echo '</table>';
echo '<br /><br />';
unset($ChangeDate);
ShowInputForm($db, $ChangeDate);
}
include 'includes/DefineSerialItems.php';
include 'includes/DefineStockTransfers.php';
include 'includes/session.inc';
$title = _('Inventory Transfer') . ' - ' . _('Receiving');
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
if (isset($_GET['NewTransfer'])) {
unset($_SESSION['Transfer']);
}
if (isset($_SESSION['Transfer']) and $_SESSION['Transfer']->TrfID == '') {
unset($_SESSION['Transfer']);
}
if (isset($_POST['ProcessTransfer'])) {
/*Ok Time To Post transactions to Inventory Transfers, and Update Posted variable & received Qty's to LocTransfers */
$PeriodNo = GetPeriod($_SESSION['Transfer']->TranDate, $db);
$SQLTransferDate = FormatDateForSQL($_SESSION['Transfer']->TranDate);
$InputError = False;
/*Start off hoping for the best */
$i = 0;
$TotalQuantity = 0;
foreach ($_SESSION['Transfer']->TransferItem as $TrfLine) {
$_POST['Qty' . $i] = filter_number_input($_POST['Qty' . $i]);
if (is_numeric($_POST['Qty' . $i])) {
/*Update the quantity received from the inputs */
$_SESSION['Transfer']->TransferItem[$i]->Quantity = $_POST['Qty' . $i];
} else {
prnMsg(_('The quantity entered for') . ' ' . $TrfLine->StockID . ' ' . _('is not numeric') . '. ' . _('All quantities must be numeric'), 'error');
$InputError = True;
}
if ($_POST['Qty' . $i] < 0) {
prnMsg(_('The quantity entered for') . ' ' . $TrfLine->StockID . ' ' . _('is negative') . '. ' . _('All quantities must be for positive numbers greater than zero'), 'error');
/*Only have dates in SuppTrans no periods so need to get the starting date */
if (mb_strpos($PeriodEnd, '/')) {
$Date_Array = explode('/', $PeriodEnd);
} elseif (mb_strpos($PeriodEnd, '.')) {
$Date_Array = explode('.', $PeriodEnd);
}
if ($_SESSION['DefaultDateFormat'] == 'd/m/Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[1] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
} elseif ($_SESSION['DefaultDateFormat'] == 'm/d/Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[0] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
} elseif ($_SESSION['DefaultDateFormat'] == 'Y/m/d') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[2] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[1]));
} elseif ($_SESSION['DefaultDateFormat'] == 'd.m.Y') {
$StartDateSQL = Date('Y-m-d', mktime(0, 0, 0, (int) $Date_Array[1] - $_POST['NoOfPeriods'] + 1, 1, (int) $Date_Array[2]));
}
$SQL = "SELECT supptrans.type,\n\t\t\tsupptrans.suppreference,\n\t\t\tsystypes.typename,\n\t\t\tsupptrans.trandate,\n\t\t\tsuppliers.suppname,\n \t\t\tsupptrans.ovamount/supptrans.rate AS netamount,\n\t\t\tsupptranstaxes.taxamount/supptrans.rate AS taxamt\n\t\tFROM supptrans\n\t\tINNER JOIN suppliers ON supptrans.supplierno=suppliers.supplierid\n\t\tINNER JOIN systypes ON supptrans.type=systypes.typeid\n\t\tINNER JOIN supptranstaxes ON supptrans.id = supptranstaxes.supptransid\n\t\tWHERE supptrans.trandate >= '" . $StartDateSQL . "'\n\t\tAND supptrans.trandate <= '" . FormatDateForSQL($PeriodEnd) . "'\n\t\tAND (supptrans.type=20 OR supptrans.type=21)\n\t\tAND supptranstaxes.taxauthid = '" . $_POST['TaxAuthority'] . "'\n\t\tORDER BY supptrans.trandate";
$SuppTransResult = DB_query($SQL, $db, '', '', false, false);
//doint trap errors in DB_query
if (DB_error_no($db) != 0) {
$title = _('Taxation Reporting Error');
include 'includes/header.inc';
echo _('The accounts payable transaction details could not be retrieved because') . ' ' . DB_error_msg($db);
echo '<br /><a href="' . $rootpath . '/index.php?">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
if ($_POST['DetailOrSummary'] == 'Detail') {
include 'includes/PDFTaxPageHeader.inc';
$DbgMsg = _('The following SQL to insert the GL transaction was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
}
/*Thats the end of the GL postings */
/*Now insert the credit note into the SuppTrans table*/
$SQL = 'INSERT INTO supptrans (transno,
type,
supplierno,
suppreference,
trandate,
duedate,
ovamount,
ovgst,
rate,
transtext)
VALUES (' . $CreditNoteNo . ",\n\t\t\t\t21,\n\t\t\t\t'" . $_SESSION['SuppTrans']->SupplierID . "',\n\t\t\t\t'" . $_SESSION['SuppTrans']->SuppReference . "',\n\t\t\t\t'" . $SQLCreditNoteDate . "',\n\t\t\t\t'" . FormatDateForSQL($_SESSION['SuppTrans']->DueDate) . "',\n\t\t\t\t" . round(-$_SESSION['SuppTrans']->OvAmount, 2) . ',
' . round(-$TaxTotal, 2) . ',
' . $_SESSION['SuppTrans']->ExRate . ",\n\t\t\t\t'" . $_SESSION['SuppTrans']->Comments . "')";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier credit note transaction could not be added to the database because');
$DbgMsg = _('The following SQL to insert the supplier credit note was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, True);
$SuppTransID = DB_Last_Insert_ID($db, 'supptrans', 'id');
/* Insert the tax totals for each tax authority where tax was charged on the invoice */
foreach ($_SESSION['SuppTrans']->Taxes as $TaxTotals) {
$SQL = 'INSERT INTO supptranstaxes (supptransid,
taxauthid,
taxamount)
VALUES (' . $SuppTransID . ',
' . $TaxTotals->TaxAuthID . ',
' . -$TaxTotals->TaxOvAmount . ')';
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The supplier transaction taxes records could not be inserted because');
$pdf->addInfo('Subject', _('Payment Run') . ' - ' . _('suppliers from') . ' ' . $_POST['FromCriteria'] . ' to ' . $_POST['ToCriteria'] . ' in ' . $_POST['Currency'] . ' ' . _('and Due By') . ' ' . $_POST['AmountsDueBy']);
$PageNumber = 1;
$line_height = 12;
/*Now figure out the invoice less credits due for the Supplier range under review */
include 'includes/PDFPaymentRunPageHeader.inc';
$sql = "SELECT suppliers.supplierid,\n\t\t\t\t\tcurrencies.decimalplaces AS currdecimalplaces,\n\t\t\t\t\tSUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance\n\t\t\tFROM suppliers INNER JOIN paymentterms\n\t\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t\t\tINNER JOIN supptrans\n\t\t\tON suppliers.supplierid = supptrans.supplierno\n\t\t\tINNER JOIN systypes\n\t\t\tON systypes.typeid = supptrans.type\n\t\t\tINNER JOIN currencies\n\t\t\tON suppliers.currcode=currencies.currabrev\n\t\t\tWHERE supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0\n\t\t\tAND supptrans.duedate <='" . FormatDateForSQL($_POST['AmountsDueBy']) . "'\n\t\t\tAND supptrans.hold=0\n\t\t\tAND suppliers.currcode = '" . $_POST['Currency'] . "'\n\t\t\tAND supptrans.supplierNo >= '" . $_POST['FromCriteria'] . "'\n\t\t\tAND supptrans.supplierno <= '" . $_POST['ToCriteria'] . "'\n\t\t\tGROUP BY suppliers.supplierid,\n\t\t\t\t\tcurrencies.decimalplaces\n\t\t\tHAVING SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) > 0\n\t\t\tORDER BY suppliers.supplierid";
$SuppliersResult = DB_query($sql, $db);
$SupplierID = '';
$TotalPayments = 0;
$TotalAccumDiffOnExch = 0;
if (isset($_POST['PrintPDFAndProcess'])) {
$ProcessResult = DB_Txn_Begin($db);
}
while ($SuppliersToPay = DB_fetch_array($SuppliersResult)) {
$CurrDecimalPlaces = $SuppliersToPay['currdecimalplaces'];
$sql = "SELECT suppliers.supplierid,\n\t\t\t\t\t\tsuppliers.suppname,\n\t\t\t\t\t\tsystypes.typename,\n\t\t\t\t\t\tpaymentterms.terms,\n\t\t\t\t\t\tsupptrans.suppreference,\n\t\t\t\t\t\tsupptrans.trandate,\n\t\t\t\t\t\tsupptrans.rate,\n\t\t\t\t\t\tsupptrans.transno,\n\t\t\t\t\t\tsupptrans.type,\n\t\t\t\t\t\t(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,\n\t\t\t\t\t\t(supptrans.ovamount + supptrans.ovgst ) AS trantotal,\n\t\t\t\t\t\tsupptrans.diffonexch,\n\t\t\t\t\t\tsupptrans.id\n\t\t\t\tFROM suppliers INNER JOIN paymentterms\n\t\t\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t\t\t\tINNER JOIN supptrans\n\t\t\t\tON suppliers.supplierid = supptrans.supplierno\n\t\t\t\tINNER JOIN systypes\n\t\t\t\tON systypes.typeid = supptrans.type\n\t\t\t\tWHERE supptrans.supplierno = '" . $SuppliersToPay['supplierid'] . "'\n\t\t\t\tAND supptrans.ovamount + supptrans.ovgst - supptrans.alloc !=0\n\t\t\t\tAND supptrans.duedate <='" . FormatDateForSQL($_POST['AmountsDueBy']) . "'\n\t\t\t\tAND supptrans.hold = 0\n\t\t\t\tAND suppliers.currcode = '" . $_POST['Currency'] . "'\n\t\t\t\tAND supptrans.supplierno >= '" . $_POST['FromCriteria'] . "'\n\t\t\t\tAND supptrans.supplierno <= '" . $_POST['ToCriteria'] . "'\n\t\t\t\tORDER BY supptrans.supplierno,\n\t\t\t\t\tsupptrans.type,\n\t\t\t\t\tsupptrans.transno";
$TransResult = DB_query($sql, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$Title = _('Payment Run - Problem Report');
include 'includes/header.inc';
prnMsg(_('The details of supplier invoices due could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . ' ' . $sql;
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($TransResult) == 0) {
include 'includes/header.inc';
prnMsg(_('There are no outstanding supplier invoices to pay'), 'info');
function ModifySalesOrderLine($OrderLine, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($OrderLine as $key => $value) {
$OrderLine[$key] = DB_escape_string($value);
}
$Errors = VerifyOrderHeaderExists($OrderLine['orderno'], sizeof($Errors), $Errors, $db);
$Errors = VerifyStockCodeExists($OrderLine['stkcode'], sizeof($Errors), $Errors, $db);
if (isset($OrderLine['unitprice'])) {
$Errors = VerifyUnitPrice($OrderLine['unitprice'], sizeof($Errors), $Errors);
}
if (isset($OrderLine['quantity'])) {
$Errors = VerifyQuantity($OrderLine['quantity'], sizeof($Errors), $Errors);
}
if (isset($OrderLine['discountpercent'])) {
//$OrderLine['discountpercent'] = $OrderLine['discountpercent'] * 100;
$Errors = VerifyDiscountPercent($OrderLine['discountpercent'], sizeof($Errors), $Errors);
$OrderLine['discountpercent'] = $OrderLine['discountpercent'] / 100;
}
if (isset($OrderLine['narrative'])) {
$Errors = VerifyNarrative($OrderLine['narrative'], sizeof($Errors), $Errors);
}
if (isset($OrderLine['itemdue'])) {
$Errors = VerifyItemDueDate($OrderLine['itemdue'], sizeof($Errors), $Errors);
}
if (isset($OrderLine['poline'])) {
$Errors = VerifyPOLine($OrderLine['poline'], sizeof($Errors), $Errors);
}
$sql = 'UPDATE salesorderdetails SET ';
foreach ($OrderLine as $key => $value) {
if ($key == 'actualdispatchdate') {
$value = FormatDateWithTimeForSQL($value);
} elseif ($key == 'itemdue') {
$value = FormatDateForSQL($value);
}
$sql .= $key . '="' . $value . '", ';
}
//$sql = substr($sql,0,-2).' WHERE orderno="'.$OrderLine['orderno'].'" and
// " orderlineno='.$OrderLine['orderlineno'];
$sql = substr($sql, 0, -2) . ' WHERE orderno="' . $OrderLine['orderno'] . '" and stkcode="' . $OrderLine['stkcode'] . '"';
//echo $sql;
//exit;
if (sizeof($Errors) == 0) {
$result = api_DB_Query($sql, $db);
echo DB_error_no($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
include 'includes/footer.inc';
exit;
}
$SQL = "SELECT debtorsmaster.name,\n\t\t\t\tdebtorsmaster.salestype\n\t\t\t\tFROM debtorsmaster\n\t\t\t\tWHERE debtorno = '" . $_SESSION['CustomerID'] . "'";
$CustNameResult = DB_query($SQL, $db);
$CustNameRow = DB_fetch_row($CustNameResult);
$CustomerName = $CustNameRow[0];
$SalesType = $CustNameRow[1];
$SQL = "SELECT prices.typeabbrev,\n \t\t\t\t\t\tprices.stockid,\n \t\t\t\t\t\tstockmaster.description,\n \t\t\t\t\t\tstockmaster.longdescription,\n \t\t\t\t\t\tprices.currabrev,\n \t\t\t\t\t\tprices.startdate,\n \t\t\t\t\t\tprices.enddate,\n \t\t\t\t\t\tprices.price,\n \t\t\t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost AS standardcost,\n \t\t\t\t\t\tstockmaster.categoryid,\n \t\t\t\t\t\tstockcategory.categorydescription,\n \t\t\t\t\t\tprices.debtorno,\n \t\t\t\t\t\tprices.branchcode,\n \t\t\t\t\t\tcustbranch.brname,\n \t\t\t\t\t\tcurrencies.decimalplaces\n\t\t\t\t\t\tFROM stockmaster INNER JOIN\tstockcategory\n\t\t\t\t\t\tON stockmaster.categoryid=stockcategory.categoryid\n\t\t\t\t\t\tINNER JOIN prices\n\t\t\t\t\t\tON stockmaster.stockid=prices.stockid\n\t\t\t\t\t\tINNER JOIN currencies\n\t\t\t\t\t\tON prices.currabrev=currencies.currabrev\n LEFT JOIN custbranch\n\t\t\t\t\t\tON prices.debtorno=custbranch.debtorno\n\t\t\t\t\t\tAND prices.branchcode=custbranch.branchcode\n\t\t\t\t\t\tWHERE prices.typeabbrev = '" . $SalesType . "'\n\t\t\t\t\t\tAND stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n\t\t\t\t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n\t\t\t\t\t\tAND prices.debtorno='" . $_SESSION['CustomerID'] . "'\n\t\t\t\t\t\tAND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'\n\t\t\t\t\t\tAND (prices.enddate='0000-00-00' OR prices.enddate >'" . FormatDateForSQL($_POST['EffectiveDate']) . "')\n\t\t\t\t\t\tORDER BY prices.currabrev,\n\t\t\t\t\t\t\tstockmaster.categoryid,\n\t\t\t\t\t\t\tstockmaster.stockid,\n\t\t\t\t\t\t\tprices.startdate";
} else {
/* the sales type list only */
$SQL = "SELECT sales_type FROM salestypes WHERE typeabbrev='" . $_POST['SalesType'] . "'";
$SalesTypeResult = DB_query($SQL, $db);
$SalesTypeRow = DB_fetch_row($SalesTypeResult);
$SalesTypeName = $SalesTypeRow[0];
$SQL = "SELECT prices.typeabbrev,\n \t\t\t\tprices.stockid,\n \t\t\t\tprices.startdate,\n \t\t\t\tprices.enddate,\n \t\t\t\tstockmaster.description,\n \t\t\t\tstockmaster.longdescription,\n \t\t\t\tprices.currabrev,\n \t\t\t\tprices.price,\n \t\t\t\tstockmaster.materialcost+stockmaster.labourcost+stockmaster.overheadcost as standardcost,\n \t\t\t\tstockmaster.categoryid,\n \t\t\t\tstockcategory.categorydescription,\n \t\t\t\tcurrencies.decimalplaces\n\t\t\t\tFROM stockmaster INNER JOIN\tstockcategory\n\t \t\t\t ON stockmaster.categoryid=stockcategory.categoryid\n\t\t\t\tINNER JOIN prices\n \t\t\t\tON stockmaster.stockid=prices.stockid\n\t\t\t\tINNER JOIN currencies\n\t\t\t\t\tON prices.currabrev=currencies.currabrev\n WHERE stockmaster.categoryid >= '" . $_POST['FromCriteria'] . "'\n \t\t\tAND stockmaster.categoryid <= '" . $_POST['ToCriteria'] . "'\n \t\t\tAND prices.typeabbrev='" . $_POST['SalesType'] . "'\n \t\t\tAND prices.startdate<='" . FormatDateForSQL($_POST['EffectiveDate']) . "'\n \t\t\tAND (prices.enddate='0000-00-00' OR prices.enddate>'" . FormatDateForSQL($_POST['EffectiveDate']) . "')\n \t\t\tAND prices.debtorno=''\n \t\t\tORDER BY prices.currabrev,\n \t\t\t\tstockmaster.categoryid,\n \t\t\t\tstockmaster.stockid,\n \t\t\t\tprices.startdate";
}
$PricesResult = DB_query($SQL, $db, '', '', false, false);
if (DB_error_no($db) != 0) {
$title = _('Price List') . ' - ' . _('Problem Report....');
include 'includes/header.inc';
prnMsg(_('The Price List could not be retrieved by the SQL because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $rootpath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
prnMsg(_('For debugging purposes the SQL used was:') . $SQL, 'error');
}
include 'includes/footer.inc';
exit;
}
if (DB_num_rows($PricesResult) == 0) {
$title = _('Print Price List Error');
$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;
// Set first date to valid manufacturing date
$calendarsql = "SELECT COUNT(*),cal2.calendardate\n\t\t\t\t\t FROM mrpcalendar\n\t\t\t\t\t\tLEFT JOIN mrpcalendar as cal2\n\t\t\t\t\t\t ON mrpcalendar.daynumber = cal2.daynumber\n\t\t\t\t\t WHERE mrpcalendar.calendardate = '" . $datearray[0] . "'\n\t\t\t\t\t\tAND cal2.manufacturingflag='1'\n\t\t\t\t\t\tGROUP BY cal2.calendardate";
$resultdate = DB_query($calendarsql, $db);
$InputError = true;
}
// end if negative would result
}
//loop around the autoissue requirements for the work order
}
if ($InputError == false) {
/************************ BEGIN SQL TRANSACTIONS ************************/
$Result = DB_Txn_Begin($db);
/*Now Get the next WOReceipt transaction type 26 - function in SQL_CommonFunctions*/
$WOReceiptNo = GetNextTransNo(26, $db);
$PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
if (!isset($_POST['ReceivedDate'])) {
$_POST['ReceivedDate'] = Date($_SESSION['DefaultDateFormat']);
}
$SQLReceivedDate = FormatDateForSQL($_POST['ReceivedDate']);
$StockGLCode = GetStockGLCode($_POST['StockID'], $db);
//Recalculate the standard for the item if there were no items previously received against the work order
if ($WORow['qtyrecd'] == 0) {
$CostResult = DB_query("SELECT SUM((materialcost+labourcost+overheadcost)*bom.quantity) AS cost\n\t\t\t\t\t\t\t\t\tFROM stockmaster INNER JOIN bom\n\t\t\t\t\t\t\t\t\tON stockmaster.stockid=bom.component\n\t\t\t\t\t\t\t\t\tWHERE bom.parent='" . $_POST['StockID'] . "'\n\t\t\t\t\t\t\t\t\tAND bom.loccode='" . $WORow['loccode'] . "'", $db);
$CostRow = DB_fetch_array($CostResult);
if (is_null($CostRow['cost']) or $CostRow['cost'] == 0) {
$Cost = 0;
} else {
$Cost = $CostRow['cost'];
}
//Need to refresh the worequirments with the bom components now incase they changed
$DelWORequirements = DB_query("DELETE FROM worequirements\n\t\t\t\t\t\t\t\t\t\t\tWHERE wo='" . $_POST['WO'] . "'\n\t\t\t\t\t\t\t\t\t\t\tAND parentstockid='" . $_POST['StockID'] . "'", $db);
//Recursively insert real component requirements
WoRealRequirements($db, $_POST['WO'], $WORow['loccode'], $_POST['StockID']);
//Need to check this against the current standard cost and do a cost update if necessary
}
}
/*loop through all line items of the order to ensure none have been invoiced since started looking at this order*/
DB_free_result($Result);
/*Now Get the next invoice number - function in SQL_CommonFunctions*/
$InvoiceNo = GetNextTransNo(10, $db);
$PeriodNo = GetPeriod($DefaultDispatchDate, $db);
/*Start an SQL transaction */
DB_Txn_Begin($db);
if ($DefaultShipVia != $_SESSION['Items']->ShipVia) {
$SQL = "UPDATE custbranch SET defaultshipvia ='" . $_SESSION['Items']->ShipVia . "' WHERE debtorno='" . $_SESSION['Items']->DebtorNo . "' AND branchcode='" . $_SESSION['Items']->Branch . "'";
$ErrMsg = _('Could not update the default shipping carrier for this branch because');
$DbgMsg = _('The SQL used to update the branch default carrier was');
$result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
}
$DefaultDispatchDate = FormatDateForSQL($DefaultDispatchDate);
/*Update order header for invoice charged on */
$SQL = "UPDATE salesorders SET comments = CONCAT(comments,' Inv ','" . $InvoiceNo . "') WHERE orderno= " . $_SESSION['ProcessingOrder'];
$ErrMsg = _('CRITICAL ERROR') . ' ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The sales order header could not be updated with the invoice number');
$DbgMsg = _('The following SQL to update the sales order was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
/*Now insert the DebtorTrans */
$SQL = "INSERT INTO debtortrans (\r\n\t\t\ttransno,\r\n\t\t\ttype,\r\n\t\t\tdebtorno,\r\n\t\t\tbranchcode,\r\n\t\t\ttrandate,\r\n\t\t\tprd,\r\n\t\t\treference,\r\n\t\t\ttpe,\r\n\t\t\torder_,\r\n\t\t\tovamount,\r\n\t\t\tovgst,\r\n\t\t\tovfreight,\r\n\t\t\trate,\r\n\t\t\tinvtext,\r\n\t\t\tshipvia,\r\n\t\t\tconsignment\r\n\t\t\t)\r\n\t\tVALUES (\r\n\t\t\t" . $InvoiceNo . ",\r\n\t\t\t10,\r\n\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t'" . $_SESSION['Items']->Branch . "',\r\n\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t" . $PeriodNo . ",\r\n\t\t\t'',\r\n\t\t\t'" . $_SESSION['Items']->DefaultSalesType . "',\r\n\t\t\t" . $_SESSION['ProcessingOrder'] . ",\r\n\t\t\t" . $_SESSION['Items']->total . ",\r\n\t\t\t" . $TaxTotal . ",\r\n\t\t\t" . $_POST['ChargeFreightCost'] . ",\r\n\t\t\t" . $_SESSION['CurrencyRate'] . ",\r\n\t\t\t'" . $_POST['InvoiceText'] . "',\r\n\t\t\t" . $_SESSION['Items']->ShipVia . ",\r\n\t\t\t'" . $_POST['Consignment'] . "'\r\n\t\t)";
$ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction record could not be inserted because');
$DbgMsg = _('The following SQL to insert the debtor transaction record was used');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$DebtorTransID = DB_Last_Insert_ID($db, 'debtortrans', 'id');
/* Insert the tax totals for each tax authority where tax was charged on the invoice */
foreach ($TaxTotals as $TaxAuthID => $TaxAmount) {
$SQL = 'INSERT INTO debtortranstaxes (debtortransid,
taxauthid,
if (DB_num_rows($CustomersResult) == 0) {
$Title = _('Customer List') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('This report has no output because there were no customers retrieved'), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
include 'includes/footer.inc';
exit;
}
include 'includes/PDFCustomerListPageHeader.inc';
$Area = '';
$SalesPerson = '';
while ($Customers = DB_fetch_array($CustomersResult, $db)) {
if ($_POST['Activity'] != 'All') {
/*Get the total turnover in local currency for the customer/branch
since the date entered */
$SQL = "SELECT SUM((ovamount+ovfreight+ovdiscount)/rate) AS turnover\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $Customers['debtorno'] . "'\n\t\t\t\t\tAND branchcode='" . $Customers['branchcode'] . "'\n\t\t\t\t\tAND (type=10 or type=11)\n\t\t\t\t\tAND trandate >='" . FormatDateForSQL($_POST['ActivitySince']) . "'";
$ActivityResult = DB_query($SQL, $db, _('Could not retrieve the activity of the branch because'), _('The failed SQL was'));
$ActivityRow = DB_fetch_row($ActivityResult);
$LocalCurrencyTurnover = $ActivityRow[0];
if ($_POST['Activity'] == 'GreaterThan') {
if ($LocalCurrencyTurnover > $_POST['ActivityAmount']) {
$PrintThisCustomer = true;
} else {
$PrintThisCustomer = false;
}
} elseif ($_POST['Activity'] == 'LessThan') {
if ($LocalCurrencyTurnover < $_POST['ActivityAmount']) {
$PrintThisCustomer = true;
} else {
$PrintThisCustomer = false;
}
