110,534
社区成员
发帖
与我相关
我的任务
分享
public List<KPISupplierReportShow> GetKPIReport
(bool? purchaseStatus,List<string> facs, List<string> status, List<string> types, string searchKey, string startDate, string endDate)
{
using (SRMDatabaseEntities entity = new SRMDatabaseEntities())
{
DateTime start = string.IsNullOrEmpty(startDate) ? DateTime.MinValue : Convert.ToDateTime(startDate);
DateTime end = string.IsNullOrEmpty(endDate) ? DateTime.MaxValue : Convert.ToDateTime(endDate);
var model = from p in entity.KPISupplierReport
join q in entity.SupplierInfoReport on p.SupplierCode equals q.SupplierCode
join j in entity.SystemUser on p.CreatedUser.ToUpper() equals j.UserId.ToUpper() into temp
from k in temp.DefaultIfEmpty()
where
(
status.Contains(p.Status)
&& facs.Contains(p.FactoryCode)
&& types.Contains(p.ReportCycle)
&& (purchaseStatus == null || q.PurchaseStatus == purchaseStatus)
&& (p.CreatedDate > start && p.CreatedDate < end)
&&
(string.IsNullOrEmpty(searchKey)
|| p.SupplierCode.Contains(searchKey)
|| p.SupplierName.Contains(searchKey)
|| p.ReportNumber.Contains(searchKey)
|| k.UserName.Contains(searchKey)
|| p.CreatedUser.Contains(searchKey)
|| p.ReportName.Contains(searchKey)
)
)
orderby p.Id descending
select new
{
q.PurchaseStatus,
p.Id,
p.ApprovalStatus,
p.CreatedDate,
k.UserName,
p.FactoryCode,
p.FactoryName,
p.FeedbackDate,
p.KPIMonthEnd,
p.KPIMonthStart,
p.KPIYear,
p.MaterielCode,
p.ReportCycle,
p.ReportName,
p.ReportNumber,
p.Score,
p.SendDate,
p.Status,
p.SupplierCode,
p.SupplierName
};
var modelList = model.ToList();
var numberList = modelList.Select(i => i.ReportNumber).ToList();
var itemList = entity.KPISupplierItem.Where(i => numberList.Contains(i.ReportNumber)).ToList();
//上面部分取数都是很快的,就是下面这部分要进行行列转换,
//把明细里对应的列写到头的行里,测试用10000条记录,这个foreach大概要2分多钟,
//除了修改超时时间限制,还有别的办法么?
List<KPISupplierReportShow> reportList = new List<KPISupplierReportShow>();
KPISupplierReportShow rshow;
List<KPISupplierItem> tempItemList;
KPISupplierItem tempItem;
foreach (var kpi in modelList)
{
rshow = new KPISupplierReportShow()
{
PurchaseStatus=kpi.PurchaseStatus? "激活":"暂停",
ApprovalStatus = EnumHelper.GetEnumDescription<WorkflowStatus>(EnumHelper.GetEnum<WorkflowStatus>(kpi.ApprovalStatus)),
CreatedDate = kpi.CreatedDate.ToString("yyyy-MM-dd"),
CreatedUser = kpi.UserName,
FactoryCode = kpi.FactoryCode,
FactoryName = kpi.FactoryName,
FeedbackDate = kpi.FeedbackDate,
Id = kpi.Id,
KPIMonthEnd = kpi.KPIMonthEnd,
KPIMonthStart = kpi.KPIMonthStart,
KPIYear = kpi.KPIYear,
MaterielCode = kpi.MaterielCode,
ReportCycle = kpi.ReportCycle,
ReportName = kpi.ReportName,
ReportNumber = kpi.ReportNumber,
Score = kpi.Score,
SendDate = kpi.SendDate,
Status = EnumHelper.GetEnumDescription<ExaminationStatus>(EnumHelper.GetEnum<ExaminationStatus>(kpi.Status)),
SupplierCode = kpi.SupplierCode,
SupplierName = kpi.SupplierName
};
tempItemList = itemList.Where(t => t.ReportNumber == kpi.ReportNumber).ToList();
//品质
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key1));
if (tempItem != null)
{
rshow.Penalty1 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark1 = tempItem.Reason;
}
//交付
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key2));
if (tempItem != null)
{
rshow.Penalty2 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark2 = tempItem.Reason;
}
//服务
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key3));
if (tempItem != null)
{
rshow.Penalty3 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark3 = tempItem.Reason;
}
//附加运费
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key4));
if (tempItem != null)
{
rshow.Penalty4 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark4 = tempItem.Reason;
}
//体系
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key5));
if (tempItem != null)
{
rshow.Penalty5 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark5 = tempItem.Reason;
}
//成本
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key6));
if (tempItem != null)
{
rshow.Penalty6 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark6 = tempItem.Reason;
}
//技术
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key7));
if (tempItem != null)
{
rshow.Penalty7 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark7 = tempItem.Reason;
}
//----add by chenyuyi on 2018-09-10 begin----
//加分项
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key8));
if (tempItem != null)
{
rshow.Penalty8 = 0 - (tempItem.TargetScore - (tempItem.Score.HasValue ? tempItem.Score.Value : 0));
rshow.PenaltyRemark8 = tempItem.Reason;
}
//风险
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key9));
if (tempItem != null)
{
rshow.PenaltyRemark9 = tempItem.Reason;
}
//红线
tempItem = tempItemList.FirstOrDefault(t => t.Title.Contains(KPISupplierReportShow.Key10));
if (tempItem != null)
{
rshow.PenaltyRemark10 = tempItem.Reason;
}
//----add by chenyuyi on 2018-09-10 end----
reportList.Add(rshow);
}
return reportList;
}
}