laravel filter results by date range

1

Hi, I need to filter according to a particular range of dates. I'm trying to do it on my own but it has not gone well. It is giving me this error. I want to filter by the field Date_emission below is the model of my table

"SQLSTATE[22007]: Invalid datetime format: 7 ERROR:  la sintaxis de entrada no es válida para tipo date: «» (SQL: select count(*) as aggregate from "presupuesto" as "p" inner join "users" as "u" on "p"."cod_usuario" = "u"."id" inner join "cliente" as "c" on "p"."cod_cliente" = "c"."codigo_cliente" inner join "contacto" as "co" on "p"."cod_contacto" = "co"."idcontacto" where "fecha_emision" between  and  or "c"."empresa" LIKE %% or "u"."name" LIKE %% or "p"."status" LIKE %% or "co"."nombre" LIKE %%)"

My Controller:

 public function index(Request $request){
 $request->fechaInicial;
        $request->fechaFinal;

        $date = date('Y-m-d');
    

    

if($request->fechaInicial == NULL || $request->fechaFinal == NULL){

  $f1 = $date;
  $f2 = $date;

}else{

$f1 = $request->fechaInicial;
    $f2 = $request->fechaFinal;


}





  if($request){
        $query=trim($request->get('searchText'));
        $f1=trim($request->get('f1'));
        $f2=trim($request->get('f2'));
        $ingreso=DB::table('presupuesto as p')
        ->join('users as u','p.cod_usuario','=','u.id')
        ->join('cliente as c','p.cod_cliente','=','c.codigo_cliente')
        ->join('contacto as co','p.cod_contacto','=','co.idcontacto')
        ->select('p.id', 'p.codigo_presupuesto','c.empresa','co.nombre','u.name','u.role','p.fecha_emision','p.fecha_expiracion','p.total', 'p.status', 'p.respaldo')
         ->whereBetween('fecha_emision', [$f1, $f2])
        ->orwhere('c.empresa','LIKE','%'.$query.'%')
                 ->orwhere ('u.name','LIKE','%'.$query.'%')
                        ->orwhere ('p.status','LIKE','%'.$query.'%')
                             ->orwhere ('co.nombre','LIKE','%'.$query.'%')
                               
        ->orderBy('p.id','desc')
        ->paginate(7);
        return view('ventas.presupuesto.index',["ingresos"=>$ingreso,"searchText"=>$query,"f1"=>$f1,"f2"=>$f2]);
      }
    }

This is my Vista

{!! Form::open(array('url'=>'ventas/presupuesto', 'method'=>'GET', 'autcomplete'=>'off', 'role'=>'search'))!!}
<div class="form-group">
	<div class="input-group">
		<input type="text" class="form-control" name="searchText" placeholder="Buscar" value="{{$searchText}}">
		 <div class="col-lg-2 col-sm-2 col-md-2 col-xs-12">
          <div class="form-group"> 
        <label class="control-label" for="date">Fecha De Emision</label>
        <input class="form-control" id="fechaInicial" name="fechaInicial"  placeholder="AA/MM/DD" value="{{$f1}}" type="text"/>
      </div>
         </div>
      <div class="col-lg-2 col-sm-2 col-md-2 col-xs-12">
          <div class="form-group"> 
        <label class="control-label" for="date">Fecha De Expiracion</label>
        <input class="form-control" id="fechaFinal" name="fechaFinal" placeholder="AA/MM/DD" value="{{$f2}}" type="text"/>
      </div>
         </div>
		<span class="input-group-btn"><button type="submit" class="btn btn-primary">Buscar</button></span>
	</div>
</div>
 @push ('scripts') <!-- Trabajar con el script definido en el layout-->
  <script>
$(document).ready(function(){
      var date_input=$('input[name="fechaInicial"]');
      var date_inputt=$('input[name="fechaFinal"]');  //our date input has the name "date"
      var container=$('.bootstrap-iso form').length>0 ? $('.bootstrap-iso form').parent() : "body";
      var options={
        format: 'yyyy/mm/dd',
        container: container,
        todayHighlight: true,
        autoclose: true,
      };
      date_input.datepicker(options);
        date_inputt.datepicker(options);
    })

    </script>
  @endpush
{{Form::close()}}

