PRINT N'Creating User-Defined Table Type [sCommonService].[GetStudentDetailType]...';


GO
CREATE TYPE [sCommonService].[GetStudentDetailType] AS TABLE (
    [OrgId]      SMALLINT NOT NULL,
    [AcademicId] BIGINT   NOT NULL);


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


GO
CREATE TABLE [dbo].[Sales] (
    [Product] VARCHAR (50)    NULL,
    [Month]   VARCHAR (10)    NULL,
    [Year]    INT             NULL,
    [Revenue] DECIMAL (10, 2) NULL
);

GO
PRINT N'Altering Procedure [sCommonService].[pMenuGet]...';


GO
-- exec sCommonService.pMenuGet 9911000000270,19184029,0,1,98
-- exec sCommonService.pMenuGet 9911000000270,19184030,0,1,97
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 Not In(31358)
  --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

PRINT N'Altering Procedure [sTimetable].[pAssignTeacherAdd]...';


GO


ALTER PROCEDURE [sTimetable].[pAssignTeacherAdd]
	 
	@rTimeTableSchemaId int,
	@rAssignTeacherXml [nvarchar](max),
	@rOrgId smallint,
	@rEmployeeGroupId int = 0
		
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rAssignTeacherXml	
BEGIN 
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 BEGIN
		
		IF @rEmployeeGroupId = -1
		BEGIN
			SET @rEmployeeGroupId = 0
		END
		--Select @rEmployeeGroupId 
		 DELETE AT FROM sTimetable.tAssignTeacher AT INNER JOIN sHrm.tEmployeeMaster EMP ON EMP.Id = AT.EmployeeId
		 INNER JOIN sHrm.tEmployeeJobDetail EJD ON EMP.Id = EJD.EmployeeId -- ADD join for mantisid 0032498  (need to delete data based on EmployeeGroup wise)
		  WHERE TimeTableSchemaId=@rTimeTableSchemaId AND EMP.OrgID =@rOrgId AND ISNULL(EMP.IsOtherUser,0) = 0  
		  AND ((EJD.EmployeeGroupId = @rEmployeeGroupId) OR (@rEmployeeGroupId = 0))
		  
		 
		INSERT INTO sTimetable.tAssignTeacher
		(
			TimeTableSchemaId,
			EmployeeId
		)
		SELECT
		
		    TimeTableSchemaId ,
	        EmployeeId 
		FROM OPENXML (@loDoc, '/Table/AssignTeacher', 1)
		WITH ( 
				TimeTableSchemaId int,
				EmployeeId bigint
			  ) 
	END
	SELECT 1 AS Result
	
	COMMIT TRANSACTION
	
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebAssignSameLectureToTeacherInSameDivision]...';


GO
ALTER 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 @TimetableId BIGINT; -- Variable to store the generated ID

		        -- Disable row count for the procedure
        SET NOCOUNT ON;

        -- Generate a new ID using sCommon.pTableRowIdGet
        EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sTimetable.tTempSTimeTable', 0, 1, 1, @lNextNumber OUTPUT;

        -- Store the generated ID for later use
        SET @TimetableId = @lNextNumber;

        -- Insert into sTimetable.tTempSTimeTable
        INSERT INTO sTimetable.tTempSTimeTable
        (
            Id,
            TimeTableSchemaId,
            EmployeeId,
            DivisionId,
            SubjectId,
            DayId,
            LectureNumber,
            LockNo,
            LockColour,
            SchoolRoomId,
            IsBreak,
            IsMergeDivision,
            IsBreakBefore
        )
        VALUES 
        (
            @TimetableId, -- Use the stored ID
            @rSchemaId,
            @rEmployeeId,
            @rDivisionId,
            @rSubjectId,
            @rDayId,
            @rLectureNumber,
            2,
            '',
            @rSchoolRoomId,
            0,
            0,
            0
        );

        -- Insert into sTimetable.tTempSTimeTableDetail using the stored ID
        INSERT INTO sTimetable.tTempTimeSTableDetail
        (
            TimetableId,
            EmployeeId,
            SubjectId,
            SchoolRoomId
        )
        VALUES 
        (
            @TimetableId, -- Use the stored ID
            @rEmployeeId,
            @rSubjectId,
            @rSchoolRoomId
        );

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


GO
ALTER PROCEDURE [sTimetable].[pWebErasePeriod]
    @rTableIndex INT,
    @rSchemaId INT,
    @rDivisionId BIGINT,
    @rDayId INT,
    @rLectureNumber INT
AS
BEGIN
    -- Check if the record is locked
    IF EXISTS (
        SELECT 1
        FROM sTimetable.tTempSTimeTable
        WHERE LockNo = 1
            AND TimeTableSchemaId = @rSchemaId
            and DayId=@rDayId and LectureNumber=@rLectureNumber and DivisionId=@rDivisionId
    )
    BEGIN
        -- Record is locked, return -2
        SELECT -2 AS Result
    END
    ELSE
    BEGIN
        -- Record is not locked, update the record
UPDATE sTimetable.tTempSTimeTable
SET EmployeeId = 0, SubjectId = 0
OUTPUT 1 AS Result
WHERE TimeTableSchemaId=@rSchemaId and DayId=@rDayId and LectureNumber=@rLectureNumber and DivisionId=@rDivisionId

    END
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebExchangeLecutres]...';


GO
ALTER PROCEDURE [sTimetable].[pWebExchangeLecutres]
    @rFirstLectureTableIndex int,
    @rSecondLectureTableIndex int,
    @rFirstLectureLectureNumber int,
    @rSecondLectureLectureNumber int,
    @rFirstEmployeeId bigint,
    @rSecondEmployeeId bigint,
    @rFirstSubjectId int,
    @rSecondSubjectId int,
    @rDivisionId int,
    @rSchemaId int,
    @rDayIdOne int,
	@rDayIdTwo int
AS
BEGIN
    SET NOCOUNT ON;  -- Disable "xx rows affected" message

	IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE EmployeeId = @rFirstEmployeeId AND LectureNumber = @rSecondLectureLectureNumber AND TimeTableSchemaId = @rSchemaId AND DayId = @rDayIdTwo)
    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 = @rDayIdOne)
    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 = @rDayIdTwo)
    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 = @rDayIdOne)
    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 = @rDayIdTwo)
    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 = @rDayIdOne)
    BEGIN
        SELECT -7; --Subject Second is at TimeOff
    END
	ELSE IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE LockNo = 1 AND LectureNumber = @rFirstLectureLectureNumber AND TimeTableSchemaId = @rSchemaId AND DayId = @rDayIdTwo)
    BEGIN
        SELECT -8; --Lecture Number 1 is Locked
    END
	ELSE IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE LockNo = -1 AND LectureNumber = @rFirstLectureLectureNumber AND TimeTableSchemaId = @rSchemaId AND DayId = @rDayIdTwo)
    BEGIN
        SELECT -9; --Lecture Number is Blocked
    END 
	ELSE IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE LockNo = 1 AND LectureNumber = @rSecondLectureLectureNumber AND TimeTableSchemaId = @rSchemaId AND DayId = @rDayIdOne)
    BEGIN
        SELECT -10; --Lecture Number 2 is Locked
    END
	ELSE IF EXISTS (SELECT 1 FROM sTimetable.tTempSTimeTable WHERE LockNo = -1 AND LectureNumber = @rSecondLectureLectureNumber AND TimeTableSchemaId = @rSchemaId AND DayId = @rDayIdOne)
    BEGIN
        SELECT -11; --Lecture Number 2 is Blocked
    END 
	ELSE
	BEGIN
		BEGIN TRY
		update stimetable.tTempstimetable
		set EmployeeId=@rSecondEmployeeId,SubjectId=@rSecondSubjectId
		where Id=@rFirstLectureTableIndex

		update stimetable.tTempstimetable
		set EmployeeId=@rFirstEmployeeId,SubjectId=@rFirstSubjectId
		where Id=@rSecondLectureTableIndex
			SELECT 1;
		END TRY
		BEGIN CATCH
			ROLLBACK;
			SELECT -1;
		END CATCH
	END
