[Release][Fix] BillingServer cannot find procedure fix

[GigaToni]

Here is "my" version of the bill_get_balance procedure. (I'm a newb in MSSQL, don't judge this), It works (you can enter the shop). But you'll still need the "bill_cancel_purchase" and "bill_purchase_item" procedure. Also I don't think there is currently the actual billing tables for storing these "transactions"

-- =============================================
-- Author:		Lee HyunSung
-- Create date: 2011-05-13
-- Description:	Cash 조회.
-- =============================================
ALTER PROCEDURE [dbo].[bill_get_balance] 
	-- Add the parameters for the stored procedure here
  [USER=1335]user[/USER]ID nvarchar(20) out,	-- Account ID
  [USER=401224]Real[/USER]Cash int out, -- Main cash
  [USER=774015]Bon[/USER]usCash int out, -- Some kind of bonus cash?
  [USER=286651]ret[/USER]Code int out -- hell idk. Just output 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @RC int, @BC int;
	select top 1 @RC=RealCash, @BC=BonusCash from dbo.tbl_UserCash WHERE UserID  [USER=1335]user[/USER]ID
	
	IF ( @@ROWCOUNT <> 1 )
	BEGIN
		INSERT INTO dbo.tbl_UserCash VALUES  [USER=1335]user[/USER]ID, 0, 0, GetDate(), GetDate(), 0);
		set  [USER=401224]Real[/USER]Cash = 0;
		set  [USER=774015]Bon[/USER]usCash = 0;
		set  [USER=286651]ret[/USER]Code = 0;
		
		SET NOCOUNT OFF;
		
		RETURN 0;
	END

	set  [USER=401224]Real[/USER]Cash = @RC;
	set  [USER=774015]Bon[/USER]usCash = @BC;
	set  [USER=286651]ret[/USER]Code = 0;
	
	SET NOCOUNT OFF;
	
	RETURN 0;
END

My "bill_purchase_item" procedure

ALTER PROCEDURE [dbo].[bill_purchase_item] 
	-- Add the parameters for the stored procedure here
	@WorldIdx tinyint,				-- 월드 Index
 [USER=1335]user[/USER]ID nvarchar(20) output,				-- 계정 아이디
 [USER=551894]Char[/USER]Name nvarchar(16),			-- 캐릭터 이름
 [USER=482628]rec[/USER]vCharOID bigint,					-- 케릭터 OID
	@BuyType tinyint,					-- 구매 타입(1:혼자사용,2:선물하기)
	@PurchaseCash int,				-- 총가격
	@ItemID_array nvarchar(1024),		-- 아이템 번호
	@ItemAmount_array nvarchar(1024),	-- 아이템 개수
	@ItemPrice_array nvarchar(1024),	-- 아이템 가격
	
 [USER=401224]Real[/USER]Cash int output,
 [USER=774015]Bon[/USER]usCash int output, 
	@BillNo int output,
 [USER=286651]ret[/USER]Code int output
AS
BEGIN

	declare @t_RealCash int, @t_BonusCash int;
	select top 1 @t_RealCash = (RealCash-@PurchaseCash), @t_BonusCash=BonusCash from [dbo].[tbl_UserCash] WHERE UserID [USER=1335]user[/USER]ID;
	
	update [dbo].[tbl_UserCash] SET RealCash=@t_RealCash WHERE UserID [USER=1335]user[/USER]ID;
	
	INSERT INTO [dbo].[tbl_CashItemLog]  VALUES(@WorldIdx, [USER=1335]user[/USER]ID, [USER=551894]Char[/USER]Name, [USER=482628]rec[/USER]vCharOID, @BuyType, @PurchaseCash, @t_RealCash, @t_BonusCash, @t_RealCash, @t_BonusCash, 0, 0, GETDATE(), GETDATE());
	
	select [USER=401224]Real[/USER]Cash = @t_RealCash, [USER=774015]Bon[/USER]usCash = @t_BonusCash, @BillNo = @@IDENTITY, [USER=286651]ret[/USER]Code = 0;
END

 

[duracel]

The tables for this procedures are dbo.tbl_UserCash - for balance, dbo.tbl_CashItemLog and dbo.tbl_CashItemInfo for purchase, for cancel proc i don't know how exactly it's used.
The actual cash a character has is RealCash+Bonus Cash. RetCode is the return code it sends back to the billing server in case of error.

This is my procedure to open cashshop and send back the amount the character has. I changed dbo.usp_BillGetBalance

