package com.slcf.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import com.slcf.pojo.UserBean;

@Repository
public interface UserDao {

	/**
	 * 分页查询所以user
	 * @param spage
	 * @param epage
	 * @return
	 */
//	@Select("select u.*,d.dept_name from tb_user u LEFT JOIN   tb_dept d ON d.dept_id=u.dept_id ORDER BY user_id desc limit #{spage},#{epage}")
	@Select("select top (#{epage}) u.*,d.dept_name from tb_user u LEFT JOIN tb_dept d ON d.dept_id=u.dept_id "
			+" where u.user_id not in (select top (#{spage}) u.user_id from tb_user u LEFT JOIN tb_dept d ON d.dept_id=u.dept_id ORDER BY user_id desc) "
			+" ORDER BY user_id desc")
	public List<UserBean> getUserList(@Param("spage")int spage,@Param("epage")int epage);
	
	/**
	 * 统计user表总数量
	 * @return
	 */
	@Select("select count(*) from tb_user u  ")
	public int getUserCount();
	
	/**
	 * 修改密码
	 * @param id
	 * @param pass
	 * @return
	 */
	@Update("update tb_user set user_password=#{pass} where user_id=#{id}")
	public int upPass(@Param("id")int id,@Param("pass")String pass);
	
	/**
	 * 添加用户操作表
	 * @param uid
	 * @param type
	 * @param name
	 * @return
	 */
	@Insert("insert into tb_user_opt(u_id,uopt_name,uopt_type,uopt_time) values(#{uid},#{name},#{type},getdate())")
	public int insertUserOpt(@Param("uid")int uid,@Param("type")String type,@Param("name")String name);
	
	/**
	 * 根据用户登录账号查询
	 * @param account
	 * @return
	 */
	@Select("select * from tb_user where user_account = #{account}")
	public List<UserBean> getUserByAccount(String account);
	
	/**
	 * 添加用户信息
	 * @param user
	 * @return
	 */
	@Insert("insert into tb_user(dept_id,user_account,user_password,user_name,user_age,user_sex,user_address,user_birth,user_phone,email,reg_time)"
			+ " values(#{dept_id},#{user_account},#{user_password},#{user_name},#{user_age},#{user_sex},#{user_address},#{user_birth},#{user_phone},#{email},getdate())")
	public int saveUser(UserBean user);
	
	/**
	 * 添加用户角色表 
	 * @param uid
	 * @return
	 */
	@Insert("insert into tb_user_role(user_id,role_id)values(#{uid},#{rid})")
	public int saveUserRole(@Param("uid")int uid,@Param("rid")int rid);
	
	/**
	 * 删除用户角色表
	 * @param uid
	 * @return
	 */
	@Delete("delete from tb_user_role where user_id =#{uid}")
	public int delUserRoleByUid(int uid);
	
	@Select("select count(*) from tb_user_role where user_id =#{uid}")
	public int getCount(int uid);
	/**
	 * 删除用户操作表
	 * @param uid
	 * @return
	 */
	@Delete("delete from tb_user_opt where u_id=#{uid}")
	public int delUserOpt(int uid);
	
	/**
	 * 删除用户表
	 * @param id
	 * @return
	 */
	@Delete("delete from tb_user where user_id=#{uid}")
	public int delUser(int id);
	
	/**
	 * 根据用户id查询用户信息
	 * @param id
	 * @return
	 */
	@Select("select * from tb_user where user_id=#{id}")
	public UserBean queryUserById(int id);
	
	@Update("update tb_user set dept_id=#{dept_id},user_name=#{user_name},user_age=#{user_age},user_sex=#{user_sex},"
			+ "user_address=#{user_address},user_birth=#{user_birth},user_phone=#{user_phone},email=#{email} where user_id=#{user_id}")
	public int upUser(UserBean user);
	
	/**
	 * 查询用户拥有的角色
	 * @param uid
	 * @return
	 */
	@Select("SELECT ur.role_id from tb_user_role ur where ur.user_id=#{uid}")
	public List<Integer> getUserRole(int uid);
	
	/**
	 * 查询所有用户信息以及其对应的部门
	 * @return
	 */
	@Select("select u.*,d.dept_name from tb_user u,tb_dept d where d.dept_id =u.dept_id")
	public List<UserBean> queryUserList();
	
	/**
	 * 条件查询用户
	 * @param userId
	 * @param userName
	 * @param deptId
	 * @return
	 */
	public List<UserBean> getUserListByCon(@Param("spage")int spage,@Param("epage")int epage,@Param("userId")Integer userId,@Param("userName")String userName,@Param("deptId")Integer deptId);

	/**
	 * 根据条件统计数量
	 * @param userId
	 * @param userName
	 * @param deptId
	 * @return
	 */
	public int getContByCon(@Param("userId")Integer userId,@Param("userName")String userName,@Param("deptId")Integer deptId);

	
	/**
	 * 表格男数据
	 * @return
	 */
	@Select("select DATE_FORMAT(reg_time,'%Y-%m') mon,count(*) num from tb_user where  reg_time "
			+ " between str_to_date('2017-01-01','%Y-%m-%d')  "
			+ "and str_to_date('2017-12-31','%Y-%m-%d')  and user_sex='男' "
			+ "group by DATE_FORMAT(reg_time,'%Y-%m') order by DATE_FORMAT(reg_time,'%Y-%m')")
	public List<UserBean> getUserCharNan();
	
	/**
	 * 表格女数据
	 * @return
	 */
	@Select("select DATE_FORMAT(reg_time,'%Y-%m') mon,count(*) num from tb_user where "
			+ " reg_time  between str_to_date('2017-01-01','%Y-%m-%d')  "
			+ "and str_to_date('2017-12-31','%Y-%m-%d')  and user_sex='女' "
			+ "group by DATE_FORMAT(reg_time,'%Y-%m') order by DATE_FORMAT(reg_time,'%Y-%m')")
	public List<UserBean> getUserCharNv();
}