PHP w2p_Database_Query::addWhere方法代码示例

 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 delete()
 {
     $q = new w2p_Database_Query();
     $q->setDelete('custom_fields_lists');
     $q->addWhere('field_id = ' . (int) $this->field_id);
     $q->addWhere('list_option_id = ' . (int) $this->list_option_id);
     return $q->exec();
 }

 public function deleteByObject($object_id)
 {
     $q = new w2p_Database_Query();
     $q->setDelete('custom_fields_values');
     $q->addWhere('value_object_id=' . (int) $object_id);
     $q->exec();
 }

 public function store($object_id)
 {
     global $db;
     $object_id = (int) $object_id;
     if ($object_id) {
         $this->value_intvalue = (int) $this->value_intvalue;
         $ins_charvalue = $this->value_charvalue == null ? '' : stripslashes($this->value_charvalue);
         $q = new w2p_Database_Query();
         $q->addTable('custom_fields_values');
         if ($this->value_id) {
             $q->addUpdate('value_charvalue', $ins_charvalue);
             $q->addUpdate('value_intvalue', $this->value_intvalue);
             $q->addWhere('value_id = ' . $this->value_id);
         } else {
             $q->addInsert('value_module', '');
             $q->addInsert('value_field_id', $this->field_id);
             $q->addInsert('value_object_id', $object_id);
             $q->addInsert('value_charvalue', $ins_charvalue);
             $q->addInsert('value_intvalue', $this->value_intvalue);
         }
         $rs = $q->exec();
         $q->clear();
         if (!$rs) {
             return $db->ErrorMsg() . ' | SQL: ';
         }
     } else {
         return 'Error: Cannot store field (' . $this->field_name . '), associated id not supplied.';
     }
 }

 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 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 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 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 remove()
 {
     $q = new w2p_Database_Query();
     $q->dropTable('links');
     $q->exec();
     $q->clear();
     $q->setDelete('sysvals');
     $q->addWhere('sysval_title = \'LinkType\'');
     $q->exec();
 }

 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 store(CAppUI $AppUI = null)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->addTable('project_designer_options');
     $q->addReplace('pd_option_user', $this->pd_option_user);
     $q->addReplace('pd_option_view_project', $this->pd_option_view_project);
     $q->addReplace('pd_option_view_gantt', $this->pd_option_view_gantt);
     $q->addReplace('pd_option_view_tasks', $this->pd_option_view_tasks);
     $q->addReplace('pd_option_view_actions', $this->pd_option_view_actions);
     $q->addReplace('pd_option_view_addtasks', $this->pd_option_view_addtasks);
     $q->addReplace('pd_option_view_files', $this->pd_option_view_files);
     $q->addWhere('pd_option_user = ' . (int) $this->pd_option_user);
     $q->exec();
     return true;
 }

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

?>
</th>
	<th><?php 
echo $AppUI->_('Description');
?>
</th>
</tr>

<?php 
foreach ($billingcodes as $code) {
    echo showcodes($code);
}
if (isset($_GET['billingcode_id'])) {
    $q->addQuery('*');
    $q->addTable('billingcode');
    $q->addWhere('billingcode_id = ' . (int) w2PgetParam($_GET, 'billingcode_id', 0));
    list($obj) = $q->loadList();
    echo '
<tr>
	<td>&nbsp;<input type="hidden" name="billingcode_id" value="' . w2PgetParam($_GET, 'billingcode_id', 0) . '" /></td>
	<td><input type="text" class="text" name="billingcode_name" value="' . $obj['billingcode_name'] . '" /></td>
	<td><input type="text" class="text" name="billingcode_value" value="' . $obj['billingcode_value'] . '" /></td>
	<td><input type="text" class="text" name="billingcode_desc" value="' . $obj['billingcode_desc'] . '" /></td>
</tr>';
} else {
    ?>
<tr>
	<td>&nbsp;</td>
	<td><input type="text" class="text" name="billingcode_name" value="" /></td>
	<td><input type="text" class="text" name="billingcode_value" value="" /></td>
	<td><input type="text" class="text" name="billingcode_desc" value="" /></td>

$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');
$q->leftJoin('task_log', 'tlog', 'tlog.task_log_task = tasks.task_id AND tlog.task_log_problem > 0');

 public static function getProjects($contactId)
 {
     $q = new w2p_Database_Query();
     $q->addQuery('p.project_id, p.project_name');
     $q->addTable('project_contacts', 'pc');
     $q->addJoin('projects', 'p', 'p.project_id = pc.project_id', 'inner');
     $q->addWhere("contact_id =  {$contactId}");
     return $q->loadList();
 }