Trying to search with specific id in a period

Question

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

I want to do it this way:

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

Whereas in the 1013 I wanted him to take from my view textbox

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

Answer 1

I managed to solve the problem, I did some SQL testing in my query 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));
    }

Answer 2

Since we don’t know the structure of your database, I can’t tell you exactly the query to be made, but as a rule, just add in the Where of select an "AND" and the Car ID or something, that can identify the car.