USE [master]
GO
/****** Object:  Database [AccountLog]    Script Date: 12/20/2015 6:07:48 PM ******/
CREATE DATABASE [AccountLog]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'AccountLog', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AccountLog.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'AccountLog_log', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\AccountLog_1.ldf' , SIZE = 5696KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [AccountLog] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AccountLog].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [AccountLog] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [AccountLog] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [AccountLog] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [AccountLog] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [AccountLog] SET ARITHABORT OFF 
GO
ALTER DATABASE [AccountLog] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [AccountLog] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [AccountLog] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [AccountLog] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [AccountLog] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [AccountLog] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [AccountLog] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [AccountLog] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [AccountLog] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [AccountLog] SET  DISABLE_BROKER 
GO
ALTER DATABASE [AccountLog] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [AccountLog] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [AccountLog] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [AccountLog] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [AccountLog] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [AccountLog] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [AccountLog] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [AccountLog] SET RECOVERY FULL 
GO
ALTER DATABASE [AccountLog] SET  MULTI_USER 
GO
ALTER DATABASE [AccountLog] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [AccountLog] SET DB_CHAINING OFF 
GO
ALTER DATABASE [AccountLog] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [AccountLog] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [AccountLog] SET DELAYED_DURABILITY = DISABLED 
GO
USE [AccountLog]
GO
/****** Object:  User [Stormy-PC\Administrator]    Script Date: 12/20/2015 6:07:48 PM ******/
CREATE USER [Stormy-PC\Administrator] FOR LOGIN [Stormy-PC\Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [si.zhong_query##$Z]    Script Date: 12/20/2015 6:07:48 PM ******/
CREATE USER [si.zhong_query##$Z] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [NT AUTHORITY\SYSTEM]    Script Date: 12/20/2015 6:07:48 PM ******/
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [aCC_sel]    Script Date: 12/20/2015 6:07:48 PM ******/
CREATE USER [aCC_sel] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_denydatawriter] ADD MEMBER [Stormy-PC\Administrator]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [si.zhong_query##$Z]
GO
ALTER ROLE [db_owner] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_datareader] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_denydatawriter] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
ALTER ROLE [db_owner] ADD MEMBER [aCC_sel]
GO
/****** Object:  Table [dbo].[tAccountLog]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tAccountLog](
	[nLogNo] [bigint] IDENTITY(1,1) NOT NULL,
	[sUserID] [nvarchar](16) NULL,
	[nType] [tinyint] NOT NULL,
	[nUserNo] [int] NULL,
	[nPlayMin] [smallint] NULL,
	[sUserPassword] [nvarchar](16) NULL,
	[nWorldNo] [tinyint] NULL,
	[nErrorCode] [smallint] NULL,
	[sIP] [varchar](16) NULL,
	[dDate] [datetime] NOT NULL CONSTRAINT [DF__tAccountL__dDate__07C12930]  DEFAULT (getdate()),
PRIMARY KEY CLUSTERED 
(
	[nLogNo] 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].[tActiveUsers]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tActiveUsers](
	[nUserNo] [int] NULL,
	[nWorldNo] [tinyint] NULL,
	[dLoginTime] [datetime] NULL,
	[nIsOnline] [tinyint] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tEvent_Friend]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tEvent_Friend](
	[nEventUserNo] [int] NOT NULL,
	[nEventCharNo] [int] NOT NULL,
	[nFriendUserNo] [int] NOT NULL,
	[nFriendCharNo] [int] NOT NULL,
	[nWorldNo] [tinyint] NOT NULL,
	[bEventComplete] [tinyint] NOT NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tEvent_Friend] PRIMARY KEY CLUSTERED 
(
	[nEventUserNo] 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
/****** Object:  Table [dbo].[tEvent_Friend_HideMsg]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tEvent_Friend_HideMsg](
	[nUserNo] [int] NOT NULL,
 CONSTRAINT [PK_tEvent_Frield_HideMsg] PRIMARY KEY CLUSTERED 
(
	[nUserNo] 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
/****** Object:  Table [dbo].[tEvent_L20]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tEvent_L20](
	[nUserNo] [int] NOT NULL,
	[nEventType] [tinyint] NOT NULL,
	[nWorldNo] [tinyint] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[dDateDone] [datetime] NOT NULL,
 CONSTRAINT [PK_tEvent_L20] PRIMARY KEY CLUSTERED 
(
	[nUserNo] ASC,
	[nEventType] 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
/****** Object:  Table [dbo].[tEventWorldCup]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tEventWorldCup](
	[nEventCharNo] [int] NOT NULL,
	[nEventUserNo] [int] NOT NULL,
	[nLevel] [tinyint] NOT NULL,
	[nWorldNo] [tinyint] NOT NULL,
 CONSTRAINT [IX_tEventWorldCup] UNIQUE NONCLUSTERED 
(
	[nEventCharNo] ASC,
	[nLevel] ASC,
	[nWorldNo] 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
/****** Object:  Table [dbo].[tHackScan]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tHackScan](
	[nNo] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nUserNo] [int] NOT NULL,
	[nWorldNo] [tinyint] NOT NULL,
	[nCharNo] [int] NOT NULL,
	[sIP] [char](20) NOT NULL,
	[nFlag] [tinyint] NOT NULL,
	[sCharID] [nvarchar](50) NOT NULL,
	[sDesc] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_tHackScan] PRIMARY KEY CLUSTERED 
(
	[nNo] 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].[tIPBlock]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tIPBlock](
	[sIPStart] [nvarchar](20) NOT NULL,
	[sIPEnd] [nvarchar](20) NOT NULL,
	[sDesc] [nvarchar](30) NULL,
	[dDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tIPBlock] PRIMARY KEY CLUSTERED 
(
	[sIPStart] ASC,
	[sIPEnd] 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
/****** Object:  Table [dbo].[tLogout]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tLogout](
	[rowNo] [int] IDENTITY(1,1) NOT NULL,
	[logDate] [datetime] NOT NULL CONSTRAINT [DF_tLogout_logDate]  DEFAULT (getdate()),
	[worldNo] [tinyint] NULL,
	[userNo] [int] NOT NULL,
	[charNo] [int] NOT NULL,
	[class] [tinyint] NOT NULL,
	[level] [tinyint] NOT NULL,
	[experience] [bigint] NOT NULL,
	[playTime] [smallint] NOT NULL CONSTRAINT [DF_tLogout_playTime]  DEFAULT ((0))
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tReport]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tReport](
	[nRegNumber] [int] IDENTITY(1,1) NOT NULL,
	[nReporter] [int] NOT NULL,
	[sReporterCharID] [nvarchar](16) NOT NULL,
	[nWorldNo] [tinyint] NOT NULL,
	[dReportDate] [datetime] NULL,
	[sHarmer] [nvarchar](16) NULL,
	[sReportType] [nvarchar](16) NULL,
	[sReason] [nvarchar](512) NULL,
	[sChatLog] [ntext] NULL,
	[nResult] [tinyint] NULL,
	[sRemark] [nvarchar](128) NULL,
 CONSTRAINT [PK_tReport] PRIMARY KEY CLUSTERED 
(
	[nRegNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[tEvent_Friend] ADD  CONSTRAINT [DF_tEvent_Friend_nQuestComplete]  DEFAULT ((0)) FOR [bEventComplete]
GO
ALTER TABLE [dbo].[tEvent_Friend] ADD  CONSTRAINT [DF_tEvent_FriendNolja_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tEvent_L20] ADD  CONSTRAINT [DF_tEvent_L20_dDateDone]  DEFAULT (getdate()) FOR [dDateDone]
GO
ALTER TABLE [dbo].[tHackScan] ADD  CONSTRAINT [DF_tHackScan_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tHackScan] ADD  CONSTRAINT [DF_tHackScan_nFlag]  DEFAULT ((0)) FOR [nFlag]
GO
ALTER TABLE [dbo].[tIPBlock] ADD  CONSTRAINT [DF_tIPBlock_dDate]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tReport] ADD  CONSTRAINT [DF_tReport_dReportDate]  DEFAULT (getdate()) FOR [dReportDate]
GO
ALTER TABLE [dbo].[tReport] ADD  CONSTRAINT [DF_tReport_nResult]  DEFAULT ((0)) FOR [nResult]
GO
/****** Object:  StoredProcedure [dbo].[p_AccountLog_Add]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_AccountLog_Add] 
/*
 Account log add
 input:
  ｷﾎｱﾗｿ｡ ｱ箙ﾏｵﾉ ﾁ､ｺｸｵ・See SQL)
 output: 
  nLogNo = ﾃﾟｰ｡ｵﾈ ｷﾎｱﾗﾀﾇ ｹ｣(ｿ｡ｷｯｽﾃ LogNo = 0 )
*/
@nType		tinyint,
@nUserNo	int,
@nWorldNo	tinyint,
@nPlayMin	smallint,
@nErrorCode smallint,
@sIP		varchar(16),
@sUserID	nvarchar(16),
@sUserPassword	nvarchar(16),
@nLogNo bigint OUTPUT
AS
SET NOCOUNT ON
SET @nLogNo = 0
INSERT tAccountLog (  nType,  nUserNo,  nWorldNo,  nPlayMin,  nErrorCode,  sIP,  sUserID,  sUserPassword )
VALUES             ( @nType, @nUserNo, @nWorldNo, @nPlayMin, @nErrorCode, @sIP, @sUserID, @sUserPassword )
SET @nLogNo = @@IDENTITY
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_AccountLog_Get]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_AccountLog_Get] 
/*
  AccountLog Get - ﾁﾖｾ錝・ﾁｶｰﾇｿ｡ ｸﾂｴﾂ ｷﾎｱﾗｸｦ ｸﾎｰﾋｻﾘｼｭ ｾﾂｴﾙ.
  input:
   - ﾁﾖｾ錝・ﾁｶｰﾇﾀ
  output:
   - Selectｵﾈ ｵ･ﾀﾌﾅｸ
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nchar(512)
SET  @SQLCMD = N'SELECT nLogNo, dDate, nType, nUserNo, nWorldNo, nPlayMin, nErrorCode, sIP, sUserID, sUserPassword FROM tAccountLog WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_AccountLog_GetCount]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_AccountLog_GetCount]
/*
  AccountLog Get Count - ﾁﾖｾ錝・ﾁｶｰﾇｿ｡ ｸﾂｴﾂ ｷﾎｱﾗｸｦ ｸﾎｰﾋｻﾘｼｭ ｾﾂｴﾙ.
  input:
   - ﾁﾖｾ錝・ﾁｶｰﾇﾀ
  output:
   - @Count = ﾇﾘｴ・ﾁｶｰﾇｿ｡ ｸｸﾁｷﾇﾏｴﾂ ｷｹﾄﾚｵ蠑・
*/
@WhereExpr nvarchar(256),
@Count int OUTPUT
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET @SQLCMD = N'SELECT @RowCount = COUNT(*) FROM tAccountLog WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD, N'@RowCount AS int OUTPUT', @RowCount = @Count OUTPUT
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_AccountLog_GetLog]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' ﾇﾔｼ・: p_AccountLog_GetLog
' ｳｻ  ｿ・: OPTool ｿ｡ｼｭ ｻ鄙・ OPToolｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮｿ｡ ｸﾂｴﾂ ｵ･ﾀﾌﾅﾍ ｼｭﾄ｡.
' ﾀﾛｼｺﾀﾚ : ﾀﾌｺｴｱ・
' ﾀﾛｼｺﾀﾏ : 2005ｳ・11ｿ・7ﾀﾏ
' ｹ・ ﾁｯ : 1.0
' ﾀﾎ  ﾀﾚ :
'			input Parameter
'				@QueryExpr		: OPTool ｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮ
'			Output Parameter
'				
'			RETURN_VALUE
'
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[p_AccountLog_GetLog]
	@QueryExpr nvarchar(256)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLCMD nvarchar(512)
	SET @SQLCMD = N'SELECT a.nLogNo, a.dDate, a.nUserNo, b.sUserID, a.nType, a.sIP, a.nPlayMin, a.sUserPassword, a.nErrorCode, a.nWorldNo
			  FROM tAccountLog a INNER JOIN Account.dbo.tUser b ON a.nUserNo = b.nUserNo ' + @QueryExpr
	EXEC sp_executesql @SQLCMD
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_AccountLog_GetLogCount]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' ﾇﾔｼ・: p_AccountLog_GetLogCount
' ｳｻ  ｿ・: OPTool ｿ｡ｼｭ ｻ鄙・ OPToolｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮｿ｡ ｸﾂｴﾂ ｵ･ﾀﾌﾅﾍﾀﾇ ｰｳｼｦ ｾ錞ﾈ.
' ﾀﾛｼｺﾀﾚ : ﾀﾌｺｴｱ・
' ﾀﾛｼｺﾀﾏ : 2005ｳ・11ｿ・7ﾀﾏ
' ｹ・ ﾁｯ : 1.0
' ﾀﾎ  ﾀﾚ :
'			input Parameter
'				@QueryExpr		: OPTool ｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮ
'			Output Parameter
				@Count			: ｼｭﾄ｡ｵﾈ ｵ･ﾀﾌﾅﾍﾀﾇ ｰｳｼ・
