[asterisk-users] Dailplan code for holiday detection?

[Tilghman Lesher]

On Tuesday 23 December 2008 04:08:00 pm Daniel Hazelbaker wrote:
> We chose to use a mySQL database to store the holiday information.
> When a call is answered we query the database to see if there is a
> "holiday" greeting recorded, if so we play the indicated greeting,
> otherwise play the "default" menu greeting. (We do our dialplans in AEL)
>
>
> context checkHoliday {
>          s =>
>            {
>             	begin:
>
>                  MYSQL(Connect temp communicator username password
> asterisk);
>                  MYSQL(Query resultid ${temp} SELECT greeting FROM
> menuGreetings WHERE startTime<=FROM_UNIXTIME(${EPOCH}) AND
> endTime>=FROM_UNIXTIME(${EPOCH}) LIMIT 1);
>                  MYSQL(Fetch foundRow ${resultid} sqlGreeting);
>                  MYSQL(Clear ${resultid});
>                  MYSQL(Disconnect ${temp});
>
>                  if ("${foundRow}"=="1")
>                  {
>                          Background(custom/mainMenu/${sqlGreeting});
>                          goto mainMenu,s,begin;
>                  }
>                  else
>                  {
>                          goto checkTime,s,begin;
>                  }
>          }
>          includes
>            {
>             	mainMenu;
>                  tempGreeting;
>                  voicemail;
>                  publicExt;
>          }
> };
>
>
> The 'checkTime' context simply checks if we are open or closed and
> plays the appropriate greeting (if no holiday greeting is found).

This dialplan is illustrative of the particular problem of the MYSQL command
in that no cleanup is performed if the dialplan terminates abnormally.  If a
device hangup occurs between the Connect and Disconnect, or worse, between
the Query and the Clear, then extra resources will be consumed until a restart
is performed.  To avoid this problem, you should ensure that you always clear
your query resources and disconnect your handles in the "h" extension.

Or use func_odbc, which performs this sort of cleanup for you.

-- 
Tilghman