root/contributed_modules/modules/isymphony/install.php

<?php
/*
 *Name         : install.php
 *Author       : Michael Yara
 *Created      : August 15, 2008
 *Last Updated : May 26, 2009
 *History      : 0.3
 *Purpose      : Create and populate isymphony tables
 *Copyright    : 2008 HEHE Enterprises, LLC
 */

global $db;

//Create location table
$query = "  CREATE TABLE IF NOT EXISTS 
      isymphony_location( admin_user_name VARCHAR(100),
                admin_password VARCHAR(100),
                originate_timeout INTEGER(10),
                auto_reload INTEGER(1),
                page_status_enabled INTEGER(1),
                jabber_host VARCHAR(100),
                jabber_domain VARCHAR(100),
                jabber_resource VARCHAR(100),
                jabber_port INTEGER(10))"; 
                
echo "Creating \"isymphony_location\" Table....<br>";               
$results = $db->query($query);
if(DB::IsError($results) && ($results->getCode() != DB_ERROR_ALREADY_EXISTS)) {
  echo "ERROR: could not create table.<br>";
} else {
  
  //Check to see if table already contains location information if not insert row of default values
  $result = $db->query("SELECT * FROM isymphony_location");
  if($result->numRows() == 0) {
    
    //Populate with default values
    echo "Populating table.....<br>";
    $prepStatement = $db->prepare("INSERT INTO isymphony_location (admin_user_name, admin_password, originate_timeout, auto_reload, page_status_enabled, jabber_port, jabber_resource) VALUES (?, ?, ?, ?, ?, ?, ?)");
    $values = array("admin", "secret", "30000", 1, 1, 5222, "iSymphony");
    $db->execute($prepStatement, $values);
  }
}               
echo "Done<br>";                

//Create users table
$query = "  CREATE TABLE IF NOT EXISTS 
      isymphony_users(isymphony_user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
              user_id VARCHAR(100) UNIQUE NOT NULL,
              add_extension INTEGER(1), 
              add_profile INTEGER(1), 
              password VARCHAR(100), 
              display_name VARCHAR(100), 
              peer VARCHAR(100),
              email VARCHAR(100),
              cell_phone VARCHAR(100),
              auto_answer INTEGER(1),
              jabber_host VARCHAR(100),
              jabber_domain VARCHAR(100),
              jabber_resource VARCHAR(100),
              jabber_port INTEGER(10),
              jabber_user_name VARCHAR(100),
              jabber_password VARCHAR(100))";

