Projekt

Allgemein

Profil

Herunterladen (85 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/IS.pm @ 4f152ed2

#=====================================================================
# LX-Office ERP
# Copyright (C) 2004
# Based on SQL-Ledger Version 2.1.9
# Web http://www.lx-office.org
#
#=====================================================================
# SQL-Ledger Accounting
# Copyright (C) 1998-2002
#
# Author: Dieter Simader
# Email: dsimader@sql-ledger.org
# Web: http://www.sql-ledger.org
#
# Contributors:
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# 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.
#======================================================================
#
# Inventory invoicing module
#
#======================================================================

package IS;

use List::Util qw(max);

use SL::AM;
use SL::ARAP;
use SL::CVar;
use SL::Common;
use SL::DATEV qw(:CONSTANTS);
use SL::DBUtils;
use SL::DO;
use SL::GenericTranslations;
use SL::HTML::Restrict;
use SL::MoreCommon;
use SL::IC;
use SL::IO;
use SL::TransNumber;
use SL::DB::Default;
use SL::DB::Tax;
use SL::DB::TaxZone;
use SL::TransNumber;
use Data::Dumper;

use strict;

sub invoice_details {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form, $locale) = @_;

$form->{duedate} ||= $form->{invdate};

# connect to database
my $dbh = $form->get_standard_dbh;
my $sth;

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);
$form->{TEMPLATE_ARRAYS} = {};

push(@project_ids, $form->{"globalproject_id"}) if ($form->{"globalproject_id"});

$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
my %price_factors;

foreach my $pfac (@{ $form->{ALL_PRICE_FACTORS} }) {
$price_factors{$pfac->{id}} = $pfac;
$pfac->{factor} *= 1;
$pfac->{formatted_factor} = $form->format_amount($myconfig, $pfac->{factor});
}

# sort items by partsgroup
for my $i (1 .. $form->{rowcount}) {
# $partsgroup = "";
# if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
# $partsgroup = $form->{"partsgroup_$i"};
# }
# push @partsgroup, [$i, $partsgroup];
push(@project_ids, $form->{"project_id_$i"}) if ($form->{"project_id_$i"});
}

my $projects = [];
my %projects_by_id;
if (@project_ids) {
$projects = SL::DB::Manager::Project->get_all(query => [ id => \@project_ids ]);
%projects_by_id = map { $_->id => $_ } @$projects;
}

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;

# sort items by partsgroup
for $i (1 .. $form->{rowcount}) {
$partsgroup = "";
if ($form->{"partsgroup_$i"} && $form->{groupitems}) {
$partsgroup = $form->{"partsgroup_$i"};
}
push @partsgroup, [$i, $partsgroup];
}

my $sameitem = "";
my @taxaccounts;
my %taxaccounts;
my %taxbase;
my $taxrate;
my $taxamount;
my $taxbase;
my $taxdiff;
my $nodiscount;
my $yesdiscount;
my $nodiscount_subtotal = 0;
my $discount_subtotal = 0;
my $position = 0;
my $subtotal_header = 0;
my $subposition = 0;

$form->{discount} = [];

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 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 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);

my @payment_arrays = qw(payment paymentaccount paymentdate paymentsource paymentmemo);

map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays);

my $totalweight = 0;
foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
$i = $item->[0];

if ($item->[1] ne $sameitem) {
push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
$sameitem = $item->[1];

map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
}

$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});

if ($form->{"id_$i"} != 0) {

# add number, description and qty to $form->{number},
if ($form->{"subtotal_$i"} && !$subtotal_header) {
$subtotal_header = $i;
$position = int($position);
$subposition = 0;
$position++;
} elsif ($subtotal_header) {
$subposition += 1;
$position = int($position);
$position = $position.".".$subposition;
} else {
$position = int($position);
$position++;
}

my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 };

push @{ $form->{TEMPLATE_ARRAYS}->{runningnumber} }, $position;
push @{ $form->{TEMPLATE_ARRAYS}->{number} }, $form->{"partnumber_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{serialnumber} }, $form->{"serialnumber_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{bin} }, $form->{"bin_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{partnotes} }, $form->{"partnotes_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{description} }, $form->{"description_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{longdescription} }, $form->{"longdescription_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"});
push @{ $form->{TEMPLATE_ARRAYS}->{qty_nofmt} }, $form->{"qty_$i"};
push @{ $form->{TEMPLATE_ARRAYS}->{unit} }, $form->{"unit_$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"});
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}->{price_factor} }, $price_factor->{formatted_factor};
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"};
push(@{ $form->{TEMPLATE_ARRAYS}->{listprice} }, $form->{"listprice_$i"});

my $sellprice = $form->parse_amount($myconfig, $form->{"sellprice_$i"});
my ($dec) = ($sellprice =~ /\.(\d+)/);
my $decimalplaces = max 2, length($dec);

my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"});

my $linetotal_exact = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
my $linetotal = $form->round_amount($linetotal_exact, 2);

my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice / $price_factor->{factor};
my $nodiscount_linetotal = $form->round_amount($nodiscount_exact_linetotal,2);

my $discount = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded

my $discount_round_error = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used

$form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, $decimalplaces);

push @{ $form->{TEMPLATE_ARRAYS}->{netprice} }, ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
push @{ $form->{TEMPLATE_ARRAYS}->{netprice_nofmt} }, ($form->{"netprice_$i"} != 0) ? $form->{"netprice_$i"} : '';

$linetotal = ($linetotal != 0) ? $linetotal : '';

