+ my $dr;
+ my $cr;
+ my $fxs;
+
+ if ($form->{summary}) {
+ $query = qq|SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN customer n ON (n.id = a.customer_id)
+ WHERE ac.fx_transaction = '1'
+ AND n.name = ?
+ AND ac.transdate = ?
+ AND ac.trans_id IN (SELECT id FROM ar a
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.source = ?)
+ AND ac.cleared = ?
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ $query .= qq|
+ UNION
+ SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN vendor n ON (n.id = a.vendor_id)
+ WHERE ac.fx_transaction = '1'
+ AND n.name = ?
+ AND ac.transdate = ?
+ AND ac.trans_id IN (SELECT id FROM ap a
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.source = ?)
+ AND ac.cleared = ?
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ } else {
+
+ $query = qq|SELECT ac.amount, ac.cleared
+ FROM acc_trans ac
+ WHERE ac.trans_id = ?
+ AND ac.fx_transaction = '1'
+ $cleared
+ AND NOT
+ (ac.chart_id IN
+ (SELECT fxgain_accno_id FROM defaults
+ UNION
+ SELECT fxloss_accno_id FROM defaults))
+ |;
+
+ }
+
+ $fxs = $dbh->prepare($query);
+
+
+ if ($form->{summary}) {
+ $query = qq|SELECT c.name
+ FROM customer c
+ JOIN ar a ON (c.id = a.customer_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ UNION
+ SELECT v.name
+ FROM vendor v
+ JOIN ap a ON (v.id = a.vendor_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ UNION
+ SELECT g.description
+ FROM gl g
+ JOIN acc_trans ac ON (g.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount > 0
+ $cleared
+ |;
+
+ $query .= " ORDER BY 1";
+ $dr = $dbh->prepare($query);
+
+
+ $query = qq|SELECT c.name
+ FROM customer c
+ JOIN ar a ON (c.id = a.customer_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ UNION
+ SELECT v.name
+ FROM vendor v
+ JOIN ap a ON (v.id = a.vendor_id)
+ JOIN acc_trans ac ON (a.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ UNION
+ SELECT g.description
+ FROM gl g
+ JOIN acc_trans ac ON (g.id = ac.trans_id)
+ WHERE ac.transdate = ?
+ AND ac.source = ?
+ AND ac.amount < 0
+ $cleared
+ |;
+
+ $query .= " ORDER BY 1";
+ $cr = $dbh->prepare($query);
+ }
+
+
+ my $name;
+ my $ref;
+ my $xfref;
+
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+
+ if ($form->{summary}) {
+
+ if ($ref->{amount} > 0) {
+ $dr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
+ $ref->{oldcleared} = $ref->{cleared};
+ $ref->{name} = ();
+ while (($name) = $dr->fetchrow_array) {
+ push @{ $ref->{name} }, $name;
+ }
+ $dr->finish;
+ } else {
+
+ $cr->execute($ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source}, $ref->{transdate}, $ref->{source});
+ $ref->{oldcleared} = $ref->{cleared};
+ $ref->{name} = ();
+ while (($name) = $cr->fetchrow_array) {
+ push @{ $ref->{name} }, $name;
+ }
+ $cr->finish;
+
+ }
+
+ } else {
+ push @{ $ref->{name} }, $ref->{description};
+ }
+
+ push @{ $form->{PR} }, $ref;
+
+ # include fx transactions
+ $amount = 0;
+ $addfx = 0;
+ $ref->{oldcleared} = $ref->{cleared};
+ if ($form->{summary}) {
+ foreach $name (@{ $ref->{name} }) {
+ $fxs->execute($name, $ref->{transdate}, $ref->{source}, $ref->{cleared}, $name, $ref->{transdate}, $ref->{source}, $ref->{cleared});
+ while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
+ $addfx = 1;
+ $amount += $fxref->{amount};
+ }
+ $fxs->finish;
+ }
+ } else {
+ $fxs->execute($ref->{id});
+ while ($fxref = $fxs->fetchrow_hashref(NAME_lc)) {
+ $addfx = 1;
+ $amount += $fxref->{amount};
+ }
+ $fxs->finish;
+ }
+
+ if ($addfx) {
+ $fxref = ();
+ map { $fxref->{$_} = $ref->{$_} } keys %$ref;
+ $fxref->{fx_transaction} = 1;
+ $fxref->{name} = ();
+ $fxref->{source} = "";
+ $fxref->{transdate} = "";
+ $fxref->{amount} = $amount;
+ push @{ $form->{PR} }, $fxref;
+ }
+