Attempting to search with specific id in a period

1

I'm trying to get a specific car between dates, because it works in periods, but I need to get the element ID, I do not know if I would have to apply some other query or include it, the database I use is SQL, case there is doubt

I want to do would be as follows:

 SELECT * FROM Abastecimento a WHERE NumCarroId = 1013 AND DtAbastecido BETWEEN '2017-07-01'AND '2017-07-30';

Being in 1013 I wanted it to take from the textbox of my view

My HQL returns me the date period:

    public IList<Abastecimento> ConsultaPeriodo(DateTime dataInicio, DateTime dataFinal)
        {
            string hql = "SELECT a FROM Abastecimento a WHERE a.DtAbastecido BETWEEN :dataInicial AND :dataFinal";
            IQuery query = session.CreateQuery(hql)
                .SetParameter("dataInicial", dataInicio)
                .SetParameter("dataFinal", dataFinal);
            return query.List<Abastecimento>();
        }

And my controller that uses this query:

    public ActionResult AbastecimentoPeriodo(DateTime dataInicio, DateTime dataFinal, int? pagina, Boolean? gerarPDF, AbastecimentoModel viewModel)
        {
            var data = ckm.ConsultaPeriodo(dataInicio,dataFinal);
            ViewBag.dataInicio = dataInicio;
            ViewBag.dataFinal = dataFinal;
            var periodo = data.Where(i => i.DtAbastecido >= dataInicio && i.DtAbastecido <= dataFinal).OrderBy(p => p.DtAbastecido).ToList<Abastecimento>();
            int paginaQdteRegistros = 25;
            int paginaNumeroNavegacao = (pagina ?? 1);
            return View(data.ToPagedList(paginaNumeroNavegacao, paginaQdteRegistros));
        }
    
asked by anonymous 21.12.2017 / 21:10

2 answers

0

I was able to solve the problem, I did some SQL test in my query it was like this

My HQL:

 public IList<Abastecimento> ConsultaPeriodo(int carro, DateTime dataInicio, DateTime dataFinal)
    {
        string hql = "SELECT a FROM Abastecimento a WHERE NumCarroId = :carro AND a.DtAbastecido BETWEEN :dataInicial AND :dataFinal";
        IQuery query = session.CreateQuery(hql)
            .SetParameter("dataInicial", dataInicio)
            .SetParameter("dataFinal", dataFinal)
            .SetParameter("carro", carro);
        return query.List<Abastecimento>();
    }

And my controller:

public ActionResult AbastecimentoPeriodo(int carro, DateTime dataInicio, DateTime dataFinal, int? pagina, Boolean? gerarPDF, AbastecimentoModel viewModel)
    {

        var data = ckm.ConsultaPeriodo(carro,dataInicio,dataFinal);
        ViewBag.dataInicio = dataInicio;
        ViewBag.carro = carro;
        ViewBag.dataFinal = dataFinal;

        var periodo = data.OrderBy(p => p.DtAbastecido).ToList<Abastecimento>();

        int paginaQdteRegistros = 25;
        int paginaNumeroNavegacao = (pagina ?? 1);
        return View(data.ToPagedList(paginaNumeroNavegacao, paginaQdteRegistros));
    }
    
26.12.2017 / 18:24
0

As we do not know the structure of your database, I can not tell you exactly the query to be made, but as a rule, just add an AND where and select the car ID or something, that can identify the car.

    
22.12.2017 / 18:51