<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use DB ;
class Calendars extends Model
{
    public $table = 'calendars'; 
    protected $fillable = [
    ];
    
    public static function calendarCurrentDateAndWorkingHours($filtersCalendars)
    {
       $id = $filtersCalendars['id'];
       $calendarCurrentDateAndWorkingHours = Calendars::leftjoin('timesheets', function ($join) use ($id) 
              {
                  $join->on("calendars.id","timesheets.calendar_id")
                       ->where("timesheets.user_id",$id);
              })
              ->leftjoin("timesheet_infos","timesheet_infos.timesheet_id","timesheets.id")
              ->select("calendars.*",DB::raw('sum(timesheet_infos.time_expend) as minuts'));
       if(isset($filtersCalendars['whereDate']) && !empty($filtersCalendars['whereDate']))
       {
         $calendarCurrentDateAndWorkingHours = $calendarCurrentDateAndWorkingHours->whereDate("calendar_date",$filtersCalendars['whereDate']);
       }
       else
       {
        $calendarCurrentDateAndWorkingHours = $calendarCurrentDateAndWorkingHours->whereDate("calendar_date",date("Y-m-d"));
       }       
       $calendarCurrentDateAndWorkingHours = $calendarCurrentDateAndWorkingHours->get();
       return $calendarCurrentDateAndWorkingHours;
    }
    public static function dateWiseWorkingHours($id)
    {
       $dateWiseWorkingHours = Calendars::join('timesheets', function ($join) use ($id) 
              {
                  $join->on("calendars.id","timesheets.calendar_id")
                       ->where("timesheets.user_id",$id);
              })
              ->join("timesheet_infos","timesheet_infos.timesheet_id","timesheets.id")
              ->leftjoin("issues","issues.id","timesheet_infos.issue_id")
              ->leftjoin("tasks","tasks.id","timesheet_infos.task_id")
              ->leftjoin("projects","projects.id","timesheet_infos.project_id")
              ->select("calendars.date_name","calendars.calendar_date","timesheets.user_id","issues.issue_description","tasks.task_name","projects.project_name",DB::raw('timesheet_infos.*,(SELECT COUNT(ti.id) FROM timesheet_infos ti WHERE timesheet_infos.timesheet_id=ti.timesheet_id group by ti.timesheet_id) as dateCount'))
              ->whereDate("calendars.calendar_date","<=",DB::raw('CURDATE()'))
              ->groupBy("timesheet_infos.id")
              ->orderBy("calendars.id","DESC")
              ->get();
        return $dateWiseWorkingHours;
    }
}