/// <summary>
/// Lấy tất cả các bản ghi có thuộc tính IsDelete là null
/// Gọi GetAllCatLeaveDayType() trong Cat_LeaveDayTypeRepository
/// </summary>
/// <returns></returns>
public IQueryable <Cat_LeaveDayType> GetCatLeaveDayType()
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
return(repo.GetAllCatLeaveDayType().Where(i => i.IsDelete == null));
}
}
/// <summary>
/// Lấy tất cả các bản ghi có thuộc tính IsDelete là null
/// Gọi GetAllCatLeaveDayType() trong Cat_LeaveDayTypeRepository
/// </summary>
/// <returns></returns>
public IQueryable<Cat_LeaveDayType> GetCatLeaveDayType()
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
return repo.GetAllCatLeaveDayType().Where(i => i.IsDelete == null);
}
}
/// <summary>
/// Tìm kiếm bản ghi có theo tham số id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Cat_LeaveDayType GetByIdCatLeaveDayType(int id)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType();
catLeaveDayType = repo.GetById(id);
if (catLeaveDayType.IsDelete == true) catLeaveDayType = null;
return catLeaveDayType;
}
}
/// <summary>
/// Tìm kiếm bản ghi có theo tham số id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public Cat_LeaveDayType GetByIdCatLeaveDayType(int id)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType();
catLeaveDayType = repo.GetById(id);
if (catLeaveDayType.IsDelete == true)
{
catLeaveDayType = null;
}
return(catLeaveDayType);
}
}
/// <summary>
/// Update bản ghi
/// </summary>
/// <param name="catLeaveDayType"></param>
/// <returns></returns>
public bool UpdateCatLeaveDayType(Cat_LeaveDayType catLeaveDayType)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
try
{
repo.Edit(catLeaveDayType);
repo.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
}
/// <summary>
/// Thêm bản ghi mới
/// </summary>
/// <param name="catLeaveDayType"></param>
/// <returns></returns>
public bool AddCatLeaveDayType(Cat_LeaveDayType catLeaveDayType)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
try
{
repo.Add(catLeaveDayType);
repo.SaveChanges();
return true;
}
catch
{
return false;
}
}
}
/// <summary>
/// Update thuộc tính IsDelete là true
/// </summary>
/// <param name="catLeaveDayTypeId"></param>
/// <returns></returns>
public bool DeleteCatLeaveDayType(int catLeaveDayTypeId)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType();
catLeaveDayType = repo.GetById(catLeaveDayTypeId);
try
{
repo.Remove(catLeaveDayType);
repo.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
}
/// <summary>
/// [Tam.Le] - 2014/08/08
/// Tạo cấu trúc bảng cho hàm Load Data của att_leaveday
/// </summary>
/// <param name="request"></param>
/// <param name="otModel"></param>
/// <returns></returns>
DataTable GetSchema()
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
DataTable tb = new DataTable();
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeEmp);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.ProfileName);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeBranch);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeOrg);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeTeam);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeSection);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodeJobtitle);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.CodePosition);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.BranchName);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.OrgName);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.TeamName);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.SectionName);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Paid);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Date, typeof(DateTime));
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateFrom, typeof(DateTime));
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateTo, typeof(DateTime));
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.udInTime);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.udOutTime);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Cat_Shift);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.Status);
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.DateExport, typeof(DateTime));
tb.Columns.Add(Att_LeaveDayEntity.FieldNames.UserExport);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var codes = repoCat_LeaveDayType.FindBy(s => s.Code != null).Select(s => s.Code).Distinct().ToList<string>();
foreach (string code in codes)
{
if (!tb.Columns.Contains(code))
{
tb.Columns.Add(code);
}
}
return tb;
}
}
/// <summary>
/// [Tam.Le] - 2014/08/08
/// Hàm xử lý load dữ liệu
/// </summary>
/// <param name="request"></param>
/// <param name="otModel"></param>
/// <returns></returns>
public DataTable LoadData(DateTime dateStart, DateTime dateEnd, List<Guid> orgIDs, string ProfileName, string CodeEmp, Guid LeavedayType, out string ErrorMessages)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
ErrorMessages = string.Empty;
//bool isIncludeQuitEmp = true;
List<Guid> leavedayTypeIds = new List<Guid>();
if (LeavedayType != null)
leavedayTypeIds.Add(LeavedayType);
List<Guid> shiftIDs = new List<Guid>();
string E_WAIT_APPROVED = LeaveDayStatus.E_WAIT_APPROVED.ToString();
string E_SUBMIT = LeaveDayStatus.E_SUBMIT.ToString();
var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork);
List<Att_LeaveDay> leaveDays = repoAtt_Leaveday.FindBy(s => s.LeaveDays > 0
&& (s.Status == E_WAIT_APPROVED || s.Status == E_SUBMIT) && dateStart <= s.DateEnd && s.DateStart <= dateEnd).ToList<Att_LeaveDay>();
foreach (var item in leaveDays)
{
item.Status = E_WAIT_APPROVED;
}
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
var workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd)
.Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID))
.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PaidRate }).Distinct().ToList();
var ledvedayPaidIds = leavedayTypes.Where(s => s.PaidRate > 0).Select(s => s.ID).ToList();
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var shifts = repoCat_Shift.GetAll().ToList();
if (orgIDs != null)
{
profiles = profiles.Where(s => s.OrgStructureID != null && orgIDs.Contains(s.OrgStructureID.Value)).ToList();
}
if (shiftIDs.Count > 0)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList();
profileIds = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (leavedayTypeIds.Count > 0)
{
leaveDays = leaveDays.Where(s => leavedayTypeIds.Contains(s.LeaveDayTypeID)).ToList();
profileIds = leaveDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
//if (!isIncludeQuitEmp)
//{
// profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
//}
var startYear = new DateTime(dateStart.Year, 1, 1);
var endYear = new DateTime(dateStart.Year, 12, DateTime.DaysInMonth(dateStart.Year, 12));
var rosterStatus = RosterStatus.E_APPROVED.ToString();
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
List<Att_Roster> listRoster = repoAtt_Roster.FindBy(d =>
d.Status == rosterStatus && profileIds.Contains(d.ProfileID) && d.DateStart <= dateEnd && d.DateEnd >= startYear).ToList<Att_Roster>();
var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork);
List<Cat_DayOff> listHoliday = repoCat_DayOff.FindBy(hol => hol.DateOff >= startYear && hol.DateOff <= endYear).ToList<Cat_DayOff>();
var repoAtt_Grade = new Att_GradeRepository(unitOfWork);
var listGrade = repoAtt_Grade.FindBy(d => d.MonthStart != null && d.MonthStart <= dateEnd
&& profileIds.Contains((Guid)d.ProfileID)).Select(d => new { d.ProfileID, d.MonthStart, d.GradeAttendanceID }).ToList();
List<Guid?> listGradeID = listGrade.Select(d => d.GradeAttendanceID).ToList();
var repoCat_Grade = new Cat_GradeAttendanceRepository(unitOfWork);
List<Cat_GradeAttendance> listGradeCfg = repoCat_Grade.FindBy(d => listGradeID.Contains(d.ID)).ToList<Cat_GradeAttendance>();
List<Att_Roster> lstRosterTypeGroup = new List<Att_Roster>();
List<Att_RosterGroup> lstRosterGroup = new List<Att_RosterGroup>();
GetRosterGroup(profileIds, startYear, dateEnd, out lstRosterTypeGroup, out lstRosterGroup);
var repoHre_WorkHistory = new Hre_WorkHistoryRepository(unitOfWork);
List<Hre_WorkHistory> listWorkHistory = repoHre_WorkHistory.FindBy(d => profileIds.Contains(d.ProfileID) && d.DateEffective <= dateEnd).ToList<Hre_WorkHistory>();
var orgTypes = new List<Cat_OrgStructureType>();
var repoorgType = new Cat_OrgStructureTypeRepository(unitOfWork);
orgTypes = repoorgType.FindBy(s => s.IsDelete == null).ToList<Cat_OrgStructureType>();
DataTable table = GetSchema();
List<string> codeRejects = new List<string>();
codeRejects.Add("SICK");
codeRejects.Add("SU");
codeRejects.Add("SD");
codeRejects.Add("D");
codeRejects.Add("D");
codeRejects.Add("DP");
codeRejects.Add("PSN");
codeRejects.Add("DSP");
codeRejects.Add("M");
List<Guid> leaveDayTypeRejectIDs = leavedayTypes.Where(s => codeRejects.Contains(s.Code)).Select(s => s.ID).ToList();
foreach (var profile in profiles)
{
var grade = listGrade.Where(d => d.ProfileID == profile.ID && d.MonthStart <= dateEnd).OrderByDescending(d => d.MonthStart).FirstOrDefault();
Cat_GradeAttendance gradeCfg = listGradeCfg.FirstOrDefault(d => grade != null && d.ID == grade.GradeAttendanceID);
List<Hre_WorkHistory> listWorkHistoryByProfile = listWorkHistory.Where(d => d.ProfileID == profile.ID).ToList();
List<Att_Roster> listRosterByProfile = listRoster.Where(d => d.ProfileID == profile.ID && d.DateStart <= dateEnd && d.DateEnd >= dateStart).ToList();
var listRosterEntity = listRosterByProfile.Select(d => new Att_RosterEntity
{
ID = d.ID,
ProfileID = d.ProfileID,
RosterGroupName = d.RosterGroupName,
Type = d.Type,
Status = d.Status,
DateEnd = d.DateEnd,
DateStart = d.DateStart,
MonShiftID = d.MonShiftID,
TueShiftID = d.TueShiftID,
WedShiftID = d.WedShiftID,
ThuShiftID = d.ThuShiftID,
FriShiftID = d.FriShiftID,
SatShiftID = d.SatShiftID,
SunShiftID = d.SunShiftID,
MonShift2ID = d.MonShiftID,
TueShift2ID = d.TueShift2ID,
WedShift2ID = d.WedShift2ID,
ThuShift2ID = d.ThuShift2ID,
FriShift2ID = d.FriShift2ID,
SatShift2ID = d.SatShift2ID,
SunShift2ID = d.SunShift2ID
}).ToList();
var listRosterGroupEntity = lstRosterGroup.Select(d => new Att_RosterGroupEntity
{
ID = d.ID,
DateEnd = d.DateEnd,
DateStart = d.DateStart,
MonShiftID = d.MonShiftID,
TueShiftID = d.TueShiftID,
WedShiftID = d.WedShiftID,
ThuShiftID = d.ThuShiftID,
FriShiftID = d.FriShiftID,
SatShiftID = d.SatShiftID,
SunShiftID = d.SunShiftID,
RosterGroupName = d.RosterGroupName
}).ToList();
Dictionary<DateTime, Cat_Shift> listMonthShifts = Att_AttendanceLib.GetDailyShifts(profile.ID, dateStart, dateEnd, listRosterEntity, listRosterGroupEntity, shifts);
for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1))
{
var leavdayProfiles = leaveDays.Where(s => s.DateStart.Date <= date.Date && date.Date <= s.DateEnd.Date && s.ProfileID == profile.ID).ToList();
if (leavdayProfiles.Count > 0)
{
bool isWorkDay = gradeCfg != null && Att_WorkDayHelper.IsWorkDay(date, gradeCfg, listMonthShifts, listHoliday);
var leaveday = leavdayProfiles.FirstOrDefault(s => ledvedayPaidIds.Contains(s.LeaveDayTypeID));
if (!isWorkDay)// neu ngay do ko phai ngay di lam
{
if (leaveday != null && leaveDayTypeRejectIDs.Contains(leaveday.LeaveDayTypeID) || listHoliday.Exists(s => s.DateOff == date))// neu ngay do la ngay nghi dc xem la ko xem ca or ngay nghi
{
isWorkDay = true;
}
}
if (isWorkDay)
{
var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date);
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
row[Att_LeaveDayEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_LeaveDayEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_LeaveDayEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_LeaveDayEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_LeaveDayEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_LeaveDayEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_LeaveDayEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_LeaveDayEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_LeaveDayEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_LeaveDayEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_LeaveDayEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty;
row[Att_LeaveDayEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty;
var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID);
row[Att_LeaveDayEntity.FieldNames.Date] = date;
row[Att_LeaveDayEntity.FieldNames.DateFrom] = dateStart;
row[Att_LeaveDayEntity.FieldNames.DateTo] = dateEnd;
row[Att_LeaveDayEntity.FieldNames.DateExport] = DateTime.Now;
//row[Att_LeavedayEntity.FieldNames.UserExport] = Session[SessionObjects.UserLogin];
if (leaveday != null && leaveday.LeaveDays > 0)
{
row["Paid"] = "X";
}
if (workDay != null)
{
row[Att_LeaveDayEntity.FieldNames.Cat_Shift] = shift.ShiftName;
row[Att_LeaveDayEntity.FieldNames.udInTime] = workDay.InTime1 != null ? workDay.InTime1.Value.ToString("HH:mm") : string.Empty;
row[Att_LeaveDayEntity.FieldNames.udOutTime] = workDay.OutTime1 != null ? workDay.OutTime1.Value.ToString("HH:mm") : string.Empty;
}
foreach (var leaday in leavedayTypes)
{
var leaday1 = leavdayProfiles.FirstOrDefault(s => s.LeaveDayTypeID == leaday.ID);
if (leaday1 != null && leaday1.LeaveDays > 0)
{
row[leaday.Code] = "X";
}
}
if (leaveday != null)
{
row[Att_LeaveDayEntity.FieldNames.Status] = leaveday.Status;
}
table.Rows.Add(row);
}
}
}
}
//EntityService.SubmitChanges(GuidContext, LoginUserID.Value);
context.SaveChanges();
return table;
}
}
public DataTable ReportMealTimeDetail(List<Guid?> CarteringIDs, List<Guid?> CanteenIDS, List<Guid?> LineIDS, DateTime dateStart, DateTime dateEndSearch, List<Guid?> orgIDs)
{
using (var context = new VnrHrmDataContext())
{
DateTime dateEnd = dateEndSearch.AddDays(1).AddMilliseconds(-1);
DataTable datatable = CreateReportMealTimeDetailSchema(dateStart, dateEnd);
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoMealRecord = new Can_MealRecordRepository(unitOfWork);
var repoLine = new Can_LineRepository(unitOfWork);
var lineHDTJobIDs = repoLine.FindBy(s => s.HDTJ != null).Select(s => s.ID).ToList();
var mealRecords = repoMealRecord.FindBy(s => s.LineID != null && !lineHDTJobIDs.Contains(s.LineID.Value)
&& s.ProfileID != null && dateStart <= s.WorkDay && s.WorkDay <= dateEnd).ToList();
if (mealRecords.Count < 1)
{
return datatable;
}
var profileIds = mealRecords.Select(s => s.ProfileID.Value).Distinct().ToList();
var repoWorkDay = new Att_WorkDayRepository(unitOfWork);
var workDayProfies = repoWorkDay.FindBy(s => s.ProfileID != null && dateStart <= s.WorkDate
&& s.WorkDate <= dateEnd && profileIds.Contains(s.ProfileID)).ToList();
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoProfile.FindBy(s => profileIds.Contains(s.ID)).ToList();
var repoLeaveday = new Att_LeavedayRepository(unitOfWork);
var leavedays = repoLeaveday.FindBy(s => dateStart <= s.DateEnd
&& s.DateStart <= dateEnd && profileIds.Contains(s.ProfileID)).
Select(s => new { s.ProfileID, s.DateStart, s.DateEnd, s.LeaveDayTypeID }).ToList();
var repoleaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var leaveDayTypes = repoleaveDayType.GetAll().Select(s => new { s.ID, s.LeaveDayTypeName }).ToList();
List<Cat_OrgStructure> orgs = new List<Cat_OrgStructure>();
var repoOrg = new Cat_OrgStructureRepository(unitOfWork);
orgs = repoOrg.FindBy(s => s.Code != null).ToList();
if (orgIDs != null && orgIDs.Count > 0)
{
profiles = profiles.Where(s => s.OrgStructureID != null && orgIDs.Contains(s.OrgStructureID.Value)).ToList();
}
if (CanteenIDS != null && CanteenIDS[0] != null)
{
mealRecords = mealRecords.Where(s => s.CanteenID != null && CanteenIDS.Contains(s.CanteenID.Value)).ToList();
}
if (CarteringIDs != null && CarteringIDs[0] != null)
{
mealRecords = mealRecords.Where(s => s.CateringID != null && CarteringIDs.Contains(s.CateringID.Value)).ToList();
}
if (LineIDS != null && LineIDS[0] != null)
{
mealRecords = mealRecords.Where(s => s.LineID != null && LineIDS.Contains(s.LineID.Value)).ToList();
}
if (mealRecords.Count < 1)
{
return datatable;
}
profileIds = mealRecords.Select(s => s.ProfileID.Value).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
var orgTypes = new List<Cat_OrgStructureType>();
var repoorgType = new Cat_OrgStructureTypeRepository(unitOfWork);
orgTypes = repoorgType.FindBy(s => s.IsDelete == null).ToList<Cat_OrgStructureType>();
var repoCanteen = new Can_CanteenRepository(unitOfWork);
var repoCatering = new Can_CateringRepository(unitOfWork);
var canteens = repoCanteen.GetAll().ToList();
var caterings = repoCatering.GetAll().ToList();
var lines = repoLine.GetAll().ToList();
#region Code Cũ
//foreach (var profile in profiles)
//{
// var workdayProfileDates = workDayProfies.Where(s => s.ProfileID == profile.Id).Select(s => s.WorkDate.Date).ToList();
// for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1))
// {
// var record = mealRecords.FirstOrDefault(s => s.ProfileID == profile.Id && date == s.TimeLog.Date && !workdayProfileDates.Contains(date.Date));
// if (record != null)
// {
// DataRow row = datatable.NewRow();
// int? orgId = profile.OrgStructureID;
// var org = orgs.FirstOrDefault(s => s.Id == profile.OrgStructureID);
// var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
// var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
// var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
// var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
// row[Can_ReportMealTimeDetailEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
// row[Can_ReportMealTimeDetailEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
// row[Can_ReportMealTimeDetailEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
// row[Can_ReportMealTimeDetailEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
// row[Can_ReportMealTimeDetailEntity.FieldNames.CodeEmp] = profile.CodeEmp;
// row[Can_ReportMealTimeDetailEntity.FieldNames.ProfileName] = profile.ProfileName;
// var line = repoLine.FindBy(m => record.LineID == m.Id).FirstOrDefault();
// var canteen = repoCanteen.FindBy(m => record.CanteenID == m.Id).FirstOrDefault();
// var catering = repoCatering.FindBy(m => record.CateringID == m.Id).FirstOrDefault();
// row[Can_ReportMealTimeDetailEntity.FieldNames.CateringName] = catering != null ? catering.CateringName : null;
// row[Can_ReportMealTimeDetailEntity.FieldNames.CanteenName] = canteen != null ? canteen.CanteenName : null;
// row[Can_ReportMealTimeDetailEntity.FieldNames.LineName] = line != null ? line.LineName : null;
// row[Can_ReportMealTimeDetailEntity.FieldNames.DateFrom] = dateStart;
// row[Can_ReportMealTimeDetailEntity.FieldNames.DateTo] = dateEnd;
// row[Can_ReportMealTimeDetailEntity.FieldNames.DatePrint] = DateTime.Now;
// if (record.TimeLog != null)
// {
// row["Data" + date.Day] = record.TimeLog.ToString(ConstantFormat.HRM_Format_Grid_ShortTime);
// }
// datatable.Rows.Add(row);
// }
// }
//}
#endregion
foreach (var profile in profiles)
{
var workdayProfileDates = workDayProfies.Where(s => s.ProfileID == profile.ID && (s.FirstInTime != null || s.LastOutTime != null)).Select(s => s.WorkDate.Date).ToList();
var mealRecordProfiles = mealRecords.Where(s => s.ProfileID == profile.ID).ToList();
for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1))
{
var leaday = leavedays.FirstOrDefault(s => s.DateStart != null && s.DateEnd != null && s.ProfileID == profile.ID && s.DateStart.Date <= date.Date && date.Date <= s.DateEnd.Date);
var record = mealRecordProfiles.FirstOrDefault(s => date.Date == s.WorkDay.Value.Date && (!workdayProfileDates.Contains(s.WorkDay.Value.Date) || leaday != null));
if (record != null)
{
DataRow row = datatable.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes);
row[Can_ReportMealTimeDetailEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Can_ReportMealTimeDetailEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Can_ReportMealTimeDetailEntity.FieldNames.Date] = record.WorkDay;
row[Can_ReportMealTimeDetailEntity.FieldNames.DateFrom] = dateStart;
row[Can_ReportMealTimeDetailEntity.FieldNames.DateTo] = dateEnd;
var cate = caterings.FirstOrDefault(s => s.ID == record.CateringID);
var cateen = canteens.FirstOrDefault(s => s.ID == record.CanteenID);
var line = lines.FirstOrDefault(s => s.ID == record.LineID);
row[Can_ReportMealTimeDetailEntity.FieldNames.CateringName] = cate != null ? cate.CateringName : string.Empty; ;
row[Can_ReportMealTimeDetailEntity.FieldNames.CanteenName] = cateen != null ? cateen.CanteenName : string.Empty; ;
row[Can_ReportMealTimeDetailEntity.FieldNames.LineName] = line != null ? line.LineName : string.Empty;
if (record.TimeLog != null)
{
row["Data" + record.WorkDay.Value.Day] = record.TimeLog == null ? null : (object)record.TimeLog.Value.ToString(ConstantFormat.HRM_Format_Grid_ShortTime) ?? DBNull.Value;
}
row[Can_ReportMealTimeDetailEntity.FieldNames.DatePrint] = DateTime.Now;
if (leaday != null)
{
var leadayType = leaveDayTypes.FirstOrDefault(s => s.ID == leaday.LeaveDayTypeID);
row[Can_ReportMealTimeDetailEntity.FieldNames.LeaveDayTypeName] = leadayType != null ? leadayType.LeaveDayTypeName : string.Empty;
}
datatable.Rows.Add(row);
}
}
}
return datatable;
}
}
/// <summary>
/// Lấy Dữ Liệu BC Thống Kê Ngày Nghỉ Ốm Năm
/// </summary>
/// <returns></returns>
//public List<Att_ReportSummaryLeaveYearSickEntity> GetReportSummaryLeaveYearSick(DateTime dateYear, List<Guid> lstProfileIDs)
public DataTable GetReportSummaryLeaveYearSick(int year, List<Hre_ProfileEntity> profiles, Guid[] leavedayTypeIds, bool isNotAllowZero, string userExport)
{
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportSummaryLeaveYearSick();
bool isIncludeQuitEmp = false;
// leavedayTypeIds = leavedayTypeIds.Where(p => p != null).ToArray();
List<Att_ReportSummaryLeaveYearSickEntity> listReportSummaryLeaveYearSick = new List<Att_ReportSummaryLeaveYearSickEntity>();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
DateTime dateStart = new DateTime(year, 1, 1);
DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12));
string key = OverTimeStatus.E_APPROVED.ToString();
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var leavedayTypes = repoCat_LeaveDayType
.FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC" && s.IsDelete == null)
.Select(s => new { s.ID, s.CodeStatistic, s.PaidRate })
.Distinct()
.ToList();
var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList();
var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
leaveDays = repoAtt_LeaveDay.FindBy(s => s.IsDelete == null && leadayTypeIDs.Contains(s.LeaveDayTypeID)
&& s.LeaveHours > 0 && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
.Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
//if (lstProfileIDs != null && lstProfileIDs.Count > 0)
//{
// leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID))
// .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
//}
//else
//{
// leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
// .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
//}
//var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
// profiles = repoHre_Profile.FindBy(s => s.IsDelete == null)
// .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString();
string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString();
var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork);
var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value)
&& (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year)
.Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
List<Att_ReportSummaryLeaveYearSickEntity> lstReportSummaryLeaveYearSickEntity = new List<Att_ReportSummaryLeaveYearSickEntity>();
Att_ReportSummaryLeaveYearSickEntity reportSummaryLeaveYearSickEntity = null;
if (leavedayTypeIds != null)
{
leaveDays = leaveDays.Where(s => leavedayTypeIds.Contains(s.LeaveDayTypeID)).ToList();
profileIds = leaveDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
if (isNotAllowZero)
{
var profileIDs = leaveDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList();
}
foreach (var profile in profiles)
{
DataRow row = table.NewRow();
reportSummaryLeaveYearSickEntity = new Att_ReportSummaryLeaveYearSickEntity();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
//reportSummaryLeaveYearSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
//reportSummaryLeaveYearSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
//reportSummaryLeaveYearSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
//reportSummaryLeaveYearSickEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
//reportSummaryLeaveYearSickEntity.ProfileName = profile.ProfileName;
//reportSummaryLeaveYearSickEntity.CodeEmp = profile.CodeEmp;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.UserExport] = userExport;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.DateExport] = DateTime.Today;
//reportSummaryLeaveYearSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
//reportSummaryLeaveYearSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
//reportSummaryLeaveYearSickEntity.DateExport = DateTime.Now;
var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE);
var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE);
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TotalAllowYear] = anual != null ? anual.Month3 : 0;
row[Att_ReportSummaryLeaveYearSickEntity.FieldNames.TotalAllowSick] = anualSick != null ? anualSick.Month3 : 0;
//reportSummaryLeaveYearSickEntity.TotalAllowYear = anual != null ? anual.Month3 : 0;
//reportSummaryLeaveYearSickEntity.TotalAllowSick = anualSick != null ? anualSick.Month3 : 0;
for (int i = 1; i <= 12; i++)
{
dateStart = new DateTime(year, i, 1);
dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i));
reportSummaryLeaveYearSickEntity.DateFrom = dateStart;
reportSummaryLeaveYearSickEntity.DateTo = dateEnd;
foreach (var leaday in leavedayTypes)
{
// Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>();
// var strCodeStatistic = leaday.CodeStatistic + i;
// double leaveDayHours = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveHours / 8);
var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID && s.LeaveHours.HasValue).Sum(s => s.LeaveHours.Value / 8);
//if (leaday != null && !string.IsNullOrEmpty(leaday.CodeStatistic) && table.Columns.Contains(strCodeStatistic))
//{
//row[leaday.CodeStatistic + i] = leaveDayHours > 0 ? leaveDayHours : (double?)null;
row[leaday.CodeStatistic + i] = sum > 0 ? (object)sum : (double?)null;
//}
// reportSummaryLeaveYearSickEntity.P = leaveDayHours > 0 ? leaveDayHours : 0;
}
}
table.Rows.Add(row);
//listReportSummaryLeaveYearSick.Add(reportSummaryLeaveYearSickEntity);
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
var confighidden = new Dictionary<string, object>();
var config90 = new Dictionary<string, object>();
config90.Add("width", 90);
var config110 = new Dictionary<string, object>();
config110.Add("width", 110);
configs.Add("CodeEmp", config90);
configs.Add("BranchCode", config90);
configs.Add("DepartmentCode", config90);
configs.Add("TeamCode", config90);
configs.Add("SectionCode", config90);
configs.Add("BranchName", config110);
configs.Add("OrgName", config110);
configs.Add("TeamName", config110);
configs.Add("SectionName", config110);
configs.Add("PositionName", config110);
configs.Add("JobTitleName", config110);
confighidden.Add("hidden", true);
configs.Add("UserExport", confighidden);
configs.Add("DateExport", confighidden);
config.Add("Width", 200);
//for (int i = 1; i <= 12; i++)
//{
// config = new Dictionary<string, object>();
// configs.Add("P" + i, confighidden);
// configs.Add("SC" + i, confighidden);
// config.Add("Width", 30);
//}
config.Add("locked", true);
configs.Add("WorkDay", config);
return table.ConfigTable(configs);
}
#region code cũ
//using (var context = new VnrHrmDataContext())
//{
// List<Att_ReportSummaryLeaveYearSickEntity> listReportSummaryLeaveYearSick = new List<Att_ReportSummaryLeaveYearSickEntity>();
// var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
// //int year = dateYear.Year;
// DateTime dateStart = new DateTime(year, 1, 1);
// DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12));
// string key = OverTimeStatus.E_APPROVED.ToString();
// var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
// var leavedayTypes = repoCat_LeaveDayType
// .FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC")
// .Select(s => new { s.ID, s.CodeStatistic, s.PaidRate })
// .Distinct()
// .ToList();
// var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList();
// var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
// var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
// if (lstProfileIDs != null && lstProfileIDs.Count > 0)
// {
// leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID))
// .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
// }
// else
// {
// leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
// .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
// }
// var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
// var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
// var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID))
// .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
// string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString();
// string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString();
// var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork);
// var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year)
// .Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList();
// var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
// var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
// var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
// var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
// var repoCat_Position = new Cat_PositionRepository(unitOfWork);
// var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
// var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
// var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
// List<Att_ReportSummaryLeaveYearSickEntity> lstReportSummaryLeaveYearSickEntity = new List<Att_ReportSummaryLeaveYearSickEntity>();
// Att_ReportSummaryLeaveYearSickEntity reportSummaryLeaveYearSickEntity = null;
// foreach (var profile in profiles)
// {
// reportSummaryLeaveYearSickEntity = new Att_ReportSummaryLeaveYearSickEntity();
// Guid? orgId = profile.OrgStructureID;
// var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
// var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
// var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
// var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
// var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
// reportSummaryLeaveYearSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
// reportSummaryLeaveYearSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
// reportSummaryLeaveYearSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
// reportSummaryLeaveYearSickEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
// var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
// var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
// reportSummaryLeaveYearSickEntity.ProfileName = profile.ProfileName;
// reportSummaryLeaveYearSickEntity.CodeEmp = profile.CodeEmp;
// reportSummaryLeaveYearSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
// reportSummaryLeaveYearSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
// reportSummaryLeaveYearSickEntity.DateExport = DateTime.Now;
// var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE);
// var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE);
// reportSummaryLeaveYearSickEntity.TotalAllowYear = anual != null ? anual.Month3 : 0;
// reportSummaryLeaveYearSickEntity.TotalAllowSick = anualSick != null ? anualSick.Month3 : 0;
// for (int i = 1; i <= 12; i++)
// {
// dateStart = new DateTime(year, i, 1);
// dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i));
// reportSummaryLeaveYearSickEntity.DateFrom = dateStart;
// reportSummaryLeaveYearSickEntity.DateTo = dateEnd;
// foreach (var leaday in leavedayTypes)
// {
// Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>();
// double leaveDayHours = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveHours / 8);
// }
// }
// listReportSummaryLeaveYearSick.Add(reportSummaryLeaveYearSickEntity);
// }
// return listReportSummaryLeaveYearSick;
//}
#endregion
}
/// <summary>
/// Lấy Dữ Liệu BC Chi Tiết Nghỉ Phép Ốm của nhân viên
/// </summary>
/// <returns></returns>
///
public DataTable GetReportLeaveYear(int dateYear, List<Guid?> leaveDayTypeIDs, List<Guid> lstProfileIDs, string userExport, string codeEmp)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
int year = dateYear;
DateTime dateStart = new DateTime(year, 1, 1);
DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12));
string key = OverTimeStatus.E_APPROVED.ToString();
var repoCat_Leaveday = new Cat_LeaveDayTypeRepository(unitOfWork);
var leavedayTypes = repoCat_Leaveday.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PaidRate }).Distinct().ToList();
var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList();
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd, s.LeaveDays }).ToList();
leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
.Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd, s.LeaveDays }).ToList();
leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.ProfileID)).ToList();
if (leaveDayTypeIDs[0] != null)
{
leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.LeaveDayTypeID)).ToList();
}
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
lstProfileIDs.AddRange(profileIds);
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, }).ToList();
profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList();
if (!string.IsNullOrEmpty(codeEmp))
{
char[] ext = new char[] { ';', ',' };
List<string> codeEmpSeachs = codeEmp.Split(ext, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
if (codeEmpSeachs.Count == 1)
{
string codeEmpSearch = codeEmpSeachs[0];
profiles = profiles.Where(hr => hr.CodeEmp == codeEmpSearch).ToList();
}
else
{
profiles = profiles.Where(hr => codeEmpSeachs.Contains(hr.CodeEmp)).ToList();
}
}
string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString();
string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString();
var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork);
var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year)
.Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
var repoAtt_Pregnancy = new Att_PregnancyRepository(unitOfWork);
var pregnancys = repoAtt_Pregnancy.FindBy(s => s.DateStart != null && s.DateEnd != null && dateStart <= s.DateEnd && s.DateStart <= dateEnd && profileIds.Contains(s.ProfileID))
.Select(s => new { s.ProfileID, s.DateStart, s.DateEnd }).ToList();
Dictionary<string, string> dicSchemma = new Dictionary<string, string>();
DataTable tb = GetSchema(dateStart, dateEnd, out dicSchemma);
int stt = 0;
foreach (var profile in profiles)
{
stt++;
DataRow dr = tb.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
dr[Att_ReportSickLeaveEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
dr[Att_ReportSickLeaveEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Att_ReportSickLeaveEntity.FieldNames.ProfileName] = profile.ProfileName;
dr[Att_ReportSickLeaveEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty;
dr[Att_ReportSickLeaveEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty;
var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE);
var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE);
if (dicSchemma.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.SumANL))
{
dr[dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL]] = anual != null ? (object)anual.Month3 : DBNull.Value;
string SttOfData = dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL].Substring(4, dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumANL].Length - 4);
if (stt == 1)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total P";
}
else if (stt == 2)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total SC";
}
}
if (dicSchemma.ContainsKey(Att_ReportSickLeaveEntity.FieldNames.SumSICK))
{
dr[dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK]] = anualSick != null ? (object)anualSick.Month3 : DBNull.Value;
string SttOfData = dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK].Substring(4, dicSchemma[Att_ReportSickLeaveEntity.FieldNames.SumSICK].Length - 4);
if (stt == 1)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total P";
}
else if (stt == 2)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = "Total SC";
}
}
//var pregnancyProfiles = pregnancys.Where(s => s.ProfileID == profile.ID).ToList();
//for (DateTime DateCheck = dateStart; DateCheck <= dateEnd; DateCheck = DateCheck.AddMonths(1))
//{
// int dayPregancy = 0;
// foreach (var pregnancyProfile in pregnancyProfiles)
// {
// for (DateTime date = pregnancyProfile.DateStart.Value; date < pregnancyProfile.DateEnd.Value; date = date.AddDays(1))
// {
// dayPregancy += 1;
// }
// }
// foreach (var leaday in leavedayTypes)
// {
// if (dicSchemma.ContainsKey(leaday.Code + DateCheck.Month))
// {
// var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.TotalDuration);
// dr[dicSchemma[leaday.CodeStatistic + DateCheck.Month]] = sum > 0 ? (object)(sum - dayPregancy) : DBNull.Value;
// string SttOfData = dicSchemma[leaday.CodeStatistic + DateCheck.Month].Substring(4, dicSchemma[leaday.CodeStatistic + DateCheck.Month].Length - 4);
// if (stt == 1)
// {
// dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = DateCheck.ToString("MMM-yyyy");
// }
// else if (stt == 2)
// {
// dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = leaday.CodeStatistic;
// }
// }
// }
//}
//dr[Att_ReportSickLeaveEntity.FieldNames.DateFrom] = dateStart;
//dr[Att_ReportSickLeaveEntity.FieldNames.DateTo] = dateEnd;
//tb.Rows.Add(dr);
var pregnancyProfiles = pregnancys.Where(s => s.ProfileID == profile.ID).ToList();
for (DateTime DateCheck = dateStart; DateCheck <= dateEnd; DateCheck = DateCheck.AddMonths(1))
{
int dayPregancy = 0;
foreach (var pregnancyProfile in pregnancyProfiles)
{
for (DateTime date = pregnancyProfile.DateStart.Value; date < pregnancyProfile.DateEnd.Value; date = date.AddDays(1))
{
dayPregancy += 1;
}
}
foreach (var leaday in leavedayTypes)
{
if (dicSchemma.ContainsKey(leaday.Code + DateCheck.Month))
{
var sum = leaveDays.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID).Sum(s => s.LeaveDays);
dr[dicSchemma[leaday.Code + DateCheck.Month]] = sum > 0 ? (object)(sum - dayPregancy) : DBNull.Value;
string SttOfData = dicSchemma[leaday.Code + DateCheck.Month].Substring(4, dicSchemma[leaday.Code + DateCheck.Month].Length - 4);
if (stt == 1)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = DateCheck.ToString("MMM-yyyy");
}
else if (stt == 2)
{
dr[Att_ReportSickLeaveEntity.FieldNames.DataHeader + SttOfData] = leaday.Code;
}
}
}
}
dr[Att_ReportSickLeaveEntity.FieldNames.DateFrom] = dateStart;
dr[Att_ReportSickLeaveEntity.FieldNames.DateTo] = dateEnd;
dr[Att_ReportSickLeaveEntity.FieldNames.UserExport] = userExport;
dr[Att_ReportSickLeaveEntity.FieldNames.DateExport] = DateTime.Today;
tb.Rows.Add(dr);
}
return tb;
}
}
public DataTable GetReportDetailedMonthlyStay(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> profiles, string orgStructureID, bool isIncludeQuitEmp, Guid[] leaveDayTypeIDs, string codeEmp, bool isNotAllowZero, string userExport, bool IsCreateTemplate, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
var baseservice = new BaseService();
string key = OverTimeStatus.E_APPROVED.ToString();
DataTable table = CreateReportDetailedMonthlyStaySchema(leaveDayTypeIDs, dateStart, dateEnd);
if (IsCreateTemplate)
{
return table.ConfigTable();
}
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork);
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
var repoCat_LeaveDayTypeRepository = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork);
var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork);
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var repoCat_GradeAtt = new Cat_GradeAttendanceRepository(unitOfWork);
var repoHre_WorkHistory = new Hre_WorkHistoryRepository(unitOfWork);
var repoAtt_Grade = new CustomBaseRepository<Att_Grade>(unitOfWork);
List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList();
List<Att_LeaveDayEntity> leaveDays = new List<Att_LeaveDayEntity>();
List<Cat_LeaveDayType> leavedaytypes = new List<Cat_LeaveDayType>();
string status = string.Empty;
List<object> para = new List<object>();
para.AddRange(new object[3]);
para[0] = (object)orgStructureID;
para[1] = dateStart;
para[2] = dateEnd;
if (lstProfileIDs != null && lstProfileIDs.Count > 0)
{
leaveDays = baseservice.GetData<Att_LeaveDayEntity>(para, ConstantSql.hrm_att_getdata_LeaveDay, UserLogin, ref status).Where(s => s.Status == key).ToList();
}
else
{
return table.ConfigTable();
}
//[Tho.Bui] Get LeavedayR=type
if (leaveDays != null && leaveDays.Count > 0)
{
List<Guid> lstLDTinLD = leaveDays.Select(s => s.LeaveDayTypeID).Distinct().ToList();
leavedaytypes = repoCat_LeaveDayTypeRepository.FindBy(s => s.IsDelete == null && lstLDTinLD.Contains(s.ID)).ToList();
List<Guid> leavedayguid = leaveDays.Select(s => s.LeaveDayTypeID).ToList();
leavedaytypes = leavedaytypes.Where(s => leavedayguid.Contains(s.ID)).ToList();
}
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
var rosterStatus = RosterStatus.E_APPROVED.ToString();
var _rosters = new List<Att_Roster>();
foreach (var tempProfileIds in lstProfileIDs.Chunk(1000))
{
_rosters.AddRange(unitOfWork.CreateQueryable<Att_Roster>(Guid.Empty, d => tempProfileIds.Contains(d.ProfileID) && d.Status == rosterStatus && d.DateStart <= dateEnd && d.DateEnd >= dateStart).ToList());
}
var rosters = new List<Att_RosterEntity>();
if (_rosters.Count > 0)
rosters = _rosters.Translate<Att_RosterEntity>();
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
var leavdayPadidIDs = leavedayTypes.Where(s => s.PaidRate > 0).Select(s => s.ID).ToList();
List<Guid> guids = profiles.Select(s => s.ID).ToList();
leaveDays = leaveDays.Where(s => guids.Contains(s.ProfileID)).ToList();
if (leaveDayTypeIDs != null)
{
leaveDays = leaveDays.Where(s => leaveDayTypeIDs.Contains(s.LeaveDayTypeID)).ToList();
profileIds = leaveDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList();
}
//var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList();
var shifts = unitOfWork.CreateQueryable<Cat_Shift>(Guid.Empty).ToList().Translate<Cat_ShiftEntity>();
List<string> codeRejects = new List<string>();
codeRejects.Add("SICK");
codeRejects.Add("SU");
codeRejects.Add("SD");
codeRejects.Add("D");
codeRejects.Add("DP");
codeRejects.Add("PSN");
codeRejects.Add("DSP");
codeRejects.Add("M");
List<Guid> leaveDayTypeRejectIDs = leavedayTypes.Where(s => codeRejects.Contains(s.Code)).Select(s => s.ID).ToList();
if (isNotAllowZero)
{
var profileIDs = leaveDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList();
}
#region sai xong xóa
string CodeEmpTest = string.Empty;
if (CodeEmpTest != string.Empty)
{
char[] ext = new char[] { ';', ',' };
List<string> lstCodeEmpTest = CodeEmpTest.Split(ext, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
profiles = profiles.Where(m => lstCodeEmpTest.Contains(m.CodeEmp)).ToList();
}
#endregion
var leveDayIDs = leaveDays.Select(s => s.LeaveDayTypeID).ToList();
leavedayTypes = leavedayTypes.Where(s => leveDayIDs.Contains(s.ID)).ToList();
var holidayDates = repoCat_DayOff.FindBy(hol => hol.DateOff >= dateStart && hol.DateOff <= dateEnd).Select(s => s.DateOff).ToList();
holidayDates = holidayDates.Select(s => s.Date).ToList();
var lstRosterGroup = unitOfWork.CreateQueryable<Att_RosterGroup>(Guid.Empty, s => s.DateStart != null && s.DateStart >= dateStart && s.DateEnd != null && s.DateEnd <= dateEnd).ToList().Translate<Att_RosterGroupEntity>();
foreach (var profile in profiles)
{
var rosterProfiles = rosters.Where(s => s.ProfileID == profile.ID).ToList();
List<DateTime> workDayDates = new List<DateTime>();
foreach (var roster in rosterProfiles)
{
for (DateTime date = roster.DateStart; date <= roster.DateEnd; date = date.AddDays(1))
{
workDayDates.Add(date.Date);
}
}
workDayDates = workDayDates.Where(s => !holidayDates.Contains(s.Date)).ToList();
DataRow row = table.NewRow();
var leaveDay = leaveDays.FirstOrDefault(s => s.ProfileID == profile.ID);
var LeaveDayType = leavedaytypes.FirstOrDefault(s => s.ID != Guid.Empty && leaveDay != null && s.ID == leaveDay.LeaveDayTypeID);
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.CodeEmp] = profile.CodeEmp;
if (profile.E_COMPANY != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_COMPANY] = profile.E_COMPANY;
if (profile.E_BRANCH != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_BRANCH] = profile.E_BRANCH;
if (profile.E_UNIT != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_UNIT] = profile.E_UNIT;
if (profile.E_DIVISION != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_DIVISION] = profile.E_DIVISION;
if (profile.E_DEPARTMENT != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT;
if (profile.E_TEAM != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_TEAM] = profile.E_TEAM;
if (profile.E_SECTION != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.E_SECTION] = profile.E_SECTION;
if (profile.JobTitleName != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.JobTitleName] = profile.JobTitleName;
if (profile.PositionName != null)
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.PositionName] = profile.PositionName;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateFrom] = dateStart;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateTo] = dateEnd;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.UserExport] = userExport;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.DateExport] = DateTime.Today;
var leadayProfiles = leaveDays.Where(s => s.ProfileID == profile.ID).ToList();
var ledayeTyepIDs = leadayProfiles.Select(s => s.LeaveDayTypeID).ToList();
double sumPaid = 0;
foreach (var leadayType in leavedayTypes.Where(s => ledayeTyepIDs.Contains(s.ID)).ToList())
{
double sum = 0;
var leavdayProfiles = leadayProfiles.Where(s => s.LeaveDayTypeID == leadayType.ID).ToList();
foreach (var leavdayProfile in leavdayProfiles)
{
var DateEnd = leavdayProfile.DateEnd;
if (DateEnd > dateEnd)
{
DateEnd = dateEnd;
}
for (DateTime date = leavdayProfile.DateStart; date <= DateEnd; date = date.AddDays(1))
{
bool isWorkDay = workDayDates.Contains(date);
if (!isWorkDay)// neu ngay do ko phai ngay di lam
{
if (leaveDayTypeRejectIDs.Contains(leavdayProfile.LeaveDayTypeID))// neu ngay do la ngay nghi dc xem la ko xem ca or ngay nghi
{
isWorkDay = true;
}
}
if (isWorkDay)
{
if (leavdayProfile.Duration > 0)
{
//sum++;
sum += leavdayProfile.Duration;
}
if (leavdayPadidIDs.Contains(leavdayProfile.LeaveDayTypeID))
{
sumPaid += leadayType.PaidRate;
//sumPaid++;
}
}
}
}
row[leadayType.Code] = sum > 0 ? (object)sum : DBNull.Value;
row[Att_ReportDetailedMonthlyStayEntity.FieldNames.Paid] = sumPaid > 0 ? (object)sumPaid : DBNull.Value;
}
#region tinh so gio nghi tung ngay cua nhan vien
var leavdayByProfile = leadayProfiles.Where(s => s.ProfileID == profile.ID).ToList();
Dictionary<DateTime, List<Guid?>> tempShiftByDate = new Dictionary<DateTime, List<Guid?>>();
tempShiftByDate = Att_AttendanceLib.GetDailyShifts(dateStart, dateEnd, profile.ID, rosterProfiles, lstRosterGroup);
foreach (var leavdayProfile in leavdayByProfile)
{
for (DateTime date = dateStart; date < dateEnd; date = date.AddDays(1))
{
bool isWorkDay = workDayDates.Contains(date);
if (isWorkDay)
{
//lay ca lam viec cua nhan vien
if (tempShiftByDate != null)
{
var lstShiftID = tempShiftByDate.Where(s => s.Key == date).SelectMany(s => s.Value).ToList();
if (lstShiftID.Count > 0)
{
var lstShiftProfileByDate = shifts.Where(s => s.WorkHours != null && lstShiftID.Contains(s.ID)).ToList();
double? _tempWorkHour = 0;
_tempWorkHour = lstShiftProfileByDate.Sum(s => s.WorkHours);
if (_tempWorkHour > 0)
{
row["Day" + date.Day.ToString()] = _tempWorkHour;
}
}
}
}
}
}
#endregion
table.Rows.Add(row);
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
var confighidden = new Dictionary<string, object>();
confighidden.Add("hidden", true);
//var configdate = new Dictionary<string, object>();
//configdate.Add("format", "{0:dd/MM/yyyy}");
config.Add("width", 100);
//config.Add("locked", true);
if (!configs.ContainsKey("CodeEmp"))
configs.Add("CodeEmp", config);
if (!configs.ContainsKey("DepartmentCode"))
configs.Add("DepartmentCode", config);
if (!configs.ContainsKey("PositionName"))
configs.Add("PositionName", config);
if (!configs.ContainsKey("JobTitleName"))
configs.Add("JobTitleName", config);
if (!configs.ContainsKey("Paid"))
configs.Add("Paid", config);
if (!configs.ContainsKey("BranchCode"))
configs.Add("BranchCode", config);
if (!configs.ContainsKey("TeamCode"))
configs.Add("TeamCode", config);
if (!configs.ContainsKey("SectionCode"))
configs.Add("SectionCode", config);
if (!configs.ContainsKey("OrgName"))
configs.Add("OrgName", config);
if (!configs.ContainsKey("TeamName"))
configs.Add("TeamName", config);
if (!configs.ContainsKey("SectionName"))
configs.Add("SectionName", config);
if (!configs.ContainsKey("BranchName"))
configs.Add("BranchName", config);
if (!configs.ContainsKey("DateFrom"))
configs.Add("DateFrom", confighidden);
if (!configs.ContainsKey("DateTo"))
configs.Add("DateTo", confighidden);
if (!configs.ContainsKey("UserExport"))
configs.Add("UserExport", confighidden);
if (!configs.ContainsKey("DateExport"))
configs.Add("DateExport", confighidden);
foreach (var leavdayType in leavedayTypes)
{
if (!configs.ContainsKey(leavdayType.Code))
configs.Add(leavdayType.Code, config);
}
return table.ConfigTable(configs);
}
}
public DataTable GetReportGeneralMonthlyAttendance(DateTime? dateStart, DateTime DateTo, string strOrgStructure, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, Guid[] payrollIDs, string employeeStatus, string groupByType, bool ExcludeIfWorkingDayIsZero, bool isShowOTHourRow, string userExport, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
var basesevise = new BaseService();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var repoAtt_Att_LeaveDay = new CustomBaseRepository<Att_LeaveDay>(unitOfWork);
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork);
var repoWorkDay = new Att_WorkDayRepository(unitOfWork);
var repoAtt_Grade = new CustomBaseRepository<Att_Grade>(unitOfWork);
var repoAtt_AttendencaTable = new Att_AttendanceTableRepository(unitOfWork);
var repoAtt_AttendanceItem = new Att_AttendanceTableItemRepository(unitOfWork);
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var repoAtt_CutOffDuration = new Att_CutOffDurationRepository(unitOfWork);
// var monthDuration = dateStart.ToString();
DataTable table = CreateReportGeneralMonthlyAttendanceSchema(UserLogin);
dateStart = new DateTime(dateStart.Value.Year, dateStart.Value.Month, 1);
// Guid? durationID = monthDuration != null ? (Guid?)(object)monthDuration : null;
DateTime monthEnd = new DateTime(dateStart.Value.Year, dateStart.Value.Month, DateTime.DaysInMonth(dateStart.Value.Year, dateStart.Value.Month));
// profiles = profiles.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.PayrollGroupID }).ToList();
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
var overtimTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).ToList();
List<Guid> profileIds = profiles.Select(s => s.ID).ToList();
DateTime dateFrom = dateStart.Value.Date.AddDays(1 - dateStart.Value.Day);
DateTime dateTo = dateFrom.Date.AddDays(1 - dateFrom.Day).AddMonths(1).AddSeconds(-1);
string status = string.Empty;
List<object> para = new List<object>();
para.AddRange(new object[3]);
para[0] = strOrgStructure;
para[1] = dateFrom;
para[2] = dateTo;
var workDays = basesevise.GetData<Att_WorkdayEntity>(para, ConstantSql.hrm_att_getdata_Workday, UserLogin, ref status).ToList();
string groupBy = groupByType != null ? groupByType.ToString() : string.Empty;
#region DataInfo
List<object> paratables = new List<object>();
paratables.AddRange(new object[7]);
paratables[3] = dateFrom;
paratables[4] = dateTo;
paratables[5] = 1;
paratables[6] = Int32.MaxValue - 1;
var tableQueryable = basesevise.GetData<Att_AttendanceTableEntity>(paratables, ConstantSql.hrm_att_sp_get_AttendanceTable, UserLogin, ref status).ToList();
List<object> paratable = new List<object>();
paratable.AddRange(new object[4]);
paratable[0] = dateFrom;
paratable[1] = dateTo;
paratable[2] = 1;
paratable[3] = Int32.MaxValue - 1;
var itemQueryable = basesevise.GetData<Att_AttendanceTableItemEntity>(paratable, ConstantSql.hrm_att_sp_getdata_AttendanceTableItem, UserLogin, ref status).ToList();
#region durationID
//if (durationID != null && durationID != Guid.Empty)
//{
// var cutOffDuration = repoAtt_CutOffDuration.FindBy(
// d => d.ID == durationID).Select(d => new { d.DateStart, d.DateEnd }).FirstOrDefault();
// if (cutOffDuration != null)
// {
// dateFrom = cutOffDuration.DateStart;
// dateTo = cutOffDuration.DateEnd;
// }
// tableQueryable = tableQueryable.Where(d => d.CutOffDurationID == durationID).ToList();
//}
//else
//{
// tableQueryable = tableQueryable.Where(d => d.MonthYear == dateFrom).ToList();
// }
#endregion
tableQueryable = tableQueryable.Where(d => d.MonthYear == dateFrom).ToList();
#endregion
if (payrollIDs != null)
{
profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList();
}
if (employeeStatus != null)
{
if (employeeStatus == StatusEmployee.E_WORKING.ToString())
{
profiles = profiles.Where(pro => (pro.DateQuit == null || pro.DateQuit >= dateTo) && pro.DateHire < dateFrom).ToList();
}
else if (employeeStatus == StatusEmployee.E_NEWEMPLOYEE.ToString())
{
profiles = profiles.Where(pro => pro.DateHire <= dateTo && pro.DateHire >= dateFrom).ToList();
}
else if (employeeStatus == StatusEmployee.E_STOPWORKING.ToString())
{
profiles = profiles.Where(pro => pro.DateQuit != null && pro.DateQuit.Value <= dateTo && pro.DateQuit.Value >= dateFrom).ToList();
}
else if (employeeStatus == StatusEmployee.E_WORKINGANDNEW.ToString())
{
profiles = profiles.Where(pro => pro.DateQuit == null || pro.DateQuit >= dateTo).ToList();
}
}
profileIds = profiles.Select(d => d.ID).ToList<Guid>();
tableQueryable = tableQueryable.Where(d => profileIds.Contains(d.ProfileID)).ToList();
var listAttendanceTable = tableQueryable
.Select(d => new
{
d.ID,
d.ProfileID,
d.AnlDayAvailable,
d.StdWorkDayCount,
d.RealWorkDayCount,
d.LateEarlyDeductionHours,
d.PaidWorkDayCount,
d.AnlDayTaken,
d.Overtime1Hours,
d.Overtime2Hours,
d.Overtime3Hours,
d.Overtime4Hours,
d.Overtime5Hours,
d.Overtime6Hours,
d.Overtime1Type,
d.Overtime2Type,
d.Overtime3Type,
d.Overtime4Type,
d.Overtime5Type,
d.Overtime6Type,
d.LeaveDay1Hours,
d.LeaveDay2Hours,
d.LeaveDay3Hours,
d.LeaveDay4Hours,
d.NightShiftHours,
d.LeaveDay1Type,
d.LeaveDay2Type,
d.LeaveDay3Type,
d.LeaveDay4Type,
}).ToList();
profileIds = listAttendanceTable.Select(s => s.ProfileID).ToList();
List<Guid> listAttendanceTableID = listAttendanceTable.Select(d => d.ID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
var listProfileInfo = profiles.Select(d => new
{
d.ID,
d.CodeEmp,
d.ProfileName,
d.DateHire,
d.DateQuit,
d.LaborType,
d.WorkingPlace,
d.OrgStructureID,
d.WorkPlaceName,
d.PositionName,
d.JobTitleName,
d.EmployeeTypeName,
d.OrgStructureName,
}).ToList();
itemQueryable = itemQueryable.Where(d => listAttendanceTableID.Contains(d.AttendanceTableID)
&& d.WorkDate >= dateFrom && d.WorkDate <= dateTo).ToList();
var listAttendanceTableItem = itemQueryable.Select(d => new
{
d.AttendanceTableID,
d.WorkDate,
d.OvertimeHours,
d.ExtraOvertimeHours,
d.ExtraOvertimeHours2,
d.ExtraOvertimeHours3,
d.WorkPaidHours,
d.NightShiftHours,
d.ShiftID,
d.WorkHourFirstHaftShift,
d.WorkHourLastHaftShift,
d.LeaveTypeID,
d.AvailableHours,
}).ToList();
var listLeaveDay = repoAtt_Att_LeaveDay.FindBy(d => profileIds.Contains(d.ProfileID)
&& dateFrom <= d.DateStart && d.DateEnd <= dateTo).Select(d => new { d.ProfileID, d.Comment }).ToList();
var listGrade = repoAtt_Grade.FindBy(d => profileIds.Contains(d.ProfileID.Value) && d.IsDelete == null).OrderByDescending(d => d.MonthStart).Select(d =>
new { d.ProfileID, d.MonthStart, d.Cat_GradeAttendance.HourOnWorkDate }).ToList();
var lstShift = repoCat_Shift.FindBy(s => s.IsDelete == null).Select(d => new { d.ID, d.IsDoubleShift, d.Code, d.FirstShiftID, d.LastShiftID }).ToList();
DataTable table_lstResult = new DataTable();
#region bang 7 dung store ben sql
table_lstResult.Columns.Add("ProfileID");
for (int i = 1; i <= 31; i++)
{
table_lstResult.Columns.Add("Data" + i);
}
//lay du lieu bang cat_shift
// var lst_CatShift = repoCat_Shift.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.IsDoubleShift, s.Code, s.FirstShiftID, s.LastShiftID }).ToList();
var lst_CatShift = lstShift.ToList();
//var lstAttendanceTable = repoAtt_AttendencaTable.FindBy(s => s.IsDelete == null && s.MonthYear == dateFrom && profileIds.Contains(s.ProfileID)).Select(s => new { s.ID, s.ProfileID }).ToList();
var lstAttendanceTable = listAttendanceTable.ToList();
var lstAttendanceTableID = lstAttendanceTable.Select(s => s.ID).Distinct().ToList();
//var lstAttendanceTableItem = repoAtt_AttendanceItem.FindBy(s => s.IsDelete == null && lstAttendanceTableID.Contains(s.AttendanceTableID))
// .Select(s => new {s.ID,s.AttendanceTableID,s.LeaveTypeID,s.WorkPaidHours,s.AvailableHours,s.ShiftID,s.WorkHourFirstHaftShift,s.WorkHourLastHaftShift,s.WorkDate }).ToList();
var lstAttendanceTableItem = listAttendanceTableItem.ToList();
int countId = lstAttendanceTableID.Count;
// DataRow rowlstResult = table_lstResult.NewRow();
var lstCat_LeaDayType = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null).ToList();
//while(countId >0)
//{
for (int i = countId; i > 0; i--)
{
// }
var AttendanceID = lstAttendanceTable.Select(s => s.ID).FirstOrDefault();
var ProfileID = lstAttendanceTable.Select(s => s.ProfileID).FirstOrDefault();
lstAttendanceTable = lstAttendanceTable.Where(s => s.ID != AttendanceID).ToList();
DataRow rowlstResult = table_lstResult.NewRow();
rowlstResult["ProfileID"] = ProfileID;
//int Numday = 1;
//while(Numday <=31)
//{
for (int Numday = 1; Numday <= 31; Numday++)
{
// }
var tab_AttendanceTableItem = lstAttendanceTableItem.Where(s => s.AttendanceTableID == AttendanceID && s.WorkDate.Day == Numday)
.Select(s => new { s.LeaveTypeID, s.WorkPaidHours, s.AvailableHours, s.ShiftID, s.WorkHourFirstHaftShift, s.WorkHourLastHaftShift }).ToList();
var leaveTypeID = tab_AttendanceTableItem.Select(s => s.LeaveTypeID).FirstOrDefault();
double workPaidHour = tab_AttendanceTableItem.Select(s => s.WorkPaidHours).FirstOrDefault();
double available = tab_AttendanceTableItem.Select(s => s.AvailableHours).FirstOrDefault();
available = 8;
var shiftID = tab_AttendanceTableItem.Select(s => s.ShiftID).FirstOrDefault();
var isDoubleShift = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.IsDoubleShift).FirstOrDefault();
double? workHourFirstHaftShift = tab_AttendanceTableItem.Select(s => s.WorkHourFirstHaftShift).FirstOrDefault();
double? workHourLastHaftShift = tab_AttendanceTableItem.Select(s => s.WorkHourLastHaftShift).FirstOrDefault();
string result = string.Empty;
if (leaveTypeID != null)
{
result = lstCat_LeaDayType.Where(s => s.ID == leaveTypeID).Select(s => s.CodeStatistic).ToString();
}
else
{
if (isDoubleShift == true)
{
if (available == workHourFirstHaftShift)
{
var firstShiftID = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.FirstShiftID).FirstOrDefault();
result = lst_CatShift.Where(s => s.ID == firstShiftID).Select(s => s.Code).FirstOrDefault();
}
else
{
result = workHourFirstHaftShift.ToString();
}
if (available == workHourLastHaftShift)
{
var lastShiftID = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.LastShiftID).FirstOrDefault();
result = result + "-" + lst_CatShift.Where(s => s.ID == lastShiftID).Select(s => s.Code).FirstOrDefault();
}
else
{
result = result + "-" + workHourLastHaftShift.ToString();
}
}
else
{
if (available == workPaidHour)
{
result = lst_CatShift.Where(s => s.ID == shiftID).Select(s => s.Code).FirstOrDefault();
}
else
{
result = workPaidHour.ToString();
}
}
}
rowlstResult["Data" + Numday] = result;
//Numday++;
}
table_lstResult.Rows.Add(rowlstResult);
//countId--;
}
#endregion
DateTime DateMin = DateTime.MinValue;
DateTime DateMax = DateTime.MinValue;
DateTime? DateS = tableQueryable.Select(m => m.DateStart).FirstOrDefault();
DateTime? DateE = tableQueryable.Select(m => m.DateEnd).FirstOrDefault();
if (DateS != null)
DateMin = DateS.Value;
if (DateE != null)
DateMax = DateE.Value;
foreach (var profile in profiles)
{
DataRow row = table.NewRow();
var attendanceTable = listAttendanceTable.Where(s => s.ProfileID == profile.ID).FirstOrDefault();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
//row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
// row[Att_ReportMonthlyTimeSheetEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
if (profile.DateHire != null)
{
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateHire] = profile.DateHire.Value;
}
if (profile.DateQuit != null)
{
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateQuit] = profile.DateQuit.Value;
}
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.UserExport] = userExport;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.DateExport] = DateTime.Today;
var gradeByProfile = listGrade.Where(d => d.ProfileID == attendanceTable.ProfileID).OrderByDescending(d => d.MonthStart).FirstOrDefault();
var listAttendanceTableItemByTable = listAttendanceTableItem.Where(d => d.AttendanceTableID == attendanceTable.ID).ToList();
var listLeaveDayByProfile = listLeaveDay.Where(d => d.ProfileID == attendanceTable.ProfileID).ToList();
double hourOnWorkDate = gradeByProfile != null ? double.Parse(gradeByProfile.HourOnWorkDate.ToString()) : 1;
#region Att_AttendanceTable
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.PaidWorkDayCount] = attendanceTable.PaidWorkDayCount;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.LateEarlyDeductionHours] = attendanceTable.LateEarlyDeductionHours;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.LateEarlyDeductionDays] = attendanceTable.LateEarlyDeductionHours / hourOnWorkDate;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.NightShiftHours] = attendanceTable.NightShiftHours;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.AnlDayAvailable] = attendanceTable.AnlDayAvailable;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.StandardWorkDays] = attendanceTable.StdWorkDayCount;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.RealWorkingDays] = attendanceTable.RealWorkDayCount;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.StandardWorkDaysFormula] = attendanceTable.RealWorkDayCount;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DateFrom] = DateMin;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DateTo] = DateMax;
if (attendanceTable.AnlDayTaken > 0)
{
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.ANL] = attendanceTable.AnlDayTaken;
}
#endregion
#region table_lstResult Att_AttendanceTableItem_Symbol
var dtRow = table_lstResult.Rows.OfType<DataRow>().Where(d => d["ProfileID"].GetString() == profile.ID.ToString()).FirstOrDefault();
if (dtRow != null)
{
for (DateTime dateCheck = DateMin; dateCheck < DateMax; dateCheck = dateCheck.AddDays(1))
{
string Result = string.Empty;
switch (dateCheck.Day)
{
case 1:
Result = dtRow["Data1"].ToString();
break;
case 2:
Result = dtRow["Data2"].ToString();
break;
case 3:
Result = dtRow["Data3"].ToString();
break;
case 4:
Result = dtRow["Data4"].ToString();
break;
case 5:
Result = dtRow["Data5"].ToString();
break;
case 6:
Result = dtRow["Data6"].ToString();
break;
case 7:
Result = dtRow["Data7"].ToString();
break;
case 8:
Result = dtRow["Data8"].ToString();
break;
case 9:
Result = dtRow["Data9"].ToString();
break;
case 10:
Result = dtRow["Data10"].ToString();
break;
case 11:
Result = dtRow["Data11"].ToString();
break;
case 12:
Result = dtRow["Data12"].ToString();
break;
case 13:
Result = dtRow["Data13"].ToString();
break;
case 14:
Result = dtRow["Data14"].ToString();
break;
case 15:
Result = dtRow["Data15"].ToString();
break;
case 16:
Result = dtRow["Data16"].ToString();
break;
case 17:
Result = dtRow["Data17"].ToString();
break;
case 18:
Result = dtRow["Data18"].ToString();
break;
case 19:
Result = dtRow["Data19"].ToString();
break;
case 20:
Result = dtRow["Data20"].ToString();
break;
case 21:
Result = dtRow["Data21"].ToString();
break;
case 22:
Result = dtRow["Data22"].ToString();
break;
case 23:
Result = dtRow["Data23"].ToString();
break;
case 24:
Result = dtRow["Data24"].ToString();
break;
case 25:
Result = dtRow["Data25"].ToString();
break;
case 26:
Result = dtRow["Data26"].ToString();
break;
case 27:
Result = dtRow["Data27"].ToString();
break;
case 28:
Result = dtRow["Data28"].ToString();
break;
case 29:
Result = dtRow["Data29"].ToString();
break;
case 30:
Result = dtRow["Data30"].ToString();
break;
case 31:
Result = dtRow["Data31"].ToString();
break;
}
//row["Data" + dateCheck.Day] = Result;
string day = dateCheck.Day.ToString();
row[day] = Result;
}
}
#endregion
#region att_AttendanceTableItem
int WorkPaidHourOver8 = 0;
int NightShiftOver6 = 0;
double WorkPaidHours = 0;
foreach (var attendanceTableItem in listAttendanceTableItemByTable)
{
var shift = lstShift.Where(m => m.ID == attendanceTableItem.ShiftID).FirstOrDefault();
if (shift != null && shift.IsDoubleShift == true)
{
if (attendanceTableItem.WorkHourFirstHaftShift >= 8)
{
WorkPaidHourOver8++;
}
if (attendanceTableItem.WorkHourLastHaftShift >= 8)
{
WorkPaidHourOver8++;
}
}
else if (attendanceTableItem.WorkPaidHours >= 8)
{
WorkPaidHourOver8++;
}
if (attendanceTableItem.NightShiftHours >= 6)
{
NightShiftOver6++;
}
WorkPaidHours += attendanceTableItem.WorkPaidHours;
}
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.WorkPaidHourOver8] = WorkPaidHourOver8;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.NightHourOver6] = NightShiftOver6;
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.WorkPaidHours] = WorkPaidHours;
#endregion
#region LeaveDayComment
string leaveDayComment = string.Empty;
foreach (var leaveDay in listLeaveDayByProfile)
{
if (!string.IsNullOrWhiteSpace(leaveDayComment))
{
leaveDayComment += ", ";
}
leaveDayComment += leaveDay.Comment;
}
row[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.Comment] = leaveDayComment;
#endregion
#region LeaveDay1Hours
// string LeaveDayTypeCode1 = string.Empty;
var LeaveDayTypeCode1 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay1Type).Select(s => s.Code).FirstOrDefault();
string leaveDayTypeCode = LeaveDayTypeCode1;
var LeaveDayTypeCodeStatistic1 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay1Type).Select(s => s.CodeStatistic).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic1))
{
leaveDayTypeCode = LeaveDayTypeCodeStatistic1;
}
leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(leaveDayTypeCode))
{
if (table.Columns.Contains(leaveDayTypeCode))
{
row[leaveDayTypeCode] = attendanceTable.LeaveDay1Hours / hourOnWorkDate;
}
if (table.Columns.Contains("h" + leaveDayTypeCode))
{
row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay1Hours;
}
}
#endregion
#region LeaveDay2Hours
leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay2Type).Select(s => s.Code).FirstOrDefault();
var LeaveDayTypeCodeStatistic2 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay2Type).Select(s => s.CodeStatistic).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic2))
{
leaveDayTypeCode = LeaveDayTypeCodeStatistic2;
}
leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(leaveDayTypeCode))
{
if (table.Columns.Contains(leaveDayTypeCode))
{
row[leaveDayTypeCode] = attendanceTable.LeaveDay2Hours / hourOnWorkDate;
}
if (table.Columns.Contains("h" + leaveDayTypeCode))
{
row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay2Hours;
}
}
#endregion
#region LeaveDay3Hours
leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay3Type).Select(s => s.Code).FirstOrDefault();
var LeaveDayTypeCodeStatistic3 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay3Type).Select(s => s.CodeStatistic).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic3))
{
leaveDayTypeCode = LeaveDayTypeCodeStatistic3;
}
leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(leaveDayTypeCode))
{
if (table.Columns.Contains(leaveDayTypeCode))
{
row[leaveDayTypeCode] = attendanceTable.LeaveDay3Hours / hourOnWorkDate;
}
if (table.Columns.Contains("h" + leaveDayTypeCode))
{
row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay3Hours;
}
}
#endregion
#region LeaveDay4Hours
leaveDayTypeCode = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay4Type).Select(s => s.Code).FirstOrDefault();
var LeaveDayTypeCodeStatistic4 = lstCat_LeaDayType.Where(s => s.ID == attendanceTable.LeaveDay4Type).Select(s => s.CodeStatistic).FirstOrDefault();
if (!string.IsNullOrWhiteSpace(LeaveDayTypeCodeStatistic4))
{
leaveDayTypeCode = LeaveDayTypeCodeStatistic4;
}
leaveDayTypeCode = leaveDayTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(leaveDayTypeCode))
{
if (table.Columns.Contains(leaveDayTypeCode))
{
row[leaveDayTypeCode] = attendanceTable.LeaveDay4Hours / hourOnWorkDate;
}
if (table.Columns.Contains("h" + leaveDayTypeCode))
{
row["h" + leaveDayTypeCode] = attendanceTable.LeaveDay4Hours;
}
}
#endregion
#region Cat_Overtime
var OvertimeTypeCode1 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime1Type).Select(s => s.Code).FirstOrDefault();
string overtimeTypeCode = OvertimeTypeCode1;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime1Hours;
}
}
var OvertimeTypeCode2 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime2Type).Select(s => s.Code).FirstOrDefault();
overtimeTypeCode = OvertimeTypeCode2;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime2Hours;
}
}
var OvertimeTypeCode3 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime3Type).Select(s => s.Code).FirstOrDefault();
overtimeTypeCode = OvertimeTypeCode3;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime3Hours;
}
}
var OvertimeTypeCode4 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime4Type).Select(s => s.Code).FirstOrDefault();
overtimeTypeCode = OvertimeTypeCode4;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime4Hours;
}
}
var OvertimeTypeCode5 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime5Type).Select(s => s.Code).FirstOrDefault();
overtimeTypeCode = OvertimeTypeCode5;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime5Hours;
}
}
var OvertimeTypeCode6 = overtimTypes.Where(s => s.ID == attendanceTable.Overtime6Type).Select(s => s.Code).FirstOrDefault();
overtimeTypeCode = OvertimeTypeCode6;
overtimeTypeCode = overtimeTypeCode.GetString().Replace('.', '_');
if (!string.IsNullOrWhiteSpace(overtimeTypeCode))
{
if (table.Columns.Contains(overtimeTypeCode))
{
row[overtimeTypeCode] = attendanceTable.Overtime6Hours;
}
}
#endregion
table.Rows.Add(row);
#region second Row (OT HOUR)
if (isShowOTHourRow)
{
DataRow dr_OT_Hour = table.NewRow();
foreach (var attendanceTableItem in listAttendanceTableItemByTable)
{
int date = attendanceTableItem.WorkDate.Day;
double totalHourOT = attendanceTableItem.OvertimeHours;
totalHourOT += attendanceTableItem.ExtraOvertimeHours;
totalHourOT += attendanceTableItem.ExtraOvertimeHours2;
totalHourOT += attendanceTableItem.ExtraOvertimeHours3;
dr_OT_Hour[Att_ReportGeneralMonthlyAttendanceEntity.FieldNames.Data + date] = totalHourOT;
}
table.Rows.Add(dr_OT_Hour);
}
#endregion
}
#region Code cu
#endregion
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
var configdouble = new Dictionary<string, object>();
var config90 = new Dictionary<string, object>();
var config95 = new Dictionary<string, object>();
var config110 = new Dictionary<string, object>();
var config81 = new Dictionary<string, object>();
config81.Add("width", 81);
for (int i = 1; i <= 31; i++)
{
if (!configs.ContainsKey("_" + i.ToString()))
configs.Add("_" + i.ToString(), config81);
}
config95.Add("width", 95);
if (!configs.ContainsKey("DateQuit"))
configs.Add("DateQuit", config95);
config90.Add("width", 90);
if (!configs.ContainsKey("DateHire"))
configs.Add("DateHire", config90);
if (!configs.ContainsKey("CodeEmp"))
configs.Add("CodeEmp", config90);
if (!configs.ContainsKey("DepartmentCode"))
configs.Add("DepartmentCode", config90);
if (!configs.ContainsKey("BranchCode"))
configs.Add("BranchCode", config90);
if (!configs.ContainsKey("TeamCode"))
configs.Add("TeamCode", config90);
if (!configs.ContainsKey("SectionCode"))
configs.Add("SectionCode", config90);
config110.Add("width", 110);
if (!configs.ContainsKey("BranchName"))
configs.Add("BranchName", config110);
if (!configs.ContainsKey("TeamName"))
configs.Add("TeamName", config110);
if (!configs.ContainsKey("SectionName"))
configs.Add("SectionName", config110);
if (!configs.ContainsKey("PositionName"))
configs.Add("PositionName", config110);
if (!configs.ContainsKey("JobTitleName"))
configs.Add("JobTitleName", config110);
config.Add("hidden", true);
if (!configs.ContainsKey("DateFrom"))
configs.Add("DateFrom", config);
if (!configs.ContainsKey("DateTo"))
configs.Add("DateTo", config);
if (!configs.ContainsKey("UserExport"))
configs.Add("UserExport", config);
if (!configs.ContainsKey("DateExport"))
configs.Add("DateExport", config);
configdouble.Add("format", "{0:n2}");
if (!configs.ContainsKey("RealWorkingDays"))
configs.Add("RealWorkingDays", configdouble);
if (!configs.ContainsKey("StandardWorkDaysFormula"))
configs.Add("StandardWorkDaysFormula", configdouble);
return table.ConfigTable(configs);
}
}
public DataTable GetReportMonthlyHourFlightLocal(Guid CutOffDurationID, string strOrgStructure, Guid[] shiftIDs, Guid[] payrollIDs, Guid[] workPlaceIDs, string codeEmp, bool isIncludeQuitEmp, bool isNotAllowZero, bool isCreateTemplate, string userExport, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportMonthlyHourFlightLocalchema(UserLogin);
if (isCreateTemplate)
{
return table.ConfigDatatable();
}
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoWorkDay = new Att_WorkDayRepository(unitOfWork);
var repoTimeSheet = new Att_TimeSheetRepository(unitOfWork);
var repoJobtype = new Cat_JobTypeRepository(unitOfWork);
var repoAtt_AttendencaTable = new CustomBaseRepository<Att_AttendanceTable>(unitOfWork);
var repoAtt_AttendanceTableItem = new CustomBaseRepository<Att_AttendanceTableItem>(unitOfWork);
var repoAtt_CutOffDuration = new CustomBaseRepository<Att_CutOffDuration>(unitOfWork);
var roleServices = new Cat_RoleServices();
var lstRole = new List<object>();
lstRole.Add(null);
lstRole.Add(null);
lstRole.Add(1);
lstRole.Add(100000000);
var listRole = roleServices.GetData<Cat_RoleEntity>(lstRole, ConstantSql.hrm_cat_sp_get_Role, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.ID, m.Code }).ToList();
var jobTypeServices = new Cat_JobTypeServices();
var lstjobType = new List<object>();
lstjobType.Add(null);
lstjobType.Add(null);
lstjobType.Add(1);
lstjobType.Add(100000000);
var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => m.Code).ToList();
List<object> lstOrgIDs = new List<object>();
lstOrgIDs.AddRange(new object[3]);
lstOrgIDs[0] = (object)strOrgStructure;
lstOrgIDs[1] = null;
lstOrgIDs[2] = codeEmp;
List<Hre_ProfileEntity> profiles = GetData<Hre_ProfileEntity>(lstOrgIDs, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
var cutoffInfo = repoAtt_CutOffDuration.FindBy(s => s.ID == CutOffDurationID).FirstOrDefault();
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
DateTime dateStart = cutoffInfo.DateStart;
DateTime dateEnd = cutoffInfo.DateEnd;
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
List<Guid> profileIds = profiles.Select(s => s.ID).ToList();
var timesheets = repoTimeSheet.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && s.Date != null && dateStart <= s.Date && s.Date <= dateEnd
&& s.IsDelete == null).Select(s => new { s.ProfileID, s.JobTypeID, s.RoleID, s.Date, s.NoHour, s.ID, s.Note }).ToList();
var jobtypes = repoJobtype.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.Code, s.RoleID }).ToList();
var workDays = repoWorkDay.FindBy(s => profileIds.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null).Select(s => new { s.ProfileID, s.ShiftID, s.Status }).ToList();
if (payrollIDs != null)
{
profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList();
}
if (workPlaceIDs != null)
{
profiles = profiles.Where(s => s.WorkPlaceID != null && workPlaceIDs.Contains(s.WorkPlaceID.Value)).ToList();
}
if (shiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList();
profileIds = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
profileIds = profiles.Select(s => s.ID).ToList();
var attendanceTables = repoAtt_AttendencaTable
.FindBy(s => s.IsDelete == null && s.CutOffDurationID == CutOffDurationID && profileIds.Contains(s.ProfileID))
.ToList();
List<Guid> attendanceTablesID = attendanceTables.Select(s => s.ID).ToList();
var attendanceTableItem = repoAtt_AttendanceTableItem
.FindBy(s => s.IsDelete == null && attendanceTablesID.Contains(s.AttendanceTableID))
.ToList();
if (isNotAllowZero)
{
profileIds = attendanceTables.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
profileIds = attendanceTables.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
foreach (var profile in profiles)
{
var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID);
var timeSheetByProfile = timesheets.Where(s => s.ProfileID == profile.ID).ToList();
if (timeSheetByProfile == null)
{
continue;
}
var lstjobtypebytimesheetpro = timeSheetByProfile.Select(s => s.JobTypeID).ToList();
var lstJobTypeCodeByTimeSheet = jobtypes.Where(s => lstjobtypebytimesheetpro.Contains(s.ID)).Select(s => new { s.ID, s.Code, s.RoleID }).ToList();
if (attendanceTableProfile == null)
{
continue;
}
var attendanceTableItemProfile = attendanceTableItem.Where(s => s.AttendanceTableID == attendanceTableProfile.ID).ToList();
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
if (profile.DateHire != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StartingDate] = profile.DateHire.Value;
if (profile.DateQuit != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.ResignedDate] = profile.DateQuit.Value;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateFrom] = dateStart;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateTo] = dateEnd;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport] = userExport;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateExport] = DateTime.Today;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? attendanceTableProfile.StdWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? attendanceTableProfile.RealWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? attendanceTableProfile.PaidWorkDayCount : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? attendanceTableProfile.AnlDayAvailable : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? attendanceTableProfile.NightShiftHours : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? attendanceTableProfile.LateEarlyDeductionHours : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? attendanceTableProfile.AnlDayTaken : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty;
foreach (var item in attendanceTableItemProfile)
{
if (item.LeaveTypeID != null)
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = leavedayTypes.Where(s => s.ID == item.LeaveTypeID).FirstOrDefault().Code;
}
}
foreach (var roleEntity in listRole)
{
var lstJobTypeByRoleID = lstJobTypeCodeByTimeSheet.Where(s => s.RoleID == roleEntity.ID).ToList();
foreach (var jobcode in lstJobTypeByRoleID)
{
var CodeRoleAndJobType = roleEntity.Code + "_" + jobcode.Code;
if (table.Columns.Contains(CodeRoleAndJobType))
{
double? nohourByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID && s.RoleID == roleEntity.ID).Sum(s => s.NoHour);
row[CodeRoleAndJobType] = nohourByPro;
}
}
}
table.Rows.Add(row);
}
return table.ConfigTable();
}
}
public DataTable GetReportMonthlyTimeSheet(DateTime? dateStart, DateTime DateTo, string strOrgStructure, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, Guid[] payrollIDs, bool isIncludeQuitEmp, bool isNotAllowZero, Guid exportid, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportMonthlyTimeSheetSchema(UserLogin);
dateStart = new DateTime(dateStart.Value.Year, dateStart.Value.Month, 1);
DateTime monthEnd = new DateTime(dateStart.Value.Year, dateStart.Value.Month, DateTime.DaysInMonth(dateStart.Value.Year, dateStart.Value.Month));
monthEnd = monthEnd.AddDays(1).AddMilliseconds(-1);
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var leavedayTypes = repoCat_LeaveDayType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.Code, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork);
var overtimTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).ToList();
var codeCenters = unitOfWork.CreateQueryable<Cat_CostCentre>(Guid.Empty, s => s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
List<Guid> profileIds = profiles.Select(s => s.ID).ToList();
string status = string.Empty;
List<object> para_reportWorDay = new List<object>();
para_reportWorDay.AddRange(new object[2]);
para_reportWorDay[0] = dateStart;
para_reportWorDay[0] = monthEnd;
var repoWorkDay = new Att_WorkDayServices();
List<Att_WorkdayEntity> workDays = repoWorkDay.GetData<Att_WorkdayEntity>(para_reportWorDay, ConstantSql.hrm_att_sp_getdata_reportWorDay, UserLogin, ref status).ToList();
workDays = workDays.Where(s => profileIds.Contains(s.ProfileID)).ToList();
if (payrollIDs != null)
{
profiles = profiles.Where(s => s.PayrollGroupID != null && payrollIDs.Contains(s.PayrollGroupID.Value)).ToList();
}
if (shiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && shiftIDs.Contains(s.ShiftID.Value)).ToList();
profileIds = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
profileIds = profiles.Select(s => s.ID).ToList();
var repoAtt_AttendencaTable = new Att_AttendanceTableRepository(unitOfWork);
List<object> para = new List<object>();
para.AddRange(new object[7]);
para[3] = dateStart;
para[4] = monthEnd;
para[5] = 1;
para[6] = int.MaxValue;
var attService = new Att_AttendanceServices();
List<Att_AttendanceTableEntity> lstAttendanceTable = attService.GetData<Att_AttendanceTableEntity>(para, ConstantSql.hrm_att_sp_get_attdancetable, UserLogin, ref status).ToList();
var attendanceTables = lstAttendanceTable.Where(s => profileIds.Contains(s.ProfileID)).ToList();
profileIds = attendanceTables.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
foreach (var profile in profiles)
{
var attendanceTableProfile = attendanceTables.FirstOrDefault(s => s.ProfileID == profile.ID);
if (attendanceTableProfile == null)
{
continue;
}
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
var cost = codeCenters.FirstOrDefault(s => s.ID == profile.CostCentreID);
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CodeCenter] = cost != null ? cost.Code : string.Empty;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.StdWorkDayCount] = attendanceTableProfile.StdWorkDayCount > 0 ? (object)attendanceTableProfile.StdWorkDayCount : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.RealWorkDayCount] = attendanceTableProfile.RealWorkDayCount > 0 ? (object)attendanceTableProfile.RealWorkDayCount : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.PaidWorkDayCount] = attendanceTableProfile.PaidWorkDayCount > 0 ? (object)attendanceTableProfile.PaidWorkDayCount : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.AnlDayAvailable] = attendanceTableProfile.AnlDayAvailable > 0 ? (object)attendanceTableProfile.AnlDayAvailable : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.NightShiftHours] = attendanceTableProfile.NightShiftHours > 0 ? (object)attendanceTableProfile.NightShiftHours : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.LateEarlyDeductionHours] = attendanceTableProfile.LateEarlyDeductionHours > 0 ? (object)attendanceTableProfile.LateEarlyDeductionHours : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? (object)attendanceTableProfile.AnlDayTaken : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty;
#region Export theo cot dong
if (exportid != Guid.Empty)
{
var leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay1Type);
if (leaday != null && !string.IsNullOrEmpty(leaday.Code) && table.Columns.Contains(leaday.Code))
{
row[leaday.Code] = attendanceTableProfile.LeaveDay1Hours > 0 ? (object)attendanceTableProfile.LeaveDay1Hours : DBNull.Value;
}
leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay2Type);
if (leaday != null)
{
row[leaday.Code] = attendanceTableProfile.LeaveDay2Hours > 0 ? (object)attendanceTableProfile.LeaveDay2Hours : DBNull.Value;
}
leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay3Type);
if (leaday != null)
{
row[leaday.Code] = attendanceTableProfile.LeaveDay3Hours > 0 ? (object)attendanceTableProfile.LeaveDay3Hours : DBNull.Value;
}
leaday = leavedayTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.LeaveDay4Type);
if (leaday != null)
{
row[leaday.Code] = attendanceTableProfile.LeaveDay4Hours > 0 ? (object)attendanceTableProfile.LeaveDay4Hours : DBNull.Value;
}
var overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime1Type);
if (overtimeType != null)
{
// row[overtimeType.Code] = attendanceTableProfile.Overtime1Hours > 0 ? (object)attendanceTableProfile.Overtime1Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime1Hours > 0 ? (object)attendanceTableProfile.Overtime1Hours : DBNull.Value;
}
overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime2Type);
if (overtimeType != null)
{
// row[overtimeType.Code] = attendanceTableProfile.Overtime2Hours > 0 ? (object)attendanceTableProfile.Overtime2Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime2Hours > 0 ? (object)attendanceTableProfile.Overtime2Hours : DBNull.Value;
}
overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime3Type);
if (overtimeType != null)
{
//row[overtimeType.Code] = attendanceTableProfile.Overtime3Hours > 0 ? (object)attendanceTableProfile.Overtime3Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime3Hours > 0 ? (object)attendanceTableProfile.Overtime3Hours : DBNull.Value;
}
overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime4Type);
if (overtimeType != null)
{
//row[overtimeType.Code] = attendanceTableProfile.Overtime4Hours > 0 ? (object)attendanceTableProfile.Overtime4Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime4Hours > 0 ? (object)attendanceTableProfile.Overtime4Hours : DBNull.Value;
}
overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime5Type);
if (overtimeType != null)
{
//row[overtimeType.Code] = attendanceTableProfile.Overtime5Hours > 0 ? (object)attendanceTableProfile.Overtime5Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime5Hours > 0 ? (object)attendanceTableProfile.Overtime5Hours : DBNull.Value;
}
overtimeType = overtimTypes.FirstOrDefault(s => s.ID == attendanceTableProfile.Overtime6Type);
if (overtimeType != null)
{
//row[overtimeType.Code] = attendanceTableProfile.Overtime6Hours > 0 ? (object)attendanceTableProfile.Overtime6Hours : DBNull.Value;
row[overtimeType.OvertimeTypeName] = attendanceTableProfile.Overtime6Hours > 0 ? (object)attendanceTableProfile.Overtime6Hours : DBNull.Value;
}
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateEarly] = (object)attendanceTableProfile.CardMissingCount ?? DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateLess2H] = (object)attendanceTableProfile.LateEarlyLeastCount ?? DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CountLateMore2H] = (object)attendanceTableProfile.LateEarlyGreaterCount ?? DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.UsableLeave] = attendanceTableProfile.TotalAnlDayAvailable > 0 ? (object)attendanceTableProfile.TotalAnlDayAvailable : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickLeave] = attendanceTableProfile.TotalSickDayAvailable > 0 ? (object)attendanceTableProfile.TotalSickDayAvailable : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.CurrentMonth] = attendanceTableProfile.AnlDayTaken > 0 ? (object)attendanceTableProfile.AnlDayTaken : DBNull.Value;
//row["SickCurrentMonth"] = attendanceTableProfile.SickDayTaken > 0 ? (object)attendanceTableProfile.SickDayTaken : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickCurrentMonth] = attendanceTableProfile.SickDayTaken > 0 ? (object)attendanceTableProfile.SickDayTaken : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.YearToDate] = attendanceTableProfile.AnlDayAdjacent > 0 ? (object)attendanceTableProfile.AnlDayAdjacent : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SickYearToDate] = attendanceTableProfile.SickDayAdjacent > 0 ? (object)attendanceTableProfile.SickDayAdjacent : DBNull.Value;
row[Att_ReportMonthlyTimeSheetEntity.FieldNames.SumMuteLateEarly] = (object)attendanceTableProfile.LateEarlyTotal ?? DBNull.Value;
}
#endregion
table.Rows.Add(row);
}
if (exportid != Guid.Empty)
{
return table.ConfigDatatable();
}
return table;
}
}
public void SetStatusLeaveOnWorkday(List<Att_WorkdayEntity> lstWorkday)
{
if (lstWorkday == null || lstWorkday.Count == 0)
return;
List<Guid> lstProfileId = lstWorkday.Select(m => m.ProfileID).ToList();
DateTime DateMin = lstWorkday.Min(m => m.WorkDate);
DateTime DateMax = lstWorkday.Max(m => m.WorkDate);
DateMax = DateMax.Date.AddDays(1).AddMinutes(-1);
string E_CANCEL = AttendanceDataStatus.E_CANCEL.ToString();
string E_REJECTED = AttendanceDataStatus.E_REJECTED.ToString();
var lstLeaveDay = new List<Att_LeaveDay>().Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.LeaveDayTypeID, m.Status }).ToList();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork);
var repoCat_DayOff = new Cat_DayOffRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
lstLeaveDay = repoAtt_Leaveday
.FindBy(m => m.Status != E_CANCEL && m.Status != E_REJECTED && m.DateStart <= DateMax
&& m.DateEnd >= DateMin && lstProfileId.Contains(m.ProfileID) && m.IsDelete == null)
.Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.LeaveDayTypeID, m.Status }).ToList();
var lstLeaveType = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null).Select(m => new { m.ID, m.Code, m.CodeStatistic }).ToList();
var lstDayOffHoliday = repoCat_DayOff.FindBy(m => m.DateOff != null && m.IsDelete == null).Select(m => new { m.DateOff, m.Type }).ToList();
foreach (var item in lstWorkday)
{
Guid profileId = item.ProfileID;
DateTime workDate = item.WorkDate.Date;
var Dayoff = lstDayOffHoliday.Where(m => m.DateOff == workDate).FirstOrDefault();
if (Dayoff != null)
{
string code = string.Empty;
if (Dayoff.Type == HolidayType.E_HOLIDAY_HLD.ToString())
{
code = "HLD";
}
else if (Dayoff.Type == HolidayType.E_WEEKEND_HLD.ToString())
{
code = "WH";
}
else
{
code = "CH";
}
var leavetype = lstLeaveType.Where(m => m.Code == code).FirstOrDefault();
if (leavetype == null)
{
continue;
}
string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code;
item.udLeavedayCode1 = leavedayCode;
continue;
}
var lstLeaveDayByProfile = lstLeaveDay.Where(m => m.ProfileID == profileId && m.DateStart.Date <= workDate && m.DateEnd.Date >= workDate).ToList();
int Num = 0;
foreach (var leaveDay in lstLeaveDayByProfile)
{
Num++;
if (Num == 3)
break;
if (leaveDay != null)
{
if (Num == 1)
{
var leavetype = lstLeaveType.Where(m => m.ID == leaveDay.LeaveDayTypeID).FirstOrDefault();
if (leavetype != null)
{
string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code;
item.udLeavedayCode1 = leavedayCode;
}
item.udLeavedayStatus1 = leaveDay.Status;
}
else if (Num == 2)
{
var leavetype = lstLeaveType.Where(m => m.ID == leaveDay.LeaveDayTypeID).FirstOrDefault();
if (leavetype != null)
{
string leavedayCode = !string.IsNullOrEmpty(leavetype.CodeStatistic) ? leavetype.CodeStatistic : leavetype.Code;
item.udLeavedayCode2 = leavedayCode;
}
item.udLeavedayStatus2 = leaveDay.Status;
}
}
}
}
}
return;
}
public void AnalyzeCompensation(int Year, List <Guid> lstProfileIDTotal)
{
//Lấy cấu hình
//lấy ngày nghỉ (Bù)
//Lấy tăng ca (Bù)
//Lấy Ca Làm Việc (Bù)
//Hệ Số Tăng Ca (Loại Tăng Ca)
foreach (var lstProfileID in lstProfileIDTotal.Chunk(500))
{
using (var context = new VnrHrmDataContext())
{
#region Khai báo
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork);
var repoAtt_CompensationConfig = new Att_CompensationConfigRepository(unitOfWork);
var repoAtt_CompensationDetail = new Att_CompensationDetailRepository(unitOfWork);
#endregion
#region Lấy Dữ Liệu
// var profileByCodeEmp = repoHre_Profile.FindBy(p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault();
var lstProfile = repoHre_Profile.FindBy(m => lstProfileID.Contains(m.ID)).Select(m => new { m.ID, m.DateHire }).ToList();
var lstCompensationConfig = repoAtt_CompensationConfig
.FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value))
.Select(m => new CompensationConfigModel()
{
ID = m.ID, ProfileID = m.ProfileID, Year = m.Year, InitAvailable = m.InitAvailable, MonthBeginInYear = m.MonthBeginInYear, MonthResetInitAvailable = m.MonthResetInitAvailable, MonthStartProfile = m.MonthStartProfile
})
.ToList();
int MonthBeginYear = 1;
if (lstCompensationConfig != null && lstCompensationConfig.Count > 0)
{
MonthBeginYear = lstCompensationConfig.FirstOrDefault().MonthBeginInYear ?? 1;
}
DateTime BeginYear = new DateTime(Year, MonthBeginYear, 1);
DateTime EndYear = BeginYear.AddYears(1).AddMinutes(-1);
string E_APPROVED_Leave = LeaveDayStatus.E_APPROVED.ToString();
var lstLeaveDayCompensationTypeID = repoCat_LeaveDayType
.FindBy(m => m.IsTimeOffInLieu == true)
.Select(m => m.ID)
.ToList();
var lstLeaveday = repoAtt_Leaveday
.FindBy(m => m.Status == E_APPROVED_Leave && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstLeaveDayCompensationTypeID.Contains(m.LeaveDayTypeID) && lstProfileID.Contains(m.ProfileID))
.Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.DurationType })
.ToList();
string E_TIMEOFF = MethodOption.E_TIMEOFF.ToString();
string E_APPROVED_Overtime = OverTimeStatus.E_APPROVED.ToString();
string E_CONFIRM_Overtime = OverTimeStatus.E_CONFIRM.ToString();
var lstOvertime = repoAtt_Overtime
.FindBy(m => m.WorkDateRoot >= BeginYear && m.WorkDateRoot <= EndYear && m.MethodPayment == E_TIMEOFF && (m.Status == E_APPROVED_Overtime || m.Status == E_CONFIRM_Overtime) && lstProfileID.Contains(m.ProfileID))
.Select(m => new { m.ID, m.ProfileID, m.WorkDateRoot, m.ApproveHours, m.ConfirmHours, m.Status }).ToList();
string E_APPROVED_Roster = RosterStatus.E_APPROVED.ToString();
var lstRoster = repoAtt_Roster
.FindBy(m => m.Status == E_APPROVED_Roster && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstProfileID.Contains(m.ProfileID))
.Select(m => new RosterModel
{
ID = m.ID,
ProfileID = m.ProfileID,
DateStart = m.DateStart,
DateEnd = m.DateEnd,
Type = m.Type,
MonShiftID = m.MonShiftID,
TueShiftID = m.TueShiftID,
WedShiftID = m.WedShiftID,
ThuShiftID = m.ThuShiftID,
FriShiftID = m.FriShiftID,
SatShiftID = m.SatShiftID,
SunShiftID = m.SunShiftID
})
.ToList();
var lstShift = repoCat_Shift.GetAll().Select(m => new { m.ID, m.WorkHours, m.StdWorkHours }).ToList();
List <Att_CompensationDetail> detailInDB = repoAtt_CompensationDetail
.FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value))
.ToList();
List <Att_CompensationDetail> detailNew = new List <Att_CompensationDetail>();
foreach (var ProfileID in lstProfileID)
{
double TotalLeaveHourBeforeMonth = 0;
double TotalOvertimeHourBeforeMonth = 0;
var Profile = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault();
int MonthHire = 1;
if (Profile.DateHire != null && Profile.DateHire >= BeginYear && Profile.DateHire <= EndYear)
{
if (Profile.DateHire.Value.Day >= 15)
{
MonthHire = Profile.DateHire.Value.AddMonths(1).Month;
}
else
{
MonthHire = Profile.DateHire.Value.Month;
}
}
for (DateTime Month = BeginYear; Month < EndYear; Month = Month.AddMonths(1))
{
if (MonthHire > Month.Month)
{
continue;
}
DateTime BeginMonth = Month;
DateTime EndMonth = BeginMonth.AddMonths(1).AddMinutes(-1);
double TotalLeaveHourInMonth = 0;
double TotalOvertimeHourInMonth = 0;
#region Tăng Ca
var lstOvertimeByProfileByMonth = lstOvertime.Where(m => m.ProfileID == ProfileID && m.WorkDateRoot >= BeginMonth && m.WorkDateRoot <= EndMonth).ToList();
TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_CONFIRM_Overtime).Sum(m => m.ConfirmHours);
TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_APPROVED_Overtime).Sum(m => m.ApproveHours ?? 0);
#endregion
#region Ngày Nghỉ
var lstLeavedayByProfileByMonth = lstLeaveday.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList();
var lstRosterByProfileByMonth = lstRoster.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList();
foreach (var Leaveday in lstLeavedayByProfileByMonth)
{
DateTime dateStart = Leaveday.DateStart < BeginMonth ? BeginMonth : Leaveday.DateStart;
DateTime dateEnd = Leaveday.DateEnd > EndMonth ? EndMonth : Leaveday.DateEnd;
for (DateTime dateCheck = dateStart; dateCheck <= dateEnd; dateCheck = dateCheck.AddDays(1))
{
if (Leaveday.DurationType != LeaveDayDurationType.E_FULLSHIFT.ToString()) // Trong Ca
{
TotalLeaveHourInMonth += Leaveday.LeaveHours ?? 0;
}
else //Toàn Ca
{
//Check xem có Ca làm việc không
Guid?ShiftID = null;
var E_TIME_OFF = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_TIME_OFF.ToString()).FirstOrDefault();
var E_CHANGE_SHIFT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_CHANGE_SHIFT.ToString()).FirstOrDefault();
var E_DEFAULT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_DEFAULT.ToString()).FirstOrDefault();
var E_ROSTERGROUP = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_ROSTERGROUP.ToString()).FirstOrDefault();
if (E_TIME_OFF != null)
{
}
else if (E_CHANGE_SHIFT != null)
{
ShiftID = GetShiftRoster(dateCheck, E_CHANGE_SHIFT);
}
else if (E_DEFAULT != null)
{
ShiftID = GetShiftRoster(dateCheck, E_DEFAULT);
}
else if (E_ROSTERGROUP != null)
{
//Chưa hỗ trợ đăng ký ca theo nhóm
}
if (ShiftID != null)
{
var Shift = lstShift.Where(m => m.ID == ShiftID.Value).FirstOrDefault();
if (Shift != null)
{
TotalLeaveHourInMonth += Shift.StdWorkHours ?? (Shift.WorkHours ?? 0);
}
}
}
}
}
#endregion
#region Gán Dữ Liệu
CompensationConfigModel Config = lstCompensationConfig.Where(m => m.ProfileID == ProfileID).FirstOrDefault();
Att_CompensationDetail detail = detailInDB.Where(m => m.MonthYear == Month && m.ProfileID == ProfileID).FirstOrDefault();
if (detail == null)
{
detail = new Att_CompensationDetail();
detail.ID = Guid.NewGuid();
detailNew.Add(detail);
}
if (Config != null)
{
if (Config.MonthResetInitAvailable != null && Config.MonthResetInitAvailable.Value <= Month.Month)
{
}
else
{
detail.InitAvailable = Config.InitAvailable;
}
detail.MonthBeginInYear = Config.MonthBeginInYear ?? 1;
detail.MonthResetInitAvailable = Config.MonthResetInitAvailable;
}
detail.ProfileID = ProfileID;
detail.Year = Year;
detail.MonthYear = Month;
detail.MonthStartProfile = MonthHire;
detail.LeaveInMonth = TotalLeaveHourInMonth;
detail.TotalLeaveBef = TotalLeaveHourBeforeMonth;
detail.OvertimeInMonth = TotalOvertimeHourInMonth;
detail.TotalOvertimeBef = TotalOvertimeHourBeforeMonth;
detail.Remain = ((detail.InitAvailable ?? 0) + TotalOvertimeHourBeforeMonth + TotalOvertimeHourInMonth) - (TotalLeaveHourBeforeMonth + TotalLeaveHourInMonth);
TotalOvertimeHourBeforeMonth += TotalOvertimeHourInMonth;
TotalLeaveHourBeforeMonth += TotalLeaveHourInMonth;
#endregion
}
}
repoAtt_CompensationDetail.Add(detailNew);
repoAtt_CompensationDetail.SaveChanges();
#endregion
}
}
}
/// <summary>
/// Lay du lieu bao cao tong hop nghi om
/// </summary>
/// <param name="dateYear"></param>
/// <param name="lstProfileIDs"></param>
/// <returns></returns>
public List<Att_ReportSickLeaveEntity> GetReportSickLeave(DateTime dateYear, List<Guid> lstProfileIDs)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
int year = dateYear.Year;
DateTime dateStart = new DateTime(year, 1, 1);
DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12));
string key = OverTimeStatus.E_APPROVED.ToString();
var repoCat_Leaveday = new Cat_LeaveDayTypeRepository(unitOfWork);
var leavedayTypes = repoCat_Leaveday.FindBy(s => s.CodeStatistic == "P" || s.CodeStatistic == "SC").Select(s => new { s.ID, s.CodeStatistic, s.PaidRate }).Distinct().ToList();
var leadayTypeIDs = leavedayTypes.Select(s => s.ID).ToList();
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var leaveDays = new List<Att_LeaveDay>().Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
if (lstProfileIDs != null && lstProfileIDs.Count > 0)
{
leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd && lstProfileIDs.Contains(s.ProfileID))
.Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
}
else
{
leaveDays = repoAtt_LeaveDay.FindBy(s => leadayTypeIDs.Contains(s.LeaveDayTypeID) && s.LeaveHours > 0 && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
.Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
}
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoHre_Profile.FindBy(s => profileIds.Contains(s.ID) && lstProfileIDs.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, }).ToList();
string E_ANNUAL_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_ANNUAL_LEAVE.ToString();
string E_SICK_LEAVE = HRM.Infrastructure.Utilities.EnumDropDown.AnnualLeaveDetailType.E_SICK_LEAVE.ToString();
var repoAtt_AnnualLeaveDetail = new Att_AnnualLeaveDetailRepository(unitOfWork);
var anualLeaves = repoAtt_AnnualLeaveDetail.FindBy(s => s.ProfileID != null && profileIds.Contains(s.ProfileID.Value) && (s.Type == E_ANNUAL_LEAVE || s.Type == E_SICK_LEAVE) && s.Year == year)
.Select(s => new { s.ProfileID, s.Month3, s.Type }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
List<Att_ReportSickLeaveEntity> lstReportSickLeaveEntity = new List<Att_ReportSickLeaveEntity>();
Att_ReportSickLeaveEntity reportSickLeaveEntity = null;
foreach (var profile in profiles)
{
reportSickLeaveEntity = new Att_ReportSickLeaveEntity();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
reportSickLeaveEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
reportSickLeaveEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
reportSickLeaveEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
reportSickLeaveEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
reportSickLeaveEntity.ProfileName = profile.ProfileName;
reportSickLeaveEntity.CodeEmp = profile.CodeEmp;
reportSickLeaveEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
reportSickLeaveEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
reportSickLeaveEntity.DateExport = DateTime.Now;
var anual = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_ANNUAL_LEAVE);
var anualSick = anualLeaves.FirstOrDefault(s => s.ProfileID == profile.ID && s.Type == E_SICK_LEAVE);
reportSickLeaveEntity.TotalP = anual != null ? anual.Month3 : 0;
reportSickLeaveEntity.TotalSC = anualSick != null ? anualSick.Month3 : 0;
//reportSickLeaveEntity.BugetYearP = anualSick != null ? anualSick.Month3 : 0;
//reportSickLeaveEntity.BalanceSC = anualSick != null ? anualSick.Month3 : 0;
for (int i = 1; i <= 12; i++)
{
dateStart = new DateTime(year, i, 1);
dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i));
reportSickLeaveEntity.DateFrom = dateStart;
reportSickLeaveEntity.DateTo = dateEnd;
foreach (var leaday in leavedayTypes)
{
Dictionary<string, double> leavedayKeyValue = new Dictionary<string, double>();
double leaveDayHours = leaveDays
.Where(s => dateStart <= s.DateEnd && s.DateStart <= dateEnd && s.ProfileID == profile.ID && s.LeaveDayTypeID == leaday.ID)
.Sum(s => s.LeaveHours.Value / 8);
}
}
lstReportSickLeaveEntity.Add(reportSickLeaveEntity);
}
return lstReportSickLeaveEntity;
}
}
public void AnalyzeCompensation(int Year, List<Guid> lstProfileIDTotal)
{
//Lấy cấu hình
//lấy ngày nghỉ (Bù)
//Lấy tăng ca (Bù)
//Lấy Ca Làm Việc (Bù)
//Hệ Số Tăng Ca (Loại Tăng Ca)
foreach (var lstProfileID in lstProfileIDTotal.Chunk(500))
{
using (var context = new VnrHrmDataContext())
{
#region Khai báo
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var repoAtt_Leaveday = new Att_LeavedayRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork);
var repoAtt_CompensationConfig = new Att_CompensationConfigRepository(unitOfWork);
var repoAtt_CompensationDetail = new Att_CompensationDetailRepository(unitOfWork);
#endregion
#region Lấy Dữ Liệu
// var profileByCodeEmp = repoHre_Profile.FindBy(p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault();
var lstProfile = repoHre_Profile.FindBy(m => lstProfileID.Contains(m.ID)).Select(m => new { m.ID, m.DateHire }).ToList();
var lstCompensationConfig = repoAtt_CompensationConfig
.FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value))
.Select(m => new CompensationConfigModel() { ID = m.ID, ProfileID = m.ProfileID, Year = m.Year, InitAvailable = m.InitAvailable, MonthBeginInYear = m.MonthBeginInYear, MonthResetInitAvailable = m.MonthResetInitAvailable, MonthStartProfile = m.MonthStartProfile })
.ToList();
int MonthBeginYear = 1;
if (lstCompensationConfig != null && lstCompensationConfig.Count>0)
{
MonthBeginYear = lstCompensationConfig.FirstOrDefault().MonthBeginInYear ?? 1;
}
DateTime BeginYear = new DateTime(Year, MonthBeginYear, 1);
DateTime EndYear = BeginYear.AddYears(1).AddMinutes(-1);
string E_APPROVED_Leave = LeaveDayStatus.E_APPROVED.ToString();
var lstLeaveDayCompensationTypeID = repoCat_LeaveDayType
.FindBy(m => m.IsTimeOffInLieu == true)
.Select(m => m.ID)
.ToList();
var lstLeaveday = repoAtt_Leaveday
.FindBy(m => m.Status == E_APPROVED_Leave && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstLeaveDayCompensationTypeID.Contains(m.LeaveDayTypeID) && lstProfileID.Contains(m.ProfileID))
.Select(m => new { m.ID, m.ProfileID, m.DateStart, m.DateEnd, m.LeaveHours, m.DurationType })
.ToList();
string E_TIMEOFF = MethodOption.E_TIMEOFF.ToString();
string E_APPROVED_Overtime = OverTimeStatus.E_APPROVED.ToString();
string E_CONFIRM_Overtime = OverTimeStatus.E_CONFIRM.ToString();
var lstOvertime = repoAtt_Overtime
.FindBy(m => m.WorkDateRoot >= BeginYear && m.WorkDateRoot <= EndYear && m.MethodPayment == E_TIMEOFF && (m.Status == E_APPROVED_Overtime || m.Status == E_CONFIRM_Overtime) && lstProfileID.Contains(m.ProfileID))
.Select(m => new { m.ID, m.ProfileID, m.WorkDateRoot, m.ApproveHours, m.ConfirmHours, m.Status }).ToList();
string E_APPROVED_Roster = RosterStatus.E_APPROVED.ToString();
var lstRoster = repoAtt_Roster
.FindBy(m => m.Status == E_APPROVED_Roster && m.DateStart <= EndYear && m.DateEnd >= BeginYear && lstProfileID.Contains(m.ProfileID))
.Select(m => new RosterModel
{
ID = m.ID,
ProfileID = m.ProfileID,
DateStart = m.DateStart,
DateEnd = m.DateEnd,
Type = m.Type,
MonShiftID = m.MonShiftID,
TueShiftID = m.TueShiftID,
WedShiftID = m.WedShiftID,
ThuShiftID = m.ThuShiftID,
FriShiftID = m.FriShiftID,
SatShiftID = m.SatShiftID,
SunShiftID = m.SunShiftID
})
.ToList();
var lstShift = repoCat_Shift.GetAll().Select(m => new { m.ID, m.WorkHours, m.StdWorkHours }).ToList();
List<Att_CompensationDetail> detailInDB = repoAtt_CompensationDetail
.FindBy(m => m.Year == Year && m.ProfileID != null && lstProfileID.Contains(m.ProfileID.Value))
.ToList();
List<Att_CompensationDetail> detailNew = new List<Att_CompensationDetail>();
foreach (var ProfileID in lstProfileID)
{
double TotalLeaveHourBeforeMonth = 0;
double TotalOvertimeHourBeforeMonth = 0;
var Profile = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault();
int MonthHire = 1;
if (Profile.DateHire != null && Profile.DateHire >= BeginYear && Profile.DateHire <= EndYear)
{
if (Profile.DateHire.Value.Day >= 15)
{
MonthHire = Profile.DateHire.Value.AddMonths(1).Month;
}
else
{
MonthHire = Profile.DateHire.Value.Month;
}
}
for (DateTime Month = BeginYear; Month < EndYear; Month = Month.AddMonths(1))
{
if (MonthHire > Month.Month)
continue;
DateTime BeginMonth = Month;
DateTime EndMonth = BeginMonth.AddMonths(1).AddMinutes(-1);
double TotalLeaveHourInMonth = 0;
double TotalOvertimeHourInMonth = 0;
#region Tăng Ca
var lstOvertimeByProfileByMonth = lstOvertime.Where(m => m.ProfileID == ProfileID && m.WorkDateRoot >= BeginMonth && m.WorkDateRoot <= EndMonth).ToList();
TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_CONFIRM_Overtime).Sum(m => m.ConfirmHours);
TotalOvertimeHourInMonth += lstOvertimeByProfileByMonth.Where(m => m.Status == E_APPROVED_Overtime).Sum(m => m.ApproveHours ?? 0);
#endregion
#region Ngày Nghỉ
var lstLeavedayByProfileByMonth = lstLeaveday.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList();
var lstRosterByProfileByMonth = lstRoster.Where(m => m.DateStart <= EndMonth && m.DateEnd >= BeginMonth && m.ProfileID == ProfileID).ToList();
foreach (var Leaveday in lstLeavedayByProfileByMonth)
{
DateTime dateStart = Leaveday.DateStart < BeginMonth ? BeginMonth : Leaveday.DateStart;
DateTime dateEnd = Leaveday.DateEnd > EndMonth ? EndMonth : Leaveday.DateEnd;
for (DateTime dateCheck = dateStart; dateCheck <= dateEnd; dateCheck = dateCheck.AddDays(1))
{
if (Leaveday.DurationType != LeaveDayDurationType.E_FULLSHIFT.ToString()) // Trong Ca
{
TotalLeaveHourInMonth += Leaveday.LeaveHours ?? 0;
}
else //Toàn Ca
{
//Check xem có Ca làm việc không
Guid? ShiftID = null;
var E_TIME_OFF = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_TIME_OFF.ToString()).FirstOrDefault();
var E_CHANGE_SHIFT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_CHANGE_SHIFT.ToString()).FirstOrDefault();
var E_DEFAULT = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_DEFAULT.ToString()).FirstOrDefault();
var E_ROSTERGROUP = lstRosterByProfileByMonth.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck && m.Type == RosterType.E_ROSTERGROUP.ToString()).FirstOrDefault();
if (E_TIME_OFF != null)
{
}
else if (E_CHANGE_SHIFT != null)
{
ShiftID = GetShiftRoster(dateCheck, E_CHANGE_SHIFT);
}
else if (E_DEFAULT != null)
{
ShiftID = GetShiftRoster(dateCheck, E_DEFAULT);
}
else if (E_ROSTERGROUP != null)
{
//Chưa hỗ trợ đăng ký ca theo nhóm
}
if (ShiftID != null)
{
var Shift = lstShift.Where(m => m.ID == ShiftID.Value).FirstOrDefault();
if (Shift != null)
{
TotalLeaveHourInMonth += Shift.StdWorkHours ?? (Shift.WorkHours ?? 0);
}
}
}
}
}
#endregion
#region Gán Dữ Liệu
CompensationConfigModel Config = lstCompensationConfig.Where(m => m.ProfileID == ProfileID).FirstOrDefault();
Att_CompensationDetail detail = detailInDB.Where(m => m.MonthYear == Month && m.ProfileID == ProfileID).FirstOrDefault();
if (detail == null)
{
detail = new Att_CompensationDetail();
detail.ID = Guid.NewGuid();
detailNew.Add(detail);
}
if (Config != null)
{
if (Config.MonthResetInitAvailable != null && Config.MonthResetInitAvailable.Value <= Month.Month)
{
}
else
{
detail.InitAvailable = Config.InitAvailable;
}
detail.MonthBeginInYear = Config.MonthBeginInYear ?? 1;
detail.MonthResetInitAvailable = Config.MonthResetInitAvailable;
}
detail.ProfileID = ProfileID;
detail.Year = Year;
detail.MonthYear = Month;
detail.MonthStartProfile = MonthHire;
detail.LeaveInMonth = TotalLeaveHourInMonth;
detail.TotalLeaveBef = TotalLeaveHourBeforeMonth;
detail.OvertimeInMonth = TotalOvertimeHourInMonth;
detail.TotalOvertimeBef = TotalOvertimeHourBeforeMonth;
detail.Remain = ((detail.InitAvailable ?? 0) + TotalOvertimeHourBeforeMonth + TotalOvertimeHourInMonth) - (TotalLeaveHourBeforeMonth + TotalLeaveHourInMonth);
TotalOvertimeHourBeforeMonth += TotalOvertimeHourInMonth;
TotalLeaveHourBeforeMonth += TotalLeaveHourInMonth;
#endregion
}
}
repoAtt_CompensationDetail.Add(detailNew);
repoAtt_CompensationDetail.SaveChanges();
#endregion
}
}
}
/// <summary>
/// Update thuộc tính IsDelete là true
/// </summary>
/// <param name="catLeaveDayTypeId"></param>
/// <returns></returns>
public bool DeleteCatLeaveDayType(int catLeaveDayTypeId)
{
using (var context = new VnrAttendanceDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_LeaveDayTypeRepository repo = new Cat_LeaveDayTypeRepository(unitOfWork);
Cat_LeaveDayType catLeaveDayType = new Cat_LeaveDayType();
catLeaveDayType = repo.GetById(catLeaveDayTypeId);
try
{
repo.Remove(catLeaveDayType);
repo.SaveChanges();
return true;
}
catch
{
return false;
}
}
}
public DataTable ReportMealTimeDetailNoEat(List<Guid?> lstWorkPlace, String status, String codeEmp, Boolean isCludeEmpQuit,
DateTime dateStart, DateTime dateEndSearch, List<Guid?> orgIDs)
{
List<Can_ReportMealTimeDetailNoEatEntity> lstreportMealTimeDetailNoEatEntity = new List<Can_ReportMealTimeDetailNoEatEntity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
DateTime dateEnd = dateEndSearch.AddDays(1).AddMilliseconds(-1);
var repomealRecordMissing = new Can_MealRecordMissingRepository(unitOfWork);
var mealRecordMissings = repomealRecordMissing.FindBy(s => s.ProfileID != null && s.WorkDate != null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd
&& s.MealAllowanceTypeSettingID == null)
.Select(s => new { s.ProfileID, s.WorkDate, s.Status, s.MealAllowanceTypeSettingID, s.OrgStructureID }).ToList();
DataTable table = GetSchemaRptMealTimeDetailNoEat();
if (mealRecordMissings == null && mealRecordMissings.Count < 1)
{
return table;
}
var profileIds = mealRecordMissings.Select(s => s.ProfileID.Value).Distinct().ToList();
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoProfile.FindBy(s => profileIds.Contains(s.ID)).ToList();
if (orgIDs != null && orgIDs[0] != null)
{
profiles = profiles.Where(hr => hr.OrgStructureID != null && orgIDs.Contains(hr.OrgStructureID)).ToList();
}
if (lstWorkPlace[0] != Guid.Empty)
{
profiles = profiles.Where(wp => wp.WorkPlaceID != null && lstWorkPlace.Contains(wp.WorkPlaceID.Value)).ToList();
}
if (isCludeEmpQuit != true)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList();
}
if (!string.IsNullOrEmpty(codeEmp))
{
char[] ext = new char[] { ';', ',' };
List<string> codeEmpSeachs = codeEmp.Split(ext, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
if (codeEmpSeachs.Count == 1)
{
string codeEmpSearch = codeEmpSeachs[0];
profiles = profiles.Where(hr => hr.CodeEmp == codeEmpSearch).ToList();
}
else
{
profiles = profiles.Where(hr => codeEmpSeachs.Contains(hr.CodeEmp)).ToList();
}
}
profileIds = profiles.Select(s => s.ID).ToList();
mealRecordMissings = mealRecordMissings.Where(s => profileIds.Contains(s.ProfileID.Value)).ToList();
//trạng thái
if (status != null)
{
mealRecordMissings = mealRecordMissings.Where(meal => status.Contains(meal.Status)).ToList();
}
//workday
var repAttWorkday = new Att_WorkDayRepository(unitOfWork);
var lstWorkday = repAttWorkday.FindBy(wd => wd.WorkDate >= dateStart && wd.WorkDate <= dateEnd && profileIds.Contains(wd.ProfileID))
.Select(wd => new {wd.ProfileID, wd.InTime1, wd.OutTime1, wd.WorkDate }).ToList();
//danh mục leaveday type
var repLeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var leadayHLDID = repLeaveDayType.FindBy(ld => ld.Code == "HLD").Select(ld => ld.ID).FirstOrDefault();
//att_leaveday
var repAttLeaveDay = new Att_LeavedayRepository(unitOfWork);
var E_APPROVED = LeaveDayStatus.E_APPROVED.ToString();
var lstLeaveday = repAttLeaveDay.FindBy(ld => ld.LeaveDayTypeID != leadayHLDID && (profileIds.Contains(ld.ProfileID))
&& ld.Status == E_APPROVED && dateStart <= ld.DateEnd && ld.DateStart <= dateEnd)
.Select(ld => new { ld.ProfileID, ld.DateStart, ld.DateEnd }).ToList();
//can_line
var repHDTjob = new Can_LineRepository(unitOfWork);
var lstHDTjobID = repHDTjob.FindBy(hdt => hdt.IsHDTJOB == true).Select(hdt => hdt.ID).ToList();
//Can_mealRecord
var repMealRecord = new Can_MealRecordRepository(unitOfWork);
var lstMealRecord = repMealRecord.FindBy(meal => meal.LineID != null && !lstHDTjobID.Contains(meal.LineID.Value) && meal.ProfileID.HasValue && profileIds.Contains(meal.ProfileID.Value)
&& dateStart <= meal.WorkDay && meal.WorkDay <= dateEnd)
.Select(meal => new { meal.ProfileID, meal.WorkDay }).ToList();
//Allowance type setting
var repAllowanceTypeSetting = new Can_MealAllowanceTypeSettingRepository(unitOfWork);
var lstAllowanceTypeSetting = repAllowanceTypeSetting.GetAll().Select(al => new {al.ID, al.MealAllowanceTypeSettingName }).ToList();
var repoOrgType = new Cat_OrgStructureTypeRepository(unitOfWork);
var listOrgType = repoOrgType.FindBy(s => s.IsDelete == null).ToList();
var repoOrg = new Cat_OrgStructureRepository(unitOfWork);
var listOrgAll = repoOrg.GetAll().ToList();
//var repoLine = new Can_LineRepository(unitOfWork);
//var repoCatering = new Can_CateringRepository(unitOfWork);
//var repoCanteen = new Can_CanteenRepository(unitOfWork);
foreach (var mealRecordMissing in mealRecordMissings)
{
// neu co quet the an thi ko tinh
var meadRecord = lstMealRecord.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.WorkDay.Value.Date == mealRecordMissing.WorkDate.Value.Date);
if (meadRecord != null)
{
continue;
}
// neu ngay do dk nghi thi ko tinh
var leaveday = lstLeaveday.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.DateStart <= mealRecordMissing.WorkDate && mealRecordMissing.WorkDate <= s.DateEnd);
if (leaveday != null)
{
continue;
}
// neu ngay do ko di lam thi ko tinh
var workday = lstWorkday.FirstOrDefault(s => s.ProfileID == mealRecordMissing.ProfileID && s.WorkDate.Date == mealRecordMissing.WorkDate.Value.Date);
if (workday == null)
{
continue;
}
DataRow row = table.NewRow();
if (workday != null && workday.InTime1 != null)
{
row["TimeIn"] = workday.InTime1;
}
if (workday != null && workday.OutTime1 != null)
{
row["TimeOut"] = workday.OutTime1;
}
var profile = profiles.FirstOrDefault(s => s.ID == mealRecordMissing.ProfileID);
if (profile != null)
{
var type = lstAllowanceTypeSetting.FirstOrDefault(s => s.ID == mealRecordMissing.MealAllowanceTypeSettingID);
var org = listOrgAll.FirstOrDefault(s => profile != null && s.ID == mealRecordMissing.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType);
var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType);
var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType);
var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType);
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeDepartment] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Can_ReportCardByLocationEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Can_ReportCardByLocationEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Can_ReportCardByLocationEntity.FieldNames.Date] = mealRecordMissing.WorkDate;
row[Can_ReportCardByLocationEntity.FieldNames.DateFrom] = dateStart;
row[Can_ReportCardByLocationEntity.FieldNames.DateTo] = dateEnd;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.DateExport] = DateTime.Now;
row[Can_ReportMealTimeDetailNoEatEntity.FieldNames.MealAllowanceName] = type != null ? type.MealAllowanceTypeSettingName : string.Empty;
table.Rows.Add(row);
}
}
return table;
}
}
/// <summary> Hàm tính toán và save lại bảo hiểm của từng người theo tháng </summary>
/// <param name="orgs"></param>
/// <param name="monthCheck">Vd: 01/12/2014</param>
/// <param name="periodInsurance">Loại tạm thời , Loại Chính Thức</param>
public void AnalyzeAndSaveInsuranceByMonth(string orgs, DateTime monthCheck, string periodInsurance, string codeEmp, List<Guid> socialInsPlaceIDs,string userLogin)
{
/*
* Goal(tính toán và save lại bảo hiểm của từng NV theo tháng và phòng ban (Tạo mới hoặc update Ins_ProfileInsuranceMonthly))
* Steps :
* - Step1 : Lấy DS NV theo phòng ban và theo tháng được chọn
* - Step2 : xử lý tính ra tháng bắt đầu join đóng BHXH,BHYT,BHTN
* - Step3 : Set giá trị cho IsLeaveNonWorkday trong profile (cho nghỉ >=14 ngày)
* - Step4 : kiểm tra xem có đóng BHXH,BHYT,BHTN
* - Step5 : Tính lương và mức đóng của BHXH,BHYT,BHTN
* - Step6 : Tạo mới hoặc cập nhật dữ liệu vào bảng Ins_ProfileInsuranceMonthly
*/
//ngày 1 tháng N
var beginMonth = new DateTime(monthCheck.Year, monthCheck.Month, 1);
//ngày 31 tháng N
DateTime endMonth = beginMonth.AddMonths(1).AddMinutes(-1);
var step = 200;
monthCheck = new DateTime(monthCheck.Year, monthCheck.Month, 1);
var lstProfile = new List<Hre_ProfileEntity>();
using (var context = new VnrHrmDataContext())
{
#region Khai báo
var status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoSalInsuranceSalary = new Sal_InsuranceSalaryRepository(unitOfWork);
var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork);
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoAtt_Roster = new Att_RosterRepository(unitOfWork);
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var repoIns_InsuranceRecord = new Ins_InsuranceRecordRepository(unitOfWork);
var profileIDByCodeEmp = Guid.Empty;
#endregion
#region phân tích theo mã NV (từ đk tìm kiếm)
if (!string.IsNullOrEmpty(codeEmp))
{
var profileByCodeEmp = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, p => p.IsDelete == null && p.CodeEmp == codeEmp).FirstOrDefault();
if (profileByCodeEmp != null)
{
profileIDByCodeEmp = profileByCodeEmp.ID;
}
else
{
return;
}
}
#endregion
//lay ds ngay nghi
var dayOffMonthChecks = unitOfWork.CreateQueryable<Cat_DayOff>(Guid.Empty, m => m.DateOff.Year == monthCheck.Year
&& m.DateOff.Month == monthCheck.Month).ToList();
#region lay hop dong theo phòng ban
//List<object> contractPara = new List<object>();
//contractPara.AddRange(new object[16]);
//contractPara[0] = null;
//contractPara[1] = null;
//contractPara[2] = orgs;
//contractPara[3] = null;
//contractPara[4] = null;
//contractPara[5] = null;
//contractPara[6] = null;
//contractPara[7] = null;
//contractPara[8] = null;
//contractPara[9] = null;
//contractPara[10] = null;
//contractPara[11] = null;
//contractPara[12] = null;
//contractPara[13] = null;
//contractPara[14] = 1;
//contractPara[15] = int.MaxValue;
//if (profileIDByCodeEmp != Guid.Empty)
//{
// contractPara[1] = codeEmp;
// contractPara[2] = null;
//}
//var contracts = GetData<Hre_ContractEntity>(contractPara, ConstantSql.hrm_hr_sp_get_Contract, ref status).Translate<Hre_Contract>().ToList();
var contracts = new List<Hre_Contract>();
#endregion
#region Get Profiles by orgs
var listObj = new List<object> { orgs, string.Empty, string.Empty };
if (profileIDByCodeEmp != Guid.Empty)
{
listObj[0] = null;
listObj[2] = codeEmp;
}
if (!string.IsNullOrEmpty(codeEmp))
{
lstProfile = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.CodeEmp == codeEmp).ToList().Translate<Hre_ProfileEntity>();
}
else if (orgs != null)
{
var lstOrderNumberORG = orgs.Split(',').Select(s => int.Parse(s)).Distinct().ToList();
var orgObj = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty).Select(p => p.ID).ToList();
var orgStructureIDs = unitOfWork.CreateQueryable<Cat_OrgStructure>(Guid.Empty, m => lstOrderNumberORG.Contains(m.OrderNumber)).Select(m => m.ID).ToList();
lstProfile = new List<Hre_ProfileEntity>();
foreach (var orgIds in orgStructureIDs.Chunk(step))
{
var profileByOrgs = unitOfWork.CreateQueryable<Hre_Profile>(Guid.Empty, m => m.OrgStructureID.HasValue && orgIds.Contains(m.OrgStructureID.Value)).ToList().Translate<Hre_ProfileEntity>();
lstProfile.AddRange(profileByOrgs);
}
}
#endregion
if (socialInsPlaceIDs != null)
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs != null && socialInsPlaceIDs.Any())
{
lstProfile = lstProfile.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
#region lay ds inusuranceSalary by profile list
//ds profileId theo phong ban
List<Guid> lstProfileIDs = lstProfile.Select(m => m.ID).ToList();
var lstInsuranceSalaryByProfile = new List<Sal_InsuranceSalary>();
foreach (var profileIds in lstProfileIDs.Chunk(step))
{
var lstInsuranceSalaryByProfileTemp = unitOfWork.CreateQueryable<Sal_InsuranceSalary>(Guid.Empty, m => m.InsuranceAmount != null && m.ProfileID != null
&& m.DateEffect <= endMonth && profileIds.Contains(m.ProfileID.Value)).ToList();
lstInsuranceSalaryByProfile.AddRange(lstInsuranceSalaryByProfileTemp);
var contractTemp = unitOfWork.CreateQueryable<Hre_Contract>(Guid.Empty, m => profileIds.Contains(m.ProfileID)).ToList();
contracts.AddRange(contractTemp);
}
if (lstInsuranceSalaryByProfile.Any())
{
lstProfileIDs = lstInsuranceSalaryByProfile.Select(p => p.ProfileID ?? Guid.Empty).ToList();
lstProfile = lstProfile.Where(p => lstProfileIDs.Contains(p.ID)).ToList();
}
#endregion
#region ds NV đóng Bảo Hiểm theo phòng ban và tháng (Ins_ProfileInsuranceMonthly)
var listInsMonthlyObj = new List<object> { orgs, monthCheck, null, 1, int.MaxValue - 1 };
if (Common.UseDataBaseName == DATABASETYPE.SQLSERVER.ToString())
{
listInsMonthlyObj.Add("id");
}
var lstProfileInsuranceMonthlyInDb = GetData<Ins_ProfileInsuranceMonthlyEntity>(listInsMonthlyObj, ConstantSql.hrm_ins_sp_get_ProfileInsMonthly,userLogin, ref status).Translate<Ins_ProfileInsuranceMonthly>();
// lstProfileInsuranceMonthlyInDb = lstProfileInsuranceMonthlyInDb.Where(m => m.PaybackID == null ).ToList();
#endregion
#region Tháng bắt đầu tham gia bảo hiểm
SetMonthJoinInsuranceByProfile(lstProfile, lstInsuranceSalaryByProfile, endMonth);
#endregion
#region Kiem tra co dong bao hiem ko?
//Kiểm tra xem co đóng BHXH,BHYT,BHTN
SetIsHaveInsurnceByProfileByMonth(lstProfile, monthCheck, contracts,userLogin);
if (lstProfile.Any())
{
lstProfileIDs = lstProfile.Select(p => p.ID).ToList();
}
#endregion
#region Nghỉ 14 ngày
List<Hre_ProfileEntity> lstProfileEntities = new List<Hre_ProfileEntity>();
List<string> lstLeaveInsuranceType = String.Join(",", leaveDayInsuranceTypes).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
if (HasLeaveGreater14day)
{
string E_APPROVE = HDTJobStatus.E_APPROVE.ToString();
var lstProfilesIdChunks = lstProfileIDs;
var leaveDayTypeIDs = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, m => m.PaidRate == 0 || lstLeaveInsuranceType.Contains(m.InsuranceType)).Select(m => m.ID).ToList();
foreach (var profileIDs in lstProfilesIdChunks.Chunk(step))
{
var lstProfileEntitiesTemp = lstProfile.Where(p => profileIDs.Contains(p.ID)).ToList();
#region workday từ 1->31 tháng N theo các NV (không có quẹt thẻ)
var lstWorkday = unitOfWork.CreateQueryable<Att_Workday>(Guid.Empty, m => m.InTime1 == null && m.OutTime1 == null && m.WorkDate >= beginMonth
&& m.WorkDate <= endMonth && profileIDs.Contains(m.ProfileID)).Select(m => new CustomWorkDayEntity() { ProfileID = m.ProfileID, InTime1 = m.InTime1, OutTime1 = m.OutTime1, WorkDate = m.WorkDate }).ToList();
#endregion
#region Leaveday (ngày nghỉ trong khoảng 1->31 tháng N) theo NV
string app = LeaveDayStatus.E_APPROVED.ToString();
var lstLeaveDay = unitOfWork.CreateQueryable<Att_LeaveDay>(Guid.Empty, m => m.Status == app && m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID))
.Select(m => new CustomLeavedayEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, LeaveDayTypeID = m.LeaveDayTypeID }).ToList();
#endregion
//paidrate = 0 :ko tra luong
var lstLeavedayTypeRateZero = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, p => p.PaidRate == 0).ToList();
#region lấy ds chứng từ bảo hiểm
var insRecords = unitOfWork.CreateQueryable<Ins_InsuranceRecord>(Guid.Empty, m =>
m.DateStart != null && m.DateEnd != null
&& m.DateStart <= endMonth && m.DateEnd >= beginMonth && profileIDs.Contains(m.ProfileID))
.Select(m => new CustomInsuranceRecordEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart.Value, DateEnd = m.DateEnd.Value, InsuranceType = m.InsuranceType }).ToList();
#endregion
var approved = RosterStatus.E_APPROVED.ToString();
var rosters = unitOfWork.CreateQueryable<Att_Roster>(Guid.Empty, p => p.Status == approved
&& (p.DateStart <= endMonth && p.DateEnd >= beginMonth)
&& profileIDs.Contains(p.ProfileID))
.Select(m => new CustomRosterEntity() { ProfileID = m.ProfileID, DateStart = m.DateStart, DateEnd = m.DateEnd, MonShiftID = m.MonShiftID, TueShiftID = m.TueShiftID, WedShiftID = m.WedShiftID, ThuShiftID = m.ThuShiftID, FriShiftID = m.FriShiftID, SatShiftID = m.SatShiftID, SunShiftID = m.SunShift2ID })
.ToList();
//kt co nghỉ hơn 14 ngay ko?
SetLeaveNonWorkdayByProfile(lstProfileEntitiesTemp, beginMonth, endMonth, lstWorkday, lstLeaveDay, lstLeavedayTypeRateZero, insRecords, rosters, dayOffMonthChecks);
//Hàm do Triet.Mai Hỗ trợ. tính ra số ngày không làm HDTJOb
// "SC, SU, SM, SD, DP, D, M, AL, TSC,TAS"
lstLeaveDay = lstLeaveDay.Where(m => m.LeaveDayTypeID != null && leaveDayTypeIDs.Contains(m.LeaveDayTypeID.Value)).ToList();
var lstHDTJob = unitOfWork.CreateQueryable<Hre_HDTJob>(Guid.Empty, m => m.Status == E_APPROVE && m.DateFrom <= endMonth && m.DateTo >= beginMonth
&& m.ProfileID.HasValue && profileIDs.Contains(m.ProfileID.Value)).ToList();
GetHDTJobDayByProfile(lstProfileEntitiesTemp, leaveDayTypeIDs, monthCheck, rosters, lstLeaveDay, lstHDTJob);
lstProfileEntities.AddRange(lstProfileEntitiesTemp);
}
}
lstProfile = lstProfileEntities;
#endregion
#region tính lương , mức đóng BHXH,BHYT,BHTN
SetMoneyInsuranceByProfileByMonth(lstProfile, lstInsuranceSalaryByProfile, endMonth, orgs, contracts,userLogin);
#endregion
#region Save Change
var lstProfileInsuranceMonthlyAddNew = new List<Ins_ProfileInsuranceMonthly>();
var lstProfileInsuranceMonthlyModify = new List<Ins_ProfileInsuranceMonthly>();
foreach (var profile in lstProfile)
{
if (lstProfileInsuranceMonthlyInDb.Where(m => m.ProfileID == profile.ID && m.PaybackID != null).FirstOrDefault() != null)
{
continue;
}
Ins_ProfileInsuranceMonthly insuranceByProfile = lstProfileInsuranceMonthlyInDb
.FirstOrDefault(m => m.ProfileID == profile.ID && m.PaybackID == null);
if (insuranceByProfile != null) //Update cái dữ liệu cũ
{
insuranceByProfile.WorkPlaceID = profile.WorkPlaceID;
insuranceByProfile.SocialInsPlaceID = profile.SocialInsPlaceID;
insuranceByProfile.IsSocialInsurance = profile.IsHaveInsSocial;
insuranceByProfile.IsHealthInsurance = profile.IsHaveInsHealth;
insuranceByProfile.IsUnEmpInsurance = profile.IsHaveInsUnEmp;
insuranceByProfile.IsPregnant = profile.IsPregnant;
insuranceByProfile.MoneySocialInsurance = profile.MoneyInsuranceSocial;
insuranceByProfile.MoneyHealthInsurance = profile.MoneyInsuranceHealth;
insuranceByProfile.MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp;
insuranceByProfile.SalaryInsurance = profile.MoneyInsuranceTotal;
insuranceByProfile.SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal;
insuranceByProfile.SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal;
insuranceByProfile.IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays;
insuranceByProfile.JobName = profile.JobName;
insuranceByProfile.AmountHDTIns = profile.AmountHDTIns;
insuranceByProfile.HDTGroupCode = profile.HDTJobGroupCode;
insuranceByProfile.TypeGetData = periodInsurance;
#region Tung.Ly set các tỉ lệ và mức đóng
insuranceByProfile.Allowance1 = profile.Allowance1;
insuranceByProfile.Allowance2 = profile.Allowance2;
insuranceByProfile.Allowance3 = profile.Allowance3;
insuranceByProfile.Allowance4 = profile.Allowance4;
insuranceByProfile.AmountChargeIns = profile.AmountChargeIns;
insuranceByProfile.SocialInsComRate = profile.SocialInsComRate;
insuranceByProfile.SocialInsComAmount = profile.SocialInsComAmount;
insuranceByProfile.SocialInsEmpRate = profile.SocialInsEmpRate;
insuranceByProfile.SocialInsEmpAmount = profile.SocialInsEmpAmount;
insuranceByProfile.HealthInsComRate = profile.HealthInsComRate;
insuranceByProfile.HealthInsComAmount = profile.HealthInsComAmount;
insuranceByProfile.HealthInsEmpRate = profile.HealthInsEmpRate;
insuranceByProfile.HealthInsEmpAmount = profile.HealthInsEmpAmount;
insuranceByProfile.UnemployComRate = profile.UnemployComRate;
insuranceByProfile.UnemployComAmount = profile.UnemployComAmount;
insuranceByProfile.UnemployEmpRate = profile.UnemployEmpRate;
insuranceByProfile.UnemployEmpAmount = profile.UnemployEmpAmount;
insuranceByProfile.IsPayback = false;
insuranceByProfile.MonthYearEffect = monthCheck;
#endregion
lstProfileInsuranceMonthlyModify.Add(insuranceByProfile);//add vao list (Modify)
}
else //Tạo mới
{
var newInsurance = new Ins_ProfileInsuranceMonthly
{
ProfileID = profile.ID,
WorkPlaceID = profile.WorkPlaceID,
SocialInsPlaceID = profile.SocialInsPlaceID,
IsSocialInsurance = profile.IsHaveInsSocial,
IsHealthInsurance = profile.IsHaveInsHealth,
IsUnEmpInsurance = profile.IsHaveInsUnEmp,
IsPregnant = profile.IsPregnant,
MoneySocialInsurance = profile.MoneyInsuranceSocial,
MoneyHealthInsurance = profile.MoneyInsuranceHealth,
MoneyUnEmpInsurance = profile.MoneyInsuranceUnEmp,
IsDecreaseWorkingDays = profile.IsDecreaseWorkingDays,
SalaryInsurance = profile.MoneyInsuranceTotal,
AmountHDTIns = profile.AmountHDTIns,
HDTGroupCode = profile.HDTJobGroupCode,
SalaryHealthInsurance = profile.MoneyInsuranceHealthTotal,
SalaryUnEmpInsurance = profile.MoneyInsuranceUnEmpTotal,
TypeGetData = periodInsurance,
MonthYear = monthCheck,
IsPayback = false,
MonthYearEffect = monthCheck,
#region Tung.Ly set các tỉ lệ và mức đóng
Allowance1 = profile.Allowance1,
Allowance2 = profile.Allowance2,
Allowance3 = profile.Allowance3,
Allowance4 = profile.Allowance4,
AmountChargeIns = profile.AmountChargeIns,
SocialInsComRate = profile.SocialInsComRate,
SocialInsComAmount = profile.SocialInsComAmount,
SocialInsEmpRate = profile.SocialInsEmpRate,
SocialInsEmpAmount = profile.SocialInsEmpAmount,
HealthInsComRate = profile.HealthInsComRate,
HealthInsComAmount = profile.HealthInsComAmount,
HealthInsEmpRate = profile.HealthInsEmpRate,
HealthInsEmpAmount = profile.HealthInsEmpAmount,
UnemployComRate = profile.UnemployComRate,
UnemployComAmount = profile.UnemployComAmount,
UnemployEmpRate = profile.UnemployEmpRate,
UnemployEmpAmount = profile.UnemployEmpAmount,
JobName = profile.JobName,
#endregion
};
lstProfileInsuranceMonthlyAddNew.Add(newInsurance);//add new
}
}
if (lstProfileInsuranceMonthlyAddNew.Any())
{
repoInsMonthly.Add(lstProfileInsuranceMonthlyAddNew);
}
if (lstProfileInsuranceMonthlyModify.Any())
{
repoInsMonthly.Edit(lstProfileInsuranceMonthlyModify);
}
repoInsMonthly.SaveChanges();
#endregion
}
}
