USE [master]
GO
/****** Object:  Database [Account]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE DATABASE [Account]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Account', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Account.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Account_log', FILENAME = N'D:\SQL SERVER DATA\SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Account_1.ldf' , SIZE = 63424KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Account] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Account].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Account] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Account] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Account] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Account] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Account] SET ARITHABORT OFF 
GO
ALTER DATABASE [Account] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Account] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Account] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Account] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Account] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Account] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Account] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Account] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Account] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Account] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Account] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Account] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Account] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Account] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Account] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Account] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Account] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Account] SET RECOVERY FULL 
GO
ALTER DATABASE [Account] SET  MULTI_USER 
GO
ALTER DATABASE [Account] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Account] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Account] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Account] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
ALTER DATABASE [Account] SET DELAYED_DURABILITY = DISABLED 
GO
USE [Account]
GO
/****** Object:  User [WIN-1BU452E8A64\Agentbavr2_yp0qa8q9]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [WIN-1BU452E8A64\Agentbavr2_yp0qa8q9] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [web_account#$12G]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [web_account#$12G] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[sys]
GO
/****** Object:  User [Stormy-PC\Administrator]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [Stormy-PC\Administrator] FOR LOGIN [Stormy-PC\Administrator] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [NT AUTHORITY\SYSTEM]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [HeroesCN]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [HeroesCN] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [acc_website]    Script Date: 12/20/2015 6:07:08 PM ******/
CREATE USER [acc_website] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [WIN-1BU452E8A64\Agentbavr2_yp0qa8q9]
GO
ALTER ROLE [db_owner] ADD MEMBER [web_account#$12G]
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_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 [HeroesCN]
GO
ALTER ROLE [db_owner] ADD MEMBER [acc_website]
GO
/****** Object:  Table [dbo].[tCash]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCash](
	[userNo] [int] NOT NULL,
	[cashtype] [tinyint] NOT NULL,
	[cash] [int] NOT NULL DEFAULT ((0)),
	[status] [bit] NOT NULL DEFAULT ((1)),
 CONSTRAINT [PK_tCash_CL] PRIMARY KEY CLUSTERED 
(
	[userNo] ASC,
	[cashtype] 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].[tCashLog]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tCashLog](
	[rowNo] [int] IDENTITY(1,1) NOT NULL,
	[userNo] [int] NOT NULL,
	[logType] [tinyint] NOT NULL,
	[cashType] [tinyint] NOT NULL,
	[cash] [int] NOT NULL,
	[reference] [nvarchar](20) NOT NULL,
	[vaildDate] [datetime] NULL,
	[issueDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tCashLog_NC] PRIMARY KEY NONCLUSTERED 
(
	[rowNo] 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].[tChargeItem]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tChargeItem](
	[rowNo] [int] IDENTITY(1,1) NOT NULL,
	[userNo] [int] NOT NULL,
	[orderNo] [nvarchar](1) NULL,
	[goodsNo] [int] NOT NULL,
	[amount] [int] NOT NULL DEFAULT ((1)),
	[registerDate] [datetime] NOT NULL DEFAULT (getdate()),
	[isDraw] [tinyint] NOT NULL DEFAULT ((0)),
	[drawDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_tChargeItem_NC] PRIMARY KEY NONCLUSTERED 
(
	[rowNo] 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].[tItem]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItem](
	[goodsNo] [int] IDENTITY(10001,1) NOT NULL,
	[billingNo] [nvarchar](1) NULL DEFAULT ((0)),
	[category] [nvarchar](10) NULL,
	[goodsNoCheck] [nvarchar](99) NULL DEFAULT ((0)),
	[name] [nvarchar](100) NULL,
	[imageName] [nvarchar](100) NULL,
	[itemType] [nvarchar](20) NULL,
	[unit] [nvarchar](99) NULL DEFAULT ((0)),
	[unitDescription] [nvarchar](999) NULL,
	[price] [nvarchar](99) NULL DEFAULT ((0)),
	[discount] [nvarchar](99) NULL DEFAULT ((0)),
	[discountStartDate] [datetime] NULL,
	[discountEndDate] [datetime] NULL,
	[isNew] [bit] NULL DEFAULT ((0)),
	[isPopular] [bit] NULL DEFAULT ((0)),
	[isRecommand] [bit] NULL DEFAULT ((0)),
	[isSell] [bit] NULL DEFAULT ((0)),
	[sellStartDate] [datetime] NULL,
	[sellEndDate] [datetime] NULL,
	[sellCount] [nvarchar](99) NULL DEFAULT ((0)),
	[registerDate] [datetime] NULL DEFAULT (getdate()),
	[description] [nvarchar](1000) NULL,
	[discount_price] [nvarchar](99) NULL,
 CONSTRAINT [PK_tItem_NC] PRIMARY KEY NONCLUSTERED 
(
	[goodsNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tItemCategory]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tItemCategory](
	[category] [nvarchar](10) NOT NULL,
	[name] [nvarchar](20) NOT NULL,
	[parent] [nvarchar](10) NULL,
	[status] [bit] NOT NULL DEFAULT ((1)),
	[registerDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_tItemCategory_CL] PRIMARY KEY CLUSTERED 
(
	[category] 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].[tMinorsPlaytime]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tMinorsPlaytime](
	[nUserNo] [int] NOT NULL,
	[bIsAdult] [bit] NOT NULL,
	[nPlaytime] [bigint] NOT NULL DEFAULT ((0)),
	[dLogoutDate] [datetime] NULL,
 CONSTRAINT [PK_tMinorsPlaytime_nUserNo_CL] 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].[tOrder]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tOrder](
	[orderNo] [int] IDENTITY(10001,1) NOT NULL,
	[orderDate] [datetime] NOT NULL,
	[orderType] [tinyint] NOT NULL,
	[userNo] [int] NOT NULL,
	[toUserNo] [int] NULL,
	[goodsNo] [int] NOT NULL,
	[goodsPrice] [int] NOT NULL,
	[amount] [int] NOT NULL,
	[discount] [int] NOT NULL,
	[orderPrice] [int] NOT NULL,
	[cashType] [tinyint] NOT NULL,
	[isPay] [bit] NOT NULL,
	[userIP] [nvarchar](30) NOT NULL,
	[pcbangNo] [int] NULL,
 CONSTRAINT [PK_tOrder_NC] PRIMARY KEY NONCLUSTERED 
(
	[orderNo] 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:  Index [IX_tOrder_orderDate_CL]    Script Date: 12/20/2015 6:07:09 PM ******/
CREATE CLUSTERED INDEX [IX_tOrder_orderDate_CL] ON [dbo].[tOrder]
(
	[orderDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tSerialKey]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tSerialKey](
	[userNo] [int] NOT NULL,
	[serialKey] [bigint] NOT NULL,
	[registerDate] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_tSerialKey_CL] PRIMARY KEY CLUSTERED 
(
	[userNo] 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].[tShop]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tShop](
	[nItemID] [int] NOT NULL,
	[sItemName] [varchar](64) NULL,
	[sItemDescription] [text] NULL,
	[nItemPrice] [int] NULL,
	[nItemGID] [int] NULL,
	[nItemCat] [int] NULL,
	[sItemImage] [varchar](64) NOT NULL,
	[nItemStatus] [int] NOT NULL,
	[nItemWaitTime] [int] NULL,
	[nIsSalary] [tinyint] NOT NULL CONSTRAINT [DF_tShop_nIsSalary]  DEFAULT ((0))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tUser]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tUser](
	[nUserNo] [int] IDENTITY(1,1) NOT NULL,
	[sUserID] [nvarchar](30) NOT NULL,
	[sUserPW] [nvarchar](20) NOT NULL,
	[sUserName] [nvarchar](10) NOT NULL,
	[bIsBlock] [bit] NOT NULL CONSTRAINT [DF__tUser__bIsBlock__014935CB]  DEFAULT ((0)),
	[bIsDelete] [bit] NOT NULL CONSTRAINT [DF__tUser__bIsDelete__023D5A04]  DEFAULT ((0)),
	[nAuthID] [tinyint] NOT NULL CONSTRAINT [DF__tUser__nAuthID__03317E3D]  DEFAULT ((1)),
	[sUserIP] [nvarchar](30) NOT NULL,
	[sEmail] [nvarchar](90) NULL,
	[nAge] [tinyint] NULL,
	[dDate] [datetime] NOT NULL CONSTRAINT [DF__tUser__dDate__0425A276]  DEFAULT (getdate()),
 CONSTRAINT [PK_tUser_CL] 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].[tUserAuth]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tUserAuth](
	[nAuthID] [tinyint] NOT NULL,
	[sAuthName] [nvarchar](11) NOT NULL,
	[bIsLoginAble] [tinyint] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_tUserAuth_CL] PRIMARY KEY CLUSTERED 
