FragenThread [Offi V14]

USE [ACCOUNT_DBF]
GO
/****** Object:  User [ongate]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [ongate] FOR LOGIN [ongate] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [billing]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [billing] FOR LOGIN [billing] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [account]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [account] FOR LOGIN [account] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [acc_on9@te]    Script Date: 10/28/2009 17:14:27 ******/
CREATE USER [acc_on9@te] FOR LOGIN [acc_on9@te] WITH DEFAULT_SCHEMA=[acc_on9@te]
GO
/****** Object:  Schema [acc_on9@te]    Script Date: 10/28/2009 17:14:28 ******/
CREATE SCHEMA [acc_on9@te] AUTHORIZATION [account]
GO
/****** Object:  Table [dbo].[CashHistory]    Script Date: 10/28/2009 17:14:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CashHistory](
	[account] [varchar](32) NULL,
	[beforeCash] [int] NULL,
	[afterCash] [int] NULL,
	[regdate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ACCOUNT_TBL]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL](
	[account] [varchar](32) NOT NULL,
	[password] [char](32) NOT NULL,
	[isuse] [char](1) NULL,
	[member] [char](1) NULL,
	[id_no1] [char](6) NULL,
	[id_no2] [char](255) NULL,
	[realname] [char](1) NULL,
	[reload] [char](1) NULL,
	[cash] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [ACCOUNT_ID1] ON [dbo].[ACCOUNT_TBL] 
(
	[account] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'123', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT_TBL', @level2type=N'COLUMN',@level2name=N'account'
GO
EXEC sys.sp_addextendedproperty @name=N'123', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ACCOUNT_TBL', @level2type=N'COLUMN',@level2name=N'password'
GO
/****** Object:  Table [dbo].[ACCOUNT_TBL_DETAIL]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL_DETAIL](
	[account] [varchar](32) NOT NULL,
	[gamecode] [char](4) NOT NULL,
	[tester] [char](1) NOT NULL,
	[m_chLoginAuthority] [char](1) NULL,
	[regdate] [datetime] NOT NULL,
	[BlockTime] [char](8) NULL,
	[EndTime] [char](8) NULL,
	[WebTime] [char](8) NULL,
	[isuse] [char](1) NULL,
	[secession] [datetime] NULL,
	[email] [varchar](100) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE UNIQUE CLUSTERED INDEX [ACCOUNT_TBL_DETAIL_ID1] ON [dbo].[ACCOUNT_TBL_DETAIL] 
(
	[account] DESC,
	[gamecode] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[PCZone]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCZone](
	[PCZoneID] [int] IDENTITY(1,1) NOT NULL,
	[PCZoneName] [varchar](100) NOT NULL,
	[Address] [varchar](100) NOT NULL,
	[Phone] [varchar](100) NOT NULL,
	[CPU] [varchar](100) NOT NULL,
	[VGA] [varchar](100) NOT NULL,
	[RAM] [varchar](100) NOT NULL,
	[Monitor] [varchar](100) NOT NULL,
	[Comment] [varchar](1000) NOT NULL,
	[Grade] [tinyint] NOT NULL,
	[Account] [varchar](32) NOT NULL,
	[RegDate] [datetime] NOT NULL,
 CONSTRAINT [PK_iCafeInfo_1] PRIMARY KEY CLUSTERED 
(
	[PCZoneID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[mydata]    Script Date: 10/28/2009 17:14:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mydata](
	[line] [varchar](8000) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_MessengerList]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_MessengerList]
@serverindex char(2),
@m_szName varchar(32)
as


set nocount on


declare @q1 nvarchar(4000)
declare @m_idPlayer char(7)
set @q1 = '
select @m_idPlayer = m_idPlayer from CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.CHARACTER_TBL where m_szName = ''' + @m_szName + ''''
exec sp_executesql @q1, N'@m_idPlayer char(7) output', @m_idPlayer output


set @q1 = '
select c.m_szName, m_idPlayer
from CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.tblMessenger as m
	inner join CHR' + @serverindex + '.CHARACTER_' + @serverindex + '_DBF.dbo.CHARACTER_TBL as c on m.idFriend = c.m_idPlayer
where m.idPlayer = ''' + @m_idPlayer + ''' and isblock = ''F'' and chUse = ''T'' order by m.idFriend'
exec sp_executesql @q1
GO
/****** Object:  Table [dbo].[AccountPlay]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AccountPlay](
	[Account] [varchar](32) NOT NULL,
	[PlayDate] [int] NOT NULL,
	[PlayTime] [int] NOT NULL,
 CONSTRAINT [PK_AccountPlay] PRIMARY KEY CLUSTERED 
(
	[Account] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PCZoneIP]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PCZoneIP](
	[IPID] [int] IDENTITY(1,1) NOT NULL,
	[PCZoneID] [int] NOT NULL,
	[IPFrom1] [tinyint] NOT NULL,
	[IPFrom2] [tinyint] NOT NULL,
	[IPFrom3] [tinyint] NOT NULL,
	[IPFrom4] [tinyint] NOT NULL,
	[IPTo4] [tinyint] NOT NULL,
	[IsUse] [tinyint] NOT NULL,
	[RegDate] [datetime] NOT NULL,
	[OperID] [varchar](32) NULL,
	[OperDate] [datetime] NULL,
 CONSTRAINT [PK_iCafeIP_1] PRIMARY KEY CLUSTERED 
(
	[IPID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_UpdateCashInfo]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_UpdateCashInfo]
@account varchar(32),
@cash int
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin
	begin tran
		insert into CashHistory (account, beforeCash, afterCash)
		select @account, cash, @cash from ACCOUNT_TBL where account = @account


		update ACCOUNT_TBL
		set cash = @cash
		where account = @account
	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[ACCOUNT_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROC [dbo].[ACCOUNT_STR]
@iaccount   	VARCHAR(32),
@ipassword VARCHAR(16)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************






	ACCOUNT_STR 스토어드
	작성자 : 최석준
	작성일 : 2004.01.18


	ex) ACCOUNT_STR 'beat','1234'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on
IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount AND gamecode = 'A000')
	BEGIN
		IF EXISTS(SELECT account FROM ACCOUNT_TBL  
                        WHERE account = @iaccount  AND password = @ipassword )
-- 			BEGIN
-- 			
-- 				DECLARE @birthyear CHAR(4),@currdate CHAR(8)
-- 				SELECT  @birthyear = 	CASE 	WHEN  SUBSTRING(id_no2,1,1) IN ('9','0') THEN '18' + SUBSTRING(id_no1,1,2)
-- 							             							WHEN  SUBSTRING(id_no2,1,1) IN ('1','2') THEN '19' + SUBSTRING(id_no1,1,2)
-- 																	WHEN	 SUBSTRING(id_no2,1,1) IN ('3','4') THEN '20' + SUBSTRING(id_no1,1,2)
-- 																	WHEN  SUBSTRING(id_no2,1,1) IN ('5','6') THEN '21' + SUBSTRING(id_no1,1,2)			
-- 																	WHEN  SUBSTRING(id_no2,1,1) IN ('7','8') THEN '22' + SUBSTRING(id_no1,1,2)
-- 														END			 
-- 				   FROM ACCOUNT_TBL
-- 				WHERE account = @iaccount
-- 
-- 				SELECT @currdate = CONVERT(CHAR(8),GETDATE(),112)
-- 
-- 				SELECT	fError = 	CASE 	WHEN a.id_no1 = 'a00000' 
-- 																THEN '0' 
-- 								 							WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
-- 										   						THEN '3' 		
-- 															WHEN a.realname = 'F' 
-- 																THEN '4' 										 			
-- 												 			WHEN  @birthyear >  DATEADD(Year,-13,@currdate)
-- 																THEN '5'
-- 												 			WHEN  @birthyear >=  DATEADD(Year,-11,@currdate) AND b.tester = '0'
-- 																THEN '6'															
-- 															ELSE '0' 	END,
-- 
-- 								fText 	=	CASE 	WHEN a.id_no1 = 'a00000' 
-- 																THEN '사원 및 기자'	
-- 												 			WHEN b.BlockTime >= @currdate AND b.EndTime < @currdate
-- 														   		THEN '계정블럭이거나 유료화 초과'			
-- 															WHEN a.realname = 'F' 
-- 																THEN '실명처리가 안된것'									 			
-- 												 			WHEN  @birthyear >  DATEADD(Year,-13,@currdate)
-- 																THEN '프리프는 12세 이상 이용가 이므로 게임접속을 할수 없습니다.'	
-- 												 			WHEN  @birthyear >=  DATEADD(Year,-11,@currdate) AND b.tester = '0'
-- 																THEN '14세 미만 가입자 분들은 부모님 동의서를 보내주셔야 게임 접속이 가능합니다'																
-- 															ELSE  '정상사용자 14세 이상' END
-- 				   FROM 	ACCOUNT_TBL a, ACCOUNT_TBL_DETAIL b 
-- 				WHERE 	a.account = b.account AND b.account = @iaccount AND a.[password] = @ipassword
-- 			END
			BEGIN
				SELECT fError = '0', fText = 'OK'
			END
		ELSE
			BEGIN
				SELECT fError = '1', fText = 'Wrong Password !!'
			END
	END	
ELSE
	BEGIN
		SELECT fError = '2', fText = 'Account Not Exists !!'
	END
RETURN


-- 계정 검사 Rule
-- 1. (fError=2 리턴) 계정있는지 확인. 계정확인시 "gamecode = A000" 만 프리프 회원.
-- 2. (fError=1 리턴) 암호확인.
-- 3. (fError=0 리턴) 사원 및 기자 확인( id_no1가 a0000 )으로 확인.계정.암호만 확인
-- 4. (fError=3 리턴) 블럭타임과, 과금타임 확인.
-- 5. (fError=4 리턴) 실명확인.
-- 6. (fError=5 리턴) 12세 이상인지 확인. 이하이면 "프리프는 12세 이상 이용가 이므로 ]
--                            게임접속을 할수 없습니다." 라고 알림.
-- 7. (fError=6 리턴) 부모동의서가 없는 애덜 확인 tester = 0 이면 "14세 미만 가입자 분들은 
--                            부모등의서를 보내주셔야 게임 접속이 가능합니다"라고 알림.
-- 8. (fError=0 리턴) 위의 예말고는 정상 사용자로 인정.
set nocount off
GO
/****** Object:  StoredProcedure [dbo].[usp_CreateNewAccount]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_CreateNewAccount]
@account varchar(32),
@pw varchar(32),
@cash int = 0,
@email varchar(100) = ''
as
set nocount on
set xact_abort on


if not exists (select * from ACCOUNT_TBL where account = @account)
begin
	declare @hash char(32), @tpw varchar(40)
	set @tpw = 'dlrjsdn' + @pw


	exec master.dbo.xp_crypt @tpw, @hash output


	begin tran
	INSERT ACCOUNT_TBL(account,password,isuse,member,id_no1,id_no2,realname, cash)
	VALUES(@account, lower(@hash), 'T', 'A', '', '', '', @cash)
	INSERT ACCOUNT_TBL_DETAIL(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession, email)
	VALUES(@account,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,10,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL, @email)
	insert AccountPlay (Account, PlayDate)
	select @account, convert(int, convert(char(8), getdate(), 112))


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[sp_UpdateAccountPassword]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[sp_UpdateAccountPassword]
		@account		varchar(32),
		@password	varchar(32)
AS
SET NOCOUNT ON




	Update ACCOUNT_TBL SET Password = @password Where Account=@account
GO
/****** Object:  StoredProcedure [dbo].[usp_ChangePW]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangePW]
@account varchar(32),
@pw varchar(32)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin
	declare @hash char(32), @tpw varchar(40)
	set @tpw = 'tpgk' + @pw


	exec master.dbo.xp_crypt @tpw, @hash output


	begin tran
	update ACCOUNT_TBL
	set [password] = lower(@hash)
	where account = @account


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[LOGIN_RELOAD_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE       PROC [dbo].[LOGIN_RELOAD_STR]
/**********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************
	LOGIN_RELOAD_STR 스토어드
	작성자 : 최석준
	작성일 : 2005.01.24
	ex) LOGIN_RELOAD_STR


**********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/


AS
set nocount on
SELECT account FROM ACCOUNT_TBL WHERE reload='T'
set nocount off


RETURN
GO
/****** Object:  StoredProcedure [dbo].[CREATE_ACCOUNT_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE    PROC  [dbo].[CREATE_ACCOUNT_STR] 
@iGu CHAR(1) = 'A',
@k INT = 100
AS
DECLARE @i INT,@j INT,@oSeq CHAR(6)
IF @iGu = 'A'
BEGIN	
	SET @j = 1
	SELECT @i = ISNULL(CONVERT(INT,(MAX(account))),0) + 1 FROM ACCOUNT_TBL
	SELECT CONVERT(INT,(MAX(account))) + @k  FROM ACCOUNT_TBL
	WHILE @j < @k + 1
	BEGIN
		SET @oSeq = RIGHT('000000' + CONVERT(VARCHAR(6),@i),6)
		INSERT ACCOUNT_TBL
		(account,password,isuse,member,id_no1,id_no2,realname)
		VALUES
		(@oSeq,@oSeq,'T','A','','','')
		INSERT ACCOUNT_TBL_DETAIL
		(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession)
		VALUES
		(@oSeq,'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,6,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL)
		SET @i = @i + 1
		SET @j = @j + 1
		IF (@j  % 100 = 0)
		PRINT @oSeq
	END
RETURN
END
GO
/****** Object:  StoredProcedure [dbo].[usp_og_ChangePassWord]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_og_ChangePassWord]
@account varchar(32),
@password varchar(32)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL where account = @account)
begin	
	begin tran			
		Update ACCOUNT_TBL SET Password = @password Where account=@account
		if @@error <> 0
		begin
			rollback tran
			select -1
		end
		else
		begin
			commit tran
			select 1
		end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[LOGINJOIN_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROC [dbo].[LOGINJOIN_STR]
	@iGu        		CHAR(2) 			= 'A1', 
	@iaccount   	VARCHAR(32)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************


	LOGINJOIN_STR 스토어드
	작성자 : 송현석
	작성일 : 2004.01.30 
	수정일 : 


	ex) LOGINONOFF_STR 'A1','1234'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/


AS
set nocount on


IF @iGu = 'A1' -- ON
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'J'
		WHERE account = @iaccount
	RETURN
	END


ELSE
IF @iGu = 'A2' -- OFF
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'O'
		WHERE account = @iaccount	
	RETURN
	END


ELSE
IF @iGu = 'A3' -- ALL OFF
	BEGIN
		UPDATE ACCOUNT_TBL_DETAIL
		SET isuse = 'O'
	RETURN
	END


set nocount off
GO
/****** Object:  StoredProcedure [dbo].[usp_ChangeEmail]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangeEmail]
@account varchar(32),
@email varchar(100)
as
set nocount on
set xact_abort on


if exists (select * from ACCOUNT_TBL_DETAIL where account = @account)
begin
	begin tran
	update ACCOUNT_TBL_DETAIL
	set email = @email
	where account = @account


	if @@error <> 0
	begin
		rollback tran
		select -1
	end
	else
	begin
		commit tran
		select 1
	end
end
else
begin
	select 0
end
GO
/****** Object:  StoredProcedure [dbo].[USP_PCZoneIP_Check]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check '218.38.238.131', @o_Grade output
select @o_Grade


exec LOGIN_STR 'chipi', '85158c7b8a7f3113b0f32b70ed936b2c', '127.0.0.0'
*/
create   proc [dbo].[USP_PCZoneIP_Check]
     @i_IPAddress varchar(15)
    , @o_Grade tinyint output