'				
'			RETURN_VALUE
'
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[p_AccountLog_GetLogCount]
	@QueryExpr nvarchar(256),
	@Count int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLCMD nvarchar(512)
	SET @SQLCMD = N'SELECT @RowCount = COUNT(*)
			  FROM tAccountLog  a INNER JOIN Account.dbo.tUser b ON a.nUserNo = b.nUserNo ' + @QueryExpr
	EXEC sp_executesql @SQLCMD, N'@RowCount AS int OUTPUT', @RowCount = @Count OUTPUT
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_DelRequest]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-17
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ﾄ｣ｱｸ ﾄﾉｸｯｸ・ｵ﨧ﾏ ｻ霖ｦ
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_DelRequest]
@nEventUserNo 	int,
@err 		smallint OUTPUT
AS
SET NOCOUNT ON
DELETE FROM tEvent_Friend WHERE nEventUserNo= @nEventUserNo
IF @@ROWCOUNT = 0
	SET @err = 1
ELSE
	SET @err = 0	
RETURN @@ERROR


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_EventComplete]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-17
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ﾀﾌｺ･ﾆｮ ﾄｺﾆｮ ｿﾏｷ・
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_EventComplete]
@nEventUserNo 	int,
@nFriendUserNo	int OUTPUT,
@nFriendCharNo	int OUTPUT,
@err 		smallint OUTPUT
AS
SET NOCOUNT ON
SET @nFriendCharNo = 0
SELECT @nFriendUserNo = nFriendUserNo, @nFriendCharNo = nFriendCharNo FROM tEvent_Friend WITH(READUNCOMMITTED) WHERE nEventUserNo = @nEventUserNo
IF @@ROWCOUNT = 0
BEGIN
	SET @err = 1
	RETURN @@ERROR
