/**
* Get the master query for retrieving a list of items subject to the model state.
*
* @return jdatabasequery
*/
public function getListQuery()
{
// Create a new query object.
$db = $this->getDbo();
$query = $db->getQuery(true);
$user = JFactory::getUser();
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.id, a.asset_id, a.project_id, a.parent_id, a.title, a.alias, a.description, a.created, ' . 'a.file_name, a.file_extension, a.file_size, ' . 'a.created_by, a.modified, a.modified_by, a.checked_out, ' . 'a.checked_out_time, a.attribs, a.access, a.state, a.ordering'));
$query->from('#__pf_design_revisions AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS editor');
$query->join('LEFT', '#__users AS uc ON uc.id = a.checked_out');
// Join over the asset groups.
$query->select('ag.title AS access_level');
$query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access');
// Join over the users for the owner.
$query->select('ua.name AS author_name, ua.email AS author_email');
$query->join('LEFT', '#__users AS ua ON ua.id = a.created_by');
// Join over the projects for the project title and alias.
$query->select('p.title AS project_title, p.alias AS project_alias');
$query->join('LEFT', '#__pf_projects AS p ON p.id = a.project_id');
// Join over the comments for comment count
$query->select('COUNT(DISTINCT co.id) AS comments');
$query->join('LEFT', '#__pf_comments AS co ON (co.context = ' . $db->quote('com_pfdesigns.revision') . ' AND co.item_id = a.id)');
// Join over the approved table for approved/declined count
$query->select('COUNT(DISTINCT ap.created_by) AS approved_count');
$query->join('LEFT', '#__pf_designs_approved AS ap ON (ap.revision_id = a.id AND ap.state = 1)');
$query->select('COUNT(DISTINCT de.created_by) AS declined_count');
$query->join('LEFT', '#__pf_designs_approved AS de ON (de.revision_id = a.id AND de.state = 0)');
// Implement View Level Access
if (!$user->authorise('core.admin', 'com_pfdesigns')) {
$levels = implode(',', $user->getAuthorisedViewLevels());
$query->where('a.access IN (' . $levels . ')');
}
// Filter fields
$filters = array();
$filters['a.state'] = array('STATE', $this->getState('filter.published'));
$filters['a.created_by'] = array('INT-NOTZERO', $this->getState('filter.author'));
$filters['a.parent_id'] = array('INT-NOTZERO', $this->getState('filter.parent_id'));
$filters['a'] = array('SEARCH', $this->getState('filter.search'));
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Group by ID
$query->group('a.id');
// Add the list ordering clause.
$query->order($this->getState('list.ordering', 'a.ordering') . ' ' . $this->getState('list.direction', 'DESC'));
return $query;
}
/**
* Gets the estimated project cost
*
* @return integer $sum
*/
public function getProjectEstimatedCost()
{
$db = $this->getDbo();
$query = $db->getQuery(true);
// Return 0 if no project is selected
if ((int) $this->getState('filter.project') == 0) {
return 0.0;
}
// Construct the query
$query->select('SUM((a.estimate / 60) * (a.rate / 60))')->from('#__pf_tasks AS a')->where('a.state = 1');
$filters = array();
$filters['a.project_id'] = array('INT-NOTZERO', $this->getState('filter.project'));
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Get the result
$db->setQuery((string) $query);
$sum = (double) $db->loadResult();
// Return the items
return $sum;
}
/**
* Build a list of task lists
*
* @return jdatabasequery
*/
public function getTaskLists()
{
$db = $this->getDbo();
$query = $db->getQuery(true);
$user = JFactory::getUser();
$access = PFtasksHelper::getActions();
// Return empty array if no project is select
$project = (int) $this->getState('filter.project');
if ($project < 0) {
return array();
}
// Construct the query
$query->select('a.id AS value, a.title AS text');
$query->from('#__pf_task_lists AS a');
// $query->join('LEFT', '#__pf_tasks AS a ON a.list_id = t.id');
// Implement View Level Access
if (!$user->authorise('core.admin', 'com_pftasks')) {
$groups = implode(',', $user->getAuthorisedViewLevels());
$query->where('a.access IN (' . $groups . ')');
}
// Filter fields
$filters = array();
$filters['a.project_id'] = array('INT-NOTZERO', $this->getState('filter.project'));
if (!$access->get('core.edit.state') && !$access->get('core.edit')) {
$filters['a.state'] = array('STATE', '1');
}
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Group and order
// $query->group('a.id');
$query->order('a.title ASC');
// Get results
$db->setQuery((string) $query);
$items = (array) $db->loadObjectList();
// Return the items
return $items;
}
/**
* Build a list of authors
*
* @return array
*/
public function getAuthors()
{
$db = $this->getDbo();
$query = $db->getQuery(true);
$user = $user = JFactory::getUser();
$access = PFmilestonesHelper::getActions();
// Return empty array if no project is select
$project = (int) $this->getState('filter.project');
if ($project <= 0) {
return array();
}
// Construct the query
$query->select('u.id AS value, u.name AS text');
$query->from('#__users AS u');
$query->join('INNER', '#__pf_milestones AS a ON a.created_by = u.id');
// Implement View Level Access
if (!$access->get('core.admin', 'com_pfmilestones')) {
$groups = implode(',', $user->getAuthorisedViewLevels());
$query->where('a.access IN (' . $groups . ')');
}
// Filter fields
$filters = array();
$filters['a.project_id'] = array('INT-NOTZERO', $this->getState('filter.project'));
if (!$access->get('core.edit.state') && !$access->get('core.edit')) {
$filters['a.state'] = array('STATE', '1');
}
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Group and order
$query->group('u.id');
$query->order('u.name ASC');
$db->setQuery((string) $query);
$items = (array) $db->loadObjectList();
// Return the items
return $items;
}
function prepareData()
{
$module = JModuleHelper::getModule('mod_pf_time');
$params = new JRegistry();
$params->loadString($module->params);
$action = JRequest::getVar('action', null);
$filter_author = $params->get('filter_own', null);
$filter_start_date = JRequest::getVar('filter_start_date', null);
$filter_end_date = JRequest::getVar('filter_end_date', null);
$filter_project = JRequest::getVar('filter_project', null);
$app = JFactory::getApplication();
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$user = JFactory::getUser();
$access = PFtasksHelper::getActions();
$taskdata = null;
// Select the required fields from the table.
$query->select('a.id, a.project_id, a.task_id, a.task_title, a.description, ' . 'a.checked_out, a.checked_out_time, a.state, a.access, a.rate, a.billable,' . 'a.created, a.created_by, a.log_date, a.log_time ');
$query->from('#__pf_timesheet AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS editor');
$query->join('LEFT', '#__users AS uc ON uc.id = a.checked_out');
// Join over the asset groups.
$query->select('ag.title AS access_level');
$query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access');
// Join over the users for the author.
$query->select('ua.name AS author_name');
$query->join('LEFT', '#__users AS ua ON ua.id = a.created_by');
// Join over the projects for the project title.
$query->select('p.title AS project_title, p.alias AS project_alias');
$query->join('LEFT', '#__pf_projects AS p ON p.id = a.project_id');
// Join over the tasks for the task title.
$query->select('t.id AS task_exists, t.alias AS task_alias, t.estimate');
$query->join('LEFT', '#__pf_tasks AS t ON t.id = a.task_id');
// Join over the milestones for the milestone alias.
$query->select('m.id AS milestone_id, m.alias AS milestone_alias');
$query->join('LEFT', '#__pf_milestones AS m ON m.id = t.milestone_id');
// Join over the task lists for the list alias.
$query->select('l.id AS list_id, l.alias AS list_alias');
$query->join('LEFT', '#__pf_task_lists AS l ON l.id = t.list_id');
// Implement View Level Access
if (!$user->authorise('core.admin')) {
$levels = implode(',', $user->getAuthorisedViewLevels());
$query->where('a.access IN (' . $levels . ')');
}
// Calculate billable amount
$query->select('CASE WHEN (a.billable = 1 AND a.rate > 0 AND a.log_time > 0) ' . 'THEN ((a.log_time / 60) * (a.rate / 60)) ' . 'ELSE "0.00"' . 'END AS billable_total');
// Filter fields
$filters = array();
$filters['a.project_id'] = array('INT-NOTZERO', $filter_project);
if ($filter_author == 1) {
$filters['a.created_by'] = array('INT-NOTZERO', $user->get('id'));
}
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
//finally add our own date range filter
if ($filter_start_date && $filter_end_date) {
$query->where("a.log_date between '{$filter_start_date}' AND '{$filter_end_date}'");
} else {
if ($filter_start_date) {
$query->where("a.log_date >= '{$filter_start_date}'");
} else {
if ($filter_end_date) {
$query->where("a.log_date <= '{$filter_end_date}'");
}
}
}
// Add the list ordering clause.
$order_col = 'a.log_date';
$order_dir = 'desc';
$query->order($db->escape($order_col . ' ' . $order_dir));
$query->group('a.id');
$db->setQuery($query);
$taskdata = $db->loadObjectList();
if ($action == 'export') {
exportData($taskdata);
} else {
displayData($taskdata);
}
}
/**
* Get the master query for retrieving a list of items subject to the model state.
*
* @return jdatabasequery
*/
protected function getListQuery()
{
$query = $this->_db->getQuery(true);
$user = JFactory::getUser();
// Get possible filters
$filter_cat = $this->getState('filter.category');
$filter_state = $this->getState('filter.published');
$filter_author = $this->getState('filter.author');
$filter_search = $this->getState('filter.search');
// Select the required fields from the table.
$query->select($this->getState('list.select', 'a.id, a.asset_id, a.catid, a.title, a.alias, a.description, a.created, ' . 'a.created_by, a.modified, a.modified_by, a.checked_out, ' . 'a.checked_out_time, a.attribs, a.access, a.state, a.start_date, ' . 'a.end_date'));
$query->from('#__pf_projects AS a');
// Join over the users for the checked out user.
$query->select('uc.name AS editor')->join('LEFT', '#__users AS uc ON uc.id = a.checked_out');
// Join over the asset groups.
$query->select('ag.title AS access_level')->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access');
// Join over the users for the owner.
$query->select('ua.name AS author_name, ua.email AS author_email')->join('LEFT', '#__users AS ua ON ua.id = a.created_by');
// Join over the milestones for milestone count
$query->select('COUNT(DISTINCT ma.id) AS milestones')->join('LEFT', '#__pf_milestones AS ma ON ma.project_id = a.id');
// Join over the categories.
$query->select('c.title AS category_title')->join('LEFT', '#__categories AS c ON c.id = a.catid');
// Join over the task lists for list count
$query->select('COUNT(DISTINCT tl.id) AS tasklists')->join('LEFT', '#__pf_task_lists AS tl ON tl.project_id = a.id');
// Join over the observer table for email notification status
if ($user->get('id') > 0) {
$query->select('COUNT(DISTINCT obs.user_id) AS watching')->join('LEFT', '#__pf_ref_observer AS obs ON (obs.item_type = ' . $this->_db->quote('com_pfprojects.project') . ' AND obs.item_id = a.id AND obs.user_id = ' . $this->_db->quote($user->get('id')) . ')');
}
// Join over the comments for comment count
$query->select('COUNT(DISTINCT co.id) AS comments')->join('LEFT', '#__pf_comments AS co ON (co.context = ' . $this->_db->quote('com_pfprojects.project') . ' AND co.item_id = a.id)');
// Implement View Level Access
if (!$user->authorise('core.admin')) {
$levels = implode(',', $user->getAuthorisedViewLevels());
$query->where('a.access IN (' . $levels . ')');
}
// Filter by a single or group of categories.
$baselevel = 1;
if (is_numeric($filter_cat)) {
$filter_cat = (int) $filter_cat;
$cat_tbl = JTable::getInstance('Category', 'JTable');
if ($cat_tbl) {
if ($cat_tbl->load($filter_cat)) {
$rgt = $cat_tbl->rgt;
$lft = $cat_tbl->lft;
$baselevel = (int) $cat_tbl->level;
$query->where('c.lft >= ' . (int) $lft);
$query->where('c.rgt <= ' . (int) $rgt);
}
}
} elseif (is_array($filter_cat)) {
JArrayHelper::toInteger($filter_cat);
$filter_cat = implode(',', $filter_cat);
$query->where('a.catid IN (' . $filter_cat . ')');
}
// Filter fields
$filters = array();
$filters['a.state'] = array('STATE', $this->getState('filter.published'));
$filters['a.created_by'] = array('INT-NOTZERO', $this->getState('filter.author'));
$filters['a'] = array('SEARCH', $this->getState('filter.search'));
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Group by ID
$query->group('a.id');
// Add the list ordering clause.
$sort = $this->getState('list.ordering', 'category_title, a.title');
$dir = $this->getState('list.direction', 'ASC');
if (empty($sort)) {
$sort = 'category_title, a.title';
}
if (empty($dir)) {
$dir = 'ASC';
}
$query->order($sort . ' ' . $dir);
return $query;
}
/**
* Build a list of item albums
*
* @return jdatabasequery
*/
public function getAlbums()
{
// Return empty array if no project is select
$project = (int) $this->getState('filter.project');
if ($project <= 0) {
return array();
}
$db = $this->getDbo();
$query = $db->getQuery(true);
$user = JFactory::getUser();
$access = PFdesignsHelper::getAlbumActions();
// Construct the query
$query->select('c.id AS value, c.title AS text')->from('#__pf_design_albums AS c')->join('INNER', '#__pf_designs AS a ON a.album_id = c.id');
// Implement View Level Access
if (!$user->authorise('core.admin', 'com_pfdesigns')) {
$levels = implode(',', $user->getAuthorisedViewLevels());
$query->where('c.access IN (' . $levels . ')');
}
// Filter fields
$filters = array();
$filters['c.project_id'] = array('INT-NOTZERO', $this->getState('filter.project'));
if (!$access->get('core.edit.state') && !$access->get('core.edit')) {
$filters['c.state'] = array('STATE', '1');
}
// Apply Filter
PFQueryHelper::buildFilter($query, $filters);
// Group and order
$query->group('c.id');
$query->order('c.title ASC');
// Return the result
$db->setQuery((string) $query);
return (array) $db->loadObjectList();
}
