﻿
 ----------------------------------M.Negm 2024-05-19--------------------------------

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1888,N'WorkFlow',N'CompensationAllowanceType',N'Index',0,N'get',3368)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1889,N'WorkFlow',N'CompensationAllowanceType',N'CompensationAllowanceType_Read',0,N'post',3369)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1890,N'WorkFlow',N'CompensationAllowanceType',N'Save',1,N'get',3370)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1891,N'WorkFlow',N'CompensationAllowanceType',N'Save',2,N'get',3371)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1892,N'WorkFlow',N'CompensationAllowanceType',N'Delete',0,N'post',3372)
Go

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1893,N'WorkFlow',N'CompensationAllowanceType',N'Save',0,N'post',3373)
Go

INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod])
VALUES(1299,NULL, NULL,N'CompensationAllowanceType',N'GetCompensationAllowanceTypeSearch',N'post')
Go
																										
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod])
VALUES(1300,NULL, NULL,N'CompensationAllowanceType',N'Put',N'Put')
Go																													

INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod])
VALUES(1301,NULL, NULL,N'CompensationAllowanceType',N'post',N'post')
Go																													

INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod])
VALUES(1302,NULL, NULL,N'CompensationAllowanceType',N'Delete',N'Delete')
Go																													

INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod])
VALUES(1303,NULL, NULL,N'CompensationAllowanceType',N'Get',N'Get')
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId])
VALUES(1574,1299,3369, NULL)
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId])
VALUES(1575,1300,3370, NULL)
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId])
VALUES(1576,1301,3370, NULL)
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId])
VALUES(1577,1302,3372, NULL)
Go

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId])
VALUES(1578,1303,3371, NULL)
Go
----------------------------------End 2024-05-19--------------------------------

----------------------------------Ali 2024-05-19--------------------------------

   INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) 
  VALUES (1420, N' عرض التأمين الصحي الشامل ', N'View Comprehensive Health Insurance', 3, 2)
  GO

     INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) 
  VALUES (1421, N'عرض التأمين الصحي الشامل موظفينى', N'View Employees Comprehensive Health Insurance', 613, 2)
GO

   INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) 
  VALUES (1422, N' عرض بيانات الشهاده الصحيه موظفينى ', N'View My Employees Health Card Data', 613, 2)
GO


----------------------------------End 2024-05-19--------------------------------
--------------------------------------- AK 2024-05-20 ---------
CREATE FUNCTION dbo.GetYearMonthsRanges 
(	
	@year int, 
	@startDay int
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT @year AS PYear, val Period, CASE WHEN @startDay=1 THEN datefromparts(@year, val, @startDay) ELSE dateAdd(Month,-1,datefromparts(@year, val, @startDay)) END  MonthStartDate, CASE WHEN @startDay=1 THEN dateAdd(DAY,-1,dateAdd(Month,1,datefromparts(@year, val, @startDay))) ELSE dateAdd(DAY,-1,datefromparts(@year, val, @startDay)) END MonthEndDate FROM dbo.f_split('1,2,3,4,5,6,7,8,9,10,11,12', ',')
)
GO
--------------------------------------- End AK 2024-05-20 ------------
--------------------------------------- Hammad 2024-05-20 ------------
ALTER TABLE dbo.FingerAreaCheckPointLogs ADD
	CheckPointURL nvarchar(MAX) NULL
