php - laravel 在日期 from_date 和 to_date 之间

Question

我在 Laravel 工作。

• 目前，我在考勤模块工作。
• 我的问题是离开申请，首先我被申请离开 from_date到 to_date。
• 接下来，我将通过错误应用相同的日期。
• 中间日期因错误而适用。这两个条件OK。
• 但我申请在 from_date 之后和 to_date 之后离开。这种情况没有被采取

如何计算这个条件。我下面的代码请检查

$keyWithData = DB::table('leave_allocations')
     ->select('employee','leave_type','name')
     ->where('leave_allocations.from_date','<=',$dateS->format('Y-m-d')." 00:00:00")
     ->where('leave_allocations.to_date','>=',$dateS->format('Y-m-d')." 00:00:00")
     ->where('leave_allocations.from_date','<=',$dateE)
     ->where('leave_allocations.to_date','>=',$dateE)
     ->where('leave_allocations.employee',$empdata->name)
     ->where('leave_allocations.leave_type',$type)
     ->first();

谢谢

Answer 1

我想你只需要 whereBetween() 和 orWhereBetween()

$keyWithData = DB::table('leave_allocations')
    ->select('employee','leave_type','name')
    ->where(function($query) use($dateS, $dateE) {
        $query->whereBetween('from_date', [
            $dateS->format('m-d-Y 00:00:00'), 
            $dateE->format('m-d-Y 00:00:00')
        ])
        ->orWhereBetween('to_date', [
            $dateS->format('m-d-Y 00:00:00'),
            $dateE->format('m-d-Y 00:00:00')
        ]);
    })
    ->where([
        ['leave_allocations.employee',$empdata->name].
        ['leave_allocations.leave_type',$type]
    ])
    ->first();

另一种使用日期范围的方法

// create a period object between new leaves start date and end date.
$period = new \DatePeriod(
    $dateS,
    new \DateInterval('P1D'),
    $dateE
);

// all the days between new leave start date and end date.
$new_leave_dates = [];

foreach ($period as $key => $value) {
   array_push($dates, $value->format('Y-m-d'));
}

// get all the rows without those days.
$keyWithData = DB::table('leave_allocations')
    ->select('employee','leave_type','name')
    ->whereNotIn('from_date', $new_leave_dates)
    ->where([
        ['leave_allocations.employee',$empdata->name].
        ['leave_allocations.leave_type',$type]
    ])
    ->first();