#!/nfs/perl/bin/perl -w
#
# zeffex - Create an Excel file from zeff time data
#
# Thomas Linden <tom@daemon.de>
#
# Copyright (c) 2002 Thomas Linden.

use strict;
use Spreadsheet::WriteExcel;
use Getopt::Std;

my $VERSION = "1.3.3";
my $me = $0;
$me =~ s(.*/)();
$me = uc($me);

my ($zero, %opt);
$zero = "00:00";

getopts('dvh', \%opt);

my($infile, $off) = @ARGV;

if ($opt{d}) {
  $zero = "0";
}

if ($opt{h}) {
  &usage;
}

if ($opt{v}) {
  print "$0 version $VERSION\n";
  exit;
}

if ($infile) {
  if (!-e $infile) {
    &usage;
  }
}
else {
  &usage;
}

my $num_costs;
my %zeff = &readcsv($infile);

my $outfile;
if ($off) {
  if (-d $off) {
    # directory
    $outfile = $infile;
    $outfile =~ s(.*/)(); # remove path
    $outfile =~ s/\.csv$/\.xls/;
    $outfile = "$off/$outfile";
  }
  else {
    $outfile = $off;
  }
}
else {
  $outfile = $infile;
  $outfile =~ s/\.csv$/\.xls/;
}

my %month = (
	     '01' => 'Jan',
	     '02' => 'Feb',
	     '03' => 'Mar',
	     '04' => 'Apr',
	     '05' => 'May',
	     '06' => 'Jun',
	     '07' => 'Jul',
	     '08' => 'Aug',
	     '09' => 'Sep',
	     '10' => 'Oct',
	     '11' => 'Nov',
	     '12' => 'Dec',
	    );

my $title;
if ($infile =~ /(\d\d)(\d\d\d\d)/) {
  $title = "Time Accounting for $month{$1} $2";
}
else {
  $title = "Time Accounting";
}


my $workbook  = new Spreadsheet::WriteExcel($outfile);
my $worksheet = $workbook->addworksheet();




# format for the day headings
my $header_f = $workbook->addformat();
$header_f->set_bold();
$header_f->set_color('blue');
$header_f->set_size(12);
$header_f->set_align('center');
$header_f->set_border();

# format for the cost heading
my $costhead_f = $workbook->addformat();
$costhead_f->set_bold();
$costhead_f->set_color('blue');
$costhead_f->set_size(12);
$costhead_f->set_align('left');
$costhead_f->set_border();

# format for the title
my $title_f = $workbook->addformat();
$title_f->set_bold();
$title_f->set_size(14);
$title_f->set_align('left');
$title_f->set_color('blue');
$title_f->set_underline();

# format for the accounted time
my $time_f = $workbook->addformat();
$time_f->set_size(10);
$time_f->set_align('center');
$time_f->set_border();

# format for zero time entries
my $zero_f = $workbook->addformat();
$zero_f->set_size(10);
$zero_f->set_align('center');
$zero_f->set_color('silver');
$zero_f->set_border();

# format for the cost center
my $cost_f = $workbook->addformat();
$cost_f->set_bold();
$cost_f->set_size(12);
$cost_f->set_border();

my $copy_f = $workbook->addformat();
$copy_f->set_size(8);
$copy_f->set_color('gray');




# starting point
my $row = 2;
my $col = 1;


#
# create the sheet

#
# write the title
$worksheet->set_row($row, 20);
$worksheet->write($row, $col, $title, $title_f);

$row += 3;


#
# write the field headings
$worksheet->write($row, $col, "Cost Center", $costhead_f);
$worksheet->write($row, $col+1, "", $header_f);

$worksheet->set_column($col+1, $col+32, 6);

foreach my $day (1 .. 31) {
  my $column = ($col + 1) + $day;
  $worksheet->write($row, $column, " $day.", $header_f);
}

$row++;

my %sum; # keep the summary of each cost center

#
# write the time data
foreach my $cost (sort keys %zeff) {
  $worksheet->write($row, $col, $cost, $cost_f);
  $worksheet->write($row, $col+1, "", $time_f);
  foreach my $day (1 .. 31) {
    my $column = ($col+1) + $day;
    $day = sprintf "%02d", $day;
    my $cell;
    if (exists $zeff{$cost}->{$day}) {
      $cell = $zeff{$cost}->{$day};
      $worksheet->write($row, $column, $cell , $time_f);
    }
    else {
      $cell = $zero;
      $worksheet->write($row, $column, $cell , $zero_f);
    }
  }
  $row++;
}


$row += 3;
$worksheet->write($row, $col, "Summary", $costhead_f);
$worksheet->write($row, $col+1, "", $costhead_f);
$row++;
foreach my $cost(sort keys %sum) {
  $worksheet->write($row, $col, $cost, $cost_f);
  $worksheet->write($row, $col+1, &sec2hours($sum{$cost}), $time_f);
  $row++;
}


#
# write the copyleft
$worksheet->write($row+3, $col, "Created with $me $VERSION on " . scalar localtime(time) . " by $ENV{USER}.", $copy_f);
$worksheet->write($row+4, $col, "Input file was: $infile.", $copy_f);
$worksheet->write($row+6, $col, "$me - Copyright (c) 2002 by Thomas Linden <tom\@daemon.de>.", $copy_f);
$worksheet->write($row+7, $col, "Spreadsheet::WriteExcel - Copyright (c) 2000-2001 by John McNamara <jmcnamara\@cpan.org>.", $copy_f);

$workbook->close();

print "$outfile written successfully\n";

exit;


sub readcsv {
  #
  # open a csv file and parse it's contents
  #
  my $csv = shift;
  my %zeff;
  open CSV, "<$csv" or die "Could not open $csv: $!\n";
  while (<CSV>) {
    chomp;
    next if /^\s*#/; # ignore comments
    next if /^\s*$/; # ignore empty lines
    my($date,$cost,$hours) = split /;/;
    my($day) = $date =~ /^(\d\d)\./;

    my($hour, $min, $sec) = split /:/, $hours;
    if ($sec) {
      if ($sec > 30) {
	$min++;
      }
    }
    my $time = &human2sec($hours);

    if (!$zero) {
      # decimal format
      my $per = int( $min / 0.6 );
      $hour =~ s/^00/0/;
      $hours = "$hour.$per";
    }
    else {
      $hours = "$hour:$min";
    }

    $sum{$cost} += $time;

    $zeff{$cost}->{$day} = $hours ;
    $num_costs++;
  }
  close CSV;

  %zeff;
}




sub usage {
  print STDERR "Usage: $0 [-d] zeff.MMYYYY.csv [output.xls | directory]\n";
  print STDERR "-d use decimal time format. default is 'hh:mm'.\n";
  exit -1;
}


sub sec2hours {
  #
  # convert given seconds into
  # human readable hour value (YY:MM)
  # seconds > 30 will be rounded up to 1 minute
  #
  my $seconds = shift;

  my $hours = $seconds / 3600;
  my($hour, $min) = split /\./, $hours;

  if ($min) {
    $min =~ s/^(\d\d)\d*$/$1/;
  }
  else {
    $min = 0;
  }

  if (!$zero) {
    return "$hour.$min";
  }
  else {
    $min = int($min * 0.6);
    return sprintf "%02d:%02d", $hour, $min;
  }
}


sub human2sec {
  #
  # convert hh:mm:ss => %010d seconds since 1.1.1970
  #
  my $human = shift;
  my ($hh, $mm, $ss) = split /:/, $human;
  if (!$ss) {
    $ss = 0;
  }
  return sprintf "%010d", ($hh * 3600) + ($mm * 60) + $ss;
}

