X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=918ca19094b561734f573a330989e416948b6970;hb=806a4de799c8b69838c4767539279298a42fcf57;hp=1ddadc2f52d6e67537225c4bd941ddcdd5d75766;hpb=c1a32d6241777c611e930a417ed982bb1a031798;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index 1ddadc2f5..918ca1909 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -34,8 +34,6 @@ package IS; -#use strict; - use List::Util qw(max); use SL::AM; @@ -44,10 +42,15 @@ use SL::CVar; use SL::Common; use SL::DBUtils; use SL::DO; +use SL::GenericTranslations; use SL::MoreCommon; use SL::IC; +use SL::IO; +use SL::TransNumber; use Data::Dumper; +use strict; + sub invoice_details { $main::lxdebug->enter_sub(); @@ -62,7 +65,8 @@ sub invoice_details { my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|; ($form->{terms}) = selectrow_query($form, $dbh, $query); - my (@project_ids, %projectnumbers); + my (@project_ids, %projectnumbers, %projectdescriptions); + $form->{TEMPLATE_ARRAYS} = {}; push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); @@ -86,19 +90,22 @@ sub invoice_details { } if (@project_ids) { - $query = "SELECT id, projectnumber FROM project WHERE id IN (" . + $query = "SELECT id, projectnumber, description FROM project WHERE id IN (" . join(", ", map({ "?" } @project_ids)) . ")"; $sth = $dbh->prepare($query); $sth->execute(@project_ids) || $form->dberror($query . " (" . join(", ", @project_ids) . ")"); while (my $ref = $sth->fetchrow_hashref()) { $projectnumbers{$ref->{id}} = $ref->{projectnumber}; + $projectdescriptions{$ref->{id}} = $ref->{description}; } $sth->finish(); } $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}}; + $form->{"globalprojectdescription"} = + $projectdescriptions{$form->{"globalproject_id"}}; my $tax = 0; my $item; @@ -137,25 +144,32 @@ sub invoice_details { IC->prepare_parts_for_printing(); + my $ic_cvar_configs = CVar->get_configs(module => 'IC'); + my @arrays = qw(runningnumber number description longdescription qty ship unit bin deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil partnotes serialnumber reqdate sellprice listprice netprice discount p_discount discount_sub nodiscount_sub - linetotal nodiscount_linetotal tax_rate projectnumber + linetotal nodiscount_linetotal tax_rate projectnumber projectdescription price_factor price_factor_name partsgroup); - my @tax_arrays = - qw(taxbase tax taxdescription taxrate taxnumber); + push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; + + my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber); + + my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo); + + map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays); foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; if ($item->[1] ne $sameitem) { - push(@{ $form->{description} }, qq|$item->[1]|); + push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|); $sameitem = $item->[1]; - map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); @@ -179,40 +193,41 @@ sub invoice_details { my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 }; - push @{ $form->{runningnumber} }, $position; - push @{ $form->{number} }, $form->{"partnumber_$i"}; - push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"}; - push @{ $form->{bin} }, $form->{"bin_$i"}; - push @{ $form->{"partnotes"} }, $form->{"partnotes_$i"}; - push @{ $form->{description} }, $form->{"description_$i"}; - push @{ $form->{longdescription} }, $form->{"longdescription_$i"}; - push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}); - push @{ $form->{unit} }, $form->{"unit_$i"}; - push @{ $form->{deliverydate_oe} }, $form->{"deliverydate_$i"}; - push @{ $form->{sellprice} }, $form->{"sellprice_$i"}; - push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"}; - push @{ $form->{transdate_oe} }, $form->{"transdate_$i"}; - push @{ $form->{invnumber} }, $form->{"invnumber"}; - push @{ $form->{invdate} }, $form->{"invdate"}; - push @{ $form->{price_factor} }, $price_factor->{formatted_factor}; - push @{ $form->{price_factor_name} }, $price_factor->{description}; - push @{ $form->{partsgroup} }, $form->{"partsgroup_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position; + push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}); + push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"}; + push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"}; + push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor}; + push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description}; + push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"}; if ($form->{lizenzen}) { if ($form->{"licensenumber_$i"}) { $query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|; my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"})); - push(@{ $form->{licensenumber} }, $licensenumber); - push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0)); + push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber); + push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0)); } else { - push(@{ $form->{licensenumber} }, ""); - push(@{ $form->{validuntil} }, ""); + push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, ""); + push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, ""); } } # listprice - push(@{ $form->{listprice} }, $form->{"listprice_$i"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"}); my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); my ($dec) = ($sellprice =~ /\.(\d+)/); @@ -226,12 +241,12 @@ sub invoice_details { my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2); $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2); - push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : ''; + push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : ''; $linetotal = ($linetotal != 0) ? $linetotal : ''; - push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : ''; - push @{ $form->{p_discount} }, $form->{"discount_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : ''; + push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"}; $form->{total} += $linetotal; $form->{nodiscount_total} += $nodiscount_linetotal; @@ -243,26 +258,27 @@ sub invoice_details { } if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) { - push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2); - push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2); $discount_subtotal = 0; $nodiscount_subtotal = 0; $subtotal_header = 0; } else { - push @{ $form->{discount_sub} }, ""; - push @{ $form->{nodiscount_sub} }, ""; + push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, ""; + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, ""; } if (!$form->{"discount_$i"}) { $nodiscount += $linetotal; } - push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2); - push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2); - push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}); + push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}); + push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}}); @taxaccounts = split(/ /, $form->{"taxaccounts_$i"}); $taxrate = 0; @@ -304,7 +320,7 @@ sub invoice_details { } } my $tax_rate = $taxrate * 100; - push(@{ $form->{tax_rate} }, qq|$tax_rate|); + push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|); if ($form->{"assembly_$i"}) { $sameitem = ""; @@ -327,46 +343,46 @@ sub invoice_details { while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) { - map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--"; - push(@{ $form->{description} }, $sameitem); + push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem); } map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description); - push(@{ $form->{description} }, + push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"} ) . qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|); - map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } $sth->finish; } + + map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs }; } } foreach my $item (sort keys %taxaccounts) { - push(@{ $form->{taxbase} }, - $form->format_amount($myconfig, $taxbase{$item}, 2)); - $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); - push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2)); - push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%}); - push(@{ $form->{taxrate} }, - $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); - push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2)); + push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2)); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%}); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"}); } for my $i (1 .. $form->{paidaccounts}) { if ($form->{"paid_$i"}) { - push(@{ $form->{payment} }, $form->{"paid_$i"}); my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"}); - push(@{ $form->{paymentaccount} }, $description); - push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"}); - push(@{ $form->{paymentsource} }, $form->{"source_$i"}); - push(@{ $form->{paymentmemo} }, $form->{"memo_$i"}); + + push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description); + push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"}); + push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"}); $form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"}); } @@ -420,6 +436,8 @@ sub customer_details { # connect to database my $dbh = $form->dbconnect($myconfig); + my $language_id = $form->{language_id}; + # get contact id, set it if nessessary $form->{cp_id} *= 1; @@ -481,6 +499,12 @@ sub customer_details { 'trans_id' => $form->{customer_id}); map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables }; + $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh, + 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'), + 'language_id' => $language_id, + 'allow_fallback' => 1); + + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -492,11 +516,14 @@ sub post_invoice { my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_; # connect to database, turn off autocommit - my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); + my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh; my ($query, $sth, $null, $project_id, @values); my $exchangerate = 0; + my $ic_cvar_configs = CVar->get_configs(module => 'IC', + dbh => $dbh); + if (!$form->{employee_id}) { $form->get_employee($dbh); } @@ -512,6 +539,9 @@ sub post_invoice { &reverse_invoice($dbh, $form); } else { + my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1); + $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique; + $query = qq|SELECT nextval('glid')|; ($form->{"id"}) = selectrow_query($form, $dbh, $query); @@ -535,7 +565,7 @@ sub post_invoice { if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { - $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy'); + $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy'); } $form->{exchangerate} = @@ -551,6 +581,7 @@ sub post_invoice { my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} }; my $price_factor; + $form->{amount} = {}; $form->{amount_cogs} = {}; foreach my $i (1 .. $form->{rowcount}) { @@ -564,8 +595,8 @@ sub post_invoice { my $baseqty; $form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1; - $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1; - $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1; + $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1; + $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1; if ($form->{storno}) { $form->{"qty_$i"} *= -1; @@ -669,18 +700,20 @@ sub post_invoice { ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); $pricegroup_id *= 1; + my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|); + # save detail record in invoice table $query = - qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty, + qq|INSERT INTO invoice (id, 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, marge_percent, marge_total, lastcost, price_factor_id, price_factor, marge_price_factor) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|; - @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), + @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', @@ -688,7 +721,7 @@ sub post_invoice { $form->{"serialnumber_$i"}, conv_i($pricegroup_id), $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', - $form->{"marge_percent_$i"}, $form->{"marge_total_$i"}, + $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, $form->{"lastcost_$i"}, conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"})); @@ -701,11 +734,18 @@ sub post_invoice { @values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"})); do_query($form, $dbh, $query, @values); } + + CVar->save_custom_variables(module => 'IC', + sub_module => 'invoice', + trans_id => $invoice_id, + configs => $ic_cvar_configs, + variables => $form, + name_prefix => 'ic_', + name_postfix => "_$i", + dbh => $dbh); } } - $form->{datepaid} = $form->{invdate}; - # total payments, don't move we need it here for my $i (1 .. $form->{paidaccounts}) { if ($form->{type} eq "credit_note") { @@ -917,9 +957,11 @@ sub post_invoice { $form->{marge_total} *= -1; } + IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh); + 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})); + $query = qq|UPDATE ar SET paid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); if (!$provided_dbh) { $dbh->commit(); @@ -957,7 +999,7 @@ sub post_invoice { $query = qq|UPDATE ar set invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, orddate = ?, quodate = ?, customer_id = ?, - amount = ?, netamount = ?, paid = ?, datepaid = ?, + amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, @@ -966,11 +1008,11 @@ sub post_invoice { cp_id = ?, marge_total = ?, marge_percent = ?, globalproject_id = ?, delivery_customer_id = ?, transaction_description = ?, delivery_vendor_id = ?, - donumber = ? + donumber = ? WHERE id = ?|; @values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"}, conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}), - $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}), + $amount, $netamount, $form->{"paid"}, conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"}, $form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"}, $form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f', @@ -979,17 +1021,10 @@ sub post_invoice { conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent}, conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}), $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}), - $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb + $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb conv_i($form->{"id"})); do_query($form, $dbh, $query, @values); - if($form->{"formname"} eq "credit_note") { - for my $i (1 .. $form->{rowcount}) { - $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 = @@ -1061,34 +1096,34 @@ sub _delete_payments { my ($self, $form, $dbh) = @_; - my @delete_oids; + my @delete_acc_trans_ids; # Delete old payment entries from acc_trans. my $query = - qq|SELECT oid + qq|SELECT acc_trans_id FROM acc_trans WHERE (trans_id = ?) AND fx_transaction UNION - SELECT at.oid + SELECT at.acc_trans_id 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})); + push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id})); $query = - qq|SELECT at.oid + qq|SELECT at.acc_trans_id 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 + ORDER BY at.acc_trans_id OFFSET 1|; - push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id})); + push @delete_acc_trans_ids, 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|)|; + if (@delete_acc_trans_ids) { + $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|; do_query($form, $dbh, $query); } @@ -1145,7 +1180,7 @@ sub post_payment { 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 + ORDER BY at.acc_trans_id LIMIT 1|; ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); @@ -1220,7 +1255,7 @@ sub cogs { my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date"; my $taxzone_id = $form->{"taxzone_id"} * 1; my $query = - qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, + qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor, 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 @@ -1246,7 +1281,7 @@ sub cogs { # total expenses and inventory # sellprice is the cost of the item - my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2); + my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2); if (!$main::eur) { $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno}; @@ -1342,6 +1377,20 @@ sub delete_invoice { my @values = (conv_i($form->{id})); + # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder + # zurückgesetzt werden. Vgl: + # id | storno | storno_id | paid | amount + #----+--------+-----------+---------+----------- + # 18 | f | | 0.00000 | 119.00000 + # ZU: + # 18 | t | | 119.00000 | 119.00000 + # + if($form->{storno}){ + # storno_id auslesen und korrigieren + my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values); + do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id); + } + # delete AR record do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values); @@ -1369,7 +1418,7 @@ sub retrieve_invoice { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->get_standard_dbh; my ($sth, $ref, $query); @@ -1454,6 +1503,7 @@ sub retrieve_invoice { 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, + i.id AS invoice_id, i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate, i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost, i.price_factor_id, i.price_factor, i.marge_price_factor, @@ -1475,6 +1525,15 @@ sub retrieve_invoice { $sth = prepare_execute_query($form, $dbh, $query, $id); while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { + # Retrieve custom variables. + my $cvars = CVar->get_custom_variables(dbh => $dbh, + module => 'IC', + sub_module => 'invoice', + trans_id => $ref->{invoice_id}, + ); + map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; + delete $ref->{invoice_id}; + map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"}; delete($ref->{"part_inventory_accno_id"}); @@ -1534,7 +1593,6 @@ sub retrieve_invoice { } my $rc = $dbh->commit; - $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -1547,7 +1605,7 @@ sub get_customer { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $dateformat = $myconfig->{dateformat}; $dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/; @@ -1584,6 +1642,9 @@ sub get_customer { WHERE c.id = ?|; push @values, $cid; $ref = selectfirst_hashref_query($form, $dbh, $query, @values); + + delete $ref->{salesman_id} if !$ref->{salesman_id}; + map { $form->{$_} = $ref->{$_} } keys %$ref; $query = @@ -1680,8 +1741,6 @@ sub get_customer { $form->{rowcount} = $i if ($i && !$form->{type}); } - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -1707,8 +1766,8 @@ sub retrieve_item { #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) { - $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; - push @values, $form->{"partnumber_$i"}; + $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|; + push @values, $form->{"partnumber_$i"}; } if ($form->{"description_$i"}) { @@ -1892,6 +1951,16 @@ sub retrieve_item { } } $sth->finish; + + foreach my $item (@{ $form->{item_list} }) { + my $custom_variables = CVar->get_custom_variables(module => 'IC', + trans_id => $item->{id}, + dbh => $dbh, + ); + + map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables }; + } + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -1922,14 +1991,16 @@ sub get_pricegroups_for_parts { $id = $form->{"id_$i"}; if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) { - $id = $form->{"new_id_$i"}; } - my ($price, $selectedpricegroup_id) = split(/--/, - $form->{"sellprice_pg_$i"}); + my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"}); my $pricegroup_old = $form->{"pricegroup_old_$i"}; + + # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically + my $sellprice = $form->{"sellprice_$i"}; + my $pricegroup_id = $form->{"pricegroup_id_$i"}; $form->{"new_pricegroup_$i"} = $selectedpricegroup_id; $form->{"old_pricegroup_$i"} = $pricegroup_old; @@ -2000,21 +2071,35 @@ sub get_pricegroups_for_parts { # if there is an exchange rate change price if (($form->{exchangerate} * 1) != 0) { - $pkr->{price} /= $form->{exchangerate}; } $pkr->{price} *= $form->{"basefactor_$i"}; - $pkr->{price} *= $basefactor; - $pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5); if ($selectedpricegroup_id eq undef) { - if ($pkr->{pricegroup_id} eq $form->{customer_klass}) { - + # new entries in article list, either old invoice was loaded (edit) or a new article was added + # Case A: open old invoice, no pricegroup selected + # Case B: add new article to invoice, no pricegroup selected + + # to distinguish case A and B the variable pricegroup_id_$i is used + # for new articles this variable isn't defined, for loaded articles it is + # sellprice can't be used, as it already has 0,00 set + + if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) { + # Case A $pkr->{selected} = ' selected'; + } elsif ($pkr->{pricegroup_id} eq $form->{customer_klass} + and not defined $form->{"pricegroup_id_$i"} + and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice + # for the case where pricegroup prices haven't been set + ) { + # Case B: use default pricegroup of customer + + $pkr->{selected} = ' selected'; # unless $form->{selected}; + # no customer pricesgroup set if ($pkr->{price} == $pkr->{default_sellprice}) { @@ -2023,29 +2108,36 @@ sub get_pricegroups_for_parts { } else { # this sub should not set anything and only return. --sschoeling, 20090506 -# $form->{"sellprice_$i"} = $pkr->{price}; +# is this correct? put in again... -- grichardson 20110119 + $form->{"sellprice_$i"} = $pkr->{price}; } - } elsif ($pkr->{price} == $pkr->{default_sellprice}) { + } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) { $pkr->{price} = $form->{"sellprice_$i"}; $pkr->{selected} = ' selected'; } } + # existing article: pricegroup or price changed if ($selectedpricegroup_id or $selectedpricegroup_id == 0) { if ($selectedpricegroup_id ne $pricegroup_old) { + # pricegroup has changed if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) { $pkr->{selected} = ' selected'; } - } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) { + } elsif ( ($form->parse_amount($myconfig, $price_new) + != $form->parse_amount($myconfig, $form->{"sellprice_$i"})) + and ($price_new ne 0) and defined $price_new) { + # sellprice has changed + # when loading existing invoices $price_new is NULL if ($pkr->{pricegroup_id} == 0) { $pkr->{price} = $form->{"sellprice_$i"}; $pkr->{selected} = ' selected'; } } elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) { + # neither sellprice nor pricegroup changed $pkr->{selected} = ' selected'; - if ( ($pkr->{pricegroup_id} == 0) - and ($pkr->{price} == $form->{"sellprice_$i"})) { + if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) { # $pkr->{price} = $form->{"sellprice_$i"}; } else { $pkr->{price} = $form->{"sellprice_$i"}; @@ -2113,4 +2205,21 @@ sub is_storno { return $result; } +sub get_standard_accno_current_assets { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->dbconnect($myconfig); + + my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|; + my ($result) = selectrow_query($form, $dbh, $query); + + $dbh->disconnect(); + + $main::lxdebug->leave_sub(); + + return $result; +} + 1;