function OpenCartToWeberpSync($ShowMessages, $db, $db_oc, $oc_tableprefix, $EmailText = '')
{
    $begintime = time_start();
    // connect to opencart DB
    DB_Txn_Begin($db);
    // check last time we run this script, so we know which records need to update from OC to webERP
    $LastTimeRun = CheckLastTimeRun('OpenCartToWeberp', $db);
    $TimeDifference = Get_SQL_to_PHP_time_difference($db);
    if ($ShowMessages) {
        prnMsg('This script was last run on: ' . $LastTimeRun . ' Server time difference: ' . $TimeDifference, 'success');
        prnMsg('Server time now: ' . GetServerTimeNow($TimeDifference), 'success');
    }
    if ($EmailText != '') {
        $EmailText = $EmailText . 'OpenCart to webERP Sync was last run on: ' . $LastTimeRun . "\n" . PrintTimeInformation($db);
    }
    // update order information
    $EmailText = SyncOrderInformation($TimeDifference, $ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // update payment information
    $EmailText = SyncPaypalPaymentInformation($TimeDifference, $ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // We are done!
    SetLastTimeRun('OpenCartToWeberp', $db);
    DB_Txn_Commit($db);
    if ($ShowMessages) {
        time_finish($begintime);
    }
    return $EmailText;
}
Example #2
0
function InsertPurchData($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 = 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);
    }
    $FieldNames = '';
    $FieldValues = '';
    foreach ($PurchDataDetails as $key => $value) {
        $FieldNames .= $key . ', ';
        $FieldValues .= '"' . $value . '", ';
    }
    if (sizeof($Errors) == 0) {
        $sql = "INSERT INTO purchdata (" . mb_substr($FieldNames, 0, -2) . ")\n\t\t\t\t\tVALUES ('" . mb_substr($FieldValues, 0, -2) . "') ";
        DB_Txn_Begin($db);
        $result = DB_Query($sql, $db);
        DB_Txn_Commit($db);
        if (DB_error_no($db) != 0) {
            $Errors[0] = DatabaseUpdateFailed;
        } else {
            $Errors[0] = 0;
        }
    }
    return $Errors;
}
function ProcessSupplier($oldCode, $newCode)
{
    global $db;
    $table_key = array('grns' => 'supplierid', 'offers' => 'supplierid', 'purchdata' => 'supplierno', 'purchorders' => 'supplierno', 'shipments' => 'supplierid', 'suppliercontacts' => 'supplierid', 'supptrans' => 'supplierno', 'www_users' => 'supplierid');
    // First check the Supplier code exists
    if (!checkSupplierExist($oldCode)) {
        prnMsg('<br /><br />' . _('The Supplier code') . ': ' . $oldCode . ' ' . _('does not currently exist as a supplier code in the system'), 'error');
        return;
    }
    $newCode = trim($newCode);
    if (checkNewCode($newCode)) {
        // Now check that the new code doesn't already exist
        if (checkSupplierExist($newCode)) {
            prnMsg(_('The replacement supplier code') . ': ' . $newCode . ' ' . _('already exists as a supplier code in the system') . ' - ' . _('a unique supplier code must be entered for the new code'), 'error');
            return;
        }
    } else {
        return;
    }
    $result = DB_Txn_Begin($db);
    prnMsg(_('Inserting the new supplier record'), 'info');
    $sql = "INSERT INTO suppliers (`supplierid`,\n\t\t`suppname`,  `address1`, `address2`, `address3`,\n\t\t`address4`,  `address5`,  `address6`, `supptype`, `lat`, `lng`,\n\t\t`currcode`,  `suppliersince`, `paymentterms`, `lastpaid`,\n\t\t`lastpaiddate`, `bankact`, `bankref`, `bankpartics`,\n\t\t`remittance`, `taxgroupid`, `factorcompanyid`, `taxref`,\n\t\t`phn`, `port`, `email`, `fax`, `telephone`)\n\tSELECT '" . $newCode . "',\n\t\t`suppname`,  `address1`, `address2`, `address3`,\n\t\t`address4`,  `address5`,  `address6`, `supptype`, `lat`, `lng`,\n\t\t`currcode`,  `suppliersince`, `paymentterms`, `lastpaid`,\n\t\t`lastpaiddate`, `bankact`, `bankref`, `bankpartics`,\n\t\t`remittance`, `taxgroupid`, `factorcompanyid`, `taxref`,\n\t\t`phn`, `port`, `email`, `fax`, `telephone`\n\t\tFROM suppliers WHERE supplierid='" . $oldCode . "'";
    $DbgMsg = _('The SQL that failed was');
    $ErrMsg = _('The SQL to insert the new suppliers master record failed') . ', ' . _('the SQL statement was');
    $result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
    foreach ($table_key as $table => $key) {
        prnMsg(_('Changing') . ' ' . $table . ' ' . _('records'), 'info');
        $sql = "UPDATE " . $table . " SET {$key}='" . $newCode . "' WHERE {$key}='" . $oldCode . "'";
        $ErrMsg = _('The SQL to update') . ' ' . $table . ' ' . _('records failed');
        $result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
    }
    prnMsg(_('Deleting the supplier code from the suppliers master table'), 'info');
    $sql = "DELETE FROM suppliers WHERE supplierid='" . $oldCode . "'";
    $ErrMsg = _('The SQL to delete the old supplier record failed');
    $result = DB_query($sql, $db, $ErrMsg, $DbgMsg, true);
    $result = DB_Txn_Commit($db);
}
 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 WeberpToOpenCartHourlySync($ShowMessages, $db, $db_oc, $oc_tableprefix, $ControlTx = TRUE, $EmailText = '')
{
    $begintime = time_start();
    if ($ControlTx) {
        DB_Txn_Begin($db);
    }
    // check last time we run this script, so we know which records need to update from OC to webERP
    $LastTimeRun = CheckLastTimeRun('WeberpToOpenCartHourly', $db);
    if ($ShowMessages) {
        $TimeDifference = Get_SQL_to_PHP_time_difference($db);
        prnMsg('This script was last run on: ' . $LastTimeRun . ' Server time difference: ' . $TimeDifference, 'success');
        prnMsg('Server time now: ' . GetServerTimeNow($TimeDifference), 'success');
    }
    if ($EmailText != '' and ControlTx) {
        $EmailText = $EmailText . 'webERP to OpenCart Hourly Sync was last run on: ' . $LastTimeRun . "\n" . PrintTimeInformation($db);
    }
    // update product basic information
    $EmailText = SyncProductBasicInformation($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // update product - sales categories relationship
    $EmailText = SyncProductSalesCategories($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // update product prices
    $EmailText = SyncProductPrices($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // update stock in hand
    $EmailText = SyncProductQOH($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // clean duplicated URL alias
    $EmailText = CleanDuplicatedUrlAlias($ShowMessages, $LastTimeRun, $db, $db_oc, $oc_tableprefix, $EmailText);
    // We are done!
    SetLastTimeRun('WeberpToOpenCartHourly', $db);
    if ($ControlTx) {
        DB_Txn_Commit($db);
    }
    if ($ShowMessages) {
        time_finish($begintime);
    }
    return $EmailText;
}
        $_POST['OldOverheadCost'] = $OldRow['overheadcost'];
    } else {
        $_POST['OldLabourCost'] = 0;
        $_POST['OldOverheadCost'] = 0;
        $_POST['LabourCost'] = 0;
        $_POST['OverheadCost'] = 0;
    }
    DB_free_result($OldResult);
    $OldCost = $_POST['OldMaterialCost'] + $_POST['OldLabourCost'] + $_POST['OldOverheadCost'];
    $NewCost = filter_number_format($_POST['MaterialCost']) + filter_number_format($_POST['LabourCost']) + filter_number_format($_POST['OverheadCost']);
    $result = DB_query("SELECT * FROM stockmaster WHERE stockid='" . $StockID . "'");
    $myrow = DB_fetch_row($result);
    if (DB_num_rows($result) == 0) {
        prnMsg(_('The entered item code does not exist'), 'error', _('Non-existent Item'));
    } elseif ($OldCost != $NewCost) {
        $Result = DB_Txn_Begin();
        ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $_POST['QOH']);
        $SQL = "UPDATE stockmaster SET\tmaterialcost='" . filter_number_format($_POST['MaterialCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlabourcost='" . filter_number_format($_POST['LabourCost']) . "',\n\t\t\t\t\t\t\t\t\t\toverheadcost='" . filter_number_format($_POST['OverheadCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlastcost='" . $OldCost . "',\n\t\t\t\t\t\t\t\t\t\tlastcostupdate ='" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\t\tWHERE stockid='" . $StockID . "'";
        $ErrMsg = _('The cost details for the stock item could not be updated because');
        $DbgMsg = _('The SQL that failed was');
        $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
        $Result = DB_Txn_Commit();
        UpdateCost($db, $StockID);
        //Update any affected BOMs
    }
}
$ErrMsg = _('The cost details for the stock item could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$result = DB_query("SELECT description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype,\n\t\t\t\t\t\t\tlastcostupdate,\n\t\t\t\t\t\t\tsum(quantity) as totalqoh\n\t\t\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\t\t\t\tINNER JOIN stockcategory\n\t\t\t\t\t\t\tON stockmaster.categoryid = stockcategory.categoryid\n\t\t\t\t\t\tWHERE stockmaster.stockid='" . $StockID . "'\n\t\t\t\t\t\tGROUP BY description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype", $ErrMsg, $DbgMsg);
$myrow = DB_fetch_array($result);
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
 $Result = DB_query($SQL, $ErrMsg, $DbgMsg);
 if ($myrow = DB_fetch_row($Result)) {
     $Area = $myrow[0];
 }
 DB_free_result($Result);
 if ($_SESSION['CompanyRecord']['gllink_stock'] == 1 and $_POST['CreditType'] == 'WriteOff' and (!isset($_POST['WriteOffGLCode']) or $_POST['WriteOffGLCode'] == '')) {
     prnMsg(_('For credit notes created to write off the stock a general ledger account is required to be selected. Please select an account to write the cost of the stock off to then click on Process again'), 'error');
     include 'includes/footer.inc';
     exit;
 }
 /*Now Get the next credit note number - function in SQL_CommonFunctions*/
 $CreditNo = GetNextTransNo(11, $db);
 $SQLCreditDate = Date('Y-m-d');
 $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
 /*Start an SQL transaction */
 DB_Txn_Begin();
 /*Now insert the Credit Note into the DebtorTrans table allocations will have to be done seperately*/
 $SQL = "INSERT INTO debtortrans (transno,\n\t\t\t\t\t\t\t \t\ttype,\n\t\t\t\t\t\t\t\t\tdebtorno,\n\t\t\t\t\t\t\t\t\tbranchcode,\n\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\tinputdate,\n\t\t\t\t\t\t\t\t\tprd,\n\t\t\t\t\t\t\t\t\ttpe,\n\t\t\t\t\t\t\t\t\tovamount,\n\t\t\t\t\t\t\t\t\tovgst,\n\t\t\t\t\t\t\t\t\tovfreight,\n\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\tinvtext,\n\t\t\t\t\t\t\t\t\tsalesperson)\n\t\t\t\t\t\t\t\t  VALUES ('" . $CreditNo . "',\n\t\t\t\t\t\t\t\t  \t'11',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->Branch . "',\n\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\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'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DefaultSalesType . "',\n\t\t\t\t\t\t\t\t\t'" . -$_SESSION['CreditItems' . $identifier]->total . "',\n\t\t\t\t\t\t\t\t\t'" . -$TaxTotal . "',\n\t\t\t\t\t\t\t\t  \t'" . -$_SESSION['CreditItems' . $identifier]->FreightCost . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CurrencyRate'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_POST['CreditText'] . "',\n\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->SalesPerson . "' )";
 $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The customer credit note transaction could not be added to the database because');
 $DbgMsg = _('The following SQL to insert the customer credit note was used');
 $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
 $CreditTransID = 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,\n\t\t\t\t\t\t\ttaxauthid,\n\t\t\t\t\t\t\ttaxamount)\n\t\t\t\tVALUES ('" . $CreditTransID . "',\n\t\t\t\t\t\t'" . $TaxAuthID . "',\n\t\t\t\t\t\t'" . -$TaxAmount / $_SESSION['CurrencyRate'] . "')";
     $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The debtor transaction taxes records could not be inserted because');
     $DbgMsg = _('The following SQL to insert the debtor transaction taxes record was used');
     $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
 }
 /* Insert stock movements for stock coming back in if the Credit is a return of goods */
 foreach ($_SESSION['CreditItems' . $identifier]->LineItems as $CreditLine) {
Example #8
0
 $Title = _('Payment Run - Problem Report');
 $RefCounter = 0;
 include 'includes/PDFStarter.php';
 $pdf->addInfo('Title', _('Payment Run Report'));
 $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;
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;
    }
}
Example #10
0
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 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;
}
Example #12
0
 } else {
     while ($myrow = DB_fetch_array($PricingDataResult)) {
         //clone the purchase data
         $sql = "INSERT INTO prices (stockid,\n                                        typeabbrev,\n                                        currabrev,\n                                        debtorno,\n                                        startdate,\n                                        enddate,\n                                        price)\n                                VALUES ('" . $_POST['StockID'] . "',\n                                    '" . $myrow['typeabbrev'] . "',\n                                    '" . $myrow['currabrev'] . "',\n                                    '" . $myrow['debtorno'] . "',\n                                    '" . $myrow['startdate'] . "',\n                                    '" . $myrow['enddate'] . "',\n                                    '" . $myrow['price'] . "')";
         $ErrMsg = _('The cloned pricing could not be added');
         $result = DB_query($sql, $db, $ErrMsg);
     }
 }
 //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.'));
     }
