PHP DBQuery::dropTemp方法代码示例

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->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 ($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) . ')');
        }
    }
    $file_versions_sql = $q->prepare();
    $q->clear();
    //file arrays
    $files = array();
    $file_versions = array();
    if ($canRead) {
        $files = db_loadList($files_sql);
        $file_versions = db_loadHashList($file_versions_sql, 'file_id');
    }
    $q->dropTemp('files_count_max' . $folder_id);
    $q->exec();
    if ($files == array()) {
        return;
    }
    ?>
	<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
	<tr>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('File Name');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Description');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Versions');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Category');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Task Name');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Owner');
    ?>
</th>
		<th nowrap="nowrap"><?php 

}
if ($project_id) {
    $q3->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
    $q3->addWhere('f.file_task = ' . $task_id);
}
$files = array();
$file_versions = array();
if ($canRead) {
    $q2->includeCount();
    $files = $q2->loadList();
    $xpg_totalrecs = $q2->foundRows();
    $file_versions = $q3->loadHashList('file_id');
}
$r->dropTemp('files_count_max');
$r->exec();
// How many pages are we dealing with here ??
$xpg_total_pages = $xpg_totalrecs > $xpg_pagesize ? ceil($xpg_totalrecs / $xpg_pagesize) : 1;
shownavbar($xpg_totalrecs, $xpg_pagesize, $xpg_total_pages, $page);
?>
<script type="text/JavaScript">
function expand(id) {
  var element = document.getElementById(id);
  element.style.display = (element.style.display == '' || element.style.display == "none") ? "block" : "none";
}
</script>

<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
<tr>
	<th nowrap="nowrap">&nbsp;</th>

    $AppUI->setState('ProjIdxCompany', $company_id);
    unset($department);
}
if (isset($_GET['orderby'])) {
    $orderdir = $AppUI->getState('ProjIdxOrderDir') ? $AppUI->getState('ProjIdxOrderDir') == 'asc' ? 'desc' : 'asc' : 'desc';
    $AppUI->setState('ProjIdxOrderBy', $_GET['orderby']);
    $AppUI->setState('ProjIdxOrderDir', $orderdir);
}
$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)

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

## Active Projects View for Calendar
## based on Companies: View Projects sub-table by gregorerhardt
##
global $AppUI, $company_id, $pstatus, $dPconfig, $project_types, $priority, $company_id;
$df = $AppUI->getPref('SHDATEFORMAT');
$project_types = dPgetSysVal('ProjectStatus');
require_once $AppUI->getModuleClass('projects');
require DP_BASE_DIR . '/functions/projects_func.php';
// 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->dropTemp('tasks_sum, tasks_summy');
$q->exec();
$q->clear();
$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');

## Active Projects View for Calendar
## based on Companies: View Projects sub-table by gregorerhardt
##
global $AppUI, $company_id, $pstatus, $dPconfig, $project_types, $priority, $company_id;
$df = $AppUI->getPref('SHDATEFORMAT');
$project_types = dPgetSysVal('ProjectStatus');
require_once $AppUI->getModuleClass('projects');
require DP_BASE_DIR . '/functions/projects_func.php';
// 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->dropTemp(array('tasks_sum', 'tasks_summy'));
$q->exec();
$q->clear();
$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');