USE [master]
GO
/****** Object:  Database [StatisticsData]    Script Date: 12/20/2015 6:10:17 PM ******/
CREATE DATABASE [StatisticsData]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'StatisticsData', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\StatisticsData.mdf' , SIZE = 21504KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StatisticsData_log', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\StatisticsData_1.ldf' , SIZE = 241216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StatisticsData] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [StatisticsData].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [StatisticsData] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [StatisticsData] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [StatisticsData] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [StatisticsData] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [StatisticsData] SET ARITHABORT OFF 
GO
ALTER DATABASE [StatisticsData] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [StatisticsData] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [StatisticsData] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [StatisticsData] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [StatisticsData] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [StatisticsData] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [StatisticsData] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [StatisticsData] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [StatisticsData] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [StatisticsData] SET  DISABLE_BROKER 
GO
ALTER DATABASE [StatisticsData] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [StatisticsData] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [StatisticsData] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [StatisticsData] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [StatisticsData] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [StatisticsData] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [StatisticsData] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [StatisticsData] SET RECOVERY FULL 
GO
ALTER DATABASE [StatisticsData] SET  MULTI_USER 
GO
ALTER DATABASE [StatisticsData] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [StatisticsData] SET DB_CHAINING OFF 
GO
ALTER DATABASE [StatisticsData] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [StatisticsData] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [StatisticsData] SET DELAYED_DURABILITY = DISABLED 
GO
USE [StatisticsData]
GO
/****** Object:  User [Stormy-PC\Administrator]    Script Date: 12/20/2015 6:10:17 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:10:17 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:10:17 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:10:17 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
/****** Object:  Table [dbo].[tAge]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tAge](
	[nLogNo] [int] IDENTITY(1,1) NOT NULL,
	[sLogDate] [nvarchar](10) NOT NULL,
	[nAge] [tinyint] NOT NULL,
	[nCount] [int] NOT NULL,
	[nYear] [smallint] NOT NULL,
	[nMonth] [tinyint] NOT NULL,
	[nDay] [tinyint] NOT NULL,
	[nWeek] [tinyint] NOT NULL,
	[nWeekDay] [tinyint] NOT NULL,
 CONSTRAINT [PK_tAge_NC] PRIMARY KEY NONCLUSTERED 
(
	[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
/****** Object:  Table [dbo].[tDayActiveUser]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDayActiveUser](
	[nIndex] [int] NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nActiveCount] [int] NOT NULL,
	[nNetmarbleCount] [int] NOT NULL,
	[nTotalUserCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] 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].[tDayClassLevel]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDayClassLevel](
	[nIndex] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nWorld] [tinyint] NOT NULL,
	[nClass] [tinyint] NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[nCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] 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].[tDayClassLevelPlay]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDayClassLevelPlay](
	[nIndex] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nWorld] [tinyint] NOT NULL,
	[nClass] [tinyint] NOT NULL,
	[nLevel] [smallint] NOT NULL,
	[nPlayMin] [int] NOT NULL,
	[nConnectCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] 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].[tDayUser]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDayUser](
	[nIndex] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL,
	[nUserCount] [int] NOT NULL,
	[nConnectCount] [int] NOT NULL,
 CONSTRAINT [PK__tDayUser__5070F446] PRIMARY KEY CLUSTERED 
(
	[nIndex] 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].[tGender]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tGender](
	[nLogNo] [int] IDENTITY(1,1) NOT NULL,
	[sLogDate] [nvarchar](10) NOT NULL,
	[nGender] [tinyint] NOT NULL,
	[nCount] [int] NOT NULL,
	[nYear] [smallint] NOT NULL,
	[nMonth] [tinyint] NOT NULL,
	[nDay] [tinyint] NOT NULL,
	[nWeek] [tinyint] NOT NULL,
	[nWeekDay] [tinyint] NOT NULL,
 CONSTRAINT [PK_tGender_NC] PRIMARY KEY NONCLUSTERED 
(
	[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
/****** Object:  Table [dbo].[tLocation]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tLocation](
	[nLogNo] [int] IDENTITY(1,1) NOT NULL,
	[sLogDate] [nvarchar](10) NOT NULL,
	[sLocation] [nvarchar](2) NOT NULL,
	[nCount] [int] NOT NULL,
	[nYear] [smallint] NOT NULL,
	[nMonth] [tinyint] NOT NULL,
	[nDay] [tinyint] NOT NULL,
	[nWeek] [tinyint] NOT NULL,
	[nWeekDay] [tinyint] NOT NULL,
 CONSTRAINT [PK_tLocation_NC] PRIMARY KEY NONCLUSTERED 
(
	[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
/****** Object:  Table [dbo].[TopCharacter]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TopCharacter](
	[nUserNo] [int] NULL,
	[nLevel] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tSameTimeChar]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSameTimeChar](
	[nIndex] [int] IDENTITY(1,1) NOT NULL,
	[dDate] [datetime] NOT NULL CONSTRAINT [DF__tSameTime__dDate__52593CB8]  DEFAULT (getdate()),
	[nWorld] [tinyint] NOT NULL,
	[nCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] 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].[tSameTimeCharClass]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSameTimeCharClass](
	[nIndex] [int] NOT NULL,
	[nClass] [tinyint] NOT NULL,
	[nCount] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[nIndex] ASC,
	[nClass] 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
ALTER TABLE [dbo].[tDayClassLevel] ADD  CONSTRAINT [DF__tDayClass__dDate__46E78A0C]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tDayClassLevelPlay] ADD  CONSTRAINT [DF__tDayClass__dDate__49C3F6B7]  DEFAULT (getdate()) FOR [dDate]
GO
ALTER TABLE [dbo].[tDayUser] ADD  CONSTRAINT [DF__tDayUser__dDate__4F7CD00D]  DEFAULT (getdate()) FOR [dDate]
GO
/****** Object:  StoredProcedure [dbo].[p_DayClassLevel_Add]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayClassLevel_Add    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayClassLevel_Add] 
/*
 DayClassLevel add record
 input:
  See SQL
 output: 
  nIndex = 추가된 번호(에러시 = 0 )
*/
@nWorld	tinyint,
@nClass	tinyint,
@nLevel smallint,
@nCount	int,
@nIndex		int OUTPUT
AS
SET NOCOUNT ON
SET @nIndex = 0
INSERT tDayClassLevel (  nWorld,  nClass,  nLevel,  nCount )
VALUES                ( @nWorld, @nClass, @nLevel, @nCount )
SET @nIndex = @@IDENTITY
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayClassLevel_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayClassLevel_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayClassLevel_Query] 
/*
  DayClassLevel Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nIndex, dDate, nWorld, nClass, nLevel, nCount FROM tDayClassLevel WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayClassLevelPlay_Add]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayClassLevelPlay_Add    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayClassLevelPlay_Add] 
/*
 DayClassLevelPlay add record
 input:
  See SQL
 output: 
  nIndex = 추가된 번호(에러시 = 0 )
*/
@nWorld	tinyint,
@nClass	tinyint,
@nLevel smallint,
@nPlayMin		int,
@nConnectCount	int,
@nIndex		int OUTPUT
AS
SET NOCOUNT ON
SET @nIndex = 0
INSERT tDayClassLevelPlay (  nWorld,  nClass,  nLevel,  nPlayMin,  nConnectCount )
VALUES                    ( @nWorld, @nClass, @nLevel, @nPlayMin, @nConnectCount)
SET @nIndex = @@IDENTITY
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayClassLevelPlay_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayClassLevelPlay_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayClassLevelPlay_Query] 
/*
  DayClassLevelPlay Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nIndex, dDate, nWorld, nClass, nLevel, nPlayMin, nConnectCount FROM tDayClassLevelPlay WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayConnectionTime]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_DayConnectionTime] 
AS
SET NOCOUNT ON
declare @nMon int
IF DAY(GETDATE()) = 1
set @nMon =MONTH(GETDATE())-1 
ELSE 
set @nMon = MONTH(GETDATE()) 
select min(nPlayMin) nMin, max(nPlayMin) nMax, avg(nPlayMin) nAvg, nWorldNo, DAY(dDate) nDay 
from [AccountLog].[dbo].[tAccountLog]			
where MONTH(dDate) = @nMon and nType = 2 
group by nWorldNo, DAY(dDate) 
order by nWorldNo, DAY(dDate) 
return


GO
/****** Object:  StoredProcedure [dbo].[p_DayUser]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_DayUser]
/*
 Day User - 일일 개별 접속자, 접속횟수
 Desc:
	Update tDayUser Statstics table
	AccountLog.dbo.tAccountLog에서 마지막으로 로그인 날짜가 어제인 
	사람수, 접속횟수를 구해서 어제의 날짜로 테이블에 기록시킨다.
*/
AS
SET NOCOUNT ON
-- GameLog's nType for LOGOUT 
DECLARE @LoginID int	
SET		@LoginID = 1
-- 검사 시간을 얻는다.(어제의 날짜 범위)
DECLARE @Date		datetime
DECLARE @StartDate	datetime 
DECLARE @EndDate	datetime 
SET @Date = GETDATE()
SET @EndDate = STR( YEAR(@Date) ) + STR( MONTH(@Date) ) + STR( DAY(@Date) )
SET @StartDate = @EndDate - 1
-- 중복기록 방지를 위해 레코드 있는 경우 삭제(어제날짜)
DELETE tDayUser WHERE dDate = @StartDate
-- 접속자수, 접속횟수 얻기
DECLARE @nUserCount    int
DECLARE @nConnectCount int
SELECT @nUserCount = COUNT(DISTINCT(nUserNo)), @nConnectCount = COUNT(*) 
--FROM [ACCOUNT-LOG].AccountLog.dbo.tAccountLog 
FROM AccountLog.dbo.tAccountLog 
WHERE (nType = @LoginID) AND (dDate >= @StartDate) AND (dDate < @EndDate)
-- Table에 기록하기
DECLARE @Index int
exec p_DayUser_Add @nUserCount, @nConnectCount, @Index OUTPUT
IF @Index <> 0 UPDATE tDayUser SET dDate = @StartDate WHERE nIndex = @Index -- 날짜를 변경
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayUser_Add]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayUser_Add    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayUser_Add] 
/*
 DayUser add
 input:
  See SQL
 output: 
  nIndex = 추가된 번호(에러시 = 0 )
*/
@nUserCount		int,
@nConnectCount	int,
@nIndex		int OUTPUT
AS
SET NOCOUNT ON
SET @nIndex = 0
INSERT tDayUser (  nUserCount,  nConnectCount )
VALUES          ( @nUserCount, @nConnectCount )
SET @nIndex = @@IDENTITY
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_DayUser_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_DayUser_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_DayUser_Query] 
/*
  DayUser Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nIndex, dDate, nUserCount, nConnectCount FROM tDayUser WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_GetMasterGrid_Select]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------  
' 작성자 : 
' 작성일 : 
' 설  명 : 스크립트 목적
' 수정일/ 수정한 사람/ 수정한 내용
' 예  제 : Exec p_GetMasterGrid_Select '2011-07-14', '00'
' 다른곳에 사용된 리스트
'-------------------------------------------------------------------------------------*/  
CREATE PROCEDURE [dbo].[p_GetMasterGrid_Select]
	 @Part nvarchar(10)
	,@date nvarchar(2)
	,@debug	bit = 0		--디버그용도/ 배치성 이라면 꼭 설정하는것이 좋습니다.