Example #13
0
     $myrow = DB_fetch_row($ResultOldRate);
     $OldRate = $myrow[0];
     /*SelectedCurrency could also exist if submit had not been clicked this code would not run in this case cos submit is false of course  see the delete code below*/
     $sql = "UPDATE currencies SET\tcountry='" . $_POST['Country'] . "',\n\t\t\t\t\t\t\t\t\t\thundredsname='" . $_POST['HundredsName'] . "',\n\t\t\t\t\t\t\t\t\t\tdecimalplaces='" . filter_number_format($_POST['DecimalPlaces']) . "',\n\t\t\t\t\t\t\t\t\t\trate='" . filter_number_format($_POST['ExchangeRate']) . "',\n\t\t\t\t\t\t\t\t\t\twebcart='" . $_POST['webcart'] . "'\n\t\t\t\t\tWHERE currabrev = '" . $SelectedCurrency . "'";
     $msg = _('The currency definition record has been updated');
     $NewRate = $_POST['ExchangeRate'];
 } else {
     if ($InputError != 1) {
         /*Selected currencies is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new payment terms form */
         $sql = "INSERT INTO currencies (currency,\n\t\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\t\tcountry,\n\t\t\t\t\t\t\t\t\t\thundredsname,\n\t\t\t\t\t\t\t\t\t\tdecimalplaces,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\twebcart)\n\t\t\t\t\t\t\t\tVALUES ('" . $CurrencyName[$_POST['Abbreviation']] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Abbreviation'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Country'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['HundredsName'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['DecimalPlaces']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['ExchangeRate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['webcart'] . "')";
         $msg = _('The currency definition record has been added');
     }
 }
 //run the SQL from either of the above possibilites
 $ExDiffTransNo = GetNextTransNo(36, $db);
 $resultTx = DB_Txn_Begin();
 $result = DB_query($sql);
 if ($InputError != 1) {
     prnMsg($msg, 'success');
 }
 /* Now we should update the functional currency value of the bank accounts of the $SelectedCurrency
 	Example: if functional currency = IDR and we have a bank account in USD.
 	Before rate was 1 USD = 9.000 IDR so OldRate = 1 /9.000 = 0.000111
 	if the new exchange rate is 1 USD = 10.000 IDR NewRate will be 0.0001.
 	If we had 5.000 USD on the bank account, we had 45.000.000 IDR on the balance sheet.
 	After we update to the new rate, we still have 5.000 USD on the bank account
 	but the balance value of the bank account is 50.000.000 IDR, so let's adjust the value */
 if (isset($SelectedCurrency) and $InputError != 1) {
     /*Get the current period */
     $PostingDate = Date($_SESSION['DefaultDateFormat']);
     $PeriodNo = GetPeriod($PostingDate, $db);
Example #14
0
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;
    }
}
Example #15
0
     $myrow = DB_fetch_row($ResultOldRate);
     $OldRate = $myrow[0];
     /*SelectedCurrency could also exist if submit had not been clicked this code would not run in this case cos submit is false of course  see the delete code below*/
     $sql = "UPDATE currencies SET country='" . $_POST['Country'] . "',\n\t\t\t\t\t\t\t\t\t\tcurrency='" . $CurrencyName[$_POST['Abbreviation']] . "',\n\t\t\t\t\t\t\t\t\t\thundredsname='" . $_POST['HundredsName'] . "',\n\t\t\t\t\t\t\t\t\t\tdecimalplaces='" . filter_number_format($_POST['DecimalPlaces']) . "',\n\t\t\t\t\t\t\t\t\t\trate='" . filter_number_format($_POST['ExchangeRate']) . "',\n\t\t\t\t\t\t\t\t\t\twebcart='" . $_POST['webcart'] . "'\n\t\t\t\t\t\t\t\t\tWHERE currabrev = '" . $SelectedCurrency . "'";
     $msg = _('The currency definition record has been updated');
     $NewRate = $_POST['ExchangeRate'];
 } else {
     if ($InputError != 1) {
         /*Selected currencies is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new payment terms form */
         $sql = "INSERT INTO currencies (currency,\n\t\t\t\t\t\t\t\t\t\tcurrabrev,\n\t\t\t\t\t\t\t\t\t\tcountry,\n\t\t\t\t\t\t\t\t\t\thundredsname,\n\t\t\t\t\t\t\t\t\t\tdecimalplaces,\n\t\t\t\t\t\t\t\t\t\trate,\n\t\t\t\t\t\t\t\t\t\twebcart)\n\t\t\t\t\t\t\t\tVALUES ('" . $CurrencyName[$_POST['Abbreviation']] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Abbreviation'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['Country'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['HundredsName'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['DecimalPlaces']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . filter_number_format($_POST['ExchangeRate']) . "',\n\t\t\t\t\t\t\t\t\t\t'" . $_POST['webcart'] . "')";
         $msg = _('The currency definition record has been added');
     }
 }
 //run the SQL from either of the above possibilites
 $ExDiffTransNo = GetNextTransNo(36, $db);
 $resultTx = DB_Txn_Begin($db);
 $result = DB_query($sql, $db);
 if ($InputError != 1) {
     prnMsg($msg, 'success');
 }
 /* Now we should update the functional currency value of the bank accounts of the $SelectedCurrency
 	Example: if functional currency = IDR and we have a bank account in USD.
 	Before rate was 1 USD = 9.000 IDR so OldRate = 1 /9.000 = 0.000111
 	if the new exchange rate is 1 USD = 10.000 IDR NewRate will be 0.0001.
 	If we had 5.000 USD on the bank account, we had 45.000.000 IDR on the balance sheet.
 	After we update to the new rate, we still have 5.000 USD on the bank account
 	but the balance value of the bank account is 50.000.000 IDR, so let's adjust the value */
 if (isset($SelectedCurrency) and $InputError != 1) {
     /*Get the current period */
     $PostingDate = Date($_SESSION['DefaultDateFormat']);
     $PeriodNo = GetPeriod($PostingDate, $db);
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,\n                                                   type,\n                                                   transno,\n                                                   loccode,\n                                                   trandate,\n                                                   prd,\n                                                   reference,\n                                                   qty,\n                                                   newqoh,\n                                                   price,\n                                                   standardcost)\n                                      \tVALUES ('" . $StockID . "',\n                                                 '26',\n                                                '" . $TransactionNo . "',\n                                                '" . $Location . "',\n                                                '" . $TranDate . "',\n                                                '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n                                                '" . $WONumber . "',\n                                                '" . $Quantity . "',\n                                                '" . $newqoh . "',\n                                                '" . $cost . "',\n                                                '" . $cost . "')";
    $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                                \t\tVALUES (26,\n                                               '" . $TransactionNo . "',\n                                               '" . $TranDate . "',\n                                               '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n                                               '" . $wipglact . "',\n                                               '" . $cost * $Quantity . "',\n                                               '" . $StockID . ' x ' . $Quantity . ' @ ' . $cost . "')";
    $glupdatesql2 = "INSERT INTO gltrans (type,\n                                                typeno,\n                                                trandate,\n                                                periodno,\n                                                account,\n                                                amount,\n                                                narrative)\n                                    \tVALUES (26,\n                                               '" . $TransactionNo . "',\n                                               '" . $TranDate . "',\n                                               '" . GetPeriodFromTransactionDate($TranDate, sizeof($Errors), $Errors, $db) . "',\n                                               '" . $stockact . ',' . $cost * -$Quantity . "',\n                                               '" . $StockID . ' x ' . $Quantity . ' @ ' . $cost . "')";
    $systypessql = "UPDATE systypes set typeno='" . $TransactionNo . "' where typeid=26";
    DB_Txn_Begin();
    DB_query($stockmovesql);
    DB_query($locstocksql);
    DB_query($glupdatesql1);
    DB_query($glupdatesql2);
    DB_query($systypessql);
    DB_Txn_Commit();
    if (DB_error_no() != 0) {
        $Errors[0] = DatabaseUpdateFailed;
    } else {
        $Errors[0] = 0;
    }
    return $Errors;
}
Example #17
0
echo '<tr><td colspan="2" align="right">' . _('Totals') . '</td>
	<td></td>
	<td>' . number_format($TotalReqdCost, 2) . '</td>
	<td></td><td></td>
	<td>' . number_format($TotalIssuedCost, 2) . '</td>
	<td align="right">' . number_format($TotalUsageVar, 2) . '</td>
	<td align="right">' . number_format($TotalCostVar, 2) . '</td></tr>';
echo '<tr><td colspan="3"></td><td><hr/></td><td colspan="2"></td><td colspan="3"><hr></td></tr>';
#echo '<tr><td colspan="7"></td><td colspan="2"><hr></td></tr>';
if (isset($_POST['Close'])) {
    DB_data_seek($WOItemsResult, 0);
    $NoItemsOnWO = DB_num_rows($WOItemsResult);
    $TotalVariance = $TotalUsageVar + $TotalCostVar;
    $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
    $WOCloseNo = GetNextTransNo(29, $db);
    $TransResult = DB_Txn_Begin($db);
    while ($WORow = DB_fetch_array($WOItemsResult)) {
        if ($TotalStdValueRecd == 0) {
            $ShareProportion = 1 / $NoItemsOnWO;
        } else {
            $ShareProportion = $WORow['stdcost'] * $WORow['qtyrecd'] / $TotalStdValueRecd;
        }
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            //we need to post the variances to stock and update the weighted average cost
            /*  need to get the current total quantity on hand
            			if the quantity on hand is less than the quantity received on the work order
            			then some of the variance needs to be written off to P & L and only the proportion
            			of the variance relating to the stock still on hand should be posted to the stock value
            			*/
            $TotOnHandResult = DB_query("SELECT SUM(quantity)\n\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\tWHERE stockid='" . $WORow['stockid'] . "'", $db);
            $TotOnHandRow = DB_fetch_row($TotOnHandResult);
 $Title = _('Payment Run - Problem Report');
 $RefCounter = 0;
 include 'includes/PDFStarter.php';
 $pdf->addInfo('Title', _('Payment Run Report'));
 $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);
 $SupplierID = '';
 $TotalPayments = 0;
 $TotalAccumDiffOnExch = 0;
 if (isset($_POST['PrintPDFAndProcess'])) {
     $ProcessResult = DB_Txn_Begin();
 }
 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, '', '', false, false);
     if (DB_error_no() != 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(), '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;
		<td class="number">' . locale_number_format($TotalUsageVar, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
		<td class="number">' . locale_number_format($TotalCostVar, $_SESSION['CompanyRecord']['decimalplaces']) . '</td>
	</tr>';
echo '<tr>
		<td colspan="3"></td>
		<td><hr/></td>
		<td colspan="2"></td>
		<td colspan="3"><hr /></td>
	</tr>';
if (isset($_POST['Close'])) {
    DB_data_seek($WOItemsResult, 0);
    $NoItemsOnWO = DB_num_rows($WOItemsResult);
    $TotalVariance = $TotalUsageVar + $TotalCostVar;
    $PeriodNo = GetPeriod(Date($_SESSION['DefaultDateFormat']), $db);
    $WOCloseNo = GetNextTransNo(29, $db);
    $TransResult = DB_Txn_Begin();
    while ($WORow = DB_fetch_array($WOItemsResult)) {
        if ($TotalStdValueRecd == 0) {
            $ShareProportion = 1 / $NoItemsOnWO;
        } else {
            $ShareProportion = $WORow['stdcost'] * $WORow['qtyrecd'] / $TotalStdValueRecd;
        }
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            //we need to post the variances to stock and update the weighted average cost
            /*  need to get the current total quantity on hand
            			if the quantity on hand is less than the quantity received on the work order
            			then some of the variance needs to be written off to P & L and only the proportion
            			of the variance relating to the stock still on hand should be posted to the stock value
            			*/
            $TotOnHandResult = DB_query("SELECT SUM(quantity)\n\t\t\t\t\t\t\t\t\t\tFROM locstock\n\t\t\t\t\t\t\t\t\t\tWHERE stockid='" . $WORow['stockid'] . "'");
            $TotOnHandRow = DB_fetch_row($TotOnHandResult);
         echo '<br>';
         echo '<div class="centre"><a href="' . $rootpath . '/SelectSalesOrder.php?' . SID . '">' . _('Select a sales order for confirming deliveries and invoicing') . '</a></div>';
         unset($_SESSION['Items']->LineItems);
         unset($_SESSION['Items']);
         unset($_SESSION['ProcessingOrder']);
         include 'includes/footer.inc';
         exit;
     }
 }
 /*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');
Example #21
0
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;
}