如何在WinForms和EntityFramework中生成数据透视图Datagridview [英] How to generate a Pivot Datagridview in WinForms and EntityFramework
问题描述
我要从这些实体开始,在WinForms和EntityFramework中生成数据透视表Datagridview:
I want to generate a Pivot Datagridview in WinForms and EntityFramework, starting from theses Entities:
public class Return
{
public Return()
{
this.ReturnQty = new List<ReturnQty>();
}
public int Id { get; set; }
public Nullable<System.DateTime> Date { get; set; }
public Nullable<int> ReturnReason { get; set; }
public Nullable<System.DateTime> Belastungsdatum { get; set; }
public virtual ICollection<ReturnQty> ReturnQty { get; set; }
}
public class ReturnQty
{
public int ID { get; set; }
public int Return_ID { get; set; }
public string ItemNo { get; set; }
public Nullable<decimal> Qty { get; set; }
public virtual Return Return { get; set; }
}
Pivot Dataviewgrid应该如下所示:
The Pivot Dataviewgrid should look like this:
这就是我现在拥有的:
public static class ReturnReasons
{
public static string a { get { return "Grund nicht erfasst"; } }
public static string a1 { get { return "Artikel mangelhaft"; } }
public static string a2 { get { return "Bestellirrtum Kunde"; } }
public static string a3 { get { return "Doppelbestellung Kunde"; } }
public static string a4 { get { return "AV Kunde"; } }
public static string a5 { get { return "Kundenadresse falsch"; } }
public static string a6 { get { return "Kunde nicht erreichbar"; } }
public static string a7 { get { return "Kundenstorno"; } }
public static string a8 { get { return "Nichtgefallen"; } }
public static string a9 { get { return "Transportschaden UPS/DHL/GLS"; } }
public static string a10 { get { return "Transportschaden Spedition"; } }
public static string a11 { get { return "Transportschaden verdeckt"; } }
public static string a12 { get { return "Kommissionierungsfehler"; } }
public static string a13 { get { return "Fehler Auftragserfasung"; } }
public static string a14 { get { return "Lieferverzögerung"; } }
public static string a15 { get { return "Warenrücksendung lt. Vereinbarung"; } }
public static string a16 { get { return "ohne Grund/sonstiges"; } }
}
public class RetourenPivot
{
public string Item{ get; set; }
public IEnumerable<int?> ReturnReason{ get; set; }
public IEnumerable<decimal?> Qty{ get; set; }
}
private void DG_databind()
{
var query = _data.RepositoryRetouren.GetAll<ReturnQty>();
// A Linq to EF which creates a List of concreate class called RetourenPivot.
var queryResults = (from iso in query
orderby iso.ItemNo ascending
group iso by iso.ItemNo into isoGroup
select new RetourenPivot()
{
Item = isoGroup.Key,
ReturnReason = isoGroup.Select(y => y.Return.ReturnReason),
Qty = isoGroup.Select(v => v.Qty)
}).ToList();
// Call a function to create a dynamically created data table with the needed columns
// Create a DataTable as a DataSource for the grid
DataTable dt = new DataTable();
// Create the DataColumns for the data table
DataColumn dc = new DataColumn("Artikel", typeof(string));
dt.Columns.Add(dc);
// Get a list of Distinct Reasons
var ReasonLabel = (from yList in queryResults.Select(Reason => Reason.ReturnReason)
from Reason in yList
select Reason.ToString()).Distinct().ToList();
// Create the DataColumns for the table
ReasonLabel.ForEach(delegate(string Reason)
{
var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p => p.Name == "a" + Reason).ToList()[0].GetValue(null, null).ToString();
dc = new DataColumn(reasonTexts, typeof(string));
dt.Columns.Add(dc);
});
// Populate the rowa of the DataTable
foreach (RetourenPivot rec in queryResults)
{
// The first two columns of the row always has a ISO Code and Description
DataRow dr = dt.NewRow();
dr[0] = rec.Item;
// For each record
var Reason = rec.ReturnReason.ToList();
var Qty = rec.Qty.ToList();
// Because each row may have different reasons I am indexing
// the with the string name
for (int i = 0; i < Qty.Count; i++)
{
var reasonTexts = typeof(ReturnReasons).GetProperties().Where(p => p.Name == "a" + Reason[i].ToString()).ToList()[0].GetValue(null, null).ToString();
dr[reasonTexts] = Qty[i].Value;
}
// Add the DataRow to the DataTable
dt.Rows.Add(dr);
}
// Bind the DataTable to the DataGridView
dataGridViewSummary1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing;
dataGridViewSummary1.ColumnHeadersHeight = 200;
//Connect Grid with DataSource
//this.dataGridViewSummary1.AutoGenerateColumns = true;
this.dataGridViewSummary1.DataSource = dt;
}
问题是,queryResults中的每个RetourenPivot可能具有相同的ReturnReason的多个数量,而我不知道如何对其进行汇总. 例如.现在看起来像
The problem is, that each RetourenPivot in queryResults might have several quantities with the same ReturnReason and I don’t know how to sum them up. E.g. now it looks like
项目123 ReturnReason {0,0,0,0,1,16} 数量{1,1,2,1,5,1}
Item 123 ReturnReason {0, 0, 0, 0, 1, 16} Qty {1, 1, 2, 1, 5, 1}
什么意思,由于0的原因,有4个数量.看起来应该像
What means, that for the reason 0 there are 4 quantities. It should look like
项目123 ReturnReason {0,1,16} 数量{5,5,1}
Item 123 ReturnReason { 0, 1, 16} Qty {5, 5, 1}
推荐答案
我在此回答我自己的问题.我不知道这种解决方案是否是最好的解决方案.
I hereby answer my own question. I don't know if this solution is the best possible one.
我为查询创建了两个临时类
I created two temporary classes for the query
public class RetourenNeu
{
public string Artikel { get; set; }
public int? Retourengrund { get; set; }
public decimal? Anzahl { get; set; }
}
public class RetourenPivot
{
public string Artikel { get; set; }
public IEnumerable<int?> Retourengrund { get; set; }
public IEnumerable<decimal?> Anzahl { get; set; }
}
然后我要问两个问题.第一个查询按ReturnReason和Itemnumber分组并求和.第二个查询按商品编号对第一个查询进行分组.
And than I make two queries. The first query groups by ReturnReason and Itemnumber and sums up the quantities. The second query groups the first query by Itemnumber.
var queryResults = from iso in query
orderby iso.Artikelnummer ascending
group iso by new { iso.Artikelnummer, iso.Retourenkopfdaten.Retourengrund } into isoGroup
select new RetourenNeu()
{
Artikel = isoGroup.Key.Artikelnummer,
Retourengrund = isoGroup.Key.Retourengrund.HasValue ? isoGroup.Key.Retourengrund.Value : 0,
Anzahl = isoGroup.Select(v => v.Anzahl).Sum()
};
var neu = (from n in queryResults
group n by n.Artikel into source
select new RetourenPivot()
{
Artikel = source.Key,
Retourengrund = source.Select(s => s.Retourengrund),
Anzahl = source.Select(s => s.Anzahl)
}).ToList();
现在结果如所要求.
这篇关于如何在WinForms和EntityFramework中生成数据透视图Datagridview的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!