20100128
20090720
if not exists(select * from information_schema.columns where TABLE_NAME='sl_sawonwork' and COLUMN_NAME='img_in')
BEGIN
ALTER TABLE sl_sawonwork
ADD img_in image
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_sawonwork' and COLUMN_NAME='img_out')
BEGIN
ALTER TABLE sl_sawonwork
ADD img_out image
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='sl_sawonwork' and COLUMN_NAME='cd_time')
BEGIN
ALTER TABLE sl_sawonwork
ADD cd_time varchar(3)
END
GO

UPDATE MS_CODE SET nm_code2 = nm_code3 WHERE cd_kind = '10'
GO

update SL_SAWONWORK set cd_time = MS_SAWON.cd_time_pay
from MS_SAWON 
where SL_SAWONWORK.cd_sawon = MS_SAWON.cd_sawon	  
GO

ALTER PROCEDURE POS_SAVE_SAWONWORK

	@CD_STORE	 	VARCHAR(4),
	@CD_SAWON	 	VARCHAR(6),
	@TIME_IN		VARCHAR(8),
	@NO_POS		VARCHAR(2),
	@IP_ADDRESS		VARCHAR(15),
	@WORK_KIND		VARCHAR(1),
	@R_NM_SAWON	VARCHAR(10) 	OUTPUT,
	@R_TIME_IN		VARCHAR(5) 	OUTPUT,
	@R_TIME_OUT	VARCHAR(5) 	OUTPUT,
	@IMG_IN		IMAGE,
	@IMG_OUT		IMAGE,
	@RESULT		INT 		OUTPUT

AS
BEGIN
	DECLARE	@CD_TIME		VARCHAR(3)

	SELECT @CD_TIME	= CD_TIME_PAY
	   FROM MS_SAWON
	WHERE CD_STORE	=@CD_STORE
	      AND CD_SAWON	=@CD_SAWON

	IF @WORK_KIND = 'S'
	BEGIN
		DECLARE	@LAST_IN	DATETIME

		SELECT @LAST_IN = TIME_IN
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_OUT	IS NULL

		SELECT @R_NM_SAWON = ''

		IF @LAST_IN IS NULL
		BEGIN

			SELECT @R_NM_SAWON 	=B.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), A.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), A.TIME_OUT, 108), '')
			   FROM SL_SAWONWORK A,  MS_SAWON B
			WHERE A.CD_STORE 	= B.CD_STORE                                                 
			      AND A.CD_SAWON 	= B.CD_SAWON    
			      AND A.CD_STORE	=@CD_STORE
			      AND A.CD_SAWON	=@CD_SAWON
			      AND CONVERT(VARCHAR(8), A.SETTLE_IN, 112) = CONVERT(VARCHAR(8), GETDATE(), 112)

			IF @R_TIME_IN <> ''
				RETURN

			SELECT @R_NM_SAWON 	= NM_SAWON,
				 @R_TIME_IN		= '',
				 @R_TIME_OUT	=''
			   FROM MS_SAWON                        
			WHERE CD_STORE 	=@CD_STORE                                                    
			      AND CD_SAWON	=@CD_SAWON    
		END
		ELSE
		BEGIN
			SELECT @R_NM_SAWON 	=A.NM_SAWON,                                                              
				 @R_TIME_IN		=ISNULL(CONVERT(VARCHAR(5), B.TIME_IN, 108), '') ,            
				 @R_TIME_OUT	=ISNULL(CONVERT(VARCHAR(5), B.TIME_OUT, 108), '')
			   FROM MS_SAWON A, SL_SAWONWORK B                         
			WHERE A.CD_STORE 	*= B.CD_STORE                                                 
			      AND A.CD_SAWON 	*= B.CD_SAWON                                                 
			      AND B.TIME_IN	=@LAST_IN
			      AND A.CD_STORE 	=@CD_STORE                                                    
			      AND A.CD_SAWON	=@CD_SAWON    
		END                                               
	END
	ELSE  IF @WORK_KIND = 'I'
	BEGIN

		INSERT INTO SL_SAWONWORK(	CD_STORE,
							CD_SAWON,
							TIME_IN,
							SETTLE_IN,
							NO_POS,
							IP_ADDRESS,
							IMG_IN,
							CD_TIME
						   )
					VALUES  (	
							@CD_STORE,
							@CD_SAWON,
							GETDATE(),
							GETDATE(),
							@NO_POS,
							@IP_ADDRESS,
							@IMG_IN,
							@CD_TIME
						   )
					
		IF @@ERROR >0 GOTO ERROR_RETURN

	END
	ELSE IF @WORK_KIND = 'O'
	BEGIN

		DECLARE	@TEMP		INT
		DECLARE 	@TEMP_TIME		DATETIME

		SELECT @TEMP_TIME = MAX(TIME_IN)
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON		

		SELECT @TEMP = DATEDIFF(n, TIME_IN, GETDATE() )
		   FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_IN	=@TEMP_TIME

		IF @TEMP < 30 GOTO ERROR_RETURN

		UPDATE SL_SAWONWORK SET TIME_OUT	=GETDATE(),
						   SETTLE_OUT	=GETDATE(),
						   IMG_OUT	=@IMG_OUT,
						   CD_TIME	=@CD_TIME
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND TIME_IN	=@TEMP_TIME

		IF @@ERROR >0 GOTO ERROR_RETURN

	END


