
GO
PRINT N'Creating Schema [sBooking]...';


GO
CREATE SCHEMA [sBooking]
    --AUTHORIZATION [dbo];


GO
PRINT N'Creating Schema [sCommonService]...';


GO
CREATE SCHEMA [sCommonService]
    --AUTHORIZATION [DEVuser];
GO

	PRINT N'Creating Table [dbo].[distinct_codes]...';


GO
CREATE TABLE [dbo].[distinct_codes] (
    [Code] VARCHAR (50) NULL
);


GO
PRINT N'Creating Table [dbo].[temp_table]...';


GO
CREATE TABLE [dbo].[temp_table] (
    [EmployeeId] BIGINT       NULL,
    [Code]       VARCHAR (50) NULL,
    [CodeCount]  INT          NULL
);


GO

PRINT N'Creating Table [sTimetable].[tTempDivision]...';


GO
CREATE TABLE [sTimetable].[tTempDivision] (
    [Id]              INT            NOT NULL,
    [Code]            NVARCHAR (20)  NOT NULL,
    [Title]           NVARCHAR (100) NOT NULL,
    [DisplayTitle]    NVARCHAR (100) NOT NULL,
    [ClassTeacherId]  BIGINT         NOT NULL,
    [OrgId]           SMALLINT       NULL,
    [OrderIndex]      SMALLINT       NOT NULL,
    [CreatedUserId]   BIGINT         NOT NULL,
    [CreatedDateTime] DATETIME       NOT NULL,
    [DivisionId]      INT            NULL,
    CONSTRAINT [PK_tTempDivision] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 85)
);


GO
PRINT N'Creating Table [sTimetable].[tTempSTimeTable]...';


GO
CREATE TABLE [sTimetable].[tTempSTimeTable] (
    [Id]                INT          NOT NULL,
    [TimeTableSchemaId] INT          NOT NULL,
    [EmployeeId]        BIGINT       NOT NULL,
    [DivisionId]        INT          NOT NULL,
    [SubjectId]         INT          NOT NULL,
    [DayId]             TINYINT      NOT NULL,
    [LectureNumber]     TINYINT      NOT NULL,
    [LockNo]            BIGINT       NULL,
    [LockColour]        VARCHAR (50) NULL,
    [SchoolRoomId]      INT          NULL,
    [IsBreak]           BIT          NULL,
    [IsMergeDivision]   BIT          NULL,
    [IsBreakBefore]     BIT          NULL,
    CONSTRAINT [PK_tTempSTimeTable] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 85)
);


GO
PRINT N'Creating Table [sTimetable].[tTempTimeSTableDetail]...';


GO
CREATE TABLE [sTimetable].[tTempTimeSTableDetail] (
    [TimetableId]  INT    NOT NULL,
    [EmployeeId]   BIGINT NOT NULL,
    [SubjectId]    INT    NOT NULL,
    [SchoolRoomId] INT    NULL
);


GO

PRINT N'Altering Procedure [sWf].[pWfApprovalAdd]...';


GO

--exec [sWf].[pWfApprovalAdd] 0,991100029, 95, 9911000000270,1,'',1,2,1,9911
ALTER PROCEDURE [sWf].[pWfApprovalAdd]
		@rId	BIGINT,
	 	@rDocSourceId BIGINT=0,
	 	@rDocSourceTypeId SMALLINT=0,
        @rActionEmployeeId BIGINT=0,
		@rActionStatusId SMALLINT=0,
		@rActionRemark NVARCHAR(500) = '',
		@rIsSequancial BIT=0,
		@rWfLevel INT=0,
		@rWfFunctionalityId SMALLINT=0,
		@rOrgGroupId SMALLINT

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
    BEGIN TRY
	BEGIN TRANSACTION
    DECLARE @lActionByEmployeeId BIGINT
	SET @lActionByEmployeeId = @rActionEmployeeId

	DECLARE @rUserId BIGINT
	SET @rUserId = @rActionEmployeeId

	IF EXISTS(SELECT SFM.Id FROM [sWf].[tWfFunctionalityMaster] SFM
		INNER JOIN [sWf].[tWfSetup] WFS ON WFS.WfFunctionalityId = SFM.Id
		WHERE WFS.WfTypeMasterId = 2 AND SFM.Id = @rWfFunctionalityId AND @rIsSequancial <> 1)
	BEGIN
		SELECT @rIsSequancial = CASE WHEN WfTypeMasterId = 2 THEN 1 ELSE 0 END,@rWfLevel = WFS.WfLevel FROM [sWf].[tWfFunctionalityMaster] SFM
		INNER JOIN [sWf].[tWfSetup] WFS ON WFS.WfFunctionalityId = SFM.Id
		WHERE WFS.WfTypeMasterId = 2 AND WfLevel > 0 AND SFM.Id = @rWfFunctionalityId
	END


		DECLARE @StatusId SMALLINT
		SET @StatusId = @rActionStatusId

		DECLARE @IsRejectedExists BIT = 0

		IF(@rIsSequancial = 1)
		BEGIN
			SELECT @rActionEmployeeId = ISNULL(U.SourceId,U.Id) FROM sSecurity.tUser U WHERE U.Id = @rActionEmployeeId

			IF(@rActionStatusId <> 4)
			BEGIN
				DECLARE @LogCount INT
				SELECT @LogCount = COUNT(AutoId) FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId = @rId

				IF(@rWfLevel <> @LogCount)
				BEGIN
					IF(@rActionStatusId = 3)
					BEGIN
						SET @StatusId = 2
					END
				END

				DECLARE @HierarchyLevelId BIGINT
				SELECT @HierarchyLevelId=ISNULL(AutoId,0) FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId =@rId AND ActionByEmployeeId=@rActionEmployeeId AND StatusId <> 4
				IF(@HierarchyLevelId > 0)
				BEGIN
					UPDATE [sWf].[tWfHierarchyLevel] SET StatusId = @StatusId , Remark = @rActionRemark
					WHERE AutoId = @HierarchyLevelId
				END

				SELECT @lActionByEmployeeId = EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = @rActionEmployeeId
						AND @rActionEmployeeId IS NOT NULL

			END
		END
		IF(((SELECT EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = (SELECT ISNULL(U.SourceId,U.Id) FROM sSecurity.tUser U WHERE U.Id = @rUserId )) IS NULL) AND (@rIsSequancial = 1) AND @StatusId <> 3 AND @StatusId <> 4)
		BEGIN
			SELECT -301;
		END
		ELSE
		BEGIN
			IF(@rId = 0)
			BEGIN

				DECLARE @lNextNumber INT
				EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sWf.tWfApprovalAction',@lNextNumber = @lNextNumber OUTPUT

				INSERT INTO sWf.tWfApprovalAction
				(
				Id,
				OrgGroupId,
				ActionEmployeeId,
				ActionStatusId,
				ActionRemark,
				DocSourceId,
				DocSourceTypeId,
				WfFunctionalityId,
				CreatedUserId,
				CreatedDateTime
				)
				VALUES
				(
					@lNextNumber,
					@rOrgGroupId,
					@rActionEmployeeId,
					@StatusId,
					@rActionRemark,
					@rDocSourceId,
					@rDocSourceTypeId,
					@rWfFunctionalityId,
					@rActionEmployeeId,
					GETUTCDATE()
				)
				SET @rId=@lNextNumber

				INSERT INTO sWf.tWfApprovalActionLog
				(
					WfApprovalActionId,
					ActionByEmployeeId,
					StatusId,
					IsSequancial,
					ActionRemark,
					CreatedUserId,
					CreatedDateTime
				)
				VALUES
				(
					@rId,
					@rActionEmployeeId,
					@rActionStatusId,
					@rIsSequancial,
					@rActionRemark,
					@rActionEmployeeId,
					GETUTCDATE()
				)

				IF(@rIsSequancial = 1 AND (@rWfLevel <> @LogCount OR @rWfLevel > 0))
				BEGIN																																																																																																																																																																				IF(@lActionByEmployeeId > 0)
					INSERT INTO [sWf].[tWfHierarchyLevel]
							(WfApprovalActionId
							,StatusId
							,Remark
							,ActionByEmployeeId
							,ActionDatetime)
						VALUES
							(@rId
							,@StatusId
							,''
							,@lActionByEmployeeId
							,sCommon.fGetDate())
				END
				SELECT @rId AS Result
			END
			ELSE
			BEGIN
			 
				IF EXISTS(SELECT AA.Id FROM sWf.tWfApprovalAction AA WHERE AA.ActionStatusId = 3 AND AA.Id = @rId AND AA.ActionEmployeeId <> @rActionEmployeeId)
				BEGIN
					SELECT -201
					SET @IsRejectedExists = 1
				END
				ELSE
				BEGIN
					IF EXISTS(SELECT ID FROM sWf.tWfApprovalAction WHERE id = @rId AND ActionStatusId <> 4)
					BEGIN
						UPDATE sWf.tWfApprovalAction
						SET
						DocSourceId=@rDocSourceId,
						DocSourceTypeId=@rDocSourceTypeId,
						ActionEmployeeId=@rActionEmployeeId,
						ActionStatusId=@StatusId,
						ActionRemark=@rActionRemark,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
						WHERE id = @rId

						INSERT INTO sWf.tWfApprovalActionLog
						(
							WfApprovalActionId,
							ActionByEmployeeId,
							StatusId,
							IsSequancial,
							ActionRemark,
							CreatedUserId,
							CreatedDateTime
						)
						VALUES
						(
							@rId,
							@rActionEmployeeId,
							@rActionStatusId,
							@rIsSequancial,
							@rActionRemark,
							@rActionEmployeeId,
							GETUTCDATE()
						)
						IF(@rIsSequancial = 1)
						BEGIN
							IF(@rWfLevel <> @LogCount AND @rActionStatusId <> 4)
							BEGIN																																										IF(@lActionByEmployeeId > 0)
								INSERT INTO [sWf].[tWfHierarchyLevel]
										(WfApprovalActionId
										,StatusId
										,Remark
										,ActionByEmployeeId
										,ActionDatetime)
									VALUES
										(@rId
										,1
										,''
										,@lActionByEmployeeId
										,sCommon.fGetDate())
							END
							ELSE
							BEGIN
									SELECT @lActionByEmployeeId = EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = @rActionEmployeeId
									DELETE FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId = @rId AND ActionByEmployeeId = @lActionByEmployeeId AND @lActionByEmployeeId IS NOT NULL
							END
						END
						SELECT @rId AS Result
					END
					ELSE
					BEGIN
						SELECT -101
						SET @IsRejectedExists = 1
					END
				END
			END
			IF EXISTS(SELECT ID FROM sWf.tWfApprovalAction WHERE id = @rId AND ActionStatusId IN(2,3,4) AND @IsRejectedExists = 0)
			BEGIN
				DECLARE @rStoreLocationId BIGINT
				SELECT @rStoreLocationId = ISNULL(ItemValue,0)
				FROM sConfig.tConfigurationSetup
				WHERE ConfigurationId  = 542 AND SourceId = @rUserId

				IF(@rDocSourceTypeId = 2040)
				BEGIN
					UPDATE sPurchase.tPurchaseRequest
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2041)
				BEGIN
					UPDATE sPurchase.tQuotationRequest
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2042)
				BEGIN
					UPDATE sPurchase.tPurchaseOrder
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2043)
				BEGIN
					UPDATE sPurchase.tGoodsReceivedNote
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2044)
				BEGIN
					UPDATE sPurchase.tSalesReturn
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
			END
			
		END
	COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION


    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	END CATCH
END
GO
PRINT N'Altering Procedure [sWf].[pWfSetupAdd]...';


GO



ALTER PROCEDURE [sWf].[pWfSetupAdd]
		@rId SMALLINT,
		@rWfTypeMasterId SMALLINT,
		@rWfLevel SMALLINT,
		@rOrgGroupId SMALLINT,
		@rWfFunctionalityId SMALLINT,
        @rAuthorityList sPurchase.ApprovingAuthorityType READONLY
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
		BEGIN
			   

			IF(@rId = 0)
			BEGIN
				IF EXISTS(SELECT WFS.WfFunctionalityId
				FROM [sWf].[tWfSetup] WFS(NOLOCK)
				WHERE WFS.[WfFunctionalityId] = @rWfFunctionalityId AND WFS.OrgGroupId = @rOrgGroupId AND WFS.WfTypeMasterId = @rWfTypeMasterId AND WFS.IsObsolete = 0)
				BEGIN
					SELECT -102
				END
				ELSE
				BEGIN
					IF NOT EXISTS(SELECT WFM.Id
					from [sWf].[tWfFunctionalityMaster] WFM 
					WHERE WFM.Id = @rWfFunctionalityId AND WFM.IsObsolete = 0)
					BEGIN
						SELECT -103
					END
					ELSE
					BEGIN
						INSERT INTO [sWf].[tWfSetup]
						(
						WfTypeMasterId,
						WfLevel,
						OrgGroupId,
						WfFunctionalityId
						)
						VALUES
						(
						@rWfTypeMasterId,
						@rWfLevel,
						@rOrgGroupId,
						@rWfFunctionalityId
						)
						SET @rId = CAST(SCOPE_IDENTITY() AS SMALLINT)

						INSERT INTO sWf.tWfApprovingAuthority
						(
						AuthoritySourceTypeId,
						AuthoritySourceId,
						OrgId,
						WfFunctionalityId
						)
						SELECT 
						AuthoritySourceTypeId,
						AuthoritySourceId,
						OrgId,
						WfFunctionalityId
						FROM @rAuthorityList

						SELECT @rId
					END
				END
			END
			ELSE
			BEGIN
				IF NOT EXISTS(SELECT AA.Id FROM [sWf].[tWfSetup] WS
							INNER JOIN [sWf].[tWfApprovalAction] AA ON WS.WfFunctionalityId = AA.WfFunctionalityId
							WHERE WS.WfFunctionalityId= @rWfFunctionalityId AND WS.OrgGroupId = @rOrgGroupId AND 
							WS.IsObsolete = 0 AND 
							(WS.WfTypeMasterId <> @rWfTypeMasterId OR (WS.WfLevel <> 0 AND WS.WfTypeMasterId = 2)) AND AA.ActionStatusId NOT IN (3,4))
				BEGIN
					UPDATE [sWf].[tWfSetup]
					SET 
						WfTypeMasterId = @rWfTypeMasterId,
						WfLevel = @rWfLevel,
						OrgGroupId = @rOrgGroupId,
						WfFunctionalityId = @rWfFunctionalityId
					WHERE AutoId =@rId AND OrgGroupId = @rOrgGroupId AND IsObsolete = 0

					DELETE FROM sWf.tWfApprovingAuthority
					WHERE WfFunctionalityId = @rWfFunctionalityId

					INSERT INTO sWf.tWfApprovingAuthority
					(
					AuthoritySourceTypeId,
					AuthoritySourceId,
					OrgId,
					WfFunctionalityId
					)
					SELECT 
					AuthoritySourceTypeId,
					AuthoritySourceId,
					OrgId,
					WfFunctionalityId
					FROM @rAuthorityList

					SELECT @rId
				END
				ELSE
				BEGIN
					SELECT -101;
				END
			END
		END
	COMMIT TRANSACTION
	END TRY	
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT -1 AS RESULT

		DECLARE @ErrorMessage NVARCHAR(4000);  
		DECLARE @ErrorSeverity INT;  
		DECLARE @ErrorState INT;  

		SELECT   
		   @ErrorMessage = ERROR_MESSAGE(),  
		   @ErrorSeverity = ERROR_SEVERITY(),  
		   @ErrorState = ERROR_STATE();  

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH
END
GO
PRINT N'Creating Procedure [sCommonService].[pMenuGet]...';


