PHP DBQuery::createTemp方法代码示例

function displayFiles($folder_id)
{
    global $AppUI, $m, $a, $tab, $page;
    global $current_uri;
    global $canAccess, $canRead, $canEdit, $canAuthor, $canDelete;
    global $canAccess_folders, $canRead_folders, $canEdit_folders;
    global $canAuthor_folders, $canDelete_folders;
    global $company_id, $project_id, $task_id;
    global $allowedCompanies, $allowedProjects, $allowedTasks, $allowedFolders;
    global $showProject, $cfObj, $dPconfig;
    $df = $AppUI->getPref('SHDATEFORMAT');
    $tf = $AppUI->getPref('TIMEFORMAT');
    $file_types = dPgetSysVal('FileType');
    $xpg_pagesize = 30;
    //TODO?: Set by System Config Value ...
    $xpg_totalrecs = countFiles($folder_id);
    //get file count for folder
    $xpg_total_pages = $xpg_totalrecs > $xpg_pagesize ? ceil($xpg_totalrecs / $xpg_pagesize) : 1;
    $xpg_min = $xpg_pagesize * ($page - 1);
    // This is where we start our record set from
    $q = new DBQuery();
    // most recent version info per file_project and file_version_id
    $q->createTemp('files_count_max' . $folder_id);
    $q->addTable('files', 'f');
    $q->addQuery('DISTINCT count(f.file_id) as file_versions' . ', max(f.file_version) as file_lastversion' . ', file_version_id, f.file_project');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
        $q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
    }
    if (count($allowedFolders)) {
        $q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('co.company_id = ' . $company_id);
        if (count($allowedCompanies)) {
            $q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
        }
    }
    $q->addGroup('f.file_version_id');
    $q->addGroup('f.file_project');
    $file_version_max_counts = $q->exec();
    $q->clear();
    // most recent version
    $q->addTable('files', 'f');
    $q->addQuery('f.*, fmc.file_versions, round(fmc.file_lastversion, 2) as file_lastversion' . ', u.user_username as file_owner, ff.file_folder_name' . ', ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner' . ', cc.contact_first_name as checkout_first_name' . ', cc.contact_last_name as checkout_last_name');
    $q->addJoin('files_count_max' . $folder_id, 'fmc', '(fmc.file_lastversion=f.file_version AND fmc.file_version_id=f.file_version_id' . ' AND fmc.file_project=f.file_project)', 'inner');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('users', 'u', 'u.user_id = f.file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout');
    $q->leftJoin('contacts', 'cc', 'cc.contact_id = cu.user_contact');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
        $q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
    }
    if (count($allowedFolders)) {
        $q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
    }
    if ($project_id) {
        $q->addWhere('f.file_project = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('f.file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('co.company_id = ' . $company_id);
        if (count($allowedCompanies)) {
            $q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
        }
    }
    $q->addOrder('p.project_name');
    $q->setLimit($xpg_pagesize, $xpg_min);
    $files_sql = $q->prepare();
    $q->clear();
    // all versions
    $q->addTable('files', 'f');
    $q->addQuery('f.*, ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner');
    $q->addJoin('projects', 'p', 'p.project_id = f.file_project');
    $q->addJoin('users', 'u', 'u.user_id = f.file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = f.file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
    $q->addWhere('f.file_folder = ' . $folder_id);
    if (count($allowedProjects)) {
        $q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
    }
    if (count($allowedTasks)) {
//.........这里部分代码省略.........

function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $cBuffer, $company, $company_id, $company_prefix, $deny, $department;
    global $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems;
    global $tasks_sum, $tasks_summy, $tasks_total, $owner, $projectTypeId, $project_status;
    global $currentTabId;
    $addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;
    //for getting permissions on project records
    $obj_project = new CProject();
    // Let's delete temproary tables
    $q = new DBQuery();
    $table_list = array('tasks_sum', 'tasks_total', 'tasks_summy', 'tasks_critical', 'tasks_problems', 'tasks_users');
    $q->dropTemp($table_list);
    $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', 't');
    $q->addQuery('t.task_project, SUM(t.task_duration * t.task_percent_complete' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) / SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_percent_complete, SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_duration');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addWhere('t.task_id = t.task_parent');
    $q->addGroup('t.task_project');
    $tasks_sum = $q->exec();
    $q->clear();
    // At this stage tasks_sum contains the project id, and the total of tasks as percentage complate and project duration.
    // I.e. one record per project
    // Task total table
    $q->createTemp('tasks_total');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, COUNT(distinct t.task_id) AS total_tasks');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addGroup('t.task_project');
    $tasks_total = $q->exec();
    $q->clear();
    // tasks_total contains the total number of tasks for each project.
    // temporary My Tasks
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $q->createTemp('tasks_summy');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, COUNT(DISTINCT t.task_id) AS my_tasks');
    $q->addWhere('t.task_owner = ' . ($user_id ? $user_id : $AppUI->user_id));
    $q->addGroup('t.task_project');
    $tasks_summy = $q->exec();
    $q->clear();
    // tasks_summy contains total count of tasks for each project that I own.
    // temporary critical tasks
    $q->createTemp('tasks_critical');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, t.task_id AS critical_task' . ', MAX(t.task_end_date) AS project_actual_end_date');
    // MerlinYoda: we don't join tables if we don't get anything out of the process
    // $q->addJoin('projects', 'p', 'p.project_id = t.task_project');
    $q->addOrder('t.task_end_date DESC');
    $q->addGroup('t.task_project');
    $tasks_critical = $q->exec();
    $q->clear();
    // tasks_critical contains the latest ending task and its end date.
    // temporary task problem logs
    $q->createTemp('tasks_problems');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, tl.task_log_problem');
    $q->addJoin('task_log', 'tl', 'tl.task_log_task = t.task_id');
    $q->addWhere('tl.task_log_problem > 0');
    $q->addGroup('t.task_project');
    $tasks_problems = $q->exec();
    $q->clear();
    // tasks_problems contains an indication of any projects that have task logs set to problem.
    if ($addProjectsWithAssignedTasks) {
        // temporary users tasks
        $q->createTemp('tasks_users');
        $q->addTable('tasks', 't');
        $q->addQuery('t.task_project, ut.user_id');
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        if ($user_id) {
            $q->addWhere('ut.user_id = ' . $user_id);
        }
        $q->addOrder('t.task_end_date DESC');
        $q->addGroup('t.task_project');
        $tasks_users = $q->exec();
        $q->clear();
    }
    // tasks_users contains all projects with tasks that have user assignments. (isn't this getting pointless?)
    // add Projects where the Project Owner is in the given department
    if ($addPwOiD && isset($department)) {
        $owner_ids = array();
        $q->addTable('users', 'u');
        $q->addQuery('u.user_id');
        $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
        $q->addWhere('c.contact_department = ' . $department);
//.........这里部分代码省略.........

    $q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($category_filter) {
    $q->addWhere($category_filter);
}
if ($company_id) {
    $q->addWhere('p.project_company = ' . $company_id);
}
if ($project_id) {
    $q->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
    $q->addWhere('f.file_task = ' . $task_id);
}
// most recent version info per file_project and file_version_id
$r->createTemp('files_count_max');
$r->addTable('files', 'f');
$r->addQuery('DISTINCT count(f.file_id) as file_versions' . ', max(f.file_version) as file_lastversion' . ', f.file_version_id, f.file_project');
$r->addJoin('projects', 'p', 'p.project_id = f.file_project');
$r->addJoin('tasks', 't', 't.task_id = f.file_task');
$r->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
if (count($allowedProjects)) {
    $r->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
    $r->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
    $r->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($company_id) {

$orderby = $AppUI->getState('ProjIdxOrderBy') ? $AppUI->getState('ProjIdxOrderBy') : 'project_end_date';
$orderdir = $AppUI->getState('ProjIdxOrderDir') ? $AppUI->getState('ProjIdxOrderDir') : 'asc';
// 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_summy, tasks_critical, tasks_problems');
$q->exec();
$q->clear();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$working_hours = $dPconfig['daily_working_hours'];
// 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, COUNT(distinct task_id) AS total_tasks, \n\t\tSUM(task_duration * task_percent_complete * IF(task_duration_type = 24, " . $working_hours . ", task_duration_type))/\n\t\tSUM(task_duration * IF(task_duration_type = 24, " . $working_hours . ", task_duration_type)) AS project_percent_complete");
$q->addGroup('task_project');
$tasks_sum = $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');
$q->addWhere("task_owner = {$AppUI->user_id}");
$q->addGroup('task_project');
$tasks_summy = $q->exec();
$q->clear();

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

<?php

## Active Projects View for Calendar
## based on Companies: View Projects sub-table by gregorerhardt
##
global $AppUI, $company_id, $pstatus, $dPconfig;
$df = $AppUI->getPref('SHDATEFORMAT');
$project_types = dPgetSysVal('ProjectStatus');
// get any records denied from viewing
$obj = new CProject();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q = new DBQuery();
$q->createTemp('tasks_sum');
$q->addQuery('task_project, COUNT(DISTINCT task_id) AS total_tasks,
			SUM(task_duration * task_duration_type * task_percent_complete) / SUM(task_duration * task_duration_type) AS project_percent_complete');
$q->addTable('tasks');
$q->addGroup('task_project');
$q->exec();
$q->clear();
// temporary My Tasks
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q->createTemp('tasks_summy');
$q->addQuery('task_project, COUNT(DISTINCT task_id) AS my_tasks');
$q->addTable('tasks');
$q->addWhere('task_owner = ' . $AppUI->user_id);
$q->addGroup('task_project');
$q->exec();
$q->clear();