My Faction change script

[[DEV]xXDASHXx]

this has posably been released some were but i forget if i have seen it around so i will releace my faction change quary i created with help from tyler. as far as i remember ( been a while sence i used it) everything is 100% acuret and workeing EXCEPT some wepons dont swap acuretly as i had multy used wepons meaning mixed faction style wepons. i can go thrue when i have a bit more time on my hands and acuretly add ALL wepons to alow swapeing of them to exact counter faction wepons. i hope you injoy it also comented out are the points (DP/AP charge) simply un coment them out and set charge at any amount for your fc to work by charge.

USE [GM_Stuff] -- <---- add what db you wish to install this quary into or creat a new db as i did for all GM_Stuff.
GO
/****** Object:  StoredProcedure [dbo].[usp_FC]    Script Date: 01/15/2012 15:47:51
AUTHOR DASH with help from Tyler ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_FC]

@UserID varchar(16)

AS
SET NOCOUNT ON

DECLARE
@User int,
@Country tinyint,
@Family tinyint,
@Charge int,
@Point int,
@CharID Int
--SET @Charge='1000' --Set the cost of Faction Change

SELECT @User=um.UserUID, @Country=umg.Country
FROM PS_UserData.dbo.Users_Master AS um
INNER JOIN PS_GameData.dbo.UserMaxGrow AS umg ON umg.UserUID = um.UserUID
WHERE um.UserID=@UserID

--SELECT @Point=Point FROM PS_UserData.dbo.Users_Master WHERE UserUID=@User

--IF (@Point < @Charge)
-- BEGIN
  --PRINT 'Insufficient AP'
 --RETURN
--END

-- Faction Selection
  UPDATE PS_GameData.dbo.UserMaxGrow SET Country=(
CASE 
 WHEN Country = 0 THEN 1 
 WHEN Country = 1 THEN 0 
 ELSE Country
END)
WHERE UserUID=@User 

  PRINT 'Faction Changed'
  GOTO Family_Change;



/*
 WHEN Family=0 AND Job = 0 THEN 'Fighter'
 WHEN Family=0 AND Job =1  THEN 'Defender'
 WHEN Family=0 AND Job =5  THEN 'Priest'
 WHEN Family=1 AND Job = 3 THEN 'Archer'
 WHEN Family=1 AND Job = 2 THEN 'Ranger'
 WHEN Family=1 AND Job = 4 THEN 'Mage'
 WHEN Family=3 AND Job = 0 THEN 'Warrior'
 WHEN Family=3 AND Job = 1 THEN 'Guardian'
 WHEN Family=3 AND Job = 3 THEN 'Hunter'
 WHEN Family=2 AND Job = 4 THEN 'Pagan'
 WHEN Family=2 AND Job = 2 THEN 'Assassin'
 WHEN Family=2 AND Job = 5 THEN 'Oracle'
*/

Family_Change:
UPDATE PS_GameData.dbo.Chars 
SET [Family] = (
CASE 
 WHEN Family=0 AND Job=5 THEN 2
 WHEN Family=0 AND Job IN (0,1) THEN 3

 WHEN Family=1 AND Job=3 THEN 3
 WHEN Family=1 AND Job IN (2,4) THEN 2

 WHEN Family=2 AND Job=5 THEN 0
 WHEN Family=2 AND Job IN (2,4) THEN 1

 WHEN Family=3 AND Job=3 THEN 1
 WHEN  Family=3 AND Job IN (0,1) THEN 0

 ELSE Family
END)
WHERE UserUID=@User
PRINT 'toon changed sucessfully'

UPDATE PS_GameData.dbo.Chars 
SET [Class] = (
CASE 
 WHEN Class='Fighter' THEN 'Warrior'
 WHEN Class='Warrior' THEN 'Fighter'
 WHEN Class='Defender' THEN 'Guardian'
 WHEN Class='Guardian'  THEN 'Defender'
 WHEN Class='Ranger' THEN 'Assassin'
 WHEN Class='Assassin' THEN 'Ranger'
 WHEN Class='Priest' THEN 'Oracle'
 WHEN Class='Oracle' THEN 'Priest'
WHEN Class='Archer' THEN 'Hunter'
 WHEN Class='Hunter' THEN 'Archer'
WHEN Class='Mage' THEN 'Pagan'
 WHEN Class='Pagan' THEN 'Mage'

 ELSE Class
END)
WHERE UserUID=@User
PRINT 'toon changed sucessfully'




--SET SPAWN POINTS TO AH
Map_Spawn_Set:
UPDATE PS_GameData.dbo.Chars 
SET Map=42, PosX=63,PosY=2, PosZ=57  
WHERE UserID=@UserID
Print 'All Toons Moved To AH'


