<?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 /* 물건번호 */
            , CONVERT(VARCHAR(10), A.LAST_EDIT_DATETIME, 121) AS 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
            , B1.AREA_ID  /* 일사량 관측지점 */
            , (SELECT AREA_NAME FROM M_PREFECTURE_AREA WHERE AREA_ID = B1.AREA_ID ) AS AREA_NAME
        FROM T_PLAN A WITH (NOLOCK)
        INNER JOIN T_PLAN_INFO A1 WITH (NOLOCK)
            ON A.OBJECT_NO = A1.OBJECT_NO
            AND A.PLAN_NO = A1.PLAN_NO
        INNER JOIN T_OBJECT B WITH (NOLOCK)
            ON A.OBJECT_NO = B.OBJECT_NO
        INNER JOIN T_OBJECT_INFO B1 WITH (NOLOCK)
            ON B.OBJECT_NO = B1.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 B1.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_SURFACE_ID
             , 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_PART_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
             , C.ITEM_NO
             , 1 AS AMOUNT
             , D.CNV_EFF
             , D.AMP
             , C.PNOW_W AS SPECIFICATION
        FROM T_PART_CIRCUIT_ITEM_ESTIMATE B WITH (NOLOCK)
        INNER JOIN M_ITEM C WITH (NOLOCK)
            ON B.ITEM_ID = C.ITEM_ID
        LEFT OUTER JOIN M_ITEM_INFO D WITH (NOLOCK)
            ON C.ITEM_ID = D.ITEM_ID
        WHERE C.POWER_COM_FLG ='1' /* PCS만 */
          AND B.OBJECT_NO = #{objectNo}
          AND B.PLAN_NO = #{planNo}
        ORDER BY C.PNOW_W
        /* TODO :: 직렬,병렬 조건 추가필요 */
    </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 T.*
        FROM (
            SELECT
                  A.ROOF_SURFACE_ID
                 , B.ITEM_ID
                 , SUM(B.AMOUNT) AS AMOUNT
                 , B.ITEM_NO
                 , ISNULL((SUM(B.AMOUNT) * TRY_CAST(MAX(B.SPECIFICATION) AS FLOAT)),0) AS TOT_SPECIFICATION /* 용량 */
                 , MAX(C.ITEM_GROUP) AS ITEM_GROUP /* 추후 지붕재테이블로 봐야함. */
                 , MAX(D.TEMP_LOSS) AS TEMP_LOSS
                 , MAX(D.TEMP_COEFF ) AS TEMP_COEFF
                 , MAX(D.CNV_EFF) AS CNV_EFF
                 , MAX(D.AMP) AS AMP
                 , MAX(B.SPECIFICATION) AS SPECIFICATION
                 , A.ROOF_SURFACE AS ROOF_SURFACE
                 , A.CLASS_TYPE AS CLASS_TYPE
                 , A.AZIMUTH AS AZIMUTH
                 , (CASE
                        WHEN A.CLASS_TYPE = 0 THEN A.SLOPE
                        ELSE A.ANGLE END) AS SLOPE_ANGLE
                 , (CASE
                        WHEN A.CLASS_TYPE = 0 THEN CAST(CAST(A.SLOPE AS INT)AS VARCHAR) + '寸'
                        ELSE CAST(CAST(A.ANGLE AS INT)AS VARCHAR) + N'º' END) AS SLOPE_ANGLE_TXT
            FROM  T_PART_ROOF_ESTIMATE A WITH (NOLOCK)
            INNER JOIN T_PART_ROOF_ITEM_ESTIMATE B WITH (NOLOCK)
                ON A.OBJECT_NO = B.OBJECT_NO
                AND A.PLAN_NO = B.PLAN_NO
                AND A.ROOF_SURFACE_ID = B.ROOF_SURFACE_ID
            INNER JOIN M_ITEM C WITH (NOLOCK)
                ON B.ITEM_ID = C.ITEM_ID
            LEFT OUTER JOIN M_ITEM_INFO D WITH (NOLOCK)
                ON C.ITEM_ID = D.ITEM_ID
            WHERE A.OBJECT_NO = #{objectNo}
            AND A.PLAN_NO = #{planNo}
            AND C.ITEM_GROUP = #{itemGroup}
            GROUP BY A.ROOF_SURFACE_ID, A.ROOF_SURFACE, A.CLASS_TYPE, A.AZIMUTH, A.ANGLE, A.SLOPE, B.ITEM_ID, B.ITEM_NO
        ) T
        ORDER BY T.ROOF_SURFACE_ID ASC, T.ITEM_ID ASC
    </select>
</mapper>