{"id":149,"date":"2018-01-27T23:33:25","date_gmt":"2018-01-28T04:33:25","guid":{"rendered":"https:\/\/www.patellis.us\/?p=149"},"modified":"2026-05-04T13:45:17","modified_gmt":"2026-05-04T17:45:17","slug":"asterisk-to-the-rescue","status":"publish","type":"post","link":"https:\/\/www.patellis.com\/index.php\/2018\/01\/27\/asterisk-to-the-rescue\/","title":{"rendered":"Asterisk to the Rescue!"},"content":{"rendered":"

Problem 1<\/strong>: Lack of attendance at our homeowners meetings due to no one remembering when the meeting is…<\/p>\n

Problem 2:<\/strong>\u00a0\u00a0It was the secretary’s responsiblity to do the calling…<\/p>\n

Bigger Problem:\u00a0I’m the secretary<\/em>.<\/strong>..<\/p>\n

Solution:<\/strong>\u00a0\u00a0Use asterisk to out dial to the members and play a recorded messages.<\/p>\n

Damn, I love technology! \u00a0Created 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\u00a0dial plan, to check for human or machine answered calls, update the database with the call’s outcome. \u00a0Added 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 \u00a0call 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\u00a0tuesday of the month at 12:00 noon. \u00a0Added code to check that only 1 outbound call was active at any one time. \u00a0This is due to the licensing of the voice font (Cepstral) that I use. \u00a0I could not see spending $129 to license 2 outbound calls to be active at one time. \u00a0So what if it it takes hour or so to call the members.<\/p>\n

The dialplan:<\/p>\n

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.
\n<\/span><\/em><\/p>\n

[outcallv2]<\/span>\n exten => s,1,MYSQL(Connect connid localhost dbuser dbpassword DATABASE)<\/span>\n exten => s,n,GotoIf($[\"${connid}\" = \"\"]?error,1)<\/span>\n exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='DIALING' where Id=${RecID})<\/span>\n exten => s,n,NoCDR<\/span>\n exten => s,n,Answer<\/span>\n exten => s,n,PlayBack(en\/silence\/halfsecond)<\/span>\n exten => s,n,AMD<\/span>\n exten => s,n,Goto(Status-${AMDSTATUS})<\/span>\n exten => s,n(Status-HUMAN),Swift(\"Hello, this is an automated reminder from Homeowners Association.\")<\/span>\n exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='HUMAN ANSWERED',LastCalled=now() where Id=${RecID})<\/span>\n 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)<\/span>\n 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)<\/span>\n exten => s,n,Goto(Done)<\/span>\n exten => s,n(Done),MYSQL(Disconnect ${connid})<\/span>\n exten => s,n,Goto(error,2)<\/span>\n exten => s,n(Status-MACHINE),WaitForSilence(1000)<\/span>\n exten => s,n,MYSQL(Query resultid ${connid} Update Members SET Status='ANSWER MACHINE',LastCalled=now() where Id=${RecID})<\/span>\n exten => s,n,Swift(\"Hello, this is an automated reminder from Homeowners Association.\")<\/span>\n 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)<\/span>\n exten => s,n,Swift(\"Thank you\")<\/span>\n exten => s,n,Goto(Done)<\/span>\n exten => s,n(Status-NOTSURE),MYSQL(Query resultid ${connid} Update Members SET Status='HUNG UP',LastCalled=now() where Id=${RecID})<\/span>\n exten => s,n,Goto(Done)<\/span>\n exten => s,n(Status-HANGUP),MYSQL(Query resultid ${connid} Update Members SET Status='NOT SURE',LastCalled=now() where Id=${RecID})<\/span>\n exten => s,n,Goto(Done)<\/span>\n exten => 1,1,Goto(s,Status-HUMAN)<\/span>\n exten => 2,1,Swift(\"You will no longer receive reminders.\")<\/span>\n exten => 2,n,Swift(\"Thank you\",500,0)<\/span>\n exten => 2,n,MYSQL(Query resultid ${connid} Update Members SET CallReminder='N' where Id=${RecID})<\/span>\n exten => 2,n(Done),MYSQL(Clear ${resultid})<\/span>\n exten => 2,n,Goto(s,Done)<\/span>\n exten => 3,1,Swift(\"Great! See you there.\")<\/span>\n exten => 3,n,Goto(s,Done)<\/span>\n exten => error,1,NoOp(Connection error - Ack!)<\/span>\n exten => error,2,Hangup<\/span><\/pre>\n
Example of a call file:<\/strong><\/p>\n
Channel:\u00a0SIP\/3215551212@mysipvendor<\/a><\/span>
\nMaxRetries: 2<\/span>
\nRetryTime: 3<\/span>
\nWaitTime: 60<\/span>
\nContext: outcallv2<\/span>
\nExtension: s<\/span>
\nPriority: 1<\/span>
\nSetvar: FirstName=Dan<\/span>
\nSetvar: RecId=1310518<\/span><\/p>\n
The “program” that runs every Tuesday:\u00a0<\/strong><\/p>\n
\n&lt;?php\n\n$dbHost='localhost';\n$dbDatabase='DBNAME';\n$dbUser='dbuser';\n$dbPassword='dbpassword';\n\nfunction ErrPrint($str)\n{\n\tprint &quot;$str\\n&quot;;\n}\n\nfunction dbConnect()\n{\n\tglobal $dbHost,$dbUser,$dbPassword,$dbDatabase,$dbConn,$debuglevel;\n\tif (!isset($dbConn))\n\t{\n\t\t$dbConn=mysql_connect($dbHost,$dbUser,$dbPassword);\n\t\tif ($debuglevel&gt;2)\n\t\t{\n\t\t\tErrPrint(&quot;Creating Database Connection&quot;);\n\t\t\texit();\n\t\t}\n\t\tmysql_selectdb($dbDatabase,$dbConn);\n\t\tif (mysql_errno()&lt;&gt;0)\n\t\t{\n\t\t\ttrigger_error(mysql_error());\n\t\t}\n\t}\n}\n\nfunction dbDisconnect()\n{\n\tglobal $dbConn,$debuglevel;\n\tif (isset($dbConn))\n\t{\n\t\tif ($debuglevel&gt;2)\n\t\t{\n\t\t\tErrPrint(&quot;Destroying Database Connection&quot;);\n\t\t}\n\t\tmysql_close($dbConn);\n\t\tunset($GLOBALS['dbConn']);\n\t}\n}\n\nfunction dbEscape($str)\n{\n\tglobal $dbConn;\n\tif (isset($dbConn))\n\t{\n\t\t$ret=mysql_escape_string($str);\n\t\treturn( $ret );\n\t}\n\telse\n\t{\n\t\treturn( '' );\n\t}\n}\n\nfunction dbEscapeQ($str)\n{\n\treturn( &quot;'&quot;.dbEscape($str).&quot;'&quot; );\n}\n\nfunction dbQuery($str)\n{\n\tglobal $dbConn,$debuglevel;\n\n\tif (isset($dbConn))\n\t{\n\t\tif ($debuglevel&gt;2)\n\t\t{\n\t\t\tErrPrint(&quot;Query: $str&quot;);\n\t\t}\n\t\t$ret=mysql_query($str,$dbConn);\n\t\tif (mysql_errno()&lt;&gt;0)\n\t\t{\n\t\t\ttrigger_error(mysql_error());\n\t\t}\n\t\treturn( $ret );\n\t}\n\telse\n\t{\n\t\treturn( null );\n\t}\n}\n\nfunction dbFinish($str)\n{\n\tglobal $dbConn;\n\tif (isset($dbConn))\n\t{\n\t\tmysql_free_result($str);\n\t}\n}\n\nfunction dbRows($str)\n{\n\tglobal $dbConn,$debuglevel;\n\tif (isset($dbConn))\n\t{\n\t\t$cnt=mysql_num_rows($str);\n\t\tif ($debuglevel&gt;2)\n\t\t{\n\t\t\tErrPrint(&quot;Row Count: $cnt&quot;);\n\t\t}\n\t\treturn($cnt);\n\t}\n\telse\n\t{\n\t\treturn(0);\n\t}\n}\n\n\/\/only run on the 2nd Tuesday of the month\ndate_default_timezone_set(&quot;America\/New_York&quot;);\n$Today=date(&quot;d&quot;,time());\n$MonthYear=date(&quot;Y-m&quot;,time());\n$SecondTues = strftime(&quot;%d&quot;,strtotime(&quot;second tuesday of $MonthYear&quot;));\n\nif ($Today !=$SecondTues)\n{\n\tprintf(&quot;Today is %s, which is not the second Tuesday of the month\\n&quot;,$Today);\n\texit();\n}\n\ndbConnect();\n$SqlCmd=&quot;SELECT Id,FirstName,LastName,PhoneNumber from Members where CallReminder='Y' order by LastName asc&quot;;\n$Result = dbQuery($SqlCmd);\n$Sequence=0;\nif (dbRows($Result)&gt;0)\n{\n\twhile ($RowData=mysql_fetch_assoc($Result))\n\t{\n\t\t$FileName=sprintf(&quot;\/tmp\/ToCall\/%s_%s&quot;,$RowData['LastName'],$RowData['PhoneNumber']);\n\t\t$FileList[$Sequence]=$FileName;\n\t\t$FileOut[$Sequence]=sprintf(&quot;\/var\/spool\/asterisk\/outgoing\/%s_%s&quot;,$RowData['LastName'],$RowData['PhoneNumber']);\n\t\t$CurrentCalling[$Sequence]=sprintf(&quot;%s %s&quot;,$RowData['FirstName'],$RowData['LastName']);\n\t\t$FP=fopen($FileName,&quot;w&quot;);\n\t\tfprintf($FP,&quot;Channel:\u00a0SIP\/%s@MySipProvider\\n&quot;;,$RowData['PhoneNumber']);\n\t\tfprintf($FP,&quot;CallerID: MyCallerId &lt;3215551212&gt;\\n&quot;);\n\t\tfprintf($FP,&quot;MaxRetries: 1\\n&quot;);\n\t\tfprintf($FP,&quot;RetryTime: 120\\n&quot;);\n\t\tfprintf($FP,&quot;WaitTime: 30\\n&quot;);\n\t\tfprintf($FP,&quot;Context: outcallv2\\n&quot;);\n\t\tfprintf($FP,&quot;Extension: s\\n&quot;);\n\t\tfprintf($FP,&quot;Priority: 1\\n&quot;);\n\t\tfprintf($FP,&quot;Setvar: FirstName=%s\\n&quot;,$RowData['FirstName']);\n\t\tfprintf($FP,&quot;Setvar: RecId=%d\\n&quot;,$RowData['Id']);\n\t\t$Sequence++;\n\t\tfclose($FP);\n\t}\n\tdbFinish($Result);\n }\n\ndbDisconnect();\nprintf(&quot;Have %d calls to make.\\n&quot;,$Sequence);\n\nfor ($count=0;$count&lt;$Sequence;$count++)\n{\n\t$Cmd=sprintf(&quot;\/bin\/mv %s \/var\/spool\/asterisk\/outgoing\/.&quot;,$FileList[$count]);\n\tprintf(&quot;%2d - Calling %s&quot;,$count+1,$CurrentCalling[$count]);\n\tSystem($Cmd);\n\twhile (file_exists($FileOut[$count]))\n\t{\n\t\tsleep(10);\n\t\tprint(&quot;.&quot;);\n\t}\n\tprint(&quot;\\n&quot;);\n}\nprint(&quot;All Done!\\n&quot;);\n?&gt;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Problem 1: Lack of attendance at our homeowners meetings due to no one remembering when the meeting is… Problem 2:\u00a0\u00a0It was the secretary’s responsiblity to do the calling… Bigger Problem:\u00a0I’m the secretary… Solution:\u00a0\u00a0Use asterisk to out dial to the members and play a recorded messages. Damn, I love technology! \u00a0Created a mysql database of all … Continue reading “Asterisk to the Rescue!”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-149","post","type-post","status-publish","format-standard","hentry","category-code"],"_links":{"self":[{"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/posts\/149","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/comments?post=149"}],"version-history":[{"count":10,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/posts\/149\/revisions"}],"predecessor-version":[{"id":336,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/posts\/149\/revisions\/336"}],"wp:attachment":[{"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/media?parent=149"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/categories?post=149"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.patellis.com\/index.php\/wp-json\/wp\/v2\/tags?post=149"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}