PHP w2p_Database_Query::addQuery方法代码示例

 public function getProjectTaskLinksByCategory(CAppUI $AppUI, $project_id = 0, $task_id = 0, $category_id = 0, $search = '')
 {
     // load the following classes to retrieved denied records
     $project = new CProject();
     $task = new CTask();
     // SETUP FOR LINK LIST
     $q = new w2p_Database_Query();
     $q->addQuery('DISTINCT links.*');
     $q->addQuery('contact_first_name, contact_last_name');
     $q->addQuery('project_name, project_color_identifier, project_status');
     $q->addQuery('task_name, task_id');
     $q->addTable('links');
     $q->leftJoin('users', 'u', 'user_id = link_owner');
     $q->leftJoin('contacts', 'c', 'user_contact = contact_id');
     if ($search != '') {
         $q->addWhere('(link_name LIKE \'%' . $search . '%\' OR link_description LIKE \'%' . $search . '%\')');
     }
     if ($project_id > 0) {
         // Project
         $q->addWhere('link_project = ' . (int) $project_id);
     }
     if ($task_id > 0) {
         // Task
         $q->addWhere('link_task = ' . (int) $task_id);
     }
     if ($category_id >= 0) {
         // Category
         $q->addWhere('link_category = ' . $category_id);
     }
     // Permissions
     $project->setAllowedSQL($AppUI->user_id, $q, 'link_project');
     $task->setAllowedSQL($AppUI->user_id, $q, 'link_task and task_project = link_project');
     $q->addOrder('project_name, link_name');
     return $q->loadList();
 }

 public function loadFull(CAppUI $AppUI, $holidayId)
 {
     $q = new w2p_Database_Query();
     $q->addTable('holiday');
     $q->addQuery('holiday.*');
     $q->addWhere('holiday.holiday_id = ' . (int) $holidayId);
     $q->loadObject($this, true, false);
 }

 public function _fetchPreviousData()
 {
     $q = new w2p_Database_Query();
     $q->addTable($this->table_name);
     $q->addQuery($this->field_name);
     $q->addWhere($this->id_field_name . ' = ' . $this->row_id);
     $previous_data = $q->loadResult();
     if ($previous_data != '') {
         $previous_data = unserialize($previous_data);
         $previous_data = !is_array($previous_data) ? array() : $previous_data;
     } else {
         $previous_data = array();
     }
     $this->previous_data = $previous_data;
 }

 public function authenticate($username, $password)
 {
     global $db, $AppUI;
     $this->username = $username;
     $q = new w2p_Database_Query();
     $q->addTable('users');
     $q->addQuery('user_id, user_password');
     $q->addWhere("user_username = '{$username}'");
     $q->addWhere("user_password = '" . MD5($password) . "'");
     $q->exec();
     if ($row = $q->fetchRow()) {
         $this->user_id = $row['user_id'];
         return true;
     }
     return false;
 }

 public function store(CAppUI $AppUI = null)
 {
     global $AppUI;
     $perms = $AppUI->acl();
     $stored = false;
     $this->_error = $this->check();
     if (count($this->_error)) {
         return $this->_error;
     }
     $q = new w2p_Database_Query();
     if ($this->message_id && $perms->checkModuleItem('forums', 'edit', $this->forum_id)) {
         $q->setDelete('forum_visits');
         $q->addWhere('visit_message = ' . (int) $this->message_id);
         $q->exec();
         if ($msg = parent::store()) {
             return $msg;
         }
         $stored = true;
     }
     if (0 == $this->message_id && $perms->checkModuleItem('forums', 'add')) {
         $this->message_date = $q->dbfnNowWithTZ();
         if ($msg = parent::store()) {
             return $msg;
         }
         $q->addTable('forum_messages');
         $q->addQuery('count(message_id), MAX(message_date)');
         $q->addWhere('message_forum = ' . (int) $this->message_forum);
         $reply = $q->fetchRow();
         //update forum descriptor
         $forum = new CForum();
         $forum->load($AppUI, $this->message_forum);
         $forum->forum_message_count = $reply[0];
         /*
          * Note: the message_date here has already been adjusted for the
          *    timezone above, so don't do it again!
          */
         $forum->forum_last_date = $this->message_date;
         $forum->forum_last_id = $this->message_id;
         $forum->store($AppUI);
         $this->sendWatchMail(false);
         $stored = true;
     }
     return $stored;
 }

 public function store()
 {
     if (!is_array($this->options)) {
         $this->options = array();
     }
     $newoptions = $this->options;
     //insert the new option
     foreach ($newoptions as $opt) {
         $q = new w2p_Database_Query();
         $q->addTable('custom_fields_lists');
         $q->addQuery('MAX(list_option_id)');
         $max_id = $q->loadResult();
         $optid = $max_id ? $max_id + 1 : 1;
         $q = new w2p_Database_Query();
         $q->addTable('custom_fields_lists');
         $q->addInsert('field_id', $this->field_id);
         $q->addInsert('list_option_id', $optid);
         $q->addInsert('list_value', $opt);
         $q->exec();
     }
 }

 public function authenticate($username, $password)
 {
     global $db, $AppUI;
     $this->username = $username;
     $q = new w2p_Database_Query();
     $q->addTable('users');
     $q->addQuery('user_id, user_password');
     $q->addWhere('user_username = \'' . $username . '\'');
     if (!($rs = $q->exec())) {
         $q->clear();
         return false;
     }
     if (!($row = $q->fetchRow())) {
         $q->clear();
         return false;
     }
     $this->user_id = $row['user_id'];
     $q->clear();
     if (MD5($password) == $row['user_password']) {
         return true;
     }
     return false;
 }

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

    } else {
        $catsql = 'file_category = ' . ($tab - 1);
    }
} else {
    if ($tab < 0) {
        $catsql = false;
    } else {
        $catsql = 'file_category = ' . $tab;
    }
}
// Fetch permissions once for all queries
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
$allowedTasks = $task->getAllowedSQL($AppUI->user_id, 'file_task');
// SQL text for count the total recs from the selected option
$q = new w2p_Database_Query();
$q->addQuery('count(file_id)');
$q->addTable('files', 'f');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$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');
if (count($allowedProjects)) {
    $q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
    $q->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
    $q->addWhere($catsql);
}
if ($company_id) {

$q->addQuery('task_duration, task_duration_type');
$q->addQuery('task_project, task_represents_project');
$q->addQuery('task_description, task_owner, task_status');
$q->addQuery('usernames.user_username, usernames.user_id');
$q->addQuery('assignees.user_username as assignee_username');
$q->addQuery('count(distinct assignees.user_id) as assignee_count');
$q->addQuery('co.contact_first_name, co.contact_last_name');
$q->addQuery('CONCAT(co.contact_first_name,\' \', co.contact_last_name) AS owner');
$q->addQuery('task_milestone');
$q->addQuery('count(distinct f.file_task) as file_count');
$q->addQuery('tlog.task_log_problem');
$q->addQuery('task_access');
//subquery the parent state
$sq = new w2p_Database_Query();
$sq->addTable('tasks', 'stasks');
$sq->addQuery('COUNT(stasks.task_id)');
$sq->addWhere('stasks.task_id <> tasks.task_id AND stasks.task_parent = tasks.task_id');
$subquery = $sq->prepare();
$sq->clear();
$q->addQuery('(' . $subquery . ') AS task_nr_of_children');
$q->addTable('tasks');
$mods = $AppUI->getActiveModules();
if (!empty($mods['history']) && canView('history')) {
    $q->addQuery('MAX(history_date) as last_update');
    $q->leftJoin('history', 'h', 'history_item = tasks.task_id AND history_table=\'tasks\'');
}
$q->addJoin('projects', 'p', 'p.project_id = task_project', 'inner');
$q->leftJoin('users', 'usernames', 'task_owner = usernames.user_id');
$q->leftJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
$q->leftJoin('users', 'assignees', 'assignees.user_id = ut.user_id');
$q->leftJoin('contacts', 'co', 'co.contact_id = usernames.user_contact');

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

$taskPriority = w2PgetSysVal('TaskPriority');
$task_project = $project_id;
$task_sort_item1 = w2PgetParam($_GET, 'task_sort_item1', '');
$task_sort_type1 = w2PgetParam($_GET, 'task_sort_type1', '');
$task_sort_item2 = w2PgetParam($_GET, 'task_sort_item2', '');
$task_sort_type2 = w2PgetParam($_GET, 'task_sort_type2', '');
$task_sort_order1 = (int) w2PgetParam($_GET, 'task_sort_order1', 0);
$task_sort_order2 = (int) w2PgetParam($_GET, 'task_sort_order2', 0);
if (isset($_POST['show_task_options'])) {
    $AppUI->setState('TaskListShowIncomplete', w2PgetParam($_POST, 'show_incomplete', 0));
}
$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id);
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q->addQuery('projects.project_id, project_color_identifier, project_name');
$q->addQuery('SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) / SUM(task_duration * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) AS project_percent_complete');
$q->addQuery('company_name');
$q->addTable('projects');
$q->leftJoin('tasks', 't1', 'projects.project_id = t1.task_project');
$q->leftJoin('companies', 'c', 'company_id = project_company');
$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('t1.task_id = t1.task_parent');
$q->addWhere('projects.project_id=' . $project_id);
if (count($allowedProjects)) {
    $q->addWhere($allowedProjects);
}
$q->addGroup('projects.project_id');
$q2 = new w2p_Database_Query();
$q2 = $q;

