Transform online hours in cash shop points

[sqrobert]

I simply couldn't find a good guide to help me with this so i managed to do it alone and now i want to share it with you.It's my first guide and i hope it's gonna go all right. So let's get started. I will show you how every hour played on mu will automatically be transformed in points for cash shop.

Step 1
First enter Start -> All programs-> Sql server -> enterprise manager ->DATABASE->MuOnline-> Tables
Here look for MEMB_STAT , right click and chose Design Table and create a new column like the following:
Column_name: OnlineHours
Type: int
Allow Nulls: unchecked
Default value: 0

Step 2
Same location in MuOnline enter Stored Procedures and find WZ_DISCONNECT_MEMB. Douable click on it and replace the actual code with this one

CREATE PROCEDURE [dbo].[WZ_DISCONNECT_MEMB]
@memb___id varchar(10)
 AS
Begin    
set nocount on
    Declare  @find_id varchar(10)    
    Declare @ConnectStat tinyint
	declare @OnlineHours real
	declare @cspoints int
    Set @ConnectStat = 0     
    Set @find_id = 'NOT'
    select @find_id = S.memb___id from MEMB_STAT S INNER JOIN MEMB_INFO I ON S.memb___id = I.memb___id 
           where I.memb___id = @memb___id
    if( @find_id <> 'NOT' )    
    begin        
	SELECT @cspoints= cs_points FROM MEMB_INFO where memb___id = @memb___id
        update MEMB_STAT set ConnectStat = @ConnectStat, DisConnectTM = getdate(), OnlineHours = @cspoints/[COLOR="Blue"]10[/COLOR]+(DATEDIFF([COLOR="Red"]hh[/COLOR],ConnectTM,getdate()))
         where memb___id = @memb___id
	SELECT  @OnlineHours =OnlineHours FROM MEMB_STAT WHERE memb___id = @memb___id

	UPDATE [dbo].[MEMB_INFO]
	SET cs_points=(@OnlineHours * [COLOR="Blue"]10[/COLOR])
	WHERE memb___id = @memb___id

    end
end
GO

Click ok and you're done
Things you should know:
The code is set to give 10 points per hour . If you want to change that do the following:
change hh(hour)
in year-yy, yyyy
quarter-qq, q
monthmm- m
day-dd, d
week-wk, ww
minute-mi, n
second-ss, s
These are the symbols if you want to change them, and to change the number of cash shop points received, change the "10" in the code with the number you want to receive.

Hope I was of help. I only tested it on sql server 2000 windows xp, don't know for others. Sorry for my english and sorry if i did something wrong. GL

EDIT: for values higher than 1 you must change in MEMB_STAT the OnlineHours column to REAL NOT INT

 

[PBPDomas]

Nice guide, thank you very much

 

[barry952]

ohh.if this works...will be great to keep people online

 

[DooMDesigN]

and how about for Sql 2005 Standard

 

[sqrobert]

i am sorry but i only knoow a few programing things, made this for my own server and posted the solution cuz i didn't find it anywhere else. If anyone else would like to test it on a different sql please post if it works for other players to see.

 

[babinelo666]

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'hh'.


Help plz

 

[Sunctru]

hehe thats a good idea to keep people online if they want something for free :d

 

[MegaEdge]

@DooMDesigN - I think that works the same way for any MSSQL. I use MSSQL 2008 and was smoothly

---
@sqrobert - I also need this for some time now and I'm looking for. But I have a question: if it does not work and I want to remove this procedure, as I do? Or just make a backup of the contents of WZ_DISCONNECT_MEMB ?

Thank you for sharing.

 

[I Am NoLimiT]

i just want to ask..how it worked if i wanted to transform hours online to PCPOINTS?.

 

[bushwhack_77]

Can you please tell me how to include auto pkclear in this script?

 

[nickypunk001]

Got this error.
Error 446: Cannot resolve collation conflict for equal to operation.

 

[nickypunk001]

Got this error.
Error 446: Cannot resolve collation conflict for equal to operation.

*bump

 

[EliteBattle]

