<?php
/*
* @package LabCMS
* @copyright (c) 2006 Alexander Levin aka Loki (loki_angel@mail.ru)
* @support http://labcms.ru
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*/

class bug_tracker_mapper
{
	protected $allowedKeys=array(
		'item'=>array(
			'version_id', 'summary', 'type', 'importance', 'author_id',
			'approver_id', 'status', 'performer_id', 'last_update'
		),
		'version'=>array('name', 'unsupported'),
		'file'=>array('post_id', 'name', 'type'),
		'post'=>array('msg', 'putdate', 'user_id', 'bug_id')
	);

	/******** темы *******/

	public function getItemsByFilter(&$count, $filter, $page=1, $atPage=10)
	{
		$order=$this->_getOrder($filter['order'], $filter['direction']);

		$data=Db::i()->selectPage(
			$count, 
			"SELECT bug_id
			FROM ?_bt_bugs b
			LEFT JOIN ?_bt_versions v ON b.version_id=v.version_id
			WHERE 1=1
			{AND `type`=?}
			{AND importance=?}
			{AND b.`status`=?}
			{AND b.version_id=?}
			{AND b.author_id=?}
			ORDER BY ".$order."
			LIMIT ?d, ?d",
			(!empty($filter['type'])?$filter['type']:DBSIMPLE_SKIP),
			(!empty($filter['importance'])?$filter['importance']:DBSIMPLE_SKIP),
			(!empty($filter['status'])?$filter['status']:DBSIMPLE_SKIP),
			(!empty($filter['version_id'])?$filter['version_id']:DBSIMPLE_SKIP),
			(!empty($filter['author_id'])?$filter['author_id']:DBSIMPLE_SKIP),
			($page-1)*$atPage, $atPage
		);

		$result=array();
		if ($data) {
			foreach ($data as $item) {
				$result[]=$item['bug_id'];
			}
		}
		return $result;
	}

	public function getItemsByArrayId($id)
	{
		$result=Db::i()->select(
			"SELECT *
			FROM ?_bt_bugs
			WHERE bug_id IN(?a)
			ORDER BY FIELD(bug_id, ?a)",
			$id, $id
		);

		$data=array();
		if ($result) {
			foreach ($result as $item) {
				$obj=Factory::create('bug_tracker.item_entity', $item);
				$data[$obj->id]=$obj;
			}
		}
		return $data;
	}

	public function addItem($item)
	{
		$insert=$item->getFilteredKeys($this->allowedKeys['item']);
		$item->id=Db::i()->query("INSERT INTO ?_bt_bugs (?#) VALUES(?a)", array_keys($insert), array_values($insert));
		return $item;
	}

	public function updateItem($item)
	{
		$data=$item->getFilteredKeys($this->allowedKeys['item']);
		return Db::i()->query("UPDATE ?_bt_bugs SET ?a WHERE bug_id=?", $data, $item->id);
	}

	public function deleteItem($item)
	{
		return Db::i()->query("DELETE FROM ?_bt_bugs WHERE bug_id=?", $item->id);
	}

	public function getCountItemsByUser($user)
	{
		return Db::i()->selectCell("SELECT COUNT(bug_id) FROM ?_bt_bugs WHERE author_id=?", $user->id);
	}

	protected function _getOrder($order, $direction)
	{
		if (!$order) $order='status';
		if (!$direction) $direction='asc';
		$key=$order.' '.$direction;

		$orderby=array(
		'status asc'=>'b.status, importance DESC, `type`, last_update DESC',
		'id asc'=>'bug_id, b.status, importance DESC, `type`, last_update DESC',
		'id desc'=>'bug_id DESC, b.status, importance DESC, `type`, last_update DESC',
		'title asc'=>'summary, b.status, importance DESC, `type`, last_update DESC',
		'title desc'=>'summary DESC, b.status, importance DESC, `type`, last_update DESC',
		'author_id asc'=>'author_id, b.status, importance DESC, `type`, last_update DESC',
		'author_id desc'=>'author_id DESC, b.status, importance DESC, `type`, last_update DESC',
		'update asc'=>'last_update, b.status, importance DESC, `type`',
		'update desc'=>'last_update DESC, b.status, importance DESC, `type`',
		'version asc'=>'v.name, b.status, importance DESC, `type`, last_update DESC',
		'version desc'=>'v.name DESC, b.status, importance DESC, `type`, last_update DESC',
		'approver_id asc'=>'approver_id, b.status, importance DESC, `type`, last_update DESC',
		'approver_id desc'=>'approver_id DESC, b.status, importance DESC, `type`, last_update DESC',
		'performer_id asc'=>'performer_id, b.status, importance DESC, `type`, last_update DESC',
		'performer_id desc'=>'performer_id DESC, b.status, importance DESC, `type`, last_update DESC',
		'type asc'=>'`type`, b.status, importance DESC, last_update DESC',
		'type desc'=>'`type` DESC, b.status, importance DESC, last_update DESC',
		);

		return array_key_exists($key, $orderby)?$orderby[$key]:$orderby['status asc'];
	}

	/******** посты *******/
	public function getPostsByItem(&$count, $item, $page=1, $atPage=1000)
	{
		$data=Db::i()->selectPage(
			$count,
			"SELECT post_id FROM ?_bt_posts
			WHERE bug_id=?
			ORDER BY post_id
			LIMIT ?d, ?d",
			$item->id, ($page-1)*$atPage, $atPage
		);
		$result=array();
		if ($data) {
			foreach ($data as $item) {
				$result[]=$item['post_id'];
			}
		}
		return $result;
	}

