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;
}
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 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;
}
    }
    DB_free_result($OldResult);
    $OldCost = $_POST['OldMaterialCost'] + $_POST['OldLabourCost'] + $_POST['OldOverheadCost'];
    $NewCost = filter_number_format($_POST['MaterialCost']) + filter_number_format($_POST['LabourCost']) + filter_number_format($_POST['OverheadCost']);
    $result = DB_query("SELECT * FROM stockmaster WHERE stockid='" . $StockID . "'");
    $myrow = DB_fetch_row($result);
    if (DB_num_rows($result) == 0) {
        prnMsg(_('The entered item code does not exist'), 'error', _('Non-existent Item'));
    } elseif ($OldCost != $NewCost) {
        $Result = DB_Txn_Begin();
        ItemCostUpdateGL($db, $StockID, $NewCost, $OldCost, $_POST['QOH']);
        $SQL = "UPDATE stockmaster SET\tmaterialcost='" . filter_number_format($_POST['MaterialCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlabourcost='" . filter_number_format($_POST['LabourCost']) . "',\n\t\t\t\t\t\t\t\t\t\toverheadcost='" . filter_number_format($_POST['OverheadCost']) . "',\n\t\t\t\t\t\t\t\t\t\tlastcost='" . $OldCost . "',\n\t\t\t\t\t\t\t\t\t\tlastcostupdate ='" . Date('Y-m-d') . "'\n\t\t\t\t\t\t\t\tWHERE stockid='" . $StockID . "'";
        $ErrMsg = _('The cost details for the stock item could not be updated because');
        $DbgMsg = _('The SQL that failed was');
        $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
        $Result = DB_Txn_Commit();
        UpdateCost($db, $StockID);
        //Update any affected BOMs
    }
}
$ErrMsg = _('The cost details for the stock item could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$result = DB_query("SELECT description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype,\n\t\t\t\t\t\t\tlastcostupdate,\n\t\t\t\t\t\t\tsum(quantity) as totalqoh\n\t\t\t\t\t\tFROM stockmaster INNER JOIN locstock\n\t\t\t\t\t\t\tON stockmaster.stockid=locstock.stockid\n\t\t\t\t\t\t\tINNER JOIN stockcategory\n\t\t\t\t\t\t\tON stockmaster.categoryid = stockcategory.categoryid\n\t\t\t\t\t\tWHERE stockmaster.stockid='" . $StockID . "'\n\t\t\t\t\t\tGROUP BY description,\n\t\t\t\t\t\t\tunits,\n\t\t\t\t\t\t\tlastcost,\n\t\t\t\t\t\t\tactualcost,\n\t\t\t\t\t\t\tmaterialcost,\n\t\t\t\t\t\t\tlabourcost,\n\t\t\t\t\t\t\toverheadcost,\n\t\t\t\t\t\t\tmbflag,\n\t\t\t\t\t\t\tstocktype", $ErrMsg, $DbgMsg);
$myrow = DB_fetch_array($result);
echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
	<div>
	<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
	<table cellpadding="2" class="selection">
		<tr>
			<th colspan="2">' . _('Item Code') . ':<input type="text" name="StockID" value="' . $StockID . '"  maxlength="20" /><input type="submit" name="Show" value="' . _('Show Cost Details') . '" /></th>
		</tr>
Пример #5
0
         }
         $LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, locale_number_format($DetailTrans['balance'], $CurrDecimalPlaces), 'right');
         $LeftOvers = $pdf->addTextWrap(405, $YPos, 60, $FontSize, locale_number_format($DiffOnExch, $_SESSION['CompanyRecord']['decimalplaces']), 'right');
         $YPos -= $line_height;
         if ($YPos < $Bottom_Margin + $line_height) {
             $PageNumber++;
             include 'includes/PDFPaymentRunPageHeader.inc';
         }
     }
     /*end while there are detail transactions to show */
 }
 /* end while there are suppliers to retrieve transactions for */
 if ($SupplierID != '') {
     /*All the payment processing is in the below file */
     include 'includes/PDFPaymentRun_PymtFooter.php';
     $ProcessResult = DB_Txn_Commit($db);
     if (DB_error_no($db) != 0) {
         $Title = _('Payment Processing - Problem Report') . '.... ';
         include 'includes/header.inc';
         prnMsg(_('None of the payments will be processed. Unfortunately, there was a problem committing the changes to the database because') . ' - ' . DB_error_msg($db), 'error');
         echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
         if ($debug == 1) {
             prnMsg(_('The SQL that failed was') . '<br />' . $SQL, 'error');
         }
         $ProcessResult = DB_Txn_Rollback($db);
         include 'includes/footer.inc';
         exit;
     }
     $LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 340 - $Left_Margin, $FontSize, _('Grand Total Payments Due'), 'left');
     $LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, locale_number_format($TotalPayments, $CurrDecimalPlaces), 'right');
     $LeftOvers = $pdf->addTextWrap(405, $YPos, 60, $FontSize, locale_number_format($TotalAccumDiffOnExch, $_SESSION['CompanyRecord']['decimalplaces']), 'right');
        if ($_SESSION['CreditItems' . $identifier]->FreightCost != 0) {
            $SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\t\tVALUES (11,\n\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\n\t\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['freightact'] . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->FreightCost / $_SESSION['CurrencyRate'] . "')";
            $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The freight GL posting for this credit note could not be inserted because');
            $DbgMsg = _('The following SQL to insert the GLTrans record was used');
            $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
        }
        foreach ($TaxTotals as $TaxAuthID => $TaxAmount) {
            if ($TaxAmount != 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\t\tVALUES (11,\n\t\t\t\t\t\t\t\t\t\t\t'" . $CreditNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $SQLCreditDate . "',\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'" . $TaxGLCodes[$TaxAuthID] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $_SESSION['CreditItems' . $identifier]->DebtorNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $TaxAmount / $_SESSION['CurrencyRate'] . "')";
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The tax GL posting could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
            }
        }
        EnsureGLEntriesBalance(11, $CreditNo, $db);
    }
    /*end of if Sales and GL integrated */
    DB_Txn_Commit();
    unset($_SESSION['CreditItems' . $identifier]->LineItems);
    unset($_SESSION['CreditItems' . $identifier]);
    echo _('Credit Note number') . ' ' . $CreditNo . ' ' . _('processed') . '<br />';
    echo '<a target="_blank" href="' . $RootPath . '/PrintCustTrans.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit">' . _('Show this Credit Note on screen') . '</a><br />';
    if ($_SESSION['InvoicePortraitFormat'] == 0) {
        echo '<a href="' . $RootPath . '/PrintCustTrans.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit&PrintPDF=True">' . _('Print this Credit Note') . '</a>';
    } else {
        echo '<a href="' . $RootPath . '/PrintCustTransPortrait.php?FromTransNo=' . $CreditNo . '&InvOrCredit=Credit&PrintPDF=True">' . _('Print this Credit Note') . '</a>';
    }
    echo '<br /><a href="' . $RootPath . '/SelectCreditItems.php">' . _('Enter Another Credit Note') . '</a>';
}
/*end of process credit note */
include 'includes/footer.inc';
Пример #7
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 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;
    }
}
Пример #9
0
         }
         $LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, locale_number_format($DetailTrans['balance'], $CurrDecimalPlaces), 'right');
         $LeftOvers = $pdf->addTextWrap(405, $YPos, 60, $FontSize, locale_number_format($DiffOnExch, $_SESSION['CompanyRecord']['decimalplaces']), 'right');
         $YPos -= $line_height;
         if ($YPos < $Bottom_Margin + $line_height) {
             $PageNumber++;
             include 'includes/PDFPaymentRunPageHeader.inc';
         }
     }
     /*end while there are detail transactions to show */
 }
 /* end while there are suppliers to retrieve transactions for */
 if ($SupplierID != '') {
     /*All the payment processing is in the below file */
     include 'includes/PDFPaymentRun_PymtFooter.php';
     $ProcessResult = DB_Txn_Commit();
     if (DB_error_no() != 0) {
         $Title = _('Payment Processing - Problem Report') . '.... ';
         include 'includes/header.inc';
         prnMsg(_('None of the payments will be processed. Unfortunately, there was a problem committing the changes to the database because') . ' - ' . DB_error_msg(), 'error');
         echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
         if ($debug == 1) {
             prnMsg(_('The SQL that failed was') . '<br />' . $SQL, 'error');
         }
         $ProcessResult = DB_Txn_Rollback();
         include 'includes/footer.inc';
         exit;
     }
     $LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 340 - $Left_Margin, $FontSize, _('Grand Total Payments Due'), 'left');
     $LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, locale_number_format($TotalPayments, $CurrDecimalPlaces), 'right');
     $LeftOvers = $pdf->addTextWrap(405, $YPos, 60, $FontSize, locale_number_format($TotalAccumDiffOnExch, $_SESSION['CompanyRecord']['decimalplaces']), 'right');
