public IQueryable<Cat_JobTitle> GetCatJobTitles()
{
using (var context = new VnrHrmHrDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork);
return repo.GetAllCatJobTitles().Where(i => i.IsDelete == null);
}
}
public Cat_JobTitle GetByIdCatJobTitles(int id)
{
using (var context = new VnrHrmHrDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork);
Cat_JobTitle CatJobTitle = new Cat_JobTitle();
CatJobTitle = repo.GetById(id);
if (CatJobTitle.IsDelete == true) CatJobTitle = null;
return CatJobTitle;
}
}
public bool AddCatJobTitles(Cat_JobTitle CatJobTitle)
{
using (var context = new VnrHrmHrDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork);
try
{
repo.Add(CatJobTitle);
repo.SaveChanges();
return true;
}
catch
{
return false;
}
}
}
public bool DeleteCatJobTitle(int CatJobTitleId)
{
using (var context = new VnrHrmHrDataContext())
{
IUnitOfWork unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
Cat_JobTitleRepository repo = new Cat_JobTitleRepository(unitOfWork);
Cat_JobTitle CatJobTitle = new Cat_JobTitle();
CatJobTitle = repo.GetById(CatJobTitleId);
try
{
repo.Remove(CatJobTitle);
repo.SaveChanges();
return true;
}
catch
{
return false;
}
}
}
public List<Hre_ReportProfileQuitEntity> GetReportProfileQuit(DateTime? DateFrom, DateTime? DateToSearch, List<Guid> lstProfileIDs)
{
List<Hre_ReportProfileQuitEntity> lstReportProfileQuit = new List<Hre_ReportProfileQuitEntity>();
DateTime DateTo = DateToSearch.Value.AddDays(1).AddMinutes(-1);
if (lstProfileIDs == null)
{
return lstReportProfileQuit;
}
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoorgs = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoorgs.FindBy(s => s.IsDelete == null && s.Code != null).ToList();
var repopostions = new Cat_PositionRepository(unitOfWork);
var postions = repopostions.FindBy(s => s.IsDelete == null && s.PositionName != null).ToList();
var repojobtitles = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repojobtitles.FindBy(s => s.IsDelete == null && s.JobTitleName != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var reporesreason = new Cat_ResignReasonRepository(unitOfWork);
var lstResReason = reporesreason.FindBy(s => s.IsDelete == null && s.ResignReasonName != null).ToList();
var repoprofiles = new Hre_ProfileRepository(unitOfWork);
var profiles = repoprofiles.FindBy(s => s.IsDelete == null && s.DateQuit >= DateFrom && s.DateQuit <= DateTo && lstProfileIDs.Contains(s.ID))
.Select(s => new
{
s.ID,
s.DateQuit,
s.OrgStructureID,
s.ProfileName,
s.CodeEmp,
s.PositionID,
s.JobTitleID,
s.IDNo,
s.CodeAttendance,
s.DateEndProbation,
s.ProbExtendDate,
s.IDDateOfIssue,
s.IDPlaceOfIssue,
s.DateHire,
s.WorkPlaceID,
s.PAStreet,
s.EmpTypeID,
s.RequestDate,
s.ResReasonID,
s.Gender
}).ToList();
foreach (var profile in profiles)
{
Hre_ReportProfileQuitEntity ReportProfileQuit = new Hre_ReportProfileQuitEntity();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var jobtitlebypro = jobtitles.Where(s => s.ID == profile.JobTitleID).FirstOrDefault();
var positiobbypro = postions.Where(s => s.ID == profile.PositionID).FirstOrDefault();
var resReasonbypro = lstResReason.Where(s => s.ID == profile.ResReasonID).FirstOrDefault();
ReportProfileQuit.CodeEmp = profile.CodeEmp;
ReportProfileQuit.ProfileName = profile.ProfileName;
ReportProfileQuit.CodeOrg = org != null ? org.Code : null;
ReportProfileQuit.OrgStructureName = org != null ? org.OrgStructureName : null;
ReportProfileQuit.JobTitleName = jobtitlebypro != null ? jobtitlebypro.JobTitleName : null;
ReportProfileQuit.PositionName = positiobbypro != null ? positiobbypro.PositionName : null;
ReportProfileQuit.DateHire = profile.DateHire;
ReportProfileQuit.RequestDate = profile.RequestDate;
ReportProfileQuit.DateQuit = profile.DateQuit;
ReportProfileQuit.ResignReasonName = resReasonbypro != null ? resReasonbypro.ResignReasonName : null;
lstReportProfileQuit.Add(ReportProfileQuit);
}
return lstReportProfileQuit;
}
}
public List<Hre_ReportExpiryContractEntity> GetReportExpiryContract(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs)
{
List<Hre_ReportExpiryContractEntity> lstReportExpiryContract = new List<Hre_ReportExpiryContractEntity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoContract = new Hre_ContractRepository(unitOfWork);
var lstContract = repoContract.FindBy(s => s.DateEnd != null && s.DateEnd >= DateFrom && s.DateEnd <= DateTo && lstProfileIDs.Contains(s.ProfileID)).ToList();
if (lstContract == null)
{
return lstReportExpiryContract;
}
List<Guid> lstProfileIDsbycontract = lstContract.Select(s => s.ProfileID).ToList();
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var lstAllprofile = repoProfile.FindBy(s => lstProfileIDsbycontract.Contains(s.ID)).Select(s => new { s.ID, s.ProfileName, s.CodeEmp, s.OrgStructureID, s.PositionID, s.JobTitleID }).ToList();
var repoOrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var lstOrgStructure = repoOrgStructure.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.OrgStructureName }).ToList();
var repoPosition = new Cat_PositionRepository(unitOfWork);
var lstPosition = repoPosition.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.PositionName }).ToList();
var repoJobtitle = new Cat_JobTitleRepository(unitOfWork);
var lstJobtitle = repoJobtitle.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.JobTitleName }).ToList();
var repoContractType = new Cat_ContractTypeRepository(unitOfWork);
var lstContractType = repoContractType.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.ContractTypeName, s.ContractNextID }).ToList();
foreach (var contract in lstContract)
{
Hre_ReportExpiryContractEntity ReportExpiryContract = new Hre_ReportExpiryContractEntity();
var profileByContract = lstAllprofile.Where(s => contract.ProfileID == s.ID).FirstOrDefault();
if (profileByContract == null)
continue;
var orgByProfile = lstOrgStructure.Where(s => profileByContract.OrgStructureID == s.ID).FirstOrDefault();
var positionByProfile = lstPosition.Where(s => contract.PositionID == s.ID).FirstOrDefault();
var jobtitleByProfile = lstJobtitle.Where(s => contract.JobTitleID == s.ID).FirstOrDefault();
var contractTypeByContract = lstContractType.Where(s => s.ID == contract.ContractTypeID).FirstOrDefault();
ReportExpiryContract.CodeEmp = profileByContract.CodeEmp;
ReportExpiryContract.ProfileName = profileByContract.ProfileName;
ReportExpiryContract.OrgStructureName = orgByProfile != null ? orgByProfile.OrgStructureName : "";
ReportExpiryContract.JobTitleName = jobtitleByProfile != null ? jobtitleByProfile.JobTitleName : "";
ReportExpiryContract.PositionName = positionByProfile != null ? positionByProfile.PositionName : "";
ReportExpiryContract.ContractTypeName = contractTypeByContract != null ? contractTypeByContract.ContractTypeName : "";
ReportExpiryContract.DateSigned = contract.DateSigned;
ReportExpiryContract.DateStart = contract.DateStart;
ReportExpiryContract.DateEnd = contract.DateEnd;
lstReportExpiryContract.Add(ReportExpiryContract);
}
return lstReportExpiryContract;
}
}
/// <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;
}
}
/// <summary>
/// Lấy Dữ Liệu BC GetReportDetailForgetCard
/// </summary>
/// <returns></returns>
public DataTable GetReportDetailForgetCard(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs, List<Guid?> ShiftIDs, string userExport)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
DataTable table = CreateReportDetailForgetCardSchema();
List<string> lstType = new List<string> {
WorkdayType.E_MISS_IN.ToString(),
WorkdayType.E_MISS_IN_OUT.ToString(),
WorkdayType.E_MISS_OUT.ToString()
};
var workDays = repoAtt_Workday.FindBy(s =>
DateFrom <= s.WorkDate && s.WorkDate <= DateTo
&& lstType.Contains(s.Type))
.Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.FirstInTime, s.LastOutTime, s.Type, s.InTime1, s.OutTime1 }).ToList();
if (lstProfileIDs != null)
{
workDays = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).ToList();
}
var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList();
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoHre_Profile.FindBy(s => (s.DateQuit == null || s.DateQuit > DateTo) && 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_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_Shift = new Cat_ShiftRepository(unitOfWork);
var shifts = repoCat_Shift.GetAll().ToList();
if (ShiftIDs[0] != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList();
}
List<Guid> guids = profiles.Select(s => s.ID).ToList();
if (guids.Count > 0)
{
workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList();
}
foreach (var profile in profiles)
{
for (DateTime date = DateFrom.Value.Date; date <= DateTo; date = date.AddDays(1))
{
var workDayProfiles = workDays.Where(s => s.WorkDate.Date == date && s.ProfileID == profile.ID).ToList();
if (workDayProfiles.Count > 0)
{
DataRow row = table.NewRow();
//var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
//var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
Guid? orgId = 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_ReportDetailForgetCardEntity.FieldNames.GroupCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportDetailForgetCardEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportDetailForgetCardEntity.FieldNames.Division] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportDetailForgetCardEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportDetailForgetCardEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportDetailForgetCardEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportDetailForgetCardEntity.FieldNames.DateFrom] = DateFrom;
row[Att_ReportDetailForgetCardEntity.FieldNames.DateTo] = DateTo;
row[Att_ReportDetailForgetCardEntity.FieldNames.DateExport] = DateTime.Today;
row[Att_ReportDetailForgetCardEntity.FieldNames.UserExport] = userExport;
var workDay1 = workDayProfiles.FirstOrDefault();
row[Att_ReportDetailForgetCardEntity.FieldNames.Date] = date;
if (workDay1.ShiftID != null)
{
var shift = shifts.Where(m => m.ID == workDay1.ShiftID).FirstOrDefault();
row[Att_ReportDetailForgetCardEntity.FieldNames.ShiftName] = shift != null ? shift.ShiftName : string.Empty;
}
if (workDay1.Type == WorkdayType.E_MISS_IN.ToString())
{
row[Att_ReportDetailForgetCardEntity.FieldNames.OutTime] = workDay1.LastOutTime != null ? workDay1.LastOutTime.Value : DateTime.MinValue;
}
else if (workDay1.Type == WorkdayType.E_MISS_OUT.ToString())
{
row[Att_ReportDetailForgetCardEntity.FieldNames.InTime] = workDay1.FirstInTime != null ? workDay1.FirstInTime.Value : DateTime.MaxValue;
}
row[Att_ReportDetailForgetCardEntity.FieldNames.ScanType] = workDay1.Type;
table.Rows.Add(row);
}
}
}
return table;
}
#region Code Cũ
//#region logic hien tai
////1. Chi tinh dc workday thieu in thieu out hoac thieu in out
////2. khong tinh dc nhưng quẹt thẻ da them bang tay boi vi khi them bang tay thì workday da du inout hok len dc bc
//#endregion
//List<Att_ReportDetailForgetCardEntity> lstReportDetailForgetCardEntity = new List<Att_ReportDetailForgetCardEntity>();
//string E_MISS_IN = WorkdayType.E_MISS_IN.ToString();
//string E_MISS_OUT = WorkdayType.E_MISS_OUT.ToString();
//string E_MISS_IN_OUT = WorkdayType.E_MISS_IN_OUT.ToString();
//#region getData
//var lstWorkday = new List<Att_Workday>().Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList();
//using (var context = new VnrHrmDataContext())
//{
// var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
// var repo = new Att_WorkDayRepository(unitOfWork);
// if (lstProfileIDs != null && lstProfileIDs.Count > 0)
// {
// lstWorkday = repo.FindBy(m => m.WorkDate >= DateFrom && m.WorkDate < DateTo
// && m.ProfileID != null && lstProfileIDs.Contains(m.ProfileID)
// && (m.Type == E_MISS_IN || m.Type == E_MISS_OUT || m.Type == E_MISS_IN_OUT))
// .Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList();
// }
// else
// {
// lstWorkday = repo.FindBy(m => (m.WorkDate >= DateFrom && m.WorkDate < DateTo)
// && (m.Type == E_MISS_IN || m.Type == E_MISS_OUT || m.Type == E_MISS_IN_OUT))
// .Select(m => new { m.ProfileID, m.WorkDate, m.ShiftID, m.Type, m.FirstInTime, m.LastOutTime }).ToList();
// }
//}
//List<Guid> lstprofilebyworkday = lstWorkday.Select(m => m.ProfileID).ToList();
//var lstProfile = new List<Hre_Profile>().Select(m => new { m.ID, m.OrgStructureID, m.CodeEmp, m.ProfileName });
//using (var context = new VnrHrmDataContext())
//{
// var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
// var repo = new Hre_ProfileRepository(unitOfWork);
// if (lstprofilebyworkday != null && lstprofilebyworkday.Count > 0)
// {
// lstProfile = repo.FindBy(m => lstprofilebyworkday.Contains(m.ID)).Select(m => new { m.ID, m.OrgStructureID, m.CodeEmp, m.ProfileName }).ToList();
// }
//}
//var lstShift = new List<Cat_Shift>().Select(m => new { m.ID, m.ShiftName, m.InTime, m.CoOut }).ToList();
//using (var context = new VnrHrmDataContext())
//{
// var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
// var repo = new Cat_ShiftRepository(unitOfWork);
// lstShift = repo.GetAll().Select(m => new { m.ID, m.ShiftName, m.InTime, m.CoOut }).ToList();
//}
//#endregion
//#region get org nhieu cap
//List<OrgTiny> lstOrgAll = new List<OrgTiny>();
//using (var context = new VnrHrmDataContext())
//{
// var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
// var repoOrg = new Cat_OrgStructureRepository(unitOfWork);
// lstOrgAll = repoOrg.FindBy(s => s.IsDelete == null).Select(m => new OrgTiny { ID = m.ID, OrgCode = m.Code, OrgName = m.OrgStructureName, ParentID = m.ParentID, TypeID = m.TypeID }).ToList();
//}
//List<Guid?> lstOrgIDs = lstProfile.Select(m => m.OrgStructureID).Distinct().ToList();
//Dictionary<Guid?, OrgLevelTypeName> OrgNameAllLevel = Cat_OrgStructureServices.GetFullLinkOrg(lstOrgIDs, lstOrgAll);
//#endregion
//foreach (var ProfileID in lstprofilebyworkday)
//{
// if (ProfileID == null)
// continue;
// var lstWorkdayByProfile = lstWorkday.Where(m => m.ProfileID == ProfileID).ToList();
// foreach (var WorkdayByProfile in lstWorkdayByProfile)
// {
// if (WorkdayByProfile.WorkDate == null)
// {
// continue;
// }
// Att_ReportDetailForgetCardEntity ReportDetailForgetCardEntity = new Att_ReportDetailForgetCardEntity();
// var profileInfomation = lstProfile.Where(m => m.ID == ProfileID).FirstOrDefault();
// #region thông tin chung
// ReportDetailForgetCardEntity.DateFrom = DateFrom;
// ReportDetailForgetCardEntity.DateTo = DateTo;
// #endregion
// #region thông tin Profile
// if (profileInfomation != null)
// {
// ReportDetailForgetCardEntity.ProfileName = profileInfomation.ProfileName;
// ReportDetailForgetCardEntity.CodeEmp = profileInfomation.CodeEmp;
// }
// #endregion
// #region thông tin Phòng ban
// Guid? orgID = null;
// if (profileInfomation != null)
// {
// orgID = profileInfomation.OrgStructureID;
// }
// if (orgID != null)
// {
// var orgByProfile = lstOrgAll.Where(m => m.ID == orgID).FirstOrDefault();
// if (orgByProfile != null)
// {
// ReportDetailForgetCardEntity.DepartmentName = orgByProfile.OrgName;
// ReportDetailForgetCardEntity.Division = orgByProfile.OrgCode;
// }
// }
// if (orgID != null && OrgNameAllLevel.ContainsKey(orgID))
// {
// var OrgNameFull = OrgNameAllLevel[orgID];
// if (OrgNameFull != null)
// {
// ReportDetailForgetCardEntity.SectionCode = OrgNameFull.SectionCode;
// ReportDetailForgetCardEntity.GroupCode = OrgNameFull.TeamCode;
// }
// }
// #endregion
// #region thông tin chấm công
// ReportDetailForgetCardEntity.Date = WorkdayByProfile.WorkDate;
// if (WorkdayByProfile.ShiftID != null)
// {
// var Shift = lstShift.Where(m => m.ID == WorkdayByProfile.ShiftID).FirstOrDefault();
// if (Shift != null)
// {
// ReportDetailForgetCardEntity.ShiftName = Shift.ShiftName;
// }
// }
// #endregion
// #region thoi gian vao ra
// if (WorkdayByProfile.Type == E_MISS_IN)
// {
// ReportDetailForgetCardEntity.OutTime = WorkdayByProfile.LastOutTime;
// }
// else if (WorkdayByProfile.Type == E_MISS_OUT)
// {
// ReportDetailForgetCardEntity.InTime = WorkdayByProfile.FirstInTime;
// }
// ReportDetailForgetCardEntity.ScanType = WorkdayByProfile.Type;
// #endregion
// lstReportDetailForgetCardEntity.Add(ReportDetailForgetCardEntity);
// }
//}
//return lstReportDetailForgetCardEntity;
#endregion
}
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;
}
}
/// <summary>
/// Lấy Dữ Liệu BC Thống Kê Đi Muộn Về Sớm
/// </summary>
/// <returns></returns>
public List<Att_ReportSummaryLateInOutEntity> GetReportSummaryLateInOut(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> profiles, Guid[] shiftIDs, bool isIncludeQuitEmp, string codeEmp, string userExport)
{
using (var context = new VnrHrmDataContext())
{
List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoAtt_WorkDay = new Att_WorkDayRepository(unitOfWork);
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoShift = new Cat_ShiftRepository(unitOfWork);
var repoCat_TAMReasonmiss = new Cat_TAMScanReasonMissRepository(unitOfWork);
string E_LATE_EARLY = WorkdayType.E_LATE_EARLY.ToString();
string E_APPROVED = WorkdayStatus.E_APPROVED.ToString();
var workDays = new List<Att_Workday>().Select(s => new { s.ProfileID, s.WorkDate, s.LateEarlyDuration, s.ShiftID, s.InTime1, s.OutTime1, s.MissInOutReason, s.LateDuration1, s.EarlyDuration1 }).ToList();
workDays = repoAtt_WorkDay.FindBy(s => s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.Type == E_LATE_EARLY && !(s.Status == E_APPROVED && s.LateEarlyDuration == 0))
.Select(s => new { s.ProfileID, s.WorkDate, s.LateEarlyDuration, s.ShiftID, s.InTime1, s.OutTime1, s.MissInOutReason, s.LateDuration1, s.EarlyDuration1 }).ToList();
var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
#region MyRegion
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();
#endregion
var reasons = repoCat_TAMReasonmiss.FindBy(s => s.IsDelete == null);
var shifts = repoShift.FindBy(s => s.IsDelete == null);
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();
}
if (!string.IsNullOrEmpty(codeEmp))
{
profiles = profiles.Where(s => s.CodeEmp == codeEmp).ToList();
}
var key = LeaveDayStatus.E_APPROVED.ToString();
var repoLeaveDay = new Att_LeavedayRepository(unitOfWork);
var leaveDays = repoLeaveDay.FindBy(s => s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd);
List<Guid> guids = profiles.Select(s => s.ID).ToList();
workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList();
profiles = profiles.Where(s => guids.Contains(s.ID)).ToList();
List<Att_ReportSummaryLateInOutEntity> lstReportSummaryLateInOutEntity = new List<Att_ReportSummaryLateInOutEntity>();
Att_ReportSummaryLateInOutEntity reportSummaryLateInOutEntityEntity = null;
foreach (var profile in profiles)
{
for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1))
{
var workDayProfiles = workDays.Where(s => s.ProfileID == profile.ID && s.WorkDate.Date == date).ToList();
var leveDay = leaveDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.DateStart <= date && date <= s.DateEnd);
if (workDayProfiles.Count > 0 && leveDay == null)
{
var workDay = workDayProfiles.FirstOrDefault(s => s.WorkDate.Date == date);
if (workDay != null)
{
reportSummaryLateInOutEntityEntity = new Att_ReportSummaryLateInOutEntity();
Guid? orgId = profile.OrgStructureID;
var reason = reasons.FirstOrDefault(s => s.ID == workDay.MissInOutReason);
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);
reportSummaryLateInOutEntityEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
reportSummaryLateInOutEntityEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
reportSummaryLateInOutEntityEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
reportSummaryLateInOutEntityEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
reportSummaryLateInOutEntityEntity.BranchName = orgBranch != null ? orgOrg.OrgStructureName : string.Empty;
reportSummaryLateInOutEntityEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
reportSummaryLateInOutEntityEntity.TeamName = orgTeam != null ? orgOrg.OrgStructureName : string.Empty;
reportSummaryLateInOutEntityEntity.SectionName = orgSection != null ? orgOrg.OrgStructureName : string.Empty;
#region MyRegion
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
#endregion
var shift = shifts.FirstOrDefault(s => s.ID == workDay.ShiftID);
reportSummaryLateInOutEntityEntity.ProfileName = profile.ProfileName;
reportSummaryLateInOutEntityEntity.CodeEmp = profile.CodeEmp;
#region MyRegion
reportSummaryLateInOutEntityEntity.PositionCode = positon != null ? positon.Code : string.Empty;
reportSummaryLateInOutEntityEntity.JobtitleCode = jobtitle != null ? jobtitle.Code : string.Empty;
reportSummaryLateInOutEntityEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
reportSummaryLateInOutEntityEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
#endregion
reportSummaryLateInOutEntityEntity.DateFrom = dateStart;
reportSummaryLateInOutEntityEntity.Date = date;
reportSummaryLateInOutEntityEntity.DateTo = dateEnd;
reportSummaryLateInOutEntityEntity.DateExport = DateTime.Now;
reportSummaryLateInOutEntityEntity.UserExport = userExport;
reportSummaryLateInOutEntityEntity.udTAMScanReasonMiss = reason == null ? string.Empty : reason.TAMScanReasonMissName;
reportSummaryLateInOutEntityEntity.ShiftName = shift == null ? string.Empty : shift.ShiftName;
reportSummaryLateInOutEntityEntity.udInTime = workDay.InTime1 != null ? workDay.InTime1.Value : DateTime.MinValue;
reportSummaryLateInOutEntityEntity.udOutTime = workDay.OutTime1 != null ? workDay.OutTime1.Value : DateTime.MinValue;
if (workDay.LateEarlyDuration >= 120)
{
reportSummaryLateInOutEntityEntity.EarlyDurationMore2 = "X";
}
else
{
reportSummaryLateInOutEntityEntity.EarlyDurationLess2 = "X";
}
if (workDay.LateDuration1 != null && workDay.LateDuration1 > 0)
{
reportSummaryLateInOutEntityEntity.LateForWork = "X";
}
if (workDay.EarlyDuration1 != null && workDay.EarlyDuration1 > 0)
{
reportSummaryLateInOutEntityEntity.EarlyGoHome = "X";
}
lstReportSummaryLateInOutEntity.Add(reportSummaryLateInOutEntityEntity);
}
}
}
#region MyRegion
//Guid? orgId = profile.OrgStructureID;
//var attendanceProfiles = attendanceTables.Where(s => s.ProfileID == profile.ID).ToList();
//var attendaceProfileIDs = attendanceProfiles.Select(s => s.ID).ToList();
//var attendanceItemProfiles = attendanceItems.Where(s => attendaceProfileIDs.Contains(s.AttendanceTableID)).ToList();
//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);
//reportSummaryLateInOutEntityEntity.LateMinutes = attendanceItemProfiles.Sum(s => s.LateInMinutes);
//reportSummaryLateInOutEntityEntity.EarlyMinutes = attendanceItemProfiles.Sum(s => s.EarlyOutMinutes);
//reportSummaryLateInOutEntityEntity.NumTimeLate = attendanceItemProfiles.Count(s => s.LateInMinutes > 0);
//reportSummaryLateInOutEntityEntity.NumTimeEarly = attendanceItemProfiles.Count(s => s.EarlyOutMinutes > 0);
//lstReportSummaryLateInOutEntity.Add(reportSummaryLateInOutEntityEntity);
#endregion
}
return lstReportSummaryLateInOutEntity;
}
}
/// <summary>
///[Tam.Le] - Lấy Dữ Liệu BC chi tiết ngày nghỉ ốm
/// </summary>
/// <returns></returns>
public List<Att_ReportDetailLeaveSickEntity> GetReportDetailLeaveSick(DateTime dateStart, DateTime dateEnd, string OrgStructureIDs, List<Hre_ProfileEntity> profiles, List<Guid?> shiftIDs, List<Guid?> leaveDayTypeIDs, Boolean? _isIncludeQuitEmp, string userExport)
{
using (var context = new VnrHrmDataContext())
{
List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
string key = OverTimeStatus.E_APPROVED.ToString();
// var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork);
var leavedayTypes = repoCat_LeaveDayType
.FindBy(s => s.CodeStatistic != null && s.IsDelete == null).Select(s => new { s.ID, s.CodeStatistic, s.PaidRate })
.Distinct()
.ToList();
var ledvedayPaidIds = leavedayTypes
.Where(s => s.PaidRate > 0 || s.CodeStatistic == "SU" || s.CodeStatistic == "SD" || s.CodeStatistic == "SC").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();
// var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var repoAtt_LeaveDay = new CustomBaseRepository<Att_LeaveDay>(unitOfWork);
leaveDays = repoAtt_LeaveDay
.FindBy(s => s.LeaveHours > 0 && s.IsDelete == null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
.Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
#region
//if (lstProfileIDs != null && lstProfileIDs.Count > 0)
//{
// leaveDays = repoAtt_LeaveDay
// .FindBy(s => s.LeaveHours > 0 && s.IsDelete==null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && 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 => s.LeaveHours > 0 && s.IsDelete==null && ledvedayPaidIds.Contains(s.LeaveDayTypeID) && s.Status == key && dateStart <= s.DateEnd && s.DateStart <= dateEnd)
// .Select(s => new { s.ProfileID, s.LeaveDayTypeID, s.LeaveHours, s.DateStart, s.DateEnd }).ToList();
//}
#endregion
var profileIds = leaveDays.Select(s => s.ProfileID).Distinct().ToList();
// var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
var repoAtt_Workday = new CustomBaseRepository<Att_Workday>(unitOfWork);
var workDays = new List<Att_Workday>().Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd)
.Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
#region
//if (lstProfileIDs != null && lstProfileIDs.Count > 0)
//{
// workDays = repoAtt_Workday
// .FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.InTime1 != null || s.OutTime1 != null) && lstProfileIDs.Contains(s.ProfileID))
// .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
//}
//else
//{
// workDays = repoAtt_Workday.FindBy(s => profileIds.Contains(s.ProfileID) && s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && (s.InTime1 != null || s.OutTime1 != null))
// .Select(s => new { s.ProfileID, s.ShiftID, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
//}
//var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
//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();
#endregion
if (OrgStructureIDs != null)
{
profiles = profiles.Where(s => s.OrgStructureID != null).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 (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 == false)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).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 repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var repoCat_Shift = new CustomBaseRepository<Cat_Shift>(unitOfWork);
var shifts = repoCat_Shift.GetAll().ToList();
List<Att_ReportDetailLeaveSickEntity> lstReportDetailLeaveSickEntity = new List<Att_ReportDetailLeaveSickEntity>();
Att_ReportDetailLeaveSickEntity reportDetailLeaveSickEntity = null;
foreach (var profile in profiles)
{
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)
{
reportDetailLeaveSickEntity = new Att_ReportDetailLeaveSickEntity();
Guid? orgId = profile.OrgStructureID;
var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date);
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);
reportDetailLeaveSickEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
reportDetailLeaveSickEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
reportDetailLeaveSickEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
reportDetailLeaveSickEntity.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);
reportDetailLeaveSickEntity.ProfileName = profile.ProfileName;
reportDetailLeaveSickEntity.CodeEmp = profile.CodeEmp;
reportDetailLeaveSickEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
reportDetailLeaveSickEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID);
var leaveday = leavdayProfiles.FirstOrDefault(s => ledvedayPaidIds.Contains(s.LeaveDayTypeID));
reportDetailLeaveSickEntity.Date = date;
reportDetailLeaveSickEntity.DateFrom = dateStart;
reportDetailLeaveSickEntity.DateTo = dateEnd;
reportDetailLeaveSickEntity.DateExport = DateTime.Now;
reportDetailLeaveSickEntity.UserExport = userExport;
if (leaveday != null)
{
//reportDetailLeaveSickEntity.Paid = leaveday.LeaveHours / 8;
reportDetailLeaveSickEntity.IsPaid = "X";
}
if (workDay != null)
{
if (workDay.ShiftID != null)
reportDetailLeaveSickEntity.ShiftName = shift.ShiftName;
if (workDay.InTime1 != null)
reportDetailLeaveSickEntity.InTime = workDay.InTime1.Value;
if (workDay.OutTime1 != null)
reportDetailLeaveSickEntity.OutTime = workDay.OutTime1.Value;
}
foreach (var leaday in leavedayTypes.Where(s => s.CodeStatistic == "SU" || s.CodeStatistic == "SD" || s.CodeStatistic == "SC"))
{
var leaday1 = leavdayProfiles.FirstOrDefault(s => s.LeaveDayTypeID == leaday.ID);
if (leaday1 != null)
{
reportDetailLeaveSickEntity.CodeStatistic = (leaday1.LeaveHours.HasValue ? leaday1.LeaveHours.Value : 0) / 8;
if (leaday.CodeStatistic == "SU")
reportDetailLeaveSickEntity.SU = "X";
else if (leaday.CodeStatistic == "SD")
reportDetailLeaveSickEntity.SD = "X";
else if (leaday.CodeStatistic == "SC")
reportDetailLeaveSickEntity.SC = "X";
}
}
lstReportDetailLeaveSickEntity.Add(reportDetailLeaveSickEntity);
}
}
}
return lstReportDetailLeaveSickEntity;
}
}
/// <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 List<Att_ProfileEntity> CheckRoster(DateTime? DateStart, DateTime? DateEnd, List<Hre_ProfileEntity> lstProfileIDsModel, bool isNotRoster, bool isDuplicateRoster, bool? isConstantRoster)
{
// List<Att_ProfileEntity> reportData = new List<Att_ProfileEntity>();
List<Guid> lstProfileIDs = lstProfileIDsModel.Select(s => s.ID).ToList();
string status = string.Empty;
List<Att_ProfileEntity> reportData = new List<Att_ProfileEntity>();
var _baseService = new BaseService();
if (DateStart > DateEnd)
{
return reportData;
}
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoLeaveDay = new Att_LeavedayRepository(unitOfWork);
List<Att_RosterEntity> listWRt = new List<Att_RosterEntity>();
string E_CANCEL = RosterStatus.E_CANCEL.ToString();
string E_REJECTED = RosterStatus.E_REJECTED.ToString();
var repoRoster = new Att_RosterRepository(unitOfWork);
var lstRoster = repoRoster.FindBy(s => s.IsDelete == null && s.DateStart <= DateEnd && s.DateEnd >= DateStart && s.Status != E_CANCEL && s.Status != E_REJECTED).ToList();
//var lstRoster = GetData<Att_RosterEntity>(lstObj, ConstantSql.hrm_att_sp_get_Roster,ref status).Where(
// s => s.StatusRoster != E_CANCEL && s.StatusRoster != E_REJECTED
// ).ToList();
var profileIds = lstRoster.Where(s => lstProfileIDs.Contains(s.ProfileID)).Select(s => s.ProfileID).Distinct().ToList();
string key = ProfileStatusSyn.E_WAITING.ToString();
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = lstProfileIDsModel.Where(s => s.IsDelete == null && s.StatusSyn != key && (s.DateQuit == null || s.DateQuit > DateStart))
.Select(s => new { s.ID, s.DateQuit, s.OrgStructureName, s.E_UNIT, s.E_DIVISION, s.E_DEPARTMENT, s.E_TEAM, s.E_SECTION, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.Email, s.CodeAttendance, s.Gender, s.BusinessPhone, s.DateEndProbation, s.DateHire }).ToList();
if (lstProfileIDs.Count != 0 && lstProfileIDs[0] != Guid.Empty)
{
profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList();
}
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != 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 orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
string E_CANCEL_LEAVE = LeaveDayStatus.E_CANCEL.ToString();
string E_REJECTED_LEAVE = LeaveDayStatus.E_REJECTED.ToString();
var lstLeaveDay = repoLeaveDay.FindBy(s => s.IsDelete == null && s.DateStart <= DateEnd && s.DateEnd >= DateStart && s.Status != E_CANCEL_LEAVE && s.Status != E_REJECTED_LEAVE).ToList();
//var lstLeaveDay = GetData<Att_LeaveDayEntity>(lstObj1, ConstantSql.hrm_att_sp_get_Leaveday, ref status).Where(
// s => s.StatusLeaveDay != E_CANCEL_LEAVE && s.StatusLeaveDay != E_REJECTED_LEAVE).ToList();
//List<Guid> guids = EntityService.GetEntityList<Att_Roster>(false, EntityService.GuidMainContext, Guid.Empty, s => s.DateStart <= DateEnd && s.DateEnd >= DateStart).Select(s => s.ProfileID).Distinct().ToList();
List<Guid> guids = lstRoster.Select(s => s.ProfileID).Distinct().ToList();
// var listProfile = GetProfilesWorking(OrgStructureID, DateStart);
if (isNotRoster == true)
{
//List<Hre_Profile> profiles = listProfile.Where(s => !guids.Contains(s.ID)).ToList();
if (isConstantRoster == true)
{
foreach (var profile in profiles)
{
for (DateTime dateCheck = DateStart.Value; dateCheck <= DateEnd.Value; dateCheck = dateCheck.AddDays(1))
{
List<Guid> ListProfileRosterIds = lstRoster.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck).Select(m => m.ProfileID).Distinct().ToList();
List<Guid> ListProfileLeaveIds = lstLeaveDay.Where(m => m.DateStart <= dateCheck && m.DateEnd >= dateCheck).Select(m => m.ProfileID).Distinct().ToList();
var profileNotIn = profiles.Where(m => !ListProfileRosterIds.Contains(m.ID) && !ListProfileLeaveIds.Contains(m.ID)).ToList();
//reportData.AddRange(profileNotIn.Translate<Att_ProfileEntity>());
//foreach (var profileNotRoster in profileNotIn)
//{
if (profileNotIn != null && profileNotIn.Count > 0)
{
Att_ProfileEntity profileResult = new Att_ProfileEntity();
//Guid? orgId = profile.OrgStructureID;
//var orgModel = orgs.Where(s => s.ID == orgId).FirstOrDefault();
//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);
profileResult.E_UNIT = profile.E_UNIT;
profileResult.E_DIVISION = profile.E_DIVISION;
profileResult.E_DEPARTMENT = profile.E_DEPARTMENT;
profileResult.E_TEAM = profile.E_TEAM;
profileResult.E_SECTION = profile.E_SECTION;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
profileResult.ID = profile.ID;
profileResult.CodeEmp = profile.CodeEmp;
profileResult.ProfileName = profile.ProfileName;
profileResult.CodeAttendance = profile.CodeAttendance;
profileResult.DateHire = profile.DateHire;
profileResult.PositionName = positon != null ? positon.PositionName : string.Empty;
profileResult.JobTitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
profileResult.Email = profile.Email;
profileResult.DateEndProbation = profile.DateEndProbation;
profileResult.DateQuit = profile.DateQuit;
if (profile.Gender == "E_FEMALE")
profileResult.Gender = "Nam";
if (profile.Gender == "E_MALE")
profileResult.Gender = "Nữ";
reportData.Add(profileResult);
}
//}
//profileResult.AddRange(profileNotIn);
}
}
reportData = reportData.Distinct().ToList();
return reportData;
}
else
{
var rs = profiles.Where(s => !guids.Contains(s.ID)).ToList().Translate<Att_ProfileEntity>();
foreach (var profile in rs)
{
Att_ProfileEntity profileResult = new Att_ProfileEntity();
//Guid? orgId = 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);
profileResult.E_UNIT = profile.E_UNIT;
profileResult.E_DIVISION = profile.E_DIVISION;
profileResult.E_DEPARTMENT = profile.E_DEPARTMENT;
profileResult.E_TEAM = profile.E_TEAM;
profileResult.E_SECTION = profile.E_SECTION;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
profileResult.ID = profile.ID;
profileResult.CodeEmp = profile.CodeEmp;
profileResult.ProfileName = profile.ProfileName;
profileResult.CodeAttendance = profile.CodeAttendance;
profileResult.DateHire = profile.DateHire;
profileResult.PositionName = positon != null ? positon.PositionName : string.Empty;
profileResult.JobTitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
profileResult.Email = profile.Email;
profileResult.DateEndProbation = profile.DateEndProbation;
profileResult.DateQuit = profile.DateQuit;
if (profile.Gender == "E_FEMALE")
profileResult.Gender = "Nam";
if (profile.Gender == "E_MALE")
profileResult.Gender = "Nữ";
reportData.Add(profileResult);
}
return reportData;
}
reportData = profiles.Distinct().ToList().Translate<Att_ProfileEntity>();
}
// danh sach trung ca
// else if (isDuplicateRoster == true)
// {
// var rosters = GetData<Att_RosterEntity>(lstObj, ConstantSql.hrm_att_sp_get_Roster, ref status).ToList();
// foreach (var pf in listProfile)
// {
// // lay ca cua nhan vien
// var listWRosterPr = rosters.Where(rt => rt.ProfileID == pf.ID).ToList();
// if (listWRosterPr.Count > 0)
// for (DateTime dx = DateStart.Value.Date; dx <= DateEnd; dx = dx.AddDays(1))
// {
// var listWRoster = listWRosterPr.Where(s => s.ProfileID == pf.ID
// && s.DateStart <= dx
// && dx <= s.DateEnd
// && s.Type == RosterType.E_DEFAULT.ToString()
// && s.Status != RosterStatus.E_REJECTED.ToString()
// && s.Status != RosterStatus.E_CANCEL.ToString()
// && s.IsDelete == null).ToList();
// if (listWRoster.Count >= 2)
// listWRt.AddRange(listWRoster);
// }
// }
// var result = listWRt.OrderBy(s => s.CodeEmp).ToList();
// // return result;
// }
//}
return reportData;
}
}
/// <summary>
/// [Hieu.Van] 01/06/2014
/// Lấy ds tất cả báo cáo Sai ca
/// </summary>
/// <returns></returns>
public List<Att_ReportWrongShiftEntity> GetReportWrongShift(DateTime dateStart, DateTime dateEnd, List<Hre_ProfileEntity> lstProfileAll, Guid[] ShiftIDs, bool isIncludeQuitEmp, string userExport)
{
using (var context = new VnrHrmDataContext())
{
List<Guid> lstProfileIDs = lstProfileAll.Select(s => s.ID).ToList();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Workday = new Att_WorkDayRepository(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_Shift = new Cat_ShiftRepository(unitOfWork);
#region MyRegion
//var workDays = repoAtt_Workday.FindBy(s => s.ShiftID != null && lstProfileIDs.Contains(s.ProfileID) && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null)
// .Select(s => new { s.ProfileID, s.WorkDate, s.ShiftID, s.InTime1, s.OutTime1, s.ShiftActual, s.ShiftApprove, s.Status }).ToList();
#endregion
var workDays = repoAtt_Workday.FindBy(s => s.ShiftID != null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd && s.IsDelete == null)
.Select(s => new { s.ProfileID, s.WorkDate, s.ShiftID, s.InTime1, s.OutTime1, s.ShiftActual, s.ShiftApprove, s.Status }).ToList();
//var profileIds = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).Select(s => s.ProfileID).Distinct().ToList();
var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList();
var profiles = lstProfileAll.Where(s => profileIds.Contains(s.ID))
.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList();
#region
// var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
//var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
//var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
#endregion
var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).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 > dateEnd).ToList();
}
#region
//if (!string.IsNullOrEmpty(CodeEmp))
//{
// lstProfileIDs = repoHre_Profile.FindBy(s => s.CodeEmp == CodeEmp).Select(p => p.ID).ToList();
//}
#endregion
List<Guid> guids = profiles.Select(s => s.ID).ToList();
// workDays = workDays.Where(s => s.ProfileID != null && guids.Contains(s.ProfileID)).ToList();
workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList();
profiles = profiles.Where(s => guids.Contains(s.ID)).ToList();
List<Att_ReportWrongShiftEntity> lstReportWrongShiftEntity = new List<Att_ReportWrongShiftEntity>();
Att_ReportWrongShiftEntity reportWrongShiftEntity = null;
#region lay danh sach phong ban theo profiles
//// var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
//var lstDepartments = new List<Cat_OrgStructure>();
//// var orgTeam = new Cat_OrgStructure();
//// var orgSection = new Cat_OrgStructure();
//var lstDivisions = new List<Cat_OrgStructure>();
//var orgIds = new List<Guid>();
//if (profiles.Any())
//{
// orgIds = profiles.Select(p => p.OrgStructureID ?? Guid.Empty).ToList();
//}
//foreach (var orgId in orgIds)
//{
// var department = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
// var division = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
// if (department != null)
// {
// lstDepartments.Add(department);
// }
// if (division != null)
// {
// lstDivisions.Add(division);
// }
//}
#endregion
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
foreach (var profile in profiles)
{
for (DateTime date = dateStart.Date; date <= dateEnd.Date; date = date.AddDays(1))
{
var workDay = workDays.FirstOrDefault(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID);
if (workDay != null && workDay.ShiftActual != workDay.ShiftID)
{
reportWrongShiftEntity = new Att_ReportWrongShiftEntity();
//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 orGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes);
var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
reportWrongShiftEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
reportWrongShiftEntity.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
reportWrongShiftEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
reportWrongShiftEntity.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty;
reportWrongShiftEntity.GroupCode = orGroup != null ? orGroup.Code : string.Empty;
reportWrongShiftEntity.GroupName = orGroup != null ? orGroup.OrgStructureName : string.Empty;
reportWrongShiftEntity.Division = orgDivision != null ? orgDivision.Code : string.Empty;
reportWrongShiftEntity.DivisionName = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
reportWrongShiftEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
reportWrongShiftEntity.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID);
var shiftActual = shifts.FirstOrDefault(s => s.ID == workDay.ShiftActual);
var shiftApprove = shifts.FirstOrDefault(s => s.ID == workDay.ShiftApprove);
reportWrongShiftEntity.ProfileName = profile.ProfileName;
reportWrongShiftEntity.CodeEmp = profile.CodeEmp;
reportWrongShiftEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
reportWrongShiftEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
reportWrongShiftEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
reportWrongShiftEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
reportWrongShiftEntity.OrgCode = org != null ? org.Code : string.Empty;
reportWrongShiftEntity.Date = date;
reportWrongShiftEntity.DateFrom = dateStart;
reportWrongShiftEntity.DateTo = dateEnd;
reportWrongShiftEntity.DateExport = DateTime.Now;
reportWrongShiftEntity.UserExport = userExport;
//reportWrongShiftEntity.UserExport = Session[SessionObjects.UserLogin];
reportWrongShiftEntity.ShiftName = shift != null ? shift.ShiftName : string.Empty;
reportWrongShiftEntity.ScheduleShift = shift != null ? shift.ShiftName : string.Empty;
reportWrongShiftEntity.ActualShift = shiftActual != null ? shiftActual.ShiftName : string.Empty;
reportWrongShiftEntity.ApprovedShift = shiftApprove != null ? shiftApprove.ShiftName : string.Empty;
reportWrongShiftEntity.Status = workDay.Status;
reportWrongShiftEntity.TimeIn = workDay.InTime1 != null ? workDay.InTime1 : null; ;
reportWrongShiftEntity.TimeOut = workDay.OutTime1 != null ? workDay.OutTime1 : null;
lstReportWrongShiftEntity.Add(reportWrongShiftEntity);
}
}
}
#region Code Cũ
//foreach (var profile in profiles)
//{
// for (DateTime date = dateStart.Date; date <= dateEnd.Date; date = date.AddDays(1))
// {
// var workDay = workDays.FirstOrDefault(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID);
// if (workDay != null && workDay.ShiftActual != workDay.ShiftID)
// {
// reportWrongShiftEntity = new Att_ReportWrongShiftEntity();
// Guid? orgId = profile.OrgStructureID;
// #region
// // var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate.Date == date.Date);
// // var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
// // var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
// #endregion
// 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);
// #region
// var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
// var orGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, orgs, orgTypes);
// var orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
// reportWrongShiftEntity.BranchCode = orgBranch != null ? orgBranch.Code : string.Empty;
// reportWrongShiftEntity.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
// reportWrongShiftEntity.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
// reportWrongShiftEntity.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty;
// reportWrongShiftEntity.GroupCode = orGroup != null ? orGroup.Code : string.Empty;
// reportWrongShiftEntity.GroupName = orGroup != null ? orGroup.OrgStructureName : string.Empty;
// reportWrongShiftEntity.Division = orgDivision != null ? orgDivision.Code : string.Empty;
// reportWrongShiftEntity.DivisionName = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
// reportWrongShiftEntity.TeamCode = orgTeam != null ? orgTeam.Code : string.Empty;
// reportWrongShiftEntity.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
// #endregion
// reportWrongShiftEntity.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
// reportWrongShiftEntity.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
// var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
// var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
// reportWrongShiftEntity.PositionName = positon != null ? positon.PositionName : string.Empty;
// reportWrongShiftEntity.JobtitleName = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
// var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID);
// var shiftActual = shifts.FirstOrDefault(s => s.ID == workDay.ShiftActual);
// var shiftApprove = shifts.FirstOrDefault(s => s.ID == workDay.ShiftApprove);
// reportWrongShiftEntity.ProfileName = profile.ProfileName;
// reportWrongShiftEntity.CodeEmp = profile.CodeEmp;
// reportWrongShiftEntity.Date = date;
// reportWrongShiftEntity.DateFrom = dateStart;
// reportWrongShiftEntity.DateTo = dateEnd;
// reportWrongShiftEntity.DateExport = DateTime.Now;
// reportWrongShiftEntity.ShiftName = shift != null ? shift.ShiftName : string.Empty;
// reportWrongShiftEntity.ScheduleShift = shift != null ? shift.ShiftName : string.Empty;
// reportWrongShiftEntity.ActualShift = shiftActual != null ? shiftActual.ShiftName : string.Empty;
// reportWrongShiftEntity.ApprovedShift = shiftApprove != null ? shiftApprove.ShiftName : string.Empty;
// reportWrongShiftEntity.Status = workDay.Status;
// reportWrongShiftEntity.TimeIn = workDay.InTime1 != null ? workDay.InTime1 : null; ;
// reportWrongShiftEntity.TimeOut = workDay.OutTime1 != null ? workDay.OutTime1 : null;
// lstReportWrongShiftEntity.Add(reportWrongShiftEntity);
// }
// }
//}
#endregion
return lstReportWrongShiftEntity;
}
}
public DataTable GetReportDailyAttendance(DateTime dateStart, DateTime dateEnd, string userExport, List<Hre_ProfileEntity> lstProfileAll, Guid[] payrollIDs, bool excludeNotInOut, bool isCreateTemplate, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportDailyAttendanceSchema();
if (isCreateTemplate)
{
return table.ConfigDatatable();
}
List<Guid> lstProfileIDs = lstProfileAll.Select(s => s.ID).ToList();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Workday = new Att_WorkDayRepository(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_Shift = new Cat_ShiftRepository(unitOfWork);
var repoWorkDay = new Att_WorkDayServices();
var repoCat_Shift = new Cat_ShiftServices();
var profiles = lstProfileAll.Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID, s.PayrollGroupID }).ToList();
if (payrollIDs != null)
{
profiles = profiles.Where(m => m.PayrollGroupID != null && payrollIDs.Contains(m.PayrollGroupID.Value)).ToList();
}
var profileIds = profiles.Select(s => s.ID).Distinct().ToList();
string status = string.Empty;
string E_APPROVED = WorkdayStatus.E_APPROVED.ToString();
List<object> para_reportWorDay = new List<object>();
para_reportWorDay.AddRange(new object[3]);
para_reportWorDay[1] = dateStart;
para_reportWorDay[2] = dateEnd;
List<Att_WorkdayEntity> workDays = repoWorkDay.GetData<Att_WorkdayEntity>(para_reportWorDay, ConstantSql.hrm_att_sp_getdata_reportWorDay, UserLogin, ref status).ToList();
workDays = workDays.Where(m => profileIds.Contains(m.ProfileID) && m.Status == E_APPROVED).ToList();
var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList();
#region
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
#endregion
if (workDays == null || workDays.Count == 0)
{
return table;
}
profileIds = workDays.Select(m => m.ProfileID).Distinct().ToList();
profiles = profiles.Where(m => profileIds.Contains(m.ID)).ToList();
List<object> para_repoCatShift = new List<object>();
//para_repoCatShift.AddRange(new object[4]);
//para_repoCatShift[0] = null;
//para_repoCatShift[1] = null;
//para_repoCatShift[2] = null;
//para_repoCatShift[3] = null;
List<Cat_ShiftEntity> lstShift = repoCat_Shift.GetData<Cat_ShiftEntity>(para_repoCatShift, ConstantSql.hrm_cat_sp_get_Shift, UserLogin, ref status).ToList();
foreach (var item in workDays)
{
DataRow row = table.NewRow();
row[Att_ReportDailyAttendanceEntity.FieldNames.DateFrom] = dateStart;
row[Att_ReportDailyAttendanceEntity.FieldNames.DateTo] = dateEnd;
var Profile = profiles.Where(m => m.ID == item.ProfileID).FirstOrDefault();
if (Profile != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.CodeEmp] = Profile.CodeEmp != null ? Profile.CodeEmp : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.ProfileName] = Profile.ProfileName != null ? Profile.ProfileName : string.Empty;
Guid? orgId = Profile.OrgStructureID;
if (orgId != null)
{
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_ReportDailyAttendanceEntity.FieldNames.ProfileOrgCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportDailyAttendanceEntity.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_ReportDailyAttendanceEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportDailyAttendanceEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
}
var shift = lstShift.Where(m => m.ID == item.ShiftID).FirstOrDefault();
if (shift != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.ShiftName] = shift.ShiftName;
}
var shiftActual = lstShift.Where(m => m.ID == item.ShiftActual).FirstOrDefault();
if (shiftActual != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.ActualShift] = shiftActual.ShiftName;
}
if (item.WorkDate != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.WorkDate] = item.WorkDate;
}
var shiftApprove = lstShift.Where(m => m.ID == item.ShiftApprove).FirstOrDefault();
if (shiftApprove != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.ApprovedShift] = shiftApprove.ShiftName;
}
if (item.InTime1 != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.TimeIn] = item.InTime1;
}
if (item.OutTime1 != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.TimeOut] = item.OutTime1;
}
row[Att_ReportDailyAttendanceEntity.FieldNames.Type] = item.Type;
row[Att_ReportDailyAttendanceEntity.FieldNames.SrcType] = item.SrcType;
row[Att_ReportDailyAttendanceEntity.FieldNames.Status] = item.Status;
row[Att_ReportDailyAttendanceEntity.FieldNames.Note] = item.Note;
if (item.LateDuration1 != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.LateDuration1] = item.LateDuration1;
}
if (item.EarlyDuration1 != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyDuration1] = item.EarlyDuration1;
}
if (item.LateEarlyDuration != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyDuration] = item.LateEarlyDuration;
}
if (item.LateEarlyRoot != null)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyRoot] = item.LateEarlyRoot;
}
row[Att_ReportDailyAttendanceEntity.FieldNames.LateEarlyReason] = item.LateEarlyReason;
row[Att_ReportDailyAttendanceEntity.FieldNames.UserExport] = userExport;
row[Att_ReportDailyAttendanceEntity.FieldNames.DateExport] = DateTime.Today;
if (item.InTimeRoot != null)
row[Att_ReportDailyAttendanceEntity.FieldNames.InTimeRoot] = item.InTimeRoot.Value;
if (item.OutTimeRoot != null)
row[Att_ReportDailyAttendanceEntity.FieldNames.OutTimeRoot] = item.OutTimeRoot.Value;
if (item.LateEarlyDuration != null)
{
if (item.LateEarlyDuration.Value < 120)
{
row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateLess2h] = "X";
}
else
{
row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateOver2h] = "X";
}
}
else
{
row[Att_ReportDailyAttendanceEntity.FieldNames.EarlyLateLess2h] = "X";
}
table.Rows.Add(row);
}
return table;
}
}
/// <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
}
public DataTable GetReportMonthlyTimeSheetV2(string WorkHourType, Guid? GradeAttendanceID, Guid CutOffDurationID,
string strProfile, string strOrgStructure, string codeEmp, bool isIncludeQuitEmp, bool isNotAllowZero,
bool isCreateTemplate, string userExport, string UserLogin, Guid? jobtitleID, Guid? positionID)
{
bool IsPaidHour = WorkHourType == EnumDropDown.WorkHourType.E_PAIDHOUR.ToString() ? true : false;
using (var context = new VnrHrmDataContext())
{
DataTable table = CreateReportMonthlyTimeSheetV2Schema(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 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 orgsService = new Cat_OrgStructureServices();
List<object> objorgs = new List<object>();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList();
var orgTypeService = new Cat_OrgStructureTypeServices();
var lstObjOrgType = new List<object>();
lstObjOrgType.Add(null);
lstObjOrgType.Add(null);
lstObjOrgType.Add(1);
lstObjOrgType.Add(int.MaxValue - 1);
var lstOrgType = orgTypeService.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status);
var jobTypeServices = new Cat_JobTypeServices();
var lstjobType = new List<object>();
lstjobType.Add(null);
lstjobType.Add(null);
lstjobType.Add(1);
lstjobType.Add(Int32.MaxValue - 1);
var listjobType = jobTypeServices.GetData<Cat_JobTypeEntity>(lstjobType, ConstantSql.hrm_cat_sp_get_JobType, UserLogin, ref status).Where(s => s.Code != null).Select(m => new { m.ID, m.Code }).ToList();
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(strOrgStructure);
var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).ToList();
List<Hre_ProfileEntity> profiles = new List<Hre_ProfileEntity>();
List<object> lstParamHR = new List<object>();
lstParamHR.Add(strOrgStructure);
lstParamHR.Add(null);
lstParamHR.Add(null);
if (strProfile != null)
{
List<Hre_ProfileEntity> _profileIDs = new List<Hre_ProfileEntity>();
var lst = strProfile.Split(',').Select(s => Guid.Parse(s)).ToList();
if (strOrgStructure != null)
{
profiles = GetData<Hre_ProfileEntity>(strOrgStructure, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
_profileIDs = profiles.Where(m => !lst.Contains(m.ID)).ToList();
profiles.AddRange(_profileIDs);
}
else
{
string selectedIds = Common.DotNetToOracle(strProfile);
profiles = GetData<Hre_ProfileEntity>(selectedIds, ConstantSql.hrm_hr_sp_get_ProfileByIds, UserLogin, ref status).ToList();
}
}
else
{
profiles = GetData<Hre_ProfileEntity>(lstParamHR, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
}
if (GradeAttendanceID != null && GradeAttendanceID != Guid.Empty)
{
var lstObjAttGrade = new List<object>();
lstObjAttGrade.AddRange(new object[6]);
lstObjAttGrade[1] = Common.DotNetToOracle(GradeAttendanceID.Value.ToString());
lstObjAttGrade[4] = 1;
lstObjAttGrade[5] = int.MaxValue - 1;
var lstAttGrade = GetData<Att_GradeEntity>(lstObjAttGrade, ConstantSql.hrm_att_sp_get_Att_Grade, UserLogin, ref status).ToList();
List<Guid> lstProOfGrade = lstAttGrade.Select(s => s.ProfileID.Value).Distinct().ToList();
profiles = profiles.Where(s => lstProOfGrade.Contains(s.ID)).ToList();
}
var cutoffInfo = repoAtt_CutOffDuration.FindBy(s => s.ID == CutOffDurationID).FirstOrDefault();
var leavedayTypes = unitOfWork.CreateQueryable<Cat_LeaveDayType>(Guid.Empty, s => s.CodeStatistic != null).Select(s => new { s.Code, s.CodeStatistic, s.ID, s.PaidRate, s.LeaveDayTypeName }).ToList();
var overtimeTypes = unitOfWork.CreateQueryable<Cat_OvertimeType>(Guid.Empty, s => s.CodeStatistic != null).ToList();
DateTime dateStart = cutoffInfo.DateStart;
DateTime dateEnd = cutoffInfo.DateEnd;
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
if(jobtitleID != null)
{
profiles = profiles.Where(s => s.JobTitleID == jobtitleID).ToList();
}
if (positionID != null)
{
profiles = profiles.Where(s => s.PositionID == positionID).ToList();
}
List<Guid> profileIds = profiles.Select(s => s.ID).ToList();
var timesheets = new List<Att_TimeSheet>().Select(s => new
{
s.ProfileID,
s.JobTypeID,
s.RoleID,
s.Date,
s.NoHour,
s.ID,
s.Note,
s.Sector
}).ToList();
var workDays = new List<Att_Workday>().Select(s => new
{
s.ProfileID,
s.ShiftID,
s.Status
}).ToList();
var attendanceTables = new List<Att_AttendanceTable>();
var attendanceTableItems = new List<Att_AttendanceTableItem>();
foreach (var item in profileIds.Chunk(1000))
{
timesheets.AddRange(repoTimeSheet.FindBy(s => s.ProfileID != null && item.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,
s.Sector
}).ToList());
workDays.AddRange(repoWorkDay.FindBy(s => item.Contains(s.ProfileID) && dateStart <= s.WorkDate
&& s.WorkDate <= dateEnd && s.IsDelete == null).Select(s => new
{
s.ProfileID,
s.ShiftID,
s.Status
}).ToList());
attendanceTables.AddRange(repoAtt_AttendencaTable.FindBy(s => s.IsDelete == null
&& s.CutOffDurationID == CutOffDurationID && item.Contains(s.ProfileID)).ToList());
}
var jobtypes = repoJobtype.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.Code }).ToList();
List<Guid> attendanceTablesID = attendanceTables.Select(s => s.ID).ToList();
foreach (var item in attendanceTablesID.Chunk(1000))
{
attendanceTableItems.AddRange(unitOfWork.CreateQueryable<Att_AttendanceTableItem>(s =>
s.IsDelete == null && item.Contains(s.AttendanceTableID)).ToList());
}
if (isNotAllowZero)
{
profileIds = attendanceTables.Where(x => x.StdWorkDayCount > 0).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();
int p = 0;
foreach (var profile in profiles)
{
p += 1;
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 }).ToList();
if (attendanceTableProfile == null)
{
continue;
}
var attendanceTableItemProfile = attendanceTableItems.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.DepartmentName] = profile.OrgStructureName;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DepartmentCode] = profile.OrgStructureCode;
if (profile.DateHire.HasValue)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateHire] = profile.DateHire.Value;
if (profile.DateEndProbation.HasValue)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value;
if (profile.E_COMPANY != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_COMPANY] = profile.E_COMPANY;
if (profile.E_BRANCH != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_BRANCH] = profile.E_BRANCH;
if (profile.E_UNIT != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_UNIT] = profile.E_UNIT;
if (profile.E_DIVISION != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DIVISION] = profile.E_DIVISION;
if (profile.E_DEPARTMENT != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT;
if (profile.E_TEAM != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_TEAM] = profile.E_TEAM;
if (profile.E_SECTION != null)
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_SECTION] = profile.E_SECTION;
//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;
#region Lấy Tên Phòng ban cho BDF
var orgName = GetParentOrg(lstallorgs, lstOrgType, profile.OrgStructureID);
if (orgName.Count != 0)
{
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Channel] = orgName[2];
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Region] = orgName[1];
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Area] = orgName[0];
}
#endregion
#region Lấy phòng ban cho VietNam EASport
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_COMPANY] = profile.E_COMPANY;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_BRANCH] = profile.E_BRANCH;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_UNIT] = profile.E_UNIT;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DIVISION] = profile.E_DIVISION;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_DEPARTMENT] = profile.E_DEPARTMENT;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_TEAM] = profile.E_TEAM;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.E_SECTION] = profile.E_SECTION;
#endregion
//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.DateExport] = DateTime.Today;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UserExport] = userExport;
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.HourPerDay] = attendanceTableProfile.HourPerDay > 0 ? attendanceTableProfile.HourPerDay : 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.Note] = attendanceTableProfile.Note != null ? attendanceTableProfile.Note : string.Empty;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.UnPaidLeave] = attendanceTableProfile.UnPaidLeave > 0 ? attendanceTableProfile.UnPaidLeave : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.AnlDayTaken] = attendanceTableProfile.AnlDayTaken > 0 ? attendanceTableProfile.AnlDayTaken : 0.0;
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.SickDayTaken] = attendanceTableProfile.SickDayTaken > 0 ? attendanceTableProfile.SickDayTaken : 0.0;
double preg = 0.0;
#region code cũ xử lý đổ (Data + Day)
//foreach (var item in attendanceTableItemProfile)
//{
// if (item.IsHavingPregTreatment)
// {
// preg += 1;
// }
// if (item.LeaveTypeID != null)
// {
// row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = leavedayTypes.Where(s => s.ID == item.LeaveTypeID).FirstOrDefault().Code;
// }
// else
// {
// if (IsPaidHour)
// {
// row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = item.WorkPaidHours;
// }
// else
// {
// row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + item.WorkDate.Day.ToString()] = item.WorkHours;
// }
// }
//}
#endregion
attendanceTableItemProfile = attendanceTableItemProfile.OrderBy(s => s.WorkDate).ToList();
Dictionary<string, double> leave_Hour = new Dictionary<string, double>();
for (int i = 0; i < attendanceTableItemProfile.Count; i++)
{
int stt = i + 1;
if (attendanceTableItemProfile[i].IsHavingPregTreatment)
{
preg += 1;
}
if (attendanceTableItemProfile[i].LeaveTypeID != null)
{
var leave = leavedayTypes.Where(s => s.ID == attendanceTableItemProfile[i].LeaveTypeID).FirstOrDefault();
if (attendanceTableItemProfile[i].LeaveDays == 0.5)
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = leave.CodeStatistic + "/2";
}
else
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = leave.CodeStatistic;
}
if (leave.CodeStatistic == null)
continue;
if (leave_Hour.ContainsKey(leave.CodeStatistic))
leave_Hour[leave.CodeStatistic] += attendanceTableItemProfile[i].LeaveHours;
else
leave_Hour[leave.CodeStatistic] = attendanceTableItemProfile[i].LeaveHours;
}
else
{
if (IsPaidHour)
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = attendanceTableItemProfile[i].WorkPaidHours;
}
else
{
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.Data + stt] = attendanceTableItemProfile[i].WorkHours;
}
}
}
foreach (var leave in leavedayTypes)
{
if (leave.CodeStatistic == null)
continue;
if (leave_Hour.ContainsKey(leave.CodeStatistic))
{
row[leave.CodeStatistic] = leave_Hour[leave.CodeStatistic];
}
}
#region Tăng Ca
if (attendanceTableProfile.Overtime1Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime1Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime1Hours;
}
if (attendanceTableProfile.Overtime2Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime2Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime2Hours;
}
if (attendanceTableProfile.Overtime3Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime3Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime3Hours;
}
if (attendanceTableProfile.Overtime4Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime4Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime4Hours;
}
if (attendanceTableProfile.Overtime5Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime5Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime5Hours;
}
if (attendanceTableProfile.Overtime6Type != null)
{
var overtime = overtimeTypes.Where(s => s.ID == attendanceTableProfile.Overtime6Type).FirstOrDefault();
if (overtime != null)
row[overtime.CodeStatistic] = attendanceTableProfile.Overtime6Hours;
}
#endregion
row[Att_ReportMonthlyTimeSheetV2Entity.FieldNames.TotalPregnancy] = preg;
foreach (var jobcode in listjobType)
{
if (table.Columns.Contains(jobcode.Code))
{
double? nohourByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID).Sum(s => s.NoHour);
double? sectorByPro = timeSheetByProfile.Where(s => s.JobTypeID == jobcode.ID).Sum(s => s.Sector);
row[jobcode.Code] = nohourByPro == null ? 0 : nohourByPro.Value;
row[jobcode.Code + "_Sector"] = sectorByPro == null ? 0 : sectorByPro.Value;
}
}
table.Rows.Add(row);
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
config.Add("format", "{0:dd/mm/yyyy}");
configs.Add("DateFrom", config);
return table.ConfigTable();
}
}
public DataTable GetReportSummaryOvertimeMonth(DateTime dateStart, DateTime dateEndSearch, string orgStructureID, List<Hre_ProfileEntity> lstProfileObjects, Guid[] lstOvertimetypeIDs, Guid[] lstShiftIDs, bool IsDisplay0Data, bool isIncludeQuitEmp, string userExport, string UserLogin)
{
DataTable table = CreateReportSummaryOvertimeMonthSchema();
DateTime dateEnd = dateEndSearch.AddDays(1).AddSeconds(-1);
using (var context = new VnrHrmDataContext())
{
BaseService basevice = new BaseService();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
string key = AttendanceDataStatus.E_APPROVED.ToString();
// var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork);
string status = string.Empty;
List<object> paraOvertime = new List<object>();
paraOvertime.AddRange(new object[3]);
paraOvertime[0] = orgStructureID;
paraOvertime[1] = dateStart;
paraOvertime[2] = dateEnd;
var overtimeDays_Actual = basevice.GetData<Att_OvertimeEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList();
string E_CASHOUT = MethodOption.E_CASHOUT.ToString();
var lstProfileIds = lstProfileObjects.Select(p => p.ID).ToList();
//var overtimeDays = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key
// && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Select(s =>
//new { s.ProfileID, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours }).ToList();
var profileIds = overtimeDays_Actual.Select(s => s.ProfileID).Distinct().ToList();
var profiles = lstProfileObjects.Select(s => new
{
s.ID,
s.DateQuit,
s.OrgStructureID,
s.ProfileName,
s.CodeEmp,
s.PositionID,
s.JobTitleID
}).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork);
var overtimeTypeIds = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.OvertimeTypeName }).Distinct().ToList();
// var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
//var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null && dateStart <= s.WorkDate && s.WorkDate <= dateEnd)
// .Select(s => new { s.ProfileID, s.ShiftID }).ToList();
var workDays = basevice.GetData<Att_WorkdayEntity>(paraOvertime, ConstantSql.hrm_att_getdata_Workday, UserLogin, ref status).ToList();
//if (lstProfileIds != null && lstProfileIds[0] != null && lstProfileIds[0] != Guid.Empty)
//{
// profiles = profiles.Where(s => lstProfileIds.Contains(s.ID)).ToList();
//}
if (lstOvertimetypeIDs != null)
{
overtimeDays_Actual = overtimeDays_Actual.Where(s => lstOvertimetypeIDs.Contains(s.OvertimeTypeID)).ToList();
}
var overtimeDays_Remain = overtimeDays_Actual.Where(m => m.MethodPayment == E_CASHOUT).ToList();
if (lstShiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && lstShiftIDs.Contains(s.ShiftID.Value)).ToList();
profileIds = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList();
}
List<Guid> guids = profiles.Select(s => s.ID).ToList();
overtimeDays_Actual = overtimeDays_Actual.Where(s => guids.Contains(s.ProfileID)).ToList();
if (IsDisplay0Data)
{
var profileIDs = overtimeDays_Actual.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList();
}
foreach (var profile in profiles)
{
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateExport] = DateTime.Now;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateFrom] = dateStart;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.DateTo] = dateEnd;
row[Att_ReportSummaryOvertimeMonthEntity.FieldNames.UserExport] = userExport;
foreach (var overtime in overtimeTypeIds)
{
if (overtime != null && !string.IsNullOrEmpty(overtime.OvertimeTypeName) && table.Columns.Contains(overtime.OvertimeTypeName))
{
var sum = overtimeDays_Actual.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.RegisterHours);
row[overtime.OvertimeTypeName] = sum > 0 ? (object)sum : DBNull.Value;
var sum1 = overtimeDays_Remain.Where(s => s.ProfileID == profile.ID && s.OvertimeTypeID == overtime.ID).Sum(s => s.ApproveHours);
row[overtime.OvertimeTypeName + "Approve"] = sum1 > 0 ? (object)sum1 : DBNull.Value;
}
}
table.Rows.Add(row);
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var confighidden = new Dictionary<string, object>();
var configwidthCodeEmp = new Dictionary<string, object>();
var configwidthCodeOrg = new Dictionary<string, object>();
var configwidthNameOrg = new Dictionary<string, object>();
configwidthCodeEmp.Add("width", 88);
configs.Add("CodeEmp", configwidthCodeEmp);
configwidthCodeOrg.Add("width", 90);
configs.Add("CodeOrg", configwidthCodeOrg);
configs.Add("CodeBranch", configwidthCodeOrg);
configs.Add("CodeTeam", configwidthCodeOrg);
configs.Add("CodePosition", configwidthCodeOrg);
configs.Add("CodeJobtitle", configwidthCodeOrg);
configs.Add("CodeSection", configwidthCodeOrg);
configwidthNameOrg.Add("width", 110);
configs.Add("BranchName", configwidthNameOrg);
configs.Add("OrgName", configwidthNameOrg);
configs.Add("TeamName", configwidthNameOrg);
configs.Add("SectionName", configwidthNameOrg);
confighidden.Add("hidden", true);
configs.Add("DateFrom", confighidden);
configs.Add("DateTo", confighidden);
configs.Add("UserExport", confighidden);
configs.Add("DateExport", confighidden);
return table.ConfigTable(configs);
}
}
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 GetReportMonthlyRoster(DateTime DateStart, DateTime DateEnd, string orgStructureID, List<Hre_ProfileEntity> profiles, bool _onlyHolydayNotHaveRoster, Guid[] lstpayrollIDs, string userExport, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (UnitOfWork)(new UnitOfWork(context));
BaseService basevervice = new BaseService();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var repoCat_Shift = new CustomBaseRepository<Cat_Shift>(unitOfWork);
var repoCat_DayOff = new CustomBaseRepository<Cat_DayOff>(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null && s.IsDelete == null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var positions = repoCat_Position.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
if (DateEnd != null)
DateEnd = DateEnd.AddDays(1).AddMilliseconds(-1);
if (lstpayrollIDs != null)
{
profiles = profiles.Where(p => p.PayrollGroupID.HasValue && lstpayrollIDs.Contains(p.PayrollGroupID.Value)).ToList();
}
string key = RosterStatus.E_APPROVED.ToString();
string key1 = RosterType.E_TIME_OFF.ToString();
List<Att_Roster> rosters = null;
var listProfileID = profiles.Select(d => d.ID).ToList();
string status = string.Empty;
List<object> paraAtt_Roster = new List<object>();
paraAtt_Roster.AddRange(new object[4]);
paraAtt_Roster[0] = (object)orgStructureID;
paraAtt_Roster[1] = DateStart;
paraAtt_Roster[2] = DateEnd;
paraAtt_Roster[3] = key;
rosters = basevervice.GetData<Att_RosterEntity>(paraAtt_Roster, ConstantSql.hrm_att_getdata_Roster, UserLogin, ref status).Where(s => listProfileID.Contains(s.ProfileID)).ToList().Translate<Att_Roster>();
var listShift = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList();
DataTable table = CreateReportMonthlyRosterSchema(listShift);
if (profiles == null)
return table;
int stt = 0;
foreach (var profile in profiles)
{
stt++;
DataRow row = table.NewRow();
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
Guid? orgId = profile.OrgStructureID;
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, 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 orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
row[Att_ReportMonthlyRosterEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportMonthlyRosterEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportMonthlyRosterEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.GroupCode] = orgGroup != null ? orgGroup.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.GroupName] = orgGroup != null ? orgGroup.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.DivisionCode] = orgDivision != null ? orgDivision.Code : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportMonthlyRosterEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportMonthlyRosterEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.JobtitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
row[Att_ReportMonthlyRosterEntity.FieldNames.DateFrom] = DateStart;
row[Att_ReportMonthlyRosterEntity.FieldNames.DateTo] = DateEnd;
row[Att_ReportMonthlyRosterEntity.FieldNames.DateExport] = DateTime.Today;
row[Att_ReportMonthlyRosterEntity.FieldNames.UserExport] = userExport;
var attRosters = rosters.Where(s => s.ProfileID == profile.ID && s.Type != key1).OrderBy(s => s.DateStart).ToList();
#region CheckData
foreach (Att_Roster attRoster in attRosters)
{
for (DateTime date = attRoster.DateStart; date <= attRoster.DateEnd; date = date.AddDays(1))
{
#region GetHeaderRow
if (stt == 1)
{
row["Header" + date.Day] = date.ToString("dd-MMM");
}
else if (stt == 2)
{
row["Header" + date.Day] = date.DayOfWeek.ToString();
}
#endregion
#region GetShiff
if (DateStart > date || date > DateEnd)
{
break;
}
if (attRoster.Type == RosterType.E_CHANGE_SHIFT.ToString())
{
var shift = listShift.Where(d => d.ID == attRoster.MonShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (attRoster.Type == RosterType.E_TIME_OFF.ToString())
{
row["Data" + date.Day] = "";
}
else if (date.DayOfWeek == DayOfWeek.Monday)
{
var shift = listShift.Where(d => d.ID == attRoster.MonShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else
{
if (date.DayOfWeek == DayOfWeek.Tuesday)
{
var shift = listShift.Where(d => d.ID == attRoster.TueShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (date.DayOfWeek == DayOfWeek.Wednesday)
{
var shift = listShift.Where(d => d.ID == attRoster.WedShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (date.DayOfWeek == DayOfWeek.Thursday)
{
var shift = listShift.Where(d => d.ID == attRoster.ThuShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (date.DayOfWeek == DayOfWeek.Friday)
{
var shift = listShift.Where(d => d.ID == attRoster.FriShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (date.DayOfWeek == DayOfWeek.Saturday)
{
var shift = listShift.Where(d => d.ID == attRoster.SatShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
else if (date.DayOfWeek == DayOfWeek.Sunday)
{
var shift = listShift.Where(d => d.ID == attRoster.SunShiftID).FirstOrDefault();
row["Data" + date.Day] = shift == null ? "" : shift.Code;
}
}
#endregion
}
}
#endregion
table.Rows.Add(row);
}
if (_onlyHolydayNotHaveRoster)
{
DataTable tblData = CreateReportMonthlyRosterSchema(listShift);
List<Cat_DayOff> dayOffs = repoCat_DayOff.FindBy(s => s.IsDelete == null && s.DateOff.Year == DateStart.Year).ToList();
foreach (DataRow dataRow in table.Rows)
{
foreach (Cat_DayOff catDayOff in dayOffs)
{
if (dataRow["Data" + catDayOff.DateOff.Day].IsNullOrEmpty())
{
DataRow row = tblData.NewRow();
foreach (DataColumn dataColumn in table.Columns)
{
row[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];
}
for (int j = 1; j <= 31; j++)
{
row["Data" + j] = "";
}
row["Data" + catDayOff.DateOff.Day] = "HLD";
table.Rows.Add(row);
break;
}
}
}
table = tblData;
}
DataTable table1 = CreateReportMonthlyRosterSchema(listShift);
foreach (DataRow dataRow in table.Rows)
{
DataRow row = table1.NewRow();
bool isCheck = false;
foreach (DataColumn dataColumn in table.Columns)
{
if (dataColumn.ColumnName.IndexOf("Data") > -1 && !isCheck)
{
int i = 1;
for (DateTime date = DateStart; date <= DateEnd; date = date.AddDays(1))
{
if (table1.Columns.Contains("Data" + i))
{
row["Data" + i] = dataRow["Data" + date.Day];
row["Header" + i] = dataRow["Header" + date.Day];
}
i++;
}
isCheck = true;
}
else if (dataColumn.ColumnName.IndexOf("Data") == -1)
{
row[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];
}
}
table1.Rows.Add(row);
}
return table1;
}
}
public DataTable GetReportDetailOvertime(string userExport, DateTime dateStart, DateTime dateEndSearch, string ProfileName, string CodeEmp, string OrgIDString, List<Guid?> lstovertimeTypeIds, bool noDisplay0Data, bool isIncludeQuitEmp)
{
DataTable table = CreateReportDetailOvertimeSchema();
DateTime dateEnd = dateEndSearch.AddDays(1).AddMinutes(-1);
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork);
string key = OverTimeStatus.E_APPROVED.ToString();
string E_CASHOUT = MethodOption.E_CASHOUT.ToString();
var overtimesQuery = unitOfWork.CreateQueryable<Att_Overtime>(s => s.IsDelete == null &&
(s.RegisterHours > 0 || s.ApproveHours > 0) && s.Status == key && dateStart <= s.WorkDate
&& s.WorkDate <= dateEnd && (s.MethodPayment == null || s.MethodPayment == E_CASHOUT));
if (!string.IsNullOrEmpty(OrgIDString))
{
List<Guid> OrgIDs = new List<Guid>();
List<string> OrgIDsArr = OrgIDString.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
foreach (var item in OrgIDsArr)
{
Guid OrgID = Guid.Empty;
Guid.TryParse(item, out OrgID);
if (OrgID != Guid.Empty)
{
OrgIDs.Add(OrgID);
}
}
overtimesQuery = overtimesQuery.Where(m => m.OrgStructureID != null && OrgIDs.Contains(m.OrgStructureID.Value));
}
if (!string.IsNullOrEmpty(ProfileName))
{
List<string> lstProfileName = ProfileName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
if (lstProfileName.Count > 1)
{
overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstProfileName.Contains(m.Hre_Profile.ProfileName));
}
else
{
overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.ProfileName != null && (m.Hre_Profile.ProfileName.Contains(ProfileName)));
}
}
if (!string.IsNullOrEmpty(CodeEmp))
{
List<string> lstCodeEmp = CodeEmp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList<string>();
if (lstCodeEmp.Count > 1)
{
overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && lstCodeEmp.Contains(m.Hre_Profile.CodeEmp));
}
else
{
overtimesQuery = overtimesQuery.Where(m => m.Hre_Profile != null && m.Hre_Profile.CodeEmp != null && (m.Hre_Profile.CodeEmp.Contains(CodeEmp)));
}
}
if (noDisplay0Data)
{
overtimesQuery = overtimesQuery.Where(s => s.ApproveHours > 0);
}
if (lstovertimeTypeIds != null && lstovertimeTypeIds[0] != null && lstovertimeTypeIds[0] != Guid.Empty)
{
overtimesQuery = overtimesQuery.Where(s => lstovertimeTypeIds.Contains(s.OvertimeTypeID));
}
var overtimes = overtimesQuery.Select(s => new { s.ProfileID, s.WorkDate, s.OvertimeTypeID, s.ApproveHours, s.RegisterHours, s.AnalyseHour, s.ConfirmHours, s.OrgStructureID, s.ReasonOT }).ToList();
var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList();
var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null & dateStart <= s.WorkDate && s.WorkDate <= dateEnd)
.Select(s => new { s.ProfileID, s.ShiftID, s.InTime1, s.OutTime1, s.WorkDate }).ToList();
var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID)).Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null && s.Code != null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork);
var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code }).ToList();
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var shifts = repoCat_Shift.FindBy(s => s.IsDelete == null).ToList();
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateEnd).ToList();
}
foreach (var profile in profiles)
{
for (DateTime date = dateStart; date <= dateEnd; date = date.AddDays(1))
{
var overtimeProfiles = overtimes.Where(s => s.WorkDate.Date == date.Date && s.ProfileID == profile.ID).ToList();
if (overtimeProfiles.Count == 0)
continue;
var lastOvertimeByProfile = overtimeProfiles.OrderByDescending(m => m.WorkDate).FirstOrDefault();
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
if (lastOvertimeByProfile != null)
orgId = lastOvertimeByProfile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportDetailOvertimeEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now;
row[Att_ReportDetailOvertimeEntity.FieldNames.ReasonOT] = lastOvertimeByProfile.ReasonOT;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
var workDay = workDays.FirstOrDefault(s => s.ProfileID == profile.ID && s.WorkDate == date);
var shift = shifts.FirstOrDefault(s => workDay != null && s.ID == workDay.ShiftID);
row[Att_ReportDetailOvertimeEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty;
row[Att_ReportDetailOvertimeEntity.FieldNames.DateFrom] = dateStart;
row[Att_ReportDetailOvertimeEntity.FieldNames.DateTo] = dateEnd;
row[Att_ReportDetailOvertimeEntity.FieldNames.DateExport] = DateTime.Now;
row[Att_ReportDetailOvertimeEntity.FieldNames.UserExport] = userExport;
row[Att_ReportDetailOvertimeEntity.FieldNames.udSubmitApproveHour] = overtimeProfiles.Where(m => m.AnalyseHour != null).Sum(m => m.AnalyseHour);
row[Att_ReportDetailOvertimeEntity.FieldNames.DateOvertime] = date;
if (workDay != null && workDay.InTime1 != null)
{
//row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value.ToString("HH:mm:ss");
row[Att_ReportDetailOvertimeEntity.FieldNames.udInTime] = workDay.InTime1.Value;
}
if (workDay != null && workDay.OutTime1 != null)
{
//row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value.ToString("HH:mm:ss");
row[Att_ReportDetailOvertimeEntity.FieldNames.udOutTime] = workDay.OutTime1.Value;
}
if (shift != null)
{
row[Att_ReportDetailOvertimeEntity.FieldNames.ShiftName] = shift.ShiftName;
}
foreach (var item in overtimeTypes)
{
var sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID).Sum(s => s.RegisterHours);
row[item.Code] = sum > 0 ? (object)sum : DBNull.Value;
sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ApproveHours > 0).Sum(s => s.ApproveHours.Value);
row[item.Code + "_Approve"] = sum > 0 ? (object)sum : DBNull.Value;
sum = overtimeProfiles.Where(s => s.OvertimeTypeID == item.ID && s.ConfirmHours != null && s.ConfirmHours > 0).Sum(s => s.ConfirmHours);
row[item.Code + "_Confirm"] = sum > 0 ? (object)sum : DBNull.Value;
}
table.Rows.Add(row);
}
}
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
var configtime = new Dictionary<string, object>();
var configwidthCode = new Dictionary<string, object>();
var configwidthName = new Dictionary<string, object>();
var configwidthshift = new Dictionary<string, object>();
var configwidthudSubmitApproveHour = new Dictionary<string, object>();
configwidthCode.Add("width", 80);
configs.Add("CodeBranch", configwidthCode);
configs.Add("CodeOrg", configwidthCode);
configs.Add("CodeTeam", configwidthCode);
configs.Add("CodeSection", configwidthCode);
configs.Add("CodeJobtitle", configwidthCode);
configs.Add("CodePosition", configwidthCode);
configwidthName.Add("width", 110);
configs.Add("BranchName", configwidthName);
configs.Add("OrgName", configwidthName);
configs.Add("TeamName", configwidthName);
configs.Add("SectionName", configwidthName);
configwidthudSubmitApproveHour.Add("width", 120);
configs.Add("udSubmitApproveHour", configwidthudSubmitApproveHour);
configwidthshift.Add("width", 90);
configs.Add("DateOvertime", configwidthshift);
configs.Add("ShiftName", configwidthshift);
config.Add("hidden", true);
configs.Add("DateFrom", config);
configs.Add("DateTo", config);
configs.Add("UserExport", config);
configs.Add("DateExport", config);
configtime.Add("width", 80);
configtime.Add("format", "{0:HH:mm:ss}");
configs.Add("udOutTime", configtime);
configs.Add("udInTime", configtime);
// return table.ConfigDatatable();
return table.ConfigTable(configs);
}
}
public DataTable GetReportStatisticsOvertimeByYear(int Year, List<Hre_ProfileEntity> profiles, List<Guid?> lstOvertimeTypeIds, bool isNotAllowZero, bool isIncludeQuitEmp, string userExport, string UserLogin)
{
DataTable table = CreateReportStatisticsOvertimeByYearSchema();
using (var context = new VnrHrmDataContext())
{
//int year = Year.Year;
int year = Year;
DateTime dateFrom = new DateTime(Year, 1, 1);
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
DateTime dateStart = new DateTime(year, 1, 1);
DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12)).AddDays(1).AddMinutes(-1);
//DateTime dateEnd = new DateTime(year, 12, DateTime.DaysInMonth(year, 12));
string key = OverTimeStatus.E_APPROVED.ToString();
// var repoAtt_Overtime = new Att_OvertimeRepository(unitOfWork);
var repoCat_LeaveDayType = new CustomBaseRepository<Cat_LeaveDayType>(unitOfWork);
// var repoCat_LeaveDayType = new Cat_LeaveDayTypeRepository(unitOfWork);
List<object> para = new List<object>();
para.AddRange(new object[3]);
para[0] = null;
para[1] = dateStart;
para[2] = dateEnd;
var basevices = new BaseService();
string status = string.Empty;
var overtimes = basevices.GetData<Att_OvertimeEntity>(para, ConstantSql.hrm_att_getdata_Overtime, UserLogin, ref status).Where(s => s.Status == key).ToList();
//var overtimes = repoAtt_Overtime.FindBy(s => s.IsDelete == null && s.Status == key && dateStart <= s.WorkDate && s.WorkDate <= dateEnd)
// .Select(s => new { s.ProfileID, s.ApproveHours, s.WorkDate, s.OvertimeTypeID }).ToList();
if (isNotAllowZero)
{
overtimes = overtimes.Where(s => s.ApproveHours > 0).ToList();
}
var profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList();
// var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
//var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID))
// .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
//Guid TypeCoLeaveID = EntityService.CreateQueryable<Cat_LeaveDayType>(false, GuidContext, Guid.Empty, m => m.IsTimeOffInLieu == true).Select(m => m.ID).FirstOrDefault();
Guid TypeCoLeaveID = repoCat_LeaveDayType.FindBy(s => s.IsDelete == null && s.IsTimeOffInLieu == true).Select(s => s.ID).FirstOrDefault();
var lstLeaveDay = basevices.GetData<Att_LeaveDayEntity>(para, ConstantSql.hrm_att_getdata_LeaveDay, UserLogin, ref status).Where(s => s.LeaveDayTypeID == TypeCoLeaveID && profileIds.Contains(s.ProfileID)).ToList();
//var lstLeaveDay = EntityService.CreateQueryable<Att_LeaveDay>(false, GuidContext, Guid.Empty, m => m.LeaveDayTypeID == TypeCoLeaveID && m.DateStart <= dateEnd && m.DateEnd >= dateStart && profileIds.Contains(m.ProfileID))
// .Select(m => new { m.ID, m.ProfileID, m.LeaveHours, m.TotalDuration }).Execute();
// var repoTimeOffInLieu = new Att_TimeOffInLieuRepository(unitOfWork);
// var timeOfInLieus = repoTimeOffInLieu.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID) && s.OvertimeID != null && dateStart <= s.Date && s.Date <= dateEnd)
//.Select(s => new { s.AccumulateLeaves, s.ProfileID }).ToList();
var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null && s.IsDelete == null).ToList();
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var repoCat_Position = new Cat_PositionRepository(unitOfWork);
var positions = repoCat_Position.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var repoCat_JobTitle = new Cat_JobTitleRepository(unitOfWork);
var jobtitles = repoCat_JobTitle.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
var repoCat_OvertimeType = new Cat_OvertimeTypeRepository(unitOfWork);
var overtimeTypes = repoCat_OvertimeType.FindBy(s => s.Code != null && s.IsDelete == null).Select(s => new { s.ID, s.Code }).Distinct().ToList();
if (lstOvertimeTypeIds != null && lstOvertimeTypeIds[0] != Guid.Empty && lstOvertimeTypeIds[0] != null)
{
overtimes = overtimes.Where(s => lstOvertimeTypeIds.Contains(s.OvertimeTypeID)).ToList();
profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
//if (lstProfileIDs.Any() && lstProfileIDs[0] != Guid.Empty && lstProfileIDs[0] != null)
//{
// profiles = profiles.Where(s => lstProfileIDs.Contains(s.ID)).ToList();
//}
if (isNotAllowZero)
{
profileIds = overtimes.Select(s => s.ProfileID).Distinct().ToList();
profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
}
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > dateStart).ToList();
}
foreach (var profile in profiles)
{
DataRow row = table.NewRow();
Guid? orgId = profile.OrgStructureID;
var org = orgs.FirstOrDefault(s => s.ID == profile.OrgStructureID);
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgOrg = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var orgTeam = LibraryService.GetNearestParent(orgId, OrgUnit.E_TEAM, orgs, orgTypes);
var orgSection = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeBranch] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeOrg] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeTeam] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeSection] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.OrgName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateExport] = DateTime.Now;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.UserExport] = userExport;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodePosition] = positon != null ? positon.Code : string.Empty;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.CodeJobtitle] = jobtitle != null ? jobtitle.Code : string.Empty;
//var timeOffInLieuProfiles = timeOfInLieus.Where(s => s.ProfileID == profile.ID).ToList();
//var sumHour = timeOffInLieuProfiles.Sum(s => s.AccumulateLeaves);
//if (sumHour > 0)
// row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = sumHour;
var lstLeaveDayByProfile = lstLeaveDay.Where(m => m.ProfileID == profile.ID).ToList();
if (lstLeaveDayByProfile.Count > 0)
{
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.SumHourCO] = lstLeaveDayByProfile.Sum(m => m.LeaveHours * (m.LeaveDays ?? 0));
}
for (int i = 1; i <= 12; i++)
{
dateStart = new DateTime(year, i, 1);
dateEnd = new DateTime(year, i, DateTime.DaysInMonth(year, i));
// row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = Year;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateFrom] = dateFrom;
row[Att_ReportStatisticsOvertimeByYearEntity.FieldNames.DateTo] = dateEnd;
var month = overtimes.Where(s => s.ProfileID == profile.ID && dateStart <= s.WorkDate && s.WorkDate <= dateEnd).Sum(s => s.ApproveHours);
if (month > 0)
row["M" + i] = month;
}
table.Rows.Add(row);
}
return table;
}
}
/// <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;
}
}
/// <summary>
/// Lấy Dữ Liệu BC Báo cáo chi tiết giờ vào giờ ra
/// </summary>
/// <param name="DateFrom">Ngày bắt đầu</param>
/// <param name="DateTo">Ngày kết thúc (Cuối ngày 23:59:59)</param>
/// <param name="lstProfileIDs">Ds ProfileIDs</param>
/// <returns></returns>
public DataTable GetReportSumaryInOut(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, Guid[] ShiftIDs, string CodeEmp, bool isIncludeQuitEmp, string userExport, string UserLogin)
{
List<Guid> lstProfileIDs = profiles.Select(s => s.ID).ToList();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
var workDays = repoAtt_Workday.FindBy(s => s.IsDelete == null &&
DateFrom <= s.WorkDate && s.WorkDate <= DateTo
&& (s.InTime1 != null || s.OutTime1 != null))
.Select(s => new { s.ProfileID, s.ShiftID, s.ShiftApprove, s.WorkDate, s.InTime1, s.OutTime1 }).ToList();
//if(lstProfileIDs != null){
// workDays = workDays.Where(s => lstProfileIDs.Contains(s.ProfileID)).ToList();
//}
if (ShiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList();
}
var profileIds = workDays.Select(s => s.ProfileID).Distinct().ToList();
// var repoHre_Profile = new Hre_ProfileRepository(unitOfWork);
//var profiles = repoHre_Profile.FindBy(s => s.IsDelete == null && profileIds.Contains(s.ID))
// .Select(s => new { s.ID, s.DateQuit, s.OrgStructureID, s.ProfileName, s.CodeEmp, s.PositionID, s.JobTitleID }).ToList();
profiles = profiles.Where(s => s.IsDelete == null && profileIds.Contains(s.ID)).ToList();
// .Select(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_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_Shift = new Cat_ShiftRepository(unitOfWork);
var shifts = repoCat_Shift.GetAll().ToList();
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > DateTo).ToList();
}
if (!string.IsNullOrEmpty(CodeEmp))
{
profiles = profiles.Where(s => s.CodeEmp == CodeEmp).ToList();
}
List<Guid> guids = profiles.Select(s => s.ID).ToList();
workDays = workDays.Where(s => guids.Contains(s.ProfileID)).ToList();
string E_FULLSHIFT = LeaveDayDurationType.E_FULLSHIFT.ToString();
string E_APPROVED = LeaveDayStatus.E_APPROVED.ToString();
var repoAtt_LeaveDay = new Att_LeavedayRepository(unitOfWork);
var leavedayProfiles = repoAtt_LeaveDay.FindBy(s => s.DurationType == E_FULLSHIFT
& s.Status == E_APPROVED && DateFrom <= s.DateEnd && s.DateStart <= DateTo).Select(s => new { s.ProfileID, s.DateStart, s.DateEnd }).ToList();
//if (isNotAllowZero)
//{
// profileIds = workDays.Select(s => s.ProfileID).ToList();
// profiles = profiles.Where(s => profileIds.Contains(s.ID)).ToList();
//}
DataTable table = CreateReportSummaryInOutSchema();
foreach (var profile in profiles)
{
for (DateTime date = DateFrom.Date; date <= DateTo; date = date.AddDays(1))
{
var leaday = leavedayProfiles.FirstOrDefault(s => s.ProfileID == profile.ID && s.DateStart <= date && date <= s.DateEnd);
var workDayProfiles = workDays.Where(s => s.WorkDate.Date == date && s.ProfileID == profile.ID).ToList();
if (workDayProfiles.Count > 0 && leaday == null)
{
DataRow row = table.NewRow();
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
Guid? orgId = profile.OrgStructureID;
var orgBranch = LibraryService.GetNearestParent(orgId, OrgUnit.E_BRANCH, orgs, orgTypes);
var orgGroup = LibraryService.GetNearestParent(orgId, OrgUnit.E_GROUP, 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 orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
row[Att_ReportSumaryInOutEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.GroupCode] = orgGroup != null ? orgGroup.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.GroupName] = orgGroup != null ? orgGroup.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.Division] = orgDivision != null ? orgDivision.Code : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.CodeEmp] = profile.CodeEmp;
row[Att_ReportSumaryInOutEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportSumaryInOutEntity.FieldNames.PositionName] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.JobtitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
var workDay1 = workDayProfiles.FirstOrDefault();
row[Att_ReportSumaryInOutEntity.FieldNames.Date] = date;
row[Att_ReportSumaryInOutEntity.FieldNames.DateFrom] = DateFrom.Date;
row[Att_ReportSumaryInOutEntity.FieldNames.DateTo] = DateTo;
row[Att_ReportSumaryInOutEntity.FieldNames.DateExport] = DateTime.Now;
row[Att_ReportSumaryInOutEntity.FieldNames.UserExport] = userExport;
// row[Att_ReportSumaryInOutEntity.FieldNames.UserExport] = Session[SessionObjects.UserLogin];
if (workDay1 != null)
{
//Guid? ShiftID = workDay1.ShiftApprove ?? workDay1.ShiftID;
var shift = shifts.FirstOrDefault(s => s.ID == workDay1.ShiftID);
row[Att_ReportSumaryInOutEntity.FieldNames.ShiftName] = shift != null ? shift.ShiftName : string.Empty;
row[Att_ReportSumaryInOutEntity.FieldNames.udTimeIn] = workDay1.InTime1 != null ? workDay1.InTime1.Value : DateTime.MinValue;
if (workDay1.OutTime1 != null)
row[Att_ReportSumaryInOutEntity.FieldNames.udTimeOut] = workDay1.OutTime1 != null ? workDay1.OutTime1.Value : DateTime.MinValue;
if (workDay1.ShiftApprove != Guid.Empty)
{
shift = shifts.FirstOrDefault(s => s.ID == workDay1.ShiftApprove);
row[Att_ReportSumaryInOutEntity.FieldNames.ApprovedShift] = shift != null ? shift.ShiftName : string.Empty;
}
}
table.Rows.Add(row);
}
}
}
return table;
}
}
public List<Hre_ReportBirthdayEntity> GetReportBirthday(DateTime? DateFrom, DateTime? DateTo, List<Guid> lstProfileIDs)
{
List<Hre_ReportBirthdayEntity> lstReportBirthday = new List<Hre_ReportBirthdayEntity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoProfile = new Hre_ProfileRepository(unitOfWork);
var lstProfile = repoProfile.FindBy(s => s.DateOfBirth != null && s.IsDelete == null && s.DateOfBirth >= DateFrom && s.DateOfBirth <= DateTo && lstProfileIDs.Contains(s.ID)).ToList();
if (lstProfile == null)
{
return lstReportBirthday;
}
List<Guid> lstProfileIDsbyBirthday = lstProfile.Select(s => s.ID).ToList();
//var repoProfile = new Hre_ProfileRepository(unitOfWork);
var lstAllprofile = repoProfile.FindBy(s => lstProfileIDsbyBirthday.Contains(s.ID)).Select(s => new { s.ID, s.ProfileName, s.CodeEmp, s.OrgStructureID, s.PositionID, s.JobTitleID, s.DateHire, s.DateOfBirth, s.PlaceOfBirth }).ToList();
var repoOrgStructure = new Cat_OrgStructureRepository(unitOfWork);
var lstOrgStructure = repoOrgStructure.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.OrgStructureName }).ToList();
var repoPosition = new Cat_PositionRepository(unitOfWork);
var lstPosition = repoPosition.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.PositionName }).ToList();
var repoJobtitle = new Cat_JobTitleRepository(unitOfWork);
var lstJobtitle = repoJobtitle.FindBy(s => s.IsDelete == null).Select(s => new { s.ID, s.JobTitleName }).ToList();
foreach (var ProfileBirthday in lstProfile)
{
Hre_ReportBirthdayEntity ReportBirthday = new Hre_ReportBirthdayEntity();
//var profileByContract = lstAllprofile.Where(s => contract.ProfileID == s.ID).FirstOrDefault();
//if (profileByContract == null)
// continue;
var orgByProfile = lstOrgStructure.Where(s => ProfileBirthday.OrgStructureID == s.ID).FirstOrDefault();
var positionByProfile = lstPosition.Where(s => ProfileBirthday.PositionID == s.ID).FirstOrDefault();
var jobtitleByProfile = lstJobtitle.Where(s => ProfileBirthday.JobTitleID == s.ID).FirstOrDefault();
ReportBirthday.CodeEmp = ProfileBirthday.CodeEmp;
ReportBirthday.ProfileName = ProfileBirthday.ProfileName;
ReportBirthday.ProfileID = ProfileBirthday.ID;
ReportBirthday.OrgStructureName = orgByProfile != null ? orgByProfile.OrgStructureName : "";
ReportBirthday.JobTitleName = jobtitleByProfile != null ? jobtitleByProfile.JobTitleName : "";
ReportBirthday.PositionName = positionByProfile != null ? positionByProfile.PositionName : "";
ReportBirthday.DateTo = ProfileBirthday.DateTo;
ReportBirthday.DateFrom = ProfileBirthday.DateFrom;
ReportBirthday.DateHire = ProfileBirthday.DateHire;
ReportBirthday.DateOfBirth = ProfileBirthday.DateOfBirth;
ReportBirthday.PlaceOfBirth = ProfileBirthday.PlaceOfBirth;
lstReportBirthday.Add(ReportBirthday);
}
return lstReportBirthday;
}
}
public DataTable GetReportProfileAllowLimitOvertime(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, string orgStructureID, string userExport, bool isCreateTemplate, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
DataTable table = CreateReportProfileAllowLimitOvertime();
if (isCreateTemplate)
{
return table;
}
if (profiles.Count == 0)
return table;
BaseService baservice = new BaseService();
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 positions = repoCat_Position.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.PositionName }).ToList();
var jobtitles = repoCat_JobTitle.FindBy(s => s.IsDelete == null && s.Code != null).Select(s => new { s.ID, s.Code, s.JobTitleName }).ToList();
string status = string.Empty;
List<object> paraAtt_AllowLimitOvertime = new List<object>();
paraAtt_AllowLimitOvertime.AddRange(new object[3]);
paraAtt_AllowLimitOvertime[0] = (object)orgStructureID;
paraAtt_AllowLimitOvertime[1] = DateFrom;
paraAtt_AllowLimitOvertime[2] = DateTo;
// var lstAllowLimit = new List<Att_AllowLimitOvertime>().Select(m => new { m.ProfileID, m.DateStart, m.DateEnd });
var lstAllowLimit = baservice.GetData<Att_AllowLimitOvertimeEntity>(paraAtt_AllowLimitOvertime, ConstantSql.hrm_att_getdata_AllowLimitOvertime, UserLogin, ref status).ToList().Translate<Att_AllowLimitOvertime>();
if (lstAllowLimit.Count == 0)
return table;
List<Guid> lstProfileIds = lstAllowLimit.Select(m => m.ProfileID ?? Guid.Empty).ToList();
profiles = profiles.Where(m => lstProfileIds.Contains(m.ID)).ToList();
var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
foreach (var AllowLimit in lstAllowLimit)
{
DataRow row = table.NewRow();
var profile = profiles.Where(m => m.ID == AllowLimit.ProfileID).FirstOrDefault();
if (profile != null)
{
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.ProfileName] = profile.ProfileName;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.CodeEmp] = profile.CodeEmp;
Guid? orgId = 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 orgDivision = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DivisionCode] = orgDivision != null ? orgDivision.Code : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DivisionName] = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.Position] = positon != null ? positon.PositionName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.JobTitle] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateFrom] = AllowLimit.DateStart;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateTo] = AllowLimit.DateEnd;
row[Att_ReportProfileAllowLimitOvertimeEntity.FieldNames.DateExport] = DateTime.Now;
}
table.Rows.Add(row);
}
return table;
}
}
public Eva_BonusSalaryEntity CalculateBonusSalary(Eva_BonusSalaryEntity model,string userLogin)
{
/*
* Goal(Tính lương thưởng)
* Steps :
* Step1 : Lay tat ca chuc Danh trong phong ban,lay tat ca nv có chức danh
* Step2 : Từ Chuc Danh , Tieu Chi , Loai Thuong,thang (lay từ mức, đến mức)
* Step2 : Tự động insert xuống table Eva_PerformanceForDetail
* Step3 : Tự động insert xuống table Eva_PerformanceEva &Eva_PerformanceEvaDetail
* - Tự Đánh Giá : orderNo =0 (Ngoai ra orderNo >0)
*/
var status = string.Empty;
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork) (new UnitOfWork(context));
var repoJobTitle = new Cat_JobTitleRepository(unitOfWork);
var repoOrg = new Cat_OrgStructureRepository(unitOfWork);
var repoSaleBonus = new Eva_SaleBonusRepository(unitOfWork);
var repoBonusSalary = new Eva_BonusSalaryRepository(unitOfWork);
var repoSaleEvaluation = new Eva_SaleEvaluationRepository(unitOfWork);
var repoProfile = new Hre_ProfileRepository(unitOfWork);
//var orgId = Guid.Parse(model.OrgStructureID);
//var a = repoOrg.FindBy(p => p.ID == orgId).Select(p=>p.OrderNumber).FirstOrDefault();
//if (a != null)
//{
// model.OrgStructureID = a.ToString();
//}
List<object> listObj = new List<object>();
listObj.Add(model.OrgStructureID);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var profilebyOrgs = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status);
//var profilebyOrgs = repoProfile.FindBy(x => x.OrgStructureID == Guid.Parse(model.OrgStructureID)).ToList();
var profiles = profilebyOrgs.Where(p => p.JobTitleID != null).ToList();
var jobTitleIds = profilebyOrgs.Where(p => p.JobTitleID != null).Select(p=>p.JobTitleID).Distinct().ToList();
var jobTitle = repoJobTitle.FindBy(p => jobTitleIds.Contains(p.ID)).ToList();
//lay tu muc den muc
var month = DateTime.Now;
// lấy danh sách thưởng doanh số dựa vào loại doanh số và chức danh và loại
var lstsalebonus = repoSaleBonus.FindBy(x => jobTitleIds.Contains(x.JobTittleID.Value) && x.Type == model.Type && x.SalesTypeID==model.SalesTypeID&&x.IsDelete==null).ToList();
// lấy danh sách doanh số dựa vào loại doanh số và chức danh và loại
if (model.Month != null)
{
month = model.Month.Value;
}
List<Eva_BonusSalary> lstEva_BonusSalaryEntity = new List<Eva_BonusSalary>();
if (profiles == null || profiles.Count == 0)
{
return null;
}
var lstBonusSalaryEdit = repoBonusSalary.FindBy(x => x.SalesTypeID == model.SalesTypeID && x.Type == model.Type && x.IsDelete == null).ToList();
var lstSaleEvaluation = repoSaleEvaluation.FindBy(x=> x.SalesTypeID == model.SalesTypeID && x.IsDelete == null).ToList();
if (model.Type == EvaBonusType.E_Month.ToString())
{
foreach (var item in profiles)
{
var lstBonusSalaryEdit2 = lstBonusSalaryEdit.Where(x => x.ProfileID == item.ID&& x.Month.Value.Month == model.Month.Value.Month).ToList();
var objSaleEvaluation = lstSaleEvaluation.Where(x => x.ProfileID == item.ID && x.Year.Value.Month == model.Month.Value.Month).FirstOrDefault();
if (lstBonusSalaryEdit2 != null && lstBonusSalaryEdit2.Count != 0)
{
var objBonusSalaryEdit = lstBonusSalaryEdit2.FirstOrDefault();
if (objSaleEvaluation != null)
{
var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= objSaleEvaluation.ResultPercent && x.ToPercent >= objSaleEvaluation.ResultPercent && x.JobTittleID == item.JobTitleID).ToList();
if (SaleBonus != null && SaleBonus.Count != 0)
{
objBonusSalaryEdit.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString());
repoBonusSalary.SaveChanges();
}
}
}
else
{
var objBonusSalary = new Eva_BonusSalary();
// objBonusSalary = model;
objBonusSalary.SalesTypeID = model.SalesTypeID;
objBonusSalary.ProfileID = item.ID;
objBonusSalary.Type = model.Type;
objBonusSalary.Month = month;
objBonusSalary.Bonus = 0;
if (objSaleEvaluation != null)
{
var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= objSaleEvaluation.ResultPercent && x.ToPercent >= objSaleEvaluation.ResultPercent && x.JobTittleID == item.JobTitleID).ToList();
if (SaleBonus != null && SaleBonus.Count != 0)
{
objBonusSalary.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString());
}
}
lstEva_BonusSalaryEntity.Add(objBonusSalary);
}
}
repoBonusSalary.Add(lstEva_BonusSalaryEntity);
repoBonusSalary.SaveChanges();
}
if (model.Type == EvaBonusType.E_Quarter.ToString())
{
//int quarter = //((month.Month-1)/ 3) + 1;Quarter
int quarter = (int)Enum.Parse(typeof(Quarter), model.Quarter);
DateTime firstDayOfQuarter = new DateTime(month.Year, (quarter - 1) * 3 + 1, 1);
DateTime lastDayOfQuarter = firstDayOfQuarter.AddMonths(3).AddDays(-1);
foreach (var item in profiles)
{
var objSaleEvaluation = lstSaleEvaluation.Where(x => x.ProfileID == item.ID && x.Year <= lastDayOfQuarter && x.Year >= firstDayOfQuarter).ToList();
// var lstBonusSalaryEdit = repoBonusSalary.FindBy(x => x.ProfileID == item.ID && x.SalesTypeID == model.SalesTypeID && x.Type == model.Type && x.Month.Value.Month == model.Month.Value.Month).ToList();
var lsttBonusSalaryEdit2 = lstBonusSalaryEdit.Where(x => x.ProfileID == item.ID && x.Month <= lastDayOfQuarter && x.Month >= firstDayOfQuarter).ToList();
if (lsttBonusSalaryEdit2 != null && lsttBonusSalaryEdit2.Count != 0)
{
var objBonusSalaryEdit = lsttBonusSalaryEdit2.FirstOrDefault();
if (objSaleEvaluation != null && objSaleEvaluation.Count != 0)
{
var AveragePercent = objSaleEvaluation.Sum(x => x.ResultPercent) / 3;
var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= AveragePercent && x.ToPercent >= AveragePercent && x.JobTittleID == item.JobTitleID).ToList();
if (SaleBonus != null && SaleBonus.Count != 0)
{
objBonusSalaryEdit.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString());
repoBonusSalary.SaveChanges();
}
}
}
else
{
var objBonusSalary = new Eva_BonusSalary();
//objBonusSalary = model;
objBonusSalary.SalesTypeID = model.SalesTypeID;
// objBonusSalary.Quarter = quarter.ToString();
objBonusSalary.ProfileID = item.ID;
objBonusSalary.Type = model.Type;
objBonusSalary.Month = month;
objBonusSalary.Bonus = 0;
if (objSaleEvaluation != null && objSaleEvaluation.Count != 0)
{
var AveragePercent = objSaleEvaluation.Sum(x => x.ResultPercent) / 3;
var SaleBonus = lstsalebonus.Where(x => x.FromPercent <= AveragePercent && x.ToPercent >= AveragePercent && x.JobTittleID == item.JobTitleID).ToList();
if (SaleBonus != null && SaleBonus.Count != 0)
{
objBonusSalary.Bonus = float.Parse(SaleBonus.FirstOrDefault().Amount.ToString());
}
}
lstEva_BonusSalaryEntity.Add(objBonusSalary);
}
}
repoBonusSalary.Add(lstEva_BonusSalaryEntity);
repoBonusSalary.SaveChanges();
}
}
return model;
}
/// <summary>
/// Lấy Dữ Liệu BC Báo cáo tổng số lần đi muộn về sớm trong tháng
/// </summary>
/// <param name="DateFrom">Ngày bắt đầu</param>
/// <param name="DateTo">Ngày kết thúc (Cuối ngày 23:59:59)</param>
/// <param name="lstProfileIDs">Ds ProfileIDs</param>
/// <returns></returns>
public List<Att_ReportSumaryLateInOutEntity> GetReportSumaryLateInOut(DateTime DateFrom, DateTime DateTo, List<Hre_ProfileEntity> profiles, Guid[] ShiftIDs, bool isIncludeQuitEmp, bool noDisplay0Data, string userExport)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoAtt_Workday = new Att_WorkDayRepository(unitOfWork);
List<Att_ReportSumaryLateInOutEntity> lstReportSumaryLateInOut = new List<Att_ReportSumaryLateInOutEntity>();
var repoCat_Shift = new Cat_ShiftRepository(unitOfWork);
var shifts = repoCat_Shift.GetAll().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();
if (!isIncludeQuitEmp)
{
profiles = profiles.Where(s => s.DateQuit == null || s.DateQuit > DateTo).ToList();
}
var workDays = repoAtt_Workday.FindBy(s =>
DateFrom <= s.WorkDate && s.WorkDate <= DateTo
&& s.LateEarlyDuration > 0 && s.IsDelete == null)
.Select(m => new { m.ID, m.ProfileID, m.LateDuration1, m.EarlyDuration1, m.LateEarlyDuration, m.ShiftID }).ToList();
if (ShiftIDs != null)
{
workDays = workDays.Where(s => s.ShiftID.HasValue && ShiftIDs.Contains(s.ShiftID.Value)).ToList();
}
//var lstWorkday = workDays.Select(m => new { m.ID, m.ProfileID, m.LateDuration1, m.EarlyDuration1, m.LateEarlyDuration }).ToList();
if (noDisplay0Data)
{
var profileIDs = workDays.Select(s => s.ProfileID).ToList();
profiles = profiles.Where(s => profileIDs.Contains(s.ID)).ToList();
}
foreach (var profile in profiles)
{
Att_ReportSumaryLateInOutEntity rptSumaryLateInOut = new Att_ReportSumaryLateInOutEntity();
Guid? orgId = 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);
rptSumaryLateInOut.GroupCode = orgBranch != null ? orgBranch.Code : string.Empty;
rptSumaryLateInOut.DepartmentCode = orgOrg != null ? orgOrg.Code : string.Empty;
rptSumaryLateInOut.Division = orgTeam != null ? orgTeam.Code : string.Empty; ;
rptSumaryLateInOut.SectionCode = orgSection != null ? orgSection.Code : string.Empty;
rptSumaryLateInOut.CodeEmp = profile.CodeEmp;
rptSumaryLateInOut.ProfileName = profile.ProfileName;
var positon = positions.FirstOrDefault(s => s.ID == profile.PositionID);
rptSumaryLateInOut.PositionCode = positon != null ? positon.Code : string.Empty;
// var jobtitle = jobtitles.FirstOrDefault(s => s.ID == profile.JobTitleID);
rptSumaryLateInOut.DateFrom = DateFrom;
rptSumaryLateInOut.DateTo = DateTo;
rptSumaryLateInOut.DateExport = DateTime.Today;
rptSumaryLateInOut.UserExport = userExport;
var lstWorkday_ByProfile = workDays.Where(m => m.ProfileID == profile.ID).ToList();
if (lstWorkday_ByProfile != null && lstWorkday_ByProfile.Count > 0)
{
var sumEarly = lstWorkday_ByProfile.Sum(s => s.EarlyDuration1);
var SumLateMinute = lstWorkday_ByProfile.Sum(s => s.LateDuration1);
var CountLate = lstWorkday_ByProfile.Count(m => m.LateDuration1 > 0);
var CountEarly = lstWorkday_ByProfile.Count(m => m.EarlyDuration1 > 0);
rptSumaryLateInOut.EarlyMinutes = sumEarly > 0 ? sumEarly : null;
rptSumaryLateInOut.LateMinutes = SumLateMinute > 0 ? SumLateMinute : null;
rptSumaryLateInOut.NumTimeEarly = CountEarly > 0 ? (int?)CountEarly : null;
rptSumaryLateInOut.NumTimeLate = CountLate > 0 ? (int?)CountLate : null;
}
lstReportSumaryLateInOut.Add(rptSumaryLateInOut);
}
return lstReportSumaryLateInOut;
}
}
