<?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.interplug.qcast.biz.displayItem.DisplayItemMapper">

    <insert id="setStoreDisplayItemSave" parameterType="com.interplug.qcast.biz.displayItem.dto.DisplayItemRequest" >
    /* sqlid : com.interplug.qcast.displayItem.setStoreDisplayItemSave */
        MERGE M_SALES_STORE_DISP_ITEM AS A
        USING
            ( SELECT #{dispTypeCd} AS DISP_TYPE_CD
                    , #{saleStoreId} AS SALE_STORE_ID
                    , #{itemId} AS ITEM_ID
            ) AS D
            ON (
                A.DISP_TYPE_CD = D.DISP_TYPE_CD
                AND A.SALE_STORE_ID = D.SALE_STORE_ID
                AND A.ITEM_ID = D.ITEM_ID
            )
        WHEN MATCHED THEN
            UPDATE SET
                  START_DATE = #{startDate}
                , END_DATE = #{endDate}
                , DEL_FLG = CASE WHEN #{delFlg} = 'FALSE' THEN 0 ELSE 1 END
                , LAST_EDIT_DATETIME = #{lastEditDatetime}
        WHEN NOT MATCHED THEN
            INSERT (
                  SALE_STORE_ID
                , DISP_TYPE_CD
                , ITEM_ID
                , START_DATE
                , END_DATE
                , DEL_FLG
                , REGIST_DATETIME
                , LAST_EDIT_DATETIME
            ) VALUES (
                  #{saleStoreId}
                , #{dispTypeCd}
                , #{itemId}
                , #{startDate}
                , #{endDate}
                , CASE WHEN #{delFlg} = 'FALSE' THEN 0 ELSE 1 END
                , #{registDatetime}
                , #{lastEditDatetime}
            );
    </insert>

    <select id="getItemList" parameterType="com.interplug.qcast.biz.displayItem.dto.ItemRequest" resultType="com.interplug.qcast.biz.displayItem.dto.ItemResponse" >
        /* sqlid : com.interplug.qcast.displayItem.getItemList */
        SELECT TT.*
        FROM (
            SELECT  /* 전체 목록중 미표시 제품 제외 */
                  MI.ITEM_ID
                , MI.ITEM_NO
                , MI.ITEM_NAME
                , MI.DISP_ORDER
                , CASE WHEN MI.POWER_COM_FLG = '1' THEN 'PC_'
                    WHEN MI.ITEM_GROUP = 'PC_' AND MI.POWER_COM_FLG = '0' THEN 'STORAGE_BATTERY'
                    ELSE MI.ITEM_GROUP END AS ITEM_GROUP
            FROM M_ITEM MI
            LEFT OUTER JOIN M_ITEM_INFO MII
                ON MI.ITEM_ID = MII.ITEM_ID
            LEFT OUTER JOIN M_SALES_STORE_DISP_ITEM SSDI
                ON MI.ITEM_ID = SSDI.ITEM_ID
                AND SSDI.SALE_STORE_ID = #{saleStoreId}
                AND SSDI.DISP_TYPE_CD = 'DISP002' /* 미표시 */
                AND dbo.fn_fmtdDate(GETDATE(), 'YYYYMMDD') BETWEEN SSDI.START_DATE AND SSDI.END_DATE
                AND SSDI.DEL_FLG = 0
            WHERE SSDI.ITEM_ID IS NULL
            AND MI.DEL_FLG = 0
            AND MI.LIMITED_RELEASE_FLG = 0
            <if test='coldZoneFlg != null and coldZoneFlg == "1"'>
                AND  MII.COLD_ZONE_FLG =  #{coldZoneFlg}
            </if>
            <if test='saltAffectedFlg != null and saltAffectedFlg == "1"'>
                AND  MII.SALT_AFFECTED_FLG =  #{saltAffectedFlg}
            </if>

            UNION ALL

            SELECT /* 미표시 제품 중 등록된 표시 제품 표시 */
                  MI.ITEM_ID
                , MI.ITEM_NO
                , MI.ITEM_NAME
                , MI.DISP_ORDER
                , CASE WHEN MI.POWER_COM_FLG = '1' THEN 'PC_'
                    WHEN MI.ITEM_GROUP = 'PC_' AND MI.POWER_COM_FLG = '0' THEN 'STORAGE_BATTERY'
                    ELSE MI.ITEM_GROUP END AS ITEM_GROUP
            FROM M_SALES_STORE_DISP_ITEM SSDI
            INNER JOIN M_ITEM MI
                ON MI.ITEM_ID = SSDI.ITEM_ID
                AND MI.LIMITED_RELEASE_FLG = 1
                AND MI.DEL_FLG = 0
            LEFT OUTER JOIN M_ITEM_INFO MII
                ON MI.ITEM_ID = MII.ITEM_ID
            WHERE SSDI.SALE_STORE_ID = #{saleStoreId}
                AND SSDI.DISP_TYPE_CD = 'DISP001' /* 표시 */
                AND dbo.fn_fmtdDate(GETDATE(), 'YYYYMMDD') BETWEEN SSDI.START_DATE AND SSDI.END_DATE
                AND SSDI.DEL_FLG = 0
                <if test='coldZoneFlg != null and coldZoneFlg == "1"'>
                    AND  MII.COLD_ZONE_FLG =  #{coldZoneFlg}
                </if>
                <if test='saltAffectedFlg != null and saltAffectedFlg == "1"'>
                    AND  MII.SALT_AFFECTED_FLG =  #{saltAffectedFlg}
                </if>
         ) TT
        ORDER BY TT.DISP_ORDER
    </select>

    <select id="getItemDetail" parameterType="String" resultType="com.interplug.qcast.biz.displayItem.dto.ItemDetailResponse" >
        /* sqlid : com.interplug.qcast.displayItem.getItemDetail */
        SELECT
              MI.ITEM_ID
            , MI.ITEM_NO
            , MI.ITEM_NAME
            , MI.GOODS_NO
            , MI.UNIT
            , MI.PNOW_W AS SPECIFICATION
            , MI.PNOW_W
            , CASE WHEN MI.POWER_COM_FLG = '1' THEN 'PC_'
                   WHEN MI.ITEM_GROUP = 'PC_' AND MI.POWER_COM_FLG = '0' THEN 'STORAGE_BATTERY'
                   ELSE MI.ITEM_GROUP END AS ITEM_GROUP
            , MI.MODULE_FLG
            , MII.PKG_MATERIAL_FLG
            , MII.FILE_UPLOAD_FLG
            , MII.ITEM_CTG_GR
            , ISNULL(MPPM.SALE_PRICE, '0') AS SALE_PRICE
            , CASE WHEN MPPM.SALE_PRICE = 0 THEN '1' ELSE '0' END AS OPEN_FLG
        FROM M_ITEM MI
        LEFT OUTER JOIN M_ITEM_INFO MII
            ON MI.ITEM_ID = MII.ITEM_ID
        LEFT OUTER JOIN M_PRICE_PATTERN_MONEY MPPM
            ON MPPM.PRICE_PATTERN = '510'
            AND MI.ITEM_ID = MPPM.ITEM_ID
        WHERE MI.DEL_FLG = 0
        AND MI.ITEM_ID = #{itemId}
    </select>

    <select id="selectItemBomList" parameterType="String" resultType="com.interplug.qcast.biz.displayItem.dto.ItemResponse" >
        /* sqlid : com.interplug.qcast.displayItem.selectItemBomList */
        SELECT
              PI.ITEM_ID
            , PI.AMOUNT AS BOM_AMOUNT
            , MI.ITEM_NO
            , MI.ITEM_NAME
            , MI.GOODS_NO
            , MI.UNIT
            , MI.PNOW_W AS SPECIFICATION
            , MI.PNOW_W
            , CASE WHEN MI.POWER_COM_FLG = '1' THEN 'PC_'
                   WHEN MI.ITEM_GROUP = 'PC_' AND MI.POWER_COM_FLG = '0' THEN 'STORAGE_BATTERY'
                   ELSE MI.ITEM_GROUP END AS ITEM_GROUP
            , MI.MODULE_FLG
            , MII.PKG_MATERIAL_FLG
            , MII.FILE_UPLOAD_FLG
            , MII.ITEM_CTG_GR
            , '0' AS SALE_PRICE
            , '0' AS OPEN_FLG
        FROM M_PACKAGE_ITEM PI WITH (NOLOCK)
        INNER JOIN M_ITEM MI WITH (NOLOCK)
            ON PI.ITEM_ID = MI.ITEM_ID
        LEFT OUTER JOIN M_ITEM_INFO MII
            ON MI.ITEM_ID = MII.ITEM_ID
        WHERE PI.PACKAGE_ITEM_ID = #{itemId}
            AND MI.DEL_FLG = 0
    </select>

    <insert id="setItemSyncSave" parameterType="com.interplug.qcast.biz.displayItem.dto.ItemSyncResponse" >
        /* sqlid : com.interplug.qcast.displayItem.setItemSyncSave */
        MERGE M_ITEM AS A
            USING
            ( SELECT #{itemId} AS ITEM_ID
            ) AS D
            ON (
            A.ITEM_ID = D.ITEM_ID
            )
            WHEN MATCHED THEN
        UPDATE SET
            ITEM_NAME = #{itemName}
            , ITEM_NO = #{itemNo}
            , GOODS_NO = #{goodsNo}
            , UNIT = #{unit}
            , SPECIFICATION = #{specification}
            , FIXED_PRICE = #{fixedPrice}
            , PNOW_W = #{pnowW}
            , ITEM_GROUP = #{itemGroup}
            , PANEL_ORDER_FLG = CASE WHEN #{panelOrderFlg} = 'FALSE' THEN 0 ELSE 1 END 
            , STAND_ORDER_FLG = CASE WHEN #{standOrderFlg} = 'FALSE' THEN 0 ELSE 1 END
            , MODULE_FLG = CASE WHEN #{moduleFlg} = 'FALSE' THEN 0 ELSE 1 END
            , POWER_COM_FLG = CASE WHEN #{powerComFlg} = 'FALSE' THEN 0 ELSE 1 END
            , OPTION_FLG = CASE WHEN #{optionFlg} = 'FALSE' THEN 0 ELSE 1 END
            , REMARKS = #{remarks}
            , DISP_ORDER = #{dispOrder}
            , DEL_FLG = CASE WHEN #{delFlg} = 'FALSE' THEN 0 ELSE 1 END
            , LIMITED_RELEASE_FLG = CASE WHEN #{limitedReleaseFlg} = 'FALSE' THEN 0 ELSE 1 END
            , YANEGI_FLG = CASE WHEN #{yanegiFlg} = 'FALSE' THEN 0 ELSE 1 END
            , HORY_FLG = CASE WHEN #{horyFlg} = 'FALSE' THEN 0 ELSE 1 END
            , TAKASIMA_FLG = CASE WHEN #{takasimaFlg} = 'FALSE' THEN 0 ELSE 1 END
            , D_SWAT_FLG = CASE WHEN #{dSwatFlg} = 'FALSE' THEN 0 ELSE 1 END
            , POWER_COM_HAVE_FLG = CASE WHEN #{powerComHaveFlg} = 'FALSE' THEN 0 ELSE 1 END
            , POWER_COM_NON_FLG = CASE WHEN #{powerComNonFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CONNECT_BOX_HAVE_FLG = CASE WHEN #{connectBoxHaveFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CONNECT_BOX_NON_FLG = CASE WHEN #{connectBoxNonFlg} = 'FALSE' THEN 0 ELSE 1 END
            , METAL_FITTING_DETAIL_FLG = CASE WHEN #{metalFittingDetailFlg} = 'FALSE' THEN 0 ELSE 1 END
            , METAL_FITTING_ALL_FLG = CASE WHEN #{metalFittingAllFlg} = 'FALSE' THEN 0 ELSE 1 END
            , STAND_FLG = CASE WHEN #{standFlg} = 'FALSE' THEN 0 ELSE 1 END
            , SECURE_TARGET_FLG = CASE WHEN #{secureTargetFlg} = 'FALSE' THEN 0 ELSE 1 END
            , DISP_CONTAINER_FLG = CASE WHEN #{dispContainerFlg} = 'FALSE' THEN 0 ELSE 1 END
            , PANEL_LAYOUT_DESIGN_DISP_FLG = CASE WHEN #{panelLayoutDesignDispFlg} = 'FALSE' THEN 0 ELSE 1 END
            , STAND_DISP_FLG = CASE WHEN #{standDispFlg} = 'FALSE' THEN 0 ELSE 1 END
            WHEN NOT MATCHED THEN
        INSERT (
            ITEM_ID
            , ITEM_NAME
            , ITEM_NO
            , GOODS_NO
            , UNIT
            , SPECIFICATION
            , FIXED_PRICE
            , PNOW_W
            , ITEM_GROUP
            , PANEL_ORDER_FLG
            , STAND_ORDER_FLG
            , MODULE_FLG
            , POWER_COM_FLG
            , OPTION_FLG
            , REMARKS
            , DISP_ORDER
            , DEL_FLG
            , LIMITED_RELEASE_FLG
            , YANEGI_FLG
            , HORY_FLG
            , TAKASIMA_FLG
            , D_SWAT_FLG
            , POWER_COM_HAVE_FLG
            , POWER_COM_NON_FLG
            , CONNECT_BOX_HAVE_FLG
            , CONNECT_BOX_NON_FLG
            , METAL_FITTING_DETAIL_FLG
            , METAL_FITTING_ALL_FLG
            , STAND_FLG
            , SECURE_TARGET_FLG
            , DISP_CONTAINER_FLG
            , PANEL_LAYOUT_DESIGN_DISP_FLG
            , STAND_DISP_FLG
        ) VALUES (
            #{itemId}
            , #{itemName}
            , #{itemNo}
            , #{goodsNo}
            , #{unit}
            , #{specification}
            , #{fixedPrice}
            , #{pnowW}
            , #{itemGroup}
            , CASE WHEN #{panelOrderFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{standOrderFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{moduleFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{powerComFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{optionFlg} = 'FALSE' THEN 0 ELSE 1 END
            , #{remarks}
            , #{dispOrder}
            , CASE WHEN #{delFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{limitedReleaseFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{yanegiFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{horyFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{takasimaFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{dSwatFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{powerComHaveFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{powerComNonFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{connectBoxHaveFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{connectBoxNonFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{metalFittingDetailFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{metalFittingAllFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{standFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{secureTargetFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{dispContainerFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{panelLayoutDesignDispFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{standDispFlg} = 'FALSE' THEN 0 ELSE 1 END
        );
    </insert>

    <insert id="setItemInfoSyncSave" parameterType="com.interplug.qcast.biz.displayItem.dto.ItemSyncResponse" >
        /* sqlid : com.interplug.qcast.displayItem.setItemInfoSyncSave */
        MERGE M_ITEM_INFO AS A
            USING
            ( SELECT #{itemId} AS ITEM_ID
            ) AS D
            ON (
            A.ITEM_ID = D.ITEM_ID
            )
            WHEN MATCHED THEN
        UPDATE SET
            GUAR_ITEM_GROUP = #{guarItemGroup}
            , FILE_UPLOAD_FLG = CASE WHEN #{fileUploadFlg} = 'FALSE' THEN 0 ELSE 1 END
            , SALT_AFFECTED_FLG = CASE WHEN #{saltAffectedFlg} = 'FALSE' THEN 0 ELSE 1 END
            , GUAR_EXCEPT_FLG = CASE WHEN #{guarExceptFlg} = 'FALSE' THEN 0 ELSE 1 END
            , COLD_ZONE_FLG = CASE WHEN #{coldZoneFlg} = 'FALSE' THEN 0 ELSE 1 END
            , GROSS_WT = #{grossWt}
            , PKG_MATERIAL_FLG = CASE WHEN #{pkgMaterialFlg} = 'FALSE' THEN 0 ELSE 1 END
            , ITEM_CTG_GR = #{itemCtgGr}
            , TEMP_LOSS = #{tempLoss}
            , TEMP_COEFF = #{tempCoeff}
            , CNV_EFF = #{cnvEff}
            , AMP = #{amp}
            , LAST_EDIT_DATETIME = GETDATE()
            WHEN NOT MATCHED THEN
        INSERT (
            ITEM_ID
            , ITEM_CTG_GR
            , GROSS_WT
            , GUAR_ITEM_GROUP
            , FILE_UPLOAD_FLG
            , SALT_AFFECTED_FLG
            , GUAR_EXCEPT_FLG
            , COLD_ZONE_FLG
            , PKG_MATERIAL_FLG
            , TEMP_LOSS
            , TEMP_COEFF
            , AMP
            , CNV_EFF
            , LAST_EDIT_DATETIME
        ) VALUES (
            #{itemId}
            , #{itemCtgGr}
            , #{grossWt}
            , #{guarItemGroup}
            , CASE WHEN #{fileUploadFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{saltAffectedFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{guarExceptFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{coldZoneFlg} = 'FALSE' THEN 0 ELSE 1 END
            , CASE WHEN #{pkgMaterialFlg} = 'FALSE' THEN 0 ELSE 1 END
            , #{tempLoss}
            , #{tempCoeff}
            , #{amp}
            , #{cnvEff}
            , GETDATE()
        );
    </insert>

    <delete id="deleteBomSync" parameterType="com.interplug.qcast.biz.displayItem.dto.BomSyncResponse" >
        DELETE FROM M_PACKAGE_ITEM
        WHERE
            PACKAGE_ITEM_ID = #{packageItemId}
          AND ITEM_ID = #{itemId}
    </delete>

    <insert id="setBomSyncSave" parameterType="com.interplug.qcast.biz.displayItem.dto.BomSyncResponse" >
        /* sqlid : com.interplug.qcast.displayItem.setBomSyncSave */
        MERGE M_PACKAGE_ITEM AS A
            USING
            ( SELECT #{itemId} AS ITEM_ID
                , #{packageItemId} AS PACKAGE_ITEM_ID
            ) AS D
            ON (
            A.PACKAGE_ITEM_ID = D.PACKAGE_ITEM_ID
            AND A.ITEM_ID = D.ITEM_ID
            )
            WHEN MATCHED THEN
        UPDATE SET
            AMOUNT = #{amount}
            WHEN NOT MATCHED THEN
        INSERT (
            PACKAGE_ITEM_ID
            , ITEM_ID
            , AMOUNT
            , MANUAL_FLG
        ) VALUES (
            #{packageItemId}
            , #{itemId}
            , #{amount}
            , CASE WHEN #{manualFlg} = 'FALSE' THEN 0 ELSE 1 END
        );
    </insert>

    <insert id="setPriceItemSyncSave" parameterType="com.interplug.qcast.biz.displayItem.dto.PriceItemSyncResponse" >
        /* sqlid : com.interplug.qcast.displayItem.setPriceItemSyncSave */
        MERGE M_PRICE_PATTERN_MONEY AS A
            USING
            ( SELECT #{pricePattern} AS PRICE_PATTERN
                , #{itemId} AS ITEM_ID
            ) AS D
            ON (
            A.PRICE_PATTERN = D.PRICE_PATTERN
            AND A.ITEM_ID = D.ITEM_ID
            )
            WHEN MATCHED THEN
        UPDATE SET
            SALE_PRICE = #{salePrice}
            , COST_PRICE = #{costPrice}
            WHEN NOT MATCHED THEN
        INSERT (
            PRICE_PATTERN
            , ITEM_ID
            , SALE_PRICE
            , COST_PRICE
        ) VALUES (
            #{pricePattern}
            , #{itemId}
            , #{salePrice}
            , #{costPrice}
        );
    </insert>
</mapper>
