<?php

class templates {
	static function records($sort_field='pk_template', $sort_order='asc', $items_per_page=0, $page_number=1) {
    	$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_template', 'name',  'datecreated');
		if (! in_array($sort_field, $allowed_sort_fields)) {
			$sort_field = 'pk_template';
		}
		
		if ($sort_order != 'asc' && $sort_order != 'desc') {
			$sort_order = 'asc';
		}
		
		// Run the sql query
		$query = $pdo->prepare("SELECT * FROM templates ORDER BY $sort_field $sort_order LIMIT :limit OFFSET :offset");
		$query->bindValue(':limit' , $limit , PDO::PARAM_INT);
		$query->bindValue(':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_template) {
		$pdo = database::pdo();

		/* Get general information about this sip phone */
		$query = $pdo->prepare("SELECT * FROM templates WHERE pk_template=:pk_template LIMIT 1");
		$query->bindParam(':pk_template', $pk_template, 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 codecs allowed for this template */
		$query = $pdo->prepare("SELECT fk_codec FROM template_codecs WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, 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['codecs'][] = $result['fk_codec'];
		}

		/* Get the list of features available for this template */
		$query = $pdo->prepare("SELECT fk_feature FROM template_features WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, 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['features'][] = $result['fk_feature'];
		}

		/* Get the list of groups that this template belongs to */
		$query = $pdo->prepare("SELECT fk_group FROM template_groups WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, 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 template can use */
		$query = $pdo->prepare("SELECT fk_outroute FROM template_outroutes WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, 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();

		/* set a flag if this is a newly created template */
		if ($data['pk_template'] == "") {
			$create_new = true;
		}
		
		/* create a record in the templates table if this is a newly created template */
		if ($create_new) {
			$query = $pdo->prepare("INSERT INTO templates(protected, name) VALUES(0, :name)");
			$query->bindParam(':name', $data['name'], PDO::PARAM_STR);
			$query->execute();
			if ($query->errorCode() != '00000') {
				$error = $query->errorInfo();
				throw new Exception($error[2]);
			}
			
			/* set the pk_template to the autogenerated id */
			$data['pk_template'] = $pdo->lastInsertId('templates_pk_template_seq');
		}
		
		/* update the main templates table information */
		$query = $pdo->prepare("
			UPDATE
				templates
			SET
				firstname_editable = :firstname_editable,
				lastname_editable  = :lastname_editable,
				password_editable  = :password_editable,
				email_editable     = :email_editable,
				fk_nattype         = :fk_nattype,
				fk_dtmfmode        = :fk_dtmfmode,
				ivrdial            = :ivrdial
			WHERE
				pk_template        = :pk_template
		");
		$query->bindParam(':firstname_editable', $data['firstname_editable'], PDO::PARAM_INT);
		$query->bindParam(':lastname_editable' , $data['lastname_editable'] , PDO::PARAM_INT);
		$query->bindParam(':password_editable' , $data['password_editable'] , PDO::PARAM_INT);
		$query->bindParam(':email_editable'    , $data['email_editable']    , PDO::PARAM_INT);
		$query->bindParam(':fk_nattype'        , $data['fk_nattype']        , PDO::PARAM_INT);
		$query->bindParam(':fk_dtmfmode'       , $data['fk_dtmfmode']       , PDO::PARAM_INT);
		$query->bindValue(':ivrdial'           , $data['ivrdial']?1:0       , PDO::PARAM_INT);
		$query->bindParam(':pk_template'       , $data['pk_template']       , PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* update the allowed codecs for this template */
		/* note : we updat by doing a delete all then insert new */
		$query = $pdo->prepare("DELETE FROM template_codecs WHERE fk_template = :pk_template");
		$query->bindValue(':pk_template', $data['pk_template']);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		if (is_array($data['codecs'])) {
			foreach ($data['codecs'] as $pk_codec) {
				$query = $pdo->prepare("INSERT INTO template_codecs (fk_template, fk_codec) VALUES (:pk_template, :pk_codec)");
				$query->bindValue(':pk_template', $data['pk_template'], PDO::PARAM_INT);
				$query->bindValue(':pk_codec'   , $pk_codec           , PDO::PARAM_INT);
				$query->execute();
				if ($query->errorCode() != '00000') {
					$error = $query->errorInfo();
					throw new Exception($error[2]);
				}
			}
		}
		
		/* update the allowed features for this template */
		$query = $pdo->prepare("DELETE FROM template_features WHERE fk_template = :pk_template");
		$query->bindValue(':pk_template', $data['pk_template']);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		if (is_array($data['features'])) {
			foreach ($data['features'] as $pk_feature) {
				$query = $pdo->prepare("INSERT INTO template_features (fk_template, fk_feature) VALUES (:pk_template, :pk_feature)");
				$query->bindValue(':pk_template', $data['pk_template'], PDO::PARAM_INT);
				$query->bindValue(':pk_feature' , $pk_feature         , 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  template */
		$query = $pdo->prepare("DELETE FROM template_outroutes WHERE fk_template = :pk_template");
		$query->bindValue(':pk_template', $data['pk_template']);
		$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) {
				$query = $pdo->prepare("INSERT INTO template_outroutes(fk_template, fk_outroute) VALUES (:pk_template, :pk_outroute)");
				$query->bindValue(':pk_template', $data['pk_template'], 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 template */
		$query = $pdo->prepare("DELETE FROM template_groups WHERE fk_template = :pk_template");
		$query->bindValue(':pk_template', $data['pk_template']);
		$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 template_groups (fk_template, fk_group) VALUES (:pk_template, :pk_group)");
				$query->bindValue(':pk_template', $data['pk_template'], 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_template'];
	}
	
	static function validate($data) {
		$errors = array();
		
		if (!preg_match('/^[a-zA-Z0-9]/', $data['name'])) {
			$errors['name']['invalid'] = true;
		}
		
		return $errors;
	}
	
	static function generate() {
		$ret = array();
		
		$ret['firstname_editable'] = 1;
		$ret['lastname_editable']  = 1;
		$ret['email_editable']     = 1;
		$ret['password_editable']  = 1;
		$ret['fk_nattype']         = 1;
		$ret['fk_dtmfmode']        = 1;
		
		$codecs = codecs::records();
		foreach($codecs as $codec) {
			if ($codec['recomended']) {
				$ret['codecs'][] = $codec['pk_codec'];
			}
		}
		
		return $ret;
	}
	
	static function count() {
		$pdo = database::pdo();
		
		$query = $pdo->prepare("SELECT COUNT(*) AS count FROM templates");
		$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 delete($pk_template) {
		$pdo = database::pdo();
	
		/* Delete entry from the templates table */
		$query = $pdo->prepare("DELETE FROM templates WHERE pk_template = :pk_template AND protected = 0");
		$query->bindParam(':pk_template', $pk_template, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* Avoid deleting from the template_* tables if the prev query failed since this means that protected could have been = 1 */
		if ($query->rowCount() == 0 ) {
			return;
		}
		
		/* delete the default codecs of this template */
		$query = $pdo->prepare("DELETE FROM template_codecs WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* delete outgoing call rules bindings for this template */
		$query = $pdo->prepare("DELETE FROM template_features WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* delete the template from the groups that it belongs to */
		$query = $pdo->prepare("DELETE FROM template_groups WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* delete outgoing call rules bindings for this template */
		$query = $pdo->prepare("DELETE FROM template_outroutes WHERE fk_template = :pk_template");
		$query->bindParam(':pk_template', $pk_template, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
	}
}