From 53beea8bb782b8ddf13d291bb274cff31456dc21 Mon Sep 17 00:00:00 2001 From: Philip Reetz Date: Mon, 11 Dec 2006 15:37:41 +0000 Subject: [PATCH] Anpassung der Routinen von Kreditoren und Debitorenbuchungen auf das neue Steuersystem --- SL/AP.pm | 10 +- SL/AR.pm | 11 +- SL/Form.pm | 105 +++++++++++++----- bin/mozilla/ap.pl | 12 +- bin/mozilla/ar.pl | 16 ++- .../Pg-upgrade-2.2.0.34-2.2.0.35.sql | 1 + 6 files changed, 106 insertions(+), 49 deletions(-) create mode 100644 sql/Pg-upgrade/Pg-upgrade-2.2.0.34-2.2.0.35.sql diff --git a/SL/AP.pm b/SL/AP.pm index 0d132293f..b0e044032 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -88,13 +88,15 @@ sub post_transaction { $form->{taxincluded} = 0 if ($form->{amount} == 0); for $i (1 .. $form->{rowcount}) { - ($form->{"taxkey_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; + ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; - $query = - qq| SELECT c.accno, t.rate FROM chart c, tax t where c.id=t.chart_id AND t.taxkey=$form->{"taxkey_$i"}|; + $query = qq|SELECT c.accno, t.taxkey, t.rate + FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) + WHERE t.id=$form->{"tax_id_$i"} + ORDER BY c.accno|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - ($form->{AP_amounts}{"tax_$i"}, $form->{"taxrate_$i"}) = + ($form->{AP_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) = $sth->fetchrow_array; $form->{AP_amounts}{"tax_$i"}{taxkey} = $form->{"taxkey_$i"}; $form->{AP_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"}; diff --git a/SL/AR.pm b/SL/AR.pm index fe687ee86..b4c0ae14d 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -91,13 +91,16 @@ sub post_transaction { $form->{taxincluded} = 0 if ($form->{amount} == 0); for $i (1 .. $form->{rowcount}) { - ($form->{"taxkey_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; + ($form->{"tax_id_$i"}, $NULL) = split /--/, $form->{"taxchart_$i"}; + + $query = qq|SELECT c.accno, t.taxkey, t.rate + FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) + WHERE t.id=$form->{"tax_id_$i"} + ORDER BY c.accno|; - $query = - qq| SELECT c.accno, t.rate FROM chart c, tax t where c.id=t.chart_id AND t.taxkey=$form->{"taxkey_$i"}|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - ($form->{AR_amounts}{"tax_$i"}, $form->{"taxrate_$i"}) = + ($form->{AR_amounts}{"tax_$i"}, $form->{"taxkey_$i"}, $form->{"taxrate_$i"}) = $sth->fetchrow_array; $form->{AR_amounts}{"tax_$i"}{taxkey} = $form->{"taxkey_$i"}; $form->{AR_amounts}{"amount_$i"}{taxkey} = $form->{"taxkey_$i"}; diff --git a/SL/Form.pm b/SL/Form.pm index cbfc81f32..381d8152d 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -1634,40 +1634,47 @@ sub create_links { my ($query, $sth); my $dbh = $self->dbconnect($myconfig); - my %xkeyref = (); - # now get the account numbers - $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id - FROM chart c - WHERE c.link LIKE '%$module%' - ORDER BY c.accno|; - - $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); - - $self->{accounts} = ""; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + if (!$self->{id}) { - foreach my $key (split /:/, $ref->{link}) { - if ($key =~ /$module/) { - - # cross reference for keys - $xkeyref{ $ref->{accno} } = $key; - - push @{ $self->{"${module}_links"}{$key} }, - { accno => $ref->{accno}, - description => $ref->{description}, - taxkey => $ref->{taxkey_id} }; - - $self->{accounts} .= "$ref->{accno} " unless $key =~ /tax/; + my $transdate = "current_date"; + if ($self->{transdate}) { + $transdate = qq|'$self->{transdate}'|; + } + + # now get the account numbers + $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id + FROM chart c, taxkeys tk + WHERE c.link LIKE '%$module%' AND c.id=tk.chart_id AND tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=$transdate ORDER BY startdate desc LIMIT 1) + ORDER BY c.accno|; + + $sth = $dbh->prepare($query); + $sth->execute || $self->dberror($query); + + $self->{accounts} = ""; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + + foreach my $key (split /:/, $ref->{link}) { + if ($key =~ /$module/) { + + # cross reference for keys + $xkeyref{ $ref->{accno} } = $key; + + push @{ $self->{"${module}_links"}{$key} }, + { accno => $ref->{accno}, + description => $ref->{description}, + taxkey => $ref->{taxkey_id}, + tax_id => $ref->{tax_id} }; + + $self->{accounts} .= "$ref->{accno} " unless $key =~ /tax/; + } } } } - $sth->finish; # get taxkeys and description - $query = qq|SELECT taxkey, taxdescription + $query = qq|SELECT id, taxkey, taxdescription FROM tax|; $sth = $dbh->prepare($query); $sth->execute || $self->dberror($query); @@ -1729,14 +1736,50 @@ sub create_links { } $sth->finish; + + my $transdate = "current_date"; + if ($self->{transdate}) { + $transdate = qq|'$self->{transdate}'|; + } + + # now get the account numbers + $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id + FROM chart c, taxkeys tk + WHERE c.link LIKE '%$module%' AND (((tk.chart_id=c.id) AND NOT(c.link like '%_tax%')) OR (NOT(tk.chart_id=c.id) AND (c.link like '%_tax%'))) AND (((tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=$transdate ORDER BY startdate desc LIMIT 1)) AND NOT(c.link like '%_tax%')) OR (c.link like '%_tax%')) + ORDER BY c.accno|; + + $sth = $dbh->prepare($query); + $sth->execute || $self->dberror($query); + + $self->{accounts} = ""; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + + foreach my $key (split /:/, $ref->{link}) { + if ($key =~ /$module/) { + + # cross reference for keys + $xkeyref{ $ref->{accno} } = $key; + + push @{ $self->{"${module}_links"}{$key} }, + { accno => $ref->{accno}, + description => $ref->{description}, + taxkey => $ref->{taxkey_id}, + tax_id => $ref->{tax_id} }; + + $self->{accounts} .= "$ref->{accno} " unless $key =~ /tax/; + } + } + } + + # get amounts from individual entries $query = qq|SELECT c.accno, c.description, a.source, a.amount, a.memo, - a.transdate, a.cleared, a.project_id, p.projectnumber, a.taxkey, t.rate + a.transdate, a.cleared, a.project_id, p.projectnumber, a.taxkey, t.rate, t.id FROM acc_trans a JOIN chart c ON (c.id = a.chart_id) LEFT JOIN project p ON (p.id = a.project_id) - LEFT Join tax t ON (a.taxkey = t.taxkey) - WHERE a.trans_id = $self->{id} + LEFT JOIN tax t ON (t.id=(SELECT tk.tax_id from taxkeys tk WHERE (tk.taxkey_id=a.taxkey) AND ((CASE WHEN a.chart_id IN (SELECT chart_id FROM taxkeys WHERE taxkey_id=a.taxkey) THEN tk.chart_id=a.chart_id ELSE 1=1 END) OR (c.link='%tax%')) AND startdate <=a.transdate ORDER BY startdate DESC LIMIT 1)) + WHERE a.trans_id = $self->{id} AND a.fx_transaction = '0' ORDER BY a.oid,a.transdate|; $sth = $dbh->prepare($query); @@ -1765,8 +1808,8 @@ sub create_links { push @{ $self->{acc_trans}{ $xkeyref{ $ref->{accno} } } }, $ref; } - $sth->finish; + $sth->finish; $query = qq|SELECT d.curr AS currencies, d.closedto, d.revtrans, (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, @@ -1817,6 +1860,8 @@ sub create_links { } + $sth->finish; + $dbh->disconnect; $main::lxdebug->leave_sub(); diff --git a/bin/mozilla/ap.pl b/bin/mozilla/ap.pl index 442531a7f..b0678ae08 100644 --- a/bin/mozilla/ap.pl +++ b/bin/mozilla/ap.pl @@ -112,7 +112,7 @@ sub create_links { $duedate = $form->{duedate}; IR->get_vendor(\%myconfig, \%$form); - + $form->{taxincluded} = $taxincluded; $form->{duedate} = $duedate if $duedate; $form->{oldvendor} = "$form->{vendor}--$form->{vendor_id}"; $form->{rowcount} = 1; @@ -122,7 +122,7 @@ sub create_links { map { $tax .= - qq|\n"; } else { $form->{"select$key"} .= - "\n"; + "\n"; } } @@ -246,7 +246,7 @@ sub create_links { if ($akey eq 'amount') { $form->{"taxchart_$k"} = $form->{taxchart}; $form->{"taxchart_$k"} =~ - /\n"; } else { $form->{"select$key"} .= - "\n"; + "\n"; } } @@ -256,7 +258,7 @@ sub create_links { if ($akey eq 'amount') { $form->{"taxchart_$k"} = $form->{taxchart}; $form->{"taxchart_$k"} =~ - /