X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=3d92746b55db2bfcf5beb45bc65a4dd2bc5eb427;hb=284470c116a0f1a73f82797160b0170e55a58652;hp=c5a8c26aec80121b539d13854b6e65b93f2004f3;hpb=a23454bb1b039a31b7f77710ff663fa9152d530c;p=kivitendo-erp.git diff --git a/SL/OE.pm b/SL/OE.pm index c5a8c26ae..3d92746b5 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -40,9 +40,13 @@ use YAML; use SL::AM; use SL::Common; use SL::CVar; +use SL::DB::Order; use SL::DB::PeriodicInvoicesConfig; +use SL::DB::Status; +use SL::DB::Tax; use SL::DBUtils; use SL::IC; +use SL::TransNumber; use strict; @@ -85,15 +89,17 @@ sub transactions { qq| ex.$rate AS exchangerate, | . qq| pr.projectnumber AS globalprojectnumber, | . qq| e.name AS employee, s.name AS salesman, | . - qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid | . + qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | . + qq| tz.description AS taxzone | . $periodic_invoices_columns . qq|FROM oe o | . qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | . qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | . - qq|LEFT JOIN exchangerate ex ON (ex.curr = o.curr | . + qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | . qq| AND ex.transdate = o.transdate) | . qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | . + qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | . qq|$periodic_invoices_joins | . qq|WHERE (o.quotation = ?) |; push(@values, $quotation); @@ -124,6 +130,11 @@ SQL push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ; } + if ($form->{"business_id"}) { + $query .= " AND ct.business_id = ?"; + push(@values, $form->{"business_id"}); + } + if ($form->{"${vc}_id"}) { $query .= " AND o.${vc}_id = ?"; push(@values, $form->{"${vc}_id"}); @@ -184,6 +195,16 @@ SQL push(@values, conv_date($form->{reqdateto})); } + if ($form->{shippingpoint}) { + $query .= qq| AND o.shippingpoint ILIKE ?|; + push(@values, '%' . $form->{shippingpoint} . '%'); + } + + if ($form->{taxzone_id} ne '') { # taxzone_id could be 0 + $query .= qq| AND tz.id = ?|; + push(@values, $form->{taxzone_id}); + } + if ($form->{transaction_description}) { $query .= qq| AND o.transaction_description ILIKE ?|; push(@values, '%' . $form->{transaction_description} . '%'); @@ -206,7 +227,9 @@ SQL "employee" => "e.name", "salesman" => "s.name", "shipvia" => "o.shipvia", - "transaction_description" => "o.transaction_description" + "transaction_description" => "o.transaction_description", + "shippingpoint" => "o.shippingpoint", + "taxzone" => "tz.description", ); if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) { $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}"; @@ -292,6 +315,10 @@ sub save { my $ml = ($form->{type} eq 'sales_order') ? 1 : -1; + my $number_field = $form->{type} =~ m{order} ? 'ordnumber' : 'quonumber'; + my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{$number_field}, id => $form->{id}); + $form->{$number_field} ||= $trans_number->create_unique; + if ($form->{id}) { $query = qq|DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) @@ -311,7 +338,7 @@ sub save { $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO oe (id, ordnumber, employee_id, curr) VALUES (?, '', ?, (SELECT curr FROM defaults))|; + $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|; do_query($form, $dbh, $query, $form->{id}, $form->{employee_id}); } @@ -494,9 +521,9 @@ sub save { qq|UPDATE oe SET ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?, customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?, - shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = (SELECT id FROM currencies WHERE curr=?), closed = ?, + shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?, delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?, - taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?, + taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,delivery_term_id = ?, globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ? WHERE id = ?|; @@ -513,6 +540,7 @@ sub save { conv_i($form->{shipto_id}), conv_i($form->{payment_id}), conv_i($form->{delivery_vendor_id}), conv_i($form->{delivery_customer_id}), + conv_i($form->{delivery_term_id}), conv_i($form->{globalproject_id}), conv_i($form->{employee_id}), conv_i($form->{salesman_id}), conv_i($form->{cp_id}), $form->{transaction_description}, @@ -660,59 +688,16 @@ sub delete { my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->get_standard_dbh; - $dbh->begin_work; - - # delete spool files - my $query = qq|SELECT s.spoolfile FROM status s | . - qq|WHERE s.trans_id = ?|; - my @values = (conv_i($form->{id})); - my $sth = $dbh->prepare($query); - $sth->execute(@values) || $self->dberror($query); - - my $spoolfile; - my @spoolfiles = (); - - while (($spoolfile) = $sth->fetchrow_array) { - push @spoolfiles, $spoolfile; - } - $sth->finish; - - # delete-values - @values = (conv_i($form->{id})); - - # periodic invoices and their configuration - do_query($form, $dbh, qq|DELETE FROM periodic_invoices WHERE config_id IN (SELECT id FROM periodic_invoices_configs WHERE oe_id = ?)|, @values); - do_query($form, $dbh, qq|DELETE FROM periodic_invoices_configs WHERE oe_id = ?|, @values); - - # delete status entries - $query = qq|DELETE FROM status | . - qq|WHERE trans_id = ?|; - do_query($form, $dbh, $query, @values); - - # delete individual entries - $query = qq|DELETE FROM orderitems | . - qq|WHERE trans_id = ?|; - do_query($form, $dbh, $query, @values); - - $query = qq|DELETE FROM shipto | . - qq|WHERE trans_id = ? AND module = 'OE'|; - do_query($form, $dbh, $query, @values); - - # delete OE record - $query = qq|DELETE FROM oe | . - qq|WHERE id = ?|; - do_query($form, $dbh, $query, @values); + my $rc = SL::DB::Order->new->db->with_transaction(sub { + my @spoolfiles = grep { $_ } map { $_->spoolfile } @{ SL::DB::Manager::Status->get_all(where => [ trans_id => $form->{id} ]) }; - my $rc = $dbh->commit; + SL::DB::Order->new(id => $form->{id})->delete; - if ($rc) { my $spool = $::lx_office_conf{paths}->{spool}; - foreach $spoolfile (@spoolfiles) { - unlink "$spool/$spoolfile" if $spoolfile; - } - } + unlink map { "$spool/$_" } @spoolfiles if $spool; + + 1; + }); $main::lxdebug->leave_sub(); @@ -784,12 +769,12 @@ sub retrieve { $query = qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, - (SELECT cu.curr FROM currencies cu WHERE cu.id=o.curr) AS currency, e.name AS employee, o.employee_id, o.salesman_id, + (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id, o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal, o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, d.description AS department, o.payment_id, o.language_id, o.taxzone_id, o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, - o.globalproject_id, o.delivered, o.transaction_description + o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id FROM oe o JOIN ${vc} cv ON (o.${vc}_id = cv.id) LEFT JOIN employee e ON (o.employee_id = e.id) @@ -853,9 +838,6 @@ sub retrieve { map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued); } # if !@ids - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); - my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; $form->{taxzone_id} = 0 unless ($form->{taxzone_id}); @@ -870,7 +852,7 @@ sub retrieve { c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, p.partnumber, p.assembly, p.listprice, o.description, o.qty, - o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, + o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost, o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription, o.price_factor_id, o.price_factor, o.marge_price_factor, @@ -887,7 +869,7 @@ sub retrieve { ($form->{id} ? qq|WHERE o.trans_id = ?| : qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) . - qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|; + qq|ORDER BY o.oid|; @ids = $form->{id} ? ($form->{id}) : @ids; $sth = prepare_execute_query($form, $dbh, $query, @values); @@ -1055,11 +1037,7 @@ sub order_details { my $tax_rate; my $taxamount; - - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); - - my (@project_ids, %projectnumbers, %projectdescriptions); + my (@project_ids); push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); @@ -1090,19 +1068,21 @@ sub order_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 = prepare_execute_query($form, $dbh, $query, @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; + } + } $form->{discount} = []; @@ -1110,20 +1090,23 @@ sub order_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 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); map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays); + my $totalweight = 0; my $sameitem = ""; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; @@ -1231,12 +1214,21 @@ sub order_details { $nodiscount += $linetotal; } + my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new; + 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}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}}); + 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; my ($taxamount, $taxbase); my $taxrate = 0; @@ -1269,9 +1261,9 @@ sub order_details { # get parts and push them onto the stack my $sortorder = ""; if ($form->{groupitems}) { - $sortorder = qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|; + $sortorder = qq|ORDER BY pg.partsgroup, a.oid|; } else { - $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|; + $sortorder = qq|ORDER BY a.oid|; } $query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, | . @@ -1301,9 +1293,16 @@ sub order_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}; + my $tax = 0; foreach $item (sort keys %taxaccounts) { $tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2); @@ -1314,8 +1313,11 @@ sub order_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{%}); } $form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2); @@ -1346,6 +1348,9 @@ sub order_details { $dbh->disconnect; + $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}; + $main::lxdebug->leave_sub(); }