Filter insert in bd according to the date

Question

Filter insert in bd according to the date

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have an application in laravel, which manages the holidays, I have a problem when filtering the dates selected by the workers, since I am only able to filter the start and end date, the problem is that according to the department 2 workers can not take vacations on the same date, but if I select for example from 1 to 15 with worker 1 it should be impossible for worker 2 to select for example from 2 to 14, however as I am only able to filter the date Start and end I accept it, and save with which the filter does not work.

My question is if there is not a way to filter a range of dates, instead of start and end dates. I leave the code:

public function store(Request $request)
    {
        $this->validate($request, [
            'type' => 'required',
            'observations' => 'required',
            'datefilter' => 'required',
        ]);
    $dep =\DB::table('workers')->select('workers.area_id')
        ->where(['workers.id' => $request->worker_id])
        ->get();

    $vacation = new Vacation();
    $vacation -> worker_id = $request['worker_id'];
    $vacation -> type = $request['type'];
    $vacation -> observations = $request['observations'];
    $vacation -> area_id = $dep[0]->area_id;
    //parse dates
    $date  = explode('-',$request['datefilter']);
    $dateFrom = date("y-m-d",strtotime( $date[0]));
    $dateTo = date("y-m-d",strtotime($date[1]));

    $vacation -> date_from = date("y-m-d", strtotime($dateFrom));
    $vacation -> date_to = date("y-m-d", strtotime( $dateTo ));

    $guardadas_datefrom = \DB::table('vacations')
        ->where('date_from',  $dateFrom)
        ->where('area_id',  $dep[0]->area_id )
        ->get();
    $guardadas_dateto = \DB::table('vacations')
        ->where('date_to',  $dateTo)
        ->where('area_id',  $dep[0]->area_id )
        ->get();

    if(!$guardadas_datefrom && !$guardadas_dateto){
       $vacation->save();

        $data = request()->all();

       return back()->with('success','Vacaciones Solicitadas correctamente');

    }else{
        return back()->with('error','Fechas no disponibles');
    }

    return back();
}

Thanks in advance. Greetings.

php laravel

asked by Peisou 02.08.2018 в 12:22

source

2 answers

1

I think that in your case there would be no problem in using whereBetween and orWhereBetween. If there are results the employee can not take a vacation T__T

$conflictivas = \DB::table('vacations')
                   ->whereBetween('date_from', [$inicio, $fin])
                   ->orWhereBetween('date_to', [$inicio, $fin])
                   ->get();

answered by 02.08.2018 в 14:10

Doubt about Kotlin language Error Call to undefined function storage_patch () when I run a seeder in laravel

score 1 · Accepted Answer

To discern that there is no record in the range / period of time indicated, you must do 4 where nested in the query. Example:

SELECT * FROM vacaciones v
WHERE 
    ( fecha_inicio >= $inicio AND fecha_fin  <= $fin )
    OR
    ( fecha_inicio >= $inicio AND fecha_fin  > $fin )
    OR
    ( fecha_inicio  < $inicio AND fecha_fin <= $fin)
    OR
    ( fecha_inicio  < $inicio AND fecha_fin > $fin)

Start date greater than or equal to start and end date less than or equal

Start date greater than or equal to start and end date greater than end

Start date less than start and end date less than or equal to end

Start date is less than start and end date is greater than end

Seen this let's move it to Eloquent

$guardadas = \DB::table('vacations')
    // ambas dentro del rango/periodo
    ->where(function($q) use ($inicio, $fin) {
        $q->where('date_from', '>=', $inicio)
            ->where('date_to', '<=', $fin);
    })
    // fin desborda rango/periodo pero inicio dentro rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->where('date_from', '>=', $inicio)
            ->where('date_to', '>', $fin);
    })
    // inicio desborda rango/periodo pero fin dentro de rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->where('date_from', '<', $inicio)
            ->where('date_to', '<=', $fin);
    })
    // inicio y fin desbordan el rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->where('date_from', '<', $inicio)
            ->where('date_to', '>', $fin);
    })
    // otras condiciones
    ->get();

Obviously, the fields in the database must be of type DATE or DATETIME, and the variables of the example $inicio and $fin must be set before the query with the supported format, 'YYYY-MM-DD 'or' YYYY-MM-DD hh: mm: ss'

Edit

The most complete option using Between would be the following:

$guardadas = \DB::table('vacations')
    // ambas dentro del rango/periodo
    ->where(function($q) use ($inicio, $fin) {
        $q->where('date_from', '>=', $inicio)
            ->where('date_to', '<=', $fin);
    })
    // fin desborda rango/periodo pero inicio dentro rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->whereBetween('date_from', [$inicio, $fin])
            ->where('date_to', '>', $fin);
    })
    // inicio desborda rango/periodo pero fin dentro de rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->where('date_from', '<', $inicio)
            ->whereBetween('date_to', [$inicio, $fin]));
    })
    // inicio y fin desbordan el rango/periodo
    ->orWhere(function($q) use ($inicio, $fin) {
        $q->where('date_from', '<', $inicio)
            ->where('date_to', '>', $fin);
    })
    // otras condiciones
    ->get();