------------------------------- A.Khaled 2020-02-27 --------------------------    
ALTER VIEW [Budget].[PreDashboardActualCost]
AS
SELECT BI.BudgetHeadCode,  BI.BudgetItemCode, BI.Arabic_Name, BI.Name, BAC.BudgetActualCostTypeCode, BAC.BudgetActualCostCode, BAC.CostYear, BAC.CostMonth, BAC.Amount, BI.SaveStatus FROM [Budget].[BudgetHeads] BH 
  INNER JOIN [Budget].[BudgetItems] BI ON BI.BudgetHeadCode=BH.BudgetHeadCode AND BI.CompareType=1 -- CompareTypeActualCost
  LEFT JOIN [Budget].[BudgetActualCosts] BAC ON BI.CompareValue=BAC.BudgetActualCostTypeCode
GO
------------------------------- End 2020-02-27 --------------------------
------------------------------- A.Khaled 2020-03-01 --------------------------
CREATE FUNCTION Budget.BudgetExpectedEmployeesByBudgetItemAndMonth
(
	@BudgetItemCode int,
	@MonthRank int
)
RETURNS FLOAT
AS
BEGIN
	DECLARE @BudgetExpectedEmployeesByBudgetItemAndMonthValue FLOAT

	SELECT @BudgetExpectedEmployeesByBudgetItemAndMonthValue=SUM(BeneficiariesCount*Value) FROM Budget.BudgetExpectedEmployees WHERE  BudgetItemCode=@BudgetItemCode AND StartMonth<=@MonthRank

	RETURN ISNULL(@BudgetExpectedEmployeesByBudgetItemAndMonthValue,0)
END
GO
ALTER VIEW [Budget].[PreDashboardBudget]
AS
WITH BEDDates AS 
(
  SELECT BIC.BudgetItemCategoryCode,BIC.Arabic_Name as CategoryNameAR,BIC.Name as CategoryNameEN,BED.BudgetItemCode, BED.EmployeeCode, BED.NewAmount, BED.EndEffectYear, BED.EndEffectMonth,  BH.FromMonth,BH.FromYear,BH.[ToMonth],BH.[ToYear],BH.MonthsCount,BED.BudgetEmployeeDetailCode
  FROM [Budget].[BudgetEmployeeDetails] BED
  INNER JOIN [Budget].[BudgetItems] BI ON BED.BudgetItemCode=BI.BudgetItemCode
  INNER JOIN [Budget].[BudgetItemCategory] BIC ON BI.BudgetItemCategoryCode=BIC.BudgetItemCategoryCode  
  INNER JOIN [Budget].[BudgetHeads] BH ON BI.BudgetHeadCode=BH.BudgetHeadCode  
  WHERE (datefromparts(BED.EndEffectYear, BED.EndEffectMonth, 1) BETWEEN datefromparts(BH.FromYear, BH.FromMonth, 1) AND datefromparts(BH.[ToYear], BH.[ToMonth], 1))
)
, BED AS 
(
  SELECT BIC.BudgetItemCategoryCode,BIC.Arabic_Name as CategoryNameAR,BIC.Name as CategoryNameEN,  BI.BudgetHeadCode, BI.BudgetItemCode, BI.Name, BI.Arabic_Name, BudEmpDtls.EmployeeCode, BudEmpDtls.NewAmount, BI.SaveStatus  FROM [Budget].[BudgetEmployeeDetails] BudEmpDtls
  INNER JOIN [Budget].[BudgetItems] BI ON BudEmpDtls.BudgetItemCode=BI.BudgetItemCode
    INNER JOIN [Budget].[BudgetItemCategory] BIC ON BI.BudgetItemCategoryCode=BIC.BudgetItemCategoryCode  
  INNER JOIN [Budget].[BudgetHeads] BH ON BI.BudgetHeadCode=BH.BudgetHeadCode
)
SELECT BED.BudgetItemCategoryCode,BED.CategoryNameAR,BED.CategoryNameEN,BED.BudgetHeadCode, BEDDates.MonthsCount, BED.BudgetItemCode, BED.Arabic_Name, BED.Name, BEDDates.BudgetEmployeeDetailCode, ROW_NUMBER() OVER(Partition by BEDDates.EmployeeCode,BED.BudgetItemCode ORDER BY MTR.Year ASC, MTR.Month ASC) AS MonthRank, MTR.Year, MTR.Month, BEDDates.EmployeeCode, BED.NewAmount, BED.SaveStatus
 FROM BED
