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

    public static function getIssue($id )
    {
    	return Issue::select('issues.*','projects.project_name','users.name as assignment_to_name','users.image as assignment_to_image' , 'issue_status.issue_status_name' , 'priority.priority_name',
            'priority.priority_class' , 
            DB::raw("DATE_FORMAT(issues.start_date,'%d, %b %Y') AS start_date_dmy"),
            DB::raw("DATE_FORMAT(issues.end_date,'%d, %b %Y') AS end_date_dmy"))
        ->join('projects','projects.id','=','issues.project_id')
        ->leftJoin('users','users.id','=','issues.assignment_to')
        ->leftJoin('issue_status','issue_status.id','=','issues.issue_status_id')
        ->leftJoin('priority','priority.id','=','issues.priority_id')
        ->where('issues.id',$id)->first();
    }
    public static function getProjectIssue($projectId ,$issueStatusId = FALSE )
    {
        $issue = Issue::select('issues.*','users.name as assignment_to_name','users.image as assignment_to_image', 'issue_status.issue_status_name' , 'priority.priority_name' , 'priority.priority_class' ,DB::raw("DATE_FORMAT(issues.start_date,'%d, %b %Y') AS start_date_dmy"),DB::raw("DATE_FORMAT(issues.end_date,'%d, %b %Y') AS end_date_dmy"))
        ->leftJoin('users','users.id','=','issues.assignment_to')
        ->leftJoin('issue_status','issue_status.id','=','issues.issue_status_id')
        ->leftJoin('priority','priority.id','=','issues.priority_id')
        ;
        $issue = $issue->where('issues.project_id',$projectId);
        if($issueStatusId !== FALSE)
        {
            $issue = $issue->where('issues.issue_status_id',$issueStatusId);
        }
        else
        {
            $issue = $issue->where('issues.issue_status_id' ,'<',4);
        }
        $issue = $issue->orderBy('issues.id', 'asc')->get();
        return $issue ;
    }

    public static function getUserIssue($userId = FALSE , $statusIds = FALSE , $projectId = FALSE)
    {
        $issues = Issue::select('issues.*', 'projects.project_name', 'priority.priority_name','priority.priority_class', 'issue_status.issue_status_name' ,'users.name as assignment_to_name','users.image as assignment_to_image' ,DB::raw("DATE_FORMAT(issues.start_date,'%d-%m-%Y') AS start_date_dmy"),DB::raw("DATE_FORMAT(issues.end_date,'%d-%m-%Y') AS end_date_dmy"))
        ->join('projects','projects.id','=','issues.project_id')
        ->leftJoin('priority','priority.id','=','issues.priority_id')
        ->leftJoin('issue_status','issue_status.id','=','issues.issue_status_id')
        ->leftJoin('users','users.id','=','issues.assignment_to') ;
        if($projectId !== FALSE)
        {
            $issues = $issues->where('projects.id',$projectId);
        }
        if( $statusIds !== FALSE )
        {
           $issues = $issues->whereIn('issues.issue_status_id',$statusIds); 
        }
        if($userId !== FALSE)
        {
            $issues = $issues->where('issues.assignment_to',$userId);
        }
        $issues = $issues->orWhere(function($query) use ($userId)
        {
            $query->where('issues.issue_status_id', '3')
                ->where('issues.assignment_from', $userId);
        });
        
        $issues = $issues->groupBy('issues.id')->orderBy('issues.id', 'DESC')->get();
        return $issues ;
    }
}