X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=ec744df2eba91e049438f2961a1db36c03ffaa0f;hb=33c46114fde417a185bcf6715e9903f08f977235;hp=5c07ca06599d9ea70af27016f7d267ba12d78fae;hpb=805e8da4fc4c631f7d62286fa63d8a5e69e769ab;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index 5c07ca065..ec744df2e 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -34,10 +34,11 @@ package IS; -use Data::Dumper; use SL::AM; use SL::Common; use SL::DBUtils; +use SL::MoreCommon; +use Data::Dumper; sub invoice_details { $main::lxdebug->enter_sub(); @@ -481,45 +482,50 @@ sub customer_details { sub post_invoice { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_; # connect to database, turn off autocommit - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); - my ($query, $sth, $null, $project_id, $deliverydate, @values); + my ($query, $sth, $null, $project_id, @values); my $exchangerate = 0; - ($null, $form->{employee_id}) = split(/--/, $form->{employee}); - unless ($form->{employee_id}) { + if (!$form->{employee_id}) { $form->get_employee($dbh); } + + $form->{defaultcurrency} = $form->get_default_currency($myconfig); ($null, $form->{department_id}) = split(/--/, $form->{department}); my $all_units = AM->retrieve_units($myconfig, $form); - if ($form->{id}) { - - &reverse_invoice($dbh, $form); + if (!$payments_only) { + if ($form->{id}) { + &reverse_invoice($dbh, $form); - } else { - $query = qq|SELECT nextval('glid')|; - ($form->{"id"}) = selectrow_query($form, $dbh, $query); + } else { + $query = qq|SELECT nextval('glid')|; + ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; - do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); + $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; + do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); - if (!$form->{invnumber}) { - $form->{invnumber} = - $form->update_defaults($myconfig, $form->{type} eq "credit_note" ? - "cnnumber" : "invnumber", $dbh); + if (!$form->{invnumber}) { + $form->{invnumber} = + $form->update_defaults($myconfig, $form->{type} eq "credit_note" ? + "cnnumber" : "invnumber", $dbh); + } } } my ($netamount, $invoicediff) = (0, 0); my ($amount, $linetotal, $lastincomeaccno); - if ($form->{currency} eq $form->{defaultcurrency}) { + my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|); + my $defaultcurrency = (split m/:/, $currencies)[0]; + + if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { $exchangerate = @@ -546,6 +552,10 @@ sub post_invoice { my $basefactor; my $basqty; + $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1; + $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1; + $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1; + if ($form->{storno}) { $form->{"qty_$i"} *= -1; } @@ -639,6 +649,8 @@ sub post_invoice { $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces); + next if $payments_only; + if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) { # adjust parts onhand quantity @@ -674,15 +686,15 @@ sub post_invoice { # get pricegroup_id and save it ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); $pricegroup_id *= 1; - my $subtotal = $form->{"subtotal_$i"} * 1; # save detail record in invoice table $query = qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty, sellprice, fxsellprice, discount, allocated, assemblyitem, unit, deliverydate, project_id, serialnumber, pricegroup_id, - ordnumber, transdate, cusordnumber, base_qty, subtotal) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; + ordnumber, transdate, cusordnumber, base_qty, subtotal, + marge_percent, marge_total, lastcost) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"}, @@ -691,7 +703,9 @@ sub post_invoice { $form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, conv_i($pricegroup_id), $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), - $form->{"cusordnumber_$i"}, $baseqty, $subtotal); + $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', + $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, + $form->{"lastcost_$i"}); do_query($form, $dbh, $query, @values); if ($form->{lizenzen} && $form->{"licensenumber_$i"}) { @@ -768,7 +782,7 @@ sub post_invoice { $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1; # update exchangerate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, $form->{exchangerate}, 0); } @@ -778,11 +792,10 @@ sub post_invoice { foreach my $trans_id (keys %{ $form->{amount} }) { foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { next unless ($form->{expense_inventory} =~ /$accno/); - if ( - ($form->{amount}{$trans_id}{$accno} = - $form->round_amount($form->{amount}{$trans_id}{$accno}, 2) - ) != 0 - ) { + + $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2); + + if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, @@ -794,11 +807,9 @@ sub post_invoice { } foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { - if ( - ($form->{amount}{$trans_id}{$accno} = - $form->round_amount($form->{amount}{$trans_id}{$accno}, 2) - ) != 0 - ) { + $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2); + + if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, @@ -831,7 +842,7 @@ sub post_invoice { $exchangerate = 0; - if ($form->{currency} eq $form->{defaultcurrency}) { + if ($form->{currency} eq $defaultcurrency) { $form->{"exchangerate_$i"} = 1; } else { $exchangerate = @@ -885,12 +896,28 @@ sub post_invoice { $diff = 0; # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0); } } + + } else { # if (!$form->{storno}) + $form->{marge_total} *= -1; + } + + if ($payments_only) { + $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); + + if (!$provided_dbh) { + $dbh->commit(); + $dbh->disconnect(); + } + + $main::lxdebug->leave_sub(); + return; } # record exchange rate differences and gains/losses @@ -914,29 +941,6 @@ sub post_invoice { $amount = $netamount + $tax; - # set values which could be empty to 0 - my $datepaid = conv_date($form->{paid}); - my $duedate = conv_date($form->{duedate}); - $deliverydate = conv_date($form->{deliverydate}); - - # fill in subject if there is none - $form->{subject} = qq|$form->{label} $form->{invnumber}| - unless $form->{subject}; - - # if there is a message stuff it into the intnotes - my $cc = "Cc: $form->{cc}\\r\n" if $form->{cc}; - my $bcc = "Bcc: $form->{bcc}\\r\n" if $form->{bcc}; - my $now = scalar localtime; - $form->{intnotes} .= qq|\r -\r| if $form->{intnotes}; - - $form->{intnotes} .= qq|[email]\r -Date: $now -To: $form->{email}\r -$cc${bcc}Subject: $form->{subject}\r -\r -Message: $form->{message}\r| if $form->{message}; - # save AR record $query = qq|UPDATE ar set invnumber = ?, @@ -972,9 +976,12 @@ Message: $form->{message}\r| if $form->{message}; employee_id = ?, salesman_id = ?, storno = ?, + storno_id = ?, globalproject_id = ?, cp_id = ?, - transaction_description = ? + transaction_description = ?, + marge_total = ?, + marge_percent = ? WHERE id = ?|; @values = ($form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"}, conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), @@ -987,11 +994,20 @@ Message: $form->{message}\r| if $form->{message}; conv_i($form->{"shipto_id"}), conv_i($form->{"delivery_customer_id"}), conv_i($form->{"delivery_vendor_id"}), conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), - $form->{"storno"} ? 't' : 'f', conv_i($form->{"globalproject_id"}), + $form->{"storno"} ? 't' : 'f', conv_i($form->{storno_id}), conv_i($form->{"globalproject_id"}), conv_i($form->{"cp_id"}), $form->{transaction_description}, + $form->{marge_total} * 1, $form->{marge_percent} * 1, conv_i($form->{"id"})); do_query($form, $dbh, $query, @values); - + + if($form->{"formname"} eq "credit_note") { + for my $i (1 .. $form->{paidaccounts}) { + $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|; + @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"})); + do_query($form, $dbh, $query, @values); + } + } + if ($form->{storno}) { $query = qq!UPDATE ar SET @@ -1016,125 +1032,122 @@ Message: $form->{message}\r| if $form->{message}; Common::webdav_folder($form) if ($main::webdav); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = 1; + if (!$provided_dbh) { + $dbh->commit(); + $dbh->disconnect(); + } $main::lxdebug->leave_sub(); return $rc; } -sub post_payment { - $main::lxdebug->enter_sub() and my ($self, $myconfig, $form, $locale) = @_; +sub _delete_payments { + $main::lxdebug->enter_sub(); - # connect to database, turn off autocommit - my $dbh = $form->dbconnect_noauto($myconfig); + my ($self, $form, $dbh) = @_; - $form->{datepaid} = $form->{invdate}; + my @delete_oids; - # total payments, don't move we need it here - for my $i ( 1 .. $form->{paidaccounts} ) { - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{"paid_$i"} *= -1 if $form->{type} eq "credit_note"; - $form->{"paid"} += $form->{"paid_$i"}; - $form->{"datepaid"} = $form->{"datepaid_$i"} if $form->{"datepaid_$i"}; + # Delete old payment entries from acc_trans. + my $query = + qq|SELECT oid + FROM acc_trans + WHERE (trans_id = ?) AND fx_transaction + + UNION + + SELECT at.oid + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|; + push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); + + $query = + qq|SELECT at.oid + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id = ?) + AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) + ORDER BY at.oid + OFFSET 1|; + push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + + if (@delete_oids) { + $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + do_query($form, $dbh, $query); } - $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); + $main::lxdebug->leave_sub(); +} - # record payments and offsetting AR - for my $i (1 .. $form->{paidaccounts}) { - if ($form->{"paid_$i"}) { +sub post_payment { + $main::lxdebug->enter_sub(); - my ($accno) = split /--/, $form->{"AR_paid_$i"}; - $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"}); - $form->{datepaid} = $form->{"datepaid_$i"}; + my ($self, $myconfig, $form, $locale) = @_; - $exchangerate = 0; - if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) { - $form->{"exchangerate_$i"} = 1; - } else { - $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy'); - $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); - } + # connect to database, turn off autocommit + my $dbh = $form->dbconnect_noauto($myconfig); - # record AR - $amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2); + my (%payments, $old_form, $row, $item, $query, %keep_vars); - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) - AND (amount = ?) AND (transdate = ?)|; - do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, conv_date($form->{"datepaid_$i"})); - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"}, conv_i($form->{"globalproject_id"}), $accno); + $old_form = save_form(); - # record payment - $form->{"paid_$i"} *= -1; + # Delete all entries in acc_trans from prior payments. + $self->_delete_payments($form, $dbh); - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) - AND (amount = ?) AND (transdate = ?) AND (source = ?) AND (memo = ?)|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"}); + # Save the new payments the user made before cleaning up $form. + map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form }; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"}, - conv_i($form->{"globalproject_id"}), $accno); + # Clean up $form so that old content won't tamper the results. + %keep_vars = map { $_, 1 } qw(login password id); + map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form }; - # gain/loss - $amount = $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * $form->{"exchangerate_$i"}; - $form->{fx}{ $form->{($amount > 0 ? 'fxgain_accno' : 'fxloss_accno')} }{ $form->{"datepaid_$i"} } += $amount; + # Retrieve the invoice from the database. + $self->retrieve_invoice($myconfig, $form); - $diff = 0; + # Set up the content of $form in the way that IS::post_invoice() expects. + $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); - # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0); - } + for $row (1 .. scalar @{ $form->{invoice_details} }) { + $item = $form->{invoice_details}->[$row - 1]; - } + map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount); + + map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item }; } - # record exchange rate differences and gains/losses - foreach my $accno (keys %{ $form->{fx} }) { - foreach my $transdate (keys %{ $form->{fx}{$accno} }) { + $form->{rowcount} = scalar @{ $form->{invoice_details} }; - if ($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) { # '=' is no typo, it's an assignment - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)) - AND (amount = ?) AND (transdate = ?) AND (cleared = ?) AND (fx_transaction = ?)|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1); - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1, conv_i($form->{"globalproject_id"}), $accno); - } + delete @{$form}{qw(invoice_details paidaccounts storno paid)}; - } - } + # Restore the payment options from the user input. + map { $form->{$_} = $payments{$_} } keys %payments; - # save AR record - delete $form->{datepaid} unless $form->{paid}; + # Get the AR accno (which is normally done by Form::create_links()). + $query = + qq|SELECT c.accno + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id = ?) + AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) + ORDER BY at.oid + LIMIT 1|; - my $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{"paid"}, conv_date($form->{"datepaid"}), conv_i($form->{"id"})); + ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); - my $rc = $dbh->commit; - $dbh->disconnect; + # Post the new payments. + $self->post_invoice($myconfig, $form, $dbh, 1); + + restore_form($old_form); - $main::lxdebug->leave_sub() and return $rc; + my $rc = $dbh->commit(); + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); + + return $rc; } sub process_assembly { @@ -1192,19 +1205,9 @@ sub cogs { my $taxzone_id = $form->{"taxzone_id"} * 1; my $query = qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, - - c1.accno AS inventory_accno, - c1.new_chart_id AS inventory_new_chart, - date($transdate) - c1.valid_from AS inventory_valid, - - c2.accno AS income_accno, - c2.new_chart_id AS income_new_chart, - date($transdate) - c2.valid_from AS income_valid, - - c3.accno AS expense_accno, - c3.new_chart_id AS expense_new_chart, - date($transdate) - c3.valid_from AS expense_valid - + c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid, + c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from AS income_valid, + c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid FROM invoice i, parts p LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id) @@ -1395,6 +1398,7 @@ sub retrieve_invoice { a.employee_id, a.salesman_id, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type, a.transaction_description, + a.marge_total, a.marge_percent, e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) @@ -1460,7 +1464,7 @@ sub retrieve_invoice { i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, - i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, + i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost, p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, @@ -1524,7 +1528,7 @@ sub retrieve_invoice { (SELECT tk.tax_id FROM taxkeys tk WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) - AND startdate <= $transdate + AND startdate <= date($transdate) ORDER BY startdate DESC LIMIT 1) ORDER BY c.accno|; @@ -1711,7 +1715,7 @@ sub get_customer { qq|SELECT tk.tax_id, t.rate FROM taxkeys tk LEFT JOIN tax t ON tk.tax_id = t.id - WHERE (tk.chart_id = ?) AND (startdate <= ?) + WHERE (tk.chart_id = ?) AND (startdate <= date(?)) ORDER BY tk.startdate DESC LIMIT 1|; my ($tax_id, $rate) = @@ -1777,7 +1781,7 @@ sub retrieve_item { my $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice, - p.listprice, p.inventory_accno_id, + p.listprice, p.inventory_accno_id, p.lastcost, c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, @@ -1868,7 +1872,7 @@ sub retrieve_item { ORDER BY startdate DESC LIMIT 1) ORDER BY c.accno|; - @values = ($accno_id, $transdate); + @values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate); $stw = $dbh->prepare($query); $stw->execute(@values) || $form->dberror($query); @@ -2144,9 +2148,9 @@ sub has_storno { sub is_storno { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form, $table) = @_; + my ($self, $myconfig, $form, $table, $id) = @_; - $main::lxdebug->leave_sub() and return 0 unless ($form->{id}); + $main::lxdebug->leave_sub() and return 0 unless ($id); # make sure there's no funny stuff in $table # ToDO: die when this happens and throw an error @@ -2155,7 +2159,7 @@ sub is_storno { my $dbh = $form->dbconnect($myconfig); my $query = qq|SELECT storno FROM $table WHERE id = ?|; - my ($result) = selectrow_query($form, $dbh, $query, $form->{id}); + my ($result) = selectrow_query($form, $dbh, $query, $id); $dbh->disconnect();