GO
-- exec sCommonService.pMenuGet 9911000000270,19184029,0,1,98
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID('[sCommonService].[pMenuGet]'))
BEGIN
    DROP PROCEDURE [sCommonService].[pMenuGet]
END

GO
CREATE PROCEDURE [sCommonService].[pMenuGet]
 @rUserId [bigint],    
 @rMenuId int = 0,    
 @rResourceTypeId int= 0,    
 @rIsCoreMenu bit = 0,
 @rModuleId int = 0   
      
WITH EXECUTE AS CALLER    
AS    
SET NOCOUNT ON    
    
BEGIN    
 IF(@rIsCoreMenu=0)    
 BEGIN    
 IF EXISTS(SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId = @rUserId)    
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
    PageURL  AS PagePath,    
    IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermissionUser P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
       WHERE    
        T.IsObsolete = 0 AND P.UserId = @rUserId    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 ELSE     
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
     PageURL  AS PagePath,    
     IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermission P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
        INNER JOIN    
        (    
        SELECT RoleId FROM sSecurity.tUserRole UR WHERE  UserId = @rUserId AND IsObsolete = 0    
        ) B ON B.RoleId = P.RoleId    
       WHERE    
        T.IsObsolete = 0    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0 AND TBL2.PermissionValue > 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 END    
 ELSE    
 BEGIN     
 SELECT    
   MenuID,    
   ModuleId,    
   ParentMenuID,    
   MenuTitle,      
   ScreenTitle,       
   PageURL,    
   ScreenId,    
   Permission,    
   OrderNumber,      
   TaskId,      
   PageURL  AS PagePath,    
   MenuIsObsolete,    
   Icon     
  FROM    
  (    

SELECT    
   ISNULL(Menu.Id,0) AS MenuID,     
   ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
   ISNULL(Menu.Title,'') AS MenuTitle,       
   ISNULL(Screen.Title, '') AS ScreenTitle,        
   ISNULL(Screen.Page, '') AS PageURL,    
   ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen.IconPath,'') AS Icon,
   ISNULL(Screen.OrderIndex,0) As OrderIndex     
  FROM     
  sSecurity.tMenu Menu     
  LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
  LEFT OUTER JOIN     
  (    
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId  AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId      
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id AND  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id        
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId     
  GROUP BY P.TaskId    
  ) TBL2 ON Screen.TaskId = TBL2.TaskId     
  LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
  WHERE 
  --(Menu.IsObsolete = 0)    
  --AND 
  ISNULL(Screen.IsObsolete, 0) = 0    
  AND (Menu.Id  IN (@rMenuId))   
  --OR ParentMenuID  in (24,2711))     
  --AND (
  ----Menu.Id IN (19184027)  
  ----OR ParentMenuID IN (55)
  --)  

  UNION
  

  SELECT     
  
   ISNULL(Menu.Id, 0) AS MenuID,     
   ISNULL(Menu.Id, 0) AS ParentMenuID,     
   ISNULL(Screen1.Title,'') AS MenuTitle,       
   ISNULL(Screen1.Title, '') AS ScreenTitle,        
   ISNULL(Screen1.Page, '')  AS PageURL,    
   ISNULL(Screen1.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen1.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen1.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen1.IconPath,'') AS Icon,
   ISNULL(Screen1.OrderIndex,0) As OrderIndex    
  FROM     
   sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
   LEFT OUTER JOIN sSecurity.tScreen Screen1 ON Screen1.ScreenGroupId = Screen.ScreenGroupId     
   LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen1.TaskId    
   LEFT OUTER JOIN (     
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId   AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId        
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id       
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId    
            
  GROUP BY P.TaskId    
  ) TBL2 ON Screen1.TaskId = TBL2.TaskId    
  WHERE 
  --(Menu.IsObsolete = 0) AND 
  ISNULL(Screen1.IsObsolete, 0) = 0    
  --AND (Menu.ParentMenuID  in (24,2711)   
  --OR Menu.Id  in (24,2711))   
  AND TBL2.PermissionValue > 0
  --AND Screen1.Id IN (31336,31337,31338,31339,31340)
  ) MenuTable      
  WHERE     
  --(MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2)
  --AND   
  ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
  AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
  --AND MenuIsObsolete = 0    
  AND MenuTable.MenuId IN (@rMenuId)   
  AND ModuleId IN (@rModuleId)
  ORDER BY MenuTable.OrderIndex, ParentMenuID
 END    
END
GO
PRINT N'Creating Procedure [sCommonService].[GetStudentProfile]...';


GO
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID('[sCommonService].[GetStudentProfile]'))
BEGIN
    DROP PROCEDURE [sCommonService].[GetStudentProfile]
END

GO
CREATE PROCEDURE [sCommonService].[GetStudentProfile] 
	@racademicId bigint,
	@rbatchId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	SELECT A.OrgId, BatchId, StudentId, A.Id AS AcademicId, SM.Code, A.RollNo, (FirstName +' '+MiddleName+' '+LastName) AS StudentName,
                            SM.FirstName, SM.MiddleName, SM.LastName, A.ClassId, A.DivisionId, (CM.DisplayTitle +'-'+ DM.DisplayTitle) AS ClassDivision,
                            DPM.Id AS DepartmentId, DPM.DisplayTitle AS DepartmentName, SM.SUID, B.Title as BatchName
                             FROM sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
                            INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
                            INNER JOIN sSetup.tDepartmentMaster DPM ON DPM.Id = CM.DepartmentId
                            INNER JOIN sSetup.tBatchMaster B ON b.Id = A.BatchId
							WHERE A.Id=@racademicId AND BatchId = @rbatchId



END
GO
PRINT N'Creating Procedure [sCommonService].[pPermissionGet]...';


GO
-- exec sCommonService.pPermissionGet 9911,9911000000270
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID('[sCommonService].[pPermissionGet]'))
BEGIN
    DROP PROCEDURE [sCommonService].[pPermissionGet]
END

GO
CREATE PROCEDURE [sCommonService].[pPermissionGet]
	@rOrgGroupId [int],
	@rUserId [bigint],
	@rModuleId [int]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	
IF EXISTS (SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId=@rUserId AND OrgGroupId=@rOrgGroupId)
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT 	
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermissionUser P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId 
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		WHERE
		P.OrgGroupId = @rOrgGroupId AND P.UserId = @rUserId  AND MP.ModuleId = @rModuleId
	) TP 
	GROUP BY TP.TaskId
END
ELSE
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT 	
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermission P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId 
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId) R
		ON R.RoleId = P.RoleId 
		WHERE
		P.OrgGroupId = @rOrgGroupId AND MP.ModuleId = @rModuleId
		--AND
	--RoleId 
	
	--IN
	-- (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId )
	) TP 
	GROUP BY TP.TaskId
	
	--SELECT	CAST(MAX(TP.C) AS BIT) AS [Create],
	--		CAST(MAX(TP.R) AS BIT) AS [Read],
	--		CAST(MAX(TP.U) AS BIT) AS [Update],
	--		CAST(MAX(TP.D) AS BIT) AS [Delete],
	--		CAST(MAX(TP.S) AS BIT) AS [Search],
	--		CAST(MAX(TP.P) AS BIT) AS [Print],
	--		CAST(MAX(TP.X) AS BIT) AS [Execute]
	--FROM
	--(
	--	SELECT 	(Permission & 1) AS C,
	--			(Permission & 2)/2 AS R,
	--			(Permission & 4)/4 AS U,
	--			(Permission & 8)/8 AS D,
	--			(Permission & 16)/16 AS S,
	--			(Permission & 32)/32 AS P,
	--			(Permission & 64)/64 AS X
	--	FROM sSecurity.tPermission
	--	WHERE TaskId = @rTaskId AND 
	--		  RoleId IN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId)
	--) TP
END


END
GO
PRINT N'Creating Procedure [sCommonService].[pScreenGet]...';


GO
-- exec sCommonService.pScreenGet 245
IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = OBJECT_ID('[sCommonService].[pScreenGet]'))
BEGIN
    DROP PROCEDURE [sCommonService].[pScreenGet]
END

GO
CREATE PROCEDURE [sCommonService].[pScreenGet] 
	@rModuleId [int]
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin
	BEGIN TRY
		SELECT	
			S.Id			AS ScreenId,
			S.Title		AS ScreenTitle,
			S.TaskId		AS TaskId,	
			S.Page		As PageUrl,
			S.IsObsolete	AS ScreenIsObsolete,	
			S.Title,		
			S.Page,
			SG.Id AS ScreenGroupId,
			ISNULL(SG.Title,'') AS ScreenGroupName
		
		FROM 
			sSecurity.tScreen S (nolock)
		LEFT JOIN sSecurity.tScreenGroupMaster SG ON SG.Id=S.ScreenGroupId
		INNER JOIN sSecurity.tTask ST ON S.TaskId = ST.Id
		WHERE 
			S.IsObsolete = 0 and ST.ModuleId = @rModuleId and S.Id NOT IN (30039,30047,30075,30076,30077,30078,30097)
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
end
GO

PRINT N'Creating Procedure [sTimetable].[pWebAddFirstLectureByDivision]...';


GO

CREATE PROCEDURE  [sTimetable].[pWebAddFirstLectureByDivision]
    @rOrgId int,
    @rTimeTableSchemaId bigint,
    @rDivisionId bigint,
    @rSubjectId int, 
    @rTeacherId bigint,
    @rDayId int,
 @rLectureNumber int = 1,
@rLockNo int = 0,
@rLockColour nvarchar ='',
@rSchoolRoomId int = 0,
@rIsBreak bit = 0,
@rIsMergeDivision bit = 0,
@rIsBreakBefore bit = 0
AS
BEGIN 
    DECLARE @lNextNumber bigint; -- You need to specify the data type for @lNextNumber

    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE TimeTableSchemaId = @rTimeTableSchemaId AND DivisionId = @rDivisionId AND LectureNumber = @rLectureNumber)
    BEGIN
        UPDATE sTimetable.tTempSTimeTable 
        SET EmployeeId = @rTeacherId, SubjectId = @rSubjectId 
        WHERE TimeTableSchemaId = @rTimeTableSchemaId AND DivisionId = @rDivisionId AND LectureNumber = @rLectureNumber;
		select 1

    END
    ELSE
    BEGIN 
		Declare @ldaycount int;

		Select @ldaycount = count(TS.dayId) from sTimetable.tTimesetup TS 
		inner join sTimetable.tAssignOrg AO on ts.timetableschemaid=ao.timetableschemaid  
		where TS.TimetableschemaId=@rTimeTableSchemaId and AO.orgid=@rOrgId

  DECLARE @Counter INT = 1;
   print @ldaycount
	WHILE @Counter <= @ldaycount
	BEGIN
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
	print  @Counter
    INSERT INTO sTimetable.tTempSTimeTable (
        Id, TimeTableSchemaId, EmployeeId, DivisionId, SubjectId, 
        DayId, LectureNumber, LockNo, LockColour, SchoolRoomId, 
        IsBreak, IsMergeDivision, IsBreakBefore
    )
    VALUES (
        @lNextNumber, 
        @rTimeTableSchemaId, @rTeacherId, @rDivisionId, @rSubjectId, 
        @Counter, @rLectureNumber, @rLockNo, @rLockColour, 
        @rSchoolRoomId, @rIsBreak, @rIsMergeDivision, @rIsBreakBefore
    );
    SET @Counter = @Counter + 1;
	print @Counter
	END;
	END
	Select 1
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebAssignSameEmployeeDiffernetDivision]...';


GO
CREATE PROCEDURE [sTimetable].[pWebAssignSameEmployeeDiffernetDivision]
	@rSchemaId bigint,
	@rDivisionId bigint,
	@rOrgId bigint,
	@rEmployeeId bigint,
	@rSubjectId bigint,
	@rSchoolroomId bigint,
	@rDayId bigint,
	@rLectureNumber int

AS
BEGIN

		DECLARE @lNextNumber BIGINT;
        -- Generate a new ID using sCommon.pTableRowIdGet
        EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @lNextNumber OUTPUT;
		select @lNextNumber
        ---- Insert into sTimetable.tTimeTable
        INSERT INTO sTimetable.tTempSTimeTable
        (
            Id,
            TimeTableSchemaId,
            EmployeeId,
            DivisionId,
            SubjectId,
            DayId,
            LectureNumber,
            LockNo,
            LockColour,
            SchoolRoomId,
            IsBreak,
            IsMergeDivision,
            IsBreakBefore
        )
        SELECT 
            @lNextNumber,
            @rSchemaId,
            @rEmployeeId,
            @rDivisionId,
            @rSubjectId,
            @rDayId,
            @rLectureNumber,
            0,
            '',
            @rSchoolRoomId,
            0,
            0,
            0;

END
GO
PRINT N'Creating Procedure [sTimetable].[pWebAssignSameLectureToTeacherInSameDivision]...';


GO
CREATE PROCEDURE [sTimetable].[pWebAssignSameLectureToTeacherInSameDivision]
    @rSchemaId INT,
    @rDivisionId INT,
    @rOrgGroupId INT,
    @rEmployeeId bigint,
    @rSubjectId INT,
    @rSchoolRoomId INT,
    @rDayId INT,
    @rLectureNumber INT
