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

    public static function getProjects($filters = FALSE)
    {
        $authUserId = \Auth::user()->id ;
        $today =  date("Y-m-d") ;
    	$project = Project::
            join('users','users.id','=','project_manager_id')
            ->leftjoin('project_types','project_types.id','=','projects.project_type_id')
            ->leftjoin('tasks', function ($join) 
            {
                $join->on('tasks.project_id', '=', 'projects.id') ;
            })
            ->leftjoin('users as userTask','userTask.id','=','tasks.assignment_to')
            ->leftjoin('issues', function ($join) 
            {
                $join->on('issues.project_id', '=', 'projects.id') ;
            })
            ->leftjoin('users as userIssue','userIssue.id','=','issues.assignment_to')
            ->select('projects.*','project_types.project_type_name' ,
                DB::raw("sum(if( tasks.end_date < '".$today."' && tasks.progress < 100 , 1 , 0 )) as delay_task_count"),
                DB::raw("sum(if( issues.end_date < '".$today."' && issues.issue_status_id < 3 , 1 , 0 )) as delay_issue_count"),
                DB::raw("  (((sum(tasks.duration * progress)) / 100) / 
                    (sum(tasks.duration))) * 100  as progress"),
                DB::raw("sum(tasks.duration) / if( count(DISTINCT issues.id) = 0 , 1 , count(DISTINCT issues.id)) as duration"),
                DB::raw("sum(tasks.planHour) / if( count(DISTINCT issues.id) = 0 , 1 , count(DISTINCT issues.id)) as planHour"),
                DB::raw("count(DISTINCT tasks.id) as taskCount"),
                'users.name as manager_name','users.image as manager_image',DB::raw("DATE_FORMAT(projects.start_date,'%d, %b %Y') AS start_date_dmy"),
                DB::raw("MAX(tasks.end_date) as tasks_end_date"),
                DB::raw("MAX(issues.end_date) as issues_end_date"),
                DB::raw("MAX(issues.end_date) as issues_end_date"),
                DB::raw("sum(if( tasks.assignment_to =  $authUserId or issues.assignment_to = $authUserId , 1 , 0 )) as authUserAccess"),
                DB::raw("GROUP_CONCAT(DISTINCT CONCAT(userTask.name,'@',userTask.image)) as taskUsers"),
                DB::raw("GROUP_CONCAT(DISTINCT CONCAT(userIssue.name,'@',userIssue.image)) as issueUsers")
                );
    	if( isset($filters['projectId']))
    	{
    		$project = $project->where('projects.id',$filters['projectId']);
    	}
        if( isset($filters['statusId'] ))
        {
            $project = $project->where('projects.project_status_id',$filters['statusId']);
        }
    	$project = $project->groupBy('projects.id')->orderBy('projects.id','desc')->get();
    	return $project ;
    }


}