function fetchResults(&$permissions) { global $AppUI; $sql = $this->_buildQuery(); $results = db_loadList($sql); $outstring = "<th nowrap='nowrap' STYLE='background: #08245b' >" . $AppUI->_('Projects') . "</th>\n"; require_once $AppUI->getModuleClass("projects"); if ($results) { foreach ($results as $records) { if ($permissions->checkModuleItem($this->table, "view", $records["project_id"])) { $obj = new CProject(); if (!in_array($records["project_id"], $obj->getDeniedRecords($AppUI->user_id))) { $outstring .= "<tr>"; $outstring .= "<td>"; $outstring .= "<a href = \"index.php?m=projects&a=view&project_id=" . $records["project_id"] . "\">" . $records["project_name"] . "</a>\n"; $outstring .= "</td>\n"; } } } $outstring .= "</tr>"; } else { $outstring .= "<tr>" . "<td>" . $AppUI->_('Empty') . "</td>" . "</tr>"; } return $outstring; }
public function getAllTasksForPeriod($start_date, $end_date, $company_id = 0, $user_id = null) { global $AppUI; $q = new w2p_Database_Query(); // convert to default db time stamp $db_start = $start_date->format(FMT_DATETIME_MYSQL); $db_end = $end_date->format(FMT_DATETIME_MYSQL); // Allow for possible passing of user_id 0 to stop user filtering if (!isset($user_id)) { $user_id = $AppUI->user_id; } // check permissions on projects $proj = new CProject(); $task_filter_where = $proj->getAllowedSQL($AppUI->user_id, 't.task_project'); // exclude read denied projects $deny = $proj->getDeniedRecords($AppUI->user_id); // check permissions on tasks $obj = new CTask(); $allow = $obj->getAllowedSQL($AppUI->user_id, 't.task_id'); $q->addTable('tasks', 't'); if ($user_id) { $q->innerJoin('user_tasks', 'ut', 't.task_id=ut.task_id'); } $q->innerJoin('projects', 'projects', 't.task_project = projects.project_id'); $q->innerJoin('companies', 'companies', 'projects.project_company = companies.company_id'); $q->leftJoin('project_departments', '', 'projects.project_id = project_departments.project_id'); $q->leftJoin('departments', '', 'departments.dept_id = project_departments.department_id'); $q->addQuery('DISTINCT t.task_id, t.task_name, t.task_start_date, t.task_end_date, t.task_percent_complete, t.task_duration' . ', t.task_duration_type, projects.project_color_identifier AS color, projects.project_name, t.task_milestone, task_description, task_type, company_name, task_access, task_owner'); $q->addWhere('task_status > -1' . ' AND (task_start_date <= \'' . $db_end . '\' AND t.task_percent_complete<100 OR task_end_date = \'0000-00-00 00:00:00\' OR task_end_date = NULL )'); $q->addWhere('project_active = 1'); if (($template_status = w2PgetConfig('template_projects_status_id')) != '') { $q->addWhere('project_status <> ' . $template_status); } if ($user_id) { $q->addWhere('ut.user_id = ' . (int) $user_id); } if ($company_id) { $q->addWhere('projects.project_company = ' . (int) $company_id); } if (count($task_filter_where) > 0) { $q->addWhere('(' . implode(' AND ', $task_filter_where) . ')'); } if (count($deny) > 0) { $q->addWhere('(t.task_project NOT IN (' . implode(', ', $deny) . '))'); } if (count($allow) > 0) { $q->addWhere('(' . implode(' AND ', $allow) . ')'); } $q->addOrder('t.task_start_date'); // assemble query $tasks = $q->loadList(-1, 'task_id'); // check tasks access $result = array(); foreach ($tasks as $key => $row) { $obj->load($row['task_id']); $canAccess = $obj->canAccess(); if (!$canAccess) { continue; } $result[$key] = $row; } // execute and return return $result; }
$canRead = $perms->checkModuleItem($m, 'view', $project_id); $canEdit = $perms->checkModuleItem($m, 'edit', $project_id); $canEditT = $perms->checkModule('tasks', 'add'); if (!$canRead) { $AppUI->redirect("m=public&a=access_denied"); } // retrieve any state parameters if (isset($_GET['tab'])) { $AppUI->setState('ProjVwTab', $_GET['tab']); } $tab = $AppUI->getState('ProjVwTab') !== NULL ? $AppUI->getState('ProjVwTab') : 0; // check if this record has dependencies to prevent deletion $msg = ''; $obj = new CProject(); // Now check if the proect is editable/viewable. $denied = $obj->getDeniedRecords($AppUI->user_id); if (in_array($project_id, $denied)) { $AppUI->redirect("m=public&a=access_denied"); } $canDelete = $obj->canDelete($msg, $project_id); // get critical tasks (criteria: task_end_date) $criticalTasks = $project_id > 0 ? $obj->getCriticalTasks($project_id) : NULL; // get ProjectPriority from sysvals $projectPriority = dPgetSysVal('ProjectPriority'); $projectPriorityColor = dPgetSysVal('ProjectPriorityColor'); $working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8; $q = new DBQuery(); //check that project has tasks; otherwise run seperate query $q->addTable('tasks'); $q->addQuery("COUNT(distinct tasks.task_id) AS total_tasks"); $q->addWhere('task_project = ' . $project_id);
function canAccess($user_id) { $q = new DBQuery(); //check whether we are explicitly denied at task level $denied_tasks = $this->getDeniedRecords($user_id); if (in_array($this->task_id, $denied_tasks)) { return false; } switch ($this->task_access) { case 0: //public $retval = true; $proj_obj = new CProject(); $denied_projects = $proj_obj->getDeniedRecords($user_id); if (in_array($this->task_project, $denied_projects)) { $retval = false; } break; case 1: //protected $q->addTable('users', 'u'); $q->innerJoin('contacts', 'c', 'c.contact_id=u.user_contact'); $q->addQuery('c.contact_company'); $q->addWhere('u.user_id=' . $user_id . ' OR u.user_id=' . $this->task_owner); $sql = $q->prepare(); $q->clear(); $user_owner_companies = db_loadColumn($sql); $company_match = true; foreach ($user_owner_companies as $current_company) { $company_match = $company_match && (!isset($last_company) || $last_company == $current_company); $last_company = $current_company; } case 2: //participant $company_match = isset($company_match) ? $company_match : true; $q->addTable('user_tasks', 'ut'); $q->addQuery('COUNT(*)'); $q->addWhere('ut.user_id=' . $user_id . ' AND ut.task_id=' . $this->task_id); $sql = $q->prepare(); $q->clear(); $count = db_loadResult($sql); $retval = $company_match && $count > 0 || $this->task_owner == $user_id; break; case 3: //private $retval = $this->task_owner == $user_id; break; case 4: //privileged $retval = true; if ($this->task_project != '') { $q->clear(); $q->addTable('users', 'u'); $q->innerJoin('contacts', 'c', 'c.contact_id=u.user_contact'); $q->addQuery('c.contact_company'); $q->addWhere('u.user_id = ' . $user_id); $user_company = $q->loadResult(); $q->clear(); $q->addTable('projects', 'p'); $q->addQuery('p.project_company'); $q->addWhere('p.project_id = ' . $this->task_project); $project_company = $q->loadResult(); $q->clear(); $q->addTable('user_tasks', 'ut'); $q->addQuery('COUNT(ut.user_id) AS user_task_count'); $q->addWhere('ut.user_id = ' . $user_id . ' AND ut.task_id = ' . $this->task_id); $count = $q->loadResult(); $q->clear(); $retval = $user_company == $project_company || $this->task_owner == $user_id || $count; } break; default: $retval = false; break; } return $retval; }
} $obj = new CCompany(); $allowed_companies_ary = $obj->getAllowedRecords($AppUI->user_id, 'company_id,company_name', 'company_name'); $allowed_companies = implode(",", array_keys($allowed_companies_ary)); if (!isset($task_id)) { $task_id = dPgetParam($_REQUEST, 'task_id', 0); } global $xpg_min, $xpg_pagesize; $xpg_pagesize = 30; $xpg_min = $xpg_pagesize * ($page - 1); // This is where we start our record set from // load the following classes to retrieved denied records include_once $AppUI->getModuleClass('projects'); include_once $AppUI->getModuleClass('tasks'); $project = new CProject(); $deny1 = $project->getDeniedRecords($AppUI->user_id); $task = new CTask(); $deny2 = $task->getDeniedRecords($AppUI->user_id); global $file_types; $file_types = dPgetSysVal("FileType"); $folder = $folder ? $folder : 0; // SQL text for count the total recs from the selected option $q = new DBQuery(); $q->addTable('files'); $q->addQuery('count(files.file_id)'); $q->addJoin('projects', 'p', 'p.project_id = file_project'); $q->addJoin('users', 'u', 'u.user_id = file_owner'); $q->addJoin('tasks', 't', 't.task_id = file_task'); $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder'); $q->addWhere('file_folder = ' . $folder); if (count($deny1) > 0) {
public function getFileCountByFolder($notUsed = null, $folder_id, $task_id, $project_id, $company_id, $allowed_companies) { // SQL text for count the total recs from the selected option $q = $this->_getQuery(); $q->addTable('files'); $q->addQuery('count(files.file_id)'); $q->addJoin('projects', 'p', 'p.project_id = file_project'); $q->addJoin('users', 'u', 'u.user_id = file_owner'); $q->addJoin('tasks', 't', 't.task_id = file_task'); $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder'); $q->addWhere('file_folder = ' . (int) $folder_id); //TODO: apply permissions properly $project = new CProject(); $project->overrideDatabase($this->_query); $deny1 = $project->getDeniedRecords($this->_AppUI->user_id); if (count($deny1) > 0) { $q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')'); } //TODO: apply permissions properly $task = new CTask(); $task->overrideDatabase($this->_query); $deny2 = $task->getDeniedRecords($this->_AppUI->user_id); if (count($deny2) > 0) { $q->addWhere('file_task NOT IN (' . implode(',', $deny2) . ')'); } if ($project_id) { $q->addWhere('file_project = ' . (int) $project_id); } if ($task_id) { $q->addWhere('file_task = ' . (int) $task_id); } if ($company_id) { $q->innerJoin('companies', 'co', 'co.company_id = p.project_company'); $q->addWhere('company_id = ' . (int) $company_id); $q->addWhere('company_id IN (' . $allowed_companies . ')'); } $q->addGroup('file_folder_name'); $q->addGroup('project_name'); $q->addGroup('file_name'); // counts total recs from selection return count($q->loadList()); }
} ?> </table> <?php $actions = array(); $actions['c'] = $AppUI->_('Copy', UI_OUTPUT_JS); if ($canEdit) { $actions['m'] = $AppUI->_('Move', UI_OUTPUT_JS); $actions['d'] = $AppUI->_('Delete', UI_OUTPUT_JS); $actions['f'] = $AppUI->_('Mark as Finished', UI_OUTPUT_JS); foreach ($priorities as $k => $v) { $actions[$k] = $AppUI->_('set priority to ' . $v, UI_OUTPUT_JS); } } $deny = $proj->getDeniedRecords($AppUI->user_id); $sql = 'SELECT p.project_id, p.project_name FROM projects AS p'; if ($deny) { $sql .= ' WHERE p.project_id NOT IN (' . implode(',', $deny) . ')'; } $sql .= ' ORDER BY p.project_name'; $projects = db_loadHashList($sql, 'project_id'); $p[0] = $AppUI->_('[none]'); foreach ($projects as $proj) { $p[$proj[0]] = $proj[1]; } if ($project_id) { $p[$project_id] = $AppUI->_('[same project]'); } natsort($p); $projects = $p;
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; }
function projects_list_data($user_id = false) { global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems, $tasks_sum, $tasks_summy, $tasks_total, $owner; $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 DBQuery(); $q->dropTemp('tasks_sum, tasks_total, tasks_summy, tasks_critical, tasks_problems, tasks_users'); $q->exec(); $q->clear(); // Task sum table // by Pablo Roca (pabloroca@mvps.org) // 16 August 2003 $working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8; // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours $q->createTemp('tasks_sum'); $q->addTable('tasks'); $q->addQuery("task_project, SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, {$working_hours}," . " task_duration_type)) / SUM(task_duration * IF(task_duration_type = 24, {$working_hours}," . " task_duration_type)) AS project_percent_complete, SUM(task_duration * IF(task_duration_type = 24," . " {$working_hours}, task_duration_type)) AS project_duration"); if ($user_id) { $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); $q->addWhere('ut.user_id = ' . $user_id); } $q->addWhere("tasks.task_id = tasks.task_parent"); $q->addGroup('task_project'); $tasks_sum = $q->exec(); $q->clear(); // Task total table $q->createTemp('tasks_total'); $q->addTable('tasks'); $q->addQuery("task_project, COUNT(distinct tasks.task_id) AS total_tasks"); if ($user_id) { $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); $q->addWhere('ut.user_id = ' . $user_id); } $q->addGroup('task_project'); $tasks_total = $q->exec(); $q->clear(); // temporary My Tasks // by Pablo Roca (pabloroca@mvps.org) // 16 August 2003 $q->createTemp('tasks_summy'); $q->addTable('tasks'); $q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks'); if ($user_id) { $q->addWhere('task_owner = ' . $user_id); } else { $q->addWhere('task_owner = ' . $AppUI->user_id); } $q->addGroup('task_project'); $tasks_summy = $q->exec(); $q->clear(); // temporary critical tasks $q->createTemp('tasks_critical'); $q->addTable('tasks'); $q->addQuery('task_project, task_id AS critical_task, MAX(task_end_date) AS project_actual_end_date'); $q->addJoin('projects', 'p', 'p.project_id = task_project'); $q->addOrder("task_end_date DESC"); $q->addGroup('task_project'); $tasks_critical = $q->exec(); $q->clear(); // temporary task problem logs $q->createTemp('tasks_problems'); $q->addTable('tasks'); $q->addQuery('task_project, task_log_problem'); $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id'); $q->addWhere("task_log_problem > '0'"); $q->addGroup('task_project'); $tasks_problems = $q->exec(); $q->clear(); if ($addProjectsWithAssignedTasks) { // temporary users tasks $q->createTemp('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 = ' . $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'); $q->addWhere('c.contact_department = ' . $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[] = $department; } $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, 'company_id,company_name', 'company_name'); if (count($companies) == 0) { $companies = array(0); } $q->addTable('projects'); $q->addQuery('projects.project_id, project_status, project_color_identifier, project_name, project_description, project_duration, project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status, project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, tt.total_tasks, tsy.my_tasks, ts.project_percent_complete, user_username'); $q->addJoin('companies', 'com', 'projects.project_company = company_id'); $q->addJoin('users', 'u', 'projects.project_owner = u.user_id'); $q->addJoin('tasks_critical', 'tc', 'projects.project_id = tc.task_project'); $q->addJoin('tasks_problems', 'tp', 'projects.project_id = tp.task_project'); $q->addJoin('tasks_sum', 'ts', 'projects.project_id = ts.task_project'); $q->addJoin('tasks_total', 'tt', 'projects.project_id = tt.task_project'); $q->addJoin('tasks_summy', 'tsy', 'projects.project_id = tsy.task_project'); if ($addProjectsWithAssignedTasks) { $q->addJoin('tasks_users', 'tu', 'projects.project_id = tu.task_project'); } // DO we have to include the above DENY WHERE restriction, too? //$q->addJoin('', '', ''); if (isset($department)) { $q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id'); } if (!isset($department) && $company_id && !$addPwOiD) { $q->addWhere("projects.project_company = '{$company_id}'"); } if (isset($department) && !$addPwOiD) { $q->addWhere("pd.department_id in ( " . implode(',', $dept_ids) . " )"); } if ($user_id && $addProjectsWithAssignedTasks) { $q->addWhere('(tu.user_id = ' . $user_id . ' OR projects.project_owner = ' . $user_id . ' )'); } elseif ($user_id) { $q->addWhere('projects.project_owner = ' . $user_id); } if ($owner > 0) { $q->addWhere('projects.project_owner = ' . $owner); } // Show Projects where the Project Owner is in the given department if ($addPwOiD && !empty($owner_ids)) { $q->addWhere('projects.project_owner IN (' . implode(',', $owner_ids) . ')'); } $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'); $obj->setAllowedSQL($AppUI->user_id, $q); $rows = $q->loadList(); //display the select list $buffer = '<select name="department" onChange="document.pickCompany.submit()" class="text">'; $buffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n"; $company = ''; foreach ($rows as $row) { if ($row["dept_parent"] == 0) { if ($company != $row['company_id']) { $buffer .= '<option value="' . $company_prefix . $row['company_id'] . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $row['company_name'] . '</option>' . "\n"; $company = $row['company_id']; } if ($row["dept_parent"] != null) { showchilddept($row); findchilddept($rows, $row["dept_id"]); } } } $buffer .= '</select>'; }
## ## Companies: View Archived Projects sub-table ## global $AppUI, $company_id; $q = new DBQuery(); $q->addTable('projects'); $q->addQuery('project_id, project_name, project_start_date, project_status, project_target_budget, project_start_date, project_priority, contact_first_name, contact_last_name'); $q->addJoin('users', 'u', 'u.user_id = projects.project_owner'); $q->addJoin('contacts', 'con', 'u.user_contact = con.contact_id'); $q->addWhere('projects.project_company = ' . $company_id); include_once $AppUI->getModuleClass('projects'); $projObj = new CProject(); $projList = $projObj->getDeniedRecords($AppUI->user_id); if (count($projList)) { $q->addWhere('NOT (project_id IN (' . implode(',', $projList) . ') )'); } $q->addWhere('projects.project_status = 7'); $q->addOrder('project_name'); $s = ''; if (!($rows = $q->loadList())) { $s .= $AppUI->_('No data available') . '<br />' . $AppUI->getMsg(); } else { $s .= '<tr>' . '<th>' . $AppUI->_('Name') . '</td>' . '<th>' . $AppUI->_('Owner') . '</td>' . '</tr>'; foreach ($rows as $row) { $s .= '<tr><td>'; $s .= '<a href="?m=projects&a=view&project_id=' . $row["project_id"] . '">' . $row["project_name"] . '</a>'; $s .= '<td>' . $row["contact_first_name"] . ' ' . $row["contact_last_name"] . '</td>'; $s .= '</tr>';
/** * @param Date Start date of the period * @param Date End date of the period * @param integer The target company */ function getTasksForPeriod($start_date, $end_date, $company_id = 0) { global $AppUI; // convert to default db time stamp $db_start = $start_date->format(FMT_DATETIME_MYSQL); $db_end = $end_date->format(FMT_DATETIME_MYSQL); // filter tasks for not allowed projects $tasks_filter = ''; $proj =& new CProject(); $task_filter_where = $proj->getAllowedSQL($AppUI->user_id, 'task_project'); if (count($task_filter_where)) { $tasks_filter = ' AND (' . implode(' AND ', $task_filter_where) . ")"; } // assemble where clause $where = "task_project = project_id" . "\n\tAND (" . "\n\t\t(task_start_date <= '{$db_end}' AND task_end_date >= '{$db_start}')" . "\n\t\tOR task_start_date BETWEEN '{$db_start}' AND '{$db_end}'" . "\n\t)" . "\n\t{$tasks_filter}"; /* OR task_end_date BETWEEN '$db_start' AND '$db_end' OR (DATE_ADD(task_start_date, INTERVAL task_duration HOUR)) BETWEEN '$db_start' AND '$db_end' OR (DATE_ADD(task_start_date, INTERVAL task_duration DAY)) BETWEEN '$db_start' AND '$db_end' */ $where .= $company_id ? "\n\tAND project_company = '{$company_id}'" : ''; // exclude read denied projects $obj = new CProject(); $deny = $obj->getDeniedRecords($AppUI->user_id); $where .= count($deny) > 0 ? "\n\tAND task_project NOT IN (" . implode(',', $deny) . ')' : ''; // get any specifically denied tasks $obj = new CTask(); $allow = $obj->getAllowedSQL($AppUI->user_id); $where .= count($allow) > 0 ? "\n\tAND " . implode(' AND ', $allow) : ''; // assemble query $sql = "SELECT DISTINCT task_id, task_name, task_start_date, task_end_date," . "\n\ttask_duration, task_duration_type," . "\n\tproject_color_identifier AS color," . "\n\tproject_name" . "\nFROM tasks,projects,companies" . "\nWHERE {$where}" . "\nORDER BY task_start_date"; //echo "<pre>$sql</pre>"; // execute and return return db_loadList($sql); }
/** * @param Date Start date of the period * @param Date End date of the period * @param integer The target company */ function getTasksForPeriod($start_date, $end_date, $company_id = 0) { global $AppUI; $q =& new DBQuery(); // convert to default db time stamp $db_start = $start_date->format(FMT_DATETIME_MYSQL); $db_end = $end_date->format(FMT_DATETIME_MYSQL); // filter tasks for not allowed projects $tasks_filter = ''; // check permissions on projects $proj = new CProject(); $task_filter_where = $proj->getAllowedSQL($AppUI->user_id, 'task_project'); // exclude read denied projects $deny = $proj->getDeniedRecords($AppUI->user_id); // check permissions on tasks $obj = new CTask(); $allow = $obj->getAllowedSQL($AppUI->user_id); $q->addTable('tasks', 't'); $q->innerJoin('projects', 'p', 't.task_project = p.project_id'); $q->addQuery('DISTINCT task_id, task_name, task_start_date, task_end_date, task_duration' . ', task_duration_type, project_color_identifier AS color, project_name'); $q->addWhere('task_status > -1' . " AND (task_start_date <= '{$db_end}' AND (task_end_date >= '{$db_start}'" . " OR task_end_date = '0000-00-00 00:00:00' OR task_end_date = NULL))"); if ($company_id) { $q->addWhere('project_company = ' . $company_id); } if (count($task_filter_where) > 0) { $q->addWhere('(' . implode(' AND ', $task_filter_where) . ')'); } if (count($deny) > 0) { $q->addWhere('(task_project NOT IN (' . implode(', ', $deny) . '))'); } if (count($allow) > 0) { $q->addWhere('(' . implode(' AND ', $allow) . ')'); } $q->addOrder('task_start_date'); // assemble query $sql = $q->prepare(); $q->clear(); //echo "<pre>$sql</pre>"; // execute and return return db_loadList($sql); }
function projects_list_data($user_id = false) { global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems, $tasks_sum, $tasks_summy, $tasks_total, $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 DBQuery(); // Let's delete support tables data $q->setDelete('tasks_sum'); $q->exec(); $q->clear(); //BEGIN: Deprecated in v2.0 $q->setDelete('tasks_total'); $q->exec(); $q->clear(); //END: Deprecated in v2.0 $q->setDelete('tasks_summy'); $q->exec(); $q->clear(); $q->setDelete('tasks_critical'); $q->exec(); $q->clear(); $q->setDelete('tasks_problems'); $q->exec(); $q->clear(); $q->setDelete('tasks_users'); $q->exec(); $q->clear(); // support task sum table // by Pablo Roca (pabloroca@mvps.org) // 16 August 2003 $working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8; // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours $q->addInsertSelect('tasks_sum'); $q->addTable('tasks'); $q->addQuery('task_project, COUNT(distinct tasks.task_id) AS total_tasks'); $q->addQuery('-1 AS project_percent_complete'); $q->addQuery('SUM(task_duration * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) AS project_duration'); if ($user_id) { $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); $q->addWhere('ut.user_id = ' . (int) $user_id); } $q->addWhere('tasks.task_id = tasks.task_parent'); $q->addGroup('task_project'); $tasks_sum = $q->exec(); $q->clear(); //BEGIN: Deprecated in v2.0 // support task total table $q->addInsertSelect('tasks_total'); $q->addTable('tasks'); $q->addQuery('task_project, COUNT(distinct tasks.task_id) AS total_tasks'); if ($user_id) { $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); $q->addWhere('ut.user_id = ' . (int) $user_id); } $q->addGroup('task_project'); $tasks_total = $q->exec(); $q->clear(); //END: Deprecated in v2.0 // support My Tasks $q->addInsertSelect('tasks_summy'); $q->addTable('tasks'); $q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks'); if ($user_id) { $q->addWhere('task_owner = ' . (int) $user_id); } else { $q->addWhere('task_owner = ' . (int) $AppUI->user_id); } $q->addGroup('task_project'); $tasks_summy = $q->exec(); $q->clear(); // support critical tasks $q->addInsertSelect('tasks_critical'); $q->addTable('tasks', 't'); $q->addQuery('task_project, task_id AS critical_task, task_end_date AS project_actual_end_date'); $sq = new DBQuery(); $sq->addTable('tasks', 'st'); $sq->addQuery('MAX(task_end_date)'); $sq->addWhere('st.task_project = t.task_project'); $q->addWhere('task_end_date = (' . $sq->prepare() . ')'); $q->addGroup('task_project'); $tasks_critical = $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_duration, project_parent, project_original_parent, project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status, project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, pr.project_task_count, tsy.my_tasks, pr.project_percent_complete, user_username, project_active'); $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_critical', 'tc', 'pr.project_id = tc.task_project'); $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project'); $q->addJoin('tasks_sum', 'ts', 'pr.project_id = ts.task_project'); $q->addJoin('tasks_summy', 'tsy', 'pr.project_id = tsy.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) . ')'); } $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) { showchilddept($row); findchilddept($rows, $row['dept_id']); } } } } } $buffer .= '</select>'; }