Projekt

Allgemein

Profil

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

kivitendo/SL/IS.pm @ 5d5a1646

#=====================================================================
# 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., 51 Franklin Street, Fifth Floor, Boston,
# MA 02110-1335, USA.
#======================================================================
#
# Inventory invoicing module
#
#======================================================================

package IS;

use List::Util qw(max sum0);
use List::MoreUtils qw(any);

use Carp;
use Data::Dumper;

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::Chart;
use SL::DB::Customer;
use SL::DB::Default;
use SL::DB::Draft;
use SL::DB::Tax;
use SL::DB::TaxZone;
use SL::TransNumber;
use SL::DB;
use SL::Presenter::Part qw(type_abbreviation classification_abbreviation);
use SL::Helper::QrBillFunctions qw(get_qrbill_account assemble_ref_number);

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

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

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} = [];

# get some values of parts from db on store them in extra array,
# 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');

my @arrays =
qw(runningnumber number description longdescription qty qty_nofmt unit bin
deliverydate_oe ordnumber_oe donumber_do transdate_oe invnumber invdate
partnotes serialnumber reqdate sellprice sellprice_nofmt listprice listprice_nofmt netprice netprice_nofmt
discount discount_nofmt p_discount discount_sub discount_sub_nofmt nodiscount_sub nodiscount_sub_nofmt
linetotal linetotal_nofmt nodiscount_linetotal nodiscount_linetotal_nofmt tax_rate projectnumber projectdescription
price_factor price_factor_name partsgroup weight weight_nofmt lineweight lineweight_nofmt);

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

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

my @invoices_for_advance_payment_arrays = qw(iap_invnumber iap_transdate
iap_amount iap_amount_nofmt
iap_taxamount iap_taxamount_nofmt
iap_open_amount iap_open_amount_nofmt
iap_netamount);

map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays, @payment_arrays, @prepared_arrays, @invoices_for_advance_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}->{entry_type} }, 'partsgroup');
push(@{ $form->{TEMPLATE_ARRAYS}->{description} }, qq|$item->[1]|);
$sameitem = $item->[1];

map({ push(@{ $form->
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}; # don't use this anymore
$form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
$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;

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

$form->{$_} = $ref->{$_} for grep { $_ ne 'id' } keys %$ref;

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

Common::webdav_folder($form);
}

return 1;
}

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, $ref, $query);

my $cid = conv_i($form->{customer_id});
my $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.pricegroup_id as customer_pricegroup_id, c.taxzone_id, c.salesman_id, cu.name AS curr,
c.taxincluded_checked, c.direct_debit,
(SELECT aba.id
FROM additional_billing_addresses aba
WHERE aba.default_address
LIMIT 1) AS default_billing_address_id,
b.discount AS tradediscount, b.description AS business
FROM customer c
LEFT JOIN business b ON (b.id = c.business_id)
LEFT JOIN currencies cu ON (c.currency_id=cu.id)
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 !$ref->{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};

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

$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, like($form->{"${field}_${i}"});
}

my