AS
SET NOCOUNT ON		
SET LOCK_TIMEOUT 10000
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	--필요하다면 고립화 수준을 낮춥니다.

DECLARE @arg int
--매개변수 검사
	IF @Part IS NULL OR @date IS NULL  
	BEGIN   
	 PRINT @Part+'1과 '+@date+'없습니다.'  
	 RETURN -1  
	END
	
	--프로시저 소스 코드 
	--declare @Part nvarchar(10)
	--set @Part = '2011-07-04'
	--declare @date int
	--set @date = '17'

	declare @datePart nvarchar(25)
	if(@date <> '00')
	begin
	set @datePart = @Part + ' ' + Convert(nvarchar(2), @date-1) + ':00:00.000'
	end
	else
	begin
	set @datePart = @Part + ' ' + '23:00:00.000'
	end

	select * from tSameTimeChar
	where dDate >= Convert(datetime,@datePart)
	and dDate < Dateadd(hh, 1, @datePart)

	--@debug은 다음과 같이 활용합니다. 
	IF @debug = 1 
		print '작업시작 -------'


GO
/****** Object:  StoredProcedure [dbo].[p_GetYearDrodownList]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------          
' 작성자 :  용효빈       
' 작성일 :     2011-07-27    
' 설  명 : 일주일간 분당 접속자 중 하루에 가장 많이 접속한 인원의 시간을 보여준다.       
' 수정일/ 수정한 사람/ 수정한 내용        
' 예  제 : exec p_YearConUser_Select 2011   
' 다른곳에 사용된 리스트        
'-------------------------------------------------------------------------------------*/          
Create PROCEDURE [dbo].[p_GetYearDrodownList]      
AS        
SET NOCOUNT ON          
SET LOCK_TIMEOUT 10000        
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.        
        
