DECLARE @IDLanguage AS INT = 1;
SELECT e.IDEmployee, NULLIF(CONCAT(e.GivenName, ' ', e.Surname), ' ') AS EmployeeName, e.GivenName, e.Surname, e.FullName, u.IDUser, u.Username, e.BirthDate, e.IDNationality AS IDNationality, n.NamePT As Nationality, GD1.IDGenericData AS IDGender, COALESCE(TL1.Translation,T1.TranslationName) AS GenderDescription, GD6.IDGenericData AS IDMaritalStatus, COALESCE(TL6.Translation,T6.TranslationName) AS MaritalStatusDescription, u.UserImage AS EmployeeImage,
                                        (SELECT TOP 1 FolderRelativePath FROM SBI_FileGeneric f
                                        INNER JOIN SBI_FileGenericObject fo ON fo.IDFileObject = f.IDFileObject
                                        INNER JOIN SBI_FileGenericObjectType fot ON fot.IDFileObjectType = fo.IDFileObjectType
                                        WHERE fot.IDApplication = 9 AND fo.IDFileObjectType = 11 AND f.Deleted=0 AND IDObject = CONVERT(nvarchar(255), u.IDUser)) AS EmployeeImagePath,
                                        e.Email, e.Mobile, e.Landline, e.AddressLine1, e.AddressLine2, e.City, e.State, e.PostalCode, e.IDCountry AS IDCountry, c.namePT As Country, e.EmergencyContactName, e.EmergencyContactKinship, e.EmergencyContactMobile, e.EmergencyContactLandline, GD4.IDGenericData AS IDEducationLevel, COALESCE(TL4.Translation,T4.TranslationName) AS EducationLevelDescription, GD2.IDGenericData AS IDAcademicDegree, COALESCE(TL2.Translation,T2.TranslationName) AS AcademicDegreeDescription, e.Course, e.EducationalInstitution, e.CitizenIdentification, e.CitizenCardExpiryDate, e.SocialSecurityIdentification, e.DriversLicense, e.CarRegistration,
                                        e.EmployeeNumber, e.Active AS EmployeeActive, e.ProfessionalEmail, e.ProfessionalMobile, e.ProfessionalLandline, e.Extension, fc.StartDate AS AdmissionDate, p.IDProfession AS IDProfession, COALESCE(TL3.Translation,T3.TranslationName, p.ProfessionDescription) AS ProfessionDescription, pc.IDProfessionalCategory AS IDProfessionalCategory, COALESCE(TL7.Translation,T7.TranslationName,pc.CategoryDescription) AS ProfessionalCategoryDescription, e.IDDepartment, COALESCE(tl5.Translation, t5.TranslationName) AS Department, h.IDUser AS IDDepartmentHead, h.FullName AS DepartmentHead, ho.IDEmployee AS IDHierarchicalOfficer, NULLIF(CONCAT(ho.GivenName, ' ', ho.Surname), ' ') AS HierarchicalOfficer, e.DismissalDate, dr.ID AS IDDismissalReason, NULLIF(CONCAT(dr.Code, ' - ', COALESCE(TL13.Translation, T13.TranslationName, dr.Description)), ' - ') AS DismissalReasonDescription,  ir.IDIRCT AS IDIRCT, NULLIF(CONCAT(ir.IRCTCode, ' - ', COALESCE(TL10.Translation, T10.TranslationName, ir.IRCTDescription)), ' - ') AS IRCTDescription, GD11.IDGenericData AS IDIRCTApplicability, COALESCE(TL11.Translation,T11.TranslationName) AS IRCTApplicabilityDescription, i.IDInsurance AS IDInsurance, NULLIF(CONCAT(i.InsuranceCode, ' - ', COALESCE(TL12.Translation, T12.TranslationName, i.InsuranceDescription)), ' - ') AS InsuranceDescription, e.HourCost,
                                        e.TIN, e.DisabilityDegree, GD8.IDGenericData AS IDTaxMaritalStatus, COALESCE(TL8.Translation,T8.TranslationName) AS TaxMaritalStatusDescription, GD9.IDGenericData AS IDIncomeOwnership, COALESCE(TL9.Translation,T9.TranslationName) AS IncomeOwnershipDescription, e.SpouseName, e.SpouseTIN, e.SpouseDisabilityDegree, e.TaxAddressLine1, e.TaxAddressLine2, e.TaxCity, e.TaxState, e.TaxPostalCode, e.IDTaxCountry AS IDTaxCountry, t.namePT AS TaxCountry,
                                        es.HoursInFullDay, es.WeeklyWorkingHours, es.IDWorkPlan, wp.Name AS WorkPlanName, e.CreatedBy, e.CreatedOn, e.UpdatedBy, e.UpdatedOn
                                        FROM SIR_Employee e
										LEFT JOIN SBI_User u ON u.IDEmployee = e.IDEmployee
                                        LEFT JOIN SBI_GenericData GD1 ON e.IDGender = GD1.IDGenericData
                                        LEFT JOIN SBI_Translation T1 ON GD1.IDDescription = T1.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL1 ON TL1.IDTranslation = T1.IDTranslation AND TL1.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_Countries n ON n.Id = e.IDNationality
                                        LEFT JOIN SBI_GenericData GD6 ON e.IDMaritalStatus = GD6.IDGenericData
                                        LEFT JOIN SBI_Translation T6 ON GD6.IDDescription = T6.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL6 ON TL6.IDTranslation = T6.IDTranslation AND TL6.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_Countries c ON c.Id = e.IDCountry
                                        LEFT JOIN SBI_GenericData GD4 ON e.IDEducationLevel = GD4.IDGenericData
                                        LEFT JOIN SBI_Translation T4 ON GD4.IDDescription = T4.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL4 ON TL4.IDTranslation = T4.IDTranslation AND TL4.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_GenericData GD2 ON e.IDAcademicDegree = GD2.IDGenericData
                                        LEFT JOIN SBI_Translation T2 ON GD2.IDDescription = T2.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL2 ON TL2.IDTranslation = T2.IDTranslation AND TL2.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_Department d ON d.IDDepartment = e.IDDepartment
                                        LEFT JOIN SBI_Translation T5 ON T5.IDTranslation = d.IDDescription
                                        LEFT JOIN SBI_TranslationLanguage TL5 ON TL5.IDTranslation = T5.IDTranslation AND TL5.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_User h ON h.IDUser = d.IDResponsible
                                        LEFT JOIN SIR_Employee ho ON ho.IDEmployee = e.IDHierarchicalOfficer
                                        LEFT JOIN SIR_Staff_DismissalReason dr ON e.IDDismissalReason = dr.ID
										LEFT JOIN SBI_Translation T13 ON dr.IDDescription = T13.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL13 ON TL13.IDTranslation = T13.IDTranslation AND TL13.IDLanguage =  @IDLanguage
                                        LEFT JOIN SIR_Staff_IRCT ir ON e.IDIRCT = ir.IDIRCT
										LEFT JOIN SBI_Translation T10 ON ir.IDDescription = T10.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL10 ON TL10.IDTranslation = T10.IDTranslation AND TL10.IDLanguage =  @IDLanguage
                                        LEFT JOIN SBI_GenericData GD11 ON e.IDIRCTApplicability = GD11.IDGenericData
                                        LEFT JOIN SBI_Translation T11 ON GD11.IDDescription = T11.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL11 ON TL11.IDTranslation = T11.IDTranslation AND TL11.IDLanguage = @IDLanguage
                                        LEFT JOIN SIR_Staff_Insurance i ON e.IDInsurance = i.IDInsurance
										LEFT JOIN SBI_Translation T12 ON i.IDDescription = T12.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL12 ON TL12.IDTranslation = T12.IDTranslation AND TL12.IDLanguage =  @IDLanguage
										OUTER APPLY
                                            (SELECT TOP 1 ec.StartDate
                                                FROM SIR_Employee_Contract ec
                                                WHERE ec.IDEmployee = e.IDEmployee
                                                ORDER BY ec.StartDate ASC) AS fc
                                        OUTER APPLY
                                            (SELECT TOP 1 ec.IDProfession, ec.IDProfessionalCategory
                                                FROM SIR_Employee_Contract ec
                                                WHERE ec.IDEmployee = e.IDEmployee
                                                ORDER BY CASE
													WHEN ec.EndDate IS NULL THEN 1 ELSE 2 END, ec.EndDate DESC, ec.StartDate DESC) AS cc
                                        LEFT JOIN SIR_Profession p ON cc.IDProfession = p.IDProfession
                                        LEFT JOIN SBI_Translation T3 ON p.IDDescription = T3.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL3 ON TL3.IDTranslation = T3.IDTranslation AND TL3.IDLanguage = @IDLanguage
                                        LEFT JOIN SIR_ProfessionalCategory pc ON cc.IDProfessionalCategory = pc.IDProfessionalCategory
                                        LEFT JOIN SBI_Translation T7 ON pc.IDDescription = T7.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL7 ON TL7.IDTranslation = T7.IDTranslation AND TL7.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_GenericData GD8 ON e.IDTaxMaritalStatus = GD8.IDGenericData
                                        LEFT JOIN SBI_Translation T8 ON GD8.IDDescription = T8.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL8 ON TL8.IDTranslation = T8.IDTranslation AND TL8.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_GenericData GD9 ON e.IDIncomeOwnership = GD9.IDGenericData
                                        LEFT JOIN SBI_Translation T9 ON GD9.IDDescription = T9.IDTranslation
                                        LEFT JOIN SBI_TranslationLanguage TL9 ON TL9.IDTranslation = T9.IDTranslation AND TL9.IDLanguage = @IDLanguage
                                        LEFT JOIN SBI_Countries t ON t.Id = e.IDTaxCountry
                                        LEFT JOIN SIR_Employee_Settings es ON es.IDEmployee = e.IDEmployee
                                        LEFT JOIN SIR_TimeTracking_WorkPlan wp ON es.IDWorkPlan = wp.IDWorkPlan WHERE NULLIF(CONCAT(e.GivenName, ' ', e.Surname), ' ') LIKE CONCAT('%', '', '%') ORDER BY NULLIF(CONCAT(e.GivenName, ' ', e.Surname), ' ')