
GO
ALTER 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.ClassId,
			SA.DivisionId,
			SA.DivisionName,
			SA.DepartmentId,
			SA.DepartmentName,
			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'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
GO
PRINT N'Altering Procedure [sCommonService].[pZoomEmployeeSearchGet]...';


GO
ALTER 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	
END
GO
PRINT N'Altering Procedure [sCommunication].[pMultiAllPhotoCategory_Get]...';


GO
ALTER PROCEDURE [sCommunication].[pMultiAllPhotoCategory_Get]
	 @rId int = 0,
	 @rSyncedPhotoCategoryId int = 0,
	 @rBatchId int,
	 @rIsDivisionWise bit=true,
	 @rBatchYear int = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
    DECLARE @lOrgGroupId smallint,@lOrgGroupBatchId int,@lOrgId smallint
    
	SELECT @lOrgGroupId=OM.OrgGRoupId,@lOrgGroupBatchId=OrgGroupBatchId,@lOrgId=OM.Id FROM sSetup.tOrganizationMaster OM
	INNER JOIN sSetup.tBatchMaster BM ON BM.OrgId=OM.Id
	WHERE Bm.Id=@rBatchId
	
    IF(@rIsDivisionWise=0)
    BEGIN
		SELECT   
			MPCM.[Id]
			,MPCM.[Title]
			,MPCM.[IsObsolete]
			--,ISNULL(PC.ScopeSourceTypeId,0) AS ScopeSourceTypeId
			,ISNULL(MPCM.Remark,'') AS Remark,
			MPCM.CreatedDateTime,
			--CASE WHEN ISNULL(MPCM.ScopeSourceTypeId,0) = 2021 THEN 'Organization'
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2017 THEN 'Department' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2016 THEN 'Class' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2005 THEN 'Division' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2014 THEN 'Hostel' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2029 THEN 'House' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2003 THEN 'Student' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 95 THEN 'Employee'
			--END +' - '+ [sCommunication].[fMultiPhotoCategoryScopeGet](MPCM.Id,PC.ScopeSourceTypeId) AS ScopeSourceType,
			U.DisplayTitle AS UserName,
			ISNULL(MPCM.OrgGroupBatchId,0) AS OrgGroupBatchId,
			MPCM.ScopeSourceTypeId AS AssigneeId
		FROM    
			[sCommunication].[tMultiPhotoCategoryMaster]  MPCM 
			--INNER JOIN sCommunication.tMultiPhotoCategoryScope PC ON MPCM.Id =PC.MultiPhotoCategoryId
			INNER JOIN sCommon.tAssigneeMaster AM ON MPCM.ScopeSourceTypeId = AM.Id  -- Add Join for Mantis Id :0033360
			INNER JOIN sSecurity.tUser U ON MPCM.CreatedUserId = U.Id
		WHERE   
			((MPCM.Id = @rId) OR (@rId = 0))
			AND ((MPCM.Id > @rSyncedPhotoCategoryId) OR (@rSyncedPhotoCategoryId = 0))
			AND OrgGroupId=@lOrgGroupId
			AND ((YEAR(MPCM.CreatedDateTime) = @rBatchYear) OR (@rBatchYear = 0))
			AND (MPCM.OrgGroupBatchId = @lOrgGroupBatchId OR ISNULL(MPCM.OrgGroupBatchId,0)=0)
		ORDER BY 
			MPCM.CreatedDateTime DESC
	END		
	ELSE
	
	 BEGIN
		SELECT   
			MPCM.[Id]
			,MPCM.[Title]
			,MPCM.[IsObsolete]
			--,ISNULL(PC.ScopeSourceTypeId,0) AS ScopeSourceTypeId
			,ISNULL(MPCM.Remark,'') AS Remark,
			MPCM.CreatedDateTime,
			--CASE WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2021 THEN 'Organization'
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2017 THEN 'Department' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2016 THEN 'Class' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2005 THEN 'Division' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2014 THEN 'Hostel' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2029 THEN 'House' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 2003 THEN 'Student' 
			--WHEN ISNULL(PC.ScopeSourceTypeId,0) = 95 THEN 'Employee'
			--END +' - '+ [sCommunication].[fMultiPhotoCategoryScopeGet](MPCM.Id,PC.ScopeSourceTypeId) AS ScopeSourceType,
			U.DisplayTitle AS UserName,
			ISNULL(MPCM.OrgGroupBatchId,0) AS OrgGroupBatchId,
			MPCM.ScopeSourceTypeId AS AssigneeId
		FROM    
			[sCommunication].[tMultiPhotoCategoryMaster]  MPCM 
			--INNER JOIN sCommunication.tMultiPhotoCategoryScope PC ON MPCM.Id =PC.MultiPhotoCategoryId
			INNER JOIN sCommon.tAssigneeMaster AM ON MPCM.ScopeSourceTypeId = AM.Id -- Add Join for Mantis Id :0033360
			INNER JOIN sSecurity.tUser U ON MPCM.CreatedUserId = U.Id
		WHERE   
			((MPCM.Id = @rId) OR (@rId = 0))
			AND ((MPCM.Id > @rSyncedPhotoCategoryId) OR (@rSyncedPhotoCategoryId = 0))
			AND OrgGroupId=@lOrgGroupId
			AND ((YEAR(MPCM.CreatedDateTime) = @rBatchYear) OR (@rBatchYear = 0))
			AND (MPCM.OrgGroupBatchId = @lOrgGroupBatchId OR ISNULL(MPCM.OrgGroupBatchId,0)=0)
		ORDER BY 
			MPCM.CreatedDateTime DESC
	END		
