393 lines
9.7 KiB
XML
393 lines
9.7 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.sample.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.ID = c.ID
|
|
AND a.BG_INFO = d.IMG_ID
|
|
AND a.CI_INFO = e.IMG_ID
|
|
ORDER BY b.CENTER_ORDER ASC
|
|
</select>
|
|
|
|
<insert id="insertWallInfo" 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>
|
|
|
|
<insert id="updateWallInfo" 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert id="deleteWallInfo" parameterType="kr.co.i4way.manage.model.WallInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_INFO
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</insert>
|
|
|
|
<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>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert id="deleteCenterInfo" parameterType="kr.co.i4way.manage.model.CenterInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_CENTER
|
|
WHERE
|
|
CENTER_ID = #{center_id}
|
|
]]>
|
|
</insert>
|
|
|
|
<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>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<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>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert id="deleteScreenInfo" parameterType="kr.co.i4way.manage.model.ScreenInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_SCREEN
|
|
WHERE ID = #{id}
|
|
]]>
|
|
</insert>
|
|
|
|
<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>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert id="deleteUserInfo" parameterType="kr.co.i4way.manage.model.UserInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_WALL_USER
|
|
WHERE
|
|
USER_ID = #{user_id}
|
|
]]>
|
|
</insert>
|
|
|
|
<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>
|
|
|
|
<insert 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}
|
|
]]>
|
|
</insert>
|
|
|
|
<insert id="deleteImageInfo" parameterType="kr.co.i4way.manage.model.ImageInfoVo">
|
|
<![CDATA[
|
|
DELETE FROM TBL_IMAGE
|
|
WHERE
|
|
IMG_ID = #{img_id}
|
|
]]>
|
|
</insert>
|
|
|
|
|
|
<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> |