<?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.material.mapper.MaWholeSetMapper">
    <insert id="insertPlanNeedInfo" useGeneratedKeys="true" keyProperty="planId">
        insert into plan_need_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="unitId != null">unit_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="remark != null">remark,</if>
            <if test="companyId != null">company_id,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="unitId != null">#{unitId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="remark != null">#{remark},</if>
            <if test="companyId != null">#{companyId},</if>
            now()
        </trim>
    </insert>

    <insert id="insertPlanBorrow" useGeneratedKeys="true" keyProperty="borrowId">
        insert into plan_borrow_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="needUnitId != null">need_unit_id,</if>
            <if test="borrowUnitId != null">borrow_unit_id,</if>
            <if test="createBy != null">create_by,</if>
            <if test="remark != null">remark,</if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="needUnitId != null">#{needUnitId},</if>
            <if test="borrowUnitId != null">#{borrowUnitId},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="remark != null">#{remark},</if>
            now()
        </trim>
    </insert>

    <insert id="insert">
        <foreach item="item" index="index" collection="wholeList" separator=";">
            insert into ma_whole_set (type_id,parent_id,part_num,whole_type_name,create_by,ascription_type,create_time,status)
            values(#{item.typeId},#{item.parentId},#{item.totalNum},#{item.wholeTypeName},#{item.createBy},#{item.ascriptionType}, now(),1)
        </foreach>
    </insert>
    <update id="updateMaType">
        update ma_type
        set is_whole_set = 1
        where type_id = #{typeId}
    </update>
    <update id="updateMaType1">
        update ma_type
        set is_whole_set = null
    </update>
    <delete id="deleteById">
        delete from ma_whole_set
        where parent_id = #{id}
    </delete>

    <select id="selectByParentId" resultType="java.lang.Integer">
        select COUNT(*) from ma_whole_set
        where parent_id = #{parentId}
    </select>
    <select id="selectList" resultType="com.bonus.sgzb.material.domain.MaWholeVo">
        SELECT
        mws.parent_id AS id,
        mws.whole_type_name AS wholeTypeName,
        mws.create_time AS createTime,
        SUM(CASE WHEN mws.ascription_type = 2 THEN 1 ELSE 0 END) AS totalNum,
        mt2.type_name AS typeName,
        mt.type_name AS typeModelName,
        su.nick_name AS nickName
        FROM ma_whole_set mws
        JOIN sys_user su ON su.user_id = mws.create_by
        JOIN ma_type mt ON mws.parent_id = mt.type_id
        JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
        mt.`status` = '0'
        AND mt.del_flag = '0'
        <if test="wholeTypeName != null and wholeTypeName != ''">
            and mws.whole_type_name like concat('%', #{wholeTypeName}, '%')
        </if>
        <if test="parentId != null and parentId != ''">
            and mws.parent_id = #{parentId}
        </if>
        <if test="startTime != null and startTime != '' and endTime != null and endTime != '' ">
            and mws.create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')
        </if>
        GROUP BY mws.parent_id,mws.whole_type_name
        order by mws.create_time desc
    </select>
    <select id="selectListById" resultType="com.bonus.sgzb.material.domain.MaWholeVo">
        SELECT
            mws.parent_id AS id,
            mt.type_id as deviceTypeId,
            mt2.type_name AS typeName,
            mt.type_name AS deviceType,
            mws.whole_type_name as wholeTypeName,
            CASE mt.manage_type
                WHEN 0 THEN
                    IFNULL(subquery0.num, 0)
                ELSE
                    IFNULL(mt.num, 0)
                END as num,
            case when mws.ascription_type = 1 then '主体设备' else '配套设备' end as deviceAscription,
            mws.ascription_type as ascriptionType,
            mws.part_num AS deviceNum
        FROM
            ma_whole_set mws
                JOIN ma_type mt ON mws.type_id = mt.type_id
                left join (SELECT mt.type_id,
                                  count(mm.ma_id) num
                           FROM ma_machine mm
                                    LEFT JOIN ma_type mt ON mt.type_id = mm.type_id
                           WHERE mm.ma_code is not null and mm.ma_status in (15)
                           GROUP BY mt.type_id) AS subquery0 ON subquery0.type_id = mt.type_id
                JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
                JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
                JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
            mws.parent_id = #{id} AND
            mws.whole_type_name = #{wholeTypeName}
    </select>

    <select id="selectTypeTree" resultType="com.bonus.sgzb.material.domain.TreeSelectId">
        SELECT
          mt4.type_id AS firstId,
          mt3.type_id AS secondId,
          mt2.type_id AS threeId,
          mt.type_id AS fourId
        FROM
          ma_type mt
        JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
          mt.`status` = '0'
          AND mt.del_flag = '0'
          <if test="id != null and id !=''">
            AND mt.type_id = #{id}
          </if>
    </select>
    <select id="selectTypeId" resultType="java.lang.Integer">
        select type_id from ma_whole_set
        where parent_id = #{id}
    </select>
    <select id="select" resultType="com.bonus.sgzb.material.domain.MaWholeVo">
        SELECT
          mt.type_id AS id,
          mt2.type_name AS typeName,
          mt.type_name AS typeModelName,
          mws.part_num AS totalNum
        FROM
          ma_whole_set mws
        JOIN ma_type mt ON mws.type_id = mt.type_id
        JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
        <if test="id != null  and id != ''">
            mws.parent_id = #{id}
        </if>
    </select>
    <select id="selectId" resultType="com.bonus.sgzb.material.domain.TreeSelectId">
        SELECT
          mt4.type_id AS firstId,
          mt3.type_id AS secondId,
          mt2.type_id AS threeId,
          mt.type_id AS fourId
        FROM
          ma_type mt
        JOIN ma_type mt2 ON mt2.type_id = mt.parent_id
        JOIN ma_type mt3 ON mt3.type_id = mt2.parent_id
        JOIN ma_type mt4 ON mt4.type_id = mt3.parent_id
        WHERE
          mt.`status` = '0'
          AND mt.del_flag = '0'
          AND mt.type_id in
          <foreach item="item" collection="typeId" open="(" separator="," close=")">
            #{item}
          </foreach>
    </select>
    <select id="selectByWholeTypeName" resultType="java.lang.Integer">
        select COUNT(*) from ma_whole_set
        where whole_type_name = #{wholeTypeName}
    </select>
    <select id="selectListByWholeTypeName" resultType="com.bonus.sgzb.material.domain.LeaseApplyDetails">
        SELECT
            mt.unit_name unitName,
            mt.type_id typeId,
            mt.num,
            mt.type_name typeModelName,
            mt2.type_name typeName,
            mws2.part_num partNum
        FROM
            ma_type mt
                LEFT JOIN ma_type mt2 ON mt.parent_id = mt2.type_id
                LEFT JOIN ma_whole_set mws2 ON mws2.type_id = mt.type_id
                AND mws2.whole_type_name = #{wholeTypeName}
        WHERE
            mt.type_id IN ( SELECT mws.type_id FROM ma_whole_set mws WHERE mws.whole_type_name = #{wholeTypeName})
          AND mt.`status` = '0'
          AND mt.del_flag = '0'
    </select>
    <select id="selectAllTypeId" resultType="com.bonus.sgzb.material.domain.MaWhole">
        select type_id from ma_whole_set
    </select>
</mapper>