1 #=====================================================================
2 # SQL-Ledger Accounting
5 # Author: Dieter Simader
6 # Email: dsimader@sql-ledger.org
7 # Web: http://www.sql-ledger.org
9 # Contributors: Jim Rawlings <jim@your-dba.com>
11 # This program is free software; you can redistribute it and/or modify
12 # it under the terms of the GNU General Public License as published by
13 # the Free Software Foundation; either version 2 of the License, or
14 # (at your option) any later version.
16 # This program is distributed in the hope that it will be useful,
17 # but WITHOUT ANY WARRANTY; without even the implied warranty of
18 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 # GNU General Public License for more details.
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23 #======================================================================
25 # Administration module
30 #======================================================================
36 my ($self, $myconfig, $form) = @_;
39 my $dbh = $form->dbconnect($myconfig);
41 my $query = qq|SELECT accno, description, charttype, gifi_accno,
44 WHERE id = $form->{id}|;
45 my $sth = $dbh->prepare($query);
46 $sth->execute || $form->dberror($query);
48 my $ref = $sth->fetchrow_hashref(NAME_lc);
50 foreach my $key (keys %$ref) {
51 $form->{"$key"} = $ref->{"$key"};
54 # get default accounts
55 $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id
57 $sth = $dbh->prepare($query);
58 $sth->execute || $form->dberror($query);
60 $ref = $sth->fetchrow_hashref(NAME_lc);
61 map { $form->{$_} = $ref->{$_} } keys %ref;
64 # check if we have any transactions
65 $query = qq|SELECT trans_id FROM acc_trans
66 WHERE chart_id = $form->{id}|;
67 ($form->{orphaned}) = $dbh->selectrow_array($query);
68 $form->{orphaned} = !$form->{orphaned};
76 my ($self, $myconfig, $form) = @_;
78 # connect to database, turn off AutoCommit
79 my $dbh = $form->dbconnect_noauto($myconfig);
82 foreach my $item ($form->{AR},
96 $form->{IC_taxservice},
99 $form->{link} .= "${item}:" if ($item);
103 # strip blanks from accno
104 map { $form->{$_} =~ s/( |')//g } qw(accno gifi_accno);
106 foreach my $item (qw(accno gifi_accno description)) {
107 $form->{$item} =~ s/-(-+)/-/g;
108 $form->{$item} =~ s/ ( )+/ /g;
114 # if we have an id then replace the old record
116 $query = qq|UPDATE chart SET
117 accno = '$form->{accno}',
118 description = |.$dbh->quote($form->{description}).qq|,
119 charttype = '$form->{charttype}',
120 gifi_accno = '$form->{gifi_accno}',
121 category = '$form->{category}',
122 link = '$form->{link}'
123 WHERE id = $form->{id}|;
125 $query = qq|INSERT INTO chart
126 (accno, description, charttype, gifi_accno, category, link)
127 VALUES ('$form->{accno}',|
128 .$dbh->quote($form->{description}).qq|,
129 '$form->{charttype}', '$form->{gifi_accno}',
130 '$form->{category}', '$form->{link}')|;
132 $dbh->do($query) || $form->dberror($query);
135 $chart_id = $form->{id};
139 $query = qq|SELECT id
141 WHERE accno = '$form->{accno}'|;
142 ($chart_id) = $dbh->selectrow_array($query);
145 if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) {
147 # add account if it doesn't exist in tax
148 $query = qq|SELECT chart_id
150 WHERE chart_id = $chart_id|;
151 my ($tax_id) = $dbh->selectrow_array($query);
153 # add tax if it doesn't exist
155 $query = qq|INSERT INTO tax (chart_id, rate)
156 VALUES ($chart_id, 0)|;
157 $dbh->do($query) || $form->dberror($query);
162 $query = qq|DELETE FROM tax
163 WHERE chart_id = $form->{id}|;
164 $dbh->do($query) || $form->dberror($query);
169 my $rc = $dbh->commit;
179 my ($self, $myconfig, $form) = @_;
181 # connect to database, turn off AutoCommit
182 my $dbh = $form->dbconnect_noauto($myconfig);
184 my $query = qq|SELECT * FROM acc_trans
185 WHERE chart_id = $form->{id}|;
186 if ($dbh->selectrow_array($query)) {
192 # delete chart of account record
193 $query = qq|DELETE FROM chart
194 WHERE id = $form->{id}|;
195 $dbh->do($query) || $form->dberror($query);
197 # set inventory_accno_id, income_accno_id, expense_accno_id to defaults
198 $query = qq|UPDATE parts
199 SET inventory_accno_id =
200 (SELECT inventory_accno_id FROM defaults)
201 WHERE inventory_accno_id = $form->{id}|;
202 $dbh->do($query) || $form->dberror($query);
204 $query = qq|UPDATE parts
205 SET income_accno_id =
206 (SELECT income_accno_id FROM defaults)
207 WHERE income_accno_id = $form->{id}|;
208 $dbh->do($query) || $form->dberror($query);
210 $query = qq|UPDATE parts
211 SET expense_accno_id =
212 (SELECT expense_accno_id FROM defaults)
213 WHERE expense_accno_id = $form->{id}|;
214 $dbh->do($query) || $form->dberror($query);
216 foreach my $table (qw(partstax customertax vendortax tax)) {
217 $query = qq|DELETE FROM $table
218 WHERE chart_id = $form->{id}|;
219 $dbh->do($query) || $form->dberror($query);
222 # commit and redirect
223 my $rc = $dbh->commit;
232 my ($self, $myconfig, $form) = @_;
234 # connect to database
235 my $dbh = $form->dbconnect($myconfig);
237 my $query = qq|SELECT accno, description
241 $sth = $dbh->prepare($query);
242 $sth->execute || $form->dberror($query);
244 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
245 push @{ $form->{ALL} }, $ref;
255 my ($self, $myconfig, $form) = @_;
257 # connect to database
258 my $dbh = $form->dbconnect($myconfig);
260 my $query = qq|SELECT accno, description
262 WHERE accno = '$form->{accno}'|;
264 ($form->{accno}, $form->{description}) = $dbh->selectrow_array($query);
266 # check for transactions
267 $query = qq|SELECT * FROM acc_trans a
268 JOIN chart c ON (a.chart_id = c.id)
269 JOIN gifi g ON (c.gifi_accno = g.accno)
270 WHERE g.accno = '$form->{accno}'|;
271 ($form->{orphaned}) = $dbh->selectrow_array($query);
272 $form->{orphaned} = !$form->{orphaned};
280 my ($self, $myconfig, $form) = @_;
282 # connect to database
283 my $dbh = $form->dbconnect($myconfig);
285 $form->{accno} =~ s/( |')//g;
287 foreach my $item (qw(accno description)) {
288 $form->{$item} =~ s/-(-+)/-/g;
289 $form->{$item} =~ s/ ( )+/ /g;
292 # id is the old account number!
294 $query = qq|UPDATE gifi SET
295 accno = '$form->{accno}',
296 description = |.$dbh->quote($form->{description}).qq|
297 WHERE accno = '$form->{id}'|;
299 $query = qq|INSERT INTO gifi
301 VALUES ('$form->{accno}',|
302 .$dbh->quote($form->{description}).qq|)|;
304 $dbh->do($query) || $form->dberror;
312 my ($self, $myconfig, $form) = @_;
314 # connect to database
315 my $dbh = $form->dbconnect($myconfig);
317 # id is the old account number!
318 $query = qq|DELETE FROM gifi
319 WHERE accno = '$form->{id}'|;
320 $dbh->do($query) || $form->dberror($query);
328 my ($self, $myconfig, $form) = @_;
330 # connect to database
331 my $dbh = $form->dbconnect($myconfig);
334 my $query = qq|SELECT id, description
336 ORDER BY 2 $form->{direction}|;
338 $sth = $dbh->prepare($query);
339 $sth->execute || $form->dberror($query);
341 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
342 push @{ $form->{ALL} }, $ref;
352 my ($self, $myconfig, $form) = @_;
354 # connect to database
355 my $dbh = $form->dbconnect($myconfig);
357 my $query = qq|SELECT description
359 WHERE id = $form->{id}|;
360 ($form->{description}) = $dbh->selectrow_array($query);
362 # see if it is in use
363 $query = qq|SELECT * FROM inventory
364 WHERE warehouse_id = $form->{id}|;
365 ($form->{orphaned}) = $dbh->selectrow_array($query);
366 $form->{orphaned} = !$form->{orphaned};
374 my ($self, $myconfig, $form) = @_;
376 # connect to database
377 my $dbh = $form->dbconnect($myconfig);
379 $form->{description} =~ s/-(-)+/-/g;
380 $form->{description} =~ s/ ( )+/ /g;
383 $query = qq|UPDATE warehouse SET
384 description = |.$dbh->quote($form->{description}).qq|
385 WHERE id = $form->{id}|;
387 $query = qq|INSERT INTO warehouse
389 VALUES (|.$dbh->quote($form->{description}).qq|)|;
391 $dbh->do($query) || $form->dberror($query);
398 sub delete_warehouse {
399 my ($self, $myconfig, $form) = @_;
401 # connect to database
402 my $dbh = $form->dbconnect($myconfig);
404 $query = qq|DELETE FROM warehouse
405 WHERE id = $form->{id}|;
406 $dbh->do($query) || $form->dberror($query);
415 my ($self, $myconfig, $form) = @_;
417 # connect to database
418 my $dbh = $form->dbconnect($myconfig);
421 my $query = qq|SELECT id, description, role
423 ORDER BY 2 $form->{direction}|;
425 $sth = $dbh->prepare($query);
426 $sth->execute || $form->dberror($query);
428 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
429 push @{ $form->{ALL} }, $ref;
439 my ($self, $myconfig, $form) = @_;
441 # connect to database
442 my $dbh = $form->dbconnect($myconfig);
444 my $query = qq|SELECT description, role
446 WHERE id = $form->{id}|;
447 ($form->{description}, $form->{role}) = $dbh->selectrow_array($query);
449 map { $form->{$_} = $ref->{$_} } keys %$ref;
451 # see if it is in use
452 $query = qq|SELECT * FROM dpt_trans
453 WHERE department_id = $form->{id}|;
454 ($form->{orphaned}) = $dbh->selectrow_array($query);
455 $form->{orphaned} = !$form->{orphaned};
462 sub save_department {
463 my ($self, $myconfig, $form) = @_;
465 # connect to database
466 my $dbh = $form->dbconnect($myconfig);
468 $form->{description} =~ s/-(-)+/-/g;
469 $form->{description} =~ s/ ( )+/ /g;
472 $query = qq|UPDATE department SET
473 description = |.$dbh->quote($form->{description}).qq|,
474 role = '$form->{role}'
475 WHERE id = $form->{id}|;
477 $query = qq|INSERT INTO department
480 .$dbh->quote($form->{description}).qq|, '$form->{role}')|;
482 $dbh->do($query) || $form->dberror($query);
489 sub delete_department {
490 my ($self, $myconfig, $form) = @_;
492 # connect to database
493 my $dbh = $form->dbconnect($myconfig);
495 $query = qq|DELETE FROM department
496 WHERE id = $form->{id}|;
505 my ($self, $myconfig, $form) = @_;
507 # connect to database
508 my $dbh = $form->dbconnect($myconfig);
511 my $query = qq|SELECT id, description, discount
513 ORDER BY 2 $form->{direction}|;
515 $sth = $dbh->prepare($query);
516 $sth->execute || $form->dberror($query);
518 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
519 push @{ $form->{ALL} }, $ref;
529 my ($self, $myconfig, $form) = @_;
531 # connect to database
532 my $dbh = $form->dbconnect($myconfig);
534 my $query = qq|SELECT description, discount
536 WHERE id = $form->{id}|;
537 ($form->{description}, $form->{discount}) = $dbh->selectrow_array($query);
545 my ($self, $myconfig, $form) = @_;
547 # connect to database
548 my $dbh = $form->dbconnect($myconfig);
550 $form->{description} =~ s/-(-)+/-/g;
551 $form->{description} =~ s/ ( )+/ /g;
552 $form->{discount} /= 100;
555 $query = qq|UPDATE business SET
556 description = |.$dbh->quote($form->{description}).qq|,
557 discount = $form->{discount}
558 WHERE id = $form->{id}|;
560 $query = qq|INSERT INTO business
561 (description, discount)
563 .$dbh->quote($form->{description}).qq|, $form->{discount})|;
565 $dbh->do($query) || $form->dberror($query);
572 sub delete_business {
573 my ($self, $myconfig, $form) = @_;
575 # connect to database
576 my $dbh = $form->dbconnect($myconfig);
578 $query = qq|DELETE FROM business
579 WHERE id = $form->{id}|;
580 $dbh->do($query) || $form->dberror($query);
588 my ($self, $myconfig, $form) = @_;
590 # connect to database
591 my $dbh = $form->dbconnect($myconfig);
593 $form->{sort} = "code" unless $form->{sort};
594 my @a = qw(code description);
595 my %ordinal = ( code => 1,
597 my $sortorder = $form->sort_order(\@a, \%ordinal);
598 my $query = qq|SELECT code, sictype, description
600 ORDER BY $sortorder|;
602 $sth = $dbh->prepare($query);
603 $sth->execute || $form->dberror($query);
605 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
606 push @{ $form->{ALL} }, $ref;
616 my ($self, $myconfig, $form) = @_;
618 # connect to database
619 my $dbh = $form->dbconnect($myconfig);
621 my $query = qq|SELECT code, sictype, description
623 WHERE code = |.$dbh->quote($form->{code});
624 my $sth = $dbh->prepare($query);
625 $sth->execute || $form->dberror($query);
627 my $ref = $sth->fetchrow_hashref(NAME_lc);
629 map { $form->{$_} = $ref->{$_} } keys %$ref;
638 my ($self, $myconfig, $form) = @_;
640 # connect to database
641 my $dbh = $form->dbconnect($myconfig);
643 foreach my $item (qw(code description)) {
644 $form->{$item} =~ s/-(-)+/-/g;
649 $query = qq|UPDATE sic SET
650 code = |.$dbh->quote($form->{code}).qq|,
651 sictype = '$form->{sictype}',
652 description = |.$dbh->quote($form->{description}).qq|
653 WHERE code = |.$dbh->quote($form->{id});
655 $query = qq|INSERT INTO sic
656 (code, sictype, description)
658 .$dbh->quote($form->{code}).qq|,
660 .$dbh->quote($form->{description}).qq|)|;
662 $dbh->do($query) || $form->dberror($query);
670 my ($self, $myconfig, $form) = @_;
672 # connect to database
673 my $dbh = $form->dbconnect($myconfig);
675 $query = qq|DELETE FROM sic
676 WHERE code = |.$dbh->quote($form->{code});
685 my ($self, $myconfig, $form) = @_;
687 # connect to database
688 my $dbh = $form->dbconnect($myconfig);
690 $form->{sort} = "code" unless $form->{sort};
691 my @a = qw(code description);
692 my %ordinal = ( code => 1,
694 my $sortorder = $form->sort_order(\@a, \%ordinal);
696 my $query = qq|SELECT code, description
698 ORDER BY $sortorder|;
699 $sth = $dbh->prepare($query);
700 $sth->execute || $form->dberror($query);
702 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
703 push @{ $form->{ALL} }, $ref;
713 my ($self, $myconfig, $form) = @_;
715 # connect to database
716 my $dbh = $form->dbconnect($myconfig);
718 my $query = qq|SELECT *
720 WHERE code = |.$dbh->quote($form->{code});
721 my $sth = $dbh->prepare($query);
722 $sth->execute || $form->dberror($query);
724 my $ref = $sth->fetchrow_hashref(NAME_lc);
726 map { $form->{$_} = $ref->{$_} } keys %$ref;
736 my ($self, $myconfig, $form) = @_;
738 # connect to database
739 my $dbh = $form->dbconnect($myconfig);
741 $form->{code} =~ s/ //g;
742 foreach my $item (qw(code description)) {
743 $form->{$item} =~ s/-(-)+/-/g;
744 $form->{$item} =~ s/ ( )+/-/g;
749 $query = qq|UPDATE language SET
750 code = |.$dbh->quote($form->{code}).qq|,
751 description = |.$dbh->quote($form->{description}).qq|
752 WHERE code = |.$dbh->quote($form->{id});
754 $query = qq|INSERT INTO language
757 .$dbh->quote($form->{code}).qq|,|
758 .$dbh->quote($form->{description}).qq|)|;
760 $dbh->do($query) || $form->dberror($query);
767 sub delete_language {
768 my ($self, $myconfig, $form) = @_;
770 # connect to database
771 my $dbh = $form->dbconnect($myconfig);
773 $query = qq|DELETE FROM language
774 WHERE code = |.$dbh->quote($form->{code});
775 $dbh->do($query) || $form->dberror($query);
784 my ($self, $form) = @_;
786 open(TEMPLATE, "$form->{file}") or $form->error("$form->{file} : $!");
798 my ($self, $form) = @_;
800 open(TEMPLATE, ">$form->{file}") or $form->error("$form->{file} : $!");
803 $form->{body} =~ s/\r\n/\n/g;
804 print TEMPLATE $form->{body};
812 sub save_preferences {
813 my ($self, $myconfig, $form, $memberfile, $userspath) = @_;
815 # connect to database
816 my $dbh = $form->dbconnect($myconfig);
819 my $query = qq|UPDATE employee
820 SET name = |.$dbh->quote($form->{name}).qq|,
821 role = '$form->{role}'
822 WHERE login = '$form->{login}'|;
823 $dbh->do($query) || $form->dberror($query);
825 # get default currency
826 $query = qq|SELECT curr, businessnumber
828 ($form->{currency}, $form->{businessnumber}) = $dbh->selectrow_array($query);
829 $form->{currency} =~ s/:.*//;
833 my $myconfig = new User "$memberfile", "$form->{login}";
835 foreach my $item (keys %$form) {
836 $myconfig->{$item} = $form->{$item};
839 $myconfig->{password} = $form->{new_password} if ($form->{old_password} ne $form->{new_password});
841 $myconfig->save_member($memberfile, $userspath);
849 my ($self, $myconfig, $form) = @_;
851 map { ($form->{$_}) = split /--/, $form->{$_} } qw(inventory_accno income_accno expense_accno fxgain_accno fxloss_accno);
854 $form->{curr} =~ s/ //g;
855 map { push(@a, uc pack "A3", $_) if $_ } split /:/, $form->{curr};
856 $form->{curr} = join ':', @a;
858 # connect to database
859 my $dbh = $form->dbconnect_noauto($myconfig);
862 my $query = qq|UPDATE defaults SET
864 (SELECT id FROM chart
865 WHERE accno = '$form->{inventory_accno}'),
867 (SELECT id FROM chart
868 WHERE accno = '$form->{income_accno}'),
870 (SELECT id FROM chart
871 WHERE accno = '$form->{expense_accno}'),
873 (SELECT id FROM chart
874 WHERE accno = '$form->{fxgain_accno}'),
876 (SELECT id FROM chart
877 WHERE accno = '$form->{fxloss_accno}'),
878 sinumber = '$form->{sinumber}',
879 vinumber = '$form->{vinumber}',
880 sonumber = '$form->{sonumber}',
881 ponumber = '$form->{ponumber}',
882 sqnumber = '$form->{sqnumber}',
883 rfqnumber = '$form->{rfqnumber}',
884 partnumber = '$form->{partnumber}',
885 employeenumber = '$form->{employeenumber}',
886 customernumber = '$form->{customernumber}',
887 vendornumber = '$form->{vendornumber}',
888 yearend = '$form->{yearend}',
889 curr = '$form->{curr}',
890 weightunit = |.$dbh->quote($form->{weightunit}).qq|,
891 businessnumber = |.$dbh->quote($form->{businessnumber});
892 $dbh->do($query) || $form->dberror($query);
894 foreach my $item (split / /, $form->{taxaccounts}) {
895 $form->{$item} = $form->parse_amount($myconfig, $form->{$item}) / 100;
896 $query = qq|UPDATE tax
897 SET rate = $form->{$item},
898 taxnumber = |.$dbh->quote($form->{"taxnumber_$item"}).qq|
899 WHERE chart_id = $item|;
900 $dbh->do($query) || $form->dberror($query);
903 my $rc = $dbh->commit;
911 sub defaultaccounts {
912 my ($self, $myconfig, $form) = @_;
914 # connect to database
915 my $dbh = $form->dbconnect($myconfig);
917 # get defaults from defaults table
918 my $query = qq|SELECT * FROM defaults|;
919 my $sth = $dbh->prepare($query);
920 $sth->execute || $form->dberror($query);
922 $form->{defaults} = $sth->fetchrow_hashref(NAME_lc);
923 $form->{defaults}{IC} = $form->{defaults}{inventory_accno_id};
924 $form->{defaults}{IC_income} = $form->{defaults}{income_accno_id};
925 $form->{defaults}{IC_expense} = $form->{defaults}{expense_accno_id};
926 $form->{defaults}{FX_gain} = $form->{defaults}{fxgain_accno_id};
927 $form->{defaults}{FX_loss} = $form->{defaults}{fxloss_accno_id};
933 $query = qq|SELECT id, accno, description, link
935 WHERE link LIKE '%IC%'
937 $sth = $dbh->prepare($query);
938 $sth->execute || $form->dberror($query);
940 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
941 foreach my $key (split(/:/, $ref->{link})) {
944 if ($key =~ /cogs/) {
945 $nkey = "IC_expense";
947 if ($key =~ /sale/) {
950 %{ $form->{IC}{$nkey}{$ref->{accno}} } = ( id => $ref->{id},
951 description => $ref->{description} );
958 $query = qq|SELECT id, accno, description
963 $sth = $dbh->prepare($query);
964 $sth->execute || $form->dberror($query);
966 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
967 %{ $form->{IC}{FX_gain}{$ref->{accno}} } = ( id => $ref->{id},
968 description => $ref->{description} );
972 $query = qq|SELECT id, accno, description
977 $sth = $dbh->prepare($query);
978 $sth->execute || $form->dberror($query);
980 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
981 %{ $form->{IC}{FX_loss}{$ref->{accno}} } = ( id => $ref->{id},
982 description => $ref->{description} );
987 # now get the tax rates and numbers
988 $query = qq|SELECT chart.id, chart.accno, chart.description,
989 tax.rate * 100 AS rate, tax.taxnumber
991 WHERE chart.id = tax.chart_id|;
993 $sth = $dbh->prepare($query);
994 $sth->execute || $form->dberror($query);
996 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
997 $form->{taxrates}{$ref->{accno}}{id} = $ref->{id};
998 $form->{taxrates}{$ref->{accno}}{description} = $ref->{description};
999 $form->{taxrates}{$ref->{accno}}{taxnumber} = $ref->{taxnumber} if $ref->{taxnumber};
1000 $form->{taxrates}{$ref->{accno}}{rate} = $ref->{rate} if $ref->{rate};
1010 my ($self, $myconfig, $form, $userspath, $gzip) = @_;
1015 my @t = localtime(time);
1018 $t[3] = substr("0$t[3]", -2);
1019 $t[4] = substr("0$t[4]", -2);
1021 my $boundary = time;
1022 my $tmpfile = "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql";
1023 my $out = $form->{OUT};
1024 $form->{OUT} = ">$tmpfile";
1026 open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!");
1028 # get sequences, functions and triggers
1035 # get dbversion from -tables.sql
1036 my $file = "$myconfig->{dbdriver}-tables.sql";
1038 open(FH, "sql/$file") or $form->error("sql/$file : $!");
1043 @dbversion = grep /defaults \(version\)/, @a;
1045 $dbversion = "@dbversion";
1046 $dbversion =~ /(\d+\.\d+\.\d+)/;
1047 $dbversion = User::calc_version($1);
1049 opendir SQLDIR, "sql/." or $form->error($!);
1050 @a = grep /$myconfig->{dbdriver}-upgrade-.*?\.sql$/, readdir SQLDIR;
1056 foreach my $line (@a) {
1058 $upgradescript = $line;
1059 $line =~ s/(^$myconfig->{dbdriver}-upgrade-|\.sql$)//g;
1061 ($mindb, $maxdb) = split /-/, $line;
1062 $mindb = User::calc_version($mindb);
1064 next if $mindb < $dbversion;
1066 $maxdb = User::calc_version($maxdb);
1068 $upgradescripts{$maxdb} = $upgradescript;
1072 $upgradescripts{$dbversion} = "$myconfig->{dbdriver}-tables.sql";
1073 $upgradescripts{functions} = "$myconfig->{dbdriver}-functions.sql";
1075 if (-f "sql/$myconfig->{dbdriver}-custom_tables.sql") {
1076 $upgradescripts{customtables} = "$myconfig->{dbdriver}-custom_tables.sql";
1078 if (-f "sql/$myconfig->{dbdriver}-custom_functions.sql") {
1079 $upgradescripts{customfunctions} = "$myconfig->{dbdriver}-custom_functions.sql";
1082 foreach my $key (sort keys %upgradescripts) {
1084 $file = $upgradescripts{$key};
1086 open(FH, "sql/$file") or $form->error("sql/$file : $!");
1088 push @schema, qq|-- $file\n|;
1092 if (/create table (\w+)/i) {
1096 if (/create sequence (\w+)/i) {
1097 push @sequences, $1;
1100 if (/end function/i) {
1101 push @functions, $_;
1106 if (/create function /i) {
1111 push @functions, $_;
1115 if (/end trigger/i) {
1121 if (/create trigger/i) {
1130 push @schema, $_ if $_ !~ /^(insert|--)/i;
1138 # connect to database
1139 my $dbh = $form->dbconnect($myconfig);
1141 my $today = scalar localtime;
1143 $myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost};
1145 print OUT qq|-- SQL-Ledger Backup
1146 -- Dataset: $myconfig->{dbname}
1147 -- Version: $form->{dbversion}
1148 -- Host: $myconfig->{dbhost}
1149 -- Login: $form->{login}
1150 -- User: $myconfig->{name}
1156 my $restrict = ($myconfig->{dbdriver} eq 'DB2') ? "RESTRICT" : "";
1158 @tables = grep !/^temp/, @tables;
1159 # drop tables and sequences
1160 map { print OUT qq|DROP TABLE $_;\n| } @tables;
1161 map { print OUT qq|DROP SEQUENCE $_ $restrict;\n| } @sequences;
1165 # triggers and index files are dropped with the tables
1168 foreach $item (@functions) {
1169 if ($item =~ /create function (.*\))/i) {
1170 print OUT qq|DROP FUNCTION $1;\n|;
1178 print OUT qq|-- set options
1179 $myconfig->{dboptions};
1188 foreach $table (@tables) {
1190 $query = qq|SELECT * FROM $table|;
1191 $sth = $dbh->prepare($query);
1192 $sth->execute || $form->dberror($query);
1194 $query = qq|INSERT INTO $table (|;
1195 $query .= join ',', (map { $sth->{NAME}->[$_] } (0 .. $sth->{NUM_OF_FIELDS} - 1));
1196 $query .= qq|) VALUES|;
1198 while (@arr = $sth->fetchrow_array) {
1202 $fields .= join ',', map { $dbh->quote($_) } @arr;
1205 print OUT qq|$query $fields;\n|;
1212 # create sequences and triggers
1213 foreach $item (@sequences) {
1214 if ($myconfig->{dbdriver} eq 'DB2') {
1215 $query = qq|SELECT NEXTVAL FOR $item FROM sysibm.sysdummy1|;
1217 $query = qq|SELECT last_value FROM $item|;
1220 $sth = $dbh->prepare($query);
1221 $sth->execute || $form->dberror($query);
1222 my ($id) = $sth->fetchrow_array;
1227 DROP SEQUENCE $item $restrict;\n|;
1229 if ($myconfig->{dbdriver} eq 'DB2') {
1230 print OUT qq|CREATE SEQUENCE $item AS INTEGER START WITH $id INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 5;\n|;
1232 print OUT qq|CREATE SEQUENCE $item START $id;\n|;
1239 map { print OUT $_ } @functions;
1242 map { print OUT $_ } @triggers;
1244 # add the index files
1245 open(FH, "sql/$myconfig->{dbdriver}-indices.sql");
1254 # compress backup if gzip defined
1257 my @args = split / /, $gzip;
1260 push @args, "$tmpfile";
1261 system(@args) == 0 or $form->error("$args[0] : $?");
1265 $suffix = ${-S} || ".gz";
1266 $tmpfile .= $suffix;
1269 if ($form->{media} eq 'email') {
1274 $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
1275 $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
1276 $mail->{subject} = "SQL-Ledger Backup / $myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix";
1277 @{ $mail->{attachments} } = ($tmpfile);
1278 $mail->{version} = $form->{version};
1279 $mail->{fileid} = "$boundary.";
1281 $myconfig->{signature} =~ s/\\n/\r\n/g;
1282 $mail->{message} = "-- \n$myconfig->{signature}";
1284 $err = $mail->send($out);
1287 if ($form->{media} eq 'file') {
1289 open(IN, "$tmpfile") or $form->error("$tmpfile : $!");
1290 open(OUT, ">-") or $form->error("STDOUT : $!");
1292 print OUT qq|Content-Type: application/file;
1293 Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix"
1312 my ($self, $myconfig, $form) = @_;
1314 my $dbh = $form->dbconnect($myconfig);
1316 my $query = qq|SELECT closedto, revtrans, audittrail
1318 ($form->{closedto}, $form->{revtrans}, $form->{audittrail}) = $dbh->selectrow_array($query);
1326 my ($self, $myconfig, $form) = @_;
1328 my $dbh = $form->dbconnect_noauto($myconfig);
1330 if ($form->{revtrans}) {
1332 $query = qq|UPDATE defaults SET closedto = NULL,
1335 if ($form->{closedto}) {
1337 $query = qq|UPDATE defaults SET closedto = '$form->{closedto}',
1341 $query = qq|UPDATE defaults SET closedto = NULL,
1346 if ($form->{audittrail}) {
1347 $query .= qq|, audittrail = '1'|;
1349 $query .= qq|, audittrail = '0'|;
1352 # set close in defaults
1353 $dbh->do($query) || $form->dberror($query);
1355 if ($form->{removeaudittrail}) {
1356 $query = qq|DELETE FROM audittrail
1357 WHERE transdate < '$form->{removeaudittrail}'|;
1358 $dbh->do($query) || $form->dberror($query);
1368 sub earningsaccounts {
1369 my ($self, $myconfig, $form) = @_;
1371 my ($query, $sth, $ref);
1373 # connect to database
1374 my $dbh = $form->dbconnect($myconfig);
1376 # get chart of accounts
1377 $query = qq|SELECT accno,description
1379 WHERE charttype = 'A'
1382 $sth = $dbh->prepare($query);
1383 $sth->execute || $form->dberror($query);
1384 $form->{chart} = "";
1386 while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
1387 push @{ $form->{chart} }, $ref;
1397 my ($self, $myconfig, $form) = @_;
1399 # connect to database, turn off AutoCommit
1400 my $dbh = $form->dbconnect_noauto($myconfig);
1404 $uid .= $form->{login};
1406 $query = qq|INSERT INTO gl (reference, employee_id)
1407 VALUES ('$uid', (SELECT id FROM employee
1408 WHERE login = '$form->{login}'))|;
1409 $dbh->do($query) || $form->dberror($query);
1411 $query = qq|SELECT id FROM gl
1412 WHERE reference = '$uid'|;
1413 ($form->{id}) = $dbh->selectrow_array($query);
1415 $query = qq|UPDATE gl SET
1416 reference = |.$dbh->quote($form->{reference}).qq|,
1417 description = |.$dbh->quote($form->{description}).qq|,
1418 notes = |.$dbh->quote($form->{notes}).qq|,
1419 transdate = '$form->{transdate}',
1421 WHERE id = $form->{id}|;
1423 $dbh->do($query) || $form->dberror($query);
1428 # insert acc_trans transactions
1429 for my $i (1 .. $form->{rowcount}) {
1431 ($accno) = split(/--/, $form->{"accno_$i"});
1434 if ($form->{"credit_$i"} != 0) {
1435 $amount = $form->{"credit_$i"};
1437 if ($form->{"debit_$i"} != 0) {
1438 $amount = $form->{"debit_$i"} * -1;
1442 # if there is an amount, add the record
1444 $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
1447 ($form->{id}, (SELECT id
1449 WHERE accno = '$accno'),
1450 $amount, '$form->{transdate}', |
1451 .$dbh->quote($form->{reference}).qq|)|;
1453 $dbh->do($query) || $form->dberror($query);
1457 $query = qq|INSERT INTO yearend (trans_id, transdate)
1458 VALUES ($form->{id}, '$form->{transdate}')|;
1459 $dbh->do($query) || $form->dberror($query);
1461 my %audittrail = ( tablename => 'gl',
1462 reference => $form->{reference},
1463 formname => 'yearend',
1465 id => $form->{id} );
1466 $form->audittrail($dbh, "", \%audittrail);
1468 # commit and redirect
1469 my $rc = $dbh->commit;