public function db()
{
$m = new Model('Post');
$list = $m->select();
dump($list);
echo $m->getLastSql();
}
public function updatescan()
{
$json = json_decode($GLOBALS['HTTP_RAW_POST_DATA']);
/* $key="access_token";
$jwt=$json->access_token;
if($json->access_token==null){
$log="无access_token";
}else{
$jwt=JWT::decode($jwt,$key,array('HS256'));
$timenow=date("Y-m-d",strtotime('now'));
if(!($jwt->aud==$json->username&&$timenow<$jwt->exp&&$timenow>$jwt->iat)){
$log="超时或名称不对称";
}
} */
$Model = new Model();
$title = $json->title;
$ids = $json->updated;
$count = count($ids);
$sql = "select uuid_short();";
$res = $Model->query($sql);
$scanid = $res[0]['uuid_short()'];
for ($i = 0; $i < $count; $i++) {
$userid = $ids[$i]->id;
$username = $ids[$i]->name;
$sql = "insert into " . __PREFIX__ . "scan (userid,username,scanid,scanname) values ({$userid},'{$username}',{$scanid},'{$title}');";
$Model->query($sql);
$Model->getLastSql();
}
}
/**
*保存新增用户
*/
public function AddSave()
{
//获取参数
$loginid = I('loginid');
$username = I('username');
$password = I('password');
$email = I('email');
$mobile = I('mobile');
$roleid = I('role_id', 0, 'int');
// 申明Model
$UserModel = M('User', 'sys_', 'DB_CONFIG1');
// 查找该账号或邮箱是否存在
$user = $UserModel->where("LoginId='%s'", $loginid)->select();
if ($user != null) {
$result['result'] = false;
$result['msg'] = '该用户已存在';
$this->ajaxReturn($result);
}
//权限选择异常
if (!$roleid) {
$result['result'] = false;
$result['msg'] = '数据异常';
$this->ajaxReturn($result);
}
// 实例化Model
$model = new Model();
$model->startTrans();
// 新增对象
$data['LoginId'] = $loginid;
$data['UserName'] = $username;
$data['PassWord'] = md5($password);
$data['Email'] = $email;
$data['Mobile'] = $mobile;
$data['CreateTime'] = time();
//获取当前时间戳
// 新增保存
$userid = $model->table('sys_user')->add($data);
if ($userid) {
$sql = $model->getLastSql();
//保存角色表数据
$UserRole = M('role_user', 'sys_', 'DB_CONFIG1');
$roleData['role_id'] = $roleid;
$roleData['user_id'] = $userid;
//权限新增
if ($model->table('sys_role_user')->add($roleData)) {
// 事务提交
$model->commit();
//返回JSON对象
$result['result'] = true;
$result['msg'] = '新增成功！';
$this->ajaxReturn($result);
} else {
//事务回滚
$model->rollback();
//返回JSON对象
$result['result'] = false;
$result['msg'] = '新增失败,已回滚！';
$this->ajaxReturn($result);
}
} else {
//事务回滚
$model->rollback();
//返回JSON对象
$result['result'] = false;
$result['msg'] = '新增失败！';
$this->ajaxReturn($result);
}
}
public function delete()
{
$uid = trim($_GET['uid']);
if (empty($uid)) {
$this->error("非法操作！", U('User/index'));
} else {
$user = D("User");
$where['id'] = $uid;
/*
* 删除Boss的审计收费信息
*/
$pro['user_id'] = $uid;
D('Probation')->where($pro)->delete();
/*
* 删除Boss的数据库信息
*/
$db = "db_" . $uid;
$sql = "DROP DATABASE " . $db;
$mo = new Model();
$mo->query($sql);
$mo->getLastSql();
/*
* 删除当前ID 的Boss 下的所有销售员Salesman信息 id -> name ->belong -> id (for Del)
*/
$name = $user->field("id,name ")->where($where)->find();
$be['belong'] = $name['name'];
$belong = $user->field("id")->where($be)->select();
$count = $user->field("id")->where($be)->count();
for ($i = 0; $i < $count; $i++) {
$wh["id"] = $belong[$i]['id'];
D('User')->where($wh)->delete();
}
/*
* 删除 当前 Boss 个人信息
*/
$res = $user->where($where)->relation(true)->delete();
if ($res) {
$this->success("删除成功", U('User/index'));
} else {
$this->error("删除失败！");
}
}
}
public function testQuery()
{
$user_model = new Model('user', $this->getConfig());
$sql = "select id,create_time from tp_user where username='******' limit 1";
$result = $user_model->query($sql);
$id = $result[0]['id'];
$time = $result[0]['create_time'];
$bind = ['create_time' => $time, 'status' => 1];
$info = $user_model->where(['create_time' => ':create_time'])->where(['status' => ':status'])->bind($bind)->field(true)->find(['cache' => ['key' => true]]);
$data = ['id' => $id, 'username' => 'test', 'password' => md5('123456'), 'status' => '1', 'create_time' => $time];
$this->assertEquals($data, $info);
$_GET['id'] = $id;
$result = $user_model->where(['id' => ':id'])->bind('id', $_GET['id'])->field('password,create_time', true)->order('id')->limit('0,10')->select(['cache' => ['key' => true, 'expire' => 0], 'index' => 'username']);
$data = ['id' => $id, 'username' => 'test', 'status' => '1'];
$this->assertEquals($data, $result['test']);
$_GET['status'] = '1';
$result = $user_model->where(['status' => ':status'])->bind('status', $_GET['status'], \PDO::PARAM_INT)->field('password,create_time', true)->order('id', 'desc')->index('id,username')->page('0,10')->select();
$data = ['1' => 'test', '2' => 'test2'];
$this->assertEquals($data, $result);
$time = $user_model->where(['status' => 1])->cache('user_create_time')->getField('create_time');
$ids = $user_model->where(['status' => 1])->cache('user_id')->getField('id', true);
$this->assertEquals(2, count($ids));
$result = $user_model->cache(true)->getField('username,status,create_time', '|');
$data = ['test' => '1|' . $time, 'test2' => '1|' . $time];
$this->assertEquals($data, $result);
$result = $user_model->cache(10)->getField('username,status');
$data = ['test' => '1', 'test2' => '1'];
$this->assertEquals($data, $result);
$result = $user_model->scope(['field' => 'username', 'where' => 'status=1'])->select();
$data = [['username' => 'test'], ['username' => 'test2']];
$this->assertEquals($data, $result);
$result = $user_model->master()->lock(true)->distinct(true)->force('create_time')->comment('查询用户名')->field('username')->fetchSql(true)->select();
$sql = 'SELECT DISTINCT `username` FROM `tp_user` FORCE INDEX ( create_time ) FOR UPDATE /* 查询用户名 */';
$this->assertEquals($sql, $result);
$order_model = new Model('order', $this->getConfig());
$result = $order_model->field('user_id,sum(amount) amount')->group('user_id')->having('sum(amount) > 1000')->select();
$this->assertEmpty($result);
$result = $order_model->getLastSql();
$sql = 'SELECT `user_id`,sum(amount) amount FROM `tp_order` GROUP BY user_id HAVING sum(amount) > 1000 ';
$this->assertEquals($sql, $result);
}
