package Lire::ReportParser::ExcelWriter;

use strict;

use vars qw/ $DATE_SYSTEM @name_cols_width/;

use base qw/ Lire::ReportParser::RowColHandler /;

use Lire::ReportParser::AsciiDocBookFormatter qw/dbk2txt/;

use POSIX qw/ceil/;
use Spreadsheet::WriteExcel;
#use Time::Timezone;

use constant MAX_EXCEL_STRING   => 255;
use constant MAX_SHEET_STRING   => 31;
use constant DEFAULT_ROW_HEIGHT => 12.75;

# Those values are in Excel Points
#use constant CHAR_WIDTH                => 5.3; 
#use constant DEFAULT_CELL_WIDTH => 48;
#use constant MAX_CELL_WIDTH    => 4 * 48;
# Spreadsheet::WriteExcel seems to work with characters
use constant CHAR_WIDTH => 1;
use constant DEFAULT_CELL_WIDTH => 9;
use constant MAX_CELL_WIDTH => 36;

BEGIN {
    # Use 1900 date system on all platforms other than Apple Mac (for which
    # use 1904 date system).
    $DATE_SYSTEM = ($^O eq 'MacOS') ? 1 : 0;
}

#
# This was inspired by the comments and programs datecalc1.pl 
# included in Spreadsheet::WriteExcel and is
# Copyright (c) 2000, Andrew Benham.
# This program is free software. It may be used, redistributed and/or
# modified under the same terms as Perl itself.
#
# It was modified to use Time::Timezone which is included in Lire.
#
sub epoch2excel_date {
    my ($time) = @_;

    # Divide timestamp by number of seconds in a day.
    # This gives a date serial with '0' on 1 Jan 1970.
    my $serial = $time / 86400;

    # Adjust the date serial by the offset appropriate to the
    # currently selected system (1900/1904).
    if ($DATE_SYSTEM == 0) {    # use 1900 system
        $serial += 25569;
    } else {                    # use 1904 system
        $serial += 24107;
    }

    # Actually, this gives the wrong date. Maybe this is specific to
    # gnumeric and OpenOffice on UNIX.
    # The correct date appears if we don't correct the value for time zone.
    #
    # Fix for timezone
    # my $offset = tz_local_offset( $time );
    # $serial += $offset / 8640;

    # Perpetuate the error that 1900 was a leap year by decrementing
    # the serial if we're using the 1900 system and the date is prior to
    # 1 Mar 1900. This has the effect of making serial value '60'
    # 29 Feb 1900.

    # This fix only has any effect if UNIX/Perl time on the platform
    # can represent 1900. Many can't.

    unless ($DATE_SYSTEM) {
        $serial-- if ($serial < 61);    # '61' is 1 Mar 1900
    }

    return $serial;
}

sub new {
    my ( $self, %args ) = @_;

    $self = $self->SUPER::new( %args,
                               'summary_when' => 'after',
                             );
    $self->{'_output_file'} =  $args{'output_file'} || '-',

    return $self;
}

sub report_start {
    my $self = shift;
    $self->SUPER::report_start( @_ );

    $self->{'report_date'} = undef;
    $self->{'report_timespan'} = undef;
    $self->{'workbook'} = new Spreadsheet::WriteExcel($self->{'_output_file'});

    # Create some format that we are going to use
    $self->{'title_fmt'} =
      $self->{'workbook'}->addformat(
                                   'size' => 18,
                                   'bold' => 1,
                                  );
    $self->{'date_fmt'} = 
      $self->{'workbook'}->addformat(
                                   'num_format' => "yyyy/mm/dd",
                                   'align'      => "left",
                                   'valign'     => "top",
                                  );
    $self->{'timestamp_fmt'} = 
      $self->{'workbook'}->addformat(
                                   'num_format' => "yyyy/mm/dd hh:mm:ss",
                                   'align'      => "left",
                                   'valign'     => "top",
                                  );
    $self->{'datetime_fmt'} = 
      $self->{'workbook'}->addformat(
                                   'num_format' => "yyyy/mm/dd hh:mm",
                                   'align'      => "left",
                                   'valign'     => "top",
                                  );
    $self->{'time_fmt'} = 
      $self->{'workbook'}->addformat(
                                   'num_format' => "hh:mm",
                                   'align'      => "left",
                                   'valign'     => "top",
                                  );
    $self->{'name_fmt'} =
      $self->{'workbook'}->addformat(
                                   'num_format' => 0x31,  # Builtin: @
                                   'align'      => "justify",
                                   'valign'     => "top",
                                  );
    $self->{'label_fmt'} =
      $self->{'workbook'}->addformat(
                                   'num_format' => 0x31,  # Builtin: @
                                   'align'      => "left",
                                   'valign'     => "left",
                                   'bold'       => 1,
                                   'size'       => 12,
                                  );
    $self->{'header_fmt'} =
      $self->{'workbook'}->addformat(
                                   'num_format' => 0x31,  # Builtin: @
                                   'align'      => "left",
                                   'valign'     => "left",
                                   'bold'       => 1,
                                  );
    $self->{'description_fmt'} =
      $self->{'workbook'}->addformat(
                                   'num_format' => 0x31,  # Builtin: @
                                   'align'      => "left",
                                   'valign'     => "left",
                                  );

    $self->{'value_fmt'} = 
      $self->{'workbook'}->addformat(
                                   'num_format' => 0x00,   # Builtin: General
                                   'align'      => "right",
                                   'valign'     => "top",
                                  );
}

