private void Bind() { try { int?Id = null; if (!string.IsNullOrEmpty(btnVendor.Tag == null ? null : btnVendor.Tag.ToString())) { Id = int.Parse(btnVendor.Tag.ToString()); } QueryAssCusandVenAnalysisInputDto inputDto = new QueryAssCusandVenAnalysisInputDto { StarTime = startTime, EndTime = endTime, ID = Id }; var chartInfo = _autofacConfig.AssAnalysisService.GetAssVendorAnalysisChart(inputDto); bc.DataSource = chartInfo; bc.DataBind(); var rows = _autofacConfig.AssAnalysisService.GetAssVendorAnalysis(inputDto); lv.DataSource = rows; lv.DataBind(); } catch (Exception ex) { Toast(ex.Message); } }
public List <BarChartDto> GetAssVendorAnalysisChart(QueryAssCusandVenAnalysisInputDto inputDto) { var types = (from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 1 select new { Level1 = type1.TYPEID, Level2 = "", Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 2 select new { Level1 = type1.PARENTTYPEID, Level2 = type1.TYPEID, Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes join type2 in SMOWMSDbContext.AssetsTypes on type1.PARENTTYPEID equals type2.TYPEID where type1.TLEVEL == 3 select new { Level1 = type2.PARENTTYPEID, Level2 = type1.PARENTTYPEID, Level3 = type1.TYPEID }); var vendorlist = _vendorReposity.GetAll(); if (inputDto.ID != null) { vendorlist = vendorlist.Where(a => a.VID == inputDto.ID); } var poRowResult = from assTemplate in SMOWMSDbContext.AssTemplates from type in types from type2 in SMOWMSDbContext.AssetsTypes join porow in SMOWMSDbContext.AssPurchaseOrderRows on assTemplate.TEMPLATEID equals porow.TEMPLATEID from vendor in vendorlist from po in SMOWMSDbContext.AssPurchaseOrders where ((assTemplate.TYPEID == type.Level1 && type.Level2 == "") || (assTemplate.TYPEID == type.Level2 && type.Level3 == "") || assTemplate.TYPEID == type.Level3) && porow.CREATEDATE >= inputDto.StarTime && porow.CREATEDATE <= inputDto.EndTime && po.STATUS > 0 && po.VID == vendor.VID && po.POID == porow.POID && type.Level1 == type2.TYPEID select new BarChartDto() { XValue = type2.NAME, Series = vendor.NAME, YValue = porow.QUANTPURCHASED }; var poRowfresult = from assQuantDto in poRowResult group assQuantDto by new { assQuantDto.XValue, assQuantDto.Series } into quant select new BarChartDto() { XValue = quant.Key.XValue, Series = quant.Key.Series, YValue = quant.Sum(a => a.YValue) }; var t3 = poRowfresult.ToList(); var t4 = new List <BarChartDto>(); if (inputDto.ID == null) { //如果是全部,则需要补全数量为0的项 //得到所有需要显示的一级资产类型 var allLevel1 = from assTemplate in SMOWMSDbContext.AssTemplates from type in types from type2 in SMOWMSDbContext.AssetsTypes join porow in SMOWMSDbContext.AssPurchaseOrderRows on assTemplate.TEMPLATEID equals porow.TEMPLATEID from vendor in vendorlist from po in SMOWMSDbContext.AssPurchaseOrders where ((assTemplate.TYPEID == type.Level1 && type.Level2 == "") || (assTemplate.TYPEID == type.Level2 && type.Level3 == "") || assTemplate.TYPEID == type.Level3) && porow.CREATEDATE >= inputDto.StarTime && porow.CREATEDATE <= inputDto.EndTime && porow.STATUS > 0 && po.VID == vendor.VID && po.POID == porow.POID && type.Level1 == type2.TYPEID select type2.NAME; List <string> typeList = allLevel1.ToList(); //得到符合条件的供应商列表 var allVendor = from assTemplate in SMOWMSDbContext.AssTemplates from type in types from type2 in SMOWMSDbContext.AssetsTypes join porow in SMOWMSDbContext.AssPurchaseOrderRows on assTemplate.TEMPLATEID equals porow.TEMPLATEID from vendor in vendorlist from po in SMOWMSDbContext.AssPurchaseOrders where ((assTemplate.TYPEID == type.Level1 && type.Level2 == "") || (assTemplate.TYPEID == type.Level2 && type.Level3 == "") || assTemplate.TYPEID == type.Level3) && porow.CREATEDATE >= inputDto.StarTime && porow.CREATEDATE <= inputDto.EndTime && porow.STATUS > 0 && po.VID == vendor.VID && po.POID == porow.POID && type.Level1 == type2.TYPEID select vendor.NAME; List <string> vendorLists = allVendor.ToList(); foreach (var vendor in vendorLists) { foreach (var tp in typeList) { var row = t3.Find(a => a.XValue == tp && a.Series == vendor); if (row == null) { BarChartDto dto = new BarChartDto(); dto.XValue = tp; dto.Series = vendor; dto.YValue = 0; t4.Add(dto); } else { t4.Add(row); } } } } else { t4 = t3; } return(t4); }
public List <AssCusAnalysisDto> GetAssCustomerAnalysis(QueryAssCusandVenAnalysisInputDto inputDto) { var types = (from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 1 select new { Level1 = type1.TYPEID, Level2 = "", Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 2 select new { Level1 = type1.PARENTTYPEID, Level2 = type1.TYPEID, Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes join type2 in SMOWMSDbContext.AssetsTypes on type1.PARENTTYPEID equals type2.TYPEID where type1.TLEVEL == 3 select new { Level1 = type2.PARENTTYPEID, Level2 = type1.PARENTTYPEID, Level3 = type1.TYPEID }); var customerlist = _customerReposity.GetAll(); if (inputDto.ID != null) { customerlist = customerlist.Where(a => a.CUSID == inputDto.ID); } var poRowResult = from assTemplate in SMOWMSDbContext.AssTemplates from type in types join sorow in SMOWMSDbContext.AssSalesOrderRows on assTemplate.TEMPLATEID equals sorow.TEMPLATEID from customer in customerlist from so in SMOWMSDbContext.AssSalesOrders where ((assTemplate.TYPEID == type.Level1 && type.Level2 == "") || (assTemplate.TYPEID == type.Level2 && type.Level3 == "") || assTemplate.TYPEID == type.Level3) && sorow.CREATEDATE >= inputDto.StarTime && sorow.CREATEDATE <= inputDto.EndTime && so.STATUS > 0 && assTemplate.TEMPLATEID == sorow.TEMPLATEID && so.CUSID == customer.CUSID && so.SOID == sorow.SOID select new AssCusAnalysisDto() { IMAGE = assTemplate.IMAGE, NAME = assTemplate.NAME, TEMPLATEID = assTemplate.TEMPLATEID, CUSNAME = customer.NAME, QUANTSALED = sorow.QUANTSALED }; var poRowfresult = from assQuantDto in poRowResult group assQuantDto by new { assQuantDto.TEMPLATEID, assQuantDto.IMAGE, assQuantDto.NAME, assQuantDto.CUSNAME } into quant select new AssCusAnalysisDto() { IMAGE = quant.Key.IMAGE, NAME = quant.Key.NAME, TEMPLATEID = quant.Key.TEMPLATEID, CUSNAME = quant.Key.CUSNAME, QUANTSALED = quant.Sum(a => a.QUANTSALED) }; var t3 = poRowfresult.ToList(); return(t3); }
public List <AssVenAnalysisDto> GetAssVendorAnalysis(QueryAssCusandVenAnalysisInputDto inputDto) { var types = (from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 1 select new { Level1 = type1.TYPEID, Level2 = "", Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes where type1.TLEVEL == 2 select new { Level1 = type1.PARENTTYPEID, Level2 = type1.TYPEID, Level3 = "" }).Union(from type1 in SMOWMSDbContext.AssetsTypes join type2 in SMOWMSDbContext.AssetsTypes on type1.PARENTTYPEID equals type2.TYPEID where type1.TLEVEL == 3 select new { Level1 = type2.PARENTTYPEID, Level2 = type1.PARENTTYPEID, Level3 = type1.TYPEID }); var vendorlist = _vendorReposity.GetAll(); if (inputDto.ID != null) { vendorlist = vendorlist.Where(a => a.VID == inputDto.ID); } var poRowResult = from assTemplate in SMOWMSDbContext.AssTemplates from type in types join porow in SMOWMSDbContext.AssPurchaseOrderRows on assTemplate.TEMPLATEID equals porow.TEMPLATEID from vendor in vendorlist from po in SMOWMSDbContext.AssPurchaseOrders where ((assTemplate.TYPEID == type.Level1 && type.Level2 == "") || (assTemplate.TYPEID == type.Level2 && type.Level3 == "") || assTemplate.TYPEID == type.Level3) && porow.CREATEDATE >= inputDto.StarTime && porow.CREATEDATE <= inputDto.EndTime && po.STATUS > 0 && po.VID == vendor.VID && po.POID == porow.POID select new AssVenAnalysisDto() { IMAGE = assTemplate.IMAGE, NAME = assTemplate.NAME, TEMPLATEID = assTemplate.TEMPLATEID, VendorName = vendor.NAME, QUANTPURCHAED = porow.QUANTPURCHASED }; var poRowfresult = from assQuantDto in poRowResult group assQuantDto by new { assQuantDto.TEMPLATEID, assQuantDto.IMAGE, assQuantDto.NAME, assQuantDto.VendorName } into quant select new AssVenAnalysisDto() { IMAGE = quant.Key.IMAGE, NAME = quant.Key.NAME, TEMPLATEID = quant.Key.TEMPLATEID, VendorName = quant.Key.VendorName, QUANTPURCHAED = quant.Sum(a => a.QUANTPURCHAED) }; var t3 = poRowfresult.ToList(); return(t3); }