ALTER TABLE D_ReliefRuleSKill
ALTER COLUMN OperatingHour INT NULL
GO

/****** Object:  StoredProcedure [dbo].[prD_ReliefRuleSkillUpd]    Script Date: 1/24/2023 10:55:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[prD_ReliefRuleSkillUpd]    
(
	@RuleDetId			Bigint,
	@RuleId				Bigint, 
	@Seq				bigint,	 	 
	@Clause				nvarchar(50),	
	@SkillId			bigint, 
	@OperatingHour		Decimal(18,2),
	@NoOfEmployee		int,
	@NoOfActivity		int,
	@UpdatedBy			nVarchar(50),
	@MinRestHours		Decimal(18,2) = 0
)
AS
DECLARE @ValidateDuplicate int = 0
BEGIN

	SELECT	@ValidateDuplicate =  COUNT(*)
	FROM	D_ReliefRuleSkill WITH (NOLOCK)
	WHERE	RuleId=@RuleId and SkillId=@SkillId
	AND		RuleDetId	!=	@RuleDetId

	If (@ValidateDuplicate = 0)
	BEGIN
		UPDATE	D_ReliefRuleSkill
		SET		RuleId				=	@RuleId, 
				Seq					=	@Seq, 				 
				Clause				=	@Clause, 
				SkillId				=	@SkillId, 
				OperatingHour		=	@OperatingHour ,
				NoOfEmployee		=	@NoOfEmployee,
				NoOfActivity		=	@NoOfActivity,
				UpdatedBy			=	@UpdatedBy,
				UpdatedDate			=	GETDATE(),
				MinRestHours		=	@MinRestHours
		WHERE	RuleDetId			=	@RuleDetId

		SELECT 1;
	END
	Else
	BEGIN
		SET @ValidateDuplicate = -1;
		SELECT @ValidateDuplicate;
	END
END
GO

ALTER TABLE J_ManningRatio 
ALTER COLUMN MaxHour INT NULL
GO

ALTER TABLE J_ManningRatio 
ALTER COLUMN RBHour INT NULL
GO

/****** Object:  StoredProcedure [dbo].[prL_AddLeave]    Script Date: 1/31/2023 9:30:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

----------------------------------------------------------------------------
-- Insert a single record into L_Leaves
----------------------------------------------------------------------------
ALTER PROC [dbo].[prL_AddLeave]
(
	@OuId				BigInt,
	@EmployeeId			BigInt,
	@ExceptionCodeId	BigInt,
	@LeaveStart			DateTime,
	@LeaveEnd			DateTime,
	@LeaveType			Int,
	@NoOfDays			Decimal(18,2),
	@ReasonId			Int,
	@IsEmergency		Bit,
	@ApprovalStatusId	Int,
	@ReferenceNo		nVarchar(50),
	@Remarks			text,
	@AppliedBy			nVarchar(50),
	@AppliedDate		DateTime,
	@ApprovedBy			nVarchar(50),
	@ApprovedDate		DateTime,
	@CreatedBy			nVarchar(50),
	@PanelClinicId		BigInt = NULL
)
AS
DECLARE @ValidateDuplicate int = 0
BEGIN
	
	--TODO: This is Temporary Solution. Required To Find Root Cause of Why Some Employees Leaves Inserted with OUId = 0 ???
	If (@OuId = 0) SELECT @OuId = OUId From C_Employee WITH (NOLOCK) WHERE EmployeeId = @EmployeeId

	INSERT INTO L_Leaves
	(OUId, EmployeeId, ExceptionCodeId, LeaveStart, LeaveEnd, LeaveType, NoOfDays, ReasonId, IsEmergency, ApprovalStatusId,
	 ReferenceNo, Remarks, AppliedBy, AppliedDate, ApprovedBy, ApprovedDate, CreatedBy, CreatedDate,PanelClinicId)
	VALUES(@OuId, @EmployeeId, @ExceptionCodeId, @LeaveStart, @LeaveEnd, @LeaveType, @NoOfDays, @ReasonId, @IsEmergency, @ApprovalStatusId,
			@ReferenceNo, @Remarks, @AppliedBy, @AppliedDate, @ApprovedBy, @ApprovedDate, @CreatedBy, GETDATE(),@PanelClinicId)
		
	SELECT  CAST(@@Identity AS bigint) 
	
END
GO

ALTER TABLE P_OvertimeSettings
ALTER COLUMN PeriodLength int NULL
GO

UPDATE TA_ExceptionCodes SET TAException='NPA' WHERE TAExceptionId = 11

DELETE FROM TA_ExceptionCodes WHERE TAExceptionId = 12

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'prM_GetShiftSignOffStatusByDate')
	DROP PROC prM_GetShiftSignOffStatusByDate
GO

----------------------------------------------------------------------------
-- Retrieve Shift Sign Off Status By Selected Date, OU And Shift Band
----------------------------------------------------------------------------
CREATE PROC prM_GetShiftSignOffStatusByDate
	@OUId bigint = NULL,
	@ShiftDate date = NULL,
	@ShiftBandId bigint = NULL,
	@StartTime datetime = NULL,
	@EndTime datetime = NULL,
	@EquipmentTypes nVarchar(100) = NULL
AS
	SELECT	StatusId,
			M_ShiftSignOffStatus.OUId,
			C_OrganizationNode.OUName,
			ShiftDate,
			M_ShiftSignOffStatus.ShiftBandId,
			D_ShiftBand.ShiftBandName,
			M_ShiftSignOffStatus.StartTime,
			M_ShiftSignOffStatus.EndTime,
			Status,
			SignedOffBy,
			SignedOffDate,
			DeployedDate,
			DeployedBy,
			DeploymentPublishedDate,
			DeploymentPublishedBy,
			EquipmentTypes,
			ActivityGenerationRuleId,
			AllocationRuleId
	FROM	M_ShiftSignOffStatus WITH (NOLOCK) 
			INNER JOIN C_OrganizationNode WITH (NOLOCK) ON M_ShiftSignOffStatus.OUId = C_OrganizationNode.OUId 
			INNER JOIN D_ShiftBand WITH (NOLOCK) ON M_ShiftSignOffStatus.ShiftBandId = D_ShiftBand.ShiftBandId
	WHERE	M_ShiftSignOffStatus.ShiftBandId		=	@ShiftBandId
	AND		M_ShiftSignOffStatus.OUId				=	@OUId	
	AND		Convert(Date,M_ShiftSignOffStatus.ShiftDate)			=	Convert(Date,@ShiftDate)
	AND		Convert(DateTime,M_ShiftSignOffStatus.StartTime)		=	Convert(DateTime,@StartTime)
	AND		Convert(DateTime,M_ShiftSignOffStatus.EndTime)			=	Convert(DateTime,@EndTime)
	AND		(ISNULL(M_ShiftSignOffStatus.EquipmentTypes,'')	=	ISNULL(@EquipmentTypes,'') OR ISNULL(@EquipmentTypes,'') = '')
GO