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;
}
function countFiles($folder)
{
global $AppUI, $company_id, $allowed_companies, $tab;
global $deny1, $deny2, $project_id, $task_id, $showProject, $file_types;
$q = new w2p_Database_Query();
$q->addTable('files');
$q->addQuery('count(files.file_id)', 'file_in_folder');
$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);
if (count($deny1) > 0) {
$q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')');
}
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 . ')');
}
$files_in_folder = $q->loadResult();
$q->clear();
return $files_in_folder;
}
public function getTaskList($userId, $days = 30)
{
/*
* This list of fields - id, name, description, startDate, endDate,
* updatedDate - are named specifically for the iCal creation.
* If you change them, it's probably going to break. So don't do that.
*/
$q = new w2p_Database_Query();
$q->addQuery('t.task_id as id');
$q->addQuery('task_name as name');
$q->addQuery('task_description as description');
$q->addQuery('task_start_date as startDate');
$q->addQuery('task_end_date as endDate');
$q->addQuery('task_updated as updatedDate');
$q->addQuery('CONCAT(\'' . W2P_BASE_URL . '/index.php?m=tasks&a=view&task_id=' . '\', t.task_id) as url');
$q->addQuery('p.project_id, p.project_name');
$q->addTable('tasks', 't');
$q->addWhere('(task_start_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ' OR task_end_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ')');
$q->addWhere('task_percent_complete < 100');
$q->addWhere('task_dynamic <> 1');
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('ut.user_id = ' . $userId);
$q->innerJoin('projects', 'p', 'p.project_id = t.task_project');
$q->addWhere('project_active > 0');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
$q->addWhere('project_status <> ' . $template_status);
}
$q->addOrder('task_start_date, task_end_date');
return $q->loadList();
}
$q->addWhere('task_project = ' . (int) $project_id);
}
switch ($f) {
case 'all':
$q->addWhere('task_status > -1');
break;
case 'myproj':
$q->addWhere('task_status > -1');
$q->addWhere('project_owner = ' . (int) $AppUI->user_id);
break;
case 'mycomp':
$q->addWhere('task_status > -1');
$q->addWhere('project_company = ' . (int) $AppUI->user_company);
break;
case 'myinact':
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('task_project = p.project_id');
$q->addWhere('ut.user_id = ' . (int) $AppUI->user_id);
break;
default:
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('task_status > -1');
$q->addWhere('task_project = p.project_id');
$q->addWhere('ut.user_id = ' . (int) $AppUI->user_id);
break;
}
// get any specifically denied tasks
$task = new CTask();
$task->setAllowedSQL($AppUI->user_id, $q);
$proTasks = $q->loadHashList('task_id');
$orrarr[] = array('task_id' => 0, 'order_up' => 0, 'order' => '');
$flags = $a['task_milestone'] ? 'm' : '';
$cap = '';
if (!$start || $start == '0000-00-00') {
$start = !$end ? date('Y-m-d') : $end;
$cap .= '(no start date)';
}
if (!$end) {
$end = $start;
$cap .= ' (no end date)';
} else {
$cap = '';
}
if ($showLabels == '1') {
$q = new w2p_Database_Query();
$q->addTable('user_tasks', 'ut');
$q->innerJoin('users', 'u', 'u.user_id = ut.user_id');
$q->innerJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addQuery('ut.task_id, u.user_username, ut.perc_assignment');
$q->addQuery('c.contact_first_name, c.contact_last_name');
$q->addWhere('ut.task_id = ' . (int) $a['task_id']);
$res = $q->loadList();
foreach ($res as $rw) {
switch ($rw['perc_assignment']) {
case 100:
$caption .= $rw['contact_first_name'] . ' ' . $rw['contact_last_name'] . ';';
break;
default:
$caption .= $rw['contact_first_name'] . ' ' . $rw['contact_last_name'] . ' [' . $rw['perc_assignment'] . '%];';
break;
}
}
/**
* @param $project_id
* @param $f
* @param $AppUI
* @param $task
*
* @return array
*/
function __extract_from_gantt_pdf4($project_id, $f, $AppUI, $task)
{
// pull tasks
$q = new w2p_Database_Query();
$q->addTable('tasks', 't');
$q->addQuery('t.task_id, task_parent, task_name, task_start_date, task_end_date,' . ' task_duration, task_duration_type, task_priority, task_percent_complete,' . ' task_hours_worked, task_order, task_project, task_milestone, task_access,' . ' task_owner, project_name, project_color_identifier, task_dynamic');
$q->addJoin('projects', 'p', 'project_id = t.task_project', 'inner');
$q->addOrder('p.project_id, t.task_end_date');
if ($project_id) {
$q->addWhere('task_project = ' . (int) $project_id);
}
switch ($f) {
case 'all':
$q->addWhere('task_status > -1');
break;
case 'myproj':
$q->addWhere('task_status > -1');
$q->addWhere('project_owner = ' . (int) $AppUI->user_id);
break;
case 'mycomp':
$q->addWhere('task_status > -1');
$q->addWhere('project_company = ' . (int) $AppUI->user_company);
break;
case 'myinact':
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('ut.user_id = ' . $AppUI->user_id);
break;
default:
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('ut.user_id = ' . (int) $AppUI->user_id);
break;
}
$q = $task->setAllowedSQL($AppUI->user_id, $q);
$proTasks = $q->loadHashList('task_id');
return $proTasks;
}
public function getCalendarEvents($userId, $days = 30)
{
/*
* This list of fields - id, name, description, startDate, endDate,
* updatedDate - are named specifically for the iCal creation.
* If you change them, it's probably going to break. So don't do that.
*/
$q = new w2p_Database_Query();
$q->addQuery('e.event_id as id');
$q->addQuery('event_title as name');
$q->addQuery('event_description as description');
$q->addQuery('event_start_date as startDate');
$q->addQuery('event_end_date as endDate');
$q->addQuery("'" . $q->dbfnNowWithTZ() . "' as updatedDate");
$q->addQuery('CONCAT(\'' . W2P_BASE_URL . '/index.php?m=calendar&a=view&event_id=' . '\', e.event_id) as url');
$q->addQuery('projects.project_id, projects.project_name');
$q->addTable('events', 'e');
$q->leftJoin('projects', 'projects', 'e.event_project = projects.project_id');
$q->addWhere('(event_start_date > ' . $q->dbfnNow() . ' OR event_end_date > ' . $q->dbfnNow() . ')');
$q->addWhere('(event_start_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ' OR event_end_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ')');
$q->innerJoin('user_events', 'ue', 'ue.event_id = e.event_id');
$q->addWhere('ue.user_id = ' . $userId);
$q->addOrder('event_start_date');
return $q->loadList();
}
public function getFileCountByFolder(CAppUI $AppUI, $folder_id, $task_id, $project_id, $company_id)
{
// SQL text for count the total recs from the selected option
$q = new w2p_Database_Query();
$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();
$deny1 = $project->getDeniedRecords($AppUI->user_id);
if (count($deny1) > 0) {
$q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')');
}
//TODO: apply permissions properly
$task = new CTask();
$deny2 = $task->getDeniedRecords($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());
}
