#!/usr/bin/perl 

# csv_to_excel.cgi - given CSV file convert to XLS and output for download
#
# created: 2013-07-27
# author: darko.prelec@gmail.com

use 5.010;
use strict;
#use warnings;
use utf8;

our $VERSION = '1.0';

use Carp;
use CGI;
use Text::CSV;
use Excel::Writer::XLSX;
use Scalar::Util qw/looks_like_number/;

$SIG{__DIE__} = sub {
    use Carp;
    my $err = shift;
    print "<pre>";
    print "ERROR: $err";
    print Carp::longmess();
    print "</pre>";
};

our $FS   = ';';
our $ROOT;
our $CGI  = CGI->new;

 $ROOT   = $ENV{'DOCUMENT_ROOT'};
 
 
sub http_die {
    my $msg = shift;

    print $CGI->header("-content-type" => "text/html");
    print "<h2>Error!</h2>\n";
    print "<b>$!</b>\n";
    print "<b>$Excel::Writer::XLSX::VERSION</b>\n";
    print "<pre>\n$msg\n</pre>\n";

    print "<pre>";
    for my $k (sort keys %ENV) {
        print "$k => $ENV{$k}\n";
    }
    print "</pre>";

    print "<pre>";
    for my $k (sort keys %INC) {
        print "$k => $INC{$k}\n";
    }
    print "</pre>\n";

    print STDERR "csv2xls.pl error $msg";

    exit;
}

sub convert_csv_to_xlsx {
    my $file = shift;
    my $content;

    open my $fh_csv,   "<", $file     or http_die("Cannot open $file: $!");
    open my $fh_excel, ">", \$content or http_die("Cannot init content: $!");

    my $workbook = Excel::Writer::XLSX->new($fh_excel)
      or http_die("Cannot init Excel::Writer: $!");

    my $worksheet  = $workbook->add_worksheet;
    my $num_format = $workbook->add_format;
    $num_format->set_num_format("0.00");

    my $csv = Text::CSV->new({ binary => 1, sep_char => $FS })
      or http_die("Text::CSV died: " . Text::CSV->error_diag);

    my $count = 0;
    my @format_fields;
    while (my $row = $csv->getline($fh_csv)) {
        if ($count == 0) {
            for my $i (0 .. $#$row) {
                if ($row->[$i] =~ /(Koli.ina|Iznos|Osnovica za PDV)/) {
                    push @format_fields, $i;
                }
            }

            # skip warnings about non-numeric value
            {no warnings; $worksheet->write($count, 0, $row);};

            $count++;
            next;
        }

        for my $i (0 .. $#$row) {
            if (looks_like_number($row->[$i])
                and grep {$i == $_} @format_fields)
            {
                $worksheet->write_number($count, $i, $row->[$i], $num_format);
            }
            else {
                $worksheet->write($count, $i, $row->[$i]);
            }
        }

        $count++;
    }

    $workbook->close;
    return $content;
}

# only for debugging
#$SIG{__DIE__} = sub {
#    http_die(Carp::longmess(@_));
#};

my $file = $CGI->param('file');

if (not defined $file) {
    print $CGI->header("-content-type" => "text/html");
    print "<h2>No file specified</h2>";
    print "No input file specified.";
    exit;
}
elsif ($file !~ m|^/mercur[^\/.]*/([^\/]+)\.csv$|
    or not -e "$ROOT$file")
{
    print $CGI->header(
        "-content-type" => "text/html",
        "-status"       => "404 Not Found"
    );
    print "<h2>Error: File not found or invalid file specified.</h2>";
    exit;
}

my $filename     = "$1.xlsx";
my $xlsx_content = convert_csv_to_xlsx("$ROOT$file");

if (length($xlsx_content) > 0) {
    print "X-Converter-Agent: csv_to_excel.pl (v.$VERSION)\n";
    print "Content-length: " . length($xlsx_content) . "\n";
    print "Content-type: application/vnd.ms-excel\n";
    print "Content-Disposition:attachment;filename=$filename\n\n";
    print $xlsx_content;
}
else {
    print $CGI->header("-content-type" => "text/html");
    print "<h2>Error: failed to generate Excel file</h2>";
}