#!/usr/bin/perl -w

# 
# Author:  Ray Burkholder
# ray@oneunified.net
# Copyright 2007 One Unified
#

use strict;
use DBI;
use File::MkTemp;
use Spreadsheet::WriteExcel;
use OneUnified::Const;

  my $currentcaller = '';  # used for marker to new worksheet
  my $currentname = '';
  my $worksheet;
  my $summaryworksheet;
  my $totalseconds = 0;
  my $reporttotalseconds = 0;

  my $row = 0;
  my $col = 0;

  my $summaryrow = 0;
  my $summarycol = 0;

sub billing2xls( $$ ) {
  my ( $dtStart, $dtEnd ) = @_;  # extension to process
  # format for $dtStart and $dtEnd is 'yyyy/mm/dd hh:mm:ss'

  my $filetemplate = "billingcdr.XXXXXXX";
  my $returnfilename = mktemp( $filetemplate ) . '.xls';
  my $cdrfilename = '/var/www/reports/' . $returnfilename;  
#  my $cdrfilename = '/tmp/' . $returnfilename;  

  my $workbook = Spreadsheet::WriteExcel->new($cdrfilename);
  die "Problems creating new Excel file: $!" unless defined $workbook;

  my $colhdr_format = $workbook->add_format();
  $colhdr_format->set_bold();
  $colhdr_format->set_align( 'center' );

  my $column_format_alignright = $workbook->add_format();
  $column_format_alignright->set_align( 'right' );

  my $column_format_alignleft = $workbook->add_format();
  $column_format_alignleft->set_align( 'left' );

  my $minute_format = $workbook->add_format();
  $minute_format->set_num_format( '0.0' );

  my $date_format = $workbook->add_format(num_format => 'yyyy/mm/dd hh:mm:ss');
  my $time_format = $workbook->add_format(num_format => 'hh:mm:ss');

  my ( $username, $password, $machine ) = getCMCDRParams();
  my $dbh = DBI->connect("DBI:Sybase:server=$machine",$username,$password) or die $DBI::errstr;
  $dbh->do("use CDR");

  my ( $rv, $sth, $rc );
  my $query;

  #--- All Outbound Calls
  $query = qq{
       SELECT DATEADD(ss, dateTimeOrigination, 'Jan 1, 1970 00:00:00') as TimeOrigination,
             callingPartyNumber, originalCalledPartyNumber, duration, Name, Description
       FROM        ( calldetailrecord as a
         left join ccm0303..Device as b  on origDeviceName = b.Name )
       WHERE originalcalledpartynumber like '[89]%'
         and len(originalcalledpartynumber)>10
         and dateTimeOrigination >= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtStart')
         and dateTimeOrigination <= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtEnd')   
       order by  callingpartynumber, datetimeorigination };

  $sth = $dbh->prepare( $query );
  $rv = $sth->execute();

  $summaryworksheet = $workbook->add_worksheet("Summary");

  $summaryworksheet->write_string( $row, $col++, 'Caller',       $colhdr_format );
  $summaryworksheet->write_string( $row, $col++, 'Name',         $colhdr_format );
  $summaryworksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
  $summaryworksheet->write_string( $row, $col++, 'Minutes',      $colhdr_format );

  $summaryworksheet->set_column( 0, 0,   8, $column_format_alignright );
  $summaryworksheet->set_column( 1, 1,  30, $column_format_alignleft );
  $summaryworksheet->set_column( 2, 2,   8, $column_format_alignright );
  $summaryworksheet->set_column( 3, 3,   8, $column_format_alignright );

  while (
    my ( $datetime, $caller, $called, $seconds, $name, $desc ) = $sth->fetchrow_array() 
    ) {

    if ( $caller ne $currentcaller ) {
      # clear out previous total first
      if ( 0 != $totalseconds ) {
        emitsummary( $minute_format );
        }

      # start new worksheet

      $worksheet   = $workbook->add_worksheet("Ext $caller");
      $row = 0;
      $col = 0;

      $worksheet->write_string( $row, $col++, 'Date Time',    $colhdr_format );
      $worksheet->write_string( $row, $col++, 'Caller',       $colhdr_format );
      $worksheet->write_string( $row, $col++, 'Orig. Called', $colhdr_format );
      $worksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
      $worksheet->write_string( $row, $col++, 'Name',         $colhdr_format );
      $worksheet->write_string( $row, $col++, 'Description',  $colhdr_format );

      $worksheet->set_column( 0, 0, 20, $column_format_alignleft );
      $worksheet->set_column( 1, 1,  8, $column_format_alignright );
      $worksheet->set_column( 2, 2, 18, $column_format_alignright );
      $worksheet->set_column( 3, 3,  8, $column_format_alignright );
      $worksheet->set_column( 4, 4, 20, $column_format_alignleft );
      $worksheet->set_column( 5, 5, 20, $column_format_alignleft );

      $totalseconds = 0;
      $currentcaller = $caller;
      $currentname = $desc;
      } 

    $row++;
    $col=0;
    $worksheet->write_date_time( $row, $col++, $datetime, $date_format );
    $worksheet->write_string( $row, $col++, $caller );
    $worksheet->write_string( $row, $col++, $called );
    $worksheet->write_number( $row, $col++, $seconds );
    $worksheet->write_string( $row, $col++, $name );
    $worksheet->write_string( $row, $col++, $desc );
  
    $totalseconds += $seconds;
    }
  $sth->finish();

  emitsummary( $minute_format );

  $summaryrow++;
  $summaryworksheet->write_number( $summaryrow, 2, $reporttotalseconds );
  $summaryworksheet->write_number( $summaryrow, 3, $reporttotalseconds/60, $minute_format );

  $workbook->close();
  $dbh->disconnect; 

  return $returnfilename;

  }

sub emitsummary( $ ) {
  my ( $minute_format ) = @_;

  $row++;
  $worksheet->write_string( $row, 2, 'Seconds' );
  $worksheet->write_number( $row, 3, $totalseconds );
  $row++;
  $worksheet->write_string( $row, 2, 'Minutes' );
  $worksheet->write_number( $row, 3, $totalseconds / 60, $minute_format );

  $summaryrow++;
  $summaryworksheet->write_url( $summaryrow, 0, "internal:'Ext $currentcaller'!A1", $currentcaller );
  $summaryworksheet->write_url( $summaryrow, 1, "internal:'Ext $currentcaller'!A1", $currentname );
  $summaryworksheet->write_number( $summaryrow, 2, $totalseconds );
  $summaryworksheet->write_number( $summaryrow, 3, $totalseconds/60, $minute_format );
       
  $reporttotalseconds += $totalseconds;
}

1;