AS
BEGIN
    BEGIN TRY
    DECLARE @lNextNumber BIGINT;
	DECLARE @rTeachersworkloadinthatdiv INT;
	DECLARE @rSubjectWorkloadinthatdiv INT;

	SELECT 
	@rTeachersworkloadinthatdiv = TW.NoOfLecture - 
	(SELECT COUNT(LectureNumber) 
	FROM sTimetable.tTempSTimeTable 
	WHERE DivisionId = @rDivisionId AND TimeTableSchemaId = @rSchemaId AND EmployeeId = @rEmployeeId
	)
	FROM sTimetable.tTeacherWorkLoad TW;

	SELECT 
	@rSubjectWorkloadinthatdiv = DW.NoOfLecture - 
	(SELECT COUNT(LectureNumber) 
	FROM sTimetable.tTempSTimeTable 
	WHERE DivisionId = @rDivisionId AND TimeTableSchemaId = @rSchemaId AND SubjectId = @rSubjectId
	)
	FROM sTimetable.tDivisionWorkLoad DW WHERE DW.DivisionId=@rDivisionId;

        -- First Condition
        IF exists(select 1 from sTimetable.tTempSTimeTable where EmployeeId=@rEmployeeId and LectureNumber=@rLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            Select -2 --Teacher Is Busy
        END
        ELSE IF exists(select 1 from sTimetable.tTeacherTimeOff where EmployeeId=@rEmployeeId and LectureNumber=@rLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -3; -- Teacher isn't available (Timeoff)
        END
        ELSE IF exists(select 1 from sTimetable.tSubjectTimeOff where SubjectId=@rSubjectId and LectureNumber=@rLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -4; --Subject is at TimeOff
        END
		Else IF NOT EXISTS (SELECT 1 FROM sTimetable.tTeacherWorkLoad WHERE EmployeeId = @rEmployeeId AND DivisionId = @rDivisionId)
		BEGIN
		SELECT -7; -- Teacher does not exist in that division
		END
		ELSE IF @rTeachersworkloadinthatdiv <= 0
		BEGIN
		SELECT -5; -- No more teacher workload available in that division
		END
		ELSE IF NOT EXISTS (SELECT 1 FROM sTimetable.tDivisionWorkLoad WHERE SubjectId = @rSubjectId AND DivisionId = @rDivisionId)
		BEGIN
		SELECT -8; -- Subject does not exist in that division
		END
		ELSE IF @rSubjectWorkloadinthatdiv <= 0
		BEGIN
		SELECT -6; -- No more subject workload available in that division
		END
        ELSE
        BEGIN
           -- Generate a new ID using sCommon.pTableRowIdGet
            EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @lNextNumber OUTPUT;
            ---- Insert into sTimetable.tTempSTimeTable
            INSERT INTO sTimetable.tTempSTimeTable
            (
                Id,
                TimeTableSchemaId,
                EmployeeId,
                DivisionId,
                SubjectId,
                DayId,
                LectureNumber,
                LockNo,
                LockColour,
                SchoolRoomId,
                IsBreak,
                IsMergeDivision,
                IsBreakBefore
            )
            SELECT 
                @lNextNumber,
                @rSchemaId,
                @rEmployeeId,
                @rDivisionId,
                @rSubjectId,
                @rDayId,
                @rLectureNumber,
                2,
                '',
                @rSchoolRoomId,
                0,
                0,
                0;

            -- Insert into sTimetable.tTempSTimeTableDetail
            INSERT INTO sTimetable.tTempTimeSTableDetail
            (
                TimetableId,
                EmployeeId,
                SubjectId,
                SchoolRoomId
            )
            VALUES (
                @lNextNumber,
                @rEmployeeId,
                @rSubjectId,
                @rSchoolRoomId
            );

            SELECT 1; -- Indicate success
        END
    END TRY
    BEGIN CATCH
        ROLLBACK;
        SELECT -1; -- Indicate failure
    END CATCH
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebBlockperiod]...';


GO
CREATE PROCEDURE [sTimetable].[pWebBlockperiod]
	@rOrgId int,
    @rSchemaId int,
    @rDivisionId int,
    @rDayId int,
    @rLectureNo int
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION; 

          update sTimeTable.tTempSTimeTable set EmployeeId = -1 ,  SubjectId = -1  FROM sTimeTable.tTempSTimeTable TTT
		  inner join sTimeTable.tAssignOrg AO on AO.TimeTableSchemaId = TTT.TimeTableSchemaId  where
		  TTT.TimeTableSchemaId = @rSchemaId and TTT.DivisionId=@rDivisionId and 
		  TTT.DayId = @rDayId and TTT.LectureNumber =@rLectureNo and AO.OrgId = @rOrgId
        IF @@ROWCOUNT > 0
        BEGIN
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION; 
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION; 
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebCheckavailibility]...';


GO
CREATE PROCEDURE [sTimetable].[pWebCheckavailibility]
    @rSchemaId bigint,
    @rOrgId bigint,
    @rEmployeeId bigint,
    @rLectureNumber bigint,
    @rDayId int
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM sTimetable.tTempSTimeTable TST
        INNER JOIN sTimetable.tAssignOrg AO ON TST.TimeTableSchemaId = AO.TimeTableSchemaId
        WHERE TST.TimeTableSchemaId = @rSchemaId
        AND TST.LectureNumber = @rLectureNumber
        AND TST.EmployeeId = @rEmployeeId
        AND TST.DayId = @rDayId
        AND AO.OrgId = @rOrgId
    )
    BEGIN
        -- The slot is not available, you can add further actions here if needed
        SELECT 404 AS Result;
    END
    ELSE
    BEGIN
        -- The slot is available, you can add further actions here if needed
        SELECT 1 AS Result;
    END
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebDeleteTimeSheet]...';


GO
 CREATE PROCEDURE [sTimetable].[pWebDeleteTimeSheet]
@rSchemaId int
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRY
	BEGIN TRANSACTION
	
	--DELETE from [sTimetable].[tTimeTableDetail] where  TimetableId in (select Id from [sTimetable].[tTimeTable] where TimeTableSchemaId = @rSchemaId)
DELETE TTD
FROM [sTimetable].[tTempTimeSTableDetail] AS TTD
inner join sTimetable.tTempSTimeTable As TTT On TTT.id = TTD.TimeTableId
WHERE TimetableId IN (
        SELECT Id
        FROM [sTimetable].[tTempSTimeTable]
        WHERE TimeTableSchemaId = @rSchemaId
        )

	--Delete  from [sTimetable].[tTimeTable] Where TimeTableSchemaId =@rSchemaId 
	Delete TTT from [sTimetable].[tTempSTimeTable] AS TTT 
	INNER JOIN sTimetable.tTimeTableSchema AS TTS ON TTS.Id = TTT.TimeTableSchemaId
INNER JOIN sSetup.tOrgGroupBatchMaster AS OGBM ON OGBM.Id = TTS.OrgGroupBatchId Where TimeTableSchemaId =@rSchemaId
	
	select 1 as Result
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebDragAndDrop]...';


GO
CREATE PROCEDURE [sTimetable].[pWebDragAndDrop]
	@rOrgGroupId int,
	@rOrgId int,
	@rSchemaId int,
	@rDivisionId int,
	@rDayId int,
	@rLectureNo int,
	@rTeacherId int,
	@rSubjectId int,
	@rLockNo int = 0,
	@rLockColour nvarchar ='',
	@rSchoolRoomId int = 0,
	@rIsBreak bit = 0,
	@rIsMergeDivision bit = 0,
	@rIsBreakBefore bit = 0
AS
BEGIN
		 BEGIN TRY
        BEGIN TRANSACTION; 
  DECLARE @lNextNumber bigint; -- You need to specify the data type for @lNextNumber
    EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
	SET NOCOUNT ON;

	if exists(SELECT Id 
    FROM sTimeTable.tTempSTimeTable TTT
    INNER JOIN sTimeTable.tAssignOrg AO ON AO.TimeTableSchemaId = TTT.TimeTableSchemaId 
    WHERE TTT.TimeTableSchemaId = @rSchemaId 
        AND TTT.DivisionId = @rDivisionId 
        AND TTT.DayId = @rDayId 
        AND TTT.LectureNumber = @rLectureNo 
        AND AO.OrgId = @rOrgId )
		  begin 
		   
			update sTimeTable.tTempSTimeTable set SubjectId= @rSubjectId, EmployeeId =@rTeacherId 
			FROM sTimeTable.tTempSTimeTable TTT
			INNER JOIN sTimeTable.tAssignOrg AO ON AO.TimeTableSchemaId = TTT.TimeTableSchemaId 
			inner join sTimetable.tTimeTableSchema TTS on TTS.Id = TTT.TimeTableSchemaId 
			inner join sSetup.tOrgGroupBatchMaster OGBM on OGBM.Id = TTS.OrgGroupBatchId 
			WHERE TTT.TimeTableSchemaId = @rSchemaId 
			AND TTT.DivisionId = @rDivisionId 
			AND TTT.DayId = @rDayId 
			AND TTT.LectureNumber = @rLectureNo 
			AND AO.OrgId = @rOrgId 
			AND OGBM.OrgGroupId = @rOrgGroupId
			AND (TTT.SubjectId <> -1 and TTT.EmployeeId <>-1 ) And (TTT.LockNo = 0)
		  end
		else
		begin
		insert into sTimeTable.tTempSTimeTable (Id, TimeTableSchemaId, EmployeeId, DivisionId, SubjectId, DayId, LectureNumber, LockNo, LockColour, SchoolRoomId, IsBreak, IsMergeDivision, IsBreakBefore)
        VALUES (@lNextNumber, @rSchemaId, @rTeacherId, @rDivisionId, @rSubjectId, @rDayId, @rLectureNo, @rLockNo, @rLockColour, @rSchoolRoomId, @rIsBreak, @rIsMergeDivision, @rIsBreakBefore);
		end
IF @@ROWCOUNT > 0
        BEGIN
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebErasePeriod]...';


GO
CREATE PROCEDURE [sTimetable].[pWebErasePeriod]
    @rOrgGroupId INT,
    @rTimeTableSchemaId BIGINT,
    @rDivisionId BIGINT,
    @rSubjectId INT, 
    @rTeacherId BIGINT,
    @rDayId INT,
    @rLectureNumber INT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Result INT;

    IF EXISTS (
        SELECT 1
        FROM sTimeTable.tTempSTimeTable TT 
        INNER JOIN sTimeTable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
        WHERE TT.TimeTableSchemaId = @rTimeTableSchemaId 
        AND TT.DayId = @rDayId 
        AND TT.DivisionId = @rDivisionId 
        AND TT.EmployeeId = @rTeacherId 
        AND TT.SubjectId = @rSubjectId 
        AND TT.LectureNumber = @rLectureNumber 
        AND AO.OrgId = @rOrgGroupId
    )
    BEGIN
        DELETE TT 
        FROM sTimeTable.tTempSTimeTable TT 
        INNER JOIN sTimeTable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
        WHERE TT.TimeTableSchemaId = @rTimeTableSchemaId 
        AND TT.DayId = @rDayId 
        AND TT.DivisionId = @rDivisionId 
        AND TT.EmployeeId = @rTeacherId 
        AND TT.SubjectId = @rSubjectId 
        AND TT.LectureNumber = @rLectureNumber 
        AND AO.OrgId = @rOrgGroupId;

        SET @Result = 1;
    END
    ELSE
    BEGIN
        SET @Result = -101;
    END

    SELECT @Result AS Result;
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebExchangeLecutres]...';


GO
CREATE PROCEDURE [sTimetable].[pWebExchangeLecutres]
   @rFirstLectureTableIndex int,
   @rSecondLectureTableIndex int,
   @rFirstLectureLectureNumber int,
   @rSecondLectureLectureNumber int,
   @rFirstEmployeeId bigint,
   @rSecondEmployeeId bigint,
   @rFirstSubjectId int,
   @rSecondSubjectId int,
   @rDivisionId int,
   @rSchemaId int,
   @rDayId int
