sql_scalarFunction.sql

gistfile1.txt

USE [SmsPanel_v2]
GO
/****** Object:  UserDefinedFunction [dbo].[Scalar_Function_BALANCE_CHECK_USERNAME]    Script Date: 8/7/2018 12:34:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Scalar_Function_BALANCE_CHECK_USERNAME]
(
@PAGE_COUNT BIGINT
, @PANEL_NUMBER BIGINT
, @UserName NVARCHAR(max)
, @Password NVARCHAR(max)
)
RETURNS BIT
AS
BEGIN
	DECLARE @BALANCE BIGINT , @USER_ID BIGINT, @UNLIMITED_BALANCE BIT = 0 ;
	
	
	Select @USER_ID = TblUser.UserID From TblUser
		Where [TblUser].[UserName] like @UserName  COLLATE SQL_Latin1_General_CP1_CI_AS
		 AND [TblUser].[Password] like @Password  COLLATE SQL_Latin1_General_CP1_CI_AS


	SELECT @UNLIMITED_BALANCE  = [TblCreditBalance].limited FROM [TblCreditBalance] WHERE [TblCreditBalance].user_id = @USER_ID;
	IF COUNT(@UNLIMITED_BALANCE) > 0
		BEGIN --USER FOUND
			IF @UNLIMITED_BALANCE = 0
					BEGIN
						SELECT @BALANCE = [tbl_mno_price].price FROM tbl_mno_price where [tbl_mno_price].Number = @PANEL_NUMBER;
						IF COUNT(@BALANCE) > 0
							BEGIN --@PANEL NUMBER IS CORRECT
								SET @BALANCE = @BALANCE * @PAGE_COUNT;
								
								SELECT @BALANCE = @BALANCE - [TblCreditBalance].balance FROM [TblCreditBalance] WHERE [TblCreditBalance].user_id = @USER_ID;

								IF @BALANCE > 0 --CURRENCT BALANCE IS LOWER THAN BALANCE NEEDED
									BEGIN
										SET @BALANCE = 0;
									END
								ELSE
									BEGIN
										SET @BALANCE = 1;
									END
							END
						--ELSE
						--	BEGIN
						--		SET @BALANCE = 0;
						--	END
					END		
			ELSE --USER HAVE UNLIMITED BALANCE 
				BEGIN
					SET @BALANCE = 1;
				END
		END

IF @BALANCE != 1
	SET @BALANCE = 0;

IF TRY_CONVERT(bigint, @BALANCE ) IS NULL   
	BEGIN
		SET @BALANCE = 0
	END

RETURN @BALANCE 
END