END
UPDATE tEvent_Friend SET  bEventComplete = 1 WHERE nEventUserNo = @nEventUserNo
IF @@ERROR <> 0
	SET @err = @@ERROR
ELSE
	SET @err = 0
RETURN @@ERROR


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_Get]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-----------------------------------
SadPig : 2006.08.08 (OPTool)
	Input Parameter
		@nFriendUserNo 
	Output Parameter
		@@ERROR		
-----------------------------------
*/
CREATE PROCEDURE [dbo].[p_Event_Friend_Get]
	@nFriendUserNo	int
AS
BEGIN
	SET NOCOUNT ON
	 
	SELECT 
		t.nEventUserNo,
		t.nEventCharNo,
		t.nFriendCharNo,
		t.nWorldNo,
		t.bEventComplete,
		t.dDate
	FROM tEvent_Friend t
	WHERE t.nFriendUserNo = @nFriendUserNo
	SET NOCOUNT OFF
	RETURN @@ERROR
END


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_GetCount]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-16
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ﾀﾚｽﾅｸｦ ﾃﾟﾃｵﾇﾑ ｽﾅｱﾔｰ｡ﾀﾔﾀﾚ ｼﾀﾚ ｿ菘ｻ
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_GetCount]
@nUserNo int,
@nCount int OUTPUT 
AS
SET NOCOUNT ON
SELECT @nCount = COUNT(*) FROM tEvent_Friend WITH(READUNCOMMITTED)
WHERE nFriendUserNo = @nUserNo
RETURN @@error


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_GetInitMsg]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-16
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ﾀﾌｺ･ﾆｮ ｸﾞｼｼﾁ・ｺ・
@modify kodong 
  -- 2010.12.21
  -- ｰ｡ﾀﾔﾀﾏｷﾎｺﾎﾅﾍ ﾇﾑｴﾞﾀﾌｳｻｿ｡ ｰ霖､ﾀﾎﾁ・ﾈｮﾀﾎﾇﾔ.
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_GetInitMsg]
	@nUserNo int,
	@bShowMsg tinyint OUTPUT,
	@bEnableRequestButton tinyint OUTPUT 
AS
BEGIN
	SET NOCOUNT ON

	DECLARE
	@dRegisterDate	datetime,
	@dEventStartDate	datetime,
	@dEventEndDate	datetime,
	@nEventCharNo	int

	--SET @dEventStartDate = '2006-05-26'
	SET @dEventStartDate = '2007-06-21' -- ｽﾃﾀﾛ
	SET @dEventEndDate =  '2020-07-13' -- ﾁｾｷ・

	SELECT @dRegisterDate = dDate
	  FROM Account.dbo.tUser tUser
	 WHERE tUser.nUserNo = @nUserNo --AND 
	IF @dRegisterDate IS NULL
		BEGIN
			SET	@bEnableRequestButton = 0
			SET @bShowMsg = 0
			RETURN
		END

	-- modify kodong 2010.12.21
	IF DATEADD(MONTH, 1, @dRegisterDate) < GETDATE()
		BEGIN
			SET	@bEnableRequestButton = 0
			SET @bShowMsg = 0
			RETURN
		END

	IF @dRegisterDate > @dEventStartDate AND @dRegisterDate < @dEventEndDate
		BEGIN
			-- ﾀﾌｹﾌ ﾃﾟﾃｵﾇﾟｴﾂｰ｡....?
			IF EXISTS ( SELECT nEventUserNo FROM tEvent_Friend WITH(READUNCOMMITTED) WHERE nEventUserNo=@nUserNo )
				BEGIN
					--ﾇﾘｴ・ ﾀﾌｺ･ﾆｮ ﾄｺﾆｮｰ｡ ﾁｸﾀ酩ﾏｴﾂｰ｡...?
					SET	@bEnableRequestButton = 2
				END
			ELSE
				BEGIN
					SET	@bEnableRequestButton = 1
				END
		END
	ELSE
		BEGIN
			SET	@bEnableRequestButton = 0
		END

	IF EXISTS ( SELECT nUserNo FROM tEvent_Friend_HideMsg WITH(READUNCOMMITTED) WHERE nUserNo=@nUserNo )
		BEGIN
			SET 	@bShowMsg = 0
		END
	ELSE
		BEGIN
			SET 	@bShowMsg = 1
		END
END


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_Giveup]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-19
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ﾀﾌｺ･ﾆｮ ﾆ・
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_Giveup]
@nEventUserNo 	int,
@nFriendCharNo 	int OUTPUT,
@err 		smallint OUTPUT
AS
SET NOCOUNT ON
SET @nFriendCharNo = 0
SELECT @nFriendCharNo = nFriendCharNo FROM tEvent_Friend WHERE nEventUserNo= @nEventUserNo
IF @@ROWCOUNT = 0
	BEGIN
		SET @err = 1
	END
ELSE
	BEGIN
		DELETE FROM tEvent_Friend WHERE nEventUserNo= @nEventUserNo
		SET @err = 0
	END
