+ my @a = qw(transdate invnumber name);
+ my $sortorder = $form->sort_order(\@a, \%ordinal);
+
+ $rate = 1 unless $rate;
+
+ if ($form->{summary}) {
+
+ $query = qq|SELECT a.id, '0' AS 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
+ AND a.invoice = '0'
+ $cashwhere
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount,
+ sum(ac.sellprice * ac.qty) * $rate * $ml AS tax,
+ a.till
+ FROM invoice ac
+ JOIN partstax pt ON (pt.parts_id = ac.parts_id)
+ JOIN chart ch ON (ch.id = pt.chart_id)
+ JOIN $form->{db} a ON (a.id = ac.trans_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)
+ WHERE $where
+ $accno
+ AND a.invoice = '1'
+ $cashwhere
+ GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name,
+ a.till
+ |;
+
+ if ($form->{fromdate}) {
+ # include open transactions from previous period
+ if ($cashwhere) {
+ $query .= qq|
+ UNION
+
+ SELECT a.id, '0' AS 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
+ AND a.invoice = '0'
+ $cashwhere
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount,
+ sum(ac.sellprice * ac.qty) * $rate * $ml AS tax,
+ a.till
+ FROM invoice ac
+ JOIN partstax pt ON (pt.parts_id = ac.parts_id)
+ JOIN chart ch ON (ch.id = pt.chart_id)
+ JOIN $form->{db} a ON (a.id = ac.trans_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)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ $accno
+ AND a.invoice = '1'
+ $cashwhere
+ GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name,
+ a.till
+ |;
+ }
+ }
+
+
+ } 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 * $rate * $ml 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) {
+ $query .= qq|
+ UNION
+
+ 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 a.datepaid >= '$form->{fromdate}'
+ $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 * $rate * $ml 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 a.datepaid >= '$form->{fromdate}'
+ $accno
+ AND a.invoice = '1'
+ $cashwhere
+ |;
+ }
+ }
+ }
+
+
+ if ($form->{report} =~ /nontaxable/) {
+
+ if ($form->{summary}) {
+ # only gather up non-taxable transactions
+ $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where
+ AND a.invoice = '0'
+ AND a.netamount = a.amount
+ $cashwhere
+ GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
+ a.till
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till
+ FROM invoice ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where
+ AND a.invoice = '1'
+ AND (
+ a.${table}_id NOT IN (
+ SELECT ${table}_id FROM ${table}tax t (${table}_id)
+ ) OR
+ ac.parts_id NOT IN (
+ SELECT parts_id FROM partstax p (parts_id)
+ )
+ )
+ $cashwhere
+ GROUP BY a.id, a.invnumber, $transdate, n.name, a.till
+ |;
+
+ if ($form->{fromdate}) {
+ if ($cashwhere) {
+ $query .= qq|
+ UNION
+
+ SELECT a.id, '0' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ AND a.invoice = '0'
+ AND a.netamount = a.amount
+ $cashwhere
+ GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
+ a.till
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till
+ FROM invoice ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ AND a.invoice = '1'
+ AND (
+ a.${table}_id NOT IN (
+ SELECT ${table}_id FROM ${table}tax t (${table}_id)
+ ) OR
+ ac.parts_id NOT IN (
+ SELECT parts_id FROM partstax p (parts_id)
+ )
+ )
+ $cashwhere
+ GROUP BY a.id, a.invnumber, $transdate, n.name, a.till
+ |;
+ }
+ }
+
+ } else {
+
+ # gather up details for non-taxable transactions
+ $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount,
+ a.notes AS description, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where
+ AND a.invoice = '0'
+ AND a.netamount = a.amount
+ $cashwhere
+ GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
+ a.notes, a.till
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount,
+ ac.description, a.till
+ FROM invoice ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE $where
+ AND a.invoice = '1'
+ AND (
+ a.${table}_id NOT IN (
+ SELECT ${table}_id FROM ${table}tax t (${table}_id)
+ ) OR
+ ac.parts_id NOT IN (
+ SELECT parts_id FROM partstax p (parts_id)
+ )
+ )
+ $cashwhere
+ GROUP BY a.id, a.invnumber, $transdate, n.name,
+ ac.description, a.till
+ |;
+
+ if ($form->{fromdate}) {
+ if ($cashwhere) {
+ $query .= qq|
+ UNION
+
+ SELECT a.id, '0' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name, a.netamount,
+ a.notes AS description, a.till
+ FROM acc_trans ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ AND a.invoice = '0'
+ AND a.netamount = a.amount
+ $cashwhere
+ GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount,
+ a.notes, a.till
+
+ UNION
+
+ SELECT a.id, '1' AS invoice, $transdate AS transdate,
+ a.invnumber, n.name,
+ sum(ac.sellprice * ac.qty) * $ml AS netamount,
+ ac.description, a.till
+ FROM invoice ac
+ JOIN $form->{db} a ON (a.id = ac.trans_id)
+ JOIN $table n ON (n.id = a.${table}_id)
+ WHERE a.datepaid >= '$form->{fromdate}'
+ AND a.invoice = '1'
+ AND (
+ a.${table}_id NOT IN (
+ SELECT ${table}_id FROM ${table}tax t (${table}_id)
+ ) OR
+ ac.parts_id NOT IN (
+ SELECT parts_id FROM partstax p (parts_id)
+ )
+ )
+ $cashwhere
+ GROUP BY a.id, a.invnumber, $transdate, n.name,
+ ac.description, a.till
+ |;
+ }
+ }
+
+ }