/// <summary> /// Lấy tất cả các bản ghi có thuộc tính IsDelete là null /// Gọi GetAllCatLeaveDayType() trong Cat_LeaveDayTypeRepository /// </summary> /// <returns></returns> public IQueryable <Cat_LeaveDayType> GetCatLeaveDayType() { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); return(repo.GetAllCatLeaveDayType().Where(i => i.IsDelete == null)); } }
/// <summary> /// Lấy tất cả các bản ghi có thuộc tính IsDelete là null /// Gọi GetAllCatLeaveDayType() trong Cat_LeaveDayTypeRepository /// </summary> /// <returns></returns> public IQueryable<Cat_LeaveDayType> GetCatLeaveDayType() { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); return repo.GetAllCatLeaveDayType().Where(i => i.IsDelete == null); } }
/// <summary> /// Tìm kiếm bản ghi có theo tham số id /// </summary> /// <param name="id"></param> /// <returns></returns> public Cat_LeaveDayType GetByIdCatLeaveDayType(int id) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType(); catLeaveDayType = repo.GetById(id); if (catLeaveDayType.IsDelete == true) catLeaveDayType = null; return catLeaveDayType; } }
/// <summary> /// Tìm kiếm bản ghi có theo tham số id /// </summary> /// <param name="id"></param> /// <returns></returns> public Cat_LeaveDayType GetByIdCatLeaveDayType(int id) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType(); catLeaveDayType = repo.GetById(id); if (catLeaveDayType.IsDelete == true) { catLeaveDayType = null; } return(catLeaveDayType); } }
/// <summary> /// Update bản ghi /// </summary> /// <param name="catLeaveDayType"></param> /// <returns></returns> public bool UpdateCatLeaveDayType(Cat_LeaveDayType catLeaveDayType) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); try { repo.Edit(catLeaveDayType); repo.SaveChanges(); return(true); } catch { return(false); } } }
/// <summary> /// Thêm bản ghi mới /// </summary> /// <param name="catLeaveDayType"></param> /// <returns></returns> public bool AddCatLeaveDayType(Cat_LeaveDayType catLeaveDayType) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); try { repo.Add(catLeaveDayType); repo.SaveChanges(); return true; } catch { return false; } } }
/// <summary> /// Update thuộc tính IsDelete là true /// </summary> /// <param name="catLeaveDayTypeId"></param> /// <returns></returns> public bool DeleteCatLeaveDayType(int catLeaveDayTypeId) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType(); catLeaveDayType = repo.GetById(catLeaveDayTypeId); try { repo.Remove(catLeaveDayType); repo.SaveChanges(); return(true); } catch { return(false); } } }
/// <summary> /// [Tam.Le] - 2014/08/08 /// Tạo cấu trúc bảng cho hàm Load Data của att_leaveday /// </summary> /// <param name="request"></param> /// <param name="otModel"></param> /// <returns></returns> DataTable GetSchema() { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DataTable tb = new DataTable(); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeEmp); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.ProfileName); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeBranch); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeOrg); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeTeam); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeSection); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeJobtitle); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodePosition); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.BranchName); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.OrgName); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.TeamName); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.SectionName); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Paid); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Date, typeof(DateTime)); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateFrom, typeof(DateTime)); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateTo, typeof(DateTime)); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.udInTime); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.udOutTime); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Cat_Shift); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Status); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateExport, typeof(DateTime)); tb.Columns.Add(Att_LeaveDayEntity.FieldNames.UserExport); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var codes = repoCat_LeaveDayType.FindBy(s => s.Code != null).Select(s => s.Code).Distinct().ToList<string>(); foreach (string code in codes) { if (!tb.Columns.Contains(code)) { tb.Columns.Add(code); } } return tb; } }
/// <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; } }
public DataTable ReportMealTimeDetail(List<Guid?> CarteringIDs, List<Guid?> CanteenIDS, List<Guid?> LineIDS, DateTime dateStart, DateTime dateEndSearch, List<Guid?> orgIDs) { using (var context = new VnrHrmDataContext()) { DateTime dateEnd = dateEndSearch.AddDays(1).AddMilliseconds(-1); DataTable datatable = CreateReportMealTimeDetailSchema(dateStart, dateEnd); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoMealRecord = new Can_MealRecordRepository(unitOfWork); var repoLine = new Can_LineRepository(unitOfWork); var lineHDTJobIDs = repoLine.FindBy(s => s.HDTJ != null).Select(s => s.ID).ToList(); var mealRecords = repoMealRecord.FindBy(s => s.LineID != null && !lineHDTJobIDs.Contains(s.LineID.Value) && s.ProfileID != null && dateStart <= s.WorkDay && s.WorkDay <= dateEnd).ToList(); if (mealRecords.Count < 1) { return datatable; } var profileIds = mealRecords.Select(s => s.ProfileID.Value).Distinct().ToList(); var repoWorkDay = new Att_WorkDayRepository(unitOfWork); var workDayProfies = repoWorkDay.FindBy(s => s.ProfileID != null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && profileIds.Contains(s.ProfileID)).ToList(); var repoProfile = new Hre_ProfileRepository(unitOfWork); var profiles = repoProfile.FindBy(s => profileIds.Contains(s.ID)).ToList(); var repoLeaveday = new Att_LeavedayRepository(unitOfWork); var leavedays = repoLeaveday.FindBy(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && profileIds.Contains(s.ProfileID)). Select(s => new { s.ProfileID, s.DateStart, s.DateEnd, s.LeaveDayTypeID }).ToList(); var repoleaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var leaveDayTypes = repoleaveDayType.GetAll().Select(s => new { s.ID, s.LeaveDayTypeName }).ToList(); List<Cat_OrgStructure> orgs = new List<Cat_OrgStructure>(); var repoOrg = new Cat_OrgStructureRepository(unitOfWork); orgs = repoOrg.FindBy(s => s.Code != null).ToList(); if (orgIDs != null && orgIDs.Count > 0) { profiles = profiles.Where(s => s.OrgStructureID != null && orgIDs.Contains(s.OrgStructureID.Value)).ToList(); } if (CanteenIDS != null && CanteenIDS[0] != null) { mealRecords = mealRecords.Where(s => s.CanteenID != null && CanteenIDS.Contains(s.CanteenID.Value)).ToList(); } if (CarteringIDs != null && CarteringIDs[0] != null) { mealRecords = mealRecords.Where(s => s.CateringID != null && CarteringIDs.Contains(s.CateringID.Value)).ToList(); } if (LineIDS != null && LineIDS[0] != null) { mealRecords = mealRecords.Where(s => s.LineID != null && LineIDS.Contains(s.LineID.Value)).ToList(); } if (mealRecords.Count < 1) { return datatable; } profileIds = mealRecords.Select(s => s.ProfileID.Value).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); var orgTypes = new List<Cat_OrgStructureType>(); var repoorgType = new Cat_OrgStructureTypeRepository(unitOfWork); orgTypes = repoorgType.FindBy(s => s.IsDelete == null).ToList<Cat_OrgStructureType>(); var repoCanteen = new Can_CanteenRepository(unitOfWork); var repoCatering = new Can_CateringRepository(unitOfWork); var canteens = repoCanteen.GetAll().ToList(); var caterings = repoCatering.GetAll().ToList(); var lines = repoLine.GetAll().ToList(); #region Code Cũ //foreach (var profile in profiles) //{ // var workdayProfileDates = workDayProfies.Where(s => s.ProfileID == profile.Id).Select(s => s.WorkDate.Date).ToList(); // for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) // { // var record = mealRecords.FirstOrDefault(s => s.ProfileID == profile.Id && date == s.TimeLog.Date && !workdayProfileDates.Contains(date.Date)); // if (record != null) // { // DataRow row = datatable.NewRow(); // int? 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[Can_ReportMealTimeDetailEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; // row[Can_ReportMealTimeDetailEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; // row[Can_ReportMealTimeDetailEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; // row[Can_ReportMealTimeDetailEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; // row[Can_ReportMealTimeDetailEntity.FieldNames.CodeEmp] = profile.CodeEmp; // row[Can_ReportMealTimeDetailEntity.FieldNames.ProfileName] = profile.ProfileName; // var line = repoLine.FindBy(m => record.LineID == m.Id).FirstOrDefault(); // var canteen = repoCanteen.FindBy(m => record.CanteenID == m.Id).FirstOrDefault(); // var catering = repoCatering.FindBy(m => record.CateringID == m.Id).FirstOrDefault(); // row[Can_ReportMealTimeDetailEntity.FieldNames.CateringName] = catering != null ? catering.CateringName : null; // row[Can_ReportMealTimeDetailEntity.FieldNames.CanteenName] = canteen != null ? canteen.CanteenName : null; // row[Can_ReportMealTimeDetailEntity.FieldNames.LineName] = line != null ? line.LineName : null; // row[Can_ReportMealTimeDetailEntity.FieldNames.DateFrom] = dateStart; // row[Can_ReportMealTimeDetailEntity.FieldNames.DateTo] = dateEnd; // row[Can_ReportMealTimeDetailEntity.FieldNames.DatePrint] = DateTime.Now; // if (record.TimeLog != null) // { // row["Data" + date.Day] = record.TimeLog.ToString(ConstantFormat.HRM_Format_Grid_ShortTime); // } // datatable.Rows.Add(row); // } // } //} #endregion foreach (var profile in profiles) { var workdayProfileDates = workDayProfies.Where(s => s.ProfileID == profile.ID && (s.FirstInTime != null || s.LastOutTime != null)).Select(s => s.WorkDate.Date).ToList(); var mealRecordProfiles = mealRecords.Where(s => s.ProfileID == profile.ID).ToList(); for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var leaday = leavedays.FirstOrDefault(s => s.DateStart != null && s.DateEnd != null && s.ProfileID == profile.ID && s.DateStart.Date <= date.Date && date.Date <= s.DateEnd.Date); var record = mealRecordProfiles.FirstOrDefault(s => date.Date == s.WorkDay.Value.Date && (!workdayProfileDates.Contains(s.WorkDay.Value.Date) || leaday != null)); if (record != null) { DataRow row = datatable.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes); row[Can_ReportMealTimeDetailEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Can_ReportMealTimeDetailEntity.FieldNames.ProfileName] = profile.ProfileName; row[Can_ReportMealTimeDetailEntity.FieldNames.Date] = record.WorkDay; row[Can_ReportMealTimeDetailEntity.FieldNames.DateFrom] = dateStart; row[Can_ReportMealTimeDetailEntity.FieldNames.DateTo] = dateEnd; var cate = caterings.FirstOrDefault(s => s.ID == record.CateringID); var cateen = canteens.FirstOrDefault(s => s.ID == record.CanteenID); var line = lines.FirstOrDefault(s => s.ID == record.LineID); row[Can_ReportMealTimeDetailEntity.FieldNames.CateringName] = cate != null ? cate.CateringName : string.Empty; ; row[Can_ReportMealTimeDetailEntity.FieldNames.CanteenName] = cateen != null ? cateen.CanteenName : string.Empty; ; row[Can_ReportMealTimeDetailEntity.FieldNames.LineName] = line != null ? line.LineName : string.Empty; if (record.TimeLog != null) { row["Data" + record.WorkDay.Value.Day] = record.TimeLog == null ? null : (object)record.TimeLog.Value.ToString(ConstantFormat.HRM_Format_Grid_ShortTime) ?? DBNull.Value; } row[Can_ReportMealTimeDetailEntity.FieldNames.DatePrint] = DateTime.Now; if (leaday != null) { var leadayType = leaveDayTypes.FirstOrDefault(s => s.ID == leaday.LeaveDayTypeID); row[Can_ReportMealTimeDetailEntity.FieldNames.LeaveDayTypeName] = leadayType != null ? leadayType.LeaveDayTypeName : string.Empty; } datatable.Rows.Add(row); } } } return datatable; } }
/// <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 }
/// <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; } }
public DataTable GetReportDetailedMonthlyStay(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> profiles, string orgStructureID, bool isIncludeQuitEmp, Guid[] leaveDayTypeIDs, string codeEmp, bool isNotAllowZero, string userExport, bool IsCreateTemplate, string UserLogin) { using (var context = new VnrHrmDataContext()) { var baseservice = new BaseService(); string key = OverTimeStatus.E_APPROVED.ToString(); DataTable table = CreateReportDetailedMonthlyStaySchema(leaveDayTypeIDs, dateStart, dateEnd); if (IsCreateTemplate) { return table.ConfigTable(); } var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork); var repoAtt_Roster = new Att_RosterRepository(unitOfWork); var repoCat_LeaveDayTypeRepository = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork); var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoCat_GradeAtt = new Cat_GradeAttendanceRepository(unitOfWork); var repoHre_WorkHistory = new Hre_WorkHistoryRepository(unitOfWork); var repoAtt_Grade = new CustomBaseRepository<Att_Grade>(unitOfWork); List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList(); List<Att_LeaveDayEntity> leaveDays = new List<Att_LeaveDayEntity>(); List<Cat_LeaveDayType> leavedaytypes = new List<Cat_LeaveDayType>(); string status = string.Empty; List<object> para = new List<object>(); para.AddRange(new object[3]); para[0] = (object)orgStructureID; para[1] = dateStart; para[2] = dateEnd; if (lstProfileIDs != null && lstProfileIDs.Count > 0) { leaveDays = baseservice.GetData<Att_LeaveDayEntity>(para, ConstantSql.hrm_att_getdata_LeaveDay, UserLogin, ref status).Where(s => s.Status == key).ToList(); } else { return table.ConfigTable(); } //[Tho.Bui] Get LeavedayR=type if (leaveDays != null && leaveDays.Count > 0) { List<Guid> lstLDTinLD = leaveDays.Select(s => s.LeaveDayTypeID).Distinct().ToList(); leavedaytypes = repoCat_LeaveDayTypeRepository.FindBy(s => s.IsDelete == null && lstLDTinLD.Contains(s.ID)).ToList(); List<Guid> leavedayguid = leaveDays.Select(s => s.LeaveDayTypeID).ToList(); leavedaytypes = leavedaytypes.Where(s => leavedayguid.Contains(s.ID)).ToList(); } var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList(); var rosterStatus = RosterStatus.E_APPROVED.ToString(); var _rosters = new List<Att_Roster>(); foreach (var tempProfileIds in lstProfileIDs.Chunk(1000)) { _rosters.AddRange(unitOfWork.CreateQueryable<Att_Roster>(Guid.Empty, d => tempProfileIds.Contains(d.ProfileID) && d.Status == rosterStatus && d.DateStart <= dateEnd && d.DateEnd >= dateStart).ToList()); } var rosters = new List<Att_RosterEntity>(); if (_rosters.Count > 0) rosters = _rosters.Translate<Att_RosterEntity>(); 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 leavdayPadidIDs = leavedayTypes.Where(s => s.PaidRate > 0).Select(s => s.ID).ToList(); List<Guid> guids = profiles.Select(s => s.ID).ToList(); leaveDays = leaveDays.Where(s => guids.Contains(s.ProfileID)).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 (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } //var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList(); var shifts = unitOfWork.CreateQueryable<Cat_Shift>(Guid.Empty).ToList().Translate<Cat_ShiftEntity>(); List<string> codeRejects = new List<string>(); codeRejects.Add("SICK"); codeRejects.Add("SU"); codeRejects.Add("SD"); 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(); if (isNotAllowZero) { var profileIDs = leaveDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList(); } #region sai xong xóa string CodeEmpTest = string.Empty; if (CodeEmpTest != string.Empty) { char[] ext = new char[] { ';', ',' }; List<string> lstCodeEmpTest = CodeEmpTest.Split(ext, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); profiles = profiles.Where(m => lstCodeEmpTest.Contains(m.CodeEmp)).ToList(); } #endregion var leveDayIDs = leaveDays.Select(s => s.LeaveDayTypeID).ToList(); leavedayTypes = leavedayTypes.Where(s => leveDayIDs.Contains(s.ID)).ToList(); var holidayDates = repoCat_DayOff.FindBy(hol => hol.DateOff >= dateStart && hol.DateOff <= dateEnd).Select(s => s.DateOff).ToList(); holidayDates = holidayDates.Select(s => s.Date).ToList(); var lstRosterGroup = unitOfWork.CreateQueryable<Att_RosterGroup>(Guid.Empty, s => s.DateStart != null && s.DateStart >= dateStart && s.DateEnd != null && s.DateEnd <= dateEnd).ToList().Translate<Att_RosterGroupEntity>(); foreach (var profile in profiles) { var rosterProfiles = rosters.Where(s => s.ProfileID == profile.ID).ToList(); List<DateTime> workDayDates = new List<DateTime>(); foreach (var roster in rosterProfiles) { for (DateTime date = roster.DateStart; date <= roster.DateEnd; date = date.AddDays(1)) { workDayDates.Add(date.Date); } } workDayDates = workDayDates.Where(s => !holidayDates.Contains(s.Date)).ToList(); DataRow row = table.NewRow(); var leaveDay = leaveDays.FirstOrDefault(s => s.ProfileID == profile.ID); var LeaveDayType = leavedaytypes.FirstOrDefault(s => s.ID != Guid.Empty && leaveDay != null && s.ID == leaveDay.LeaveDayTypeID); row[Att_ReportDetailedMonthlyStayEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.CodeEmp] = profile.CodeEmp; if (profile.E_COMPANY != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_COMPANY] = profile.E_COMPANY; if (profile.E_BRANCH != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_BRANCH] = profile.E_BRANCH; if (profile.E_UNIT != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_UNIT] = profile.E_UNIT; if (profile.E_DIVISION != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_DIVISION] = profile.E_DIVISION; if (profile.E_DEPARTMENT != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT; if (profile.E_TEAM != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_TEAM] = profile.E_TEAM; if (profile.E_SECTION != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_SECTION] = profile.E_SECTION; if (profile.JobTitleName != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.JobTitleName] = profile.JobTitleName; if (profile.PositionName != null) row[Att_ReportDetailedMonthlyStayEntity.FieldNames.PositionName] = profile.PositionName; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateTo] = dateEnd; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.UserExport] = userExport; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateExport] = DateTime.Today; var leadayProfiles = leaveDays.Where(s => s.ProfileID == profile.ID).ToList(); var ledayeTyepIDs = leadayProfiles.Select(s => s.LeaveDayTypeID).ToList(); double sumPaid = 0; foreach (var leadayType in leavedayTypes.Where(s => ledayeTyepIDs.Contains(s.ID)).ToList()) { double sum = 0; var leavdayProfiles = leadayProfiles.Where(s => s.LeaveDayTypeID == leadayType.ID).ToList(); foreach (var leavdayProfile in leavdayProfiles) { var DateEnd = leavdayProfile.DateEnd; if (DateEnd > dateEnd) { DateEnd = dateEnd; } for (DateTime date = leavdayProfile.DateStart; date <= DateEnd; date = date.AddDays(1)) { bool isWorkDay = workDayDates.Contains(date); if (!isWorkDay)// neu ngay do ko phai ngay di lam { if (leaveDayTypeRejectIDs.Contains(leavdayProfile.LeaveDayTypeID))// neu ngay do la ngay nghi dc xem la ko xem ca or ngay nghi { isWorkDay = true; } } if (isWorkDay) { if (leavdayProfile.Duration > 0) { //sum++; sum += leavdayProfile.Duration; } if (leavdayPadidIDs.Contains(leavdayProfile.LeaveDayTypeID)) { sumPaid += leadayType.PaidRate; //sumPaid++; } } } } row[leadayType.Code] = sum > 0 ? (object)sum : DBNull.Value; row[Att_ReportDetailedMonthlyStayEntity.FieldNames.Paid] = sumPaid > 0 ? (object)sumPaid : DBNull.Value; } #region tinh so gio nghi tung ngay cua nhan vien var leavdayByProfile = leadayProfiles.Where(s => s.ProfileID == profile.ID).ToList(); Dictionary<DateTime, List<Guid?>> tempShiftByDate = new Dictionary<DateTime, List<Guid?>>(); tempShiftByDate = Att_AttendanceLib.GetDailyShifts(dateStart, dateEnd, profile.ID, rosterProfiles, lstRosterGroup); foreach (var leavdayProfile in leavdayByProfile) { for (DateTime date = dateStart; date < dateEnd; date = date.AddDays(1)) { bool isWorkDay = workDayDates.Contains(date); if (isWorkDay) { //lay ca lam viec cua nhan vien if (tempShiftByDate != null) { var lstShiftID = tempShiftByDate.Where(s => s.Key == date).SelectMany(s => s.Value).ToList(); if (lstShiftID.Count > 0) { var lstShiftProfileByDate = shifts.Where(s => s.WorkHours != null && lstShiftID.Contains(s.ID)).ToList(); double? _tempWorkHour = 0; _tempWorkHour = lstShiftProfileByDate.Sum(s => s.WorkHours); if (_tempWorkHour > 0) { row["Day" + date.Day.ToString()] = _tempWorkHour; } } } } } } #endregion table.Rows.Add(row); } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); var confighidden = new Dictionary<string, object>(); confighidden.Add("hidden", true); //var configdate = new Dictionary<string, object>(); //configdate.Add("format", "{0:dd/MM/yyyy}"); config.Add("width", 100); //config.Add("locked", true); if (!configs.ContainsKey("CodeEmp")) configs.Add("CodeEmp", config); if (!configs.ContainsKey("DepartmentCode")) configs.Add("DepartmentCode", config); if (!configs.ContainsKey("PositionName")) configs.Add("PositionName", config); if (!configs.ContainsKey("JobTitleName")) configs.Add("JobTitleName", config); if (!configs.ContainsKey("Paid")) configs.Add("Paid", config); if (!configs.ContainsKey("BranchCode")) configs.Add("BranchCode", config); if (!configs.ContainsKey("TeamCode")) configs.Add("TeamCode", config); if (!configs.ContainsKey("SectionCode")) configs.Add("SectionCode", config); if (!configs.ContainsKey("OrgName")) configs.Add("OrgName", config); if (!configs.ContainsKey("TeamName")) configs.Add("TeamName", config); if (!configs.ContainsKey("SectionName")) configs.Add("SectionName", config); if (!configs.ContainsKey("BranchName")) configs.Add("BranchName", config); if (!configs.ContainsKey("DateFrom")) configs.Add("DateFrom", confighidden); if (!configs.ContainsKey("DateTo")) configs.Add("DateTo", confighidden); if (!configs.ContainsKey("UserExport")) configs.Add("UserExport", confighidden); if (!configs.ContainsKey("DateExport")) configs.Add("DateExport", confighidden); foreach (var leavdayType in leavedayTypes) { if (!configs.ContainsKey(leavdayType.Code)) configs.Add(leavdayType.Code, config); } 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 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; } }
public void SetStatusLeaveOnWorkday(List<Att_WorkdayEntity> lstWorkday) { if (lstWorkday == null || lstWorkday.Count == 0) return; List<Guid> lstProfileId = lstWorkday.Select(m => m.ProfileID).ToList(); DateTime DateMin = lstWorkday.Min(m => m.WorkDate); DateTime DateMax = lstWorkday.Max(m => m.WorkDate); DateMax = DateMax.Date.AddDays(1).AddMinutes(-1); string E_CANCEL = AttendanceDataStatus.E_CANCEL.ToString(); string E_REJECTED = AttendanceDataStatus.E_REJECTED.ToString(); var lstLeaveDay = new List<Att_LeaveDay>().Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.LeaveDayTypeID, m.Status }).ToList(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork); var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); lstLeaveDay = repoAtt_Leaveday .FindBy(m => m.Status != E_CANCEL && m.Status != E_REJECTED && m.DateStart <= DateMax && m.DateEnd >= DateMin && lstProfileId.Contains(m.ProfileID) && m.IsDelete == null) .Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.LeaveDayTypeID, m.Status }).ToList(); var lstLeaveType = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null).Select(m => new { m.ID, m.Code, m.CodeStatistic }).ToList(); var lstDayOffHoliday = repoCat_DayOff.FindBy(m => m.DateOff != null && m.IsDelete == null).Select(m => new { m.DateOff, m.Type }).ToList(); foreach (var item in lstWorkday) { Guid profileId = item.ProfileID; DateTime workDate = item.WorkDate.Date; var Dayoff = lstDayOffHoliday.Where(m => m.DateOff == workDate).FirstOrDefault(); if (Dayoff != null) { string code = string.Empty; if (Dayoff.Type == HolidayType.E_HOLIDAY_HLD.ToString()) { code = "HLD"; } else if (Dayoff.Type == HolidayType.E_WEEKEND_HLD.ToString()) { code = "WH"; } else { code = "CH"; } var leavetype = lstLeaveType.Where(m => m.Code == code).FirstOrDefault(); if (leavetype == null) { continue; } string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code; item.udLeavedayCode1 = leavedayCode; continue; } var lstLeaveDayByProfile = lstLeaveDay.Where(m => m.ProfileID == profileId && m.DateStart.Date <= workDate && m.DateEnd.Date >= workDate).ToList(); int Num = 0; foreach (var leaveDay in lstLeaveDayByProfile) { Num++; if (Num == 3) break; if (leaveDay != null) { if (Num == 1) { var leavetype = lstLeaveType.Where(m => m.ID == leaveDay.LeaveDayTypeID).FirstOrDefault(); if (leavetype != null) { string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code; item.udLeavedayCode1 = leavedayCode; } item.udLeavedayStatus1 = leaveDay.Status; } else if (Num == 2) { var leavetype = lstLeaveType.Where(m => m.ID == leaveDay.LeaveDayTypeID).FirstOrDefault(); if (leavetype != null) { string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code; item.udLeavedayCode2 = leavedayCode; } item.udLeavedayStatus2 = leaveDay.Status; } } } } } return; }
public void AnalyzeCompensation(int Year, List <Guid> lstProfileIDTotal) { //Lấy cấu hình //lấy ngày nghỉ (Bù) //Lấy tăng ca (Bù) //Lấy Ca Làm Việc (Bù) //Hệ Số Tăng Ca (Loại Tăng Ca) foreach (var lstProfileID in lstProfileIDTotal.Chunk(500)) { using (var context = new VnrHrmDataContext()) { #region Khai báo var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoAtt_Roster = new Att_RosterRepository(unitOfWork); var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); var repoAtt_CompensationConfig = new Att_CompensationConfigRepository(unitOfWork); var repoAtt_CompensationDetail = new Att_CompensationDetailRepository(unitOfWork); #endregion #region Lấy Dữ Liệu // var profileByCodeEmp = repoHre_Profile.FindBy(p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault(); var lstProfile = repoHre_Profile.FindBy(m => lstProfileID.Contains(m.ID)).Select(m => new { m.ID, m.DateHire }).ToList(); var lstCompensationConfig = repoAtt_CompensationConfig .FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value)) .Select(m => new CompensationConfigModel() { ID = m.ID, ProfileID = m.ProfileID, Year = m.Year, InitAvailable = m.InitAvailable, MonthBeginInYear = m.MonthBeginInYear, MonthResetInitAvailable = m.MonthResetInitAvailable, MonthStartProfile = m.MonthStartProfile }) .ToList(); int MonthBeginYear = 1; if (lstCompensationConfig != null && lstCompensationConfig.Count > 0) { MonthBeginYear = lstCompensationConfig.FirstOrDefault().MonthBeginInYear ?? 1; } DateTime BeginYear = new DateTime(Year, MonthBeginYear, 1); DateTime EndYear = BeginYear.AddYears(1).AddMinutes(-1); string E_APPROVED_Leave = LeaveDayStatus.E_APPROVED.ToString(); var lstLeaveDayCompensationTypeID = repoCat_LeaveDayType .FindBy(m => m.IsTimeOffInLieu == true) .Select(m => m.ID) .ToList(); var lstLeaveday = repoAtt_Leaveday .FindBy(m => m.Status == E_APPROVED_Leave && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstLeaveDayCompensationTypeID.Contains(m.LeaveDayTypeID) && lstProfileID.Contains(m.ProfileID)) .Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.DurationType }) .ToList(); string E_TIMEOFF = MethodOption.E_TIMEOFF.ToString(); string E_APPROVED_Overtime = OverTimeStatus.E_APPROVED.ToString(); string E_CONFIRM_Overtime = OverTimeStatus.E_CONFIRM.ToString(); var lstOvertime = repoAtt_Overtime .FindBy(m => m.WorkDateRoot >= BeginYear && m.WorkDateRoot <= EndYear && m.MethodPayment == E_TIMEOFF && (m.Status == E_APPROVED_Overtime || m.Status == E_CONFIRM_Overtime) && lstProfileID.Contains(m.ProfileID)) .Select(m => new { m.ID, m.ProfileID, m.WorkDateRoot, m.ApproveHours, m.ConfirmHours, m.Status }).ToList(); string E_APPROVED_Roster = RosterStatus.E_APPROVED.ToString(); var lstRoster = repoAtt_Roster .FindBy(m => m.Status == E_APPROVED_Roster && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstProfileID.Contains(m.ProfileID)) .Select(m => new RosterModel { ID = m.ID, ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, Type = m.Type, MonShiftID = m.MonShiftID, TueShiftID = m.TueShiftID, WedShiftID = m.WedShiftID, ThuShiftID = m.ThuShiftID, FriShiftID = m.FriShiftID, SatShiftID = m.SatShiftID, SunShiftID = m.SunShiftID }) .ToList(); var lstShift = repoCat_Shift.GetAll().Select(m => new { m.ID, m.WorkHours, m.StdWorkHours }).ToList(); List <Att_CompensationDetail> detailInDB = repoAtt_CompensationDetail .FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value)) .ToList(); List <Att_CompensationDetail> detailNew = new List <Att_CompensationDetail>(); foreach (var ProfileID in lstProfileID) { double TotalLeaveHourBeforeMonth = 0; double TotalOvertimeHourBeforeMonth = 0; var Profile = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault(); int MonthHire = 1; if (Profile.DateHire != null && Profile.DateHire >= BeginYear && Profile.DateHire <= EndYear) { if (Profile.DateHire.Value.Day >= 15) { MonthHire = Profile.DateHire.Value.AddMonths(1).Month; } else { MonthHire = Profile.DateHire.Value.Month; } } for (DateTime Month = BeginYear; Month < EndYear; Month = Month.AddMonths(1)) { if (MonthHire > Month.Month) { continue; } DateTime BeginMonth = Month; DateTime EndMonth = BeginMonth.AddMonths(1).AddMinutes(-1); double TotalLeaveHourInMonth = 0; double TotalOvertimeHourInMonth = 0; #region Tăng Ca var lstOvertimeByProfileByMonth = lstOvertime.Where(m => m.ProfileID == ProfileID && m.WorkDateRoot >= BeginMonth && m.WorkDateRoot <= EndMonth).ToList(); TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_CONFIRM_Overtime).Sum(m => m.ConfirmHours); TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_APPROVED_Overtime).Sum(m => m.ApproveHours ?? 0); #endregion #region Ngày Nghỉ var lstLeavedayByProfileByMonth = lstLeaveday.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList(); var lstRosterByProfileByMonth = lstRoster.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList(); foreach (var Leaveday in lstLeavedayByProfileByMonth) { DateTime dateStart = Leaveday.DateStart < BeginMonth ? BeginMonth : Leaveday.DateStart; DateTime dateEnd = Leaveday.DateEnd > EndMonth ? EndMonth : Leaveday.DateEnd; for (DateTime dateCheck = dateStart; dateCheck <= dateEnd; dateCheck = dateCheck.AddDays(1)) { if (Leaveday.DurationType != LeaveDayDurationType.E_FULLSHIFT.ToString()) // Trong Ca { TotalLeaveHourInMonth += Leaveday.LeaveHours ?? 0; } else //Toàn Ca { //Check xem có Ca làm việc không Guid?ShiftID = null; var E_TIME_OFF = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_TIME_OFF.ToString()).FirstOrDefault(); var E_CHANGE_SHIFT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_CHANGE_SHIFT.ToString()).FirstOrDefault(); var E_DEFAULT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_DEFAULT.ToString()).FirstOrDefault(); var E_ROSTERGROUP = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_ROSTERGROUP.ToString()).FirstOrDefault(); if (E_TIME_OFF != null) { } else if (E_CHANGE_SHIFT != null) { ShiftID = GetShiftRoster(dateCheck, E_CHANGE_SHIFT); } else if (E_DEFAULT != null) { ShiftID = GetShiftRoster(dateCheck, E_DEFAULT); } else if (E_ROSTERGROUP != null) { //Chưa hỗ trợ đăng ký ca theo nhóm } if (ShiftID != null) { var Shift = lstShift.Where(m => m.ID == ShiftID.Value).FirstOrDefault(); if (Shift != null) { TotalLeaveHourInMonth += Shift.StdWorkHours ?? (Shift.WorkHours ?? 0); } } } } } #endregion #region Gán Dữ Liệu CompensationConfigModel Config = lstCompensationConfig.Where(m => m.ProfileID == ProfileID).FirstOrDefault(); Att_CompensationDetail detail = detailInDB.Where(m => m.MonthYear == Month && m.ProfileID == ProfileID).FirstOrDefault(); if (detail == null) { detail = new Att_CompensationDetail(); detail.ID = Guid.NewGuid(); detailNew.Add(detail); } if (Config != null) { if (Config.MonthResetInitAvailable != null && Config.MonthResetInitAvailable.Value <= Month.Month) { } else { detail.InitAvailable = Config.InitAvailable; } detail.MonthBeginInYear = Config.MonthBeginInYear ?? 1; detail.MonthResetInitAvailable = Config.MonthResetInitAvailable; } detail.ProfileID = ProfileID; detail.Year = Year; detail.MonthYear = Month; detail.MonthStartProfile = MonthHire; detail.LeaveInMonth = TotalLeaveHourInMonth; detail.TotalLeaveBef = TotalLeaveHourBeforeMonth; detail.OvertimeInMonth = TotalOvertimeHourInMonth; detail.TotalOvertimeBef = TotalOvertimeHourBeforeMonth; detail.Remain = ((detail.InitAvailable ?? 0) + TotalOvertimeHourBeforeMonth + TotalOvertimeHourInMonth) - (TotalLeaveHourBeforeMonth + TotalLeaveHourInMonth); TotalOvertimeHourBeforeMonth += TotalOvertimeHourInMonth; TotalLeaveHourBeforeMonth += TotalLeaveHourInMonth; #endregion } } repoAtt_CompensationDetail.Add(detailNew); repoAtt_CompensationDetail.SaveChanges(); #endregion } } }
/// <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 void AnalyzeCompensation(int Year, List<Guid> lstProfileIDTotal) { //Lấy cấu hình //lấy ngày nghỉ (Bù) //Lấy tăng ca (Bù) //Lấy Ca Làm Việc (Bù) //Hệ Số Tăng Ca (Loại Tăng Ca) foreach (var lstProfileID in lstProfileIDTotal.Chunk(500)) { using (var context = new VnrHrmDataContext()) { #region Khai báo var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoAtt_Roster = new Att_RosterRepository(unitOfWork); var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); var repoAtt_CompensationConfig = new Att_CompensationConfigRepository(unitOfWork); var repoAtt_CompensationDetail = new Att_CompensationDetailRepository(unitOfWork); #endregion #region Lấy Dữ Liệu // var profileByCodeEmp = repoHre_Profile.FindBy(p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault(); var lstProfile = repoHre_Profile.FindBy(m => lstProfileID.Contains(m.ID)).Select(m => new { m.ID, m.DateHire }).ToList(); var lstCompensationConfig = repoAtt_CompensationConfig .FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value)) .Select(m => new CompensationConfigModel() { ID = m.ID, ProfileID = m.ProfileID, Year = m.Year, InitAvailable = m.InitAvailable, MonthBeginInYear = m.MonthBeginInYear, MonthResetInitAvailable = m.MonthResetInitAvailable, MonthStartProfile = m.MonthStartProfile }) .ToList(); int MonthBeginYear = 1; if (lstCompensationConfig != null && lstCompensationConfig.Count>0) { MonthBeginYear = lstCompensationConfig.FirstOrDefault().MonthBeginInYear ?? 1; } DateTime BeginYear = new DateTime(Year, MonthBeginYear, 1); DateTime EndYear = BeginYear.AddYears(1).AddMinutes(-1); string E_APPROVED_Leave = LeaveDayStatus.E_APPROVED.ToString(); var lstLeaveDayCompensationTypeID = repoCat_LeaveDayType .FindBy(m => m.IsTimeOffInLieu == true) .Select(m => m.ID) .ToList(); var lstLeaveday = repoAtt_Leaveday .FindBy(m => m.Status == E_APPROVED_Leave && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstLeaveDayCompensationTypeID.Contains(m.LeaveDayTypeID) && lstProfileID.Contains(m.ProfileID)) .Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.DurationType }) .ToList(); string E_TIMEOFF = MethodOption.E_TIMEOFF.ToString(); string E_APPROVED_Overtime = OverTimeStatus.E_APPROVED.ToString(); string E_CONFIRM_Overtime = OverTimeStatus.E_CONFIRM.ToString(); var lstOvertime = repoAtt_Overtime .FindBy(m => m.WorkDateRoot >= BeginYear && m.WorkDateRoot <= EndYear && m.MethodPayment == E_TIMEOFF && (m.Status == E_APPROVED_Overtime || m.Status == E_CONFIRM_Overtime) && lstProfileID.Contains(m.ProfileID)) .Select(m => new { m.ID, m.ProfileID, m.WorkDateRoot, m.ApproveHours, m.ConfirmHours, m.Status }).ToList(); string E_APPROVED_Roster = RosterStatus.E_APPROVED.ToString(); var lstRoster = repoAtt_Roster .FindBy(m => m.Status == E_APPROVED_Roster && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstProfileID.Contains(m.ProfileID)) .Select(m => new RosterModel { ID = m.ID, ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, Type = m.Type, MonShiftID = m.MonShiftID, TueShiftID = m.TueShiftID, WedShiftID = m.WedShiftID, ThuShiftID = m.ThuShiftID, FriShiftID = m.FriShiftID, SatShiftID = m.SatShiftID, SunShiftID = m.SunShiftID }) .ToList(); var lstShift = repoCat_Shift.GetAll().Select(m => new { m.ID, m.WorkHours, m.StdWorkHours }).ToList(); List<Att_CompensationDetail> detailInDB = repoAtt_CompensationDetail .FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value)) .ToList(); List<Att_CompensationDetail> detailNew = new List<Att_CompensationDetail>(); foreach (var ProfileID in lstProfileID) { double TotalLeaveHourBeforeMonth = 0; double TotalOvertimeHourBeforeMonth = 0; var Profile = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault(); int MonthHire = 1; if (Profile.DateHire != null && Profile.DateHire >= BeginYear && Profile.DateHire <= EndYear) { if (Profile.DateHire.Value.Day >= 15) { MonthHire = Profile.DateHire.Value.AddMonths(1).Month; } else { MonthHire = Profile.DateHire.Value.Month; } } for (DateTime Month = BeginYear; Month < EndYear; Month = Month.AddMonths(1)) { if (MonthHire > Month.Month) continue; DateTime BeginMonth = Month; DateTime EndMonth = BeginMonth.AddMonths(1).AddMinutes(-1); double TotalLeaveHourInMonth = 0; double TotalOvertimeHourInMonth = 0; #region Tăng Ca var lstOvertimeByProfileByMonth = lstOvertime.Where(m => m.ProfileID == ProfileID && m.WorkDateRoot >= BeginMonth && m.WorkDateRoot <= EndMonth).ToList(); TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_CONFIRM_Overtime).Sum(m => m.ConfirmHours); TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_APPROVED_Overtime).Sum(m => m.ApproveHours ?? 0); #endregion #region Ngày Nghỉ var lstLeavedayByProfileByMonth = lstLeaveday.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList(); var lstRosterByProfileByMonth = lstRoster.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList(); foreach (var Leaveday in lstLeavedayByProfileByMonth) { DateTime dateStart = Leaveday.DateStart < BeginMonth ? BeginMonth : Leaveday.DateStart; DateTime dateEnd = Leaveday.DateEnd > EndMonth ? EndMonth : Leaveday.DateEnd; for (DateTime dateCheck = dateStart; dateCheck <= dateEnd; dateCheck = dateCheck.AddDays(1)) { if (Leaveday.DurationType != LeaveDayDurationType.E_FULLSHIFT.ToString()) // Trong Ca { TotalLeaveHourInMonth += Leaveday.LeaveHours ?? 0; } else //Toàn Ca { //Check xem có Ca làm việc không Guid? ShiftID = null; var E_TIME_OFF = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_TIME_OFF.ToString()).FirstOrDefault(); var E_CHANGE_SHIFT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_CHANGE_SHIFT.ToString()).FirstOrDefault(); var E_DEFAULT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_DEFAULT.ToString()).FirstOrDefault(); var E_ROSTERGROUP = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_ROSTERGROUP.ToString()).FirstOrDefault(); if (E_TIME_OFF != null) { } else if (E_CHANGE_SHIFT != null) { ShiftID = GetShiftRoster(dateCheck, E_CHANGE_SHIFT); } else if (E_DEFAULT != null) { ShiftID = GetShiftRoster(dateCheck, E_DEFAULT); } else if (E_ROSTERGROUP != null) { //Chưa hỗ trợ đăng ký ca theo nhóm } if (ShiftID != null) { var Shift = lstShift.Where(m => m.ID == ShiftID.Value).FirstOrDefault(); if (Shift != null) { TotalLeaveHourInMonth += Shift.StdWorkHours ?? (Shift.WorkHours ?? 0); } } } } } #endregion #region Gán Dữ Liệu CompensationConfigModel Config = lstCompensationConfig.Where(m => m.ProfileID == ProfileID).FirstOrDefault(); Att_CompensationDetail detail = detailInDB.Where(m => m.MonthYear == Month && m.ProfileID == ProfileID).FirstOrDefault(); if (detail == null) { detail = new Att_CompensationDetail(); detail.ID = Guid.NewGuid(); detailNew.Add(detail); } if (Config != null) { if (Config.MonthResetInitAvailable != null && Config.MonthResetInitAvailable.Value <= Month.Month) { } else { detail.InitAvailable = Config.InitAvailable; } detail.MonthBeginInYear = Config.MonthBeginInYear ?? 1; detail.MonthResetInitAvailable = Config.MonthResetInitAvailable; } detail.ProfileID = ProfileID; detail.Year = Year; detail.MonthYear = Month; detail.MonthStartProfile = MonthHire; detail.LeaveInMonth = TotalLeaveHourInMonth; detail.TotalLeaveBef = TotalLeaveHourBeforeMonth; detail.OvertimeInMonth = TotalOvertimeHourInMonth; detail.TotalOvertimeBef = TotalOvertimeHourBeforeMonth; detail.Remain = ((detail.InitAvailable ?? 0) + TotalOvertimeHourBeforeMonth + TotalOvertimeHourInMonth) - (TotalLeaveHourBeforeMonth + TotalLeaveHourInMonth); TotalOvertimeHourBeforeMonth += TotalOvertimeHourInMonth; TotalLeaveHourBeforeMonth += TotalLeaveHourInMonth; #endregion } } repoAtt_CompensationDetail.Add(detailNew); repoAtt_CompensationDetail.SaveChanges(); #endregion } } }
/// <summary> /// Update thuộc tính IsDelete là true /// </summary> /// <param name="catLeaveDayTypeId"></param> /// <returns></returns> public bool DeleteCatLeaveDayType(int catLeaveDayTypeId) { using (var context = new VnrAttendanceDataContext()) { IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork); Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType(); catLeaveDayType = repo.GetById(catLeaveDayTypeId); try { repo.Remove(catLeaveDayType); repo.SaveChanges(); return true; } catch { return false; } } }
public DataTable ReportMealTimeDetailNoEat(List<Guid?> lstWorkPlace, String status, String codeEmp, Boolean isCludeEmpQuit, DateTime dateStart, DateTime dateEndSearch, List<Guid?> orgIDs) { List<Can_ReportMealTimeDetailNoEatEntity> lstreportMealTimeDetailNoEatEntity = new List<Can_ReportMealTimeDetailNoEatEntity>(); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DateTime dateEnd = dateEndSearch.AddDays(1).AddMilliseconds(-1); var repomealRecordMissing = new Can_MealRecordMissingRepository(unitOfWork); var mealRecordMissings = repomealRecordMissing.FindBy(s => s.ProfileID != null && s.WorkDate != null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.MealAllowanceTypeSettingID == null) .Select(s => new { s.ProfileID, s.WorkDate, s.Status, s.MealAllowanceTypeSettingID, s.OrgStructureID }).ToList(); DataTable table = GetSchemaRptMealTimeDetailNoEat(); if (mealRecordMissings == null && mealRecordMissings.Count < 1) { return table; } var profileIds = mealRecordMissings.Select(s => s.ProfileID.Value).Distinct().ToList(); var repoProfile = new Hre_ProfileRepository(unitOfWork); var profiles = repoProfile.FindBy(s => profileIds.Contains(s.ID)).ToList(); if (orgIDs != null && orgIDs[0] != null) { profiles = profiles.Where(hr => hr.OrgStructureID != null && orgIDs.Contains(hr.OrgStructureID)).ToList(); } if (lstWorkPlace[0] != Guid.Empty) { profiles = profiles.Where(wp => wp.WorkPlaceID != null && lstWorkPlace.Contains(wp.WorkPlaceID.Value)).ToList(); } if (isCludeEmpQuit != true) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).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(); } } profileIds = profiles.Select(s => s.ID).ToList(); mealRecordMissings = mealRecordMissings.Where(s => profileIds.Contains(s.ProfileID.Value)).ToList(); //trạng thái if (status != null) { mealRecordMissings = mealRecordMissings.Where(meal => status.Contains(meal.Status)).ToList(); } //workday var repAttWorkday = new Att_WorkDayRepository(unitOfWork); var lstWorkday = repAttWorkday.FindBy(wd => wd.WorkDate >= dateStart && wd.WorkDate <= dateEnd && profileIds.Contains(wd.ProfileID)) .Select(wd => new {wd.ProfileID, wd.InTime1, wd.OutTime1, wd.WorkDate }).ToList(); //danh mục leaveday type var repLeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var leadayHLDID = repLeaveDayType.FindBy(ld => ld.Code == "HLD").Select(ld => ld.ID).FirstOrDefault(); //att_leaveday var repAttLeaveDay = new Att_LeavedayRepository(unitOfWork); var E_APPROVED = LeaveDayStatus.E_APPROVED.ToString(); var lstLeaveday = repAttLeaveDay.FindBy(ld => ld.LeaveDayTypeID != leadayHLDID && (profileIds.Contains(ld.ProfileID)) && ld.Status == E_APPROVED && dateStart <= ld.DateEnd && ld.DateStart <= dateEnd) .Select(ld => new { ld.ProfileID, ld.DateStart, ld.DateEnd }).ToList(); //can_line var repHDTjob = new Can_LineRepository(unitOfWork); var lstHDTjobID = repHDTjob.FindBy(hdt => hdt.IsHDTJOB == true).Select(hdt => hdt.ID).ToList(); //Can_mealRecord var repMealRecord = new Can_MealRecordRepository(unitOfWork); var lstMealRecord = repMealRecord.FindBy(meal => meal.LineID != null && !lstHDTjobID.Contains(meal.LineID.Value) && meal.ProfileID.HasValue && profileIds.Contains(meal.ProfileID.Value) && dateStart <= meal.WorkDay && meal.WorkDay <= dateEnd) .Select(meal => new { meal.ProfileID, meal.WorkDay }).ToList(); //Allowance type setting var repAllowanceTypeSetting = new Can_MealAllowanceTypeSettingRepository(unitOfWork); var lstAllowanceTypeSetting = repAllowanceTypeSetting.GetAll().Select(al => new {al.ID, al.MealAllowanceTypeSettingName }).ToList(); var repoOrgType = new Cat_OrgStructureTypeRepository(unitOfWork); var listOrgType = repoOrgType.FindBy(s => s.IsDelete == null).ToList(); var repoOrg = new Cat_OrgStructureRepository(unitOfWork); var listOrgAll = repoOrg.GetAll().ToList(); //var repoLine = new Can_LineRepository(unitOfWork); //var repoCatering = new Can_CateringRepository(unitOfWork); //var repoCanteen = new Can_CanteenRepository(unitOfWork); foreach (var mealRecordMissing in mealRecordMissings) { // neu co quet the an thi ko tinh var meadRecord = lstMealRecord.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.WorkDay.Value.Date == mealRecordMissing.WorkDate.Value.Date); if (meadRecord != null) { continue; } // neu ngay do dk nghi thi ko tinh var leaveday = lstLeaveday.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.DateStart <= mealRecordMissing.WorkDate && mealRecordMissing.WorkDate <= s.DateEnd); if (leaveday != null) { continue; } // neu ngay do ko di lam thi ko tinh var workday = lstWorkday.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.WorkDate.Date == mealRecordMissing.WorkDate.Value.Date); if (workday == null) { continue; } DataRow row = table.NewRow(); if (workday != null && workday.InTime1 != null) { row["TimeIn"] = workday.InTime1; } if (workday != null && workday.OutTime1 != null) { row["TimeOut"] = workday.OutTime1; } var profile = profiles.FirstOrDefault(s => s.ID == mealRecordMissing.ProfileID); if (profile != null) { var type = lstAllowanceTypeSetting.FirstOrDefault(s => s.ID == mealRecordMissing.MealAllowanceTypeSettingID); var org = listOrgAll.FirstOrDefault(s => profile != null && s.ID == mealRecordMissing.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType); var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType); var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType); var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType); row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeDepartment] = orgOrg != null ? orgOrg.Code : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Can_ReportCardByLocationEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Can_ReportCardByLocationEntity.FieldNames.ProfileName] = profile.ProfileName; row[Can_ReportCardByLocationEntity.FieldNames.Date] = mealRecordMissing.WorkDate; row[Can_ReportCardByLocationEntity.FieldNames.DateFrom] = dateStart; row[Can_ReportCardByLocationEntity.FieldNames.DateTo] = dateEnd; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.DateExport] = DateTime.Now; row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.MealAllowanceName] = type != null ? type.MealAllowanceTypeSettingName : string.Empty; table.Rows.Add(row); } } return table; } }
/// <summary> Hàm tính toán và save lại bảo hiểm của từng người theo tháng </summary> /// <param name="orgs"></param> /// <param name="monthCheck">Vd: 01/12/2014</param> /// <param name="periodInsurance">Loại tạm thời , Loại Chính Thức</param> public void AnalyzeAndSaveInsuranceByMonth(string orgs, DateTime monthCheck, string periodInsurance, string codeEmp, List<Guid> socialInsPlaceIDs,string userLogin) { /* * Goal(tính toán và save lại bảo hiểm của từng NV theo tháng và phòng ban (Tạo mới hoặc update Ins_ProfileInsuranceMonthly)) * Steps : * - Step1 : Lấy DS NV theo phòng ban và theo tháng được chọn * - Step2 : xử lý tính ra tháng bắt đầu join đóng BHXH,BHYT,BHTN * - Step3 : Set giá trị cho IsLeaveNonWorkday trong profile (cho nghỉ >=14 ngày) * - Step4 : kiểm tra xem có đóng BHXH,BHYT,BHTN * - Step5 : Tính lương và mức đóng của BHXH,BHYT,BHTN * - Step6 : Tạo mới hoặc cập nhật dữ liệu vào bảng Ins_ProfileInsuranceMonthly */ //ngày 1 tháng N var beginMonth = new DateTime(monthCheck.Year, monthCheck.Month, 1); //ngày 31 tháng N DateTime endMonth = beginMonth.AddMonths(1).AddMinutes(-1); var step = 200; monthCheck = new DateTime(monthCheck.Year, monthCheck.Month, 1); var lstProfile = new List<Hre_ProfileEntity>(); using (var context = new VnrHrmDataContext()) { #region Khai báo var status = string.Empty; var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork); var repoSalInsuranceSalary = new Sal_InsuranceSalaryRepository(unitOfWork); var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork); var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork); var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); var repoAtt_Roster = new Att_RosterRepository(unitOfWork); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var repoIns_InsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork); var profileIDByCodeEmp = Guid.Empty; #endregion #region phân tích theo mã NV (từ đk tìm kiếm) if (!string.IsNullOrEmpty(codeEmp)) { var profileByCodeEmp = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault(); if (profileByCodeEmp != null) { profileIDByCodeEmp = profileByCodeEmp.ID; } else { return; } } #endregion //lay ds ngay nghi var dayOffMonthChecks = unitOfWork.CreateQueryable<Cat_DayOff>(Guid.Empty, m => m.DateOff.Year == monthCheck.Year && m.DateOff.Month == monthCheck.Month).ToList(); #region lay hop dong theo phòng ban //List<object> contractPara = new List<object>(); //contractPara.AddRange(new object[16]); //contractPara[0] = null; //contractPara[1] = null; //contractPara[2] = orgs; //contractPara[3] = null; //contractPara[4] = null; //contractPara[5] = null; //contractPara[6] = null; //contractPara[7] = null; //contractPara[8] = null; //contractPara[9] = null; //contractPara[10] = null; //contractPara[11] = null; //contractPara[12] = null; //contractPara[13] = null; //contractPara[14] = 1; //contractPara[15] = int.MaxValue; //if (profileIDByCodeEmp != Guid.Empty) //{ // contractPara[1] = codeEmp; // contractPara[2] = null; //} //var contracts = GetData<Hre_ContractEntity>(contractPara, ConstantSql.hrm_hr_sp_get_Contract, ref status).Translate<Hre_Contract>().ToList(); var contracts = new List<Hre_Contract>(); #endregion #region Get Profiles by orgs var listObj = new List<object> { orgs, string.Empty, string.Empty }; if (profileIDByCodeEmp != Guid.Empty) { listObj[0] = null; listObj[2] = codeEmp; } if (!string.IsNullOrEmpty(codeEmp)) { lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.CodeEmp == codeEmp).ToList().Translate<Hre_ProfileEntity>(); } else if (orgs != null) { var lstOrderNumberORG = orgs.Split(',').Select(s => int.Parse(s)).Distinct().ToList(); var orgObj = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty).Select(p => p.ID).ToList(); var orgStructureIDs = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty, m => lstOrderNumberORG.Contains(m.OrderNumber)).Select(m => m.ID).ToList(); lstProfile = new List<Hre_ProfileEntity>(); foreach (var orgIds in orgStructureIDs.Chunk(step)) { var profileByOrgs = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.OrgStructureID.HasValue && orgIds.Contains(m.OrgStructureID.Value)).ToList().Translate<Hre_ProfileEntity>(); lstProfile.AddRange(profileByOrgs); } } #endregion if (socialInsPlaceIDs != null) { socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList(); if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any()) { lstProfile = lstProfile.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList(); } } #region lay ds inusuranceSalary by profile list //ds profileId theo phong ban List<Guid> lstProfileIDs = lstProfile.Select(m => m.ID).ToList(); var lstInsuranceSalaryByProfile = new List<Sal_InsuranceSalary>(); foreach (var profileIds in lstProfileIDs.Chunk(step)) { var lstInsuranceSalaryByProfileTemp = unitOfWork.CreateQueryable<Sal_InsuranceSalary>(Guid.Empty, m => m.InsuranceAmount != null && m.ProfileID != null && m.DateEffect <= endMonth && profileIds.Contains(m.ProfileID.Value)).ToList(); lstInsuranceSalaryByProfile.AddRange(lstInsuranceSalaryByProfileTemp); var contractTemp = unitOfWork.CreateQueryable<Hre_Contract>(Guid.Empty, m => profileIds.Contains(m.ProfileID)).ToList(); contracts.AddRange(contractTemp); } if (lstInsuranceSalaryByProfile.Any()) { lstProfileIDs = lstInsuranceSalaryByProfile.Select(p => p.ProfileID ?? Guid.Empty).ToList(); lstProfile = lstProfile.Where(p => lstProfileIDs.Contains(p.ID)).ToList(); } #endregion #region ds NV đóng Bảo Hiểm theo phòng ban và tháng (Ins_ProfileInsuranceMonthly) var listInsMonthlyObj = new List<object> { orgs, monthCheck, null, 1, int.MaxValue - 1 }; if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString()) { listInsMonthlyObj.Add("id"); } var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly,userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>(); // lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(m => m.PaybackID == null ).ToList(); #endregion #region Tháng bắt đầu tham gia bảo hiểm SetMonthJoinInsuranceByProfile(lstProfile, lstInsuranceSalaryByProfile, endMonth); #endregion #region Kiem tra co dong bao hiem ko? //Kiểm tra xem co đóng BHXH,BHYT,BHTN SetIsHaveInsurnceByProfileByMonth(lstProfile, monthCheck, contracts,userLogin); if (lstProfile.Any()) { lstProfileIDs = lstProfile.Select(p => p.ID).ToList(); } #endregion #region Nghỉ 14 ngày List<Hre_ProfileEntity> lstProfileEntities = new List<Hre_ProfileEntity>(); List<string> lstLeaveInsuranceType = String.Join(",", leaveDayInsuranceTypes).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList(); if (HasLeaveGreater14day) { string E_APPROVE = HDTJobStatus.E_APPROVE.ToString(); var lstProfilesIdChunks = lstProfileIDs; var leaveDayTypeIDs = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, m => m.PaidRate == 0 || lstLeaveInsuranceType.Contains(m.InsuranceType)).Select(m => m.ID).ToList(); foreach (var profileIDs in lstProfilesIdChunks.Chunk(step)) { var lstProfileEntitiesTemp = lstProfile.Where(p => profileIDs.Contains(p.ID)).ToList(); #region workday từ 1->31 tháng N theo các NV (không có quẹt thẻ) var lstWorkday = unitOfWork.CreateQueryable<Att_Workday>(Guid.Empty, m => m.InTime1 == null && m.OutTime1 == null && m.WorkDate >= beginMonth && m.WorkDate <= endMonth && profileIDs.Contains(m.ProfileID)).Select(m => new CustomWorkDayEntity() { ProfileID = m.ProfileID, InTime1 = m.InTime1, OutTime1 = m.OutTime1, WorkDate = m.WorkDate }).ToList(); #endregion #region Leaveday (ngày nghỉ trong khoảng 1->31 tháng N) theo NV string app = LeaveDayStatus.E_APPROVED.ToString(); var lstLeaveDay = unitOfWork.CreateQueryable<Att_LeaveDay>(Guid.Empty, m => m.Status == app && m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID)) .Select(m => new CustomLeavedayEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, LeaveDayTypeID = m.LeaveDayTypeID }).ToList(); #endregion //paidrate = 0 :ko tra luong var lstLeavedayTypeRateZero = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, p => p.PaidRate == 0).ToList(); #region lấy ds chứng từ bảo hiểm var insRecords = unitOfWork.CreateQueryable<Ins_InsuranceRecord>(Guid.Empty, m => m.DateStart != null && m.DateEnd != null && m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID)) .Select(m => new CustomInsuranceRecordEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart.Value, DateEnd = m.DateEnd.Value, InsuranceType = m.InsuranceType }).ToList(); #endregion var approved = RosterStatus.E_APPROVED.ToString(); var rosters = unitOfWork.CreateQueryable<Att_Roster>(Guid.Empty, p => p.Status == approved && (p.DateStart <= endMonth && p.DateEnd >= beginMonth) && profileIDs.Contains(p.ProfileID)) .Select(m => new CustomRosterEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, MonShiftID = m.MonShiftID, TueShiftID = m.TueShiftID, WedShiftID = m.WedShiftID, ThuShiftID = m.ThuShiftID, FriShiftID = m.FriShiftID, SatShiftID = m.SatShiftID, SunShiftID = m.SunShift2ID }) .ToList(); //kt co nghỉ hơn 14 ngay ko? SetLeaveNonWorkdayByProfile(lstProfileEntitiesTemp, beginMonth, endMonth, lstWorkday, lstLeaveDay, lstLeavedayTypeRateZero, insRecords, rosters, dayOffMonthChecks); //Hàm do Triet.Mai Hỗ trợ. tính ra số ngày không làm HDTJOb // "SC, SU, SM, SD, DP, D, M, AL, TSC,TAS" lstLeaveDay = lstLeaveDay.Where(m => m.LeaveDayTypeID != null && leaveDayTypeIDs.Contains(m.LeaveDayTypeID.Value)).ToList(); var lstHDTJob = unitOfWork.CreateQueryable<Hre_HDTJob>(Guid.Empty, m => m.Status == E_APPROVE && m.DateFrom <= endMonth && m.DateTo >= beginMonth && m.ProfileID.HasValue && profileIDs.Contains(m.ProfileID.Value)).ToList(); GetHDTJobDayByProfile(lstProfileEntitiesTemp, leaveDayTypeIDs, monthCheck, rosters, lstLeaveDay, lstHDTJob); lstProfileEntities.AddRange(lstProfileEntitiesTemp); } } lstProfile = lstProfileEntities; #endregion #region tính lương , mức đóng BHXH,BHYT,BHTN SetMoneyInsuranceByProfileByMonth(lstProfile, lstInsuranceSalaryByProfile, endMonth, orgs, contracts,userLogin); #endregion #region Save Change var lstProfileInsuranceMonthlyAddNew = new List<Ins_ProfileInsuranceMonthly>(); var lstProfileInsuranceMonthlyModify = new List<Ins_ProfileInsuranceMonthly>(); foreach (var profile in lstProfile) { if (lstProfileInsuranceMonthlyInDb.Where(m => m.ProfileID == profile.ID && m.PaybackID != null).FirstOrDefault() != null) { continue; } Ins_ProfileInsuranceMonthly insuranceByProfile = lstProfileInsuranceMonthlyInDb .FirstOrDefault(m => m.ProfileID == profile.ID && m.PaybackID == null); if (insuranceByProfile != null) //Update cái dữ liệu cũ { insuranceByProfile.WorkPlaceID = profile.WorkPlaceID; insuranceByProfile.SocialInsPlaceID = profile.SocialInsPlaceID; insuranceByProfile.IsSocialInsurance = profile.IsHaveInsSocial; insuranceByProfile.IsHealthInsurance = profile.IsHaveInsHealth; insuranceByProfile.IsUnEmpInsurance = profile.IsHaveInsUnEmp; insuranceByProfile.IsPregnant = profile.IsPregnant; insuranceByProfile.MoneySocialInsurance = profile.MoneyInsuranceSocial; insuranceByProfile.MoneyHealthInsurance = profile.MoneyInsuranceHealth; insuranceByProfile.MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp; insuranceByProfile.SalaryInsurance = profile.MoneyInsuranceTotal; insuranceByProfile.SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal; insuranceByProfile.SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal; insuranceByProfile.IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays; insuranceByProfile.JobName = profile.JobName; insuranceByProfile.AmountHDTIns = profile.AmountHDTIns; insuranceByProfile.HDTGroupCode = profile.HDTJobGroupCode; insuranceByProfile.TypeGetData = periodInsurance; #region Tung.Ly set các tỉ lệ và mức đóng insuranceByProfile.Allowance1 = profile.Allowance1; insuranceByProfile.Allowance2 = profile.Allowance2; insuranceByProfile.Allowance3 = profile.Allowance3; insuranceByProfile.Allowance4 = profile.Allowance4; insuranceByProfile.AmountChargeIns = profile.AmountChargeIns; insuranceByProfile.SocialInsComRate = profile.SocialInsComRate; insuranceByProfile.SocialInsComAmount = profile.SocialInsComAmount; insuranceByProfile.SocialInsEmpRate = profile.SocialInsEmpRate; insuranceByProfile.SocialInsEmpAmount = profile.SocialInsEmpAmount; insuranceByProfile.HealthInsComRate = profile.HealthInsComRate; insuranceByProfile.HealthInsComAmount = profile.HealthInsComAmount; insuranceByProfile.HealthInsEmpRate = profile.HealthInsEmpRate; insuranceByProfile.HealthInsEmpAmount = profile.HealthInsEmpAmount; insuranceByProfile.UnemployComRate = profile.UnemployComRate; insuranceByProfile.UnemployComAmount = profile.UnemployComAmount; insuranceByProfile.UnemployEmpRate = profile.UnemployEmpRate; insuranceByProfile.UnemployEmpAmount = profile.UnemployEmpAmount; insuranceByProfile.IsPayback = false; insuranceByProfile.MonthYearEffect = monthCheck; #endregion lstProfileInsuranceMonthlyModify.Add(insuranceByProfile);//add vao list (Modify) } else //Tạo mới { var newInsurance = new Ins_ProfileInsuranceMonthly { ProfileID = profile.ID, WorkPlaceID = profile.WorkPlaceID, SocialInsPlaceID = profile.SocialInsPlaceID, IsSocialInsurance = profile.IsHaveInsSocial, IsHealthInsurance = profile.IsHaveInsHealth, IsUnEmpInsurance = profile.IsHaveInsUnEmp, IsPregnant = profile.IsPregnant, MoneySocialInsurance = profile.MoneyInsuranceSocial, MoneyHealthInsurance = profile.MoneyInsuranceHealth, MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp, IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays, SalaryInsurance = profile.MoneyInsuranceTotal, AmountHDTIns = profile.AmountHDTIns, HDTGroupCode = profile.HDTJobGroupCode, SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal, SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal, TypeGetData = periodInsurance, MonthYear = monthCheck, IsPayback = false, MonthYearEffect = monthCheck, #region Tung.Ly set các tỉ lệ và mức đóng Allowance1 = profile.Allowance1, Allowance2 = profile.Allowance2, Allowance3 = profile.Allowance3, Allowance4 = profile.Allowance4, AmountChargeIns = profile.AmountChargeIns, SocialInsComRate = profile.SocialInsComRate, SocialInsComAmount = profile.SocialInsComAmount, SocialInsEmpRate = profile.SocialInsEmpRate, SocialInsEmpAmount = profile.SocialInsEmpAmount, HealthInsComRate = profile.HealthInsComRate, HealthInsComAmount = profile.HealthInsComAmount, HealthInsEmpRate = profile.HealthInsEmpRate, HealthInsEmpAmount = profile.HealthInsEmpAmount, UnemployComRate = profile.UnemployComRate, UnemployComAmount = profile.UnemployComAmount, UnemployEmpRate = profile.UnemployEmpRate, UnemployEmpAmount = profile.UnemployEmpAmount, JobName = profile.JobName, #endregion }; lstProfileInsuranceMonthlyAddNew.Add(newInsurance);//add new } } if (lstProfileInsuranceMonthlyAddNew.Any()) { repoInsMonthly.Add(lstProfileInsuranceMonthlyAddNew); } if (lstProfileInsuranceMonthlyModify.Any()) { repoInsMonthly.Edit(lstProfileInsuranceMonthlyModify); } repoInsMonthly.SaveChanges(); #endregion } }