Here, try that.
Code:
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
