<?php
namespace App;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Zizaco\Entrust\Traits\EntrustUserTrait;
use DB ;
class User extends Authenticatable
{
    use EntrustUserTrait, Notifiable;
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    public function timeZone()
    {
        return $this->belongsTo('App\Time_zone','timeZoneId')->withDefault();
    }

    public function jobRole()
    {
        return $this->belongsTo('App\Job_role','jobRoleId')->withDefault();
    }

    public function language()
    {
      return $this->belongsToMany('App\Language', 'user_language_rels','userId','languageId'); 
    }
    public function skill()
    {
      return $this->belongsToMany('App\Skill', 'user_skill_rels','userId','skillId'); 
    }
    public function specialization()
    {
      return $this->belongsToMany('App\Specialization', 'user_specialization_rels','userId','specializationId'); 
    }

    public static function businessUnit()
    {
        
        return  \App\User::
            leftjoin('units','units.id','=','users.unitId')
            ->leftjoin('divisions','units.id','=','users.divisionId')
            ->leftjoin('departments','departments.id','=','users.departmentId')
            ->leftjoin('sub_departments','sub_departments.id','=','users.subDepartmentId')
            ->leftjoin('job_roles','job_roles.id','=','users.jobRoleId')
            ->leftjoin('users as reportingToName','reportingToName.id','=','users.reportingToId')
            ->select('units.unitName','divisions.divisionName','departments.departmentName','sub_departments.subDepartmentName','job_roles.jobRoleName','reportingToName.name as reportingToName')
            ->where('users.id', \Auth::user()->id )
            ->first();
           
    }

    public static function userSpecialization($id = FALSE)
    {
        $userId = \Auth::user()->id ; 
        if( $id !== FALSE )
        {
            $userId = $id ;
        }
        return \App\User_specialization_rel::
        join('specializations','specializations.id','=','user_specialization_rels.specializationId')
        ->join('degrees','degrees.id','=','specializations.degreeId')
        ->join('qualifications','qualifications.id','=','degrees.qualificationId')
         ->where('user_specialization_rels.userId', $userId )
        ->select('specializations.specializationName',
            'degrees.degreeName','qualifications.qualificationName'
        )->get();  
    }


    public function getSkills($skillTypeId)
    {
        return \App\User_skill_rel::
            join('skills','skills.id','=','user_skill_rels.skillId')
            ->join('comepetency_levels','comepetency_levels.id','=','user_skill_rels.comepetencyLevelId')
            ->select('user_skill_rels.id','skills.skillName','comepetency_levels.comepetencyLevelName')
            ->where('user_skill_rels.userId', $this->id)
            ->where('skills.skillTypeId', $skillTypeId)
            ->groupBy('user_skill_rels.id')->get();
    }

    public function getlanguages()
    {
        return \App\User_language_rel::
            join('languages','languages.id','=','user_language_rels.languageId')
            ->select('user_language_rels.*','languages.languageName')
            ->where('user_language_rels.userId', $this->id)
            ->groupBy('user_language_rels.id')->get();
    }

