<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;
use Auth;
use App\AuditsHistory;
use App\AuditLocation;

class Audits extends Model
{
    protected  $table = "audits";

     public function auditsAssign()
    {
       return $this->hasMany(\App\AuditsAssign::class,'auditId','id');
    }
    
    public static function addStatus($id,$status)
    {
       $audits_history=AuditsHistory::where(array("auditId"=>$id,"status"=>$status))->get();
       if(count($audits_history)<1)
       {
          $auditsins=new AuditsHistory();
          $auditsins->auditId=$id;
          $auditsins->status=$status;
          $auditsins->updatedby=Auth::user()->id;
          $auditsins->save();

          if($status=="start")
          {
              $audits=Audits::find($id);
              $audits->status=$status;
              $audits->save();
          }
          if($status=="submited")
          {
            $audits=Audits::find($id);
              $audits->status=$status;
              $audits->save();
          }
       }
      
    }

    public static function auditFilePercentage($id)
    {
      $totalQuestion=DB::table("audits_questions")->where(array("auditId"=>$id))->count();
      $file=DB::table("audits_questions")->where(array("auditId"=>$id))->where("answer","!=",null)->count();

      return $file*100/$totalQuestion;

    }

    public static function scoringMethodologyFunction($auditId)
    {

        $userType=Auth::user()->type;
        $questionsRs=DB::table('audits_questions')
         ->join("questions","questions.id","audits_questions.questionId")
         ->join("category",'category.id','questions.categoryId')
         ->leftjoin("question_guidelines","questions.id","question_guidelines.questionId")
         ->leftjoin("documents","question_guidelines.documentId","documents.id")
         ->where(array("audits_questions.auditId"=>$auditId))
         ->when($userType,function($q) use ($userType)
            {
                if ($userType=="user" ) {
                  $q->join("audits_actionablepoint","audits_actionablepoint.auditQuestionId","audits_questions.id");
                  $q->where('audits_actionablepoint.assignTo',Auth::user()->id);
                }
            })
         ->select("audits_questions.*","questions.extra_description","questions.ratingCriteria","questions.criticalityLevel","questions.name as qname","category.name as cname","category.id as categoryId","category.parentId",DB::raw("(GROUP_CONCAT(documents.image SEPARATOR '@')) as doc_name"))
         ->groupBy("audits_questions.id")
         ->orderBy("audits_questions.orders","asc")
         ->get();

          $questions=array();
          foreach ($questionsRs as $quest) {
              $quest->id=intval($quest->id);
              $quest->locationId=intval($quest->locationId);
              $quest->questionId=intval($quest->questionId);
              $questions[]=$quest;

          }

      $question_array = \App\Questions::getScoringMethodology($questions,$auditId);

 
       $audit_category_weightage = \App\AuditCategoryWeightage::join("category","category.id","audit_category_weightage.categoryId")
      ->where(array('auditId'=>$auditId))
      ->select("audit_category_weightage.*",DB::raw("(select COUNT(cate.id) from category as cate where cate.parentId=audit_category_weightage.categoryId) as subCategory_count"))
      ->get();
      
       
        $array = array();   
        $array_parent = array();

      // Arrange Question Category Wise(Category and subcategory both)
        for ($i=0; $i <count($audit_category_weightage) ; $i++) 
        {
          for($j=0;$j<count($question_array); $j++)
          {
            if($question_array[$j]['que_cate_id']==$audit_category_weightage[$i]['categoryId'])
            { 
              // Get Subcategory Question
              $array []= array(
                "category_wise_score"=>$question_array[$j]['score_percent'],
                "score_max"=>$question_array[$j]['score_max'],
                "score_achived"=>$question_array[$j]['score_achived'],
                'category_weightage'=>$audit_category_weightage[$i]->weightage,
                'categoryId'=>$question_array[$j]['que_cate_id'],
                'parentId'=>$question_array[$j]['category_parentId'],
              );
            }
            if($question_array[$j]['category_parentId']==$audit_category_weightage[$i]['categoryId'])
            {
              // Get Subcategory Question and arrange in category wise
                $array_parent []=array(
                  "category_wise_score"=>$question_array[$j]['score_percent'],
                  "score_max"=>$question_array[$j]['score_max'],
                "score_achived"=>$question_array[$j]['score_achived'],
                  'category_weightage'=>$audit_category_weightage[$i]->weightage,
                  'categoryId'=>$question_array[$j]['category_parentId'],
                  'parentId'=>$question_array[$j]['category_parentId'],
                );
            }
          }  
        }


          $question_merge = array_merge($array,$array_parent);
          
      // Merge Question score category wise              
        $res  = array();
        foreach($question_merge as $vals){
          if(array_key_exists($vals['categoryId'],$res)){
              $res[$vals['categoryId']]['category_wise_score']    += $vals['category_wise_score'];
              $res[$vals['categoryId']]['score_max']  += $vals['score_max'];
              $res[$vals['categoryId']]['score_achived']  += $vals['score_achived'];
              $res[$vals['categoryId']]['categoryId']  = $vals['categoryId'];
              $res[$vals['categoryId']]['parentId']  = $vals['parentId'];
          }
          else{
              $res[$vals['categoryId']]  = $vals;
          }
        }
        
      //Count Category
        $new_arr = array();
        foreach($question_merge as $k => $v) { 
            $new_arr[$v['categoryId']][]=$v; 
        }

     
      // Get Score Category wise and save in database
        /*print_r($res);
        die();*/
        $score_category_wise= array();
        for ($i=0; $i <count($audit_category_weightage) ; $i++) 
        {     
          if(array_key_exists($audit_category_weightage[$i]['categoryId'],$res))
          {         
            if($res[$audit_category_weightage[$i]['categoryId']]['score_max']!=0)
            {
              $category_score_per = ($res[$audit_category_weightage[$i]['categoryId']]['score_achived']/$res[$audit_category_weightage[$i]['categoryId']]['score_max']);
            }
            else{
              $category_score_per = 0;
            }
              

              $score_category_wise []= array(
                'category_wise_score'=>($audit_category_weightage[$i]->weightage*($category_score_per)),

                'category_weightage'=>$audit_category_weightage[$i]->weightage,
                'categoryId'=>$audit_category_weightage[$i]->categoryId,
                'total_que_in_category'=>count($new_arr[$audit_category_weightage[$i]['categoryId']]),
              );
          }
      
        }
        /*echo "<pre>";
        print_r($score_category_wise);
        die();*/
        $totalScore = 0;
          foreach ($score_category_wise as $arr) {
            \App\AuditCategoryWeightage::updateCategoryPercent($auditId,$arr['categoryId'],$arr['category_wise_score']);
            $totalScore += $arr['category_wise_score'];
          }


          Audits::saveBadgeValue($auditId, $totalScore);
        
          return true;
          
    }

