<?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.butlerApp.dao.jurisdiction.ButlerInspectionDao">
    <select id="list" parameterType="com.api.model.butlerApp.ButlerInspectionSearch" resultType="com.api.vo.butlerApp.ButlerInspectionVo">
        select sie.id,concat(sip.code,'-',sie.sort) as code,sip.name,sie.begin_date,sie.actual_begin_date,sie.actual_end_date,su.actual_name as inspector_name
        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
            and sip.inspector = #{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>
    </select>

    <select id="findDetailById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerInspectionFDBIVo">
        select sie.id,sie.inspection_plan_id,concat(sip.code,'-',sie.sort) as code,sip.name,sie.begin_date,sie.end_date,sie.actual_begin_date,sie.actual_end_date,sie.sort
        from sys_inspection_execute as sie,sys_inspection_plan as sip
        where sie.inspection_plan_id = sip.id and sie.id = #{executeId}
    </select>

    <select id="findPointByPlanId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerPointVo">
        select sipo.id,sipo.name,sipo.longitude,sipo.latitude
        from sys_inspection_plan as sipl,sys_inspection_route as sir,sys_inspection_point_route as sipr,sys_inspection_point as sipo
        where sipl.inspection_route_id = sir.id and sipr.inspection_point_id = sipo.id and sipr.inspection_route_id = sir.id and sipo.is_delete = 1 and sipl.id = #{planId}
    </select>

    <select id="countCheckNumByPointId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_inspection_check_items as sici where inspection_point_id = #{id}
    </select>

    <select id="findPointByExecuteId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerPointVo">
        select id,name,complete_date,longitude,latitude from sys_inspection_execute_point where execute_id = #{executeId}
    </select>

    <select id="countCheckNumByPointId2" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_inspection_execute_check_items where execute_point_id = #{id}
    </select>

    <select id="findPlanInspectionPoint" parameterType="java.lang.Integer" resultType="com.api.model.butlerApp.ButlerExecutePoint">
        select sipo.id,sipo.code,sipo.name,sipo.type,sipo.longitude,sipo.latitude
        from sys_inspection_execute as sie,sys_inspection_plan as sipl,sys_inspection_route as sir,sys_inspection_point_route as sipr,sys_inspection_point as sipo
        where sie.inspection_plan_id = sipl.id and sipl.inspection_route_id = sir.id and sipr.inspection_point_id = sipo.id and sipr.inspection_route_id = sir.id and sir.is_delete = 1 and sipo.is_delete = 1 and sie.id = #{executeId}
    </select>

    <insert id="insertExecutePoint" parameterType="com.api.model.butlerApp.ButlerExecutePoint">
        insert into sys_inspection_execute_point(
            execute_id,
            code,
            name,
            <if test="longitude != null">
                longitude,
            </if>
            <if test="latitude != null">
                latitude,
            </if>
            type
        )values (
            #{executeId},
            #{code},
            #{name},
            <if test="longitude != null">
                #{longitude},
            </if>
            <if test="latitude != null">
                #{latitude},
            </if>
            #{type}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <select id="findPlanPointCheck" parameterType="java.lang.Integer" resultType="com.api.model.butlerApp.ButlerExecuteCheck">
        select name from sys_inspection_check_items where inspection_point_id = #{id}
    </select>

    <insert id="insertExecuteCheck" parameterType="com.api.model.butlerApp.ButlerExecuteCheck">
        insert into sys_inspection_execute_check_items(
            execute_point_id,
            name
        )values (
            #{executePointId},
            #{name}
        )
    </insert>
    <update id="updateActualBeginDateById" parameterType="com.api.model.butlerApp.ButlerExecuteIdAndBeginDate">
        update sys_inspection_execute set actual_begin_date = #{actualBeginDate} where id = #{executeId}
    </update>

    <select id="findPointCodeByExecuteId" parameterType="java.lang.Integer" resultType="java.lang.String">
        select code from sys_inspection_execute_point where execute_id = #{executeId}
    </select>

    <select id="findExecutePointByCode" parameterType="com.api.model.butlerApp.ButlerExecuteIdAndExecutePointCode" resultType="com.api.vo.butlerApp.ButlerExecutePointVo">
        select * from sys_inspection_execute_point where execute_id = #{executeId}  and code = #{executePointCode}
    </select>

    <select id="findExecuteCheckByPointId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecuteCheckVo">
        select * from sys_inspection_execute_check_items where execute_point_id = #{id}
    </select>
    <update id="updateExecuteCheck" parameterType="com.api.model.butlerApp.ButlerExecuteCheck">
        update sys_inspection_execute_check_items
        <set>
            status = #{status},
            <if test="remakes != null and remakes != ''">
                remakes = #{remakes},
            </if>
        </set>
        where id = #{id}
    </update>

    <update id="updateExecutePoint" parameterType="com.api.model.butlerApp.ButlerPointIdAndCompleteDate">
        update sys_inspection_execute_point
        set complete_date = #{completeDate}
        where id = #{pointId}
    </update>

    <select id="findCompleteDateById" parameterType="java.lang.Integer" resultType="com.api.model.butlerApp.ButlerExecutePoint">
        select * from sys_inspection_execute_point where id = #{executePointId}
    </select>

    <select id="countExecutePoint" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_inspection_execute_point where execute_id = #{executeId} and complete_date is null
    </select>

    <update id="updateExecute" parameterType="com.api.model.butlerApp.ButlerExecuteIdAndActualEndDate">
        update sys_inspection_execute
        set actual_end_date = #{actualEndDate}
        where id = #{executeId}
    </update>

    <select id="findExecutePointById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecutePointFBIVo">
        select * from sys_inspection_execute_point where id = #{executePointId}
    </select>

    <select id="findExecutePointById2" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecutePointFBIVo">
        select * from sys_inspection_point where id = #{planPointId}
    </select>

    <select id="findExecuteCheckByPointId2" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecuteCheckFBIVo">
        select * from sys_inspection_execute_check_items where execute_point_id = #{executePointId}
    </select>

    <select id="findExecuteCheckByPointId3" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecuteCheckFBIVo">
        select * from sys_inspection_check_items where inspection_point_id = #{planPointId}
    </select>

    <update id="updatePlanByPlanId" parameterType="com.api.model.butlerApp.ButlerPlanIdAndActualBeginDate">
        update sys_inspection_plan
        set actual_begin_date = #{actualBeginDate}
        where id = #{planId}
    </update>

    <select id="findExecuteByExecuteId" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysInspectionExecute">
        select * from sys_inspection_execute where id =#{executeId}
    </select>

    <select id="findPlanById" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysInspectionPlan">
        select * from sys_inspection_plan where id = #{inspectionPlanId}
    </select>

    <select id="findSpaceTimeById" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select space_time from sys_inspection_route where id = #{inspectionRouteId}
    </select>

    <select id="countExecuteNumByPlanId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_inspection_execute where inspection_plan_id = #{inspectionPlanId}
    </select>

    <select id="findOldExecuteByToday" parameterType="java.util.Date" resultType="com.api.model.butlerService.SysInspectionExecute">
        select * from sys_inspection_execute
        where DATE_FORMAT(begin_date,'%Y-%m-%d') <![CDATA[ < ]]> DATE_FORMAT(#{date},'%Y-%m-%d') and actual_end_date is null
    </select>

    <insert id="insertExecuteMap" parameterType="com.api.model.butlerApp.ButlerExecuteMap">
        insert into sys_inspection_execute_map(
            execute_id,
            longitude,
            latitude,
            create_id,
            create_date
        )values (
            #{executeId},
            #{longitude},
            #{latitude},
            #{createId},
            #{createDate}
        )
    </insert>

    <select id="getLocation" parameterType="java.lang.Integer" resultType="com.api.vo.butlerApp.ButlerExecuteMapVo">
        select id,longitude,latitude,create_date from sys_inspection_execute_map where execute_id =#{executeId} order by create_date
    </select>


</mapper>