(
	[nAuthID] 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].[tUserBlock]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tUserBlock](
	[nBlockNo] [int] IDENTITY(1,1) NOT NULL,
	[nUserNo] [int] NOT NULL,
	[nType] [tinyint] NOT NULL,
	[dBlockDate] [datetime] NOT NULL,
	[nTimeValue] [int] NOT NULL,
	[dEndDate] [datetime] NOT NULL,
	[bStatus] [bit] NOT NULL,
	[sComment] [nvarchar](200) NULL,
 CONSTRAINT [PK_tUserBlock_NC] PRIMARY KEY NONCLUSTERED 
(
	[nBlockNo] 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:  Index [IDX_tUserBlock_nUserNo_CL]    Script Date: 12/20/2015 6:07:09 PM ******/
CREATE CLUSTERED INDEX [IDX_tUserBlock_nUserNo_CL] ON [dbo].[tUserBlock]
(
	[nUserNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[tUserOption]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tUserOption](
	[nUserNo] [int] NOT NULL,
	[sEmail] [nvarchar](90) NULL,
	[sJuminNo] [nvarchar](50) NULL,
	[sPhoneNo] [nvarchar](20) NULL,
	[nZipNo] [int] NULL,
	[sAddress] [nvarchar](90) NULL,
	[bIsMail] [bit] NOT NULL,
	[sChannel] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_tUserOption_CL] 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:  View [dbo].[v_item]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[v_item] AS 
SELECT
dbo.tItem.goodsNo,
dbo.tItem.billingNo,
dbo.tItem.category,
dbo.tItem.groupNo,
dbo.tItem.name,
dbo.tItem.imageName,
dbo.tItem.itemType,
dbo.tItem.unit,
dbo.tItem.unitDescription,
dbo.tItem.price,
dbo.tItem.discount,
dbo.tItem.discountStartDate,
dbo.tItem.discountEndDate,
dbo.tItem.isNew,
dbo.tItem.isPopular,
dbo.tItem.isRecommand,
dbo.tItem.isSell,
dbo.tItem.sellStartDate,
dbo.tItem.sellEndDate,
dbo.tItem.sellCount,
dbo.tItem.registerDate,
dbo.tItem.description,
dbo.tItemCategory.category AS category_id,
dbo.tItemCategory.name AS category_name,
dbo.tItem.discount_price

FROM
dbo.tItem
INNER JOIN dbo.tItemCategory ON dbo.tItem.category = dbo.tItemCategory.category
WHERE
dbo.tItem.sellStartDate < getdate() AND
dbo.tItem.sellEndDate > getdate()

GO
/****** Object:  Index [IDX_tCashLog_CL]    Script Date: 12/20/2015 6:07:09 PM ******/
CREATE NONCLUSTERED INDEX [IDX_tCashLog_CL] ON [dbo].[tCashLog]
(
	[userNo] ASC,
	[logType] ASC,
	[cashType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_tUser_sUserID_sUserPW_NC]    Script Date: 12/20/2015 6:07:09 PM ******/
CREATE NONCLUSTERED INDEX [IX_tUser_sUserID_sUserPW_NC] ON [dbo].[tUser]
(
	[sUserID] ASC,
	[sUserPW] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tCashLog] ADD  DEFAULT (getdate()) FOR [issueDate]
GO
ALTER TABLE [dbo].[tOrder] ADD  DEFAULT (getdate()) FOR [orderDate]
GO
ALTER TABLE [dbo].[tOrder] ADD  DEFAULT ((0)) FOR [orderType]
GO
ALTER TABLE [dbo].[tOrder] ADD  DEFAULT ((1)) FOR [amount]
GO
ALTER TABLE [dbo].[tOrder] ADD  DEFAULT ((0)) FOR [discount]
GO
ALTER TABLE [dbo].[tOrder] ADD  DEFAULT ((0)) FOR [isPay]
GO
ALTER TABLE [dbo].[tUserBlock] ADD  DEFAULT ((1)) FOR [nType]
GO
ALTER TABLE [dbo].[tUserBlock] ADD  DEFAULT (getdate()) FOR [dBlockDate]
GO
ALTER TABLE [dbo].[tUserBlock] ADD  DEFAULT ((1)) FOR [nTimeValue]
GO
ALTER TABLE [dbo].[tUserBlock] ADD  DEFAULT (getdate()) FOR [dEndDate]
GO
ALTER TABLE [dbo].[tUserBlock] ADD  DEFAULT ((1)) FOR [bStatus]
GO
ALTER TABLE [dbo].[tUserOption] ADD  DEFAULT ((0)) FOR [bIsMail]
GO
ALTER TABLE [dbo].[tUserOption] ADD  DEFAULT ('ONSON') FOR [sChannel]
GO
ALTER TABLE [dbo].[tCash]  WITH CHECK ADD  CONSTRAINT [FK__tCash__userNo__173876EA] FOREIGN KEY([userNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tCash] CHECK CONSTRAINT [FK__tCash__userNo__173876EA]
GO
ALTER TABLE [dbo].[tCashLog]  WITH CHECK ADD  CONSTRAINT [FK__tCashLog__userNo__1B0907CE] FOREIGN KEY([userNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tCashLog] CHECK CONSTRAINT [FK__tCashLog__userNo__1B0907CE]
GO
ALTER TABLE [dbo].[tItem]  WITH CHECK ADD  CONSTRAINT [FK_tItem_tItemCategory_parent] FOREIGN KEY([category])
REFERENCES [dbo].[tItemCategory] ([category])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tItem] CHECK CONSTRAINT [FK_tItem_tItemCategory_parent]
GO
ALTER TABLE [dbo].[tItemCategory]  WITH CHECK ADD  CONSTRAINT [FK_tItemCategory_tItemCategory_parent] FOREIGN KEY([parent])
REFERENCES [dbo].[tItemCategory] ([category])
GO
ALTER TABLE [dbo].[tItemCategory] CHECK CONSTRAINT [FK_tItemCategory_tItemCategory_parent]
GO
ALTER TABLE [dbo].[tOrder]  WITH CHECK ADD  CONSTRAINT [FK_tOrder_tUser_toUserNo] FOREIGN KEY([toUserNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tOrder] CHECK CONSTRAINT [FK_tOrder_tUser_toUserNo]
GO
ALTER TABLE [dbo].[tOrder]  WITH CHECK ADD  CONSTRAINT [FK_tOrder_tUser_userNo] FOREIGN KEY([userNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tOrder] CHECK CONSTRAINT [FK_tOrder_tUser_userNo]
GO
ALTER TABLE [dbo].[tSerialKey]  WITH CHECK ADD  CONSTRAINT [FK_tSerialKey_tUser_userNo] FOREIGN KEY([userNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tSerialKey] CHECK CONSTRAINT [FK_tSerialKey_tUser_userNo]
GO
ALTER TABLE [dbo].[tUser]  WITH CHECK ADD  CONSTRAINT [FK_tUser_tUserAuth_nAuthID] FOREIGN KEY([nAuthID])
REFERENCES [dbo].[tUserAuth] ([nAuthID])
GO
ALTER TABLE [dbo].[tUser] CHECK CONSTRAINT [FK_tUser_tUserAuth_nAuthID]
GO
ALTER TABLE [dbo].[tUserBlock]  WITH CHECK ADD  CONSTRAINT [FK_tUserBlock_tUser_nUserNo] FOREIGN KEY([nUserNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tUserBlock] CHECK CONSTRAINT [FK_tUserBlock_tUser_nUserNo]
GO
ALTER TABLE [dbo].[tUserOption]  WITH CHECK ADD  CONSTRAINT [FK_tUserOption_tUser_nUserNo] FOREIGN KEY([nUserNo])
REFERENCES [dbo].[tUser] ([nUserNo])
GO
ALTER TABLE [dbo].[tUserOption] CHECK CONSTRAINT [FK_tUserOption_tUser_nUserNo]
GO
/****** Object:  StoredProcedure [dbo].[usp_Cash_getCash]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' 捞  抚 : usp_Cash_getCash
' 累己磊 : Ko Dong Gyun(kodong@gmail.com)
' 累己老 : 2007斥 06岿 14老
' Description :
'   蜡历狼 某浆儡咀阑 炼雀窍绰 历厘橇肺矫历
' Parameter :
'	input Parameter
'		@userNo   : 蜡历锅龋
'		@cashType : 某浆辆幅(0:某浆/1:捞亥飘某浆)
'	return Value
'		RecordSet
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Cash_getCash]
	@userNo   int
,	@cashType tinyint = 1
,	@CASH	  int = 0 output
AS
BEGIN
	SET NOCOUNT ON

	SET @CASH = 0
	SELECT @CASH = ISNULL(cash, 0) FROM tCash 
	 WHERE userNo = @userNo AND cashType = @cashType AND status = 0

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Cash_setCash]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*--------------------------------------------------------------------------------------
' 捞  抚 : usp_Cash_setCash
' 累己磊 : Ko Dong Gyun(kodong@gmail.com)
' 累己老 : 2007斥 06岿 14老
' Description :
'   蜡历狼 某浆甫 面傈窍绰 橇肺矫历
' Parameter :
'	input Parameter
'		@userNo    : 蜡历锅龋
'		@cashType  : 某浆辆幅(1:某浆/2:捞亥飘某浆)
'		@cash      : 陛咀
'		@reference : 林巩锅龋(捞亥飘某浆老锭绰 捞亥飘锅龋(20070629_01)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Cash_setCash]
	@userNo    int
,	@cashType  tinyint
,	@cash      int
,	@reference nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	BEGIN TRAN

	IF EXISTS(SELECT userNo FROM tCash WHERE userNo = @userNo AND cashType = @cashType)
		BEGIN
			UPDATE tCash SET cash = cash + @cash WHERE userNo = @userNo AND cashType = @cashType
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				BEGIN
					ROLLBACK TRAN
					RETURN (-1)
				END
		END
	ELSE
		BEGIN
			INSERT INTO tCash (userNo, cashType, cash)
			VALUES (@userNo, @cashType, @cash)
			IF @@ERROR <> 0
				BEGIN
					ROLLBACK TRAN
					RETURN (-1)
				END
		END

	INSERT INTO tCashLog (userNo, logType, cashType, cash, reference)
	VALUES (@userNo, 1, @cashType, @cash, @reference)
	IF @@ERROR <> 0 OR @@IDENTITY = 0
		BEGIN
			ROLLBACK TRAN
			RETURN (-1)
		END
	ELSE
		BEGIN
			COMMIT TRAN
			RETURN (0)
		END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Cash_useCash]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
	

/*--------------------------------------------------------------------------------------
' 捞  抚 : usp_Cash_useCash
' 累己磊 : Ko Dong Gyun(kodong@gmail.com)
' 累己老 : 2007斥 06岿 14老
' Description :
'   蜡历狼 某浆甫 瞒皑(荤侩)窍绰 橇肺矫历
' Parameter :
'	input Parameter
'		@userNo : 蜡历锅龋
'		@type   : 某浆辆幅(0:某浆/1:捞亥飘某浆)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Cash_useCash]
	@userNo    int
,	@cashType  tinyint
,	@cash      int
,	@reference nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @REMAIN_CASH INT
	SET @REMAIN_CASH = 0

	SELECT @REMAIN_CASH = ISNULL(cash, 0) FROM tCash WHERE userNo = @userNo AND cashType = @cashType AND status = 0
	IF @REMAIN_CASH >= @cash
		BEGIN
			BEGIN TRAN

			UPDATE tCash SET cash = @REMAIN_CASH - @cash WHERE userNo = @userNo AND cashType = @cashType
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				BEGIN
					ROLLBACK TRAN
					RETURN (-9)
				END

			INSERT INTO tCashLog (userNo, logType, cashType, cash, reference)
			VALUES (@userNo, 2, @cashType, @cash, @reference)
			IF @@ERROR <> 0 OR @@IDENTITY = 0
				BEGIN
					ROLLBACK TRAN
					RETURN (-9)
				END
			ELSE
				BEGIN
					COMMIT TRAN
					RETURN (0)
				END
		END	
	ELSE
		RETURN (-1)

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Charge_ItemDraw]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------------------
' Name : usp_Charge_ItemDraw
' Description : Charge Item Draw
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@rowNo	: Row No
'				@userNo	: Row No
'			output Parameter
'				@RET
'					-2 : mismatch rowNo / userNo
'					-1 : used item
'					 0 : DB Error
'					 1 : Success
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Charge_ItemDraw]
	@rowNo INT,
	@userNo INT,
	@RET	INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @IS_DRAW AS BIT

	-- check user
	IF EXISTS(SELECT rowNo FROM tChargeItem WHERE rowNo = @rowNo AND userNo = @userNo)
		BEGIN
			-- check used
			SELECT @IS_DRAW = isDraw FROM tChargeItem WHERE rowNo = @rowNo
			IF @IS_DRAW = 0
				BEGIN
					BEGIN TRAN

					-- item use
					UPDATE tChargeItem SET isDraw = 1, drawDate = GETDATE() WHERE rowNo = @rowNo
					IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
						BEGIN
							-- db Error
							SET @RET = 0
							ROLLBACK TRAN
						END
					ELSE
						BEGIN
							-- success
							SET @RET = 1
							COMMIT TRAN
						END
				END
			ELSE
				-- used item
				SET @RET = -1
		END		
	ELSE
		-- mismatch rowNo / userNo
		SET @RET = -2

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Charge_ItemInsert]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Charge_ItemInsert
' Description : Charge Item Insert
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-16
' Parameter :
'			input Parameter
'				@userNo	: User No
'				@orderNo: Order No
'				@goodsNo: Goods No
'				@amount	: Amount
'			output Parameter
'				@RET 
'					 0 : DB Error
'					 1 : Success
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Charge_ItemInsert]
	@userNo  INT,
	@orderNo BIGINT,
	@goodsNo INT,
	@amount	 INT,
	@RET	 INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	INSERT INTO tChargeItem (userNo, orderNo, goodsNo, amount)
	VALUES (@userNo, @orderNo, @goodsNo, @amount)
	IF @@ERROR <> 0 OR @@IDENTITY = 0
		SET @RET = 0
	ELSE
		SET @RET = 1	

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Charge_ItemSelectList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Charge_ItemSelectList
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@userNo	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Charge_ItemSelectList]
	@userNo INT
AS
BEGIN
	SET NOCOUNT ON

	SELECT rowNo, goodsNo, amount, registerDate
	FROM tChargeItem WITH (NOLOCK)
	WHERE userNo = @userNo AND isDraw = 0

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_China_getMinorsPlaytime]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_China_getMinorsPlaytime
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.2
' Description :
'   유저번호에 따른 누적시간과 최종로그아웃 정보를 가져옴
' Parameter :
'	input Parameter
'		@nUserNo : 유저번호
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1492 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_China_getMinorsPlaytime]
	@nUserNo int
,	@nRet    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @isAdult    bit
	DECLARE @playtime   bigint
	DECLARE @logoutDate datetime
	
	SET @nRet = 0
	
	SELECT @isAdult = bIsAdult, @playtime = nPlaytime, @logoutDate = dLogoutDate 
	  FROM tMinorsPlaytime WITH(UPDLOCK, HOLDLOCK)
	 WHERE nUserNo = @nUserNo
	IF @logoutDate IS NULL OR DATEADD(hour, 5, @logoutDate) <= GETDATE() BEGIN
		BEGIN TRAN
		
		SET @playtime = 0
		
		UPDATE tMinorsPlaytime 
		   SET nPlaytime = @playtime
		 WHERE nUserNo = @nUserNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -1111
			RETURN
		END
		
		COMMIT TRAN
	END

	SELECT @isAdult AS bIsAdult, @playtime AS nPlaytime
END


GO
/****** Object:  StoredProcedure [dbo].[usp_China_setMinorsPlaytime]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_China_setMinorsPlaytime
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2011.11.2
' Description :
'   로그아웃시 총 플레이시간을 저장함.
' Parameter :
'	input Parameter
'		@nUserNo   : 유저번호
'		@nPlaytime : 플레이시간
'	output Parameter
'		@nRet	 : check SQL execute(valid/invalid)
'			= 0	 : works ok
'			< 0	 : error
'				-1492 : db error(insert)
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_China_setMinorsPlaytime]
	@nUserNo     int
,	@nPlaytime   bigint
,	@nRet        int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	
	SET @nRet = 0
	
	IF EXISTS(SELECT nUserNo FROM tMinorsPlaytime WHERE nUserNo = @nUserNo) BEGIN
		BEGIN TRAN
		
		UPDATE tMinorsPlaytime 
		   SET nPlaytime = @nPlaytime 
		     , dLogoutDate = GETDATE()
		 WHERE nUserNo = @nUserNo 
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			SET @nRet = -1111
			RETURN
		END
		ELSE BEGIN
			COMMIT TRAN
			RETURN
		END
	END
	ELSE BEGIN
		SET @nRet = -2222
		RETURN
	END
END
GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getBuyCount]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getBuyCount
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getBuyCount]
	@userNo INT = 0,
	@CNT INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SELECT @CNT = ISNULL(COUNT(orderNo), 0)
	  FROM tOrder WITH (NOLOCK)
	WHERE userNo = @userNo AND isPay = 1

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getBuyList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getBuyList
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getBuyList]
	@userNo    INT = 0,
	@pageNo    INT = 1,
	@pageLimit INT = 8
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @STMT AS NVARCHAR(1000)
	DECLARE @PARAMS AS NVARCHAR(100)
	DECLARE @TOP AS INT
	DECLARE @START AS INT

	SET @TOP = ((@pageNo - 1) * @pageLimit) + 1

	SET @STMT = ''
	SET @STMT = @STMT + 'SELECT @MIN = ISNULL(MIN(orderNo), 0) '
	SET @STMT = @STMT + '  FROM (	SELECT TOP ' + CAST(@TOP AS NVARCHAR) + ' orderNo '
	SET @STMT = @STMT + '			  FROM tOrder WITH (NOLOCK) '
	SET @STMT = @STMT + '			WHERE userNo = ' + CAST(@userNo AS NVARCHAR(10))
	SET @STMT = @STMT + '			ORDER BY orderNo DESC '
	SET @STMT = @STMT + ') AS T1'
	SET @PARAMS = N'@MIN AS INT OUTPUT'
	EXEC sp_executesql @STMT, @PARAMS, @MIN = @START OUTPUT

	SET @STMT = ''
	SET @STMT = @STMT + 'SELECT TOP ' + CAST(@pageLimit AS NVARCHAR(2)) + ' CONVERT(NVARCHAR(10), orderDate, 120) AS orderDate, orderType, T1.goodsNo, name, imageName, unit, unitDescription, orderPrice '
	SET @STMT = @STMT + '  FROM tOrder AS T1, tItem AS T2 '
	SET @STMT = @STMT + 'WHERE userNo = ' + CAST(@userNo AS NVARCHAR(10)) +  ' AND isPay = 1 AND T1.goodsNo = T2.goodsNo AND orderNo <= ' + CAST(@START AS NVARCHAR(10))
	SET @STMT = @STMT + ' ORDER BY orderNo DESC'
	EXEC sp_executesql @STMT

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getCategoryName]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getCategoryName
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getCategoryName]
	@category NVARCHAR(10) = '',
	@CATEGORY_NAME NVARCHAR(10) OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	SELECT @CATEGORY_NAME = name FROM tItemCategory WITH(NOLOCK) WHERE category = @category

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getEventList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getEventList
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getEventList]
	@eventType TINYINT = 1,
	@pageNo    INT = 1,
	@pageLimit INT = 3
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @STMT AS NVARCHAR(1000)
	DECLARE @PARAMS AS NVARCHAR(100)
	DECLARE @WHERE AS NVARCHAR(500)
	DECLARE @TODAY AS NVARCHAR(10)
	DECLARE @TOP AS INT
	DECLARE @START AS INT

	SET @TODAY = CONVERT(NVARCHAR(10), GETDATE(), 120)
	SET @TOP = ((@pageNo - 1) * @pageLimit) + 1
	SET @WHERE = ''
	IF @eventType = 1
		SET @WHERE = 'WHERE isNew = 1 '
	ELSE IF @eventType = 2
		SET @WHERE = 'WHERE isPopular = 1 '
	ELSE IF @eventType = 3
		SET @WHERE = 'WHERE isRecommand = 1 '

	SET @STMT = ''
	SET @STMT = @STMT + 'SELECT @MIN = ISNULL(MIN(goodsNo), 0) '
	SET @STMT = @STMT + '  FROM (	SELECT TOP ' + CAST(@TOP AS NVARCHAR) + ' goodsNo '
	SET @STMT = @STMT + '			  FROM VT_ITEM_SELL '
	SET @STMT = @STMT + @WHERE
	SET @STMT = @STMT + ') AS T1'
	SET @PARAMS = N'@MIN AS INT OUTPUT'
	EXEC sp_executesql @STMT, @PARAMS, @MIN = @START OUTPUT

	SET @STMT = ''
	SET @STMT = @STMT + 'SELECT TOP ' + CAST(@pageLimit AS NVARCHAR) + ' goodsNo, groupNo, name, imageName, unit, unitDescription, price, discount, sellPrice = CASE '
	SET @STMT = @STMT + '		WHEN discountStartDate <= ''' + @TODAY + ''' AND discountEndDate >= ''' + @TODAY + ''' THEN price - ((price * discount) / 100) ELSE price END, description '
	SET @STMT = @STMT + '  FROM VT_ITEM_SELL '
	SET @STMT = @STMT + @WHERE + ' AND goodsNo <= ' + CAST(@START AS NVARCHAR(10))
	EXEC sp_executesql @STMT

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getGroup]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getGroup
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getGroup]
	@groupNo INT
AS
BEGIN
	SET NOCOUNT ON

	SELECT category, name, imageName, itemType, description 
	  FROM VT_ITEM_GROUP WITH (NOLOCK) 
	 WHERE groupNo = @groupNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getGroupList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getGroupList
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getGroupList]
	@category NVARCHAR(10) = '',
	@pageNo INT = 1,
	@pageLimit INT = 6
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @STMT AS NVARCHAR(1000)
	DECLARE @PARAMS AS NVARCHAR(100)
	DECLARE @WHERE AS NVARCHAR(500)
	DECLARE @TODAY AS NVARCHAR(10)
	DECLARE @TOP AS INT
	DECLARE @START AS INT

	SET @TODAY = CONVERT(NVARCHAR(10), GETDATE(), 120)
	SET @TOP = ((@pageNo - 1) * @pageLimit) + 1
	SET @WHERE = ''

	IF @pageNo = 1
		BEGIN
			IF @category <> ''
				SET @WHERE = ' WHERE category LIKE ''' + @category + '%'''

			SET @STMT = ''
			SET @STMT = @STMT + 'SELECT TOP ' + CAST(@pageLimit AS NVARCHAR) + ' goodsNo, groupNo, name, imageName, unit, unitDescription, price, discount, sellPrice = CASE '
			SET @STMT = @STMT + '		WHEN discountStartDate <= ''' + @TODAY + ''' AND discountEndDate >= ''' + @TODAY + ''' THEN price - ((price * discount) / 100) ELSE price END '
			SET @STMT = @STMT + '  FROM VT_ITEM_GROUP '
			SET @STMT = @STMT + @WHERE
			EXEC sp_executesql @STMT
		END
	ELSE
		BEGIN
			IF @category <> ''
				SET @WHERE = ' AND category LIKE ''' + @category + '%'''

			SET @STMT = ''
			SET @STMT = @STMT + 'SELECT @MIN = ISNULL(MIN(goodsNo), 0) '
			SET @STMT = @STMT + '  FROM (	SELECT TOP ' + CAST(@TOP AS NVARCHAR) + ' goodsNo '
			SET @STMT = @STMT + '			  FROM VT_ITEM_GROUP '
			SET @STMT = @STMT + '			WHERE isSell = 1 '
			SET @STMT = @STMT + @WHERE
			SET @STMT = @STMT + ') AS T1'
			SET @PARAMS = N'@MIN AS INT OUTPUT'
			EXEC sp_executesql @STMT, @PARAMS, @MIN = @START OUTPUT
		
			SET @STMT = ''
			SET @STMT = @STMT + 'SELECT TOP ' + CAST(@pageLimit AS NVARCHAR) + ' goodsNo, groupNo, name, imageName, unit, unitDescription, price, discount, sellPrice = CASE '
			SET @STMT = @STMT + '		WHEN discountStartDate <= ''' + @TODAY + ''' AND discountEndDate >= ''' + @TODAY + ''' THEN price - ((price * discount) / 100) ELSE price END '
			SET @STMT = @STMT + '  FROM VT_ITEM_GROUP '
			SET @STMT = @STMT + 'WHERE goodsNo <= ' + CAST(@START AS NVARCHAR(10)) + @WHERE
			EXEC sp_executesql @STMT
		END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getGroupPrice]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getGroupPrice
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getGroupPrice]
	@groupNo INT
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TODAY AS NVARCHAR(10)
	SET @TODAY = CONVERT(NVARCHAR(10), GETDATE(), 120)

	SELECT goodsNo, unit, unitDescription, price, discount, sellPrice = 
		CASE
			WHEN discountStartDate <= @TODAY AND discountEndDate >= @TODAY THEN price - ((price * discount) / 100) 
			ELSE price 
		END
	  FROM VT_ITEM_SELL
	WHERE groupNo = @groupNo
	ORDER BY goodsNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getItem]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getItem
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getItem]
	@goodsNo INT
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TODAY AS NVARCHAR(10)
	SET @TODAY = CONVERT(NVARCHAR(10), GETDATE(), 120)

	SELECT goodsNo, billingNo, category, groupNo, name, imageName, itemType, unit, unitDescription, price, discount, sellPrice = 
		CASE
			WHEN discountStartDate <= @TODAY AND discountEndDate >= @TODAY THEN price - ((price * discount) / 100) 
			ELSE price 
		END, isSell, description 
	  FROM tItem WITH (NOLOCK)
	WHERE goodsNo = @goodsNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getOrder]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getOrder
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getOrder]
	@orderNo INT
AS
BEGIN
	SET NOCOUNT ON

	SELECT orderDate, orderType, userNo, goodsNo, orderPrice, cashType, isPay, userIP
	  FROM tOrder WITH (NOLOCK)
	WHERE orderNo = @orderNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getOrderList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getOrderList
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getOrderList]
	@userNo INT
AS
BEGIN
	SET NOCOUNT ON

	SELECT orderDate, orderType, T1.goodsNo, name, imageName, orderPrice
	  FROM tOrder AS T1, tItem AS T2
	WHERE userNo = @userNo AND isPay = 1 AND T1.goodsNo = T2.goodsNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getSubCategory]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getSubCategory
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getSubCategory]
	@parent NVARCHAR(10)
AS
BEGIN
	SET NOCOUNT ON

	SELECT * FROM tItemCategory WITH(NOLOCK) WHERE parent = @parent

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getTargetUser]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getTargetUser
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getTargetUser]
	@worldNo  INT,
	@charName NVARCHAR(20),
	@USER_NO  INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	IF @worldNo = 0
		BEGIN
			SELECT @USER_NO = ISNULL(nUserNo, 0)
			  FROM W00DATA.W00_Data.dbo.tCharacter
			WHERE sID = @charName AND bDeleted = 0
		END
/*	ELSE
		BEGIN
			SELECT @USER_NO = ISNULL(nUserNo, 0)
			  FROM W01CHAR.World01_Character.dbo.tCharacter
			WHERE sID = @charName AND bDeleted = 0
		END
*/
	SET @USER_NO = ISNULL(@USER_NO, 0)

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getTotalCount]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getTotalCount
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getTotalCount]
	@eventType INT = 0,
	@category  NVARCHAR(10) = '',
	@TOTAL     INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @STMT AS NVARCHAR(1000)
	DECLARE @PARAMS AS NVARCHAR(100)

	IF @eventType > 0
		BEGIN
			SET @STMT = 'SELECT @CNT = COUNT(goodsNo) FROM VT_ITEM_SELL '
			IF @eventType = 1
				SET @STMT = @STMT +  'WHERE isNew = 1 '
			ELSE IF @eventType = 2
				SET @STMT = @STMT +  'WHERE isPopular = 1 '
			ELSE IF @eventType = 3
				SET @STMT = @STMT +  'WHERE isRecommand = 1 '
		END
	ELSE
		BEGIN 
			SET @STMT = 'SELECT @CNT = COUNT(goodsNo) FROM VT_ITEM_GROUP '
			IF @category <> ''
				SET @STMT = @STMT + ' WHERE category LIKE ''' + @category + '%'''			
		END
	SET @PARAMS = N'@CNT AS INT OUTPUT'
	EXEC sp_executesql @STMT, @PARAMS, @CNT = @TOTAL OUTPUT

	SET @TOTAL = ISNULL(@TOTAL, 0)

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_getUser]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_getUser
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_getUser]
	@userNo    INT = 0,
	@serialKey INT = 0,
	@USER_ID   NVARCHAR(20) = '' OUTPUT,
	@AUTH_ID   INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	IF @serialKey = 0
		BEGIN
			SET @USER_ID = ''
			SET @AUTH_ID = 0
		END
	ELSE
		BEGIN
			IF EXISTS(SELECT nUserNo FROM tUser WITH (NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0)
				BEGIN
					SELECT @USER_ID = sUserID, @AUTH_ID = nAuthID
					  FROM tUser AS T1 INNER JOIN tSerialKey AS T2 ON T1.nUserNo = T2.userNo
					 WHERE T2.userNo = @userNo AND T2.serialKey = @serialKey

					SET @USER_ID = ISNULL(@USER_ID, '')
					IF @USER_ID <> '' OR @USER_ID <> NULL
						DELETE FROM tSerialKey WHERE userNo = @userNo
				END
			ELSE
				BEGIN
					SET @USER_ID = ''
					SET @AUTH_ID = 0
				END
		END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setBuyOrder]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_setBuyOrder
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setBuyOrder]
	@orderNo    INT,
	@userNo     INT,
	@targetUser INT = NULL,
	@RET        INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	
	-- 林巩皝E?函紒E
	DECLARE @ORDER_TYPE AS INT
	DECLARE @ORDER_USER AS NVARCHAR(20)
	DECLARE @GOODS_NO AS INT
	DECLARE @AMOUNT AS INT
	DECLARE @ORDER_PRICE AS INT
	DECLARE @CASH_TYPE AS TINYINT
	DECLARE @IS_PAY AS INT
	DECLARE @OWNER AS INT

	IF EXISTS(SELECT nUserNo FROM tUser WHERE nUserNo = @userNo AND bIsDelete = 0)
		BEGIN
			BEGIN TRAN

			IF EXISTS(SELECT orderNo FROM tOrder WHERE orderNo = @orderNo)
				BEGIN
					SELECT @ORDER_TYPE = orderType, 
						   @ORDER_USER = userNo, 
						   @GOODS_NO = goodsNo, 
						   @AMOUNT = amount, 
						   @ORDER_PRICE = orderPrice, 
						   @CASH_TYPE = cashType,
						   @IS_PAY = isPay
					  FROM tOrder
					WHERE orderNo = @orderNo

					IF @ORDER_TYPE = 1 AND (@targetUser = 0 or @targetUser IS NULL)
						BEGIN
							SET @RET = -4
							GOTO GT_OUT
						END

					IF @ORDER_TYPE = 0
						SET @OWNER = @userNo
					ELSE
						SET @OWNER = @targetUser

					IF @ORDER_USER <> @userNo
						BEGIN
							SET @RET = -5
							GOTO GT_OUT
						END

					IF @IS_PAY = 1
						BEGIN
							SET @RET = -6
							GOTO GT_OUT
						END
				END
			ELSE
				BEGIN
					SET @RET = -7
					GOTO GT_OUT
				END

			UPDATE tOrder SET toUserNo = @OWNER, isPay = 1
			WHERE orderNo = @orderNo
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				BEGIN
					SET @RET = -9
					GOTO GT_OUT
				END

			EXEC @RET = usp_Cash_useCash @userNo, @CASH_TYPE, @ORDER_PRICE, @orderNo
			IF @RET <> 0
				BEGIN
					SET @RET = @RET
					GOTO GT_OUT
				END

			INSERT INTO tChargeItem (userNo, orderNo, goodsNo, amount)
			VALUES (@OWNER, @orderNo, @GOODS_NO, @AMOUNT)
			IF @@ERROR <> 0 OR @@IDENTITY = 0
				BEGIN
					SET @RET = -9
					GOTO GT_OUT
				END

			UPDATE tItem SET sellCount = sellCount + 1 WHERE goodsNo = @GOODS_NO
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				BEGIN
					SET @RET = -9
					GOTO GT_OUT
				END
			ELSE
				BEGIN
					SET @RET = 0
					GOTO GT_OUT
				END
		END
	ELSE
		SET @RET = -2

GT_OUT:
	IF @RET <> 0
		ROLLBACK TRAN
	ELSE
		COMMIT TRAN

	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setCategory]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_setCategory
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setCategory]
	@category NVARCHAR(10),
	@name NVARCHAR(20),
	@parent NVARCHAR(10),
	@status BIT,
	@RET INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	IF EXISTS(SELECT category FROM tItemCategory WITH(NOLOCK) WHERE category = @parent)
		BEGIN
			IF EXISTS(SELECT category FROM tItemCategory WITH(NOLOCK) WHERE category = @category)
				BEGIN
					UPDATE tItemCategory 
						SET name = @name,
							parent = @parent,
							status = @status
					WHERE category = @category
					IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
						SET @RET = -2
					ELSE
						SET @RET = 1
				END
			ELSE
				BEGIN
					INSERT INTO tItemCategory (category, name, parent)
					VALUES (@category, @name, @parent)
					IF @@ERROR <> 0
						SET @RET = -1
					ELSE
						SET @RET = 1
				END
		END
	ELSE
		SET @RET = -3

	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setItem]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_setItem
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setItem]
	@goodsNo INT,
	@category NVARCHAR(10),
	@groupNo INT,
	@name NVARCHAR(100),
	@imageName NVARCHAR(100),
	@itemType NVARCHAR(20),
	@unit INT,
	@price INT,
	@discount INT,
	@isRecommand BIT,
	@isPopular BIT,
	@isNew BIT,
	@isSell BIT,
	@sellStartDate NVARCHAR(10) = '',
	@sellEndDate NVARCHAR(10) = '',
	@discountStartDate NVARCHAR(10) = '',
	@discountEndDate NVARCHAR(10) = '',
	@RET INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	IF EXISTS(SELECT goodsNo FROM tItem WHERE goodsNo = @goodsNo)
		BEGIN
			BEGIN TRAN

			UPDATE tItem SET
				category = @category,
				groupNo = @groupNo,
				name = @name,
				imageName = @imageName,
				itemType = @itemType,
				unit = @unit,
				price = @price,
				discount = @discount,
				isRecommand = @isRecommand,
				isPopular = @isPopular,
				isNew = @isNew,
				isSell = @isSell,
				sellStartDate = @sellStartDate,
				sellEndDate = @sellEndDate,
				discountStartDate = @discountStartDate,
				discountEndDate = @discountEndDate
			WHERE goodsNo = @goodsNo
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				BEGIN
					SET @RET = -1
					ROLLBACK TRAN
				END
			ELSE
				BEGIN
					SET @RET = 1
					COMMIT TRAN
				END
		END
	ELSE
		SET @RET = -2

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setOrder]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_setOrder
' Description : Get Charge Item List
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@USER_NO	: User No
'			output Parameter
'				recordSet (rowNo, goodsNo, amount, registerDate)
' Modify :
'	2007-08-24 PC箒EIP甫 眉农窍咯 铰牢等 PC规狼 IP老 版縼EPC箒E概免肺 扁废
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setOrder]
	@orderType TINYINT,
	@userNo INT,
	@goodsNo INT,
	@goodsPrice INT,
	@amount INT,
	@discount INT,
	@orderPrice INT,
	@cashType int,
	@userIP NVARCHAR(30),
	@ORDER_NO INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @pcbangNo AS INT

	BEGIN TRAN

	IF EXISTS(SELECT pcbangNo FROM tPcbangIP WITH(NOLOCK) WHERE ip = @userIP)
		BEGIN
			SELECT @pcbangNo = T1.pcbangNo 
			  FROM tPcbang AS T1 WITH(NOLOCK) INNER JOIN tPcbangIP AS T2 WITH(NOLOCK) ON T1.pcbangNo = T2.pcbangNo
			 WHERE T1.grade > 0 AND T2.ip = @userIP
		END

	INSERT INTO tOrder (orderType, userNo, goodsNo, goodsPrice, amount, discount, orderPrice, cashType, userIP, pcbangNo)
	VALUES (@orderType, @userNo, @goodsNo, @goodsPrice, @amount, @discount, @orderPrice, @cashType, @userIP, @pcbangNo)
	IF @@ERROR <> 0 OR @@IDENTITY = 0
		BEGIN
			SET @ORDER_NO = 0
			ROLLBACK TRAN
		END
	ELSE
		BEGIN
			SET @ORDER_NO = @@IDENTITY
			COMMIT TRAN
		END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_Item_setSerialKey]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*---------------------------------------------------------------------------------------