sub report_end {
    my $self = shift;
    $self->SUPER::report_end( @_ );

    # Activate the first sheet
    my $first = ($self->{'workbook'}->sheets)[0];
    if ( $first ) {
        $first->set_first_sheet();
        $first->activate();
    }
    $self->{'workbook'}->close();

    return;
}

sub section_end {
    my $self = shift;

    $self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
                                    "No subreports were generated for this section.",
                                    $self->{'label_fmt'},
                                  )
      if ( ! $self->current_section_subreport_count() );

    $self->SUPER::section_end( @_ );
    return;
}

sub handle_title {
    my ( $self, $title ) = @_;

    # Trim the title
    $title =~ s/^\s*//;
    $title =~ s/\s*$//;
    if ($self->in_element( "lire:section" ) ) {
        # Section's title
        my $title = substr( $title, 0, MAX_SHEET_STRING );

        my $first_sheet = ! $self->{'workbook'}->sheets;

        # Create a new worksheet
        $self->{'curr_section'} = $self->{'workbook'}->addworksheet( $title );
        $self->{'curr_row'} = 0;
        $self->{'curr_cols_width'} = [];

        if ( $first_sheet ) {
            # Put "header" information on first sheet
            my @labels = ( "Report generated", "Log file started",
                           "Log file ended" );
            my @dates = ( $self->{'report_date'}, @{$self->{'report_timespan'}} );
            for (my $i=0; $i<@labels; $i++) {
                my $date = epoch2excel_date( $dates[$i] );
                $self->{'curr_section'}->write( $i, 0, $labels[$i],
                                              $self->{'label_fmt'},
                                            );
                $self->{'curr_section'}->write( $i, 1, $date,
                                              $self->{'timestamp_fmt'},
                                            );
                $self->{'curr_section'}->set_row( $i, 14 );
            }

            $self->{'curr_row'} = @labels + 1;

            my $width = length("yyyy/mm/dd hh:mm:ss") * CHAR_WIDTH;
            $self->{'curr_cols_width'}[1] = $width;
            $self->{'curr_section'}->set_column( 1, 1, $width );
        }
    } else {
        # Subreport's title
        my $title = substr( $title, 0, MAX_EXCEL_STRING );

        $self->{'curr_section'}->set_row( $self->{'curr_row'}, 22 );
        $self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
                                      $title,
                                      $self->{'title_fmt'},
                                    );
    }

    return;
}

sub handle_description {
    my ( $self, $docbook ) = @_;

    my $desc = dbk2txt( $docbook );
    # Put each line in a separate cell
    foreach my $line ( split /\n/, $desc ) {
        if ( defined $line ) {
            # Trim spaces
            $line =~ s/^\s+//;
            $line =~ s/\s+$//;
            $self->{'curr_section'}->write( $self->{'curr_row'}, 0,
                                          $line,
                                          $self->{'description_fmt'},
                                        );
        }

        $self->{'curr_row'}++;
    }

    # Skip one row after the description
    $self->{'curr_row'}++;

    return;
}

sub handle_date {
    my ( $self, $date, $time ) = @_;

    $self->{'report_date'} = $time
      unless defined $self->{'report_date'};

    return;
}

sub handle_timespan {
    my ( $self, $timespan, $start, $end ) = @_;

    $self->{'report_timespan'} = [$start, $end]
      unless defined $self->{'report_timespan'};

    return;
}

sub subreport_end {
    my $self = shift;

    $self->SUPER::subreport_end( @_ );

    # Leave one extra empty row
    $self->{'curr_row'} += 2;

    return;
}

sub table_start {
    my $self = shift;

    $self->SUPER::table_start( @_ );

    $self->{'curr_headers'} = [];

    return;
}

sub table_end {
    my $self = shift;

    # Skip one row
    $self->{'curr_row'}++;

    $self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
                                    "No content in report.",
                                    $self->{'label_fmt'} )
      if ! $self->current_table_entry_count();

    $self->SUPER::table_end( @_ );
    return;
}

sub table_info_end {
    my $self = shift;

    $self->SUPER::table_info_end( @_ );

    # Upgrade the column's width of the sheet based
    # on the suggested column's width
    my $table_info = $self->current_table_info();
    my @cols = $table_info->column_infos();

    foreach my $col ( @cols ) {
        my $i = $col->col_start;
        my $curr_width  = $self->{'curr_cols_width'}[$i] || DEFAULT_CELL_WIDTH;
        my $width = $col->col_width * CHAR_WIDTH;

        $width = MAX_CELL_WIDTH if $width > MAX_CELL_WIDTH;

        if ( $width > $curr_width ) {
            $self->{'curr_cols_width'}[$i] = $width;
            $self->{'curr_section'}->set_column( $i, $i, $width );
        }
    }
    return;
}