    public static function saveBadgeValue($auditId, $totalScore)
    {
        $departId = DB::table('audits')
            ->join('units','units.id','audits.unitId')
            ->join('departments','departments.id','units.departmentId')
            ->where(array('audits.id'=>$auditId))
            ->select('departments.id')->first();
            
        $badgeValue = DB::table('audits_badge')->where(array('departmentId'=>$departId->id))->get();

        $result="";
        $totalScore = number_format($totalScore,2);
        if(count($badgeValue)>0)
        {
          if($totalScore>$badgeValue[0]->gold_range)
          {
              $badge = "diamond";
          }
          else if($totalScore>$badgeValue[0]->silver_range && $totalScore<=$badgeValue[0]->gold_range)
          {
              $badge = "gold";
          }
          else if($totalScore>$badgeValue[0]->bronze_range && $totalScore<=$badgeValue[0]->silver_range)
          {
              $badge = "silver";
          }
          else if($totalScore>$badgeValue[0]->improvement_range && $totalScore<=$badgeValue[0]->bronze_range)
          {
              $badge = "bronze";
          }
          else if($totalScore<=$badgeValue[0]->improvement_range)
          {
              $badge = "needs_improvement";
          }
          $result = Audits::find($auditId);
          $result->badge = $badge;
          $result->save();
        }

        
        return true;

    }
     public static function addAuditLocation($audit_id,$unit_id){
        $result=AuditLocation::where(array("auditId"=>$audit_id))->count();
        if($result<1)
        {
           $uresults=DB::table("unit_locations")->where(array("unitId"=>$unit_id))->get();
           foreach ($uresults as $uresult) {
                 $AuditLocation=new AuditLocation();
                 $AuditLocation->auditId=$audit_id;
                 $AuditLocation->locationId=$uresult->locationId;
                 $AuditLocation->save();
           }
        }
     }

     public static function IsAuditLocationExist($audit_id){
        $result=AuditLocation::where(array("auditId"=>$audit_id))->count();
        return $result;
       
     }

