Пример #1
0
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\">";
            print "<tr>";
            print "<td>";
            print "<p><b>Select an item to be credited from the selected invoice - note that amounts can be varied once selected:</b></p>";
            print "<form method=\"" . $obj_invoice_form->method . "\" action=\"" . $obj_invoice_form->action . "\">";
            $obj_invoice_form->render_field("invoice_item");
            print "<br>";
            $obj_invoice_form->render_field("id");
            $obj_invoice_form->render_field("page");
            $obj_invoice_form->render_field("submit");
            print "</form>";
            print "</td>";
            print "</tr>";
            print "</table>";
        } else {
            /*
            	No invoice items!
            */
            format_msgbox("important", "<p>Unable to add any items to this credit note - the selected invoice has no items on it.</p>");
        }
        print "<br>";
    }
}
Пример #2
0
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>";
    }
}
Пример #3
0
 function execute()
 {
     /*
     	Filter selection form
     */
     // fetch existing values
     $this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_as_of_yyyy"] . "-" . $_GET["date_as_of_mm"] . "-" . $_GET["date_as_of_dd"]);
     $this->mode = @security_script_input("/^\\S*\$/", $_GET["mode"]);
     if (!$this->mode) {
         if ($_SESSION["account_reports"]["mode"]) {
             $this->mode = $_SESSION["account_reports"]["mode"];
         } else {
             $this->mode = "Accrual/Invoice";
         }
     }
     if (!$this->date_end || $this->date_end == "--") {
         if ($_SESSION["account_reports"]["date_end"]) {
             $this->date_end = $_SESSION["account_reports"]["date_end"];
         } else {
             $this->date_end = date("Y-m-d");
         }
     }
     // save to session vars
     $_SESSION["account_reports"]["date_end"] = $this->date_end;
     $_SESSION["account_reports"]["mode"] = $this->mode;
     // define form
     $this->obj_form = new form_input();
     $this->obj_form->method = "get";
     $this->obj_form->action = "index.php";
     $this->obj_form->formname = "accounts_report_incomestatement";
     $this->obj_form->language = $_SESSION["user"]["lang"];
     // hidden values
     $structure = NULL;
     $structure["fieldname"] = "page";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $_GET["page"];
     $this->obj_form->add_input($structure);
     // date selection
     $structure = NULL;
     $structure["fieldname"] = "date_as_of";
     $structure["type"] = "date";
     $structure["defaultvalue"] = $this->date_end;
     $this->obj_form->add_input($structure);
     // mode selection
     $structure = NULL;
     $structure["fieldname"] = "mode";
     $structure["type"] = "radio";
     $structure["values"] = array("Accrual/Invoice", "Cash");
     $structure["defaultvalue"] = $this->mode;
     $this->obj_form->add_input($structure);
     // submit
     $structure = NULL;
     $structure["fieldname"] = "submit";
     $structure["type"] = "submit";
     $structure["defaultvalue"] = "Apply Filter Options";
     $this->obj_form->add_input($structure);
     /*
     	Asset Accounts
     */
     // chart details
     $sql_obj = new sql_query();
     $sql_obj->prepare_sql_settable("account_charts");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("code_chart");
     $sql_obj->prepare_sql_addfield("description");
     $sql_obj->prepare_sql_addwhere("chart_type='2'");
     $sql_obj->generate_sql();
     $sql_obj->execute();
     $sql_obj->fetch_array();
     $this->data_assets = $sql_obj->data;
     unset($sql_obj);
     /*
     	Liability Accounts
     */
     // chart details
     $sql_obj = new sql_query();
     $sql_obj->prepare_sql_settable("account_charts");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("code_chart");
     $sql_obj->prepare_sql_addfield("description");
     $sql_obj->prepare_sql_addwhere("chart_type='3'");
     $sql_obj->generate_sql();
     $sql_obj->execute();
     $sql_obj->fetch_array();
     $this->data_liabilities = $sql_obj->data;
     unset($sql_obj);
     /*
     	Equitity Accounts
     */
     // chart details
     $sql_obj = new sql_query();
     $sql_obj->prepare_sql_settable("account_charts");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("code_chart");
     $sql_obj->prepare_sql_addfield("description");
     $sql_obj->prepare_sql_addwhere("chart_type='4'");
     $sql_obj->generate_sql();
     $sql_obj->execute();
     $sql_obj->fetch_array();
     $this->data_equity = $sql_obj->data;
     unset($sql_obj);
     /*
     	Income Charts
     */
     // chart details
     $sql_obj = new sql_query();
     $sql_obj->prepare_sql_settable("account_charts");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("code_chart");
     $sql_obj->prepare_sql_addfield("description");
     $sql_obj->prepare_sql_addwhere("chart_type='5'");
     $sql_obj->generate_sql();
     $sql_obj->execute();
     $sql_obj->fetch_array();
     $this->data_income = $sql_obj->data;
     unset($sql_obj);
     /*
     	Expense Charts
     */
     // chart details
     $sql_obj = new sql_query();
     $sql_obj->prepare_sql_settable("account_charts");
     $sql_obj->prepare_sql_addfield("id");
     $sql_obj->prepare_sql_addfield("code_chart");
     $sql_obj->prepare_sql_addfield("description");
     $sql_obj->prepare_sql_addwhere("chart_type='6'");
     $sql_obj->generate_sql();
     $sql_obj->execute();
     $sql_obj->fetch_array();
     $this->data_expense = $sql_obj->data;
     unset($sql_obj);
     /*
     	Amounts
     	
     	This section fetches the total amounts for the different accounts. This code is a bit different to the invoicestatement code
     	and instead of working on an invoice basis, works on a transaction basis.
     
     	Accural/Invoice:
     		1. Fetch all transactions from account_trans
     		2. Total up credits+debits for each account
     
     	Cash:
     		1. Fetch all transactions from account_trans.
     		2. Total up any ar_pay, ap_pay or gl transactions.
     		2. For all other transactions, do a lookup against the invoice - if the invoice has been paid at all, (either partially
     		   or fully) then include the transaction.
     
     		Note: The behaviour of including partically paid invoces is different to how all the other application features (such as tax
     		collected/paid) work, however it is required in order to have the balance sheet showing correct tax/income amounts.
     
     		This behaviour is also the same as how SQL-Ledger generates balance sheets, which will not confuse users whom have migrated.
     */
     // Run through all the transactions
     $sql_obj = new sql_query();
     $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"]);
                     }
                 }
                 // end of loop through liability charts
                 // run through equity charts
                 if (isset($this->data_equity)) {
                     for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
                         if ($data_trans["chartid"] == $this->data_equity[$i]["id"]) {
                             @($this->data_equity[$i]["amount"] -= $data_trans["amount_debit"]);
                             @($this->data_equity[$i]["amount"] += $data_trans["amount_credit"]);
                         }
                     }
                 }
                 // end of loop through equity charts
                 // run through income charts
                 for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
                     if ($data_trans["chartid"] == $this->data_income[$i]["id"]) {
                         @($this->data_income[$i]["amount"] -= $data_trans["amount_debit"]);
                         @($this->data_income[$i]["amount"] += $data_trans["amount_credit"]);
                     }
                 }
                 // end of loop through income charts
                 // run through expense charts
                 for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
                     if ($data_trans["chartid"] == $this->data_expense[$i]["id"]) {
                         @($this->data_expense[$i]["amount"] += $data_trans["amount_debit"]);
                         @($this->data_expense[$i]["amount"] -= $data_trans["amount_credit"]);
                     }
                 }
                 // end of loop through expense charts
             }
             // end if valid
         }
         // end of transaction loop
     }
     // end if transaction exist
     /*
     	Totals
     */
     // assets
     if ($this->data_assets) {
         for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
             @($this->data_totals["assets"] += $this->data_assets[$i]["amount"]);
         }
     }
     // liabilities
     if ($this->data_liabilities) {
         for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
             @($this->data_totals["liabilities"] += $this->data_liabilities[$i]["amount"]);
         }
     }
     // equity
     if ($this->data_equity) {
         for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
             @($this->data_totals["equity"] += $this->data_equity[$i]["amount"]);
         }
     }
     // income
     if ($this->data_income) {
         for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
             @($this->data_totals["income"] += $this->data_income[$i]["amount"]);
         }
     }
     // expense
     if ($this->data_expense) {
         for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
             @($this->data_totals["expense"] += $this->data_expense[$i]["amount"]);
         }
     }
     // final
     $this->data_totals["current_earnings"] = $this->data_totals["income"] - $this->data_totals["expense"];
     $this->data_totals["equity"] += $this->data_totals["current_earnings"];
     $this->data_totals["liabilities_and_equity"] = $this->data_totals["liabilities"] + $this->data_totals["equity"];
     // formatting
     $this->data_totals["liabilities"] = format_money($this->data_totals["liabilities"]);
     $this->data_totals["assets"] = format_money($this->data_totals["assets"]);
     $this->data_totals["equity"] = format_money($this->data_totals["equity"]);
     $this->data_totals["current_earnings"] = format_money($this->data_totals["current_earnings"]);
     $this->data_totals["liabilities_and_equity"] = format_money($this->data_totals["liabilities_and_equity"]);
 }