X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCA.pm;h=76d2b320a5580cb202d3f9beb4be9ee2a27d411a;hb=d3897394b6547332743ef7a0c984d391f872373c;hp=dfe67837d7a9aeb413df4e1e5ec3adc30b09cf4f;hpb=4dbb09950c9f5596646537c12d991c99086fe7c1;p=kivitendo-erp.git diff --git a/SL/CA.pm b/SL/CA.pm index dfe67837d..76d2b320a 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -34,59 +34,80 @@ # #====================================================================== - package CA; use Data::Dumper; +use SL::DBUtils; sub all_accounts { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $chart_id) = @_; + + my %amount; - my $amount = (); # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT c.accno, - SUM(a.amount) AS amount - FROM chart c, acc_trans a - WHERE c.id = a.chart_id - GROUP BY c.accno|; + my $query = + qq|SELECT c.accno, SUM(a.amount) AS amount | . + qq|FROM chart c, acc_trans a | . + qq|WHERE c.id = a.chart_id | . + qq|GROUP BY c.accno|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $amount{$ref->{accno}} = $ref->{amount} + $amount{ $ref->{accno} } = $ref->{amount}; } $sth->finish; - - $query = qq|SELECT accno, description - FROM gifi|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my $gifi = (); - while (my ($accno, $description) = $sth->fetchrow_array) { - $gifi{$accno} = $description; - } - $sth->finish; + my $where = "AND c.id = $chart_id" if ($chart_id ne ''); + + $query = qq{ + SELECT + c.accno, + c.id, + c.description, + c.charttype, + c.category, + c.link, + c.pos_bwa, + c.pos_bilanz, + c.pos_eur, + c.valid_from, + c.datevautomatik, + comma(tk.startdate) AS startdate, + comma(tk.taxkey_id) AS taxkey, + comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription, + comma(tx.taxnumber) AS taxaccount, + comma(tk.pos_ustva) AS tk_ustva, + ( SELECT accno + FROM chart c2 + WHERE c2.id = c.id + ) AS new_account + FROM chart c + LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) + LEFT JOIN tax tx ON (tk.tax_id = tx.id) + WHERE 1=1 + $where + GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno, + c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, + c.datevautomatik + ORDER BY c.accno + }; + + my $sth = prepare_execute_query($form, $dbh, $query); + + $form->{CA} = []; - $query = qq|SELECT c.id, c.accno, c.description, c.charttype, c.gifi_accno, - c.category, c.link - FROM chart c - ORDER BY accno|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while (my $ca = $sth->fetchrow_hashref(NAME_lc)) { - $ca->{amount} = $amount{$ca->{accno}}; - $ca->{gifi_description} = $gifi{$ca->{gifi_accno}}; + $ca->{amount} = $amount{ $ca->{accno} }; if ($ca->{amount} < 0) { $ca->{debit} = $ca->{amount} * -1; } else { $ca->{credit} = $ca->{amount}; } - push @{ $form->{CA} }, $ca; + push(@{ $form->{CA} }, $ca); } $sth->finish; @@ -95,7 +116,6 @@ sub all_accounts { $main::lxdebug->leave_sub(); } - sub all_transactions { $main::lxdebug->enter_sub(); @@ -105,322 +125,235 @@ sub all_transactions { my $dbh = $form->dbconnect($myconfig); # get chart_id - my $query = qq|SELECT c.id FROM chart c - WHERE c.accno = '$form->{accno}'|; - if ($form->{accounttype} eq 'gifi') { - $query = qq|SELECT c.id FROM chart c - WHERE c.gifi_accno = '$form->{gifi_accno}'|; - } - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my @id = (); - while (my ($id) = $sth->fetchrow_array) { - push @id, $id; - } - $sth->finish; + my $query = qq|SELECT id FROM chart WHERE accno = ?|; + my @id = selectall_array_query($form, $dbh, $query, $form->{accno}); my $fromdate_where; my $todate_where; - - my $where = '1 = 1'; + + my $where = qq|1 = 1|; + # build WHERE clause from dates if any -# if ($form->{fromdate}) { -# $where .= " AND ac.transdate >= '$form->{fromdate}'"; -# } -# if ($form->{todate}) { -# $where .= " AND ac.transdate <= '$form->{todate}'"; -# } - + # if ($form->{fromdate}) { + # $where .= " AND ac.transdate >= '$form->{fromdate}'"; + # } + # if ($form->{todate}) { + # $where .= " AND ac.transdate <= '$form->{todate}'"; + # } + + my (@values, @where_values, @subwhere_values); if ($form->{fromdate}) { - $fromto = " AND ac.transdate >= '$form->{fromdate}'"; - $subwhere .= " AND transdate >= '$form->{fromdate}'"; - $glwhere = " AND ac.transdate >= '$form->{fromdate}'"; + $where .= qq| AND ac.transdate >= ?|; + $subwhere .= qq| AND transdate >= ?|; + push(@where_values, conv_date($form->{fromdate})); + push(@subwhere_values, conv_date($form->{fromdate})); } if ($form->{todate}) { - $fromto .= " AND ac.transdate <= '$form->{todate}'"; - $subwhere .= " AND transdate <= '$form->{todate}'"; + $where .= qq| AND ac.transdate <= ?|; + $subwhere .= qq| AND transdate <= ?|; + push(@where_values, conv_date($form->{todate})); + push(@subwhere_values, conv_date($form->{todate})); } - if ($form->{eur}) { - $AR_PAID = qq|AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AR_paid%' - $subwhere - )|; - $AP_PAID = qq|AND ac.trans_id IN - ( - SELECT trans_id - FROM acc_trans - JOIN chart ON (chart_id = id) - WHERE link LIKE '%AP_paid%' - $subwhere - )|; - } else { - $where .= $fromto; - $AR_PAID = ""; - $AP_PAID = ""; - $glwhere = ""; - } - my $sortorder = join ', ', $form->sort_columns(qw(transdate reference description)); - my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE : q|'0'|; - + + my $sortorder = join ', ', + $form->sort_columns(qw(transdate reference description)); + my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|; + # Oracle workaround, use ordinal positions - my %ordinal = ( transdate => 4, - reference => 2, - description => 3 ); + my %ordinal = (transdate => 4, + reference => 2, + description => 3); map { $sortorder =~ s/$_/$ordinal{$_}/ } keys %ordinal; - - my ($null, $department_id) = split /--/, $form->{department}; - my $dpt_where; - my $dpt_join; + my ($null, $department_id) = split(/--/, $form->{department}); + my ($dpt_where, $dpt_join, @department_values); if ($department_id) { - $dpt_join = qq| - JOIN department t ON (t.trans_id = ac.trans_id) - |; - $dpt_where == qq| - AND t.department_id = $department_id - |; + $dpt_join = qq| JOIN department t ON (t.id = a.department_id) |; + $dpt_where = qq| AND t.id = ? |; + @department_values = ($department_id); } - my $project; + my ($project, @project_values); if ($form->{project_id}) { - $project = qq| - AND ac.project_id = $form->{project_id} - |; + $project = qq| AND ac.project_id = ? |; + @project_values = (conv_i($form->{project_id})); } - if ($form->{accno} || $form->{gifi_accno}) { - # get category for account - $query = qq|SELECT c.category - FROM chart c - WHERE c.accno = '$form->{accno}'|; - - if ($form->{accounttype} eq 'gifi') { - $query = qq|SELECT c.category - FROM chart c - WHERE c.gifi_accno = '$form->{gifi_accno}' - AND c.charttype = 'A'|; - } + if ($form->{accno}) { - $sth = $dbh->prepare($query); + # get category for account + $query = qq|SELECT category FROM chart WHERE accno = ?|; + ($form->{category}) = selectrow_query($form, $dbh, $query, $form->{accno}); - $sth->execute || $form->dberror($query); - ($form->{category}) = $sth->fetchrow_array; - $sth->finish; - if ($form->{fromdate}) { - # get beginning balance - $query = qq|SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN chart c ON (ac.chart_id = c.id) - $dpt_join - WHERE c.accno = '$form->{accno}' - AND ac.transdate < '$form->{fromdate}' - $dpt_where - $project - |; + $query = + qq|SELECT SUM(ac.amount) | . + qq|FROM acc_trans ac | . + qq|JOIN chart c ON (ac.chart_id = c.id) | . + $dpt_join . + qq|WHERE c.accno = ? | . + qq|AND ac.transdate < ? | . + $dpt_where . + $project; + @values = ($form->{accno}, conv_date($form->{fromdate}), + @department_values, @project_values); if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice) - 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 c.accno = '$form->{accno}' - AND a.transdate < '$form->{fromdate}' - AND c.category = 'I' - $dpt_where - $project - - UNION - - SELECT SUM(ac.sellprice) - 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 c.accno = '$form->{accno}' - AND a.transdate < '$form->{fromdate}' - AND c.category = 'E' - $dpt_where - $project - |; - + $query .= + qq|UNION | . + + qq|SELECT SUM(ac.qty * ac.sellprice) | . + qq|FROM invoice ac | . + qq|JOIN ar a ON (ac.trans_id = a.id) | . + qq|JOIN parts p ON (ac.parts_id = p.id) | . + qq|JOIN chart c ON (p.income_accno_id = c.id) | . + $dpt_join . + qq|WHERE c.accno = ? | . + qq| AND a.transdate < ? | . + qq| AND c.category = 'I' | . + $dpt_where . + $project . + + qq|UNION | . + + qq|SELECT SUM(ac.qty * ac.sellprice) | . + qq|FROM invoice ac | . + qq|JOIN ap a ON (ac.trans_id = a.id) | . + qq|JOIN parts p ON (ac.parts_id = p.id) | . + qq|JOIN chart c ON (p.expense_accno_id = c.id) | . + $dpt_join . + qq|WHERE c.accno = ? | . + qq| AND a.transdate < ? | . + qq| AND c.category = 'E' | . + $dpt_where . + $project; + + push(@values, + $form->{accno}, conv_date($form->{transdate}), + @department_values, @project_values, + $form->{accno}, conv_date($form->{transdate}), + @department_values, @project_values); } - if ($form->{accounttype} eq 'gifi') { - $query = qq|SELECT SUM(ac.amount) - FROM acc_trans ac - JOIN chart c ON (ac.chart_id = c.id) - $dpt_join - WHERE c.gifi_accno = '$form->{gifi_accno}' - AND ac.transdate < '$form->{fromdate}' - $dpt_where - $project - |; - - if ($form->{project_id}) { - - $query .= qq| - - UNION - - SELECT SUM(ac.sellprice) - 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 c.gifi_accno = '$form->{gifi_accno}' - AND a.transdate < '$form->{fromdate}' - AND c.category = 'I' - $dpt_where - $project - - UNION - - SELECT SUM(ac.sellprice) - 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 c.gifi_accno = '$form->{gifi_accno}' - AND a.transdate < '$form->{fromdate}' - AND c.category = 'E' - $dpt_where - $project - |; - - } - } - - $sth = $dbh->prepare($query); - - $sth->execute || $form->dberror($query); - ($form->{balance}) = $sth->fetchrow_array; - $sth->finish; + ($form->{balance}) = selectrow_query($form, $dbh, $query, @values); } } $query = ""; my $union = ""; + @values = (); - foreach my $id (@id) { + foreach my $id (@id) { + # NOTE: Postgres is really picky about the order of implicit CROSS + # JOINs with ',' if you alias the tables and want to use the + # alias later in another JOIN. the alias you want to use has to + # be the most recent in the list, otherwise Postgres will + # overwrite the alias internally and complain. For this reason, + # in the next 3 SELECTs, the 'a' alias is last in the list. + # Don't change this, and if you do, substitute the ',' with CROSS + # JOIN ... that also works. + # get all transactions - $query .= qq|$union - SELECT g.id, g.reference, g.description, ac.transdate, - $false AS invoice, ac.amount, 'gl' as module - FROM gl g, acc_trans ac $dpt_from - WHERE $where - $glwhere - $dpt_join - $project - AND ac.chart_id = $id - AND ac.trans_id = g.id - UNION - SELECT a.id, a.invnumber, c.name, ac.transdate, - a.invoice, ac.amount, 'ar' as module - FROM ar a, acc_trans ac, customer c $dpt_from - WHERE $where - $dpt_join - $project - AND ac.chart_id = $id - AND ac.trans_id = a.id - $AR_PAID - AND a.customer_id = c.id - UNION - SELECT a.id, a.invnumber, v.name, ac.transdate, - a.invoice, ac.amount, 'ap' as module - FROM ap a, acc_trans ac, vendor v $dpt_from - WHERE $where - $dpt_join - $project - AND ac.chart_id = $id - AND ac.trans_id = a.id - $AP_PAID - AND a.vendor_id = v.id - |; - $union = qq| - UNION ALL - |; + $query .= + $union . + qq|SELECT a.id, a.reference, a.description, ac.transdate, | . + qq| $false AS invoice, ac.amount, 'gl' as module | . + qq|FROM acc_trans ac, gl a | . + $dpt_join . + qq|WHERE | . $where . $dpt_where . $project . + qq| AND ac.chart_id = ? | . + qq| AND ac.trans_id = a.id | . + + qq|UNION ALL | . + + qq|SELECT a.id, a.invnumber, c.name, ac.transdate, | . + qq| a.invoice, ac.amount, 'ar' as module | . + qq|FROM acc_trans ac, customer c, ar a | . + $dpt_join . + qq|WHERE | . $where . $dpt_where . $project . + qq| AND ac.chart_id = ? | . + qq| AND NOT a.storno | . + qq| AND ac.trans_id = a.id | . + qq| AND a.customer_id = c.id | . + + qq|UNION ALL | . + + qq|SELECT a.id, a.invnumber, v.name, ac.transdate, | . + qq| a.invoice, ac.amount, 'ap' as module | . + qq|FROM acc_trans ac, vendor v, ap a | . + $dpt_join . + qq|WHERE | . $where . $dpt_where . $project . + qq| AND ac.chart_id = ? | . + qq| AND ac.trans_id = a.id | . + qq| AND NOT a.storno | . + qq| AND a.vendor_id = v.id |; + + push(@values, + @where_values, @department_values, @project_values, $id, + @where_values, @department_values, @project_values, $id, + @where_values, @department_values, @project_values, $id); + + $union = qq|UNION ALL |; if ($form->{project_id}) { $fromdate_where =~ s/ac\./a\./; - $todate_where =~ s/ac\./a\./; - - $query .= qq| - - UNION ALL - - SELECT a.id, a.invnumber, c.name, a.transdate, - a.invoice, ac.sellprice, 'ar' as module - FROM ar a - JOIN invoice ac ON (ac.trans_id = a.id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN customer c ON (a.customer_id = c.id) - $dpt_join - WHERE p.income_accno_id = $id - $fromdate_where - $todate_where - $dpt_where - $project - - UNION ALL - - SELECT a.id, a.invnumber, v.name, a.transdate, - a.invoice, ac.sellprice, 'ap' as module - FROM ap a - JOIN invoice ac ON (ac.trans_id = a.id) - JOIN parts p ON (ac.parts_id = p.id) - JOIN vendor v ON (a.vendor_id = v.id) - $dpt_join - WHERE p.expense_accno_id = $id - $fromdate_where - $todate_where - $dpt_where - $project - |; - + $todate_where =~ s/ac\./a\./; + + $query .= + qq|UNION ALL | . + + qq|SELECT a.id, a.invnumber, c.name, a.transdate, | . + qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module | . + qq|FROM ar a | . + qq|JOIN invoice ac ON (ac.trans_id = a.id) | . + qq|JOIN parts p ON (ac.parts_id = p.id) | . + qq|JOIN customer c ON (a.customer_id = c.id) | . + $dpt_join . + qq|WHERE p.income_accno_id = ? | . + $fromdate_where . + $todate_where . + $dpt_where . + $project . + + qq|UNION ALL | . + + qq|SELECT a.id, a.invnumber, v.name, a.transdate, | . + qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module | . + qq|FROM ap a | . + qq|JOIN invoice ac ON (ac.trans_id = a.id) | . + qq|JOIN parts p ON (ac.parts_id = p.id) | . + qq|JOIN vendor v ON (a.vendor_id = v.id) | . + $dpt_join . + qq|WHERE p.expense_accno_id = ? | . + $fromdate_where . + $todate_where . + $dpt_where . + $project; + + push(@values, + $id, @department_values, @project_values, + $id, @department_values, @project_values); + $fromdate_where =~ s/a\./ac\./; - $todate_where =~ s/a\./ac\./; - + $todate_where =~ s/a\./ac\./; + } - - $union = qq| - UNION ALL - |; + + $union = qq|UNION ALL|; } - $query .= qq| - ORDER BY $sortorder|; + my $sort = grep({ $form->{sort} eq $_ } qw(transdate reference description)) ? $form->{sort} : 'transdate'; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $query .= qq|ORDER BY $sort|; + $sth = prepare_execute_query($form, $dbh, $query, @values); + $form->{CA} = []; while (my $ca = $sth->fetchrow_hashref(NAME_lc)) { - - # gl - if ($ca->{module} eq "gl") { - $ca->{module} = "gl"; - } - # ap if ($ca->{module} eq "ap") { $ca->{module} = ($ca->{invoice}) ? 'ir' : 'ap'; @@ -432,17 +365,19 @@ sub all_transactions { } if ($ca->{amount} < 0) { - $ca->{debit} = $ca->{amount} * -1; + $ca->{debit} = $ca->{amount} * -1; $ca->{credit} = 0; } else { $ca->{credit} = $ca->{amount}; - $ca->{debit} = 0; + $ca->{debit} = 0; } - push @{ $form->{CA} }, $ca; - + $ca->{index} = join "--", map { $ca->{$_} } qw(id reference description); + + push(@{ $form->{CA} }, $ca); + } - + $sth->finish; $dbh->disconnect; @@ -450,4 +385,3 @@ sub all_transactions { } 1; -