How to do JSON function return Monthly amount of liters fueled in one year period

Question

How to do JSON function return Monthly amount of liters fueled in one year period

Asked 3 years, 10 months ago

Viewed 40 times

0

The function should return this Example: { "January": 500.00, "February": 514.00, ....}

My table has the following fields.

Km of supply Liters Abastecidos Valor Pago Date of supply

I developed an instruction that manages to group the months, but I’m having difficulties to make the sum of the liters supplied in each month and the total liters within a year.

    //GET: api/GetTotalLitrosPorAno/consulta=litros&ano=2020
    [HttpGet("GetTotalLitrosPorAno/{ano}")]
    public async Task<ActionResult<IEnumerable<Veiculo>>> GetTotalLitrosPorAno(string ano)
    {
        DateTime d = new DateTime(int.Parse(ano), 1, 1);

        var a = await _context.Veiculos
                         .Where(v => v.DataAbastecimento.Year == d.Year)
                         .GroupBy(x => new
                         {
                             Month = x.DataAbastecimento.Month
                         })
                         .ToListAsync();


        return Ok(a);

    }

How can I perform this procedure ?

I’m sorry, your question has nothing to do with JSON, Visual Studio or webapi... is a matter of Linus and lambda queries.

– Leandro Angelo

2020/08/24 at 14:51
@Leandroangelo, how to proceed since then, you could assist me in the matter of Lillian and lambda queries ?

– Cyberlacs

2020/08/24 at 14:55
And yet in its proposal, the Json does not make sense if the months are not arranged in another object, which would be the year... Unless you want to add up all the "Windows" of all the years...

– Leandro Angelo

2020/08/24 at 15:02

2 answers

1

You would need to group by date, or direct month and then select on top of the generated list by multiplying the value by the amount of liters.

I made a console application to illustrate your case, I hope it helps or give you a light, follows below the DTO used for simulation of your bank:

public class Valores
    {
        public double ValorPago { get; set; }
        public DateTime DataAbastecimento { get; set; }
        public double QtdLitros { get; set; }
    }

and the program class with the code.

class Program
    {
        static void Main(string[] args)
        {
            //carrega valores aleatórios
            List<Valores> valores = new List<Valores>() {
                new Valores { DataAbastecimento = new DateTime(2020,1,20), ValorPago = 3.00, QtdLitros = 20 },
                new Valores { DataAbastecimento = new DateTime(2020,1,20), ValorPago = 3.20, QtdLitros = 21 },
                new Valores { DataAbastecimento = new DateTime(2020,1,20), ValorPago = 3.40, QtdLitros = 23 },
                new Valores { DataAbastecimento = new DateTime(2020,2,20), ValorPago = 3.50, QtdLitros = 15 },
                new Valores { DataAbastecimento = new DateTime(2020,2,20), ValorPago = 3.00, QtdLitros = 30 },
                new Valores { DataAbastecimento = new DateTime(2020,3,20), ValorPago = 3.80, QtdLitros = 22 },
                new Valores { DataAbastecimento = new DateTime(2020,3,20), ValorPago = 3.45, QtdLitros = 27 },
                new Valores { DataAbastecimento = new DateTime(2020,5,20), ValorPago = 3.60, QtdLitros = 29 },
                new Valores { DataAbastecimento = new DateTime(2020,4,20), ValorPago = 3.70, QtdLitros = 18 },
                new Valores { DataAbastecimento = new DateTime(2020,6,20), ValorPago = 3.50, QtdLitros = 5 },
                new Valores { DataAbastecimento = new DateTime(2020,8,20), ValorPago = 3.80, QtdLitros = 45 },
                new Valores { DataAbastecimento = new DateTime(2020,9,20), ValorPago = 3.90, QtdLitros = 20 },
                new Valores { DataAbastecimento = new DateTime(2020,10,20), ValorPago = 3.50, QtdLitros = 30 },
                new Valores { DataAbastecimento = new DateTime(2020,11,20), ValorPago = 3.70, QtdLitros = 12 },
                new Valores { DataAbastecimento = new DateTime(2020,12,20), ValorPago = 3.80, QtdLitros = 18 },
                new Valores { DataAbastecimento = new DateTime(2020,3,20), ValorPago = 3.80, QtdLitros = 12 },
                new Valores { DataAbastecimento = new DateTime(2020,12,20), ValorPago = 3.85, QtdLitros = 45 },
                new Valores { DataAbastecimento = new DateTime(2020,11,20), ValorPago = 3.50, QtdLitros = 50 },
                new Valores { DataAbastecimento = new DateTime(2020,5,20), ValorPago = 3.60, QtdLitros = 40 },
                new Valores { DataAbastecimento = new DateTime(2020,7,20), ValorPago = 3.45, QtdLitros = 30 },
                new Valores { DataAbastecimento = new DateTime(2020,8,20), ValorPago = 3.10, QtdLitros = 12 },
                new Valores { DataAbastecimento = new DateTime(2020,6,20), ValorPago = 3.15, QtdLitros = 18 },
                new Valores { DataAbastecimento = new DateTime(2020,9,20), ValorPago = 3.30, QtdLitros = 16 },
                new Valores { DataAbastecimento = new DateTime(2020,10,20), ValorPago = 3.45, QtdLitros = 19 },
                new Valores { DataAbastecimento = new DateTime(2020,12,20), ValorPago = 3.78, QtdLitros = 12 },
                new Valores { DataAbastecimento = new DateTime(2020,11,20), ValorPago = 3.85, QtdLitros = 21 },
                new Valores { DataAbastecimento = new DateTime(2020,4,20), ValorPago = 3.98, QtdLitros = 24 },
                new Valores { DataAbastecimento = new DateTime(2020,11,20), ValorPago = 3.45, QtdLitros = 26 },
            };

            var valoresAgrupados = valores.Where(w => w.DataAbastecimento.Year == DateTime.Now.Date.Year)
                                    .GroupBy(g => g.DataAbastecimento.Month).Select(s => new { Month = s.Key, Valor = s.Sum(soma => soma.QtdLitros * soma.ValorPago)});

            foreach (var item in valoresAgrupados)
            {
                Console.WriteLine($"Mês: {item.Month} - valor: ${item.Valor}");
            }
        }
    }

