X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FAM.pm;h=df8bf23e510f5414a6baf0ffcf5309919264e54f;hb=72be9c763f3b7f7df1fae4fe10011e45f9e2ad1d;hp=f455e65a243d1c499b8e5c4e880479a0625ec41e;hpb=9a795b8553bd43a379646bc6244c618e570556e9;p=kivitendo-erp.git diff --git a/SL/AM.pm b/SL/AM.pm index f455e65a2..df8bf23e5 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -37,9 +37,13 @@ package AM; +use Carp; use Data::Dumper; +use Encode; use SL::DBUtils; +use strict; + sub get_account { $main::lxdebug->enter_sub(); @@ -47,24 +51,27 @@ sub get_account { # connect to database my $dbh = $form->dbconnect($myconfig); - 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 $query = qq{ + SELECT c.accno, c.description, c.charttype, c.category, + c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, + c.pos_bwa, datevautomatik, + tk.taxkey_id, tk.pos_ustva, tk.tax_id, + tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate + 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 = ? + }; + + + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); my $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref("NAME_lc"); foreach my $key (keys %$ref) { $form->{"$key"} = $ref->{"$key"}; @@ -75,24 +82,36 @@ sub get_account { # get default accounts $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id FROM defaults|; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - $ref = $sth->fetchrow_hashref(NAME_lc); + $ref = $sth->fetchrow_hashref("NAME_lc"); - map { $form->{$_} = $ref->{$_} } keys %ref; + map { $form->{$_} = $ref->{$_} } keys %{ $ref }; $sth->finish; + + # get taxkeys and description - $query = qq§SELECT id, taxkey,id||'--'||taxkey AS tax, taxdescription - FROM tax ORDER BY taxkey§; + $query = qq{ + SELECT + id, + (SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno, + taxkey, + id||'--'||taxkey AS tax, + taxdescription, + rate + FROM tax ORDER BY taxkey + }; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $form->{TAXKEY} = []; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { push @{ $form->{TAXKEY} }, $ref; } @@ -100,20 +119,58 @@ sub get_account { if ($form->{id}) { # get new accounts $query = qq|SELECT id, accno,description - FROM chart WHERE link = ?|; + FROM chart + WHERE link = ? + ORDER BY accno|; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})"); $form->{NEWACCOUNT} = []; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { push @{ $form->{NEWACCOUNT} }, $ref; } $sth->finish; + + # get the taxkeys of account + + $query = qq{ + SELECT + tk.id, + tk.chart_id, + c.accno, + tk.tax_id, + t.taxdescription, + t.rate, + tk.taxkey_id, + tk.pos_ustva, + tk.startdate + FROM taxkeys tk + LEFT JOIN tax t ON (t.id = tk.tax_id) + LEFT JOIN chart c ON (c.id = t.chart_id) + + WHERE tk.chart_id = ? + ORDER BY startdate DESC + }; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); + $sth = $dbh->prepare($query); + + $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); + + $form->{ACCOUNT_TAXKEYS} = []; + + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push @{ $form->{ACCOUNT_TAXKEYS} }, $ref; + } + + $sth->finish; + } # check if we have any transactions $query = qq|SELECT a.trans_id FROM acc_trans a WHERE a.chart_id = ?|; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); @@ -126,6 +183,7 @@ sub get_account { if ($form->{new_chart_id}) { $query = qq|SELECT current_date-valid_from FROM chart WHERE id = ?|; + $main::lxdebug->message(LXDebug->QUERY(), "\$query=\n $query"); my ($count) = selectrow_query($form, $dbh, $query, $form->{id}); if ($count >=0) { $form->{new_chart_valid} = 1; @@ -141,6 +199,9 @@ sub get_account { sub save_account { $main::lxdebug->enter_sub(); + # TODO: it should be forbidden to change an account to a heading if there + # have been bookings to this account in the past + my ($self, $myconfig, $form) = @_; # connect to database, turn off AutoCommit @@ -149,7 +210,14 @@ sub save_account { # sanity check, can't have AR with AR_... if ($form->{AR} || $form->{AP} || $form->{IC}) { map { delete $form->{$_} } - qw(AR_amount AR_tax AR_paid AP_amount AP_tax AP_paid IC_sale IC_cogs IC_taxpart IC_income IC_expense IC_taxservice CT_tax); + qw(AR_amount AR_tax AR_paid AP_amount AP_tax AP_paid IC_sale IC_cogs IC_taxpart IC_income IC_expense IC_taxservice); + } + + if ($form->{AR_include_in_dropdown}) { + $form->{$form->{AR_include_in_dropdown}} = $form->{AR_include_in_dropdown}; + } + if ($form->{AP_include_in_dropdown}) { + $form->{$form->{AP_include_in_dropdown}} = $form->{AP_include_in_dropdown}; } $form->{link} = ""; @@ -160,7 +228,7 @@ sub save_account { $form->{IC}, $form->{IC_sale}, $form->{IC_cogs}, $form->{IC_taxpart}, $form->{IC_income}, $form->{IC_expense}, - $form->{IC_taxservice}, $form->{CT_tax} + $form->{IC_taxservice} ) { $form->{link} .= "${item}:" if ($item); } @@ -175,75 +243,183 @@ sub save_account { $form->{id} = ""; } + if (!$form->{id} || $form->{id} eq "") { + $query = qq|SELECT nextval('id')|; + ($form->{"id"}) = selectrow_query($form, $dbh, $query); + $query = qq|INSERT INTO chart (id, accno) VALUES (?, ?)|; + do_query($form, $dbh, $query, $form->{"id"}, $form->{"accno"}); + } + my @values; - my ($tax_id, $taxkey) = split(/--/, $form->{tax}); - my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01"; - if ($form->{id} && $form->{orphaned}) { + if ($form->{id}) { + + # if charttype is heading make sure certain values are empty + # specifically, if charttype is changed from an existing account, empty the + # fields unnecessary for headings, so that e.g. heading doesn't appear in + # drop-down menues due to still having a valid "link" entry + + if ( $form->{charttype} eq 'H' ) { + $form->{link} = ''; + $form->{pos_bwa} = ''; + $form->{pos_bilanz} = ''; + $form->{pos_eur} = ''; + $form->{new_chart_id} = ''; + $form->{valid_from} = ''; + }; + $query = qq|UPDATE chart SET - 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 = ?, 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @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})); + accno = ?, + description = ?, + charttype = ?, + category = ?, + link = ?, + pos_bwa = ?, + pos_bilanz = ?, + pos_eur = ?, + new_chart_id = ?, + valid_from = ?, + datevautomatik = ? + WHERE id = ?|; + + @values = ( + $form->{accno}, + $form->{description}, + $form->{charttype}, + $form->{category}, + $form->{link}, + 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->{datevautomatik} eq 'T') ? 'true':'false', + $form->{id}, + ); + } + do_query($form, $dbh, $query, @values); - #Save Taxes - if (!$form->{id}) { - $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)); + #Save Taxkeys - } else { - $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|; - do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id)); + my @taxkeys = (); + + my $MAX_TRIES = 10; # Maximum count of taxkeys in form + my $tk_count; + + READTAXKEYS: + for $tk_count (0 .. $MAX_TRIES) { + + # Loop control + + # Check if the account already exists, else cancel + + print(STDERR "Keine Taxkeys weil ID =: $form->{id}\n"); + + last READTAXKEYS if ( $form->{'id'} == 0); + + # check if there is a startdate + if ( $form->{"taxkey_startdate_$tk_count"} eq '' ) { + $tk_count++; + next READTAXKEYS; + } + + # Add valid taxkeys into the array + push @taxkeys , + { + id => ($form->{"taxkey_id_$tk_count"} eq 'NEW') ? conv_i('') : conv_i($form->{"taxkey_id_$tk_count"}), + tax_id => conv_i($form->{"taxkey_tax_$tk_count"}), + startdate => conv_date($form->{"taxkey_startdate_$tk_count"}), + chart_id => conv_i($form->{"id"}), + pos_ustva => conv_i($form->{"taxkey_pos_ustva_$tk_count"}), + delete => ( $form->{"taxkey_del_$tk_count"} eq 'delete' ) ? '1' : '', + }; + + $tk_count++; + } + + TAXKEY: + for my $j (0 .. $#taxkeys){ + if ( defined $taxkeys[$j]{'id'} ){ + # delete Taxkey? + + if ($taxkeys[$j]{'delete'}){ + $query = qq{ + DELETE FROM taxkeys WHERE id = ? + }; + + @values = ($taxkeys[$j]{'id'}); + + do_query($form, $dbh, $query, @values); + + next TAXKEY; + } + + # UPDATE Taxkey + + $query = qq{ + UPDATE taxkeys + SET taxkey_id = (SELECT taxkey FROM tax WHERE tax.id = ?), + chart_id = ?, + tax_id = ?, + pos_ustva = ?, + startdate = ? + WHERE id = ? + }; + @values = ( + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'chart_id'}, + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'pos_ustva'}, + $taxkeys[$j]{'startdate'}, + $taxkeys[$j]{'id'}, + ); + do_query($form, $dbh, $query, @values); + } + else { + # INSERT Taxkey + + $query = qq{ + INSERT INTO taxkeys ( + taxkey_id, + chart_id, + tax_id, + pos_ustva, + startdate + ) + VALUES ((SELECT taxkey FROM tax WHERE tax.id = ?), ?, ?, ?, ?) + }; + @values = ( + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'chart_id'}, + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'pos_ustva'}, + $taxkeys[$j]{'startdate'}, + ); + + do_query($form, $dbh, $query, @values); + } - $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)); } + # Update chart.taxkey_id to the latest from taxkeys for this chart. + $query = <{id}); + # commit my $rc = $dbh->commit; $dbh->disconnect; @@ -291,6 +467,11 @@ sub delete_account { WHERE id = ?|; do_query($form, $dbh, $query, $form->{id}); + # delete account taxkeys + $query = qq|DELETE FROM taxkeys + WHERE chart_id = ?|; + do_query($form, $dbh, $query, $form->{id}); + # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; @@ -300,7 +481,7 @@ sub delete_account { return $rc; } -sub warehouses { +sub lead { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -308,14 +489,14 @@ sub warehouses { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT id, description - FROM warehouse - ORDER BY 2|; + my $query = qq|SELECT id, lead + FROM leads + ORDER BY 2|; - $sth = $dbh->prepare($query); + my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { push @{ $form->{ALL} }, $ref; } @@ -325,7 +506,7 @@ sub warehouses { $main::lxdebug->leave_sub(); } -sub get_warehouse { +sub get_lead { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -333,102 +514,105 @@ sub get_warehouse { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT w.description - FROM warehouse w - WHERE w.id = $form->{id}|; + my $query = + qq|SELECT l.id, l.lead | . + qq|FROM leads l | . + qq|WHERE l.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); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; - # see if it is in use - $query = qq|SELECT count(*) FROM inventory i - WHERE i.warehouse_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; - $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_warehouse { +sub save_lead { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; + my ($query); # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{description} =~ s/\'/\'\'/g; - + my @values = ($form->{description}); + # id is the old record if ($form->{id}) { - $query = qq|UPDATE warehouse SET - description = '$form->{description}' - WHERE id = $form->{id}|; + $query = qq|UPDATE leads SET + lead = ? + WHERE id = ?|; + puhs(@values, $form->{id}); } else { - $query = qq|INSERT INTO warehouse - (description) - VALUES ('$form->{description}')|; + $query = qq|INSERT INTO leads + (lead) + VALUES (?)|; } - $dbh->do($query) || $form->dberror($query); + do_query($form, $dbh, $query, @values); $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub delete_warehouse { +sub delete_lead { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; + my ($query); # connect to database my $dbh = $form->dbconnect($myconfig); - $query = qq|DELETE FROM warehouse - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM leads + WHERE id = ?|; + do_query($form, $dbh, $query, $form->{id}); $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub departments { +sub language { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $return_list) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT d.id, d.description, d.role - FROM department d - ORDER BY 2|; + my $query = + "SELECT id, description, template_code, article_code, " . + " output_numberformat, output_dateformat, output_longdates " . + "FROM language ORDER BY description"; - $sth = $dbh->prepare($query); + my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; + my $ary = []; + + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push(@{ $ary }, $ref); } $sth->finish; $dbh->disconnect; $main::lxdebug->leave_sub(); + + if ($return_list) { + return @{$ary}; + } else { + $form->{ALL} = $ary; + } } -sub get_department { +sub get_language { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -436,103 +620,105 @@ sub get_department { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT d.description, d.role - FROM department d - WHERE d.id = $form->{id}|; + my $query = + "SELECT description, template_code, article_code, " . + " output_numberformat, output_dateformat, output_longdates " . + "FROM language 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); + my $ref = $sth->fetchrow_hashref("NAME_lc"); map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; - # see if it is in use - $query = qq|SELECT count(*) FROM dpt_trans d - WHERE d.department_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; - $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_department { +sub get_language_details { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $id) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{description} =~ s/\'/\'\'/g; - - if ($form->{id}) { - $query = qq|UPDATE department SET - description = '$form->{description}', - role = '$form->{role}' - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO department - (description, role) - VALUES ('$form->{description}', '$form->{role}')|; - } - $dbh->do($query) || $form->dberror($query); - + my $query = + "SELECT template_code, " . + " output_numberformat, output_dateformat, output_longdates " . + "FROM language WHERE id = ?"; + my @res = selectrow_query($form, $dbh, $query, $id); $dbh->disconnect; $main::lxdebug->leave_sub(); + + return @res; } -sub delete_department { +sub save_language { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); + my (@values, $query); - $query = qq|DELETE FROM department - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + map({ push(@values, $form->{$_}); } + qw(description template_code article_code + output_numberformat output_dateformat output_longdates)); + + # id is the old record + if ($form->{id}) { + $query = + "UPDATE language SET " . + " description = ?, template_code = ?, article_code = ?, " . + " output_numberformat = ?, output_dateformat = ?, " . + " output_longdates = ? " . + "WHERE id = ?"; + push(@values, $form->{id}); + } else { + $query = + "INSERT INTO language (" . + " description, template_code, article_code, " . + " output_numberformat, output_dateformat, output_longdates" . + ") VALUES (?, ?, ?, ?, ?, ?)"; + } + do_query($form, $dbh, $query, @values); $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub lead { +sub delete_language { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; + my $query; # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT id, lead - FROM leads - ORDER BY 2|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $dbh = $form->dbconnect_noauto($myconfig); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; + foreach my $table (qw(generic_translations units_language)) { + $query = qq|DELETE FROM $table WHERE language_id = ?|; + do_query($form, $dbh, $query, $form->{"id"}); } - $sth->finish; + $query = "DELETE FROM language WHERE id = ?"; + do_query($form, $dbh, $query, $form->{"id"}); + + $dbh->commit(); $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub get_lead { + +sub buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; @@ -540,404 +726,93 @@ sub get_lead { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = - qq|SELECT l.id, l.lead - FROM leads l - WHERE l.id = $form->{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|; + my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; + $form->{ALL} = []; + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + push @{ $form->{ALL} }, $ref; + } $sth->finish; - $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub save_lead { +sub get_buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; + my $query; # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{lead} =~ s/\'/\'\'/g; - - # id is the old record if ($form->{id}) { - $query = qq|UPDATE leads SET - lead = '$form->{description}' - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO leads - (lead) - VALUES ('$form->{description}')|; - } - $dbh->do($query) || $form->dberror($query); + $query = + qq|SELECT 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 + WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); - $dbh->disconnect; + my $ref = $sth->fetchrow_hashref("NAME_lc"); - $main::lxdebug->leave_sub(); -} - -sub delete_lead { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $query = qq|DELETE FROM leads - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub business { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT id, description, discount, customernumberinit, salesman - FROM business - ORDER BY 2|; - - $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_business { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = - qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman - FROM business b - WHERE b.id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub save_business { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $form->{description} =~ s/\'/\'\'/g; - $form->{discount} /= 100; - $form->{salesman} *= 1; - - # id is the old record - if ($form->{id}) { - $query = qq|UPDATE business SET - description = '$form->{description}', - discount = $form->{discount}, - customernumberinit = '$form->{customernumberinit}', - salesman = '$form->{salesman}' - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO business - (description, discount, customernumberinit, salesman) - VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|; - } - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub delete_business { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $query = qq|DELETE FROM business - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - - -sub language { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form, $return_list) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = - "SELECT id, description, template_code, article_code, " . - " output_numberformat, output_dateformat, output_longdates " . - "FROM language ORDER BY description"; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ary = []; - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push(@{ $ary }, $ref); - } - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); - - if ($return_list) { - return @{$ary}; - } else { - $form->{ALL} = $ary; - } -} - -sub get_language { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = - "SELECT description, template_code, article_code, " . - " output_numberformat, output_dateformat, output_longdates " . - "FROM language 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; - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_language_details { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form, $id) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = - "SELECT template_code, " . - " output_numberformat, output_dateformat, output_longdates " . - "FROM language WHERE id = ?"; - my @res = $dbh->selectrow_array($query, undef, $id); - $dbh->disconnect; - - $main::lxdebug->leave_sub(); - - return @res; -} - -sub save_language { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - my (@values, $query); - - map({ push(@values, $form->{$_}); } - qw(description template_code article_code - output_numberformat output_dateformat output_longdates)); - - # id is the old record - if ($form->{id}) { - $query = - "UPDATE language SET " . - " description = ?, template_code = ?, article_code = ?, " . - " output_numberformat = ?, output_dateformat = ?, " . - " output_longdates = ? " . - "WHERE id = ?"; - push(@values, $form->{id}); - } else { - $query = - "INSERT INTO language (" . - " description, template_code, article_code, " . - " output_numberformat, output_dateformat, output_longdates" . - ") VALUES (?, ?, ?, ?, ?, ?)"; - } - $dbh->do($query, undef, @values) || - $form->dberror($query . " (" . join(", ", @values) . ")"); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub delete_language { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - 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 = ?"; - do_query($form, $dbh, $query, $form->{"id"}); - - $dbh->commit(); - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - - -sub buchungsgruppe { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # 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 sortkey|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $form->{ALL} = []; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_buchungsgruppe { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - if ($form->{id}) { - my $query = - qq|SELECT 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 - 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; + map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; - my $query = + $query = qq|SELECT count(id) = 0 AS orphaned FROM parts WHERE buchungsgruppen_id = ?|; - ($form->{orphaned}) = $dbh->selectrow_array($query, undef, $form->{id}); - $form->dberror($query . " ($form->{id})") if ($dbh->err); + ($form->{orphaned}) = selectrow_query($form, $dbh, $query, $form->{id}); } $query = "SELECT inventory_accno_id, income_accno_id, expense_accno_id ". "FROM defaults"; ($form->{"std_inventory_accno_id"}, $form->{"std_income_accno_id"}, - $form->{"std_expense_accno_id"}) = $dbh->selectrow_array($query); + $form->{"std_expense_accno_id"}) = selectrow_query($form, $dbh, $query); my $module = "IC"; $query = qq|SELECT c.accno, c.description, c.link, c.id, @@ -949,7 +824,7 @@ sub get_buchungsgruppe { my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { foreach my $key (split(/:/, $ref->{link})) { if (!$form->{"std_inventory_accno_id"} && ($key eq "IC")) { $form->{"std_inventory_accno_id"} = $ref->{"id"}; @@ -1018,569 +893,264 @@ sub save_buchungsgruppe { income_accno_id_1, expense_accno_id_1, income_accno_id_2, expense_accno_id_2, income_accno_id_3, expense_accno_id_3, - sortkey) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - } - do_query($form, $dbh, $query, @values); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub delete_buchungsgruppe { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $query = qq|DELETE FROM buchungsgruppen WHERE id = ?|; - do_query($form, $dbh, $query, $form->{id}); - - $dbh->disconnect; - - $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(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT id, printer_description, template_code, printer_command - FROM printers - ORDER BY 2|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $form->{"ALL"} = []; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_printer { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = - qq|SELECT p.printer_description, p.template_code, p.printer_command - FROM printers p - WHERE p.id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $ref = $sth->fetchrow_hashref(NAME_lc); - - map { $form->{$_} = $ref->{$_} } keys %$ref; - - $sth->finish; - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub save_printer { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $form->{printer_description} =~ s/\'/\'\'/g; - $form->{printer_command} =~ s/\'/\'\'/g; - $form->{template_code} =~ s/\'/\'\'/g; - - - # id is the old record - if ($form->{id}) { - $query = qq|UPDATE printers SET - printer_description = '$form->{printer_description}', - template_code = '$form->{template_code}', - printer_command = '$form->{printer_command}' - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO printers - (printer_description, template_code, printer_command) - VALUES ('$form->{printer_description}', '$form->{template_code}', '$form->{printer_command}')|; - } - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub delete_printer { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - $query = qq|DELETE FROM printers - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub payment { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - 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)) { - push @{ $form->{ALL} }, $ref; - } - - $sth->finish; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); -} - -sub get_payment { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query = qq|SELECT * FROM payment_terms 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(); - - $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(); -} - -sub save_payment { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - 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); - - } else { - $query = - qq|DELETE FROM translation_payment_terms | . - qq|WHERE payment_terms_id = ?|; - do_query($form, $dbh, $query, $form->{"id"}); + sortkey) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; } - - $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); - } - $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(); } -sub delete_payment { +sub delete_buchungsgruppe { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - my $query = - qq|DELETE FROM translation_payment_terms WHERE payment_terms_id = ?|; - do_query($form, $dbh, $query, $form->{"id"}); + my $dbh = $form->dbconnect($myconfig); - $query = qq|DELETE FROM payment_terms WHERE id = ?|; - do_query($form, $dbh, $query, $form->{"id"}); + my $query = qq|DELETE FROM buchungsgruppen WHERE id = ?|; + do_query($form, $dbh, $query, $form->{id}); - $dbh->commit(); $dbh->disconnect; $main::lxdebug->leave_sub(); } -sub sic { +sub swap_sortkeys { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $table) = @_; # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->get_standard_dbh($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); - my $query = qq|SELECT code, sictype, description - FROM sic - ORDER BY code|; + $query = qq|UPDATE $table SET sortkey = ? WHERE id = ?|; + my $sth = prepare_query($form, $dbh, $query); - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + do_statement($form, $sth, $query, $sortkeys[1], $form->{"id1"}); + do_statement($form, $sth, $query, $sortkeys[0], $form->{"id2"}); - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{ALL} }, $ref; - } + $sth->finish(); - $sth->finish; - $dbh->disconnect; + $dbh->commit(); $main::lxdebug->leave_sub(); } -sub get_sic { +sub prepare_template_filename { $main::lxdebug->enter_sub(); my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my ($filename, $display_filename); - my $query = qq|SELECT s.code, s.sictype, s.description - FROM sic s - WHERE s.code = '$form->{code}'|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + if ($form->{type} eq "stylesheet") { + $filename = "css/$myconfig->{stylesheet}"; + $display_filename = $myconfig->{stylesheet}; - my $ref = $sth->fetchrow_hashref(NAME_lc); + } else { + $filename = $form->{formname}; - map { $form->{$_} = $ref->{$_} } keys %$ref; + if ($form->{language}) { + my ($id, $template_code) = split(/--/, $form->{language}); + $filename .= "_${template_code}"; + } - $sth->finish; + if ($form->{printer}) { + my ($id, $template_code) = split(/--/, $form->{printer}); + $filename .= "_${template_code}"; + } - $dbh->disconnect; + $filename .= "." . ($form->{format} eq "html" ? "html" : "tex"); + if ($form->{"formname"} =~ m|\.\.| || $form->{"formname"} =~ m|^/|) { + $filename =~ s|.*/||; + } + $display_filename = $filename; + $filename = "$myconfig->{templates}/$filename"; + } $main::lxdebug->leave_sub(); + + return ($filename, $display_filename); } -sub save_sic { + +sub load_template { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $filename) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my ($content, $lines) = ("", 0); - $form->{code} =~ s/\'/\'\'/g; - $form->{description} =~ s/\'/\'\'/g; + local *TEMPLATE; - # if there is an id - if ($form->{id}) { - $query = qq|UPDATE sic SET - code = '$form->{code}', - sictype = '$form->{sictype}', - description = '$form->{description}' - WHERE code = '$form->{id}'|; - } else { - $query = qq|INSERT INTO sic - (code, sictype, description) - VALUES ('$form->{code}', '$form->{sictype}', '$form->{description}')|; + if (open(TEMPLATE, $filename)) { + while (