﻿-----------------------------------------------AK 2023-07-27 ------------------------------------
INSERT INTO [dbo].SecurityGroupDetail(SecurityGroupID,PrivilegeID)
SELECT SecurityGroupID, CASE 
WHEN PrivilegeID=5149 THEN 15339
WHEN PrivilegeID=5150 THEN 15340 
WHEN PrivilegeID=5151 THEN 15341 
WHEN PrivilegeID=5152 THEN 15342 
WHEN PrivilegeID=5153 THEN 15343 
 ELSE 0 END AS PrivilegeID 
FROM [dbo].SecurityGroupDetail WHERE PrivilegeID in (5149,5150,5151,5152,5153)
Go

delete from dbo.SecurityGroupDetail where PrivilegeID in(  5149,5150,5150, 5152,5153)
Go
delete from SecurityPrivilege where ID in(  5149,5150,5150, 5152,5153)
Go
-----------------------------------------------End ------------------------------------
-----------------------------------------------AK 2023-07-31 ------------------------------------
UPDATE ConfigParameter SET ConfigDescription=N'None is the default value' WHERE ConfigKey='RecOnlineRecruitmentLink'
Go
-----------------------------------------------End ------------------------------------

-----------------------------------------------MahmoudNegm 2023-08-1 ------------------------------------
 insert into SecurityPrivilege  (id, DescriptionAR, DescriptionEN , SystemMenuCode , PrivilegeType ) 
 values ( 15344, N'إرجاع طلب  العرض الوظيفى ' , N'Inverse Job Offer Request Signature' ,1008 , 2)
Go
-----------------------------------------------End ------------------------------------

-----------------------------------------------MahmoudNegm 2023-08-2 ------------------------------------

delete EmailTemplate where EmailTemplateTypeCode in (select EmailTemplateTypeCode from EmailTemplateType where RequestTypeCode=58)
delete EmailTemplateType where RequestTypeCode=58
Go


 SET IDENTITY_INSERT[dbo].[EmailTemplateType] ON
INSERT[dbo].[EmailTemplateType]([EmailTemplateTypeCode], [Name],[Arabic_Name], [IsEditable],[AppModuleCode], RequestTypeCode)
VALUES(343,N'job Offer Sending',N'ارسال العرض الوظيفى ',0,1,58)
INSERT[dbo].[EmailTemplateType]([EmailTemplateTypeCode], [Name],[Arabic_Name], [IsEditable],[AppModuleCode],RequestTypeCode)
VALUES(344,N'Job Offer Accept',N'الموافقة على العرض الوظيفى ',0,1,58)
INSERT[dbo].[EmailTemplateType]([EmailTemplateTypeCode], [Name],[Arabic_Name], [IsEditable],[AppModuleCode],RequestTypeCode)
VALUES(345,N'Job Offer Refuse',N'رفض العرض الوظيفى',0,1,58)
INSERT[dbo].[EmailTemplateType]([EmailTemplateTypeCode], [Name],[Arabic_Name], [IsEditable],[AppModuleCode],RequestTypeCode) 
VALUES(346,N'Send Notification AFter Track',N'ارسال اشعار بعد نهاية المسار',0,1,58)
 
SET IDENTITY_INSERT [dbo].[EmailTemplateType] OFF

INSERT[dbo].[EmailTemplate]([Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode])
VALUES(N'Job Offer Sending',N'ارسال العرض الوظيفى  ',343,N'ارسال',N'ارسال',1,NULL)
INSERT[dbo].[EmailTemplate]([Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode])
VALUES(N'Job Offer  Accept',N'الموافقة على العرض الوظيفى',344,N'الموافقة على',N'الموافقة على',1,NULL)
INSERT[dbo].[EmailTemplate]( [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode])
VALUES(N'Job Offer Refuse',N'رفض العرض الوظيفى',345,N'رفض',N'رفض',1,NULL)
INSERT[dbo].[EmailTemplate]( [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) 
VALUES(N'Job Offer Notification AFter Track',N'ارسال اشعار بعد نهاية المسار',346,N'ارسال اشعار بعد نهاية المسار',N'ارسال اشعار بعد نهاية المسار',1,NULL)
Go

-----------------------------------------------End ------------------------------------
-----------------------------------------------AK 2023-08-02 ------------------------------------
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Positions ADD
	JobDescriptionFileName nvarchar(500) NULL
GO
ALTER TABLE dbo.Positions SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO
ALTER procedure [dbo].[Emp_GetEmployeeProfile] 
(
@userCode	int
)
AS
SELECT  
e.[EmployeeCode]
,e.[Name]
,e.[Arabic_Name]
,[Sex]
,[BirthDate]
,[BirthCityCode]
,[BirthDestination]
,[BirthDestinationArabic]
,e.PositionCode       
,[LokerNumber]
,[IDTypeCode]
,[IdIssueFrom]
,[IdIssueFromArabic]
,[IdNumber]
,[IDpolice_station]
,[IdCityCode]
,[IdIssueDate]
,[IdExpireDate]
     
,[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 ------------------------------------
-----------------------------------------------Hammad 02-08-2023 ----------------------

ALTER TABLE WF_Track
ADD IsIndoor bit NOT NULL DEFAULT 0
Go


CREATE TABLE [dbo].[WF_TrackGeneralTypes](
	[TrackGeneralTypeCode] [int] IDENTITY(1,1) NOT NULL,
	[TrackId] [int] NOT NULL,
	[GeneralTypeCode] [tinyint] NOT NULL,
 CONSTRAINT [PK_WF_TrackGeneralTypes] PRIMARY KEY CLUSTERED 
(
	[TrackGeneralTypeCode] 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].[WF_TrackGeneralTypes]  WITH CHECK ADD  CONSTRAINT [FK_WF_TrackGeneralTypes_GeneralTypes] FOREIGN KEY([GeneralTypeCode])
REFERENCES [dbo].[GeneralTypes] ([GeneralTypeCode])
GO

ALTER TABLE [dbo].[WF_TrackGeneralTypes] CHECK CONSTRAINT [FK_WF_TrackGeneralTypes_GeneralTypes]
GO

ALTER TABLE [dbo].[WF_TrackGeneralTypes]  WITH CHECK ADD  CONSTRAINT [FK_WF_TrackGeneralTypes_WF_Track] FOREIGN KEY([TrackId])
REFERENCES [dbo].[WF_Track] ([TrackId])
GO

ALTER TABLE [dbo].[WF_TrackGeneralTypes] CHECK CONSTRAINT [FK_WF_TrackGeneralTypes_WF_Track]
GO

-----------------------------------------------End 02-08-2023 ----------------------