	public function getLastPosts($limit)
	{
		$data=Db::i()->selectCol(
			"SELECT post_id FROM ?_bt_posts
			ORDER BY post_id DESC
			LIMIT ?d",
			$limit
		);

		return $data;
	}

	public function getPostsByArrayId($id)
	{
		$result=Db::i()->select(
			"SELECT *
			FROM ?_bt_posts
			WHERE post_id IN(?a)
			ORDER BY FIELD(post_id, ?a)",
			$id, $id
		);

		$data=array();
		if ($result) {
			foreach ($result as $item) {
				$obj=Factory::create('bug_tracker.post_entity', $item);
				$data[$obj->id]=$obj;
			}
		}
		return $data;
	}

	public function addPost($post)
	{
		$insert=$post->getFilteredKeys($this->allowedKeys['post']);
		$post->id=Db::i()->query("INSERT INTO ?_bt_posts (?#) VALUES(?a)", array_keys($insert), array_values($insert));
		return $post;
	}
	
	public function updatePost($post)
	{
		$data=$post->getFilteredKeys($this->allowedKeys['post']);
		return Db::i()->query("UPDATE ?_bt_posts SET ?a WHERE post_id=?", $data, $post->id);
	}

	public function deletePosts($posts)
	{
		return Db::i()->query("DELETE FROM ?_bt_posts WHERE post_id IN(?a)", array_keys($posts));
	}
	
	public function setItemLastUpdate($item)
	{
		$item->last_update=Db::i()->selectCell(
			"SELECT putdate FROM ?_bt_posts WHERE bug_id=? ORDER BY post_id DESC LIMIT 1", $item->id
		);
		return $item;
	}

	/******** файлы *******/
	public function addFile($file)
	{
		$insert=$file->getFilteredKeys($this->allowedKeys['file']);

		return 	$file->id=Db::i()->query(
			"INSERT INTO ?_bt_files (?#) VALUES(?a)", array_keys($insert), array_values($insert)
		);
	}

	public function deleteFile($file)
	{
		return 	$file->id=Db::i()->query(
			"DELETE FROM ?_bt_files WHERE file_id=?", $file->id
		);
	}

	public function updateFile($file)
	{
		$update=$file->getFilteredKeys($this->allowedKeys['file']);
		return Db::i()->query(
			"UPDATE ?_bt_files SET ?a WHERE file_id=?", $update, $file->id
		);
	}

	public function getFilesByPosts($posts)
	{
		foreach ($posts as $post) {
			$arr[]=$post->id;
		}
		return Db::i()->selectcol("SELECT file_id FROM ?_bt_files WHERE post_id IN(?a)", $arr);
	}

	public function getFilesByArrayId($id)
	{
		$result=Db::i()->select(
			"SELECT * FROM ?_bt_files
			WHERE file_id IN(?a)
			ORDER BY FIELD(file_id, ?a)",
			$id, $id
		);
		
		$data=array();
		if ($result) {
			foreach ($result as $item) {
				$obj=Factory::create('bug_tracker.file_entity', $item);
				$data[$obj->id]=$obj;
			}
		}
		return $data;
	}

	/******** версии *******/
	public function getVersionsByArrayId($id)
	{
		$result=Db::i()->select(
			"SELECT *
			FROM ?_bt_versions
			WHERE version_id IN(?a)
			ORDER BY FIELD(version_id, ?a)",
			$id, $id
		);

		$data=array();
		if ($result) {
			foreach ($result as $item) {
				$obj=Factory::create('bug_tracker.version_entity', $item);
				$data[$obj->id]=$obj;
			}
		}
		return $data;
	}

	public function getSupportedVersions(&$count)
	{
		$data=Db::i()->selectPage(
			$count,
			"SELECT version_id FROM ?_bt_versions
			WHERE unsupported!=1
			ORDER BY name DESC"
		);
		$result=array();
		if ($data) {
			foreach ($data as $item) {
				$result[]=$item['version_id'];
			}
		}
		return $result;
	}

	public function getAllVersions(&$count)
	{
		$data=Db::i()->selectPage(
			$count,
			"SELECT version_id FROM ?_bt_versions
			ORDER BY name DESC"
		);
		$result=array();
		if ($data) {
			foreach ($data as $item) {
				$result[]=$item['version_id'];
			}
		}
		return $result;
	}

	public function updateVersion($version)
	{
		$data=$version->getFilteredKeys($this->allowedKeys['version']);
		return Db::i()->query("UPDATE ?_bt_versions SET ?a WHERE version_id=?", $data, $version->id);
	}

	public function addVersion($version)
	{
		$insert=$version->getFilteredKeys($this->allowedKeys['version']);

		return 	$version->id=Db::i()->query(
			"INSERT INTO ?_bt_versions (?#) VALUES(?a)", array_keys($insert), array_values($insert)
		);
	}

	public function deleteVersion($version)
	{
		return Db::i()->query("DELETE FROM ?_bt_versions WHERE version_id=?", $version->id);
	}

	public function isVersionsHasItems($versions)
	{
		return Db::i()->selectCol(
			"SELECT v.version_id AS ARRAY_KEY, bug_id
			FROM ?_bt_versions v
			LEFT JOIN ?_bt_bugs b ON v.version_id=b.version_id
			WHERE v.version_id IN (?a)
			GROUP BY v.version_id", $versions
		);
	}
}