<?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.api.manage.dao.butlerService.SysInspectionPlanDao">
    <select id="list" parameterType="com.api.model.butlerService.SearchInspectionPlan" resultType="com.api.vo.butlerService.VoInspectionPlan">
        select sip.id,sip.code,sir.code as route_code,sip.name,sip.plan_begin_date,sip.actual_begin_date,so.name as organization_name,su.actual_name as inspector_name,sip.status
        from sys_inspection_plan as sip,sys_inspection_route as sir,sys_user as su,sys_organization as so
        <where>
            sip.inspection_route_id = sir.id
            and sip.inspector = su.id
            and sip.organization_id = so.id
            and sip.is_delete = 1
            <if test="code != null and code != ''">
                and sip.code = #{code}
            </if>
            <if test="name != null and name != ''">
                and sip.name like concat('%',#{name},'%')
            </if>
            <if test="status != null">
                and sip.status = #{status}
            </if>
            <if test="inspectionRouteId != null">
                and sip.inspection_route_id = #{inspectionRouteId}
            </if>
            <if test="planBeginDateStart != null">
                and sip.plan_begin_date <![CDATA[ >= ]]> #{planBeginDateStart}
            </if>
            <if test="planBeginDateEnd != null">
                and sip.plan_begin_date <![CDATA[ <= ]]> #{planBeginDateEnd}
            </if>
            <if test="actualBeginDateStart != null">
                and sip.actual_begin_date <![CDATA[ >= ]]> #{actualBeginDateStart}
            </if>
            <if test="actualBeginDateEnd != null">
                and sip.actual_begin_date <![CDATA[ <= ]]> #{actualBeginDateEnd}
            </if>
        </where>
    </select>

    <insert id="insert" parameterType="com.api.model.butlerService.SysInspectionPlan">
        insert into sys_inspection_plan(
            inspection_route_id,
            code,
            name,
            status,
            organization_id,
            inspector,
            plan_begin_date,
            is_sort,
            check_rate_type,
            create_id,
            create_date,
            is_delete
        )values (
            #{inspectionRouteId},
            #{code},
            #{name},
            #{status},
            #{organizationId},
            #{inspector},
            #{planBeginDate},
            #{isSort},
            #{checkRateType},
            #{createId},
            #{createDate},
            #{isDelete}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <insert id="insertExecute" parameterType="com.api.model.butlerService.SysInspectionExecute">
        insert into sys_inspection_execute(
            inspection_plan_id,
            begin_date,
            end_date,
            sort
        )values (
            #{inspectionPlanId},
            #{beginDate},
            #{endDate},
            #{sort}
        )
    </insert>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFBIInspectionPlan">
        select sip.id,sir.name as route_name,sir.code as route_code,sip.inspection_route_id as route_id,sip.name,so.name as organization_name,sip.organization_id,
        su.actual_name as inspector_name,sip.inspector as inspector_id,sip.plan_begin_date,sip.is_sort,sip.check_rate_type
        from sys_inspection_plan as sip,sys_inspection_route as sir,sys_organization as so,sys_user as su
        where sip.inspection_route_id = sir.id and sip.organization_id = so.id and sip.inspector = su.id and sip.id = #{id} and sip.is_delete = 1
    </select>

    <update id="falseDelete" parameterType="java.lang.Integer">
        update sys_inspection_plan set is_delete = 0 where id = #{id}
    </update>

    <select id="findStatusById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select status from sys_inspection_plan where id = #{id} and is_delete = 1
    </select>

    <update id="updateStatus" parameterType="com.api.model.butlerService.PlanIdAndStatus">
        update sys_inspection_plan set status = #{status} where id = #{planId}
    </update>

    <select id="findNewPlan" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysInspectionExecute">
        select * from sys_inspection_execute where inspection_plan_id = #{id} order by sort desc limit 1
    </select>
    <select id="executeList" parameterType="com.api.model.butlerService.SearchInspectionExecute" resultType="com.api.vo.butlerService.VoInspectionExecute">
        select T.* from (
            select sie.id,concat(sip.code,'-',sie.sort) as code,sip.name,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date,su.actual_name as inspector_name,su.tel as inspector_tel
            from sys_inspection_execute as sie,sys_inspection_plan as sip,sys_user as su
            <where>
                sie.inspection_plan_id = sip.id
                and sip.inspector = su.id
                <if test="inspectionStatus == 1">
                    and sie.actual_begin_date is null
                    and sie.actual_end_date is null
                </if>
                <if test="inspectionStatus == 2">
                    and sie.actual_begin_date is not null
                    and sie.actual_end_date is not null
                </if>
                <if test="inspectionStatus == 3">
                    and sie.actual_begin_date is not null
                    and sie.actual_end_date is null
                </if>
                <if test="inspectionStatus == 4">
                    and sie.actual_begin_date is null
                    and sie.actual_end_date is not null
                </if>
            </where>
            order by sie.begin_date desc
        ) as T
        <where>
            <if test="code != null and code != ''">
                and T.code like concat('%',#{code},'%')
            </if>
            <if test="inspectorTel != null and inspectorTel != ''">
                and T.inspector_tel like concat('%',#{inspectorTel},'%')
            </if>
        </where>
    </select>
</mapper>