RETURN @@ERROR


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_SetHideMsg]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-17
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ｴﾌｻ・ｾﾈｺｸｿｩﾁﾖｱ・ﾃｼﾅｩｹﾚｽｺ ｼｳﾁ､
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_Friend_SetHideMsg]
@nUserNo 	int,
@err 		smallint OUTPUT
AS
SET NOCOUNT ON
IF EXISTS (SELECT nUserNo FROM tEvent_Friend_HideMsg WITH(READUNCOMMITTED)
		WHERE nUserNo= @nUserNo )
	BEGIN
		SET @err = 1
	END
ELSE
	BEGIN
		INSERT INTO tEvent_Friend_HideMsg(nUserNo) VALUES(@nUserNo)
		IF @@ERROR <> 0
			SET @err = @@ERROR
		ELSE
			SET @err = 0
	END
RETURN


GO
/****** Object:  StoredProcedure [dbo].[p_Event_Friend_SetRequest]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-18
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>

@err	0		<ｼｺｰ・
	1		<ﾀﾌｹﾌ ｵ﨧ﾏﾇﾟﾀｽ>
	2		<ﾄ｣ｱｸﾄﾉｸｯｸ暲ﾌ ﾁｸﾀ酩ﾏﾁ・ｾﾊﾀｽ>
	3		<ｼｭｹ・ｿﾀｹﾙ>
	4		<ｵ﨧ﾏｿ｡ｷｯ>
	5		<ｰｰﾀｺｰｳﾁ､>

@brief	ﾄ｣ｱｸ ﾄﾉｸｯｸ・ﾃﾟﾃｵ
==================================================*/

create  PROCEDURE [dbo].[p_Event_Friend_SetRequest]
@nEventUserNo 	int,
@nEventCharNo 	int,

@sFriendCharID	nvarchar(16),

@nWorldNo	tinyint,

@nFriendCharNo	int OUTPUT,
@err 		smallint OUTPUT

AS
SET NOCOUNT ON

DECLARE
@nFriendUserNo	int

SET @err = 0
SET @nFriendCharNo = 0

IF EXISTS (SELECT nEventUserNo FROM tEvent_Friend WITH(READUNCOMMITTED)
		WHERE nEventUserNo= @nEventUserNo )
	BEGIN
		PRINT('ﾀﾌｹﾌ ｵ﨧ﾏﾇﾑ ｰｳﾁ､')
		SET @err = 1
	END
ELSE
	BEGIN
		--World 0
		IF @nWorldNo = 0

			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM World00_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	
				BEGIN
					PRINT('ﾁｸﾀ酩ﾏﾁ・ｾﾊｴﾂ ﾄﾉｸｯﾅﾍ')
					SET @err = 2			
				END
			END
/*		--World 1
		ELSE IF @nWorldNo = 1
			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM [W01CHAR].World01_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	
				BEGIN
					PRINT('ﾁｸﾀ酩ﾏﾁ・ｾﾊｴﾂ ﾄﾉｸｯﾅﾍ')
					SET @err = 2			
				END		
			END
		--ﾁﾟｿ・ ｻ邉ｻ ﾅﾗｽｺﾆｮｶ・ﾀﾏｴﾜ ｸｷｾﾆｳ・IDCｷﾎ ｿﾈｱ篋・ｼｭｹｳｼ｡ ｸﾂｰﾔ ﾁﾖｼｮﾀｻ ﾇｮｾ﨨ﾟﾇﾔ)


		--World 2
		ELSE IF @nWorldNo = 2
			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM [W02CHAR].World02_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	SET @err = 2			
			END

		--World 3
		ELSE IF @nWorldNo = 3
			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM [W03CHAR].World03_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	SET @err = 2			
			END

		--World 4
		ELSE IF @nWorldNo = 4
			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM [W04CHAR].World04_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	SET @err = 2			
			END

		--World 5
		ELSE IF @nWorldNo = 5
			BEGIN
				SELECT @nFriendCharNo=nCharNo, @nFriendUserNo=nUserNo
				FROM [W05CHAR].World05_Character.dbo.tCharacter AS CHARDATA 
				WHERE  CHARDATA.sID=@sFriendCharID
				
				IF @@ROWCOUNT = 0	SET @err = 2			
			END
		*/
		ELSE
			BEGIN
				SET @err = 3
			END	

		--END

	END



IF @err = 0
BEGIN
	IF @nEventUserNo = @nFriendUserNo
		BEGIN
			SET @err = 5
		END
	
	ELSE
		BEGIN
			INSERT INTO tEvent_Friend(nEventUserNo,nEventCharNo,nFriendUserNo,nFriendCharNo,nWorldNo) 
				VALUES(@nEventUserNo,@nEventCharNo,@nFriendUserNo,@nFriendCharNo,@nWorldNo)
			IF @@ERROR <> 0		SET @err = 4
		END
END

RETURN @@error


GO
/****** Object:  StoredProcedure [dbo].[p_Event_L20_Get]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-----------------------------------
CJC : 2007.08.03
p_Event_L20_Get
output:
	@nRet = 0, NOT FOUND
	@nRet = 1, OK, COUNT
	@nRet = ?, COUNT
-----------------------------------
*/
CREATE PROCEDURE [dbo].[p_Event_L20_Get]
	@nUserNo	int,
	@nEventType	tinyint,
	
	@nWorldNo	tinyint output,
	@nCharNo	int output,
	@dDateDone	datetime output,
	@nRet		int output
AS
BEGIN
	SET NOCOUNT ON
	SET @nWorldNo = 0
	SET @nCharNo = 0
	SET @dDateDone = getdate()
 	SELECT 
		@nWorldNo = nWorldNo,
		@nCharNo = nCharNo,
		@dDateDone = dDateDone
	FROM tEvent_L20
	WHERE nUserNo = @nUserNo AND nEventType = @nEventType
	SET @nRet = @@ROWCOUNT
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_Event_L20_Set]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-----------------------------------
CJC : 2007.08.03
p_Event_L20_Set
output:
 @nRet = 0, OK
 @nRet = 1, ALREAD EXISTS
 @nRet = 2, Not found user
 @nRet = 3, Out of event date
 @nRet = ?, DB ERROR
-----------------------------------
*/
CREATE PROCEDURE [dbo].[p_Event_L20_Set]
	@nUserNo	int,
	@nEventType	tinyint,
	@nWorldNo	tinyint,
	@nCharNo	int,
	@nEventPoint 	int,
	@nRet		int output
