my $query = qq|SELECT accno, description
FROM chart
WHERE link LIKE '%_paid%'
- AND category = 'A'
+ AND (category = 'A' OR category = 'L')
ORDER BY accno|;
my $sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
push @{ $form->{PR} }, $ref;
}
$sth->finish;
+
+ $form->all_years($dbh, $myconfig);
+
$dbh->disconnect;
}
# connect to database, turn AutoCommit off
my $dbh = $form->dbconnect_noauto($myconfig);
- my ($query, $sth);
-
- # get cleared balance
- if ($form->{fromdate}) {
- $query = qq|SELECT sum(a.amount)
- FROM acc_trans a, chart c
- WHERE a.transdate < date '$form->{fromdate}'
- AND a.cleared = '1'
- AND c.id = a.chart_id
- AND c.accno = '$form->{accno}'
- |;
- } else {
- $query = qq|SELECT sum(a.amount)
- FROM acc_trans a, chart c
- WHERE a.cleared = '1'
- AND c.id = a.chart_id
- AND c.accno = '$form->{accno}'
- |;
- }
+ my $query;
+ my $sth;
+
+ $query = qq|SELECT category FROM chart
+ WHERE accno = '$form->{accno}'|;
+ ($form->{category}) = $dbh->selectrow_array($query);
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $cleared;
- ($form->{beginningbalance}) = $sth->fetchrow_array;
+ ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
- $sth->finish;
+ my $transdate = qq| AND ac.transdate < date '$form->{fromdate}'|;
- my %oid = ( 'Pg' => 'ac.oid',
- 'Oracle' => 'ac.rowid');
-
- $query = qq|SELECT c.name, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, a.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM customer c, acc_trans ac, ar a, chart ch
- WHERE c.id = a.customer_id
- AND ac.cleared = '0'
- AND ac.trans_id = a.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
+ if (! $form->{fromdate}) {
+ $cleared = qq| AND ac.cleared = '1'|;
+ $transdate = "";
+ }
+
+ # get beginning balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ $transdate
+ $cleared
|;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
-
- $query .= qq|
-
- UNION
- SELECT v.name, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, a.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM vendor v, acc_trans ac, ap a, chart ch
- WHERE v.id = a.vendor_id
- AND ac.cleared = '0'
- AND ac.trans_id = a.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
- |;
-
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
-
- $query .= qq|
+ ($form->{beginningbalance}) = $dbh->selectrow_array($query);
+
+ # fx balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ AND ac.fx_transaction = '1'
+ $transdate
+ $cleared
+ |;
+ ($form->{fx_balance}) = $dbh->selectrow_array($query);
- UNION
- SELECT g.description, ac.source, ac.transdate, ac.cleared,
- ac.fx_transaction, ac.amount, g.id,
- $oid{$myconfig->{dbdriver}} AS oid
- FROM gl g, acc_trans ac, chart ch
- WHERE g.id = ac.trans_id
- AND ac.cleared = '0'
- AND ac.trans_id = g.id
- AND ac.chart_id = ch.id
- AND ch.accno = '$form->{accno}'
+
+ $transdate = "";
+ if ($form->{todate}) {
+ $transdate = qq| AND ac.transdate <= date '$form->{todate}'|;
+ }
+
+ # get statement balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ $transdate
+ |;
+ ($form->{endingbalance}) = $dbh->selectrow_array($query);
+
+ # fx balance
+ $query = qq|SELECT sum(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ WHERE c.accno = '$form->{accno}'
+ AND ac.fx_transaction = '1'
+ $transdate
|;
+ ($form->{fx_endingbalance}) = $dbh->selectrow_array($query);
- $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
- $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
- $query .= " ORDER BY 3,7,8";
+ $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};
+
+ if ($form->{report}) {
+ $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
+ if ($form->{cleared}) {
+ $cleared = qq| AND ac.cleared = '1'|;
+ }
+ if ($form->{outstanding}) {
+ $cleared = ($form->{cleared}) ? "" : qq| AND ac.cleared = '0'|;
+ }
+ if (! $form->{fromdate}) {
+ $form->{beginningbalance} = 0;
+ $form->{fx_balance} = 0;
+ }
+ }
+
+
+ if ($form->{summary}) {
+ $query = qq|SELECT ac.transdate, ac.source,
+ sum(ac.amount) AS amount, ac.cleared
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.amount >= 0
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source,
+ sum(ac.amount) AS amount, ac.cleared
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.amount < 0
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+ $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
+
+ $query .= " ORDER BY 1,2";
+
+ } else {
+
+ $query = qq|SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, g.id, g.description
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN gl g ON (g.id = ac.trans_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, a.id, n.name
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN customer n ON (n.id = a.customer_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= qq|
+ UNION
+ SELECT ac.transdate, ac.source, ac.fx_transaction,
+ ac.amount, ac.cleared, a.id, n.name
+ FROM acc_trans ac
+ JOIN chart ch ON (ac.chart_id = ch.id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN vendor n ON (n.id = a.vendor_id)
+ WHERE ch.accno = '$form->{accno}'
+ AND ac.fx_transaction = '0'
+ $cleared|;
+ $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate};
+ $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate};
+
+ $query .= " ORDER BY 1,2,3";
+ }
$sth = $dbh->prepare($query);
$sth->execute || $form->dberror($query);
- while (my $pr = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{PR} }, $pr;
+ 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;
+ }
+
}
$sth->finish;
# connect to database
my $dbh = $form->dbconnect($myconfig);
- my ($query, $i);
- my %oid = ( 'Pg' => 'oid',
- 'Oracle' => 'rowid');
+ my $query = qq|SELECT id FROM chart
+ WHERE accno = '$form->{accno}'|;
+ my ($chart_id) = $dbh->selectrow_array($query);
+ $chart_id *= 1;
+
+ $query = qq|SELECT trans_id FROM acc_trans
+ WHERE source = ?
+ AND transdate = ?
+ AND cleared = '0'|;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ my $i;
+ my $trans_id;
+
+ $query = qq|UPDATE acc_trans SET cleared = '1'
+ WHERE cleared = '0'
+ AND trans_id = ?
+ AND transdate = ?
+ AND chart_id = $chart_id|;
+ my $tth = $dbh->prepare($query) || $form->dberror($query);
# clear flags
for $i (1 .. $form->{rowcount}) {
- if ($form->{"cleared_$i"}) {
- $query = qq|UPDATE acc_trans SET cleared = '1'
- WHERE $oid{$myconfig->{dbdriver}} = $form->{"oid_$i"}|;
- $dbh->do($query) || $form->dberror($query);
-
- # clear fx_transaction
- if ($form->{"fxoid_$i"}) {
- $query = qq|UPDATE acc_trans SET cleared = '1'
- WHERE $oid{$myconfig->{dbdriver}} = $form->{"fxoid_$i"}|;
- $dbh->do($query) || $form->dberror($query);
+ if ($form->{"cleared_$i"} && ! $form->{"oldcleared_$i"}) {
+ if ($form->{summary}) {
+ $sth->execute($form->{"source_$i"}, $form->{"transdate_$i"}) || $form->dberror;
+
+ while (($trans_id) = $sth->fetchrow_array) {
+ $tth->execute($trans_id, $form->{"transdate_$i"}) || $form->dberror;
+ $tth->finish;
+ }
+ $sth->finish;
+
+ } else {
+
+ $tth->execute($form->{"id_$i"}, $form->{"transdate_$i"}) || $form->dberror;
+ $tth->finish;
}
}
}
projects / freeside.git / blobdiff