<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.goafanti.common.dao.AdminMapper" >
  <resultMap id="BaseResultMap" type="com.goafanti.common.model.Admin" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="number" property="number" jdbcType="INTEGER" />
    <result column="province" property="province" jdbcType="INTEGER" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="superior_id" property="superiorId" jdbcType="VARCHAR" />
    <result column="city" property="city" jdbcType="INTEGER" />
    <result column="department_id" property="departmentId" jdbcType="VARCHAR"/>
  </resultMap>
  <sql id="Base_Column_List" >
    id, mobile, name, password, email, create_time, number, province, position, superior_id,
    city,department_id
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from admin
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from admin
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.goafanti.common.model.Admin" >
    insert into admin (id, mobile, name, 
      password, email, create_time, 
      number, province, position, superior_id, city,department_id)
    values (#{id,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{password,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{number,jdbcType=INTEGER}, #{province,jdbcType=INTEGER}, #{position,jdbcType=VARCHAR},
      #{superiorId,jdbcType=VARCHAR}, #{city,jdbcType=INTEGER},#{departmentId,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.goafanti.common.model.Admin" >
    insert into admin
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="mobile != null" >
        mobile,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="password != null" >
        password,
      </if>
      <if test="email != null" >
        email,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
      <if test="number != null" >
        number,
      </if>
       <if test="province != null" >
        province,
      </if>
      <if test="position != null" >
        position,
      </if>
      <if test="superiorId != null" >
        superior_id,
      </if>
      <if test="city != null" >
        city,
      </if>
      <if test="departmentId != null">
      	department_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="mobile != null" >
        #{mobile,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        #{password,jdbcType=VARCHAR},
      </if>
      <if test="email != null" >
        #{email,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="number != null" >
        #{number,jdbcType=INTEGER},
      </if>
      <if test="province != null" >
        #{province,jdbcType=INTEGER},
      </if>
      <if test="position != null" >
        #{position,jdbcType=VARCHAR},
      </if>
      <if test="superiorId != null" >
        #{superiorId,jdbcType=VARCHAR},
      </if>
      <if test="city != null" >
        #{city,jdbcType=INTEGER},
      </if>
     <if test="departmentId != null">
     	#{departmentId,jdbcType=VARCHAR},
     </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.goafanti.common.model.Admin" >
    update admin
    <set >
      <if test="mobile != null" >
        mobile = #{mobile,jdbcType=VARCHAR},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="password != null" >
        password = #{password,jdbcType=VARCHAR},
      </if>
      <if test="email != null" >
        email = #{email,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="number != null" >
        number = #{number,jdbcType=INTEGER},
      </if>
       <if test="province != null" >
        province = #{province,jdbcType=INTEGER},
      </if>
      <if test="position != null" >
        position = #{position,jdbcType=VARCHAR},
      </if>
      <if test="superiorId != null" >
        superior_id = #{superiorId,jdbcType=VARCHAR},
      </if>
       <if test="city != null" >
        city = #{city,jdbcType=INTEGER},
      </if>
      <if test="departmentId != null">
     	department_id = #{departmentId,jdbcType=VARCHAR},
      </if>
      <if test="status != null">
     	status = #{status,jdbcType=VARCHAR},
      </if>
      <if test="userNo != null">
     	user_no = #{userNo,jdbcType=VARCHAR},
      </if>
      <if test="duty != null">
     	duty = #{duty,jdbcType=VARCHAR},
      </if>
      <if test="district != null">
     	district = #{district,jdbcType=VARCHAR},
      </if>
      <if test="headPortraitUrl != null">
     	head_portrait_url = #{headPortraitUrl,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.goafanti.common.model.Admin" >
    update admin
    set mobile = #{mobile,jdbcType=VARCHAR},
      name = #{name,jdbcType=VARCHAR},
      password = #{password,jdbcType=VARCHAR},
      email = #{email,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      number = #{number,jdbcType=INTEGER},
      province = #{province,jdbcType=INTEGER},
      position = #{position,jdbcType=VARCHAR},
      superior_id = #{superiorId,jdbcType=VARCHAR},
      city = #{city,jdbcType=INTEGER},
      department_id = #{departmentId,jdbcType=VARCHAR}
    where id = #{id,jdbcType=VARCHAR}
  </update>
  
  <select id="selectAllAdmin" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from admin 
  </select>
  
  <select id="selectByMobile" parameterType="java.lang.String" resultMap="BaseResultMap">
     select 
    <include refid="Base_Column_List" />
    from admin 
    where mobile = #{mobile,jdbcType=VARCHAR}
  </select> 
  
  <select id="selectRolesByPrimaryKey" parameterType="java.lang.String">
   select r.name, r.id, r.type
    from role r
    left join user_role ur on ur.rid=r.id
    where ur.uid = #{uid,jdbcType=VARCHAR}
  </select>
  
  <select id="selectCognizanceConsultant" resultMap="BaseResultMap"  >
    select 
    <include refid="Base_Column_List" />
    from admin
    where id  <![CDATA[ <> ]]> '1'
  </select>
  
  <select id="selectCognizancePrincipal" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4','5','6')
	group by a.id
  </select>
  
  <select id="selectPatentAuthor" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4','5','6')
	group by a.id
  </select>
  
  <select id="selectPatentPrincipal" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4','5','6')
	group by a.id
  </select>
  
   <select id="selectCopyrightConsultant" resultMap="BaseResultMap"  >
    select 
    <include refid="Base_Column_List" />
    from admin
    where id  <![CDATA[ <> ]]> '1'
  </select>
  
   <select id="selectCopyrightPrincipal" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4','5','6')
	group by a.id
  </select>
  
   <select id="selectTechprojectConsultant" resultMap="BaseResultMap"  >
    select 
    <include refid="Base_Column_List" />
    from admin
    where id  <![CDATA[ <> ]]> '1'
  </select>
  
   <select id="selectTechprojectPrincipal" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4','5','6')
	group by a.id
  </select>
  
   <select id="selectRoleByPrimaryKey" parameterType="java.lang.String" resultType="String">
   	SELECT rid FROM user_role 
   		where uid = #{uid,jdbcType=VARCHAR}
   </select>
   
   <select id="selectAccoutManager" resultMap="BaseResultMap">
   	select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid = '4' 
   </select>
   
   <select id="selectTechnician" resultMap="BaseResultMap">
   select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4')
	group by a.id
   </select>
   
   <select id="selectSalesman" resultMap="BaseResultMap">
   select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid in ('5', '6') 
   </select>
  
   <select id="selectContractManager" resultMap="BaseResultMap"  >
    select a.id, a.name, a.position from admin a 
		INNER JOIN user_role  ur on a.id = ur.uid
	where ur.rid not in('1','2','3','4')
	group by a.id
  </select>
  
   <select id="selectTechBroder" resultMap="BaseResultMap">
      select
  	  u.rid as rolesId, a.name,a.id, a.position
  	from user_role u
  	left join admin a on a.id =u.uid
  	left join role r on r.id =u.rid
  	where
  	r.role_name='技术经纪专员'
   </select>
   
   <select id="listAdminSelectBySuperAdmin" resultMap="BaseResultMap">
   	select 
   	<include refid="Base_Column_List" />
   	from admin 
   	where id <![CDATA[ <> ]]> '1'
   </select>
   
   <select id="listAdminSelectByAreaAdmin"  resultMap="BaseResultMap"> 
    select 
    	a.id, a.name, a.mobile,
    	a.create_time as createTime, a.number,  aa.name as superior
    from admin a
    left join admin_location al on al.admin_id = a.id
    left join admin aa on aa.id = a.superior_id
    where 1 = 1
    <if test="provinceList != null and provinceList.size > 0">
	   and  al.province in
	    <foreach collection="provinceList" item="plist"  open="(" separator="," close=")">
			#{plist}
		</foreach>
	</if>
	<if test="cityList != null and cityList.size > 0">
		and al.city in 
		<foreach collection="cityList" item="clist"  open="(" separator="," close=")">
			#{clist}
		</foreach>
	</if>
   </select>
   
    <select id= "findAdminListByPage" parameterType="java.lang.String" resultType="com.goafanti.admin.bo.AdminListBo" >
  	select 
   		 a.number, a.mobile, a.name, 
   		 a.position, a.email, a.id,
   		 a.province,
   		 a.city,
   		 a.create_time as createTime, 
   		 aa.name as superior, 
   		 a.superior_id as superiorId,
   		 a.department_id as departmentId,
   		 dm.name as departmentName
    from admin a 
    left join admin_location al on a.id = al.admin_id
    left join admin aa on aa.id = a.superior_id
    left join department_management dm on a.department_id = dm.id
    where a.id <![CDATA[ <> ]]> '1'
    <if test = "mobile != null"> 
    	and a.mobile = #{mobile,jdbcType=VARCHAR}
    </if>
    <if test = "name != null"> 
    	and a.name = #{name,jdbcType=VARCHAR}
    </if>
    <if test = "number != null">
    	and a.number = #{number,jdbcType=INTEGER}
    </if>
    <if test = "province != null">
    	and a.province = #{province,jdbcType=VARCHAR}
    </if>
    order by a.number 
    <if test="page_sql!=null">
			${page_sql}
	</if>
  </select>
  
  <select id = "findAdminCount" parameterType="java.lang.String" resultType="java.lang.Integer">
   	select count(1) from admin
    where id <![CDATA[ <> ]]> '1'
    <if test = "mobile != null"> 
    	and mobile = #{mobile,jdbcType=VARCHAR}
    </if>
     <if test = "name != null"> 
    	and name = #{name,jdbcType=VARCHAR}
    </if>
    <if test = "number != null">
    	and number = #{number,jdbcType=INTEGER}
    </if>
    <if test = "province != null">
    	and a.province = #{province,jdbcType=VARCHAR}
    </if>
  </select>

  
  <select id="listAdminByName" parameterType="java.lang.String" resultMap="BaseResultMap">
	select id,name from admin where name like concat('%',#{name},'%')  
  </select>

  <!--*************************************************************************-->
  <select id= "selectAdminListByPage" parameterType="java.lang.String" resultType="com.goafanti.admin.bo.AdminListBo" >
  	select x.*,y.name as departmentName from(select 
   		 a.user_no as userNo, a.mobile, a.name, 
   		 a.position, a.email, a.id,
   		 a.province,
   		 a.city,
   		 a.create_time as createTime, 
   		 aa.name as superior, 
   		 a.superior_id as superiorId,
   		 a.department_id as departmentId,
   		 a.duty,a.status
    from admin a 
    left join admin_location al on a.id = al.admin_id
    left join admin aa on aa.id = a.superior_id
    left join user_role ur on a.id=ur.uid
    where a.id <![CDATA[ <> ]]> '1'
    and (a.status !='注销' or a.status is null)
    <if test = "name != null"> 
    	and a.name like concat('%',#{name,jdbcType=VARCHAR},'%')
    </if>
    <if test = "departmentId != null">
    	and a.department_id = #{departmentId,jdbcType=VARCHAR}
    </if>
    <if test = "rid != null">
    	and ur.rid = #{rid,jdbcType=VARCHAR}
    </if>
    <if test = "duty != null">
    	and a.duty = #{duty,jdbcType=VARCHAR}
    </if>
    <if test = "position != null">
    	and a.position = #{position,jdbcType=VARCHAR}
    </if>
    <if test = "status != null">
    	and a.status = #{status,jdbcType=VARCHAR}
    </if>
    <if test = "mobile != null"> 
   		and a.mobile = #{mobile,jdbcType=VARCHAR}
    </if>
    <if test = "superiorId != null"> 
   		and a.superior_id = #{superiorId,jdbcType=VARCHAR}
    </if>
    group by a.id
    order by a.number )x left join department_management y on x.departmentId = y.id
    <if test="page_sql!=null">
			${page_sql}
	</if>
  </select>
  <select id = "selectAdminCount" parameterType="java.lang.String" resultType="java.lang.Integer">
   	select count(t.counts) 
   	from(select a.id counts from (select a.id  from admin a
   	left join user_role ur on a.id=ur.uid
    where a.id <![CDATA[ <> ]]> '1'
    and (a.status != '注销' or a.status is null)
    <if test = "name != null"> 
    	and a.name like concat('%',#{name,jdbcType=VARCHAR},'%')
    </if>
    <if test = "departmentId != null">
    	and a.department_id = #{departmentId,jdbcType=VARCHAR}
    </if>
    <if test = "rid != null">
    	and ur.rid = #{rid,jdbcType=VARCHAR}
    </if>
    <if test = "duty != null">
    	and a.duty = #{duty,jdbcType=VARCHAR}
    </if>
    <if test = "position != null">
    	and a.position = #{position,jdbcType=VARCHAR}
    </if>
    <if test = "status != null">
    	and a.status = #{status,jdbcType=VARCHAR}
    </if>
    <if test = "mobile != null"> 
   		and a.mobile = #{mobile,jdbcType=VARCHAR}
    </if>
    <if test = "superiorId != null"> 
   		and a.superior_id = #{superiorId,jdbcType=VARCHAR}
    </if>
    )a
    group by a.id)t
  </select>
  <insert id="insertn" parameterType="com.goafanti.common.model.Admin" >
    insert into admin (id, mobile, name, 
      password, email, create_time, 
      number, province, position, superior_id, city,department_id,status,user_no,duty,district,head_portrait_url)
    values (#{id,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, 
      #{password,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{number,jdbcType=INTEGER}, #{province,jdbcType=INTEGER}, #{position,jdbcType=VARCHAR},
      #{superiorId,jdbcType=VARCHAR}, #{city,jdbcType=INTEGER},#{departmentId,jdbcType=VARCHAR},
      #{status,jdbcType=VARCHAR},#{userNo,jdbcType=VARCHAR},#{duty,jdbcType=VARCHAR},#{district,jdbcType=VARCHAR},#{headPortraitUrl,jdbcType=VARCHAR}
      )
  </insert>
  <select id="selectUserNoBySuperiorId" parameterType="java.lang.String" resultType="java.lang.String">
  	select 
   		user_no as userNo
    from admin 
    where
    	id=#{superiorId,jdbcType=VARCHAR}
  </select>
  <select id="selectCountBySuperiorId" parameterType="java.lang.String" resultType="java.lang.Integer">
  	select 
   		count(1)
    from admin 
    where
    	superior_id=#{superiorId,jdbcType=VARCHAR}
  </select>
  <select id="selectIdByUserNo" parameterType="java.lang.String" resultType="java.lang.String">
  		select
  		id
  		from admin
  		where
  		user_no=#{UserNo,jdbcType=VARCHAR}
  </select>
  <select id= "selectAllByid" parameterType="java.lang.String" resultType="com.goafanti.common.model.Admin" >
  	select 
   	  id, mobile, name, 
      password, email, create_time as createTime, 
      province, position, 
      superior_id as superiorId,department_id as departmentId,
      status,user_no as userNo,duty,district,head_portrait_url as headPortraitUrl
    from admin 
    where 
    id=#{id,jdbcType=VARCHAR}
  </select>
  <select id= "selectNameByid" parameterType="java.lang.String" resultType="java.lang.String" >
  	select 
   	  name
    from admin 
    where 
    id=#{id,jdbcType=VARCHAR}
  </select>
  <select id= "selectRolesByUid" parameterType="java.lang.String" resultType="java.lang.String">
  	select
  	  r.role_name as roles
  	from user_role ur
  	left join role r on r.id = ur.rid
  	where
  	uid=#{id,jdbcType=VARCHAR}
  </select>
  <select id= "selectRolesIdByUid" parameterType="java.lang.String" resultType="java.lang.String">
  	select
  	  rid as rolesId
  	from user_role 
  	where
  	uid=#{id,jdbcType=VARCHAR}
  </select>
  <select id= "selectStatusByMobile" parameterType="java.lang.String" resultType="java.lang.String" >
  	select 
   	  status
    from admin 
    where 
    mobile=#{mobile,jdbcType=VARCHAR}
  </select>
  <delete id="deleteById" parameterType="java.lang.String" >
  	delete from admin
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  
  <select id="selectIDNBySuperiorId" parameterType="java.lang.String" resultType="com.goafanti.common.model.Admin">
	select id, mobile, name, 
	    password, email, create_time as createTime, 
	    province, position, 
	    superior_id as superiorId,department_id as departmentId,
	    status,user_no as userNo,duty,district,head_portrait_url as headPortraitUrl
	from admin
	where
	superior_id=#{superiorId,jdbcType=VARCHAR}
  </select>
  <select id="selectUserNoById" resultType="java.lang.String">
	select user_no as userNo
	from admin
	where
	id=#{superiorId,jdbcType=VARCHAR}  
  </select>
  <update id="updateByUserNo" parameterType="com.goafanti.common.model.Admin">
	update admin
	set user_no = #{userNo,jdbcType=VARCHAR}
	where
	id=#{id,jdbcType=VARCHAR}  
  </update>
  <select id="selectIdBySuperiorId" parameterType="java.lang.String" resultType="java.lang.String">
	select id
	from admin
	where
	superior_id=#{superiorId,jdbcType=VARCHAR}
  </select>
  
  <select id="selectSubList" parameterType="java.lang.String" resultType="com.goafanti.admin.bo.AdminListBo">
	select
		a.id,
		a.user_no as userNo,
		a.mobile,
		a.name,
		a.position,
		b.name as departmentName
	from
		admin a left join department_management b on
		a.department_id = b.id
	where
		a.department_id =(
			select
				department_id
			from
				admin
			where
				id = #{adminId,jdbcType=VARCHAR}
		)
	and a.id <![CDATA[ <> ]]> #{adminId,jdbcType=VARCHAR}
  </select>
  
  <select id="selectAdminByRoleId" parameterType="java.lang.String" resultMap="BaseResultMap">
	select a.id,a.name from  admin a left join user_role b on a.id = b.uid left join role c on b.rid = c.id where c.id = #{roleId,jdbcType=VARCHAR} limit 1
  </select>
  
  <select id="selectDepartmentStaff" parameterType="java.lang.String" resultType="com.goafanti.admin.bo.AdminListBo">
	select a.id ,a.name,a.department_id,b.name 
		from admin a left join department_management b
		on a.department_id= b.id
		where 1=1
		<if test = "departmentId != null and departmentId !='' "> 
   		and a.department_id = #{departmentId,jdbcType=VARCHAR}
    </if>
		<if test = "name != null"> 
   		and a.name like  concat('%',#{name,jdbcType=VARCHAR},'%')
    </if>
		
  </select>
  <select id="selectAdminName" parameterType="java.lang.String" resultMap="BaseResultMap">
		select id, name from admin where name = #{name,jdbcType=VARCHAR}
  </select>
  
  
</mapper>