﻿----------------------------Ali 2023-11-14------------------


ALTER TABLE dbo.Religion ADD
	CreatedBy int NOT NULL CONSTRAINT DF_Religion_CreatedBy DEFAULT (1),
	CreationDate datetime NOT NULL CONSTRAINT DF_Religion_CreationDate DEFAULT (getdate()),
	ModifiedBy int NULL,
	ModificationDate datetime NULL
GO

INSERT INTO [SST.ERP_Client]. [dbo].[APIs] ([ApiId], [ApiName]) VALUES (12, N'Personal')
Go

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1023,N'Basic Data',N'بيانات أساسية',27,N'',N'','fa fa-thumb-tack',N'',1,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1023,22)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4016,N'بيانات أساسية',N'Basic Data',1023,1)
Go



INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1024,N'Religion',N'الديانه',1023,N'/Personal/Religion',N'Index','fa fa-thumb-tack',N'get',1,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1024,22)
Go


INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4010,N'الديانه',N'Religion',1024,1)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4011,N'عرض الديانه',N'View Religion',1024,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4012,N'إضافة الديانه',N'Add Religion',1024,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4013,N'تعديل الديانه',N'Edit Religion',1024,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4014,N'حذف الديانه',N'Delete Religion',1024,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4015,N'حفظ الديانه',N'Save Religion',1024,2)
Go


INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1315,N'Personal',N'Religion',N'Index',0,N'get',4010)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1316,N'Personal',N'Religion',N'Religion_Read',0,N'post',4011)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1317,N'Personal',N'Religion',N'Save',1,N'get',4012)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1318,N'Personal',N'Religion',N'Save',2,N'get',4013)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1319,N'Personal',N'Religion',N'Delete',0,N'post',4014)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1320,N'Personal',N'Religion',N'Save',0,N'post',4015)
Go



INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (881, NULL, NULL, N'Religion', N'GetReligionsSearch', N'Post')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (882, NULL, NULL, N'Religion', N'GetReligions', N'Get')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (883, NULL, NULL, N'Religion', N'Put', N'Put')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (884, NULL, NULL, N'Religion', N'Post', N'Post')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (885, NULL, NULL, N'Religion', N'Delete', N'Delete')
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES (886, NULL, NULL, N'Religion', N'Get', N'Get')
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1108, 881, 4011, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1109, 882, 4011, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1110, 883, 4015, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1111, 884, 4015, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1112, 885, 4014, NULL)
GO
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1113, 886, 4013, NULL)
GO



BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Religion
	DROP CONSTRAINT DF_Religion_CreatedBy
GO
ALTER TABLE dbo.Religion
	DROP CONSTRAINT DF_Religion_CreationDate
GO
CREATE TABLE dbo.Tmp_Religion
	(
	ReligionCode tinyint NOT NULL IDENTITY (1, 1),
	Name nvarchar(30) NULL,
	Arabic_Name nvarchar(30) NULL,
	MDT datetime NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	ModifiedBy int NULL,
	ModificationDate datetime NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Religion SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_Religion ADD CONSTRAINT
	DF_Religion_CreatedBy DEFAULT ((1)) FOR CreatedBy
GO
ALTER TABLE dbo.Tmp_Religion ADD CONSTRAINT
	DF_Religion_CreationDate DEFAULT (getdate()) FOR CreationDate
GO
SET IDENTITY_INSERT dbo.Tmp_Religion ON
GO
IF EXISTS(SELECT * FROM dbo.Religion)
	 EXEC('INSERT INTO dbo.Tmp_Religion (ReligionCode, Name, Arabic_Name, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate)
		SELECT ReligionCode, Name, Arabic_Name, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate FROM dbo.Religion WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Religion OFF
GO
DROP TABLE dbo.Religion
GO
EXECUTE sp_rename N'dbo.Tmp_Religion', N'Religion', 'OBJECT' 
GO
ALTER TABLE dbo.Religion ADD CONSTRAINT
	PK_Religion PRIMARY KEY CLUSTERED 
	(
	ReligionCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT


----------------------------End 2023-11-14------------------
---------------------------------Negm 15-11-2023 ---------------------

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1303,N'WorkFlow',N'FinancialPaymentHrRequest',N'Index',0,N'get',3616)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1304,N'WorkFlow',N'FinancialPaymentHrRequest',N'FinancialPaymentHrRequest_Read',0,N'post',3617)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1305,N'WorkFlow',N'FinancialPaymentHrRequest',N'Save',1,N'get',3618)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1306,N'WorkFlow',N'FinancialPaymentHrRequest',N'Save',2,N'get',3619)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1307,N'WorkFlow',N'FinancialPaymentHrRequest',N'Delete',0,N'post',3621)

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

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1309,N'WorkFlow',N'FinancialPaymentHrRequest',N'Cancel',0,N'post',3622)
Go





INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1310,N'WorkFlow',N'FinancialPaymentHrRequestSignature',N'Index',0,N'get',3623)
GO

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1311,N'WorkFlow',N'FinancialPaymentHrRequestSignature',N'FinancialPaymentHrRequestSignature_Read',0,N'post',3624)
GO

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) 
VALUES(1312,N'WorkFlow',N'FinancialPaymentHrRequestSignature',N'Save',2,N'get',3625)
GO

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID])
VALUES(1314,N'WorkFlow',N'FinancialPaymentHrRequestSignature',N'Save',0,N'post',3626)
GO

INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES (1107, 12, 3622, 31)
GO

----------------------------------------------------------------------
---------------------------------Hammad 16-11-2023--------------------
INSERT [dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId], [ConfigDescription]) 
VALUES (N'DisplayEmpSpecialIDInProfile', N'Display Employee Special ID In Profile  ', N' عرض الرقم الخاص للموظف فى الملف الشخصى ', N'false', 1, 56, 1, 1, NULL, NULL, NULL, N' عرض الرقم الخاص للموظف فى الملف الشخصى     ')
GO


Alter procedure [dbo].[Emp_GetEmployeeProfile] 
(
@userCode	int
)
AS
SELECT  
e.[EmployeeCode]
,e.[Name]
,e.[Arabic_Name]
,e.[ExperienceDate] 
,[Sex]
,[BirthDate]
,[BirthCityCode]
,[BirthDestination]
,[BirthDestinationArabic]
,e.PositionCode       
,[LokerNumber]
,[IDTypeCode]
,[IdIssueFrom]
,[IdIssueFromArabic]
,[IdNumber]
,[IDpolice_station]
,[IdCityCode]
,[IdIssueDate]
,[IdExpireDate]
 ,[SpecialID]    
,[Children_Number]
     
,[HiringDate]
,[ContractDate]
,[ContractExpireDate]    
     
,[InsuranceNumber]
,e.[ReligionCode]      
    
,[ResignationCode]
,[ResignationDate]     
,[Active]
   
,[CityCode]
,[EngFullAddress]
,[ArabicFullAddress]
,[OldFullAddress]    
,[Police_Station]
,[EngArea]
,[EngPolice_Station]
     
,[MotherName]
,e.[Telephone]
,e.[Mobile]
,e.[EMail]
,[CurrPercent]
,[Hidden]
,[Transfer]
,[VacationCalcCode]
,[DayRate]
,[WorkHoures]     
,[RoomNumber]     
,[BasicSalary]
    
,[StopSalaryReason]
,[NotApplicableToTransportationAllowance]
,[StopSalary]
,[ResignationPostingDate]
       
,[EmployeeType]
,[BirthDateHijri]
,[ContractDateHijri]
,[ContractExpireDateHijri]
,[HiringDateHijri]
,[ProbationDate]
,[ProbationDateHijri]
,[ExperienceDateHijri]
,[LastWageHijri]
,[InsureDateHijri]
,[ResignationDateHijri]
,[ResignationPostingDateHijri]
      
,dbo.Location.[Name] LocationNameEn
,dbo.Location.[Arabic_Name] LocationNameAr	
,dbo.SocialInsurance.[BasicEmployeeShare]
,dbo.SocialInsurance.[BasicCompanyShare]
,dbo.SocialInsurance.[VariableEmployeeShare]
,dbo.SocialInsurance.[VariableCompanyShare]
,dbo.SocialInsurance.[InsuranceDate]
,dbo.SocialInsurance.[InsuranceDateHijri]
,dbo.SocialInsurance.MaxBasic

,dbo.Insurer.[Name] as InsurerNameEn
,dbo.Insurer.[Arabic_Name] as InsurerNameAr
,dbo.InsuranceTypes.[Name] as InsuranceTypesNameEn
,dbo.InsuranceTypes.[Arabic_Name] as InsuranceTypesNameAr
,dbo.Sections.Name SectionsNameEn
,dbo.Sections.Arabic_Name SectionsNameAr
,dbo.Departments.Name DepartmentsNameEn
,dbo.Departments.Arabic_Name DepartmentsNameAr
,dbo.Divisions.Name DivisionsNameEn
,dbo.Divisions.Arabic_Name DivisionsNameAr
,dbo.Org4.Name Org4NameEn
,dbo.Org4.Arabic_Name Org4NameAr
,dbo.Org5.Name Org5NameEn
,dbo.Org5.Arabic_Name Org5NameAr
,dbo.Nationality.[Name] NationalityNameEn
,dbo.Nationality.[Arabic_Name] NationalityNameAr
,dbo.Nationality.[CountryName]
,dbo.Nationality.[CountryArabic_Name]
,dbo.MaritalStatus.[Name] MaritalStatusNameEn
,dbo.MaritalStatus.[Arabic_Name] MaritalStatusNameAr
,dbo.EmployeeEducationData.[EducationDescription]
,dbo.[Contract].[Name] ContractNameEn
,dbo.[Contract].[Arabic_Name] ContractNameAr
,dbo.[Currencies].[Name] CurrenciesNameEn
,dbo.[Currencies].[Arabic_Name] CurrenciesNameAr
,dbo.Levels.Name LevelsNameEn
,dbo.Levels.Arabic_Name LevelsNameAr
,dbo.Positions.Name PositionsNameEn
,dbo.Positions.Arabic_Name PositionsNameAr
,dbo.Positions.JobDescriptionFileName 
,dbo.[GeneralTypes].[Name] GeneralTypesNameEn
,dbo.[GeneralTypes].[Arabic_Name] GeneralTypesNameAr
,UserImage
,RecieveEmailNotifications
,UserName

