Esempio n. 1
0
 function execute()
 {
     // establish a new table object
     $this->obj_table = new table();
     $this->obj_table->language = $_SESSION["user"]["lang"];
     $this->obj_table->tablename = "account_gl";
     // define all the columns and structure
     $this->obj_table->add_column("standard", "date_trans", "account_trans.date_trans");
     $this->obj_table->add_column("standard", "code_reference", "NONE");
     $this->obj_table->add_column("standard", "description", "account_trans.memo");
     $this->obj_table->add_column("standard", "source", "account_trans.source");
     $this->obj_table->add_column("money", "debit", "account_trans.amount_debit");
     $this->obj_table->add_column("money", "credit", "account_trans.amount_credit");
     $this->obj_table->add_column("standard", "code_chart", "CONCAT_WS('--', account_charts.code_chart, account_charts.description)");
     // defaults
     $this->obj_table->columns = array("date_trans", "code_reference", "description", "source", "debit", "credit", "code_chart");
     $this->obj_table->columns_order = array("date_trans");
     $this->obj_table->columns_order_options = array("date_trans", "description", "source", "code_chart");
     // totals
     $this->obj_table->total_columns = array("debit", "credit");
     // define SQL structure
     $this->obj_table->sql_obj->prepare_sql_settable("account_trans");
     $this->obj_table->sql_obj->prepare_sql_addfield("id", "account_trans.id");
     $this->obj_table->sql_obj->prepare_sql_addfield("type", "account_trans.type");
     $this->obj_table->sql_obj->prepare_sql_addfield("customid", "account_trans.customid");
     $this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN account_charts ON account_charts.id = account_trans.chartid");
     // acceptable filter options
     $structure = NULL;
     $structure["fieldname"] = "date_start";
     $structure["type"] = "date";
     $structure["sql"] = "date_trans >= 'value'";
     $this->obj_table->add_filter($structure);
     $structure = NULL;
     $structure["fieldname"] = "date_end";
     $structure["type"] = "date";
     $structure["sql"] = "date_trans <= 'value'";
     $this->obj_table->add_filter($structure);
     $structure = NULL;
     $structure["fieldname"] = "searchbox";
     $structure["type"] = "input";
     $structure["sql"] = "(memo LIKE '%value%' OR source LIKE '%value%')";
     $this->obj_table->add_filter($structure);
     // load options
     $this->obj_table->load_options_form();
     // set default of 1 month range if no range has been set
     if (!isset($this->obj_table->filter["filter_date_start"]["defaultvalue"])) {
         $this->obj_table->filter["filter_date_start"]["defaultvalue"] = time_calculate_monthdate_first();
         $this->obj_table->filter["filter_date_end"]["defaultvalue"] = time_calculate_monthdate_last();
     }
     if ($this->obj_table->filter["filter_date_start"]["defaultvalue"] && $this->obj_table->filter["filter_date_end"]["defaultvalue"]) {
         // fetch all the transaction information
         $this->obj_table->generate_sql();
         // add ordering rule to order by the ID - this causes all the transactions
         // to be sorted by the other that they were addded to the database once they have
         // been sorted by date. If this was not done, the accounts look odd with transactions being
         // out of order.
         $this->obj_table->sql_obj->string .= ", id ASC";
         $this->obj_table->load_data_sql();
     }
 }