USE [DragonaAccount]
GO
/****** Object:  StoredProcedure [dbo].[usp_BillGetBalance]    Script Date: 01/08/2015 18:29:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Lee HyunSung
-- Create date: 2011-05-13
-- Description:	Cash 조회.
-- =============================================
ALTER PROCEDURE [dbo].[usp_BillGetBalance] 
	-- Add the parameters for the stored procedure here
	@IdName nvarchar(20)	-- 계정 아이디
AS
BEGIN

	SET Transaction Isolation Level Read uncommitted
	SET NOCOUNT ON;

	declare   [USER=401224]Real[/USER]Cash int, 
		   [USER=774015]Bon[/USER]usCash int, 
		   [USER=286651]ret[/USER]Code int
	
	
	SET   [USER=286651]ret[/USER]Code = 0
	IF (NOT EXISTS ( SELECT UserID FROM tbl_UserCash where UserID = @IdName))
	BEGIN
		SET   [USER=286651]ret[/USER]Code = 200
		INSERT INTO tbl_UserCash (UserID)
		VALUES (@IdName)
	END
	ELSE
	BEGIN
		SELECT   [USER=401224]Real[/USER]Cash = RealCash,   [USER=774015]Bon[/USER]usCash = BonusCash from tbl_UserCash where UserID = @IdName 
		SET   [USER=286651]ret[/USER]Code = 0
	END

	SELECT @IdName,   [USER=401224]Real[/USER]Cash,   [USER=774015]Bon[/USER]usCash,   [USER=286651]ret[/USER]Code
	
	SET NOCOUNT OFF;
END

Can't remember if i did also change the tbl_UserCash so that RealCash and BonusCash are set to default 0.

And as a temporary for buying items from cashshop i use this dbo.usp_BillPurchaseItem (still need to add some checks so it can update the cash user has after he bought something:

USE [DragonaAccount]
GO
/****** Object:  StoredProcedure [dbo].[usp_BillPurchaseItem]    Script Date: 01/08/2015 18:24:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Lee HyunSung
-- Create date: 2011-05-13
-- Description:	Cash 아이템 구매.
-- =============================================
ALTER PROCEDURE [dbo].[usp_BillPurchaseItem] 
	-- Add the parameters for the stored procedure here
	@i_WorldIndex tinyint,				-- 월드 Index
	@i_IdName nvarchar(20),				-- 계정 아이디
	@i_CharName nvarchar(16),			-- 캐릭터 이름
	@i_CharOID bigint,					-- 케릭터 OID
	@i_BuyType tinyint,					-- 구매 타입(1:혼자사용,2:선물하기)
	@i_PurchaseCash int,				-- 총가격
	@i_ItemID_array nvarchar(1024),		-- 아이템 번호
	@i_ItemAmount_array nvarchar(1024),	-- 아이템 개수
	@i_ItemPrice_array nvarchar(1024)	-- 아이템 가격
AS
BEGIN
	
	SET NOCOUNT ON;

	declare   [USER=401224]Real[/USER]Cash int,   [USER=774015]Bon[/USER]usCash int, @BillNo int,   [USER=286651]ret[/USER]Code int

	INSERT INTO tbl_CashItemInfoLog(WorldIdx, UserID, CharName, RecvCharOID, BuyType, ItemID, ItemAmount, ItemPrice, PurchaseCash)
	VALUES (@i_WorldIndex, @i_IdName, @i_CharName, @i_CharOID, @i_BuyType, @i_ItemID_array, @i_ItemAmount_array, @i_ItemPrice_array, @i_PurchaseCash )

	SELECT @BillNo = BillNo from tbl_CashItemInfoLog
	SELECT   [USER=401224]Real[/USER]Cash = RealCash,   [USER=774015]Bon[/USER]usCash = BonusCash from tbl_UserCash where UserID = @i_IdName
	
	
	UPDATE tbl_UserCash 
	SET RealCash =   [USER=401224]Real[/USER]Cash - @i_PurchaseCash
	WHERE UserID = @i_IdName
		
	select @BillNo,@i_IdName , [USER=401224]Real[/USER]Cash , [USER=774015]Bon[/USER]usCash

END

With this table

USE [DragonaAccount]
GO

/****** Object:  Table [dbo].[tbl_CashItemInfoLog]    Script Date: 01/08/2015 18:45:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_CashItemInfoLog](
	[BillNo] [int] IDENTITY(1,1) NOT NULL,
	[WorldIdx] [tinyint] NOT NULL,
	[UserID] [nvarchar](20) NOT NULL,
	[CharName] [nvarchar](16) NOT NULL,
	[RecvCharOID] [bigint] NOT NULL,
	[BuyType] [tinyint] NOT NULL,
	[ItemID] [nvarchar](1024) NOT NULL,
	[ItemAmount] [nvarchar](1024) NOT NULL,
	[ItemPrice] [nvarchar](1024) NOT NULL,
	[PurchaseCash] [int] NOT NULL,
	[RealCashDiff] [int] NOT NULL,
	[BonusCashDiff] [int] NOT NULL,
	[ChangeRealCash] [int] NOT NULL,
	[ChangeBonusCash] [int] NOT NULL,
	[ChangeState] [int] NOT NULL,
	[CancelState] [tinyint] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastUpdateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl_CashItemInfoLog] PRIMARY KEY CLUSTERED 
(
	[BillNo] 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

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_CharName]  DEFAULT ('') FOR [CharName]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_RealCashDiff]  DEFAULT ((0)) FOR [RealCashDiff]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_BonusCashDiff]  DEFAULT ((0)) FOR [BonusCashDiff]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_ChangeRealCash]  DEFAULT ((0)) FOR [ChangeRealCash]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_ChangeBonusCash]  DEFAULT ((0)) FOR [ChangeBonusCash]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_ChangeState]  DEFAULT ((0)) FOR [ChangeState]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_CancelState]  DEFAULT ((0)) FOR [CancelState]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO

ALTER TABLE [dbo].[tbl_CashItemInfoLog] ADD  CONSTRAINT [DF_tbl_CashItemInfoLog_LastUpdateDate]  DEFAULT (getdate()) FOR [LastUpdateDate]
GO

 

[magraopb]

Hi all,
We keep working with these files, for now.. trying fix CashShop, duracel your first procedure have 2 errors we fix them.
I'm trying to fix others procedures "Bill_Purchase_Item"


Can you help us?