X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=bc651c8ed6b896b49a276cae16114d6a4753a164;hb=2766521474b20c77e62d169a1c99efb41cb1c56f;hp=acbd8dab65d748d790daa74c470bc8e8f3695a48;hpb=ce2e13e64bf9de995020cf18fd7ef36f199f018d;p=kivitendo-erp.git diff --git a/SL/DO.pm b/SL/DO.pm index acbd8dab6..bc651c8ed 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -38,7 +38,14 @@ use YAML; use SL::AM; use SL::Common; +use SL::CVar; +use SL::DB::DeliveryOrder; +use SL::DB::Status; use SL::DBUtils; +use SL::RecordLinks; +use SL::IC; + +use strict; sub transactions { $main::lxdebug->enter_sub(); @@ -55,35 +62,36 @@ sub transactions { my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; - $query = - qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.transdate, - ct.name, dord.${vc}_id, dord.globalproject_id, + my $query = + qq|SELECT dord.id, dord.donumber, dord.ordnumber, + dord.transdate, dord.reqdate, + ct.${vc}number, ct.name, dord.${vc}_id, dord.globalproject_id, dord.closed, dord.delivered, dord.shippingpoint, dord.shipvia, dord.transaction_description, pr.projectnumber AS globalprojectnumber, + dep.description AS department, e.name AS employee, - sm.name AS salesman, - oe.id AS oe_id + sm.name AS salesman FROM delivery_orders dord LEFT JOIN $vc ct ON (dord.${vc}_id = ct.id) LEFT JOIN employee e ON (dord.employee_id = e.id) LEFT JOIN employee sm ON (dord.salesman_id = sm.id) LEFT JOIN project pr ON (dord.globalproject_id = pr.id) - LEFT JOIN oe ON ((dord.ordnumber = oe.ordnumber) AND NOT COALESCE(oe.quotation, FALSE))|; + LEFT JOIN department dep ON (dord.department_id = dep.id) +|; push @where, ($form->{type} eq 'sales_delivery_order' ? '' : 'NOT ') . qq|COALESCE(dord.is_sales, FALSE)|; - my $department_id = (split /--/, $form->{department})[1]; - if ($department_id) { + if ($form->{department_id}) { push @where, qq|dord.department_id = ?|; - push @values, conv_i($department_id); + push @values, conv_i($form->{department_id}); } if ($form->{project_id}) { - $query .= + push @where, qq|(dord.globalproject_id = ?) OR EXISTS (SELECT * FROM delivery_order_items doi - WHERE (doi.project_id = ?) AND (oi.delivery_order_id = dord.id))|; + WHERE (doi.project_id = ?) AND (doi.delivery_order_id = dord.id))|; push @values, conv_i($form->{project_id}), conv_i($form->{project_id}); } @@ -101,6 +109,10 @@ sub transactions { push @where, "dord.$item = ?"; push @values, conv_i($form->{$item}); } + if (!$main::auth->assert('sales_all_edit', 1)) { + push @where, qq|dord.employee_id = (select id from employee where login= ?)|; + push @values, $form->{login}; + } foreach my $item (qw(donumber ordnumber cusordnumber transaction_description)) { next unless ($form->{$item}); @@ -108,7 +120,8 @@ sub transactions { push @values, '%' . $form->{$item} . '%'; } - if (!($form->{open} && $form->{closed})) { + if (($form->{open} || $form->{closed}) && + ($form->{open} ne $form->{closed})) { push @where, ($form->{open} ? "NOT " : "") . "COALESCE(dord.closed, FALSE)"; } @@ -117,6 +130,11 @@ sub transactions { push @where, ($form->{delivered} ? "" : "NOT ") . "COALESCE(dord.delivered, FALSE)"; } + if ($form->{serialnumber}) { + push @where, 'dord.id IN (SELECT doi.delivery_order_id FROM delivery_order_items doi WHERE doi.serialnumber LIKE ?)'; + push @values, '%' . $form->{serialnumber} . '%'; + } + if($form->{transdatefrom}) { push @where, qq|dord.transdate >= ?|; push @values, conv_date($form->{transdatefrom}); @@ -127,12 +145,23 @@ sub transactions { push @values, conv_date($form->{transdateto}); } + if($form->{reqdatefrom}) { + push @where, qq|dord.reqdate >= ?|; + push @values, conv_date($form->{reqdatefrom}); + } + + if($form->{reqdateto}) { + push @where, qq|dord.reqdate <= ?|; + push @values, conv_date($form->{reqdateto}); + } + if (@where) { $query .= " WHERE " . join(" AND ", map { "($_)" } @where); } my %allowed_sort_columns = ( "transdate" => "dord.transdate", + "reqdate" => "dord.reqdate", "id" => "dord.id", "donumber" => "dord.donumber", "ordnumber" => "dord.ordnumber", @@ -140,19 +169,38 @@ sub transactions { "employee" => "e.name", "salesman" => "sm.name", "shipvia" => "dord.shipvia", - "transaction_description" => "dord.transaction_description" + "transaction_description" => "dord.transaction_description", + "department" => "lower(dep.description)", ); - my $sortoder = "dord.id"; + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + my $sortorder = "dord.id"; if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) { $sortorder = $allowed_sort_columns{$form->{sort}}; } - $query .= qq| ORDER by | . $sortorder; + $query .= qq| ORDER by | . $sortorder . " $sortdir"; $form->{DO} = selectall_hashref_query($form, $dbh, $query, @values); - $main::lxdebug->dump(0, "DO", $form->{DO}); + if (scalar @{ $form->{DO} }) { + $query = + qq|SELECT id + FROM oe + WHERE NOT COALESCE(quotation, FALSE) + AND (ordnumber = ?) + AND (COALESCE(${vc}_id, 0) != 0)|; + + my $sth = prepare_query($form, $dbh, $query); + + foreach my $dord (@{ $form->{DO} }) { + next unless ($dord->{ordnumber}); + do_statement($form, $sth, $query, $dord->{ordnumber}); + ($dord->{oe_id}) = $sth->fetchrow_array(); + } + + $sth->finish(); + } $main::lxdebug->leave_sub(); } @@ -173,6 +221,9 @@ sub save { my $all_units = AM->retrieve_units($myconfig, $form); $form->{all_units} = $all_units; + my $ic_cvar_configs = CVar->get_configs(module => 'IC', + dbh => $dbh); + $form->{donumber} = $form->update_defaults($myconfig, $form->{type} eq 'sales_delivery_order' ? 'sdonumber' : 'pdonumber', $dbh) unless $form->{donumber}; $form->{employee_id} = (split /--/, $form->{employee})[1] if !$form->{employee_id}; $form->get_employee($dbh) unless ($form->{employee_id}); @@ -195,12 +246,12 @@ sub save { $query = qq|SELECT nextval('id')|; ($form->{id}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO delivery_orders (id, donumber, employee_id) VALUES (?, '', ?)|; + $query = qq|INSERT INTO delivery_orders (id, donumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults LIMIT 1))|; do_query($form, $dbh, $query, $form->{id}, conv_i($form->{employee_id})); } my $project_id; - my $reqdate; + my $items_reqdate; $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS'); my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} }; @@ -223,14 +274,14 @@ sub save { id, delivery_order_id, parts_id, description, longdescription, qty, base_qty, sellprice, discount, unit, reqdate, project_id, serialnumber, ordnumber, transdate, cusordnumber, - lastcost, price_factor_id, price_factor, marge_price_factor) + lastcost, price_factor_id, price_factor, marge_price_factor, pricegroup_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, - (SELECT factor FROM price_factors WHERE id = ?), ?)|; + (SELECT factor FROM price_factors WHERE id = ?), ?, ?)|; my $h_item = prepare_query($form, $dbh, $q_item); my $q_item_stock = - qq|INSERT INTO delivery_order_items_stock (delivery_order_item_id, qty, unit, warehouse_id, bin_id, chargenumber) - VALUES (?, ?, ?, ?, ?, ?)|; + qq|INSERT INTO delivery_order_items_stock (delivery_order_item_id, qty, unit, warehouse_id, bin_id, chargenumber, bestbefore) + VALUES (?, ?, ?, ?, ?, ?, ?)|; my $h_item_stock = prepare_query($form, $dbh, $q_item_stock); my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in'; @@ -248,49 +299,66 @@ sub save { } my $baseqty = $form->{"qty_$i"} * $basefactor; - $form->{"lastcost_$i"} *= 1; - # set values to 0 if nothing entered - $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100; + $form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}); $form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"}); + $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}); $price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1; - $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2); + my $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2); - $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef; + $items_reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef; do_statement($form, $h_item_id, $q_item_id); my ($item_id) = $h_item_id->fetchrow_array(); + # Get pricegroup_id and save it. Unfortunately the interface + # also uses ID "0" for signalling that none is selected, but "0" + # must not be stored in the database. Therefore we cannot simply + # use conv_i(). + my $pricegroup_id = $form->{"pricegroup_id_$i"} * 1; + $pricegroup_id = undef if !$pricegroup_id; + # save detail record in delivery_order_items table @values = (conv_i($item_id), conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"}, $baseqty, - $form->{"sellprice_$i"}, $form->{"discount_$i"}, - $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}), + $form->{"sellprice_$i"}, $form->{"discount_$i"} / 100, + $form->{"unit_$i"}, conv_date($items_reqdate), conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"}, $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}), $form->{"cusordnumber_$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"})); + conv_i($form->{"marge_price_factor_$i"}), + $pricegroup_id); do_statement($form, $h_item, $q_item, @values); my $stock_info = DO->unpack_stock_information('packed' => $form->{"stock_${in_out}_$i"}); foreach my $sinfo (@{ $stock_info }) { @values = ($item_id, $sinfo->{qty}, $sinfo->{unit}, conv_i($sinfo->{warehouse_id}), - conv_i($sinfo->{bin_id}), $sinfo->{chargenumber}); + conv_i($sinfo->{bin_id}), $sinfo->{chargenumber}, conv_date($sinfo->{bestbefore})); do_statement($form, $h_item_stock, $q_item_stock, @values); } + + CVar->save_custom_variables(module => 'IC', + sub_module => 'delivery_order_items', + trans_id => $item_id, + configs => $ic_cvar_configs, + variables => $form, + name_prefix => 'ic_', + name_postfix => "_$i", + dbh => $dbh); } $h_item_id->finish(); $h_item->finish(); $h_item_stock->finish(); - ($null, $form->{department_id}) = split(/--/, $form->{department}); + # reqdate is last items reqdate (?: old behaviour) if not already set + $form->{reqdate} ||= $items_reqdate; # save DO record $query = qq|UPDATE delivery_orders SET @@ -299,13 +367,14 @@ sub save { shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, closed = ?, delivered = ?, department_id = ?, language_id = ?, shipto_id = ?, globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, - is_sales = ? + is_sales = ?, taxzone_id = ?, taxincluded = ?, terms = ?, currency_id = (SELECT id FROM currencies WHERE name = ?), + delivery_term_id = ? WHERE id = ?|; @values = ($form->{donumber}, $form->{ordnumber}, $form->{cusordnumber}, conv_date($form->{transdate}), conv_i($form->{vendor_id}), conv_i($form->{customer_id}), - conv_date($reqdate), $form->{shippingpoint}, $form->{shipvia}, + conv_date($form->{reqdate}), $form->{shippingpoint}, $form->{shipvia}, $form->{notes}, $form->{intnotes}, $form->{closed} ? 't' : 'f', $form->{delivered} ? "t" : "f", conv_i($form->{department_id}), conv_i($form->{language_id}), conv_i($form->{shipto_id}), @@ -313,6 +382,8 @@ sub save { conv_i($form->{salesman_id}), conv_i($form->{cp_id}), $form->{transaction_description}, $form->{type} =~ /^sales/ ? 't' : 'f', + conv_i($form->{taxzone_id}), $form->{taxincluded} ? 't' : 'f', conv_i($form->{terms}), $form->{currency}, + conv_i($form->{delivery_term_id}), conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -327,95 +398,144 @@ sub save { # save printed, emailed, queued $form->save_status($dbh); + # Link this delivery order to the quotations it was created from. + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => $form->{convert_from_oe_ids}, + 'to_table' => 'delivery_orders', + 'to_id' => $form->{id}, + ); + delete $form->{convert_from_oe_ids}; + + $self->mark_orders_if_delivered('do_id' => $form->{id}, + 'type' => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase', + 'dbh' => $dbh,); + my $rc = $dbh->commit(); $form->{saved_donumber} = $form->{donumber}; - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); $main::lxdebug->leave_sub(); return $rc; } -sub close_order { +sub mark_orders_if_delivered { $main::lxdebug->enter_sub(); - my ($self) = @_; + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(do_id type)); my $myconfig = \%main::myconfig; my $form = $main::form; - return $main::lxdebug->leave_sub() unless ($form->{id}); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my $dbh = $form->get_standard_dbh($myconfig); - do_query($form, $dbh, qq|UPDATE do SET closed = TRUE where id = ?|, conv_i($form->{id})); - $dbh->commit(); + my @links = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'to_id' => $params{do_id}); + + my $oe_id = @links ? $links[0]->{from_id} : undef; + + return $main::lxdebug->leave_sub() if (!$oe_id); + + my $all_units = AM->retrieve_all_units(); + + my $query = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit + FROM orderitems oi + LEFT JOIN parts p ON (oi.parts_id = p.id) + WHERE (oi.trans_id = ?)|; + my $sth = prepare_execute_query($form, $dbh, $query, $oe_id); + + my %shipped = $self->get_shipped_qty('type' => $params{type}, + 'oe_id' => $oe_id,); + my %ordered = (); + + while (my $ref = $sth->fetchrow_hashref()) { + $ref->{baseqty} = $ref->{qty} * $all_units->{$ref->{unit}}->{factor} / $all_units->{$ref->{partunit}}->{factor}; + + if ($ordered{$ref->{parts_id}}) { + $ordered{$ref->{parts_id}}->{baseqty} += $ref->{baseqty}; + } else { + $ordered{$ref->{parts_id}} = $ref; + } + } + + $sth->finish(); + + map { $_->{baseqty} = $_->{qty} * $all_units->{$_->{unit}}->{factor} / $all_units->{$_->{partunit}}->{factor} } values %shipped; + + my $delivered = 1; + foreach my $part (values %ordered) { + if (!$shipped{$part->{parts_id}} || ($shipped{$part->{parts_id}}->{baseqty} < $part->{baseqty})) { + $delivered = 0; + last; + } + } + + if ($delivered) { + $query = qq|UPDATE oe + SET delivered = TRUE + WHERE id = ?|; + do_query($form, $dbh, $query, $oe_id); + $dbh->commit() if (!$params{dbh}); + } $main::lxdebug->leave_sub(); } -sub delete { +sub close_orders { $main::lxdebug->enter_sub(); - my ($self) = @_; + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(ids)); + + if (('ARRAY' ne ref $params{ids}) || !scalar @{ $params{ids} }) { + $main::lxdebug->leave_sub(); + return; + } my $myconfig = \%main::myconfig; my $form = $main::form; - my $spool = $main::spool; - # connect to database - my $dbh = $form->get_standard_dbh($myconfig); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - # delete spool files - my $query = qq|SELECT s.spoolfile FROM status s WHERE s.trans_id = ?|; - my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); + my $query = qq|UPDATE delivery_orders SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar(@{ $params{ids} })) . qq|)|; - my $spoolfile; - my @spoolfiles = (); + do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} }); - while (($spoolfile) = $sth->fetchrow_array) { - push @spoolfiles, $spoolfile; - } - $sth->finish(); + $dbh->commit() unless ($params{dbh}); - # delete-values - @values = (conv_i($form->{id})); + $main::lxdebug->leave_sub(); +} - # delete status entries - $query = qq|DELETE FROM status - WHERE trans_id = ?|; - do_query($form, $dbh, $query, @values); +sub delete { + $main::lxdebug->enter_sub(); - # delete individual entries - $query = qq|DELETE FROM delivery_order_items_stock - WHERE delivery_order_item_id IN ( - SELECT id FROM delivery_order_items - WHERE delivery_order_id = ? - )|; - do_query($form, $dbh, $query, @values); + my ($self) = @_; - # delete individual entries - $query = qq|DELETE FROM delivery_order_items - WHERE delivery_order_id = ?|; - do_query($form, $dbh, $query, @values); + my $myconfig = \%main::myconfig; + my $form = $main::form; + my $spool = $::lx_office_conf{paths}->{spool}; - # delete DO record - $query = qq|DELETE FROM delivery_orders - 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} ]) }; - $query = qq|DELETE FROM shipto - WHERE trans_id = ? AND module = 'DO'|; - do_query($form, $dbh, $query, @values); + SL::DB::DeliveryOrder->new(id => $form->{id})->delete; - my $rc = $dbh->commit(); + my $spool = $::lx_office_conf{paths}->{spool}; + unlink map { "$spool/$_" } @spoolfiles if $spool; - if ($rc) { - foreach $spoolfile (@spoolfiles) { - unlink "$spool/$spoolfile" if $spoolfile; - } - } + 1; + }); $main::lxdebug->leave_sub(); @@ -425,7 +545,8 @@ sub delete { sub retrieve { $main::lxdebug->enter_sub(); - my ($self) = @_; + my $self = shift; + my %params = @_; my $myconfig = \%main::myconfig; my $form = $main::form; @@ -435,98 +556,138 @@ sub retrieve { my ($query, $query_add, @values, $sth, $ref); - if (!$form->{id}) { - $ref = selectfirst_hashref_query($form, $dbh, qq|SELECT current_date AS transdate, current_date AS reqdate|); + my $ic_cvar_configs = CVar->get_configs(module => 'IC', + dbh => $dbh); + + my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor'; + + my $mode = !$params{ids} ? 'default' : ref $params{ids} eq 'ARRAY' ? 'multi' : 'single'; + + if ($mode eq 'default') { + $ref = selectfirst_hashref_query($form, $dbh, qq|SELECT current_date AS transdate|); map { $form->{$_} = $ref->{$_} } keys %$ref; + + # if reqdate is not set from oe-workflow, set it to transdate (which is current date) + $form->{reqdate} ||= $form->{transdate}; + + # get last name used + $form->lastname_used($dbh, $myconfig, $vc) unless $form->{"${vc}_id"}; + + $main::lxdebug->leave_sub(); + + return 1; } - my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; + my @do_ids = map { conv_i($_) } ($mode eq 'multi' ? @{ $params{ids} } : ($params{ids})); + my $do_ids_placeholders = join(', ', ('?') x scalar(@do_ids)); - if ($form->{id}) { + # retrieve order for single id + # NOTE: this query is intended to fetch all information only ONCE. + # so if any of these infos is important (or even different) for any item, + # it will be killed out and then has to be fetched from the item scope query further down + $query = + qq|SELECT dord.cp_id, dord.donumber, dord.ordnumber, dord.transdate, dord.reqdate, + dord.shippingpoint, dord.shipvia, dord.notes, dord.intnotes, + e.name AS employee, dord.employee_id, dord.salesman_id, + dord.${vc}_id, cv.name AS ${vc}, + dord.closed, dord.reqdate, dord.department_id, dord.cusordnumber, + d.description AS department, dord.language_id, + dord.shipto_id, + dord.globalproject_id, dord.delivered, dord.transaction_description, + dord.taxzone_id, dord.taxincluded, dord.terms, (SELECT cu.name FROM currencies cu WHERE cu.id=dord.currency_id) AS currency, + dord.delivery_term_id + FROM delivery_orders dord + JOIN ${vc} cv ON (dord.${vc}_id = cv.id) + LEFT JOIN employee e ON (dord.employee_id = e.id) + LEFT JOIN department d ON (dord.department_id = d.id) + WHERE dord.id IN ($do_ids_placeholders)|; + $sth = prepare_execute_query($form, $dbh, $query, @do_ids); - # retrieve order for single id - # NOTE: this query is intended to fetch all information only ONCE. - # so if any of these infos is important (or even different) for any item, - # it will be killed out and then has to be fetched from the item scope query further down - $query = - qq|SELECT dord.cp_id, dord.donumber, dord.ordnumber, dord.transdate, dord.reqdate, - dord.shippingpoint, dord.shipvia, dord.notes, dord.intnotes, - e.name AS employee, dord.employee_id, dord.salesman_id, - dord.${vc}_id, cv.name AS ${vc}, - dord.closed, dord.reqdate, dord.department_id, dord.cusordnumber, - d.description AS department, dord.language_id, - dord.shipto_id, - dord.globalproject_id, dord.delivered, dord.transaction_description - FROM delivery_orders dord - JOIN ${vc} cv ON (dord.${vc}_id = cv.id) - LEFT JOIN employee e ON (dord.employee_id = e.id) - LEFT JOIN department d ON (dord.department_id = d.id) - WHERE dord.id = ?|; - $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - - $ref = $sth->fetchrow_hashref(NAME_lc); - $sth->finish(); + delete $form->{"${vc}_id"}; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + if ($form->{"${vc}_id"} && ($ref->{"${vc}_id"} != $form->{"${vc}_id"})) { + $sth->finish(); + $main::lxdebug->leave_sub(); + + return 0; + } map { $form->{$_} = $ref->{$_} } keys %$ref if ($ref); + $form->{donumber_array} .= $form->{donumber} . ' '; + } + $sth->finish(); - $form->{saved_donumber} = $form->{donumber}; + $form->{donumber_array} =~ s/\s*$//g; + + $form->{saved_donumber} = $form->{donumber}; - # if not given, fill transdate with current_date - $form->{transdate} = $form->current_date($myconfig) unless $form->{transdate}; + # if not given, fill transdate with current_date + $form->{transdate} = $form->current_date($myconfig) unless $form->{transdate}; + if ($mode eq 'single') { $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'DO'|; - $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); + $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); - $ref = $sth->fetchrow_hashref(NAME_lc); - delete($ref->{id}); + $ref = $sth->fetchrow_hashref("NAME_lc"); + delete $ref->{id}; map { $form->{$_} = $ref->{$_} } keys %$ref; - $sth->finish; + $sth->finish(); # 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, conv_i($form->{id})); + $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + while ($ref = $sth->fetchrow_hashref("NAME_lc")) { $form->{printed} .= "$ref->{formname} " if $ref->{printed}; $form->{emailed} .= "$ref->{formname} " if $ref->{emailed}; $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile}; } - $sth->finish; + $sth->finish(); map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued); - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); - - my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date"; + } else { + delete $form->{id}; + } - # retrieve individual items - # this query looks up all information about the items - # stuff different from the whole will not be overwritten, but saved with a suffix. - $query = - qq|SELECT doi.id AS delivery_order_items_id, - p.partnumber, p.assembly, doi.description, doi.qty, - doi.sellprice, doi.parts_id AS id, doi.unit, doi.discount, p.bin, p.notes AS partnotes, - doi.reqdate, doi.project_id, doi.serialnumber, doi.lastcost, - doi.ordnumber, doi.transdate, doi.cusordnumber, doi.longdescription, - doi.price_factor_id, doi.price_factor, doi.marge_price_factor, - pr.projectnumber, - pg.partsgroup - FROM delivery_order_items doi - JOIN parts p ON (doi.parts_id = p.id) - JOIN delivery_orders dord ON (doi.delivery_order_id = dord.id) - LEFT JOIN project pr ON (doi.project_id = pr.id) - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE doi.delivery_order_id = ? - ORDER BY doi.$oid{$myconfig->{dbdriver}}|; - - $form->{form_details} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id})); + # retrieve individual items + # this query looks up all information about the items + # stuff different from the whole will not be overwritten, but saved with a suffix. + $query = + qq|SELECT doi.id AS delivery_order_items_id, + p.partnumber, p.assembly, p.listprice, doi.description, doi.qty, + doi.sellprice, doi.parts_id AS id, doi.unit, doi.discount, p.notes AS partnotes, + doi.reqdate, doi.project_id, doi.serialnumber, doi.lastcost, + doi.ordnumber, doi.transdate, doi.cusordnumber, doi.longdescription, + doi.price_factor_id, doi.price_factor, doi.marge_price_factor, doi.pricegroup_id, + pr.projectnumber, dord.transdate AS dord_transdate, + pg.partsgroup + FROM delivery_order_items doi + JOIN parts p ON (doi.parts_id = p.id) + JOIN delivery_orders dord ON (doi.delivery_order_id = dord.id) + LEFT JOIN project pr ON (doi.project_id = pr.id) + LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) + WHERE doi.delivery_order_id IN ($do_ids_placeholders) + ORDER BY doi.oid|; + + $form->{form_details} = selectall_hashref_query($form, $dbh, $query, @do_ids); + + # Retrieve custom variables. + foreach my $doi (@{ $form->{form_details} }) { + my $cvars = CVar->get_custom_variables(dbh => $dbh, + module => 'IC', + sub_module => 'delivery_order_items', + trans_id => $doi->{delivery_order_items_id}, + ); + map { $doi->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars }; + } + if ($mode eq 'single') { my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in'; $query = - qq|SELECT qty, unit, bin_id, warehouse_id, chargenumber - FROM delivery_order_items_stock - WHERE delivery_order_item_id = ?|; + qq|SELECT qty, unit, bin_id, warehouse_id, chargenumber, bestbefore + FROM delivery_order_items_stock + WHERE delivery_order_item_id = ?|; my $sth = prepare_query($form, $dbh, $query); foreach my $doi (@{ $form->{form_details} }) { @@ -540,25 +701,19 @@ sub retrieve { } $sth->finish(); - - } else { - # get last name used - $form->lastname_used($dbh, $myconfig, $form->{vc}) unless $form->{"$form->{vc}_id"}; - } - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); $main::lxdebug->leave_sub(); + + return 1; } sub order_details { $main::lxdebug->enter_sub(); - my ($self) = @_; - - my $myconfig = \%main::myconfig; - my $form = $main::form; + my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->get_standard_dbh($myconfig); @@ -570,11 +725,10 @@ sub order_details { my @partsgroup = (); my $partsgroup; my $position = 0; + my $subtotal_header = 0; + my $subposition = 0; - my %oid = ('Pg' => 'oid', - 'Oracle' => 'rowid'); - - my (@project_ids, %projectnumbers); + my (@project_ids, %projectnumbers, %projectdescriptions); push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"}); @@ -589,24 +743,27 @@ sub order_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 = 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(); } $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}}; + $form->{"globalprojectdescription"} = + $projectdescriptions{$form->{"globalproject_id"}}; my $q_pg = qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup FROM assembly a JOIN parts p ON (a.parts_id = p.id) LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) WHERE a.bom = '1' - AND a.id = ? $sortorder|; + AND a.id = ?|; my $h_pg = prepare_query($form, $dbh, $q_pg); my $q_bin_wh = qq|SELECT (SELECT description FROM bin WHERE id = ?) AS bin, @@ -617,20 +774,31 @@ sub order_details { my $num_si = 0; + my $ic_cvar_configs = CVar->get_configs(module => 'IC'); + + $form->{TEMPLATE_ARRAYS} = { }; + IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form); + my @arrays = qw(runningnumber number description longdescription qty unit - partnotes serialnumber reqdate projectnumber + partnotes serialnumber reqdate projectnumber projectdescription si_runningnumber si_number si_description - si_warehouse si_bin si_chargenumber si_qty si_unit); + si_warehouse si_bin si_chargenumber si_bestbefore si_qty si_unit weight lineweight); + + map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays); + + push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs }; + $form->get_lists('price_factors' => 'ALL_PRICE_FACTORS'); + my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} }; + + my $totalweight = 0; my $sameitem = ""; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { $i = $item->[0]; next if (!$form->{"id_$i"}); - $position++; - if ($item->[1] ne $sameitem) { push(@{ $form->{description} }, qq|$item->[1]|); $sameitem = $item->[1]; @@ -641,19 +809,46 @@ sub order_details { $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); # add number, description and qty to $form->{number}, .... + if ($form->{"subtotal_$i"} && !$subtotal_header) { + $subtotal_header = $i; + $position = int($position); + $subposition = 0; + $position++; + } elsif ($subtotal_header) { + $subposition += 1; + $position = int($position); + $position = $position.".".$subposition; + } else { + $position = int($position); + $position++; + } my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 }; - push @{ $form->{runningnumber} }, $position; - push @{ $form->{number} }, $form->{"partnumber_$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->{partnotes} }, $form->{"partnotes_$i"}; - push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"}; - push @{ $form->{reqdate} }, $form->{"reqdate_$i"}; - push @{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}; + push @{ $form->{TEMPLATE_ARRAYS}{runningnumber} }, $position; + push @{ $form->{TEMPLATE_ARRAYS}{number} }, $form->{"partnumber_$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}{qty_nofmt} }, $form->{"qty_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{unit} }, $form->{"unit_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{partnotes} }, $form->{"partnotes_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{serialnumber} }, $form->{"serialnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{reqdate} }, $form->{"reqdate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}; + push @{ $form->{TEMPLATE_ARRAYS}{projectdescription} }, + $projectdescriptions{$form->{"project_id_$i"}}; + + if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) { + $subtotal_header = 0; + } + + 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; if ($form->{"assembly_$i"}) { $sameitem = ""; @@ -662,23 +857,22 @@ sub order_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|; } do_statement($form, $h_pg, $q_pg, conv_i($form->{"id_$i"})); - while (my $ref = $h_pg->fetchrow_hashref(NAME_lc)) { + while (my $ref = $h_pg->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); } + push(@{ $form->{TEMPLATE_ARRAYS}->{"description"} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq| -- $ref->{partnumber}, $ref->{description}|); - push(@{ $form->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|); - - map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); + map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays)); } } @@ -691,21 +885,35 @@ sub order_details { do_statement($form, $h_bin_wh, $q_bin_wh, conv_i($si->{bin_id}), conv_i($si->{warehouse_id})); my $bin_wh = $h_bin_wh->fetchrow_hashref(); - push @{ $form->{si_runningnumber} }, $num_si; - push @{ $form->{si_number} }, $form->{"partnumber_$i"}; - push @{ $form->{si_description} }, $form->{"description_$i"}; - push @{ $form->{si_warehouse} }, $bin_wh->{warehouse}; - push @{ $form->{si_bin} }, $bin_wh->{bin}; - push @{ $form->{si_chargenumber} }, $si->{chargenumber}; - push @{ $form->{si_qty} }, $form->format_amount($myconfig, $si->{qty} * 1); - push @{ $form->{si_unit} }, $si->{unit}; + push @{ $form->{TEMPLATE_ARRAYS}{si_runningnumber}[$position-1] }, $num_si; + push @{ $form->{TEMPLATE_ARRAYS}{si_number}[$position-1] }, $form->{"partnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{si_description}[$position-1] }, $form->{"description_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{si_warehouse}[$position-1] }, $bin_wh->{warehouse}; + push @{ $form->{TEMPLATE_ARRAYS}{si_bin}[$position-1] }, $bin_wh->{bin}; + push @{ $form->{TEMPLATE_ARRAYS}{si_chargenumber}[$position-1] }, $si->{chargenumber}; + push @{ $form->{TEMPLATE_ARRAYS}{si_bestbefore}[$position-1] }, $si->{bestbefore}; + push @{ $form->{TEMPLATE_ARRAYS}{si_qty}[$position-1] }, $form->format_amount($myconfig, $si->{qty} * 1); + push @{ $form->{TEMPLATE_ARRAYS}{si_qty_nofmt}[$position-1] }, $si->{qty} * 1; + push @{ $form->{TEMPLATE_ARRAYS}{si_unit}[$position-1] }, $si->{unit}; } } + + push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, + CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_) + for @{ $ic_cvar_configs }; } + $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3); + $form->{totalweight_nofmt} = $totalweight; + my $defaults = AM->get_defaults(); + $form->{weightunit} = $defaults->{weightunit}; + $h_pg->finish(); $h_bin_wh->finish(); + $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(); @@ -716,6 +924,8 @@ sub project_description { my ($self, $dbh, $id) = @_; + my $form = $main::form; + my $query = qq|SELECT description FROM project WHERE id = ?|; my ($value) = selectrow_query($form, $dbh, $query, $id); @@ -750,7 +960,7 @@ sub unpack_stock_information { } sub get_item_availability { - $main::lxdebug->enter_sub(); + $::lxdebug->enter_sub; my $self = shift; my %params = @_; @@ -758,23 +968,22 @@ sub get_item_availability { Common::check_params(\%params, qw(parts_id)); my @parts_ids = 'ARRAY' eq ref $params{parts_id} ? @{ $params{parts_id} } : ($params{parts_id}); - my $form = $main::form; my $query = - qq|SELECT i.warehouse_id, i.bin_id, i.chargenumber, SUM(qty) AS qty, i.parts_id, + qq|SELECT i.warehouse_id, i.bin_id, i.chargenumber, i.bestbefore, SUM(qty) AS qty, i.parts_id, w.description AS warehousedescription, b.description AS bindescription FROM inventory i LEFT JOIN warehouse w ON (i.warehouse_id = w.id) LEFT JOIN bin b ON (i.bin_id = b.id) WHERE (i.parts_id IN (| . join(', ', ('?') x scalar(@parts_ids)) . qq|)) - AND qty > 0 - GROUP BY i.warehouse_id, i.bin_id, i.chargenumber, i.parts_id, w.description, b.description - ORDER BY LOWER(w.description), LOWER(b.description), LOWER(i.chargenumber)|; - - my $contents = selectall_hashref_query($form, $form->get_standard_dbh($myconfig), $query, @parts_ids); + GROUP BY i.warehouse_id, i.bin_id, i.chargenumber, i.bestbefore, i.parts_id, w.description, b.description + HAVING SUM(qty) > 0 + ORDER BY LOWER(w.description), LOWER(b.description), LOWER(i.chargenumber), i.bestbefore +|; + my $contents = selectall_hashref_query($::form, $::form->get_standard_dbh, $query, @parts_ids); - $main::lxdebug->leave_sub(); + $::lxdebug->leave_sub; return @{ $contents }; } @@ -793,7 +1002,7 @@ sub check_stock_availability { my $dbh = $form->get_standard_dbh($myconfig); - my $units = AM->retrieve_units($myconfig, $form, "dimension"); + my $units = AM->retrieve_units($myconfig, $form); my ($partunit) = selectrow_query($form, $dbh, qq|SELECT unit FROM parts WHERE id = ?|, conv_i($params{parts_id})); my $unit_factor = $units->{$partunit}->{factor} || 1; @@ -808,7 +1017,8 @@ sub check_stock_availability { foreach my $row (@contents) { next if (($row->{bin_id} != $sinfo->{bin_id}) || ($row->{warehouse_id} != $sinfo->{warehouse_id}) || - ($row->{chargenumber} ne $sinfo->{chargenumber})); + ($row->{chargenumber} ne $sinfo->{chargenumber}) || + ($row->{bestbefore} ne $sinfo->{bestbefore})); $found = 1; @@ -856,11 +1066,13 @@ sub transfer_in_out { "${prefix}_warehouse_id" => $request->{warehouse_id}, "${prefix}_bin_id" => $request->{bin_id}, 'chargenumber' => $request->{chargenumber}, + 'bestbefore' => $request->{bestbefore}, 'qty' => $request->{qty}, 'unit' => $request->{unit}, 'oe_id' => $form->{id}, 'shippingdate' => 'current_date', 'transfer_type' => $params{direction} eq 'in' ? 'stock' : 'shipped', + 'project_id' => $request->{project_id}, }; } @@ -869,4 +1081,75 @@ sub transfer_in_out { $main::lxdebug->leave_sub(); } +sub get_shipped_qty { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(type oe_id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my @links = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'from_id' => $params{oe_id}, + 'to_table' => 'delivery_orders'); + my @values = map { $_->{to_id} } @links; + + if (!scalar @values) { + $main::lxdebug->leave_sub(); + return (); + } + + my $query = + qq|SELECT doi.parts_id, doi.qty, doi.unit, p.unit AS partunit + FROM delivery_order_items doi + LEFT JOIN delivery_orders o ON (doi.delivery_order_id = o.id) + LEFT JOIN parts p ON (doi.parts_id = p.id) + WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|; + + my %ship = (); + my $entries = selectall_hashref_query($form, $dbh, $query, @values); + my $all_units = AM->retrieve_all_units(); + + foreach my $entry (@{ $entries }) { + $entry->{qty} *= AM->convert_unit($entry->{unit}, $entry->{partunit}, $all_units); + + if (!$ship{$entry->{parts_id}}) { + $ship{$entry->{parts_id}} = $entry; + } else { + $ship{$entry->{parts_id}}->{qty} += $entry->{qty}; + } + } + + $main::lxdebug->leave_sub(); + + return %ship; +} + +sub is_marked_as_delivered { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my ($delivered) = selectfirst_array_query($form, $dbh, qq|SELECT delivered FROM delivery_orders WHERE id = ?|, conv_i($params{id})); + + $main::lxdebug->leave_sub(); + + return $delivered ? 1 : 0; +} + + 1;