Esempio n. 2
0
 function execute()
 {
     log_debug("taxes_report_transactions", "Executing execute()");
     if ($this->mode == "collected") {
         $this->type = "ar";
     } elseif ($this->mode == "paid") {
         $this->type = "ap";
     } else {
         return 0;
     }
     /*
     	Define table structure
     */
     $this->obj_table = new table();
     // configure the table
     $this->obj_table->language = $_SESSION["user"]["lang"];
     $this->obj_table->tablename = "tax_report_" . $this->type;
     // define all the columns and structure
     $this->obj_table->add_column("date", "date_trans", "account_" . $this->type . ".date_trans");
     $this->obj_table->add_column("standard", "code_invoice", "account_" . $this->type . ".code_invoice");
     if ($this->type == "ap") {
         $this->obj_table->add_column("standard", "name_vendor", "vendors.name_vendor");
     } else {
         $this->obj_table->add_column("standard", "name_customer", "customers.name_customer");
     }
     $this->obj_table->add_column("money", "amount", "account_" . $this->type . ".amount");
     $this->obj_table->add_column("money", "amount_tax", "NONE");
     // total rows
     $this->obj_table->total_columns = array("amount", "amount_tax");
     $this->obj_table->total_rows = array("amount", "amount_tax");
     // defaults
     if ($this->type == "ap") {
         $this->obj_table->columns = array("date_trans", "code_invoice", "name_vendor", "amount", "amount_tax");
         $this->obj_table->columns_order = array("date_trans", "name_vendor");
     } else {
         $this->obj_table->columns = array("date_trans", "code_invoice", "name_customer", "amount", "amount_tax");
         $this->obj_table->columns_order = array("date_trans", "name_customer");
     }
     // define SQL structure
     $this->obj_table->sql_obj->prepare_sql_settable("account_" . $this->type);
     if ($this->type == "ap") {
         $this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN vendors ON account_" . $this->type . ".vendorid = vendors.id");
     } else {
         $this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN customers ON account_" . $this->type . ".customerid = customers.id");
     }
     $this->obj_table->sql_obj->prepare_sql_addfield("id", "account_" . $this->type . ".id");
     $this->obj_table->sql_obj->prepare_sql_addfield("amount_total", "account_" . $this->type . ".amount_total");
     /*
     	Filter Options
     */
     // acceptable filter options
     $this->obj_table->add_fixed_option("id", $this->taxid);
     $structure = NULL;
     $structure["fieldname"] = "date_start";
     $structure["type"] = "date";
     $this->obj_table->add_filter($structure);
     $structure = NULL;
     $structure["fieldname"] = "date_end";
     $structure["type"] = "date";
     $this->obj_table->add_filter($structure);
     $structure = NULL;
     $structure["fieldname"] = "mode";
     $structure["type"] = "radio";
     $structure["values"] = array("Accrual/Invoice", "Cash");
     $this->obj_table->add_filter($structure);
     // load options
     $this->obj_table->load_options_form();
     // set default of 1 month range if no range has been set
     if (empty($this->obj_table->filter["filter_date_start"]["defaultvalue"])) {
         $this->obj_table->filter["filter_date_start"]["defaultvalue"] = time_calculate_monthdate_first();
         $this->obj_table->filter["filter_date_end"]["defaultvalue"] = time_calculate_monthdate_last();
     }
     /*
     	Create SQL filters from user-selected options
     
     	These filters are too complex to perform using the standard SQL based filtering
     	of the tables class proved by amberphplib, so we have to use this code
     	to manipulate the class data structure directly
     */
     // depending on the filter options, generate SQL filtering rules
     if (isset($this->obj_table->filter["filter_mode"]["defaultvalue"]) && $this->obj_table->filter["filter_mode"]["defaultvalue"] == "Cash") {
         /*
         	Cash Mode
         
         	We need to work out all the payments in this period, then create an array of all the invoices
         	that they belong to.
         */
         // store invoice IDs here
         $invoice_ids = NULL;
         // select all payments in the desired time period
         $sql_obj = new sql_query();
         $sql_obj->string = "SELECT\n\t\t\t\t\t\titemid\n\t\t\t\t\t\tFROM account_items_options\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\toption_name='DATE_TRANS'\n\t\t\t\t\t\tAND\n\t\t\t\t\t\toption_value >= '" . $this->obj_table->filter["filter_date_start"]["defaultvalue"] . "'\n\t\t\t\t\t\tAND\n\t\t\t\t\t\toption_value <= '" . $this->obj_table->filter["filter_date_end"]["defaultvalue"] . "'";
         $sql_obj->execute();
         if ($sql_obj->num_rows()) {
             $sql_obj->fetch_array();
             foreach ($sql_obj->data as $data) {
                 // fetch item details
                 $sql_item_obj = new sql_query();
                 $sql_item_obj->string = "SELECT invoiceid FROM account_items WHERE id='" . $data["itemid"] . "' AND invoicetype='" . $this->type . "' AND type='payment' LIMIT 1";
                 $sql_item_obj->execute();
                 if ($sql_item_obj->num_rows()) {
                     $sql_item_obj->fetch_array();
                     $invoice_ids[$sql_item_obj->data[0]["invoiceid"]] = "on";
                 }
             }
         }
         unset($sql_obj);
         unset($sql_item_obj);
         // select invoices with payments within the date range
         if ($invoice_ids) {
             $invoice_ids_keys = array_keys($invoice_ids);
             $invoice_ids_count = count($invoice_ids_keys);
             $invoice_ids_sql = "";
             $i = 0;
             foreach ($invoice_ids_keys as $id) {
                 $i++;
                 if ($i == $invoice_ids_count) {
                     $invoice_ids_sql .= "account_" . $this->type . ".id='{$id}' ";
                 } else {
                     $invoice_ids_sql .= "account_" . $this->type . ".id='{$id}' OR ";
                 }
             }
             $this->obj_table->sql_obj->prepare_sql_addwhere("({$invoice_ids_sql})");
             // fetch records
             $this->obj_table->generate_sql();
             $this->obj_table->load_data_sql();
         }
     } else {
         /*
         	Invoice Mode
         
         	Simply select all invoices that fall in the provided date period.
         */
         // select all invoices in the desired time period
         $this->obj_table->sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->obj_table->filter["filter_date_start"]["defaultvalue"] . "'");
         $this->obj_table->sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->obj_table->filter["filter_date_end"]["defaultvalue"] . "'");
         // fetch records
         $this->obj_table->generate_sql();
         $this->obj_table->load_data_sql();
     }
     /*
     	Generate tax totals per invoice
     */
     if ($this->obj_table->data_num_rows) {
         $deleted_invoices = 0;
         if (isset($this->obj_table->filter["filter_mode"]["defaultvalue"]) && $this->obj_table->filter["filter_mode"]["defaultvalue"] == "Cash") {
             /*
             	Cash Mode
             
             	The main SQL query has returned a number of invoices, all which have at least one payment in the selected
             	date period.
             
             	We now need to process these invoices in one of two different ways, depending on the payments.
             
             	1. If the invoice has been fully paid, with all the payments falling into the selected date period, we can treat
             	   the invoice in the same way as the Invoice/Accural mode.
             
             	2. If the invoice has not been fully paid, or if only some of the payments fall in the selected date period, we
             	   need to calculate the tax share of the payments which *do* fall into their period and adjust the output to this value.
             
             	These calculations are nessacary to correctly comply with sales tax legislation such as the New Zealand GST laws which
             	require GST to be paid upon recipt of any payment on the period when the payment occurs, regardless whether or not
             	the invoice is fully paid.
             */
             for ($i = 0; $i < $this->obj_table->data_num_rows; $i++) {
                 log_debug("page", "Calculating taxes on invoice " . $this->obj_table->data[$i]["code_invoice"] . " on cash method");
                 // create payment total
                 $payment_total = 0;
                 // fetch all the payments for this invoice
                 $sql_items_obj = new sql_query();
                 $sql_items_obj->string = "SELECT\n\t\t\t\t\t\t\t\t\tid,\n\t\t\t\t\t\t\t\t\tamount\n\t\t\t\t\t\t\t\t\tFROM account_items\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tinvoicetype='{$this->type}'\n\t\t\t\t\t\t\t\t\tAND invoiceid='" . $this->obj_table->data[$i]["id"] . "'\n\t\t\t\t\t\t\t\t\tAND type='payment'";
                 $sql_items_obj->execute();
                 $sql_items_obj->fetch_array();
                 foreach ($sql_items_obj->data as $data_item) {
                     // check if payment belongs to this date range
                     $sql_obj = new sql_query();
                     $sql_obj->string = "SELECT\n\t\t\t\t\t\t\t\t\t\tid\n\t\t\t\t\t\t\t\t\t\tFROM account_items_options\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\titemid='" . $data_item["id"] . "'\n\t\t\t\t\t\t\t\t\t\tAND option_name='DATE_TRANS'\n\t\t\t\t\t\t\t\t\t\tAND option_value >= '" . $this->obj_table->filter["filter_date_start"]["defaultvalue"] . "'\n\t\t\t\t\t\t\t\t\t\tAND option_value <= '" . $this->obj_table->filter["filter_date_end"]["defaultvalue"] . "'\n\t\t\t\t\t\t\t\t\t\tLIMIT 1";
                     $sql_obj->execute();
                     if ($sql_obj->num_rows()) {
                         // add payment to total
                         $payment_total += $data_item["amount"];
                     }
                 }
                 // fetch total of the tax for this invoice
                 $sql_obj = new sql_query();
                 $sql_obj->string = "SELECT SUM(amount) as amount FROM account_items WHERE type='tax' AND customid='" . $this->taxid . "' AND invoicetype='" . $this->type . "' AND invoiceid='" . $this->obj_table->data[$i]["id"] . "'";
                 $sql_obj->execute();
                 $sql_obj->fetch_array();
                 if (!$sql_obj->data[0]["amount"]) {
                     log_debug("page", "Invoice does not have this tax, removing invoice from the list");
                     // delete this invoice from the list, since it has no tax items of the type that we want
                     unset($this->obj_table->data[$i]);
                     $deleted_invoices++;
                 } else {
                     // add the tax amount
                     $this->obj_table->data[$i]["amount_tax"] = $sql_obj->data[0]["amount"];
                     // is the invoice fully paid?
                     if ($payment_total == $this->obj_table->data[$i]["amount_total"]) {
                         log_debug("page", "Invoice is fully paid, using tax totals from DB");
                         // nothing to do, we can use the tax amount we gained from the database
                     } elseif ($payment_total > $this->obj_table->data[$i]["amount_total"]) {
                         log_debug("page", "Invoice has been overpaid and will not be included in the results.");
                         unset($this->obj_table->data[$i]);
                         $deleted_invoices++;
                     } else {
                         log_debug("page", "Invoice is not fully paid, calculating tax based on amount paid.");
                         /*
                         	Work out the payment amount with no tax
                         
                         	To do this, we divide the payment by the invoice total and then multiply by the invoice non-tax total.
                         
                         	Example:
                         		Invoice of $100. Total with tax of $112.50
                         
                         		Payment of $20 made.
                         
                         		20 / 112.50 = 0.1777
                         
                         		0.1777 * 100 = 17.777
                         
                         		Therefore the amount before tax is $17.78
                         
                         		We can further prove the calculation by applying the original tax (12.5%) to show:
                         		17.7777 * 1.125 == $20
                         */
                         $payment_total = $payment_total / $this->obj_table->data[$i]["amount_total"] * $this->obj_table->data[$i]["amount"];
                         /*
                         	The invoice was not fully paid in this period - in order to provide valid tax reporting we need to work out
                         	the amount of tax for the amount paid.
                         
                         	We do this by fetching the tax total of the invoice (for the selected tax type), dividing it by the invoice
                         	amount and then multiplying by the payment.
                         
                         	For example:
                         
                         		Invoice of $100 with tax of 12.5% == $112.5
                         		
                         		12.5 / 100 == 0.125
                         
                         		Payment of $20 made
                         
                         		0.125 * $20 = 2.5
                         
                         		Therefore, for a payment of $20, the tax is $2.50
                         
                         	This also works correctly for fixed-price taxes:
                         		
                         		Invoice of $100 with tax of $50 == $150
                         
                         		50 / 100 = 0.5
                         
                         		Payment of $20 made
                         
                         		0.5 * 20 = 10
                         
                         		Therefore, for a payment of $20, the tax is $10
                         
                         
                         	This method is better than using the taxrate percentage in the DB, since that may have been changed
                         	since the invoice was created, or possibly the tax amount has been adjusted if this is an AP invoice.
                         */
                         // work out the tax rate
                         $taxrate = $this->obj_table->data[$i]["amount_tax"] / $this->obj_table->data[$i]["amount"];
                         log_debug("page", "Calculated Taxrate is: " . $taxrate);
                         // update the invoice details for display
                         $this->obj_table->data[$i]["amount"] = $payment_total;
                         $this->obj_table->data[$i]["amount_tax"] = $taxrate * $payment_total;
                     }
                 }
             }
         } else {
             log_debug("page", "Calculating taxes for invoice on invoice/accural method");
             /*
             	Invoice / Accural Mode
             
             	We can not just use the tax amount on the invoice, since the `account_$this->type.total_tax` field may include amounts
             	of other taxes, so we need to total up the tax ourselves and work out the sum.
             
             	There are two approaches to handling this:
             	
             	1. Fetch totals for the selected tax type for all invoices into
             	   an array, and then pull the data we want from that
             	   
             	2. Fetch total for each invoice by using a seporate sql query. This is
             	   the approach chosen here.
             
             	Option #1 will be more efficent initally, but could cause huge slowdowns once users
             	end up with large databases of many/complex invoices.
             
             	Option #2 may be a bit inefficent on large queries, but at worst the user will most
             	likely only be looking at between 1 to 12 months worth of invoices.
             
             	Possibly some tests should be carried out in order to determine the optimal query method
             	here.
             */
             for ($i = 0; $i < $this->obj_table->data_num_rows; $i++) {
                 $sql_obj = new sql_query();
                 $sql_obj->string = "SELECT SUM(amount) as amount FROM account_items WHERE type='tax' AND customid='" . $this->taxid . "' AND invoicetype='" . $this->type . "' AND invoiceid='" . $this->obj_table->data[$i]["id"] . "'";
                 $sql_obj->execute();
                 $sql_obj->fetch_array();
                 if (!$sql_obj->data[0]["amount"]) {
                     // delete this invoice from the list, since it has no tax items of the type that we want
                     unset($this->obj_table->data[$i]);
                     $deleted_invoices++;
                 } else {
                     // add the tax amount
                     $this->obj_table->data[$i]["amount_tax"] = $sql_obj->data[0]["amount"];
                 }
             }
         }
         /*
         	Append credit notes
         
         	Credit notes are a special condition, we need to fetch any ap_credit_tax or ar_credit_tax items
         	and add to the tax report based on the data of the credit note, regardless whether we are using payment or
         	invoice basis.
         	
         	AR/Tax Collected Report	== include AP Credit Notes (paying tax that was claimed)
         	AP/Tax Paid Report	== include AR Credit Notes (claiming back tax paid)
         */
         if ($this->type == "ap") {
             $type_credit = "ar_credit";
         } else {
             $type_credit = "ap_credit";
         }
         log_write("debug", "inc_taxes", "Fetching tax items for {$type_credit} credit notes");
         // fetch matching credit node IDs
         $sql_credit_obj = new sql_query();
         $sql_credit_obj->string = "SELECT id, code_credit, amount, date_trans FROM account_" . $type_credit . " WHERE date_trans >= '" . $this->obj_table->filter["filter_date_start"]["defaultvalue"] . "' AND date_trans <= '" . $this->obj_table->filter["filter_date_end"]["defaultvalue"] . "'";
         $sql_credit_obj->execute();
         if ($sql_credit_obj->num_rows()) {
             $sql_credit_obj->fetch_array();
             // fetch tax items for the selected tax
             foreach ($sql_credit_obj->data as $data_credit) {
                 $sql_credititems_obj = new sql_query();
                 $sql_credititems_obj->string = "SELECT amount FROM account_items WHERE invoiceid='" . $data_credit["id"] . "' AND invoicetype='" . $type_credit . "' AND type='tax' AND customid='" . $this->taxid . "'";
                 $sql_credititems_obj->execute();
                 if ($sql_credititems_obj->num_rows()) {
                     // credit items exist
                     log_write("debug", "inc_taxes", "Adding taxes for credit note " . $data_credit["code_credit"] . "");
                     // add tax items to total
                     $sql_credititems_obj->fetch_array();
                     $data_credit["amount_tax"] = 0;
                     foreach ($sql_credititems_obj->data as $data_credit_tax) {
                         $data_credit["amount_tax"] += $data_credit_tax["amount"];
                     }
                     // add entry to table/report
                     $row = array();
                     $row["id"] = $data_credit["id"];
                     $row["amount"] = $data_credit["amount"];
                     $row["amount_tax"] = $data_credit["amount_tax"];
                     $row["date_trans"] = $data_credit["date_trans"];
                     $row["code_invoice"] = $data_credit["code_credit"];
                     $row["name_vendor"] = "AR Credit";
                     $row["name_customer"] = "AP Credit";
                     // add row to table
                     $this->obj_table->data[] = $row;
                     $this->obj_table->data_num_rows++;
                 } else {
                     log_write("debug", "inc_taxes", "Credit note " . $data_credit["code_credit"] . " is within the report period, but has no appropiate tax items");
                 }
                 unset($sql_credititems_obj);
             }
         }
         /*
         	Re-index the data results to fix any holes created by deleted invoices
         */
         $this->obj_table->data = array_values($this->obj_table->data);
         $this->obj_table->data_num_rows = $this->obj_table->data_num_rows - $deleted_invoices;
     }
     return 1;
 }