DECLARE @arg int        
--매개변수 검사    
        
 --프로시저 소스 코드         


 select distinct DATEPART(YY, dDate) as yearDate     
 from dbo.tSameTimeChar


GO
/****** Object:  StoredProcedure [dbo].[p_MinConUser_Select]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------          
' 작성자 :  용효빈       
' 작성일 :     2011-07-27    
' 설  명 : 일주일간 분당 접속자 중 하루에 가장 많이 접속한 인원의 시간을 보여준다.       
' 수정일/ 수정한 사람/ 수정한 내용        
' 예  제 : exec p_MinConUser_Select   8, 0    
' 다른곳에 사용된 리스트        
'-------------------------------------------------------------------------------------*/          
CREATE PROCEDURE [dbo].[p_MinConUser_Select]   
	@Smonth int ,
	@Flat nchar(1) = 0   
AS        
SET NOCOUNT ON          
SET LOCK_TIMEOUT 10000        
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.        
        
DECLARE @arg int        
--매개변수 검사    
declare @beforeMonth int
declare @afterMonth int


         
 --프로시저 소스 코드         
if( @Flat = 0 )
begin
	set @afterMonth =  Convert(nvarchar(2), Dateadd(MM, 1, GETDATE()))
	set @beforeMonth =  Convert(nvarchar(2), Datepart(mm,GETDATE()))
