summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rwxr-xr-xLedgerSMB/CA.pm349
1 files changed, 236 insertions, 113 deletions
diff --git a/LedgerSMB/CA.pm b/LedgerSMB/CA.pm
index ca6eedb9..b92ac49d 100755
--- a/LedgerSMB/CA.pm
+++ b/LedgerSMB/CA.pm
@@ -42,12 +42,13 @@ sub all_accounts {
my $amount = ();
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
- my $query = qq|SELECT accno, SUM(acc_trans.amount) AS amount
- FROM chart, acc_trans
- WHERE chart.id = acc_trans.chart_id
- GROUP BY accno|;
+ my $query = qq|
+ SELECT accno, SUM(acc_trans.amount) AS amount
+ FROM chart, acc_trans
+ WHERE chart.id = acc_trans.chart_id
+ GROUP BY accno|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -58,8 +59,9 @@ sub all_accounts {
$sth->finish;
- $query = qq|SELECT accno, description
- FROM gifi|;
+ $query = qq|
+ SELECT accno, description
+ FROM gifi|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -72,10 +74,11 @@ sub all_accounts {
$sth->finish;
- $query = qq|SELECT c.id, c.accno, c.description, c.charttype,
- c.gifi_accno, c.category, c.link
- FROM chart c
- ORDER BY accno|;
+ $query = qq|
+ SELECT c.id, c.accno, c.description, c.charttype,
+ c.gifi_accno, c.category, c.link
+ FROM chart c
+ ORDER BY accno|;
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
@@ -94,7 +97,7 @@ sub all_accounts {
}
$sth->finish;
- $dbh->disconnect;
+ $dbh->commit;
}
@@ -104,21 +107,26 @@ sub all_transactions {
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
# get chart_id
- my $query = qq|SELECT id
- FROM chart
- WHERE accno = '$form->{accno}'|;
+ 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 = '$form->{gifi_accno}'|;
+ $query = qq|
+ SELECT id
+ FROM chart
+ WHERE gifi_accno = ?|;
+ $accno = $form->{gifi_accno};
}
my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth->execute($accno) || $form->dberror($query);
my @id = ();
@@ -133,16 +141,19 @@ sub all_transactions {
($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
+ my $fdate;
if ($form->{fromdate}) {
- $fromdate_where = qq| AND ac.transdate >= '$form->{fromdate}' |;
+ $fromdate_where = qq| AND ac.transdate >= ? |;
+ $fdate = $form->{fromdate};
}
-
+ my $tdate;
if ($form->{todate}) {
- $todate_where .= qq| AND ac.transdate <= '$form->{todate}' |;
+ $todate_where .= qq| AND ac.transdate <= ? |;
+ $tdate = $form->{todate};
}
- my $false = ($myconfig->{dbdriver} =~ /Pg/) ? FALSE : q|'0'|;
+ my $false = 'FALSE';
# Oracle workaround, use ordinal positions
my %ordinal = ( transdate => 4,
@@ -160,34 +171,47 @@ sub all_transactions {
($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 = $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 = $project_id |;
+ $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 = '$form->{accno}'|;
+ $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 = '$form->{gifi_accno}'
- AND charttype = 'A'|;
+ $query = qq|
+ SELECT description, category, link, contra
+ FROM chart
+ WHERE gifi_accno = ?
+ AND charttype = 'A'|;
+ $accno = $form->{gifi_accno};
}
- ($form->{description}, $form->{category}, $form->{link}, $form->{contra}) = $dbh->selectrow_array($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute($accno);
+ ($form->{description}, $form->{category}, $form->{link},
+ $form->{contra})
+ = $sth->fetchrow_array($query);
if ($form->{fromdate}) {
@@ -200,27 +224,58 @@ sub all_transactions {
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 = '$form->{gifi_accno}'
- AND ac.transdate < '$form->{fromdate}'
- AND a.department_id = $department_id
- $project |;
-
+ $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
+ = ?
+ $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 = '$form->{accno}'
- AND ac.transdate < '$form->{fromdate}'
- AND a.department_id = $department_id
- $project |;
+ $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
+ = ?
+ $project |;
+ push @queryargs, $form->{accno},
+ $form->{fromdate},
+ $department_id;
+ if ($p_id){
+ push @queryargs, $p_id;
+ }
}
$union = qq| UNION ALL |;
@@ -229,24 +284,41 @@ sub all_transactions {
} 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)
- WHERE c.gifi_accno = '$form->{gifi_accno}'
- AND ac.transdate < '$form->{fromdate}'
- $project |;
+ $query = qq|
+ SELECT SUM(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON
+ (ac.chart_id = c.id)
+ WHERE c.gifi_accno = ?
+ AND ac.transdate < ?
+ $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)
- WHERE c.accno = '$form->{accno}'
- AND ac.transdate < '$form->{fromdate}'
- $project |;
+ $query = qq|
+ SELECT SUM(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c
+ ON (ac.chart_id = c.id)
+ WHERE c.accno = ?
+ AND ac.transdate < ?
+ $project |;
+ @queryargs = ($form->{accno},
+ $form->{fromdate});
+ if ($p_id){
+ push @queryargs, $p_id;
+ }
}
}
- ($form->{balance}) = $dbh->selectrow_array($query);
-
+ $sth = $dbh->prepare($query);
+ $sth->execute(@queryargs);
+ ($form->{balance}) = $sth->fetchrow_array($query);
+ $sth->finish;
+ @queryargs = ();
}
}
@@ -256,57 +328,108 @@ sub all_transactions {
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 = $id
- $fromdate_where
- $todate_where
- $dpt_where
- $project
-
- UNION ALL
-
- SELECT a.id, a.invnumber, c.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 customer c ON (a.customer_id = c.id)
- $dpt_join
- WHERE ac.chart_id = $id
- $fromdate_where
- $todate_where
- $dpt_where
- $project
-
- UNION ALL
-
- SELECT a.id, a.invnumber, v.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 vendor v ON (a.vendor_id = v.id)
- $dpt_join
- WHERE ac.chart_id = $id
- $fromdate_where
- $todate_where
- $dpt_where
- $project |;
+ $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
+ $project|;
+ if ($d_id){
+ push @queryargs, $d_id;
+ }
+ 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, c.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 customer c ON (a.customer_id = c.id)
+ $dpt_join
+ WHERE ac.chart_id = ?
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project|;
+
+ if ($d_id){
+ push @queryargs, $d_id;
+ }
+ 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, v.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 vendor v ON (a.vendor_id = v.id)
+ $dpt_join
+ WHERE ac.chart_id = ?
+ $fromdate_where
+ $todate_where
+ $dpt_where
+ $project |;
+
+ if ($d_id){
+ push @queryargs, $d_id;
+ }
+ 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 || $form->dberror($query);
+ $sth->execute(@queryargs) || $form->dberror($query);
$query = qq|SELECT c.id, c.accno
FROM chart c
@@ -389,7 +512,7 @@ sub all_transactions {
}
$sth->finish;
- $dbh->disconnect;
+ $dbh->commit;
}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-29 19:00:01 +0000