PHP JO_Db类代码示例

 public static function createUser($data)
 {
     $db = JO_Db::getDefaultAdapter();
     if (!$data['username'] || Model_Users::getByUsername($data['username'])) {
         return false;
     }
     if (!$data['email'] || Model_Users::getByEmail($data['email'])) {
         return false;
     }
     $db->insert(Model_Users::getPrefixDB() . 'users', array('username' => $data['username'], 'password' => $data['password'], 'email' => $data['email'], 'firstname' => $data['firstname'], 'lastname' => $data['lastname'], 'firmname' => $data['firmname'], 'profile_title' => $data['profile_title'], 'profile_desc' => $data['profile_desc'], 'register_datetime' => $data['register_datetime'], 'status' => 'activate'));
     $user_id = $db->lastInsertId();
     if ($user_id) {
         $upload_path = BASE_PATH . '/uploads';
         $user_path = '/users/' . JO_Date::getInstance($data['register_datetime'], 'yy/mm/')->toString() . $user_id . '/';
         $upload_path .= $user_path;
         if ($data['avatar'] && @getimagesize($data['avatar'])) {
             $name = basename($data['avatar']);
             if (copy($data['avatar'], $upload_path . $name)) {
                 $db->update('users', array('avatar' => $user_path . $name), array('user_id' => $user_id));
             }
         }
         if ($data['homeimage'] && @getimagesize($data['homeimage'])) {
             $name = basename($data['homeimage']);
             if (copy($data['homeimage'], $upload_path . $name)) {
                 $db->update('users', array('homeimage' => $user_path . $name), array('user_id' => $user_id));
             }
         }
         return Model_Users::getUser($user_id);
     }
 }

 public function __construct($username, $password)
 {
     $db = JO_Db::getDefaultAdapter();
     //select default pin data
     $query = self::getListUsersQuery();
     $query->where('email = ? OR username = ?', (string) $username);
     $query->where('password = ?', (string) md5($password))->limit(1);
     $response = $db->fetchRow($query);
     $response = is_array($response) ? $response : array();
     if ($response) {
         $groups = unserialize($response['groups']);
         if (is_array($groups) && count($groups) > 0) {
             $query_group = $db->select()->from('user_groups')->where("ug_id IN (?)", new JO_Db_Expr(implode(',', array_keys($groups))));
             $fetch_all = $db->fetchAll($query_group);
             $response['access'] = array();
             if ($fetch_all) {
                 foreach ($fetch_all as $row) {
                     $modules = unserialize($row['rights']);
                     if (is_array($modules)) {
                         foreach ($modules as $module => $ison) {
                             foreach ($ison as $m => $on) {
                                 $response['access'][$module][$m] = $m;
                             }
                         }
                     }
                 }
             }
         }
         parent::__construct($response);
         new Model_Users_Edit($response['user_id'], array('last_login' => new JO_Db_Expr('NOW()')));
     }
 }

 public function __construct($pin_id)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = self::getListPinsQuery();
     //$query->columns(array('pin_next'=>new JO_Db_Expr('('.$db->select()->from(array('next'=>'pins'),'pin_id')->where('pin_id > ?', (string)$pin_id)->order('pin_id ASC')->limit(1).')')));
     //$query->columns(array('pin_prev'=>new JO_Db_Expr('('.$db->select()->from(array('prev'=>'pins'),'pin_id')->where('pin_id < ?', (string)$pin_id)->order('pin_id DESC')->limit(1).')')));
     if (JO_Session::get('user[user_id]')) {
         $query->columns(array('following_board' => new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->limit(1) . ') + (' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->where('board_id = pins.board_id')->limit(1) . ')-(' . $db->select()->from('users_following_ignore', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->where('board_id = pins.board_id')->limit(1) . '))')));
         $query->columns(array('following_user' => new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->limit(1) . ') + (' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->where('board_id = pins.board_id')->limit(1) . '))')));
         $query->columns(array('following_via' => new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.via')->limit(1) . ') + (' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.via')->where('board_id = pins.board_id')->limit(1) . '))')));
     } else {
         $query->columns(array('following_board' => new JO_Db_Expr("0")));
         $query->columns(array('following_user' => new JO_Db_Expr("0")));
         $query->columns(array('following_via' => new JO_Db_Expr("0")));
     }
     $rows_source = self::describeTable('pins_sources', 'source_');
     $query->joinLeft('pins_sources', 'pins.source_id = pins_sources.source_id', $rows_source);
     $query->where('pins.pin_id = ?', (string) $pin_id);
     //v2.2
     if (Helper_Config::get('config_enable_follow_private_profile')) {
         $query = self::filterFriend($query);
     }
     $response = $db->fetchRow($query);
     $response = is_array($response) ? $response : array();
     $this->data = $response;
     // 		parent::__construct($response);
 }

 public static function deleteCollection($id)
 {
     $db = JO_Db::getDefaultAdapter();
     $db->delete('collections', array('id = ?' => (int) $id));
     $db->delete('items_collections', array('collection_id = ?' => (int) $id));
     $db->delete('collections_rates', array('collection_id = ?' => (int) $id));
 }

 public static function getSubCategoriesAPP($category_id)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = $db->select()->from('category', array('title', 'category_id', 'sort_order'))->where('parent_id = ?', $category_id)->where('status = ?', 1)->order('category.sort_order ASC');
     $result = $db->fetchAll($query);
     return $result;
 }

 public function getPercentRow($user)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = $db->select()->from(Model_Users::getPrefixDB() . 'users', 'commission_percent')->where('user_id= ?', $user['user_id'])->limit(1, 0);
     $user_data = $db->fetchRow($query);
     if ($user_data && round($user_data['commission_percent']) > 0) {
         return array('percent' => floatval($user_data['commission_percent']), 'to' => 0);
     }
     $no_exclusive_author_percent = 30;
     if (JO_Registry::get('no_exclusive_author_percent')) {
         $no_exclusive_author_percent = (int) JO_Registry::get('no_exclusive_author_percent');
     }
     $exclusive_author_percent = 40;
     if (JO_Registry::get('exclusive_author_percent')) {
         $exclusive_author_percent = (int) JO_Registry::get('exclusive_author_percent');
     }
     if ($user['exclusive_author'] == 'false') {
         $percent = array('percent' => $no_exclusive_author_percent, 'to' => 0);
     } else {
         $query = $db->select()->from(Model_Users::getPrefixDB() . 'percents')->where("`from` <= ? AND (`to` > ? OR `to` = 0)", $user['sold']);
         $data = $db->fetchRow($query);
         if (count($data) == 0) {
             $percent = array('percent' => $exclusive_author_percent, 'to' => 0);
         } else {
             $percent = $data;
         }
     }
     return $percent;
 }

 public static function initSession($user_id)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = $db->select()->from(self::getPrefixDB() . 'users')->where('user_id = ?', (int) $user_id)->limit(1, 0);
     $user_data = $db->fetchRow($query);
     if ($user_data && $user_data['status'] == 'activate') {
         $groups = unserialize($user_data['groups']);
         if (is_array($groups) && count($groups) > 0) {
             $query_group = $db->select()->from(self::getPrefixDB() . 'user_groups')->where("ug_id IN (?)", new JO_Db_Expr(implode(',', array_keys($groups))));
             $fetch_all = $db->fetchAll($query_group);
             $user_data['access'] = array();
             if ($fetch_all) {
                 foreach ($fetch_all as $row) {
                     $modules = unserialize($row['rights']);
                     if (is_array($modules)) {
                         foreach ($modules as $module => $ison) {
                             $user_data['access'][$module] = $module;
                         }
                     }
                 }
             }
         }
         if (isset($user_data['access']) && count($user_data['access'])) {
             $user_data['is_admin'] = true;
         }
         $db->update(self::getPrefixDB() . 'users', array('last_login_datetime' => new JO_Db_Expr('NOW()'), 'ip_address' => JO_Request::getInstance()->getClientIp()), array('user_id = ?' => (int) $user_id));
         JO_Session::set($user_data);
     }
     return $user_data;
 }

 public function __construct($data = array())
 {
     $db = JO_Db::getDefaultAdapter();
     //select default pin data
     $query = self::getListPinsQueryLite();
     if (isset($data['cover']) && $data['cover']) {
         $query->where('pins.pin_id = ?', (string) $data['cover']);
     } else {
         $query->where('pins.pin_id = 0');
     }
     if (isset($data['pins']) && count($data['pins'])) {
         $query->orWhere('pins.pin_id IN (?)', new JO_Db_Expr(implode(',', $data['pins'])));
     } else {
         $query->orWhere('pins.pin_id = 0');
     }
     //v2.2
     if (Helper_Config::get('config_enable_follow_private_profile')) {
         $query = self::filterFriend($query);
     }
     //sort and limit add to query from Model_Pins_Abstract
     if (isset($data['cover']) && $data['cover']) {
         if (isset($data['pins']) && count($data['pins'])) {
             $query->order(new JO_Db_Expr('FIELD(pins.pin_id,' . (string) $data['cover'] . ',' . implode(',', $data['pins']) . '), pins.pin_id DESC'));
         } else {
             $query->order(new JO_Db_Expr('FIELD(pins.pin_id,' . (string) $data['cover'] . '), pins.pin_id DESC'));
         }
     } else {
         $query->order('pins.pin_id DESC');
     }
     $this->data = $db->fetchAll($query);
     // 		parent::__construct($db->fetchAll($query));
 }

 public function __construct($data = array())
 {
     $db = JO_Db::getDefaultAdapter();
     try {
         $db->beginTransaction();
         $data['user_id'] = isset($data['user_id']) ? $data['user_id'] : JO_Session::get('user[user_id]');
         $data['public'] = isset($data['public']) ? (int) $data['public'] : 1;
         $data['date_added'] = date('Y-m-d H:i:s');
         $board_id = Helper_Db::create('boards', $data);
         if (!$board_id) {
             return $this;
         }
         Helper_Db::insert('users_boards', array('user_id' => isset($data['user_id']) ? (string) $data['user_id'] : JO_Session::get('user[user_id]'), 'board_id' => $board_id, 'is_author' => 1));
         if (isset($data['friends'])) {
             foreach ($data['friends'] as $fr) {
                 Helper_Db::insert('users_boards', array('user_id' => $fr, 'board_id' => $board_id));
             }
         }
         $config_private_boards = Helper_Config::get('config_private_boards');
         Helper_Db::update('users', array('boards' => new JO_Db_Expr("(SELECT COUNT(board_id) FROM boards WHERE user_id = '" . (isset($data['user_id']) ? (string) $data['user_id'] : JO_Session::get('user[user_id]')) . "' " . ($config_private_boards ? ' AND public = 1' : '') . ")")), array('user_id = ?' => isset($data['user_id']) ? (string) $data['user_id'] : JO_Session::get('user[user_id]')));
         ////autoseo
         new Model_Boards_Autoseo($board_id);
         $this->board_id = $board_id;
         $db->commit();
     } catch (JO_Exception $e) {
         $db->rollBack();
         $this->error = $e->getMessage();
     }
 }

 public static function getWithdraws($from, $to)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = $db->select()->from(Model_Users::getPrefixDB() . 'withdraw')->where("`paid` = 'true'")->order('paid_datetime');
     $d = explode('-', $from);
     if (count($d) == 3 && checkdate($d[1], $d[2], $d[0])) {
         $query->where('`paid_datetime` >= ?', $from);
     }
     $d = explode('-', $to);
     if (count($d) == 3 && checkdate($d[1], $d[2], $d[0])) {
         $query->where('`paid_datetime` <= ?', $to);
     }
     $results = $db->fetchAll($query);
     $return = array();
     if ($results) {
         foreach ($results as $result) {
             $date = explode(' ', $result['paid_datetime']);
             $date = $date[0];
             if (!isset($return[$date]['amount'])) {
                 $return[$date]['amount'] = 0;
             }
             $return[$date]['amount'] += $result['amount'];
         }
     }
     return $return;
 }

 public static function replyMail($data)
 {
     $db = JO_Db::getDefaultAdapter();
     $db->insert('users_mails', array('from_user_id' => isset($data['user_id']) ? (string) $data['user_id'] : JO_Session::get('user[user_id]'), 'date_mail' => new JO_Db_Expr('NOW()'), 'text_mail' => (string) $data['text'], 'parent_mail_id' => isset($data['parent']) ? (string) $data['parent'] : 0));
     $mail_id = $db->lastInsertId();
     if (!$mail_id) {
         return false;
     }
     $replies = self::getReplyConversation($data['parent']);
     $recipients = "";
     foreach ($replies as $reply) {
         if ($reply["from_user_id"] != JO_Session::get('user[user_id]')) {
             $pos = strpos($recipients, $reply["from_user_id"] . ",");
             if ($pos === false) {
                 $recipients .= $reply["from_user_id"] . ",";
                 $db->insert('users_mails_to', array('user_id' => $reply["from_user_id"], 'mail_id' => $mail_id));
             }
         }
         if ($reply["user_id"] != JO_Session::get('user[user_id]')) {
             $pos = strpos($recipients, $reply["user_id"] . ",");
             if ($pos === false) {
                 $recipients .= $reply["user_id"] . ",";
                 $db->insert('users_mails_to', array('user_id' => $reply["user_id"], 'mail_id' => $mail_id));
             }
         }
     }
     return array('status' => "OK");
 }

 public function __construct($pin_id)
 {
     $db = JO_Db::getDefaultAdapter();
     $query = $db->select()->from('pins_gallery')->where('pin_id = ?', $pin_id)->order('sort_order ASC');
     $this->data = $db->fetchAll($query);
     // 		parent::__construct($result);
 }

 public static function get($key = null)
 {
     /*self::getInstance();
     		if($key === null) {
     			return self::$data;
     		} else {
     			if(isset(self::$data[$key])) {
     				return self::$data[$key];
     			} else if(JO_Registry::isRegistered($key)) {
     				JO_Registry::get($key);
     			} else {
     				return null;
     			}
     		}*/
     if (isset(self::$data[$key])) {
         return self::$data[$key];
     } else {
         $db = JO_Db::getDefaultAdapter();
         $query = $db->select()->from('system')->where('`key` = ?', $key)->limit(1);
         $result = $db->fetchRow($query);
         if ($result) {
             if ($result['serialize']) {
                 self::$data[$key] = unserialize($result['value']);
             } else {
                 self::$data[$key] = $result['value'];
             }
             return self::$data[$key];
         }
     }
     return null;
 }

 private function getRandum($query, $data = array())
 {
     $db = JO_Db::getDefaultAdapter();
     //// load from cache
     $check = $db->select()->from('cache_index')->where('start_limit = ?', 'home')->limit(1);
     $cache = $db->fetchRow($check);
     if (!isset($cache['data']) || !$cache['data']) {
         $query->where('pins.pin_id = 0');
         return $query;
     }
     if ($cache && $cache['data']) {
         $query->where('pins.pin_id IN (' . $cache['data'] . ')');
     } else {
         $query->where('pins.pin_id = 0');
     }
     //v2.2
     if (Helper_Config::get('config_enable_follow_private_profile')) {
         $query = self::filterFriend($query);
     }
     //sort and limit add to query from Model_Pins_Abstract
     $query = self::sortOrderLimit($query, $data);
     $query->reset(JO_Db_Select::ORDER);
     $query->order(new JO_Db_Expr('FIELD(pins.pin_id,' . $cache['data'] . ')'));
     return $query;
 }

 public function __construct($board_id)
 {
     $db = JO_Db::getDefaultAdapter();
     try {
         $db->beginTransaction();
         $board_info = new Model_Boards_Board($board_id);
         if (!$board_info->count()) {
             return $this;
         }
         $result = null;
         if ($board_info['board_pins'] > 0) {
             $pins_query = $db->select()->from('pins')->where('board_id = ?', $board_id)->where('user_id = ?', $board_info['user_user_id']);
             $pins = $db->fetchAll($pins_query);
             if ($pins) {
                 foreach ($pins as $pin) {
                     $deleted = new Model_Pins_Delete($pin['pin_id']);
                     if (!$result) {
                         $result = $deleted;
                     }
                 }
             }
         }
         $res = Helper_Db::delete('users_following', array('board_id = ?' => $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::delete('users_following_ignore', array('board_id = ?' => $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::delete('users_boards', array('board_id = ?' => $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::delete('users_history', array('board_id = ?' => $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::delete('url_alias', array('query = ?' => 'board_id=' . $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::delete('boards', array('board_id = ?' => $board_id));
         if (!$result) {
             $result = $res;
         }
         $res = Helper_Db::update('users', array('boards' => new JO_Db_Expr("(SELECT COUNT(board_id) FROM boards WHERE user_id = '" . $board_info['user_user_id'] . "')")), array('user_id = ?' => $board_info['user_user_id']));
         if (!$result) {
             $result = $res;
         }
         if ($board_info['board_pins'] > 0) {
             ///////////////// update latest pins for user /////////////////////
             new Model_Users_UpdateLatestPins($board_info['user_user_id']);
         }
         $this->affected_rows = $result;
         $db->commit();
     } catch (JO_Exception $e) {
         $db->rollBack();
     }
 }