From 50606312fee0bb0dbe984e2c08af53b7dab229bd Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 10 Jul 2011 23:01:30 +0200 Subject: [PATCH] Implemented journal entries with multiply virtual AR/AP subaccounts. --- admin/db/fiscalyears_db.inc | 20 ++-- gl/bank_account_reconcile.php | 2 +- gl/gl_journal.php | 9 +- gl/includes/db/gl_db_accounts.inc | 26 +++++ gl/includes/db/gl_db_trans.inc | 16 ++- gl/includes/ui/gl_journal_ui.inc | 108 +++++++++++++------- gl/inquiry/bank_inquiry.php | 2 +- gl/view/gl_deposit_view.php | 2 +- gl/view/gl_payment_view.php | 2 +- gl/view/gl_trans_view.php | 27 +++-- includes/types.inc | 74 ++++++++++++++ includes/ui/items_cart.inc | 42 +++++++- includes/ui/ui_lists.inc | 50 +++++++++ purchasing/includes/db/invoice_items_db.inc | 5 +- purchasing/includes/ui/invoice_ui.inc | 14 +-- reporting/rep601.php | 2 +- reporting/rep702.php | 2 +- sql/alter2.4.sql | 5 + 18 files changed, 328 insertions(+), 80 deletions(-) diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc index ba04017c..ec00e067 100644 --- a/admin/db/fiscalyears_db.inc +++ b/admin/db/fiscalyears_db.inc @@ -322,17 +322,25 @@ function delete_this_fiscalyear($selected_id) $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE tran_date <= '$to'"; db_query($sql, "Could not delete exchange rates"); - $sql = "SELECT account, SUM(amount) AS amount FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account"; + $sql = "SELECT account, SUM(amount) AS amount, person_type_id, person_id FROM " + .TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account, person_type_id, person_id"; $result = db_query($sql, "Could not retrieve gl trans"); $trans_no = get_next_trans_no(ST_JOURNAL); + + $last_account=''; while ($row = db_fetch($result)) { - $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; - db_query($sql, "Could not delete gl trans"); - if (is_account_balancesheet($row['account'])) + if ($last_account != $row['account']) // deletes all subledgers postings, so do it once for account + { + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; + db_query($sql, "Could not delete gl trans"); + $last_account = $row['account']; + } + if (is_account_balancesheet($row['account']) && $row['amount']) { - $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES - (".ST_JOURNAL.", $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})"; + $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount, person_type_id, person_id) VALUES + (".ST_JOURNAL.", $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']}, " + .db_escape($row['person_type_id'], true).", ".db_escape($row['person_id'], true).")"; db_query($sql, "Could not insert gl trans"); } } diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index 4fa56550..b6a5355d 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -88,7 +88,7 @@ function fmt_credit($row) function fmt_person($row) { - return payment_person_name($row["person_type_id"],$row["person_id"]); + return get_counterparty_name($trans["type"], $trans["trans_no"]); } $update_pager = false; diff --git a/gl/gl_journal.php b/gl/gl_journal.php index bb744de6..5cc96652 100644 --- a/gl/gl_journal.php +++ b/gl/gl_journal.php @@ -41,6 +41,7 @@ page($_SESSION['page_title'], false, false,'', $js); function line_start_focus() { global $Ajax; + unset($_POST['Index']); $Ajax->activate('items_table'); set_focus('_code_id_edit'); } @@ -109,7 +110,7 @@ function create_cart($type=0, $trans_no=0) if ($row['amount'] == 0) continue; $date = $row['tran_date']; $cart->add_gl_item($row['account'], $row['dimension_id'], - $row['dimension2_id'], $row['amount'], $row['memo_']); + $row['dimension2_id'], $row['amount'], $row['memo_'], '', $row['person_id']); } } $cart->memo_ = get_comments_string($type, $trans_no); @@ -266,7 +267,7 @@ function handle_update_item() $amount = -input_num('AmountCredit'); $_SESSION['journal_items']->update_gl_item($_POST['Index'], $_POST['code_id'], - $_POST['dimension_id'], $_POST['dimension2_id'], $amount, $_POST['LineMemo']); + $_POST['dimension_id'], $_POST['dimension2_id'], $amount, $_POST['LineMemo'], '', get_post('person_id')); } line_start_focus(); } @@ -292,7 +293,7 @@ function handle_new_item() $amount = -input_num('AmountCredit'); $_SESSION['journal_items']->add_gl_item($_POST['code_id'], $_POST['dimension_id'], - $_POST['dimension2_id'], $amount, $_POST['LineMemo']); + $_POST['dimension2_id'], $amount, $_POST['LineMemo'], '', get_post('person_id')); line_start_focus(); } @@ -312,7 +313,7 @@ if (isset($_POST['CancelItemChanges'])) if (isset($_POST['go'])) { - display_quick_entries($_SESSION['journal_items'], $_POST['person_id'], input_num('totamount'), QE_JOURNAL); + display_quick_entries($_SESSION['journal_items'], $_POST['quick'], input_num('totamount'), QE_JOURNAL); $_POST['totamount'] = price_format(0); $Ajax->activate('totamount'); line_start_focus(); } diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index f085cfe8..3ba13334 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -179,4 +179,30 @@ function gl_account_in_quick_entry_lines($acc) $myrow = db_fetch_row($result); return ($myrow[0] > 0); } +// +// Returns n>0 when account is AR, n<0 when account is AP +// (priority for AR accounts) +// + function is_subledger_account($account) + { + $sql = "SELECT 1 FROM ".TB_PREF."cust_branch WHERE receivables_account=".db_escape($account) + ." UNION SELECT -1 FROM ".TB_PREF."suppliers WHERE payable_account=".db_escape($account); + + $result = db_query($sql,"Couldn't test AR/AP account"); + $myrow = db_fetch_row($result); + return $myrow[0]; + } + +function get_subaccount_name($code_id, $person_id) +{ + $sql = "SELECT debtor_ref as ref FROM ".TB_PREF."cust_branch branch LEFT JOIN ".TB_PREF."debtors_master d ON branch.debtor_no = d.debtor_no + WHERE branch.receivables_account=".db_escape($code_id)." AND d.debtor_no=".db_escape($person_id) + ." UNION SELECT supp_ref as ref FROM ".TB_PREF."suppliers supp + WHERE payable_account=".db_escape($code_id)." AND supplier_id=".db_escape($person_id); + $result = db_query($sql, 'cannot retrieve counterparty name'); + $row = db_fetch($result); + + return $row ? $row['ref'] : ''; +} + ?> \ No newline at end of file diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 5b693997..078d3446 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -42,6 +42,9 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension else $memo_ = $_SESSION["wa_current_user"]->username . " - " . $memo_; } + if (!is_subledger_account($account)) + $person_id = $person_type_id = null; + $sql = "INSERT INTO ".TB_PREF."gl_trans ( type, type_no, tran_date, account, dimension_id, dimension2_id, memo_, amount"; @@ -497,9 +500,12 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) // post to first found bank account using given gl acount code. $is_bank_to = is_bank_account($journal_item->code_id); - add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id, - $journal_item->dimension_id, $journal_item->dimension2_id, - $journal_item->reference, $journal_item->amount); + add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id, + $journal_item->dimension_id, $journal_item->dimension2_id, + $journal_item->reference, $journal_item->amount, null, + $journal_item->person_type_id, + $journal_item->person_id); + if ($is_bank_to) { add_bank_trans($trans_type, $trans_id, $is_bank_to, $ref, @@ -534,7 +540,9 @@ function write_journal_entries(&$cart, $reverse, $use_transaction=true) add_gl_trans($trans_type, $trans_id_reverse, $reversingDate, $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id, - $journal_item->reference, -$journal_item->amount); + $journal_item->reference, -$journal_item->amount, + null, $journal_item->person_type_id, $journal_item->person_id); + if ($is_bank_to) { add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref, diff --git a/gl/includes/ui/gl_journal_ui.inc b/gl/includes/ui/gl_journal_ui.inc index 8c4aec6a..f9e5585c 100644 --- a/gl/includes/ui/gl_journal_ui.inc +++ b/gl/includes/ui/gl_journal_ui.inc @@ -43,9 +43,9 @@ function display_order_header(&$Order) { table_section(3, "40%"); start_row(); - quick_entries_list_cells(_("Quick Entry").":", 'person_id', null, QE_JOURNAL, true); - $qid = get_quick_entry(get_post('person_id')); - if (list_updated('person_id')) { + quick_entries_list_cells(_("Quick Entry").":", 'quick', null, QE_JOURNAL, true); + $qid = get_quick_entry(get_post('quick')); + if (list_updated('quick')) { unset($_POST['totamount']); // enable default $Ajax->activate('totamount'); } @@ -63,7 +63,7 @@ function display_order_header(&$Order) end_row(); } - + end_outer_table(1); } @@ -77,6 +77,9 @@ function display_gl_items($title, &$order) $dim = get_company_pref('use_dimension'); + $sub_type = is_subledger_account(get_post('code_id')); + $has_subaccounts = $order->has_sub_accounts(); + div_start('items_table'); start_table(TABLESTYLE, "colspan=7 width=95%"); if ($dim == 2) @@ -90,19 +93,28 @@ function display_gl_items($title, &$order) _("Debit"), _("Credit"), _("Memo"), ""); if (count($order->gl_items)) $th[] = ''; + if ($sub_type || $has_subaccounts) + array_insert($th, 2, _("Counterparty")); - table_header($th); + table_header($th); $k = 0; $id = find_submit('Edit'); + if ($id == -1) + $id = get_post('Index', -1); + foreach ($order->gl_items as $line => $item) { if ($id != $line) { alt_table_row_color($k); - label_cells($item->code_id, $item->description); + $acc = $item->code_id . ($item->person_id ? sprintf(' %05d', $item->person_id) : ''); + label_cells($acc, $item->description); + + label_cell($item->person_name); + if ($dim >= 1) label_cell(get_dimension_string($item->dimension_id, true)); if ($dim > 1) @@ -111,7 +123,7 @@ function display_gl_items($title, &$order) { amount_cell(abs($item->amount)); label_cell(""); - } + } else { label_cell(""); @@ -127,12 +139,12 @@ function display_gl_items($title, &$order) } else { - gl_edit_item_controls($order, $dim, $line); + gl_edit_item_controls($order, $dim, $item->person_id, $line); } } if ($id == -1) - gl_edit_item_controls($order, $dim); + gl_edit_item_controls($order, $dim, $sub_type); if ($order->count_gl_items()) { @@ -151,35 +163,47 @@ function display_gl_items($title, &$order) //--------------------------------------------------------------------------------- -function gl_edit_item_controls(&$order, $dim, $Index=null) +function gl_edit_item_controls(&$order, $dim, $sub_accounts, $Index=null) { global $Ajax; start_row(); $id = find_submit('Edit'); + if ($id == -1) + $continue = $id = get_post('Index', -1); + if ($Index != -1 && $Index == $id) { // Modifying an existing row $item = $order->gl_items[$Index]; - $_POST['code_id'] = $item->code_id; - $_POST['dimension_id'] = $item->dimension_id; - $_POST['dimension2_id'] = $item->dimension2_id; - if ($item->amount > 0) + if (!isset($continue)) { - $_POST['AmountDebit'] = price_format($item->amount); - $_POST['AmountCredit'] = ""; + $_POST['code_id'] = $item->code_id; + $_POST['dimension_id'] = $item->dimension_id; + $_POST['dimension2_id'] = $item->dimension2_id; + $_POST['person_id'] = $item->person_id; + if ($item->amount > 0) + { + $_POST['AmountDebit'] = price_format($item->amount); + $_POST['AmountCredit'] = ""; + } + else + { + $_POST['AmountDebit'] = ""; + $_POST['AmountCredit'] = price_format(abs($item->amount)); + } + $_POST['description'] = $item->description; + $_POST['LineMemo'] = $item->reference; } - else - { - $_POST['AmountDebit'] = ""; - $_POST['AmountCredit'] = price_format(abs($item->amount)); - } - $_POST['description'] = $item->description; - $_POST['LineMemo'] = $item->reference; - hidden('Index', $id); $skip_bank = !$_SESSION["wa_current_user"]->can_access('SA_BANKJOURNAL'); - echo gl_all_accounts_list('code_id', null, $skip_bank, true); + echo gl_all_accounts_list('code_id', null, $skip_bank, true, _('[Select account]'), true, false, false); + + if (is_subledger_account(get_post('code_id'))) + { + subledger_list_cells(null, 'person_id', get_post('code_id')); + } elseif ($order->has_sub_accounts()) + label_cell(''); if ($dim >= 1) dimensions_list_cells(null, 'dimension_id', null, true, " ", false, 1); if ($dim > 1) @@ -188,20 +212,32 @@ function gl_edit_item_controls(&$order, $dim, $Index=null) } else { - // Adding a new row - $_POST['AmountDebit'] = ''; //price_format(0); - $_POST['AmountCredit'] = ''; //price_format(0); - $_POST['dimension_id'] = 0; - $_POST['dimension2_id'] = 0; - //$_POST['LineMemo'] = ""; // let memo go to next line Joe Hunt 2010-05-30 - $_POST['_code_id_edit'] = ""; - $_POST['code_id'] = ""; + + if (!list_updated('code_id')) + { + // Adding a new row + $_POST['AmountDebit'] = ''; //price_format(0); + $_POST['AmountCredit'] = ''; //price_format(0); + $_POST['dimension_id'] = 0; + $_POST['dimension2_id'] = 0; + //$_POST['LineMemo'] = ""; // let memo go to next line Joe Hunt 2010-05-30 + $_POST['_code_id_edit'] = ""; + $_POST['code_id'] = ""; + } if(isset($_POST['_code_id_update'])) { - $Ajax->activate('code_id'); + $Ajax->activate('code_id'); } - + $skip_bank = !$_SESSION["wa_current_user"]->can_access('SA_BANKJOURNAL'); - echo gl_all_accounts_list('code_id', null, $skip_bank, true); + echo gl_all_accounts_list('code_id', null, $skip_bank, true, _('[Select account]'), true, false, false); + + if ($sub_accounts) + { + subledger_list_cells(null, 'person_id', get_post('code_id')); + } + elseif ($order->has_sub_accounts()) + label_cell(''); + if ($dim >= 1) dimensions_list_cells(null, 'dimension_id', null, true, " ", false, 1); if ($dim > 1) diff --git a/gl/inquiry/bank_inquiry.php b/gl/inquiry/bank_inquiry.php index b264fe7f..30380989 100644 --- a/gl/inquiry/bank_inquiry.php +++ b/gl/inquiry/bank_inquiry.php @@ -102,7 +102,7 @@ while ($myrow = db_fetch($result)) label_cell($trandate); display_debit_or_credit_cells($myrow["amount"]); amount_cell($running_total); - label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"])); + label_cell(get_counterparty_name($myrow["type"], $myrow["trans_no"])); label_cell(get_gl_view_str($myrow["type"], $myrow["trans_no"])); end_row(); if ($myrow["amount"] > 0 ) diff --git a/gl/view/gl_deposit_view.php b/gl/view/gl_deposit_view.php index 949282b5..bd231d93 100644 --- a/gl/view/gl_deposit_view.php +++ b/gl/view/gl_deposit_view.php @@ -68,7 +68,7 @@ label_cells(_("Amount"), number_format2($to_trans['amount'], user_price_dec()), label_cells(_("Date"), sql2date($to_trans['trans_date']), "class='tableheader2'"); end_row(); start_row(); -label_cells(_("From"), payment_person_name($to_trans['person_type_id'], $to_trans['person_id']), "class='tableheader2'", "colspan=$colspan1"); +label_cells(_("From"), get_counterparty_name(ST_BANKDEPOSIT, $trans_no), "class='tableheader2'", "colspan=$colspan1"); label_cells(_("Deposit Type"), $bank_transfer_types[$to_trans['account_type']], "class='tableheader2'"); end_row(); start_row(); diff --git a/gl/view/gl_payment_view.php b/gl/view/gl_payment_view.php index f348de15..681373c2 100644 --- a/gl/view/gl_payment_view.php +++ b/gl/view/gl_payment_view.php @@ -66,7 +66,7 @@ label_cells(_("Amount"), number_format2(-$from_trans['amount'], user_price_dec() label_cells(_("Date"), sql2date($from_trans['trans_date']), "class='tableheader2'"); end_row(); start_row(); -label_cells(_("Pay To"), payment_person_name($from_trans['person_type_id'], $from_trans['person_id']), "class='tableheader2'", "colspan=$colspan1"); +label_cells(_("Pay To"), get_counterparty_name(ST_BANKPAYMENT, $trans_no), "class='tableheader2'", "colspan=$colspan1"); label_cells(_("Payment Type"), $bank_transfer_types[$from_trans['account_type']], "class='tableheader2'"); end_row(); start_row(); diff --git a/gl/view/gl_trans_view.php b/gl/view/gl_trans_view.php index 4e61fef9..5d54c72f 100644 --- a/gl/view/gl_trans_view.php +++ b/gl/view/gl_trans_view.php @@ -33,14 +33,19 @@ function display_gl_heading($myrow) $trans_name = $systypes_array[$_GET['type_id']]; start_table(TABLESTYLE, "width=95%"); $th = array(_("General Ledger Transaction Details"), _("Reference"), - _("Date"), _("Person/Item")); - table_header($th); - start_row(); + _("Date")); + + if ($_GET['type_id'] != ST_JOURNAL) + $th[] = _("Counterparty"); + + table_header($th); + start_row(); label_cell("$trans_name #" . $_GET['trans_no']); label_cell($myrow["reference"]); label_cell(sql2date($myrow["tran_date"])); - label_cell(payment_person_name($myrow["person_type_id"],$myrow["person_id"])); - + if ($_GET['type_id'] != ST_JOURNAL) + label_cell(get_counterparty_name($_GET['type_id'],$_GET['trans_no'])); + end_row(); comments_display_row($_GET['type_id'], $_GET['trans_no']); @@ -68,6 +73,7 @@ else if ($dim == 1) else $th = array(_("Account Code"), _("Account Name"), _("Debit"), _("Credit"), _("Memo")); + $k = 0; //row colour counter $heading_shown = false; @@ -81,12 +87,15 @@ while ($myrow = db_fetch($result)) start_table(TABLESTYLE, "width=95%"); table_header($th); $heading_shown = true; - } + } alt_table_row_color($k); - - label_cell($myrow['account']); - label_cell($myrow['account_name']); + + $counterpartyname = get_subaccount_name($myrow["account"], $myrow["person_id"]); + $counterparty_id = $counterpartyname ? sprintf(' %05d', $myrow["person_id"]) : ''; + + label_cell($myrow['account'].$counterparty_id); + label_cell($myrow['account_name'] . ($counterpartyname ? ': '.$counterpartyname : '')); if ($dim >= 1) label_cell(get_dimension_string($myrow['dimension_id'], true)); if ($dim > 1) diff --git a/includes/types.inc b/includes/types.inc index 70fb7f0c..460c5193 100644 --- a/includes/types.inc +++ b/includes/types.inc @@ -137,6 +137,80 @@ function payment_person_name($type, $person_id, $full=true) { } } +// +// Returns counterparty (supplier/customer) name for selected transaction. +// +function get_counterparty_name($trans_type, $trans_no, $full=true) +{ + switch($trans_type) + { + case ST_SALESORDER: + case ST_SALESQUOTE: + $sql = "SELECT order.customer_id as person_id, debtor.name as name + FROM ".TB_PREF."sales_orders order, ".TB_PREF."debtors_master debtor + WHERE order_no=".db_escape($trans_no)." AND trans_type=".db_escape($trans_type) + ." AND order.debtor_no=debtor.debtor_no"; + break; + + case ST_SALESINVOICE : + case ST_CUSTCREDIT : + case ST_CUSTPAYMENT : + case ST_CUSTDELIVERY : + $sql = "SELECT trans.debtor_no as person_id, debtor.name as name + FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master debtor + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.debtor_no=debtor.debtor_no"; + break; + + case ST_PURCHORDER : + $sql = "SELECT order.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."purch_orders order, ".TB_PREF."suppliers supp + WHERE order_no=".db_escape($trans_no) + ." AND order.supplier_id=supp.supplier_id"; + break; + + case ST_SUPPINVOICE : + case ST_SUPPCREDIT : + case ST_SUPPAYMENT : + $sql = "SELECT trans.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."supp_trans trans, ".TB_PREF."suppliers supp + WHERE trans_no=".db_escape($trans_no)." AND type=".db_escape($trans_type) + ." AND trans.supplier_id=supp.supplier_id"; + break; + + case ST_SUPPRECEIVE : + $sql = "SELECT trans.supplier_id as person_id, supp.supp_name as name + FROM ".TB_PREF."grn_batch trans, ".TB_PREF."suppliers supp + WHERE id=".db_escape($trans_no) + ." AND trans.supplier_id=supp.supplier_id"; + break; + + case ST_JOURNAL: // FIXME - this one can have multiply counterparties of various types depending on person_type_id + + default: + /* // internal operations + case ST_WORKORDER : + case ST_INVADJUST : // GRN/DN returns ? + case ST_BANKTRANSFER : + case ST_LOCTRANSFER : + case ST_MANUISSUE : + case ST_MANURECEIVE : + case ST_COSTUPDATE : + case ST_BANKPAYMENT : // generic bank transactions + case ST_BANKDEPOSIT :*/ + return null; + } + + $result = db_query($sql, 'cannot retrieve counterparty name'); + if (db_num_rows($result)) + { + $row = db_fetch($result); + return sprintf("[%05s] %s", $row['person_id'], $row['name']); + } + + return ''; +} + function payment_person_has_items($type) { switch ($type) { diff --git a/includes/ui/items_cart.inc b/includes/ui/items_cart.inc index a5e15fdb..25101807 100644 --- a/includes/ui/items_cart.inc +++ b/includes/ui/items_cart.inc @@ -26,7 +26,6 @@ class items_cart var $transfer_type; var $increase; var $memo_; - var $person_id; var $branch_id; var $reference; var $original_amount; @@ -95,12 +94,12 @@ class items_cart // ----------- GL item functions - function add_gl_item($code_id, $dimension_id, $dimension2_id, $amount, $reference, $description=null) + function add_gl_item($code_id, $dimension_id, $dimension2_id, $amount, $reference, $description=null, $person_id=null) { if (isset($code_id) && $code_id != "" && isset($amount) && isset($dimension_id) && isset($dimension2_id)) { - $this->gl_items[] = new gl_item($code_id, $dimension_id, $dimension2_id, $amount, $reference, $description); + $this->gl_items[] = new gl_item($code_id, $dimension_id, $dimension2_id, $amount, $reference, $description, $person_id); return true; } else @@ -112,9 +111,20 @@ class items_cart return false; } - function update_gl_item($index, $code_id, $dimension_id, $dimension2_id, $amount, $reference, $description=null) + function update_gl_item($index, $code_id, $dimension_id, $dimension2_id, $amount, $reference, $description=null, $person_id=null) { $this->gl_items[$index]->code_id = $code_id; + $this->gl_items[$index]->person_id = $person_id; + + $gl_type = is_subledger_account($code_id, $person_id); + if ($gl_type) + { + $this->gl_items[$index]->person_type_id = $gl_type > 0 ? PT_CUSTOMER : PT_SUPPLIER; + $this->gl_items[$index]->person_name = get_subaccount_name($code_id, $person_id); + } else + { + $this->gl_items[$index]->person_type_id = $this->gl_items[$index]->person_name = ''; + } $this->gl_items[$index]->dimension_id = $dimension_id; $this->gl_items[$index]->dimension2_id = $dimension2_id; $this->gl_items[$index]->amount = $amount; @@ -177,6 +187,18 @@ class items_cart $this->gl_items = array(); } + // + // Check if cart contains virtual subaccount (AP/AR) postings + // + function has_sub_accounts() + { + foreach ($this->gl_items as $gl_item) + { + if ($gl_item->person_id) + return true; + } + return false; + } } //-------------------------------------------------------------------------------------------- @@ -256,9 +278,12 @@ class gl_item var $amount; var $reference; var $description; + var $person_id; + var $person_type_id; + var $person_name; function gl_item($code_id, $dimension_id, $dimension2_id, $amount, $reference, - $description=null) + $description=null, $person_id=null) { //echo "adding $index, $code_id, $dimension_id, $amount, $reference
"; @@ -268,6 +293,13 @@ class gl_item $this->description = $description; $this->code_id = $code_id; + $this->person_id = $person_id; + $gl_type = is_subledger_account($code_id, $person_id); + if ($gl_type) + { + $this->person_type_id = $gl_type > 0 ? PT_CUSTOMER : PT_SUPPLIER; + $this->person_name = get_subaccount_name($code_id, $person_id); + } $this->dimension_id = $dimension_id; $this->dimension2_id = $dimension2_id; $this->amount = $amount; diff --git a/includes/ui/ui_lists.inc b/includes/ui/ui_lists.inc index 0fde17de..24939a1a 100644 --- a/includes/ui/ui_lists.inc +++ b/includes/ui/ui_lists.inc @@ -2359,4 +2359,54 @@ function tax_algorithm_list_row($label, $name, $value=null, $submit_on_change=fa tax_algorithm_list_cells(null, $name, $value, $submit_on_change); echo "\n"; } +//---------------------------------------------------------------------------------------------- + +function subledger_list($name, $account, $selected_id=null) +{ + global $all_items; + + $type = is_subledger_account($account); + if (!$type) + return ''; + + if($type > 0) + $sql = "SELECT DISTINCT d.debtor_no as id, debtor_ref as name + FROM " + .TB_PREF."debtors_master d," + .TB_PREF."cust_branch c + WHERE d.debtor_no=c.debtor_no AND c.receivables_account=".db_escape($account); + else + $sql = "SELECT supplier_id as id, supp_ref as name + FROM " + .TB_PREF."suppliers s + WHERE s.payable_account=".db_escape($account); + + $mode = get_company_pref('no_customer_list'); + + return combo_input($name, $selected_id, $sql, 'id', 'name', + array( + 'type' => 1, + 'size' => 20, + 'async' => false, + ) ); +} + +function subledger_list_cells($label, $name, $account, $selected_id=null) +{ + if ($label != null) + echo "$label\n"; + echo ""; + echo subledger_list($name, $account, $selected_id); + echo "\n"; +} + +function subledger_list_row($label, $name, $selected_id=null, $all_option = false, + $submit_on_change=false, $show_inactive=false, $editkey = false) +{ + echo "$label"; + echo subledger_list($name, $account, $selected_id); + echo "\n\n"; +} + + ?> \ No newline at end of file diff --git a/purchasing/includes/db/invoice_items_db.inc b/purchasing/includes/db/invoice_items_db.inc index d6953157..e851f573 100644 --- a/purchasing/includes/db/invoice_items_db.inc +++ b/purchasing/includes/db/invoice_items_db.inc @@ -44,11 +44,10 @@ function add_supp_invoice_gl_item($supp_trans_type, $supp_trans_no, $gl_code, $a function get_supp_invoice_items($supp_trans_type, $supp_trans_no) { - $sql = "SELECT *, unit_price AS FullUnitPrice FROM ".TB_PREF."supp_invoice_items inv, - ".TB_PREF."grn_items grn + $sql = "SELECT *, unit_price AS FullUnitPrice FROM " + .TB_PREF."supp_invoice_items inv LEFT JOIN ".TB_PREF."grn_items grn ON grn.id =inv.grn_item_id WHERE supp_trans_type = ".db_escape($supp_trans_type)." AND supp_trans_no = ".db_escape($supp_trans_no) - ." AND grn.id =inv.grn_item_id" ." ORDER BY inv.id"; return db_query($sql, "Cannot retreive supplier transaction detail records"); } diff --git a/purchasing/includes/ui/invoice_ui.inc b/purchasing/includes/ui/invoice_ui.inc index a508d4bb..5d28ec60 100644 --- a/purchasing/includes/ui/invoice_ui.inc +++ b/purchasing/includes/ui/invoice_ui.inc @@ -103,12 +103,11 @@ function invoice_header(&$supp_trans) ref_row(_("Reference:"), 'reference', '', $Refs->get_next($supp_trans->trans_type)); - if (isset($_POST['invoice_no'])) + if ($supp_trans->trans_type == ST_SUPPCREDIT) { - label_row(_("Supplier's Ref.:"), $_POST['invoice_no'].hidden('invoice_no', $_POST['invoice_no'], false).hidden('supp_reference', $_POST['invoice_no'], false)); - } - else - text_row(_("Supplier's Ref.:"), 'supp_reference', $_POST['supp_reference'], 20, 20); + label_row(_("Source Invoices:"), implode(',' , $supp_trans->src_docs),'','','src_docs'); + } + text_row(_("Supplier's Ref.:"), 'supp_reference', $_POST['supp_reference'], 20, 60); table_section(2, "33%"); @@ -517,11 +516,12 @@ function display_grn_items(&$supp_trans, $mode=0) alt_table_row_color($k); $grn_batch = get_grn_batch_from_item($entered_grn->id); - label_cell(get_trans_view_str(ST_SUPPRECEIVE,$grn_batch)); + label_cell(get_trans_view_str(ST_SUPPRECEIVE, $grn_batch)); if ($mode == 1) { label_cell($entered_grn->id); - label_cell(""); // PO + $row = get_grn_batch($grn_batch); + label_cell(get_trans_view_str(ST_PURCHORDER, $row["purch_order_no"])); // PO } label_cell($entered_grn->item_code); label_cell($entered_grn->item_description); diff --git a/reporting/rep601.php b/reporting/rep601.php index 1d47bc78..7ad92bcb 100644 --- a/reporting/rep601.php +++ b/reporting/rep601.php @@ -118,7 +118,7 @@ function print_bank_transactions() $rep->TextCol(1, 2, $myrow['trans_no']); $rep->TextCol(2, 3, $myrow['ref']); $rep->DateCol(3, 4, $myrow["trans_date"], true); - $rep->TextCol(4, 5, payment_person_name($myrow["person_type_id"],$myrow["person_id"], false)); + $rep->TextCol(4, 5, get_counterparty_name($myrow["type"], $myrow["type_no"], false)); if ($myrow['amount'] > 0.0) { $rep->AmountCol(5, 6, abs($myrow['amount']), $dec); diff --git a/reporting/rep702.php b/reporting/rep702.php index 01d3606a..9fdde38e 100644 --- a/reporting/rep702.php +++ b/reporting/rep702.php @@ -95,7 +95,7 @@ function print_list_of_journal_entries() $rep->TextCol(0, 1, $TransName . " # " . $myrow['type_no']); $rep->TextCol(1, 2, get_reference($myrow['type'], $myrow['type_no'])); $rep->DateCol(2, 3, $myrow['tran_date'], true); - $coms = payment_person_name($myrow["person_type_id"],$myrow["person_id"]); + $coms = get_subaccount_name($myrow["account"], $myrow["person_id"]); $memo = get_comments_string($myrow['type'], $myrow['type_no']); if ($memo != '') { diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index c9900f7c..d84441ac 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -21,4 +21,9 @@ CREATE TABLE `0_wo_costing` ( PRIMARY KEY (`id`) ) TYPE=InnoDB; +UPDATE `0_gl_trans` gl + LEFT JOIN `0_cust_branch` br ON br.receivables_account=gl.account AND br.debtor_no=gl.person_id AND gl.person_type_id=2 + LEFT JOIN `0_suppliers` sup ON sup.payable_account=gl.account AND sup.supplier_id=gl.person_id AND gl.person_type_id=3 + SET `person_id` = IF(br.receivables_account, br.debtor_no, IF(sup.payable_account, sup.supplier_id, NULL)), + `person_type_id` = IF(br.receivables_account, 2, IF(sup.payable_account, 3, NULL)); -- 2.30.2