@extends ('layouts.admin')
@section ('contenido')
	<div class="row">
		<div class="col-lg-8 col-md-8 col-sm-8 col-xs-12">
			<h3>Listado de Presupuestos <a href="/ventas/presupuesto/create"><button class="btn btn-success">Nuevo</button></a></h3>
			@include('ventas.presupuesto.search')
		</div>
	</div>
	<div class="row">
		<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
			<div class="table-responsive">
				<table class="table table-striped table-bordered table-condensed table-hover">
					<thead>
						<th>Codigo Presupuesto</th>
						<th>Empresa</th>
						<th>Contacto</th>
						<th>Usuario</th>
						<th>Rol</th>
						<th>Fecha Emision</th>
						<th>Fecha Expiracion</th>
						<th>Total</th>
						<th>Estado</th>
						<th>Opciones</th>
					</thead>
					<!-- bucle -->
							
					@foreach ($ingresos as $ing)
					<tr>
						<td>{{$ing->codigo_presupuesto}}</td>
						<td>{{$ing->empresa}}</td>
						<td>{{$ing->nombre}}</td>
						<td>{{$ing->name}}</td>
						<td>{{$ing->role}}</td>
						<td>{{$ing->fecha_emision}}</td>
						<td>{{$ing->fecha_expiracion}}</td>
						<td>{{$ing->total}}</td>
						<td>{{$ing->status}}</td>
						<td>
									<a href="{{URL::action('PresupuestoController@edit',$ing->id)}}"><button class="btn btn-info">Editar</button></a>
							<a href="{{URL::action('PresupuestoController@show', $ing->id)}}"><button class="btn btn-info"> Detalles</button></a>
											<a href="/archivos/respaldoLegal/{{$ing->respaldo}}" download="{{$ing->respaldo}}">
								<button type="button" class="btn btn-primary">
								<i class="glyphicon">
									Descargar
								</i>
								</button>
							</a>
									<a href="" data-target="#modal-delete-{{$ing->id}}" data-toggle="modal"><button class="btn btn-danger"> Anular</button></a>	
						</td>

						<td>

							
						
					</tr>
					@include('ventas.presupuesto.modal')
					@endforeach
					
				</table>
				
			</div>
			{{$ingresos->render()}}
			
		</div>
	</div>
@endsection
When I open my view index shows me all budgets created, I have the option to filter the records either by the company the contact etc. but I would also like to know for example that you bring me all the budget where your issue date is between 2017-06-06 and 2017-06-18, what I need is a filter of dates I do not know if I make myself understood. but if it has cost me to try to do it

This is what I found Expected ':', but '=' was found. Declaration rejected. budget

    
asked by Jorge Ortiz 07.06.2017 в 17:37
source

1 answer

1

You're confusing how you should pass the variables between server and client.

In the view you could have something like this, using helper old() to show the previous data:

<input class="form-control" id="fechaInicial" name="fechaInicial"  placeholder="AA/MM/DD" value="{{ old('fechaInicial') }}" type="text"/>

In the controller, at a point you are trying to get two variables f1 and f2 of the request, which as far as I can see, do not exist. You should simply use the values you have already obtained before.

Here the code, with a bit of refactoring, there are many things that are left over, especially the request conditional, since there will always be a Request in Laravel, I do not know what you want to compare or verify there:

public function index(Request $request)
{
    // eliminamos validaciones innecesarias y ponemos la fecha de hoy por defecto en ambas variables
    $f1 = $f2 = date('Y-m-d');

    if(! is_null($request->fechaInicial) && ! empty($request->fechaInicial) && ! is_null($request->fechaFinal) || ! empty($request->fechaFinal) {
        $f1 = $request->fechaInicial;
        $f2 = $request->fechaFinal;
    }

    // if($request){

        // no es necesario el trim si se utiliza el middleware correcto
        $query = trim($request->get('searchText'));

        // esto se puede hacer mejor con Eloquent
        $ingreso = DB::table('presupuesto as p')
            ->join('users as u','p.cod_usuario','=','u.id')
            ->join('cliente as c','p.cod_cliente','=','c.codigo_cliente')
            ->join('contacto as co','p.cod_contacto','=','co.idcontacto')
            ->select('p.id', 'p.codigo_presupuesto','c.empresa','co.nombre','u.name','u.role','p.fecha_emision','p.fecha_expiracion','p.total', 'p.status', 'p.respaldo')
            ->whereBetween('fecha_emision', [$f1, $f2])
            ->orwhere('c.empresa','LIKE','%'.$query.'%')
            ->orwhere ('u.name','LIKE','%'.$query.'%')
            ->orwhere ('p.status','LIKE','%'.$query.'%')
            ->orwhere ('co.nombre','LIKE','%'.$query.'%')                
            ->orderBy('p.id','desc')
            ->paginate(7);

        return view('ventas.presupuesto.index',["ingresos" => $ingreso, "searchText" => $query,"f1" => $f1, "f2" => $f2]);

    // }
}
    
answered by 07.06.2017 в 23:21