locked
Sum values by state and in the same list RRS feed

  • Question

  • User1670624291 posted

    In my current project I am making a list of orders and in each line I have several colors associated with the order as well as the quantities per color

    public class OrdersColorsViewModel
        {
            public Programa order { get; set; }
            public List<ColorsAndQuantities> colers { get; set; }
            public Malha malha { get; set; }
            public Preco_Malha preco { get; set; }
            public List<Programa_Malha> promalha { get; set; }
        }
    
    public class ColorsAndQuantities
        {
            public Programa_Cor coler { get; set; }
            public List<Programa_Cor_Info> quant { get; set; }
            public List<Programa_Cor_Info_Status> status { get; set; }
        }

    For better perception I changed my joins:

    img1-2

    var result1 = (from m in malha
                               join p in precos on m.ID_Ref equals p.ID_Ref into malhapreco
                               from mp in malhapreco
                               join o in order on mp.Ref_Balu equals o.Ref_Balu into malhaprcOrders
                               from mpo in malhaprcOrders
                               join pm in promalha on mpo.ID_Programa equals pm.ID_Programa into allOrder
                               from ao in allOrder
                               select new OrdersColorsViewModel
                               {
                                   malha = m,
                                   preco = mp,
                                   order = mpo,
                                   promalha = allOrder.ToList(),
                               }).ToList();

    and 

    var result2 = (from c in coler
                               join q in quant on new { orderId = c.ID_Programa, colorlineId = c.ID_Linha_Cor } equals new { orderId = q.ID_Programa, colorlineId = q.ID_Linha_Cor } into orderColerQuant
                               from ocq in orderColerQuant
                               join s in statu on new { orderId = ocq.ID_Programa, colorlineId = ocq.ID_Linha_Cor } equals new { orderId = s.ID_Programa, colorlineId = s.ID_Linha_Cor } into orderColerQuantStat
                               from ocqs in orderColerQuantStat
            
                               group new ColorsAndQuantities
                               {
                                   coler = c,
                                   quant = orderColerQuant.ToList(),
                                   status = orderColerQuantStat.ToList(),
                               } by c.ID_Programa).ToList();

    what gives rise to the result:

    var result = (from r1 in result1
                              join r2 in result2 on r1.order.ID_Programa equals r2.Key
                              select new OrdersColorsViewModel
                              {
                                  order = r1.order,                            
                                  malha = r1.malha,
                                  preco = r1.preco,
                                  promalha = r1.promalha,
                                  colers = r2.ToList(),
                              }).ToList().OrderBy(s => s.order.Semana_Embarque);

    in my listing I try to summarize the number of pieces by status, but I have a problem, in sum, when I have an order with more than one line it only adds the first

    img1-4

    in my example, enc 20/1069 I have two colors in the same status but the value I get from my sum is 114 and not 207 (114 + 93)

    string testcort = "A testar corte";
                ViewBag.testcortVB = result.Where(a => a.colers[0].status[0].Status == testcort).Select(a => a.colers[0].quant[0].Cliente).Sum();

    I would like to know how can I structure my project so that this does not happen?

    my goal is to have a list with the quantities by status as i currently have

    -------Update:-----------------------------------------------------------------------------------------------------------------------------------

    in my view I got the total through

    @foreach (var item in Model)
        {
            @item.colers.Sum(i => i.quant[0].Cliente)
        }

    can I apply the above method to my controller?

    Thanks for all the help

    Monday, March 1, 2021 10:04 AM

Answers

  • User1686398519 posted

    Hi MiguelMi, 

    1. I understand your question like this:
      1. MiguelMi

        result.Where(a => a.colers[0].status[0].Status == testcort)
      2. The data you get is this: there are many Orders, each Order has many Colors, and each Color has many Status and N°Pecas.
      3. I simplified your question as shown in the figure below:
        1. You want to sum the N°Pecas of each Order in the result filtered by the value of Status.
    2. If I understand correctly, you can achieve your needs like this:
      • result.Select(m => m.colers.Select(c => new { 
                            Clientelist = c.coler.quant.Select(i => i.Cliente).ToList(),
                            status = c.coler.status.Where(n => n.Status == teststatus).ToList().ToList()
                    }).ToList())
                     .Select(m=>m.Where(i=>i.status.Count()!=0).Sum(p=>p.Clientelist.Sum()))
                     .ToList();
    3. I wrote an example to help you understand the above solution.
      1. Your model is too complex, so the example I gave simplifies the model, you can modify it according to the actual situation.
      2. I did not get data from the database, but simulated some test data.
      3. Model
        •     public class OrdersColorsViewModel
              {
                  public List<ColorsAndQuantities> colers { get; set; }
              }
              public class ColorsAndQuantities
              {
                  public Programa_Cor coler { get; set; }
              }
              public class Programa_Cor
              {
                  public List<Programa_Status> status { get; set; }
                  public List<Programa_Quant> quant { get; set; }
              }
              public class Programa_Status
              {
                  public int StatusId { get; set; }
                  public string Status { get; set; }
              }
              public class Programa_Quant
              {
                  public int QuantId { get; set; }
                  public int Cliente { get; set; }
              }
      4. Controller
        •         public List<OrdersColorsViewModel> testdata()
                  {
                      OrdersColorsViewModel model = new OrdersColorsViewModel
                      {
                          colers = new List<ColorsAndQuantities> {
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=1, Cliente=114}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=2, Cliente=93}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=3, Cliente=1}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status2", StatusId=2}
                                       }
                                  }
                              }
                          }
                      };
                      OrdersColorsViewModel model2 = new OrdersColorsViewModel
                      {
                          colers = new List<ColorsAndQuantities> {
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=4, Cliente=2}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=4, Cliente=9}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status3", StatusId=3}
                                       }
                                  }
                              }
                          }
                      };
          
                      List<OrdersColorsViewModel> result = new List<OrdersColorsViewModel>();
                      result.Add(model);
                      result.Add(model2);
                      return result;
                  }
                  public ActionResult Index()
                  {
                      var result = testdata();
                      string teststatus = "Status1";
                      var t1 = result.Select(m => m.colers.Select(c => new { 
                              Clientelist = c.coler.quant.Select(i => i.Cliente).ToList(),
                              status = c.coler.status.Where(n => n.Status == teststatus).ToList().ToList()
                      }).ToList())
                       .Select(m=>m.Where(i=>i.status.Count()!=0).Sum(p=>p.Clientelist.Sum()))
                       .ToList();
                      return View();
                  }
        • Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 2, 2021 10:03 AM

