public function setAllowedSQL($uid, &$query, $index = null, $key = 'pr') { $oCpy = new CCompany(); parent::setAllowedSQL($uid, $query, $index, $key); $oCpy->setAllowedSQL($uid, $query, ($key ? $key . '.' : '') . 'project_company'); //Department permissions $oDpt = new CDepartment(); $query->leftJoin('project_departments', '', $key . '.project_id = project_departments.project_id'); $oDpt->setAllowedSQL($uid, $query, 'project_departments.department_id'); }
public static function searchContacts(CAppUI $AppUI = null, $where = '', $searchString = '') { global $AppUI; $showfields = array('contact_address1' => 'contact_address1', 'contact_address2' => 'contact_address2', 'contact_city' => 'contact_city', 'contact_state' => 'contact_state', 'contact_zip' => 'contact_zip', 'contact_country' => 'contact_country', 'contact_company' => 'contact_company', 'company_name' => 'company_name', 'dept_name' => 'dept_name'); $additional_filter = ''; if ($searchString != '') { $additional_filter = "OR contact_first_name like '%{$searchString}%'\n OR contact_last_name like '%{$searchString}%'\n\t\t\t OR CONCAT(contact_first_name, ' ', contact_last_name) like '%{$searchString}%'\n OR company_name like '%{$searchString}%'\n OR contact_notes like '%{$searchString}%'"; } // assemble the sql statement $q = new w2p_Database_Query(); $q->addQuery('contact_id, contact_order_by'); $q->addQuery($showfields); $q->addQuery('contact_first_name, contact_last_name, contact_title'); $q->addQuery('contact_updatekey, contact_updateasked, contact_lastupdate'); $q->addQuery('contact_email, contact_phone'); $q->addQuery('user_id'); $q->addTable('contacts', 'a'); $q->leftJoin('companies', 'b', 'a.contact_company = b.company_id'); $q->leftJoin('departments', '', 'contact_department = dept_id'); $q->leftJoin('users', '', 'contact_id = user_contact'); $q->addWhere("(contact_first_name LIKE '{$where}%' OR contact_last_name LIKE '{$where}%' " . $additional_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 )'); $company = new CCompany(); $company->setAllowedSQL($AppUI->user_id, $q); $department = new CDepartment(); $department->setAllowedSQL($AppUI->user_id, $q); $q->addOrder('contact_first_name'); $q->addOrder('contact_last_name'); return $q->loadList(); }
function setAllowedSQL($uid, &$query, $index = null, $key = null) { $oCpy = new CCompany(); parent::setAllowedSQL($uid, $query, $index, $key); $oCpy->setAllowedSQL($uid, $query, ($key ? $key . '.' : '') . 'project_company'); }
function getStructuredProjects($original_project_id = 0, $project_status = -1, $active_only = false) { global $AppUI, $st_projects_arr; $st_projects = array(0 => ''); $q = new w2p_Database_Query(); $q->addTable('projects'); $q->addJoin('companies', '', 'projects.project_company = company_id', 'inner'); $q->addQuery('DISTINCT(projects.project_id), project_name, project_parent'); if ($original_project_id) { $q->addWhere('project_original_parent = ' . (int) $original_project_id); } if ($project_status >= 0) { $q->addWhere('project_status = ' . (int) $project_status); } if ($active_only) { $q->addWhere('project_active = 1'); } $q->addOrder('project_start_date, project_end_date'); $obj = new CCompany(); $obj->setAllowedSQL($AppUI->user_id, $q); $dpt = new CDepartment(); $dpt->setAllowedSQL($AppUI->user_id, $q); $q->leftJoin('project_departments', 'pd', 'pd.project_id = projects.project_id'); $q->leftJoin('departments', 'd', 'd.dept_id = pd.department_id'); $st_projects = $q->loadList(); $tnums = count($st_projects); for ($i = 0; $i < $tnums; $i++) { $st_project = $st_projects[$i]; if ($st_project['project_parent'] == $st_project['project_id']) { show_st_project($st_project); find_proj_child($st_projects, $st_project['project_id']); } } }
} if (isset($department)) { $q->addWhere("pd.department_id in ( " . implode(',', $dept_ids) . " )"); } if (strlen($alias_string) >= 1) { $q->addWhere("projects.project_short_name LIKE '%" . $alias_string . "%'"); } if ($fcliente != 'no') { $q->addWhere("projects.project_client = {$fcliente}"); } if ($fmoldista != 'no') { $q->addWhere("projects.project_mold = {$fmoldista}"); } $q->addGroup('projects.project_id'); $q->addOrder("{$orderby} {$orderdir}"); $obj->setAllowedSQL($AppUI->user_id, $q); $projects = $q->loadList(); // get the list of permitted companies $companies = arrayMerge(array('0' => $AppUI->_('All')), $companies); //get list of all departments, filtered by the list of permitted companies. $q->clear(); $q->addTable('companies'); $q->addQuery('company_id, company_name, dep.*'); $q->addJoin('departments', 'dep', 'companies.company_id = dep.dept_company'); $q->addOrder('company_name,dept_parent,dept_name'); $q->addWhere('company_type in (0,1)'); $obj->setAllowedSQL($AppUI->user_id, $q); $rows = $q->loadList(); //display the select list $buffer = '<select name="department" class="text">'; $buffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
function setAllowedSQL($uid, &$query, $index = null) { $oCpy = new CCompany(); parent::setAllowedSQL($uid, $query, $index); $oCpy->setAllowedSQL($uid, $query, "project_company"); }
function projects_list_data($user_id = false) { global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $orderby, $orderdir, $tasks_problems, $owner, $search_text, $project_type; $addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0; // get any records denied from viewing $obj = new CProject(); $deny = $obj->getDeniedRecords($AppUI->user_id); // Let's delete temproary tables $q = new w2p_Database_Query(); $q->setDelete('tasks_problems'); $q->exec(); $q->clear(); $q->setDelete('tasks_users'); $q->exec(); $q->clear(); // support task problem logs $q->addInsertSelect('tasks_problems'); $q->addTable('tasks'); $q->addQuery('task_project, task_log_problem'); $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id', 'inner'); $q->addWhere('task_log_problem = 1'); $q->addGroup('task_project'); $tasks_problems = $q->exec(); $q->clear(); if ($addProjectsWithAssignedTasks) { // support users tasks $q->addInsertSelect('tasks_users'); $q->addTable('tasks'); $q->addQuery('task_project'); $q->addQuery('ut.user_id'); $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); if ($user_id) { $q->addWhere('ut.user_id = ' . (int) $user_id); } $q->addOrder('task_end_date DESC'); $q->addGroup('task_project'); $q->exec(); $q->clear(); } // add Projects where the Project Owner is in the given department if ($addPwOiD && isset($department)) { $q->addTable('users'); $q->addQuery('user_id'); $q->addJoin('contacts', 'c', 'c.contact_id = user_contact', 'inner'); $q->addWhere('c.contact_department = ' . (int) $department); $owner_ids = $q->loadColumn(); $q->clear(); } if (isset($department)) { //If a department is specified, we want to display projects from the department, and all departments under that, so we need to build that list of departments $dept_ids = array(); $q->addTable('departments'); $q->addQuery('dept_id, dept_parent'); $q->addOrder('dept_parent,dept_name'); $rows = $q->loadList(); addDeptId($rows, $department); $dept_ids[] = isset($department->dept_id) ? $department->dept_id : 0; $dept_ids[] = $department > 0 ? $department : 0; } $q->clear(); // retrieve list of records // modified for speed // by Pablo Roca (pabloroca@mvps.org) // 16 August 2003 // get the list of permitted companies $obj = new CCompany(); $companies = $obj->getAllowedRecords($AppUI->user_id, 'companies.company_id,companies.company_name', 'companies.company_name'); if (count($companies) == 0) { $companies = array(); } $q->addTable('projects', 'pr'); $q->addQuery('pr.*, project_scheduled_hours as project_duration, project_actual_end_date as project_end_actual, company_id, company_name, project_last_task as critical_task, tp.task_log_problem, user_username, task_log_problem, u.user_id'); $fields = w2p_System_Module::getSettings('projects', 'index_list'); unset($fields['department_list']); // added as an alias below foreach ($fields as $field => $notUsed) { $q->addQuery($field); } $q->addQuery('ct.contact_display_name AS owner_name'); $q->addJoin('companies', 'c', 'c.company_id = pr.project_company'); $q->addJoin('users', 'u', 'pr.project_owner = u.user_id'); $q->addJoin('contacts', 'ct', 'ct.contact_id = u.user_contact'); $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project'); if ($addProjectsWithAssignedTasks) { $q->addJoin('tasks_users', 'tu', 'pr.project_id = tu.task_project'); } if (!isset($department) && $company_id > 0 && !$addPwOiD) { $q->addWhere('pr.project_company = ' . (int) $company_id); } if ($project_type > -1) { $q->addWhere('pr.project_type = ' . (int) $project_type); } if (isset($department) && !$addPwOiD) { $q->addWhere('project_departments.department_id in ( ' . implode(',', $dept_ids) . ' )'); } if ($user_id && $addProjectsWithAssignedTasks) { $q->addWhere('(tu.user_id = ' . (int) $user_id . ' OR pr.project_owner = ' . (int) $user_id . ' )'); } elseif ($user_id) { $q->addWhere('pr.project_owner = ' . (int) $user_id); } if ($owner > 0) { $q->addWhere('pr.project_owner = ' . (int) $owner); } if (mb_trim($search_text)) { $q->addWhere('pr.project_name LIKE \'%' . $search_text . '%\' OR pr.project_description LIKE \'%' . $search_text . '%\''); } // Show Projects where the Project Owner is in the given department if ($addPwOiD && !empty($owner_ids)) { $q->addWhere('pr.project_owner IN (' . implode(',', $owner_ids) . ')'); } $orderby = 'project_company' == $orderby ? 'company_name' : $orderby; $q->addGroup('pr.project_id'); $q->addOrder($orderby . ' ' . $orderdir); $prj = new CProject(); $q = $prj->setAllowedSQL($AppUI->user_id, $q, null, 'pr'); $dpt = new CDepartment(); $projects = $q->loadList(); // get the list of permitted companies $companies = arrayMerge(array('0' => $AppUI->_('All')), $companies); $company_array = $companies; //get list of all departments, filtered by the list of permitted companies. $q->clear(); $q->addTable('companies'); $q->addQuery('company_id, company_name, dep.*'); $q->addJoin('departments', 'dep', 'companies.company_id = dep.dept_company'); $q->addOrder('company_name,dept_parent,dept_name'); $q = $obj->setAllowedSQL($AppUI->user_id, $q); $q = $dpt->setAllowedSQL($AppUI->user_id, $q); $rows = $q->loadList(); //display the select list $buffer = '<select name="department" id="department" onChange="document.pickCompany.submit()" class="text" style="width: 200px;">'; $company = ''; foreach ($company_array as $key => $c_name) { $buffer .= '<option value="' . $company_prefix . $key . '" style="font-weight:bold;"' . ($company_id == $key ? 'selected="selected"' : '') . '>' . $c_name . '</option>' . "\n"; foreach ($rows as $row) { if ($row['dept_parent'] == 0) { if ($key == $row['company_id']) { if ($row['dept_parent'] != null) { findchilddept($rows, $row['dept_id']); } } } } } $buffer .= '</select>'; return $projects; }