' Name : usp_Item_setSerialKey
' Description : 酒捞袍竵E牢刘阑 困茄 矫府倔虐甫 积己秦辑 涝仿窍绰 橇肺矫纴E
' Creator : Ko Dong Gyun(kodong@empal.com)
' Creat Date : 2006-06-17
' Parameter :
'			input Parameter
'				@userNo    : User No
'				@serialKey : User No
'			output Parameter
'				@RET : 0 or 1 ( F / T )
---------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_Item_setSerialKey]
	@userNo    int = 0,
	@serialKey bigint = 0,
	@RET       int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	IF @userNo = 0 OR @serialKey = 0
		RETURN (-1)

	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0)
		BEGIN
			BEGIN TRAN 

			IF EXISTS(SELECT userNo FROM tSerialKey WHERE userNo = @userNo)
				DELETE FROM tSerialKey WHERE userNo = @userNo

			INSERT INTO tSerialKey (userNo, serialKey)
			VALUES (@userNo, @serialKey)
			IF @@ERROR <> 0
				BEGIN 
					ROLLBACK TRAN
					SET @RET = 0
				END
			ELSE
				BEGIN
					COMMIT TRAN
					SET @RET = 1
				END
		END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getBuyList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_getBuyList
' 작성자 : kodong
' 작성일 : 2007년 07월 25일
' Description :
'  운영툴에서 결제내역 얻기
' Parameter :
'	input Parameter
'		@userNo   : 유저번호(필수)
'	output Parameter
'		see SQL query
'
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getBuyList]
	@userNo   int
