PHP CTask::getAllowedSQL方法代码示例

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

} 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 )');
}
if ($catsql) {

 $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();
     $task->bind($task_data);

}
if (($project_id || !$task_id) && !$min_view) {
    if ($search_text = $AppUI->getState('searchtext')) {
        $q->addWhere('( task_name LIKE (\'%' . $search_text . '%\') OR task_description LIKE (\'%' . $search_text . '%\') )');
    }
}
// filter tasks considering task and project permissions
$projects_filter = '';
$tasks_filter = '';
// TODO: Enable tasks filtering
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'task_project');
if (count($allowedProjects)) {
    $q->addWhere($allowedProjects);
}
$obj = new CTask();
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id, 'tasks.task_id');
if (count($allowedTasks)) {
    $q->addWhere($allowedTasks);
}
// Filter by company
if (!$min_view && $f2 != 'allcompanies') {
    $q->addJoin('companies', 'c', 'c.company_id = p.project_company', 'inner');
    $q->addWhere('company_id = ' . (int) $f2);
}
$q->addGroup('tasks.task_id');
if (!$project_id && !$task_id) {
    $q->addOrder('p.project_id, task_start_date, task_end_date');
} else {
    $q->addOrder('task_start_date, task_end_date');
}
//print_r($q->prepare());

 $sql->addQuery('t.*');
 if ($use_period) {
     $sql->addWhere("((task_start_date >= '{$ss}' AND task_start_date <= '{$se}') " . " OR (task_end_date <= '{$se}' AND task_end_date >= '{$ss}'))");
 }
 $sql->addWhere('task_percent_complete < 100');
 if ($project_id != 'all') {
     $sql->addWhere('t.task_project = \'' . $project_id . '\'');
 }
 if ($company_id != 'all') {
     $sql->addWhere("p.project_company='{$company_id}'");
 }
 if ($log_userfilter != 0) {
     $sql->addWhere('ut.user_id = ' . $log_userfilter);
 }
 $task = new CTask();
 $allowedTasks = $task->getAllowedSQL($AppUI->user_id, 't.task_id');
 if (count($allowedTasks)) {
     $sql->addWhere(implode(' AND ', $allowedTasks));
 }
 $allowedChildrenTasks = $task->getAllowedSQL($AppUI->user_id, 't.task_parent');
 if (count($allowedChildrenTasks)) {
     $sql->addWhere(implode(' AND ', $allowedChildrenTasks));
 }
 // Now add the required restrictions.
 $proj->setAllowedSQL($AppUI->user_id, $sql, null, 'p');
 $sql->addOrder('task_project, task_end_date, task_start_date');
 $task_list_hash = $sql->loadHashList('task_id');
 $task_list = array();
 $task_assigned_users = array();
 $user_assigned_tasks = array();
 foreach ($task_list_hash as $task_id => $task_data) {

 /**
  * @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);
 }

 /**
 * @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);
 }