<?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.pwrGnrSimulation.PwrGnrSimMapper">

    <select id="selectPlanInfo" parameterType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRequest"
            resultType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimPlanResponse">
        /* sqlid : com.interplug.qcast.api.pwrGnrSimulation.selectPlanInfo (견적서 정보 조회) */
        SELECT
            A.OBJECT_NO /* 물건번호 */
            , A.PLAN_NO /* 물건번호 */
            , A.DRAWING_ESTIMATE_CREATE_DATE/* 작성일 */
            , A.CAPACITY /* 시스템 용량 */
            , A.SNOWFALL/* 적설조건 */
            , ISNULL(C1.CODE_NM, '') AS STANDARD_WIND_SPEED_ID/* 풍속조건명 */
            , B.PREF_ID  /* 도도부현 */
            , (SELECT PREF_NAME FROM M_PREFECTURE WHERE PREF_ID = B.PREF_ID ) AS PREF_NAME
            , B.AREA_ID  /* 일사량 관측지점 */
            , (SELECT AREA_NAME FROM M_PREFECTURE_AREA WHERE AREA_ID = B.AREA_ID ) AS AREA_NAME
        FROM T_PLAN A WITH (NOLOCK)
        INNER JOIN T_OBJECT B WITH (NOLOCK)
            ON A.OBJECT_NO = B.OBJECT_NO
        LEFT OUTER JOIN M_COMM_L C1 WITH (NOLOCK)
            ON C1.HEAD_CD = '202000'
            AND A.STANDARD_WIND_SPEED_ID = C1.CODE
        WHERE B.AREA_ID IS NOT NULL
        AND A.DRAWING_ESTIMATE_CREATE_DATE IS NOT NULL
        AND A.OBJECT_NO = #{objectNo}
        AND A.PLAN_NO = #{planNo}
    </select>

    <select id="selectRoofList" parameterType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRequest"
            resultType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRoofResponse">
        /* sqlid : com.interplug.qcast.api.pwrGnrSimulation.selectRoofList (견적서 지붕재 정보 조회) */
        SELECT
            A.ROOF_NO
             , A.ROOF_SURFACE
             , A.CLASS_TYPE
             , A.AZIMUTH
             , (CASE WHEN A.CLASS_TYPE = 0 THEN A.SLOPE ELSE A.ANGLE END) AS SLOPE_ANGLE
        FROM  T_ROOF_ESTIMATE A WITH (NOLOCK)
        WHERE A.OBJECT_NO = #{objectNo}
          AND A.PLAN_NO = #{planNo}
    </select>

    <select id="selectRoofPcsGroupList" parameterType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRequest"
            resultType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRoofResponse">
        /* sqlid : com.interplug.qcast.api.pwrGnrSimulation.selectRoofPcsGroupList (견적서 지붕재 pcs 그룹 list 조회)  */
        SELECT
            B.ITEM_ID
             , B.ITEM_NO
             ,SUM(B.AMOUNT) AS AMOUNT
             , C.CNV_EFF
             , C.AMP
             , B.SPECIFICATION
        FROM  T_ROOF_ITEM_ESTIMATE B WITH (NOLOCK)
        INNER JOIN M_ITEM C WITH (NOLOCK)
            ON B.ITEM_ID = C.ITEM_ID
        WHERE B.OBJECT_NO = #{objectNo}
          AND B.PLAN_NO = #{planNo}
          AND C.ITEM_GROUP ='PC_'
        group by B.ITEM_ID, B.ITEM_NO, C.CNV_EFF , C.AMP ,B.SPECIFICATION
        ORDER BY B.SPECIFICATION
    </select>


    <select id="selectRoofItemList" parameterType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRequest"
            resultType="com.interplug.qcast.biz.pwrGnrSimulation.dto.PwrGnrSimRoofResponse">
        /* sqlid : com.interplug.qcast.api.pwrGnrSimulation.selectRoofItemList (견적서 지붕재 아이템 정보 조회) */
        SELECT
              A.ROOF_NO
             , B.ITEM_ID
             , B.AMOUNT
             , B.ITEM_NO
             , ((B.AMOUNT * TRY_CAST(B.SPECIFICATION AS FLOAT)) / 1000) AS TOT_SPECIFICATION /* 용량 */
             , C.ITEM_GROUP
            /* 추후 지붕재테이블로 봐야함. */
             , C.TEMP_LOSS
             , C.TEMP_COEFF
             , C.CNV_EFF
             , C.AMP
             , B.SPECIFICATION
        FROM  T_ROOF_ESTIMATE A WITH (NOLOCK)
        INNER JOIN T_ROOF_ITEM_ESTIMATE B WITH (NOLOCK)
            ON A.OBJECT_NO = B.OBJECT_NO
            AND A.PLAN_NO = B.PLAN_NO
            AND A.ROOF_NO = B.ROOF_NO
        INNER JOIN M_ITEM C WITH (NOLOCK)
            ON B.ITEM_ID = C.ITEM_ID
        WHERE A.OBJECT_NO = #{objectNo}
        AND A.PLAN_NO = #{planNo}
        AND C.ITEM_GROUP = #{itemGroup}
        ORDER BY B.SPECIFICATION DESC
    </select>
</mapper>