X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2FSL%2FAM.pm;h=dbdd61111edd16d6c68b02ba85ca39ce5128a6ce;hp=d691b3ce63bb95417d6aa48e2cf6cde3444aadcb;hb=refs%2Fheads%2FSQL_LEDGER;hpb=52072fcd26f2faf57923f598c358e7f47c4e2643 diff --git a/sql-ledger/SL/AM.pm b/sql-ledger/SL/AM.pm index d691b3ce6..dbdd61111 100644 --- a/sql-ledger/SL/AM.pm +++ b/sql-ledger/SL/AM.pm @@ -1,12 +1,12 @@ #===================================================================== # SQL-Ledger Accounting -# Copyright (C) 2001 +# Copyright (C) 2000 # # Author: Dieter Simader # Email: dsimader@sql-ledger.org # Web: http://www.sql-ledger.org # -# Contributors: +# Contributors: Jim Rawlings # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -51,9 +51,6 @@ sub get_account { $form->{"$key"} = $ref->{"$key"}; } - $sth->finish; - - # get default accounts $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id FROM defaults|; @@ -61,10 +58,15 @@ sub get_account { $sth->execute || $form->dberror($query); $ref = $sth->fetchrow_hashref(NAME_lc); - map { $form->{$_} = $ref->{$_} } keys %ref; - $sth->finish; + + # check if we have any transactions + $query = qq|SELECT trans_id FROM acc_trans + WHERE chart_id = $form->{id}|; + ($form->{orphaned}) = $dbh->selectrow_array($query); + $form->{orphaned} = !$form->{orphaned}; + $dbh->disconnect; } @@ -76,11 +78,6 @@ sub save_account { # connect to database, turn off AutoCommit my $dbh = $form->dbconnect_noauto($myconfig); - # 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); - } - $form->{link} = ""; foreach my $item ($form->{AR}, $form->{AR_amount}, @@ -103,18 +100,22 @@ sub save_account { } chop $form->{link}; - # if we have an id then replace the old record - $form->{description} =~ s/'/''/g; - # strip blanks from accno - map { $form->{$_} =~ s/ //g; } qw(accno gifi_accno); + map { $form->{$_} =~ s/( |')//g } qw(accno gifi_accno); - my ($query, $sth); + foreach my $item (qw(accno gifi_accno description)) { + $form->{$item} =~ s/-(-+)/-/g; + $form->{$item} =~ s/ ( )+/ /g; + } + my $query; + my $sth; + + # if we have an id then replace the old record if ($form->{id}) { $query = qq|UPDATE chart SET accno = '$form->{accno}', - description = '$form->{description}', + description = |.$dbh->quote($form->{description}).qq|, charttype = '$form->{charttype}', gifi_accno = '$form->{gifi_accno}', category = '$form->{category}', @@ -123,38 +124,31 @@ sub save_account { } else { $query = qq|INSERT INTO chart (accno, description, charttype, gifi_accno, category, link) - VALUES ('$form->{accno}', '$form->{description}', + VALUES ('$form->{accno}',| + .$dbh->quote($form->{description}).qq|, '$form->{charttype}', '$form->{gifi_accno}', '$form->{category}', '$form->{link}')|; } $dbh->do($query) || $form->dberror($query); - - if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) { - my $chart_id = $form->{id}; - - unless ($form->{id}) { - # get id from chart - $query = qq|SELECT id - FROM chart - WHERE accno = '$form->{accno}'|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($chart_id) = $sth->fetchrow_array; - $sth->finish; - } - + $chart_id = $form->{id}; + + if (! $form->{id}) { + # get id from chart + $query = qq|SELECT id + FROM chart + WHERE accno = '$form->{accno}'|; + ($chart_id) = $dbh->selectrow_array($query); + } + + if ($form->{IC_taxpart} || $form->{IC_taxservice} || $form->{CT_tax}) { + # add account if it doesn't exist in tax $query = qq|SELECT chart_id FROM tax WHERE chart_id = $chart_id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my ($tax_id) = $sth->fetchrow_array; - $sth->finish; + my ($tax_id) = $dbh->selectrow_array($query); # add tax if it doesn't exist unless ($tax_id) { @@ -171,7 +165,6 @@ sub save_account { } } - # commit my $rc = $dbh->commit; $dbh->disconnect; @@ -187,6 +180,14 @@ sub delete_account { # connect to database, turn off AutoCommit my $dbh = $form->dbconnect_noauto($myconfig); + + my $query = qq|SELECT * FROM acc_trans + WHERE chart_id = $form->{id}|; + if ($dbh->selectrow_array($query)) { + $dbh->disconnect; + return; + } + # delete chart of account record $query = qq|DELETE FROM chart @@ -244,22 +245,479 @@ sub gifi_accounts { push @{ $form->{ALL} }, $ref; } - $sth->finish; $dbh->disconnect; } -sub get_gifi { +sub get_gifi { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT accno, description + FROM gifi + WHERE accno = '$form->{accno}'|; + + ($form->{accno}, $form->{description}) = $dbh->selectrow_array($query); + + # check for transactions + $query = qq|SELECT * FROM acc_trans a + JOIN chart c ON (a.chart_id = c.id) + JOIN gifi g ON (c.gifi_accno = g.accno) + WHERE g.accno = '$form->{accno}'|; + ($form->{orphaned}) = $dbh->selectrow_array($query); + $form->{orphaned} = !$form->{orphaned}; + + $dbh->disconnect; + +} + + +sub save_gifi { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{accno} =~ s/( |')//g; + + foreach my $item (qw(accno description)) { + $form->{$item} =~ s/-(-+)/-/g; + $form->{$item} =~ s/ ( )+/ /g; + } + + # id is the old account number! + if ($form->{id}) { + $query = qq|UPDATE gifi SET + accno = '$form->{accno}', + description = |.$dbh->quote($form->{description}).qq| + WHERE accno = '$form->{id}'|; + } else { + $query = qq|INSERT INTO gifi + (accno, description) + VALUES ('$form->{accno}',| + .$dbh->quote($form->{description}).qq|)|; + } + $dbh->do($query) || $form->dberror; + + $dbh->disconnect; + +} + + +sub delete_gifi { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # id is the old account number! + $query = qq|DELETE FROM gifi + WHERE accno = '$form->{id}'|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub warehouses { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->sort_order(); + my $query = qq|SELECT id, description + FROM warehouse + ORDER BY 2 $form->{direction}|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $dbh->disconnect; + +} + + + +sub get_warehouse { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT description + FROM warehouse + WHERE id = $form->{id}|; + ($form->{description}) = $dbh->selectrow_array($query); + + # see if it is in use + $query = qq|SELECT * FROM inventory + WHERE warehouse_id = $form->{id}|; + ($form->{orphaned}) = $dbh->selectrow_array($query); + $form->{orphaned} = !$form->{orphaned}; + + $dbh->disconnect; + +} + + +sub save_warehouse { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; + + if ($form->{id}) { + $query = qq|UPDATE warehouse SET + description = |.$dbh->quote($form->{description}).qq| + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO warehouse + (description) + VALUES (|.$dbh->quote($form->{description}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub delete_warehouse { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM warehouse + WHERE id = $form->{id}|; + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + + +sub departments { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->sort_order(); + my $query = qq|SELECT id, description, role + FROM department + ORDER BY 2 $form->{direction}|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $dbh->disconnect; + +} + + + +sub get_department { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT description, role + FROM department + WHERE id = $form->{id}|; + ($form->{description}, $form->{role}) = $dbh->selectrow_array($query); + + map { $form->{$_} = $ref->{$_} } keys %$ref; + + # see if it is in use + $query = qq|SELECT * FROM dpt_trans + WHERE department_id = $form->{id}|; + ($form->{orphaned}) = $dbh->selectrow_array($query); + $form->{orphaned} = !$form->{orphaned}; + + $dbh->disconnect; + +} + + +sub save_department { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; + + if ($form->{id}) { + $query = qq|UPDATE department SET + description = |.$dbh->quote($form->{description}).qq|, + role = '$form->{role}' + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO department + (description, role) + VALUES (| + .$dbh->quote($form->{description}).qq|, '$form->{role}')|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub delete_department { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM department + WHERE id = $form->{id}|; + $dbh->do($query); + + $dbh->disconnect; + +} + + +sub business { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->sort_order(); + my $query = qq|SELECT id, description, discount + FROM business + ORDER BY 2 $form->{direction}|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $dbh->disconnect; + +} + + + +sub get_business { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT description, discount + FROM business + WHERE id = $form->{id}|; + ($form->{description}, $form->{discount}) = $dbh->selectrow_array($query); + + $dbh->disconnect; + +} + + +sub save_business { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{description} =~ s/-(-)+/-/g; + $form->{description} =~ s/ ( )+/ /g; + $form->{discount} /= 100; + + if ($form->{id}) { + $query = qq|UPDATE business SET + description = |.$dbh->quote($form->{description}).qq|, + discount = $form->{discount} + WHERE id = $form->{id}|; + } else { + $query = qq|INSERT INTO business + (description, discount) + VALUES (| + .$dbh->quote($form->{description}).qq|, $form->{discount})|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub delete_business { + 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; + +} + + +sub sic { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "code" unless $form->{sort}; + my @a = qw(code description); + my %ordinal = ( code => 1, + description => 3 ); + my $sortorder = $form->sort_order(\@a, \%ordinal); + my $query = qq|SELECT code, sictype, description + FROM sic + ORDER BY $sortorder|; + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $dbh->disconnect; + +} + + + +sub get_sic { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + my $query = qq|SELECT code, sictype, description + FROM sic + WHERE code = |.$dbh->quote($form->{code}); + 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; + +} + + +sub save_sic { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + foreach my $item (qw(code description)) { + $form->{$item} =~ s/-(-)+/-/g; + } + + # if there is an id + if ($form->{id}) { + $query = qq|UPDATE sic SET + code = |.$dbh->quote($form->{code}).qq|, + sictype = '$form->{sictype}', + description = |.$dbh->quote($form->{description}).qq| + WHERE code = |.$dbh->quote($form->{id}); + } else { + $query = qq|INSERT INTO sic + (code, sictype, description) + VALUES (| + .$dbh->quote($form->{code}).qq|, + '$form->{sictype}',| + .$dbh->quote($form->{description}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->disconnect; + +} + + +sub delete_sic { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $query = qq|DELETE FROM sic + WHERE code = |.$dbh->quote($form->{code}); + $dbh->do($query); + + $dbh->disconnect; + +} + + +sub language { + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + $form->{sort} = "code" unless $form->{sort}; + my @a = qw(code description); + my %ordinal = ( code => 1, + description => 2 ); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq|SELECT code, description + FROM language + ORDER BY $sortorder|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ALL} }, $ref; + } + + $dbh->disconnect; + +} + + + +sub get_language { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT accno, description - FROM gifi - WHERE accno = '$form->{accno}'|; + my $query = qq|SELECT * + FROM language + WHERE code = |.$dbh->quote($form->{code}); my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -268,30 +726,36 @@ sub get_gifi { map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; + $dbh->disconnect; } -sub save_gifi { +sub save_language { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); - - $form->{description} =~ s/'/''/g; - $form->{accno} =~ s/ //g; - # id is the old account number! + $form->{code} =~ s/ //g; + foreach my $item (qw(code description)) { + $form->{$item} =~ s/-(-)+/-/g; + $form->{$item} =~ s/ ( )+/-/g; + } + + # if there is an id if ($form->{id}) { - $query = qq|UPDATE gifi SET - accno = '$form->{accno}', - description = '$form->{description}' - WHERE accno = '$form->{id}'|; + $query = qq|UPDATE language SET + code = |.$dbh->quote($form->{code}).qq|, + description = |.$dbh->quote($form->{description}).qq| + WHERE code = |.$dbh->quote($form->{id}); } else { - $query = qq|INSERT INTO gifi - (accno, description) - VALUES ('$form->{accno}', '$form->{description}')|; + $query = qq|INSERT INTO language + (code, description) + VALUES (| + .$dbh->quote($form->{code}).qq|,| + .$dbh->quote($form->{description}).qq|)|; } $dbh->do($query) || $form->dberror($query); @@ -300,15 +764,14 @@ sub save_gifi { } -sub delete_gifi { +sub delete_language { my ($self, $myconfig, $form) = @_; # connect to database my $dbh = $form->dbconnect($myconfig); - # id is the old account number! - $query = qq|DELETE FROM gifi - WHERE accno = '$form->{id}'|; + $query = qq|DELETE FROM language + WHERE code = |.$dbh->quote($form->{code}); $dbh->do($query) || $form->dberror($query); $dbh->disconnect; @@ -316,6 +779,7 @@ sub delete_gifi { } + sub load_template { my ($self, $form) = @_; @@ -348,6 +812,42 @@ sub save_template { sub save_preferences { my ($self, $myconfig, $form, $memberfile, $userspath) = @_; + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # update name + my $query = qq|UPDATE employee + SET name = |.$dbh->quote($form->{name}).qq|, + role = '$form->{role}' + WHERE login = '$form->{login}'|; + $dbh->do($query) || $form->dberror($query); + + # get default currency + $query = qq|SELECT curr, businessnumber + FROM defaults|; + ($form->{currency}, $form->{businessnumber}) = $dbh->selectrow_array($query); + $form->{currency} =~ s/:.*//; + + $dbh->disconnect; + + my $myconfig = new User "$memberfile", "$form->{login}"; + + foreach my $item (keys %$form) { + $myconfig->{$item} = $form->{$item}; + } + + $myconfig->{password} = $form->{new_password} if ($form->{old_password} ne $form->{new_password}); + + $myconfig->save_member($memberfile, $userspath); + + 1; + +} + + +sub save_defaults { + my ($self, $myconfig, $form) = @_; + map { ($form->{$_}) = split /--/, $form->{$_} } qw(inventory_accno income_accno expense_accno fxgain_accno fxloss_accno); my @a; @@ -358,8 +858,6 @@ sub save_preferences { # connect to database my $dbh = $form->dbconnect_noauto($myconfig); - # these defaults are database wide - # user specific variables are in myconfig # save defaults my $query = qq|UPDATE defaults SET inventory_accno_id = @@ -377,28 +875,27 @@ sub save_preferences { fxloss_accno_id = (SELECT id FROM chart WHERE accno = '$form->{fxloss_accno}'), - invnumber = '$form->{invnumber}', + sinumber = '$form->{sinumber}', + vinumber = '$form->{vinumber}', sonumber = '$form->{sonumber}', ponumber = '$form->{ponumber}', + sqnumber = '$form->{sqnumber}', + rfqnumber = '$form->{rfqnumber}', + partnumber = '$form->{partnumber}', + employeenumber = '$form->{employeenumber}', + customernumber = '$form->{customernumber}', + vendornumber = '$form->{vendornumber}', yearend = '$form->{yearend}', curr = '$form->{curr}', - weightunit = '$form->{weightunit}', - businessnumber = '$form->{businessnumber}' - |; + weightunit = |.$dbh->quote($form->{weightunit}).qq|, + businessnumber = |.$dbh->quote($form->{businessnumber}); $dbh->do($query) || $form->dberror($query); - # update name - my $name = $form->{name}; - $name =~ s/'/''/g; - $query = qq|UPDATE employee - SET name = '$name' - WHERE login = '$form->{login}'|; - $dbh->do($query) || $form->dberror($query); - foreach my $item (split / /, $form->{taxaccounts}) { + $form->{$item} = $form->parse_amount($myconfig, $form->{$item}) / 100; $query = qq|UPDATE tax - SET rate = |.($form->{$item} / 100).qq|, - taxnumber = '$form->{"taxnumber_$item"}' + SET rate = $form->{$item}, + taxnumber = |.$dbh->quote($form->{"taxnumber_$item"}).qq| WHERE chart_id = $item|; $dbh->do($query) || $form->dberror($query); } @@ -406,17 +903,6 @@ sub save_preferences { my $rc = $dbh->commit; $dbh->disconnect; - # save first currency in myconfig - $form->{currency} = substr($form->{curr},0,3); - - my $myconfig = new User "$memberfile", "$form->{login}"; - - foreach my $item (keys %$form) { - $myconfig->{$item} = $form->{$item}; - } - - $myconfig->save_member($memberfile, $userspath); - $rc; } @@ -449,7 +935,7 @@ sub defaultaccounts { WHERE link LIKE '%IC%' ORDER BY accno|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { foreach my $key (split(/:/, $ref->{link})) { @@ -475,7 +961,7 @@ sub defaultaccounts { AND charttype = 'A' ORDER BY accno|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { %{ $form->{IC}{FX_gain}{$ref->{accno}} } = ( id => $ref->{id}, @@ -489,7 +975,7 @@ sub defaultaccounts { AND charttype = 'A' ORDER BY accno|; $sth = $dbh->prepare($query); - $sth->execute || $self->dberror($query); + $sth->execute || $form->dberror($query); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { %{ $form->{IC}{FX_loss}{$ref->{accno}} } = ( id => $ref->{id}, @@ -521,51 +1007,138 @@ sub defaultaccounts { sub backup { - my ($self, $myconfig, $form, $userspath) = @_; + my ($self, $myconfig, $form, $userspath, $gzip) = @_; - my ($tmpfile, $out, $mail); + my $mail; + my $err; - if ($form->{media} eq 'email') { + my @t = localtime(time); + $t[4]++; + $t[5] += 1900; + $t[3] = substr("0$t[3]", -2); + $t[4] = substr("0$t[4]", -2); + + my $boundary = time; + my $tmpfile = "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql"; + my $out = $form->{OUT}; + $form->{OUT} = ">$tmpfile"; + + open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!"); + + # get sequences, functions and triggers + my @tables = (); + my @sequences = (); + my @functions = (); + my @triggers = (); + my @schema = (); + + # get dbversion from -tables.sql + my $file = "$myconfig->{dbdriver}-tables.sql"; - my $boundary = time; - $tmpfile = "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}.sql"; - $out = $form->{OUT}; - $form->{OUT} = ">$tmpfile"; - - use SL::Mailer; - $mail = new Mailer; + open(FH, "sql/$file") or $form->error("sql/$file : $!"); - $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|; - $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|; - $mail->{subject} = "SQL-Ledger Backup / $myconfig->{dbname}-$form->{dbversion}.sql"; - @{ $mail->{attachments} } = ($tmpfile); - $mail->{version} = $form->{version}; - $mail->{fileid} = "$boundary."; + my @a = ; + close(FH); - $myconfig->{signature} =~ s/\\n/\r\n/g; - $mail->{message} = "--\n$myconfig->{signature}"; + @dbversion = grep /defaults \(version\)/, @a; + + $dbversion = "@dbversion"; + $dbversion =~ /(\d+\.\d+\.\d+)/; + $dbversion = User::calc_version($1); + + opendir SQLDIR, "sql/." or $form->error($!); + @a = grep /$myconfig->{dbdriver}-upgrade-.*?\.sql$/, readdir SQLDIR; + closedir SQLDIR; + + my $mindb; + my $maxdb; + + foreach my $line (@a) { + + $upgradescript = $line; + $line =~ s/(^$myconfig->{dbdriver}-upgrade-|\.sql$)//g; - } + ($mindb, $maxdb) = split /-/, $line; + $mindb = User::calc_version($mindb); + + next if $mindb < $dbversion; - if ($form->{OUT}) { - open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!"); - } else { - open(OUT, ">-") or $form->error("STDOUT : $!"); + $maxdb = User::calc_version($maxdb); + + $upgradescripts{$maxdb} = $upgradescript; } - if ($form->{media} eq 'file') { - print OUT qq|Content-Type: Application/File; -Content-Disposition: filename="$myconfig->{dbname}-$form->{dbversion}.sql"\n\n|; + + $upgradescripts{$dbversion} = "$myconfig->{dbdriver}-tables.sql"; + $upgradescripts{functions} = "$myconfig->{dbdriver}-functions.sql"; + + if (-f "sql/$myconfig->{dbdriver}-custom_tables.sql") { + $upgradescripts{customtables} = "$myconfig->{dbdriver}-custom_tables.sql"; + } + if (-f "sql/$myconfig->{dbdriver}-custom_functions.sql") { + $upgradescripts{customfunctions} = "$myconfig->{dbdriver}-custom_functions.sql"; + } + + foreach my $key (sort keys %upgradescripts) { + + $file = $upgradescripts{$key}; + + open(FH, "sql/$file") or $form->error("sql/$file : $!"); + + push @schema, qq|-- $file\n|; + + while () { + + if (/create table (\w+)/i) { + push @tables, $1; + } + + if (/create sequence (\w+)/i) { + push @sequences, $1; + } + + if (/end function/i) { + push @functions, $_; + $function = 0; + next; + } + + if (/create function /i) { + $function = 1; + } + + if ($function) { + push @functions, $_; + next; + } + + if (/end trigger/i) { + push @triggers, $_; + $trigger = 0; + next; + } + + if (/create trigger/i) { + $trigger = 1; + } + + if ($trigger) { + push @triggers, $_; + next; + } + + push @schema, $_ if $_ !~ /^(insert|--)/i; + + } + close(FH); + } + # connect to database my $dbh = $form->dbconnect($myconfig); - # get all the tables - my @tables = $dbh->tables; - my $today = scalar localtime; - $myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost}; @@ -577,39 +1150,56 @@ Content-Disposition: filename="$myconfig->{dbname}-$form->{dbversion}.sql"\n\n|; -- User: $myconfig->{name} -- Date: $today -- --- set options +|; + + + my $restrict = ($myconfig->{dbdriver} eq 'DB2') ? "RESTRICT" : ""; + + @tables = grep !/^temp/, @tables; + # drop tables and sequences + map { print OUT qq|DROP TABLE $_;\n| } @tables; + map { print OUT qq|DROP SEQUENCE $_ $restrict;\n| } @sequences; + + print OUT "--\n"; + + # triggers and index files are dropped with the tables + + # drop functions + foreach $item (@functions) { + if ($item =~ /create function (.*\))/i) { + print OUT qq|DROP FUNCTION $1;\n|; + } + } + + # add schema + print OUT @schema; + print OUT "\n"; + + print OUT qq|-- set options $myconfig->{dboptions}; -- |; + my $query; + my $sth; + my @arr; + my $fields; + foreach $table (@tables) { - my $query = qq|SELECT * FROM $table|; - - my $sth = $dbh->prepare($query); + + $query = qq|SELECT * FROM $table|; + $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $query = qq|INSERT INTO $table (|; - map { $query .= qq|$sth->{NAME}->[$_],| } (0 .. $sth->{NUM_OF_FIELDS} - 1); - chop $query; - + $query .= join ',', (map { $sth->{NAME}->[$_] } (0 .. $sth->{NUM_OF_FIELDS} - 1)); $query .= qq|) VALUES|; - print OUT qq|-- -DELETE FROM $table; -|; - while (my @arr = $sth->fetchrow_array) { + while (@arr = $sth->fetchrow_array) { $fields = "("; - foreach my $item (@arr) { - if (defined $item) { - $item =~ s/'/''/g; - $fields .= qq|'$item',|; - } else { - $fields .= 'NULL,'; - } - } - - chop $fields; + + $fields .= join ',', map { $dbh->quote($_) } @arr; $fields .= ")"; print OUT qq|$query $fields;\n|; @@ -618,28 +1208,103 @@ DELETE FROM $table; $sth->finish; } - $query = qq|SELECT last_value FROM id|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - my ($id) = $sth->fetchrow_array; - $sth->finish; + # create sequences and triggers + foreach $item (@sequences) { + if ($myconfig->{dbdriver} eq 'DB2') { + $query = qq|SELECT NEXTVAL FOR $item FROM sysibm.sysdummy1|; + } else { + $query = qq|SELECT last_value FROM $item|; + } + + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + my ($id) = $sth->fetchrow_array; + $sth->finish; + $id++; - print OUT qq|-- -DROP SEQUENCE id; -CREATE SEQUENCE id START $id; -|; + print OUT qq|-- +DROP SEQUENCE $item $restrict;\n|; + + if ($myconfig->{dbdriver} eq 'DB2') { + print OUT qq|CREATE SEQUENCE $item AS INTEGER START WITH $id INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 5;\n|; + } else { + print OUT qq|CREATE SEQUENCE $item START $id;\n|; + } + } + + print OUT "--\n"; + + # functions + map { print OUT $_ } @functions; + + # triggers + map { print OUT $_ } @triggers; + + # add the index files + open(FH, "sql/$myconfig->{dbdriver}-indices.sql"); + @a = ; + close(FH); + print OUT @a; close(OUT); $dbh->disconnect; + # compress backup if gzip defined + my $suffix = ""; + if ($gzip) { + my @args = split / /, $gzip; + my @s = @args; + + push @args, "$tmpfile"; + system(@args) == 0 or $form->error("$args[0] : $?"); + + shift @s; + my %s = @s; + $suffix = ${-S} || ".gz"; + $tmpfile .= $suffix; + } + if ($form->{media} eq 'email') { - my $err = $mail->send($out); - $_ = $tmpfile; - unlink; + + use SL::Mailer; + $mail = new Mailer; + + $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|; + $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|; + $mail->{subject} = "SQL-Ledger Backup / $myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix"; + @{ $mail->{attachments} } = ($tmpfile); + $mail->{version} = $form->{version}; + $mail->{fileid} = "$boundary."; + + $myconfig->{signature} =~ s/\\n/\r\n/g; + $mail->{message} = "-- \n$myconfig->{signature}"; + + $err = $mail->send($out); } + + if ($form->{media} eq 'file') { + + open(IN, "$tmpfile") or $form->error("$tmpfile : $!"); + open(OUT, ">-") or $form->error("STDOUT : $!"); + + print OUT qq|Content-Type: application/file; +Content-Disposition: attachment; filename="$myconfig->{dbname}-$form->{dbversion}-$t[5]$t[4]$t[3].sql$suffix" + +|; + + while () { + print OUT $_; + } + + close(IN); + close(OUT); + } + + unlink "$tmpfile"; + } @@ -648,13 +1313,9 @@ sub closedto { my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT closedto, revtrans FROM defaults|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - ($form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array; - - $sth->finish; + my $query = qq|SELECT closedto, revtrans, audittrail + FROM defaults|; + ($form->{closedto}, $form->{revtrans}, $form->{audittrail}) = $dbh->selectrow_array($query); $dbh->disconnect; @@ -664,15 +1325,15 @@ sub closedto { sub closebooks { my ($self, $myconfig, $form) = @_; - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->dbconnect_noauto($myconfig); if ($form->{revtrans}) { - + $query = qq|UPDATE defaults SET closedto = NULL, revtrans = '1'|; } else { if ($form->{closedto}) { - + $query = qq|UPDATE defaults SET closedto = '$form->{closedto}', revtrans = '0'|; } else { @@ -682,11 +1343,134 @@ sub closebooks { } } + if ($form->{audittrail}) { + $query .= qq|, audittrail = '1'|; + } else { + $query .= qq|, audittrail = '0'|; + } + # set close in defaults $dbh->do($query) || $form->dberror($query); + + if ($form->{removeaudittrail}) { + $query = qq|DELETE FROM audittrail + WHERE transdate < '$form->{removeaudittrail}'|; + $dbh->do($query) || $form->dberror($query); + } + + + $dbh->commit; + $dbh->disconnect; +} + + +sub earningsaccounts { + my ($self, $myconfig, $form) = @_; + + my ($query, $sth, $ref); + + # connect to database + my $dbh = $form->dbconnect($myconfig); + + # get chart of accounts + $query = qq|SELECT accno,description + FROM chart + WHERE charttype = 'A' + AND category = 'Q' + ORDER by accno|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + $form->{chart} = ""; + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{chart} }, $ref; + } + $sth->finish; + $dbh->disconnect; + +} + + +sub post_yearend { + my ($self, $myconfig, $form) = @_; + + # connect to database, turn off AutoCommit + my $dbh = $form->dbconnect_noauto($myconfig); + + my $query; + my $uid = time; + $uid .= $form->{login}; + + $query = qq|INSERT INTO gl (reference, employee_id) + VALUES ('$uid', (SELECT id FROM employee + WHERE login = '$form->{login}'))|; + $dbh->do($query) || $form->dberror($query); + + $query = qq|SELECT id FROM gl + WHERE reference = '$uid'|; + ($form->{id}) = $dbh->selectrow_array($query); + + $query = qq|UPDATE gl SET + reference = |.$dbh->quote($form->{reference}).qq|, + description = |.$dbh->quote($form->{description}).qq|, + notes = |.$dbh->quote($form->{notes}).qq|, + transdate = '$form->{transdate}', + department_id = 0 + WHERE id = $form->{id}|; + + $dbh->do($query) || $form->dberror($query); + + my $amount; + my $accno; + + # insert acc_trans transactions + for my $i (1 .. $form->{rowcount}) { + # extract accno + ($accno) = split(/--/, $form->{"accno_$i"}); + $amount = 0; + + if ($form->{"credit_$i"} != 0) { + $amount = $form->{"credit_$i"}; + } + if ($form->{"debit_$i"} != 0) { + $amount = $form->{"debit_$i"} * -1; + } + + + # if there is an amount, add the record + if ($amount != 0) { + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, + source) + VALUES + ($form->{id}, (SELECT id + FROM chart + WHERE accno = '$accno'), + $amount, '$form->{transdate}', | + .$dbh->quote($form->{reference}).qq|)|; + + $dbh->do($query) || $form->dberror($query); + } + } + + $query = qq|INSERT INTO yearend (trans_id, transdate) + VALUES ($form->{id}, '$form->{transdate}')|; + $dbh->do($query) || $form->dberror($query); + + my %audittrail = ( tablename => 'gl', + reference => $form->{reference}, + formname => 'yearend', + action => 'posted', + id => $form->{id} ); + $form->audittrail($dbh, "", \%audittrail); + # commit and redirect + my $rc = $dbh->commit; + $dbh->disconnect; + + $rc; + }