echo "Creating \"isymphony_users\" Table....<br>";
$results = $db->query($query);
if(DB::IsError($results) && ($results->getCode() != DB_ERROR_ALREADY_EXISTS)) {
  echo "ERROR: could not create table.<br>";
} else {


  //If updating check if proper column entries exist. If not create them. Used for module upgrade purposes.
  $iSymphonyUserTableStringColumns = array("email", "cell_phone", "jabber_host", "jabber_domain", "jabber_resource", "jabber_user_name", "jabber_password");
  $iSymphonyUserTableIntegerColumns = array("jabber_port");
  $iSymphonyUserTableBooleanColumns= array("auto_answer");

  foreach($iSymphonyUserTableStringColumns as $stringColumn) {
    $sql = "SELECT $stringColumn FROM isymphony_users";
    $check = $db->getRow($sql, DB_FETCHMODE_ASSOC);
    if(DB::IsError($check)) {
        $sql = "ALTER TABLE isymphony_users ADD $stringColumn VARCHAR(200);";
        $result = $db->query($sql);
        if(DB::IsError($result)) { die_freepbx($result->getDebugInfo()); }
    }
  }
  
  foreach($iSymphonyUserTableIntegerColumns as $integerColumn) {
    $sql = "SELECT $integerColumn FROM isymphony_users";
    $check = $db->getRow($sql, DB_FETCHMODE_ASSOC);
    if(DB::IsError($check)) {
        $sql = "ALTER TABLE isymphony_users ADD $integerColumn INTEGER(10);";
        $result = $db->query($sql);
        if(DB::IsError($result)) { die_freepbx($result->getDebugInfo()); }
    }
  }
  
  foreach($iSymphonyUserTableBooleanColumns as $booleanColumn) {
    $sql = "SELECT $booleanColumn FROM isymphony_users";
    $check = $db->getRow($sql, DB_FETCHMODE_ASSOC);
    if(DB::IsError($check)) {
        $sql = "ALTER TABLE isymphony_users ADD $booleanColumn INTEGER(1);";
        $result = $db->query($sql);
        if(DB::IsError($result)) { die_freepbx($result->getDebugInfo()); }
    }
  }

  //Add users to table
  echo "Populating table.....<br>";
  if((function_exists("core_users_list")) && (($freePBXUsers = core_users_list()) !== null)){
    foreach($freePBXUsers as $freePBXUser) {
      if(function_exists("core_devices_get")) {
        $freePBXDeviceInfo = core_devices_get($freePBXUser[0]);
        
        $userId = $freePBXUser[0];
        $extensionDisplayName = $freePBXUser[1] == "" ? $freePBXUser[0] : $freePBXUser[1];
        $extensionPeer = ($freePBXDeviceInfo['dial'] != "") ? $freePBXDeviceInfo['dial'] : "SIP/$userId";
        
        $prepStatement = $db->prepare("INSERT INTO isymphony_users (user_id, add_extension, add_profile, password, display_name, peer, auto_answer, jabber_resource) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        $values = array($userId, 1, 1, "secret", $extensionDisplayName, $extensionPeer, 0, "iSymphony");
        $db->execute($prepStatement, $values);
      }
    }
  }
}
echo "Done<br>";

//Create queues table
$query = "  CREATE TABLE IF NOT EXISTS 
      isymphony_queues( isymphony_queue_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                queue_id VARCHAR(100) UNIQUE NOT NULL,
                add_queue INTEGER(1), 
                display_name VARCHAR(100))";
    
echo "Creating \"isymphony_queues\" Table....<br>";     
$results = $db->query($query);          
if(DB::IsError($results) && ($results->getCode() != DB_ERROR_ALREADY_EXISTS)) {
  echo "ERROR: could not create table.<br>";
} else {
  //Add queues to table
  echo "Populating table.....<br>";
  if((function_exists("queues_list")) && (($freePBXQueues = queues_list()) !== null)) {
    foreach($freePBXQueues as $freePBXQueue) {
      
      $queueId = $freePBXQueue[0];
      $queueDispalyName = $freePBXQueue[1] == "" ? $freePBXQueue[0] : $freePBXQueue[1];
      
      $prepStatement = $db->prepare("INSERT INTO isymphony_queues (queue_id, add_queue, display_name) VALUES (?, ?, ?)");
      $values = array($queueId, 1, $queueDispalyName);
      $db->execute($prepStatement, $values);
    }
  }
}               
echo "Done<br>";

//Create conference rooms table
$query = "  CREATE TABLE IF NOT EXISTS 
      isymphony_conference_rooms( isymphony_conference_room_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                    conference_room_id VARCHAR(100) UNIQUE NOT NULL,
                    add_conference_room INTEGER(1), 
                    display_name VARCHAR(100))";
    
echo "Creating \"isymphony_conference_rooms\" Table....<br>";     
$results = $db->query($query);          
if(DB::IsError($results) && ($results->getCode() != DB_ERROR_ALREADY_EXISTS)) {
  echo "ERROR: could not create table.<br>";
} else {
  //Add rooms to table
  echo "Populating table.....<br>";
  if((function_exists("conferences_list")) && (($freePBXConferenceRooms = conferences_list()) !== null)) {
    foreach($freePBXConferenceRooms as $freePBXConferenceRoom) {
      
      $conferenceRoomId = $freePBXConferenceRoom[0];
      $conferenceRoomsDispalyName = $freePBXConferenceRoom[1] == "" ? $freePBXConferenceRoom[0] : $freePBXConferenceRoom[1];
      
      $prepStatement = $db->prepare("INSERT INTO isymphony_conference_rooms (conference_room_id, add_conference_room, display_name) VALUES (?, ?, ?)");
      $values = array($conferenceRoomId, 1, $conferenceRoomsDispalyName);
      $db->execute($prepStatement, $values);
    }
  }
}               
echo "Done<br>";
?>