PHP sql_query::prepare_sql_setlimit方法代码示例

function credit_render_invoiceselect($type, $id, $processpage)
{
    log_debug("inc_credits", "credit_render_summarybox({$type}, {$id})");
    // fetch credit information
    $sql_obj = new sql_query();
    $sql_obj->prepare_sql_settable("account_{$type}");
    if ($type == "ar_credit") {
        $sql_obj->prepare_sql_addfield("date_sent");
        $sql_obj->prepare_sql_addfield("sentmethod");
    }
    $sql_obj->prepare_sql_addfield("code_credit");
    $sql_obj->prepare_sql_addfield("amount_total");
    $sql_obj->prepare_sql_addfield("invoiceid");
    $sql_obj->prepare_sql_addfield("locked");
    $sql_obj->prepare_sql_addwhere("id='{$id}'");
    $sql_obj->prepare_sql_setlimit("1");
    $sql_obj->generate_sql();
    $sql_obj->execute();
    if ($sql_obj->num_rows()) {
        $sql_obj->fetch_array();
        if ($sql_obj->data[0]["locked"]) {
            // credit note is locked, nothing todo
            return 1;
        }
        /*
        	Select Invoice Items
        */
        $invoice_type = "unknown";
        if ($type == "ar_credit") {
            $invoice_type = "ar";
        } elseif ($type == "ap_credit") {
            $invoice_type = "ap";
        }
        $sql_invoice_obj = new sql_query();
        $sql_invoice_obj->string = "SELECT id as itemid, type, customid, chartid, quantity, units, price, amount, description FROM account_items WHERE invoiceid='" . $sql_obj->data[0]["invoiceid"] . "' AND invoicetype='" . $invoice_type . "' AND type!='payment' AND type!='tax'";
        $sql_invoice_obj->execute();
        if ($sql_invoice_obj->num_rows()) {
            $sql_invoice_obj->fetch_array();
        }
        /*
        	Create Form
        */
        $obj_invoice_form = new form_input();
        $obj_invoice_form->formname = $type . "_invoiceselect";
        $obj_invoice_form->language = $_SESSION["user"]["lang"];
        $obj_invoice_form->action = "index.php";
        $obj_invoice_form->method = "GET";
        // ID
        $structure = NULL;
        $structure["fieldname"] = "id";
        $structure["type"] = "hidden";
        $structure["defaultvalue"] = $id;
        $obj_invoice_form->add_input($structure);
        $structure = NULL;
        $structure["fieldname"] = "page";
        $structure["type"] = "hidden";
        $structure["defaultvalue"] = $processpage;
        $obj_invoice_form->add_input($structure);
        // submit
        $structure = NULL;
        $structure["fieldname"] = "submit";
        $structure["type"] = "submit";
        $structure["defaultvalue"] = "submit_add_credit_item";
        $obj_invoice_form->add_input($structure);
        /*
        	Generate Items Radio Array
        */
        if ($sql_invoice_obj->num_rows()) {
            $structure = NULL;
            $structure["fieldname"] = "invoice_item";
            $structure["type"] = "radio";
            foreach ($sql_invoice_obj->data as $data_invoice) {
                $description = $data_invoice["description"];
                switch ($data_invoice["type"]) {
                    case "standard":
                        $description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data_invoice["chartid"] . "' LIMIT 1");
                        break;
                    case "product":
                        $description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_product, name_product) as value FROM products WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
                        break;
                    case "service":
                    case "service_usage":
                        $description = sql_get_singlevalue("SELECT name_service as value FROM services WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
                        break;
                    default:
                        $description = "unknown item";
                        break;
                }
                $description .= " <i>" . $data_invoice["description"] . "</i>";
                $description .= " [" . format_money($data_invoice["amount"]) . " exc tax]";
                $structure["values"][] = $data_invoice["itemid"];
                $structure["translations"][$data_invoice["itemid"]] = $description;
            }
            $obj_invoice_form->add_input($structure);
        }
        /*
        	Render Form
        */
        if ($sql_invoice_obj->num_rows()) {
            print "<table width=\"100%\" class=\"table_highlight_info\">";
//.........这里部分代码省略.........

function invoice_render_summarybox($type, $id)
{
    log_debug("inc_invoices", "invoice_render_summarybox({$type}, {$id})");
    // fetch invoice information
    $sql_obj = new sql_query();
    $sql_obj->prepare_sql_settable("account_{$type}");
    if ($type == "ar") {
        $sql_obj->prepare_sql_addfield("date_sent");
        $sql_obj->prepare_sql_addfield("sentmethod");
    }
    $sql_obj->prepare_sql_addfield("code_invoice");
    $sql_obj->prepare_sql_addfield("amount_total");
    $sql_obj->prepare_sql_addfield("amount_paid");
    $sql_obj->prepare_sql_addwhere("id='{$id}'");
    $sql_obj->prepare_sql_setlimit("1");
    $sql_obj->generate_sql();
    $sql_obj->execute();
    if ($sql_obj->num_rows()) {
        $sql_obj->fetch_array();
        // check for presence of invoice items
        $sql_item_obj = new sql_query();
        $sql_item_obj->string = "SELECT id FROM account_items WHERE invoicetype='{$type}' AND invoiceid='{$id}' LIMIT 1";
        $sql_item_obj->execute();
        if (!$sql_item_obj->num_rows()) {
            print "<table width=\"100%\" class=\"table_highlight_important\">";
            print "<tr>";
            print "<td>";
            print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " has no items on it</b>";
            print "<p>This invoice is currently empty, add some items to it using the Invoice Items page.</p>";
            print "</td>";
            print "</tr>";
            print "</table>";
        } else {
            if ($sql_obj->data[0]["amount_paid"] == $sql_obj->data[0]["amount_total"]) {
                print "<table width=\"100%\" class=\"table_highlight_open\">";
                print "<tr>";
                print "<td>";
                print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is closed (fully paid).</b>";
                print "<p>This invoice has been fully paid and no further action is required.</p>";
                print "</td>";
                print "</tr>";
                print "</table>";
            } else {
                print "<table width=\"100%\" class=\"table_highlight_important\">";
                print "<tr>";
                print "<td>";
                print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is open (unpaid).</b>";
                print "<table cellpadding=\"4\">";
                print "<tr>";
                print "<td>Total Due:</td>";
                print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
                print "</tr>";
                print "<tr>";
                print "<td>Total Paid:</td>";
                print "<td>" . format_money($sql_obj->data[0]["amount_paid"]) . "</td>";
                print "</tr>";
                $amount_due = $sql_obj->data[0]["amount_total"] - $sql_obj->data[0]["amount_paid"];
                print "<tr>";
                print "<td>Amount Due:</td>";
                print "<td>" . format_money($amount_due) . "</td>";
                print "</tr>";
                if ($type == "ar") {
                    print "<tr>";
                    print "<td>Date Sent:</td>";
                    if ($sql_obj->data[0]["sentmethod"] == "") {
                        print "<td><i>Has not been sent to customer</i></td>";
                    } else {
                        print "<td>" . $sql_obj->data[0]["date_sent"] . " (" . $sql_obj->data[0]["sentmethod"] . ")</td>";
                    }
                    print "</tr>";
                }
                print "</tr></table>";
                print "</td>";
                print "</tr>";
                print "</table>";
            }
        }
        print "<br>";
    }
}

//.........这里部分代码省略.........
     $sql_obj->prepare_sql_settable("account_trans");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("type");
     $sql_obj->prepare_sql_addfield("customid");
     $sql_obj->prepare_sql_addfield("chartid");
     $sql_obj->prepare_sql_addfield("amount_debit");
     $sql_obj->prepare_sql_addfield("amount_credit");
     // date options
     if ($this->date_end) {
         $sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
     }
     // run through transaction entries
     $sql_obj->generate_sql();
     $sql_obj->execute();
     if ($sql_obj->num_rows()) {
         $sql_obj->fetch_array();
         foreach ($sql_obj->data as $data_trans) {
             log_debug("balancesheet", "Processing transaction " . $data_trans["id"] . " with type " . $data_trans["type"] . "");
             $valid = 0;
             if ($this->mode == "Cash") {
                 // CASH
                 if ($data_trans["type"] == "ar_pay" || $data_trans["type"] == "ap_pay" || $data_trans["type"] == "gl") {
                     $valid = 1;
                 } else {
                     // check if the transaction invoice has any payments or not
                     $sql_invoice_obj = new sql_query();
                     if ($data_trans["type"] == "ap" || $data_trans["type"] == "ap_tax") {
                         $sql_invoice_obj->prepare_sql_settable("account_ap");
                     } else {
                         $sql_invoice_obj->prepare_sql_settable("account_ar");
                     }
                     $sql_invoice_obj->prepare_sql_addfield("amount_paid");
                     $sql_invoice_obj->prepare_sql_addwhere("id='" . $data_trans["customid"] . "'");
                     $sql_invoice_obj->prepare_sql_setlimit("1");
                     $sql_invoice_obj->generate_sql();
                     $sql_invoice_obj->execute();
                     if ($sql_invoice_obj->num_rows()) {
                         $sql_invoice_obj->fetch_array();
                         if ($sql_invoice_obj->data[0]["amount_paid"] > 0) {
                             // invoice has some amount of payment against it, and should therefore be displayed.
                             $valid = 1;
                         }
                     } else {
                         log_write("error", "balancesheet", "Unable to find parent invoice (" . $data_trans["customid"] . ") for transaction " . $data_trans["id"] . " - Database might be damanged.");
                     }
                     unset($sql_invoice_obj);
                 }
             } else {
                 // ACCURAL/INVOICE
                 $valid = 1;
             }
             if ($valid) {
                 log_debug("balancesheet", "Transaction is valid - chartid: " . $data_trans["chartid"] . ", credit: " . $data_trans["amount_credit"] . ", debit: " . $data_trans["amount_debit"] . "");
                 // run through asset charts
                 for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
                     if ($data_trans["chartid"] == $this->data_assets[$i]["id"]) {
                         @($this->data_assets[$i]["amount"] += $data_trans["amount_debit"]);
                         @($this->data_assets[$i]["amount"] -= $data_trans["amount_credit"]);
                     }
                 }
                 // end of loop through asset charts
                 // run through liability charts
                 for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
                     if ($data_trans["chartid"] == $this->data_liabilities[$i]["id"]) {
                         @($this->data_liabilities[$i]["amount"] -= $data_trans["amount_debit"]);
                         @($this->data_liabilities[$i]["amount"] += $data_trans["amount_credit"]);