512 lines
13 KiB
XML
512 lines
13 KiB
XML
<?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="kr.co.i4way.manage.dao.ManageDao">
|
|
<select id="getDual" resultType="java.lang.String">
|
|
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24MISS') FROM DUAL
|
|
</select>
|
|
|
|
<select id="getCommCode" parameterType="kr.co.i4way.manage.model.CommonInfoVo" resultType="hashmap">
|
|
SELECT CODE
|
|
,P_CODE
|
|
,USE_YN
|
|
,ORDER_NO
|
|
,CODE_NAME
|
|
,CODE_DESC1
|
|
,CODE_DESC2
|
|
FROM TBL_WALL_COMMON
|
|
WHERE P_CODE = #{p_code}
|
|
<if test='use_yn != "ALL" '>
|
|
AND USE_YN = #{use_yn}
|
|
</if>
|
|
ORDER BY ORDER_NO ASC
|
|
</select>
|
|
|
|
<select id="getLoginInfo" parameterType="kr.co.i4way.manage.model.LoginVo" resultType="hashmap">
|
|
<![CDATA[
|
|
SELECT USER_ID
|
|
,USER_NAME
|
|
,PASSWD
|
|
,CENTER_ID
|
|
,CUSTOM1
|
|
,CUSTOM2
|
|
FROM TBL_WALL_USER
|
|
WHERE USER_ID = #{user_id}
|
|
AND PASSWD = #{passwd}
|
|
]]>
|
|
</select>
|
|
|
|
<select id="getWallInfo_Manage" parameterType="kr.co.i4way.manage.model.WallInfoVo" resultType="hashmap">
|
|
SELECT a.ID,
|
|
a.NAME,
|
|
a.CENTER_ID,
|
|
b.CENTER_NAME,
|
|
a.SCREEN_ID,
|
|
c.NAME AS SCREEN_NAME,
|
|
a.BG_INFO,
|
|
d.IMG_NM AS BG_NM,
|
|
d.IMG_PATH AS BG_PATH,
|
|
d.IMG_REAL_NM AS BG_REAL_NM,
|
|
a.CI_INFO,
|
|
e.IMG_NM AS CI_NM,
|
|
e.IMG_PATH AS CI_PATH,
|
|
e.IMG_REAL_NM AS CI_REAL_NM,
|
|
a.CUSTOM1,
|
|
a.CUSTOM2
|
|
FROM TBL_WALL_INFO a, TBL_WALL_CENTER b, TBL_WALL_SCREEN c, TBL_IMAGE d, TBL_IMAGE e
|
|
WHERE a.CENTER_ID = b.CENTER_ID
|
|
AND a.SCREEN_ID = c.ID
|
|
AND a.BG_INFO = d.IMG_ID
|
|
AND a.CI_INFO = e.IMG_ID
|
|
ORDER BY b.CENTER_ORDER ASC
|
|
</select>
|
|
|
|
<select id="getWallList" parameterType="kr.co.i4way.manage.model.WallInfoVo" resultType="hashmap">
|
|
SELECT a.ID AS WALL_ID
|
|
,a.NAME AS WALL_NAME
|
|
,a.CENTER_ID
|
|
,b.CENTER_NAME
|
|
,a.SCREEN_ID
|
|
,c.NAME AS SCREEN_NAME
|
|
FROM TBL_WALL_INFO a, TBL_WALL_CENTER b, TBL_WALL_SCREEN c
|
|
WHERE a.CENTER_ID = b.CENTER_ID
|
|
AND a.SCREEN_ID = c.ID
|
|
<choose>
|
|
<when test="center_id != null">
|
|
<![CDATA[
|
|
AND a.CENTER_ID IN (SELECT REGEXP_SUBSTR(#{center_id}, '[^,]+', 1, LEVEL)
|
|
FROM DUAL
|
|
CONNECT BY LEVEL <= REGEXP_COUNT(#{center_id}, ',') + 1)
|
|
]]>
|
|
</when>
|
|
<otherwise>
|
|
AND a.CENTER_ID = #{center_id}
|
|
</otherwise>
|
|
</choose>
|
|
ORDER BY a.ID ASC
|
|
</select>
|
|
|
|
<select id="getWall_Custom" parameterType="kr.co.i4way.manage.model.WallCustomVo" resultType="hashmap">
|
|
SELECT a.WALL_ID
|
|
,b.NAME AS WALL_NAME
|
|
,b.CENTER_ID
|
|
,c.CENTER_NAME
|
|
,b.SCREEN_ID
|
|
,d.NAME AS SCREEN_NAME
|
|
,a.VIEW_YN
|
|
,a.WALL_ORDER
|
|
,a.WALL_DURATION
|
|
,a.FONT_COLOR
|
|
,a.CHANGE_EFFECT
|
|
,a.READY_CUST
|
|
,a.SVC_LVL
|
|
,a.ABN_CALL
|
|
,a.ANS_RTO
|
|
,a.ETC1
|
|
,a.ETC2
|
|
,a.ETC3
|
|
,a.ETC4
|
|
,a.ETC5
|
|
FROM TBL_WALL_CUSTOM a, TBL_WALL_INFO b, TBL_WALL_CENTER c, TBL_WALL_SCREEN d
|
|
WHERE a.WALL_ID = b.ID
|
|
AND b.CENTER_ID = c.CENTER_ID
|
|
AND b.SCREEN_ID = d.ID
|
|
AND USER_ID = #{user_id}
|
|
ORDER BY WALL_ORDER ASC
|
|
</select>
|
|
|
|
<insert id="insertWall_Custom" parameterType="kr.co.i4way.manage.model.WallCustomVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_CUSTOM(ID, USER_ID, WALL_ID,VIEW_YN,WALL_ORDER,WALL_DURATION,FONT_COLOR,CHANGE_EFFECT,READY_CUST,SVC_LVL,ABN_CALL,ANS_RTO,ETC1,ETC2,ETC3,ETC4,ETC5,CUSTOM1,CUSTOM2) VALUES(
|
|
SEQ_WALL_CUSTOM.NEXTVAL
|
|
, #{user_id}
|
|
, #{wall_id}
|
|
, 'Y'
|
|
, 99
|
|
, 5
|
|
, '#000000'
|
|
, '1'
|
|
, '5'
|
|
, '90'
|
|
, '5'
|
|
, '90'
|
|
, ''
|
|
, ''
|
|
, ''
|
|
, ''
|
|
, ''
|
|
, ''
|
|
, ''
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateWall_Order" parameterType="kr.co.i4way.manage.model.WallCustomVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_CUSTOM SET
|
|
WALL_ORDER = #{wall_order}
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</update>
|
|
|
|
|
|
<insert id="insertWallInfo_Manage" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_INFO(ID, NAME, CENTER_ID, SCREEN_ID, BG_INFO, CI_INFO, CUSTOM1, CUSTOM2) VALUES(
|
|
#{id}
|
|
, #{name}
|
|
, #{center_id}
|
|
, #{screen_id}
|
|
, #{bg_info}
|
|
, #{ci_info}
|
|
, #{custom1}
|
|
, #{custom2}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateWallInfo_Manage" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_INFO SET
|
|
NAME = #{name}
|
|
,CENTER_ID = #{center_id}
|
|
,SCREEN_ID = #{screen_id}
|
|
,BG_INFO = #{bg_info}
|
|
,CI_INFO = #{ci_info}
|
|
,CUSTOM1 = #{custom1}
|
|
,CUSTOM2 = #{custom2}
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteWallInfo" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_INFO
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</delete>
|
|
|
|
<insert id="insertWallThreshold" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_THRESHOLD(WALL_ID, READY_CUST, SVC_LVL, ABN_CALL, ANS_RTO) VALUES(
|
|
#{id}
|
|
, '5'
|
|
, '80'
|
|
, '5'
|
|
, '80'
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateWallThreshold" parameterType="kr.co.i4way.manage.model.WallThresholdVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_THRESHOLD SET
|
|
REASY_CUST = #{ready_cust}
|
|
,SVC_LVL = #{svc_lvl}
|
|
,ABN_CALL = #{abn_call}
|
|
,ANS_RTO = #{ans_rto}
|
|
WHERE WALL_ID = #{wall_id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteWallThreshold" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_THRESHOLD
|
|
WHERE WALL_ID = #{wall_id}
|
|
]]>
|
|
</delete>
|
|
|
|
<select id="getCenterInfo_Manage" parameterType="kr.co.i4way.manage.model.CenterInfoVo" resultType="hashmap">
|
|
SELECT CENTER_ID
|
|
,CENTER_NAME
|
|
,CENTER_ORDER
|
|
,USE_YN
|
|
,CUSTOM1
|
|
,CUSTOM2
|
|
FROM TBL_WALL_CENTER
|
|
ORDER BY CENTER_ORDER ASC
|
|
</select>
|
|
|
|
<insert id="insertCenterInfo" parameterType="kr.co.i4way.manage.model.CenterInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_CENTER(CENTER_ID, CENTER_NAME, CENTER_ORDER, USE_YN, CUSTOM1, CUSTOM2) VALUES(
|
|
#{center_id}
|
|
, #{center_name}
|
|
, #{center_order}
|
|
, #{use_yn}
|
|
, #{custom1}
|
|
, #{custom2}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateCenterInfo" parameterType="kr.co.i4way.manage.model.CenterInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_CENTER SET
|
|
CENTER_NAME = #{center_name}
|
|
,CENTER_ORDER = #{center_order}
|
|
,USE_YN = #{use_yn}
|
|
,CUSTOM1 = #{custom1}
|
|
,CUSTOM2 = #{custom2}
|
|
WHERE
|
|
CENTER_ID = #{center_id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteCenterInfo" parameterType="kr.co.i4way.manage.model.CenterInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_CENTER
|
|
WHERE
|
|
CENTER_ID = #{center_id}
|
|
]]>
|
|
</delete>
|
|
|
|
<select id="getObjInfo_Manage" parameterType="kr.co.i4way.manage.model.ObjInfoVo" resultType="hashmap">
|
|
SELECT a.CENTER_ID
|
|
,b.CENTER_NAME
|
|
,a.OBJ_ID
|
|
,a.DBID
|
|
,a.OBJ_TYPE
|
|
,c.CODE_NAME AS OBJ_TYPE_NAME
|
|
,a.OBJ_NAME
|
|
FROM TBL_WALL_OBJ a, TBL_WALL_CENTER b, TBL_WALL_COMMON c
|
|
WHERE a.CENTER_ID = b.CENTER_ID
|
|
AND a.OBJ_TYPE = c.CODE
|
|
AND c.P_CODE = 'A001'
|
|
ORDER BY b.CENTER_ORDER, a.OBJ_NAME ASC
|
|
</select>
|
|
|
|
<insert id="insertObjInfo" parameterType="kr.co.i4way.manage.model.ObjInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_OBJ(CENTER_ID, OBJ_ID, DBID, OBJ_TYPE, OBJ_NAME, CUSTOM1, CUSTOM2) VALUES(
|
|
#{center_id}
|
|
, #{obj_id}
|
|
, #{dbid}
|
|
, #{obj_type}
|
|
, #{obj_name}
|
|
, #{custom1}
|
|
, #{custom2}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateObjInfo" parameterType="kr.co.i4way.manage.model.ObjInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_OBJ SET
|
|
DBID = #{dbid}
|
|
,OBJ_TYPE = #{obj_type}
|
|
,OBJ_NAME = #{obj_name}
|
|
,CUSTOM1 = #{custom1}
|
|
,CUSTOM2 = #{custom2}
|
|
WHERE CENTER_ID = #{center_id}
|
|
AND OBJ_ID = #{obj_id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteObjInfo" parameterType="kr.co.i4way.manage.model.ObjInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_OBJ
|
|
WHERE CENTER_ID = #{center_id}
|
|
AND OBJ_ID = #{obj_id}
|
|
]]>
|
|
</delete>
|
|
|
|
<select id="getScreenInfo_Manage" parameterType="kr.co.i4way.manage.model.ScreenInfoVo" resultType="hashmap">
|
|
SELECT ID
|
|
,NAME
|
|
,URL
|
|
,CUSTOM1
|
|
,CUSTOM2
|
|
FROM TBL_WALL_SCREEN
|
|
ORDER BY ID ASC
|
|
</select>
|
|
|
|
<insert id="insertScreenInfo" parameterType="kr.co.i4way.manage.model.ScreenInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_SCREEN(ID, NAME, URL, CUSTOM1, CUSTOM2) VALUES(
|
|
#{id}
|
|
, #{name}
|
|
, #{url}
|
|
, #{custom1}
|
|
, #{custom2}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateScreenInfo" parameterType="kr.co.i4way.manage.model.ScreenInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_SCREEN SET
|
|
NAME = #{name}
|
|
,URL = #{url}
|
|
,CUSTOM1 = #{custom1}
|
|
,CUSTOM2 = #{custom2}
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteScreenInfo" parameterType="kr.co.i4way.manage.model.ScreenInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_SCREEN
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</delete>
|
|
|
|
<select id="getUserInfo_Manage" parameterType="kr.co.i4way.manage.model.UserInfoVo" resultType="hashmap">
|
|
SELECT a.USER_ID
|
|
,a.USER_NAME
|
|
,a.PASSWD
|
|
,a.CENTER_ID
|
|
,a.GRADE
|
|
,b.CODE_NAME AS GRADE_NM
|
|
,a.CUSTOM1
|
|
,a.CUSTOM2
|
|
FROM TBL_WALL_USER a, TBL_WALL_COMMON b
|
|
WHERE a.GRADE = b.CODE
|
|
AND b.P_CODE = 'A002'
|
|
ORDER BY a.USER_NAME ASC
|
|
</select>
|
|
|
|
<insert id="insertUserInfo" parameterType="kr.co.i4way.manage.model.UserInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_WALL_USER(USER_ID, USER_NAME, PASSWD, CENTER_ID, GRADE, CUSTOM1, CUSTOM2) VALUES(
|
|
#{user_id}
|
|
, #{user_name}
|
|
, #{passwd}
|
|
, #{center_id}
|
|
, #{grade}
|
|
, #{custom1}
|
|
, #{custom2}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateUserInfo" parameterType="kr.co.i4way.manage.model.UserInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_USER SET
|
|
USER_NAME = #{user_name}
|
|
,PASSWD = #{passwd}
|
|
,CENTER_ID = #{center_id}
|
|
,GRADE = #{grade}
|
|
,CUSTOM1 = #{custom1}
|
|
,CUSTOM2 = #{custom2}
|
|
WHERE
|
|
USER_ID = #{user_id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteUserInfo" parameterType="kr.co.i4way.manage.model.UserInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_USER
|
|
WHERE
|
|
USER_ID = #{user_id}
|
|
]]>
|
|
</delete>
|
|
|
|
<select id="getImageInfo_Manage" parameterType="kr.co.i4way.manage.model.ImageInfoVo" resultType="hashmap">
|
|
SELECT IMG_ID
|
|
, IMG_PATH
|
|
, IMG_FILE_NM
|
|
, IMG_REAL_NM
|
|
, IMG_TYPE
|
|
, DECODE(IMG_TYPE, 'B', '배경', 'C', 'CI') AS IMG_TYPE_NM
|
|
, IMG_NM
|
|
, CUSTOM1
|
|
FROM TBL_IMAGE
|
|
ORDER BY IMG_FILE_NM ASC
|
|
</select>
|
|
|
|
<insert id="insertImageInfo" parameterType="kr.co.i4way.manage.model.ImageInfoVo">
|
|
<![CDATA[
|
|
INSERT INTO TBL_IMAGE(IMG_ID, IMG_PATH, IMG_TYPE, IMG_FILE_NM, IMG_NM, CUSTOM1, IMG_REAL_NM) VALUES(
|
|
#{img_id}
|
|
, #{img_path}
|
|
, #{img_type}
|
|
, #{img_file_nm}
|
|
, #{img_nm}
|
|
, #{custom1}
|
|
, #{img_real_nm}
|
|
)
|
|
]]>
|
|
</insert>
|
|
|
|
<update id="updateImageInfo" parameterType="kr.co.i4way.manage.model.ImageInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_IMAGE SET
|
|
IMG_PATH = #{img_path}
|
|
,IMG_TYPE = #{img_type}
|
|
,IMG_FILE_NM = #{img_file_nm}
|
|
,IMG_NM = #{img_nm}
|
|
,CUSTOM1 = #{custom1}
|
|
,IMG_REAL_NM = #{img_real_nm}
|
|
WHERE
|
|
IMG_ID = #{img_id}
|
|
]]>
|
|
</update>
|
|
|
|
<delete id="deleteImageInfo" parameterType="kr.co.i4way.manage.model.ImageInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_IMAGE
|
|
WHERE
|
|
IMG_ID = #{img_id}
|
|
]]>
|
|
</delete>
|
|
|
|
|
|
<select id="getWallInfo" parameterType="kr.co.i4way.sample.model.WallInfoVo" resultType="hashmap">
|
|
SELECT a.ID,
|
|
a.CENTER_ID,
|
|
b.CENTER_NAME,
|
|
a.WALL_NAME,
|
|
a.BG_INFO,
|
|
c.IMG_PATH BG_PATH,
|
|
c.IMG_FILE_NM BG_FILE,
|
|
c.IMG_NM BG_NM,
|
|
d.IMG_PATH CI_PATH,
|
|
d.IMG_FILE_NM CI_FILE,
|
|
d.IMG_NM CI_NM,
|
|
a.VIEW_YN,
|
|
b.CENTER_ORDER,
|
|
a.WALL_ORDER,
|
|
a.WALL_DURATION,
|
|
a.FONT_COLOR,
|
|
a.CI_INFO,
|
|
a.WALL_URL,
|
|
a.CHANGE_EFFECT
|
|
FROM TBL_WALL_INFO a, TBL_WALL_CENTER b, TBL_IMAGE c, TBL_IMAGE d
|
|
WHERE a.CENTER_ID = b.CENTER_ID
|
|
AND a.BG_INFO = c.IMG_ID
|
|
AND a.CI_INFO = d.IMG_ID
|
|
AND a.VIEW_YN = 'Y'
|
|
<if test='center_id != "" '>
|
|
<if test="query_arry != null and query_arry.length > 0">
|
|
AND a.CENTER_ID IN
|
|
<foreach collection="query_arry" item="item" open="(" close=")" separator=",">
|
|
#{item}
|
|
</foreach>
|
|
</if>
|
|
</if>
|
|
ORDER BY b.CENTER_ORDER, a.WALL_ORDER ASC
|
|
</select>
|
|
|
|
<select id="getImages" parameterType="kr.co.i4way.manage.model.ImageInfoVo" resultType="hashmap">
|
|
SELECT IMG_ID
|
|
, IMG_PATH
|
|
, IMG_FILE_NM
|
|
, IMG_NM
|
|
, CUSTOM1
|
|
FROM TBL_IMAGE
|
|
WHERE IMG_TYPE = #{img_type}
|
|
ORDER BY IMG_FILE_NM ASC
|
|
</select>
|
|
|
|
<update id="saveWallInfo" parameterType="kr.co.i4way.sample.model.WallInfoVo">
|
|
<![CDATA[
|
|
UPDATE TBL_WALL_INFO SET
|
|
BG_INFO = #{bg_info}
|
|
,CI_INFO = #{ci_info}
|
|
WHERE
|
|
ID = #{id}
|
|
]]>
|
|
</update>
|
|
|
|
</mapper> |