<?php
class Document extends MY_Model
{
    const DB_TABLE = 'document';
    const DB_TABLE_PK = 'document_id';

    public function load($id)
	{
		$this->load->model("document_tag");
		$success    = parent::load($id);
		$this->tags = $this->document_tag->loadByDocumentID($this->document_id);

		return $success;
	}

    function loadBySiteID($id, $user)
    {
        $query = $this->db->query("SELECT *
                                  FROM `document`
                                  INNER JOIN `acl` AS a USING(document_id)
                                  WHERE `published`='1' AND `site_id`=? AND a.read=1 AND a.acl_role_id IN ?
                                  GROUP BY `document`.document_id
                                  ORDER BY `title`",
                                  array($id, $user->roles));

        return $query->result();
    }

    function loadByCategoryID($site_id, $category_id, $tag_ids, $tag_logic, $orderby, $q, $user)
    {
    	return $this->loadByCategoryIDYear($site_id, $category_id, $tag_ids, $tag_logic, null, $orderby, $q, $user);
    }

    function loadByCategoryIDYear($site_id, $category_id, $tag_ids, $tag_logic, $year, $orderby, $q, $user)
    {
    	if(!isset($user->roles) || count($user->roles) == 0)
    	{
    		$user->roles = array(1); // Public
    	}

    	$where 		= "d.published=1 AND a.read=1 AND a.acl_role_id IN(" . implode(",", $user->roles) . ")";
		$select 	= "";
		$having 	= "";
		$groupby 	= "d.`document_id`";

    	if($site_id)
    	{
    		$where .= " AND d.`site_id`='" . $this->db->escape_str($site_id) . "'";
    	}
		if($category_id > 0)
    	{
    		$where .= " AND d.`category_id`='" . $this->db->escape_str($category_id) . "'";
    	}
		if($tag_ids)
		{
			// If tag_ids isn't already an array, split on commas if they exist.
			// If tag_ids is an integer we'll get an array of one element.
			if(!is_array($tag_ids))
			{
				$tag_ids = explode(",", $tag_ids);
			}

			$having_tags = "";

			// Match on ALL or ANY of the supplied tags, based on $tag_logic.
			foreach($tag_ids as $tag_id)
			{
				$having_tags .= ($having_tags ? " " . $tag_logic . " " : "") . "`tag_ids` REGEXP '^([0-9]+,){0,100}(" . $tag_id . ")(,[0-9]+){0,100}$'";
			}

			$having .= ($having ? " AND " : "") . "(" . $having_tags . ")";
		}
		if($year)
		{
			$where .= " AND SUBSTRING(d.`created`, 1, 4)='" . $this->db->escape_str($year) . "'";
		}

    	if($q)
    	{
    		$where .= " AND (d.`title` LIKE '%" . $this->db->escape_str($q) . "%'
    					OR d.`description` LIKE '%" . $this->db->escape_str($q) . "%')";

    		$q 			 = strtolower($q);
			$keywords 	 = explode(" ", $q);
			// Add the full query as the last keyword so that a full match gets more points.
			$keywords[]  = $q;
			$keywords 	 = array_unique($keywords);
			$select  	 = ', 0';

			// Tally up the number of occurrances for each keyword in a few of our fields.
			foreach($keywords as $q)
			{
				// Skip keywords that are a single letter because they return basically everything.
				if(strlen($q) == 1)
				{
					continue;
				}
				$select .= ' + (LENGTH(d.`title`) - LENGTH(REPLACE(LOWER(d.`title`), "' . $this->db->escape_str($q) . '", ""))) / LENGTH("' . $this->db->escape_str($q) . '")';
				$select .= ' + IF(d.`description` IS NULL, 0, (LENGTH(d.`description`) - LENGTH(REPLACE(LOWER(d.`description`), "' . $this->db->escape_str($q) . '", ""))) / LENGTH("' . $this->db->escape_str($q) . '"))';
				$select .= ' + (LENGTH(d.`url`) - LENGTH(REPLACE(LOWER(d.`url`), "' . $this->db->escape_str($q) . '", ""))) / LENGTH("' . $this->db->escape_str($q) . '")';
			}
			$select 	.= ' AS points';
			$orderby 	 = "points DESC, " . $orderby;
			$having 	 = "points > 0";
    	}

