$sortTasksByName = w2PgetParam($_REQUEST, 'sortTasksByName', 0);
$addPwOiD = w2PgetParam($_REQUEST, 'addPwOiD', 0);
$pjobj = new CProject();
/*
** Load department info for the case where one
** wants to see the ProjectsWithOwnerInDeparment (PwOiD)
** instead of the projects related to the given department.
*/
if ($addPwOiD && $department > 0) {
$owner_ids = array();
$q = new w2p_Database_Query();
$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();
}
// pull valid projects and their percent complete information
$q = new w2p_Database_Query();
$q->addTable('projects', 'pr');
$q->addQuery('DISTINCT pr.project_id, project_color_identifier, project_name, project_start_date, project_end_date,
max(t1.task_end_date) AS project_actual_end_date, project_percent_complete,
project_status, project_active');
$q->addJoin('tasks', 't1', 'pr.project_id = t1.task_project');
$q->addJoin('companies', 'c1', 'pr.project_company = c1.company_id');
if ($department > 0 && !$addPwOiD) {
$q->addWhere('project_departments.department_id = ' . (int) $department);
}
if ($project_type > -1) {
$q->addWhere('pr.project_type = ' . (int) $project_type);
echo $AppUI->_('User Efficiency (based on completed tasks)');
?>
</th>
</tr>
<?php
if (count($user_list)) {
$percentage_sum = $hours_allocated_sum = $hours_worked_sum = 0;
$sum_total_hours_allocated = $sum_total_hours_worked = 0;
$sum_hours_allocated_complete = $sum_hours_worked_complete = 0;
//TODO: Split times for which more than one users were working...
foreach ($user_list as $user_id => $user) {
$q->addTable('user_tasks', 'ut');
$q->addQuery('task_id');
$q->addWhere('user_id = ' . (int) $user_id);
$tasks_id = $q->loadColumn();
$q->clear();
$total_hours_allocated = $total_hours_worked = 0;
$hours_allocated_complete = $hours_worked_complete = 0;
foreach ($tasks_id as $task_id) {
if (isset($task_list[$task_id])) {
// Now let's figure out how many time did the user spent in this task
$q->addTable('task_log');
$q->addQuery('SUM(task_log_hours)');
$q->addWhere('task_log_task =' . (int) $task_id);
$q->addWhere('task_log_creator =' . (int) $user_id);
$hours_worked = round($q->loadResult(), 2);
$q->clear();
$q->addTable('tasks');
$q->addQuery('task_percent_complete');
$q->addWhere('task_id =' . (int) $task_id);
/**
* Tests adding a reminder to a task
*/
public function testAddReminder()
{
global $AppUI;
global $w2Pconfig;
$this->obj->load(1);
// Ensure our global setting for task_reminder_control is set properly for this
$old_task_reminder_control = $w2Pconfig['task_reminder_control'];
$w2Pconfig['task_reminder_control'] = true;
$this->obj->addReminder();
$this->obj->task_percent_complete = 50;
$xml_file_dataset = $this->createXMLDataset($this->getDataSetPath() . 'tasksTestAddReminder.xml');
$xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('event_queue' => array('queue_start')));
$xml_db_dataset = $this->getConnection()->createDataSet();
$xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('event_queue' => array('queue_start')));
$this->assertTablesEqual($xml_file_filtered_dataset->getTable('event_queue'), $xml_db_filtered_dataset->getTable('event_queue'));
$now_secs = time();
$min_time = $now_secs - 10;
/**
* Get updated dates to test against
*/
$q = new w2p_Database_Query();
$q->addTable('event_queue');
$q->addQuery('queue_start');
$q->addWhere('queue_id = 2');
$results = $q->loadColumn();
foreach ($results as $queue_start) {
$this->assertGreaterThanOrEqual($min_time, $queue_start);
$this->assertLessThanOrEqual($now_secs, $queue_start);
}
$w2Pconfig['task_reminder_control'] = $old_task_reminder_control;
}
<?php
echo $call_back_string;
?>
self.close();
</script>
<?php
}
// Remove any empty elements
$contacts_id = remove_invalid(explode(',', $selected_contacts_id));
$selected_contacts_id = implode(',', $contacts_id);
$q = new w2p_Database_Query();
if (strlen($selected_contacts_id) > 0 && !$show_all && !$company_id) {
$q->addTable('contacts');
$q->addQuery('DISTINCT contact_company');
$q->addWhere('contact_id IN (' . $selected_contacts_id . ')');
$where = implode(',', $q->loadColumn());
$q->clear();
if (substr($where, 0, 1) == ',') {
$where = '0' . $where;
}
$where = $where ? 'contact_company IN(' . $where . ')' : '';
} elseif (!$company_id && !$show_all) {
// Contacts from all allowed companies
$where = '(contact_company IS NULL OR contact_company = 0)';
$company_name = $AppUI->_('No Company');
} elseif ($show_all) {
$company_name = $AppUI->_('Allowed Companies');
} else {
// Contacts for this company only
$q->addWhere('contact_company = ' . (int) $company_id);
}
/**
* This function recursively updates all tasks project
* to the one passed as parameter
*/
public function updateSubTasksProject($new_project, $task_id = null)
{
$q = new w2p_Database_Query();
if (is_null($task_id)) {
$task_id = $this->task_id;
}
$q->addTable('tasks');
$q->addQuery('task_id');
$q->addWhere('task_parent = ' . (int) $task_id);
$tasks_id = $q->loadColumn();
$q->clear();
if (count($tasks_id) == 0) {
return true;
}
// update project of children
$q->addTable('tasks');
$q->addUpdate('task_project', $new_project);
$q->addWhere('task_parent = ' . (int) $task_id);
$q->exec();
$q->clear();
foreach ($tasks_id as $id) {
if ($id != $task_id) {
$this->updateSubTasksProject($new_project, $id);
}
}
}
/**
* Tests importing tasks from one project to another
*/
public function testImportTasks()
{
$this->markTestIncomplete("This test is failing miserably.. not sure of the best way to solve it yet.");
$this->obj->load(4);
$response = $this->obj->importTasks(3);
$this->assertEquals(array(), $response);
// $xml_file_dataset = $this->createXMLDataSet($this->getDataSetPath().'projectsTestImportTasks.xml');
// $xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('tasks' => array('task_created', 'task_updated')));
// $xml_db_dataset = $this->getConnection()->createDataSet();
// $xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('tasks' => array('task_created', 'task_updated')));
// $this->assertTablesEqual($xml_file_filtered_dataset->getTable('tasks'), $xml_db_filtered_dataset->getTable('tasks'));
$now_secs = time();
$min_time = $now_secs - 10;
/**
* Get created dates to test against
*/
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('task_created');
$q->addWhere('task_project = 4');
$results = $q->loadColumn();
global $AppUI;
foreach ($results as $created) {
$created = strtotime($AppUI->formatTZAwareTime($created, '%Y-%m-%d %T'));
$this->assertGreaterThanOrEqual($created, $now_secs);
$this->assertLessThanOrEqual($created, $min_time);
}
/**
* Get updated dates to test against
*/
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('task_updated');
$q->addWhere('task_project = 4');
$results = $q->loadColumn();
foreach ($results as $updated) {
$updated = strtotime($AppUI->formatTZAwareTime($updated, '%Y-%m-%d %T'));
$this->assertGreaterThanOrEqual($min_time, $updated);
$this->assertLessThanOrEqual($now_secs, $updated);
}
// $xml_dataset = $this->createXMLDataSet($this->getDataSetPath().'projectsTestImportTasks.xml');
// $this->assertTablesEqual($xml_dataset->getTable('user_tasks'), $this->getConnection()->createDataSet()->getTable('user_tasks'));
// $this->assertTablesEqual($xml_dataset->getTable('task_dependencies'), $this->getConnection()->createDataSet()->getTable('task_dependencies'));
}
echo styleRenderBoxTop();
}
echo '<table cellspacing="0" cellpadding="4" border="0" width="100%" class="std">
<tr>
<td>';
$total = 0;
$q = new w2p_Database_Query();
if ($fullaccess) {
$q->addTable('companies');
$q->addQuery('company_id');
} else {
$q->addTable('companies');
$q->addQuery('company_id');
$q->addWhere('company_owner = ' . (int) $AppUI->user_id);
}
$companies = $q->loadColumn();
$q->clear();
if (!empty($companies)) {
foreach ($companies as $company) {
$total += showcompany($company);
}
} else {
$q->addTable('companies');
$q->addQuery('company_id');
foreach ($q->loadColumn() as $company) {
$total += showcompany($company, true);
}
}
echo '<h2>' . $AppUI->_('Total Hours') . ': ';
printf("%.2f", $total);
echo '</h2>';
/**
* Overload of the w2PObject::getDeniedRecords
* to ensure that the projects owned by denied companies are denied.
*
* @author handco <*****@*****.**>
* @see w2PObject::getAllowedRecords
*/
public function getDeniedRecords($uid)
{
$aBuf1 = parent::getDeniedRecords($uid);
$oCpy = new CCompany();
// Retrieve which projects are allowed due to the company rules
$aCpiesAllowed = $oCpy->getAllowedRecords($uid, 'company_id,company_name');
//Department permissions
$oDpt = new CDepartment();
$aDptsAllowed = $oDpt->getAllowedRecords($uid, 'dept_id,dept_name');
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addQuery('projects.project_id');
$q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
if (count($aCpiesAllowed)) {
if (array_search('0', $aCpiesAllowed) === false) {
//If 0 (All Items of a module) are not permited then just add the allowed items only
$q->addWhere('NOT (project_company IN (' . implode(',', array_keys($aCpiesAllowed)) . '))');
} else {
//If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
}
} else {
//if the user is not allowed any company then lets shut him off
$q->addWhere('0=1');
}
if (count($aDptsAllowed)) {
if (array_search('0', $aDptsAllowed) === false) {
//If 0 (All Items of a module) are not permited then just add the allowed items only
$q->addWhere('NOT (department_id IN (' . implode(',', array_keys($aDptsAllowed)) . '))');
} else {
//If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
$q->addWhere('NOT (department_id IS NULL)');
}
} else {
//If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
$q->addWhere('NOT (department_id IS NULL)');
}
$aBuf2 = $q->loadColumn();
$q->clear();
return array_merge($aBuf1, $aBuf2);
}
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;
}
/**
* @param $department
*
* @return array
*/
function __extract_from_projects_gantt($department)
{
$q = new w2p_Database_Query();
$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();
return $owner_ids;
}
/**
* canTaskAccess()
* Used to check if a user has task_access to see the task in task list context
* (This function was optimized to try to use the DB the least possible)
*
* @param mixed $task_id
* @param mixed $task_access
* @param mixed $task_owner
* @return true if user has task access to it, or false if he doesn't
*/
function canTaskAccess($task_id, $task_access, $task_owner)
{
global $AppUI;
$q = new w2p_Database_Query();
if (!$task_id || !isset($task_access)) {
return false;
}
//if for some weird reason we have tasks without an owner, lets make them visible at least for admins, or else we take the risk of having phantom tasks.
if (!$task_owner) {
$task_owner = $AppUI->user_id;
}
$user_id = $AppUI->user_id;
// Let's see if this user has admin privileges, if so return true
if ($AppUI->user_is_admin) {
return true;
}
switch ($task_access) {
case 0:
// public
$retval = true;
break;
case 1:
// protected
$q->addTable('users');
$q->addQuery('user_company');
$q->addWhere('user_id=' . (int) $user_id . ' OR user_id=' . (int) $task_owner);
$user_owner_companies = $q->loadColumn();
$q->clear();
$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');
$q->addQuery('COUNT(task_id)');
$q->addWhere('user_id=' . (int) $user_id . ' AND task_id=' . (int) $task_id);
$count = $q->loadResult();
$q->clear();
$retval = $company_match && $count > 0 || $count > 0 || $task_owner == $user_id;
break;
case 3:
// private
$retval = $task_owner == $user_id;
break;
default:
$retval = false;
break;
}
return $retval;
}
$titleBlock->show();
$filter_param = w2PgetParam($_REQUEST, 'filter', '');
$filter = array();
if ($filter_param) {
$in_filter = $_REQUEST['filter'];
$filter[] = 'history_table = \'' . $_REQUEST['filter'] . '\' ';
} else {
$in_filter = '';
}
if (!empty($_REQUEST['project_id'])) {
$project_id = w2PgetParam($_REQUEST, 'project_id', 0);
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('task_id');
$q->addWhere('task_project = ' . (int) $project_id);
$project_tasks = implode(',', $q->loadColumn());
if (!empty($project_tasks)) {
$project_tasks = 'OR (history_table = \'tasks\' AND history_item IN (' . $project_tasks . '))';
}
$q->addTable('files');
$q->addQuery('file_id');
$q->addWhere('file_project = ' . (int) $project_id);
$project_files = implode(',', $q->loadColumn());
if (!empty($project_files)) {
$project_files = 'OR (history_table = \'files\' AND history_item IN (' . $project_files . '))';
}
$filter[] = '((history_table = \'projects\' AND history_item = \'' . (int) $project_id . '\') ' . $project_tasks . ' ' . $project_files . ')';
}
$page = isset($_REQUEST['pg']) ? (int) $_REQUEST['pg'] : 1;
$limit = isset($_REQUEST['limit']) ? (int) $_REQUEST['limit'] : 100;
$offset = ($page - 1) * $limit;
/**
* Tests importing tasks from one project to another
*/
public function testImportTasks()
{
$this->obj->load(4);
$response = $this->obj->importTasks(3);
$this->assertEquals(array(), $response);
$xml_file_dataset = $this->createXMLDataSet($this->getDataSetPath() . 'projectsTestImportTasks.xml');
$xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('tasks' => array('task_created', 'task_updated')));
$xml_db_dataset = $this->getConnection()->createDataSet();
$xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('tasks' => array('task_created', 'task_updated')));
$this->assertTablesEqual($xml_file_filtered_dataset->getTable('tasks'), $xml_db_filtered_dataset->getTable('tasks'));
$now_secs = time();
$min_time = $now_secs - 10;
/**
* Get created dates to test against
*/
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('task_created');
$q->addWhere('task_project = 4');
$results = $q->loadColumn();
foreach ($results as $created) {
$this->assertGreaterThanOrEqual($min_time, strtotime($created));
$this->assertLessThanOrEqual($now_secs, strtotime($created));
}
/**
* Get updated dates to test against
*/
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('task_updated');
$q->addWhere('task_project = 4');
$results = $q->loadColumn();
foreach ($results as $updated) {
$this->assertGreaterThanOrEqual($min_time, strtotime($updated));
$this->assertLessThanOrEqual($now_secs, strtotime($updated));
}
$xml_dataset = $this->createXMLDataSet($this->getDataSetPath() . 'projectsTestImportTasks.xml');
$this->assertTablesEqual($xml_dataset->getTable('user_tasks'), $this->getConnection()->createDataSet()->getTable('user_tasks'));
$this->assertTablesEqual($xml_dataset->getTable('task_dependencies'), $this->getConnection()->createDataSet()->getTable('task_dependencies'));
}
public static function getUserDeptId($user_id)
{
$q = new w2p_Database_Query();
$q->addQuery('con.contact_department');
$q->addTable('users', 'u');
$q->addJoin('contacts', 'con', 'user_contact = contact_id', 'inner');
$q->addWhere('u.user_id = ' . (int) $user_id);
$user_dept = $q->loadColumn();
$q->clear();
return $user_dept;
}
