public DataTable LoadDataAnalyzeOvertime_DataLimit(string strOrgStructure, DateTime DateStart, DateTime DateEnd, List<Hre_ProfileEntity> lstProfile, List<string> lstTypeData, Att_OvertimeInfoFillterAnalyze _OvertimeInfoFillterAnalyzeEntity, string userLogin) { DataTable table = GetSchemaExportExcel(); string status = string.Empty; DateStart = DateStart.Date; DateEnd = DateEnd.Date.AddDays(1).AddMilliseconds(-1); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var repoCat_ShiftItem = new Cat_ShiftItemRepository(unitOfWork); var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var repoCat_OrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork); var repoCat_OrgStructureType = new CustomBaseRepository<Cat_OrgStructureType>(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); List<WorkdayCustom> lstWorkday = new List<WorkdayCustom>(); List<Guid> lstProfileId = lstProfile.Select(s => s.ID).ToList(); List<object> lst3ParamSE = new List<object>(); lst3ParamSE.Add(strOrgStructure); lst3ParamSE.Add(DateStart); lst3ParamSE.Add(DateEnd); if (lstProfile.Count > 0 && strOrgStructure == null) { var lstWD = repoAtt_WorkDay.FindBy(s => s.IsDelete == null && s.WorkDate >= DateStart && s.WorkDate <= DateEnd && lstProfileId.Contains(s.ProfileID)).ToList(); if (lstWD.Count > 0) { lstWorkday = lstWD.Translate<WorkdayCustom>(); } } else { lstWorkday = GetData<WorkdayCustom>(lst3ParamSE, ConstantSql.hrm_att_getdata_Workday, userLogin, ref status).ToList(); } //var lstWorkdayQuery = repoAtt_WorkDay.FindBy(m => m.IsDelete == null && m.WorkDate >= DateStart && m.WorkDate <= DateEnd); //if (lstProfileId.Count > 0) //{ // lstWorkdayQuery = lstWorkdayQuery.Where(m => lstProfileId.Contains(m.ProfileID)); //} //lstWorkday = lstWorkdayQuery.ToList().Translate<Att_WorkdayEntity>(); List<Cat_ShiftEntity> lstShift = repoCat_Shift .FindBy(s => s.IsDelete == null) .ToList() .Translate<Cat_ShiftEntity>(); List<Cat_ShiftItemEntity> lstShiftItem = repoCat_ShiftItem .FindBy(s => s.IsDelete == null) .ToList() .Translate<Cat_ShiftItemEntity>(); List<Cat_DayOffEntity> LstDayOff = repoCat_DayOff .FindBy(m => m.IsDelete == null && m.DateOff >= DateStart && m.DateOff < DateEnd) .ToList() .Translate<Cat_DayOffEntity>(); List<Cat_OvertimeTypeEntity> lstOvertimeType = repoCat_OvertimeType .FindBy(s => s.IsDelete == null).ToList() .Translate<Cat_OvertimeTypeEntity>(); string E_CANCEL = OverTimeStatus.E_CANCEL.ToString(); string E_REJECTED = OverTimeStatus.E_REJECTED.ToString(); DateTime beforeDateStart = DateStart.Date.AddDays(-1); DateTime afterDateEnd = DateEnd.Date.AddDays(2).AddMinutes(-1); // string E_HOLIDAY_HLD = HolidayType.E_HOLIDAY_HLD.ToString(); List<DateTime> lstHoliday = repoCat_DayOff .FindBy(s => s.IsDelete == null) .Select(m => m.DateOff).ToList<DateTime>(); //lstWorkday = filterData_NonAllowOT_inGrade(lstWorkday); List<Att_OvertimeEntity> lstOvertimeCal = AnalyzeOvertime(lstWorkday, lstShift, lstShiftItem, LstDayOff, lstOvertimeType, _OvertimeInfoFillterAnalyzeEntity, userLogin); if (lstOvertimeCal.Count == 0) { lstOvertimeCache = lstOvertimeCal; return table; } OvertimePermitEntity overtimePermit = getOvertimePermit(userLogin); FilterNonOvertimeByGradeConfig(lstOvertimeCal); SetNonOT(lstOvertimeCal); SetStatusOvertimeOnWorkday(lstOvertimeCal); FilterOvertimeByMaxHourPerDay(lstOvertimeCal, overtimePermit, _OvertimeInfoFillterAnalyzeEntity.MaximumOvertimeHour); RoundOT(lstOvertimeCal, userLogin); FillterAllowOvertime(context, lstOvertimeCal, overtimePermit, lstWorkday); RoundOT(lstOvertimeCal, userLogin); lstOvertimeCal = lstOvertimeCal.Where(m => m.RegisterHours != null && m.RegisterHours > 0).ToList(); SetStatusLeaveOnWorkday(lstOvertimeCal);//Set loại ngày nghỉ cho OT if (lstTypeData.Count > 0) { Expression<Func<Att_OvertimeEntity, bool>> predicate = VnResource.Helper.Linq.PredicateBuilder.False<Att_OvertimeEntity>(); if (lstTypeData.Any(m => m == ComputeLeavedayType.E_DATA_LEAVE.ToString())) { Expression<Func<Att_OvertimeEntity, bool>> predicate1 = m => m.udLeaveTypeCode != null || m.udLeaveTypeCode2 != null; predicate = predicate.Or(predicate1); //lstOvertimeCal = lstOvertimeCal.Where(m => (m.udLeaveTypeCode == null || m.udLeaveTypeCode == string.Empty) && (m.udLeaveTypeCode2 == null || m.udLeaveTypeCode2 == string.Empty)).ToList(); } if (lstTypeData.Any(m => m == ComputeLeavedayType.E_DATA_NON_LEAVE.ToString())) { Expression<Func<Att_OvertimeEntity, bool>> predicate2 = m => (m.udLeaveTypeCode == null || m.udLeaveTypeCode == string.Empty) && (m.udLeaveTypeCode2 == null || m.udLeaveTypeCode2 == string.Empty); predicate = predicate.Or(predicate2); //lstOvertimeCal = lstOvertimeCal.Where(m => m.udLeaveTypeCode != null || m.udLeaveTypeCode2 != null).ToList(); } if (lstTypeData.Any(m => m == ComputeOvertimeType.E_DATA_OT.ToString())) { Expression<Func<Att_OvertimeEntity, bool>> predicate2 = m => m.udOvertimeStatus != null; predicate = predicate.Or(predicate2); } if (lstTypeData.Any(m => m == ComputeOvertimeType.E_DATA_NON_OT.ToString())) { Expression<Func<Att_OvertimeEntity, bool>> predicate2 = m => m.udOvertimeStatus == null; predicate = predicate.Or(predicate2); } lstOvertimeCal = lstOvertimeCal.AsQueryable().Where(predicate).ToList(); } //lstOvertimeCal = lstOvertimeCal.OrderBy(m => m.WorkDate.Date).ThenBy(m => m.ProfileID).ToList(); //lstOvertimeCache = lstOvertimeCal; //BindToGrid(lstOvertimeCache); lstOvertimeCal = lstOvertimeCal .Where(m => m.udIsLimitHour == true || m.udIsLimitHourLv1 == true || m.udIsLimitHourLv2 == true) .OrderBy(m => m.WorkDate.Date) .ThenBy(m => m.ProfileID) .ToList(); //lstOvertimeCal.ForEach(s => s.IsValid = false); #region process return table foreach (var item in lstOvertimeCal) { DataRow row = table.NewRow(); Guid? orgId = lstProfile.Where(s => s.ID == item.ProfileID).FirstOrDefault().OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == orgId); 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_OvertimeEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_OvertimeEntity.FieldNames.OrgStructureCode] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_OvertimeEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_OvertimeEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty; row[Att_OvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_OvertimeEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_OvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_OvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_OvertimeEntity.FieldNames.WorkDate] = item.WorkDate; row[Att_OvertimeEntity.FieldNames.InTime] = item.InTime; row[Att_OvertimeEntity.FieldNames.OutTime] = item.OutTime; if (item.udIsLimitHour == null || item.udIsLimitHour == false) { row[Att_OvertimeEntity.FieldNames.Valid] = "Level1"; } else if (item.udIsLimitHourLv1 == null || item.udIsLimitHourLv1 == false) { row[Att_OvertimeEntity.FieldNames.Valid] = "Level2"; } else if (item.udIsLimitHourLv2 == null || item.udIsLimitHourLv2 == false) { row[Att_OvertimeEntity.FieldNames.Valid] = "Level3"; } else { row[Att_OvertimeEntity.FieldNames.Valid] = string.Empty; } if (item.OvertimeTypeID != Guid.Empty) { var OTType = lstOvertimeType.Where(s => s.ID == item.OvertimeTypeID).FirstOrDefault(); row[Att_OvertimeEntity.FieldNames.OvertimeTypeID] = item.OvertimeTypeID; row[Att_OvertimeEntity.FieldNames.OvertimeTypeName] = OTType.OvertimeTypeName; row[OTType.Code] = item.RegisterHours; row[OTType.Code + "Confirm"] = item.ApproveHours ?? 0.0; } if (item.ShiftID != Guid.Empty) { var _shift = lstShift.Where(s => s.ID == item.ShiftID).FirstOrDefault(); row[Att_OvertimeEntity.FieldNames.ShiftID] = item.ShiftID; row[Att_OvertimeEntity.FieldNames.ShiftName] = _shift.ShiftName; } if (item.ProfileID != Guid.Empty) { var temp = lstProfile.Where(s => s.ID == item.ProfileID).FirstOrDefault(); row[Att_OvertimeEntity.FieldNames.ProfileID] = item.ProfileID; row[Att_OvertimeEntity.FieldNames.ProfileName] = temp.ProfileName; row[Att_OvertimeEntity.FieldNames.CodeEmp] = temp.CodeEmp; //row[Att_ReportDetailOvertimeEntity.FieldNames.OrgStructureName] = temp.OrgStructureName; } row[Att_OvertimeEntity.FieldNames.TotalRow] = lstOvertimeCal.Count; row[Att_OvertimeEntity.FieldNames.DateExport] = DateTime.Now; row[Att_OvertimeEntity.FieldNames.AnalyseHour] = item.AnalyseHour; row[Att_OvertimeEntity.FieldNames.udHourByDate] = item.udHourByDate; row[Att_OvertimeEntity.FieldNames.udHourByWeek] = item.udHourByWeek; row[Att_OvertimeEntity.FieldNames.udHourByMonth] = item.udHourByMonth; row[Att_OvertimeEntity.FieldNames.udHourByYear] = item.udHourByYear; row[Att_OvertimeEntity.FieldNames.udLeaveTypeCode] = item.udLeaveTypeCode; row[Att_OvertimeEntity.FieldNames.udOvertimeStatus] = item.udOvertimeStatus; row[Att_OvertimeEntity.FieldNames.RegisterHours] = item.RegisterHours; row[Att_OvertimeEntity.FieldNames.ApproveHours] = item.RegisterHours; table.Rows.Add(row); } #endregion return table; } }
// Sonvo - 20140807 - sửa lại logic giống bản 7 DataTable CreateReportDetailOvertimeSchema() { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DataTable tb = new DataTable("Att_ReportDetailOvertimeEntity"); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeEmp); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.ProfileName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeOrg); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeJobtitle); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodePosition); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeBranch); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeTeam); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.CodeSection); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.BranchName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.OrgName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.TeamName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.SectionName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.DateOvertime, typeof(DateTime)); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.udInTime, typeof(DateTime)); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.udOutTime, typeof(DateTime)); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.ShiftName); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.udSubmitApproveHour); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.ReasonOT); var repoOvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var lstCodeOvertimeType = repoOvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => s.Code).Distinct().ToList(); //DataRow row = tb.NewRow(); foreach (string code in lstCodeOvertimeType) { if (!tb.Columns.Contains(code)) { tb.Columns.Add(code); //row[code] = code; } if (!tb.Columns.Contains(code + "_Confirm")) { tb.Columns.Add(code + "_Confirm"); } if (!tb.Columns.Contains(code + "_Approve")) { tb.Columns.Add(code + "_Approve"); } } tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.DateFrom, typeof(DateTime)); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.DateTo, typeof(DateTime)); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.UserExport); tb.Columns.Add(Att_ReportDetailOvertimeEntity.FieldNames.DateExport, typeof(DateTime)); //tb.Rows.Add(row); return tb; } }
public DataTable GetReportDetailOvertime(string userExport, DateTime dateStart, DateTime dateEndSearch, string ProfileName, string CodeEmp, string OrgIDString, List<Guid?> lstovertimeTypeIds, bool noDisplay0Data, bool isIncludeQuitEmp) { DataTable table = CreateReportDetailOvertimeSchema(); DateTime dateEnd = dateEndSearch.AddDays(1).AddMinutes(-1); using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); string key = OverTimeStatus.E_APPROVED.ToString(); string E_CASHOUT = MethodOption.E_CASHOUT.ToString(); var overtimesQuery = unitOfWork.CreateQueryable<Att_Overtime>(s => s.IsDelete == null && (s.RegisterHours > 0 || s.ApproveHours > 0) && s.Status == key && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.MethodPayment == null || s.MethodPayment == E_CASHOUT)); if (!string.IsNullOrEmpty(OrgIDString)) { List<Guid> OrgIDs = new List<Guid>(); List<string> OrgIDsArr = OrgIDString.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); foreach (var item in OrgIDsArr) { Guid OrgID = Guid.Empty; Guid.TryParse(item, out OrgID); if (OrgID != Guid.Empty) { OrgIDs.Add(OrgID); } } overtimesQuery = overtimesQuery.Where(m => m.OrgStructureID != null && OrgIDs.Contains(m.OrgStructureID.Value)); } if (!string.IsNullOrEmpty(ProfileName)) { List<string> lstProfileName = ProfileName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); if (lstProfileName.Count > 1) { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstProfileName.Contains(m.Hre_Profile.ProfileName)); } else { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.ProfileName != null && (m.Hre_Profile.ProfileName.Contains(ProfileName))); } } if (!string.IsNullOrEmpty(CodeEmp)) { List<string> lstCodeEmp = CodeEmp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>(); if (lstCodeEmp.Count > 1) { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstCodeEmp.Contains(m.Hre_Profile.CodeEmp)); } else { overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.CodeEmp != null && (m.Hre_Profile.CodeEmp.Contains(CodeEmp))); } } if (noDisplay0Data) { overtimesQuery = overtimesQuery.Where(s => s.ApproveHours > 0); } if (lstovertimeTypeIds != null && lstovertimeTypeIds[0] != null && lstovertimeTypeIds[0] != Guid.Empty) { overtimesQuery = overtimesQuery.Where(s => lstovertimeTypeIds.Contains(s.OvertimeTypeID)); } var overtimes = overtimesQuery.Select(s => new { s.ProfileID, s.WorkDate, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours, s.AnalyseHour, s.ConfirmHours, s.OrgStructureID, s.ReasonOT }).ToList(); var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null & dateStart <= s.WorkDate && s.WorkDate <= dateEnd) .Select(s => new { s.ProfileID, s.ShiftID, s.InTime1, s.OutTime1, s.WorkDate }).ToList(); var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_Shift = new Cat_ShiftRepository(unitOfWork); var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList(); if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } foreach (var profile in profiles) { for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1)) { var overtimeProfiles = overtimes.Where(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID).ToList(); if (overtimeProfiles.Count == 0) continue; var lastOvertimeByProfile = overtimeProfiles.OrderByDescending(m => m.WorkDate).FirstOrDefault(); DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; if (lastOvertimeByProfile != null) orgId = lastOvertimeByProfile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportDetailOvertimeEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportDetailOvertimeEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportDetailOvertimeEntity.FieldNames.ReasonOT] = lastOvertimeByProfile.ReasonOT; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate == date); var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID); row[Att_ReportDetailOvertimeEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; row[Att_ReportDetailOvertimeEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportDetailOvertimeEntity.FieldNames.DateTo] = dateEnd; row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportDetailOvertimeEntity.FieldNames.UserExport] = userExport; row[Att_ReportDetailOvertimeEntity.FieldNames.udSubmitApproveHour] = overtimeProfiles.Where(m => m.AnalyseHour != null).Sum(m => m.AnalyseHour); row[Att_ReportDetailOvertimeEntity.FieldNames.DateOvertime] = date; if (workDay != null && workDay.InTime1 != null) { //row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value.ToString("HH:mm:ss"); row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value; } if (workDay != null && workDay.OutTime1 != null) { //row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value.ToString("HH:mm:ss"); row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value; } if (shift != null) { row[Att_ReportDetailOvertimeEntity.FieldNames.ShiftName] = shift.ShiftName; } foreach (var item in overtimeTypes) { var sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID).Sum(s => s.RegisterHours); row[item.Code] = sum > 0 ? (object)sum : DBNull.Value; sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ApproveHours > 0).Sum(s => s.ApproveHours.Value); row[item.Code + "_Approve"] = sum > 0 ? (object)sum : DBNull.Value; sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ConfirmHours != null && s.ConfirmHours > 0).Sum(s => s.ConfirmHours); row[item.Code + "_Confirm"] = sum > 0 ? (object)sum : DBNull.Value; } table.Rows.Add(row); } } var configs = new Dictionary<string, Dictionary<string, object>>(); var config = new Dictionary<string, object>(); var configtime = new Dictionary<string, object>(); var configwidthCode = new Dictionary<string, object>(); var configwidthName = new Dictionary<string, object>(); var configwidthshift = new Dictionary<string, object>(); var configwidthudSubmitApproveHour = new Dictionary<string, object>(); configwidthCode.Add("width", 80); configs.Add("CodeBranch", configwidthCode); configs.Add("CodeOrg", configwidthCode); configs.Add("CodeTeam", configwidthCode); configs.Add("CodeSection", configwidthCode); configs.Add("CodeJobtitle", configwidthCode); configs.Add("CodePosition", configwidthCode); configwidthName.Add("width", 110); configs.Add("BranchName", configwidthName); configs.Add("OrgName", configwidthName); configs.Add("TeamName", configwidthName); configs.Add("SectionName", configwidthName); configwidthudSubmitApproveHour.Add("width", 120); configs.Add("udSubmitApproveHour", configwidthudSubmitApproveHour); configwidthshift.Add("width", 90); configs.Add("DateOvertime", configwidthshift); configs.Add("ShiftName", configwidthshift); config.Add("hidden", true); configs.Add("DateFrom", config); configs.Add("DateTo", config); configs.Add("UserExport", config); configs.Add("DateExport", config); configtime.Add("width", 80); configtime.Add("format", "{0:HH:mm:ss}"); configs.Add("udOutTime", configtime); configs.Add("udInTime", configtime); // return table.ConfigDatatable(); return table.ConfigTable(configs); } }
public DataTable 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 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 DataTable GetReportStatisticsOvertimeByYear(int Year, List<Hre_ProfileEntity> profiles, List<Guid?> lstOvertimeTypeIds, bool isNotAllowZero, bool isIncludeQuitEmp, string userExport, string UserLogin) { DataTable table = CreateReportStatisticsOvertimeByYearSchema(); using (var context = new VnrHrmDataContext()) { //int year = Year.Year; int year = Year; DateTime dateFrom = new DateTime(Year, 1, 1); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DateTime dateStart = new DateTime(year, 1, 1); DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)).AddDays(1).AddMinutes(-1); //DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)); string key = OverTimeStatus.E_APPROVED.ToString(); // var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork); // var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork); List<object> para = new List<object>(); para.AddRange(new object[3]); para[0] = null; para[1] = dateStart; para[2] = dateEnd; var basevices = new BaseService(); string status = string.Empty; var overtimes = basevices.GetData<Att_OvertimeEntity>(para, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList(); //var overtimes = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) // .Select(s => new { s.ProfileID, s.ApproveHours, s.WorkDate, s.OvertimeTypeID }).ToList(); if (isNotAllowZero) { overtimes = overtimes.Where(s => s.ApproveHours > 0).ToList(); } var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); // var repoHre_Profile = new Hre_ProfileRepository(unitOfWork); //var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)) // .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); //Guid TypeCoLeaveID = EntityService.CreateQueryable<Cat_LeaveDayType>(false, GuidContext, Guid.Empty, m => m.IsTimeOffInLieu == true).Select(m => m.ID).FirstOrDefault(); Guid TypeCoLeaveID = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null && s.IsTimeOffInLieu == true).Select(s => s.ID).FirstOrDefault(); var lstLeaveDay = basevices.GetData<Att_LeaveDayEntity>(para, ConstantSql.hrm_att_getdata_LeaveDay, UserLogin, ref status).Where(s => s.LeaveDayTypeID == TypeCoLeaveID && profileIds.Contains(s.ProfileID)).ToList(); //var lstLeaveDay = EntityService.CreateQueryable<Att_LeaveDay>(false, GuidContext, Guid.Empty, m => m.LeaveDayTypeID == TypeCoLeaveID && m.DateStart <= dateEnd && m.DateEnd >= dateStart && profileIds.Contains(m.ProfileID)) // .Select(m => new { m.ID, m.ProfileID, m.LeaveHours, m.TotalDuration }).Execute(); // var repoTimeOffInLieu = new Att_TimeOffInLieuRepository(unitOfWork); // var timeOfInLieus = repoTimeOffInLieu.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID) && s.OvertimeID != null && dateStart <= s.Date && s.Date <= dateEnd) //.Select(s => new { s.AccumulateLeaves, s.ProfileID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null && s.IsDelete == null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code }).Distinct().ToList(); if (lstOvertimeTypeIds != null && lstOvertimeTypeIds[0] != Guid.Empty && lstOvertimeTypeIds[0] != null) { overtimes = overtimes.Where(s => lstOvertimeTypeIds.Contains(s.OvertimeTypeID)).ToList(); profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } //if (lstProfileIDs.Any() && lstProfileIDs[0] != Guid.Empty && lstProfileIDs[0] != null) //{ // profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList(); //} if (isNotAllowZero) { profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList(); } foreach (var profile in profiles) { DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.UserExport] = userExport; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; //var timeOffInLieuProfiles = timeOfInLieus.Where(s => s.ProfileID == profile.ID).ToList(); //var sumHour = timeOffInLieuProfiles.Sum(s => s.AccumulateLeaves); //if (sumHour > 0) // row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = sumHour; var lstLeaveDayByProfile = lstLeaveDay.Where(m => m.ProfileID == profile.ID).ToList(); if (lstLeaveDayByProfile.Count > 0) { row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = lstLeaveDayByProfile.Sum(m => m.LeaveHours * (m.LeaveDays ?? 0)); } for (int i = 1; i <= 12; i++) { dateStart = new DateTime(year, i, 1); dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i)); // row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = Year; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = dateFrom; row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateTo] = dateEnd; var month = overtimes.Where(s => s.ProfileID == profile.ID && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Sum(s => s.ApproveHours); if (month > 0) row["M" + i] = month; } table.Rows.Add(row); } return table; } }
public DataTable GetReportSummaryOvertimeMonth(DateTime dateStart, DateTime dateEndSearch, string orgStructureID, List<Hre_ProfileEntity> lstProfileObjects, Guid[] lstOvertimetypeIDs, Guid[] lstShiftIDs, bool IsDisplay0Data, bool isIncludeQuitEmp, string userExport, string UserLogin) { DataTable table = CreateReportSummaryOvertimeMonthSchema(); DateTime dateEnd = dateEndSearch.AddDays(1).AddSeconds(-1); using (var context = new VnrHrmDataContext()) { BaseService basevice = new BaseService(); var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); string key = AttendanceDataStatus.E_APPROVED.ToString(); // var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork); string status = string.Empty; List<object> paraOvertime = new List<object>(); paraOvertime.AddRange(new object[3]); paraOvertime[0] = orgStructureID; paraOvertime[1] = dateStart; paraOvertime[2] = dateEnd; var overtimeDays_Actual = basevice.GetData<Att_OvertimeEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList(); string E_CASHOUT = MethodOption.E_CASHOUT.ToString(); var lstProfileIds = lstProfileObjects.Select(p => p.ID).ToList(); //var overtimeDays = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key // && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Select(s => //new { s.ProfileID, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours }).ToList(); var profileIds = overtimeDays_Actual.Select(s => s.ProfileID).Distinct().ToList(); var profiles = lstProfileObjects.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList(); var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork); var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList(); var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork); var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList(); var repoCat_Position = new Cat_PositionRepository(unitOfWork); var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork); var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList(); var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var overtimeTypeIds = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).Distinct().ToList(); // var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork); //var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd) // .Select(s => new { s.ProfileID, s.ShiftID }).ToList(); var workDays = basevice.GetData<Att_WorkdayEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Workday, UserLogin, ref status).ToList(); //if (lstProfileIds != null && lstProfileIds[0] != null && lstProfileIds[0] != Guid.Empty) //{ // profiles = profiles.Where(s => lstProfileIds.Contains(s.ID)).ToList(); //} if (lstOvertimetypeIDs != null) { overtimeDays_Actual = overtimeDays_Actual.Where(s => lstOvertimetypeIDs.Contains(s.OvertimeTypeID)).ToList(); } var overtimeDays_Remain = overtimeDays_Actual.Where(m => m.MethodPayment == E_CASHOUT).ToList(); if (lstShiftIDs != null) { workDays = workDays.Where(s => s.ShiftID.HasValue && lstShiftIDs.Contains(s.ShiftID.Value)).ToList(); profileIds = workDays.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList(); } if (!isIncludeQuitEmp) { profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList(); } List<Guid> guids = profiles.Select(s => s.ID).ToList(); overtimeDays_Actual = overtimeDays_Actual.Where(s => guids.Contains(s.ProfileID)).ToList(); if (IsDisplay0Data) { var profileIDs = overtimeDays_Actual.Select(s => s.ProfileID).ToList(); profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList(); } foreach (var profile in profiles) { DataRow row = table.NewRow(); Guid? orgId = profile.OrgStructureID; var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID); var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes); var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes); var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes); var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes); row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty; var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID); var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID); row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeEmp] = profile.CodeEmp; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.ProfileName] = profile.ProfileName; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateExport] = DateTime.Now; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateFrom] = dateStart; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateTo] = dateEnd; row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.UserExport] = userExport; foreach (var overtime in overtimeTypeIds) { if (overtime != null && !string.IsNullOrEmpty(overtime.OvertimeTypeName) && table.Columns.Contains(overtime.OvertimeTypeName)) { var sum = overtimeDays_Actual.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.RegisterHours); row[overtime.OvertimeTypeName] = sum > 0 ? (object)sum : DBNull.Value; var sum1 = overtimeDays_Remain.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.ApproveHours); row[overtime.OvertimeTypeName + "Approve"] = sum1 > 0 ? (object)sum1 : DBNull.Value; } } table.Rows.Add(row); } var configs = new Dictionary<string, Dictionary<string, object>>(); var confighidden = new Dictionary<string, object>(); var configwidthCodeEmp = new Dictionary<string, object>(); var configwidthCodeOrg = new Dictionary<string, object>(); var configwidthNameOrg = new Dictionary<string, object>(); configwidthCodeEmp.Add("width", 88); configs.Add("CodeEmp", configwidthCodeEmp); configwidthCodeOrg.Add("width", 90); configs.Add("CodeOrg", configwidthCodeOrg); configs.Add("CodeBranch", configwidthCodeOrg); configs.Add("CodeTeam", configwidthCodeOrg); configs.Add("CodePosition", configwidthCodeOrg); configs.Add("CodeJobtitle", configwidthCodeOrg); configs.Add("CodeSection", configwidthCodeOrg); configwidthNameOrg.Add("width", 110); configs.Add("BranchName", configwidthNameOrg); configs.Add("OrgName", configwidthNameOrg); configs.Add("TeamName", configwidthNameOrg); configs.Add("SectionName", configwidthNameOrg); confighidden.Add("hidden", true); configs.Add("DateFrom", confighidden); configs.Add("DateTo", confighidden); configs.Add("UserExport", confighidden); configs.Add("DateExport", confighidden); return table.ConfigTable(configs); } }
DataTable CreateReportSummaryOvertimeMonthSchema() { using (var context = new VnrHrmDataContext()) { var unitOfWork = (IUnitOfWork)(new UnitOfWork(context)); DataTable tb = new DataTable("Att_ReportSummaryOvertimeMonthEntity"); // DataRow dr = tb.NewRow(); //tb.Columns.Add(ConstantDisplay.HRM_Att_Report_CodeEmp.TranslateString()); //tb.Columns.Add(ConstantDisplay.HRM_Att_Report_ProfileName.TranslateString()); //tb.Columns.Add(ConstantDisplay.HRM_Att_Report_CodeOrg.TranslateString()); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeEmp); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.ProfileName); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeOrg); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeTeam); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeSection); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeJobtitle); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodePosition); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeBranch); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.BranchName); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.OrgName); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.TeamName); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.SectionName); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateFrom, typeof(DateTime)); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateTo, typeof(DateTime)); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.UserExport); tb.Columns.Add(Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateExport, typeof(DateTime)); var repoOvertimeType = new Cat_OvertimeTypeRepository(unitOfWork); var lstCodeOvertimeType = repoOvertimeType.FindBy(s => s.Code != null).Select(s => s.OvertimeTypeName).Distinct().ToList(); foreach (string code in lstCodeOvertimeType) { if (!tb.Columns.Contains(code)) { tb.Columns.Add(code); tb.Columns.Add(code + "Approve"); } } return tb; } }