diff options
Diffstat (limited to 'LedgerSMB')
| -rw-r--r-- | LedgerSMB/CA.pm | 466 | ||||
| -rw-r--r-- | LedgerSMB/GL.pm | 8 |
2 files changed, 5 insertions, 469 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} ); |