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>"; } }
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>"; } }
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"]); }