INNER JOIN BEDDates ON BED.EmployeeCode=BEDDates.EmployeeCode AND BED.BudgetItemCode=BEDDates.BudgetItemCode
CROSS APPLY dbo.MonthsToRows(BEDDates.FromMonth,BEDDates.FromYear,BEDDates.EndEffectMonth,BEDDates.EndEffectYear) AS MTR
GO
CREATE VIEW [Budget].[PreDashboardBudgetWithExpectedEmployees]
AS
SELECT PDB.BudgetItemCategoryCode, PDB.CategoryNameAR, PDB.CategoryNameEN, PDB.BudgetHeadCode, PDB.BudgetItemCode, PDB.Arabic_Name, PDB.Name, PDB.MonthRank, PDB.Year, PDB.Month, SUM(PDB.NewAmount)  + Budget.BudgetExpectedEmployeesByBudgetItemAndMonth(PDB.BudgetItemCode,PDB.MonthRank) NewAmount 
FROM [Budget].[PreDashboardBudget] PDB
GROUP BY PDB.BudgetItemCategoryCode, PDB.CategoryNameAR, PDB.CategoryNameEN, PDB.BudgetHeadCode, PDB.BudgetItemCode, PDB.Arabic_Name, PDB.Name, PDB.MonthRank, PDB.Year, PDB.Month
--ORDER BY PDB.BudgetHeadCode, PDB.BudgetItemCode, PDB.MonthRank, PDB.Year, PDB.Month
GO
------------------------------- End 2020-03-01 --------------------------
------------------------------- Hammad 2020-03-02 -----------------------

ALTER TABLE dbo.WF_LoanTypeDetials ADD
	HiringFromMonthCount int NULL,
	HasGuarantor bit NOT NULL CONSTRAINT DF_WF_LoanTypeDetials_HasGuarantor DEFAULT 1,
	HiringGuarantorFromMonthCount int NULL,
	GuaranteeEmployeeCountYear int NULL,
	NoLoanDuringGuaranteeEmployee bit NOT NULL CONSTRAINT DF_WF_LoanTypeDetials_NoLoanDuringGuarantee DEFAULT 0,
	GuarantorApprovalRequired bit NOT NULL CONSTRAINT DF_WF_LoanTypeDetials_GuarantorApprovalRequired DEFAULT 0
GO

------------------------------- End 2020-03-02 -----------------------
-------------------------------Sayed 2020-03-02-----------------------

CREATE TABLE [dbo].[Relatives](
	[SerialAuto] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeCode] [int] NOT NULL,
	[Name] [nvarchar](255) NULL,
	[Arabic_Name] [nvarchar](255) NULL,
	[RelativeBirthDate] [datetime] NULL,
	[RelativeBirthDateHijri] [nvarchar](10) NULL,
	[RelativeTypeCode] [int] NOT NULL,
	[NationalityCode] [int] NULL,
	[ReligionCode] [int] NULL,
	[Gender] [int] NULL,
	[ResidenceCode] [int] NULL,
	[ResidenceNumber] [nvarchar](100) NULL,
	[ResidenceJob] [nvarchar](150) NULL,
	[ResidenceSource] [nvarchar](150) NULL,
	[ResidenceIssueDate] [datetime] NULL,
	[ResidenceIssueDateHijri] [nvarchar](10) NULL,
	[ResidenceExpireDate] [datetime] NULL,
	[ResidenceExpireDateHijri] [nvarchar](10) NULL,
	[PassportNumber] [nvarchar](100) NULL,
	[PassportSource] [nvarchar](100) NULL,
	[PassportIssueDate] [datetime] NULL,
	[PassportIssueDateHijri] [nvarchar](10) NULL,
	[PassportExpireDate] [datetime] NULL,
	[PassportExpireDateHijri] [nvarchar](10) NULL,
	[EntryNumber] [nvarchar](100) NULL,
	[EntryPort] [nvarchar](100) NULL,
	[EntryDate] [datetime] NULL,
	[EntryDateHijri] [nvarchar](10) NULL,
	[MedicalCardNumber] [nvarchar](100) NULL,
	[MedicalIssuePlace] [nvarchar](100) NULL,
	[MedicalIssueDate] [datetime] NULL,
	[MedicalIssueDateHijri] [nvarchar](10) NULL,
	[MedicalExpireDate] [datetime] NULL,
	[MedicalExpireDateHijri] [nvarchar](10) NULL,
	[MedicalTypeCode] [int] NULL,
	[MDT] [datetime] NULL,
 CONSTRAINT [PK_Relatives] PRIMARY KEY CLUSTERED 
