PHP DB_Txn_Rollback函数代码示例

        /*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');
    }
    $pdf->OutputD($_SESSION['DatabaseName'] . '_Payment_Run_' . Date('Y-m-d_Hms') . '.pdf');
    $pdf->__destruct();
} else {
    /*The option to print PDF was not hit */
    $Title = _('Payment Run');
    include 'includes/header.inc';
    echo '<p class="page_title_text"><img src="' . $RootPath . '/css/' . $Theme . '/images/maintenance.png" title="' . _('Supplier Types') . '" alt="" />' . $Title . '</p>';
    if (isset($_POST['Currency']) and !is_numeric(filter_number_format($_POST['ExRate']))) {

    $sql = "SELECT DISTINCT b1.component\n\t\t\tFROM bom as b1\n\t\t\tleft join bom as b2 on b2.parent=b1.component\n\t\t\tWHERE b2.parent is null;";
    $ErrMsg = _('An error occurred selecting all bottom level components');
    $DbgMsg = _('The SQL that was used to select bottom level components and failed in the process was');
    $result = DB_query($sql, $db, $ErrMsg, $DbgMsg);
    while ($item = DB_fetch_array($result)) {
        $inputerror = UpdateCost($db, $item['component']);
        if ($inputerror == 0) {
            prnMsg(_('Component') . ' ' . $item['component'] . ' ' . _('has been processed'), 'success');
        } else {
            break;
        }
    }
    if ($inputerror == 1) {
        //exited loop with errors so rollback
        prnMsg(_('Failed on item') . ' ' . $item['component'] . ' ' . _('Cost update has been rolled back'), 'error');
        DB_Txn_Rollback($db);
    } else {
        //all good so commit data transaction
        DB_Txn_Commit($db);
        prnMsg(_('All cost updates committed to the database.'), 'success');
    }
} else {
    echo '<br />
		<br />';
    prnMsg(_('This script will not update the General Ledger stock balances for the changed costs. If you use integrated stock then do not use this utility'), 'warn');
    echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">';
    echo '<div>';
    echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
    echo '<p class="page_title_text">
			<img src="' . $RootPath . '/css/' . $Theme . '/images/sales.png" title="' . _('Search') . '" alt="" />' . ' ' . _('Update costs for all items listed in a bill of materials') . '<br />
		</p>

//.........这里部分代码省略.........
     * AllocDetails['customerref']
     */
    $Errors = array();
    $db = db($User, $Password);
    if (gettype($db) == 'integer') {
        $Errors[0] = NoAuthorisation;
        return $Errors;
    }
    $Errors = VerifyDebtorExists($AllocDetails['debtorno'], sizeof($Errors), $Errors, $db);
    /*Get the outstanding amount to allocate (all amounts in FX) from the transaction*/
    if ($AllocDetails['type'] != '11' and $AllocDetails['type'] != 12) {
        $Errors[] = MustBeReceiptOrCreditNote;
    }
    $SQL = "SELECT id,\n\t\t\t\t\trate,\n\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS lefttoalloc\n\t\t\t\tFROM debtortrans\n\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\tAND type='" . $AllocDetails['type'] . "'\n\t\t\t\tAND transno='" . $AllocDetails['transno'] . "'";
    $Result = api_DB_query($SQL, $db);
    $LeftToAllocRow = DB_fetch_array($Result);
    if (DB_num_rows($Result) == 0) {
        $Errors[0] = NoTransactionToAllocate;
        return $Errors;
    }
    if ($LeftToAllocRow['lefttoalloc'] < 0) {
        /* negative if it is a positive receipt to allocate against invoices */
        /*Now look for invoices with the same customerref to allocate to */
        $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=10\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc >0";
        $Result = api_DB_query($SQL, $db);
        if (DB_num_rows($Result) == 0) {
            $Errors[0] = NoTransactionToAllocate;
            return $Errors;
        }
        /* The receipts will always be smaller than the invoice as there can be several receipts to make up payment for a single invocie - but not vice-versa - that's why there is no point iterating over the possibilities  - in the words of McCloud "There can be only one"*/
        $OSInvRow = DB_fetch_array($Result);
        if ($OSInvRow['rate'] == $LeftToAllocRow['rate'] and $OSInvRow['outstanding'] > 0) {
            if ($OSInvRow['outstanding'] + $LeftToAllocRow['lefttoalloc'] >= 0) {
                /*We can allocate the whole amount of the credit/receipt */
                $AllocateAmount = -$LeftToAllocRow['lefttoalloc'];
            } else {
                /*We can only allocate the rest of the invoice outstanding */
                $AllocateAmount = $OSInvRow['outstanding'];
            }
            DB_Txn_Begin($db);
            /*Now insert the allocation records */
            $SQL = "INSERT INTO custallocns (amt,\n\t\t\t\t\t\t\t\t\t\t\t\tdatealloc,\n\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocto)\n\t\t\t\t\t\t\t\t\tVALUE('" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t'" . $LeftToAllocRow['id'] . "',\n\t\t\t\t\t\t\t\t\t\t'" . $OSInvRow['id'] . "')";
            $Result = api_DB_query($SQL, $db, '', '', true);
            /*Now update the allocated amounts in the debtortrans for both transactions */
            $SQL = "UPDATE debtortrans SET alloc=alloc-" . $AllocateAmount . "\n\t\t\t\t\t\tWHERE id = '" . $LeftToAllocRow['id'] . "'";
            $Result = api_DB_query($SQL, $db, '', '', true);
            $SQL = "UPDATE debtortrans SET alloc=alloc+" . $AllocateAmount . "\n\t\t\t\t\t\tWHERE id = '" . $OSInvRow['id'] . "'";
            $Result = api_DB_query($SQL, $db, '', '', true);
        }
        /*end if the exchange rates are the same so no diff on exchange */
        /*end if it is a normal allocation of receipt to invoice*/
    } elseif ($LeftToAllocRow['lefttoalloc'] > 0) {
        /* it is a payment - negative receipt - already checked type=12 need to find credit note to allocate to
        	  Now look for credit notes  type 11 with the same customerref to allocate to */
        $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=11\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc < -0.005";
        $Result = api_DB_query($SQL, $db);
        if (DB_num_rows($Result) == 0) {
            // then we need to cast the net further afield and look for normal receipts to allocate to
            $SQL = "SELECT id,\n\t\t\t\t\t\trate,\n\t\t\t\t\t\tovamount+ovgst+ovdiscount-alloc AS outstanding\n\t\t\t\t\tFROM debtortrans\n\t\t\t\t\tWHERE debtorno='" . $AllocDetails['debtorno'] . "'\n\t\t\t\t\tAND type=12\n\t\t\t\t\tAND reference='" . $AllocDetails['customerref'] . "'\n\t\t\t\t\tAND ovamount+ovgst+ovdiscount-alloc < 0";
            $Result = api_DB_query($SQL, $db);
        }
        if (DB_num_rows($Result) == 0) {
            //then no trans to allocate this to
            $Errors[0] = 'no transactions to allocate this to';
            return $Errors;
        }
        $LeftToAllocate = $LeftToAllocRow['lefttoalloc'];
        DB_Txn_Begin($db);
        /* there could be several receipts to allocate against ... loop through until $LefToAllocate is exhausted */
        while ($OSCreditRow = DB_fetch_array($Result)) {
            if ($OSCreditRow['rate'] == $LeftToAllocRow['rate'] and $LeftToAllocate > 0) {
                if ($OSCreditRow['outstanding'] + $LeftToAllocate <= 0) {
                    /*We can allocate the whole amount of the receipt */
                    $AllocateAmount = $LeftToAllocate;
                    $LeftToAllocate = 0;
                } else {
                    /*We can only allocate the rest of the invoice outstanding */
                    $AllocateAmount = $OSCreditRow['outstanding'];
                    $LeftToAllocate += $OSCreditRow['outstanding'];
                }
                /*Now insert the allocation records */
                $SQL = "INSERT INTO custallocns (amt,\n\t\t\t\t\t\t\t\t\t\t\t\t\tdatealloc,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocfrom,\n\t\t\t\t\t\t\t\t\t\t\t\t\ttransid_allocto)\n\t\t\t\t\t\t\t\t\t\tVALUE('" . $AllocateAmount . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . Date('Y-m-d') . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $OSCreditRow['id'] . "',\n\t\t\t\t\t\t\t\t\t\t\t'" . $LeftToAllocRow['id'] . "')";
                $Result = api_DB_query($SQL, $db, '', '', true);
                /*Now update the allocated amounts in the debtortrans for both transactions */
                $SQL = "UPDATE debtortrans SET alloc=alloc+" . $AllocateAmount . "\n\t\t\t\t\t\t\tWHERE id = '" . $LeftToAllocRow['id'] . "'";
                $Result = api_DB_query($SQL, $db, '', '', true);
                $SQL = "UPDATE debtortrans SET alloc=alloc-" . $AllocateAmount . "\n\t\t\t\t\t\t\tWHERE id = '" . $OSCreditRow['id'] . "'";
                $Result = api_DB_query($SQL, $db, '', '', true);
            }
        }
        //end loop around potential positive receipts not fully allocated already
    }
    if (sizeof($Errors) == 0) {
        $Result = DB_Txn_Commit($db);
        $Errors[0] = 0;
    } else {
        $Result = DB_Txn_Rollback($db);
    }
    return $Errors;
}

        //run the SQL from either of the above possibilites
        if (is_array($sql)) {
            $result = DB_Txn_Begin();
            $tmpErr = _('Could not update unit of measure');
            $tmpDbg = _('The sql that failed was') . ':';
            foreach ($sql as $stmt) {
                $result = DB_query($stmt, $tmpErr, $tmpDbg, true);
                if (!$result) {
                    $InputError = 1;
                    break;
                }
            }
            if ($InputError != 1) {
                $result = DB_Txn_Commit();
            } else {
                $result = DB_Txn_Rollback();
            }
        } else {
            $result = DB_query($sql);
        }
        prnMsg($msg, 'success');
    }
    unset($SelectedMeasureID);
    unset($_POST['SelectedMeasureID']);
    unset($_POST['MeasureName']);
} elseif (isset($_GET['delete'])) {
    //the link to delete a selected record was clicked instead of the submit button
    // PREVENT DELETES IF DEPENDENT RECORDS IN 'stockmaster'
    // Get the original name of the unit of measure the ID is just a secure way to find the unit of measure
    $sql = "SELECT unitname FROM unitsofmeasure\n\t\tWHERE unitid = '" . $SelectedMeasureID . "'";
    $result = DB_query($sql);

                if (!($Result = DB_query($SQL, $db))) {
                    $Error = _('Could not update exchange difference in General Ledger');
                }
                $SQL = "INSERT INTO gltrans (\n\t\t\t\t\t\t\ttype,\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\t) VALUES (\n\t\t\t\t\t\t\t'" . $_SESSION['Alloc']->TransType . "',\n\t\t\t\t\t\t\t'" . $_SESSION['Alloc']->TransNo . "',\n\t\t\t\t\t\t\t'" . $_SESSION['Alloc']->TransDate . "',\n\t\t\t\t\t\t\t'" . $PeriodNo . "',\n\t\t\t\t\t\t\t'" . $_SESSION['CompanyRecord']['debtorsact'] . "',\n\t\t\t\t\t\t\t'',\n\t\t\t\t\t\t\t'" . -$MovtInDiffOnExch . "'\n\t\t\t\t\t\t)";
                if (!($Result = DB_query($SQL, $db))) {
                    $Error = _('Could not update debtors control in General Ledger');
                }
            }
        }
        //
        //========[ COMMIT TRANSACTION ]===========
        //
        if (empty($Error)) {
            $Result = DB_Txn_Commit($db);
        } else {
            $Result = DB_Txn_Rollback($db);
            prnMsg($Error, 'error');
        }
        unset($_SESSION['Alloc']);
        unset($_POST['AllocTrans']);
    }
}
if (isset($_GET['AllocTrans'])) {
    if (isset($_SESSION['Alloc'])) {
        unset($_SESSION['Alloc']->Allocs);
        unset($_SESSION['Alloc']);
    }
    $_SESSION['Alloc'] = new Allocation();
    $_POST['AllocTrans'] = $_GET['AllocTrans'];
    // Set AllocTrans when page first called
    $SQL = "SELECT systypes.typename,\n\t\t\t\tdebtortrans.type,\n\t\t\t\tdebtortrans.transno,\n\t\t\t\tdebtortrans.trandate,\n\t\t\t\tdebtortrans.debtorno,\n\t\t\t\tdebtorsmaster.name,\n\t\t\t\tdebtortrans.rate,\n\t\t\t\t(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount) as total,\n\t\t\t\tdebtortrans.diffonexch,\n\t\t\t\tdebtortrans.alloc,\n\t\t\t\tcurrencies.decimalplaces\n\t\t\tFROM debtortrans INNER JOIN systypes\n\t\t\tON debtortrans.type = systypes.typeid\n\t\t\tINNER JOIN debtorsmaster\n\t\t\tON debtortrans.debtorno = debtorsmaster.debtorno\n\t\t\tINNER JOIN currencies\n\t\t\tON debtorsmaster.currcode=currencies.currabrev\n\t\t\tWHERE debtortrans.id='" . $_POST['AllocTrans'] . "'";

                $InputError = 1;
            }
        } else {
            //item insert failed so set some useful error info
            $InputError = 1;
        }
        if ($InputError == 1) {
            //this row failed so exit loop
            break;
        }
        $row++;
    }
    if ($InputError == 1) {
        //exited loop with errors so rollback
        prnMsg(_('Failed on row ' . $row . '. Batch import has been rolled back.'), 'error');
        DB_Txn_Rollback();
    } else {
        //all good so commit data transaction
        DB_Txn_Commit();
        prnMsg(_('Batch Import of') . ' ' . $FileName . ' ' . _('has been completed. All transactions committed to the database.'), 'success');
        if ($_POST['UpdateIfExists'] == 1) {
            prnMsg(_('Updated:') . ' ' . $UpdatedNum . ' ' . _('Insert:') . $InsertNum);
        } else {
            prnMsg(_('Exist:') . ' ' . $UpdatedNum . ' ' . _('Insert:') . $InsertNum);
        }
    }
    fclose($FileHandle);
} elseif (isset($_POST['gettemplate']) || isset($_GET['gettemplate'])) {
    //download an import template
    echo '<br /><br /><br />"' . implode('","', $FieldHeadings) . '"<br /><br /><br />';
} else {