PHP w2p_Database_Query::loadHashList方法代码示例

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

 /**
  * Tests resetting project parents.
  */
 public function testResetProjectParents()
 {
     global $AppUI;
     $st_projects = array(0 => '');
     $q = new w2p_Database_Query();
     $q->addTable('projects');
     $q->addQuery('project_id, project_name, project_parent');
     $q->addOrder('project_name');
     $st_projects = $q->loadHashList('project_id');
     reset_project_parents($st_projects);
     $this->assertEquals(4, count($st_projects));
     $this->assertEquals(1, $st_projects[1]['project_id']);
     $this->assertEquals('Test Project', $st_projects[1]['project_name']);
     $this->assertEquals(1, $st_projects[1]['project_parent']);
     $this->assertEquals(1, $st_projects[1][0]);
     $this->assertEquals('Test Project', $st_projects[1][1]);
     $this->assertEquals('', $st_projects[1][2]);
     $this->assertEquals(2, $st_projects[2]['project_id']);
     $this->assertEquals('Test Project 2', $st_projects[2]['project_name']);
     $this->assertEquals(1, $st_projects[2]['project_parent']);
     $this->assertEquals(2, $st_projects[2][0]);
     $this->assertEquals('Test Project 2', $st_projects[2][1]);
     $this->assertEquals(1, $st_projects[2][2]);
     $this->assertEquals(3, $st_projects[3]['project_id']);
     $this->assertEquals('Test Project 3', $st_projects[3]['project_name']);
     $this->assertEquals(1, $st_projects[3]['project_parent']);
     $this->assertEquals(3, $st_projects[3][0]);
     $this->assertEquals('Test Project 3', $st_projects[3][1]);
     $this->assertEquals(1, $st_projects[3][2]);
     $this->assertEquals(4, $st_projects[4]['project_id']);
     $this->assertEquals('Test Project 4', $st_projects[4]['project_name']);
     $this->assertEquals(1, $st_projects[4]['project_parent']);
     $this->assertEquals(4, $st_projects[4][0]);
     $this->assertEquals('Test Project 4', $st_projects[4][1]);
     $this->assertEquals(1, $st_projects[4][2]);
 }

    $q->addQuery('t.task_id, round(t.task_duration * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)/count(ut.task_id),2) as hours_allocated');
    $q->addWhere('t.task_id = ut.task_id');
    $q->addWhere('t.task_milestone = 0');
    $q->addWhere('project_active = 1');
    if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
        $q->addWhere('project_status <> ' . (int) $template_status);
    }
    if ($project_id != 0) {
        $q->addWhere('t.task_project = ' . (int) $project_id);
    }
    if (!$log_all) {
        $q->addWhere('t.task_start_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'');
        $q->addWhere('t.task_start_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\'');
    }
    $q->addGroup('t.task_id');
    $task_list = $q->loadHashList('task_id');
    $q->clear();
    ?>

<table cellspacing="1" cellpadding="4" border="0" class="tbl">
	<tr>
		<th colspan='2'><?php 
    echo $AppUI->_('User');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Hours allocated');
    ?>
</th>
		<th><?php 
    echo $AppUI->_('Hours worked');

//Lets load the users panel viewing options
$q = new w2p_Database_Query();
$q->addTable('project_designer_options', 'pdo');
$q->addQuery('pdo.*');
$q->addWhere('pdo.pd_option_user = ' . (int) $AppUI->user_id);
$view_options = $q->loadList();
$project_id = (int) w2PgetParam($_POST, 'project_id', 0);
$project_id = (int) w2PgetParam($_GET, 'project_id', $project_id);
$extra = array('where' => 'project_active = 1');
$project = new CProject();
$projects = $project->getAllowedRecords($AppUI->user_id, 'projects.project_id,project_name', 'project_name', null, $extra, 'projects');
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addQuery('projects.project_id, company_name');
$q->addJoin('companies', 'co', 'co.company_id = project_company');
$idx_companies = $q->loadHashList();
$q->clear();
foreach ($projects as $prj_id => $prj_name) {
    $projects[$prj_id] = $idx_companies[$prj_id] . ': ' . $prj_name;
}
asort($projects);
$projects = arrayMerge(array('0' => $AppUI->_('(None)', UI_OUTPUT_RAW)), $projects);
$extra = array();
$task = new CTask();
$tasks = $task->getAllowedRecords($AppUI->user_id, 'task_id,task_name', 'task_name', null, $extra);
$tasks = arrayMerge(array('0' => $AppUI->_('(None)', UI_OUTPUT_RAW)), $tasks);
if (!$project_id) {
    // setup the title block
    $ttl = 'ProjectDesigner';
    $titleBlock = new w2p_Theme_TitleBlock($ttl, 'projectdesigner.png', $m, $m . '.' . $a);
    $titleBlock->addCrumb('?m=projects', 'projects list');

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

function changeIt() {

function showcompany($company, $restricted = false)
{
    global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all;
    $q = new w2p_Database_Query();
    $q->addTable('projects');
    $q->addQuery('project_id, project_name');
    $q->addWhere('project_company = ' . (int) $company);
    $projects = $q->loadHashList();
    $q->clear();
    $q->addTable('companies');
    $q->addQuery('company_name');
    $q->addWhere('company_id = ' . (int) $company);
    $company_name = $q->loadResult();
    $q->clear();
    $table = '<h2>Company: ' . $company_name . '</h2>
    	<table cellspacing="1" cellpadding="4" border="0" class="tbl">';
    $project_row = '
        <tr>
                <th>' . $AppUI->_('Project') . '</th>';
    $pdfth[] = $AppUI->_('Project');
    $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>';
    $pdfth[] = $AppUI->_('Total');
    $pdfdata[] = $pdfth;
    $hours = 0.0;
    $table .= $project_row;
    foreach ($projects as $project => $name) {
        $pdfproject = array();
        $pdfproject[] = $name;
        $project_hours = 0;
        $project_row = '<tr><td>' . $name . '</td>';
        $q->addTable('projects');
        $q->addTable('tasks');
        $q->addTable('task_log');
        $q->addQuery('task_log_costcode, SUM(task_log_hours) as hours');
        $q->addWhere('project_id = ' . (int) $project);
        $q->addWhere('project_active = 1');
        if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
            $q->addWhere('project_status <> ' . (int) $template_status);
        }
        if ($log_start_date != 0 && !$log_all) {
            $q->addWhere('task_log_date >=' . $log_start_date);
        }
        if ($log_end_date != 0 && !$log_all) {
            $q->addWhere('task_log_date <=' . $log_end_date);
        }
        if ($restricted) {
            $q->addWhere('task_log_creator = ' . (int) $AppUI->user_id);
        }
        $q->addWhere('project_id = task_project');
        $q->addWhere('task_id = task_log_task');
        $q->addGroup('project_id');
        $task_logs = $q->loadHashList();
        $q->clear();
        foreach ($task_logs as $task_log) {
            $project_hours += $task_log;
        }
        $project_row .= '<td style="text-align:right;">' . sprintf('%.2f', round($project_hours, 2)) . '</td></tr>';
        $pdfproject[] = round($project_hours, 2);
        $hours += $project_hours;
        if ($project_hours > 0) {
            $table .= $project_row;
            $pdfdata[] = $pdfproject;
        }
    }
    if ($hours > 0) {
        $pdfdata[] = array($AppUI->_('Total'), round($hours, 2));
        $allpdfdata[$company_name] = $pdfdata;
        echo $table;
        echo '<tr><td>' . $AppUI->_('Total') . '</td><td style="text-align:right;">' . sprintf('%.2f', round($hours, 2)) . '</td></tr></table>';
    }
    return $hours;
}

 public function getUsersWithRole()
 {
     $q = new w2p_Database_Query();
     $q->addTable($this->_db_acl_prefix . 'groups_aro_map', 'g');
     $q->addQuery('DISTINCT(g.aro_id)');
     $result = $q->loadHashList();
     $q->clear();
     if (count($result)) {
         return $result;
     } else {
         return false;
     }
 }

$callback = w2PgetParam($_GET, 'callback', 0);
$table = w2PgetParam($_GET, 'table', 0);
$user_id = w2PgetParam($_GET, 'user_id', 0);
$ok = $callback & $table;
$title = 'Generic Selector';
$q = new w2p_Database_Query();
$q->addTable($table);
$query_result = false;
switch ($table) {
    case 'companies':
        $obj = new CCompany();
        $title = 'Company';
        $q->addQuery('company_id, company_name');
        $q->addOrder('company_name');
        $q->addWhere(selPermWhere($obj, 'company_id', 'company_name'));
        $resultList = $q->loadHashList();
        break;
    case 'departments':
        // known issue: does not filter out denied companies
        $title = 'Department';
        $company_id = w2PgetParam($_GET, 'company_id', 0);
        $obj = new CDepartment();
        $q->addWhere(selPermWhere($obj, 'dept_id', 'dept_name'));
        $q->addWhere('dept_company = company_id ');
        $q->addTable('companies', 'b');
        $company = new CCompany();
        $allowed = $company->getAllowedRecords($AppUI->user_id, 'company_id, company_name');
        if (count($allowed)) {
            $q->addWhere('company_id IN (' . implode(',', array_keys($allowed)) . ') ');
        }
        $hide_company = w2PgetParam($_GET, 'hide_company', 0);

 public static function getSettings($module, $configName = '')
 {
     $q = new w2p_Database_Query();
     $q->addTable('module_config');
     $q->addQuery('module_config_value, module_config_text');
     $q->addWhere("module_name = '{$module}'");
     if ('' != $configName) {
         $q->addWhere("module_config_name = '{$configName}'");
     }
     $q->addOrder('module_config_order, module_config_value ASC');
     return $q->loadHashList();
 }

 /**
  * Find a queue record (or records) based upon the
  *
  */
 public function find($module, $type, $id = 0)
 {
     $q = new w2p_Database_Query();
     $q->addTable($this->table);
     $q->addWhere("queue_module = '{$module}'");
     $q->addWhere("queue_type = '{$type}'");
     if ($id) {
         $q->addWhere("queue_origin_id = {$id}");
     }
     return $q->loadHashList('queue_id');
 }

 public function checkClash($userlist = null)
 {
     global $AppUI;
     if (!isset($userlist)) {
         return false;
     }
     $users = explode(',', $userlist);
     // Now, remove the owner from the list, as we will always clash on this.
     $key = array_search($AppUI->user_id, $users);
     if (isset($key) && $key !== false) {
         // Need both for change in php 4.2.0
         unset($users[$key]);
     }
     if (!count($users)) {
         return false;
     }
     $start_date = new w2p_Utilities_Date($this->event_start_date);
     $end_date = new w2p_Utilities_Date($this->event_end_date);
     // Now build a query to find matching events.
     $q = new w2p_Database_Query();
     $q->addTable('events', 'e');
     $q->addQuery('e.event_owner, ue.user_id, e.event_cwd, e.event_id, e.event_start_date, e.event_end_date');
     $q->addJoin('user_events', 'ue', 'ue.event_id = e.event_id');
     $q->addWhere('event_start_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\'');
     $q->addWhere('event_end_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'');
     $q->addWhere('(e.event_owner IN (' . implode(',', $users) . ') OR ue.user_id IN (' . implode(',', $users) . ') )');
     $q->addWhere('e.event_id <>' . $this->event_id);
     $result = $q->exec();
     if (!$result) {
         return false;
     }
     $clashes = array();
     while ($row = $q->fetchRow()) {
         array_push($clashes, $row['event_owner']);
         if ($row['user_id']) {
             array_push($clashes, $row['user_id']);
         }
     }
     $clash = array_unique($clashes);
     $q->clear();
     if (count($clash)) {
         $q->addTable('users', 'u');
         $q->addTable('contacts', 'con');
         $q->addQuery('user_id');
         $q->addQuery('CONCAT_WS(\' \',contact_first_name,contact_last_name)');
         $q->addWhere('user_id IN (' . implode(',', $clash) . ')');
         $q->addWhere('user_contact = contact_id');
         return $q->loadHashList();
     } else {
         return false;
     }
 }

 public static function getContactList(CAppUI $AppUI = null, $deptId)
 {
     global $AppUI;
     if ($AppUI->isActiveModule('contacts') && canView('contacts') && (int) $deptId > 0) {
         $q = new w2p_Database_Query();
         $q->addTable('contacts', 'con');
         $q->addQuery('con.contact_id, con.contact_first_name');
         $q->addQuery('con.contact_last_name');
         $q->addWhere('contact_department = ' . (int) $deptId);
         $q->addWhere('(contact_owner = ' . (int) $AppUI->user_id . ' OR contact_private = 0)');
         $q->addOrder('contact_first_name');
         $results = $q->loadHashList('contact_id');
     }
     return $results;
 }

function get_dependencies_pd($task_id)
{
    // Pull tasks dependencies
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addTable('task_dependencies', 'td');
    $q->addQuery('t.task_id, t.task_name');
    $q->addWhere('td.dependencies_task_id = ' . (int) $task_id);
    $q->addWhere('t.task_id = td.dependencies_req_task_id');
    $taskDep = $q->loadHashList();
}

$q3->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q3->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
if (count($allowedProjects)) {
    $q3->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
    $q3->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
    $q3->addWhere($catsql);
}
$files = array();
$file_versions = array();
if ($canRead) {
    $files = $q2->loadList();
    $file_versions = $q3->loadHashList('file_id');
}
// counts total recs from selection
$xpg_totalrecs = count($q->loadList());
//TODO: I don't like the ++$tab construct here... seems kludgy.
echo buildPaginationNav($AppUI, $m, $tab, $xpg_totalrecs, $xpg_pagesize, $page);
?>
<script language="javascript" 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>

    if (function_exists('styleRenderBoxBottom')) {
        echo styleRenderBoxBottom();
    }
    echo '<br />';
    if (function_exists('styleRenderBoxTop')) {
        echo styleRenderBoxTop();
    }
    echo '<table cellspacing="0" cellpadding="4" border="0" width="100%" class="std">
<tr>
	<td>';
    // Let's figure out which users we have
    $q = new w2p_Database_Query();
    $q->addTable('users', 'u');
    $q->addQuery('u.user_id, u.user_username, contact_first_name, contact_last_name');
    $q->addJoin('contacts', 'c', 'u.user_contact = contact_id', 'inner');
    $user_list = $q->loadHashList('user_id');
    $q->clear();
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addTable('user_tasks', 'ut');
    $q->addTable('projects', 'pr');
    $q->addQuery('t.*, ut.*, pr.project_name');
    $q->addWhere('( task_start_date
			   BETWEEN \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\' 
	                AND \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\' 
	           OR task_end_date	BETWEEN \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\' 
	                AND \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\' 
		   OR ( task_start_date <= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'
	                AND task_end_date >= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\') )');
    $q->addWhere('task_end_date IS NOT NULL');
    $q->addWhere('task_end_date <> \'0000-00-00 00:00:00\'');