#!/usr/bin/perl
# Phone Logger for KX-TD1232, parses input from the serial port and inserts into a 
# mysql database. Munged together from the call logger code made by Glenn Powers 
# for the XRMS project.
# Andrew Keynes <ak@saint.net.au>
#
# Original Purpose
# -----------------------------------------------------------------------------------
# Phone Logger for XRMS -  interfaces XRMS with the Call Detail Records from a
# Panasonic KX-TD816 Digital Super Hybrid Phone System. It will probably also
# work with the KX-TD1232.
# -----------------------------------------------------------------------------------
# Original script can be found in the /plugins/cti/ directory of the xrms project.

# copyright 2004 Glenn Powers <glenn@net127.com>
# Licensed Under the Open Software License v. 2.0

# default area code (prepended to 8-digit numbers)
my $area_code = "08" ;
my $dial_prefix = "1414" ;

use DBI;

# open the serial port
open(SERIAL,"</dev/ttyS1");

while($line=<SERIAL>) {

# open the log file
open(LOG,">>/var/log/phone.log");

# print line to log
print LOG "$line";

#close the log file
close(LOG);

# remove trailing newline
chop $line;

# skip headers
if ($line =~ /^\-+$/) {
    next;
};

# Columns as follows:
#	Date (DD/MM/YY)
#	Time (HH:mm:AM/PM)
#	Ext (918, 964 etc) - Internal Phone Extension
#	CO (Call Out line?) -
#	Dial Number (<I> indicates an incoming call,
#	All outgoing calls have the 1414 prefix)
#	ANS (Time taken to answer the call)
#	Duration (Duration of the call)
#	Cost:$ (If system setup for phone accounting,
#	the cost of the call)
#	Acc (6 digit accounting code?) Accounting Code
#	CD (TR = Call was tranferred,
#	FW = Extension number was setup to fowrard to
#	an external number, RM = Call accessed the
#	remote maintenance modem, NA = No Answer?)
if ($line =~ /^  Date     Time   Ext CO     Dial Number       ANS  Duration Cost\:\$     Acc  CD/) {
    next
};

if ($line =~ /^AT/) {
    next
};

if ($line =~ /^$/) {
    next
};

# split up and re-arrange the input line
#	Group 1, 2 & 3 - Rearrange to form a standard YYYY-MM-DD
#	Group 4 - HH
#	Group 5 - MM
#	Group 6 - AM/PM
#	Group 7 - Extension
#	Group 8 - CO
#	Group 9 - Phone Number
#	Group 10 & 11 - Rearrange to form HHMMSS suitable for a mysql TIME column
#	Group 12, 13 & 14 - Rearrange to form HHMMSS suitable for a mysql TIME column
#	Group 15 - Call cost
#	Group 16 - Accounting Code
#	Group 17 - Call Code?
$line=~s/^(\d\d)\/(\d\d)\/(\d\d) (\d\d)\:(\d\d)(\w\w)  (...) (\d\d)
	(....................) (.)(.)(..)  (..).(..).(..) (........) (......) (..)
	/20$3-$2-$1,$4,$5,$6,$7,$8,$9,$10$12,$13$14$15,$16,$17,$18/;

($date,$hh,$mm,$apm,$ext,$co,$dial,$answer,$duration,$cost,$acc,$cd)=split(",",$line);

# Convert to 24-hr time
if ($apm =~/PM/) {
    if ($hh < 12) {
        $hh=$hh+12;
    };
};

$time = "$hh:$mm";

# determine activity_type_id
if ($dial =~ /(\<I\>)/) {
    $dial =~ s/(\<I\>)//g ;
    $activity_type_id = 2; # "Call From"
} elsif ($dial =~ /^($dial_prefix)(................)$/) {
    $dial =~ s/^($dial_prefix)(................)$/$2/g ;
    $activity_type_id = 1; # "Call To"
} else {
    $activity_type_id = 1; # "Call To"
};

# remove spaces from dialed number, answer time, cost, account code and cd
$dial =~ s/ +//g ;
$answer =~ s/ +//g ;
$cost =~ s/ +//g ;
$acc =~ s/ +//g ;
$cd =~ s/ +//g ;

# format answer time as HHMMSS
if($answer =~ /(\d\d\d)/) {
    $answer = "000" . $answer ;
}

# add the area code to phone number if required
if($dial =~ /^(\d{8})$/) {
    $dial = $area_code . $dial ;
}

# make insert statment
$insert = "INSERT INTO `phone_system_log` ( 
	`CallID`, 
	`Date`, 
	`Extension`, 
	`CO`, 
	`Phone_Number`, 
	`Answer_Time`, 
	`Duration`, 
	`Cost`, 
	`Account_Code`, 
	`CD`, 
	`Activity_Type` ) VALUES ( 
	'', 
	'" . $date_time . "', 
	'" . $ext . "', 
	'" . $co . "', 
	'" . $dial . "', 
	'" . $answer . "', 
	'" . $duration . "', 
	'" . $cost . "', 
	'" . $acc . "', 
	'" . $cd . "', 
	'" . $activity_type_id . "' );" ;

# open database connection
$dbh = DBI->connect('DBI:mysql:database:127.0.0.1', 'username','password') 
	or die $DBI::errstr;

# prepare the insert statement
$cursor = $dbh->prepare($insert);

# do the insert
$cursor->execute;

# close the database connection
$dbh->disconnect;

# close the loop
};
close(SERIAL);