X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FCP.pm;h=75e753e07605f309881729e2697f722ba2a13a18;hb=1b032ef45d886922f1fe347ec234c9b0c8c50efd;hp=fbfd6fa435a48dd17a121794f2fcad8a86c5b324;hpb=a590a6518025280c2d6e76c87641764ca6198521;p=kivitendo-erp.git diff --git a/SL/CP.pm b/SL/CP.pm index fbfd6fa43..75e753e07 100644 --- a/SL/CP.pm +++ b/SL/CP.pm @@ -25,7 +25,8 @@ # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software -# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. +# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, +# MA 02110-1335, USA. #====================================================================== # # Check and receipt printing payment module backend routines @@ -36,6 +37,7 @@ package CP; use SL::DBUtils; +use SL::DB; use strict; @@ -66,8 +68,7 @@ sub paymentaccounts { my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = SL::DB->client->dbh; my $ARAP = $form->{ARAP} eq "AR" ? "AR" : "AP"; @@ -76,7 +77,7 @@ sub paymentaccounts { qq|FROM chart | . qq|WHERE link LIKE ? |. qq|ORDER BY accno|; - my $sth = prepare_execute_query($form, $dbh, $query, '%' . $ARAP . '%'); + my $sth = prepare_execute_query($form, $dbh, $query, like($ARAP)); $form->{PR}{ $form->{ARAP} } = (); $form->{PR}{"$form->{ARAP}_paid"} = (); @@ -93,56 +94,9 @@ sub paymentaccounts { } $sth->finish; - # get currencies and closedto - $query = qq|SELECT curr, closedto FROM defaults|; - ($form->{currencies}, $form->{closedto}) = - selectrow_query($form, $dbh, $query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_openvc { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->dbconnect($myconfig); - - my $arap = ($form->{vc} eq 'customer') ? 'ar' : 'ap'; - my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; - my $query = - qq|SELECT count(*) | . - qq|FROM $vc ct, $arap a | . - qq|WHERE (a.${vc}_id = ct.id) AND (a.amount != a.paid)|; - my ($count) = selectrow_query($form, $dbh, $query); - - # build selection list - if ($count < $myconfig->{vclimit}) { - $query = - qq|SELECT DISTINCT ct.id, ct.name | . - qq|FROM $vc ct, $arap a | . - qq|WHERE (a.${vc}_id = ct.id) AND (a.amount != a.paid) | . - qq|ORDER BY ct.name|; - $form->{"all_$form->{vc}"} = selectall_hashref_query($form, $dbh, $query); - } - - if ($form->{ARAP} eq 'AR') { - $query = - qq|SELECT d.id, d.description | . - qq|FROM department d | . - qq|WHERE d.role = 'P' | . - qq|ORDER BY 2|; - } else { - $query = - qq|SELECT d.id, d.description | . - qq|FROM department d | . - qq|ORDER BY 2|; - } - $form->{all_departments} = selectall_hashref_query($form, $dbh, $query); - - $dbh->disconnect; + # get closedto + $query = qq|SELECT closedto FROM defaults|; + ($form->{closedto}) = selectrow_query($form, $dbh, $query); $main::lxdebug->leave_sub(); } @@ -153,21 +107,29 @@ sub get_openinvoices { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = SL::DB->client->dbh; my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; my $buysell = $form->{vc} eq 'customer' ? "buy" : "sell"; my $arap = $form->{arap} eq "ar" ? "ar" : "ap"; + my @values = (conv_i($form->{"${vc}_id"}), "$form->{currency}"); + my $whereinvoice = ''; + if ($::form->{invnumber}) { + $whereinvoice = ' AND a.invnumber LIKE ? '; + push @values, $::form->{invnumber}; + } + my $query = - qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, a.curr | . + qq|SELECT a.id, a.invnumber, a.transdate, a.amount, a.paid, cu.name AS curr | . qq|FROM $arap a | . - qq|WHERE (a.${vc}_id = ?) AND (COALESCE(a.curr, '') = ?) AND NOT (a.amount = a.paid)| . + qq|LEFT JOIN currencies cu ON (cu.id=a.currency_id)| . + qq|WHERE (a.${vc}_id = ?) AND cu.name = ? AND NOT (a.amount = a.paid)| . + $whereinvoice . qq|ORDER BY a.id|; - my $sth = prepare_execute_query($form, $dbh, $query, - conv_i($form->{"${vc}_id"}), - "$form->{currency}"); + + my $sth = prepare_execute_query($form, $dbh, $query, @values); $form->{PR} = []; while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { @@ -185,24 +147,29 @@ sub get_openinvoices { SELECT COUNT(*) FROM $arap WHERE (${vc}_id = ?) - AND (COALESCE(curr, '') <> ?) + AND ((SELECT cu.name FROM currencies cu WHERE cu.id=${arap}.currency_id) <> ?) AND (amount <> paid) SQL ($form->{openinvoices_other_currencies}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{"${vc}_id"}), "$form->{currency}"); - $dbh->disconnect; - $main::lxdebug->leave_sub(); } sub process_payment { + my ($self, $myconfig, $form) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_process_payment, $self, $myconfig, $form); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _process_payment { my ($self, $myconfig, $form) = @_; my $amount; - # connect to database, turn AutoCommit off - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = SL::DB->client->dbh; my ($paymentaccno) = split /--/, $form->{account}; @@ -240,11 +207,6 @@ sub process_payment { qq| (c.link LIKE '%:AP:%')) |; } - my $paymentamount = $form->{amount}; - - my $null; - ($null, $form->{department_id}) = split(/--/, $form->{department}); - $form->{department_id} *= 1; # query to retrieve paid amount $query = @@ -260,14 +222,13 @@ sub process_payment { $form->{"due_$i"} = $form->parse_amount($myconfig, $form->{"due_$i"}); if ($form->{"checked_$i"} && $form->{"paid_$i"}) { - $paymentamount = (($paymentamount * 1000) - ($form->{"paid_$i"} * 1000)) / 1000; # get exchangerate for original $query = qq|SELECT $buysell | . qq|FROM exchangerate e | . qq|JOIN ${arap} a ON (a.transdate = e.transdate) | . - qq|WHERE (e.curr = ?) AND (a.id = ?)|; + qq|WHERE (e.currency_id = (SELECT id FROM currencies WHERE name = ?)) AND (a.id = ?)|; my ($exchangerate) = selectrow_query($form, $dbh, $query, $form->{currency}, $form->{"id_$i"}); @@ -286,20 +247,20 @@ sub process_payment { # add AR/AP $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount) | . - qq|VALUES (?, ?, ?, ?)|; + qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, chart_link, taxkey, tax_id) | . + qq|VALUES (?, ?, ?, ?, (SELECT link FROM chart WHERE id=?), 0, (SELECT id FROM tax WHERE taxkey=0 LIMIT 1))|; do_query($form, $dbh, $query, $form->{"id_$i"}, $id, - conv_date($form->{datepaid}), $amount * $ml); + conv_date($form->{datepaid}), $amount * $ml, $id); # add payment $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, | . - qq| source, memo) | . - qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; + qq| source, memo, chart_link, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, (SELECT link FROM chart WHERE accno=?), 0, (SELECT id FROM tax WHERE taxkey=0 LIMIT 1))|; my @values = (conv_i($form->{"id_$i"}), $paymentaccno, conv_date($form->{datepaid}), $form->{"paid_$i"} * $ml * -1, $form->{source}, - $form->{memo}); + $form->{memo}, $paymentaccno); do_query($form, $dbh, $query, @values); # add exchangerate difference if currency ne defaultcurrency @@ -310,11 +271,11 @@ sub process_payment { # exchangerate difference $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, amount, | . - qq| cleared, fx_transaction) | . - qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; + qq| cleared, fx_transaction, chart_link, taxkey, tax_id) | . + qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, (SELECT link FROM chart WHERE accno = ?), 0, (SELECT id FROM tax WHERE taxkey=0 LIMIT 1))|; @values = (conv_i($form->{"id_$i"}), $paymentaccno, conv_date($form->{datepaid}), ($amount * $ml * -1), '0', - '1'); + '1', $paymentaccno); do_query($form, $dbh, $query, @values); # gain/loss @@ -326,11 +287,11 @@ sub process_payment { my $accno_id = ($amount < 0) ? $fxgain_accno_id : $fxloss_accno_id; $query = qq|INSERT INTO acc_trans (trans_id, chart_id, transdate, | . - qq| amount, cleared, fx_transaction) | . - qq|VALUES (?, ?, ?, ?, ?, ?)|; + qq| amount, cleared, fx_transaction, chart_link, taxkey, tax_id) | . + qq|VALUES (?, ?, ?, ?, ?, ?, (SELECT link FROM chart WHERE id=?), 0, (SELECT id FROM tax WHERE taxkey=0 LIMIT 1))|; @values = (conv_i($form->{"id_$i"}), $accno_id, conv_date($form->{datepaid}), $amount * $ml * -1, '0', - '1'); + '1', $accno_id); do_query($form, $dbh, $query, @values); } } @@ -358,32 +319,16 @@ sub process_payment { # saving the history $form->{id} = $form->{"id_$i"}; if(!exists $form->{addition}) { - $form->{snumbers} = qq|invnumber_| . $form->{"invnumber_$i"}; - $form->{addition} = "POSTED"; + $form->{snumbers} = qq|invnumber_| . $form->{"invnumber_$i"}; + $form->{what_done} = "invoice"; + $form->{addition} = "PAYMENT POSTED"; $form->save_history; } # /saving the history } } - my $rc; - if ($form->round_amount($paymentamount, 2) < 0) { - # Hier werden negativen Zahlungseingänge abgefangen - # Besser: in Oberfläche schon prüfen - # Zahlungsein- und ausgänge sind immer positiv - $dbh->rollback; - $rc = 0; - } - if ($form->round_amount($paymentamount, 2) == 0) { - $rc = $dbh->commit; - } - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); - - return $rc; + return 1; } 1; -