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