2 Create brokerReport.sql 2.03 KB
Newer Older
TongZuu committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
IF OBJECT_ID ( 'broker_assignReport', 'P' ) IS NOT NULL 
   DROP PROCEDURE broker_assignReport;

go
CREATE PROCEDURE broker_assignReport
AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL Serializable;
	BEGIN TRANSACTION;
	DECLARE @EmptyNum int;
	DECLARE @SV varchar(20) , @QID varchar(20) , @TYPE varchar(2) , @GROUP varchar(2);
	
	DECLARE @LockResult int;
	EXECUTE @LockResult = sp_getapplock @Resource = 'Serializable_Report',@LockMode = 'Exclusive',@LockTimeout = 0,
    @LockOwner = 'Session',@DbPrincipal = 'dbo';
	    
    IF @LockResult <> 0
		BEGIN
		   ROLLBACK TRANSACTION
		   RAISERROR ( 51001, 16, 1 )
		   RETURN
		END
		
	SELECT 
		@EmptyNum = ISNULL(SUM(THREAD_REPORT-PROCESS_REPORT) , 0)
	FROM vw_csc_server WHERE SERVER_STATUS = '1'
	
	PRINT 'REPORT EMPIY is '
	PRINT @EmptyNum;
	if @EmptyNum > 0 
	BEGIN
		DECLARE db_cursor_queue CURSOR FOR 
		WITH SIMCSC_QUQUE AS(
			SELECT ROW_NUMBER() OVER(ORDER BY ququeid) AS ROWNUMBER,* FROM CSC_QUQUE WHERE PROCESS_GROUP = 'R' and PROCESS_STATUS = 'B'
		)
		SELECT QUQUEID,PROCESS_TYPE,PROCESS_GROUP FROM SIMCSC_QUQUE 
		WHERE ROWNUMBER <= @EmptyNum 
		ORDER BY PROCESS_PRIORITY,RUNNO,QUQUEID
		
		OPEN db_cursor_queue  
		FETCH NEXT FROM db_cursor_queue INTO @QID,@TYPE,@GROUP
		
		WHILE @@FETCH_STATUS = 0  
		BEGIN  
			PRINT 'FETCH DATA [ '+@QID+' ][ '+@TYPE+' ][ '+@GROUP+' ]';
			SET @SV = dbo.getReadyServer(@GROUP , @TYPE);
			PRINT 'GET SERVER [ '+@SV+' ]';
			IF @SV <> ''
			BEGIN
				PRINT 'IS UPDATE QUQUE [ '+@QID+' ][ '+@SV+' ]';
        BEGIN TRANSACTION;
				  UPDATE CSC_QUQUE SET PROCESS_STATUS = 'T',SERVER_NAME = @SV WHERE QUQUEID = @QID;
				COMMIT TRANSACTION;
        
        BEGIN TRANSACTION;
				  UPDATE CSC_SERVER SET LAST_ASSIGN = CONVERT(VARCHAR , GETDATE() , 120) WHERE SERVER_NAME = @SV;
				COMMIT TRANSACTION;
			END
			FETCH NEXT FROM db_cursor_queue INTO @QID,@TYPE,@GROUP
		END  
		CLOSE db_cursor_queue  
		DEALLOCATE db_cursor_queue 
	END
	EXECUTE sp_releaseapplock @Resource = 'Serializable_Report',@LockOwner = 'Session',@DbPrincipal = 'dbo';
	COMMIT TRANSACTION;
END
GO