如何在Winforms和EntityFramework中生成枢轴DataGridView[英] How to generate a Pivot Datagridview in WinForms and EntityFramework

问题描述

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

The Pivot Dataviewgrid should look like this:

This is what I have now:

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

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

Item 123 ReturnReason {0, 0, 0, 0, 1, 16} Qty {1, 1, 2, 1, 5, 1}

What means, that for the reason 0 there are 4 quantities. It should look like

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

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

Now the result is as required.

其他推荐答案

this is not a recipe but only some advices to solve your problem.

1) ReturnReason should be a class:

public class ReturnReason
{
   public int Code { get; set; }
   public string Description {get; set;}
}

2) Create a ReturnReasonList class. Implement it as a singleton and use it to provide Reasons:

public sealed class ReturnReasonList
{
   protected List<ReturnReason> pInstance = new List<ReturnReason> {
      { .Code=1, .Description="whatever reason 1" },
      { .Code=2, .Description="whatever reason 2" },
      ... 
      { .Code=n, .Description="whatever reason n" }};


   private ReturnReasonList() {}

   public List<Returnreason> pInstance
   {
      get
      {
         return pInstance;
      }
   }
}

3) Consider what you really need for each pair (ItemNo, ReturnReason):

public class ReturnedQuantity 
{
   int ItemNumber { get; set; }
   Returnreason { get; set;}
   int Quantity {get; set;}
}

4) Now you have to create a List from your return data:

var query = _data.RepositoryRetouren.GetAll<ReturnQty>();
List<ReturnedQuantity> returns = from r in query
                                 join rs in ReturnReasonList.Instance
                                 on r.ReturnReason = rs.Code
                                 group by rs, r.ItemNo
                                 select new ReturnedQuantity() {.ItemNo = r.ItemNo, .Reason= rs, .Quantity = SUM(r.Qty) };

5) Fill your DataGridView columns collections with items in ReturnReasonList.Items

6) Fill your DataGridView rows collection with items in your article list.

7) Fill your DataGridView cells with the quantities.