<?php
class groups {
	static function records($sort_field='pk_group', $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_group', 'name',  'datecreated');
		if (! in_array($sort_field, $allowed_sort_fields)) {
			$sort_field = 'pk_group';
		}
		
		if ($sort_order != 'asc' && $sort_order != 'desc') {
			$sort_order = 'asc';
		}
		
		// Run the sql query
		$query = $pdo->prepare("
			SELECT
				groups.*,
				(SELECT COUNT(fk_extension) FROM extension_groups WHERE fk_group = pk_group) AS members_count
			FROM
				groups
			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_group) {
		$pdo = database::pdo();
		
		/* Get general information about this group */
		$query = $pdo->prepare("SELECT * from groups WHERE pk_group=:pk_group LIMIT 1");
		$query->bindParam(':pk_group', $pk_group, 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 extensions that belong to this group */
		$query = $pdo->prepare("SELECT fk_extension FROM extension_groups WHERE fk_group = :pk_group");
		$query->bindParam(':pk_group', $pk_group, 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['extensions'][] = $result['fk_extension'];
		}
		
		return $ret;
	}

	static function save($data) {
		$pdo = database::pdo();
		
		/* create new record if no pk_group was provided */
		if ($data['pk_group'] == "") {
			/* create a record in the main extensions table */
			$query = $pdo->prepare("INSERT INTO groups(name) VALUES('')");
			$query->execute();
			if ($query->errorCode() != '00000') {
				$error = $query->errorInfo();
				throw new Exception($error[2]);
			}
			
			/* set the pk_extension to the autogenerated id */
			$data['pk_group'] = $pdo->lastInsertId('groups_pk_group_seq');
		}
		
		/* set the name of this group */
		$query = $pdo->prepare("UPDATE groups SET name = :name WHERE pk_group = :pk_group");
		$query->bindValue(':name'    , $data['name']    , PDO::PARAM_STR);
		$query->bindValue(':pk_group', $data['pk_group'], PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* update the member extensions (update by delete old and insert new) */
		$query = $pdo->prepare("DELETE FROM extension_groups WHERE fk_group = :pk_group");
		$query->bindValue(':pk_group', $data['pk_group'], PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		if (is_array($data['extensions'])) {
			foreach ($data['extensions'] as $pk_extension) {
				$query = $pdo->prepare("INSERT INTO extension_groups (fk_extension, fk_group) VALUES (:pk_extension, :pk_group)");
				$query->bindValue(':pk_extension', $pk_extension    , PDO::PARAM_INT);
				$query->bindValue(':pk_group'    , $data['pk_group'], PDO::PARAM_INT);
				$query->execute();
				if ($query->errorCode() != '00000') {
					$error = $query->errorInfo();
					throw new Exception($error[2]);
				}
			}
		}
		
		/* Notify the pbx about the changes */
		pbx::notify_changes('groups');
		
		return $data['pk_group'];
	}
	
	static function validate($data) {
		$errors = array();

		/* make sure that the name is not empty */
		if (! preg_match('/^[a-zA-Z0-9]{1}[a-zA-Z0-9 ]{0,31}$/', $data['name'])) {
			$errors['name']['invalid'] = true;
		}

		return $errors;
	}
	
	static function generate() {
		$ret = array();

		return $ret;
	}
	
	static function count() {
		$pdo = database::pdo();
		
		$query = $pdo->prepare("SELECT COUNT(*) AS count FROM groups");
		$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_group) {
		$pdo = database::pdo();
		
		/* delete entry from the groups table */
		$query = $pdo->prepare("DELETE FROM groups WHERE pk_group = :pk_group");
		$query->bindParam(':pk_group', $pk_group, 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_group = :pk_group");
		$query->bindParam(':pk_group', $pk_group, PDO::PARAM_INT);
		$query->execute();
		if ($query->errorCode() != '00000') {
			$error = $query->errorInfo();
			throw new Exception($error[2]);
		}
		
		/* Notify the pbx about the changes */
		pbx::notify_changes('groups');
	}
}