summaryrefslogtreecommitdiff
path: root/LedgerSMB
diff options
context:
space:
mode:
Diffstat (limited to 'LedgerSMB')
-rwxr-xr-xLedgerSMB/RP.pm270
1 files changed, 131 insertions, 139 deletions
diff --git a/LedgerSMB/RP.pm b/LedgerSMB/RP.pm
index 71432f58..a54dd688 100755
--- a/LedgerSMB/RP.pm
+++ b/LedgerSMB/RP.pm
@@ -1716,169 +1716,161 @@ sub get_taxaccounts {
sub tax_report {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
- my ($null, $department_id) = split /--/, $form->{department};
+ my ($null, $department_id) = split /--/, $form->{department};
- # build WHERE
- my $where = "1 = 1";
- my $cashwhere = "";
-
- if ($department_id) {
- $where .= qq|
- AND a.department_id = $department_id
- |;
- }
+ # build WHERE
+ my $where = "1 = 1";
+ my $cashwhere = "";
+
+ if ($department_id) {
+ $where .= qq|AND a.department_id = |.
+ $dbh->quote($department_id);
+ }
- my $query;
- my $sth;
- my $accno;
+ my $query;
+ my $sth;
+ my $accno;
- if ($form->{accno}) {
- if ($form->{accno} =~ /^gifi_/) {
- ($null, $accno) = split /_/, $form->{accno};
- $accno = qq| AND ch.gifi_accno = '$accno'|;
- } else {
- $accno = $form->{accno};
- $accno = qq| AND ch.accno = '$accno'|;
- }
- }
+ if ($form->{accno}) {
+ if ($form->{accno} =~ /^gifi_/) {
+ ($null, $accno) = split /_/, $form->{accno};
+ $accno = $dbh->quote($accno);
+ $accno = qq| AND ch.gifi_accno = $accno|;
+ } else {
+ $accno = $dbh->quote($form->{accno});
+ $accno = qq| AND ch.accno = $accno|;
+ }
+ }
- my $table;
- my $ARAP;
+ my $table;
+ my $ARAP;
- if ($form->{db} eq 'ar') {
- $table = "customer";
- $ARAP = "AR";
- }
- if ($form->{db} eq 'ap') {
- $table = "vendor";
- $ARAP = "AP";
- }
+ if ($form->{db} eq 'ar') {
+ $table = "customer";
+ $ARAP = "AR";
+ }
+ if ($form->{db} eq 'ap') {
+ $table = "vendor";
+ $ARAP = "AP";
+ }
- my $transdate = "a.transdate";
+ my $transdate = "a.transdate";
- ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
+ ($form->{fromdate}, $form->{todate}) =
+ $form->from_to($form->{year},$form->{month}, $form->{interval})
+ if $form->{year} && $form->{month};
- # if there are any dates construct a where
- if ($form->{fromdate} || $form->{todate}) {
- if ($form->{fromdate}) {
- $where .= " AND $transdate >= '$form->{fromdate}'";
- }
- if ($form->{todate}) {
- $where .= " AND $transdate <= '$form->{todate}'";
- }
- }
+ # if there are any dates construct a where
+ if ($form->{fromdate} || $form->{todate}) {
+ if ($form->{fromdate}) {
+ $where .= " AND $transdate >= '$form->{fromdate}'";
+ }
+ if ($form->{todate}) {
+ $where .= " AND $transdate <= '$form->{todate}'";
+ }
+ }
- if ($form->{method} eq 'cash') {
- $transdate = "a.datepaid";
+ if ($form->{method} eq 'cash') {
+ $transdate = "a.datepaid";
- my $todate = $form->{todate};
- if (! $todate) {
- ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
- }
+ my $todate = $form->{todate};
+ if (! $todate) {
+ ($todate) = $dbh->selectrow_array(
+ qq|SELECT current_date|);
+ }
- $cashwhere = qq|
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = chart.id)
- WHERE link LIKE '%${ARAP}_paid%'
- AND $transdate <= '$todate'
- AND a.paid = a.amount
- )
- |;
+ $cashwhere = qq|
+ AND ac.trans_id IN (
+ SELECT trans_id
+ FROM acc_trans
+ JOIN chart ON (chart_id = chart.id)
+ WHERE link LIKE '%${ARAP}_paid%'
+ AND $transdate <= |.$dbh->quote($todate).qq|
+ AND a.paid = a.amount)|;
- }
+ }
- my $ml = ($form->{db} eq 'ar') ? 1 : -1;
+ my $ml = ($form->{db} eq 'ar') ? 1 : -1;
- my %ordinal = ( 'transdate' => 3,
- 'invnumber' => 4,
- 'name' => 5
- );
+ my %ordinal = ( 'transdate' => 3, 'invnumber' => 4, 'name' => 5 );
- my @a = qw(transdate invnumber name);
- my $sortorder = $form->sort_order(\@a, \%ordinal);
+ my @a = qw(transdate invnumber name);
+ my $sortorder = $form->sort_order(\@a, \%ordinal);
- if ($form->{summary}) {
+ if ($form->{summary}) {
- $query = qq|SELECT a.id, a.invoice, $transdate AS transdate,
- a.invnumber, n.name, a.netamount,
- ac.amount * $ml AS tax,
- a.till
- FROM acc_trans ac
- JOIN $form->{db} a ON (a.id = ac.trans_id)
- JOIN chart ch ON (ch.id = ac.chart_id)
- JOIN $table n ON (n.id = a.${table}_id)
- WHERE $where
- $accno
- $cashwhere
- |;
-
- if ($form->{fromdate}) {
- # include open transactions from previous period
- if ($cashwhere) {
- $query .= qq|
- UNION
-
- SELECT a.id, a.invoice, $transdate AS transdate,
- a.invnumber, n.name, a.netamount,
- ac.amount * $ml AS tax,
- a.till
- FROM acc_trans ac
- JOIN $form->{db} a ON (a.id = ac.trans_id)
- JOIN chart ch ON (ch.id = ac.chart_id)
- JOIN $table n ON (n.id = a.${table}_id)
- WHERE a.datepaid >= '$form->{fromdate}'
- $accno
- $cashwhere
- |;
- }
- }
+ $query = qq|
+ SELECT a.id, a.invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount,
+ ac.amount * $ml AS tax, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN chart ch ON (ch.id = ac.chart_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where $accno $cashwhere |;
+
+ if ($form->{fromdate}) {
+ # include open transactions from previous period
+ if ($cashwhere) {
+ $query .= qq|
+ UNION
+
+ SELECT a.id, a.invoice,
+ $transdate AS transdate, a.invnumber,
+ n.name, a.netamount, ac.
+ amount * $ml AS tax, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN chart ch ON (ch.id = ac.chart_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ $accno $cashwhere|;
+ }
+ }
- } else {
-
- $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
- a.invnumber, n.name, a.netamount,
- ac.amount * $ml AS tax,
- a.notes AS description, a.till
- FROM acc_trans ac
- JOIN $form->{db} a ON (a.id = ac.trans_id)
- JOIN chart ch ON (ch.id = ac.chart_id)
- JOIN $table n ON (n.id = a.${table}_id)
- WHERE $where
- $accno
- AND a.invoice = '0'
- $cashwhere
-
- UNION
+ } else {
+
+ $query = qq|
+ SELECT a.id, '0' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount,
+ ac.amount * $ml AS tax, a.notes AS description,
+ a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN chart ch ON (ch.id = ac.chart_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where $accno AND a.invoice = '0' $cashwhere
+
+ UNION
- SELECT a.id, '1' AS invoice, $transdate AS transdate,
- a.invnumber, n.name,
- i.sellprice * i.qty * $ml AS netamount,
- i.sellprice * i.qty * $ml *
- (SELECT tx.rate FROM tax tx WHERE tx.chart_id = ch.id AND (tx.validto > $transdate OR tx.validto IS NULL) ORDER BY validto LIMIT 1) AS tax,
- i.description, a.till
- FROM acc_trans ac
- JOIN $form->{db} a ON (a.id = ac.trans_id)
- JOIN chart ch ON (ch.id = ac.chart_id)
- JOIN $table n ON (n.id = a.${table}_id)
- JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id)
- JOIN invoice i ON (i.trans_id = a.id)
- JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id)
- WHERE $where
- $accno
- AND a.invoice = '1'
- $cashwhere
- |;
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ i.sellprice * i.qty * $ml AS netamount,
+ i.sellprice * i.qty * $ml *
+ (SELECT tx.rate FROM tax tx
+ WHERE tx.chart_id = ch.id
+ AND (tx.validto > $transdate
+ OR tx.validto IS NULL)
+ ORDER BY validto LIMIT 1)
+ AS tax, i.description, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN chart ch ON (ch.id = ac.chart_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ JOIN ${table}tax t
+ ON (t.${table}_id = n.id AND t.chart_id = ch.id)
+ JOIN invoice i ON (i.trans_id = a.id)
+ JOIN partstax pt
+ ON (pt.parts_id = i.parts_id
+ AND pt.chart_id = ch.id)
+ WHERE $where $accno AND a.invoice = '1' $cashwhere|;
if ($form->{fromdate}) {
if ($cashwhere) {
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-28 04:45:47 +0000