use SL::ARAP;
use SL::CVar;
use SL::Common;
+use SL::DATEV qw(:CONSTANTS);
use SL::DBUtils;
use SL::DO;
use SL::GenericTranslations;
+use SL::HTML::Restrict;
use SL::MoreCommon;
use SL::IC;
use SL::IO;
use SL::TransNumber;
+use SL::DB::Default;
+use SL::DB::Tax;
+use SL::TransNumber;
use Data::Dumper;
use strict;
$form->{duedate} ||= $form->{invdate};
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $sth;
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, %projectdescriptions);
+ my (@project_ids);
$form->{TEMPLATE_ARRAYS} = {};
push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});
push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
}
+ my $projects = [];
+ my %projects_by_id;
if (@project_ids) {
- $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();
+ $projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
+ %projects_by_id = map { $_->id => $_ } @$projects;
}
- $form->{"globalprojectnumber"} =
- $projectnumbers{$form->{"globalproject_id"}};
- $form->{"globalprojectdescription"} =
- $projectdescriptions{$form->{"globalproject_id"}};
+ if ($projects_by_id{$form->{"globalproject_id"}}) {
+ $form->{globalprojectnumber} = $projects_by_id{$form->{"globalproject_id"}}->projectnumber;
+ $form->{globalprojectdescription} = $projects_by_id{$form->{"globalproject_id"}}->description;
+
+ for (@{ $projects_by_id{$form->{"globalproject_id"}}->cvars_by_config }) {
+ $form->{"project_cvar_" . $_->config->name} = $_->value_as_text;
+ }
+ }
my $tax = 0;
my $item;
my $i;
my @partsgroup = ();
my $partsgroup;
- my %oid = ('Pg' => 'oid',
- 'Oracle' => 'rowid');
# sort items by partsgroup
for $i (1 .. $form->{rowcount}) {
$form->{discount} = [];
- IC->prepare_parts_for_printing();
+ IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
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
- deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
+ deliverydate_oe ordnumber_oe donumber_do transdate_oe validuntil
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);
+ price_factor price_factor_name partsgroup weight lineweight);
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);
map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
+ my $totalweight = 0;
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];
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}->{deliverydate_oe} }, $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}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$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_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 = ?|;
- 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->{TEMPLATE_ARRAYS}->{licensenumber} }, "");
- push(@{ $form->{TEMPLATE_ARRAYS}->{validuntil} }, "");
- }
- }
-
- # listprice
- push(@{ $form->{TEMPLATE_ARRAYS}->{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 $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);
+ 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 $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
+ my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);
+
+ my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
+
+ my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
+
$form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
- push @{ $form->{TEMPLATE_ARRAYS}->{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) : '';
+ push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';
$linetotal = ($linetotal != 0) ? $linetotal : '';
- push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
- push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
+ push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
+ 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->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
- push @{ $form->{TEMPLATE_ARRAYS}->{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}->{discount_sub_nofmt} }, $discount_subtotal;
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;
$discount_subtotal = 0;
$nodiscount_subtotal = 0;
$subtotal_header = 0;
} else {
- push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, "";
- push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
+ push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
}
if (!$form->{"discount_$i"}) {
$nodiscount += $linetotal;
}
- 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->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
+ push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
+
+ my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
- push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
- push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
+ push @{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $project->projectnumber;
+ push @{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $project->description;
+
+ my $lineweight = $form->{"qty_$i"} * $form->{"weight_$i"};
+ $totalweight += $lineweight;
+ push @{ $form->{TEMPLATE_ARRAYS}->{weight} }, $form->format_amount($myconfig, $form->{"weight_$i"}, 3);
+ push @{ $form->{TEMPLATE_ARRAYS}->{weight_nofmt} }, $form->{"weight_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{lineweight} }, $form->format_amount($myconfig, $lineweight, 3);
+ push @{ $form->{TEMPLATE_ARRAYS}->{lineweight_nofmt} }, $lineweight;
@taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
$taxrate = 0;
my $sortorder = "";
if ($form->{groupitems}) {
$sortorder =
- qq|ORDER BY pg.partsgroup, a.$oid{$myconfig->{dbdriver}}|;
+ qq|ORDER BY pg.partsgroup, a.oid|;
} else {
- $sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
+ $sortorder = qq|ORDER BY a.oid|;
}
$query =
$sth->finish;
}
- 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 };
+
+ push @{ $form->{TEMPLATE_ARRAYS}->{"project_cvar_" . $_->config->name} }, $_->value_as_text for @{ $project->cvars_by_config };
}
}
+ $form->{totalweight} = $form->format_amount($myconfig, $totalweight, 3);
+ $form->{totalweight_nofmt} = $totalweight;
+ my $defaults = AM->get_defaults();
+ $form->{weightunit} = $defaults->{weightunit};
+
foreach my $item (sort keys %taxaccounts) {
$tax += $taxamount = $form->round_amount($taxaccounts{$item}, 2);
push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 2));
+ push(@{ $form->{TEMPLATE_ARRAYS}->{tax_nofmt} }, $taxamount );
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}->{taxrate_nofmt} }, $form->{"${item}_rate"} * 100);
push(@{ $form->{TEMPLATE_ARRAYS}->{taxnumber} }, $form->{"${item}_taxnumber"});
+
+ my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
+ my $description = $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) if $tax_obj;
+ push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
}
for my $i (1 .. $form->{paidaccounts}) {
}
}
if($form->{taxincluded}) {
- $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
+ $form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
+ $form->{subtotal_nofmt} = $form->{total} - $tax;
}
else {
- $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
+ $form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
+ $form->{subtotal_nofmt} = $form->{total};
}
$form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
$form->set_payment_options($myconfig, $form->{invdate});
- $form->{username} = $myconfig->{name};
+ $form->{delivery_term} = SL::DB::Manager::DeliveryTerm->find_by(id => $form->{delivery_term_id} || undef);
+ $form->{delivery_term}->description_long($form->{delivery_term}->translated_attribute('description_long', $form->{language_id})) if $form->{delivery_term} && $form->{language_id};
- $dbh->disconnect;
+ $form->{username} = $myconfig->{name};
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form, @wanted_vars) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $language_id = $form->{language_id};
# get rest for the customer
my $query =
qq|SELECT ct.*, cp.*, ct.notes as customernotes,
- ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail
+ ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
+ cu.name AS currency
FROM customer ct
LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
+ LEFT JOIN currencies cu ON (ct.currency_id = cu.id)
WHERE (ct.id = ?) $where
ORDER BY cp.cp_id
LIMIT 1|;
'allow_fallback' => 1);
- $dbh->disconnect;
-
$main::lxdebug->leave_sub();
}
# connect to database, turn off autocommit
my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
+ my $restricter = SL::HTML::Restrict->create;
my ($query, $sth, $null, $project_id, @values);
my $exchangerate = 0;
}
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
+ my $defaultcurrency = $form->{defaultcurrency};
- ($null, $form->{department_id}) = split(/--/, $form->{department});
+ # Seit neuestem wird die department_id schon übergeben UND $form->department nicht mehr
+ # korrekt zusammengebaut. Sehr wahrscheinlich beim Umstieg auf T8 kaputt gegangen
+ # Ich lass den Code von 2005 erstmal noch stehen ;-) jb 03-2011
+ if (!$form->{department_id}){
+ ($null, $form->{department_id}) = split(/--/, $form->{department});
+ }
my $all_units = AM->retrieve_units($myconfig, $form);
$query = qq|SELECT nextval('glid')|;
($form->{"id"}) = selectrow_query($form, $dbh, $query);
- $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|;
- do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
+ $query = qq|INSERT INTO ar (id, invnumber, currency_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?))|;
+ do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency});
if (!$form->{invnumber}) {
- $form->{invnumber} =
- $form->update_defaults($myconfig, $form->{type} eq "credit_note" ?
- "cnnumber" : "invnumber", $dbh);
+ my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, id => $form->{id});
+ $form->{invnumber} = $trans_number->create_unique;
}
}
}
my ($netamount, $invoicediff) = (0, 0);
my ($amount, $linetotal, $lastincomeaccno);
- my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|);
- my $defaultcurrency = (split m/:/, $currencies)[0];
-
if ($form->{currency} eq $defaultcurrency) {
$form->{exchangerate} = 1;
} else {
if ($form->{"assembly_$i"}) {
# record assembly item as allocated
- &process_assembly($dbh, $form, $form->{"id_$i"}, $baseqty);
+ &process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);
} else {
- $allocated = &cogs($dbh, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
+ $allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
}
}
- # get pricegroup_id and save it
+ # 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().
($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
$pricegroup_id *= 1;
+ $pricegroup_id = undef if !$pricegroup_id;
my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
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,
+ ordnumber, donumber, 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 = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
- $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
+ $form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated, 'f',
$form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
- $form->{"serialnumber_$i"}, conv_i($pricegroup_id),
- $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
+ $form->{"serialnumber_$i"}, $pricegroup_id,
+ $form->{"ordnumber_$i"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
$form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
$form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
$form->{"lastcost_$i"},
conv_i($form->{"marge_price_factor_$i"}));
do_query($form, $dbh, $query, @values);
- if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
- $query =
- qq|INSERT INTO licenseinvoice (trans_id, license_id)
- VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|;
- @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,
}
$project_id = conv_i($form->{"globalproject_id"});
+ # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
+ my $taxdate = $form->{deliverydate} ? $form->{deliverydate} : $form->{invdate};
foreach my $trans_id (keys %{ $form->{amount_cogs} }) {
foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
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));
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
$form->{amount_cogs}{$trans_id}{$accno} = 0;
}
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));
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, (SELECT id FROM tax WHERE taxkey=0), 0, ?, (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($trans_id), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
- @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
$form->{amount}{$trans_id}{$accno} = 0;
}
if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
- @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
if (!$form->{storno}) {
for my $i (1 .. $form->{paidaccounts}) {
+ if ($form->{"acc_trans_id_$i"}
+ && $payments_only
+ && (SL::DB::Default->get->payments_changeable == 0)) {
+ next;
+ }
+
next if ($form->{"paid_$i"} == 0);
my ($accno) = split(/--/, $form->{"AR_paid_$i"});
if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id)
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
- @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
do_query($form, $dbh, $query, @values);
}
# record payment
$form->{"paid_$i"} *= -1;
+ my $gldate = (conv_date($form->{"gldate_$i"}))? conv_date($form->{"gldate_$i"}) : conv_date($form->current_date($myconfig));
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?,
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
- $form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
+ $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
do_query($form, $dbh, $query, @values);
# exchangerate difference
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
- $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
+ $form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
# gain/loss
$amount =
- $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
- $form->{"exchangerate_$i"};
+ $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
+ $form->{"exchangerate_$i"};
if ($amount > 0) {
- $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
+ $form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
} else {
- $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
- $amount;
+ $form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
}
$diff = 0;
IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
- if ($payments_only) {
- $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
- do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
-
- $dbh->commit if !$provided_dbh;
-
- $main::lxdebug->leave_sub();
- return;
- }
-
# record exchange rate differences and gains/losses
foreach my $accno (keys %{ $form->{fx} }) {
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
- if (
- ($form->{fx}{$accno}{$transdate} =
- $form->round_amount($form->{fx}{$accno}{$transdate}, 2)
- ) != 0
- ) {
+ $form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
+ if ( $form->{fx}{$accno}{$transdate} != 0 ) {
$query =
- qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id)
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
- (SELECT taxkey_id FROM chart WHERE accno = ?), ?)|;
- @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
+ (SELECT tax_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ (SELECT taxkey_id
+ FROM taxkeys
+ WHERE chart_id= (SELECT id
+ FROM chart
+ WHERE accno = ?)
+ AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1),
+ ?,
+ (SELECT link FROM chart WHERE accno = ?))|;
+ @values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
}
+ if ($payments_only) {
+ $query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
+
+ $dbh->commit if !$provided_dbh;
+
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
$amount = $netamount + $tax;
# save AR record
amount = ?, netamount = ?, paid = ?,
duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
shipvia = ?, terms = ?, notes = ?, intnotes = ?,
- curr = ?, department_id = ?, payment_id = ?, taxincluded = ?,
+ currency_id = (SELECT id FROM currencies WHERE name = ?),
+ 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 = ?,
globalproject_id = ?, delivery_customer_id = ?,
transaction_description = ?, delivery_vendor_id = ?,
- donumber = ?
+ donumber = ?, invnumber_for_credit_note = ?, direct_debit = ?,
+ delivery_term_id = ?
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"}),
conv_i($form->{"cp_id"}), 1 * $form->{marge_total} , 1 * $form->{marge_percent},
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
+ $form->{"donumber"}, $form->{"invnumber_for_credit_note"}, $form->{direct_debit} ? 't' : 'f',
+ conv_i($form->{delivery_term_id}),
conv_i($form->{"id"}));
do_query($form, $dbh, $query, @values);
'arap_id' => $form->{id},
'table' => 'ar',);
+ # safety check datev export
+ if ($::instance_conf->get_datev_check_on_sales_invoice) {
+ my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
+ $transdate ||= DateTime->today;
+
+ my $datev = SL::DATEV->new(
+ exporttype => DATEV_ET_BUCHUNGEN,
+ format => DATEV_FORMAT_KNE,
+ dbh => $dbh,
+ from => $transdate,
+ to => $transdate,
+ trans_id => $form->{id},
+ );
+
+ $datev->export;
+
+ if ($datev->errors) {
+ $dbh->rollback;
+ die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
+ }
+ }
+
my $rc = 1;
$dbh->commit if !$provided_dbh;
my ($self, $myconfig, $form, $locale) = @_;
# connect to database, turn off autocommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->get_standard_dbh;
my (%payments, $old_form, $row, $item, $query, %keep_vars);
$old_form = save_form();
# Delete all entries in acc_trans from prior payments.
- $self->_delete_payments($form, $dbh);
+ if (SL::DB::Default->get->payments_changeable != 0) {
+ $self->_delete_payments($form, $dbh);
+ }
# Save the new payments the user made before cleaning up $form.
- map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
+ map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^gldate_\d+$|^acc_trans_id_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form };
# Clean up $form so that old content won't tamper the results.
%keep_vars = map { $_, 1 } qw(login password id);
restore_form($old_form);
my $rc = $dbh->commit();
- $dbh->disconnect();
$main::lxdebug->leave_sub();
sub process_assembly {
$main::lxdebug->enter_sub();
- my ($dbh, $form, $id, $totalqty) = @_;
+ my ($dbh, $myconfig, $form, $id, $totalqty) = @_;
my $query =
qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
$ref->{qty} *= $totalqty;
if ($ref->{assembly}) {
- &process_assembly($dbh, $form, $ref->{parts_id}, $ref->{qty});
+ &process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
next;
} else {
if ($ref->{inventory_accno_id}) {
- $allocated = &cogs($dbh, $form, $ref->{parts_id}, $ref->{qty});
+ $allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
}
}
sub cogs {
$main::lxdebug->enter_sub();
- my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_;
+ # adjust allocated in table invoice according to FIFO princicple
+ # for a certain part with part_id $id
+
+ my ($dbh, $myconfig, $form, $id, $totalqty, $basefactor, $row) = @_;
$basefactor ||= 1;
my $allocated = 0;
my $qty;
+# all invoice entries of an example part:
+
+# id | trans_id | base_qty | allocated | sellprice | inventory_accno | income_accno | expense_accno
+# ---+----------+----------+-----------+-----------+-----------------+--------------+---------------
+# 4 | 4 | -5 | 5 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
+# 5 | 5 | 4 | -4 | 50.00000 | 1140 | 4400 | 5400 sold 4 for 50
+# 6 | 6 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
+# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
+# 8 | 8 | 1 | -1 | 50.00000 | 1140 | 4400 | 5400 sold 1 for 50
+
+# AND ((i.base_qty + i.allocated) < 0) filters out all but line with id=7, elsewhere i.base_qty + i.allocated has already reached 0
+# and all parts have been allocated
+
+# so transaction 8 only sees transaction 7 with unallocated parts and adjusts allocated for that transaction, before allocated was 0
+# 7 | 7 | -5 | 1 | 20.00000 | 1140 | 4400 | 5400 bought 5 for 20
+
+# in this example there are still 4 unsold articles
+
+
+ # search all invoice entries for the part in question, adjusting "allocated"
+ # until the total number of sold parts has been reached
+
+ # ORDER BY trans_id ensures FIFO
+
+
while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
if (($qty = (($ref->{base_qty} * -1) - $ref->{allocated})) > $totalqty) {
$qty = $totalqty;
}
+ # update allocated in invoice
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
# total expenses and inventory
# sellprice is the cost of the item
my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
- if (!$::lx_office_conf{system}->{eur}) {
+ if ( $::instance_conf->get_inventory_system eq 'perpetual' ) {
+ # Bestandsmethode: when selling parts, deduct their purchase value from the inventory account
$ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
# add to expense
$form->{amount_cogs}{ $form->{id} }{ $ref->{expense_accno} } += -$linetotal;
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);
-
- if ($form->{lizenzen}) {
- $query =
- qq|DELETE FROM licenseinvoice
- WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|;
- do_query($form, $dbh, $query, @values);
- }
-
do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
$main::lxdebug->leave_sub();
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->get_standard_dbh;
&reverse_invoice($dbh, $form);
do_query($form, $dbh, qq|UPDATE ar SET storno = 'f', paid = 0 WHERE id = ?|, $invoice_id);
}
- # delete AR record
- do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values);
-
# delete spool files
my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values);
- # delete status entries
- do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values);
+ my @queries = (
+ qq|DELETE FROM status WHERE trans_id = ?|,
+ qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
+ qq|DELETE FROM ar WHERE id = ?|,
+ );
+
+ map { do_query($form, $dbh, $_, @values) } @queries;
my $rc = $dbh->commit;
- $dbh->disconnect;
if ($rc) {
my $spool = $::lx_office_conf{paths}->{spool};
my ($sth, $ref, $query);
- my $query_transdate = ", current_date AS invdate" if !$form->{id};
+ my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';
$query =
qq|SELECT
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno,
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno,
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno,
- (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
- d.curr AS currencies
+ (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno
${query_transdate}
FROM defaults d|;
a.orddate, a.quodate, a.globalproject_id,
a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id,
- a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id,
+ a.duedate, a.taxincluded, (SELECT cu.name FROM currencies cu WHERE cu.id=a.currency_id) AS currency, a.shipto_id, a.cp_id,
a.employee_id, a.salesman_id, a.payment_id,
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, a.donumber
+ a.transaction_description, a.donumber, a.invnumber_for_credit_note,
+ a.marge_total, a.marge_percent, a.direct_debit, a.delivery_term_id,
+ e.name AS employee
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $id);
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
-
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
# get shipto
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.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, i.ordnumber, i.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
i.price_factor_id, i.price_factor, i.marge_price_factor,
- p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel,
+ p.partnumber, p.assembly, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, p.formel, p.listprice,
pr.projectnumber, pg.partsgroup, prg.pricegroup
FROM invoice i
}
- if ($form->{lizenzen}) {
- $query = qq|SELECT l.licensenumber, l.id AS licenseid FROM license l, licenseinvoice li WHERE l.id = li.license_id AND li.trans_id = ?|;
- my ($licensenumber, $licenseid) = selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
- $ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
- }
-
$ref->{qty} *= -1 if $form->{type} eq "credit_note";
chop $ref->{taxaccounts};
$query =
qq|SELECT
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.email, c.cc, c.bcc, c.language_id, c.payment_id, c.delivery_term_id,
c.street, c.zipcode, c.city, c.country,
- c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id,
+ c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, cu.name AS curr,
+ c.taxincluded_checked, c.direct_debit,
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
b.discount AS tradediscount, b.description AS business
FROM customer c
LEFT JOIN business b ON (b.id = c.business_id)
LEFT JOIN payment_terms pt ON ($payment_id (c.payment_id = pt.id))
+ LEFT JOIN currencies cu ON (c.currency_id=cu.id)
WHERE c.id = ?|;
push @values, $cid;
$ref = selectfirst_hashref_query($form, $dbh, $query, @values);
map { $form->{$_} = $ref->{$_} } keys %$ref;
+ # use customer currency
+ $form->{currency} = $form->{curr};
+
$query =
qq|SELECT sum(amount - paid) AS dunning_amount
FROM ar
$query =
qq|SELECT o.amount,
(SELECT e.buy FROM exchangerate e
- WHERE e.curr = o.curr
+ WHERE e.currency_id = o.currency_id
AND e.transdate = o.transdate)
FROM oe o
WHERE o.customer_id = ?
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $i = $form->{rowcount};
push @values, $form->{"partnumber_$i"};
}
+ # Search for part ID overrides all other criteria.
+ if ($form->{"id_${i}"}) {
+ $where = qq|p.id = ?|;
+ @values = ($form->{"id_${i}"});
+ }
+
if ($form->{"description_$i"}) {
$where .= qq| ORDER BY p.description|;
} else {
c3.new_chart_id AS expense_new_chart,
date($transdate) - c3.valid_from AS expense_valid,
- p.unit, p.assembly, p.bin, p.onhand,
+ p.unit, p.assembly, p.onhand,
p.notes AS partnotes, p.notes AS longdescription,
p.not_discountable, p.formel, p.payment_id AS part_payment_id,
- p.price_factor_id,
+ p.price_factor_id, p.weight,
pfac.factor AS price_factor,
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);
+ my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id = ? AND tr.parts_id = ?| ],
+ [ qq|SELECT tr.translation, tr.longdescription
+ FROM translation tr
+ WHERE tr.language_id IN
+ (SELECT id
+ FROM language
+ WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
+ AND tr.parts_id = ?
+ LIMIT 1| ] );
+ map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
+
while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
# In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
my $i = 0;
while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {
- # if ($customertax{$ref->{accno}})
if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
$stw->finish;
chop $ref->{taxaccounts};
+
if ($form->{language_id}) {
- $query =
- qq|SELECT tr.translation, tr.longdescription
- FROM translation tr
- WHERE tr.language_id = ? AND tr.parts_id = ?|;
- @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
- my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
- if ($translation ne "") {
+ for my $spec (@translation_queries) {
+ do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
+ my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
+ next unless $translation;
$ref->{description} = $translation;
$ref->{longdescription} = $longdescription;
-
- } else {
- $query =
- qq|SELECT tr.translation, tr.longdescription
- FROM translation tr
- WHERE tr.language_id IN
- (SELECT id
- FROM language
- WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
- AND tr.parts_id = ?
- LIMIT 1|;
- @values = (conv_i($form->{language_id}), conv_i($ref->{id}));
- my ($translation, $longdescription) = selectrow_query($form, $dbh, $query, @values);
- if ($translation ne "") {
- $ref->{description} = $translation;
- $ref->{longdescription} = $longdescription;
- }
+ last;
}
}
$ref->{onhand} *= 1;
push @{ $form->{item_list} }, $ref;
-
- if ($form->{lizenzen}) {
- if ($ref->{inventory_accno} > 0) {
- $query =
- qq|SELECT l.*
- 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')) {
- push @{ $form->{LIZENZEN}{ $ref->{id} } }, $ptr;
- }
- $stw->finish;
- }
- }
}
$sth->finish;
+ $_->[1]->finish for @translation_queries;
foreach my $item (@{ $form->{item_list} }) {
my $custom_variables = CVar->get_custom_variables(module => 'IC',
map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
}
- $dbh->disconnect;
-
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form) = @_;
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
$form->{"PRICES"} = {};
my $pricegroup_old = $form->{"pricegroup_old_$i"};
- # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
+ # sellprice has format 13,0000 or 0,00000, can't check for 0 numerically
my $sellprice = $form->{"sellprice_$i"};
my $pricegroup_id = $form->{"pricegroup_id_$i"};
$form->{"new_pricegroup_$i"} = $selectedpricegroup_id;
}
my $query =
- qq|SELECT
+ qq|SELECT
+ 0 as pricegroup_id,
+ sellprice AS default_sellprice,
+ '' AS pricegroup,
+ sellprice AS price,
+ 'selected' AS selected
+ FROM parts
+ WHERE id = ?
+ UNION ALL
+ SELECT
pricegroup_id,
- (SELECT p.sellprice FROM parts p WHERE p.id = ?) AS default_sellprice,
- (SELECT pg.pricegroup FROM pricegroup pg WHERE id = pricegroup_id) AS pricegroup,
+ parts.sellprice AS default_sellprice,
+ pricegroup.pricegroup,
price,
'' AS selected
FROM prices
+ LEFT JOIN parts ON parts.id = parts_id
+ LEFT JOIN pricegroup ON pricegroup.id = pricegroup_id
WHERE parts_id = ?
-
- UNION
-
- SELECT
- 0 as pricegroup_id,
- (SELECT sellprice FROM parts WHERE id = ?) AS default_sellprice,
- '' AS pricegroup,
- (SELECT DISTINCT sellprice FROM parts where id = ?) AS price,
- 'selected' AS selected
- FROM prices
-
ORDER BY pricegroup|;
- my @values = (conv_i($id), conv_i($id), conv_i($id), conv_i($id));
+ my @values = (conv_i($id), conv_i($id));
my $pkq = prepare_execute_query($form, $dbh, $query, @values);
while (my $pkr = $pkq->fetchrow_hashref('NAME_lc')) {
$pkr->{price} *= $form->{"basefactor_$i"};
$pkr->{price} *= $basefactor;
+ $pkr->{price_ufmt} = $pkr->{price};
$pkr->{price} = $form->format_amount($myconfig, $pkr->{price}, 5);
- if ($selectedpricegroup_id eq undef) {
+ if (!defined $selectedpricegroup_id) {
# new entries in article list, either old invoice was loaded (edit) or a new article was added
# Case A: open old invoice, no pricegroup selected
# Case B: add new article to invoice, no pricegroup selected
if ($pkr->{pricegroup_id} eq $form->{"pricegroup_id_$i"} and defined $form->{"pricegroup_id_$i"}) {
# Case A
$pkr->{selected} = ' selected';
-
} elsif ($pkr->{pricegroup_id} eq $form->{customer_klass}
and not defined $form->{"pricegroup_id_$i"}
- and $pkr->{price} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
- # for the case where pricegroup prices haven't been set
+ and $pkr->{price_ufmt} != 0 # only use customer pricegroup price if it has a value, else use default_sellprice
+ # for the case where pricegroup prices haven't been set
) {
# Case B: use default pricegroup of customer
$pkr->{selected} = ' selected'; # unless $form->{selected};
-
# no customer pricesgroup set
- if ($pkr->{price} == $pkr->{default_sellprice}) {
+ if ($pkr->{price_ufmt} == $pkr->{default_sellprice}) {
$pkr->{price} = $form->{"sellprice_$i"};
$form->{"sellprice_$i"} = $pkr->{price};
}
- } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
+ } elsif ($pkr->{price_ufmt} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
$pkr->{price} = $form->{"sellprice_$i"};
$pkr->{selected} = ' selected';
}
$pkr->{selected} = ' selected';
}
} elsif ( ($form->parse_amount($myconfig, $price_new)
- != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
+ != $form->parse_amount($myconfig, $form->{"sellprice_$i"}))
and ($price_new ne 0) and defined $price_new) {
# sellprice has changed
# when loading existing invoices $price_new is NULL
$pkq->finish;
}
- $dbh->disconnect;
-
$main::lxdebug->leave_sub();
}
# ToDO: die when this happens and throw an error
$main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
my ($result) = selectrow_query($form, $dbh, $query, $form->{id});
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
return $result;
# ToDO: die when this happens and throw an error
$main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $query = qq|SELECT storno FROM $table WHERE id = ?|;
my ($result) = selectrow_query($form, $dbh, $query, $id);
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
return $result;
my ($self, $myconfig, $form) = @_;
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
my ($result) = selectrow_query($form, $dbh, $query);
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
return $result;
projects / kivitendo-erp.git / blobdiff