<?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.StandardConfigManageMapper">
    <insert id="insertConfig">
        insert into bm_standard_config(
            `name`,
            parent_id,
            level,
            create_by,
            create_time,
            update_by,
            update_time
        )
        values(
            #{configName},
            #{parentId},
            #{level},
            #{createBy},
            #{createTime},
            #{createBy},
            #{createTime}
        )
    </insert>
    <insert id="addConfigDetails">
        insert into bm_standard_config_details(
            config_id,
            type_id,
            create_by,
            create_time,
            update_by,
            update_time
        )
        values(
            #{configId},
            #{typeId},
            #{createBy},
            #{createTime},
            #{createBy},
            #{createTime}
        )
    </insert>
    <update id="updateConfig">
        update bm_standard_config
        set
        <if test="configName != null and configName != ''">
            `name` = #{configName},
        </if>
        <if test="parentId != null and parentId != ''">
            parent_id = #{parentId},
        </if>
        <if test="level != null and level != ''">
            level = #{level},
        </if>
        update_by = #{updateBy},
        update_time = #{updateTime}
        where id = #{configId}
    </update>

    <update id="deleteConfigByConfigId">
        update bm_standard_config
        set del_flag    = 2,
            update_by   = #{updateBy},
            update_time = #{updateTime}
        where id = #{configId}
    </update>
    <update id="deleteConfigDetailByConfigId">
        update bm_standard_config_details
        set del_flag    = 2,
            update_by   = #{updateBy},
            update_time = #{updateTime}
        where config_id = #{configId}
    </update>
    <update id="updateConfigDetails">
        update bm_standard_config_details
        set
        <if test="num != null">
                num = #{num},
        </if>
        <if test="remark != null and remark != ''">
            remark = #{remark},
        </if>
        update_by = #{updateBy},
        update_time = #{updateTime}
        where id = #{detailsId}
    </update>

    <update id="delConfigDetails">
        update bm_standard_config_details
        set
        del_flag = 2,
        update_by = #{updateBy},
        update_time = #{updateTime}
        where id = #{detailsId}
    </update>


    <select id="selectStandardConfigTree" resultType="com.bonus.material.ma.domain.StandardConfigBean">
        SELECT
            bsc1.id,
            bsc1.`name` as configName,
            bsc1.parent_id as parentId,
            bsc1.level
        FROM
            bm_standard_config bsc1
        WHERE
            bsc1.del_flag = 0
    </select>
    <select id="selectParentId" resultType="java.lang.Integer">
        SELECT DISTINCT
        bsc1.id
        FROM
        bm_standard_config bsc1
        LEFT JOIN bm_standard_config bsc2 ON bsc1.parent_id = bsc2.id
        WHERE
        bsc1.id IS NOT NULL
        and bsc2.id IS NOT NULL
        <if test="level == 1">
            and bsc2.id=#{configId}
        </if>
        <if test="level == 2">
            and bsc1.id=#{configId}
        </if>
    </select>
    <select id="getListByParentId" resultType="com.bonus.material.ma.domain.vo.StandardConfigDetailsVo">
        SELECT
        bscd.id as detailsId,
        a.houseName,
        a.constructionType,
        a.materialType,
        a.typeName,
        a.typeModelName,
        a.unit,
        bscd.num,
        bscd.remark
        FROM
        bm_standard_config_details bscd
        LEFT JOIN
        (
        SELECT
        mt.type_id AS typeId,
        whi.house_name as houseName,
        mt4.type_name AS constructionType,
        mt3.type_name AS materialType,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        mt.unit_name AS unit
        FROM
        ma_type mt
        LEFT JOIN
        ma_type mt2 ON mt2.type_id = mt.parent_id
        LEFT JOIN
        ma_type mt3 ON mt3.type_id = mt2.parent_id
        LEFT JOIN
        ma_type mt4 ON mt4.type_id = mt3.parent_id
        LEFT JOIN wh_house_set whs on whs.type_id=mt4.type_id and whs.del_flag=0
        LEFT JOIN wh_house_info whi on whi.house_id=whs.house_id and whi.del_flag=0
        WHERE
        mt.`level` = 4
        AND mt.del_flag = '0'
        ) a ON a.typeId = bscd.type_id
        WHERE
        bscd.del_flag = 0
        <if test="configId != null and configId != ''">
            AND bscd.config_id = #{configId}
        </if>
        <if test="bean.keyWord != null and bean.keyWord !=''">
            AND (a.houseName like concat('%',#{bean.keyWord},'%')
            or a.constructionType like concat('%',#{bean.keyWord},'%')
            or a.materialType like concat('%',#{bean.keyWord},'%')
            or a.typeName like concat('%',#{bean.keyWord},'%')
            or a.typeModelName like concat('%',#{bean.keyWord},'%')
            or bscd.remark like concat('%',#{bean.keyWord},'%')
            )
        </if>
        order by bscd.type_id asc
    </select>

    <select id="queryByNameAndParentId" resultType="com.bonus.material.ma.domain.StandardConfigBean">
        SELECT id        AS configId,
               parent_id AS parentId,
               `name`    AS configName,
               `level`
        FROM bm_standard_config
        WHERE `name` = #{configName}
          AND parent_id = #{parentId}
          AND del_flag = '0'
    </select>
    <select id="selectByConfigId" resultType="com.bonus.material.ma.domain.vo.StandardConfigDetailsVo">
        SELECT
            config_id AS configId
        FROM
            bm_standard_config_details bscd
        WHERE
            del_flag = 0
          AND config_id = #{configId}
    </select>
    <select id="getCountById" resultType="java.lang.Integer">
        SELECT count(1) AS count
        FROM
            bm_standard_config_details bscd
        WHERE
            del_flag = 0
          AND config_id = #{configId}
          AND type_id = #{typeId}
    </select>
    <select id="getConfigLevelTwoList" resultType="com.bonus.material.ma.domain.StandardConfigBean">
        SELECT
            bsc.id AS configId,
            bsc.`name` AS configName,
            bsc.parent_id AS parentId,
            bsc.`level` AS level
        FROM
            bm_standard_config bsc
        WHERE
            bsc.del_flag = 0
          AND bsc.`level` = 2
    </select>
    <select id="getListsByConfigId" resultType="com.bonus.material.ma.domain.vo.StandardConfigDetailsVo">
        select *
        from (SELECT mt1.type_id     as typeId,
                     mt1.manage_type as manageType,
                     mt2.type_name   as maTypeName,
                     mt1.type_name   as typeName,
                     mt1.unit_name   as unitName,
                     bscd.num        as preNum,
                     bscd.del_flag   as delFlag,
                     CASE mt1.manage_type
                         WHEN 0 THEN
                             IFNULL(subquery0.num, 0)
                         ELSE
                             IFNULL(mt1.storage_num, 0)
                         END         as storageNum
              FROM bm_standard_config_details bscd
                       LEFT JOIN ma_type mt1 on mt1.type_id = bscd.type_id and mt1.del_flag = 0
                       LEFT JOIN ma_type mt2 ON mt2.type_id = mt1.parent_id and mt2.del_flag = 0
                       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 = mt1.type_id
              WHERE bscd.config_id = #{configId}) a
        where  a.delFlag = '0'
    </select>
</mapper>
