function projects_list_data($user_id = false) { global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $tasks_problems, $owner, $projectTypeId, $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'); $tasks_users = $q->exec(); $q->clear(); } // add Projects where the Project Owner is in the given department if ($addPwOiD && isset($department)) { $owner_ids = array(); $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_id, project_status, project_color_identifier, project_type, project_name, project_description, project_scheduled_hours as project_duration, project_parent, project_original_parent, project_percent_complete, project_color_identifier, project_company, company_name, project_status, project_last_task as critical_task, tp.task_log_problem, user_username, project_active'); $fields = w2p_Core_Module::getSettings('projects', 'index_list'); unset($fields['department_list']); // added as an alias below foreach ($fields as $field => $text) { $q->addQuery($field); } $q->addQuery('CONCAT(ct.contact_first_name, \' \', ct.contact_last_name) AS owner_name'); $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 && !$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(); $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'); $obj->setAllowedSQL($AppUI->user_id, $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; }