1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Administration module
36 #======================================================================
44 $main::lxdebug->enter_sub();
46 my ($self, $myconfig, $form) = @_;
49 my $dbh = $form->dbconnect($myconfig);
51 qq!SELECT c.accno, c.description, c.charttype, c.gifi_accno, c.category,! .
52 qq! c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, ! .
54 qq! tk.taxkey_id, tk.pos_ustva, tk.tax_id, ! .
55 qq! tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate ! .
57 qq!LEFT JOIN taxkeys tk ! .
58 qq!ON (c.id=tk.chart_id AND tk.id = ! .
59 qq! (SELECT id FROM taxkeys ! .
60 qq! WHERE taxkeys.chart_id = c.id AND startdate <= current_date ! .
61 qq! ORDER BY startdate DESC LIMIT 1)) ! .
64 my $sth = $dbh->prepare($query);
65 $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
67 my $ref = $sth->fetchrow_hashref(NAME_lc);
69 foreach my $key (keys %$ref) {
70 $form->{"$key"} = $ref->{"$key"};
75 # get default accounts
76 $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id
78 $sth = $dbh->prepare($query);
79 $sth->execute || $form->dberror($query);
81 $ref = $sth->fetchrow_hashref(NAME_lc);
83 map { $form->{$_} = $ref->{$_} } keys %ref;
87 # get taxkeys and description
88 $query = qq§SELECT id, taxkey,id||'--'||taxkey AS tax, taxdescription
89 FROM tax ORDER BY taxkey§;
90 $sth = $dbh->prepare($query);
91 $sth->execute || $form->dberror($query);
95 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
96 push @{ $form->{TAXKEY} }, $ref;
102 $query = qq|SELECT id, accno,description
103 FROM chart WHERE link = ?|;
104 $sth = $dbh->prepare($query);
105 $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})");
107 $form->{NEWACCOUNT} = [];
108 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
109 push @{ $form->{NEWACCOUNT} }, $ref;
114 # check if we have any transactions
115 $query = qq|SELECT a.trans_id FROM acc_trans a
116 WHERE a.chart_id = ?|;
117 $sth = $dbh->prepare($query);
118 $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
120 ($form->{orphaned}) = $sth->fetchrow_array;
121 $form->{orphaned} = !$form->{orphaned};
124 # check if new account is active
125 $form->{new_chart_valid} = 0;
126 if ($form->{new_chart_id}) {
127 $query = qq|SELECT current_date-valid_from FROM chart
129 my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
131 $form->{new_chart_valid} = 1;
138 $main::lxdebug->leave_sub();
142 $main::lxdebug->enter_sub();
144 my ($self, $myconfig, $form) = @_;
146 # connect to database, turn off AutoCommit
147 my $dbh = $form->dbconnect_noauto($myconfig);
149 # sanity check, can't have AR with AR_...
150 if ($form->{AR} || $form->{AP} || $form->{IC}) {
151 map { delete $form->{$_} }
152 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);
156 foreach my $item ($form->{AR}, $form->{AR_amount},
157 $form->{AR_tax}, $form->{AR_paid},
158 $form->{AP}, $form->{AP_amount},
159 $form->{AP_tax}, $form->{AP_paid},
160 $form->{IC}, $form->{IC_sale},
161 $form->{IC_cogs}, $form->{IC_taxpart},
162 $form->{IC_income}, $form->{IC_expense},
163 $form->{IC_taxservice}, $form->{CT_tax}
165 $form->{link} .= "${item}:" if ($item);
169 # strip blanks from accno
170 map { $form->{$_} =~ s/ //g; } qw(accno);
174 if ($form->{id} eq "NULL") {
180 my ($tax_id, $taxkey) = split(/--/, $form->{tax});
181 my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01";
183 if ($form->{id} && $form->{orphaned}) {
184 $query = qq|UPDATE chart SET
185 accno = ?, description = ?, charttype = ?,
186 gifi_accno = ?, category = ?, link = ?,
188 pos_ustva = ?, pos_bwa = ?, pos_bilanz = ?,
189 pos_eur = ?, new_chart_id = ?, valid_from = ?
191 @values = ($form->{accno}, $form->{description}, $form->{charttype},
192 $form->{gifi_accno}, $form->{category}, $form->{link},
194 conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
195 conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
196 conv_i($form->{new_chart_id}),
197 conv_date($form->{valid_from}),
200 } elsif ($form->{id} && !$form->{new_chart_valid}) {
201 $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ?
203 @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}),
206 $query = qq|INSERT INTO chart
207 (accno, description, charttype,
208 gifi_accno, category, link,
210 pos_ustva, pos_bwa, pos_bilanz, pos_eur,
211 new_chart_id, valid_from)
212 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
213 @values = ($form->{accno}, $form->{description}, $form->{charttype},
214 $form->{gifi_accno}, $form->{category}, $form->{link},
216 conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}),
217 conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}),
218 conv_i($form->{new_chart_id}),
219 conv_date($form->{valid_from}));
222 do_query($form, $dbh, $query, @values);
227 qq|INSERT INTO taxkeys | .
228 qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
229 qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|;
230 do_query($form, $dbh, $query,
231 $form->{accno}, conv_i($tax_id), conv_i($taxkey),
232 conv_i($form->{pos_ustva}), conv_date($startdate));
235 $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|;
236 do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id));
239 qq|INSERT INTO taxkeys | .
240 qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | .
241 qq|VALUES (?, ?, ?, ?, ?)|;
242 do_query($form, $dbh, $query,
243 $form->{id}, conv_i($tax_id), conv_i($taxkey),
244 conv_i($form->{pos_ustva}), conv_date($startdate));
248 my $rc = $dbh->commit;
251 $main::lxdebug->leave_sub();
257 $main::lxdebug->enter_sub();
259 my ($self, $myconfig, $form) = @_;
261 # connect to database, turn off AutoCommit
262 my $dbh = $form->dbconnect_noauto($myconfig);
264 my $query = qq|SELECT count(*) FROM acc_trans a
265 WHERE a.chart_id = ?|;
266 my ($count) = selectrow_query($form, $dbh, $query, $form->{id});
270 $main::lxdebug->leave_sub();
274 # set inventory_accno_id, income_accno_id, expense_accno_id to defaults
275 foreach my $type (qw(inventory income expense)) {
278 qq|SET ${type}_accno_id = (SELECT ${type}_accno_id FROM defaults) | .
279 qq|WHERE ${type}_accno_id = ?|;
280 do_query($form, $dbh, $query, $form->{id});
283 foreach my $table (qw(partstax customertax vendortax tax)) {
284 $query = qq|DELETE FROM $table
286 do_query($form, $dbh, $query, $form->{id});
289 # delete chart of account record
290 $query = qq|DELETE FROM chart
292 do_query($form, $dbh, $query, $form->{id});
294 # commit and redirect
295 my $rc = $dbh->commit;
298 $main::lxdebug->leave_sub();
304 $main::lxdebug->enter_sub();
306 my ($self, $myconfig, $form) = @_;
308 # connect to database
309 my $dbh = $form->dbconnect($myconfig);
311 my $query = qq|SELECT id, description
315 $sth = $dbh->prepare($query);
316 $sth->execute || $form->dberror($query);
318 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
319 push @{ $form->{ALL} }, $ref;
325 $main::lxdebug->leave_sub();
329 $main::lxdebug->enter_sub();
331 my ($self, $myconfig, $form) = @_;
333 # connect to database
334 my $dbh = $form->dbconnect($myconfig);
336 my $query = qq|SELECT w.description
338 WHERE w.id = $form->{id}|;
339 my $sth = $dbh->prepare($query);
340 $sth->execute || $form->dberror($query);
342 my $ref = $sth->fetchrow_hashref(NAME_lc);
344 map { $form->{$_} = $ref->{$_} } keys %$ref;
348 # see if it is in use
349 $query = qq|SELECT count(*) FROM inventory i
350 WHERE i.warehouse_id = $form->{id}|;
351 $sth = $dbh->prepare($query);
352 $sth->execute || $form->dberror($query);
354 ($form->{orphaned}) = $sth->fetchrow_array;
355 $form->{orphaned} = !$form->{orphaned};
360 $main::lxdebug->leave_sub();
364 $main::lxdebug->enter_sub();
366 my ($self, $myconfig, $form) = @_;
368 # connect to database
369 my $dbh = $form->dbconnect($myconfig);
371 $form->{description} =~ s/\'/\'\'/g;
374 $query = qq|UPDATE warehouse SET
375 description = '$form->{description}'
376 WHERE id = $form->{id}|;
378 $query = qq|INSERT INTO warehouse
380 VALUES ('$form->{description}')|;
382 $dbh->do($query) || $form->dberror($query);
386 $main::lxdebug->leave_sub();
389 sub delete_warehouse {
390 $main::lxdebug->enter_sub();
392 my ($self, $myconfig, $form) = @_;
394 # connect to database
395 my $dbh = $form->dbconnect($myconfig);
397 $query = qq|DELETE FROM warehouse
398 WHERE id = $form->{id}|;
399 $dbh->do($query) || $form->dberror($query);
403 $main::lxdebug->leave_sub();
407 $main::lxdebug->enter_sub();
409 my ($self, $myconfig, $form) = @_;
411 # connect to database
412 my $dbh = $form->dbconnect($myconfig);
414 my $query = qq|SELECT d.id, d.description, d.role
418 $sth = $dbh->prepare($query);
419 $sth->execute || $form->dberror($query);
421 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
422 push @{ $form->{ALL} }, $ref;
428 $main::lxdebug->leave_sub();
432 $main::lxdebug->enter_sub();
434 my ($self, $myconfig, $form) = @_;
436 # connect to database
437 my $dbh = $form->dbconnect($myconfig);
439 my $query = qq|SELECT d.description, d.role
441 WHERE d.id = $form->{id}|;
442 my $sth = $dbh->prepare($query);
443 $sth->execute || $form->dberror($query);
445 my $ref = $sth->fetchrow_hashref(NAME_lc);
447 map { $form->{$_} = $ref->{$_} } keys %$ref;
451 # see if it is in use
452 $query = qq|SELECT count(*) FROM dpt_trans d
453 WHERE d.department_id = $form->{id}|;
454 $sth = $dbh->prepare($query);
455 $sth->execute || $form->dberror($query);
457 ($form->{orphaned}) = $sth->fetchrow_array;
458 $form->{orphaned} = !$form->{orphaned};
463 $main::lxdebug->leave_sub();
466 sub save_department {
467 $main::lxdebug->enter_sub();
469 my ($self, $myconfig, $form) = @_;
471 # connect to database
472 my $dbh = $form->dbconnect($myconfig);
474 $form->{description} =~ s/\'/\'\'/g;
477 $query = qq|UPDATE department SET
478 description = '$form->{description}',
479 role = '$form->{role}'
480 WHERE id = $form->{id}|;
482 $query = qq|INSERT INTO department
484 VALUES ('$form->{description}', '$form->{role}')|;
486 $dbh->do($query) || $form->dberror($query);
490 $main::lxdebug->leave_sub();
493 sub delete_department {
494 $main::lxdebug->enter_sub();
496 my ($self, $myconfig, $form) = @_;
498 # connect to database
499 my $dbh = $form->dbconnect($myconfig);
501 $query = qq|DELETE FROM department
502 WHERE id = $form->{id}|;
503 $dbh->do($query) || $form->dberror($query);
507 $main::lxdebug->leave_sub();
511 $main::lxdebug->enter_sub();
513 my ($self, $myconfig, $form) = @_;
515 # connect to database
516 my $dbh = $form->dbconnect($myconfig);
518 my $query = qq|SELECT id, lead
522 $sth = $dbh->prepare($query);
523 $sth->execute || $form->dberror($query);
525 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
526 push @{ $form->{ALL} }, $ref;
532 $main::lxdebug->leave_sub();
536 $main::lxdebug->enter_sub();
538 my ($self, $myconfig, $form) = @_;
540 # connect to database
541 my $dbh = $form->dbconnect($myconfig);
544 qq|SELECT l.id, l.lead
546 WHERE l.id = $form->{id}|;
547 my $sth = $dbh->prepare($query);
548 $sth->execute || $form->dberror($query);
550 my $ref = $sth->fetchrow_hashref(NAME_lc);
552 map { $form->{$_} = $ref->{$_} } keys %$ref;
558 $main::lxdebug->leave_sub();
562 $main::lxdebug->enter_sub();
564 my ($self, $myconfig, $form) = @_;
566 # connect to database
567 my $dbh = $form->dbconnect($myconfig);
569 $form->{lead} =~ s/\'/\'\'/g;
571 # id is the old record
573 $query = qq|UPDATE leads SET
574 lead = '$form->{description}'
575 WHERE id = $form->{id}|;
577 $query = qq|INSERT INTO leads
579 VALUES ('$form->{description}')|;
581 $dbh->do($query) || $form->dberror($query);
585 $main::lxdebug->leave_sub();
589 $main::lxdebug->enter_sub();
591 my ($self, $myconfig, $form) = @_;
593 # connect to database
594 my $dbh = $form->dbconnect($myconfig);
596 $query = qq|DELETE FROM leads
597 WHERE id = $form->{id}|;
598 $dbh->do($query) || $form->dberror($query);
602 $main::lxdebug->leave_sub();
606 $main::lxdebug->enter_sub();
608 my ($self, $myconfig, $form) = @_;
610 # connect to database
611 my $dbh = $form->dbconnect($myconfig);
613 my $query = qq|SELECT id, description, discount, customernumberinit, salesman
617 $sth = $dbh->prepare($query);
618 $sth->execute || $form->dberror($query);
620 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
621 push @{ $form->{ALL} }, $ref;
627 $main::lxdebug->leave_sub();
631 $main::lxdebug->enter_sub();
633 my ($self, $myconfig, $form) = @_;
635 # connect to database
636 my $dbh = $form->dbconnect($myconfig);
639 qq|SELECT b.description, b.discount, b.customernumberinit, b.salesman
641 WHERE b.id = $form->{id}|;
642 my $sth = $dbh->prepare($query);
643 $sth->execute || $form->dberror($query);
645 my $ref = $sth->fetchrow_hashref(NAME_lc);
647 map { $form->{$_} = $ref->{$_} } keys %$ref;
653 $main::lxdebug->leave_sub();
657 $main::lxdebug->enter_sub();
659 my ($self, $myconfig, $form) = @_;
661 # connect to database
662 my $dbh = $form->dbconnect($myconfig);
664 $form->{description} =~ s/\'/\'\'/g;
665 $form->{discount} /= 100;
666 $form->{salesman} *= 1;
668 # id is the old record
670 $query = qq|UPDATE business SET
671 description = '$form->{description}',
672 discount = $form->{discount},
673 customernumberinit = '$form->{customernumberinit}',
674 salesman = '$form->{salesman}'
675 WHERE id = $form->{id}|;
677 $query = qq|INSERT INTO business
678 (description, discount, customernumberinit, salesman)
679 VALUES ('$form->{description}', $form->{discount}, '$form->{customernumberinit}', '$form->{salesman}')|;
681 $dbh->do($query) || $form->dberror($query);
685 $main::lxdebug->leave_sub();
688 sub delete_business {
689 $main::lxdebug->enter_sub();
691 my ($self, $myconfig, $form) = @_;
693 # connect to database
694 my $dbh = $form->dbconnect($myconfig);
696 $query = qq|DELETE FROM business
697 WHERE id = $form->{id}|;
698 $dbh->do($query) || $form->dberror($query);
702 $main::lxdebug->leave_sub();
707 $main::lxdebug->enter_sub();
709 my ($self, $myconfig, $form, $return_list) = @_;
711 # connect to database
712 my $dbh = $form->dbconnect($myconfig);
715 "SELECT id, description, template_code, article_code, " .
716 " output_numberformat, output_dateformat, output_longdates " .
717 "FROM language ORDER BY description";
719 $sth = $dbh->prepare($query);
720 $sth->execute || $form->dberror($query);
724 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
725 push(@{ $ary }, $ref);
731 $main::lxdebug->leave_sub();
741 $main::lxdebug->enter_sub();
743 my ($self, $myconfig, $form) = @_;
745 # connect to database
746 my $dbh = $form->dbconnect($myconfig);
749 "SELECT description, template_code, article_code, " .
750 " output_numberformat, output_dateformat, output_longdates " .
751 "FROM language WHERE id = ?";
752 my $sth = $dbh->prepare($query);
753 $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
755 my $ref = $sth->fetchrow_hashref(NAME_lc);
757 map { $form->{$_} = $ref->{$_} } keys %$ref;
763 $main::lxdebug->leave_sub();
766 sub get_language_details {
767 $main::lxdebug->enter_sub();
769 my ($self, $myconfig, $form, $id) = @_;
771 # connect to database
772 my $dbh = $form->dbconnect($myconfig);
775 "SELECT template_code, " .
776 " output_numberformat, output_dateformat, output_longdates " .
777 "FROM language WHERE id = ?";
778 my @res = $dbh->selectrow_array($query, undef, $id);
781 $main::lxdebug->leave_sub();
787 $main::lxdebug->enter_sub();
789 my ($self, $myconfig, $form) = @_;
791 # connect to database
792 my $dbh = $form->dbconnect($myconfig);
793 my (@values, $query);
795 map({ push(@values, $form->{$_}); }
796 qw(description template_code article_code
797 output_numberformat output_dateformat output_longdates));
799 # id is the old record
802 "UPDATE language SET " .
803 " description = ?, template_code = ?, article_code = ?, " .
804 " output_numberformat = ?, output_dateformat = ?, " .
805 " output_longdates = ? " .
807 push(@values, $form->{id});
810 "INSERT INTO language (" .
811 " description, template_code, article_code, " .
812 " output_numberformat, output_dateformat, output_longdates" .
813 ") VALUES (?, ?, ?, ?, ?, ?)";
815 $dbh->do($query, undef, @values) ||
816 $form->dberror($query . " (" . join(", ", @values) . ")");
820 $main::lxdebug->leave_sub();
823 sub delete_language {
824 $main::lxdebug->enter_sub();
826 my ($self, $myconfig, $form) = @_;
828 # connect to database
829 my $dbh = $form->dbconnect_noauto($myconfig);
831 foreach my $table (qw(translation_payment_terms units_language)) {
832 my $query = qq|DELETE FROM $table WHERE language_id = ?|;
833 do_query($form, $dbh, $query, $form->{"id"});
836 $query = "DELETE FROM language WHERE id = ?";
837 do_query($form, $dbh, $query, $form->{"id"});
842 $main::lxdebug->leave_sub();
847 $main::lxdebug->enter_sub();
849 my ($self, $myconfig, $form) = @_;
851 # connect to database
852 my $dbh = $form->dbconnect($myconfig);
854 my $query = qq|SELECT id, description,
856 (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno,
858 (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0,
860 (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0,
862 (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1,
864 (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1,
866 (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2,
868 (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2,
870 (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3,
872 (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3
876 $sth = $dbh->prepare($query);
877 $sth->execute || $form->dberror($query);
880 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
881 push @{ $form->{ALL} }, $ref;
887 $main::lxdebug->leave_sub();
890 sub get_buchungsgruppe {
891 $main::lxdebug->enter_sub();
893 my ($self, $myconfig, $form) = @_;
895 # connect to database
896 my $dbh = $form->dbconnect($myconfig);
900 qq|SELECT description, inventory_accno_id,
901 (SELECT accno FROM chart WHERE id = inventory_accno_id) AS inventory_accno,
903 (SELECT accno FROM chart WHERE id = income_accno_id_0) AS income_accno_0,
905 (SELECT accno FROM chart WHERE id = expense_accno_id_0) AS expense_accno_0,
907 (SELECT accno FROM chart WHERE id = income_accno_id_1) AS income_accno_1,
909 (SELECT accno FROM chart WHERE id = expense_accno_id_1) AS expense_accno_1,
911 (SELECT accno FROM chart WHERE id = income_accno_id_2) AS income_accno_2,
913 (select accno FROM chart WHERE id = expense_accno_id_2) AS expense_accno_2,
915 (SELECT accno FROM chart WHERE id = income_accno_id_3) AS income_accno_3,
917 (SELECT accno FROM chart WHERE id = expense_accno_id_3) AS expense_accno_3
920 my $sth = $dbh->prepare($query);
921 $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})");
923 my $ref = $sth->fetchrow_hashref(NAME_lc);
925 map { $form->{$_} = $ref->{$_} } keys %$ref;
930 qq|SELECT count(id) = 0 AS orphaned
932 WHERE buchungsgruppen_id = ?|;
933 ($form->{orphaned}) = $dbh->selectrow_array($query, undef, $form->{id});
934 $form->dberror($query . " ($form->{id})") if ($dbh->err);
937 $query = "SELECT inventory_accno_id, income_accno_id, expense_accno_id ".
939 ($form->{"std_inventory_accno_id"}, $form->{"std_income_accno_id"},
940 $form->{"std_expense_accno_id"}) = $dbh->selectrow_array($query);
943 $query = qq|SELECT c.accno, c.description, c.link, c.id,
944 d.inventory_accno_id, d.income_accno_id, d.expense_accno_id
945 FROM chart c, defaults d
946 WHERE c.link LIKE '%$module%'
950 my $sth = $dbh->prepare($query);
951 $sth->execute || $form->dberror($query);
952 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
953 foreach my $key (split(/:/, $ref->{link})) {
954 if (!$form->{"std_inventory_accno_id"} && ($key eq "IC")) {
955 $form->{"std_inventory_accno_id"} = $ref->{"id"};
957 if ($key =~ /$module/) {
958 if ( ($ref->{id} eq $ref->{inventory_accno_id})
959 || ($ref->{id} eq $ref->{income_accno_id})
960 || ($ref->{id} eq $ref->{expense_accno_id})) {
961 push @{ $form->{"${module}_links"}{$key} },
962 { accno => $ref->{accno},
963 description => $ref->{description},
964 selected => "selected",
967 push @{ $form->{"${module}_links"}{$key} },
968 { accno => $ref->{accno},
969 description => $ref->{description},
981 $main::lxdebug->leave_sub();
984 sub save_buchungsgruppe {
985 $main::lxdebug->enter_sub();
987 my ($self, $myconfig, $form) = @_;
989 # connect to database
990 my $dbh = $form->dbconnect($myconfig);
992 my @values = ($form->{description}, $form->{inventory_accno_id},
993 $form->{income_accno_id_0}, $form->{expense_accno_id_0},
994 $form->{income_accno_id_1}, $form->{expense_accno_id_1},
995 $form->{income_accno_id_2}, $form->{expense_accno_id_2},
996 $form->{income_accno_id_3}, $form->{expense_accno_id_3});
1000 # id is the old record
1002 $query = qq|UPDATE buchungsgruppen SET
1003 description = ?, inventory_accno_id = ?,
1004 income_accno_id_0 = ?, expense_accno_id_0 = ?,
1005 income_accno_id_1 = ?, expense_accno_id_1 = ?,
1006 income_accno_id_2 = ?, expense_accno_id_2 = ?,
1007 income_accno_id_3 = ?, expense_accno_id_3 = ?
1009 push(@values, $form->{id});
1011 $query = qq|SELECT COALESCE(MAX(sortkey) + 1, 1) FROM buchungsgruppen|;
1012 my ($sortkey) = $dbh->selectrow_array($query);
1013 $form->dberror($query) if ($dbh->err);
1014 push(@values, $sortkey);
1015 $query = qq|INSERT INTO buchungsgruppen
1016 (description, inventory_accno_id,
1017 income_accno_id_0, expense_accno_id_0,
1018 income_accno_id_1, expense_accno_id_1,
1019 income_accno_id_2, expense_accno_id_2,
1020 income_accno_id_3, expense_accno_id_3,
1022 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|;
1024 do_query($form, $dbh, $query, @values);
1028 $main::lxdebug->leave_sub();
1031 sub delete_buchungsgruppe {
1032 $main::lxdebug->enter_sub();
1034 my ($self, $myconfig, $form) = @_;
1036 # connect to database
1037 my $dbh = $form->dbconnect($myconfig);
1039 $query = qq|DELETE FROM buchungsgruppen WHERE id = ?|;
1040 do_query($form, $dbh, $query, $form->{id});
1044 $main::lxdebug->leave_sub();
1048 $main::lxdebug->enter_sub();
1050 my ($self, $myconfig, $form, $table) = @_;
1052 # connect to database
1053 my $dbh = $form->dbconnect_noauto($myconfig);
1057 (SELECT sortkey FROM $table WHERE id = ?) AS sortkey1,
1058 (SELECT sortkey FROM $table WHERE id = ?) AS sortkey2|;
1059 my @values = ($form->{"id1"}, $form->{"id2"});
1060 my @sortkeys = selectrow_query($form, $dbh, $query, @values);
1061 $main::lxdebug->dump(0, "v", \@values);
1062 $main::lxdebug->dump(0, "s", \@sortkeys);
1064 $query = qq|UPDATE $table SET sortkey = ? WHERE id = ?|;
1065 my $sth = $dbh->prepare($query);
1066 $sth->execute($sortkeys[1], $form->{"id1"}) ||
1067 $form->dberror($query . " ($sortkeys[1], $form->{id1})");
1068 $sth->execute($sortkeys[0], $form->{"id2"}) ||
1069 $form->dberror($query . " ($sortkeys[0], $form->{id2})");
1075 $main::lxdebug->leave_sub();
1079 $main::lxdebug->enter_sub();
1081 my ($self, $myconfig, $form) = @_;
1083 # connect to database
1084 my $dbh = $form->dbconnect($myconfig);
1086 my $query = qq|SELECT id, printer_description, template_code, printer_command
1090 $sth = $dbh->prepare($query);
1091 $sth->execute || $form->dberror($query);
1093 $form->{"ALL"} = [];
1094 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1095 push @{ $form->{ALL} }, $ref;
1101 $main::lxdebug->leave_sub();
1105 $main::lxdebug->enter_sub();
1107 my ($self, $myconfig, $form) = @_;
1109 # connect to database
1110 my $dbh = $form->dbconnect($myconfig);
1113 qq|SELECT p.printer_description, p.template_code, p.printer_command
1115 WHERE p.id = $form->{id}|;
1116 my $sth = $dbh->prepare($query);
1117 $sth->execute || $form->dberror($query);
1119 my $ref = $sth->fetchrow_hashref(NAME_lc);
1121 map { $form->{$_} = $ref->{$_} } keys %$ref;
1127 $main::lxdebug->leave_sub();
1131 $main::lxdebug->enter_sub();
1133 my ($self, $myconfig, $form) = @_;
1135 # connect to database
1136 my $dbh = $form->dbconnect($myconfig);
1138 $form->{printer_description} =~ s/\'/\'\'/g;
1139 $form->{printer_command} =~ s/\'/\'\'/g;
1140 $form->{template_code} =~ s/\'/\'\'/g;
1143 # id is the old record
1145 $query = qq|UPDATE printers SET
1146 printer_description = '$form->{printer_description}',
1147 template_code = '$form->{template_code}',
1148 printer_command = '$form->{printer_command}'
1149 WHERE id = $form->{id}|;
1151 $query = qq|INSERT INTO printers
1152 (printer_description, template_code, printer_command)
1153 VALUES ('$form->{printer_description}', '$form->{template_code}', '$form->{printer_command}')|;
1155 $dbh->do($query) || $form->dberror($query);
1159 $main::lxdebug->leave_sub();
1162 sub delete_printer {
1163 $main::lxdebug->enter_sub();
1165 my ($self, $myconfig, $form) = @_;
1167 # connect to database
1168 my $dbh = $form->dbconnect($myconfig);
1170 $query = qq|DELETE FROM printers
1171 WHERE id = $form->{id}|;
1172 $dbh->do($query) || $form->dberror($query);
1176 $main::lxdebug->leave_sub();
1180 $main::lxdebug->enter_sub();
1182 my ($self, $myconfig, $form) = @_;
1184 # connect to database
1185 my $dbh = $form->dbconnect($myconfig);
1187 my $query = qq|SELECT * FROM payment_terms ORDER BY sortkey|;
1189 $sth = $dbh->prepare($query);
1190 $sth->execute || $form->dberror($query);
1193 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1194 push @{ $form->{ALL} }, $ref;
1200 $main::lxdebug->leave_sub();
1204 $main::lxdebug->enter_sub();
1206 my ($self, $myconfig, $form) = @_;
1208 # connect to database
1209 my $dbh = $form->dbconnect($myconfig);
1211 my $query = qq|SELECT * FROM payment_terms WHERE id = ?|;
1212 my $sth = $dbh->prepare($query);
1213 $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
1215 my $ref = $sth->fetchrow_hashref(NAME_lc);
1216 map { $form->{$_} = $ref->{$_} } keys %$ref;
1220 qq|SELECT t.language_id, t.description_long, l.description AS language | .
1221 qq|FROM translation_payment_terms t | .
1222 qq|LEFT JOIN language l ON t.language_id = l.id | .
1223 qq|WHERE t.payment_terms_id = ? | .
1225 qq|SELECT l.id AS language_id, NULL AS description_long, | .
1226 qq|l.description AS language | .
1227 qq|FROM language l|;
1228 $sth = $dbh->prepare($query);
1229 $sth->execute($form->{"id"}) || $form->dberror($query . " ($form->{id})");
1232 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1233 $mapping{ $ref->{"language_id"} } = $ref
1234 unless (defined($mapping{ $ref->{"language_id"} }));
1238 $form->{"TRANSLATION"} = [sort({ $a->{"language"} cmp $b->{"language"} }
1243 $main::lxdebug->leave_sub();
1247 $main::lxdebug->enter_sub();
1249 my ($self, $myconfig, $form) = @_;
1251 # connect to database
1252 my $dbh = $form->dbconnect_noauto($myconfig);
1257 $query = qq|SELECT nextval('id'), COALESCE(MAX(sortkey) + 1, 1) | .
1258 qq|FROM payment_terms|;
1260 ($form->{id}, $sortkey) = selectrow_query($form, $dbh, $query);
1262 $query = qq|INSERT INTO payment_terms (id, sortkey) VALUES (?, ?)|;
1263 do_query($form, $dbh, $query, $form->{id}, $sortkey);
1267 qq|DELETE FROM translation_payment_terms | .
1268 qq|WHERE payment_terms_id = ?|;
1269 do_query($form, $dbh, $query, $form->{"id"});
1272 $query = qq|UPDATE payment_terms SET
1273 description = ?, description_long = ?,
1275 terms_netto = ?, terms_skonto = ?,
1278 my @values = ($form->{description}, $form->{description_long},
1279 $form->{ranking} * 1,
1280 $form->{terms_netto} * 1, $form->{terms_skonto} * 1,
1281 $form->{percent_skonto} * 1,
1283 do_query($form, $dbh, $query, @values);
1285 $query = qq|SELECT id FROM language|;
1287 my $sth = $dbh->prepare($query);
1288 $sth->execute() || $form->dberror($query);
1290 while (my ($id) = $sth->fetchrow_array()) {
1291 push(@language_ids, $id);
1296 qq|INSERT INTO translation_payment_terms | .
1297 qq|(language_id, payment_terms_id, description_long) | .
1298 qq|VALUES (?, ?, ?)|;
1299 $sth = $dbh->prepare($query);
1301 foreach my $language_id (@language_ids) {
1302 do_statement($form, $sth, $query, $language_id, $form->{"id"},
1303 $form->{"description_long_${language_id}"});
1310 $main::lxdebug->leave_sub();
1313 sub delete_payment {
1314 $main::lxdebug->enter_sub();
1316 my ($self, $myconfig, $form) = @_;
1318 # connect to database
1319 my $dbh = $form->dbconnect_noauto($myconfig);
1322 qq|DELETE FROM translation_payment_terms WHERE payment_terms_id = ?|;
1323 do_query($form, $dbh, $query, $form->{"id"});
1325 $query = qq|DELETE FROM payment_terms WHERE id = ?|;
1326 do_query($form, $dbh, $query, $form->{"id"});
1331 $main::lxdebug->leave_sub();
1335 $main::lxdebug->enter_sub();
1337 my ($self, $myconfig, $form) = @_;
1339 # connect to database
1340 my $dbh = $form->dbconnect($myconfig);
1342 my $query = qq|SELECT code, sictype, description
1346 $sth = $dbh->prepare($query);
1347 $sth->execute || $form->dberror($query);
1349 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1350 push @{ $form->{ALL} }, $ref;
1356 $main::lxdebug->leave_sub();
1360 $main::lxdebug->enter_sub();
1362 my ($self, $myconfig, $form) = @_;
1364 # connect to database
1365 my $dbh = $form->dbconnect($myconfig);
1367 my $query = qq|SELECT s.code, s.sictype, s.description
1369 WHERE s.code = '$form->{code}'|;
1370 my $sth = $dbh->prepare($query);
1371 $sth->execute || $form->dberror($query);
1373 my $ref = $sth->fetchrow_hashref(NAME_lc);
1375 map { $form->{$_} = $ref->{$_} } keys %$ref;
1381 $main::lxdebug->leave_sub();
1385 $main::lxdebug->enter_sub();
1387 my ($self, $myconfig, $form) = @_;
1389 # connect to database
1390 my $dbh = $form->dbconnect($myconfig);
1392 $form->{code} =~ s/\'/\'\'/g;
1393 $form->{description} =~ s/\'/\'\'/g;
1397 $query = qq|UPDATE sic SET
1398 code = '$form->{code}',
1399 sictype = '$form->{sictype}',
1400 description = '$form->{description}'
1401 WHERE code = '$form->{id}'|;
1403 $query = qq|INSERT INTO sic
1404 (code, sictype, description)
1405 VALUES ('$form->{code}', '$form->{sictype}', '$form->{description}')|;
1407 $dbh->do($query) || $form->dberror($query);
1411 $main::lxdebug->leave_sub();
1415 $main::lxdebug->enter_sub();
1417 my ($self, $myconfig, $form) = @_;
1419 # connect to database
1420 my $dbh = $form->dbconnect($myconfig);
1422 $query = qq|DELETE FROM sic
1423 WHERE code = '$form->{code}'|;
1424 $dbh->do($query) || $form->dberror($query);
1428 $main::lxdebug->leave_sub();
1432 $main::lxdebug->enter_sub();
1434 my ($self, $form) = @_;
1436 open(TEMPLATE, "$form->{file}") or $form->error("$form->{file} : $!");
1438 while (<TEMPLATE>) {
1439 $form->{body} .= $_;
1444 $main::lxdebug->leave_sub();
1448 $main::lxdebug->enter_sub();
1450 my ($self, $form) = @_;
1452 open(TEMPLATE, ">$form->{file}") or $form->error("$form->{file} : $!");
1455 $form->{body} =~ s/\r\n/\n/g;
1456 print TEMPLATE $form->{body};
1460 $main::lxdebug->leave_sub();
1463 sub save_preferences {
1464 $main::lxdebug->enter_sub();
1466 my ($self, $myconfig, $form, $memberfile, $userspath, $webdav) = @_;
1468 map { ($form->{$_}) = split(/--/, $form->{$_}) }
1469 qw(inventory_accno income_accno expense_accno fxgain_accno fxloss_accno);
1472 $form->{curr} =~ s/ //g;
1473 map { push(@a, uc pack "A3", $_) if $_ } split(/:/, $form->{curr});
1474 $form->{curr} = join ':', @a;
1476 # connect to database
1477 my $dbh = $form->dbconnect_noauto($myconfig);
1479 # these defaults are database wide
1480 # user specific variables are in myconfig
1482 my $query = qq|UPDATE defaults SET
1483 inventory_accno_id =
1484 (SELECT c.id FROM chart c
1485 WHERE c.accno = '$form->{inventory_accno}'),
1487 (SELECT c.id FROM chart c
1488 WHERE c.accno = '$form->{income_accno}'),
1490 (SELECT c.id FROM chart c
1491 WHERE c.accno = '$form->{expense_accno}'),
1493 (SELECT c.id FROM chart c
1494 WHERE c.accno = '$form->{fxgain_accno}'),
1496 (SELECT c.id FROM chart c
1497 WHERE c.accno = '$form->{fxloss_accno}'),
1498 invnumber = '$form->{invnumber}',
1499 cnnumber = '$form->{cnnumber}',
1500 sonumber = '$form->{sonumber}',
1501 ponumber = '$form->{ponumber}',
1502 sqnumber = '$form->{sqnumber}',
1503 rfqnumber = '$form->{rfqnumber}',
1504 customernumber = '$form->{customernumber}',
1505 vendornumber = '$form->{vendornumber}',
1506 articlenumber = '$form->{articlenumber}',
1507 servicenumber = '$form->{servicenumber}',
1508 yearend = '$form->{yearend}',
1509 curr = '$form->{curr}',
1510 businessnumber = '$form->{businessnumber}'
1512 $dbh->do($query) || $form->dberror($query);
1515 my $name = $form->{name};
1516 $name =~ s/\'/\'\'/g;
1517 $query = qq|UPDATE employee
1519 WHERE login = '$form->{login}'|;
1520 $dbh->do($query) || $form->dberror($query);
1522 # foreach my $item (split(/ /, $form->{taxaccounts})) {
1523 # $query = qq|UPDATE tax
1524 # SET rate = | . ($form->{$item} / 100) . qq|,
1525 # taxnumber = '$form->{"taxnumber_$item"}'
1526 # WHERE chart_id = $item|;
1527 # $dbh->do($query) || $form->dberror($query);
1530 my $rc = $dbh->commit;
1533 # save first currency in myconfig
1534 $form->{currency} = substr($form->{curr}, 0, 3);
1536 my $myconfig = new User "$memberfile", "$form->{login}";
1538 foreach my $item (keys %$form) {
1539 $myconfig->{$item} = $form->{$item};
1542 $myconfig->save_member($memberfile, $userspath);
1546 qw(angebote bestellungen rechnungen anfragen lieferantenbestellungen einkaufsrechnungen);
1547 foreach $directory (@webdavdirs) {
1548 $file = "webdav/" . $directory . "/webdav-user";
1549 if ($myconfig->{$directory}) {
1550 open(HTACCESS, "$file") or die "cannot open webdav-user $!\n";
1551 while (<HTACCESS>) {
1552 ($login, $password) = split(/:/, $_);
1553 if ($login ne $form->{login}) {
1558 open(HTACCESS, "> $file") or die "cannot open webdav-user $!\n";
1559 $newfile .= $myconfig->{login} . ":" . $myconfig->{password} . "\n";
1560 print(HTACCESS $newfile);
1563 $form->{$directory} = 0;
1564 open(HTACCESS, "$file") or die "cannot open webdav-user $!\n";
1565 while (<HTACCESS>) {
1566 ($login, $password) = split(/:/, $_);
1567 if ($login ne $form->{login}) {
1572 open(HTACCESS, "> $file") or die "cannot open webdav-user $!\n";
1573 print(HTACCESS $newfile);
1579 $main::lxdebug->leave_sub();
1584 sub defaultaccounts {
1585 $main::lxdebug->enter_sub();
1587 my ($self, $myconfig, $form) = @_;
1589 # connect to database
1590 my $dbh = $form->dbconnect($myconfig);
1592 # get defaults from defaults table
1593 my $query = qq|SELECT * FROM defaults|;
1594 my $sth = $dbh->prepare($query);
1595 $sth->execute || $form->dberror($query);
1597 $form->{defaults} = $sth->fetchrow_hashref(NAME_lc);
1598 $form->{defaults}{IC} = $form->{defaults}{inventory_accno_id};
1599 $form->{defaults}{IC_income} = $form->{defaults}{income_accno_id};
1600 $form->{defaults}{IC_expense} = $form->{defaults}{expense_accno_id};
1601 $form->{defaults}{FX_gain} = $form->{defaults}{fxgain_accno_id};
1602 $form->{defaults}{FX_loss} = $form->{defaults}{fxloss_accno_id};
1606 $query = qq|SELECT c.id, c.accno, c.description, c.link
1608 WHERE c.link LIKE '%IC%'
1610 $sth = $dbh->prepare($query);
1611 $sth->execute || $self->dberror($query);
1613 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1614 foreach my $key (split(/:/, $ref->{link})) {
1617 if ($key =~ /cogs/) {
1618 $nkey = "IC_expense";
1620 if ($key =~ /sale/) {
1621 $nkey = "IC_income";
1623 %{ $form->{IC}{$nkey}{ $ref->{accno} } } = (
1625 description => $ref->{description}
1632 $query = qq|SELECT c.id, c.accno, c.description
1634 WHERE c.category = 'I'
1635 AND c.charttype = 'A'
1637 $sth = $dbh->prepare($query);
1638 $sth->execute || $self->dberror($query);
1640 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1641 %{ $form->{IC}{FX_gain}{ $ref->{accno} } } = (
1643 description => $ref->{description}
1648 $query = qq|SELECT c.id, c.accno, c.description
1650 WHERE c.category = 'E'
1651 AND c.charttype = 'A'
1653 $sth = $dbh->prepare($query);
1654 $sth->execute || $self->dberror($query);
1656 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1657 %{ $form->{IC}{FX_loss}{ $ref->{accno} } } = (
1659 description => $ref->{description}
1664 # now get the tax rates and numbers
1665 $query = qq|SELECT c.id, c.accno, c.description,
1666 t.rate * 100 AS rate, t.taxnumber
1668 WHERE c.id = t.chart_id|;
1670 $sth = $dbh->prepare($query);
1671 $sth->execute || $form->dberror($query);
1673 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1674 $form->{taxrates}{ $ref->{accno} }{id} = $ref->{id};
1675 $form->{taxrates}{ $ref->{accno} }{description} = $ref->{description};
1676 $form->{taxrates}{ $ref->{accno} }{taxnumber} = $ref->{taxnumber}
1677 if $ref->{taxnumber};
1678 $form->{taxrates}{ $ref->{accno} }{rate} = $ref->{rate} if $ref->{rate};
1684 $main::lxdebug->leave_sub();
1688 $main::lxdebug->enter_sub();
1690 my ($self, $myconfig, $form, $userspath) = @_;
1694 my $boundary = time;
1696 "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}.sql";
1697 my $out = $form->{OUT};
1698 $form->{OUT} = ">$tmpfile";
1700 if ($form->{media} eq 'email') {
1705 $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
1706 $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
1708 "Lx-Office Backup / $myconfig->{dbname}-$form->{dbversion}.sql";
1709 @{ $mail->{attachments} } = ($tmpfile);
1710 $mail->{version} = $form->{version};
1711 $mail->{fileid} = "$boundary.";
1713 $myconfig->{signature} =~ s/\\n/\r\n/g;
1714 $mail->{message} = "--\n$myconfig->{signature}";
1718 open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!");
1720 # get sequences, functions and triggers
1721 open(FH, "sql/lx-office.sql") or $form->error("sql/lx-office.sql : $!");
1734 # Remove DOS and Unix style line endings.
1737 # ignore comments or empty lines
1738 next if /^(--.*|\s+)$/;
1740 for (my $i = 0; $i < length($_); $i++) {
1741 my $char = substr($_, $i, 1);
1743 # Are we inside a string?
1745 if ($char eq $quote_chars[-1]) {
1751 if (($char eq "'") || ($char eq "\"")) {
1752 push(@quote_chars, $char);
1754 } elsif ($char eq ";") {
1756 # Query is complete. Check for triggers and functions.
1757 if ($query =~ /^create\s+function\s+\"?(\w+)\"?/i) {
1758 push(@functions, $query);
1760 } elsif ($query =~ /^create\s+trigger\s+\"?(\w+)\"?/i) {
1761 push(@triggers, $query);
1763 } elsif ($query =~ /^create\s+sequence\s+\"?(\w+)\"?/i) {
1764 push(@sequences, $1);
1766 } elsif ($query =~ /^create\s+table\s+\"?(\w+)\"?/i) {
1767 $tablespecs{$1} = $query;
1769 } elsif ($query =~ /^create\s+index\s+\"?(\w+)\"?/i) {
1770 push(@indices, $query);
1784 # connect to database
1785 my $dbh = $form->dbconnect($myconfig);
1787 # get all the tables
1788 my @tables = $dbh->tables('', '', 'customer', '', { noprefix => 0 });
1790 my $today = scalar localtime;
1792 $myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost};
1794 print OUT qq|-- Lx-Office Backup
1795 -- Dataset: $myconfig->{dbname}
1796 -- Version: $form->{dbversion}
1797 -- Host: $myconfig->{dbhost}
1798 -- Login: $form->{login}
1799 -- User: $myconfig->{name}
1803 $myconfig->{dboptions};
1807 print OUT "-- DROP Sequences\n";
1809 foreach $item (@sequences) {
1810 print OUT qq|DROP SEQUENCE $item;\n|;
1813 print OUT "-- DROP Triggers\n";
1815 foreach $item (@triggers) {
1816 if ($item =~ /^create\s+trigger\s+\"?(\w+)\"?\s+.*on\s+\"?(\w+)\"?\s+/i) {
1817 print OUT qq|DROP TRIGGER "$1" ON "$2";\n|;
1821 print OUT "-- DROP Functions\n";
1823 foreach $item (@functions) {
1824 if ($item =~ /^create\s+function\s+\"?(\w+)\"?/i) {
1825 print OUT qq|DROP FUNCTION "$1" ();\n|;
1829 foreach $table (@tables) {
1830 if (!($table =~ /^sql_.*/)) {
1831 my $query = qq|SELECT * FROM $table|;
1833 my $sth = $dbh->prepare($query);
1834 $sth->execute || $form->dberror($query);
1836 $query = "INSERT INTO $table (";
1837 map { $query .= qq|$sth->{NAME}->[$_],| }
1838 (0 .. $sth->{NUM_OF_FIELDS} - 1);
1841 $query .= ") VALUES";
1843 if ($tablespecs{$table}) {
1845 print(OUT "DROP TABLE $table;\n");
1846 print(OUT $tablespecs{$table}, ";\n");
1849 print(OUT "DELETE FROM $table;\n");
1851 while (my @arr = $sth->fetchrow_array) {
1854 foreach my $item (@arr) {
1855 if (defined $item) {
1856 $item =~ s/\'/\'\'/g;
1857 $fields .= qq|'$item',|;
1866 print OUT qq|$query $fields;\n|;
1873 # create indices, sequences, functions and triggers
1875 print(OUT "-- CREATE Indices\n");
1876 map({ print(OUT "$_;\n"); } @indices);
1878 print OUT "-- CREATE Sequences\n";
1879 foreach $item (@sequences) {
1880 $query = qq|SELECT last_value FROM $item|;
1881 $sth = $dbh->prepare($query);
1882 $sth->execute || $form->dberror($query);
1883 my ($id) = $sth->fetchrow_array;
1887 CREATE SEQUENCE $item START $id;
1891 print OUT "-- CREATE Functions\n";
1894 map { print(OUT $_, ";\n"); } @functions;
1896 print OUT "-- CREATE Triggers\n";
1899 map { print(OUT $_, ";\n"); } @triggers;
1906 my @args = ("gzip", "$tmpfile");
1907 system(@args) == 0 or $form->error("$args[0] : $?");
1911 if ($form->{media} eq 'email') {
1912 @{ $mail->{attachments} } = ($tmpfile);
1913 $err = $mail->send($out);
1916 if ($form->{media} eq 'file') {
1918 open(IN, "$tmpfile") or $form->error("$tmpfile : $!");
1919 open(OUT, ">-") or $form->error("STDOUT : $!");
1921 print OUT qq|Content-Type: application/x-tar-gzip;
1922 Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}.sql.gz"
1937 $main::lxdebug->leave_sub();
1941 $main::lxdebug->enter_sub();
1943 my ($self, $myconfig, $form) = @_;
1945 my $dbh = $form->dbconnect($myconfig);
1947 my $query = qq|SELECT closedto, revtrans FROM defaults|;
1948 my $sth = $dbh->prepare($query);
1949 $sth->execute || $form->dberror($query);
1951 ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
1957 $main::lxdebug->leave_sub();
1961 $main::lxdebug->enter_sub();
1963 my ($self, $myconfig, $form) = @_;
1965 my $dbh = $form->dbconnect($myconfig);
1967 if ($form->{revtrans}) {
1969 $query = qq|UPDATE defaults SET closedto = NULL,
1971 } elsif ($form->{closedto}) {
1973 $query = qq|UPDATE defaults SET closedto = '$form->{closedto}',
1977 $query = qq|UPDATE defaults SET closedto = NULL,
1981 # set close in defaults
1982 $dbh->do($query) || $form->dberror($query);
1986 $main::lxdebug->leave_sub();
1990 my ($self, $units, $unit_name, $factor) = @_;
1992 $factor = 1 unless ($factor);
1994 my $unit = $units->{$unit_name};
1996 if (!defined($unit) || !$unit->{"base_unit"} ||
1997 ($unit_name eq $unit->{"base_unit"})) {
1998 return ($unit_name, $factor);
2001 return AM->get_base_unit($units, $unit->{"base_unit"}, $factor * $unit->{"factor"});
2004 sub retrieve_units {
2005 $main::lxdebug->enter_sub();
2007 my ($self, $myconfig, $form, $type, $prefix) = @_;
2009 my $dbh = $form->dbconnect($myconfig);
2011 my $query = "SELECT *, base_unit AS original_base_unit FROM units";
2014 $query .= " WHERE (type = ?)";
2018 my $sth = $dbh->prepare($query);
2019 $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
2022 while (my $ref = $sth->fetchrow_hashref()) {
2023 $units->{$ref->{"name"}} = $ref;
2027 my $query_lang = "SELECT id, template_code FROM language ORDER BY description";
2028 $sth = $dbh->prepare($query_lang);
2029 $sth->execute() || $form->dberror($query_lang);
2031 while ($ref = $sth->fetchrow_hashref()) {
2032 push(@languages, $ref);
2036 $query_lang = "SELECT ul.localized, ul.localized_plural, l.id, l.template_code " .
2037 "FROM units_language ul " .
2038 "LEFT JOIN language l ON ul.language_id = l.id " .
2039 "WHERE ul.unit = ?";
2040 $sth = $dbh->prepare($query_lang);
2042 foreach my $unit (values(%{$units})) {
2043 ($unit->{"${prefix}base_unit"}, $unit->{"${prefix}factor"}) = AM->get_base_unit($units, $unit->{"name"});
2045 $unit->{"LANGUAGES"} = {};
2046 foreach my $lang (@languages) {
2047 $unit->{"LANGUAGES"}->{$lang->{"template_code"}} = { "template_code" => $lang->{"template_code"} };
2050 $sth->execute($unit->{"name"}) || $form->dberror($query_lang . " (" . $unit->{"name"} . ")");
2051 while ($ref = $sth->fetchrow_hashref()) {
2052 map({ $unit->{"LANGUAGES"}->{$ref->{"template_code"}}->{$_} = $ref->{$_} } keys(%{$ref}));
2059 $main::lxdebug->leave_sub();
2064 sub translate_units {
2065 $main::lxdebug->enter_sub();
2067 my ($self, $form, $template_code, $unit, $amount) = @_;
2069 my $units = $self->retrieve_units(\%main::myconfig, $form);
2071 my $h = $units->{$unit}->{"LANGUAGES"}->{$template_code};
2072 my $new_unit = $unit;
2074 if (($amount != 1) && $h->{"localized_plural"}) {
2075 $new_unit = $h->{"localized_plural"};
2076 } elsif ($h->{"localized"}) {
2077 $new_unit = $h->{"localized"};
2081 $main::lxdebug->leave_sub();
2087 $main::lxdebug->enter_sub();
2089 my ($self, $myconfig, $form, $units) = @_;
2091 my $dbh = $form->dbconnect($myconfig);
2093 foreach my $unit (values(%{$units})) {
2094 my $base_unit = $unit->{"original_base_unit"};
2095 while ($base_unit) {
2096 $units->{$base_unit}->{"DEPENDING_UNITS"} = [] unless ($units->{$base_unit}->{"DEPENDING_UNITS"});
2097 push(@{$units->{$base_unit}->{"DEPENDING_UNITS"}}, $unit->{"name"});
2098 $base_unit = $units->{$base_unit}->{"original_base_unit"};
2102 foreach my $unit (values(%{$units})) {
2103 $unit->{"in_use"} = 0;
2104 map({ $_ = $dbh->quote($_); } @{$unit->{"DEPENDING_UNITS"}});
2106 foreach my $table (qw(parts invoice orderitems)) {
2107 my $query = "SELECT COUNT(*) FROM $table WHERE unit ";
2109 if (0 == scalar(@{$unit->{"DEPENDING_UNITS"}})) {
2110 $query .= "= " . $dbh->quote($unit->{"name"});
2112 $query .= "IN (" . $dbh->quote($unit->{"name"}) . "," . join(",", @{$unit->{"DEPENDING_UNITS"}}) . ")";
2115 my ($count) = $dbh->selectrow_array($query);
2116 $form->dberror($query) if ($dbh->err);
2119 $unit->{"in_use"} = 1;
2127 $main::lxdebug->leave_sub();
2130 sub unit_select_data {
2131 $main::lxdebug->enter_sub();
2133 my ($self, $units, $selected, $empty_entry) = @_;
2138 push(@{$select}, { "name" => "", "base_unit" => "", "factor" => "", "selected" => "" });
2141 foreach my $unit (sort({ $a->{"sortkey"} <=> $b->{"sortkey"} } keys(%{$units}))) {
2142 push(@{$select}, { "name" => $unit,
2143 "base_unit" => $units->{$unit}->{"base_unit"},
2144 "factor" => $units->{$unit}->{"factor"},
2145 "selected" => ($unit eq $selected) ? "selected" : "" });
2148 $main::lxdebug->leave_sub();
2153 sub unit_select_html {
2154 $main::lxdebug->enter_sub();
2156 my ($self, $units, $name, $selected, $convertible_into) = @_;
2158 my $select = "<select name=${name}>";
2160 foreach my $unit (sort({ $a->{"sortkey"} <=> $b->{"sortkey"} } keys(%{$units}))) {
2161 if (!$convertible_into ||
2162 ($units->{$convertible_into} &&
2163 ($units->{$convertible_into}->{"base_unit"} eq $units->{$unit}->{"base_unit"}))) {
2164 $select .= "<option" . (($unit eq $selected) ? " selected" : "") . ">${unit}</option>";
2167 $select .= "</select>";
2169 $main::lxdebug->leave_sub();
2175 $main::lxdebug->enter_sub();
2177 my ($self, $myconfig, $form, $name, $base_unit, $factor, $type, $languages) = @_;
2179 my $dbh = $form->dbconnect_noauto($myconfig);
2181 my $query = qq|SELECT COALESCE(MAX(sortkey), 0) + 1 FROM units|;
2182 my ($sortkey) = selectrow_query($form, $dbh, $query);
2184 $query = "INSERT INTO units (name, base_unit, factor, type, sortkey) " .
2185 "VALUES (?, ?, ?, ?, ?)";
2186 do_query($form, $dbh, $query, $name, $base_unit, $factor, $type, $sortkey);
2189 $query = "INSERT INTO units_language (unit, language_id, localized, localized_plural) VALUES (?, ?, ?, ?)";
2190 my $sth = $dbh->prepare($query);
2191 foreach my $lang (@{$languages}) {
2192 my @values = ($name, $lang->{"id"}, $lang->{"localized"}, $lang->{"localized_plural"});
2193 $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
2201 $main::lxdebug->leave_sub();
2205 $main::lxdebug->enter_sub();
2207 my ($self, $myconfig, $form, $type, $units, $delete_units) = @_;
2209 my $dbh = $form->dbconnect_noauto($myconfig);
2211 my ($base_unit, $unit, $sth, $query);
2213 $query = "DELETE FROM units_language";
2214 $dbh->do($query) || $form->dberror($query);
2216 if ($delete_units && (0 != scalar(@{$delete_units}))) {
2217 $query = "DELETE FROM units WHERE name IN (";
2218 map({ $query .= "?," } @{$delete_units});
2219 substr($query, -1, 1) = ")";
2220 $dbh->do($query, undef, @{$delete_units}) ||
2221 $form->dberror($query . " (" . join(", ", @{$delete_units}) . ")");
2224 $query = "UPDATE units SET name = ?, base_unit = ?, factor = ? WHERE name = ?";
2225 $sth = $dbh->prepare($query);
2227 my $query_lang = "INSERT INTO units_language (unit, language_id, localized, localized_plural) VALUES (?, ?, ?, ?)";
2228 my $sth_lang = $dbh->prepare($query_lang);
2230 foreach $unit (values(%{$units})) {
2231 $unit->{"depth"} = 0;
2232 my $base_unit = $unit;
2233 while ($base_unit->{"base_unit"}) {
2235 $base_unit = $units->{$base_unit->{"base_unit"}};
2239 foreach $unit (sort({ $a->{"depth"} <=> $b->{"depth"} } values(%{$units}))) {
2240 if ($unit->{"LANGUAGES"}) {
2241 foreach my $lang (@{$unit->{"LANGUAGES"}}) {
2242 next unless ($lang->{"id"} && $lang->{"localized"});
2243 my @values = ($unit->{"name"}, $lang->{"id"}, $lang->{"localized"}, $lang->{"localized_plural"});
2244 $sth_lang->execute(@values) || $form->dberror($query_lang . " (" . join(", ", @values) . ")");
2248 next if ($unit->{"unchanged_unit"});
2250 my @values = ($unit->{"name"}, $unit->{"base_unit"}, $unit->{"factor"}, $unit->{"old_name"});
2251 $sth->execute(@values) || $form->dberror($query . " (" . join(", ", @values) . ")");
2255 $sth_lang->finish();
2259 $main::lxdebug->leave_sub();
2263 $main::lxdebug->enter_sub();
2265 my ($self, $myconfig, $form, $dir, $name_1, $unit_type) = @_;
2267 my $dbh = $form->dbconnect_noauto($myconfig);
2271 $query = qq|SELECT sortkey FROM units WHERE name = ?|;
2272 my ($sortkey_1) = selectrow_query($form, $dbh, $query, $name_1);
2275 qq|SELECT sortkey FROM units | .
2276 qq|WHERE sortkey | . ($dir eq "down" ? ">" : "<") . qq| ? AND type = ? | .
2277 qq|ORDER BY sortkey | . ($dir eq "down" ? "ASC" : "DESC") . qq| LIMIT 1|;
2278 my ($sortkey_2) = selectrow_query($form, $dbh, $query, $sortkey_1, $unit_type);
2280 if (defined($sortkey_1)) {
2281 $query = qq|SELECT name FROM units WHERE sortkey = ${sortkey_2}|;
2282 my ($name_2) = selectrow_query($form, $dbh, $query);
2284 if (defined($name_2)) {
2285 $query = qq|UPDATE units SET sortkey = ? WHERE name = ?|;
2286 my $sth = $dbh->prepare($query);
2288 do_statement($form, $sth, $query, $sortkey_1, $name_2);
2289 do_statement($form, $sth, $query, $sortkey_2, $name_1);
2296 $main::lxdebug->leave_sub();