public DataTable GetReportMonthlyHourFlightLocal(Guid CutOffDurationID, string strOrgStructure, Guid[] shiftIDs, Guid[] payrollIDs, Guid[] workPlaceIDs, string codeEmp, bool isIncludeQuitEmp, bool isNotAllowZero, bool isCreateTemplate, string userExport, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportMonthlyHourFlightLocalchema(UserLogin);
if (isCreateTemplate)
{
return table.ConfigDatatable();
}
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoWorkDay = new Att_WorkDayRepository(unitOfWork);
var repoTimeSheet = new Att_TimeSheetRepository(unitOfWork);
var repoJobtype = new Cat_JobTypeRepository(unitOfWork);
var repoAtt_AttendencaTable = new CustomBaseRepository<Att_AttendanceTable>(unitOfWork);
var repoAtt_AttendanceTableItem = new CustomBaseRepository<Att_AttendanceTableItem>(unitOfWork);
var repoAtt_CutOffDuration = new CustomBaseRepository<Att_CutOffDuration>(unitOfWork);
var roleServices = new Cat_RoleServices();
var lstRole = new List<object>();
lstRole.Add(null);
lstRole.Add(null);
lstRole.Add(1);
lstRole.Add(100000000);
var listRole = roleServices.GetData<Cat_RoleEntity>(lstRole, ConstantSql.hrm_cat_sp_get_Role, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.ID, m.Code }).ToList();
var jobTypeServices = new Cat_JobTypeServices();
var lstjobType = new List<object>();
lstjobType.Add(null);
lstjobType.Add(null);
lstjobType.Add(1);
lstjobType.Add(100000000);
var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => m.Code).ToList();
List<object> lstOrgIDs = new List<object>();
lstOrgIDs.AddRange(new object[3]);
lstOrgIDs[0] = (object)strOrgStructure;
lstOrgIDs[1] = null;
lstOrgIDs[2] = codeEmp;
List<Hre_ProfileEntity> profiles = GetData<Hre_ProfileEntity>(lstOrgIDs, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
var cutoffInfo = repoAtt_CutOffDuration.FindBy(s => s.ID == CutOffDurationID).FirstOrDefault();
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
DateTime dateStart = cutoffInfo.DateStart;
DateTime dateEnd = cutoffInfo.DateEnd;
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
List<Guid> profileIds = profiles.Select(s => s.ID).ToList();
var timesheets = repoTimeSheet.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && s.Date != null && dateStart <= s.Date && s.Date <= dateEnd
&& s.IsDelete == null).Select(s => new { s.ProfileID, s.JobTypeID, s.RoleID, s.Date, s.NoHour, s.ID, s.Note }).ToList();
var jobtypes = repoJobtype.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.Code, s.RoleID }).ToList();
var workDays = repoWorkDay.FindBy(s => profileIds.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.ShiftID, s.Status }).ToList();
if (payrollIDs != null)
{
profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList();
}
if (workPlaceIDs != null)
{
profiles = profiles.Where(s => s.WorkPlaceID != null && workPlaceIDs.Contains(s.WorkPlaceID.Value)).ToList();
}
if (shiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList();
profileIds = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
profileIds = profiles.Select(s => s.ID).ToList();
var attendanceTables = repoAtt_AttendencaTable
.FindBy(s => s.IsDelete == null && s.CutOffDurationID == CutOffDurationID && profileIds.Contains(s.ProfileID))
.ToList();
List<Guid> attendanceTablesID = attendanceTables.Select(s => s.ID).ToList();
var attendanceTableItem = repoAtt_AttendanceTableItem
.FindBy(s => s.IsDelete == null && attendanceTablesID.Contains(s.AttendanceTableID))
.ToList();
if (isNotAllowZero)
{
profileIds = attendanceTables.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
profileIds = attendanceTables.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
foreach (var profile in profiles)
{
var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID);
var timeSheetByProfile = timesheets.Where(s => s.ProfileID == profile.ID).ToList();
if (timeSheetByProfile == null)
{
continue;
}
var lstjobtypebytimesheetpro = timeSheetByProfile.Select(s => s.JobTypeID).ToList();
var lstJobTypeCodeByTimeSheet = jobtypes.Where(s => lstjobtypebytimesheetpro.Contains(s.ID)).Select(s => new { s.ID, s.Code, s.RoleID }).ToList();
if (attendanceTableProfile == null)
{
continue;
}
var attendanceTableItemProfile = attendanceTableItem.Where(s => s.AttendanceTableID == attendanceTableProfile.ID).ToList();
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
if (profile.DateHire != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StartingDate] = profile.DateHire.Value;
if (profile.DateQuit != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ResignedDate] = profile.DateQuit.Value;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateFrom] = dateStart;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateTo] = dateEnd;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport] = userExport;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateExport] = DateTime.Today;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? attendanceTableProfile.StdWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? attendanceTableProfile.RealWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? attendanceTableProfile.PaidWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? attendanceTableProfile.AnlDayAvailable : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? attendanceTableProfile.NightShiftHours : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? attendanceTableProfile.LateEarlyDeductionHours : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? attendanceTableProfile.AnlDayTaken : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty;
foreach (var item in attendanceTableItemProfile)
{
if (item.LeaveTypeID != null)
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = leavedayTypes.Where(s => s.ID == item.LeaveTypeID).FirstOrDefault().Code;
}
}
foreach (var roleEntity in listRole)
{
var lstJobTypeByRoleID = lstJobTypeCodeByTimeSheet.Where(s => s.RoleID == roleEntity.ID).ToList();
foreach (var jobcode in lstJobTypeByRoleID)
{
var CodeRoleAndJobType = roleEntity.Code + "_" + jobcode.Code;
if (table.Columns.Contains(CodeRoleAndJobType))
{
double? nohourByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID && s.RoleID == roleEntity.ID).Sum(s => s.NoHour);
row[CodeRoleAndJobType] = nohourByPro;
}
}
}
table.Rows.Add(row);
}
return table.ConfigTable();
}
}
DataTable CreateReportMonthlyHourFlightLocalchema(string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
DataTable tb = new DataTable("ReportMonthlyHourFlightLocal");
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.CodeEmp);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ProfileName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PositionName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.JobTitleName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StdWorkDayCount, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.RealWorkDayCount, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PaidWorkDayCount, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayAvailable, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.NightShiftHours, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.LateEarlyDeductionHours, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken, typeof(Double));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ResignedDate, typeof(DateTime));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StartingDate, typeof(DateTime));
//tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note);
for (int i = 1; i <= 31; i++)
{
DataColumn column = new DataColumn(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + i);
column.Caption = i.ToString();
tb.Columns.Add(column);
}
#region MyRegion
var leavedayTypeServices = new Cat_LeaveDayTypeServices();
var lstObjLeavedayType = new List<object>();
lstObjLeavedayType.Add(null);
lstObjLeavedayType.Add(null);
lstObjLeavedayType.Add(1);
lstObjLeavedayType.Add(100000000);
var lstLeavedayType = leavedayTypeServices.GetData<Cat_LeaveDayTypeEntity>(lstObjLeavedayType, ConstantSql.hrm_cat_sp_get_LeaveDayType, UserLogin, ref status).Where(s => s.Code != null).Select(m => m.Code).ToList();
foreach (var item in lstLeavedayType)
{
if (!tb.Columns.Contains(item))
{
tb.Columns.Add(item);
}
}
#endregion
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchCode);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamCode);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionCode);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamName);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionName);
var jobTypeServices = new Cat_JobTypeServices();
var lstjobType = new List<object>();
lstjobType.Add(null);
lstjobType.Add(null);
lstjobType.Add(1);
lstjobType.Add(100000000);
var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.RoleID, m.Code }).ToList();
var roleServices = new Cat_RoleServices();
var lstRole = new List<object>();
lstRole.Add(null);
lstRole.Add(null);
lstRole.Add(1);
lstRole.Add(100000000);
var listRole = roleServices.GetData<Cat_RoleEntity>(lstRole, ConstantSql.hrm_cat_sp_get_Role, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.Code, m.ID }).ToList();
foreach (var roleEntity in listRole)
{
var lstJobTypeByRole = listjobType.Where(s => s.RoleID == roleEntity.ID).ToList();
foreach (var item in lstJobTypeByRole)
{
var CodeRoleAndJobType = roleEntity.Code + "_" + item.Code;
if (!tb.Columns.Contains(CodeRoleAndJobType))
{
tb.Columns.Add(CodeRoleAndJobType);
}
}
}
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateFrom, typeof(DateTime));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateTo, typeof(DateTime));
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport);
tb.Columns.Add(Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateExport, typeof(DateTime));
return tb;
}
}