push @{ $form->{TEMPLATE_ARRAYS}->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : '';
push @{ $form->{TEMPLATE_ARRAYS}->{discount_nofmt} }, ($discount != 0) ? $discount * -1 : '';
push @{ $form->{TEMPLATE_ARRAYS}->{p_discount} }, $form->{"discount_$i"};

$form->{total} += $linetotal;
$form->{nodiscount_total} += $nodiscount_linetotal;
$form->{discount_total} += $discount;

if ($subtotal_header) {
$discount_subtotal += $linetotal;
$nodiscount_subtotal += $nodiscount_linetotal;
}

if ($form->{"subtotal_$i"} && $subtotal_header && ($subtotal_header != $i)) {
push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} }, $form->format_amount($myconfig, $discount_subtotal, 2);
push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub_nofmt} }, $discount_subtotal;
push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, $form->format_amount($myconfig, $nodiscount_subtotal, 2);
push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub_nofmt} }, $nodiscount_subtotal;

$discount_subtotal = 0;
$nodiscount_subtotal = 0;
$subtotal_header = 0;

} else {
push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
}

if (!$form->{"discount_$i"}) {
$nodiscount += $linetotal;
}

push @{ $form->{TEMPLATE_ARRAYS}->{linetotal} }, $form->format_amount($myconfig, $linetotal, 2);
push @{ $form->{TEMPLATE_ARRAYS}->{linetotal_nofmt} }, $linetotal_exact;
push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal} }, $form->format_amount($myconfig, $nodiscount_linetotal, 2);
push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_linetotal_nofmt} }, $nodiscount_linetotal;

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;
$taxdiff = 0;

map { $taxrate += $form->{"${_}_rate"} } @taxaccounts;

if ($form->{taxincluded}) {

# calculate tax
$taxamount = $linetotal * $taxrate / (1 + $taxrate);
$taxbase = $linetotal - $taxamount;
} else {
$taxamount = $linetotal * $taxrate;
$taxbase = $linetotal;
}

if ($form->round_amount($taxrate, 7) == 0) {
if ($form->{taxincluded}) {
foreach my $accno (@taxaccounts) {
$taxamount = $form->round_amount($linetotal * $form->{"${accno}_rate"} / (1 + abs($form->{"${accno}_rate"})), 2);

$taxaccounts{$accno} += $taxamount;
$taxdiff += $taxamount;

$taxbase{$accno} += $taxbase;
}
$taxaccounts{ $taxaccounts[0] } += $taxdiff;
} else {
foreach my $accno (@taxaccounts) {
$taxaccounts{$accno} += $linetotal * $form->{"${accno}_rate"};
$taxbase{$accno} += $taxbase;
}
}
} else {
foreach my $accno (@taxaccounts) {
$taxaccounts{$accno} += $taxamount * $form->{"${accno}_rate"} / $taxrate;
$taxbase{$accno} += $taxbase;
}
}
my $tax_rate = $taxrate * 100;
push(@{ $form->{TEMPLATE_ARRAYS}->{tax_rate} }, qq|$tax_rate|);
if ($form->{"assembly_$i"}) {
$sameitem = "";

# get parts and push them onto the stack
my $sortorder = "";
if ($form->{groupitems}) {
$sortorder =
qq|ORDER BY pg.partsgroup, a.oid|;
} else {
$sortorder = qq|ORDER BY a.oid|;
}

$query =
qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
WHERE (a.bom = '1') AND (a.id = ?) $sortorder|;
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));

while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
$sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, $sameitem);
}

map { $form->{"a_$_"} = $ref->{$_} } qw(partnumber description);

push(@{ $form->{TEMPLATE_ARRAYS}->{description} },
$form->format_amount($myconfig, $ref->{qty} * $form->{"qty_$i"}
)
. qq| -- $form->{"a_partnumber"}, $form->{"a_description"}|);
map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));

}
$sth->finish;
}

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}->{taxbase} }, $form->format_amount($myconfig, $taxbase{$item}, 2));
push(@{ $form->{TEMPLATE_ARRAYS}->{taxbase_nofmt} }, $taxbase{$item});
push(@{ $form->{TEMPLATE_ARRAYS}->{tax} }, $form->format_amount($myconfig, $taxamount, 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}->{taxnumber} }, $form->{"${item}_taxnumber"});

my $tax_obj = SL::DB::Manager::Tax->find_by(taxnumber => $form->{"${item}_taxnumber"});
my $description = $tax_obj ? $tax_obj->translated_attribute('taxdescription', $form->{language_id}, 0) : '';
push(@{ $form->{TEMPLATE_ARRAYS}->{taxdescription} }, $description . q{ } . 100 * $form->{"${item}_rate"} . q{%});
}

for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"}) {
my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});

push(@{ $form->{TEMPLATE_ARRAYS}->{payment} }, $form->{"paid_$i"});
push(@{ $form->{TEMPLATE_ARRAYS}->{paymentaccount} }, $description);
push(@{ $form->{TEMPLATE_ARRAYS}->{paymentdate} }, $form->{"datepaid_$i"});
push(@{ $form->{TEMPLATE_ARRAYS}->{paymentsource} }, $form->{"source_$i"});
push(@{ $form->{TEMPLATE_ARRAYS}->{paymentmemo} }, $form->{"memo_$i"});

$form->{paid} += $form->parse_amount($myconfig, $form->{"paid_$i"});
}
}
if($form->{taxincluded}) {
$form->{subtotal} = $form->format_amount($myconfig, $form->{total} - $tax, 2);
$form->{subtotal_nofmt} = $form->{total} - $tax;
}
else {
$form->{subtotal} = $form->format_amount($myconfig, $form->{total}, 2);
$form->{subtotal_nofmt} = $form->{total};
}

