Пример #1
0
 /**
  * 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;
 }
Пример #2
0
 /**
  * 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;
 }
Пример #3
0
 /**
  * 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;
 }
Пример #4
0
 /**
  * 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;
 }
Пример #5
0
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);
    }
}
Пример #6
0
 /**
  * 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;
 }
Пример #7
0
 /**
  * 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();
 }