END;
GO
PRINT N'Altering Procedure [sTimetable].[pWebFetchEmptyPossiblePositions]...';


GO
ALTER PROCEDURE [sTimetable].[pWebFetchEmptyPossiblePositions] --9912000000013,9912000000018,991100022,991200001
	@rFirstEmployeeId bigint,
	@rSecondEmployeeId bigint,
	@rSchemaId int,
	@rDivisionId int
AS
BEGIN
	select
	TST.DayId,
	TST.LectureNumber,
	DM.Code
	from sTimetable.tTempSTimetable TST
	Inner JOIN sCommon.tDayMaster DM ON TST.DayId=DM.Id
    where (TST.EmployeeId!=@rFirstEmployeeId or (@rFirstEmployeeId=0)) and (TST.EmployeeId!=@rSecondEmployeeId or (@rSecondEmployeeId=0)) 
	and TimetableschemaId=@rSchemaId
	 and DivisionId=@rDivisionId
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGenerateTimeTable]...';


GO
--exec [sTimetable].[pWebGenerateTimeTable] 9915,0,991100022
ALTER 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
(
	DivisionId INT,
    Code VARCHAR(255),
    TimeTableSchemaId INT,
    ClassId INT,
    OrderIndex INT,
	OrgId INT,
    DayId INT,
    LectureNumber INT
);

WITH DivisionDetails AS (
    SELECT DM.Id, (OM.CODE + ' :' + DM.Code) AS Code, AO.TimeTableSchemaId, DM.ClassId, DM.OrderIndex,AO.OrgId
    FROM sSetup.tDivisionMaster DM
    INNER JOIN sTimeTable.tAssignOrg AO ON AO.OrgId = DM.OrgId
	INNER jOIN sSetup.tOrganizationMaster OM On AO.OrgId = OM.Id
    WHERE  AO.TimeTableSchemaId = @rSchemaId and  (DM.OrgId = @rOrgId Or (@rOrgId = 0))
),
AllDays AS (
    SELECT DayId, MAX(NoOfPeriod) AS NoOfPeriod
    FROM sTimeTable.tTimesetup
    WHERE TimeTableSchemaId = @rSchemaId
    GROUP BY DayId
)

-- Populate the main temporary table
INSERT INTO @Temp_TimeTable_AlterMainTable
SELECT 
    DivisionDetails.Id as DivisionId,
    DivisionDetails.Code as Code,
    DivisionDetails.TimeTableSchemaId as  TimeTableSchemaId,
    DivisionDetails.ClassId as ClassId,
    DivisionDetails.OrderIndex as OrderIndex,
	DivisionDetails.OrgId as OrgId,
    AllDays.DayId as DayId,
    Number.N as LectureNumber
FROM 
    DivisionDetails
CROSS JOIN 
    AllDays
CROSS JOIN 
    (
        SELECT 1 AS N
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
        UNION ALL
        SELECT 8
		UNION ALL
        SELECT 9
		UNION ALL
        SELECT 10
		UNION ALL
        SELECT 11
		UNION ALL
        SELECT 12
		UNION ALL
        SELECT 13
		UNION ALL
        SELECT 14
		UNION ALL
        SELECT 15
		UNION ALL
        SELECT 16
		UNION ALL
        SELECT 17
		UNION ALL
        SELECT 18
		UNION ALL
        SELECT 19
		UNION ALL
        SELECT 20
		UNION ALL
        SELECT 21
		UNION ALL
        SELECT 21
		UNION ALL
        SELECT 23
		UNION ALL
        SELECT 24
		UNION ALL
        SELECT 25
		UNION ALL
        SELECT 26
		UNION ALL
        SELECT 27
		UNION ALL
        SELECT 28
		UNION ALL
        SELECT 29
		UNION ALL
        SELECT 30

    ) AS Number
WHERE 
    Number.N <= AllDays.NoOfPeriod
ORDER BY 
    DivisionDetails.Id,
    AllDays.DayId,
    Number.N;


-- 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  OrderIndex, DivisionId) AS RowNum,
    TimeTableSchemaId,
    0 AS EmployeeId,
    DivisionId,
    0 AS SubjectId,
    DayId,
    LectureNumber,
    0 AS LockNo,
    0 AS LockColour,
    0 AS SchoolRoom,
    0 AS Isbreack,
    0 AS IsMergeDivision,
	0 as IsBreakBefore
FROM 
    @Temp_TimeTable_AlterMainTable o


-- 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
)


update sTimetable.tTempSTimeTable set LockColour = '#FFFF00' where Lecturenumber = 1

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'

----------------------------------result data for grid------------------------------------------------------------
	--select * from sTimetable.tTempSTimeTable where TimeTableSchemaId = @rSchemaId
	select  TTT.* , isnull((EM.FirstName + ' ' + EM.LastName), 0 ) as  EmployeeName ,  isnull (SM.Code,0) as SubjectName from sTimetable.tTempSTimeTable TTT left join  [sHrm].[tEmployeeMaster] EM on EM.Id = TTT.EmployeeId 
left join [sSetup].[tSubjectMaster] SM on SM.Id  = TTT.SubjectId where TimeTableSchemaId = @rSchemaId


	-------------------------------------------------------------division header ---------------------------------------------------------
	select distinct DivisionId,Code,OrderIndex,OrgId From @Temp_TimeTable_AlterMainTable 
	order by OrderIndex


	--select d.Id,D.Code,D.OrgId from sSetup.tDivisionMaster D
	--inner join sTimetable.TtEMPsTIMETABLE tst ON tst.DivisIonId=D.Id
	--where TST.TimeTableSchemaId=@rSchemaId
	--Group by D.Id,D.Code,D.orgId
	--order by D.Id asc
	------------------------------------------------------------DayId and Lecturenumber for RowsTitle-----------------------------------------------------------------------------------
	SELECT DayId, MAX(NoOfPeriod) AS NoOfPeriod
    FROM sTimeTable.tTimesetup
    WHERE TimeTableSchemaId = @rSchemaId
    GROUP BY DayId
END;
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetDivisionWorkLoadbyDivisionId]...';