$form->{nodiscount_subtotal} = $form->format_amount($myconfig, $form->{nodiscount_total}, 2);
$form->{discount_total} = $form->format_amount($myconfig, $form->{discount_total}, 2);
$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);

$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->{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();
}

sub project_description {
$main::lxdebug->enter_sub();

my ($self, $dbh, $id) = @_;
my $form = \%main::form;

my $query = qq|SELECT description FROM project WHERE id = ?|;
my ($description) = selectrow_query($form, $dbh, $query, conv_i($id));

$main::lxdebug->leave_sub();

return $_;
}

sub customer_details {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form, @wanted_vars) = @_;

# connect to database
my $dbh = $form->get_standard_dbh;

my $language_id = $form->{language_id};

# get contact id, set it if nessessary
$form->{cp_id} *= 1;

my @values = (conv_i($form->{customer_id}));

my $where = "";
if ($form->{cp_id}) {
$where = qq| AND (cp.cp_id = ?) |;
push(@values, conv_i($form->{cp_id}));
}

# 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,
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|;
my $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
# we have no values, probably a invalid contact person. hotfix and first idea for issue #10
if (!$ref) {
my $customer = SL::DB::Manager::Customer->find_by(id => $::form->{customer_id});
if ($customer) {
$ref->{name} = $customer->name;
$ref->{street} = $customer->street;
$ref->{zipcode} = $customer->zipcode;
$ref->{country} = $customer->country;
}
my $contact = SL::DB::Manager::Contact->find_by(cp_id => $::form->{cp_id});
if ($contact) {
$ref->{cp_name} = $contact->cp_name;
$ref->{cp_givenname} = $contact->cp_givenname;
$ref->{cp_gender} = $contact->cp_gender;
}
}
# remove id and taxincluded before copy back
delete @$ref{qw(id taxincluded)};

@wanted_vars = grep({ $_ } @wanted_vars);
if (scalar(@wanted_vars) > 0) {
my %h_wanted_vars;
map({ $h_wanted_vars{$_} = 1; } @wanted_vars);
map({ delete($ref->{$_}) unless ($h_wanted_vars{$_}); } keys(%{$ref}));
}

map { $form->{$_} = $ref->{$_} } keys %$ref;

if ($form->{delivery_customer_id}) {
$query =
qq|SELECT *, notes as customernotes
FROM customer
WHERE id = ?
LIMIT 1|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));

map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
}

if ($form->{delivery_vendor_id}) {
$query =
qq|SELECT *, notes as customernotes
FROM customer
WHERE id = ?
LIMIT 1|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));

map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
}

my $custom_variables = CVar->get_custom_variables('dbh' => $dbh,
'module' => 'CT',
'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);


$main::lxdebug->leave_sub();
}

sub post_invoice {
$main::lxdebug->enter_sub();

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 $restricter = SL::HTML::Restrict->create;

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);
}

$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
if (!$form->{department_id}){
($null, $form->{department_id}) = split(/--/, $form->{department});
}

my $all_units = AM->retrieve_units($myconfig, $form);

if (!$payments_only) {
if ($form->{id}) {
&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);

$query = qq|INSERT INTO ar (id, invnumber, currency_id, taxzone_id) VALUES (?, ?, (SELECT id FROM currencies WHERE name=?), ?)|;
do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}, $form->{currency}, $form->{taxzone_id});

if (!$form->{invnumber}) {
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);

if ($form->{currency} eq $defaultcurrency) {
$form->{exchangerate} = 1;
} else {
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
}

$form->{exchangerate} =
($exchangerate)
? $exchangerate
: $form->parse_amount($myconfig, $form->{exchangerate});

$form->{expense_inventory} = "";

my %baseunits;

$form->get_lists('price_factors' => 'ALL_PRICE_FACTORS');
my %price_factors = map { $_->{id} => $_->{factor} } @{ $form->{ALL_PRICE_FACTORS} };
my $price_factor;

$form->{amount} = {};
$form->{amount_cogs} = {};

