my $query =
qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.transdate,
my $query =
qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.transdate,
- ct.name, dord.${vc}_id, dord.globalproject_id,
+ 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,
dord.closed, dord.delivered, dord.shippingpoint, dord.shipvia,
dord.transaction_description,
pr.projectnumber AS globalprojectnumber,
e.name AS employee,
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)
e.name AS employee,
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)
push @where, ($form->{type} eq 'sales_delivery_order' ? '' : 'NOT ') . qq|COALESCE(dord.is_sales, FALSE)|;
push @where, ($form->{type} eq 'sales_delivery_order' ? '' : 'NOT ') . qq|COALESCE(dord.is_sales, FALSE)|;
$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
id, delivery_order_id, parts_id, description, longdescription, qty, base_qty,
sellprice, discount, unit, reqdate, project_id, serialnumber,
ordnumber, transdate, cusordnumber,
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
- 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';
my $h_item_stock = prepare_query($form, $dbh, $q_item_stock);
my $in_out = $form->{type} =~ /^sales/ ? 'out' : 'in';
# set values to 0 if nothing entered
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"});
$form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
# set values to 0 if nothing entered
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"});
$form->{"sellprice_$i"} = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
my $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
my $linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);
do_statement($form, $h_item_id, $q_item_id);
my ($item_id) = $h_item_id->fetchrow_array();
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"} / 100,
# 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"} / 100,
- $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
+ $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"}),
$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"}),
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}),
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}),
do_statement($form, $h_item_stock, $q_item_stock, @values);
}
do_statement($form, $h_item_stock, $q_item_stock, @values);
}
@values = ($form->{donumber}, $form->{ordnumber},
$form->{cusordnumber}, conv_date($form->{transdate}),
conv_i($form->{vendor_id}), conv_i($form->{customer_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}),
$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}),
conv_i($form->{salesman_id}), conv_i($form->{cp_id}),
$form->{transaction_description},
$form->{type} =~ /^sales/ ? 't' : 'f',
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->{curr},
+ 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);
conv_i($form->{id}));
do_query($form, $dbh, $query, @values);
'to_table' => 'delivery_orders',
'to_id' => $params{do_id});
'to_table' => 'delivery_orders',
'to_id' => $params{do_id});
my $mode = !$params{ids} ? 'default' : ref $params{ids} eq 'ARRAY' ? 'multi' : 'single';
if ($mode eq 'default') {
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, current_date AS reqdate|);
+ $ref = selectfirst_hashref_query($form, $dbh, qq|SELECT current_date AS transdate|);
d.description AS department, dord.language_id,
dord.shipto_id,
dord.globalproject_id, dord.delivered, dord.transaction_description,
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, dord.curr
+ 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)
FROM delivery_orders dord
JOIN ${vc} cv ON (dord.${vc}_id = cv.id)
LEFT JOIN employee e ON (dord.employee_id = e.id)
$form->{saved_donumber} = $form->{donumber};
# if not given, fill transdate with current_date
$form->{saved_donumber} = $form->{donumber};
# if not given, fill transdate with current_date
# stuff different from the whole will not be overwritten, but saved with a suffix.
$query =
qq|SELECT doi.id AS delivery_order_items_id,
# 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.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,
+ 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
pr.projectnumber, dord.transdate AS dord_transdate,
pg.partsgroup
FROM delivery_order_items doi
- 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);
FROM delivery_order_items_stock
WHERE delivery_order_item_id = ?|;
my $sth = prepare_query($form, $dbh, $query);
join(", ", map("?", @project_ids)) . ")";
$sth = prepare_execute_query($form, $dbh, $query, @project_ids);
while (my $ref = $sth->fetchrow_hashref()) {
$projectnumbers{$ref->{id}} = $ref->{projectnumber};
join(", ", map("?", @project_ids)) . ")";
$sth = prepare_execute_query($form, $dbh, $query, @project_ids);
while (my $ref = $sth->fetchrow_hashref()) {
$projectnumbers{$ref->{id}} = $ref->{projectnumber};
my $q_pg = qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
FROM assembly a
my $q_pg = qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
FROM assembly a
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}{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}{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}{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"}};
$sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
push(@{ $form->{description} }, $sameitem);
}
$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));
push(@{ $form->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
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_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_qty}[$position-1] }, $form->format_amount($myconfig, $si->{qty} * 1);
push @{ $form->{TEMPLATE_ARRAYS}{si_qty}[$position-1] }, $form->format_amount($myconfig, $si->{qty} * 1);
- map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
+ push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
+ CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
+ for @{ $ic_cvar_configs };
Common::check_params(\%params, qw(parts_id));
my @parts_ids = 'ARRAY' eq ref $params{parts_id} ? @{ $params{parts_id} } : ($params{parts_id});
Common::check_params(\%params, qw(parts_id));
my @parts_ids = 'ARRAY' eq ref $params{parts_id} ? @{ $params{parts_id} } : ($params{parts_id});
- 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|))
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
- 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);
+ my $contents = selectall_hashref_query($::form, $::form->get_standard_dbh, $query, @parts_ids);
foreach my $row (@contents) {
next if (($row->{bin_id} != $sinfo->{bin_id}) ||
($row->{warehouse_id} != $sinfo->{warehouse_id}) ||
foreach my $row (@contents) {
next if (($row->{bin_id} != $sinfo->{bin_id}) ||
($row->{warehouse_id} != $sinfo->{warehouse_id}) ||
"${prefix}_warehouse_id" => $request->{warehouse_id},
"${prefix}_bin_id" => $request->{bin_id},
'chargenumber' => $request->{chargenumber},
"${prefix}_warehouse_id" => $request->{warehouse_id},
"${prefix}_bin_id" => $request->{bin_id},
'chargenumber' => $request->{chargenumber},
'qty' => $request->{qty},
'unit' => $request->{unit},
'oe_id' => $form->{id},
'shippingdate' => 'current_date',
'transfer_type' => $params{direction} eq 'in' ? 'stock' : 'shipped',
'qty' => $request->{qty},
'unit' => $request->{unit},
'oe_id' => $form->{id},
'shippingdate' => 'current_date',
'transfer_type' => $params{direction} eq 'in' ? 'stock' : 'shipped',