PHP w2p_Database_Query::loadColumn方法代码示例

$sortTasksByName = w2PgetParam($_REQUEST, 'sortTasksByName', 0);
$addPwOiD = w2PgetParam($_REQUEST, 'addPwOiD', 0);
$pjobj = new CProject();
/*
** Load department info for the case where one
** wants to see the ProjectsWithOwnerInDeparment (PwOiD)
** instead of the projects related to the given department.
*/
if ($addPwOiD && $department > 0) {
    $owner_ids = array();
    $q = new w2p_Database_Query();
    $q->addTable('users');
    $q->addQuery('user_id');
    $q->addJoin('contacts', 'c', 'c.contact_id = user_contact', 'inner');
    $q->addWhere('c.contact_department = ' . (int) $department);
    $owner_ids = $q->loadColumn();
    $q->clear();
}
// pull valid projects and their percent complete information
$q = new w2p_Database_Query();
$q->addTable('projects', 'pr');
$q->addQuery('DISTINCT pr.project_id, project_color_identifier, project_name, project_start_date, project_end_date,
                max(t1.task_end_date) AS project_actual_end_date, project_percent_complete,
                project_status, project_active');
$q->addJoin('tasks', 't1', 'pr.project_id = t1.task_project');
$q->addJoin('companies', 'c1', 'pr.project_company = c1.company_id');
if ($department > 0 && !$addPwOiD) {
    $q->addWhere('project_departments.department_id = ' . (int) $department);
}
if ($project_type > -1) {
    $q->addWhere('pr.project_type = ' . (int) $project_type);

    echo $AppUI->_('User Efficiency (based on completed tasks)');
    ?>
</th>
	</tr>

<?php 
    if (count($user_list)) {
        $percentage_sum = $hours_allocated_sum = $hours_worked_sum = 0;
        $sum_total_hours_allocated = $sum_total_hours_worked = 0;
        $sum_hours_allocated_complete = $sum_hours_worked_complete = 0;
        //TODO: Split times for which more than one users were working...
        foreach ($user_list as $user_id => $user) {
            $q->addTable('user_tasks', 'ut');
            $q->addQuery('task_id');
            $q->addWhere('user_id = ' . (int) $user_id);
            $tasks_id = $q->loadColumn();
            $q->clear();
            $total_hours_allocated = $total_hours_worked = 0;
            $hours_allocated_complete = $hours_worked_complete = 0;
            foreach ($tasks_id as $task_id) {
                if (isset($task_list[$task_id])) {
                    // Now let's figure out how many time did the user spent in this task
                    $q->addTable('task_log');
                    $q->addQuery('SUM(task_log_hours)');
                    $q->addWhere('task_log_task =' . (int) $task_id);
                    $q->addWhere('task_log_creator =' . (int) $user_id);
                    $hours_worked = round($q->loadResult(), 2);
                    $q->clear();
                    $q->addTable('tasks');
                    $q->addQuery('task_percent_complete');
                    $q->addWhere('task_id =' . (int) $task_id);

 /**
  * Tests adding a reminder to a task
  */
 public function testAddReminder()
 {
     global $AppUI;
     global $w2Pconfig;
     $this->obj->load(1);
     // Ensure our global setting for task_reminder_control is set properly for this
     $old_task_reminder_control = $w2Pconfig['task_reminder_control'];
     $w2Pconfig['task_reminder_control'] = true;
     $this->obj->addReminder();
     $this->obj->task_percent_complete = 50;
     $xml_file_dataset = $this->createXMLDataset($this->getDataSetPath() . 'tasksTestAddReminder.xml');
     $xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('event_queue' => array('queue_start')));
     $xml_db_dataset = $this->getConnection()->createDataSet();
     $xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('event_queue' => array('queue_start')));
     $this->assertTablesEqual($xml_file_filtered_dataset->getTable('event_queue'), $xml_db_filtered_dataset->getTable('event_queue'));
     $now_secs = time();
     $min_time = $now_secs - 10;
     /**
      * Get updated dates to test against
      */
     $q = new w2p_Database_Query();
     $q->addTable('event_queue');
     $q->addQuery('queue_start');
     $q->addWhere('queue_id = 2');
     $results = $q->loadColumn();
     foreach ($results as $queue_start) {
         $this->assertGreaterThanOrEqual($min_time, $queue_start);
         $this->assertLessThanOrEqual($now_secs, $queue_start);
     }
     $w2Pconfig['task_reminder_control'] = $old_task_reminder_control;
 }

	<?php 
    echo $call_back_string;
    ?>
	self.close();
</script>
<?php 
}
// Remove any empty elements
$contacts_id = remove_invalid(explode(',', $selected_contacts_id));
$selected_contacts_id = implode(',', $contacts_id);
$q = new w2p_Database_Query();
if (strlen($selected_contacts_id) > 0 && !$show_all && !$company_id) {
    $q->addTable('contacts');
    $q->addQuery('DISTINCT contact_company');
    $q->addWhere('contact_id IN (' . $selected_contacts_id . ')');
    $where = implode(',', $q->loadColumn());
    $q->clear();
    if (substr($where, 0, 1) == ',') {
        $where = '0' . $where;
    }
    $where = $where ? 'contact_company IN(' . $where . ')' : '';
} elseif (!$company_id && !$show_all) {
    //  Contacts from all allowed companies
    $where = '(contact_company IS NULL OR contact_company = 0)';
    $company_name = $AppUI->_('No Company');
} elseif ($show_all) {
    $company_name = $AppUI->_('Allowed Companies');
} else {
    // Contacts for this company only
    $q->addWhere('contact_company = ' . (int) $company_id);
}

 /**
  * This function recursively updates all tasks project
  * to the one passed as parameter
  */
 public function updateSubTasksProject($new_project, $task_id = null)
 {
     $q = new w2p_Database_Query();
     if (is_null($task_id)) {
         $task_id = $this->task_id;
     }
     $q->addTable('tasks');
     $q->addQuery('task_id');
     $q->addWhere('task_parent = ' . (int) $task_id);
     $tasks_id = $q->loadColumn();
     $q->clear();
     if (count($tasks_id) == 0) {
         return true;
     }
     // update project of children
     $q->addTable('tasks');
     $q->addUpdate('task_project', $new_project);
     $q->addWhere('task_parent = ' . (int) $task_id);
     $q->exec();
     $q->clear();
     foreach ($tasks_id as $id) {
         if ($id != $task_id) {
             $this->updateSubTasksProject($new_project, $id);
         }
     }
 }

 /**
  * Tests importing tasks from one project to another
  */
 public function testImportTasks()
 {
     $this->markTestIncomplete("This test is failing miserably.. not sure of the best way to solve it yet.");
     $this->obj->load(4);
     $response = $this->obj->importTasks(3);
     $this->assertEquals(array(), $response);
     //        $xml_file_dataset = $this->createXMLDataSet($this->getDataSetPath().'projectsTestImportTasks.xml');
     //        $xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('tasks' => array('task_created', 'task_updated')));
     //        $xml_db_dataset = $this->getConnection()->createDataSet();
     //        $xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('tasks' => array('task_created', 'task_updated')));
     //        $this->assertTablesEqual($xml_file_filtered_dataset->getTable('tasks'), $xml_db_filtered_dataset->getTable('tasks'));
     $now_secs = time();
     $min_time = $now_secs - 10;
     /**
      * Get created dates to test against
      */
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addQuery('task_created');
     $q->addWhere('task_project = 4');
     $results = $q->loadColumn();
     global $AppUI;
     foreach ($results as $created) {
         $created = strtotime($AppUI->formatTZAwareTime($created, '%Y-%m-%d %T'));
         $this->assertGreaterThanOrEqual($created, $now_secs);
         $this->assertLessThanOrEqual($created, $min_time);
     }
     /**
      * Get updated dates to test against
      */
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addQuery('task_updated');
     $q->addWhere('task_project = 4');
     $results = $q->loadColumn();
     foreach ($results as $updated) {
         $updated = strtotime($AppUI->formatTZAwareTime($updated, '%Y-%m-%d %T'));
         $this->assertGreaterThanOrEqual($min_time, $updated);
         $this->assertLessThanOrEqual($now_secs, $updated);
     }
     //        $xml_dataset = $this->createXMLDataSet($this->getDataSetPath().'projectsTestImportTasks.xml');
     //        $this->assertTablesEqual($xml_dataset->getTable('user_tasks'), $this->getConnection()->createDataSet()->getTable('user_tasks'));
     //        $this->assertTablesEqual($xml_dataset->getTable('task_dependencies'), $this->getConnection()->createDataSet()->getTable('task_dependencies'));
 }

        echo styleRenderBoxTop();
    }
    echo '<table cellspacing="0" cellpadding="4" border="0" width="100%" class="std">
	<tr>
		<td>';
    $total = 0;
    $q = new w2p_Database_Query();
    if ($fullaccess) {
        $q->addTable('companies');
        $q->addQuery('company_id');
    } else {
        $q->addTable('companies');
        $q->addQuery('company_id');
        $q->addWhere('company_owner = ' . (int) $AppUI->user_id);
    }
    $companies = $q->loadColumn();
    $q->clear();
    if (!empty($companies)) {
        foreach ($companies as $company) {
            $total += showcompany($company);
        }
    } else {
        $q->addTable('companies');
        $q->addQuery('company_id');
        foreach ($q->loadColumn() as $company) {
            $total += showcompany($company, true);
        }
    }
    echo '<h2>' . $AppUI->_('Total Hours') . ': ';
    printf("%.2f", $total);
    echo '</h2>';

 /**
  *	Overload of the w2PObject::getDeniedRecords
  *	to ensure that the projects owned by denied companies are denied.
  *
  *	@author	handco <handco@sourceforge.net>
  *	@see	w2PObject::getAllowedRecords
  */
 public function getDeniedRecords($uid)
 {
     $aBuf1 = parent::getDeniedRecords($uid);
     $oCpy = new CCompany();
     // Retrieve which projects are allowed due to the company rules
     $aCpiesAllowed = $oCpy->getAllowedRecords($uid, 'company_id,company_name');
     //Department permissions
     $oDpt = new CDepartment();
     $aDptsAllowed = $oDpt->getAllowedRecords($uid, 'dept_id,dept_name');
     $q = new w2p_Database_Query();
     $q->addTable('projects');
     $q->addQuery('projects.project_id');
     $q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
     if (count($aCpiesAllowed)) {
         if (array_search('0', $aCpiesAllowed) === false) {
             //If 0 (All Items of a module) are not permited then just add the allowed items only
             $q->addWhere('NOT (project_company IN (' . implode(',', array_keys($aCpiesAllowed)) . '))');
         } else {
             //If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
         }
     } else {
         //if the user is not allowed any company then lets shut him off
         $q->addWhere('0=1');
     }
     if (count($aDptsAllowed)) {
         if (array_search('0', $aDptsAllowed) === false) {
             //If 0 (All Items of a module) are not permited then just add the allowed items only
             $q->addWhere('NOT (department_id IN (' . implode(',', array_keys($aDptsAllowed)) . '))');
         } else {
             //If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
             $q->addWhere('NOT (department_id IS NULL)');
         }
     } else {
         //If 0 (All Items of a module) are permited then don't add a where clause so the user is permitted to see all
         $q->addWhere('NOT (department_id IS NULL)');
     }
     $aBuf2 = $q->loadColumn();
     $q->clear();
     return array_merge($aBuf1, $aBuf2);
 }

function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $tasks_problems, $owner, $projectTypeId, $search_text, $project_type;
    $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 w2p_Database_Query();
    $q->setDelete('tasks_problems');
    $q->exec();
    $q->clear();
    $q->setDelete('tasks_users');
    $q->exec();
    $q->clear();
    // support task problem logs
    $q->addInsertSelect('tasks_problems');
    $q->addTable('tasks');
    $q->addQuery('task_project, task_log_problem');
    $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id', 'inner');
    $q->addWhere('task_log_problem = 1');
    $q->addGroup('task_project');
    $tasks_problems = $q->exec();
    $q->clear();
    if ($addProjectsWithAssignedTasks) {
        // support users tasks
        $q->addInsertSelect('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 = ' . (int) $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', 'inner');
        $q->addWhere('c.contact_department = ' . (int) $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');
        $q->addQuery('dept_id, dept_parent');
        $q->addOrder('dept_parent,dept_name');
        $rows = $q->loadList();
        addDeptId($rows, $department);
        $dept_ids[] = isset($department->dept_id) ? $department->dept_id : 0;
        $dept_ids[] = $department > 0 ? $department : 0;
    }
    $q->clear();
    // retrieve list of records
    // modified for speed
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    // get the list of permitted companies
    $obj = new CCompany();
    $companies = $obj->getAllowedRecords($AppUI->user_id, 'companies.company_id,companies.company_name', 'companies.company_name');
    if (count($companies) == 0) {
        $companies = array();
    }
    $q->addTable('projects', 'pr');
    $q->addQuery('pr.project_id, project_status, project_color_identifier,
		project_type, project_name, project_description, project_scheduled_hours as project_duration,
		project_parent, project_original_parent, project_percent_complete,
		project_color_identifier, project_company,
        company_name, project_status, project_last_task as critical_task,
        tp.task_log_problem, user_username, project_active');
    $fields = w2p_Core_Module::getSettings('projects', 'index_list');
    unset($fields['department_list']);
    // added as an alias below
    foreach ($fields as $field => $text) {
        $q->addQuery($field);
    }
    $q->addQuery('CONCAT(ct.contact_first_name, \' \', ct.contact_last_name) AS owner_name');
    $q->addJoin('users', 'u', 'pr.project_owner = u.user_id');
    $q->addJoin('contacts', 'ct', 'ct.contact_id = u.user_contact');
    $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project');
    if ($addProjectsWithAssignedTasks) {
        $q->addJoin('tasks_users', 'tu', 'pr.project_id = tu.task_project');
    }
    if (!isset($department) && $company_id && !$addPwOiD) {
        $q->addWhere('pr.project_company = ' . (int) $company_id);
    }
    if ($project_type > -1) {
        $q->addWhere('pr.project_type = ' . (int) $project_type);
    }
    if (isset($department) && !$addPwOiD) {
        $q->addWhere('project_departments.department_id in ( ' . implode(',', $dept_ids) . ' )');
    }
//.........这里部分代码省略.........

/**
 * @param $department
 *
 * @return array
 */
function __extract_from_projects_gantt($department)
{
    $q = new w2p_Database_Query();
    $q->addTable('users');
    $q->addQuery('user_id');
    $q->addJoin('contacts', 'c', 'c.contact_id = user_contact', 'inner');
    $q->addWhere('c.contact_department = ' . (int) $department);
    $owner_ids = $q->loadColumn();
    return $owner_ids;
}

/**
 * canTaskAccess()
 * Used to check if a user has task_access to see the task in task list context
 * (This function was optimized to try to use the DB the least possible)
 *
 * @param mixed $task_id
 * @param mixed $task_access
 * @param mixed $task_owner
 * @return true if user has task access to it, or false if he doesn't
 */
function canTaskAccess($task_id, $task_access, $task_owner)
{
    global $AppUI;
    $q = new w2p_Database_Query();
    if (!$task_id || !isset($task_access)) {
        return false;
    }
    //if for some weird reason we have tasks without an owner, lets make them visible at least for admins, or else we take the risk of having phantom tasks.
    if (!$task_owner) {
        $task_owner = $AppUI->user_id;
    }
    $user_id = $AppUI->user_id;
    // Let's see if this user has admin privileges, if so return true
    if ($AppUI->user_is_admin) {
        return true;
    }
    switch ($task_access) {
        case 0:
            // public
            $retval = true;
            break;
        case 1:
            // protected
            $q->addTable('users');
            $q->addQuery('user_company');
            $q->addWhere('user_id=' . (int) $user_id . ' OR user_id=' . (int) $task_owner);
            $user_owner_companies = $q->loadColumn();
            $q->clear();
            $company_match = true;
            foreach ($user_owner_companies as $current_company) {
                $company_match = $company_match && (!isset($last_company) || $last_company == $current_company);
                $last_company = $current_company;
            }
        case 2:
            // participant
            $company_match = isset($company_match) ? $company_match : true;
            $q->addTable('user_tasks');
            $q->addQuery('COUNT(task_id)');
            $q->addWhere('user_id=' . (int) $user_id . ' AND task_id=' . (int) $task_id);
            $count = $q->loadResult();
            $q->clear();
            $retval = $company_match && $count > 0 || $count > 0 || $task_owner == $user_id;
            break;
        case 3:
            // private
            $retval = $task_owner == $user_id;
            break;
        default:
            $retval = false;
            break;
    }
    return $retval;
}

$titleBlock->show();
$filter_param = w2PgetParam($_REQUEST, 'filter', '');
$filter = array();
if ($filter_param) {
    $in_filter = $_REQUEST['filter'];
    $filter[] = 'history_table = \'' . $_REQUEST['filter'] . '\' ';
} else {
    $in_filter = '';
}
if (!empty($_REQUEST['project_id'])) {
    $project_id = w2PgetParam($_REQUEST, 'project_id', 0);
    $q = new w2p_Database_Query();
    $q->addTable('tasks');
    $q->addQuery('task_id');
    $q->addWhere('task_project = ' . (int) $project_id);
    $project_tasks = implode(',', $q->loadColumn());
    if (!empty($project_tasks)) {
        $project_tasks = 'OR (history_table = \'tasks\' AND history_item IN (' . $project_tasks . '))';
    }
    $q->addTable('files');
    $q->addQuery('file_id');
    $q->addWhere('file_project = ' . (int) $project_id);
    $project_files = implode(',', $q->loadColumn());
    if (!empty($project_files)) {
        $project_files = 'OR (history_table = \'files\' AND history_item IN (' . $project_files . '))';
    }
    $filter[] = '((history_table = \'projects\' AND history_item = \'' . (int) $project_id . '\') ' . $project_tasks . ' ' . $project_files . ')';
}
$page = isset($_REQUEST['pg']) ? (int) $_REQUEST['pg'] : 1;
$limit = isset($_REQUEST['limit']) ? (int) $_REQUEST['limit'] : 100;
$offset = ($page - 1) * $limit;

 /**
  * Tests importing tasks from one project to another
  */
 public function testImportTasks()
 {
     $this->obj->load(4);
     $response = $this->obj->importTasks(3);
     $this->assertEquals(array(), $response);
     $xml_file_dataset = $this->createXMLDataSet($this->getDataSetPath() . 'projectsTestImportTasks.xml');
     $xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('tasks' => array('task_created', 'task_updated')));
     $xml_db_dataset = $this->getConnection()->createDataSet();
     $xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('tasks' => array('task_created', 'task_updated')));
     $this->assertTablesEqual($xml_file_filtered_dataset->getTable('tasks'), $xml_db_filtered_dataset->getTable('tasks'));
     $now_secs = time();
     $min_time = $now_secs - 10;
     /**
      * Get created dates to test against
      */
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addQuery('task_created');
     $q->addWhere('task_project = 4');
     $results = $q->loadColumn();
     foreach ($results as $created) {
         $this->assertGreaterThanOrEqual($min_time, strtotime($created));
         $this->assertLessThanOrEqual($now_secs, strtotime($created));
     }
     /**
      * Get updated dates to test against
      */
     $q = new w2p_Database_Query();
     $q->addTable('tasks');
     $q->addQuery('task_updated');
     $q->addWhere('task_project = 4');
     $results = $q->loadColumn();
     foreach ($results as $updated) {
         $this->assertGreaterThanOrEqual($min_time, strtotime($updated));
         $this->assertLessThanOrEqual($now_secs, strtotime($updated));
     }
     $xml_dataset = $this->createXMLDataSet($this->getDataSetPath() . 'projectsTestImportTasks.xml');
     $this->assertTablesEqual($xml_dataset->getTable('user_tasks'), $this->getConnection()->createDataSet()->getTable('user_tasks'));
     $this->assertTablesEqual($xml_dataset->getTable('task_dependencies'), $this->getConnection()->createDataSet()->getTable('task_dependencies'));
 }

 public static function getUserDeptId($user_id)
 {
     $q = new w2p_Database_Query();
     $q->addQuery('con.contact_department');
     $q->addTable('users', 'u');
     $q->addJoin('contacts', 'con', 'user_contact = contact_id', 'inner');
     $q->addWhere('u.user_id = ' . (int) $user_id);
     $user_dept = $q->loadColumn();
     $q->clear();
     return $user_dept;
 }