[Dev.Ri3o]

اعمل دول كدة

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_SubtractSilk_VAS]    Script Date: 1/13/2017 2:56:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER                  PROCEDURE [CGI].[CGI_SubtractSilk_VAS]
	@RefundID VARCHAR(25),
	@UserID   VARCHAR(25),
	@Password   VARCHAR(50),
	@PkgID   INT,
	@NumSilk INT,
	@Price INT
as
	
	DECLARE @UserJID INT
	DECLARE @SilkRemain INT
	-- DECLARE @PointRemain INT
	SET @UserJID = CGI.checkAuthUserPass(@UserID,@Password)
	IF( @UserJID > 0)
	     BEGIN
		SET @UserJID = CGI.getUserJID(@UserID)
		IF( not exists( SELECT OrderNumber from SK_SilkBuyList where OrderNumber = @RefundID))
		   BEGIN
			SET @SilkRemain = CGI.getSilkOwn(@UserJID)
			IF(@SilkRemain>=@NumSilk)
				BEGIN
					BEGIN TRANSACTION
					UPDATE SK_Silk SET silk_own = silk_own - @NumSilk WHERE JID = @UserJID
					--Insert Silk Own				
					INSERT SK_SubtractSilk_VAS(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber,SlipPaper,RegDate) VALUES( @UserJID,0,1,@NumSilk,@SilkRemain - @NumSilk,@PkgID,1,@RefundID,'User Refund Silk from VDC-Net2E Billing System',GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@SilkRemain - @NumSilk,@NumSilk,0,1)
					IF (@@error <> 0 or @@rowcount = 0)
						BEGIN
							SELECT  Result = 'FAIL'
							ROLLBACK TRANSACTION
							RETURN
						END
					SELECT Result = 'SUCCESS'
					COMMIT TRANSACTION	
					RETURN
				END
			ELSE
				BEGIN
					SELECT  Result = 'NOTENOUGH'
					RETURN
				END
		END
             ELSE
                BEGIN
                     SELECT  Result = 'ORDEREXIST'
		     RETURN
                END
           END
	ELSE
		BEGIN
			SELECT  Result = 'INVALIDUSER';
			RETURN
		END

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_VTCWebPurchaseSilk]    Script Date: 1/13/2017 2:56:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CGI].[CGI_VTCWebPurchaseSilk]
	@OrderID VARCHAR(25), 
	@UserID   VARCHAR(25), 
	@PkgID   INT, 
	@NumSilk INT, 
	@Price INT, 
	@PartnerTransID Bigint,
	@PGCompany tinyint,
	@ClientUser Varchar(25),
	@ClientIPA Varchar(15)
as
	DECLARE @UserJID INT
	DECLARE @SilkRemain INT
	--DECLARE @PointRemain INT
	SET @UserJID = CGI.getUserJID(@UserID)
	IF( @UserJID >= 0)
	    BEGIN
		IF( not exists( SELECT OrderNumber from SK_SilkBuyList where OrderNumber = @OrderID))
		BEGIN
			BEGIN TRANSACTION
--	Select @NumSilk= CAST(ROUND(@NumSilk*1.1,0) as int)
                           IF( not exists( SELECT * from SK_Silk where JID = @UserJID))
				BEGIN
					INSERT SK_Silk(JID,silk_own,silk_gift,silk_point)VALUES(@UserJID,@NumSilk,0,0)
					--INSERT Silk Own
--					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber, PGCompany, PayMethod,SlipPaper,RegDate) VALUES( @UserJID,0,0,@NumSilk,@NumSilk,@PkgID,1,@OrderID,3,1,'Purchase Silk from VTC paygate System',GETDATE())
					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber, PGCompany, PayMethod, PGUniqueNo, srID, SlipPaper, IP, RegDate) VALUES( @UserJID,0,0,@NumSilk,@NumSilk,@PkgID,1, @OrderID, @PGCompany, 1, @PartnerTransID, @ClientUser,  'Purchase '+ cast(@NumSilk as varchar(25)) + ' Silk using ' + cast(@Price  as varchar(25)) + ' Vcoin', @ClientIPA, GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@NumSilk,@NumSilk,0,0)
				END
			    ELSE
				BEGIN
					SET @SilkRemain = CGI.getSilkOwn(@UserJID)
					UPDATE SK_Silk SET silk_own = silk_own + @NumSilk WHERE JID = @UserJID
					--INSERT Silk Own					
