X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAM.pm;h=f455e65a243d1c499b8e5c4e880479a0625ec41e;hb=9a795b8553bd43a379646bc6244c618e570556e9;hp=8d6730e31f223cc535dcff27b443a8bb85ef6497;hpb=d05eb259dd6b68cc75c29aa32961cdfce40bc45e;p=kivitendo-erp.git diff --git a/SL/AM.pm b/SL/AM.pm index 8d6730e31..f455e65a2 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -45,19 +45,24 @@ sub get_account { my ($self, $myconfig, $form) = @_; - $form->{id} = "NULL" unless ($form->{id}); - # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq§SELECT c.accno, c.description, c.charttype, c.gifi_accno, - c.category,c.link, tk.taxkey_id, tk.pos_ustva, tk.tax_id,tk.tax_id||'--'||tk.taxkey_id AS tax, tk.startdate, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, c.pos_bwa - FROM chart c LEFT JOIN taxkeys tk - ON (c.id=tk.chart_id AND tk.id = (SELECT id from taxkeys where taxkeys.chart_id =c.id AND startdate<=current_date ORDER BY startdate desc LIMIT 1)) - WHERE c.id = $form->{id}§; - + my $query = + qq!SELECT c.accno, c.description, c.charttype, c.gifi_accno, c.category,! . + qq! c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, ! . + qq! c.pos_bwa, ! . + qq! tk.taxkey_id, tk.pos_ustva, tk.tax_id, ! . + qq! tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate ! . + qq!FROM chart c ! . + qq!LEFT JOIN taxkeys tk ! . + qq!ON (c.id=tk.chart_id AND tk.id = ! . + qq! (SELECT id FROM taxkeys ! . + qq! WHERE taxkeys.chart_id = c.id AND startdate <= current_date ! . + qq! ORDER BY startdate DESC LIMIT 1)) ! . + qq!WHERE c.id = ?!; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); my $ref = $sth->fetchrow_hashref(NAME_lc); @@ -93,16 +98,13 @@ sub get_account { $sth->finish; if ($form->{id}) { - - $where = " WHERE link='$form->{link}'"; - - # get new accounts $query = qq|SELECT id, accno,description - FROM chart $where|; + FROM chart WHERE link = ?|; $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})"); + $form->{NEWACCOUNT} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{NEWACCOUNT} }, $ref; } @@ -111,9 +113,9 @@ sub get_account { } # check if we have any transactions $query = qq|SELECT a.trans_id FROM acc_trans a - WHERE a.chart_id = $form->{id}|; + WHERE a.chart_id = ?|; $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); ($form->{orphaned}) = $sth->fetchrow_array; $form->{orphaned} = !$form->{orphaned}; @@ -123,11 +125,8 @@ sub get_account { $form->{new_chart_valid} = 0; if ($form->{new_chart_id}) { $query = qq|SELECT current_date-valid_from FROM chart - WHERE id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my ($count) = $sth->fetchrow_array; + WHERE id = ?|; + my ($count) = selectrow_query($form, $dbh, $query, $form->{id}); if ($count >=0) { $form->{new_chart_valid} = 1; } @@ -167,9 +166,6 @@ sub save_account { } chop $form->{link}; - # if we have an id then replace the old record - $form->{description} =~ s/\'/\'\'/g; - # strip blanks from accno map { $form->{$_} =~ s/ //g; } qw(accno); @@ -179,95 +175,74 @@ sub save_account { $form->{id} = ""; } - map({ $form->{$_} = "NULL" unless ($form->{$_}); } - qw(pos_ustva pos_bwa pos_bilanz pos_eur new_chart_id)); - my($tax_id, $taxkey) = split /--/, $form->{tax}; - $form->{valid_from} = ($form->{valid_from}) ? "'$form->{valid_from}'" : "NULL"; - my $startdate = ($form->{startdate}) ? "'$form->{startdate}'" : "'1970-01-01'"; + my @values; + + my ($tax_id, $taxkey) = split(/--/, $form->{tax}); + my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01"; + if ($form->{id} && $form->{orphaned}) { $query = qq|UPDATE chart SET - accno = '$form->{accno}', - description = '$form->{description}', - charttype = '$form->{charttype}', - gifi_accno = '$form->{gifi_accno}', - category = '$form->{category}', - link = '$form->{link}', - taxkey_id = $taxkey, - pos_ustva = $form->{pos_ustva}, - pos_bwa = $form->{pos_bwa}, - pos_bilanz = $form->{pos_bilanz}, - pos_eur = $form->{pos_eur}, - new_chart_id = $form->{new_chart_id}, - valid_from = $form->{valid_from} - WHERE id = $form->{id}|; + accno = ?, description = ?, charttype = ?, + gifi_accno = ?, category = ?, link = ?, + taxkey_id = ?, + pos_ustva = ?, pos_bwa = ?, pos_bilanz = ?, + pos_eur = ?, new_chart_id = ?, valid_from = ? + WHERE id = ?|; + @values = ($form->{accno}, $form->{description}, $form->{charttype}, + $form->{gifi_accno}, $form->{category}, $form->{link}, + conv_i($taxkey), + conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}), + conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}), + conv_i($form->{new_chart_id}), + conv_date($form->{valid_from}), + $form->{id}); + } elsif ($form->{id} && !$form->{new_chart_valid}) { - $query = qq|UPDATE chart SET - new_chart_id = $form->{new_chart_id}, - valid_from = $form->{valid_from} - WHERE id = $form->{id}|; + $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ? + WHERE id = ?|; + @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}), + $form->{id}); } else { - $query = qq|INSERT INTO chart - (accno, description, charttype, gifi_accno, category, link, taxkey_id, pos_ustva, pos_bwa, pos_bilanz,pos_eur, new_chart_id, valid_from) - VALUES ('$form->{accno}', '$form->{description}', - '$form->{charttype}', '$form->{gifi_accno}', - '$form->{category}', '$form->{link}', $taxkey, $form->{pos_ustva}, $form->{pos_bwa}, $form->{pos_bilanz}, $form->{pos_eur}, $form->{new_chart_id}, $form->{valid_from})|; + (accno, description, charttype, + gifi_accno, category, link, + taxkey_id, + pos_ustva, pos_bwa, pos_bilanz, pos_eur, + new_chart_id, valid_from) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; + @values = ($form->{accno}, $form->{description}, $form->{charttype}, + $form->{gifi_accno}, $form->{category}, $form->{link}, + conv_i($taxkey), + conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}), + conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}), + conv_i($form->{new_chart_id}), + conv_date($form->{valid_from})); + } - $dbh->do($query) || $form->dberror($query); + do_query($form, $dbh, $query, @values); #Save Taxes if (!$form->{id}) { - $query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ((SELECT id FROM chart where accno='$form->{accno}'), $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|; - $dbh->do($query) || $form->dberror($query); + $query = + qq|INSERT INTO taxkeys | . + qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | . + qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; + do_query($form, $dbh, $query, + $form->{accno}, conv_i($tax_id), conv_i($taxkey), + conv_i($form->{pos_ustva}), conv_date($startdate)); + } else { - $query = qq|DELETE FROM taxkeys WHERE chart_id=$form->{id} AND tax_id=$tax_id|; - $dbh->do($query) || $form->dberror($query); - $query = qq|INSERT INTO taxkeys (chart_id,tax_id,taxkey_id, pos_ustva, startdate) VALUES ($form->{id}, $tax_id, $taxkey,$form->{pos_ustva}, $startdate)|; - $dbh->do($query) || $form->dberror($query); - } + $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|; + do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id)); -# if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) { -# -# my $chart_id = $form->{id}; -# -# unless ($form->{id}) { -# -# # get id from chart -# $query = qq|SELECT c.id -# FROM chart c -# WHERE c.accno = '$form->{accno}'|; -# $sth = $dbh->prepare($query); -# $sth->execute || $form->dberror($query); -# -# ($chart_id) = $sth->fetchrow_array; -# $sth->finish; -# } -# -# # add account if it doesn't exist in tax -# $query = qq|SELECT t.chart_id -# FROM tax t -# WHERE t.chart_id = $chart_id|; -# $sth = $dbh->prepare($query); -# $sth->execute || $form->dberror($query); -# -# my ($tax_id) = $sth->fetchrow_array; -# $sth->finish; -# -# # add tax if it doesn't exist -# unless ($tax_id) { -# $query = qq|INSERT INTO tax (chart_id, rate) -# VALUES ($chart_id, 0)|; -# $dbh->do($query) || $form->dberror($query); -# } -# } else { -# -# # remove tax -# if ($form->{id}) { -# $query = qq|DELETE FROM tax -# WHERE chart_id = $form->{id}|; -# $dbh->do($query) || $form->dberror($query); -# } -# } + $query = + qq|INSERT INTO taxkeys | . + qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | . + qq|VALUES (?, ?, ?, ?, ?)|; + do_query($form, $dbh, $query, + $form->{id}, conv_i($tax_id), conv_i($taxkey), + conv_i($form->{pos_ustva}), conv_date($startdate)); + } # commit my $rc = $dbh->commit; @@ -287,48 +262,35 @@ sub delete_account { my $dbh = $form->dbconnect_noauto($myconfig); my $query = qq|SELECT count(*) FROM acc_trans a - WHERE a.chart_id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + WHERE a.chart_id = ?|; + my ($count) = selectrow_query($form, $dbh, $query, $form->{id}); - if ($sth->fetchrow_array) { - $sth->finish; + if ($count) { $dbh->disconnect; $main::lxdebug->leave_sub(); return; } - $sth->finish; - - # delete chart of account record - $query = qq|DELETE FROM chart - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); # set inventory_accno_id, income_accno_id, expense_accno_id to defaults - $query = qq|UPDATE parts - SET inventory_accno_id = - (SELECT inventory_accno_id FROM defaults) - WHERE inventory_accno_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|UPDATE parts - SET income_accno_id = - (SELECT income_accno_id FROM defaults) - WHERE income_accno_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|UPDATE parts - SET expense_accno_id = - (SELECT expense_accno_id FROM defaults) - WHERE expense_accno_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + foreach my $type (qw(inventory income expense)) { + $query = + qq|UPDATE parts | . + qq|SET ${type}_accno_id = (SELECT ${type}_accno_id FROM defaults) | . + qq|WHERE ${type}_accno_id = ?|; + do_query($form, $dbh, $query, $form->{id}); + } foreach my $table (qw(partstax customertax vendortax tax)) { $query = qq|DELETE FROM $table - WHERE chart_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + WHERE chart_id = ?|; + do_query($form, $dbh, $query, $form->{id}); } + # delete chart of account record + $query = qq|DELETE FROM chart + WHERE id = ?|; + do_query($form, $dbh, $query, $form->{id}); + # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; @@ -338,114 +300,6 @@ sub delete_account { return $rc; } -sub gifi_accounts { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT accno, description - FROM gifi - ORDER BY accno|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_gifi { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT g.accno, g.description - FROM gifi g - WHERE g.accno = '$form->{accno}'|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - - # check for transactions - $query = qq|SELECT count(*) FROM acc_trans a, chart c, gifi g - WHERE c.gifi_accno = g.accno - AND a.chart_id = c.id - AND g.accno = '$form->{accno}'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; - $sth->finish; - $form->{orphaned} = !$form->{orphaned}; - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub save_gifi { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $form->{description} =~ s/\'/\'\'/g; - - # id is the old account number! - if ($form->{id}) { - $query = qq|UPDATE gifi SET - accno = '$form->{accno}', - description = '$form->{description}' - WHERE accno = '$form->{id}'|; - } else { - $query = qq|INSERT INTO gifi - (accno, description) - VALUES ('$form->{accno}', '$form->{description}')|; - } - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub delete_gifi { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - # id is the old account number! - $query = qq|DELETE FROM gifi - WHERE accno = '$form->{id}'|; - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - sub warehouses { $main::lxdebug->enter_sub(); @@ -974,13 +828,13 @@ sub delete_language { # connect to database my $dbh = $form->dbconnect_noauto($myconfig); - my $query = "DELETE FROM units_language WHERE language_id = ?"; - $dbh->do($query, undef, $form->{"id"}) || - $form->dberror($query . " ($form->{id})"); + foreach my $table (qw(translation_payment_terms units_language)) { + my $query = qq|DELETE FROM $table WHERE language_id = ?|; + do_query($form, $dbh, $query, $form->{"id"}); + } $query = "DELETE FROM language WHERE id = ?"; - $dbh->do($query, undef, $form->{"id"}) || - $form->dberror($query . " ($form->{id})"); + do_query($form, $dbh, $query, $form->{"id"}); $dbh->commit(); $dbh->disconnect; @@ -997,13 +851,32 @@ sub buchungsgruppe { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT id, description, inventory_accno_id, (select accno from chart where id=inventory_accno_id) as inventory_accno, income_accno_id_0, (select accno from chart where id=income_accno_id_0) as income_accno_0, expense_accno_id_0, (select accno from chart where id=expense_accno_id_0) as expense_accno_0, income_accno_id_1, (select accno from chart where id=income_accno_id_1) as income_accno_1, expense_accno_id_1, (select accno from chart where id=expense_accno_id_1) as expense_accno_1, income_accno_id_2, (select accno from chart where id=income_accno_id_2) as income_accno_2, expense_accno_id_2, (select accno from chart where id=expense_accno_id_2) as expense_accno_2, income_accno_id_3, (select accno from chart where id=income_accno_id_3) as income_accno_3, expense_accno_id_3, (select accno from chart where id=expense_accno_id_3) as expense_accno_3 - FROM buchungsgruppen - ORDER BY id|; + my $query = qq|SELECT id, description, + inventory_accno_id, + (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno, + income_accno_id_0, + (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0, + expense_accno_id_0, + (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0, + income_accno_id_1, + (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1, + expense_accno_id_1, + (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1, + income_accno_id_2, + (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2, + expense_accno_id_2, + (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2, + income_accno_id_3, + (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3, + expense_accno_id_3, + (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3 + FROM buchungsgruppen + ORDER BY sortkey|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); + $form->{ALL} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{ALL} }, $ref; } @@ -1122,6 +995,8 @@ sub save_buchungsgruppe { $form->{income_accno_id_2}, $form->{expense_accno_id_2}, $form->{income_accno_id_3}, $form->{expense_accno_id_3}); + my $query; + # id is the old record if ($form->{id}) { $query = qq|UPDATE buchungsgruppen SET @@ -1133,13 +1008,18 @@ sub save_buchungsgruppe { WHERE id = ?|; push(@values, $form->{id}); } else { + $query = qq|SELECT COALESCE(MAX(sortkey) + 1, 1) FROM buchungsgruppen|; + my ($sortkey) = $dbh->selectrow_array($query); + $form->dberror($query) if ($dbh->err); + push(@values, $sortkey); $query = qq|INSERT INTO buchungsgruppen (description, inventory_accno_id, income_accno_id_0, expense_accno_id_0, income_accno_id_1, expense_accno_id_1, income_accno_id_2, expense_accno_id_2, - income_accno_id_3, expense_accno_id_3) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; + income_accno_id_3, expense_accno_id_3, + sortkey) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; } do_query($form, $dbh, $query, @values); @@ -1164,6 +1044,37 @@ sub delete_buchungsgruppe { $main::lxdebug->leave_sub(); } +sub swap_sortkeys { + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $table) = @_; + + # connect to database + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query = + qq|SELECT + (SELECT sortkey FROM $table WHERE id = ?) AS sortkey1, + (SELECT sortkey FROM $table WHERE id = ?) AS sortkey2|; + my @values = ($form->{"id1"}, $form->{"id2"}); + my @sortkeys = selectrow_query($form, $dbh, $query, @values); + $main::lxdebug->dump(0, "v", \@values); + $main::lxdebug->dump(0, "s", \@sortkeys); + + $query = qq|UPDATE $table SET sortkey = ? WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($sortkeys[1], $form->{"id1"}) || + $form->dberror($query . " ($sortkeys[1], $form->{id1})"); + $sth->execute($sortkeys[0], $form->{"id2"}) || + $form->dberror($query . " ($sortkeys[0], $form->{id2})"); + $sth->finish(); + + $dbh->commit(); + $dbh->disconnect; + + $main::lxdebug->leave_sub(); +} + sub printer { $main::lxdebug->enter_sub(); @@ -1273,15 +1184,13 @@ sub payment { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT * - FROM payment_terms - ORDER BY id|; + my $query = qq|SELECT * FROM payment_terms ORDER BY sortkey|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); + $form->{ALL} = []; while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100)); push @{ $form->{ALL} }, $ref; } @@ -1299,20 +1208,36 @@ sub get_payment { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = - qq|SELECT * - FROM payment_terms - WHERE id = $form->{id}|; + my $query = qq|SELECT * FROM payment_terms WHERE id = ?|; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})"); my $ref = $sth->fetchrow_hashref(NAME_lc); - $ref->{percent_skonto} = $form->format_amount($myconfig,($ref->{percent_skonto} * 100)); - map { $form->{$_} = $ref->{$_} } keys %$ref; + $sth->finish(); + $query = + qq|SELECT t.language_id, t.description_long, l.description AS language | . + qq|FROM translation_payment_terms t | . + qq|LEFT JOIN language l ON t.language_id = l.id | . + qq|WHERE t.payment_terms_id = ? | . + qq|UNION | . + qq|SELECT l.id AS language_id, NULL AS description_long, | . + qq|l.description AS language | . + qq|FROM language l|; + $sth = $dbh->prepare($query); + $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})"); + + my %mapping; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + $mapping{ $ref->{"language_id"} } = $ref + unless (defined($mapping{ $ref->{"language_id"} })); + } $sth->finish; + $form->{"TRANSLATION"} = [sort({ $a->{"language"} cmp $b->{"language"} } + values(%mapping))]; + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -1324,35 +1249,62 @@ sub save_payment { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->dbconnect_noauto($myconfig); - $form->{description} =~ s/\'/\'\'/g; - $form->{description_long} =~ s/\'/\'\'/g; - $percentskonto = $form->parse_amount($myconfig, $form->{percent_skonto}) /100; - $form->{ranking} *= 1; - $form->{terms_netto} *= 1; - $form->{terms_skonto} *= 1; - $form->{percent_skonto} *= 1; + my $query; + if (!$form->{id}) { + $query = qq|SELECT nextval('id'), COALESCE(MAX(sortkey) + 1, 1) | . + qq|FROM payment_terms|; + my $sortkey; + ($form->{id}, $sortkey) = selectrow_query($form, $dbh, $query); + $query = qq|INSERT INTO payment_terms (id, sortkey) VALUES (?, ?)|; + do_query($form, $dbh, $query, $form->{id}, $sortkey); - # id is the old record - if ($form->{id}) { - $query = qq|UPDATE payment_terms SET - description = '$form->{description}', - ranking = $form->{ranking}, - description_long = '$form->{description_long}', - terms_netto = $form->{terms_netto}, - terms_skonto = $form->{terms_skonto}, - percent_skonto = $percentskonto - WHERE id = $form->{id}|; } else { - $query = qq|INSERT INTO payment_terms - (description, ranking, description_long, terms_netto, terms_skonto, percent_skonto) - VALUES ('$form->{description}', $form->{ranking}, '$form->{description_long}', $form->{terms_netto}, $form->{terms_skonto}, $percentskonto)|; + $query = + qq|DELETE FROM translation_payment_terms | . + qq|WHERE payment_terms_id = ?|; + do_query($form, $dbh, $query, $form->{"id"}); + } + + $query = qq|UPDATE payment_terms SET + description = ?, description_long = ?, + ranking = ?, + terms_netto = ?, terms_skonto = ?, + percent_skonto = ? + WHERE id = ?|; + my @values = ($form->{description}, $form->{description_long}, + $form->{ranking} * 1, + $form->{terms_netto} * 1, $form->{terms_skonto} * 1, + $form->{percent_skonto} * 1, + $form->{id}); + do_query($form, $dbh, $query, @values); + + $query = qq|SELECT id FROM language|; + my @language_ids; + my $sth = $dbh->prepare($query); + $sth->execute() || $form->dberror($query); + + while (my ($id) = $sth->fetchrow_array()) { + push(@language_ids, $id); } - $dbh->do($query) || $form->dberror($query); + $sth->finish(); + $query = + qq|INSERT INTO translation_payment_terms | . + qq|(language_id, payment_terms_id, description_long) | . + qq|VALUES (?, ?, ?)|; + $sth = $dbh->prepare($query); + + foreach my $language_id (@language_ids) { + do_statement($form, $sth, $query, $language_id, $form->{"id"}, + $form->{"description_long_${language_id}"}); + } + $sth->finish(); + + $dbh->commit(); $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -1364,12 +1316,16 @@ sub delete_payment { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->dbconnect_noauto($myconfig); - $query = qq|DELETE FROM payment_terms - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + my $query = + qq|DELETE FROM translation_payment_terms WHERE payment_terms_id = ?|; + do_query($form, $dbh, $query, $form->{"id"}); + $query = qq|DELETE FROM payment_terms WHERE id = ?|; + do_query($form, $dbh, $query, $form->{"id"}); + + $dbh->commit(); $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -2182,7 +2138,7 @@ sub unit_select_data { push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" }); } - foreach my $unit (sort({ lc($a) cmp lc($b) } keys(%{$units}))) { + foreach my $unit (sort({ $a->{"sortkey"} <=> $b->{"sortkey"} } keys(%{$units}))) { push(@{$select}, { "name" => $unit, "base_unit" => $units->{$unit}->{"base_unit"}, "factor" => $units->{$unit}->{"factor"}, @@ -2201,7 +2157,7 @@ sub unit_select_html { my $select = "