PHP DBQuery::addOrder方法代码示例

function getFolderSelectList()
{
    global $AppUI;
    $folders = array(0 => '');
    $q = new DBQuery();
    $q->addTable('file_folders');
    $q->addQuery('file_folder_id, file_folder_name, file_folder_parent');
    $q->addOrder('file_folder_name');
    $sql = $q->prepare();
    //	$sql = "SELECT file_folder_id, file_folder_name, file_folder_parent FROM file_folders";
    $vfolders = arrayMerge(array('0' => array(0, $AppUI->_('Root'), -1)), db_loadHashList($sql, 'file_folder_id'));
    $folders = array_filter($vfolders, "check_perm");
    return $folders;
}

/** Retrieve tasks with first task_end_dates within given project
 * @param int Project_id
 * @param int SQL-limit to limit the number of returned tasks
 * @return array List of criticalTasks
 */
function getCriticalTasksInverted($project_id = null, $limit = 1)
{
    if (!$project_id) {
        $result = array();
        $result[0]['task_end_date'] = '0000-00-00 00:00:00';
        return $result;
    } else {
        $q = new DBQuery();
        $q->addTable('tasks');
        $q->addWhere('task_project = ' . (int) $project_id . ' AND NOT ISNULL( task_end_date ) AND task_end_date <>  \'0000-00-00 00:00:00\'');
        $q->addOrder('task_start_date ASC');
        $q->setLimit($limit);
        return $q->loadList();
    }
}

/** Retrieve tasks with first task_end_dates within given project
* @param int Project_id
* @param int SQL-limit to limit the number of returned tasks
* @return array List of criticalTasks
*/
function getCriticalTasksInverted($project_id = NULL, $limit = 1)
{
    if (!$project_id) {
        $result = array();
        $result[0]['task_end_date'] = '0000-00-00 00:00:00';
        return $result;
    } else {
        $q = new DBQuery();
        $q->addTable('tasks');
        $q->addWhere("task_project = {$project_id} AND !isnull( task_end_date ) AND task_end_date !=  '0000-00-00 00:00:00'");
        $q->addOrder('task_start_date ASC');
        $q->setLimit($limit);
        return $q->loadList();
    }
}

function getAllUsersGroupByDept()
{
    $q = new DBQuery();
    $q->addTable('users');
    $q->addQuery('user_id, contact_department, concat_ws(", ", contact_last_name, contact_first_name) as contact_name');
    $q->addJoin('contacts', 'con', 'contact_id = user_contact');
    $q->addOrder('contact_last_name');
    $res = $q->exec();
    $userlist = array();
    while ($row = $q->fetchRow()) {
        if ($row['contact_department'] == null) {
            $row['contact_department'] = 0;
        }
        if (!isset($userlist[$row['contact_department']])) {
            $userlist[$row['contact_department']] = array();
        }
        $userlist[$row['contact_department']][$row['user_id']] = $row['contact_name'];
    }
    $q->clear();
    return $userlist;
}

    if ($company_id) {
        $q = new DBQuery();
        $q->addTable('departments', 'dep');
        $q->addQuery('dept_id, dept_name, dept_parent');
        $q->addWhere('dep.dept_company = ' . $company_id);
        $q->addWhere('dep.dept_id != ' . $dept_id);
        $depts = $q->loadArrayList();
        $depts['0'] = array(0, '- ' . $AppUI->_('Select Unit') . ' -', -1);
    }
    // collect all the users for the department owner list
    $q = new DBQuery();
    $q->addTable('users', 'u');
    $q->addTable('contacts', 'con');
    $q->addQuery('user_id');
    $q->addQuery('CONCAT_WS(", ",contact_last_name, contact_first_name)');
    $q->addOrder('contact_first_name');
    $q->addWhere('u.user_contact = con.contact_id');
    $q->addOrder('contact_last_name, contact_first_name');
    $owners = arrayMerge(array('0' => ''), $q->loadHashList());
    // setup the title block
    $ttl = $company_id > 0 ? "Edit Department" : "Add Department";
    $titleBlock = new CTitleBlock($ttl, 'users.gif', $m, "{$m}.{$a}");
    $titleBlock->addCrumb("?m=companies", "companies list");
    $titleBlock->addCrumb("?m=companies&a=view&company_id={$company_id}", "view this company");
    $titleBlock->show();
    ?>
