<?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.canvasStatus.CanvasStatusMapper">

    <select id="getCanvasStatusNewObjectNo" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatus">
        /* sqlid : com.interplug.qcast.canvasStatus.getCanvasStatusNewObjectNo */
        SELECT #{userId} + FORMAT(GETDATE(), 'yyMMdd') + RIGHT('000' + CAST(ISNULL(MAX(CAST(RIGHT(object_no, 3) AS INT)), 0) + 1 AS VARCHAR (3)), 3) AS objectNo
        FROM TB_CANVAS_STATUS
        WHERE object_no LIKE #{userId} + FORMAT(GETDATE(), 'yyMMdd') + '%'
    </select>

    <select id="getCanvasStatusImageAdd" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatus">
        /* sqlid : com.interplug.qcast.canvasStatus.getCanvasStatusImageAdd */
        SELECT 'image' + RIGHT('00' + CAST(ISNULL(MAX(CAST(RIGHT(image_name, 2) AS INT)), 0) + 1 AS VARCHAR (2)), 2) AS imageName
        FROM TB_CANVAS_STATUS
        WHERE object_no = #{objectNo}
    </select>

    <select id="selectAllCanvasStatus" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatusResponse">
        /* sqlid : com.interplug.qcast.canvasStatus.selectAllCanvasStatus 전체 견적서 조회 */
        SELECT id
             , user_id
             , object_no
             , image_name
             , canvas_status
             , regist_datetime
             , last_edit_datetime
             , bg_image_name
             , map_position_address
        FROM TB_CANVAS_STATUS
        WHERE user_id = #{userId}
    </select>

    <select id="selectObjectNoCanvasStatus" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatusResponse">
        /* sqlid : com.interplug.qcast.canvasStatus.selectObjectNoCanvasStatus 견적서 조회 */
        SELECT id
        , user_id
        , object_no
        , image_name
        , canvas_status
        , regist_datetime
        , last_edit_datetime
        , bg_image_name
        , map_position_address
        FROM TB_CANVAS_STATUS
        WHERE object_no = #{objectNo}
        <if test="userId != null and userId != ''">
            AND user_id = #{userId}
        </if>
    </select>

    <select id="getMaxIdCanvasStatus" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatusResponse">
        /* sqlid : com.interplug.qcast.canvasStatus.getMaxIdCanvasStatus 견적서 조회(Max id) */
        SELECT MAX(id) AS id
        FROM TB_CANVAS_STATUS
        WHERE object_no = #{objectNo}
          AND user_id = #{userId}
    </select>

    <select id="getIdCanvasStatus" parameterType="integer"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatusResponse">
        /* sqlid : com.interplug.qcast.canvasStatus.getIdCanvasStatus 견적서 조회(id별) */
        SELECT id
        FROM TB_CANVAS_STATUS
        WHERE id = #{id}
    </select>

    <select id="getObjectNoCanvasStatus" parameterType="string"
            resultType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatusResponse">
        /* sqlid : com.interplug.qcast.canvasStatus.getObjectNoCanvasStatus 견적서 조회 */
        SELECT object_no
        FROM TB_CANVAS_STATUS
        WHERE object_no = #{objectNo}
    </select>

    <insert id="insertCanvasStatus" parameterType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatus">
        /* sqlid : com.interplug.qcast.canvasStatus.insertCanvasStatus 견적서 등록 */
        INSERT INTO TB_CANVAS_STATUS
        ( user_id
        , object_no
        , image_name
        , canvas_status
        , regist_datetime
        , bg_image_name
        , map_position_address)
        VALUES ( #{userId}
               , #{objectNo}
               , #{imageName}
               , #{canvasStatus}
               , GETDATE()
               , #{bgImageName}
               , #{mapPositionAddress})
    </insert>

    <update id="updateCanvasStatus" parameterType="com.interplug.qcast.biz.canvasStatus.dto.CanvasStatus">
        /* sqlid : com.interplug.qcast.canvasStatus.updateCanvasStatus 견적서 수정 */
        UPDATE TB_CANVAS_STATUS
        SET canvas_status        = #{canvasStatus}
          , bg_image_name        = #{bgImageName}
          , map_position_address = #{mapPositionAddress}
          , last_edit_datetime   = GETDATE()
        WHERE id = #{id}
    </update>

    <delete id="deleteObjectNoCanvasStatus" parameterType="string">
        /* sqlid : com.interplug.qcast.canvasStatus.deleteObjectNoCanvasStatus 견적서 삭제 */
        DELETE
        FROM TB_CANVAS_STATUS
        WHERE object_no = #{objectNo}
    </delete>

    <delete id="deleteIdCanvasStatus" parameterType="integer">
        /* sqlid : com.interplug.qcast.canvasStatus.deleteIdCanvasStatus 이미지(템플릿) 삭제 */
        DELETE
        FROM TB_CANVAS_STATUS
        WHERE id = #{id}
    </delete>

</mapper>