DataTable CreateReportHCSalesSchema(string userLogin)
{
using (var context = new VnrHrmDataContext())
{
string status = string.Empty;
DataTable datatable = new DataTable("Hre_ReportHCSalesEntity");
string key1 = "SaleIn";
string key2 = "SaleOut";
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.CodeEmp);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.ProfileName);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.PositionName);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.JobTitleName);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.ProfileSupervisorName);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.DateHire, typeof(DateTime));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.Channel);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.Region);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.Area);
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.WorkingPlaceName);
var salesTypeServices = new Eva_SalesTypeServices();
var lstObjSalesType = new List<object>();
lstObjSalesType.Add(null);
lstObjSalesType.Add(1);
lstObjSalesType.Add(int.MaxValue - 1);
var lstSalesType = salesTypeServices.GetData<Eva_SalesTypeEntity>(lstObjSalesType, ConstantSql.hrm_eva_sp_get_SalesType, userLogin, ref status).Select(s => s.Code).ToList();
if (lstSalesType != null && lstSalesType.Count > 0)
{
lstSalesType = lstSalesType.Where(s => s != key1 && s != key2).ToList();
}
for (int i = 1; i <= 12; i++)
{
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.TargetSalesIn + "_" + i, typeof(double));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.ActSalesIn + "_" + i, typeof(double));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.SalesIn + "_" + i, typeof(double));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.TargetSalesOut + "_" + i, typeof(double));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.ActSalesOut + "_" + i, typeof(double));
datatable.Columns.Add(Hre_ReportHCSalesEntity.FieldNames.SalesOut + "_" + i, typeof(double));
foreach (var item in lstSalesType)
{
datatable.Columns.Add(item + "_" + i, typeof(double));
}
}
return datatable;
}
}
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);
}
}