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>';
}
