use SL::IO;
use SL::TransNumber;
use SL::DB::Default;
+use SL::DB::Tax;
+use SL::TransNumber;
use Data::Dumper;
use strict;
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 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}->{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}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;
- push(@{ $form->{TEMPLATE_ARRAYS}->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
- push(@{ $form->{TEMPLATE_ARRAYS}->{projectdescription} }, $projectdescriptions{$form->{"project_id_$i"}});
+ my $project = $projects_by_id{$form->{"project_id_$i"}} || SL::DB::Project->new;
+
+ 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 =
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}->{tax_nofmt} }, $taxamount );
push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate} }, $form->format_amount($myconfig, $form->{"${item}_rate"} * 100));
push(@{ $form->{TEMPLATE_ARRAYS}->{taxrate_nofmt} }, $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"});
+
+ 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}) {
$form->set_payment_options($myconfig, $form->{invdate});
+ $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};
+
$form->{username} = $myconfig->{name};
$main::lxdebug->leave_sub();
my $query =
qq|SELECT ct.*, cp.*, ct.notes as customernotes,
ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail,
- ct.curr AS currency
+ 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|;
map { $form->{$_} = $ref->{$_} } keys %$ref;
- # remove any trailing whitespace
- $form->{currency} =~ s/\s*$// if ($form->{currency});
-
if ($form->{delivery_customer_id}) {
$query =
qq|SELECT *, notes as customernotes
}
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
+ my $defaultcurrency = $form->{defaultcurrency};
+
# 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
$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 {
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->{"discount_$i"}, $allocated, 'f',
$form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
$form->{"serialnumber_$i"}, $pricegroup_id,
- $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
+ $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"},
}
$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} }) {
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 = ?, invnumber_for_credit_note = ?, direct_debit = ?
+ 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->{"globalproject_id"}), conv_i($form->{"delivery_customer_id"}),
$form->{transaction_description}, conv_i($form->{"delivery_vendor_id"}),
$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);
dbh => $dbh,
from => $transdate,
to => $transdate,
+ trans_id => $form->{id},
);
$datev->export;
# connect to database, turn off autocommit
my $dbh = $form->get_standard_dbh;
- $dbh->begin_work;
my (%payments, $old_form, $row, $item, $query, %keep_vars);
# connect to database
my $dbh = $form->get_standard_dbh;
- $dbh->begin_work;
&reverse_invoice($dbh, $form);
(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.donumber, a.invnumber_for_credit_note,
- a.marge_total, a.marge_percent, a.direct_debit,
+ 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)
$ref = selectfirst_hashref_query($form, $dbh, $query, $id);
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
- # remove any trailing whitespace
- $form->{currency} =~ s/\s*$//;
-
$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.listprice,
+ 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
$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.curr,
+ 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;
- # remove any trailing whitespace
- $form->{curr} =~ s/\s*$//;
-
- # use customer currency if not empty
- $form->{currency} = $form->{curr} if $form->{curr};
+ # use customer currency
+ $form->{currency} = $form->{curr};
$query =
qq|SELECT sum(amount - paid) AS dunning_amount
$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 = ?
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,