GO
ALTER 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
                                           AND dw.SubjectId = tw.subjectid -- This condition ensures only teacher workloads for the subjects taught in the division are considered
										   AND dw.TimeTableSchemaId=tw.TimeTableSchemaId
    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.TimetableSchemaId=@rSchemaId;
end
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetDropdownOnClassChange]...';


GO
ALTER PROCEDURE [sTimetable].[pWebGetDropdownOnClassChange]
(
@rOrgId int,
@rSchemaId int,
@rClassId int
)
AS
BEGIN
		--Division
		SELECT D.Id AS DivisionId,D.DisplayTitle AS DivisionTitle FROM sSetup.tDivisionMaster D INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = D.OrgId INNER JOIN sTimetable.tAssignOrg AO ON d.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId AND D.ClassId = @rClassId;
		--Subject
		SELECT S.Id AS SubjectId, S.Title AS SubjectTitle
		FROM sSetup.tSubjectMaster S
		INNER JOIN sTimetable.tDivisionWorkload DW ON S.Id = DW.SubjectId
		INNER JOIN sSetup.tDivisionMaster D ON DW.DivisionId = D.ID
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = S.OrgId 
		INNER JOIN sTimetable.tAssignOrg AO ON S.OrgId = AO.OrgId 
		WHERE AO.TimeTableSchemaId = @rSchemaId
		AND AO.OrgId = @rOrgId 
		AND D.ClassId = @rClassId
		GROUP BY S.Id, S.Title;
		--Teacher
		SELECT T.Id AS TeacherId,T.ShortName AS TeacherTitle 
		FROM sHrm.tEmployeeMaster T
		INNER JOIN sTimetable.tTeacherworkload TW ON t.Id = Tw.EmployeeId
		INNER JOIN sSetup.tDivisionMaster D ON TW.DivisionId = D.ID
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = T.OrgId 
		INNER JOIN sTimetable.tAssignOrg AO ON T.OrgId = AO.OrgId 
		WHERE AO.TimeTableSchemaId = @rSchemaId
		AND AO.OrgId = @rOrgId 
		AND D.ClassId = @rClassId
		group by T.Id, t.ShortName
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetDropdownOnDivisionChange]...';


GO
ALTER PROCEDURE [sTimetable].[pWebGetDropdownOnDivisionChange](
	@rOrgId int,
	@rSchemaId int,
	@rClassId int,
	@rDivisionId int
)
AS
BEGIN
		--Subject
		SELECT S.Id AS SubjectId, S.Title AS SubjectTitle
		FROM sSetup.tSubjectMaster S
		INNER JOIN sTimetable.tDivisionWorkload DW ON S.Id = DW.SubjectId
		INNER JOIN sSetup.tDivisionMaster D ON DW.DivisionId = D.ID
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = S.OrgId 
		INNER JOIN sTimetable.tAssignOrg AO ON S.OrgId = AO.OrgId 
		WHERE AO.TimeTableSchemaId = @rSchemaId
		AND AO.OrgId = @rOrgId 
		AND D.Id=@rDivisionId
		AND D.ClassId = @rClassId
		GROUP BY S.Id, S.Title;
		--Teacher

		SELECT T.Id AS TeacherId,T.ShortName AS TeacherTitle 
		FROM sHrm.tEmployeeMaster T
		INNER JOIN sTimetable.tTeacherworkload TW ON t.Id = Tw.EmployeeId
		INNER JOIN sSetup.tDivisionMaster D ON TW.DivisionId = D.ID
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = T.OrgId 
		INNER JOIN sTimetable.tAssignOrg AO ON T.OrgId = AO.OrgId 
		WHERE AO.TimeTableSchemaId = @rSchemaId
		AND AO.OrgId = @rOrgId 
		AND D.ClassId = @rClassId
		group by T.Id, t.ShortName

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


GO
ALTER PROCEDURE [sTimetable].[pWebGetDropdownOnOrganizationChange] 
(@rOrgId int,
@rSchemaId int
)
AS
BEGIN
		--Class DropDown
		SELECT DISTINCT CR.Id AS ClassId,CR.Title + '-' + STR(CR.OrgId) AS ClassTitle FROM sSetup.tClassMaster CR INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Subject Dropdown
		SELECT DISTINCT S.Id AS SubjectId, S.Title AS SubjectTitle FROM sSetup.tSubjectMaster S INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = S.OrgId INNER JOIN sTimetable.tAssignOrg AO ON S.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Division Dropwdown
		SELECT DISTINCT D.Id AS DivisionId, D.DisplayTitle AS DivisionTitle FROM sSetup.tDivisionMaster D INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = D.OrgId INNER JOIN sTimetable.tAssignOrg AO ON D.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId;
		--Teachers Dropdown
		SELECT DISTINCT T.ID AS TeacherId, T.ShortName  AS TeacherTitle FROM shRM.TEMPLOYEEMASTER T INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = t.OrgID INNER JOIN sTimetable.tAssignOrg AO ON t.orgid = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId AND AO.OrgId = @rOrgId 	group by T.Id, t.ShortName
	
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetdropdownTimetableShemachange]...';


GO
ALTER PROCEDURE [sTimetable].[pWebGetdropdownTimetableShemachange] --991100022
@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.OrgId) AS ClassTitle FROM sSetup.tClassMaster CR INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CR.OrgId INNER JOIN sTimetable.tAssignOrg AO ON CR.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId;
		--Subject Dropdown
		SELECT DISTINCT S.Id AS SubjectId, S.Title AS SubjectTitle FROM sSetup.tSubjectMaster S INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = S.OrgId INNER JOIN sTimetable.tAssignOrg AO ON S.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId 
		--Division Dropwdown
		SELECT DISTINCT D.Id AS DivisionId, D.DisplayTitle AS DivisionTitle FROM sSetup.tDivisionMaster D INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = D.OrgId  INNER JOIN sTimetable.tAssignOrg AO ON D.OrgId = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId;
		--Teachers Dropdown
		SELECT DISTINCT T.ID AS TeacherId, T.ShortName  AS TeacherTitle FROM shRM.TEMPLOYEEMASTER T INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = t.OrgID INNER JOIN sTimetable.tAssignOrg AO ON t.orgid = AO.OrgId WHERE AO.TimeTableSchemaId = @rSchemaId 	group by T.Id, t.ShortName
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetSuggestedSubjectList]...';


GO
ALTER PROCEDURE [sTimetable].[pWebGetSuggestedSubjectList] --991100022,0,0,0,991200001,0,0,0
	@rSchemaId INT,
	@rOrgId INT,
	@rClassId INT,
	@rDivisionId INT,
	@rSubjectId INT,
	@rEmployeeId BIGINT,
	@rFromFilter INT,
	@rToFilter INT
