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