AS
BEGIN

		IF exists(select 1 from sTimetable.tTempSTimeTable where EmployeeId=@rFirstEmployeeId and LectureNumber=@rSecondLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            Select -2 --Teacher First Is Busy
        END
		Else IF exists(select 1 from sTimetable.tTempSTimeTable where EmployeeId=@rSecondEmployeeId and LectureNumber=@rFirstLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            Select -3 --Teacher Second Is Busy
        END
		ELSE IF exists(select 1 from sTimetable.tTeacherTimeOff where EmployeeId=@rFirstEmployeeId and LectureNumber=@rSecondLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -4; -- Teacher First isn't available (Timeoff)
        END
		ELSE IF exists(select 1 from sTimetable.tTeacherTimeOff where EmployeeId=@rSecondEmployeeId and LectureNumber=@rFirstLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -5; -- Teacher Second isn't available (Timeoff)
        END
		ELSE IF exists(select 1 from sTimetable.tSubjectTimeOff where SubjectId=@rFirstSubjectId and LectureNumber=@rSecondLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -6; --Subject First is at TimeOff
        END
		ELSE IF exists(select 1 from sTimetable.tSubjectTimeOff where SubjectId=@rSecondSubjectId and LectureNumber=@rFirstLectureLectureNumber and TimeTableSchemaId=@rSchemaId and DayId=@rDayId)
        BEGIN
            SELECT -7; --Subject Second is at TimeOff
        END
		ELSE
		BEGIN
			BEGIN TRY
				UPDATE sTimetable.tTempSTimeTable
				SET LectureNumber = @rSecondLectureLectureNumber
				WHERE divisionid = @rDivisionId AND TimetableSchemaId=@rSchemaId AND Id = @rFirstLectureTableIndex;

				UPDATE sTimetable.tTempSTimeTable
				SET LectureNumber = @rFirstLectureLectureNumber
				WHERE divisionid = @rDivisionId AND TimetableSchemaId=@rSchemaId AND Id = @rSecondLectureTableIndex;
				SELECT 1;
			END TRY
			BEGIN CATCH
				ROLLBACK;
				SELECT -1;
			END CATCH
		END
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebGenerateTimeTable]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGenerateTimeTable]
@rOrgGroupId int,
@rOrgId int,
@rSchemaId int
AS
BEGIN

  DECLARE @lNextNumber bigint; 
    EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
Declare  @Temp_TimeTable_AlterMainTable table
(
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    TimeTableSchemaId int,
    DayID tinyint,
    NoOfPeriod tinyint,
    OrgId int,
    DisplayTitle nvarchar(500),
    DivisionTitile nvarchar(500),
    DivisionId int
)

-- Populate the main temporary table
INSERT INTO @Temp_TimeTable_AlterMainTable
SELECT 
    TS.TimeTableSchemaId AS TimeTableSchemaId,
    TS.DayID AS DayID,
    TS.NoOfPeriod AS NoOfPeriod,
    AO.OrgId AS OrgId,
    CM.DisplayTitle AS DisplayTitle,
    D.DisplayTitle AS DivisionTitile,
    D.id AS DivisionId
FROM 
    sTimeTable.tTimesetup TS 
INNER JOIN sTimetable.tTimeTableSchema TSH ON TSH.Id = TS.TimeTableSchemaId
INNER JOIN sTimetable.tAssignOrg AO ON TSH.Id = AO.TimeTableSchemaId
INNER JOIN sSetup.tClassMaster CM ON CM.Id = TS.ClassId
INNER JOIN sSetup.tDivisionMaster D ON D.ClassId = CM.Id
WHERE 
    TS.TimeTableSchemaId = @rSchemaId and  (AO.OrgId = @rOrgId Or (@rOrgId = 0))
GROUP BY 
    TS.TimeTableSchemaId, AO.OrgId, D.Id, CM.DisplayTitle, D.DisplayTitle, TS.DayID, TS.NoOfPeriod
ORDER BY 
    D.id

-- Create the secondary temporary table
Declare @Temp_TimeTable_AlterSecondaryTempTable table(
    RowNum int,
    TimeTableSchemaId int,
    EmployeeId int,
    DivisionId int,
    SubjectId int,
    DayID int,
    LectureNumber int,
    LockNo int,
    LockColour int,
    SchoolRoom int,
    Isbreack int,
    IsMergeDivision int,
	IsBreakBefore int 
)

-- Insert data into the secondary temporary table
INSERT INTO @Temp_TimeTable_AlterSecondaryTempTable
SELECT 
    ROW_NUMBER() OVER (ORDER BY  o.OrgId,o.DivisionId, o.DayID) AS RowNum,
    TimeTableSchemaId,
    0 AS EmployeeId,
    DivisionId,
    0 AS SubjectId,
    DayID,
    ROW_NUMBER() OVER (PARTITION BY  o.OrgId,o.DivisionId, o.DayID ORDER BY  OrgId,DivisionId) AS LectureNumber,
    0 AS LockNo,
    0 AS LockColour,
    0 AS SchoolRoom,
    0 AS Isbreack,
    0 AS IsMergeDivision,
	0 as IsBreakBefore
FROM 
    @Temp_TimeTable_AlterMainTable o
CROSS APPLY (
    SELECT TOP(NoOfPeriod) NoOfPeriod
    FROM @Temp_TimeTable_AlterMainTable
) od

print @lNextNumber
-- Insert data from #Temp_TimeTable_AlterSecondaryTempTable into sTimetable.tTempSTimeTable
INSERT INTO sTimetable.tTempSTimeTable (
    ID,
    TimeTableSchemaId,
    EmployeeId,
    DivisionId,
    SubjectId,
    DayID,
    LectureNumber,
    LockNo,
    LockColour,
    SchoolRoomId,
    IsBreak,
    IsMergeDivision,
    IsBreakBefore
)
SELECT 
(@lNextNumber +RowNum ) as Id,
	tst.TimeTableSchemaId,
    tst.EmployeeId,
    tst.DivisionId,
    tst.SubjectId,
    tst.DayID,
    tst.LectureNumber,
    tst.LockNo,
    tst.LockColour,
    tst.SchoolRoom,
    tst.Isbreack,
    tst.IsMergeDivision,
    tst.IsBreakBefore
FROM 
    @Temp_TimeTable_AlterSecondaryTempTable tst
	WHERE NOT EXISTS (
    SELECT 1
    FROM sTimetable.tTempSTimeTable existing
    WHERE existing.TimeTableSchemaId = tst.TimeTableSchemaId
    AND existing.DivisionId = tst.DivisionId
    AND existing.DayID = tst.DayID
    AND existing.LectureNumber = tst.LectureNumber
)
	print @lNextNumber

UPDATE tttst
SET tttst.EmployeeId = -2,
    tttst.SubjectId = -2
FROM sTimetable.tTempSTimeTable tttst
INNER JOIN sTimetable.tDivisionTimeOff t2 ON tttst.TimeTableSchemaId = t2.TimeTableSchemaId
                                          AND tttst.DivisionId = t2.DivisionId
                                          AND tttst.DayId = t2.DayId
                                          AND tttst.LectureNumber = t2.LectureNumber;
update sCommon.tTableRowId set CurrentNumber =(select max(id) from sTimetable.tTempSTimeTable) where TableTitle = 'sTimetable.tTempSTimeTable'
	select * from sTimetable.tTempSTimeTable 
	--select * from @Temp_TimeTable_AlterMainTable
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetDataByView]...';


GO
CREATE procedure [sTimetable].[pWebGetDataByView]

@rTimeTableSchemaId Bigint ,
@rDivisionId Bigint = 0,
@rOrgId Bigint,
@rTeacherId Bigint =0,
@rSubjectId Bigint =0

as 
begin
if (@rSubjectId <> 0)
begin
		Select TT.Id, TT.EMployeeId, TT.DivisionId, TT.SubjectId , TT.DayId, TT.LectureNumber ,SU.Title, TE.Title
		,DI.DisplayTitle
		from sTimetable.tTempSTimeTable TT inner join [sTimetable].[tSubject] SU on SU.SubjectId = TT.SubjectId 
		inner join sTimetable.tTeacher TE on TE.EmployeeId = TT.EMployeeId
		inner join sTimetable.tAssignOrg AO  on AO.TimeTableSchemaId = TT.TimeTableSchemaId
		inner join sTimetable.tDivision DI on DI.DivisionId = TT.DivisionId 
		where TT.TimeTableSchemaId = @rTimeTableSchemaId and AO.OrgId = @rOrgId and SU.SubjectId = @rSubjectId

end
else if (@rDivisionId <> 0)
begin
		Select TT.Id, TT.EMployeeId, TT.DivisionId, TT.SubjectId , TT.DayId, TT.LectureNumber ,SU.Title, TE.Title
		,DI.DisplayTitle
		from sTimetable.tTempSTimeTable TT inner join [sTimetable].[tSubject] SU on SU.SubjectId = TT.SubjectId 
		inner join sTimetable.tTeacher TE on TE.EmployeeId = TT.EMployeeId
		inner join sTimetable.tAssignOrg AO  on AO.TimeTableSchemaId = TT.TimeTableSchemaId
		inner join sTimetable.tDivision DI on DI.DivisionId = TT.DivisionId 
		where TT.TimeTableSchemaId = @rTimeTableSchemaId and AO.OrgId = @rOrgId and DI.DivisionId =@rDivisionId
end

else if (@rTeacherId <> 0)
begin

		Select TT.Id, TT.EMployeeId, TT.DivisionId, TT.SubjectId , TT.DayId, TT.LectureNumber ,SU.Title, TE.Title
		,DI.DisplayTitle
		from sTimetable.tTempSTimeTable TT inner join [sTimetable].[tSubject] SU on SU.SubjectId = TT.SubjectId 
		inner join sTimetable.tTeacher TE on TE.EmployeeId = TT.EMployeeId
		inner join sTimetable.tAssignOrg AO  on AO.TimeTableSchemaId = TT.TimeTableSchemaId
		inner join sTimetable.tDivision DI on DI.DivisionId = TT.DivisionId 
		where TT.TimeTableSchemaId = @rTimeTableSchemaId and AO.OrgId = @rOrgId and TE.EmployeeId = @rTeacherId

end
else
begin
	Select TT.Id, TT.EMployeeId, TT.DivisionId, TT.SubjectId , TT.DayId, TT.LectureNumber ,SU.Title, TE.Title
		,DI.DisplayTitle
		from sTimetable.tTempSTimeTable TT inner join [sTimetable].[tSubject] SU on SU.SubjectId = TT.SubjectId 
		inner join sTimetable.tTeacher TE on TE.EmployeeId = TT.EMployeeId
		inner join sTimetable.tAssignOrg AO  on AO.TimeTableSchemaId = TT.TimeTableSchemaId
		inner join sTimetable.tDivision DI on DI.DivisionId = TT.DivisionId 
		where TT.TimeTableSchemaId = @rTimeTableSchemaId and AO.OrgId = @rOrgId 
end

end
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetDivisionWorkLoadbyDivisionId]...';


GO
CREATE Procedure [sTimetable].[pWebGetDivisionWorkLoadbyDivisionId]
@rDivisionId int,
@rSchemaId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin 
SELECT DISTINCT
    tw.employeeid,
    dw.DivisionId,
    dw.NoOfLecture - ISNULL(tt.CountSubjectId, 0) AS DivisionWorkLoad,
    dw.SubjectId,
    tw.nooflecture - ISNULL(tt.CountTeacherId, 0) AS TeacherWorkLoad
FROM 
    sTimetable.tDivisionWorkLoad AS dw
INNER JOIN 
    sTimetable.tTeacherWorkLoad AS tw ON dw.DivisionId = tw.divisionid
LEFT JOIN (
    SELECT
        DivisionId,
        SubjectId,
        COUNT(*) AS CountSubjectId,
        EmployeeId,
        COUNT(EmployeeId) AS CountTeacherId
    FROM
        sTimetable.tTempSTimeTable
    WHERE
        DivisionId = @rDivisionId
    GROUP BY
        DivisionId,
        SubjectId,
        EmployeeId
) AS tt ON dw.DivisionId = tt.DivisionId AND dw.SubjectId = tt.SubjectId AND tw.employeeid = tt.EmployeeId
WHERE 
    dw.DivisionId = @rDivisionId 
    AND dw.SubjectId = tw.subjectid
	AND dw.TimetableSchemaId=@rSchemaId;
end
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetDropdownOnClassChange]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetDropdownOnClassChange](
@rOrgId int,
@rSchemaId int,
@rClassId int
)
AS
BEGIN
		--Division
		SELECT D.Id AS DivisionId,D.DisplayTitle AS DivisionTitle FROM sTimetable.tDivision D INNER JOIN sTimetable.tDivisionClassRoom DC ON D.Id = DC.DivisionId INNER JOIN sTimetable.tClassRoom CR ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId AND DC.ClassRoomId = @rClassId;
		--Subject
		SELECT S.Id AS SubjectId, S.Title AS SubjectTitle FROM sTimetable.tDivision D INNER JOIN sTimetable.tDivisionClassRoom DC ON D.Id = DC.DivisionId INNER JOIN sTimetable.tClassRoom CR ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tSubject S ON S.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId AND DC.ClassRoomId = @rClassId;
		--Teacher
		SELECT T.EmployeeId AS TeacherId,T.Title AS TeacherTitle FROM sTimetable.tDivision D INNER JOIN sTimetable.tDivisionClassRoom DC ON D.Id = DC.DivisionId INNER JOIN sTimetable.tClassRoom CR ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tTeacher T ON T.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId AND DC.ClassRoomId = @rClassId;

END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetDropdownOnDivisionChange]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetDropdownOnDivisionChange](
	@rOrgId int,
	@rSchemaId int,
	@rClassId int,
	@rDivisionId int
)
AS
BEGIN
		--Subject
		SELECT 
    S.Id AS SubjectId,
    S.Title AS SubjectTitle
FROM 
    sTimetable.tDivision D 
INNER JOIN 
    sTimetable.tDivisionClassRoom DC ON D.Id = DC.DivisionId 
INNER JOIN 
    sTimetable.tClassRoom CR ON CR.Id = DC.ClassRoomId 
INNER JOIN 
    sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId 
INNER JOIN 
    sTimetable.tSubject S ON S.OrgId = OM.Id 
INNER JOIN 
    sTimetable.tTeacher T ON T.OrgId = OM.Id 
INNER JOIN 
    sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId 
WHERE 
    AO.TimeTableSchemaId = @rSchemaId 
    AND AO.OrgId = @rOrgId 
    AND DC.ClassRoomId = @rClassId 
    AND D.Id = @rDivisionId
GROUP BY
    S.Id, S.Title;

		----Teacher
		SELECT 
    T.EmployeeId AS TeacherId,
    T.Title AS TeacherTitle
FROM 
    sTimetable.tDivision D 
INNER JOIN 
    sTimetable.tDivisionClassRoom DC ON D.Id = DC.DivisionId 
INNER JOIN 
    sTimetable.tClassRoom CR ON CR.Id = DC.ClassRoomId 
INNER JOIN 
    sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId 
INNER JOIN 
    sTimetable.tSubject S ON S.OrgId = OM.Id 
INNER JOIN 
    sTimetable.tTeacher T ON T.OrgId = OM.Id 
INNER JOIN 
    sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId 
WHERE 
    AO.TimeTableSchemaId = @rSchemaId 
    AND AO.OrgId = @rOrgId 
    AND DC.ClassRoomId = @rClassId 
    AND D.Id = @rDivisionId
GROUP BY
    T.EmployeeId, T.Title;


END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetDropdownOnOrganizationChange]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetDropdownOnOrganizationChange] (
@rOrgId int,
@rSchemaId int
)
AS
BEGIN
		--Class DropDown
		SELECT DISTINCT CR.Id AS ClassId,CR.Title + '-' + STR(CR.OrgGroupId) AS ClassTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Subject Dropdown
		SELECT DISTINCT S.Id AS SubjectId, S.Title AS SubjectTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tSubject S ON S.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Division Dropwdown
		SELECT DISTINCT D.Id AS DivisionId, D.DisplayTitle AS DivisionTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tDivision D ON D.Id = DC.DivisionId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Teachers Dropdown
		SELECT DISTINCT T.EmployeeId AS TeacherId, T.Title AS TeacherTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tTeacher T ON T.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
	
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetdropdownTimetableShemachange]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetdropdownTimetableShemachange] 
@rSchemaId bigint
AS
BEGIN
        --Org Dropdown
		Select Distinct OM.Id, OM.Title from sSetup.tOrganizationMaster OM inner join sTimetable.tAssignOrg AO on OM.Id=AO.OrgId where AO.TimeTableSchemaId=@rSchemaId 
		--Class DropDown
		SELECT DISTINCT CR.Id AS ClassId,CR.Title + '-' + STR(CR.OrgGroupId) AS ClassTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId;
		--Subject Dropdown
		SELECT DISTINCT S.Id AS SubjectId, S.Title AS SubjectTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tSubject S ON S.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId 
		--Division Dropwdown
		SELECT DISTINCT D.Id AS DivisionId, D.DisplayTitle AS DivisionTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tDivision D ON D.Id = DC.DivisionId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId;
		--Teachers Dropdown
		SELECT DISTINCT T.EmployeeId AS TeacherId, T.Title AS TeacherTitle FROM sTimetable.tClassRoom CR INNER JOIN sTimetable.tDivisionClassRoom DC ON CR.Id = DC.ClassRoomId INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgGroupId INNER JOIN sTimetable.tTeacher T ON T.OrgId = OM.Id INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgGroupId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId;
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetGridBySchemaId]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetGridBySchemaId] 
	@rSchemaId int,
	@rClassId int,
	@rOrgId int
