qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
qq| o.transaction_description, | .
qq| o.marge_total, o.marge_percent, | .
qq| o.closed, o.delivered, o.quonumber, o.cusordnumber, o.shippingpoint, o.shipvia, | .
qq| o.transaction_description, | .
qq| o.marge_total, o.marge_percent, | .
qq| pr.projectnumber AS globalprojectnumber, | .
qq| e.name AS employee, s.name AS salesman, | .
qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
qq| pr.projectnumber AS globalprojectnumber, | .
qq| e.name AS employee, s.name AS salesman, | .
qq| ct.${vc}number AS vcnumber, ct.country, ct.ustid, ct.business_id, | .
qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
qq| AND ex.transdate = o.transdate) | .
qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
qq| AND ex.transdate = o.transdate) | .
qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
qq|$periodic_invoices_joins | .
qq|WHERE (o.quotation = ?) |;
qq|LEFT JOIN tax_zones tz ON (o.taxzone_id = tz.id) | .
qq|$periodic_invoices_joins | .
qq|WHERE (o.quotation = ?) |;
if (!$main::auth->assert('sales_all_edit', 1)) {
$query .= " AND o.employee_id = (select id from employee where login= ?)";
if (!$main::auth->assert('sales_all_edit', 1)) {
$query .= " AND o.employee_id = (select id from employee where login= ?)";
+ if($form->{insertdatefrom}) {
+ $query .= qq| AND o.itime::DATE >= ?|;
+ push(@values, conv_date($form->{insertdatefrom}));
+ }
+
+ if($form->{insertdateto}) {
+ $query .= qq| AND o.itime::DATE <= ?|;
+ push(@values, conv_date($form->{insertdateto}));
+ }
+
if ($form->{shippingpoint}) {
$query .= qq| AND o.shippingpoint ILIKE ?|;
push(@values, '%' . $form->{shippingpoint} . '%');
if ($form->{shippingpoint}) {
$query .= qq| AND o.shippingpoint ILIKE ?|;
push(@values, '%' . $form->{shippingpoint} . '%');
+ my ($cvar_where, @cvar_values) = CVar->build_filter_query('module' => 'CT',
+ 'trans_id_field' => 'ct.id',
+ 'filter' => $form,
+ );
+ if ($cvar_where) {
+ $query .= qq| AND ($cvar_where)|;
+ push @values, @cvar_values;
+ }
+
- my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name")));
+ my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"), "o.itime"));
my %allowed_sort_columns = (
"transdate" => "o.transdate",
"reqdate" => "o.reqdate",
my %allowed_sort_columns = (
"transdate" => "o.transdate",
"reqdate" => "o.reqdate",
"shipvia" => "o.shipvia",
"transaction_description" => "o.transaction_description",
"shippingpoint" => "o.shippingpoint",
"shipvia" => "o.shipvia",
"transaction_description" => "o.transaction_description",
"shippingpoint" => "o.shippingpoint",
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
my $query = qq|SELECT id FROM employee WHERE login = ?|;
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
my $query = qq|SELECT id FROM employee WHERE login = ?|;
- my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login});
+ my ($e_id) = selectrow_query($form, $dbh, $query, $::myconfig{login});
$query =
qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
$query =
qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount,
- $query = qq|DELETE FROM custom_variables
- WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))
- AND (sub_module = 'orderitems')
- AND (trans_id IN (SELECT id FROM orderitems WHERE trans_id = ?))|;
- do_query($form, $dbh, $query, $form->{id});
-
$query = qq|DELETE FROM shipto | .
qq|WHERE trans_id = ? AND module = 'OE'|;
do_query($form, $dbh, $query, $form->{id});
$query = qq|DELETE FROM shipto | .
qq|WHERE trans_id = ? AND module = 'OE'|;
do_query($form, $dbh, $query, $form->{id});
+ CVar->get_non_editable_ic_cvars(form => $form,
+ dbh => $dbh,
+ row => $i,
+ sub_module => 'orderitems',
+ may_converted_from => ['orderitems', 'invoice']);
+
+ my $position = $i;
+
# save detail record in orderitems table
if (! $form->{"orderitems_id_$i"}) {
$query = qq|SELECT nextval('orderitemsid')|;
($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
# save detail record in orderitems table
if (! $form->{"orderitems_id_$i"}) {
$query = qq|SELECT nextval('orderitemsid')|;
($form->{"orderitems_id_$i"}) = selectrow_query($form, $dbh, $query);
- $query = qq|INSERT INTO orderitems (id) VALUES (?)|;
- do_query($form, $dbh, $query, $form->{"orderitems_id_$i"});
+ $query = qq|INSERT INTO orderitems (id, position) VALUES (?, ?)|;
+ do_query($form, $dbh, $query, $form->{"orderitems_id_$i"}, conv_i($position));
my $orderitems_id = $form->{"orderitems_id_$i"};
push @processed_orderitems, $orderitems_id;
$query = <<SQL;
UPDATE orderitems SET
my $orderitems_id = $form->{"orderitems_id_$i"};
push @processed_orderitems, $orderitems_id;
$query = <<SQL;
UPDATE orderitems SET
- trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
+ trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
sellprice = ?, discount = ?, unit = ?, reqdate = ?, project_id = ?, serialnumber = ?, ship = ?,
pricegroup_id = ?, ordnumber = ?, transdate = ?, cusordnumber = ?, subtotal = ?,
marge_percent = ?, marge_total = ?, lastcost = ?, price_factor_id = ?,
- conv_i($form->{id}), conv_i($form->{"id_$i"}),
+ conv_i($form->{id}), conv_i($position), conv_i($form->{"id_$i"}),
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
$form->{"qty_$i"}, $baseqty,
$fxsellprice, $form->{"discount_$i"},
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}),
$form->{"qty_$i"}, $baseqty,
$fxsellprice, $form->{"discount_$i"},
+
+ # link previous items with orderitems
+ foreach (qw(orderitems invoice)) {
+ if (!$form->{saveasnew} && !$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => $_,
+ 'from_ids' => $form->{"converted_from_${_}_id_$i"},
+ 'to_table' => 'orderitems',
+ 'to_id' => $orderitems_id,
+ );
+ }
+ delete $form->{"converted_from_${_}_id_$i"};
+ }
# search for orphaned ids
$query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
@values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
# search for orphaned ids
$query = sprintf 'SELECT id FROM orderitems WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_orderitems;
@values = (conv_i($form->{id}), map { conv_i($_) } @processed_orderitems);
conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
$amount, $netamount, conv_date($reqdate),
$form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
conv_i($form->{vendor_id}), conv_i($form->{customer_id}),
$amount, $netamount, conv_date($reqdate),
$form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
- $form->{shipvia}, $form->{notes}, $form->{intnotes},
+ $form->{shipvia}, $restricter->process($form->{notes}), $form->{intnotes},
$form->{currency}, $form->{closed} ? 't' : 'f',
$form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
$quotation, conv_i($form->{department_id}),
$form->{currency}, $form->{closed} ? 't' : 'f',
$form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
$quotation, conv_i($form->{department_id}),
$form->{convert_from_oe_ids} =~ s/^\s+//;
$form->{convert_from_oe_ids} =~ s/\s+$//;
my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
delete $form->{convert_from_oe_ids};
$form->{convert_from_oe_ids} =~ s/^\s+//;
$form->{convert_from_oe_ids} =~ s/\s+$//;
my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids};
delete $form->{convert_from_oe_ids};
-
- if (scalar @convert_from_oe_ids) {
- RecordLinks->create_links('dbh' => $dbh,
- 'mode' => 'ids',
- 'from_table' => 'oe',
- 'from_ids' => \@convert_from_oe_ids,
- 'to_table' => 'oe',
- 'to_id' => $form->{id},
- );
-
+ @{ $form->{convert_from_oe_ids} } = @convert_from_oe_ids;
+ foreach (qw(ar oe)) {
+ if (!$form->{useasnew} && $form->{"convert_from_${_}_ids"}) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => $_,
+ 'from_ids' => $form->{"convert_from_${_}_ids"},
+ 'to_table' => 'oe',
+ 'to_id' => $form->{id},
+ );
+ delete $form->{"convert_from_${_}_ids"};
+ }
$self->_close_quotations_rfqs('dbh' => $dbh,
'from_id' => \@convert_from_oe_ids,
$self->_close_quotations_rfqs('dbh' => $dbh,
'from_id' => \@convert_from_oe_ids,
- my $wday = (localtime(time))[6];
- my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
+ my $extra_days = $form->{type} eq 'sales_quotation' ? $::instance_conf->get_reqdate_interval : 1;
+ my $next_workday = DateTime->today_local->add(days => $extra_days);
+ my $day_of_week = $next_workday->day_of_week;
- # if we have a client configured interval for sales quotation, we add this
- $next_workday += $::instance_conf->get_reqdate_interval if ($::instance_conf->get_reqdate_interval &&
- $form->{type} eq 'sales_quotation' );
+ $next_workday->add(days => (8 - $day_of_week)) if $day_of_week >= 6;
- $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|;
+ $form->{transdate} = DateTime->today_local->to_kivitendo;
+ $form->{reqdate} = $next_workday->to_kivitendo;
o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id,
- o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id
- , o.order_probability, o.expected_billing_date
+ o.globalproject_id, o.delivered, o.transaction_description, o.delivery_term_id,
+ o.itime::DATE AS insertdate, o.order_probability, o.expected_billing_date
($form->{id}
? qq|WHERE o.trans_id = ?|
: qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
($form->{id}
? qq|WHERE o.trans_id = ?|
: qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
@ids = $form->{id} ? ($form->{id}) : @ids;
$sth = prepare_execute_query($form, $dbh, $query, @values);
@ids = $form->{id} ? ($form->{id}) : @ids;
$sth = prepare_execute_query($form, $dbh, $query, @values);
# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
my $oe_query = qq|SELECT * FROM oe WHERE id = ?|;
my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
$order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
my $order = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
$order->{orderitems} = selectall_hashref_query( $form, $dbh, $oi_query, conv_i($params{id}));
+ # get some values of parts from db on store them in extra array,
+ # so that they can be sorted in later
+ my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
+ my @prepared_arrays = keys %prepared_template_arrays;
+
my $ic_cvar_configs = CVar->get_configs(module => 'IC');
my $project_cvar_configs = CVar->get_configs(module => 'Projects');
my @arrays =
my $ic_cvar_configs = CVar->get_configs(module => 'IC');
my $project_cvar_configs = CVar->get_configs(module => 'Projects');
my @arrays =
- qw(runningnumber number description longdescription qty ship unit bin
- partnotes serialnumber reqdate sellprice listprice netprice
- discount p_discount discount_sub nodiscount_sub
- linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
- price_factor price_factor_name partsgroup weight lineweight);
+ qw(runningnumber number description longdescription qty qty_nofmt ship ship_nofmt unit bin
+ partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
+ discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
+ linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
+ price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);
push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
push @arrays, map { "project_cvar_$_->{name}" } @{ $project_cvar_configs };
my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
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}->{runningnumber} }, $position;
push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
push @{ $form->{TEMPLATE_ARRAYS}->{reqdate} }, $form->{"reqdate_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{sellprice_nofmt} }, $form->parse_amount($myconfig, $form->{"sellprice_$i"});
- push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->format_amount($myconfig, $form->{"listprice_$i"}, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{listprice_nofmt} }, $form->{"listprice_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
push @{ $form->{TEMPLATE_ARRAYS}->{price_factor_name} }, $price_factor->{description};
push @{ $form->{TEMPLATE_ARRAYS}->{partsgroup} }, $form->{"partsgroup_$i"};
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}) . qq|, $ref->{partnumber}, $ref->{description}|);
+ CVar->get_non_editable_ic_cvars(form => $form,
+ dbh => $dbh,
+ row => $i,
+ sub_module => 'orderitems',
+ may_converted_from => ['orderitems', 'invoice']);
+
push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
for @{ $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 };