AS
BEGIN
	SET NOCOUNT ON
	
	-- ﾀﾌｺ･ﾆｮ ｱ箍｣ ﾈｮﾀﾎ
	DECLARE @dEventStartDate datetime
	DECLARE @dEventEndDate	datetime
	DECLARE @dRegisterDate	datetime
	
	SET @dEventStartDate = '2005-08-07' --ｽﾃﾀﾛﾀﾏ
	SET @dEventEndDate =   '2020-12-31' --ﾁｾｷ眤ﾏ
	SELECT @dRegisterDate=dDate FROM Account.dbo.tUser tUser WHERE tUser.nUserNo = @nUserNo
	IF @@ROWCOUNT = 0
	BEGIN
		SET	@nRet = 2
		RETURN 2
	END
	IF @dRegisterDate < @dEventStartDate OR @dRegisterDate > @dEventEndDate
	BEGIN
		SET	@nRet = 3
		RETURN 3
	END
	
	-- ﾀﾌｹﾌ ｿﾏｷ・ﾇﾟｴﾂﾁ・ﾈｮﾀﾎ
	IF EXISTS( SELECT nUserNo FROM tEvent_L20 WITH(READUNCOMMITTED) WHERE nUserNo = @nUserNo AND nEventType = @nEventType )
	BEGIN
		SET @nRet = 1
		RETURN 1
	END
	
	-- ｿﾏｷ眦ｳｸｮ / ｱ箙ﾏ
	BEGIN TRAN
	INSERT INTO tEvent_L20	( nUserNo, nEventType, nWorldNo, nCharNo) 
	VALUES					(@nUserNo,@nEventType,@nWorldNo,@nCharNo)
	SET @nRet = @@ERROR
	IF @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		RETURN -1
	END
	
	-- ｿﾏｷ・ﾀﾌｺ･ﾆｮ ﾆﾎﾆｮ ﾁﾞ
	EXEC @nRet = Account.dbo.usp_Cash_setCash @nUserNo, 2, @nEventPoint, N'200708_EVENT_L20'
	IF @nRet <> 0
	BEGIN
		ROLLBACK TRAN
		RETURN -1
	END
	COMMIT TRAN
	RETURN 0
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_Event_WorldCup_Enter]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-05-26
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ｿ蠧ﾅ ﾀﾌｺ･ﾆｮｿ｡ ﾀﾀｸﾈｴﾙ.
==================================================*/
CREATE PROCEDURE [dbo].[p_Event_WorldCup_Enter]
@nCharNo	int,
@nUserNo	int,
@nLevel		tinyint,
@nWorldNo	tinyint,
@err 		smallint OUTPUT
AS
SET NOCOUNT ON
IF GETDATE() > '2006-06-07'
BEGIN
	SET @err = 3
	RETURN @@ERROR
END
IF EXISTS ( SELECT nEventCharNo FROM tEventWorldCup WHERE nEventCharNo=@nCharNo AND nLevel=@nLevel AND nWorldNo=@nWorldNo )
	BEGIN
		SET @err = 1
	END 
ELSE
	BEGIN
		INSERT INTO tEventWorldCup(nEventCharNo, nEventUserNo, nLevel, nWorldNo)
			VALUES(@nCharNo, @nUserNo, @nLevel, @nWorldNo )
		IF @@ERROR <> 0 --Error
			SET @err = 2
		ELSE
			SET @err = 0
	END
RETURN @@ERROR


GO
/****** Object:  StoredProcedure [dbo].[p_GetCharacterPlayTime_Select]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------        
' 작성자 : 용효빈      
' 작성일 : 2011-07-28      
' 설  명 : 캐릭터 별 플레이타임을 가져온다.      
' 수정일/ 수정한 사람/ 수정한 내용      
' 예  제 : Exec  p_GetCharacterPlayTime_Select  ''      
' 다른곳에 사용된 리스트      
' db : accountlog
'-------------------------------------------------------------------------------------*/        
CREATE PROCEDURE [dbo].[p_GetCharacterPlayTime_Select]      
 @SearchText nvarchar(50) = 0 --파라미터 설명       
AS      
SET NOCOUNT ON        
SET LOCK_TIMEOUT 10000      
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.      
      
DECLARE @arg int      
--매개변수 검사      
 IF @SearchText IS NULL       
 BEGIN         
  PRINT @SearchText+'1과 없습니다.'        
  RETURN -1        
 END      
       
       
 declare @strSql nvarchar(4000)      
 --프로시저 소스 코드       
 set @strSql = ''      
 set @strSql = @strSql + ' select nCharNo, [sID], rtrim(nPlayMin/60)+ ''H '' + rtrim(nPlayMin%60)+''M'' as PlayTime, nPlayMin from W00DATA.W00_Data.dbo.tCharacter '      
 set @strSql = @strSql + ' where nAdminLevel <> 100 and nExp > 0 and bDeleted = 0 '      
       
 if(@SearchText <> '' )      
 begin      
  set @strSql = @strSql + ' and sID like ' + '''' + '%' + @SearchText  + '%' + ''''      
 end      
 set @strSql = @strSql + 'order by nPlayMin desc '  
      
 exec SP_executeSql @strSql


GO
/****** Object:  StoredProcedure [dbo].[p_GetDayUniqActive_Select]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------        
' 작성자 : 용효빈      
' 작성일 : 2011-07-28      
' 설  명 : 일일통계관련 결과를 가져온다. 유니크와 액티브      
' 수정일/ 수정한 사람/ 수정한 내용      
' 예  제 : Exec p_GetDayUniqActive_Select '', '', 2     
' 다른곳에 사용된 리스트  
' db : accountlog    
'-------------------------------------------------------------------------------------*/        
CREATE PROCEDURE [dbo].[p_GetDayUniqActive_Select]       
 @YearParm nvarchar(4) = 0 --파라미터 설명      
 ,@MonthParm nvarchar(2) = 0 --파라미터 설명      
 ,@Flat int = 0 --파라미터 설명      
 ,@debug bit = 0  --디버그용도/ 배치성 이라면 꼭 설정하는것이 좋습니다.      
AS      
SET NOCOUNT ON        
SET LOCK_TIMEOUT 10000      
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.      
      
DECLARE @arg int      
--매개변수 검사      
 IF @YearParm IS NULL OR @MonthParm IS NULL        
 BEGIN         
  PRINT @YearParm+'1과 '+@MonthParm+'없습니다.'        
  RETURN -1        
 END      
 -- @Flat < 1:일별 유니크, 2:일별 액티브 >      
 --프로시저 소스 코드      
 if(@YearParm = '' or @MonthParm = '')      
 begin      
  set @YearParm = DATEPART(YY, getdate())      
  set @MonthParm = DATEPART(MM, getdate())      
 end      
       
 if(@Flat = 1)      
 begin      
  --============ 일별 유니크 수 구하는 쿼리 =================================      
  SELECT dDate, COUNT(DISTINCT(nUserNo)) AS nCnt      
    FROM (      
    SELECT CONVERT(NVARCHAR(10), dDate, 120) AS dDate, nUserNo      
   FROM dbo.tAccountLog WITH(NOLOCK)      
   WHERE  nType = 1) AS T1      
   where DatePart(YY, dDate) = @YearParm and   DatePart(MM, dDate) = @MonthParm      
   GROUP BY dDate      
   ORDER BY 1  desc    
  end      
        
  else    
  begin         
  --============ 일별 회원가입 유저 수 구하는 쿼리 =================================      
       
   select Convert(nvarchar(10), dDate,120) as ddate, count(*) as nCnt    
   from Account.dbo.tUser    
   where DatePart(YY, dDate) = @YearParm and   DatePart(MM, dDate) = @MonthParm       
   group by Convert(nvarchar(10), dDate,120)    
  order by ddate desc    
    
    
  select Sum(aaa.nCnt) as sumCnt  
  from  
  (  
     select Convert(nvarchar(10), dDate,120) as ddate, count(*) as nCnt    
   from Account.dbo.tUser    
   where DatePart(YY, dDate) = @YearParm and   DatePart(MM, dDate) = @MonthParm       
   group by Convert(nvarchar(10), dDate,120)     
   ) aaa    
    
        
  end


