From 70917210d2437b36ae8faf21418f007b11819b7d Mon Sep 17 00:00:00 2001 From: einhverfr Date: Tue, 14 Oct 2008 18:35:10 +0000 Subject: Performance tuning for main trial balance. git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2369 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/RP.pm | 139 ++++++++++++++++++++++++++++++++++++++++---------------- 1 file changed, 100 insertions(+), 39 deletions(-) (limited to 'LedgerSMB') diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm index 22af9bb1..dfff734f 100644 --- a/LedgerSMB/RP.pm +++ b/LedgerSMB/RP.pm @@ -1318,7 +1318,7 @@ sub trial_balance { my ( $self, $myconfig, $form ) = @_; my $dbh = $form->{dbh}; - my $approved = ($form->{approved})? 'TRUE' : 'FALSE'; + my $approved = 'FALSE'; my ( $query, $sth, $ref ); my %balance = (); @@ -1343,7 +1343,6 @@ sub trial_balance { $dpt_where = qq| AND t.department_id = | . $dbh->quote($department_id); } - if ($project_id) { $project = qq| AND ac.project_id = | . $dbh->quote($project_id); @@ -1354,8 +1353,7 @@ sub trial_balance { if $form->{year} && $form->{month}; # get beginning balances - if ( $form->{fromdate} ) { - + if ( ($department_id or $form->{accounttype} eq 'gifi') and $form->{fromdate}) { if ( $form->{accounttype} eq 'gifi' ) { $query = qq| @@ -1443,6 +1441,7 @@ sub trial_balance { $trb{ $ref->{accno} }{description} = $ref->{description}; $trb{ $ref->{accno} }{charttype} = 'H'; $trb{ $ref->{accno} }{category} = $ref->{category}; + $trb{ $ref->{accno} }{accno} = $ref->{accno}; $trb{ $ref->{accno} }{contra} = $ref->{contra}; push @headingaccounts, $ref->{accno}; @@ -1450,23 +1449,85 @@ sub trial_balance { $sth->finish; - if ( $form->{fromdate} || $form->{todate} ) { - if ( $form->{fromdate} ) { - $where .= - " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} ); - $invwhere .= - " AND a.transdate >= " . $dbh->quote( $form->{fromdate} ); + if (!$department_id and !$form->{gifi}){ + my $datefrom = $dbh->quote($form->{fromdate}); + my $dateto = $dbh->quote($form->{todate}); + my $safe_project_id = $dbh->quote($project_id); + $query = "SELECT c.id AS chart_id, c.accno, c.description, c.contra, + c.category, + SUM(CASE WHEN ac.transdate < $datefrom + THEN ac.amount + ELSE 0 END) AS balance, + SUM(CASE WHEN ac.transdate >= + coalesce($datefrom, ac.transdate) + AND ac.amount > 0 + THEN ac.amount + ELSE 0 END) AS credit, + SUM(CASE WHEN ac.transdate >= + coalesce($datefrom, ac.transdate) + AND ac.amount < 0 + THEN ac.amount + ELSE 0 END) * -1 AS debit, + SUM(CASE WHEN ac.transdate >= + coalesce($datefrom, ac.transdate) + THEN ac.amount + ELSE 0 + END) as amount + FROM acc_trans ac + JOIN (select id, approved FROM ap + UNION ALL + select id, approved FROM gl + UNION ALL + select id, approved FROM ar) g + ON (g.id = ac.trans_id) + JOIN chart c ON (c.id = ac.chart_id) + WHERE ac.transdate <= $dateto OR $dateto IS NULL + AND ac.approved AND g.approved + AND ($safe_project_id IS NULL + OR $safe_project_id = ac.project_id) + GROUP BY c.id, c.accno, c.description, c.contra, + c.category + ORDER BY c.accno"; + my $sth = $dbh->prepare($query); + $sth->execute(); + while ($ref = $sth->fetchrow_hashref('NAME_lc')){ + $form->db_parse_numeric(sth=>$sth, hashref=>$ref); + $ref->{debit} = $form->round_amount($ref->{debit}, 2); + $ref->{credit} = $form->round_amount($ref->{credit}, 2); + $ref->{balance} = $form->round_amount($ref->{balance}, 2); + $trb{ $ref->{accno} }{accno} = $ref->{accno}; + $trb{ $ref->{accno} }{description} = $ref->{description}; + $trb{ $ref->{accno} }{charttype} = 'A'; + $trb{ $ref->{accno} }{amount} = $ref->{amount}; + $trb{ $ref->{accno} }{debit} = $ref->{debit}; + $trb{ $ref->{accno} }{credit} = $ref->{credit}; + $trb{ $ref->{accno} }{category} = $ref->{category}; + $trb{ $ref->{accno} }{contra} = $ref->{contra}; + $trb{ $ref->{accno} }{balance} = $ref->{balance}; } - if ( $form->{todate} ) { - $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} ); - $invwhere .= - " AND a.transdate <= " . $dbh->quote( $form->{todate} ); + $form->{TB} = []; + foreach my $accno ( sort keys %trb ) { + push @{$form->{TB}}, $trb{$accno}; + } + return; + } else { + if ( $form->{fromdate} || $form->{todate} ) { + if ( $form->{fromdate} ) { + $where .= + " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} ); + $invwhere .= + " AND a.transdate >= " . $dbh->quote( $form->{fromdate} ); + } + if ( $form->{todate} ) { + $where .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} ); + $invwhere .= + " AND a.transdate <= " . $dbh->quote( $form->{todate} ); + } } - } - if ( $form->{accounttype} eq 'gifi' ) { + if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| + $query = qq| SELECT g.accno, g.description, c.category, SUM(ac.amount) AS amount, c.contra FROM acc_trans ac @@ -1483,10 +1544,10 @@ sub trial_balance { GROUP BY g.accno, g.description, c.category, c.contra ORDER BY accno|; - } - else { + } + else { - $query = qq| + $query = qq| SELECT c.accno, c.description, c.category, SUM(ac.amount) AS amount, c.contra FROM acc_trans ac @@ -1502,13 +1563,13 @@ sub trial_balance { GROUP BY c.accno, c.description, c.category, c.contra ORDER BY accno|; - } + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - # prepare query for each account - $query = qq| + # prepare query for each account + $query = qq| SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) $dpt_join @@ -1532,9 +1593,9 @@ sub trial_balance { AND ($approved OR gl.approved) AND c.accno = ?) AS credit |; - if ( $form->{accounttype} eq 'gifi' ) { + if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| + $query = qq| SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) @@ -1551,21 +1612,21 @@ sub trial_balance { AND ($approved OR ac.approved) AND c.gifi_accno = ?) AS credit|; - } + } - $drcr = $dbh->prepare($query); + $drcr = $dbh->prepare($query); - # calculate debit and credit for the period - while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { - $form->db_parse_numeric(sth=>$sth, hashref=>$ref); - $trb{ $ref->{accno} }{description} = $ref->{description}; - $trb{ $ref->{accno} }{charttype} = 'A'; - $trb{ $ref->{accno} }{category} = $ref->{category}; - $trb{ $ref->{accno} }{contra} = $ref->{contra}; - $trb{ $ref->{accno} }{amount} += $ref->{amount}; + # calculate debit and credit for the period + while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) { + $form->db_parse_numeric(sth=>$sth, hashref=>$ref); + $trb{ $ref->{accno} }{description} = $ref->{description}; + $trb{ $ref->{accno} }{charttype} = 'A'; + $trb{ $ref->{accno} }{category} = $ref->{category}; + $trb{ $ref->{accno} }{contra} = $ref->{contra}; + $trb{ $ref->{accno} }{amount} += $ref->{amount}; + } + $sth->finish; } - $sth->finish; - my ( $debit, $credit ); foreach my $accno ( sort keys %trb ) { -- cgit v1.2.3