Arturo Martinez Arturo Martinez - 27 days ago 6
C# Question

C# List with Unique data based on the Date

I have a table that has the sales of the current month in order by how many time a dish has being sold in each day of the month but in this order:

[Dishes]
Fecha | Pdv | Pla_ID | Quantity | Price | Total |
===============================================================================
2016-11-03 00:00:00.000 REST 65 4 50.00 200.00
2016-11-05 00:00:00.000 REST 65 1 50.00 50.00
2016-11-07 00:00:00.000 REST 65 7 50.00 350.00
2016-11-03 00:00:00.000 REST 70 6 100.00 600.00
2016-11-04 00:00:00.000 REST 70 7 100.00 700.00
2016-11-05 00:00:00.000 REST 70 1 100.00 100.00
2016-11-06 00:00:00.000 REST 70 3 100.00 300.00
2016-11-07 00:00:00.000 REST 70 1 100.00 100.00


Im looking for a way to save the records into a list but with the date being a coulmn for each day of the month, and the Quantity values getting asigned to the day in wich they where sold like this
In this Order:

[Result]
|PDV|Pla_Id|Price|1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|Quantity_Total|Total|
========================================================================================================================
|REST| 65 | 50 |0|0|4|0|1|0|7|0|0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 12 | 600 |
|REST| 70 | 50 |0|0|6|7|1|3|1|0|0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 18 | 1800 |


"Pla_Id=65"
Sold 4 times the 3rd of november , 1 time the fifth and 7 tiemes the seventh total of 12 times in the month of november

"Pla_id=70"
Sold 6 times the 3rd of november , 7 time the fourth, 1 time the fifth, 3 times the six and 1 time the seventh giving a total of 18 times in the month of november

So my list would look like this:

["REST","65","50","0","0","4","0","1","0","7","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","12","$600.00"]
["REST","70","100","0","0","6","7","1","3","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","18","$1800.00"]


This is my current Aproach

foreach (var item in db.Dishes)
{
List<string> listItem = new List<string>();
decimal costo = 0;

for (int i = 1; i <= 31; i++)
{
var value = 0;
if (item.Fecha.Day == i) { value = (int)item.Quantity; costo = costo + item.Total; }
}

listItem.Add(item.Pdv);
listItem.Add(item.Pla_ID);
listItem.Add(item.Price);


for (int i = 1; i <= 30; i++)
{
var value = 0;
int month = item.Fecha.Month;
if (item.Fecha.Day == i)
{
value = (int)item.Quantity; listItem.Add(value.ToString());
}
value = 0;
listItem.Add(value.ToString());
}

listItem.Add((item.Quantity).ToString());

listItem.Add(item.price * item.Quantity);

}


however this give methe quantity for each dish asigned to each day but in a diferent line giving me a 3 result lines for "Pla_Id=65", and 5 Lines for "Pla_Id=70" each has the quantity on their corresponding day but it should be only 1 line for each diferent dish not for each diferent day,

How can i acomplish this?

Answer

I have created a following model to Answer this question:

// Main model Dishes class

public class Dishes
{
    public DateTime Fecha { get; set;}

    public string Pdv { get; set;}

    public int Pla_ID { get; set;}

    public int Quantity { get; set;}

    public double Price { get; set;}

    public double Total { get; set;}    
}

// Processing Logic

  • Create a Grouping based on fields Pdv, Pla_Id and Price as follows:

      var dishesGrouping = 
      dishes.GroupBy(d => new {d.Pdv,d.Pla_ID,d.Price});
    

Based on the understanding that Price will also remain constant for a combination of Pdv, Pla_Id, as I can see in your data or otherwise Price need to move out of the Key and would be part of Day wise structure like Quantity.

  • Now create a following DataStructure

    var dishGroupingDictionary = dishesGrouping
                             .ToDictionary(dishGrp => dishGrp.Key, dishGrp =>
                             {
                                var dishDictionary = new Dictionary<int, int>();
    
                                for(int i=1; i<=30;i++)
                                   dishDictionary[i] = 0;
    
                                 foreach (var grp in dishGrp)
                                 {
                                    dishDictionary[grp.Fecha.Day] = 
                                    grp.Quantity;
                                 }
    
                                return dishDictionary;
                             });
    
  • This will create data in the format: Dictionary<Anonymous(string,int,double),Dictionary<int,int>>

  • Now you have a structure will all the information required for further processing to provide an example:

  • Outer Dictionary has a Key as combination of Pdv,Pla_ID and Price, which is the unique combination required

  • Inner Dictionary contains Day Wise data for Quantity, All the Dates are filled with 0 default and is updated for the available dates in each Group Key.

  • Now each Key-Value Pair would contain the Unique combination of Pdv,Pla_ID and Price, Day wise quantities can be added via inner Dictionary and Price can be multiplied for total. All the remaining Days shall be 0 by default.

// Following is the simple data printing logic. You may even plan to store it in a different and flatter model for quick usage, but its preferred to keep Day wise data in a collection like Dictionary, since its easy to extend and modify, which is not simple for model.

foreach (var kv in dishGroupingDictionary)
{
    Console.WriteLine("Pdv:{0},Pla_Id:    
    {1},Price{2}",kv.Key.Pdv,kv.Key.Pla_ID,kv.Key.Price);

    int totalQuantity = kv.Value.Sum(x => x.Value);

    Console.WriteLine("Total Quantity: {0}", totalQuantity);

    double totalPrice = totalQuantity * kv.Key.Price;

    Console.WriteLine("Total Price: {0}", totalPrice);

  // Day wise data printing:        
  foreach(var kvChild in kv.Value)
    Console.WriteLine("Day:{0},Quantity:{1}",kvChild.Key,kvChild.Value);        

}