-- 프로시저 정상 완료
	SELECT @RESULT =0
	RETURN

-- 실행도중 에러 발생
ERROR_RETURN:
	SELECT @RESULT = -1
	RETURN
END
GO


ALTER  PROCEDURE SAVE_SAWONWORK_SETTLE
	@CD_STORE 	    		VARCHAR(4),
	@YMD_WORK			VARCHAR(8),
	@CD_SAWON			VARCHAR(6),
	@SETTLE_IN			VARCHAR(20),
	@SETTLE_IN1			VARCHAR(20),
	@SETTLE_OUT		VARCHAR(20),
	@WORK_KIND			CHAR(1)
AS
BEGIN
	DECLARE	@CD_TIME	VARCHAR(3)

	SELECT @CD_TIME = CD_TIME_PAY
	   FROM MS_SAWON
	WHERE CD_STORE	=@CD_STORE
	      AND CD_SAWON	=@CD_SAWON

	IF @WORK_KIND = 'I' 
	BEGIN
		INSERT INTO SL_SAWONWORK(
						CD_STORE,
						CD_SAWON,
						SETTLE_IN,
						SETTLE_OUT,
						CD_TIME
					       )
				VALUES
					      (
						@CD_STORE,
						@CD_SAWON,
						@SETTLE_IN,
						@SETTLE_OUT,
						@CD_TIME
					       )
	END
	ELSE IF @WORK_KIND = 'U'
	BEGIN
		UPDATE SL_SAWONWORK SET	SETTLE_IN    =  @SETTLE_IN,
						SETTLE_OUT =  @SETTLE_OUT,
						CD_TIME	=@CD_TIME
						
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		      AND CONVERT(VARCHAR(20), SETTLE_IN, 120) = @SETTLE_IN1
	END
	ELSE IF @WORK_KIND = 'D'
	BEGIN
		DELETE FROM SL_SAWONWORK
		WHERE CD_STORE	=@CD_STORE
		      AND CD_SAWON	=@CD_SAWON
		       AND CONVERT(VARCHAR(20), SETTLE_IN, 120) = @SETTLE_IN1
	END

END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_menu' and COLUMN_NAME='cd_menu_minipos')
BEGIN
ALTER TABLE ms_menu
ADD cd_menu_minipos varchar(3)
END
GO

if not exists(select * from information_schema.columns where TABLE_NAME='ms_menu' and COLUMN_NAME='nm_menu_minipos')
BEGIN
ALTER TABLE ms_menu
ADD nm_menu_minipos varchar(16)
END
GO

