From 7618d9d62ee39a932f56565a4ce25d965bf53462 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 31 Jul 2019 11:46:36 +0200 Subject: [PATCH] Payment terms related functions moved to separate file, common function for calculating invoice due date, fixed due date calculation for end of the following month. --- admin/db/company_db.inc | 85 +------------------ admin/db/payment_terms_db.inc | 91 +++++++++++++++++++++ includes/db/sql_functions.inc | 30 +++++++ purchasing/includes/db/invoice_db.inc | 12 +-- purchasing/includes/ui/invoice_ui.inc | 28 +------ purchasing/includes/ui/po_ui.inc | 5 +- sales/customer_delivery.php | 2 +- sales/customer_invoice.php | 6 +- sales/includes/cart_class.inc | 4 +- sales/includes/db/recurrent_invoices_db.inc | 2 +- sales/includes/db/sales_order_db.inc | 21 ----- sales/includes/ui/sales_order_ui.inc | 6 +- sales/sales_order_entry.php | 2 +- sql/en_US-demo.sql | 2 +- sql/en_US-new.sql | 2 +- 15 files changed, 150 insertions(+), 148 deletions(-) create mode 100644 admin/db/payment_terms_db.inc diff --git a/admin/db/company_db.inc b/admin/db/company_db.inc index b774d2bc..c7e7c1ae 100644 --- a/admin/db/company_db.inc +++ b/admin/db/company_db.inc @@ -9,6 +9,8 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ +include_once($path_to_root. "/admin/db/payment_terms_db.inc"); + /* Update main or gl company setup. */ @@ -86,89 +88,6 @@ function get_company_extensions($id = -1) { return $installed_extensions; } -function add_payment_terms($terms, $type, $days=0, $early_discount=0, $early_days=0) -{ - begin_transaction(__FUNCTION__, func_get_args()); - - $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, type, days, early_discount, early_days) - VALUES (" .db_escape($terms) . "," . db_escape($type) . ", ".db_escape($days). ", ".db_escape($early_discount). ", ".db_escape($early_days).")"; - - db_query($sql,"The payment term could not be added"); - - commit_transaction(); -} - -function update_payment_terms($selected_id, $terms, $type, $days=0, $early_discount=0, $early_days=0) -{ - begin_transaction(__FUNCTION__, func_get_args()); - - $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($terms) . ", - type=".db_escape($type).", - days=" . db_escape($days).", - early_discount=".db_escape($early_discount).", - early_days=".db_escape($early_days). - " WHERE id = " .db_escape($selected_id); - - db_query($sql,"The payment term could not be updated"); - - commit_transaction(); -} - -function delete_payment_terms($selected_id) -{ - begin_transaction(__FUNCTION__, func_get_args()); - - $sql="DELETE FROM ".TB_PREF."payment_terms WHERE id=".db_escape($selected_id); - db_query($sql,"could not delete a payment terms"); - - commit_transaction(); -} - -function get_payment_terms($selected_id) -{ - $sql = "SELECT * FROM ".TB_PREF."payment_terms t WHERE id=".db_escape($selected_id); - - $result = db_query($sql,"could not get payment term"); - - return db_fetch($result); -} - -function get_payment_terms_all($show_inactive) -{ - $sql = "SELECT * FROM ".TB_PREF."payment_terms"; - if (!$show_inactive) $sql .= " WHERE !inactive"; - return db_query($sql,"could not get payment terms"); -} -/* - Return number of records in tables, where some foreign key $id is used. - $id - searched key value - $tables - array of table names (without prefix); when table name is used as a key, then - value is name of foreign key field. For numeric keys $stdkey field name is used. - $stdkey - standard name of foreign key. -*/ -function key_in_foreign_table($id, $tables, $stdkey) -{ - - if (!is_array($tables)) - $tables = array($tables); - - $sqls = array(); - foreach ($tables as $tbl => $key) { - if (is_numeric($tbl)) { - $tbl = $key; - $key = $stdkey; - } - $sqls[] = "(SELECT COUNT(*) as cnt FROM `".TB_PREF."$tbl` WHERE `$key`=".db_escape($id).")\n"; - } - - $sql = "SELECT sum(cnt) FROM (". implode(' UNION ', $sqls).") as counts"; - - $result = db_query($sql, "check relations for ".implode(',',$tables)." failed"); - $count = db_fetch($result); - - return $count[0]; -} - //--------------------------------------------------------------------------------------------- // // Resets $theme references in users records to 'default'. diff --git a/admin/db/payment_terms_db.inc b/admin/db/payment_terms_db.inc new file mode 100644 index 00000000..3934622c --- /dev/null +++ b/admin/db/payment_terms_db.inc @@ -0,0 +1,91 @@ +. +***********************************************************************/ + +function add_payment_terms($terms, $type, $days=0, $early_discount=0, $early_days=0) +{ + begin_transaction(__FUNCTION__, func_get_args()); + + $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, type, days, early_discount, early_days) + VALUES (" .db_escape($terms) . "," . db_escape($type) . ", ".db_escape($days). ", ".db_escape($early_discount). ", ".db_escape($early_days).")"; + + db_query($sql,"The payment term could not be added"); + + commit_transaction(); +} + +function update_payment_terms($selected_id, $terms, $type, $days=0, $early_discount=0, $early_days=0) +{ + begin_transaction(__FUNCTION__, func_get_args()); + + $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($terms) . ", + type=".db_escape($type).", + days=" . db_escape($days).", + early_discount=".db_escape($early_discount).", + early_days=".db_escape($early_days). + " WHERE id = " .db_escape($selected_id); + + db_query($sql,"The payment term could not be updated"); + + commit_transaction(); +} + +function delete_payment_terms($selected_id) +{ + begin_transaction(__FUNCTION__, func_get_args()); + + $sql="DELETE FROM ".TB_PREF."payment_terms WHERE id=".db_escape($selected_id); + db_query($sql,"could not delete a payment terms"); + + commit_transaction(); +} + +function get_payment_terms($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."payment_terms t WHERE id=".db_escape($selected_id); + + $result = db_query($sql,"could not get payment term"); + + return db_fetch($result); +} + +function get_payment_terms_all($show_inactive) +{ + $sql = "SELECT * FROM ".TB_PREF."payment_terms"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + return db_query($sql,"could not get payment terms"); +} + +/* + Calculate due date using terms data provided either as table of payment data or payment terms id +*/ +function get_payment_due_date($terms_data, $date) +{ + if (!is_array($terms_data)) + $terms_data = get_payment_terms($terms_data); + + if (!is_date($date)) + $date = new_doc_date(); + + if (!$terms_data) + return $date; + + if ($terms_data['type'] == PTT_FOLLOWING) { + $end = end_month(add_months(begin_month($date), 1)); + $duedate = add_days(end_month($date), $terms_data['days']); + if (date1_greater_date2($duedate, $end)) + $duedate = $end; + } elseif ($terms_data['type'] == PTT_DAYS) + $duedate = add_days($date, $terms_data['days']); + else + $duedate = $date; + return $duedate; +} diff --git a/includes/db/sql_functions.inc b/includes/db/sql_functions.inc index 3b4b5acd..56392e6d 100644 --- a/includes/db/sql_functions.inc +++ b/includes/db/sql_functions.inc @@ -101,3 +101,33 @@ function running_total_sql($table, $column, $index) (SELECT @total:=0) total_var"; } +/* + Return number of records in tables, where some foreign key $id is used. + $id - searched key value + $tables - array of table names (without prefix); when table name is used as a key, then + value is name of foreign key field. For numeric keys $stdkey field name is used. + $stdkey - standard name of foreign key. +*/ +function key_in_foreign_table($id, $tables, $stdkey) +{ + + if (!is_array($tables)) + $tables = array($tables); + + $sqls = array(); + foreach ($tables as $tbl => $key) { + if (is_numeric($tbl)) { + $tbl = $key; + $key = $stdkey; + } + $sqls[] = "(SELECT COUNT(*) as cnt FROM `".TB_PREF."$tbl` WHERE `$key`=".db_escape($id).")\n"; + } + + $sql = "SELECT sum(cnt) FROM (". implode(' UNION ', $sqls).") as counts"; + + $result = db_query($sql, "check relations for ".implode(',',$tables)." failed"); + $count = db_fetch($result); + + return $count[0]; +} + diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 443a16ed..11fed129 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -38,12 +38,7 @@ function read_supplier_details_to_trans(&$supp_trans, $supplier_id) if (!is_date_in_fiscalyear($supp_trans->tran_date)) $supp_trans->tran_date = end_fiscalyear(); } - if ($supp_trans->supplier_id != $supplier_id) - get_duedate_from_terms($supp_trans); - $supp_trans->supplier_id = $supplier_id; - $supp_trans->tax_included = $myrow['tax_included']; - $supp_trans->supplier_name = $myrow['supp_name']; $supp_trans->terms = array( 'description' => $myrow['terms'], 'type' => $myrow['type'], @@ -51,6 +46,13 @@ function read_supplier_details_to_trans(&$supp_trans, $supplier_id) 'early_discount' => $myrow['early_discount'], 'early_days' => $myrow['early_days'], ); + + if ($supp_trans->supplier_id != $supplier_id) + $supp_trans->due_date = get_payment_due_date($supp_trans->terms, $supp_trans->tran_date); + + $supp_trans->supplier_id = $supplier_id; + $supp_trans->tax_included = $myrow['tax_included']; + $supp_trans->supplier_name = $myrow['supp_name']; $supp_trans->credit = $myrow['cur_credit']; $supp_trans->tax_description = $myrow['tax_group_name']; diff --git a/purchasing/includes/ui/invoice_ui.inc b/purchasing/includes/ui/invoice_ui.inc index e677cf3a..14953a2f 100644 --- a/purchasing/includes/ui/invoice_ui.inc +++ b/purchasing/includes/ui/invoice_ui.inc @@ -109,6 +109,9 @@ function invoice_header(&$supp_trans) $supp_trans->clear_items(); read_supplier_details_to_trans($supp_trans, $_POST['supplier_id']); copy_from_trans($supp_trans); + $supp_trans->due_date = get_payment_due_date($supp_trans->terms, $supp_trans->tran_date); + $_POST['due_date'] = $supp_trans->due_date; + $Ajax->activate('due_date'); } date_row(_("Date") . ":", 'tran_date', '', true, 0, 0, 0, "", true); @@ -127,7 +130,7 @@ function invoice_header(&$supp_trans) if (isset($_POST['_tran_date_changed'])) { $Ajax->activate('_ex_rate'); $supp_trans->tran_date = $_POST['tran_date']; - get_duedate_from_terms($supp_trans); + $supp_trans->due_date = get_payment_due_date($supp_trans->terms, $supp_trans->tran_date); $_POST['due_date'] = $supp_trans->due_date; $Ajax->activate('due_date'); } @@ -573,26 +576,3 @@ function display_grn_items(&$supp_trans, $mode=0) return $total_grn_value; } - -//-------------------------------------------------------------------------------------------------- -function get_duedate_from_terms(&$trans) -{ - $date = $trans->tran_date; - - if (!is_date($date)) - { - $date = Today(); - } - if ($trans->terms['type'] == PTT_FOLLOWING) - { /*Its a day in the following month when due */ - $trans->due_date = - add_days(end_month($date), $trans->terms["days"]); - } - else - { /*Use the Days Before Due to add to the invoice date */ - $trans->due_date = add_days($date, $trans->terms["days"]); - } -} - -//-------------------------------------------------------------------------------------------------- - diff --git a/purchasing/includes/ui/po_ui.inc b/purchasing/includes/ui/po_ui.inc index ccef48c3..65ed33e5 100644 --- a/purchasing/includes/ui/po_ui.inc +++ b/purchasing/includes/ui/po_ui.inc @@ -124,7 +124,8 @@ function display_po_header(&$order) if ($order->supplier_id != get_post('supplier_id',-1)) { $old_supp = $order->supplier_id; get_supplier_details_to_order($order, $_POST['supplier_id']); - get_duedate_from_terms($order); + $order->due_date = get_payment_due_date($order->terms, $order->tran_date); + $_POST['due_date'] = $order->due_date; // supplier default price update @@ -146,7 +147,7 @@ function display_po_header(&$order) if (isset($_POST['_OrderDate_changed'])) { $order->tran_date = $_POST['OrderDate']; - get_duedate_from_terms($order); + $order->due_date = get_payment_due_date($order->terms, $order->tran_date); $_POST['due_date'] = $order->due_date; $Ajax->activate('due_date'); } diff --git a/sales/customer_delivery.php b/sales/customer_delivery.php index 1bec9393..637c238d 100644 --- a/sales/customer_delivery.php +++ b/sales/customer_delivery.php @@ -373,7 +373,7 @@ echo "";// outer table start_table(TABLESTYLE, "width='90%'"); if (!isset($_POST['due_date']) || !is_date($_POST['due_date'])) { - $_POST['due_date'] = get_invoice_duedate($_SESSION['Items']->payment, $_POST['DispatchDate']); + $_POST['due_date'] = get_payment_due_date($_SESSION['Items']->payment, $_POST['DispatchDate']); } customer_credit_row($_SESSION['Items']->customer_id, $_SESSION['Items']->credit, "class='tableheader2'"); diff --git a/sales/customer_invoice.php b/sales/customer_invoice.php index 2bc41391..e46e38e7 100644 --- a/sales/customer_invoice.php +++ b/sales/customer_invoice.php @@ -189,7 +189,7 @@ if (isset($_POST['Update'])) { $Ajax->activate('Items'); } if (isset($_POST['_InvoiceDate_changed'])) { - $_POST['due_date'] = get_invoice_duedate($_SESSION['Items']->payment, $_POST['InvoiceDate']); + $_POST['due_date'] = get_payment_due_date($_SESSION['Items']->payment, $_POST['InvoiceDate']); $Ajax->activate('due_date'); } @@ -381,7 +381,7 @@ if(list_updated('payment')) { copy_to_cart(); $order->payment = get_post('payment'); $order->payment_terms = get_payment_terms($order->payment); - $_POST['due_date'] = $order->due_date = get_invoice_duedate($order->payment, $order->document_date); + $_POST['due_date'] = $order->due_date = get_payment_due_date($order->payment, $order->document_date); $_POST['Comments'] = ''; $Ajax->activate('due_date'); $Ajax->activate('options'); @@ -486,7 +486,7 @@ date_cells(_("Date"), 'InvoiceDate', '', $_SESSION['Items']->trans_no == 0, 0, 0, 0, "class='tableheader2'", true); if (!isset($_POST['due_date']) || !is_date($_POST['due_date'])) { - $_POST['due_date'] = get_invoice_duedate($_SESSION['Items']->payment, $_POST['InvoiceDate']); + $_POST['due_date'] = get_payment_due_date($_SESSION['Items']->payment, $_POST['InvoiceDate']); } date_cells(_("Due Date"), 'due_date', '', null, 0, 0, 0, "class='tableheader2'"); diff --git a/sales/includes/cart_class.inc b/sales/includes/cart_class.inc index 611194bf..b35b25f4 100644 --- a/sales/includes/cart_class.inc +++ b/sales/includes/cart_class.inc @@ -158,7 +158,7 @@ class Cart $this->dimension2_id = $cust['dimension2_id']; } if ($type == ST_SALESINVOICE) { - $this->due_date = get_invoice_duedate($this->payment, $this->document_date); + $this->due_date = get_payment_due_date($this->payment, $this->document_date); } $this->src_docs = $this->trans_no; @@ -270,7 +270,7 @@ class Cart } if ($type == ST_SALESINVOICE) { $this->due_date = - get_invoice_duedate($this->payment, $this->document_date); + get_payment_due_date($this->payment, $this->document_date); } else $this->due_date = add_days($this->document_date, $SysPrefs->default_delivery_required_by()); diff --git a/sales/includes/db/recurrent_invoices_db.inc b/sales/includes/db/recurrent_invoices_db.inc index 5529d0c2..7aca37cf 100644 --- a/sales/includes/db/recurrent_invoices_db.inc +++ b/sales/includes/db/recurrent_invoices_db.inc @@ -194,7 +194,7 @@ function create_template_invoice($customer_id, $branch_id, $order_no, $tmpl_no, $doc->trans_no = 0; $doc->document_date = $date; - $doc->due_date = get_invoice_duedate($doc->payment, $doc->document_date); + $doc->due_date = get_payment_due_date($doc->payment, $doc->document_date); $doc->reference = $Refs->get_next($doc->trans_type, null, array('customer' => $customer_id, 'branch' => $branch_id, 'date' => $date)); diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index f4d18625..7abe7621 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -383,27 +383,6 @@ function close_sales_order($order_no) //--------------------------------------------------------------------------------------------------------------- -function get_invoice_duedate($terms, $invdate) -{ - if (!is_date($invdate)) - { - return new_doc_date(); - } - - $myrow = get_payment_terms($terms); - - if (!$myrow) - return $invdate; - - if ($myrow['type'] == PTT_FOLLOWING) - $duedate = add_days(end_month($invdate), $myrow['days']); - elseif ($myrow['type'] == PTT_DAYS) - $duedate = add_days($invdate, $myrow['days']); - else - $duedate = $invdate; - return $duedate; -} - function get_customer_to_order($customer_id) { // Now check to ensure this account is not on hold */ diff --git a/sales/includes/ui/sales_order_ui.inc b/sales/includes/ui/sales_order_ui.inc index 2d9be2dc..1a75eba3 100644 --- a/sales/includes/ui/sales_order_ui.inc +++ b/sales/includes/ui/sales_order_ui.inc @@ -118,7 +118,7 @@ function get_customer_details_to_order(&$order, $customer_id, $branch_id) $order->set_delivery($myrow["default_ship_via"], $myrow["br_name"], $address); if ($order->trans_type == ST_SALESINVOICE) { - $order->due_date = get_invoice_duedate($order->payment, $order->document_date); + $order->due_date = get_payment_due_date($order->payment, $order->document_date); } elseif ($order->trans_type == ST_SALESORDER) $order->due_date = add_days($order->document_date, $SysPrefs->default_delivery_required_by()); @@ -387,7 +387,7 @@ function display_order_header(&$order, $editable, $date_text) if (get_post('payment') !== $order->payment) { $order->payment = get_post('payment'); $order->payment_terms = get_payment_terms($order->payment); - $order->due_date = get_invoice_duedate($order->payment, $order->document_date); + $order->due_date = get_payment_due_date($order->payment, $order->document_date); if ($order->payment_terms['type'] == PTT_CASH) { $_POST['Location'] = $order->Location = $order->pos['pos_location']; $order->location_name = $order->pos['location_name']; @@ -434,7 +434,7 @@ function display_order_header(&$order, $editable, $date_text) } $Ajax->activate('_ex_rate'); if ($order->trans_type == ST_SALESINVOICE) { - $_POST['delivery_date'] = get_invoice_duedate(get_post('payment'), get_post('OrderDate')); + $_POST['delivery_date'] = get_payment_due_date(get_post('payment'), get_post('OrderDate')); } else $_POST['delivery_date'] = add_days(get_post('OrderDate'), $SysPrefs->default_delivery_required_by()); $Ajax->activate('items_table'); diff --git a/sales/sales_order_entry.php b/sales/sales_order_entry.php index 663bcee7..6f434d86 100644 --- a/sales/sales_order_entry.php +++ b/sales/sales_order_entry.php @@ -664,7 +664,7 @@ function create_cart($type, $trans_no) $doc->trans_no = 0; $doc->document_date = new_doc_date(); if ($type == ST_SALESINVOICE) { - $doc->due_date = get_invoice_duedate($doc->payment, $doc->document_date); + $doc->due_date = get_payment_due_date($doc->payment, $doc->document_date); $doc->pos = get_sales_point(user_pos()); } else $doc->due_date = $doc->document_date; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index b1e7c9ed..43fc5110 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -1093,7 +1093,7 @@ CREATE TABLE `0_payment_terms` ( INSERT INTO `0_payment_terms` VALUES ('1', 'Due 15th Of the Following Month', '4', '17', '0', '0', '0'), -('2', 'Due By End Of The Following Month', '4', '30', '0', '0', '0'), +('2', 'Due By End Of The Following Month', '4', '31', '0', '0', '0'), ('3', 'Payment due within 10 days', '10', '3', '0', '0', '0', '0'), ('4', 'Cash Only', '2', '0', '0', '0', '0'), ('5', 'Prepaid', '1', '0', '0', '0', '0'); diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index e1f378d6..30a44cd8 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -877,7 +877,7 @@ CREATE TABLE `0_payment_terms` ( INSERT INTO `0_payment_terms` VALUES ('1', 'Due 15th Of the Following Month', '4', '17', '0', '0', '0'), -('2', 'Due By End Of The Following Month', '4', '30', '0', '0', '0'), +('2', 'Due By End Of The Following Month', '4', '31', '0', '0', '0'), ('3', 'Payment due within 10 days', '3', '10', '0', '0', '0'), ('4', 'Cash Only', '2', '0', '0', '0', '0'), ('5', 'Prepaid', '1', '0', '0', '0', '0'); -- 2.30.2