<?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.material.ma.mapper.TypeMapper">

    <resultMap type="com.bonus.material.ma.domain.Type" id="TypeResult">
        <result property="typeId"    column="type_id"    />
        <result property="typeName"    column="type_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="keeperNickName"    column="keeper_nick_name"    />
        <result property="repairNickName"    column="repair_nick_name"    />
        <result property="houseId"    column="house_id"    />
        <result property="houseName"    column="house_name"    />
        <result property="storageNum"    column="storage_num"    />
        <result property="typeCode"    column="type_code"    />
        <result property="modelCode"    column="model_code"    />
        <result property="unitId"    column="unit_id"    />
        <result property="unitName"    column="unit_name"    />
        <result property="unitValue"    column="unit_value"    />
        <result property="sortNum"    column="sort_num"    />
        <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="payRatio"    column="pay_ratio"    />
        <result property="payPrice"    column="pay_price"    />
        <result property="taxRatio"    column="tax_ratio"    />
        <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="facModel"    column="fac_model"    />
        <result property="intelligentCode"    column="intelligent_code"    />
        <result property="isTest" column="is_test" />
        <result property="companyId" column="company_id" />
        <result property="samplingRatio" column="sampling_ratio" />
    </resultMap>

    <resultMap type="com.bonus.material.ma.domain.vo.MaTypeVo" id="MaTypeVoResult">
        <result property="typeId"    column="type_id"    />
        <result property="typeName"    column="type_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="storageNum"    column="storage_num"    />
        <result property="typeCode"    column="type_code"    />
        <result property="modelCode"    column="model_code"    />
        <result property="unitId"    column="unit_id"    />
        <result property="unitName"    column="unit_name"    />
        <result property="unitValue"    column="unit_value"    />
        <result property="sortNum"    column="sort_num"    />
        <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="payRatio"    column="pay_ratio"    />
        <result property="payPrice"    column="pay_price"    />
        <result property="taxRatio"    column="tax_ratio"    />
        <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="facModel"    column="fac_model"    />
        <result property="intelligentCode"    column="intelligent_code"    />
        <result property="itemType" column="itemType" />
        <result property="materialType" column="materialType" />
        <result property="materialName" column="materialName" />
        <result property="houseName" column="house_name" />
    </resultMap>

    <resultMap type="com.bonus.material.ma.domain.vo.MaTypeConfigVo" id="MaTypeConfigVoResult">
        <result property="typeId"    column="type_id"    />
        <result property="typeName"    column="type_name"    />
        <result property="parentId"    column="parent_id"    />
        <result property="level"    column="level"    />
        <result property="houseId"    column="house_id"    />
        <result property="houseName" column="house_name" />
        <result property="parentOneLevelName" column="parentFourLevelName" />
        <result property="parentTwoLevelName" column="parentThreeLevelName" />
        <result property="parentThreeLevelName" column="parentTwoLevelName" />
        <result property="parentFourLevelName" column="parentOneLevelName" />
    </resultMap>

    <sql id="selectTypeVo">
        select
            type_id, type_name, parent_id, storage_num, type_code, model_code, unit_id, unit_name, manage_type, lease_price,
            eff_time, rent_price, buy_price, pay_ratio, pay_price, tax_ratio, level, rated_load, test_load, holding_time, warn_num,
            create_by, create_time, update_by, update_time, is_plan,is_ancuo, remark, fac_model, intelligent_code
        from ma_type
    </sql>

    <select id="selectTypeList" parameterType="com.bonus.material.ma.domain.Type" resultMap="TypeResult">
        select
            mt4.type_id, mt4.type_name, mt4.parent_id, mt4.storage_num, mt4.type_code, mt4.model_code, mt4.unit_id, mt4.unit_name, mt4.manage_type, mt4.lease_price,
            mt4.eff_time, mt4.rent_price, mt4.buy_price, mt4.pay_ratio, mt4.pay_price, mt4.tax_ratio, mt4.level, mt4.rated_load, mt4.test_load, mt4.holding_time, mt4.warn_num,
            mt4.create_by, mt4.create_time, mt4.update_by, mt4.update_time, mt4.is_plan,mt4.is_ancuo, mt4.remark, mt4.fac_model, mt4.intelligent_code, whs.house_id, whi.house_name
        from ma_type mt4
                 left join ma_type mt3 on mt3.type_id=mt4.parent_id
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt4.del_flag = 0 and mt4.level = 4

        UNION

        select
            mt3.type_id, mt3.type_name, mt3.parent_id, mt3.storage_num, mt3.type_code, mt3.model_code, mt3.unit_id, mt3.unit_name, mt3.manage_type, mt3.lease_price,
            mt3.eff_time, mt3.rent_price, mt3.buy_price, mt3.pay_ratio, mt3.pay_price, mt3.tax_ratio, mt3.level, mt3.rated_load, mt3.test_load, mt3.holding_time, mt3.warn_num,
            mt3.create_by, mt3.create_time, mt3.update_by, mt3.update_time, mt3.is_plan,mt3.is_ancuo, mt3.remark, mt3.fac_model, mt3.intelligent_code, whs.house_id, whi.house_name
        from ma_type mt3
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt3.del_flag = 0 and mt3.level = 3

        UNION

        select
            mt2.type_id, mt2.type_name, mt2.parent_id, mt2.storage_num, mt2.type_code, mt2.model_code, mt2.unit_id, mt2.unit_name, mt2.manage_type, mt2.lease_price,
            mt2.eff_time, mt2.rent_price, mt2.buy_price, mt2.pay_ratio, mt2.pay_price, mt2.tax_ratio, mt2.level, mt2.rated_load, mt2.test_load, mt2.holding_time, mt2.warn_num,
            mt2.create_by, mt2.create_time, mt2.update_by, mt2.update_time, mt2.is_plan,mt2.is_ancuo, mt2.remark, mt2.fac_model, mt2.intelligent_code, whs.house_id, whi.house_name
        from ma_type mt2
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt2.del_flag = 0 and mt2.level = 2

        UNION

        select
            mt1.type_id, mt1.type_name, mt1.parent_id, mt1.storage_num, mt1.type_code, mt1.model_code, mt1.unit_id, mt1.unit_name, mt1.manage_type, mt1.lease_price,
            mt1.eff_time, mt1.rent_price, mt1.buy_price, mt1.pay_ratio, mt1.pay_price, mt1.tax_ratio, mt1.level, mt1.rated_load, mt1.test_load, mt1.holding_time, mt1.warn_num,
            mt1.create_by, mt1.create_time, mt1.update_by, mt1.update_time, mt1.is_plan,mt1.is_ancuo, mt1.remark, mt1.fac_model, mt1.intelligent_code, whs.house_id, whi.house_name
        from ma_type mt1
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt1.del_flag = 0 and mt1.level = 1

        order by type_id
    </select>

    <select id="selectTypeByTypeId" parameterType="Long" resultMap="TypeResult">
        SELECT
            t.type_id,t.type_name,t.parent_id,t.storage_num,t.type_code,
            t.model_code,t.unit_id,t.unit_name,t.unit_value,t.sort_num,t.manage_type,t.lease_price,t.eff_time,
            t.rent_price,t.buy_price,t.pay_ratio,t.pay_price,t.tax_ratio,t.level,t.rated_load,t.test_load,
            t.holding_time,t.warn_num,t.create_by,t.create_time,t.update_by,t.update_time,t.is_plan,
            t.is_ancuo,t.remark,t.fac_model,t.intelligent_code,t.is_test,
            GROUP_CONCAT(distinct  su.user_name)  as keeper_nick_name,
            GROUP_CONCAT(distinct su.user_id) as keeperId,
            GROUP_CONCAT(distinct su2.user_name) as repair_nick_name,
            GROUP_CONCAT(distinct su2.user_id) as repairerId,
            hi.house_name,
            t.sampling_ratio
        FROM ma_type AS t
                 left join ma_type mt2 on t.parent_id  = mt2.type_id
                 left join ma_type mt3 on mt2.parent_id = mt3.type_id
                 left join wh_house_set hs on mt3.parent_id = hs.type_id
                 left join wh_house_info hi on hs.house_id = hi.house_id
                 left join ma_type_keeper mtk on t.type_id = mtk.type_id
                 left join ma_type_repair mtr on t.type_id = mtr.type_id
                 left join sys_user su on mtk.user_id = su.user_id and su.del_flag = 0
                 left join sys_user su2 on mtr.user_id = su2.user_id and su2.del_flag = 0

        WHERE t.type_id = #{typeId} and t.del_flag = 0
        GROUP BY t.type_id
        limit 1
    </select>

    <select id="selectParentId" resultType="java.lang.Integer">
        SELECT DISTINCT
            mt2.type_id
        FROM
            ma_type mt
        LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
        LEFT JOIN ma_type mt3 ON mt2.parent_id = mt3.type_id
        LEFT JOIN ma_type mt4 ON mt3.parent_id = mt4.type_id
        LEFT JOIN wh_house_set mhs on mt4.type_id = mhs.type_id
        <where>
            <if test="level == 1">
                and mt4.type_id = #{typeId}
            </if>
            <if test="level == 2">
                and mt3.type_id = #{typeId}
            </if>
            <if test="level == 3">
                and mt2.type_id = #{typeId}
            </if>
            <if test="level == 0">
                and mt.level = 4
            </if>
            and mt2.type_id is not null
            and mhs.house_id = #{houseId} and mhs.del_flag = 0

        </where>
    </select>

    <insert id="insertType" parameterType="com.bonus.material.ma.domain.Type" 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="storageNum != null">storage_num,</if>
            <if test="typeCode != null">type_code,</if>
            <if test="modelCode != null">model_code,</if>
            <if test="unitId != null">unit_id,</if>
            <if test="unitName != null">unit_name,</if>
            <if test="unitValue != null">unit_value,</if>
            <if test="sortNum != null">sort_num,</if>
            <if test="manageType != null and manageType != ''">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="payRatio != null">pay_ratio,</if>
            <if test="payPrice != null">pay_price,</if>
            <if test="taxRatio != null">tax_ratio,</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="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>
            <if test="intelligentCode != null">intelligent_code,</if>
            <if test="isTest != null">is_test,</if>
            <if test="samplingRatio != null">sampling_ratio,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="typeName != null and typeName != ''">#{typeName},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="storageNum != null">#{storageNum},</if>
            <if test="typeCode != null">#{typeCode},</if>
            <if test="modelCode != null">#{modelCode},</if>
            <if test="unitId != null">#{unitId},</if>
            <if test="unitName != null">#{unitName},</if>
            <if test="unitValue != null">#{unitValue},</if>
            <if test="sortNum != null">#{sortNum},</if>
            <if test="manageType != null and manageType != ''">#{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="payRatio != null">#{payRatio},</if>
            <if test="payPrice != null">#{payPrice},</if>
            <if test="taxRatio != null">#{taxRatio},</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="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>
            <if test="intelligentCode != null">#{intelligentCode},</if>
            <if test="isTest != null">#{isTest},</if>
            <if test="samplingRatio != null">#{samplingRatio},</if>
         </trim>
    </insert>

    <update id="updateType" parameterType="com.bonus.material.ma.domain.Type">
        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="storageNum != null">storage_num = #{storageNum},</if>
            <if test="typeCode != null">type_code = #{typeCode},</if>
            <if test="modelCode != null">model_code = #{modelCode},</if>
            <if test="unitId != null">unit_id = #{unitId},</if>
            <if test="unitName != null">unit_name = #{unitName},</if>
            <if test="unitName != null">unit_name = #{unitName},</if>
            <if test="unitValue != null">unit_value = #{unitValue},</if>
            <if test="sortNum != null">sort_num = #{sortNum},</if>
            <if test="manageType != null and manageType != ''">manage_type = #{manageType},</if>
            <if test="leasePrice != null">lease_price = #{leasePrice},</if>
            <if test="effTime != null">eff_time = #{effTime},</if>
            <if test="rentPrice != null">rent_price = #{rentPrice},</if>
            <if test="buyPrice != null">buy_price = #{buyPrice},</if>
            <if test="payRatio != null">pay_ratio = #{payRatio},</if>
            <if test="payPrice != null">pay_price = #{payPrice},</if>
            <if test="taxRatio != null">tax_ratio = #{taxRatio},</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="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="facModel != null">fac_model = #{facModel},</if>
            <if test="intelligentCode != null">intelligent_code = #{intelligentCode},</if>
            <if test="isTest != null">is_test = #{isTest},</if>
            <if test="samplingRatio != null">sampling_ratio = #{samplingRatio},</if>
        </trim>
        where type_id = #{typeId}
    </update>

    <delete id="deleteTypeByTypeId" parameterType="Long">
        delete from ma_type where type_id = #{typeId}
    </delete>

    <delete id="logicDeleteTypeByTypeIds" parameterType="String">
        update ma_type set del_flag = 2 where type_id in
        <foreach item="typeId" collection="array" open="(" separator="," close=")">
            #{typeId}
        </foreach>
    </delete>

    <update id="logicDeleteTypeByTypeId">
        update ma_type set del_flag = 2 where type_id = #{typeId,jdbcType=BIGINT}
    </update>

    <select id="selectMaTypeTree" resultMap="TypeResult">
        select
            mt3.type_id, mt3.type_name, mt3.parent_id, mt3.storage_num, mt3.type_code, mt3.model_code, mt3.unit_id,
            mt3.unit_name, mt3.level, whs.house_id
        from ma_type mt3
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
        where mt3.del_flag = 0 and mt3.level = 3

        UNION

        select
            mt2.type_id, mt2.type_name, mt2.parent_id, mt2.storage_num, mt2.type_code, mt2.model_code, mt2.unit_id,
            mt2.unit_name, mt2.level, whs.house_id
        from ma_type mt2
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
        where mt2.del_flag = 0 and mt2.level = 2

        UNION

        select
            mt1.type_id, mt1.type_name, mt1.parent_id, mt1.storage_num, mt1.type_code, mt1.model_code, mt1.unit_id,
            mt1.unit_name, mt1.level, whs.house_id
        from ma_type mt1
                 left join wh_house_set whs on mt1.type_id=whs.type_id
        where mt1.del_flag = 0 and mt1.level = 1

        order by type_id
    </select>

