PHP w2p_Database_Query::addOrder方法代码示例

 public function getStructure($moduleName)
 {
     $q = new w2p_Database_Query();
     $q->addTable('custom_fields_struct');
     $q->addWhere("field_module = '{$moduleName}'");
     $q->addOrder('field_order ASC');
     return $q->loadList();
 }

 public function getMessages(CAppUI $AppUI, $forum_id = 0, $message_id = 0, $sortDir = 'asc')
 {
     $q = new w2p_Database_Query();
     $q->addTable('forums');
     $q->addTable('forum_messages');
     $q->addQuery('forum_messages.*,	contact_first_name, contact_last_name, contact_email,
         contact_display_name, user_username, forum_moderated, visit_user');
     $q->addJoin('forum_visits', 'v', 'visit_user = ' . (int) $AppUI->user_id . ' AND visit_forum = ' . (int) $forum_id . ' AND visit_message = forum_messages.message_id');
     $q->addJoin('users', 'u', 'message_author = u.user_id', 'inner');
     $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
     $q->addWhere('forum_id = message_forum AND (message_id = ' . (int) $message_id . ' OR message_parent = ' . (int) $message_id . ')');
     $q->addOrder('message_date ' . $sortDir);
     return $q->loadList();
 }

 public function load()
 {
     global $db;
     $q = new w2p_Database_Query();
     $q->addTable('custom_fields_lists');
     $q->addWhere('field_id = ' . $this->field_id);
     $q->addOrder('list_value');
     if (!$q->exec()) {
         return $db->ErrorMsg();
     }
     while ($opt_row = $q->fetchRow()) {
         $this->options[$opt_row['list_option_id']] = $opt_row['list_value'];
     }
 }

/** 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 w2p_Database_Query();
        $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();
    }
}

 public function w2Psearch_acl($application = 'application', $op, $user = 'user', $userid, $module)
 {
     global $w2p_performance_acltime, $w2p_performance_aclchecks;
     $q = new w2p_Database_Query();
     $q->addTable($this->_db_acl_prefix . 'permissions');
     $q->addQuery('acl_id, access, item_id');
     $q->addWhere('module = \'' . $module . '\'');
     $q->addWhere('action = \'' . $op . '\'');
     $q->addWhere('user_id = ' . (int) $userid);
     $q->addOrder('acl_id DESC');
     if (W2P_PERFORMANCE_DEBUG) {
         $startTime = array_sum(explode(' ', microtime()));
     }
     $res = $q->loadList();
     if (W2P_PERFORMANCE_DEBUG) {
         ++$w2p_performance_aclchecks;
         $w2p_performance_acltime += array_sum(explode(' ', microtime())) - $startTime;
     }
     return $res;
 }

    if (!$log_all) {
        $q->addWhere('task_log_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'');
        $q->addWhere('task_log_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\'');
    }
    if ($log_ignore) {
        $q->addWhere('task_log_hours > 0');
    }
    if ($log_userfilter) {
        $q->addWhere('task_log_creator = ' . (int) $log_userfilter);
    }
    $proj = new CProject();
    $allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'task_project');
    if (count($allowedProjects)) {
        $q->addWhere(implode(' AND ', $allowedProjects));
    }
    $q->addOrder('creator');
    $q->addOrder('company_name');
    $q->addOrder('project_name');
    $q->addOrder('task_log_date');
    $logs = $q->loadList();
    echo db_error();
    ?>
	<table cellspacing="1" cellpadding="4" border="0" class="tbl">
	<tr>
		<th><?php 
    echo $AppUI->_('Creator');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Company');
    ?>

function getStructuredProjects($original_project_id = 0, $project_status = -1, $active_only = false)
{
    global $AppUI, $st_projects_arr;
    $st_projects = array(0 => '');
    $q = new w2p_Database_Query();
    $q->addTable('projects');
    $q->addJoin('companies', '', 'projects.project_company = company_id', 'inner');
    $q->addQuery('DISTINCT(projects.project_id), project_name, project_parent');
    if ($original_project_id) {
        $q->addWhere('project_original_parent = ' . (int) $original_project_id);
    }
    if ($project_status >= 0) {
        $q->addWhere('project_status = ' . (int) $project_status);
    }
    if ($active_only) {
        $q->addWhere('project_active = 1');
    }
    $q->addOrder('project_start_date, project_end_date');
    $obj = new CCompany();
    $obj->setAllowedSQL($AppUI->user_id, $q);
    $dpt = new CDepartment();
    $dpt->setAllowedSQL($AppUI->user_id, $q);
    $q->leftJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
    $q->leftJoin('departments', 'd', 'd.dept_id = pd.department_id');
    $st_projects = $q->loadList();
    $tnums = count($st_projects);
    for ($i = 0; $i < $tnums; $i++) {
        $st_project = $st_projects[$i];
        if ($st_project['project_parent'] == $st_project['project_id']) {
            show_st_project($st_project);
            find_proj_child($st_projects, $st_project['project_id']);
        }
    }
}

$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'p.project_id');
$where_list = count($allowedProjects) ? implode(' AND ', $allowedProjects) : '';
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q = new w2p_Database_Query();
$q->addTable('projects', 'p');
$q->addQuery('company_name, p.project_id, project_color_identifier, project_name, project_percent_complete');
$q->addJoin('companies', 'com', 'company_id = project_company', 'inner');
$q->addJoin('tasks', 't1', 'p.project_id = t1.task_project', 'inner');
$q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
$q->addWhere($where_list . ($where_list ? ' AND ' : '') . 't1.task_id = t1.task_parent');
$q->addGroup('p.project_id');
if (!$project_id && !$task_id) {
    $q->addOrder('project_name');
}
if ($project_id > 0) {
    $q->addWhere('p.project_id = ' . $project_id);
}
$q2 = new w2p_Database_Query();
$q2->addTable('projects');
$q2->addQuery('project_id, COUNT(t1.task_id) AS total_tasks');
$q2->addJoin('tasks', 't1', 'projects.project_id = t1.task_project', 'inner');
if ($where_list) {
    $q2->addWhere($where_list);
}
if ($project_id > 0) {
    $q2->addWhere('project_id = ' . $project_id);
}
$q2->addGroup('project_id');

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

// Check permissions
$perms =& $AppUI->acl();
if (!canEdit('system')) {
    $AppUI->redirect('m=public&a=access_denied');
}
$q = new w2p_Database_Query();
$q->addTable('billingcode', 'bc');
$q->addQuery('billingcode_id, billingcode_name, billingcode_value, billingcode_desc, billingcode_status');
$q->addOrder('billingcode_name ASC');
$q->addWhere('company_id = ' . (int) $company_id);
$billingcodes = $q->loadList();
$q->clear();
$q = new w2p_Database_Query();
$q->addTable('companies', 'c');
$q->addQuery('company_id, company_name');
$q->addOrder('company_name ASC');
$company_list = $q->loadHashList();
$company_list[0] = $AppUI->_('Select Company');
$q->clear();
$company_name = $company_list[$company_id];
$titleBlock = new CTitleBlock('Edit Billing Codes', 'myevo-weather.png', $m, $m . '.' . $a);
$titleBlock->addCrumb('?m=system', 'system admin');
$titleBlock->show();
?>
<script language="javascript" type="text/javascript">
<!--
function submitIt(){
	var form = document.changeuser;
	form.submit();
}

 public function getAllowedTaskList($AppUI, $task_project = 0)
 {
     $results = array();
     $q = new w2p_Database_Query();
     $q->addQuery('task_id, task_name, task_parent, task_access, task_owner');
     $q->addQuery('task_start_date, task_end_date, task_percent_complete');
     $q->addOrder('task_parent, task_parent = task_id desc');
     $q->addTable('tasks', 't');
     if ($task_project) {
         $q->addWhere('task_project = ' . (int) $task_project);
     }
     if ($orderby == '') {
         $q->addOrder('task_parent, task_parent = task_id desc');
     } else {
         $q->addOrder($orderby);
     }
     $task_list = $q->loadList();
     foreach ($task_list as $task) {
         if (canTaskAccess($task['task_id'], $task['task_access'], $task['task_owner'])) {
             $results[] = $task;
         }
     }
     return $results;
 }

// check permissions
$perms =& $AppUI->acl();
$canEdit = canEdit('system');
$canRead = canView('system');
if (!$canRead) {
    $AppUI->redirect('m=public&a=access_denied');
}
$AppUI->savePlace();
$hidden_modules = array('public', 'install');
$q = new w2p_Database_Query();
$q->addQuery('*');
$q->addTable('modules');
foreach ($hidden_modules as $no_show) {
    $q->addWhere('mod_directory <> \'' . $no_show . '\'');
}
$q->addOrder('mod_ui_order');
$modules = $q->loadList();
// 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="100%" class="tbl">
    <tr>
        <th colspan="2"><?php 
echo $AppUI->_('Module');
?>
</th>
        <th><?php 

                $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 w2p_Database_Query();
$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 = w2PgetParam($_REQUEST, 'tab', 0);
?>

<table cellpadding="2" cellspacing="1" border="0" width="100%" class="tbl">
    <tr>
        <th colspan="2">&nbsp; <?php 
echo $AppUI->_('sort by');
?>
:&nbsp;</th>
        <?php 
$fieldList = array('user_username', 'contact_last_name', 'company_name', 'date_time_in', 'user_ip');
$fieldNames = array('Login Name', 'Real Name', 'Company', 'Date Time IN', 'Internet Address');
foreach ($fieldNames as $index => $name) {

 public function _buildQuery()
 {
     $q = new w2p_Database_Query();
     if ($this->table_alias) {
         $q->addTable($this->table, $this->table_alias);
     } else {
         $q->addTable($this->table);
     }
     $q->addQuery('DISTINCT(' . $this->table_key . ')');
     if (isset($this->table_key2)) {
         $q->addQuery($this->table_key2);
     }
     //--MSy--
     foreach ($this->table_joins as $join) {
         $q->addJoin($join['table'], $join['alias'], $join['join']);
     }
     foreach ($this->display_fields as $fld) {
         $q->addQuery($fld);
     }
     $q->addOrder($this->table_orderby);
     if ($this->table_groupby) {
         $q->addGroup($this->table_groupby);
     }
     if ($this->table_extra) {
         $q->addWhere($this->table_extra);
     }
     $ignore = w2PgetSysVal('FileIndexIgnoreWords');
     $ignore = explode(',', $ignore['FileIndexIgnoreWords']);
     $this->keywords = array_diff(array_keys($this->keywords), $ignore);
     $sql = '';
     foreach ($this->keywords as $keyword) {
         $sql .= '(';
         foreach ($this->search_fields as $field) {
             //OR treatment to each keyword
             // Search for semi-colons, commas or spaces and allow any to be separators
             $or_keywords = preg_split('/[\\s,;]+/', $keyword);
             foreach ($or_keywords as $or_keyword) {
                 if ($this->search_options['ignore_specchar'] == 'on') {
                     $tmppattern = recode2regexp_utf8($or_keyword);
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' REGEXP \'' . $tmppattern . '\' or ';
                     } else {
                         $sql .= ' ' . $field . ' REGEXP BINARY \'' . $tmppattern . '\' or ';
                     }
                 } else {
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' LIKE "%' . $or_keyword . '%" or ';
                     } else {
                         $sql .= ' ' . $field . ' LIKE BINARY "%' . $or_keyword . '%" or ';
                     }
                 }
             }
         }
         // foreach $field
         $sql = substr($sql, 0, -4);
         if ($this->search_options['all_words'] == 'on') {
             $sql .= ') and ';
         } else {
             $sql .= ') or ';
         }
     }
     // foreach $keyword
     //--MSy--
     $sql = substr($sql, 0, -4);
     if ($sql) {
         $q->addWhere($sql);
         return $q;
     } else {
         return null;
     }
 }

 public function getTaskLogs(CAppUI $AppUI = null, $projectId, $user_id = 0, $hide_inactive = false, $hide_complete = false, $cost_code = 0)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->addTable('task_log');
     $q->addQuery('DISTINCT task_log.*, user_username, task_id');
     $q->addQuery("CONCAT(contact_first_name, ' ', contact_last_name) AS real_name");
     $q->addQuery('billingcode_name as task_log_costcode');
     $q->addJoin('users', 'u', 'user_id = task_log_creator');
     $q->addJoin('tasks', 't', 'task_log_task = t.task_id');
     $q->addJoin('contacts', 'ct', 'contact_id = user_contact');
     $q->addJoin('billingcode', 'b', 'task_log.task_log_costcode = billingcode_id');
     $q->addWhere('task_project = ' . (int) $projectId);
     if ($user_id > 0) {
         $q->addWhere('task_log_creator=' . $user_id);
     }
     if ($hide_inactive) {
         $q->addWhere('task_status>=0');
     }
     if ($hide_complete) {
         $q->addWhere('task_percent_complete < 100');
     }
     if ($cost_code > 0) {
         $q->addWhere("billingcode_id = {$cost_code}");
     }
     $q->addOrder('task_log_date');
     $q->addOrder('task_log_created');
     $this->setAllowedSQL($AppUI->user_id, $q, 'task_project');
     return $q->loadList();
 }