From c440a20c234d4fa586ae30edd77377d08238eb6d Mon Sep 17 00:00:00 2001 From: einhverfr Date: Mon, 20 Oct 2008 01:16:29 +0000 Subject: Removing ca.pl's list_transactions and CA.pm's all_transactions git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2375 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/CA.pm | 466 -------------------------------------------------------- LedgerSMB/GL.pm | 8 +- bin/ca.pl | 238 ----------------------------- 3 files changed, 5 insertions(+), 707 deletions(-) diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm index 169e6827..bd61e113 100644 --- a/LedgerSMB/CA.pm +++ b/LedgerSMB/CA.pm @@ -114,470 +114,4 @@ sub all_accounts { } -sub all_transactions { - - my ( $self, $myconfig, $form ) = @_; - - # connect to database - my $dbh = $form->{dbh}; - my $approved = ($form->{approved})? 'TRUE' : 'FALSE'; - - # get chart_id - my $query = qq| - SELECT id - FROM chart - WHERE accno = ?|; - - my $accno = $form->{accno}; - - if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| - SELECT id - FROM chart - WHERE gifi_accno = ?|; - $accno = $form->{gifi_accno}; - } - - my $sth = $dbh->prepare($query); - $sth->execute($accno) || $form->dberror($query); - - my @id = (); - - while ( my ($id) = $sth->fetchrow_array ) { - push @id, $id; - } - - $sth->finish; - - my $fromdate_where; - my $todate_where; - ( $form->{fromdate}, $form->{todate} ) = - $form->from_to( $form->{year}, $form->{month}, $form->{interval} ) - if !$form->{fromdate} && !$form->{todate}; - - my $fdate; - if ( $form->{fromdate} ) { - $fromdate_where = qq| AND ac.transdate >= ? |; - $fdate = $form->{fromdate}; - } - my $tdate; - if ( $form->{todate} ) { - $todate_where .= qq| AND ac.transdate <= ? |; - $tdate = $form->{todate}; - } - - my $false = 'FALSE'; - - # Oracle workaround, use ordinal positions - my %ordinal = ( - transdate => 4, - reference => 2, - description => 3 - ); - - my @a = qw(transdate reference description); - my $sortorder = $form->sort_order( \@a, \%ordinal ); - - my $null; - my $department_id; - my $dpt_where; - my $dpt_join; - my $union; - - ( $null, $department_id ) = split /--/, $form->{department}; - - my $d_id; - if ($department_id) { - $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |; - $dpt_where = qq| AND t.id = ? |; - $d_id = $department_id; - } - - my $project; - my $project_id; - my $p_id; - if ( $form->{projectnumber} ) { - ( $null, $project_id ) = split /--/, $form->{projectnumber}; - $project = qq| AND ac.project_id = ? |; - $p_id = $project_id; - } - - @queryargs = (); - - if ( $form->{accno} || $form->{gifi_accno} ) { - - # get category for account - $query = qq| - SELECT description, category, link, contra - FROM chart - WHERE accno = ?|; - - $accno = $form->{accno}; - if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| - SELECT description, category, link, contra - FROM chart - WHERE gifi_accno = ? - AND charttype = 'A'|; - $accno = $form->{gifi_accno}; - } - - $sth = $dbh->prepare($query); - $sth->execute($accno); - ( - $form->{description}, $form->{category}, - $form->{link}, $form->{contra} - ) = $sth->fetchrow_array; - - if ( $form->{fromdate} ) { - - if ($department_id) { - - # get beginning balance - $query = ""; - $union = ""; - - for (qw(ar ap gl)) { - - if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| - $union - SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN $_ a - ON - (a.id = - ac.trans_id) - JOIN chart c - ON - (ac.chart_id = - c.id) - WHERE c.gifi_accno = ? - AND ac.transdate - < ? - AND - a.department_id - = ? - AND ($approved OR - (a.approved - AND ac.approved)) - $project |; - - push @queryargs, $form->{gifi_accno}, $form->{fromdate}, - $form->{department_id}; - if ($p_id) { - push @queryargs, $p_id; - } - } - else { - - $query .= qq| - $union - SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN $_ a ON - (a.id = - ac.trans_id) - JOIN chart c ON - (ac.chart_id = - c.id) - WHERE c.accno = ? - AND ac.transdate - < ? - AND - a.department_id - = ? - AND ($approved OR - (a.approved - AND ac.approved)) - $project |; - push @queryargs, $form->{accno}, $form->{fromdate}, - $department_id; - if ($p_id) { - push @queryargs, $p_id; - } - } - - $union = qq| UNION ALL |; - } - - } - else { - - if ( $form->{accounttype} eq 'gifi' ) { - $query = qq| - SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN chart c ON - (ac.chart_id = c.id) - JOIN transactions t ON - (t.id = ac.trans_id) - JOIN (SELECT id, approved - 'ar' AS tablename - FROM ar - UNION - SELECT id, approved, - 'ap' AS tablename - FROM ap - UNION - SELECT id, approved, - 'gl' AS tablename - FROM gl - ) a ON - (a.tablename = t.table_name - AND a.id = t.id) - WHERE c.gifi_accno = ? - AND ac.transdate < ? - AND ($approved OR - (a.approved - AND ac.approved)) - $project |; - @queryargs = ( $form->{gifi_accno}, $form->{fromdate} ); - if ($p_id) { - push @query_ags, $p_id; - } - } - else { - $query = qq| - SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN chart c - ON (ac.chart_id = c.id) - JOIN transactions t ON - (t.id = ac.trans_id) - JOIN (SELECT id, approved, - 'ar' AS tablename - FROM ar - UNION - SELECT id, approved, - 'ap' AS tablename - FROM ap - UNION - SELECT id, approved, - 'gl' AS tablename - FROM gl - ) a ON - (a.tablename = t.table_name - AND a.id = t.id) - WHERE c.accno = ? - AND ac.transdate < ? - AND ($approved OR - (a.approved - AND ac.approved)) - $project |; - @queryargs = ( $form->{accno}, $form->{fromdate} ); - if ($p_id) { - push @queryargs, $p_id; - } - } - } - $sth = $dbh->prepare($query); - $sth->execute(@queryargs); - my @balance = $sth->fetchrow_array; - $form->db_parse_numeric(sth=>$sth, arrayref=>\@balance); - ( $form->{balance} ) = @balance; - $sth->finish; - @queryargs = (); - } - } - - $query = ""; - $union = ""; - - foreach my $id (@id) { - - # get all transactions - $query .= qq| - $union - SELECT a.id, a.reference, a.description, ac.transdate, - $false AS invoice, ac.amount, 'gl' as module, - ac.cleared, ac.source, '' AS till, ac.chart_id - FROM gl a - JOIN acc_trans ac ON (ac.trans_id = a.id) - $dpt_join - WHERE ac.chart_id = ? - $fromdate_where - $todate_where - $dpt_where - AND ($approved OR (a.approved AND ac.approved)) - $project|; - push @queryargs, $id; - if ($fdate) { - push @queryargs, $fdate; - } - if ($tdate) { - push @queryargs, $tdate; - } - if ($d_id) { - push @queryargs, $d_id; - } - if ($p_id) { - push @queryargs, $p_id; - } - $query .= qq| - - UNION ALL - - SELECT a.id, a.invnumber, e.name, ac.transdate, - a.invoice, ac.amount, 'ar' as module, ac.cleared, - ac.source, a.till, ac.chart_id - FROM ar a - JOIN acc_trans ac ON (ac.trans_id = a.id) - JOIN entity_credit_account eca - ON (a.entity_credit_account = eca.id) - JOIN entity e ON (eca.entity_id = e.id) - $dpt_join - WHERE ac.chart_id = ? - AND ($approved OR (a.approved AND ac.approved)) - $fromdate_where - $todate_where - $dpt_where - $project|; - - push @queryargs, $id; - if ($fdate) { - push @queryargs, $fdate; - } - if ($tdate) { - push @queryargs, $tdate; - } - if ($d_id) { - push @queryargs, $d_id; - } - if ($p_id) { - push @queryargs, $p_id; - } - - $query .= qq| - UNION ALL - - SELECT a.id, a.invnumber, e.name, ac.transdate, - a.invoice, ac.amount, 'ap' as module, ac.cleared, - ac.source, a.till, ac.chart_id - FROM ap a - JOIN acc_trans ac ON (ac.trans_id = a.id) - JOIN entity_credit_account eca - ON (a.entity_credit_account = eca.id) - JOIN entity e ON (eca.entity_id = e.id) - $dpt_join - WHERE ac.chart_id = ? - AND ($approved OR (a.approved AND ac.approved)) - $fromdate_where - $todate_where - $dpt_where - $project |; - - push @queryargs, $id; - if ($fdate) { - push @queryargs, $fdate; - } - if ($tdate) { - push @queryargs, $tdate; - } - if ($d_id) { - push @queryargs, $d_id; - } - if ($p_id) { - push @queryargs, $p_id; - } - $union = qq| UNION ALL |; - } - - $query .= qq| ORDER BY $sortorder |; - $sth = $dbh->prepare($query); - $sth->execute(@queryargs) || $form->dberror($query); - - $query = qq|SELECT c.id, c.accno - FROM chart c - JOIN acc_trans ac ON (ac.chart_id = c.id) - WHERE ac.amount >= 0 - AND (c.link = 'AR' OR c.link = 'AP') - AND ac.approved OR $approved - AND ac.trans_id = ?|; - - my $dr = $dbh->prepare($query) || $form->dberror($query); - - $query = qq|SELECT c.id, c.accno - FROM chart c - JOIN acc_trans ac ON (ac.chart_id = c.id) - WHERE ac.amount < 0 - AND (c.link = 'AR' OR c.link = 'AP') - AND ac.approved OR $approved - AND ac.trans_id = ?|; - - my $cr = $dbh->prepare($query) || $form->dberror($query); - - my $accno; - my $chart_id; - my %accno; - - while ( my $ca = $sth->fetchrow_hashref(NAME_lc) ) { - $form->db_parse_numeric(sth=>$sth, hashref=>$ca); - # gl - if ( $ca->{module} eq "gl" ) { - $ca->{module} = "gl"; - } - - # ap - if ( $ca->{module} eq "ap" ) { - $ca->{module} = ( $ca->{invoice} ) ? 'ir' : 'ap'; - $ca->{module} = 'ps' if $ca->{till}; - } - - # ar - if ( $ca->{module} eq "ar" ) { - $ca->{module} = ( $ca->{invoice} ) ? 'is' : 'ar'; - $ca->{module} = 'ps' if $ca->{till}; - } - - if ( $ca->{amount} ) { - %accno = (); - - if ( $ca->{amount} < 0 ) { - $ca->{debit} = $ca->{amount} * -1; - $ca->{credit} = 0; - $dr->execute( $ca->{id} ); - $ca->{accno} = (); - - while ( my @dr_results = $dr->fetchrow_array ) { - $form->db_parse_numeric(sth=>$dr, arrayref=>\@dr_results); - ($chart_id, $accno) = @dr_results; - $accno{$accno} = 1 if $chart_id ne $ca->{chart_id}; - } - - $dr->finish; - - for ( sort keys %accno ) { push @{ $ca->{accno} }, "$_ " } - - } - else { - - $ca->{credit} = $ca->{amount}; - $ca->{debit} = 0; - - $cr->execute( $ca->{id} ); - $ca->{accno} = (); - - while ( my @cr_results = $cr->fetchrow_array ) { - $form->db_parse_numeric(sth=>$cr, arrayref=>\@cr_results); - ($chart_id, $accno) = @cr_results; - $accno{$accno} = 1 if $chart_id ne $ca->{chart_id}; - } - - $cr->finish; - - for ( keys %accno ) { push @{ $ca->{accno} }, "$_ " } - - } - - push @{ $form->{CA} }, $ca; - } - } - - $sth->finish; - $dbh->commit; - -} - 1; - diff --git a/LedgerSMB/GL.pm b/LedgerSMB/GL.pm index 63201e69..9d30d604 100644 --- a/LedgerSMB/GL.pm +++ b/LedgerSMB/GL.pm @@ -272,9 +272,11 @@ sub all_transactions { $apwhere .= " AND lower(ac.memo) LIKE $var"; } - ( $form->{datefrom}, $form->{dateto} ) = - $form->from_to( $form->{year}, $form->{month}, $form->{interval} ) - if $form->{year} && $form->{month}; + if (!form->{datefrom} && !$form->{dateto} + && form->{year} && $form->{month}){ + ( $form->{datefrom}, $form->{dateto} ) = + $form->from_to( $form->{year}, $form->{month}, $form->{interval} ); + } if ( $form->{datefrom} ) { $glwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} ); diff --git a/bin/ca.pl b/bin/ca.pl index 7b85c73f..fba81cab 100644 --- a/bin/ca.pl +++ b/bin/ca.pl @@ -260,244 +260,6 @@ sub list { }); } -sub list_transactions { - - CA->all_transactions( \%myconfig, \%$form ); - - $department = $form->escape( $form->{department} ); - $projectnumber = $form->escape( $form->{projectnumber} ); - $title = $form->escape( $form->{title} ); - - # construct href - $href = -"$form->{script}?action=list_transactions&department=$department&projectnumber=$projectnumber&title=$title"; - for ( - qw(path oldsort accno login sessionid fromdate todate accounttype gifi_accno l_heading l_subtotal l_accno) - ) - { - $href .= "&$_=$form->{$_}"; - } - - $drilldown = $href; - $drilldown .= "&sort=$form->{sort}"; - - $href .= "&direction=$form->{direction}"; - - $form->sort_order(); - - $drilldown .= "&direction=$form->{direction}"; - - $form->{prevreport} = $href unless $form->{prevreport}; - $href .= "&prevreport=" . $form->escape( $form->{prevreport} ); - $drilldown .= "&prevreport=" . $form->escape( $form->{prevreport} ); - - # figure out which column comes first - $column_header{transdate} = { - text => $locale->text('Date'), - href => "$href&sort=transdate"}; - $column_header{reference} = { - text => $locale->text('Reference'), - href => "$href&sort=reference"}; - $column_header{description} = { - text => $locale->text('Description'), - href => "$href&sort=description"}; - $column_header{cleared} = $locale->text('R'); - $column_header{source} = $locale->text('Source'); - $column_header{debit} = $locale->text('Debit'); - $column_header{credit} = $locale->text('Credit'); - $column_header{balance} = $locale->text('Balance'); - $column_header{accno} = $locale->text('AR/AP'); - - @columns = qw(transdate reference description debit credit); - if ( $form->{link} =~ /_paid/ ) { - @columns = - qw(transdate reference description source cleared debit credit); - } - push @columns, "accno" if $form->{l_accno}; - @column_index = $form->sort_columns(@columns); - - if ( $form->{accounttype} eq 'gifi' ) { - for (qw(accno description)) { $form->{$_} = $form->{"gifi_$_"} } - } - if ( $form->{accno} ) { - push @column_index, "balance"; - } - - $form->{title} = - ( $form->{accounttype} eq 'gifi' ) - ? $locale->text('GIFI') - : $locale->text('Account'); - - $form->{title} .= " $form->{accno} - $form->{description}"; - - my @options; - if ( $form->{department} ) { - ($department) = split /--/, $form->{department}; - push @options, $locale->text('Department') . " : $department"; - } - if ( $form->{projectnumber} ) { - ($projectnumber) = split /--/, $form->{projectnumber}; - push @options, $locale->text('Project Number') . " : $projectnumber"; - } - - if ( $form->{fromdate} || $form->{todate} ) { - - if ( $form->{fromdate} ) { - $fromdate = $locale->date( \%myconfig, $form->{fromdate}, 1 ); - } - if ( $form->{todate} ) { - $todate = $locale->date( \%myconfig, $form->{todate}, 1 ); - } - - $form->{period} = "$fromdate - $todate"; - } - else { - $form->{period} = - $locale->date( \%myconfig, $form->current_date( \%myconfig ), 1 ); - } - - if ($form->{prevreport}) { - push @options, {text => $form->{period}, href=> $form->{prevreport}}; - $form->{period} = "{prevreport}>$form->{period}"; - } - - - # construct callback - $department = $form->escape( $form->{department}, 1 ); - $projectnumber = $form->escape( $form->{projectnumber}, 1 ); - $title = $form->escape( $form->{title}, 1 ); - $form->{prevreport} = $form->escape( $form->{prevreport}, 1 ); - - $form->{callback} = -"$form->{script}?action=list_transactions&department=$department&projectnumber=$projectnumber&title=$title"; - for ( - qw(path direction oldsort accno login sessionid fromdate todate accounttype gifi_accno l_heading l_subtotal l_accno prevreport) - ) - { - $form->{callback} .= "&$_=$form->{$_}"; - } - - # add sort to callback - $form->{callback} = - $form->escape( $form->{callback} . "&sort=$form->{sort}" ); - - my @rows; - if ( @{ $form->{CA} } ) { - $sameitem = $form->{CA}->[0]->{ $form->{sort} }; - } - - $ml = ( $form->{category} =~ /(A|E)/ ) ? -1 : 1; - $ml *= -1 if $form->{contra}; - - if ( $form->{accno} && $form->{balance} ) { - my %column_data; - - for (@column_index) { $column_data{$_} = " " } - - $column_data{balance} = - $form->format_amount( \%myconfig, $form->{balance} * $ml, 2, 0 ); - - $i++; - $i %= 2; - - $column_data{i} = $i; - push @rows, \%column_data; - - } - - foreach my $ca ( @{ $form->{CA} } ) { - my %column_data; - - if ( $form->{l_subtotal} eq 'Y' ) { - if ( $sameitem ne $ca->{ $form->{sort} } ) { - push @rows, &ca_subtotal; - } - } - - $column_data{debit} = - $form->format_amount( \%myconfig, $ca->{debit}, 2, " " ); - $column_data{credit} = - $form->format_amount( \%myconfig, $ca->{credit}, 2, " " ); - - $form->{balance} += $ca->{amount}; - $column_data{balance} = - $form->format_amount( \%myconfig, $form->{balance} * $ml, 2, 0 ); - - $subtotaldebit += $ca->{debit}; - $subtotalcredit += $ca->{credit}; - - $totaldebit += $ca->{debit}; - $totalcredit += $ca->{credit}; - - $column_data{transdate} = $ca->{transdate}; - $column_data{reference} = { - text => $ca->{reference}, - href => "$ca->{module}.pl?path=$form->{path}&action=edit&id=$ca->{id}&login=$form->{login}&sessionid=$form->{sessionid}&callback=$form->{callback}"}; - - $column_data{description} = $ca->{description}; - - $column_data{cleared} = - ( $ca->{cleared} ) ? "*" : " "; - $column_data{source} = $ca->{source}; - - $column_data{accno} = []; - for ( @{ $ca->{accno} } ) { - push @{$column_data{accno}}, {text => $_, href=> "$drilldown&accno=$_>"}; - } - - if ( $ca->{id} != $sameid ) { - $i++; - $i %= 2; - } - $sameid = $ca->{id}; - - $column_data{i} = $i; - push @rows, \%column_data; - - } - - if ( $form->{l_subtotal} eq 'Y' ) { - push @rows, &ca_subtotal; - } - - for (@column_index) { $column_data{$_} = " " } - - $column_data{debit} = - $form->format_amount( \%myconfig, $totaldebit, 2, " " ); - $column_data{credit} = - $form->format_amount( \%myconfig, $totalcredit, 2, " " ); - $column_data{balance} = - $form->format_amount( \%myconfig, $form->{balance} * $ml, 2, 0 ); - - my @buttons; - push @buttons, { - name => 'action', - value => 'csv_list_transactions', - text => $locale->text('CSV Report'), - type => 'submit', - class => 'submit', - }; - - $form->{callback} = $form->unescape($form->{callback}); - my $template = LedgerSMB::Template->new( - user => \%myconfig, - locale => $locale, - path => 'UI', - template => 'ca-list-transactions', - format => ($form->{action} =~ /^csv/)? 'CSV': 'HTML'); - $template->render({ - form => \%$form, - options => \@options, - buttons => \@buttons, - columns => \@column_index, - heading => \%column_header, - totals => \%column_data, - rows => \@rows, - }); -} - -sub csv_list_transactions { &list_transactions } - sub ca_subtotal { my %column_data; -- cgit v1.2.3