/**
* 删除一条记录
* @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;
}