AS
BEGIN
Select  Distinct TS.DayId, Max(TS.NoOfPeriod)
from sTimetable.tTimeSetup TS
Inner Join sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId=TS.TimeTableSchemaId
WHERE TS.TimeTableSchemaId=@rSchemaId 
AND TS.ClassId=@rClassId 
And AO.OrgId=@rOrgId

Group By DayId
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetLectureNumberBySchemaId]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetLectureNumberBySchemaId] 
	@rSchemaId int
AS
BEGIN
Select DayId, EmployeeId, LectureNumber from sTimetable.tTempSTimeTable WHERE TimeTableSchemaId=@rSchemaId Order By EmployeeId asc
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetLectureNumberOfEmployeeOfDay]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetLectureNumberOfEmployeeOfDay]
    @rSchemaId bigint,
    @rEmployeeId bigint,
    @rDayId int
AS
BEGIN
    Select LectureNumber from sTimetable.tTempSTimeTable where EmployeeId=@rEmployeeId and DayId=@rDayId and TimeTableSchemaId=@rSchemaId
    union all
    select LectureNumber from sTimetable.tTeacherTimeOff where EmployeeId=@rEmployeeId and DayId=@rDayId and TimeTableSchemaId=@rSchemaId
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetSubjectTimetOff]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetSubjectTimetOff]
@rSchemaId int,
@rSubjectId int
AS
BEGIN
	Select DayId,LectureNumber from sTimetable.tSubjectTimeOff where TimetableSchemaId=@rSchemaId and SubjectId=@rSubjectId
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetSuggestedSubjectList]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetSuggestedSubjectList]  --101,990100069,9915
	@rDivisonId bigint,
	@rSchemaId bigint,
	@rOrgId bigint,
	@rFromFilter int,
	@rToFilter int
AS
BEGIN
SELECT
    TW.SubjectId,
    S.Title AS Subject,
    D.title AS Division,
    D.DivisionId,
    T.Title AS Teacher,
    T.EmployeeId AS TeacherId,
    (T.Title +' - '+ S.Title) AS Code,
    TW.NoOfLecture AS Total,
    (TW.NoOfLecture - (
        SELECT COUNT(LectureNumber)
        FROM sTimetable.tTempSTimeTable
        WHERE DivisionId = @rDivisonId
        AND TimeTableSchemaId = @rSchemaId and EmployeeId=T.EmployeeId
    )) AS Remain
FROM
    sTimetable.tTeacherWorkLoad TW
INNER JOIN
    sTimetable.tTeacher T ON TW.EmployeeId = T.EmployeeId
INNER JOIN
    sTimetable.tSubject S ON S.SubjectId = Tw.SubjectId
INNER JOIN
    sTimetable.tDivision D ON D.DivisionId = TW.DivisionId
INNER JOIN
    sTimetable.tTempSTimeTable TT ON TT.DivisionId = TW.DivisionId
INNER JOIN 
	sTimetable.tAssignOrg AO ON	AO.TimeTableSchemaId = TT.TimeTableSchemaId
WHERE
    (Tw.DivisionId = @rDivisonId Or (@rDivisonId = 0))
    AND (TW.TimeTableSchemaId = @rSchemaId OR (@rSchemaId = 0))
	AND (AO.OrgId=@rOrgId OR (@rOrgId = 0))
	AND TT.LectureNumber>@rFromFilter and TT.LectureNumber<@rToFilter
GROUP BY
    TW.SubjectId,
    S.Title,
    D.title,
    D.DivisionId,
    T.Title,
    T.EmployeeId,
    (T.Title +' - '+ S.Title),
    TW.NoOfLecture;

END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetTimeTableBySchemaId]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetTimeTableBySchemaId] --991100022,991200003,0,9912,991200002,0,0
    @rSchemaId bigint,
	@rDivisionId BIGINT,
	@rDayId int,
    @rOrgId bigint ,
    @rClassId bigint,
    @rFromFilter int ,
    @rToFilter int 
AS
BEGIN
    SET NOCOUNT ON;
    sELECT OM.ID AS OrganizationId,
        OM.Title AS OrganizationTitle,
        CM.ID AS ClassId,
        Cm.Title AS ClassTitle,
        TST.DivisionId,
        TST.EmployeeId,
        TST.SubjectId,
        TST.DayId,
        TST.LectureNumber,
        --TE.Title AS TeacherTitle,
        --SU.Title AS SubjectTitle,
        TST.Id 
FROM STIMETABLE.tTempSTimeTable TST
INNER JOIN sSetup.tDivisionMaster DM on TST.DivisionId=DM.Id
Inner Join sSetup.tClassMaster CM ON DM.ClassId=CM.Id
Inner Join sSetup.tOrganizationMaster OM ON OM.Id=CM.OrgId
Inner Join sTimetable.tTimeTableSchema TSC ON TSC.Id=tst.TimeTableSchemaId
iNNER Join sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId=TSC.Id
where TSC.Id=@rSchemaId
AND (OM.Id = @rOrgId OR (@rOrgId = 0 or isnull(@rOrgId,0) = 0))
AND (DM.ClassId = @rClassId OR (@rClassId = 0 or isnull(@rClassId,0) = 0))
AND (DM.Id = @rDivisionId OR (@rDivisionId = 0 or isnull(@rDivisionId,0) = 0))
AND (TST.DayId = @rDayId OR (@rDayId = 0 or isnull(@rDayId,0) = 0))
AND (TST.LectureNumber >=@rFromFilter OR (@rFromFilter = 0 or isnull(@rFromFilter,0) = 0))
AND (TST.LectureNumber <=@rToFilter OR (@rToFilter = 0 or isnull(@rToFilter,0) = 0))
GROUP BY 
OM.ID, OM.Title, CM.ID, Cm.Title, TST.DivisionId, TST.EmployeeId, TST.SubjectId, 
TST.DayId, TST.LectureNumber, TST.Id
ORDER BY 
Om.Id,TST.DayId
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetTimetablebySchemaIdAndByDivisionId]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetTimetablebySchemaIdAndByDivisionId]
    @rSchemaId BIGINT,
    @rDivisionId BIGINT,
	@rOrgId Bigint
AS
BEGIN
SELECT 
	TT.DayId,
    TT.SubjectId,
    SU.Title AS SubjectTitle,
    TT.EmployeeId as TeacherId,
    TE.Title AS TeacherTitle,
    TT.LectureNumber,
    TT.Id as TableIndexId
FROM 
    sTimetable.tTempSTimeTable TT
INNER JOIN 
    sTimetable.tTeacher TE ON TE.EmployeeId = TT.EmployeeId
INNER JOIN 
    sTimetable.tSubject SU ON SU.SubjectId = TT.SubjectId 
INNER JOIN 
    sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
WHERE 
    TT.TimeTableSchemaId = @rSchemaId 
    AND TT.DivisionId = @rDivisionId
    AND AO.OrgId = @rOrgId
ORDER BY 
    TT.SubjectId, TT.LectureNumber;
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebGetTimetablebySchemaIdAndByDivisionIdForExchangingLectures]...';


GO
CREATE PROCEDURE [sTimetable].[pWebGetTimetablebySchemaIdAndByDivisionIdForExchangingLectures]
    @rSchemaId BIGINT,
    @rDivisionId BIGINT,
	@rOrgId Bigint
AS
BEGIN
SELECT 
	TT.DayId,
    TT.SubjectId,
    SU.Title AS SubjectTitle,
    TT.EmployeeId as TeacherId,
    TE.Title AS TeacherTitle,
    TT.LectureNumber,
    TT.Id as TableIndexId
FROM 
    sTimetable.tTempSTimeTable TT
INNER JOIN 
    sTimetable.tTeacher TE ON TE.EmployeeId = TT.EmployeeId
INNER JOIN 
    sTimetable.tSubject SU ON SU.SubjectId = TT.SubjectId 
INNER JOIN 
    sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
WHERE 
    TT.TimeTableSchemaId = @rSchemaId 
    AND TT.DivisionId = @rDivisionId
    AND AO.OrgId = @rOrgId
ORDER BY 
    TT.SubjectId, TT.LectureNumber;
END;
GO
PRINT N'Creating Procedure [sTimetable].[pWebLockperiod]...';


GO
CREATE PROCEDURE [sTimetable].[pWebLockperiod]
	@rOrgId int,
    @rSchemaId int,
    @rDivisionId int,
    @rDayId int,
    @rLectureNo int,
	@rLockNo int
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION; -- Start the transaction

        -- Perform the update operation
          update sTimeTable.tTempSTimeTable set LockNo = @rLockNo 
		  FROM sTimeTable.tTempSTimeTable TTT
		  inner join sTimeTable.tAssignOrg AO on AO.TimeTableSchemaId = TTT.TimeTableSchemaId  where
		  TTT.TimeTableSchemaId = @rSchemaId and TTT.DivisionId=@rDivisionId and 
		  TTT.DayId = @rDayId and TTT.LectureNumber =@rLectureNo and AO.OrgId = @rOrgId

        -- Check if any rows were affected by the update
        IF @@ROWCOUNT > 0
        BEGIN
            -- If rows were updated, select 1
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            -- If no rows were updated, select 0
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION; -- Commit the transaction
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION; -- Rollback the transaction if an error occurs
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebMangeOldTimetableTemp]...';


GO
CREATE PROCEDURE [sTimetable].[pWebMangeOldTimetableTemp] 
(
    @rDivisionId INT,
    @rSchemaId INT,
    @rOrgId INT,
    @rFromFilter INT,
    @rToFilter INT
)
AS
BEGIN
    DELETE TST
    FROM sTimetable.tTempSTimeTable TST
    INNER JOIN sTimetable.tAssignOrg AO ON TST.TimeTableSchemaId = AO.TimeTableSchemaId
    WHERE 
        TST.TimeTableSchemaId = @rSchemaId  -- Ensure TimeTableSchemaId is always provided
        AND (TST.DivisionId = @rDivisionId OR (@rDivisionId = 0))
        AND (AO.OrgId = @rOrgId OR (@rOrgId = 0))
        AND (
            TST.LectureNumber > @rFromFilter OR (@rFromFilter = 0 OR ISNULL(@rFromFilter, 0) = 0)
        )
        AND (
            TST.LectureNumber < @rToFilter OR (@rToFilter = 0 OR ISNULL(@rToFilter, 0) = 0)
        );

    DECLARE @lNextNumber INT;
    EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT;

    -- Assuming that sTimetable.tTempSTimeTable and sTimetable.tTimetable are valid tables
    INSERT INTO sTimetable.tTempSTimeTable
    -- Explicitly list the columns to ensure proper mapping
    SELECT @lNextNumber,
           TT.TimeTableSchemaId,
           TT.EmployeeId, 
           TT.DivisionId, 
           TT.SubjectId,
           TT.DayId,
           TT.LectureNumber, 
           TT.LockNo, 
           TT.LockColour, TT.SchoolRoomId, TT.IsBreak, TT.IsMergeDivision, TT.IsBreakBefore
    FROM sTimetable.tTempSTimeTable TT
    INNER JOIN sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
    WHERE
        TT.TimeTableSchemaId = @rSchemaId  -- Ensure TimeTableSchemaId is always provided
        AND (TT.DivisionId = @rDivisionId OR (@rDivisionId = 0))
        AND (AO.OrgId = @rOrgId OR (@rOrgId = 0))
        AND (
            TT.LectureNumber > @rFromFilter OR (@rFromFilter = 0 OR ISNULL(@rFromFilter, 0) = 0)
        )
        AND (
            TT.LectureNumber < @rToFilter OR (@rToFilter = 0 OR ISNULL(@rToFilter, 0) = 0)
        );
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebSetColor]...';


GO
CREATE PROCEDURE [sTimetable].[pWebSetColor]
	@rOrgId int,
    @rSchemaId int,
    @rDivisionId int,
    @rDayId int,
    @rLectureNo int,
    @rColorCode nvarchar(20) 
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

       update sTimeTable.tTempSTimeTable 
	   set LockColour = @rColorCode  FROM sTimeTable.tTempSTimeTable TTT
		  inner join sTimeTable.tAssignOrg AO on AO.TimeTableSchemaId = TTT.TimeTableSchemaId   where
		  TTT.TimeTableSchemaId = @rSchemaId and TTT.DivisionId=@rDivisionId and 
		  TTT.DayId = @rDayId and TTT.LectureNumber =@rLectureNo and AO.OrgId = @rOrgId


        IF @@ROWCOUNT > 0
        BEGIN
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION; 
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION; 
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebTimeOffLecture]...';


GO
CREATE PROCEDURE [sTimetable].[pWebTimeOffLecture]  
	@rSchemaId int,
	@rDivisionId int,
	@rDayId tinyint,
	@rLeactureNumber int,
	@rSubjectId int,
	@rEmployeeId int,
	@rSchoolRoomId int,
	@Return int OUTPUT
