#!/usr/bin/perl 


use strict;
use DBI;
use DBI::DBD;
use Date::Manip;

# Open log file
open(LOG, "+>>/var/log/talkserver_term_log") || die("Unable to open log file");

my @tm = localtime(time);
my $min = $tm[1];
my $hour = $tm[2];
my $day = $tm[3];
my $month = $tm[4]+1;
my $year = $tm[5]+1900;

print LOG "\n# LOG START\n";
print LOG "# Date: $month/$day/$year\n";
print LOG "# Time: $hour:$min\n";

# Get database settings
my $dbhost;
my $dbuser;
my $dbpwd;
open(TSCONF, "</etc/talkserver.conf") || die("Unable to open talkserver conf file");
while (<TSCONF>)
    {
    chomp;
    my $curLine = $_;    
    if (substr($curLine,0,1) ne ';')
	{
	if ($curLine =~ m/DatabaseName/)
	    {	    
	    my $sub1 = substr($curLine, index($curLine, 'HOST=', 0)+5);
	    my $sub2 = substr($sub1, 0, index($sub1, ')', 0));
	    $dbhost = $sub2;
	    }
	if ($curLine =~ m/DatabaseUser/)
	    {	    
	    my $pos = index($curLine, '=', 0);
	    $dbuser = substr($curLine, $pos+1, length($curLine)-$pos-1);
	    }
	if ($curLine =~ m/DatabasePassword/)
	    {	    
	    my $pos = index($curLine, '=', 0);
	    $dbpwd = substr($curLine, $pos+1, length($curLine)-$pos-1);
	    }
	}
    }
close(TSCONF);

# Connect
print LOG "# Connection: host=$dbhost, user=$dbuser, pwd=$dbpwd\n";
my $dbh = DBI->connect("dbi:Oracle:host=$dbhost;sid=ORCL", $dbuser, $dbpwd);
unless ($dbh){
    print LOG "# ERROR occured while connecting to the database....$DBI::errstr\n";
    exit;
    }

# Is there anything for us to do?
if (GetNumRecords('tbl_conferences_term') + GetNumRecords('tbl_participants_term') == 0){ 
    print LOG "# No records\n";
    exit; 
    }

my @arrColumns;

# Terminated conferences
@arrColumns = (
	'Name',
	'MCUID',
	'AccountID',
	'AccountUser',
	'PhoneNumber',
	'Identifier',
	'ConferenceID',
	'ConferenceGroupID',
	'Status',
	'Chair_Password',
	'Chair_Email',
	'Part_Password',
	'MaxParticipants',
	'Broadcast',
	'RecordFile',
	'Start_Mode',
	"TO_CHAR(StartTime, 'DD-MM-YYYY HH24:MI:SS')",
	'End_Mode',
	"TO_CHAR(EndTime, 'DD-MM-YYYY HH24:MI:SS')",
	'Recurring_Length',
	'Recurring_Freq',
	'Recurring_Weekends',
	'ServerID',
	'EmailMessage',
	'Status_TerminatedByError',
	'Panel_Password',
	'Description');
PrintTable("tbl_conferences_term", "ConferenceID", 6, @arrColumns);

# Left participants
@arrColumns = (
    'PIndex',
    'Name',
    'PhoneNumber',
    "TO_CHAR(Join_Time, 'DD-MM-YYYY HH24:MI:SS')",
    "TO_CHAR(Left_Time, 'DD-MM-YYYY HH24:MI:SS')",
    'CallID',
    'Status',
    'ConferenceID',
    'UserGuid',
    'Incoming',
    'GatewayAddress');
PrintTable("tbl_participants_term", "CallID", 5,  @arrColumns);

# Cleanup
close(LOG);
$dbh->disconnect();
exit;

#-----------------------------------------------------------------------------

sub CommaSepCols
{
    my (@arrColumns) = @_;
    my $strColumns;
    my $curCol;
    foreach $curCol (@arrColumns)
	{
        if ($strColumns eq ''){ $strColumns .= $curCol; }
	else{ $strColumns .= ", $curCol";	}
        }
    return $strColumns;
}

#-----------------------------------------------------------------------------

sub PrintCols
{
    my (@arrColumns) = @_;
    my $i=0;
    foreach my $curCol (@arrColumns)
	{
        print LOG "$i->$curCol\n"; 
	$i++;
        }
    print LOG "\n";
}

#-----------------------------------------------------------------------------

sub PrintTable
{
    my ($strTable, $strIdName, $nIdIndex, @arrColumns) = @_;
    
    # Heading
    print LOG "TABLENAME: $strTable\n\n";
    
    # Columns
    print LOG "COLUMNNAMES:\n";
    PrintCols(@arrColumns);
    
    # Rows
    print LOG "ROWS:\n";    
    my $strColumns = CommaSepCols(@arrColumns);
    my $sql = "SELECT $strColumns FROM $strTable";
    my $cursor = $dbh->prepare($sql);
    my $rc = $cursor->execute;
    while (my @row = $cursor->fetchrow_array())
	{
	my $i = 0;
	foreach my $curCol (@arrColumns)
	    {
	    print LOG "$i: $row[$i]\n";
	    $i++;
	    }
	print LOG "\n";
	
	# remove from table
	DeleteRow($strTable, $strIdName, $row[$nIdIndex]);
	}
    $cursor->finish();
}

#-----------------------------------------------------------------------------

sub DeleteRow
{
    my ($strTable, $idName, $id) = @_;
    my $sql = "DELETE FROM $strTable WHERE $idName='$id'";
    my $cursor = $dbh->prepare($sql);
    my $rc = $cursor->execute;
}

#-----------------------------------------------------------------------------

sub GetNumRecords
{
    my ($strTable) = @_;
    my $sql = "SELECT count(*) from $strTable";
    my $cursor = $dbh->prepare($sql);
    my $rc = $cursor->execute;
    my @row = $cursor->fetchrow_array();
    my $count = $row[0];
    return $count;
}

#-----------------------------------------------------------------------------
