<?php
class extensions {
	static function records($sort_field='pk_extension', $sort_order='asc', $items_per_page=0, $page_number=1, $type=null) {
    	$pdo = database::pdo();

		// Calculate the $limit / $offset based on $items_per_page / $page_number
		if ($items_per_page == 0) {
			$limit  = 9999;
			$offset = 0;
		} else {
			$limit  = $items_per_page;
			$offset = ($page_number-1) * $items_per_page;
		}

		// Sanitize the sort_filed  / sort_order
		$allowed_sort_fields = array('pk_extension', 'name', 'type', 'datecreated');
		if (! in_array($sort_field, $allowed_sort_fields)) {
			$sort_field = 'pk_extension';
		}
		if ($sort_field == 'datecreated') {
			$sort_field = 'extensions.datecreated';
		}
		
		if ($sort_order != 'asc' && $sort_order != 'desc') {
			$sort_order = 'asc';
		}
		
		// Run the sql query
		$query = $pdo->prepare("
			SELECT
				extensions.*,
					COALESCE(ext_sipphones.firstname||' ','') || COALESCE(ext_sipphones.lastname,'') ||
					COALESCE(ext_virtual.firstname||' ','') || COALESCE(ext_virtual.lastname,'') ||
					COALESCE(ext_queues.name||' ', '') || COALESCE(ivr_menus.name,'') 
				AS name,
				datecreated
			FROM
				extensions
				LEFT JOIN ext_sipphones ON ext_sipphones.pk_extension = extensions.pk_extension
				LEFT JOIN ext_virtual   ON ext_virtual.pk_extension   = extensions.pk_extension
				LEFT JOIN ext_queues    ON ext_queues.pk_extension    = extensions.pk_extension
				LEFT JOIN ext_ivr       ON ext_ivr.pk_extension       = extensions.pk_extension
				LEFT JOIN ivr_menus     ON ext_ivr.fk_menu            = ivr_menus.pk_menu
			".($type=='phone'     ?" WHERE extensions.type IN ('sipphone') ":'')."
			".($type=='ivr'       ?" WHERE extensions.type IN ('ivr') ":'')."
			".($type=='agent_only'?" WHERE extensions.type IN ('agent') ":'')."
			".($type==null        ?" WHERE NOT extensions.type LIKE ('%_reserved') ":'')."
			ORDER BY
				$sort_field $sort_order
			LIMIT :limit OFFSET :offset
		");
		$query->bindParam(':limit'     , $limit     , PDO::PARAM_INT);
		$query->bindParam(':offset'    , $offset    , PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}

		$ret = $query->fetchAll(PDO::FETCH_ASSOC);
		return $ret;
	}
	
	static function retrive($pk_extension) {
		$pdo = database::pdo();

		/* Get general information about this extension */
		$query = $pdo->prepare("SELECT * from extensions WHERE pk_extension=:pk_extension LIMIT 1");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		$ret = $query->fetch(PDO::FETCH_ASSOC);

		/* If we have no results at the last query, return a null array */
		if (empty($ret)) { 
			return null;
		}
		
		/* Get the list of groups that this extension belongs to */
		$query = $pdo->prepare("SELECT fk_group FROM extension_groups WHERE fk_extension = :pk_extension");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		while ($result = $query->fetch(PDO::FETCH_ASSOC)) {
			$ret['groups'][] = $result['fk_group'];
		}

		/* Get the list of outgoing routes that this extension can use */
		$ret['outroutes'] = array();
		$query = $pdo->prepare("SELECT fk_outroute FROM extension_outroutes WHERE fk_extension = :pk_extension");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		while ($result = $query->fetch(PDO::FETCH_ASSOC)) {
			$ret['outroutes'][] = $result['fk_outroute'];
		}

		return $ret;
	}
	
	static function save($data) {
		$pdo = database::pdo();

		/* create new record if no pk_extension was provided */
		if ($data['pk_extension'] == "") {
			/* create a record in the main extensions table */
			$query = $pdo->prepare("INSERT INTO extensions(type, extension) VALUES(:type, :extension)");
			$query->bindValue(':type'     , $data['type']     , PDO::PARAM_STR);
			$query->bindValue(':extension', $data['extension'], PDO::PARAM_STR);
			$query->execute();
			if ($query->errorCode() != '00000') {
				$error = $query->errorInfo();
				throw new Exception($error[2]);
			}
			
			/* set the pk_extension to the autogenerated id */
			$data['pk_extension'] = $pdo->lastInsertId('extensions_pk_extension_seq');
		}
		
		/* set if this extension can be dialed from a ivr or not */
		$query = $pdo->prepare("
			UPDATE
				extensions
			SET
				feature = :feature,
				ivrdial = :ivrdial 
			WHERE
				pk_extension = :pk_extension
		");
		$query->bindValue(':feature'     , $data['feature']?1:0 , PDO::PARAM_INT);
		$query->bindValue(':ivrdial'     , $data['ivrdial']?1:0 , PDO::PARAM_INT);
		$query->bindValue(':pk_extension', $data['pk_extension'], PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* update the allowed outgoing call routes for this  extension */
		$query = $pdo->prepare("DELETE FROM extension_outroutes WHERE fk_extension = :pk_extension");
		$query->bindValue(':pk_extension', $data['pk_extension']);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		if (is_array($data['outroutes'])) {
			foreach ($data['outroutes'] as $pk_outroute=>$is_active) {
				if (!$is_active) { continue; }
				$query = $pdo->prepare("INSERT INTO extension_outroutes(fk_extension, fk_outroute) VALUES (:pk_extension, :pk_outroute)");
				$query->bindValue(':pk_extension', $data['pk_extension'], PDO::PARAM_INT);
				$query->bindValue(':pk_outroute' , $pk_outroute         , PDO::PARAM_INT);
				$query->execute();
				if ($query->errorCode() != '00000') {
					$error = $query->errorInfo();
					throw new Exception($error[2]);
				}
			}
		}
		
		/* update the group bindings for this extension */
		$query = $pdo->prepare("DELETE FROM extension_groups WHERE fk_extension = :pk_extension");
		$query->bindValue(':pk_extension', $data['pk_extension']);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		if (is_array($data['groups'])) {
			foreach ($data['groups'] as $pk_group) {
				$query = $pdo->prepare("INSERT INTO extension_groups (fk_extension, fk_group) VALUES (:pk_extension, :pk_group)");
				$query->bindValue(':pk_extension', $data['pk_extension'], PDO::PARAM_INT);
				$query->bindValue(':pk_group'    , $pk_group            , PDO::PARAM_INT);
				$query->execute();
				if ($query->errorCode() != '00000') {
					$error = $query->errorInfo();
					throw new Exception($error[2]);
				}
			}
		}
		
		return $data['pk_extension'];
	}
	
	static function validate($data) {
		$errors = array();

		if ($data['pk_extension'] == '') {
			$create_new = true;
		}
		
		/* the extension tests make sense only if is new extension */
		if ($create_new) {
			/* regular extensions shold be numeric, 3 to 5 digits in legth */
			if ($data['feature'] != 1 && !preg_match('/^[0-9]{3,5}$/', $data['extension'])) {
				$errors['extension']['invalid'] = true;
				
			/* feature code extensions shold be numeric, 2 digits in legth */
			} else if ($data['feature'] == 1 && !preg_match('/^[0-9]{2}$/', $data['extension'])) {
				$errors['extension']['invalid'] = true;
				
			/* sipphone's extension need to be unique */
			} else {
				if (extensions::retrive_by_extension($data['extension']) != null) {
					$errors['extension']['duplicate'] = true;
				}
			}
		}
		
		return $errors;
	}
	
	static function generate() {
		$ret = array();

		return $ret;
	}
	
	static function delete($pk_extension) {
		$pdo = database::pdo();

		$extension = extensions::retrive($pk_extension);
		status_extensions::delete($extension['extension']);

		/* delete entry from the extensions table */
		$query = $pdo->prepare("DELETE FROM extensions WHERE pk_extension = :pk_extension");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* delete the extensions from the groups that it belongs to */
		$query = $pdo->prepare("DELETE FROM extension_groups WHERE fk_extension = :pk_extension");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* delete outgoing call routes bindings for this extension */
		$query = $pdo->prepare("DELETE FROM extension_outroutes WHERE fk_extension = :pk_extension");
		$query->bindParam(':pk_extension', $pk_extension, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
	}
	
	
	static function count($type="") {
		$pdo = database::pdo();
		
		$query = $pdo->prepare("
			SELECT
				COUNT(*) AS count 
			FROM
				extensions
			".($type=='phone'     ?" WHERE extensions.type IN ('sipphone') ":'')."
			".($type=='ivr'       ?" WHERE extensions.type IN ('ivr') ":'')."
			".($type=='agent_only'?" WHERE extensions.type IN ('agent') ":'')."
			".($type==null        ?" WHERE NOT extensions.type LIKE ('%_reserved') ":'')."

		");
		$query->execute();
			if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		$ret = $query->fetch(PDO::FETCH_ASSOC);
		
		return $ret['count'];		
	}
	
	static function retrive_by_extension($extension) {
		$pdo = database::pdo();

		/* get the info associated with this extension from the extensions table*/
		$query = $pdo->prepare("SELECT * FROM extensions WHERE extension = :extension");
		$query->bindParam(':extension', $extension, PDO::PARAM_STR);
		
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		$data = $query->fetch(PDO::FETCH_ASSOC);

		return extensions::retrive($data['pk_extension']);
	}
}