vmail-2-odbc.php

#!/usr/bin/php -q
<?
$my_user = "convert";           # MySQL user (with min. INSERT privlege on 'voicemessages' table)
$my_passwd = "convert";         # MySQL user password
$my_host = "localhost";         # MySQL hostname
$my_database = "asterisk";      # MySQL database name
$my_table = "voicemessages";    # MySQL table name (normally 'voicemessages')
$msg_format = "WAV";            # recording format to use (WAV|wav|gsm)
$dir_prefix = "/var/spool/asterisk/voicemail/"; # root of current file-based voicemail spool 
$ignore_default = TRUE;         # TRUE ignores 'default' context, FALSE will insert vboxes from 'default' context

echo "Starting....\n";
$DB = mysql_connect($my_host, $my_user, $my_passwd) or die("Could not connect: " . mysql_error());
mysql_select_db($my_database) or die("Could not select database: " . mysql_error());

if(!is_dir($dir_prefix)) die("$dir_prefix is not a directory.\n");
$DirH = opendir($dir_prefix) or die ("opendir failed on $dir_prefix.\n");
while(FALSE !== ($DirEnt = readdir($DirH)) ) {  # each of these directories is a voicemailbox context
        if($DirEnt == "." || $DirEnt == "..") continue;
        if($DirEnt == "default" && $ignore_default) continue;
        echo "Processing context directory $dir_prefix$DirEnt\n";
        $mailboxcontext=$DirEnt;
        $CtxH = opendir($dir_prefix.$DirEnt) or die ("opendir failed on $dir_prefix$DirEnt\n");
        while(FALSE !== ($CtxEnt = readdir($CtxH)) ) {  # each of these directories is a voicemailbox 
                if($CtxEnt == "." || $CtxEnt == "..") continue;
                $mailbox=$CtxEnt;
                echo "\tVoicemailbox $mailbox\n";
                $MbxH = opendir($dir_prefix.$DirEnt."/".$mailbox) or die ("opendir failed on $dir_prefix$DirEnt/$mailbox\n");
                while(FALSE !== ($MbxEnt = readdir($MbxH)) ) {  # each of these directories is a folder or announcemen/greeting
                        if($MbxEnt == "." || $MbxEnt == "..") continue;
                        $path = $dir_prefix.$DirEnt."/".$mailbox."/".$MbxEnt;
                        if(is_dir($path)) {
                                echo "\t\tVmailbox folder $MbxEnt: ";
                                $FldH = opendir($path) or die ("opendir failed on $path\n");
                                while(FALSE !== ($FldEnt = readdir($FldH))) {   # entries in mailbox folders: the messages
                                        if($FldEnt == "." || $FldEnt == "..")   continue;
                                        if(strpos($FldEnt,"txt") !== FALSE) {
                                                doMsgInsert($mailboxcontext,$mailbox,$FldEnt,$path);
                                                echo ".";
                                         }
                                 } # end mailbox-folder directory loop
                                closedir($FldH);
                                echo "\n";
                         } else {
                                switch($MbxEnt) {
                                        case "busy.".$msg_format:
                                        case "temp.".$msg_format:
                                        case "greet.".$msg_format:
                                        case "unavail.".$msg_format:
                                                $announce = substr($MbxEnt,0,-4);
                                                break;
                                        default:
                                                $announce = "WRONG_FORMAT";
                                                break;
                                 }
                                if($announce == "WRONG_FORMAT") continue;
                                doGreetInsert($mailboxcontext,$mailbox,$path);
                                echo "\t  -- Mailbox recording $announce done.\t \n";
                         }
                 } # end voicemailbox directory loop
                closedir($MbxH);
         } # end voicemailbox directory loop
        closedir($CtxH);
} # end voicemailbox context directory loop
closedir($DirH);

echo "\n";
echo "All done.\n";
exit(0);

function doMsgInsert($mailboxcontext,$mailbox,$msgFile,$path) {
        global $msg_format;
        $Ipref = "INSERT INTO voicemessages (msgnum,context,callerid,duration,origtime,dir,mailboxuser,mailboxcontext,recording) VALUES (";
        $msgnum = 0+substr($msgFile,3,4);
        $DETAILS = parse_ini_file($path."/".$msgFile,FALSE);
        $DFile = fopen($path."/".$msgFile,"r") or die ("Could not open $dir_prefix $MSG_FILE \n");
        while(!feof($DFile)) {          # need to get callerid seperately
                $LINE = rtrim(fgets($DFile,4096));
                if(strpos($LINE,"callerid") !== FALSE)  list($y,$callerid) = explode("=",$LINE);
         }
        fclose($DFile);
        $recording = mysql_real_escape_string(file_get_contents($path."/".substr($msgFile,0,-3).$msg_format));
        if($recording) $BLOB = "read ok"; else $BLOB = "false";
        $INSERT = $Ipref . "'".$msgnum."','".$DETAILS['context']."','" . mysql_real_escape_string($callerid)."','";
        if (!isset($DETAILS['duration']))       $DETAILS['duration'] = 0;
        $INSERT .= $DETAILS['duration']."','";
        if (!isset($DETAILS['origtime']))       $DETAILS['origtime'] = 0;
        $INSERT .= $DETAILS['origtime']."','" . $path . "','$mailbox','$mailboxcontext','";
        $INSERT_REAL = $INSERT . $recording ."')";
        $INSERT .= $BLOB. "')";
        $R = mysql_query($INSERT_REAL) or die("Problem with INSERT: \n$INSERT\n\n". mysql_error()."\n");
} # end function doMsgInsert()

function doGreetInsert($mailboxcontext,$mailbox,$path) {
        $Gpref = "INSERT INTO voicemessages (msgnum,dir,mailboxuser,mailboxcontext,recording) VALUES (-1,'";
        $dir = substr($path,0,-4);
        $recording = mysql_real_escape_string(file_get_contents($path));
        if($recording) $BLOB = "ok"; else $BLOB = "failed";
        $INSERT = $Gpref . $dir . "','$mailbox','$mailboxcontext','";
        $INSERT_REAL = $INSERT . $recording . "')";
        $INSERT .= $BLOB."')";
        $R = mysql_query($INSERT_REAL) or die("Problem with INSERT: \n$INSERT\n\n". mysql_error()."\n");
} # end function doGreetInsert()

?>