use SL::Common;
use SL::DBUtils;
use SL::DO;
+use SL::GenericTranslations;
use SL::MoreCommon;
+use SL::IC;
+use SL::IO;
use Data::Dumper;
+use strict;
+
sub invoice_details {
$main::lxdebug->enter_sub();
my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
($form->{terms}) = selectrow_query($form, $dbh, $query);
- my (@project_ids, %projectnumbers);
+ my (@project_ids, %projectnumbers, %projectdescriptions);
+ $form->{TEMPLATE_ARRAYS} = {};
push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
}
# sort items by partsgroup
- for $i (1 .. $form->{rowcount}) {
- $partsgroup = "";
- if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
- $partsgroup = $form->{"partsgroup_$i"};
- }
- push @partsgroup, [$i, $partsgroup];
+ for my $i (1 .. $form->{rowcount}) {
+# $partsgroup = "";
+# if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
+# $partsgroup = $form->{"partsgroup_$i"};
+# }
+# push @partsgroup, [$i, $partsgroup];
push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
}
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 = $dbh->prepare($query);
$sth->execute(@project_ids) ||
$form->dberror($query . " (" . join(", ", @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 $tax = 0;
my $item;
$form->{discount} = [];
+ IC->prepare_parts_for_printing();
+
+ my $ic_cvar_configs = CVar->get_configs(module => 'IC');
+
my @arrays =
qw(runningnumber number description longdescription qty ship unit bin
deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
partnotes serialnumber reqdate sellprice listprice netprice
discount p_discount discount_sub nodiscount_sub
- linetotal nodiscount_linetotal tax_rate projectnumber
- price_factor price_factor_name);
+ linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
+ price_factor price_factor_name partsgroup);
- my @tax_arrays =
- qw(taxbase tax taxdescription taxrate taxnumber);
+ push @arrays, map { "ic_cvar_$_->{name}" } @{ $ic_cvar_configs };
+
+ my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
+
+ my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);
+
+ map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];
if ($item->[1] ne $sameitem) {
- push(@{ $form->{description} }, qq|$item->[1]|);
+ push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
$sameitem = $item->[1];
- map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
+ map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };
- push @{ $form->{runningnumber} }, $position;
- push @{ $form->{number} }, $form->{"partnumber_$i"};
- push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"};
- push @{ $form->{bin} }, $form->{"bin_$i"};
- push @{ $form->{"partnotes"} }, $form->{"partnotes_$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->{deliverydate_oe} }, $form->{"deliverydate_$i"};
- push @{ $form->{sellprice} }, $form->{"sellprice_$i"};
- push @{ $form->{ordnumber_oe} }, $form->{"ordnumber_$i"};
- push @{ $form->{transdate_oe} }, $form->{"transdate_$i"};
- push @{ $form->{invnumber} }, $form->{"invnumber"};
- push @{ $form->{invdate} }, $form->{"invdate"};
- push @{ $form->{price_factor} }, $price_factor->{formatted_factor};
- push @{ $form->{price_factor_name} }, $price_factor->{description};
+ push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
+ push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$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}->{deliverydate_oe} }, $form->{"reqdate_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{sellprice} }, $form->{"sellprice_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
+ 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}->{reqdate} }, $form->{"reqdate_$i"};
if ($form->{lizenzen}) {
if ($form->{"licensenumber_$i"}) {
$query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|;
- ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
- push(@{ $form->{licensenumber} }, $licensenumber);
- push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
+ my ($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, $licensenumber);
+ push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
} else {
- push(@{ $form->{licensenumber} }, "");
- push(@{ $form->{validuntil} }, "");
+ push(@{ $form->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
+ push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
}
}
# listprice
- push(@{ $form->{listprice} }, $form->{"listprice_$i"});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});
my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
my ($dec) = ($sellprice =~ /\.(\d+)/);
my $decimalplaces = max 2, length($dec);
- my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces);
- my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2);
+ my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});
+ my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
+ my $linetotal = $form->round_amount($linetotal_exact, 2);
+ my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
+ $decimalplaces);
my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
$form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
- push @{ $form->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
+ push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
$linetotal = ($linetotal != 0) ? $linetotal : '';
- push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : '';
- push @{ $form->{p_discount} }, $form->{"discount_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
+ push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
$form->{total} += $linetotal;
$form->{nodiscount_total} += $nodiscount_linetotal;
}
if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
- push @{ $form->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
- push @{ $form->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
$discount_subtotal = 0;
$nodiscount_subtotal = 0;
$subtotal_header = 0;
} else {
- push @{ $form->{discount_sub} }, "";
- push @{ $form->{nodiscount_sub} }, "";
+ push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
}
if (!$form->{"discount_$i"}) {
$nodiscount += $linetotal;
}
- push @{ $form->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
- push @{ $form->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
- push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
@taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
$taxrate = 0;
if ($form->round_amount($taxrate, 7) == 0) {
if ($form->{taxincluded}) {
- foreach $item (@taxaccounts) {
- $taxamount =
- $form->round_amount($linetotal * $form->{"${item}_rate"} /
- (1 + abs($form->{"${item}_rate"})),
- 2);
+ foreach my $accno (@taxaccounts) {
+ $taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);
- $taxaccounts{$item} += $taxamount;
- $taxdiff += $taxamount;
+ $taxaccounts{$accno} += $taxamount;
+ $taxdiff += $taxamount;
- $taxbase{$item} += $taxbase;
+ $taxbase{$accno} += $taxbase;
}
$taxaccounts{ $taxaccounts[0] } += $taxdiff;
} else {
- foreach $item (@taxaccounts) {
- $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
- $taxbase{$item} += $taxbase;
+ foreach my $accno (@taxaccounts) {
+ $taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
+ $taxbase{$accno} += $taxbase;
}
}
} else {
- foreach $item (@taxaccounts) {
- $taxaccounts{$item} +=
- $taxamount * $form->{"${item}_rate"} / $taxrate;
- $taxbase{$item} += $taxbase;
+ foreach my $accno (@taxaccounts) {
+ $taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
+ $taxbase{$accno} += $taxbase;
}
}
- $tax_rate = $taxrate * 100;
- push(@{ $form->{tax_rate} }, qq|$tax_rate|);
+ my $tax_rate = $taxrate * 100;
+ push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
if ($form->{"assembly_$i"}) {
$sameitem = "";
WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->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);
}
map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);
- push(@{ $form->{description} },
+ push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
$form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
)
. qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
- map({ push(@{ $form->{$_} }, "") } grep({ $_ ne "description" } @arrays));
+ map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}
$sth->finish;
}
+
+ map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
}
}
foreach my $item (sort keys %taxaccounts) {
- push(@{ $form->{taxbase} },
- $form->format_amount($myconfig, $taxbase{$item}, 2));
-
$tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
- push(@{ $form->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
- push(@{ $form->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
- push(@{ $form->{taxrate} },
- $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
- push(@{ $form->{taxnumber} }, $form->{"${item}_taxnumber"});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $form->{"${item}_description"} . q{ } . 100 * $form->{"${item}_rate"} . q{%});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
}
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"}) {
- push(@{ $form->{payment} }, $form->{"paid_$i"});
my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
- push(@{ $form->{paymentaccount} }, $description);
- push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
- push(@{ $form->{paymentsource} }, $form->{"source_$i"});
+
+ push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
+ push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
+ push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});
$form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
}
$main::lxdebug->enter_sub();
my ($self, $dbh, $id) = @_;
+ my $form = \%main::form;
my $query = qq|SELECT description FROM project WHERE id = ?|;
my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));
# connect to database
my $dbh = $form->dbconnect($myconfig);
+ my $language_id = $form->{language_id};
+
# get contact id, set it if nessessary
$form->{cp_id} *= 1;
'trans_id' => $form->{customer_id});
map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
+ $form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
+ 'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
+ 'language_id' => $language_id,
+ 'allow_fallback' => 1);
+
+
$dbh->disconnect;
$main::lxdebug->leave_sub();
my ($query, $sth, $null, $project_id, @values);
my $exchangerate = 0;
+ my $ic_cvar_configs = CVar->get_configs(module => 'IC',
+ dbh => $dbh);
+
if (!$form->{employee_id}) {
$form->get_employee($dbh);
}
-
+
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
($null, $form->{department_id}) = split(/--/, $form->{department});
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
my $price_factor;
+ $form->{amount} = {};
+ $form->{amount_cogs} = {};
+
foreach my $i (1 .. $form->{rowcount}) {
if ($form->{type} eq "credit_note") {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
}
my $basefactor;
- my $basqty;
+ my $baseqty;
$form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$i"}) * 1;
- $form->{"marge_total_$i"} = $form->parse_amount($myconfig, $form->{"marge_total_$i"}) * 1;
- $form->{"lastcost_$i"} = $form->{"lastcost_$i"} * 1;
+ $form->{"marge_absolut_$i"} = $form->parse_amount($myconfig, $form->{"marge_absolut_$i"}) * 1;
+ $form->{"lastcost_$i"} = $form->parse_amount($myconfig, $form->{"lastcost_$i"}) * 1;
if ($form->{storno}) {
$form->{"qty_$i"} *= -1;
($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
$pricegroup_id *= 1;
+ my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
+
# save detail record in invoice table
$query =
- qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty,
+ qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty,
sellprice, fxsellprice, discount, allocated, assemblyitem,
unit, deliverydate, project_id, serialnumber, pricegroup_id,
ordnumber, transdate, cusordnumber, base_qty, subtotal,
marge_percent, marge_total, lastcost,
price_factor_id, price_factor, marge_price_factor)
- VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
(SELECT factor FROM price_factors WHERE id = ?), ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
+ @values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
$form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated, 'f',
$form->{"serialnumber_$i"}, conv_i($pricegroup_id),
$form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
$form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
- $form->{"marge_percent_$i"}, $form->{"marge_total_$i"},
+ $form->{"marge_percent_$i"}, $form->{"marge_absolut_$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"}));
@values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
do_query($form, $dbh, $query, @values);
}
+
+ CVar->save_custom_variables(module => 'IC',
+ sub_module => 'invoice',
+ trans_id => $invoice_id,
+ configs => $ic_cvar_configs,
+ variables => $form,
+ name_prefix => 'ic_',
+ name_postfix => "_$i",
+ dbh => $dbh);
}
}
- $form->{datepaid} = $form->{invdate};
-
# total payments, don't move we need it here
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{type} eq "credit_note") {
$project_id = conv_i($form->{"globalproject_id"});
+ foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
+ foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
+ next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
+
+ $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
+
+ if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
+ $query =
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
+ @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
+ do_query($form, $dbh, $query, @values);
+ $form->{amount_cogs}{$trans_id}{$accno} = 0;
+ }
+ }
+
+ foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
+ $form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);
+
+ if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$accno} != 0)) {
+ $query =
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, 0, ?)|;
+ @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id));
+ do_query($form, $dbh, $query, @values);
+ }
+ }
+ }
+
foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
next unless ($form->{expense_inventory} =~ /\Q$accno\E/);
$form->{marge_total} *= -1;
}
+ IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
+
if ($payments_only) {
- $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
+ $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
if (!$provided_dbh) {
$dbh->commit();
$amount = $netamount + $tax;
# save AR record
+ #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
+
$query = qq|UPDATE ar set
invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
- amount = ?, netamount = ?, paid = ?, datepaid = ?,
+ amount = ?, netamount = ?, paid = ?,
duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
shipvia = ?, terms = ?, notes = ?, intnotes = ?,
curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
type = ?, language_id = ?, taxzone_id = ?, shipto_id = ?,
employee_id = ?, salesman_id = ?, storno_id = ?, storno = ?,
- cp_id = ?, marge_total = ?, marge_percent = ?,
+ cp_id = ?, marge_total = ?, marge_percent = ?,
globalproject_id = ?, delivery_customer_id = ?,
- transaction_description = ?, delivery_vendor_id = ?
+ transaction_description = ?, delivery_vendor_id = ?,
+ donumber = ?
WHERE id = ?|;
@values = ( $form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
- conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
- $amount, $netamount, $form->{"paid"}, conv_date($form->{"datepaid"}),
+ conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}), conv_i($form->{"customer_id"}),
+ $amount, $netamount, $form->{"paid"},
conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}), '1', $form->{"shippingpoint"},
$form->{"shipvia"}, conv_i($form->{"terms"}), $form->{"notes"}, $form->{"intnotes"},
$form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}), $form->{"taxincluded"} ? 't' : 'f',
$form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
- conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
+ conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? 't' : 'f',
conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
- conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
+ conv_i($form->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
$form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
+ $form->{"donumber"}, #das entsprechende feld lieferscheinnummer aus der html-form 12.02.09 jb
conv_i($form->{"id"}));
do_query($form, $dbh, $query, @values);
-
- if($form->{"formname"} eq "credit_note") {
- for my $i (1 .. $form->{rowcount}) {
- $query = qq|UPDATE parts SET onhand = onhand - ? WHERE id = ?|;
- @values = (conv_i($form->{"qty_$i"}), conv_i($form->{"id_$i"}));
- do_query($form, $dbh, $query, @values);
- }
- }
-
+
+
if ($form->{storno}) {
$query =
qq!UPDATE ar SET
my ($self, $form, $dbh) = @_;
- my @delete_oids;
+ my @delete_acc_trans_ids;
# Delete old payment entries from acc_trans.
my $query =
- qq|SELECT oid
+ qq|SELECT acc_trans_id
FROM acc_trans
WHERE (trans_id = ?) AND fx_transaction
UNION
- SELECT at.oid
+ SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
- push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));
$query =
- qq|SELECT at.oid
+ qq|SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
- ORDER BY at.oid
+ ORDER BY at.acc_trans_id
OFFSET 1|;
- push @delete_oids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
+ push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));
- if (@delete_oids) {
- $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|;
+ if (@delete_acc_trans_ids) {
+ $query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
do_query($form, $dbh, $query);
}
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
- ORDER BY at.oid
+ ORDER BY at.acc_trans_id
LIMIT 1|;
($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
WHERE (a.id = ?)|;
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
my $allocated = 0;
$query =
qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
- @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
+ my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
do_query($form, $dbh, $query, @values);
}
$main::lxdebug->enter_sub();
my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
+
+ $basefactor ||= 1;
+
$form->{taxzone_id} *=1;
my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
my $taxzone_id = $form->{"taxzone_id"} * 1;
my $allocated = 0;
my $qty;
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
$qty = $totalqty;
}
# total expenses and inventory
# sellprice is the cost of the item
- $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / $basefactor, 2);
+ my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
if (!$main::eur) {
$ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
# add to expense
- $form->{amount}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
+ $form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
$form->{expense_inventory} .= " " . $ref->{expense_accno};
$ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
# deduct inventory
- $form->{amount}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
+ $form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
$form->{expense_inventory} .= " " . $ref->{inventory_accno};
}
WHERE i.trans_id = ?|;
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
if ($ref->{inventory_accno_id}) {
# de-allocated purchases
ORDER BY i.trans_id DESC|;
my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
- while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
- $qty = $ref->{qty};
+ while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
+ my $qty = $ref->{qty};
if (($ref->{qty} - $inhref->{allocated}) > 0) {
$qty = $inhref->{allocated};
}
$sth->finish;
# delete acc_trans
- @values = (conv_i($form->{id}));
+ my @values = (conv_i($form->{id}));
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values);
$dbh->disconnect;
if ($rc) {
- map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
+ map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
}
$main::lxdebug->leave_sub();
my $id = conv_i($form->{id});
# retrieve invoice
+ #erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb
+
$query =
qq|SELECT
a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
a.transaction_description,
a.marge_total, a.marge_percent,
- e.name AS employee
+ e.name AS employee, a.donumber
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = ?|;
$query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
$sth = prepare_execute_query($form, $dbh, $query, $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};
my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
: $form->{invdate} ? $dbh->quote($form->{invdate})
: "current_date";
-
+
my $taxzone_id = $form->{taxzone_id} *= 1;
$taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid,
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from AS expense_valid,
+ i.id AS invoice_id,
i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.discount, i.parts_id AS id, i.unit, i.deliverydate AS reqdate,
i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
i.price_factor_id, i.price_factor, i.marge_price_factor,
$sth = prepare_execute_query($form, $dbh, $query, $id);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
+ # Retrieve custom variables.
+ my $cvars = CVar->get_custom_variables(dbh => $dbh,
+ module => 'IC',
+ sub_module => 'invoice',
+ trans_id => $ref->{invoice_id},
+ );
+ map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
+ delete $ref->{invoice_id};
+
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
delete($ref->{"part_inventory_accno_id"});
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE t.id IN
(SELECT tk.tax_id FROM taxkeys tk
- WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
+ WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
AND startdate <= date($transdate)
ORDER BY startdate DESC LIMIT 1)
ORDER BY c.accno|;
my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
$ref->{taxaccounts} = "";
my $i=0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+ while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
# get customer
$query =
qq|SELECT
- c.name AS customer, c.discount, c.creditlimit, c.terms,
+ c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit, c.terms,
c.email, c.cc, c.bcc, c.language_id, c.payment_id,
c.street, c.zipcode, c.city, c.country,
c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
WHERE c.id = ?|;
push @values, $cid;
$ref = selectfirst_hashref_query($form, $dbh, $query, @values);
+
+ delete $ref->{salesman_id} if !$ref->{salesman_id};
+
map { $form->{$_} = $ref->{$_} } keys %$ref;
$query =
WHERE o.customer_id = ?
AND o.quotation = '0'
AND o.closed = '0'|;
- $sth = prepare_execute_query($form, $dbh, $query, $cid);
+ my $sth = prepare_execute_query($form, $dbh, $query, $cid);
while (my ($amount, $exch) = $sth->fetchrow_array) {
$exch = 1 unless $exch;
$sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
my $i = 0;
- while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
if ($ref->{category} eq 'I') {
$i++;
$form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
my $where = qq|NOT p.obsolete = '1'|;
my @values;
- foreach my $column (qw(p.partnumber p.description pgpartsgroup)) {
+ foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
my ($table, $field) = split m/\./, $column;
next if !$form->{"${field}_${i}"};
$where .= qq| AND lower(${column}) ILIKE ?|;
push @values, '%' . $form->{"${field}_${i}"} . '%';
}
+ #Es soll auch nach EAN gesucht werden, ohne Einschränkung durch Beschreibung
+ if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
+ $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
+ push @values, $form->{"partnumber_$i"};
+ }
+
if ($form->{"description_$i"}) {
$where .= qq| ORDER BY p.description|;
} else {
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+ while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
# In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
# es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
}
# get tax rates and description
- $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+ my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query =
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
FROM tax t
LIMIT 1)
ORDER BY c.accno|;
@values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
- $stw = $dbh->prepare($query);
+ my $stw = $dbh->prepare($query);
$stw->execute(@values) || $form->dberror($query);
$ref->{taxaccounts} = "";
my $i = 0;
- while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+ while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
- # if ($customertax{$ref->{accno}}) {
+ # if ($customertax{$ref->{accno}})
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
FROM license l
WHERE l.parts_id = ? AND NOT l.id IN (SELECT li.license_id FROM licenseinvoice li)|;
my $stw = prepare_execute_query($form, $dbh, $query, conv_i($ref->{id}));
- while (my $ptr = $stw->fetchrow_hashref(NAME_lc)) {
+ while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
}
$stw->finish;
}
}
$sth->finish;
+
+ foreach my $item (@{ $form->{item_list} }) {
+ my $custom_variables = CVar->get_custom_variables(module => 'IC',
+ trans_id => $item->{id},
+ dbh => $dbh,
+ );
+
+ map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
+ }
+
$dbh->disconnect;
$main::lxdebug->leave_sub();
$id = $form->{"id_$i"};
if (!($form->{"id_$i"}) and $form->{"new_id_$i"}) {
-
$id = $form->{"new_id_$i"};
}
- ($price, $selectedpricegroup_id) = split(/--/,
- $form->{"sellprice_pg_$i"});
+ my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
- $pricegroup_old = $form->{"pricegroup_old_$i"};
+ my $pricegroup_old = $form->{"pricegroup_old_$i"};
$form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
$form->{"old_pricegroup_$i"} = $pricegroup_old;
- $price_new = $form->{"price_new_$i"};
- $price_old = $form->{"price_old_$i"};
+ my $price_new = $form->{"price_new_$i"};
+ my $price_old = $form->{"price_old_$i"};
if (!$form->{"unit_old_$i"}) {
# Neue Ware aus der Datenbank. In diesem Fall ist unit_$i die
$form->{"basefactor_$i"} = 1;
}
- $query =
+ my $query =
qq|SELECT
pricegroup_id,
(SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
FROM prices
ORDER BY pricegroup|;
- @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
+ my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
my $pkq = prepare_execute_query($form, $dbh, $query, @values);
- while ($pkr = $pkq->fetchrow_hashref(NAME_lc)) {
+ while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
$pkr->{id} = $id;
$pkr->{selected} = '';
# if there is an exchange rate change price
if (($form->{exchangerate} * 1) != 0) {
-
$pkr->{price} /= $form->{exchangerate};
}
$pkr->{price} *= $form->{"basefactor_$i"};
-
$pkr->{price} *= $basefactor;
-
$pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
if ($selectedpricegroup_id eq undef) {
} else {
- $form->{"sellprice_$i"} = $pkr->{price};
+# this sub should not set anything and only return. --sschoeling, 20090506
+# $form->{"sellprice_$i"} = $pkr->{price};
}
} elsif ($pkr->{price} == $pkr->{default_sellprice}) {