January 2018 – Patellis.com

Problem 1: Lack of attendance at our homeowners meetings due to no one remembering when the meeting is…

Problem 2: It was the secretary’s responsiblity to do the calling…

Bigger Problem: I’m the secretary...

Solution: Use asterisk to out dial to the members and play a recorded messages.

Damn, I love technology! Created a mysql database of all the members, containing first/last name, phone number, status, last time called and an opt-out flag. Next, threw together am outbound dial plan, to check for human or machine answered calls, update the database with the call’s outcome. Added menu to allow user to replay message, opt-out of receiving future messages and allow user to said (by pressing 3) they might attend the meeting. Finally, wrote a quick php program to create call files and to feed them to asterisk via /var/spool/asterisk/outgoing. The program is called by a crontab entry, but only does it’s thing second tuesday of the month at 12:00 noon. Added code to check that only 1 outbound call was active at any one time. This is due to the licensing of the voice font (Cepstral) that I use. I could not see spending $129 to license 2 outbound calls to be active at one time. So what if it it takes hour or so to call the members.

The dialplan:

I know that I should make a change to allow for a gosub for the main “text” of the message, since the HUMAN-ANSWERED and MACHINE-ANSWERED message is 99% the same, the only difference is that the MACHINE-ANSWERED message skips the menu options.

[outcallv2]
 exten => s,1,MYSQL(Connect connid localhost dbuser dbpassword DATABASE)
 exten => s,n,GotoIf($["${connid}" = ""]?error,1)
 exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='DIALING' where Id=${RecID})
 exten => s,n,NoCDR
 exten => s,n,Answer
 exten => s,n,PlayBack(en/silence/halfsecond)
 exten => s,n,AMD
 exten => s,n,Goto(Status-${AMDSTATUS})
 exten => s,n(Status-HUMAN),Swift("Hello, this is an automated reminder from Homeowners Association.")
 exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='HUMAN ANSWERED',LastCalled=now() where Id=${RecID})
 exten => s,n,Swift("This reminder is intended for ${FirstName}. The meeting is tonight at 7 P.M. at the Beer Garden on Lake Drive.",500,0)
 exten => s,n,Swift("Press 1 to listen again. Press 2 to be removed from future calls. Press 3 if you think you will be attending",4000,1)
 exten => s,n,Goto(Done)
 exten => s,n(Done),MYSQL(Disconnect ${connid})
 exten => s,n,Goto(error,2)
 exten => s,n(Status-MACHINE),WaitForSilence(1000)
 exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='ANSWER MACHINE',LastCalled=now() where Id=${RecID})
 exten => s,n,Swift("Hello, this is an automated reminder from Homeowners Association.")
 exten => s,n,Swift("This reminder is intended for ${FirstName}. The meeting is tonight at 7 P.M. at the Beer Garden on Lake Drive.",400,0)
 exten => s,n,Swift("Thank you")
 exten => s,n,Goto(Done)
 exten => s,n(Status-NOTSURE),MYSQL(Query resultid ${connid} Update Members SET Status='HUNG UP',LastCalled=now() where Id=${RecID})
 exten => s,n,Goto(Done)
 exten => s,n(Status-HANGUP),MYSQL(Query resultid ${connid} Update Members SET Status='NOT SURE',LastCalled=now() where Id=${RecID})
 exten => s,n,Goto(Done)
 exten => 1,1,Goto(s,Status-HUMAN)
 exten => 2,1,Swift("You will no longer receive reminders.")
 exten => 2,n,Swift("Thank you",500,0)
 exten => 2,n,MYSQL(Query resultid ${connid} Update Members SET CallReminder='N' where Id=${RecID})
 exten => 2,n(Done),MYSQL(Clear ${resultid})
 exten => 2,n,Goto(s,Done)
 exten => 3,1,Swift("Great! See you there.")
 exten => 3,n,Goto(s,Done)
 exten => error,1,NoOp(Connection error - Ack!)
 exten => error,2,Hangup

Example of a call file:

Channel: SIP/3215551212@mysipvendor
MaxRetries: 2
RetryTime: 3
WaitTime: 60
Context: outcallv2
Extension: s
Priority: 1
Setvar: FirstName=Dan
Setvar: RecId=1310518

The “program” that runs every Tuesday:

<?php

$dbHost='localhost';
$dbDatabase='DBNAME';
$dbUser='dbuser';
$dbPassword='dbpassword';

function ErrPrint($str)
{
	print "$str\n";
}