GO
--------------------------------------- End ------------
----------------------------------Ali 2024-05-20-------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ComprehensiveHealthInsuranceTypes](
	[ComprehensiveHealthInsuranceTypeCode] [smallint] NOT NULL,
	[Name] [nvarchar](150) NULL,
	[Arabic_Name] [nvarchar](150) NULL,
	[CompanySharePercent] [float] NULL,
	[EmployeeSharePercent] [float] NULL,
	[MaximumTotal] [float] NULL,
	[MinimumTotal] [float] NULL,
	[SCPercent] [float] NULL,
	[SCWay] [tinyint] NULL,
	[MaximumCompanyAmount] [float] NULL,
	[MinimumCompanyAmount] [float] NULL,
	[MaximumEmployeeAmount] [float] NULL,
	[MinimumEmployeeAmount] [float] NULL,
	[Closed] [bit] NULL,
	[MDT] [datetime] NULL,
	[WifeSharePercent] [float] NULL,
	[ChildrenAndDependentsPercent] [float] NULL,
	[ComprehensiveBranch] [nvarchar](100) NULL,
	[ComprehensiveStation] [nvarchar](100) NULL,
	[ComprehensiveEstablishmentName] [nvarchar](150) NULL,
	[ComprehensiveEstablishmentNumber] [nvarchar](100) NULL,
	[ComprehensiveEstablishmentAddress] [nvarchar](255) NULL,
	[ComprehensiveInsuranceOffice] [nvarchar](150) NULL,
	[ComprehensiveResponsibleManagerName] [nvarchar](150) NULL,
	[ComprehensiveResponsibleManagerIDNumber] [nvarchar](100) NULL,
	[ComprehensiveContactNumber] [nvarchar](100) NULL,
	[ComprehensiveMinimumDaysToCalculateInCaseOfResignation] [tinyint] NULL,
	[ComprehensiveMinimumDaysToCalculateInCaseOfNewHire] [tinyint] NULL,
	[CalculationIfAppointmentResignationInSameMonth] [bit] NULL,
 CONSTRAINT [PK_ComprehensiveHealthInsuranceTypes] PRIMARY KEY CLUSTERED 
(
	[ComprehensiveHealthInsuranceTypeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ComprehensiveHealthInsuranceTypes] ADD  CONSTRAINT [DF_ComprehensiveHealthInsuranceTypes_Closed]  DEFAULT ((0)) FOR [Closed]
GO

ALTER TABLE [dbo].[ComprehensiveHealthInsuranceTypes] ADD  CONSTRAINT [DF_ComprehensiveHealthInsuranceTypes_ComprehensiveMinimumDaysToCalculateInCaseOfResignation]  DEFAULT ((0)) FOR [ComprehensiveMinimumDaysToCalculateInCaseOfResignation]
GO

ALTER TABLE [dbo].[ComprehensiveHealthInsuranceTypes] ADD  CONSTRAINT [DF_ComprehensiveHealthInsuranceTypes_ComprehensiveMinimumDaysToCalculateInCaseOfNewHire]  DEFAULT ((0)) FOR [ComprehensiveMinimumDaysToCalculateInCaseOfNewHire]
GO

ALTER TABLE [dbo].[ComprehensiveHealthInsuranceTypes] ADD  CONSTRAINT [DF_ComprehensiveHealthInsuranceTypes_CalculationIfAppointmentResignationInSameMonth]  DEFAULT ((0)) FOR [CalculationIfAppointmentResignationInSameMonth]
GO



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EmployeeComprehensiveHealthInsurance](
	[EmployeeCode] [int] NOT NULL,
	[ComprehensiveHealthInsuranceTypeCode] [smallint] NULL,
	[ComprehensiveHealthInsuranceSalary] [float] NULL,
	[ComprehensiveHealthInsuranceCompanyShare] [float] NULL,
	[ComprehensiveHealthInsuranceEmployeeShare] [float] NULL,
	[ComprehensiveHealthInsuranceWifeShare] [float] NULL,
	[ComprehensiveHealthInsuranceDependentsShare] [float] NULL,
	[ComprehensiveHealthInsuranceIssueDate] [datetime] NULL,
	[ComprehensiveHealthInsuranceIssueDateHijri] [nvarchar](10) NULL,
	[ComprehensiveHealthInsuranceIDNumber] [nvarchar](20) NULL,
	[ComprehensiveHealthInsuranceIssuePlace] [nvarchar](100) NULL,
	[RecordAddBy] [nvarchar](20) NULL,
	[RecordAddDate] [datetime] NULL,
	[RecordLastChangeBy] [nvarchar](20) NULL,
	[RecordChangeDate] [datetime] NULL,
	[MDT] [datetime] NULL,
	[GrossUpEmployeeShare] [bit] NULL,
	[GrossUpWifeShare] [bit] NULL,
	[GrossUpDependentsShare] [bit] NULL,
	[ComprehensiveHealthInsuranceSalaryText] [nvarchar](255) NULL,
	[ComprehensiveHealthInsuranceSalaryArabicText] [nvarchar](255) NULL,
	[CardIssueDate] [datetime] NULL,
	[CardIssueDateHijri] [nvarchar](10) NULL,
 CONSTRAINT [PK_EmployeeComprehensiveHealthInsuranceEmployeeCode] PRIMARY KEY CLUSTERED 
(
	[EmployeeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeComprehensiveHealthInsurance] ADD  CONSTRAINT [DF_EmployeeComprehensiveHealthInsurance_GrossUpEmployeeShare]  DEFAULT ((0)) FOR [GrossUpEmployeeShare]
GO

ALTER TABLE [dbo].[EmployeeComprehensiveHealthInsurance] ADD  CONSTRAINT [DF_EmployeeComprehensiveHealthInsurance_GrossUpWifeShare]  DEFAULT ((0)) FOR [GrossUpWifeShare]
GO

ALTER TABLE [dbo].[EmployeeComprehensiveHealthInsurance] ADD  CONSTRAINT [DF_EmployeeComprehensiveHealthInsurance_GrossUpDependentsShare]  DEFAULT ((0)) FOR [GrossUpDependentsShare]
GO

--------------------------------- End 2024-05-20 ------------
--------------------------------- Mostafa 2024-05-20 ------------

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1894,N'WorkFlow',N'LoanType',N'Index',0,N'get',3305)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1895,N'WorkFlow',N'LoanType',N'LoanType_Read',0,N'post',3306)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1896,N'WorkFlow',N'LoanType',N'Save',1,N'get',3307)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1897,N'WorkFlow',N'LoanType',N'Save',2,N'get',3308)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1898,N'WorkFlow',N'LoanType',N'Delete',0,N'post',3309)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1899,N'WorkFlow',N'LoanType',N'Save',0,N'post',3310)
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1304,NULL, NULL,N'LoanType',N'GetLoanTypeSearch',N'post')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1305,NULL, NULL,N'LoanType',N'GetLoanType',N'Get')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1306,NULL, NULL,N'LoanType',N'Put',N'Put')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1307,NULL, NULL,N'LoanType',N'post',N'post')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1308,NULL, NULL,N'LoanType',N'Delete',N'Delete')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1309,NULL, NULL,N'LoanType',N'Get',N'Get')
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1579,1304,3306, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1580,1305,3306, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1581,1306,3310, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1582,1307,3310, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1583,1308,3309, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1584,1309,3308, NULL)
GO
UPDATE WF_LoanType SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE WF_LoanType SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
--------------------------------- End 2024-05-20 ------------

