public static function getFileList(CAppUI $AppUI = null, $company_id, $project_id, $task_id, $category_id)
{
global $AppUI;
$q = new DBQuery();
$q->addQuery('f.*');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('project_departments', 'pd', 'p.project_id = pd.project_id');
$q->addJoin('departments', '', 'pd.department_id = dept_id');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$project = new CProject();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
if (count($allowedProjects)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (isset($company_id) && (int) $company_id > 0) {
$q->addWhere('project_company = ' . (int) $company_id);
}
if (isset($project_id) && (int) $project_id > 0) {
$q->addWhere('file_project = ' . (int) $project_id);
}
if (isset($task_id) && (int) $task_id > 0) {
$q->addWhere('file_task = ' . (int) $task_id);
}
if ($category_id >= 0) {
$q->addWhere('file_category = ' . (int) $category_id);
}
return $q->loadList();
}
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;
}
$q->addJoin('projects', '', 'projects.project_id = task_project', 'inner');
$q->addJoin('project_departments', '', 'project_departments.project_id = projects.project_id');
$q->addJoin('departments', '', 'department_id = dept_id');
$q->addWhere('project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
$q->addWhere('project_status <> ' . (int) $template_status);
}
if ($use_period) {
$q->addWhere('( (task_start_date >= ' . $ss . ' AND task_start_date <= ' . $se . ') OR ' . '(task_end_date <= ' . $se . ' AND task_end_date >= ' . $ss . ') )');
}
if ($project_id != 0) {
$q->addWhere('task_project=' . $project_id);
}
$proj = new CProject();
$obj = new CTask();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'task_project');
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id);
if (count($allowedProjects)) {
$q->addWhere(implode(' AND ', $allowedProjects));
}
if (count($allowedTasks)) {
$q->addWhere(implode(' AND ', $allowedTasks));
}
$q->addOrder('task_end_date');
$task_list_hash = $q->loadHashList('task_id');
$q->clear();
$task_list = array();
$task_assigned_users = array();
$i = 0;
foreach ($task_list_hash as $task_id => $task_data) {
$task = new CTask();
$catsql = false;
} elseif ($tabbed) {
if ($tab <= 0) {
$catsql = false;
} else {
$catsql = 'file_category = ' . ($tab - 1);
}
} else {
if ($tab < 0) {
$catsql = false;
} else {
$catsql = 'file_category = ' . $tab;
}
}
// Fetch permissions once for all queries
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
$allowedTasks = $task->getAllowedSQL($AppUI->user_id, 'file_task');
// SQL text for count the total recs from the selected option
$q = new w2p_Database_Query();
$q->addQuery('count(file_id)');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
if (count($allowedProjects)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
/**
* @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, $user_id = null, $filter_proj_archived = false, $filter_proj_completed = false)
{
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);
// Allow for possible passing of user_id 0 to stop user filtering
if (!isset($user_id)) {
$user_id = $AppUI->user_id;
}
// filter tasks for not allowed projects
$tasks_filter = '';
// 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');
$parent_task_allow = $obj->getAllowedSQL($AppUI->user_id, 't.task_parent');
$q->addTable('tasks', 't');
if ($user_id) {
$q->innerJoin('user_tasks', 'ut', 't.task_id=ut.task_id');
}
$q->innerJoin('projects', 'p', 't.task_project = p.project_id');
$q->addQuery('DISTINCT t.task_id, t.task_name, t.task_start_date, t.task_end_date' . ', t.task_duration, t.task_duration_type' . ', p.project_color_identifier AS color, p.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)" . " OR task_start_date BETWEEN '{$db_start}' AND '{$db_end}'))");
if ($user_id) {
$q->addWhere("ut.user_id = '{$user_id}'");
}
if ($company_id) {
$q->addWhere('p.project_company = ' . $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) . ')');
}
if (count($parent_task_allow) > 0) {
$q->addWhere('(' . implode(' AND ', $parent_task_allow) . ')');
}
if ($filter_proj_archived) {
$q->addWhere('p.project_status <> 7');
}
if ($filter_proj_archived) {
$q->addWhere('p.project_status <> 5');
}
$q->addOrder('t.task_start_date');
// assemble query
$sql = $q->prepare();
$q->clear();
//echo "<pre>$sql</pre>";
// execute and return
return db_loadList($sql);
}
public static function getFileList($AppUI = null, $company_id = 0, $project_id = 0, $task_id = 0, $category_id = 0)
{
global $AppUI;
$q = new w2p_Database_Query();
$q->addQuery('f.*');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('project_departments', 'pd', 'p.project_id = pd.project_id');
$q->addJoin('departments', '', 'pd.department_id = dept_id');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$project = new CProject();
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
if (count($allowedProjects)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (isset($company_id) && (int) $company_id > 0) {
$q->addWhere('project_company = ' . (int) $company_id);
}
if (isset($project_id) && (int) $project_id > 0) {
$q->addWhere('file_project = ' . (int) $project_id);
}
if (isset($task_id) && (int) $task_id > 0) {
$q->addWhere('file_task = ' . (int) $task_id);
}
if ($category_id >= 0) {
$q->addWhere('file_category = ' . (int) $category_id);
}
return $q->loadList();
}
$child_t->task_priority = $action;
$child_t->store();
}
}
}
}
}
}
}
}
}
$AppUI->savePlace();
$proj = new CProject();
$tobj = new CTask();
$q = new DBQuery();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'p.project_id');
$allowedTasks = $tobj->getAllowedSQL($AppUI->user_id, 't.task_id');
//query my sub-tasks (ignoring task parents)
$q->addTable('tasks', 't');
$q->innerJoin('projects', 'p', 'p.project_id = t.task_project');
$q->addQuery('t.*, p.project_name, p.project_id, p.project_color_identifier');
if ($project_id) {
$q->addWhere('p.project_id = ' . $project_id);
}
if (count($allowedTasks)) {
$q->addWhere($allowedTasks);
}
if (count($allowedProjects)) {
$q->addWhere($allowedProjects);
}
$q->addGroup('t.task_id');
$proFilter = dPgetParam($_REQUEST, 'proFilter', '-1');
if ($proFilter == '-2') {
$filter1[] = ' project_status != 3';
} else {
if ($proFilter != '-1') {
$filter1[] = ' project_status = ' . $proFilter;
}
}
if ($company_id != 0) {
$filter1[] = ' project_company = ' . $company_id;
}
if ($showInactive != '1') {
$filter1[] = ' project_status <> 7';
}
$pjobj = new CProject();
$allowed_projects = $pjobj->getAllowedSQL($AppUI->user_id);
$where = array_merge($filter1, $allowed_projects);
// pull valid projects and their percent complete information
$q = new DBQuery();
$q->addTable('tasks', 't');
$q->addJoin('user_tasks', 'ut', 't.task_id = ut.task_id');
$q->addJoin('users', 'u', 'u.user_id = ut.user_id');
$q->addJoin('projects', 'p', 'p.project_id = t.task_project');
$q->addJoin('companies', 'c', 'p.project_company = c.company_id');
$q->addQuery('u.user_username, t.task_name, t.task_start_date, t.task_milestone' . ', ut.perc_assignment, t.task_end_date, t.task_dynamic' . ', p.project_color_identifier, p.project_name');
$q->addOrder('t.task_name, t.task_start_date, t.task_end_date, ut.perc_assignment');
$tasks = $q->loadList();
$q->clear();
$q->addTable('user_tasks', 'ut');
$q->innerJoin('users', 'u', 'u.user_id = ut.user_id');
$q->innerJoin('tasks', 't', 't.task_id = ut.task_id');
if ($task_priority > -2 && $task_priority < 2) {
// set priority
$q->addTable('tasks');
$q->addUpdate('task_priority', $task_priority);
$q->addWhere('task_id=' . $val);
}
}
}
db_exec($q->prepare(true));
echo db_error();
}
}
$AppUI->savePlace();
$proj = new CProject();
$tobj = new CTask();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id);
$allowedTasks = $tobj->getAllowedSQL($AppUI->user_id, 'task_id');
// query my sub-tasks (ignoring task parents)
$q = new DBQuery();
$q->addTable('tasks', 'ta');
$q->leftJoin('projects', 'pr', 'pr.project_id = ta.task_project');
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = ta.task_id AND ut.user_id = ' . $user_id);
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = ta.task_id AND tp.user_id = ' . $user_id);
$q->addQuery('ta.*');
$q->addQuery('pr.project_name, pr.project_id, pr.project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addWhere('(ta.task_percent_complete < 100 OR ta.task_percent_complete IS NULL)');
$q->addWhere('ta.task_status = 0');
if (!$showArcProjs) {
$q->addWhere('project_status <> 7');
}
$durnTypes = w2PgetSysVal('TaskDurationType');
$taskPriority = w2PgetSysVal('TaskPriority');
$task_project = $project_id;
$task_sort_item1 = w2PgetParam($_GET, 'task_sort_item1', '');
$task_sort_type1 = w2PgetParam($_GET, 'task_sort_type1', '');
$task_sort_item2 = w2PgetParam($_GET, 'task_sort_item2', '');
$task_sort_type2 = w2PgetParam($_GET, 'task_sort_type2', '');
$task_sort_order1 = (int) w2PgetParam($_GET, 'task_sort_order1', 0);
$task_sort_order2 = (int) w2PgetParam($_GET, 'task_sort_order2', 0);
if (isset($_POST['show_task_options'])) {
$AppUI->setState('TaskListShowIncomplete', w2PgetParam($_POST, 'show_incomplete', 0));
}
$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
// $allowedProjects = $project->getAllowedRecords($AppUI->user_id, 'project_id, project_name');
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'projects.project_id');
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q->addQuery('projects.project_id, project_color_identifier, project_name');
$q->addQuery('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');
$q->addQuery('company_name');
$q->addTable('projects');
$q->leftJoin('tasks', 't1', 'projects.project_id = t1.task_project');
$q->leftJoin('companies', 'c', 'company_id = project_company');
$q->leftJoin('project_departments', 'project_departments', 'projects.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
$q->addWhere('t1.task_id = t1.task_parent');
$q->addWhere('projects.project_id=' . $project_id);
if (count($allowedProjects)) {
$q->addWhere($allowedProjects);
}
$q->addGroup('projects.project_id');
/**
* Utility function to return an array of events with a period
* @param Date Start date of the period
* @param Date End date of the period
* @return array A list of events
*/
public function getEventsForPeriod($start_date, $end_date, $filter = 'all', $user_id = null, $project_id = 0, $company_id = 0)
{
global $AppUI;
// convert to default db time stamp
$db_start = $start_date->format(FMT_DATETIME_MYSQL);
$db_start = $AppUI->convertToSystemTZ($db_start);
$db_end = $end_date->format(FMT_DATETIME_MYSQL);
$db_end = $AppUI->convertToSystemTZ($db_end);
if (!isset($user_id)) {
$user_id = $AppUI->user_id;
}
$project = new CProject();
if ($project_id) {
$p =& $AppUI->acl();
if ($p->checkModuleItem('projects', 'view', $project_id, $user_id)) {
$allowedProjects = array('p.project_id = ' . (int) $project_id);
} else {
$allowedProjects = array('1=0');
}
} else {
$allowedProjects = $project->getAllowedSQL($user_id ? $user_id : $AppUI->user_id, 'event_project');
}
//do similiar actions for recurring and non-recurring events
$queries = array('q' => 'q', 'r' => 'r');
foreach ($queries as $query_set) {
${$query_set} = new w2p_Database_Query();
${$query_set}->addTable('events', 'e');
${$query_set}->addQuery('e.*');
${$query_set}->addOrder('e.event_start_date, e.event_end_date ASC');
${$query_set}->leftJoin('projects', 'p', 'p.project_id = e.event_project');
${$query_set}->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
${$query_set}->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
if ($company_id) {
${$query_set}->addWhere('project_company = ' . (int) $company_id);
} else {
if ($AppUI->getState('CalIdxCompany')) {
${$query_set}->addWhere('project_company = ' . $AppUI->getState('CalIdxCompany'));
}
}
if (count($allowedProjects)) {
${$query_set}->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ' ) ' . ($AppUI->getState('CalIdxCompany') ? '' : ($project_id ? '' : ' OR event_project = 0 ')) . ')');
}
switch ($filter) {
case 'my':
${$query_set}->addJoin('user_events', 'ue', 'ue.event_id = e.event_id AND ue.user_id =' . $user_id);
${$query_set}->addWhere('(ue.user_id = ' . (int) $user_id . ') AND (event_private = 0 OR event_owner=' . (int) $user_id . ')');
break;
case 'own':
${$query_set}->addWhere('event_owner =' . (int) $user_id);
break;
case 'all':
${$query_set}->addWhere('(event_private = 0 OR event_owner=' . (int) $user_id . ')');
break;
default:
${$query_set}->addWhere('(event_private = 0 OR event_owner=' . (int) $user_id . ')');
}
if ($query_set == 'q') {
// assemble query for non-recursive events
${$query_set}->addWhere('(event_recurs <= 0)');
// following line is only good for *non-recursive* events
${$query_set}->addWhere('(event_start_date <= \'' . $db_end . '\' AND event_end_date >= \'' . $db_start . '\' OR event_start_date BETWEEN \'' . $db_start . '\' AND \'' . $db_end . '\')');
$eventList = ${$query_set}->loadList();
} elseif ($query_set == 'r') {
// assemble query for recursive events
${$query_set}->addWhere('(event_recurs > 0)');
$eventListRec = ${$query_set}->loadList();
}
}
//Calculate the Length of Period (Daily, Weekly, Monthly View)
setlocale(LC_TIME, 'en');
$periodLength = Date_Calc::dateDiff($start_date->getDay(), $start_date->getMonth(), $start_date->getYear(), $end_date->getDay(), $end_date->getMonth(), $end_date->getYear());
setlocale(LC_ALL, $AppUI->user_lang);
// AJD: Should this be going off the end of the array? I don't think so.
// If it should then a comment to that effect would be nice.
for ($i = 0, $i_cmp = sizeof($eventListRec); $i < $i_cmp; $i++) {
//note from merlinyoda: j=0 is the original event according to getRecurrentEventforPeriod
// So, since the event is *recurring* x times, the loop condition should be j <= x, not j < x.
// This way the original and all recurrances are covered.
for ($j = 0, $j_cmp = intval($eventListRec[$i]['event_times_recuring']); $j <= $j_cmp; $j++) {
//Daily View
//show all
if ($periodLength <= 1) {
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $eventListRec[$i]['event_start_date'], $eventListRec[$i]['event_end_date'], $eventListRec[$i]['event_recurs'], $eventListRec[$i]['event_times_recuring'], $j);
} elseif ($periodLength > 1 && $eventListRec[$i]['event_recurs'] == 1 && $j == 0) {
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $eventListRec[$i]['event_start_date'], $eventListRec[$i]['event_end_date'], $eventListRec[$i]['event_recurs'], $eventListRec[$i]['event_times_recuring'], $j);
$eventListRec[$i]['event_title'] = $eventListRec[$i]['event_title'] . ' (' . $AppUI->_('Hourly') . ')';
} elseif ($periodLength > 1 && $eventListRec[$i]['event_recurs'] > 1) {
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $eventListRec[$i]['event_start_date'], $eventListRec[$i]['event_end_date'], $eventListRec[$i]['event_recurs'], $eventListRec[$i]['event_times_recuring'], $j);
}
//add values to the eventsArray if check for recurrent event was positive
if (sizeof($recEventDate) > 0) {
$eList[0] = $eventListRec[$i];
$eList[0]['event_start_date'] = $recEventDate[0]->format(FMT_DATETIME_MYSQL);
$eList[0]['event_end_date'] = $recEventDate[1]->format(FMT_DATETIME_MYSQL);
$eventList = array_merge($eventList, $eList);
}
// clear array of positive recurrent events for the case that next loop recEventDate is empty in order to avoid double display
$recEventDate = array();
}
}
$i = 0;
foreach ($eventList as $event) {
$eventList[$i]['event_start_date'] = $AppUI->formatTZAwareTime($event['event_start_date'], '%Y-%m-%d %H:%M:%S');
$eventList[$i]['event_end_date'] = $AppUI->formatTZAwareTime($event['event_end_date'], '%Y-%m-%d %H:%M:%S');
$i++;
}
//echo '<pre>'; print_r($eventList); echo '</pre>';
//return a list of non-recurrent and recurrent events
return $eventList;
}
/**
* Utility function to return an array of events with a period
* @param Date Start date of the period
* @param Date End date of the period
* @return array A list of events
*/
function getEventsForPeriod($start_date, $end_date, $filter = 'all', $user_id = null, $project_id = 0)
{
global $AppUI;
// the event times are stored as unix time stamps, just to be different
// convert to default db time stamp
$db_start = $start_date->format(FMT_DATETIME_MYSQL);
$db_end = $end_date->format(FMT_DATETIME_MYSQL);
if (!isset($user_id)) {
$user_id = $AppUI->user_id;
}
$project = new CProject();
if ($project_id) {
$p =& $AppUI->acl();
if ($p->checkModuleItem('projects', 'view', $project_id, $user_id)) {
$allowedProjects = array('p.project_id = ' . $project_id);
} else {
$allowedProjects = array('1=0');
}
} else {
$allowedProjects = $project->getAllowedSQL($user_id, 'event_project');
}
//do similiar actions for recurring and non-recurring events
$queries = array('q' => 'q', 'r' => 'r');
foreach ($queries as $query_set) {
${$query_set} = new DBQuery();
${$query_set}->addTable('events', 'e');
${$query_set}->addQuery('DISTINCT e.*');
${$query_set}->addOrder('e.event_start_date, e.event_end_date ASC');
${$query_set}->addJoin('projects', 'p', 'p.project_id = e.event_project');
if ($AppUI->getState('CalIdxCompany')) {
${$query_set}->addWhere('p.project_company = ' . $AppUI->getState('CalIdxCompany'));
}
if (count($allowedProjects)) {
${$query_set}->addWhere('((' . implode(' AND ', $allowedProjects) . ') ' . ($AppUI->getState('CalIdxCompany') || $project_id ? '' : ' OR event_project = 0 ') . ')');
}
switch ($filter) {
case 'my':
${$query_set}->addJoin('user_events', 'ue', 'ue.event_id = e.event_id AND ue.user_id =' . $user_id);
${$query_set}->addWhere('(ue.user_id = ' . $user_id . ') AND (event_private=0 OR event_owner=' . $user_id . ')');
break;
case 'own':
${$query_set}->addWhere('e.event_owner =' . $user_id);
break;
case 'all':
${$query_set}->addWhere('(e.event_private=0 OR e.event_owner=' . $user_id . ')');
break;
}
if ($query_set == 'q') {
// assemble query for non-recursive events
// following line is only good for *non-recursive* events
${$query_set}->addWhere('(event_recurs <= 0)');
${$query_set}->addWhere("(event_start_date < '{$db_end}'" . " AND event_end_date > '{$db_start}')");
$eventList = ${$query_set}->loadList();
} else {
if ($query_set == 'r') {
// assemble query for recursive events
${$query_set}->addWhere('(event_recurs > 0)');
$eventListRec = ${$query_set}->loadList();
}
}
}
//Calculate the Length of Period (Daily, Weekly, Monthly View)
setlocale(LC_ALL, 'en_AU' . ($locale_char_set ? '.' . $locale_char_set : '.utf8'));
$periodLength = Date_Calc::dateDiff($end_date->getDay(), $end_date->getMonth(), $end_date->getYear(), $start_date->getDay(), $start_date->getMonth(), $start_date->getYear());
setlocale(LC_ALL, $AppUI->user_lang);
foreach ($eventListRec as $key => $ia) {
$end = intval($ia['event_times_recuring']);
for ($j = 0; $j < $end; $j++) {
$recEventDate = array();
if ($periodLength <= 1) {
// Daily View or clash check: show all
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $ia['event_start_date'], $ia['event_end_date'], $ia['event_recurs'], $ia['event_times_recuring'], $j);
} else {
if ($ia['event_recurs'] == 1 && $j == 0) {
// Weekly or Monthly View and Hourly Recurrent Events
//show one time and add string 'hourly'
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $ia['event_start_date'], $ia['event_end_date'], $ia['event_recurs'], $ia['event_times_recuring'], $j);
$eventListRec[$key]['event_title'] = $ia['event_title'] . ' (' . $AppUI->_('Hourly') . ')';
} else {
if ($ia['event_recurs'] > 1) {
//Weekly and Monthly View and higher recurrence mode
//show all events of recurrence > 1
$recEventDate = CEvent::getRecurrentEventforPeriod($start_date, $end_date, $ia['event_start_date'], $ia['event_end_date'], $ia['event_recurs'], $ia['event_times_recuring'], $j);
}
}
}
//add values to the eventsArray if check for recurrent event was positive
if (!empty($recEventDate)) {
$display_start = $recEventDate[0]->format(FMT_DATETIME_MYSQL);
$display_end = $recEventDate[1]->format(FMT_DATETIME_MYSQL);
$eventListRec[$key]['event_start_date'] = $display_start;
$eventListRec[$key]['event_end_date'] = $display_end;
$eventList = array_merge($eventList, array($eventListRec[$key]));
}
}
}
//return a list of non-recurrent and recurrent events
return $eventList;
}
$q->addWhere('task_owner = ' . $AppUI->user_id);
$q->addGroup('task_project');
$q->exec();
$q->clear();
$q->addTable('projects', 'pr');
$q->addQuery('project_id, project_name, project_status, project_color_identifier,
project_start_date, project_end_date, project_priority,
ts.total_tasks, tsm.my_tasks, ts.project_percent_complete,
contact_first_name, contact_last_name, user_username');
$q->addJoin('users', 'u', 'u.user_id = pr.project_owner');
$q->addJoin('contacts', 'con', 'u.user_contact = con.contact_id');
$q->addJoin('tasks_sum', 'ts', 'pr.project_id = ts.task_project');
$q->addJoin('tasks_summy', 'tsm', 'pr.project_id = tsm.task_project');
$q->addWhere('pr.project_status <> 7');
$q->addWhere('pr.project_status <> 5');
$allowed_where = $obj->getAllowedSQL($AppUI->user_id);
if ($allowed_where) {
$q->addWhere(implode(' AND ', $allowed_where));
}
$q->addOrder('project_end_date');
$projects = $q->loadList();
?>
<table width="100%" border="0" cellpadding="3" cellspacing="1" class="tbl">
<tr>
<td align="right" width="65" nowrap="nowrap"> <?php
echo $AppUI->_('sort by');
?>
: </td>
<th nowrap="nowrap">
<a href="?m=projects&orderby=project_name" class="hdr"><?php
/**
* @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);
}
