PHP CProject::getDeniedRecords方法代码示例

 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) . ' )');
    }
//.........这里部分代码省略.........

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');
//.........这里部分代码省略.........

##
##	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"] . '&nbsp;' . $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');
//.........这里部分代码省略.........