summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rw-r--r--LedgerSMB/CA.pm466
-rw-r--r--LedgerSMB/GL.pm8
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} );