my @arrays =
qw(runningnumber number description longdescription qty ship unit bin
- deliverydate_oe ordnumber_oe transdate_oe licensenumber validuntil
+ deliverydate_oe ordnumber_oe transdate_oe validuntil
partnotes serialnumber reqdate sellprice listprice netprice
discount p_discount discount_sub nodiscount_sub
linetotal nodiscount_linetotal tax_rate projectnumber projectdescription
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+)/);
$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 };
}
}
# 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,
+ ct.curr AS currency
FROM customer ct
LEFT JOIN contacts cp on ct.id = cp.cp_cv_id
WHERE (ct.id = ?) $where
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
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);
}
}
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,
if (!$form->{storno}) {
for my $i (1 .. $form->{paidaccounts}) {
+ if ($form->{"acc_trans_id_$i"}
+ && $payments_only
+ && ($::lx_office_conf{features}->{payments_changeable} == 0)) {
+ next;
+ }
+
next if ($form->{"paid_$i"} == 0);
my ($accno) = split(/--/, $form->{"AR_paid_$i"});
# 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 = ?), ?, ?, ?, ?,
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id)
+ VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
(SELECT taxkey_id 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, $project_id);
do_query($form, $dbh, $query, @values);
# exchangerate difference
$old_form = save_form();
# Delete all entries in acc_trans from prior payments.
- $self->_delete_payments($form, $dbh);
+ if ($::lx_office_conf{features}->{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);
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();
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;
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
$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");
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,
- 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.bin, 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};
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,
+ c.notes AS intnotes, c.klass as customer_klass, c.taxzone_id, c.salesman_id, c.curr,
$duedate + COALESCE(pt.terms_netto, 0) AS duedate,
b.discount AS tradediscount, b.description AS business
FROM customer c
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};
+
$query =
qq|SELECT sum(amount - paid) AS dunning_amount
FROM ar
push @values, $form->{"partnumber_$i"};
}
+ # Search for part ID overrides all other criteria.
if ($form->{"id_${i}"}) {
- $where .= qq| AND p.id = ?|;
- push @values, $form->{"id_${i}"};
+ $where = qq|p.id = ?|;
+ @values = ($form->{"id_${i}"});
}
if ($form->{"description_$i"}) {
$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;
projects / kivitendo-erp.git / blobdiff