From 1d3cf13fe8d61a0e5703e57e2ca640c1e39adb53 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 10 May 2009 18:47:21 +0000 Subject: [PATCH] Support for periodic journal trans closing/indexing. --- admin/fiscalyears.php | 26 ++++++-- gl/includes/db/gl_db_trans.inc | 106 ++++++++++++++++++++++++++++++++- gl/inquiry/journal_inquiry.php | 26 +++++--- includes/db/audit_trail_db.inc | 84 +++++++++++++++++++++++++- 4 files changed, 224 insertions(+), 18 deletions(-) diff --git a/admin/fiscalyears.php b/admin/fiscalyears.php index e5e9c2f7..e5cc244b 100644 --- a/admin/fiscalyears.php +++ b/admin/fiscalyears.php @@ -68,12 +68,13 @@ function close_year($year) $row = db_fetch_row($result); $balance = round2($row[0], user_price_dec()); + + begin_transaction(); + $to = sql2date($to); + if ($balance != 0.0) { $co = get_company_prefs(); - $to = sql2date($to); - - begin_transaction(); $trans_type = systypes::journal_entry(); $trans_id = get_next_trans_no($trans_type); @@ -83,10 +84,21 @@ function close_year($year) add_gl_trans($trans_type, $trans_id, $to, $co['profit_loss_year_act'], 0, 0, _("Closing Year"), $balance); - commit_transaction(); } + close_transactions($to); + commit_transaction(); +} + +function open_year($year) +{ + $myrow = get_fiscalyear($year); + $from = sql2date($myrow['begin']); + + begin_transaction(); + open_transactions($from); + commit_transaction(); } - + function handle_submit() { global $selected_id, $Mode; @@ -94,7 +106,9 @@ function handle_submit() if ($selected_id != -1) { if ($_POST['closed'] == 1) - close_year($selected_id); + close_year($selected_id); + else + open_year($selected_id); update_fiscalyear($selected_id, $_POST['closed']); display_notification(_('Selected fiscal year has been updated')); } diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index b077e5d5..7091ffb7 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -308,10 +308,81 @@ function get_tax_summary($from, $to) return db_query($sql,"Cannot retrieve tax summary"); } +//-------------------------------------------------------------------------------- +function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) +{ + begin_transaction(); + + $trans_type = systypes::journal_entry(); + $trans_id = get_next_trans_no($trans_type); + + foreach ($items as $journal_item) + { + // 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); + if ($is_bank_to) + { + add_bank_trans($trans_type, $trans_id, $is_bank_to, $ref, + $date_, $journal_item->amount, + 0, "", get_company_currency(), + "Cannot insert a destination bank transaction"); + } + // store tax details if the gl account is a tax account + add_gl_tax_details($journal_item->code_id, + $trans_type, $trans_id, -$journal_item->amount, 1, $date_, $memo_); + } + + add_comments($trans_type, $trans_id, $date_, $memo_); + + references::save($trans_type, $trans_id, $ref); + add_audit_trail($trans_type, $trans_id, $date_); + + if ($reverse) + { + + //$reversingDate = date(user_date_display(), + // Mktime(0,0,0,get_month($date_)+1,1,get_year($date_))); + $reversingDate = begin_month(add_months($date_, 1)); + + $trans_id_reverse = get_next_trans_no($trans_type); + + foreach ($items as $journal_item) + { + $is_bank_to = is_bank_account($journal_item->code_id); + + 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); + if ($is_bank_to) + { + add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref, + $reversingDate, $journal_item->amount, + 0, "", get_company_currency(), + "Cannot insert a destination bank transaction"); + } + // store tax details if the gl account is a tax account + add_gl_tax_details($journal_item->code_id, + $trans_type, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_); + } + + add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); + + references::save($trans_type, $trans_id_reverse, $ref); + add_audit_trail($trans_type, $trans_id_reverse, $reversingDate); + } + + commit_transaction(); + + return $trans_id; +} //-------------------------------------------------------------------------------- // Write/update journal entries. // -function write_journal_entries(&$cart) +function write_journal_entries(&$cart, $reverse) { $date_ = $cart->tran_date; $ref = $cart->reference; @@ -358,6 +429,39 @@ function write_journal_entries(&$cart) commit_transaction(); + if ($reverse) + { + //$reversingDate = date(user_date_display(), + // Mktime(0,0,0,get_month($date_)+1,1,get_year($date_))); + $reversingDate = begin_month(add_months($date_, 1)); + + $trans_id_reverse = get_next_trans_no($trans_type); + + foreach ($cart->gl_items as $journal_item) + { + $is_bank_to = is_bank_account($journal_item->code_id); + + 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); + if ($is_bank_to) + { + add_bank_trans($trans_type, $trans_id_reverse, $is_bank_to, $ref, + $reversingDate, $journal_item->amount, + 0, "", get_company_currency(), + "Cannot insert a destination bank transaction"); + } + // store tax details if the gl account is a tax account + add_gl_tax_details($journal_item->code_id, + $trans_type, $trans_id, $journal_item->amount, 1, $reversingDate, $memo_); + } + + add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_); + + references::save($trans_type, $trans_id_reverse, $ref); + add_audit_trail($trans_type, $trans_id_reverse, $reversingDate); + } + return $trans_id; } diff --git a/gl/inquiry/journal_inquiry.php b/gl/inquiry/journal_inquiry.php index a0ad749c..31037434 100644 --- a/gl/inquiry/journal_inquiry.php +++ b/gl/inquiry/journal_inquiry.php @@ -44,10 +44,12 @@ start_row(); ref_cells(_("Reference:"), 'Ref', '',null, _('Enter reference fragment or leave empty')); -journal_types_list_cells(_("Transaction Type:"), "filterType"); +journal_types_list_cells(_("Type:"), "filterType"); date_cells(_("From:"), 'FromDate', '', null, 0, 0, -1); date_cells(_("To:"), 'ToDate'); +check_cells( _("Show closed:"), 'AlsoClosed', null); + submit_cells('Search', _("Search"), '', '', 'default'); end_row(); @@ -55,6 +57,11 @@ end_table(); end_form(); +function journal_pos($row) +{ + return $row['gl_seq'] ? $row['gl_seq'] : '-'; +} + function systype_name($dummy, $type) { return systypes::name($type); @@ -70,11 +77,6 @@ function gl_link($row) return get_gl_view_str($row["type"], $row["type_no"]); } -function is_closed($row) -{ - return $row['gl_seq'] ? _('Yes') : _('No'); -} - $editors = array( 0 => "/gl/gl_journal.php?ModifyGL=Yes&trans_no=%d&trans_type=%d", // 1=> Bank Payment, @@ -109,20 +111,23 @@ function edit_link($row) ICON_EDIT) : ''; } -$sql = "SELECT gl.tran_date, +$sql = "SELECT a.gl_seq, + gl.tran_date, gl.type, gl.type_no, refs.reference, SUM(IF(gl.amount>0, gl.amount,0)) as amount," ."com.memo_" ." 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 (gl.type=com.type AND gl.type_no=com.id)" ." LEFT JOIN ". TB_PREF."refs as refs ON (gl.type=refs.type AND gl.type_no=refs.id) WHERE gl.tran_date >= '" . date2sql($_POST['FromDate']) . "' AND gl.tran_date <= '" . date2sql($_POST['ToDate']) . "' - AND gl.amount!=0"; + AND gl.amount!=0 AND !ISNULL(a.gl_seq)"; if (isset($_POST['Ref']) && $_POST['Ref'] != "") { $sql .= " AND reference LIKE '%". $_POST['Ref'] . "%'"; @@ -134,6 +139,7 @@ if (get_post('filterType') != -1) { $sql .= " GROUP BY gl.type, gl.type_no"; $cols = array( + _("#") => array('fun'=>'journal_pos', 'align'=>'center'), _("Date") =>array('name'=>'tran_date','type'=>'date','ord'=>'desc'), _("Type") => array('fun'=>'systype_name'), _("Trans #") => array('fun'=>'view_link'), @@ -144,6 +150,10 @@ $cols = array( array('insert'=>true, 'fun'=>'edit_link') ); +if (!check_value('AlsoClosed')) { + $cols[_("#")] = 'skip'; +} + $table =& new_db_pager('journal_tbl', $sql, $cols); if (get_post('Search')) { diff --git a/includes/db/audit_trail_db.inc b/includes/db/audit_trail_db.inc index 261193f8..6113bfd3 100644 --- a/includes/db/audit_trail_db.inc +++ b/includes/db/audit_trail_db.inc @@ -13,14 +13,21 @@ function add_audit_trail($trans_type, $trans_no, $trans_date, $descr='') { $sql = "INSERT INTO ".TB_PREF."audit_trail" - . " (type, trans_no, user, fiscal_year, gl_date, description) + . " (type, trans_no, user, fiscal_year, gl_date, description, gl_seq) VALUES($trans_type, $trans_no," . $_SESSION["wa_current_user"]->user. "," . get_company_pref('f_year') ."," . "'". date2sql($trans_date) ."'," - . db_escape($descr). ")"; + . db_escape($descr). ", 0)"; db_query($sql, "Cannot add audit info"); + + // all audit records beside latest one should have gl_seq set to NULL + // to avoid need for subqueries (not existing in MySQL 3) all over the code + $sql = "UPDATE ".TB_PREF."audit_trail SET gl_seq = NULL" + . " WHERE type=$trans_type AND trans_no=$trans_no AND id!=".db_insert_id(); + + db_query($sql, "Cannot update audit gl_seq"); } function get_audit_trail_all($trans_type, $trans_no) @@ -34,7 +41,7 @@ function get_audit_trail_all($trans_type, $trans_no) function get_audit_trail_last($trans_type, $trans_no) { $sql = "SELECT * FROM ".TB_PREF."audit_trail" - ." WHERE type=$trans_type AND trans_no=$trans_no ORDER BY id desc"; + ." WHERE type=$trans_type AND trans_no=$trans_no AND NOT ISNULL(gl_seq)"; $res = db_query($sql, "Cannot get last audit info for transaction"); if ($res) @@ -43,4 +50,75 @@ function get_audit_trail_last($trans_type, $trans_no) return $row; } +/* + Confirm and close for edition all transactions up to date $todate, + and reindex journal. +*/ +function close_transactions($todate) { + $errors = 0; + $sql = "SELECT DISTINCT a.id, a.gl_date, a.fiscal_year" + ." FROM ".TB_PREF."gl_trans gl" + ." LEFT JOIN ". TB_PREF."audit_trail a ON + (gl.type=a.type AND gl.type_no=a.trans_no)" + . " WHERE gl_date<='". date2sql($todate) ."'" + . " AND NOT ISNULL(gl_seq)" + . " ORDER BY a.fiscal_year, a.gl_date, a.id"; + + $result = db_query($sql, "Cannot select transactions for closing"); + + if (db_num_rows($result)) { + $last_year = 0; + + while ($row = db_fetch($result)) { + if ($row['fiscal_year'] == null) { + $errors = 1; continue; + } + if ($last_year != $row['fiscal_year']) { + $last_year = $row['fiscal_year']; + $counter = 1; // reset counter on fiscal year change + } else + $counter++; + $sql2 = "UPDATE ".TB_PREF."audit_trail SET" + . " gl_seq=$counter" + . " WHERE id=".$row['id']; + + db_query($sql2, "Cannot reindex journal"); + } + } + + if ($errors) + display_warning(_("Some transactions journal GL postings were not indexed due to lack of audit trail record.")); +} + +/* + Reopen all transactions for edition up from date $fromdate +*/ +function open_transactions($fromdate) { + + $sql = "SELECT a.id, a.gl_date, a.fiscal_year" + ." FROM ".TB_PREF."gl_trans gl" + ." LEFT JOIN ". TB_PREF."audit_trail a ON + (gl.type=a.type AND gl.type_no=a.trans_no)" + . " WHERE gl_date>='". date2sql($fromdate) ."'" + . " AND !ISNULL(gl_seq)" + . " ORDER BY a.fiscal_year, a.gl_date, a.id"; + + $result = db_query($sql, "Cannot select transactions for openning"); + + if (db_num_rows($result)) { + $last_year = 0; + + while ($row = db_fetch($result)) { + if ($row['fiscal_year'] == null) { + continue; + } + $sql2 = "UPDATE ".TB_PREF."audit_trail SET" + . " gl_seq=0" + . " WHERE id=".$row['id']; + + db_query($sql2, "Cannot clear journal order"); + } + } +} + ?> -- 2.30.2