    public static function getSkillChartDetail( $userId , $jobRoleId )
    { 
        $userId = $userId ;
        $jobRoleId = $jobRoleId ;
        $jobRole = \App\Job_role::find($jobRoleId);

        $languages = \App\Language::
            Join('job_role_language_rels', function($join) use ($jobRoleId){
                $join->on('languages.id', '=', 'job_role_language_rels.languageId')
                ->where('job_role_language_rels.jobRoleId',$jobRoleId);
            })
            ->leftjoin('user_language_rels', function($join) use ($userId){
                $join->on('user_language_rels.languageId', '=', 'languages.id');
                //->where('user_language_rels.reading','job_role_language_rels.reading')
                // ->where('user_language_rels.writeing','job_role_language_rels.writeing')
                // ->where('user_language_rels.speaking','job_role_language_rels.speaking')
                // ->where('user_language_rels.understanding','job_role_language_rels.understanding')
                $join->where('user_language_rels.userId',$userId);
            })
            ->select('languages.id','languages.languageName','job_role_language_rels.jobRoleId','job_role_language_rels.reading','job_role_language_rels.writeing','job_role_language_rels.speaking','job_role_language_rels.understanding','user_language_rels.languageId as userLanguageId'
                ,'user_language_rels.reading as userReading','user_language_rels.writeing as userWriteing','user_language_rels.speaking as userSpeaking','user_language_rels.understanding as userUnderstanding')
            ->groupBy('languages.id')->get();  

        $skills = \App\Skill::
            Join('job_role_skill_rels', function($join) use ($jobRoleId){
                $join->on('skills.id', '=', 'job_role_skill_rels.skillId')
                ->where('job_role_skill_rels.jobRoleId',$jobRoleId);
            })
            ->leftjoin('user_skill_rels', function($join) use ($userId){
                $join->on('user_skill_rels.skillId', '=', 'job_role_skill_rels.skillId')
                ->where('user_skill_rels.isApproved',1)
                ->where('user_skill_rels.userId',$userId);
            })
            ->select('skills.id','skills.skillName','skills.skillTypeId','job_role_skill_rels.jobRoleId','job_role_skill_rels.comepetencyLevelId',
                'user_skill_rels.comepetencyLevelId as userComepetencyLevelId'
                )
            ->groupBy('skills.id')->get();  

        $jobRoleSpecializationRels = \App\Job_role_specialization_rel::
            join('specializations', 'specializations.id', '=', 'job_role_specialization_rels.specializationId')
            ->join('degrees','degrees.id','=','specializations.degreeId')
            ->leftjoin('user_specialization_rels', function($join) use ($userId){
                $join->on('user_specialization_rels.specializationId', '=', 'specializations.id')
                ->where('user_specialization_rels.userId',$userId);
            })
            ->where('job_role_specialization_rels.jobRoleId',$jobRoleId)
            ->select('specializations.id as specializationId','specializations.specializationName',
                'degrees.id as degreeId','degrees.degreeName','degrees.qualificationId',
                'user_specialization_rels.specializationId as userSpecializationId'
            )->groupBy('job_role_specialization_rels.id')->get();
        $jobSkilUserSkill = User::getJobSkilUserSkill( $userId , $jobRoleId ) ; 
        return array('languages'=>$languages , 'skills'=>$skills ,'jobRoleSpecializationRels'=>$jobRoleSpecializationRels , 'jobSkilUserSkill'=>$jobSkilUserSkill ,'jobRole'=>$jobRole );
    }
    public static function getUserSkillChartDetail( $userId  ,$jobRoleId )
    {
        $userId = $userId ;
        $jobRoleId = $jobRoleId ;
        $jobRole = \App\Job_role::find($jobRoleId);

        $languages = \App\User_language_rel::join('languages','languages.id','=','user_language_rels.languageId')->where('userId', $userId)->get();
        $skills =  \App\User_skill_rel::
            join('skills','skills.id','=','user_skill_rels.skillId')
            ->select('skills.id','skills.skillName','skills.skillTypeId',
                'user_skill_rels.comepetencyLevelId as comepetencyLevelId')
            ->where('user_skill_rels.userId', $userId )
            ->where('user_skill_rels.isApproved',1)
            ->get();  

        $jobRoleSpecializationRels = \App\User_specialization_rel::
            join('specializations', 'specializations.id', '=', 'user_specialization_rels.specializationId')
            ->join('degrees','degrees.id','=','specializations.degreeId')
            ->where('user_specialization_rels.userId',$userId)
            ->select('specializations.id as specializationId','specializations.specializationName',
                'degrees.id as degreeId','degrees.degreeName','degrees.qualificationId'
            )->groupBy('user_specialization_rels.id')->get();

        return array('languages'=>$languages , 'skills'=>$skills ,'jobRoleSpecializationRels'=>$jobRoleSpecializationRels  ,'jobRole'=>$jobRole );
    }

    public static function getAppyledjob( $reportingToId = FALSE  )
    {
        $jobApply = DB::table('job_apply')
            ->select( DB::raw("DATE_FORMAT(job_roles_apply.created_at, '%M %d, %Y') as created_at") ,'users.name','job_roles.jobRoleName','users.name','users.employeeId','users.image','job_roles_apply.jobRoleName as jobRoleNameApply')
            ->leftJoin('users','users.id','=','job_apply.user_id')
            ->leftJoin('job_roles','job_roles.id','=','users.jobRoleId')
            ->leftJoin('job_roles as job_roles_apply','job_roles_apply.id','=','job_apply.role_id')
            ->where('job_apply.status_id','=', 1);
            if( $reportingToId !== FALSE )
            {
                $jobApply = $jobApply->where('users.reportingToId','=',  \Auth::user()->id );
            }
            $jobApply = $jobApply->get();
            return $jobApply  ;
    }

