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); } }