,dbo.EmployeesExtend.[IssuePlace] as EmpExtendIssuePlace
,dbo.EmployeesExtend.[CardNumber] as  EmpExtendCardNumber
,dbo.EmployeesExtend.[IssueDate] as EmpExtendIssueDate
,dbo.EmployeesExtend.[IssueDateHijri] as EmpExtendIssueDateHijri
,dbo.EmployeesExtend.[ExpireDate] as EmpExtendExpireDate
,dbo.EmployeesExtend.[ExpireDateHijri] as EmpExtendExpireDateHijri
,dbo.EmployeesExtend.ExtensionNumber
,dbo.EmployeesExtend.EMail2 

,dbo.[HealthInsuranceTypes].[Name] as MedicalInsuranceTypeNameEn
,dbo.[HealthInsuranceTypes].[Arabic_Name] as MedicalInsuranceTypeNameAr
   
FROM [dbo].[SecurityUser]
left join [dbo].[Employees] e on [dbo].SecurityUser.[EmployeeCode] = e.EmployeeCode
left join [dbo].[Location] on [dbo].Location.[LocationCode] = e.LocationCode
left join [dbo].[SocialInsurance] on [dbo].SocialInsurance.[EmployeeCode] = e.EmployeeCode
left join [dbo].[Insurer] on [dbo].Insurer.[InsurerCode] = [dbo].SocialInsurance.[InsurerCode]
left join [dbo].[InsuranceTypes] on [dbo].InsuranceTypes.InsuranceCode = e.InsuranceCode
left join [dbo].[Sections] on [dbo].Sections.SectionCode = e.SectionCode
left join [dbo].[Departments] on [dbo].Departments.DepartmentCode = [dbo].Sections.DepartmentCode
left join [dbo].Divisions on [dbo].Divisions.DivisionCode = [dbo].Departments.DivisionCode
left join [dbo].Org4 on [dbo].Org4.Org4Code = [dbo].Divisions.FatherCode
left join [dbo].Org5 on [dbo].Org5.Org5Code = [dbo].Org4.FatherCode
left join [dbo].[Nationality] on [dbo].Nationality.[NationalityCode] = e.NationalityCode
left join [dbo].[MaritalStatus] on [dbo].MaritalStatus.[MaritalCode] = e.MaritalCode
left join [dbo].[EmployeeEducationData] on [dbo].EmployeeEducationData.[EmployeeCode] = e.EmployeeCode
left join [dbo].[Contract] on [dbo].[Contract].[ContractCode] = e.ContractCode
left join [dbo].[Currencies] on [dbo].[Currencies].CurrencyCode = e.CurrencyCode
left join [dbo].[Levels] on [dbo].Levels.LevelCode = e.LevelCode
left join [dbo].[GeneralTypes] on [dbo].GeneralTypes.[GeneralTypeCode] = e.GeneralTypeCode
left join [dbo].[Positions] on [dbo].Positions.PositionCode = e.PositionCode
left join [dbo].[EmployeesExtend] on [dbo].EmployeesExtend.[EmployeeExtendCode] = e.EmployeeCode
left join [dbo].[HealthInsuranceTypes] on [dbo].HealthInsuranceTypes.HealthInsuranceTypeCode = [dbo].[EmployeesExtend].HealthInsuranceTypeCode
where [dbo].[SecurityUser].[EmployeeCode] = @userCode;


GO




---------------------------------End 16-11-2023--------------------
---------------------------------Ali 19-11-2023--------------------

SET IDENTITY_INSERT [dbo].[AppModule] ON 
INSERT [dbo].[AppModule] ([AppModuleCode], [Name], [Arabic_Name], [IsLicensed], [IntegrateWithLedger], [LicenseNumber]) VALUES (22, N'Personnel', N'شئون الموظفين', 1, 0, NULL)
SET IDENTITY_INSERT [dbo].[AppModule] OFF
GO

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1024,22)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1023,22)
Go

---------------------------------End 19-11-2023--------------------