end
else
begin
	set @afterMonth =  @Smonth + 1
	set @beforeMonth =   @Smonth
end


 select a.ddate, max(a.nCount) as nCount
 from         
 (
 select  Convert(nvarchar(10),dDate,120) as ddate , nCount        
 from dbo.tSameTimeChar 
 where Convert(nvarchar(2), Datepart(mm,dDate))  >=  @beforeMonth and Convert(nvarchar(2), Datepart(mm,dDate)) < @afterMonth     
 ) a 
 group by a.dDate
 order by a.dDate desc  
 
 

 
 --select aaa.dayDate, aaa.maxcount, bbb.subdate        
 --from        
 --(        
 --select convert(nvarchar(10),a.DayDate) as dayDate , max(a.ncount) as maxcount        
 --from        
 --(        
 --select convert(nvarchar(13),dDate,120) as DayDate, SUBSTRING(convert(nvarchar(13),dDate,120), 12,2) as subdate, sum(nCount) as ncount         
 --from dbo.tSameTimeChar        
 --group by convert(nvarchar(13),dDate,120)        
 --) a group by convert(nvarchar(10),a.DayDate)        
 --) aaa        
 --  inner join        
 --(        
 --select convert(nvarchar(13),dDate,120) as DayDate,convert(nvarchar(10),dDate,120) as chaDate,  SUBSTRING(convert(nvarchar(13),dDate,120), 12,2) as subdate, sum(nCount) as ncount         
 --from dbo.tSameTimeChar        
 --where nCount <> 0        
 --group by convert(nvarchar(13),dDate,120), convert(nvarchar(10),dDate,120)         
 ----order by DayDate        
 --) bbb on aaa.dayDate = bbb.chaDate and aaa.maxcount = bbb.ncount        
 --where  aaa.dayDate >= DateAdd(WEEK, -1, Convert(nvarchar(10),GETDATE(),120))        
         
 -- 프로시저의 끝


GO
/****** Object:  StoredProcedure [dbo].[p_SameTimeChar_Add]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_SameTimeChar_Add    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_SameTimeChar_Add] 
/*
 SameTimeChar add
 input:
  See SQL
 output: 
  nIndex = 추가된 번호(에러시 = 0 )
*/
@nWorld	tinyint,
@nCount		int,
@nIndex		int OUTPUT
AS
SET NOCOUNT ON
---
SET @nIndex = 0
 