<script language="javascript">
function testURL( x ) {
	var test = "document.editFrm.dept_url.value";
	test = eval(test);
	if (test.length > 6) {

        break;
    case "departments":
        $id_field = "dept_id";
        $name_field = "dept_name";
        $selection_string = "Department";
        $filter = "dept_company = " . $_GET["company_id"];
        $additional_get_information = "company_id=" . $_GET["company_id"];
        break;
}
$q = new DBQuery();
$q->addTable($table_name);
$q->addQuery("{$id_field}, {$name_field}");
if ($filter != null) {
    $q->addWhere($filter);
}
$q->addOrder($name_field);
$company_list = array("0" => "") + $q->loadHashList();
?>

<?php 
if (dPgetParam($_POST, $id_field, 0) != 0) {
    $q = new DBQuery();
    $q->addTable($table_name);
    $q->addQuery('*');
    $q->addWhere("{$id_field}=" . $_POST[$id_field]);
    $sql = $q->prepare();
    $q->clear();
    db_loadHash($sql, $r_data);
    $data_update_script = "";
    $update_address = isset($_POST["overwrite_address"]);
    if ($table_name == "companies") {

                $r->clear();
            }
        }
    } while ($boot_query_row);
    $msg = $boot_user_name . ' logged out by ' . $AppUI->user_first_name . ' ' . $AppUI->user_last_name;
    $AppUI->setMsg($msg, UI_MSG_OK);
    $AppUI->redirect('m=admin&tab=3');
}
$q = new DBQuery();
$q->addTable('sessions', 's');
$q->addQuery('DISTINCT(session_id), user_access_log_id, u.user_id as u_user_id, user_username,' . ' contact_last_name, contact_first_name, company_name, contact_company,' . ' date_time_in, user_ip');
$q->addJoin('user_access_log', 'ual', 'session_user = user_access_log_id');
$q->addJoin('users', 'u', 'ual.user_id = u.user_id');
$q->addJoin('contacts', 'con', 'u.user_contact = contact_id');
$q->addJoin('companies', 'com', 'contact_company = company_id');
$q->addOrder($orderby);
$rows = $q->loadList();
$q->clear();
$tab = (int) dPgetParam($_REQUEST, 'tab', 0);
?>
<script language="JavaScript" type="text/javascript">
//<![CDATA[
  function logoutSession(sessionID, logID, userID, userName) {
  var frm;
  frm = document.getElementById("frmSessions");
  frm.out_session.value = sessionID;
  frm.out_user_log_id.value = logID;
  frm.out_user_id.value = userID;
  frm.out_name.value = userName;
  frm.submit();
}

if (!$canEdit) {
    $AppUI->redirect("m=public&a=access_denied");
}
// use the object oriented design of dP for loading the log that should be edited
// therefore create a new instance of the Timesheet Class
$obj = new CTimesheet();
$df = $AppUI->getPref('SHDATEFORMAT');
// pull users
// pull users
$q = new DBQuery();
$q->addTable('tasks', 't');
$q->addTable('projects', 'p');
$q->addTable('user_tasks', 'u');
$q->addQuery('t.task_id');
$q->addQuery('CONCAT_WS(" - ",p.project_short_name, t.task_name)');
$q->addOrder('p.project_short_name, t.task_name');
$q->addWhere('t.task_project = p.project_id and t.task_dynamic = 0 and t.task_percent_complete!=100 and u.task_id=t.task_id and u.user_id=' . $user_id);
//Devido a possibilidade de edição de registros, as tarefas de projetos arquivados e em espera serão apresentadas.
//$q->addWhere('p.project_status!=7 and p.project_status!=4');//[7] Projetos Arquivados e [4] Projetos Em Espera
$tasks = $q->loadHashList();
// load the record data in case of that this script is used to edit the log qith task_log_id (transmitted via GET)
if (!$obj->load($task_log_id, false) && $task_log_id > 0) {
    // show some error messages using the dPFramework if loadOperation failed
    // these error messages are nicely integrated with the frontend of dP
    // use detailed error messages as often as possible
    $AppUI->setMsg('Timesheet');
    $AppUI->setMsg("invalidID", UI_MSG_ERROR, true);
    $AppUI->redirect();
    // go back to the calling location
}
// check if this record has dependancies to prevent deletion

