<?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.xintong.visualinspection.dao.cluster.UserInfoDao" >
    <resultMap id="BaseResultMap" type="com.xintong.visualinspection.bean.User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="organid" property="organid" jdbcType="INTEGER" />
        <result column="workno" property="workno" jdbcType="VARCHAR" />
        <result property="truename" column="truename" jdbcType="VARCHAR"/>
        <result property="positionid" column="positionid" jdbcType="INTEGER"/>
        <result property="position_name" column="position_name" jdbcType="INTEGER"/>
        <result property="idno" column="idno" jdbcType="VARCHAR"/>
        <result property="pic" column="pic" jdbcType="VARCHAR"/>
        <result column="workno" property="workno" jdbcType="VARCHAR" />
        <result column="fee_station_name" property="fee_station_name" jdbcType="VARCHAR" />
        <result column="delete_time" property="delete_time" jdbcType="DATE" />
        <result column="position_name" property="position_name" jdbcType="VARCHAR" />
    </resultMap>
    <resultMap id="userMap" type="com.xintong.visualinspection.bean.User">
        <id property="id" column="ID"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="password" column="PASSWORD" jdbcType="VARCHAR"/>
        <result column="organid" property="organid" jdbcType="INTEGER" />
        <result property="truename" column="truename" jdbcType="VARCHAR"/>
        <result property="positionid" column="positionid" jdbcType="INTEGER"/>
        <result property="road_manager_id" column="road_manager_id" jdbcType="INTEGER"/>
        <result property="idno" column="idno" jdbcType="VARCHAR"/>
        <result property="pic" column="pic" jdbcType="VARCHAR"/>
        <result column="workno" property="workno" jdbcType="VARCHAR" />
        <result column="delete_time" property="delete_time" jdbcType="DATE" />
        <result column="position_name" property="position_name" jdbcType="VARCHAR" />
        <collection property="roles" ofType="com.xintong.visualinspection.bean.Role">
            <result column="name" property="name"/>
        </collection>
    </resultMap>

    <resultMap id="BaseResultMapByInvalid" type="com.xintong.visualinspection.bean.User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="organid" property="organid" jdbcType="INTEGER" />
        <result column="workno" property="workno" jdbcType="VARCHAR" />
        <result property="truename" column="truename" jdbcType="VARCHAR"/>
        <result property="positionid" column="positionid" jdbcType="INTEGER"/>
        <result property="position_name" column="position_name" jdbcType="INTEGER"/>
        <result property="idno" column="idno" jdbcType="VARCHAR"/>
        <result property="pic" column="pic" jdbcType="VARCHAR"/>
        <result column="workno" property="workno" jdbcType="VARCHAR" />
        <result column="fee_station_name" property="fee_station_name" jdbcType="VARCHAR" />
        <result column="delete_time" property="delete_time" jdbcType="DATE" />
        <result column="invalidtime" property="invalidtime" jdbcType="VARCHAR" />
        <result column="position_name" property="position_name" jdbcType="VARCHAR" />
    </resultMap>


    <select id="getAll" resultMap="BaseResultMap"  >
        SELECT
        a.*,(select NAME from  sys_job where id = a.POSITIONID ) as position_name
        FROM t_sys_users a
    </select>

    <select id="getOne" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
        SELECT
        *
        FROM t_sys_users
        WHERE id = #{id}
    </select>

	<select id="getOneDetail" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
        SELECT
        t.* ,  fs.name AS fee_station_name,(select NAME from  sys_job where id = t.POSITIONID ) as position_name
        FROM t_sys_users t
        LEFT JOIN t_br_layer_fee_station fs ON t.organid = fs.organ_id
        WHERE t.id = #{id} 
    </select>

    <insert id="insert" parameterType="com.xintong.visualinspection.bean.User" >
        INSERT INTO
        t_sys_users
        (username,password,TRUENAME,ORGANID,MOBILE,PHONE,POSITIONID,WORKNO,IDNO,EMAIL,PIC)
        VALUES
        (#{username},#{password},#{truename},#{organid},#{mobile},#{phone},#{positionid},#{workno},#{idno},#{email},#{pic})
    </insert>

    <update id="update" parameterType="com.xintong.visualinspection.bean.User" >
        UPDATE
        t_sys_users
        SET
        <if test="username != null">username = #{username},</if>
        <if test="truename != null">truename = #{truename},</if>
        <if test="organid != null">organid = #{organid},</if>
        <if test="age != null">age = #{age},</if>
        <if test="password != null">password = #{password},</if>
        <if test="pic != null">pic = #{pic},</if>
        <if test="workno != null">workno = #{workno},</if>
        <if test="mobile != null">mobile = #{mobile},</if>
        <if test="idno != null">idno = #{idno},</if>
        <if test="email != null">email = #{email},</if>
        <if test="positionid != null">POSITIONID = #{positionid},</if>
        
        id = #{id}
        WHERE
        id = #{id}
    </update>

	<update id="delete" parameterType="com.xintong.visualinspection.bean.User" >
        UPDATE
        	t_sys_users
        SET
        	status = -1,delete_time = now()
        WHERE
        	id = #{id}
    </update>


    <select id="findByUserName" parameterType="String" resultMap="userMap">
        select u.*,o.parentid as road_manager_id,(select NAME from  sys_job where id = u.POSITIONID ) as position_name
        from t_sys_users u left join t_sys_organ o on u.organid=o.id
        where username= #{username} and u.status >0
    </select>
    
    <select id="getUsersCenterManageCount" parameterType="com.xintong.visualinspection.bean.StatisticsBean"  resultType="java.lang.Integer">
    	SELECT  count(1)  FROM t_sys_users t 
    			INNER JOIN t_br_layer_fee_station tf ON t.organid = tf.organ_id  
    			WHERE t.status >0  AND t.positionid  in (1,4,5,6)  AND t.organid in ( SELECT id FROM t_sys_organ where parentid=#{dept_id} )  
    </select>
    
    <select id="getUsersCountList"  resultType="com.xintong.visualinspection.bean.StatisticsBean">
    	SELECT t.organid AS dept_id , count(1) AS people_num,tf.name FROM t_sys_users t 
    			INNER JOIN t_br_layer_fee_station tf ON t.organid = tf.organ_id  
    			WHERE t.status >0  AND t.positionid  in (1,4,5,6) GROUP BY t.organid
			ORDER BY t.organid 
    </select>
    
    <select id="getUsers" parameterType="com.xintong.visualinspection.bean.User" resultMap="BaseResultMapByInvalid"  >
        SELECT
        t.* , tso.organname AS fee_station_name,tso.parentid AS parent_organid ,deleted_user.starttime AS invalidtime
        FROM t_sys_users t 
        <!-- LEFT JOIN t_br_layer_fee_station fs ON t.organid = fs.organ_id -->
        LEFT JOIN t_sys_organ tso ON t.organid = tso.id
        LEFT JOIN  (select t_s_y.id,date_sub(t_p.starttime,interval 1 hour) as starttime from t_sys_users t_s_y
        LEFT join (select starttime,endtime from check_task_period ) t_p on DELETE_TIME &gt;= starttime and DELETE_TIME &lt; endtime
        WHERE STATUS =-1 and DELETE_TIME &gt;= t_p.starttime and DELETE_TIME &lt; t_p.endtime ) deleted_user
        on deleted_user.id = t.id
        where 1=1 and t.status>0 
        <if test="organid != null and organid != 0">and t.organid = #{organid} </if>
        <if test="username != null">and t.username = #{username} </if>
        <if test="positionid != null and positionid != 0">and t.positionid = #{positionid} </if>
        <if test="classid != null and classid != 0">and t.classid = #{classid} </if>
        <if test="teamid != null and teamid != 0">and t.teamid = #{teamid} </if>
        <if test="parent_organid!=null and parent_organid!=0">
        	and t.organid in ( select id from t_sys_organ where parentid = #{parent_organid}  )
        </if>
        <if test="position_ids != null and position_ids.size() > 0">AND
        		t.positionid in 
        		<foreach collection="position_ids" item="position_id" index="index"
		            open="(" close=")" separator=",">
		            #{position_id}
		        </foreach>
        	</if>
        order by workno
    </select>


    <select id="getAllUsers" parameterType="com.xintong.visualinspection.bean.User" resultMap="BaseResultMapByInvalid"  >
        SELECT
        t.* , fs.name  AS fee_station_name,tso.parentid AS parent_organid ,deleted_user.starttime AS invalidtime
        FROM t_sys_users t LEFT JOIN t_br_layer_fee_station fs ON t.organid = fs.organ_id
        LEFT JOIN t_sys_organ tso ON t.organid = tso.id
        LEFT JOIN  (select t_s_y.id,date_sub(t_p.starttime,interval 1 hour) as starttime from t_sys_users t_s_y
                       LEFT join (select starttime,endtime from check_task_period ) t_p on DELETE_TIME &gt;= starttime and DELETE_TIME &lt; endtime
                     WHERE STATUS =-1 and DELETE_TIME &gt;= t_p.starttime and DELETE_TIME &lt; t_p.endtime ) deleted_user
                 on deleted_user.id = t.id

        where 1=1
        <if test="organid != null and organid != 0">and t.organid = #{organid} </if>
        <if test="username != null">and t.username = #{username} </if>
        <if test="positionid != null and positionid != 0">and t.positionid = #{positionid} </if>
        <if test="classid != null and classid != 0">and t.classid = #{classid} </if>
        <if test="teamid != null and teamid != 0">and t.teamid = #{teamid} </if>
        <if test="parent_organid!=null and parent_organid!=0">
            and t.organid in ( select id from t_sys_organ where parentid = #{parent_organid}  )
        </if>
        <if test="position_ids != null and position_ids.size() > 0">AND
            t.positionid in
            <foreach collection="position_ids" item="position_id" index="index"
                     open="(" close=")" separator=",">
                #{position_id}
            </foreach>
        </if>
        order by workno
    </select>
    <select id="getAllCheckmans" parameterType="com.xintong.visualinspection.bean.User" resultMap="BaseResultMap"  >
        SELECT *
        FROM t_sys_users
        where organid=29 and status>0 and positionid = 2
    </select>
</mapper>