<!--    <select id="selectMaTypeTree2" resultMap="TypeResult">-->
<!--        select-->
<!--            mt.type_id, mt.type_name, mt.parent_id, mt.storage_num, mt.type_code, mt.model_code, mt.unit_id, mt.unit_name,-->
<!--            mt.manage_type, mt.lease_price, mt.eff_time, mt.rent_price, mt.buy_price, mt.pay_ratio, mt.pay_price,-->
<!--            mt.tax_ratio, mt.level, mt.rated_load, mt.test_load, mt.holding_time, mt.warn_num,-->
<!--            mt.create_by, mt.create_time, mt.update_by, mt.update_time, mt.is_plan,mt.is_ancuo,-->
<!--            mt.remark, mt.fac_model, mt.intelligent_code, whs.house_id-->
<!--        from ma_type mt-->
<!--                 left join wh_house_set whs on mt.type_id=whs.type_id-->
<!--        where mt.del_flag = 0 and mt.level != #{level}-->
<!--    </select>-->

    <select id="selectTypeListAndParent" resultMap="MaTypeVoResult">
        SELECT
            a.*,                                        -- 当前层级的所有字段
            b.type_name AS parentThreeLevelName,        -- 父层级名称
            c.type_name AS parentTwoLevelName,          -- 祖父层级名称
            d.type_name AS parentOneLevelName,           -- 曾祖父层级名称
            whi.house_name
        FROM
            ma_type a
        LEFT JOIN
            wh_house_set whs ON a.type_id = whs.type_id AND whs.del_flag = '0'
        LEFT JOIN
            wh_house_info whi ON whi.house_id = whs.house_id AND whs.del_flag = '0'
        LEFT JOIN
            ma_type b ON a.parent_id = b.type_id and b.del_flag = '0'       -- 第一层，父类型
        LEFT JOIN
            ma_type c ON b.parent_id = c.type_id and c.del_flag = '0'       -- 第二层，祖父类型
        LEFT JOIN
            ma_type d ON c.parent_id = d.type_id and d.del_flag = '0'       -- 第三层，曾祖父类型
        <where>
            <if test="typeName != null  and typeName != ''"> and a.type_name like concat('%', #{typeName}, '%')</if>
            <if test="parentId != null and parentId != '0' "> and a.parent_id = #{parentId}</if>
            <if test="storageNum != null "> and a.storage_num = #{storageNum}</if>
            <if test="typeCode != null  and typeCode != ''"> and a.type_code = #{typeCode}</if>
            <if test="modelCode != null  and modelCode != ''"> and a.model_code = #{modelCode}</if>
            <if test="unitId != null "> and a.unit_id = #{unitId}</if>
            <if test="manageType != null  and manageType != ''"> and a.manage_type = #{manageType}</if>
            <if test="leasePrice != null "> and a.lease_price = #{leasePrice}</if>
            <if test="effTime != null "> and a.eff_time = #{effTime}</if>
            <if test="rentPrice != null "> and a.rent_price = #{rentPrice}</if>
            <if test="buyPrice != null "> and a.buy_price = #{buyPrice}</if>
            <if test="payRatio != null "> and a.pay_ratio = #{payRatio}</if>
            <if test="level != null  and level != ''"> and a.`level` = #{level}</if>
            <if test="ratedLoad != null  and ratedLoad != ''"> and a.rated_load = #{ratedLoad}</if>
            <if test="testLoad != null  and testLoad != ''"> and a.test_load = #{testLoad}</if>
            <if test="holdingTime != null "> and a.holding_time = #{holdingTime}</if>
            <if test="warnNum != null "> and a.warn_num = #{warnNum}</if>
            <if test="isPlan != null  and isPlan != ''"> and a.is_plan = #{isPlan}</if>
            <if test="isAncuo != null  and isAncuo != ''"> and a.is_ancuo = #{isAncuo}</if>
            <if test="facModel != null  and facModel != ''"> and a.fac_model = #{facModel}</if>
            <if test="intelligentCode != null  and intelligentCode != ''"> and a.intelligent_code = #{intelligentCode}</if>
            and a.del_flag = '0'
        </where>
    </select>

    <select id="selectMaTypeListByHouseId" resultType="com.bonus.material.ma.domain.vo.MaTypeSelectVo">
        select
            wh_house_set.type_id AS typeId,
            mt.type_name AS typeName,mt.parent_id AS parentId,mt.level
        from
            wh_house_set
        left join
            ma_type mt on wh_house_set.type_id = mt.type_id
        where
            wh_house_set.house_id = #{mouseId} and wh_house_set.del_flag = 0
    </select>

    <select id="getMaTypeSelectVoListByParentId" resultType="com.bonus.material.ma.domain.vo.MaTypeSelectVo">
        select
            type_id as typeId,
            type_name as typeName,
            parent_id as parentId,
            level
        from
            ma_type
        where
            parent_id = #{parentId} and del_flag = 0
    </select>

    <select id="selectFourLevelTypeListAndParent" resultMap="MaTypeConfigVoResult">
        SELECT
            a.*,                                        -- 当前层级的所有字段
            a.type_name AS parentFourLevelName,         -- 当前层级名称
            b.type_name AS parentThreeLevelName,        -- 父层级名称
            c.type_name AS parentTwoLevelName,          -- 祖父层级名称
            d.type_name AS parentOneLevelName,           -- 曾祖父层级名称
            whs.house_id,whi.house_name
        FROM
            ma_type a
        LEFT JOIN
            wh_house_set whs ON a.type_id = whs.type_id AND whs.del_flag = '0'
        LEFT JOIN
            wh_house_info whi ON whi.house_id = whs.house_id AND whs.del_flag = '0'
        LEFT JOIN
            ma_type b ON a.parent_id = b.type_id AND b.del_flag = '0'       -- 第一层，父类型
        LEFT JOIN
            ma_type c ON b.parent_id = c.type_id AND c.del_flag = '0'       -- 第二层，祖父类型
        LEFT JOIN
            ma_type d ON c.parent_id = d.type_id AND d.del_flag = '0'       -- 第三层，曾祖父类型
        WHERE
            a.del_flag = 0 AND a.`level` = '4'
    </select>

    <select id="selectThreeLevelTypeListAndParent" resultMap="MaTypeConfigVoResult">
        SELECT
            a.*,
            a.type_name AS parentThreeLevelName,
            b.type_name AS parentTwoLevelName,            -- 父类型名称
            c.type_name AS parentOneLevelName,       -- 祖父类型名称
            NULL AS parentFourLevelName
        FROM
            ma_type a
                LEFT JOIN
            ma_type b ON a.parent_id = b.type_id       -- 第一层，父类型
                LEFT JOIN
            ma_type c ON b.parent_id = c.type_id       -- 第二层，祖父类型
        WHERE
            a.del_flag = 0 AND a.`level` = '3'
    </select>

    <select id="selectMaTypeList" resultMap="TypeResult">
        select DISTINCT m.type_id, m.type_name, m.parent_id, m.unit_id, m.unit_name, m.unit_value,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.sort_num,
                        m.del_flag, m.create_by, m.create_time,
                        m.remark,m.type_id id , m.type_name label,
                        CASE m.manage_type
                        WHEN 0 THEN
                        IFNULL(subquery0.num, 0)
                        ELSE
                        IFNULL(m.storage_num, 0)
                        END as storage_num,
                        m.company_id as companyId
        from ma_type m
                 left join (SELECT mt.type_id,
                            mt2.type_name AS             typeName,
                            mt.type_name  AS             typeModelName,
                            count(mm.ma_id) num
                            FROM ma_machine mm
                            LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
                            LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                            WHERE mm.ma_code is not null and mm.ma_status in (1)
                            GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = m.type_id
        <where>
            m.del_flag = '0'
            <if test="typeName != null and typeName !=''">
                AND m.type_name like concat('%',#{typeName},'%')
            </if>
            <if test="typeId != null and typeId !=''">
                AND m.type_id = #{typeId}
            </if>
        </where>

    </select>

    <select id="selectMaTypeThreeList" resultMap="TypeResult">
        select DISTINCT m.type_id, m.type_name, m.parent_id, m.unit_id, m.unit_name, m.unit_value,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.type_id id , m.type_name label,
        CASE m.manage_type
        WHEN 0 THEN
        IFNULL(subquery0.num, 0)
        ELSE
        IFNULL(m.storage_num, 0)
        END as storage_num
        from ma_type m
        left join (SELECT mt.type_id,
        mt2.type_name AS             typeName,
        mt.type_name  AS             typeModelName,
        count(mm.ma_id) num
        FROM ma_machine mm
        LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
        LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        WHERE mm.ma_code is not null and mm.ma_status in (1)
        GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = m.type_id
        <where>
            m.del_flag = '0'
            and m.level != '4'
            <if test="typeName != null and typeName !=''">
                AND m.type_name like concat('%',#{typeName},'%')
            </if>
        </where>
    </select>

    <select id="getListByTypeName" resultType="com.bonus.material.ma.domain.vo.MaTypeVo">
        SELECT DISTINCT
            m3.type_name AS itemType,
            m2.type_name AS materialType,
            m1.type_name AS materialName,
            m.type_id AS typeId,
            m.type_name AS typeName,
            m.parent_id as parentId,
            m.manage_type as manageType,
            m.unit_name as unitName,
            m.lease_price as leasePrice,
            m.rent_price as rentPrice,
            m.eff_time as effTime,
            m.buy_price as buyPrice,
            m.LEVEL as level,
            m.rated_load as ratedLoad,
            m.test_load as testLoad,
            m.holding_time as holdingTime,
            m.remark as remark,whi.house_name as houseName,
            m.fac_model AS facModel,su.user_name as keeperNickName,su2.user_name as repairNickName,
            m.sort_num as sortNum
        FROM
            ma_type m
                LEFT JOIN ma_type m1 ON m.parent_id = m1.type_id and m1.del_flag = '0'
                LEFT JOIN ma_type m2 ON m1.parent_id = m2.type_id and m2.del_flag = '0'
                LEFT JOIN ma_type m3 ON m2.parent_id = m3.type_id and m3.del_flag = '0'
                left join ma_type_keeper mtk on m.type_id = mtk.type_id
                left join ma_type_repair mtr on m.type_id = mtr.type_id
                left join sys_user su on mtk.user_id = su.user_id
                left join sys_user su2 on mtr.user_id = su2.user_id
                left join wh_house_set whs on m.type_id = whs.type_id
                left join wh_house_info whi on whs.house_id = whi.house_id
        WHERE m.parent_id = #{typeId} and m.del_flag = '0'
        <if test="type.keyword != null and type.keyword !=''">
            AND (m.type_name like concat('%',#{type.keyword},'%')
                or m1.type_name like concat('%',#{type.keyword},'%')
                or m2.type_name like concat('%',#{type.keyword},'%')
                or m3.type_name like concat('%',#{type.keyword},'%')
                )
        </if>
        GROUP BY m.type_id
        ORDER BY
        m.sort_num ASC,
        CASE
        WHEN m.update_time > m.create_time THEN
        m.update_time ELSE m.create_time
        END DESC,
        m.create_time DESC
    </select>
    <select id="queryByNameAndParentId" resultType="com.bonus.material.ma.domain.Type">
        select
            type_id as typeId, parent_id as parentId, type_name as typeName, level as level
        from ma_type
        where type_name = #{typeName} and parent_id = #{parentId}  and del_flag = '0'
    </select>

    <select id="selectById" resultType="com.bonus.material.ma.domain.Type">
        select
            type_id as typeId, parent_id as parentId, type_name as typeName, level as level
        from ma_type
        where parent_id = #{typeId} and del_flag = '0'
    </select>

    <select id="selectTypeList4Keeper" parameterType="com.bonus.material.ma.domain.Type" resultMap="TypeResult">
        select
            mt4.type_id, mt4.type_name, mt4.parent_id, mt4.storage_num, mt4.type_code, mt4.model_code, mt4.unit_id, mt4.unit_name, mt4.manage_type, mt4.lease_price,
            mt4.eff_time, mt4.rent_price, mt4.buy_price, mt4.pay_ratio, mt4.pay_price, mt4.tax_ratio, mt4.level, mt4.rated_load, mt4.test_load, mt4.holding_time, mt4.warn_num,
            mt4.create_by, mt4.create_time, mt4.update_by, mt4.update_time, mt4.is_plan,mt4.is_ancuo, mt4.remark, mt4.fac_model, mt4.intelligent_code, whs.house_id, whi.house_name,su.nick_name as keeper_nick_name
        from ma_type mt4
                 left join ma_type mt3 on mt3.type_id=mt4.parent_id
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
                 left join ma_type_keeper mtk on mt4.type_id=mtk.type_id
                 left join sys_user su on mtk.user_id=su.user_id
        where mt4.del_flag = 0 and mt4.level = 4
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>
        <if test="level == 3">
            and mt3.type_id=#{typeId}
        </if>

        UNION

        select
            mt3.type_id, mt3.type_name, mt3.parent_id, mt3.storage_num, mt3.type_code, mt3.model_code, mt3.unit_id, mt3.unit_name, mt3.manage_type, mt3.lease_price,
            mt3.eff_time, mt3.rent_price, mt3.buy_price, mt3.pay_ratio, mt3.pay_price, mt3.tax_ratio, mt3.level, mt3.rated_load, mt3.test_load, mt3.holding_time, mt3.warn_num,
            mt3.create_by, mt3.create_time, mt3.update_by, mt3.update_time, mt3.is_plan,mt3.is_ancuo, mt3.remark, mt3.fac_model, mt3.intelligent_code, whs.house_id, whi.house_name,null as keeper_nick_name
        from ma_type mt3
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt3.del_flag = 0 and mt3.level = 3
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>
        <if test="level == 3">
            and mt3.type_id=#{typeId}
        </if>

        UNION

        select
            mt2.type_id, mt2.type_name, mt2.parent_id, mt2.storage_num, mt2.type_code, mt2.model_code, mt2.unit_id, mt2.unit_name, mt2.manage_type, mt2.lease_price,
            mt2.eff_time, mt2.rent_price, mt2.buy_price, mt2.pay_ratio, mt2.pay_price, mt2.tax_ratio, mt2.level, mt2.rated_load, mt2.test_load, mt2.holding_time, mt2.warn_num,
            mt2.create_by, mt2.create_time, mt2.update_by, mt2.update_time, mt2.is_plan,mt2.is_ancuo, mt2.remark, mt2.fac_model, mt2.intelligent_code, whs.house_id, whi.house_name, null as keeper_nick_name
        from ma_type mt2
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt2.del_flag = 0 and mt2.level = 2
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>

        UNION

        select
            mt1.type_id, mt1.type_name, mt1.parent_id, mt1.storage_num, mt1.type_code, mt1.model_code, mt1.unit_id, mt1.unit_name, mt1.manage_type, mt1.lease_price,
            mt1.eff_time, mt1.rent_price, mt1.buy_price, mt1.pay_ratio, mt1.pay_price, mt1.tax_ratio, mt1.level, mt1.rated_load, mt1.test_load, mt1.holding_time, mt1.warn_num,
            mt1.create_by, mt1.create_time, mt1.update_by, mt1.update_time, mt1.is_plan,mt1.is_ancuo, mt1.remark, mt1.fac_model, mt1.intelligent_code, whs.house_id, whi.house_name,null as keeper_nick_name
        from ma_type mt1
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt1.del_flag = 0 and mt1.level = 1
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>

        order by type_id
    </select>

    <select id="selectTypeList4Repair" parameterType="com.bonus.material.ma.domain.Type" resultMap="TypeResult">
        select
            mt4.type_id, mt4.type_name, mt4.parent_id, mt4.storage_num, mt4.type_code, mt4.model_code, mt4.unit_id, mt4.unit_name, mt4.manage_type, mt4.lease_price,
            mt4.eff_time, mt4.rent_price, mt4.buy_price, mt4.pay_ratio, mt4.pay_price, mt4.tax_ratio, mt4.level, mt4.rated_load, mt4.test_load, mt4.holding_time, mt4.warn_num,
            mt4.create_by, mt4.create_time, mt4.update_by, mt4.update_time, mt4.is_plan,mt4.is_ancuo, mt4.remark, mt4.fac_model, mt4.intelligent_code, whs.house_id, whi.house_name,su.nick_name as repair_nick_name
        from ma_type mt4
                 left join ma_type mt3 on mt3.type_id=mt4.parent_id
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
                 left join ma_type_repair mtr on mt4.type_id=mtr.type_id
                 left join sys_user su on mtr.user_id=su.user_id
        where mt4.del_flag = 0 and mt4.level = 4
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>
        <if test="level == 3">
            and mt3.type_id=#{typeId}
        </if>

        UNION

        select
            mt3.type_id, mt3.type_name, mt3.parent_id, mt3.storage_num, mt3.type_code, mt3.model_code, mt3.unit_id, mt3.unit_name, mt3.manage_type, mt3.lease_price,
            mt3.eff_time, mt3.rent_price, mt3.buy_price, mt3.pay_ratio, mt3.pay_price, mt3.tax_ratio, mt3.level, mt3.rated_load, mt3.test_load, mt3.holding_time, mt3.warn_num,
            mt3.create_by, mt3.create_time, mt3.update_by, mt3.update_time, mt3.is_plan,mt3.is_ancuo, mt3.remark, mt3.fac_model, mt3.intelligent_code, whs.house_id, whi.house_name,null as repair_nick_name
        from ma_type mt3
                 left join ma_type mt2 on mt2.type_id=mt3.parent_id
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt3.del_flag = 0 and mt3.level = 3
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>
        <if test="level == 3">
            and mt3.type_id=#{typeId}
        </if>

        UNION

        select
            mt2.type_id, mt2.type_name, mt2.parent_id, mt2.storage_num, mt2.type_code, mt2.model_code, mt2.unit_id, mt2.unit_name, mt2.manage_type, mt2.lease_price,
            mt2.eff_time, mt2.rent_price, mt2.buy_price, mt2.pay_ratio, mt2.pay_price, mt2.tax_ratio, mt2.level, mt2.rated_load, mt2.test_load, mt2.holding_time, mt2.warn_num,
            mt2.create_by, mt2.create_time, mt2.update_by, mt2.update_time, mt2.is_plan,mt2.is_ancuo, mt2.remark, mt2.fac_model, mt2.intelligent_code, whs.house_id, whi.house_name, null as repair_nick_name
        from ma_type mt2
                 left join ma_type mt1 on mt1.type_id=mt2.parent_id
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt2.del_flag = 0 and mt2.level = 2
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>
        <if test="level == 2">
            and mt2.type_id=#{typeId}
        </if>

        UNION

        select
            mt1.type_id, mt1.type_name, mt1.parent_id, mt1.storage_num, mt1.type_code, mt1.model_code, mt1.unit_id, mt1.unit_name, mt1.manage_type, mt1.lease_price,
            mt1.eff_time, mt1.rent_price, mt1.buy_price, mt1.pay_ratio, mt1.pay_price, mt1.tax_ratio, mt1.level, mt1.rated_load, mt1.test_load, mt1.holding_time, mt1.warn_num,
            mt1.create_by, mt1.create_time, mt1.update_by, mt1.update_time, mt1.is_plan,mt1.is_ancuo, mt1.remark, mt1.fac_model, mt1.intelligent_code, whs.house_id, whi.house_name,null as repair_nick_name
        from ma_type mt1
                 left join wh_house_set whs on mt1.type_id=whs.type_id
                 left join wh_house_info whi on whs.house_id=whi.house_id
        where mt1.del_flag = 0 and mt1.level = 1
        <if test="level == 1">
            and mt1.type_id=#{typeId}
        </if>

        order by type_id
    </select>

    <update id="updateMaTypeStockNum">
        UPDATE
            ma_type
        SET
            storage_num = storage_num - #{record.outNum} ,update_time = NOW()
        WHERE
            type_id = #{record.typeId}
    </update>

    <select id="selectByTypeId" resultType="com.bonus.material.ma.domain.Type">
        select * from ma_type WHERE
            type_id = #{record.typeId}
    </select>

    <select id="getMaType" resultType="com.bonus.material.ma.domain.Type">
        select lease_price as leasePrice,
               rent_price as rentPrice,
               buy_price as buyPrice,
               pay_price as payPrice
        from ma_type
        where type_id = #{typeId} and del_flag = 0
    </select>

    <select id="getCountOfCodeMachine" resultType="java.lang.Integer">
        select count(mm.ma_id)
        FROM ma_type mt
                 left join ma_machine mm on mm.type_id=mt.type_id
        WHERE
            mm.type_id = #{record.typeId}
          and mm.ma_code is not null
          and mm.ma_status in (1)
          and mt.`level` = 4
          and mt.del_flag = '0'
    </select>

    <select id="getMachineByCt" resultType="java.lang.Integer">
        SELECT
            CASE mt.manage_type
                WHEN 0 THEN
                    IFNULL(subquery0.num, 0)
                ELSE
                    IFNULL(mt.storage_num, 0)
                END as num
        FROM ma_type mt
                 left join (SELECT mt.type_id,
                                   mt2.type_name AS             typeName,
                                   mt.type_name  AS             typeModelName,
                                   count(mm.ma_id) num
                            FROM ma_machine mm
                                     LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
                                     LEFT JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                            WHERE mm.ma_code is not null and mm.ma_status in (1)
                            GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = mt.type_id
        WHERE mt.del_flag = '0' and mt.type_id = #{typeId}
    </select>

    <delete id="deleteTypeKeeperByTypeId">
        delete from ma_type_keeper where type_id = #{typeId}
    </delete>

    <insert id="insertTypeKeeper">
        insert into ma_type_keeper(type_id, user_id) values (#{typeId},#{keeperId})
    </insert>


    <delete id="deleteTypeRepairerByTypeId">
        delete from ma_type_repair where type_id = #{typeId}
    </delete>

    <insert id="insertTypeRepairer">
        insert into ma_type_repair(type_id, user_id) values (#{typeId},#{repairerId})
    </insert>

    <insert id="addMaTypeStockNum">
        UPDATE
            ma_type
        SET
            storage_num = storage_num + #{record.outNum} ,update_time = NOW()
        WHERE
            type_id = #{record.typeId}
    </insert>

    <insert id="insertMaTypeLeasePriceHistory">
        INSERT INTO ma_type_history(type_id, before_price, after_price, create_time, create_by)
        VALUES (#{typeId}, #{beforePrice} ,#{afterPrice}, #{createTime}, #{createBy})
    </insert>

    <select id="getMaTypeHistoryListBy" resultType="com.bonus.material.ma.domain.MaTypeHistory">
        select
            mth.id, mth.type_id as typeId, mth.before_price as beforePrice, mth.after_price as afterPrice,
            mth.create_time as createTime, mth.create_by as createBy,
            mt4.type_name as maModel, mt3.type_name as maName, mt2.type_name as maType
        from
            ma_type_history mth
        left join ma_type mt4 on mt4.type_id = mth.type_id and mt4.level = 4 and mt4.del_flag = 0
        left join ma_type mt3 on mt3.type_id = mt4.parent_id and mt3.level = 3 and mt3.del_flag = 0
        left join ma_type mt2 on mt2.type_id = mt3.parent_id and mt2.level = 2 and mt2.del_flag = 0
        <where>
            <if test="typeId != null">
                and mth.type_id = #{typeId}
            </if>
            <if test="maModel != null and maModel != ''">
                and mt4.type_name like concat('%',#{maModel},'%')
            </if>
            <if test="maName != null and maName != ''">
                and mt3.type_name like concat('%',#{maName},'%')
            </if>
            <if test="maType != null and maType != ''">
                and mt2.type_name like concat('%',#{maType},'%')
            </if>

        </where>
    </select>

    <select id="selectLeasePriceByTypeId" resultType="java.math.BigDecimal">
        select lease_price from ma_type where type_id = #{typeId}
    </select>
    <select id="selectMaTypeThreeLists" resultMap="TypeResult">
        SELECT m.type_id,
               m.type_name,
               m.parent_id,
               m.manage_type,
               m.LEVEL,
               m.type_id   AS id,
               m.type_name AS label
        FROM ma_type m
        WHERE m.type_id IN (SELECT DISTINCT mt_parent.parent_id
                            FROM slt_agreement_info sai
                                     INNER JOIN ma_type mt ON mt.type_id = sai.type_id
                                     LEFT JOIN ma_type mt_parent ON (
                                        mt_parent.type_id = mt.type_id OR
                                        mt_parent.type_id = mt.parent_id OR
                                        EXISTS(
                                                SELECT 1
                                                FROM ma_type mt2
                                                WHERE mt2.type_id = mt.parent_id
                                                  AND mt_parent.type_id = mt2.parent_id
                                            )
                                )
                            WHERE sai.agreement_id = #{agreementId}
                              AND sai.STATUS = '0')
    </select>

    <select id="getListLevel" resultType="com.bonus.material.ma.domain.vo.MaTypeVo">
        SELECT
            type_id AS typeId,
            type_name AS typeName
        FROM
            ma_type
        WHERE `level` = '1'
    </select>
    <select id="getCount" resultType="java.lang.Integer">
        SELECT
            count(1)
        FROM
            ma_type
        WHERE
            del_flag = '0'
          AND parent_id = #{typeId}
    </select>
</mapper>