I got same error as well. but i rename cs_points to WCoin

 

[19871986]

UPDATE [dbo].[MEMB_INFO]
SET cs_points=(@OnlineHours * 10)
WHERE memb___id = @memb___id

Here for save old points otherwise its remove before,

UPDATE [dbo].[MEMB_INFO]
SET cs_points=cs_points + (@OnlineHours * 10)
WHERE memb___id = @memb___id

 

[mindfreakz1]

Thanks for this GUIDE keep it up!

 

[jasontechie]

Is there any guide for transforming online hours to PC points?

 

[mRpLaYbOy]

How to transform online hours in webshop credits?
Help Please!

 

[nemoma]

For Webshop Credits and PCPoints, it's actually quite similar.
Assuming you are using MUCore, the location of Webshop Credits should locate at MEMB_CREDITS, thus, you add these lines:

under the declaration:

[B]declare @credits int[/B]

Under Selection:

[B]SELECT @credits= credits FROM MEMB_CREDITS where memb___id = @uid[/B]
Under Calculation of Online Hours ->CSPoints
[B]	UPDATE [dbo].[MEMB_CREDITS]
	SET credits= credits + (@OnlineHours * 10)
	WHERE memb___id = @uid[/B]

PCPoints are similar, assuming you are using the TitanTech Packages, it should be located at SCFPCPoints under Characters table:
declare @SCFPCPoints int
SELECT @SCFPCPoints= SCFPCPoints FROM Character where AccountID = @uid
UPDATE [dbo].[Character]
SET SCFPCPoints= SCFPCPoints + (@OnlineHours * 1)
WHERE AccountID = @uid

These should work.

Notice that the commands must go to their sections! If you paste them together there WILL be an error. My Completed scripts are here:

BEGIN TRANSACTION

SET NOCOUNT ON
	Declare  @find_id varchar(10)
	declare @OnlineHours real
	declare @SCFVipMoney int
	declare @SCFPCPoints int
	declare @credits int

	SELECT @SCFVipMoney= SCFVipMoney FROM MEMB_INFO where memb___id = @uid
	SELECT @SCFPCPoints= SCFPCPoints FROM Character where AccountID = @uid
	SELECT @credits= credits FROM MEMB_CREDITS where memb___id = @uid

IF EXISTS ( SELECT memb___id FROM MEMB_STAT WITH (READUNCOMMITTED)
WHERE memb___id = @uid )
Begin
UPDATE MEMB_STAT
SET DisConnectTM = (getdate()), connectstat = 0,OnlineHours = @SCFVipMoney/10+(DATEDIFF(hh,ConnectTM,getdate())) WHERE memb___id = @uid
	SELECT  @OnlineHours =OnlineHours FROM MEMB_STAT WHERE memb___id = @uid

	UPDATE [dbo].[MEMB_INFO]
	SET SCFVipMoney= SCFVipMoney + (@OnlineHours * 1)
	WHERE memb___id = @uid

	UPDATE [dbo].[Character]
	SET SCFPCPoints= SCFPCPoints + (@OnlineHours * 1)
	WHERE AccountID = @uid

	UPDATE [dbo].[MEMB_CREDITS]
	SET credits= credits + (@OnlineHours * 0)
	WHERE memb___id = @uid
End

Again, don't outright copy it! No two databases are the same!

 

[ihyajade]

For Webshop Credits and PCPoints, it's actually quite similar.
Assuming you are using MUCore, the location of Webshop Credits should locate at MEMB_CREDITS, thus, you add these lines:

under the declaration:

[B]declare @credits int[/B]

Under Selection:

[B]SELECT @credits= credits FROM MEMB_CREDITS where memb___id = @uid[/B]
Under Calculation of Online Hours ->CSPoints
[B]	UPDATE [dbo].[MEMB_CREDITS]
	SET credits= credits + (@OnlineHours * 10)
	WHERE memb___id = @uid[/B]

[/QUTE]

where will I add this? In Table or Stored Procedure?

 

[daezugun]

any additional and clear guidelines?