1 files changed, 940 insertions, 156 deletions

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 <jim@your-dba.com>
 #
 # 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);
+  
+  foreach my $item (qw(accno gifi_accno description)) {
+    $form->{$item} =~ s/-(-+)/-/g;
+    $form->{$item} =~ s/ ( )+/ /g;
+  }
   
-  my ($query, $sth);
+  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,7 +245,6 @@ sub gifi_accounts {
     push @{ $form->{ALL} }, $ref;
   }
 
-  $sth->finish;
   $dbh->disconnect;
   
 }
@@ -260,14 +260,17 @@ sub get_gifi {
   my $query = qq|SELECT accno, description
                  FROM gifi
 	         WHERE accno = '$form->{accno}'|;
-  my $sth = $dbh->prepare($query);
-  $sth->execute || $form->dberror($query);
 
-  my $ref = $sth->fetchrow_hashref(NAME_lc);
-  
-  map { $form->{$_} = $ref->{$_} } keys %$ref;
+  ($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};
 
-  $sth->finish;
   $dbh->disconnect;
 
 }
@@ -279,21 +282,26 @@ sub save_gifi {
   # connect to database
   my $dbh = $form->dbconnect($myconfig);
   
-  $form->{description} =~ s/'/''/g;
-  $form->{accno} =~ s/ //g;
+  $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 = '$form->{description}'
+		description = |.$dbh->quote($form->{description}).qq|
 		WHERE accno = '$form->{id}'|;
   } else {
     $query = qq|INSERT INTO gifi 
                 (accno, description)
-                VALUES ('$form->{accno}', '$form->{description}')|;
+                VALUES ('$form->{accno}',|
+		.$dbh->quote($form->{description}).qq|)|;
   }
-  $dbh->do($query) || $form->dberror($query);
+  $dbh->do($query) || $form->dberror; 
   
   $dbh->disconnect;
 
@@ -316,6 +324,462 @@ sub delete_gifi {
 }
 
 
+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 *
+                 FROM language
+	         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_language {
+  my ($self, $myconfig, $form) = @_;
+  
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+
+  $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 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 language
+                (code, description)
+                VALUES (|
+		.$dbh->quote($form->{code}).qq|,|
+		.$dbh->quote($form->{description}).qq|)|;
+  }
+  $dbh->do($query) || $form->dberror($query);
+  
+  $dbh->disconnect;
+
+}
+
+
+sub delete_language {
+  my ($self, $myconfig, $form) = @_;
+  
+  # connect to database
+  my $dbh = $form->dbconnect($myconfig);
+  
+  $query = qq|DELETE FROM language
+	      WHERE code = |.$dbh->quote($form->{code});
+  $dbh->do($query) || $form->dberror($query);
+  
+  $dbh->disconnect;
+
+}
+
+
+
 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 = <FH>;
+  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 (<FH>) {
+
+      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 = <FH>;
+  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 (<IN>) {
+      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,13 +1343,136 @@ 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;
+
+}
+
+
 1;