X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=8d61b0f1fb0869fad5ceb247a078c8cffcd45836;hb=a00cdb315e4f27fb5074fda82fd0d270a2fc77c0;hp=44050b89b7032c24ade0da1c8f517703a05d0eac;hpb=4a9b86a56fa151d2fde072202ccabd5fc1fbaacc;p=kivitendo-erp.git diff --git a/SL/DO.pm b/SL/DO.pm index 44050b89b..8d61b0f1f 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -38,7 +38,12 @@ use YAML; use SL::AM; use SL::Common; +use SL::CVar; use SL::DBUtils; +use SL::RecordLinks; +use SL::IC; + +use strict; sub transactions { $main::lxdebug->enter_sub(); @@ -55,7 +60,7 @@ sub transactions { my $vc = $form->{vc} eq "customer" ? "customer" : "vendor"; - $query = + my $query = qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.transdate, ct.name, dord.${vc}_id, dord.globalproject_id, dord.closed, dord.delivered, dord.shippingpoint, dord.shipvia, @@ -71,17 +76,16 @@ sub transactions { 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}); } @@ -99,6 +103,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}); @@ -106,7 +114,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)"; } @@ -141,12 +150,13 @@ sub transactions { "transaction_description" => "dord.transaction_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); @@ -188,6 +198,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}); @@ -238,14 +251,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'; @@ -263,14 +276,13 @@ 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; @@ -281,30 +293,39 @@ sub save { @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->{"sellprice_$i"}, $form->{"discount_$i"} / 100, $form->{"unit_$i"}, conv_date($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"}), + conv_i($form->{"pricegroup_id_$i"})); 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}); # save DO record $query = @@ -314,7 +335,7 @@ 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 = ?, curr = ? WHERE id = ?|; @values = ($form->{donumber}, $form->{ordnumber}, @@ -328,6 +349,7 @@ 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}), substr($form->{currency}, 0, 3), conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -342,7 +364,17 @@ sub save { # save printed, emailed, queued $form->save_status($dbh); - $self->close_order_if_delivered('do_id' => $form->{id}, + # 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,); @@ -350,14 +382,14 @@ sub save { $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_if_delivered { +sub mark_orders_if_delivered { $main::lxdebug->enter_sub(); my $self = shift; @@ -365,41 +397,32 @@ sub close_order_if_delivered { Common::check_params(\%params, qw(do_id type)); - my $myconfig = \%main::myconfig; - my $form = $main::form; - - my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - - my $query = qq|SELECT ordnumber FROM delivery_orders WHERE id = ?|; - my ($ordnumber) = selectfirst_array_query($form, $dbh, $query, conv_i($params{do_id})); + my $myconfig = \%main::myconfig; + my $form = $main::form; - return $main::lxdebug->leave_sub() if (!$ordnumber); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my $vc = $params{type} eq 'purchase' ? 'vendor' : 'customer'; - $query = qq|SELECT id - FROM oe - WHERE NOT COALESCE(quotation, FALSE) - AND (COALESCE(${vc}_id, 0) > 0) - AND (ordnumber = ?) - ORDER BY id - LIMIT 1|; + my @links = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'to_id' => $params{do_id}); - my ($oe_id) = selectfirst_array_query($form, $dbh, $query, $ordnumber); + 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 %shipped = $self->get_shipped_qty('type' => $params{type}, - 'ordnumber' => $ordnumber,); - my %ordered = (); - - $query = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit + 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}; @@ -424,16 +447,42 @@ sub close_order_if_delivered { if ($delivered) { $query = qq|UPDATE oe - SET delivered = TRUE, closed = TRUE + SET delivered = TRUE WHERE id = ?|; do_query($form, $dbh, $query, $oe_id); - $dbh->commit() if (!$params{dbh}); } $main::lxdebug->leave_sub(); } +sub close_orders { + $main::lxdebug->enter_sub(); + + 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 $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|UPDATE delivery_orders SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar(@{ $params{ids} })) . qq|)|; + + do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} }); + + $dbh->commit() unless ($params{dbh}); + + $main::lxdebug->leave_sub(); +} + sub delete { $main::lxdebug->enter_sub(); @@ -441,7 +490,7 @@ sub delete { my $myconfig = \%main::myconfig; my $form = $main::form; - my $spool = $main::spool; + my $spool = $::lx_office_conf{paths}->{spool}; # connect to database my $dbh = $form->get_standard_dbh($myconfig); @@ -452,6 +501,7 @@ sub delete { my $spoolfile; my @spoolfiles = (); + my @values; while (($spoolfile) = $sth->fetchrow_array) { push @spoolfiles, $spoolfile; @@ -515,6 +565,9 @@ sub retrieve { my ($query, $query_add, @values, $sth, $ref); + 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'; @@ -546,7 +599,8 @@ sub retrieve { 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.globalproject_id, dord.delivered, dord.transaction_description, + dord.taxzone_id, dord.taxincluded, dord.terms, dord.curr AS currency FROM delivery_orders dord JOIN ${vc} cv ON (dord.${vc}_id = cv.id) LEFT JOIN employee e ON (dord.employee_id = e.id) @@ -555,7 +609,7 @@ sub retrieve { $sth = prepare_execute_query($form, $dbh, $query, @do_ids); delete $form->{"${vc}_id"}; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { if ($form->{"${vc}_id"} && ($ref->{"${vc}_id"} != $form->{"${vc}_id"})) { $sth->finish(); $main::lxdebug->leave_sub(); @@ -564,9 +618,15 @@ sub retrieve { } map { $form->{$_} = $ref->{$_} } keys %$ref if ($ref); + $form->{donumber_array} .= $form->{donumber} . ' '; } $sth->finish(); + # remove any trailing whitespace + $form->{currency} =~ s/\s*$//; + + $form->{donumber_array} =~ s/\s*$//g; + $form->{saved_donumber} = $form->{donumber}; # if not given, fill transdate with current_date @@ -576,7 +636,7 @@ sub retrieve { $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'DO'|; $sth = prepare_execute_query($form, $dbh, $query, $form->{id}); - $ref = $sth->fetchrow_hashref(NAME_lc); + $ref = $sth->fetchrow_hashref("NAME_lc"); delete $ref->{id}; map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish(); @@ -585,7 +645,7 @@ sub retrieve { $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})); - 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}; @@ -605,12 +665,12 @@ sub retrieve { # 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, + p.partnumber, p.assembly, p.listprice, 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, + 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) @@ -622,11 +682,21 @@ sub retrieve { $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 + 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); @@ -644,7 +714,7 @@ sub retrieve { $sth->finish(); } - Common::webdav_folder($form) if ($main::webdav); + Common::webdav_folder($form); $main::lxdebug->leave_sub(); @@ -673,7 +743,7 @@ sub order_details { 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"}); @@ -688,24 +758,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, @@ -716,11 +789,23 @@ sub order_details { my $num_si = 0; + my $ic_cvar_configs = CVar->get_configs(module => 'IC'); + + $form->{TEMPLATE_ARRAYS} = { }; + IC->prepare_parts_for_printing(); + 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); + + 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 $sameitem = ""; foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) { @@ -743,16 +828,19 @@ sub order_details { 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->{"assembly_$i"}) { $sameitem = ""; @@ -768,13 +856,12 @@ sub order_details { 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)); $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--"; push(@{ $form->{description} }, $sameitem); } - push(@{ $form->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|); map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays)); @@ -790,16 +877,22 @@ 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 }; } $h_pg->finish(); @@ -815,6 +908,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); @@ -849,7 +944,7 @@ sub unpack_stock_information { } sub get_item_availability { - $main::lxdebug->enter_sub(); + $::lxdebug->enter_sub; my $self = shift; my %params = @_; @@ -857,23 +952,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)|; + 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); - my $contents = selectall_hashref_query($form, $form->get_standard_dbh($myconfig), $query, @parts_ids); - - $main::lxdebug->leave_sub(); + $::lxdebug->leave_sub; return @{ $contents }; } @@ -907,7 +1001,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; @@ -955,11 +1050,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}, }; } @@ -974,29 +1071,37 @@ sub get_shipped_qty { my $self = shift; my %params = @_; - Common::check_params(\%params, qw(type ordnumber)); + 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 $notsales = $params{type} eq 'sales' ? '' : 'NOT'; + 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 = + 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 ($notsales o.is_sales) - AND (o.ordnumber = ?)|; + WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|; my %ship = (); - my $entries = selectall_hashref_query($form, $dbh, $query, $params{ordnumber}); + my $entries = selectall_hashref_query($form, $dbh, $query, @values); my $all_units = AM->retrieve_all_units(); foreach my $entry (@{ $entries }) { - $entry->{qty} *= $all_units->{$entry->{unit}}->{factor} / $all_units->{$entry->{partunit}}->{factor}; + $entry->{qty} *= AM->convert_unit($entry->{unit}, $entry->{partunit}, $all_units); if (!$ship{$entry->{parts_id}}) { $ship{$entry->{parts_id}} = $entry;