ALTER   PROCEDURE SAVE_MS_MENU

	@CD_STORE 	    		VARCHAR(4),
	@CD_MENU      		VARCHAR(14),
	@NM_MENU		    	VARCHAR(50),
	@NM_MENU_SHORT	    	VARCHAR(50),
	@DS_MENU_TYPE    		VARCHAR(1),
	@DS_GENDER			VARCHAR(1),
	@CD_UPJANG			VARCHAR(4),
	@PR_TIP			INTEGER,
	@PR_SALE     			INTEGER,
	@PR_SALE1			INTEGER,
	@DS_TAX 	    		VARCHAR(1),
	@YN_DC			VARCHAR(1),
	@YN_POINT			VARCHAR(1),
	@YN_OKC			VARCHAR(1),
	@CD_CLASS 	    		VARCHAR(8),
	@YN_USE			VARCHAR(1),
	@DS_STOCK			VARCHAR(1),
	@STOCK_CLASS		VARCHAR(3),
	@NM_UNIT			VARCHAR(30),
	@CD_TRDPL			VARCHAR(6),
	@PR_BUY			INT,
	@QTY_SAFETY		INT,
	@CD_SAWON_CHG   		VARCHAR(6),
	@YN_DAMDANG		VARCHAR(1),
	@I_SW           			CHAR(1)
AS
BEGIN
    DECLARE @CNT INT

    IF @I_SW = 0
    BEGIN
		INSERT INTO  MS_MENU (	CD_STORE, 	 
						CD_MENU,     
						CD_CLASS, 	
						CD_UPJANG, 
						NM_MENU,	
						NM_MENU_SHORT,	 
						DS_MENU_TYPE,
						DS_GENDER,
						PR_TIP,	
						PR_SALE,     
						PR_SALE1,
						DS_TAX, 	
						YN_DC,    
						YN_POINT,
						YN_OKC,
						DS_STOCK,
						STOCK_CLASS,
						NM_UNIT,
						CD_TRDPL,
						PR_BUY,
						QTY_SAFETY,
						YN_USE,		
						CD_SAWON_CHG,
						YN_DAMDANG
						)

                       		 	VALUES(	@CD_STORE, 	 
						@CD_MENU,     
						@CD_CLASS, 	 
						@CD_UPJANG,
						@NM_MENU,		
						@NM_MENU_SHORT, 
						@DS_MENU_TYPE,
						@DS_GENDER,
						@PR_TIP,	
						@PR_SALE,     
						@PR_SALE1,
						@DS_TAX, 	   
						@YN_DC, 
						@YN_POINT,
						@YN_OKC,
						@DS_STOCK,
						@STOCK_CLASS,
						@NM_UNIT,
						@CD_TRDPL,
						@PR_BUY,
						@QTY_SAFETY,
						@YN_USE,		
						@CD_SAWON_CHG,
						@YN_DAMDANG
						)			

    END
    ELSE 
    BEGIN
		UPDATE  MS_MENU     SET 
						CD_CLASS		=@CD_CLASS, 	 
						CD_UPJANG		=@CD_UPJANG,
						NM_MENU		=@NM_MENU, 
						NM_MENU_SHORT        =@NM_MENU_SHORT,						
						DS_MENU_TYPE	=@DS_MENU_TYPE,
						DS_GENDER		=@DS_GENDER,
						PR_TIP		=@PR_TIP,
						PR_SALE		=@PR_SALE,     
						PR_SALE1		=@PR_SALE1,
						DS_TAX 		=@DS_TAX,    
						YN_DC			=@YN_DC,
						YN_POINT		=@YN_POINT,
						YN_OKC		=@YN_OKC,
						DS_STOCK		=@DS_STOCK,
						STOCK_CLASS		=@STOCK_CLASS,
						NM_UNIT		=@NM_UNIT,
						CD_TRDPL		=@CD_TRDPL,
						PR_BUY		=@PR_BUY,
						QTY_SAFETY		=@QTY_SAFETY,
						YN_USE		=@YN_USE,
						CD_SAWON_CHG	=@CD_SAWON_CHG,
						YN_DAMDANG		=@YN_DAMDANG,
						DT_CHANGE          	=GETDATE()
				WHERE    CD_STORE 		= @CD_STORE 
				      AND    CD_MENU 		= @CD_MENU


    END
    

	
END
GO

