public DataTable GetReportProfile(List<Hre_ProfileEntity> lstprofile, Guid _CutOffDurationID, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (UnitOfWork)(new UnitOfWork(context));
DataTable table = CreateReportProfileScheme();
if (lstprofile == null)
return table;
//var repoCat_OrgStructure = new Cat_OrgStructureRepository(unitOfWork);
//var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
//var orgs = repoCat_OrgStructure.FindBy(s => s.Code != null).ToList();
//var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
//hop dong
string status = string.Empty;
var hreServiceContract = new Hre_ContractServices();
List<object> paraContract = new List<object>();
paraContract.AddRange(new object[21]);
paraContract[19] = 1;
paraContract[20] = int.MaxValue;
var lstContract = hreServiceContract.GetData<Hre_ContractEntity>(paraContract, ConstantSql.hrm_hr_sp_get_Contract, UserLogin, ref status);
//ky cong
string cutoff = Common.DotNetToOracle(_CutOffDurationID.ToString());
var baseService = new BaseService();
var objAtt_CutOffDurationEntity = baseService
.GetData<Att_CutOffDurationEntity>(cutoff, ConstantSql.hrm_att_sp_get_CutOffDurationById, UserLogin, ref status)
.FirstOrDefault();
//
DateTime _dateFrom = DateTime.MinValue;
DateTime _dateTo = DateTime.MaxValue;
if (objAtt_CutOffDurationEntity != null)
{
if (objAtt_CutOffDurationEntity.DateStart != null)
_dateFrom = objAtt_CutOffDurationEntity.DateStart;
if (objAtt_CutOffDurationEntity.DateEnd != null)
_dateTo = objAtt_CutOffDurationEntity.DateEnd;
}
var salServiceBasicSalary = new Sal_BasicSalaryServices();
List<object> paraBasicSalary = new List<object>();
paraBasicSalary.AddRange(new object[4]);
paraBasicSalary[0] = _dateFrom;
paraBasicSalary[1] = _dateTo;
paraBasicSalary[2] = 1;
paraBasicSalary[3] = int.MaxValue - 1;
var lstBasicSalary = salServiceBasicSalary.GetData<Sal_BasicSalaryEntity>(paraBasicSalary, ConstantSql.hrm_sal_sp_getdata_BasicSalaryByCutOff, UserLogin, ref status);
//Hre_ProfilePartyUnion
var repoHre_ProfilePartyUnion = new Hre_ProfilePartyUnionRepository(unitOfWork);
var lstProfilePartyUnion = repoHre_ProfilePartyUnion.FindBy(s => s.IsDelete == null && s.IsTradeUnionist == true && s.YouthUnionEnrolledDate <= _dateTo).ToList();
//Hre_StopWorking
List<object> paraStopWorking = new List<object>();
paraStopWorking.AddRange(new object[17]);
paraStopWorking[6] = _dateFrom;
paraStopWorking[7] = _dateTo;
paraStopWorking[15] = 1;
paraStopWorking[16] = int.MaxValue - 1;
var hreServiceStopWorking = new Hre_StopWorkingServices();
var lstStopWorking = hreServiceStopWorking.GetData<Hre_StopWorkingEntity>(paraStopWorking, ConstantSql.hrm_hr_sp_get_StopWorking, UserLogin, ref status);
//Ins_ProfileInsuranceMonthly
var repoProfileInsuranceMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var lstProfileInsuranceMonthly = repoProfileInsuranceMonthly.FindBy(s => s.IsDelete == null && s.MonthYear >= _dateFrom && s.MonthYear <= _dateTo).ToList();
foreach (var profile in lstprofile)
{
if (profile != null)
{
var objContract = lstContract.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateUpdate).FirstOrDefault();
var objBasicSalary = lstBasicSalary.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateOfEffect).FirstOrDefault();
var objProfilePartyUnion = lstProfilePartyUnion.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.YouthUnionEnrolledDate).FirstOrDefault();
var objStopWorking = lstStopWorking.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.DateStop).FirstOrDefault();
var objProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(s => s.ProfileID == profile.ID).OrderByDescending(s => s.MonthYear).FirstOrDefault();
DataRow row = table.NewRow();
if (profile.CodeEmp != null)
row[Sal_ReportProfileEntity.FieldNames.CodeEmp] = profile.CodeEmp;
if (profile.ProfileName != null)
row[Sal_ReportProfileEntity.FieldNames.ProfileName] = profile.ProfileName;
if (profile.UnitNameOrg != null)
row[Sal_ReportProfileEntity.FieldNames.UnitNameOrg] = profile.UnitNameOrg;
if (profile.DepartmentNameOrg != null)
row[Sal_ReportProfileEntity.FieldNames.DepartmentNameOrg] = profile.DepartmentNameOrg;
if (profile.SectionNameOrg != null)
row[Sal_ReportProfileEntity.FieldNames.SectionNameOrg] = profile.SectionNameOrg;
if (profile.TeamNameOrg != null)
row[Sal_ReportProfileEntity.FieldNames.TeamNameOrg] = profile.TeamNameOrg;
if (profile.WorkPlaceName != null)
row[Sal_ReportProfileEntity.FieldNames.WorkPlaceName] = profile.WorkPlaceName;
if (profile.StatusSyn != null)
row[Sal_ReportProfileEntity.FieldNames.StatusSyn] = profile.StatusSyn;
if (objBasicSalary != null && objBasicSalary.SalaryClassName != null)
row[Sal_ReportProfileEntity.FieldNames.SalaryClassName] = objBasicSalary.SalaryClassName;
if (objBasicSalary != null && objBasicSalary.GrossAmount != null)
row[Sal_ReportProfileEntity.FieldNames.BasicSalary] = objBasicSalary.GrossAmount;
if (objBasicSalary != null && objBasicSalary.SalaryRankName != null)
row[Sal_ReportProfileEntity.FieldNames.SalaryRankName] = objBasicSalary.SalaryRankName;
if (profile.CostCentreCode != null)
row[Sal_ReportProfileEntity.FieldNames.CostCentreCode] = profile.CostCentreCode;
if (profile.IDNo != null)
row[Sal_ReportProfileEntity.FieldNames.IDNo] = profile.IDNo;
if (profile.DateOfBirth != null)
row[Sal_ReportProfileEntity.FieldNames.DateOfBirth] = profile.DateOfBirth;
if (profile.DateHire != null)
row[Sal_ReportProfileEntity.FieldNames.DateHire] = profile.DateHire;
if (objContract != null)
{
if (objContract.DateStart != null)
row[Sal_ReportProfileEntity.FieldNames.DateStart] = objContract.DateStart;
if (objContract.DateEnd != null)
row[Sal_ReportProfileEntity.FieldNames.DateEnd] = objContract.DateEnd;
}
//if (profile.WorkingPlace != null)
// row[Sal_ReportProfileEntity.FieldNames.WorkingPlace] = profile.WorkingPlace;
if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsUnEmpInsurance != null)
row[Sal_ReportProfileEntity.FieldNames.IsUnEmpInsurance] = objProfileInsuranceMonthly.IsUnEmpInsurance;
if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsSocialInsurance != null)
row[Sal_ReportProfileEntity.FieldNames.IsSocialInsurance] = objProfileInsuranceMonthly.IsSocialInsurance;
if (objProfileInsuranceMonthly != null && objProfileInsuranceMonthly.IsHealthInsurance != null)
row[Sal_ReportProfileEntity.FieldNames.IsHealthInsurance] = objProfileInsuranceMonthly.IsHealthInsurance;
if (objProfilePartyUnion != null && objProfilePartyUnion.IsTradeUnionist != null)
row[Sal_ReportProfileEntity.FieldNames.IsTradeUnionist] = objProfilePartyUnion.IsTradeUnionist;
if (objStopWorking != null && objStopWorking.DateStop != null)
row[Sal_ReportProfileEntity.FieldNames.DateStop] = objStopWorking.DateStop;
if (profile.DateHire != null)
row[Sal_ReportProfileEntity.FieldNames.DateHire] = profile.DateHire;
table.Rows.Add(row);
}
}
return table.ConfigTable(true);
}
}
public DataTable BC_INSURANCETOTAL(DateTime DateStart, DateTime DateEnd, string orgs, List<Guid> socialInsPlaceIDs, string userLogin)
{
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoProfileInsMonthly = new Ins_ProfileInsuranceMonthlyRepository(unitOfWork);
var repoCat_Region = new Cat_RegionRepository(unitOfWork);
var repoCat_Province = new Cat_ProvinceRepository(unitOfWork);
string status = string.Empty;
#region get province
var provinces = repoCat_Province.FindBy(p => p.IsDelete == null).ToList();
#endregion
#region Get Profiles by orgs
var listObj = new List<object> { orgs, string.Empty, string.Empty };
var lstProfiles = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status);
#endregion
List<Guid> lstProfileID = lstProfiles.Select(m => m.ID).ToList();
// Cần phải where thêm điều kiện là lấy 1 trong 2 loại (có một loại là chính và một loại là tạm - 2 loại này dùng để ra báo cáo chênh lệch)
//Ps. Hàm bên dưới thiếu điều kiện where đó.
List<Ins_ProfileInsuranceMonthly> lstProfileInsuranceMonthly = repoProfileInsMonthly.FindBy(m => m.ProfileID != null && m.MonthYear >= DateStart && m.MonthYear <= DateEnd).ToList();
lstProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(m => lstProfileID.Contains(m.ProfileID.Value)).ToList();
if (socialInsPlaceIDs != null)
{
socialInsPlaceIDs = socialInsPlaceIDs.Where(p => p != null && p != Guid.Empty).ToList();
if (socialInsPlaceIDs.Any())
{
lstProfileInsuranceMonthly = lstProfileInsuranceMonthly.Where(p => socialInsPlaceIDs != null && socialInsPlaceIDs.Count > 0 && socialInsPlaceIDs.Contains(p.SocialInsPlaceID ?? Guid.Empty)).ToList();
}
}
var socialInsPlaceObjs = lstProfileInsuranceMonthly.Select(p => p.SocialInsPlaceID).Distinct().ToList();
provinces = provinces.Where(p => socialInsPlaceObjs.Contains(p.ID)).ToList();
DataTable dt = CreateReportInsuranceScheme();
int stt = 0;
for (DateTime DateCheck = DateStart; DateCheck <= DateEnd; DateCheck = DateCheck.AddMonths(1))
{
//danh sach du lieu BH tháng năm đang kiểm tra
var lstProfileInsuranceMonthly_ByMonth = lstProfileInsuranceMonthly.Where(m => m.IsSocialInsurance.HasValue && m.IsSocialInsurance.Value && m.MonthYear.HasValue && m.MonthYear.Value.Year == DateCheck.Year
&& m.MonthYear.Value.Month == DateCheck.Month).ToList();
if (!lstProfileInsuranceMonthly_ByMonth.Any())
{
continue;
}
// stt++;
DataRow dr = dt.NewRow();
#region Thêm dòng đầu (dòng ngày tháng và tổng số lượng NV , tổng tiền đóng BH)
stt++;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.Stt] = stt;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.Name] = DateCheck.ToString(ConstantFormat.HRM_Format_MonthYear);
var TotalEmp = lstProfileInsuranceMonthly_ByMonth.Select(m => m.ProfileID).Distinct().ToList().Count;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalEmp] = TotalEmp;
#region Tổng Lương BHXH +BHYT+ BHTN (khong cộng phần điều chỉnh)
var totalSocial = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount);
var totalHealth = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount);
var totalUnEmp = lstProfileInsuranceMonthly_ByMonth.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.UnemployComAmount + m.UnemployEmpAmount);
#endregion
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHXH] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount);
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHYT] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); ;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHTN] = lstProfileInsuranceMonthly_ByMonth.Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); ;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalAll] = totalSocial + totalHealth + totalUnEmp;
dt.Rows.Add(dr);
#endregion
#region Ds nơi đóng BH theo tháng năm của NV
var tempSocialInsPlaceIds = lstProfileInsuranceMonthly_ByMonth.Select(p => p.SocialInsPlaceID).Distinct().ToList();
var provinceByInsProfileMonthlys = provinces.Where(p => tempSocialInsPlaceIds.Contains(p.ID)).ToList();
foreach (var insSocialPlace in provinceByInsProfileMonthlys)
{
dr = dt.NewRow();
var profileInsuranceMonthly_ByMonths = lstProfileInsuranceMonthly_ByMonth.Where(p => p.SocialInsPlaceID == insSocialPlace.ID).ToList();
dr[Ins_ReportInsuranceTotalEntity.FieldNames.Stt] = DBNull.Value;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.Name] = insSocialPlace.ProvinceName;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalEmp] = profileInsuranceMonthly_ByMonths.Select(m => m.ProfileID).Distinct().ToList().Count;
#region Tổng Lương BHXH +BHYT+ BHTN (khong cộng phần điều chỉnh)
var wtotalSocial = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount);
var wtotalHealth = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount);
var wtotalUnEmp = profileInsuranceMonthly_ByMonths.Where(m => m.IsPayback == null || m.IsPayback.Value == false).Sum(m => m.UnemployComAmount + m.UnemployEmpAmount);
#endregion
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHXH] = profileInsuranceMonthly_ByMonths.Sum(m => m.SocialInsComAmount + m.SocialInsEmpAmount); ;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHYT] = profileInsuranceMonthly_ByMonths.Sum(m => m.HealthInsComAmount + m.HealthInsEmpAmount); ;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.BHTN] = profileInsuranceMonthly_ByMonths.Sum(m => m.UnemployComAmount + m.UnemployEmpAmount); ;
dr[Ins_ReportInsuranceTotalEntity.FieldNames.TotalAll] = wtotalSocial+wtotalHealth+wtotalUnEmp;
dt.Rows.Add(dr);
}
stt = 0;
#endregion
}
return dt.ConfigTable(true);
}
}