- Joined
- Aug 14, 2009
- Messages
- 2,304
- Reaction score
- 1,189
-- =============================================
-- 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
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
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
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
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