X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=24e88f004f6e94643b8a7748edcae6ea53b4c404;hb=b36f0864f0304deb66f4215c52b68351f90b2309;hp=1c812e24f57548c934478f9bd7f80893fa84caf3;hpb=aa4fda0ec21c0715f1d8af27efdd900af3dd4915;p=kivitendo-erp.git diff --git a/SL/OE.pm b/SL/OE.pm index 1c812e24f..24e88f004 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -50,6 +50,8 @@ use SL::DBUtils; use SL::HTML::Restrict; use SL::IC; use SL::TransNumber; +use SL::Util qw(trim); +use SL::DB; use Text::ParseWords; use strict; @@ -158,7 +160,7 @@ sub transactions { WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id )) SQL - push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ; + push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"}); } if ($form->{"business_id"}) { @@ -172,12 +174,12 @@ SQL } elsif ($form->{$vc}) { $query .= " AND ct.name ILIKE ?"; - push(@values, '%' . $form->{$vc} . '%'); + push(@values, like($form->{$vc})); } if ($form->{"cp_name"}) { $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)"; - push(@values, ('%' . $form->{"cp_name"} . '%')x2); + push(@values, (like($form->{"cp_name"}))x2); } if (!$main::auth->assert('sales_all_edit', 1)) { @@ -208,12 +210,12 @@ SQL if ($form->{$ordnumber}) { $query .= qq| AND o.$ordnumber ILIKE ?|; - push(@values, '%' . $form->{$ordnumber} . '%'); + push(@values, like($form->{$ordnumber})); } if ($form->{cusordnumber}) { $query .= qq| AND o.cusordnumber ILIKE ?|; - push(@values, '%' . $form->{cusordnumber} . '%'); + push(@values, like($form->{cusordnumber})); } if($form->{transdatefrom}) { @@ -248,7 +250,7 @@ SQL if ($form->{shippingpoint}) { $query .= qq| AND o.shippingpoint ILIKE ?|; - push(@values, '%' . $form->{shippingpoint} . '%'); + push(@values, like($form->{shippingpoint})); } if ($form->{taxzone_id} ne '') { # taxzone_id could be 0 @@ -258,7 +260,7 @@ SQL if ($form->{transaction_description}) { $query .= qq| AND o.transaction_description ILIKE ?|; - push(@values, '%' . $form->{transaction_description} . '%'); + push(@values, like($form->{transaction_description})); } if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) { @@ -273,7 +275,7 @@ SQL if (($form->{order_probability_value} || '') ne '') { my $op = $form->{order_probability_value} eq 'le' ? '<=' : '>='; $query .= qq| AND (o.order_probability ${op} ?)|; - push @values, $form->{order_probability_value}; + push @values, trim($form->{order_probability_value}); } if ($form->{expected_billing_date_from}) { @@ -286,6 +288,33 @@ SQL push @values, conv_date($form->{expected_billing_date_to}); } + if ($form->{parts_partnumber}) { + $query .= <{parts_partnumber}); + } + + if ($form->{parts_description}) { + $query .= <{parts_description}); + } + if ($form->{all}) { my @tokens = parse_line('\s+', 0, $form->{all}); # ordnumber quonumber customer.name vendor.name transaction_description @@ -295,7 +324,7 @@ SQL ct.name ILIKE ? OR o.transaction_description ILIKE ? )| for @tokens; - push @values, ("%$_%")x4 for @tokens; + push @values, (like($_))x4 for @tokens; } my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT', @@ -391,12 +420,22 @@ sub transactions_for_todo_list { } sub save { + my ($self, $myconfig, $form) = @_; + $main::lxdebug->enter_sub(); + + my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form); + + $::lxdebug->leave_sub; + + return $rc; +} + +sub _save { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; - # connect to database, turn off autocommit - my $dbh = $form->get_standard_dbh; + my $dbh = SL::DB->client->dbh; my $restricter = SL::HTML::Restrict->create; my ($query, @values, $sth, $null); @@ -420,6 +459,11 @@ sub save { $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber} if ($form->{id}) { + $query = qq|DELETE FROM custom_variables + WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo'))) + AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'OE') AND (trans_id = ?)))|; + do_query($form, $dbh, $query, $form->{id}); + $query = qq|DELETE FROM shipto | . qq|WHERE trans_id = ? AND module = 'OE'|; do_query($form, $dbh, $query, $form->{id}); @@ -625,8 +669,12 @@ SQL dbh => $dbh); # link previous items with orderitems - foreach (qw(orderitems invoice)) { - if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) { + # assume we have a new workflow if we link from invoice or order to quotation + # unluckily orderitems are used for quotation and orders - therefore one more + # check to be sure NOT to link from order to quotation + foreach (qw(orderitems)) { + if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"} + && $form->{type} !~ 'quotation') { RecordLinks->create_links('dbh' => $dbh, 'mode' => 'ids', 'from_table' => $_, @@ -725,28 +773,21 @@ SQL $form->save_status($dbh); # Link this record to the records it was created from. - # check every record type we may link. i am not happy with converting the string to array back - # should be a array from the start (OE.pm -> retrieve). - # and that i need the local array ref for close_quotation_rfqs. better ideas welcome $form->{convert_from_oe_ids} =~ s/^\s+//; $form->{convert_from_oe_ids} =~ s/\s+$//; my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids}; delete $form->{convert_from_oe_ids}; - @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids; - foreach (qw(ar oe)) { - if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) { - RecordLinks->create_links('dbh' => $dbh, - 'mode' => 'ids', - 'from_table' => $_, - 'from_ids' => $form->{"convert_from_${_}_ids"}, - 'to_table' => 'oe', - 'to_id' => $form->{id}, - ); - delete $form->{"convert_from_${_}_ids"}; - } + if (!$form->{useasnew} && scalar @convert_from_oe_ids) { + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => \@convert_from_oe_ids, + 'to_table' => 'oe', + 'to_id' => $form->{id}, + ); $self->_close_quotations_rfqs('dbh' => $dbh, 'from_id' => \@convert_from_oe_ids, - 'to_id' => $form->{id}) if $_ eq 'oe'; + 'to_id' => $form->{id}); } if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { @@ -763,8 +804,6 @@ SQL Common::webdav_folder($form); - my $rc = $dbh->commit; - $self->save_periodic_invoices_config(dbh => $dbh, oe_id => $form->{id}, config_yaml => $form->{periodic_invoices_config}) @@ -772,7 +811,7 @@ SQL $main::lxdebug->leave_sub(); - return $rc; + return 1; } sub save_periodic_invoices_config { @@ -799,7 +838,7 @@ sub load_periodic_invoice_config { if ($config_obj) { my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity order_value_periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id - print printer_id copies direct_debit) }; + print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) }; $form->{periodic_invoices_config} = YAML::Dump($config); } } @@ -816,37 +855,38 @@ sub _close_quotations_rfqs { my $myconfig = \%main::myconfig; my $form = $main::form; - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || SL::DB->client->dbh; - my $query = qq|SELECT quotation FROM oe WHERE id = ?|; - my $sth = prepare_query($form, $dbh, $query); + SL::DB->client->with_transaction(sub { - do_statement($form, $sth, $query, conv_i($params{to_id})); + my $query = qq|SELECT quotation FROM oe WHERE id = ?|; + my $sth = prepare_query($form, $dbh, $query); - my ($quotation) = $sth->fetchrow_array(); + do_statement($form, $sth, $query, conv_i($params{to_id})); - if ($quotation) { - $main::lxdebug->leave_sub(); - return; - } + my ($quotation) = $sth->fetchrow_array(); - my @close_ids; + if ($quotation) { + return 1; + } - foreach my $from_id (@{ $params{from_id} }) { - $from_id = conv_i($from_id); - do_statement($form, $sth, $query, $from_id); - ($quotation) = $sth->fetchrow_array(); - push @close_ids, $from_id if ($quotation); - } + my @close_ids; - $sth->finish(); + foreach my $from_id (@{ $params{from_id} }) { + $from_id = conv_i($from_id); + do_statement($form, $sth, $query, $from_id); + ($quotation) = $sth->fetchrow_array(); + push @close_ids, $from_id if ($quotation); + } - if (scalar @close_ids) { - $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|; - do_query($form, $dbh, $query, @close_ids); + $sth->finish(); - $dbh->commit() unless ($params{dbh}); - } + if (scalar @close_ids) { + $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|; + do_query($form, $dbh, $query, @close_ids); + } + 1; + }) or do { die SL::DB->client->error }; $main::lxdebug->leave_sub(); } @@ -865,7 +905,7 @@ sub delete { unlink map { "$spool/$_" } @spoolfiles if $spool; 1; - }); + }) or do { die SL::DB->client->error }; $main::lxdebug->leave_sub(); @@ -873,12 +913,20 @@ sub delete { } sub retrieve { + my ($self, $myconfig, $form) = @_; $main::lxdebug->enter_sub(); + my $rc = SL::DB->client->with_transaction(\&_retrieve, $self, $myconfig, $form); + + $::lxdebug->leave_sub; + return $rc; +} + +sub _retrieve { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->get_standard_dbh; + my $dbh = SL::DB->client->dbh; my ($query, $query_add, @values, @ids, $sth); @@ -1010,6 +1058,15 @@ sub retrieve { map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; + if ($form->{shipto_id}) { + my $cvars = CVar->get_custom_variables( + dbh => $dbh, + module => 'ShipTo', + trans_id => $form->{shipto_id}, + ); + $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars }; + } + # get printed, emailed and queued $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|; $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); @@ -1036,8 +1093,9 @@ sub retrieve { 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, 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.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, + p.partnumber, p.part_type, p.listprice, o.description, o.qty, + p.classification_id, + o.sellprice, o.parts_id AS id, o.unit, o.discount, p.notes AS partnotes, p.part_type, 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, o.active_price_source, o.active_discount_source, @@ -1069,10 +1127,10 @@ sub retrieve { map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; # Handle accounts. - if (!$ref->{"part_inventory_accno_id"}) { + if (!$ref->{"part_type"} eq 'part') { map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)); } - delete($ref->{"part_inventory_accno_id"}); + # delete($ref->{"part_inventory_accno_id"}); # in collective order, copy global ordnumber, transdate, cusordnumber into item scope # unless already present there @@ -1167,11 +1225,7 @@ sub retrieve { $self->load_periodic_invoice_config($form); - my $rc = $dbh->commit; - - $main::lxdebug->leave_sub(); - - return $rc; + return 1; } sub retrieve_simple { @@ -1204,7 +1258,7 @@ sub order_details { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->get_standard_dbh; + my $dbh = SL::DB->client->dbh; my $query; my @values = (); my $sth; @@ -1537,21 +1591,21 @@ sub order_details { $form->{subtotal_nofmt} = $form->{ordtotal}; } - $form->{ordtotal} = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax; + my $grossamount = ($form->{taxincluded}) ? $form->{ordtotal} : $form->{ordtotal} + $tax; + $form->{ordtotal} = $form->round_amount( $grossamount, 2, 1); + $form->{rounding} = $form->round_amount( + $form->{ordtotal} - $form->round_amount($grossamount, 2), + 2 + ); # format amounts + $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2); $form->{quototal} = $form->{ordtotal} = $form->format_amount($myconfig, $form->{ordtotal}, 2); - if ($form->{type} =~ /_quotation/) { - $form->set_payment_options($myconfig, $form->{quodate}); - } else { - $form->set_payment_options($myconfig, $form->{orddate}); - } + $form->set_payment_options($myconfig, $form->{$form->{type} =~ /_quotation/ ? 'quodate' : 'orddate'}, $form->{type}); $form->{username} = $myconfig->{name}; - $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};