$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);
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;
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'));
}
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;
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;
}
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();
end_form();
+function journal_pos($row)
+{
+ return $row['gl_seq'] ? $row['gl_seq'] : '-';
+}
+
function systype_name($dummy, $type)
{
return systypes::name($type);
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,
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'] . "%'";
$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'),
array('insert'=>true, 'fun'=>'edit_link')
);
+if (!check_value('AlsoClosed')) {
+ $cols[_("#")] = 'skip';
+}
+
$table =& new_db_pager('journal_tbl', $sql, $cols);
if (get_post('Search')) {
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)
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)
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");
+ }
+ }
+}
+
?>
projects / fa-stable.git / commitdiff