#!/usr/bin/perl -w

# 2006/12/29
# Ray Burkholder
# ray@oneunified.net


use strict;
use DBI;
use Spreadsheet::WriteExcel;
use Mail::Sender;

my %maildetails = (
  to => 'root@localhost',
  from => 'root@localhost',
  server => '127.0.0.1'
  );

my $sDbConn=q{dbi:Pg:dbname=oneunified};
my $sDbUser = q{oneunified};
my $sDbPass = q{oneunified};

my $hDB = DBI->connect( $sDbConn, $sDbUser, $sDbPass, { RaiseError => 1, AutoCommit => 1 } );

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

$sth = $hDB->prepare(
  q{ select id, device, 
     to_timestamp( setuptime, 'YYYY-MM-DD HH:MI:SS' ), 
     address1, address2, 
     to_timestamp( disconnecttime - connecttime, 'HH:MI:SS' ) as duration 
     from cdr
     where address1 similar to '(81|91|8011|9011)%' or address2 similar to '(81|91|8011|9011)%'
     order by device, setuptime; } );
$rv = $sth->execute();

my $workbook = Spreadsheet::WriteExcel->new("gatewaycdr.xls");
die "Problems creating new Excel file: $!" unless defined $workbook;
my $worksheet   = $workbook->add_worksheet();

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

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

$worksheet->write_string( $row, $col++, 'ID', $colhdr_format );
$worksheet->write_string( $row, $col++, 'Gateway', $colhdr_format );
$worksheet->write_string( $row, $col++, 'Connected', $colhdr_format );
$worksheet->write_string( $row, $col++, 'Caller', $colhdr_format );
$worksheet->write_string( $row, $col++, 'Called', $colhdr_format );
$worksheet->write_string( $row, $col++, 'Duration', $colhdr_format );

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

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

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');
 
while (
  my ( $id, $device, $setuptime, $address1, $address2, $duration ) = $sth->fetchrow_array() 
  ) {
    if ( defined( $address1 ) and defined( $address2 ) ) {
      $row++;
      $col=0;
      $worksheet->write_number( $row, $col++, $id );
      $worksheet->write_string( $row, $col++, $device );
      $setuptime =~ s/ /T/;
      $setuptime =~ s/-04$//;
      $worksheet->write_date_time( $row, $col++, $setuptime, $date_format );
      $worksheet->write_string( $row, $col++, $address2 );
      $worksheet->write_string( $row, $col++, $address1 );
      $duration =~ s/0001-01-01 /T/;
      $duration =~ s/ BC//;
      $worksheet->write_date_time( $row, $col++, $duration, $time_format );
#      print "$id, $device, $setuptime, $address1, $address2, $duration\n";
    }
  }

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

my $mailsender = new Mail::Sender( { smtp => $maildetails{server}, from => $maildetails{from} } );
if ( defined( $mailsender ) ) {
$mailsender -> MailFile( {
  to => $maildetails{to},
  subject => 'Call Detail Record Spreadsheet Attached',
  msg => "Attached is cdr spreadsheet with current records from database.\n\n" ,
#  debug => 'senddbg.txt',
#  debug_level => 3,
  file => 'gatewaycdr.xls' } );
}
else {
  print "no mailsender was returned\n";
}

1;