/[SQL2XLS]/sql2xlsx.cgi
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /sql2xlsx.cgi

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 5 by dpavlin, Mon Nov 3 18:31:58 2008 UTC revision 10 by dpavlin, Mon Nov 3 19:19:39 2008 UTC
# Line 36  use Encode qw/decode/; Line 36  use Encode qw/decode/;
36  use Data::Dump qw/dump/;  use Data::Dump qw/dump/;
37    
38  # edit following to set defaults  # edit following to set defaults
39  my $dsn      = 'DBI:Pg:dbname=';  our $dsn      = 'DBI:Pg:dbname=';
40  my $database = 'template1';  our $database = 'template1';
41  my $user     = 'dpavlin';  our $user     = 'dpavlin';
42  my $passwd   = '';  our $passwd   = '';
43  my $path     = 'sql_reports.xls';  our $path     = 'sql_reports.xls';
44    
45  my $db_encoding     = 'iso-8859-2';  our $db_encoding     = 'iso-8859-2';
46  my $xls_date_format = 'dd.mm.yyyy';  our $xls_date_format = 'dd.mm.yyyy';
47    
48  my $debug = 1;  our $debug = 1;
49    
50  my $sql_dir = path_translated || '.';  my $sql_dir = path_translated || '.';
51  $sql_dir =~ s,/[^/]+$,,;  $sql_dir =~ s,/[^/]+$,,;
# Line 54  opendir(DIR, $sql_dir) || die "can't ope Line 54  opendir(DIR, $sql_dir) || die "can't ope
54  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);  my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
55  closedir DIR;  closedir DIR;
56    
57    my $config_path = "$sql_dir/config.pl";
58    warn "# using $config_path\n";
59    require $config_path if -e $config_path;
60    
61  my $workbook;  my $workbook;
62  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {  if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
63          # use as cgi script          # use as cgi script
# Line 83  foreach my $sql_file (@sql_files) { Line 87  foreach my $sql_file (@sql_files) {
87          # Add a worksheet          # Add a worksheet
88          my $worksheet = $workbook->addworksheet($sheet_name);          my $worksheet = $workbook->addworksheet($sheet_name);
89    
90          print STDERR "working on $sql_file...\n" if ($debug);          print STDERR "working on $sql_file\n" if ($debug);
91    
92          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";          open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
93          my $comment;          my $comment = '';
94          my $sql = "";          my $sql = "";
95          while(<SQL>) {          while(<SQL>) {
96                  chomp;                  chomp;
97                  if (/^\\c\s+(\S+)/) {                  if (/^\\c\s+(\S+)/) {
98                          warn "## connect to $1\n" if $debug;                          $dbh->disconnect if $dbh;
99                            print STDERR "## connect to $1\n" if $debug;
100                          $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;                          $dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
101                  } elsif (/^--(.+)/) {                  } elsif (/^--(.+)/) {
102                          $comment.=$1;                          $comment.=$1;
# Line 113  foreach my $sql_file (@sql_files) { Line 118  foreach my $sql_file (@sql_files) {
118                  my $fmt_comment = $workbook->addformat();    # Add a format                  my $fmt_comment = $workbook->addformat();    # Add a format
119                  $fmt_comment->set_bold();                  $fmt_comment->set_bold();
120    
121                    $comment =~ s/^\s+//;
122                    $comment =~ s/\s+$//;
123    
124                  $worksheet->write($row, 0, _c($comment), $fmt_comment);                  $worksheet->write($row, 0, _c($comment), $fmt_comment);
125                  $row+=2;                  $row+=2;
126          }          }
# Line 128  foreach my $sql_file (@sql_files) { Line 136  foreach my $sql_file (@sql_files) {
136          }          }
137          $row++;          $row++;
138    
139          my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} };          my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
140    
141          while (my @row = $sth->fetchrow_array() ) {          while (my @row = $sth->fetchrow_array() ) {
142                  for(my $col=0; $col<=$#row; $col++) {                  for(my $col=0; $col<=$#row; $col++) {
Legend:
Removed from v.5  
changed lines
  Added in v.10
  ViewVC Help
Powered by ViewVC 1.1.26