#!/usr/bin/perl

#
# this reads caller ID info (as detected by my modem) from STDIN and
# writes corresponding call records to a database.
#
# configuring the modem and routing its output to my STDIN is
# outside the scope of this script.
#
# blame: shardy@@differentchairs.com
#

use DBI;

my $calldate;
my $calltime;
my $callnumber;
my $callname;

#
# assume we've got a mysql database with a phonecall table like this:
#
#	CREATE TABLE  `mysql`.`phonecall` (
#	  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
#	  `calltime` datetime NOT NULL,
#	  `number` char(11) DEFAULT '0000000000',
#	  `name` char(30) DEFAULT '',
#	  PRIMARY KEY (`id`)
#	);
#

my $dbhost 		= "localhost";
my $dbport 		= 3306;
my $database 	= "mysql";
my $tablename	= "phonecall";

$| = 1; # (autoflush)

# assume anonymous INSERTs work

my $dbh = DBI->connect("dbi:mysql:database=$database;host=$dbhost;port=$dbport");

clearRec();
#
# my modem spits out callerID info that looks as follows:
#
#  DATE=0720
#  TIME=1855
#  NMBR=2068424162
#  NAME=LBRY
#
# (NAME could be missing.) Turn this into sql that adds corresponding record:
#
#   insert into mysql.phonecall (calltime,number,name) values('2009-07-20 18:55:00','2068424162','LBRY');
#
while (<>) {
	print;
	chomp;

	if (/^DATE=(..)(..).*/) {
		writeRec();
		$calldate = (1900+(localtime)[5]) . "-$1-$2";
	}
	elsif (/^TIME=(..)(..).*/) {
		$calltime = "$1:$2:00";
	}
	elsif (/^NAME=(.*[^\s])[\s]*/) {
		$callname = $1;
		writeRec();
	}
	elsif (/^NMBR=(.*[^\s])[\s]*/) {
		if (0!=length($number)) {
			writeRec();
		}
		else {
			$callnumber = $1;
		}
	}
}

sub clearRec
{
	$calldate="";
	$calltime="";
	$callnumber="";
	$callname="";
}

sub writeRec
{
	my $values = "\'$calldate $calltime\'";
	my $fields = "calltime";

	if (0!=length($calldate )) {

		if (0!=length($callnumber)) {
			$values = $values . ",\'$callnumber\'";
			$fields = $fields . ",number";
		}
		if (0!=length($callname)) {
			$values = $values . ",\'$callname\'";
			$fields = $fields . ",name";
		}
		my $sql = "insert into $database.$tablename ($fields) values($values);";

		print "$sql\n";

		my $InsertRecord = $dbh->do($sql);
		if (!$InsertRecord)
		{
			print("FAIL: $DBI::errstr");
		}
		else
		{
			# security issue...
			system("cmd.exe", "/c","onIncomingCall.cmd","$calldate","$calltime","$callnumber","$callname");
		}
	}
	clearRec();
}