PHP w2p_Database_Query::innerJoin方法代码示例

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