as
set nocount on
set transaction isolation level read uncommitted


declare @i_IP1 tinyint
declare @i_IP2 tinyint
declare @i_IP3 tinyint
declare @i_IP4 tinyint


declare @index int


--@i_IP1
set @index = charindex('.', @i_IPAddress)
set @i_IP1 = left(@i_IPAddress, @index-1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress)-@index)


--@i_IP2
set @index = charindex('.', @i_IPAddress)
set @i_IP2 = left(@i_IPAddress, @index-1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress)-@index)


--@i_IP3, @i_IP4 
set @index = charindex('.', @i_IPAddress)
set @i_IP3 = left(@i_IPAddress, @index-1)
set @i_IP4 = right(@i_IPAddress, len(@i_IPAddress)-@index)


--PCZoneIP_Check
select @o_Grade = b.Grade
from PCZoneIP a
        inner join PCZone b
            on a.PCZoneID = b.PCZoneID
where IsUse in (1, 9)
and IPFrom1 = @i_IP1
and IPFrom2 = @i_IP2
and IPFrom3 = @i_IP3
and IPFrom4 <= @i_IP4 and IPTo4 >= @i_IP4


select @o_Grade = isnull(@o_Grade, 0)
GO
/****** Object:  StoredProcedure [dbo].[LOGIN_STR]    Script Date: 10/28/2009 17:27:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOGIN_STR]
	@iaccount   	VARCHAR(32),
	@ipassword 		char(32)
   -- Ver. 14 PCZoneIP
   ,@i_IPAddress varchar(15) = '0.0.0.0'
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************






	ACCOUNT_STR 스토어드
	작성자 : 최석준
	작성일 : 2004.01.18


	ex) ACCOUNT_STR 'beat','1234'
SELECT * FROM ACCOUNT_TBL_DETAIL WHERE account='aeonsoft'


***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on


-- Ver 14. PCZoneIP_Check
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check @i_IPAddress, @o_Grade output      


IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b 
                  WHERE a.account = b.account AND a.account = @iaccount ) -- AND gamecode = 'A000') 
BEGIN
	
	DECLARE @curDate char(8)


		IF EXISTS(SELECT account FROM ACCOUNT_TBL  
                        WHERE account = @iaccount  AND password = @ipassword) BEGIN


			SELECT 	@curDate=CONVERT(CHAR(8), getdate(), 112)
			
			SELECT 	fError=CASE 
							-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN '91'
							WHEN BlockTime>=@curDate THEN '9' 
							ELSE '0' END,
				   	fText= CASE 
							-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN 'Session Expired'
							WHEN BlockTime>=@curDate THEN 'Block' ELSE 'OK' END,
				   	fCheck=tester,
					f18='1' 
					-- Ver14. PCZoneIP
					 ,fPCZone = @o_Grade
			FROM ACCOUNT_TBL a INNER JOIN ACCOUNT_TBL_DETAIL b ON (a.account=b.account)
			WHERE a.account=@iaccount
		END
		ELSE BEGIN
			SELECT fError = '1', fText = 'Wrong Password !!',fCheck ='',f18='1', fPCZone = '0'			-- PCZoneIP (, fPCZone = '0' 추가)
		END
END	
ELSE BEGIN
	SELECT fError = '2', fText = 'Account Not Exists !!',fCheck ='',f18='1', fPCZone = '0'			-- PCZoneIP (, fPCZone = '0' 추가)
END


RETURN


-- 계정 검사 Rule
-- 1. (fError=2 리턴) 계정있는지 확인. 계정확인시 "gamecode = A000" 만 프리프 회원.
-- 2. (fError=1 리턴) 암호확인.
-- 3. (fError=0 리턴) 사원 및 기자 확인( id_no1가 a0000 )으로 확인.계정.암호만 확인
-- 4. (fError=3 리턴) 블럭타임과, 과금타임 확인.
-- 5. (fError=4 리턴) 실명확인.
-- 6. (fError=5 리턴) 12세 이상인지 확인. 이하이면 "프리프는 12세 이상 이용가 이므로 ]
--                            게임접속을 할수 없습니다." 라고 알림.
-- 7. (fError=6 리턴) 부모동의서가 없는 애덜 확인 tester = 0 이면 "14세 미만 가입자 분들은 
--                            부모등의서를 보내주셔야 게임 접속이 가능합니다"라고 알림.
-- 8. (fError=0 리턴) 위의 예말고는 정상 사용자로 인정.
set nocount off
GO
/****** Object:  Default [DF_CashHistory_regdate]    Script Date: 10/28/2009 17:14:39 ******/
ALTER TABLE [dbo].[CashHistory] ADD  CONSTRAINT [DF_CashHistory_regdate]  DEFAULT (getdate()) FOR [regdate]
GO
/****** Object:  Default [DF_ACCOUNT_TBL_cash]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[ACCOUNT_TBL] ADD  CONSTRAINT [DF_ACCOUNT_TBL_cash]  DEFAULT ((0)) FOR [cash]
GO
/****** Object:  Default [DF_ACCOUNT_DETAIL_email]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[ACCOUNT_TBL_DETAIL] ADD  CONSTRAINT [DF_ACCOUNT_DETAIL_email]  DEFAULT ('') FOR [email]
GO
/****** Object:  Default [DF_PCZone_Grade]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_Grade]  DEFAULT ((1)) FOR [Grade]
GO
/****** Object:  Default [DF_PCZone_Account]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_Account]  DEFAULT ('') FOR [Account]
GO
/****** Object:  Default [DF_PCZone_RegDate]    Script Date: 10/28/2009 17:14:40 ******/
ALTER TABLE [dbo].[PCZone] ADD  CONSTRAINT [DF_PCZone_RegDate]  DEFAULT (getdate()) FOR [RegDate]
GO
/****** Object:  Default [DF_AccountPlay_Account]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_Account]  DEFAULT ('') FOR [Account]
GO
/****** Object:  Default [DF_AccountPlay_PlayDate]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_PlayDate]  DEFAULT ('') FOR [PlayDate]
GO
/****** Object:  Default [DF_AccountPlay_PlayTime]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[AccountPlay] ADD  CONSTRAINT [DF_AccountPlay_PlayTime]  DEFAULT ((0)) FOR [PlayTime]
GO
/****** Object:  Default [DF_PCZoneIP_IsUse]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP] ADD  CONSTRAINT [DF_PCZoneIP_IsUse]  DEFAULT ((0)) FOR [IsUse]
GO
/****** Object:  Default [DF_PCZoneIP_RegDate]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP] ADD  CONSTRAINT [DF_PCZoneIP_RegDate]  DEFAULT (getdate()) FOR [RegDate]
GO
/****** Object:  ForeignKey [FK_iCafeIP_iCafeInfo1]    Script Date: 10/28/2009 17:27:48 ******/
ALTER TABLE [dbo].[PCZoneIP]  WITH CHECK ADD  CONSTRAINT [FK_iCafeIP_iCafeInfo1] FOREIGN KEY([PCZoneID])
REFERENCES [dbo].[PCZone] ([PCZoneID])
GO
ALTER TABLE [dbo].[PCZoneIP] CHECK CONSTRAINT [FK_iCafeIP_iCafeInfo1]
GO