<?xml version="1.0" encoding="utf-8"?>
<sql-resource key="nhssysinq-sql">
  <!-- 임시 SELECT 문 공통코드 테이블에서 조회로 변경해야함. -->
  <query name="SEL_CALENDAR_DATE">
  <![CDATA[  	
   SELECT DT AS SYS_DATE   FROM  MST_CLNDR
	WHERE 1 = 1
	  AND CMP_CD  =  ? 
	  AND DT BETWEEN ?  AND  ?
	ORDER BY DT
  ]]>
  </query>
  
  <!-- 임시 SELECT 문 공통코드 테이블에서 조회로 변경해야함. -->
  <query name="SEL_SYSTEM_DATE">
  <![CDATA[
  	SELECT TO_CHAR(FN_GET_SYSDATE(?)- ?,'YYYYMMDD')  AS SYS_DATE FROM DUAL
  ]]>
  </query>
  
  <!-- 고객구매이력 -->
  <query name="SEL_CUST_BUY_HIST">
  <![CDATA[
	SELECT A.ITEM_CD
	FROM NHS_CUST_BUY_HIST A, NHS_PRD_MST B
	WHERE A.ITEM_CD = B.ITEM_CD
	AND   A.CMP_CD =?
	AND   A.SALES_ORG_CD =?
	AND   A.HPC_CUST_CD LIKE ?
	AND   B.ORDER_TYPE ='B'
	GROUP BY A.ITEM_CD
	]]>
  </query>
  
   <!-- 제품변경 정보 -->
  <query name="SEL_CHANGE_ITEM_INFO">
  <![CDATA[
	SELECT A.CMP_CD,
		   A.SALES_ORG_CD,
		   A.STOR_CD,
		   A.PRD_CATE_CD,
		   A.TOUCH_KEY_CD,
		   A.ITEM_CD,
		   A.DISPLAY_ORDER,
		   A.USE_YN,
		   A.SOLDOUT_YN,				   
		   A.RECOMMAND_YN,
		   A.NEW_YN,
		   A.PRESENT_YN,
		   A.SALES_PRC,
		   A.OPTN_YN,
		   A.TOUCH_KEY_NM,
		   A.LAST_UPD_TIME,
		   B.FILE_NAME ,
		   A.REG_USER_ID,
		   A.REG_DATE,
		   A.UPD_USER_ID,
		   A.UPD_DATE
      FROM NHS_STOR_PRD_KEY A
      LEFT OUTER JOIN NHS_PRD_MST B ON  A.CMP_CD = B.CMP_CD
       AND A.SALES_ORG_CD = B.SALES_ORG_CD
       AND A.ITEM_CD = B.ITEM_CD
     WHERE A.CMP_CD  = ?
       AND A.SALES_ORG_CD = ? 
       AND A.STOR_CD  = ? 
       AND TO_CHAR(A.LAST_UPD_TIME,'YYYYMMDDHH24MISS') > ?
	]]>
  </query>
  
  <query name="INS_SEND_MSG">
  <![CDATA[
  		INSERT INTO BM_REQUEST(MSG_ID, SVC_ID, YELLOW_ID, SEND_MSG_KIND, PHONE_NO, TEMP_ID, MESSGE, RESERVE_DT,SWITCH_SEND_DIV)
  		SELECT LPAD(NVL(MAX(TO_NUMBER(MSG_ID)),0)+1,20,'0'),
  		       ?,?,?,?,?,?,?,?
  		FROM BM_REQUEST_RESULT
  ]]>
  </query>
  
  <!-- 단말기 상태 정보 업데이트 -->
  <query name="INS_DEVICE_STATUS_INFO">
  <![CDATA[
  	 MERGE INTO NHS_POS_INFO T
	      USING ( SELECT ? AS CMP_CD,
	      				 ? AS SALES_ORG_CD,
		                 ? AS STOR_CD,
		                 ? AS POS_NO,
		                 ? AS HDD_TOT,
		                 ? AS HDD_USE,
		                 ? AS MEM_TOT,
		                 ? AS MEM_USE,
		                 ? AS CPU_USE,
		                 TO_DATE(?,'YYYYMMDDHH24MISS') LAST_BOOT_TIME,
		                 TO_DATE(?,'YYYYMMDDHH24MISS') LAST_SHUTDOWN_TIME,
		                 SYSDATE AS LAST_UPD_TIME,
		                 ? REG_USER_ID,
		                 SYSDATE AS REG_DATE,
		                 ? UPD_USER_ID,
		                 SYSDATE AS UPD_DATE
	           FROM  DUAL
	       ) R
		  ON (    T.CMP_CD      = R.CMP_CD
		  	  AND T.SALES_ORG_CD     = R.SALES_ORG_CD
		      AND T.STOR_CD     = R.STOR_CD
		      AND T.POS_NO      = R.POS_NO
		       )
		WHEN MATCHED THEN
		      UPDATE SET T.HDD_TOT = R.HDD_TOT
		                ,T.HDD_USE = R.HDD_USE
		                ,T.MEM_TOT = R.MEM_TOT
		                ,T.MEM_USE = R.MEM_USE
		                ,T.CPU_USE = R.CPU_USE
		                ,T.LAST_BOOT_TIME = R.LAST_BOOT_TIME
		                ,T.LAST_SHUTDOWN_TIME = R.LAST_SHUTDOWN_TIME
		                ,T.LAST_UPD_TIME = R.LAST_UPD_TIME
		                ,T.REG_USER_ID = R.REG_USER_ID
		                ,T.REG_DATE = R.REG_DATE
		                ,T.UPD_USER_ID = R.UPD_USER_ID
		                ,T.UPD_DATE = R.UPD_DATE
		WHEN NOT MATCHED THEN
	      INSERT (
	          CMP_CD, SALES_ORG_CD, STOR_CD, POS_NO, HDD_TOT, HDD_USE, MEM_TOT, 
	          MEM_USE, CPU_USE, LAST_BOOT_TIME, LAST_SHUTDOWN_TIME, LAST_UPD_TIME, 
	          REG_USER_ID, REG_DATE, UPD_USER_ID ,UPD_DATE
	      )
	      VALUES ( 
	          R.CMP_CD, R.SALES_ORG_CD, R.STOR_CD, R.POS_NO, R.HDD_TOT, R.HDD_USE, R.MEM_TOT, 
	          R.MEM_USE, R.CPU_USE, R.LAST_BOOT_TIME, R.LAST_SHUTDOWN_TIME, R.LAST_UPD_TIME, 
	          R.REG_USER_ID, R.REG_DATE, R.UPD_USER_ID ,R.UPD_DATE
	       )
  ]]>
  </query>
  
  <!-- 전체 버전  -->
  <query name="SEL_POS_DEPLOY_MNG_2_NEW">
  <![CDATA[
 	SELECT 
	       TO_CHAR(SYSDATE,'YYYYMMDD')     AS TRANS_YMD
	     , ?       	AS STORE_CD
	     , ?		AS FILE_GROUP 
	     , ?	    AS TRANS_ID			-- 배신구분
	     , 1        AS TRANS_SEQ
	     , '0'      AS URGENT_YN
	     , TO_CHAR(SYSDATE,'YYYYMMDD') || '00001'      AS DEPLOY_VER
	     , ?    	AS TRANS_FILE_DIR
	     , ?       AS DEPLOY_FILE_NM  
	     , ''       AS OPER_YMDHMS
	     , ''		AS OPER_ID
	     , ''		AS SC_RCV_DATE
	     , ''		AS SEND_DIV 
	     , ''		AS MSG_CD
	     , TO_CHAR(SYSDATE,'YYYYMMDD')		AS APPLY_DATE
	     , TO_CHAR(SYSDATE,'HH24MI')       AS APPLY_TIME
	     , ''				AS POS_RCV_DATE
	     , ''				AS POS_APPLY_DATE
	     , TO_CHAR(SYSDATE,'YYYYMMDD')    AS DEPLOY_DATE
	  	 , TO_CHAR(SYSDATE,'HH24MI')      AS DEPLOY_TIME
      FROM DUAL 
    ]]>
  </query> 
  
  <!-- 0:배신준비중,1:배신생성중,2:배신준비완료,3:배신완료,4:다운완료,5:적용완료,6:다운실패,7:적용실패,D:배포성공(SC),C:접속실패(SC) -->
  <!--  DOWN 완료 되면 해당일자  미DOWN 항목도 업데이트 -->
  <query name="UPD_POS_DEPLOY_MNG_NEW_1">
  <![CDATA[   
     UPDATE WAT_DEPLOY_STATUS
        SET WORK_DIV     = ?
          , WORK_DATE     = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )		--배포일시
          , POS_RCV_DATE  = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )		--POS 적용일시
      WHERE 1= 1
      	AND DEPLOY_DT 	= ? 
        AND CMP_CD  	= ?
        AND STOR_CD  	= ?
        AND POS_NO    	= ?
        AND DEPLOY_DIV  = ?			--FILE_GROUP 
        AND FILE_DIV  = ?			-- FILE_DIV
        AND SEQ_NO 		= ?		--
      	AND FILE_VER    = ?
    ]]>
  </query>
  
  <!-- 0:배신준비중,1:배신생성중,2:배신준비완료,3:배신완료,4:다운완료,5:적용완료,6:다운실패,7:적용실패,D:배포성공(SC),C:접속실패(SC) -->
  <query name="MER_POS_WAT_DEPLOY_STATUS_NEW">
  <![CDATA[ 

	 	MERGE INTO WAT_DEPLOY_STATUS T
	     USING ( SELECT  ? AS CMP_CD,
	                     ? AS DEPLOY_DT,
	                     ? AS DEPLOY_DIV,
	                     ? AS FILE_DIV, 
	                     ? AS SEQ_NO, 
	                     ? AS STOR_CD,
	                     ? AS POS_NO, 
	                     ? AS WORK_DIV,
	                     ? AS FILE_VER,
	                     TO_CHAR(SYSDATE,'yyyyMMddHH24MISS')   AS WORK_DATE,
	                     TO_CHAR(SYSDATE,'yyyyMMddHH24MISS')   AS POS_RCV_DATE,	 
	                     SYSDATE	AS REG_DATE
	               FROM  DUAL
	           ) R
	      ON (    T.CMP_CD 		= R.CMP_CD
	      	  AND T.DEPLOY_DT	= R.DEPLOY_DT
	      	  AND T.DEPLOY_DIV 	= R.DEPLOY_DIV
	      	  AND T.FILE_DIV    = R.FILE_DIV
	          AND T.SEQ_NO 		= R.SEQ_NO
	          AND T.STOR_CD    	= R.STOR_CD
	          AND T.POS_NO    	= R.POS_NO
	           )
	    WHEN MATCHED THEN
	          UPDATE SET
				T.WORK_DIV 			= R.WORK_DIV,
	          	T.POS_RCV_DATE      = R.POS_RCV_DATE,
	          	T.UPD_USER_ID 		= 'POSDAEMON',
	          	T.UPD_DATE		 	= SYSDATE
	          
	    WHEN NOT MATCHED THEN
          INSERT (
			  CMP_CD	, DEPLOY_DT		, DEPLOY_DIV		, FILE_DIV			, SEQ_NO			, STOR_CD			, POS_NO		, DEPLOY_SEQ, 
			  FILE_VER	, WORK_DIV		, WORK_DATE			, POS_RCV_DATE		, POS_APPLY_DATE	, SC_RCV_DATE		, 
			  SEND_DIV	, SC_APPLY_DATE	, REG_USER_ID		, REG_DATE			, UPD_USER_ID		, UPD_DATE 
          )
          VALUES ( 
              R.CMP_CD	, R.DEPLOY_DT	, R.DEPLOY_DIV		, R.FILE_DIV 		, R.SEQ_NO			, R.STOR_CD		, R.POS_NO, R.SEQ_NO,
              R.FILE_VER, R.WORK_DIV	, R.WORK_DATE		, R.POS_RCV_DATE	, ''			, ''				, 
              ''		, ''			, 'POSDAEMON'		, R.REG_DATE		, ''			, '' 
           )
           
    ]]>
  </query>  
  
  <!-- 04:배신정보 업데이트(POS배신결과전송용) -->
  <!-- 0:배신준비중,1:배신생성중,2:배신준비완료,3:배신완료,4:다운완료,5:적용완료,6:다운실패,7:적용실패,D:배포성공(SC),C:접속실패(SC) -->
  <query name="UPD_POS_DEPLOY_MNG_0">
  <![CDATA[ 
     UPDATE POS_DEPLOY_MNG
        SET DEPLOY_WORK_DIV      = ?
          , DEPLOY_DATE    		= FN_DATETIME2STR()
      WHERE DEPLOY_DT 	= ?
        AND CMP_CD  	= ?
        AND STOR_CD  	= ?
        AND POS_NO    	= ?
        AND DEPLOY_DIV  = ?
        AND DEPLOY_VER 	<= ? 
    ]]>
  </query>
  
    <!-- 04:배신정보 업데이트(POS적용완료결과전송용) 5  -->
  <query name="UPD_POS_DEPLOY_MNG_NEW_2">
  <![CDATA[   
     UPDATE WAT_DEPLOY_STATUS
        SET WORK_DIV        = ?
          , FILE_VER        = ?
          , WORK_DATE     	= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )	-- 배포 일시
          , POS_APPLY_DATE 	= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )	-- POS적용일시
      WHERE 1= 1
      	AND DEPLOY_DT 	= ?  
        AND CMP_CD  	= ?
        AND STOR_CD  	= ?
        AND POS_NO    	= ?
        AND DEPLOY_DIV  = ?
        AND FILE_DIV    = ?			-- FILE_DIV
        AND SEQ_NO		= ?
      	AND FILE_VER    = ?
    ]]>
  </query>
  
    <!-- 04:배신정보 업데이트(POS적용완료결과전송용) 5  -->
  <!-- 0:배신준비중,1:배신생성중,2:배신준비완료,3:배신완료,4:다운완료,5:적용완료,6:다운실패,7:적용실패,D:배포성공(SC),C:접속실패(SC) -->
  <query name="UPD_POS_DEPLOY_MNG_NEW_3">
  <![CDATA[   
     UPDATE WAT_DEPLOY_STATUS
        SET WORK_DIV         = ?
          , WORK_DATE     	= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )	-- 배포 일시
          , POS_APPLY_DATE 	= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS' )	-- POS적용일시
      WHERE  1= 1
      	AND DEPLOY_DT 	= ?
        AND CMP_CD  	= ?
        AND STOR_CD  	= ?
        AND POS_NO    	= ?
        AND DEPLOY_DIV  = ?
        AND FILE_DIV    = ?			-- FILE_DIV
        AND SEQ_NO		= ?
      	AND FILE_VER    = ?
    ]]>
  </query>
  
</sql-resource>	  