INSERT tSameTimeChar (  nWorld,  nCount )
VALUES               ( @nWorld, @nCount )
SET @nIndex = @@IDENTITY
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_SameTimeChar_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_SameTimeChar_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_SameTimeChar_Query] 
/*
  SameTimeChar Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nIndex, dDate, nWorld, nCount FROM tSameTimeChar WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_SameTimeCharClass_Add]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' ﾀﾌ  ｸｧ : p_SameTimeCharClass_Add
' ﾀﾛｼｺﾀﾚ : Jin Chang Yong (luna-rainbow@onson.com)
' ﾀﾛｼｺﾀﾏ : 2011.06.03
' Description :
'   ﾁ｢ｼﾓﾁﾟﾀﾎ ﾅｬｷｹｽｺｺｰ ﾄﾉｸｯﾅﾍ ｼ・ﾀ惕・
' Parameter :
'    input Parameter
'       @nIndex : tSameTimeChar ﾅﾗﾀﾌｺ暲ﾇ ｾ鋗ﾀ ﾀﾎｵｦｽｺｿ｡ ﾇﾘｴ酩ﾏｴﾂ ﾁ､ｺｸ
'       @nClass : ﾅｬｷｹｽｺ ｹ｣
'       @nCount : ﾅｬｷｹｽｺｺｰ ﾀﾎｿ・
'       @nRet   : 
'   output Parameter
'       @nRet    : check SQL execute(valid/invalid)
'           = 0  : works ok
'           < 0  : error
'               -1000 : ﾀﾌｹﾌ ﾁｸﾀ酩ﾏｴﾂ ﾁ､ｺｸｸｦ ｶﾇ ﾃﾟｰ｡ﾇﾏｷﾁｰ・ﾇﾔ
'               -1001 : ﾄﾉｸｯﾅﾍ ﾅｬｷｹｽｺ ﾁ､ｺｸ ﾃﾟｰ｡ ｽﾇﾆﾐ
'    return Record
'       nCharNo
'-------------------------------------------------------------------------------------*/

CREATE PROC [dbo].[p_SameTimeCharClass_Add]
    @nIndex             INT,
    @nClass             TINYINT,
    @nCount             INT,
    @nRet               INT     OUTPUT
AS
BEGIN

    SET @nRet = 0


    -- ｰｰﾀｺ ﾁ､ｺｸｰ｡ ﾀﾌｹﾌ ｵ鮴錞ﾍ ﾀﾖｴﾂﾁ・ﾈｮﾀﾎ
    IF EXISTS ( SELECT * FROM tSameTimeCharClass WHERE nIndex = @nIndex AND nClass = @nClass )
    BEGIN
        SET @nRet = -1000
        RETURN
    END

    --  ﾄﾉｸｯﾅﾍ ﾅｬｷｹｽｺ ﾁ､ｺｸ ﾃﾟｰ｡
    INSERT INTO tSameTimeCharClass ( nIndex, nClass, nCount )
    VALUES ( @nIndex, @nClass, @nCount )

    IF @@ERROR <> 0
    BEGIN
        SET @nRet = -1001
        RETURN
    END

END


