vendornumber = ?,
articlenumber = ?,
servicenumber = ?,
+ sdonumber = ?,
+ pdonumber = ?,
yearend = ?,
curr = ?,
businessnumber = ?|;
$form->{sqnumber}, $form->{rfqnumber},
$form->{customernumber}, $form->{vendornumber},
$form->{articlenumber}, $form->{servicenumber},
+ $form->{sdonumber}, $form->{pdonumber},
$form->{yearend}, $currency,
$form->{businessnumber});
do_query($form, $dbh, $query, @values);
sub retrieve_units {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form, $type, $prefix) = @_;
+ my ($self, $myconfig, $form, $prefix) = @_;
my $dbh = $form->dbconnect($myconfig);
my $query = "SELECT *, base_unit AS original_base_unit FROM units";
- my @values;
- if ($type) {
- $query .= " WHERE (type = ?)";
- @values = ($type);
- }
- my $sth = $dbh->prepare($query);
- $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
+ my $sth = prepare_execute_query($form, $dbh, $query);
my $units = {};
while (my $ref = $sth->fetchrow_hashref()) {
sub add_unit {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form, $name, $base_unit, $factor, $type, $languages) = @_;
+ 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, type, sortkey) " .
- "VALUES (?, ?, ?, ?, ?)";
- do_query($form, $dbh, $query, $name, $base_unit, $factor, $type, $sortkey);
+ $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 (?, ?, ?, ?)";
sub save_units {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form, $type, $units, $delete_units) = @_;
+ my ($self, $myconfig, $form, $units, $delete_units) = @_;
my $dbh = $form->dbconnect_noauto($myconfig);
sub swap_units {
$main::lxdebug->enter_sub();
- my ($self, $myconfig, $form, $dir, $name_1, $unit_type) = @_;
+ my ($self, $myconfig, $form, $dir, $name_1) = @_;
my $dbh = $form->dbconnect_noauto($myconfig);
$query =
qq|SELECT sortkey FROM units | .
- qq|WHERE sortkey | . ($dir eq "down" ? ">" : "<") . qq| ? AND type = ? | .
+ qq|WHERE sortkey | . ($dir eq "down" ? ">" : "<") . qq| ? | .
qq|ORDER BY sortkey | . ($dir eq "down" ? "ASC" : "DESC") . qq| LIMIT 1|;
- my ($sortkey_2) = selectrow_query($form, $dbh, $query, $sortkey_1, $unit_type);
+ my ($sortkey_2) = selectrow_query($form, $dbh, $query, $sortkey_1);
if (defined($sortkey_1)) {
$query = qq|SELECT name FROM units WHERE sortkey = ${sortkey_2}|;
$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 / kivitendo-erp.git / blobdiff