- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- %{ $form->{IC}{FX_gain}{ $ref->{accno} } } = (
- id => $ref->{id},
- description => $ref->{description}
- );
- }
- $sth->finish;
-
- $query = qq|SELECT c.id, c.accno, c.description
- FROM chart c
- WHERE c.category = 'E'
- AND c.charttype = 'A'
- ORDER BY c.accno|;
- $sth = $dbh->prepare($query);
- $sth->execute || $self->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- %{ $form->{IC}{FX_loss}{ $ref->{accno} } } = (
- id => $ref->{id},
- description => $ref->{description}
- );
- }
- $sth->finish;
-
- # now get the tax rates and numbers
- $query = qq|SELECT c.id, c.accno, c.description,
- t.rate * 100 AS rate, t.taxnumber
- FROM chart c, tax t
- WHERE c.id = t.chart_id|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $form->{taxrates}{ $ref->{accno} }{id} = $ref->{id};
- $form->{taxrates}{ $ref->{accno} }{description} = $ref->{description};
- $form->{taxrates}{ $ref->{accno} }{taxnumber} = $ref->{taxnumber}
- if $ref->{taxnumber};
- $form->{taxrates}{ $ref->{accno} }{rate} = $ref->{rate} if $ref->{rate};
- }
-
- $sth->finish;
- $dbh->disconnect;
-
- $main::lxdebug->leave_sub();
-}
-
-sub backup {
- $main::lxdebug->enter_sub();
-
- my ($self, $myconfig, $form, $userspath) = @_;
-
- my $mail;
- my $err;
- my $boundary = time;
- my $tmpfile =
- "$userspath/$boundary.$myconfig->{dbname}-$form->{dbversion}.sql";
- my $out = $form->{OUT};
- $form->{OUT} = ">$tmpfile";
-
- if ($form->{media} eq 'email') {
-
- use SL::Mailer;
- $mail = new Mailer;
-
- $mail->{to} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
- $mail->{from} = qq|"$myconfig->{name}" <$myconfig->{email}>|;
- $mail->{subject} =
- "Lx-Office Backup / $myconfig->{dbname}-$form->{dbversion}.sql";
- @{ $mail->{attachments} } = ($tmpfile);
- $mail->{version} = $form->{version};
- $mail->{fileid} = "$boundary.";
-
- $myconfig->{signature} =~ s/\\n/\r\n/g;
- $mail->{message} = "--\n$myconfig->{signature}";
-
- }
-
- open(OUT, "$form->{OUT}") or $form->error("$form->{OUT} : $!");
-
- # get sequences, functions and triggers
- open(FH, "sql/lx-office.sql") or $form->error("sql/lx-office.sql : $!");
-
- my @sequences = ();
- my @functions = ();
- my @triggers = ();
- my @indices = ();
- my %tablespecs;
-
- my $query = "";
- my @quote_chars;
-
- while (<FH>) {
-
- # Remove DOS and Unix style line endings.
- s/[\r\n]//g;
-
- # ignore comments or empty lines
- next if /^(--.*|\s+)$/;
-
- for (my $i = 0; $i < length($_); $i++) {
- my $char = substr($_, $i, 1);
-
- # Are we inside a string?
- if (@quote_chars) {
- if ($char eq $quote_chars[-1]) {
- pop(@quote_chars);
- }
- $query .= $char;
-
- } else {
- if (($char eq "'") || ($char eq "\"")) {
- push(@quote_chars, $char);
-
- } elsif ($char eq ";") {
-
- # Query is complete. Check for triggers and functions.
- if ($query =~ /^create\s+function\s+\"?(\w+)\"?/i) {
- push(@functions, $query);
-
- } elsif ($query =~ /^create\s+trigger\s+\"?(\w+)\"?/i) {
- push(@triggers, $query);
-
- } elsif ($query =~ /^create\s+sequence\s+\"?(\w+)\"?/i) {
- push(@sequences, $1);
-
- } elsif ($query =~ /^create\s+table\s+\"?(\w+)\"?/i) {
- $tablespecs{$1} = $query;
-
- } elsif ($query =~ /^create\s+index\s+\"?(\w+)\"?/i) {
- push(@indices, $query);
-
- }
-
- $query = "";
- $char = "";
- }
-
- $query .= $char;
- }
- }
- }
- close(FH);
-
- # connect to database
- my $dbh = $form->dbconnect($myconfig);
-
- # get all the tables
- my @tables = $dbh->tables('', '', 'customer', '', { noprefix => 0 });
-
- my $today = scalar localtime;
-
- $myconfig->{dbhost} = 'localhost' unless $myconfig->{dbhost};
-
- print OUT qq|-- Lx-Office Backup
--- Dataset: $myconfig->{dbname}
--- Version: $form->{dbversion}
--- Host: $myconfig->{dbhost}
--- Login: $form->{login}
--- User: $myconfig->{name}
--- Date: $today
---
--- set options
-$myconfig->{dboptions};
---
-|;
-
- print OUT "-- DROP Sequences\n";
- my $item;
- foreach $item (@sequences) {
- print OUT qq|DROP SEQUENCE $item;\n|;
- }
-
- print OUT "-- DROP Triggers\n";
-
- foreach $item (@triggers) {
- if ($item =~ /^create\s+trigger\s+\"?(\w+)\"?\s+.*on\s+\"?(\w+)\"?\s+/i) {
- print OUT qq|DROP TRIGGER "$1" ON "$2";\n|;
- }
- }
-
- print OUT "-- DROP Functions\n";
-
- foreach $item (@functions) {
- if ($item =~ /^create\s+function\s+\"?(\w+)\"?/i) {
- print OUT qq|DROP FUNCTION "$1" ();\n|;
- }
- }
-
- foreach $table (@tables) {
- if (!($table =~ /^sql_.*/)) {
- my $query = qq|SELECT * FROM $table|;
-
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- $query = "INSERT INTO $table (";
- map { $query .= qq|$sth->{NAME}->[$_],| }
- (0 .. $sth->{NUM_OF_FIELDS} - 1);
- chop $query;
-
- $query .= ") VALUES";
-
- if ($tablespecs{$table}) {
- print(OUT "--\n");
- print(OUT "DROP TABLE $table;\n");
- print(OUT $tablespecs{$table}, ";\n");
- } else {
- print(OUT "--\n");
- print(OUT "DELETE FROM $table;\n");
- }
- while (my @arr = $sth->fetchrow_array) {
-
- $fields = "(";
- foreach my $item (@arr) {
- if (defined $item) {
- $item =~ s/\'/\'\'/g;
- $fields .= qq|'$item',|;
- } else {
- $fields .= 'NULL,';
- }
- }
-
- chop $fields;
- $fields .= ")";
-
- print OUT qq|$query $fields;\n|;
- }
-
- $sth->finish;
- }
- }
-
- # create indices, sequences, functions and triggers
-
- print(OUT "-- CREATE Indices\n");
- map({ print(OUT "$_;\n"); } @indices);
-
- print OUT "-- CREATE Sequences\n";
- foreach $item (@sequences) {
- $query = qq|SELECT last_value FROM $item|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
- my ($id) = $sth->fetchrow_array;
- $sth->finish;
-
- print OUT qq|--
-CREATE SEQUENCE $item START $id;
-|;
- }
-
- print OUT "-- CREATE Functions\n";
-
- # functions
- map { print(OUT $_, ";\n"); } @functions;
-
- print OUT "-- CREATE Triggers\n";
-
- # triggers
- map { print(OUT $_, ";\n"); } @triggers;
-
- close(OUT);
-
- $dbh->disconnect;
-
- # compress backup
- my @args = ("gzip", "$tmpfile");
- system(@args) == 0 or $form->error("$args[0] : $?");
-
- $tmpfile .= ".gz";
-
- if ($form->{media} eq 'email') {
- @{ $mail->{attachments} } = ($tmpfile);
- $err = $mail->send($out);
- }
-
- if ($form->{media} eq 'file') {
-
- open(IN, "$tmpfile") or $form->error("$tmpfile : $!");
- open(OUT, ">-") or $form->error("STDOUT : $!");