X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=fe2318cc823ca3173252951228813c6b15335e85;hb=5547891f42ef3b13d477eba65bb0537ece1d703c;hp=49dbb9f4ddd8ea5ef98d3b9569f065268caceb4b;hpb=6376d92f01632a5dccf74ef58d17738779741e4e;p=kivitendo-erp.git diff --git a/SL/DO.pm b/SL/DO.pm index 49dbb9f4d..fe2318cc8 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -43,6 +43,8 @@ use SL::DBUtils; use SL::RecordLinks; use SL::IC; +use strict; + sub transactions { $main::lxdebug->enter_sub(); @@ -58,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, @@ -102,6 +104,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}); @@ -252,8 +258,8 @@ sub save { 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'; @@ -271,14 +277,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; @@ -289,7 +294,7 @@ 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"}), @@ -303,7 +308,7 @@ sub save { 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); } @@ -497,6 +502,7 @@ sub delete { my $spoolfile; my @spoolfiles = (); + my @values; while (($spoolfile) = $sth->fetchrow_array) { push @spoolfiles, $spoolfile; @@ -604,7 +610,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(); @@ -617,6 +623,8 @@ sub retrieve { } $sth->finish(); + $form->{donumber_array} =~ s/\s*$//g; + $form->{saved_donumber} = $form->{donumber}; # if not given, fill transdate with current_date @@ -626,7 +634,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(); @@ -635,7 +643,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}; @@ -686,7 +694,7 @@ sub retrieve { 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); @@ -733,7 +741,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"}); @@ -748,24 +756,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, @@ -783,14 +794,17 @@ sub order_details { 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, @tax_arrays); + 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) { $i = $item->[0]; @@ -822,6 +836,8 @@ sub order_details { 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 = ""; @@ -837,7 +853,7 @@ 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} : "--"; @@ -865,6 +881,7 @@ sub order_details { 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_unit}[$position-1] }, $si->{unit}; } @@ -886,6 +903,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); @@ -929,18 +948,19 @@ sub get_item_availability { my @parts_ids = 'ARRAY' eq ref $params{parts_id} ? @{ $params{parts_id} } : ($params{parts_id}); my $form = $main::form; + my $myconfig = \%main::myconfig; 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|)) - GROUP BY i.warehouse_id, i.bin_id, i.chargenumber, i.parts_id, w.description, b.description + 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) + ORDER BY LOWER(w.description), LOWER(b.description), LOWER(i.chargenumber), i.bestbefore |; my $contents = selectall_hashref_query($form, $form->get_standard_dbh($myconfig), $query, @parts_ids); @@ -978,7 +998,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; @@ -1026,11 +1047,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}, }; }