<?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.bonus.sgzb.base.mapper.MaTypeMapper">
    <resultMap type="com.bonus.sgzb.base.api.domain.MaType" id="MaTypeResult">
        <result property="typeId"    column="type_id"    />
        <result property="typeName"    column="type_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="status"    column="status"    />
        <result property="num"    column="num"    />
        <result property="companyId"    column="company_id"    />
        <result property="code"    column="code"    />
        <result property="unitId"    column="unit_id"    />
        <result property="unitName"    column="unit_name"    />
        <result property="manageType"    column="manage_type"    />
        <result property="leasePrice"    column="lease_price"    />
        <result property="effTime"    column="eff_time"    />
        <result property="rentPrice"    column="rent_price"    />
        <result property="buyPrice"    column="buy_price"    />
        <result property="payPrice"    column="pay_price"    />
        <result property="level"    column="level"    />
        <result property="ratedLoad"    column="rated_load"    />
        <result property="testLoad"    column="test_load"    />
        <result property="holdingTime"    column="holding_time"    />
        <result property="warnNum"    column="warn_num"    />
        <result property="delFlag"    column="del_flag"    />
        <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="isPlan"    column="is_plan"    />
        <result property="isAncuo"    column="is_ancuo"    />
        <result property="remark"    column="remark"    />
        <result property="childPhoto"    column="childPhoto"    />
    </resultMap>

    <sql id="selectMaTypeVo">
        select type_id, type_name, parent_id, status, num, company_id, code, unit_id, unit_name, manage_type, lease_price, eff_time, rent_price, buy_price, pay_price, level, rated_load, test_load, holding_time, warn_num, del_flag, create_by, create_time, update_by, update_time, is_plan, is_ancuo, remark from ma_type
    </sql>

    <insert id="insertType" parameterType="com.bonus.sgzb.base.api.domain.MaType" useGeneratedKeys="true" keyProperty="typeId">
        insert into ma_type
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="typeName != null and typeName != ''">type_name,</if>
            <if test="parentId != null">parent_id,</if>
            <if test="status != null">status,</if>
            <if test="num != null">num,</if>
            <if test="companyId != null">company_id,</if>
            <if test="code != null">code,</if>
            <if test="unitId != null">unit_id,</if>
            <if test="unitName != null">unit_name,</if>
            <if test="manageType != null">manage_type,</if>
            <if test="leasePrice != null">lease_price,</if>
            <if test="effTime != null">eff_time,</if>
            <if test="rentPrice != null">rent_price,</if>
            <if test="buyPrice != null">buy_price,</if>
            <if test="payPrice != null">pay_price,</if>
            <if test="level != null">level,</if>
            <if test="ratedLoad != null">rated_load,</if>
            <if test="testLoad != null">test_load,</if>
            <if test="holdingTime != null">holding_time,</if>
            <if test="warnNum != null">warn_num,</if>
            <if test="delFlag != null">del_flag,</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="isPlan != null">is_plan,</if>
            <if test="isAncuo != null">is_ancuo,</if>
            <if test="remark != null">remark,</if>
            <if test="facModel != null">fac_model,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeName != null and typeName != ''">#{typeName},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="status != null">#{status},</if>
            <if test="num != null">#{num},</if>
            <if test="companyId != null">#{companyId},</if>
            <if test="code != null">#{code},</if>
            <if test="unitId != null">#{unitId},</if>
            <if test="unitName != null">#{unitName},</if>
            <if test="manageType != null">#{manageType},</if>
            <if test="leasePrice != null">#{leasePrice},</if>
            <if test="effTime != null">#{effTime},</if>
            <if test="rentPrice != null">#{rentPrice},</if>
            <if test="buyPrice != null">#{buyPrice},</if>
            <if test="payPrice != null">#{payPrice},</if>
            <if test="level != null">#{level},</if>
            <if test="ratedLoad != null">#{ratedLoad},</if>
            <if test="testLoad != null">#{testLoad},</if>
            <if test="holdingTime != null">#{holdingTime},</if>
            <if test="warnNum != null">#{warnNum},</if>
            <if test="delFlag != null">#{delFlag},</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="isPlan != null">#{isPlan},</if>
            <if test="isAncuo != null">#{isAncuo},</if>
            <if test="remark != null">#{remark},</if>
            <if test="facModel != null">#{facModel},</if>
        </trim>
    </insert>

    <insert id="insertTypeFile" parameterType="com.bonus.sgzb.base.domain.MaTypeFile" useGeneratedKeys="true" keyProperty="typeId">
        insert into ma_type_file(
        <if test="typeId != null and typeId != 0">type_id,</if>
        <if test="maId != null and maId != ''">ma_id,</if>
        <if test="fileName != null and fileName != 0">file_name,</if>
        <if test="fileUrl != null and fileUrl != ''">file_url,</if>
        <if test="fileType != null and fileType != 0">file_type,</if>
        <if test="userId != null and userId != 0">user_id,</if>
        <if test="status != null and status != 0">status,</if>
        <if test="companyId != null and companyId != ''">company_id,</if>
        time
        )values(
        <if test="typeId != null and typeId != 0">#{typeId},</if>
        <if test="maId != null and maId != ''">#{maId},</if>
        <if test="fileName != null and fileName != 0">#{fileName},</if>
        <if test="fileUrl != null and fileUrl != ''">#{fileUrl},</if>
        <if test="fileType != null and fileType != ''">#{fileType},</if>
        <if test="userId != null and userId != 0">#{userId},</if>
        <if test="status != null and status != ''">#{status},</if>
        <if test="companyId != null and companyId != ''">#{companyId},</if>
        sysdate()
        )
    </insert>

    <update id="updateType">
        update ma_type
        <trim prefix="SET" suffixOverrides=",">
            <if test="typeName != null and typeName != ''">type_name = #{typeName},</if>
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="status != null">status = #{status},</if>
            <if test="num != null">num = #{num},</if>
            <if test="unitId != null">unit_id = #{unitId},</if>
            <if test="unitName != null">unit_name = #{unitName},</if>
            <if test="manageType != null">manage_type = #{manageType},</if>
            <if test="leasePrice != null">lease_price = #{leasePrice},</if>
            <if test="rentPrice != null">rent_price = #{rentPrice},</if>
            <if test="effTime != null">eff_time = #{effTime},</if>
            <if test="buyPrice != null">buy_price = #{buyPrice},</if>
            <if test="payPrice != null">pay_price = #{payPrice},</if>
            <if test="level != null">level = #{level},</if>
            <if test="ratedLoad != null">rated_load = #{ratedLoad},</if>
            <if test="testLoad != null">test_load = #{testLoad},</if>
            <if test="holdingTime != null">holding_time = #{holdingTime},</if>
            <if test="warnNum != null">warn_num = #{warnNum},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</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="isPlan != null">is_plan = #{isPlan},</if>
            <if test="isAncuo != null">is_ancuo = #{isAncuo},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="companyId != null">company_id = #{companyId},</if>
            <if test="facModel != null">fac_model = #{facModel},</if>
        </trim>
        where type_id = #{typeId}
    </update>

    <delete id="deleteTypeById" parameterType="Long">
        update ma_type set del_flag = '2' where type_id = #{typeId}
    </delete>
    <delete id="deleteTypeByTypeId">
        delete from ma_type_repair where type_id = #{typeId}
    </delete>
    <delete id="deleteKeeperByTypeId">
        delete from ma_type_keeper where type_id = #{typeId}
    </delete>
    <delete id="deletePropSetByTypeId">
        delete from ma_prop_set where type_id = #{typeId}
    </delete>

    <select id="selectMaTypeList" parameterType="String" resultMap="MaTypeResult">
        select DISTINCT m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id, m.unit_name, m.manage_type,
               m.lease_price,m.eff_time, m.rent_price, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
               m.holding_time, m.warn_num, mtf.file_name photoName, mtf.file_url photoUrl,
               mtf2.file_name documentName, mtf2.file_url documentUrl, mtk.user_id keeperUserId,
               su.nick_name keeperUserName, mpi.prop_name, m.del_flag, m.create_by, m.create_time,
               m.remark, m.company_id ,m.type_id id , m.type_name label
        from ma_type m
        left join ma_prop_set mps on m.type_id = mps.type_id and mps.`status`='0' and mps.del_flag='0'
        left join ma_prop_info mpi on mps.prop_id = mpi.prop_id and mpi.`status`='0' and mpi.del_flag='0'
        left join (select * from ma_type_file where file_type = '1') mtf on m.type_id = mtf.type_id
        left join (select * from ma_type_file where file_type = '2') mtf2 on m.type_id = mtf2.type_id
        left join ma_type_keeper mtk on mtf.type_id = mtk.type_id
        left join sys_user su on mtk.user_id = su.user_id
        <where>
            m.status = '0'
            and m.del_flag = '0'
            <if test="typeName != null and typeName !=''">
                AND type_name like concat('%',#{typeName},'%')
            </if>
        </where>
    </select>

    <select id="selectTypeList" resultType="com.bonus.sgzb.base.api.domain.MaType">
        <include refid="selectMaTypeVo"/>
        <where>
            <if test="typeName != null and typeName != ''">
                AND type_name like concat('%', #{typeName}, '%')
            </if>
        </where>
    </select>

    <select id="selectTypeById" resultType="java.lang.Integer">
        select count(1) from ma_type
        where del_flag = '0' and parent_id = #{typeId} limit 1
    </select>

    <select id="checkTypeNameUnique" resultType="com.bonus.sgzb.base.api.domain.MaType">
        <include refid="selectMaTypeVo"/>
        where type_name = #{typeName} limit 1
    </select>

    <insert id="insertKeeper">
            insert into ma_type_keeper
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="typeId != null">type_id,</if>
                <if test="userId != null">user_id,</if>
                <if test="time != null">time,</if>
                <if test="companyId != null">company_id,</if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="typeId != null">#{typeId},</if>
                <if test="userId != null">#{userId},</if>
                <if test="time != null">#{time},</if>
                <if test="companyId != null">#{companyId},</if>
            </trim>
    </insert>

    <insert id="insertRepair">
        insert into ma_type_repair
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="typeId != null">type_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="time != null">time,</if>
            <if test="companyId != null">company_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeId != null">#{typeId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="time != null">#{time},</if>
            <if test="companyId != null">#{companyId},</if>
        </trim>
    </insert>

    <insert id="insertMaPropSet">
        insert into ma_prop_set
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="propId != null">prop_id,</if>
            <if test="typeId != null">type_id,</if>
            <if test="status != null">status,</if>
            <if test="delFlag != null">del_flag,</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="remark != null">remark,</if>
            <if test="companyId != null">company_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="propId != null">#{propId},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="status != null">#{status},</if>
            <if test="delFlag != null">#{delFlag},</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="remark != null">#{remark},</if>
            <if test="companyId != null">#{companyId},</if>
        </trim>
    </insert>

    <select id="selectMaTypeByTypeId" resultMap="MaTypeResult">
        select m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id,m.unit_name, m.manage_type,
               m.lease_price, m.eff_time, m.rent_price, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
               m.holding_time, m.warn_num, mtf.file_name photoName, mtf.file_url photoUrl,
               mtf2.file_name documentName, mtf2.file_url documentUrl, mtk.user_id keeperUserId,
               su.nick_name keeperUserName, mtr.user_id repairUserId, su1.nick_name repairUserName,mpi.prop_id as propId, mpi.prop_name as propName,
               m.del_flag, m.create_by, m.create_time,
               m.remark, m.company_id,m.fac_model as facModel
        from ma_type m
                     left join ma_prop_set mps on m.type_id = mps.type_id
                     left join ma_prop_info mpi on mps.prop_id = mpi.prop_id
                     left join (select * from ma_type_file where file_type = '1') mtf on m.type_id = mtf.type_id
                     left join (select * from ma_type_file where file_type = '2') mtf2 on m.type_id = mtf2.type_id
                     left join ma_type_keeper mtk on m.type_id = mtk.type_id
                     left join sys_user su on mtk.user_id = su.user_id
                     left join ma_type_repair mtr on m.type_id = mtr.type_id
                     left join sys_user su1 on mtr.user_id = su1.user_id
        where m.type_id = #{typeId} and m.status = '0'
    </select>

    <update id="updateKeeperByTypeId">
        update ma_type_keeper set user_id = #{userId} where type_id = #{typeId}
    </update>

    <update id="updateRepairByTypeId">
        update ma_type_repair set user_id = #{userId} where type_id = #{typeId}
    </update>

    <update id="updatePropSetByTypeId">
        update ma_prop_set set prop_id = #{propId} where type_id = #{typeId}
    </update>

    <select id="selectTypeByTypeId" parameterType="long" resultMap="MaTypeResult">
        <include refid="selectMaTypeVo"/>
        where type_id = #{typeId}
    </select>

    <select id="selectMaTypeTree" resultMap="MaTypeResult" parameterType="java.lang.String">
        select m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id, m.manage_type,
               m.lease_price, m.eff_time, m.rent_price, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
               m.holding_time, m.warn_num, m.del_flag, m.create_by, m.create_time,
               m.remark, m.company_id
        from ma_type m
        where level != 4 and m.status = '0' and del_flag = '0'
    </select>
    <select id="getMaTypeSelect" resultType="com.bonus.sgzb.base.api.domain.MaType">
        select m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id, m.manage_type,
        m.lease_price,m.eff_time, m.rent_price, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
        m.holding_time, m.warn_num, m.del_flag, m.create_by, m.create_time,
        m.remark, m.company_id
        from ma_type m
        <where>
            m.status = '0'
        </where>
    </select>

    <select id="getListByParentId" resultMap="MaTypeResult">
        select DISTINCT m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id, m.unit_name, m.manage_type,
        m.lease_price,m.rent_price, m.eff_time, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
        m.holding_time, m.warn_num, mtf.file_name photoName, mtf.file_url photoUrl,
        mtf2.file_name documentName, mtf2.file_url documentUrl, mtk.user_id keeperUserId,
        su.nick_name keeperUserName, mpi.prop_name, m.del_flag, m.create_by, m.create_time,
        m.remark, m.company_id,m.fac_model as facModel
        from ma_type m
        left join ma_prop_set mps on m.type_id = mps.type_id
        left join ma_prop_info mpi on mps.prop_id = mpi.prop_id
        left join (select * from ma_type_file where file_type = '1') mtf on m.type_id = mtf.type_id
        left join (select * from ma_type_file where file_type = '2') mtf2 on m.type_id = mtf2.type_id
        left join ma_type_keeper mtk on m.type_id = mtk.type_id
        left join sys_user su on mtk.user_id = su.user_id
        where m.parent_id = #{typeId} and m.status = '0' and m.del_flag = '0'
        <if test="typeName != null and typeName !=''">
            AND type_name like concat('%',#{typeName},'%')
        </if>
    </select>

    <select id="selectMaTypeTreeByLevel" resultMap="MaTypeResult" parameterType="java.lang.String">
        select m.type_id, m.type_name, m.parent_id, m.status, m.num, m.unit_id, m.manage_type,
        m.lease_price, m.eff_time, m.rent_price, m.buy_price, m.pay_price, m.level, m.rated_load, m.test_load,
        m.holding_time, m.warn_num, m.del_flag, m.create_by, m.create_time,
        m.remark, m.company_id,mtf.file_url photoUrl
        from ma_type m
        left join (select * from ma_type_file where file_type = '1') mtf on m.type_id = mtf.type_id
        <where>
            m.level = '4' and m.parent_id = #{id,jdbcType=VARCHAR} and m.del_flag = '0'
        </where>
    </select>

    <select id="selectMaTypeListByLevelNotFour" resultMap="MaTypeResult">
        SELECT
        m.type_id,
        m.type_name,
        m.parent_id,
        m.STATUS,
        m.num,
        m.unit_id,
        m.manage_type,
        m.lease_price,
        m.eff_time,
        m.rent_price,
        m.buy_price,
        m.pay_price,
        m.LEVEL,
        m.rated_load,
        m.test_load,
        m.holding_time,
        m.warn_num,
        m.del_flag,
        m.create_by,
        m.create_time,
        m.remark,
        m.company_id,
        mtf.file_url photoUrl,
        mt.photoUrl as childPhoto
        FROM
        ma_type m
        LEFT JOIN ( SELECT * FROM ma_type_file WHERE file_type = '1' ) mtf ON m.type_id = mtf.type_id
        LEFT JOIN (
        SELECT
        m.*,
        mtf.file_url photoUrl
        FROM
        ma_type m
        LEFT JOIN ( SELECT * FROM ma_type_file WHERE file_type = '1' ) mtf ON m.type_id = mtf.type_id
        WHERE
        m.del_flag = '0'
        GROUP BY m.parent_id
        ) mt on mt.parent_id = m.type_id
        <where>
            m.level != 4 and m.del_flag = '0'
            <if test="parentId!= null and parentId != ''">
                and m.parent_id = #{parentId}
            </if>
            <if test="keyword!= null and keyword != ''">
                and locate(#{keyword}, m.type_name) > 0
            </if>
        </where>
    </select>

    <update id="updateTypeNum">
        update ma_type set num = IFNULL( num, 0 ) + 1 where type_id = #{typeId}
    </update>

</mapper>