GO
/****** Object:  StoredProcedure [dbo].[p_GetYearMonthLIst_Select]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------    
' 작성자 : 용효빈  
' 작성일 : 2011-07-28  
' 설  명 : 검색조건 년도와 월을 가져온다.  
' 수정일/ 수정한 사람/ 수정한 내용  
' 예  제 : Exec p_GetYearMonthLIst_Select   
' 다른곳에 사용된 리스트  
' db : accountlog 
'-------------------------------------------------------------------------------------*/    
Create PROCEDURE [dbo].[p_GetYearMonthLIst_Select]   
AS  
SET NOCOUNT ON    
SET LOCK_TIMEOUT 10000  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.  
  
DECLARE @arg int  
--매개변수 검사  
   
 --프로시저 소스 코드  
 select distinct DATEPART(YY, ddate) as [Year] from tAccountLog WHERE  nType = 1   
 select distinct DATEPART(MM, ddate) as [Month] from tAccountLog WHERE  nType = 1    
  
 -- 프로시저의 끝


GO
/****** Object:  StoredProcedure [dbo].[p_HackScan_Add]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_HackScan_Add]
/*
 HackScan Add
 input:
  ｽｺﾄｵｵﾈ ﾁ､ｺｸ ﾃﾟｰ｡
 output: 
  nRet,  0 = OK, ?=Error code
*/
@nUserNo	int,
@nWorldNo	tinyint,
@nCharNo	int,
@sIP		char(20),
@nFlag		tinyint,
@sCharID	nvarchar(40),
@sDesc	nvarchar(100),
@nRet		int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	INSERT tHackScan( nUserNo, nWorldNo, nCharNo, sIP, nFlag, sCharID, sDesc )
	VALUES            ( @nUserNo, @nWorldNo, @nCharNo, @sIP, @nFlag, @sCharID, @sDesc )
	SET @nRet = @@ERROR
	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_IPBlock_Add]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_IPBlock_Add] 
/*
 IP Block Add
 input:
  ﾃﾟｰ｡ﾇﾒ ｺ昞ｰIP ｴ・ｪ
 output: 
  nRet,  0 = OK, ?=Error code
*/
@sIPStart	nvarchar(20),
@sIPEnd	nvarchar(20),
@sDesc	nvarchar(30),
@nRet		int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET @nRet = -1
	INSERT tIPBlock( sIPStart, sIPEnd, sDesc )
	VALUES             ( @sIPStart, @sIPEnd, @sDesc )
	SET @nRet = @@ERROR
	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_IPBlock_Delete]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_IPBlock_Delete]
/*
 IP Block Delete
 input:
  ｺ昞ｰﾀﾌ ｵﾇｾ鋿ﾖｴﾂ ﾁ､ｺｸ ﾁｦｰﾅ
 output: 
  Selected records
*/
@sIP 		nvarchar(20),
@nRet		int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	DELETE FROM tIPBlock WHERE @sIP >= sIPStart AND @sIP <= sIPEnd
	SET @nRet = @@ERROR
	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_IPBlock_Get]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_IPBlock_Get]
/*
 IP Block Get
 input:
  ｺ昞ｰﾀﾌ ｵﾇｾ鋿ﾖｴﾂ ﾁ､ｺｸ ｾ・
 output: 
  Selected records
*/
@sIP 		nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON
	SELECT sIPStart, sIPEnd, sDesc, dDate FROM tIPBlock WHERE @sIP >= sIPStart AND @sIP <= sIPEnd
	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_IPBlock_GetList]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_IPBlock_GetList]
/*
 IP Block Get
 input:
  ｺ昞ｰﾀﾌ ｵﾇｾ鋿ﾖｴﾂ ｸﾏ
 output: 
  Selected records
*/
AS
BEGIN
	SET NOCOUNT ON
	SELECT sIPStart, sIPEnd, sDesc, dDate FROM tIPBlock
	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_IPBlock_IsBlock]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[p_IPBlock_IsBlock]
/*
 Is IP block ?
 input:
  IP
 output: 
  bIsBlock, 0=No block, 1=Block
*/
	@nUserNo    int
,	@sIP 		nvarchar(20)
,	@bIsBlock	tinyint OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SET @bIsBlock = 0

	IF EXISTS(SELECT sIPStart FROM tIPBlock WHERE @sIP >= sIPStart AND @sIP <= sIPEnd) BEGIN
			SET @bIsBlock = 1
	END

	SET NOCOUNT OFF
END
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Add]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-03-06
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	ｽﾅｰ晙酩ﾗﾀｻ ﾃﾟｰ｡ﾇﾑｴﾙ.
==================================================*/
CREATE PROCEDURE [dbo].[p_Report_Add] 
@nReporter int,
@sReporterCharID nvarchar(16),
@sHarmer nvarchar(16),
@nWorldNo tinyint,
@sReportType nvarchar(16),
@sReason nvarchar(512),
@sChatLog text,
@nReportRegNo int OUTPUT 
AS
SET NOCOUNT ON
DECLARE	@ret	int
SET 		@ret = 0
--1ｽﾃｰ｣ｿ｡ 1ﾈｸｸｸ ｰ｡ｴﾉﾇﾕｴﾏｴﾙ.
IF EXISTS (SELECT nReporter FROM tReport(NOLOCK) 
		WHERE 	nReporter = @nReporter AND 
			nWorldNo=@nWorldNo AND
			dReportDate > dateadd(hour, -1, getdate()) )
BEGIN
	SET @nReportRegNo = -1
	RETURN @ret
END
--ｵｿﾀﾏ ﾄｳｸｯﾅﾍｿ｡ ｴ・ﾘｼｭｴﾂ 1ﾀﾏ 1ﾈｸｸｸ ｰ｡ｴﾉﾇﾕｴﾏｴﾙ.
IF EXISTS (SELECT nReporter FROM tReport(NOLOCK) 
		WHERE 	nReporter = @nReporter AND 
			nWorldNo=@nWorldNo AND
			dReportDate > dateadd(day, -1, getdate()) AND
			sHarmer = @sHarmer)