END
GO

ALTER PROCEDURE [sTimetable].[pDeleteTimeTable]

	  @rSelectScreen nvarchar(100),
	  @rTimeTableSchemaId int,
	  @rDivisionId int = 0,
	  @rOrgId int = 0,
	  @rUserSorceTypeId int = 0,
	  @rUserId BigInt = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
---
BEGIN
	
	-- #Start MantisId : 0032945
	IF OBJECT_ID('tempdb..#TempDivisionMaster') IS NOT NULL
    DROP TABLE #TempDivisionMaster;

	CREATE TABLE #TempDivisionMaster
	(DivisionId Int Not Null)

	IF(@rDivisionId = 0)
		BEGIN
			IF(@rUserSorceTypeId = 95)
				BEGIN
					INSERT INTO #TempDivisionMaster
					SELECT CD.DivisionId FROM sSetup.vClassDivision CD 
					INNER JOIN sTimetable.tAssignDivision AD ON CD.DivisionId = AD.DivisionId 
					INNER JOIN sTimetable.tAssignOrg AO ON AD.TimeTableSchemaId = AO.TimeTableSchemaId  AND CD.OrgId = AO.OrgId 
					INNER JOIN sTimetable.tAssignTeacher AT ON AT.TimeTableSchemaId = AD.TimeTableSchemaId 
					INNER JOIN sSecurity.tUser U ON U.SourceTypeId = 95 AND U.SourceId = AT.EmployeeId 
					WHERE AD.TimeTableSchemaId = @rTimeTableSchemaId AND U.Id = @rUserId AND AO.OrgId = @rOrgId
				END
			ELSE
				BEGIN
					INSERT INTO #TempDivisionMaster
					SELECT CD.DivisionId FROM sSetup.vClassDivision CD 
					INNER JOIN sTimetable.tAssignDivision AD ON CD.DivisionId = AD.DivisionId 
					INNER JOIN sTimetable.tAssignOrg AO ON AD.TimeTableSchemaId = AO.TimeTableSchemaId  AND CD.OrgId = AO.OrgId 
					WHERE AD.TimeTableSchemaId = @rTimeTableSchemaId AND AO.OrgId = @rOrgId
				END
		END
	ELSE
		BEGIN
			INSERT INTO #TempDivisionMaster VALUES(@rDivisionId)
		END
		-- # End Mantis Id : 0032945
		-- Add DivisionId IN (Select DivisionId From #TempDivisionMaster) For All Query For Mantis Id : 0032945

	IF(@rSelectScreen='Proxy Setting')
	BEGIN
		DELETE FROM sTimetable.tProxySetting WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END

	ELSE IF(@rSelectScreen='Assign Time Table Time')
	BEGIN
		IF EXISTS(SELECT * FROM sTimetable.tProxySetting WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster))
		BEGIN
			SELECT 101 AS Result
			RETURN;
		END
		DELETE FROM sTimetable.tTimeTableTime WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END

	ELSE IF(@rSelectScreen='Time Table')
	BEGIN
		IF EXISTS(SELECT * FROM sTimetable.tTimeTableTime WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster))
		BEGIN
			SELECT 102 AS Result
			RETURN;
		END
		DELETE FROM sTimetable.tTimeTable WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END

	ELSE IF (@rSelectScreen='Teacher Work Load')
	BEGIN
		IF EXISTS(SELECT * FROM sTimetable.tTimeTable WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster))
		BEGIN
			SELECT 103 AS Result
			RETURN;
		END
		DELETE FROM sTimetable.tTeacherWorkLoad WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END

	ELSE IF (@rSelectScreen='Division Work Load')
	BEGIN
		IF EXISTS(SELECT * FROM sTimetable.tTeacherWorkLoad WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster))
		BEGIN
			SELECT 104 AS Result
			RETURN;
		END
		DELETE FROM sTimetable.tDivisionWorkLoad WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END

	ELSE IF (@rSelectScreen='Time Setup')
	BEGIN
		IF EXISTS(SELECT * FROM sTimetable.tDivisionWorkLoad WHERE TimeTableSchemaId=@rTimeTableSchemaId AND DivisionId IN (Select DivisionId From #TempDivisionMaster))
		BEGIN
			SELECT 105 AS Result
			RETURN;
		END
		DELETE FROM sTimetable.tTimeSetup WHERE TimeTableSchemaId=@rTimeTableSchemaId AND ClassId IN (Select DivisionId From #TempDivisionMaster)
		SELECT 1 AS Result
	END
END
GO
PRINT N'Altering Procedure [sTimetable].[pSyncTimeTable]...';


GO
ALTER PROCEDURE [sTimetable].[pSyncTimeTable]
@rOrgGroupId int,
@rSchemaId int			
AS
BEGIN
	SET NOCOUNT ON;
	  DECLARE @lNextNumber bigint; 
	BEGIN TRY
	begin
    EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, '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'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 as Result; -- Indicate success
    END TRY
    BEGIN CATCH
        ROLLBACK;
        SELECT -1 as Result; -- 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, LockColour = ''
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 as Result; --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 as Result; --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 as Result; -- 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 as Result; -- 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 as Result; --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 as Result; --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 as Result; --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 as Result; --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 as Result; --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 as Result; --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 as Result;
		END TRY
		BEGIN CATCH
			ROLLBACK;
			SELECT -1 as Result;
		END CATCH
	END
END;
GO
PRINT N'Altering Procedure [sTimetable].[pWebGenerateTimeTable]...';


GO
--exec [sTimetable].[pWebGenerateTimeTable] 9915,0,991100006
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 , DM.OrgId from sTimetable.tTempSTimeTable TTT 
		inner join sSetup.tDivisionMaster DM on DM.Id = TTT.DivisionId
	left join  [sHrm].[tEmployeeMaster] EM on EM.Id = TTT.EmployeeId 
left join [sSetup].[tSubjectMaster] SM on SM.Id  = TTT.SubjectId where TimeTableSchemaId = @rSchemaId and  (DM.OrgId = @rOrgId Or (@rOrgId = 0))



	-------------------------------------------------------------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].[pWebGetLectureNumberOfEmployeeOfDay]...';


GO

----exec [sTimetable].[pWebGetLectureNumberOfEmployeeOfDay]991100022, '9912000000012,9912000000013'
ALTER PROCEDURE [sTimetable].[pWebGetLectureNumberOfEmployeeOfDay]
    @rSchemaId bigint,
    @rEmployeeId nvarchar(max)
AS
BEGIN


SET @rEmployeeId = REPLACE(@rEmployeeId, ',', ''',''');
		DECLARE @sql NVARCHAR(MAX)
		SET @sql = 'Select LectureNumber, DayId , EmployeeId from sTimetable.tTempSTimeTable where TimeTableSchemaId='''+CAST(@rSchemaId as nvarchar(max))+''' and EmployeeId in (''' + @rEmployeeId + ''') union all
		Select LectureNumber,DayId , EmployeeId  from sTimetable.tTeacherTimeOff where TimeTableSchemaId='''+CAST(@rSchemaId as nvarchar(max))+''' and EmployeeId in (''' + @rEmployeeId + ''') order by DayId , LectureNumber';
	
		EXEC sp_executesql @sql;
    --Select LectureNumber from sTimetable.tTempSTimeTable where EmployeeId=@rEmployeeId  and TimeTableSchemaId=@rSchemaId
    --union all
    --select LectureNumber from sTimetable.tTeacherTimeOff where EmployeeId=@rEmployeeId and TimeTableSchemaId=@rSchemaId
END
GO
PRINT N'Altering Procedure [sTimetable].[pWebGetSubjectTimetOff]...';


GO
---- exec [sTimetable].[pWebGetSubjectTimetOff] 991100022, '991200001,991200003'
ALTER PROCEDURE [sTimetable].[pWebGetSubjectTimetOff]
@rSchemaId int,
@rSubjectId nvarchar(MAX)
AS
BEGIN


SET @rSubjectId = REPLACE(@rSubjectId, ',', ''',''');
		DECLARE @sql NVARCHAR(MAX)
		SET @sql = 'Select DayId,LectureNumber, SubjectId from sTimetable.tSubjectTimeOff where TimetableSchemaId= '''+CAST(@rSchemaId as nvarchar(max))+''' and SubjectId in (''' + @rSubjectId + ''')';
	
		EXEC sp_executesql @sql;


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


GO
ALTER PROCEDURE [sTimetable].[pWebGetTimeTableBySchemaId] --991100022,0,0,0,0,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'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 as DivisionName,
        TST.SubjectId,
        S.Code as SubjectName,
        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'Creating Procedure [sCommonService].[pGetUserByMeetingWith]...';


GO
CREATE PROCEDURE [sCommonService].[pGetUserByMeetingWith]
    @rMeetingWithId INT,
    @rMeetingWithName VARCHAR(255),
	@rOrgId smallint,
	@rBatchId int
AS
BEGIN
    IF @rMeetingWithName = 'SubjectTeacher' and @rMeetingWithId = 2
    BEGIN
        -- Select data based on meetingID
        SELECT DISTINCT EM.ID AS Id, EM.FirstName + ' ' + EM.LastName AS Name 
        FROM sAcademic.tTeacherSubjectDivision TSD
		INNER JOIN sStudent.tAcademic SA ON SA.DivisionId = TSD.DivisionId and SA.BatchId = @rBatchId
        INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = TSD.SubjectId
        INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = TSD.EmployeeId and EM.StatusId = 7 and EM.OrgID = @rOrgId 
    END
    ELSE IF @rMeetingWithName = 'ClassTeacher' and @rMeetingWithId = 1
    BEGIN
        -- Select data for principal
        	 SELECT DISTINCT EMT.ID AS Id, EMT.FirstName + ' ' + EMT.LastName AS Name
			 FROM sStudent.vStudentAcademic SA
			 INNER JOIN sSetup.tDivisionMaster DM ON DM.ClassId = SA.ClassId 
			 INNER JOIN sHrm.tEmployeeMaster EMT ON EMT.Id = DM.InchargeEmployeeId and ISNULL(EMT.IsOtherUser, 0) = 0 and EMT.StatusId = 7 and EMT.OrgID = @rOrgId
			 WHERE SA.BatchId = @rBatchId 
	END
	ELSE IF @rMeetingWithName = 'Principal' and @rMeetingWithId = 3
    BEGIN
        -- Select data for principal
     SELECT Emp.ID AS Id, Emp.FirstName + ' ' + Emp.LastName AS Name FROM sSecurity.tUser Users			
			INNER JOIN sHrm.vExtEmployeeMaster Emp			
			ON Emp.Id = Users.SourceId AND Users.SourceTypeId = 95	and Users.OrgId = @rOrgId						
			INNER JOIN sSecurity.tUserRole UR ON UR.UserId=Users.Id AND UR.IsObsolete=0 
			AND UR.IsPrimary=1  
			INNER JOIN sSecurity.tRole R ON R.Id=UR.RoleId  and R.OrgGroupId= @rOrgId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = Users.OrgId
			INNER JOIN sSetup.tAffiliationMaster AT ON OM.AffiliationId = AT.Id
			LEFT JOIN sSecurity.tUserProfile Uprof ON Users.Id = Uprof.UserId
        WHERE 
		  Emp.Designation = 'Principal' 
		  and Emp.StatusId = 7
    END
END
GO
PRINT N'Creating Procedure [sConfig].[pConfigurationSetupValidate]...';


GO
PRINT N'Creating Procedure [sTimetable].[pWebTimetableschemaName]...';


GO

----exec [sTimetable].[pWebGetLectureNumberOfEmployeeOfDay]991100022, '9912000000012,9912000000013'
CREATE PROCEDURE [sTimetable].[pWebTimetableschemaName]
    @rSchemaId int
AS
BEGIN


select Title as SchemaName From sTimeTable.tTimeTableSchema where Id = @rSchemaId
END
GO