<?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.dcsoft.system.mapper.SysPeopleRecordMapper">

    <resultMap type="SysPeopleRecord" id="SysPeopleRecordResult">
        <result property="id"    column="id"    />
        <result property="peopleId"    column="people_id"    />
        <result property="equipmentId"    column="equipment_id"    />
        <result property="deviceIp"    column="device_ip"    />
        <result property="admitGuid"    column="admit_guid"    />
        <result property="recMode"    column="rec_mode"    />
        <result property="filePath"    column="file_path"    />
        <result property="showTime"    column="show_time"    />
        <result property="showDate"    column="show_date"    />
        <result property="aliveType"    column="alive_type"    />
        <result property="recScore"    column="rec_score"    />
        <result property="deviceNo"    column="device_no"    />
        <result property="deviceVersion"    column="device_version"    />
        <result property="source"    column="source"    />
        <result property="type"    column="type"    />
        <result property="cardNo"    column="card_no"    />
        <result property="deviceName"    column="device_name"    />
        <result property="recType"    column="rec_type"    />
        <result property="result"    column="result"    />
        <result property="permissionTimeType"    column="permission_time_type"    />
        <result property="passTimeType"    column="pass_time_type"    />
        <result property="recModeType"    column="rec_mode_type"    />
        <result property="storageId"    column="storage_id"    />
        <result property="timestamp"    column="timestamp"    />
        <result property="admitName"    column="admit_name"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="flag"    column="flag"    />
        <result property="groupId"    column="group_id"    />
        <result property="peopleName"    column="people_name"    />
        <result property="equipmentName"    column="equipmentName"    />
        <result property="gh"    column="gh"    />
        <result property="sign"    column="sign"    />

        <association property="equipment"    column="equipment_id" javaType="SysEquipment" resultMap="equipmentResult" />

    </resultMap>

    <resultMap id="equipmentResult" type="SysEquipment">
        <id     property="id"    column="equipment_id"     />
        <result property="name"  column="equipmentName"   />
    </resultMap>

    <sql id="selectSysPeopleRecordVo">
        select spr.id, spr.people_id, spr.equipment_id, spr.device_ip, spr.admit_guid, spr.rec_mode, spr.file_path, spr.show_time, spr.show_date, spr.alive_type,
               spr.rec_score, spr.device_no, spr.device_version, spr.source, spr.type, spr.card_no, spr.device_name, spr.rec_type, spr.result, spr.permission_time_type,
               spr.pass_time_type, spr.rec_mode_type, spr.storage_id, spr.timestamp, spr.admit_name, spr.remark, spr.create_by, spr.create_time, spr.update_by, spr.update_time,
               e.name equipmentName,spr.flag,spr.group_id,p.name people_name,p.gh,spr.sign
        from sys_people_record spr
        left join sys_equipment e on spr.equipment_id=e.id
        left join sys_people p on spr.people_id=p.id

    </sql>

    <select id="selectSysPeopleRecordList" parameterType="SysPeopleRecord" resultMap="SysPeopleRecordResult">
        <include refid="selectSysPeopleRecordVo"/>
        <where>
            <if test="peopleId != null  and peopleId != ''"> and spr.people_id = #{peopleId}</if>
            <if test="equipmentId != null  and equipmentId != ''"> and spr.equipment_id = #{equipmentId}</if>
            <if test="deviceIp != null  and deviceIp != ''"> and spr.device_ip = #{deviceIp}</if>
            <if test="admitGuid != null  and admitGuid != ''"> and spr.admit_guid = #{admitGuid}</if>
            <if test="recMode != null  and recMode != ''"> and spr.rec_mode = #{recMode}</if>
            <if test="filePath != null  and filePath != ''"> and spr.file_path = #{filePath}</if>
            <if test="showTime != null "> and spr.show_time = #{showTime}</if>
            <if test="showDate != null "> and DATE_FORMAT(spr.show_date, '%Y-%m-%d') = DATE_FORMAT(#{showDate}, '%Y-%m-%d')</if>
            <if test="showDateState != null"> and spr.show_date <![CDATA[ >= ]]> #{showDateState}</if>
            <if test="showDateEnd != null"> and spr.show_date <![CDATA[ <= ]]> #{showDateEnd}</if>
            <if test="aliveType != null  and aliveType != ''"> and spr.alive_type = #{aliveType}</if>
            <if test="recScore != null  and recScore != ''"> and spr.rec_score = #{recScore}</if>
            <if test="deviceNo != null  and deviceNo != ''"> and spr.device_no = #{deviceNo}</if>
            <if test="deviceVersion != null  and deviceVersion != ''"> and spr.device_version = #{deviceVersion}</if>
            <if test="source != null  and source != ''"> and spr.source = #{source}</if>
            <if test="type != null  and type != ''"> and spr.type = #{type}</if>
            <if test="cardNo != null  and cardNo != ''"> and spr.card_no = #{cardNo}</if>
            <if test="deviceName != null  and deviceName != ''"> and spr.device_name like concat('%', #{deviceName}, '%')</if>
            <if test="recType != null  and recType != ''"> and spr.rec_type = #{recType}</if>
            <if test="result != null  and result != ''"> and spr.result = #{result}</if>
            <if test="permissionTimeType != null  and permissionTimeType != ''"> and spr.permission_time_type = #{permissionTimeType}</if>
            <if test="passTimeType != null  and passTimeType != ''"> and spr.pass_time_type = #{passTimeType}</if>
            <if test="recModeType != null  and recModeType != ''"> and spr.rec_mode_type = #{recModeType}</if>
            <if test="storageId != null  and storageId != ''"> and spr.storage_id = #{storageId}</if>
            <if test="timestamp != null "> and spr.timestamp = #{timestamp}</if>
            <if test="flag != null  and flag != ''"> and spr.flag = #{flag}</if>
            <if test="groupId != null  and groupId != ''">
                <if test="groupId == -1 ">
                    and spr.group_id is not null
                </if>
                <if test="groupId != -1 ">
                    and spr.group_id = #{groupId}
                </if>
             </if>
            <if test="admitName != null  and admitName != ''"> and spr.admit_name like concat('%', #{admitName}, '%')</if>
            <if test="peopleName != null  and peopleName != ''">  and p.name like concat('%', #{peopleName}, '%')</if>

        </where>
        order by spr.show_time desc
    </select>

    <select id="selectSysPeopleRecordLists" parameterType="SysPeopleRecord" resultMap="SysPeopleRecordResult">
        <include refid="selectSysPeopleRecordVo"/>
        <where>
            <if test="peopleId != null  and peopleId != ''"> and spr.people_id = #{peopleId}</if>
            <if test="equipmentId != null  and equipmentId != ''"> and spr.equipment_id = #{equipmentId}</if>
            <if test="deviceIp != null  and deviceIp != ''"> and spr.device_ip = #{deviceIp}</if>
            <if test="admitGuid != null  and admitGuid != ''"> and spr.admit_guid = #{admitGuid}</if>
            <if test="recMode != null  and recMode != ''"> and spr.rec_mode = #{recMode}</if>
            <if test="filePath != null  and filePath != ''"> and spr.file_path = #{filePath}</if>
            <if test="showTime != null "> and spr.show_time = #{showTime}</if>
            <if test="showDate != null "> and DATE_FORMAT(spr.show_date, '%Y-%m-%d') = DATE_FORMAT(#{showDate}, '%Y-%m-%d')</if>
            <if test="aliveType != null  and aliveType != ''"> and spr.alive_type = #{aliveType}</if>
            <if test="recScore != null  and recScore != ''"> and spr.rec_score = #{recScore}</if>
            <if test="deviceNo != null  and deviceNo != ''"> and spr.device_no = #{deviceNo}</if>
            <if test="deviceVersion != null  and deviceVersion != ''"> and spr.device_version = #{deviceVersion}</if>
            <if test="source != null  and source != ''"> and spr.source = #{source}</if>
            <if test="type != null  and type != ''"> and spr.type = #{type}</if>
            <if test="cardNo != null  and cardNo != ''"> and spr.card_no = #{cardNo}</if>
            <if test="deviceName != null  and deviceName != ''"> and spr.device_name like concat('%', #{deviceName}, '%')</if>
            <if test="recType != null  and recType != ''"> and spr.rec_type = #{recType}</if>
            <if test="result != null  and result != ''"> and spr.result = #{result}</if>
            <if test="permissionTimeType != null  and permissionTimeType != ''"> and spr.permission_time_type = #{permissionTimeType}</if>
            <if test="passTimeType != null  and passTimeType != ''"> and spr.pass_time_type = #{passTimeType}</if>
            <if test="recModeType != null  and recModeType != ''"> and spr.rec_mode_type = #{recModeType}</if>
            <if test="storageId != null  and storageId != ''"> and spr.storage_id = #{storageId}</if>
            <if test="timestamp != null "> and spr.timestamp = #{timestamp}</if>
            <if test="flag != null  and flag != ''"> and spr.flag = #{flag}</if>
            <if test="groupId != null  and groupId != ''">
                <if test="groupId == -1 ">
                    and spr.group_id is not null
                </if>
                <if test="groupId != -1 ">
                    and spr.group_id = #{groupId}
                </if>
            </if>
            <if test="admitName != null  and admitName != ''"> and spr.admit_name like concat('%', #{admitName}, '%')</if>
            <if test="peopleName != null  and peopleName != ''">  and p.name like concat('%', #{peopleName}, '%')</if>

        </where>
        order by spr.show_time
    </select>

    <select id="selectSysPeopleRecordById" parameterType="Long" resultMap="SysPeopleRecordResult">
        <include refid="selectSysPeopleRecordVo"/>
        where spr.id = #{id}
    </select>

    <insert id="insertSysPeopleRecord" parameterType="SysPeopleRecord" useGeneratedKeys="true" keyProperty="id">
        insert into sys_people_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="peopleId != null">people_id,</if>
            <if test="equipmentId != null">equipment_id,</if>
            <if test="deviceIp != null">device_ip,</if>
            <if test="admitGuid != null">admit_guid,</if>
            <if test="recMode != null">rec_mode,</if>
            <if test="filePath != null">file_path,</if>
            <if test="showTime != null">show_time,</if>
            <if test="showDate != null">show_date,</if>
            <if test="aliveType != null">alive_type,</if>
            <if test="recScore != null">rec_score,</if>
            <if test="deviceNo != null">device_no,</if>
            <if test="deviceVersion != null">device_version,</if>
            <if test="source != null">source,</if>
            <if test="type != null">type,</if>
            <if test="cardNo != null">card_no,</if>
            <if test="deviceName != null">device_name,</if>
            <if test="recType != null">rec_type,</if>
            <if test="result != null">result,</if>
            <if test="permissionTimeType != null">permission_time_type,</if>
            <if test="passTimeType != null">pass_time_type,</if>
            <if test="recModeType != null">rec_mode_type,</if>
            <if test="storageId != null">storage_id,</if>
            <if test="timestamp != null">timestamp,</if>
            <if test="admitName != null">admit_name,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="groupId != null">group_id,</if>
            <if test="flag != null">flag,</if>
            <if test="sign != null">sign,</if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="peopleId != null">#{peopleId},</if>
            <if test="equipmentId != null">#{equipmentId},</if>
            <if test="deviceIp != null">#{deviceIp},</if>
            <if test="admitGuid != null">#{admitGuid},</if>
            <if test="recMode != null">#{recMode},</if>
            <if test="filePath != null">#{filePath},</if>
            <if test="showTime != null">#{showTime},</if>
            <if test="showDate != null">#{showDate},</if>
            <if test="aliveType != null">#{aliveType},</if>
            <if test="recScore != null">#{recScore},</if>
            <if test="deviceNo != null">#{deviceNo},</if>
            <if test="deviceVersion != null">#{deviceVersion},</if>
            <if test="source != null">#{source},</if>
            <if test="type != null">#{type},</if>
            <if test="cardNo != null">#{cardNo},</if>
            <if test="deviceName != null">#{deviceName},</if>
            <if test="recType != null">#{recType},</if>
            <if test="result != null">#{result},</if>
            <if test="permissionTimeType != null">#{permissionTimeType},</if>
            <if test="passTimeType != null">#{passTimeType},</if>
            <if test="recModeType != null">#{recModeType},</if>
            <if test="storageId != null">#{storageId},</if>
            <if test="timestamp != null">#{timestamp},</if>
            <if test="admitName != null">#{admitName},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="groupId != null">#{groupId},</if>
            <if test="flag != null">#{flag},</if>
            <if test="sign != null">#{sign},</if>
        </trim>
    </insert>

    <update id="updateSysPeopleRecord" parameterType="SysPeopleRecord">
        update sys_people_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="peopleId != null">people_id = #{peopleId},</if>
            <if test="equipmentId != null">equipment_id = #{equipmentId},</if>
            <if test="deviceIp != null">device_ip = #{deviceIp},</if>
            <if test="admitGuid != null">admit_guid = #{admitGuid},</if>
            <if test="recMode != null">rec_mode = #{recMode},</if>
            <if test="filePath != null">file_path = #{filePath},</if>
            <if test="showTime != null">show_time = #{showTime},</if>
            <if test="showDate != null">show_date = #{showDate},</if>
            <if test="aliveType != null">alive_type = #{aliveType},</if>
            <if test="recScore != null">rec_score = #{recScore},</if>
            <if test="deviceNo != null">device_no = #{deviceNo},</if>
            <if test="deviceVersion != null">device_version = #{deviceVersion},</if>
            <if test="source != null">source = #{source},</if>
            <if test="type != null">type = #{type},</if>
            <if test="cardNo != null">card_no = #{cardNo},</if>
            <if test="deviceName != null">device_name = #{deviceName},</if>
            <if test="recType != null">rec_type = #{recType},</if>
            <if test="result != null">result = #{result},</if>
            <if test="permissionTimeType != null">permission_time_type = #{permissionTimeType},</if>
            <if test="passTimeType != null">pass_time_type = #{passTimeType},</if>
            <if test="recModeType != null">rec_mode_type = #{recModeType},</if>
            <if test="storageId != null">storage_id = #{storageId},</if>
            <if test="timestamp != null">timestamp = #{timestamp},</if>
            <if test="admitName != null">admit_name = #{admitName},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="groupId != null">group_id = #{groupId},</if>
            <if test="flag != null">flag = #{flag},</if>
            <if test="sign != null">sign = #{sign},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSysPeopleRecordById" parameterType="Long">
        delete from sys_people_record where id = #{id}
    </delete>

    <delete id="deleteSysPeopleRecordByIds" parameterType="String">
        delete from sys_people_record where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="topApi"  resultType="java.util.Map">
       select count(1) num from sys_branch
       union all
       select count(1) num from sys_people
       union all
       select count(1) num from sys_equipment
       union all
       select count(1) num from kq_group
    </select>
    <select id="selectPeopleRecordById" resultType="java.lang.String">
        select id from sys_equipment where sequence = #{deviceKey} limit 1
    </select>
    <select id="queryThroughCount" resultType="com.dcsoft.system.domain.SysPeopleRecord">
        select  date.DATE hour,
                sum(if(date_format(now(), '%Y-%m-%d') = date_format(create_time, '%Y-%m-%d'), 1, 0)) today,
                sum(if(date_format(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d') = date_format(create_time, '%Y-%m-%d'), 1, 0)) yesterday

        from (SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL ((@I := @I + 1)) HOUR), '%H:00') AS DATE
              FROM (SELECT A
                    FROM (SELECT 1 AS A UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
                             JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) AS B
                                  ON 1) AS B,
                   (SELECT @I := -1) AS I
              ORDER BY DATE) date
                 left join sys_people_record spr on SUBSTRING(date.DATE, 1, 2) = date_format(create_time, '%H')
        group by date.DATE
        order by date.DATE
    </select>


</mapper>