AS
BEGIN
	DECLARE @count int
	DECLARE @Timetable TABLE 
	(
		Id int,
		TimeTableSchemaId	int,
		EmployeeId	bigint,
		DivisionId	int,
		SubjectId	int,
		DayId	tinyint,
		LectureNumber	tinyint,
		LockNo	bigint,
		LockColour	varchar(50),
		SchoolRoomId	int,
		IsBreak	bit,
		IsMergeDivision	bit,
		IsBreakBefore	bit
	)
	

	INSERT INTO @Timetable
	Select * from [sTimetable].[tTempSTimeTable] Where TimeTableSchemaId= @rSchemaId 

	
	
	set @count = (SELECT COUNT(*) FROM [sTimetable].[tDivisionTimeOff] Where TimeTableSchemaId= @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber)
	IF (@count > 0)
	BEGIN
		update [sTimetable].[tTempSTimeTable] set EmployeeId = -2 , SubjectId = -2 WHERE DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber AND LockNo != 1
		--SELECT 1 --- division time off
		set @Return = 1 --- division time off
	END


	set @count = (SELECT COUNT(*) FROM [sTimetable].[tSubjectTimeOff] Where TimeTableSchemaId= @rSchemaId AND SubjectId = @rSubjectId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber)
	IF (@count > 0)
	BEGIN
		update [sTimetable].[tTempSTimeTable] set EmployeeId = 0 , SubjectId = 0 WHERE DivisionId = @rDivisionId AND SubjectId = @rSubjectId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber AND LockNo != 1
		SELECT 2 -- Subject time off
		set @Return = 2 -- Subject time off
	END


	set @count = (SELECT COUNT(*) FROM [sTimetable].[tTeacherTimeOff] Where TimeTableSchemaId= @rSchemaId AND EmployeeId = @rEmployeeId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber)
	IF (@count > 0)
	BEGIN
		
		--SELECT 3 -- Teacher time off
		set @Return = 3 -- Teacher time off
	END


	set @count = (SELECT COUNT(*) FROM [sTimetable].[tClassRoomTimeOff] Where TimeTableSchemaId= @rSchemaId AND ClassRoomId = @rSchoolRoomId AND DayId = @rDayId AND LectureNumber = @rLeactureNumber)
	IF (@count > 0)
	BEGIN
		
		--SELECT 4 -- Class room time off
		set @Return = 4 -- Class room time off
	END

	SELECT 0  -- default select 

END
GO


PRINT N'Creating Procedure [sTimetable].[pWebTimeTableXMLAdd]...';


GO

CREATE PROCEDURE [sTimetable].[pWebTimeTableXMLAdd]
    @rOrgGroupId int,
    @rXMLDoc [nvarchar](max)
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rXMLDoc

--BEGIN TRANSACTION;

BEGIN
    --BEGIN TRY
        DECLARE @lNextNumber BIGINT    

		DELETE TT 
		FROM sTimeTable.tTempSTimetable TT 
		INNER JOIN sTimeTable.tTimeTableSchema TTS ON TT.TimeTableSchemaID = TTS.Id 
		--INNER JOIN sSetup.tOrgGroupBatchMaster OGB ON TTS.OrgGroupBatchId = OGB.Id
		INNER JOIN (
			SELECT DISTINCT 
				TimeTableSchemaId,
				DivisionId
			FROM OPENXML (@loDoc, '/Table/TimeTable', 2) 
			WITH (
				TimeTableSchemaId int,
				DivisionId int
			)
		) OXM ON OXM.TIMETABLESCHEMAID = TTS.ID
		--AND OGB.OrgGroupId = @rOrgGroupId
		WHERE TT.DivisionId = OXM.DivisionId
		AND TT.LockNo=0 
		  --AND OGB.OrgGroupId = @rOrgGroupId;


        EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
        BEGIN
            INSERT INTO sTimetable.tTempSTimeTable
            (
                Id,
                TimeTableSchemaId,
                EmployeeId,
                DivisionId,
                SubjectId,
                DayId,
                LectureNumber,
                LockNo,
                LockColour,
                SchoolRoomId,
                IsBreak,
                IsMergeDivision,
                IsBreakBefore
            )
            SELECT
                @lNextNumber+ ROW_NUMBER() OVER(ORDER BY TimeTableSchemaId),
                TimeTableSchemaId,
                EmployeeId,
                DivisionId,
                SubjectId,
                DayId,
                LectureNumber,
                LockNo,
                LockColour,
                SchoolRoomId,
                IsBreak,
                IsMergeDivision,
                IsBreakBefore
            FROM OPENXML (@loDoc, '/Table/TimeTable', 2) WITH ( 
                TimeTableSchemaId int,
                EmployeeId bigint,
                DivisionId int,
                SubjectId int,
                DayId tinyint,
                LectureNumber tinyint,
                LockNo bigint,
                LockColour varchar(50),
                SchoolRoomId int,
                IsBreak bit,
                IsMergeDivision bit,
                IsBreakBefore bit
            )
            EXEC sp_xml_removedocument @loDoc
            EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
            --SELECT @lNextNumber = MAX(ID) FROM sTimetable.tTimeTable 
            --EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sTimetable.tTimeTable', @rCurrentNumber = @lNextNumber 
            SELECT 1 AS Result
        END
    --END TRY

    --BEGIN CATCH 
    --    --ROLLBACK TRANSACTION

    --    --SELECT -1 AS Result
    --END CATCH
END

--COMMIT TRANSACTION;
GO
PRINT N'Creating Procedure [sTimetable].[pWebUnlockperiod]...';


GO
CREATE PROCEDURE [sTimetable].[pWebUnlockperiod]
	@rOrgId int,
    @rSchemaId int,
    @rDivisionId int,
    @rDayId int,
    @rLectureNo int
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION; -- Start the transaction

        -- Perform the update operation
          update sTimeTable.tTempSTimeTable set LockNo = 0  FROM sTimeTable.tTempSTimeTable TTT
		  inner join sTimeTable.tAssignOrg AO on AO.TimeTableSchemaId = TTT.TimeTableSchemaId 
		  where TTT.TimeTableSchemaId = @rSchemaId and TTT.DivisionId=@rDivisionId and 
		  TTT.DayId = @rDayId and TTT.LectureNumber =@rLectureNo and AO.OrgId = @rOrgId
        -- Check if any rows were affected by the update
        IF @@ROWCOUNT > 0
        BEGIN
            -- If rows were updated, select 1
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            -- If no rows were updated, select 0
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION; -- Commit the transaction
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION; -- Rollback the transaction if an error occurs
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebUnSetColor]...';


GO
CREATE PROCEDURE [sTimetable].[pWebUnSetColor]
	@rOrgId int,
    @rSchemaId int,
    @rDivisionId int,
    @rDayId int,
    @rLectureNo int
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION; 

        UPDATE sTimeTable.tTempSTimeTable 
        SET LockColour = 0 
			 FROM sTimeTable.tTempSTimeTable TTT
		  inner join sTimeTable.tAssignOrg AO on AO.TimeTableSchemaId = TTT.TimeTableSchemaId   where
		  TTT.TimeTableSchemaId = @rSchemaId and TTT.DivisionId=@rDivisionId and 
		  TTT.DayId = @rDayId and TTT.LectureNumber =@rLectureNo and AO.OrgId = @rOrgId 
        IF @@ROWCOUNT > 0
        BEGIN
            SELECT 1 AS result;
        END
        ELSE
        BEGIN
            SELECT 0 AS result;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        select -101 AS result;
    END CATCH
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebWeeklyLectureReport]...';


GO
CREATE PROCEDURE [sTimetable].[pWebWeeklyLectureReport]
	@rSchemaId INT ,
	@rOrgId INT ,
	@rClassId INT ,
	@rDivisionId INT ,
	@rEmployeeId BIGINT ,
	@rSubjectId INT,
	@rFromFilter INT ,
	@rToFilter INT 
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @Datatable_TimeTable TABLE (
	EmplopyeeName varchar(max),
    Mon INT,
    Tue INT,
    Wed INT,
    Thu INT,
    Fri INT, 
    Sat INT
);
INSERT INTO @Datatable_TimeTable (EmplopyeeName,Mon, Tue, Wed, Thu, Fri, Sat)

 SELECT 
   'Total Remaining Weekly lecture',
    COUNT(CASE WHEN TST.DayId = 2 THEN TST.id ELSE NULL END),
    COUNT(CASE WHEN TST.DayId = 3 THEN TST.id ELSE NULL END),
    COUNT(CASE WHEN TST.DayId = 4 THEN TST.id ELSE NULL END),
    COUNT(CASE WHEN TST.DayId = 5 THEN TST.id ELSE NULL END),
    COUNT(CASE WHEN TST.DayId = 6 THEN TST.id ELSE NULL END),
    COUNT(CASE WHEN TST.DayId = 7 THEN TST.id ELSE NULL END)
FROM sTimeTable.tTempSTimeTable TST
INNER JOIN sSetup.tDivisionMaster DM on TST.DivisionId=DM.Id
INNER JOIN sSetup.tClassMaster CM ON DM.ClassId=CM.Id
INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=CM.OrgId
WHERE TST.EmployeeId = 0 and TST.EmployeeId <> -2  AND TST.TimeTableSchemaId = @rSchemaId
AND (OM.Id = @rOrgId Or (@rOrgId = 0))
AND (CM.Id = @rClassId Or (@rClassId = 0))
AND (DM.Id = @rDivisionId Or (@rDivisionId = 0))
AND ((TST.LectureNumber >= @rFromFilter Or (@rFromFilter = 0)) AND (TST.LectureNumber <= @rToFilter Or (@rToFilter = 0)))
INSERT INTO @Datatable_TimeTable (EmplopyeeName,Mon, Tue, Wed, Thu, Fri, Sat)
SELECT 
(select Distinct EM.ShortName from sTimetable.tTeacherWorkLoad TW inner join sHrm.tEmployeeMaster EM on EM.Id = twl.EmployeeId where TimeTableSchemaId = @rSchemaId and TW.EmployeeId = twl.EmployeeId )as Employee,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 2 AND TimeTableSchemaId = @rSchemaId) as AssignLecture1,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 3 AND TimeTableSchemaId = @rSchemaId) as AssignLecture2,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 4 AND TimeTableSchemaId = @rSchemaId) as AssignLecture3,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 5 AND TimeTableSchemaId = @rSchemaId) as AssignLecture4,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 6 AND TimeTableSchemaId = @rSchemaId) as AssignLecture5,
    (SELECT COUNT(id) FROM sTimeTable.tTempSTimeTable WHERE EmployeeId = twl.EmployeeId AND Dayid = 7 AND TimeTableSchemaId = @rSchemaId) as AssignLecture6
FROM 
    (SELECT DISTINCT EmployeeId 
     FROM sTimetable.tTeacherWorkLoad 
     WHERE TimeTableSchemaId = @rSchemaId) AS twl
select * from @Datatable_TimeTable


END
GO
PRINT N'Creating Procedure [sTimetable].[pWebXMLTempTimetableUpdate]...';


GO
CREATE PROCEDURE [sTimetable].[pWebXMLTempTimetableUpdate] 
    @rXmlDoc nvarchar(MAX)
AS

SET NOCOUNT ON

DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rXMLDoc

--BEGIN TRANSACTION;

--BEGIN TRY
    DECLARE @lNextNumber BIGINT    
    -----------------------
    UPDATE [sTimetable].[tTempSTimeTable]
SET
    [EmployeeId] = ISNULL(A.EmployeeId, 0),
    [SubjectId] = ISNULL(A.SubjectId, 0)
    FROM OPENXML (@loDoc, '/Table/TimeTable', 2) WITH ( 
        Id int, -- Assuming there is an Id field in your XML data
        EmployeeId bigint,
        SubjectId int
    ) AS A
    WHERE [sTimetable].[tTempSTimeTable].Id = A.Id

    EXEC sp_xml_removedocument @loDoc
    --EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTimetable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT
    --SELECT @lNextNumber = MAX(ID) FROM sTimetable.tTimeTable 
    --EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sTimetable.tTimeTable', @rCurrentNumber = @lNextNumber 
    SELECT 1 AS Result
--END TRY

--BEGIN CATCH 
--    --ROLLBACK TRANSACTION

--    --SELECT -1 AS Result
--END CATCH
--COMMIT TRANSACTION;
GO

PRINT N'Creating Procedure [sTimetable].[pWebSetSameLecture]...';


GO
CREATE PROCEDURE [sTimetable].[pWebSetSameLecture]  
	@rDivisionId int,
	@rSchemaId int,
	@rOrgId int,
	@rFromFilter int,
	@rToFilter int,
	@rSubjectId int,
	@rDayId int,
	@rEmployeeId Bigint,
	@rSchoolRoomId int,
	@rLectureNumber int
	
AS
BEGIN
	DECLARE @count int
	DECLARE @timeofflecturecount int 
	DECLARE @cntSubjectLec  int = 0
	DECLARE @timetabledetailcount int = 0
    DECLARE @WorkloadData TABLE (
        SubjectId INT,
        Subject NVARCHAR(200),
		Division NVARCHAR(200),
		DivisionId INT,
		Teacher NVARCHAR(200),
		TeacherId BIGINT,
		Code NVARCHAR(500),
		Total tinyint,
		Remain tinyint
    );

	DECLARE @TimeTable Table(
		Id	int,
		TimeTableSchemaId	int,
		EmployeeId	bigint,
		DivisionId	int,
		SubjectId	int,
		DayId	tinyint,
		LectureNumber	tinyint,
		LockNo	bigint,
		LockColour	varchar(50),
		SchoolRoomId	int,
		IsBreak	bit,
		IsMergeDivision	bit,
		IsBreakBefore	bit
	)

	--INSERT INTO @WorkloadData (SubjectId, Subject,Division , DivisionId ,Teacher,TeacherId,Code,Total,Remain)
 --   EXEC [sTimetable].[pWebGetSuggestedSubjectList] 0, 0 , 0 , 0 , 0 , 0

	

	IF @rOrgId > 0
	BEGIN
		
		INSERT INTO @WorkloadData (SubjectId, Subject,Division , DivisionId ,Teacher,TeacherId,Code,Total,Remain)
		EXEC [sTimetable].[pWebGetSuggestedSubjectList] @rDivisionId, @rSchemaId , @rOrgId , @rFromFilter , @rToFilter 
	END	
	ELSE 
	BEGIN	
		INSERT INTO @WorkloadData (SubjectId, Subject,Division , DivisionId ,Teacher,TeacherId,Code,Total,Remain)
		EXEC [sTimetable].[pWebGetSuggestedSubjectList] 0, 0 , 0 , 0 , 0 
	END
	set @count = (SELECT COUNT(*) FROM @WorkloadData)

	IF @count = 0
	BEGIN
		select 5 as Result -- For this Lecture Same Teacher Workload not alloted
	END
	ELSE 
	BEGIN	
		  EXEC [sTimetable].[pWebTimeOffLecture] @rSchemaId,@rDivisionId,@rDayId,@rLectureNumber,@rSubjectId,@rEmployeeId,@rSchoolRoomId, @Return = @timeofflecturecount OUTPUT 

		  IF (@timeofflecturecount >=1)
		  BEGIN
				SELECT 6 as Result-- For this Lecture Timeoff set
		  END
		  ELSE
		  BEGIN
			set @cntSubjectLec = (select COUNT(*) from [sTimetable].[tTempSTimeTable]  where TimeTableSchemaId = @rSchemaId AND SubjectId = @rSubjectId AND EmployeeId = @rEmployeeId AND DivisionId = @rDivisionId)
			set @timetabledetailcount = (select COUNT(*) FROM [sTimetable].[tTempTimeSTableDetail] where SubjectId = @rSubjectId AND EmployeeId = @rEmployeeId  AND TimetableId IN (Select ID from tTimeTable Where DivisionId=@rDivisionId))	

				IF (@timetabledetailcount > 0)
				BEGIN	
					set @cntSubjectLec = @cntSubjectLec + @timetabledetailcount
				END

				IF ( (CONVERT(int,(SELECT Total FROM @WorkloadData))) - (@cntSubjectLec)  < = 0)
				BEGIN
					SELECT 7 as Result -- No more Lecture Allocation
				END
				ELSE
				BEGIN	
					INSERT INTO @TimeTable
					SELECT * from [sTimetable].[tTempSTimeTable]  where TimeTableSchemaId = @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLectureNumber AND LockNo != 1

					IF((Select EmployeeId FROM @TimeTable) != 0)
					BEGIN
						SELECT 8 as Result --This lecture already Set
					END
					ELSE
					BEGIN
						IF((SELECT COUNT(*) FROM @TimeTable) > 0)
						BEGIN
							Update [sTimetable].[tTempSTimeTable] set EmployeeId = @rEmployeeId , SubjectId  = @rSubjectId WHERE TimeTableSchemaId = @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLectureNumber AND LockNo != 1
							SELECT 9  as Result-- Success
						END
					END

				END
		  END
	END 