GO
/****** Object:  StoredProcedure [dbo].[p_UserAge_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_UserAge_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_UserAge_Query] 
/*
  UserAge Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nLogNo, nYear, nMonth, nDay, nWeek, nWeekDay, nAge, nCount FROM tAge WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_UserAgeReport]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_UserAgeReport    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_UserAgeReport]
	@S_DATE NVARCHAR(10) = '',
	@E_DATE NVARCHAR(10) = ''
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	DECLARE @S_YEAR AS INT
	DECLARE @S_MONTH AS INT
	DECLARE @S_DAY AS INT
	DECLARE @E_YEAR AS INT
	DECLARE @E_MONTH AS INT
	DECLARE @E_DAY AS INT
	IF @S_DATE = ''
		SET @S_DATE = DATEADD(dd, -1, GETDATE())
	IF @E_DATE = ''
		SET @E_DATE = DATEADD(dd, -1, GETDATE())
	SET @S_YEAR  = DATENAME(yyyy, @S_DATE)
	SET @S_MONTH = DATENAME(mm, @S_DATE)
	SET @S_DAY   = DATENAME(dd, @S_DATE)
	SET @E_YEAR  = DATENAME(yyyy, @E_DATE)
	SET @E_MONTH = DATENAME(mm, @E_DATE)
	SET @E_DAY   = DATENAME(dd, @E_DATE)
	PRINT '시작일 : ' + @S_DATE
	PRINT '종료일 : ' + @E_DATE
	PRINT @S_MONTH
	PRINT @E_MONTH
	SELECT CASE(nGender)
				WHEN 0 THEN '여자'
				WHEN 1 THEN '남자'
		   END AS [성별], nCount AS [인원]
	  FROM tGender(NOLOCK)
	 WHERE nYear >= @S_YEAR AND nMonth >= @S_MONTH AND nDay >= @S_DAY
	   AND nYear <= @E_YEAR AND nMonth <= @E_MONTH AND nDay <= @E_DAY
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END


GO
/****** Object:  StoredProcedure [dbo].[p_UserGender_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_UserGender_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_UserGender_Query] 
/*
  UserGender Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nLogNo, nYear, nMonth, nDay, nWeek, nWeekDay, nGender, nCount FROM tGender WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_UserGenderTest]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_UserGenderTest    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
--SELECT * FROM tGender 
--SELECT DATEADD(dd, -1, GETDATE())
CREATE  PROCEDURE [dbo].[p_UserGenderTest]
	@DATE	NVARCHAR(10) = ''
AS
BEGIN 
	DECLARE @YEAR AS INT
	DECLARE @MONTH AS INT
	DECLARE @DAY AS INT
	DECLARE @LOG_DATE AS NVARCHAR(10)
	DECLARE @BASE_DATE AS NVARCHAR(10)
	IF @DATE = ''
		SET @LOG_DATE = CONVERT(NVARCHAR(10), DATEADD(dd, -1, GETDATE()), 120)
	ELSE
		SET @LOG_DATE = @DATE
	PRINT @LOG_DATE
	PRINT '--------------'
	SET @YEAR  = DATENAME(yyyy, @LOG_DATE)
	SET @MONTH = DATENAME(mm, @LOG_DATE)
	SET @DAY   = DATENAME(dd, @LOG_DATE)
	SET @BASE_DATE = CONVERT(NVARCHAR(10), DATEADD(dd, 1, @LOG_DATE), 120)
	PRINT @BASE_DATE
	PRINT '--------------'
	PRINT @YEAR
	PRINT '--------------'
END
EXEC p_UserGender


GO
/****** Object:  StoredProcedure [dbo].[p_UserLocation_Query]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** 개체: 저장 프로시저 dbo.p_UserLocation_Query    스크립트 날짜: 2005-06-13 오후 8:20:09 ******/
CREATE PROCEDURE [dbo].[p_UserLocation_Query] 
/*
  UserLocation Query - 주어진 조건에 맞는 데이타를 모두검색해서 얻는다.
  input:
   - 주어진 조건절
  output:
   - Select된 데이타
*/
@WhereExpr nvarchar(256)
AS
SET NOCOUNT ON
DECLARE @SQLCMD nvarchar(512)
SET  @SQLCMD = N'SELECT nLogNo, nYear, nMonth, nDay, nWeek, nWeekDay, sLocation, nCount FROM tLocation WHERE ' + @WhereExpr
EXEC sp_executesql @SQLCMD
-- end


GO
/****** Object:  StoredProcedure [dbo].[p_YearConUser_Select]    Script Date: 12/20/2015 6:10:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------            
' 작성자 :  용효빈         
' 작성일 :     2011-07-27      
' 설  명 : 일주일간 분당 접속자 중 하루에 가장 많이 접속한 인원의 시간을 보여준다.         
' 수정일/ 수정한 사람/ 수정한 내용          
' 예  제 : exec p_YearConUser_Select 2011     
' 다른곳에 사용된 리스트          
'-------------------------------------------------------------------------------------*/            
CREATE PROCEDURE [dbo].[p_YearConUser_Select]     
 @Year int      
AS          
SET NOCOUNT ON            
SET LOCK_TIMEOUT 10000          
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --필요하다면 고립화 수준을 낮춥니다.          
          
DECLARE @arg int          
--매개변수 검사      
declare @beforeMonth int  
declare @afterMonth int  
           
 --프로시저 소스 코드           
  
 select CONVERT(nvarchar(7), a.dDate, 120) as ddate, nWorld, max(a.nCount) as maxcount  
 from           
 (  
 select   *        
 from dbo.tSameTimeChar   
 where Datepart(yy, GETDATE())  =  @Year    
 ) a   
 group by CONVERT(nvarchar(7), a.dDate, 120), nWorld 
 order by ddate desc


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