AS
BEGIN
    SELECT
        TW.SubjectId,
        S.Title AS Subject,
        D.Code AS Division,
        D.Id AS DivisionId,
        T.ShortName AS Teacher,
        T.Id AS TeacherId,
        (T.FirstName +' - '+ S.Title) AS Code,
        TW.NoOfLecture AS Total,
        (TW.NoOfLecture - (
            SELECT COUNT(LectureNumber)
            FROM sTimetable.tTempSTimeTable
            WHERE  DivisionId = TW.DivisionId 
            AND TimeTableSchemaId = TW.TimeTableSchemaId  AND EmployeeId=T.Id and SubjectId = TW.SubjectId
        )) AS Remain

		
    FROM
        sTimetable.tTeacherWorkLoad TW
    INNER JOIN
        sHrm.tEmployeeMaster T ON TW.EmployeeId = T.Id
    INNER JOIN
        sSetup.tSubjectMaster S ON S.Id = TW.SubjectId
    INNER JOIN
        sSetup.tDivisionMaster D ON D.Id = TW.DivisionId
    INNER JOIN
        sTimetable.tTempSTimeTable TT ON TT.DivisionId = TW.DivisionId
    INNER JOIN 
        sTimetable.tAssignOrg AO ON AO.TimeTableSchemaId = TT.TimeTableSchemaId
    WHERE
        TW.TimeTableSchemaId = @rSchemaId 
        AND (D.OrgId=@rOrgId OR @rOrgId = 0)
        AND (TW.SubjectId=@rSubjectId OR @rSubjectId = 0)
        AND (D.ClassId= @rClassId OR @rClassId = 0)
        AND (Tw.DivisionId = @rDivisionId OR @rDivisionId = 0)
        AND (TW.EmployeeId=@rEmployeeId OR @rEmployeeId = 0)
        AND (TT.LectureNumber>@rFromFilter OR @rFromFilter=0)
        AND (TT.LectureNumber<@rToFilter OR @rToFilter=0)
    GROUP BY
		tw.Timetableschemaid,
		tw.DivisionId,
        TW.SubjectId,
        S.Title,
        D.Code,
        D.Id,
        T.ShortName,
        T.Id,
        (T.FirstName +' - '+ S.Title),
        TW.NoOfLecture;
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebLockperiod]...';


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

        -- Perform the update operation
          update sTimeTable.tTempSTimeTable set LockNo = @rLockNo , LockColour = '#800080'
		  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'Altering Procedure [sTimetable].[PWebSetRemainDivisionGet]...';


GO

ALTER PROCEDURE [sTimetable].[PWebSetRemainDivisionGet] 
    @rSchemaId INT,
    @rDivisionId INT,
    @rOrgId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        TST.DivisionId,
		TST.DayId,
		TST.LectureNumber,
        D.Code,
        TST.SubjectId,
        S.Code,
        TST.EmployeeId,
        EM.ShortName,
		D.OrgId,
		TST.LockColour,
		DM.Code as DayCode,
		TST.Id
    FROM 
        sTimetable.tTempStimetable TST 
    Left JOIN 
        sSetup.tDivisionMaster D ON D.Id = TST.DivisionId
    Left JOIN 
        sSETUP.tSubjectMaster S ON S.Id = TST.SubjectId
    Left JOIN 
        sHRM.TemployeeMaster EM ON EM.ID = TST.EMPLOYEEID
	Left Join
		sCommon.tDayMaster  DM ON DM.Id=tst.DayId
    WHERE 
        TST.TimeTableSchemaId = @rSchemaId 
        AND TST.DivisionId = @rDivisionId 
        AND D.Id = @rDivisionId 
        AND D.OrgId = @rOrgId;
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebTimeOffLecture]...';


GO
ALTER PROCEDURE [sTimetable].[pWebTimeOffLecture]  
	@rSchemaId int,
	@rDivisionId int,
	@rDayId tinyint,
	@rLeactureNumber int,
	@rSubjectId int,
	@rEmployeeId bigint,
	@rSchoolRoomId int = 0,
	@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

	set @Return = 0 -- default select 

END
GO
PRINT N'Altering Procedure [sTimetable].[pWebUnlockperiod]...';


GO
ALTER 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 , 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
        -- 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'Altering Procedure [sTimetable].[pWebUnSetColor]...';


GO
ALTER 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 and TTT.LockNo != 1
        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'Altering Procedure [sTransport].[pVehicleDocumentsGet]...';


GO

CREATE PROCEDURE [sCommonService].[pClassGet]
	@rDepartmentId int,
	@rOrgId int,
	@rUserId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
		SELECT DISTINCT CM.Id,CM.DisplayTitle AS Title FROM sSetup.tDivisionMaster DM
                               INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId AND CM.OrgId=  @rOrgId 
                               INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id AND U.SourceTypeId=2005
                               WHERE  CM.DepartmentId= @rDepartmentId  AND U.UserId = @rUserId
	END TRY

	BEGIN CATCH
	 SELECT -1 as Result
	END CATCH

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


GO
PRINT N'Creating Procedure [sCommonService].[pClassGet]...';


GO




CREATE PROCEDURE [sCommonService].[pDepartmentGet]
		@rOrgId int,
		@rUserId bigint 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT 
		DM.Id, 
		DM.DisplayTitle AS Title 
		FROM sSetup.tDepartmentMaster DM 
		INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id 
		AND U.SourceTypeId=2017
        WHERE IsObsolete = 0 
		AND OrgId = @rOrgId  
		AND UserId = @rUserId
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sCommonService].[pDivisionGet]...';


GO


CREATE PROCEDURE [sCommonService].[pDivisionGet]
	 
	 @rClassId int,
	 @rUserSourceId bigint,
	 @rUserSourceTypeId int,
	 @rOrgId int,
	 @rUserId bigint,
	 @rBatchId int,
	 @rAttendanceEntryBasedOn nvarchar
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	set @rAttendanceEntryBasedOn = 'User Access'
	BEGIN TRY
        if (@rAttendanceEntryBasedOn = 'Class Teacher' AND @rUserSourceTypeId = 95)
			BEGIN
				SELECT DISTINCT DM.Id AS Id, DM.DisplayTitle AS Title
					   FROM sSetup.tClassMaster CM 
					   INNER JOIN sSetup.tDivisionMaster DM ON DM.ClassId = CM.Id
                       INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=CM.OrgId
                       WHERE DM.InchargeemployeeID= @rUserSourceId  
					   AND CM.OrgId= @rOrgId 
					   AND CM.Id = @rClassId
			END	 
		else if (@rAttendanceEntryBasedOn = 'Academic Setup' AND @rUserSourceTypeId = 95)
				BEGIN
					SELECT DISTINCT DivisionId AS Id, DivisionTitle, DivisionOrderIndex AS Title 
                        FROM sAcademic.vTeacherSubjectDivision TSD 
						INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=TSD.OrgId 
						WHERE  TSD.EmployeeId= @rUserSourceId 
						AND TSD.ClassId = @rClassId
                        ORDER BY DivisionOrderIndex
				END
		else
					BEGIN
						SELECT DM.Id,DM.DisplayTitle AS Title 
							   FROM sSetup.tDivisionMaster DM 
                               INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId AND CM.OrgId= @rOrgId 
                               INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id AND U.SourceTypeId=2005 
                               WHERE  CM.Id = @rClassId 
							   AND UserId = @rUserId 
							   Order by DM.OrderIndex
					END
	END TRY

	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Creating Procedure [sCommonService].[pEmployeeDivisionClassDepartmentGet]...';


