void QueryCertificates(DiagramViewModel model)
{
var planItems = new List <HumDistributionPlanItem>();
if (model.AreaId != 0)
{
planItems = db.HumDistributionPlanItems.Where(pi => pi.AreaId == model.AreaId).Include(x => x.HumDistributionPlan).ToList();
}
else
{
planItems = db.HumDistributionPlanItems.ToList();
}
var planItemIds = planItems.Select(x => x.Id).ToList();
var reportItems = db.ReportItems.Where(x => planItemIds.Contains(x.HumDistributionPlanItemId ?? 0)).ToList();
foreach (var productItems in planItems.GroupBy(x => x.ProductId))
{
var productName = db.Products.Find(productItems.Key).Name;
foreach (var unitTypeItems in productItems.GroupBy(x => x.UnitTypeId))
{
var unitTypeName = db.UnitTypes.Find(unitTypeItems.Key).Name;
var modelItem = new BarDiagramItem
{
CategoryName = string.Format("{0} ({1})", productName, unitTypeName.ToLower())
};
var certificates = unitTypeItems.Select(x => x.HumDistributionPlan.CertificateNo ?? "").Distinct().Count();
modelItem.Received = certificates;
modelItem.Balance = 0;
model.BarDiagramItems.Add(modelItem);
//Pie items
model.PieDiagramItems.Add(new PieDiagramItem
{
Name = modelItem.CategoryName,
Amount = modelItem.Received
});
}
}
model.BarDiagramItems = model.BarDiagramItems.OrderBy(x => x.CategoryName).ToList();
}
void QueryProducts(DiagramViewModel model)
{
var planItems = new List <HumDistributionPlanItem>();
var planItemsQuery = db.HumDistributionPlanItems as IQueryable <HumDistributionPlanItem>;
/*if (model.AreaId != 0)
* {
* planItems = (from pi in db.HumDistributionPlanItems
* where pi.AreaId == model.AreaId
* select pi).ToList();
* planItemsQuery = planItemsQuery.Where(x => x.AreaId == model.AreaId);
* }
* else
* planItems = db.HumDistributionPlanItems.ToList();*/
if (model.AreaId != 0)
{
planItemsQuery = planItemsQuery.Where(x => x.AreaId == model.AreaId);
}
if (model.From != null)
{
planItemsQuery = planItemsQuery.Where(x => x.HumDistributionPlan.Date >= model.From);
}
if (model.To != null)
{
planItemsQuery = planItemsQuery.Where(x => x.HumDistributionPlan.Date <= model.To);
}
planItems = planItemsQuery.ToList();
var planItemIds = planItems.Select(x => x.Id).ToList();
var reportItems = db.ReportItems.Where(x => planItemIds.Contains(x.HumDistributionPlanItemId ?? 0)).ToList();
foreach (var productItems in planItems.GroupBy(x => x.ProductId))
{
var productName = db.Products.Find(productItems.Key).Name;
foreach (var unitTypeItems in productItems.GroupBy(x => x.UnitTypeId))
{
var unitTypeName = db.UnitTypes.Find(unitTypeItems.Key).Name;
var modelItem = new BarDiagramItem
{
CategoryName = string.Format("{0} ({1})", productName, unitTypeName.ToLower())
};
modelItem.Received = (int)unitTypeItems.Sum(x => x.Amount ?? 0.0);
var unitTypeItemIds = unitTypeItems.Select(x => x.Id);
var unitReportItems = reportItems.Where(x => unitTypeItemIds.Contains(x.HumDistributionPlanItemId ?? 0));
if (unitReportItems != null && unitReportItems.Count() > 0)
{
modelItem.Issued = (int)unitReportItems.Sum(x => x.FactAmount);
}
model.BarDiagramItems.Add(modelItem);
//Pie items
model.PieDiagramItems.Add(new PieDiagramItem
{
Name = modelItem.CategoryName,
Amount = modelItem.Received
});
}
}
model.BarDiagramItems = model.BarDiagramItems.OrderBy(x => x.CategoryName).ToList();
}