AS
BEGIN
	SET NOCOUNT ON
	SELECT orderNo, orderDate, T1.goodsNo, name, unit, unitDescription, toUserNo, 
		   orderType = CASE orderType WHEN 0 THEN 'Buy' ELSE 'Gift' END, orderPrice,
		   cashType  = CASE cashType  WHEN 1 THEN 'Game' ELSE 'Event' END
	  FROM tOrder AS T1, tItem AS T2
	 WHERE userNo = @userNo AND isPay = 1 AND T1.goodsNo = T2.goodsNo
	 ORDER BY orderNo
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getEventPointList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_getEventPointList
' 작성자 : CJC
' 작성일 : 2007년 07월 25일
' Description :
'  운영툴에서 이벤트 포인트 충천내역 얻기
' Parameter :
'	input Parameter
'		@userNo   : 유저번호(필수)
'	output Parameter
'		see SQL query
'
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getEventPointList]
	@userNo   int
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	SELECT   rowNo, issueDate, cash, reference
	FROM      tCashLog
	WHERE   (userNo = @userNo) AND (cashType = 2) AND (logType = 1)
	ORDER BY rowNo
	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getPayAgreement]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_Pay_getAgreement
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2007년 06월 26일
' Description :
'   만 20세미만 유저의 부모님동의서 목록 가져오기
' Parameter :
'	input Parameter
'		@userNo  : 유저번호(필수)
'		@getType : 가져오는 방식(선택:Default 0) - 0:목록/1:최근1개
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getPayAgreement]
	@userNo  int
