1 Topic by apmuthu (edited by apmuthu 07/16/2016 01:22:47 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Topic: Closing and Opening Fiscal Years - Speedup

The file includes/db/audit_trail_db.inc has the functions close_transactions() and open_transactions() and in both, only the audit_trail.gl_seq field is updated and the gl_trans.gl_date field is unnecessarily being called in for ordering whereas the audit_trail.gl_date already has the info taken from the former at the time of transaction creation.

In the function open_transactions() there is no need for any ordering as a mere set to 0 of the gl_seq field is all that is done wholesale.

In the close_transactions(), the LEFT JOIN should be an INNER JOIN as there is no point in attempting to update records in audit_trail table where no id exists if it only existed in the gl_trans table!

A pure MySQL implementation of the close_transactions sql would reduce to:

SET @lastyr:=0;
SET @counter:=1;
CREATE TEMPORARY TABLE audit_tmp SELECT id, Counter AS gl_seq FROM (
SELECT id, gl_date, fiscal_year
    , (@counter:=IF(fiscal_year <> @lastyr, 1, @counter+1)) AS Counter
    , (@lastyr:=IF(fiscal_year <> @lastyr, fiscal_year, @lastyr)) AS LastYr FROM
( SELECT DISTINCT a.id, a.gl_date, a.fiscal_year 
FROM gl_trans gl INNER JOIN audit_trail a ON (gl.type=a.type AND gl.type_no=a.trans_no) 
WHERE gl_date<='2015-12-31' AND NOT ISNULL(gl_seq) 
ORDER BY a.fiscal_year, a.gl_date, a.id) b
) c;
UPDATE audit_tmp a LEFT JOIN audit_trail b USING (id) SET b.gl_seq=a.gl_seq;
DROP TABLE audit_tmp;

Likewise, the open_transactions sql would reduce to:

UPDATE audit_trail SET gl_seq=0 WHERE gl_date>='2016-01-01' AND !ISNULL(gl_seq);

Failure to correct this will cause a timeout when trying to close large fiscal years.

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Closing and Opening Fiscal Years - Speedup

Even if we mimic the mysqli_multi_query in the mysql driver used in FA 2.3.x, it is still very slow when the equivalent function below is used:

function multiQuery($queryBlock, $delimiter = ';') {
    $inString = false;
    $escChar = false;
    $sql = '';
    $stringChar = '';
    $queryLine = array();
    $sqlRows = explode ( "\n", $queryBlock );
    $delimiterLen = strlen ( $delimiter );
    do {
        $sqlRow = current ( $sqlRows ) . "\n";
        $sqlRowLen = strlen ( $sqlRow );
        for ( $i = 0; $i < $sqlRowLen; $i   ) {
            if ( ( substr ( ltrim ( $sqlRow ), $i, 2 ) === '--' || substr ( ltrim ( $sqlRow ), $i, 1 ) === '#' ) && !$inString ) {
                break;
            }
            $znak = substr ( $sqlRow, $i, 1 );
            if ( $znak === '\'' || $znak === '"' ) {
                if ( $inString ) {
                    if ( !$escChar && $znak === $stringChar ) {
                        $inString = false;
                    }
                }
                else {
                    $stringChar = $znak;
                    $inString = true;
                }
            }
            if ( $znak === '\\' && substr ( $sqlRow, $i - 1, 2 ) !== '\\\\' ) {
                $escChar = !$escChar;
            }
            else {
                $escChar = false;
            }
            if ( substr ( $sqlRow, $i, $delimiterLen ) === $delimiter ) {
                if ( !$inString ) {
                    $sql = trim ( $sql );
                    $delimiterMatch = array();
                    if ( preg_match ( '/^DELIMITER[[:space:]]*([^[:space:]] )$/i', $sql, $delimiterMatch ) ) {
                        $delimiter = $delimiterMatch [1];
                        $delimiterLen = strlen ( $delimiter );
                    }
                    else {
                        $queryLine [] = $sql;
                    }
                    $sql = '';
                    continue;
                }
            }
            $sql .= $znak;
        }
    } while ( next( $sqlRows ) !== false );
 
    return $queryLine;
}

It's usage will be:

$multiple_queries = 'SELECT CURRENT_USER();
SELECT Name FROM City ORDER BY ID LIMIT 20, 5;';
 
$sql_queries = multiQuery($multiple_queries);
 
foreach($sql_queries as $sql_query) {
    // Execute each query
    mysql_query($sql_query) or exit('SQL: '.$sql_query.', Error: '.mysql_error());
}

Tutorial on MySQLi multi query function

apmuthu's Website

3 Reply by apmuthu (edited by apmuthu 07/17/2016 04:39:12 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Closing and Opening Fiscal Years - Speedup

For a start, if you have a large amount of transactions in each year you wish to close/open,  the function open_transactions can be replaced with:

function open_transactions($fromdate) {

    $sql = "UPDATE ".TB_PREF."audit_trail SET gl_seq=0 WHERE gl_date>='". date2sql($fromdate) ."' AND !ISNULL(gl_seq) AND !ISNULL(fiscal_year)";
    db_query($sql, "Cannot open transactions");

}

The above has been committed in my GitHub Repo - 1, 2.

The function close_transactions called in function close_year in admin/db/fiscalyears_db.inc be commented out and it's functionality be manually executed from the sql obtained from:

$sql = "
DROP TABLE IF EXISTS ".TB_PREF."audit_tmp;
CREATE TEMPORARY TABLE ".TB_PREF."audit_tmp SELECT id, Counter AS gl_seq FROM (
SELECT id, gl_date, fiscal_year
    , (@counter:=IF(fiscal_year <> @lastyr, 1, @counter+1)) AS Counter
    , (@lastyr:=IF(fiscal_year <> @lastyr, fiscal_year, @lastyr)) AS LastYr FROM
( SELECT DISTINCT a.id, a.gl_date, a.fiscal_year 
FROM ".TB_PREF."gl_trans gl INNER 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) b, (SELECT (@lastyr:=0), (@counter:=1)) w
) c;
UPDATE ".TB_PREF."audit_tmp a LEFT JOIN ".TB_PREF."audit_trail b USING (id) SET b.gl_seq=a.gl_seq;
DROP TABLE ".TB_PREF."audit_tmp;
";

We also need to see if there is any performance gain by indexing gl_seq in the audit_trail table.

Only closed fiscal years will have the audit_table.gl_seq chronologically sequentially numbered (some will be NULL and some others 0 as well). All open fiscal years will have the said field as 0 or NULL only.

At the end of successful closure of a fiscal year, on it's last date as the last 2 gl_trans.counter entries we get the memo_ field set to Closing Year with the amount values in contra. When the fiscal year is then re-opened, these entries still remain and will need to be manually deleted to be re-created when it is subsequently closed.

The Audit Trail ERD is available in the Wiki.

apmuthu's Website

4 Reply by apmuthu (edited by apmuthu 07/19/2016 03:08:32 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Closing and Opening Fiscal Years - Speedup

The final version of functions close_transactions() and open_transactions in the file includes/db/audit_trail_db.inc that works even for large datasets has been committed to my GitHub repo. It is now an entirely MySQL affair and no updation by cycling through redundant id instances of the audit_trail table and is as follows:

/*
    Confirm and close for edition all transactions up to date $todate, 
    and reindex    journal.
*/
function close_transactions($todate) {
    $errors = 0;

    begin_transaction();

    $sql = "DROP TABLE IF EXISTS ".TB_PREF."audit_tmp";
    db_query($sql, "Cannot drop hold table if present");

    $sql2 = "CREATE TABLE ".TB_PREF."audit_tmp SELECT id, Counter AS gl_seq FROM 
        ( SELECT id, gl_date, fiscal_year
            , (@counter:=IF(fiscal_year <> @lastyr, 1, @counter+1)) AS Counter
            , (@lastyr:=IF(fiscal_year <> @lastyr, fiscal_year, @lastyr)) AS LastYr FROM
            ( SELECT DISTINCT a.id, a.gl_date, a.fiscal_year 
                FROM ".TB_PREF."gl_trans gl INNER 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) b, (SELECT (@lastyr:=0), (@counter:=1)
            ) w
        ) c";
    db_query($sql2, "Cannot create hold table for gl_seq sequential values");

    $sql3 = "UPDATE ".TB_PREF."audit_tmp a LEFT JOIN ".TB_PREF."audit_trail b USING (id) 
            SET b.gl_seq=a.gl_seq";
    $result = db_query($sql3, "Cannot update audit_trail.gl_seq from hold table");

    db_query($sql, "Cannot drop hold table");

    commit_transaction();
}

/*
    Reopen all transactions for edition up from date $fromdate
*/
function open_transactions($fromdate) {

    $sql = "UPDATE ".TB_PREF."audit_trail SET gl_seq=0 
        WHERE gl_date>='". date2sql($fromdate) ."' 
        AND !ISNULL(gl_seq) AND !ISNULL(fiscal_year)";

    $result = db_query($sql, "Cannot open transactions");
}

The Audit Trail relationships section of Wiki has been updated.

An example of the Opening / Closing entries in the gl_trans table is attached. The sql to extract it is:

$sql = "SELECT * FROM ".TB_PREF".gl_trans WHERE `type`=0 AND memo_ IN ("._('Open Balance').", "._('Closing Year').")";

or in plain sql with no translation and no table prefix:

SELECT * FROM gl_trans WHERE `type`=0 AND memo_ IN ('Open Balance', 'Closing Year');
Post's attachments

ClosingBals_FA.png 25 kb, 1 downloads since 2016-07-19 

You don't have the permssions to download the attachments of this post.

apmuthu's Website