sub handle_header_row {
    my ( $self, $row ) = @_;

    push @{$self->{'curr_headers'}}, $row;

    return;
}

sub write_header_rows {
    my $self = $_[0];

    foreach my $row ( @{$self->{'curr_headers'}} ) {
        $self->{'curr_row'}++;
        $self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;

        foreach my $c ( @$row ) {
            next unless defined $c;

            my $s = substr $c->label, 0, MAX_EXCEL_STRING;
            $self->{'curr_section'}->write( $self->{'curr_row'}, $c->col_start,
                                          $s, $self->{'header_fmt'} );
        }
    }
    return;
}

sub handle_row {
    my ( $self, $row ) = @_;

    if ( $self->{'curr_headers'} ) {
        $self->write_header_rows;
        delete $self->{'curr_headers'};
    }

    $self->{'curr_row'}++;
    $self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;

    foreach my $c ( @$row ) {
        next unless defined $c;

        if ($c->{'col_info'}->class eq 'categorical' ) {
            $self->write_categorical_cell( $c );
        } else {
            $self->write_value_cell( $c );
        }
    }
    return;
}

sub handle_table_summary {
    my ( $self, $nrecords, $row ) = @_;

    return unless $nrecords &&
      $self->current_table_entry_count();

    $self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;

    # Write the summary cell
    $self->{'curr_section'}->write( $self->{'curr_row'},
                                  0,
                                  "Total",
                                  $self->{'header_fmt'},
                                );

    foreach my $c ( @$row ) {
        next unless defined $c;

        $self->write_value_cell( $c );
    }

    # We increase it only after because it was already increased
    # in table_end
    $self->{'curr_row'}++;

    return;
}

sub write_categorical_cell {
    my ( $self, $name ) = @_;

    my $s = substr $name->{'content'}, 0, MAX_EXCEL_STRING;
    my $fmt = $self->{'name_fmt'};

    # Convert time classes to their Excel representation
    if ( $name->{'value'} =~ /^\d+$/ &&
         $name->{'content'} !~ /^(\[|Week)/ && #! Skip Week and rangegroup
         defined $name->{'range'} &&
         $name->{'range'} =~ /^\d+$/
       )
    {
        $s = epoch2excel_date( $name->{'value'} );
        if ( $name->{'range'} < 86400 ) {
            $fmt = $self->{'datetime_fmt'};
        } else {
            $fmt = $self->{'date_fmt'};
        }
    }

    my $col = $name->{'col_info'}->col_start;
    my $len = length $s;

    # Enlarge the row's height i
    if ( $len * CHAR_WIDTH > MAX_CELL_WIDTH ) {
        my $row = ceil( $len * CHAR_WIDTH / MAX_CELL_WIDTH );
        my $height = $row * DEFAULT_ROW_HEIGHT;

        if ( $height > $self->{'curr_row_height'} ) {
            $self->{'curr_section'}->set_row( $self->{'curr_row'}, $height, );
            $self->{'curr_row_height'} = $height;
        }
    }

    # Write it
    $self->{'curr_section'}->write( $self->{'curr_row'}, $col, $s, $fmt );

    return;
}

sub write_value_cell {
    my ( $self, $value ) = @_;

    $self->{'curr_section'}->write( $self->{'curr_row'},
                                  $value->{'col_info'}->col_start,
                                  $value->{'content'},
                                  $self->{'value_fmt'} );

    return;
}

1;

__END__

=pod

=head1 NAME

Lire::ReportParser::ExcelWriter - Transform Lire XML Reports into a Excel95(tm) spreadsheet.

=head1 SYNOPSIS

    use Lire::ReportParser::ExceltWriter;

    my $parser = new Lire::ReportParser::ExcelWriter();

    eval { $parser->parsefile( "report.xml" ) };
    print "Parse failed: $@" if $@;

=head1 DESCRIPTION

This is a Lire::ReportParser processor which will transform the Lire
XML report into a Excel95(tm) spreadsheet which will be output on
STDOUT.

Its constructor doesn't take any parameters.

=head1 SEE ALSO

Lire::ReportParser(3pm) Spreadsheet::WriteExcel(3pm)

=head1 VERSION

$Id: ExcelWriter.pm,v 1.19 2006/07/23 13:16:31 vanbaal Exp $

=head1 COPYRIGHT

Copyright (C) 2001-2002 Stichting LogReport Foundation LogReport@LogReport.org

This file is part of Lire.

Lire is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program (see COPYING); if not, check with
http://www.gnu.org/copyleft/gpl.html. 

=head1 AUTHOR

Francis J. Lacoste <flacoste@logreport.org>

=cut