<?php
/**
 * @version        4.3.0
 * @package        Joomla
 * @subpackage     Helpdesk Pro
 * @author         Tuan Pham Ngoc
 * @copyright      Copyright (C) 2013 - 2021 Ossolution Team
 * @license        GNU/GPL, see LICENSE.php
 */

namespace OSSolution\HelpdeskPro\Site\Helper;

use OSL\Container\Container;
use OSL\Utils\Database as DatabaseUtils;

defined('_JEXEC') or die;

class Database
{
	/**
	 * Get all published categories
	 *
	 * @param   string  $order
	 * @param   array   $filters
	 * @param   string  $fieldSuffix
	 * @param   int     $categoryType
	 *
	 * @return array
	 */
	public static function getAllCategories($order = 'title', $filters = array(), $fieldSuffix = '', $categoryType = 1)
	{
		$db    = Container::getInstance('com_helpdeskpro')->db;
		$query = $db->getQuery(true);
		$query->select('id, parent_id, managers')
			->select($db->quoteName('title' . $fieldSuffix, 'title'))
			->from('#__helpdeskpro_categories')
			->where('published=1')
			->where('category_type IN (0, ' . $categoryType . ')')
			->order($order);

		foreach ($filters as $filter)
		{
			$query->where($filter);
		}

		$db->setQuery($query);

		return $db->loadObjectList();
	}

	/**
	 * Get all published statuses
	 *
	 * @param   string  $order
	 * @param   string  $fieldSuffix
	 *
	 * @return array
	 */
	public static function getAllStatuses($order = 'ordering', $fieldSuffix = '')
	{
		$db    = Container::getInstance('com_helpdeskpro')->db;
		$query = $db->getQuery(true);
		$query->select('id, title')
			->from('#__helpdeskpro_statuses')
			->where('published=1')
			->order($order);

		if ($fieldSuffix)
		{
			DatabaseUtils::getMultilingualFields($query, array('title'), $fieldSuffix);
		}

		$db->setQuery($query);

		return $db->loadObjectList();
	}

	/**
	 * Get all published priorities
	 *
	 * @param   string  $order
	 * @param   string  $fieldSuffix
	 *
	 * @return array
	 */
	public static function getAllPriorities($order = 'ordering', $fieldSuffix = '')
	{
		$db    = Container::getInstance('com_helpdeskpro')->db;
		$query = $db->getQuery(true);
		$query->select('id, title')
			->from('#__helpdeskpro_priorities')
			->where('published=1')
			->order($order);

		if ($fieldSuffix)
		{
			DatabaseUtils::getMultilingualFields($query, array('title'), $fieldSuffix);
		}

		$db->setQuery($query);

		return $db->loadObjectList();
	}

	/**
	 * Get all published labels
	 *
	 * @param   string  $order
	 *
	 * @return array
	 */
	public static function getAllLabels($order = 'title')
	{
		$db    = Container::getInstance('com_helpdeskpro')->db;
		$query = $db->getQuery(true);
		$query->select('id, title')
			->from('#__helpdeskpro_labels')
			->where('published=1')
			->order($order);
		$db->setQuery($query);

		return $db->loadObjectList();
	}

	/**
	 * Get all published labels
	 *
	 * @param   int  $staffGroupId
	 *
	 * @return array
	 */
	public static function getAllStaffs($staffGroupId)
	{
		$db           = Container::getInstance('com_helpdeskpro')->db;
		$query        = $db->getQuery(true);
		$config       = Helper::getConfig();
		$displayField = $config->get('staff_display_field', 'username') ?: 'username';

		$query->select("a.id, a.username, a.name, a.email")
			->from("#__users AS a")
			->innerJoin("#__user_usergroup_map AS b ON a.id = b.user_id")
			->where("group_id=" . (int) $staffGroupId)
			->order($displayField);
		$db->setQuery($query);

		return $db->loadObjectList();
	}

	/**
	 * Get all custom fields which will be displayed in list view
	 *
	 * @param   string  $fieldSuffix
	 *
	 * @return array
	 */
	public static function getFieldsOnListView($fieldSuffix = null)
	{
		$db    = Container::getInstance('com_helpdeskpro')->db;
		$query = $db->getQuery(true);

		$query->select('*')
			->from('#__helpdeskpro_fields')
			->where('show_in_list_view=1')
			->where('published=1')
			->order('ordering');

		if ($fieldSuffix)
		{
			DatabaseUtils::getMultilingualFields($query, array('title', 'description', 'values', 'default_values'), $fieldSuffix);
		}

		$db->setQuery($query);

		return $db->loadObjectList();
	}
}