BEGIN
	SET @nReportRegNo = -2
	RETURN @ret
END
INSERT tReport (sReportType,nReporter,sReporterCharID,sHarmer,nWorldNo,sReason,sChatLog)
VALUES (@sReportType,@nReporter, @sReporterCharID, @sHarmer, @nWorldNo,@sReason,@sChatLog )
SET @ret = @@error
IF @ret <> 0 
	SET @nReportRegNo = 0
ELSE
	SET @nReportRegNo = @@IDENTITY
RETURN @ret


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Deal_OPT]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' ﾇﾔｼ・: p_Report_Deal_OPT
' ｳｻ  ｿ・: OPTool ｿ｡ｼｭ ｻ鄙・ ｽﾅｰ桒ﾑ ｳｻｿ・ｻ OPToolｷﾎ ﾃｳｸｮﾇﾔ.
' ﾀﾛｼｺﾀﾚ : ﾀﾌｺｴｱ・
' ﾀﾛｼｺﾀﾏ : 2006ｳ・04ｿ・25ﾀﾏ
' ｹ・ ﾁｯ : 1.0
' ﾀﾎ  ﾀﾚ :
'			input Parameter
'				@nRegNumer  : ﾃｳｸｮｵﾉ ｸｮﾆｮ ｳﾑｹ・
'				@nResult	: ﾃｳｸｮｰ皺・
'				@sRemark	: ｿ錞ｵﾀﾚ ﾀﾇｰﾟ
'
'			Output Parameter
'				@nRet		: 0 = Error, 1 = Success
'		
'			RETURN_VALUE
'
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[p_Report_Deal_OPT]
	@nRegNumber	int,
	@nResult	tinyint,
	@sRemark	nvarchar(128),
	
	@nRet		int OUTPUT
	
AS
SET NOCOUNT ON
UPDATE tReport SET 
	nResult			= @nResult,
	sRemark			= @sRemark
WHERE nRegNumber = @nRegNumber
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
	SET @nRet = 0
	RETURN
END
SET @nRet = 1


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Del]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-03-07
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	1ｽﾃｰ｣ ﾀﾌｳｻｿ｡ ｽﾅｰ昉ﾈ ｻ酩ﾗﾀｻ ﾃ・ﾒﾇﾑｴﾙ.
==================================================*/
CREATE PROCEDURE [dbo].[p_Report_Del]
@nRegNumber int,
@nRet smallint OUTPUT
AS
SET NOCOUNT ON
DECLARE	
@dReportDate	datetime,
@nResult	tinyint
--NoError(ErrorCode.hｿﾍ ｿｬｵｿｵﾊ)
SET @nRet = 0
SELECT @dReportDate=dReportDate, @nResult=nResult FROM tReport WHERE nRegNumber=@nRegNumber 
--ﾀﾟｸﾈ ｵ﨧ﾏｹ｣
IF @@ROWCOUNT = 0
BEGIN
	SET @nRet = 1
	RETURN @@error
END
--1ｽﾃｰ｣ ﾁｭ ｽﾅｰ昻ﾂ ﾃ・ﾒﾇﾒｼ・ｾｽ.
IF @dReportDate < DATEADD(hour, -1, GETDATE())
BEGIN
	SET @nRet = 2
	RETURN @@error
END
--ﾃｳｸｮｵﾈ ｰ皺嶸ﾂ ﾃ・ﾒﾇﾒ ｼ・ｾｽ
IF @nResult <> 0
BEGIN
	SET @nRet = 3
	RETURN @@error
END
DELETE FROM tReport WHERE nRegNumber=@nRegNumber
RETURN @@error


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Get]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==================================================
@author	lenasoft
@date	2006-03-07
@return	0		<ｼｺｰ・
	others		<ｽﾇﾆﾐ>
@brief	1ｽﾃｰ｣ ﾀﾌｳｻｿ｡ ｽﾅｰ昉ﾈ ｸﾏﾀｻ ｾ錞ﾂｴﾙ
==================================================*/
CREATE PROCEDURE [dbo].[p_Report_Get] 
@nReporter int,
@nWorldNo tinyint
AS
SET NOCOUNT ON
SELECT 	nRegNumber,nResult,sRemark  FROM tReport
WHERE 	nReporter = @nReporter AND nWorldNo=@nWorldNo AND
	dReportDate > dateadd(hour, -1, getdate()) 
RETURN @@error


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Get_Count_OPT]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' ﾇﾔｼ・: p_Report_Get_Count_OPT
' ｳｻ  ｿ・: OPTool ｿ｡ｼｭ ｻ鄙・ OPToolｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮｿ｡ ｸﾂｴﾂ ｸｮﾆｮﾀﾇ ｰｳｼｦ ｾ錞ﾈ.
' ﾀﾛｼｺﾀﾚ : ﾀﾌｺｴｱ・
' ﾀﾛｼｺﾀﾏ : 2006ｳ・04ｿ・13ﾀﾏ
' ｹ・ ﾁｯ : 1.0
' ﾀﾎ  ﾀﾚ :
'			input Parameter
'				@QueryExpr		: OPTool ｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮ
'			Output Parameter
				@Count			: ｼｭﾄ｡ｵﾈ ｵ･ﾀﾌﾅﾍﾀﾇ ｰｳｼ・
'				
'			RETURN_VALUE
'
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[p_Report_Get_Count_OPT]
	@QueryExpr nvarchar(256),
	@Count int OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLCMD nvarchar(512)
	SET @SQLCMD = N'SELECT @RowCount = COUNT(*) 
		FROM tReport ' + @QueryExpr
	EXEC sp_executesql @SQLCMD, N'@RowCount AS int OUTPUT', @RowCount = @Count OUTPUT
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_Report_Get_OPT]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' ﾇﾔｼ・: p_Report_Get_OPT
' ｳｻ  ｿ・: OPTool ｿ｡ｼｭ ｻ鄙・ OPToolｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮｿ｡ ｸﾂｴﾂ ｸｮﾆｮｸｦ ｾ錞ﾈ.
' ﾀﾛｼｺﾀﾚ : ﾀﾌｺｴｱ・
' ﾀﾛｼｺﾀﾏ : 2006ｳ・04ｿ・13ﾀﾏ
' ｹ・ ﾁｯ : 1.0
' ﾀﾎ  ﾀﾚ :
'			input Parameter
'				@QueryExpr		: OPTool ｿ｡ｼｭ ﾀﾔｷﾂﾇﾑ ﾄｮｹｮ
'			Output Parameter
'		
'			RETURN_VALUE
'
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[p_Report_Get_OPT]
	@QueryExpr nvarchar(256)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQLCMD nvarchar(512)
	SET @SQLCMD = N'SELECT nRegNumber, sReportType, nReporter, sReporterCharID, sHarmer, nWorldNo, sReason, sChatLog, dReportDate, nResult, sRemark 
		FROM tReport ' + @QueryExpr
	EXEC sp_executesql @SQLCMD
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Connection_getWorldUserList]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Connection_getWorldUserList]
/* Re-Built by Myth // 2012.05

Get ActiveUsers for each World

@nRet <> 0 	// Error
@nRet = 0 	// No Error
*/
@nWorldNo tinyint,
@nRet int OUTPUT
AS
BEGIN
SET NOCOUNT ON