foreach my $i (1 .. $form->{rowcount}) {
if ($form->{type} eq "credit_note") {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
$form->{shipped} = 1;
} else {
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
}
my $basefactor;
my $baseqty;

$form->{"marge_percent_$i"} = $form->parse_amount($myconfig, $form->{"marge_percent_$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;
}

if ($form->{"id_$i"}) {
my $item_unit;

if (defined($baseunits{$form->{"id_$i"}})) {
$item_unit = $baseunits{$form->{"id_$i"}};
} else {
# get item baseunit
$query = qq|SELECT unit FROM parts WHERE id = ?|;
($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
$baseunits{$form->{"id_$i"}} = $item_unit;
}

if (defined($all_units->{$item_unit}->{factor})
&& ($all_units->{$item_unit}->{factor} ne '')
&& ($all_units->{$item_unit}->{factor} != 0)) {
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
} else {
$basefactor = 1;
}
$baseqty = $form->{"qty_$i"} * $basefactor;

my ($allocated, $taxrate) = (0, 0);
my $taxamount;

# add tax rates
map { $taxrate += $form->{"${_}_rate"} } split(/ /, $form->{"taxaccounts_$i"});

# keep entered selling price
my $fxsellprice =
$form->parse_amount($myconfig, $form->{"sellprice_$i"});

my ($dec) = ($fxsellprice =~ /\.(\d+)/);
$dec = length $dec;
my $decimalplaces = ($dec > 2) ? $dec : 2;

# undo discount formatting
$form->{"discount_$i"} = $form->parse_amount($myconfig, $form->{"discount_$i"}) / 100;

# deduct discount
$form->{"sellprice_$i"} = $fxsellprice * (1 - $form->{"discount_$i"});

# round linetotal to 2 decimal places
$price_factor = $price_factors{ $form->{"price_factor_id_$i"} } || 1;
$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2);

if ($form->{taxincluded}) {
$taxamount = $linetotal * ($taxrate / (1 + $taxrate));
$form->{"sellprice_$i"} =
$form->{"sellprice_$i"} * (1 / (1 + $taxrate));
} else {
$taxamount = $linetotal * $taxrate;
}

$netamount += $linetotal;

if ($taxamount != 0) {
map {
$form->{amount}{ $form->{id} }{$_} +=
$taxamount * $form->{"${_}_rate"} / $taxrate
} split(/ /, $form->{"taxaccounts_$i"});
}

# add amount to income, $form->{amount}{trans_id}{accno}
$amount = $form->{"sellprice_$i"} * $form->{"qty_$i"} * $form->{exchangerate} / $price_factor;

$linetotal = $form->round_amount($form->{"sellprice_$i"} * $form->{"qty_$i"} / $price_factor, 2) * $form->{exchangerate};
$linetotal = $form->round_amount($linetotal, 2);

# this is the difference from the inventory
$invoicediff += ($amount - $linetotal);

$form->{amount}{ $form->{id} }{ $form->{"income_accno_$i"} } +=
$linetotal;

$lastincomeaccno = $form->{"income_accno_$i"};

# adjust and round sellprice
$form->{"sellprice_$i"} =
$form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate},
$decimalplaces);

next if $payments_only;

if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) {

if ($form->{"assembly_$i"}) {
# record assembly item as allocated
&process_assembly($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty);

} else {
$allocated = &cogs($dbh, $myconfig, $form, $form->{"id_$i"}, $baseqty, $basefactor, $i);
}
}

# Get pricegroup_id and save it. Unfortunately the interface
# also uses ID "0" for signalling that none is selected, but "0"
# must not be stored in the database. Therefore we cannot simply
# use conv_i().
($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
$pricegroup_id *= 1;
$pricegroup_id = undef if !$pricegroup_id;

my ($invoice_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('invoiceid')|);

# 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"}),
$form->{"description_$i"}, $restricter->process($form->{"longdescription_$i"}), $form->{"qty_$i"},
$form->{"sellprice_$i"}, $fxsellprice,
$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',
$form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
$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"}));
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);
}
}

# total payments, don't move we need it here
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{type} eq "credit_note") {
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}) * -1;
} else {
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
}
$form->{paid} += $form->{"paid_$i"};
$form->{datepaid} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
}

my ($tax, $diff) = (0, 0);

$netamount = $form->round_amount($netamount, 2);

# figure out rounding errors for total amount vs netamount + taxes
if ($form->{taxincluded}) {

$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
$diff += $amount - $netamount * $form->{exchangerate};
$netamount = $amount;

foreach my $item (split(/ /, $form->{taxaccounts})) {
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
$tax += $form->{amount}{ $form->{id} }{$item};
$netamount -= $form->{amount}{ $form->{id} }{$item};
}

$invoicediff += $diff;
######## this only applies to tax included
if ($lastincomeaccno) {
$form->{amount}{ $form->{id} }{$lastincomeaccno} += $invoicediff;
}

} else {
$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
$diff = $amount - $netamount * $form->{exchangerate};
$netamount = $amount;
foreach my $item (split(/ /, $form->{taxaccounts})) {
$form->{amount}{ $form->{id} }{$item} =
$form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
$amount =
$form->round_amount(
$form->{amount}{ $form->{id} }{$item} * $form->{exchangerate},
2);
$diff +=
$amount - $form->{amount}{ $form->{id} }{$item} *
$form->{exchangerate};
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
$tax += $form->{amount}{ $form->{id} }{$item};
}
}

$form->{amount}{ $form->{id} }{ $form->{AR} } = $netamount + $tax;
$form->{paid} =
$form->round_amount($form->{paid} * $form->{exchangerate} + $diff, 2);

# reverse AR
$form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;

# update exchangerate
if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
$form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
$form->{exchangerate}, 0);
}

$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} }) {
foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
next unless ($form->{expense_inventory} =~ /\Q$accno\E/);

$form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);

if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$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), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
$form->{amount_cogs}{$trans_id}{$accno} = 0;
}
}

foreach my $accno (keys %{ $form->{amount_cogs}{$trans_id} }) {
$form->{amount_cogs}{$trans_id}{$accno} = $form->round_amount($form->{amount_cogs}{$trans_id}{$accno}, 2);

if (!$payments_only && ($form->{amount_cogs}{$trans_id}{$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), $accno, $form->{amount_cogs}{$trans_id}{$accno}, conv_date($form->{invdate}), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
}

foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
next unless ($form->{expense_inventory} =~ /\Q$accno\E/);

$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);

if (!$payments_only && ($form->{amount}{$trans_id}{$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 tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT taxkey_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
$form->{amount}{$trans_id}{$accno} = 0;
}
}

foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);

if (!$payments_only && ($form->{amount}{$trans_id}{$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 tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT taxkey_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
}

# deduct payment differences from diff
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"paid_$i"} != 0) {
$amount =
$form->round_amount($form->{"paid_$i"} * $form->{exchangerate}, 2);
$diff -= $amount - $form->{"paid_$i"} * $form->{exchangerate};
}
}