GO


CREATE PROCEDURE [sCommonService].[pEmployeeDivisionClassDepartmentGet]
	 
	 @rEmployeeIds nvarchar(max),
	 @rClassIds nvarchar(max),
	 @rDepartmentIds nvarchar(max),
	 @rDivisionIds nvarchar(max),
	 @rOrgId int
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	BEGIN TRY
		
		SET @rEmployeeIds = REPLACE(@rEmployeeIds, ',', ''',''');
		SET @rDepartmentIds = REPLACE(@rDepartmentIds, ',', ''',''');
		SET @rClassIds = REPLACE(@rClassIds, ',', ''',''');
		SET @rDivisionIds = REPLACE(@rDivisionIds, ',', ''',''');
		DECLARE @sql NVARCHAR(MAX),
		@OrgId NVARCHAR(10);
		SET @OrgId = CONVERT(NVARCHAR(50), @rOrgId);
		SET @sql = 'SELECT EmployeeId AS Id,
				EmployeeName AS Title ,
				Code
				FROM sHrm.vEmployeeCommunication 
				WHERE ISNULL(IsOtherUser,0) = 0 
				AND StatusId=7 
				AND Id in (''' + @rEmployeeIds + ''')
				AND OrgId='''+ @OrgId + ''' ORDER BY Code
	
				SELECT 
				Distinct DM.Id, 
				DM.DisplayTitle AS Title 
				FROM sSetup.tDepartmentMaster DM 
				INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id 
				AND U.SourceTypeId=2017
				WHERE DM.IsObsolete = 0 
				AND OrgId = '''+ @OrgId + '''  
				AND DM.Id in (''' + @rDepartmentIds + ''')
		
				SELECT DISTINCT 
				CM.Id,
				CM.DisplayTitle AS Title 
				FROM sSetup.tDivisionMaster DM
				INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId AND CM.OrgId=  '''+ @OrgId + ''' 
				INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id AND U.SourceTypeId=2005
				WHERE  CM.Id in (''' + @rClassIds + ''') AND DM.IsObsolete = 0 

				SELECT DM.Id,DM.DisplayTitle AS Title 
				FROM sSetup.tDivisionMaster DM 
				INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=DM.OrgId
				WHERE DM.Id in (''' + @rDivisionIds + ''') and DM.OrgId = '''+ @OrgId + '''';

		EXEC sp_executesql @sql;
	END TRY
	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Creating Procedure [sCommonService].[pEmployeeGet]...';


GO


CREATE PROCEDURE [sCommonService].[pEmployeeGet]
	 
	 @rDepartmentId int,
	 @rOrgId int
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	BEGIN TRY
	SELECT EmployeeId AS Id,
	EmployeeName AS Title ,
	Code
	FROM sHrm.vEmployeeCommunication 
	WHERE ISNULL(IsOtherUser,0) = 0 
	AND StatusId=7 
	AND DepartmentId=@rDepartmentId 
	AND OrgId=@rOrgId ORDER BY Code
	END TRY

	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Creating Procedure [sCommonService].[pGetStudentDetail]...';


GO
CREATE PROCEDURE [sCommonService].[pGetStudentDetail]
	@StudentDetail [sCommonService].[GetStudentDetailType] READONLY,
	@AssigneeToId tinyint = 2

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
		SELECT
			2003 as SourceTypeId,
			SM.Id as SourceId,
			[sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Source,
			'Student' AS SourceType,
			SA.RollNo,
			SA.Code,
			SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
			OM.DisplayTitle AS OrgName,
			AM.Id as AssigneeToId,
			AM.Title As AssignedTo
			FROM sStudent.tStudentMaster SM 
			INNER JOIN  sStudent.vStudentAcademic SA ON SM.Id=SA.StudentId AND SA.IsActiveBatch= 1 AND SA.StatusId= 1 AND SA.OrgID IN (SELECT OrgId FROM @StudentDetail) 
			INNER JOIN sSetup.tOrganizationMaster OM ON SM.OrgId = OM.Id
			left JOIN sCommon.tAssigneeMaster AM ON AM.Id = @AssigneeToId where SM.Id IN (SELECT AcademicId FROM @StudentDetail)
	END TRY

	BEGIN CATCH
	 SELECT -1 as Result
	END CATCH

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


GO

CREATE PROCEDURE [sCommonService].[pUpdateZoomMeetingAccountToken]
	@rAccessToken nvarchar(max),
	@rZoomMeetingAccountId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			UPDATE sAcademic.tOnlineMeetingAccount SET JWTToken = @rAccessToken Where Id = @rZoomMeetingAccountId AND MeetingProviderId = 1
			SELECT 1 as Result
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sCommonService].[pUserDetailsGet]...';


GO


--exec [sCommonService].[pUserDetailsGet] 9911, '9911000000545, 9911000000003, 9911000000270'
CREATE PROCEDURE [sCommonService].[pUserDetailsGet]
	@rOrgId smallint,
	@rUserId nvarchar(max)

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
		SET @rUserId = REPLACE(@rUserId, ',', ''',''');
		DECLARE @sql NVARCHAR(MAX),
		@OrgId NVARCHAR(10);
		SET @OrgId = CONVERT(NVARCHAR(50), @rOrgId);
		SET @sql = 'SELECT distinct
		UserTbl.Id AS Id, 		
		UserTbl.LoginTitle,
		UserTbl.DisplayTitle as Title,		 
		UserTbl.SourceId AS UserSourceId,
		UserTbl.SourceTypeId AS UserSourceTypeId
		--,
		--CASE WHEN UserTbl.SourceTypeId = 95 THEN
		--	(EM.FirstName +'' ''+ ISNULL(EM.MiddleName,'') + '' '' + EM.LastName)
		--ELSE
		--	SA.Name
		--END   AS EmployeeFullName,
		--CASE WHEN UserTbl.SourceTypeId = 95 THEN
		--	(EM.FirstName + ISNULL('' '' + EM.MiddleName, '') + '' '' + EM.LastName)
		--ELSE
		--	SA.Name
		--END   AS UserFullName
					
		FROM sSecurity.tUser UserTbl
			INNER JOIN sSecurity.tUserRole UR
				ON UR.UserId=UserTbl.Id AND UR.IsObsolete=0 AND UR.IsPrimary=1 AND UR.UserId=UserTbl.Id	
			LEFT JOIN sHrm.vExtEmployeeMaster EM
				ON EM.Id = UserTbl.SourceId 
				--AND UserTbl.OrgId = EM.OrgId 
				AND UserTbl.SourceTypeId = 95 
				--AND EM.StatusId !=9
			LEFT JOIN sStudent.vStudentAcademic SA
				ON SA.StudentId = UserTbl.SourceId 
				--AND UserTbl.OrgId = SA.OrgId 
				--AND SA.BatchId=@rBatchId 
				AND UserTbl.SourceTypeId = 2003	
			LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
		WHERE 
			UserTbl.Id in ('''+ @rUserId +''')
			AND UserTbl.IsObsolete=0
			AND ((EM.StatusId = 7 AND UserTbl.SourceTypeId = 95) OR (SA.StatusId = 1 AND UserTbl.SourceTypeId = 2003) OR (UserTbl.SourceTypeId=1) OR (ISNULL(UserTbl.SourceTypeId,0) = 0))'

		EXEC sp_executesql @sql;
	END TRY

	BEGIN CATCH
	 SELECT -1 as Result
	END CATCH

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


