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;
}
}
