USE [master]
GO
/****** Database Designed by Sedrika ******/
/****** Objekt: Database [ADMINPANEL_DBF] ******/
CREATE DATABASE [ADMINPANEL_DBF]
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ADMINPANEL_DBF', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ADMINPANEL_DBF].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ARITHABORT OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ADMINPANEL_DBF] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ADMINPANEL_DBF] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [ADMINPANEL_DBF] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ENABLE_BROKER
GO
ALTER DATABASE [ADMINPANEL_DBF] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ADMINPANEL_DBF] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ADMINPANEL_DBF] SET READ_WRITE
GO
ALTER DATABASE [ADMINPANEL_DBF] SET RECOVERY SIMPLE
GO
ALTER DATABASE [ADMINPANEL_DBF] SET MULTI_USER
GO
ALTER DATABASE [ADMINPANEL_DBF] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ADMINPANEL_DBF] SET DB_CHAINING OFF
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[ATTRIBUTE_TBL] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ATTRIBUTE_TBL](
[ID] [int] NOT NULL,
[Name] [text] COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'1', N'STR' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'2', N'STA' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'3', N'DEX' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'4', N'INT' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'9', N'Kritische Trefferrate' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'77', N'Kritischer Schaden' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'11', N'Geschwindigkeit' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'24', N'Angriffs Geschwindigkeit' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'35', N'Zusätzl. HP Rate' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'36', N'Zusätzl. MP Rate' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'37', N'Zusätzl. FP Rate' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[ATTRIBUTE_TBL]( [ID], [Name] ) VALUES( N'75', N'Magiebeschleunigung' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[AUTH_TBL] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AUTH_TBL](
[AuthKey] [char](1) COLLATE Latin1_General_CI_AS NOT NULL,
[AuthName] [text] COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'F', N'Normal' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'G', N'Mapper' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'H', N'GM Lv. 1' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'I', N'GM Lv. 2' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'J', N'GM Lv. 3' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'K', N'Developer' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'L', N'Operator' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'P', N'Admin' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[AUTH_TBL]( [AuthKey], [AuthName] ) VALUES ( N'Z', N'Super Admin' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[Config_Panel] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Config_Panel](
[Col] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Val] [text] COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'PasswordHash', N'kikugalanet' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'PanelTitle', N'Administration Panel © 2013 Sedrika' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'PanelPassword', N'71ec9e24074e6c24d295080b5b323ebd' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'AllowServerCommands', N'checked' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'APIPort', N'17000' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'BackupPath', N'C:\\DB_Backup\\' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Panel] ( [Col], [Val] ) VALUES ( N'SecurityCheck', N'checked' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[Config_PSC] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Config_PSC](
[Col] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Val] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'10,00', N'1000' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'15,00', N'1500' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'20,00', N'2000' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'25,00', N'2750' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'30,00', N'3500' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'50,00', N'6000' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_PSC] ( [Col], [Val] ) VALUES ( N'100,00', N'13000' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[Config_Server] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Config_Server](
[Col] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Val] [text] COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Account', N'1. Account\\AccountServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Certifier', N'2. Certifier\\Certifier.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Database', N'3. Database\\DatabaseServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Core', N'4. Core\\CoreServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Login', N'5. Login\\LoginServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Cache', N'6. Cache\\CacheServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'World', N'7. World\\WorldServer.exe' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'Antihack', N'-' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[Config_Server] ( [Col], [Val] ) VALUES ( N'DirectoryPath', N'H:\\Games\\Flyff\\Server\\Program\\' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[ITEM_TBL] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ITEM_TBL](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,
[Name] [text] COLLATE Latin1_General_CI_AS NULL,
[Command] [text] COLLATE Latin1_General_CI_AS NULL,
[Image] [text] COLLATE Latin1_General_CI_AS NULL,
[Level] [int] NULL,
[Cost] [int] NULL,
[Job] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[JOB_TBL] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JOB_TBL](
[ID] [int] NULL,
[Name] [text] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'0', N'Vagrant' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'1', N'Mercenary' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'2', N'Assist' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'3', N'Acrobat' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'4', N'Magician' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'6', N'Knight' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'7', N'Blade' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'8', N'Jester' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'9', N'Ranger' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'10', N'Ringmaster' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'11', N'Billposter' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'12', N'Psykeeper' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'13', N'Elementor' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'16', N'Master Knight' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'17', N'Master Blade' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'18', N'Master Jester' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'19', N'Master Ranger' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'20', N'Master Ringmaster' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'21', N'Master Billposter' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'22', N'Master Psykeeper' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'23', N'Master Elementor' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'24', N'Hero Knight' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'25', N'Hero Blade' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'26', N'Hero Jester' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'27', N'Hero Ranger' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'28', N'Hero Ringmaster' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'29', N'Hero Billposter' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'30', N'Hero Psykeeper' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'31', N'Hero Elementor' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'32', N'Templar' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'33', N'Slayer' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'34', N'Harlequin' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'35', N'Crackshooter' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'36', N'Seraph' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'37', N'Force Master' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'38', N'Mentalist' )
GO
INSERT INTO [ADMINPANEL_DBF].[dbo].[JOB_TBL] ( [ID], [Name] ) ( N'39', N'Arcanist' )
GO
USE [ADMINPANEL_DBF]
GO
/****** Objekt: Table [dbo].[Maintenance] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Maintenance](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[Account] [varchar](16) COLLATE Latin1_General_CI_AS NOT NULL,
[OldPassword] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[NewPassword] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[NewPassword_Blank] [varchar](6) COLLATE Latin1_General_CI_AS NOT NULL,
[IsMaintenance] [int] NOT NULL,
[Date] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [ADMINPANEL_DBF]
GO
/****** Objekt: StoredProcedure [dbo].[AdminPanel] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sedrika
-- Create date: 03.11.2012
-- Description: Admin Panel
-- =============================================
CREATE PROCEDURE [dbo].[AdminPanel]
@user varchar(16) = '',
@pass varchar(32) = '',
@action varchar(64) = '',
@id int = 0,
@cat int = 0,
@title varchar(128) = '',
@image varchar(128) = '',
@content text = '',
@maintenance_newpass varchar(32) = '',
@maintenance_newpass_blank varchar(6) = '',
@maintenance_seq int = 0,
@worth varchar(6) = ''
AS
BEGIN
SET NOCOUNT ON;
if @action = 'Login'
begin
if exists (select * from [ACCOUNT_DBF].[dbo].[ACCOUNT_TBL] where [account] = @user and [password] = @pass )
begin
select 'RetVal' = 1
end
else
begin
select 'RetVal' = 0
end
end
else if @action = 'GetLord'
begin
SELECT TOP 1 m_szName FROM CHARACTER_01_DBF.dbo.[tblLord] a LEFT JOIN CHARACTER_01_DBF.dbo.CHARACTER_TBL b ON RIGHT('0000000' + CONVERT(VARCHAR(7),a.idLord),7)= b.m_idPlayer ORDER BY s_date DESC
end
else if @action = 'GetMVP'
begin
SELECT TOP 1 m_szName FROM CHARACTER_01_DBF.dbo.[tblCombatJoinPlayer] JP LEFT JOIN CHARACTER_01_DBF.dbo.tblCombatInfo CI ON JP.CombatID = CI.CombatID LEFT JOIN CHARACTER_01_DBF.dbo.CHARACTER_TBL C ON JP.PlayerID = C.m_idPlayer WHERE CI.[Status] = '30' ORDER BY JP.CombatID DESC, Point DESC
end
else if @action = 'GetGSWinner'
begin
SELECT TOP 1 m_szGuild FROM CHARACTER_01_DBF.dbo.[tblCombatJoinGuild] JG LEFT JOIN CHARACTER_01_DBF.dbo.tblCombatInfo CI ON JG.CombatID = CI.CombatID LEFT JOIN CHARACTER_01_DBF.dbo.GUILD_TBL G ON JG.GuildID = G.m_idGuild WHERE CI.[Status] = '30' ORDER BY JG.CombatID DESC, Point DESC
end
else if @action = 'GetCountAccount'
begin
select COUNT(*) as RetVal from ACCOUNT_DBF.dbo.ACCOUNT_TBL
end
else if @action = 'GetCountCharacter'
begin
select COUNT(*) as RetVal from CHARACTER_01_DBF.dbo.CHARACTER_TBL
end
else if @action = 'GetCountGuild'
begin
select COUNT(*) as RetVal from CHARACTER_01_DBF.dbo.GUILD_TBL
end
else if @action = 'GetCountOnlineUser'
begin
select COUNT(*) as RetVal from CHARACTER_01_DBF.dbo.CHARACTER_TBL where MultiServer != 0
end
else if @action = 'UpdateNewsCat'
begin
UPDATE [WEBSITE_DBF].[dbo].web_newscategories set title = @title, icon = @image where ncatid = @id
end
else if @action = 'AddNewsCat'
begin
insert into WEBSITE_DBF.dbo.web_newscategories( title, icon )
values( @title, @image )
end
else if @action = 'UpdateNews'
begin
UPDATE [WEBSITE_DBF].[dbo].web_news set title = @title, [text] = @content, category = @cat where nid = @id
end
else if @action = 'AddNews'
begin
insert into WEBSITE_DBF.dbo.web_news( title, [text], category, author, [datetime], [views] )
values( @title, @content, @cat, @user, GETDATE(), 0 )
end
else if @action = 'IsAdmin'
begin
if exists( select * from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where [account] = @user and m_chLoginAuthority = 'P' or [account] = @user and m_chLoginAuthority = 'Z' )
begin
select 'RetVal' = 1
end
else
begin
select 'RetVal' = 0
end
end
else if @action = 'GetAccountInfo'
begin
if exists( select * from ACCOUNT_DBF.dbo.ACCOUNT_TBL where [account] = @user)
begin
declare @ret_cash varchar(32)
declare @ret_auth1 varchar(1)
declare @ret_regdate varchar(32)
declare @ret_btime varchar(32)
declare @ret_etime varchar(32)
declare @ret_email varchar(128)
declare @ret_lastip varchar(15)
set @ret_cash = (select cash from ACCOUNT_DBF.dbo.ACCOUNT_TBL where [account] = @user)
set @ret_auth1 = (select m_chLoginAuthority from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where [account] = @user)
set @ret_regdate = (select regdate from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where [account] = @user)
set @ret_email = (select email from ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL where [account] = @user)
if exists( select * from LOGGING_01_DBF.dbo.LOG_LOGIN_TBL where [account] = @user)
begin
set @ret_lastip = (select top 1 remoteIP from LOGGING_01_DBF.dbo.LOG_LOGIN_TBL where [account] = @user order by SEQ desc)
end
else
begin
set @ret_lastip = '-'
end
select 'RetVal' = @user + '|' + @ret_cash + '|' + @ret_auth1 + '|' + @ret_regdate + '|' +
@ret_email + '|' + @ret_lastip
end
else
begin
select 'RetVal' = 0
end
end
else if @action = 'GetCharacterFromAccount'
begin
if exists( select * from ACCOUNT_DBF.dbo.ACCOUNT_TBL where [account] = @user)
begin
select m_szName, isblock, MultiServer, m_nLevel from CHARACTER_01_DBF.dbo.CHARACTER_TBL where account = @user
end
else
begin
select 0
end
end
else if @action = 'GetCharacterInfo'
begin
if exists (select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user )
begin
declare @ret_idPlayer varchar(7)
declare @ret_status_onoff varchar(1)
declare @ret_level varchar(3)
declare @ret_job varchar(2)
declare @ret_world varchar(4)
declare @ret_pos_x varchar(20)
declare @ret_pos_y varchar(20)
declare @ret_pos_z varchar(20)
declare @ret_isblock varchar(1)
declare @ret_playerslot varchar(1)
declare @ret_sex2 varchar(1)
declare @ret_hp varchar(15)
declare @ret_mp varchar(15)
declare @ret_fp varchar(15)
declare @ret_penya varchar(10)
declare @ret_str varchar(7)
declare @ret_sta varchar(7)
declare @ret_dex varchar(7)
declare @ret_int varchar(7)
declare @ret_gp varchar(7)
declare @ret_auth2 varchar(1)
declare @ret_bankpw varchar(4)
declare @ret_guild varchar(32)
declare @ret_couple varchar(32)
declare @ret_messenger varchar(5)
declare @ret_account varchar(16)
set @ret_idPlayer = (select m_idPlayer from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_status_onoff = (select MultiServer from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_level = (select m_nLevel from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_job = (select m_nJob from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_world = (select dwWorldID from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_pos_x = (select m_vPos_x from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_pos_y = (select m_vPos_y from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_pos_z = (select m_vPos_z from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_isblock = (select isblock from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_playerslot = (select playerslot from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_sex2 = (select m_dwSex from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_hp = (select m_nHitPoint from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_mp = (select m_nManaPoint from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_fp = (select m_nFatiguePoint from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_penya = (select m_dwGold from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_str = (select m_nStr from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_sta = (select m_nSta from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_dex = (select m_nDex from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_int = (select m_nInt from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_gp = (select m_nRemainGP from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_auth2 = (select m_chAuthority from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
set @ret_bankpw = (select m_BankPw from CHARACTER_01_DBF.dbo.BANK_TBL where m_idPlayer = @ret_idPlayer)
set @ret_account = (select account from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_idPlayer = @ret_idPlayer)
if exists (select * from CHARACTER_01_DBF.dbo.GUILD_MEMBER_TBL where m_idPlayer = @ret_idPlayer)
begin
set @ret_guild = (select m_szGuild from CHARACTER_01_DBF.dbo.GUILD_TBL where m_idGuild = (select m_idGuild from CHARACTER_01_DBF.dbo.GUILD_MEMBER_TBL where m_idPlayer = @ret_idPlayer))
end
else
begin
set @ret_guild = '-'
end
if exists (select * from CHARACTER_01_DBF.dbo.tblCouplePlayer where idPlayer = CONVERT(integer, @ret_idplayer))
begin
declare @ret_couple_partner varchar(7)
set @ret_couple_partner = (select idPlayer from CHARACTER_01_DBF.dbo.tblCouplePlayer where cid = (select cid from CHARACTER_01_DBF.dbo.tblCouplePlayer where idPlayer = CONVERT( integer, @ret_idPlayer )) and idPlayer != CONVERT( integer, @ret_idPlayer ) )
set @ret_couple = (select m_szName from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_idPlayer = RIGHT( '0000000'+ @ret_couple_partner, 7))
end
else
begin
set @ret_couple = '-'
end
set @ret_messenger = (select COUNT(*) from CHARACTER_01_DBF.dbo.tblMessenger where idPlayer = @ret_idplayer)
select 'RetVal' = @user + ' (' + @ret_idPlayer + ')|' + @ret_status_onoff + '|' + @ret_level + '|' + @ret_job + '|' + @ret_world + '|' +
@ret_pos_x + '|' + @ret_pos_y + '|' + @ret_pos_z + '|' + @ret_isblock + '|' + @ret_playerslot + '|' + @ret_sex2 + '|' + @ret_hp + '|' +
@ret_mp + '|' + @ret_fp + '|' + @ret_penya + '|' + @ret_str + '|' + @ret_sta + '|' + @ret_dex + '|' + @ret_int + '|' + @ret_gp + '|' +
@ret_auth2 + '|' + @ret_bankpw + '|' + @ret_guild + '|' + @ret_couple + '|' + @ret_messenger + '|' + @ret_account + '|' + @ret_idPlayer
end
else
begin
select 'RetVal' = 0
end
end
else if @action = 'GetCharacterMessenger'
begin
if exists( select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user )
begin
declare @ret_idPlayer2 varchar(7)
set @ret_idPlayer2 = (select m_idPlayer from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
select idFriend from CHARACTER_01_DBF.dbo.tblMessenger where idPlayer = @ret_idPlayer2
end
else
begin
select 0
end
end
else if @action = 'GetCharacterNameViaID'
begin
if exists(select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_idPlayer = @user)
begin
select 'RetVal' = (select m_szName from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_idPlayer = @user )
end
else
begin
select 'RetVal' = '-';
end
end
else if @action = 'GetCharacterIDViaName'
begin
if exists(select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user)
begin
select 'RetVal' = (select m_idPlayer from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user )
end
else
begin
select 'RetVal' = '-';
end
end
else if @action = 'BackupDatabase'
begin
declare @path varchar(512)
declare @path1 varchar(512)
declare @path2 varchar(512)
declare @path3 varchar(512)
declare @path4 varchar(512)
--declare @path5 text
set @path = (select Val from [Config_Panel] where Col = 'BackupPath')
set @path1 = @path + 'ACCOUNT_DBF.bak'
set @path2 = @path + 'CHARACTER_01_DBF.bak'
set @path3 = @path + 'WEBSITE_DBF.bak'
set @path4 = @path + 'ADMINPANEL_DBF.bak'
--set @path5 = @path + 'LOGGING_01_DBF.bak'
BACKUP DATABASE [ACCOUNT_DBF] TO DISK = @path1 WITH NOFORMAT, NOINIT, NAME = N'ACCOUNT_DBF-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [CHARACTER_01_DBF] TO DISK = @path2 WITH NOFORMAT, NOINIT, NAME = N'CHARACTER_01_DBF-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [WEBSITE_DBF] TO DISK = @path3 WITH NOFORMAT, NOINIT, NAME = N'WEBSITE_DBF-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [ADMINPANEL_DBF] TO DISK = @path4 WITH NOFORMAT, NOINIT, NAME = N'ADMINPANEL_DBF-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--BACKUP DATABASE [LOGGING_01_DBF] TO DISK = @path5 WITH NOFORMAT, NOINIT, NAME = N'LOGGING_01_DBF-Vollständig Datenbank Sichern', SKIP, NOREWIND, NOUNLOAD, STATS = 10
select 'RetVal' = '<div class="success">Datenbank Backup wurde erstellt!</div>'
end
else if @action = 'ClearLoggingDatabase'
begin
TRUNCATE TABLE LOGGING_01_DBF.dbo.CHARACTER_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_BILLING_ITEM_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_CHARACTER_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_DEATH_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_GUILD_BANK_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_GUILD_DISPERSION_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_GUILD_SERVICE_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_GUILD_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_GUILD_WAR_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_HONOR_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_INS_DUNGEON_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_ITEM_EVENT_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_ITEM_REMOVE_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_ITEM_SEND_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_ITEM_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_LEVELUP_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_PK_PVP_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_LOGIN_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_QUEST_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_RESPAWN_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_SKILL_FREQUENCY_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_SVRDOWN_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.LOG_UNIQUE_TBL
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblCampus_PointLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblCampusLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblChangeNameHistoryLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblChangeNameLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblGuildHouse_FurnitureLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblGuildHouseLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblLogExpBox
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblPetLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblQuestLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblQuizAnswerLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblQuizLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblQuizUserLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblSkillPointLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblSystemErrorLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblTradeDetailLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblTradeItemLog
TRUNCATE TABLE LOGGING_01_DBF.dbo.tblTradeLog
DBCC SHRINKDATABASE (LOGGING_01_DBF)
select 'RetVal' = '<div class="success">Logging Datenbank wurde bereinigt!</div>'
end
else if @action = 'GetCharacterList'
begin
select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL order by m_szName asc
end
else if @action = 'DeleteCharacter'
begin
update CHARACTER_01_DBF.dbo.CHARACTER_TBL set isblock = 'D' where m_szName = @user
select 'RetVal' = '<div class="success">' + @user + ' wurde gelöscht.</div>'
end
else if @action = 'RestoreCharacter'
begin
update CHARACTER_01_DBF.dbo.CHARACTER_TBL set isblock = 'F' where m_szName = @user
select 'RetVal' = '<div class="success">' + @user + ' wurde wiederhergestellt.</div>'
end
else if @action = 'GetMaintenanceId'
begin
select COUNT(*) as RetVal from Maintenance where Account = @user
end
else if @action = 'SetMaintenance'
begin
if exists (select * from ACCOUNT_DBF.dbo.ACCOUNT_TBL where account = @user )
begin
if exists (select * from Maintenance where [Account] = @user and [IsMaintenance] = 1)
begin
Select 'RetVal' = '<div class="fail"><b>' + @user + '</b> ist bereits in der Wartung!</div>'
end
else
begin
declare @maintenance_oldpass varchar(32)
set @maintenance_oldpass = (select [password] from ACCOUNT_DBF.dbo.ACCOUNT_TBL where [account] = @user)
insert into Maintenance( [Account], [OldPassword], [NewPassword], [NewPassword_Blank], [IsMaintenance], [Date] )
values ( @user, @maintenance_oldpass, @maintenance_newpass, @maintenance_newpass_blank, 1, GETDATE() )
update ACCOUNT_DBF.dbo.ACCOUNT_TBL set [password] = @maintenance_newpass, [id_no1] = @maintenance_newpass, [id_no2] = @maintenance_newpass where [account] = @user
Select 'RetVal' = '<div class="success">Wartung von <b>' + @user + '</b> hat begonnen. Passwort lautet <b>' + @maintenance_newpass_blank + '</b></div>'
end
end
else
begin
select 'RetVal' = '<div class="fail"><b>' + @user + '</b> existiert nicht!</div>'
end
end
else if @action = 'RemMaintenance'
begin
if exists ( select * from Maintenance where [SEQ] = @maintenance_seq and [IsMaintenance] = 0)
begin
Select 'RetVal' = '<div class="fail">Diese SEQ Id ist nicht mehr in der Wartung!</div>'
end
else
begin
declare @maintenance_oldpass2 varchar(32)
declare @maintenance_name varchar(16)
set @maintenance_oldpass2 = (select [OldPassword] from Maintenance where [SEQ] = @maintenance_seq)
set @maintenance_name = (select [ACCOUNT] from Maintenance where [SEQ] = @maintenance_seq)
update Maintenance set [IsMaintenance] = 0 where [SEQ] = @maintenance_seq
update ACCOUNT_DBF.dbo.ACCOUNT_TBL set [password] = @maintenance_oldpass2, [id_no1] = @maintenance_oldpass2, [id_no2] = @maintenance_oldpass2 where [account] = @maintenance_name
Select 'RetVal' = '<div class="success">Wartung von <b>' + @maintenance_name + '</b> beendet!</div>'
end
end
else if @action = 'AddPSCCash'
begin
if exists (select * from WEBSITE_DBF.dbo.web_psclogs where pscid = @id and done = 0 )
begin
declare @cashworth int
set @cashworth = (select val from config_psc where Col = (select worth from WEBSITE_DBF.dbo.web_psclogs where pscid = @id))
update ACCOUNT_DBF.dbo.ACCOUNT_TBL set cash = ( cash + @cashworth )
update WEBSITE_DBF.dbo.web_psclogs set done = 1 where pscid= @id
select 'RetVal' = '1'
end
else
begin
select 'RetVal' = '0'
end
end
else if @action = 'IsUserOnline'
begin
if exists (select * from CHARACTER_01_DBF.dbo.CHARACTER_TBL where m_szName = @user and MultiServer > 0)
begin
select 'RetVal' = '1';
end
else
begin
select 'RetVal' = '0';
end
end
END
USE [ADMINPANEL_DBF]
GO
/****** Objekt: StoredProcedure [dbo].[usp_Inventory] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Sedrika
-- Create date: 02.01.2013
-- Description: usp_Inventory
-- =============================================
CREATE PROCEDURE [dbo].[usp_Inventory]
@action char(2) = 'I1',
@user varchar(32) = '',
@nPocket int = 0
AS
BEGIN
SET NOCOUNT ON;
declare @m_idPlayer char(7)
set @m_idPlayer = (select m_idPlayer from [CHARACTER_01_DBF].[dbo].[CHARACTER_TBL] where m_szName = @user)
-- Inventory
if @action = 'I1'
begin
select 'RetVal' = (select m_Inventory from [CHARACTER_01_DBF].[dbo].[INVENTORY_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'I2'
begin
select 'RetVal' = (select m_apIndex from [CHARACTER_01_DBF].[dbo].[INVENTORY_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'I3'
begin
select 'RetVal' = (select m_dwGold from [CHARACTER_01_DBF].[dbo].[CHARACTER_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'I4'
begin
select 'RetVal' = (select m_extInventory from [CHARACTER_01_DBF].[dbo].[INVENTORY_EXT_TBL] where m_idPlayer = @m_idPlayer)
end
-- Bank
else if @action = 'B1'
begin
select 'RetVal' = (select m_Bank from [CHARACTER_01_DBF].[dbo].[BANK_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'B2'
begin
select 'RetVal' = (select m_apIndex_Bank from [CHARACTER_01_DBF].[dbo].[BANK_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'B3'
begin
select 'RetVal' = (select m_dwGoldBank from [CHARACTER_01_DBF].[dbo].[BANK_TBL] where m_idPlayer = @m_idPlayer)
end
else if @action = 'B4'
begin
select 'RetVal' = (select m_extBank from [CHARACTER_01_DBF].[dbo].[BANK_EXT_TBL] where m_idPlayer = @m_idPlayer)
end
-- Pocket
else if @action = 'P1'
begin
select 'RetVal' = (select szItem from [CHARACTER_01_DBF].[dbo].[tblPocket] where idPlayer = @m_idPlayer and nPocket = @nPocket)
end
else if @action = 'P2'
begin
select 'RetVal' = (select szIndex from [CHARACTER_01_DBF].[dbo].[tblPocket] where idPlayer = @m_idPlayer and nPocket = @nPocket)
end
else if @action = 'P3'
begin
select 'RetVal' = (select szExt from [CHARACTER_01_DBF].[dbo].[tblPocketExt] where idPlayer = @m_idPlayer and nPocket = @nPocket)
end
END