<?php

/* $Id$ $URL$ */
if (!defined('W2P_BASE_DIR')) {
    die('You should not access this file directly.');
}
$perms =& $AppUI->acl();
if (!canView('tasks')) {
    $AppUI->redirect('m=public&a=access_denied');
}
$proj = (int) w2PgetParam($_GET, 'project', 0);
$userFilter = w2PgetParam($_GET, 'userFilter', false);
$q = new w2p_Database_Query();
$q->addQuery('t.task_id, t.task_name');
$q->addTable('tasks', 't');
if ($userFilter) {
    $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
    $q->addWhere('ut.user_id = ' . (int) $AppUI->user_id);
}
if ($proj != 0) {
    $q->addWhere('task_project = ' . (int) $proj);
}
$tasks = $q->loadList();
$q->clear();
?>

<script language="javascript" type="text/javascript">
function loadTasks() {
	var tasks = new Array();
	var sel = parent.document.forms['form'].new_task;
	while (sel.options.length) {

 /**
  * Tests storing task log in database
  */
 public function testStoreUpdate()
 {
     $this->obj->bind($this->post_data, null, true, true);
     $this->obj->task_log_id = 1;
     unset($this->obj->task_log_created);
     $this->obj->store();
     $xml_file_dataset = $this->createXMLDataSet($this->getDataSetPath() . 'tasklogsTestStoreUpdate.xml');
     $xml_file_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_file_dataset, array('task_log' => array('task_log_created', 'task_log_updated')));
     $xml_db_dataset = $this->getConnection()->createDataSet();
     $xml_db_filtered_dataset = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($xml_db_dataset, array('task_log' => array('task_log_created', 'task_log_updated')));
     $this->assertTablesEqual($xml_file_filtered_dataset->getTable('task_log'), $xml_db_filtered_dataset->getTable('task_log'));
     $this->assertTablesEqual($xml_file_filtered_dataset->getTable('tasks'), $xml_db_filtered_dataset->getTable('tasks'));
     $this->assertTablesEqual($xml_file_filtered_dataset->getTable('projects'), $xml_db_filtered_dataset->getTable('projects'));
     /**
      * Get updated dates to test against
      */
     $now_secs = time();
     $min_time = $now_secs - 10;
     $q = new w2p_Database_Query();
     $q->addTable('task_log');
     $q->addQuery('task_log_updated');
     $q->addWhere('task_log_id = 1');
     $results = $q->loadList();
     foreach ($results as $dates) {
         $this->assertGreaterThanOrEqual($min_time, strtotime($dates['task_log_updated']));
         $this->assertLessThanOrEqual($now_secs, strtotime($dates['task_log_updated']));
     }
 }

}
##
## add or edit a user preferences
##
$user_id = (int) w2PgetParam($_GET, 'user_id', '0');
$perms =& $AppUI->acl();
// check permissions for this record
$canEdit = canEdit('system');
// Check permissions
if (!$canEdit && $user_id != $AppUI->user_id) {
    $AppUI->redirect('m=public&a=access_denied');
}
// load the preferences
$q = new w2p_Database_Query();
$q->addTable('user_preferences');
$q->addQuery('pref_name, pref_value');
$q->addWhere('pref_user = ' . (int) $user_id);
$prefs = $q->loadHashList();
$q->clear();
// get the user name
if ($user_id) {
    $user = CContact::getContactByUserid($user_id);
} else {
    $user = 'Default';
}
$titleBlock = new w2p_Theme_TitleBlock('Edit User Preferences', 'myevo-weather.png', $m, $m . '.' . $a);
$perms =& $AppUI->acl();
if ($canEdit) {
    $titleBlock->addCrumb('?m=system', 'system admin');
    $titleBlock->addCrumb('?m=system&a=systemconfig', 'system configuration');
}