        $query = $this->db->query("SELECT d.*, c.`name` AS category_name,
                                  td.tag_id, GROUP_CONCAT(DISTINCT td.tag_id) AS tag_ids" . $select . "
		                          FROM `document` AS d
                                  " . ($category_id > 0 ? "INNER" : "LEFT") . " JOIN `categories` AS c USING(category_id)
                                  INNER JOIN `acl` AS a USING(document_id)
                                  LEFT JOIN `tag_document` AS td ON d.document_id=td.document_id
					  	          WHERE " . $where .
					  	          ($groupby ? " GROUP BY " . $groupby : "") .
					  	          ($having ? " HAVING " . $having : "") . "
								  ORDER BY $orderby");

        return $query->result();
    }

    function loadByURL($url)
    {
        $query = $this->db->query("SELECT *
                                  FROM `document`
                                  WHERE `url`=?",
                                  array($url));

        if($query->num_rows() > 0)
    	{
    		return $this->populate($query->row());
    	}

    	return false;
    }

    function loadByDirectory($site_id, $path, $user)
    {
    	if(!isset($user->roles) || count($user->roles) == 0)
    	{
    		$user->roles = array(1); // Public
    	}

		$path = str_replace(ABSOLUTE_PATH . "/documents", "", $path);

    	if(startsWith($path, "/"))
    	{
    		$path = substr($path, 1);
    	}
    	if($path != "")
    	{
    		$path .= "/";
    	}

        $query = $this->db->query("SELECT *
                                  FROM `document`
                                  INNER JOIN `acl` AS a USING(document_id)
                                  WHERE `published`='1' AND `site_id`=? AND `url` LIKE '" . $this->db->escape_str($path) . "%' AND a.read=1 AND a.acl_role_id IN ?
                                  GROUP BY `document`.document_id
                                  ORDER BY `title`",
                                  array($site_id, $user->roles));

        return $query->result();
    }

    function getFiles($site_id, $user)
    {
        if(!isset($user->roles) || count($user->roles) == 0)
    	{
    		$user->roles = array(1); // Public
    	}

		$query = $this->db->query("SELECT DISTINCT(`filename`) AS `filename`
                                  FROM `document`
                                  INNER JOIN `acl` AS a USING(document_id)
                                  WHERE `site_id`=? AND a.read=1 AND a.acl_role_id IN ?
                                  GROUP BY `document`.document_id
                                  ORDER BY `filename`",
                                  array($site_id, $user->roles));

        return $query->result();
    }

    // No ACL checks are done here because this function is used to look for orphaned document entries.
    function loadByFilename($filename, $user)
    {
        if(!isset($user->roles) || count($user->roles) == 0)
    	{
    		$user->roles = array(1); // Public
    	}

		$query = $this->db->query("SELECT *
                                  FROM `document`
                                  WHERE `filename`=?
                                  ORDER BY `title`",
                                  array($filename));

        return $query->result();
    }

    function search($q, $user)
	{
		if($q == null)
		{
			return array();
		}

		if(!isset($user->roles) || count($user->roles) == 0)
    	{
    		$user->roles = array(1); // Public
    	}

		$q = $this->db->escape_str($q);

		$query = $this->db->query("SELECT d.*
		                          FROM `document` AS d
		                          INNER JOIN `acl` AS a USING(document_id)
		                          WHERE (a.read=1 AND a.acl_role_id IN ?) AND
			                          d.`title` LIKE '%$q%' OR
			                          d.`description` LIKE '%$q%'
		                          GROUP BY d.`document_id`
		                          ORDER BY `title`",
		                          array($user->roles));

		return $query->result();
	}
}
