![[HELP] ACCOUNT_DBF.sql](https://www.elitepvpers.com/forum/iconimages/flyff-private-server/help-account_dbf-sql_ltr.gif){kind=small}
** edit **
Update Sql Server last version and some errors have been removed, but they remain a part of them.
Error
Code:
Msg 15007, Level 16, State 1, Line 2 'Web' is not a valid login or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. Msg 15151, Level 16, State 1, Line 1 Cannot add the principal 'Web', because it does not exist or you do not have permission. The module 'LOGIN_STR' depends on the missing object 'dbo.USP_PCZoneIP_Check'. The module will still be created; however, it cannot run successfully until the object exists.
ACCOUNT_DBF.sql
PHP Code:
USE [master]
GO
/****** Object: Database [ACCOUNT_DBF] Script Date: 10/6/2561 4:42:31 ******/
CREATE DATABASE [ACCOUNT_DBF]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'ACCOUNT_DBF', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_FLYFF_TEST\MSSQL\DATA\ACCOUNT_DBF.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'ACCOUNT_DBF_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_FLYFF_TEST\MSSQL\DATA\ACCOUNT_DBF_log.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [ACCOUNT_DBF] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ACCOUNT_DBF].[dbo].[sp_fulltext_database] [MENTION=292198]Action[/MENTION] = 'enable'
end
GO
ALTER DATABASE [ACCOUNT_DBF] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ARITHABORT OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ACCOUNT_DBF] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ACCOUNT_DBF] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ENABLE_BROKER
GO
ALTER DATABASE [ACCOUNT_DBF] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ACCOUNT_DBF] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ACCOUNT_DBF] SET MULTI_USER
GO
ALTER DATABASE [ACCOUNT_DBF] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ACCOUNT_DBF] SET DB_CHAINING OFF
GO
ALTER DATABASE [ACCOUNT_DBF] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [ACCOUNT_DBF] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [ACCOUNT_DBF] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [ACCOUNT_DBF] SET QUERY_STORE = OFF
GO
USE [ACCOUNT_DBF]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
USE [ACCOUNT_DBF]
GO
/****** Object: User [Web] Script Date: 10/6/2561 4:42:32 ******/
CREATE USER [Web] FOR LOGIN [Web] WITH DEFAULT_SCHEMA=[db_accessadmin]
GO
ALTER ROLE [db_owner] ADD MEMBER [Web]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [Web]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [Web]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [Web]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Web]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [Web]
GO
ALTER ROLE [db_denydatareader] ADD MEMBER [Web]
GO
ALTER ROLE [db_denydatawriter] ADD MEMBER [Web]
GO
/****** Object: Table [dbo].[ACCOUNT_TBL] Script Date: 10/6/2561 4:42:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL](
[account] [varchar](32) NOT NULL,
[password] [varchar](32) NOT NULL,
[isuse] [char](1) NOT NULL,
[member] [char](1) NOT NULL,
[id_no1] [varchar](32) NULL,
[id_no2] [varchar](32) NULL,
[realname] [char](1) NOT NULL,
[reload] [char](1) NULL,
[OldPassword] [varchar](32) NULL,
[TempPassword] [varchar](32) NULL,
[cash] [int] NULL,
[bonus] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ACCOUNT_TBL_DETAIL] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ACCOUNT_TBL_DETAIL](
[account] [varchar](32) NOT NULL,
[gamecode] [char](4) NOT NULL,
[tester] [char](1) NOT NULL,
[m_chLoginAuthority] [char](1) NOT NULL,
[regdate] [datetime] NOT NULL,
[BlockTime] [char](8) NULL,
[EndTime] [char](8) NULL,
[WebTime] [char](8) NULL,
[isuse] [char](1) NOT NULL,
[secession] [datetime] NULL,
[email] [varchar](100) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[AccountPlay] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccountPlay](
[Account] [varchar](32) NOT NULL,
[PlayDate] [int] NOT NULL,
[PlayTime] [int] NOT NULL,
CONSTRAINT [PK_AccountPlay] PRIMARY KEY CLUSTERED
(
[Account] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[CashHistory] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CashHistory](
[account] [varchar](32) NULL,
[beforeCash] [int] NULL,
[afterCash] [int] NULL,
[regdate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Event_LikeShare] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Event_LikeShare](
[id] [int] IDENTITY(1,1) NOT NULL,
[account] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Event_PCZone_20091027] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Event_PCZone_20091027](
[SEQ] [bigint] NOT NULL,
[id_no1] [varchar](32) NOT NULL,
[id_no2] [varchar](32) NOT NULL,
[account] [varchar](32) NOT NULL,
[s_date] [char](8) NOT NULL,
[RegIP] [varchar](15) NOT NULL,
[m_idPlayer] [char](7) NULL,
[Gift_Item] [bit] NOT NULL,
[Gift_Date] [datetime] NULL,
[serverindex] [char](2) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Event_PCZone_20091027_01] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Event_PCZone_20091027_01](
[SEQ] [bigint] NOT NULL,
[id_no1] [varchar](6) NOT NULL,
[id_no2] [varchar](7) NOT NULL,
[account] [varchar](32) NOT NULL,
[s_date] [char](8) NOT NULL,
[RegIP] [varchar](15) NOT NULL,
[Gift_Item] [bit] NOT NULL,
[Gift_Date] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[GM_Cash] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GM_Cash](
[id] [int] IDENTITY(1,1) NOT NULL,
[account] [varchar](50) NOT NULL,
[value] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PCZone] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PCZone](
[PCZoneID] [int] NOT NULL,
[PCZoneName] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL,
[Phone] [varchar](100) NOT NULL,
[CPU] [varchar](100) NOT NULL,
[VGA] [varchar](100) NOT NULL,
[RAM] [varchar](100) NOT NULL,
[Monitor] [varchar](100) NOT NULL,
[Comment] [varchar](1000) NOT NULL,
[Grade] [tinyint] NOT NULL,
[Account] [varchar](32) NOT NULL,
[RegDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PCZoneIP] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PCZoneIP](
[IPID] [int] NOT NULL,
[PCZoneID] [int] NOT NULL,
[IPFrom1] [tinyint] NOT NULL,
[IPFrom2] [tinyint] NOT NULL,
[IPFrom3] [tinyint] NOT NULL,
[IPFrom4] [tinyint] NOT NULL,
[IPTo4] [tinyint] NOT NULL,
[IsUse] [tinyint] NOT NULL,
[RegDate] [datetime] NOT NULL,
[OperID] [varchar](32) NULL,
[OperDate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblEvent_Board_Provide] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEvent_Board_Provide](
[account] [varchar](32) NULL,
[serverindex] [char](2) NULL,
[m_idPlayer] [char](7) NULL,
[m_szName] [varchar](32) NULL,
[regdate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblEventRecommend] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEventRecommend](
[ofaccount] [varchar](32) NULL,
[byaccount] [varchar](32) NULL,
[regdate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[truemoney] Script Date: 10/6/2561 4:42:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[truemoney](
[card_id] [int] IDENTITY(1,1) NOT NULL,
[password] [varchar](14) NULL,
[user_no] [varchar](32) NULL,
[amount] [int] NULL,
[status] [tinyint] NULL,
[added_time] [datetime] NULL,
[charid] [char](7) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ACCOUNT_TBL] ADD CONSTRAINT [DF_ACCOUNT_TBL_bonus] DEFAULT ((0)) FOR [bonus]
GO
ALTER TABLE [dbo].[AccountPlay] ADD CONSTRAINT [DF_AccountPlay_Account] DEFAULT ('') FOR [Account]
GO
ALTER TABLE [dbo].[AccountPlay] ADD CONSTRAINT [DF_AccountPlay_PlayDate] DEFAULT ('') FOR [PlayDate]
GO
ALTER TABLE [dbo].[AccountPlay] ADD CONSTRAINT [DF_AccountPlay_PlayTime] DEFAULT ((0)) FOR [PlayTime]
GO
ALTER TABLE [dbo].[CashHistory] ADD CONSTRAINT [DF_CashHistory_regdate] DEFAULT (getdate()) FOR [regdate]
GO
/****** Object: StoredProcedure [dbo].[ACCOUNT_STR] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ACCOUNT_STR]
@iaccount VARCHAR(32),
[MENTION=2699544]ipass[/MENTION]word VARCHAR(16)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************
ACCOUNT_STR ????
??? : ???
??? : 2004.01.18
ex) ACCOUNT_STR 'beat','1234'
***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on
IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b
WHERE a.account = b.account AND a.account = @iaccount AND gamecode = 'A000')
BEGIN
IF EXISTS(SELECT account FROM ACCOUNT_TBL
WHERE account = @iaccount AND password = [MENTION=2699544]ipass[/MENTION]word )
-- BEGIN
--
-- DECLARE [MENTION=1603449]Birth[/MENTION]year CHAR(4) [MENTION=1127475]Curr[/MENTION]date CHAR(8)
-- SELECT [MENTION=1603449]Birth[/MENTION]year = CASE WHEN SUBSTRING(id_no2,1,1) IN ('9','0') THEN '18' + SUBSTRING(id_no1,1,2)
-- WHEN SUBSTRING(id_no2,1,1) IN ('1','2') THEN '19' + SUBSTRING(id_no1,1,2)
-- WHEN SUBSTRING(id_no2,1,1) IN ('3','4') THEN '20' + SUBSTRING(id_no1,1,2)
-- WHEN SUBSTRING(id_no2,1,1) IN ('5','6') THEN '21' + SUBSTRING(id_no1,1,2)
-- WHEN SUBSTRING(id_no2,1,1) IN ('7','8') THEN '22' + SUBSTRING(id_no1,1,2)
-- END
-- FROM ACCOUNT_TBL
-- WHERE account = @iaccount
--
-- SELECT [MENTION=1127475]Curr[/MENTION]date = CONVERT(CHAR(8),GETDATE(),112)
--
-- SELECT fError = CASE WHEN a.id_no1 = 'a00000'
-- THEN '0'
-- WHEN b.BlockTime >= [MENTION=1127475]Curr[/MENTION]date AND b.EndTime < [MENTION=1127475]Curr[/MENTION]date
-- THEN '3'
-- WHEN a.realname = 'F'
-- THEN '4'
-- WHEN [MENTION=1603449]Birth[/MENTION]year > DATEADD(Year,-13 [MENTION=1127475]Curr[/MENTION]date)
-- THEN '5'
-- WHEN [MENTION=1603449]Birth[/MENTION]year >= DATEADD(Year,-11 [MENTION=1127475]Curr[/MENTION]date) AND b.tester = '0'
-- THEN '6'
-- ELSE '0' END,
--
-- fText = CASE WHEN a.id_no1 = 'a00000'
-- THEN '?? ? ??'
-- WHEN b.BlockTime >= [MENTION=1127475]Curr[/MENTION]date AND b.EndTime < [MENTION=1127475]Curr[/MENTION]date
-- THEN '??????? ??? ??'
-- WHEN a.realname = 'F'
-- THEN '????? ???'
-- WHEN [MENTION=1603449]Birth[/MENTION]year > DATEADD(Year,-13 [MENTION=1127475]Curr[/MENTION]date)
-- THEN '???? 12? ?? ??? ??? ????? ?? ????.'
-- WHEN [MENTION=1603449]Birth[/MENTION]year >= DATEADD(Year,-11 [MENTION=1127475]Curr[/MENTION]date) AND b.tester = '0'
-- THEN '14? ?? ??? ??? ??? ???? ????? ?? ??? ?????'
-- ELSE '????? 14? ??' END
-- FROM ACCOUNT_TBL a, ACCOUNT_TBL_DETAIL b
-- WHERE a.account = b.account AND b.account = @iaccount AND a.[password] = [MENTION=2699544]ipass[/MENTION]word
-- END
BEGIN
SELECT fError = '0', fText = 'OK'
END
ELSE
BEGIN
SELECT fError = '1', fText = 'Wrong Password !!'
END
END
ELSE
BEGIN
SELECT fError = '2', fText = 'Account Not Exists !!'
END
RETURN
-- ?? ?? Rule
-- 1. (fError=2 ??) ????? ??. ????? "gamecode = A000" ? ??? ??.
-- 2. (fError=1 ??) ????.
-- 3. (fError=0 ??) ?? ? ?? ??( id_no1? a0000 )?? ??.??.??? ??
-- 4. (fError=3 ??) ?????, ???? ??.
-- 5. (fError=4 ??) ????.
-- 6. (fError=5 ??) 12? ???? ??. ???? "???? 12? ?? ??? ??? ]
-- ????? ?? ????." ?? ??.
-- 7. (fError=6 ??) ?????? ?? ?? ?? tester = 0 ?? "14? ?? ??? ???
-- ?????? ????? ?? ??? ?????"?? ??.
-- 8. (fError=0 ??) ?? ???? ?? ???? ??.
set nocount off
GO
/****** Object: StoredProcedure [dbo].[LOGIN_STR] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[LOGIN_STR]
@iaccount VARCHAR(32),
[MENTION=2699544]ipass[/MENTION]word char(32)
-- Ver. 14 PCZoneIP
,@i_IPAddress varchar(15) = '0.0.0.0'
AS
set nocount on
-- Ver 14. PCZoneIP_Check
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check @i_IPAddress, @o_Grade output
IF EXISTS(SELECT a.account FROM ACCOUNT_TBL a,ACCOUNT_TBL_DETAIL b
WHERE a.account = b.account AND a.account = @iaccount ) -- AND gamecode = 'A000')
BEGIN
DECLARE [MENTION=438803]curda[/MENTION]te char(8)
IF EXISTS(SELECT account FROM ACCOUNT_TBL
WHERE account = @iaccount AND password = [MENTION=2699544]ipass[/MENTION]word) BEGIN
SELECT [MENTION=438803]curda[/MENTION]te=CONVERT(CHAR(8), getdate(), 112)
SELECT fError=CASE
-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN '91'
WHEN BlockTime> [MENTION=438803]curda[/MENTION]te THEN '9'
ELSE '0' END,
fText= CASE
-- WHEN session<>@isession OR sessionExpireDt<getdate() THEN 'Session Expired'
WHEN BlockTime> [MENTION=438803]curda[/MENTION]te THEN 'Block' ELSE 'OK' END,
fCheck=tester,
f18='9'
-- Ver14. PCZoneIP
,fPCZone = @o_Grade
FROM ACCOUNT_TBL a INNER JOIN ACCOUNT_TBL_DETAIL b ON (a.account=b.account)
WHERE a.account=@iaccount
END
ELSE BEGIN
SELECT fError = '1', fText = 'Wrong Password !!',fCheck ='',f18='1', fPCZone = '0' -- PCZoneIP (, fPCZone = '0' ??)
END
END
ELSE BEGIN
SELECT fError = '2', fText = 'Account Not Exists !!',fCheck ='',f18='1', fPCZone = '0' -- PCZoneIP (, fPCZone = '0' ??)
END
RETURN
-- ?? ?? Rule
-- 1. (fError=2 ??) ????? ??. ????? "gamecode = A000" ? ??? ??.
-- 2. (fError=1 ??) ????.
-- 3. (fError=0 ??) ?? ? ?? ??( id_no1? a0000 )?? ??.??.??? ??
-- 4. (fError=3 ??) ?????, ???? ??.
-- 5. (fError=4 ??) ????.
-- 6. (fError=5 ??) 12? ???? ??. ???? "???? 12? ?? ??? ??? ]
-- ????? ?? ????." ?? ??.
-- 7. (fError=6 ??) ?????? ?? ?? ?? tester = 0 ?? "14? ?? ??? ???
-- ?????? ????? ?? ??? ?????"?? ??.
-- 8. (fError=0 ??) ?? ???? ?? ???? ??.
set nocount off
GO
/****** Object: StoredProcedure [dbo].[LOGINJOIN_STR] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[LOGINJOIN_STR]
@iGu CHAR(2) = 'A1',
@iaccount VARCHAR(32)
/***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************
LOGINJOIN_STR ????
??? : ???
??? : 2004.01.30
??? : 2004.04.08 @iGu = 'A3' WHERE ?? ??
ex) LOGINONOFF_STR 'A1','1234'
***********************************************************************************
***********************************************************************************
***********************************************************************************
***********************************************************************************/
AS
set nocount on
IF @iGu = 'A1' -- ON
BEGIN
UPDATE ACCOUNT_TBL_DETAIL
SET isuse = 'J'
WHERE account = @iaccount
RETURN
END
ELSE
IF @iGu = 'A2' -- OFF
BEGIN
UPDATE ACCOUNT_TBL_DETAIL
SET isuse = 'O'
WHERE account = @iaccount
RETURN
END
ELSE
IF @iGu = 'A3' -- ALL OFF
BEGIN
UPDATE ACCOUNT_TBL_DETAIL
SET isuse = 'O'
WHERE isuse = 'J'
RETURN
END
set nocount off
GO
/****** Object: StoredProcedure [dbo].[sp_UpdateAccountPassword] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateAccountPassword]
[MENTION=323619]aCCount[/MENTION] varchar(32),
[MENTION=413413]password[/MENTION] varchar(32)
AS
SET NOCOUNT ON
Update ACCOUNT_TBL SET Password = [MENTION=413413]password[/MENTION] Where Account [MENTION=323619]aCCount[/MENTION]
GO
/****** Object: StoredProcedure [dbo].[usp_ChangeEmail] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangeEmail]
[MENTION=323619]aCCount[/MENTION] varchar(32),
[MENTION=950314]email[/MENTION] varchar(100)
as
set nocount on
set xact_abort on
if exists (select * from ACCOUNT_TBL_DETAIL where account = [MENTION=323619]aCCount[/MENTION])
begin
begin tran
update ACCOUNT_TBL_DETAIL
set email = [MENTION=950314]email[/MENTION]
where account = [MENTION=323619]aCCount[/MENTION]
if @@error <> 0
begin
rollback tran
select -1
end
else
begin
commit tran
select 1
end
end
else
begin
select 0
end
GO
/****** Object: StoredProcedure [dbo].[usp_ChangePW] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_ChangePW]
[MENTION=323619]aCCount[/MENTION] varchar(32),
@pw varchar(32)
as
set nocount on
set xact_abort on
if exists (select * from ACCOUNT_TBL where account = [MENTION=323619]aCCount[/MENTION])
begin
begin tran
update ACCOUNT_TBL
set [password] = @pw
where account = [MENTION=323619]aCCount[/MENTION]
if @@error <> 0
begin
rollback tran
select -1
end
else
begin
commit tran
select 1
end
end
else
begin
select 0
end
GO
/****** Object: StoredProcedure [dbo].[usp_CreateNewAccount] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_CreateNewAccount]
[MENTION=323619]aCCount[/MENTION] varchar(32),
@pw varchar(32),
[MENTION=314772]cash[/MENTION] int = 0,
[MENTION=950314]email[/MENTION] varchar(100) = ''
as
set nocount on
set xact_abort on
if not exists (select * from ACCOUNT_TBL where account = [MENTION=323619]aCCount[/MENTION])
begin
begin tran
INSERT ACCOUNT_TBL(account,password,isuse,member,id_no1,id_no2,realname, cash)
VALUES [MENTION=323619]aCCount[/MENTION], @pw, 'T', 'A', '', '', '', [MENTION=314772]cash[/MENTION])
INSERT ACCOUNT_TBL_DETAIL(account,gamecode,tester,m_chLoginAuthority,regdate,BlockTime,EndTime,WebTime,isuse,secession, email)
VALUES [MENTION=323619]aCCount[/MENTION],'A000','2','F',GETDATE(),CONVERT(CHAR(8),GETDATE()-1,112),CONVERT(CHAR(8),DATEADD(year,10,GETDATE()),112),CONVERT(CHAR(8),GETDATE()-1,112),'T',NULL, [MENTION=950314]email[/MENTION])
insert AccountPlay (Account, PlayDate)
select [MENTION=323619]aCCount[/MENTION], convert(int, convert(char(8), getdate(), 112))
if @@error <> 0
begin
rollback tran
select -1
end
else
begin
commit tran
select 1
end
end
else
begin
select 0
end
GO
/****** Object: StoredProcedure [dbo].[usp_MessengerList] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_MessengerList]
[MENTION=2138478]serverin[/MENTION]dex char(2),
@m_szName varchar(32)
as
set nocount on
declare @q1 nvarchar(4000)
declare @m_idPlayer char(7)
set @q1 = '
select @m_idPlayer = m_idPlayer from CHR' + [MENTION=2138478]serverin[/MENTION]dex + '.CHARACTER_' + [MENTION=2138478]serverin[/MENTION]dex + '_DBF.dbo.CHARACTER_TBL where m_szName = ''' + @m_szName + ''''
exec sp_executesql @q1, N'@m_idPlayer char(7) output', @m_idPlayer output
set @q1 = '
select c.m_szName, m_idPlayer
from CHR' + [MENTION=2138478]serverin[/MENTION]dex + '.CHARACTER_' + [MENTION=2138478]serverin[/MENTION]dex + '_DBF.dbo.tblMessenger as m
inner join CHR' + [MENTION=2138478]serverin[/MENTION]dex + '.CHARACTER_' + [MENTION=2138478]serverin[/MENTION]dex + '_DBF.dbo.CHARACTER_TBL as c on m.idFriend = c.m_idPlayer
where m.idPlayer = ''' + @m_idPlayer + ''' and isblock = ''F'' and chUse = ''T'' order by m.idFriend'
exec sp_executesql @q1
GO
/****** Object: StoredProcedure [dbo].[usp_og_ChangePassWord] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_og_ChangePassWord]
[MENTION=323619]aCCount[/MENTION] varchar(32),
[MENTION=413413]password[/MENTION] varchar(32)
as
set nocount on
set xact_abort on
if exists (select * from ACCOUNT_TBL where account = [MENTION=323619]aCCount[/MENTION])
begin
begin tran
Update ACCOUNT_TBL SET Password = [MENTION=413413]password[/MENTION] Where account [MENTION=323619]aCCount[/MENTION]
if @@error <> 0
begin
rollback tran
select -1
end
else
begin
commit tran
select 1
end
end
else
begin
select 0
end
GO
/****** Object: StoredProcedure [dbo].[usp_passwordhistory] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[usp_passwordhistory]
[MENTION=323619]aCCount[/MENTION] varchar(20)
as
set nocount on
set xact_abort on
select ChangeDt
from MANAGE_DBF.dbo.tblChangePasswordHistory
where account = [MENTION=323619]aCCount[/MENTION]
order by ChangeDt desc
GO
/****** Object: StoredProcedure [dbo].[USP_PCZoneIP_Check] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
declare @o_Grade tinyint
exec dbo.USP_PCZoneIP_Check '218.38.238.131', @o_Grade output
select @o_Grade
exec LOGIN_STR 'chipi', '85158c7b8a7f3113b0f32b70ed936b2c', '127.0.0.0'
*/
CREATE proc [dbo].[USP_PCZoneIP_Check]
@i_IPAddress varchar(15)
, @o_Grade tinyint output
as
set nocount on
set transaction isolation level read uncommitted
declare @i_IP1 tinyint
declare @i_IP2 tinyint
declare @i_IP3 tinyint
declare @i_IP4 tinyint
declare [MENTION=539730]INDEX[/MENTION] int
--@i_IP1
set [MENTION=539730]INDEX[/MENTION] = charindex('.', @i_IPAddress)
set @i_IP1 = left(@i_IPAddress, [MENTION=6668744]Index-[/MENTION]1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress) [MENTION=539730]INDEX[/MENTION])
--@i_IP2
set [MENTION=539730]INDEX[/MENTION] = charindex('.', @i_IPAddress)
set @i_IP2 = left(@i_IPAddress, [MENTION=6668744]Index-[/MENTION]1)
set @i_IPAddress = right(@i_IPAddress, len(@i_IPAddress) [MENTION=539730]INDEX[/MENTION])
--@i_IP3, @i_IP4
set [MENTION=539730]INDEX[/MENTION] = charindex('.', @i_IPAddress)
set @i_IP3 = left(@i_IPAddress, [MENTION=6668744]Index-[/MENTION]1)
set @i_IP4 = right(@i_IPAddress, len(@i_IPAddress) [MENTION=539730]INDEX[/MENTION])
--PCZoneIP_Check
select @o_Grade = b.Grade
from PCZoneIP a
inner join PCZone b
on a.PCZoneID = b.PCZoneID
where IsUse in (1, 9)
and IPFrom1 = @i_IP1
and IPFrom2 = @i_IP2
and IPFrom3 = @i_IP3
and IPFrom4 <= @i_IP4 and IPTo4 >= @i_IP4
select @o_Grade = isnull(@o_Grade, 0)
GO
/****** Object: StoredProcedure [dbo].[usp_undopw] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create proc [dbo].[usp_undopw]
@id varchar(20),
[MENTION=4379977]DTime[/MENTION] varchar(25)
as
set nocount on
set xact_abort on
declare [MENTION=284430]check[/MENTION] int
declare [MENTION=1832562]oldpass[/MENTION]word varchar(40)
set [MENTION=284430]check[/MENTION] = (select count([Password]) from MANAGE_DBF.dbo.tblChangePasswordHistory where account = @id and ChangeDt >= [MENTION=4379977]DTime[/MENTION] + '.000' and ChangeDt <= [MENTION=4379977]DTime[/MENTION] + '.999')
if [MENTION=284430]check[/MENTION] = 0)
select 0
else
begin
set [MENTION=1832562]oldpass[/MENTION]word = (select [Password] from MANAGE_DBF.dbo.tblChangePasswordHistory where account = @id and ChangeDt >= [MENTION=4379977]DTime[/MENTION] + '.000' and ChangeDt <= [MENTION=4379977]DTime[/MENTION] + '.999')
update ACCOUNT_DBF.dbo.ACCOUNT_TBL
set [password] = [MENTION=1832562]oldpass[/MENTION]word
where account=@id
select 1
end
GO
/****** Object: StoredProcedure [dbo].[usp_UpdateCashInfo] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[usp_UpdateCashInfo]
[MENTION=323619]aCCount[/MENTION] varchar(32),
[MENTION=314772]cash[/MENTION] int
as
set nocount on
set xact_abort on
if exists (select * from ACCOUNT_TBL where account = [MENTION=323619]aCCount[/MENTION])
begin
begin tran
insert into CashHistory (account, beforeCash, afterCash)
select [MENTION=323619]aCCount[/MENTION], cash, [MENTION=314772]cash[/MENTION] from ACCOUNT_TBL where account = [MENTION=323619]aCCount[/MENTION]
update ACCOUNT_TBL
set cash = [MENTION=314772]cash[/MENTION]
where account = [MENTION=323619]aCCount[/MENTION]
if @@error <> 0
begin
rollback tran
select -1
end
else
begin
commit tran
select 1
end
end
else
begin
select 0
end
GO
/****** Object: StoredProcedure [dbo].[uspChangePassword] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[uspChangePassword]
[MENTION=1122106]Pacco[/MENTION]unt varchar(32),
@pFlag int=1,
[MENTION=1563809]ppa[/MENTION]ssword varchar(32)=''
AS
SET NOCOUNT ON
DECLARE [MENTION=2858660]passwords[/MENTION]tring varchar(50)
DECLARE [MENTION=342323]hash[/MENTION]Password char(32)
DECLARE [MENTION=1832562]oldpass[/MENTION]word char(32)
SELECT [MENTION=1832562]oldpass[/MENTION]word=password FROM ACCOUNT_TBL WHERE account [MENTION=1122106]Pacco[/MENTION]unt
IF @@ROWCOUNT=0 BEGIN
SELECT retValue=9000
RETURN
END
ELSE BEGIN
IF @pFlag=0 BEGIN
IF [MENTION=1563809]ppa[/MENTION]ssword='' BEGIN
PRINT 'automatically convert to password:aeonsoft'
SET [MENTION=1563809]ppa[/MENTION]ssword='aeonsoft'
END
UPDATE ACCOUNT_TBL SET password [MENTION=1563809]ppa[/MENTION]ssword WHERE account [MENTION=1122106]Pacco[/MENTION]unt
IF @@ROWCOUNT=0 BEGIN
SELECT retValue=9001
RETURN
END
INSERT INTO MANAGE_DBF.dbo.tblChangePasswordHistory(account, ChangeDt, Password, NewPassword) VALUES [MENTION=1122106]Pacco[/MENTION]unt, getdate(), [MENTION=1832562]oldpass[/MENTION]word, lower [MENTION=342323]hash[/MENTION]Password))
IF @@ROWCOUNT=0 BEGIN
SELECT retValue=9001
RETURN
END
END
ELSE BEGIN
SELECT TOP 1 [MENTION=1832562]oldpass[/MENTION]word=Password FROM MANAGE_DBF.dbo.tblChangePasswordHistory WHERE account [MENTION=1122106]Pacco[/MENTION]unt ORDER BY ChangeDt DESC
IF @@ROWCOUNT=0 BEGIN
SELECT retValue=9002
RETURN
END
ELSE BEGIN
UPDATE ACCOUNT_DBF.dbo.ACCOUNT_TBL SET password [MENTION=1832562]oldpass[/MENTION]word WHERE account [MENTION=1122106]Pacco[/MENTION]unt
IF @@ROWCOUNT=0 BEGIN
SELECT retValue=9003
RETURN
END
END
END
END
PRINT 'Change Successfully'
SELECT retValue=1
RETURN
SET NOCOUNT OFF
GO
/****** Object: StoredProcedure [dbo].[uspRecommendAccount] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[uspRecommendAccount]
[MENTION=532572]ofa[/MENTION]ccount varchar(32),
[MENTION=538234]Bya[/MENTION]ccount varchar(32)
as
set nocount on
insert into tblEventRecommend(ofaccount, byaccount)
select [MENTION=532572]ofa[/MENTION]ccount, [MENTION=538234]Bya[/MENTION]ccount
GO
/****** Object: StoredProcedure [dbo].[uspVerifyEventReturnAccount] Script Date: 10/6/2561 4:42:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspVerifyEventReturnAccount]
[MENTION=1122106]Pacco[/MENTION]unt varchar(32)
[MENTION=3807069]ReTuRn_[/MENTION]value char(1) output
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM tblEventReturn200607 WHERE account [MENTION=1122106]Pacco[/MENTION]unt) BEGIN
UPDATE tblEventReturn200607 SET ConfirmDt=getdate() WHERE account [MENTION=1122106]Pacco[/MENTION]unt
SET [MENTION=3807069]ReTuRn_[/MENTION]value = 1
PRINT [MENTION=3807069]ReTuRn_[/MENTION]value)
RETURN 1
END
ELSE BEGIN
SET [MENTION=3807069]ReTuRn_[/MENTION]value = 0
PRINT [MENTION=3807069]ReTuRn_[/MENTION]value)
RETURN 0
END
SET NOCOUNT OFF
GO
USE [master]
GO
ALTER DATABASE [ACCOUNT_DBF] SET READ_WRITE
GO