# record payments and offsetting AR
if (!$form->{storno}) {
for my $i (1 .. $form->{paidaccounts}) {

if ($form->{"acc_trans_id_$i"}
&& $payments_only
&& (SL::DB::Default->get->payments_changeable == 0)) {
next;
}

next if ($form->{"paid_$i"} == 0);

my ($accno) = split(/--/, $form->{"AR_paid_$i"});
$form->{"datepaid_$i"} = $form->{invdate}
unless ($form->{"datepaid_$i"});
$form->{datepaid} = $form->{"datepaid_$i"};

$exchangerate = 0;

if ($form->{currency} eq $defaultcurrency) {
$form->{"exchangerate_$i"} = 1;
} else {
$exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy');
$form->{"exchangerate_$i"} = $exchangerate || $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
}

# record AR
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);

if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 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 tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT taxkey_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, conv_date($taxdate), $form->{AR}, conv_date($taxdate), $project_id, $form->{AR});
do_query($form, $dbh, $query, @values);
}

# 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, gldate, source, memo, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
(SELECT tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT taxkey_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
$gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($taxdate), $accno, conv_date($taxdate), $project_id, $accno);
do_query($form, $dbh, $query, @values);

# exchangerate difference
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;

# gain/loss
$amount =
$form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
$form->{"exchangerate_$i"};
if ($amount > 0) {
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } += $amount;
} else {
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } += $amount;
}

$diff = 0;

# update exchange rate
if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
$form->update_exchangerate($dbh, $form->{currency},
$form->{"datepaid_$i"},
$form->{"exchangerate_$i"}, 0);
}
}

} else { # if (!$form->{storno})
$form->{marge_total} *= -1;
}

IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);

# record exchange rate differences and gains/losses
foreach my $accno (keys %{ $form->{fx} }) {
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
$form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2);
if ( $form->{fx}{$accno}{$transdate} != 0 ) {

$query =
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, tax_id, taxkey, project_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1',
(SELECT tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
(SELECT taxkey_id
FROM taxkeys
WHERE chart_id= (SELECT id
FROM chart
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
?,
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, conv_date($taxdate), $accno, conv_date($taxdate), conv_i($project_id), $accno);
do_query($form, $dbh, $query, @values);
}
}
}

if ($payments_only) {
$query = qq|UPDATE ar SET paid = ? WHERE id = ?|;
do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));

$dbh->commit if !$provided_dbh;

$main::lxdebug->leave_sub();
return;
}

$amount = $netamount + $tax;

# save AR record
#erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb

$query = qq|UPDATE ar set
invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?,
transdate = ?, orddate = ?, quodate = ?, customer_id = ?,
amount = ?, netamount = ?, paid = ?,
duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?,
shipvia = ?, terms = ?, notes = ?, intnotes = ?,
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 = ?,
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"}),
$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',
$form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}), conv_i($form->{"shipto_id"}),
conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}), conv_i($form->{storno_id}), $form->{"storno"} ? '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"}, $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);


if ($form->{storno}) {
$query =
qq!UPDATE ar SET
paid = paid + amount,
storno = 't',
intnotes = ? || intnotes
WHERE id = ?!;
do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_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//;

# add shipto
if (!$form->{shipto_id}) {
$form->add_shipto($dbh, $form->{id}, "AR");
}

# save printed, emailed and queued
$form->save_status($dbh);

Common::webdav_folder($form);

if ($form->{convert_from_ar_ids}) {
RecordLinks->create_links('dbh' => $dbh,
'mode' => 'ids',
'from_table' => 'ar',
'from_ids' => $form->{convert_from_ar_ids},
'to_table' => 'ar',
'to_id' => $form->{id},
);
delete $form->{convert_from_ar_ids};
}

# Link this record to the records it was created from.
RecordLinks->create_links('dbh' => $dbh,
'mode' => 'ids',
'from_table' => 'oe',
'from_ids' => $form->{convert_from_oe_ids},
'to_table' => 'ar',
'to_id' => $form->{id},
);
delete $form->{convert_from_oe_ids};

my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};

if (scalar @convert_from_do_ids) {
DO->close_orders('dbh' => $dbh,
'ids' => \@convert_from_do_ids);

RecordLinks->create_links('dbh' => $dbh,
'mode' => 'ids',
'from_table' => 'delivery_orders',
'from_ids' => \@convert_from_do_ids,
'to_table' => 'ar',
'to_id' => $form->{id},
);
}
delete $form->{convert_from_do_ids};

ARAP->close_orders_if_billed('dbh' => $dbh,
'arap_id' => $form->{id},
'table' => 'ar',);

# 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,
from => $transdate,
to => $transdate,
trans_id => $form->{id},
);

$datev->export;

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;
}

sub _delete_payments {
$main::lxdebug->enter_sub();

my ($self, $form, $dbh) = @_;

my @delete_acc_trans_ids;

# Delete old payment entries from acc_trans.
my $query =
qq|SELECT acc_trans_id
FROM acc_trans
WHERE (trans_id = ?) AND fx_transaction

UNION

SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?) AND (c.link LIKE '%AR_paid%')|;
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}), conv_i($form->{id}));

$query =
qq|SELECT at.acc_trans_id
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
ORDER BY at.acc_trans_id
OFFSET 1|;
push @delete_acc_trans_ids, selectall_array_query($form, $dbh, $query, conv_i($form->{id}));

if (@delete_acc_trans_ids) {
$query = qq|DELETE FROM acc_trans WHERE acc_trans_id IN (| . join(", ", @delete_acc_trans_ids) . qq|)|;
do_query($form, $dbh, $query);
}

$main::lxdebug->leave_sub();
}

sub post_payment {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form, $locale) = @_;

