PHP DBQuery::loadList方法代码示例

 function store()
 {
     $msg = $this->check();
     if ($msg) {
         return get_class($this) . "::store-check failed";
     }
     $q = new DBQuery();
     if ($this->user_id) {
         // save the old password
         $perm_func = "updateLogin";
         $q->addTable('users');
         $q->addQuery('user_password');
         $q->addWhere("user_id = {$this->user_id}");
         $pwd = $q->loadResult();
         if ($pwd != $this->user_password) {
             $this->user_password = md5($this->user_password);
         } else {
             $this->user_password = null;
         }
         $ret = db_updateObject('users', $this, 'user_id', false);
     } else {
         $perm_func = "addLogin";
         $this->user_password = md5($this->user_password);
         $ret = db_insertObject('users', $this, 'user_id');
     }
     if (!$ret) {
         return get_class($this) . "::store failed <br />" . db_error();
     } else {
         $acl =& $GLOBALS['AppUI']->acl();
         $acl->{$perm_func}($this->user_id, $this->user_username);
         //Insert Default Preferences
         //Lets check if the user has allready default users preferences set, if not insert the default ones
         $q->addTable('user_preferences', 'upr');
         $q->addWhere("upr.pref_user = {$this->user_id}");
         $uprefs = $q->loadList();
         $q->clear();
         if (!count($uprefs) && $this->user_id > 0) {
             //Lets get the default users preferences
             $q->addTable('user_preferences', 'dup');
             $q->addWhere("dup.pref_user = 0");
             $dprefs = $q->loadList();
             $q->clear();
             foreach ($dprefs as $dprefskey => $dprefsvalue) {
                 $q->addTable('user_preferences', 'up');
                 $q->addInsert('pref_user', $this->user_id);
                 $q->addInsert('pref_name', $dprefsvalue['pref_name']);
                 $q->addInsert('pref_value', $dprefsvalue['pref_value']);
                 $q->exec();
                 $q->clear();
             }
         }
         return NULL;
     }
 }

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

    }
    // show tasks which are both finished and past in (dark)gray
    if ($progress >= 100 && $end_date->isPast() && get_class($bar) == 'ganttbar') {
        $bar->caption->SetColor('darkgray');
        $bar->title->SetColor('darkgray');
        $bar->setColor('darkgray');
        $bar->SetFillColor('darkgray');
        $bar->SetPattern(BAND_SOLID, 'gray');
        $bar->progress->SetFillColor('darkgray');
        $bar->progress->SetPattern(BAND_SOLID, 'gray', 98);
    }
    $q = new DBQuery();
    $q->addTable('task_dependencies');
    $q->addQuery('dependencies_task_id');
    $q->addWhere('dependencies_req_task_id=' . (int) $a['task_id']);
    $query = $q->loadList();
    foreach ($query as $dep) {
        // find row num of dependencies
        for ($d = 0, $d_cmp = count($gantt_arr); $d < $d_cmp; $d++) {
            if ($gantt_arr[$d][0]['task_id'] == $dep['dependencies_task_id']) {
                $bar->SetConstrain($d, CONSTRAIN_ENDSTART);
            }
        }
    }
    unset($query);
    $q->clear();
    $graph->Add($bar);
}
unset($gantt_arr);
$today = new CDate();
$vline = new GanttVLine($today->format(FMT_TIMESTAMP_DATE), $AppUI->_('Today', UI_OUTPUT_RAW));