END
GO

/****** Object:  Schema [sCommonService]    Script Date: 01-03-2024 12:10:22 ******/
IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'sCommonService')
DROP SCHEMA [sCommonService]
GO
/****** Object:  Schema [sCommonService]    Script Date: 01-03-2024 12:10:22 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'sCommonService')
EXEC sys.sp_executesql N'CREATE SCHEMA [sCommonService]'
GO

/****** Object:  StoredProcedure [sCommonService].[pScreenGet]    Script Date: 01-03-2024 12:08:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pScreenGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sCommonService].[pScreenGet]
GO
/****** Object:  StoredProcedure [sCommonService].[pPermissionGet]    Script Date: 01-03-2024 12:08:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pPermissionGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sCommonService].[pPermissionGet]
GO
/****** Object:  StoredProcedure [sCommonService].[pMenuGet]    Script Date: 01-03-2024 12:08:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pMenuGet]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sCommonService].[pMenuGet]
GO
/****** Object:  StoredProcedure [sCommonService].[GetStudentProfile]    Script Date: 01-03-2024 12:08:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[GetStudentProfile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sCommonService].[GetStudentProfile]
GO

GO
/****** Object:  StoredProcedure [sCommonService].[GetStudentProfile]    Script Date: 01-03-2024 12:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[GetStudentProfile]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sCommonService].[GetStudentProfile] AS'
END
GO

ALTER PROCEDURE [sCommonService].[GetStudentProfile]
	@racademicId bigint,
	@rbatchId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	SELECT A.OrgId, BatchId, StudentId, A.Id AS AcademicId, SM.Code, A.RollNo, (FirstName +' '+MiddleName+' '+LastName) AS StudentName,
                            SM.FirstName, SM.MiddleName, SM.LastName, A.ClassId, A.DivisionId, (CM.DisplayTitle +'-'+ DM.DisplayTitle) AS ClassDivision,
                            DPM.Id AS DepartmentId, DPM.DisplayTitle AS DepartmentName, SM.SUID, B.Title as BatchName
                             FROM sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
                            INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
                            INNER JOIN sSetup.tDepartmentMaster DPM ON DPM.Id = CM.DepartmentId
                            INNER JOIN sSetup.tBatchMaster B ON b.Id = A.BatchId
							WHERE A.Id=@racademicId AND BatchId = @rbatchId



END

GO
/****** Object:  StoredProcedure [sCommonService].[pMenuGet]    Script Date: 01-03-2024 12:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pMenuGet]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sCommonService].[pMenuGet] AS'
END
GO
-- exec sCommonService.pMenuGet 9911000000270,19184029,0,1,98
ALTER PROCEDURE [sCommonService].[pMenuGet]
 @rUserId [bigint],
 @rMenuId int = 0,
 @rResourceTypeId int= 0,
 @rIsCoreMenu bit = 0,
 @rModuleId int = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
 IF(@rIsCoreMenu=0)
 BEGIN
 IF EXISTS(SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId = @rUserId)
  BEGIN
   SELECT
    MenuID,
    ModuleId,
    ParentMenuID,
    MenuTitle,
    ScreenTitle,
    PageURL,
    ScreenId,
    Permission,
    OrderNumber,
    TaskId,
    PageURL  AS PagePath,
    IconPath

   FROM
     (SELECT Menu.Id AS MenuID,
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,
    Menu.Title AS MenuTitle,
    ISNULL(Screen.Title, '') AS ScreenTitle,
    ISNULL(Screen.Page, '') AS PageURL,
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,
    ISNULL(Menu.ScreenId, 0) AS ScreenId,
    ISNULL(PermissionValue, 0) AS Permission,
    ISNULL(OrderNumber, 0) AS OrderNumber,
    ISNULL(Screen.TaskId,0) AS TaskId,
    ISNULL(Screen.IconPath,'') As IconPath,
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,
    ISNULL(T.ModuleId,0) AS ModuleId


   FROM sSecurity.tMenu Menu
   LEFT OUTER JOIN sSecurity.tScreen Screen
    ON Menu.ScreenId = Screen.Id
   LEFT OUTER JOIN (
       SELECT
        TaskId,
        SUM(Permission & 2) PermissionValue
       FROM
        sSecurity.tPermissionUser P
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId
       WHERE
        T.IsObsolete = 0 AND P.UserId = @rUserId
       GROUP BY
        TaskId) TBL2
       ON
        Screen.TaskId = TBL2.TaskId
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId

       WHERE
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page
       AND ISNULL(Screen.IsObsolete, 0) = 0)
       MenuTable
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId
   WHERE
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2
    AND
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))

   ORDER BY
    ParentMenuID
  END
 ELSE
  BEGIN
   SELECT
    MenuID,
    ModuleId,
    ParentMenuID,
    MenuTitle,
    ScreenTitle,
    PageURL,
    ScreenId,
    Permission,
    OrderNumber,
    TaskId,
     PageURL  AS PagePath,
     IconPath

   FROM
     (SELECT Menu.Id AS MenuID,
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,
    Menu.Title AS MenuTitle,
    ISNULL(Screen.Title, '') AS ScreenTitle,
    ISNULL(Screen.Page, '') AS PageURL,
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,
    ISNULL(Menu.ScreenId, 0) AS ScreenId,
    ISNULL(PermissionValue, 0) AS Permission,
    ISNULL(OrderNumber, 0) AS OrderNumber,
    ISNULL(Screen.TaskId,0) AS TaskId,
    ISNULL(Screen.IconPath,'') As IconPath,
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,
    ISNULL(T.ModuleId,0) AS ModuleId


   FROM sSecurity.tMenu Menu
   LEFT OUTER JOIN sSecurity.tScreen Screen
    ON Menu.ScreenId = Screen.Id
   LEFT OUTER JOIN (
       SELECT
        TaskId,
        SUM(Permission & 2) PermissionValue
       FROM
        sSecurity.tPermission P
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId
        INNER JOIN
        (
        SELECT RoleId FROM sSecurity.tUserRole UR WHERE  UserId = @rUserId AND IsObsolete = 0
        ) B ON B.RoleId = P.RoleId
       WHERE
        T.IsObsolete = 0
       GROUP BY
        TaskId) TBL2
       ON
        Screen.TaskId = TBL2.TaskId
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId

       WHERE
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page
       AND ISNULL(Screen.IsObsolete, 0) = 0 AND TBL2.PermissionValue > 0)
       MenuTable
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId
   WHERE
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2
    AND
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))

   ORDER BY
    ParentMenuID
  END
 END
 ELSE
 BEGIN
 SELECT
   MenuID,
   ModuleId,
   ParentMenuID,
   MenuTitle,
   ScreenTitle,
   PageURL,
   ScreenId,
   Permission,
   OrderNumber,
   TaskId,
   PageURL  AS PagePath,
   MenuIsObsolete,
   Icon
  FROM
  (

SELECT
   ISNULL(Menu.Id,0) AS MenuID,
   ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,
   ISNULL(Menu.Title,'') AS MenuTitle,
   ISNULL(Screen.Title, '') AS ScreenTitle,
   ISNULL(Screen.Page, '') AS PageURL,
   ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,
   ISNULL(Screen.Id, 0) AS ScreenId,
   ISNULL(PermissionValue, 0) AS Permission,
   ISNULL(OrderNumber, 0) AS OrderNumber,
   ISNULL(Screen.TaskId,0) AS TaskId,
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,
   ISNULL(T.ModuleId,0) AS ModuleId,
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,
   ISNULL(Screen.IconPath,'') AS Icon,
   ISNULL(Screen.OrderIndex,0) As OrderIndex
  FROM
  sSecurity.tMenu Menu
  LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id
  LEFT OUTER JOIN
  (
  SELECT
   P.TaskId,
   SUM(OT.AvailablePermission & 2) PermissionValue
  FROM
   sSecurity.tPermission P
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId  AND T.IsObsolete = 0
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id AND  MM.IsObsolete = 0
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId
  GROUP BY P.TaskId
  ) TBL2 ON Screen.TaskId = TBL2.TaskId
  LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId
  WHERE
  --(Menu.IsObsolete = 0)
  --AND
  ISNULL(Screen.IsObsolete, 0) = 0
  AND (Menu.Id  IN (@rMenuId))
  --OR ParentMenuID  in (24,2711))
  --AND (
  ----Menu.Id IN (19184027)
  ----OR ParentMenuID IN (55)
  --)

  UNION


  SELECT

   ISNULL(Menu.Id, 0) AS MenuID,
   ISNULL(Menu.Id, 0) AS ParentMenuID,
   ISNULL(Screen1.Title,'') AS MenuTitle,
   ISNULL(Screen1.Title, '') AS ScreenTitle,
   ISNULL(Screen1.Page, '')  AS PageURL,
   ISNULL(Screen1.ScreenGroupId,0) AS ScreenGroupId,
   ISNULL(Screen1.Id, 0) AS ScreenId,
   ISNULL(PermissionValue, 0) AS Permission,
   ISNULL(OrderNumber, 0) AS OrderNumber,
   ISNULL(Screen1.TaskId,0) AS TaskId,
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,
   ISNULL(T.ModuleId,0) AS ModuleId,
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,
   ISNULL(Screen1.IconPath,'') AS Icon,
   ISNULL(Screen1.OrderIndex,0) As OrderIndex
  FROM
   sSecurity.tMenu Menu
   LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id
   LEFT OUTER JOIN sSecurity.tScreen Screen1 ON Screen1.ScreenGroupId = Screen.ScreenGroupId
   LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen1.TaskId
   LEFT OUTER JOIN (
  SELECT
   P.TaskId,
   SUM(OT.AvailablePermission & 2) PermissionValue
  FROM
   sSecurity.tPermission P
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId   AND T.IsObsolete = 0
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId

  GROUP BY P.TaskId
  ) TBL2 ON Screen1.TaskId = TBL2.TaskId
  WHERE
  --(Menu.IsObsolete = 0) AND
  ISNULL(Screen1.IsObsolete, 0) = 0
  --AND (Menu.ParentMenuID  in (24,2711)
  --OR Menu.Id  in (24,2711))
  AND TBL2.PermissionValue > 0
  --AND Screen1.Id IN (31336,31337,31338,31339,31340)
  ) MenuTable
  WHERE
  --(MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2)
  --AND
  ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))
  AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))
  --AND MenuIsObsolete = 0
  AND MenuTable.MenuId IN (@rMenuId)
  AND ModuleId IN (@rModuleId)
  ORDER BY MenuTable.OrderIndex, ParentMenuID
 END
END
GO
/****** Object:  StoredProcedure [sCommonService].[pPermissionGet]    Script Date: 01-03-2024 12:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pPermissionGet]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sCommonService].[pPermissionGet] AS'
END
GO
-- exec sCommonService.pPermissionGet 9911,9911000000270
ALTER PROCEDURE [sCommonService].[pPermissionGet]
	@rOrgGroupId [int],
	@rUserId [bigint],
	@rModuleId [int]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN


IF EXISTS (SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId=@rUserId AND OrgGroupId=@rOrgGroupId)
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermissionUser P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		WHERE
		P.OrgGroupId = @rOrgGroupId AND P.UserId = @rUserId  AND MP.ModuleId = @rModuleId
	) TP
	GROUP BY TP.TaskId
END
ELSE
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermission P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId) R
		ON R.RoleId = P.RoleId
		WHERE
		P.OrgGroupId = @rOrgGroupId AND MP.ModuleId = @rModuleId
		--AND
	--RoleId

	--IN
	-- (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId )
	) TP
	GROUP BY TP.TaskId

	--SELECT	CAST(MAX(TP.C) AS BIT) AS [Create],
	--		CAST(MAX(TP.R) AS BIT) AS [Read],
	--		CAST(MAX(TP.U) AS BIT) AS [Update],
	--		CAST(MAX(TP.D) AS BIT) AS [Delete],
	--		CAST(MAX(TP.S) AS BIT) AS [Search],
	--		CAST(MAX(TP.P) AS BIT) AS [Print],
	--		CAST(MAX(TP.X) AS BIT) AS [Execute]
	--FROM
	--(
	--	SELECT 	(Permission & 1) AS C,
	--			(Permission & 2)/2 AS R,
	--			(Permission & 4)/4 AS U,
	--			(Permission & 8)/8 AS D,
	--			(Permission & 16)/16 AS S,
	--			(Permission & 32)/32 AS P,
	--			(Permission & 64)/64 AS X
	--	FROM sSecurity.tPermission
	--	WHERE TaskId = @rTaskId AND
	--		  RoleId IN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId)
	--) TP
END


END


GO
/****** Object:  StoredProcedure [sCommonService].[pScreenGet]    Script Date: 01-03-2024 12:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sCommonService].[pScreenGet]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sCommonService].[pScreenGet] AS'
END
GO
-- exec sCommonService.pScreenGet 245
ALTER PROCEDURE [sCommonService].[pScreenGet]
	@rModuleId [int]
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin
	BEGIN TRY
		SELECT
			S.Id			AS ScreenId,
			S.Title		AS ScreenTitle,
			S.TaskId		AS TaskId,
			S.Page		As PageUrl,
			S.IsObsolete	AS ScreenIsObsolete,
			S.Title,
			S.Page,
			SG.Id AS ScreenGroupId,
			ISNULL(SG.Title,'') AS ScreenGroupName

		FROM
			sSecurity.tScreen S (nolock)
		LEFT JOIN sSecurity.tScreenGroupMaster SG ON SG.Id=S.ScreenGroupId
		INNER JOIN sSecurity.tTask ST ON S.TaskId = ST.Id
		WHERE
			S.IsObsolete = 0 and ST.ModuleId = @rModuleId and S.Id NOT IN (30039,30047,30075,30076,30077,30078,30097)
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	END CATCH
end


GO

/****** Object:  StoredProcedure [sWf].[pWfApprovalAdd]    Script Date: 26-02-2024 13:41:00 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sWf].[pWfApprovalAdd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sWf].[pWfApprovalAdd]
GO
/****** Object:  StoredProcedure [sWf].[pWfApprovalAdd]    Script Date: 26-02-2024 13:41:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sWf].[pWfApprovalAdd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sWf].[pWfApprovalAdd] AS'
END
GO

--exec [sWf].[pWfApprovalAdd] 0,991100029, 95, 9911000000270,1,'',1,2,1,9911
ALTER PROCEDURE [sWf].[pWfApprovalAdd]
		@rId	BIGINT,
	 	@rDocSourceId BIGINT=0,
	 	@rDocSourceTypeId SMALLINT=0,
        @rActionEmployeeId BIGINT=0,
		@rActionStatusId SMALLINT=0,
		@rActionRemark NVARCHAR(500) = '',
		@rIsSequancial BIT=0,
		@rWfLevel INT=0,
		@rWfFunctionalityId SMALLINT=0,
		@rOrgGroupId SMALLINT

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
    BEGIN TRY
	BEGIN TRANSACTION
    DECLARE @lActionByEmployeeId BIGINT
	SET @lActionByEmployeeId = @rActionEmployeeId

	DECLARE @rUserId BIGINT
	SET @rUserId = @rActionEmployeeId

	IF EXISTS(SELECT SFM.Id FROM [sWf].[tWfFunctionalityMaster] SFM
		INNER JOIN [sWf].[tWfSetup] WFS ON WFS.WfFunctionalityId = SFM.Id
		WHERE WFS.WfTypeMasterId = 2 AND SFM.Id = @rWfFunctionalityId AND @rIsSequancial <> 1)
	BEGIN
		SELECT @rIsSequancial = CASE WHEN WfTypeMasterId = 2 THEN 1 ELSE 0 END,@rWfLevel = WFS.WfLevel FROM [sWf].[tWfFunctionalityMaster] SFM
		INNER JOIN [sWf].[tWfSetup] WFS ON WFS.WfFunctionalityId = SFM.Id
		WHERE WFS.WfTypeMasterId = 2 AND WfLevel > 0 AND SFM.Id = @rWfFunctionalityId
	END


		DECLARE @StatusId SMALLINT
		SET @StatusId = @rActionStatusId

		DECLARE @IsRejectedExists BIT = 0

		IF(@rIsSequancial = 1)
		BEGIN
			SELECT @rActionEmployeeId = ISNULL(U.SourceId,U.Id) FROM sSecurity.tUser U WHERE U.Id = @rActionEmployeeId

			IF(@rActionStatusId <> 4)
			BEGIN
				DECLARE @LogCount INT
				SELECT @LogCount = COUNT(AutoId) FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId = @rId

				IF(@rWfLevel <> @LogCount)
				BEGIN
					IF(@rActionStatusId = 3)
					BEGIN
						SET @StatusId = 2
					END
				END

				DECLARE @HierarchyLevelId BIGINT
				SELECT @HierarchyLevelId=ISNULL(AutoId,0) FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId =@rId AND ActionByEmployeeId=@rActionEmployeeId AND StatusId <> 4
				IF(@HierarchyLevelId > 0)
				BEGIN
					UPDATE [sWf].[tWfHierarchyLevel] SET StatusId = @StatusId , Remark = @rActionRemark
					WHERE AutoId = @HierarchyLevelId
				END

				SELECT @lActionByEmployeeId = EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = @rActionEmployeeId
						AND @rActionEmployeeId IS NOT NULL

			END
		END
		IF(((SELECT EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = (SELECT ISNULL(U.SourceId,U.Id) FROM sSecurity.tUser U WHERE U.Id = @rUserId )) IS NULL) AND (@rIsSequancial = 1) AND @StatusId <> 3 AND @StatusId <> 4)
		BEGIN
			SELECT -301;
		END
		ELSE
		BEGIN
			IF(@rId = 0)
			BEGIN

				DECLARE @lNextNumber INT
				EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sWf.tWfApprovalAction',@lNextNumber = @lNextNumber OUTPUT

				INSERT INTO sWf.tWfApprovalAction
				(
				Id,
				OrgGroupId,
				ActionEmployeeId,
				ActionStatusId,
				ActionRemark,
				DocSourceId,
				DocSourceTypeId,
				WfFunctionalityId,
				CreatedUserId,
				CreatedDateTime
				)
				VALUES
				(
					@lNextNumber,
					@rOrgGroupId,
					@rActionEmployeeId,
					@StatusId,
					@rActionRemark,
					@rDocSourceId,
					@rDocSourceTypeId,
					@rWfFunctionalityId,
					@rActionEmployeeId,
					GETUTCDATE()
				)
				SET @rId=@lNextNumber

				INSERT INTO sWf.tWfApprovalActionLog
				(
					WfApprovalActionId,
					ActionByEmployeeId,
					StatusId,
					IsSequancial,
					ActionRemark,
					CreatedUserId,
					CreatedDateTime
				)
				VALUES
				(
					@rId,
					@rActionEmployeeId,
					@rActionStatusId,
					@rIsSequancial,
					@rActionRemark,
					@rActionEmployeeId,
					GETUTCDATE()
				)

				IF(@rIsSequancial = 1 AND (@rWfLevel <> @LogCount OR @rWfLevel > 0))
				BEGIN																																																																																																																																																																				IF(@lActionByEmployeeId > 0)
					INSERT INTO [sWf].[tWfHierarchyLevel]
							(WfApprovalActionId
							,StatusId
							,Remark
							,ActionByEmployeeId
							,ActionDatetime)
						VALUES
							(@rId
							,@StatusId
							,''
							,@lActionByEmployeeId
							,sCommon.fGetDate())
				END
				SELECT @rId AS Result
			END
			ELSE
			BEGIN
				IF EXISTS(SELECT AA.Id FROM sWf.tWfApprovalAction AA WHERE AA.ActionStatusId = 3 AND AA.Id = @rId AND AA.ActionEmployeeId <> @rActionEmployeeId)
				BEGIN
					SELECT -201
					SET @IsRejectedExists = 1
				END
				ELSE
				BEGIN
					IF EXISTS(SELECT ID FROM sWf.tWfApprovalAction WHERE id = @rId AND ActionStatusId <> 4)
					BEGIN
						UPDATE sWf.tWfApprovalAction
						SET
						DocSourceId=@rDocSourceId,
						DocSourceTypeId=@rDocSourceTypeId,
						ActionEmployeeId=@rActionEmployeeId,
						ActionStatusId=@StatusId,
						ActionRemark=@rActionRemark,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
						WHERE id = @rId

						INSERT INTO sWf.tWfApprovalActionLog
						(
							WfApprovalActionId,
							ActionByEmployeeId,
							StatusId,
							IsSequancial,
							ActionRemark,
							CreatedUserId,
							CreatedDateTime
						)
						VALUES
						(
							@rId,
							@rActionEmployeeId,
							@rActionStatusId,
							@rIsSequancial,
							@rActionRemark,
							@rActionEmployeeId,
							GETUTCDATE()
						)
						IF(@rIsSequancial = 1)
						BEGIN
							IF(@rWfLevel <> @LogCount AND @rActionStatusId <> 4)
							BEGIN																																										IF(@lActionByEmployeeId > 0)
								INSERT INTO [sWf].[tWfHierarchyLevel]
										(WfApprovalActionId
										,StatusId
										,Remark
										,ActionByEmployeeId
										,ActionDatetime)
									VALUES
										(@rId
										,1
										,''
										,@lActionByEmployeeId
										,sCommon.fGetDate())
							END
							ELSE
							BEGIN
									SELECT @lActionByEmployeeId = EmployeeManagerId FROM [sHrm].[tEmployeeJobDetail] WHERE EmployeeId = @rActionEmployeeId
									DELETE FROM [sWf].[tWfHierarchyLevel] WHERE WfApprovalActionId = @rId AND ActionByEmployeeId = @lActionByEmployeeId AND @lActionByEmployeeId IS NOT NULL
							END
						END
						SELECT @rId AS Result
					END
					ELSE
					BEGIN
						SELECT -101
						SET @IsRejectedExists = 1
					END
				END
			END
			IF EXISTS(SELECT ID FROM sWf.tWfApprovalAction WHERE id = @rId AND ActionStatusId IN(2,3,4) AND @IsRejectedExists = 0)
			BEGIN
				DECLARE @rStoreLocationId BIGINT
				SELECT @rStoreLocationId = ISNULL(ItemValue,0)
				FROM sConfig.tConfigurationSetup
				WHERE ConfigurationId  = 542 AND SourceId = @rUserId

				IF(@rDocSourceTypeId = 2040)
				BEGIN
					UPDATE sPurchase.tPurchaseRequest
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2041)
				BEGIN
					UPDATE sPurchase.tQuotationRequest
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2042)
				BEGIN
					UPDATE sPurchase.tPurchaseOrder
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2043)
				BEGIN
					UPDATE sPurchase.tGoodsReceivedNote
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
				ELSE IF(@rDocSourceTypeId = 2044)
				BEGIN
					UPDATE sPurchase.tSalesReturn
					SET StatusId = @StatusId,
						UpdatedUserId = @rActionEmployeeId,
						UpdatedDateTime = GETUTCDATE()
					WHERE Id = @rDocSourceId AND OrgGroupId = @rOrgGroupId AND LocationId = @rStoreLocationId AND IsObsolete = 0
				END
			END
		END
	COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION


    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	END CATCH
END

GO
--------------------------------------------------------------------------------------------------------------
/****** Object:  StoredProcedure [sWf].[pWfSetupAdd]    Script Date: 21-02-2024 17:44:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sWf].[pWfSetupAdd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [sWf].[pWfSetupAdd]
GO
/****** Object:  StoredProcedure [sWf].[pWfSetupAdd]    Script Date: 21-02-2024 17:44:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[sWf].[pWfSetupAdd]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [sWf].[pWfSetupAdd] AS'
END
GO



ALTER PROCEDURE [sWf].[pWfSetupAdd]
		@rId SMALLINT,
		@rWfTypeMasterId SMALLINT,
		@rWfLevel SMALLINT,
		@rOrgGroupId SMALLINT,
		@rWfFunctionalityId SMALLINT,
        @rAuthorityList sPurchase.ApprovingAuthorityType READONLY
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
		BEGIN


			IF(@rId = 0)
			BEGIN
				IF EXISTS(SELECT WFS.WfFunctionalityId
				FROM [sWf].[tWfSetup] WFS(NOLOCK)
				WHERE WFS.[WfFunctionalityId] = @rWfFunctionalityId AND WFS.OrgGroupId = @rOrgGroupId AND WFS.WfTypeMasterId = @rWfTypeMasterId AND WFS.IsObsolete = 0)
				BEGIN
					SELECT -102
				END
				ELSE
				BEGIN
					IF NOT EXISTS(SELECT WFM.Id
					from [sWf].[tWfFunctionalityMaster] WFM
					WHERE WFM.Id = @rWfFunctionalityId AND WFM.IsObsolete = 0)
					BEGIN
						SELECT -103
					END
					ELSE
					BEGIN
						INSERT INTO [sWf].[tWfSetup]
						(
						WfTypeMasterId,
						WfLevel,
						OrgGroupId,
						WfFunctionalityId
						)
						VALUES
						(
						@rWfTypeMasterId,
						@rWfLevel,
						@rOrgGroupId,
						@rWfFunctionalityId
						)
						SET @rId = CAST(SCOPE_IDENTITY() AS SMALLINT)

						INSERT INTO sWf.tWfApprovingAuthority
						(
						AuthoritySourceTypeId,
						AuthoritySourceId,
						OrgId,
						WfFunctionalityId
						)
						SELECT
						AuthoritySourceTypeId,
						AuthoritySourceId,
						OrgId,
						WfFunctionalityId
						FROM @rAuthorityList

						SELECT @rId
					END
				END
			END
			ELSE
			BEGIN
				IF NOT EXISTS(SELECT AA.Id FROM [sWf].[tWfSetup] WS
							INNER JOIN [sWf].[tWfApprovalAction] AA ON WS.WfFunctionalityId = AA.WfFunctionalityId
							WHERE WS.WfFunctionalityId= @rWfFunctionalityId AND WS.OrgGroupId = @rOrgGroupId AND
							WS.IsObsolete = 0 AND
							(WS.WfTypeMasterId <> @rWfTypeMasterId OR (WS.WfLevel <> 0 AND WS.WfTypeMasterId = 2)) AND AA.ActionStatusId NOT IN (3,4))
				BEGIN
					UPDATE [sWf].[tWfSetup]
					SET
						WfTypeMasterId = @rWfTypeMasterId,
						WfLevel = @rWfLevel,
						OrgGroupId = @rOrgGroupId,
						WfFunctionalityId = @rWfFunctionalityId
					WHERE AutoId =@rId AND OrgGroupId = @rOrgGroupId AND IsObsolete = 0

					DELETE FROM sWf.tWfApprovingAuthority
					WHERE WfFunctionalityId = @rWfFunctionalityId

					INSERT INTO sWf.tWfApprovingAuthority
					(
					AuthoritySourceTypeId,
					AuthoritySourceId,
					OrgId,
					WfFunctionalityId
					)
					SELECT
					AuthoritySourceTypeId,
					AuthoritySourceId,
					OrgId,
					WfFunctionalityId
					FROM @rAuthorityList

					SELECT @rId
				END
				ELSE
				BEGIN
					SELECT -101;
				END
			END
		END
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT -1 AS RESULT

		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SELECT
		   @ErrorMessage = ERROR_MESSAGE(),
		   @ErrorSeverity = ERROR_SEVERITY(),
		   @ErrorState = ERROR_STATE();

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH
END
GO
---------------------------------------------------------------------------------------------------------------
