using _0_Framework.Application; using _0_Framework.Application.Enums; using _0_Framework.Exceptions; using _0_Framework.InfraStructure; using Company.Domain.ClientEmployeeWorkshopAgg; using Company.Domain.EmployeeAccountAgg; using Company.Domain.EmployeeAgg; using Company.Domain.EmployeeInsuranceRecordAgg; using Company.Domain.InsuranceListAgg; using Company.Domain.LeftWorkTempAgg; using CompanyManagment.App.Contracts.Employee; using CompanyManagment.App.Contracts.Employee.DTO; using CompanyManagment.App.Contracts.EmployeeInsuranceRecord; using CompanyManagment.App.Contracts.LeftWorkTemp; using Microsoft.Data.SqlClient; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Threading.Tasks; namespace CompanyManagment.EFCore.Repository; public class EmployeeRepository : RepositoryBase, IEmployeeRepository { private readonly IConfiguration _configuration; private readonly CompanyContext _context; public bool nationalCodValid = false; public bool idnumberIsOk = true; public bool nameIsOk = true; public bool nationalcodeIsOk = true; public bool StatCity = true; public bool city = true; public DateTime initial = new DateTime(1922, 01, 01, 00, 00, 00, 0000000); private readonly IAuthHelper _authHelper; public EmployeeRepository(CompanyContext context, IConfiguration configuration, IAuthHelper authHelper) : base(context) { _context = context; _configuration = configuration; _authHelper = authHelper; } public List GetEmployee() { return _context.Employees.Where(x => x.IsActive).Select(x => new EmployeeViewModel { Id = x.id, FName = x.FName, LName = x.LName, EmployeeFullName = x.FName + " " + x.LName, FatherName = x.FatherName, NationalCode = x.NationalCode, IdNumber = x.IdNumber, DateOfBirth = x.DateOfBirth == initial ? "" : x.DateOfBirth.ToFarsi(), Address = x.Address, State = x.State, City = x.City }).ToList(); } public EditEmployee GetDetails(long id) { return _context.Employees.Select(x => new EditEmployee { Id = x.id, FName = x.FName, LName = x.LName, Gender = x.Gender, NationalCode = x.NationalCode, IdNumber = x.IdNumber, Nationality = x.Nationality, FatherName = x.FatherName, DateOfBirth = x.DateOfBirth == initial ? "" : x.DateOfBirth.ToFarsi(), DateOfIssue = x.DateOfIssue == initial ? "" : x.DateOfIssue.ToFarsi(), PlaceOfIssue = x.PlaceOfIssue, Phone = x.Phone, Address = x.Address, State = x.State, City = x.City, MaritalStatus = x.MaritalStatus, MilitaryService = x.MilitaryService, LevelOfEducation = x.LevelOfEducation, FieldOfStudy = x.FieldOfStudy, BankCardNumber = x.BankCardNumber, BankBranch = x.BankBranch, InsuranceCode = x.InsuranceCode, InsuranceHistoryByYear = x.InsuranceHistoryByYear, InsuranceHistoryByMonth = x.InsuranceHistoryByMonth, NumberOfChildren = x.NumberOfChildren, OfficePhone = x.OfficePhone, EmployeeFullName = x.FName + " " + x.LName, MclsUserName = x.MclsUserName, MclsPassword = x.MclsPassword, EserviceUserName = x.EserviceUserName, EservicePassword = x.EservicePassword, TaxOfficeUserName = x.TaxOfficeUserName, TaxOfficepassword = x.TaxOfficepassword, SanaUserName = x.SanaUserName, SanaPassword = x.SanaPassword, }) .FirstOrDefault(x => x.Id == id); } public async Task> Search(EmployeeSearchModel searchModel) { List query = null; var connection = _configuration.GetConnectionString("MesbahDb"); using (var conn = new SqlConnection(connection)) { await conn.OpenAsync(); var command = new SqlCommand("EmployeesMainList", conn); command.CommandType = CommandType.StoredProcedure; using (var reader = await command.ExecuteReaderAsync()) { query = new List(); while (await reader.ReadAsync()) { var item = new EmployeeViewModel(); item.Id = (long)reader["Id"]; item.FName = (string)reader?["FName"]; item.LName = (string)reader["LName"]; item.NationalCode = reader["NationalCode"] != DBNull.Value ? (string)reader["NationalCode"] : null; item.IdNumber = reader["IdNumber"] != DBNull.Value ? (string)reader["IdNumber"] : null; item.Gender = reader["Gender"] != DBNull.Value ? (string)reader["Gender"] : null; var dateOBirth = (DateTime)reader["DateOfBirth"]; item.DateOfBirth = (DateTime)reader["DateOfBirth"] == initial ? "" : dateOBirth.ToFarsi(); item.InsuranceCode = reader["InsuranceCode"] != DBNull.Value ? (string)reader["InsuranceCode"] : null; item.IsActiveString = (string)reader["IsActiveString"]; item.EmployeeFullName = item.FName.Trim() + " " + item.LName.Trim(); //var children = _context.EmployeeChildrenSet.Count(x => x.EmployeeId == item.Id); //item.NumberOfChildren = Convert.ToString(children); query.Add(item); } } } //var query = result.Select(x => new EmployeeViewModel // { // Id = x.Id, // FName = x.FName, // LName = x.LName, // NationalCode = x.NationalCode, // IdNumber = x.IdNumber, // EmployeeFullName = x.FName + " " + x.LName, // IsActiveString = x.IsActiveString, // }); if (!string.IsNullOrWhiteSpace(searchModel.FName)) query = query.Where(x => x.FName.Contains(searchModel.FName)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.LName)) query = query.Where(x => x.LName.Contains(searchModel.LName)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.NationalCode)) query = query.Where(x => x.NationalCode.Contains(searchModel.NationalCode)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.IdNumber)) query = query.Where(x => x.IdNumber.Contains(searchModel.IdNumber)).ToList(); if (searchModel.Id > 0) query = query.Where(x => x.Id == searchModel.Id).ToList(); if (searchModel.IsActiveString == null) { query = query.Where(x => x.IsActiveString == "true").ToList(); } if (searchModel.IsActiveString == "false") { query = query.Where(x => x.IsActiveString == "false").ToList(); } else if (searchModel.IsActiveString == "both") { query = query.Where(x => x.IsActiveString == "false" || x.IsActiveString == "true").ToList(); } return query.OrderByDescending(x => x.Id).Take(100).ToList(); } public async Task> GetEmployeeToList() { var watch = System.Diagnostics.Stopwatch.StartNew(); List result = null; var connection = _configuration.GetConnectionString("MesbahDb"); using (var conn = new SqlConnection(connection)) { await conn.OpenAsync(); var command = new SqlCommand("EmployeeFullNameId", conn); command.CommandType = CommandType.StoredProcedure; using (var reader = await command.ExecuteReaderAsync()) { result = new List(); while (await reader.ReadAsync()) { var item = new EmployeeSelectListViewModel(); item.Id = (long)reader["Id"]; var fname = (string)reader["FName"]; var lname = (string)reader["LName"]; item.EmployeeFullName = fname.Trim() + " " + lname.Trim(); result.Add(item); } } } Console.WriteLine(watch.Elapsed); return result; } public List SearchInsuranceRecord(EmployeeInsuranceRecordSearchModel searchModel) { var query = _context.EmployeeInsuranceRecords .Include(c => c.Employee) .Include(c => c.Workshop).Select(x => new EmployeeInsuranceRecordViewModel { Id = x.id, EmployeeId = x.EmployeeId, WorkShopId = x.WorkShopId, DateOfStart = x.DateOfStart == initial ? "" : x.DateOfStart.ToFarsi(), DateOfEnd = x.DateOfEnd == initial ? "" : x.DateOfEnd.ToFarsi(), TotalDays = Tools.BetweenDateGeorgianToDay(x.DateOfStart, x.DateOfEnd), Employee = new EmployeeViewModel { Id = x.Workshop.id, FName = x.Employee.FName, LName = x.Employee.LName }, Workshop = new App.Contracts.Workshop.WorkshopViewModel { Id = x.Workshop.id, WorkshopFullName = x.Workshop.WorkshopFullName, IsActiveString = x.Workshop.IsActiveString } }); if (searchModel.EmployeeId != 0) query = query.Where(x => x.EmployeeId == searchModel.EmployeeId); return query.OrderByDescending(x => x.Id).ToList(); } public void CreateEmployeeInsuranceRecord(EmployeeInsuranceRecord entity) { _context.Add(entity); } public EmployeeInsuranceRecord GetEmployeeInsuranceRecord(long id) { return _context.Find(id); } public void RemoveEmployeeInsuranceRecord(long id) { var query = _context.EmployeeInsuranceRecords.Where(x => x.id == id).FirstOrDefault(); _context.Set().Remove(query); _context.SaveChanges(); } //بیمه public EditEmployee GetDetailsByADDate(long id) { return _context.Employees.Select(x => new EditEmployee { Id = x.id, FName = x.FName, LName = x.LName, Gender = x.Gender, NationalCode = x.NationalCode, IdNumber = x.IdNumber, Nationality = x.Nationality, FatherName = x.FatherName, DateOfBirthGr = x.DateOfBirth, DateOfIssueGr = x.DateOfIssue, DateOfBirth = x.DateOfBirth.ToFarsi(), DateOfIssue = x.DateOfIssue.ToFarsi(), PlaceOfIssue = x.PlaceOfIssue, Phone = x.Phone, Address = x.Address, State = x.State, City = x.City, MaritalStatus = x.MaritalStatus, MilitaryService = x.MilitaryService, LevelOfEducation = x.LevelOfEducation, FieldOfStudy = x.FieldOfStudy, BankCardNumber = x.BankCardNumber, BankBranch = x.BankBranch, InsuranceCode = x.InsuranceCode, InsuranceHistoryByYear = x.InsuranceHistoryByYear, InsuranceHistoryByMonth = x.InsuranceHistoryByMonth, NumberOfChildren = x.NumberOfChildren, OfficePhone = x.OfficePhone, EmployeeFullName = x.FName + " " + x.LName, MclsUserName = x.MclsUserName, MclsPassword = x.MclsPassword, EserviceUserName = x.EserviceUserName, EservicePassword = x.EservicePassword, TaxOfficeUserName = x.TaxOfficeUserName, TaxOfficepassword = x.TaxOfficepassword, SanaUserName = x.SanaUserName, SanaPassword = x.SanaPassword, }) .FirstOrDefault(x => x.Id == id); } public List GetEmployeeByTextSearch(string textSearch) { var empList = _context.Employees.FromSqlInterpolated($"SelectQuery_EmployeeTextSearch {textSearch}").AsNoTracking().ToList(); return empList.Select(x => new EmployeeViewModel() { Id = x.id, EmployeeFullName = x.FName + " " + x.LName, IsActive = x.IsActive }).Where(x => x.IsActive).ToList(); } #region Client public List SearchForClient(EmployeeSearchModel searchModel) { //var employeeIds = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == searchModel.WorkshopId).Select(x => x.EmployeeId).ToList(); //List query = null; //query = _context.Employees.Where(x => x.IsActive).Select(x => new EmployeeViewModel //{ // Id = x.id, // FName = x.FName, // LName = x.LName, // EmployeeFullName = x.FName + " " + x.LName, // FatherName = x.FatherName, // NationalCode = x.NationalCode, // IdNumber = x.IdNumber, // DateOfBirth = x.DateOfBirth == initial ? "" : x.DateOfBirth.ToFarsi(), // Address = x.Address, // State = x.State, // City = x.City, // Gender = x.Gender, // InsuranceCode = x.InsuranceCode, // IsActiveString = x.IsActiveString, //}).Take(200).ToList(); List query = null; query = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == searchModel.WorkshopId).Include(x => x.Employee).ToList().Select(x => new EmployeeViewModel { Id = x.Employee.id, FName = x.Employee.FName, LName = x.Employee.LName, EmployeeFullName = x.Employee.FName + " " + x.Employee.LName, FatherName = x.Employee.FatherName, NationalCode = x.Employee.NationalCode, IdNumber = x.Employee.IdNumber, DateOfBirth = x.Employee.DateOfBirth == initial ? "" : x.Employee.DateOfBirth.ToFarsi(), Address = x.Employee.Address, State = x.Employee.State, City = x.Employee.City, Gender = x.Employee.Gender, InsuranceCode = x.Employee.InsuranceCode, IsActiveString = x.Employee.IsActiveString, IsActive = x.Employee.IsActive, }).Where(x => x.IsActive).ToList(); //if (!string.IsNullOrWhiteSpace(searchModel.FName)) // query = query.Where(x => x.FName.Contains(searchModel.FName)).ToList(); //if (!string.IsNullOrWhiteSpace(searchModel.LName)) // query = query.Where(x => x.LName.Contains(searchModel.LName)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.EmployeeFullName)) query = query.Where(x => x.EmployeeFullName.Contains(searchModel.EmployeeFullName)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.NationalCode)) query = query.Where(x => x.NationalCode.Contains(searchModel.NationalCode)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.InsuranceCode)) query = query.Where(x => x.InsuranceCode != null && x.InsuranceCode.Contains(searchModel.InsuranceCode)).ToList(); if (searchModel.Id > 0) query = query.Where(x => x.Id == searchModel.Id).ToList(); if (searchModel.IsActiveString == null) { query = query.Where(x => x.IsActiveString == "true").ToList(); } if (searchModel.IsActiveString == "false") { query = query.Where(x => x.IsActiveString == "false").ToList(); } else if (searchModel.IsActiveString == "both") { query = query.Where(x => x.IsActiveString == "false" || x.IsActiveString == "true").ToList(); } return query.OrderByDescending(x => x.Id).ToList(); } public void Remove(long id) { var employee = _context.Employees.Where(x => x.id == id)?.FirstOrDefault(); _context.Employees.Remove(employee); } public List GetEmployeeByTextSearchForClient(string textSearch, long workshopId) { //var acountID = _authHelper.CurrentAccountId(); var empList = _context.Employees.FromSqlInterpolated($"SelectQuery_EmployeeTextSearchForClient {textSearch},{workshopId}").AsNoTracking().ToList(); return empList.Select(x => new EmployeeViewModel() { Id = x.id, SearchResultTitle = x.FName + " " + x.LName, }).ToList(); } public List GetEmployeeByTextSearchNationalCodeForClient(string textSearch, long workshopId) { //var acountID = _authHelper.CurrentAccountId(); var empList = _context.Employees.FromSqlInterpolated($"SelectQuery_EmployeeTextSearchNationalCodeForClient {textSearch},{workshopId}").AsNoTracking().ToList(); return empList.Select(x => new EmployeeViewModel() { Id = x.id, SearchResultTitle = x.NationalCode, }).ToList(); } public List GetEmployeeByTextSearchInsuranceCodeForClient(string textSearch, long workshopId) { //var acountID = _authHelper.CurrentAccountId(); var empList = _context.Employees.FromSqlInterpolated($"SelectQuery_EmployeeTextSearchInsuranceCodeForClient {textSearch},{workshopId}").AsNoTracking().ToList(); return empList.Select(x => new EmployeeViewModel() { Id = x.id, SearchResultTitle = x.InsuranceCode, }).ToList(); } public long CreateEmployeeForClient(CreateEmployee command) { using (var transaction = _context.Database.BeginTransaction()) { try { string initial = "1300/10/11"; var dateOfBirth = command.DateOfBirth != null ? command.DateOfBirth.ToGeorgianDateTime() : initial.ToGeorgianDateTime(); var dateOfIssue = command.DateOfIssue != null ? command.DateOfIssue.ToGeorgianDateTime() : initial.ToGeorgianDateTime(); var employeeData = new Employee(command.FName, command.LName, command.FatherName, dateOfBirth, dateOfIssue, command.PlaceOfIssue, command.NationalCode, command.IdNumber, command.Gender, command.Nationality, command.IdNumberSerial, command.IdNumberSeri, command.Phone, command.Address, command.State, command.City, command.MaritalStatus, command.MilitaryService, command.LevelOfEducation, command.FieldOfStudy, command.BankCardNumber, command.BankBranch, command.InsuranceCode, command.InsuranceHistoryByYear, command.InsuranceHistoryByMonth, command.NumberOfChildren, command.OfficePhone, command.MclsUserName, command.MclsPassword, command.EserviceUserName, command.EservicePassword, command.TaxOfficeUserName, command.TaxOfficepassword, command.SanaUserName, command.SanaPassword); Create(employeeData); _context.SaveChanges(); var acountID = _authHelper.CurrentAccountId(); //خودش var employeeAccount = new EmployeeAccount(); employeeAccount.AccountId = acountID; employeeAccount.EmployeeId = employeeData.id; _context.EmployeeAccounts.Add(employeeAccount); if (acountID != 3)//آقای مصباح { employeeAccount = new EmployeeAccount(); employeeAccount.AccountId = 3; employeeAccount.EmployeeId = employeeData.id; _context.EmployeeAccounts.Add(employeeAccount); } if (acountID != 2) //آقای فرخی { employeeAccount = new EmployeeAccount(); employeeAccount.AccountId = 2; employeeAccount.EmployeeId = employeeData.id; _context.EmployeeAccounts.Add(employeeAccount); } var clientEmployeeWorkshops = new ClientEmployeeWorkshop(); clientEmployeeWorkshops.WorkshopId = command.WorkshopId; clientEmployeeWorkshops.EmployeeId = employeeData.id; _context.ClientEmployeeWorkshops.Add(clientEmployeeWorkshops); _context.SaveChanges(); transaction.Commit(); return employeeData.id; } catch (Exception) { transaction.Rollback(); return 0; } } } public bool ExistsEmployeeAccountNationalCode(string nationalCode) { var acountID = _authHelper.CurrentAccountId(); var employeeIdstList = _context.EmployeeAccounts.Where(x => x.AccountId == acountID).Select(x => x.EmployeeId).ToList(); if (employeeIdstList != null && employeeIdstList.Count > 0) { return _context.Employees.Any(x => employeeIdstList.Contains(x.id) && x.NationalCode == nationalCode); } else return false; } public bool ExistsEmployeeAccountNationalCodeEmployeeId(string nationalcode, long id) { var acountID = _authHelper.CurrentAccountId(); var employerIdstList = _context.EmployeeAccounts.Where(x => x.AccountId == acountID && x.EmployeeId != id).Select(x => x.EmployeeId).ToList(); if (employerIdstList != null && employerIdstList.Count > 0) { return _context.Employees.Any(x => employerIdstList.Contains(x.id) && x.NationalCode == nationalcode); } else return false; } public bool ExistsEmployeeWorkshopNationalCode(string commandNationalCode, long commandWorkshopId) { var employerIdstList = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == commandWorkshopId).Select(x => x.EmployeeId).ToList(); if (employerIdstList != null && employerIdstList.Count > 0) { return _context.Employees.Any(x => employerIdstList.Contains(x.id) && x.NationalCode == commandNationalCode); } else return false; } public bool ExistsEmployeeWorkshopNationalCodeEmployeeId(string nationalCode, long workshopId, long id) { var employerIdstList = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == workshopId && x.EmployeeId != id).Select(x => x.EmployeeId).ToList(); if (employerIdstList != null && employerIdstList.Count > 0) { return _context.Employees.Any(x => employerIdstList.Contains(x.id) && x.NationalCode == nationalCode); } else return false; } public bool ExistsEmployeeWorkshoppInsuranceCode(string insuranceCode, long workshopId) { var employerIdstList = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == workshopId).Select(x => x.EmployeeId).ToList(); if (employerIdstList != null && employerIdstList.Count > 0) { return _context.Employees.Any(x => employerIdstList.Contains(x.id) && x.InsuranceCode == insuranceCode); } else return false; } public bool ExistsEmployeeWorkshopInsuranceCodeEmployeeId(string commandInsuranceCode, long commandWorkshopId, long commandId) { var employerIdstList = _context.ClientEmployeeWorkshops.Where(x => x.WorkshopId == commandWorkshopId && x.EmployeeId != commandId).Select(x => x.EmployeeId).ToList(); if (employerIdstList != null && employerIdstList.Count > 0) { return _context.Employees.Any(x => employerIdstList.Contains(x.id) && x.InsuranceCode == commandInsuranceCode); } else return false; } #endregion #region NewByHeydari public List SearchForMain(EmployeeSearchModel searchModel) { bool hasSearch = false; var currentDate = DateTime.Now.AddDays(1).Date; //var query = _context.Employees.Include(x=>x.LeftWorks).Include(x=>x.LeftWorkInsurances).Select(x => new EmployeeViewModel //{ // Id = x.id, // FName = x.FName, // LName = x.LName, // NationalCode = x.NationalCode, // IdNumber = x.IdNumber, // Gender = x.Gender, // DateOfBirth = (DateTime)x.DateOfBirth == initial ? "" : x.DateOfBirth.ToFarsi(), // InsuranceCode = x.InsuranceCode, // IsActiveString = x.IsActiveString, // EmployeeFullName = x.FName.Trim() + " " + x.LName.Trim(), // LeftWorkList = x.LeftWorks.Select(y=>new LeftWorkViewModel(){WorkshopId = y.WorkshopId,WorkshopName =y.WorkshopName,LeftWorkDateGr = y.LeftWorkDate}).Where(y=>y.LeftWorkDateGr.Date< currentDate).ToList(), // LeftWorkInsuranceList = x.LeftWorkInsurances.Select(y=>new LeftWorkInsuranceViewModel(){WorkshopId = y.WorkshopId,WorkshopName =y.WorkshopName,LeftWorkDateGr = y.LeftWorkDate}).Where(y => y.LeftWorkDateGr!=null && ((DateTime)y.LeftWorkDateGr).Date < currentDate).ToList(), //}).ToList(); var query = _context.Employees.Select(x => new EmployeeViewModel { Id = x.id, FName = x.FName, LName = x.LName, NationalCode = x.NationalCode, IdNumber = x.IdNumber, Gender = x.Gender, DateOfBirth = (DateTime)x.DateOfBirth == initial ? "" : x.DateOfBirth.ToFarsi(), InsuranceCode = x.InsuranceCode, IsActiveString = x.IsActiveString, IsActive = x.IsActive, EmployeeFullName = x.FName.Trim() + " " + x.LName.Trim(), }).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.NationalCode)) { hasSearch = true; query = query.Where(x => !string.IsNullOrEmpty(x.NationalCode) && x.NationalCode.Contains(searchModel.NationalCode)).ToList(); } if (searchModel.Id > 0) { hasSearch = true; var serchedById = query.FirstOrDefault(x => x.Id == searchModel.Id); query = query.Where(x => x.EmployeeFullName.Contains(serchedById.EmployeeFullName)).ToList(); } else if (!string.IsNullOrEmpty(searchModel.EmployeeName)) { hasSearch = true; query = query.Where(x => (!string.IsNullOrEmpty(x.FName) && x.FName.StartsWith(searchModel.EmployeeName)) || (!string.IsNullOrEmpty(x.LName) && x.LName.StartsWith(searchModel.EmployeeName)) || (x.FName + " " + x.LName).Contains(searchModel.EmployeeName)) .OrderByDescending(x => x.Id).ToList(); } #region workshop if (searchModel.WorkshopId > 0 || !string.IsNullOrEmpty(searchModel.WorkshopName)) { hasSearch = true; List employeeIds = new List(); if (searchModel.WorkshopId > 0) { employeeIds = _context.LeftWorkList.Where(x => x.WorkshopId == searchModel.WorkshopId).Select(x => x.EmployeeId).ToList(); employeeIds.AddRange(_context.LeftWorkInsuranceList.Where(x => x.WorkshopId == searchModel.WorkshopId).Select(x => x.EmployeeId).ToList()); } else if (!string.IsNullOrEmpty(searchModel.WorkshopName)) { employeeIds = _context.LeftWorkList.Where(x => x.WorkshopName.Contains(searchModel.WorkshopName)).Select(x => x.EmployeeId).ToList(); employeeIds.AddRange(_context.LeftWorkInsuranceList.Where(x => x.WorkshopName.Contains(searchModel.WorkshopName)).Select(x => x.EmployeeId).ToList()); } query = query.Where(x => employeeIds.Contains(x.Id)).ToList(); } #endregion #region employer if (searchModel.EmployerId > 0 || !string.IsNullOrEmpty(searchModel.EmployerLName)) { hasSearch = true; List employeeIds = new List(); List workshopIds = new List(); if (searchModel.EmployerId > 0) { workshopIds = _context.WorkshopEmployers.Where(x => x.EmployerId == searchModel.EmployerId).Select(x => x.WorkshopId).ToList(); } else if (!string.IsNullOrEmpty(searchModel.EmployerLName)) { var employerIds = _context.Employers.Where(x => (!string.IsNullOrEmpty(x.FName) && x.FName.Contains(searchModel.EmployerLName)) || (!string.IsNullOrEmpty(x.LName) && x.LName.Contains(searchModel.EmployerLName))).Select(x => x.id); workshopIds = _context.WorkshopEmployers.Where(x => employerIds.Contains(x.EmployerId)).Select(x => x.WorkshopId).ToList(); } employeeIds = _context.LeftWorkList.Where(x => workshopIds.Contains(x.WorkshopId)).Select(x => x.EmployeeId).ToList(); employeeIds.AddRange(_context.LeftWorkInsuranceList.Where(x => workshopIds.Contains(x.WorkshopId)).Select(x => x.EmployeeId).ToList()); query = query.Where(x => employeeIds.Contains(x.Id)).ToList(); } #endregion if (!string.IsNullOrEmpty(searchModel.InsuranceCode)) { hasSearch = true; query = query.Where(x => !string.IsNullOrEmpty(x.InsuranceCode) && x.InsuranceCode.Contains(searchModel.InsuranceCode)).ToList(); } if (!string.IsNullOrEmpty(searchModel.State)) { hasSearch = true; query = query.Where(x => x.State == searchModel.State).ToList(); } if (!string.IsNullOrEmpty(searchModel.City)) { hasSearch = true; query = query.Where(x => x.City == searchModel.City).ToList(); } switch (searchModel.IsActiveString) { case null: case "": query = query.Where(x => x.IsActive == true).ToList(); break; case "false": query = query.Where(x => x.IsActive == false).ToList(); hasSearch = true; break; case "both": query = query.Where(x => x.IsActive == true || x.IsActive == false).ToList(); hasSearch = true; break; } if (hasSearch) { return query.OrderByDescending(x => x.Id).ToList(); } else { return query.OrderByDescending(x => x.Id).Take(100).ToList(); } } public Employee GetByNationalCode(string nationalCode) { return _context.Employees.FirstOrDefault(x => x.NationalCode == nationalCode); } #endregion #region Mahan public List GetBy(List employeeIds) { return _context.Employees.Where(x => employeeIds.Contains(x.id)).Select(x => new EmployeeViewModel() { Id = x.id, EmployeeFullName = x.FullName }).ToList(); } public Employee GetByNationalCodeIgnoreQueryFilter(string nationalCode) { return _context.Employees.IgnoreQueryFilters().FirstOrDefault(x => x.NationalCode == nationalCode); } public async Task> GetClientEmployeesStartWork(long workshopId) { var res = await _context.Employees .GroupJoin(_context.LeftWorkTemps.Where(x => x.WorkshopId == workshopId && x.LeftWorkType == LeftWorkTempType.StartWork), employees => employees.id, leftWorkTemp => leftWorkTemp.EmployeeId, (employee, leftWorkTemps) => new { employee, leftWorkTemps }) .GroupJoin(_context.EmployeeDocuments.Where(x => x.WorkshopId == workshopId), (query) => query.employee.id, (employeeDoc) => employeeDoc.EmployeeId, (query, employeeDoc) => new { query, employeeDoc }).SelectMany(x => x.employeeDoc.DefaultIfEmpty(), (q, doc) => new { q.query, EmployeeDoc = doc }).Where(x => x.query.leftWorkTemps.Any(l => l.WorkshopId == workshopId && l.LeftWorkType == LeftWorkTempType.StartWork)).Select( x => new ClientStartedWorkEmployeesDto() { WorkshopId = workshopId, EmployeeId = x.query.employee.id, EmployeeName = x.query.employee.FName + " " + x.query.employee.LName, // ReSharper disable once SimplifyConditionalTernaryExpression HasCompleteEmployeeDocument = x.EmployeeDoc == null ? false : x.EmployeeDoc.IsConfirmed }).ToListAsync(); return res; } public async Task> GetEmployeesForLeftWorkTemp(long workshopId) { var res = await _context.Employees .Join(_context.LeftWorkTemps.Where(x => x.WorkshopId == workshopId && x.LeftWorkType == LeftWorkTempType.LeftWork), employees => employees.id, leftWorkTemp => leftWorkTemp.EmployeeId, (employee, leftWorkTemps) => new { employee, leftWorkTemps }) .Select( x => new ClientLeftWorkWorkEmployeesDto() { WorkshopId = workshopId, EmployeeId = x.employee.id, EmployeeName = x.employee.FName + " " + x.employee.LName, LeftWorkDateTime = x.leftWorkTemps.LeftWork.ToFarsi() }).ToListAsync(); return res; } public Employee GetIgnoreQueryFilter(long id) { return _context.Employees.IgnoreQueryFilters().FirstOrDefault(x => x.id == id); } public async Task> WorkedEmployeesInWorkshopSelectList(long workshopId) { var workshopActiveLeftWorksQuery = _context.LeftWorkList.Where(x => x.WorkshopId == workshopId); var workshopActiveInsuranceLeftWorksQuery = _context.LeftWorkInsuranceList.Where(x => x.WorkshopId == workshopId); var employeesQuery = _context.Employees.Where(x => workshopActiveLeftWorksQuery.Any(y => y.EmployeeId == x.id) || workshopActiveInsuranceLeftWorksQuery.Any(y => y.EmployeeId == x.id)).Select(x => new { leftWork = workshopActiveLeftWorksQuery.Where(l => l.EmployeeId == x.id).OrderByDescending(i => i.StartWorkDate).FirstOrDefault(), insuranceLeftWork = workshopActiveInsuranceLeftWorksQuery.Where(i => i.EmployeeId == x.id).OrderByDescending(i => i.StartWorkDate).FirstOrDefault(), Employee = x }); return (await employeesQuery.ToListAsync()).Select(x => { var leftWork = x.leftWork; var insuranceLeftWork = x.insuranceLeftWork; return new EmployeeSelectListViewModel() { Id = x.Employee.id, EmployeeFullName = x.Employee.FullName, Black = ((leftWork != null && leftWork.LeftWorkDate < DateTime.Now && insuranceLeftWork != null && insuranceLeftWork.LeftWorkDate != null) || (leftWork != null && insuranceLeftWork == null && leftWork.LeftWorkDate < DateTime.Now) || (insuranceLeftWork != null && insuranceLeftWork.LeftWorkDate != null && x.leftWork == null && insuranceLeftWork.LeftWorkDate != null)) ? true : false, }; }).OrderBy(x => x.Black).ToList(); } #endregion #region Pooya public List GetRangeByIds(IEnumerable newEmployeeIds) { return _context.Employees.Where(x => newEmployeeIds.Contains(x.id)).ToList(); } public List GetWorkingEmployeesByWorkshopId(long workshopId) { var dateNow = DateTime.Now.Date; var workshopActiveLeftWorksQuery = _context.LeftWorkList.Where(x => x.WorkshopId == workshopId && x.StartWorkDate <= dateNow && x.LeftWorkDate > dateNow); var workshopActiveInsuranceLeftWorksQuery = _context.LeftWorkInsuranceList.Where(x => x.WorkshopId == workshopId && x.StartWorkDate <= dateNow && (x.LeftWorkDate > dateNow || x.LeftWorkDate == null)); var employeesQuery = _context.Employees.Where(x => workshopActiveLeftWorksQuery.Any(y => y.EmployeeId == x.id) || workshopActiveInsuranceLeftWorksQuery.Any(y => y.EmployeeId == x.id)); return employeesQuery.Select(x => new EmployeeViewModel() { Id = x.id, EmployeeFullName = x.FullName }).ToList(); } public async Task GetEmployeeEditInEmployeeDocumentWorkFlow(long employeeId, long workshopId) { var employee = await _context.Employees.Where(x => x.id == employeeId).Select(x => new GetEditEmployeeInEmployeeDocumentViewModel() { EmployeeId = x.id, LName = x.LName, BirthDate = x.DateOfBirth.ToFarsi(), FName = x.FName, FatherName = x.FatherName, MaritalStatus = x.MaritalStatus, MilitaryService = x.MilitaryService, NationalCode = x.NationalCode, IdNumber = x.IdNumber, Nationality = x.Nationality, Gender = x.Gender, IsAuthorized = x.IsAuthorized }).FirstOrDefaultAsync(); var employeeClientTemp = await _context.EmployeeClientTemps.FirstOrDefaultAsync(x => x.EmployeeId == employeeId && x.WorkshopId == workshopId); if (employeeClientTemp != null) { employee.MaritalStatus = employeeClientTemp.MaritalStatus; } return employee; } public List<(long Id, string Name)> SimpleGetRangeByIds(IEnumerable newEmployeeIds) { return _context.Employees.Where(x => newEmployeeIds.Contains(x.id)).Select(x => new { Id = x.id, x.FName, x.LName }).AsEnumerable().Select(x => (x.Id, x.FName + " " + x.LName)).ToList(); } public List GetWorkingEmployeesByWorkshopIdsAndNationalCodeAndDate(List workshopIds, string nationalCode, DateTime date) { return _context.Employees.Where(x => x.NationalCode.Contains(nationalCode)).Include(x => x.LeftWorks).Include(x => x.LeftWorkInsurances) .Where(x => (x.LeftWorks.Any(y => workshopIds.Contains(y.WorkshopId) && y.StartWorkDate <= date && y.LeftWorkDate >= date)) || (x.LeftWorkInsurances.Any(y => workshopIds.Contains(y.WorkshopId) && y.StartWorkDate <= date && y.LeftWorkDate == null))) .Select(x => new EmployeeViewModel() { NationalCode = x.NationalCode, FName = x.FName, LName = x.LName, Phone = x.Phone }).ToList(); } public List GetWorkedEmployeesByWorkshopIdsAndNationalCodeAndDate(List workshopIds, string nationalCode, DateTime date) { return _context.Employees.Where(x => x.NationalCode.Contains(nationalCode)).Include(x => x.LeftWorks).Include(x => x.LeftWorkInsurances) .Where(x => x.LeftWorks.Any(y => workshopIds.Contains(y.WorkshopId)) || x.LeftWorkInsurances.Any(y => workshopIds.Contains(y.WorkshopId))) .Select(x => new EmployeeViewModel() { NationalCode = x.NationalCode, FName = x.FName, LName = x.LName, Phone = x.Phone }).ToList(); } #endregion #region Api public async Task> GetSelectList(string searchText, long id) { var query = _context.Employees.AsQueryable(); EmployeeSelectListViewModel idSelected = null; if (id > 0) { idSelected = await query.Select(x => new EmployeeSelectListViewModel() { Id = x.id, EmployeeFullName = x.FName + " " + x.LName }).FirstOrDefaultAsync(x => x.Id == id); } if (!string.IsNullOrWhiteSpace(searchText)) { query = query.Where(x => (x.FName + " " + x.LName).Contains(searchText)); } var list = await query.Take(100).Select(x => new EmployeeSelectListViewModel() { Id = x.id, EmployeeFullName = x.FName + " " + x.LName }).ToListAsync(); if (idSelected != null) list.Add(idSelected); return list.DistinctBy(x => x.Id).ToList(); } public async Task> GetList(GetEmployeeListSearchModel searchModel) { var query = _context.Employees.Include(x => x.LeftWorks).Include(x => x.LeftWorkInsurances).AsQueryable(); if (!string.IsNullOrWhiteSpace(searchModel.NationalCode)) { query = query.Where(x => x.NationalCode.Contains(searchModel.NationalCode)); } if (searchModel.EmployeeId > 0) { query = query.Where(x => x.id == searchModel.EmployeeId); } if (searchModel.WorkshopId > 0) { query = query.Where(x => x.LeftWorks.Any(l => l.WorkshopId == searchModel.WorkshopId) || x.LeftWorkInsurances.Any(l => l.WorkshopId == searchModel.WorkshopId)); } #region employer if (searchModel.EmployerId > 0) { var workshopIdsByEmployer = _context.WorkshopEmployers.Where(x => x.EmployerId == searchModel.EmployerId) .Include(x => x.Workshop).Select(x => x.Workshop.id).AsQueryable(); query = query.Where(x => x.LeftWorks.Any(l => workshopIdsByEmployer.Contains(l.WorkshopId)) || x.LeftWorkInsurances.Any(l => workshopIdsByEmployer.Contains(l.WorkshopId))); } #endregion if (!string.IsNullOrEmpty(searchModel.InsuranceCode)) { query = query.Where(x => x.InsuranceCode.Contains(searchModel.InsuranceCode)); } if (searchModel.EmployeeStatus != ActivationStatus.None) { var status = searchModel.EmployeeStatus switch { ActivationStatus.Active => true, ActivationStatus.DeActive => false, _ => throw new BadRequestException("پارامتر جستجو نامعتبر است") }; query = query.Where(x => x.IsActiveString == status.ToString() || x.IsActive == status); } var list = await query.Skip(searchModel.PageIndex).Take(30).ToListAsync(); var employeeIds = list.Select(x => x.id); var children = await _context.EmployeeChildrenSet.Where(x => employeeIds.Contains(x.EmployeeId)).ToListAsync(); var result = list.Select(x => new GetEmployeeListViewModel() { BirthDate = x.DateOfBirth.ToFarsi(), ChildrenCount = children.Count(c => c.EmployeeId == x.id).ToString(), EmployeeFullName = x.FullName, EmployeeStatus = x.IsActive switch { true => ActivationStatus.Active, false => ActivationStatus.DeActive }, Gender = x.Gender switch { "مرد" => Gender.Male, "زن" => Gender.Female, _ => Gender.None }, Id = x.id, InsuranceCode = x.InsuranceCode, NationalCode = x.NationalCode }).ToList(); return result; } public Task> GetClientEmployeeList(GetClientEmployeeListSearchModel searchModel, long workshopId) { // var leftDate = Tools.GetUndefinedDateTime(); // // var personnelCodes = _context.PersonnelCodeSet.Include(x => x.Employee) // .ThenInclude(x => x.EmployeeChildrenList).IgnoreQueryFilters().Where(x => x.WorkshopId == workshopId).ToList(); // // var contractLeftWork = // _context.LeftWorkList.Where(x => x.WorkshopId == workshopId) // .GroupBy(x => x.EmployeeId).Select(x => x.OrderByDescending(y => y.LeftWork).First()).ToList(); // // // // // var insuranceLeftWork = _context.LeftWorkInsuranceList.Where(x => x.WorkshopId == workshopId).Select(x => new // { // EmployeeId = x.EmployeeId, // FullName = x.EmployeeFullName, // PersonnelCode = 0, // InsurancePerson = true, // InsuranceLeft = x.LeftWorkDate != null, // StartWork = x.StartWorkDate, // LeftWork = x.LeftWorkDate ?? leftDate, // LastStartInsuranceWork = x.StartWorkDate.ToFarsi(), // LastLeftInsuranceWork = x.LeftWorkDate != null ? x.LeftWorkDate.ToFarsi() : "-", // LastStartContractWork = "-", // LastLeftContractWork = "-" // }).GroupBy(x => x.EmployeeId) // .Select(x => x.OrderByDescending(y => y.LeftWork).First()).ToList(); // // var leftWorkTemp = _context.LeftWorkTemps.Where(x => x.WorkshopId == workshopId).Select(x => new // { // WorkshopId = x.WorkshopId, // EmployeeId = x.EmployeeId, // PersonnelCode = 0, // ContractPerson = true, // ContractLeft = x.LeftWork != leftDate, // StartWork = x.StartWork, // LeftWork = x.LeftWork, // LastStartContractWork = x.StartWork.ToFarsi(), // LastLeftContractWork = x.LeftWork != leftDate ? x.LeftWork.ToFarsi() : "-", // LastStartInsuranceWork = "-", // LastLeftInsuranceWork = "-", // LefWorkTemp = x.LeftWorkType == LeftWorkTempType.LeftWork, // CreatedByClient = x.LeftWorkType == LeftWorkTempType.StartWork // }).ToList(); // // // var employeeClientTemp = _context.EmployeeClientTemps.Where(x => x.WorkshopId == workshopId).Select(x => new // { // WorkshopId = x.WorkshopId, // EmployeeId = x.EmployeeId, // PersonnelCode = 0, // CreatedByClient = true, // ContractPerson = true // }).ToList(); // // var resultTemp = employeeClientTemp.Concat(leftWorkTemp).ToList().GroupBy(x => x.EmployeeId); // // // // var result = contractLeftWork.Concat(insuranceLeftWork).GroupBy(x => x.EmployeeId).ToList(); // // result = result.Concat(resultTemp).GroupBy(x => x.First().EmployeeId).Select(x => x.First()).ToList(); // // var employeeClientTempList = employeeClientTemp.ToList(); // var startWorkTempsForWorkshop = leftWorkTemp.Where(x => x.CreatedByClient).ToList(); // var leftWorkTempsForWorkshop = leftWorkTemp.Where(x => x.LefWorkTemp).ToList(); // // return result.Select(x => // { // var insurance = x.FirstOrDefault(y => y.InsurancePerson); // var contract = x.FirstOrDefault(y => y.ContractPerson); // var personnelCode = personnelCodes.FirstOrDefault(y => y.EmployeeId == x.Key); // var employee = personnelCode.Employee; // var employeeClient = employeeClientTempList.FirstOrDefault(t => t.EmployeeId == x.First().EmployeeId); // var startWorkTemp = startWorkTempsForWorkshop.FirstOrDefault(s => s.EmployeeId == x.First().EmployeeId); // var leftWorkTemp = leftWorkTempsForWorkshop.FirstOrDefault(s => s.EmployeeId == x.First().EmployeeId); // // // return new GetClientEmployeeListViewModel() // { // EmployeeId = x.Key, // FullName = employee.FullName, // PersonnelCode = personnelCode?.PersonnelCode ?? 0, // HasContractLeftWork = insurance != null, // HasInsuranceLeftWork = contract != null, // // LeftWorkCompletely = (insurance?.InsuranceLeft ?? false) && (contract?.ContractLeft ?? false), // // LastStartInsuranceWork = insurance != null ? insurance.LastStartInsuranceWork : "-", // LastLeftInsuranceWork = insurance != null ? insurance.LastLeftInsuranceWork : "-", // LastStartContractWork = contract != null ? contract.LastStartContractWork : "-", // LastLeftContractWork = contract != null ? contract.LastLeftContractWork : "-", // // NationalCode = employee.NationalCode, // IdNumber = employee.IdNumber, // MaritalStatus = employee.MaritalStatus, // DateOfBirthFa = employee.DateOfBirth.ToFarsi(), // FatherName = employee.FatherName, // ChildrenCount = employee.EmployeeChildrenList.Count, // // PendingStartWork = employeeClient != null || startWorkTemp != null, // PendingLeftWork = leftWorkTemp != null, // // // }; // }).OrderByDescending(x => x.StartWork).ToList(); throw new NotImplementedException(); } public async Task> ListOfAllEmployeesClient(EmployeeSearchModelDto searchModel, long workshopId) { var hasNotStoppedWorkingYet = Tools.GetUndefinedDateTime(); var baseQuery = await ( from personnelCode in _context.PersonnelCodeSet join employee in _context.Employees on personnelCode.EmployeeId equals employee.id where personnelCode.WorkshopId == workshopId select new { Employee = employee, PersonnelCode = personnelCode } ).ToListAsync(); if (!string.IsNullOrWhiteSpace(searchModel.EmployeeFullName)) baseQuery = baseQuery.Where(x => x.Employee.FullName.Contains(searchModel.EmployeeFullName)).ToList(); if (!string.IsNullOrWhiteSpace(searchModel.NationalCode)) baseQuery = baseQuery.Where(x => x.Employee.NationalCode.Contains(searchModel.NationalCode)).ToList(); var employeeIds = baseQuery.Select(x => x.Employee.id).ToList(); var leftWorks = await _context.LeftWorkList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var insuranceLeftWorks = await _context.LeftWorkInsuranceList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var children = await _context.EmployeeChildrenSet.Where(x => employeeIds.Contains(x.EmployeeId)).ToListAsync(); var clientTemp = await _context.EmployeeClientTemps.Where(x => x.WorkshopId == workshopId) .Select(x => x.EmployeeId).ToListAsync(); var leftWorkTempData = await _context.LeftWorkTemps.Where(x => x.WorkshopId == workshopId).ToListAsync(); var startWorkTemp = leftWorkTempData.Where(x => x.LeftWorkType == LeftWorkTempType.StartWork).ToList(); var leftWorkTemp = leftWorkTempData.Where(x => x.LeftWorkType == LeftWorkTempType.LeftWork).ToList(); var result = baseQuery.Select(x => { var left = leftWorks .Where(l => l.EmployeeId == x.Employee.id) .MaxBy(l => l.StartWorkDate); var insuranceLeftWork = insuranceLeftWorks .Where(l => l.EmployeeId == x.Employee.id).MaxBy(l => l.StartWorkDate); var contractStart = left != null ? left.StartWorkDate.ToFarsi() : ""; var contractLeft = left != null ? left.LeftWorkDate != hasNotStoppedWorkingYet ? left.LeftWorkDate.ToFarsi() : "" : ""; var insuranceStart = insuranceLeftWork != null ? insuranceLeftWork.StartWorkDate.ToFarsi() : ""; var insuranceLeft = insuranceLeftWork != null ? insuranceLeftWork.LeftWorkDate != null ? insuranceLeftWork.LeftWorkDate.ToFarsi() : "" : ""; int personnelCode = Convert.ToInt32($"{x.PersonnelCode.PersonnelCode}"); int numberOfChildren = children.Count(ch => ch.EmployeeId == x.Employee.id); bool employeeHasLeft = (!string.IsNullOrWhiteSpace(insuranceLeft) && !string.IsNullOrWhiteSpace(contractLeft)) || (left == null && !string.IsNullOrWhiteSpace(insuranceLeft)) || (insuranceLeftWork == null && !string.IsNullOrWhiteSpace(contractLeft)); bool hasClientTemp = clientTemp.Any(c => c == x.Employee.id); bool hasStartWorkTemp = startWorkTemp.Any(st => st.EmployeeId == x.Employee.id); bool hasLeftWorkTemp = leftWorkTemp.Any(lf => lf.EmployeeId == x.Employee.id); return new EmployeeListDto { Id = x.Employee.id, EmployeeFullName = x.Employee.FullName, PersonnelCode = personnelCode, MaritalStatus = x.Employee.MaritalStatus, NationalCode = x.Employee.NationalCode, IdNumber = x.Employee.IdNumber, DateOfBirth = x.Employee.DateOfBirth.ToFarsi(), FatherName = x.Employee.FatherName, NumberOfChildren = $"{numberOfChildren}", LatestContractStartDate = contractStart, ContractLeftDate = contractLeft, LatestInsuranceStartDate = insuranceStart, InsuranceLeftDate = insuranceLeft, HasContract = !string.IsNullOrWhiteSpace(contractStart), HasInsurance = !string.IsNullOrWhiteSpace(insuranceStart), EmployeeStatusInWorkshop = hasClientTemp || hasStartWorkTemp ? EmployeeStatusInWorkshop.CreatedByClient : hasLeftWorkTemp ? EmployeeStatusInWorkshop.LefWorkTemp : employeeHasLeft ? EmployeeStatusInWorkshop.HasLeft : EmployeeStatusInWorkshop.Working, }; }).OrderBy(x => x.EmployeeStatusInWorkshop).ThenBy(x => x.PersonnelCode).ToList(); return result; } public async Task> PrintAllEmployeesInfoClient(long workshopId) { var res = await ListOfAllEmployeesClient(new EmployeeSearchModelDto(), workshopId); return res .Where(x=>x.EmployeeStatusInWorkshop != EmployeeStatusInWorkshop.CreatedByClient && x.EmployeeStatusInWorkshop != EmployeeStatusInWorkshop.LefWorkTemp) .Select(x => new PrintAllEmployeesInfoDtoClient(x)).ToList(); } public async Task> PrintAllDetailsPersonnelInfoClient(long workshopId) { var hasNotStoppedWorkingYet = Tools.GetUndefinedDateTime(); var baseQuery = await ( from personnelCode in _context.PersonnelCodeSet join employee in _context.Employees on personnelCode.EmployeeId equals employee.id where personnelCode.WorkshopId == workshopId select new { Employee = employee, PersonnelCode = personnelCode } ).ToListAsync(); var employeeIds = baseQuery.Select(x => x.Employee.id).ToList(); var leftWorks = await _context.LeftWorkList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var insuranceLeftWorks = await _context.LeftWorkInsuranceList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var children = await _context.EmployeeChildrenSet.Where(x => employeeIds.Contains(x.EmployeeId)).ToListAsync(); var result = baseQuery.Select(x => { var left = leftWorks .Where(l => l.EmployeeId == x.Employee.id) .MaxBy(l => l.StartWorkDate); var insuranceLeftWork = insuranceLeftWorks .Where(l => l.EmployeeId == x.Employee.id).MaxBy(l => l.StartWorkDate); var contractLeft = left != null ? left.LeftWorkDate != hasNotStoppedWorkingYet ? left.LeftWorkDate.ToFarsi() : "" : ""; var insuranceLeft = insuranceLeftWork != null ? insuranceLeftWork.LeftWorkDate != null ? insuranceLeftWork.LeftWorkDate.ToFarsi() : "" : ""; int personnelCode = Convert.ToInt32($"{x.PersonnelCode.PersonnelCode}"); int numberOfChildren = children.Count(ch => ch.EmployeeId == x.Employee.id); bool employeeHasLeft = (!string.IsNullOrWhiteSpace(insuranceLeft) && !string.IsNullOrWhiteSpace(contractLeft)) || (left == null && !string.IsNullOrWhiteSpace(insuranceLeft)) || (insuranceLeftWork == null && !string.IsNullOrWhiteSpace(contractLeft)); return new { EmployeeFullName = x.Employee.FullName, PersonnelCode = personnelCode, NationalCode = x.Employee.NationalCode, IdNumber = x.Employee.IdNumber, DateOfBirth = x.Employee.DateOfBirth.ToFarsi(), FatherName = x.Employee.FatherName, NumberOfChildren = $"{numberOfChildren}", Black = employeeHasLeft, State = x.Employee.State, City = x.Employee.City, Address = x.Employee.Address }; }).OrderBy(x => x.Black ? 1 : 0).ThenBy(x => x.PersonnelCode).ToList(); return result.Select(x => new PrintAllDetailsPersonnelInfoDtoClient { EmployeeFullName = x.EmployeeFullName, NationalCode = x.NationalCode, IdNumber = x.IdNumber, FatherName = x.FatherName, DateOfBirth = x.DateOfBirth, NumberOfChildren = x.NumberOfChildren, State = x.State, City = x.City, Address = x.Address }).ToList(); } public async Task> GetWorkingEmployeesSelectList(long workshopId) { var hasNotStoppedWorkingYet = Tools.GetUndefinedDateTime(); var baseQuery = await ( from personnelCode in _context.PersonnelCodeSet join employee in _context.Employees on personnelCode.EmployeeId equals employee.id where personnelCode.WorkshopId == workshopId select new { Employee = employee, PersonnelCode = personnelCode } ).ToListAsync(); var employeeIds = baseQuery.Select(x => x.Employee.id).ToList(); var leftWorks = await _context.LeftWorkList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var insuranceLeftWorks = await _context.LeftWorkInsuranceList .Where(x => x.WorkshopId == workshopId && employeeIds.Contains(x.EmployeeId)) .ToListAsync(); var result = baseQuery.Select(x => { var left = leftWorks .Where(l => l.EmployeeId == x.Employee.id) .MaxBy(l => l.StartWorkDate); var insuranceLeftWork = insuranceLeftWorks .Where(l => l.EmployeeId == x.Employee.id).MaxBy(l => l.StartWorkDate); var contractLeft = left != null ? left.LeftWorkDate != hasNotStoppedWorkingYet ? left.LeftWorkDate.ToFarsi() : "" : ""; var insuranceLeft = insuranceLeftWork != null ? insuranceLeftWork.LeftWorkDate != null ? insuranceLeftWork.LeftWorkDate.ToFarsi() : "" : ""; bool employeeHasLeft = (!string.IsNullOrWhiteSpace(insuranceLeft) && !string.IsNullOrWhiteSpace(contractLeft)) || (left == null && !string.IsNullOrWhiteSpace(insuranceLeft)) || (insuranceLeftWork == null && !string.IsNullOrWhiteSpace(contractLeft)); return new EmployeeSelectListViewModel { Id = x.Employee.id, EmployeeFullName = x.Employee.FullName, Black = employeeHasLeft, }; }).OrderBy(x => x.Black ? 1 : 0).ToList(); return result; } #endregion }