$q->addWhere('task_project = ' . $task_project);
$q->addWhere('task_id = task_parent');
$q->addOrder('task_start_date');
$sql = $q->prepare();
$q->clear();
$root_tasks = db_loadHashList($sql, 'task_id');
$projTasks = array();
global $task_parent_options;
$task_parent_options = "";
// Now lets get non-root tasks, grouped by the task parent
$q = new DBQuery();
$q->addQuery('task_id, task_name, task_end_date, task_start_date, task_milestone, task_parent, task_dynamic');
$q->addTable('tasks');
$q->addWhere('task_project = ' . $task_project);
$q->addWhere('task_id <> task_parent');
$q->addOrder('task_start_date');
$sql = $q->prepare();
$q->clear();
$parents = array();
$projTasksWithEndDates = array(0 => $AppUI->_('None'));
//arrays contains task end date info for setting new task start date as maximum end date of dependenced tasks
global $all_tasks;
$all_tasks = array();
$sub_tasks = db_exec($sql);
if ($sub_tasks) {
    while ($sub_task = db_fetch_assoc($sub_tasks)) {
        // Build parent/child task list
        $parents[$sub_task['task_parent']][] = $sub_task['task_id'];
        $all_tasks[$sub_task['task_id']] = $sub_task;
        build_date_list($projTasksWithEndDates, $sub_task);
    }

 /**
  *	Calculate the extent of utilization of user assignments
  *	@param string hash	 a hash for the returned hashList
  *	@param array users	 an array of user_ids calculating their assignment capacity
  *	@return array		 returns hashList of extent of utilization for assignment of the users
  */
 function getAllocation($hash = NULL, $users = NULL)
 {
     // if (! dPgetConfig('check_overallocation') && ! dPgetConfig('direct_edit_assignment')) {
     return array();
     //}
     $q = new DBQuery();
     // retrieve the systemwide default preference for the assignment maximum
     $q->addTable('user_preferences');
     $q->addQuery('pref_value');
     $q->addWhere("pref_user = 0 AND pref_name = 'TASKASSIGNMAX'");
     $sql = $q->prepare();
     $q->clear();
     $result = db_loadHash($sql, $sysChargeMax);
     if (!$result) {
         $scm = 0;
     } else {
         $scm = $sysChargeMax['pref_value'];
     }
     /*
      * provide actual assignment charge, individual chargeMax 
      * and freeCapacity of users' assignments to tasks
      */
     $q->addTable('users', 'u');
     $q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact');
     $q->leftJoin('user_tasks', 'ut', 'ut.user_id = u.user_id');
     $q->leftJoin('user_preferences', 'up', 'up.pref_user = u.user_id');
     $q->addQuery("u.user_id, CONCAT(CONCAT_WS(' [', CONCAT_WS(' '" . ', contact_first_name, contact_last_name), IF(IFNULL((IFNULL(up.pref_value' . ', ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value) > 0' . ', IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment))' . ', up.pref_value), 0)), ' . "'%]')" . ' AS userFC' . ', IFNULL(SUM(ut.perc_assignment), 0) AS charge, u.user_username' . ', IFNULL(up.pref_value,' . $scm . ') AS chargeMax' . ', IF(IFNULL((IFNULL(up.pref_value, ' . $scm . ') ' . '- SUM(ut.perc_assignment)), up.pref_value) > 0' . ', IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment))' . ', up.pref_value), 0) AS freeCapacity');
     if (!empty($users)) {
         // use userlist if available otherwise pull data for all users
         $q->addWhere('u.user_id IN (' . implode(',', $users) . ')');
     }
     $q->addGroup('u.user_id');
     $q->addOrder('contact_last_name, contact_first_name');
     $sql = $q->prepare();
     $q->clear();
     //echo "<pre>$sql</pre>";
     return db_loadHashList($sql, $hash);
 }

    die('You should not access this file directly.');
}
$AppUI->savePlace();
$canEdit = getPermission($m, 'edit');
$canRead = getPermission($m, 'view');
if (!$canRead) {
    $AppUI->redirect('m=public&a=access_denied');
}
$hidden_modules = array('public', 'install');
$q = new DBQuery();
$q->addQuery('*');
$q->addTable('modules');
foreach ($hidden_modules as $no_show) {
    $q->addWhere('mod_directory != \'' . $no_show . '\'');
}
$q->addOrder('mod_ui_order');
$modules = db_loadList($q->prepare());
// get the modules actually installed on the file system
$modFiles = $AppUI->readDirs('modules');
$titleBlock = new CTitleBlock('Modules', 'power-management.png', $m, $m . "." . $a);
$titleBlock->addCrumb('?m=system', 'System Admin');
$titleBlock->show();
?>