,	@getType int = 0
AS
BEGIN
	SET NOCOUNT ON
	IF @getType = 0
		SELECT rowNo, userNo, name, juminNo, phoneNo, status, issueDate, vaildDate
          FROM tPayAgreement WITH(NOLOCK)
         WHERE userNo = @userNo
		 ORDER BY rowNo DESC
	ELSE
		SELECT TOP 1 rowNo, userNo, name, juminNo, phoneNo, status, issueDate, vaildDate
          FROM tPayAgreement WITH(NOLOCK)
         WHERE userNo = @userNo
		 ORDER BY rowNo DESC
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getPayList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_getPayList
' 작성자 : CJC
' 작성일 : 2007년 07월 25일
' Description :
'  운영툴에서 결제내역 얻기
' Parameter :
'	input Parameter
'		@userNo   : 유저번호(필수)
'	output Parameter
'		see SQL query
'
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getPayList]
	@userNo   int
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	SELECT V.orderNo, V.resultDate, T.name, V.cash, V.pgTID FROM VT_PAY AS V 
	INNER JOIN tPayType AS T ON T.payNo = V.payNo
	WHERE userno=@userNo ORDER BY orderNo
	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getUserCount]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' 함수명 : usp_OPT_getUserCount
' 내  용 : 유저 총 수를 구하는 프로시저
'          @KEYFIELD, @KEYWORD 값이 있을때는 검색 총 수를 구한다.
' 작성자 : 고동균
' 작성일 : 2004년 06월 15일
' 버  젼 : 1.0
' 인  자 :
'			input Parameter
'				@KEYFIELD	: 검색필드
'				@KEYWORD	: 검색값
' 수  정 : 
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[usp_OPT_getUserCount]
	@KEYFIELD AS NVARCHAR(20) = '',
	@KEYWORD  AS NVARCHAR(20) = ''
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	DECLARE @STMT AS NVARCHAR(1000)
	
	IF @KEYFIELD <> '' AND @KEYWORD <> ''
		SET @STMT = 'SELECT COUNT(nUserNo) FROM tUser(NOLOCK) WHERE ' + @KEYFIELD + ' LIKE ' + '''%' + @KEYWORD + '%'''
	ELSE
		SET @STMT = 'SELECT COUNT(nUserNo) FROM tUser(NOLOCK)'	
	
	EXEC sp_executesql @STMT
	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getUserID]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_getUserID