GO
CREATE PROCEDURE [sCommonService].[pZoomEmployeeSearchGet]
	@rOrgId [int],
	@rEmployeeCode [nvarchar](20) = '',
	@rFirstName [nvarchar](30) = '',
	@rMobileNumber [nvarchar](20) = '',
	@rDOB [datetime] = '1/1/1900',
	--@rEmployeeLocationId int,
	@rLastName [nvarchar](30) = '',
	@rParticipantSourceTypeId int = 0,
	@rDepartmentId int = 0,
	@rEmployeeId bigint = 0,
	@rStatusId smallint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
 --SET @rEmployeeCode = sCommon.fSearchStringGet(@rEmployeeCode)
IF(@rParticipantSourceTypeId = 95 OR @rParticipantSourceTypeId = 0 )
BEGIN
SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
SET @rLastName = sCommon.fSearchStringGet(@rLastName)
 SET @rMobileNumber = sCommon.fSearchStringGet(@rMobileNumber) 
 
 	 SELECT OrgID,
			EM.Id,
			EM.EmployeeId,
			Code,
			FirstName,
			MiddleName,
			LastName,
		    EmployeeName,	 			
			GenderId,
			ISNULL(DateOfBirth,'1900/01/01') AS DateOfBirth,
			ISNULL(DateOfJoining,'1900/01/01') AS DateOfJoining,
			'' AS Gender,
			--PermanentAddressId,
			--PresentAddressId AS CommunicationAddressId,
			--PresentContactId AS CommunicationContactId,
			StatusId,
			EM.IsObsolete,
			PhoneNoCompany,
			CompanyEmail,
			ExtensionNo,			
			DepartmentId,  
			DepartmentCode,  
			DepartmentName,
		    ISNULL (MobileNumber,0) AS MobileNumber,
			--EmployeeJobTitleId,
			--EmployeeTypeId,
			--EmployeeJobTitle,
			--EmployeeLocationId,
			ISNULL(DATEDIFF(yy,DateOfBirth,sCommon.fGetDate()),0) AS Age,
			DesignationId,
			--ProcessTypeId,
			ParticipantSourceTypeId
			
 
	 FROM sHrm.vExtEmployeeMaster EM 
	 INNER JOIN sAcademic.tOnlineMeetingAccount OA 
	 On OA.EmployeeId = EM.EmployeeId 
		 WHERE 
		      ((EM.Code = @rEmployeeCode) OR (ISNULL(@rEmployeeCode ,'')=''))
			  AND ((EM.FirstName Like  @rFirstName)  OR (ISNULL(@rFirstName,'') = ''))
			  AND ((EM.LastName Like  @rLastName)  OR (ISNULL(@rLastName,'') = ''))
			  AND ((EM.MobileNumber Like @rMobileNumber) OR(ISNULL(@rMobileNumber,'') = ''))				 
			  AND EM.IsObsolete = 0
			  AND OA.IsObsolete = 0
			  --AND EM.EmployeeGroupId=1
			  AND ((EM.OrgId = @rOrgId) OR (@rOrgId = 0))
			  --AND ((EM.EmployeeLocationId = @rEmployeeLocationId) OR (ISNULL(@rEmployeeLocationId,0) = 0) )		  			  
			  AND ((EM.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0) )	
			  AND ((EM.StatusId=@rStatusId) OR (@rStatusId=0))
			  AND ISNULL(EM.IsOtherUser,0) = 0 	 
		END
		
   ELSE IF(@rParticipantSourceTypeId = 1001) 
	  BEGIN
	  SELECT
		 OrgId,
	     DependentId,
	     EmployeeCode AS Code,
	     Ef.EmployeeId AS Id,
	     Name AS EmployeeName,
	     DateOfBirth,
	     AGE,
	     0 AS GenderId,
	     DepartmentId,
	     DepartmentName,
	     0 AS MobileNumber,
	     DesignationId,
	     '' AS FirstName,
	     '' AS LastName,
	     1001 AS ParticipantSourceTypeId
	     
	     FROM sHrm.vExtEmployeeFamilyDetail Ef INNER JOIN sAcademic.tOnlineMeetingAccount OA 
	 On Ef.EmployeeId = OA.EmployeeId  Where 
	      (EmployeeCode Like @rEmployeeCode) AND DependentId = @rEmployeeId AND OA.IsObsolete = 0
			   	AND OrgId = @rOrgId  AND ISNULL(IsOtherUser,0) = 0 	 
			 
			   	--AND EmployeeLocationId = @rEmployeeLocationId 
	      
	  END	
		
   --ELSE IF(@rParticipantSourceTypeId = 1009)       
   --    BEGIN 
   --      SELECT 
	  --      OrgId,
	  --      EmployeeId AS Id,     
	  --      EmployeeId,
			--Code,
			--FirstName,	 
			--LastName,
			--EmployeeName,
			--GenderId,
			--ISNULL(DateOfBirth,'') AS DateOfBirth, 
			--'' AS DepartmentName,
			--1009 AS ParticipantSourceTypeId,		 			 
			--0 AS  DepartmentId,	 
			--0 AS DesignationId,	 		
			--ISNULL(DATEDIFF(yy,DateOfBirth,GETDATE()),0) AS Age
			----0 AS ProcessTypeId
			--,OrgId
			----,LocationId
			--,'' AS MobileNumber 
	       
			
	  --FROM  sHrm.vExtCsrBenefiterMaster          
   --     WHERE   (Code Like @rEmployeeCode)
			--   	AND OrgId = @rOrgId 
			--   	--AND LocationId = @rEmployeeLocationId
 		-- END
END
GO
PRINT N'Creating Procedure [sCommonService].[pZoomMeetingAccountDetailsGet]...';


GO
CREATE PROCEDURE [sCommonService].[pZoomMeetingAccountDetailsGet]
	@rEmployeeId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT Id,EmployeeId, APIKey, APISecret,OAuthAccountId,OAuthClientId,OAuthClientSecret,AccountEmailId,JWTToken FROM sAcademic.tOnlineMeetingAccount WHERE EmployeeId = @rEmployeeId AND ISNULL(IsObsolete,0) = 0
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sTimetable].[pSyncTimeTable]...';


GO
CREATE PROCEDURE sTimeTable.pSyncTimeTable
@rOrgGroupId int,
@rSchemaId int			
AS
BEGIN
	SET NOCOUNT ON;
	  DECLARE @lNextNumber bigint; 
	BEGIN TRY
	begin
    EXECUTE sCommon.pTableRowIdGet 9915, 'sTimetable.tTimeTable', 0, 1, 1, @outNextNumber = @lNextNumber OUTPUT

	Delete from sTimetable.tTimeTableDetail WHERE TimeTableId IN(select id From sTimeTable.tTimeTable Where TimeTableSchemaId = @rSchemaId)
	Delete from sTimetable.tTimeTable WHERE TimeTableSchemaId = @rSchemaId
	

