PHP w2p_Database_Query类代码示例

 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 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 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 remove(CAppUI $AppUI = null)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->dropTable('history');
     $q->exec();
     $perms = $AppUI->acl();
     return $perms->unregisterModule('history');
 }

 public function bind($hash)
 {
     if (!is_array($hash)) {
         return get_class($this) . "::bind failed";
     } else {
         $q = new w2p_Database_Query();
         $q->bindHashToObject($hash, $this);
         $q->clear();
         return null;
     }
 }

 /**
  * @todo refactor
  */
 public function store()
 {
     $q = $this->_getQuery();
     if ($msg = $this->delete()) {
         return 'CPreference::store-delete failed ' . $msg;
     }
     $q = new w2p_Database_Query();
     if (!$q->insertObject('user_preferences', $this)) {
         return 'CPreference::store failed ' . db_error();
     } else {
         return null;
     }
 }

 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 upgrade($old_version)
 {
     switch ($old_version) {
         case '1.0':
             $q = new w2p_Database_Query();
             $q->addTable('resources');
             $q->addField('resource_key', 'varchar(64) not null default ""');
             $q->exec();
             if (db_error()) {
                 return false;
             }
             // FALLTHROUGH
         // FALLTHROUGH
         case '1.0.1':
             break;
     }
     return true;
 }

 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 install()
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->createTable('links');
     $q->createDefinition('(
         link_id int( 11 ) NOT NULL AUTO_INCREMENT ,
         link_url varchar( 255 ) NOT NULL default "",
         link_project int( 11 ) NOT NULL default "0",
         link_task int( 11 ) NOT NULL default "0",
         link_name varchar( 255 ) NOT NULL default "",
         link_parent int( 11 ) default "0",
         link_description text,
         link_owner int( 11 ) default "0",
         link_date datetime default NULL ,
         link_icon varchar( 20 ) default "obj/",
         link_category int( 11 ) NOT NULL default "0",
         PRIMARY KEY ( link_id ) ,
         KEY idx_link_task ( link_task ) ,
         KEY idx_link_project ( link_project ) ,
         KEY idx_link_parent ( link_parent )
         ) ENGINE = MYISAM DEFAULT CHARSET=utf8 ');
     $q->exec($sql);
     $i = 0;
     $linkTypes = array('Unknown', 'Document', 'Application');
     foreach ($linkTypes as $linkType) {
         $q->clear();
         $q->addTable('sysvals');
         $q->addInsert('sysval_key_id', 1);
         $q->addInsert('sysval_title', 'LinkType');
         $q->addInsert('sysval_value', $linkType);
         $q->addInsert('sysval_value_id', $i);
         $q->exec();
         $i++;
     }
     $perms = $AppUI->acl();
     return $perms->registerModule('Links', 'links');
 }

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

$q->addQuery('ut.user_task_priority');
$q->addQuery('task_priority, task_percent_complete');
$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');

** 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);
}
if ($owner > 0) {
    $q->addWhere('pr.project_owner = ' . (int) $owner);
}