use strict;
-sub get_part {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to db
- my $dbh = $form->get_standard_dbh;
-
- my $sth;
-
- my $query =
- qq|SELECT p.*,
- c1.accno AS inventory_accno,
- c2.accno AS income_accno,
- c3.accno AS expense_accno,
- pg.partsgroup
- FROM parts p
- LEFT JOIN chart c1 ON (p.inventory_accno_id = c1.id)
- LEFT JOIN chart c2 ON (p.income_accno_id = c2.id)
- LEFT JOIN chart c3 ON (p.expense_accno_id = c3.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE p.id = ? |;
- my $ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{id}));
-
- # copy to $form variables
- map { $form->{$_} = $ref->{$_} } (keys %{$ref});
-
- $form->{mtime} = $form->{itime} if !$form->{mtime};
- $form->{lastmtime} = $form->{mtime};
- $form->{onhand} *= 1;
-
- # part or service item
- if ($form->{part_type} eq 'assembly') {
-
- # retrieve assembly items
- $query =
- qq|SELECT p.id, p.partnumber, p.description,
- p.sellprice, p.lastcost, p.weight, a.qty, a.bom, p.unit,
- pg.partsgroup, p.price_factor_id, pfac.factor AS price_factor
- FROM parts p
- JOIN assembly a ON (a.parts_id = p.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- LEFT JOIN price_factors pfac ON pfac.id = p.price_factor_id
- WHERE (a.id = ?)
- ORDER BY a.oid|;
- $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-
- $form->{assembly_rows} = 0;
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- $form->{assembly_rows}++;
- foreach my $key (keys %{$ref}) {
- $form->{"${key}_$form->{assembly_rows}"} = $ref->{$key};
- }
- }
- $sth->finish;
-
- }
-
- # setup accno hash for <option checked> {amount} is used in create_links
- $form->{amount}{IC} = $form->{inventory_accno};
- $form->{amount}{IC_income} = $form->{income_accno};
- $form->{amount}{IC_sale} = $form->{income_accno};
- $form->{amount}{IC_expense} = $form->{expense_accno};
- $form->{amount}{IC_cogs} = $form->{expense_accno};
-
- # get prices
- $query = <<SQL;
- SELECT pg.pricegroup, pg.id AS pricegroup_id, COALESCE(pr.price, 0) AS price
- FROM pricegroup pg
- LEFT JOIN prices pr ON (pr.pricegroup_id = pg.id) AND (pr.parts_id = ?)
- ORDER BY lower(pg.pricegroup)
-SQL
-
- my $row = 1;
- foreach $ref (selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}))) {
- $form->{"${_}_${row}"} = $ref->{$_} for qw(pricegroup_id pricegroup price);
- $row++;
- }
- $form->{price_rows} = $row - 1;
-
- # get makes
- if ($form->{makemodel}) {
- #hli
- $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
- qq|WHERE m.parts_id = ? order by m.sortorder asc|;
- my @values = ($form->{id});
- $sth = $dbh->prepare($query);
- $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
-
- my $i = 1;
-
- while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
- $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
- {
- $i++;
- }
- $sth->finish;
- $form->{makemodel_rows} = $i - 1;
-
- }
-
- # get translations
- $query = qq|SELECT language_id, translation, longdescription
- FROM translation
- WHERE parts_id = ?|;
- $form->{translations} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
-
- # is it an orphan
- my @referencing_tables = qw(invoice orderitems inventory);
- my %column_map = ( );
- my $parts_id = conv_i($form->{id});
-
- $form->{orphaned} = 1;
-
- foreach my $table (@referencing_tables) {
- my $column = $column_map{$table} || 'parts_id';
- $query = qq|SELECT $column FROM $table WHERE $column = ? LIMIT 1|;
- my ($found) = selectrow_query($form, $dbh, $query, $parts_id);
-
- if ($found) {
- $form->{orphaned} = 0;
- last;
- }
- }
-
- $form->{"unit_changeable"} = $form->{orphaned};
-
- Common::webdav_folder($form) if $::lx_office_conf{features}{webdav};
-
- $main::lxdebug->leave_sub();
-}
-
sub get_pricegroups {
$main::lxdebug->enter_sub();
$main::lxdebug->leave_sub();
}
-sub save {
- my ($self, $myconfig, $form) = @_;
- $main::lxdebug->enter_sub();
-
- my $rc = SL::DB->client->with_transaction(\&_save, $self, $myconfig, $form);
-
- $main::lxdebug->leave_sub();
- return $rc;
-}
-
-sub _save {
- my ($self, $myconfig, $form) = @_;
- my @values;
-
- my $dbh = SL::DB->client->dbh;
- my $restricter = SL::HTML::Restrict->create;
-
- # save the part
- # make up a unique handle and store in partnumber field
- # then retrieve the record based on the unique handle to get the id
- # replace the partnumber field with the actual variable
- # add records for makemodel
-
- # if there is a $form->{id} then replace the old entry
- # delete all makemodel entries and add the new ones
-
- # undo amount formatting
- map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
- qw(rop weight listprice sellprice gv lastcost);
-
- my $makemodel = ($form->{make_1} || $form->{model_1} || ($form->{makemodel_rows} > 1)) ? 1 : 0;
-
-
- my ($query, $sth);
-
- my $priceupdate = ', priceupdate = current_date';
-
- if ($form->{id}) {
- my $trans_number = SL::TransNumber->new(type => $form->{part_type}, dbh => $dbh, number => $form->{partnumber}, id => $form->{id});
- if (!$trans_number->is_unique) {
- $::lxdebug->leave_sub;
- return 3;
- }
-
- # get old price
- $query = qq|SELECT sellprice FROM parts WHERE id = ?|;
- my ($sellprice) = selectrow_query($form, $dbh, $query, conv_i($form->{id}));
-
- # delete makemodel records
- do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
-
- if ($form->{part_type} eq 'assembly') {
- # delete assembly records
- do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
- }
-
- # delete translations
- do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
-
- # Check whether or not the prices have changed. If they haven't
- # then 'priceupdate' should not be updated.
- my $previous_values = selectfirst_hashref_query($form, $dbh, qq|SELECT * FROM parts WHERE id = ?|, conv_i($form->{id})) || {};
- $priceupdate = '' if (all { $previous_values->{$_} == $form->{$_} } qw(sellprice lastcost listprice));
-
- } else {
- my $trans_number = SL::TransNumber->new(type => $form->{part_type}, dbh => $dbh, number => $form->{partnumber}, save => 1);
-
- if ($form->{partnumber} && !$trans_number->is_unique) {
- $::lxdebug->leave_sub;
- return 3;
- }
-
- $form->{partnumber} ||= $trans_number->create_unique;
-
- ($form->{id}) = selectrow_query($form, $dbh, qq|SELECT nextval('id')|);
- do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber, unit, part_type) VALUES (?, ?, ?, ?)|, $form->{id}, $form->{partnumber}, $form->{unit}, $form->{part_type});
-
- $form->{orphaned} = 1;
- }
- my $partsgroup_id = undef;
-
- if ($form->{partsgroup}) {
- (my $partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
- }
-
- my ($subq_inventory, $subq_expense, $subq_income);
- if ($form->{part_type} eq "part") {
- $subq_inventory =
- qq|(SELECT bg.inventory_accno_id
- FROM buchungsgruppen bg
- WHERE bg.id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq|)|;
- } else {
- $subq_inventory = "NULL";
- }
-
- if ($form->{part_type} ne "assembly") {
- $subq_expense =
- qq|(SELECT tc.expense_accno_id
- FROM taxzone_charts tc
- WHERE tc.buchungsgruppen_id = | . conv_i($form->{"buchungsgruppen_id"}, 'NULL') . qq| and tc.taxzone_id = 0)|;
- } else {
- $subq_expense = "NULL";
- }
-
- normalize_text_blocks();
-
- $query =
- qq|UPDATE parts SET
- partnumber = ?,
- description = ?,
- makemodel = ?,
- listprice = ?,
- sellprice = ?,
- lastcost = ?,
- weight = ?,
- unit = ?,
- notes = ?,
- formel = ?,
- rop = ?,
- warehouse_id = ?,
- bin_id = ?,
- buchungsgruppen_id = ?,
- payment_id = ?,
- inventory_accno_id = $subq_inventory,
- income_accno_id = (SELECT tc.income_accno_id FROM taxzone_charts tc WHERE tc.taxzone_id = 0 and tc.buchungsgruppen_id = ?),
- expense_accno_id = $subq_expense,
- obsolete = ?,
- image = ?,
- drawing = ?,
- shop = ?,
- ve = ?,
- gv = ?,
- ean = ?,
- has_sernumber = ?,
- not_discountable = ?,
- microfiche = ?,
- part_type = ?,
- partsgroup_id = ?,
- price_factor_id = ?
- $priceupdate
- WHERE id = ?|;
- @values = ($form->{partnumber},
- $form->{description},
- $makemodel ? 't' : 'f',
- $form->{listprice},
- $form->{sellprice},
- $form->{lastcost},
- $form->{weight},
- $form->{unit},
- $restricter->process($form->{notes}),
- $form->{formel},
- $form->{rop},
- conv_i($form->{warehouse_id}),
- conv_i($form->{bin_id}),
- conv_i($form->{buchungsgruppen_id}),
- conv_i($form->{payment_id}),
- conv_i($form->{buchungsgruppen_id}),
- $form->{obsolete} ? 't' : 'f',
- $form->{image},
- $form->{drawing},
- $form->{shop} ? 't' : 'f',
- conv_i($form->{ve}),
- conv_i($form->{gv}),
- $form->{ean},
- $form->{has_sernumber} ? 't' : 'f',
- $form->{not_discountable} ? 't' : 'f',
- $form->{microfiche},
- $form->{part_type},
- conv_i($partsgroup_id),
- conv_i($form->{price_factor_id}),
- conv_i($form->{id})
- );
- do_query($form, $dbh, $query, @values);
-
- $form->new_lastmtime('parts');
-
- # delete translation records
- do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
-
- my @translations = grep { $_->{language_id} && $_->{translation} } @{ $form->{translations} || [] };
- if (@translations) {
- $query = qq|INSERT into translation (parts_id, language_id, translation, longdescription)
- VALUES ( ?, ?, ?, ? )|;
- $sth = $dbh->prepare($query);
-
- foreach my $translation (@translations) {
- do_statement($form, $sth, $query, conv_i($form->{id}), conv_i($translation->{language_id}), $translation->{translation}, $restricter->process($translation->{longdescription}));
- }
-
- $sth->finish();
- }
-
- # delete price records
- do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id}));
-
- $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) VALUES(?, ?, ?)|;
- $sth = prepare_query($form, $dbh, $query);
-
- for my $i (1 .. $form->{price_rows}) {
- my $price = $form->parse_amount($myconfig, $form->{"price_$i"});
- next unless $price;
-
- @values = (conv_i($form->{id}), conv_i($form->{"pricegroup_id_$i"}), $price);
- do_statement($form, $sth, $query, @values);
- }
-
- $sth->finish;
-
- # insert makemodel records
- my $lastupdate = '';
- my $value = 0;
- for my $i (1 .. $form->{makemodel_rows}) {
- if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
- #hli
- $value = $form->parse_amount($myconfig, $form->{"lastcost_$i"});
- if ($value == $form->parse_amount($myconfig, $form->{"old_lastcost_$i"}))
- {
- if ($form->{"lastupdate_$i"} eq "") {
- $lastupdate = 'now()';
- } else {
- $lastupdate = $dbh->quote($form->{"lastupdate_$i"});
- }
- } else {
- $lastupdate = 'now()';
- }
- $query = qq|INSERT INTO makemodel (parts_id, make, model, lastcost, lastupdate, sortorder) | .
- qq|VALUES (?, ?, ?, ?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"}, $value, $lastupdate, conv_i($form->{"sortorder_$i"}) );
-
- do_query($form, $dbh, $query, @values);
- }
- }
-
- # add assembly records
- if ($form->{part_type} eq 'assembly') {
- # check additional assembly row
- my $i = $form->{assembly_rows};
- # if last row is not empty add them
- if ($form->{"partnumber_$i"} ne "") {
- $query = qq|SELECT id FROM parts WHERE partnumber = ?|;
- my ($partid) = selectrow_query($form, $dbh, $query,$form->{"partnumber_$i"} );
- if ( $partid ) {
- $form->{"qty_$i"} = 1 unless ($form->{"qty_$i"});
- $form->{"id_$i"} = $partid;
- $form->{"bom_$i"} = 0;
- $form->{assembly_rows}++;
- }
- else {
- $::form->error($::locale->text("uncorrect partnumber ").$form->{"partnumber_$i"});
- }
- }
-
- for my $i (1 .. $form->{assembly_rows}) {
- $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
-
- if ($form->{"qty_$i"} != 0) {
- $form->{"bom_$i"} *= 1;
- $query = qq|INSERT INTO assembly (id, parts_id, qty, bom) | .
- qq|VALUES (?, ?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"id_$i"}), conv_i($form->{"qty_$i"}), $form->{"bom_$i"} ? 't' : 'f');
- do_query($form, $dbh, $query, @values);
- }
- }
- my @a = localtime;
- $a[5] += 1900;
- $a[4]++;
- my $shippingdate = "$a[5]-$a[4]-$a[3]";
-
- $form->get_employee($dbh);
-
- }
-
- #set expense_accno=inventory_accno if they are different => bilanz
- my $vendor_accno =
- ($form->{expense_accno} != $form->{inventory_accno})
- ? $form->{inventory_accno}
- : $form->{expense_accno};
-
- # get tax rates and description
- my $accno_id =
- ($form->{vc} eq "customer") ? $form->{income_accno} : $vendor_accno;
- $query =
- qq|SELECT c.accno, c.description, t.rate, t.taxnumber
- FROM chart c, tax t
- WHERE (c.id = t.chart_id) AND (t.taxkey IN (SELECT taxkey_id FROM chart where accno = ?))
- ORDER BY c.accno|;
- my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
-
- $form->{taxaccount} = "";
- while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
- $form->{taxaccount} .= "$ptr->{accno} ";
- if (!($form->{taxaccount2} =~ /\Q$ptr->{accno}\E/)) {
- $form->{"$ptr->{accno}_rate"} = $ptr->{rate};
- $form->{"$ptr->{accno}_description"} = $ptr->{description};
- $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber};
- $form->{taxaccount2} .= " $ptr->{accno} ";
- }
- }
-
- CVar->save_custom_variables(dbh => $dbh,
- module => 'IC',
- trans_id => $form->{id},
- variables => $form,
- save_validity => 1);
-
- # Delete saved custom variable values for configs that have been
- # marked invalid for this part.
- $query = <<SQL;
- DELETE FROM custom_variables
- WHERE (config_id IN (
- SELECT val.config_id
- FROM custom_variables_validity val
- LEFT JOIN custom_variable_configs val_cfg ON (val.config_id = val_cfg.id)
- WHERE (val_cfg.module = 'IC')
- AND (val.trans_id = ?)))
- AND (trans_id = ?)
-SQL
- do_query($form, $dbh, $query, ($form->{id}) x 2);
-
- return 1;
-}
-
sub retrieve_assemblies {
$main::lxdebug->enter_sub();
$main::lxdebug->leave_sub();
}
-sub delete {
- my ($self, $myconfig, $form) = @_;
- $main::lxdebug->enter_sub();
-
- my $rc = SL::DB->client->with_transaction(\&_delete, $self, $myconfig, $form);
-
- $main::lxdebug->leave_sub();
- return $rc;
-}
-
-sub _delete {
- my ($self, $myconfig, $form) = @_;
- my @values = (conv_i($form->{id}));
-
- my %columns = ( "assembly" => "id", "parts" => "id" );
-
- for my $table (qw(prices makemodel inventory assembly translation parts)) {
- my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
- do_query($form, SL::DB->client->dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
- }
-
- return 1;
-}
-
sub assembly_item {
$main::lxdebug->enter_sub();
return $num_updated;
}
-sub create_links {
- $main::lxdebug->enter_sub();
-
- my ($self, $module, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->get_standard_dbh;
-
- my @values = like($module);
- my $query;
-
- if ($form->{id}) {
- $query =
- qq|SELECT c.accno, c.description, c.link, c.id,
- p.inventory_accno_id, p.income_accno_id, p.expense_accno_id
- FROM chart c, parts p
- WHERE (c.link LIKE ?) AND (p.id = ?)
- ORDER BY c.accno|;
- push(@values, conv_i($form->{id}));
-
- } else {
- $query =
- qq|SELECT c.accno, c.description, c.link, c.id,
- d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
- FROM chart c, defaults d
- WHERE c.link LIKE ?
- ORDER BY c.accno|;
- }
-
- my $sth = prepare_execute_query($form, $dbh, $query, @values);
- while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
- foreach my $key (split(/:/, $ref->{link})) {
- if ($key =~ /\Q$module\E/) {
- if ( ($ref->{id} eq $ref->{inventory_accno_id})
- || ($ref->{id} eq $ref->{income_accno_id})
- || ($ref->{id} eq $ref->{expense_accno_id})) {
- push @{ $form->{"${module}_links"}{$key} },
- { accno => $ref->{accno},
- description => $ref->{description},
- selected => "selected" };
- $form->{"${key}_default"} = "$ref->{accno}--$ref->{description}";
- } else {
- push @{ $form->{"${module}_links"}{$key} },
- { accno => $ref->{accno},
- description => $ref->{description},
- selected => "" };
- }
- }
- }
- }
- $sth->finish;
-
- # get buchungsgruppen
- $form->{BUCHUNGSGRUPPEN} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM buchungsgruppen|);
-
- # get payment terms
- $form->{payment_terms} = selectall_hashref_query($form, $dbh, qq|SELECT id, description FROM payment_terms ORDER BY sortkey|);
-
- if (!$form->{id}) {
- ($form->{priceupdate}) = selectrow_query($form, $dbh, qq|SELECT current_date|);
- }
-
- $main::lxdebug->leave_sub();
-}
-
# get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100
sub get_parts {
$main::lxdebug->enter_sub();
return $sum;
} #end get_soldtotal
-sub retrieve_languages {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form) = @_;
-
- # connect to database
- my $dbh = $form->get_standard_dbh;
-
- my @values;
- my $where;
- my $query;
-
- if ($form->{language_values} ne "") {
- $query =
- qq|SELECT l.id, l.description, tr.translation, tr.longdescription
- FROM language l
- LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)
- ORDER BY lower(l.description)|;
- @values = (conv_i($form->{id}));
-
- } else {
- $query = qq|SELECT id, description
- FROM language
- ORDER BY lower(description)|;
- }
-
- my $languages = selectall_hashref_query($form, $dbh, $query, @values);
-
- $main::lxdebug->leave_sub();
-
- return $languages;
-}
-
sub follow_account_chain {
$main::lxdebug->enter_sub(2);
projects / kivitendo-erp.git / blobdiff