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();
}
