+ while (2 <= scalar(@_)) {
+ my $qty = shift(@_);
+ my $unit = $units->{shift(@_)};
+
+ croak "No unit defined with name $unit" if (!defined $unit);
+
+ if (!$base_unit) {
+ $base_unit = $unit->{base_unit};
+ } elsif ($base_unit ne $unit->{base_unit}) {
+ croak "Adding values with incompatible base units $base_unit/$unit->{base_unit}";
+ }
+
+ $sum += $qty * $unit->{factor};
+ }
+
+ $main::lxdebug->leave_sub();
+
+ return wantarray ? ($sum, $base_unit) : $sum;
+}
+
+sub add_unit {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form, $name, $base_unit, $factor, $languages) = @_;
+
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ my $query = qq|SELECT COALESCE(MAX(sortkey), 0) + 1 FROM units|;
+ my ($sortkey) = selectrow_query($form, $dbh, $query);
+
+ $query = "INSERT INTO units (name, base_unit, factor, sortkey) " .
+ "VALUES (?, ?, ?, ?)";
+ do_query($form, $dbh, $query, $name, $base_unit, $factor, $sortkey);
+
+ if ($languages) {
+ $query = "INSERT INTO units_language (unit, language_id, localized, localized_plural) VALUES (?, ?, ?, ?)";
+ my $sth = $dbh->prepare($query);
+ foreach my $lang (@{$languages}) {
+ my @values = ($name, $lang->{"id"}, $lang->{"localized"}, $lang->{"localized_plural"});
+ $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
+ }
+ $sth->finish();
+ }
+
+ $dbh->commit();
+ $dbh->disconnect();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_units {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form, $units, $delete_units) = @_;
+
+ my $dbh = $form->dbconnect_noauto($myconfig);
+
+ my ($base_unit, $unit, $sth, $query);
+
+ $query = "DELETE FROM units_language";
+ $dbh->do($query) || $form->dberror($query);
+
+ if ($delete_units && (0 != scalar(@{$delete_units}))) {
+ $query = "DELETE FROM units WHERE name IN (";
+ map({ $query .= "?," } @{$delete_units});
+ substr($query, -1, 1) = ")";
+ $dbh->do($query, undef, @{$delete_units}) ||
+ $form->dberror($query . " (" . join(", ", @{$delete_units}) . ")");
+ }
+
+ $query = "UPDATE units SET name = ?, base_unit = ?, factor = ? WHERE name = ?";
+ $sth = $dbh->prepare($query);
+
+ my $query_lang = "INSERT INTO units_language (unit, language_id, localized, localized_plural) VALUES (?, ?, ?, ?)";
+ my $sth_lang = $dbh->prepare($query_lang);
+
+ foreach $unit (values(%{$units})) {
+ $unit->{"depth"} = 0;
+ my $base_unit = $unit;
+ while ($base_unit->{"base_unit"}) {
+ $unit->{"depth"}++;
+ $base_unit = $units->{$base_unit->{"base_unit"}};
+ }
+ }
+
+ foreach $unit (sort({ $a->{"depth"} <=> $b->{"depth"} } values(%{$units}))) {
+ if ($unit->{"LANGUAGES"}) {
+ foreach my $lang (@{$unit->{"LANGUAGES"}}) {
+ next unless ($lang->{"id"} && $lang->{"localized"});
+ my @values = ($unit->{"name"}, $lang->{"id"}, $lang->{"localized"}, $lang->{"localized_plural"});
+ $sth_lang->execute(@values) || $form->dberror($query_lang . " (" . join(", ", @values) . ")");
+ }
+ }
+
+ next if ($unit->{"unchanged_unit"});
+
+ my @values = ($unit->{"name"}, $unit->{"base_unit"}, $unit->{"factor"}, $unit->{"old_name"});
+ $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
+ }
+
+ $sth->finish();
+ $sth_lang->finish();
+ $dbh->commit();
+ $dbh->disconnect();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub taxes {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT
+ t.id,
+ t.taxkey,
+ t.taxdescription,
+ round(t.rate * 100, 2) AS rate,
+ (SELECT accno FROM chart WHERE id = chart_id) AS taxnumber,
+ (SELECT description FROM chart WHERE id = chart_id) AS account_description
+ FROM tax t
+ ORDER BY taxkey|;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ $form->{TAX} = [];
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ push @{ $form->{TAX} }, $ref;
+ }
+
+ $sth->finish;
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_tax_accounts {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ my $dbh = $form->dbconnect($myconfig);
+
+ # get Accounts from chart
+ my $query = qq{ SELECT
+ id,
+ accno || ' - ' || description AS taxaccount
+ FROM chart
+ WHERE link LIKE '%_tax%'
+ ORDER BY accno
+ };
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute || $form->dberror($query);
+
+ $form->{ACCOUNTS} = [];
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ push @{ $form->{ACCOUNTS} }, $ref;
+ }
+
+ $sth->finish;
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_tax {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->dbconnect($myconfig);
+
+ my $query = qq|SELECT
+ taxkey,
+ taxdescription,
+ round(rate * 100, 2) AS rate,
+ chart_id
+ FROM tax
+ WHERE id = ? |;
+
+ my $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
+
+ my $ref = $sth->fetchrow_hashref("NAME_lc");
+
+ map { $form->{$_} = $ref->{$_} } keys %$ref;
+
+ $sth->finish;
+
+ # see if it is used by a taxkey
+ $query = qq|SELECT count(*) FROM taxkeys
+ WHERE tax_id = ? AND chart_id >0|;
+
+ ($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id});
+
+ $form->{orphaned} = !$form->{orphaned};
+ $sth->finish;
+
+ if (!$form->{orphaned} ) {
+ $query = qq|SELECT DISTINCT c.id, c.accno
+ FROM taxkeys tk
+ JOIN tax t ON (t.id = tk.tax_id)
+ JOIN chart c ON (c.id = tk.chart_id)
+ WHERE tk.tax_id = ?|;
+
+ $sth = $dbh->prepare($query);
+ $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
+
+ $form->{TAXINUSE} = [];
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ push @{ $form->{TAXINUSE} }, $ref;
+ }
+
+ $sth->finish;
+ }
+
+ $dbh->disconnect;
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_tax {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+ my $query;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ $form->{rate} = $form->{rate} / 100;
+
+ my @values = ($form->{taxkey}, $form->{taxdescription}, $form->{rate}, $form->{chart_id}, $form->{chart_id} );
+ if ($form->{id} ne "") {
+ $query = qq|UPDATE tax SET
+ taxkey = ?,
+ taxdescription = ?,
+ rate = ?,
+ chart_id = ?,
+ taxnumber = (SELECT accno FROM chart WHERE id= ? )
+ WHERE id = ?|;
+ push(@values, $form->{id});
+
+ } else {
+ #ok
+ $query = qq|INSERT INTO tax (
+ taxkey,
+ taxdescription,
+ rate,
+ chart_id,
+ taxnumber
+ )
+ VALUES (?, ?, ?, ?, (SELECT accno FROM chart WHERE id = ?) )|;
+ }
+ do_query($form, $dbh, $query, @values);
+
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub delete_tax {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+ my $query;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ $query = qq|DELETE FROM tax
+ WHERE id = ?|;
+ do_query($form, $dbh, $query, $form->{id});
+
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_price_factor {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my $query;
+ my @values = ($form->{description}, conv_i($form->{factor}));
+
+ if ($form->{id}) {
+ $query = qq|UPDATE price_factors SET description = ?, factor = ? WHERE id = ?|;
+ push @values, conv_i($form->{id});
+
+ } else {
+ $query = qq|INSERT INTO price_factors (description, factor, sortkey) VALUES (?, ?, (SELECT COALESCE(MAX(sortkey), 0) + 1 FROM price_factors))|;
+ }
+
+ do_query($form, $dbh, $query, @values);
+
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_all_price_factors {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ $form->{PRICE_FACTORS} = selectall_hashref_query($form, $dbh, qq|SELECT * FROM price_factors ORDER BY sortkey|);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_price_factor {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT description, factor,
+ ((SELECT COUNT(*) FROM parts WHERE price_factor_id = ?) +
+ (SELECT COUNT(*) FROM invoice WHERE price_factor_id = ?) +
+ (SELECT COUNT(*) FROM orderitems WHERE price_factor_id = ?)) = 0 AS orphaned
+ FROM price_factors WHERE id = ?|;
+
+ ($form->{description}, $form->{factor}, $form->{orphaned}) = selectrow_query($form, $dbh, $query, (conv_i($form->{id})) x 4);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub delete_price_factor {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ do_query($form, $dbh, qq|DELETE FROM price_factors WHERE id = ?|, conv_i($form->{id}));
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_warehouse {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my ($query, @values, $sth);
+
+ if (!$form->{id}) {
+ $query = qq|SELECT nextval('id')|;
+ ($form->{id}) = selectrow_query($form, $dbh, $query);
+
+ $query = qq|INSERT INTO warehouse (id, sortkey) VALUES (?, (SELECT COALESCE(MAX(sortkey), 0) + 1 FROM warehouse))|;
+ do_query($form, $dbh, $query, $form->{id});
+ }
+
+ do_query($form, $dbh, qq|UPDATE warehouse SET description = ?, invalid = ? WHERE id = ?|,
+ $form->{description}, $form->{invalid} ? 't' : 'f', conv_i($form->{id}));
+
+ if (0 < $form->{number_of_new_bins}) {
+ $query = qq|INSERT INTO bin (warehouse_id, description) VALUES (?, ?)|;
+ $sth = prepare_query($form, $dbh, $query);
+
+ foreach my $i (1..$form->{number_of_new_bins}) {
+ do_statement($form, $sth, $query, conv_i($form->{id}), "$form->{prefix}${i}");
+ }
+
+ $sth->finish();
+ }
+
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+}
+
+sub save_bins {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my ($query, @values, $commit_necessary, $sth);
+
+ @values = map { $form->{"id_${_}"} } grep { $form->{"delete_${_}"} } (1..$form->{rowcount});
+
+ if (@values) {
+ $query = qq|DELETE FROM bin WHERE id IN (| . join(', ', ('?') x scalar(@values)) . qq|)|;
+ do_query($form, $dbh, $query, @values);
+
+ $commit_necessary = 1;
+ }
+
+ $query = qq|UPDATE bin SET description = ? WHERE id = ?|;
+ $sth = prepare_query($form, $dbh, $query);
+
+ foreach my $row (1..$form->{rowcount}) {
+ next if ($form->{"delete_${row}"});
+
+ do_statement($form, $sth, $query, $form->{"description_${row}"}, conv_i($form->{"id_${row}"}));
+
+ $commit_necessary = 1;
+ }
+
+ $sth->finish();
+
+ $dbh->commit() if ($commit_necessary);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub delete_warehouse {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my $id = conv_i($form->{id});
+ my $query = qq|SELECT i.bin_id FROM inventory i WHERE i.bin_id IN (SELECT b.id FROM bin b WHERE b.warehouse_id = ?) LIMIT 1|;
+ my ($count) = selectrow_query($form, $dbh, $query, $id);
+
+ if ($count) {
+ $main::lxdebug->leave_sub();
+ return 0;
+ }
+
+ do_query($form, $dbh, qq|DELETE FROM bin WHERE warehouse_id = ?|, conv_i($form->{id}));
+ do_query($form, $dbh, qq|DELETE FROM warehouse WHERE id = ?|, conv_i($form->{id}));
+
+ $dbh->commit();
+
+ $main::lxdebug->leave_sub();
+
+ return 1;
+}
+
+sub get_all_warehouses {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my $query = qq|SELECT w.id, w.description, w.invalid,
+ (SELECT COUNT(b.description) FROM bin b WHERE b.warehouse_id = w.id) AS number_of_bins
+ FROM warehouse w
+ ORDER BY w.sortkey|;
+
+ $form->{WAREHOUSES} = selectall_hashref_query($form, $dbh, $query);
+
+ $main::lxdebug->leave_sub();
+}
+
+sub get_warehouse {
+ $main::lxdebug->enter_sub();
+
+ my ($self, $myconfig, $form) = @_;
+
+ # connect to database
+ my $dbh = $form->get_standard_dbh($myconfig);
+
+ my $id = conv_i($form->{id});
+ my $query = qq|SELECT w.description, w.invalid
+ FROM warehouse w
+ WHERE w.id = ?|;
+
+ my $ref = selectfirst_hashref_query($form, $dbh, $query, $id);
+
+ map { $form->{$_} = $ref->{$_} } keys %{ $ref };
+
+ $query = qq|SELECT b.*, EXISTS
+ (SELECT i.warehouse_id
+ FROM inventory i
+ WHERE i.bin_id = b.id
+ LIMIT 1)
+ AS in_use
+ FROM bin b
+ WHERE b.warehouse_id = ?|;
+
+ $form->{BINS} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
+
+ $main::lxdebug->leave_sub();
+}
+
+1;
projects / mfinanz.git / blobdiff