# connect to database, turn off autocommit
my $dbh = $form->get_standard_dbh;

my (%payments, $old_form, $row, $item, $query, %keep_vars);

$old_form = save_form();

# Delete all entries in acc_trans from prior payments.
if (SL::DB::Default->get->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+$|^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);
map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form };

# Retrieve the invoice from the database.
$self->retrieve_invoice($myconfig, $form);

# Set up the content of $form in the way that IS::post_invoice() expects.
$form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate});

for $row (1 .. scalar @{ $form->{invoice_details} }) {
$item = $form->{invoice_details}->[$row - 1];

map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount);

map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item };
}

$form->{rowcount} = scalar @{ $form->{invoice_details} };

delete @{$form}{qw(invoice_details paidaccounts storno paid)};

# Restore the payment options from the user input.
map { $form->{$_} = $payments{$_} } keys %payments;

# Get the AR accno (which is normally done by Form::create_links()).
$query =
qq|SELECT c.accno
FROM acc_trans at
LEFT JOIN chart c ON (at.chart_id = c.id)
WHERE (trans_id = ?)
AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%'))
ORDER BY at.acc_trans_id
LIMIT 1|;

($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));

# Post the new payments.
$self->post_invoice($myconfig, $form, $dbh, 1);

restore_form($old_form);

my $rc = $dbh->commit();

$main::lxdebug->leave_sub();

return $rc;
}

sub process_assembly {
$main::lxdebug->enter_sub();

my ($dbh, $myconfig, $form, $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
FROM assembly a
JOIN parts p ON (a.parts_id = p.id)
WHERE (a.id = ?)|;
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));

while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

my $allocated = 0;

$ref->{inventory_accno_id} *= 1;
$ref->{expense_accno_id} *= 1;

# multiply by number of assemblies
$ref->{qty} *= $totalqty;

if ($ref->{assembly}) {
&process_assembly($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
next;
} else {
if ($ref->{inventory_accno_id}) {
$allocated = &cogs($dbh, $myconfig, $form, $ref->{parts_id}, $ref->{qty});
}
}

# save detail record for individual assembly item in invoice table
$query =
qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|;
my @values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
do_query($form, $dbh, $query, @values);

}

$sth->finish;

$main::lxdebug->leave_sub();
}

sub cogs {
$main::lxdebug->enter_sub();

# 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;

$form->{taxzone_id} *=1;
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, 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
FROM invoice i, parts p
LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)
WHERE (i.parts_id = p.id)
AND (i.parts_id = ?)
AND ((i.base_qty + i.allocated) < 0)
ORDER BY trans_id|;
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id));

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 ( $::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;
$form->{expense_inventory} .= " " . $ref->{expense_accno};
$ref->{inventory_accno} = ($form->{"inventory_accno_$row"}) ? $form->{"inventory_accno_$row"} : $ref->{inventory_accno};
# deduct inventory
$form->{amount_cogs}{ $form->{id} }{ $ref->{inventory_accno} } -= -$linetotal;
$form->{expense_inventory} .= " " . $ref->{inventory_accno};
}

# add allocated
$allocated -= $qty;

last if (($totalqty -= $qty) <= 0);
}

$sth->finish;

$main::lxdebug->leave_sub();

return $allocated;
}

sub reverse_invoice {
$main::lxdebug->enter_sub();

my ($dbh, $form) = @_;

# reverse inventory items
my $query =
qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
WHERE i.trans_id = ?|;
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));

while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

if ($ref->{inventory_accno_id}) {
# de-allocated purchases
$query =
qq|SELECT i.id, i.trans_id, i.allocated
FROM invoice i
WHERE (i.parts_id = ?) AND (i.allocated > 0)
ORDER BY i.trans_id DESC|;
my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));

while (my $inhref = $sth2->fetchrow_hashref('NAME_lc')) {
my $qty = $ref->{qty};
if (($ref->{qty} - $inhref->{allocated}) > 0) {
$qty = $inhref->{allocated};
}

# update invoice
$form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);

last if (($ref->{qty} -= $qty) <= 0);
}
$sth2->finish;
}
}

$sth->finish;

# 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();
}

sub delete_invoice {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database
my $dbh = $form->get_standard_dbh;

&reverse_invoice($dbh, $form);

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 spool files
my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile 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;

if ($rc) {
my $spool = $::lx_office_conf{paths}->{spool};
map { unlink "$spool/$_" if -f "$spool/$_"; } @spoolfiles;
}

$main::lxdebug->leave_sub();

return $rc;
}

