USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [CGI].[CGI_SubtractSilk_VAS]    Script Date: 30/04/2017 12:49:41 ص ******/
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