--Deletion of Teleportation Spots (Not finished yet)
SELECT CharID INTO #CharTemp FROM PS_GameData.dbo.Chars WHERE UserID=@UserID
DELETE FROM [PS_GameData].[dbo].[CharSavePoint] WHERE [CharID] IN (SELECT [CharID] FROM #CharTemp)
GOTO ItemUpdate;

ItemUpdate:
UPDATE PS_GameData.dbo.CharItems
SET [Type]=(
CASE Type
WHEN 1 THEN 3
 WHEN 3 THEN 1
 WHEN 2 THEN 4
 WHEN 4 THEN 2
 WHEN 11 THEN 14
 WHEN 14 THEN 11
 WHEN 16 THEN 31
 WHEN 31 THEN 16
 WHEN 17 THEN 32
 WHEN 32 THEN 17
 WHEN 18 THEN 33
 WHEN 33 THEN 18
 WHEN 19 THEN 34
 WHEN 34 THEN 19
 WHEN 20 THEN 35
 WHEN 35 THEN 20
 WHEN 21 THEN 36
 WHEN 36 THEN 21
 ELSE [Type]
END)
WHERE CharID IN (SELECT [CharID]FROM #CharTemp);
PRINT 'Items changed'
GOTO WHUpdate;

WHUpdate:
UPDATE PS_GameData.dbo.UserStoredItems
SET [Type]=(
CASE Type
WHEN 1 THEN 3
 WHEN 3 THEN 1
 WHEN 2 THEN 4
 WHEN 4 THEN 2
 WHEN 11 THEN 14
 WHEN 14 THEN 11
 WHEN 16 THEN 31
 WHEN 31 THEN 16
 WHEN 17 THEN 32
 WHEN 32 THEN 17
 WHEN 18 THEN 33
 WHEN 33 THEN 18
 WHEN 19 THEN 34
 WHEN 34 THEN 19
 WHEN 20 THEN 35
 WHEN 35 THEN 20
 WHEN 21 THEN 36
 WHEN 36 THEN 21
 ELSE [Type]
END)
WHERE UserUID = @User ;
PRINT 'WHItems changed'
GOTO ItemID;

ItemID:
UPDATE PS_GameData.dbo.CharItems set ItemID = ( ([Type]*1000)+[TypeID])
WHERE CharID IN (SELECT [CharID]FROM #CharTemp);
GOTO WHItemID;

WHItemID:
UPDATE PS_GameData.dbo.UserStoredItems set ItemID = ( ([Type]*1000)+[TypeID])
WHERE UserUID=@User
--GOTO DP_Charge;

--Point removal
--DP_Charge:
--UPDATE PS_UserData.dbo.Users_Master
--SET Point=(Point-@Charge)
--WHERE UserUID=@User
--PRINT convert(varchar(30), @Charge) + ' DP deleated sucesffuly'


--Dropping temporary tables
DROP TABLE #CharTemp

SET NOCOUNT OFF

edited to anounce this section as it dosent exist in any db unless added manyaly as i did

 UPDATE PS_GameData.dbo.Chars 
SET [Class] = (
CASE 
 WHEN Class='Fighter' THEN 'Warrior'
 WHEN Class='Warrior' THEN 'Fighter'
 WHEN Class='Defender' THEN 'Guardian'
 WHEN Class='Guardian'  THEN 'Defender'
 WHEN Class='Ranger' THEN 'Assassin'
 WHEN Class='Assassin' THEN 'Ranger'
 WHEN Class='Priest' THEN 'Oracle'
 WHEN Class='Oracle' THEN 'Priest'
WHEN Class='Archer' THEN 'Hunter'
 WHEN Class='Hunter' THEN 'Archer'
WHEN Class='Mage' THEN 'Pagan'
 WHEN Class='Pagan' THEN 'Mage'

 ELSE Class
END)
WHERE UserUID=@User
PRINT 'toon changed sucessfully'

simply coment this section out unless you add the row for class into you tabel as i did

[JohnHeatz]

Is it me or you have a customized database?

GM_Stuff is something that I have never seen on a database

[[DEV]xXDASHXx]

Originally Posted by JohnHeatz

Is it me or you have a customized database?

GM_Stuff is something that I have never seen on a database

OOPSSSSSSSSSSS lmao ya i have a 100% compleat custumized db i will fix that lmao... i created a DB named GM_Stuff and that is were ALL baned account info and created quarys get inserted in my db to make it a bit cleaner as i have 1 db with all gm/admin activated info is stored into and alow access to for other admins instead of allowing access to all dbs its one db with in return tightens my db a bit with other access to it

i am workeing on a text document set that includes my most used quarys from this fc scrip to 3 day bans 7day bans ip bans admin/gm account creations FULL server account swips admin/gm account deleation and swip just a few quarys i played with when i was the dev at dashing shaiya befor i closed it and retierd. SO if there are any requests on quarys just drop a post here and i will see what i can get acomplished.. my sql isnot top notch so it may include un nessesary checks and calls stuff like that but it will work and any BETTER sql coder is morethen welcome to edit any of my quarys to alow a more clean look.

[Cosmin6294]

i have erorr in database , DEV can you help me ?

[Cosmin6294]

who can help me ? pls?