sub retrieve_invoice {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database
my $dbh = $form->get_standard_dbh;

my ($sth, $ref, $query);

my $query_transdate = !$form->{id} ? ", current_date AS invdate" : '';

$query =
qq|SELECT
(SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno,
(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
${query_transdate}
FROM defaults d|;

$ref = selectfirst_hashref_query($form, $dbh, $query);
map { $form->{$_} = $ref->{$_} } keys %{ $ref };

if ($form->{id}) {
my $id = conv_i($form->{id});

# retrieve invoice
#erweiterung um das entsprechende feld lieferscheinnummer (a.donumber) in der html-maske anzuzeigen 12.02.2009 jb

$query =
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.shippingpoint, a.shipvia, a.terms, 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.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,
e.name AS employee
FROM ar a
LEFT JOIN employee e ON (e.id = a.employee_id)
WHERE a.id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $id);
map { $form->{$_} = $ref->{$_} } keys %{ $ref };

$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");

foreach my $vc (qw(customer vendor)) {
next if !$form->{"delivery_${vc}_id"};
($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
}

# get printed, emailed
$query = qq|SELECT printed, emailed, spoolfile, formname FROM status WHERE trans_id = ?|;
$sth = prepare_execute_query($form, $dbh, $query, $id);

while ($ref = $sth->fetchrow_hashref('NAME_lc')) {
$form->{printed} .= "$ref->{formname} " if $ref->{printed};
$form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
$form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
}
$sth->finish;
map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);

my $transdate = $form->{deliverydate} ? $dbh->quote($form->{deliverydate})
: $form->{invdate} ? $dbh->quote($form->{invdate})
: "current_date";


my $taxzone_id = $form->{taxzone_id} *= 1;
$taxzone_id = SL::DB::Manager::TaxZone->get_default->id unless SL::DB::Manager::TaxZone->find_by(id => $taxzone_id);

# retrieve individual items
$query =
qq|SELECT
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,

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.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

FROM invoice i
LEFT JOIN parts p ON (i.parts_id = p.id)
LEFT JOIN project pr ON (i.project_id = pr.id)
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id)

LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id)
LEFT JOIN chart c2 ON ((SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c2.id)
LEFT JOIN chart c3 ON ((SELECT tc.expense_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_id) = c3.id)

WHERE (i.trans_id = ?) AND NOT (i.assemblyitem = '1') ORDER BY i.id|;

$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"});

foreach my $type (qw(inventory income expense)) {
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
my $query = qq|SELECT accno, new_chart_id, date($transdate) - valid_from FROM chart WHERE id = ?|;
@$ref{ map $type.$_, qw(_accno _new_chart _valid) } = selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
}
}

# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query =
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber FROM tax t
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE t.id IN
(SELECT tk.tax_id FROM taxkeys tk
WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?)
AND startdate <= date($transdate)
ORDER BY startdate DESC LIMIT 1)
ORDER BY c.accno|;
my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
$ref->{taxaccounts} = "";
my $i=0;
while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {

if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
}
$ref->{taxaccounts} .= "$ptr->{accno} ";

if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
$form->{taxaccounts} .= "$ptr->{accno} ";
}

}

$ref->{qty} *= -1 if $form->{type} eq "credit_note";

chop $ref->{taxaccounts};
push @{ $form->{invoice_details} }, $ref;
$stw->finish;
}
$sth->finish;

Common::webdav_folder($form);
}

my $rc = $dbh->commit;

$main::lxdebug->leave_sub();

return $rc;
}

sub get_customer {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database
my $dbh = $form->get_standard_dbh;

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 $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
$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.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.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);

delete $ref->{salesman_id} if !$ref->{salesman_id};

map { $form->{$_} = $ref->{$_} } keys %$ref;

# use customer currency
$form->{currency} = $form->{curr};

$query =
qq|SELECT sum(amount - paid) AS dunning_amount
FROM ar
WHERE (paid < amount)
AND (customer_id = ?)
AND (dunning_config_id IS NOT NULL)|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
map { $form->{$_} = $ref->{$_} } keys %$ref;

$query =
qq|SELECT dnn.dunning_description AS max_dunning_level
FROM dunning_config dnn
WHERE id IN (SELECT dunning_config_id
FROM ar
WHERE (paid < amount) AND (customer_id = ?) AND (dunning_config_id IS NOT NULL))
ORDER BY dunning_level DESC LIMIT 1|;
$ref = selectfirst_hashref_query($form, $dbh, $query, $cid);
map { $form->{$_} = $ref->{$_} } keys %$ref;

$form->{creditremaining} = $form->{creditlimit};
$query = qq|SELECT SUM(amount - paid) FROM ar WHERE customer_id = ?|;
my ($value) = selectrow_query($form, $dbh, $query, $cid);
$form->{creditremaining} -= $value;

$query =
qq|SELECT o.amount,
(SELECT e.buy FROM exchangerate e
WHERE e.currency_id = o.currency_id
AND e.transdate = o.transdate)
FROM oe o
WHERE o.customer_id = ?
AND o.quotation = '0'
AND o.closed = '0'|;
my $sth = prepare_execute_query($form, $dbh, $query, $cid);

while (my ($amount, $exch) = $sth->fetchrow_array) {
$exch = 1 unless $exch;
$form->{creditremaining} -= $amount * $exch;
}
$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();
}

sub retrieve_item {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

# connect to database
my $dbh = $form->get_standard_dbh;

my $i = $form->{rowcount};

my $where = qq|NOT p.obsolete = '1'|;
my @values;

foreach my $column (qw(p.partnumber p.description pgpartsgroup )) {
my ($table, $field) = split m/\./, $column;
next if !$form->{"${field}_${i}"};
$where .= qq| AND lower(${column}) ILIKE ?|;
push @values, '%' . $form->{"${field}_${i}"} . '%';
}

my (%mm_by_id);
if ($form->{"partnumber_$i"} && !$form->{"description_$i"}) {
$where .= qq| OR (NOT p.obsolete = '1' AND p.ean = ? )|;
push @values, $form->{"partnumber_$i"};

# also search hits in makemodels, but only cache the results by id and merge later
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_ids = map { $_->{parts_id} } @$mm_results;
push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;

if (@mm_ids) {
$where .= qq| OR p.id IN (| . join(',', ('?') x @mm_ids) . qq|)|;
push @values, @mm_ids;
}
}

# Search for part ID overrides all other criteria.
if ($form->{"id_${i}"}) {
$where = qq|p.id = ?|;
@values = ($form->{"id_${i}"});
}

if ($form->{"description_$i"}) {
$where .= qq| ORDER BY p.description|;
} else {
$where .= qq| ORDER BY p.partnumber|;
}

my $transdate;
if ($form->{type} eq "invoice") {
$transdate =
$form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
$form->{invdate} ? $dbh->quote($form->{invdate}) :
"current_date";
} else {
$transdate =
$form->{transdate} ? $dbh->quote($form->{transdate}) :
"current_date";
}

my $taxzone_id = $form->{taxzone_id} * 1;
$taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id);

