X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAP.pm;h=eabf47112e4e649cef8c7cbe531e866b29b1a06a;hb=627503c1d8443acdb4c50362c3981e68564a12d4;hp=804c9b47826dbc46980572f8e4defab35757dec3;hpb=0c4b51268cd7b4dd912442e6024543fadc56f740;p=kivitendo-erp.git diff --git a/SL/AP.pm b/SL/AP.pm index 804c9b478..eabf47112 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -55,10 +55,8 @@ sub post_transaction { my $exchangerate = 0; $form->{defaultcurrency} = $form->get_default_currency($myconfig); - delete $form->{currency} unless $form->{defaultcurrency}; ($null, $form->{department_id}) = split(/--/, $form->{department}); - $form->{department_id} *= 1; if ($form->{currency} eq $form->{defaultcurrency}) { $form->{exchangerate} = 1; @@ -165,31 +163,24 @@ sub post_transaction { do_query($form, $dbh, $query, $form->{id}); } else { - my $uid = rand() . time; - - $uid .= $form->{login}; - - $uid = substr($uid, 2, 75); + ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('glid')|); + $query = - qq|INSERT INTO ap (invnumber, employee_id) | . - qq|VALUES (?, (SELECT e.id FROM employee e WHERE e.login = ?))|; - do_query($form, $dbh, $query, $uid, $form->{login}); + qq|INSERT INTO ap (id, invnumber, employee_id,currency_id) | . + qq|VALUES (?, ?, (SELECT e.id FROM employee e WHERE e.login = ?), + (SELECT id FROM currencies WHERE name = ?) )|; + do_query($form, $dbh, $query, $form->{id}, $form->{invnumber}, $form->{login}, $form->{currency}); - $query = qq|SELECT a.id FROM ap a - WHERE a.invnumber = ?|; - ($form->{id}) = selectrow_query($form, $dbh, $query, $uid); } - $form->{invnumber} = $form->{id} unless $form->{invnumber}; - $query = qq|UPDATE ap SET - invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, + transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, amount = ?, duedate = ?, paid = ?, netamount = ?, - curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ?, - globalproject_id = ? + currency_id = (SELECT id FROM currencies WHERE name = ?), notes = ?, department_id = ?, storno = ?, storno_id = ?, + globalproject_id = ?, direct_debit = ? WHERE id = ?|; - @values = ($form->{invnumber}, conv_date($form->{transdate}), + @values = (conv_date($form->{transdate}), $form->{ordnumber}, conv_i($form->{vendor_id}), $form->{taxincluded} ? 't' : 'f', $form->{invtotal}, conv_date($form->{duedate}), $form->{invpaid}, @@ -197,6 +188,7 @@ sub post_transaction { $form->{currency}, $form->{notes}, conv_i($form->{department_id}), $form->{storno}, $form->{storno_id}, conv_i($form->{globalproject_id}), + $form->{direct_debit} ? 't' : 'f', $form->{id}); do_query($form, $dbh, $query, @values); @@ -209,24 +201,28 @@ sub post_transaction { # insert detail records in acc_trans $query = qq|INSERT INTO acc_trans | . - qq| (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id)| . + qq| (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link)| . qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . - qq| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| ?, ?, ?, ?, ?,| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{AP_amounts}{"amount_$i"}, $form->{"amount_$i"}, conv_date($form->{transdate}), - $project_id, $form->{"taxkey_$i"}, $form->{AP_amounts}{"amount_$i"}, conv_date($form->{transdate})); + $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}), + $form->{AP_amounts}{"amount_$i"}); do_query($form, $dbh, $query, @values); if ($form->{"tax_$i"} != 0) { # insert detail records in acc_trans $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, | . - qq| project_id, taxkey, tax_id) | . + qq| project_id, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), | . - qq| ?, ?, ?, ?, (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| ?, ?, ?, ?, ?,| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{AP_amounts}{"tax_$i"}, $form->{"tax_$i"}, conv_date($form->{transdate}), - $project_id, $form->{"taxkey_$i"}, $form->{AP_amounts}{"tax_$i"}, conv_date($form->{transdate})); + $project_id, $form->{"taxkey_$i"}, conv_i($form->{"tax_id_$i"}), + $form->{AP_amounts}{"tax_$i"}); do_query($form, $dbh, $query, @values); } @@ -235,12 +231,20 @@ sub post_transaction { # add payables $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . - qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id| . + qq| FROM taxkeys| . + qq| WHERE chart_id= (SELECT id | . + qq| FROM chart| . + qq| WHERE accno = ?)| . + qq| AND startdate <= ?| . + qq| ORDER BY startdate DESC LIMIT 1),| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{AP_amounts}{payables}, $form->{payables}, - conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate})); + conv_date($form->{transdate}), $form->{AP_amounts}{payables}, $form->{AP_amounts}{payables}, conv_date($form->{transdate}), + $form->{AP_amounts}{payables}); do_query($form, $dbh, $query, @values); } @@ -284,13 +288,21 @@ sub post_transaction { 2); if ($form->{payables}) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, | . qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . - qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| (SELECT tax_id| . + qq| FROM taxkeys| . + qq| WHERE chart_id= (SELECT id | . + qq| FROM chart| . + qq| WHERE accno = ?)| . + qq| AND startdate <= ?| . + qq| ORDER BY startdate DESC LIMIT 1),| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{AP_payables}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"})); + $form->{AP_payables}, $form->{AP_payables}, conv_date($form->{"datepaid_$i"}), + $form->{AP_payables}); do_query($form, $dbh, $query, @values); } $form->{payables} = $amount; @@ -298,14 +310,22 @@ sub post_transaction { # add payment my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig)); $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, project_id, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . - qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . + qq| (SELECT tax_id| . + qq| FROM taxkeys| . + qq| WHERE chart_id= (SELECT id | . + qq| FROM chart| . + qq| WHERE accno = ?)| . + qq| AND startdate <= ?| . + qq| ORDER BY startdate DESC LIMIT 1),| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $form->{"paid_$i"}, conv_date($form->{"datepaid_$i"}), $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $project_id, $form->{"AP_paid_account_$i"}, - $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}), + $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); # add exchange rate difference @@ -314,14 +334,22 @@ sub post_transaction { ($form->{"exchangerate_$i"} - 1), 2); if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . qq| (SELECT taxkey_id FROM chart WHERE accno = ?), | . - qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; + qq| (SELECT tax_id| . + qq| FROM taxkeys| . + qq| WHERE chart_id= (SELECT id | . + qq| FROM chart| . + qq| WHERE accno = ?)| . + qq| AND startdate <= ?| . + qq| ORDER BY startdate DESC LIMIT 1),| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; @values = ($form->{id}, $form->{"AP_paid_account_$i"}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, $form->{"AP_paid_account_$i"}, - $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"})); + $form->{"AP_paid_account_$i"}, conv_date($form->{"datepaid_$i"}), + $form->{"AP_paid_account_$i"}); do_query($form, $dbh, $query, @values); } @@ -333,16 +361,23 @@ sub post_transaction { if ($amount != 0) { $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id) | . + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, fx_transaction, cleared, project_id, taxkey, tax_id, chart_link) | . qq|VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 't', 'f', ?, | . - qq| (SELECT taxkey_id FROM chart WHERE accno = ?)| . - qq| (SELECT tax_id FROM taxkeys WHERE taxkey_id= (SELECT taxkey_id FROM chart WHERE accno = ?) AND startdate <= ? ORDER BY startdate DESC LIMIT 1))|; - @values = ($form->{id}, ($amount > 0) ? - $form->{fxgain_accno} : $form->{fxloss_accno}, + qq| (SELECT taxkey_id FROM chart WHERE accno = ?),| . + qq| (SELECT tax_id| . + qq| FROM taxkeys| . + qq| WHERE chart_id= (SELECT id | . + qq| FROM chart| . + qq| WHERE accno = ?)| . + qq| AND startdate <= ?| . + qq| ORDER BY startdate DESC LIMIT 1),| . + qq| (SELECT c.link FROM chart c WHERE c.accno = ?))|; + @values = ($form->{id}, + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, $amount, conv_date($form->{"datepaid_$i"}), $project_id, - ($amount > 0) ? - $form->{fxgain_accno} : $form->{fxloss_accno}, - ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, conv_date($form->{"datepaid_$i"})); + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}, conv_date($form->{"datepaid_$i"}), + ($amount > 0) ? $form->{fxgain_accno} : $form->{fxloss_accno}); do_query($form, $dbh, $query, @values); } @@ -373,6 +408,7 @@ sub post_transaction { dbh => $dbh, from => $transdate, to => $transdate, + trans_id => $form->{id}, ); $datev->export; @@ -403,13 +439,10 @@ sub delete_transaction { # connect to database my $dbh = $form->dbconnect_noauto($myconfig); + # acc_trans entries are deleted by database triggers. my $query = qq|DELETE FROM ap WHERE id = ?|; do_query($form, $dbh, $query, $form->{id}); - $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|; - do_query($form, $dbh, $query, $form->{id}); - - # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; @@ -450,6 +483,11 @@ sub ap_transactions { qq|LEFT JOIN payment_terms pt ON (pt.id = v.payment_id)|; my $where = ''; + + unless ( $::auth->assert('show_ap_transactions', 1) ) { + $where .= " AND NOT invoice = 'f' "; # remove ap transactions from Sales -> Reports -> Invoices + }; + my @values; if ($form->{vendor_id}) { @@ -625,7 +663,6 @@ sub post_payment { $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); $form->{defaultcurrency} = $form->get_default_currency($myconfig); - delete $form->{currency} unless $form->{defaultcurrency}; # Get the AP accno. $query = @@ -658,7 +695,7 @@ sub setup_form { my ($self, $form, $for_post_payments) = @_; my ($exchangerate, $i, $j, $k, $key, $akey, $ref, $index, $taxamount, $totalamount, $totaltax, $totalwithholding, $withholdingrate, - $taxincluded, $tax, $diff); + $tax, $diff); # forex $form->{forex} = $form->{exchangerate}; @@ -762,7 +799,6 @@ sub setup_form { } } - $form->{taxincluded} = $taxincluded if ($form->{id}); $form->{paidaccounts} = 1 if not defined $form->{paidaccounts}; if ($form->{taxincluded} && $form->{taxrate} && $totalamount) { @@ -838,4 +874,3 @@ sub storno { } 1; -