#!/usr/bin/perl -w

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


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

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

  my $filetemplate = "ext${ext}cdr.XXXXXXX";
  my $returnfilename =  mktemp( $filetemplate ) . '.xls';
  my $cdrfilename = '/var/www/reports/' . $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 = $workbook->add_format();
  $column_format->set_align( 'right' );

  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;
  my $totalseconds;

  #--- Inbound calls
  $query = 
    qq{SELECT DATEADD(ss, dateTimeOrigination, 'Jan 1, 1970 00:00:00') as TimeOrigination, 
             callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, duration
       FROM         CallDetailRecord   
       WHERE     ((originalCalledPartyNumber = '$ext' or finalCalledPartyNumber = '$ext')
         and (len(callingPartyNumber)>5 or len(callingPartyNumber)<4))
         and dateTimeOrigination >= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtStart')
         and dateTimeOrigination <= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtEnd') };

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

  my $worksheet   = $workbook->add_worksheet("Inbound to $ext");

  my $row = 0;
  my $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++, 'Final Called', $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Minutes',      $colhdr_format );

  $worksheet->set_column( 0, 0, 20, $column_format );
  $worksheet->set_column( 1, 1, 12, $column_format );
  $worksheet->set_column( 2, 2, 12, $column_format );
  $worksheet->set_column( 3, 3, 12, $column_format );
  $worksheet->set_column( 4, 4, 10, $column_format );
  $worksheet->set_column( 5, 5, 10, $column_format );

  $totalseconds = 0;
  while (
    my ( $datetime, $caller, $origcalled, $finalcalled, $seconds ) = $sth->fetchrow_array() 
    ) {
      $row++;
      $col=0;
      $worksheet->write_date_time( $row, $col++, $datetime, $date_format );
      $worksheet->write_string( $row, $col++, $caller );
      $worksheet->write_string( $row, $col++, $origcalled );
      $worksheet->write_string( $row, $col++, $finalcalled );
      $worksheet->write_number( $row, $col++, $seconds );
      $worksheet->write_number( $row, $col++, $seconds/60, $minute_format );
      $totalseconds += $seconds;
    }
  $row++;
  $worksheet->write_number( $row, 4, $totalseconds );
  $worksheet->write_number( $row, 5, $totalseconds/60, $minute_format );
  $sth->finish();

  #--- Outbound Local
  $query = 
    qq{SELECT DATEADD(ss, dateTimeOrigination, 'Jan 1, 1970 00:00:00') as TimeOrigination,
             callingPartyNumber, originalCalledPartyNumber, duration
       FROM         CallDetailRecord
       WHERE     (CallingPartyNumber = '$ext')
         and (len(originalcalledPartyNumber)>5 and len(originalcalledPartyNumber)<=11)
         and dateTimeOrigination >= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtStart')
         and dateTimeOrigination <= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtEnd')} ;

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

  my $worksheet   = $workbook->add_worksheet("$ext to Local");

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

  $worksheet->write_string( $row, $col++, 'Date Time',    $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Caller',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Called',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Minutes',      $colhdr_format );

  $worksheet->set_column( 0, 0, 20, $column_format );
  $worksheet->set_column( 1, 1, 12, $column_format );
  $worksheet->set_column( 2, 2, 12, $column_format );
  $worksheet->set_column( 3, 3, 10, $column_format );
  $worksheet->set_column( 4, 4, 10, $column_format );

  $totalseconds = 0;
  while (
    my ( $datetime, $caller, $called, $seconds ) = $sth->fetchrow_array() 
    ) {
      $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_number( $row, $col++, $seconds/60, $minute_format );
      $totalseconds += $seconds;
    }
  $row++;
  $worksheet->write_number( $row, 3, $totalseconds );
  $worksheet->write_number( $row, 4, $totalseconds/60, $minute_format );
  $sth->finish();

  #--- Outbound long distance
  $query = 
    qq{SELECT DATEADD(ss, dateTimeOrigination, 'Jan 1, 1970 00:00:00') as TimeOrigination,
             callingPartyNumber, originalCalledPartyNumber, duration
       FROM         CallDetailRecord
       WHERE     (CallingPartyNumber = '$ext')
         and originalcalledPartyNumber like '[89]1%'
         and dateTimeOrigination >= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtStart')
         and dateTimeOrigination <= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtEnd')} ;

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

  my $worksheet   = $workbook->add_worksheet("$ext to Long Distance");

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

  $worksheet->write_string( $row, $col++, 'Date Time',    $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Caller',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Called',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Minutes',      $colhdr_format );

  $worksheet->set_column( 0, 0, 20, $column_format );
  $worksheet->set_column( 1, 1, 12, $column_format );
  $worksheet->set_column( 2, 2, 12, $column_format );
  $worksheet->set_column( 3, 3, 10, $column_format );
  $worksheet->set_column( 4, 4, 10, $column_format );

  $totalseconds = 0;
  while (
    my ( $datetime, $caller, $called, $seconds ) = $sth->fetchrow_array() 
    ) {
      $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_number( $row, $col++, $seconds/60, $minute_format );
      $totalseconds += $seconds;
    }
  $row++;
  $worksheet->write_number( $row, 3, $totalseconds );
  $worksheet->write_number( $row, 4, $totalseconds/60, $minute_format );
  $sth->finish();

  #--- Outbound international
  $query = 
    qq{SELECT DATEADD(ss, dateTimeOrigination, 'Jan 1, 1970 00:00:00') as TimeOrigination,
             callingPartyNumber, originalCalledPartyNumber, duration
       FROM         CallDetailRecord
       WHERE     (CallingPartyNumber = '$ext')
         and originalcalledPartyNumber like '[89]011%'
         and dateTimeOrigination >= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtStart')
         and dateTimeOrigination <= datediff( ss, 'Jan 1, 1970 00:00:00', '$dtEnd')} ;

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

  my $worksheet   = $workbook->add_worksheet("$ext to International");

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

  $worksheet->write_string( $row, $col++, 'Date Time',    $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Caller',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Called',       $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Seconds',      $colhdr_format );
  $worksheet->write_string( $row, $col++, 'Minutes',      $colhdr_format );

  $worksheet->set_column( 0, 0, 20, $column_format );
  $worksheet->set_column( 1, 1, 12, $column_format );
  $worksheet->set_column( 2, 2, 12, $column_format );
  $worksheet->set_column( 3, 3, 10, $column_format );
  $worksheet->set_column( 4, 4, 10, $column_format );

  $totalseconds = 0;
  while (
    my ( $datetime, $caller, $called, $seconds ) = $sth->fetchrow_array() 
    ) {
      $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_number( $row, $col++, $seconds/60, $minute_format );
      $totalseconds += $seconds;
    }
  $row++;
  $worksheet->write_number( $row, 3, $totalseconds );
  $worksheet->write_number( $row, 4, $totalseconds/60, $minute_format );
  $sth->finish();


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

  return $returnfilename;

  }

1;