<?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.yeejoin.amos.patrol.business.dao.mapper.TaskworkMapper">

    <insert id="save" keyColumn="id" keyProperty="id"
            parameterType="com.yeejoin.amos.patrol.business.entity.mybatis.TaskworkBo"
            useGeneratedKeys="true">
        insert into spc_taskwork
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="type != null">
                type,
            </if>
            <if test="post != null">
                post,
            </if>
            <if test="part != null">
                part,
            </if>
            <if test="regionId != null">
                region_id,
            </if>
            <if test="departmentId != null">
                department_id,
            </if>
            <if test="groupId != null">
                groupId,
            </if>
            <if test="status != null">
                status,
            </if>
            <if test="riskLevelId != null">
                risk_level_id,
            </if>
            <if test="creatorId != null">
                creator_id,
            </if>
            <if test="isMonitor != null">
                is_monitor,
            </if>
            <if test="orgCode != null">
                org_code,
            </if>
            <if test="currentFlowRecordId != null">
                current_flow_record_id,
            </if>
            <if test="instanceId != null">
                instance_id,
            </if>
            <if test="businessKey != null">
                business_key,
            </if>
            <if test="applyUserId != null">
                apply_user_id,
            </if>
            <if test="applyDepartmentId != null">
                apply_department_id,
            </if>
            <if test="applyDate != null">
                apply_date,
            </if>
            <if test="createDate != null">
                create_date,
            </if>
            <if test="updateDate != null">
                update_date,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="type != null">
                #{type},
            </if>
            <if test="post != null">
                #{post},
            </if>
            <if test="part != null">
                #{part},
            </if>
            <if test="regionId != null">
                #{regionId},
            </if>
            <if test="departmentId != null">
                #{departmentId},
            </if>
            <if test="groupId != null">
                #{groupId},
            </if>
            <if test="status != null">
                #{status},
            </if>
            <if test="riskLevelId != null">
                #{riskLevelId},
            </if>
            <if test="creatorId != null">
                #{creatorId},
            </if>
            <if test="isMonitor != null">
                #{isMonitor},
            </if>
            <if test="orgCode != null">
                #{orgCode},
            </if>
            <if test="currentFlowRecordId != null">
                #{currentFlowRecordId},
            </if>
            <if test="instanceId != null">
                #{instanceId},
            </if>
            <if test="businessKey != null">
                #{businessKey},
            </if>
            <if test="applyUserId != null">
                #{applyUserId},
            </if>
            <if test="applyDepartmentId != null">
                #{applyDepartmentId},
            </if>
            <if test="applyDate != null">
                #{applyDate},
            </if>
            <if test="createDate != null">
                #{createDate},
            </if>
            <if test="updateDate != null">
                #{updateDate},
            </if>
        </trim>
    </insert>

    <update id="update" parameterType="com.yeejoin.amos.patrol.business.entity.mybatis.TaskworkBo">
        update spc_taskwork
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="type != null">
                type = #{type},
            </if>
            <if test="post != null">
                post = #{post},
            </if>
            <if test="part != null">
                part = #{part},
            </if>
            <if test="regionId != null">
                region_id = #{regionId},
            </if>
            <if test="departmentId != null">
                department_id = #{departmentId},
            </if>
            <if test="groupId != null">
                group_id = #{groupId},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="riskLevelId != null">
                risk_level_id = #{riskLevelId},
            </if>
            <if test="creatorId != null">
                creator_id = #{creatorId},
            </if>
            <if test="isMonitor != null">
                is_monitor = #{isMonitor},
            </if>
            <if test="orgCode != null">
                org_code = #{orgCode},
            </if>
            <if test="currentFlowRecordId != null">
                current_flow_record_id = #{currentFlowRecordId},
            </if>
            <if test="instanceId != null">
                instance_id = #{instanceId},
            </if>
            <if test="businessKey != null">
                business_key = #{businessKey},
            </if>
            <if test="applyUserId != null">
                apply_user_id = #{applyUserId},
            </if>
            <if test="applyDepartmentId != null">
                apply_department_id = #{applyDepartmentId},
            </if>
            <if test="applyDate != null">
                apply_date = #{applyDate},
            </if>
            <if test="createDate != null">
                create_date = #{createDate},
            </if>
            <if test="updateDate != null">
                update_date = #{updateDate},
            </if>
            <if test="violateState != null">
                violate_state = #{violateState},
            </if>
        </set>
        where id = #{id}
    </update>

    <select id="getById" resultType="com.yeejoin.amos.patrol.business.entity.mybatis.TaskworkBo">
        select
            a.*,
            b.level as levelDesc
        from
            spc_taskwork as a
        left join
            spc_risk_level as b on a.risk_level_id = b.id
        where
            a.id = #{id}
    </select>

    <select id="listByMap" resultType="com.yeejoin.amos.patrol.business.entity.mybatis.TaskworkBo">
        select
            a.*,
            b.level as levelDesc
        from
            spc_taskwork as a
        left join
            spc_risk_level as b on a.risk_level_id = b.id
        left join
            spc_taskwork_audit_log as c on a.current_flow_record_id = c.id
        <where>
            <if test="true">
                and a.status in (2,3,4,5,6,7)
            </if>
            <if test="userId != null and userId != ''">
                and find_in_set(#{userId},c.flow_task_user_ids)
            </if>
            <if test="orgCode != null and orgCode != ''">
                and a.org_code=#{orgCode}
            </if>
            <if test="name != null and name != ''">
                and a.name like concat('%',#{name},'%')
            </if>
            <if test="status != null and status != ''">
                and a.status=#{status}
            </if>
            <if test="code != null and code != ''">
                and b.code=#{code}
            </if>
        </where>
        order by a.create_date desc
        <if test="limit != null and offset != null">
            limit #{offset},#{limit}
        </if>
    </select>

    <select id="countByMap" resultType="java.lang.Long">
        select
            count(1)
        from
            spc_taskwork as a
        left join
            spc_risk_level as b on a.risk_level_id = b.id
        left join
            spc_taskwork_audit_log as c on a.current_flow_record_id = c.id
        <where>
            <if test="true">
                and a.status in (2,3,4,5,6,7)
            </if>
            <if test="userId != null and userId != ''">
                and find_in_set(#{userId},c.flow_task_user_ids)
            </if>
            <if test="orgCode != null and orgCode != ''">
                and a.org_code=#{orgCode}
            </if>
            <if test="name != null and name != ''">
                and a.name like concat('%',#{name},'%')
            </if>
            <if test="status != null and status != ''">
                and a.status=#{status}
            </if>
            <if test="code != null and code != ''">
                and b.code=#{code}
            </if>
        </where>
    </select>

    <select id="countByTypeAndDeptId" resultType="java.lang.Long">
        SELECT
            COUNT( 1 )
        FROM
            spc_taskwork_apply_record a
            LEFT JOIN spc_risk_source b ON a.risk_source_id = b.id
            AND FIND_IN_SET( #{type}, b.taskwork_type )
        WHERE
            (
            a.STATUS IN ( 4, 5, 6 )
            OR ( a.STATUS = 7 AND DATEDIFF( a.update_date, CURDATE( ) ) = 0 )
            )
            AND b.taskwork_type IS NOT NULL
            <if test="departmentId != null and departmentId != -1">
                AND a.taskwork_apply_dept = #{departmentId}
            </if>
    </select>
    <select id="countViolateState" resultType="java.lang.Long">
        select
          count(1)
        from
            spc_taskwork a
        where
            type = #{type}
        and
            department_id = #{departmentId}
        and 
        (a.`status` in (5,6) or (a.`status` = 7 and DATEDIFF(a.update_date,CURDATE()) =0 ))
        and
        violate_state != 1
    </select>
</mapper>