use SL::CVar;
use SL::DATEV qw(:CONSTANTS);
use SL::DBUtils;
+use SL::DB::Draft;
use SL::DO;
use SL::GenericTranslations;
use SL::HTML::Restrict;
use SL::MoreCommon;
use SL::DB::Default;
use SL::DB::TaxZone;
+use SL::DB::MakeModel;
use SL::DB;
+use SL::Presenter::Part qw(type_abbreviation classification_abbreviation);
use List::Util qw(min);
use strict;
+use constant PCLASS_OK => 0;
+use constant PCLASS_NOTFORSALE => 1;
+use constant PCLASS_NOTFORPURCHASE => 2;
sub post_invoice {
- my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
+ my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
$main::lxdebug->enter_sub();
- my $rc = SL::DB->client->with_transaction(\&_post_invoice, $self, $myconfig, $form, $provided_dbh, $payments_only);
+ my $rc = SL::DB->client->with_transaction(\&_post_invoice, $self, $myconfig, $form, $provided_dbh, %params);
$::lxdebug->leave_sub;
return $rc;
}
sub _post_invoice {
- my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
+ my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
+ my $payments_only = $params{payments_only};
my $dbh = $provided_dbh || SL::DB->client->dbh;
my $restricter = SL::HTML::Restrict->create;
if ($form->{currency} ne $defaultcurrency) && !$exchangerate;
# record acc_trans transactions
+ my $taxdate = $form->{tax_point} || $form->{deliverydate} || $form->{invdate};
foreach my $trans_id (keys %{ $form->{amount} }) {
foreach my $accno (keys %{ $form->{amount}{$trans_id} }) {
$form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2);
ORDER BY startdate DESC LIMIT 1),
(SELECT link FROM chart WHERE accno = ?))|;
@values = ($trans_id, $accno, $form->{amount}{$trans_id}{$accno},
- conv_date($form->{invdate}), $accno, conv_date($form->{invdate}), $project_id, $accno, conv_date($form->{invdate}), $accno);
+ conv_date($form->{invdate}), $accno, conv_date($taxdate), $project_id, $accno, conv_date($taxdate), $accno);
do_query($form, $dbh, $query, @values);
}
}
$form->{amount}{ $form->{id} }{ $form->{AP} } = $form->{paid} if $form->{amount}{$form->{id}}{$form->{AP}} == 0;
+ my %already_cleared = %{ $params{already_cleared} // {} };
+
# record payments and offsetting AP
for my $i (1 .. $form->{paidaccounts}) {
if ($form->{"acc_trans_id_$i"}
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $paiddiff, 2) * -1;
+ my $new_cleared = !$form->{"acc_trans_id_$i"} ? 'f'
+ : !$already_cleared{$form->{"acc_trans_id_$i"}} ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{amount} != $form->{"paid_$i"} ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{accno} != $accno ? 'f'
+ : $already_cleared{$form->{"acc_trans_id_$i"}}->{cleared} ? 't'
+ : 'f';
+
# record AP
if ($form->{amount}{ $form->{id} }{ $form->{AP} } != 0) {
- $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, tax_id, chart_link)
+ $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id, cleared, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?,
(SELECT taxkey_id
FROM taxkeys
WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- ?,
+ ?, ?,
(SELECT tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
ORDER BY startdate DESC LIMIT 1),
(SELECT link FROM chart WHERE accno = ?))|;
@values = (conv_i($form->{id}), $form->{AP}, $amount,
- $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP});
+ $form->{"datepaid_$i"}, $form->{AP}, conv_date($form->{"datepaid_$i"}), $project_id, $new_cleared, $form->{AP}, conv_date($form->{"datepaid_$i"}), $form->{AP});
do_query($form, $dbh, $query, @values);
}
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, taxkey, project_id, tax_id, chart_link)
+ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo, taxkey, project_id, cleared, tax_id, chart_link)
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?,
(SELECT taxkey_id
FROM taxkeys
FROM chart WHERE accno = ?)
AND startdate <= ?
ORDER BY startdate DESC LIMIT 1),
- ?,
+ ?, ?,
(SELECT tax_id
FROM taxkeys
WHERE chart_id= (SELECT id
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($form->{"datepaid_$i"}), $project_id, $accno, conv_date($form->{"datepaid_$i"}), $accno);
+ $gldate, $form->{"source_$i"}, $form->{"memo_$i"}, $accno, conv_date($form->{"datepaid_$i"}), $project_id, $new_cleared, $accno, conv_date($form->{"datepaid_$i"}), $accno);
do_query($form, $dbh, $query, @values);
$exchangerate = 0;
# save AP record
$query = qq|UPDATE ap SET
- invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?,
- orddate = ?, quodate = ?, vendor_id = ?, amount = ?,
- netamount = ?, paid = ?, duedate = ?,
- invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
- intnotes = ?, storno_id = ?, storno = ?,
+ invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?,
+ orddate = ?, quodate = ?, vendor_id = ?, amount = ?,
+ netamount = ?, paid = ?, duedate = ?, deliverydate = ?,
+ invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
+ intnotes = ?, storno_id = ?, storno = ?, tax_point = ?,
cp_id = ?, employee_id = ?, department_id = ?, delivery_term_id = ?,
+ payment_id = ?, transaction_description = ?,
currency_id = (SELECT id FROM currencies WHERE name = ?),
globalproject_id = ?, direct_debit = ?
WHERE id = ?|;
@values = (
$form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, conv_date($form->{invdate}),
conv_date($form->{orddate}), conv_date($form->{quodate}), conv_i($form->{vendor_id}), $amount,
- $netamount, $form->{paid}, conv_date($form->{duedate}),
+ $netamount, $form->{paid}, conv_date($form->{duedate}), conv_date($form->{deliverydate}),
'1', $taxzone_id, $restricter->process($form->{notes}), $form->{taxincluded} ? 't' : 'f',
- $form->{intnotes}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
+ $form->{intnotes}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', conv_date($form->{tax_point}),
conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{delivery_term_id}),
+ conv_i($form->{payment_id}), $form->{transaction_description},
$form->{"currency"},
conv_i($form->{globalproject_id}),
$form->{direct_debit} ? 't' : 'f',
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_purchase_invoice) {
- # if we need department for kostenstelle in DATEV check
- $form->{department} = SL::DB::Manager::Department->find_by(id => $form->{department_id})->description if $form->{department_id};
- 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) {
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
# retrieve invoice
$query = qq|SELECT cp_id, invnumber, transdate AS invdate, duedate,
- orddate, quodate, globalproject_id,
+ orddate, quodate, deliverydate, tax_point, globalproject_id,
ordnumber, quonumber, paid, taxincluded, notes, taxzone_id, storno, gldate,
mtime, itime,
intnotes, (SELECT cu.name FROM currencies cu WHERE cu.id=ap.currency_id) AS currency, direct_debit,
- delivery_term_id
+ payment_id, delivery_term_id, transaction_description
FROM ap
WHERE id = ?|;
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
delete $ref->{id};
map { $form->{$_} = $ref->{$_} } keys %$ref;
- my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
+ my $transdate = $form->{tax_point} ? $dbh->quote($form->{tax_point}) :$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);
i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, i.parts_id AS id, i.unit, i.deliverydate, i.project_id, i.serialnumber,
i.price_factor_id, i.price_factor, i.marge_price_factor, i.discount, i.active_price_source, i.active_discount_source,
p.partnumber, p.part_type, pr.projectnumber, pg.partsgroup
+ ,p.classification_id
FROM invoice i
JOIN parts p ON (i.parts_id = p.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
+ qq|SELECT c.accno, t.taxdescription, t.rate, t.id as tax_id,
+ c.accno as taxnumber -- taxnumber is same as accno, but still accessed as taxnumber in code
+ FROM tax t
LEFT JOIN chart c ON (c.id = t.chart_id)
WHERE t.id in
(SELECT tk.tax_id FROM taxkeys tk
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
+ $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
$form->{taxaccounts} .= "$ptr->{accno} ";
}
}
$sth->finish();
- if (!$params->{id} && $params->{type} !~ /_(order|quotation)/) {
- # setup last accounts used
- $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 ap a ON (a.id = ac.trans_id)
- WHERE (a.vendor_id = ?)
- AND (NOT ((c.link LIKE '%_tax%') OR (c.link LIKE '%_paid%')))
- AND (a.id IN (SELECT max(a2.id) FROM ap a2 WHERE a2.vendor_id = ?))|;
- my $refs = selectall_hashref_query($form, $dbh, $query, $vid, $vid);
-
- my $i = 0;
- for $ref (@$refs) {
- if ($ref->{category} eq 'E') {
- $i++;
- my ($tax_id, $rate);
- if ($params->{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 <= ?)
- ORDER BY tk.startdate DESC
- LIMIT 1|;
- ($tax_id, $rate) = selectrow_query($form, $dbh, $tax_query, $ref->{id}, $params->{initial_transdate});
- $params->{"taxchart_$i"} = "${tax_id}--${rate}";
- }
-
- $params->{"AP_amount_$i"} = "$ref->{accno}--$tax_id";
- }
-
- if ($ref->{category} eq 'L') {
- $params->{APselected} = $params->{AP_1} = $ref->{accno};
- }
- }
- $params->{rowcount} = $i if ($i && !$params->{type});
- }
-
$main::lxdebug->leave_sub();
}
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
p.price_factor_id,
p.ean,
+ p.classification_id,
pfac.factor AS price_factor,
c3.new_chart_id AS expense_new_chart,
date($transdate) - c3.valid_from AS expense_valid,
+ pt.used_for_purchase AS used_for_purchase,
pg.partsgroup
FROM parts p
FROM taxzone_charts tc
WHERE tc.taxzone_id = '$taxzone_id' and tc.buchungsgruppen_id = p.buchungsgruppen_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);
map { push @{ $_ }, prepare_query($form, $dbh, $_->[0]) } @translation_queries;
$form->{item_list} = [];
+ my $has_wrong_pclass = PCLASS_OK;
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
if ($mm_by_id{$ref->{id}}) {
if (($::form->{"partnumber_$i"} ne '') && ($ref->{ean} eq $::form->{"partnumber_$i"})) {
push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
}
+ $ref->{type_and_classific} = type_abbreviation($ref->{part_type}) .
+ classification_abbreviation($ref->{classification_id});
+ if (! $ref->{used_for_purchase} ) {
+ $has_wrong_pclass = PCLASS_NOTFORPURCHASE;
+ 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.
# 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
+ qq|SELECT c.accno, t.taxdescription, t.rate, c.accno as taxnumber, t.id as tax_id
FROM tax t
LEFT JOIN chart c on (c.id = t.chart_id)
WHERE t.id IN
$form->{"$ptr->{accno}_rate"} = $ptr->{rate};
$form->{"$ptr->{accno}_description"} = $ptr->{taxdescription};
$form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
+ $form->{"$ptr->{accno}_tax_id"} = $ptr->{tax_id};
$form->{taxaccounts} .= "$ptr->{accno} ";
}
chop $ref->{taxaccounts};
$ref->{onhand} *= 1;
-
push @{ $form->{item_list} }, $ref;
}
$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 };
}
'trans_id' => $form->{vendor_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'),
'allow_fallback' => 1);
$old_form = save_form();
+ $query = <<SQL;
+ SELECT at.acc_trans_id, at.amount, at.cleared, c.accno
+ FROM acc_trans at
+ LEFT JOIN chart c ON (at.chart_id = c.id)
+ WHERE (at.trans_id = ?)
+SQL
+
+ my %already_cleared = selectall_as_map($form, $dbh, $query, 'acc_trans_id', [ qw(amount cleared accno) ], $form->{id});
+
# Delete all entries in acc_trans from prior payments.
if (SL::DB::Default->get->payments_changeable != 0) {
$self->_delete_payments($form, $dbh);
($form->{AP}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id}));
# Post the new payments.
- $self->post_invoice($myconfig, $form, $dbh, 1);
+ $self->post_invoice($myconfig, $form, $dbh, payments_only => 1, already_cleared => \%already_cleared);
restore_form($old_form);