my $query =
qq|SELECT
p.id, p.partnumber, p.description, p.sellprice,
p.listprice, p.inventory_accno_id, p.lastcost,
p.ean,

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,

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.weight,

pfac.factor AS price_factor,

pg.partsgroup

FROM parts p
LEFT JOIN chart c1 ON
((SELECT inventory_accno_id
FROM buchungsgruppen
WHERE id = p.buchungsgruppen_id) = c1.id)
LEFT JOIN chart c2 ON
((SELECT tc.income_accno_id
FROM taxzone_charts tc
WHERE tc.buchungsgruppen_id = p.buchungsgruppen_id and tc.taxzone_id = ${taxzone_id}) = c2.id)
LEFT JOIN chart c3 ON
((SELECT tc.expense_accno_id
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 price_factors pfac ON (pfac.id = p.price_factor_id)
WHERE $where|;
my $sth = prepare_execute_query($form, $dbh, $query, @values);

my @translation_queries = ( [ qq|SELECT tr.translation, tr.longdescription
FROM translation tr
WHERE tr.language_id = ? AND tr.parts_id = ?| ],
[ qq|SELECT tr.translation, tr.longdescription
FROM translation tr
WHERE tr.language_id IN
(SELECT id
FROM language
WHERE article_code = (SELECT article_code FROM language WHERE id = ?))
AND tr.parts_id = ?
LIMIT 1| ] );
map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;

while (my $ref = $sth->fetchrow_hashref('NAME_lc')) {

if ($mm_by_id{$ref->{id}}) {
$ref->{makemodels} = $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"}) {
push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
}

# 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.
if (!$ref->{inventory_accno_id}) {
map({ delete($ref->{"inventory_${_}"}); } qw(accno new_chart valid));
}
delete($ref->{inventory_accno_id});

foreach my $type (qw(inventory income expense)) {
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
my $query =
qq|SELECT accno, new_chart_id, date($transdate) - valid_from
FROM chart
WHERE id = ?|;
($ref->{"${type}_accno"},
$ref->{"${type}_new_chart"},
$ref->{"${type}_valid"})
= selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
}
}

if ($form->{payment_id} eq "") {
$form->{payment_id} = $form->{part_payment_id};
}

# get tax rates and description
my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
$query =
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber
FROM tax t
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE t.id in
(SELECT tk.tax_id
FROM taxkeys tk
WHERE tk.chart_id = (SELECT id from chart WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC
LIMIT 1)
ORDER BY c.accno|;
@values = ($accno_id, $transdate eq "current_date" ? "now" : $transdate);
my $stw = $dbh->prepare($query);
$stw->execute(@values) || $form->dberror($query);

$ref->{taxaccounts} = "";
my $i = 0;
while (my $ptr = $stw->fetchrow_hashref('NAME_lc')) {

if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
$i++;
$ptr->{accno} = $i;
}
$ref->{taxaccounts} .= "$ptr->{accno} ";

if (!($form->{taxaccounts} =~ /\Q$ptr->{accno}\E/)) {
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
$form->{taxaccounts} .= "$ptr->{accno} ";
}

}

$stw->finish;
chop $ref->{taxaccounts};

if ($form->{language_id}) {
for my $spec (@translation_queries) {
do_statement($form, $spec->[1], $spec->[0], conv_i($form->{language_id}), conv_i($ref->{id}));
my ($translation, $longdescription) = $spec->[1]->fetchrow_array;
next unless $translation;
$ref->{description} = $translation;
$ref->{longdescription} = $longdescription;
last;
}
}

$ref->{onhand} *= 1;

push @{ $form->{item_list} }, $ref;
}
$sth->finish;
$_->[1]->finish for @translation_queries;

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 };
}

$main::lxdebug->leave_sub();
}

sub has_storno {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form, $table) = @_;

$main::lxdebug->leave_sub() and return 0 unless ($form->{id});

# make sure there's no funny stuff in $table
# ToDO: die when this happens and throw an error
$main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);

my $dbh = $form->get_standard_dbh;

my $query = qq|SELECT storno FROM $table WHERE storno_id = ?|;
my ($result) = selectrow_query($form, $dbh, $query, $form->{id});

$main::lxdebug->leave_sub();

return $result;
}

sub is_storno {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form, $table, $id) = @_;

$main::lxdebug->leave_sub() and return 0 unless ($id);

# make sure there's no funny stuff in $table
# ToDO: die when this happens and throw an error
$main::lxdebug->leave_sub() and return 0 if ($table =~ /\W/);

my $dbh = $form->get_standard_dbh;

my $query = qq|SELECT storno FROM $table WHERE id = ?|;
my ($result) = selectrow_query($form, $dbh, $query, $id);

$main::lxdebug->leave_sub();

return $result;
}

sub get_standard_accno_current_assets {
$main::lxdebug->enter_sub();

my ($self, $myconfig, $form) = @_;

my $dbh = $form->get_standard_dbh;

my $query = qq| SELECT accno FROM chart WHERE id = (SELECT ar_paid_accno_id FROM defaults)|;
my ($result) = selectrow_query($form, $dbh, $query);

$main::lxdebug->leave_sub();

return $result;
}

1;
(38-38/77)