[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [470] trunk/LedgerSMB/RP.pm
- Subject: SF.net SVN: ledger-smb: [470] trunk/LedgerSMB/RP.pm
- From: ..hidden..
- Date: Thu, 02 Nov 2006 16:48:53 -0800
Revision: 470
http://svn.sourceforge.net/ledger-smb/?rev=470&view=rev
Author: einhverfr
Date: 2006-11-02 16:48:48 -0800 (Thu, 02 Nov 2006)
Log Message:
-----------
RP.pm Mostly done
Modified Paths:
--------------
trunk/LedgerSMB/RP.pm
Modified: trunk/LedgerSMB/RP.pm
===================================================================
--- trunk/LedgerSMB/RP.pm 2006-11-02 23:24:28 UTC (rev 469)
+++ trunk/LedgerSMB/RP.pm 2006-11-03 00:48:48 UTC (rev 470)
@@ -23,7 +23,7 @@
#
#======================================================================
#
-# This file has NOT undergone whitespace cleanup.
+# This file has undergone whitespace cleanup.
#
#======================================================================
#
@@ -34,1567 +34,1622 @@
package RP;
sub inventory_activity {
- my ($self, $myconfig, $form) = @_;
- ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval}) if $form->{fromyear} && $form->{frommonth};
+ my ($self, $myconfig, $form) = @_;
+ ($form->{fromdate}, $form->{todate}) =
+ $form->from_to($form->{fromyear}, $form->{frommonth},
+ $form->{interval})
+ if $form->{fromyear} && $form->{frommonth};
+ my $dbh = $form->{dbh};
- unless ($form->{sort_col}){
- $form->{sort_col} = 'partnumber';
- }
+ unless ($form->{sort_col}){
+ $form->{sort_col} = 'partnumber';
+ }
- my $dbh = $form->dbconnect($myconfig) || $form->dberror();
- my $where = '';
- if ($form->{fromdate}){
- $where .= "AND coalesce(ar.duedate, ap.duedate) >= ".$dbh->quote($form->{fromdate});
- }
- if ($form->{todate}){
- $where .= "AND coalesce(ar.duedate, ap.duedate) < ".$dbh->quote($form->{todate}). " ";
- }
- if ($form->{partnumber}){
- $where .= qq|AND p.partnumber ILIKE '%|.$form->{partnumber}.qq|%' |;
- }
- if ($form->{description}){
- $where .= q|AND p.description ILIKE '%|.$form->{description}.q|%' |;
- }
- $where =~ s/^AND/WHERE/;
+ my $where = '';
+ if ($form->{fromdate}){
+ $where .= "AND coalesce(ar.duedate, ap.duedate) >= ".
+ $dbh->quote($form->{fromdate});
+ }
+ if ($form->{todate}){
+ $where .= "AND coalesce(ar.duedate, ap.duedate) < ".
+ $dbh->quote($form->{todate}). " ";
+ }
+ if ($form->{partnumber}){
+ $where .= qq| AND p.partnumber ILIKE |.
+ $dbh->quote('%'."$form->{partnumber}%");
+ }
+ if ($form->{description}){
+ $where .= q| AND p.description ILIKE |
+ .$dbh->quote('%'."$form->{description}%");
+ }
+ $where =~ s/^\s?AND/WHERE/;
- my $query = qq|
- SELECT min(p.description) AS description,
- min(p.partnumber) AS partnumber, sum(
- CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END
- ) AS sold, sum (
- CASE WHEN i.qty > 0 THEN i.sellprice * i.qty ELSE 0 END
- ) AS revenue, sum(
- CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END
- ) AS received, sum(
- CASE WHEN i.qty < 0 THEN i.sellprice * i.qty * -1
- ELSE 0 END
- ) as expenses, min(p.id) as id
- FROM invoice i
- INNER JOIN parts p ON (i.parts_id = p.id)
- LEFT JOIN ar ON (ar.id = i.trans_id)
- LEFT JOIN ap ON (ap.id = i.trans_id)
- $where
- GROUP BY i.parts_id
- ORDER BY $form->{sort_col}
- |;
- my $sth = $dbh->prepare($query) || $form->dberror($query);
- $sth->execute() || $form->dberror($query);
- @cols = qw(description sold revenue partnumber received expense);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{net_income} = $ref->{revenue} - $ref->{expense};
- map {$ref->{$_} =~ s/^\s*//} @cols;
- map {$ref->{$_} =~ s/\s*$//} @cols;
- push @{$form->{TB}}, $ref;
- }
- $sth->finish;
- $dbh->disconnect;
+ my $query = qq|
+ SELECT min(p.description) AS description,
+ min(p.partnumber) AS partnumber, sum(
+ CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END) AS sold,
+ sum (CASE WHEN i.qty > 0
+ THEN i.sellprice * i.qty
+ ELSE 0 END) AS revenue,
+ sum(CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END)
+ AS received, sum(CASE WHEN i.qty < 0
+ THEN i.sellprice * i.qty * -1
+ ELSE 0 END) as expenses,
+ min(p.id) as id
+ FROM invoice i
+ JOIN parts p ON (i.parts_id = p.id)
+ LEFT JOIN ar ON (ar.id = i.trans_id)
+ LEFT JOIN ap ON (ap.id = i.trans_id)
+ $where
+ GROUP BY i.parts_id
+ ORDER BY $form->{sort_col}|;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+ $sth->execute() || $form->dberror($query);
+ @cols = qw(description sold revenue partnumber received expense);
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $ref->{net_income} = $ref->{revenue} - $ref->{expense};
+ map {$ref->{$_} =~ s/^\s*//} @cols;
+ map {$ref->{$_} =~ s/\s*$//} @cols;
+ push @{$form->{TB}}, $ref;
+ }
+ $sth->finish;
+ $dbh->commit;
}
sub yearend_statement {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->dbconnect($myconfig);
- # if todate < existing yearends, delete GL and yearends
- my $query = qq|SELECT trans_id FROM yearend
- WHERE transdate >= '$form->{todate}'|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- my @trans_id = ();
- my $id;
- while (($id) = $sth->fetchrow_array) {
- push @trans_id, $id;
- }
- $sth->finish;
+ # if todate < existing yearends, delete GL and yearends
+ my $query = qq|SELECT trans_id FROM yearend WHERE transdate >= ?|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($form->{todate}) || $form->dberror($query);
+
+ my @trans_id = ();
+ my $id;
+ while (($id) = $sth->fetchrow_array) {
+ push @trans_id, $id;
+ }
+ $sth->finish;
- $query = qq|DELETE FROM gl
- WHERE id = ?|;
- $sth = $dbh->prepare($query) || $form->dberror($query);
+ $query = qq|DELETE FROM gl WHERE id = ?|;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
- $query = qq|DELETE FROM acc_trans
- WHERE trans_id = ?|;
- my $ath = $dbh->prepare($query) || $form->dberror($query);
+ $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
+ my $ath = $dbh->prepare($query) || $form->dberror($query);
- foreach $id (@trans_id) {
- $sth->execute($id);
- $ath->execute($id);
+ foreach $id (@trans_id) {
+ $sth->execute($id);
+ $ath->execute($id);
- $sth->finish;
- $ath->finish;
- }
+ $sth->finish;
+ $ath->finish;
+ }
- my $last_period = 0;
- my @categories = qw(I E);
- my $category;
+ my $last_period = 0;
+ my @categories = qw(I E);
+ my $category;
- $form->{decimalplaces} *= 1;
+ $form->{decimalplaces} *= 1;
- &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, ..hidden..);
+ &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, ..hidden..);
- # disconnect
- $dbh->disconnect;
+ $dbh->commit;
- # now we got $form->{I}{accno}{ }
- # and $form->{E}{accno}{ }
+ # now we got $form->{I}{accno}{ }
+ # and $form->{E}{accno}{ }
- my %account = ( 'I' => { 'label' => 'income',
- 'labels' => 'income',
- 'ml' => 1 },
- 'E' => { 'label' => 'expense',
- 'labels' => 'expenses',
- 'ml' => -1 }
+ my %account = (
+ 'I' => {
+ 'label' => 'income',
+ 'labels' => 'income',
+ 'ml' => 1 },
+ 'E' => {
+ 'label' => 'expense',
+ 'labels' => 'expenses',
+ 'ml' => -1 }
);
- foreach $category (@categories) {
- foreach $key (sort keys %{ $form->{$category} }) {
- if ($form->{$category}{$key}{charttype} eq 'A') {
- $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
- }
- }
- }
+ foreach $category (@categories) {
+ foreach $key (sort keys %{ $form->{$category} }) {
+ if ($form->{$category}{$key}{charttype} eq 'A') {
+ $form->{"total_$account{$category}{labels}_this_period"}
+ += $form->{$category}{$key}{this}
+ * $account{$category}{ml};
+ }
+ }
+ }
- # totals for income and expenses
- $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
- $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
+ # totals for income and expenses
+ $form->{total_income_this_period} = $form->round_amount(
+ $form->{total_income_this_period}, $form->{decimalplaces});
+ $form->{total_expenses_this_period} = $form->round_amount(
+ $form->{total_expenses_this_period}, $form->{decimalplaces});
- # total for income/loss
- $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
+ # total for income/loss
+ $form->{total_this_period}
+ = $form->{total_income_this_period}
+ - $form->{total_expenses_this_period};
}
sub income_statement {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
- my $last_period = 0;
- my @categories = qw(I E);
- my $category;
+ my $last_period = 0;
+ my @categories = qw(I E);
+ my $category;
- $form->{decimalplaces} *= 1;
+ $form->{decimalplaces} *= 1;
- if (! ($form->{fromdate} || $form->{todate})) {
- if ($form->{fromyear} && $form->{frommonth}) {
- ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{fromyear}, $form->{frommonth}, $form->{interval});
- }
- }
+ if (! ($form->{fromdate} || $form->{todate})) {
+ if ($form->{fromyear} && $form->{frommonth}) {
+ ($form->{fromdate}, $form->{todate})
+ = $form->from_to(
+ $form->{fromyear},
+ $form->{frommonth}, $form->{interval});
+ }
+ }
- &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, ..hidden.., 1);
+ &get_accounts(
+ $dbh, $last_period, $form->{fromdate}, $form->{todate},
+ $form, ..hidden.., 1);
- if (! ($form->{comparefromdate} || $form->{comparetodate})) {
- if ($form->{compareyear} && $form->{comparemonth}) {
- ($form->{comparefromdate}, $form->{comparetodate}) = $form->from_to($form->{compareyear}, $form->{comparemonth}, $form->{interval});
- }
- }
+ if (! ($form->{comparefromdate} || $form->{comparetodate})) {
+ if ($form->{compareyear} && $form->{comparemonth}) {
+ ($form->{comparefromdate}, $form->{comparetodate})
+ = $form->from_to(
+ $form->{compareyear},
+ $form->{comparemonth},
+ $form->{interval});
+ }
+ }
- # if there are any compare dates
- if ($form->{comparefromdate} || $form->{comparetodate}) {
- $last_period = 1;
+ # if there are any compare dates
+ if ($form->{comparefromdate} || $form->{comparetodate}) {
+ $last_period = 1;
- &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, ..hidden.., 1);
- }
+ &get_accounts(
+ $dbh, $last_period, $form->{comparefromdate},
+ $form->{comparetodate}, $form, ..hidden.., 1);
+ }
- # disconnect
- $dbh->disconnect;
+ $dbh->commit;
- # now we got $form->{I}{accno}{ }
- # and $form->{E}{accno}{ }
+ # now we got $form->{I}{accno}{ }
+ # and $form->{E}{accno}{ }
- my %account = ( 'I' => { 'label' => 'income',
- 'labels' => 'income',
- 'ml' => 1 },
- 'E' => { 'label' => 'expense',
- 'labels' => 'expenses',
- 'ml' => -1 }
+ my %account = (
+ 'I' => {
+ 'label' => 'income',
+ 'labels' => 'income',
+ 'ml' => 1 },
+ 'E' => {
+ 'label' => 'expense',
+ 'labels' => 'expenses',
+ 'ml' => -1 }
);
- my $str;
+ my $str;
- foreach $category (@categories) {
+ foreach $category (@categories) {
- foreach $key (sort keys %{ $form->{$category} }) {
- # push description onto array
+ foreach $key (sort keys %{ $form->{$category} }) {
+ # push description onto array
- $str = ($form->{l_heading}) ? $form->{padding} : "";
+ $str = ($form->{l_heading}) ? $form->{padding} : "";
- if ($form->{$category}{$key}{charttype} eq "A") {
- $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
- }
- if ($form->{$category}{$key}{charttype} eq "H") {
- if ($account{$category}{subtotal} && $form->{l_subtotal}) {
- $dash = "- ";
- push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
- push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ if ($form->{$category}{$key}{charttype} eq "A") {
+ $str .=
+ ($form->{l_accno})
+ ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
+ : "$form->{$category}{$key}{description}";
+ }
+ if ($form->{$category}{$key}{charttype} eq "H") {
+ if ($account{$category}{subtotal}
+ && $form->{l_subtotal}) {
+
+ $dash = "- ";
+ push(@{$form->{"$account{$category}{label}_account"}},
+ "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
+
+ push(@{$form->{"$account{$category}{labels}_this_period"}},
+ $form->format_amount(
+ $myconfig,
+ $account{$category}{subthis}
+ * $account{$category}{ml},
+ $form->{decimalplaces},
+ $dash));
- if ($last_period) {
- push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
+ if ($last_period) {
+ # Chris T: Giving up on
+ # Formatting this one :-(
+ push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
- }
+ }
- $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
+ $str = "$form->{br}$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
- $account{$category}{subthis} = $form->{$category}{$key}{this};
- $account{$category}{sublast} = $form->{$category}{$key}{last};
- $account{$category}{subdescription} = $form->{$category}{$key}{description};
- $account{$category}{subtotal} = 1;
+ $account{$category}{subthis}
+ = $form->{$category}{$key}{this};
+ $account{$category}{sublast}
+ = $form->{$category}{$key}{last};
+ $account{$category}{subdescription}
+ = $form->{$category}{$key}{description};
+ $account{$category}{subtotal} = 1;
- $form->{$category}{$key}{this} = 0;
- $form->{$category}{$key}{last} = 0;
+ $form->{$category}{$key}{this} = 0;
+ $form->{$category}{$key}{last} = 0;
- next unless $form->{l_heading};
+ next unless $form->{l_heading};
- $dash = " ";
- }
+ $dash = " ";
+ }
- push(@{$form->{"$account{$category}{label}_account"}}, $str);
+ push(@{$form->{"$account{$category}{label}_account"}},
+ $str);
- if ($form->{$category}{$key}{charttype} eq 'A') {
- $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
- $dash = "- ";
- }
+ if ($form->{$category}{$key}{charttype} eq 'A') {
+ $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
+
+ $dash = "- ";
+ }
- push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- # add amount or - for last period
- if ($last_period) {
- $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
+ # add amount or - for last period
+ if ($last_period) {
+ $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
- push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
- }
+ push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig,$form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
+ }
- $str = ($form->{l_heading}) ? $form->{padding} : "";
- if ($account{$category}{subtotal} && $form->{l_subtotal}) {
- push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
- push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ $str = ($form->{l_heading}) ? $form->{padding} : "";
+ if ($account{$category}{subtotal} && $form->{l_subtotal}) {
+ push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
+ push(@{$form->{"$account{$category}{labels}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- if ($last_period) {
- push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
- }
+ if ($last_period) {
+ push(@{$form->{"$account{$category}{labels}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
+ }
- }
+ }
- # totals for income and expenses
- $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
- $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
+ # totals for income and expenses
+ $form->{total_income_this_period} = $form->round_amount($form->{total_income_this_period}, $form->{decimalplaces});
+ $form->{total_expenses_this_period} = $form->round_amount($form->{total_expenses_this_period}, $form->{decimalplaces});
- # total for income/loss
- $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
+ # total for income/loss
+ $form->{total_this_period} = $form->{total_income_this_period} - $form->{total_expenses_this_period};
- if ($last_period) {
- # total for income/loss
- $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
+ if ($last_period) {
+ # total for income/loss
+ $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period} - $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
- # totals for income and expenses for last_period
- $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
- $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
+ # totals for income and expenses for last_period
+ $form->{total_income_last_period} = $form->format_amount($myconfig, $form->{total_income_last_period}, $form->{decimalplaces}, "- ");
+ $form->{total_expenses_last_period} = $form->format_amount($myconfig, $form->{total_expenses_last_period}, $form->{decimalplaces}, "- ");
- }
+ }
- $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
- $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
- $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_income_this_period} = $form->format_amount($myconfig,$form->{total_income_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_expenses_this_period} = $form->format_amount($myconfig,$form->{total_expenses_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_this_period} = $form->format_amount($myconfig,$form->{total_this_period}, $form->{decimalplaces}, "- ");
}
sub balance_sheet {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
- my $last_period = 0;
- my @categories = qw(A L Q);
+ my $last_period = 0;
+ my @categories = qw(A L Q);
- my $null;
+ my $null;
- if ($form->{asofdate}) {
- if ($form->{asofyear} && $form->{asofmonth}) {
- if ($form->{asofdate} !~ /\W/) {
- $form->{asofdate} = "$form->{asofyear}$form->{asofmonth}$form->{asofdate}";
- }
- }
- } else {
- if ($form->{asofyear} && $form->{asofmonth}) {
- ($null, $form->{asofdate}) = $form->from_to($form->{asofyear}, $form->{asofmonth});
- }
- }
+ if ($form->{asofdate}) {
+ if ($form->{asofyear} && $form->{asofmonth}) {
+ if ($form->{asofdate} !~ /\W/) {
+ $form->{asofdate}
+ = "$form->{asofyear}$form->{asofmonth}$form->{asofdate}";
+ }
+ }
+ } else {
+ if ($form->{asofyear} && $form->{asofmonth}) {
+ ($null, $form->{asofdate})
+ = $form->from_to(
+ $form->{asofyear}, $form->{asofmonth});
+ }
+ }
- # if there are any dates construct a where
- if ($form->{asofdate}) {
+ # if there are any dates construct a where
+ if ($form->{asofdate}) {
- $form->{this_period} = "$form->{asofdate}";
- $form->{period} = "$form->{asofdate}";
+ $form->{this_period} = "$form->{asofdate}";
+ $form->{period} = "$form->{asofdate}";
- }
+ }
- $form->{decimalplaces} *= 1;
+ $form->{decimalplaces} *= 1;
- &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, ..hidden.., 1);
+ &get_accounts(
+ $dbh, $last_period, "", $form->{asofdate}, $form,
+ ..hidden.., 1);
- if ($form->{compareasofdate}) {
- if ($form->{compareasofyear} && $form->{compareasofmonth}) {
- if ($form->{compareasofdate} !~ /\W/) {
- $form->{compareasofdate} = "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}";
- }
- }
- } else {
- if ($form->{compareasofyear} && $form->{compareasofmonth}) {
- ($null, $form->{compareasofdate}) = $form->from_to($form->{compareasofyear}, $form->{compareasofmonth});
- }
- }
+ if ($form->{compareasofdate}) {
+ if ($form->{compareasofyear} && $form->{compareasofmonth}) {
+ if ($form->{compareasofdate} !~ /\W/) {
+ $form->{compareasofdate} = "$form->{compareasofyear}$form->{compareasofmonth}$form->{compareasofdate}";
+ }
+ }
+ } else {
+ if ($form->{compareasofyear} && $form->{compareasofmonth}) {
+ ($null, $form->{compareasofdate}) = $form->from_to(
+ $form->{compareasofyear},
+ $form->{compareasofmonth});
+ }
+ }
- # if there are any compare dates
- if ($form->{compareasofdate}) {
+ # if there are any compare dates
+ if ($form->{compareasofdate}) {
- $last_period = 1;
- &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, ..hidden.., 1);
+ $last_period = 1;
+ &get_accounts(
+ $dbh, $last_period, "", $form->{compareasofdate},
+ $form, ..hidden.., 1);
- $form->{last_period} = "$form->{compareasofdate}";
+ $form->{last_period} = "$form->{compareasofdate}";
- }
+ }
- # disconnect
- $dbh->disconnect;
+ $dbh->commit;
- # now we got $form->{A}{accno}{ } assets
- # and $form->{L}{accno}{ } liabilities
- # and $form->{Q}{accno}{ } equity
- # build asset accounts
+ # now we got $form->{A}{accno}{ } assets
+ # and $form->{L}{accno}{ } liabilities
+ # and $form->{Q}{accno}{ } equity
+ # build asset accounts
- my $str;
- my $key;
+ my $str;
+ my $key;
- my %account = ( 'A' => { 'label' => 'asset',
- 'labels' => 'assets',
- 'ml' => -1 },
- 'L' => { 'label' => 'liability',
- 'labels' => 'liabilities',
- 'ml' => 1 },
- 'Q' => { 'label' => 'equity',
- 'labels' => 'equity',
- 'ml' => 1 }
+ my %account = (
+ 'A' => {
+ 'label' => 'asset',
+ 'labels' => 'assets',
+ 'ml' => -1 },
+ 'L' => {
+ 'label' => 'liability',
+ 'labels' => 'liabilities',
+ 'ml' => 1 },
+ 'Q' => {
+ 'label' => 'equity',
+ 'labels' => 'equity',
+ 'ml' => 1 }
);
- foreach $category (@categories) {
+ foreach $category (@categories) {
- foreach $key (sort keys %{ $form->{$category} }) {
+ foreach $key (sort keys %{ $form->{$category} }) {
- $str = ($form->{l_heading}) ? $form->{padding} : "";
+ $str = ($form->{l_heading}) ? $form->{padding} : "";
- if ($form->{$category}{$key}{charttype} eq "A") {
- $str .= ($form->{l_accno}) ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}" : "$form->{$category}{$key}{description}";
- }
- if ($form->{$category}{$key}{charttype} eq "H") {
- if ($account{$category}{subtotal} && $form->{l_subtotal}) {
- $dash = "- ";
- push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
- push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ if ($form->{$category}{$key}{charttype} eq "A") {
+ $str .=
+ ($form->{l_accno})
+ ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"
+ : "$form->{$category}{$key}{description}";
+ }
+ if ($form->{$category}{$key}{charttype} eq "H") {
+ if ($account{$category}{subtotal}
+ && $form->{l_subtotal}) {
+
+ $dash = "- ";
+ push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
+ push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- if ($last_period) {
- push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
- }
+ if ($last_period) {
+ push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
+ }
- $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
+ $str = "$form->{bold}$form->{$category}{$key}{description}$form->{endbold}";
- $account{$category}{subthis} = $form->{$category}{$key}{this};
- $account{$category}{sublast} = $form->{$category}{$key}{last};
- $account{$category}{subdescription} = $form->{$category}{$key}{description};
- $account{$category}{subtotal} = 1;
+ $account{$category}{subthis} = $form->{$category}{$key}{this};
+ $account{$category}{sublast} = $form->{$category}{$key}{last};
+ $account{$category}{subdescription} = $form->{$category}{$key}{description};
+ $account{$category}{subtotal} = 1;
- $form->{$category}{$key}{this} = 0;
- $form->{$category}{$key}{last} = 0;
+ $form->{$category}{$key}{this} = 0;
+ $form->{$category}{$key}{last} = 0;
- next unless $form->{l_heading};
+ next unless $form->{l_heading};
- $dash = " ";
- }
+ $dash = " ";
+ }
- # push description onto array
- push(@{$form->{"$account{$category}{label}_account"}}, $str);
+ # push description onto array
+ push(@{$form->{"$account{$category}{label}_account"}},
+ $str);
- if ($form->{$category}{$key}{charttype} eq 'A') {
- $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
- $dash = "- ";
- }
+ if ($form->{$category}{$key}{charttype} eq 'A') {
+ $form->{"total_$account{$category}{labels}_this_period"} += $form->{$category}{$key}{this} * $account{$category}{ml};
+ $dash = "- ";
+ }
- push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{this} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- if ($last_period) {
- $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
+ if ($last_period) {
+ $form->{"total_$account{$category}{labels}_last_period"} += $form->{$category}{$key}{last} * $account{$category}{ml};
- push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
- }
+ push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $form->{$category}{$key}{last} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
+ }
- $str = ($form->{l_heading}) ? $form->{padding} : "";
- if ($account{$category}{subtotal} && $form->{l_subtotal}) {
- push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
- push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ $str = ($form->{l_heading}) ? $form->{padding} : "";
+ if ($account{$category}{subtotal} && $form->{l_subtotal}) {
+ push(@{$form->{"$account{$category}{label}_account"}}, "$str$form->{bold}$account{$category}{subdescription}$form->{endbold}");
+ push(@{$form->{"$account{$category}{label}_this_period"}}, $form->format_amount($myconfig, $account{$category}{subthis} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- if ($last_period) {
- push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
- }
- }
+ if ($last_period) {
+ push(@{$form->{"$account{$category}{label}_last_period"}}, $form->format_amount($myconfig, $account{$category}{sublast} * $account{$category}{ml}, $form->{decimalplaces}, $dash));
+ }
+ }
- }
+ }
- # totals for assets, liabilities
- $form->{total_assets_this_period} = $form->round_amount($form->{total_assets_this_period}, $form->{decimalplaces});
- $form->{total_liabilities_this_period} = $form->round_amount($form->{total_liabilities_this_period}, $form->{decimalplaces});
- $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period}, $form->{decimalplaces});
+ # totals for assets, liabilities
+ $form->{total_assets_this_period} = $form->round_amount(
+ $form->{total_assets_this_period}, $form->{decimalplaces});
+ $form->{total_liabilities_this_period} = $form->round_amount(
+ $form->{total_liabilities_this_period},
+ $form->{decimalplaces});
+ $form->{total_equity_this_period} = $form->round_amount(
+ $form->{total_equity_this_period}, $form->{decimalplaces});
- # calculate earnings
- $form->{earnings_this_period} = $form->{total_assets_this_period} - $form->{total_liabilities_this_period} - $form->{total_equity_this_period};
+ # calculate earnings
+ $form->{earnings_this_period} = $form->{total_assets_this_period}
+ - $form->{total_liabilities_this_period}
+ - $form->{total_equity_this_period};
- push(@{$form->{equity_this_period}}, $form->format_amount($myconfig, $form->{earnings_this_period}, $form->{decimalplaces}, "- "));
+ push(@{$form->{equity_this_period}},
+ $form->format_amount(
+ $myconfig, $form->{earnings_this_period},
+ $form->{decimalplaces}, "- "));
- $form->{total_equity_this_period} = $form->round_amount($form->{total_equity_this_period} + $form->{earnings_this_period}, $form->{decimalplaces});
+ $form->{total_equity_this_period} = $form->round_amount(
+ $form->{total_equity_this_period}
+ + $form->{earnings_this_period},
+ $form->{decimalplaces});
- # add liability + equity
- $form->{total_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period} + $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
+ # add liability + equity
+ $form->{total_this_period} = $form->format_amount(
+ $myconfig,
+ $form->{total_liabilities_this_period}
+ + $form->{total_equity_this_period},
+ $form->{decimalplaces}, "- ");
- if ($last_period) {
- # totals for assets, liabilities
- $form->{total_assets_last_period} = $form->round_amount($form->{total_assets_last_period}, $form->{decimalplaces});
- $form->{total_liabilities_last_period} = $form->round_amount($form->{total_liabilities_last_period}, $form->{decimalplaces});
- $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period}, $form->{decimalplaces});
+ if ($last_period) {
+ # totals for assets, liabilities
+ $form->{total_assets_last_period} = $form->round_amount(
+ $form->{total_assets_last_period},
+ $form->{decimalplaces});
+ $form->{total_liabilities_last_period} = $form->round_amount(
+ $form->{total_liabilities_last_period},
+ $form->{decimalplaces});
+ $form->{total_equity_last_period} = $form->round_amount(
+ $form->{total_equity_last_period},
+ $form->{decimalplaces});
- # calculate retained earnings
- $form->{earnings_last_period} = $form->{total_assets_last_period} - $form->{total_liabilities_last_period} - $form->{total_equity_last_period};
+ # calculate retained earnings
+ $form->{earnings_last_period}
+ = $form->{total_assets_last_period}
+ - $form->{total_liabilities_last_period}
+ - $form->{total_equity_last_period};
- push(@{$form->{equity_last_period}}, $form->format_amount($myconfig,$form->{earnings_last_period}, $form->{decimalplaces}, "- "));
+ push(@{$form->{equity_last_period}},
+ $form->format_amount(
+ $myconfig,$form->{earnings_last_period},
+ $form->{decimalplaces}, "- "));
- $form->{total_equity_last_period} = $form->round_amount($form->{total_equity_last_period} + $form->{earnings_last_period}, $form->{decimalplaces});
+ $form->{total_equity_last_period} = $form->round_amount(
+ $form->{total_equity_last_period}
+ + $form->{earnings_last_period},
+ $form->{decimalplaces});
- # add liability + equity
- $form->{total_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period} + $form->{total_equity_last_period}, $form->{decimalplaces}, "- ");
+ # add liability + equity
+ $form->{total_last_period} = $form->format_amount(
+ $myconfig,
+ $form->{total_liabilities_last_period}
+ + $form->{total_equity_last_period},
+ $form->{decimalplaces}, "- ");
- }
+ }
- $form->{total_liabilities_last_period} = $form->format_amount($myconfig, $form->{total_liabilities_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_liabilities_last_period});
+ $form->{total_liabilities_last_period} = $form->format_amount(
+ $myconfig, $form->{total_liabilities_last_period},
+ $form->{decimalplaces}, "- ")
+ if ($form->{total_liabilities_last_period});
- $form->{total_equity_last_period} = $form->format_amount($myconfig, $form->{total_equity_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_equity_last_period});
+ $form->{total_equity_last_period} = $form->format_amount(
+ $myconfig, $form->{total_equity_last_period},
+ $form->{decimalplaces}, "- ")
+ if ($form->{total_equity_last_period});
- $form->{total_assets_last_period} = $form->format_amount($myconfig, $form->{total_assets_last_period}, $form->{decimalplaces}, "- ") if ($form->{total_assets_last_period});
+ $form->{total_assets_last_period} = $form->format_amount(
+ $myconfig, $form->{total_assets_last_period},
+ $form->{decimalplaces}, "- ")
+ if ($form->{total_assets_last_period});
- $form->{total_assets_this_period} = $form->format_amount($myconfig, $form->{total_assets_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_assets_this_period} = $form->format_amount(
+ $myconfig, $form->{total_assets_this_period},
+ $form->{decimalplaces}, "- ");
- $form->{total_liabilities_this_period} = $form->format_amount($myconfig, $form->{total_liabilities_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_liabilities_this_period} = $form->format_amount(
+ $myconfig, $form->{total_liabilities_this_period},
+ $form->{decimalplaces}, "- ");
- $form->{total_equity_this_period} = $form->format_amount($myconfig, $form->{total_equity_this_period}, $form->{decimalplaces}, "- ");
+ $form->{total_equity_this_period} = $form->format_amount(
+ $myconfig, $form->{total_equity_this_period},
+ $form->{decimalplaces}, "- ");
}
sub get_accounts {
- my ($dbh, $last_period, $fromdate, $todate, $form, $categories, $excludeyearend) = @_;
+ my ($dbh, $last_period, $fromdate, $todate, $form, $categories,
+ $excludeyearend) = @_;
- my $department_id;
- my $project_id;
+ my $department_id;
+ my $project_id;
- ($null, $department_id) = split /--/, $form->{department};
- ($null, $project_id) = split /--/, $form->{projectnumber};
+ ($null, $department_id) = split /--/, $form->{department};
+ ($null, $project_id) = split /--/, $form->{projectnumber};
- my $query;
- my $dpt_where;
- my $dpt_join;
- my $project;
- my $where = "1 = 1";
- my $glwhere = "";
- my $subwhere = "";
- my $yearendwhere = "1 = 1";
- my $item;
+ my $query;
+ my $dpt_where;
+ my $dpt_join;
+ my $project;
+ my $where = "1 = 1";
+ my $glwhere = "";
+ my $subwhere = "";
+ my $yearendwhere = "1 = 1";
+ my $item;
- my $category = "AND (";
- foreach $item (@{ $categories }) {
- $category .= qq|c.category = '$item' OR |;
- }
- $category =~ s/OR $/\)/;
+ my $category = "AND (";
+ foreach $item (@{ $categories }) {
+ $category .= qq|c.category = |.$dbh->quote($item).qq| OR |;
+ }
+ $category =~ s/OR $/\)/;
- # get headings
- $query = qq|SELECT accno, description, category
- FROM chart c
- WHERE c.charttype = 'H'
- $category
- ORDER by c.accno|;
+ # get headings
+ $query = qq|
+ SELECT accno, description, category
+ FROM chart c
+ WHERE c.charttype = 'H' $category
+ ORDER BY c.accno|;
- if ($form->{accounttype} eq 'gifi')
- {
- $query = qq|SELECT g.accno, g.description, c.category
- FROM gifi g
- JOIN chart c ON (c.gifi_accno = g.accno)
- WHERE c.charttype = 'H'
- $category
+ if ($form->{accounttype} eq 'gifi'){
+ $query = qq|
+ SELECT g.accno, g.description, c.category
+ FROM gifi g
+ JOIN chart c ON (c.gifi_accno = g.accno)
+ WHERE c.charttype = 'H' $category
ORDER BY g.accno|;
- }
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- my @headingaccounts = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc))
- {
- $form->{$ref->{category}}{$ref->{accno}}{description} = "$ref->{description}";
- $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
- $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
+ my @headingaccounts = ();
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)){
+ $form->{$ref->{category}}{$ref->{accno}}{description}
+ = "$ref->{description}";
+
+ $form->{$ref->{category}}{$ref->{accno}}{charttype} = "H";
+ $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
- push @headingaccounts, $ref->{accno};
- }
+ push @headingaccounts, $ref->{accno};
+ }
- $sth->finish;
+ $sth->finish;
- if ($form->{method} eq 'cash' && !$todate) {
- ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
- }
+ if ($form->{method} eq 'cash' && !$todate) {
+ ($todate) = $dbh->selectrow_array(qq|SELECT current_date|);
+ }
- if ($fromdate) {
- if ($form->{method} eq 'cash') {
- $subwhere .= " AND transdate >= '$fromdate'";
- $glwhere = " AND ac.transdate >= '$fromdate'";
- } else {
- $where .= " AND ac.transdate >= '$fromdate'";
- }
- }
+ if ($fromdate) {
+ if ($form->{method} eq 'cash') {
+ $subwhere .= " AND transdate >= ".
+ $dbh->quote($fromdate);
+ $glwhere = " AND ac.transdate >= ".
+ $dbh->quote($fromdate);
+ } else {
+ $where .= " AND ac.transdate >= ".
+ $dbh->quote($fromdate);
+ }
+ }
- if ($todate) {
- $where .= " AND ac.transdate <= '$todate'";
- $subwhere .= " AND transdate <= '$todate'";
- $yearendwhere = "ac.transdate < '$todate'";
- }
+ if ($todate) {
+ $where .= " AND ac.transdate <= ".$dbh->quote($todate);
+ $subwhere .= " AND transdate <= ".$dbh->quote($todate);
+ $yearendwhere = "ac.transdate < ".$dbh->quote($todate);
+ }
- if ($excludeyearend) {
- $ywhere = " AND ac.trans_id NOT IN
- (SELECT trans_id FROM yearend)";
+ if ($excludeyearend) {
+ $ywhere = "
+ AND ac.trans_id NOT IN (SELECT trans_id FROM yearend)";
- if ($todate) {
- $ywhere = " AND ac.trans_id NOT IN
- (SELECT trans_id FROM yearend
- WHERE transdate <= '$todate')";
- }
+ if ($todate) {
+ $ywhere = "
+ AND ac.trans_id NOT IN
+ (SELECT trans_id FROM yearend
+ WHERE transdate <= ".dbh->quote($todate).")";
+ }
- if ($fromdate) {
- $ywhere = " AND ac.trans_id NOT IN
- (SELECT trans_id FROM yearend
- WHERE transdate >= '$fromdate')";
- if ($todate) {
- $ywhere = " AND ac.trans_id NOT IN
- (SELECT trans_id FROM yearend
- WHERE transdate >= '$fromdate'
- AND transdate <= '$todate')";
- }
- }
- }
+ if ($fromdate) {
+ $ywhere = "
+ AND ac.trans_id NOT IN
+ (SELECT trans_id FROM yearend
+ WHERE transdate >= ".$dbh->quote($fromdate).
+ ")";
+ if ($todate) {
+ $ywhere = "
+ AND ac.trans_id NOT IN
+ (SELECT trans_id FROM yearend
+ WHERE transdate >= "
+ .$dbh->quote($fromdate)."
+ AND transdate <= ".
+ $dbh->quote($todate).")";
+ }
+ }
+ }
- if ($department_id) {
- $dpt_join = qq|
- JOIN department t ON (a.department_id = t.id)
- |;
- $dpt_where = qq|
- AND t.id = $department_id
- |;
- }
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN department t ON (a.department_id = t.id)|;
+ $dpt_where = qq|
+ AND t.id = $department_id|;
+ }
- if ($project_id) {
- $project = qq|
- AND ac.project_id = $project_id
- |;
- }
+ if ($project_id) {
+ $project = qq|
+ AND ac.project_id = $project_id|;
+ }
- if ($form->{accounttype} eq 'gifi') {
+ if ($form->{accounttype} eq 'gifi') {
- if ($form->{method} eq 'cash') {
+ if ($form->{method} eq 'cash') {
- $query = qq|
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AR_paid%'
- $subwhere
- )
- $project
- GROUP BY g.accno, g.description, c.category
+ $query = qq|
+ SELECT g.accno, sum(ac.amount) AS amount,
+ g.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar a ON (a.id = ac.trans_id)
+ JOIN gifi g ON (g.accno = c.gifi_accno)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND ac.trans_id IN (
+ SELECT trans_id
+ FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AR_paid%'
+ $subwhere)
+ $project
+ GROUP BY g.accno, g.description, c.category
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND c.gifi_accno = ''
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AR_paid%'
- $subwhere
- )
- $project
- GROUP BY c.category
+ UNION ALL
- UNION ALL
+ SELECT '' AS accno, SUM(ac.amount) AS amount,
+ '' AS description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND c.gifi_accno = '' AND
+ ac.trans_id IN
+ (SELECT trans_id FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AR_paid%'
+ $subwhere) $project
+ GROUP BY c.category
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AP_paid%'
- $subwhere
- )
- $project
- GROUP BY g.accno, g.description, c.category
+ UNION ALL
+
+ SELECT g.accno, sum(ac.amount) AS amount,
+ g.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ JOIN gifi g ON (g.accno = c.gifi_accno)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND ac.trans_id IN
+ (SELECT trans_id FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AP_paid%'
+ $subwhere) $project
+ GROUP BY g.accno, g.description, c.category
- UNION ALL
+ UNION ALL
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND c.gifi_accno = ''
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AP_paid%'
- $subwhere
- )
- $project
- GROUP BY c.category
+ SELECT '' AS accno, SUM(ac.amount) AS amount,
+ '' AS description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ap a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND c.gifi_accno = ''
+ AND ac.trans_id IN
+ (SELECT trans_id FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AP_paid%' $subwhere)
+ $project
+ GROUP BY c.category
- UNION ALL
+ UNION ALL
--- add gl
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- JOIN gl a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $glwhere
- $dpt_where
- $category
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY g.accno, g.description, c.category
+ SELECT g.accno, sum(ac.amount) AS amount,
+ g.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gifi g ON (g.accno = c.gifi_accno)
+ JOIN gl a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where $ywhere $glwhere $dpt_where
+ $category AND NOT
+ (c.link = 'AR' OR c.link = 'AP')
+ $project
+ GROUP BY g.accno, g.description, c.category
- UNION ALL
-
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gl a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $glwhere
- $dpt_where
- $category
- AND c.gifi_accno = ''
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY c.category
- |;
+ UNION ALL
- if ($excludeyearend) {
+ SELECT '' AS accno, SUM(ac.amount) AS amount,
+ '' AS description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id)
+ $dpt_join
+ WHERE $where $ywhere $glwhere $dpt_where
+ $category AND c.gifi_accno = ''
+ AND NOT
+ (c.link = 'AR' OR c.link = 'AP')
+ $project
+ GROUP BY c.category|;
- # this is for the yearend
+ if ($excludeyearend) {
- $query .= qq|
- UNION ALL
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM yearend y
- JOIN gl a ON (a.id = y.trans_id)
- JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $yearendwhere
- AND c.category = 'Q'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
- }
+ $query .= qq|
- } else {
+ UNION ALL
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
- }
+ SELECT g.accno,
+ sum(ac.amount) AS amount,
+ g.description, c.category
+ FROM yearend y
+ JOIN gl a ON (a.id = y.trans_id)
+ JOIN acc_trans ac
+ ON (ac.trans_id = y.trans_id)
+ JOIN chart c
+ ON (c.id = ac.chart_id)
+ JOIN gifi g
+ ON (g.accno = c.gifi_accno)
+ $dpt_join
+ WHERE $yearendwhere
+ AND c.category = 'Q'
+ $dpt_where $project
+ GROUP BY g.accno, g.description,
+ c.category|;
+ }
- $query = qq|
-
- SELECT g.accno, SUM(ac.amount) AS amount,
- g.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- $project
- GROUP BY g.accno, g.description, c.category
+ } else {
+
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN dpt_trans t
+ ON (t.trans_id = ac.trans_id)|;
+ $dpt_where = qq|
+ AND t.department_id = |.
+ $dbh->quote($department_id);
+ }
+
+ $query = qq|
+ SELECT g.accno, SUM(ac.amount) AS amount,
+ g.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gifi g ON (c.gifi_accno = g.accno)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ $project
+ GROUP BY g.accno, g.description, c.category
- UNION ALL
+ UNION ALL
- SELECT '' AS accno, SUM(ac.amount) AS amount,
- '' AS description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND c.gifi_accno = ''
- $project
- GROUP BY c.category
- |;
+ SELECT '' AS accno, SUM(ac.amount) AS amount,
+ '' AS description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND c.gifi_accno = '' $project
+ GROUP BY c.category|;
- if ($excludeyearend) {
+ if ($excludeyearend) {
- # this is for the yearend
+ $query .= qq|
- $query .= qq|
+ UNION ALL
- UNION ALL
-
- SELECT g.accno, sum(ac.amount) AS amount,
- g.description, c.category
- FROM yearend y
- JOIN gl a ON (a.id = y.trans_id)
- JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (g.accno = c.gifi_accno)
- $dpt_join
- WHERE $yearendwhere
- AND c.category = 'Q'
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category
- |;
- }
- }
+ SELECT g.accno,
+ sum(ac.amount)
+ AS amount,
+ g.description,
+ c.category
+ FROM yearend y
+ JOIN gl a
+ ON (a.id = y.trans_id)
+ JOIN acc_trans ac
+ ON (ac.trans_id =
+ y.trans_id)
+ JOIN chart c
+ ON
+ (c.id = ac.chart_id)
+ JOIN gifi g
+ ON (g.accno =
+ c.gifi_accno)
+ $dpt_join
+ WHERE $yearendwhere
+ AND c.category = 'Q'
+ $dpt_where $project
+ GROUP BY g.accno,
+ g.description,
+ c.category|;
+ }
+ }
- } else { # standard account
+ } else { # standard account
- if ($form->{method} eq 'cash') {
+ if ($form->{method} eq 'cash') {
- $query = qq|
+ $query = qq|
+ SELECT c.accno, sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ar a ON (a.id = ac.trans_id) $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND ac.trans_id IN (
+ SELECT trans_id FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AR_paid%' $subwhere)
+ $project
+ GROUP BY c.accno, c.description, c.category
+
+ UNION ALL
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ar a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AR_paid%'
- $subwhere
- )
-
- $project
- GROUP BY c.accno, c.description, c.category
+ SELECT c.accno, sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN ap a ON (a.id = ac.trans_id) $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ AND ac.trans_id IN (
+ SELECT trans_id FROM acc_trans
+ JOIN chart ON (chart_id = id)
+ WHERE link LIKE '%AP_paid%' $subwhere)
+ $project
+ GROUP BY c.accno, c.description, c.category
- UNION ALL
-
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN ap a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- AND ac.trans_id IN
- (
- SELECT trans_id
- FROM acc_trans
- JOIN chart ON (chart_id = id)
- WHERE link LIKE '%AP_paid%'
- $subwhere
- )
-
- $project
- GROUP BY c.accno, c.description, c.category
-
- UNION ALL
+ UNION ALL
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gl a ON (a.id = ac.trans_id)
- $dpt_join
- WHERE $where
- $ywhere
- $glwhere
- $dpt_where
- $category
- AND NOT (c.link = 'AR' OR c.link = 'AP')
- $project
- GROUP BY c.accno, c.description, c.category
- |;
+ SELECT c.accno, sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gl a ON (a.id = ac.trans_id) $dpt_join
+ WHERE $where $ywhere $glwhere $dpt_where $category
+ AND NOT (c.link = 'AR' OR c.link = 'AP')
+ $project
+ GROUP BY c.accno, c.description, c.category|;
- if ($excludeyearend) {
+ if ($excludeyearend) {
# this is for the yearend
- $query .= qq|
+ $query .= qq|
- UNION ALL
-
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM yearend y
- JOIN gl a ON (a.id = y.trans_id)
- JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $yearendwhere
- AND c.category = 'Q'
- $dpt_where
- $project
- GROUP BY c.accno, c.description, c.category
- |;
- }
+ UNION ALL
- } else {
+ SELECT c.accno,
+ sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM yearend y
+ JOIN gl a ON (a.id = y.trans_id)
+ JOIN acc_trans ac
+ ON (ac.trans_id = y.trans_id)
+ JOIN chart c
+ ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $yearendwhere AND
+ c.category = 'Q' $dpt_where
+ $project
+ GROUP BY c.accno, c.description,
+ c.category|;
+ }
+
+ } else {
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (t.trans_id = ac.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
- }
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN dpt_trans t
+ ON (t.trans_id = ac.trans_id)|;
+ $dpt_where = qq| AND t.department_id = |.
+ $dbh->quote($department_id);
+ }
- $query = qq|
-
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $ywhere
- $dpt_where
- $category
- $project
- GROUP BY c.accno, c.description, c.category
- |;
+ $query = qq|
+ SELECT c.accno, sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $ywhere $dpt_where $category
+ $project
+ GROUP BY c.accno, c.description, c.category|;
- if ($excludeyearend) {
+ if ($excludeyearend) {
- # this is for the yearend
-
- $query .= qq|
+ $query .= qq|
- UNION ALL
+ UNION ALL
- SELECT c.accno, sum(ac.amount) AS amount,
- c.description, c.category
- FROM yearend y
- JOIN gl a ON (a.id = y.trans_id)
- JOIN acc_trans ac ON (ac.trans_id = y.trans_id)
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $yearendwhere
- AND c.category = 'Q'
- $dpt_where
- $project
- GROUP BY c.accno, c.description, c.category
- |;
- }
- }
- }
+ SELECT c.accno,
+ sum(ac.amount) AS amount,
+ c.description, c.category
+ FROM yearend y
+ JOIN gl a ON (a.id = y.trans_id)
+ JOIN acc_trans ac
+ ON (ac.trans_id = y.trans_id)
+ JOIN chart c
+ ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $yearendwhere AND
+ c.category = 'Q' $dpt_where
+ $project
+ GROUP BY c.accno, c.description,
+ c.category|;
+ }
+ }
+ }
- my @accno;
- my $accno;
- my $ref;
+ my @accno;
+ my $accno;
+ my $ref;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- # get last heading account
- @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
- $accno = pop @accno;
- if ($accno && ($accno ne $ref->{accno}) ) {
- if ($last_period)
- {
- $form->{$ref->{category}}{$accno}{last} += $ref->{amount};
- } else {
- $form->{$ref->{category}}{$accno}{this} += $ref->{amount};
- }
- }
+ # get last heading account
+ @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
+ $accno = pop @accno;
+ if ($accno && ($accno ne $ref->{accno}) ) {
+ if ($last_period) {
+ $form->{$ref->{category}}{$accno}{last}
+ += $ref->{amount};
+ } else {
+ $form->{$ref->{category}}{$accno}{this}
+ += $ref->{amount};
+ }
+ }
- $form->{$ref->{category}}{$ref->{accno}}{accno} = $ref->{accno};
- $form->{$ref->{category}}{$ref->{accno}}{description} = $ref->{description};
- $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
+ $form->{$ref->{category}}{$ref->{accno}}{accno}
+ = $ref->{accno};
+ $form->{$ref->{category}}{$ref->{accno}}{description}
+ = $ref->{description};
+ $form->{$ref->{category}}{$ref->{accno}}{charttype} = "A";
- if ($last_period) {
- $form->{$ref->{category}}{$ref->{accno}}{last} += $ref->{amount};
- } else {
- $form->{$ref->{category}}{$ref->{accno}}{this} += $ref->{amount};
- }
- }
- $sth->finish;
+ if ($last_period) {
+ $form->{$ref->{category}}{$ref->{accno}}{last}
+ += $ref->{amount};
+ } else {
+ $form->{$ref->{category}}{$ref->{accno}}{this}
+ += $ref->{amount};
+ }
+ }
+ $sth->finish;
- # remove accounts with zero balance
- foreach $category (@{ $categories }) {
- foreach $accno (keys %{ $form->{$category} }) {
- $form->{$category}{$accno}{last} = $form->round_amount($form->{$category}{$accno}{last}, $form->{decimalplaces});
- $form->{$category}{$accno}{this} = $form->round_amount($form->{$category}{$accno}{this}, $form->{decimalplaces});
+ # remove accounts with zero balance
+ foreach $category (@{ $categories }) {
+ foreach $accno (keys %{ $form->{$category} }) {
+ $form->{$category}{$accno}{last} = $form->round_amount(
+ $form->{$category}{$accno}{last},
+ $form->{decimalplaces});
+ $form->{$category}{$accno}{this} = $form->round_amount(
+ $form->{$category}{$accno}{this},
+ $form->{decimalplaces});
- delete $form->{$category}{$accno} if ($form->{$category}{$accno}{this} == 0 && $form->{$category}{$accno}{last} == 0);
- }
- }
+ delete $form->{$category}{$accno}
+ if ($form->{$category}{$accno}{this} == 0
+ && $form->{$category}{$accno}{last}
+ == 0);
+ }
+ }
}
sub trial_balance {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->{dbh};
- my ($query, $sth, $ref);
- my %balance = ();
- my %trb = ();
- my $null;
- my $department_id;
- my $project_id;
- my @headingaccounts = ();
- my $dpt_where;
- my $dpt_join;
- my $project;
+ my ($query, $sth, $ref);
+ my %balance = ();
+ my %trb = ();
+ my $null;
+ my $department_id;
+ my $project_id;
+ my @headingaccounts = ();
+ my $dpt_where;
+ my $dpt_join;
+ my $project;
- my $where = "1 = 1";
- my $invwhere = $where;
+ my $where = "1 = 1";
+ my $invwhere = $where;
- ($null, $department_id) = split /--/, $form->{department};
- ($null, $project_id) = split /--/, $form->{projectnumber};
+ ($null, $department_id) = split /--/, $form->{department};
+ ($null, $project_id) = split /--/, $form->{projectnumber};
- if ($department_id) {
- $dpt_join = qq|
- JOIN dpt_trans t ON (ac.trans_id = t.trans_id)
- |;
- $dpt_where = qq|
- AND t.department_id = $department_id
- |;
- }
+ if ($department_id) {
+ $dpt_join = qq|
+ JOIN dpt_trans t ON (ac.trans_id = t.trans_id)|;
+ $dpt_where = qq|
+ AND t.department_id = |.$dbh->quote($department_id);
+ }
- if ($project_id) {
- $project = qq|
- AND ac.project_id = $project_id
- |;
- }
+ if ($project_id) {
+ $project = qq|
+ AND ac.project_id = |.$dbh->quote($project_id);
+ }
- ($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};
- # get beginning balances
- if ($form->{fromdate}) {
+ # get beginning balances
+ if ($form->{fromdate}) {
- if ($form->{accounttype} eq 'gifi') {
+ if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount,
- g.description, c.contra
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE ac.transdate < '$form->{fromdate}'
- $dpt_where
- $project
- GROUP BY g.accno, c.category, g.description, c.contra
- |;
+ $query = qq|
+ SELECT g.accno, c.category,
+ SUM(ac.amount) AS amount,
+ g.description, c.contra
+ FROM acc_trans ac
+ JOIN chart c ON (ac.chart_id = c.id)
+ JOIN gifi g ON (c.gifi_accno = g.accno)
+ $dpt_join
+ WHERE ac.transdate < '$form->{fromdate}'
+ $dpt_where $project
+ GROUP BY g.accno, c.category, g.description,
+ c.contra|;
- } else {
+ } else {
- $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount,
- c.description, c.contra
- FROM acc_trans ac
- JOIN chart c ON (ac.chart_id = c.id)
- $dpt_join
- WHERE ac.transdate < '$form->{fromdate}'
- $dpt_where
- $project
- GROUP BY c.accno, c.category, c.description, c.contra
- |;
+ $query = qq|
+ SELECT c.accno, c.category,
+ SUM(ac.amount) AS amount,
+ c.description, c.contra
+ FROM acc_trans ac
+ JOIN chart c ON (ac.chart_id = c.id)
+ $dpt_join
+ WHERE ac.transdate < '$form->{fromdate}'
+ $dpt_where $project
+ GROUP BY c.accno, c.category, c.description,
+ c.contra|;
- }
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{amount} = $form->round_amount($ref->{amount}, 2);
- $balance{$ref->{accno}} = $ref->{amount};
+ while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $ref->{amount} = $form->round_amount($ref->{amount},
+ 2);
+ $balance{$ref->{accno}} = $ref->{amount};
- if ($form->{all_accounts}) {
- $trb{$ref->{accno}}{description} = $ref->{description};
- $trb{$ref->{accno}}{charttype} = 'A';
- $trb{$ref->{accno}}{category} = $ref->{category};
- $trb{$ref->{accno}}{contra} = $ref->{contra};
- }
+ if ($form->{all_accounts}) {
+ $trb{$ref->{accno}}{description}
+ = $ref->{description};
+ $trb{$ref->{accno}}{charttype}
+ = 'A';
+ $trb{$ref->{accno}}{category}
+ = $ref->{category};
+ $trb{$ref->{accno}}{contra}
+ = $ref->{contra};
+ }
- }
- $sth->finish;
+ }
+ $sth->finish;
- }
+ }
- # get headings
- $query = qq|SELECT c.accno, c.description, c.category
- FROM chart c
- WHERE c.charttype = 'H'
- ORDER by c.accno|;
+ # get headings
+ $query = qq|
+ SELECT c.accno, c.description, c.category FROM chart c
+ WHERE c.charttype = 'H'
+ ORDER by c.accno|;
- if ($form->{accounttype} eq 'gifi')
- {
- $query = qq|SELECT g.accno, g.description, c.category, c.contra
- FROM gifi g
- JOIN chart c ON (c.gifi_accno = g.accno)
- WHERE c.charttype = 'H'
- ORDER BY g.accno|;
- }
+ if ($form->{accounttype} eq 'gifi'){
+ $query = qq|
+ SELECT g.accno, g.description, c.category, c.contra
+ FROM gifi g
+ JOIN chart c ON (c.gifi_accno = g.accno)
+ WHERE c.charttype = 'H'
+ ORDER BY g.accno|;
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- while ($ref = $sth->fetchrow_hashref(NAME_lc))
- {
- $trb{$ref->{accno}}{description} = $ref->{description};
- $trb{$ref->{accno}}{charttype} = 'H';
- $trb{$ref->{accno}}{category} = $ref->{category};
- $trb{$ref->{accno}}{contra} = $ref->{contra};
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $trb{$ref->{accno}}{description} = $ref->{description};
+ $trb{$ref->{accno}}{charttype} = 'H';
+ $trb{$ref->{accno}}{category} = $ref->{category};
+ $trb{$ref->{accno}}{contra} = $ref->{contra};
- push @headingaccounts, $ref->{accno};
- }
+ push @headingaccounts, $ref->{accno};
+ }
- $sth->finish;
+ $sth->finish;
- if ($form->{fromdate} || $form->{todate}) {
- if ($form->{fromdate}) {
- $where .= " AND ac.transdate >= '$form->{fromdate}'";
- $invwhere .= " AND a.transdate >= '$form->{fromdate}'";
- }
- if ($form->{todate}) {
- $where .= " AND ac.transdate <= '$form->{todate}'";
- $invwhere .= " AND a.transdate <= '$form->{todate}'";
- }
- }
+ if ($form->{fromdate} || $form->{todate}) {
+ if ($form->{fromdate}) {
+ $where .= " AND ac.transdate >= "
+ .$dbh->quote($form->{fromdate});
+ $invwhere .= " AND a.transdate >= ".
+ $dbh->quote($form->{fromdate});
+ }
+ if ($form->{todate}) {
+ $where .= " AND ac.transdate <= ".
+ $dbh->quote($form->{todate});
+ $invwhere .= " AND a.transdate <= "
+ .$dbh->quote($form->{todate});
+ }
+ }
- if ($form->{accounttype} eq 'gifi') {
+ if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT g.accno, g.description, c.category,
- SUM(ac.amount) AS amount, c.contra
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- JOIN gifi g ON (c.gifi_accno = g.accno)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- GROUP BY g.accno, g.description, c.category, c.contra
- ORDER BY accno|;
+ $query = qq|
+ SELECT g.accno, g.description, c.category,
+ SUM(ac.amount) AS amount, c.contra
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ JOIN gifi g ON (c.gifi_accno = g.accno)
+ $dpt_join
+ WHERE $where $dpt_where $project
+ GROUP BY g.accno, g.description, c.category, c.contra
+ ORDER BY accno|;
- } else {
+ } else {
- $query = qq|SELECT c.accno, c.description, c.category,
- SUM(ac.amount) AS amount, c.contra
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- GROUP BY c.accno, c.description, c.category, c.contra
- ORDER BY accno|;
+ $query = qq|
+ SELECT c.accno, c.description, c.category,
+ SUM(ac.amount) AS amount, c.contra
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $dpt_where $project
+ GROUP BY c.accno, c.description, c.category, c.contra
+ ORDER BY accno|;
- }
+ }
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- # prepare query for each account
- $query = qq|SELECT (SELECT SUM(ac.amount) * -1
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount < 0
- AND c.accno = ?) AS debit,
-
- (SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount > 0
- AND c.accno = ?) AS credit
- |;
+ # prepare query for each account
+ $query = qq|
+ SELECT (SELECT SUM(ac.amount) * -1 FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $dpt_where $project AND ac.amount < 0
+ AND c.accno = ?) AS debit,
+ (SELECT SUM(ac.amount FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $dpt_where $project AND ac.amount > 0
+ AND c.accno = ?) AS credit |;
- if ($form->{accounttype} eq 'gifi') {
+ if ($form->{accounttype} eq 'gifi') {
- $query = qq|SELECT (SELECT SUM(ac.amount) * -1
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount < 0
- AND c.gifi_accno = ?) AS debit,
+ $query = qq|
+ SELECT (SELECT SUM(ac.amount) * -1
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $dpt_where $project AND ac.amount < 0
+ AND c.gifi_accno = ?) AS debit,
- (SELECT SUM(ac.amount)
- FROM acc_trans ac
- JOIN chart c ON (c.id = ac.chart_id)
- $dpt_join
- WHERE $where
- $dpt_where
- $project
- AND ac.amount > 0
- AND c.gifi_accno = ?) AS credit|;
+ (SELECT SUM(ac.amount)
+ FROM acc_trans ac
+ JOIN chart c ON (c.id = ac.chart_id)
+ $dpt_join
+ WHERE $where $dpt_where $project AND ac.amount > 0
+ AND c.gifi_accno = ?) AS credit|;
- }
+ }
- $drcr = $dbh->prepare($query);
+ $drcr = $dbh->prepare($query);
- # calculate debit and credit for the period
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $trb{$ref->{accno}}{description} = $ref->{description};
- $trb{$ref->{accno}}{charttype} = 'A';
- $trb{$ref->{accno}}{category} = $ref->{category};
- $trb{$ref->{accno}}{contra} = $ref->{contra};
- $trb{$ref->{accno}}{amount} += $ref->{amount};
- }
- $sth->finish;
+ # calculate debit and credit for the period
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ $trb{$ref->{accno}}{description} = $ref->{description};
+ $trb{$ref->{accno}}{charttype} = 'A';
+ $trb{$ref->{accno}}{category} = $ref->{category};
+ $trb{$ref->{accno}}{contra} = $ref->{contra};
+ $trb{$ref->{accno}}{amount} += $ref->{amount};
+ }
+ $sth->finish;
- my ($debit, $credit);
+ my ($debit, $credit);
- foreach my $accno (sort keys %trb) {
- $ref = ();
+ foreach my $accno (sort keys %trb) {
+ $ref = ();
- $ref->{accno} = $accno;
- for (qw(description category contra charttype amount)) { $ref->{$_} = $trb{$accno}{$_} }
+ $ref->{accno} = $accno;
+ for (qw(description category contra charttype amount)) {
+ $ref->{$_} = $trb{$accno}{$_};
+ }
- $ref->{balance} = $balance{$ref->{accno}};
+ $ref->{balance} = $balance{$ref->{accno}};
- if ($trb{$accno}{charttype} eq 'A') {
- if ($project_id) {
+ if ($trb{$accno}{charttype} eq 'A') {
+ if ($project_id) {
- if ($ref->{amount} < 0) {
- $ref->{debit} = $ref->{amount} * -1;
- } else {
- $ref->{credit} = $ref->{amount};
- }
- next if $form->round_amount($ref->{amount}, 2) == 0;
+ if ($ref->{amount} < 0) {
+ $ref->{debit} = $ref->{amount} * -1;
+ } else {
+ $ref->{credit} = $ref->{amount};
+ }
+ next if $form->round_amount(
+ $ref->{amount}, 2) == 0;
- } else {
+ } else {
- # get DR/CR
- $drcr->execute($ref->{accno}, $ref->{accno});
+ # get DR/CR
+ $drcr->execute($ref->{accno}, $ref->{accno});
- ($debit, $credit) = (0,0);
- while (($debit, $credit) = $drcr->fetchrow_array) {
- $ref->{debit} += $debit;
- $ref->{credit} += $credit;
- }
- $drcr->finish;
+ ($debit, $credit) = (0,0);
+ while (($debit, $credit)
+ = $drcr->fetchrow_array) {
+ $ref->{debit} += $debit;
+ $ref->{credit} += $credit;
+ }
+ $drcr->finish;
- }
+ }
- $ref->{debit} = $form->round_amount($ref->{debit}, 2);
- $ref->{credit} = $form->round_amount($ref->{credit}, 2);
+ $ref->{debit} = $form->round_amount($ref->{debit}, 2);
+ $ref->{credit}
+ = $form->round_amount($ref->{credit}, 2);
- if (!$form->{all_accounts}) {
- next if $form->round_amount($ref->{debit} + $ref->{credit}, 2) == 0;
- }
- }
+ if (!$form->{all_accounts}) {
+ next
+ if $form->round_amount(
+ $ref->{debit} + $ref->{credit},
+ 2)
+ == 0;
+ }
+ }
- # add subtotal
- @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
- $accno = pop @accno;
- if ($accno) {
- $trb{$accno}{debit} += $ref->{debit};
- $trb{$accno}{credit} += $ref->{credit};
- }
+ # add subtotal
+ @accno = grep { $_ le "$ref->{accno}" } @headingaccounts;
+ $accno = pop @accno;
+ if ($accno) {
+ $trb{$accno}{debit} += $ref->{debit};
+ $trb{$accno}{credit} += $ref->{credit};
+ }
- push @{ $form->{TB} }, $ref;
+ push @{ $form->{TB} }, $ref;
- }
+ }
- $dbh->disconnect;
+ $dbh->commit;
- # debits and credits for headings
- foreach $accno (@headingaccounts) {
- foreach $ref (@{ $form->{TB} }) {
- if ($accno eq $ref->{accno}) {
- $ref->{debit} = $trb{$accno}{debit};
- $ref->{credit} = $trb{$accno}{credit};
- }
- }
- }
+ # debits and credits for headings
+ foreach $accno (@headingaccounts) {
+ foreach $ref (@{ $form->{TB} }) {
+ if ($accno eq $ref->{accno}) {
+ $ref->{debit} = $trb{$accno}{debit};
+ $ref->{credit} = $trb{$accno}{credit};
+ }
+ }
+ }
}
sub aging {
- my ($self, $myconfig, $form) = @_;
+ my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
- my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
+ my $dbh = $form->{dbh};
+ my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir';
- my $query = qq|SELECT value FROM defaults WHERE settings_key = 'curr'|;
- ($form->{currencies}) = $dbh->selectrow_array($query);
+ my $query = qq|SELECT value FROM defaults WHERE settings_key = 'curr'|;
+ ($form->{currencies}) = $dbh->selectrow_array($query);
- ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month};
+ ($null, $form->{todate})
+ = $form->from_to($form->{year}, $form->{month})
+ if $form->{year} && $form->{month};
- if (! $form->{todate}) {
- $query = qq|SELECT current_date|;
- ($form->{todate}) = $dbh->selectrow_array($query);
- }
+ if (! $form->{todate}) {
+ $query = qq|SELECT current_date|;
+ ($form->{todate}) = $dbh->selectrow_array($query);
+ }
- my $where = "1 = 1";
- my $name;
- my $null;
- my $ref;
- my $transdate = ($form->{overdue}) ? "duedate" : "transdate";
+ my $where = "1 = 1";
+ my $name;
+ my $null;
+ my $ref;
+ my $transdate = ($form->{overdue}) ? "duedate" : "transdate";
- if ($form->{"$form->{ct}_id"}) {
- $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|;
- } else {
- if ($form->{$form->{ct}} ne "") {
- $name = $form->like(lc $form->{$form->{ct}});
- $where .= qq| AND lower(ct.name) LIKE '$name'| if $form->{$form->{ct}};
- }
- }
+ if ($form->{"$form->{ct}_id"}) {
+ $where .= qq| AND ct.id = |.
+ $dbh->quote($form->{"$form->{ct}_id"});
+ } else {
+ if ($form->{$form->{ct}} ne "") {
+ $name = $dbh->quote($form->like(
+ lc $form->{$form->{ct}}));
+ $where .= qq| AND lower(ct.name) LIKE $name|
+ if $form->{$form->{ct}};
+ }
+ }
- if ($form->{department}) {
- ($null, $department_id) = split /--/, $form->{department};
- $where .= qq| AND a.department_id = $department_id|;
- }
+ if ($form->{department}) {
+ ($null, $department_id) = split /--/, $form->{department};
+ $where .= qq| AND a.department_id = |.
+ $dbh->quote($department_id);
+ }
- # select outstanding vendors or customers, depends on $ct
- $query = qq|SELECT DISTINCT ct.id, ct.name, ct.language_code
- FROM $form->{ct} ct
- JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id)
- WHERE $where
- AND a.paid != a.amount
- AND (a.$transdate <= '$form->{todate}')
- ORDER BY ct.name|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror;
+ # select outstanding vendors or customers, depends on $ct
+ $query = qq|
+ SELECT DISTINCT ct.id, ct.name, ct.language_code
+ FROM $form->{ct} ct
+ JOIN $form->{arap} a ON (a.$form->{ct}_id = ct.id)
+ WHERE $where AND a.paid != a.amount
+ AND (a.$transdate <= ?)
+ ORDER BY ct.name|;
+ my $sth = $dbh->prepare($query);
+ $sth->execute($form->{todate}) || $form->dberror;
- my @ot = ();
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @ot, $ref;
- }
- $sth->finish;
+ my @ot = ();
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @ot, $ref;
+ }
+ $sth->finish;
- my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
+ my $buysell = ($form->{arap} eq 'ar') ? 'buy' : 'sell';
- my %interval = ( 'Pg' => {
- 'c0' => "(date '$form->{todate}' - interval '0 days')",
- 'c30' => "(date '$form->{todate}' - interval '30 days')",
- 'c60' => "(date '$form->{todate}' - interval '60 days')",
- 'c90' => "(date '$form->{todate}' - interval '90 days')" },
- 'DB2' => {
- 'c0' => "(date ('$form->{todate}') - 0 days)",
- 'c30' => "(date ('$form->{todate}') - 30 days)",
- 'c60' => "(date ('$form->{todate}') - 60 days)",
- 'c90' => "(date ('$form->{todate}') - 90 days)" }
+ my $todate = $dbh->quote($form->{todate});
+ my %interval = (
+ 'c0' => "(date $todate - interval '0 days')",
+ 'c30' => "(date $todate - interval '30 days')",
+ 'c60' => "(date $todate - interval '60 days')",
+ 'c90' => "(date $todate - interval '90 days')"
);
- $interval{Oracle} = $interval{PgPP} = $interval{Pg};
- # for each company that has some stuff outstanding
- $form->{currencies} ||= ":";
+ # for each company that has some stuff outstanding
+ $form->{currencies} ||= ":";
- $where = qq|
- a.paid != a.amount
- AND c.id = ?
- AND a.curr = ?|;
+ $where = qq|a.paid != a.amount AND c.id = ? AND a.curr = ?|;
- if ($department_id) {
- $where .= qq| AND a.department_id = $department_id|;
- }
+ if ($department_id) {
+ $where .= qq| AND a.department_id = |.
+ $dbh->quote($department_id);
+ }
- $query = "";
- my $union = "";
+ $query = "";
+ my $union = "";
- if ($form->{c0}) {
- $query .= qq|
- SELECT c.id AS ctid, c.$form->{ct}number, c.name,
- c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
- c.contact, c.email,
- c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
- c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
- a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
- (a.amount - a.paid) as c0, 0.00 as c30, 0.00 as c60, 0.00 as c90,
- a.duedate, a.invoice, a.id, a.curr,
- (SELECT $buysell FROM exchangerate e
- WHERE a.curr = e.curr
- AND e.transdate = a.transdate) AS exchangerate
- FROM $form->{arap} a
- JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
- WHERE $where
- AND (
- a.$transdate <= $interval{$myconfig->{dbdriver}}{c0}
- AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c30}
- )
-|;
+ if ($form->{c0}) {
+ $query .= qq|
+ SELECT c.id AS ctid, c.$form->{ct}number, c.name,
+ c.address1, c.address2, c.city, c.state,
+ c.zipcode, c.country, c.contact, c.email,
+ c.phone as $form->{ct}phone,
+ c.fax as $form->{ct}fax,
+ c.$form->{ct}number,
+ c.taxnumber as $form->{ct}taxnumber,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes, (a.amount - a.paid) as c0,
+ 0.00 as c30, 0.00 as c60, 0.00 as c90,
+ a.duedate, a.invoice, a.id, a.curr,
+ (SELECT $buysell FROM exchangerate e
+ WHERE a.curr = e.curr
+ AND e.transdate = a.transdate)
+ AS exchangerate
+ FROM $form->{arap} a
+ JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
+ WHERE $where AND ( a.$transdate <= $interval{c0}
+ AND a.$transdate >= $interval{c30} )|;
- $union = qq|
- UNION
-|;
+ $union = qq|UNION|;
- }
+ }
- if ($form->{c30}) {
+ if ($form->{c30}) {
- $query .= qq|
+ $query .= qq|
- $union
+ $union
- SELECT c.id AS ctid, c.$form->{ct}number, c.name,
- c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
- c.contact, c.email,
- c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
- c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
- a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
- 0.00 as c0, (a.amount - a.paid) as c30, 0.00 as c60, 0.00 as c90,
- a.duedate, a.invoice, a.id, a.curr,
- (SELECT $buysell FROM exchangerate e
- WHERE a.curr = e.curr
- AND e.transdate = a.transdate) AS exchangerate
- FROM $form->{arap} a
- JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
- WHERE $where
- AND (
- a.$transdate < $interval{$myconfig->{dbdriver}}{c30}
- AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c60}
- )
-|;
+ SELECT c.id AS ctid, c.$form->{ct}number, c.name,
+ c.address1, c.address2, c.city, c.state,
+ c.zipcode, c.country, c.contact, c.email,
+ c.phone as $form->{ct}phone,
+ c.fax as $form->{ct}fax, c.$form->{ct}number,
+ c.taxnumber as $form->{ct}taxnumber,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes, 0.00 as c0,
+ (a.amount - a.paid) as c30, 0.00 as c60,
+ 0.00 as c90, a.duedate, a.invoice, a.id, a.curr,
+ (SELECT $buysell FROM exchangerate e
+ WHERE a.curr = e.curr
+ AND e.transdate = a.transdate)
+ AS exchangerate
+ FROM $form->{arap} a
+ JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
+ WHERE $where AND (a.$transdate < $interval{c30}
+ AND a.$transdate >= $interval{c60})|;
- $union = qq|
- UNION
-|;
+ $union = qq|UNION|;
- }
+ }
- if ($form->{c60}) {
+ if ($form->{c60}) {
- $query .= qq|
-
- $union
+ $query .= qq|
+ $union
- SELECT c.id AS ctid, c.$form->{ct}number, c.name,
- c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
- c.contact, c.email,
- c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
- c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
- a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
- 0.00 as c0, 0.00 as c30, (a.amount - a.paid) as c60, 0.00 as c90,
- a.duedate, a.invoice, a.id, a.curr,
- (SELECT $buysell FROM exchangerate e
- WHERE a.curr = e.curr
- AND e.transdate = a.transdate) AS exchangerate
- FROM $form->{arap} a
- JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
- WHERE $where
- AND (
- a.$transdate < $interval{$myconfig->{dbdriver}}{c60}
- AND a.$transdate >= $interval{$myconfig->{dbdriver}}{c90}
- )
-|;
+ SELECT c.id AS ctid, c.$form->{ct}number, c.name,
+ c.address1, c.address2, c.city, c.state,
+ c.zipcode, c.country, c.contact, c.email,
+ c.phone as $form->{ct}phone,
+ c.fax as $form->{ct}fax, c.$form->{ct}number,
+ c.taxnumber as $form->{ct}taxnumber,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes, 0.00 as c0, 0.00 as c30,
+ (a.amount - a.paid) as c60, 0.00 as c90,
+ a.duedate, a.invoice, a.id, a.curr,
+ (SELECT $buysell FROM exchangerate e
+ WHERE a.curr = e.curr
+ AND e.transdate = a.transdate)
+ AS exchangerate
+ FROM $form->{arap} a
+ JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
+ WHERE $where AND (a.$transdate < $interval{c60}
+ AND a.$transdate >= $interval{c90})|;
- $union = qq|
- UNION
-|;
+ $union = qq|UNION|;
- }
+ }
- if ($form->{c90}) {
+ if ($form->{c90}) {
- $query .= qq|
+ $query .= qq|
+ $union
+ SELECT c.id AS ctid, c.$form->{ct}number, c.name,
+ c.address1, c.address2, c.city, c.state,
+ c.zipcode, c.country, c.contact, c.email,
+ c.phone as $form->{ct}phone,
+ c.fax as $form->{ct}fax, c.$form->{ct}number,
+ c.taxnumber as $form->{ct}taxnumber,
+ a.invnumber, a.transdate, a.till, a.ordnumber,
+ a.ponumber, a.notes, 0.00 as c0, 0.00 as c30,
+ 0.00 as c60, (a.amount - a.paid) as c90,
+ a.duedate, a.invoice, a.id, a.curr,
+ (SELECT $buysell FROM exchangerate e
+ WHERE a.curr = e.curr
+ AND e.transdate = a.transdate)
+ AS exchangerate
+ FROM $form->{arap} a
+ JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
+ WHERE $where
+ AND a.$transdate < $interval{c90}|;
+ }
+ $query .= qq| ORDER BY ctid, $transdate, invnumber|;
+ $sth = $dbh->prepare($query) || $form->dberror($query);
- $union
-
- SELECT c.id AS ctid, c.$form->{ct}number, c.name,
- c.address1, c.address2, c.city, c.state, c.zipcode, c.country,
- c.contact, c.email,
- c.phone as $form->{ct}phone, c.fax as $form->{ct}fax,
- c.$form->{ct}number, c.taxnumber as $form->{ct}taxnumber,
- a.invnumber, a.transdate, a.till, a.ordnumber, a.ponumber, a.notes,
- 0.00 as c0, 0.00 as c30, 0.00 as c60, (a.amount - a.paid) as c90,
- a.duedate, a.invoice, a.id, a.curr,
- (SELECT $buysell FROM exchangerate e
- WHERE a.curr = e.curr
- AND e.transdate = a.transdate) AS exchangerate
- FROM $form->{arap} a
- JOIN $form->{ct} c ON (a.$form->{ct}_id = c.id)
- WHERE $where
- AND a.$transdate < $interval{$myconfig->{dbdriver}}{c90}
-|;
- }
-
- $query .= qq|
-
- ORDER BY ctid, $transdate, invnumber|;
-
- $sth = $dbh->prepare($query) || $form->dberror($query);
-
- my @var = ();
+ my @var = ();
- if ($form->{c0} + $form->{c30} + $form->{c60} + $form->{c90}) {
- foreach $curr (split /:/, $form->{currencies}) {
+ if ($form->{c0} + $form->{c30} + $form->{c60} + $form->{c90}) {
+ foreach $curr (split /:/, $form->{currencies}) {
- foreach $item (@ot) {
+ foreach $item (@ot) {
- @var = ();
- for (qw(c0 c30 c60 c90)) { push @var, ($item->{id}, $curr) if $form->{$_} }
+ @var = ();
+ for (qw(c0 c30 c60 c90)) {
+ push @var, ($item->{id}, $curr)
+ if $form->{$_} }
- $sth->execute(@var);
+ $sth->execute(@var);
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{module} = ($ref->{invoice}) ? $invoice : $form->{arap};
- $ref->{module} = 'ps' if $ref->{till};
- $ref->{exchangerate} = 1 unless $ref->{exchangerate};
- $ref->{language_code} = $item->{language_code};
- push @{ $form->{AG} }, $ref;
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)){
+ $ref->{module} =
+ ($ref->{invoice})
+ ? $invoice
+ : $form->{arap};
+ $ref->{module} = 'ps' if $ref->{till};
+ $ref->{exchangerate} = 1
+ unless $ref->{exchangerate};
+ $ref->{language_code}
+ = $item->{language_code};
+ push @{ $form->{AG} }, $ref;
+ }
+ $sth->finish;
+
+ }
+ }
}
- $sth->finish;
- }
- }
- }
+ # get language
+ my $query = qq|SELECT * FROM language ORDER BY 2|;
+ $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
- # get language
- my $query = qq|SELECT *
- FROM language
- ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
+ while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ push @{ $form->{all_language} }, $ref;
+ }
+ $sth->finish;
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{all_language} }, $ref;
- }
- $sth->finish;
+ $dbh->commit;
- # disconnect
- $dbh->disconnect;
-
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.