<table border="0" cellpadding="2" cellspacing="1" width="98%" class="tbl">
<tr>
	<th colspan="2"><?php 
echo $AppUI->_('Module');
?>
</th>
	<th><?php 

<?php

/* COMPANIES $Id: vw_depts.php,v 1.15.2.1 2006/07/31 11:54:15 nybod Exp $ */
##
##	Companies: View Projects sub-table
##
global $AppUI, $company_id, $canEdit;
$q = new DBQuery();
$q->addTable('departments');
$q->addQuery('departments.*, COUNT(contact_department) dept_users');
$q->addJoin('contacts', 'c', 'c.contact_department = dept_id');
$q->addWhere('dept_company = ' . $company_id);
$q->addGroup('dept_id');
$q->addOrder('dept_parent, dept_name');
$sql = $q->prepare();
$q->clear();
// function renamed to avoid naming clash
function showchilddept_comp(&$a, $level = 0)
{
    global $AppUI;
    $s = '
	<td>
		<a href="index.php?m=departments&a=addedit&dept_id=' . $a['dept_id'] . '" title="' . $AppUI->_('edit') . '">
			' . dPshowImage('images/edit.gif', 12, 12, '') . '
	</td>
	<td>';
    for ($y = 0; $y < $level; $y++) {
        if ($y + 1 == $level) {
            $s .= '<img src="images/corner-dots.gif" width="16" height="12" border="0">';
        } else {
            $s .= '<img src="images/shim.gif" width="16" height="12" border="0">';

 public function getAllowedRecords($uid)
 {
     global $AppUI;
     $q = new DBQuery();
     $q->addTable('file_folders');
     $q->addQuery('*');
     $q->addOrder('file_folder_parent');
     $q->addOrder('file_folder_name');
     return $q->loadHashList();
 }

function getDepartmentSelectionList($company_id, $checked_array = array(), $dept_parent = 0, $spaces = 0)
{
    global $departments_count;
    $parsed = '';
    if ($departments_count < 6) {
        $departments_count++;
    }
    $q = new DBQuery();
    $q->addTable('departments');
    $q->addQuery('dept_id, dept_name');
    $q->addWhere("dept_parent = '{$dept_parent}' and dept_company = '{$company_id}'");
    $q->addOrder('dept_name');
    $depts_list = $q->loadHashList("dept_id");
    foreach ($depts_list as $dept_id => $dept_info) {
        $selected = in_array($dept_id, $checked_array) ? ' selected="selected"' : '';
        $parsed .= '<option value="' . $dept_id . '"' . $selected . '>' . str_repeat('&nbsp;', $spaces) . $dept_info['dept_name'] . '</option>';
        $parsed .= getDepartmentSelectionList($company_id, $checked_array, $dept_id, $spaces + 5);
    }
    return $parsed;
}

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