Expected result:

Mês: 1 - valor: $205,4
Mês: 2 - valor: $142,5
Mês: 3 - valor: $222,35
Mês: 5 - valor: $248,4
Mês: 4 - valor: $162,12
Mês: 6 - valor: $74,19999999999999
Mês: 8 - valor: $208,2
Mês: 9 - valor: $130,8
Mês: 10 - valor: $170,55
Mês: 11 - valor: $389,95
Mês: 12 - valor: $287,01
Mês: 7 - valor: $103,5

Browser other questions tagged c# visual-studio asp.net-core asp.net-web-api

You are not signed in. Login or sign up in order to post.

by Cyberlacs • **935** points · Answer 1 · 2020-08-24T17:16:16+00:00

I managed to develop a solution that served perfectly in my project, may not be correct but it worked and I am sharing.

        [HttpGet("GetTotalLitrosPorAno/{ano}")]
    public async Task<ActionResult<IEnumerable<Veiculo>>> GetTotalLitrosPorAno(string ano)
    {
        DateTime d = new DateTime(int.Parse(ano), 1, 1);

        //var ops = await _context.Veiculos.Where(v => v.DataAbastecimento.Year == d.Year).ToListAsync();
        ////var conta = (dynamic)null;
        //var conta = ops.Sum(v => v.Litros);

        List<Relatorio> lista = new List<Relatorio>();

        //AQUI GROUPBY FUNCIONA MAS NÃO CONSEGUI FAZER SOMATÓRIO DE CADA MÊS
        var eai = await _context.Veiculos
                         .Where(v => v.DataAbastecimento.Year == d.Year)
                         .GroupBy(x => new
                         {
                             Month = x.DataAbastecimento.Month
                         })
                         .ToListAsync(); 

        var mes = "";
        decimal valor = 0;
        foreach (var item in eai)
        {
            valor = 0;

            switch (item.Key.Month)
            {
                case 1:
                    mes = "Janeiro";
                    break;
                case 2:
                    mes = "Fevereiro";
                    break;
                case 3:
                    mes = "Março";
                    break;
                case 4:
                    mes = "Abril";
                    break;
                case 5:
                    mes = "Maio";
                    break;
                case 6:
                    mes = "Junho";
                    break;
                case 7:
                    mes = "Julho";
                    break;
                case 8:
                    mes = "Agosto";
                    break;
                case 9:
                    mes = "Setembro";
                    break;
                case 10:
                    mes = "Outubro";
                    break;
                case 11:
                    mes = "Novembro";
                    break;
                case 12:
                    mes = "Dezembro";
                    break;
                default:
                    mes = "Mês inexistente";
                    break;
            }

            foreach (var k in item)
            {
                valor = valor + k.Valor;
            }

            lista.Add(new Relatorio { Mes = mes, Valor = valor });
        }

        //string json = JsonConvert.SerializeObject(lista, Formatting.Indented);

        return Ok(lista);
        //return Ok(abcdefghijklmnopqrstuvxz);

    }

This function returns me to JSON the way I wanted, bringing the planned result.

[
{
    "mes": "Janeiro",
    "valor": 300.00
},
{
    "mes": "Fevereiro",
    "valor": 200.00
},
{
    "mes": "Março",
    "valor": 400.00
},
{
    "mes": "Maio",
    "valor": 100.00
},
{
    "mes": "Junho",
    "valor": 200.00
},
{
    "mes": "Julho",
    "valor": 500.00
},
{
    "mes": "Agosto",
    "valor": 400.00
},
{
    "mes": "Outubro",
    "valor": 300.00
},
{
    "mes": "Novembro",
    "valor": 100.00
},
{
    "mes": "Dezembro",
    "valor": 300.00
}
]