X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=c9a44281d84f0aa95e511e6e108b7078017f9297;hb=1a106c11e3f0e22b84e92d6f52c232b5b47d3d99;hp=e17efc6e6da991880d9056b1c6a3eb00682d2b55;hpb=59f7af73667e284d8ee23ff78ba9433185bab7c6;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index e17efc6e6..c9a44281d 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -45,6 +45,8 @@ use SL::DO; use SL::GenericTranslations; use SL::MoreCommon; use SL::IC; +use SL::IO; +use SL::TransNumber; use Data::Dumper; use strict; @@ -57,13 +59,13 @@ sub invoice_details { $form->{duedate} ||= $form->{invdate}; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $sth; 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"}); @@ -88,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; @@ -143,10 +148,10 @@ sub invoice_details { my @arrays = qw(runningnumber number description longdescription qty ship unit bin - deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil + deliverydate_oe ordnumber_oe transdate_oe 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); push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; @@ -196,9 +201,11 @@ sub invoice_details { 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}->{qty_nofmt} }, $form->{"qty_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$i"}; - push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"deliverydate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{deliverydate_oe} }, $form->{"reqdate_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $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"}; @@ -207,22 +214,7 @@ sub invoice_details { 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->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber); - push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0)); - - } else { - push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, ""); - push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, ""); - } - } - - # listprice - push(@{ $form->{TEMPLATE_ARRAYS}->{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+)/); @@ -236,12 +228,14 @@ 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->{TEMPLATE_ARRAYS}->{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) : ''; + push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : ''; $linetotal = ($linetotal != 0) ? $linetotal : ''; - push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : ''; - push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : ''; + push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : ''; + push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"}; $form->{total} += $linetotal; $form->{nodiscount_total} += $nodiscount_linetotal; @@ -253,8 +247,10 @@ sub invoice_details { } if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) { - 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); + push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal; + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal; $discount_subtotal = 0; $nodiscount_subtotal = 0; @@ -269,10 +265,13 @@ sub invoice_details { $nodiscount += $linetotal; } - 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->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact; + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2); + push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal; - push(@{ $form->{TEMPLATE_ARRAYS}->{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; @@ -354,7 +353,9 @@ sub invoice_details { $sth->finish; } - map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs }; + push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, + CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_) + for @{ $ic_cvar_configs }; } } @@ -362,8 +363,11 @@ sub invoice_details { $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2)); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item}); push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2)); + push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount ); push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100)); + push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $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"}); } @@ -382,10 +386,12 @@ sub invoice_details { } } if($form->{taxincluded}) { - $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2); + $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2); + $form->{subtotal_nofmt} = $form->{total} - $tax; } else { - $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2); + $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2); + $form->{subtotal_nofmt} = $form->{total}; } $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2); @@ -403,8 +409,6 @@ sub invoice_details { $form->{username} = $myconfig->{name}; - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -428,7 +432,7 @@ sub customer_details { my ($self, $myconfig, $form, @wanted_vars) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $language_id = $form->{language_id}; @@ -499,8 +503,6 @@ sub customer_details { 'allow_fallback' => 1); - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -510,7 +512,7 @@ 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; @@ -523,8 +525,12 @@ sub post_invoice { } $form->{defaultcurrency} = $form->get_default_currency($myconfig); - - ($null, $form->{department_id}) = split(/--/, $form->{department}); + # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr + # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen + # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011 + if (!$form->{department_id}){ + ($null, $form->{department_id}) = split(/--/, $form->{department}); + } my $all_units = AM->retrieve_units($myconfig, $form); @@ -533,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); @@ -556,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} = @@ -586,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; @@ -680,10 +689,10 @@ sub post_invoice { if ($form->{"assembly_$i"}) { # record assembly item as allocated - &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty); + &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty); } else { - $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i); + $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i); } } @@ -712,20 +721,12 @@ 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"})); do_query($form, $dbh, $query, @values); - if ($form->{lizenzen} && $form->{"licensenumber_$i"}) { - $query = - qq|INSERT INTO licenseinvoice (trans_id, license_id) - VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|; - @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, @@ -879,6 +880,12 @@ sub post_invoice { if (!$form->{storno}) { for my $i (1 .. $form->{paidaccounts}) { + if ($form->{"acc_trans_id_$i"} + && $payments_only + && ($::lx_office_conf{features}->{payments_changeable} == 0)) { + next; + } + next if ($form->{"paid_$i"} == 0); my ($accno) = split(/--/, $form->{"AR_paid_$i"}); @@ -909,13 +916,14 @@ sub post_invoice { # record payment $form->{"paid_$i"} *= -1; + 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, source, memo, taxkey, project_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, + qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; @values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, - $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); + $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id); do_query($form, $dbh, $query, @values); # exchangerate difference @@ -948,14 +956,13 @@ 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(); - $dbh->disconnect(); - } + $dbh->commit if !$provided_dbh; $main::lxdebug->leave_sub(); return; @@ -988,7 +995,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 = ?, @@ -997,11 +1004,11 @@ sub post_invoice { cp_id = ?, marge_total = ?, marge_percent = ?, globalproject_id = ?, delivery_customer_id = ?, transaction_description = ?, delivery_vendor_id = ?, - donumber = ? + donumber = ?, invnumber_for_credit_note = ? 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', @@ -1010,17 +1017,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"}, $form->{"invnumber_for_credit_note"}, 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 = @@ -1044,7 +1044,7 @@ sub post_invoice { # save printed, emailed and queued $form->save_status($dbh); - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); # Link this record to the records it was created from. RecordLinks->create_links('dbh' => $dbh, @@ -1077,10 +1077,7 @@ sub post_invoice { 'table' => 'ar',); my $rc = 1; - if (!$provided_dbh) { - $dbh->commit(); - $dbh->disconnect(); - } + $dbh->commit if !$provided_dbh; $main::lxdebug->leave_sub(); @@ -1132,17 +1129,20 @@ sub post_payment { my ($self, $myconfig, $form, $locale) = @_; # connect to database, turn off autocommit - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->get_standard_dbh; + $dbh->begin_work; my (%payments, $old_form, $row, $item, $query, %keep_vars); $old_form = save_form(); # Delete all entries in acc_trans from prior payments. - $self->_delete_payments($form, $dbh); + if ($::lx_office_conf{features}->{payments_changeable} != 0) { + $self->_delete_payments($form, $dbh); + } # 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 }; + map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form }; # Clean up $form so that old content won't tamper the results. %keep_vars = map { $_, 1 } qw(login password id); @@ -1187,7 +1187,6 @@ sub post_payment { restore_form($old_form); my $rc = $dbh->commit(); - $dbh->disconnect(); $main::lxdebug->leave_sub(); @@ -1197,7 +1196,7 @@ sub post_payment { sub process_assembly { $main::lxdebug->enter_sub(); - my ($dbh, $form, $id, $totalqty) = @_; + my ($dbh, $myconfig, $form, $id, $totalqty) = @_; my $query = qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit, @@ -1218,11 +1217,11 @@ sub process_assembly { $ref->{qty} *= $totalqty; if ($ref->{assembly}) { - &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty}); + &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty}); next; } else { if ($ref->{inventory_accno_id}) { - $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty}); + $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty}); } } @@ -1243,7 +1242,10 @@ sub process_assembly { sub cogs { $main::lxdebug->enter_sub(); - my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_; + # adjust allocated in table invoice according to FIFO princicple + # for a certain part with part_id $id + + my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_; $basefactor ||= 1; @@ -1251,7 +1253,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 @@ -1268,18 +1270,45 @@ sub cogs { my $allocated = 0; my $qty; +# all invoice entries of an example part: + +# id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno +# ---+----------+----------+-----------+-----------+-----------------+--------------+--------------- +# 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 +# 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50 +# 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50 +# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 +# 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50 + +# AND ((i.base_qty + i.allocated) < 0) filters out all but line with id=7, elsewhere i.base_qty + i.allocated has already reached 0 +# and all parts have been allocated + +# so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0 +# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20 + +# in this example there are still 4 unsold articles + + + # search all invoice entries for the part in question, adjusting "allocated" + # until the total number of sold parts has been reached + + # ORDER BY trans_id ensures FIFO + + while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) { $qty = $totalqty; } + # update allocated in invoice $form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty); # 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) { + if ( $::instance_conf->get_inventory_system eq 'perpetual' ) { + # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account $ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno}; # add to expense $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal; @@ -1348,14 +1377,6 @@ sub reverse_invoice { my @values = (conv_i($form->{id})); do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values); do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values); - - if ($form->{lizenzen}) { - $query = - qq|DELETE FROM licenseinvoice - WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|; - do_query($form, $dbh, $query, @values); - } - do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values); $main::lxdebug->leave_sub(); @@ -1364,15 +1385,30 @@ sub reverse_invoice { sub delete_invoice { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form, $spool) = @_; + my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->get_standard_dbh; + $dbh->begin_work; &reverse_invoice($dbh, $form); 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); @@ -1383,9 +1419,9 @@ sub delete_invoice { do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values); my $rc = $dbh->commit; - $dbh->disconnect; if ($rc) { + my $spool = $::lx_office_conf{paths}->{spool}; map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles; } @@ -1400,7 +1436,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); @@ -1435,15 +1471,17 @@ sub retrieve_invoice { a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id, 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.transaction_description, a.donumber, a.invnumber_for_credit_note, a.marge_total, a.marge_percent, - e.name AS employee, a.donumber + e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) WHERE a.id = ?|; $ref = selectfirst_hashref_query($form, $dbh, $query, $id); map { $form->{$_} = $ref->{$_} } keys %{ $ref }; + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); @@ -1489,7 +1527,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 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, - p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, + p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice, pr.projectnumber, pg.partsgroup, prg.pricegroup FROM invoice i @@ -1557,12 +1595,6 @@ sub retrieve_invoice { } - if ($form->{lizenzen}) { - $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|; - my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos})); - $ref->{lizenzen} = ""; - } - $ref->{qty} *= -1 if $form->{type} eq "credit_note"; chop $ref->{taxaccounts}; @@ -1571,11 +1603,10 @@ sub retrieve_invoice { } $sth->finish; - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); } my $rc = $dbh->commit; - $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -1588,7 +1619,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/; @@ -1724,8 +1755,6 @@ sub get_customer { $form->{rowcount} = $i if ($i && !$form->{type}); } - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -1735,7 +1764,7 @@ sub retrieve_item { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $i = $form->{rowcount}; @@ -1751,8 +1780,14 @@ 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"}; + } + + # Search for part ID overrides all other criteria. + if ($form->{"id_${i}"}) { + $where = qq|p.id = ?|; + @values = ($form->{"id_${i}"}); } if ($form->{"description_$i"}) { @@ -1820,6 +1855,19 @@ sub retrieve_item { WHERE $where|; my $sth = prepare_execute_query($form, $dbh, $query, @values); + my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription + FROM translation tr + WHERE tr.language_id = ? AND tr.parts_id = ?| ], + [ qq|SELECT tr.translation, tr.longdescription + FROM translation tr + WHERE tr.language_id IN + (SELECT id + FROM language + WHERE article_code = (SELECT article_code FROM language WHERE id = ?)) + AND tr.parts_id = ? + LIMIT 1| ] ); + map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries; + while (my $ref = $sth->fetchrow_hashref('NAME_lc')) { # In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn @@ -1887,55 +1935,24 @@ sub retrieve_item { $stw->finish; chop $ref->{taxaccounts}; + if ($form->{language_id}) { - $query = - qq|SELECT tr.translation, tr.longdescription - FROM translation tr - WHERE tr.language_id = ? AND tr.parts_id = ?|; - @values = (conv_i($form->{language_id}), conv_i($ref->{id})); - my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values); - if ($translation ne "") { + for my $spec (@translation_queries) { + do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id})); + my ($translation, $longdescription) = $spec->[1]->fetchrow_array; + next unless $translation; $ref->{description} = $translation; $ref->{longdescription} = $longdescription; - - } else { - $query = - qq|SELECT tr.translation, tr.longdescription - FROM translation tr - WHERE tr.language_id IN - (SELECT id - FROM language - WHERE article_code = (SELECT article_code FROM language WHERE id = ?)) - AND tr.parts_id = ? - LIMIT 1|; - @values = (conv_i($form->{language_id}), conv_i($ref->{id})); - my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values); - if ($translation ne "") { - $ref->{description} = $translation; - $ref->{longdescription} = $longdescription; - } + last; } } $ref->{onhand} *= 1; push @{ $form->{item_list} }, $ref; - - if ($form->{lizenzen}) { - if ($ref->{inventory_accno} > 0) { - $query = - qq|SELECT l.* - FROM license l - WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|; - my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id})); - while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) { - push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr; - } - $stw->finish; - } - } } $sth->finish; + $_->[1]->finish for @translation_queries; foreach my $item (@{ $form->{item_list} }) { my $custom_variables = CVar->get_custom_variables(module => 'IC', @@ -1946,8 +1963,6 @@ sub retrieve_item { map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables }; } - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -1963,7 +1978,7 @@ sub get_pricegroups_for_parts { my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; $form->{"PRICES"} = {}; @@ -1982,6 +1997,10 @@ sub get_pricegroups_for_parts { 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; @@ -2023,27 +2042,27 @@ sub get_pricegroups_for_parts { } my $query = - qq|SELECT + qq|SELECT + 0 as pricegroup_id, + sellprice AS default_sellprice, + '' AS pricegroup, + sellprice AS price, + 'selected' AS selected + FROM parts + WHERE id = ? + UNION ALL + SELECT pricegroup_id, - (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice, - (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup, + parts.sellprice AS default_sellprice, + pricegroup.pricegroup, price, '' AS selected FROM prices + LEFT JOIN parts ON parts.id = parts_id + LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id WHERE parts_id = ? - - UNION - - SELECT - 0 as pricegroup_id, - (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice, - '' AS pricegroup, - (SELECT DISTINCT sellprice FROM parts where id = ?) AS price, - 'selected' AS selected - FROM prices - ORDER BY pricegroup|; - my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id)); + my @values = (conv_i($id), conv_i($id)); my $pkq = prepare_execute_query($form, $dbh, $query, @values); while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) { @@ -2060,10 +2079,27 @@ sub get_pricegroups_for_parts { $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}) { @@ -2072,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"}; @@ -2111,8 +2154,6 @@ sub get_pricegroups_for_parts { $pkq->finish; } - $dbh->disconnect; - $main::lxdebug->leave_sub(); } @@ -2127,13 +2168,11 @@ sub has_storno { # ToDO: die when this happens and throw an error $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/); - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|; my ($result) = selectrow_query($form, $dbh, $query, $form->{id}); - $dbh->disconnect(); - $main::lxdebug->leave_sub(); return $result; @@ -2150,12 +2189,25 @@ sub is_storno { # ToDO: die when this happens and throw an error $main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/); - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh; my $query = qq|SELECT storno FROM $table WHERE id = ?|; my ($result) = selectrow_query($form, $dbh, $query, $id); - $dbh->disconnect(); + $main::lxdebug->leave_sub(); + + return $result; +} + +sub get_standard_accno_current_assets { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->get_standard_dbh; + + my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|; + my ($result) = selectrow_query($form, $dbh, $query); $main::lxdebug->leave_sub();