' 작성자 : Ko Dong Gyun(kodong@aragon.co.kr)
' 작성일 : 2007년 05월 31일
' Description :
'   OPTool 관련 프로시저
' Parameter :
'	input Parameter
'		@userNo   : 아이디
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getUserID]
	@userNo int
AS
BEGIN
	SET NOCOUNT ON
	SELECT sUserID FROM tUser WHERE nUserNo = @userNo
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_getUserNo]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_getUserNo
' 작성자 : Ko Dong Gyun(kodong@aragon.co.kr)
' 작성일 : 2007년 05월 31일
' Description :
'   OPTool 관련 프로시저
' Parameter :
'	input Parameter
'		@userID   : 아이디
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_getUserNo]
	@userID nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON
	SELECT nUserNo FROM tUser WHERE sUserID = @userID
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_searchUser]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_searchUser
' 작성자 : Ko Dong Gyun(kodong@aragon.co.kr)
' 작성일 : 2007년 05월 31일
' Description :
'   OPTool에서 사용. 특정 조건에 맞는 유저리스트 서치.
' Parameter :
'	input Parameter
'		@userID   : 아이디
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_searchUser]
	@key   int,
	@value nvarchar(100)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @STMT       AS NVARCHAR(1000)
	DECLARE @SQL_WHERE	AS NVARCHAR(200)
	SET @SQL_WHERE = ' WHERE ' +
	CASE @key
		WHEN 1 THEN ' sUserName = ''' + @value + ''''
		WHEN 3 THEN ' nAuthID = ' + CAST(@value AS NVARCHAR(100))
		WHEN 5 THEN ' bIsBlock = ' + CAST(@value AS NVARCHAR(100))
	END
	SET @STMT = 'SELECT * FROM tUser(NOLOCK)' + @SQL_WHERE
	EXEC sp_executesql @STMT
	
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_setPayAgreement]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 이  름 : usp_OPT_setPayAgreement
' 작성자 : Ko Dong Gyun(kodong@gmail.com)
' 작성일 : 2007년 06월 26일
' Description :
'   만 20세미만 유저의 부모님동의서 도착여부 체크(FAX 확인 후 OPTool에서 체크)
' Parameter :
'	input Parameter
'		@rowNo  : 일련번호(필수)
'		@userNo : 유저번호(필수)
'		@status : 도착여부(필수) - 0:미도착/1:도착
'	output Parameter
'		@RET    : 처리결과
'			-9 : DB Error
'			-2 : mismatch userNo
'			-1 : no seek rowNo
'			 0 : ok
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_setPayAgreement]
	@rowNo  int
,	@userNo int
,	@status tinyint
,	@RET    int = 0 output
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	DECLARE @USER_NO INT
	SET @RET = 0
	IF EXISTS(SELECT rowNo FROM tPayAgreement WHERE rowNo = @rowNo)
		BEGIN
			SELECT @USER_NO = userNo FROM tPayAgreement WHERE rowNo = @rowNo
			IF @USER_NO <> @userNo
				SET @RET = -2
			IF @RET = 0
				BEGIN
					BEGIN TRAN
					UPDATE tPayAgreement SET status = @status WHERE rowNo = @rowNo
					IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
						BEGIN
							SET @RET = -9
							ROLLBACK TRAN
						END
					ELSE
						BEGIN
							SET @RET = 0
							COMMIT TRAN
						END
				END
		END
	ELSE
		SET @RET = -1
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_setUserAuthID]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_setUserAuthID
' 작성자 : CJC
' 작성일 : 2007년 06월 07일
' Description :
'  운영툴에서 유저권한 세팅
' Parameter :
'	input Parameter
'		@userNo   : 유저번호(필수)
'		@AuthID   : 유전권한(필수)
'	return Value
'			 0 : works ok
'			-1 : unknown userNo
'			-9 : DB Error
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_setUserAuthID]
	@userNo   int
,	@AuthID   tinyint
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	DECLARE @RET AS INT
	
	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0)
		BEGIN
			UPDATE tUser 
			   SET nAuthID = @AuthID
			 WHERE nUserNo = @userNo
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				RETURN (-9)
			ELSE
				RETURN (0)
		END
	ELSE
		RETURN (-1)
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_OPT_setUserBlock]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*-------------------------------------------------------------------------------------
' 이  름 : usp_OPT_setUserBlock
' 작성자 : CJC
' 작성일 : 2007년 06월 07일
' Description :
'  운영툴에서 유저블럭 세팅
' Parameter :
'	input Parameter
'		@userNo   : 유저번호(필수)
'		@block   : 유전권한(필수)
'	return Value
'			 0 : works ok
'			-1 : unknown userNo
'			-9 : DB Error
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_OPT_setUserBlock]
	@userNo   int
,	@block   bit
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	DECLARE @RET AS INT
	
	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0)
		BEGIN
			UPDATE tUser 
			   SET bIsBlock = @block
			 WHERE nUserNo = @userNo
			IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
				RETURN (-9)
			ELSE
				RETURN (0)
		END
	ELSE
		RETURN (-1)
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_chkBlock]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_chkBlock
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   Block 沥焊
' Parameter :
'	input Parameter
'		@userNo   : 蜡历锅龋(鞘荐)
'	output Paramter
'		@userNo
'			n  : 蜡历锅龋
'	return Value
'			 0 : works ok
'			-1 : unknown userNo
'			-9 : DB Error
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_chkBlock]
	@userNo INT
,	@RET    INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000
	
	DECLARE @BLOCK_NO INT
	DECLARE @TYPE     BIT
	DECLARE @END_DATE DATETIME	
	
	SELECT TOP 1 @BLOCK_NO = nBlockNo, @TYPE = nType, @END_DATE = dEndDate 
	  FROM tUserBlock(NOLOCK) 
	 WHERE nUserNo = @userNo AND bStatus = 1
	 ORDER BY nBlockNo
	IF @TYPE = 1 AND @END_DATE < GETDATE() BEGIN
		BEGIN TRAN
		
		UPDATE tUserBlock SET bStatus = 0 WHERE nBlockNo = @BLOCK_NO
		IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN
			ROLLBACK TRAN
			SET @RET = 0
			RETURN
		END
	
		-- 眠啊 拌沥喉钒惑怕 蜡公眉农
		IF EXISTS(SELECT nBlockNo FROM tUserBlock(NOLOCK) WHERE nUserNo = @userNo AND bStatus = 1) BEGIN
			SET @RET = 0
		END
		ELSE BEGIN
			UPDATE tUser SET bIsBlock = 0 WHERE nUserNo = @userNo
			IF @@ERROR <> 0 OR @@ROWCOUNT = 0
				BEGIN
					ROLLBACK TRAN
					SET @RET = 0
					RETURN
				END
	
			SET @RET = 1
		END	
		
		COMMIT TRAN
	END
	SET NOCOUNT OFF
	SET LOCK_TIMEOUT -1
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_chkUserID]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*--------------------------------------------------------------------------------------
' 捞  抚 : usp_User_chkUserID
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   OPTool, Web俊辑 雀盔啊涝矫 酒捞叼 吝汗眉农窍绰 橇肺矫历
' Parameter :
'	input Parameter
'		@userID   : 酒捞叼
'	return Value
'			100 : 酒捞叼啊 涝仿登瘤 臼疽嚼聪促.
'			1 : Success
'-------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_chkUserID]
	@userID nvarchar(20) = ''
AS
BEGIN
	SET NOCOUNT ON

	IF (@userID = '' OR LEN(@userID) < 5 OR LEN(@userID) > 16) BEGIN
		RETURN -1
	END

	IF EXISTS (SELECT sUserID FROM tUser WITH(NOLOCK) WHERE sUserID = @userID AND bIsDelete = 0)
		RETURN -2
	ELSE
		RETURN 0

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_delete]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_delete
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description : 雀盔呕硼
' Parameter :
'	input Parameter
'		@userNo  : 蜡历锅龋(鞘荐)
'		@type    : 呕硼荤蜡(可记) - 0篮 包府磊俊 狼茄 呕硼
'		@comment : 窍绊酵篮富(可记)
'	return Value
'			 0 : works ok
'			-1 : unknown userNo
'			-9 : DB Error
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_delete]
	@userNo  int
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0) BEGIN
		BEGIN TRAN

		UPDATE tUser
		   SET sUserID = '~' + sUserID + '_' + CONVERT(NVARCHAR(8), GETDATE(), 112),
		       bIsDelete = 1
		 WHERE nUserNo = @userNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			RETURN (-9)
		END

		UPDATE tUserOption 
		   SET sJuminNo = '~' + sJuminNo + '_' + CONVERT(NVARCHAR(8), GETDATE(), 112)
		 WHERE nUserNo = @userNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			RETURN (-9)
		END
		ELSE BEGIN
			COMMIT TRAN
			RETURN (0)
		END	
	END
	ELSE BEGIN
		RETURN (-1)
	END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_getAuthList]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_User_getAuthList]
AS
BEGIN
	SET NOCOUNT ON

	SELECT nAuthID, sAuthName, bIsLoginAble  FROM tUserAuth

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_getLogin]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_getLogin
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   昆荤捞飘 肺弊牢阑 困茄 沥焊甫 啊廉坷绰 扁瓷
'	2007斥 07岿 30老 荐沥荤亲
'	  - tUserOption 抛捞喉俊辑 sEmail, sJuminNo, sPhoneNo甫 眠啊窃.
' Parameter :
'	input Parameter
'		@userID   : 酒捞叼(鞘荐)
'		@userPW   : 厚剐锅龋(鞘荐)
'	return Value
'		RecordSet
'			nUserNo, sUserID, sUserPW, sUserName, bIsBlock, nAuthID, sEmail, sJuminNo, sPhoneNo
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_getLogin]
	@userID   NVARCHAR(20)
,	@userPW   NVARCHAR(20)
AS
BEGIN
	SET NOCOUNT ON

	SELECT T1.nUserNo, sUserID, sUserPW, sUserName, bIsBlock, nAuthID, sEmail, sJuminNo, sPhoneNo
	  FROM tUser AS T1 LEFT JOIN tUserOption AS T2 ON T1.nUserNo = T2.nUserNo
	 WHERE sUserID = @userID AND sUserPW = @userPW AND bIsDelete = 0

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_getUser]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_User_getUser]
	@userNo int
AS
BEGIN
	SET NOCOUNT ON

	SELECT nUserNo, sUserID, sUserPW, sUserName, bIsBlock, bIsDelete, nAuthID, sUserIP, dDate
	  FROM tUser WITH(NOLOCK)
	 WHERE nUserNo = @userNo

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_insert]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_insert
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   Web俊辑 雀盔啊涝矫 龋免窍绰 历厘橇肺矫历
' Parameter :
'	input Parameter
'		@userID   : 酒捞叼(鞘荐)
'		@userPW   : 厚剐锅龋(鞘荐)
'		@userName : 捞抚(鞘荐)
'		@userIP   : 酒捞乔(鞘荐)
'		@eMail    : EMail(可记)
'		@juminNo  : 林刮殿废锅龋(可记)
'		@phoneNo  : 傈拳锅龋(可记)
'		@zipNo    : 快祈锅龋(可记)
'		@address  : 惑技林家(可记)
'		@isMail   : EMail 荐脚咯何(可记)
'		@channel  : 啊涝版肺(可记)
'		@isOption : 何啊沥焊狼 扁废咯何(鞘荐)
'	output Paramter
'		@userNo
'			n  : 蜡历锅龋
'	return Value
'			 0 : works ok
'			-1 : Duplicate juminNo
'			-2 : Duplicate userID
'			-3 : Fail Option
'			-9 : DB Error
' 荐沥老
'	2007-08-07 : PANDORA TV 盲澄眠啊肺 牢秦 @channel 眠啊窃(啊涝版肺)
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_insert]
	@userID   nvarchar(20)
,	@userPW   nvarchar(20)
,	@userName nvarchar(10)
,	@userIP   nvarchar(30)
,	@eMail    nvarchar(70) = NULL
,	@juminNo  nvarchar(20) = NULL
,	@phoneNo  nvarchar(20) = NULL
,	@zipNo    int = NULL
,	@address  nvarchar(90) = NULL
,	@isMail   bit = NULL
,	@channel  nvarchar(20) = 'ONSON'
,	@isOption bit
,	@userNo   int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @RET AS INT

	/* 肺拿篮 狼固啊 绝澜.(林刮殿废锅龋 眉农)
	SET EXEC @RET = usp_User_chkRegister @juminNo
	IF (@RET <> 0)
		RETURN (-1)
	*/

	EXEC @RET = usp_User_chkUserID @userID
	IF (@RET <> 0)
		RETURN (-2)

	BEGIN TRAN

	INSERT INTO tUser (sUserID, sUserPW, sUserName, sUserIP)
	VALUES (@userID, @userPW, @userName, @userIP)
	IF (@@ERROR <> 0 OR SCOPE_IDENTITY() = 0) BEGIN
		ROLLBACK TRAN
		RETURN (-9)
	END
	ELSE BEGIN			
		SET @userNo = SCOPE_IDENTITY()

		IF (@isOption = 1) BEGIN
			EXEC @RET = usp_User_insertOption @userNo, @eMail, @juminNo, @phoneNo, @zipNo, @address, @isMail, @channel
			IF (@RET <> 0) BEGIN
				ROLLBACK TRAN
				RETURN (-3)
			END
		END

		COMMIT TRAN
		RETURN (0)
	END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT ON
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_insertOption]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_insertOption
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   Web俊辑 雀盔啊涝矫 龋免窍绰 历厘橇肺矫历
' Parameter :
'	input Parameter
'		@userID   : 酒捞叼(鞘荐)
'		@eMail    : EMail(可记)
'		@juminNo  : 林刮殿废锅龋(可记)
'		@phoneNo  : 傈拳锅龋(可记)
'		@zipNo    : 快祈锅龋(可记)
'		@address  : 惑技林家(可记)
'		@isMail   : EMail 荐脚咯何(可记)
'		@channel  : 啊涝版肺(可记)
'	return Value
'			 0 : works ok
'			-1 : DB Error
' 荐沥老
'	2007-08-07 : PANDORA TV 盲澄眠啊肺 牢秦 @channel 眠啊窃(啊涝版肺)
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_insertOption]
	@userNo  int
,	@eMail   nvarchar(70) = NULL
,	@juminNo nvarchar(20) = NULL
,	@phoneNo nvarchar(20) = NULL
,	@zipNo   int = NULL
,	@address nvarchar(90) = NULL
,	@isMail  bit = NULL
,	@channel nvarchar(20) = NULL
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	INSERT INTO tUserOption (nUserNo, sEmail, sJuminNo, sPhoneNo, nZipNo, sAddress, bIsMail, sChannel)
	VALUES (@userNo, @eMail, @juminNo, @phoneNo, @zipNo, @address, @isMail, @channel)
	IF @@ERROR <> 0
		RETURN (-1)
	ELSE
		RETURN (0)
	
	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_loginGame]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_loginGame
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   拌沥喉钒牢 荤侩磊狼 版快 usp_User_chkBlock 历厘橇肺矫历甫 龋免窍咯
'   拌沥喉钒 惑怕狼 蜡瘤咯何甫 眉农窃.
' Parameter :
'	input Parameter
'		@userID	: 酒捞叼
'		@userPW	: 厚剐锅龋
'	output Paramter
'		@userNo		: 蜡历锅龋(角菩矫 0 阑 RETURN)
'		@authID		: 蜡历殿鞭(1:雀盔, 2:努肺令海鸥抛胶磐, 3:抛胶飘, 9:包府磊)
'		@block		: 拌沥喉钒沥焊
'		@isLoginable: 肺弊牢 啊瓷咯何(弊缝沥氓)
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_loginGame]
	@userID      nvarchar(20)
,	@userPW      nvarchar(16)
,	@userNo      int = 0 OUTPUT
,	@authID      tinyint = 0 OUTPUT
,	@block     	 int = 0 OUTPUT
,	@isLoginable int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @RET INT	
	SET @userNo = 0
	
	SELECT @userNo = nUserNo, @authID = U.nAuthID, @block = bIsBlock, @isLoginable = bIsLoginAble
	  FROM dbo.tUser AS U(NOLOCK) INNER JOIN dbo.tUserAuth AS UA(NOLOCK) ON U.nAuthID = UA.nAuthID
	 WHERE sUserID = @userID AND sUserPW = @userPW AND bIsDelete = 0
	IF @userNo = 0 BEGIN
		SET @userNo = -1
		SET @authID = 0
		SET @block  = 0
	END
	ELSE BEGIN
		IF @block = 1 BEGIN
			EXEC usp_User_chkBlock @userNo, @RET OUTPUT
			IF @RET = 1 BEGIN
				SELECT @block = bIsBlock FROM tUser(NOLOCK) WHERE nUserNo = @userNo
			END
		END
	END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_setLoginable]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
'--------------------------------------------------------------------------------------
' 窃荐疙 : usp_User_setLoginable
' 郴  侩 : 弊缝喊 立加啊瓷咯何甫 汲沥窍绰 橇肺矫历
' 累己磊 : 绊悼闭
' 累己老 : 2005斥 09岿 09老
' 滚  怜 : 1.0
' 牢  磊 :
'			input Parameter
'				@AUTH_ID		: 弊缝锅龋
'				@IS_LOGINABLE	: 啊瓷咯何(True/False)
'			output Parameter
'				0 : 角菩
'				1 : 己傍
'--------------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[usp_User_setLoginable]
	@authID      int
,	@isLoginable int
,	@RET         int = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON

	IF EXISTS(SELECT nAuthID FROM tUserAuth WHERE nAuthID = @authID) BEGIN
		UPDATE tUserAuth SET bIsLoginAble = @isLoginable WHERE nAuthID = @authID
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
			SET @RET = 0
		ELSE
			SET @RET = 1
	END
	ELSE BEGIN
		SET @RET = 0
	END

	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_update]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_update
' 累己磊 : Ko Dong Gyun(kodong@aragon.co.kr)
' 累己老 : 2007斥 05岿 07老
' Description :
'   Web俊辑 雀盔啊涝矫 龋免窍绰 历厘橇肺矫历
' Parameter :
'	input Parameter
'		@userNo   : 蜡历锅龋(鞘荐)
'		@eMail    : EMail(可记)
'		@phoneNo  : 傈拳锅龋(可记)
'		@zipNo    : 快祈锅龋(可记)
'		@address  : 惑技林家(可记)
'		@isMail   : EMail 荐脚咯何(可记)
'	output Paramter
'		@userNo
'			n  : 蜡历锅龋
'	return Value
'			 0 : works ok
'			-1 : unknown userNo
'			-9 : DB Error
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_update]
	@userNo   int
,	@eMail    nvarchar(70) = NULL
,	@phoneNo  nvarchar(20) = NULL
,	@zipNo    int = NULL
,	@address  nvarchar(90) = NULL
,	@isMail   bit = NULL
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0) BEGIN
		BEGIN TRAN

		UPDATE tUserOption 
		   SET sEmail = @eMail, sPhoneNo = @phoneNo, nZipNo = @zipNo, sAddress = @address, bIsMail = @isMail
		 WHERE nUserNo = @userNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			RETURN (-9)
		END
		ELSE BEGIN
			COMMIT TRAN
			RETURN (0)
		END
	END
	ELSE BEGIN
		RETURN (-1)
	END

	SET LOCK_TIMEOUT -1
	SET NOCOUNT OFF
END

GO
/****** Object:  StoredProcedure [dbo].[usp_User_updatePassword]    Script Date: 12/20/2015 6:07:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------------------
' 捞  抚 : usp_User_updatePassword
'------------------------------------------------------------------------------------*/
CREATE PROCEDURE [dbo].[usp_User_updatePassword]
	@userNo int
,	@prevPW nvarchar(20)
,	@userPW nvarchar(20)
,	@userIP nvarchar(30)
AS
BEGIN
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 5000

	DECLARE @CURR_PW AS NVARCHAR(20)

	IF EXISTS(SELECT nUserNo FROM tUser WITH(NOLOCK) WHERE nUserNo = @userNo AND bIsDelete = 0) BEGIN
		SELECT @CURR_PW = sUserPW FROM tUser WHERE nUserNo = @userNo
		If @CURR_PW <> @prevPW BEGIN
			RETURN (-2)
		END

		BEGIN TRAN

		UPDATE tUser SET sUserPW = @userPW WHERE nUserNo = @userNo
		IF @@ERROR <> 0 OR @@ROWCOUNT <> 1 BEGIN
			ROLLBACK TRAN
			RETURN (-9)
		END

		INSERT INTO tPasswordLog (nUserNo, sPrevPW, sCurrPW, sUserIP)
		VALUES (@userNo, @CURR_PW, @userPW, @userIP)
		IF @@ERROR <> 0 OR @@IDENTITY = 0 BEGIN
			ROLLBACK TRAN
			RETURN (-9)
		END

		COMMIT TRAN
		RETURN (0)
	END
	ELSE BEGIN
		RETURN (-1)
	END
END

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城道具写入背包(2)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tChargeItem'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品号码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'goodsNo'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'category'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品分组(相同的组在购买窗选择时出现)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'goodsNoCheck'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品l图片文件名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'imageName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖单位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'unit'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖单位说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'unitDescription'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖价格' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'price'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折扣价格(%率)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'discount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折价出售开始日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'discountStartDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'折价出售结束日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'discountEndDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否新商品(1是/0否)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'isNew'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'热销商品(1是/0否)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'isPopular'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'推荐商品(1是/0否)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'isRecommand'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否允许贩卖(1是/0否)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'isSell'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖开始日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'sellStartDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖结束日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'sellEndDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'贩卖数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'sellCount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品入库日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'registerDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem', @level2type=N'COLUMN',@level2name=N'description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城道具' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItem'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory', @level2type=N'COLUMN',@level2name=N'category'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商品类别名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上位类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory', @level2type=N'COLUMN',@level2name=N'parent'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用(1启用/0不启用)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory', @level2type=N'COLUMN',@level2name=N'status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类别入库日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory', @level2type=N'COLUMN',@level2name=N'registerDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城道具类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tItemCategory'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'商城订单(1)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tOrder'
GO
USE [master]
GO
ALTER DATABASE [Account] SET  READ_WRITE 
GO