--					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber, PGCompany, PayMethod, SlipPaper,RegDate) VALUES( @UserJID,0,0,@NumSilk,@SilkRemain + @NumSilk,@PkgID,1,@OrderID,3,1,'Purchase Silk from VTC paygate System',GETDATE())
					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber, PGCompany, PayMethod, PGUniqueNo, srID, SlipPaper, IP, RegDate)  VALUES( @UserJID,0,0,@NumSilk,@SilkRemain + @NumSilk,@PkgID,1,@OrderID, @PGCompany, 1, @PartnerTransID, @ClientUser,  'Purchase ' + cast(@NumSilk as varchar(25))  + ' Silk using ' + cast(@Price as varchar(25))  + ' Vcoin', @ClientIPA, GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@SilkRemain + @NumSilk,@NumSilk,0,0)
				END
			IF (@@error <> 0 or @@rowcount = 0)
				BEGIN
					SELECT Result = 'FAIL'
					ROLLBACK TRANSACTION
					RETURN
				END
			SELECT Result = 'SUCCESS'
			COMMIT TRANSACTION	
			RETURN
		   END
		ELSE --else if order
		   BEGIN
			SELECT Result = 'ORDEREXIST'
		   END    
	    END
	ELSE
		BEGIN
			SELECT Result = 'NOUSER'
			RETURN
		END

SET QUOTED_IDENTIFIER OFF

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_WebGetTotalSilk]    Script Date: 1/13/2017 2:57:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER   PROCEDURE [CGI].[CGI_WebGetTotalSilk]
as
	DECLARE @own FLOAT
	SET @own = 0
	SELECT @own = sum(silk_own) FROM SK_Silk
	IF( @@error <> 0 or @@rowcount = 0 or @own = 0 or @own IS null)
		BEGIN
			SELECT Result = -1
			RETURN
		END
	ELSE
		BEGIN
			SELECT Result = @own
			RETURN 
		END

SET QUOTED_IDENTIFIER OFF

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_WebPurchaseSilk]    Script Date: 1/13/2017 2:57:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER                   PROCEDURE [CGI].[CGI_WebPurchaseSilk]
	@OrderID VARCHAR(25),
	@UserID   VARCHAR(25),
	@PkgID   INT,
	@NumSilk INT,
	@Price INT
as
	DECLARE @UserJID INT
	DECLARE @SilkRemain INT
	--DECLARE @PointRemain INT
	SET @UserJID = CGI.getUserJID(@UserID)
	IF( @UserJID >= 0)
	    BEGIN
		IF( not exists( SELECT OrderNumber from SK_SilkBuyList where OrderNumber = @OrderID))
		BEGIN
			BEGIN TRANSACTION
                           IF( not exists( SELECT * from SK_Silk where JID = @UserJID))
				BEGIN
					INSERT SK_Silk(JID,silk_own,silk_gift,silk_Point)VALUES(@UserJID,@NumSilk,0,0)
					--INSERT Silk Own
					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber,SlipPaper,RegDate) VALUES( @UserJID,0,0,@NumSilk,@NumSilk,@PkgID,1,@OrderID,"User Purchase Silk from VDC-Net2E Billing System",GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@NumSilk,@NumSilk,0,0)
				END
			    ELSE
				BEGIN
					SET @SilkRemain = CGI.getSilkOwn(@UserJID)
					UPDATE SK_Silk SET silk_own = silk_own + @NumSilk WHERE JID = @UserJID
					--INSERT Silk Own
					
					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber,SlipPaper,RegDate) VALUES( @UserJID,0,0,@NumSilk,@SilkRemain + @NumSilk,@PkgID,1,@OrderID,"User Purchase Silk from VDC-Net2E Billing System",GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@SilkRemain + @NumSilk,@NumSilk,0,0)
				END
			IF (@@error <> 0 or @@rowcount = 0)
				BEGIN
					SELECT Result = "FAIL"
					ROLLBACK TRANSACTION
					RETURN
				END
			SELECT Result = "SUCCESS"
			COMMIT TRANSACTION	
			RETURN
		   END
		ELSE --else if order
		   BEGIN
			SELECT Result = "ORDEREXIST"
		   END    
	    END
	ELSE
		BEGIN
			SELECT Result = "NOUSER"
			RETURN
		END

