<?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.djhk.uav.equ.mapper.EquInfoMapper">

    <resultMap id="EquInfoEntityResult" type="com.djhk.uav.equ.domain.EquInfoEntity">
        <id property="equId" column="equ_id" jdbcType="INTEGER"/>
        <result property="equCode" column="equ_code" jdbcType="VARCHAR"/>
        <result property="typeId" column="type_id" jdbcType="CHAR"/>
        <result property="modelId" column="model_id" jdbcType="INTEGER"/>
        <result property="equSts" column="equ_sts" jdbcType="CHAR"/>
        <result property="equSn" column="equ_sn" jdbcType="VARCHAR"/>
        <result property="gbCode" column="gb_code" jdbcType="VARCHAR"/>
        <result property="organId" column="organ_id" jdbcType="BIGINT"/>
        <result property="mngName" column="mng_name" jdbcType="VARCHAR"/>
        <result property="mngCode" column="mng_code" jdbcType="VARCHAR"/>
        <result property="purDate" column="pur_date" jdbcType="VARCHAR"/>
        <result property="mtnDate" column="mtn_date" jdbcType="VARCHAR"/>
        <result property="locLon" column="loc_lon" jdbcType="DOUBLE"/>
        <result property="locLat" column="loc_lat" jdbcType="DOUBLE"/>
        <result property="delFlag" column="del_flag" jdbcType="VARCHAR"/>
        <result property="createBy" column="create_by" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="typeName" column="type_name" jdbcType="VARCHAR"/>
        <result property="deptName" column="dept_name" jdbcType="VARCHAR"/>
        <result property="modelName" column="model_name" jdbcType="VARCHAR"/>
        <result property="classify" column="classify" jdbcType="VARCHAR"/>
        <result property="equLoad" column="equ_load" jdbcType="VARCHAR"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
        <result property="insuranceUrl" column="insurance_url" jdbcType="VARCHAR"/>
        <result property="attribution" column="attribution" jdbcType="VARCHAR"/>
        <result property="applySts" column="apply_sts" jdbcType="VARCHAR"/>
        <result property="equFunction" column="equ_function" jdbcType="VARCHAR"/>
        <result property="equType" column="equ_type" jdbcType="VARCHAR"/>
        <result property="rangeRad" column="range_rad" jdbcType="VARCHAR"/>
        <result property="spacialFunction" column="spacial_function" jdbcType="VARCHAR"/>
    </resultMap>


    <resultMap id="MonitorEquInfoEntityResult" type="com.djhk.uav.equ.domain.EquInfoEntity">
        <id property="equId" column="equ_id" jdbcType="INTEGER"/>
        <result property="equCode" column="equ_code" jdbcType="VARCHAR"/>
        <result property="typeId" column="type_id" jdbcType="CHAR"/>
        <result property="modelId" column="model_id" jdbcType="INTEGER"/>
        <result property="equSts" column="equ_sts" jdbcType="CHAR"/>
        <result property="equSn" column="equ_sn" jdbcType="VARCHAR"/>
        <result property="gbCode" column="gb_code" jdbcType="VARCHAR"/>
        <result property="organId" column="organ_id" jdbcType="BIGINT"/>
        <result property="mngName" column="mng_name" jdbcType="VARCHAR"/>
        <result property="mngCode" column="mng_code" jdbcType="VARCHAR"/>
        <result property="purDate" column="pur_date" jdbcType="VARCHAR"/>
        <result property="mtnDate" column="mtn_date" jdbcType="VARCHAR"/>
        <result property="locLon" column="loc_lon" jdbcType="DOUBLE"/>
        <result property="locLat" column="loc_lat" jdbcType="DOUBLE"/>
        <result property="delFlag" column="del_flag" jdbcType="VARCHAR"/>
        <result property="createBy" column="create_by" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
        <result property="typeName" column="type_name" jdbcType="VARCHAR"/>
        <result property="deptName" column="dept_name" jdbcType="VARCHAR"/>
        <result property="modelName" column="model_name" jdbcType="VARCHAR"/>
        <result property="classify" column="classify" jdbcType="VARCHAR"/>
        <result property="equLoad" column="equ_load" jdbcType="VARCHAR"/>
        <result property="remark" column="remark" jdbcType="VARCHAR"/>
        <result property="insuranceUrl" column="insurance_url" jdbcType="VARCHAR"/>
        <result property="attribution" column="attribution" jdbcType="VARCHAR"/>
        <result property="applySts" column="apply_sts" jdbcType="VARCHAR"/>
        <result property="equFunction" column="equ_function" jdbcType="VARCHAR"/>
        <result property="equType" column="equ_type" jdbcType="VARCHAR"/>
        <result property="rangeRad" column="range_rad" jdbcType="VARCHAR"/>
        <result property="spacialFunction" column="spacial_function" jdbcType="VARCHAR"/>
        <collection property="modelParamList" javaType="list" ofType="com.djhk.uav.equ.domain.EquModelParam" select="selectEquModelParams" column="model_id"></collection>
    </resultMap>

    <sql id="selectEquInfoVo">
       select equ_id,equ_code,type_id,gb_code,
        model_id,equ_sts,equ_sn,
        organ_id,mng_name,mng_code,
        pur_date,mtn_date,loc_lon,
        loc_lat,create_by,create_time,
        update_by,update_time,classify,equ_load,remark,insurance_url,attribution,
        equ_function,equ_type,range_rad,spacial_function
        from equ_info equ
    </sql>

    <sql id="selectEquInfoAndOthers">
       select equ.equ_id,equ.equ_code,equ.type_id,equ.gb_code,
        equ.model_id,equ.equ_sts,equ.equ_sn,
        equ.organ_id,equ.mng_name,equ.mng_code,
        equ.pur_date,equ.mtn_date,equ.loc_lon,
        equ.loc_lat,equ.create_by,equ.create_time,equ.classify,equ.equ_load,equ.remark,equ.insurance_url,
        equ.remark,equ.attribution,equ.equ_function,equ.equ_type,equ.range_rad,equ.spacial_function,
        equ.update_by,equ.update_time,dept.dept_name,type.type_name
        from equ_info equ left join sys_dept dept on equ.organ_id = dept.dept_id
				left join equ_type type on equ.type_id=type.type_id
    </sql>

    <select id="selectEquModelParams" resultType="com.djhk.uav.equ.domain.EquModelParam" parameterType="Integer">
        select param_code,param_value
        from equ_model_param
        where model_id= #{model_id}
    </select>

    <select id="selectEquInfoList" parameterType="com.djhk.uav.equ.domain.EquInfoEntity"
            resultMap="EquInfoEntityResult">
        <include refid="selectEquInfoAndOthers"/>
        <where>
            and equ.del_flag = '0'
            <if test="modelId != null and modelId != ''">
                AND equ.model_id like concat('%' , #{modelId}, '%')
            </if>
            <if test="equCode != null and equCode != ''">
                AND equ.equ_code like concat('%' , #{equCode}, '%')
            </if>
            <if test="typeId != null and typeId != ''">
                AND equ.type_id = #{typeId}
            </if>
            <if test="modelId != null and modelId != ''">
                AND equ.model_id like concat('%' , #{modelId}, '%')
            </if>
            <if test="equSn != null and equSn != ''">
                AND equ.equ_sn like concat('%', #{equSn}, '%')
            </if>
            <if test="mngName != null and mngName != ''">
                AND equ.mng_name like concat('%', #{mngName}, '%')
            </if>
            <if test="organId != null and organId != 0">
                AND (equ.organ_id = #{organId} OR equ.organ_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
                find_in_set(#{organId}, ancestors) ))
            </if>
        </where>
        order by equ_code desc
    </select>

    <select id="selectEquInfoById" parameterType="String" resultMap="EquInfoEntityResult">
        <include refid="selectEquInfoVo"/>
        where equ_id = #{equId}
    </select>

    <select id="selectEquInfoByEquSn" parameterType="com.djhk.uav.equ.domain.EquInfoEntity" resultMap="EquInfoEntityResult">
        <include refid="selectEquInfoVo"/>
        where equ_sn = #{equSn} and organ_id = #{organId}
    </select>


    <delete id="deleteEquInfoById" parameterType="String">
 		delete from equ_info where equ_id = #{equId}
 	</delete>


    <delete id="deleteEquInfoByIds" parameterType="String">
        delete from equ_info where equ_id in
        <foreach collection="equIds" item="equIds" open="(" separator="," close=")">
            #{equIds}
        </foreach>
    </delete>
    <update id="updateEquInfoByIds" parameterType="String">
        update equ_info set del_flag = '2' where equ_id in
        <foreach collection="equIds" item="equIds" open="(" separator="," close=")">
            #{equIds}
        </foreach>
    </update>

    <insert id="insertEquInfo" parameterType="com.djhk.uav.equ.domain.EquInfoEntity">
        insert into equ_info(
        <if test="equCode != null">equ_code,</if>
        <if test="typeId != null and typeId != ''">type_id,</if>
        <if test="modelId != null and modelId != ''">model_id,</if>
        <if test="equSts != null and equSts != ''">equ_sts,</if>
        <if test="equSn != null and equSn != ''">equ_sn,</if>
        <if test="organId != null and organId != ''">organ_id,</if>
        <if test="gbCode != null and gbCode != ''">gb_code,</if>
        <if test="mngName != null and mngName != ''">mng_name,</if>
        <if test="mngCode != null and mngCode != ''">mng_code,</if>
        <if test="purDate != null and purDate != ''">pur_date,</if>
        <if test="mtnDate != null and mtnDate != ''">mtn_date,</if>
        <if test="locLon != null and locLon != ''">loc_lon,</if>
        <if test="locLat != null and locLat != ''">loc_lat,</if>
        <if test="updateBy != null and updateBy != ''">update_by,</if>
        <if test="updateTime != null and updateTime != ''">update_time,</if>
        <if test="createBy != null and createBy != ''">create_by,</if>
        <if test="classify != null and createBy != ''">classify,</if>
        <if test="equLoad != null and equLoad != ''">equ_load,</if>
        <if test="insuranceUrl != null and insuranceUrl != ''">insurance_url,</if>
        <if test="remark != null and remark != ''">remark,</if>
        <if test="attribution != null and attribution != ''">attribution,</if>
        <if test="equFunction != null and equFunction != ''">equ_function,</if>
        <if test="equType != null and equType != ''">equ_type,</if>
        <if test="rangeRad != null and rangeRad != ''">range_rad,</if>
        <if test="spacialFunction != null and spacialFunction != ''">spacial_function,</if>
        create_time
        )values(
        <if test="equCode != null">#{equCode},</if>
        <if test="typeId != null and typeId != ''">#{typeId},</if>
        <if test="modelId != null and modelId != ''">#{modelId},</if>
        <if test="equSts != null and equSts != ''">#{equSts},</if>
        <if test="equSn != null and equSn != ''">#{equSn},</if>
        <if test="organId != null and organId != ''">#{organId},</if>
        <if test="gbCode != null and gbCode != ''">#{gbCode},</if>
        <if test="mngName != null and mngName != ''">#{mngName},</if>
        <if test="mngCode != null and mngCode != ''">#{mngCode},</if>
        <if test="purDate != null and purDate != ''">#{purDate},</if>
        <if test="mtnDate != null and mtnDate != ''">#{mtnDate},</if>
        <if test="locLon != null and locLon != ''">#{locLon},</if>
        <if test="locLat != null and locLat != ''">#{locLat},</if>
        <if test="updateBy != null and updateBy != ''">#{updateBy},</if>
        <if test="updateTime != null and updateTime != ''">#{updateTime},</if>
        <if test="createBy != null and createBy != ''">#{createBy},</if>
        <if test="classify != null and classify != ''">#{classify},</if>
        <if test="equLoad != null and equLoad != ''">#{equLoad},</if>
        <if test="insuranceUrl != null and insuranceUrl != ''">#{insuranceUrl},</if>
        <if test="remark != null and remark != ''">#{remark},</if>
        <if test="attribution != null and attribution != ''">#{attribution},</if>
        <if test="equFunction != null and equFunction != ''">#{equFunction},</if>
        <if test="equType != null and equType != ''">#{equType},</if>
        <if test="rangeRad != null and rangeRad != ''">#{rangeRad},</if>
        <if test="spacialFunction != null and spacialFunction != ''">#{spacialFunction},</if>
        DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s')
        )
    </insert>

    <update id="updateEquInfo" parameterType="com.djhk.uav.equ.domain.EquInfoEntity">
        update equ_info
        <set>
            <if test="equCode != null">equ_code = #{equCode},</if>
            <if test="typeId != null and typeId != ''">type_id = #{typeId},</if>
            <if test="modelId != null and modelId != ''">model_id = #{modelId},</if>
            <if test="equSts != null and equSts != ''">equ_sts = #{equSts},</if>
            <if test="equSn != null and equSn != ''">equ_sn = #{equSn},</if>
            <if test="gbCode != null and gbCode != ''">gb_code = #{gbCode},</if>
            <if test="organId != null and organId != ''">organ_id = #{organId},</if>
            <if test="mngName != null and mngName != ''">mng_name = #{mngName},</if>
            <if test="mngCode != null and mngCode != ''">mng_code = #{mngCode},</if>
            <if test="purDate != null and purDate != ''">pur_date = #{purDate},</if>
            <if test="mtnDate != null and mtnDate != ''">mtn_date = #{mtnDate},</if>
            <if test="locLon != null and locLon != ''">loc_lon = #{locLon},</if>
            <if test="locLat != null and locLat != ''">loc_lat = #{locLat},</if>
            <if test="delFlag != null and delFlag != ''">loc_lat = #{locLat},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="classify != null and classify != ''">classify = #{classify},</if>
            <if test="equLoad != null and equLoad != ''">equ_load = #{equLoad},</if>
            <if test="insuranceUrl != null and insuranceUrl != ''">insurance_url = #{insuranceUrl},</if>
            <if test="remark != null and remark != ''">remark = #{remark},</if>
            <if test="attribution != null and attribution != ''">attribution = #{attribution},</if>
            <if test="equFunction != null and equFunction != ''">equ_function = #{equFunction},</if>
            <if test="equType != null and equType != ''">equ_type = #{equType},</if>
            <if test="rangeRad != null and rangeRad != ''">range_rad = #{rangeRad},</if>
            <if test="spacialFunction != null and spacialFunction != ''">spacial_function = #{spacialFunction},</if>
            update_time = DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:%s')
        </set>
        where equ_id = #{equId}
    </update>

    <select id="getEquTj" resultType="java.util.Map">
        select c.typeId as type,c.modelId as model,c.count,t.type_name as typeName,m.dict_label as modelName from (
        SELECT type_id as typeId, model_id as modelId, COUNT(*) AS count FROM equ_info equ
        <where>
            <if test="organId != null and organId != 0">
                AND (equ.organ_id = #{organId} OR equ.organ_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
                find_in_set(#{organId}, ancestors) ))
            </if>
        </where>
        GROUP BY type_id, model_id ) c
        left join equ_type t on c.typeId = t.type_id left join sys_dict_data m on c.modelId = m.dict_value
        WHERE m.dict_type= 'equ_model_info'

    </select>

    <select id="getEquTjForDept" resultType="java.util.Map">
        select c.organId as deptId ,dept.dept_name as lable ,c.count as value from (
        SELECT organ_id as organId, COUNT(*) AS count FROM equ_info equ
        where 1=1
        <if test="organId != null and organId != 0">
            AND (equ.organ_id = #{organId} OR equ.organ_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
            find_in_set(#{organId}, ancestors) ))
        </if>
        GROUP BY organ_id) c
        left join sys_dept dept on c.organId = dept.dept_id
    </select>
    <select id="selectMonitorEquList" resultMap="MonitorEquInfoEntityResult">
        select equ.equ_id,equ.equ_code,equ.type_id,equ.gb_code,
               equ.model_id,equ.equ_sts,equ.equ_sn,
               equ.organ_id,equ.mng_name,equ.mng_code,
               equ.pur_date,equ.mtn_date,equ.loc_lon,
               equ.loc_lat,equ.create_by,equ.create_time,
               equ.update_by,equ.update_time,model.model_name
        from equ_info equ left join equ_model model on equ.model_id = model.model_id
        where
        equ.equ_sts='0'
        AND  equ.del_flag='0'
        AND  equ.type_id IN
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
    <select id="selectEquInfoTotal" resultType="java.lang.Integer">
        select count(equ.equ_id)
        from equ_info equ left join sys_dept dept on equ.organ_id = dept.dept_id
                          left join equ_model model on equ.model_id = model.model_id
                          left join equ_type type on equ.type_id=type.type_id
        where
        equ.type_id=10
        AND  equ.del_flag='0'
        AND (equ.organ_id = #{deptId} OR equ.organ_id IN ( SELECT t.dept_id FROM sys_dept t WHERE
            find_in_set(#{deptId}, ancestors) ))
    </select>
    <select id="selectEquInfoExportList" resultType="com.djhk.uav.equ.domain.dto.EquInfoExportDto">
        <include refid="selectEquInfoVo"/>
        <where>
            and equ.del_flag = "0"
            <if test="ids != null and ids.size() > 0">
                and id in
                <foreach item="id" collection="ids" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
            <if test="createBy != null and createBy != '' ">
                AND equ.create_by like concat('%',#{createBy},'%')
            </if>
            <if test="typeId != null and typeId != '' ">
                AND equ.type_id =  #{typeId}
            </if>
            <if test="deptId != null and deptId != '' ">
                AND equ.organ_id in (select dept_id  from sys_dept WHERE  dept_id = #{deptId}  OR FIND_IN_SET(#{deptId}, ancestors))
            </if>
            <!--<if test="orgIds != null and orgIds.size() > 0">
                and organ_id in
                <foreach collection="orgIds" item="item" open="(" separator="," close=")">
                    #{item}
                    /*select * from sys_dept d WHERE  dept_id = 53569  OR FIND_IN_SET(53569, ancestors)*/
                </foreach>
            </if>-->
        </where>
        order by equ.create_Time desc
    </select>
    <select id="selectEquInfoByTypeId" resultType="com.djhk.uav.equ.domain.EquInfoEntity">
        select equ.equ_id,equ.equ_code,equ.type_id,equ.gb_code,
               equ.model_id,equ.equ_sts,equ.equ_sn,
               equ.organ_id,equ.mng_name,equ.mng_code,
               equ.pur_date,equ.mtn_date,equ.loc_lon,
               equ.loc_lat,equ.create_by,equ.create_time,
               equ.update_by,equ.update_time
        from equ_info equ
        where
            equ.del_flag='0'
          AND  equ.type_id = #{typeId}
          AND  equ.organ_id = #{deptId}
        ORDER BY equ.equ_id  DESC
            LIMIT 1

    </select>
</mapper>
