<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use DB ;
class Task extends Model 
{
    protected $fillable = [  
    ];

    public static function getTask($id )
    {
    	return Task::join('projects','projects.id','=','tasks.project_id')
        ->leftJoin('users','users.id','=','tasks.assignment_to')
        ->select('tasks.*','projects.project_name','users.name as assignment_to_name',
            'users.email as assignment_to_email','users.image as assignment_to_image' ,DB::raw("DATE_FORMAT(tasks.start_date,'%d, %b %Y') AS start_date_dmy"),DB::raw("DATE_FORMAT(tasks.end_date,'%d, %b %Y') AS end_date_dmy"))
        ->where('tasks.id',$id)->first();
    }
    public static function getProjectTask($projectId )
    {
        $tasks = Task::select('tasks.*','users.name as assignment_to_name','users.image as assignment_to_image' ,
            DB::raw("DATE_FORMAT(tasks.start_date,'%d, %b %Y') AS start_date_dmy"),
            DB::raw("DATE_FORMAT(tasks.end_date,'%d, %b %Y') AS end_date_dmy"))
        ->leftJoin('users','users.id','=','tasks.assignment_to');
        $tasks = $tasks->where('tasks.project_id',$projectId);
        $tasks = $tasks->orderBy('tasks.task_no', 'asc')->get();
        return $tasks ;
    }

    public static function getUserTasks($userId = FALSE,$progress = FALSE,$projectId = FALSE)
    {
        $tasks = Task::select('tasks.*', 'projects.project_name' ,'users.name as assignment_to_name','users.image as assignment_to_image' ,DB::raw("DATE_FORMAT(tasks.start_date,'%d-%m-%Y') AS start_date_dmy"),DB::raw("DATE_FORMAT(tasks.end_date,'%d-%m-%Y') AS end_date_dmy"),
            'predecessor_tasks.progress as predecessor_progress',
            'predecessor_tasks.id as predecessor_task_id'
            )
        ->join('projects','projects.id','=','tasks.project_id')
        ->leftJoin('users','users.id','=','tasks.assignment_to')
        //->leftJoin('tasks as complete','tasks.predecessor','=','complete.predecessor')
        ->leftJoin('tasks as predecessor_tasks', function ($join) 
        {
            $join->on('tasks.predecessor', '=', 'predecessor_tasks.task_no') ;
            $join->on( 'predecessor_tasks.project_id' , '=' , 'tasks.project_id' ) ;
            //->where('predecessor_tasks.progress', 100); 
        }); 
        
        if($userId !== FALSE)
        {
            $tasks = $tasks->where('tasks.assignment_to',$userId);
        }

        if($progress === FALSE)
        {
            $tasks = $tasks->where('tasks.progress', '<' , 100 );
        }
        else
        {
            $tasks = $tasks->where('tasks.progress', '=' , $progress );
        }
        if($projectId !== FALSE)
        {
            $tasks = $tasks->where('projects.id',$projectId);
        }
        $tasks = $tasks->groupBy('tasks.id')->orderBy('tasks.id', 'DESC')->get();

        return $tasks ;
    }
}