INSERT INTO sTimetable.tTimeTable
    (Id, TimeTableSchemaId, EmployeeId, DivisionId, SubjectId, DayId, LectureNumber, LockColour, SchoolRoomId, IsBreak, IsMergeDivision, IsBreakBefore)
SELECT 
    @lNextNumber +(ROW_NUMBER() OVER (ORDER BY Id)), 
    TimeTableSchemaId, 
    EmployeeId, 
    DivisionId, 
    SubjectId, 
    DayId, 
    LectureNumber, 
    LockColour, 
    SchoolRoomId, 
    IsBreak, 
    IsMergeDivision, 
    IsBreakBefore 
FROM 
    sTimetable.tTempSTimeTable 
WHERE 
    TimeTableSchemaId = @rSchemaId

update sCommon.tTableRowId set CurrentNumber =(select max(id) from sTimetable.tTimeTable where TimeTableSchemaId = @rSchemaId) where TableTitle = 'sTimetable.tTimeTable' and OrgId = @rOrgGroupId

Insert into  sTimetable.tTimeTableDetail
(TimetableId , EmployeeId, SubjectId, SchoolRoomId)

SELECT 
    Id, 
    EmployeeId, 
    SubjectId, 
    SchoolRoomId
FROM 
    sTimetable.tTimeTable AS t
WHERE 
    TimeTableSchemaId = @rSchemaId
    AND LectureNumber IN (
       SELECT  LectureNumber
        FROM sTimetable.tTempSTimeTable
        WHERE TimeTableSchemaId = @rSchemaId
        GROUP BY DayId, LectureNumber,DivisionId 
        HAVING COUNT(*) > 1)
		and DayId IN( SELECT DayId
        FROM sTimetable.tTempSTimeTable
        WHERE TimeTableSchemaId = @rSchemaId
        GROUP BY DayId, LectureNumber,DivisionId 
        HAVING COUNT(*) > 1)
		and DivisionId in( SELECT DivisionId
        FROM sTimetable.tTempSTimeTable
        WHERE TimeTableSchemaId = @rSchemaId
        GROUP BY DayId, LectureNumber,DivisionId 
        HAVING COUNT(*) > 1)

		SELECT 1 AS Result
		end
		 END TRY
    BEGIN CATCH
        -- If an error occurs, return 0
        SELECT -101 AS Result
    END CATCH;
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebShowForSetRemain]...';


GO
CREATE PROCEDURE sTimetable.pWebShowForSetRemain --991100022,2,2
    @rSchemaId INT,
    @rDayId INT,
    @rLectureNumber INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        TST.DivisionId,
        D.Code,
		D.OrgId
    FROM 
        sTimetable.tTempStimetable TST 
    INNER JOIN 
        sSetup.tDivisionMaster D ON D.Id = TST.DivisionId
    WHERE 
        TST.TimeTableSchemaId = @rSchemaId 
        AND SubjectId = 0
        AND EmployeeId = 0
        AND LectureNumber = @rLectureNumber
    GROUP BY 
        TST.DivisionId, D.Code,D.orgId; -- You need to include D.Code in the GROUP BY clause
END
GO
PRINT N'Creating Procedure [sTimetable].[pWebTimetablebyschemaTest]...';


GO

CREATE PROCEDURE sTimetable.pWebTimetablebyschemaTest
	@rSchemaId int,
	@rOrgId int

AS
BEGIN
select Id,LectureNumber from sTimetable.tTempSTimetable
END
GO

PRINT N'Altering Procedure [sTimetable].[pWebDragAndDrop]...';


GO
ALTER PROCEDURE [sTimetable].[pWebDragAndDrop]
	--@rOrgGroupId int,
	@rOrgId int,
	@rSchemaId int,
	@rDivisionId int,
	@rDayId int,
	@rLectureNo int,
	@rTeacherId bigint,
	@rSubjectId int,
	@rClassId int= 0,
	@rLockNo int = 0,
	@rLockColour nvarchar(50) ='',
	@rSchoolRoomId int = 0,
	@rIsBreak bit = 0,
	@rIsMergeDivision bit = 0,
	@rIsBreakBefore bit = 0
AS
BEGIN
		 BEGIN TRY
        BEGIN TRANSACTION; 
	SET NOCOUNT ON;
	-------NEw changes
		DECLARE @WorkloadData TABLE (
        SubjectId INT,
        Subject NVARCHAR(200),
		Division NVARCHAR(200),
		DivisionId INT,
		Teacher NVARCHAR(200),
		TeacherId BIGINT,
		Code NVARCHAR(500),
		Total int,
		Remain int
		);
		DECLARE @count int
		DECLARE @timeofflecturecount int


			INSERT INTO @WorkloadData (SubjectId, Subject,Division , DivisionId ,Teacher,TeacherId,Code,Total,Remain)
			EXEC [sTimetable].[pWebGetSuggestedSubjectList] @rSchemaId , @rOrgId ,@rClassId,@rDivisionId, @rSubjectId, @rTeacherId,0,0

			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 
			AND TTT.EmployeeId >0)
				 BEGIN 
				  
					select 2 As Result -- Period is Busy
				 END
				ELSE
				BEGIN
					IF ((select Remain FROM @WorkloadData) <= 0 )
					BEGIN
						-- logc check for  get remian from workload
						select 3 as Result-- All Lecture Given For Teacher
					END
					ELSE
					BEGIN

						IF((select DivisionId  FROM @WorkloadData) != @rDivisionId)
						BEGIN
							-- check division id with the  division drag and drop
							select 7 as Result-- Wrong Division ! Check The Division
						END
						ELSE
						BEGIN
							set @count = (SELECT COUNT(*) from sTimeTable.tTempSTimeTable WHERE 
							EmployeeId = @rTeacherId AND
							DayId = @rDayId AND LectureNumber = @rLectureNo)

							IF(@count > 0 )
							BEGIN
								select 1 as Result -- Employee is Engaged
							END
							ELSE
							BEGIN
								EXEC [sTimetable].[pWebTimeOffLecture] @rSchemaId,@rDivisionId,@rDayId,@rLectureNo,@rSubjectId,@rTeacherId,@rSchoolRoomId, @Return = @timeofflecturecount OUTPUT 
								IF(@timeofflecturecount >=1)
								BEGIN
									SELECT 6 as Result-- For this Lecture Timeoff set
								END
								ELSE
								BEGIN
									set @count = (SELECT Count(*) from [sTimetable].[tTempSTimeTable]  where TimeTableSchemaId = @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLectureNo AND LockNo <> 0)
									IF(@count != 1)
									BEGIN
										--update [sTimetable].[tTempSTimeTable] set EmployeeId = @rEmployeeId AND SubjectId = @rSubjectId WHERE TimeTableSchemaId = @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLectureNo AND LockNo != 1)
										Update [sTimetable].[tTempSTimeTable] set TimeTableSchemaId =@rSchemaId , EmployeeId = @rTeacherId , DivisionId  = @rDivisionId,
										SubjectId =  @rSubjectId , DayId = @rDayId , LectureNumber = @rLectureNo , LockNo = @rLockNo , LockColour = @rLockColour , SchoolRoomId = @rSchoolRoomId,
										IsBreak = @rIsBreak, IsMergeDivision = @rIsMergeDivision , IsBreakBefore = @rIsBreakBefore
										where  TimeTableSchemaId = @rSchemaId AND DivisionId = @rDivisionId AND DayId = @rDayId AND LectureNumber = @rLectureNo        
										
										select 5 as Result -- Success
									END
									ELSE
									BEGIN
										SELECT 4 as Result -- lecture is locked
									END
								END
							END
						END
						
					END

				
				END
	
	

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


