function action_update()
{
log_debug("inc_credit_refund", "Executing action_update()");
// we must have an ID provided
if (!$this->id) {
log_debug("inc_credit_refund", "No credit refund ID supplied to action_update function");
return 0;
}
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Update the Refund Details
*/
$sql_obj->string = "UPDATE `" . $this->type . "s_credits` SET " . "date_trans='" . $this->data["date_trans"] . "', " . "type='refund', " . "amount_total='-" . $this->data["amount_total"] . "', " . "id_custom='0', " . "id_employee='" . $this->data["id_employee"] . "', " . "id_" . $this->type . "='" . $this->data["id_" . $this->type] . "', " . "description='" . $this->data["description"] . "' " . "WHERE id='" . $this->id . "' LIMIT 1";
if (!$sql_obj->execute()) {
$sql_obj->trans_rollback();
log_write("error", "inc_credit_refund", "Unable to update database with credit refund entry. No changes have been made.");
return 0;
}
/*
Update Ledger Records
*/
if ($this->type == "customer") {
// delete existing records
$sql_obj->string = "DELETE FROM account_trans WHERE (type='ar_refund') AND customid='" . $this->id . "'";
$sql_obj->execute();
// add new ledger records
ledger_trans_add("credit", "ar_refund", $this->id, $this->data["date_trans"], $this->data["account_asset"], $this->data["amount_total"], 'CREDIT REFUND', $this->data["description"]);
ledger_trans_add("debit", "ar_refund", $this->id, $this->data["date_trans"], $this->data["account_dest"], $this->data["amount_total"], 'CREDIT REFUND', $this->data["description"]);
} else {
// delete existing records
$sql_obj->string = "DELETE FROM account_trans WHERE (type='ap_refund') AND customid='" . $this->id . "'";
$sql_obj->execute();
// add new ledger records
ledger_trans_add("debit", "ar_refund", $this->id, $this->data["date_trans"], $this->data["account_asset"], $this->data["amount_total"], 'CREDIT REFUND', $this->data["description"]);
ledger_trans_add("credit", "ar_refund", $this->id, $this->data["date_trans"], $this->data["account_dest"], $this->data["amount_total"], 'CREDIT REFUND', $this->data["description"]);
}
/*
Commit
*/
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "inc_credit_refund", "An error occured whilst attempting to update credit refund. No changes have been made.");
return 0;
} else {
$sql_obj->trans_commit();
return 1;
}
}
function action_update_ledger()
{
log_debug("invoice_items", "Executing action_update_ledger()");
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
// fetch key information from invoice
$sql_inv_obj = new sql_query();
$sql_inv_obj->string = "SELECT id, dest_account, date_trans FROM account_" . $this->type_invoice . " WHERE id='" . $this->id_invoice . "' LIMIT 1";
$sql_inv_obj->execute();
$sql_inv_obj->fetch_array();
// remove all the old ledger entries belonging to this invoice
$sql_obj->string = "DELETE FROM `account_trans` WHERE customid='" . $this->id_invoice . "' AND (type='" . $this->type_invoice . "' || type='" . $this->type_invoice . "_pay' || type='" . $this->type_invoice . "_tax')";
$sql_obj->execute();
/*
PROCESS NON-PAYMENT ITEMS
For all normal items, we want to aggregate the totals per chart then add ledger entries
per-invoice, not per-item.
Then we create the following in the ledger:
AR INVOICES
* A single debit from the AR account
* A single credit to each different account for the items.
AP INVOICES
* A single credit to the AP account
* A single debit to each different account for the items
Payment items need to be handled differently - see code further down.
*/
// add up the total for the AR entry
$amount = 0;
// Fetch totals per chart from the items table.
$sql_obj = new sql_query();
$sql_obj->string = "SELECT chartid, type, SUM(amount) as amount FROM `account_items` WHERE invoicetype='" . $this->type_invoice . "' AND invoiceid='" . $this->id_invoice . "' AND type!='payment' GROUP BY chartid";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $item_data) {
// set trans type
if ($item_data["type"] == "tax") {
$trans_type = $this->type_invoice . "_tax";
} else {
$trans_type = $this->type_invoice;
}
// create ledger entry for this account
switch ($this->type_invoice) {
case "ar_credit":
ledger_trans_add("debit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ap_credit":
ledger_trans_add("credit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ap":
ledger_trans_add("debit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ar":
default:
ledger_trans_add("credit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
}
// add up the total for the AR entry.
$amount += $item_data["amount"];
}
switch ($this->type_invoice) {
case "ap":
// create credit from AP account
ledger_trans_add("credit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ap_credit":
// create debit to AP account
ledger_trans_add("debit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ar_credit":
// create credit from AR account
ledger_trans_add("credit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ar":
default:
// create debit to AR account
ledger_trans_add("debit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
}
}
/*
PROCESS PAYMENT ITEMS
Payment entries are different to other items, in that we need to add stand alone
entries for each payment item, since payments can be made on different dates, so therefore
can not be aggregated.
*/
// run though each payment item
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT id, chartid, amount, description FROM `account_items` WHERE invoicetype='" . $this->type_invoice . "' AND invoiceid='" . $this->id_invoice . "' AND type='payment'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data) {
// fetch information from options
$sql_option_obj = new sql_query();
$sql_option_obj->string = "SELECT option_name, option_value FROM account_items_options WHERE itemid='" . $data["id"] . "'";
$sql_option_obj->execute();
$sql_option_obj->fetch_array();
foreach ($sql_option_obj->data as $option_data) {
if ($option_data["option_name"] == "SOURCE") {
$data["source"] = $option_data["option_value"];
}
if ($option_data["option_name"] == "DATE_TRANS") {
$data["date_trans"] = $option_data["option_value"];
}
if ($option_data["option_name"] == "CREDIT") {
$data["credit"] = $option_data["option_value"];
}
}
// addslashes to memo & source fields - since we have pulled data from the DB, that data could
// contains quotation marks or other unacceptable input, so we must process it
$data["description"] = addslashes($data["description"]);
$data["source"] = addslashes($data["source"]);
if ($this->type_invoice == "ap") {
// we need to credit the destination account for the payment to come from and debit the AP account
ledger_trans_add("credit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $data["chartid"], $data["amount"], $data["source"], $data["description"]);
ledger_trans_add("debit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $sql_inv_obj->data[0]["dest_account"], $data["amount"], $data["source"], $data["description"]);
// if a credit, we need to subtract from vendor credit pool
if (!empty($data["credit"])) {
$id_vendor = sql_get_singlevalue("SELECT vendorid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$id_employee = sql_get_singlevalue("SELECT employeeid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$sql_obj->string = "DELETE FROM vendors_credits WHERE id_vendor='" . $id_vendor . "' AND type='payment' AND id_custom='" . $data["id"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->string = "INSERT INTO vendors_credits (date_trans, type, amount_total, id_custom, id_employee, id_vendor, description) VALUES ('" . $data["date_trans"] . "', 'payment', '-" . $data["amount"] . "', '" . $data["id"] . "', '" . $id_employee . "', '" . $id_vendor . "', '" . $data["description"] . "')";
$sql_obj->execute();
}
} else {
// we need to debit the destination account for the payment to go into and credit the AR account
ledger_trans_add("debit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $data["chartid"], $data["amount"], $data["source"], $data["description"]);
ledger_trans_add("credit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $sql_inv_obj->data[0]["dest_account"], $data["amount"], $data["source"], $data["description"]);
// if a credit, we need to subtract from customer credit pool
if (!empty($data["credit"])) {
$id_customer = sql_get_singlevalue("SELECT customerid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$id_employee = sql_get_singlevalue("SELECT employeeid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$sql_obj->string = "DELETE FROM customers_credits WHERE id_customer='" . $id_customer . "' AND type='payment' AND id_custom='" . $data["id"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->string = "INSERT INTO customers_credits (date_trans, type, amount_total, id_custom, id_employee, id_customer, description) VALUES ('" . $data["date_trans"] . "', 'payment', '-" . $data["amount"] . "', '" . $data["id"] . "', '" . $id_employee . "', '" . $id_customer . "', '" . $data["description"] . "')";
$sql_obj->execute();
}
}
}
}
/*
Commit
*/
$sql_obj = new sql_query();
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "invoice_items", "An error occured whilst attempting to update ledger for invoice. No changes have been made.");
return 0;
} else {
$sql_obj->trans_commit();
return 1;
}
}
function action_update_rows()
{
log_debug("inc_gl", "Executing action_update_rows()");
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Delete existing transaction rows
*/
$sql_obj->string = "DELETE FROM account_trans WHERE type='gl' AND customid='" . $this->id . "'";
$sql_obj->execute();
/*
Create new transaction rows
*/
// run through all transactions
for ($i = 0; $i <= $this->data["num_trans"]; $i++) {
// if enabled, overwrite any description fields of transactions with the master one
if ($this->data["description_useall"] == "on") {
$this->data["trans"][$i]["description"] = $this->data["description"];
}
// post transaction
if ($this->data["trans"][$i]["account"]) {
if ($this->data["trans"][$i]["debit"] != "0.00") {
if (!ledger_trans_add("debit", "gl", $this->id, $this->data["date_trans"], $this->data["trans"][$i]["account"], $this->data["trans"][$i]["debit"], $this->data["trans"][$i]["source"], $this->data["trans"][$i]["description"])) {
$sql_obj->trans_rollback();
return 0;
}
} else {
if (!ledger_trans_add("credit", "gl", $this->id, $this->data["date_trans"], $this->data["trans"][$i]["account"], $this->data["trans"][$i]["credit"], $this->data["trans"][$i]["source"], $this->data["trans"][$i]["description"])) {
$sql_obj->trans_rollback();
return 0;
}
}
}
}
/*
Commit
*/
if (error_check()) {
$sql_obj->trans_rollback();
return 0;
} else {
$sql_obj->trans_commit();
return 1;
}
}
