X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIS.pm;h=5a6484505c7f60f0345136a035e192cef3106a18;hb=f203bc24057969b15c414e84272afb6bbd740e77;hp=c5147b1afb4957d81a00f3d1c466e1092a16b440;hpb=a23454bb1b039a31b7f77710ff663fa9152d530c;p=kivitendo-erp.git diff --git a/SL/IS.pm b/SL/IS.pm index c5147b1af..5a6484505 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -44,11 +44,14 @@ use SL::DATEV qw(:CONSTANTS); use SL::DBUtils; use SL::DO; use SL::GenericTranslations; +use SL::HTML::Restrict; use SL::MoreCommon; use SL::IC; use SL::IO; use SL::TransNumber; use SL::DB::Default; +use SL::DB::Tax; +use SL::TransNumber; use Data::Dumper; use strict; @@ -67,7 +70,7 @@ 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, %projectdescriptions); + my (@project_ids); $form->{TEMPLATE_ARRAYS} = {}; push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); @@ -91,31 +94,27 @@ sub invoice_details { push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"}); } + my $projects = []; + my %projects_by_id; if (@project_ids) { - $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(); + $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]); + %projects_by_id = map { $_->id => $_ } @$projects; } - $form->{"globalprojectnumber"} = - $projectnumbers{$form->{"globalproject_id"}}; - $form->{"globalprojectdescription"} = - $projectdescriptions{$form->{"globalproject_id"}}; + if ($projects_by_id{$form->{"globalproject_id"}}) { + $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber; + $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description; + + for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) { + $form->{"project_cvar_" . $_->config->name} = $_->value_as_text; + } + } my $tax = 0; my $item; my $i; my @partsgroup = (); my $partsgroup; - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); # sort items by partsgroup for $i (1 .. $form->{rowcount}) { @@ -147,16 +146,18 @@ sub invoice_details { IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form); my $ic_cvar_configs = CVar->get_configs(module => 'IC'); + my $project_cvar_configs = CVar->get_configs(module => 'Projects'); my @arrays = qw(runningnumber number description longdescription qty ship unit bin - deliverydate_oe ordnumber_oe transdate_oe validuntil + deliverydate_oe ordnumber_oe donumber_do transdate_oe validuntil partnotes serialnumber reqdate sellprice listprice netprice discount p_discount discount_sub nodiscount_sub linetotal nodiscount_linetotal tax_rate projectnumber projectdescription - price_factor price_factor_name partsgroup); + price_factor price_factor_name partsgroup weight lineweight); push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; + push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs }; my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber); @@ -164,6 +165,7 @@ sub invoice_details { map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays); + my $totalweight = 0; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; @@ -209,6 +211,7 @@ sub invoice_details { 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}->{donumber_do} }, $form->{"donumber_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"}; push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"}; push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"}; @@ -277,8 +280,17 @@ sub invoice_details { 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}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}}); + my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new; + + push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber; + push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description; + + my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"}; + $totalweight += $lineweight; + push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3); + push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3); + push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight; @taxaccounts = split(/ /, $form->{"taxaccounts_$i"}); $taxrate = 0; @@ -328,9 +340,9 @@ sub invoice_details { my $sortorder = ""; if ($form->{groupitems}) { $sortorder = - qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|; + qq|ORDER BY pg.partsgroup, a.oid|; } else { - $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|; + $sortorder = qq|ORDER BY a.oid|; } $query = @@ -363,9 +375,16 @@ sub invoice_details { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_) for @{ $ic_cvar_configs }; + + push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config }; } } + $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3); + $form->{totalweight_nofmt} = $totalweight; + my $defaults = AM->get_defaults(); + $form->{weightunit} = $defaults->{weightunit}; + foreach my $item (sort keys %taxaccounts) { $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); @@ -375,8 +394,11 @@ sub invoice_details { 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"}); + + my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"}); + my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj; + push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%}); } for my $i (1 .. $form->{paidaccounts}) { @@ -414,6 +436,9 @@ sub invoice_details { $form->set_payment_options($myconfig, $form->{invdate}); + $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef); + $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id}; + $form->{username} = $myconfig->{name}; $main::lxdebug->leave_sub(); @@ -458,9 +483,10 @@ sub customer_details { my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail, - (SELECT cu.curr FROM currencies cu WHERE cu.id=ct.curr) AS currency + cu.name AS currency FROM customer ct LEFT JOIN contacts cp on ct.id = cp.cp_cv_id + LEFT JOIN currencies cu ON (ct.currency_id = cu.id) WHERE (ct.id = ?) $where ORDER BY cp.cp_id LIMIT 1|; @@ -521,6 +547,7 @@ sub post_invoice { # connect to database, turn off autocommit my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh; + my $restricter = SL::HTML::Restrict->create; my ($query, $sth, $null, $project_id, @values); my $exchangerate = 0; @@ -555,13 +582,12 @@ sub post_invoice { $query = qq|SELECT nextval('glid')|; ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber, curr) VALUES (?, ?, (SELECT id FROM currencies WHERE curr=?))|; + $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|; do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}); if (!$form->{invnumber}) { - $form->{invnumber} = - $form->update_defaults($myconfig, $form->{type} eq "credit_note" ? - "cnnumber" : "invnumber", $dbh); + my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id}); + $form->{invnumber} = $trans_number->create_unique; } } } @@ -718,19 +744,19 @@ sub post_invoice { 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, + ordnumber, donumber, 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 = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}), - $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"}, + $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"}, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated, 'f', $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, $pricegroup_id, - $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), + $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f', $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"}, $form->{"lastcost_$i"}, @@ -1081,14 +1107,15 @@ sub post_invoice { amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, - curr = (SELECT id FROM currencies WHERE curr= ?), + currency_id = (SELECT id FROM currencies WHERE name = ?), department_id = ?, payment_id = ?, taxincluded = ?, type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?, employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?, cp_id = ?, marge_total = ?, marge_percent = ?, globalproject_id = ?, delivery_customer_id = ?, transaction_description = ?, delivery_vendor_id = ?, - donumber = ?, invnumber_for_credit_note = ?, direct_debit = ? + donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?, + delivery_term_id = ? 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"}), @@ -1102,6 +1129,7 @@ sub post_invoice { conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}), $form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}), $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f', + conv_i($form->{delivery_term_id}), conv_i($form->{"id"})); do_query($form, $dbh, $query, @values); @@ -1171,6 +1199,7 @@ sub post_invoice { dbh => $dbh, from => $transdate, to => $transdate, + trans_id => $form->{id}, ); $datev->export; @@ -1235,7 +1264,6 @@ sub post_payment { # connect to database, turn off autocommit my $dbh = $form->get_standard_dbh; - $dbh->begin_work; my (%payments, $old_form, $row, $item, $query, %keep_vars); @@ -1494,7 +1522,6 @@ sub delete_invoice { # connect to database my $dbh = $form->get_standard_dbh; - $dbh->begin_work; &reverse_invoice($dbh, $form); @@ -1574,11 +1601,11 @@ sub retrieve_invoice { a.orddate, a.quodate, a.globalproject_id, a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, - a.duedate, a.taxincluded, (SELECT cu.curr FROM currencies cu WHERE cu.id=a.curr) AS currency, a.shipto_id, a.cp_id, + a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) 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.donumber, a.invnumber_for_credit_note, - a.marge_total, a.marge_percent, a.direct_debit, + a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id, e.name AS employee FROM ar a LEFT JOIN employee e ON (e.id = a.employee_id) @@ -1628,9 +1655,9 @@ sub retrieve_invoice { 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.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.donumber, 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.listprice, + p.partnumber, p.assembly, 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 @@ -1748,15 +1775,16 @@ sub get_customer { $query = qq|SELECT c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms, - c.email, c.cc, c.bcc, c.language_id, c.payment_id, + c.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id, c.street, c.zipcode, c.city, c.country, - c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, (SELECT cu.curr FROM currencies cu WHERE cu.id=c.curr) AS curr, + c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr, c.taxincluded_checked, c.direct_debit, $duedate + COALESCE(pt.terms_netto, 0) AS duedate, b.discount AS tradediscount, b.description AS business FROM customer c LEFT JOIN business b ON (b.id = c.business_id) LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id)) + LEFT JOIN currencies cu ON (c.currency_id=cu.id) WHERE c.id = ?|; push @values, $cid; $ref = selectfirst_hashref_query($form, $dbh, $query, @values); @@ -1795,7 +1823,7 @@ sub get_customer { $query = qq|SELECT o.amount, (SELECT e.buy FROM exchangerate e - WHERE e.curr = o.curr + WHERE e.currency_id = o.currency_id AND e.transdate = o.transdate) FROM oe o WHERE o.customer_id = ? @@ -1935,10 +1963,10 @@ sub retrieve_item { c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid, - p.unit, p.assembly, p.bin, p.onhand, + p.unit, p.assembly, p.onhand, p.notes AS partnotes, p.notes AS longdescription, p.not_discountable, p.formel, p.payment_id AS part_payment_id, - p.price_factor_id, + p.price_factor_id, p.weight, pfac.factor AS price_factor,