    public static function getCompetencyPercent( $userId  )
    {

        $competencyPercent = \App\User::
            join('job_role_skill_rels', 'job_role_skill_rels.jobRoleId','=','users.jobRoleId')
            ->join('skills','skills.id','=','job_role_skill_rels.skillId')
            ->join('skill_types','skill_types.id','=','skills.skillTypeId')
            ->leftJoin('user_skill_rels', function($join) {
                $join->on('user_skill_rels.userId', '=', 'users.id')
                ->on('user_skill_rels.skillId', '=', 'job_role_skill_rels.skillId');
            })
            ->select('skills.skillTypeId','skill_types.skillTypeName',  
                DB::raw('
                    (
                    (
                    sum( if( user_skill_rels.id > 0 , 1 ,0 ) )
                    /
                    count(job_role_skill_rels.id )
                    )
                    *
                    100 
                    )
                    as percent'
                 ),
                DB::raw(' sum( if( user_skill_rels.id > 0 , 1 ,0 ) ) as userSkillRelId'),
                DB::raw('count(job_role_skill_rels.id ) as jobRoleKkillRelId'),
                'skills.skillTypeId'
            )
            ->where('users.id', $userId )
            ->groupBy('skills.skillTypeId')->get();
        return $competencyPercent ; 

        // SELECT skills.skillTypeId , 
        // sum( if(job_role_skill_rels.id > 0 , 1 , 0) ) as countTotal ,
        // sum( if(user_skill_rels.id > 0 , 1 , 0) ) as countUser
        // FROM users 
        // inner join job_role_skill_rels on job_role_skill_rels.jobRoleId = users.jobRoleId 
        // inner join skills on skills.id = job_role_skill_rels.skillId 
        // left join user_skill_rels on user_skill_rels.userId = users.id and user_skill_rels.skillId = job_role_skill_rels.skillId 
        // where users.id = 3 group by skills.skillTypeId

    }
    public static function getJobSkilUserSkill( $userId , $jobRoleId = FALSE )
    {
        $id = $userId ; 
        $where = "";
        if($jobRoleId !== FALSE) 
        {
            $where = "where tbl1.id = $jobRoleId";
        }
        
        $result = DB::select( DB::raw("SELECT tbl1.*,job_roles.jobRoleName , divisions.divisionName , job_apply.status_id  From  ( 
        SELECT job_roles.id , COUNT(job_roles.id) as idCount , COUNT( user_skill_rels.id ) as userCount , if( COUNT(job_roles.id) = COUNT( user_skill_rels.id ) , 'Y' , 'N' ) as isAllowed  FROM `job_roles` inner join job_role_skill_rels on job_roles.id = job_role_skill_rels.jobRoleId left join user_skill_rels on user_skill_rels.skillId = job_role_skill_rels.skillId AND user_skill_rels.comepetencyLevelId >= job_role_skill_rels.comepetencyLevelId AND user_skill_rels.isApproved = 1 and user_skill_rels.userId = $userId
        GROUP by job_roles.id
        UNION
        SELECT job_roles.id , COUNT(job_roles.id) as idCount , sum( if(
        job_role_language_rels.reading <= user_language_rels.reading && job_role_language_rels.writeing <= user_language_rels.writeing && job_role_language_rels.speaking <= user_language_rels.speaking && job_role_language_rels.understanding <= user_language_rels.understanding , 1 , 0 )) as userCount , if( COUNT(job_roles.id) = 
        sum( if(job_role_language_rels.reading <= user_language_rels.reading && job_role_language_rels.writeing <= user_language_rels.writeing && job_role_language_rels.speaking <= user_language_rels.speaking && job_role_language_rels.understanding <= user_language_rels.understanding , 1 , 0 )) , 'Y' , 'N' ) as isAllowed 
        FROM `job_roles` inner join job_role_language_rels  on job_roles.id = job_role_language_rels.jobRoleId 
        left join user_language_rels  on user_language_rels.languageId = job_role_language_rels.languageId AND  user_language_rels.userId = $userId 
        GROUP by job_roles.id  
        UNION
        SELECT job_roles.id , COUNT(job_role_specialization_rels.id) as idCount , COUNT( user_specialization_rels.id ) as userCount , 
        if( COUNT(job_role_specialization_rels.id) = 0 OR COUNT( user_specialization_rels.id ) > 0  , 'Y' , 'N' ) as isAllowed  
        FROM job_roles 
        inner join job_role_specialization_rels  on job_roles.id = job_role_specialization_rels.jobRoleId 
        left join user_specialization_rels  on user_specialization_rels.specializationId = job_role_specialization_rels.specializationId and user_specialization_rels.userId = $userId
        GROUP by job_roles.id
        ) as tbl1
        INNER join job_roles on job_roles.id = tbl1.id
        INNER join sub_departments  on sub_departments.id = job_roles.subDepartmentId
        INNER join departments  on departments.id = sub_departments.departmentId
        INNER join divisions on divisions.id = departments.id
        LEFT join job_apply on job_apply.role_id = job_roles.id and job_apply.status_id = 
        '1' and  job_apply.user_id = $userId
        $where
        GROUP by tbl1.id having COUNT(  tbl1.id ) = sum( if( tbl1.isAllowed = 'Y',1,0 ))
        "));
        return $result ;
    }
}