(
	[SerialAuto] 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
/****** Object:  Table [dbo].[RelativeTypes]    Script Date: 02/03/2020 16:14:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RelativeTypes](
	[RelativeTypeCode] [int] NOT NULL,
	[Name] [nvarchar](255) NULL,
	[Arabic_Name] [nvarchar](255) NULL,
	[MDT] [datetime] NULL,
 CONSTRAINT [PK_RelativeTypes] PRIMARY KEY CLUSTERED 
(
	[RelativeTypeCode] 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
CREATE TABLE [dbo].[HealthInsuranceTypes](
	[HealthInsuranceTypeCode] [nvarchar](3) NOT NULL,
	[Name] [nvarchar](255) NULL,
	[Arabic_Name] [nvarchar](255) NULL,
	[Closed] [bit] NULL,
	[RelativeAmount0] [float] NULL,
	[RelativeAmount1] [float] NULL,
	[RelativeAmount2] [float] NULL,
	[RelativeAmount3] [float] NULL,
	[RelativeAmount4] [float] NULL,
	[RelativeAmount5] [float] NULL,
	[RelativeAmount6] [float] NULL,
	[RelativeAmount7] [float] NULL,
	[RelativeAmount8] [float] NULL,
	[RelativeAmount9] [float] NULL,
	[RelativeAmount10] [float] NULL,
	[MDT] [datetime] NULL,
	[RelativeAmountOverage0] [float] NULL,
	[RelativeAmountOverage1] [float] NULL,
	[RelativeAmountOverage2] [float] NULL,
	[RelativeAmountOverage3] [float] NULL,
	[RelativeAmountOverage4] [float] NULL,
	[RelativeAmountOverage5] [float] NULL,
	[RelativeAmountOverage6] [float] NULL,
	[RelativeAmountOverage7] [float] NULL,
	[RelativeAmountOverage8] [float] NULL,
	[RelativeAmountOverage9] [float] NULL,
	[RelativeAmountOverage10] [float] NULL,
 CONSTRAINT [PK_HealthInsuranceTypes] PRIMARY KEY CLUSTERED 
(
	[HealthInsuranceTypeCode] 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
-------------------------------End 2020-03-02-----------------------
-------------------------------Ali 2020-03-02-----------------------
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (1196, N'عرض التامين الطبى', N'View Medical Insurance', 3, 2)
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]
      ,[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.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.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.[HealthInsuranceTypes].[Name] as MedicalInsuranceTypeNameEn
	  ,dbo.[HealthInsuranceTypes].[Arabic_Name] as MedicalInsuranceTypeNameAr
	



	 
	  ,dbo.Relatives.[MedicalIssuePlace] 
	  ,dbo.Relatives.[MedicalCardNumber] 
	  ,dbo.Relatives.[MedicalIssueDate] 
	  ,dbo.Relatives.[MedicalIssueDateHijri] 
	  ,dbo.Relatives.[MedicalExpireDate] 
	  ,dbo.Relatives.[MedicalExpireDateHijri] 
	  ,dbo.[RelativeTypes].[Name] as RelativeTypeNameEn
	  ,dbo.[RelativeTypes].[Arabic_Name] as RelativeTypeNameAr
	 

	  
     
   
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].[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].[Relatives] on [dbo].Relatives.[EmployeeCode] = e.EmployeeCode
left join [dbo].[RelativeTypes] on [dbo].RelativeTypes.RelativeTypeCode = [dbo].[Relatives].RelativeTypeCode
left join [dbo].[HealthInsuranceTypes] on [dbo].HealthInsuranceTypes.HealthInsuranceTypeCode = [dbo].[EmployeesExtend].HealthInsuranceTypeCode



where [dbo].[SecurityUser].[EmployeeCode] = @userCode;


Go
-------------------------------End 2020-03-02-----------------------
-------------------------------Ali 2020-03-03-----------------------
ALTER TABLE dbo.TimePermissionsTypes ADD
	EffectInAppraisal bit NOT NULL DEFAULT (1)
GO

ALTER TABLE dbo.VacationType ADD
	EffectInAppraisal bit NOT NULL DEFAULT (1)
GO

  update [SystemMenu] set [ParentID]=52 , [Sequence]=7
  where SystemMenuCode=201
  GO
    update [SystemMenu] set [ParentID]=52 , [Sequence]=8
  where SystemMenuCode=215
  GO
-------------------------------End 2020-03-03-----------------------
-------------------------------Sayed 2020-03-04---------------


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]
      ,[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.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.[HealthInsuranceTypes].[Name] as MedicalInsuranceTypeNameEn
	  ,dbo.[HealthInsuranceTypes].[Arabic_Name] as MedicalInsuranceTypeNameAr
	



	 
	  ,dbo.Relatives.[MedicalIssuePlace] 
	  ,dbo.Relatives.[MedicalCardNumber] 
	  ,dbo.Relatives.[MedicalIssueDate] 
	  ,dbo.Relatives.[MedicalIssueDateHijri] 
	  ,dbo.Relatives.[MedicalExpireDate] 
	  ,dbo.Relatives.[MedicalExpireDateHijri] 
	  ,dbo.[RelativeTypes].[Name] as RelativeTypeNameEn
	  ,dbo.[RelativeTypes].[Arabic_Name] as RelativeTypeNameAr
	   ,dbo.[Relatives].[Name] as RelativesNameEn
	  ,dbo.[Relatives].[Arabic_Name] as RelativesNameAr
	 

	  
     
   
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].[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].[Relatives] on [dbo].Relatives.[EmployeeCode] = e.EmployeeCode
left join [dbo].[RelativeTypes] on [dbo].RelativeTypes.RelativeTypeCode = [dbo].[Relatives].RelativeTypeCode
left join [dbo].[HealthInsuranceTypes] on [dbo].HealthInsuranceTypes.HealthInsuranceTypeCode = [dbo].[EmployeesExtend].HealthInsuranceTypeCode



where [dbo].[SecurityUser].[EmployeeCode] = @userCode;



--------------------------End 2020-03-04-----------------------------