package IS;
-#use strict;
-
use List::Util qw(max);
use SL::AM;
use SL::Common;
use SL::DBUtils;
use SL::DO;
+use SL::GenericTranslations;
use SL::MoreCommon;
use SL::IC;
+use SL::IO;
+use SL::TransNumber;
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"});
}
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;
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
+ linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
price_factor price_factor_name partsgroup);
+ 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);
- $form->{TEMPLATE_ARRAYS} = { map { $_ => [] } (@arrays, @tax_arrays, @payment_arrays) };
+ map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];
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->{"deliverydate_$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}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
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;
}
$sth->finish;
}
+
+ map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
}
}
# 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 ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
# connect to database, turn off autocommit
- my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+ my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
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);
}
&reverse_invoice($dbh, $form);
} else {
+ my $trans_number = SL::TransNumber->new(type => $form->{type}, dbh => $dbh, number => $form->{invnumber}, save => 1);
+ $form->{invnumber} = $trans_number->create_unique unless $trans_number->is_unique;
+
$query = qq|SELECT nextval('glid')|;
($form->{"id"}) = selectrow_query($form, $dbh, $query);
if ($form->{currency} eq $defaultcurrency) {
$form->{exchangerate} = 1;
} else {
- $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, 'buy');
+ $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
}
$form->{exchangerate} =
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
my $price_factor;
+ $form->{amount} = {};
$form->{amount_cogs} = {};
foreach my $i (1 .. $form->{rowcount}) {
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") {
$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();
$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 = ?,
cp_id = ?, marge_total = ?, marge_percent = ?,
globalproject_id = ?, delivery_customer_id = ?,
transaction_description = ?, delivery_vendor_id = ?,
- donumber = ?
+ 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"}),
+ $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',
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"}, #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 =
my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
my $taxzone_id = $form->{"taxzone_id"} * 1;
my $query =
- qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice,
+ qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, i.price_factor,
c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from AS inventory_valid,
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
# total expenses and inventory
# sellprice is the cost of the item
- my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( $basefactor || 1 ), 2);
+ my $linetotal = $form->round_amount(($ref->{sellprice} * $qty) / ( ($ref->{price_factor} || 1) * ( $basefactor || 1 )), 2);
if (!$main::eur) {
$ref->{expense_accno} = ($form->{"expense_accno_$row"}) ? $form->{"expense_accno_$row"} : $ref->{expense_accno};
my @values = (conv_i($form->{id}));
+ # Falls wir ein Storno haben, müssen zwei Felder in der stornierten Rechnung wieder
+ # zurückgesetzt werden. Vgl:
+ # id | storno | storno_id | paid | amount
+ #----+--------+-----------+---------+-----------
+ # 18 | f | | 0.00000 | 119.00000
+ # ZU:
+ # 18 | t | | 119.00000 | 119.00000
+ #
+ if($form->{storno}){
+ # storno_id auslesen und korrigieren
+ my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT storno_id FROM ar WHERE id = ?|,@values);
+ 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);
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = $form->get_standard_dbh;
my ($sth, $ref, $query);
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')) {
+ # 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"});
}
my $rc = $dbh->commit;
- $dbh->disconnect;
$main::lxdebug->leave_sub();
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = $form->get_standard_dbh;
my $dateformat = $myconfig->{dateformat};
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
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 =
$form->{rowcount} = $i if ($i && !$form->{type});
}
- $dbh->disconnect;
-
$main::lxdebug->leave_sub();
}
#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"};
+ $where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
+ push @values, $form->{"partnumber_$i"};
}
if ($form->{"description_$i"}) {
}
}
$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"};
}
- my ($price, $selectedpricegroup_id) = split(/--/,
- $form->{"sellprice_pg_$i"});
+ my ($price, $selectedpricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
my $pricegroup_old = $form->{"pricegroup_old_$i"};
+
+ # 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;
$form->{"old_pricegroup_$i"} = $pricegroup_old;
# 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) {
- if ($pkr->{pricegroup_id} eq $form->{customer_klass}) {
-
+ # 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
+
+ # to distinguish case A and B the variable pricegroup_id_$i is used
+ # for new articles this variable isn't defined, for loaded articles it is
+ # sellprice can't be used, as it already has 0,00 set
+
+ 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
+ ) {
+ # Case B: use default pricegroup of customer
+
+ $pkr->{selected} = ' selected'; # unless $form->{selected};
+
# no customer pricesgroup set
if ($pkr->{price} == $pkr->{default_sellprice}) {
} else {
# this sub should not set anything and only return. --sschoeling, 20090506
-# $form->{"sellprice_$i"} = $pkr->{price};
+# is this correct? put in again... -- grichardson 20110119
+ $form->{"sellprice_$i"} = $pkr->{price};
}
- } elsif ($pkr->{price} == $pkr->{default_sellprice}) {
+ } elsif ($pkr->{price} == $pkr->{default_sellprice} and $pkr->{default_sellprice} != 0) {
$pkr->{price} = $form->{"sellprice_$i"};
$pkr->{selected} = ' selected';
}
}
+ # existing article: pricegroup or price changed
if ($selectedpricegroup_id or $selectedpricegroup_id == 0) {
if ($selectedpricegroup_id ne $pricegroup_old) {
+ # pricegroup has changed
if ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
$pkr->{selected} = ' selected';
}
- } elsif (($price_new != $form->{"sellprice_$i"}) and ($price_new ne 0)) {
+ } elsif ( ($form->parse_amount($myconfig, $price_new)
+ != $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
if ($pkr->{pricegroup_id} == 0) {
$pkr->{price} = $form->{"sellprice_$i"};
$pkr->{selected} = ' selected';
}
} elsif ($pkr->{pricegroup_id} eq $selectedpricegroup_id) {
+ # neither sellprice nor pricegroup changed
$pkr->{selected} = ' selected';
- if ( ($pkr->{pricegroup_id} == 0)
- and ($pkr->{price} == $form->{"sellprice_$i"})) {
+ if ( ($pkr->{pricegroup_id} == 0) and ($pkr->{price} == $form->{"sellprice_$i"})) {
# $pkr->{price} = $form->{"sellprice_$i"};
} else {
$pkr->{price} = $form->{"sellprice_$i"};
return $result;
}
+sub get_standard_accno_current_assets {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ 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;
+}
+
1;
projects / kivitendo-erp.git / blobdiff