public void NUnit_Contract_Domain_Add()
{
Cat_OrgStructureServices service = new Cat_OrgStructureServices();
bool result = true;
int countSC = 0;
for (int i = 1; i <= 10; i++)
{
var model = new Cat_OrgStructure
{
OrgStructureName = "Phong Ban " + i,
Code = "Add Mã " + i,
IsRoot = true,
Description = "Mô Tả " + i,
TypeID = 1,
ParentID = 1,
OrderNumber = i
};
result = service.AddCatOrgStructure(model);
NUnit.Framework.Assert.IsTrue(result);
if (result == true)
{
countSC += 1;
Console.WriteLine("Process Success >>> Create >>> " + model.Id
+ " | " + model.OrgStructureName
+ " | " + model.Code
+ " | " + model.IsRoot
+ " | " + model.Description
+ " | " + model.TypeID
+ " | " + model.ParentID
+ " | " + model.OrderNumber
);
}
}
Console.WriteLine("Total success record: " + countSC);
}
// GET api/<controller>/5
public Sys_LockObjectModel Get(Guid id)
{
string status = string.Empty;
var model = new Sys_LockObjectModel();
ActionService service = new ActionService(UserLogin);
var cutOfServices = new Att_CutOffDurationServices();
var orgServices = new Cat_OrgStructureServices();
var entity = service.GetByIdUseStore<Sys_LockObjectEntity>(id, ConstantSql.hrm_sys_sp_get_LockObjectByID, ref status);
List<object> listModel = new List<object>();
listModel.AddRange(new object[3]);
listModel[1] = 1;
listModel[2] = Int32.MaxValue - 1;
List<Att_CutOffDurationEntity> listCutoffduration_All = cutOfServices.GetData<Att_CutOffDurationEntity>(listModel, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList();
var objOrg = new List<object>();
objOrg.Add(null);
objOrg.Add(null);
objOrg.Add(null);
objOrg.Add(1);
objOrg.Add(int.MaxValue -1);
var lstOrg = orgServices.GetData<Cat_OrgStructureEntity>(objOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, UserLogin, ref status).ToList();
if (entity != null)
{
model = entity.CopyData<Sys_LockObjectModel>();
if (model.PayrollGroups != null)
{
model.lstPayrollGroupID = Common.GetListNumbersFromBinary(model.PayrollGroups);
model.PayrollGroupID = string.Join(",", model.lstPayrollGroupID);
}
if (model.OrgStructures != null)
{
model.lstOrgStructureID = Common.GetListNumbersFromBinary(model.OrgStructures);
var lstOrgName = lstOrg.Where(s => model.lstOrgStructureID.Contains(s.OrderNumber)).Select(s => s.OrgStructureName).ToList();
model.OrgStructureID = string.Join(",", model.lstOrgStructureID);
model.OrgStructureName = string.Join(",", lstOrgName);
}
var cutOfDurationEntity = listCutoffduration_All.Where(s => s.DateEnd == model.DateEnd && s.DateStart == model.DateStart).FirstOrDefault();
if (cutOfDurationEntity != null)
{
model.CutOffDurationID = cutOfDurationEntity.ID;
model.CutOffDurationName = cutOfDurationEntity.CutOffDurationName;
}
}
model.ActionStatus = status;
return model;
}
public void NUnit_Contract_Domain_GetById()
{
Cat_OrgStructureServices service = new Cat_OrgStructureServices();
var model = new Cat_OrgStructure { Id = 3 };
var result = service.GetByIdCatOrgStructure(model.Id) as Cat_OrgStructure;
NUnit.Framework.Assert.IsNotNull(result);
Console.Write("SearchResult: " + result.Id
+ " | " + result.OrgStructureName
+ " | " + result.Code
+ " | " + result.IsRoot
+ " | " + result.Description
+ " | " + result.TypeID
+ " | " + result.ParentID
+ " | " + result.OrderNumber
);
}
/// <summary>
/// [Hien.Nguyen] Lấy danh sách phòng ban tổng hợp nhân viên
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
///
//[OutputCache(NoStore = true, Duration = 0)]
public JsonResult GetOrgStructureTreeSummaryProfile(Guid? id)
{
var service = new Cat_OrgStructureServices();
string status = string.Empty;
//var listEntity = service.GetData<Cat_OrgStructureTreeViewEntity>(ConstantSql.hrm_cat_sp_get_OrgStructure_Data, ref status);
//var listModel = new List<CatOrgStructureModel>();
//if (listEntity != null)
//{
// listModel = listEntity.Translate<CatOrgStructureModel>();
//}
//var modelChild = OrgStructureParentAndChild(id);
//var org=listModel.Where(m=>id.HasValue?m.ParentID==id:m.ParentID==null).Select(s=>new{id=s.ID
var listModel = new List<CatOrgStructureModel>();
if (HttpContext.Cache["List_OrgStructureTreeViewSumProfile"] == null)
{
var listEntity = service.GetDataNotParam<Cat_OrgStructureTreeViewEntity>(ConstantSql.hrm_cat_sp_get_OrgStructure_Data_SumProfile, UserLogin, ref status);
//listModel = listEntity.Translate<CatOrgStructureModel>();
if (listEntity != null)
{
listModel = listEntity.Translate<CatOrgStructureModel>();
// HttpContext.Cache["List_OrgStructureTreeViewSumProfile"] = listModel;
HttpContext.Cache.Add("List_OrgStructureTreeViewSumProfile", listModel, null, DateTime.Now.AddDays(30), TimeSpan.Zero, CacheItemPriority.Default, null);
}
}
else
{
listModel = HttpContext.Cache["List_OrgStructureTreeViewSumProfile"] as List<CatOrgStructureModel>;
}
var orgStructure = from e in listModel.OrderBy(m => m.OrderNumber)
where (id.HasValue ? e.ParentID == id : e.ParentID == null)
select new
{
id = e.ID,
Name = e.OrgStructureName + " (" + e.ProfileIsWorking + "/" + (GetCountProfile(listModel, e.ID, new int[2])[0] + e.ProfileIsWorking).ToString() + ")",
hasChildren = listModel.Any(ch => ch.ParentID == e.ID),
IconPath = ConstantPathWeb.HrWebUrl + ConstantPath.IconPath + (e.Icon ?? "icon1.png"),
OrderNumber = e.OrderNumber,
Code = e.Code,
ProfileCount = e.ProfileCount,
ProfileIsWorking = e.ProfileIsWorking,
};
return Json(orgStructure, JsonRequestBehavior.AllowGet);
}
public CatOrgStructureModel Post([Bind]CatOrgStructureModel model)
{
#region Validate
string message = string.Empty;
var checkValidate = HRM.Business.Main.Domain.ValidatorService.OnValidateData<CatOrgStructureModel>(model, "Cat_OrgStructure", ref message);
if (!checkValidate)
{
model.ActionStatus = message;
return model;
}
#endregion
ActionService service = new ActionService(UserLogin);
//Xóa cache lưu lại của cây phòng ban
HttpContext.Current.Cache.Remove("List_OrgStructureTreeView");
HttpContext.Current.Cache.Remove("List_OrgStructureTreeViewSumProfile");
#region Get max Order Number of Orgstructure
if (model != null && model.ID == Guid.Empty)
{
Cat_OrgStructureServices orgService = new Cat_OrgStructureServices();
model.OrderNumber = orgService.GetMaxOrgstructureOrder();
}
#endregion
return service.UpdateOrCreate<Cat_OrgStructureEntity, CatOrgStructureModel>(model);
}
DataTable CreateReportHCGenderSchema(Guid orgID, Guid? orgTypeID, string userLogin)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
DataTable datatable = new DataTable("Hre_ReportHCGenderEntity");
DataRow dr = datatable.NewRow();
datatable.Columns.Add(Hre_ReportHCGenderEntity.FieldNames.OrgStructureName);
datatable.Columns.Add(Hre_ReportHCGenderEntity.FieldNames.JobTitleName);
datatable.Columns.Add(Hre_ReportHCGenderEntity.FieldNames.HeadCount);
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
// var lstOrgName = lstallorgs.Where(s => s.ID == orgID).FirstOrDefault();
//dr[Hre_ReportHCGenderEntity.FieldNames.OrgStructureName] = lstOrgName == null ? string.Empty : lstOrgName.OrgStructureName + " - " + Gender;
int count = 0;
foreach (var item in lstorgs)
{
orderNumber = string.Empty;
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
List<Cat_OrgStructure> lstOrgByOrderNumberCount = new List<Cat_OrgStructure>();
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(orderNumber);
if (orderNumber != string.Empty)
{
lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin,ref status).OrderBy(s => s.OrgStructureName).ToList();
}
if (orgTypeID != null)
{
lstOrgByOrderNumberCount = lstOrgByOrderNumberCount.Where(s => s.OrgStructureTypeID == orgTypeID).ToList();
}
foreach (var org in lstOrgByOrderNumberCount)
{
if (!string.IsNullOrEmpty(org.OrgStructureName))
{
//xử lý tạo thêm 1 dòng để chứa những tên phòng ban trùng tên
if (datatable.Columns.Contains(org.OrgStructureName))
{
dr[0] += org.OrgStructureName + ",";
count++;
datatable.Columns.Add(org.OrgStructureName, typeof(int));
}
else
{
//datatable.Columns.Add(org.OrgStructureName);
datatable.Columns.Add(org.OrgStructureName);
}
}
}
}
datatable.Columns.Add(Hre_ReportHCGenderEntity.FieldNames.Total);
// dr[1] = count;
datatable.Rows.Add(dr);
return datatable;
}
}
public DataTable GetReportHCByMonth(DateTime? DateSearch, Guid? orgID, bool isCreateTemplate, string userLogin)
{
DataTable table = CreateReportHCByMonthSchema(orgID);
if (isCreateTemplate)
{
return table.ConfigTable();
}
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var repoPosition = new CustomBaseRepository<Cat_Position>(unitOfWork);
var orgStructureServices = new Cat_OrgStructureServices();
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
var lstOrgName = lstallorgs.Where(s => s.ID == orgID).FirstOrDefault();
var listorgid = lstorgs.Select(s => new { s.ID, s.OrderNumber, s.Code, s.OrgStructureName }).ToList();
var orgIDs = string.Empty;
orderNumber = string.Empty;
foreach (var item in listorgid)
{
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
}
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(orderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin, ref status).Select(s => s.ID).ToList();
List<object> listObj = new List<object>();
listObj.Add(orderNumber);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var lstprofile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).ToList();
var PositionServices = new Cat_PositionServices();
var lstObjPosition = new List<object>();
lstObjPosition.Add(null);
lstObjPosition.Add(null);
lstObjPosition.Add(1);
lstObjPosition.Add(int.MaxValue - 1);
var lstPosition = GetData<Cat_PositionEntity>(lstObjPosition, ConstantSql.hrm_cat_sp_get_Position, userLogin, ref status).ToList();
var orgTypeServices = new Cat_OrgStructureTypeServices();
var objOrgType = new List<object>();
objOrgType.Add(null);
objOrgType.Add(null);
objOrgType.Add(1);
objOrgType.Add(int.MaxValue - 1);
var lstOrgType = orgTypeServices.GetData<Cat_OrgStructureTypeEntity>(objOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, userLogin, ref status).ToList().Translate<Cat_OrgStructureType>();
var PlanHeadCountServices = new Hre_PlanHeadCountServices();
var objPlanHeadCount = new List<object>();
objPlanHeadCount.Add(null);
objPlanHeadCount.Add(1);
objPlanHeadCount.Add(int.MaxValue - 1);
var lstobjPlanHeadCount = orgTypeServices.GetData<Hre_PlanHeadCountEntity>(objPlanHeadCount, ConstantSql.hrm_hr_sp_get_PlanHeadCount, userLogin, ref status).ToList();
foreach (var item in lstOrgByOrderNumber)
{
DataRow row = table.NewRow();
var lstpositionbyOrg = lstPosition.Where(s => s.OrgStructureID == item).ToList();
if (lstpositionbyOrg.Count == 0)
{
continue;
}
foreach (var position in lstpositionbyOrg)
{
Guid? orgId = item;
var org = lstallorgs.FirstOrDefault(s => s.ID == item);
var E_DIVISION = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, lstallorgs, lstOrgType);
var E_DEPARTMENT = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, lstallorgs, lstOrgType);
var E_SECTION = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, lstallorgs, lstOrgType);
var countFirstOfMonth = lstprofile.Where(s => s.DateOfEffect != null && s.PositionID == position.ID && s.DateOfEffect.Value.Month <= DateSearch.Value.Month).Count();
var newHiring = lstprofile.Where(s => s.DateHire != null && s.PositionID == position.ID && s.DateHire.Value.Month == DateSearch.Value.Month).Count();
var resignInMonth = lstprofile.Where(s => s.DateQuit != null && s.PositionID == position.ID && s.DateQuit.Value.Month == DateSearch.Value.Month).Count();
var tranferInMonth = lstprofile.Where(s => s.DateOfEffect != null &&
(s.PositionID == position.ID && s.DateOfEffect.Value.Month < DateSearch.Value.Month)
&& (s.PositionID != position.ID && s.DateOfEffect.Value.Month >= DateSearch.Value.Month)).Count();
var joinInMonth = lstprofile.Where(s => s.DateOfEffect != null &&
(s.PositionID == position.ID && s.DateOfEffect.Value.Month >= DateSearch.Value.Month)
&& (s.PositionID != position.ID && s.DateOfEffect.Value.Month < DateSearch.Value.Month)).Count();
var headcount = lstobjPlanHeadCount.Where(s => s.OrgStructureID == item && s.PostionID == position.ID).FirstOrDefault();
row[Hre_ReportHCByMonthEntity.FieldNames.Division] = E_DIVISION != null ? E_DIVISION.OrgStructureName : null;
row[Hre_ReportHCByMonthEntity.FieldNames.Department] = E_DEPARTMENT != null ? E_DEPARTMENT.OrgStructureName : null;
row[Hre_ReportHCByMonthEntity.FieldNames.Section] = E_SECTION != null ? E_SECTION.OrgStructureName : null;
row[Hre_ReportHCByMonthEntity.FieldNames.Position] = position.PositionName;
row[Hre_ReportHCByMonthEntity.FieldNames.FirstOfMonth] = countFirstOfMonth;
row[Hre_ReportHCByMonthEntity.FieldNames.NewHiring] = newHiring;
row[Hre_ReportHCByMonthEntity.FieldNames.Resign] = resignInMonth;
row[Hre_ReportHCByMonthEntity.FieldNames.Transfer] = tranferInMonth;
row[Hre_ReportHCByMonthEntity.FieldNames.Join] = joinInMonth;
row[Hre_ReportHCByMonthEntity.FieldNames.HeadcountBudget] = headcount != null ? headcount.HrPlanHC : null;
table.Rows.Add(row);
}
}
return table;
}
}
public DataTable GetReportMonthlyHC(DateTime DateSearch, List<Guid?> lstjobTitles, Guid orgID, Guid? orgTypeId, bool isCreateTemplate, string userLogin)
{
DataTable table = CreateReportMonthlyHCSchema();
if (isCreateTemplate)
{
return table;
}
string status = string.Empty;
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var serviceProfile = new Hre_ProfileServices();
var baseService = new BaseService();
var lstObjProfileIDs = new List<object>();
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
var listorgid = lstorgs.Select(s => new { s.ID, s.OrderNumber, s.OrgStructureTypeID }).ToList();
//Xử Lý lấy tất cả nhân viên trong phòng ban đã chọn và group 1 cấp
var orgIDs = string.Empty;
orderNumber = string.Empty;
foreach (var item in listorgid)
{
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
}
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
if (orderNumber == string.Empty)
return table;
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(orderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin ,ref status).Select(s => new { s.ID, s.OrderNumber, s.OrgStructureName, s.OrgStructureTypeID, s.ParentID }).ToList();
if (orgTypeId != null)
{
lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID == orgTypeId).ToList();
}
List<object> listObj = new List<object>();
listObj.Add(orderNumber);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var headCountService = new Hre_PlanHeadCountServices();
var lstObjPlanHeadCount = new List<object>();
lstObjPlanHeadCount.Add(null);
lstObjPlanHeadCount.Add(1);
lstObjPlanHeadCount.Add(10000000);
var lstPlanHeadCount = GetData<Hre_PlanHeadCountEntity>(lstObjPlanHeadCount, ConstantSql.hrm_hr_sp_get_PlanHeadCount, userLogin, ref status).ToList();
var lstProfile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).Where(s => s.DateHire != null && s.DateQuit == null && s.DateHire <= DateSearch).ToList();
var lstprofileWorking = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).Where(s => s.DateHire != null && s.StatusSyn == null && s.DateHire.Value.Year == DateSearch.Year && s.DateQuit == null).ToList();
var lstprofileQuit = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).Where(s => s.DateQuit != null && s.DateQuit.Value.Year == DateSearch.Year).ToList();
var jobtitleService = new Cat_JobTitleServices();
var lstObjJobtitle = new List<object>();
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(1);
lstObjJobtitle.Add(100000);
var lstJobtitle = GetData<Cat_JobTitleEntity>(lstObjJobtitle, ConstantSql.hrm_cat_sp_get_JobTitle, userLogin, ref status).ToList();
if (lstjobTitles != null && lstjobTitles[0] != null && lstjobTitles[0] != Guid.Empty)
{
lstprofileWorking = lstprofileWorking.Where(s => s.JobTitleID != null && lstjobTitles.Contains(s.JobTitleID.Value)).ToList();
lstprofileQuit = lstprofileQuit.Where(s => s.JobTitleID != null && lstjobTitles.Contains(s.JobTitleID.Value)).ToList();
lstJobtitle = lstJobtitle.Where(s => lstjobTitles.Contains(s.ID)).ToList();
}
if (lstprofileWorking == null || lstorgs == null || lstprofileQuit == null)
{
return table;
}
foreach (var org in lstOrgByOrderNumber)
{
DataRow row = table.NewRow();
var parentOrg = lstallorgs.Where(s => s.ID == org.ParentID).FirstOrDefault();
row[Hre_ReportMonthlyHCEntity.FieldNames.OrgStructureParentName] = parentOrg != null ? parentOrg.OrgStructureName : string.Empty;
row[Hre_ReportMonthlyHCEntity.FieldNames.OrgStructureName] = org != null ? org.OrgStructureName : string.Empty;
//xử lý đếm nhân viên của phòng ban con
var IDsCount = string.Empty;
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(orderNumber);
var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID }).ToList();
if (orgTypeId != null)
{
lstOrgByOrderNumberCount = lstOrgByOrderNumberCount.Where(s => s.OrgStructureTypeID == orgTypeId).ToList();
}
var lstOrgGroupByType = lstOrgByOrderNumberCount.Select(s => s.ID).ToList();
bool addTitle = false;
foreach (var jobtitle in lstJobtitle)
{
DataRow row1 = table.NewRow();
var countingPlanHeadCount = 0;
var counttingProfileWorking = 0;
var countingProfileQuit = 0;
if (jobtitle.OrgStructureID != null)
{
if (jobtitle.OrgStructureID == orgID)
{
row1[Hre_ReportMonthlyHCEntity.FieldNames.JobTitleName] = jobtitle != null ? jobtitle.JobTitleName : string.Empty;
#region Approved
var countProfile = 0;
var totalProfile = 0;
var lstProfileWorkingByJobTitleID = lstprofileWorking.Where(s => jobtitle.ID == s.JobTitleID && lstOrgGroupByType.Contains(s.OrgStructureID.Value)).Select(p => new { p.ID, p.DateHire, p.StatusSyn }).ToList();
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 01).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 01).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJan] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 02).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 02).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppFeb] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 03).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 03).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
// row1[Hre_ReportMonthlyHCEntity.FieldNames.AppMar] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 04).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 04).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
// row1[Hre_ReportMonthlyHCEntity.FieldNames.AppApr] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 05).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 05).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
// row1[Hre_ReportMonthlyHCEntity.FieldNames.AppMay] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 06).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 06).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJun] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 07).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 07).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJul] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 08).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 08).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppAug] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 09).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 09).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppSep] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 10).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 10).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
// row1[Hre_ReportMonthlyHCEntity.FieldNames.AppOct] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 11).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 11).ToList().Count();
//countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppNov] = totalProfile;
}
if (lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 12).ToList() != null)
{
countProfile = lstProfileWorkingByJobTitleID.Where(s => s.DateHire.Value.Month == 12).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalProfile += countProfile;
//row1[Hre_ReportMonthlyHCEntity.FieldNames.AppDec] = totalProfile;
}
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJan] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppFeb] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppMar] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppApr] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppMay] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJun] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppJul] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppAug] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppSep] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppOct] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppNov] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppDec] = totalProfile;
row1[Hre_ReportMonthlyHCEntity.FieldNames.AppYear] = totalProfile;
#endregion
#region Actual HC
double countProfileWorking = 0;
double totalProfileWorking = 0;
var lstProfileByJobTitleID = lstProfile.Where(s => jobtitle.ID == s.JobTitleID && lstOrgGroupByType.Contains(s.OrgStructureID.Value)).Select(p => new { p.ID, p.DateHire, p.StatusSyn, p.DateQuit }).ToList();
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 01).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 01).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActJan] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 02).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 02).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActFeb] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 03).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 03).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActMar] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 04).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 04).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActApr] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 05).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 05).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActMay] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 06).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 06).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActJun] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 07).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 07).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActJul] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 08).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 08).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActAug] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 09).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 09).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActSep] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 10).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 10).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActOct] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 11).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 11).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActNov] = countProfileWorking;
}
if (lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 12).ToList() != null)
{
countProfileWorking = lstProfileByJobTitleID.Where(s => s.DateHire.Value.Month == 12).ToList().Count();
// counttingProfileWorking += countProfileWorking;
totalProfileWorking += countProfileWorking;
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActDec] = countProfileWorking;
}
double total = 0;
if (totalProfileWorking / 12 < 1 && totalProfileWorking / 12 > 0)
{
total = 1;
}
else
{
total = Math.Round(totalProfileWorking / 12);
}
row1[Hre_ReportMonthlyHCEntity.FieldNames.ActYear] = totalProfileWorking;
#endregion
#region Leaver HC
var countProfileQuit = 0;
var totalQuit = 0;
var lstProfileQuitByJobTitleID = lstprofileQuit.Where(s => jobtitle.ID == s.JobTitleID && lstOrgGroupByType.Contains(s.OrgStructureID.Value)).Select(p => new { p.ID, p.DateHire, p.StatusSyn, p.DateQuit }).ToList();
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 01).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 01).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaJan] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 02).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 02).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaFeb] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 03).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 03).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaMar] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 04).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 04).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaApr] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 05).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 05).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaMay] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 06).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 06).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaJun] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 07).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 07).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaJul] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 08).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 08).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaAug] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 09).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 09).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaSep] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 10).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 10).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaOct] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 11).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 11).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaNov] = countProfileQuit;
}
if (lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 12).ToList() != null)
{
countProfileQuit = lstProfileQuitByJobTitleID.Where(s => s.DateQuit.Value.Month == 12).ToList().Count();
// countingProfileQuit += countProfileQuit;
totalQuit += countProfileQuit;
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaDec] = countProfileQuit;
}
row1[Hre_ReportMonthlyHCEntity.FieldNames.LeaYear] = totalQuit;
#endregion
//if (totalQuit == 0 && totalProfileWorking == 0)
//{
// continue;
//}
//if (lstProfileQuitByJobTitleID.Count == 0 && lstProfileWorkingByJobTitleID.Count == 0)
//{
// continue;
//}
if (!addTitle)
{
table.Rows.Add(row);
addTitle = true;
}
table.Rows.Add(row1);
}
}
}
}
DataRow datarow = table.NewRow();
datarow[1] = "Total GT";
for (int i = 3; i < table.Columns.Count; i++)
{
int gt = 0;
for (int j = 0; j < table.Rows.Count; j++)
{
var valueRow = table.Rows[j][i].ToString();
if (!string.IsNullOrEmpty(valueRow) && !string.IsNullOrWhiteSpace(valueRow))
{
var value = int.Parse(valueRow);
if (value >= 0)
{
gt += value;
}
}
}
datarow[i] = gt;
}
table.Rows.Add(datarow);
return table;
}
}
public Hre_DisciplineModel Post([Bind]Hre_DisciplineModel model)
{
#region Validate
string message = string.Empty;
var checkValidate = HRM.Business.Main.Domain.ValidatorService.OnValidateData<Hre_DisciplineModel>(model, "Hre_Discipline", ref message);
if (!checkValidate)
{
model.ActionStatus = message;
return model;
}
#endregion
#region cột DepartmentName = phòng ban hiện tại của NV
string status = string.Empty;
var orgServices = new Cat_OrgStructureServices();
var profileServices = new Hre_ProfileServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList();
string orgstructureNameByProfile = string.Empty;
if (model.ProfileID != null)
{
var profilebydis = profileServices.GetData<Hre_ProfileEntity>(Common.DotNetToOracle(model.ProfileID.ToString()), ConstantSql.hrm_hr_sp_get_ProfileById, UserLogin, ref status).FirstOrDefault();
if (profilebydis != null)
{
Guid? orgId = profilebydis.OrgStructureID;
var org = lstOrg.FirstOrDefault(s => s.ID == profilebydis.OrgStructureID);
var department = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_DEPARTMENT, lstOrg, lstOrgType);
orgstructureNameByProfile = department != null ? department.OrgStructureName : string.Empty;
}
}
model.DepartmentName = orgstructureNameByProfile;
#endregion
ActionService service = new ActionService(UserLogin);
return service.UpdateOrCreate<Hre_DisciplineEntity, Hre_DisciplineModel>(model);
}
public ActionResult GetReportWorkHistoryDept([DataSourceRequest] DataSourceRequest request, Hre_ReportWorkHistoryDeptModel Model)
{
#region Validate
string message = string.Empty;
var checkValidate = ValidatorService.OnValidateData<Hre_ReportWorkHistoryDeptModel>(Model, "Hre_ReportWorkHistoryDept", ref message);
if (!checkValidate)
{
return Json(message);
}
#endregion
var actionServices = new ActionService(UserLogin);
var profileServices = new Hre_ProfileServices();
var rptServices = new Hre_ReportServices();
List<object> listObj = new List<object>();
listObj.Add(Model.DateFrom);
listObj.Add(Model.DateTo);
listObj.Add(Model.ProfileName);
listObj.Add(Model.CodeEmp);
listObj.Add(Model.JobTitleID);
listObj.Add(Model.PositionID);
listObj.Add(Model.OrgStructureIDs);
listObj.Add(Model.TypeOfTransferID);
listObj.Add(Model.SalaryClassID);
listObj.Add(Model.WorkPlaceID);
listObj.Add(Model.Status);
listObj.Add(1);
listObj.Add(int.MaxValue - 1);
string status = string.Empty;
var result = actionServices.GetData<Hre_ReportWorkHistoryDeptEntity>(listObj, ConstantSql.hrm_hr_sp_get_RptWorkHistoryDept, ref status).ToList().Translate<Hre_ReportWorkHistoryDeptModel>();
HeaderInfo headerInfo1 = new HeaderInfo() { Name = "DateFrom", Value = Model.DateFrom != null ? Model.DateFrom : DateTime.Now };
HeaderInfo headerInfo2 = new HeaderInfo() { Name = "DateTo", Value = Model.DateTo != null ? Model.DateTo : DateTime.Now };
HeaderInfo headerInfo3 = new HeaderInfo() { Name = "WorkPlaceName", Value = ((result != null && result.FirstOrDefault() != null) && result.FirstOrDefault().WorkPlaceName != null) ? result.FirstOrDefault().WorkPlaceName : "" };
List<HeaderInfo> listHeaderInfo = new List<HeaderInfo>() { headerInfo1, headerInfo2, headerInfo3 };
if (Model != null && Model.IsCreateTemplate)
{
var path = Common.GetPath("Templates");
ExportService exportService = new ExportService();
ConfigExport cfgExport = new ConfigExport()
{
Object = new Hre_ReportWorkHistoryDeptModel(),
FileName = "Hre_ReportWorkHistoryDept",
OutPutPath = path,
HeaderInfo = listHeaderInfo,
DownloadPath = Hrm_Main_Web + "Templates",
IsDataTable = false
};
var str = exportService.CreateTemplate(cfgExport);
return Json(str);
}
bool isgroup = profileServices.IsGroupByOrgProfileQuit();
if (isgroup == true)
{
List<Hre_ReportWorkHistoryDeptModel> resultNew = new List<Hre_ReportWorkHistoryDeptModel>();
if (result.Count > 0)
{
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = actionServices.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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 = actionServices.GetData<Cat_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, ref status).ToList();
foreach (var item in result)
{
var orgName = new List<string>();
if (item.OrgStructureID != null)
{
orgName = rptServices.GetParentOrgName(lstOrg, lstOrgType, item.OrgStructureID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
}
if (orgName.Count > 0)
{
item.Channel = orgName[2];
item.Region = orgName[1];
item.Area = orgName[0];
}
resultNew.Add(item);
}
}
if (Model.ExportID != Guid.Empty)
{
var fullPath = ExportService.Export(Model.ExportID, resultNew, listHeaderInfo, Model.ExportType);
return Json(fullPath);
}
return Json(resultNew.ToDataSourceResult(request));
}
if (Model.ExportID != Guid.Empty)
{
var fullPath = ExportService.Export(Model.ExportID, result, listHeaderInfo, Model.ExportType);
return Json(fullPath);
}
return Json(result.ToDataSourceResult(request));
}
/// <summary>
/// BC bảng lương
/// <param name="dateStartCutOffDuration">Ngày bắt đầu của kỳ lương</param>
/// <param name="monthYear">Tháng</param>
/// <param name="listOrgIDs">Ds phòng ban</param>
/// <param name="listPrGroupIDs">Ds nhóm Lương</param>
/// <param name="isIncludeQuitEmp">Bao gồm NV nghỉ việc</param>
/// </sumary>
public DataTable RefreshData(DateTime dateStartCutOffDuration, DateTime dateEndCutOffDuration, DateTime monthYear, List<Hre_ProfileEntity> listProfile, Guid? gradePayrollID, Boolean isIncludeQuitEmp, string codeEmp, string orderNumber, string Transfer, Guid[] workingPlaceID, Guid[] costcenterIds, string UserLogin)
{
try
{
using (var context = new VnrHrmDataContext())
{
#region " Load dữ liệu"
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
//kỳ lương (hiện tại mặc định đầu tháng -> cuối tháng)
DateTime from = new DateTime(monthYear.Year, monthYear.Month, 1);
DateTime to = new DateTime(monthYear.Year, monthYear.Month, DateTime.DaysInMonth(monthYear.Year, monthYear.Month));
//Ds tất cả phòng ban
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
// var reposOrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork);
var listOrgAll = orgServices.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList();
var lstObjOrgNumber = new List<object>();
lstObjOrgNumber.Add(orderNumber);
var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrgNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList();
//Ds cửa Hàng
var shopServices = new Cat_ShopServices();
var lstObjShop = new List<object>();
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(1);
lstObjShop.Add(int.MaxValue - 1);
var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList();
//Ds tất cả loại PB
var orgTypeServices = new Cat_OrgStructureTypeServices();
var lstObjOrgType = new List<object>();
lstObjOrgType.Add(string.Empty);
lstObjOrgType.Add(string.Empty);
lstObjOrgType.Add(1);
lstObjOrgType.Add(int.MaxValue - 1);
var listOrgType = orgTypeServices.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList();
//Ds nhân viên
var reposProfile = new CustomBaseRepository<Hre_Profile>(unitOfWork);
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.AddRange(new object[17]);
lstObjProfile[15] = 1;
lstObjProfile[16] = int.MaxValue - 1;
var lstModel = new List<object>();
lstModel.AddRange(new object[10]);
//lstModel[4] = CutOffDuration.DateStart;
//lstModel[5] = CutOffDuration.DateEnd;
lstModel[8] = 1;
lstModel[9] = Int32.MaxValue - 1;
List<Sal_HoldSalaryEntity> listHoldSalary = orgTypeServices.GetData<Sal_HoldSalaryEntity>(lstModel, ConstantSql.hrm_sal_sp_get_HoldSalary, UserLogin, ref status).Where(m => m.MonthSalary <= dateEndCutOffDuration && (m.MonthEndSalary == null || m.MonthEndSalary >= dateStartCutOffDuration)).ToList();
//bỏ những nhân viên đang bị hold lương
listProfile = listProfile.Where(m => !listHoldSalary.Any(t => t.ProfileID == m.ID)).ToList();
bool isGroup = profileServices.IsGroupByOrgProfileQuit();
//lọc theo nhóm lương
List<Guid> listProfileID = listProfile.Select(s => s.ID).Distinct().ToList();
//lọc nhân viên nghỉ việc
if (!isIncludeQuitEmp)
{
listProfile = listProfile.Where(pro => pro.DateQuit == null || pro.DateQuit > from).ToList();
}
if (workingPlaceID != null)
{
listProfile = listProfile.Where(pro => pro.WorkPlaceID != null && workingPlaceID.Contains(pro.WorkPlaceID.Value)).ToList();
}
if (costcenterIds != null)
{
listProfile = listProfile.Where(pro => pro.CostCentreID != null && costcenterIds.Contains(pro.CostCentreID.Value)).ToList();
}
//lọc theo tên nhân viên
if (!string.IsNullOrEmpty(codeEmp))
{
listProfile = listProfile.Where(s => s.CodeEmp != null && s.CodeEmp.Contains(codeEmp)).ToList();
}
//ds chế độ lương
var saleGradeServices = new Sal_GradeServices();
var lstObjSalGrade = new List<object>();
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(1);
lstObjSalGrade.Add(int.MaxValue - 1);
var reposSalGrade = new CustomBaseRepository<Sal_Grade>(unitOfWork);
var lstGradeAll = reposSalGrade.FindBy(s => s.IsDelete != true).ToList();
//var lstGradeAll = GetData<Sal_GradeEntity>(lstObjSalGrade, ConstantSql.hrm_sal_sp_get_Sal_Grade, UserLogin,ref status);
//ds thông tin lương
var salInfoServices = new Sal_SalaryInformationServices();
var lstObjSalInfo = new List<object>();
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(null);
lstObjSalInfo.Add(1);
lstObjSalInfo.Add(int.MaxValue - 1);
var salInfoServices1 = new Sal_SalaryInformationServices();
var lstProfileIDs = new List<Guid>();
var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformationEntity>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation, UserLogin, ref status).ToList();
if (Transfer == EnumDropDown.Transfer.E_TRANSFER.ToString())
{
lstSalaryInformation = lstSalaryInformation.Where(s => s.IsCash == true).ToList();
lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList();
}
if (Transfer == EnumDropDown.Transfer.E_CASH.ToString())
{
lstSalaryInformation = lstSalaryInformation.Where(s => s.IsCash == false).ToList();
lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList();
}
if (Transfer == EnumDropDown.Transfer.E_OTHER.ToString())
{
lstProfileIDs = lstSalaryInformation.Select(s => s.ProfileID).ToList();
}
//ds loại mã lương
var salaryClassServices = new Cat_SalaryClassServices();
var lstObjSalaryClass = new List<object>();
lstObjSalaryClass.Add(null);
lstObjSalaryClass.Add(1);
lstObjSalaryClass.Add(int.MaxValue - 1);
var lstSalaryClass = salaryClassServices.GetData<Cat_SalaryClassEntity>(lstObjSalaryClass, ConstantSql.hrm_cat_sp_get_SalaryClass, UserLogin, ref status).ToList().Translate<Cat_SalaryClass>();
var revenueProfileServices = new Sal_RevenueForProfileServices();
var lstObjRevenueForProfile = new List<object>();
lstObjRevenueForProfile.Add(null);
lstObjRevenueForProfile.Add(1);
lstObjRevenueForProfile.Add(int.MaxValue - 1);
var lstRevenueForProfile = revenueProfileServices.GetData<Sal_RevenueForProfileEntity>(lstObjRevenueForProfile, ConstantSql.hrm_sal_sp_get_RevenueForProfile, UserLogin, ref status).ToList();
//Ds phần tử lương
var elementServices = new Cat_ElementServices();
var methodPayroll = MethodPayroll.E_NORMAL.ToString();
var lstObjElement = new List<object>();
lstObjElement.AddRange(new object[8]);
lstObjElement[6] = 1;
lstObjElement[7] = int.MaxValue - 1;
var reposPayrollElement = new CustomBaseRepository<Cat_Element>(unitOfWork);
var listPayrollElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_ElementByMethod, UserLogin, ref status).Where(s => s.MethodPayroll == methodPayroll).ToList().Translate<Cat_Element>();
#region Chọn lấy back up bảng lương - Tung.Ly 20150513
var salComputePayrollService = new Sal_ComputePayrollServices();
var lstSalPayrollTb = new List<Sal_PayrollTableEntity>();
var lstSalPayrollTbItem = new List<Sal_PayrollTableItemEntity>();
#region param store bang luong
var payrollTableServices = new Sal_PayrollTableServices();
var lstObjPayrollTable = new List<object>();
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(from);
lstObjPayrollTable.Add(to);
lstObjPayrollTable.Add(1);
lstObjPayrollTable.Add(int.MaxValue - 1);
#endregion
#region param store bảng lương chi tiết
var payrollTableItemServices = new Sal_PayrollTableItemServices();
var lstObjPayrollTableItem = new List<object>();
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(from);
lstObjPayrollTableItem.Add(to);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(1);
lstObjPayrollTableItem.Add(int.MaxValue - 1);
#endregion
//kiem ra co du lieu backup không theo kỳ lương
if (salComputePayrollService.CheckDataIsBackUp(TypeDataBKInScheduleTask.E_PAYROLL_BK.ToString(), dateStartCutOffDuration))
{
//Du liệu backup
#region ds bảng lương(backcup)
lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTableBK, UserLogin, ref status)
.Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList();
List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList();
#endregion
#region ds bảng lương chi tiết(backup)
lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItemBK, UserLogin, ref status)
.Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList();
#endregion
}
else
{
#region ds bảng lương
// var reposSalPayrollTb = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork);
lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status)
.Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList();
List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList();
#endregion
#region ds bảng lương chi tiết
// var reposSalPayrollTbItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork);
lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status)
.Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList();
#endregion
}
#endregion
// var lstSalPayrollTbItem = reposSalPayrollTbItem.GetAll().Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList();
//Tạo cột dữ liệu cho table
DataTable tblData = GetSchemaOverallPayroll();
#endregion
#region " Không có dữ liệu Sal_PayrollTable"
if (lstSalPayrollTb == null || lstSalPayrollTb.Count <= 0)
{
foreach (Hre_ProfileEntity profile in listProfile)
{
var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault();
DataRow dr = tblData.NewRow();
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.OrgStructureName] = profile.OrgStructureName;
dr["ShopGroupName"] = lstRankByProfileId != null ? lstRankByProfileId.ShopGroupName : string.Empty;
dr["OrgStructureType"] = profile.E_COMPANY_CODE + "->" + profile.E_BRANCH_CODE + "->" + profile.E_UNIT_CODE + "->" + profile.E_DIVISION_CODE + "->" + profile.E_DEPARTMENT_CODE + "->" + profile.E_TEAM_CODE + "->" + profile.E_SECTION_CODE;
//Sal_BasicSalaryEntity BasicSalaryByProfile = listBasicSalary.SingleOrDefault(m => m.ProfileID == profile.ID);
//dr["SalaryRankName"] = BasicSalaryByProfile != null ? BasicSalaryByProfile.SalaryRankName : "";
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.EmployeeTypeName;
//dr["CostCenterCodePayrollTable"] = profile.CostCentreNamePayrollTable;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.WorkPlaceName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.CostCentreName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.CostCentreCode;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.PositionName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.JobTitleName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email;
if (profile.DateHire != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value;
if (profile.DateQuit != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit;
if (profile.DateEndProbation != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.PayrollGroupName;
dr["CostSource"] = profile.CostSourceName;
if (profile.SalaryClassID != null)
{
Cat_SalaryClass salClass = lstSalaryClass.Where(s => s.ID == profile.SalaryClassID.Value && s.IsDelete == null).FirstOrDefault();
if (salClass != null)
{
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryClassName] = salClass.SalaryClassName;
}
}
if (profile.OrgStructureID != null)
{
var orgName = GetParentOrg(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2];
dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1];
dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0];
}
// Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault();
//if (hrcontract != null && hrcontract.RankRateID != null)
//{
// Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault();
// dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank != null ? salrank.SalaryRankName : string.Empty;
//}
#region Insurance, Bank
var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList();
if (salaryInformationList.Count > 0)
{
Sal_SalaryInformationEntity bankSalary = salaryInformationList[0];
string accountTemp = string.Empty;
string accountNo = string.Empty;
string accountNo2 = string.Empty;
string bankNameTemp = string.Empty;
string bankCode = string.Empty;
accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2;
bankNameTemp = bankSalary.BankName == null ? string.Empty : bankSalary.BankName;
bankCode = bankSalary.BankCode1 == null ? string.Empty : bankSalary.BankCode1;
if (!string.IsNullOrEmpty(accountTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim();
if (!string.IsNullOrEmpty(accountNo))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim();
if (!string.IsNullOrEmpty(accountNo2))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim();
if (!string.IsNullOrEmpty(bankCode))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim();
if (!string.IsNullOrEmpty(bankNameTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim();
}
#endregion
tblData.Rows.Add(dr);
}
}
#endregion
#region " Có dữ liệu tính lương Sal_PayrollTable"
else
{
foreach (Sal_PayrollTableEntity payroll in lstSalPayrollTb)
{
if (Transfer != null)
{
listProfile = listProfile.Where(s => lstProfileIDs.Contains(s.ID)).ToList();
}
Hre_ProfileEntity profile = listProfile.Where(s => s.ID == payroll.ProfileID).FirstOrDefault();
if (profile == null)
continue;
var lstRevenueByProfileId = lstRevenueForProfile.Where(s => s.ProfileID == payroll.ProfileID).FirstOrDefault();
var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault();
//var unusualByProfileId = lstUnusual.Where(s => s.ProfileID == profile.ID).FirstOrDefault();
List<Sal_PayrollTableItemEntity> listItem = lstSalPayrollTbItem.Where(pi => pi.PayrollTableID == payroll.ID).ToList();
DataRow dr = tblData.NewRow();
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ID] = payroll.ID;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.OrgStructureName] = profile.OrgStructureName;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IsPaid] = payroll.IsPaid == null ? false : payroll.IsPaid.Value;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType;
dr["OrgStructureType"] = profile.E_COMPANY_CODE + "->" + profile.E_BRANCH_CODE + "->" + profile.E_UNIT_CODE + "->" + profile.E_DIVISION_CODE + "->" + profile.E_DEPARTMENT_CODE + "->" + profile.E_TEAM_CODE + "->" + profile.E_SECTION_CODE;
dr["ShopGroupName"] = lstRankByProfileId != null ? lstRankByProfileId.ShopGroupName : string.Empty;
//Sal_BasicSalaryEntity BasicSalaryByProfile = listBasicSalary.SingleOrDefault(m => m.ProfileID == profile.ID);
//dr["SalaryRankName"] = BasicSalaryByProfile != null ? BasicSalaryByProfile.SalaryRankName : "";
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.EmployeeTypeName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.WorkPlaceName;
// dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty;
dr["CostCenterCodePayrollTable"] = payroll.CostCentreNamePayrollTable;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.CostCentreName;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.CostCentreCode;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.PositionName;
//dr["PositionCode"] = profile.Cat_Position != null ? profile.Cat_Position.Code : string.Empty;
dr["PositionCode"] = profile.PositionCode;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.JobTitleName;
//dr["JobtitleCode"] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.Code : string.Empty;
dr["JobtitleCode"] = profile.JobTitleCode;
dr["CostSource"] = profile.CostSourceName;
//dr["Rank"] = lstRankByProfileId != null ? lstRankByProfileId.Rank : string.Empty;
//dr["Target"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Target : 0;
//dr["Actual"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Actual : 0;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty;
if (profile.DateHire != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value;
if (profile.DateQuit != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit;
if (profile.DateEndProbation != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value;
//if (unusualByProfileId != null)
//{
// if (unusualByProfileId.MonthStart != null)
// {
// dr["MonthStart"] = unusualByProfileId.MonthStart;
// }
//}
//if (unusualByProfileId != null)
//{
// if (unusualByProfileId.MonthEnd != null)
// {
// dr["MonthEnd"] = unusualByProfileId.MonthEnd;
// }
//}
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.PayrollGroupName;
if (profile.OrgStructureID != null)
{
var orgName = new List<string>();
var orgCode = new List<string>();
if (isGroup)
{
var orgEntity = listOrgAll.Where(s => s.ID == profile.OrgStructureID).FirstOrDefault();
if (orgEntity != null)
{
orgName = GetParentOrgNameForShiseido(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
}
orgCode = GetParentOrgCodeForShiseido(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
}
}
dr["Công Ty"] = orgName[4];
dr["Chi Nhánh"] = orgName[3];
dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2];
dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1];
dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0];
dr["Mã Công Ty"] = orgCode[4];
dr["Mã Chi Nhánh"] = orgCode[3];
dr["Mã Channel"] = orgCode[2];
dr["Mã Region"] = orgCode[1];
dr["Mã Area"] = orgCode[0];
}
//var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType);
//var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType);
//var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType);
//var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType);
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
}
// Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault();
if (profile.SalaryClassID != null)
{
Cat_SalaryClass salClass = lstSalaryClass.Where(s => s.ID == profile.SalaryClassID.Value && s.IsDelete == null).FirstOrDefault();
if (salClass != null)
{
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryClassName] = salClass.SalaryClassName;
}
}
//if (hrcontract != null && hrcontract.RankRateID != null)
//{
// if (profile.SalaryClassID != null)
// {
// Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.SalaryClassID == profile.SalaryClassID.Value && hrcontract.RankRateID == rk.ID && rk.IsDelete == null).FirstOrDefault();
// if (salrank != null)
// dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank.SalaryRankName;
// }
//}
#region Insurance, Bank
var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList();
if (salaryInformationList.Count > 0)
{
Sal_SalaryInformationEntity bankSalary = salaryInformationList[0];
string accountTemp = string.Empty;
string accountNo = string.Empty;
string accountNo2 = string.Empty;
string bankNameTemp = string.Empty;
string bankCode = string.Empty;
accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2;
bankNameTemp = bankSalary.BankName == null ? string.Empty : bankSalary.BankName;
bankCode = bankSalary.BankCode1 == null ? string.Empty : bankSalary.BankCode1;
if (!string.IsNullOrEmpty(accountTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim();
if (!string.IsNullOrEmpty(accountNo))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim();
if (!string.IsNullOrEmpty(accountNo2))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim();
if (!string.IsNullOrEmpty(bankCode))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim();
if (!string.IsNullOrEmpty(bankNameTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim();
}
#endregion
#region sal grade
Sal_Grade grade = new Sal_Grade();
if (gradePayrollID != null)
{
grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID && gradePayrollID.Value == gr.GradePayrollID
&& gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault();
}
else
{
grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID
&& gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault();
}
if (grade != null)
{
Cat_GradePayroll gradepayroll = grade.Cat_GradePayroll;
if (gradepayroll != null && gradepayroll.IsFormulaSalary == true)
{
try
{
var lstSalPrItem = lstSalPayrollTbItem.Where(sal => sal.PayrollTableID == payroll.ID).ToList();
var lstelement = listPayrollElement.Where(pr => pr.GradePayrollID == gradepayroll.ID).ToList();
if (lstelement != null && lstelement.Count > 0)
{
foreach (Cat_Element payrollElement in lstelement)
{
if (payrollElement != null && !String.IsNullOrEmpty(payrollElement.ElementCode))
{
if (payrollElement.Type == "Nvarchar")
{
//Add phần tử vào cột dữ liệu nếu chưa có
if (!tblData.Columns.Contains(payrollElement.ElementCode))
{
tblData.Columns.Add(payrollElement.ElementCode);
}
}
else
{
//Add phần tử vào cột dữ liệu nếu chưa có
if (!tblData.Columns.Contains(payrollElement.ElementCode))
{
tblData.Columns.Add(payrollElement.ElementCode, typeof(Double));
}
}
//Lấy value của phần tử
if (tblData.Columns.Contains(payrollElement.ElementCode))
{
Sal_PayrollTableItemEntity item = lstSalPrItem.Where(it => it.Code == payrollElement.ElementCode).FirstOrDefault();
Double value = 0;
if (item != null)
{
if (item.ValueType == typeof(Double).Name)
{
Double.TryParse(item.Value, out value);
dr[payrollElement.ElementCode] = value;
}
if (item.ValueType == EnumDropDown.DataType.Nvarchar.ToString())
{
dr[payrollElement.ElementCode] = item.Value;
}
}
}
}
}
}
}
catch { }
}
tblData.Rows.Add(dr);
}
#endregion
}
}
#endregion
var configs = new Dictionary<string, Dictionary<string, object>>();
var config = new Dictionary<string, object>();
config.Add("hidden", true);
configs.Add("ID", config);
return tblData.ConfigTable(configs, true);
}
}
catch (Exception ex)
{
return new DataTable();
}
}
public void NUnit_Contract_Domain_Edit()
{
Cat_OrgStructureServices service = new Cat_OrgStructureServices();
var model = new Cat_OrgStructure
{
Id = 5,
OrgStructureName = "Update Phòng Ban ",
Code = "Update Mã ",
IsRoot = true,
Description = "Update Mô Tả ",
TypeID = 1,
ParentID = 1,
OrderNumber = 321
};
bool result = service.UpdateCatOrgStructure(model);
NUnit.Framework.Assert.IsTrue(result);
if (result == true)
{
Console.WriteLine("Process Success >>> Update >>> " + model.Id
+ " | " + model.OrgStructureName
+ " | " + model.Code
+ " | " + model.IsRoot
+ " | " + model.Description
+ " | " + model.TypeID
+ " | " + model.ParentID
+ " | " + model.OrderNumber
);
}
}
public void NUnit_Contract_Domain_Get()
{
Cat_OrgStructureServices service = new Cat_OrgStructureServices();
IQueryable<Cat_OrgStructure> repo = service.GetCatOrgStructure();
Console.Write("Total Record: " + repo.Count());
}
public void NUnit_Contract_Domain_Remove()
{
Cat_OrgStructureServices service = new Cat_OrgStructureServices();
int rs = 0;
var model = new Cat_OrgStructure { Id = 3 };
bool result = service.DeleteCatOrgStructure(model.Id);
NUnit.Framework.Assert.IsTrue(result);
if (result == true)
{
rs += 1;
Console.WriteLine("Process Success >>> Remove >>> " + model.Id);
}
}
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 ActionResult ExportProfileListByTemplate([DataSourceRequest] DataSourceRequest request, Hre_ProfileActiveSearchModel model)
{
//if(model.ExportId == Guid.Empty)
//{
// return null;
//}
var actionService = new ActionService(UserLogin);
string status = string.Empty;
var profileServices = new Hre_ProfileServices();
bool isGroup = profileServices.IsGroupByOrgProfileQuit();
var isDataTable = false;
object obj = new Hre_ProfileModel();
var result = GetListData<Hre_ProfileModel, Hre_ProfileEntity, Hre_ProfileActiveSearchModel>(request, model, ConstantSql.hrm_hr_sp_get_ProfileActive, ref status);
if (isGroup == true)
{
var rptServices = new Hre_ReportServices();
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = actionService.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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 = actionService.GetData<Cat_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, ref status).ToList();
result = GetListData<Hre_ProfileModel, Hre_ProfileEntity, Hre_ProfileActiveSearchModel>(request, model, ConstantSql.hrm_hr_sp_get_ProfileActive, ref status);
DataTable table = new DataTable("Hre_ProfileModel");
table.Columns.Add("CodeEmp");
table.Columns.Add("ProfileName");
table.Columns.Add("FirstName");
table.Columns.Add("NameFamily");
table.Columns.Add("Channel");
table.Columns.Add("Region");
table.Columns.Add("Area");
table.Columns.Add("IDNo");
table.Columns.Add("IDDateOfIssue", typeof(DateTime));
table.Columns.Add("IDPlaceOfIssue");
table.Columns.Add("DateOfBirth", typeof(DateTime));
table.Columns.Add("PlaceOfBirth");
table.Columns.Add("Gender");
table.Columns.Add("MarriageStatus");
table.Columns.Add("NationalityName");
table.Columns.Add("ReligionName");
table.Columns.Add("EthnicGroupName");
table.Columns.Add("Email");
table.Columns.Add("CellPhone");
table.Columns.Add("CodeTax");
table.Columns.Add("TDistrictName");
table.Columns.Add("PDistrictName");
table.Columns.Add("JobTitleName");
table.Columns.Add("DateHire", typeof(DateTime));
table.Columns.Add("ContractNo");
table.Columns.Add("DateStartContract");
table.Columns.Add("DateEndContract");
table.Columns.Add("ContractTypeName");
table.Columns.Add("TimesOfContract");
table.Columns.Add("Notes");
table.Columns.Add("TAddress");
table.Columns.Add("PAddress");
table.Columns.Add("BasicSalary", typeof(double));
var salaryServices = new Sal_BasicSalaryServices();
var objSalary = new List<object>();
objSalary.AddRange(new object[10]);
objSalary[8] = 1;
objSalary[9] = int.MaxValue - 1;
var lstBasicSalary = actionService.GetData<Sal_BasicSalaryEntity>(objSalary, ConstantSql.hrm_sal_sp_get_BasicPayroll, ref status).ToList();
var unuServices = new Sal_UnusualAllowanceServices();
var objUnu = new List<object>();
objUnu.AddRange(new object[9]);
objUnu[7] = 1;
objUnu[8] = int.MaxValue - 1;
var lstUnu = actionService.GetData<Sal_UnusualAllowanceEntity>(objUnu, ConstantSql.hrm_sal_sp_get_UnusualED, ref status).ToList();
foreach (var item in result)
{
var basicSalaryEntity = lstBasicSalary.Where(s => s.ProfileID == item.ID).OrderByDescending(s => s.DateUpdate).FirstOrDefault();
var lstUnuByProfileID = lstUnu.Where(s => s.ProfileID == item.ID).ToList();
var orgName = new List<string>();
if (item.OrgStructureID != null)
{
orgName = rptServices.GetParentOrgName(lstOrg, lstOrgType, item.OrgStructureID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
}
var lstObjPayroll = new List<object>();
lstObjPayroll.Add(item.ID);
lstObjPayroll.Add(null);
lstObjPayroll.Add(null);
lstObjPayroll.Add(null);
lstObjPayroll.Add(1);
lstObjPayroll.Add(int.MaxValue - 1);
var payrollTableByProfileID = actionService.GetData<Sal_PayrollTableItemEntity>(lstObjPayroll, ConstantSql.hrm_sal_sp_get_PayrollTableItemByProfile, ref status).OrderByDescending(s => s.DateCreate);
var lstObjContract = new List<object>();
lstObjContract.Add(item.ID);
var lstContract = actionService.GetData<Hre_ContractEntity>(lstObjContract, ConstantSql.hrm_hr_sp_get_ContractsByProfileId, ref status).OrderBy(s => s.DateCreate).ToList();
var lstObjRelative = new List<object>();
lstObjRelative.Add(item.ID);
lstObjRelative.Add(1);
lstObjRelative.Add(Int32.MaxValue - 1);
var lstDependant = actionService.GetData<Hre_DependantEntity>(lstObjRelative, ConstantSql.hrm_hr_sp_get_DependantByProfileId, ref status).ToList();
DataRow dr = table.NewRow();
dr["CodeEmp"] = item.CodeEmp;
dr["ProfileName"] = item.ProfileName;
dr["FirstName"] = item.FirstName;
dr["NameFamily"] = item.NameFamily;
if (orgName.Count > 0)
{
dr["Channel"] = orgName[2];
dr["Region"] = orgName[1];
dr["Area"] = orgName[0];
}
dr["IDNo"] = item.IDNo;
if (item.IDDateOfIssue != null)
{
dr["IDDateOfIssue"] = item.IDDateOfIssue.Value;
}
dr["IDPlaceOfIssue"] = item.IDPlaceOfIssue;
if (item.DateOfBirth != null)
{
dr["DateOfBirth"] = item.DateOfBirth;
}
dr["Gender"] = item.Gender;
if (item.Gender == EnumDropDown.Gender.E_FEMALE.ToString())
{
dr["Gender"] = "Nữ";
}
if (item.Gender == EnumDropDown.Gender.E_MALE.ToString())
{
dr["Gender"] = "Nam";
}
dr["MarriageStatus"] = item.MarriageStatus;
dr["NationalityName"] = item.NationalityName;
dr["ReligionName"] = item.ReligionName;
dr["EthnicGroupName"] = item.EthnicGroupName;
dr["Email"] = item.Email;
dr["CellPhone"] = item.Cellphone;
dr["CodeTax"] = item.CodeTax;
dr["TDistrictName"] = item.TDistrictName;
dr["PDistrictName"] = item.PDistrictName;
dr["JobTitleName"] = item.JobTitleName;
dr["Notes"] = item.Notes;
dr["TAddress"] = item.TAddress;
dr["PAddress"] = item.PAddress;
if (basicSalaryEntity != null)
{
dr["BasicSalary"] = double.Parse(basicSalaryEntity.GrossAmount);
}
if (item.DateHire != null)
{
dr["DateHire"] = item.DateHire.Value;
}
//if (payrollTableByProfileID != null)
//{
// foreach (var payroll in payrollTableByProfileID)
// {
// Double value = 0;
// if (!table.Columns.Contains(payroll.Code))
// {
// table.Columns.Add(payroll.Code, typeof(Double));
// }
// if (table.Columns.Contains(payroll.Code))
// {
// if (payroll.ValueType == typeof(Double).Name)
// {
// Double.TryParse(payroll.Value, out value);
// }
// dr[payroll.Code] = value;
// }
// }
//}
if (lstContract != null && lstContract.Count > 0)
{
var contractEntity = lstContract.FirstOrDefault();
dr["TimesOfContract"] = lstContract.Count;
dr["ContractNo"] = contractEntity.ContractNo;
dr["ContractTypeName"] = contractEntity.ContractTypeName;
dr["DateStartContract"] = contractEntity.DateStart;
if (contractEntity.DateEnd != null)
{
dr["DateStartContract"] = contractEntity.DateEnd.Value;
}
}
if (lstUnuByProfileID.Count > 0 && lstUnuByProfileID != null)
{
foreach (var unu in lstUnuByProfileID)
{
var titleName = unu.UnusualEDTypeCode + "|" + unu.UnusualAllowanceCfgName;
if (!table.Columns.Contains(titleName))
{
table.Columns.Add(titleName);
}
if (table.Columns.Contains(titleName))
{
dr[titleName] = unu.Amount;
}
}
}
if (lstDependant.Count > 0 && lstDependant != null)
{
foreach (var dependant in lstDependant)
{
var titleName = dependant.DependantName + "|" + dependant.RelativeTypeName;
if (!table.Columns.Contains(titleName))
{
table.Columns.Add(titleName);
}
if (table.Columns.Contains(titleName))
{
dr[titleName] = dependant.DependantName;
}
}
}
table.Rows.Add(dr);
}
if (model.IsCreateTemplateForDynamicGrid)
{
obj = result;
isDataTable = false;
}
if (model != null && model.IsCreateTemplate)
{
var path = Common.GetPath("Templates");
ExportService exportService = new ExportService();
ConfigExport cfgExport = new ConfigExport()
{
Object = table,
FileName = "Hre_ProfileModel",
OutPutPath = path,
// HeaderInfo = listHeaderInfo,
DownloadPath = Hrm_Main_Web + "Templates",
IsDataTable = true
};
var str = exportService.CreateTemplate(cfgExport);
return Json(str);
}
if (model.ExportId != Guid.Empty)
{
var fullPath = ExportService.Export(model.ExportId, table, null, model.ExportType);
return Json(fullPath);
}
return Json(result.ToDataSourceResult(request));
}
if (model.IsCreateTemplateForDynamicGrid)
{
obj = result;
isDataTable = false;
}
if (model != null && model.IsCreateTemplate)
{
var path = Common.GetPath("Templates");
ExportService exportService = new ExportService();
ConfigExport cfgExport = new ConfigExport()
{
Object = obj,
FileName = "Hre_ProfileModel",
OutPutPath = path,
// HeaderInfo = listHeaderInfo,
DownloadPath = Hrm_Main_Web + "Templates",
IsDataTable = isDataTable
};
var str = exportService.CreateTemplate(cfgExport);
return Json(str);
}
if (model.ExportId != Guid.Empty)
{
var fullPath = ExportService.Export(model.ExportId, result, null, model.ExportType);
return Json(fullPath);
}
return Json(result.ToDataSourceResult(request));
}
public ActionResult GetReportPrenancy([DataSourceRequest] DataSourceRequest request, Hre_ReportPregnancyModel Model)
{
#region Validate
string message = string.Empty;
var checkValidate = ValidatorService.OnValidateData<Hre_ReportPregnancyModel>(Model, "Hre_ReportPregnancy", ref message);
if (!checkValidate)
{
return Json(message);
}
#endregion
var service = new Hre_ReportServices();
var actionServices = new ActionService(UserLogin);
string status = string.Empty;
DateTime From = SqlDateTime.MinValue.Value;
DateTime To = SqlDateTime.MaxValue.Value;
if (Model.DateStart != null)
{
From = Model.DateStart.Value;
}
if (Model.DateEnd != null)
{
To = Model.DateEnd.Value;
}
var isDataTable = false;
object obj = new Hre_ReportPregnancyModel();
List<object> listObj = new List<object>();
listObj.Add(Model.DateStart);
listObj.Add(Model.DateEnd);
listObj.Add(Model.OrgStructureID);
listObj.Add(Model.ProfileName);
listObj.Add(Model.CodeEmp);
var result = actionServices.GetData<Hre_ReportPregnancyEntity>(listObj, ConstantSql.hrm_hr_sp_get_RptPrenancy, ref status).ToList();
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = actionServices.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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 = actionServices.GetData<Cat_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, ref status).ToList();
var lstRptPrenancyEntity = new List<Hre_ReportPregnancyEntity>();
foreach (var item in result)
{
var pregnancyEntity = new Hre_ReportPregnancyEntity();
var orgName = service.GetParentOrgName(lstOrg, lstOrgType, item.OrgID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
pregnancyEntity = item.CopyData<Hre_ReportPregnancyEntity>();
pregnancyEntity.Channel = orgName[2];
pregnancyEntity.Region = orgName[1];
pregnancyEntity.Area = orgName[0];
lstRptPrenancyEntity.Add(pregnancyEntity);
}
var lstRptPrenancyModel = lstRptPrenancyEntity.Translate<Hre_ReportPregnancyModel>();
if (Model.IsCreateTemplateForDynamicGrid)
{
obj = lstRptPrenancyModel;
isDataTable = true;
}
if (Model != null && Model.IsCreateTemplate)
{
var path = Common.GetPath("Templates");
ExportService exportService = new ExportService();
ConfigExport cfgExport = new ConfigExport()
{
Object = obj,
FileName = "Hre_ReportPregnancyModel",
OutPutPath = path,
DownloadPath = Hrm_Main_Web + "Templates",
IsDataTable = isDataTable
};
var str = exportService.CreateTemplate(cfgExport);
return Json(str);
}
if (Model.ExportID != Guid.Empty)
{
string[] valueField = null;
if (Model.ValueFields != null)
{
valueField = Model.ValueFields.Split(',');
}
var fullPath = ExportService.Export(Model.ExportID, lstRptPrenancyModel, null, Model.ExportType);
return Json(fullPath);
}
return Json(lstRptPrenancyModel.ToDataSourceResult(request));
}
public DataTable ComparePayroll(Guid[] _cutOffDurationIDs, Guid[] _elementIDs, string OrgOrderNumber, Guid? OrgTypeID, bool isCreateTemplate, string CompareType, string ShowDataType, Guid[] WorkingPlace, string UserLogin)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
DataTable table = GetSchemaComparePayroll(_cutOffDurationIDs, _elementIDs, CompareType, OrgOrderNumber, OrgTypeID, ShowDataType, UserLogin);
var E_ORGSTRUCTURE = EnumDropDown.CompareType.E_ORGSTRUCTURE.ToString();
var E_PROFILE = EnumDropDown.CompareType.E_PROFILE.ToString();
var column = EnumDropDown.ShowDataType.E_COLUMN.ToString();
var row = EnumDropDown.ShowDataType.E_ROW.ToString();
if (isCreateTemplate)
{
return table.ConfigTable();
}
#region Group Theo Nhân Viên
if (CompareType == E_PROFILE || string.IsNullOrEmpty(CompareType))
{
// Xử Lý Group Phòng Ban
var orgsService = new Cat_OrgStructureServices();
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(OrgOrderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber }).ToList();
if (OrgTypeID != null)
{
lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList();
}
var strOrderNumer = string.Empty;
var OrderNumber = string.Empty;
OrderNumber = null;
foreach (var OrgOrderNumer in lstOrgByOrderNumber)
{
strOrderNumer += OrgOrderNumer.OrderNumber + ",";
}
if (!string.IsNullOrEmpty(strOrderNumer))
{
OrderNumber = strOrderNumer.Substring(0, strOrderNumer.Length - 1);
}
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.Add(OrderNumber);
lstObjProfile.Add(null);
lstObjProfile.Add(null);
var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
if (WorkingPlace != null)
{
listProfile = listProfile.Where(m => m.WorkPlaceID != null && WorkingPlace.Contains((Guid)m.WorkPlaceID)).ToList();
}
var attCutOffDurationServices = new Att_CutOffDurationServices();
var lstObjCutOff = new List<object>();
lstObjCutOff.Add(null);
lstObjCutOff.Add(1);
lstObjCutOff.Add(int.MaxValue - 1);
var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList();
lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList();
var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList();
var elementServices = new Cat_ElementServices();
var lstObjElement = new List<object>();
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(1);
lstObjElement.Add(int.MaxValue - 1);
var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList();
lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList();
var payrollTableServices = new Sal_PayrollTableServices();
var lstObjPayrollTable = new List<object>();
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(1);
lstObjPayrollTable.Add(int.MaxValue - 1);
var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList();
var payrollTableItemServices = new Sal_PayrollTableItemServices();
var lstObjPayrollTableItem = new List<object>();
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(1);
lstObjPayrollTableItem.Add(int.MaxValue - 1);
var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList();
foreach (var profile in listProfile)
{
DataRow dr = table.NewRow();
dr[Sal_ComparePayrollEntity.FieldNames.CodeEmp] = profile != null ? profile.CodeEmp : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.ProfileName] = profile != null ? profile.ProfileName : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = profile != null ? profile.OrgStructureName : string.Empty;
foreach (var element in lstElement)
{
foreach (var cutOff in lstCutOffDuration)
{
var lstPayrollTableByProfileID = lstPayrollTable.Where(s => s.ProfileID == profile.ID && cutOff.ID == s.CutOffDurationID.Value).ToList();
foreach (var payrollTable in lstPayrollTableByProfileID)
{
var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID).ToList();
foreach (var payrollTableItem in payrollTableItemData)
{
var elementData = payrollTableItemData.Where(s => element.ElementCode == s.Code).FirstOrDefault();
double value = 0;
var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString();
if (table.Columns.Contains(elementName))
{
if (elementData != null)
{
if (elementData.ValueType == typeof(Double).Name)
{
Double.TryParse(elementData.Value, out value);
}
}
dr[elementName] = value;
}
}
}
}
}
table.Rows.Add(dr);
}
}
#endregion
#region Group Theo Phòng Ban
if (CompareType == E_ORGSTRUCTURE)
{
#region Show dữ liệu theo dạng cột
if (ShowDataType == column)
{
// Xử Lý Group Phòng Ban
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList();
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(OrgOrderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber, s.OrgStructureName, s.Code }).ToList();
if (OrgTypeID != null)
{
lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList();
}
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.AddRange(new object[18]);
lstObjProfile[16] = 1;
lstObjProfile[17] = int.MaxValue - 1;
var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList();
if (WorkingPlace != null)
{
listProfile = listProfile.Where(m => m.WorkPlaceID != null && WorkingPlace.Contains((Guid)m.WorkPlaceID)).ToList();
}
var attCutOffDurationServices = new Att_CutOffDurationServices();
var lstObjCutOff = new List<object>();
lstObjCutOff.Add(null);
lstObjCutOff.Add(1);
lstObjCutOff.Add(int.MaxValue - 1);
var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList();
lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList();
var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList();
var elementServices = new Cat_ElementServices();
var lstObjElement = new List<object>();
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(1);
lstObjElement.Add(int.MaxValue - 1);
var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList();
lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList();
var payrollTableServices = new Sal_PayrollTableServices();
var lstObjPayrollTable = new List<object>();
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(1);
lstObjPayrollTable.Add(int.MaxValue - 1);
var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList();
var payrollTableItemServices = new Sal_PayrollTableItemServices();
var lstObjPayrollTableItem = new List<object>();
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(1);
lstObjPayrollTableItem.Add(int.MaxValue - 1);
var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList();
foreach (var org in lstOrgByOrderNumber)
{
DataRow dr = table.NewRow();
double total = 0;
//xử lý đếm nhân viên của phòng ban con
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
// orderNumber += "," + org.OrderNumber.ToString();
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjProfileByOrgOrderNumer = new List<object>();
lstObjProfileByOrgOrderNumer.Add(orderNumber);
lstObjProfileByOrgOrderNumer.Add(null);
lstObjProfileByOrgOrderNumer.Add(null);
var listProfileByOrderNumber = profileServices.GetData<Hre_ProfileEntity>(lstObjProfileByOrgOrderNumer, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).Select(s => s.ID).ToList();
dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureCode] = org != null ? org.Code : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = org != null ? org.OrgStructureName : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile] = listProfile.Where(m => m.OrgStructureID == org.ID).Count();
var lstProfileByOrgID = listProfile.Where(s => listProfileByOrderNumber.Contains(s.ID)).ToList();
foreach (var profile in lstProfileByOrgID)
{
foreach (var element in lstElement)
{
foreach (var cutOff in lstCutOffDuration)
{
var lstPayrollTableByProfileID = lstPayrollTable.Where(s => s.ProfileID == profile.ID && cutOff.ID == s.CutOffDurationID.Value).ToList();
foreach (var payrollTable in lstPayrollTableByProfileID)
{
var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID).ToList();
foreach (var payrollTableItem in payrollTableItemData)
{
var elementData = payrollTableItemData.Where(s => element.ElementCode == s.Code).FirstOrDefault();
double value = 0;
var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString();
if (table.Columns.Contains(elementName))
{
if (elementData != null)
{
if (elementData.ValueType == typeof(Double).Name)
{
Double.TryParse(elementData.Value, out value);
}
}
total += value;
dr[elementName] = total;
}
}
}
}
}
}
table.Rows.Add(dr);
}
}
#endregion
#region Show dữ liệu theo dạng Dòng
if (ShowDataType == row)
{
// Xử Lý Group Phòng Ban
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList();
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(OrgOrderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber, s.OrgStructureName, s.Code }).ToList();
if (OrgTypeID != null)
{
lstOrgByOrderNumber = lstOrgByOrderNumber.Where(s => s.OrgStructureTypeID.Value == OrgTypeID.Value).ToList();
}
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.AddRange(new object[18]);
lstObjProfile[16] = 1;
lstObjProfile[17] = int.MaxValue - 1;
var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList();
var attCutOffDurationServices = new Att_CutOffDurationServices();
var lstObjCutOff = new List<object>();
lstObjCutOff.Add(null);
lstObjCutOff.Add(1);
lstObjCutOff.Add(int.MaxValue - 1);
var lstCutOffDuration = attCutOffDurationServices.GetData<Att_CutOffDurationEntity>(lstObjCutOff, ConstantSql.hrm_att_sp_get_CutOffDurations, UserLogin, ref status).ToList();
lstCutOffDuration = lstCutOffDuration.Where(s => _cutOffDurationIDs.Contains(s.ID)).ToList();
var lstCutOffDurationID = lstCutOffDuration.Select(s => s.ID).ToList();
var elementServices = new Cat_ElementServices();
var lstObjElement = new List<object>();
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(1);
lstObjElement.Add(int.MaxValue - 1);
var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList();
lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList();
var payrollTableServices = new Sal_PayrollTableServices();
var lstObjPayrollTable = new List<object>();
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(1);
lstObjPayrollTable.Add(int.MaxValue - 1);
var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList();
var payrollTableItemServices = new Sal_PayrollTableItemServices();
var lstObjPayrollTableItem = new List<object>();
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(1);
lstObjPayrollTableItem.Add(int.MaxValue - 1);
var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList();
foreach (var org in lstOrgByOrderNumber)
{
DataRow dr = table.NewRow();
//xử lý đếm nhân viên của phòng ban con
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
// orderNumber += "," + org.OrderNumber.ToString();
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjProfileByOrgOrderNumer = new List<object>();
lstObjProfileByOrgOrderNumer.Add(orderNumber);
lstObjProfileByOrgOrderNumer.Add(null);
lstObjProfileByOrgOrderNumer.Add(null);
var listProfileByOrderNumber = profileServices.GetData<Hre_ProfileEntity>(lstObjProfileByOrgOrderNumer, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).Select(s => s.ID).ToList();
dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureCode] = org != null ? org.Code : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.OrgStructureName] = org != null ? org.OrgStructureName : string.Empty;
dr[Sal_ComparePayrollEntity.FieldNames.GroupNumberProfile] = listProfile.Where(m => m.OrgStructureID == org.ID).Count();
table.Rows.Add(dr);
var lstProfileByOrgID = listProfile.Where(s => listProfileByOrderNumber.Contains(s.ID)).ToList();
var lstProfileID = lstProfileByOrgID.Select(s => s.ID).ToList();
foreach (var element in lstElement)
{
DataRow dr1 = table.NewRow();
double total = 0;
dr1[Sal_ComparePayrollEntity.FieldNames.ElementName] = element.ElementCode;
foreach (var cutOff in lstCutOffDuration)
{
var lstPayrollTableByProfileID = lstPayrollTable.Where(s => lstProfileID.Contains(s.ProfileID) && cutOff.ID == s.CutOffDurationID.Value).ToList();
foreach (var payrollTable in lstPayrollTableByProfileID)
{
var payrollTableItemData = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID && element.ElementCode == s.Code).ToList();
foreach (var payrollTableItem in payrollTableItemData)
{
double value = 0;
var elementName = element.ElementName + "_" + (lstCutOffDuration.IndexOf(cutOff) + 1).ToString();
if (table.Columns.Contains(cutOff.CutOffDurationName))
{
if (payrollTableItem.ValueType == typeof(Double).Name)
{
Double.TryParse(payrollTableItem.Value, out value);
}
total += value;
dr1[cutOff.CutOffDurationName] = total;
}
}
}
}
if (total > 0)
{
table.Rows.Add(dr1);
}
}
}
}
#endregion
}
#endregion
//Dictionary<string, Dictionary<string, object>> configs = new Dictionary<string, Dictionary<string, object>>();
//Dictionary<string, object> config = new Dictionary<string, object>();
//config.Add("locked", true);
//configs.Add("CodeEmp", config);
//configs.Add("ProfileName", config);
//configs.Add("OrgStructureCode", config);
//configs.Add("OrgStructureName", config);
return table.ConfigTable(true);
}
}
public ActionResult GetProfileQuitList([DataSourceRequest] DataSourceRequest request, Hre_ProfileQuitSearchModel model)
{
var profileServices = new Hre_ProfileServices();
var service = new ActionService(UserLogin);
bool isGroup = profileServices.IsGroupByOrgProfileQuit();
if (isGroup == true)
{
#region Group theo DS phòng ban - DDF
var rptServices = new Hre_ReportServices();
string status = string.Empty;
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = service.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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 = service.GetData<Cat_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, ref status).ToList();
ListQueryModel lstModel = new ListQueryModel
{
PageIndex = request.Page,
PageSize = request.PageSize,
Filters = ExtractFilterAttributes(request),
Sorts = ExtractSortAttributes(request),
AdvanceFilters = ExtractAdvanceFilterAttributes(model)
};
var lstEntity = service.GetData<Hre_ProfileEntity>(lstModel, ConstantSql.hrm_hr_sp_get_ProfileQuit, ref status);
var lstProfileEntity = new List<Hre_ProfileEntity>();
var lstProfileModel = new List<Hre_ProfileModel>();
if (lstEntity != null)
{
request.Page = 1;
foreach (var item in lstEntity)
{
var profileEntity = new Hre_ProfileEntity();
var orgName = new List<string>();
if (item.OrgStructureID != null)
{
orgName = rptServices.GetParentOrgName(lstOrg, lstOrgType, item.OrgStructureID);
}
// orgName = rptServices.GetParentOrgName(lstOrg, lstOrgType, item.OrgStructureID);
if (orgName.Count == 0)
continue;
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
profileEntity = item.CopyData<Hre_ProfileEntity>();
profileEntity.Channel = orgName[2];
profileEntity.Region = orgName[1];
profileEntity.Area = orgName[0];
lstProfileEntity.Add(profileEntity);
}
lstProfileModel = lstProfileEntity.Translate<Hre_ProfileModel>();
var dataSourceResult = lstProfileModel.ToDataSourceResult(request);
if (lstProfileModel.FirstOrDefault().GetPropertyValue("TotalRow") != null)
{
dataSourceResult.Total = lstProfileModel.Count() <= 0 ? 0 : (int)lstProfileModel.FirstOrDefault().GetPropertyValue("TotalRow");
}
return Json(dataSourceResult, JsonRequestBehavior.AllowGet);
}
#endregion
}
return GetListDataAndReturn<Hre_ProfileModel, Hre_ProfileEntity, Hre_ProfileQuitSearchModel>(request, model, ConstantSql.hrm_hr_sp_get_ProfileQuit);
}
public DataTable GetReportCostCentreByOrg(Guid[] _cutOffDurationIDs, Guid[] _elementIDs, string OrgOrderNumber, string UserLogin, bool isCreateTemplate)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
var baservice = new BaseService();
DataTable table = GetSchemaRptCostCentreByOrg(_elementIDs, UserLogin);
if (isCreateTemplate)
{
return table.ConfigTable();
}
// Xử Lý Group Phòng Ban
var orgsService = new Cat_OrgStructureServices();
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(OrgOrderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructureEntity>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => new { s.ID, s.OrgStructureTypeID, s.OrderNumber, s.OrgStructureName, s.GroupCostCentreID, s.Code }).ToList();
var strOrderNumer = string.Empty;
var OrderNumber = string.Empty;
OrderNumber = null;
foreach (var OrgOrderNumer in lstOrgByOrderNumber)
{
strOrderNumer += OrgOrderNumer.OrderNumber + ",";
}
if (!string.IsNullOrEmpty(strOrderNumer))
{
OrderNumber = strOrderNumer.Substring(0, strOrderNumer.Length - 1);
}
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.Add(OrderNumber);
lstObjProfile.Add(null);
lstObjProfile.Add(null);
var listProfile = profileServices.GetData<Hre_ProfileEntity>(lstObjProfile, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
var lstProfileIDs = listProfile.Select(s => s.ID).ToList();
List<object> lstObjCostCentreSal = new List<object>();
lstObjCostCentreSal.AddRange(new object[4]);
lstObjCostCentreSal[0] = null;
lstObjCostCentreSal[1] = null;
lstObjCostCentreSal[2] = 1;
lstObjCostCentreSal[3] = int.MaxValue - 1;
var lstSal_CostCentreSal = baservice.GetData<Sal_CostCentreSalEntity>(lstObjCostCentreSal, ConstantSql.hrm_sal_sp_getdata_CostCentreSal, UserLogin, ref status).ToList();
var lstObjCostCentre = new List<object>();
lstObjCostCentre.AddRange(new object[4]);
lstObjCostCentre[0] = null;
lstObjCostCentre[1] = null;
lstObjCostCentre[2] = 1;
lstObjCostCentre[3] = int.MaxValue - 1;
var lstCostCentre = baservice.GetData<Cat_CostCentreEntity>(lstObjCostCentreSal, ConstantSql.hrm_cat_sp_get_CostCentre, UserLogin, ref status).ToList();
var lstCostCentreIDs = lstCostCentre.Select(s => s.ID).ToList();
var payrollTableServices = new Sal_PayrollTableServices();
var lstObjPayrollTable = new List<object>();
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(null);
lstObjPayrollTable.Add(1);
lstObjPayrollTable.Add(int.MaxValue - 1);
var lstPayrollTable = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin, ref status).ToList();
var payrollTableItemServices = new Sal_PayrollTableItemServices();
var lstObjPayrollTableItem = new List<object>();
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(null);
lstObjPayrollTableItem.Add(1);
lstObjPayrollTableItem.Add(int.MaxValue - 1);
var lstPayrollTableItem = payrollTableItemServices.GetData<Sal_PayrollTableItemEntity>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin, ref status).ToList();
var elementServices = new Cat_ElementServices();
var lstObjElement = new List<object>();
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(null);
lstObjElement.Add(1);
lstObjElement.Add(int.MaxValue - 1);
var lstElement = elementServices.GetData<Cat_ElementEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element, UserLogin, ref status).ToList();
lstElement = lstElement.Where(s => _elementIDs.Contains(s.ID)).ToList();
foreach (var cost in lstCostCentre)
{
// var lstOrgByCostcentre = lstOrgByOrderNumber.Where(s => s.GroupCostCentreID.Value == cost.ID).ToList();
foreach (var org in lstOrgByOrderNumber)
{
DataRow dr = table.NewRow();
double? sumProfile = 0;
var lstProfileByOrg = listProfile.Where(s => s.OrgStructureID.Value == org.ID).ToList();
dr[Sal_ReportCostCentreByOrgEntity.FieldNames.CostCentreCode] = cost == null ? string.Empty : cost.Code;
dr[Sal_ReportCostCentreByOrgEntity.FieldNames.OrgStructureCode] = org == null ? string.Empty : org.Code;
dr[Sal_ReportCostCentreByOrgEntity.FieldNames.OrgStructureName] = org == null ? string.Empty : org.OrgStructureName;
foreach (var profile in lstProfileByOrg)
{
var lstPayrollTableByProfileID = lstPayrollTable.Where(s => profile.ID == s.ProfileID && s.CutOffDurationID.Value == _cutOffDurationIDs[0]).ToList();
foreach (var payrollTable in lstPayrollTableByProfileID)
{
var lstPayrollTableItemByTableID = lstPayrollTableItem.Where(s => s.PayrollTableID == payrollTable.ID).ToList();
foreach (var item in lstPayrollTableItemByTableID)
{
var costcentreByProfile = lstSal_CostCentreSal.Where(s => s.CostCentreID.Value == cost.ID && payrollTable.ProfileID == s.ProfileID.Value && item.Code == s.ElementType).ToList();
foreach (var costcentre in costcentreByProfile)
{
foreach (var element in lstElement)
{
Double value = 0;
if (costcentre.ElementType == element.ElementCode)
{
if (table.Columns.Contains(element.ElementName))
{
Double.TryParse(item.Value, out value);
sumProfile += value * costcentre.Rate;
dr[element.ElementName] = sumProfile;
}
}
}
}
}
}
}
table.Rows.Add(dr);
}
}
return table.ConfigTable(true);
}
}
public DataTable GetReportRevenueForShop(DateTime dateFrom, DateTime dateTo, string orderNumber, string UserLogin, bool isCreateTemplate)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
DataTable table = GetSchemaReportVenueForShop(UserLogin);
if (isCreateTemplate)
{
return table.ConfigTable();
}
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(orderNumber);
var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList();
var shopServices = new Cat_ShopServices();
var lstObjShop = new List<object>();
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(1);
lstObjShop.Add(int.MaxValue - 1);
var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList();
var lstShopID = new List<Guid>();
if (lstOrgID != null)
{
lstShop = lstShop.Where(s => lstOrgID.Contains(s.OrgStructureID.Value)).ToList();
}
var kpiBonusService = new Cat_KPIBonusServices();
var lstObjKPIBonus = new List<object>();
lstObjKPIBonus.Add(null);
lstObjKPIBonus.Add(null);
lstObjKPIBonus.Add(1);
lstObjKPIBonus.Add(int.MaxValue - 1);
var lstKPIBonus = kpiBonusService.GetData<Cat_KPIBonusEntity>(lstObjKPIBonus, ConstantSql.hrm_cat_sp_get_KPIBonus, UserLogin, ref status).ToList();
var kpiBonusItemServices = new Cat_KPIBonusItemServices();
var lstObjKPIBonusItem = new List<object>();
lstObjKPIBonusItem.Add(null);
lstObjKPIBonusItem.Add(null);
lstObjKPIBonusItem.Add(1);
lstObjKPIBonusItem.Add(int.MaxValue - 1);
var lstKPIBonusItem = kpiBonusItemServices.GetData<Cat_KPIBonusItemEntity>(lstObjKPIBonusItem, ConstantSql.hrm_cat_sp_get_KPIBonusItem, UserLogin, ref status).ToList();
var revenueForRecordServices = new Sal_RevenueRecordService();
var lstObjRevenueRecord = new List<object>();
lstObjRevenueRecord.Add(null);
lstObjRevenueRecord.Add(null);
lstObjRevenueRecord.Add(1);
lstObjRevenueRecord.Add(int.MaxValue - 1);
var lstRevenueRecord = revenueForRecordServices.GetData<Sal_RevenueRecordEntity>(lstObjRevenueRecord, ConstantSql.hrm_sal_sp_get_RevenueRecord, UserLogin, ref status).Where(s => s.Month.Value.Month == dateFrom.Month && s.Month.Value.Year == dateFrom.Year && s.Month.Value.Month == dateFrom.Month && s.Month.Value.Year == dateFrom.Year).ToList();
var revenueForShopServices = new Sal_RevenueForShopServices();
var lstObjRevenueForShop = new List<object>();
lstObjRevenueForShop.Add(null);
lstObjRevenueForShop.Add(null);
lstObjRevenueForShop.Add(null);
lstObjRevenueForShop.Add(null);
lstObjRevenueForShop.Add(1);
lstObjRevenueForShop.Add(int.MaxValue - 1);
var lstRevenueForShop = revenueForRecordServices.GetData<Sal_RevenueForShopEntity>(lstObjRevenueForShop, ConstantSql.hrm_sal_sp_get_RevenueForShop, UserLogin, ref status).ToList();
List<object> listModel = new List<object>();
listModel.AddRange(new object[18]);
listModel[16] = 1;
listModel[17] = int.MaxValue - 1;
List<Hre_ProfileEntity> listProfile = GetData<Hre_ProfileEntity>(listModel, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList();
List<ElementFormula> listElementFormula = new List<ElementFormula>();
foreach (var item in lstShop)
{
#region Lấy các phần tử là Enum
Sal_RevenueForShopEntity RevenueForShopItem = new Sal_RevenueForShopEntity();
RevenueForShopItem = lstRevenueForShop.Where(m => m.ShopID == item.ID && m.KPIBonusID == lstKPIBonus.Where(t => t.IsTotalRevenue == true).FirstOrDefault().ID && m.DateFrom <= dateTo && m.DateTo >= dateFrom).FirstOrDefault();
if (RevenueForShopItem != null)
{
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_TAGET_SHOP.ToString(), RevenueForShopItem.Target, 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_ACTUAL_SHOP.ToString(), RevenueForShopItem.Actual, 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_PRECENT_REVENUE.ToString(), RevenueForShopItem.Actual / RevenueForShopItem.Target, 0));
}
else
{
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_TAGET_SHOP.ToString(), 0, 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_ACTUAL_SHOP.ToString(), 0, 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_PRECENT_REVENUE.ToString(), 0, 0));
}
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SHOPMEMBER.ToString(), listProfile.Where(m => m.ShopID == item.ID).Count(), 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SL.ToString(), lstShop.Where(m => m.ID == item.ID).FirstOrDefault().NoShiftLeader, 0));
listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_RANK.ToString(), lstShop.Where(m => m.ID == item.ID).FirstOrDefault().Rank, 0));
#endregion
DataRow dr = table.NewRow();
var lstRevenueForShopByID = lstRevenueForShop.Where(s => s.ShopID == item.ID && lstKPIBonus.Where(m => m.IsTotalRevenue == true).FirstOrDefault().ID == s.KPIBonusID).FirstOrDefault();
dr[Sal_ReportRevenueForShopsEntity.FieldNames.ShopCode] = item != null ? item.Code : string.Empty;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.ShopName] = item != null ? item.ShopName : string.Empty;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Rank] = item != null ? item.Rank : string.Empty;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Target] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Target : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Actual] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Actual : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.CompletionRate] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Actual.Value / lstRevenueForShopByID.Target.Value : 0;
double totalPrice = 0;
foreach (var kpi in lstKPIBonus)
{
var CommissionName = Sal_ReportRevenueForShopsEntity.FieldNames.Commission + kpi.KPIBonusName;
var lstKpiBonusByID = lstKPIBonusItem.Where(s => s.ShopID == item.ID && kpi.ID == s.KPIBonusID).FirstOrDefault();
var lstRevenueRecordByID = lstRevenueRecord.Where(s => s.ShopID.Value == item.ID && s.KPIBonusID == kpi.ID).FirstOrDefault();
if (table.Columns.Contains(kpi.KPIBonusName))
{
dr[kpi.KPIBonusName] = lstKpiBonusByID != null ? lstKpiBonusByID.Value : 0;
}
if (table.Columns.Contains(CommissionName))
{
dr[CommissionName] = lstRevenueRecordByID != null ? lstRevenueRecordByID.Amount * 1 : 0;
totalPrice += lstRevenueRecordByID != null ? (double)lstRevenueRecordByID.Amount : 0;
}
}
var lstRevenueRecordByItem = lstRevenueRecord.Where(s => s.KPIBonusID == null && s.Type == EnumDropDown.SalesType.E_ITEM_MAJOR.ToString() && s.ShopID == item.ID).FirstOrDefault();
var lstRevenueRecordByLineItem = lstRevenueRecord.Where(s => s.KPIBonusID == null && s.Type == EnumDropDown.SalesType.E_LINEITEM_MAJOR.ToString() && s.ShopID == item.ID).FirstOrDefault();
dr[Sal_ReportRevenueForShopsEntity.FieldNames.TopLine5Forcus] = item != null ? item.MainLineProduct : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.CommissionTopLine5Forcus] = lstRevenueRecordByItem != null ?
lstRevenueRecordByItem.Amount : 0;
totalPrice += lstRevenueRecordByItem != null ? (double)lstRevenueRecordByItem.Amount : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.ProductForcus] = item != null ? item.PromoteProduct : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.CommissionProductForcus] = lstRevenueRecordByLineItem != null ? lstRevenueRecordByLineItem.Amount : 0;
totalPrice += lstRevenueRecordByLineItem != null ? (double)lstRevenueRecordByLineItem.Amount : 0;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Commission] = totalPrice;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Incentive] = totalPrice;
double FriceSL = (double)GetObjectValue(new List<Cat_ElementEntity>(), listElementFormula, item.Formular1).Value;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Shiftleader] = FriceSL;
dr[Sal_ReportRevenueForShopsEntity.FieldNames.Total] = totalPrice + FriceSL;
table.Rows.Add(dr);
}
return table.ConfigTable(true);
}
}
public DataTable GetReportHaveChildUsunual(string orderNumberOrg, DateTime? dateFrom, DateTime? dateTo, string UserLogin, bool IsCreateTemplate)
{
DataTable table = CreateReportHaveChildUsunualSchema();
if (IsCreateTemplate)
{
return table;
}
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var orgTypeServices = new Cat_OrgStructureTypeServices();
var objOrgType = new List<object>();
objOrgType.Add(null);
objOrgType.Add(null);
objOrgType.Add(1);
objOrgType.Add(int.MaxValue - 1);
var lstOrgType = orgTypeServices.GetData<Cat_OrgStructureTypeEntity>(objOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList();
var orgServices = new Cat_OrgStructureServices();
var objOrg = new List<object>();
objOrg.Add(null);
objOrg.Add(null);
objOrg.Add(null);
objOrg.Add(1);
objOrg.Add(int.MaxValue - 1);
var lstOrg = orgServices.GetData<Cat_OrgStructureEntity>(objOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, UserLogin, ref status).ToList();
//if (lstOrgType != null)
//{
// lstOrg = lstOrg.Where(s => s.OrgStructureTypeID != null && s.OrgStructureTypeID == lstOrgType.ID).ToList();
//}
var lstOrgNumber = lstOrg.Select(s => s.OrderNumber).ToList();
var strOrderNumber = string.Join(",", lstOrgNumber);
var profileServices = new Hre_ProfileServices();
var objProfile = new List<object>();
objProfile.Add(strOrderNumber);
objProfile.Add(null);
objProfile.Add(null);
var lstProfile = profileServices.GetData<Hre_ProfileEntity>(objProfile, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, UserLogin, ref status).ToList();
var relativeServices = new Hre_RelativesServices();
var objRelative = new List<object>();
objRelative.AddRange(new object[11]);
objRelative[9] = 1;
objRelative[10] = int.MaxValue - 1;
var lstRelatives = relativeServices.GetData<Hre_RelativesEntity>(objRelative, ConstantSql.hrm_hr_sp_get_Relatives, UserLogin, ref status).ToList();
var repoCat_OrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork);
var repoCat_OrgStructureType = new Cat_OrgStructureTypeRepository(unitOfWork);
var orgs = repoCat_OrgStructure.FindBy(s => s.IsDelete == null).ToList();
var orgTypes = repoCat_OrgStructureType.FindBy(s => s.IsDelete == null).ToList();
var UnusualAllowance = new Sal_UnusualAllowanceServices();
var objUnusualAllowance = new List<object>();
objUnusualAllowance.AddRange(new object[7]);
objUnusualAllowance[3] = dateFrom;
objUnusualAllowance[4] = dateTo;
objUnusualAllowance[5] = 1;
objUnusualAllowance[6] = int.MaxValue - 1;
List<Sal_UnusualAllowanceEntity> listUnusualAllowance = UnusualAllowance.GetData<Sal_UnusualAllowanceEntity>(objUnusualAllowance, ConstantSql.hrm_sal_sp_get_UnusualEDChildCare, UserLogin, ref status);
//lọc nhân viên có hưởng phụ cấp con nhỏ
lstProfile = lstProfile.Where(m => listUnusualAllowance.Any(t => t.ProfileID == m.ID)).ToList();
foreach (var item in lstProfile)
{
DataRow dr = table.NewRow();
var lstRelativeByProfileID = lstRelatives.Where(s => s.ProfileID == item.ID).ToList();
if (lstRelativeByProfileID.Count == 0)
{
continue;
}
Guid? orgId = item.OrgStructureID;
var unit = LibraryService.GetNearestParent(orgId, OrgUnit.E_UNIT, orgs, orgTypes);
var division = LibraryService.GetNearestParent(orgId, OrgUnit.E_DIVISION, orgs, orgTypes);
var department = LibraryService.GetNearestParent(orgId, OrgUnit.E_DEPARTMENT, orgs, orgTypes);
var section = LibraryService.GetNearestParent(orgId, OrgUnit.E_SECTION, orgs, orgTypes);
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.CodeEmp] = item.CodeEmp;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.ProfileName] = item.ProfileName;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.E_UNIT] = item.E_UNIT;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.E_DIVISION] = item.E_DIVISION;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.E_DEPARTMENT] = item.E_DEPARTMENT;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.E_TEAM] = item.E_TEAM;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.E_SECTION] = item.E_SECTION;
if (lstRelativeByProfileID.Count > 0)
{
var count = 0;
foreach (var relative in lstRelativeByProfileID)
{
var _tmp = new DateTime();
count++;
if (count == 11)
{
break;
}
if (relative.YearOfBirth != null)
{
var strYearOfBirth = relative.YearOfBirth.Split('/');
var newYOB = new DateTime(int.Parse(strYearOfBirth[2]), int.Parse(strYearOfBirth[1]), int.Parse(strYearOfBirth[0]));
var dateCheck = DateTime.Now;
var age = (dateCheck.Subtract(_tmp).TotalDays / 30) / 12;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.RelativeName + count] = relative.RelativeName;
dr[Sal_ReportHaveChildUsunualEntity.FieldNames.DateBorn + count] = newYOB;
}
}
}
table.Rows.Add(dr);
}
return table.ConfigTable(true);
}
}
public List<Hre_ReportProfileWorkingEntity> GetReportProfileWorking(DateTime? DateFrom, DateTime? DateTo, string lstOrgOrderNumber, string codeEmp, string userLogin)
{
string status = string.Empty;
List<Hre_ReportProfileWorkingEntity> lstReportProfileWorkingEntity = new List<Hre_ReportProfileWorkingEntity>();
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var orgsService = new Cat_OrgStructureServices();
var orgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var empoyeeTypesServices = new Cat_EmployeeTypeServices();
var empoyeeTypes = empoyeeTypesServices.GetDataNotParam<Cat_EmployeeTypeEntity>(ConstantSql.hrm_cat_sp_get_AllEmpType, userLogin, ref status).ToList();
var postionsServices = new Cat_PositionServices();
var postions = postionsServices.GetDataNotParam<Cat_PositionEntity>(ConstantSql.hrm_cat_sp_get_AllPosition, userLogin, ref status).ToList();
var orgTypesServices = new Cat_OrgStructureTypeServices();
var orgTypes = orgTypesServices.GetDataNotParam<Cat_OrgStructureType>(ConstantSql.hrm_cat_sp_get_AllOrgType, userLogin, ref status).ToList();
var hrService = new Hre_ProfileServices();
List<object> listObjHr = new List<object>();
listObjHr.Add(lstOrgOrderNumber);
listObjHr.Add(DateFrom);
listObjHr.Add(DateTo);
listObjHr.Add(codeEmp);
var profiles = hrService.GetData<Hre_ProfileEntity>(listObjHr, ConstantSql.hrm_hr_sp_get_RptWorkingProfile, userLogin,ref status).ToList();
foreach (var profile in profiles)
{
Hre_ReportProfileWorkingEntity ReportProfileWorkingEntity = new Hre_ReportProfileWorkingEntity();
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);
ReportProfileWorkingEntity.CodeBranch = orgBranch != null ? orgBranch.Code : string.Empty;
ReportProfileWorkingEntity.CodeOrg = orgOrg != null ? orgOrg.Code : string.Empty;
ReportProfileWorkingEntity.CodeTeam = orgTeam != null ? orgTeam.Code : string.Empty;
ReportProfileWorkingEntity.CodeSection = orgSection != null ? orgSection.Code : string.Empty;
ReportProfileWorkingEntity.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
ReportProfileWorkingEntity.OrgName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
ReportProfileWorkingEntity.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
ReportProfileWorkingEntity.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty;
ReportProfileWorkingEntity.CodeEmp = profile.CodeEmp;
ReportProfileWorkingEntity.ProfileName = profile.ProfileName;
ReportProfileWorkingEntity.IDNo = profile.IDNo;
ReportProfileWorkingEntity.Gender = profile.Gender;
ReportProfileWorkingEntity.CodeAttendance = profile.CodeAttendance;
ReportProfileWorkingEntity.PAStreet = profile.PAddress;
ReportProfileWorkingEntity.DateHire = profile.DateHire;
var employeeType = empoyeeTypes.Where(s => profile.EmpTypeID == s.ID).FirstOrDefault();
if (employeeType != null)
{
ReportProfileWorkingEntity.EmployeeTypeName = employeeType.EmployeeTypeName;
}
var position = postions.Where(s => profile.PositionID == s.ID).FirstOrDefault();
if (position != null)
{
ReportProfileWorkingEntity.PositionName = position.PositionName;
}
lstReportProfileWorkingEntity.Add(ReportProfileWorkingEntity);
}
return lstReportProfileWorkingEntity;
}
}
public DataTable ReportUnusualPay(DateTime monthYear, List<Guid> listProfileIDs, Guid? payrollGroupID, Boolean isIncludeQuitEmp, string codeEmp, string orderNumber, string UserLogin)
{
try
{
using (var context = new VnrHrmDataContext())
{
#region " Load dữ liệu"
string status = string.Empty;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
//kỳ lương (hiện tại mặc định đầu tháng -> cuối tháng)
DateTime from = new DateTime(monthYear.Year, monthYear.Month, 1);
DateTime to = new DateTime(monthYear.Year, monthYear.Month, DateTime.DaysInMonth(monthYear.Year, monthYear.Month));
//Ds tất cả phòng ban
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
// var reposOrgStructure = new CustomBaseRepository<Cat_OrgStructure>(unitOfWork);
var listOrgAll = orgServices.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, UserLogin, ref status).ToList();
var lstObjOrgNumber = new List<object>();
lstObjOrgNumber.Add(orderNumber);
var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrgNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList();
//Ds cửa Hàng
var shopServices = new Cat_ShopServices();
var lstObjShop = new List<object>();
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(null);
lstObjShop.Add(1);
lstObjShop.Add(int.MaxValue - 1);
var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList();
//Ds tất cả loại PB
var orgTypeServices = new Cat_OrgStructureTypeServices();
var lstObjOrgType = new List<object>();
lstObjOrgType.Add(string.Empty);
lstObjOrgType.Add(string.Empty);
lstObjOrgType.Add(1);
lstObjOrgType.Add(int.MaxValue - 1);
// var reposOrgStructureType = new CustomBaseRepository<Cat_OrgStructureType>(unitOfWork);
var listOrgType = orgTypeServices.GetData<Cat_OrgStructureType>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, UserLogin, ref status).ToList();
//Ds nhân viên
var reposProfile = new CustomBaseRepository<Hre_Profile>(unitOfWork);
var profileServices = new Hre_ProfileServices();
var lstObjProfile = new List<object>();
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(string.Empty);
lstObjProfile.Add(1);
lstObjProfile.Add(int.MaxValue - 1);
//lọc theo phòng ban
//var listProfile = reposProfile.GetAll().Where(hr => hr.IsDelete == null
// && hr.OrgStructureID.HasValue
// && listOrgIDs.Contains(hr.OrgStructureID.Value)).ToList();
var listProfile = reposProfile.GetAll().Where(s => s.IsDelete != true).ToList();
if (listProfileIDs != null)
{
listProfile = listProfile.Where(s => listProfileIDs.Contains(s.ID)).ToList();
}
//lọc theo nhóm lương
// listProfile = listProfile.Where(hr => hr.PayrollGroupID.HasValue && listPrGroupIDs.Contains(hr.PayrollGroupID.Value)).ToList();
List<Guid> listProfileID = listProfile.Select(s => s.ID).Distinct().ToList();
//lọc nhân viên nghỉ việc
if (!isIncludeQuitEmp)
{
listProfile = listProfile.Where(pro => pro.DateHire <= to && (pro.DateQuit == null || pro.DateQuit.Value > from)).ToList();
}
if (payrollGroupID != null && payrollGroupID != Guid.Empty)
{
listProfile = listProfile.Where(s => s.PayrollGroupID == payrollGroupID).ToList();
}
//lọc theo tên nhân viên
if (!string.IsNullOrEmpty(codeEmp))
{
listProfile = listProfile.Where(s => s.CodeEmp == codeEmp).ToList();
}
//ds chế độ lương
var saleGradeServices = new Sal_GradeServices();
var lstObjSalGrade = new List<object>();
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(string.Empty);
lstObjSalGrade.Add(1);
lstObjSalGrade.Add(int.MaxValue - 1);
var reposSalGrade = new CustomBaseRepository<Sal_Grade>(unitOfWork);
var lstGradeAll = reposSalGrade.GetAll().Where(s => s.IsDelete != true).ToList();
// var lstGradeAll = saleGradeServices.GetData<Sal_Grade>(lstObjSalGrade, ConstantSql.hrm_sal_sp_get_Sal_Grade, UserLogin,ref status).ToList();
//ds thông tin lương
var salInfoServices = new Sal_SalaryInformationServices();
var lstObjSalInfo = new List<object>();
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(string.Empty);
lstObjSalInfo.Add(1);
lstObjSalInfo.Add(int.MaxValue - 1);
var salInfoServices1 = new Sal_SalaryInformationServices();
var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformationEntity>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation, UserLogin, ref status).OrderByDescending(sal => sal.DateUpdate).ToList().Translate<Sal_SalaryInformation>();
//var reposSalaryInformation = new CustomBaseRepository<Sal_SalaryInformation>(unitOfWork);
//var lstSalaryInformation = salInfoServices.GetData<Sal_SalaryInformation>(lstObjSalInfo, ConstantSql.hrm_sal_sp_get_Sal_SalaryInformation,UserLogin,ref status).OrderByDescending(sal => sal.DateUpdate).ToList();
// var lstSalaryInformation = new Sal_SalaryInformation();
//ds hợp đồng
var contractServices = new Hre_ContractServices();
var lstObjContract = new List<object>();
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(string.Empty);
lstObjContract.Add(1);
lstObjContract.Add(int.MaxValue - 1);
var reposHreContract = new CustomBaseRepository<Hre_Contract>(unitOfWork);
// var lstHreContractAll = contractServices.GetData<Hre_ContractEntity>(lstObjContract, ConstantSql.hrm_hr_sp_get_Contract, UserLogin,ref status).ToList().Translate<Hre_Contract>();
var lstHreContractAll = reposHreContract.GetAll().Where(s => s.IsDelete != true).ToList();
//ds mức lươn64864866498*-*/-*/7896543564555555978g
var salaryRankServices = new Cat_SalaryRankServices();
var lstObjSalaryRank = new List<object>();
lstObjSalaryRank.Add(string.Empty);
lstObjSalaryRank.Add(null);
lstObjSalaryRank.Add(1);
lstObjSalaryRank.Add(int.MaxValue - 1);
var reposSalaryRank = new CustomBaseRepository<Cat_SalaryRank>(unitOfWork);
var lstSalaryRankAll = salaryRankServices.GetData<Cat_SalaryRankEntity>(lstObjSalaryRank, ConstantSql.hrm_cat_sp_get_SalaryRank, UserLogin, ref status).ToList().Translate<Cat_SalaryRank>();
var revenueProfileServices = new Sal_RevenueForProfileServices();
var lstObjRevenueForProfile = new List<object>();
lstObjRevenueForProfile.Add(null);
lstObjRevenueForProfile.Add(1);
lstObjRevenueForProfile.Add(int.MaxValue - 1);
var lstRevenueForProfile = revenueProfileServices.GetData<Sal_RevenueForProfileEntity>(lstObjRevenueForProfile, ConstantSql.hrm_sal_sp_get_RevenueForProfile, UserLogin, ref status).ToList();
//Ds phần tử lương
var elementServices = new Cat_ElementServices();
var methodPayroll = MethodPayroll.E_ADNVANCE_PAYMENT.ToString();
var lstObjElement = new List<object>();
lstObjElement.Add("");
lstObjElement.Add("");
lstObjElement.Add("");
lstObjElement.Add(1);
lstObjElement.Add(int.MaxValue - 1);
var reposPayrollElement = new CustomBaseRepository<Cat_Element>(unitOfWork);
//var reposPayrollElement = new CustomBaseRepository<Cat_ElementMultiEntity>(unitOfWork);
// var listPayrollElement = elementServices.GetData<Cat_ElementMultiEntity>(lstObjElement, ConstantSql.hrm_cat_sp_get_Element,UserLogin,ref status).ToList();
var listPayrollElement = reposPayrollElement.GetAll().Where(s => s.MethodPayroll == methodPayroll && s.IsDelete != true).ToList();
// ds Bảng Ứng Lương
var repoUnusualPay = new CustomBaseRepository<Sal_UnusualPay>(unitOfWork);
var lstUnusualPay = repoUnusualPay.GetAll().Where(s => s.IsDelete != true && s.MonthYear == monthYear && listProfileID.Contains(s.ProfileID)).ToList();
List<Guid> lstUnusualPayIDs = lstUnusualPay.Select(s => s.ID).ToList();
//Ds Bảng Ứng Lương Chi Tiết
var repoUnusualPayItem = new CustomBaseRepository<Sal_UnusualPayItem>(unitOfWork);
var lstUnusualPayItem = repoUnusualPayItem.GetAll().Where(s => s.IsDelete != true && lstUnusualPayIDs.Contains(s.UnusualPayID)).ToList();
// //ds bảng lương
// var payrollTableServices = new Sal_PayrollTableServices();
// var lstObjPayrollTable = new List<object>();
// lstObjPayrollTable.Add(string.Empty);
// lstObjPayrollTable.Add(string.Empty);
// lstObjPayrollTable.Add(1);
// lstObjPayrollTable.Add(int.MaxValue -1);
// var reposSalPayrollTb = new CustomBaseRepository<Sal_PayrollTable>(unitOfWork);
//// var lstSalPayrollTb = payrollTableServices.GetData<Sal_PayrollTableEntity>(lstObjPayrollTable, ConstantSql.hrm_sal_sp_get_PayrollTable, UserLogin,ref status).Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList();
// var lstSalPayrollTb = reposSalPayrollTb.GetAll().Where(pr => pr.IsDelete == null && pr.MonthYear == monthYear && listProfileID.Contains(pr.ProfileID)).ToList();
// List<Guid> listSalPayrollIDs = lstSalPayrollTb.Select(p => p.ID).ToList();
// //order theo pb
// lstSalPayrollTb = lstSalPayrollTb.OrderBy(m => m.Hre_Profile.Cat_OrgStructure.OrderOrg != null ? m.Hre_Profile.Cat_OrgStructure.OrderOrg.Value : 0).ToList();
// //ds bảng lương chi tiết
// var payrollTableItemServices = new Sal_PayrollTableItemServices();
// var lstObjPayrollTableItem = new List<object>();
// lstObjPayrollTableItem.Add(string.Empty);
// lstObjPayrollTableItem.Add(string.Empty);
// lstObjPayrollTableItem.Add(1);
// lstObjPayrollTableItem.Add(int.MaxValue -1);
// var reposSalPayrollTbItem = new CustomBaseRepository<Sal_PayrollTableItem>(unitOfWork);
//// var lstSalPayrollTbItem = payrollTableItemServices.GetData<Sal_PayrollTableItem>(lstObjPayrollTableItem, ConstantSql.hrm_sal_sp_get_PayrollTableItem, UserLogin,ref status).Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList();
// var lstSalPayrollTbItem = reposSalPayrollTbItem.GetAll().Where(it => it.IsDelete == null && listSalPayrollIDs.Contains(it.PayrollTableID)).ToList();
//Tạo cột dữ liệu cho table
DataTable tblData = GetSchemaReportUnusualPay();
#endregion
#region " Không có dữ liệu Sal_PayrollTable"
if (lstUnusualPay == null || lstUnusualPay.Count <= 0)
{
foreach (Hre_Profile profile in listProfile)
{
DataRow dr = tblData.NewRow();
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty;
// dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty;
if (profile.DateHire != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value;
if (profile.DateQuit != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit;
if (profile.DateEndProbation != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty;
if (profile.OrgStructureID != null)
{
var orgName = GetParentOrg(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2];
dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1];
dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0];
//var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType);
//var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType);
//var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType);
//var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType);
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
}
Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault();
if (hrcontract != null && hrcontract.RankRateID != null)
{
Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault();
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank != null ? salrank.SalaryRankName : string.Empty;
}
#region Insurance, Bank
var salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList();
if (salaryInformationList.Count > 0)
{
Sal_SalaryInformation bankSalary = salaryInformationList[0];
string accountTemp = string.Empty;
string accountNo = string.Empty;
string accountNo2 = string.Empty;
string bankNameTemp = string.Empty;
string bankCode = string.Empty;
accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2;
bankNameTemp = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankName;
bankCode = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankCode;
if (!string.IsNullOrEmpty(accountTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim();
if (!string.IsNullOrEmpty(accountNo))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim();
if (!string.IsNullOrEmpty(accountNo2))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim();
if (!string.IsNullOrEmpty(bankCode))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim();
if (!string.IsNullOrEmpty(bankNameTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim();
}
#endregion
tblData.Rows.Add(dr);
}
}
#endregion
#region " Có dữ liệu tính lương Sal_PayrollTable"
else
{
foreach (Sal_UnusualPay payroll in lstUnusualPay)
{
Hre_Profile profile = listProfile.Where(s => s.ID == payroll.ProfileID).FirstOrDefault();
// var lstRevenueByProfileId = lstRevenueForProfile.Where(s => s.ProfileID == payroll.ProfileID).FirstOrDefault();
// var lstRankByProfileId = lstShop.Where(s => s.ID == profile.ShopID).FirstOrDefault();
List<Sal_UnusualPayItem> listItem = lstUnusualPayItem.Where(pi => pi.UnusualPayID == payroll.ID).ToList();
if (profile == null)
continue;
DataRow dr = tblData.NewRow();
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.MonthYear] = monthYear;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeEmp] = profile.CodeEmp;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CodeAttendance] = profile.CodeAttendance;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.ProfileName] = profile.ProfileName;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.LaborType] = profile.LaborType;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.EmployeeType] = profile.Cat_EmployeeType != null ? profile.Cat_EmployeeType.EmployeeTypeName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Supervisor] = profile.Supervisor != null ? profile.Supervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.HighSupervisor] = profile.HighSupervisor != null ? profile.HighSupervisor : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkPlace] = profile.Cat_WorkPlace != null ? profile.Cat_WorkPlace.WorkPlaceName : string.Empty;
// dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.WorkingPlace] = profile.WorkingPlace != null ? profile.WorkingPlace : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TaxCode] = profile.CodeTax != null ? profile.CodeTax : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.IDNo] = profile.IDNo != null ? profile.IDNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenter] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.CostCentreName : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.CostCenterCode] = profile.Cat_CostCentre != null ? profile.Cat_CostCentre.Code : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PositionName] = profile.Cat_Position != null ? profile.Cat_Position.PositionName : string.Empty;
dr["PositionCode"] = profile.Cat_Position != null ? profile.Cat_Position.Code : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.JobtitleName] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.JobTitleName : string.Empty;
dr["JobtitleCode"] = profile.Cat_JobTitle != null ? profile.Cat_JobTitle.Code : string.Empty;
// dr["Rank"] = lstRankByProfileId != null ? lstRankByProfileId.Rank : string.Empty;
// dr["Target"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Target : 0;
// dr["Actual"] = lstRevenueByProfileId != null ? lstRevenueByProfileId.Actual : 0;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.Email] = profile.Email != null ? profile.Email : string.Empty;
if (profile.DateHire != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateHire] = profile.DateHire.Value;
if (profile.DateQuit != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateQuit] = profile.DateQuit;
if (profile.DateEndProbation != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DateEndProbation] = profile.DateEndProbation.Value;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SocialInsNo] = profile.SocialInsNo != null ? profile.SocialInsNo : string.Empty;
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.PayrollGroup] = profile.Cat_PayrollGroup != null ? profile.Cat_PayrollGroup.PayrollGroupName : string.Empty;
if (profile.OrgStructureID != null)
{
var orgName = GetParentOrgNameForShiseido(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
if (orgName.Count < 5)
{
orgName.Insert(0, string.Empty);
}
}
dr["Công Ty"] = orgName[4];
dr["Chi Nhánh"] = orgName[3];
dr[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2];
dr[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1];
dr[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0];
var orgCode = GetParentOrgCodeForShiseido(listOrgAll, listOrgType, profile.OrgStructureID);
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
if (orgCode.Count < 5)
{
orgCode.Insert(0, string.Empty);
}
}
dr["Mã Công Ty"] = orgCode[4];
dr["Mã Chi Nhánh"] = orgCode[3];
dr["Mã Channel"] = orgCode[2];
dr["Mã Region"] = orgCode[1];
dr["Mã Area"] = orgCode[0];
//var orgBranch = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_BRANCH, listOrgAll, listOrgType);
//var orgOrg = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_DEPARTMENT, listOrgAll, listOrgType);
//var orgTeam = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_TEAM, listOrgAll, listOrgType);
//var orgSection = LibraryService.GetNearestParent(profile.OrgStructureID, OrgUnit.E_SECTION, listOrgAll, listOrgType);
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchCode] = orgBranch != null ? orgBranch.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentCode] = orgOrg != null ? orgOrg.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamCode] = orgTeam != null ? orgTeam.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionCode] = orgSection != null ? orgSection.Code : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BranchName] = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.DepartmentName] = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.TeamName] = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
//dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SectionName] = orgSection != null ? orgSection.OrgStructureName : string.Empty;
}
Hre_Contract hrcontract = lstHreContractAll.Where(hr => hr.ProfileID == profile.ID && hr.IsDelete == null).FirstOrDefault();
if (hrcontract != null && hrcontract.RankRateID != null)
{
Cat_SalaryRank salrank = lstSalaryRankAll.Where(rk => rk.ID == hrcontract.RankRateID.Value && rk.IsDelete == null).FirstOrDefault();
if (salrank != null)
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.SalaryRankName] = salrank.SalaryRankName;
}
#region Insurance, Bank
List<Sal_SalaryInformation> salaryInformationList = lstSalaryInformation.Where(sal => sal.ProfileID == profile.ID).ToList();
if (salaryInformationList.Count > 0)
{
Sal_SalaryInformation bankSalary = salaryInformationList[0];
string accountTemp = string.Empty;
string accountNo = string.Empty;
string accountNo2 = string.Empty;
string bankNameTemp = string.Empty;
string bankCode = string.Empty;
accountTemp = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo = bankSalary.AccountNo == null ? string.Empty : bankSalary.AccountNo;
accountNo2 = bankSalary.AccountNo2 == null ? string.Empty : bankSalary.AccountNo2;
bankNameTemp = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankName;
bankCode = bankSalary.Cat_Bank == null ? string.Empty : bankSalary.Cat_Bank.BankCode;
if (!string.IsNullOrEmpty(accountTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankAccountNo] = accountTemp.Trim();
if (!string.IsNullOrEmpty(accountNo))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo] = accountNo.Trim();
if (!string.IsNullOrEmpty(accountNo2))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.AccountNo2] = accountNo2.Trim();
if (!string.IsNullOrEmpty(bankCode))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankCode] = bankCode.Trim();
if (!string.IsNullOrEmpty(bankNameTemp))
dr[Sal_ReportBasicSalaryMonthlyEntity.FieldNames.BankName] = bankNameTemp.Trim();
}
#endregion
#region sal grade
// Sal_Grade grade = lstGradeAll.Where(gr => gr.IsDelete == null && gr.ProfileID == profile.ID
// && gr.MonthStart < to).OrderByDescending(gr => gr.MonthStart).FirstOrDefault();
// if (grade != null)
// {
// Cat_GradePayroll gradepayroll = grade.Cat_GradePayroll1;
// if (gradepayroll != null && gradepayroll.IsFormulaSalary == true)
// {
try
{
var lstSalPrItem = lstUnusualPayItem.Where(sal => sal.UnusualPayID == payroll.ID).ToList();
// var lstelement = listPayrollElement.Where(pr => pr.GradePayrollID == gradepayroll.ID).ToList();
if (listPayrollElement != null && listPayrollElement.Count > 0)
{
foreach (Cat_Element payrollElement in listPayrollElement)
{
if (payrollElement != null && !String.IsNullOrEmpty(payrollElement.ElementCode))
{
//Add phần tử vào cột dữ liệu nếu chưa có
if (!tblData.Columns.Contains(payrollElement.ElementCode))
{
tblData.Columns.Add(payrollElement.ElementCode, typeof(Double));
}
//Lấy value của phần tử
if (tblData.Columns.Contains(payrollElement.ElementCode))
{
Sal_UnusualPayItem item = lstSalPrItem.Where(it => it.Element == payrollElement.ElementCode).FirstOrDefault();
Double value = 0;
if (item != null)
{
//if (item.ValueType == typeof(Double).Name)
// {
Double.TryParse(item.Amount.ToString(), out value);
// }
}
dr[payrollElement.ElementCode] = value;
}
}
}
}
}
catch { }
// }
// }
#endregion
tblData.Rows.Add(dr);
}
}
#endregion
return tblData.ConfigTable(true);
}
}
catch (Exception ex)
{
return new DataTable();
}
}
public DataTable GetReportHCSeniority(DateTime dateSearch, List<Guid> lstjobTitles, Guid orgID, Guid? orgTypeId, bool isIncludeQuitEmp, bool isCreateTemplate, string userLogin)
{
List<string> lstSeniority = new List<string> { ">12 Tháng", "9-12 Tháng", "6-9 Tháng", "3-6 Tháng", "0-2 Tháng" };
DataTable table = CreateReportHCSenioritySchema(orgID, orgTypeId, userLogin);
if (isCreateTemplate)
{
return table.ConfigTable();
}
string status = string.Empty;
using (var context = new VnrHrmDataContext())
{
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var serviceProfile = new Hre_ProfileServices();
var baseService = new BaseService();
var lstObjProfileIDs = new List<object>();
DataRow row1 = table.NewRow();
var jobtitleServices = new Cat_JobTitleServices();
var lstJobtitle = jobtitleServices.GetData<Cat_JobTitleEntity>(lstjobTitles[0], ConstantSql.hrm_cat_sp_get_JobTitleById, userLogin,ref status).FirstOrDefault();
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
var lstOrgName = lstallorgs.Where(s => s.ID == orgID).FirstOrDefault();
var listorgid = lstorgs.Select(s => new { s.ID, s.OrderNumber, s.Code, s.OrgStructureName }).ToList();
//Xử Lý lấy tất cả nhân viên trong phòng ban đã chọn và group 1 cấp
var orgIDs = string.Empty;
orderNumber = string.Empty;
foreach (var item in listorgid)
{
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
}
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(orderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin,ref status).Select(s => s.ID).ToList();
List<object> listObj = new List<object>();
listObj.Add(orderNumber);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var lstprofile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin,ref status).ToList();
if (!isIncludeQuitEmp)
{
lstprofile = lstprofile.Where(s => s.DateQuit == null).ToList();
}
if (lstjobTitles != null && lstjobTitles[0] != null && lstjobTitles[0] != Guid.Empty)
{
lstprofile = lstprofile.Where(s => s.JobTitleID != null && lstjobTitles.Contains(s.JobTitleID.Value)).ToList();
}
if (lstprofile == null || lstorgs == null)
{
return table;
}
if (lstJobtitle != null)
{
row1[Hre_ReportHCSeniorityEntity.FieldNames.OrgStructureName] = lstOrgName == null ? string.Empty : lstOrgName.OrgStructureName + " - " + lstJobtitle.JobTitleName;
}
else
{
row1[Hre_ReportHCSeniorityEntity.FieldNames.OrgStructureName] = lstOrgName == null ? string.Empty : lstOrgName.OrgStructureName;
}
table.Rows.Add(row1);
foreach (var item in lstSeniority)
{
DataRow row = table.NewRow();
row[Hre_ReportHCSeniorityEntity.FieldNames.Type] = item;
var IDsCount = string.Empty;
var count = 0;
int totalcount = 0;
foreach (var org in lstorgs)
{
//xử lý đến nhân viên của phòng ban con
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(orderNumber);
var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin,ref status).ToList();
if (orgTypeId != null)
{
lstOrgByOrderNumberCount = lstOrgByOrderNumberCount.Where(s => s.OrgStructureTypeID == orgTypeId).ToList();
}
foreach (var orgName in lstOrgByOrderNumberCount)
{
var lstprofilebyOrg = lstprofile.Where(s => s.OrgStructureID != null && orgName.ID == s.OrgStructureID.Value).ToList();
if (lstprofilebyOrg == null)
{
continue;
}
// > 12 tháng
DateTime dateHire = DateTime.MinValue;
if (item == ">12 Tháng")
{
dateHire = dateSearch.AddMonths(-12);
count = lstprofilebyOrg.Where(m => m.DateHire < dateHire).Count();
}
// 9 -12 tháng
if (item == "9-12 Tháng")
{
dateHire = dateSearch.AddMonths(-9);
DateTime datehireTo = dateSearch.AddMonths(-12);
count = lstprofilebyOrg.Where(m => m.DateHire < dateHire && m.DateHire >= datehireTo).Count();
}
// 6-9 tháng
if (item == "6-9 Tháng")
{
dateHire = dateSearch.AddMonths(-6);
DateTime datehireTo = dateSearch.AddMonths(-9);
count = lstprofilebyOrg.Where(m => m.DateHire < dateHire && m.DateHire >= datehireTo).Count();
}
// 3-6 tháng
if (item == "3-6 Tháng")
{
dateHire = dateSearch.AddMonths(-3);
DateTime datehireTo = dateSearch.AddMonths(-6);
count = lstprofilebyOrg.Where(m => m.DateHire < dateHire && m.DateHire >= datehireTo).Count();
}
// 0-2 tháng
if (item == "0-2 Tháng")
{
dateHire = dateSearch.AddMonths(-2);
count = lstprofilebyOrg.Where(m => m.DateHire >= dateHire).Count();
}
totalcount += count;
if (count > 0)
{
var orgStructureName = orgName.OrgStructureName + "_" + orgName.Code;
if (orgName != null && !string.IsNullOrEmpty(orgName.OrgStructureName) && table.Columns.Contains(orgName.OrgStructureName))
{
row[Hre_ReportHCGenderEntity.FieldNames.HeadCount] = totalcount;
row[orgName.OrgStructureName] = count;
row[Hre_ReportHCGenderEntity.FieldNames.Total] = totalcount;
}
}
}
}
//if (totalcount == 0)
//{
// continue;
//}
table.Rows.Add(row);
}
DataRow datarow = table.NewRow();
datarow[1] = "Total GT";
for (int i = 2; i < table.Columns.Count; i++)
{
int gt = 0;
for (int j = 0; j < table.Rows.Count; j++)
{
var valueRow = table.Rows[j][i].ToString();
if (!string.IsNullOrEmpty(valueRow) && !string.IsNullOrWhiteSpace(valueRow))
{
var value = int.Parse(valueRow);
if (value >= 0)
{
gt += value;
}
}
}
datarow[i] = gt;
}
table.Rows.Add(datarow);
return table.ConfigTable(true);
}
}
public DataTable GetReportTraineeJoinCourse(Guid[] rankIds, string[] courseIds, string orderNumber, bool IsCreateTemplate, DateTime? dateSeniory, string UserLogin)
{
DataTable table = CreateReportTraineeJoinCourseSchema();
if (IsCreateTemplate)
{
return table;
}
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
var dateCheck = DateTime.Now;
//var dateSeniotyConfig = "31/03/N";
// var strDateSeniorty = dateSeniotyConfig.Split('/');
var dateCheckConfig = DateTime.Now;
//if (dateCheckConfig.Day <= int.Parse(strDateSeniorty[0]) && dateCheckConfig.Month <= int.Parse(strDateSeniorty[1]))
//{
// dateCheckConfig = new DateTime(dateCheckConfig.Year - 1, dateCheckConfig.Month, dateCheckConfig.Day);
//}
if (dateSeniory != null)
{
dateCheckConfig = dateSeniory.Value;
}
var profileServices = new Hre_ProfileServices();
var objProfile = new List<object>();
objProfile.AddRange(new object[17]);
objProfile[2] = orderNumber;
objProfile[15] = 1;
objProfile[16] = int.MaxValue - 1;
//objProfile.Add(orderNumber);
//objProfile.Add(null);
//objProfile.Add(null);
var lstProfile = profileServices.GetData<Hre_ProfileEntity>(objProfile, ConstantSql.hrm_hr_sp_get_ProfileAll, UserLogin, ref status).Where(s => s.DateQuit == null).ToList();
//var lstProfile = profileServices.GetData<Hre_ProfileEntity>(objProfile, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, ref status).ToList();
var lstProfileID = lstProfile.Select(s => s.ID).ToList();
var orgsService = new Cat_OrgStructureServices();
var objOrg = new List<object>();
objOrg.AddRange(new object[5]);
objOrg[3] = 1;
objOrg[4] = int.MaxValue - 1;
var lstallorgs = orgsService.GetData<Cat_OrgStructureEntity>(objOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, UserLogin, ref status).ToList();
var courseServices = new Tra_CourseServices();
var objCourse = new List<object>();
objCourse.AddRange(new object[11]);
objCourse[9] = 1;
objCourse[10] = int.MaxValue - 1;
var lstCourse = courseServices.GetData<Tra_CourseEntity>(objCourse, ConstantSql.hrm_tra_sp_get_Course, UserLogin, ref status).ToList();
if (courseIds != null)
{
lstCourse = lstCourse.Where(s => courseIds.Contains(s.Code)).ToList();
}
var jobTitleServices = new Cat_JobTitleServices();
var objJobtitle = new List<object>();
objJobtitle.Add(null);
objJobtitle.Add(null);
objJobtitle.Add(null);
objJobtitle.Add(1);
objJobtitle.Add(int.MaxValue - 1);
var lstJobtitle = jobTitleServices.GetData<Cat_JobTitleEntity>(objJobtitle, ConstantSql.hrm_cat_sp_get_JobTitle, UserLogin, ref status).ToList();
var classServices = new Tra_ClassServices();
var objClass = new List<object>();
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(null);
objClass.Add(1);
objClass.Add(int.MaxValue - 1);
var lstClass = classServices.GetData<Tra_ClassEntity>(objClass, ConstantSql.hrm_tra_sp_get_Class, UserLogin, ref status).ToList();
var salaryClassServices = new Cat_SalaryClassServices();
var objSalaryClass = new List<object>();
objSalaryClass.Add(null);
objSalaryClass.Add(1);
objSalaryClass.Add(int.MaxValue - 1);
var lstSalaryClass = salaryClassServices.GetData<Cat_SalaryClassEntity>(objSalaryClass, ConstantSql.hrm_cat_sp_get_SalaryClass, UserLogin, ref status).ToList();
if (rankIds != null)
{
lstSalaryClass = lstSalaryClass.Where(s => rankIds.Contains(s.ID)).ToList();
}
var rankServices = new Cat_SalaryRankServices();
var objRank = new List<object>();
objRank.Add(null);
objRank.Add(null);
objRank.Add(1);
objRank.Add(int.MaxValue - 1);
var lstRank = rankServices.GetData<Cat_SalaryRankEntity>(objRank, ConstantSql.hrm_cat_sp_get_SalaryRank, UserLogin, ref status).ToList();
var traineeServices = new Tra_TraineeServices();
var objTrainee = new List<object>();
objTrainee.AddRange(new object[15]);
objTrainee[13] = 1;
objTrainee[14] = int.MaxValue - 1;
var lstTrainee = rankServices.GetData<Tra_TraineeEntity>(objTrainee, ConstantSql.hrm_tra_sp_get_Trainee, UserLogin, ref status).ToList();
var workingHistoryServices = new Hre_WorkHistoryServices();
var objWorking = new List<object>();
objWorking.AddRange(new object[17]);
objWorking[15] = 1;
objWorking[16] = int.MaxValue - 1;
var lstWorking = workingHistoryServices.GetData<Hre_WorkHistoryEntity>(objWorking, ConstantSql.hrm_hr_sp_get_WorkHistory, UserLogin, ref status).ToList();
var stopWorkingServices = new Hre_StopWorkingServices();
var objStopWorking = new List<object>();
objStopWorking.AddRange(new object[17]);
objStopWorking[15] = 1;
objStopWorking[16] = int.MaxValue - 1;
var lstStopWorking = stopWorkingServices.GetData<Hre_StopWorkingEntity>(objStopWorking, ConstantSql.hrm_hr_sp_get_StopWorking, UserLogin, ref status).ToList();
var traineeCertificateServices = new Tra_TraineeCertificateServices();
var objTraineeCertificate = new List<object>();
objTraineeCertificate.Add(1);
objTraineeCertificate.Add(int.MaxValue - 1);
var lstTraineeCertificate = traineeCertificateServices.GetData<Tra_TraineeCertificateEntity>(objTraineeCertificate, ConstantSql.hrm_tra_sp_get_TraineeCertificate, UserLogin, ref status).ToList();
var lstTraineeIDFromTraineeCertificate = lstTraineeCertificate.Select(s => s.TraineeID).ToList();
var requirmentDetailServices = new Tra_RequirementTrainDetailServices();
var objRequirementDetail = new List<object>();
objRequirementDetail.Add(null);
objRequirementDetail.Add(1);
objRequirementDetail.Add(int.MaxValue - 1);
var lstRequirementDetail = requirmentDetailServices.GetData<Tra_RequirementTrainDetailEntity>(objRequirementDetail, ConstantSql.hrm_tra_sp_get_RequirementDetail, UserLogin, ref status).ToList();
//if(lstTrainee != null)
//{
// lstTrainee = lstTrainee.Where(s => lstProfileID.Contains(s.ProfileID)).ToList();
//}
if (lstTraineeCertificate != null)
{
lstTrainee = lstTrainee.Where(s => !lstTraineeIDFromTraineeCertificate.Contains(s.ID)).ToList();
}
foreach (var item in lstCourse)
{
string[] arrCourse = new string[1];
if (!string.IsNullOrEmpty(item.CourseListID))
{
arrCourse = item.CourseListID.Split(',').ToArray();
}
string[] arrJobtitle = new string[1];
int[] arrOrg = new int[1];
if (!string.IsNullOrEmpty(item.JobTitleListCode1))
{
arrJobtitle = item.JobTitleListCode1.Split(',').ToArray();
}
if (!string.IsNullOrEmpty(item.OrgListCode1))
{
arrOrg = item.OrgListCode1.Split(',').Select(s => int.Parse(s)).ToArray();
}
var lstJobtitleByJobtitleCode = lstJobtitle.Where(s => arrJobtitle.Contains(s.Code)).Select(s => s.ID).ToList();
var lstOrgByOrderNumber = lstallorgs.Where(s => arrOrg.Contains(s.OrderNumber)).Select(s => s.ID).ToList();
string[] _ranklistID = new string[1];
if (!string.IsNullOrEmpty(item.RankListID))
{
_ranklistID = item.RankListID.Split(',').ToArray();
}
// var lstClassByCourseID = lstClass.Where(s => s.CourseID == item.ID && arrCourse.Contains(s.CourseID)).ToList();
var salaryClassEntity = lstSalaryClass.Where(s => _ranklistID.Contains(s.Code)).ToList();
var lstSalaryClassID = salaryClassEntity.Select(s => s.ID).ToList();
var lstTraineePassedCourse = new List<Tra_TraineeEntity>();
// var lstTraineeForProfile = lstProfile.Where(s => s.CodeEmp == "M26423");
if (arrCourse[0] != null)
{
lstTraineePassedCourse = lstTrainee.Where(s => arrCourse.Contains(s.CourseCode) && s.Status == EnumDropDown.TraineeStatus.E_PASSED.ToString()).ToList();
int soLuongKhoaTienQUyet = arrCourse.Count();
var lstCheckTraineePassedCourse = lstTraineePassedCourse.GroupBy(m => m.ProfileID).Where(m => m.Count() >= soLuongKhoaTienQUyet).ToList();
var lstProfileIDPassedCourse = lstCheckTraineePassedCourse.Select(s => s.Key).Distinct().ToList();
lstProfile = lstProfile.Where(s => lstProfileIDPassedCourse.Contains(s.ID)).ToList();
}
foreach (var profileEntity in lstProfile)
{
var traineePassCourse = lstTrainee.Where(s => s.CourseID == item.ID && profileEntity.ID == s.ProfileID && s.Status == EnumDropDown.TraineeStatus.E_PASSED.ToString()).FirstOrDefault();
if (traineePassCourse != null)
{
continue;
}
Guid? orgId = profileEntity.OrgStructureID;
// var org = lstOrg.FirstOrDefault(s => s.ID == profileEntity.OrgStructureID);
// var orgOrg = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_DEPARTMENT, lstOrg, lstOrgType);
DataRow dr = table.NewRow();
bool flag = false;
var lstTraineeByProfileID = lstTrainee.Where(s => s.ProfileID == profileEntity.ID && arrCourse.Contains(s.CourseCode)).ToList();
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.ID] = profileEntity.ID != null ? profileEntity.ID : Guid.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.CodeEmp] = profileEntity.CodeEmp != null ? profileEntity.CodeEmp : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.TraineeName] = profileEntity.ProfileName != null ? profileEntity.ProfileName : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.IDNo] = profileEntity.IDNo != null ? profileEntity.IDNo : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.OrgStructureName] = profileEntity.OrgStructureName != null ? profileEntity.OrgStructureName : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.E_UNIT] = profileEntity.E_UNIT != null ? profileEntity.E_UNIT : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.E_DIVISION] = profileEntity.E_DIVISION != null ? profileEntity.E_DIVISION : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.E_DEPARTMENT] = profileEntity.E_DEPARTMENT != null ? profileEntity.E_DEPARTMENT : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.E_SECTION] = profileEntity.E_SECTION != null ? profileEntity.E_SECTION : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.E_TEAM] = profileEntity.E_TEAM != null ? profileEntity.E_TEAM : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.JobTitleName] = profileEntity.JobTitleName != null ? profileEntity.JobTitleName : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.PositionName] = profileEntity.PositionName != null ? profileEntity.PositionName : string.Empty;
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.CourseName] = item.CourseName != null ? item.CourseName : string.Empty;
//var profileEntity = lstProfile.Where(s => s.ID == trainee.ProfileID && s.SalaryClassID != null).FirstOrDefault();
var stopWorkingEntity = lstStopWorking.Where(s => s.ProfileID.Value == profileEntity.ID).OrderByDescending(s => s.DateUpdate).FirstOrDefault();
//var workingEntity = new Hre_WorkHistoryEntity();
var lstWorkingByProfileID = lstWorking.Where(s => s.ProfileID == profileEntity.ID && lstSalaryClassID.Contains(s.SalaryClassID == null ? Guid.Empty : s.SalaryClassID.Value)).ToList();
if (lstJobtitleByJobtitleCode.Count > 0)
{
lstWorkingByProfileID = lstWorkingByProfileID.Where(s => lstJobtitleByJobtitleCode.Contains(s.JobTitleID != null ? s.JobTitleID.Value : Guid.Empty)).ToList();
}
if (lstOrgByOrderNumber.Count > 0)
{
lstWorkingByProfileID = lstWorkingByProfileID.Where(s => lstOrgByOrderNumber.Contains(s.OrganizationStructureID != null ? s.OrganizationStructureID.Value : Guid.Empty)).ToList();
}
var workingEntity = lstWorkingByProfileID.OrderByDescending(s => s.DateUpdate).FirstOrDefault();
var dateSuspend = new TimeSpan();
var dateSeniority = new TimeSpan();
double dateResult = 0;
double monthSeniority = 0;
if (profileEntity.DateHire == null)
{
continue;
}
dateSeniority = dateCheckConfig.Subtract(profileEntity.DateHire.Value);
if (stopWorkingEntity != null && stopWorkingEntity.DateStop != null && stopWorkingEntity.DateComeBack != null)
{
dateSuspend = stopWorkingEntity.DateComeBack.Value.Subtract(stopWorkingEntity.DateStop.Value);
dateResult = dateSeniority.Subtract(dateSuspend).TotalDays / 30;
monthSeniority = Math.Floor(dateResult);
}
dateSeniority = dateCheckConfig.Subtract(profileEntity.DateHire.Value);
monthSeniority = Math.Floor(dateSeniority.TotalDays / 30);
dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.Seniority] = monthSeniority;
#region KT điều kiện seniority và maxSeniority != null
if (item.Seniority != null && item.MaxSeniority != null)
{
if (monthSeniority >= item.Seniority && item.MaxSeniority >= monthSeniority)
{
if (salaryClassEntity != null)
{
if (workingEntity != null)
{
// if (profileEntity.SalaryClassID != workingEntity.SalaryClassID)
// {
var dateCheckRank = dateCheck.Subtract(workingEntity.DateEffective).TotalDays / 30;
var monthRank = Math.Floor(dateCheckRank);
if (item.TimeOnCurrentRank != null)
{
if (monthRank < item.TimeOnCurrentRank)
{
flag = true;
// table.Rows.Add(dr);
}
}
//}
}
}
}
else
{
flag = true;
}
}
#endregion
#region KT điều kiện seniority != null && maxSeniority == null
if (item.Seniority != null && item.MaxSeniority == null)
{
if (monthSeniority >= item.Seniority)
{
if (salaryClassEntity != null)
{
if (workingEntity != null)
{
// if (profileEntity.SalaryClassID != workingEntity.SalaryClassID)
// {
var dateCheckRank = dateCheck.Subtract(workingEntity.DateEffective).TotalDays / 30;
var monthRank = Math.Floor(dateCheckRank);
if (item.TimeOnCurrentRank != null)
{
if (monthRank < item.TimeOnCurrentRank)
{
flag = true;
// table.Rows.Add(dr);
}
}
// }
}
}
}
else
{
flag = true;
}
}
#endregion
#region KT điều kiện seniority == && maxSeniority != null
if (item.MaxSeniority != null && item.Seniority == null)
{
if (monthSeniority <= item.MaxSeniority)
{
if (salaryClassEntity != null)
{
if (workingEntity != null)
{
// if (profileEntity.SalaryClassID != workingEntity.SalaryClassID)
// {
var dateCheckRank = dateCheck.Subtract(workingEntity.DateEffective).TotalDays / 30;
var monthRank = Math.Floor(dateCheckRank);
if (item.TimeOnCurrentRank != null)
{
if (item.TimeOnCurrentRank != null)
{
if (monthRank < item.TimeOnCurrentRank)
{
flag = true;
// table.Rows.Add(dr);
}
}
}
//}
}
}
}
else
{
flag = true;
}
}
#endregion
//#region kiem tra dk Seniority == null và MaxSeniority == null
//if (item.Seniority == null && item.MaxSeniority == null)
//{
// if (salaryClassEntity != null)
// {
// if (workingEntity != null)
// {
// // if (profileEntity.SalaryClassID != workingEntity.SalaryClassID)
// // {
// var dateCheckRank = dateCheck.Subtract(workingEntity.DateEffective).TotalDays / 30;
// var monthRank = Math.Floor(dateCheckRank);
// if (item.TimeOnCurrentRank != null)
// {
// if (item.TimeOnCurrentRank != null)
// {
// if (monthRank < item.TimeOnCurrentRank)
// {
// flag = true;
// // table.Rows.Add(dr);
// }
// }
// }
// //}
// }
// }
//}
//#endregion
if (lstTraineeByProfileID.Count > 0)
{
foreach (var traineeEntity in lstTraineeByProfileID)
{
// dr[Tra_ReportTraineeJoinCourseEntity.FieldNames.Note] = traineeEntity.TeacherComment != null ? traineeEntity.TeacherComment : string.Empty;
double monthCourse = Math.Floor(dateCheck.Subtract(traineeEntity.EndDate.Value).TotalDays / 30);
var traineeInRequirementDetail = lstRequirementDetail.Where(s => s.CourseID == traineeEntity.ID && traineeEntity.ID == s.ProfileID && s.YearAnalyze != null && s.YearAnalyze.Value.Year == dateCheckConfig.Year).ToList();
if (traineeInRequirementDetail.Count >= 1)
{
flag = true;
continue;
}
if (traineeEntity.EndDate == null)
{
flag = true;
continue;
}
if (item.DurationMinCourse != null)
{
if (monthCourse < item.DurationMinCourse)
{
flag = true;
continue;
}
}
}
}
else
{
var traineeInRequirementDetail = lstRequirementDetail.Where(s => s.CourseID == item.ID && profileEntity.ID == s.ProfileID && s.YearAnalyze != null && s.YearAnalyze.Value.Year == dateCheckConfig.Year).ToList();
if (traineeInRequirementDetail.Count >= 1)
{
flag = true;
continue;
}
}
if (flag == false)
{
if (salaryClassEntity != null)
{
if (workingEntity != null)
{
// if (profileEntity.SalaryClassID != workingEntity.SalaryClassID)
// {
var dateCheckRank = dateCheck.Subtract(workingEntity.DateEffective).TotalDays / 30;
var monthRank = Math.Floor(dateCheckRank);
if (item.TimeOnCurrentRank != null)
{
if (item.TimeOnCurrentRank != null)
{
if (monthRank >= item.TimeOnCurrentRank)
{
flag = true;
table.Rows.Add(dr);
}
}
}
else
{
table.Rows.Add(dr);
}
// }
}
}
}
}
}
}
return table;
}
public DataTable GetReportHCGender(DateTime dateSearch, List<Guid?> lstjobTitles, Guid orgID, Guid? orgTypeID, string Gender, bool _isIncludeQuitEmp, bool isCreateTemplate, string userLogin)
{
DataTable table = CreateReportHCGenderSchema(orgID, orgTypeID,userLogin);
if (isCreateTemplate)
{
return table.ConfigTable();
}
string status = string.Empty;
using (var context = new VnrHrmDataContext())
{
DataRow row1 = table.NewRow();
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var serviceProfile = new Hre_ProfileServices();
var baseService = new BaseService();
var lstObjProfileIDs = new List<object>();
var orgsService = new Cat_OrgStructureServices();
var lstallorgs = orgsService.GetDataNotParam<Cat_OrgStructure>(ConstantSql.hrm_cat_sp_get_AllOrg, userLogin, ref status).ToList();
var lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
var lstOrgName = lstallorgs.Where(s => s.ID == orgID).FirstOrDefault();
string male = EnumDropDown.GenderType.E_MALE.ToString();
string female = EnumDropDown.GenderType.E_FEMALE.ToString();
var listorgid = lstorgs.Select(s => new { s.ID, s.OrderNumber, s.Code, s.OrgStructureName }).ToList();
//Xử Lý lấy tất cả nhân viên trong phòng ban đã chọn và group 1 cấp
var orgIDs = string.Empty;
orderNumber = string.Empty;
foreach (var item in listorgid)
{
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
}
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(orderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin ,ref status).Select(s => s.ID).ToList();
List<object> listObj = new List<object>();
listObj.Add(orderNumber);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var lstprofile = GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin ,ref status).ToList();
if (!_isIncludeQuitEmp)
{
lstprofile = lstprofile.Where(s => s.DateQuit == null).ToList();
}
var jobtitleService = new Cat_JobTitleServices();
var lstObjJobtitle = new List<object>();
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(null);
lstObjJobtitle.Add(1);
lstObjJobtitle.Add(100000);
var lstJobtitle = GetData<Cat_JobTitleEntity>(lstObjJobtitle, ConstantSql.hrm_cat_sp_get_JobTitle, userLogin ,ref status).ToList();
if (lstjobTitles != null && lstjobTitles[0] != null && lstjobTitles[0] != Guid.Empty)
{
lstprofile = lstprofile.Where(s => s.JobTitleID != null && lstjobTitles.Contains(s.JobTitleID.Value)).ToList();
lstJobtitle = lstJobtitle.Where(s => lstjobTitles.Contains(s.ID)).ToList();
}
if (lstprofile == null || lstorgs == null)
{
return table;
}
string gender = string.Empty;
if (Gender == male)
{
gender = "Male";
}
else
{
gender = "Female";
}
row1[Hre_ReportHCGenderEntity.FieldNames.OrgStructureName] = lstOrgName == null ? string.Empty : lstOrgName.OrgStructureName + " - " + gender;
table.Rows.Add(row1);
foreach (var item in lstJobtitle)
{
bool addTitle = false;
int totalcount = 0;
var count = 0;
DataRow row = table.NewRow();
var lstJobtitleById = lstJobtitle.Where(s => item.ID == s.ID).FirstOrDefault();
row[Hre_ReportHCGenderEntity.FieldNames.JobTitleName] = lstJobtitleById == null ? string.Empty : lstJobtitleById.JobTitleName;
foreach (var org in listorgid)
{
//xử lý đếm nhân viên của phòng ban con
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(orderNumber);
var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin,ref status).ToList();
if (orgTypeID != null)
{
lstOrgByOrderNumberCount = lstOrgByOrderNumberCount.Where(s => s.OrgStructureTypeID == orgTypeID).ToList();
}
foreach (var orgName in lstOrgByOrderNumberCount)
{
var lstprofilebyOrg = lstprofile.Where(s => s.OrgStructureID != null && orgName.ID == s.OrgStructureID.Value && s.JobTitleID == lstJobtitleById.ID && s.Gender == Gender && s.DateHire != null && s.DateHire.Value <= dateSearch).ToList();
if (lstprofilebyOrg == null)
{
continue;
}
count = lstprofilebyOrg.Count();
totalcount += count;
if (count > 0)
{
var orgStructureName = orgName.OrgStructureName;
if (orgName != null && !string.IsNullOrEmpty(orgName.OrgStructureName) && table.Columns.Contains(orgStructureName))
{
row[orgName.OrgStructureName] = count;
}
row[Hre_ReportHCGenderEntity.FieldNames.HeadCount] = totalcount;
row[Hre_ReportHCGenderEntity.FieldNames.Total] = totalcount;
}
}
}
if (totalcount == 0)
{
continue;
}
table.Rows.Add(row);
}
DataRow datarow = table.NewRow();
datarow[1] = "Total GT";
for (int i = 2; i < table.Columns.Count; i++)
{
int gt = 0;
for (int j = 0; j < table.Rows.Count; j++)
{
var valueRow = table.Rows[j][i].ToString();
if (!string.IsNullOrEmpty(valueRow) && !string.IsNullOrWhiteSpace(valueRow))
{
var value = int.Parse(valueRow);
if (value >= 0)
{
gt += value;
}
}
}
datarow[i] = gt;
}
table.Rows.Add(datarow);
return table.ConfigTable(true);
}
}
public ActionResult ExportProfileDisciplineListByTemplate([DataSourceRequest] DataSourceRequest request, Hre_DisciplineSearchModel model)
{
var ActionService = new ActionService(UserLogin);
string status = string.Empty;
var isDataTable = false;
object obj = new Hre_ProfileModel();
var result = GetListData<Hre_DisciplineModel, Hre_DisciplineEntity, Hre_DisciplineSearchModel>(request, model, ConstantSql.hrm_hr_sp_get_Discipline, ref status);
if (result != null && result.Count > 0)
{
#region lấy Org và OrgType
var orgServices = new Cat_OrgStructureServices();
var lstObjOrg = new List<object>();
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(null);
lstObjOrg.Add(1);
lstObjOrg.Add(int.MaxValue - 1);
var lstOrg = ActionService.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructure, 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 = ActionService.GetData<Cat_OrgStructureTypeEntity>(lstObjOrgType, ConstantSql.hrm_cat_sp_get_OrgStructureType, ref status).ToList();
#endregion
foreach (var item in result)
{
Guid? orgId = item.OrgStructureID1;
var org = lstOrg.FirstOrDefault(s => s.ID == item.OrgStructureID1);
var orgBranch = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_BRANCH, lstOrg, lstOrgType);
var orgGroup = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_GROUP, lstOrg, lstOrgType);
var orgOrg = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_DEPARTMENT, lstOrg, lstOrgType);
var orgTeam = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_TEAM, lstOrg, lstOrgType);
var orgSection = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_SECTION, lstOrg, lstOrgType);
var orgDivision = LibraryService.GetNearestParentEntity(orgId, OrgUnit.E_DIVISION, lstOrg, lstOrgType);
item.BranchName = orgBranch != null ? orgBranch.OrgStructureName : string.Empty;
item.GroupName = orgGroup != null ? orgGroup.OrgStructureName : string.Empty;
item.DepartmentName = orgOrg != null ? orgOrg.OrgStructureName : string.Empty;
item.TeamName = orgTeam != null ? orgTeam.OrgStructureName : string.Empty;
item.SectionName = orgSection != null ? orgSection.OrgStructureName : string.Empty;
item.DivisionName = orgDivision != null ? orgDivision.OrgStructureName : string.Empty;
item.DisciplineCount = result.Where(s => s.ProfileID == item.ProfileID).Count();
}
}
//if (model.IsCreateTemplateForDynamicGrid)
//{
// obj = result;
// isDataTable = false;
//}
if (model != null && model.IsCreateTemplate)
{
var path = Common.GetPath("Templates");
ExportService exportService = new ExportService();
ConfigExport cfgExport = new ConfigExport()
{
Object = new Hre_DisciplineModel(),
FileName = "Hre_Discipline",
OutPutPath = path,
// HeaderInfo = listHeaderInfo,
DownloadPath = Hrm_Main_Web + "Templates",
IsDataTable = isDataTable
};
var str = exportService.CreateTemplate(cfgExport);
return Json(str);
}
if (model.ExportId != Guid.Empty)
{
var fullPath = ExportService.Export(model.ExportId, result, null, model.ExportType);
return Json(fullPath);
}
return Json(result.ToDataSourceResult(request));
}
public DataTable GetReportHCSales(DateTime DateSearch, Guid orgID, bool isCreateTemplate,string userLogin)
{
Hre_ReportServices reportServices = new Hre_ReportServices();
DataTable table = CreateReportHCSalesSchema(userLogin);
string status = string.Empty;
using (var context = new VnrHrmDataContext())
{
if (isCreateTemplate)
{
return table.ConfigTable();
}
string key1 = "SaleIn";
string key2 = "SaleOut";
var count = 1;
var unitOfWork = (IUnitOfWork)(new UnitOfWork(context));
var serviceProfile = new Hre_ProfileServices();
var baseService = new BaseService();
var orgsService = new Cat_OrgStructureServices();
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 lstorgs = lstallorgs.Where(s => s.ParentID == orgID).ToList();
var lstOrgName = lstallorgs.Where(s => s.ID == orgID).FirstOrDefault();
var listorgid = lstorgs.Select(s => new { s.ID, s.OrderNumber, s.Code, s.OrgStructureName }).ToList();
//Xử Lý lấy tất cả nhân viên trong phòng ban đã chọn và group 1 cấp
var orgIDs = string.Empty;
orderNumber = string.Empty;
foreach (var item in listorgid)
{
orderNumber += item.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, item.ID);
}
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumber = new List<object>();
lstObjOrgByOrderNumber.Add(orderNumber);
var lstOrgByOrderNumber = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumber, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin, ref status).Select(s => s.ID).ToList();
List<object> listObj = new List<object>();
listObj.Add(orderNumber);
listObj.Add(string.Empty);
listObj.Add(string.Empty);
var lstprofile = reportServices.GetData<Hre_ProfileEntity>(listObj, ConstantSql.hrm_hr_sp_get_ProfileIdsByOrg, userLogin, ref status).ToList();
var salesTypeServices = new Eva_SalesTypeServices();
var lstObjSalesType = new List<object>();
lstObjSalesType.Add(null);
lstObjSalesType.Add(1);
lstObjSalesType.Add(int.MaxValue);
var lstSalesType = salesTypeServices.GetData<Eva_SalesTypeEntity>(lstObjSalesType, ConstantSql.hrm_eva_sp_get_SalesType, userLogin, ref status).Select(s => s.Code).ToList();
var saleEvaluationServices = new Eva_SaleEvaluationServices();
var lstObjSaleEvaluation = new List<object>();
lstObjSaleEvaluation.Add(null);
lstObjSaleEvaluation.Add(null);
lstObjSaleEvaluation.Add(null);
lstObjSaleEvaluation.Add(1);
lstObjSaleEvaluation.Add(int.MaxValue);
var lstSaleEvaluation = saleEvaluationServices.GetData<Eva_SaleEvaluationEntity>(lstObjSaleEvaluation, ConstantSql.hrm_eva_sp_get_SaleEvaluation, userLogin, ref status).ToList();
foreach (var org in listorgid)
{
DataRow row = table.NewRow();
row[Hre_ReportHCSalesEntity.FieldNames.CodeEmp] = org == null ? string.Empty : org.OrgStructureName;
//xử lý đếm nhân viên của phòng ban con
orderNumber = string.Empty;
orderNumber += org.OrderNumber.ToString() + ",";
getChildOrgStructure(lstallorgs, org.ID);
if (orderNumber.IndexOf(',') > 0)
orderNumber = orderNumber.Substring(0, orderNumber.Length - 1);
var lstObjOrgByOrderNumberCount = new List<object>();
lstObjOrgByOrderNumberCount.Add(orderNumber);
var lstOrgByOrderNumberCount = orgsService.GetData<Cat_OrgStructure>(lstObjOrgByOrderNumberCount, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, userLogin, ref status).ToList();
//if(count <= 0){
// continue;
//}
bool addTitle = false;
foreach (var item in lstOrgByOrderNumberCount)
{
var lstprofilebyOrg = lstprofile.Where(s => s.OrgStructureID != null && item.ID == s.OrgStructureID.Value && s.DateHire != null && s.DateHire.Value.Year <= DateSearch.Year && s.DateQuit == null).Select(s => s.ID).ToList();
if (lstprofilebyOrg == null && lstprofilebyOrg.Count <= 0)
{
continue;
}
var lstSaleEvaluations = lstSaleEvaluation.Where(s => lstprofilebyOrg.Contains(s.ProfileID.Value) && s.Year != null && s.Year.Value.Year <= DateSearch.Year).ToList();
count = 0;
count = lstSaleEvaluation.Count;
foreach (var sale in lstSaleEvaluations)
{
var lstProfileResult = lstprofile.Where(s => s.ID == sale.ProfileID.Value).FirstOrDefault();
var orgName = reportServices.GetParentOrg(lstallorgs, lstOrgType, lstProfileResult.OrgStructureID);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
if (orgName.Count < 3)
{
orgName.Insert(0, string.Empty);
}
}
DataRow row1 = table.NewRow();
row1[Hre_ReportHCSalesEntity.FieldNames.CodeEmp] = lstProfileResult == null ? string.Empty : lstProfileResult.CodeEmp;
row1[Hre_ReportHCSalesEntity.FieldNames.ProfileName] = lstProfileResult == null ? string.Empty : lstProfileResult.ProfileName;
row1[Hre_ReportHCSalesEntity.FieldNames.PositionName] = lstProfileResult == null ? string.Empty : lstProfileResult.PositionName;
row1[Hre_ReportHCSalesEntity.FieldNames.JobTitleName] = lstProfileResult == null ? string.Empty : lstProfileResult.JobTitleName;
row1[Hre_ReportHCSalesEntity.FieldNames.ProfileSupervisorName] = lstProfileResult == null ? string.Empty : lstProfileResult.SupervisorName;
row1[Hre_ReportHCSalesEntity.FieldNames.DateHire] = lstProfileResult == null ? string.Empty : lstProfileResult.DateHire.Value.ToShortDateString();
row1[Hre_ReportHCSalesEntity.FieldNames.Channel] = orgName[2];
row1[Hre_ReportHCSalesEntity.FieldNames.Region] = orgName[1];
row1[Hre_ReportHCSalesEntity.FieldNames.Area] = orgName[0];
row1[Hre_ReportHCSalesEntity.FieldNames.WorkingPlaceName] = lstProfileResult == null ? string.Empty : lstProfileResult.WorkPlaceName;
for (int i = 1; i <= 12; i++)
{
var saleCode = sale.SalesTypeCode + "_" + i;
if (sale.SalesTypeCode == key1 && sale.Year != null && sale.Year.Value.Month == i)
{
row1[Hre_ReportHCSalesEntity.FieldNames.TargetSalesIn + "_" + i] = sale.TagetNumber;
row1[Hre_ReportHCSalesEntity.FieldNames.ActSalesIn + "_" + i] = sale.ResultNumber;
row1[Hre_ReportHCSalesEntity.FieldNames.SalesIn + "_" + i] = sale.ResultPercent != null ? sale.ResultPercent.Value.ToString(ConstantFormat.HRM_Format_Number_Double2) : null;
}
if (sale.SalesTypeCode == key2 && sale.Year != null && sale.Year.Value.Month == i)
{
row1[Hre_ReportHCSalesEntity.FieldNames.TargetSalesOut + "_" + i] = sale.TagetNumber;
row1[Hre_ReportHCSalesEntity.FieldNames.ActSalesOut + "_" + i] = sale.ResultNumber;
row1[Hre_ReportHCSalesEntity.FieldNames.SalesOut + "_" + i] = sale.ResultPercent != null ? sale.ResultPercent.Value.ToString(ConstantFormat.HRM_Format_Number_Double2) : null;
}
if (lstSaleEvaluation.Where(s => s.Year != null).Select(s => s.Year.Value.Month).ToList().Contains(i) && table.Columns.Contains(saleCode))
{
row1[sale.SalesTypeCode + "_" + i] = sale.ResultPercent != null ? sale.ResultPercent.Value.ToString(ConstantFormat.HRM_Format_Number_Double2) : null;
}
}
if (!addTitle)
{
table.Rows.Add(row);
addTitle = true;
}
table.Rows.Add(row1);
}
}
}
return table.ConfigTable(true);
}
}
/// <summary>
/// [Chuc.Nguyen] - Lấy danh sách phòng ban
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
///
//[OutputCache(NoStore = true, Duration = 0)]
public JsonResult GetOrgStructureTree(Guid? id, string UserName)
{
var service = new Cat_OrgStructureServices();
string status = string.Empty;
var listModel = new List<CatOrgStructureModel>();
if (HttpContext.Cache[UserName] == null)
{
List<Object> listObject = new List<object>();
listObject.Add(UserName != null ? UserName : "");
var listEntity = service.GetData<Cat_OrgStructureTreeViewEntity>(listObject, ConstantSql.hrm_cat_sp_get_OrgStructure_Data, UserLogin, ref status);
#region Xử lý phân quyền cho cây phòng ban
if (UserName == string.Empty || UserName == Common.UserNameSystem)
{
listEntity.ForEach(m => m.IsShow = true);
}
#endregion
if (listEntity != null)
{
listModel = listEntity.Translate<CatOrgStructureModel>();
//HttpContext.Cache["List_OrgStructureTreeView"] = listModel;
//HttpContext.Cache.Add(UserName, listModel, null, DateTime.Now.AddDays(30), TimeSpan.Zero, CacheItemPriority.Default, null);
HttpContext.Cache[UserName] = listModel;
}
}
else
{
listModel = HttpContext.Cache[UserName] as List<CatOrgStructureModel>;
}
//lấy quyền phòng ban theo user
var orgStructure = from e in listModel
where (id.HasValue ? e.ParentID == id : e.ParentID == null)
select new
{
id = e.ID,
Name = e.OrgStructureName,
hasChildren = listModel.Any(ch => ch.ParentID == e.ID),
IconPath = ConstantPathWeb.HrWebUrl + ConstantPath.IconPath + (e.Icon ?? "icon1.png"),
OrderNumber = e.OrderNumber,
Code = e.Code,
IsShow = e.IsShow
};
return Json(orgStructure.OrderBy(m => m.Code), JsonRequestBehavior.AllowGet);
}
