# GNU General Public License for more details.
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
-# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
+# MA 02110-1335, USA.
#======================================================================
#
# Inventory received module
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;
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) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_post_invoice, $self, $myconfig, $form, $provided_dbh, $payments_only);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _post_invoice {
my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_;
- # connect to database, turn off autocommit
- my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig);
+ my $dbh = $provided_dbh || SL::DB->client->dbh;
my $restricter = SL::HTML::Restrict->create;
$form->{defaultcurrency} = $form->get_default_currency($myconfig);
dbh => $dbh);
# link previous items with invoice items See IS.pm (no credit note -> no invoice item)
- foreach (qw(delivery_order_items orderitems)) {
+ foreach (qw(delivery_order_items orderitems invoice)) {
if (!$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
RecordLinks->create_links('dbh' => $dbh,
'mode' => 'ids',
if ($payments_only) {
$query = qq|UPDATE ap SET paid = ? WHERE id = ?|;
do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
+ $form->new_lastmtime('ap');
- if (!$provided_dbh) {
- $dbh->commit();
- $dbh->disconnect();
- }
-
- $main::lxdebug->leave_sub();
return;
}
invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?,
intnotes = ?, storno_id = ?, storno = ?,
cp_id = ?, employee_id = ?, department_id = ?, delivery_term_id = ?,
+ currency_id = (SELECT id FROM currencies WHERE name = ?),
globalproject_id = ?, direct_debit = ?
WHERE id = ?|;
@values = (
'1', $taxzone_id, $restricter->process($form->{notes}), $form->{taxincluded} ? 't' : 'f',
$form->{intnotes}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), conv_i($form->{delivery_term_id}),
+ $form->{"currency"},
conv_i($form->{globalproject_id}),
$form->{direct_debit} ? 't' : 'f',
conv_i($form->{id})
do_query($form, $dbh, $query, conv_i($form->{id}));
}
+ $form->new_lastmtime('ap');
$form->{name} = $form->{vendor};
$form->{name} =~ s/--\Q$form->{vendor_id}\E//;
Common::webdav_folder($form);
- # Link this record to the records it was created from.
- if ($form->{convert_from_oe_ids}) {
- RecordLinks->create_links('dbh' => $dbh,
- 'mode' => 'ids',
- 'from_table' => 'oe',
- 'from_ids' => $form->{convert_from_oe_ids},
- 'to_table' => 'ap',
- 'to_id' => $form->{id},
+ # Link this record to the records it was created from order or invoice (storno)
+ foreach (qw(oe ap)) {
+ if ($form->{"convert_from_${_}_ids"}) {
+ RecordLinks->create_links('dbh' => $dbh,
+ 'mode' => 'ids',
+ 'from_table' => $_,
+ 'from_ids' => $form->{"convert_from_${_}_ids"},
+ 'to_table' => 'ap',
+ 'to_id' => $form->{id},
);
- delete $form->{convert_from_oe_ids};
+ delete $form->{"convert_from_${_}_ids"};
+ }
}
my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
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) {
- $dbh->rollback;
die join "\n", $::locale->text('DATEV check returned errors:'), $datev->errors;
}
}
- my $rc = 1;
- if (!$provided_dbh) {
- $rc = $dbh->commit();
- $dbh->disconnect();
- }
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub reverse_invoice {
# reverse inventory items
my $query =
- qq|SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id, i.qty, i.allocated, i.sellprice
+ qq|SELECT i.parts_id, p.part_type, i.qty, i.allocated, i.sellprice
FROM invoice i, parts p
WHERE (i.parts_id = p.id)
AND (i.trans_id = ?)|;
while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
$netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);
- next unless $ref->{inventory_accno_id};
+ next unless $ref->{part_type} eq 'part';
# if $ref->{allocated} > 0 than we sold that many items
next if ($ref->{allocated} <= 0);
my ($self, $myconfig, $form) = @_;
my $query;
# connect to database
- my $dbh = $form->dbconnect_noauto($myconfig);
-
- &reverse_invoice($dbh, $form);
+ my $dbh = SL::DB->client->dbh;
- my @values = (conv_i($form->{id}));
+ SL::DB->client->with_transaction(sub{
- # delete zero entries
- # wtf? use case for this?
- $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
- do_query($form, $dbh, $query);
+ &reverse_invoice($dbh, $form);
+ my @values = (conv_i($form->{id}));
- my @queries = (
- qq|DELETE FROM invoice WHERE trans_id = ?|,
- qq|DELETE FROM ap WHERE id = ?|,
- );
+ # delete zero entries
+ # wtf? use case for this?
+ $query = qq|DELETE FROM acc_trans WHERE amount = 0|;
+ do_query($form, $dbh, $query);
- map { do_query($form, $dbh, $_, @values) } @queries;
- my $rc = $dbh->commit;
- $dbh->disconnect;
+ my @queries = (
+ qq|DELETE FROM invoice WHERE trans_id = ?|,
+ qq|DELETE FROM ap WHERE id = ?|,
+ );
- $main::lxdebug->leave_sub();
+ map { do_query($form, $dbh, $_, @values) } @queries;
+ 1;
+ }) or do { die SL::DB->client->error };
- return $rc;
+ return 1;
}
sub retrieve_invoice {
my ($self, $myconfig, $form) = @_;
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my ($query, $sth, $ref, $q_invdate);
map { $form->{$_} = $ref->{$_} } keys %$ref;
if (!$form->{id}) {
- $dbh->disconnect();
$main::lxdebug->leave_sub();
return;
i.id AS invoice_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.inventory_accno_id AS part_inventory_accno_id, pr.projectnumber, pg.partsgroup
+ p.partnumber, p.part_type, pr.projectnumber, pg.partsgroup
+ ,p.classification_id
FROM invoice i
JOIN parts p ON (i.parts_id = p.id)
);
map { $ref->{"ic_cvar_$_->{name}"} = $_->{value} } @{ $cvars };
- map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_inventory_accno_id"};
- delete($ref->{"part_inventory_accno_id"});
+ map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid)) if !$ref->{"part_type"} eq 'part';
foreach my $type (qw(inventory income expense)) {
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
Common::webdav_folder($form);
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
}
$params = $form unless defined $params && ref $params eq "HASH";
# connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my $dateformat = $myconfig->{dateformat};
$dateformat .= "yy" if $myconfig->{dateformat} !~ /^y/;
}
$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});
- }
-
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my $i = $form->{rowcount};
# don't include assemblies or obsolete parts
- my $where = "NOT p.assembly = '1' AND NOT p.obsolete = '1'";
+ my $where = "NOT p.part_type = 'assembly' AND NOT p.obsolete = '1'";
my @values;
foreach my $table_column (qw(p.partnumber p.description pg.partsgroup)) {
my $field = (split m{\.}, $table_column)[1];
next unless $form->{"${field}_${i}"};
$where .= " AND lower(${table_column}) LIKE lower(?)";
- push @values, '%' . $form->{"${field}_${i}"} . '%';
+ push @values, like($form->{"${field}_${i}"});
}
my (%mm_by_id);
LEFT JOIN parts ON parts.id = parts_id
WHERE NOT parts.obsolete AND model ILIKE ? AND (make IS NULL OR make = ?);
|;
- my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, '%' . $form->{"partnumber_$i"} . '%', $::form->{vendor_id});
+ my $mm_results = selectall_hashref_query($::form, $dbh, $mm_query, like($form->{"partnumber_$i"}), $::form->{vendor_id});
my @mm_ids = map { $_->{parts_id} } @$mm_results;
push @{$mm_by_id{ $_->{parts_id} } ||= []}, $_ for @$mm_results;
my $query =
qq|SELECT
p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
- p.unit, p.assembly, p.onhand, p.formel,
+ p.unit, p.part_type, p.onhand, p.formel,
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
- p.inventory_accno_id, p.price_factor_id,
+ 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}}) {
push @{ $ref->{matches} ||= [] }, $::locale->text('Model') . ': ' . join ', ', map { $_->{model} } @{ $mm_by_id{$ref->{id}} };
}
- if ($ref->{ean} eq $::form->{"partnumber_$i"}) {
+ if (($::form->{"partnumber_$i"} ne '') && ($ref->{ean} eq $::form->{"partnumber_$i"})) {
push @{ $ref->{matches} ||= [] }, $::locale->text('EAN') . ': ' . $ref->{ean};
}
+ $ref->{type_and_classific} = $::request->presenter->type_abbreviation($ref->{part_type}).
+ $::request->presenter->classification_abbreviation($ref->{classification_id});
+ if (! $ref->{used_for_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.
$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 };
}
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form, @wanted_vars) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my @values;
LIMIT 1|;
my $ref = selectfirst_hashref_query($form, $dbh, $query, $form->{vendor_id}, @values);
- # remove id and taxincluded before copy back
- delete @$ref{qw(id taxincluded)};
+ # remove id,notes (double of vendornotes) and taxincluded before copy back
+ delete @$ref{qw(id taxincluded notes)};
@wanted_vars = grep({ $_ } @wanted_vars);
if (scalar(@wanted_vars) > 0) {
'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);
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
}
my ($self, $myconfig, $form) = @_;
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
+ my $dbh = SL::DB->client->dbh;
my $query =
qq|SELECT accno, description, link
}
$sth->finish();
- $dbh->disconnect();
-
$main::lxdebug->leave_sub();
}
}
sub post_payment {
+ my ($self, $myconfig, $form, $locale) = @_;
$main::lxdebug->enter_sub();
+ my $rc = SL::DB->client->with_transaction(\&_post_payment, $self, $myconfig, $form, $locale);
+
+ $::lxdebug->leave_sub;
+ return $rc;
+}
+
+sub _post_payment {
my ($self, $myconfig, $form, $locale) = @_;
- # connect to database, turn off autocommit
- my $dbh = $form->dbconnect_noauto($myconfig);
+ my $dbh = SL::DB->client->dbh;
my (%payments, $old_form, $row, $item, $query, %keep_vars);
restore_form($old_form);
- my $rc = $dbh->commit();
- $dbh->disconnect();
-
- $main::lxdebug->leave_sub();
-
- return $rc;
+ return 1;
}
sub get_duedate {