GO
ALTER PROCEDURE [sTimetable].[pWebSetSameLecture]  
	@rDivisionId int,
	@rSchemaId int,
	@rOrgId 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 int,
		Remain 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 @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] @rSchemaId, @rOrgId , 0 , @rDivisionId, @rSubjectId,@rEmployeeId, 0, 0 
	END	
	--ELSE 
	--BEGIN	
	--	INSERT INTO @WorkloadData (SubjectId, Subject,Division , DivisionId ,Teacher,TeacherId,Code,Total,Remain)
	--	EXEC [sTimetable].[pWebGetSuggestedSubjectList] 0, 0 , 0 , 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 sTimeTable.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

					--select * from @TimeTable
					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


CREATE PROCEDURE [sSecurity].[pMenu_Migration]    
 @rUserId [bigint],    
 @rMenuId int = 0,    
 @rResourceTypeId int= 0,    
 @rIsCoreMenu bit = 0,
 @rEnvironmentName nvarchar(max)  
      
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)     
       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(Menu.ScreenId, 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    
  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 (
		(@rEnvironmentName = 'Local' AND Menu.Id in(24,2711,2411,2714,55,2412,53,52,5515,19181022,19184006,2622)) -- updated by parth for showing more menu inside the communication 07-02-2023 
	OR (@rEnvironmentName = 'QA' AND Menu.Id IN (24, 2714, 2711))
	OR (@rEnvironmentName = 'LiveEnvironment' AND Menu.Id IN (24, 2714, 2711))
	)
	
  --AND Menu.Id  in (24,2711,2411)  -- Issue: For Only Student Center, My Communication Menu , Developer: Foram, Date: 15-07-2022
  --AND (Menu.Id  in (24,2711)   
  --Or ParentMenuID  in (24,2711))     
  --AND (Menu.Id IN (15, 32, 55,24, 19181042, 36, 3606,2714)  
  --OR ParentMenuID IN (15, 32, 55, 19181042, 36, 24, 18))  
  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(Menu.ScreenId, 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    
  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 Screen1.Id IN (10187,30363,30342,20241,30282,20565,20563,30377,20915,30331,20564,20180,20617,30364,20130,30818,30828,20191,30787,20996,20193,20717  -- Issue: For Only Student Center Menu , Developer: Foram, Date: 15-07-2022  
		,9137,20054,20085,20088,20093,20099,20104,20107,20172,20198,20204,20209,20222,20566,20577,20692,20723,20724,30228,30242,30254,30365,30556,30559,30585,30794,30858,30943,31175,30921,20103,20102,20223,30561, -- Issue: For Only My Communication Menu , Developer: Foram, Date: 20-07-2022  
		30857,30696,30739,30284,20171,20105,31048,9136,30678,20179,30793,20720,20115,20693,20086,20819,20126,20950,20094,30856,30855,30795,30789,20697,20758,20083,30245,30246,20125,30874,30274,30817,30251,30252,30253,30247,30395, -- this line for communication screen changes by parth
		20983,30071,30103,30859,30935,30602,30848,30362,20181,20246,20576,20916,20979,30020,30093,30106,30115,30124,30134,30139,30149,30257,30327,30788,30455,30344,31005, -- this line for employee center screen changes by parth
		20108,30298,30552,20025,20026,20068,20336,20551,20719,20866,20894,20899,20900,3009530203,30296,30701,30816,30962, -- this line for fees screen changes by parth
		20396,20630,30138,30548,31012,20075,20161,20163,20167,20654,20985,30974,30994,20388,20073,20074,20165, -- this line for atte screen changes by parth
		20766,20927,20555,30366,20622,30651,30515,20554,20561,20159, 30664, -- this line is use for academic module
		30236,30500,30600,30604,31188,31182,30797,30471,30456,30803,30411,30412,30409,30410,30447,30449,20949,20197,20056,20952,20059,20150,20153) --17/05/2023
  --AND Screen1.Id IN (9147, 30300, 30301, 30330, 30341, 30782, 30783, 30784, 30785, 30786, 30787, 30788, 30790, 30791, 30792, 30499,20246,
  --30914,30913,30912,30911
  --)  
  ) 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 (15,32,36,55,24,1515,3606,19184009,19181042,2714,19184020) 
  AND (
		(@rEnvironmentName = 'Local' AND MenuTable.MenuId in(24,2711,2411,2714,55,2412,53,52,5515,19181022,19184006,2622)) -- updated by parth for showing more menu inside the communication 07-02-2023 
	OR (@rEnvironmentName = 'QA' AND MenuTable.MenuId IN (24, 2714, 2711))
	OR (@rEnvironmentName = 'LiveEnvironment' AND MenuTable.MenuId IN (24, 2714, 2711))
	)
  --AND MenuTable.MenuId  in (24,2711,2411,2714,55,2412,53,52,5515,19181022,19184006,2622)  -- updated by parth for showing more menu inside the communication 07-02-2023
  --AND MenuTable.MenuId  in (24,2711,2411)  -- Issue: For Only Student Center, My Communication Menu , Developer: Foram, Date: 15-07-2022  
  ORDER BY ParentMenuID    
 END    
END

GO




-- exec [sOp].[pPaymentTransactionStatusDetailGet] 9911, 9911, 991100105, 9911000000570, 9911000002072
CREATE PROCEDURE [sOp].[pPaymentTransactionStatusDetailGet]
@rOrgGroupId smallint=0,
@rOrgId int=0,
@rBatchId int=0,
@rUserId bigint,
@rPaymentTransactionId bigint
AS
BEGIN
	SELECT 
		PT.TransactionStatusId AS StatusId
	FROM sOp.tPaymentTransaction PT 
		LEFT JOIN sAlert.tOSType OS ON OS.Id = PT.OSTypeId                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		INNER JOIN sOp.tTransactionStatusMaster TSM ON TSM.ID = PT.TransactionStatusId
		INNER JOIN sOp.tFunctionalityMaster FM ON FM.Id = PT.FunctionalityId
		INNER JOIN sStudent.tAcademic A ON A.Id = PT.PayerSourceId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id = A.StudentId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.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.tOrganizationMaster OM ON OM.Id = CM.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId
	WHERE OM.OrgGroupId = @rOrgGroupId
		AND OM.Id = @rOrgId
		AND BM.Id = @rBatchId
		AND SM.id = @rUserId
		AND PT.Id = @rPaymentTransactionId
END