public IQueryable<Cat_JobTitle> GetCatJobTitles() { using (var context = new VnrHrmHrDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork); return repo.GetAllCatJobTitles().Where(i => i.IsDelete == null); } }
public Cat_JobTitle GetByIdCatJobTitles(int id) { using (var context = new VnrHrmHrDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork); Cat_JobTitle CatJobTitle = new Cat_JobTitle(); CatJobTitle = repo.GetById(id); if (CatJobTitle.IsDelete == true) CatJobTitle = null; return CatJobTitle; } }
public bool AddCatJobTitles(Cat_JobTitle CatJobTitle) { using (var context = new VnrHrmHrDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork); try { repo.Add(CatJobTitle); repo.SaveChanges(); return true; } catch { return false; } } }
public bool DeleteCatJobTitle(int CatJobTitleId) { using (var context = new VnrHrmHrDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork); Cat_JobTitle CatJobTitle = new Cat_JobTitle(); CatJobTitle = repo.GetById(CatJobTitleId); try { repo.Remove(CatJobTitle); repo.SaveChanges(); return true; } catch { return false; } } }
public List<Hre_ReportProfileQuitEntity> GetReportProfileQuit(DateTime? DateFrom, DateTime? DateToSearch, List<Guid> lstProfileIDs) { List<Hre_ReportProfileQuitEntity> lstReportProfileQuit = new List<Hre_ReportProfileQuitEntity>(); DateTime DateTo = DateToSearch.Value.AddDays(1).AddMinutes(-1); if (lstProfileIDs == null) { return lstReportProfileQuit; } using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoorgs = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoorgs.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); var repopostions = new Cat_PositionRepository(unitOfWork); var postions = repopostions.FindBy(s => s.IsDelete == null && s.PositionName != null).ToList(); var repojobtitles = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repojobtitles.FindBy(s => s.IsDelete == null && s.JobTitleName != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var reporesreason = new Cat_ResignReasonRepository(unitOfWork); var lstResReason = reporesreason.FindBy(s => s.IsDelete == null && s.ResignReasonName != null).ToList(); var repoprofiles = new Hre_ProfileRepository(unitOfWork); var profiles = repoprofiles.FindBy(s => s.IsDelete == null && s.DateQuit >= DateFrom && s.DateQuit <= DateTo && lstProfileIDs.Contains(s.ID)) .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.IDNo, s.CodeAttendance, s.DateEndProbation, s.ProbExtendDate, s.IDDateOfIssue, s.IDPlaceOfIssue, s.DateHire, s.WorkPlaceID, s.PAStreet, s.EmpTypeID, s.RequestDate, s.ResReasonID, s.Gender }).ToList(); foreach (var profile in profiles) { Hre_ReportProfileQuitEntity ReportProfileQuit = new Hre_ReportProfileQuitEntity(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var jobtitlebypro = jobtitles.Where(s => s.ID == profile.JobTitleID).FirstOrDefault(); var positiobbypro = postions.Where(s => s.ID == profile.PositionID).FirstOrDefault(); var resReasonbypro = lstResReason.Where(s => s.ID == profile.ResReasonID).FirstOrDefault(); ReportProfileQuit.CodeEmp = profile.CodeEmp; ReportProfileQuit.ProfileName = profile.ProfileName; ReportProfileQuit.CodeOrg = org != null ? org.Code : null; ReportProfileQuit.OrgStructureName = org != null ? org.OrgStructureName : null; ReportProfileQuit.JobTitleName = jobtitlebypro != null ? jobtitlebypro.JobTitleName : null; ReportProfileQuit.PositionName = positiobbypro != null ? positiobbypro.PositionName : null; ReportProfileQuit.DateHire = profile.DateHire; ReportProfileQuit.RequestDate = profile.RequestDate; ReportProfileQuit.DateQuit = profile.DateQuit; ReportProfileQuit.ResignReasonName = resReasonbypro != null ? resReasonbypro.ResignReasonName : null; lstReportProfileQuit.Add(ReportProfileQuit); } return lstReportProfileQuit; } }
public List<Hre_ReportExpiryContractEntity> GetReportExpiryContract(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs) { List<Hre_ReportExpiryContractEntity> lstReportExpiryContract = new List<Hre_ReportExpiryContractEntity>(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoContract = new Hre_ContractRepository(unitOfWork); var lstContract = repoContract.FindBy(s => s.DateEnd != null && s.DateEnd >= DateFrom && s.DateEnd <= DateTo && lstProfileIDs.Contains(s.ProfileID)).ToList(); if (lstContract == null) { return lstReportExpiryContract; } List<Guid> lstProfileIDsbycontract = lstContract.Select(s => s.ProfileID).ToList(); var repoProfile = new Hre_ProfileRepository(unitOfWork); var lstAllprofile = repoProfile.FindBy(s => lstProfileIDsbycontract.Contains(s.ID)).Select(s => new { s.ID, s.ProfileName, s.CodeEmp, s.OrgStructureID, s.PositionID, s.JobTitleID }).ToList(); var repoOrgStructure = new Cat_OrgStructureRepository(unitOfWork); var lstOrgStructure = repoOrgStructure.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.OrgStructureName }).ToList(); var repoPosition = new Cat_PositionRepository(unitOfWork); var lstPosition = repoPosition.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.PositionName }).ToList(); var repoJobtitle = new Cat_JobTitleRepository(unitOfWork); var lstJobtitle = repoJobtitle.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.JobTitleName }).ToList(); var repoContractType = new Cat_ContractTypeRepository(unitOfWork); var lstContractType = repoContractType.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.ContractTypeName, s.ContractNextID }).ToList(); foreach (var contract in lstContract) { Hre_ReportExpiryContractEntity ReportExpiryContract = new Hre_ReportExpiryContractEntity(); var profileByContract = lstAllprofile.Where(s => contract.ProfileID == s.ID).FirstOrDefault(); if (profileByContract == null) continue; var orgByProfile = lstOrgStructure.Where(s => profileByContract.OrgStructureID == s.ID).FirstOrDefault(); var positionByProfile = lstPosition.Where(s => contract.PositionID == s.ID).FirstOrDefault(); var jobtitleByProfile = lstJobtitle.Where(s => contract.JobTitleID == s.ID).FirstOrDefault(); var contractTypeByContract = lstContractType.Where(s => s.ID == contract.ContractTypeID).FirstOrDefault(); ReportExpiryContract.CodeEmp = profileByContract.CodeEmp; ReportExpiryContract.ProfileName = profileByContract.ProfileName; ReportExpiryContract.OrgStructureName = orgByProfile != null ? orgByProfile.OrgStructureName : ""; ReportExpiryContract.JobTitleName = jobtitleByProfile != null ? jobtitleByProfile.JobTitleName : ""; ReportExpiryContract.PositionName = positionByProfile != null ? positionByProfile.PositionName : ""; ReportExpiryContract.ContractTypeName = contractTypeByContract != null ? contractTypeByContract.ContractTypeName : ""; ReportExpiryContract.DateSigned = contract.DateSigned; ReportExpiryContract.DateStart = contract.DateStart; ReportExpiryContract.DateEnd = contract.DateEnd; lstReportExpiryContract.Add(ReportExpiryContract); } return lstReportExpiryContract; } }
/// <summary> /// Lấy Dữ Liệu BC Chi Tiết Nghỉ Phép Ốm của nhân viên /// </summary> /// <returns></returns> /// public DataTable GetReportLeaveYear(int dateYear, List<Guid?> leaveDayTypeIDs, List<Guid> lstProfileIDs, string userExport, string codeEmp) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); int year = dateYear; DateTime dateStart = new DateTime(year, 1, 1); DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); string key = OverTimeStatus.E_APPROVED.ToString(); var repoCat_Leaveday = new Cat_LeaveDayTypeRepository(unitOfWork); var leavedayTypes = repoCat_Leaveday.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PaidRate }).Distinct().ToList(); var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList(); var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd, s.LeaveDays }).ToList(); leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd, s.LeaveDays }).ToList(); leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.ProfileID)).ToList(); if (leaveDayTypeIDs[0] != null) { leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.LeaveDayTypeID)).ToList(); } var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); lstProfileIDs.AddRange(profileIds); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, }).ToList(); profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList(); if (!string.IsNullOrEmpty(codeEmp)) { char[] ext = new char[] { ';', ',' }; List<string> codeEmpSeachs = codeEmp.Split(ext, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); if (codeEmpSeachs.Count == 1) { string codeEmpSearch = codeEmpSeachs[0]; profiles = profiles.Where(hr => hr.CodeEmp == codeEmpSearch).ToList(); } else { profiles = profiles.Where(hr => codeEmpSeachs.Contains(hr.CodeEmp)).ToList(); } } string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString(); string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString(); var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork); var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year) .Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoAtt_Pregnancy = new Att_PregnancyRepository(unitOfWork); var pregnancys = repoAtt_Pregnancy.FindBy(s => s.DateStart != null && s.DateEnd != null && dateStart <= s.DateEnd && s.DateStart <= dateEnd && profileIds.Contains(s.ProfileID)) .Select(s => new { s.ProfileID, s.DateStart, s.DateEnd }).ToList(); Dictionary<string, string> dicSchemma = new Dictionary<string, string>(); DataTable tb = GetSchema(dateStart, dateEnd, out dicSchemma); int stt = 0; foreach (var profile in profiles) { stt++; DataRow dr = tb.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); dr[Att_ReportSickLeaveEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); dr[Att_ReportSickLeaveEntity.FieldNames.CodeEmp] = profile.CodeEmp; dr[Att_ReportSickLeaveEntity.FieldNames.ProfileName] = profile.ProfileName; dr[Att_ReportSickLeaveEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; dr[Att_ReportSickLeaveEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE); var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE); if (dicSchemma.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.SumANL)) { dr[dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL]] = anual != null ? (object)anual.Month3 : DBNull.Value; string SttOfData = dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL].Substring(4, dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL].Length - 4); if (stt == 1) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total P"; } else if (stt == 2) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total SC"; } } if (dicSchemma.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.SumSICK)) { dr[dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK]] = anualSick != null ? (object)anualSick.Month3 : DBNull.Value; string SttOfData = dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK].Substring(4, dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK].Length - 4); if (stt == 1) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total P"; } else if (stt == 2) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total SC"; } } //var pregnancyProfiles = pregnancys.Where(s => s.ProfileID == profile.ID).ToList(); //for (DateTime DateCheck = dateStart; DateCheck <= dateEnd; DateCheck = DateCheck.AddMonths(1)) //{ // int dayPregancy = 0; // foreach (var pregnancyProfile in pregnancyProfiles) // { // for (DateTime date = pregnancyProfile.DateStart.Value; date < pregnancyProfile.DateEnd.Value; date = date.AddDays(1)) // { // dayPregancy += 1; // } // } // foreach (var leaday in leavedayTypes) // { // if (dicSchemma.ContainsKey(leaday.Code + DateCheck.Month)) // { // var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.TotalDuration); // dr[dicSchemma[leaday.CodeStatistic + DateCheck.Month]] = sum > 0 ? (object)(sum - dayPregancy) : DBNull.Value; // string SttOfData = dicSchemma[leaday.CodeStatistic + DateCheck.Month].Substring(4, dicSchemma[leaday.CodeStatistic + DateCheck.Month].Length - 4); // if (stt == 1) // { // dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = DateCheck.ToString("MMM-yyyy"); // } // else if (stt == 2) // { // dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = leaday.CodeStatistic; // } // } // } //} //dr[Att_ReportSickLeaveEntity.FieldNames.DateFrom] = dateStart; //dr[Att_ReportSickLeaveEntity.FieldNames.DateTo] = dateEnd; //tb.Rows.Add(dr); var pregnancyProfiles = pregnancys.Where(s => s.ProfileID == profile.ID).ToList(); for (DateTime DateCheck = dateStart; DateCheck <= dateEnd; DateCheck = DateCheck.AddMonths(1)) { int dayPregancy = 0; foreach (var pregnancyProfile in pregnancyProfiles) { for (DateTime date = pregnancyProfile.DateStart.Value; date < pregnancyProfile.DateEnd.Value; date = date.AddDays(1)) { dayPregancy += 1; } } foreach (var leaday in leavedayTypes) { if (dicSchemma.ContainsKey(leaday.Code + DateCheck.Month)) { var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveDays); dr[dicSchemma[leaday.Code + DateCheck.Month]] = sum > 0 ? (object)(sum - dayPregancy) : DBNull.Value; string SttOfData = dicSchemma[leaday.Code + DateCheck.Month].Substring(4, dicSchemma[leaday.Code + DateCheck.Month].Length - 4); if (stt == 1) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = DateCheck.ToString("MMM-yyyy"); } else if (stt == 2) { dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = leaday.Code; } } } } dr[Att_ReportSickLeaveEntity.FieldNames.DateFrom] = dateStart; dr[Att_ReportSickLeaveEntity.FieldNames.DateTo] = dateEnd; dr[Att_ReportSickLeaveEntity.FieldNames.UserExport] = userExport; dr[Att_ReportSickLeaveEntity.FieldNames.DateExport] = DateTime.Today; tb.Rows.Add(dr); } return tb; } }
/// <summary> /// Lấy Dữ Liệu BC GetReportDetailForgetCard /// </summary> /// <returns></returns> public DataTable GetReportDetailForgetCard(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs, List<Guid?> ShiftIDs, string userExport) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); DataTable table = CreateReportDetailForgetCardSchema(); List<string> lstType = new List<string> { WorkdayType.E_MISS_IN.ToString(), WorkdayType.E_MISS_IN_OUT.ToString(), WorkdayType.E_MISS_OUT.ToString() }; var workDays = repoAtt_Workday.FindBy(s => DateFrom <= s.WorkDate && s.WorkDate <= DateTo && lstType.Contains(s.Type)) .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.FirstInTime, s.LastOutTime, s.Type, s.InTime1, s.OutTime1 }).ToList(); if (lstProfileIDs != null) { workDays = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).ToList(); } var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => (s.DateQuit == null || s.DateQuit > DateTo) && profileIds.Contains(s.ID)) .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.GetAll().ToList(); if (ShiftIDs[0] != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList(); } List<Guid> guids = profiles.Select(s => s.ID).ToList(); if (guids.Count > 0) { workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList(); } foreach (var profile in profiles) { for (DateTime date = DateFrom.Value.Date; date <= DateTo; date = date.AddDays(1)) { var workDayProfiles = workDays.Where(s => s.WorkDate.Date == date && s.ProfileID == profile.ID).ToList(); if (workDayProfiles.Count > 0) { DataRow row = table.NewRow(); //var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); //var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); Guid? orgId = profile.OrgStructureID; var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportDetailForgetCardEntity.FieldNames.GroupCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportDetailForgetCardEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportDetailForgetCardEntity.FieldNames.Division] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportDetailForgetCardEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportDetailForgetCardEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportDetailForgetCardEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportDetailForgetCardEntity.FieldNames.DateFrom] = DateFrom; row[Att_ReportDetailForgetCardEntity.FieldNames.DateTo] = DateTo; row[Att_ReportDetailForgetCardEntity.FieldNames.DateExport] = DateTime.Today; row[Att_ReportDetailForgetCardEntity.FieldNames.UserExport] = userExport; var workDay1 = workDayProfiles.FirstOrDefault(); row[Att_ReportDetailForgetCardEntity.FieldNames.Date] = date; if (workDay1.ShiftID != null) { var shift = shifts.Where(m => m.ID == workDay1.ShiftID).FirstOrDefault(); row[Att_ReportDetailForgetCardEntity.FieldNames.ShiftName] = shift != null ? shift.ShiftName : string.Empty; } if (workDay1.Type == WorkdayType.E_MISS_IN.ToString()) { row[Att_ReportDetailForgetCardEntity.FieldNames.OutTime] = workDay1.LastOutTime != null ? workDay1.LastOutTime.Value : DateTime.MinValue; } else if (workDay1.Type == WorkdayType.E_MISS_OUT.ToString()) { row[Att_ReportDetailForgetCardEntity.FieldNames.InTime] = workDay1.FirstInTime != null ? workDay1.FirstInTime.Value : DateTime.MaxValue; } row[Att_ReportDetailForgetCardEntity.FieldNames.ScanType] = workDay1.Type; table.Rows.Add(row); } } } return table; } #region Code Cũ //#region logic hien tai ////1. Chi tinh dc workday thieu in thieu out hoac thieu in out ////2. khong tinh dc nhưng quẹt thẻ da them bang tay boi vi khi them bang tay thì workday da du inout hok len dc bc //#endregion //List<Att_ReportDetailForgetCardEntity> lstReportDetailForgetCardEntity = new List<Att_ReportDetailForgetCardEntity>(); //string E_MISS_IN = WorkdayType.E_MISS_IN.ToString(); //string E_MISS_OUT = WorkdayType.E_MISS_OUT.ToString(); //string E_MISS_IN_OUT = WorkdayType.E_MISS_IN_OUT.ToString(); //#region getData //var lstWorkday = new List<Att_Workday>().Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList(); //using (var context = new VnrHrmDataContext()) //{ // var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); // var repo = new Att_WorkDayRepository(unitOfWork); // if (lstProfileIDs != null && lstProfileIDs.Count > 0) // { // lstWorkday = repo.FindBy(m => m.WorkDate >= DateFrom && m.WorkDate < DateTo // && m.ProfileID != null && lstProfileIDs.Contains(m.ProfileID) // && (m.Type == E_MISS_IN || m.Type == E_MISS_OUT || m.Type == E_MISS_IN_OUT)) // .Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList(); // } // else // { // lstWorkday = repo.FindBy(m => (m.WorkDate >= DateFrom && m.WorkDate < DateTo) // && (m.Type == E_MISS_IN || m.Type == E_MISS_OUT || m.Type == E_MISS_IN_OUT)) // .Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList(); // } //} //List<Guid> lstprofilebyworkday = lstWorkday.Select(m => m.ProfileID).ToList(); //var lstProfile = new List<Hre_Profile>().Select(m => new { m.ID, m.OrgStructureID, m.CodeEmp, m.ProfileName }); //using (var context = new VnrHrmDataContext()) //{ // var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); // var repo = new Hre_ProfileRepository(unitOfWork); // if (lstprofilebyworkday != null && lstprofilebyworkday.Count > 0) // { // lstProfile = repo.FindBy(m => lstprofilebyworkday.Contains(m.ID)).Select(m => new { m.ID, m.OrgStructureID, m.CodeEmp, m.ProfileName }).ToList(); // } //} //var lstShift = new List<Cat_Shift>().Select(m => new { m.ID, m.ShiftName, m.InTime, m.CoOut }).ToList(); //using (var context = new VnrHrmDataContext()) //{ // var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); // var repo = new Cat_ShiftRepository(unitOfWork); // lstShift = repo.GetAll().Select(m => new { m.ID, m.ShiftName, m.InTime, m.CoOut }).ToList(); //} //#endregion //#region get org nhieu cap //List<OrgTiny> lstOrgAll = new List<OrgTiny>(); //using (var context = new VnrHrmDataContext()) //{ // var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); // var repoOrg = new Cat_OrgStructureRepository(unitOfWork); // lstOrgAll = repoOrg.FindBy(s => s.IsDelete == null).Select(m => new OrgTiny { ID = m.ID, OrgCode = m.Code, OrgName = m.OrgStructureName, ParentID = m.ParentID, TypeID = m.TypeID }).ToList(); //} //List<Guid?> lstOrgIDs = lstProfile.Select(m => m.OrgStructureID).Distinct().ToList(); //Dictionary<Guid?, OrgLevelTypeName> OrgNameAllLevel = Cat_OrgStructureServices.GetFullLinkOrg(lstOrgIDs, lstOrgAll); //#endregion //foreach (var ProfileID in lstprofilebyworkday) //{ // if (ProfileID == null) // continue; // var lstWorkdayByProfile = lstWorkday.Where(m => m.ProfileID == ProfileID).ToList(); // foreach (var WorkdayByProfile in lstWorkdayByProfile) // { // if (WorkdayByProfile.WorkDate == null) // { // continue; // } // Att_ReportDetailForgetCardEntity ReportDetailForgetCardEntity = new Att_ReportDetailForgetCardEntity(); // var profileInfomation = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault(); // #region thông tin chung // ReportDetailForgetCardEntity.DateFrom = DateFrom; // ReportDetailForgetCardEntity.DateTo = DateTo; // #endregion // #region thông tin Profile // if (profileInfomation != null) // { // ReportDetailForgetCardEntity.ProfileName = profileInfomation.ProfileName; // ReportDetailForgetCardEntity.CodeEmp = profileInfomation.CodeEmp; // } // #endregion // #region thông tin Phòng ban // Guid? orgID = null; // if (profileInfomation != null) // { // orgID = profileInfomation.OrgStructureID; // } // if (orgID != null) // { // var orgByProfile = lstOrgAll.Where(m => m.ID == orgID).FirstOrDefault(); // if (orgByProfile != null) // { // ReportDetailForgetCardEntity.DepartmentName = orgByProfile.OrgName; // ReportDetailForgetCardEntity.Division = orgByProfile.OrgCode; // } // } // if (orgID != null && OrgNameAllLevel.ContainsKey(orgID)) // { // var OrgNameFull = OrgNameAllLevel[orgID]; // if (OrgNameFull != null) // { // ReportDetailForgetCardEntity.SectionCode = OrgNameFull.SectionCode; // ReportDetailForgetCardEntity.GroupCode = OrgNameFull.TeamCode; // } // } // #endregion // #region thông tin chấm công // ReportDetailForgetCardEntity.Date = WorkdayByProfile.WorkDate; // if (WorkdayByProfile.ShiftID != null) // { // var Shift = lstShift.Where(m => m.ID == WorkdayByProfile.ShiftID).FirstOrDefault(); // if (Shift != null) // { // ReportDetailForgetCardEntity.ShiftName = Shift.ShiftName; // } // } // #endregion // #region thoi gian vao ra // if (WorkdayByProfile.Type == E_MISS_IN) // { // ReportDetailForgetCardEntity.OutTime = WorkdayByProfile.LastOutTime; // } // else if (WorkdayByProfile.Type == E_MISS_OUT) // { // ReportDetailForgetCardEntity.InTime = WorkdayByProfile.FirstInTime; // } // ReportDetailForgetCardEntity.ScanType = WorkdayByProfile.Type; // #endregion // lstReportDetailForgetCardEntity.Add(ReportDetailForgetCardEntity); // } //} //return lstReportDetailForgetCardEntity; #endregion }
public DataTable GetReportMonthlyHourFlightLocal(Guid CutOffDurationID, string strOrgStructure, Guid[] shiftIDs, Guid[] payrollIDs, Guid[] workPlaceIDs, string codeEmp, bool isIncludeQuitEmp, bool isNotAllowZero, bool isCreateTemplate, string userExport, string UserLogin) { using (var context = new VnrHrmDataContext()) { DataTable table = CreateReportMonthlyHourFlightLocalchema(UserLogin); if (isCreateTemplate) { return table.ConfigDatatable(); } string status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoWorkDay = new Att_WorkDayRepository(unitOfWork); var repoTimeSheet = new Att_TimeSheetRepository(unitOfWork); var repoJobtype = new Cat_JobTypeRepository(unitOfWork); var repoAtt_AttendencaTable = new CustomBaseRepository<Att_AttendanceTable>(unitOfWork); var repoAtt_AttendanceTableItem = new CustomBaseRepository<Att_AttendanceTableItem>(unitOfWork); var repoAtt_CutOffDuration = new CustomBaseRepository<Att_CutOffDuration>(unitOfWork); var roleServices = new Cat_RoleServices(); var lstRole = new List<object>(); lstRole.Add(null); lstRole.Add(null); lstRole.Add(1); lstRole.Add(100000000); var listRole = roleServices.GetData<Cat_RoleEntity>(lstRole, ConstantSql.hrm_cat_sp_get_Role, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.ID, m.Code }).ToList(); var jobTypeServices = new Cat_JobTypeServices(); var lstjobType = new List<object>(); lstjobType.Add(null); lstjobType.Add(null); lstjobType.Add(1); lstjobType.Add(100000000); var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => m.Code).ToList(); List<object> lstOrgIDs = new List<object>(); lstOrgIDs.AddRange(new object[3]); lstOrgIDs[0] = (object)strOrgStructure; lstOrgIDs[1] = null; lstOrgIDs[2] = codeEmp; List<Hre_ProfileEntity> profiles = GetData<Hre_ProfileEntity>(lstOrgIDs, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList(); var cutoffInfo = repoAtt_CutOffDuration.FindBy(s => s.ID == CutOffDurationID).FirstOrDefault(); var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList(); DateTime dateStart = cutoffInfo.DateStart; DateTime dateEnd = cutoffInfo.DateEnd; var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); List<Guid> profileIds = profiles.Select(s => s.ID).ToList(); var timesheets = repoTimeSheet.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && s.Date != null && dateStart <= s.Date && s.Date <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.JobTypeID, s.RoleID, s.Date, s.NoHour, s.ID, s.Note }).ToList(); var jobtypes = repoJobtype.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.Code, s.RoleID }).ToList(); var workDays = repoWorkDay.FindBy(s => profileIds.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.ShiftID, s.Status }).ToList(); if (payrollIDs != null) { profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList(); } if (workPlaceIDs != null) { profiles = profiles.Where(s => s.WorkPlaceID != null && workPlaceIDs.Contains(s.WorkPlaceID.Value)).ToList(); } if (shiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } profileIds = profiles.Select(s => s.ID).ToList(); var attendanceTables = repoAtt_AttendencaTable .FindBy(s => s.IsDelete == null && s.CutOffDurationID == CutOffDurationID && profileIds.Contains(s.ProfileID)) .ToList(); List<Guid> attendanceTablesID = attendanceTables.Select(s => s.ID).ToList(); var attendanceTableItem = repoAtt_AttendanceTableItem .FindBy(s => s.IsDelete == null && attendanceTablesID.Contains(s.AttendanceTableID)) .ToList(); if (isNotAllowZero) { profileIds = attendanceTables.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } profileIds = attendanceTables.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); foreach (var profile in profiles) { var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID); var timeSheetByProfile = timesheets.Where(s => s.ProfileID == profile.ID).ToList(); if (timeSheetByProfile == null) { continue; } var lstjobtypebytimesheetpro = timeSheetByProfile.Select(s => s.JobTypeID).ToList(); var lstJobTypeCodeByTimeSheet = jobtypes.Where(s => lstjobtypebytimesheetpro.Contains(s.ID)).Select(s => new { s.ID, s.Code, s.RoleID }).ToList(); if (attendanceTableProfile == null) { continue; } var attendanceTableItemProfile = attendanceTableItem.Where(s => s.AttendanceTableID == attendanceTableProfile.ID).ToList(); DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; if (profile.DateHire != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StartingDate] = profile.DateHire.Value; if (profile.DateQuit != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ResignedDate] = profile.DateQuit.Value; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateFrom] = dateStart; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateTo] = dateEnd; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport] = userExport; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateExport] = DateTime.Today; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? attendanceTableProfile.StdWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? attendanceTableProfile.RealWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? attendanceTableProfile.PaidWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? attendanceTableProfile.AnlDayAvailable : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? attendanceTableProfile.NightShiftHours : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? attendanceTableProfile.LateEarlyDeductionHours : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? attendanceTableProfile.AnlDayTaken : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty; foreach (var item in attendanceTableItemProfile) { if (item.LeaveTypeID != null) { row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = leavedayTypes.Where(s => s.ID == item.LeaveTypeID).FirstOrDefault().Code; } } foreach (var roleEntity in listRole) { var lstJobTypeByRoleID = lstJobTypeCodeByTimeSheet.Where(s => s.RoleID == roleEntity.ID).ToList(); foreach (var jobcode in lstJobTypeByRoleID) { var CodeRoleAndJobType = roleEntity.Code + "_" + jobcode.Code; if (table.Columns.Contains(CodeRoleAndJobType)) { double? nohourByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID && s.RoleID == roleEntity.ID).Sum(s => s.NoHour); row[CodeRoleAndJobType] = nohourByPro; } } } table.Rows.Add(row); } return table.ConfigTable(); } }
public DataTable GetReportMonthlyTimeSheet(DateTime? dateStart, DateTime DateTo, string strOrgStructure, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, Guid[] payrollIDs, bool isIncludeQuitEmp, bool isNotAllowZero, Guid exportid, string UserLogin) { using (var context = new VnrHrmDataContext()) { DataTable table = CreateReportMonthlyTimeSheetSchema(UserLogin); dateStart = new DateTime(dateStart.Value.Year, dateStart.Value.Month, 1); DateTime monthEnd = new DateTime(dateStart.Value.Year, dateStart.Value.Month, DateTime.DaysInMonth(dateStart.Value.Year, dateStart.Value.Month)); monthEnd = monthEnd.AddDays(1).AddMilliseconds(-1); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoProfile = new Hre_ProfileRepository(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).ToList(); var codeCenters = unitOfWork.CreateQueryable<Cat_CostCentre>(Guid.Empty, s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); List<Guid> profileIds = profiles.Select(s => s.ID).ToList(); string status = string.Empty; List<object> para_reportWorDay = new List<object>(); para_reportWorDay.AddRange(new object[2]); para_reportWorDay[0] = dateStart; para_reportWorDay[0] = monthEnd; var repoWorkDay = new Att_WorkDayServices(); List<Att_WorkdayEntity> workDays = repoWorkDay.GetData<Att_WorkdayEntity>(para_reportWorDay, ConstantSql.hrm_att_sp_getdata_reportWorDay, UserLogin, ref status).ToList(); workDays = workDays.Where(s => profileIds.Contains(s.ProfileID)).ToList(); if (payrollIDs != null) { profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList(); } if (shiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } profileIds = profiles.Select(s => s.ID).ToList(); var repoAtt_AttendencaTable = new Att_AttendanceTableRepository(unitOfWork); List<object> para = new List<object>(); para.AddRange(new object[7]); para[3] = dateStart; para[4] = monthEnd; para[5] = 1; para[6] = int.MaxValue; var attService = new Att_AttendanceServices(); List<Att_AttendanceTableEntity> lstAttendanceTable = attService.GetData<Att_AttendanceTableEntity>(para, ConstantSql.hrm_att_sp_get_attdancetable, UserLogin, ref status).ToList(); var attendanceTables = lstAttendanceTable.Where(s => profileIds.Contains(s.ProfileID)).ToList(); profileIds = attendanceTables.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); foreach (var profile in profiles) { var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID); if (attendanceTableProfile == null) { continue; } DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var cost = codeCenters.FirstOrDefault(s => s.ID == profile.CostCentreID); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportMonthlyTimeSheetEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeCenter] = cost != null ? cost.Code : string.Empty; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? (object)attendanceTableProfile.StdWorkDayCount : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? (object)attendanceTableProfile.RealWorkDayCount : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? (object)attendanceTableProfile.PaidWorkDayCount : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? (object)attendanceTableProfile.AnlDayAvailable : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? (object)attendanceTableProfile.NightShiftHours : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? (object)attendanceTableProfile.LateEarlyDeductionHours : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? (object)attendanceTableProfile.AnlDayTaken : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty; #region Export theo cot dong if (exportid != Guid.Empty) { var leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay1Type); if (leaday != null && !string.IsNullOrEmpty(leaday.Code) && table.Columns.Contains(leaday.Code)) { row[leaday.Code] = attendanceTableProfile.LeaveDay1Hours > 0 ? (object)attendanceTableProfile.LeaveDay1Hours : DBNull.Value; } leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay2Type); if (leaday != null) { row[leaday.Code] = attendanceTableProfile.LeaveDay2Hours > 0 ? (object)attendanceTableProfile.LeaveDay2Hours : DBNull.Value; } leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay3Type); if (leaday != null) { row[leaday.Code] = attendanceTableProfile.LeaveDay3Hours > 0 ? (object)attendanceTableProfile.LeaveDay3Hours : DBNull.Value; } leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay4Type); if (leaday != null) { row[leaday.Code] = attendanceTableProfile.LeaveDay4Hours > 0 ? (object)attendanceTableProfile.LeaveDay4Hours : DBNull.Value; } var overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime1Type); if (overtimeType != null) { // row[overtimeType.Code] = attendanceTableProfile.Overtime1Hours > 0 ? (object)attendanceTableProfile.Overtime1Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime1Hours > 0 ? (object)attendanceTableProfile.Overtime1Hours : DBNull.Value; } overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime2Type); if (overtimeType != null) { // row[overtimeType.Code] = attendanceTableProfile.Overtime2Hours > 0 ? (object)attendanceTableProfile.Overtime2Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime2Hours > 0 ? (object)attendanceTableProfile.Overtime2Hours : DBNull.Value; } overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime3Type); if (overtimeType != null) { //row[overtimeType.Code] = attendanceTableProfile.Overtime3Hours > 0 ? (object)attendanceTableProfile.Overtime3Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime3Hours > 0 ? (object)attendanceTableProfile.Overtime3Hours : DBNull.Value; } overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime4Type); if (overtimeType != null) { //row[overtimeType.Code] = attendanceTableProfile.Overtime4Hours > 0 ? (object)attendanceTableProfile.Overtime4Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime4Hours > 0 ? (object)attendanceTableProfile.Overtime4Hours : DBNull.Value; } overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime5Type); if (overtimeType != null) { //row[overtimeType.Code] = attendanceTableProfile.Overtime5Hours > 0 ? (object)attendanceTableProfile.Overtime5Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime5Hours > 0 ? (object)attendanceTableProfile.Overtime5Hours : DBNull.Value; } overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime6Type); if (overtimeType != null) { //row[overtimeType.Code] = attendanceTableProfile.Overtime6Hours > 0 ? (object)attendanceTableProfile.Overtime6Hours : DBNull.Value; row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime6Hours > 0 ? (object)attendanceTableProfile.Overtime6Hours : DBNull.Value; } row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateEarly] = (object)attendanceTableProfile.CardMissingCount ?? DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateLess2H] = (object)attendanceTableProfile.LateEarlyLeastCount ?? DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateMore2H] = (object)attendanceTableProfile.LateEarlyGreaterCount ?? DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.UsableLeave] = attendanceTableProfile.TotalAnlDayAvailable > 0 ? (object)attendanceTableProfile.TotalAnlDayAvailable : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickLeave] = attendanceTableProfile.TotalSickDayAvailable > 0 ? (object)attendanceTableProfile.TotalSickDayAvailable : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CurrentMonth] = attendanceTableProfile.AnlDayTaken > 0 ? (object)attendanceTableProfile.AnlDayTaken : DBNull.Value; //row["SickCurrentMonth"] = attendanceTableProfile.SickDayTaken > 0 ? (object)attendanceTableProfile.SickDayTaken : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickCurrentMonth] = attendanceTableProfile.SickDayTaken > 0 ? (object)attendanceTableProfile.SickDayTaken : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.YearToDate] = attendanceTableProfile.AnlDayAdjacent > 0 ? (object)attendanceTableProfile.AnlDayAdjacent : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickYearToDate] = attendanceTableProfile.SickDayAdjacent > 0 ? (object)attendanceTableProfile.SickDayAdjacent : DBNull.Value; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SumMuteLateEarly] = (object)attendanceTableProfile.LateEarlyTotal ?? DBNull.Value; } #endregion table.Rows.Add(row); } if (exportid != Guid.Empty) { return table.ConfigDatatable(); } return table; } }
/// <summary> /// Lấy Dữ Liệu BC Thống Kê Đi Muộn Về Sớm /// </summary> /// <returns></returns> public List<Att_ReportSummaryLateInOutEntity> GetReportSummaryLateInOut(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, bool isIncludeQuitEmp, string codeEmp, string userExport) { using (var context = new VnrHrmDataContext()) { List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoShift = new Cat_ShiftRepository(unitOfWork); var repoCat_TAMReasonmiss = new Cat_TAMScanReasonMissRepository(unitOfWork); string E_LATE_EARLY = WorkdayType.E_LATE_EARLY.ToString(); string E_APPROVED = WorkdayStatus.E_APPROVED.ToString(); var workDays = new List<Att_Workday>().Select(s => new { s.ProfileID, s.WorkDate, s.LateEarlyDuration, s.ShiftID, s.InTime1, s.OutTime1, s.MissInOutReason, s.LateDuration1, s.EarlyDuration1 }).ToList(); workDays = repoAtt_WorkDay.FindBy(s => s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.Type == E_LATE_EARLY && !(s.Status == E_APPROVED && s.LateEarlyDuration == 0)) .Select(s => new { s.ProfileID, s.WorkDate, s.LateEarlyDuration, s.ShiftID, s.InTime1, s.OutTime1, s.MissInOutReason, s.LateDuration1, s.EarlyDuration1 }).ToList(); var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); #region MyRegion var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); #endregion var reasons = repoCat_TAMReasonmiss.FindBy(s => s.IsDelete == null); var shifts = repoShift.FindBy(s => s.IsDelete == null); if (shiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } if (!string.IsNullOrEmpty(codeEmp)) { profiles = profiles.Where(s => s.CodeEmp == codeEmp).ToList(); } var key = LeaveDayStatus.E_APPROVED.ToString(); var repoLeaveDay = new Att_LeavedayRepository(unitOfWork); var leaveDays = repoLeaveDay.FindBy(s => s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd); List<Guid> guids = profiles.Select(s => s.ID).ToList(); workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList(); profiles = profiles.Where(s => guids.Contains(s.ID)).ToList(); List<Att_ReportSummaryLateInOutEntity> lstReportSummaryLateInOutEntity = new List<Att_ReportSummaryLateInOutEntity>(); Att_ReportSummaryLateInOutEntity reportSummaryLateInOutEntityEntity = null; foreach (var profile in profiles) { for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var workDayProfiles = workDays.Where(s => s.ProfileID == profile.ID && s.WorkDate.Date == date).ToList(); var leveDay = leaveDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.DateStart <= date && date <= s.DateEnd); if (workDayProfiles.Count > 0 && leveDay == null) { var workDay = workDayProfiles.FirstOrDefault(s => s.WorkDate.Date == date); if (workDay != null) { reportSummaryLateInOutEntityEntity = new Att_ReportSummaryLateInOutEntity(); Guid? orgId = profile.OrgStructureID; var reason = reasons.FirstOrDefault(s => s.ID == workDay.MissInOutReason); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); reportSummaryLateInOutEntityEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; reportSummaryLateInOutEntityEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; reportSummaryLateInOutEntityEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; reportSummaryLateInOutEntityEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; reportSummaryLateInOutEntityEntity.BranchName = orgBranch != null ? orgOrg.OrgStructureName : string.Empty; reportSummaryLateInOutEntityEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; reportSummaryLateInOutEntityEntity.TeamName = orgTeam != null ? orgOrg.OrgStructureName : string.Empty; reportSummaryLateInOutEntityEntity.SectionName = orgSection != null ? orgOrg.OrgStructureName : string.Empty; #region MyRegion var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); #endregion var shift = shifts.FirstOrDefault(s => s.ID == workDay.ShiftID); reportSummaryLateInOutEntityEntity.ProfileName = profile.ProfileName; reportSummaryLateInOutEntityEntity.CodeEmp = profile.CodeEmp; #region MyRegion reportSummaryLateInOutEntityEntity.PositionCode = positon != null ? positon.Code : string.Empty; reportSummaryLateInOutEntityEntity.JobtitleCode = jobtitle != null ? jobtitle.Code : string.Empty; reportSummaryLateInOutEntityEntity.PositionName = positon != null ? positon.PositionName : string.Empty; reportSummaryLateInOutEntityEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; #endregion reportSummaryLateInOutEntityEntity.DateFrom = dateStart; reportSummaryLateInOutEntityEntity.Date = date; reportSummaryLateInOutEntityEntity.DateTo = dateEnd; reportSummaryLateInOutEntityEntity.DateExport = DateTime.Now; reportSummaryLateInOutEntityEntity.UserExport = userExport; reportSummaryLateInOutEntityEntity.udTAMScanReasonMiss = reason == null ? string.Empty : reason.TAMScanReasonMissName; reportSummaryLateInOutEntityEntity.ShiftName = shift == null ? string.Empty : shift.ShiftName; reportSummaryLateInOutEntityEntity.udInTime = workDay.InTime1 != null ? workDay.InTime1.Value : DateTime.MinValue; reportSummaryLateInOutEntityEntity.udOutTime = workDay.OutTime1 != null ? workDay.OutTime1.Value : DateTime.MinValue; if (workDay.LateEarlyDuration >= 120) { reportSummaryLateInOutEntityEntity.EarlyDurationMore2 = "X"; } else { reportSummaryLateInOutEntityEntity.EarlyDurationLess2 = "X"; } if (workDay.LateDuration1 != null && workDay.LateDuration1 > 0) { reportSummaryLateInOutEntityEntity.LateForWork = "X"; } if (workDay.EarlyDuration1 != null && workDay.EarlyDuration1 > 0) { reportSummaryLateInOutEntityEntity.EarlyGoHome = "X"; } lstReportSummaryLateInOutEntity.Add(reportSummaryLateInOutEntityEntity); } } } #region MyRegion //Guid? orgId = profile.OrgStructureID; //var attendanceProfiles = attendanceTables.Where(s => s.ProfileID == profile.ID).ToList(); //var attendaceProfileIDs = attendanceProfiles.Select(s => s.ID).ToList(); //var attendanceItemProfiles = attendanceItems.Where(s => attendaceProfileIDs.Contains(s.AttendanceTableID)).ToList(); //var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); //var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); //var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); //var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); //var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); //reportSummaryLateInOutEntityEntity.LateMinutes = attendanceItemProfiles.Sum(s => s.LateInMinutes); //reportSummaryLateInOutEntityEntity.EarlyMinutes = attendanceItemProfiles.Sum(s => s.EarlyOutMinutes); //reportSummaryLateInOutEntityEntity.NumTimeLate = attendanceItemProfiles.Count(s => s.LateInMinutes > 0); //reportSummaryLateInOutEntityEntity.NumTimeEarly = attendanceItemProfiles.Count(s => s.EarlyOutMinutes > 0); //lstReportSummaryLateInOutEntity.Add(reportSummaryLateInOutEntityEntity); #endregion } return lstReportSummaryLateInOutEntity; } }
/// <summary> ///[Tam.Le] - Lấy Dữ Liệu BC chi tiết ngày nghỉ ốm /// </summary> /// <returns></returns> public List<Att_ReportDetailLeaveSickEntity> GetReportDetailLeaveSick(DateTime dateStart, DateTime dateEnd, string OrgStructureIDs, List<Hre_ProfileEntity> profiles, List<Guid?> shiftIDs, List<Guid?> leaveDayTypeIDs, Boolean? _isIncludeQuitEmp, string userExport) { using (var context = new VnrHrmDataContext()) { List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); string key = OverTimeStatus.E_APPROVED.ToString(); // var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork); var leavedayTypes = repoCat_LeaveDayType .FindBy(s => s.CodeStatistic != null && s.IsDelete == null).Select(s => new { s.ID, s.CodeStatistic, s.PaidRate }) .Distinct() .ToList(); var ledvedayPaidIds = leavedayTypes .Where(s => s.PaidRate > 0 || s.CodeStatistic == "SU" || s.CodeStatistic == "SD" || s.CodeStatistic == "SC").Select(s => s.ID) .ToList(); var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); // var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var repoAtt_LeaveDay = new CustomBaseRepository<Att_LeaveDay>(unitOfWork); leaveDays = repoAtt_LeaveDay .FindBy(s => s.LeaveHours > 0 && s.IsDelete == null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); #region //if (lstProfileIDs != null && lstProfileIDs.Count > 0) //{ // leaveDays = repoAtt_LeaveDay // .FindBy(s => s.LeaveHours > 0 && s.IsDelete==null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID)) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); //} //else //{ // leaveDays = repoAtt_LeaveDay // .FindBy(s => s.LeaveHours > 0 && s.IsDelete==null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); //} #endregion var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); // var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var repoAtt_Workday = new CustomBaseRepository<Att_Workday>(unitOfWork); var workDays = new List<Att_Workday>().Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); #region //if (lstProfileIDs != null && lstProfileIDs.Count > 0) //{ // workDays = repoAtt_Workday // .FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.InTime1 != null || s.OutTime1 != null) && lstProfileIDs.Contains(s.ProfileID)) // .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); //} //else //{ // workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.InTime1 != null || s.OutTime1 != null)) // .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); //} //var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); //profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID)) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); #endregion if (OrgStructureIDs != null) { profiles = profiles.Where(s => s.OrgStructureID != null).ToList(); } if (leaveDayTypeIDs != null) { leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.LeaveDayTypeID)).ToList(); profileIds = leaveDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (shiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (_isIncludeQuitEmp == false) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); //var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoCat_Shift = new CustomBaseRepository<Cat_Shift>(unitOfWork); var shifts = repoCat_Shift.GetAll().ToList(); List<Att_ReportDetailLeaveSickEntity> lstReportDetailLeaveSickEntity = new List<Att_ReportDetailLeaveSickEntity>(); Att_ReportDetailLeaveSickEntity reportDetailLeaveSickEntity = null; foreach (var profile in profiles) { for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var leavdayProfiles = leaveDays.Where(s => s.DateStart.Date <= date.Date && date.Date <= s.DateEnd.Date && s.ProfileID == profile.ID).ToList(); if (leavdayProfiles.Count > 0) { reportDetailLeaveSickEntity = new Att_ReportDetailLeaveSickEntity(); Guid? orgId = profile.OrgStructureID; var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date); var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); reportDetailLeaveSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; reportDetailLeaveSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; reportDetailLeaveSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; reportDetailLeaveSickEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); reportDetailLeaveSickEntity.ProfileName = profile.ProfileName; reportDetailLeaveSickEntity.CodeEmp = profile.CodeEmp; reportDetailLeaveSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty; reportDetailLeaveSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); var leaveday = leavdayProfiles.FirstOrDefault(s => ledvedayPaidIds.Contains(s.LeaveDayTypeID)); reportDetailLeaveSickEntity.Date = date; reportDetailLeaveSickEntity.DateFrom = dateStart; reportDetailLeaveSickEntity.DateTo = dateEnd; reportDetailLeaveSickEntity.DateExport = DateTime.Now; reportDetailLeaveSickEntity.UserExport = userExport; if (leaveday != null) { //reportDetailLeaveSickEntity.Paid = leaveday.LeaveHours / 8; reportDetailLeaveSickEntity.IsPaid = "X"; } if (workDay != null) { if (workDay.ShiftID != null) reportDetailLeaveSickEntity.ShiftName = shift.ShiftName; if (workDay.InTime1 != null) reportDetailLeaveSickEntity.InTime = workDay.InTime1.Value; if (workDay.OutTime1 != null) reportDetailLeaveSickEntity.OutTime = workDay.OutTime1.Value; } foreach (var leaday in leavedayTypes.Where(s => s.CodeStatistic == "SU" || s.CodeStatistic == "SD" || s.CodeStatistic == "SC")) { var leaday1 = leavdayProfiles.FirstOrDefault(s => s.LeaveDayTypeID == leaday.ID); if (leaday1 != null) { reportDetailLeaveSickEntity.CodeStatistic = (leaday1.LeaveHours.HasValue ? leaday1.LeaveHours.Value : 0) / 8; if (leaday.CodeStatistic == "SU") reportDetailLeaveSickEntity.SU = "X"; else if (leaday.CodeStatistic == "SD") reportDetailLeaveSickEntity.SD = "X"; else if (leaday.CodeStatistic == "SC") reportDetailLeaveSickEntity.SC = "X"; } } lstReportDetailLeaveSickEntity.Add(reportDetailLeaveSickEntity); } } } return lstReportDetailLeaveSickEntity; } }
/// <summary> /// Lay du lieu bao cao tong hop nghi om /// </summary> /// <param name="dateYear"></param> /// <param name="lstProfileIDs"></param> /// <returns></returns> public List<Att_ReportSickLeaveEntity> GetReportSickLeave(DateTime dateYear, List<Guid> lstProfileIDs) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); int year = dateYear.Year; DateTime dateStart = new DateTime(year, 1, 1); DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); string key = OverTimeStatus.E_APPROVED.ToString(); var repoCat_Leaveday = new Cat_LeaveDayTypeRepository(unitOfWork); var leavedayTypes = repoCat_Leaveday.FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC").Select(s => new { s.ID, s.CodeStatistic, s.PaidRate }).Distinct().ToList(); var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList(); var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); if (lstProfileIDs != null && lstProfileIDs.Count > 0) { leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID)) .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); } else { leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); } var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID) && lstProfileIDs.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, }).ToList(); string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString(); string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString(); var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork); var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year) .Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); List<Att_ReportSickLeaveEntity> lstReportSickLeaveEntity = new List<Att_ReportSickLeaveEntity>(); Att_ReportSickLeaveEntity reportSickLeaveEntity = null; foreach (var profile in profiles) { reportSickLeaveEntity = new Att_ReportSickLeaveEntity(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); reportSickLeaveEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; reportSickLeaveEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; reportSickLeaveEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; reportSickLeaveEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); reportSickLeaveEntity.ProfileName = profile.ProfileName; reportSickLeaveEntity.CodeEmp = profile.CodeEmp; reportSickLeaveEntity.PositionName = positon != null ? positon.PositionName : string.Empty; reportSickLeaveEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; reportSickLeaveEntity.DateExport = DateTime.Now; var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE); var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE); reportSickLeaveEntity.TotalP = anual != null ? anual.Month3 : 0; reportSickLeaveEntity.TotalSC = anualSick != null ? anualSick.Month3 : 0; //reportSickLeaveEntity.BugetYearP = anualSick != null ? anualSick.Month3 : 0; //reportSickLeaveEntity.BalanceSC = anualSick != null ? anualSick.Month3 : 0; for (int i = 1; i <= 12; i++) { dateStart = new DateTime(year, i, 1); dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i)); reportSickLeaveEntity.DateFrom = dateStart; reportSickLeaveEntity.DateTo = dateEnd; foreach (var leaday in leavedayTypes) { Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>(); double leaveDayHours = leaveDays .Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID) .Sum(s => s.LeaveHours.Value / 8); } } lstReportSickLeaveEntity.Add(reportSickLeaveEntity); } return lstReportSickLeaveEntity; } }
public List<Att_ProfileEntity> CheckRoster(DateTime? DateStart, DateTime? DateEnd, List<Hre_ProfileEntity> lstProfileIDsModel, bool isNotRoster, bool isDuplicateRoster, bool? isConstantRoster) { // List<Att_ProfileEntity> reportData = new List<Att_ProfileEntity>(); List<Guid> lstProfileIDs = lstProfileIDsModel.Select(s => s.ID).ToList(); string status = string.Empty; List<Att_ProfileEntity> reportData = new List<Att_ProfileEntity>(); var _baseService = new BaseService(); if (DateStart > DateEnd) { return reportData; } using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoLeaveDay = new Att_LeavedayRepository(unitOfWork); List<Att_RosterEntity> listWRt = new List<Att_RosterEntity>(); string E_CANCEL = RosterStatus.E_CANCEL.ToString(); string E_REJECTED = RosterStatus.E_REJECTED.ToString(); var repoRoster = new Att_RosterRepository(unitOfWork); var lstRoster = repoRoster.FindBy(s => s.IsDelete == null && s.DateStart <= DateEnd && s.DateEnd >= DateStart && s.Status != E_CANCEL && s.Status != E_REJECTED).ToList(); //var lstRoster = GetData<Att_RosterEntity>(lstObj, ConstantSql.hrm_att_sp_get_Roster,ref status).Where( // s => s.StatusRoster != E_CANCEL && s.StatusRoster != E_REJECTED // ).ToList(); var profileIds = lstRoster.Where(s => lstProfileIDs.Contains(s.ProfileID)).Select(s => s.ProfileID).Distinct().ToList(); string key = ProfileStatusSyn.E_WAITING.ToString(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = lstProfileIDsModel.Where(s => s.IsDelete == null && s.StatusSyn != key && (s.DateQuit == null || s.DateQuit > DateStart)) .Select(s => new { s.ID, s.DateQuit, s.OrgStructureName, s.E_UNIT, s.E_DIVISION, s.E_DEPARTMENT, s.E_TEAM, s.E_SECTION, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.Email, s.CodeAttendance, s.Gender, s.BusinessPhone, s.DateEndProbation, s.DateHire }).ToList(); if (lstProfileIDs.Count != 0 && lstProfileIDs[0] != Guid.Empty) { profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList(); } var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); string E_CANCEL_LEAVE = LeaveDayStatus.E_CANCEL.ToString(); string E_REJECTED_LEAVE = LeaveDayStatus.E_REJECTED.ToString(); var lstLeaveDay = repoLeaveDay.FindBy(s => s.IsDelete == null && s.DateStart <= DateEnd && s.DateEnd >= DateStart && s.Status != E_CANCEL_LEAVE && s.Status != E_REJECTED_LEAVE).ToList(); //var lstLeaveDay = GetData<Att_LeaveDayEntity>(lstObj1, ConstantSql.hrm_att_sp_get_Leaveday, ref status).Where( // s => s.StatusLeaveDay != E_CANCEL_LEAVE && s.StatusLeaveDay != E_REJECTED_LEAVE).ToList(); //List<Guid> guids = EntityService.GetEntityList<Att_Roster>(false, EntityService.GuidMainContext, Guid.Empty, s => s.DateStart <= DateEnd && s.DateEnd >= DateStart).Select(s => s.ProfileID).Distinct().ToList(); List<Guid> guids = lstRoster.Select(s => s.ProfileID).Distinct().ToList(); // var listProfile = GetProfilesWorking(OrgStructureID, DateStart); if (isNotRoster == true) { //List<Hre_Profile> profiles = listProfile.Where(s => !guids.Contains(s.ID)).ToList(); if (isConstantRoster == true) { foreach (var profile in profiles) { for (DateTime dateCheck = DateStart.Value; dateCheck <= DateEnd.Value; dateCheck = dateCheck.AddDays(1)) { List<Guid> ListProfileRosterIds = lstRoster.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck).Select(m => m.ProfileID).Distinct().ToList(); List<Guid> ListProfileLeaveIds = lstLeaveDay.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck).Select(m => m.ProfileID).Distinct().ToList(); var profileNotIn = profiles.Where(m => !ListProfileRosterIds.Contains(m.ID) && !ListProfileLeaveIds.Contains(m.ID)).ToList(); //reportData.AddRange(profileNotIn.Translate<Att_ProfileEntity>()); //foreach (var profileNotRoster in profileNotIn) //{ if (profileNotIn != null && profileNotIn.Count > 0) { Att_ProfileEntity profileResult = new Att_ProfileEntity(); //Guid? orgId = profile.OrgStructureID; //var orgModel = orgs.Where(s => s.ID == orgId).FirstOrDefault(); //var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); //var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); //var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); //var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); profileResult.E_UNIT = profile.E_UNIT; profileResult.E_DIVISION = profile.E_DIVISION; profileResult.E_DEPARTMENT = profile.E_DEPARTMENT; profileResult.E_TEAM = profile.E_TEAM; profileResult.E_SECTION = profile.E_SECTION; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); profileResult.ID = profile.ID; profileResult.CodeEmp = profile.CodeEmp; profileResult.ProfileName = profile.ProfileName; profileResult.CodeAttendance = profile.CodeAttendance; profileResult.DateHire = profile.DateHire; profileResult.PositionName = positon != null ? positon.PositionName : string.Empty; profileResult.JobTitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; profileResult.Email = profile.Email; profileResult.DateEndProbation = profile.DateEndProbation; profileResult.DateQuit = profile.DateQuit; if (profile.Gender == "E_FEMALE") profileResult.Gender = "Nam"; if (profile.Gender == "E_MALE") profileResult.Gender = "Nữ"; reportData.Add(profileResult); } //} //profileResult.AddRange(profileNotIn); } } reportData = reportData.Distinct().ToList(); return reportData; } else { var rs = profiles.Where(s => !guids.Contains(s.ID)).ToList().Translate<Att_ProfileEntity>(); foreach (var profile in rs) { Att_ProfileEntity profileResult = new Att_ProfileEntity(); //Guid? orgId = profile.OrgStructureID; //var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); //var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); //var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); //var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); profileResult.E_UNIT = profile.E_UNIT; profileResult.E_DIVISION = profile.E_DIVISION; profileResult.E_DEPARTMENT = profile.E_DEPARTMENT; profileResult.E_TEAM = profile.E_TEAM; profileResult.E_SECTION = profile.E_SECTION; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); profileResult.ID = profile.ID; profileResult.CodeEmp = profile.CodeEmp; profileResult.ProfileName = profile.ProfileName; profileResult.CodeAttendance = profile.CodeAttendance; profileResult.DateHire = profile.DateHire; profileResult.PositionName = positon != null ? positon.PositionName : string.Empty; profileResult.JobTitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; profileResult.Email = profile.Email; profileResult.DateEndProbation = profile.DateEndProbation; profileResult.DateQuit = profile.DateQuit; if (profile.Gender == "E_FEMALE") profileResult.Gender = "Nam"; if (profile.Gender == "E_MALE") profileResult.Gender = "Nữ"; reportData.Add(profileResult); } return reportData; } reportData = profiles.Distinct().ToList().Translate<Att_ProfileEntity>(); } // danh sach trung ca // else if (isDuplicateRoster == true) // { // var rosters = GetData<Att_RosterEntity>(lstObj, ConstantSql.hrm_att_sp_get_Roster, ref status).ToList(); // foreach (var pf in listProfile) // { // // lay ca cua nhan vien // var listWRosterPr = rosters.Where(rt => rt.ProfileID == pf.ID).ToList(); // if (listWRosterPr.Count > 0) // for (DateTime dx = DateStart.Value.Date; dx <= DateEnd; dx = dx.AddDays(1)) // { // var listWRoster = listWRosterPr.Where(s => s.ProfileID == pf.ID // && s.DateStart <= dx // && dx <= s.DateEnd // && s.Type == RosterType.E_DEFAULT.ToString() // && s.Status != RosterStatus.E_REJECTED.ToString() // && s.Status != RosterStatus.E_CANCEL.ToString() // && s.IsDelete == null).ToList(); // if (listWRoster.Count >= 2) // listWRt.AddRange(listWRoster); // } // } // var result = listWRt.OrderBy(s => s.CodeEmp).ToList(); // // return result; // } //} return reportData; } }
/// <summary> /// [Hieu.Van] 01/06/2014 /// Lấy ds tất cả báo cáo Sai ca /// </summary> /// <returns></returns> public List<Att_ReportWrongShiftEntity> GetReportWrongShift(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> lstProfileAll, Guid[] ShiftIDs, bool isIncludeQuitEmp, string userExport) { using (var context = new VnrHrmDataContext()) { List<Guid> lstProfileIDs = lstProfileAll.Select(s => s.ID).ToList(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); #region MyRegion //var workDays = repoAtt_Workday.FindBy(s => s.ShiftID != null && lstProfileIDs.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null) // .Select(s => new { s.ProfileID, s.WorkDate, s.ShiftID, s.InTime1, s.OutTime1, s.ShiftActual, s.ShiftApprove, s.Status }).ToList(); #endregion var workDays = repoAtt_Workday.FindBy(s => s.ShiftID != null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null) .Select(s => new { s.ProfileID, s.WorkDate, s.ShiftID, s.InTime1, s.OutTime1, s.ShiftActual, s.ShiftApprove, s.Status }).ToList(); //var profileIds = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).Select(s => s.ProfileID).Distinct().ToList(); var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList(); var profiles = lstProfileAll.Where(s => profileIds.Contains(s.ID)) .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); #region // var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); //var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); //var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); #endregion var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList(); if (ShiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } #region //if (!string.IsNullOrEmpty(CodeEmp)) //{ // lstProfileIDs = repoHre_Profile.FindBy(s => s.CodeEmp == CodeEmp).Select(p => p.ID).ToList(); //} #endregion List<Guid> guids = profiles.Select(s => s.ID).ToList(); // workDays = workDays.Where(s => s.ProfileID != null && guids.Contains(s.ProfileID)).ToList(); workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList(); profiles = profiles.Where(s => guids.Contains(s.ID)).ToList(); List<Att_ReportWrongShiftEntity> lstReportWrongShiftEntity = new List<Att_ReportWrongShiftEntity>(); Att_ReportWrongShiftEntity reportWrongShiftEntity = null; #region lay danh sach phong ban theo profiles //// var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); //var lstDepartments = new List<Cat_OrgStructure>(); //// var orgTeam = new Cat_OrgStructure(); //// var orgSection = new Cat_OrgStructure(); //var lstDivisions = new List<Cat_OrgStructure>(); //var orgIds = new List<Guid>(); //if (profiles.Any()) //{ // orgIds = profiles.Select(p => p.OrgStructureID ?? Guid.Empty).ToList(); //} //foreach (var orgId in orgIds) //{ // var department = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); // var division = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); // if (department != null) // { // lstDepartments.Add(department); // } // if (division != null) // { // lstDivisions.Add(division); // } //} #endregion var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); foreach (var profile in profiles) { for (DateTime date = dateStart.Date; date <= dateEnd.Date; date = date.AddDays(1)) { var workDay = workDays.FirstOrDefault(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID); if (workDay != null && workDay.ShiftActual != workDay.ShiftID) { reportWrongShiftEntity = new Att_ReportWrongShiftEntity(); //DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var orGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes); var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); reportWrongShiftEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; reportWrongShiftEntity.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; reportWrongShiftEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; reportWrongShiftEntity.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty; reportWrongShiftEntity.GroupCode = orGroup != null ? orGroup.Code : string.Empty; reportWrongShiftEntity.GroupName = orGroup != null ? orGroup.OrgStructureName : string.Empty; reportWrongShiftEntity.Division = orgDivision != null ? orgDivision.Code : string.Empty; reportWrongShiftEntity.DivisionName = orgDivision != null ? orgDivision.OrgStructureName : string.Empty; reportWrongShiftEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; reportWrongShiftEntity.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); var shiftActual = shifts.FirstOrDefault(s => s.ID == workDay.ShiftActual); var shiftApprove = shifts.FirstOrDefault(s => s.ID == workDay.ShiftApprove); reportWrongShiftEntity.ProfileName = profile.ProfileName; reportWrongShiftEntity.CodeEmp = profile.CodeEmp; reportWrongShiftEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; reportWrongShiftEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; reportWrongShiftEntity.PositionName = positon != null ? positon.PositionName : string.Empty; reportWrongShiftEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; reportWrongShiftEntity.OrgCode = org != null ? org.Code : string.Empty; reportWrongShiftEntity.Date = date; reportWrongShiftEntity.DateFrom = dateStart; reportWrongShiftEntity.DateTo = dateEnd; reportWrongShiftEntity.DateExport = DateTime.Now; reportWrongShiftEntity.UserExport = userExport; //reportWrongShiftEntity.UserExport = Session[SessionObjects.UserLogin]; reportWrongShiftEntity.ShiftName = shift != null ? shift.ShiftName : string.Empty; reportWrongShiftEntity.ScheduleShift = shift != null ? shift.ShiftName : string.Empty; reportWrongShiftEntity.ActualShift = shiftActual != null ? shiftActual.ShiftName : string.Empty; reportWrongShiftEntity.ApprovedShift = shiftApprove != null ? shiftApprove.ShiftName : string.Empty; reportWrongShiftEntity.Status = workDay.Status; reportWrongShiftEntity.TimeIn = workDay.InTime1 != null ? workDay.InTime1 : null; ; reportWrongShiftEntity.TimeOut = workDay.OutTime1 != null ? workDay.OutTime1 : null; lstReportWrongShiftEntity.Add(reportWrongShiftEntity); } } } #region Code Cũ //foreach (var profile in profiles) //{ // for (DateTime date = dateStart.Date; date <= dateEnd.Date; date = date.AddDays(1)) // { // var workDay = workDays.FirstOrDefault(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID); // if (workDay != null && workDay.ShiftActual != workDay.ShiftID) // { // reportWrongShiftEntity = new Att_ReportWrongShiftEntity(); // Guid? orgId = profile.OrgStructureID; // #region // // var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date); // // var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); // // var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); // #endregion // var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); // var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); // var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); // #region // var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); // var orGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes); // var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); // reportWrongShiftEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; // reportWrongShiftEntity.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; // reportWrongShiftEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; // reportWrongShiftEntity.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty; // reportWrongShiftEntity.GroupCode = orGroup != null ? orGroup.Code : string.Empty; // reportWrongShiftEntity.GroupName = orGroup != null ? orGroup.OrgStructureName : string.Empty; // reportWrongShiftEntity.Division = orgDivision != null ? orgDivision.Code : string.Empty; // reportWrongShiftEntity.DivisionName = orgDivision != null ? orgDivision.OrgStructureName : string.Empty; // reportWrongShiftEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; // reportWrongShiftEntity.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; // #endregion // reportWrongShiftEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; // reportWrongShiftEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; // var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); // var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); // reportWrongShiftEntity.PositionName = positon != null ? positon.PositionName : string.Empty; // reportWrongShiftEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; // var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); // var shiftActual = shifts.FirstOrDefault(s => s.ID == workDay.ShiftActual); // var shiftApprove = shifts.FirstOrDefault(s => s.ID == workDay.ShiftApprove); // reportWrongShiftEntity.ProfileName = profile.ProfileName; // reportWrongShiftEntity.CodeEmp = profile.CodeEmp; // reportWrongShiftEntity.Date = date; // reportWrongShiftEntity.DateFrom = dateStart; // reportWrongShiftEntity.DateTo = dateEnd; // reportWrongShiftEntity.DateExport = DateTime.Now; // reportWrongShiftEntity.ShiftName = shift != null ? shift.ShiftName : string.Empty; // reportWrongShiftEntity.ScheduleShift = shift != null ? shift.ShiftName : string.Empty; // reportWrongShiftEntity.ActualShift = shiftActual != null ? shiftActual.ShiftName : string.Empty; // reportWrongShiftEntity.ApprovedShift = shiftApprove != null ? shiftApprove.ShiftName : string.Empty; // reportWrongShiftEntity.Status = workDay.Status; // reportWrongShiftEntity.TimeIn = workDay.InTime1 != null ? workDay.InTime1 : null; ; // reportWrongShiftEntity.TimeOut = workDay.OutTime1 != null ? workDay.OutTime1 : null; // lstReportWrongShiftEntity.Add(reportWrongShiftEntity); // } // } //} #endregion return lstReportWrongShiftEntity; } }
public DataTable GetReportDailyAttendance(DateTime dateStart, DateTime dateEnd, string userExport, List<Hre_ProfileEntity> lstProfileAll, Guid[] payrollIDs, bool excludeNotInOut, bool isCreateTemplate, string UserLogin) { using (var context = new VnrHrmDataContext()) { DataTable table = CreateReportDailyAttendanceSchema(); if (isCreateTemplate) { return table.ConfigDatatable(); } List<Guid> lstProfileIDs = lstProfileAll.Select(s => s.ID).ToList(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); // var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoWorkDay = new Att_WorkDayServices(); var repoCat_Shift = new Cat_ShiftServices(); var profiles = lstProfileAll.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.PayrollGroupID }).ToList(); if (payrollIDs != null) { profiles = profiles.Where(m => m.PayrollGroupID != null && payrollIDs.Contains(m.PayrollGroupID.Value)).ToList(); } var profileIds = profiles.Select(s => s.ID).Distinct().ToList(); string status = string.Empty; string E_APPROVED = WorkdayStatus.E_APPROVED.ToString(); List<object> para_reportWorDay = new List<object>(); para_reportWorDay.AddRange(new object[3]); para_reportWorDay[1] = dateStart; para_reportWorDay[2] = dateEnd; List<Att_WorkdayEntity> workDays = repoWorkDay.GetData<Att_WorkdayEntity>(para_reportWorDay, ConstantSql.hrm_att_sp_getdata_reportWorDay, UserLogin, ref status).ToList(); workDays = workDays.Where(m => profileIds.Contains(m.ProfileID) && m.Status == E_APPROVED).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); #region var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); #endregion if (workDays == null || workDays.Count == 0) { return table; } profileIds = workDays.Select(m => m.ProfileID).Distinct().ToList(); profiles = profiles.Where(m => profileIds.Contains(m.ID)).ToList(); List<object> para_repoCatShift = new List<object>(); //para_repoCatShift.AddRange(new object[4]); //para_repoCatShift[0] = null; //para_repoCatShift[1] = null; //para_repoCatShift[2] = null; //para_repoCatShift[3] = null; List<Cat_ShiftEntity> lstShift = repoCat_Shift.GetData<Cat_ShiftEntity>(para_repoCatShift, ConstantSql.hrm_cat_sp_get_Shift, UserLogin, ref status).ToList(); foreach (var item in workDays) { DataRow row = table.NewRow(); row[Att_ReportDailyAttendanceEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportDailyAttendanceEntity.FieldNames.DateTo] = dateEnd; var Profile = profiles.Where(m => m.ID == item.ProfileID).FirstOrDefault(); if (Profile != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.CodeEmp] = Profile.CodeEmp != null ? Profile.CodeEmp : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.ProfileName] = Profile.ProfileName != null ? Profile.ProfileName : string.Empty; Guid? orgId = Profile.OrgStructureID; if (orgId != null) { var org = orgs.FirstOrDefault(s => s.ID == Profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportDailyAttendanceEntity.FieldNames.ProfileOrgCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; } var positon = positions.FirstOrDefault(s => s.ID == Profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == Profile.JobTitleID); row[Att_ReportDailyAttendanceEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportDailyAttendanceEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; } var shift = lstShift.Where(m => m.ID == item.ShiftID).FirstOrDefault(); if (shift != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.ShiftName] = shift.ShiftName; } var shiftActual = lstShift.Where(m => m.ID == item.ShiftActual).FirstOrDefault(); if (shiftActual != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.ActualShift] = shiftActual.ShiftName; } if (item.WorkDate != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.WorkDate] = item.WorkDate; } var shiftApprove = lstShift.Where(m => m.ID == item.ShiftApprove).FirstOrDefault(); if (shiftApprove != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.ApprovedShift] = shiftApprove.ShiftName; } if (item.InTime1 != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.TimeIn] = item.InTime1; } if (item.OutTime1 != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.TimeOut] = item.OutTime1; } row[Att_ReportDailyAttendanceEntity.FieldNames.Type] = item.Type; row[Att_ReportDailyAttendanceEntity.FieldNames.SrcType] = item.SrcType; row[Att_ReportDailyAttendanceEntity.FieldNames.Status] = item.Status; row[Att_ReportDailyAttendanceEntity.FieldNames.Note] = item.Note; if (item.LateDuration1 != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.LateDuration1] = item.LateDuration1; } if (item.EarlyDuration1 != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyDuration1] = item.EarlyDuration1; } if (item.LateEarlyDuration != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyDuration] = item.LateEarlyDuration; } if (item.LateEarlyRoot != null) { row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyRoot] = item.LateEarlyRoot; } row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyReason] = item.LateEarlyReason; row[Att_ReportDailyAttendanceEntity.FieldNames.UserExport] = userExport; row[Att_ReportDailyAttendanceEntity.FieldNames.DateExport] = DateTime.Today; if (item.InTimeRoot != null) row[Att_ReportDailyAttendanceEntity.FieldNames.InTimeRoot] = item.InTimeRoot.Value; if (item.OutTimeRoot != null) row[Att_ReportDailyAttendanceEntity.FieldNames.OutTimeRoot] = item.OutTimeRoot.Value; if (item.LateEarlyDuration != null) { if (item.LateEarlyDuration.Value < 120) { row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateLess2h] = "X"; } else { row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateOver2h] = "X"; } } else { row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateLess2h] = "X"; } table.Rows.Add(row); } return table; } }
/// <summary> /// Lấy Dữ Liệu BC Thống Kê Ngày Nghỉ Ốm Năm /// </summary> /// <returns></returns> //public List<Att_ReportSummaryLeaveYearSickEntity> GetReportSummaryLeaveYearSick(DateTime dateYear, List<Guid> lstProfileIDs) public DataTable GetReportSummaryLeaveYearSick(int year, List<Hre_ProfileEntity> profiles, Guid[] leavedayTypeIds, bool isNotAllowZero, string userExport) { using (var context = new VnrHrmDataContext()) { DataTable table = CreateReportSummaryLeaveYearSick(); bool isIncludeQuitEmp = false; // leavedayTypeIds = leavedayTypeIds.Where(p => p != null).ToArray(); List<Att_ReportSummaryLeaveYearSickEntity> listReportSummaryLeaveYearSick = new List<Att_ReportSummaryLeaveYearSickEntity>(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DateTime dateStart = new DateTime(year, 1, 1); DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); string key = OverTimeStatus.E_APPROVED.ToString(); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var leavedayTypes = repoCat_LeaveDayType .FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC" && s.IsDelete == null) .Select(s => new { s.ID, s.CodeStatistic, s.PaidRate }) .Distinct() .ToList(); var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList(); var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); leaveDays = repoAtt_LeaveDay.FindBy(s => s.IsDelete == null && leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && dateStart <= s.DateEnd && s.DateStart <= dateEnd) .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); //if (lstProfileIDs != null && lstProfileIDs.Count > 0) //{ // leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID)) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); //} //else //{ // leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); //} //var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); // profiles = repoHre_Profile.FindBy(s => s.IsDelete == null) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString(); string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString(); var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork); var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year) .Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); List<Att_ReportSummaryLeaveYearSickEntity> lstReportSummaryLeaveYearSickEntity = new List<Att_ReportSummaryLeaveYearSickEntity>(); Att_ReportSummaryLeaveYearSickEntity reportSummaryLeaveYearSickEntity = null; if (leavedayTypeIds != null) { leaveDays = leaveDays.Where(s => leavedayTypeIds.Contains(s.LeaveDayTypeID)).ToList(); profileIds = leaveDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } if (isNotAllowZero) { var profileIDs = leaveDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList(); } foreach (var profile in profiles) { DataRow row = table.NewRow(); reportSummaryLeaveYearSickEntity = new Att_ReportSummaryLeaveYearSickEntity(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; //reportSummaryLeaveYearSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; //reportSummaryLeaveYearSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; //reportSummaryLeaveYearSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; //reportSummaryLeaveYearSickEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; //reportSummaryLeaveYearSickEntity.ProfileName = profile.ProfileName; //reportSummaryLeaveYearSickEntity.CodeEmp = profile.CodeEmp; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.UserExport] = userExport; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.DateExport] = DateTime.Today; //reportSummaryLeaveYearSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty; //reportSummaryLeaveYearSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; //reportSummaryLeaveYearSickEntity.DateExport = DateTime.Now; var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE); var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE); row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TotalAllowYear] = anual != null ? anual.Month3 : 0; row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TotalAllowSick] = anualSick != null ? anualSick.Month3 : 0; //reportSummaryLeaveYearSickEntity.TotalAllowYear = anual != null ? anual.Month3 : 0; //reportSummaryLeaveYearSickEntity.TotalAllowSick = anualSick != null ? anualSick.Month3 : 0; for (int i = 1; i <= 12; i++) { dateStart = new DateTime(year, i, 1); dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i)); reportSummaryLeaveYearSickEntity.DateFrom = dateStart; reportSummaryLeaveYearSickEntity.DateTo = dateEnd; foreach (var leaday in leavedayTypes) { // Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>(); // var strCodeStatistic = leaday.CodeStatistic + i; // double leaveDayHours = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveHours / 8); var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID && s.LeaveHours.HasValue).Sum(s => s.LeaveHours.Value / 8); //if (leaday != null && !string.IsNullOrEmpty(leaday.CodeStatistic) && table.Columns.Contains(strCodeStatistic)) //{ //row[leaday.CodeStatistic + i] = leaveDayHours > 0 ? leaveDayHours : (double?)null; row[leaday.CodeStatistic + i] = sum > 0 ? (object)sum : (double?)null; //} // reportSummaryLeaveYearSickEntity.P = leaveDayHours > 0 ? leaveDayHours : 0; } } table.Rows.Add(row); //listReportSummaryLeaveYearSick.Add(reportSummaryLeaveYearSickEntity); } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); var confighidden = new Dictionary<string, object>(); var config90 = new Dictionary<string, object>(); config90.Add("width", 90); var config110 = new Dictionary<string, object>(); config110.Add("width", 110); configs.Add("CodeEmp", config90); configs.Add("BranchCode", config90); configs.Add("DepartmentCode", config90); configs.Add("TeamCode", config90); configs.Add("SectionCode", config90); configs.Add("BranchName", config110); configs.Add("OrgName", config110); configs.Add("TeamName", config110); configs.Add("SectionName", config110); configs.Add("PositionName", config110); configs.Add("JobTitleName", config110); confighidden.Add("hidden", true); configs.Add("UserExport", confighidden); configs.Add("DateExport", confighidden); config.Add("Width", 200); //for (int i = 1; i <= 12; i++) //{ // config = new Dictionary<string, object>(); // configs.Add("P" + i, confighidden); // configs.Add("SC" + i, confighidden); // config.Add("Width", 30); //} config.Add("locked", true); configs.Add("WorkDay", config); return table.ConfigTable(configs); } #region code cũ //using (var context = new VnrHrmDataContext()) //{ // List<Att_ReportSummaryLeaveYearSickEntity> listReportSummaryLeaveYearSick = new List<Att_ReportSummaryLeaveYearSickEntity>(); // var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); // //int year = dateYear.Year; // DateTime dateStart = new DateTime(year, 1, 1); // DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); // string key = OverTimeStatus.E_APPROVED.ToString(); // var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); // var leavedayTypes = repoCat_LeaveDayType // .FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC") // .Select(s => new { s.ID, s.CodeStatistic, s.PaidRate }) // .Distinct() // .ToList(); // var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList(); // var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); // var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); // if (lstProfileIDs != null && lstProfileIDs.Count > 0) // { // leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID)) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); // } // else // { // leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd) // .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList(); // } // var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); // var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); // var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID)) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); // string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString(); // string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString(); // var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork); // var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year) // .Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList(); // var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); // var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); // var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); // var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); // var repoCat_Position = new Cat_PositionRepository(unitOfWork); // var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); // var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); // var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); // List<Att_ReportSummaryLeaveYearSickEntity> lstReportSummaryLeaveYearSickEntity = new List<Att_ReportSummaryLeaveYearSickEntity>(); // Att_ReportSummaryLeaveYearSickEntity reportSummaryLeaveYearSickEntity = null; // foreach (var profile in profiles) // { // reportSummaryLeaveYearSickEntity = new Att_ReportSummaryLeaveYearSickEntity(); // Guid? orgId = profile.OrgStructureID; // var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); // var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); // var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); // var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); // var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); // reportSummaryLeaveYearSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty; // reportSummaryLeaveYearSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; // reportSummaryLeaveYearSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty; // reportSummaryLeaveYearSickEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty; // var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); // var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); // reportSummaryLeaveYearSickEntity.ProfileName = profile.ProfileName; // reportSummaryLeaveYearSickEntity.CodeEmp = profile.CodeEmp; // reportSummaryLeaveYearSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty; // reportSummaryLeaveYearSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty; // reportSummaryLeaveYearSickEntity.DateExport = DateTime.Now; // var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE); // var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE); // reportSummaryLeaveYearSickEntity.TotalAllowYear = anual != null ? anual.Month3 : 0; // reportSummaryLeaveYearSickEntity.TotalAllowSick = anualSick != null ? anualSick.Month3 : 0; // for (int i = 1; i <= 12; i++) // { // dateStart = new DateTime(year, i, 1); // dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i)); // reportSummaryLeaveYearSickEntity.DateFrom = dateStart; // reportSummaryLeaveYearSickEntity.DateTo = dateEnd; // foreach (var leaday in leavedayTypes) // { // Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>(); // double leaveDayHours = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveHours / 8); // } // } // listReportSummaryLeaveYearSick.Add(reportSummaryLeaveYearSickEntity); // } // return listReportSummaryLeaveYearSick; //} #endregion }
public DataTable GetReportMonthlyTimeSheetV2(string WorkHourType, Guid? GradeAttendanceID, Guid CutOffDurationID, string strProfile, string strOrgStructure, string codeEmp, bool isIncludeQuitEmp, bool isNotAllowZero, bool isCreateTemplate, string userExport, string UserLogin, Guid? jobtitleID, Guid? positionID) { bool IsPaidHour = WorkHourType == EnumDropDown.WorkHourType.E_PAIDHOUR.ToString() ? true : false; using (var context = new VnrHrmDataContext()) { DataTable table = CreateReportMonthlyTimeSheetV2Schema(UserLogin); if (isCreateTemplate) { return table.ConfigDatatable(); } string status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var repoWorkDay = new Att_WorkDayRepository(unitOfWork); var repoTimeSheet = new Att_TimeSheetRepository(unitOfWork); var repoJobtype = new Cat_JobTypeRepository(unitOfWork); var repoAtt_AttendencaTable = new CustomBaseRepository<Att_AttendanceTable>(unitOfWork); var repoAtt_AttendanceTableItem = new CustomBaseRepository<Att_AttendanceTableItem>(unitOfWork); var repoAtt_CutOffDuration = new CustomBaseRepository<Att_CutOffDuration>(unitOfWork); var orgsService = new Cat_OrgStructureServices(); List<object> objorgs = new List<object>(); var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList(); var orgTypeService = new Cat_OrgStructureTypeServices(); var lstObjOrgType = new List<object>(); lstObjOrgType.Add(null); lstObjOrgType.Add(null); lstObjOrgType.Add(1); lstObjOrgType.Add(int.MaxValue - 1); var lstOrgType = orgTypeService.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status); var jobTypeServices = new Cat_JobTypeServices(); var lstjobType = new List<object>(); lstjobType.Add(null); lstjobType.Add(null); lstjobType.Add(1); lstjobType.Add(Int32.MaxValue - 1); var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.ID, m.Code }).ToList(); var lstObjOrgByOrderNumberCount = new List<object>(); lstObjOrgByOrderNumberCount.Add(strOrgStructure); var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).ToList(); List<Hre_ProfileEntity> profiles = new List<Hre_ProfileEntity>(); List<object> lstParamHR = new List<object>(); lstParamHR.Add(strOrgStructure); lstParamHR.Add(null); lstParamHR.Add(null); if (strProfile != null) { List<Hre_ProfileEntity> _profileIDs = new List<Hre_ProfileEntity>(); var lst = strProfile.Split(',').Select(s => Guid.Parse(s)).ToList(); if (strOrgStructure != null) { profiles = GetData<Hre_ProfileEntity>(strOrgStructure, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList(); _profileIDs = profiles.Where(m => !lst.Contains(m.ID)).ToList(); profiles.AddRange(_profileIDs); } else { string selectedIds = Common.DotNetToOracle(strProfile); profiles = GetData<Hre_ProfileEntity>(selectedIds, ConstantSql.hrm_hr_sp_get_ProfileByIds, UserLogin, ref status).ToList(); } } else { profiles = GetData<Hre_ProfileEntity>(lstParamHR, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList(); } if (GradeAttendanceID != null && GradeAttendanceID != Guid.Empty) { var lstObjAttGrade = new List<object>(); lstObjAttGrade.AddRange(new object[6]); lstObjAttGrade[1] = Common.DotNetToOracle(GradeAttendanceID.Value.ToString()); lstObjAttGrade[4] = 1; lstObjAttGrade[5] = int.MaxValue - 1; var lstAttGrade = GetData<Att_GradeEntity>(lstObjAttGrade, ConstantSql.hrm_att_sp_get_Att_Grade, UserLogin, ref status).ToList(); List<Guid> lstProOfGrade = lstAttGrade.Select(s => s.ProfileID.Value).Distinct().ToList(); profiles = profiles.Where(s => lstProOfGrade.Contains(s.ID)).ToList(); } var cutoffInfo = repoAtt_CutOffDuration.FindBy(s => s.ID == CutOffDurationID).FirstOrDefault(); var leavedayTypes = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, s => s.CodeStatistic != null).Select(s => new { s.Code, s.CodeStatistic, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList(); var overtimeTypes = unitOfWork.CreateQueryable<Cat_OvertimeType>(Guid.Empty, s => s.CodeStatistic != null).ToList(); DateTime dateStart = cutoffInfo.DateStart; DateTime dateEnd = cutoffInfo.DateEnd; if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } if(jobtitleID != null) { profiles = profiles.Where(s => s.JobTitleID == jobtitleID).ToList(); } if (positionID != null) { profiles = profiles.Where(s => s.PositionID == positionID).ToList(); } List<Guid> profileIds = profiles.Select(s => s.ID).ToList(); var timesheets = new List<Att_TimeSheet>().Select(s => new { s.ProfileID, s.JobTypeID, s.RoleID, s.Date, s.NoHour, s.ID, s.Note, s.Sector }).ToList(); var workDays = new List<Att_Workday>().Select(s => new { s.ProfileID, s.ShiftID, s.Status }).ToList(); var attendanceTables = new List<Att_AttendanceTable>(); var attendanceTableItems = new List<Att_AttendanceTableItem>(); foreach (var item in profileIds.Chunk(1000)) { timesheets.AddRange(repoTimeSheet.FindBy(s => s.ProfileID != null && item.Contains(s.ProfileID.Value) && s.Date != null && dateStart <= s.Date && s.Date <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.JobTypeID, s.RoleID, s.Date, s.NoHour, s.ID, s.Note, s.Sector }).ToList()); workDays.AddRange(repoWorkDay.FindBy(s => item.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.ShiftID, s.Status }).ToList()); attendanceTables.AddRange(repoAtt_AttendencaTable.FindBy(s => s.IsDelete == null && s.CutOffDurationID == CutOffDurationID && item.Contains(s.ProfileID)).ToList()); } var jobtypes = repoJobtype.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.Code }).ToList(); List<Guid> attendanceTablesID = attendanceTables.Select(s => s.ID).ToList(); foreach (var item in attendanceTablesID.Chunk(1000)) { attendanceTableItems.AddRange(unitOfWork.CreateQueryable<Att_AttendanceTableItem>(s => s.IsDelete == null && item.Contains(s.AttendanceTableID)).ToList()); } if (isNotAllowZero) { profileIds = attendanceTables.Where(x => x.StdWorkDayCount > 0).Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } profileIds = attendanceTables.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); int p = 0; foreach (var profile in profiles) { p += 1; var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID); var timeSheetByProfile = timesheets.Where(s => s.ProfileID == profile.ID).ToList(); //if (timeSheetByProfile == null) //{ // continue; //} var lstjobtypebytimesheetpro = timeSheetByProfile.Select(s => s.JobTypeID).ToList(); var lstJobTypeCodeByTimeSheet = jobtypes.Where(s => lstjobtypebytimesheetpro.Contains(s.ID)).Select(s => new { s.ID, s.Code }).ToList(); if (attendanceTableProfile == null) { continue; } var attendanceTableItemProfile = attendanceTableItems.Where(s => s.AttendanceTableID == attendanceTableProfile.ID).ToList(); DataRow row = table.NewRow(); //Guid? orgId = profile.OrgStructureID; //var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); //var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); //var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); //var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); //var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); //var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); //var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentName] = profile.OrgStructureName; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = profile.OrgStructureCode; if (profile.DateHire.HasValue) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateHire] = profile.DateHire.Value; if (profile.DateEndProbation.HasValue) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value; if (profile.E_COMPANY != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_COMPANY] = profile.E_COMPANY; if (profile.E_BRANCH != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_BRANCH] = profile.E_BRANCH; if (profile.E_UNIT != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_UNIT] = profile.E_UNIT; if (profile.E_DIVISION != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DIVISION] = profile.E_DIVISION; if (profile.E_DEPARTMENT != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT; if (profile.E_TEAM != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_TEAM] = profile.E_TEAM; if (profile.E_SECTION != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_SECTION] = profile.E_SECTION; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; #region Lấy Tên Phòng ban cho BDF var orgName = GetParentOrg(lstallorgs, lstOrgType, profile.OrgStructureID); if (orgName.Count != 0) { if (orgName.Count < 3) { orgName.Insert(0, string.Empty); if (orgName.Count < 3) { orgName.Insert(0, string.Empty); } } row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Channel] = orgName[2]; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Region] = orgName[1]; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Area] = orgName[0]; } #endregion #region Lấy phòng ban cho VietNam EASport row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_COMPANY] = profile.E_COMPANY; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_BRANCH] = profile.E_BRANCH; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_UNIT] = profile.E_UNIT; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DIVISION] = profile.E_DIVISION; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_TEAM] = profile.E_TEAM; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_SECTION] = profile.E_SECTION; #endregion //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; //row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; if (profile.DateHire != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StartingDate] = profile.DateHire.Value; if (profile.DateQuit != null) row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ResignedDate] = profile.DateQuit.Value; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateFrom] = dateStart; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateTo] = dateEnd; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateExport] = DateTime.Today; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport] = userExport; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? attendanceTableProfile.StdWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? attendanceTableProfile.RealWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? attendanceTableProfile.PaidWorkDayCount : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.HourPerDay] = attendanceTableProfile.HourPerDay > 0 ? attendanceTableProfile.HourPerDay : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? attendanceTableProfile.AnlDayAvailable : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? attendanceTableProfile.NightShiftHours : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? attendanceTableProfile.LateEarlyDeductionHours : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UnPaidLeave] = attendanceTableProfile.UnPaidLeave > 0 ? attendanceTableProfile.UnPaidLeave : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? attendanceTableProfile.AnlDayTaken : 0.0; row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SickDayTaken] = attendanceTableProfile.SickDayTaken > 0 ? attendanceTableProfile.SickDayTaken : 0.0; double preg = 0.0; #region code cũ xử lý đổ (Data + Day) //foreach (var item in attendanceTableItemProfile) //{ // if (item.IsHavingPregTreatment) // { // preg += 1; // } // if (item.LeaveTypeID != null) // { // row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = leavedayTypes.Where(s => s.ID == item.LeaveTypeID).FirstOrDefault().Code; // } // else // { // if (IsPaidHour) // { // row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = item.WorkPaidHours; // } // else // { // row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = item.WorkHours; // } // } //} #endregion attendanceTableItemProfile = attendanceTableItemProfile.OrderBy(s => s.WorkDate).ToList(); Dictionary<string, double> leave_Hour = new Dictionary<string, double>(); for (int i = 0; i < attendanceTableItemProfile.Count; i++) { int stt = i + 1; if (attendanceTableItemProfile[i].IsHavingPregTreatment) { preg += 1; } if (attendanceTableItemProfile[i].LeaveTypeID != null) { var leave = leavedayTypes.Where(s => s.ID == attendanceTableItemProfile[i].LeaveTypeID).FirstOrDefault(); if (attendanceTableItemProfile[i].LeaveDays == 0.5) { row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = leave.CodeStatistic + "/2"; } else { row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = leave.CodeStatistic; } if (leave.CodeStatistic == null) continue; if (leave_Hour.ContainsKey(leave.CodeStatistic)) leave_Hour[leave.CodeStatistic] += attendanceTableItemProfile[i].LeaveHours; else leave_Hour[leave.CodeStatistic] = attendanceTableItemProfile[i].LeaveHours; } else { if (IsPaidHour) { row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = attendanceTableItemProfile[i].WorkPaidHours; } else { row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = attendanceTableItemProfile[i].WorkHours; } } } foreach (var leave in leavedayTypes) { if (leave.CodeStatistic == null) continue; if (leave_Hour.ContainsKey(leave.CodeStatistic)) { row[leave.CodeStatistic] = leave_Hour[leave.CodeStatistic]; } } #region Tăng Ca if (attendanceTableProfile.Overtime1Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime1Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime1Hours; } if (attendanceTableProfile.Overtime2Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime2Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime2Hours; } if (attendanceTableProfile.Overtime3Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime3Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime3Hours; } if (attendanceTableProfile.Overtime4Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime4Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime4Hours; } if (attendanceTableProfile.Overtime5Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime5Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime5Hours; } if (attendanceTableProfile.Overtime6Type != null) { var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime6Type).FirstOrDefault(); if (overtime != null) row[overtime.CodeStatistic] = attendanceTableProfile.Overtime6Hours; } #endregion row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TotalPregnancy] = preg; foreach (var jobcode in listjobType) { if (table.Columns.Contains(jobcode.Code)) { double? nohourByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID).Sum(s => s.NoHour); double? sectorByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID).Sum(s => s.Sector); row[jobcode.Code] = nohourByPro == null ? 0 : nohourByPro.Value; row[jobcode.Code + "_Sector"] = sectorByPro == null ? 0 : sectorByPro.Value; } } table.Rows.Add(row); } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); config.Add("format", "{0:dd/mm/yyyy}"); configs.Add("DateFrom", config); return table.ConfigTable(); } }
public DataTable GetReportSummaryOvertimeMonth(DateTime dateStart, DateTime dateEndSearch, string orgStructureID, List<Hre_ProfileEntity> lstProfileObjects, Guid[] lstOvertimetypeIDs, Guid[] lstShiftIDs, bool IsDisplay0Data, bool isIncludeQuitEmp, string userExport, string UserLogin) { DataTable table = CreateReportSummaryOvertimeMonthSchema(); DateTime dateEnd = dateEndSearch.AddDays(1).AddSeconds(-1); using (var context = new VnrHrmDataContext()) { BaseService basevice = new BaseService(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); string key = AttendanceDataStatus.E_APPROVED.ToString(); // var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); string status = string.Empty; List<object> paraOvertime = new List<object>(); paraOvertime.AddRange(new object[3]); paraOvertime[0] = orgStructureID; paraOvertime[1] = dateStart; paraOvertime[2] = dateEnd; var overtimeDays_Actual = basevice.GetData<Att_OvertimeEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList(); string E_CASHOUT = MethodOption.E_CASHOUT.ToString(); var lstProfileIds = lstProfileObjects.Select(p => p.ID).ToList(); //var overtimeDays = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key // && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Select(s => //new { s.ProfileID, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours }).ToList(); var profileIds = overtimeDays_Actual.Select(s => s.ProfileID).Distinct().ToList(); var profiles = lstProfileObjects.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypeIds = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).Distinct().ToList(); // var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); //var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) // .Select(s => new { s.ProfileID, s.ShiftID }).ToList(); var workDays = basevice.GetData<Att_WorkdayEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Workday, UserLogin, ref status).ToList(); //if (lstProfileIds != null && lstProfileIds[0] != null && lstProfileIds[0] != Guid.Empty) //{ // profiles = profiles.Where(s => lstProfileIds.Contains(s.ID)).ToList(); //} if (lstOvertimetypeIDs != null) { overtimeDays_Actual = overtimeDays_Actual.Where(s => lstOvertimetypeIDs.Contains(s.OvertimeTypeID)).ToList(); } var overtimeDays_Remain = overtimeDays_Actual.Where(m => m.MethodPayment == E_CASHOUT).ToList(); if (lstShiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && lstShiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } List<Guid> guids = profiles.Select(s => s.ID).ToList(); overtimeDays_Actual = overtimeDays_Actual.Where(s => guids.Contains(s.ProfileID)).ToList(); if (IsDisplay0Data) { var profileIDs = overtimeDays_Actual.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList(); } foreach (var profile in profiles) { DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateTo] = dateEnd; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.UserExport] = userExport; foreach (var overtime in overtimeTypeIds) { if (overtime != null && !string.IsNullOrEmpty(overtime.OvertimeTypeName) && table.Columns.Contains(overtime.OvertimeTypeName)) { var sum = overtimeDays_Actual.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.RegisterHours); row[overtime.OvertimeTypeName] = sum > 0 ? (object)sum : DBNull.Value; var sum1 = overtimeDays_Remain.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.ApproveHours); row[overtime.OvertimeTypeName + "Approve"] = sum1 > 0 ? (object)sum1 : DBNull.Value; } } table.Rows.Add(row); } var configs = new Dictionary<string, Dictionary<string, object>>(); var confighidden = new Dictionary<string, object>(); var configwidthCodeEmp = new Dictionary<string, object>(); var configwidthCodeOrg = new Dictionary<string, object>(); var configwidthNameOrg = new Dictionary<string, object>(); configwidthCodeEmp.Add("width", 88); configs.Add("CodeEmp", configwidthCodeEmp); configwidthCodeOrg.Add("width", 90); configs.Add("CodeOrg", configwidthCodeOrg); configs.Add("CodeBranch", configwidthCodeOrg); configs.Add("CodeTeam", configwidthCodeOrg); configs.Add("CodePosition", configwidthCodeOrg); configs.Add("CodeJobtitle", configwidthCodeOrg); configs.Add("CodeSection", configwidthCodeOrg); configwidthNameOrg.Add("width", 110); configs.Add("BranchName", configwidthNameOrg); configs.Add("OrgName", configwidthNameOrg); configs.Add("TeamName", configwidthNameOrg); configs.Add("SectionName", configwidthNameOrg); confighidden.Add("hidden", true); configs.Add("DateFrom", confighidden); configs.Add("DateTo", confighidden); configs.Add("UserExport", confighidden); configs.Add("DateExport", confighidden); return table.ConfigTable(configs); } }
public DataTable GetReportGeneralMonthlyAttendance(DateTime? dateStart, DateTime DateTo, string strOrgStructure, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, Guid[] payrollIDs, string employeeStatus, string groupByType, bool ExcludeIfWorkingDayIsZero, bool isShowOTHourRow, string userExport, string UserLogin) { using (var context = new VnrHrmDataContext()) { var basesevise = new BaseService(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoProfile = new Hre_ProfileRepository(unitOfWork); var repoAtt_Att_LeaveDay = new CustomBaseRepository<Att_LeaveDay>(unitOfWork); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var repoWorkDay = new Att_WorkDayRepository(unitOfWork); var repoAtt_Grade = new CustomBaseRepository<Att_Grade>(unitOfWork); var repoAtt_AttendencaTable = new Att_AttendanceTableRepository(unitOfWork); var repoAtt_AttendanceItem = new Att_AttendanceTableItemRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoAtt_CutOffDuration = new Att_CutOffDurationRepository(unitOfWork); // var monthDuration = dateStart.ToString(); DataTable table = CreateReportGeneralMonthlyAttendanceSchema(UserLogin); dateStart = new DateTime(dateStart.Value.Year, dateStart.Value.Month, 1); // Guid? durationID = monthDuration != null ? (Guid?)(object)monthDuration : null; DateTime monthEnd = new DateTime(dateStart.Value.Year, dateStart.Value.Month, DateTime.DaysInMonth(dateStart.Value.Year, dateStart.Value.Month)); // profiles = profiles.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.PayrollGroupID }).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList(); var overtimTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).ToList(); List<Guid> profileIds = profiles.Select(s => s.ID).ToList(); DateTime dateFrom = dateStart.Value.Date.AddDays(1 - dateStart.Value.Day); DateTime dateTo = dateFrom.Date.AddDays(1 - dateFrom.Day).AddMonths(1).AddSeconds(-1); string status = string.Empty; List<object> para = new List<object>(); para.AddRange(new object[3]); para[0] = strOrgStructure; para[1] = dateFrom; para[2] = dateTo; var workDays = basesevise.GetData<Att_WorkdayEntity>(para, ConstantSql.hrm_att_getdata_Workday, UserLogin, ref status).ToList(); string groupBy = groupByType != null ? groupByType.ToString() : string.Empty; #region DataInfo List<object> paratables = new List<object>(); paratables.AddRange(new object[7]); paratables[3] = dateFrom; paratables[4] = dateTo; paratables[5] = 1; paratables[6] = Int32.MaxValue - 1; var tableQueryable = basesevise.GetData<Att_AttendanceTableEntity>(paratables, ConstantSql.hrm_att_sp_get_AttendanceTable, UserLogin, ref status).ToList(); List<object> paratable = new List<object>(); paratable.AddRange(new object[4]); paratable[0] = dateFrom; paratable[1] = dateTo; paratable[2] = 1; paratable[3] = Int32.MaxValue - 1; var itemQueryable = basesevise.GetData<Att_AttendanceTableItemEntity>(paratable, ConstantSql.hrm_att_sp_getdata_AttendanceTableItem, UserLogin, ref status).ToList(); #region durationID //if (durationID != null && durationID != Guid.Empty) //{ // var cutOffDuration = repoAtt_CutOffDuration.FindBy( // d => d.ID == durationID).Select(d => new { d.DateStart, d.DateEnd }).FirstOrDefault(); // if (cutOffDuration != null) // { // dateFrom = cutOffDuration.DateStart; // dateTo = cutOffDuration.DateEnd; // } // tableQueryable = tableQueryable.Where(d => d.CutOffDurationID == durationID).ToList(); //} //else //{ // tableQueryable = tableQueryable.Where(d => d.MonthYear == dateFrom).ToList(); // } #endregion tableQueryable = tableQueryable.Where(d => d.MonthYear == dateFrom).ToList(); #endregion if (payrollIDs != null) { profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList(); } if (employeeStatus != null) { if (employeeStatus == StatusEmployee.E_WORKING.ToString()) { profiles = profiles.Where(pro => (pro.DateQuit == null || pro.DateQuit >= dateTo) && pro.DateHire < dateFrom).ToList(); } else if (employeeStatus == StatusEmployee.E_NEWEMPLOYEE.ToString()) { profiles = profiles.Where(pro => pro.DateHire <= dateTo && pro.DateHire >= dateFrom).ToList(); } else if (employeeStatus == StatusEmployee.E_STOPWORKING.ToString()) { profiles = profiles.Where(pro => pro.DateQuit != null && pro.DateQuit.Value <= dateTo && pro.DateQuit.Value >= dateFrom).ToList(); } else if (employeeStatus == StatusEmployee.E_WORKINGANDNEW.ToString()) { profiles = profiles.Where(pro => pro.DateQuit == null || pro.DateQuit >= dateTo).ToList(); } } profileIds = profiles.Select(d => d.ID).ToList<Guid>(); tableQueryable = tableQueryable.Where(d => profileIds.Contains(d.ProfileID)).ToList(); var listAttendanceTable = tableQueryable .Select(d => new { d.ID, d.ProfileID, d.AnlDayAvailable, d.StdWorkDayCount, d.RealWorkDayCount, d.LateEarlyDeductionHours, d.PaidWorkDayCount, d.AnlDayTaken, d.Overtime1Hours, d.Overtime2Hours, d.Overtime3Hours, d.Overtime4Hours, d.Overtime5Hours, d.Overtime6Hours, d.Overtime1Type, d.Overtime2Type, d.Overtime3Type, d.Overtime4Type, d.Overtime5Type, d.Overtime6Type, d.LeaveDay1Hours, d.LeaveDay2Hours, d.LeaveDay3Hours, d.LeaveDay4Hours, d.NightShiftHours, d.LeaveDay1Type, d.LeaveDay2Type, d.LeaveDay3Type, d.LeaveDay4Type, }).ToList(); profileIds = listAttendanceTable.Select(s => s.ProfileID).ToList(); List<Guid> listAttendanceTableID = listAttendanceTable.Select(d => d.ID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); var listProfileInfo = profiles.Select(d => new { d.ID, d.CodeEmp, d.ProfileName, d.DateHire, d.DateQuit, d.LaborType, d.WorkingPlace, d.OrgStructureID, d.WorkPlaceName, d.PositionName, d.JobTitleName, d.EmployeeTypeName, d.OrgStructureName, }).ToList(); itemQueryable = itemQueryable.Where(d => listAttendanceTableID.Contains(d.AttendanceTableID) && d.WorkDate >= dateFrom && d.WorkDate <= dateTo).ToList(); var listAttendanceTableItem = itemQueryable.Select(d => new { d.AttendanceTableID, d.WorkDate, d.OvertimeHours, d.ExtraOvertimeHours, d.ExtraOvertimeHours2, d.ExtraOvertimeHours3, d.WorkPaidHours, d.NightShiftHours, d.ShiftID, d.WorkHourFirstHaftShift, d.WorkHourLastHaftShift, d.LeaveTypeID, d.AvailableHours, }).ToList(); var listLeaveDay = repoAtt_Att_LeaveDay.FindBy(d => profileIds.Contains(d.ProfileID) && dateFrom <= d.DateStart && d.DateEnd <= dateTo).Select(d => new { d.ProfileID, d.Comment }).ToList(); var listGrade = repoAtt_Grade.FindBy(d => profileIds.Contains(d.ProfileID.Value) && d.IsDelete == null).OrderByDescending(d => d.MonthStart).Select(d => new { d.ProfileID, d.MonthStart, d.Cat_GradeAttendance.HourOnWorkDate }).ToList(); var lstShift = repoCat_Shift.FindBy(s => s.IsDelete == null).Select(d => new { d.ID, d.IsDoubleShift, d.Code, d.FirstShiftID, d.LastShiftID }).ToList(); DataTable table_lstResult = new DataTable(); #region bang 7 dung store ben sql table_lstResult.Columns.Add("ProfileID"); for (int i = 1; i <= 31; i++) { table_lstResult.Columns.Add("Data" + i); } //lay du lieu bang cat_shift // var lst_CatShift = repoCat_Shift.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.IsDoubleShift, s.Code, s.FirstShiftID, s.LastShiftID }).ToList(); var lst_CatShift = lstShift.ToList(); //var lstAttendanceTable = repoAtt_AttendencaTable.FindBy(s => s.IsDelete == null && s.MonthYear == dateFrom && profileIds.Contains(s.ProfileID)).Select(s => new { s.ID, s.ProfileID }).ToList(); var lstAttendanceTable = listAttendanceTable.ToList(); var lstAttendanceTableID = lstAttendanceTable.Select(s => s.ID).Distinct().ToList(); //var lstAttendanceTableItem = repoAtt_AttendanceItem.FindBy(s => s.IsDelete == null && lstAttendanceTableID.Contains(s.AttendanceTableID)) // .Select(s => new {s.ID,s.AttendanceTableID,s.LeaveTypeID,s.WorkPaidHours,s.AvailableHours,s.ShiftID,s.WorkHourFirstHaftShift,s.WorkHourLastHaftShift,s.WorkDate }).ToList(); var lstAttendanceTableItem = listAttendanceTableItem.ToList(); int countId = lstAttendanceTableID.Count; // DataRow rowlstResult = table_lstResult.NewRow(); var lstCat_LeaDayType = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null).ToList(); //while(countId >0) //{ for (int i = countId; i > 0; i--) { // } var AttendanceID = lstAttendanceTable.Select(s => s.ID).FirstOrDefault(); var ProfileID = lstAttendanceTable.Select(s => s.ProfileID).FirstOrDefault(); lstAttendanceTable = lstAttendanceTable.Where(s => s.ID != AttendanceID).ToList(); DataRow rowlstResult = table_lstResult.NewRow(); rowlstResult["ProfileID"] = ProfileID; //int Numday = 1; //while(Numday <=31) //{ for (int Numday = 1; Numday <= 31; Numday++) { // } var tab_AttendanceTableItem = lstAttendanceTableItem.Where(s => s.AttendanceTableID == AttendanceID && s.WorkDate.Day == Numday) .Select(s => new { s.LeaveTypeID, s.WorkPaidHours, s.AvailableHours, s.ShiftID, s.WorkHourFirstHaftShift, s.WorkHourLastHaftShift }).ToList(); var leaveTypeID = tab_AttendanceTableItem.Select(s => s.LeaveTypeID).FirstOrDefault(); double workPaidHour = tab_AttendanceTableItem.Select(s => s.WorkPaidHours).FirstOrDefault(); double available = tab_AttendanceTableItem.Select(s => s.AvailableHours).FirstOrDefault(); available = 8; var shiftID = tab_AttendanceTableItem.Select(s => s.ShiftID).FirstOrDefault(); var isDoubleShift = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.IsDoubleShift).FirstOrDefault(); double? workHourFirstHaftShift = tab_AttendanceTableItem.Select(s => s.WorkHourFirstHaftShift).FirstOrDefault(); double? workHourLastHaftShift = tab_AttendanceTableItem.Select(s => s.WorkHourLastHaftShift).FirstOrDefault(); string result = string.Empty; if (leaveTypeID != null) { result = lstCat_LeaDayType.Where(s => s.ID == leaveTypeID).Select(s => s.CodeStatistic).ToString(); } else { if (isDoubleShift == true) { if (available == workHourFirstHaftShift) { var firstShiftID = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.FirstShiftID).FirstOrDefault(); result = lst_CatShift.Where(s => s.ID == firstShiftID).Select(s => s.Code).FirstOrDefault(); } else { result = workHourFirstHaftShift.ToString(); } if (available == workHourLastHaftShift) { var lastShiftID = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.LastShiftID).FirstOrDefault(); result = result + "-" + lst_CatShift.Where(s => s.ID == lastShiftID).Select(s => s.Code).FirstOrDefault(); } else { result = result + "-" + workHourLastHaftShift.ToString(); } } else { if (available == workPaidHour) { result = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.Code).FirstOrDefault(); } else { result = workPaidHour.ToString(); } } } rowlstResult["Data" + Numday] = result; //Numday++; } table_lstResult.Rows.Add(rowlstResult); //countId--; } #endregion DateTime DateMin = DateTime.MinValue; DateTime DateMax = DateTime.MinValue; DateTime? DateS = tableQueryable.Select(m => m.DateStart).FirstOrDefault(); DateTime? DateE = tableQueryable.Select(m => m.DateEnd).FirstOrDefault(); if (DateS != null) DateMin = DateS.Value; if (DateE != null) DateMax = DateE.Value; foreach (var profile in profiles) { DataRow row = table.NewRow(); var attendanceTable = listAttendanceTable.Where(s => s.ProfileID == profile.ID).FirstOrDefault(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; //row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; // row[Att_ReportMonthlyTimeSheetEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; if (profile.DateHire != null) { row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateHire] = profile.DateHire.Value; } if (profile.DateQuit != null) { row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateQuit] = profile.DateQuit.Value; } row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.UserExport] = userExport; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateExport] = DateTime.Today; var gradeByProfile = listGrade.Where(d => d.ProfileID == attendanceTable.ProfileID).OrderByDescending(d => d.MonthStart).FirstOrDefault(); var listAttendanceTableItemByTable = listAttendanceTableItem.Where(d => d.AttendanceTableID == attendanceTable.ID).ToList(); var listLeaveDayByProfile = listLeaveDay.Where(d => d.ProfileID == attendanceTable.ProfileID).ToList(); double hourOnWorkDate = gradeByProfile != null ? double.Parse(gradeByProfile.HourOnWorkDate.ToString()) : 1; #region Att_AttendanceTable row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.PaidWorkDayCount] = attendanceTable.PaidWorkDayCount; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.LateEarlyDeductionHours] = attendanceTable.LateEarlyDeductionHours; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.LateEarlyDeductionDays] = attendanceTable.LateEarlyDeductionHours / hourOnWorkDate; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.NightShiftHours] = attendanceTable.NightShiftHours; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.AnlDayAvailable] = attendanceTable.AnlDayAvailable; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.StandardWorkDays] = attendanceTable.StdWorkDayCount; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.RealWorkingDays] = attendanceTable.RealWorkDayCount; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.StandardWorkDaysFormula] = attendanceTable.RealWorkDayCount; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DateFrom] = DateMin; row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DateTo] = DateMax; if (attendanceTable.AnlDayTaken > 0) { row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.ANL] = attendanceTable.AnlDayTaken; } #endregion #region table_lstResult Att_AttendanceTableItem_Symbol var dtRow = table_lstResult.Rows.OfType<DataRow>().Where(d => d["ProfileID"].GetString() == profile.ID.ToString()).FirstOrDefault(); if (dtRow != null) { for (DateTime dateCheck = DateMin; dateCheck < DateMax; dateCheck = dateCheck.AddDays(1)) { string Result = string.Empty; switch (dateCheck.Day) { case 1: Result = dtRow["Data1"].ToString(); break; case 2: Result = dtRow["Data2"].ToString(); break; case 3: Result = dtRow["Data3"].ToString(); break; case 4: Result = dtRow["Data4"].ToString(); break; case 5: Result = dtRow["Data5"].ToString(); break; case 6: Result = dtRow["Data6"].ToString(); break; case 7: Result = dtRow["Data7"].ToString(); break; case 8: Result = dtRow["Data8"].ToString(); break; case 9: Result = dtRow["Data9"].ToString(); break; case 10: Result = dtRow["Data10"].ToString(); break; case 11: Result = dtRow["Data11"].ToString(); break; case 12: Result = dtRow["Data12"].ToString(); break; case 13: Result = dtRow["Data13"].ToString(); break; case 14: Result = dtRow["Data14"].ToString(); break; case 15: Result = dtRow["Data15"].ToString(); break; case 16: Result = dtRow["Data16"].ToString(); break; case 17: Result = dtRow["Data17"].ToString(); break; case 18: Result = dtRow["Data18"].ToString(); break; case 19: Result = dtRow["Data19"].ToString(); break; case 20: Result = dtRow["Data20"].ToString(); break; case 21: Result = dtRow["Data21"].ToString(); break; case 22: Result = dtRow["Data22"].ToString(); break; case 23: Result = dtRow["Data23"].ToString(); break; case 24: Result = dtRow["Data24"].ToString(); break; case 25: Result = dtRow["Data25"].ToString(); break; case 26: Result = dtRow["Data26"].ToString(); break; case 27: Result = dtRow["Data27"].ToString(); break; case 28: Result = dtRow["Data28"].ToString(); break; case 29: Result = dtRow["Data29"].ToString(); break; case 30: Result = dtRow["Data30"].ToString(); break; case 31: Result = dtRow["Data31"].ToString(); break; } //row["Data" + dateCheck.Day] = Result; string day = dateCheck.Day.ToString(); row[day] = Result; } } #endregion #region att_AttendanceTableItem int WorkPaidHourOver8 = 0; int NightShiftOver6 = 0; double WorkPaidHours = 0; foreach (var attendanceTableItem in listAttendanceTableItemByTable) { var shift = lstShift.Where(m => m.ID == attendanceTableItem.ShiftID).FirstOrDefault(); if (shift != null && shift.IsDoubleShift == true) { if (attendanceTableItem.WorkHourFirstHaftShift >= 8) { WorkPaidHourOver8++; } if (attendanceTableItem.WorkHourLastHaftShift >= 8) { WorkPaidHourOver8++; } } else if (attendanceTableItem.WorkPaidHours >= 8) { WorkPaidHourOver8++; } if (attendanceTableItem.NightShiftHours >= 6) { NightShiftOver6++; } WorkPaidHours += attendanceTableItem.WorkPaidHours; } row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.WorkPaidHourOver8] = WorkPaidHourOver8; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.NightHourOver6] = NightShiftOver6; row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.WorkPaidHours] = WorkPaidHours; #endregion #region LeaveDayComment string leaveDayComment = string.Empty; foreach (var leaveDay in listLeaveDayByProfile) { if (!string.IsNullOrWhiteSpace(leaveDayComment)) { leaveDayComment += ", "; } leaveDayComment += leaveDay.Comment; } row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.Comment] = leaveDayComment; #endregion #region LeaveDay1Hours // string LeaveDayTypeCode1 = string.Empty; var LeaveDayTypeCode1 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay1Type).Select(s => s.Code).FirstOrDefault(); string leaveDayTypeCode = LeaveDayTypeCode1; var LeaveDayTypeCodeStatistic1 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay1Type).Select(s => s.CodeStatistic).FirstOrDefault(); if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic1)) { leaveDayTypeCode = LeaveDayTypeCodeStatistic1; } leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(leaveDayTypeCode)) { if (table.Columns.Contains(leaveDayTypeCode)) { row[leaveDayTypeCode] = attendanceTable.LeaveDay1Hours / hourOnWorkDate; } if (table.Columns.Contains("h" + leaveDayTypeCode)) { row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay1Hours; } } #endregion #region LeaveDay2Hours leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay2Type).Select(s => s.Code).FirstOrDefault(); var LeaveDayTypeCodeStatistic2 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay2Type).Select(s => s.CodeStatistic).FirstOrDefault(); if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic2)) { leaveDayTypeCode = LeaveDayTypeCodeStatistic2; } leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(leaveDayTypeCode)) { if (table.Columns.Contains(leaveDayTypeCode)) { row[leaveDayTypeCode] = attendanceTable.LeaveDay2Hours / hourOnWorkDate; } if (table.Columns.Contains("h" + leaveDayTypeCode)) { row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay2Hours; } } #endregion #region LeaveDay3Hours leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay3Type).Select(s => s.Code).FirstOrDefault(); var LeaveDayTypeCodeStatistic3 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay3Type).Select(s => s.CodeStatistic).FirstOrDefault(); if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic3)) { leaveDayTypeCode = LeaveDayTypeCodeStatistic3; } leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(leaveDayTypeCode)) { if (table.Columns.Contains(leaveDayTypeCode)) { row[leaveDayTypeCode] = attendanceTable.LeaveDay3Hours / hourOnWorkDate; } if (table.Columns.Contains("h" + leaveDayTypeCode)) { row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay3Hours; } } #endregion #region LeaveDay4Hours leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay4Type).Select(s => s.Code).FirstOrDefault(); var LeaveDayTypeCodeStatistic4 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay4Type).Select(s => s.CodeStatistic).FirstOrDefault(); if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic4)) { leaveDayTypeCode = LeaveDayTypeCodeStatistic4; } leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(leaveDayTypeCode)) { if (table.Columns.Contains(leaveDayTypeCode)) { row[leaveDayTypeCode] = attendanceTable.LeaveDay4Hours / hourOnWorkDate; } if (table.Columns.Contains("h" + leaveDayTypeCode)) { row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay4Hours; } } #endregion #region Cat_Overtime var OvertimeTypeCode1 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime1Type).Select(s => s.Code).FirstOrDefault(); string overtimeTypeCode = OvertimeTypeCode1; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime1Hours; } } var OvertimeTypeCode2 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime2Type).Select(s => s.Code).FirstOrDefault(); overtimeTypeCode = OvertimeTypeCode2; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime2Hours; } } var OvertimeTypeCode3 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime3Type).Select(s => s.Code).FirstOrDefault(); overtimeTypeCode = OvertimeTypeCode3; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime3Hours; } } var OvertimeTypeCode4 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime4Type).Select(s => s.Code).FirstOrDefault(); overtimeTypeCode = OvertimeTypeCode4; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime4Hours; } } var OvertimeTypeCode5 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime5Type).Select(s => s.Code).FirstOrDefault(); overtimeTypeCode = OvertimeTypeCode5; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime5Hours; } } var OvertimeTypeCode6 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime6Type).Select(s => s.Code).FirstOrDefault(); overtimeTypeCode = OvertimeTypeCode6; overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_'); if (!string.IsNullOrWhiteSpace(overtimeTypeCode)) { if (table.Columns.Contains(overtimeTypeCode)) { row[overtimeTypeCode] = attendanceTable.Overtime6Hours; } } #endregion table.Rows.Add(row); #region second Row (OT HOUR) if (isShowOTHourRow) { DataRow dr_OT_Hour = table.NewRow(); foreach (var attendanceTableItem in listAttendanceTableItemByTable) { int date = attendanceTableItem.WorkDate.Day; double totalHourOT = attendanceTableItem.OvertimeHours; totalHourOT += attendanceTableItem.ExtraOvertimeHours; totalHourOT += attendanceTableItem.ExtraOvertimeHours2; totalHourOT += attendanceTableItem.ExtraOvertimeHours3; dr_OT_Hour[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.Data + date] = totalHourOT; } table.Rows.Add(dr_OT_Hour); } #endregion } #region Code cu #endregion var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); var configdouble = new Dictionary<string, object>(); var config90 = new Dictionary<string, object>(); var config95 = new Dictionary<string, object>(); var config110 = new Dictionary<string, object>(); var config81 = new Dictionary<string, object>(); config81.Add("width", 81); for (int i = 1; i <= 31; i++) { if (!configs.ContainsKey("_" + i.ToString())) configs.Add("_" + i.ToString(), config81); } config95.Add("width", 95); if (!configs.ContainsKey("DateQuit")) configs.Add("DateQuit", config95); config90.Add("width", 90); if (!configs.ContainsKey("DateHire")) configs.Add("DateHire", config90); if (!configs.ContainsKey("CodeEmp")) configs.Add("CodeEmp", config90); if (!configs.ContainsKey("DepartmentCode")) configs.Add("DepartmentCode", config90); if (!configs.ContainsKey("BranchCode")) configs.Add("BranchCode", config90); if (!configs.ContainsKey("TeamCode")) configs.Add("TeamCode", config90); if (!configs.ContainsKey("SectionCode")) configs.Add("SectionCode", config90); config110.Add("width", 110); if (!configs.ContainsKey("BranchName")) configs.Add("BranchName", config110); if (!configs.ContainsKey("TeamName")) configs.Add("TeamName", config110); if (!configs.ContainsKey("SectionName")) configs.Add("SectionName", config110); if (!configs.ContainsKey("PositionName")) configs.Add("PositionName", config110); if (!configs.ContainsKey("JobTitleName")) configs.Add("JobTitleName", config110); config.Add("hidden", true); if (!configs.ContainsKey("DateFrom")) configs.Add("DateFrom", config); if (!configs.ContainsKey("DateTo")) configs.Add("DateTo", config); if (!configs.ContainsKey("UserExport")) configs.Add("UserExport", config); if (!configs.ContainsKey("DateExport")) configs.Add("DateExport", config); configdouble.Add("format", "{0:n2}"); if (!configs.ContainsKey("RealWorkingDays")) configs.Add("RealWorkingDays", configdouble); if (!configs.ContainsKey("StandardWorkDaysFormula")) configs.Add("StandardWorkDaysFormula", configdouble); return table.ConfigTable(configs); } }
public DataTable GetReportMonthlyRoster(DateTime DateStart, DateTime DateEnd, string orgStructureID, List<Hre_ProfileEntity> profiles, bool _onlyHolydayNotHaveRoster, Guid[] lstpayrollIDs, string userExport, string UserLogin) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (UnitOfWork)(new UnitOfWork(context)); BaseService basevervice = new BaseService(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_Shift = new CustomBaseRepository<Cat_Shift>(unitOfWork); var repoCat_DayOff = new CustomBaseRepository<Cat_DayOff>(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null && s.IsDelete == null).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var positions = repoCat_Position.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); if (DateEnd != null) DateEnd = DateEnd.AddDays(1).AddMilliseconds(-1); if (lstpayrollIDs != null) { profiles = profiles.Where(p => p.PayrollGroupID.HasValue && lstpayrollIDs.Contains(p.PayrollGroupID.Value)).ToList(); } string key = RosterStatus.E_APPROVED.ToString(); string key1 = RosterType.E_TIME_OFF.ToString(); List<Att_Roster> rosters = null; var listProfileID = profiles.Select(d => d.ID).ToList(); string status = string.Empty; List<object> paraAtt_Roster = new List<object>(); paraAtt_Roster.AddRange(new object[4]); paraAtt_Roster[0] = (object)orgStructureID; paraAtt_Roster[1] = DateStart; paraAtt_Roster[2] = DateEnd; paraAtt_Roster[3] = key; rosters = basevervice.GetData<Att_RosterEntity>(paraAtt_Roster, ConstantSql.hrm_att_getdata_Roster, UserLogin, ref status).Where(s => listProfileID.Contains(s.ProfileID)).ToList().Translate<Att_Roster>(); var listShift = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList(); DataTable table = CreateReportMonthlyRosterSchema(listShift); if (profiles == null) return table; int stt = 0; foreach (var profile in profiles) { stt++; DataRow row = table.NewRow(); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); Guid? orgId = profile.OrgStructureID; var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); row[Att_ReportMonthlyRosterEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportMonthlyRosterEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportMonthlyRosterEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.GroupCode] = orgGroup != null ? orgGroup.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.GroupName] = orgGroup != null ? orgGroup.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.DivisionCode] = orgDivision != null ? orgDivision.Code : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportMonthlyRosterEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportMonthlyRosterEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.JobtitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; row[Att_ReportMonthlyRosterEntity.FieldNames.DateFrom] = DateStart; row[Att_ReportMonthlyRosterEntity.FieldNames.DateTo] = DateEnd; row[Att_ReportMonthlyRosterEntity.FieldNames.DateExport] = DateTime.Today; row[Att_ReportMonthlyRosterEntity.FieldNames.UserExport] = userExport; var attRosters = rosters.Where(s => s.ProfileID == profile.ID && s.Type != key1).OrderBy(s => s.DateStart).ToList(); #region CheckData foreach (Att_Roster attRoster in attRosters) { for (DateTime date = attRoster.DateStart; date <= attRoster.DateEnd; date = date.AddDays(1)) { #region GetHeaderRow if (stt == 1) { row["Header" + date.Day] = date.ToString("dd-MMM"); } else if (stt == 2) { row["Header" + date.Day] = date.DayOfWeek.ToString(); } #endregion #region GetShiff if (DateStart > date || date > DateEnd) { break; } if (attRoster.Type == RosterType.E_CHANGE_SHIFT.ToString()) { var shift = listShift.Where(d => d.ID == attRoster.MonShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (attRoster.Type == RosterType.E_TIME_OFF.ToString()) { row["Data" + date.Day] = ""; } else if (date.DayOfWeek == DayOfWeek.Monday) { var shift = listShift.Where(d => d.ID == attRoster.MonShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else { if (date.DayOfWeek == DayOfWeek.Tuesday) { var shift = listShift.Where(d => d.ID == attRoster.TueShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (date.DayOfWeek == DayOfWeek.Wednesday) { var shift = listShift.Where(d => d.ID == attRoster.WedShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (date.DayOfWeek == DayOfWeek.Thursday) { var shift = listShift.Where(d => d.ID == attRoster.ThuShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (date.DayOfWeek == DayOfWeek.Friday) { var shift = listShift.Where(d => d.ID == attRoster.FriShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (date.DayOfWeek == DayOfWeek.Saturday) { var shift = listShift.Where(d => d.ID == attRoster.SatShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } else if (date.DayOfWeek == DayOfWeek.Sunday) { var shift = listShift.Where(d => d.ID == attRoster.SunShiftID).FirstOrDefault(); row["Data" + date.Day] = shift == null ? "" : shift.Code; } } #endregion } } #endregion table.Rows.Add(row); } if (_onlyHolydayNotHaveRoster) { DataTable tblData = CreateReportMonthlyRosterSchema(listShift); List<Cat_DayOff> dayOffs = repoCat_DayOff.FindBy(s => s.IsDelete == null && s.DateOff.Year == DateStart.Year).ToList(); foreach (DataRow dataRow in table.Rows) { foreach (Cat_DayOff catDayOff in dayOffs) { if (dataRow["Data" + catDayOff.DateOff.Day].IsNullOrEmpty()) { DataRow row = tblData.NewRow(); foreach (DataColumn dataColumn in table.Columns) { row[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName]; } for (int j = 1; j <= 31; j++) { row["Data" + j] = ""; } row["Data" + catDayOff.DateOff.Day] = "HLD"; table.Rows.Add(row); break; } } } table = tblData; } DataTable table1 = CreateReportMonthlyRosterSchema(listShift); foreach (DataRow dataRow in table.Rows) { DataRow row = table1.NewRow(); bool isCheck = false; foreach (DataColumn dataColumn in table.Columns) { if (dataColumn.ColumnName.IndexOf("Data") > -1 && !isCheck) { int i = 1; for (DateTime date = DateStart; date <= DateEnd; date = date.AddDays(1)) { if (table1.Columns.Contains("Data" + i)) { row["Data" + i] = dataRow["Data" + date.Day]; row["Header" + i] = dataRow["Header" + date.Day]; } i++; } isCheck = true; } else if (dataColumn.ColumnName.IndexOf("Data") == -1) { row[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName]; } } table1.Rows.Add(row); } return table1; } }
public DataTable GetReportDetailOvertime(string userExport, DateTime dateStart, DateTime dateEndSearch, string ProfileName, string CodeEmp, string OrgIDString, List<Guid?> lstovertimeTypeIds, bool noDisplay0Data, bool isIncludeQuitEmp) { DataTable table = CreateReportDetailOvertimeSchema(); DateTime dateEnd = dateEndSearch.AddDays(1).AddMinutes(-1); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); string key = OverTimeStatus.E_APPROVED.ToString(); string E_CASHOUT = MethodOption.E_CASHOUT.ToString(); var overtimesQuery = unitOfWork.CreateQueryable<Att_Overtime>(s => s.IsDelete == null && (s.RegisterHours > 0 || s.ApproveHours > 0) && s.Status == key && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.MethodPayment == null || s.MethodPayment == E_CASHOUT)); if (!string.IsNullOrEmpty(OrgIDString)) { List<Guid> OrgIDs = new List<Guid>(); List<string> OrgIDsArr = OrgIDString.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); foreach (var item in OrgIDsArr) { Guid OrgID = Guid.Empty; Guid.TryParse(item, out OrgID); if (OrgID != Guid.Empty) { OrgIDs.Add(OrgID); } } overtimesQuery = overtimesQuery.Where(m => m.OrgStructureID != null && OrgIDs.Contains(m.OrgStructureID.Value)); } if (!string.IsNullOrEmpty(ProfileName)) { List<string> lstProfileName = ProfileName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); if (lstProfileName.Count > 1) { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstProfileName.Contains(m.Hre_Profile.ProfileName)); } else { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.ProfileName != null && (m.Hre_Profile.ProfileName.Contains(ProfileName))); } } if (!string.IsNullOrEmpty(CodeEmp)) { List<string> lstCodeEmp = CodeEmp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); if (lstCodeEmp.Count > 1) { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstCodeEmp.Contains(m.Hre_Profile.CodeEmp)); } else { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.CodeEmp != null && (m.Hre_Profile.CodeEmp.Contains(CodeEmp))); } } if (noDisplay0Data) { overtimesQuery = overtimesQuery.Where(s => s.ApproveHours > 0); } if (lstovertimeTypeIds != null && lstovertimeTypeIds[0] != null && lstovertimeTypeIds[0] != Guid.Empty) { overtimesQuery = overtimesQuery.Where(s => lstovertimeTypeIds.Contains(s.OvertimeTypeID)); } var overtimes = overtimesQuery.Select(s => new { s.ProfileID, s.WorkDate, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours, s.AnalyseHour, s.ConfirmHours, s.OrgStructureID, s.ReasonOT }).ToList(); var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null & dateStart <= s.WorkDate && s.WorkDate <= dateEnd) .Select(s => new { s.ProfileID, s.ShiftID, s.InTime1, s.OutTime1, s.WorkDate }).ToList(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList(); if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } foreach (var profile in profiles) { for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var overtimeProfiles = overtimes.Where(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID).ToList(); if (overtimeProfiles.Count == 0) continue; var lastOvertimeByProfile = overtimeProfiles.OrderByDescending(m => m.WorkDate).FirstOrDefault(); DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; if (lastOvertimeByProfile != null) orgId = lastOvertimeByProfile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportDetailOvertimeEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportDetailOvertimeEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportDetailOvertimeEntity.FieldNames.ReasonOT] = lastOvertimeByProfile.ReasonOT; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate == date); var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); row[Att_ReportDetailOvertimeEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportDetailOvertimeEntity.FieldNames.DateTo] = dateEnd; row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportDetailOvertimeEntity.FieldNames.UserExport] = userExport; row[Att_ReportDetailOvertimeEntity.FieldNames.udSubmitApproveHour] = overtimeProfiles.Where(m => m.AnalyseHour != null).Sum(m => m.AnalyseHour); row[Att_ReportDetailOvertimeEntity.FieldNames.DateOvertime] = date; if (workDay != null && workDay.InTime1 != null) { //row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value.ToString("HH:mm:ss"); row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value; } if (workDay != null && workDay.OutTime1 != null) { //row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value.ToString("HH:mm:ss"); row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value; } if (shift != null) { row[Att_ReportDetailOvertimeEntity.FieldNames.ShiftName] = shift.ShiftName; } foreach (var item in overtimeTypes) { var sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID).Sum(s => s.RegisterHours); row[item.Code] = sum > 0 ? (object)sum : DBNull.Value; sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ApproveHours > 0).Sum(s => s.ApproveHours.Value); row[item.Code + "_Approve"] = sum > 0 ? (object)sum : DBNull.Value; sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ConfirmHours != null && s.ConfirmHours > 0).Sum(s => s.ConfirmHours); row[item.Code + "_Confirm"] = sum > 0 ? (object)sum : DBNull.Value; } table.Rows.Add(row); } } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); var configtime = new Dictionary<string, object>(); var configwidthCode = new Dictionary<string, object>(); var configwidthName = new Dictionary<string, object>(); var configwidthshift = new Dictionary<string, object>(); var configwidthudSubmitApproveHour = new Dictionary<string, object>(); configwidthCode.Add("width", 80); configs.Add("CodeBranch", configwidthCode); configs.Add("CodeOrg", configwidthCode); configs.Add("CodeTeam", configwidthCode); configs.Add("CodeSection", configwidthCode); configs.Add("CodeJobtitle", configwidthCode); configs.Add("CodePosition", configwidthCode); configwidthName.Add("width", 110); configs.Add("BranchName", configwidthName); configs.Add("OrgName", configwidthName); configs.Add("TeamName", configwidthName); configs.Add("SectionName", configwidthName); configwidthudSubmitApproveHour.Add("width", 120); configs.Add("udSubmitApproveHour", configwidthudSubmitApproveHour); configwidthshift.Add("width", 90); configs.Add("DateOvertime", configwidthshift); configs.Add("ShiftName", configwidthshift); config.Add("hidden", true); configs.Add("DateFrom", config); configs.Add("DateTo", config); configs.Add("UserExport", config); configs.Add("DateExport", config); configtime.Add("width", 80); configtime.Add("format", "{0:HH:mm:ss}"); configs.Add("udOutTime", configtime); configs.Add("udInTime", configtime); // return table.ConfigDatatable(); return table.ConfigTable(configs); } }
public DataTable GetReportStatisticsOvertimeByYear(int Year, List<Hre_ProfileEntity> profiles, List<Guid?> lstOvertimeTypeIds, bool isNotAllowZero, bool isIncludeQuitEmp, string userExport, string UserLogin) { DataTable table = CreateReportStatisticsOvertimeByYearSchema(); using (var context = new VnrHrmDataContext()) { //int year = Year.Year; int year = Year; DateTime dateFrom = new DateTime(Year, 1, 1); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DateTime dateStart = new DateTime(year, 1, 1); DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)).AddDays(1).AddMinutes(-1); //DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); string key = OverTimeStatus.E_APPROVED.ToString(); // var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork); // var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); List<object> para = new List<object>(); para.AddRange(new object[3]); para[0] = null; para[1] = dateStart; para[2] = dateEnd; var basevices = new BaseService(); string status = string.Empty; var overtimes = basevices.GetData<Att_OvertimeEntity>(para, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList(); //var overtimes = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) // .Select(s => new { s.ProfileID, s.ApproveHours, s.WorkDate, s.OvertimeTypeID }).ToList(); if (isNotAllowZero) { overtimes = overtimes.Where(s => s.ApproveHours > 0).ToList(); } var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); // var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); //var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); //Guid TypeCoLeaveID = EntityService.CreateQueryable<Cat_LeaveDayType>(false, GuidContext, Guid.Empty, m => m.IsTimeOffInLieu == true).Select(m => m.ID).FirstOrDefault(); Guid TypeCoLeaveID = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null && s.IsTimeOffInLieu == true).Select(s => s.ID).FirstOrDefault(); var lstLeaveDay = basevices.GetData<Att_LeaveDayEntity>(para, ConstantSql.hrm_att_getdata_LeaveDay, UserLogin, ref status).Where(s => s.LeaveDayTypeID == TypeCoLeaveID && profileIds.Contains(s.ProfileID)).ToList(); //var lstLeaveDay = EntityService.CreateQueryable<Att_LeaveDay>(false, GuidContext, Guid.Empty, m => m.LeaveDayTypeID == TypeCoLeaveID && m.DateStart <= dateEnd && m.DateEnd >= dateStart && profileIds.Contains(m.ProfileID)) // .Select(m => new { m.ID, m.ProfileID, m.LeaveHours, m.TotalDuration }).Execute(); // var repoTimeOffInLieu = new Att_TimeOffInLieuRepository(unitOfWork); // var timeOfInLieus = repoTimeOffInLieu.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID) && s.OvertimeID != null && dateStart <= s.Date && s.Date <= dateEnd) //.Select(s => new { s.AccumulateLeaves, s.ProfileID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null && s.IsDelete == null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code }).Distinct().ToList(); if (lstOvertimeTypeIds != null && lstOvertimeTypeIds[0] != Guid.Empty && lstOvertimeTypeIds[0] != null) { overtimes = overtimes.Where(s => lstOvertimeTypeIds.Contains(s.OvertimeTypeID)).ToList(); profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } //if (lstProfileIDs.Any() && lstProfileIDs[0] != Guid.Empty && lstProfileIDs[0] != null) //{ // profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList(); //} if (isNotAllowZero) { profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } foreach (var profile in profiles) { DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.UserExport] = userExport; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; //var timeOffInLieuProfiles = timeOfInLieus.Where(s => s.ProfileID == profile.ID).ToList(); //var sumHour = timeOffInLieuProfiles.Sum(s => s.AccumulateLeaves); //if (sumHour > 0) // row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = sumHour; var lstLeaveDayByProfile = lstLeaveDay.Where(m => m.ProfileID == profile.ID).ToList(); if (lstLeaveDayByProfile.Count > 0) { row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = lstLeaveDayByProfile.Sum(m => m.LeaveHours * (m.LeaveDays ?? 0)); } for (int i = 1; i <= 12; i++) { dateStart = new DateTime(year, i, 1); dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i)); // row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = Year; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = dateFrom; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateTo] = dateEnd; var month = overtimes.Where(s => s.ProfileID == profile.ID && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Sum(s => s.ApproveHours); if (month > 0) row["M" + i] = month; } table.Rows.Add(row); } return table; } }
/// <summary> /// [Tam.Le] - 2014/08/08 /// Hàm xử lý load dữ liệu /// </summary> /// <param name="request"></param> /// <param name="otModel"></param> /// <returns></returns> public DataTable LoadData(DateTime dateStart, DateTime dateEnd, List<Guid> orgIDs, string ProfileName, string CodeEmp, Guid LeavedayType, out string ErrorMessages) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); ErrorMessages = string.Empty; //bool isIncludeQuitEmp = true; List<Guid> leavedayTypeIds = new List<Guid>(); if (LeavedayType != null) leavedayTypeIds.Add(LeavedayType); List<Guid> shiftIDs = new List<Guid>(); string E_WAIT_APPROVED = LeaveDayStatus.E_WAIT_APPROVED.ToString(); string E_SUBMIT = LeaveDayStatus.E_SUBMIT.ToString(); var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork); List<Att_LeaveDay> leaveDays = repoAtt_Leaveday.FindBy(s => s.LeaveDays > 0 && (s.Status == E_WAIT_APPROVED || s.Status == E_SUBMIT) && dateStart <= s.DateEnd && s.DateStart <= dateEnd).ToList<Att_LeaveDay>(); foreach (var item in leaveDays) { item.Status = E_WAIT_APPROVED; } var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID)) .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PaidRate }).Distinct().ToList(); var ledvedayPaidIds = leavedayTypes.Where(s => s.PaidRate > 0).Select(s => s.ID).ToList(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.GetAll().ToList(); if (orgIDs != null) { profiles = profiles.Where(s => s.OrgStructureID != null && orgIDs.Contains(s.OrgStructureID.Value)).ToList(); } if (shiftIDs.Count > 0) { workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (leavedayTypeIds.Count > 0) { leaveDays = leaveDays.Where(s => leavedayTypeIds.Contains(s.LeaveDayTypeID)).ToList(); profileIds = leaveDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } //if (!isIncludeQuitEmp) //{ // profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); //} var startYear = new DateTime(dateStart.Year, 1, 1); var endYear = new DateTime(dateStart.Year, 12, DateTime.DaysInMonth(dateStart.Year, 12)); var rosterStatus = RosterStatus.E_APPROVED.ToString(); var repoAtt_Roster = new Att_RosterRepository(unitOfWork); List<Att_Roster> listRoster = repoAtt_Roster.FindBy(d => d.Status == rosterStatus && profileIds.Contains(d.ProfileID) && d.DateStart <= dateEnd && d.DateEnd >= startYear).ToList<Att_Roster>(); var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork); List<Cat_DayOff> listHoliday = repoCat_DayOff.FindBy(hol => hol.DateOff >= startYear && hol.DateOff <= endYear).ToList<Cat_DayOff>(); var repoAtt_Grade = new Att_GradeRepository(unitOfWork); var listGrade = repoAtt_Grade.FindBy(d => d.MonthStart != null && d.MonthStart <= dateEnd && profileIds.Contains((Guid)d.ProfileID)).Select(d => new { d.ProfileID, d.MonthStart, d.GradeAttendanceID }).ToList(); List<Guid?> listGradeID = listGrade.Select(d => d.GradeAttendanceID).ToList(); var repoCat_Grade = new Cat_GradeAttendanceRepository(unitOfWork); List<Cat_GradeAttendance> listGradeCfg = repoCat_Grade.FindBy(d => listGradeID.Contains(d.ID)).ToList<Cat_GradeAttendance>(); List<Att_Roster> lstRosterTypeGroup = new List<Att_Roster>(); List<Att_RosterGroup> lstRosterGroup = new List<Att_RosterGroup>(); GetRosterGroup(profileIds, startYear, dateEnd, out lstRosterTypeGroup, out lstRosterGroup); var repoHre_WorkHistory = new Hre_WorkHistoryRepository(unitOfWork); List<Hre_WorkHistory> listWorkHistory = repoHre_WorkHistory.FindBy(d => profileIds.Contains(d.ProfileID) && d.DateEffective <= dateEnd).ToList<Hre_WorkHistory>(); var orgTypes = new List<Cat_OrgStructureType>(); var repoorgType = new Cat_OrgStructureTypeRepository(unitOfWork); orgTypes = repoorgType.FindBy(s => s.IsDelete == null).ToList<Cat_OrgStructureType>(); DataTable table = GetSchema(); List<string> codeRejects = new List<string>(); codeRejects.Add("SICK"); codeRejects.Add("SU"); codeRejects.Add("SD"); codeRejects.Add("D"); codeRejects.Add("D"); codeRejects.Add("DP"); codeRejects.Add("PSN"); codeRejects.Add("DSP"); codeRejects.Add("M"); List<Guid> leaveDayTypeRejectIDs = leavedayTypes.Where(s => codeRejects.Contains(s.Code)).Select(s => s.ID).ToList(); foreach (var profile in profiles) { var grade = listGrade.Where(d => d.ProfileID == profile.ID && d.MonthStart <= dateEnd).OrderByDescending(d => d.MonthStart).FirstOrDefault(); Cat_GradeAttendance gradeCfg = listGradeCfg.FirstOrDefault(d => grade != null && d.ID == grade.GradeAttendanceID); List<Hre_WorkHistory> listWorkHistoryByProfile = listWorkHistory.Where(d => d.ProfileID == profile.ID).ToList(); List<Att_Roster> listRosterByProfile = listRoster.Where(d => d.ProfileID == profile.ID && d.DateStart <= dateEnd && d.DateEnd >= dateStart).ToList(); var listRosterEntity = listRosterByProfile.Select(d => new Att_RosterEntity { ID = d.ID, ProfileID = d.ProfileID, RosterGroupName = d.RosterGroupName, Type = d.Type, Status = d.Status, DateEnd = d.DateEnd, DateStart = d.DateStart, MonShiftID = d.MonShiftID, TueShiftID = d.TueShiftID, WedShiftID = d.WedShiftID, ThuShiftID = d.ThuShiftID, FriShiftID = d.FriShiftID, SatShiftID = d.SatShiftID, SunShiftID = d.SunShiftID, MonShift2ID = d.MonShiftID, TueShift2ID = d.TueShift2ID, WedShift2ID = d.WedShift2ID, ThuShift2ID = d.ThuShift2ID, FriShift2ID = d.FriShift2ID, SatShift2ID = d.SatShift2ID, SunShift2ID = d.SunShift2ID }).ToList(); var listRosterGroupEntity = lstRosterGroup.Select(d => new Att_RosterGroupEntity { ID = d.ID, DateEnd = d.DateEnd, DateStart = d.DateStart, MonShiftID = d.MonShiftID, TueShiftID = d.TueShiftID, WedShiftID = d.WedShiftID, ThuShiftID = d.ThuShiftID, FriShiftID = d.FriShiftID, SatShiftID = d.SatShiftID, SunShiftID = d.SunShiftID, RosterGroupName = d.RosterGroupName }).ToList(); Dictionary<DateTime, Cat_Shift> listMonthShifts = Att_AttendanceLib.GetDailyShifts(profile.ID, dateStart, dateEnd, listRosterEntity, listRosterGroupEntity, shifts); for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var leavdayProfiles = leaveDays.Where(s => s.DateStart.Date <= date.Date && date.Date <= s.DateEnd.Date && s.ProfileID == profile.ID).ToList(); if (leavdayProfiles.Count > 0) { bool isWorkDay = gradeCfg != null && Att_WorkDayHelper.IsWorkDay(date, gradeCfg, listMonthShifts, listHoliday); var leaveday = leavdayProfiles.FirstOrDefault(s => ledvedayPaidIds.Contains(s.LeaveDayTypeID)); if (!isWorkDay)// neu ngay do ko phai ngay di lam { if (leaveday != null && leaveDayTypeRejectIDs.Contains(leaveday.LeaveDayTypeID) || listHoliday.Exists(s => s.DateOff == date))// neu ngay do la ngay nghi dc xem la ko xem ca or ngay nghi { isWorkDay = true; } } if (isWorkDay) { var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date); DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_LeaveDayEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_LeaveDayEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_LeaveDayEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_LeaveDayEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_LeaveDayEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_LeaveDayEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_LeaveDayEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_LeaveDayEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_LeaveDayEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_LeaveDayEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_LeaveDayEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_LeaveDayEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); row[Att_LeaveDayEntity.FieldNames.Date] = date; row[Att_LeaveDayEntity.FieldNames.DateFrom] = dateStart; row[Att_LeaveDayEntity.FieldNames.DateTo] = dateEnd; row[Att_LeaveDayEntity.FieldNames.DateExport] = DateTime.Now; //row[Att_LeavedayEntity.FieldNames.UserExport] = Session[SessionObjects.UserLogin]; if (leaveday != null && leaveday.LeaveDays > 0) { row["Paid"] = "X"; } if (workDay != null) { row[Att_LeaveDayEntity.FieldNames.Cat_Shift] = shift.ShiftName; row[Att_LeaveDayEntity.FieldNames.udInTime] = workDay.InTime1 != null ? workDay.InTime1.Value.ToString("HH:mm") : string.Empty; row[Att_LeaveDayEntity.FieldNames.udOutTime] = workDay.OutTime1 != null ? workDay.OutTime1.Value.ToString("HH:mm") : string.Empty; } foreach (var leaday in leavedayTypes) { var leaday1 = leavdayProfiles.FirstOrDefault(s => s.LeaveDayTypeID == leaday.ID); if (leaday1 != null && leaday1.LeaveDays > 0) { row[leaday.Code] = "X"; } } if (leaveday != null) { row[Att_LeaveDayEntity.FieldNames.Status] = leaveday.Status; } table.Rows.Add(row); } } } } //EntityService.SubmitChanges(GuidContext, LoginUserID.Value); context.SaveChanges(); return table; } }
/// <summary> /// Lấy Dữ Liệu BC Báo cáo chi tiết giờ vào giờ ra /// </summary> /// <param name="DateFrom">Ngày bắt đầu</param> /// <param name="DateTo">Ngày kết thúc (Cuối ngày 23:59:59)</param> /// <param name="lstProfileIDs">Ds ProfileIDs</param> /// <returns></returns> public DataTable GetReportSumaryInOut(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, Guid[] ShiftIDs, string CodeEmp, bool isIncludeQuitEmp, string userExport, string UserLogin) { List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null && DateFrom <= s.WorkDate && s.WorkDate <= DateTo && (s.InTime1 != null || s.OutTime1 != null)) .Select(s => new { s.ProfileID, s.ShiftID, s.ShiftApprove, s.WorkDate, s.InTime1, s.OutTime1 }).ToList(); //if(lstProfileIDs != null){ // workDays = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).ToList(); //} if (ShiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList(); } var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList(); // var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); //var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); profiles = profiles.Where(s => s.IsDelete == null && profileIds.Contains(s.ID)).ToList(); // .Select(s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.GetAll().ToList(); if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > DateTo).ToList(); } if (!string.IsNullOrEmpty(CodeEmp)) { profiles = profiles.Where(s => s.CodeEmp == CodeEmp).ToList(); } List<Guid> guids = profiles.Select(s => s.ID).ToList(); workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList(); string E_FULLSHIFT = LeaveDayDurationType.E_FULLSHIFT.ToString(); string E_APPROVED = LeaveDayStatus.E_APPROVED.ToString(); var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var leavedayProfiles = repoAtt_LeaveDay.FindBy(s => s.DurationType == E_FULLSHIFT & s.Status == E_APPROVED && DateFrom <= s.DateEnd && s.DateStart <= DateTo).Select(s => new { s.ProfileID, s.DateStart, s.DateEnd }).ToList(); //if (isNotAllowZero) //{ // profileIds = workDays.Select(s => s.ProfileID).ToList(); // profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); //} DataTable table = CreateReportSummaryInOutSchema(); foreach (var profile in profiles) { for (DateTime date = DateFrom.Date; date <= DateTo; date = date.AddDays(1)) { var leaday = leavedayProfiles.FirstOrDefault(s => s.ProfileID == profile.ID && s.DateStart <= date && date <= s.DateEnd); var workDayProfiles = workDays.Where(s => s.WorkDate.Date == date && s.ProfileID == profile.ID).ToList(); if (workDayProfiles.Count > 0 && leaday == null) { DataRow row = table.NewRow(); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); Guid? orgId = profile.OrgStructureID; var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); row[Att_ReportSumaryInOutEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.GroupCode] = orgGroup != null ? orgGroup.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.GroupName] = orgGroup != null ? orgGroup.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.Division] = orgDivision != null ? orgDivision.Code : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportSumaryInOutEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportSumaryInOutEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.JobtitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; var workDay1 = workDayProfiles.FirstOrDefault(); row[Att_ReportSumaryInOutEntity.FieldNames.Date] = date; row[Att_ReportSumaryInOutEntity.FieldNames.DateFrom] = DateFrom.Date; row[Att_ReportSumaryInOutEntity.FieldNames.DateTo] = DateTo; row[Att_ReportSumaryInOutEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportSumaryInOutEntity.FieldNames.UserExport] = userExport; // row[Att_ReportSumaryInOutEntity.FieldNames.UserExport] = Session[SessionObjects.UserLogin]; if (workDay1 != null) { //Guid? ShiftID = workDay1.ShiftApprove ?? workDay1.ShiftID; var shift = shifts.FirstOrDefault(s => s.ID == workDay1.ShiftID); row[Att_ReportSumaryInOutEntity.FieldNames.ShiftName] = shift != null ? shift.ShiftName : string.Empty; row[Att_ReportSumaryInOutEntity.FieldNames.udTimeIn] = workDay1.InTime1 != null ? workDay1.InTime1.Value : DateTime.MinValue; if (workDay1.OutTime1 != null) row[Att_ReportSumaryInOutEntity.FieldNames.udTimeOut] = workDay1.OutTime1 != null ? workDay1.OutTime1.Value : DateTime.MinValue; if (workDay1.ShiftApprove != Guid.Empty) { shift = shifts.FirstOrDefault(s => s.ID == workDay1.ShiftApprove); row[Att_ReportSumaryInOutEntity.FieldNames.ApprovedShift] = shift != null ? shift.ShiftName : string.Empty; } } table.Rows.Add(row); } } } return table; } }
public List<Hre_ReportBirthdayEntity> GetReportBirthday(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs) { List<Hre_ReportBirthdayEntity> lstReportBirthday = new List<Hre_ReportBirthdayEntity>(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoProfile = new Hre_ProfileRepository(unitOfWork); var lstProfile = repoProfile.FindBy(s => s.DateOfBirth != null && s.IsDelete == null && s.DateOfBirth >= DateFrom && s.DateOfBirth <= DateTo && lstProfileIDs.Contains(s.ID)).ToList(); if (lstProfile == null) { return lstReportBirthday; } List<Guid> lstProfileIDsbyBirthday = lstProfile.Select(s => s.ID).ToList(); //var repoProfile = new Hre_ProfileRepository(unitOfWork); var lstAllprofile = repoProfile.FindBy(s => lstProfileIDsbyBirthday.Contains(s.ID)).Select(s => new { s.ID, s.ProfileName, s.CodeEmp, s.OrgStructureID, s.PositionID, s.JobTitleID, s.DateHire, s.DateOfBirth, s.PlaceOfBirth }).ToList(); var repoOrgStructure = new Cat_OrgStructureRepository(unitOfWork); var lstOrgStructure = repoOrgStructure.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.OrgStructureName }).ToList(); var repoPosition = new Cat_PositionRepository(unitOfWork); var lstPosition = repoPosition.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.PositionName }).ToList(); var repoJobtitle = new Cat_JobTitleRepository(unitOfWork); var lstJobtitle = repoJobtitle.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.JobTitleName }).ToList(); foreach (var ProfileBirthday in lstProfile) { Hre_ReportBirthdayEntity ReportBirthday = new Hre_ReportBirthdayEntity(); //var profileByContract = lstAllprofile.Where(s => contract.ProfileID == s.ID).FirstOrDefault(); //if (profileByContract == null) // continue; var orgByProfile = lstOrgStructure.Where(s => ProfileBirthday.OrgStructureID == s.ID).FirstOrDefault(); var positionByProfile = lstPosition.Where(s => ProfileBirthday.PositionID == s.ID).FirstOrDefault(); var jobtitleByProfile = lstJobtitle.Where(s => ProfileBirthday.JobTitleID == s.ID).FirstOrDefault(); ReportBirthday.CodeEmp = ProfileBirthday.CodeEmp; ReportBirthday.ProfileName = ProfileBirthday.ProfileName; ReportBirthday.ProfileID = ProfileBirthday.ID; ReportBirthday.OrgStructureName = orgByProfile != null ? orgByProfile.OrgStructureName : ""; ReportBirthday.JobTitleName = jobtitleByProfile != null ? jobtitleByProfile.JobTitleName : ""; ReportBirthday.PositionName = positionByProfile != null ? positionByProfile.PositionName : ""; ReportBirthday.DateTo = ProfileBirthday.DateTo; ReportBirthday.DateFrom = ProfileBirthday.DateFrom; ReportBirthday.DateHire = ProfileBirthday.DateHire; ReportBirthday.DateOfBirth = ProfileBirthday.DateOfBirth; ReportBirthday.PlaceOfBirth = ProfileBirthday.PlaceOfBirth; lstReportBirthday.Add(ReportBirthday); } return lstReportBirthday; } }
public DataTable GetReportProfileAllowLimitOvertime(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, string orgStructureID, string userExport, bool isCreateTemplate, string UserLogin) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DataTable table = CreateReportProfileAllowLimitOvertime(); if (isCreateTemplate) { return table; } if (profiles.Count == 0) return table; BaseService baservice = new BaseService(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); string status = string.Empty; List<object> paraAtt_AllowLimitOvertime = new List<object>(); paraAtt_AllowLimitOvertime.AddRange(new object[3]); paraAtt_AllowLimitOvertime[0] = (object)orgStructureID; paraAtt_AllowLimitOvertime[1] = DateFrom; paraAtt_AllowLimitOvertime[2] = DateTo; // var lstAllowLimit = new List<Att_AllowLimitOvertime>().Select(m => new { m.ProfileID, m.DateStart, m.DateEnd }); var lstAllowLimit = baservice.GetData<Att_AllowLimitOvertimeEntity>(paraAtt_AllowLimitOvertime, ConstantSql.hrm_att_getdata_AllowLimitOvertime, UserLogin, ref status).ToList().Translate<Att_AllowLimitOvertime>(); if (lstAllowLimit.Count == 0) return table; List<Guid> lstProfileIds = lstAllowLimit.Select(m => m.ProfileID ?? Guid.Empty).ToList(); profiles = profiles.Where(m => lstProfileIds.Contains(m.ID)).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); foreach (var AllowLimit in lstAllowLimit) { DataRow row = table.NewRow(); var profile = profiles.Where(m => m.ID == AllowLimit.ProfileID).FirstOrDefault(); if (profile != null) { row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.CodeEmp] = profile.CodeEmp; Guid? orgId = profile.OrgStructureID; var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DivisionCode] = orgDivision != null ? orgDivision.Code : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.Position] = positon != null ? positon.PositionName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.JobTitle] = jobtitle != null ? jobtitle.JobTitleName : string.Empty; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateFrom] = AllowLimit.DateStart; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateTo] = AllowLimit.DateEnd; row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateExport] = DateTime.Now; } table.Rows.Add(row); } return table; } }
public Eva_BonusSalaryEntity CalculateBonusSalary(Eva_BonusSalaryEntity model,string userLogin) { /* * Goal(Tính lương thưởng) * Steps : * Step1 : Lay tat ca chuc Danh trong phong ban,lay tat ca nv có chức danh * Step2 : Từ Chuc Danh , Tieu Chi , Loai Thuong,thang (lay từ mức, đến mức) * Step2 : Tự động insert xuống table Eva_PerformanceForDetail * Step3 : Tự động insert xuống table Eva_PerformanceEva &Eva_PerformanceEvaDetail * - Tự Đánh Giá : orderNo =0 (Ngoai ra orderNo >0) */ var status = string.Empty; using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork) (new UnitOfWork(context)); var repoJobTitle = new Cat_JobTitleRepository(unitOfWork); var repoOrg = new Cat_OrgStructureRepository(unitOfWork); var repoSaleBonus = new Eva_SaleBonusRepository(unitOfWork); var repoBonusSalary = new Eva_BonusSalaryRepository(unitOfWork); var repoSaleEvaluation = new Eva_SaleEvaluationRepository(unitOfWork); var repoProfile = new Hre_ProfileRepository(unitOfWork); //var orgId = Guid.Parse(model.OrgStructureID); //var a = repoOrg.FindBy(p => p.ID == orgId).Select(p=>p.OrderNumber).FirstOrDefault(); //if (a != null) //{ // model.OrgStructureID = a.ToString(); //} List<object> listObj = new List<object>(); listObj.Add(model.OrgStructureID); listObj.Add(string.Empty); listObj.Add(string.Empty); var profilebyOrgs = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status); //var profilebyOrgs = repoProfile.FindBy(x => x.OrgStructureID == Guid.Parse(model.OrgStructureID)).ToList(); var profiles = profilebyOrgs.Where(p => p.JobTitleID != null).ToList(); var jobTitleIds = profilebyOrgs.Where(p => p.JobTitleID != null).Select(p=>p.JobTitleID).Distinct().ToList(); var jobTitle = repoJobTitle.FindBy(p => jobTitleIds.Contains(p.ID)).ToList(); //lay tu muc den muc var month = DateTime.Now; // lấy danh sách thưởng doanh số dựa vào loại doanh số và chức danh và loại var lstsalebonus = repoSaleBonus.FindBy(x => jobTitleIds.Contains(x.JobTittleID.Value) && x.Type == model.Type && x.SalesTypeID==model.SalesTypeID&&x.IsDelete==null).ToList(); // lấy danh sách doanh số dựa vào loại doanh số và chức danh và loại if (model.Month != null) { month = model.Month.Value; } List<Eva_BonusSalary> lstEva_BonusSalaryEntity = new List<Eva_BonusSalary>(); if (profiles == null || profiles.Count == 0) { return null; } var lstBonusSalaryEdit = repoBonusSalary.FindBy(x => x.SalesTypeID == model.SalesTypeID && x.Type == model.Type && x.IsDelete == null).ToList(); var lstSaleEvaluation = repoSaleEvaluation.FindBy(x=> x.SalesTypeID == model.SalesTypeID && x.IsDelete == null).ToList(); if (model.Type == EvaBonusType.E_Month.ToString()) { foreach (var item in profiles) { var lstBonusSalaryEdit2 = lstBonusSalaryEdit.Where(x => x.ProfileID == item.ID&& x.Month.Value.Month == model.Month.Value.Month).ToList(); var objSaleEvaluation = lstSaleEvaluation.Where(x => x.ProfileID == item.ID && x.Year.Value.Month == model.Month.Value.Month).FirstOrDefault(); if (lstBonusSalaryEdit2 != null && lstBonusSalaryEdit2.Count != 0) { var objBonusSalaryEdit = lstBonusSalaryEdit2.FirstOrDefault(); if (objSaleEvaluation != null) { var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= objSaleEvaluation.ResultPercent && x.ToPercent >= objSaleEvaluation.ResultPercent && x.JobTittleID == item.JobTitleID).ToList(); if (SaleBonus != null && SaleBonus.Count != 0) { objBonusSalaryEdit.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString()); repoBonusSalary.SaveChanges(); } } } else { var objBonusSalary = new Eva_BonusSalary(); // objBonusSalary = model; objBonusSalary.SalesTypeID = model.SalesTypeID; objBonusSalary.ProfileID = item.ID; objBonusSalary.Type = model.Type; objBonusSalary.Month = month; objBonusSalary.Bonus = 0; if (objSaleEvaluation != null) { var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= objSaleEvaluation.ResultPercent && x.ToPercent >= objSaleEvaluation.ResultPercent && x.JobTittleID == item.JobTitleID).ToList(); if (SaleBonus != null && SaleBonus.Count != 0) { objBonusSalary.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString()); } } lstEva_BonusSalaryEntity.Add(objBonusSalary); } } repoBonusSalary.Add(lstEva_BonusSalaryEntity); repoBonusSalary.SaveChanges(); } if (model.Type == EvaBonusType.E_Quarter.ToString()) { //int quarter = //((month.Month-1)/ 3) + 1;Quarter int quarter = (int)Enum.Parse(typeof(Quarter), model.Quarter); DateTime firstDayOfQuarter = new DateTime(month.Year, (quarter - 1) * 3 + 1, 1); DateTime lastDayOfQuarter = firstDayOfQuarter.AddMonths(3).AddDays(-1); foreach (var item in profiles) { var objSaleEvaluation = lstSaleEvaluation.Where(x => x.ProfileID == item.ID && x.Year <= lastDayOfQuarter && x.Year >= firstDayOfQuarter).ToList(); // var lstBonusSalaryEdit = repoBonusSalary.FindBy(x => x.ProfileID == item.ID && x.SalesTypeID == model.SalesTypeID && x.Type == model.Type && x.Month.Value.Month == model.Month.Value.Month).ToList(); var lsttBonusSalaryEdit2 = lstBonusSalaryEdit.Where(x => x.ProfileID == item.ID && x.Month <= lastDayOfQuarter && x.Month >= firstDayOfQuarter).ToList(); if (lsttBonusSalaryEdit2 != null && lsttBonusSalaryEdit2.Count != 0) { var objBonusSalaryEdit = lsttBonusSalaryEdit2.FirstOrDefault(); if (objSaleEvaluation != null && objSaleEvaluation.Count != 0) { var AveragePercent = objSaleEvaluation.Sum(x => x.ResultPercent) / 3; var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= AveragePercent && x.ToPercent >= AveragePercent && x.JobTittleID == item.JobTitleID).ToList(); if (SaleBonus != null && SaleBonus.Count != 0) { objBonusSalaryEdit.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString()); repoBonusSalary.SaveChanges(); } } } else { var objBonusSalary = new Eva_BonusSalary(); //objBonusSalary = model; objBonusSalary.SalesTypeID = model.SalesTypeID; // objBonusSalary.Quarter = quarter.ToString(); objBonusSalary.ProfileID = item.ID; objBonusSalary.Type = model.Type; objBonusSalary.Month = month; objBonusSalary.Bonus = 0; if (objSaleEvaluation != null && objSaleEvaluation.Count != 0) { var AveragePercent = objSaleEvaluation.Sum(x => x.ResultPercent) / 3; var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= AveragePercent && x.ToPercent >= AveragePercent && x.JobTittleID == item.JobTitleID).ToList(); if (SaleBonus != null && SaleBonus.Count != 0) { objBonusSalary.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString()); } } lstEva_BonusSalaryEntity.Add(objBonusSalary); } } repoBonusSalary.Add(lstEva_BonusSalaryEntity); repoBonusSalary.SaveChanges(); } } return model; }
/// <summary> /// Lấy Dữ Liệu BC Báo cáo tổng số lần đi muộn về sớm trong tháng /// </summary> /// <param name="DateFrom">Ngày bắt đầu</param> /// <param name="DateTo">Ngày kết thúc (Cuối ngày 23:59:59)</param> /// <param name="lstProfileIDs">Ds ProfileIDs</param> /// <returns></returns> public List<Att_ReportSumaryLateInOutEntity> GetReportSumaryLateInOut(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, Guid[] ShiftIDs, bool isIncludeQuitEmp, bool noDisplay0Data, string userExport) { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); List<Att_ReportSumaryLateInOutEntity> lstReportSumaryLateInOut = new List<Att_ReportSumaryLateInOutEntity>(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.GetAll().ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > DateTo).ToList(); } var workDays = repoAtt_Workday.FindBy(s => DateFrom <= s.WorkDate && s.WorkDate <= DateTo && s.LateEarlyDuration > 0 && s.IsDelete == null) .Select(m => new { m.ID, m.ProfileID, m.LateDuration1, m.EarlyDuration1, m.LateEarlyDuration, m.ShiftID }).ToList(); if (ShiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList(); } //var lstWorkday = workDays.Select(m => new { m.ID, m.ProfileID, m.LateDuration1, m.EarlyDuration1, m.LateEarlyDuration }).ToList(); if (noDisplay0Data) { var profileIDs = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList(); } foreach (var profile in profiles) { Att_ReportSumaryLateInOutEntity rptSumaryLateInOut = new Att_ReportSumaryLateInOutEntity(); Guid? orgId = profile.OrgStructureID; var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); rptSumaryLateInOut.GroupCode = orgBranch != null ? orgBranch.Code : string.Empty; rptSumaryLateInOut.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty; rptSumaryLateInOut.Division = orgTeam != null ? orgTeam.Code : string.Empty; ; rptSumaryLateInOut.SectionCode = orgSection != null ? orgSection.Code : string.Empty; rptSumaryLateInOut.CodeEmp = profile.CodeEmp; rptSumaryLateInOut.ProfileName = profile.ProfileName; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); rptSumaryLateInOut.PositionCode = positon != null ? positon.Code : string.Empty; // var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); rptSumaryLateInOut.DateFrom = DateFrom; rptSumaryLateInOut.DateTo = DateTo; rptSumaryLateInOut.DateExport = DateTime.Today; rptSumaryLateInOut.UserExport = userExport; var lstWorkday_ByProfile = workDays.Where(m => m.ProfileID == profile.ID).ToList(); if (lstWorkday_ByProfile != null && lstWorkday_ByProfile.Count > 0) { var sumEarly = lstWorkday_ByProfile.Sum(s => s.EarlyDuration1); var SumLateMinute = lstWorkday_ByProfile.Sum(s => s.LateDuration1); var CountLate = lstWorkday_ByProfile.Count(m => m.LateDuration1 > 0); var CountEarly = lstWorkday_ByProfile.Count(m => m.EarlyDuration1 > 0); rptSumaryLateInOut.EarlyMinutes = sumEarly > 0 ? sumEarly : null; rptSumaryLateInOut.LateMinutes = SumLateMinute > 0 ? SumLateMinute : null; rptSumaryLateInOut.NumTimeEarly = CountEarly > 0 ? (int?)CountEarly : null; rptSumaryLateInOut.NumTimeLate = CountLate > 0 ? (int?)CountLate : null; } lstReportSumaryLateInOut.Add(rptSumaryLateInOut); } return lstReportSumaryLateInOut; } }