$q->leftJoin('project_departments', 'project_departments', 'projects.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('task_project = ' . (int) $project_id);
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'task_project');
if (count($allowedProjects)) {
    $q->addWhere($allowedProjects);
}
$obj = new CTask();
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id, 'tasks.task_id');
if (count($allowedTasks)) {
    $q->addWhere($allowedTasks);
}
$q->addGroup('tasks.task_id');
$q->addOrder('task_start_date');
if ($canViewTasks) {
    $tasks = $q->loadList();
}
// POST PROCESSING TASKS
foreach ($tasks as $row) {
    //add information about assigned users into the page output
    $q->clear();
    $q->addQuery('ut.user_id,	u.user_username');
    $q->addQuery('contact_email, ut.perc_assignment, SUM(ut.perc_assignment) AS assign_extent');
    $q->addQuery('contact_first_name, contact_last_name');
    $q->addTable('user_tasks', 'ut');
    $q->leftJoin('users', 'u', 'u.user_id = ut.user_id');
    $q->leftJoin('contacts', 'c', 'u.user_contact = c.contact_id');
    $q->addWhere('ut.task_id = ' . (int) $row['task_id']);
    $q->addGroup('ut.user_id');
    $q->addOrder('perc_assignment desc, user_username');
    $assigned_users = array();

 function notify($comment = '')
 {
     $q = new DBQuery();
     global $AppUI, $locale_char_set;
     $df = $AppUI->getPref('SHDATEFORMAT');
     $df .= ' ' . $AppUI->getPref('TIMEFORMAT');
     $q->addTable("projects");
     $q->addQuery("project_name");
     $q->addWhere("project_id={$this->task_project}");
     $projname = htmlspecialchars_decode(db_loadResult($q->prepare(true)));
     $mail = new Mail();
     $mail->Subject($projname . '::' . $this->task_name . ' ' . $AppUI->_($this->_action, UI_OUTPUT_RAW), $locale_char_set);
     // c = creator
     // a = assignee
     // o = owner
     $q->addTable('tasks', 't');
     $q->leftJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
     $q->leftJoin('users', 'o', 'o.user_id = t.task_owner');
     $q->leftJoin('contacts', 'oc', 'oc.contact_id = o.user_contact');
     $q->leftJoin('users', 'c', 'c.user_id = t.task_creator');
     $q->leftJoin('contacts', 'cc', 'cc.contact_id = c.user_contact');
     $q->leftJoin('users', 'a', 'a.user_id = ut.user_id');
     $q->leftJoin('contacts', 'ac', 'ac.contact_id = a.user_contact');
     $q->addQuery('t.task_id, c.user_id as creator_id, cc.contact_email as creator_email' . ', cc.contact_first_name as creator_first_name' . ', cc.contact_last_name as creator_last_name' . ', o.user_id as owner_id, oc.contact_email as owner_email' . ', oc.contact_first_name as owner_first_name' . ', oc.contact_last_name as owner_last_name' . ', a.user_id as assignee_id, ac.contact_email as assignee_email' . ', ac.contact_first_name as assignee_first_name' . ', ac.contact_last_name as assignee_last_name');
     $q->addWhere(' t.task_id = ' . $this->task_id);
     $users = $q->loadList();
     if (count($users)) {
         $task_start_date = new CDate($this->task_start_date);
         $task_finish_date = new CDate($this->task_end_date);
         $priority = dPgetSysVal('TaskPriority');
         $body = $AppUI->_('Project', UI_OUTPUT_RAW) . ': ' . $projname . "\n" . $AppUI->_('Task', UI_OUTPUT_RAW) . ':	 ' . $this->task_name;
         $body .= "\n" . $AppUI->_('Priority') . ': ' . $priority[$this->task_priority];
         $body .= "\n" . $AppUI->_('Start Date', UI_OUTPUT_RAW) . ': ' . $task_start_date->format($df) . "\n" . $AppUI->_('Finish Date', UI_OUTPUT_RAW) . ': ' . ($this->task_end_date != '' ? $task_finish_date->format($df) : '') . "\n" . $AppUI->_('URL', UI_OUTPUT_RAW) . ': ' . DP_BASE_URL . '/index.php?m=tasks&a=view&task_id=' . $this->task_id . "\n\n" . $AppUI->_('Description', UI_OUTPUT_RAW) . ': ' . "\n" . $this->task_description;
         if ($users[0]['creator_email']) {
             $body .= "\n\n" . $AppUI->_('Creator', UI_OUTPUT_RAW) . ':' . "\n" . $users[0]['creator_first_name'] . ' ' . $users[0]['creator_last_name'] . ', ' . $users[0]['creator_email'];
         }
         $body .= "\n\n" . $AppUI->_('Owner', UI_OUTPUT_RAW) . ':' . "\n" . $users[0]['owner_first_name'] . ' ' . $users[0]['owner_last_name'] . ', ' . $users[0]['owner_email'];
         if ($comment != '') {
             $body .= "\n\n" . $comment;
         }
         $mail->Body($body, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
         $mail->From('"' . $AppUI->user_first_name . ' ' . $AppUI->user_last_name . '" <' . $AppUI->user_email . '>');
         $owner_is_assigned = false;
         foreach ($users as $row) {
             if ($mail->ValidEmail($row['assignee_email'])) {
                 $mail->To($row['assignee_email'], true);
                 $mail->Send();
             }
             if ($row['assignee_id'] == $row['owner_id']) {
                 $owner_is_assigned = true;
             }
         }
         if ($AppUI->getPref('MAILALL') && !$owner_is_assigned) {
             $last_record = array_pop($users);
             $owner_email = $last_record['owner_email'];
             array_push($users, $last_record);
             if ($mail->ValidEmail($owner_email)) {
                 $mail->To($owner_email, true);
                 $mail->Send();
             }
         }
     }
     return '';
 }

function displayFiles($folder)
{
    global $m, $a, $tab, $AppUI, $xpg_min, $xpg_pagesize;
    global $deny1, $deny2, $project_id, $task_id, $showProject, $file_types, $cfObj;
    global $xpg_totalrecs, $xpg_total_pages, $page;
    global $company_id, $allowed_companies, $current_uri, $dPconfig;
    $canEdit = !getDenyEdit($m, $folder);
    $canRead = !getDenyRead($m, $folder);
    $df = $AppUI->getPref('SHDATEFORMAT');
    $tf = $AppUI->getPref('TIMEFORMAT');
    // SETUP FOR FILE LIST
    $q = new DBQuery();
    $q->addTable('files');
    $q->addQuery('files.*,count(file_version) as file_versions,round(max(file_version), 2) as file_lastversion,file_folder_id, file_folder_name,project_name, project_color_identifier,contact_first_name, contact_last_name,task_name,task_id');
    $q->addJoin('projects', 'p', 'p.project_id = file_project');
    $q->addJoin('users', 'u', 'u.user_id = file_owner');
    $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
    $q->addJoin('tasks', 't', 't.task_id = file_task');
    $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
    $q->addWhere('file_folder = ' . $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 = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('company_id = ' . $company_id);
        $q->addWhere('company_id IN (' . $allowed_companies . ')');
    }
    $q->addGroup('file_folder');
    $q->addGroup('project_name');
    $q->addGroup('file_name');
    $q->addOrder('file_folder');
    $q->addOrder('project_name');
    $q->addOrder('file_name');
    $q->setLimit($xpg_pagesize, $xpg_min);
    $files_sql = $q->prepare();
    $q->clear();
    $q = new DBQuery();
    $q->addTable('files');
    $q->addQuery('files.file_id, file_version, file_project, file_name, file_task, file_description, user_username as file_owner, file_size, file_category, file_type, file_date, file_folder_name');
    $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 = ' . $folder);
    if ($project_id) {
        $q->addWhere('file_project = ' . $project_id);
    }
    if ($task_id) {
        $q->addWhere('file_task = ' . $task_id);
    }
    if ($company_id) {
        $q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
        $q->addWhere('company_id = ' . $company_id);
        $q->addWhere('company_id IN (' . $allowed_companies . ')');
    }
    $file_versions_sql = $q->prepare();
    $q->clear();
    $files = array();
    $file_versions = array();
    if ($canRead) {
        $files = db_loadList($files_sql);
        $file_versions = db_loadList($file_versions_sql);
    }
    if ($files === array()) {
        return 0;
    }
    ?>
	<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
	<tr>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('File Name');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Description');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Versions');
    ?>
</th>
	    <th><?php 
    echo $AppUI->_('Category');
    ?>
</th>
		<th nowrap="nowrap"><?php 
    echo $AppUI->_('Task Name');
    ?>
</th>
		<th><?php 
//.........这里部分代码省略.........

if ($isNewUser) {
    // check if a user with the param Username already exists
    $userEx = FALSE;
    function userExistence($userName)
    {
        global $obj, $userEx;
        if ($userName == $obj->user_username) {
            $userEx = TRUE;
        }
    }
    //pull a list of existing usernames
    $sql = "SELECT user_username FROM users";
    $q = new DBQuery();
    $q->addTable('users', 'u');
    $q->addQuery('user_username');
    $users = $q->loadList();
    // Iterate the above userNameExistenceCheck for each user
    foreach ($users as $usrs) {
        $usrLst = array_map("userExistence", $usrs);
    }
    // If userName already exists quit with error and do nothing
    if ($userEx == TRUE) {
        $AppUI->setMsg("already exists. Try another username.", UI_MSG_ERROR, true);
        $AppUI->redirect();
    }
    $contact->contact_owner = $AppUI->user_id;
}
if ($msg = $contact->store()) {
    $AppUI->setMsg($msg, UI_MSG_ERROR);
} else {
    $obj->user_contact = $contact->contact_id;

function dPgetUsernameFromID($user)
{
    $q = new DBQuery();
    $q->addTable('users');
    $q->addQuery('contact_first_name, contact_last_name');
    $q->addJoin('contacts', 'con', 'contact_id = user_contact');
    $q->addWhere('user_id = \'' . $user . "'");
    $r = $q->loadList();
    return $r[0]['contact_first_name'] . ' ' . $r[0]['contact_last_name'];
}

global $m, $a, $project_id, $task_id, $f;
global $canEdit, $canAccessTask;
$q = new DBQuery();
$q->addTable('tasks');
$q->addQuery('task_id, task_parent, task_name, task_start_date, task_end_date,
	task_dynamic, task_milestone');
if (isset($project_id)) {
    $q->addWhere('task_project=' . $project_id);
} else {
    if ($task_id) {
        $q->addWhere('task_parent=' . $task_id);
        $q->addWhere('task_id !=' . $task_id);
    }
}
$q->addOrder('task_order');
$p['tasks'] = $q->loadList();
$q->clear();
if ($p['tasks']) {
    global $tasks_filtered, $children_of;
    //get list of task ids and set-up array of children
    foreach ($p['tasks'] as $i => $t) {
        $tasks_filtered[] = $t['task_id'];
        $children_of[$t['task_parent']] = $children_of[$t['task_parent']] ? $children_of[$t['task_parent']] : array();
        if ($t['task_parent'] != $t['task_id']) {
            array_push($children_of[$t['task_parent']], $t['task_id']);
        }
    }
    ?>

		
		<?php 

/* COMPANIES $Id: vw_users.php,v 1.12.4.2 2007/03/06 00:34:40 merlinyoda Exp $ */
if (!defined('DP_BASE_DIR')) {
    die('You should not access this file directly.');
}
##
##	Companies: View User sub-table
##
global $AppUI, $company_id;
$q = new DBQuery();
$q->addTable('users');
$q->addQuery('user_id, user_username, contact_first_name, contact_last_name');
$q->addJoin('contacts', 'c', 'users.user_contact = contact_id');
$q->addWhere('contact_company = ' . $company_id);
$q->addOrder('contact_last_name');
if (!($rows = $q->loadList())) {
    echo $AppUI->_('No data available') . '<br />' . $AppUI->getMsg();
} else {
    ?>
<table width="100%" border=0 cellpadding="2" cellspacing="1" class="tbl">
<tr>
	<th><?php 
    echo $AppUI->_('Username');
    ?>
</td>
	<th><?php 
    echo $AppUI->_('Name');
    ?>
</td>
</tr>
<?php 

 public function w2Psearch_acl($application = 'application', $op, $user = 'user', $userid, $module)
 {
     global $w2p_performance_acltime, $w2p_performance_aclchecks;
     $q = new DBQuery();
     $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;
 }

 public function getCompanyList($AppUI, $companyType = -1, $searchString = '', $ownerId = 0, $orderby = 'company_name', $orderdir = 'ASC')
 {
     $q = new DBQuery();
     $q->addTable('companies', 'c');
     $q->addQuery('c.company_id, c.company_name, c.company_type, c.company_description, count(distinct p.project_id) as countp, count(distinct p2.project_id) as inactive, con.contact_first_name, con.contact_last_name');
     $q->addJoin('projects', 'p', 'c.company_id = p.project_company AND p.project_active = 1');
     $q->addJoin('users', 'u', 'c.company_owner = u.user_id');
     $q->addJoin('contacts', 'con', 'u.user_contact = con.contact_id');
     $q->addJoin('projects', 'p2', 'c.company_id = p2.project_company AND p2.project_active = 0');
     $where = $this->getAllowedSQL($AppUI->user_id, 'c.company_id');
     $q->addWhere($where);
     if ($companyType > -1) {
         $q->addWhere('c.company_type = ' . (int) $companyType);
     }
     if ($searchString != '') {
         $q->addWhere('c.company_name LIKE "%' . $searchString . '%"');
     }
     if ($ownerId > 0) {
         $q->addWhere('c.company_owner = ' . $ownerId);
     }
     $q->addGroup('c.company_id');
     $q->addOrder($orderby . ' ' . $orderdir);
     return $q->loadList();
 }

 /**
 * Gets a list of the modules that should appear in the menu
 * @return array Named array list in the form
 * ['module directory', 'module name', 'module_icon']
 */
 function getMenuModules()
 {
     $q = new DBQuery();
     $q->addTable('modules');
     $q->addQuery('mod_directory, mod_ui_name, mod_ui_icon');
     $q->addWhere('mod_active > 0 AND mod_ui_active > 0 AND mod_directory <> \'public\'');
     $q->addWhere('mod_type != \'utility\'');
     $q->addOrder('mod_ui_order');
     return $q->loadList();
 }

	</td>
</tr>

</table>
</form>

<table cellspacing="0" cellpadding="0" border="1" align="center">
<tr>
	<td>
<?php 
if ($a != 'todo') {
    $q = new DBQuery();
    $q->addTable('tasks');
    $q->addQuery('COUNT(*) AS N');
    $q->addWhere('task_project=' . $project_id);
    $cnt = $q->loadList();
    $q->clear();
} else {
    $cnt[0]['N'] = empty($tasks) ? 0 : 1;
}
if ($cnt[0]['N'] > 0) {
    $src = '?m=tasks&amp;a=gantt&amp;suppressHeaders=1&amp;project_id=' . $project_id . ($display_option == 'all' ? '' : '&amp;start_date=' . $start_date->format('%Y-%m-%d') . '&amp;end_date=' . $end_date->format('%Y-%m-%d')) . "&width='" . "+((navigator.appName=='Netscape'?window.innerWidth:document.body.offsetWidth)*0.95)" . "+'&amp;showLabels=" . $showLabels . '&amp;showWork=' . $showWork . '&amp;sortByName=' . $sortByName . '&amp;showPinned=' . $showPinned . '&amp;showArcProjs=' . $showArcProjs . '&amp;showHoldProjs=' . $showHoldProjs . '&amp;showDynTasks=' . $showDynTasks . '&amp;showLowTasks=' . $showLowTasks . '&amp;caller=' . $a . '&amp;user_id=' . $user_id;
    ?>
	<script type="text/javascript">document.write('<img src="<?php 
    echo $src;
    ?>
" alt="" />')</script>
<?php 
    //If we have a problem displaying this we need to display a warning.
    //Put it at the bottom just in case
    if (!dPcheckMem(32 * 1024 * 1024)) {