SET QUOTED_IDENTIFIER OFF

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_WebRefundSilk]    Script Date: 1/13/2017 2:57:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER                  PROCEDURE [CGI].[CGI_WebRefundSilk]
	@RefundID VARCHAR(25),
	@UserID   VARCHAR(25),
	@Password   VARCHAR(50),
	@PkgID   INT,
	@NumSilk INT,
	@Price INT
as
	
	DECLARE @UserJID INT
	DECLARE @SilkRemain INT
	-- DECLARE @PointRemain INT
	SET @UserJID = CGI.checkAuthUserPass(@UserID,@Password)
	IF( @UserJID >= 0)
	     BEGIN
		SET @UserJID = CGI.getUserJID(@UserID)
		IF( not exists( SELECT OrderNumber from SK_SilkBuyList where OrderNumber = @RefundID))
		   BEGIN
			SET @SilkRemain = CGI.getSilkOwn(@UserJID)
			IF(@SilkRemain>=@NumSilk)
				BEGIN
					BEGIN TRANSACTION
					UPDATE SK_Silk SET silk_own = silk_own - @NumSilk WHERE JID = @UserJID
					--Insert Silk Own
					INSERT SK_SilkBuyList(UserJID,Silk_Type,Silk_Reason,Silk_Offset,Silk_Remain,ID,BuyQuantity,OrderNumber,SlipPaper,RegDate) VALUES( @UserJID,0,1,@NumSilk,@SilkRemain - @NumSilk,@PkgID,1,@RefundID,"User Refund Silk from VDC-Net2E Billing System",GETDATE())
					INSERT SK_SilkChange_BY_Web(JID,silk_remain,silk_offset,silk_type,reason) VALUES(@UserJID,@SilkRemain - @NumSilk,@NumSilk,0,1)
					IF (@@error <> 0 or @@rowcount = 0)
						BEGIN
							SELECT  Result = "FAIL"
							ROLLBACK TRANSACTION
							RETURN
						END
					SELECT Result = "SUCCESS"
					COMMIT TRANSACTION	
					RETURN
				END
			ELSE
				BEGIN
					SELECT  Result = "NOTENOUGH"
					RETURN
				END
		END
             ELSE
                BEGIN
                     SELECT  Result = "ORDEREXIST"
		     RETURN
                END
           END
	ELSE
		BEGIN
			SELECT  Result = "INVALIDUSER";
			RETURN
		END

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_GetSilkDataForGameServer]    Script Date: 1/13/2017 2:57:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER    PROCEDURE [dbo].[_GetSilkDataForGameServer]  
@UserJID int,  
@SilkOwn int output,  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--  마ى‌¼ë¦¬ى§€ ىµœى„*يک¸)    
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
@SilkGift  int output,  
@Mileage int output  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
 AS  
 set @SilkOwn = 0  
 set @SilkGift = 0  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--  마ى‌¼ë¦¬ى§€ ىµœى„*يک¸)    
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
 set @Mileage = 0  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--  마ى‌¼ë¦¬ى§€ ىµœى„*يک¸)    
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
 select @silkown = silk_own, @silkgift = silk_gift, @Mileage = silk_point from sk_silk with (nolock) where JID = @UserJID  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@  
  
 return 0