X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FRP.pm;h=791b22bbab90a1e5fde454345b44d0d72a4490a4;hp=3f07bb52512bffb92fb3810273fc369c2b0c8ce5;hb=32306b5f8ffe4ce594409aa6e89626740b225a39;hpb=52072fcd26f2faf57923f598c358e7f47c4e2643 diff --git a/sql-ledger/SL/RP.pm b/sql-ledger/SL/RP.pm index 3f07bb525..791b22bba 100644 --- a/sql-ledger/SL/RP.pm +++ b/sql-ledger/SL/RP.pm @@ -1,12 +1,13 @@ #===================================================================== # SQL-Ledger Accounting -# Copyright (C) 1998-2002 +# Copyright (C) 2001 # # Author: Dieter Simader # Email: dsimader@sql-ledger.org # Web: http://www.sql-ledger.org # # Contributors: Benjamin Lee +# Jim Rawlings # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -29,6 +30,82 @@ package RP; +sub yearend_statement { + my ($self, $myconfig, $form) = @_; + + # connect to database + 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; + + $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); + + foreach $id (@trans_id) { + $sth->execute($id); + $ath->execute($id); + } + $sth->finish; + + + my $last_period = 0; + my @categories = qw(I E); + my $category; + + $form->{decimalplaces} *= 1; + + &get_accounts($dbh, 0, $form->{fromdate}, $form->{todate}, $form, \@categories); + + # disconnect + $dbh->disconnect; + + + # 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 } + ); + + 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}); + + # 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) = @_; @@ -41,13 +118,25 @@ sub income_statement { $form->{decimalplaces} *= 1; - &get_accounts($dbh, $last_period, $form->{fromdate}, $form->{todate}, $form, \@categories); + 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, \@categories, 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 there are any compare dates if ($form->{comparefromdate} || $form->{comparetodate}) { $last_period = 1; - &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories); + &get_accounts($dbh, $last_period, $form->{comparefromdate}, $form->{comparetodate}, $form, \@categories, 1); } @@ -134,7 +223,7 @@ sub income_statement { } - + # 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}); @@ -160,7 +249,6 @@ sub income_statement { } - sub balance_sheet { my ($self, $myconfig, $form) = @_; @@ -168,8 +256,16 @@ sub balance_sheet { my $dbh = $form->dbconnect($myconfig); my $last_period = 0; - my @categories = qw(A L Q); + my @categories = qw(A C L Q); + my $null; + + if (! $form->{asofdate}) { + 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}) { @@ -180,13 +276,19 @@ sub balance_sheet { $form->{decimalplaces} *= 1; - &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories); + &get_accounts($dbh, $last_period, "", $form->{asofdate}, $form, \@categories, 1); + + if (! $form->{compareasofdate}) { + if ($form->{compareasofyear} && $form->{compareasofmonth}) { + ($null, $form->{compareasofdate}) = $form->from_to($form->{compareasofyear}, $form->{compareasofmonth}); + } + } # if there are any compare dates if ($form->{compareasofdate}) { $last_period = 1; - &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories); + &get_accounts($dbh, $last_period, "", $form->{compareasofdate}, $form, \@categories, 1); $form->{last_period} = "$form->{compareasofdate}"; @@ -212,11 +314,11 @@ sub balance_sheet { 'labels' => 'liabilities', 'ml' => 1 }, 'Q' => { 'label' => 'equity', - 'labels' => 'equities', + 'labels' => 'equity', 'ml' => 1 } ); - foreach $category (@categories) { + foreach $category (grep { !/C/ } @categories) { foreach $key (sort keys %{ $form->{$category} }) { @@ -284,9 +386,9 @@ sub balance_sheet { # 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 retained earnings + # 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}, "- ")); @@ -301,7 +403,7 @@ sub balance_sheet { # 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}; @@ -327,17 +429,28 @@ sub balance_sheet { $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}, "- "); - -} +} sub get_accounts { - my ($dbh, $last_period, $fromdate, $todate, $form, $categories) = @_; + my ($dbh, $last_period, $fromdate, $todate, $form, $categories, $yearend) = @_; + my $department_id; + my $project_id; + + ($null, $department_id) = split /--/, $form->{department}; + ($null, $project_id) = split /--/, $form->{projectnumber}; + my $query; - my $where = "WHERE 1 = 1"; - my $subwhere; + my $dpt_where; + my $dpt_join; + my $project; + my $where = "1 = 1"; + my $glwhere = ""; + my $projectwhere = ""; + my $subwhere = ""; + my $yearendwhere = "1 = 1"; my $item; my $category = "AND ("; @@ -380,18 +493,59 @@ sub get_accounts { $sth->finish; - $where .= " AND ac.transdate >= '$fromdate'" if $fromdate; + if ($fromdate) { + $where .= " AND ac.transdate >= '$fromdate'"; + $projectwhere .= " AND transdate >= '$fromdate'"; + if ($form->{method} eq 'cash') { + $subwhere .= " AND transdate >= '$fromdate'"; + $glwhere = " AND ac.transdate >= '$fromdate'"; + } + } if ($todate) { $where .= " AND ac.transdate <= '$todate'"; - $subwhere = " AND transdate <= '$todate'"; + $projectwhere .= " AND transdate <= '$todate'"; + $subwhere .= " AND transdate <= '$todate'"; + $yearendwhere = "ac.transdate < '$todate'"; + } + + if ($yearend) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend)"; + + 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 ($todate) { + $ywhere = " AND ac.trans_id NOT IN + (SELECT trans_id FROM yearend + WHERE transdate <= '$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 ($form->{project_id}) + if ($project_id) { $project = qq| - AND ac.project_id = $form->{project_id} + AND ac.project_id = $project_id |; } @@ -410,7 +564,10 @@ sub get_accounts { 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND ac.trans_id IN ( @@ -423,14 +580,17 @@ sub get_accounts { $project GROUP BY g.accno, g.description, c.category - UNION + 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND c.gifi_accno = '' AND ac.trans_id IN @@ -444,7 +604,7 @@ sub get_accounts { $project GROUP BY c.category - UNION + UNION ALL SELECT g.accno, sum(ac.amount) AS amount, g.description, c.category @@ -452,7 +612,10 @@ sub get_accounts { 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND ac.trans_id IN ( @@ -465,14 +628,17 @@ sub get_accounts { $project GROUP BY g.accno, g.description, c.category - UNION + 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND c.gifi_accno = '' AND ac.trans_id IN @@ -486,7 +652,7 @@ sub get_accounts { $project GROUP BY c.category - UNION + UNION ALL -- add gl @@ -496,20 +662,28 @@ sub get_accounts { 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) - $where + $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 + 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) - $where + $dpt_join + WHERE $where + $ywhere + $glwhere + $dpt_where $category AND c.gifi_accno = '' AND NOT (c.link = 'AR' OR c.link = 'AP') @@ -517,8 +691,126 @@ sub get_accounts { GROUP BY c.category |; + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM yearend y + 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.accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + + if ($project_id) { + + $query .= qq| + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + 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 g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + 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 AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (g.accno = c.gifi_accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + 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 + |; + } + } 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 + |; + + } + $query = qq| SELECT g.accno, SUM(ac.amount) AS amount, @@ -526,32 +818,121 @@ sub get_accounts { FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) JOIN gifi g ON (c.gifi_accno = g.accno) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_from $category $project GROUP BY g.accno, g.description, c.category - UNION + 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_from $category AND c.gifi_accno = '' $project - GROUP by c.category + GROUP BY c.category |; - + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT g.accno, sum(ac.amount) AS amount, + g.description, c.category + FROM yearend y + 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.accno) + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + + if ($project_id) + { + + $query .= qq| + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + SELECT g.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + g.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + |; + } + } - } else { + } else { # standard account if ($form->{method} eq 'cash') { - $query = qq| SELECT c.accno, sum(ac.amount) AS amount, @@ -559,7 +940,10 @@ sub get_accounts { FROM acc_trans ac JOIN chart c ON (c.id = ac.chart_id) JOIN ar a ON (a.id = ac.trans_id) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND ac.trans_id IN ( @@ -573,14 +957,17 @@ sub get_accounts { $project GROUP BY c.accno, c.description, c.category - UNION + 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) - $where + $dpt_join + WHERE $where + $ywhere + $dpt_where $category AND ac.trans_id IN ( @@ -594,57 +981,262 @@ sub get_accounts { $project GROUP BY c.accno, c.description, c.category - UNION + 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) - $where + $dpt_join + WHERE $where + $ywhere + $glwhere + $dpt_from $category AND NOT (c.link = 'AR' OR c.link = 'AP') $project GROUP BY c.accno, c.description, c.category |; - - } else { - - $query = qq| - - SELECT c.accno, sum(ac.amount) AS amount, + + if ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, c.description, c.category - FROM acc_trans ac + FROM yearend y + JOIN acc_trans ac ON (ac.trans_id = y.trans_id) JOIN chart c ON (c.id = ac.chart_id) - $where - $category + $dpt_join + WHERE $yearendwhere + AND c.category = 'Q' + $dpt_where $project GROUP BY c.accno, c.description, c.category |; + } - } - - } + + if ($project_id) + { + $query .= qq| + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%AR_paid%' + $subwhere + ) - my @accno; - my $accno; - my $ref; - - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $project + GROUP BY c.accno, c.description, c.category - while ($ref = $sth->fetchrow_hashref(NAME_lc)) - { + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + 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 AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + 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 + |; + } + + } 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 + |; + } + + + $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 ($yearend) { + + # this is for the yearend + + $query .= qq| + + UNION ALL + + SELECT c.accno, sum(ac.amount) AS amount, + c.description, c.category + FROM yearend y + 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 + |; + } + + + if ($project_id) + { + + $query .= qq| + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ar a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.income_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + $ywhere + AND c.category = 'I' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.qty) AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + SELECT c.accno AS accno, SUM(ac.sellprice * ac.allocated) * -1 AS amount, + c.description AS description, c.category + FROM invoice ac + JOIN ap a ON (a.id = ac.trans_id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c on (p.expense_accno_id = c.id) + $dpt_join + WHERE 1 = 1 $projectwhere + AND ac.assemblyitem = '0' + $ywhere + AND c.category = 'E' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + |; + + } + } + } + + my @accno; + my $accno; + my $ref; + + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) + { if ($ref->{category} eq 'C') { $ref->{category} = 'A'; } - + # get last heading account @accno = grep { $_ le "$ref->{accno}" } @headingaccounts; $accno = pop @accno; - if ($accno) { + if ($accno && ($accno ne $ref->{accno}) ) { if ($last_period) { $form->{$ref->{category}}{$accno}{last} += $ref->{amount}; @@ -681,7 +1273,7 @@ sub get_accounts { -sub trial_balance_details { +sub trial_balance { my ($self, $myconfig, $form) = @_; my $dbh = $form->dbconnect($myconfig); @@ -689,40 +1281,72 @@ sub trial_balance_details { 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 = "WHERE 1 = 1"; + my $where = "1 = 1"; + my $invwhere = $where; + + ($null, $department_id) = split /--/, $form->{department}; + ($null, $project_id) = split /--/, $form->{projectnumber}; - if ($form->{project_id}) { - $where .= qq| - AND a.project_id = $form->{project_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 = $department_id |; } + + # project_id only applies to getting transactions + # it has nothing to do with a trial balance + # but we use the same function to collect information + + if ($project_id) { + $project = qq| + AND ac.project_id = $project_id + |; + } + + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + # get beginning balances if ($form->{fromdate}) { if ($form->{accounttype} eq 'gifi') { - $query = qq|SELECT g.accno, c.category, SUM(a.amount) AS amount, + $query = qq|SELECT g.accno, c.category, SUM(ac.amount) AS amount, g.description - FROM acc_trans a - JOIN chart c ON (a.chart_id = c.id) + FROM acc_trans ac + JOIN chart c ON (ac.chart_id = c.id) JOIN gifi g ON (c.gifi_accno = g.accno) - $where - AND a.transdate < '$form->{fromdate}' + $dpt_join + WHERE ac.transdate < '$form->{fromdate}' + $dpt_where + $project GROUP BY g.accno, c.category, g.description |; } else { - $query = qq|SELECT c.accno, c.category, SUM(a.amount) AS amount, + $query = qq|SELECT c.accno, c.category, SUM(ac.amount) AS amount, c.description - FROM acc_trans a - JOIN chart c ON (a.chart_id = c.id) - $where - AND a.transdate < '$form->{fromdate}' + 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 |; + } $sth = $dbh->prepare($query); @@ -761,7 +1385,6 @@ sub trial_balance_details { $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - my @headingaccounts = (); while ($ref = $sth->fetchrow_hashref(NAME_lc)) { $trb{$ref->{accno}}{description} = $ref->{description}; @@ -776,10 +1399,12 @@ sub trial_balance_details { if ($form->{fromdate} || $form->{todate}) { if ($form->{fromdate}) { - $where .= " AND a.transdate >= '$form->{fromdate}'"; + $where .= " AND ac.transdate >= '$form->{fromdate}'"; + $invwhere .= " AND a.transdate >= '$form->{fromdate}'"; } if ($form->{todate}) { - $where .= " AND a.transdate <= '$form->{todate}'"; + $where .= " AND ac.transdate <= '$form->{todate}'"; + $invwhere .= " AND a.transdate <= '$form->{todate}'"; } } @@ -787,82 +1412,214 @@ sub trial_balance_details { if ($form->{accounttype} eq 'gifi') { $query = qq|SELECT g.accno, g.description, c.category, - SUM(a.amount) AS amount - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) + SUM(ac.amount) AS amount + FROM acc_trans ac + JOIN chart c ON (c.id = ac.chart_id) JOIN gifi g ON (c.gifi_accno = g.accno) - $where + $dpt_join + WHERE $where + $dpt_where + $project GROUP BY g.accno, g.description, c.category - - UNION + |; + + if ($project_id) { - SELECT '' AS accno, '' AS description, c.category, - SUM(a.amount) AS amount - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where - AND c.gifi_accno = '' - GROUP BY c.category + $query .= qq| + + -- sold items + + UNION ALL + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ar a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.income_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + UNION ALL + + -- bought services + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + -- COGS + + UNION ALL + + SELECT g.accno, g.description, c.category, + SUM(ac.sellprice * ac.allocated) * -1 AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + $dpt_join + WHERE $invwhere + AND ac.assemblyitem = '0' + $dpt_where + $project + GROUP BY g.accno, g.description, c.category + + |; + } + + $query .= qq| ORDER BY accno|; } else { $query = qq|SELECT c.accno, c.description, c.category, - SUM(a.amount) AS amount - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where + SUM(ac.amount) AS amount + 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 + |; + + if ($project_id) { + + $query .= qq| + + -- sold items + + UNION ALL + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ar a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.income_accno_id = c.id) + $dpt_join + WHERE $invwhere + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + UNION ALL + + -- bought services + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.qty) AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + $dpt_join + WHERE $invwhere + AND p.inventory_accno_id IS NULL + AND p.assembly = '0' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + -- cogs + + UNION ALL + + SELECT c.accno, c.description, c.category, + SUM(ac.sellprice * ac.allocated) * -1 AS amount + FROM invoice ac + JOIN ap a ON (ac.trans_id = a.id) + JOIN parts p ON (ac.parts_id = p.id) + JOIN chart c ON (p.expense_accno_id = c.id) + $dpt_join + WHERE $invwhere + AND ac.assemblyitem = '0' + $dpt_where + $project + GROUP BY c.accno, c.description, c.category + + |; + } + + $query .= qq| ORDER BY accno|; } - + $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - # prepare query for each account - - $query = qq|SELECT (SELECT SUM(a.amount) * -1 - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where - AND a.amount < 0 + $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(a.amount) - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where - AND a.amount > 0 + + (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') { - $query = qq|SELECT (SELECT SUM(a.amount) * -1 - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where - AND a.amount < 0 + $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(a.amount) - FROM acc_trans a - JOIN chart c ON (c.id = a.chart_id) - $where - AND a.amount > 0 + + (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); - # calculate the debit and credit in the period + $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}}{amount} += $ref->{amount}; - } $sth->finish; @@ -870,29 +1627,41 @@ sub trial_balance_details { foreach my $accno (sort keys %trb) { $ref = (); - + $ref->{accno} = $accno; map { $ref->{$_} = $trb{$accno}{$_} } qw(description category charttype amount); $ref->{balance} = $form->round_amount($balance{$ref->{accno}}, 2); if ($trb{$accno}{charttype} eq 'A') { - # get DR/CR - $drcr->execute($ref->{accno}, $ref->{accno}) || $form->dberror($query); - - ($debit, $credit) = (0,0); - while (($debit, $credit) = $drcr->fetchrow_array) { - $ref->{debit} += $debit; - $ref->{credit} += $credit; + 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; + + } else { + + # 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; + } - $drcr->finish; $ref->{debit} = $form->round_amount($ref->{debit}, 2); $ref->{credit} = $form->round_amount($ref->{credit}, 2); } - # add subtotal @accno = grep { $_ le "$ref->{accno}" } @headingaccounts; $accno = pop @accno; @@ -900,7 +1669,7 @@ sub trial_balance_details { $trb{$accno}{debit} += $ref->{debit}; $trb{$accno}{credit} += $ref->{credit}; } - + push @{ $form->{TB} }, $ref; } @@ -920,7 +1689,6 @@ sub trial_balance_details { } - sub aging { my ($self, $myconfig, $form) = @_; @@ -928,10 +1696,15 @@ sub aging { my $dbh = $form->dbconnect($myconfig); my $invoice = ($form->{arap} eq 'ar') ? 'is' : 'ir'; + ($null, $form->{todate}) = $form->from_to($form->{year}, $form->{month}) if $form->{year} && $form->{month}; + $form->{todate} = $form->current_date($myconfig) unless ($form->{todate}); + my $where = "1 = 1"; my $name; + my $null; + my $ref; if ($form->{"$form->{ct}_id"}) { $where .= qq| AND ct.id = $form->{"$form->{ct}_id"}|; @@ -942,11 +1715,22 @@ sub aging { } } + my $dpt_join; + if ($form->{department}) { + ($null, $department_id) = split /--/, $form->{department}; + $dpt_join = qq| + JOIN department d ON (a.department_id = d.id) + |; + + $where .= qq| AND a.department_id = $department_id|; + } + # select outstanding vendors or customers, depends on $ct - my $query = qq|SELECT DISTINCT ct.id, ct.name - FROM $form->{ct} ct, $form->{arap} a + my $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) + $dpt_join WHERE $where - AND a.$form->{ct}_id = ct.id AND a.paid != a.amount AND (a.transdate <= '$form->{todate}') ORDER BY ct.name|; @@ -955,115 +1739,140 @@ sub aging { $sth->execute || $form->dberror; 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)" } + ); + + $interval{Oracle} = $interval{PgPP} = $interval{Pg}; + # for each company that has some stuff outstanding - while ( my ($id) = $sth->fetchrow_array ) { + my $id; + while (($id, $null, $language_code) = $sth->fetchrow_array ) { $query = qq| - --- between 0-30 days - - SELECT $form->{ct}.id AS ctid, $form->{ct}.name, - addr1, addr2, addr3, addr4, contact, - phone as customerphone, fax as customerfax, $form->{ct}number, - "invnumber", "transdate", - (amount - paid) as "c0", 0.00 as "c30", 0.00 as "c60", 0.00 as "c90", - "duedate", invoice, $form->{arap}.id, - (SELECT $buysell FROM exchangerate - WHERE $form->{arap}.curr = exchangerate.curr - AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate - FROM $form->{arap}, $form->{ct} - WHERE paid != amount - AND $form->{arap}.$form->{ct}_id = $form->{ct}.id - AND $form->{ct}.id = $id + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, 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, + (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 a.paid != a.amount + AND c.id = $id AND ( - transdate <= (date '$form->{todate}' - interval '0 days') - AND transdate >= (date '$form->{todate}' - interval '30 days') + a.transdate <= $interval{$myconfig->{dbdriver}}{c0} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c30} ) UNION --- between 31-60 days - - SELECT $form->{ct}.id AS ctid, $form->{ct}.name, - addr1, addr2, addr3, addr4, contact, - phone as customerphone, fax as customerfax, $form->{ct}number, - "invnumber", "transdate", - 0.00 as "c0", (amount - paid) as "c30", 0.00 as "c60", 0.00 as "c90", - "duedate", invoice, $form->{arap}.id, - (SELECT $buysell FROM exchangerate - WHERE $form->{arap}.curr = exchangerate.curr - AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate - FROM $form->{arap}, $form->{ct} - WHERE paid != amount - AND $form->{arap}.$form->{ct}_id = $form->{ct}.id - AND $form->{ct}.id = $id + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, 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, + (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 a.paid != a.amount + AND c.id = $id AND ( - transdate < (date '$form->{todate}' - interval '30 days') - AND transdate >= (date '$form->{todate}' - interval '60 days') + a.transdate < $interval{$myconfig->{dbdriver}}{c30} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c60} ) UNION --- between 61-90 days - - SELECT $form->{ct}.id AS ctid, $form->{ct}.name, - addr1, addr2, addr3, addr4, contact, - phone as customerphone, fax as customerfax, $form->{ct}number, - "invnumber", "transdate", - 0.00 as "c0", 0.00 as "c30", (amount - paid) as "c60", 0.00 as "c90", - "duedate", invoice, $form->{arap}.id, - (SELECT $buysell FROM exchangerate - WHERE $form->{arap}.curr = exchangerate.curr - AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate - FROM $form->{arap}, $form->{ct} - WHERE paid != amount - AND $form->{arap}.$form->{ct}_id = $form->{ct}.id - AND $form->{ct}.id = $id + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, 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, + (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 a.paid != a.amount + AND c.id = $id AND ( - transdate < (date '$form->{todate}' - interval '60 days') - AND transdate >= (date '$form->{todate}' - interval '90 days') + a.transdate < $interval{$myconfig->{dbdriver}}{c60} + AND a.transdate >= $interval{$myconfig->{dbdriver}}{c90} ) UNION --- over 90 days - - SELECT $form->{ct}.id AS ctid, $form->{ct}.name, - addr1, addr2, addr3, addr4, contact, - phone as customerphone, fax as customerfax, $form->{ct}number, - "invnumber", "transdate", - 0.00 as "c0", 0.00 as "c30", 0.00 as "c60", (amount - paid) as "c90", - "duedate", invoice, $form->{arap}.id, - (SELECT $buysell FROM exchangerate - WHERE $form->{arap}.curr = exchangerate.curr - AND exchangerate.transdate = $form->{arap}.transdate) AS exchangerate - FROM $form->{arap}, $form->{ct} - WHERE paid != amount - AND $form->{arap}.$form->{ct}_id = $form->{ct}.id - AND $form->{ct}.id = $id - AND transdate < (date '$form->{todate}' - interval '90 days') + SELECT c.id AS ctid, c.name, + c.address1, c.address2, c.city, c.state, c.zipcode, c.country, + c.contact, c.email, + c.phone as customerphone, c.fax as customerfax, c.$form->{ct}number, + a.invnumber, a.transdate, a.till, a.ordnumber, 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, + (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 a.paid != a.amount + AND c.id = $id + AND a.transdate < $interval{$myconfig->{dbdriver}}{c90} ORDER BY - ctid, invnumber, transdate + ctid, transdate, invnumber |; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + 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} = $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); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } $sth->finish; + # disconnect $dbh->disconnect; @@ -1079,11 +1888,8 @@ sub get_customer { my $query = qq|SELECT name, email, cc, bcc FROM $form->{ct} ct WHERE ct.id = $form->{"$form->{ct}_id"}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror; - - ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $sth->fetchrow_array; - $sth->finish; + ($form->{$form->{ct}}, $form->{email}, $form->{cc}, $form->{bcc}) = $dbh->selectrow_array($query); + $dbh->disconnect; } @@ -1096,29 +1902,34 @@ sub get_taxaccounts { my $dbh = $form->dbconnect($myconfig); # get tax accounts - my $query = qq|SELECT accno, description - FROM chart - WHERE link LIKE '%CT_tax%' - ORDER BY accno|; + my $query = qq|SELECT c.accno, c.description, t.rate, c.link + FROM chart c, tax t + WHERE c.link LIKE '%CT_tax%' + AND c.id = t.chart_id + ORDER BY c.accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; - while ( my ($accno, $description) = $sth->fetchrow_array ) { - push @{ $form->{taxaccounts} }, "$accno--$description"; + my $ref = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{taxaccounts} }, $ref; } $sth->finish; # get gifi tax accounts - my $query = qq|SELECT DISTINCT ON (g.accno) g.accno, g.description - FROM gifi g, chart c + my $query = qq|SELECT DISTINCT g.accno, g.description, + sum(t.rate) AS rate + FROM gifi g, chart c, tax t WHERE g.accno = c.gifi_accno + AND c.id = t.chart_id AND c.link LIKE '%CT_tax%' + GROUP BY g.accno, g.description ORDER BY accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror; - while ( my ($accno, $description) = $sth->fetchrow_array ) { - push @{ $form->{gifi_taxaccounts} }, "$accno--$description"; + while ($ref = $sth->fetchrow_hashref(NAME_lc) ) { + push @{ $form->{gifi_taxaccounts} }, $ref; } $sth->finish; @@ -1134,34 +1945,52 @@ sub tax_report { # connect to database my $dbh = $form->dbconnect($myconfig); + my ($null, $department_id) = split /--/, $form->{department}; + # build WHERE - my $where = qq|WHERE ac.trans_id = a.id - AND ac.chart_id = ch.id|; - + my $where = "1 = 1"; + my $cashwhere = ""; - if ($form->{accno} =~ /^gifi_/) { - my ($null, $accno) = split /_/, $form->{accno}; - $where .= qq| AND ch.gifi_accno = '$accno'|; - } else { - $where .= qq| AND ch.accno = '$form->{accno}'|; + if ($department_id) { + $where .= qq| + AND a.department_id = $department_id + |; } + + my $query; + my $sth; + my $accno; + my $rate; + + if ($form->{accno}) { + if ($form->{accno} =~ /^gifi_/) { + ($null, $accno) = split /_/, $form->{accno}; + $rate = $form->{"$form->{accno}_rate"}; + $accno = qq| AND ch.gifi_accno = '$accno'|; + } else { + $accno = $form->{accno}; + $rate = $form->{"$form->{accno}_rate"}; + $accno = qq| AND ch.accno = '$accno'|; + } + } + $rate *= 1; my $table; + my $ARAP; if ($form->{db} eq 'ar') { - $where .= " AND n.id = a.customer_id"; $table = "customer"; + $ARAP = "AR"; } if ($form->{db} eq 'ap') { - $where .= " AND n.id = a.vendor_id"; $table = "vendor"; + $ARAP = "AP"; } - my $transdate = ($form->{cashbased}) ? "a.datepaid" : "ac.transdate"; - if ($form->{cashbased}) { - $where .= " AND a.amount = a.paid"; - } + my $transdate = "a.transdate"; + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; + # if there are any dates construct a where if ($form->{fromdate} || $form->{todate}) { if ($form->{fromdate}) { @@ -1171,29 +2000,372 @@ sub tax_report { $where .= " AND $transdate <= '$form->{todate}'"; } } + + + if ($form->{method} eq 'cash') { + $transdate = "a.datepaid"; + + my $todate = ($form->{todate}) ? $form->{todate} : $form->current_date($myconfig); + + $cashwhere = qq| + AND ac.trans_id IN + ( + SELECT trans_id + FROM acc_trans + JOIN chart ON (chart_id = id) + WHERE link LIKE '%${ARAP}_paid%' + AND $transdate <= '$todate' + AND a.paid = a.amount + ) + |; + + } + + + my $ml = ($form->{db} eq 'ar') ? 1 : -1; - my $query = qq|SELECT a.id, a.invoice, $transdate AS transdate, a.invnumber, - n.name, a.netamount,|; - my $sortorder = join ', ', $form->sort_columns(qw(transdate invnumber name)); - $sortorder = $form->{sort} unless $sortorder; + my %ordinal = ( 'transdate' => 3, + 'invnumber' => 4, + 'name' => 5 + ); - if ($form->{db} eq 'ar') { - $query .= " ac.amount AS tax"; - } - if ($form->{db} eq 'ap') { - $query .= " ac.amount * -1 AS tax"; + my @a = qw(transdate invnumber name); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + $rate = 1 unless $rate; + + if ($form->{summary}) { + + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + sum(ac.sellprice * ac.qty) * $rate * $ml AS tax, + a.till + FROM invoice ac + JOIN partstax pt ON (pt.parts_id = ac.parts_id) + JOIN chart ch ON (ch.id = pt.chart_id) + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + WHERE $where + $accno + AND a.invoice = '1' + $cashwhere + GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name, + a.till + |; + + if ($form->{fromdate}) { + # include open transactions from previous period + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + sum(ac.sellprice * ac.qty) * $rate * $ml AS tax, + a.till + FROM invoice ac + JOIN partstax pt ON (pt.parts_id = ac.parts_id) + JOIN chart ch ON (ch.id = pt.chart_id) + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '1' + $cashwhere + GROUP BY a.id, a.invoice, $transdate, a.invnumber, n.name, + a.till + |; + } + } + + + } else { + + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + i.sellprice * i.qty * $ml AS netamount, + i.sellprice * i.qty * $rate * $ml AS tax, + i.description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + JOIN invoice i ON (i.trans_id = a.id) + JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id) + WHERE $where + $accno + AND a.invoice = '1' + $cashwhere + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + ac.amount * $ml AS tax, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '0' + $cashwhere + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + i.sellprice * i.qty * $ml AS netamount, + i.sellprice * i.qty * $rate * $ml AS tax, + i.description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN chart ch ON (ch.id = ac.chart_id) + JOIN $table n ON (n.id = a.${table}_id) + JOIN ${table}tax t ON (t.${table}_id = n.id AND t.chart_id = ch.id) + JOIN invoice i ON (i.trans_id = a.id) + JOIN partstax pt ON (pt.parts_id = i.parts_id AND pt.chart_id = ch.id) + WHERE a.datepaid >= '$form->{fromdate}' + $accno + AND a.invoice = '1' + $cashwhere + |; + } + } + } + + + if ($form->{report} =~ /nontaxable/) { + + if ($form->{summary}) { + # only gather up non-taxable transactions + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, a.till + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, a.till + |; + } + } + + } else { + + # gather up details for non-taxable transactions + $query = qq|SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.notes, a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + ac.description, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE $where + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, + ac.description, a.till + |; + + if ($form->{fromdate}) { + if ($cashwhere) { + $query .= qq| + UNION + + SELECT a.id, '0' AS invoice, $transdate AS transdate, + a.invnumber, n.name, a.netamount, + a.notes AS description, a.till + FROM acc_trans ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '0' + AND a.netamount = a.amount + $cashwhere + GROUP BY a.id, $transdate, a.invnumber, n.name, a.netamount, + a.notes, a.till + + UNION + + SELECT a.id, '1' AS invoice, $transdate AS transdate, + a.invnumber, n.name, + sum(ac.sellprice * ac.qty) * $ml AS netamount, + ac.description, a.till + FROM invoice ac + JOIN $form->{db} a ON (a.id = ac.trans_id) + JOIN $table n ON (n.id = a.${table}_id) + WHERE a.datepaid >= '$form->{fromdate}' + AND a.invoice = '1' + AND ( + a.${table}_id NOT IN ( + SELECT ${table}_id FROM ${table}tax t (${table}_id) + ) OR + ac.parts_id NOT IN ( + SELECT parts_id FROM partstax p (parts_id) + ) + ) + $cashwhere + GROUP BY a.id, a.invnumber, $transdate, n.name, + ac.description, a.till + |; + } + } + + } } + $query .= qq| - FROM acc_trans ac, "$form->{db}" a, "$table" n, chart ch - $where - ORDER by $sortorder|; + ORDER by $sortorder|; - my $sth = $dbh->prepare($query); + $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while ( my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{TR} }, $ref; + $ref->{tax} = $form->round_amount($ref->{tax}, 2); + push @{ $form->{TR} }, $ref if $ref->{netamount} != 0; } $sth->finish; @@ -1208,21 +2380,23 @@ sub paymentaccounts { # connect to database, turn AutoCommit off my $dbh = $form->dbconnect_noauto($myconfig); - my $arap = uc $form->{db}; - $arap .= "_paid"; + my $ARAP = uc $form->{db}; # get A(R|P)_paid accounts my $query = qq|SELECT accno, description FROM chart - WHERE link LIKE '%$arap%'|; + WHERE link LIKE '%${ARAP}_paid%' + ORDER BY accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{PR} }, $ref; } - $sth->finish; + + $form->all_years($dbh, $myconfig); + $dbh->disconnect; } @@ -1242,9 +2416,62 @@ sub payments { if ($form->{db} eq 'ap') { $table = 'vendor'; } + + + my $query; + my $sth; + my $dpt_join; + my $where; + my $var; + + if ($form->{department_id}) { + $dpt_join = qq| + JOIN dpt_trans t ON (t.trans_id = ac.trans_id) + |; + + $where = qq| + AND t.department_id = $form->{department_id} + |; + } + + ($form->{fromdate}, $form->{todate}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; - my ($query, $sth); + if ($form->{fromdate}) { + $where .= " AND ac.transdate >= '$form->{fromdate}'"; + } + if ($form->{todate}) { + $where .= " AND ac.transdate <= '$form->{todate}'"; + } + if (!$form->{fx_transaction}) { + $where .= " AND ac.fx_transaction = '0'"; + } + + if ($form->{description}) { + $var = $form->like(lc $form->{description}); + $where .= " AND lower(c.name) LIKE '$var'"; + } + if ($form->{source}) { + $var = $form->like(lc $form->{source}); + $where .= " AND lower(ac.source) LIKE '$var'"; + } + if ($form->{memo}) { + $var = $form->like(lc $form->{memo}); + $where .= " AND lower(ac.memo) LIKE '$var'"; + } + + my %ordinal = ( 'name' => 1, + 'transdate' => 2, + 'source' => 4, + 'employee' => 6, + 'till' => 7 + ); + + my @a = qw(name transdate employee); + my $sortorder = $form->sort_order(\@a, \%ordinal); + my $glwhere = $where; + $glwhere =~ s/\(c.name\)/\(g.description\)/; + # cycle through each id foreach my $accno (split(/ /, $form->{paymentaccounts})) { @@ -1258,37 +2485,51 @@ sub payments { push @{ $form->{PR} }, $ref; $sth->finish; - $query = qq|SELECT c.name, a.invnumber, a.ordnumber, - ac.transdate, - ac.amount * $ml AS paid, ac.source, a.invoice, a.id, - '$form->{db}' AS module - FROM $table c, acc_trans ac, $form->{db} a - WHERE c.id = a.${table}_id - AND ac.trans_id = a.id - AND ac.chart_id = $ref->{id}|; - - $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate}; - $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate}; + $query = qq|SELECT c.name, ac.transdate, sum(ac.amount) * $ml AS paid, + ac.source, ac.memo, e.name AS employee, a.till, a.curr + FROM acc_trans ac + JOIN $form->{db} a ON (ac.trans_id = a.id) + JOIN $table c ON (c.id = a.${table}_id) + LEFT JOIN employee e ON (a.employee_id = e.id) + $dpt_join + WHERE ac.chart_id = $ref->{id} + $where|; + + if ($form->{till}) { + $query .= " AND a.invoice = '1' + AND NOT a.till IS NULL"; + + if ($myconfig->{role} eq 'user') { + $query .= " AND e.login = '$form->{login}'"; + } + } $query .= qq| + GROUP BY c.name, ac.transdate, ac.source, ac.memo, + e.name, a.till, a.curr + |; + + if (! $form->{till}) { +# don't need gl for a till + + $query .= qq| UNION - SELECT g.description, g.reference, NULL AS ordnumber, - ac.transdate, - ac.amount * $ml AS paid, ac.source, '0' as invoice, g.id, - 'gl' AS module - FROM gl g, acc_trans ac - WHERE g.id = ac.trans_id - AND ac.chart_id = $ref->{id} - AND (ac.amount * $ml) > 0 + SELECT g.description, ac.transdate, sum(ac.amount) * $ml AS paid, ac.source, + ac.memo, e.name AS employee, '' AS till, '' AS curr + FROM acc_trans ac + JOIN gl g ON (g.id = ac.trans_id) + LEFT JOIN employee e ON (g.employee_id = e.id) + $dpt_join + WHERE ac.chart_id = $ref->{id} + $glwhere + AND (ac.amount * $ml) > 0 + GROUP BY g.description, ac.transdate, ac.source, ac.memo, e.name |; - $query .= " AND ac.transdate >= '$form->{fromdate}'" if $form->{fromdate}; - $query .= " AND ac.transdate <= '$form->{todate}'" if $form->{todate}; - + } - my $sortorder = join ', ', $form->sort_columns(qw(name invnumber ordnumber transdate source)); - - $query .= " ORDER BY $sortorder"; + $query .= qq| + ORDER BY $sortorder|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query);