Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision f087c373

Von Jan Büren vor mehr als 10 Jahren hinzugefügt

persistente ids für invoice (items)

analog zu do, oe auch die verknüpften items für rechnungen persistent machen.
- invoice_id retrieve_invoice in array übernehmen
- invoice_pos entfernt (war ggf. vor 2006 ähnlich vorgesehen)
- reverse_invoice gekürzt, sodass hier keine invoice gelöscht werden
- delete_invoice erweitert, sodass hier invoice gelöscht wird
- ferner code von IS.pm nach IR.pm portiert (queries in array)
- use_as_new invoice_ids löschen
- ferner bei storno invoice_ids löschen und ...
- bei Verkaufsrechnung Gutschrift

Ferner Kommentare (IR.pm) eingerückt

tests:

Verkaufsrechnung:
gesamten beleg löschen i.O.
update i.O.
als neu speichern i.O.
mittlere position löschen i.O.
Storno i.O.
Gutschrift i.O.

Einkaufsrechnung:
als neu speichern i.O.
Zahlung buchen i.O.
mittlere position löschen i.O.
gesamten beleg löschen i.O.
Storno i.O.
keine Gutschrift möglich

Unterschiede anzeigen:

SL/IR.pm
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
my $price_factor;
my @processed_invoice_ids;
for my $i (1 .. $form->{rowcount}) {
next unless $form->{"id_$i"};
......
map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
#####################################################################
# das ist aus IS.pm kopiert. schlimm. jb 7.10.2009
# ich würde mir wünschen, dass diese vier stellen zusammengefasst werden
# ... vier stellen = (einkauf + verkauf) * (maske + backend)
# ansonsten stolpert man immer wieder viermal statt einmal heftig
# und auch das undo discount formatting ist nicht besonders wartungsfreundlich
# keine ahnung wofür das in IS.pm gemacht wird:
# my ($dec) = ($fxsellprice =~ /\.(\d+)/);
# $dec = length $dec;
# my $decimalplaces = ($dec > 2) ? $dec : 2;
# copied from IS.pm, with some changes (no decimalplaces corrections here etc)
# TODO maybe use PriceTaxCalculation or something like this for backends (IR.pm / IS.pm)
# undo discount formatting
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;
......
ORDER BY transdate|;
# ORDER BY transdate guarantees FIFO
# sold two items without having bought them yet, example result of query:
# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
# ---+-----+-----------+----------+--------------------+------------------+------------
# 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05
# sold two items without having bought them yet, example result of query:
# id | qty | allocated | trans_id | inventory_accno_id | expense_accno_id | transdate
# ---+-----+-----------+----------+--------------------+------------------+------------
# 9 | 2 | 0 | 9 | 15 | 151 | 2011-01-05
# base_qty + allocated > 0 if article has already been sold but not bought yet
# base_qty + allocated > 0 if article has already been sold but not bought yet
# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9;
# qty | allocated | base_qty | sellprice
# -----+-----------+----------+------------
# 2 | 0 | 2 | 1000.00000
# select qty,allocated,base_qty,sellprice from invoice where trans_id = 9;
# qty | allocated | base_qty | sellprice
# -----+-----------+----------+------------
# 2 | 0 | 2 | 1000.00000
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
......
if ($ref->{allocated} < 0) {
# we have an entry for it already, adjust amount
# we have an entry for it already, adjust amount
$form->update_balance($dbh, "acc_trans", "amount",
qq| (trans_id = $ref->{trans_id})
AND (chart_id = $ref->{inventory_accno_id})
......
$ref->{inventory_accno_id});
do_query($form, $dbh, $query, @values);
# add expense
# add expense
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, tax_id, chart_link) VALUES (?, ?, ?, ?,
(SELECT taxkey_id
FROM taxkeys
......
next if $payments_only;
# save detail record in invoice table
my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
if (!$form->{"invoice_id_$i"}) {
# there is no persistent id, therefore create one with all necessary constraints
my $q_invoice_id = qq|SELECT nextval('invoiceid')|;
my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id);
do_statement($form, $h_invoice_id, $q_invoice_id);
$form->{"invoice_id_$i"} = $h_invoice_id->fetchrow_array();
my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|;
do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"}));
$h_invoice_id->finish();
}
$query =
qq|INSERT INTO invoice (id, trans_id, parts_id, description, longdescription, qty, base_qty,
sellprice, fxsellprice, discount, allocated, unit, deliverydate,
project_id, serialnumber, price_factor_id, price_factor, marge_price_factor,
active_price_source, active_discount_source)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?, ?, ?)|;
@values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
# save detail record in invoice table
$query = <<SQL;
UPDATE invoice SET trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?, base_qty = ?,
sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, unit = ?, deliverydate = ?,
project_id = ?, serialnumber = ?, price_factor_id = ?,
price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?,
active_price_source = ?, active_discount_source = ?
WHERE id = ?
SQL
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"} * -1,
$baseqty * -1, $form->{"sellprice_$i"}, $fxsellprice, $form->{"discount_$i"}, $allocated,
$form->{"unit_$i"}, conv_date($form->{deliverydate}),
conv_i($form->{"project_id_$i"}), $form->{"serialnumber_$i"},
conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"marge_price_factor_$i"}),
$form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
);
conv_i($form->{"invoice_id_$i"}));
do_query($form, $dbh, $query, @values);
push @processed_invoice_ids, $form->{"invoice_id_$i"};
CVar->save_custom_variables(module => 'IC',
sub_module => 'invoice',
trans_id => $invoice_id,
trans_id => $form->{"invoice_id_$i"},
configs => $ic_cvar_configs,
variables => $form,
name_prefix => 'ic_',
......
'arap_id' => $form->{id},
'table' => 'ap',);
# search for orphaned invoice items
$query = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids;
@values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids);
my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
if (scalar @orphaned_ids) {
# clean up invoice items
$query = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
do_query($form, $dbh, $query, @orphaned_ids);
}
# safety check datev export
if ($::instance_conf->get_datev_check_on_purchase_invoice) {
my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
......
$query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
do_query($form, $dbh, $query, $id);
# delete invoice entries
$query = qq|DELETE FROM invoice WHERE trans_id = ?|;
do_query($form, $dbh, $query, $id);
$query = qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AP')|;
do_query($form, $dbh, $query, $id);
......
&reverse_invoice($dbh, $form);
my @values = (conv_i($form->{id}));
# delete zero entries
# wtf? use case for this?
$query = qq|DELETE FROM acc_trans WHERE amount = 0|;
do_query($form, $dbh, $query);
# delete AP record
$query = qq|DELETE FROM ap WHERE id = ?|;
do_query($form, $dbh, $query, conv_i($form->{id}));
my @queries = (
qq|DELETE FROM invoice WHERE trans_id = ?|,
qq|DELETE FROM ap WHERE id = ?|,
);
map { do_query($form, $dbh, $_, @values) } @queries;
my $rc = $dbh->commit;
$dbh->disconnect;
......
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"});
SL/IS.pm
$form->{amount} = {};
$form->{amount_cogs} = {};
my @processed_invoice_ids;
foreach my $i (1 .. $form->{rowcount}) {
if ($form->{type} eq "credit_note") {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
......
$pricegroup_id *= 1;
$pricegroup_id = undef if !$pricegroup_id;
my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);
if (!$form->{"invoice_id_$i"}) {
# there is no persistent id, therefore create one with all necessary constraints
my $q_invoice_id = qq|SELECT nextval('invoiceid')|;
my $h_invoice_id = prepare_query($form, $dbh, $q_invoice_id);
do_statement($form, $h_invoice_id, $q_invoice_id);
$form->{"invoice_id_$i"} = $h_invoice_id->fetchrow_array();
my $q_create_invoice_id = qq|INSERT INTO invoice (id, trans_id, parts_id) values (?, ?, ?)|;
do_query($form, $dbh, $q_create_invoice_id, conv_i($form->{"invoice_id_$i"}), conv_i($form->{id}), conv_i($form->{"id_$i"}));
$h_invoice_id->finish();
}
# save detail record in invoice table
$query =
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, donumber, transdate, cusordnumber, base_qty, subtotal,
marge_percent, marge_total, lastcost, active_price_source, active_discount_source,
price_factor_id, price_factor, marge_price_factor)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
(SELECT factor FROM price_factors WHERE id = ?), ?)|;
@values = ($invoice_id, conv_i($form->{id}), conv_i($form->{"id_$i"}),
$query = <<SQL;
UPDATE invoice SET trans_id = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?,
sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, assemblyitem = ?,
unit = ?, deliverydate = ?, project_id = ?, serialnumber = ?, pricegroup_id = ?,
ordnumber = ?, donumber = ?, transdate = ?, cusordnumber = ?, base_qty = ?, subtotal = ?,
marge_percent = ?, marge_total = ?, lastcost = ?, active_price_source = ?, active_discount_source = ?,
price_factor_id = ?, price_factor = (SELECT factor FROM price_factors WHERE id = ?), marge_price_factor = ?
WHERE id = ?
SQL
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$form->{"discount_$i"}, $allocated, 'f',
......
$form->{"lastcost_$i"},
$form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
conv_i($form->{"price_factor_id_$i"}), conv_i($form->{"price_factor_id_$i"}),
conv_i($form->{"marge_price_factor_$i"}));
conv_i($form->{"marge_price_factor_$i"}),
conv_i($form->{"invoice_id_$i"}));
do_query($form, $dbh, $query, @values);
push @processed_invoice_ids, $form->{"invoice_id_$i"};
CVar->save_custom_variables(module => 'IC',
sub_module => 'invoice',
trans_id => $invoice_id,
trans_id => $form->{"invoice_id_$i"},
configs => $ic_cvar_configs,
variables => $form,
name_prefix => 'ic_',
......
'arap_id' => $form->{id},
'table' => 'ar',);
# search for orphaned invoice items
$query = sprintf 'SELECT id FROM invoice WHERE trans_id = ? AND NOT id IN (%s)', join ', ', ("?") x scalar @processed_invoice_ids;
@values = (conv_i($form->{id}), map { conv_i($_) } @processed_invoice_ids);
my @orphaned_ids = map { $_->{id} } selectall_hashref_query($form, $dbh, $query, @values);
if (scalar @orphaned_ids) {
# clean up invoice items
$query = sprintf 'DELETE FROM invoice WHERE id IN (%s)', join ', ', ("?") x scalar @orphaned_ids;
do_query($form, $dbh, $query, @orphaned_ids);
}
# safety check datev export
if ($::instance_conf->get_datev_check_on_sales_invoice) {
my $transdate = $::form->{invdate} ? DateTime->from_lxoffice($::form->{invdate}) : undef;
......
# delete acc_trans
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);
do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values);
$main::lxdebug->leave_sub();
......
my @queries = (
qq|DELETE FROM status WHERE trans_id = ?|,
qq|DELETE FROM periodic_invoices WHERE ar_id = ?|,
qq|DELETE FROM invoice WHERE trans_id = ?|,
qq|DELETE FROM ar WHERE id = ?|,
);
......
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.donumber, i.transdate, i.cusordnumber, i.subtotal, i.lastcost,
i.project_id, i.serialnumber, 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, i.active_price_source, i.active_discount_source,
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
......
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"});
bin/mozilla/io.pl
push @hidden_vars, qw(invoice_id converted_from_quotation_orderitems_id converted_from_order_orderitems_id
converted_from_delivery_order_items_id);
}
if ($::form->{type} =~ /credit_note/) {
push @hidden_vars, qw(invoice_id converted_from_invoice_id);
}
if ($is_delivery_order) {
map { $form->{"${_}_${i}"} = $form->format_amount(\%myconfig, $form->{"${_}_${i}"}) } qw(sellprice discount lastcost);
push @hidden_vars, grep { defined $form->{"${_}_${i}"} } qw(sellprice discount not_discountable price_factor_id lastcost);
bin/mozilla/ir.pl
# Payments must not be recorded for the new storno invoice.
$form->{paidaccounts} = 0;
map { my $key = $_; delete $form->{$key} if grep { $key =~ /^$_/ } qw(datepaid_ gldate_ acc_trans_id_ source_ memo_ paid_ exchangerate_ AR_paid_) } keys %{ $form };
# set new ids for storno invoice
delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
# saving the history
if(!exists $form->{addition} && $form->{id} ne "") {
......
$form->{paidaccounts} = 1;
$form->{rowcount}--;
$form->{invdate} = $form->current_date(\%myconfig);
delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
&display_form;
$main::lxdebug->leave_sub();
bin/mozilla/is.pl
shiptoname shiptostreet shiptozipcode shiptocity shiptocountry shiptocontact shiptophone shiptofax
shiptoemail shiptodepartment_1 shiptodepartment_2 shiptocp_gender message email subject cc bcc taxaccounts cursor_fokus
convert_from_do_ids convert_from_oe_ids convert_from_ar_ids
invoice_id
show_details
), @custom_hiddens,
map { $_.'_rate', $_.'_description', $_.'_taxnumber' } split / /, $form->{taxaccounts}];
......
$form->{employee_id} = SL::DB::Manager::Employee->current->id;
$form->{forex} = $form->check_exchangerate(\%myconfig, $form->{currency}, $form->{invdate}, 'buy');
$form->{exchangerate} = $form->{forex} if $form->{forex};
delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
&display_form;
......
$form->{invnumber} = "Storno zu " . $form->{invnumber};
$form->{invdate} = DateTime->today->to_lxoffice;
$form->{rowcount}++;
# set new ids for storno invoice
delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
post();
$main::lxdebug->leave_sub();
......
$form->{"${_}_${i}"} = $form->parse_amount(\%myconfig, $form->{"${_}_${i}"}) if $form->{"${_}_${i}"};
}
}
# set new persistent ids for credit note
delete $form->{"invoice_id_$_"} for 1 .. $form->{"rowcount"};
my $currency = $form->{currency};
&invoice_links;

Auch abrufbar als: Unified diff