Пример #10
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;
}
Пример #11
0
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL posting for the cost variance could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
                $SQL = "INSERT INTO gltrans (type,\n\t\t\t\t\t\t\ttypeno,\n\t\t\t\t\t\t\ttrandate,\n\t\t\t\t\t\t\tperiodno,\n\t\t\t\t\t\t\taccount,\n\t\t\t\t\t\t\tnarrative,\n\t\t\t\t\t\t\tamount)\n\t\t\t\t\t\tVALUES (29,\n\t\t\t\t\t\t\t" . $WOCloseNo . ",\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t" . $PeriodNo . ",\n\t\t\t\t\t\t\t" . $WORow['wipact'] . ",\n\t\t\t\t\t\t\t'" . $_POST['WO'] . ' - ' . $WORow['stockid'] . ' ' . _('share of cost variance') . "',\n\t\t\t\t\t\t\t" . $TotalCostVar * $ShareProportion . ")";
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL posting for the WIP side of the cost variance posting could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
            }
            //end of if gl-stock integrated and there's a cost variance
        }
        //end of standard costing section
    }
    // end loop around the items on the work order
    $CloseWOResult = DB_query('UPDATE workorders SET closed=1 WHERE wo=' . $_POST['WO'], $db, _('Could not update the work order to closed because:'), _('The SQL used to close the work order was:'), true);
    $DeleteAnyWOSerialNos = DB_query('DELETE FROM woserialnos WHERE wo=' . $_POST['WO'], $db, _('Could not delete the predefined work order serial numbers'), _('The SQL used to delete the predefined serial numbers was:'), true);
    $TransResult = DB_Txn_Commit($db);
    if ($_SESSION['CompanyRecord']['gllink_stock'] == 1) {
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            prnMsg(_('The item cost as calculated from the work order has been applied against the weighted average cost and the necessary GL journals created to update stock as a result of closing this work order'), 'success');
        } else {
            prnMsg(_('The work order has been closed and general ledger entries made for the variances on the work order'), 'success');
        }
    } else {
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            prnMsg(_('The item costs resulting from the work order have been applied against the weighted average stock value of the items on the work order, and the work order has been closed'), 'success');
        } else {
            prnMsg(_('The work order has been closed'), 'success');
        }
    }
    $WorkOrderRow['closed'] = 1;
}
Пример #12
0
         $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.'));
     }
     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']);
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL posting for the cost variance could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
                $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\tVALUES (29,\n\t\t\t\t\t\t\t'" . $WOCloseNo . "',\n\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t'" . $WORow['wipact'] . "',\n\t\t\t\t\t\t\t'" . $_POST['WO'] . ' - ' . $WORow['stockid'] . ' ' . _('share of cost variance') . "',\n\t\t\t\t\t\t\t'" . round($TotalCostVar * $ShareProportion, $_SESSION['CompanyRecord']['decimalplaces']) . "')";
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The GL posting for the WIP side of the cost variance posting could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
            }
            //end of if gl-stock integrated and there's a cost variance
        }
        //end of standard costing section
    }
    // end loop around the items on the work order
    $CloseWOResult = DB_query("UPDATE workorders SET closed=1, closecomments = '" . $_POST['CloseComments'] . "' WHERE wo='" . $_POST['WO'] . "'", _('Could not update the work order to closed because:'), _('The SQL used to close the work order was:'), true);
    $DeleteAnyWOSerialNos = DB_query("DELETE FROM woserialnos WHERE wo='" . $_POST['WO'] . "'", _('Could not delete the predefined work order serial numbers'), _('The SQL used to delete the predefined serial numbers was:'), true);
    $TransResult = DB_Txn_Commit();
    if ($_SESSION['CompanyRecord']['gllink_stock'] == 1) {
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            prnMsg(_('The item cost as calculated from the work order has been applied against the weighted average cost and the necessary GL journals created to update stock as a result of closing this work order'), 'success');
        } else {
            prnMsg(_('The work order has been closed and general ledger entries made for the variances on the work order'), 'success');
        }
    } else {
        if ($_SESSION['WeightedAverageCosting'] == 1) {
            prnMsg(_('The item costs resulting from the work order have been applied against the weighted average stock value of the items on the work order, and the work order has been closed'), 'success');
        } else {
            prnMsg(_('The work order has been closed'), 'success');
        }
    }
    $WorkOrderRow['closed'] = 1;
}
Пример #14
0
            /* Now calculate the Balance in functional currency at the new rate */
            $NewBalanceInFucntionalCurrency = $BalanceInAccountCurrency / $NewRate;
            /* If some adjustment has to be done, do it! */
            $DifferenceToAdjust = $NewBalanceInFucntionalCurrency - $OldBalanceInFunctionalCurrency;
            if ($OldRate != $NewRate) {
                $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\t\t  VALUES (36,\n\t\t\t\t\t\t\t\t\t\t\t'" . $ExDiffTransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($PostingDate) . "',\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'" . $_SESSION['CompanyRecord']['exchangediffact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrowBankAccount['bankaccountname'] . ' ' . _('currency rate adjustment to') . ' ' . locale_number_format($NewRate, 8) . ' ' . $SelectedCurrency . '/' . $_SESSION['CompanyRecord']['currencydefault'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$DifferenceToAdjust . "')";
                $ErrMsg = _('Cannot insert a GL entry for the exchange difference because');
                $DbgMsg = _('The SQL that failed to insert the exchange difference GL entry was');
                $result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
                $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\t\t  VALUES (36,\n\t\t\t\t\t\t\t\t\t\t\t'" . $ExDiffTransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($PostingDate) . "',\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'" . $myrowBankAccount['accountcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrowBankAccount['bankaccountname'] . ' ' . _('currency rate adjustment to') . ' ' . locale_number_format($NewRate, 8) . ' ' . $SelectedCurrency . '/' . $_SESSION['CompanyRecord']['currencydefault'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $DifferenceToAdjust . "')";
                $result = DB_query($SQL, $ErrMsg, $DbgMsg, true);
                prnMsg(_('Bank Account') . ' ' . $myrowBankAccount['bankaccountname'] . ' ' . _('Currency Rate difference of') . ' ' . locale_number_format($DifferenceToAdjust, $_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('has been posted'), 'success');
            }
        }
    }
    $resultTx = DB_Txn_Commit();
    unset($SelectedCurrency);
    unset($_POST['Country']);
    unset($_POST['HundredsName']);
    unset($_POST['DecimalPlaces']);
    unset($_POST['ExchangeRate']);
    unset($_POST['Abbreviation']);
    unset($_POST['webcart']);
} elseif (isset($_GET['delete'])) {
    //the link to delete a selected record was clicked instead of the submit button
    // PREVENT DELETES IF DEPENDENT RECORDS IN DebtorsMaster
    $sql = "SELECT COUNT(*) FROM debtorsmaster\n\t\t\tWHERE currcode = '" . $SelectedCurrency . "'";
    $result = DB_query($sql);
    $myrow = DB_fetch_row($result);
    if ($myrow[0] > 0) {
        prnMsg(_('Cannot delete this currency because customer accounts have been created referring to this currency') . '<br />' . _('There are') . ' ' . $myrow[0] . ' ' . _('customer accounts that refer to this currency'), 'warn');
Пример #15
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;
    }
}
Пример #16
0
            /* Now calculate the Balance in functional currency at the new rate */
            $NewBalanceInFucntionalCurrency = $BalanceInAccountCurrency / $NewRate;
            /* If some adjustment has to be done, do it! */
            $DifferenceToAdjust = $NewBalanceInFucntionalCurrency - $OldBalanceInFunctionalCurrency;
            if (OldRate != NewRate) {
                $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\t\t  VALUES (36,\n\t\t\t\t\t\t\t\t\t\t\t'" . $ExDiffTransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($PostingDate) . "',\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'" . $_SESSION['CompanyRecord']['exchangediffact'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrowBankAccount['bankaccountname'] . ' ' . _('currency rate adjustment to') . ' ' . locale_number_format($NewRate, 8) . ' ' . $SelectedCurrency . '/' . $_SESSION['CompanyRecord']['currencydefault'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . -$DifferenceToAdjust . "')";
                $ErrMsg = _('Cannot insert a GL entry for the exchange difference because');
                $DbgMsg = _('The SQL that failed to insert the exchange difference GL entry was');
                $result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
                $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\t\t  VALUES (36,\n\t\t\t\t\t\t\t\t\t\t\t'" . $ExDiffTransNo . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . FormatDateForSQL($PostingDate) . "',\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'" . $myrowBankAccount['accountcode'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $myrowBankAccount['bankaccountname'] . ' ' . _('currency rate adjustment to') . ' ' . locale_number_format($NewRate, 8) . ' ' . $SelectedCurrency . '/' . $_SESSION['CompanyRecord']['currencydefault'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $DifferenceToAdjust . "')";
                $result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
                prnMsg(_('Bank Account') . ' ' . $myrowBankAccount['bankaccountname'] . ' ' . _('Currency Rate difference of') . ' ' . locale_number_format($DifferenceToAdjust, $_SESSION['CompanyRecord']['decimalplaces']) . ' ' . _('has been posted'), 'success');
            }
        }
    }
    $resultTx = DB_Txn_Commit($db);
    unset($SelectedCurrency);
    unset($_POST['Country']);
    unset($_POST['HundredsName']);
    unset($_POST['DecimalPlaces']);
    unset($_POST['ExchangeRate']);
    unset($_POST['Abbreviation']);
    unset($_POST['webcart']);
} elseif (isset($_GET['delete'])) {
    //the link to delete a selected record was clicked instead of the submit button
    // PREVENT DELETES IF DEPENDENT RECORDS IN DebtorsMaster
    $sql = "SELECT COUNT(*) FROM debtorsmaster\n\t\t\tWHERE currcode = '" . $SelectedCurrency . "'";
    $result = DB_query($sql, $db);
    $myrow = DB_fetch_row($result);
    if ($myrow[0] > 0) {
        prnMsg(_('Cannot delete this currency because customer accounts have been created referring to this currency') . '<br />' . _('There are') . ' ' . $myrow[0] . ' ' . _('customer accounts that refer to this currency'), 'warn');
Пример #17
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,\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;
}
Пример #18
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;
}
Пример #19
0
 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);
 }