All replies

  • User-1151440187 posted

    Hi

    string testcort = "A testar corte";
                ViewBag.testcortVB = result.Where(a => a.colers[0].status[0].Status == testcort).Select(a => a.colers[0].quant[0].Cliente).Sum();

    Above code is okay but still try this : 

    string testcort = "A testar corte";
                ViewBag.testcortVB = result.Where(a => a.colers[0].status[0].Status == testcort).Sum(a => a.colers[0].quant[0].Cliente);

    Hope it helps.

    Yes you can add this code in controller.

        @foreach (var item in Model)
        {
            @item.colers.Sum(i => i.quant[0].Cliente)
        }

    Tuesday, March 2, 2021 4:25 AM
  • User1670624291 posted

    Hi, shaili shah

    string testcort = "A testar corte";
                ViewBag.testcortVB = result.Where(a => a.colers[0].status[0].Status == testcort).Sum(a => a.colers[0].quant[0].Cliente);

    I tried it but I got the same result

    Tuesday, March 2, 2021 8:18 AM
  • User1686398519 posted

    Hi MiguelMi, 

    1. I understand your question like this:
      1. MiguelMi

        result.Where(a => a.colers[0].status[0].Status == testcort)
      2. The data you get is this: there are many Orders, each Order has many Colors, and each Color has many Status and N°Pecas.
      3. I simplified your question as shown in the figure below:
        1. You want to sum the N°Pecas of each Order in the result filtered by the value of Status.
    2. If I understand correctly, you can achieve your needs like this:
      • result.Select(m => m.colers.Select(c => new { 
                            Clientelist = c.coler.quant.Select(i => i.Cliente).ToList(),
                            status = c.coler.status.Where(n => n.Status == teststatus).ToList().ToList()
                    }).ToList())
                     .Select(m=>m.Where(i=>i.status.Count()!=0).Sum(p=>p.Clientelist.Sum()))
                     .ToList();
    3. I wrote an example to help you understand the above solution.
      1. Your model is too complex, so the example I gave simplifies the model, you can modify it according to the actual situation.
      2. I did not get data from the database, but simulated some test data.
      3. Model
        •     public class OrdersColorsViewModel
              {
                  public List<ColorsAndQuantities> colers { get; set; }
              }
              public class ColorsAndQuantities
              {
                  public Programa_Cor coler { get; set; }
              }
              public class Programa_Cor
              {
                  public List<Programa_Status> status { get; set; }
                  public List<Programa_Quant> quant { get; set; }
              }
              public class Programa_Status
              {
                  public int StatusId { get; set; }
                  public string Status { get; set; }
              }
              public class Programa_Quant
              {
                  public int QuantId { get; set; }
                  public int Cliente { get; set; }
              }
      4. Controller
        •         public List<OrdersColorsViewModel> testdata()
                  {
                      OrdersColorsViewModel model = new OrdersColorsViewModel
                      {
                          colers = new List<ColorsAndQuantities> {
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=1, Cliente=114}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=2, Cliente=93}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=3, Cliente=1}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status2", StatusId=2}
                                       }
                                  }
                              }
                          }
                      };
                      OrdersColorsViewModel model2 = new OrdersColorsViewModel
                      {
                          colers = new List<ColorsAndQuantities> {
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=4, Cliente=2}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status1", StatusId=1}
                                       }
                                  }
                              },
                              new ColorsAndQuantities{
                                  coler=new Programa_Cor{
                                       quant=new List<Programa_Quant>{
                                           new Programa_Quant{ QuantId=4, Cliente=9}
                                       },
                                       status=new List<Programa_Status>
                                       {
                                           new Programa_Status{Status="Status3", StatusId=3}
                                       }
                                  }
                              }
                          }
                      };
          
                      List<OrdersColorsViewModel> result = new List<OrdersColorsViewModel>();
                      result.Add(model);
                      result.Add(model2);
                      return result;
                  }
                  public ActionResult Index()
                  {
                      var result = testdata();
                      string teststatus = "Status1";
                      var t1 = result.Select(m => m.colers.Select(c => new { 
                              Clientelist = c.coler.quant.Select(i => i.Cliente).ToList(),
                              status = c.coler.status.Where(n => n.Status == teststatus).ToList().ToList()
                      }).ToList())
                       .Select(m=>m.Where(i=>i.status.Count()!=0).Sum(p=>p.Clientelist.Sum()))
                       .ToList();
                      return View();
                  }
        • Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 2, 2021 10:03 AM
  • User1670624291 posted

    Hi YihuiSun,

    Thanks a lot for the help,

    System.Collections.Generic.List`1[System.Nullable`1[System.Int32]]

    Update: already find out why the message is being displayed so I used:

    ViewBag.testcortVB = string.Join(", ", t1);

    and result

    Yes, got it,

    ViewBag.testcortVB = string.Join(", ", testcort.Where(a => a.Value != 0));

    thanks again YihuiSun

    Tuesday, March 2, 2021 10:45 AM