public function getFilteredDepartmentList($AppUI = null, $deptType = -1, $searchString = '', $ownerId = 0, $orderby = 'dept_name', $orderdir = 'ASC') { $orderby = in_array($orderby, array('dept_name', 'dept_type', 'countp', 'inactive')) ? $orderby : 'dept_name'; $q = $this->_getQuery(); $q->addTable('departments'); $q->addQuery('departments.*, COUNT(ct.contact_department) dept_users, count(distinct p.project_id) as countp, count(distinct p2.project_id) as inactive, con.contact_first_name, con.contact_last_name'); $q->addJoin('companies', 'c', 'c.company_id = departments.dept_company'); $q->addJoin('project_departments', 'pd', 'pd.department_id = dept_id'); $q->addJoin('projects', 'p', 'pd.project_id = p.project_id AND p.project_active = 1'); $q->leftJoin('users', 'u', 'dept_owner = u.user_id'); $q->leftJoin('contacts', 'con', 'u.user_contact = con.contact_id'); $q->addJoin('projects', 'p2', 'pd.project_id = p2.project_id AND p2.project_active = 0'); $q->addJoin('contacts', 'ct', 'ct.contact_department = dept_id'); $q->addGroup('dept_id'); $q->addOrder('dept_parent, dept_name'); $oCpy = new CCompany(); $oCpy->overrideDatabase($this->_query); $where = $oCpy->getAllowedSQL($this->_AppUI->user_id, 'c.company_id'); $q->addWhere($where); if ($deptType > -1) { $q->addWhere('dept_type = ' . (int) $deptType); } if ($searchString != '') { $q->addWhere("dept_name LIKE '%{$searchString}%'"); } if ($ownerId > 0) { $q->addWhere('dept_owner = ' . $ownerId); } $q->addGroup('dept_id'); $q->addOrder($orderby . ' ' . $orderdir); return $q->loadList(); }
/** * Calculate the extent of utilization of user assignments * @param string hash a hash for the returned hashList * @param array users an array of user_ids calculating their assignment capacity * @return array returns hashList of extent of utilization for assignment of the users */ public function getAllocation($hash = null, $users = null, $get_user_list = false) { /* * TODO: The core of this function has been simplified to always return 100% * free capacity available. The allocation checking (aka resource * management) is a complex subject which is currently not even close to be * handled properly. */ global $AppUI; if (!w2PgetConfig('check_overallocation', false)) { if ($get_user_list) { $users_list = w2PgetUsersHashList(); foreach ($users_list as $key => $user) { $users_list[$key]['userFC'] = $user['contact_name']; } $hash = $users_list; } else { $hash = array(); } } else { $q = new w2p_Database_Query(); // retrieve the systemwide default preference for the assignment maximum $q->addTable('user_preferences'); $q->addQuery('pref_value'); $q->addWhere('pref_user = 0 AND pref_name = \'' . TASKASSIGNMAX . '\''); $sysChargeMax = $q->loadHash(); $q->clear(); if (!$sysChargeMax) { $scm = 0; } else { $scm = $sysChargeMax['pref_value']; } /* * provide actual assignment charge, individual chargeMax * and freeCapacity of users' assignments to tasks */ $q->addTable('users', 'u'); $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact', 'inner'); $q->leftJoin('user_tasks', 'ut', 'ut.user_id = u.user_id'); $q->leftJoin('user_preferences', 'up', 'up.pref_user = u.user_id'); $q->addWhere("up.pref_name = 'TASKASSIGNMAX'"); $q->addQuery('u.user_id, CONCAT(CONCAT_WS(\' [\', CONCAT_WS(\' \', contact_first_name, contact_last_name), IF(IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value) > 0, IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value), 0)), \'%]\') AS userFC, IFNULL(SUM(ut.perc_assignment), 0) AS charge'); $q->addQuery('u.user_username, IFNULL(up.pref_value,' . $scm . ') AS chargeMax'); $q->addQuery('IFNULL(up.pref_value, ' . $scm . ') AS freeCapacity'); if (!empty($users)) { // use userlist if available otherwise pull data for all users $q->addWhere('u.user_id IN (' . implode(',', $users) . ')'); } $q->addGroup('u.user_id'); $q->addOrder('contact_first_name, contact_last_name'); // get CCompany() to filter by company $obj = new CCompany(); $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id'); $q->addJoin('companies', 'com', 'company_id = contact_company'); if ($companies) { $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)'); } $dpt = new CDepartment(); $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id'); $q->addJoin('departments', 'dep', 'dept_id = contact_department'); if ($depts) { $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)'); } $hash = $q->loadHashList($hash); $q->clear(); } return $hash; }
$q->leftJoin('companies', 'b', 'company_id = contact_company'); $q->leftJoin('departments', 'c', 'dept_id = contact_department'); $q->addQuery('contact_id, contact_first_name, contact_last_name, contact_company, contact_department'); $q->addQuery('company_name'); $q->addQuery('dept_name'); if ($where) { // Don't assume where is set. Change needed to fix Mantis Bug 0002056 $q->addWhere($where); } if ($where_dept) { // Don't assume where is set. Change needed to fix Mantis Bug 0002056 $q->addWhere($where_dept); } $oCpy = new CCompany(); $aCpies = $oCpy->getAllowedRecords($AppUI->user_id, 'company_id, company_name', 'company_name'); $where = $oCpy->getAllowedSQL($AppUI->user_id, 'contact_company'); $q->addWhere($where); $oDpt = new CDepartment(); $where = $oDpt->getAllowedSQL($AppUI->user_id, 'contact_department'); $q->addWhere($where); $q->addWhere('(contact_owner = ' . (int) $AppUI->user_id . ' OR contact_private = 0)'); $q->addOrder('company_name, contact_company, dept_name, contact_department, contact_last_name'); // May need to review this. $contacts = $q->loadHashList('contact_id'); ?> <form action="index.php?m=public&a=contact_selector&dialog=1&<?php if (!is_null($call_back)) { echo 'call_back=' . $call_back . '&'; } ?>
public function getAllowedSQL($uid, $index = null) { $oCpy = new CCompany(); $where = $oCpy->getAllowedSQL($uid, 'project_company'); $oDpt = new CDepartment(); $where += $oDpt->getAllowedSQL($uid, 'dept_id'); $project_where = parent::getAllowedSQL($uid, $index); return array_merge($where, $project_where); }
$canRead_folders = getPermission('file_folders', 'view'); $canEdit_folders = getPermission('file_folders', 'edit'); $canAuthor_folders = getPermission('file_folders', 'add'); $canDelete_folders = getPermission('file_folders', 'delete'); // load the following classes to retrieved denied records include_once $AppUI->getModuleClass('projects'); include_once $AppUI->getModuleClass('tasks'); if (!isset($project_id)) { $project_id = dPgetParam($_REQUEST, 'project_id', 0); } if (!$project_id) { $showProject = true; } global $allowedCompanies, $allowedProjects, $allowedTasks, $allowedFolders; $company = new CCompany(); $allowedCompanies = $company->getAllowedSQL($AppUI->user_id, 'co.company_id'); $project = new CProject(); $allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'f.file_project'); $task = new CTask(); $allowedTasks = $task->getAllowedSQL($AppUI->user_id, 'f.file_task'); $cfObj = new CFileFolder(); $allowedFolderIDs = $cfObj->getAllowedSQL($AppUI->user_id, 'ff.file_folder_id'); $allowedFolders = $cfObj->getAllowedSQL($AppUI->user_id, 'f.file_folder'); // $parent_id is the parent of the children we want to see // $level is increased when we go deeper into the tree, used to display a nice indented tree function displayFolders($folder_id = 0, $level = 0) { global $AppUI, $m, $a, $tab; global $current_uri; global $canAccess_folders, $canRead_folders, $canEdit_folders; global $canAuthor_folders, $canDelete_folders;
function getAllowedSQL($uid, $index = null) { $oCpy = new CCompany(); $where = $oCpy->getAllowedSQL($uid, 'project_company'); $project_where = parent::getAllowedSQL($uid, $index); return array_merge($where, $project_where); }
$q->leftJoin('users', 'u', 'u.user_contact=c.contact_id'); $q->addQuery('DISTINCT UPPER(SUBSTRING(' . $search_name . ',1,1)) as L, user_id'); $q->addWhere('contact_private = 0 OR (contact_private = 1 AND contact_owner = ' . $AppUI->user_id . ') OR contact_owner IS NULL OR contact_owner = 0'); $arr = $q->loadList(); foreach ($arr as $L) { if (!$L['user_id'] || $perms->checkLogin($L['user_id'])) { $let .= $L['L']; } } } $q->clear(); // optional fields shown in the list (could be modified to allow breif and verbose, etc) $showfields = array('contact_company' => 'contact_company', 'company_name' => 'company_name', 'contact_phone' => 'contact_phone', 'contact_email' => 'contact_email'); require_once $AppUI->getModuleClass('companies'); $company = new CCompany(); $allowedCompanies = $company->getAllowedSQL($AppUI->user_id); // assemble the sql statement $q->addTable('contacts', 'a'); $q->leftJoin('companies', 'b', 'a.contact_company = b.company_id'); $q->leftJoin('users', 'u', 'u.user_contact=a.contact_id'); $q->addQuery('contact_id, contact_order_by'); $q->addQuery('contact_first_name, contact_last_name, contact_phone, contact_owner'); $q->addQuery($showfields); $q->addQuery('user_id'); foreach ($search_map as $search_name) { $where_filter .= ' OR ' . $search_name . " LIKE '{$where}%'"; } $where_filter = mb_substr($where_filter, 4); $where_filter .= $additional_filter ? ' OR ' . $additional_filter : ''; $q->addWhere('(' . $where_filter . ')'); $q->addWhere('(contact_private = 0 OR (contact_private = 1 AND contact_owner = ' . $AppUI->user_id . ') OR contact_owner IS NULL OR contact_owner = 0)');
function w2PgetUsersHashList($stub = null, $where = null, $orderby = 'contact_first_name, contact_last_name') { global $AppUI; $q = new DBQuery(); $q->addTable('users'); $q->addQuery('DISTINCT(user_id), user_username, contact_last_name, contact_first_name, contact_email, company_name, contact_company, dept_id, dept_name, CONCAT(contact_first_name,\' \',contact_last_name) contact_name, user_type'); $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner'); if ($stub) { $q->addWhere('(UPPER(user_username) LIKE \'' . $stub . '%\' or UPPER(contact_first_name) LIKE \'' . $stub . '%\' OR UPPER(contact_last_name) LIKE \'' . $stub . '%\')'); } elseif ($where) { $where = $q->quote('%' . $where . '%'); $q->addWhere('(UPPER(user_username) LIKE ' . $where . ' OR UPPER(contact_first_name) LIKE ' . $where . ' OR UPPER(contact_last_name) LIKE ' . $where . ')'); } $q->addGroup('user_id'); $q->addOrder($orderby); // get CCompany() to filter by company $obj = new CCompany(); $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id'); $q->addJoin('companies', 'com', 'company_id = contact_company'); if ($companies) { $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)'); } $dpt = new CDepartment(); $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id'); $q->addJoin('departments', 'dep', 'dept_id = contact_department'); if ($depts) { $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)'); } return $q->loadHashList('user_id'); }
public function getFilteredDepartmentList($notUsed = null, $deptType = -1, $searchString = '', $ownerId = 0, $orderby = 'dept_name', $orderdir = 'ASC') { $q = $this->_getQuery(); $q->addTable('departments'); $q->addQuery('departments.*, count(distinct p.project_id) as countp, count(distinct p2.project_id) as inactive'); $q->addJoin('project_departments', 'pd', 'pd.department_id = dept_id'); $q->addJoin('projects', 'p', 'pd.project_id = p.project_id AND p.project_active = 1'); $q->addJoin('projects', 'p2', 'pd.project_id = p2.project_id AND p2.project_active = 0'); $q->addGroup('dept_id'); $q->addOrder('dept_name'); $oCpy = new CCompany(); $oCpy->overrideDatabase($this->_query); $where = $oCpy->getAllowedSQL($this->_AppUI->user_id, 'dept_company'); $q->addWhere($where); $q = $this->setAllowedSQL($this->_AppUI->user_id, $q); if ($deptType > -1) { $q->addWhere('dept_type = ' . (int) $deptType); } if ($searchString != '') { $q->addWhere("dept_name LIKE '%{$searchString}%'"); } if ($ownerId > 0) { $q->addWhere('dept_owner = ' . $ownerId); } $q->addGroup('dept_id'); $orderby = property_exists($this, $orderby) || in_array($orderby, array('countp', 'inactive')) ? $orderby : 'dept_name'; $q->addOrder($orderby . ' ' . $orderdir); return $q->loadList(); }
public function search($search, $days = 0) { $hook = $this->hook_search(); $searchfields = $hook['search_fields']; foreach ($searchfields as $key => $field) { $searchfields[$key] = "{$field} like '%{$search}%'"; } $where = implode(' OR ', $searchfields); $q = $this->_getQuery(); $q->addQuery('distinct(c.contact_id)'); $q->addQuery('c.*'); $q->addQuery('user_id'); $q->addTable('contacts', 'c'); $q->leftJoin('companies', 'b', 'c.contact_company = b.company_id'); $q->leftJoin('departments', '', 'c.contact_department = dept_id'); $q->leftJoin('users', '', 'contact_id = user_contact'); $q->leftJoin('contacts_methods', 'cm', 'c.contact_id = cm.contact_id'); $q->addWhere('(' . $where . ')'); if ($days) { $q->addWhere('contact_lastupdate >= ' . $q->dbfnDateAdd($q->dbfnNow(), -$days, 'DAY')); } $q->addWhere(' (contact_private=0 OR (contact_private=1 AND contact_owner=' . $this->_AppUI->user_id . ') OR contact_owner IS NULL OR contact_owner = 0 )'); //TODO: We need to convert this from static to use ->overrideDatabase() for testing. $company = new CCompany(); $allow_where = $company->getAllowedSQL($this->_AppUI->user_id, 'contact_company'); if (count($allow_where)) { $q->addWhere('(contact_company = 0 OR contact_company IS NULL OR (' . implode(' AND ', $allow_where) . '))'); } //TODO: We need to convert this from static to use ->overrideDatabase() for testing. $department = new CDepartment(); $q = $department->setAllowedSQL($this->_AppUI->user_id, $q); $q->addOrder('contact_first_name'); $q->addOrder('contact_last_name'); return $q->loadList(); }