ALTER   PROCEDURE [dbo].[TR_BUDGET_CHECK_LIMIT_YEAR_Byid]
@p_GD_ID	varchar(20)  = NULL,
@p_GD_CODE	varchar(20)  = NULL,
@p_BRANCH_ID varchar(20)  = NULL,
@p_DEP_ID	varchar(20)  = NULL,
@p_KHOI_ID	varchar(20)  = NULL,
@p_TLNAME	varchar(20)  = NULL,
@p_ROLENAME	varchar(20)  = NULL,
@p_MONTH varchar(20) = NULL,
@p_YEAR varchar(20) = NULL,
@p_BUDGET_LIMIT_CURRENT decimal(18,0) OUT,
@p_BUDGET_USED_CURRENT decimal(18,0) OUT
AS
	-- validation
	DECLARE @l_BUDGET_LIMIT_CURRENT DECIMAL(18,0)
	-- Lấy hạn mức đã sử dụng đến hiện tại
	DECLARE @l_BUDGET_USED_CURRENT DECIMAL(18,0)
	DECLARE @l_BUDGET_USED_M1 DECIMAL(18,0), @l_BUDGET_USED_M2 DECIMAL(18,0), @l_BUDGET_USED_M3 DECIMAL(18,0), @l_BUDGET_USED_M4 DECIMAL(18,0), @l_BUDGET_USED_M5 DECIMAL(18,0),
	@l_BUDGET_USED_M6 DECIMAL(18,0), @l_BUDGET_USED_M7 DECIMAL(18,0), @l_BUDGET_USED_M8 DECIMAL(18,0), @l_BUDGET_USED_M9 DECIMAL(18,0), @l_BUDGET_USED_M10 DECIMAL(18,0),
	@l_BUDGET_USED_M11 DECIMAL(18,0), @l_BUDGET_USED_M12 DECIMAL(18,0)

	IF(ISNULL(@p_ROLENAME, '') = '')
	BEGIN
		-- duyet moi kiem tra han muc
		IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND AUTH_STATUS = 'A' AND BUDGET_YEAR = @p_YEAR))
		BEGIN
			--Validation is here
			/*
			NSCP theo định mức năm sẽ được tính bằng lũy kế.
			Ban đầu, sẽ có 1 ngân sách năm được cấp, nhưng chỉ là dự kiến(hứa sẽ cấp), còn ngân sách thật sự sẽ được chia từng tháng.
			VD: Ngân sách năm được cấp 1 tỷ. Nhưng 1 tỷ này sẽ chia ra cho 12 tháng.
			T1 được cấp 100tr, nếu dùng quá định mức sẽ báo.
			*/
			-- Lấy hạn mức lũy kế theo tháng hiện tại
			SET @l_BUDGET_LIMIT_CURRENT = ISNULL((SELECT BUDGET_LIMIT_CURRENT = CASE	WHEN MONTH(GETDATE()) = 1 then M1
																						WHEN MONTH(GETDATE()) = 2 then M2
																						WHEN MONTH(GETDATE()) = 3 then M3
																						WHEN MONTH(GETDATE()) = 4 then M4
																						WHEN MONTH(GETDATE()) = 5 then M5
																						WHEN MONTH(GETDATE()) = 6 then M6
																						WHEN MONTH(GETDATE()) = 7 then M7
																						WHEN MONTH(GETDATE()) = 8 then M8
																						WHEN MONTH(GETDATE()) = 9 then M9
																						WHEN MONTH(GETDATE()) = 10 then M10
																						WHEN MONTH(GETDATE()) = 11 then M11
																						WHEN MONTH(GETDATE()) = 12 then M12
																						ELSE M1
																				END
											FROM TR_REQ_BUDGET_YEAR_LIMIT 
											WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'),0)
			-- Lấy hạn mức đã sử dụng đến hiện tại
			SET @l_BUDGET_USED_M1  = ISNULL((SELECT SUM(M1)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M2  = ISNULL((SELECT SUM(M2)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M3  = ISNULL((SELECT SUM(M3)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M4  = ISNULL((SELECT SUM(M4)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M5  = ISNULL((SELECT SUM(M5)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M6  = ISNULL((SELECT SUM(M6)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M7  = ISNULL((SELECT SUM(M7)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M8  = ISNULL((SELECT SUM(M8)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M9  = ISNULL((SELECT SUM(M9)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M10 = ISNULL((SELECT SUM(M10) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M11 = ISNULL((SELECT SUM(M11) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M12 = ISNULL((SELECT SUM(M12) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' GROUP BY GD_ID),0)

			SET @l_BUDGET_USED_CURRENT = @l_BUDGET_USED_M1 + @l_BUDGET_USED_M2 + @l_BUDGET_USED_M3 + @l_BUDGET_USED_M4 + @l_BUDGET_USED_M5 + @l_BUDGET_USED_M6 + @l_BUDGET_USED_M7 + @l_BUDGET_USED_M8 
			+ @l_BUDGET_USED_M9 + @l_BUDGET_USED_M10 + @l_BUDGET_USED_M11 + @l_BUDGET_USED_M12 

			SET @p_BUDGET_LIMIT_CURRENT = @l_BUDGET_LIMIT_CURRENT;
			SET @p_BUDGET_USED_CURRENT = @l_BUDGET_USED_CURRENT;
			
			-- CHECK NEU KHONG AP DUNG TINH HAN MUC THI BYPASS
			IF(ISNULL((SELECT BUDGET_LIMIT_AMT FROM TR_REQ_BUDGET_YEAR_LIMIT WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' ),0) > 0)
			BEGIN
				IF(@l_BUDGET_USED_CURRENT > @l_BUDGET_LIMIT_CURRENT)
				BEGIN
					RETURN '-1'
				END
				ELSE
				BEGIN
					RETURN '0'
				END
			END
			ELSE
			BEGIN
				RETURN '1'
			END
			
		END
		ELSE
		BEGIN
			RETURN '1'
		END
	END
	ELSE
	BEGIN
		print 'tlname'
		-- duyet moi kiem tra han muc
		IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @p_GD_ID AND KHOI_ID = @p_KHOI_ID AND BRANCH_ID = @p_BRANCH_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A'))
		BEGIN
			print 'da duyet'
			--Validation is here
			/*
			NSCP theo định mức năm sẽ được tính bằng lũy kế.
			Ban đầu, sẽ có 1 ngân sách năm được cấp, nhưng chỉ là dự kiến(hứa sẽ cấp), còn ngân sách thật sự sẽ được chia từng tháng.
			VD: Ngân sách năm được cấp 1 tỷ. Nhưng 1 tỷ này sẽ chia ra cho 12 tháng.
			T1 được cấp 100tr, nếu dùng quá định mức sẽ báo.
			*/
			-- Lấy hạn mức lũy kế theo tháng hiện tại
			SET @l_BUDGET_LIMIT_CURRENT = ISNULL((SELECT BUDGET_LIMIT_CURRENT = CASE	WHEN MONTH(GETDATE()) = 1 then M1
																						WHEN MONTH(GETDATE()) = 2 then M2
																						WHEN MONTH(GETDATE()) = 3 then M3
																						WHEN MONTH(GETDATE()) = 4 then M4
																						WHEN MONTH(GETDATE()) = 5 then M5
																						WHEN MONTH(GETDATE()) = 6 then M6
																						WHEN MONTH(GETDATE()) = 7 then M7
																						WHEN MONTH(GETDATE()) = 8 then M8
																						WHEN MONTH(GETDATE()) = 9 then M9
																						WHEN MONTH(GETDATE()) = 10 then M10
																						WHEN MONTH(GETDATE()) = 11 then M11
																						WHEN MONTH(GETDATE()) = 12 then M12
																						ELSE M1
																				END
											FROM TR_REQ_BUDGET_YEAR_LIMIT 
											WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'),0)
			-- Lấy hạn mức đã sử dụng đến hiện tại
			SET @l_BUDGET_USED_M1  = ISNULL((SELECT SUM(M1)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M2  = ISNULL((SELECT SUM(M2)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M3  = ISNULL((SELECT SUM(M3)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M4  = ISNULL((SELECT SUM(M4)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M5  = ISNULL((SELECT SUM(M5)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M6  = ISNULL((SELECT SUM(M6)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M7  = ISNULL((SELECT SUM(M7)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M8  = ISNULL((SELECT SUM(M8)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M9  = ISNULL((SELECT SUM(M9)  FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M10 = ISNULL((SELECT SUM(M10) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M11 = ISNULL((SELECT SUM(M11) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)
			SET @l_BUDGET_USED_M12 = ISNULL((SELECT SUM(M12) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR GROUP BY GD_ID),0)

			SET @l_BUDGET_USED_CURRENT = @l_BUDGET_USED_M1 + @l_BUDGET_USED_M2 + @l_BUDGET_USED_M3 + @l_BUDGET_USED_M4 + @l_BUDGET_USED_M5 + @l_BUDGET_USED_M6 + @l_BUDGET_USED_M7 + @l_BUDGET_USED_M8 
			+ @l_BUDGET_USED_M9 + @l_BUDGET_USED_M10 + @l_BUDGET_USED_M11 + @l_BUDGET_USED_M12 

			SET @p_BUDGET_LIMIT_CURRENT = @l_BUDGET_LIMIT_CURRENT;
			SET @p_BUDGET_USED_CURRENT = @l_BUDGET_USED_CURRENT;

			print @p_BUDGET_LIMIT_CURRENT
			print @p_BUDGET_USED_CURRENT

			-- CHECK NEU KHONG AP DUNG TINH HAN MUC THI BYPASS
			IF(ISNULL((SELECT BUDGET_LIMIT_AMT FROM TR_REQ_BUDGET_YEAR_LIMIT WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR ),0) > 0)
			BEGIN
				IF(@l_BUDGET_USED_CURRENT > @l_BUDGET_LIMIT_CURRENT)
				BEGIN
					RETURN '-1'
				END
				ELSE
				BEGIN
					RETURN '0'
				END
			END
			ELSE
			BEGIN
				RETURN '1'
			END
		END
		ELSE
		BEGIN
			RETURN '1'
		END
	END