﻿---------------------------- AK 2023-06-22 ----- 
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 [SST.ERP_Client].dbo.Clients ADD HasWhatsAppService nvarchar(1000) NULL
GO
ALTER TABLE [SST.ERP_Client].dbo.Clients SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
---------------------------- End 2023-06-22 -----
---------------------------- AK 2023-06-25 ----- 
INSERT [dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES (N'WhatsAppAlternateMobileNumber', N'WhatsApp Alternate Mobile Number', N'رقم الواتساب البديل', N'', 1, 51, 4, 1, NULL, NULL, NULL,N'WhatsApp Alternate Mobile Number')
GO
---------------------------- End 2023-06-25 -----
---------------------------- Hammad 2023-06-25 --
ALTER TABLE dbo.SecurityUser ADD
	RecieveWhatsAppNotifications bit NOT NULL CONSTRAINT DF_SecurityUser_RecieveWhatsAppNotifications DEFAULT 0,
	Mobile nvarchar(50) NULL,
	RecruitResponsibleInterview bit NOT NULL CONSTRAINT DF_SecurityUser_RecruitResponsibleInterview DEFAULT 0
GO


alter procedure [dbo].[Emp_GetEmployeeProfile] 
(
@userCode	int
)
AS


SELECT  
e.[EmployeeCode]
,e.[Name]
,e.[Arabic_Name]
,[Sex]
,[BirthDate]
,[BirthCityCode]
,[BirthDestination]
,[BirthDestinationArabic]
          
,[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.[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






update SecurityUser set RecruitResponsibleInterview=1 where SecurityUserCode in (SELECT val FROM dbo.f_split((Select SUBSTRING((SELECT ',' + UserCodeResponsibleInterview FROM Rec_RecruitJobStages  where UserCodeResponsibleInterview is not null FOR XML PATH('')), 2 , 999999)), ','))
update SecurityUser set RecruitResponsibleInterview=1 where SecurityUserCode in (SELECT val FROM dbo.f_split((Select SUBSTRING((SELECT ',' + UserCodeResponsibleEvaluation FROM Rec_RecruitJob where UserCodeResponsibleEvaluation is not null FOR XML PATH('')), 2 , 999999)), ','))



---------------------------- End 2023-06-25 --