# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Inventory invoicing module
use List::Util qw(max);
+use Carp;
use SL::AM;
use SL::ARAP;
use SL::CVar;
use SL::IO;
use SL::TransNumber;
use SL::DB::Default;
+use SL::DB::Draft;
use SL::DB::Tax;
use SL::DB::TaxZone;
use SL::TransNumber;
+use SL::DB;
use Data::Dumper;
use strict;
+use constant PCLASS_OK => 0;
+use constant PCLASS_NOTFORSALE => 1;
+use constant PCLASS_NOTFORPURCHASE => 2;
sub invoice_details {
$main::lxdebug->enter_sub();
+ # prepare invoice for printing
+
my ($self, $myconfig, $form, $locale) = @_;
$form->{duedate} ||= $form->{invdate};
# so that they can be sorted in later
my %prepared_template_arrays = IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
my @prepared_arrays = keys %prepared_template_arrays;
+ my @separate_totals = qw(non_separate_subtotal);
my $ic_cvar_configs = CVar->get_configs(module => 'IC');
my $project_cvar_configs = CVar->get_configs(module => 'Projects');
if ($form->{"id_$i"} != 0) {
+ # Prepare linked items for printing
+ if ( $form->{"invoice_id_$i"} ) {
+
+ require SL::DB::InvoiceItem;
+ my $invoice_item = SL::DB::Manager::InvoiceItem->find_by( id => $form->{"invoice_id_$i"} );
+ my $linkeditems = $invoice_item->linked_records( direction => 'from', recursive => 1 );
+
+ # check for (recursively) linked sales quotation items, sales order
+ # items and sales delivery order items.
+
+ # The checks for $form->{"ordnumber_$i"} and quo and do are for the old
+ # behaviour, where this data was stored in its own database fields in
+ # the invoice items, and there were no record links for the items.
+
+ # If this information were to be fetched in retrieve_invoice, e.g. for showing
+ # this information in the second row, then these fields will already have
+ # been set and won't be calculated again. This shouldn't be done there
+ # though, as each invocation creates several database calls per item, and would
+ # make the interface very slow for many items. So currently these
+ # requests are only made when printing the record.
+
+ # When using the workflow an invoice item can only be (recursively) linked to at
+ # most one sales quotation item and at most one delivery order item. But it may
+ # be linked back to several order items, if collective orders were involved. If
+ # that is the case we will always choose the very first order item from the
+ # original order, i.e. where it first appeared in an order.
+
+ # TODO: credit note items aren't checked for a record link to their
+ # invoice item
+
+ unless ( $form->{"ordnumber_$i"} ) {
+
+ # $form->{"ordnumber_$i"} comes from ordnumber in invoice, if an
+ # entry exists this must be from before the change from ordnumber to linked items.
+ # So we just use that value and don't check for linked items.
+ # In that case there won't be any links for quo or do items either
+
+ # sales order items are fetched and sorted by id, the lowest id is first
+ # It is assumed that the id always grows, so the item we want (the original) will have the lowest id
+ # better solution: filter the order_item that doesn't have any links from other order_items
+ # or maybe fetch linked_records with param save_path and order by _record_length_depth
+ my @linked_orderitems = grep { $_->isa("SL::DB::OrderItem") && $_->record->type eq 'sales_order' } @{$linkeditems};
+ if ( scalar @linked_orderitems ) {
+ @linked_orderitems = sort { $a->id <=> $b->id } @linked_orderitems;
+ my $orderitem = $linked_orderitems[0]; # 0: the original order item, -1: the last collective order item
+
+ $form->{"ordnumber_$i"} = $orderitem->record->record_number;
+ $form->{"transdate_oe_$i"} = $orderitem->record->transdate->to_kivitendo;
+ $form->{"cusordnumber_oe_$i"} = $orderitem->record->cusordnumber;
+ };
+
+ my @linked_quoitems = grep { $_->isa("SL::DB::OrderItem") && $_->record->type eq 'sales_quotation' } @{$linkeditems};
+ if ( scalar @linked_quoitems ) {
+ croak "an invoice item may only be linked back to 1 sales quotation item, something is wrong\n" unless scalar @linked_quoitems == 1;
+ $form->{"quonumber_$i"} = $linked_quoitems[0]->record->record_number;
+ $form->{"transdate_quo_$i"} = $linked_quoitems[0]->record->transdate->to_kivitendo;
+ };
+
+ my @linked_deliveryorderitems = grep { $_->isa("SL::DB::DeliveryOrderItem") && $_->record->type eq 'sales_delivery_order' } @{$linkeditems};
+ if ( scalar @linked_deliveryorderitems ) {
+ croak "an invoice item may only be linked back to 1 sales delivery item, something is wrong\n" unless scalar @linked_deliveryorderitems == 1;
+ $form->{"donumber_$i"} = $linked_deliveryorderitems[0]->record->record_number;
+ $form->{"transdate_do_$i"} = $linked_deliveryorderitems[0]->record->transdate->to_kivitendo;
+ };
+ };
+ };
+
+
# add number, description and qty to $form->{number},
if ($form->{"subtotal_$i"} && !$subtotal_header) {
$subtotal_header = $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"});
+ # linked item print variables
+ push @{ $form->{TEMPLATE_ARRAYS}->{quonumber_quo} }, $form->{"quonumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{transdate_quo} }, $form->{"transdate_quo_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{ordnumber_oe} }, $form->{"ordnumber_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_oe_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{cusordnumber_oe} }, $form->{"cusordnumber_oe_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{donumber_do} }, $form->{"donumber_$i"};
- push @{ $form->{TEMPLATE_ARRAYS}->{transdate_oe} }, $form->{"transdate_$i"};
+ push @{ $form->{TEMPLATE_ARRAYS}->{transdate_do} }, $form->{"transdate_do_$i"};
+
push @{ $form->{TEMPLATE_ARRAYS}->{invnumber} }, $form->{"invnumber"};
push @{ $form->{TEMPLATE_ARRAYS}->{invdate} }, $form->{"invdate"};
push @{ $form->{TEMPLATE_ARRAYS}->{price_factor} }, $price_factor->{formatted_factor};
push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};
+ if ( $prepared_template_arrays{separate}[$i - 1] ) {
+ my $pabbr = $prepared_template_arrays{separate}[$i - 1];
+ if ( ! $form->{"separate_${pabbr}_subtotal"} ) {
+ push @separate_totals , "separate_${pabbr}_subtotal";
+ $form->{"separate_${pabbr}_subtotal"} = 0;
+ }
+ $form->{"separate_${pabbr}_subtotal"} += $linetotal;
+ } else {
+ $form->{non_separate_subtotal} += $linetotal;
+ }
+
$form->{total} += $linetotal;
$form->{nodiscount_total} += $nodiscount_linetotal;
$form->{discount_total} += $discount;
$form->{nodiscount} = $form->format_amount($myconfig, $nodiscount, 2);
$form->{yesdiscount} = $form->format_amount($myconfig, $form->{nodiscount_total} - $nodiscount, 2);
- $form->{invtotal} = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
- $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
+ my $grossamount = ($form->{taxincluded}) ? $form->{total} : $form->{total} + $tax;
+ $form->{invtotal} = $form->round_amount($grossamount, 2, 1);
+ $form->{rounding} = $form->round_amount(
+ $form->{invtotal} - $form->round_amount($grossamount, 2),
+ 2
+ );
+ $form->{rounding} = $form->format_amount($myconfig, $form->{rounding}, 2);
+ $form->{total} = $form->format_amount($myconfig, $form->{invtotal} - $form->{paid}, 2);
$form->{invtotal} = $form->format_amount($myconfig, $form->{invtotal}, 2);
$form->{paid} = $form->format_amount($myconfig, $form->{paid}, 2);
- $form->set_payment_options($myconfig, $form->{invdate});
+ $form->set_payment_options($myconfig, $form->{invdate}, 'sales_invoice');
+ $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
$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};
+ $form->{$_} = $form->format_amount($myconfig, $form->{$_}, 2) for @separate_totals;
$main::lxdebug->leave_sub();
}
$ref->{street} = $customer->street;
$ref->{zipcode} = $customer->zipcode;
$ref->{country} = $customer->country;
+ $ref->{gln} = $customer->gln;
}
my $contact = SL::DB::Manager::Contact->find_by(cp_id => $::form->{cp_id});
if ($contact) {
$ref->{cp_gender} = $contact->cp_gender;
}
}
- # remove id and taxincluded before copy back
- delete @$ref{qw(id taxincluded)};
+ # remove id,notes (double of customernotes) and taxincluded before copy back
+ delete @$ref{qw(id taxincluded notes)};
@wanted_vars = grep({ $_ } @wanted_vars);
if (scalar(@wanted_vars) > 0) {
'trans_id' => $form->{customer_id});
map { $form->{"vc_cvar_$_->{name}"} = $_->{value} } @{ $custom_variables };
+ if ($form->{cp_id}) {
+ $custom_variables = CVar->get_custom_variables(dbh => $dbh,
+ module => 'Contacts',
+ trans_id => $form->{cp_id});
+ $form->{"cp_cvar_$_->{name}"} = $_->{value} for @{ $custom_variables };
+ }
+
$form->{cp_greeting} = GenericTranslations->get('dbh' => $dbh,
'translation_type' => 'greetings::' . ($form->{cp_gender} eq 'f' ? 'female' : 'male'),
'language_id' => $language_id,
}
sub post_invoice {
+ my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_post_invoice, $self, $myconfig, $form, $provided_dbh, $payments_only);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _post_invoice {
my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
- # connect to database, turn off autocommit
- my $dbh = $provided_dbh ? $provided_dbh : $form->get_standard_dbh;
+ my $dbh = $provided_dbh || SL::DB->client->dbh;
my $restricter = SL::HTML::Restrict->create;
my ($query, $sth, $null, $project_id, @values);
UPDATE invoice SET trans_id = ?, position = ?, parts_id = ?, description = ?, longdescription = ?, qty = ?,
sellprice = ?, fxsellprice = ?, discount = ?, allocated = ?, assemblyitem = ?,
unit = ?, deliverydate = ?, project_id = ?, serialnumber = ?, pricegroup_id = ?,
- ordnumber = ?, donumber = ?, transdate = ?, cusordnumber = ?, base_qty = ?, subtotal = ?,
+ 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 = ?
$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"}, $form->{"donumber_$i"}, conv_date($form->{"transdate_$i"}),
- $form->{"cusordnumber_$i"}, $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
+ $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
$form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
$form->{"lastcost_$i"},
$form->{"active_price_source_$i"}, $form->{"active_discount_source_$i"},
}
}
- $form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
- $form->{paid} =
- $form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);
+ # Invoice Summary includes Rounding
+ my $grossamount = $netamount + $tax;
+ my $rounding = $form->round_amount(
+ $form->round_amount($grossamount, 2, 1) - $form->round_amount($grossamount, 2),
+ 2
+ );
+ my $rnd_accno = $rounding == 0 ? 0
+ : $rounding > 0 ? $form->{rndgain_accno}
+ : $form->{rndloss_accno}
+ ;
+ $form->{amount}{ $form->{id} }{ $form->{AR} } = $form->round_amount($grossamount, 2, 1);
+ $form->{paid} = $form->round_amount(
+ $form->{paid} * $form->{exchangerate} + $diff,
+ 2
+ );
# reverse AR
$form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
do_query($form, $dbh, $query, @values);
}
}
+ if (!$payments_only && ($rnd_accno != 0)) {
+ $query =
+ 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), $rnd_accno, $rounding, conv_date($form->{invdate}), conv_i($project_id), $rnd_accno);
+ do_query($form, $dbh, $query, @values);
+ $rnd_accno = 0;
+ }
}
# deduct payment differences from diff
$query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
- $dbh->commit if !$provided_dbh;
+ $form->new_lastmtime('ar');
- $main::lxdebug->leave_sub();
return;
}
- $amount = $netamount + $tax;
+ $amount = $form->round_amount( $netamount + $tax, 2, 1);
# save AR record
#erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
if ($form->{storno}) {
$query =
qq!UPDATE ar SET
- paid = paid + amount,
+ paid = amount,
storno = 't',
intnotes = ? || intnotes
WHERE id = ?!;
do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
}
- $form->{name} = $form->{customer};
- $form->{name} =~ s/--\Q$form->{customer_id}\E//;
+ # maybe we are in a larger transaction and the current
+ # object is not yet persistent in the db, therefore we
+ # need the current dbh to get the not yet committed mtime
+ $form->new_lastmtime('ar', $provided_dbh);
# add shipto
if (!$form->{shipto_id}) {
do_query($form, $dbh, $query, @orphaned_ids);
}
+ if ($form->{draft_id}) {
+ SL::DB::Manager::Draft->delete_all(where => [ id => delete($form->{draft_id}) ]);
+ }
+
# 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,
trans_id => $form->{id},
);
- $datev->export;
+ $datev->generate_datev_data;
if ($datev->errors) {
- $dbh->rollback;
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
}
}
- my $rc = 1;
- $dbh->commit if !$provided_dbh;
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub transfer_out {
parts_id => $part->id,
bin_id => $bin_id);
if ($error == 1) {
- push @errors, $::locale->text('Part "#1" has chargenumber or best before date set. So it cannot be transfered automaticaly.',
+ push @errors, $::locale->text('Part "#1" has chargenumber or best before date set. So it cannot be transfered automatically.',
$part->description);
}
my $form_unit_obj = SL::DB::Unit->new(name => $unit)->load;
}
sub post_payment {
+ my ($self, $myconfig, $form, $locale) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, $myconfig, $form, $locale);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _post_payment {
my ($self, $myconfig, $form, $locale) = @_;
- # connect to database, turn off autocommit
- my $dbh = $form->get_standard_dbh;
+ my $dbh = SL::DB->client->dbh;
my (%payments, $old_form, $row, $item, $query, %keep_vars);
restore_form($old_form);
- my $rc = $dbh->commit();
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub process_assembly {
my ($dbh, $myconfig, $form, $position, $id, $totalqty) = @_;
my $query =
- qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit,
- p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
+ qq|SELECT a.parts_id, a.qty, p.part_type, p.partnumber, p.description, p.unit
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
WHERE (a.id = ?)|;
# reverse inventory items
my $query =
- qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
+ qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.part_type
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
WHERE i.trans_id = ?|;
# delete acc_trans
my @values = (conv_i($form->{id}));
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values);
+
+ $query = qq|DELETE FROM custom_variables
+ WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE (module = 'ShipTo')))
+ AND (trans_id IN (SELECT shipto_id FROM shipto WHERE (module = 'AR') AND (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();
}
sub delete_invoice {
+ my ($self, $myconfig, $form) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_delete_invoice, $self, $myconfig, $form);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _delete_invoice {
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->get_standard_dbh;
+ my $dbh = SL::DB->client->dbh;
&reverse_invoice($dbh, $form);
_delete_transfers($dbh, $form, $form->{id});
map { do_query($form, $dbh, $_, @values) } @queries;
- my $rc = $dbh->commit;
-
- if ($rc) {
- my $spool = $::lx_office_conf{paths}->{spool};
- map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
- }
+ my $spool = $::lx_office_conf{paths}->{spool};
+ map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub retrieve_invoice {
+ my ($self, $myconfig, $form) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_retrieve_invoice, $self, $myconfig, $form);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _retrieve_invoice {
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->get_standard_dbh;
+ my $dbh = SL::DB->client->dbh;
my ($sth, $ref, $query);
(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
+ (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno,
+ (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id = c.id) AS rndgain_accno,
+ (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id = c.id) AS rndloss_accno
${query_transdate}
FROM defaults d|;
qq|SELECT
a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber,
a.orddate, a.quodate, a.globalproject_id,
- a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate,
+ a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.storno_id, a.gldate,
a.shippingpoint, a.shipvia, a.notes, a.intnotes, a.taxzone_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.mtime, a.itime,
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.delivery_term_id,
+ dc.dunning_description,
e.name AS employee
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
+ LEFT JOIN dunning_config dc ON (a.dunning_config_id = dc.id)
WHERE a.id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $id);
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
+ $form->{mtime} = $form->{itime} if !$form->{mtime};
+ $form->{lastmtime} = $form->{mtime};
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
}
+ # get shipto
+ $query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
+ $form->{$_} = $ref->{$_} for grep { m{^shipto(?!_id$)} } keys %$ref;
+
# get printed, emailed
$query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
$sth = prepare_execute_query($form, $dbh, $query, $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.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,
+ p.partnumber, p.part_type, p.notes AS partnotes, p.formel, p.listprice,
+ p.classification_id,
pr.projectnumber, pg.partsgroup, prg.pricegroup
FROM invoice i
}
$sth->finish;
- Common::webdav_folder($form);
- }
+ # Fetch shipping address.
+ $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'AR'|;
+ $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{id});
- my $rc = $dbh->commit;
+ $form->{$_} = $ref->{$_} for grep { $_ ne 'id' } keys %$ref;
- $main::lxdebug->leave_sub();
+ if ($form->{shipto_id}) {
+ my $cvars = CVar->get_custom_variables(
+ dbh => $dbh,
+ module => 'ShipTo',
+ trans_id => $form->{shipto_id},
+ );
+ $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
+ }
- return $rc;
+ Common::webdav_folder($form);
+ }
+
+ return 1;
}
sub get_customer {
my $dateformat = $myconfig->{dateformat};
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
- my (@values, $duedate, $ref, $query);
-
- if ($form->{invdate}) {
- $duedate = "to_date(?, '$dateformat')";
- push @values, $form->{invdate};
- } else {
- $duedate = "current_date";
- }
+ my (@values, $ref, $query);
my $cid = conv_i($form->{customer_id});
my $payment_id;
- if ($form->{payment_id}) {
- $payment_id = "(pt.id = ?) OR";
- push @values, conv_i($form->{payment_id});
- }
-
# get customer
+ my $where = '';
+ if ($cid) {
+ $where .= 'AND c.id = ?';
+ push @values, $cid;
+ }
$query =
qq|SELECT
c.id AS customer_id, c.name AS customer, c.discount as customer_discount, c.creditlimit,
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, cu.name AS curr,
+ c.notes AS intnotes, c.pricegroup_id as customer_pricegroup_id, 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;
+ WHERE 1 = 1 $where|;
$ref = selectfirst_hashref_query($form, $dbh, $query, @values);
delete $ref->{salesman_id} if !$ref->{salesman_id};
+ delete $ref->{payment_id} if $form->{payment_id};
map { $form->{$_} = $ref->{$_} } keys %$ref;
+ if ($form->{payment_id}) {
+ my $reference_date = $form->{invdate} ? DateTime->from_kivitendo($form->{invdate}) : undef;
+ $form->{duedate} = SL::DB::PaymentTerm->new(id => $form->{payment_id})->load->calc_date(reference_date => $reference_date)->to_kivitendo;
+ } else {
+ $form->{duedate} = DateTime->today_local->to_kivitendo;
+ }
+
# use customer currency
$form->{currency} = $form->{curr};
}
$sth->finish;
- # setup last accounts used for this customer
- if (!$form->{id} && $form->{type} !~ /_(order|quotation)/) {
- $query =
- qq|SELECT c.id, c.accno, c.description, c.link, c.category
- FROM chart c
- JOIN acc_trans ac ON (ac.chart_id = c.id)
- JOIN ar a ON (a.id = ac.trans_id)
- WHERE a.customer_id = ?
- AND NOT (c.link LIKE '%_tax%' OR c.link LIKE '%_paid%')
- AND a.id IN (SELECT max(a2.id) FROM ar a2 WHERE a2.customer_id = ?)|;
- $sth = prepare_execute_query($form, $dbh, $query, $cid, $cid);
-
- my $i = 0;
- while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
- if ($ref->{category} eq 'I') {
- $i++;
- $form->{"AR_amount_$i"} = "$ref->{accno}--$ref->{description}";
-
- if ($form->{initial_transdate}) {
- my $tax_query =
- qq|SELECT tk.tax_id, t.rate
- FROM taxkeys tk
- LEFT JOIN tax t ON tk.tax_id = t.id
- WHERE (tk.chart_id = ?) AND (startdate <= date(?))
- ORDER BY tk.startdate DESC
- LIMIT 1|;
- my ($tax_id, $rate) =
- selectrow_query($form, $dbh, $tax_query, $ref->{id},
- $form->{initial_transdate});
- $form->{"taxchart_$i"} = "${tax_id}--${rate}";
- }
- }
- if ($ref->{category} eq 'A') {
- $form->{ARselected} = $form->{AR_1} = $ref->{accno};
- }
- }
- $sth->finish;
- $form->{rowcount} = $i if ($i && !$form->{type});
- }
-
$main::lxdebug->leave_sub();
}
my ($table, $field) = split m/\./, $column;
next if !$form->{"${field}_${i}"};
$where .= qq| AND lower(${column}) ILIKE ?|;
- push @values, '%' . $form->{"${field}_${i}"} . '%';
+ push @values, like($form->{"${field}_${i}"});
}
my (%mm_by_id);
my $mm_query = qq|
SELECT parts_id, model FROM makemodel LEFT JOIN parts ON parts.id = parts_id WHERE NOT parts.obsolete AND model ILIKE ?;
|;
- my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%');
+ my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, like($form->{"partnumber_$i"}));
my @mm_ids = map { $_->{parts_id} } @$mm_results;
push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
my $query =
qq|SELECT
p.id, p.partnumber, p.description, p.sellprice,
- p.listprice, p.inventory_accno_id, p.lastcost,
- p.ean,
+ p.listprice, p.part_type, p.lastcost,
+ p.ean, p.notes,
+ p.classification_id,
c1.accno AS inventory_accno,
c1.new_chart_id AS inventory_new_chart,
c3.new_chart_id AS expense_new_chart,
date($transdate) - c3.valid_from AS expense_valid,
- p.unit, p.assembly, p.onhand,
+ p.unit, p.part_type, 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.weight,
pfac.factor AS price_factor,
-
+ pt.used_for_sale AS used_for_sale,
pg.partsgroup
FROM parts p
FROM taxzone_charts tc
WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c3.id)
LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)
+ LEFT JOIN part_classifications pt ON (pt.id = p.classification_id)
LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);
LIMIT 1| ] );
map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
+ my $has_wrong_pclass = PCLASS_OK;
while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
if ($mm_by_id{$ref->{id}}) {
push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
}
- if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
+ if (($::form->{"partnumber_$i"} ne '') && ($ref->{ean} eq $::form->{"partnumber_$i"})) {
push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
}
+ $ref->{type_and_classific} = $::request->presenter->type_abbreviation($ref->{part_type}).
+ $::request->presenter->classification_abbreviation($ref->{classification_id});
+ if (! $ref->{used_for_sale} ) {
+ $has_wrong_pclass = PCLASS_NOTFORSALE ;
+ next;
+ }
# In der Buchungsgruppe ist immer ein Bestandskonto verknuepft, auch wenn
# es sich um eine Dienstleistung handelt. Bei Dienstleistungen muss das
# Buchungskonto also aus dem Ergebnis rausgenommen werden.
$sth->finish;
$_->[1]->finish for @translation_queries;
+ $form->{is_wrong_pclass} = $has_wrong_pclass;
+ $form->{NOTFORSALE} = PCLASS_NOTFORSALE;
+ $form->{NOTFORPURCHASE} = PCLASS_NOTFORPURCHASE;
foreach my $item (@{ $form->{item_list} }) {
my $custom_variables = CVar->get_custom_variables(module => 'IC',
trans_id => $item->{id},
dbh => $dbh,
);
-
+ $form->{is_wrong_pclass} = PCLASS_OK; # one correct type
map { $item->{"ic_cvar_" . $_->{name} } = $_->{value} } @{ $custom_variables };
}
-
$main::lxdebug->leave_sub();
}
projects / kivitendo-erp.git / blobdiff