        public static function sendNotificationForActionPlanAssignmentToUMAdminWhenSubmit($id,$companyId)
    {
      $Actionablepoints=\App\AuditsActionablepoint::
      join("audits_questions","audits_questions.id","audits_actionablepoint.auditQuestionId")
      ->join("users","users.id","audits_actionablepoint.assignTo")
      ->where(array("audits_questions.auditId"=>$id))
      ->whereNotNull("audits_actionablepoint.assignTo")
      ->whereNotNull("audits_actionablepoint.duedate")
      ->select("audits_actionablepoint.*","users.name as username","users.email as useremail","users.type as usertype")
      ->get();

      $audits = Audits::where(array("id"=>$id))->get();
      $unit = \App\Units::where(array("id"=>$audits[0]->unitId))->get();
      $manager = \App\User::where(array("id"=>$audits[0]->managerId))->get();
      $admin = \App\User::where(array('companyId'=>$companyId,'type'=>'admin'))->get();

      $foradmin= '<table width="100%" cellpadding="10" cellspacing="0" style="margin:0 auto; border:1px solid #ddd"><tr>
              <th style="border-bottom:1px solid #ddd;">Actions</th>
              <th style="border-bottom:1px solid #ddd; border-left:1px solid #ddd">Deadline</th></tr>';
 

       $template_id=config("sendgridTemplateIds.Notification_for_Action_Plan_Assignment_to_Admin_User_Manager");
      $from_name='Admin';
      $from_email=config('constants.admin_email');
      
      $subject ="Notification for Action Plan Assignment";

      $action_assignment_date = date("d-M-Y");
      $audit_name = $audits[0]->title;
      $submission_date = $audits[0]->auditdate;
      $site_name = $unit[0]->name;
       $user_name="--";


      foreach ($Actionablepoints as $Actionablepoint) {
  
      $actionable_point=$Actionablepoint->title;
      $action_assignment_date = date("d-M-Y",strtotime($Actionablepoint->duedate));
  
   
      $action_deadline_table = '<table width="100%" cellpadding="10" cellspacing="0" style="margin:0 auto; border:1px solid #ddd"><tr>
              <th style="border-bottom:1px solid #ddd;">Actions</th>
              <th style="border-bottom:1px solid #ddd; border-left:1px solid #ddd">Deadline</th></tr>';
      $action_deadline_table .='<tr><td style="border-bottom:1px solid #ddd;">'.$actionable_point.'</td>';
      $action_deadline_table .='<td style="border-bottom:1px solid #ddd; border-left:1px solid #ddd">'.$action_assignment_date.'</td></tr></table>';

      $foradmin.='<tr><td style="border-bottom:1px solid #ddd;">'.$actionable_point.'</td>';
      $foradmin .='<td style="border-bottom:1px solid #ddd; border-left:1px solid #ddd">'.$action_assignment_date.'</td></tr>';



      
      $user_name=$Actionablepoint->username;


    if($Actionablepoint->useremail!="")
    {
      $user_type = ucfirst($Actionablepoint->usertype);
      $to_name = $user_name;  
      $to_email = $Actionablepoint->useremail; 
     

      $data = array(':admin_email' => array($from_email),':user_name' => array($user_name),':user_email' => array($to_email),':audit_name' => array($audit_name),':site_name' => array($site_name),':submission_date' => array($submission_date),':action_assignment_date' => array($action_assignment_date),':user_type' => array($user_type),':action_deadline_table' => array($action_deadline_table));
          
      \App\Sendgrid::send_mail( $subject , $template_id , $data , $to_email , $to_name, $from_email );  
      }
    
    }
    $foradmin .='</table>';

      if($manager[0]->type=="manager")
      {
        $user_type = ucfirst($manager[0]->type);
        $to_name = $manager[0]->name;  
        $to_email = $manager[0]->email;
         $to_email = "vikram.imenso@gmail.com";

        $data = array(':admin_email' => array($from_email),':user_name' => array($user_name),':user_email' => array($to_email),':audit_name' => array($audit_name),':site_name' => array($site_name),':submission_date' => array($submission_date),':action_assignment_date' => array($action_assignment_date),':user_type' => array($user_type),':action_deadline_table' => array($foradmin));
          
        \App\Sendgrid::send_mail( $subject , $template_id , $data , $to_email , $to_name, $from_email );  
        
      }
      if($admin[0]->type=="admin")
      {
        
        $user_type = ucfirst($admin[0]->type);
        $to_name = $admin[0]->name;  
        $to_email = $admin[0]->email;
        $to_email = "mahesh.imenso@gmail.com";


        $data = array(':admin_email' => array($from_email),':user_name' => array($user_name),':user_email' => array($to_email),':audit_name' => array($audit_name),':site_name' => array($site_name),':submission_date' => array($submission_date),':action_assignment_date' => array($action_assignment_date),':user_type' => array($user_type),':action_deadline_table' => array($foradmin));
          
        \App\Sendgrid::send_mail( $subject , $template_id , $data , $to_email , $to_name, $from_email );  
      }
  }

     
}