Пример #20
0
            $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\tamount\r\n\t\t\t\t\t)\r\n\t\t\t\tVALUES (\r\n\t\t\t\t\t10,\r\n\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t" . $_SESSION['CompanyRecord']['freightact'] . ",\r\n\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t" . -$_SESSION['Items']->FreightCost / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t)";
            $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The freight GL posting could not be inserted because');
            $DbgMsg = _('The following SQL to insert the GLTrans record was used');
            $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
        }
        foreach ($TaxTotals as $TaxAuthID => $TaxAmount) {
            if ($TaxAmount != 0) {
                $SQL = "INSERT INTO gltrans (\r\n\t\t\t\t\t\ttype,\r\n\t\t\t\t\t\ttypeno,\r\n\t\t\t\t\t\ttrandate,\r\n\t\t\t\t\t\tperiodno,\r\n\t\t\t\t\t\taccount,\r\n\t\t\t\t\t\tnarrative,\r\n\t\t\t\t\t\tamount\r\n\t\t\t\t\t\t)\r\n\t\t\t\t\tVALUES (\r\n\t\t\t\t\t\t10,\r\n\t\t\t\t\t\t" . $InvoiceNo . ",\r\n\t\t\t\t\t\t'" . $DefaultDispatchDate . "',\r\n\t\t\t\t\t\t" . $PeriodNo . ",\r\n\t\t\t\t\t\t" . $TaxGLCodes[$TaxAuthID] . ",\r\n\t\t\t\t\t\t'" . $_SESSION['Items']->DebtorNo . "',\r\n\t\t\t\t\t\t" . -$TaxAmount / $_SESSION['CurrencyRate'] . "\r\n\t\t\t\t\t)";
                $ErrMsg = _('CRITICAL ERROR') . '! ' . _('NOTE DOWN THIS ERROR AND SEEK ASSISTANCE') . ': ' . _('The tax GL posting could not be inserted because');
                $DbgMsg = _('The following SQL to insert the GLTrans record was used');
                $Result = DB_query($SQL, $db, $ErrMsg, $DbgMsg, true);
            }
        }
    }
    /*end of if Sales and GL integrated */
    DB_Txn_Commit($db);
    unset($_SESSION['Items']->LineItems);
    unset($_SESSION['Items']);
    unset($_SESSION['ProcessingOrder']);
    echo prnMsg(_('Invoice number') . ' ' . $InvoiceNo . ' ' . _('processed'), 'success');
    echo '<br><div class="centre">';
    if ($_SESSION['InvoicePortraitFormat'] == 0) {
        echo '<img src="' . $rootpath . '/css/' . $theme . '/images/printer.png" title="' . _('Print') . '" alt="">' . ' ' . '<a target="_blank" href="' . $rootpath . '/PrintCustTrans.php?' . SID . 'FromTransNo=' . $InvoiceNo . '&InvOrCredit=Invoice&PrintPDF=True">' . _('Print this invoice') . ' (' . _('Landscape') . ')</a><br><br>';
    } else {
        echo '<img src="' . $rootpath . '/css/' . $theme . '/images/printer.png" title="' . _('Print') . '" alt="">' . ' ' . '<a target="_blank" href="' . $rootpath . '/PrintCustTransPortrait.php?' . SID . 'FromTransNo=' . $InvoiceNo . '&InvOrCredit=Invoice&PrintPDF=True">' . _('Print this invoice') . ' (' . _('Portrait') . ')</a><br><br>';
    }
    echo '<a href="' . $rootpath . '/SelectSalesOrder.php?' . SID . '">' . _('Select another order for invoicing') . '</a><br><br>';
    echo '<a href="' . $rootpath . '/SelectOrderItems.php?' . SID . 'NewOrder=Yes">' . _('Sales Order Entry') . '</a></div><br>';
    /*end of process invoice */
} else {
    /*Process Invoice not set so allow input of invoice data */
Пример #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;
}