From c640dae024fb9554d476bd863a68ef82ada8822d Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 19 Mar 2015 15:17:54 +0100 Subject: [PATCH] Fixed handling extended journal entries in various places. --- admin/db/transactions_db.inc | 46 +++--- gl/includes/db/gl_db_banking.inc | 2 +- gl/includes/db/gl_db_trans.inc | 142 +++++++++++++----- gl/inquiry/journal_inquiry.php | 20 ++- gl/view/gl_trans_view.php | 64 +++++--- includes/db/audit_trail_db.inc | 22 ++- includes/ui/ui_lists.inc | 21 +++ .../allocations/supplier_allocation_main.php | 4 +- purchasing/includes/db/supp_trans_db.inc | 9 +- .../inquiry/supplier_allocation_inquiry.php | 33 ++-- purchasing/supplier_payment.php | 6 +- reporting/rep101.php | 55 ++++--- reporting/rep102.php | 22 ++- sales/includes/db/cust_trans_db.inc | 20 ++- sales/includes/db/sales_invoice_db.inc | 4 +- sales/inquiry/customer_allocation_inquiry.php | 8 +- sales/inquiry/customer_inquiry.php | 5 +- 17 files changed, 329 insertions(+), 154 deletions(-) diff --git a/admin/db/transactions_db.inc b/admin/db/transactions_db.inc index 2ee7fa98..d3e3cfc5 100644 --- a/admin/db/transactions_db.inc +++ b/admin/db/transactions_db.inc @@ -186,30 +186,30 @@ function get_systype_db_info($type) { switch ($type) { - case ST_JOURNAL : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); - case ST_BANKPAYMENT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); - case ST_BANKDEPOSIT : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case ST_JOURNAL : return array(TB_PREF."journal", "type", "trans_no", "reference", "tran_date"); + case ST_BANKPAYMENT : return array(TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case ST_BANKDEPOSIT : return array(TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); case 3 : return null; - case ST_BANKTRANSFER : return array("".TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); - case ST_SALESINVOICE : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTCREDIT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTPAYMENT : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_CUSTDELIVERY : return array("".TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); - case ST_LOCTRANSFER : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); - case ST_INVADJUST : return array("".TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); - case ST_PURCHORDER : return array("".TB_PREF."purch_orders", null, "order_no", "reference", "ord_date"); - case ST_SUPPINVOICE : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPCREDIT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPAYMENT : return array("".TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); - case ST_SUPPRECEIVE : return array("".TB_PREF."grn_batch", null, "id", "reference", "delivery_date"); - case ST_WORKORDER : return array("".TB_PREF."workorders", null, "id", "wo_ref", "released_date"); - case ST_MANUISSUE : return array("".TB_PREF."wo_issues", null, "issue_no", "reference", "issue_date"); - case ST_MANURECEIVE : return array("".TB_PREF."wo_manufacture", null, "id", "reference", "date_"); - case ST_SALESORDER : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); - case 31 : return array("".TB_PREF."service_orders", null, "order_no", "cust_ref", "date"); - case ST_SALESQUOTE : return array("".TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); - case ST_DIMENSION : return array("".TB_PREF."dimensions", null, "id", "reference", "date_"); - case ST_COSTUPDATE : return array("".TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); + case ST_BANKTRANSFER : return array(TB_PREF."bank_trans", "type", "trans_no", "ref", "trans_date"); + case ST_SALESINVOICE : return array(TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTCREDIT : return array(TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTPAYMENT : return array(TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_CUSTDELIVERY : return array(TB_PREF."debtor_trans", "type", "trans_no", "reference", "tran_date"); + case ST_LOCTRANSFER : return array(TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); + case ST_INVADJUST : return array(TB_PREF."stock_moves", "type", "trans_no", "reference", "tran_date"); + case ST_PURCHORDER : return array(TB_PREF."purch_orders", null, "order_no", "reference", "ord_date"); + case ST_SUPPINVOICE : return array(TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPCREDIT : return array(TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPAYMENT : return array(TB_PREF."supp_trans", "type", "trans_no", "reference", "tran_date"); + case ST_SUPPRECEIVE : return array(TB_PREF."grn_batch", null, "id", "reference", "delivery_date"); + case ST_WORKORDER : return array(TB_PREF."workorders", null, "id", "wo_ref", "released_date"); + case ST_MANUISSUE : return array(TB_PREF."wo_issues", null, "issue_no", "reference", "issue_date"); + case ST_MANURECEIVE : return array(TB_PREF."wo_manufacture", null, "id", "reference", "date_"); + case ST_SALESORDER : return array(TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); + case 31 : return array(TB_PREF."service_orders", null, "order_no", "cust_ref", "date"); + case ST_SALESQUOTE : return array(TB_PREF."sales_orders", "trans_type", "order_no", "reference", "ord_date"); + case ST_DIMENSION : return array(TB_PREF."dimensions", null, "id", "reference", "date_"); + case ST_COSTUPDATE : return array(TB_PREF."gl_trans", "type", "type_no", null, "tran_date"); } display_db_error("invalid type ($type) sent to get_systype_db_info", "", true); diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index a26d92e7..cbeea72b 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -92,7 +92,7 @@ function get_cust_account_curr_balances($date) { $to = date2sql($date); - $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT.", + $sql = "SELECT SUM(IF(t.type =".ST_CUSTCREDIT." OR t.type = ".ST_CUSTPAYMENT." OR t.type = ".ST_BANKDEPOSIT." OR t.type = ".ST_JOURNAL.", -(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount))) AS amount, dt.debtor_no, dt.name, dt.curr_code, b.receivables_account diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index d42bd732..18c6ba66 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -104,45 +104,69 @@ function add_gl_balance($type, $trans_id, $date_, $amount, $person_type_id=null, function get_gl_transactions($from_date, $to_date, $trans_no=0, $account=null, $dimension=0, $dimension2=0, $filter_type=null, - $amount_min=null, $amount_max=null) + $amount_min=null, $amount_max=null, $person_id=null) { global $SysPrefs; - + $from = date2sql($from_date); $to = date2sql($to_date); - $sql = "SELECT ".TB_PREF."gl_trans.*, " - .TB_PREF."chart_master.account_name FROM " - .TB_PREF."gl_trans - LEFT JOIN ".TB_PREF."voided v ON " - .TB_PREF."gl_trans.type_no=v.id AND v.type=".TB_PREF."gl_trans.type," - .TB_PREF."chart_master" - ." WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account + $sql = "SELECT gl.*, j.event_date, j.doc_date, a.gl_seq, u.user_id, st.supp_reference, gl.person_id subcode, + IFNULL(IFNULL(sup.supp_name, debt.name), bt.person_id) as person_name, + IFNULL(gl.person_id, IFNULL(sup.supplier_id, debt.debtor_no)) as person_id, + IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date, + coa.account_name, ref.reference + FROM " + .TB_PREF."gl_trans gl + LEFT JOIN ".TB_PREF."voided v ON gl.type_no=v.id AND v.type=gl.type + + LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id) + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id + LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no) + + LEFT JOIN ".TB_PREF."suppliers sup ON st.supplier_id=sup.supplier_id OR grn.supplier_id=sup.supplier_id + LEFT JOIN ".TB_PREF."cust_branch branch ON dt.branch_code=branch.branch_code + LEFT JOIN ".TB_PREF."debtors_master debt ON dt.debtor_no=debt.debtor_no + + LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0 + AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id + + LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no + LEFT JOIN ".TB_PREF."audit_trail a ON a.type=gl.type AND a.trans_no=gl.type_no AND NOT ISNULL(gl_seq) + LEFT JOIN ".TB_PREF."users u ON a.user=u.id + + LEFT JOIN ".TB_PREF."refs ref ON ref.type=gl.type AND ref.id=gl.type_no," + .TB_PREF."chart_master coa + WHERE coa.account_code=gl.account AND ISNULL(v.date_) - AND tran_date >= '$from' - AND tran_date <= '$to'"; + AND gl.tran_date >= '$from' + AND gl.tran_date <= '$to'"; if (isset($SysPrefs->show_voided_gl_trans) && $SysPrefs->show_voided_gl_trans == 0) - $sql .= " AND ".TB_PREF."gl_trans.amount <> 0"; + $sql .= " AND gl.amount <> 0"; + + if ($person_id) + $sql .= " AND gl.person_id=".db_escape($person_id); + if ($trans_no > 0) - $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE ".db_escape('%'.$trans_no); + $sql .= " AND gl.type_no LIKE ".db_escape('%'.$trans_no);; if ($account != null) - $sql .= " AND ".TB_PREF."gl_trans.account = ".db_escape($account); + $sql .= " AND gl.account = ".db_escape($account); - if ($dimension != 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension_id = ".($dimension<0?0:db_escape($dimension)); + if ($dimension > 0) + $sql .= " AND gl.dimension_id = ".($dimension<0 ? 0 : db_escape($dimension)); - if ($dimension2 != 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = ".($dimension2<0?0:db_escape($dimension2)); + if ($dimension2 > 0) + $sql .= " AND gl.dimension2_id = ".($dimension2<0 ? 0 : db_escape($dimension2)); if ($filter_type != null AND is_numeric($filter_type)) - $sql .= " AND ".TB_PREF."gl_trans.type= ".db_escape($filter_type); - + $sql .= " AND gl.type= ".db_escape($filter_type); + if ($amount_min != null) - $sql .= " AND ABS(".TB_PREF."gl_trans.amount) >= ABS(".db_escape($amount_min).")"; + $sql .= " AND ABS(gl.amount) >= ABS(".db_escape($amount_min).")"; if ($amount_max != null) - $sql .= " AND ABS(".TB_PREF."gl_trans.amount) <= ABS(".db_escape($amount_max).")"; + $sql .= " AND ABS(gl.amount) <= ABS(".db_escape($amount_max).")"; $sql .= " ORDER BY tran_date, counter"; @@ -154,14 +178,27 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, function get_gl_trans($type, $trans_id) { - $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference FROM " - .TB_PREF."gl_trans as gl + $sql = "SELECT gl.*, cm.account_name, IF(ISNULL(refs.reference), '', refs.reference) AS reference, user.real_name, + IFNULL(st.tran_date, IFNULL(dt.tran_date, IFNULL(bt.trans_date, IFNULL(grn.delivery_date, gl.tran_date)))) as doc_date, + IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference + FROM ".TB_PREF."gl_trans as gl LEFT JOIN ".TB_PREF."chart_master as cm ON gl.account = cm.account_code - LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id)" + LEFT JOIN ".TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id) + LEFT JOIN ".TB_PREF."audit_trail as audit ON (gl.type=audit.type AND gl.type_no=audit.trans_no AND NOT ISNULL(gl_seq)) + LEFT JOIN ".TB_PREF."users as user ON (audit.user=user.id) + # all this below just to retrieve doc_date :> + LEFT JOIN ".TB_PREF."supp_trans st ON gl.type_no=st.trans_no AND st.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=st.supplier_id) + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=gl.type_no AND gl.type=".ST_SUPPRECEIVE." AND gl.person_id=grn.supplier_id + LEFT JOIN ".TB_PREF."debtor_trans dt ON gl.type_no=dt.trans_no AND dt.type=gl.type AND (gl.type!=".ST_JOURNAL." OR gl.person_id=dt.debtor_no) + LEFT JOIN ".TB_PREF."bank_trans bt ON bt.type=gl.type AND bt.trans_no=gl.type_no AND bt.amount!=0 + AND bt.person_type_id=gl.person_type_id AND bt.person_id=gl.person_id + LEFT JOIN ".TB_PREF."journal j ON j.type=gl.type AND j.trans_no=gl.type_no" + ." WHERE gl.type= ".db_escape($type) ." AND gl.type_no = ".db_escape($trans_id) ." AND gl.amount <> 0" - ." ORDER BY counter"; + ." ORDER BY tran_date, counter"; + return db_query($sql, "The gl transactions could not be retrieved"); } @@ -518,18 +555,26 @@ function clear_gl_trans($type, $trans_id, $nested=false) commit_transaction(); } -function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false) +function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false, + $user_id=null, $contractor_id=null, $dimension=null) { $sql = "SELECT IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq, gl.tran_date, - gl.type, - gl.type_no, - refs.reference, - SUM(IF(gl.amount>0, gl.amount,0)) as amount, - com.memo_, - IF(ISNULL(u.user_id),'',u.user_id) as user_id - FROM ".TB_PREF."gl_trans as gl + gl.type as trans_type, + gl.type_no as trans_no, + IFNULL(max(supp.supp_name), max(cust.name)) as name, + IF(ISNULL(st.supp_reference), '', st.supp_reference) AS supp_reference, + refs.reference," + .($dimension ? " -SUM(IF(dim.dimension in(gl.dimension_id,gl.dimension2_id), gl.amount, 0)) as amount,":" SUM(IF(gl.amount>0, gl.amount,0)) as amount,") + ."com.memo_, + IF(ISNULL(u.user_id),'',u.user_id) as user_id"; + + if ($contractor_id > 0) { + $sql.= ", st.supplier_id, dt.debtor_no "; + } + + $sql.= " FROM ".TB_PREF."gl_trans as gl LEFT JOIN ".TB_PREF."audit_trail as a ON (gl.type=a.type AND gl.type_no=a.trans_no) LEFT JOIN ".TB_PREF."comments as com ON @@ -538,11 +583,18 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al (gl.type=refs.type AND gl.type_no=refs.id) LEFT JOIN ".TB_PREF."users as u ON a.user=u.id - WHERE gl.tran_date >= '" . date2sql($from) . "' + LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=gl.type AND gl.type_no=dt.trans_no + LEFT JOIN ".TB_PREF."debtors_master cust ON gl.person_type_id=2 AND gl.person_id=cust.debtor_no + LEFT JOIN ".TB_PREF."supp_trans st ON st.type=gl.type AND gl.type_no=st.trans_no + LEFT JOIN ".TB_PREF."suppliers supp ON gl.person_type_id=3 AND gl.person_id=supp.supplier_id" + .($dimension ? + " LEFT JOIN (SELECT type, type_no, MAX(IFNULL(dimension_id, dimension2_id)) dimension FROM ".TB_PREF."gl_trans GROUP BY type, type_no) dim + ON gl.type=dim.type AND gl.type_no=dim.type_no" : '') + ." WHERE gl.tran_date >= '" . date2sql($from) . "' AND gl.tran_date <= '" . date2sql($to) . "' AND gl.amount!=0"; if ($ref) { - $sql .= " AND reference LIKE ". db_escape("%$ref%"); + $sql .= " AND refs.reference LIKE ". db_escape("%$ref%"); } if ($memo) { $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%"); @@ -553,9 +605,21 @@ function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $al if (!$alsoclosed) { $sql .= " AND gl_seq=0"; } - else - $sql .= " AND NOT ISNULL(a.gl_seq)"; + else + $sql .= " AND NOT ISNULL(a.gl_seq)"; + + if ($user_id != null) + $sql .= " AND user_id = ".db_escape($user_id); + + if ($contractor_id > 0) { + $sql.= " AND (dt.debtor_no =".$contractor_id; + $sql.= " OR st.supplier_id =".$contractor_id.") "; + } + + if ($dimension != null) + $sql .= " AND dim.dimension = ".db_escape($dimension); + + $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl_seq, gl.type, gl.type_no"; - $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no"; return $sql; } diff --git a/gl/inquiry/journal_inquiry.php b/gl/inquiry/journal_inquiry.php index 0b5a47dc..ad77e8bf 100644 --- a/gl/inquiry/journal_inquiry.php +++ b/gl/inquiry/journal_inquiry.php @@ -48,12 +48,15 @@ journal_types_list_cells(_("Type:"), "filterType"); date_cells(_("From:"), 'FromDate', '', null, 0, -1, 0); date_cells(_("To:"), 'ToDate'); -check_cells( _("Show closed:"), 'AlsoClosed', null); - submit_cells('Search', _("Search"), '', '', 'default'); end_row(); start_row(); ref_cells(_("Memo:"), 'Memo', '',null, _('Enter memo fragment or leave empty')); +users_list_cells(_("User:"), 'userid', null, false); +if (get_company_pref('use_dimension') && isset($_POST['dimension'])) // display dimension only, when started in dimension mode + dimensions_list_cells(_('Dimension:'), 'dimension', null, true, null, true); +users_list_cells(_("User:"), 'userid', null, false); +check_cells( _("Show closed:"), 'AlsoClosed', null); end_row(); end_table(); @@ -71,25 +74,25 @@ function systype_name($dummy, $type) function view_link($row) { - return get_trans_view_str($row["type"], $row["type_no"]); + return get_trans_view_str($row["trans_type"], $row["trans_no"]); } function gl_link($row) { - return get_gl_view_str($row["type"], $row["type_no"]); + return get_gl_view_str($row["trans_type"], $row["trans_no"]); } function edit_link($row) { $ok = true; - if ($row['type'] == ST_SALESINVOICE) + if ($row['trans_type'] == ST_SALESINVOICE) { - $myrow = get_customer_trans($row["type_no"], $row["type"]); - if ($myrow['alloc'] != 0 || get_voided_entry(ST_SALESINVOICE, $row["type_no"]) !== false) + $myrow = get_customer_trans($row["trans_no"], $row["trans_type"]); + if ($myrow['alloc'] != 0 || get_voided_entry(ST_SALESINVOICE, $row["trans_no"]) !== false) $ok = false; } - return $ok ? trans_editor_link($row["type"], $row["type_no"]) : ''; + return $ok ? trans_editor_link($row["trans_type"], $row["trans_type"]) : ''; } $sql = get_sql_for_journal_inquiry(get_post('filterType', -1), get_post('FromDate'), @@ -100,6 +103,7 @@ $cols = array( _("Date") =>array('name'=>'tran_date','type'=>'date','ord'=>'desc'), _("Type") => array('fun'=>'systype_name'), _("Trans #") => array('fun'=>'view_link'), + _("Counterparty") => array('ord' => ''), _("Reference"), _("Amount") => array('type'=>'amount'), _("Memo"), diff --git a/gl/view/gl_trans_view.php b/gl/view/gl_trans_view.php index b202b6bc..7e37d0a6 100644 --- a/gl/view/gl_trans_view.php +++ b/gl/view/gl_trans_view.php @@ -29,27 +29,54 @@ if (!isset($_GET['type_id']) || !isset($_GET['trans_no'])) function display_gl_heading($myrow) { - global $systypes_array; + global $systypes_array, $type_shortcuts; + $trans_name = $systypes_array[$_GET['type_id']]; + $journal = $_GET['type_id'] == ST_JOURNAL; + start_table(TABLESTYLE, "width='95%'"); $th = array(_("General Ledger Transaction Details"), _("Reference"), - _("Date")); - - if ($_GET['type_id'] != ST_JOURNAL) - $th[] = _("Counterparty"); - - table_header($th); - start_row(); + _("Transaction Date"), _("Journal #")); + + if ($_GET['type_id'] == ST_JOURNAL) + array_insert($th, 3, array(_("Document Date"), _("Event Date"))); + else + array_insert($th, 3, array(_("Counterparty"))); + + if($myrow['supp_reference']) + { + array_insert($th, 2, array(_("Supplier Reference"))); + } + table_header($th); + start_row(); label_cell("$trans_name #" . $_GET['trans_no']); - label_cell($myrow["reference"]); - label_cell(sql2date($myrow["tran_date"])); - if ($_GET['type_id'] != ST_JOURNAL) + label_cell($type_shortcuts[$myrow['type']].' '.$myrow["reference"], "align='center'"); + if($myrow['supp_reference']) + { + label_cell($myrow["supp_reference"], "align='center'"); + } + label_cell(sql2date($myrow["doc_date"]), "align='center'"); + if ($journal) + { + $header = get_journal($myrow['type'], $_GET['trans_no']); + label_cell(sql2date($header["doc_date"]), "align='center'"); + label_cell(sql2date($header["event_date"]), "align='center'"); + } else label_cell(get_counterparty_name($_GET['type_id'],$_GET['trans_no'])); - + label_cell( get_journal_number($myrow['type'], $_GET['trans_no']), "align='center'"); end_row(); + start_row(); + label_cells(_('Entered By'), $myrow["real_name"], "class='tableheader2'", "colspan=" . + ($journal ? ($header['rate']==1 ? '3':'1'):'6')); + if ($journal) + { + if ($header['rate'] != 1) + label_cells(_('Exchange rate'), $header["rate"].' ', "class='tableheader2'"); + label_cells(_('Source document'), $header["source_ref"], "class='tableheader2'"); + } + end_row(); comments_display_row($_GET['type_id'], $_GET['trans_no']); - end_table(1); } $result = get_gl_trans($_GET['type_id'], $_GET['trans_no']); @@ -65,13 +92,13 @@ if (db_num_rows($result) == 0) $dim = get_company_pref('use_dimension'); if ($dim == 2) - $th = array(_("Account Code"), _("Account Name"), _("Dimension")." 1", _("Dimension")." 2", + $th = array(_("Journal Date"), _("Account Code"), _("Account Name"), _("Dimension")." 1", _("Dimension")." 2", _("Debit"), _("Credit"), _("Memo")); else if ($dim == 1) - $th = array(_("Account Code"), _("Account Name"), _("Dimension"), + $th = array(_("Journal Date"), _("Account Code"), _("Account Name"), _("Dimension"), _("Debit"), _("Credit"), _("Memo")); else - $th = array(_("Account Code"), _("Account Name"), + $th = array(_("Journal Date"), _("Account Code"), _("Account Name"), _("Debit"), _("Credit"), _("Memo")); $k = 0; //row colour counter @@ -94,6 +121,7 @@ while ($myrow = db_fetch($result)) $counterpartyname = get_subaccount_name($myrow["account"], $myrow["person_id"]); $counterparty_id = $counterpartyname ? sprintf(' %05d', $myrow["person_id"]) : ''; + label_cell(sql2date($myrow['tran_date'])); label_cell($myrow['account'].$counterparty_id); label_cell($myrow['account_name'] . ($counterpartyname ? ': '.$counterpartyname : '')); if ($dim >= 1) @@ -109,10 +137,11 @@ while ($myrow = db_fetch($result)) else $credit += $myrow['amount']; } + if ($heading_shown) { start_row("class='inquirybg' style='font-weight:bold'"); - label_cell(_("Total"), "colspan=2"); + label_cell(_("Total"), "colspan=3"); if ($dim >= 1) label_cell(''); if ($dim > 1) @@ -129,4 +158,3 @@ if ($heading_shown) is_voided_display($_GET['type_id'], $_GET['trans_no'], _("This transaction has been voided.")); end_page(true, false, false, $_GET['type_id'], $_GET['trans_no']); - diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc index c341a2f0..6aa91d98 100644 --- a/includes/db/audit_trail_db.inc +++ b/includes/db/audit_trail_db.inc @@ -107,25 +107,33 @@ function close_transactions($todate) { return $errors; } -/* - Closed transactions have gl_seq number assigned. -*/ -function is_closed_trans($type, $trans_no) { +function get_journal_number($type, $trans_no) { $cdate = get_company_pref('gl_closing_date'); if (!$cdate) return false; // FIXME: gl_date can be badly entered for some transactions due to bug in previous FA versions - $sql = "SELECT gl_seq FROM ".TB_PREF."audit_trail" + $sql = "SELECT MAX(gl_seq) as gl_seq FROM ".TB_PREF."audit_trail" . " WHERE type=".db_escape($type) ." AND trans_no=".db_escape($trans_no) ." AND gl_date<='$cdate'"; // date is stored in sql format // ." AND (gl_date<='$cdate'" // some transaction can be not sequenced due to 0 amount, however after edition this could change // ." OR gl_seq>0)"; - $res = db_query($sql, "Cannot check transaction"); + if (db_num_rows($res)) + { + $myrow =db_fetch($res); + return $myrow['gl_seq'] ? $myrow['gl_seq'] : _('None'); + } + return "-"; +} + +/* + Closed transactions have gl_seq number assigned. +*/ +function is_closed_trans($type, $trans_no) { - return db_num_rows($res); + return get_journal_number($type, $trans_no) > 0; } diff --git a/includes/ui/ui_lists.inc b/includes/ui/ui_lists.inc index 2c55d1b1..e5e113e4 100644 --- a/includes/ui/ui_lists.inc +++ b/includes/ui/ui_lists.inc @@ -2447,3 +2447,24 @@ function accounts_type_list_row($label, $name, $selected_id=null) echo "\n"; } +function users_list_cells($label, $name, $selected_id=null, $submit_on_change=false, $spec_opt=true) +{ + $where = false; + $sql = " SELECT user_id, real_name FROM ".TB_PREF."users"; + + if ($label != null) + echo "$label\n"; + echo ""; + + echo combo_input($name, $selected_id, $sql, 'user_id', 'real_name', + array( + 'spec_option' => $spec_opt===true ?_("All users") : $spec_opt, + 'spec_id' => '', + 'order' => 'real_name', + 'select_submit'=> $submit_on_change, + 'async' => false + ) ); + echo ""; + +} + diff --git a/purchasing/allocations/supplier_allocation_main.php b/purchasing/allocations/supplier_allocation_main.php index d4fef640..c8264626 100644 --- a/purchasing/allocations/supplier_allocation_main.php +++ b/purchasing/allocations/supplier_allocation_main.php @@ -69,12 +69,12 @@ function alloc_link($row) { return pager_link(_("Allocate"), "/purchasing/allocations/supplier_allocate.php?trans_no=" - .$row["trans_no"] . "&trans_type=" . $row["type"], ICON_ALLOC); + .$row["trans_no"] . "&trans_type=" . $row["type"]. "&supplier_id=" . $row["supplier_id"], ICON_ALLOC); } function amount_left($row) { - return price_format(-$row["Total"]-$row["alloc"]); + return price_format($row['type'] == ST_JOURNAL ? abs($row["Total"])-$row["alloc"] : -$row["Total"]-$row["alloc"]); } function amount_total($row) diff --git a/purchasing/includes/db/supp_trans_db.inc b/purchasing/includes/db/supp_trans_db.inc index a03ea70d..35b7c9ab 100644 --- a/purchasing/includes/db/supp_trans_db.inc +++ b/purchasing/includes/db/supp_trans_db.inc @@ -51,7 +51,7 @@ function write_supp_trans($type, $trans_no, $supplier_id, $date_, $due_date, $re //------------------------------------------------------------------------------------------------------------- -function get_supp_trans($trans_no, $trans_type=-1) +function get_supp_trans($trans_no, $trans_type=-1, $supplier_id=null) { $sql = "SELECT ".TB_PREF."supp_trans.*, (".TB_PREF."supp_trans.ov_amount+".TB_PREF."supp_trans.ov_gst+".TB_PREF."supp_trans.ov_discount) AS Total, ".TB_PREF."suppliers.supp_name AS supplier_name, ".TB_PREF."suppliers.curr_code AS curr_code "; @@ -75,7 +75,10 @@ function get_supp_trans($trans_no, $trans_type=-1) $sql .= " WHERE ".TB_PREF."supp_trans.trans_no=".db_escape($trans_no)." AND ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id"; - if ($trans_type > 0) + if (isset($supplier_id)) + $sql .= " AND ".TB_PREF."supp_trans.supplier_id=".db_escape($supplier_id); + + if ($trans_type > -1) $sql .= " AND ".TB_PREF."supp_trans.type=".db_escape($trans_type); if ($trans_type == ST_SUPPAYMENT || $trans_type == ST_BANKPAYMENT) @@ -101,10 +104,10 @@ function get_supp_trans($trans_no, $trans_type=-1) display_db_error("duplicate supplier transactions found for given params", $sql, true); exit; } - return db_fetch($result); } + //---------------------------------------------------------------------------------------- function exists_supp_trans($type, $type_no) diff --git a/purchasing/inquiry/supplier_allocation_inquiry.php b/purchasing/inquiry/supplier_allocation_inquiry.php index 84fb2e12..7f3b6196 100644 --- a/purchasing/inquiry/supplier_allocation_inquiry.php +++ b/purchasing/inquiry/supplier_allocation_inquiry.php @@ -87,23 +87,32 @@ function due_date($row) function fmt_balance($row) { - $value = ($row["type"] == ST_BANKPAYMENT || $row["type"] == ST_SUPPCREDIT || $row["type"] == ST_SUPPAYMENT) - ? -$row["TotalAmount"] - $row["Allocated"] - : $row["TotalAmount"] - $row["Allocated"]; + $value = ($row["type"] == ST_BANKPAYMENT || $row["type"] == ST_SUPPCREDIT || $row["type"] == ST_SUPPAYMENT) ? -$row["TotalAmount"] - $row["Allocated"] + : ($row["type"] == ST_JOURNAL ? abs($row["TotalAmount"]) - $row["Allocated"] : + $row["TotalAmount"] - $row["Allocated"]); return $value; } function alloc_link($row) { - if (($row["type"] == ST_BANKPAYMENT || $row["type"] == ST_SUPPCREDIT || $row["type"] == ST_SUPPAYMENT) - && (-$row["TotalAmount"] - $row["Allocated"]) >= 0) - return pager_link(_("Allocations"), "/purchasing/allocations/supplier_allocate.php?trans_no=" . - $row["trans_no"]. "&trans_type=" . $row["type"]. "&supplier_id=" . $row["supplier_id"], ICON_ALLOC); - elseif ($row["type"] == ST_SUPPINVOICE && ($row["TotalAmount"] - $row["Allocated"]) > 0) - return pager_link(_("Pay"), "/purchasing/supplier_payment.php?supplier_id=".$row["supplier_id"] - ."&PInvoice=".$row["trans_no"], ICON_MONEY ); - else - return ''; + $link = + pager_link(_("Allocations"), + "/purchasing/allocations/supplier_allocate.php?trans_no=" . + $row["trans_no"]. "&trans_type=" . $row["type"]. "&supplier_id=" . $row["supplier_id"], ICON_ALLOC ); + + if ($row["type"] == ST_BANKPAYMENT || $row["type"] == ST_SUPPAYMENT || + (($row["type"] == ST_SUPPCREDIT || $row["type"] == ST_JOURNAL) && $row["TotalAmount"] < 0)) + return floatcmp(-$row["TotalAmount"], $row["Allocated"]) ? $link : ''; + + $link = + pager_link(_("Payment"), + "/purchasing/supplier_payment.php?supplier_id=".$row["supplier_id"]."&PInvoice=" + . $row["trans_no"]."&trans_type=" . $row["type"], ICON_MONEY); + + if ($row["type"] == ST_SUPPINVOICE || (($row["type"] == ST_SUPPCREDIT || $row["type"] == ST_JOURNAL) && $row["TotalAmount"] > 0)) + return floatcmp($row["TotalAmount"], $row["Allocated"]) ? $link : ''; + + } function fmt_debit($row) diff --git a/purchasing/supplier_payment.php b/purchasing/supplier_payment.php index f9f1105e..955bd23c 100644 --- a/purchasing/supplier_payment.php +++ b/purchasing/supplier_payment.php @@ -71,16 +71,16 @@ if (!isset($_POST['bank_account'])) { // first page call if (isset($_GET['PInvoice'])) { // get date and supplier - $inv = get_supp_trans($_GET['PInvoice'], ST_SUPPINVOICE); + $inv = get_supp_trans($_GET['PInvoice'], $_GET['trans_type']); $dflt_act = get_default_bank_account($inv['curr_code']); $_POST['bank_account'] = $dflt_act['id']; - if($inv) { + if ($inv) { $_SESSION['alloc']->person_id = $_POST['supplier_id'] = $inv['supplier_id']; $_SESSION['alloc']->read(); $_POST['DatePaid'] = sql2date($inv['tran_date']); $_POST['memo_'] = $inv['supp_reference']; foreach($_SESSION['alloc']->allocs as $line => $trans) { - if ($trans->type == ST_SUPPINVOICE && $trans->type_no == $_GET['PInvoice']) { + if ($trans->type == $_GET['trans_type'] && $trans->type_no == $_GET['PInvoice']) { $un_allocated = abs($trans->amount) - $trans->amount_allocated; $_SESSION['alloc']->amount = $_SESSION['alloc']->allocs[$line]->current_allocated = $un_allocated; $_POST['amount'] = $_POST['amount'.$line] = price_format($un_allocated); diff --git a/reporting/rep101.php b/reporting/rep101.php index bfadef13..a7d1c1f3 100644 --- a/reporting/rep101.php +++ b/reporting/rep101.php @@ -35,14 +35,14 @@ function get_open_balance($debtorno, $to) if($to) $to = date2sql($to); - $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges, - SUM(IF(t.type <> ".ST_SALESINVOICE." AND t.type <> ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits, - SUM(t.alloc) AS Allocated, - SUM(IF(t.type = ".ST_SALESINVOICE." OR t.type = ".ST_BANKPAYMENT.", - (t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount - t.alloc), - ((t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1 + t.alloc))) AS OutStanding + $sql = "SELECT SUM(IF(t.type = ".ST_SALESINVOICE." OR (t.type = ".ST_JOURNAL." AND t.ov_amount>0), + -abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount), 0)) AS charges,"; + $sql .= "SUM(IF(t.type != ".ST_SALESINVOICE." AND NOT(t.type = ".ST_JOURNAL." AND t.ov_amount>0), + abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) * -1, 0)) AS credits,"; + $sql .= "SUM(t.alloc) AS Allocated,"; + + $sql .= "SUM(IF(t.type = ".ST_SALESINVOICE.", 1, -1) * + (abs(t.ov_amount + t.ov_gst + t.ov_freight + t.ov_freight_tax + t.ov_discount) - abs(t.alloc))) AS OutStanding FROM ".TB_PREF."debtor_trans t WHERE t.debtor_no = ".db_escape($debtorno) ." AND t.type <> ".ST_CUSTDELIVERY; @@ -59,19 +59,34 @@ function get_transactions($debtorno, $from, $to) $from = date2sql($from); $to = date2sql($to); - $sql = "SELECT ".TB_PREF."debtor_trans.*, - (".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + - ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) - AS TotalAmount, ".TB_PREF."debtor_trans.alloc AS Allocated, - ((".TB_PREF."debtor_trans.type = ".ST_SALESINVOICE.") - AND ".TB_PREF."debtor_trans.due_date < '$to') AS OverDue - FROM ".TB_PREF."debtor_trans - WHERE ".TB_PREF."debtor_trans.tran_date >= '$from' - AND ".TB_PREF."debtor_trans.tran_date <= '$to' - AND ".TB_PREF."debtor_trans.debtor_no = ".db_escape($debtorno)." - AND ".TB_PREF."debtor_trans.type <> ".ST_CUSTDELIVERY." - ORDER BY ".TB_PREF."debtor_trans.tran_date"; + $allocated_from = + "(SELECT trans_type_from as trans_type, trans_no_from as trans_no, date_alloc, sum(amt) amount + FROM ".TB_PREF."cust_allocations alloc + WHERE person_id=".db_escape($debtorno)." + AND date_alloc <= '$to' + GROUP BY trans_type_from, trans_no_from) alloc_from"; + $allocated_to = + "(SELECT trans_type_to as trans_type, trans_no_to as trans_no, date_alloc, sum(amt) amount + FROM ".TB_PREF."cust_allocations alloc + WHERE person_id=".db_escape($debtorno)." + AND date_alloc <= '$to' + GROUP BY trans_type_to, trans_no_to) alloc_to"; + + $sql = "SELECT trans.*, + (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, + IFNULL(alloc_from.amount, alloc_to.amount) AS Allocated, + ((trans.type = ".ST_SALESINVOICE.") AND trans.due_date < '$to') AS OverDue + FROM ".TB_PREF."debtor_trans trans + LEFT JOIN ".TB_PREF."voided voided ON trans.type=voided.type AND trans.trans_no=voided.id + LEFT JOIN $allocated_from ON alloc_from.trans_type = trans.type AND alloc_from.trans_no = trans.trans_no + LEFT JOIN $allocated_to ON alloc_to.trans_type = trans.type AND alloc_to.trans_no = trans.trans_no + WHERE trans.tran_date >= '$from' + AND trans.tran_date <= '$to' + AND trans.debtor_no = ".db_escape($debtorno)." + AND trans.type <> ".ST_CUSTDELIVERY." + AND ISNULL(voided.id) + ORDER BY trans.tran_date"; return db_query($sql,"No transactions were returned"); } diff --git a/reporting/rep102.php b/reporting/rep102.php index 9fb6459d..d108f1c1 100644 --- a/reporting/rep102.php +++ b/reporting/rep102.php @@ -42,13 +42,14 @@ function get_invoices($customer_id, $to, $all=true) $value = "(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc)"; + $sign = "IF(`type` IN(".implode(',', array(ST_CUSTCREDIT,ST_CUSTPAYMENT,ST_BANKDEPOSIT,ST_JOURNAL))."), -1, 1)"; $due = "IF (".TB_PREF."debtor_trans.type=".ST_SALESINVOICE.",".TB_PREF."debtor_trans.due_date,".TB_PREF."debtor_trans.tran_date)"; $sql = "SELECT ".TB_PREF."debtor_trans.type, ".TB_PREF."debtor_trans.reference, ".TB_PREF."debtor_trans.tran_date, - $value as Balance, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$value,0) AS Due, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$value,0) AS Overdue1, - IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$value,0) AS Overdue2 + $sign*$value as Balance, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) > 0,$sign*$value,0) AS Due, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays1,$sign*$value,0) AS Overdue1, + IF ((TO_DAYS('$todate') - TO_DAYS($due)) > $PastDueDays2,$sign*$value,0) AS Overdue2 FROM ".TB_PREF."debtors_master, ".TB_PREF."debtor_trans @@ -57,9 +58,13 @@ function get_invoices($customer_id, $to, $all=true) AND ".TB_PREF."debtors_master.debtor_no = ".TB_PREF."debtor_trans.debtor_no AND ".TB_PREF."debtor_trans.debtor_no = $customer_id AND ".TB_PREF."debtor_trans.tran_date <= '$todate' - AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount) > ".FLOAT_COMP_DELTA." "; + AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " + .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " + .TB_PREF."debtor_trans.ov_discount) > " . FLOAT_COMP_DELTA; if (!$all) - $sql .= "AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + ".TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + ".TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc) > ".FLOAT_COMP_DELTA." "; + $sql .= "AND ABS(".TB_PREF."debtor_trans.ov_amount + ".TB_PREF."debtor_trans.ov_gst + " + .TB_PREF."debtor_trans.ov_freight + ".TB_PREF."debtor_trans.ov_freight_tax + " + .TB_PREF."debtor_trans.ov_discount - ".TB_PREF."debtor_trans.alloc) > " . FLOAT_COMP_DELTA; $sql .= "ORDER BY ".TB_PREF."debtor_trans.tran_date"; return db_query($sql, "The customer details could not be retrieved"); @@ -175,7 +180,7 @@ function print_aged_customer_analysis() if ($no_zeros && floatcmp(array_sum($str), 0) == 0) continue; $rep->fontSize += 2; - $rep->TextCol(0, 2, $myrow['name']); + $rep->TextCol(0, 2, $myrow["name"]); if ($convert) $rep->TextCol(2, 3, $myrow['curr_code']); $rep->fontSize -= 2; $total[0] += ($custrec["Balance"] - $custrec["Due"]); @@ -198,6 +203,7 @@ function print_aged_customer_analysis() $rep->TextCol(0, 1, $systypes_array[$trans['type']], -2); $rep->TextCol(1, 2, $trans['reference'], -2); $rep->DateCol(2, 3, $trans['tran_date'], true, -2); + if ($trans['type'] == ST_CUSTCREDIT || $trans['type'] == ST_CUSTPAYMENT || $trans['type'] == ST_BANKDEPOSIT) { $trans['Balance'] *= -1; @@ -205,6 +211,7 @@ function print_aged_customer_analysis() $trans['Overdue1'] *= -1; $trans['Overdue2'] *= -1; } + foreach ($trans as $i => $value) $trans[$i] *= $rate; $str = array($trans["Balance"] - $trans["Due"], @@ -260,4 +267,3 @@ function print_aged_customer_analysis() $rep->NewLine(); $rep->End(); } - diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index dfca4cc8..d6eddbe0 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -111,13 +111,14 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, } db_query($sql, "The debtor transaction record could not be inserted"); - add_audit_trail($trans_type, $trans_no, $date_, $new ? '': _("Updated.")); + if ($trans_type != ST_JOURNAL) // skip for journal entries + add_audit_trail($trans_type, $trans_no, $date_, $new ? '': _("Updated.")); return $trans_no; } //---------------------------------------------------------------------------------------- -function get_customer_trans($trans_id, $trans_type) +function get_customer_trans($trans_id, $trans_type, $customer_id=null) { global $SysPrefs; @@ -131,7 +132,6 @@ function get_customer_trans($trans_id, $trans_type) if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) { // it's a payment so also get the bank account - // Chaitanya : Added bank_act to support Customer Payment Edit $sql .= ",bank_act,".TB_PREF."bank_accounts.bank_name, ".TB_PREF."bank_accounts.bank_account_name, ".TB_PREF."bank_accounts.account_type AS BankTransType, ".TB_PREF."bank_accounts.bank_curr_code, @@ -149,6 +149,10 @@ function get_customer_trans($trans_id, $trans_type) .TB_PREF."tax_groups.id AS tax_group_id "; } + if ($trans_type == ST_JOURNAL) { + $sql .= ", branch.*"; + } + $sql .= " FROM ".TB_PREF."debtor_trans trans LEFT JOIN ".TB_PREF."comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN ".TB_PREF."shippers ON ".TB_PREF."shippers.shipper_id=trans.ship_via, @@ -166,10 +170,17 @@ function get_customer_trans($trans_id, $trans_type) .TB_PREF."tax_groups "; } + if ($trans_type == ST_JOURNAL) { + $sql .= ", ".TB_PREF."cust_branch branch "; + } + $sql .= " WHERE trans.trans_no=".db_escape($trans_id)." AND trans.type=".db_escape($trans_type)." AND trans.debtor_no=cust.debtor_no"; + if (isset($customer_id)) + $sql .= " AND trans.debtor_no=".db_escape($customer_id); + if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) { // it's a payment so also get the bank account $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_id)." @@ -183,6 +194,9 @@ function get_customer_trans($trans_id, $trans_type) AND branch.branch_code = trans.branch_code AND branch.tax_group_id = ".TB_PREF."tax_groups.id "; } + if ($trans_type == ST_JOURNAL) { + $sql .= " AND branch.branch_code = trans.branch_code "; + } $result = db_query($sql, "Cannot retreive a debtor transaction"); diff --git a/sales/includes/db/sales_invoice_db.inc b/sales/includes/db/sales_invoice_db.inc index 73338a41..d2b97cf2 100644 --- a/sales/includes/db/sales_invoice_db.inc +++ b/sales/includes/db/sales_invoice_db.inc @@ -199,8 +199,8 @@ function write_sales_invoice(&$invoice) _('Cash invoice').' '.$invoice_no); add_cust_allocation($amount, ST_CUSTPAYMENT, $pmtno, ST_SALESINVOICE, $invoice_no, $invoice->customer_id, $date_); - update_debtor_trans_allocation(ST_SALESINVOICE, $invoice_no); - update_debtor_trans_allocation(ST_CUSTPAYMENT, $pmtno); + update_debtor_trans_allocation(ST_SALESINVOICE, $invoice_no, $invoice->customer_id); + update_debtor_trans_allocation(ST_CUSTPAYMENT, $pmtno, $invoice->customer_id); } } //_vd($allocs); diff --git a/sales/inquiry/customer_allocation_inquiry.php b/sales/inquiry/customer_allocation_inquiry.php index 6d01aebc..ab75d237 100644 --- a/sales/inquiry/customer_allocation_inquiry.php +++ b/sales/inquiry/customer_allocation_inquiry.php @@ -101,8 +101,10 @@ function alloc_link($row) { /*its a credit note which could have an allocation */ return $link; - } - elseif (($row["type"] == ST_CUSTPAYMENT || $row["type"] == ST_BANKDEPOSIT) && + } elseif ($row["type"] == ST_JOURNAL && $row['TotalAmount'] < 0) + { + return $link; + } elseif (($row["type"] == ST_CUSTPAYMENT || $row["type"] == ST_BANKDEPOSIT) && (floatcmp($row['TotalAmount'], $row['Allocated']) >= 0)) { /*its a receipt which could have an allocation*/ @@ -112,7 +114,7 @@ function alloc_link($row) { /*its a negative receipt */ return ''; - } elseif ($row["type"] == ST_SALESINVOICE && ($row['TotalAmount'] - $row['Allocated']) > 0) + } elseif (($row["type"] == ST_SALESINVOICE && ($row['TotalAmount'] - $row['Allocated']) > 0) || $row["type"] == ST_BANKPAYMENT) return pager_link(_("Payment"), "/sales/customer_payments.php?customer_id=".$row["debtor_no"]."&SInvoice=" . $row["trans_no"], ICON_MONEY); diff --git a/sales/inquiry/customer_inquiry.php b/sales/inquiry/customer_inquiry.php index 14457aa9..b90eacec 100644 --- a/sales/inquiry/customer_inquiry.php +++ b/sales/inquiry/customer_inquiry.php @@ -138,7 +138,7 @@ function gl_view($row) function fmt_debit($row) { $value = - $row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT ? + $row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT || $row['type']==ST_JOURNAL ? -$row["TotalAmount"] : $row["TotalAmount"]; return $value>=0 ? price_format($value) : ''; @@ -147,11 +147,12 @@ function fmt_debit($row) function fmt_credit($row) { $value = - !($row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT) ? + !($row['type']==ST_CUSTCREDIT || $row['type']==ST_CUSTPAYMENT || $row['type']==ST_BANKDEPOSIT || $row['type']==ST_JOURNAL) ? -$row["TotalAmount"] : $row["TotalAmount"]; return $value>0 ? price_format($value) : ''; } + function credit_link($row) { global $page_nested; -- 2.30.2