PHP w2p_Database_Query::addTable方法代码示例

 public function install()
 {
     global $AppUI;
     // Create holiday table
     $q = new w2p_Database_Query();
     $q->createTable('holiday');
     $q->createDefinition('(
         `holiday_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
         `holiday_user` int(10) NOT NULL DEFAULT \'0\',
         `holiday_type` int(10) NOT NULL DEFAULT \'0\',
         `holiday_annual` int(10) NOT NULL DEFAULT \'0\',
         `holiday_start_date` datetime NOT NULL DEFAULT \'0000-00-00 00:00:00\',
         `holiday_end_date` datetime NOT NULL DEFAULT \'0000-00-00 00:00:00\',
         `holiday_description` text,
         PRIMARY KEY (`holiday_id`),
         KEY `holiday_start_end_date` (`holiday_start_date`,`holiday_end_date`),
         KEY `holiday_type` (`holiday_type`),
         KEY `holiday_user` (`holiday_user`)
         ) ENGINE=MyISAM CHARACTER SET=utf8 COLLATE=utf8_general_ci
     ');
     $q->exec();
     $q->clear();
     // Create settings table
     $q->createTable('holiday_settings');
     $q->createDefinition('(
         `holiday_manual` int(10) NOT NULL default \'0\',
         `holiday_auto` int(10) NOT NULL default \'0\',
         `holiday_driver` int(10) NOT NULL default \'-1\',
         `holiday_filter` int(10) NOT NULL default \'-1\',
         UNIQUE KEY `holiday_manual` (holiday_manual),
         UNIQUE KEY `holiday_auto` (holiday_auto),
         UNIQUE KEY `holiday_driver` (holiday_driver),
         UNIQUE KEY `holiday_filter` (holiday_filter)
         ) ENGINE=MyISAM CHARACTER SET=utf8 COLLATE=utf8_general_ci
     ');
     $q->exec();
     $q->clear();
     // Set default settings
     $q->addTable('holiday_settings');
     $q->addInsert('holiday_manual', 0);
     $q->addInsert('holiday_auto', 0);
     $q->addInsert('holiday_driver', -1);
     $q->addInsert('holiday_filter', -1);
     $q->exec();
     $i = 0;
     $user_holiday_types = array('holidays', 'sick stoppage', 'formation', 'seminar', 'mission', 'strike', 'other holiday');
     foreach ($user_holiday_types as $user_holiday_type) {
         $q->clear();
         $q->addTable('sysvals');
         $q->addInsert('sysval_key_id', 1);
         // select list
         $q->addInsert('sysval_title', 'UserHolidayType');
         $q->addInsert('sysval_value', $user_holiday_type);
         $q->addInsert('sysval_value_id', $i++);
         $q->exec();
     }
     $perms = $AppUI->acl();
     return $perms->registerModule('Holiday', 'holiday');
 }

 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 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 store()
 {
     $this->w2PTrimAll();
     $msg = $this->check();
     if ($msg) {
         return get_class($this) . '::store-check failed - ' . $msg;
     }
     $values = parseFormatSysval($this->sysval_value, $this->sysval_key_id);
     //lets delete the old values
     $q = new w2p_Database_Query();
     if ($this->sysval_key_id && $this->sysval_title) {
         $q->setDelete('sysvals');
         $q->addWhere('sysval_key_id = ' . (int) $this->sysval_key_id);
         $q->addWhere('sysval_title = \'' . $this->sysval_title . '\'');
         if (!$q->exec()) {
             $q->clear();
             return get_class($this) . '::store failed: ' . db_error();
         }
     }
     foreach ($values as $key => $value) {
         $q->addTable('sysvals');
         $q->addInsert('sysval_key_id', $this->sysval_key_id);
         $q->addInsert('sysval_title', $this->sysval_title);
         $q->addInsert('sysval_value_id', $key);
         $q->addInsert('sysval_value', $value);
         if (!$q->exec()) {
             $q->clear();
             return get_class($this) . '::store failed: ' . db_error();
         }
         $q->clear();
     }
     return null;
 }

 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 _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}'");
     $q->addWhere("user_password = '" . MD5($password) . "'");
     $q->exec();
     if ($row = $q->fetchRow()) {
         $this->user_id = $row['user_id'];
         return true;
     }
     return false;
 }

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

    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
    $user_list = w2PgetUsersHashList();
    // Now which tasks will we need and the real allocated hours (estimated time / number of users)
    // Also we will use tasks with duration_type = 1 (hours) and those that are not marked
    // as milstones
    // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
    $working_hours = $w2Pconfig['daily_working_hours'];
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addTable('user_tasks', 'ut');
    $q->addJoin('projects', '', 'project_id = task_project', 'inner');
    $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) . '\'');

}
$mods = $AppUI->getActiveModules();
$history_active = !empty($mods['history']) && canView('history');
// Number of columns (used to calculate how many columns to span things through)
$cols = 13;
/****
// Let's figure out which tasks are selected
*/
$q = new w2p_Database_Query();
$pinned_only = (int) w2PgetParam($_GET, 'pinned', 0);
if (isset($_GET['pin'])) {
    $pin = (int) w2PgetParam($_GET, 'pin', 0);
    $msg = '';
    // load the record data
    if ($pin) {
        $q->addTable('user_task_pin');
        $q->addInsert('user_id', $AppUI->user_id);
        $q->addInsert('task_id', $task_id);
    } else {
        $q->setDelete('user_task_pin');
        $q->addWhere('user_id = ' . (int) $AppUI->user_id);
        $q->addWhere('task_id = ' . (int) $task_id);
    }
    if (!$q->exec()) {
        $AppUI->setMsg('ins/del err', UI_MSG_ERROR, true);
    } else {
        $q->clear();
    }
    $AppUI->redirect('', -1);
}
$AppUI->savePlace();