function dbConnect()
{
	global $dbHost,$dbUser,$dbPassword,$dbDatabase,$dbConn,$debuglevel;
	if (!isset($dbConn))
	{
		$dbConn=mysql_connect($dbHost,$dbUser,$dbPassword);
		if ($debuglevel>2)
		{
			ErrPrint("Creating Database Connection");
			exit();
		}
		mysql_selectdb($dbDatabase,$dbConn);
		if (mysql_errno()<>0)
		{
			trigger_error(mysql_error());
		}
	}
}

function dbDisconnect()
{
	global $dbConn,$debuglevel;
	if (isset($dbConn))
	{
		if ($debuglevel>2)
		{
			ErrPrint("Destroying Database Connection");
		}
		mysql_close($dbConn);
		unset($GLOBALS['dbConn']);
	}
}

function dbEscape($str)
{
	global $dbConn;
	if (isset($dbConn))
	{
		$ret=mysql_escape_string($str);
		return( $ret );
	}
	else
	{
		return( '' );
	}
}

function dbEscapeQ($str)
{
	return( "'".dbEscape($str)."'" );
}

function dbQuery($str)
{
	global $dbConn,$debuglevel;

	if (isset($dbConn))
	{
		if ($debuglevel>2)
		{
			ErrPrint("Query: $str");
		}
		$ret=mysql_query($str,$dbConn);
		if (mysql_errno()<>0)
		{
			trigger_error(mysql_error());
		}
		return( $ret );
	}
	else
	{
		return( null );
	}
}

function dbFinish($str)
{
	global $dbConn;
	if (isset($dbConn))
	{
		mysql_free_result($str);
	}
}

function dbRows($str)
{
	global $dbConn,$debuglevel;
	if (isset($dbConn))
	{
		$cnt=mysql_num_rows($str);
		if ($debuglevel>2)
		{
			ErrPrint("Row Count: $cnt");
		}
		return($cnt);
	}
	else
	{
		return(0);
	}
}

//only run on the 2nd Tuesday of the month
date_default_timezone_set("America/New_York");
$Today=date("d",time());
$MonthYear=date("Y-m",time());
$SecondTues = strftime("%d",strtotime("second tuesday of $MonthYear"));

if ($Today !=$SecondTues)
{
	printf("Today is %s, which is not the second Tuesday of the month\n",$Today);
	exit();
}

dbConnect();
$SqlCmd="SELECT Id,FirstName,LastName,PhoneNumber from Members where CallReminder='Y' order by LastName asc";
$Result = dbQuery($SqlCmd);
$Sequence=0;
if (dbRows($Result)>0)
{
	while ($RowData=mysql_fetch_assoc($Result))
	{
		$FileName=sprintf("/tmp/ToCall/%s_%s",$RowData['LastName'],$RowData['PhoneNumber']);
		$FileList[$Sequence]=$FileName;
		$FileOut[$Sequence]=sprintf("/var/spool/asterisk/outgoing/%s_%s",$RowData['LastName'],$RowData['PhoneNumber']);
		$CurrentCalling[$Sequence]=sprintf("%s %s",$RowData['FirstName'],$RowData['LastName']);
		$FP=fopen($FileName,"w");
		fprintf($FP,"Channel: SIP/%s@MySipProvider\n";,$RowData['PhoneNumber']);
		fprintf($FP,"CallerID: MyCallerId <3215551212>\n");
		fprintf($FP,"MaxRetries: 1\n");
		fprintf($FP,"RetryTime: 120\n");
		fprintf($FP,"WaitTime: 30\n");
		fprintf($FP,"Context: outcallv2\n");
		fprintf($FP,"Extension: s\n");
		fprintf($FP,"Priority: 1\n");
		fprintf($FP,"Setvar: FirstName=%s\n",$RowData['FirstName']);
		fprintf($FP,"Setvar: RecId=%d\n",$RowData['Id']);
		$Sequence++;
		fclose($FP);
	}
	dbFinish($Result);
 }
 
dbDisconnect();
printf("Have %d calls to make.\n",$Sequence);

for ($count=0;$count<$Sequence;$count++)
{
	$Cmd=sprintf("/bin/mv %s /var/spool/asterisk/outgoing/.",$FileList[$count]);
	printf("%2d - Calling %s",$count+1,$CurrentCalling[$count]);
	System($Cmd);
	while (file_exists($FileOut[$count]))
	{
		sleep(10);
		print(".");
	}
	print("\n");
}
print("All Done!\n");
?>

Month: January 2018

Asterisk to the Rescue!