<?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.object.ObjectMapper">
    <select id="selectPrefList" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.PrefResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectPrefList */
        SELECT
              P.PREF_ID
            , P.PREF_NAME
        FROM M_PREFECTURE P WITH (NOLOCK)
        ORDER BY P.DISP_ORDER ASC
    </select>

    <select id="selectPrefAreaList" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.PrefResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectPrefAreaList */
        SELECT
              A.AREA_ID
            , A.PREF_ID
            , A.AREA_NAME AS PREF_NAME
        FROM M_PREFECTURE_AREA A WITH (NOLOCK)
        WHERE A.PREF_ID = #{prefId}
        ORDER BY A.AREA_ID ASC
    </select>

    <select id="selectWindSpeedList" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.WindSpeedResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectWindSpeedList */
        SELECT
              A.CITY
            , A.STANDARD_WIND_SPEED_ID
            , A.REMARKS
        FROM M_CITY_WIND_SPEED A WITH (NOLOCK)
        WHERE A.CITY = #{city}
        ORDER BY A.STANDARD_WIND_SPEED_ID ASC, A.CITY ASC
    </select>

    <select id="selectSaleStoreFirstList" resultType="com.interplug.qcast.biz.object.dto.SaleStoreResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectSaleStoreFirstList */
        SELECT
              T.*
        FROM (
            SELECT
                  CASE WHEN SS.SALE_STORE_LEVEL = '1' THEN 'Y' ELSE 'N' END AS FIRST_AGENT_YN
                , SS.SALE_STORE_ID
                , SS.SALE_STORE_NAME
                , SS.SALE_STORE_LEVEL
                , SS.FIRST_AGENT_ID
                , CASE WHEN UFA.DISP_ORDER IS NULL THEN 'B' ELSE 'A' + CAST(UFA.DISP_ORDER AS NVARCHAR) END AS PRIORITY
            FROM M_SALES_STORE SS WITH(NOLOCK)
            LEFT OUTER JOIN M_USER_FAV_SALES_STORE UFA WITH (NOLOCK)
                ON SS.SALE_STORE_ID = UFA.SALE_STORE_ID
                AND UFA.USER_ID = #{userId}
                AND UFA.DEL_FLG = '0'
            WHERE SS.APPROVE_FLG = '2'
                AND SS.DEL_FLG = '0'
                AND SS.SALE_STORE_LEVEL = '1'
        ) T
        ORDER BY T.PRIORITY ASC, T.SALE_STORE_LEVEL ASC, T.SALE_STORE_ID ASC
    </select>

    <select id="selectSaleStoreList" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.SaleStoreResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectSaleStoreList */
        /* 계층형 구조에 맞는 SALE_STORE_ID 축출 - 재귀함수 */
        WITH SALES_STORE_CTE AS (
            SELECT
                  SALE_STORE_ID
                , SALE_STORE_NAME
                , SALE_STORE_LEVEL
                , FIRST_AGENT_ID
            FROM M_SALES_STORE WITH(NOLOCK)
            WHERE APPROVE_FLG  = '2'
                AND DEL_FLG = '0'
                AND SALE_STORE_ID = #{saleStoreId}

            UNION ALL
            SELECT
                  A.SALE_STORE_ID
                , A.SALE_STORE_NAME
                , A.SALE_STORE_LEVEL
                , A.FIRST_AGENT_ID
            FROM M_SALES_STORE A WITH(NOLOCK)
            INNER JOIN SALES_STORE_CTE B
            ON A.PARENT_SALE_AGENT_ID = B.SALE_STORE_ID
            WHERE A.APPROVE_FLG  = '2'
                AND A.DEL_FLG = '0'
        )

        SELECT
              TT.*
        FROM (
            SELECT
                  T.*
                , CASE WHEN T.FIRST_AGENT_YN = 'Y' THEN 'A0'
                       WHEN UFA.DISP_ORDER IS NULL THEN 'B'
                  ELSE 'A' + CAST(UFA.DISP_ORDER AS NVARCHAR) END AS PRIORITY
            FROM (
                <if test='firstFlg != null and firstFlg == "1"'>
                SELECT
                      'Y' AS FIRST_AGENT_YN
                    , A.SALE_STORE_ID
                    , A.SALE_STORE_NAME
                    , A.SALE_STORE_LEVEL
                    , A.FIRST_AGENT_ID
                FROM M_SALES_STORE A WITH(NOLOCK)
                WHERE SALE_STORE_ID = (SELECT FIRST_AGENT_ID FROM M_SALES_STORE WHERE SALE_STORE_ID = #{saleStoreId} AND DEL_FLG = '0')
                    AND APPROVE_FLG = '2'
                    AND DEL_FLG = '0'
                UNION ALL
                </if>
                SELECT
                      CASE WHEN SALE_STORE_LEVEL = '1' THEN 'Y' ELSE 'N' END AS FIRST_AGENT_YN
                    , A.SALE_STORE_ID
                    , A.SALE_STORE_NAME
                    , A.SALE_STORE_LEVEL
                    , A.FIRST_AGENT_ID
                FROM SALES_STORE_CTE A
                <if test='firstFlg == null or firstFlg != "1"'>
                WHERE A.SALE_STORE_LEVEL <![CDATA[ <> ]]> '1'
                </if>
            ) T
            LEFT OUTER JOIN M_USER_FAV_SALES_STORE UFA WITH (NOLOCK)
                ON T.SALE_STORE_ID = UFA.SALE_STORE_ID
                AND UFA.USER_ID = #{userId}
                AND UFA.DEL_FLG = '0'
        ) TT
        ORDER BY TT.PRIORITY ASC, TT.SALE_STORE_LEVEL ASC, TT.SALE_STORE_ID ASC
    </select>

    <select id="selectSaleStoreInfo" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.SaleStoreResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectSaleStoreInfo */
        SELECT
              A.SALE_STORE_NAME
            , B.BUSINESS_CHARGER
            , B.BUSINESS_CHARGER_TEL
            , B.BUSINESS_CHARGER_MAIL
        FROM M_SALES_STORE A WITH(NOLOCK)
        LEFT JOIN M_BUSINESS_CHARGER B WITH(NOLOCK)
            ON A.BUSINESS_CHARGER_CD = B.BUSINESS_CHARGER_CD
        WHERE A.SALE_STORE_ID = #{saleStoreId}
    </select>

    <select id="selectObjectList" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest" resultType="com.interplug.qcast.biz.object.dto.ObjectResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectObjectList */

        <if test='saleStoreId != null and saleStoreId != "T01"'>
        /* 계층형 구조에 맞는 SALE_STORE_ID 축출 - 재귀함수 */
        WITH SALES_STORE_CTE AS (
            SELECT
                  SALE_STORE_ID
                , SALE_STORE_LEVEL
                , PARENT_SALE_AGENT_ID
            FROM M_SALES_STORE WITH(NOLOCK)
            WHERE APPROVE_FLG  = '2'
                AND DEL_FLG = '0'
                AND SALE_STORE_ID = #{saleStoreId}

            UNION ALL
            SELECT
                  A.SALE_STORE_ID
                , A.SALE_STORE_LEVEL
                , A.PARENT_SALE_AGENT_ID
            FROM M_SALES_STORE A WITH(NOLOCK)
            INNER JOIN SALES_STORE_CTE B
                ON A.PARENT_SALE_AGENT_ID = B.SALE_STORE_ID
            WHERE A.APPROVE_FLG  = '2'
                AND A.DEL_FLG = '0'
        )
        </if>

        SELECT
              TT.*
        FROM
        (
            SELECT
                  COUNT(1) OVER() AS TOT_CNT
                , ROW_NUMBER() OVER(
                    ORDER BY
                        <choose>
                            <when test='schSortType != null and schSortType == "R"'>
                            O.CREATE_DATETIME DESC
                            </when>
                            <otherwise>
                                O.LAST_EDIT_DATETIME DESC
                            </otherwise>
                        </choose>
                  ) AS ROW_NUMBER
                , O.OBJECT_NO
                , O.OBJECT_NAME
                , O.SALE_STORE_ID
                , O.ZIP_NO
                , O.ADDRESS
                , O.RECEIVE_USER
                , O.SPECIFICATION_CONFIRM_DATE
                , O.CREATE_DATETIME
                , O.LAST_EDIT_DATETIME
                , O.AREA_ID
                , O.STANDARD_WIND_SPEED_ID
                , O.VERTICAL_SNOW_COVER
                , O.SURFACE_TYPE
                , O.INSTALL_HEIGHT
                , O.CON_TYPE
                , O.COLD_REGION_FLG
                , O.SALT_AREA_FLG
                , ISNULL(O.TEMP_FLG, '0') AS TEMP_FLG
                , S.SALE_STORE_NAME
                , S.DISP_COMPANY_NAME
                , (SELECT NAME FROM M_USER WHERE USER_ID = O.CREATE_USER) AS CREATE_USER_NAME
                , (SELECT NAME FROM M_USER WHERE USER_ID = O.LAST_EDIT_USER) AS LAST_EDIT_USER_NAME
                , (SELECT COUNT(1) FROM T_PLAN WHERE OBJECT_NO = O.OBJECT_NO AND DEL_FLG = '0') AS PLAN_TOT_CNT
            FROM T_OBJECT O WITH (NOLOCK)
            INNER JOIN M_SALES_STORE S WITH (NOLOCK)
                ON O.SALE_STORE_ID = S.SALE_STORE_ID
            <if test='saleStoreId != null and saleStoreId != "T01"'>
            INNER JOIN SALES_STORE_CTE T
                ON S.SALE_STORE_ID = T.SALE_STORE_ID
            </if>
            WHERE O.SOURCE_ORIGIN = 'QCAST_III'
                AND (O.ORG_DEL_FLG = '0' OR (O.TEMP_FLG = '1' AND O.TEMP_DEL_FLG = '0'))
            <if test='schObjectNo != null and schObjectNo != ""'>
                AND O.OBJECT_NO LIKE '%' + #{schObjectNo} + '%'
            </if>
            <if test='schSaleStoreId != null and schSaleStoreId != ""'>
                AND O.SALE_STORE_ID LIKE '%' + #{schSaleStoreId} + '%'
            </if>
            <choose>
                <when test='schOtherSelSaleStoreId != null and schOtherSelSaleStoreId != ""'>
                    /* 2차점까지 고름 */
                    AND O.SALE_STORE_ID = #{schOtherSelSaleStoreId}
                </when>
                <when test='schSelSaleStoreId != null and schSelSaleStoreId != ""'>
                    /* 1차점까지만 고름 */
                    AND O.SALE_STORE_ID = #{schSelSaleStoreId}
                </when>
            </choose>
            <if test='schAddress != null and schAddress != ""'>
                AND O.ADDRESS LIKE '%' + #{schAddress} + '%'
            </if>
            <if test='schObjectName != null and schObjectName != ""'>
                AND O.OBJECT_NAME LIKE '%' + #{schObjectName} + '%'
            </if>
            <if test='schSaleStoreName != null and schSaleStoreName != ""'>
                AND S.SALE_STORE_NAME LIKE '%' + #{schSaleStoreName} + '%'
            </if>
            <choose>
                <when test='schSpecDateYn != null and schSpecDateYn == "Y"'>
                AND O.SPECIFICATION_CONFIRM_DATE IS NOT NULL
                </when>
                <when test='schSpecDateYn != null and schSpecDateYn == "N"'>
                AND O.SPECIFICATION_CONFIRM_DATE IS NULL
                </when>
            </choose>
            <if test='schReceiveUser != null and schReceiveUser != ""'>
                AND O.RECEIVE_USER LIKE '%' + #{schReceiveUser} + '%'
            </if>
            <if test='schDispCompanyName != null and schDispCompanyName != ""'>
                AND S.DISP_COMPANY_NAME LIKE '%' + #{schDispCompanyName} + '%'
            </if>
            <choose>
                <when test='schDateType != null and schDateType == "U" and (schFromDt != null and schFromDt != "") and (schToDt != null and schToDt != "")'>
                /* 갱신일 시작일,종료일 검색조건 */
                AND O.LAST_EDIT_DATETIME BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR(10), PARSE(#{schFromDt} AS DATE USING 'en-US')) + ' 00:00:00') AND CONVERT(DATETIME, CONVERT(VARCHAR(10), PARSE(#{schToDt} AS DATE USING 'en-US')) + ' 23:59:59')
                </when>
                <when test='schDateType != null and schDateType == "R" and (schFromDt != null and schFromDt != "") and (schToDt != null and schToDt != "")'>
                /* 등록일 시작일,종료일 검색조건 */
                AND O.CREATE_DATETIME BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR(10), PARSE(#{schFromDt} AS DATE USING 'en-US')) + ' 00:00:00') AND CONVERT(DATETIME, CONVERT(VARCHAR(10), PARSE(#{schToDt} AS DATE USING 'en-US')) + ' 23:59:59')
                </when>
            </choose>
        ) TT
        <if test='startRow != null and startRow != "" and endRow != null and endRow != ""'>
            WHERE TT.ROW_NUMBER BETWEEN #{startRow} AND #{endRow}
        </if>
        ORDER BY TT.ROW_NUMBER
    </select>

    <select id="selectObjectDetail" parameterType="String" resultType="com.interplug.qcast.biz.object.dto.ObjectResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectObjectDetail */
        SELECT
              O.OBJECT_NO
            , O.SALE_STORE_ID
            , O.PLAN_REQ_NO
            , O.WORK_NO
            , O.OBJECT_STATUS_ID
            , O.OBJECT_NAME
            , O.OBJECT_NAME_OMIT
            , O.OBJECT_NAME_KANA
            , O.ZIP_NO
            , O.PREF_ID
            , O.ADDRESS
            , O.REMARKS
            , O.SAME_OBJECT_INFO
            , O.RECEIVE_COMPANY_NAME
            , O.RECEIVE_USER
            , O.SPECIFICATION_CONFIRM_DATE
            , O.CONTENTS_PATH
            , O.AREA_ID
            , O.STANDARD_WIND_SPEED_ID
            , O.VERTICAL_SNOW_COVER
            , O.SURFACE_TYPE
            , O.INSTALL_HEIGHT
            , O.CON_TYPE
            , O.COLD_REGION_FLG
            , O.SALT_AREA_FLG
            , ISNULL(O.TEMP_FLG, '0') AS TEMP_FLG
            , O.CREATE_DATETIME
            , O.LAST_EDIT_DATETIME
            , (SELECT NAME FROM M_USER WHERE USER_ID = O.CREATE_USER) AS CREATE_USER_NAME
            , (SELECT NAME FROM M_USER WHERE USER_ID = O.LAST_EDIT_USER) AS LAST_EDIT_USER_NAME
            , S.FIRST_AGENT_ID
            , S.SALE_STORE_LEVEL
            , ISNULL(P.PREF_NAME, '') AS PREF_NAME
            , ISNULL(PA.AREA_NAME, '') AS AREA_NAME
            , (SELECT COUNT(1) FROM T_PLAN WHERE OBJECT_NO = O.OBJECT_NO AND DEL_FLG = '0' AND DRAWING_ESTIMATE_CREATE_DATE IS NOT NULL) AS ESTIMATE_TOT_CNT
        FROM T_OBJECT O WITH (NOLOCK)
        INNER JOIN M_SALES_STORE S WITH(NOLOCK)
            ON O.SALE_STORE_ID = S.SALE_STORE_ID
        LEFT OUTER JOIN M_PREFECTURE P
            ON O.PREF_ID = P.PREF_ID
        LEFT OUTER JOIN M_PREFECTURE_AREA PA
            ON O.AREA_ID = PA.AREA_ID
        WHERE O.OBJECT_NO  = #{objectNo}
            AND O.SOURCE_ORIGIN = 'QCAST_III'
            AND (O.ORG_DEL_FLG = '0' OR (O.TEMP_FLG = '1' AND O.TEMP_DEL_FLG = '0'))
    </select>

    <select id="selectObjectNo" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest" resultType="String">
        /* sqlid : com.interplug.qcast.biz.object.selectObjectNo */
        SELECT
            'R' + #{saleStoreId} + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR), 2) + RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR), 2) + RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR), 2) +
            RIGHT (
                '00' +
                CAST (
                    (
                        SELECT
                              ISNULL(MAX(NO), 1)
                        FROM T_OBJECT_NO
                        WHERE SALE_STORE_ID = #{saleStoreId}
                            AND YEAR = RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR), 2)
                            AND MONTH = RIGHT ('0' + CAST (MONTH (GETDATE()) AS VARCHAR), 2)
                            AND DAY = RIGHT ('0' + CAST (DAY (GETDATE()) AS VARCHAR), 2)
                    ) AS VARCHAR
                ), 3)
    </select>

    <select id="selectPlanList" parameterType="com.interplug.qcast.biz.object.dto.PlanRequest" resultType="com.interplug.qcast.biz.object.dto.PlanResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectPlanList */
        SELECT
              T.OBJECT_NO
            , T.PLAN_NO
            , T.CONSTRUCT_SPECIFICATION
            , T.SETUP_HEIGHT
            , T.WEATHER_POINT
            , T.ROOF_KIND_ID
            , T.SLOPE
            , T.ROOF_MATERIAL_CLASS_ID
            , T.ROOF_MATERIAL_ID
            , T.SUPPORT_METHOD_ID
            , T.MODULE_MODEL
            , T.CHARGER
            , T.ESTIMATE_VALIDITY_TERM
            , T.DECISION_PLAN
            , T.NUMBER
            , T.CAPACITY
            , T.SNOWFALL
            , T.STANDARD_WIND_SPEED_CHECK
            , T.OPTION_COVER
            , T.HANWFA_FLG
            , T.STAND_KIND_ID
            , T.STANDARD_WIND_SPEED_ID
            , T.SUPPORT_MEAKER
            , T.CONSUMPTION_TAX_ID
            , T.STATUS
            , T.PC_TYPE_NO
            , T.NORTH_ARRANGEMENT
            , T.ROOF_MATERIAL_ID_MULTI
            , T.SUPPORT_METHOD_ID_MULTI
            , T.SUPPORT_MEAKER_MULTI
            , T.DIFF_ROOF_ENABLED
            , T.ORDER_FLG
            , T.ESTIMATE_DATE
            , T.CONSTRUCT_SPECIFICATION_MULTI
        FROM T_PLAN T WITH (NOLOCK)
        WHERE T.OBJECT_NO = #{objectNo}
            AND T.DEL_FLG = '0'
    </select>

    <insert id="insertObjectNo" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.insertObjectNo */
        INSERT INTO T_OBJECT_NO
        (
              SALE_STORE_ID
            , YEAR
            , MONTH
            , DAY
            , NO
        )
        SELECT
              #{saleStoreId}
            , RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR), 2)
            , RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR), 2)
            , RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR), 2)
            , ISNULL(MAX(NO), 0) + 1 FROM T_OBJECT_NO WHERE SALE_STORE_ID = #{saleStoreId} AND YEAR = RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR), 2) AND MONTH = RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR), 2) AND DAY = RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR), 2)
    </insert>

    <insert id="insertObject" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.insertObject */
        INSERT INTO T_OBJECT
        (
              OBJECT_NO
            , SALE_STORE_ID
            , OBJECT_STATUS_ID
            , OBJECT_NAME
            , OBJECT_NAME_OMIT
            , OBJECT_NAME_KANA
            , ZIP_NO
            , PREF_ID
            , ADDRESS
            , ADDRESSEE_COMPANY_NAME
            , ADDRESSEE_COMPANY_NAME_OMIT
            , REMARKS
            , SAME_OBJECT_INFO
            , RECEIVE_USER
            , DELIVERY_HOPE_DATE
            , CONSTRUCT_SCHEDULED_DATE
            , CAR_KIND_CD
            , TRACK_KAIND
            , TRACK_10T_DELIVERY
            , TRACK_WEIGHT
            , TRACK_TIME_SPECIFY
            , FORKLIFT
            , HOUSE_CLASS_CD
            , FIRST_STORE_CHARGER
            , CONTENTS_PATH
            , DEL_FLG
            , CREATE_DATETIME
            , CREATE_USER
            , LAST_EDIT_DATETIME
            , LAST_EDIT_USER
            , EDIT_AGENCY
            , NORTH_ARRANGEMENT
            , PLAN_REQ_NO
            , AREA_ID
            , STANDARD_WIND_SPEED_ID
            , VERTICAL_SNOW_COVER
            , SURFACE_TYPE
            , INSTALL_HEIGHT
            , CON_TYPE
            , COLD_REGION_FLG
            , SALT_AREA_FLG
            , SOURCE_ORIGIN
            , TEMP_FLG
            , TEMP_DEL_FLG
            , ORG_DEL_FLG
        ) VALUES (
              #{objectNo}
            , #{saleStoreId}
            , #{objectStatusId}
            , #{objectName}
            , #{objectNameOmit}
            , #{objectNameKana}
            , #{zipNo}
            , #{prefId}
            , #{address}
            , #{addresseeCompanyName}
            , #{addresseeCompanyNameOmit}
            , #{remarks}
            , '1'
            , #{receiveUser}
            , GETDATE()
            , GETDATE()
            , '0'
            , '4'
            , '1'
            , '10'
            , '0'
            , '1'
            , '0'
            , #{receiveUser}
            , #{contentsPath}
            , #{delFlg}
            , GETDATE()
            , #{userId}
            , GETDATE()
            , #{userId}
            , '0'
            , '0'
            , #{planReqNo}
            , #{areaId}
            , #{standardWindSpeedId}
            , #{verticalSnowCover}
            , #{surfaceType}
            , #{installHeight}
            , #{conType}
            , #{coldRegionFlg}
            , #{saltAreaFlg}
            , 'QCAST_III'
            , #{tempFlg}
            , #{tempDelFlg}
            , #{orgDelFlg}
        )
    </insert>

    <insert id="insertObjectCopy" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.insertObjectCopy */
        INSERT INTO T_OBJECT
        (
              OBJECT_NO
            , SALE_STORE_ID
            , OBJECT_STATUS_ID
            , OBJECT_NAME
            , OBJECT_NAME_OMIT
            , OBJECT_NAME_KANA
            , ZIP_NO
            , PREF_ID
            , ADDRESS
            , ADDRESSEE_COMPANY_NAME
            , ADDRESSEE_COMPANY_NAME_OMIT
            , REMARKS
            , SAME_OBJECT_INFO
            , RECEIVE_USER
            , DELIVERY_HOPE_DATE
            , CONSTRUCT_SCHEDULED_DATE
            , CAR_KIND_CD
            , TRACK_KAIND
            , TRACK_10T_DELIVERY
            , TRACK_WEIGHT
            , TRACK_TIME_SPECIFY
            , FORKLIFT
            , HOUSE_CLASS_CD
            , FIRST_STORE_CHARGER
            , CONTENTS_PATH
            , DEL_FLG
            , CREATE_DATETIME
            , CREATE_USER
            , LAST_EDIT_DATETIME
            , LAST_EDIT_USER
            , EDIT_AGENCY
            , NORTH_ARRANGEMENT
            , AREA_ID
            , STANDARD_WIND_SPEED_ID
            , VERTICAL_SNOW_COVER
            , SURFACE_TYPE
            , INSTALL_HEIGHT
            , CON_TYPE
            , COLD_REGION_FLG
            , SALT_AREA_FLG
            , TEMP_FLG
            , TEMP_DEL_FLG
            , ORG_DEL_FLG
        )
        SELECT
              #{copyObjectNo} AS OBJECT_NO
            , #{saleStoreId} AS SALE_STORE_ID
            , OBJECT_STATUS_ID
            , OBJECT_NAME
            , OBJECT_NAME_OMIT
            , OBJECT_NAME_KANA
            , ZIP_NO
            , PREF_ID
            , ADDRESS
            , ADDRESSEE_COMPANY_NAME
            , ADDRESSEE_COMPANY_NAME_OMIT
            , REMARKS
            , SAME_OBJECT_INFO
            , #{receiveUser} AS RECEIVE_USER
            , GETDATE() AS DELIVERY_HOPE_DATE
            , GETDATE() AS CONSTRUCT_SCHEDULED_DATE
            , CAR_KIND_CD
            , TRACK_KAIND
            , TRACK_10T_DELIVERY
            , TRACK_WEIGHT
            , TRACK_TIME_SPECIFY
            , FORKLIFT
            , HOUSE_CLASS_CD
            , #{receiveUser} AS FIRST_STORE_CHARGER
            , #{contentsPath} AS CONTENTS_PATH
            , #{delFlg} AS DEL_FLG
            , GETDATE() AS CREATE_DATETIME
            , #{userId} AS CREATE_USER
            , GETDATE() AS LAST_EDIT_DATETIME
            , #{userId} AS LAST_EDIT_USER
            , EDIT_AGENCY
            , NORTH_ARRANGEMENT
            , AREA_ID
            , STANDARD_WIND_SPEED_ID
            , VERTICAL_SNOW_COVER
            , SURFACE_TYPE
            , INSTALL_HEIGHT
            , CON_TYPE
            , COLD_REGION_FLG
            , SALT_AREA_FLG
            , #{tempFlg} AS TEMP_FLG
            , #{tempDelFlg} AS TEMP_DEL_FLG
            , #{orgDelFlg} AS ORG_DEL_FLG
        FROM T_OBJECT O WITH (NOLOCK)
        WHERE O.OBJECT_NO = #{objectNo}
    </insert>

    <update id="updateObject" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.updateObject */
        UPDATE T_OBJECT
        SET
              SALE_STORE_ID = #{saleStoreId}
            , OBJECT_STATUS_ID = #{objectStatusId}
            , OBJECT_NAME = #{objectName}
            , OBJECT_NAME_OMIT = #{objectNameOmit}
            , OBJECT_NAME_KANA = #{objectNameKana}
            , ZIP_NO = #{zipNo}
            , PREF_ID = #{prefId}
            , ADDRESS = #{address}
            , ADDRESSEE_COMPANY_NAME = #{addresseeCompanyName}
            , ADDRESSEE_COMPANY_NAME_OMIT = #{addresseeCompanyNameOmit}
            , REMARKS = #{remarks}
            , RECEIVE_USER = #{receiveUser}
            , CONTENTS_PATH = #{contentsPath}
            , PLAN_REQ_NO = #{planReqNo}
            , AREA_ID = #{areaId}
            , STANDARD_WIND_SPEED_ID = #{standardWindSpeedId}
            , VERTICAL_SNOW_COVER = #{verticalSnowCover}
            , SURFACE_TYPE = #{surfaceType}
            , INSTALL_HEIGHT = #{installHeight}
            , CON_TYPE = #{conType}
            , COLD_REGION_FLG = #{coldRegionFlg}
            , SALT_AREA_FLG = #{saltAreaFlg}
            , LAST_EDIT_DATETIME = GETDATE()
            , LAST_EDIT_USER = #{userId}
        WHERE OBJECT_NO = #{objectNo}
    </update>

    <update id="updateObjectDelivery" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.updateObjectDelivery */
        UPDATE A SET
              A.RECEIVE_COMPANY_NAME = B.DISP_COMPANY_NAME
            , A.DELIVERY_ZIP_NO = B.DISP_ZIP_NO
            , A.DELIVERY_TARGET = B.DISP_ADDRESS
            , A.DELIVERY_TEL = B.DISP_TEL
        FROM T_OBJECT AS A
        INNER JOIN M_SALES_STORE AS B
            ON A.SALE_STORE_ID = B.SALE_STORE_ID
        WHERE A.OBJECT_NO = #{objectNo}
    </update>

    <update id="deleteObject" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.deleteObject */
        UPDATE T_OBJECT
        SET
              DEL_FLG = '1'
            , TEMP_DEL_FLG = '1'
            , LAST_EDIT_DATETIME = GETDATE()
            , LAST_EDIT_USER = #{userId}
        WHERE OBJECT_NO = #{objectNo}
    </update>

    <insert id="insertPlan" parameterType="com.interplug.qcast.biz.object.dto.PlanRequest">
        /* sqlid : com.interplug.qcast.biz.object.insertPlan*/
        <selectKey resultType="String" keyProperty="planNo" order="BEFORE">
            SELECT CAST(ISNULL(MAX(PLAN_NO), 0) + 1 AS NVARCHAR) FROM T_PLAN WHERE OBJECT_NO = #{objectNo}
        </selectKey>

        INSERT INTO T_PLAN
        (
              OBJECT_NO
            , PLAN_NO
            , CONSTRUCT_SPECIFICATION
            , SETUP_HEIGHT
            , WEATHER_POINT
            , ROOF_KIND_ID
            , SLOPE
            , ROOF_MATERIAL_CLASS_ID
            , ROOF_MATERIAL_ID
            , SUPPORT_METHOD_ID
            , MODULE_MODEL
            , CHARGER
            , ESTIMATE_VALIDITY_TERM
            , DECISION_PLAN
            , NUMBER
            , CAPACITY
            , SNOWFALL
            , STANDARD_WIND_SPEED_CHECK
            , OPTION_COVER
            , HANWFA_FLG
            , STAND_KIND_ID
            , STANDARD_WIND_SPEED_ID
            , SUPPORT_MEAKER
            , CONSUMPTION_TAX_ID
            , STATUS
            , LAST_EDIT_DATETIME
            , LAST_EDIT_USER
            , DEL_FLG
            , PC_TYPE_NO
            , NORTH_ARRANGEMENT
            , ROOF_MATERIAL_ID_MULTI
            , SUPPORT_METHOD_ID_MULTI
            , SUPPORT_MEAKER_MULTI
            , DIFF_ROOF_ENABLED
            , ORDER_FLG
        ) VALUES (
              #{objectNo}
            , #{planNo}
            , #{constructSpecification}
            , #{setupHeight}
            , #{weatherPoint}
            , #{roofKindId}
            , #{slope}
            , #{roofMaterialClassId}
            , #{roofMaterialId}
            , #{supportMethodId}
            , #{moduleModel}
            , (SELECT RECEIVE_USER FROM T_OBJECT WHERE OBJECT_NO = #{objectNo})
            , #{estimateValidityTerm}
            , CASE WHEN #{planNo} = '1' THEN '1' ELSE '0' END
            , #{number}
            , #{capacity}
            , #{snowfall}
            , #{standardWindSpeedCheck}
            , #{optionCover}
            , #{hanwfaFlg}
            , #{standKindId}
            , #{standardWindSpeedId}
            , #{supportMeaker}
            , #{consumptionTaxId}
            , #{status}
            , GETDATE()
            , #{userId}
            , #{delFlg}
            , #{pcTypeNo}
            , #{northArrangement}
            , #{roofMaterialIdMulti}
            , #{supportMethodIdMulti}
            , #{supportMeakerMulti}
            , #{diffRoofEnabled}
            , #{orderFlg}
        )
    </insert>

    <delete id="deletePlan" parameterType="com.interplug.qcast.biz.object.dto.PlanRequest">
        /* sqlid : com.interplug.qcast.biz.object.deletePlan */
        DELETE FROM T_PLAN
        WHERE OBJECT_NO = #{objectNo}
            AND PLAN_NO = #{planNo}
    </delete>

    <update id="updateObjectNoChange" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.updateObjectChange */
        UPDATE T_OBJECT
        SET
              OBJECT_NO = #{newObjectNo}
            , TEMP_FLG = '0'
            , TEMP_DEL_FLG = '0'
            , ORG_DEL_FLG = '0'
        WHERE OBJECT_NO = #{objectNo}
    </update>

    <update id="updatePlanObjectNoChange" parameterType="com.interplug.qcast.biz.object.dto.ObjectRequest">
        /* sqlid : com.interplug.qcast.biz.object.updatePlanObjectChange */
        UPDATE T_PLAN
        SET
              OBJECT_NO = #{newObjectNo}
        WHERE OBJECT_NO = #{objectNo}
    </update>

    <select id="selectUpload" parameterType="com.interplug.qcast.biz.object.dto.UploadRequest" resultType="com.interplug.qcast.biz.object.dto.UploadResponse">
        /* sqlid : com.interplug.qcast.biz.object.selectUpload */
        SELECT
              TOP 1
              U.OBJECT_NO
            , U.NO
            , U.FAILE_NAME
        FROM T_UPLOAD U WITH (NOLOCK)
        WHERE U.OBJECT_NO = #{objectNo}
            AND U.NO = #{no}
    </select>

</mapper>