/** * 删除一条记录 * @param integer $userId * @param integer $groupId * @return integer */ public function remove($userId, $groupId) { if (($userId = (int) $userId) <= 0) { return false; } if (($groupId = (int) $groupId) <= 0) { return false; } $pks = array('user_id' => $userId, 'group_id' => $groupId); $tableName = $this->getTblprefix() . TableNames::getUsergroups(); $sql = $this->getCommandBuilder()->createDelete($tableName, '`user_id` = ? AND `group_id` = ?'); $rowCount = $this->delete($sql, $pks); return $rowCount; }
/** * 通过多个字段名和值,查询多条记录 * @param array $params * @param string $order * @param integer $limit * @param integer $offset * @param string $option * @return array */ public function findAll(array $params = array(), $order = '', $limit = 0, $offset = 0, $option = '') { $commandBuilder = $this->getCommandBuilder(); $usersTblName = $this->getTblprefix() . TableNames::getUsers(); $userGroupsTblName = $this->getTblprefix() . TableNames::getUsergroups(); $sql = 'SELECT ' . $option . ' `u`.`user_id`, `u`.`login_name`, `u`.`login_type`, `u`.`user_name`, `u`.`user_mail`, `u`.`user_phone`, `u`.`dt_registered`, `u`.`dt_last_login`, `u`.`dt_last_repwd`, `u`.`ip_registered`, `u`.`ip_last_login`, `u`.`ip_last_repwd`, `u`.`login_count`, `u`.`repwd_count`, `u`.`valid_mail`, `u`.`valid_phone`, `u`.`forbidden`, `u`.`trash` FROM `' . $usersTblName . '` AS `u`'; if (isset($attributes['group_id'])) { $sql .= ' LEFT JOIN `' . $userGroupsTblName . '` AS `g` ON `u`.`user_id` = `g`.`user_id`'; } $condition = '1'; $attributes = array(); if (isset($params['login_name'])) { $loginName = trim($params['login_name']); if ($loginName !== '') { $condition .= ' AND `u`.`login_name` LIKE ' . $commandBuilder::PLACE_HOLDERS; $attributes['login_name'] = '%' . $loginName . '%'; } } if (isset($params['login_type'])) { $loginType = trim($params['login_type']); if ($loginType !== '') { $condition .= ' AND `u`.`login_type` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['login_type'] = $loginType; } } if (isset($params['user_name'])) { $userName = trim($params['user_name']); if ($userName !== '') { $condition .= ' AND `u`.`user_name` LIKE ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_name'] = '%' . $userName . '%'; } else { $condition .= ' AND `u`.`user_name` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_name'] = ''; } } if (isset($params['user_mail'])) { $userMail = trim($params['user_mail']); if ($userMail !== '') { $condition .= ' AND `u`.`user_mail` LIKE ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_mail'] = '%' . $userMail . '%'; } else { $condition .= ' AND `u`.`user_mail` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_mail'] = ''; } } if (isset($params['user_phone'])) { $userPhone = trim($params['user_phone']); if ($userPhone !== '') { $condition .= ' AND `u`.`user_phone` LIKE ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_phone'] = '%' . $userPhone . '%'; } else { $condition .= ' AND `u`.`user_phone` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_phone'] = ''; } } if (isset($params['valid_mail'])) { $validMail = trim($params['valid_mail']); if ($validMail !== '') { $condition .= ' AND `u`.`valid_mail` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['valid_mail'] = $validMail; } } if (isset($params['valid_phone'])) { $validPhone = trim($params['valid_phone']); if ($validPhone !== '') { $condition .= ' AND `u`.`valid_phone` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['valid_phone'] = $validPhone; } } if (isset($params['forbidden'])) { $forbidden = trim($params['forbidden']); if ($forbidden !== '') { $condition .= ' AND `u`.`forbidden` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['forbidden'] = $forbidden; } } if (isset($params['trash'])) { $trash = trim($params['trash']); if ($trash !== '') { $condition .= ' AND `u`.`trash` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['trash'] = $trash; } } if (isset($params['group_id'])) { $groupId = (int) $params['group_id']; if ($groupId > 0) { $condition .= ' AND `g`.`group_id` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['group_id'] = $groupId; } } if (isset($params['dt_registered_ge'])) { $dtRegisteredGe = trim($params['dt_registered_ge']); if ($dtRegisteredGe !== '') { $condition .= ' AND `u`.`dt_registered` >= ' . $commandBuilder::PLACE_HOLDERS; $attributes['dt_registered_ge'] = $dtRegisteredGe; } } if (isset($params['dt_registered_le'])) { $dtRegisteredLe = trim($params['dt_registered_le']); if ($dtRegisteredLe !== '') { $condition .= ' AND `u`.`dt_registered` <= ' . $commandBuilder::PLACE_HOLDERS; $attributes['dt_registered_le'] = $dtRegisteredLe; } } if (isset($params['dt_last_login_ge'])) { $dtLastLoginGe = trim($params['dt_last_login_ge']); if ($dtLastLoginGe !== '') { $condition .= ' AND `u`.`dt_last_login` >= ' . $commandBuilder::PLACE_HOLDERS; $attributes['dt_last_login_ge'] = $dtLastLoginGe; } } if (isset($params['dt_last_login_le'])) { $dtLastLoginLe = trim($params['dt_last_login_le']); if ($dtLastLoginLe !== '') { $condition .= ' AND `u`.`dt_last_login` <= ' . $commandBuilder::PLACE_HOLDERS; $attributes['dt_last_login_le'] = $dtLastLoginLe; } } if (isset($params['login_count_ge'])) { $loginCountGe = (int) $params['login_count_ge']; if ($loginCountGe >= 0) { $condition .= ' AND `u`.`login_count` >= ' . $commandBuilder::PLACE_HOLDERS; $attributes['login_count_ge'] = $loginCountGe; } } if (isset($params['login_count_le'])) { $loginCountLe = (int) $params['login_count_le']; if ($loginCountLe >= 0) { $condition .= ' AND `u`.`login_count` <= ' . $commandBuilder::PLACE_HOLDERS; $attributes['login_count_le'] = $loginCountLe; } } if (isset($params['ip_registered'])) { $ipRegistered = (int) $params['ip_registered']; $condition .= ' AND `u`.`ip_registered` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['ip_registered'] = $ipRegistered; } if (isset($params['user_id'])) { $userId = (int) $params['user_id']; if ($userId > 0) { $condition .= ' AND `u`.`user_id` = ' . $commandBuilder::PLACE_HOLDERS; $attributes['user_id'] = $userId; } } $sql = $commandBuilder->applyCondition($sql, $condition); $sql = $commandBuilder->applyOrder($sql, $order); $sql = $commandBuilder->applyLimit($sql, $limit, $offset); if ($option === 'SQL_CALC_FOUND_ROWS') { $ret = $this->fetchAllNoCache($sql, $attributes); if (isset($attributes['login_name'])) { $attributes['login_name'] = $loginName; } if (isset($attributes['user_name'])) { $attributes['user_name'] = $userName; } if (isset($attributes['user_mail'])) { $attributes['user_mail'] = $userMail; } if (isset($attributes['user_phone'])) { $attributes['user_phone'] = $userPhone; } if (isset($attributes['ip_registered'])) { $attributes['ip_registered'] = long2ip($ipRegistered); } if (is_array($ret)) { $ret['attributes'] = $attributes; $ret['order'] = $order; $ret['limit'] = $limit; $ret['offset'] = $offset; } } else { $ret = $this->fetchAll($sql, $attributes); } return $ret; }