SELECT nUserNo FROM tActiveUsers WHERE nWorldNo = @nWorldNo

SET @nRet = @@ROWCOUNT

END


GO
/****** Object:  StoredProcedure [dbo].[usp_Connection_setClearAll]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Connection_setClearAll]
/* Re-Built by Myth // 2012.05

Clear all active users from tActiveUsers

@nRet <> 0 	// Error
@nRet = 0 	// No Error
*/
@nRet int OUTPUT
AS
BEGIN
SELECT @@ROWCOUNT FROM tAccountLog
SET @nRet = @@ROWCOUNT
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Connection_setClearWorld]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Connection_setClearWorld]
/* Re-Built by Myth // 2012.05

Clear tActiveUsers when World is turned off

@nRet <> 0 	// Error
@nRet = 0 	// No Error
*/
@nWorldNo int,
@nRet int OUTPUT
AS
BEGIN
SET NOCOUNT ON

DELETE FROM tActiveUsers WHERE nWorldNo = @nWorldNo

SET @nRet = @@ERROR
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Connection_setLogin]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Connection_setLogin]
/* Re-built by Myth // 2012.05

Add user to ActiveUsers

@nRet <> 0 	// Error
@nRet = 0 	// No Error
*/
@nUserNo int,
@nWorldNo tinyint,
@nRet int OUTPUT
AS
BEGIN
SET NOCOUNT ON

INSERT INTO tActiveUsers ( nUserNo, nWorldNo, dLoginTime, nIsOnline )
VALUES 									( @nUserNo, @nWorldNo, getdate(), 1 )
SET @nRet = @@ERROR
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Connection_setLogout]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Connection_setLogout]
/* Re-built by Myth // 2012.05

Delete user from ActiveUsers on Logout

@nRet <> 0 	// Error
@nRet = 0 	// No Error
*/
@nUserNo int,
@nWorldNo tinyint,
@nRet int OUTPUT
AS
BEGIN
SET NOCOUNT ON

SET @nRet = -1

DELETE FROM tActiveUsers WHERE nUserNo = @nUserNo
SET @nRet = @@ERROR
END


GO
/****** Object:  StoredProcedure [dbo].[usp_EventiPad_setTicket]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_EventiPad_setTicket]
/*
Re-Built by Myth // 10.2012

Input:
	- Unknown
Output:
	- Unknown
*/
@nUnk1 int,
@nUnk2 int,
@nRet int OUTPUT
AS
BEGIN
SET @nRet = -1
END


GO
/****** Object:  StoredProcedure [dbo].[usp_GetDayUniqActive_Select]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------        
' 작성자 : OnNet Jeff      
' 작성일 : 2011-12-08      
' 설  명 : 일일통계관련 결과를 가져온다. 유니크와 액티브      
'-------------------------------------------------------------------------------------*/        
CREATE PROCEDURE [dbo].[usp_GetDayUniqActive_Select]       
 @BeginDate DATETIME,
 @EndDate  DATETIME
AS      
SET NOCOUNT ON        
SET LOCK_TIMEOUT 10000      
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.      
    
BEGIN      
	SELECT A.dDate as curDate, A.nCnt as ActiveUser, ISNULL(B.nCnt, 0) as UniqueUser, 
		Round((1 - (ISNULL(B.nCnt, 0)/Convert(Float,A.nCnt))) * 100, 1) as reEnterRate
	FROM 
	 (
	  SELECT dDate, COUNT(DISTINCT(nUserNo)) AS nCnt      
		FROM (      
		SELECT CONVERT(NVARCHAR(10), dDate, 120) AS dDate, nUserNo      
	   FROM dbo.tAccountLog WITH(NOLOCK)      
	   WHERE  nType = 1) AS T1
	   where dDate >= @BeginDate and dDate < @EndDate
	   GROUP BY dDate            
	   ) A Left Join 
		(
			select Convert(nvarchar(10), dDate,120) as ddate, count(*) as nCnt    
			from Account.dbo.tUser    
			where dDate >= @BeginDate and dDate < @EndDate
			group by Convert(nvarchar(10), dDate,120)    
		) B on A.dDate = B.ddate
	ORDER BY A.dDate ASC
END


GO
/****** Object:  StoredProcedure [dbo].[usp_Log_setLogout]    Script Date: 12/20/2015 6:07:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 2. ﾀ惕衂ﾁｷﾎｽﾃﾀ・ｻｼｺ(ｱ箙ﾏ)
CREATE PROCEDURE [dbo].[usp_Log_setLogout]
	@WORLD_NO TINYINT		-- ｼｭｹ・
,	@USER_NO INT			-- ﾀｯﾀ・ｹ｣
,	@CHAR_NO INT			-- ﾄｳｸｯﾅﾍ ｹ｣
,	@CLASS TINYINT			-- ﾄｳｸｯﾅﾍ ﾅｬｷ｡ｽｺ
,	@LEVEL SMALLINT			-- ﾄｳｸｯﾅﾍ ﾇ邱ｹｺｧ
,	@EXPERIENCE BIGINT		-- ﾈｹｵ・ｰ貮霪｡
,	@PLAYTIME SMALLINT		-- ﾇﾃｷｹﾀﾌｽﾃｰ｣
,	@RET INT = 0 OUTPUT		-- ｸｮﾅﾏｰｪ(0:ｽﾇﾆﾐ/1:ｼｺｰ・
AS
BEGIN
	SET NOCOUNT ON
--	SET LOCKTIME_OUT 5000
	INSERT INTO tLogout (worldNo, userNo, charNo, class, level, experience, playTime)
	VALUES (@WORLD_NO, @USER_NO, @CHAR_NO, @CLASS, @LEVEL, @EXPERIENCE, @PLAYTIME)
	IF @@ERROR <> 0 OR @@IDENTITY = 0
		SET @RET = 0
	ELSE
		SET @RET = 1
	SET NOCOUNT OFF
--	SET LOCKTIME_OUT -1
END


GO
USE [master]
GO
ALTER DATABASE [AccountLog] SET  READ_WRITE 
GO
