function InsertGLAccountGroup($AccountGroupDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($AccountGroupDetails as $key => $value) {
$AccountGroupDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyAccountGroup($AccountGroupDetails['groupname'], sizeof($Errors), $Errors, $db);
$Errors = VerifyAccountSectionExists($AccountGroupDetails['sectioninaccounts'], sizeof($Errors), $Errors, $db);
if (isset($AccountGroupDetails['pandl'])) {
$Errors = VerifyPandL($AccountGroupDetails['pandl'], sizeof($Errors), $Errors);
}
$Errors = VerifyParentGroupExists($AccountGroupDetails['parentgroupname'], sizeof($Errors), $Errors, $db);
$FieldNames = '';
$FieldValues = '';
foreach ($AccountGroupDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
if (sizeof($Errors) == 0) {
$sql = 'INSERT INTO accountgroups (' . substr($FieldNames, 0, -2) . ') ' . 'VALUES (' . substr($FieldValues, 0, -2) . ') ';
$result = DB_Query($sql, $db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function InsertSalesArea($AreaDetails, $User, $Password)
{
$Errors = array();
$db = db($User, $Password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyAreaCodeDoesntExist($AreaDetails['areacode'], 0, $Errors, $db);
if (sizeof($Errors > 0)) {
// return $Errors;
}
$FieldNames = '';
$FieldValues = '';
foreach ($AreaDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
$sql = 'INSERT INTO areas (' . mb_substr($FieldNames, 0, -2) . ")\n\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "') ";
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
if (DB_error_no() != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function InsertSalesType($SalesTypeDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$FieldNames = '';
$FieldValues = '';
foreach ($SalesTypeDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
$sql = 'INSERT INTO salestypes (' . substr($FieldNames, 0, -2) . ') ' . 'VALUES (' . substr($FieldValues, 0, -2) . ') ';
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function DB_query_oc($SQL, $ErrorMessage = '', $DebugMessage = '', $Transaction = false, $TrapErrors = true)
{
global $db_oc;
global $PathPrefix;
$Result = mysqli_query($db_oc, $SQL);
if ($DebugMessage == '') {
$DebugMessage = _('The SQL that failed was');
}
if (DB_error_no($db_oc) != 0 and $TrapErrors == true) {
if ($TrapErrors) {
require_once $PathPrefix . 'includes/header.inc';
}
prnMsg($ErrorMessage . '<br />' . DB_error_msg($db_oc), 'error', _('Database Error') . ' ' . DB_error_no($db_oc));
if ($Debug == 1) {
prnMsg($DebugMessage . '<br />' . $SQL . '<br />', 'error', _('Database SQL Failure'));
}
if ($Transaction) {
$SQL = 'rollback';
$Result = DB_query_oc($SQL);
if (DB_error_no() != 0) {
prnMsg(_('Error Rolling Back Transaction'), 'error', _('Database Rollback Error') . ' ' . DB_error_no($db_oc));
} else {
prnMsg(_('Rolling Back Transaction OK'), 'error', _('Database Rollback Due to Error Above'));
}
}
if ($TrapErrors) {
include $PathPrefix . 'includes/footer.inc';
exit;
}
}
return $Result;
}
function InsertGLAccount($AccountDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($AccountDetails as $key => $value) {
$AccountDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyAccountCode($AccountDetails['accountcode'], sizeof($Errors), $Errors, $db);
if (isset($AccountDetails['accountname'])) {
$Errors = VerifyAccountName($AccountDetails['accountname'], sizeof($Errors), $Errors);
}
$Errors = VerifyAccountGroupExists($AccountDetails['group_'], sizeof($Errors), $Errors, $db);
$FieldNames = '';
$FieldValues = '';
foreach ($AccountDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
if (sizeof($Errors) == 0) {
$sql = "INSERT INTO chartmaster (" . mb_substr($FieldNames, 0, -2) . ") " . "VALUES ('" . mb_substr($FieldValues, 0, -2) . "') ";
$result = DB_Query($sql, $db);
$sql = "INSERT INTO chartdetails (accountcode,\n\t\t\t\t\t\t\tperiod)\n\t\t\t\tSELECT " . $AccountDetails['accountcode'] . ",\n\t\t\t\t\tperiodno\n\t\t\t\tFROM periods";
$result = DB_query($sql, $db, '', '', '', false);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function InsertGLAccountSection($AccountSectionDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($AccountSectionDetails as $key => $value) {
$AccountSectionDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyAccountSection($AccountSectionDetails['sectionname'], sizeof($Errors), $Errors, $db);
if (isset($AccountSectionDetails['accountname'])) {
$Errors = VerifySectionName($AccountSectionDetails['sectionname'], sizeof($Errors), $Errors);
}
$FieldNames = '';
$FieldValues = '';
foreach ($AccountSectionDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
if (sizeof($Errors) == 0) {
$sql = "INSERT INTO accountsection ('" . mb_substr($FieldNames, 0, -2) . "')\n\t\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "')";
$result = DB_Query($sql, $db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function executeSQL($sql, $db, $TrapErrors = False)
{
global $SQLFile;
/* Run an sql statement and return an error code */
if (!isset($SQLFile)) {
$result = DB_query($sql, $db, '', '', false, $TrapErrors);
return DB_error_no($db);
} else {
fwrite($SQLFile, $sql . ";\n");
}
}
function executeSQL($sql, $db, $TrapErrors = False)
{
global $SQLFile;
/* Run an sql statement and return an error code */
if (!isset($SQLFile)) {
DB_IgnoreForeignKeys($db);
$result = DB_query($sql, $db, '', '', false, $TrapErrors);
$ErrorNumber = DB_error_no($db);
DB_ReinstateForeignKeys($db);
return $ErrorNumber;
} else {
fwrite($SQLFile, $sql . ";\n");
}
}
echo '<br />' . _('There are') . ' ' . $myrow[0] . ' ' . _('contracts referring to this branch/customer');
} else {
//check if this it the last customer branch - don't allow deletion of the last branch
$SQL = "SELECT COUNT(*) FROM custbranch WHERE debtorno='" . $DebtorNo . "'";
$result = DB_query($SQL);
$myrow = DB_fetch_row($result);
if ($myrow[0] == 1) {
prnMsg(_('Cannot delete this branch because it is the only branch defined for this customer.'), 'warn');
} else {
$SQL = "DELETE FROM custbranch WHERE branchcode='" . $SelectedBranch . "' AND debtorno='" . $DebtorNo . "'";
if ($_SESSION['SalesmanLogin'] != '') {
$SQL .= " AND custbranch.salesman='" . $_SESSION['SalesmanLogin'] . "'";
}
$ErrMsg = _('The branch record could not be deleted') . ' - ' . _('the SQL server returned the following message');
$result = DB_query($SQL, $ErrMsg);
if (DB_error_no() == 0) {
prnMsg(_('Branch Deleted'), 'success');
}
}
}
}
}
}
}
//end ifs to test if the branch can be deleted
}
}
if (!isset($SelectedBranch)) {
/* It could still be the second time the page has been run and a record has been selected for modification - SelectedBranch will exist because it was sent with the new call. If its the first time the page has been displayed with no parameters then none of the above are true and the list of branches will be displayed with links to delete or edit each. These will call the same page again and allow update/input or deletion of the records*/
$SQL = "SELECT debtorsmaster.name,\n\t\t\t\t\tcustbranch.branchcode,\n\t\t\t\t\tbrname,\n\t\t\t\t\tsalesman.salesmanname,\n\t\t\t\t\tareas.areadescription,\n\t\t\t\t\tcontactname,\n\t\t\t\t\tphoneno,\n\t\t\t\t\tfaxno,\n\t\t\t\t\tcustbranch.email,\n\t\t\t\t\ttaxgroups.taxgroupdescription,\n\t\t\t\t\tcustbranch.disabletrans\n\t\t\t\tFROM custbranch INNER JOIN debtorsmaster\n\t\t\t\tON custbranch.debtorno=debtorsmaster.debtorno\n\t\t\t\tINNER JOIN areas\n\t\t\t\tON custbranch.area=areas.areacode\n\t\t\t\tINNER JOIN salesman\n\t\t\t\tON custbranch.salesman=salesman.salesmancode\n\t\t\t\tINNER JOIN taxgroups\n\t\t\t\tON custbranch.taxgroupid=taxgroups.taxgroupid\n\t\t\t\tWHERE custbranch.debtorno = '" . $DebtorNo . "'";
if ($_SESSION['SalesmanLogin'] != '') {
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;
}
function StockAdjustment($StockID, $Location, $Quantity, $TranDate, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyStockCodeExists($StockID, sizeof($Errors), $Errors, $db);
$balances = GetStockBalance($StockID, $user, $password);
$balance = 0;
for ($i = 0; $i < sizeof($balances); $i++) {
$balance = $balance + $balances[$i]['quantity'];
}
$newqoh = $Quantity + $balance;
$itemdetails = GetStockItem($StockID, $user, $password);
$adjglact = GetCategoryGLCode($itemdetails[1]['categoryid'], 'adjglact', $db);
$stockact = GetCategoryGLCode($itemdetails[1]['categoryid'], 'stockact', $db);
$stockmovesql = "INSERT INTO stockmoves (stockid,\n type,\n transno,\n loccode,\n trandate,\n prd,\n reference,\n qty,\n newqoh)\n VALUES ('" . $StockID . "',\n '17',\n '" . GetNextTransactionNo(17, $db) . "',\n '" . $Location . "',\n '" . $TranDate . "',\n '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n 'api adjustment',\n '" . $Quantity . "',\n '" . $newqoh . "')";
$locstocksql = 'UPDATE locstock SET quantity = quantity + ' . $Quantity . "\n WHERE loccode='" . $Location . "'\n AND stockid='" . $StockID . "'";
$glupdatesql1 = "INSERT INTO gltrans (type,\n typeno,\n trandate,\n periodno,\n account,\n amount,\n narrative)\n VALUES ('17',\n '" . GetNextTransactionNo(17, $db) . "',\n '" . $TranDate . "',\n '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n '" . $adjglact . "',\n '" . $itemdetails['materialcost'] * -$Quantity . "',\n '" . $StockID . ' x ' . $Quantity . ' @ ' . $itemdetails['materialcost'] . "')";
$glupdatesql2 = "INSERT INTO gltrans (type,\n typeno,\n trandate,\n periodno,\n account,\n amount,\n narrative)\n\t\t\t\t\t\tVALUES ('17',\n '" . GetNextTransactionNo(17, $db) . "',\n '" . $TranDate . "',\n '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n '" . $stockact . "',\n '" . $itemdetails['materialcost'] * $Quantity . "',\n '" . $StockID . ' x ' . $Quantity . ' @ ' . $itemdetails['materialcost'] . "')";
$systypessql = "UPDATE systypes set typeno='" . GetNextTransactionNo(17, $db) . "' where typeid='17'";
DB_Txn_Begin($db);
DB_query($stockmovesql, $db);
DB_query($locstocksql, $db);
DB_query($glupdatesql1, $db);
DB_query($glupdatesql2, $db);
DB_query($systypessql, $db);
DB_Txn_Commit($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
return $Errors;
} else {
return 0;
}
}
function ModifyBranch($BranchDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($BranchDetails as $key => $value) {
$BranchDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyBranchNoExists($BranchDetails['debtorno'], $BranchDetails['branchcode'], sizeof($Errors), $Errors, $db);
$Errors = VerifyBranchName($BranchDetails['brname'], sizeof($Errors), $Errors, $db);
if (isset($BranchDetails['address1'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address1'], 40, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['address2'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address2'], 40, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['address3'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address3'], 40, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['address4'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address4'], 50, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['address5'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address5'], 20, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['address6'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['address6'], 15, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['lat'])) {
$Errors = VerifyLatitude($BranchDetails['lat'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['lng'])) {
$Errors = VerifyLongitude($BranchDetails['lng'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['estdeliverydays'])) {
$Errors = VerifyEstDeliveryDays($BranchDetails['estdeliverydays'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['area'])) {
$Errors = VerifyAreaCode($BranchDetails['area'], sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['salesman'])) {
$Errors = VerifySalesmanCode($BranchDetails['salesman'], sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['fwddate'])) {
$Errors = VerifyFwdDate($BranchDetails['fwddate'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['phoneno'])) {
$Errors = VerifyPhoneNumber($BranchDetails['phoneno'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['faxno'])) {
$Errors = VerifyFaxNumber($BranchDetails['faxno'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['contactname'])) {
$Errors = VerifyContactName($BranchDetails['contactname'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['email'])) {
$Errors = VerifyEmailAddress($BranchDetails['email'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['defaultlocation'])) {
$Errors = VerifyDefaultLocation($BranchDetails['defaultlocation'], sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['taxgroupid'])) {
$Errors = VerifyTaxGroupId($BranchDetails['taxgroupid'], sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['defaultshipvia'])) {
$Errors = VerifyDefaultShipVia($BranchDetails['defaultshipvia'], sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['deliverblind'])) {
$Errors = VerifyDeliverBlind($BranchDetails['deliverblind'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['disabletrans'])) {
$Errors = VerifyDisableTrans($BranchDetails['disabletrans'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['brpostaddr1'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr1'], 40, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['brpostaddr2'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr2'], 40, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['brpostaddr3'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr3'], 30, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['brpostaddr4'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr4'], 20, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['brpostaddr5'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr5'], 20, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['brpostaddr6'])) {
$Errors = VerifyBranchAddressLine($BranchDetails['brpostaddr6'], 15, sizeof($Errors), $Errors, $db);
}
if (isset($BranchDetails['specialinstructions'])) {
$Errors = VerifySpecialInstructions($BranchDetails['specialinstructions'], sizeof($Errors), $Errors);
}
if (isset($BranchDetails['custbranchcode'])) {
$Errors = VerifyCustBranchCode($BranchDetails['custbranchcode'], sizeof($Errors), $Errors);
}
$FieldNames = '';
$FieldValues = '';
foreach ($BranchDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
$sql = 'UPDATE custbranch SET ';
foreach ($BranchDetails as $key => $value) {
$sql .= $key . '="' . $value . '", ';
}
$sql = substr($sql, 0, -2) . ' WHERE debtorno="' . $BranchDetails['debtorno'] . '" and
branchcode="' . $BranchDetails['branchcode'] . '"';
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
echo DB_error_no($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function ModifyStockCategory($CategoryDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($CategoryDetails as $key => $value) {
$CategoryDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyStockCategoryExists($CategoryDetails['categoryid'], sizeof($Errors), $Errors, $db);
$Errors = VerifyCategoryID($CategoryDetails['categoryid'], sizeof($Errors), $Errors);
$Errors = VerifyCategoryDescription($CategoryDetails['categorydescription'], sizeof($Errors), $Errors);
$Errors = VerifyStockType($CategoryDetails['stocktype'], sizeof($Errors), $Errors);
$Errors = VerifyAccountCodeExists($CategoryDetails['stockact'], sizeof($Errors), $Errors, $db);
$Errors = VerifyAccountCodeExists($CategoryDetails['adjglact'], sizeof($Errors), $Errors, $db);
$Errors = VerifyAccountCodeExists($CategoryDetails['purchpricevaract'], sizeof($Errors), $Errors, $db);
$Errors = VerifyAccountCodeExists($CategoryDetails['materialuseagevarac'], sizeof($Errors), $Errors, $db);
$Errors = VerifyAccountCodeExists($CategoryDetails['wipact'], sizeof($Errors), $Errors, $db);
$FieldNames = '';
$FieldValues = '';
foreach ($CategoryDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
$sql = "UPDATE stockcategory SET ";
foreach ($CategoryDetails as $key => $value) {
$sql .= $key . "='" . $value . "', ";
}
$sql = mb_substr($sql, 0, -2) . " WHERE categoryid='" . $CategoryDetails['categoryid'] . "'";
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
echo DB_error_no($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function StockAdjustment($StockID, $Location, $Quantity, $TranDate, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyStockCodeExists($StockID, sizeof($Errors), $Errors, $db);
$balances = GetStockBalance($StockID, $user, $password);
$balance = 0;
for ($i = 0; $i < sizeof($balances); $i++) {
$balance = $balance + $balances[$i]['quantity'];
}
$newqoh = $Quantity + $balance;
$itemdetails = GetStockItem($StockID, $user, $password);
$adjglact = GetCategoryGLCode($itemdetails['categoryid'], 'adjglact', $db);
$stockact = GetCategoryGLCode($itemdetails['categoryid'], 'stockact', $db);
$stockmovesql = 'INSERT INTO stockmoves (stockid, type, transno, loccode, trandate, prd, reference, qty, newqoh)
VALUES ("' . $StockID . '", 17,' . GetNextTransactionNo(17, $db) . ',"' . $Location . '","' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',"api adjustment",' . $Quantity . ',' . $newqoh . ')';
$locstocksql = 'UPDATE locstock SET quantity = quantity + ' . $Quantity . ' WHERE loccode="' . $Location . '" AND stockid="' . $StockID . '"';
$glupdatesql1 = 'INSERT INTO gltrans (type, typeno, trandate, periodno, account, amount, narrative)
VALUES (17,' . GetNextTransactionNo(17, $db) . ',"' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',' . $adjglact . ',' . $itemdetails['materialcost'] * -$Quantity . ',"' . $StockID . ' x ' . $Quantity . ' @ ' . $itemdetails['materialcost'] . '")';
$glupdatesql2 = 'INSERT INTO gltrans (type, typeno, trandate, periodno, account, amount, narrative)
VALUES (17,' . GetNextTransactionNo(17, $db) . ',"' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',' . $stockact . ',' . $itemdetails['materialcost'] * $Quantity . ',"' . $StockID . ' x ' . $Quantity . ' @ ' . $itemdetails['materialcost'] . '")';
$systypessql = 'UPDATE systypes set typeno=' . GetNextTransactionNo(17, $db) . ' where typeid=17';
DB_Txn_Begin($db);
DB_query($stockmovesql, $db);
DB_query($locstocksql, $db);
DB_query($glupdatesql1, $db);
DB_query($glupdatesql2, $db);
DB_query($systypessql, $db);
DB_Txn_Commit($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
return $Errors;
} else {
return 0;
}
}
break;
case 1050:
echo '<tr><td>' . $comment . '</td><td style="background-color:yellow">' . _('Note') . ' - ' . _('Table has already been created') . '</td></tr>';
break;
case 1060:
echo '<tr><td>' . $comment . '</td><td style="background-color:yellow">' . _('Note') . ' - ' . _('Column has already been created') . '</td></tr>';
break;
case 1061:
echo '<tr><td>' . $comment . '</td><td style="background-color:yellow">' . _('Note') . ' - ' . _('Index already exists') . '</td></tr>';
break;
case 1062:
echo '<tr><td>' . $comment . '</td><td style="background-color:yellow">' . _('Note') . ' - ' . _('Entry has already been done') . '</td></tr>';
break;
case 1068:
echo '<tr><td>' . $comment . '</td><td style="background-color:yellow">' . _('Note') . ' - ' . _('Primary key already exists') . '</td></tr>';
break;
default:
echo '<tr><td>' . $comment . '</td><td style="background-color:red">' . _('Failure') . ' - ' . _('Error number') . ' - ' . DB_error_no() . '</td></tr>';
break;
}
unset($sql);
}
}
//end if its a valid sql line not a comment
}
//end of for loop around the lines of the sql script
echo '</table>';
/*Now run the data conversions required. */
}
/*Dont do upgrade */
include 'includes/footer.inc';
function ModifyPurchData($PurchDataDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($PurchDataDetails as $key => $value) {
$PurchDataDetails[$key] = DB_escape_string($value);
}
$Errors = VerifyPurchDataLineExists($PurchDataDetails['supplierno'], $PurchDataDetails['stockid'], sizeof($Errors), $Errors, $db);
$Errors = VerifyStockCodeExists($PurchDataDetails['stockid'], sizeof($Errors), $Errors, $db);
$Errors = VerifySupplierNoExists($PurchDataDetails['supplierno'], sizeof($Errors), $Errors, $db);
if (isset($StockItemDetails['price'])) {
$Errors = VerifyUnitPrice($PurchDataDetails['price'], sizeof($Errors), $Errors);
}
if (isset($StockItemDetails['suppliersuom'])) {
$Errors = VerifySuppliersUOM($PurchDataDetails['suppliersuom'], sizeof($Errors), $Errors);
}
if (isset($StockItemDetails['conversionfactor'])) {
$Errors = VerifyConversionFactor($PurchDataDetails['conversionfactor'], sizeof($Errors), $Errors);
}
if (isset($StockItemDetails['supplierdescription'])) {
$Errors = VerifySupplierDescription($PurchDataDetails['supplierdescription'], sizeof($Errors), $Errors);
}
if (isset($StockItemDetails['leadtime'])) {
$Errors = VerifyLeadTime($PurchDataDetails['leadtime'], sizeof($Errors), $Errors);
}
if (isset($StockItemDetails['preferred'])) {
$Errors = VerifyPreferredFlag($PurchDataDetails['preferred'], sizeof($Errors), $Errors);
}
$sql = "UPDATE purchdata SET ";
foreach ($PurchDataDetails as $key => $value) {
$sql .= $key . "='" . $value . "', ";
}
$sql = mb_substr($sql, 0, -2) . " WHERE stockid='" . $PurchDataDetails['stockid'] . "'\n\t\t\t\t\t\t\t\tAND supplierno='" . $PurchDataDetails['supplierno'] . "'";
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
echo DB_error_no($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
$result = DB_query($sql, $db);
$sql = 'CREATE TEMPORARY TABLE passbom (
part char(20),
sortpart text)';
$result = DB_query($sql, $db);
$sql = "INSERT INTO passbom (part, sortpart)\n\t\t\t\t\t SELECT bom.component AS part,\n\t\t\t\t\t\t\t CONCAT(passbom2.sortpart,bom.component) AS sortpart\n\t\t\t\t\t FROM bom,passbom2 \n\t\t\t\t\t WHERE bom.parent = passbom2.part\n\t\t\t\t\t\tAND bom.effectiveto >= NOW() AND bom.effectiveafter <= NOW()";
$result = DB_query($sql, $db);
$sql = 'SELECT COUNT(*) FROM bom,passbom WHERE bom.parent = passbom.part';
$result = DB_query($sql, $db);
$myrow = DB_fetch_row($result);
$componentctr = $myrow[0];
}
// End of while $componentctr > 0
}
// End of if $_POST['Levels']
if (DB_error_no($db) != 0) {
$title = _('Indented BOM Listing') . ' - ' . _('Problem Report');
include 'includes/header.inc';
prnMsg(_('The Indented BOM Listing could not be retrieved by the SQL because') . ' ' . DB_error_msg($db), 'error');
echo "<br><a href='" . $rootpath . '/index.php?' . SID . "'>" . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo "<br>{$sql}";
}
include 'includes/footer.inc';
exit;
}
$sql = 'SELECT stockmaster.stockid,
stockmaster.description
FROM stockmaster
WHERE stockid = ' . "'" . $_POST['Part'] . "'";
$result = DB_query($sql, $db);
function GetCustomerBranch($DebtorNumber, $BranchCode, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyBranchNoExists($DebtorNumber, $BranchCode, sizeof($Errors), $Errors, $db);
if (sizeof($Errors) != 0) {
return $Errors;
}
$sql = "SELECT * FROM custbranch\n WHERE debtorno='" . $DebtorNumber . "'\n AND branchcode='" . $BranchCode . "'";
$result = api_DB_Query($sql, $db);
if (DB_error_no() != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
if (DB_num_rows($result) > 0) {
$Errors += DB_fetch_array($result);
}
}
return $Errors;
}
function InsertSalesCredit($CreditDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($CreditDetails as $key => $value) {
$CreditDetails[$key] = DB_escape_string($value);
}
$PartCode = $CreditDetails['partcode'];
$Errors = VerifyStockCodeExists($PartCode, sizeof($Errors), $Errors, $db);
unset($CreditDetails['partcode']);
$SalesArea = $CreditDetails['salesarea'];
unset($CreditDetails['salesarea']);
$CreditDetails['transno'] = GetNextTransactionNo(11, $db);
$CreditDetails['type'] = 10;
$Errors = VerifyDebtorExists($CreditDetails['debtorno'], sizeof($Errors), $Errors, $db);
$Errors = VerifyBranchNoExists($CreditDetails['debtorno'], $CreditDetails['branchcode'], sizeof($Errors), $Errors, $db);
$Errors = VerifyTransNO($CreditDetails['transno'], 10, sizeof($Errors), $Errors, $db);
$Errors = VerifyTransactionDate($CreditDetails['trandate'], sizeof($Errors), $Errors, $db);
if (isset($CreditDetails['settled'])) {
$Errors = VerifySettled($CreditDetails['settled'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['reference'])) {
$Errors = VerifyReference($CreditDetails['reference'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['tpe'])) {
$Errors = VerifyTpe($CreditDetails['tpe'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['order_'])) {
$Errors = VerifyOrderNumber($CreditDetails['order_'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['rate'])) {
$Errors = VerifyExchangeRate($CreditDetails['rate'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['ovamount'])) {
$Errors = VerifyOVAmount($CreditDetails['ovamount'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['ovgst'])) {
$Errors = VerifyOVGst($CreditDetails['ovgst'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['ovfreight'])) {
$Errors = VerifyOVFreight($CreditDetails['ovfreight'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['ovdiscount'])) {
$Errors = VerifyOVDiscount($CreditDetails['ovdiscount'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['diffonexch'])) {
$Errors = VerifyDiffOnExchange($CreditDetails['diffonexch'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['alloc'])) {
$Errors = VerifyAllocated($CreditDetails['alloc'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['invtext'])) {
$Errors = VerifyInvoiceText($CreditDetails['invtext'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['shipvia'])) {
$Errors = VerifyShipVia($CreditDetails['shipvia'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['edisent'])) {
$Errors = VerifyEdiSent($CreditDetails['edisent'], sizeof($Errors), $Errors);
}
if (isset($CreditDetails['consignment'])) {
$Errors = VerifyConsignment($CreditDetails['consignment'], sizeof($Errors), $Errors);
}
$FieldNames = '';
$FieldValues = '';
$CreditDetails['trandate'] = ConvertToSQLDate($CreditDetails['trandate']);
$CreditDetails['prd'] = GetPeriodFromTransactionDate($CreditDetails['trandate'], sizeof($Errors), $Errors, $db);
foreach ($CreditDetails as $key => $value) {
$FieldNames .= $key . ', ';
$FieldValues .= '"' . $value . '", ';
}
if (sizeof($Errors) == 0) {
$result = DB_Txn_Begin($db);
$sql = "INSERT INTO debtortrans (" . mb_substr($FieldNames, 0, -2) . ")\n\t\t\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "') ";
$result = DB_Query($sql, $db);
$sql = "UPDATE systypes SET typeno='" . GetNextTransactionNo(11, $db) . "' WHERE typeid=10";
$result = DB_Query($sql, $db);
$SalesGLCode = GetSalesGLCode($SalesArea, $PartCode, $db);
$DebtorsGLCode = GetDebtorsGLCode($db);
$sql = "INSERT INTO gltrans VALUES(null,\n\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t'" . GetNextTransactionNo(11, $db) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['prd'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $DebtorsGLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . _('Invoice for') . ' - ' . $CreditDetails['debtorno'] . ' ' . -'Total' . ' - ' . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['jobref'] . "')";
$result = DB_Query($sql, $db);
$sql = "INSERT INTO gltrans VALUES(null,\n\t\t\t\t\t\t\t\t\t\t\t10,\n\t\t\t\t\t\t\t\t\t\t\t'" . GetNextTransactionNo(11, $db) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['trandate'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['prd'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SalesGLCode . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . _('Invoice for') . ' - ' . $CreditDetails['debtorno'] . ' ' . _('Total') . ' - ' . $CreditDetails['ovamount'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -intval($CreditDetails['ovamount']) . "',\n\t\t\t\t\t\t\t\t\t\t\t0,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditDetails['jobref'] . "')";
$result = DB_Query($sql, $db);
$result = DB_Txn_Commit($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
return $Errors;
} else {
return $Errors;
}
}
function executeSQL($sql, $db, $TrapErrors = False)
{
/* Run an sql statement and return an error code */
DB_IgnoreForeignKeys($db);
$result = DB_query($sql, $db, '', '', false, $TrapErrors);
$ErrorNumber = DB_error_no($db);
DB_ReinstateForeignKeys($db);
return $ErrorNumber;
}
function ModifyLocation($Location, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($Location as $key => $value) {
$Location[$key] = DB_escape_string($value);
}
$Errors = VerifyLocationExists($Location['loccode'], sizeof($Errors), $Errors, $db);
$Errors = VerifyLocationName($Location['locationname'], sizeof($Errors), $Errors, $db);
$Errors = VerifyTaxProvinceId($Location['taxprovinceid'], sizeof($Errors), $Errors, $db);
if (isset($Location['deladd1'])) {
$Errors = VerifyAddressLine($Location['deladd1'], 40, sizeof($Errors), $Errors);
}
if (isset($Location['deladd2'])) {
$Errors = VerifyAddressLine($Location['deladd2'], 40, sizeof($Errors), $Errors);
}
if (isset($Location['deladd3'])) {
$Errors = VerifyAddressLine($Location['deladd3'], 40, sizeof($Errors), $Errors);
}
if (isset($Location['deladd4'])) {
$Errors = VerifyAddressLine($Location['deladd4'], 40, sizeof($Errors), $Errors);
}
if (isset($Location['deladd5'])) {
$Errors = VerifyAddressLine($Location['deladd5'], 20, sizeof($Errors), $Errors);
}
if (isset($Location['deladd6'])) {
$Errors = VerifyAddressLine($Location['deladd6'], 15, sizeof($Errors), $Errors);
}
if (isset($Location['tel'])) {
$Errors = VerifyPhoneNumber($Location['tel'], sizeof($Errors), $Errors);
}
if (isset($Location['fax'])) {
$Errors = VerifyFaxNumber($Location['fax'], sizeof($Errors), $Errors);
}
if (isset($Location['email'])) {
$Errors = VerifyEmailAddress($Location['email'], sizeof($Errors), $Errors);
}
if (isset($Location['contact'])) {
$Errors = VerifyContactName($Location['contact'], sizeof($Errors), $Errors);
}
$sql = 'UPDATE locations SET ';
foreach ($Location as $key => $value) {
$sql .= $key . '="' . $value . '", ';
}
$sql = substr($sql, 0, -2) . ' WHERE loccode="' . $Location['loccode'] . '"';
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
break;
case 1050:
echo '<tr><td>' . $comment . '</td><td bgcolor="yellow">' . _('Note') . ' - ' . _('Table has already been created') . '</td></tr>';
break;
case 1060:
echo '<tr><td>' . $comment . '</td><td bgcolor="yellow">' . _('Note') . ' - ' . _('Column has already been created') . '</td></tr>';
break;
case 1061:
echo '<tr><td>' . $comment . '</td><td bgcolor="yellow">' . _('Note') . ' - ' . _('Index already exists') . '</td></tr>';
break;
case 1062:
echo '<tr><td>' . $comment . '</td><td bgcolor="yellow">' . _('Note') . ' - ' . _('Entry has already been done') . '</td></tr>';
break;
case 1068:
echo '<tr><td>' . $comment . '</td><td bgcolor="yellow">' . _('Note') . ' - ' . _('Primary key already exists') . '</td></tr>';
break;
default:
echo '<tr><td>' . $comment . '</td><td bgcolor="red">' . _('Failure') . ' - ' . _('Error number') . ' - ' . DB_error_no($db) . '</td></tr>';
break;
}
unset($sql);
}
}
//end if its a valid sql line not a comment
}
//end of for loop around the lines of the sql script
echo '</table>';
/*Now run the data conversions required. */
}
/*Dont do upgrade */
include 'includes/footer.inc';
function ModifyCustomer($CustomerDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($CustomerDetails as $key => $value) {
$CustomerDetails[$key] = DB_escape_string($value);
}
if (!isset($CustomerDetails['debtorno'])) {
$Errors[sizeof($Errors)] = NoDebtorNumber;
return $Errors;
}
$Errors = VerifyDebtorExists($CustomerDetails['debtorno'], sizeof($Errors), $Errors, $db);
if (in_array(DebtorDoesntExist, $Errors)) {
return $Errors;
}
if (isset($CustomerDetails['name'])) {
$Errors = VerifyDebtorName($CustomerDetails['name'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address1'])) {
$Errors = VerifyAddressLine($CustomerDetails['address1'], 40, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address2'])) {
$Errors = VerifyAddressLine($CustomerDetails['address2'], 40, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address3'])) {
$Errors = VerifyAddressLine($CustomerDetails['address3'], 40, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address4'])) {
$Errors = VerifyAddressLine($CustomerDetails['address4'], 50, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address5'])) {
$Errors = VerifyAddressLine($CustomerDetails['address5'], 20, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['address6'])) {
$Errors = VerifyAddressLine($CustomerDetails['address6'], 15, sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['currcode'])) {
$Errors = VerifyCurrencyCode($CustomerDetails['currcode'], sizeof($Errors), $Errors, $db);
}
if (isset($CustomerDetails['salestype'])) {
$Errors = VerifySalesType($CustomerDetails['salestype'], sizeof($Errors), $Errors, $db);
}
if (isset($CustomerDetails['clientsince'])) {
$Errors = VerifyClientSince($CustomerDetails['clientsince'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['holdreason'])) {
$Errors = VerifyHoldReason($CustomerDetails['holdreason'], sizeof($Errors), $Errors, $db);
}
if (isset($CustomerDetails['paymentterms'])) {
$Errors = VerifyPaymentTerms($CustomerDetails['paymentterms'], sizeof($Errors), $Errors, $db);
}
if (isset($CustomerDetails['discount'])) {
$Errors = VerifyDiscount($CustomerDetails['discount'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['pymtdiscount'])) {
$Errors = VerifyPymtDiscount($CustomerDetails['pymtdiscount'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['lastpaid'])) {
$Errors = VerifyLastPaid($CustomerDetails['lastpaid'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['lastpaiddate'])) {
$Errors = VerifyLastPaidDate($CustomerDetails['lastpaiddate'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['creditlimit'])) {
$Errors = VerifyCreditLimit($CustomerDetails['creditlimit'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['invaddrbranch'])) {
$Errors = VerifyInvAddrBranch($CustomerDetails['invaddrbranch'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['discountcode'])) {
$Errors = VerifyDiscountCode($CustomerDetails['discountcode'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['ediinvoices'])) {
$Errors = VerifyEDIInvoices($CustomerDetails['ediinvoices'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['ediorders'])) {
$Errors = VerifyEDIOrders($CustomerDetails['ediorders'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['edireference'])) {
$Errors = VerifyEDIReference($CustomerDetails['edireference'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['editransport'])) {
$Errors = VerifyEDITransport($CustomerDetails['editransport'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['ediserveruser'])) {
$Errors = VerifyEDIServerUser($CustomerDetails['ediserveruser'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['ediserverpwd'])) {
$Errors = VerifyEDIServerPassword($CustomerDetails['ediserverpwd'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['taxref'])) {
$Errors = VerifyTaxRef($CustomerDetails['taxref'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['customerpoline'])) {
$Errors = VerifyCustomerPOLine($CustomerDetails['customerpoline'], sizeof($Errors), $Errors);
}
if (isset($CustomerDetails['typeid'])) {
$Errors = VerifyCustomerType($CustomerDetails['typeid'], sizeof($Errors), $Errors, $db);
}
$sql = "UPDATE debtorsmaster SET ";
foreach ($CustomerDetails as $key => $value) {
$sql .= $key . '="' . $value . '", ';
}
$sql = mb_substr($sql, 0, -2) . " WHERE debtorno='" . $CustomerDetails['debtorno'] . "'";
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
function Save($db, $Update = '')
{
if ($Update == '') {
foreach ($this->LineItems as $LineItems) {
if ($LineItems->Deleted == False) {
$sql = "INSERT INTO offers (\n\t\t\t\t\t\t\tsupplierid,\n\t\t\t\t\t\t\ttenderid,\n\t\t\t\t\t\t\tstockid,\n\t\t\t\t\t\t\tquantity,\n\t\t\t\t\t\t\tuom,\n\t\t\t\t\t\t\tprice,\n\t\t\t\t\t\t\texpirydate,\n\t\t\t\t\t\t\tcurrcode)\n\t\t\t\t\t\tVALUES (\n\t\t\t\t\t\t\t'" . $this->SupplierID . "',\n\t\t\t\t\t\t\t'" . $this->TenderID . "',\n\t\t\t\t\t\t\t'" . $LineItems->StockID . "',\n\t\t\t\t\t\t\t'" . $LineItems->Quantity . "',\n\t\t\t\t\t\t\t'" . $LineItems->Units . "',\n\t\t\t\t\t\t\t'" . $LineItems->Price . "',\n\t\t\t\t\t\t\t'" . FormatDateForSQL($LineItems->ExpiryDate) . "',\n\t\t\t\t\t\t\t'" . $this->CurrCode . "'\n\t\t\t\t\t\t)";
$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 quantity='" . $LineItem->Quantity . "',\n\t\t\t\t\t\t\t\t\t\t\tprice='" . $LineItem->Price . "',\n\t\t\t\t\t\t\t\t\t\t\texpirydate='" . FormatDateForSQL($LineItem->ExpiryDate) . "'\n\t\t\t\t\t\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 suppliers offer could not be deleted on the database because');
$DbgMsg = _('The SQL statement used to delete 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 deleted in the database'), 'success');
}
}
}
}
}
prnMsg(_('The sales order demand quantities could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
// Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
if ($_POST['Location'] == 'All') {
$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem\n\t\t\t\tFROM salesorderdetails INNER JOIN bom\n\t\t\t\t\tON salesorderdetails.stkcode=bom.parent\n\t\t\t\t\tINNER JOIN\tstockmaster\n\t\t\t\t\tON stockmaster.stockid=bom.parent\n\t\t\t\t\tINNER JOIN salesorders\n\t\t\t\t\tON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\tWHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND bom.component='" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND stockmaster.mbflag='A'\n\t\t\t\tAND salesorderdetails.completed=0\n\t\t\t\tAND salesorders.quotation=0";
} else {
$SQL = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem\n\t\t\t\tFROM salesorderdetails INNER JOIN bom\n\t\t\t\t\tON salesorderdetails.stkcode=bom.parent\n\t\t\t\t\tINNER JOIN\tstockmaster\n\t\t\t\t\tON stockmaster.stockid=bom.parent\n\t\t\t\t\tINNER JOIN salesorders\n\t\t\t\t\tON salesorders.orderno = salesorderdetails.orderno\n\t\t\t\t\tINNER JOIN locationusers ON locationusers.loccode=salesorders.fromstkloc AND locationusers.userid='" . $_SESSION['UserID'] . "' AND locationusers.canview=1\n\t\t\t\tWHERE salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0\n\t\t\t\tAND bom.component='" . $InventoryPlan['stockid'] . "'\n\t\t\t\tAND stockmaster.stockid=bom.parent\n\t\t\t\tAND salesorders.fromstkloc ='" . $_POST['Location'] . "'\n\t\t\t\tAND stockmaster.mbflag='A'\n\t\t\t\tAND salesorderdetails.completed=0\n\t\t\t\tAND salesorders.quotation=0";
}
$BOMDemandResult = DB_query($SQL, '', '', false, false);
if (DB_error_no() != 0) {
$Title = _('Inventory Planning') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The sales order demand quantities from parent assemblies could not be retrieved by the SQL because') . ' - ' . DB_error_msg(), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . $SQL;
}
include 'includes/footer.inc';
exit;
}
// Get the QOO due to Purchase orders for all locations. Function defined in SQL_CommonFunctions.inc
// Get the QOO dues to Work Orders for all locations. Function defined in SQL_CommonFunctions.inc
if ($_POST['Location'] == 'All') {
$QOO = GetQuantityOnOrderDueToPurchaseOrders($InventoryPlan['stockid'], '');
$QOO += GetQuantityOnOrderDueToWorkOrders($InventoryPlan['stockid'], '');
function WorkOrderReceive($WONumber, $StockID, $Location, $Quantity, $TranDate, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
$Errors = VerifyStockCodeExists($StockID, sizeof($Errors), $Errors, $db);
$Errors = VerifyWorkOrderExists($WONumber, sizeof($Errors), $Errors, $db);
$Errors = VerifyStockLocation($Location, sizeof($Errors), $Errors, $db);
$Errors = VerifyReceivedQuantity($Quantity, sizeof($Errors), $Errors);
// $Errors = VerifyTransactionDate($TranDate, sizeof($Errors), $Errors);
if (sizeof($Errors) != 0) {
return $Errors;
}
$itemdetails = GetStockItem($StockID, $user, $password);
$balances = GetStockBalance($StockID, $user, $password);
$balance = 0;
for ($i = 0; $i < sizeof($balances); $i++) {
$balance = $balance + $balances[$i]['quantity'];
}
$newqoh = $Quantity + $balance;
$wipglact = GetCategoryGLCode($itemdetails['categoryid'], 'wipact', $db);
$stockact = GetCategoryGLCode($itemdetails['categoryid'], 'stockact', $db);
$costsql = 'SELECT costissued FROM workorders WHERE wo=' . $WONumber;
$costresult = DB_query($costsql);
$myrow = DB_fetch_row($costresult);
$cost = $myrow[0];
$TransactionNo = GetNextTransactionNo(26, $db);
$stockmovesql = 'INSERT INTO stockmoves (stockid, type, transno, loccode, trandate, prd, reference, qty, newqoh,
price, standardcost)
VALUES ("' . $StockID . '", 26,' . $TransactionNo . ',"' . $Location . '","' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',"' . $WONumber . '",' . $Quantity . ',' . $newqoh . ',' . $cost . ',' . $cost . ')';
$locstocksql = 'UPDATE locstock SET quantity = quantity + ' . $Quantity . ' WHERE loccode="' . $Location . '" AND stockid="' . $StockID . '"';
$glupdatesql1 = 'INSERT INTO gltrans (type, typeno, trandate, periodno, account, amount, narrative)
VALUES (26,' . $TransactionNo . ',"' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',' . $wipglact . ',' . $cost * $Quantity . ',"' . $StockID . ' x ' . $Quantity . ' @ ' . $cost . '")';
$glupdatesql2 = 'INSERT INTO gltrans (type, typeno, trandate, periodno, account, amount, narrative)
VALUES (26,' . $TransactionNo . ',"' . $TranDate . '",' . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . ',' . $stockact . ',' . $cost * -$Quantity . ',"' . $StockID . ' x ' . $Quantity . ' @ ' . $cost . '")';
$systypessql = 'UPDATE systypes set typeno=' . $TransactionNo . ' where typeid=26';
DB_Txn_Begin($db);
DB_query($stockmovesql, $db);
DB_query($locstocksql, $db);
DB_query($glupdatesql1, $db);
DB_query($glupdatesql2, $db);
DB_query($systypessql, $db);
DB_Txn_Commit($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
return $Errors;
}
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;
}
function ModifySupplier($SupplierDetails, $user, $password)
{
$Errors = array();
$db = db($user, $password);
if (gettype($db) == 'integer') {
$Errors[0] = NoAuthorisation;
return $Errors;
}
foreach ($SupplierDetails as $key => $value) {
$SupplierDetails[$key] = DB_escape_string($value);
}
$Errors = VerifySupplierNoExists($SupplierDetails['supplierid'], sizeof($Errors), $Errors, $db);
$Errors = VerifySupplierName($SupplierDetails['suppname'], sizeof($Errors), $Errors);
if (isset($SupplierDetails['address1'])) {
$Errors = VerifyAddressLine($SupplierDetails['address1'], 40, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['address2'])) {
$Errors = VerifyAddressLine($SupplierDetails['address2'], 40, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['address3'])) {
$Errors = VerifyAddressLine($SupplierDetails['address3'], 40, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['address4'])) {
$Errors = VerifyAddressLine($SupplierDetails['address4'], 50, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['address5'])) {
$Errors = VerifyAddressLine($SupplierDetails['address5'], 20, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['address6'])) {
$Errors = VerifyAddressLine($SupplierDetails['address6'], 15, sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['lat'])) {
$Errors = VerifyLatitude($SupplierDetails['lat'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['lng'])) {
$Errors = VerifyLongitude($SupplierDetails['lng'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['currcode'])) {
$Errors = VerifyCurrencyCode($SupplierDetails['currcode'], sizeof($Errors), $Errors, $db);
}
if (isset($SupplierDetails['suppliersince'])) {
$Errors = VerifySupplierSince($SupplierDetails['suppliersince'], sizeof($Errors), $Errors, $db);
}
if (isset($SupplierDetails['paymentterms'])) {
$Errors = VerifyPaymentTerms($SupplierDetails['paymentterms'], sizeof($Errors), $Errors, $db);
}
if (isset($SupplierDetails['lastpaid'])) {
$Errors = VerifyLastPaid($SupplierDetails['lastpaid'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['lastpaiddate'])) {
$Errors = VerifyLastPaidDate($SupplierDetails['lastpaiddate'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['bankact'])) {
$Errors = VerifyBankAccount($SupplierDetails['bankact'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['bankref'])) {
$Errors = VerifyBankRef($SupplierDetails['bankref'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['bankpartics'])) {
$Errors = VerifyBankPartics($SupplierDetails['bankpartics'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['remittance'])) {
$Errors = VerifyRemittance($SupplierDetails['remittance'], sizeof($Errors), $Errors);
}
if (isset($SupplierDetails['taxgroupid'])) {
$Errors = VerifyTaxGroupId($SupplierDetails['taxgroupid'], sizeof($Errors), $Errors, $db);
}
if (isset($SupplierDetails['factorcompanyid'])) {
$Errors = VerifyFactorCompany($SupplierDetails['factorcompanyid'], sizeof($Errors), $Errors, $db);
}
if (isset($CustomerDetails['taxref'])) {
$Errors = VerifyTaxRef($CustomerDetails['taxref'], sizeof($Errors), $Errors);
}
$sql = 'UPDATE suppliers SET ';
foreach ($SupplierDetails as $key => $value) {
$sql .= $key . '="' . $value . '", ';
}
$sql = mb_substr($sql, 0, -2) . " WHERE supplierid='" . $SupplierDetails['supplierid'] . "'";
if (sizeof($Errors) == 0) {
$result = DB_Query($sql, $db);
echo DB_error_no($db);
if (DB_error_no($db) != 0) {
$Errors[0] = DatabaseUpdateFailed;
} else {
$Errors[0] = 0;
}
}
return $Errors;
}
}
//What about cost data?
//get any existing cost data
$sql = "SELECT materialcost,\n labourcost,\n overheadcost,\n mbflag,\n sum(quantity) as totalqoh\n FROM stockmaster INNER JOIN locstock\n ON stockmaster.stockid=locstock.stockid\n WHERE stockmaster.stockid='" . $_POST['OldStockID'] . "'\n GROUP BY description,\n units,\n lastcost,\n actualcost,\n materialcost,\n labourcost,\n overheadcost,\n mbflag";
$ErrMsg = _('The entered item code does not exist');
$OldResult = DB_query($sql, $db, $ErrMsg);
$OldRow = DB_fetch_array($OldResult);
//now update cloned item costs
$Result = DB_Txn_Begin($db);
$SQL = "UPDATE stockmaster SET\tmaterialcost='" . $OldRow['materialcost'] . "',\n\t\t\t\t\t\t\t\t\t\tlabourcost ='" . $OldRow['labourcost'] . "',\n\t\t\t\t\t\t\t\t\t\toverheadcost ='" . $OldRow['overheadcost'] . "',\n\t\t\t\t\t\t\t\t\t\tlastcost ='" . $OldRow['lastcost'] . "',\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='" . $_POST['StockID'] . "'";
$ErrMsg = _('The cost details for the cloned stock item could not be updated because');
$DbgMsg = _('The SQL that failed was');
$Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
$Result = DB_Txn_Commit($db);
//finish up
if (DB_error_no($db) == 0) {
prnMsg(_('New Cloned Item') . ' ' . '<a href="SelectProduct.php?StockID=' . $_POST['StockID'] . '">' . $_POST['StockID'] . '</a> ' . _('has been added to the database') . '<br />' . _('We also attempted to setup item purchase data and pricing.'));
if ($NoPricingData == 1) {
prnMsg(_('There is no pricing data to clone. Use the following link to add pricing.'));
}
prnMsg('<br />' . '<a target="_blank" href="Prices.php?Item=' . $_POST['StockID'] . '">' . _('Review Item Prices') . '</a> ', 'success');
if ($NoPurchasingData == 1) {
prnMsg(_('There is no purchasing data to clone .Use the following link to add purchasing data.'));
}
prnMsg('<br />' . '<a target="_blank" href="PurchData.php?StockID=' . $_POST['StockID'] . '">' . _('Review Item Purchase Data.') . '</a> ', 'success') . prnMsg(_('Costing was updated for this cloned item.') . '<br />' . '<a target="_blank" href="StockCostUpdate.php?StockID=' . $_POST['StockID'] . '">' . _('Review Item Cost') . '</a>', 'success');
echo '<br />';
unset($_POST['Description']);
unset($_POST['LongDescription']);
unset($_POST['EOQ']);
// Leave Category ID set for ease of batch entry
//unset($_POST['CategoryID']);
$sql = "SELECT SUM(value) FROM shipmentcharges WHERE stockid='' AND shiptref =" . $_GET['SelectedShipment'];
$ErrMsg = _('Shipment') . ' ' . $_GET['SelectedShipment'] . ' ' . _('general costs cannot be retrieved from the database');
$GetShiptCostsResult = DB_query($sql, $db, $ErrMsg);
if (DB_num_rows($GetShiptCostsResult) == 0) {
echo '<BR>';
prnMsg(_('No General Cost Records exist for Shipment') . ' ' . $_GET['SelectedShipment'] . ' ' . _('in the database'), 'error');
include 'includes/footer.inc';
exit;
}
$myrow = DB_fetch_row($GetShiptCostsResult);
$TotalCostsToApportion = $myrow[0];
/*Now Get the total of stock items invoiced against the shipment */
$sql = "SELECT SUM(value) FROM shipmentcharges WHERE stockid<>'' AND shiptref =" . $_GET['SelectedShipment'];
$ErrMsg = _('Shipment') . ' ' . $_GET['SelectedShipment'] . ' ' . _('Item costs cannot be retrieved from the database');
$GetShiptCostsResult = DB_query($sql, $db);
if (DB_error_no($db) != 0 or DB_num_rows($GetShiptCostsResult) == 0) {
echo '<BR>';
prnMsg(_('No Item Cost Records exist for Shipment') . ' ' . $_GET['SelectedShipment'] . ' ' . _('in the database'), 'error');
include 'includes/footer.inc';
exit;
}
$myrow = DB_fetch_row($GetShiptCostsResult);
$TotalInvoiceValueOfShipment = $myrow[0];
/*Now get the lines on the shipment */
$LineItemsSQL = "SELECT purchorderdetails.itemcode,\n\t\t\tpurchorderdetails.itemdescription,\n\t\t\tSUM(purchorderdetails.qtyinvoiced) as totqtyinvoiced,\n\t\t\tSUM(purchorderdetails.quantityrecd) as totqtyrecd\n\t FROM purchorderdetails\n\t\tWHERE purchorderdetails.shiptref=" . $_GET['SelectedShipment'] . "\n\t\tGROUP BY purchorderdetails.itemcode,\n\t\t purchorderdetails.itemdescription";
$ErrMsg = _('The lines on the shipment could not be retrieved from the database');
$LineItemsResult = db_query($LineItemsSQL, $db, $ErrMsg);
if (db_num_rows($LineItemsResult) > 0) {
if (isset($_POST['Close'])) {
while ($myrow = DB_fetch_array($LineItemsResult)) {
if ($myrow['totqtyinvoiced'] < $myrow['totqtyrecd']) {
