<?php
defined('BASEPATH') or exit('Direct access is not allowed');
/**
 * Automotive Model
 */
class TrainingscheduleModel extends Model
{
  /**
  * Inicia conexión DB
  */
  public function __construct()
  {
    parent::__construct();
  }

  /**
  * Obtiene el usuario de la DB por ID
  */
  public function getUser($id)
  {
    return $this->db->query("SELECT * FROM `usuario` WHERE `id_dev` = $id")->fetch_array(MYSQLI_ASSOC);
  }
  public function get_training_list($selected_segment_id)
  {
    $query="";
    if(empty($selected_segment_id)){
      $query="SELECT * FROM training_details where is_active = '1' ORDER BY name ASC";
    }else{
      $query="SELECT * FROM training_details where segment_id=$selected_segment_id and is_active = '1' ORDER BY name ASC"; 
    }
    $result = $this->db->query($query);
    return $result->fetch_all(MYSQLI_ASSOC);
  }

  public function get_module_list($selected_segment_id)
  { $query="";
    if(empty($selected_segment_id)){
      $query="SELECT * FROM training_module where is_active = '1' order by id desc";
    }else{
      $query="SELECT * FROM training_module where segment_id=$selected_segment_id and is_active = '1' order by id desc"; 
    }
    $result = $this->db->query($query);//ORDER BY module_name ASC
    return $result->fetch_all(MYSQLI_ASSOC);
  }

  public function get_user_list()
  {
    $result = $this->db->query("SELECT * FROM usuarios where is_active = '1' ORDER BY user_name ASC");
    return $result->fetch_all(MYSQLI_ASSOC);
  }

    public function save_training_schedule($data,$userId,$segment_id,$segment_name){ 
      date_default_timezone_set('Asia/Kolkata');
      $sql_query="";
      $start_date = date('Y-m-d H:i:s', strtotime($data["start_date"]));
      $end_date = date('Y-m-d H:i:s', strtotime($data["end_date"]));
      if($data["id"] =="0" || empty($data["id"])){
        $sql_query="INSERT INTO `training_schedule` (`training_name`, `training_id`, `module_name`, `module_id`, `start_date`, `end_date`, `trainer_name`, `trainer_id`, `is_all_day`, `user_id`, `created_date`,`discription`,`is_active`,`segment_id`,`segment_name`,`status`) 
        VALUES ('".$data["training_title"]."', ".$data["training_id"].",'".$data["module_title"]."', ".$data["module_id"].", '".$start_date."', '".$end_date."','".$data["trainer_name"]."',".$data["trainer_id"].",'".$data["is_allday"]."', '$userId',  '".date("Y/m/d")."','".$data["discription"]."', '1','$segment_id','$segment_name','".$data["status"]."')";  
      }else{
          $id=$data["id"];
          $sql_query="UPDATE `training_schedule` SET `start_date` = '".$start_date."', `end_date` = '".$end_date."' WHERE (`id` = '$id')";
          if($data["status"] != ""){
            $sql_query="UPDATE `training_schedule` SET `start_date` = '".$start_date."', `end_date` = '".$end_date."', `status` = '".$data["status"]."' WHERE (`id` = '$id')";   
          }
      }
  
      $result = $this->db->query($sql_query);
     if ($result === TRUE) {
        return 1;
    } else {
        return 0;
    }
    }
    public function delete_training_schedule($data,$userId,$segment_id,$segment_name){ 
      date_default_timezone_set('Asia/Kolkata');
      $sql_query="";
      $start_date = date('Y-m-d H:i:s', strtotime($data["start_date"]));
      $end_date = date('Y-m-d H:i:s', strtotime($data["end_date"]));
      $id=$data["id"];
      $sql_query="UPDATE `training_schedule` SET `is_active` = '0' WHERE (`id` = '$id')";
      $result = $this->db->query($sql_query);
     if ($result === TRUE) {
        return 1;
    } else {
        return 0;
    }
    }
    public function get_schedule_list($selected_segment_id)
    { $query="";
      if(empty($selected_segment_id)){
        $query="SELECT * FROM training_schedule where is_active = '1' order by id desc";
      }else{
        $query="SELECT * FROM training_schedule where segment_id=$selected_segment_id and is_active = '1' order by id desc"; 
      }
      $result = $this->db->query($query);//ORDER BY module_name ASC
      return $result->fetch_all(MYSQLI_ASSOC);
    }

    public function get_schedule_data_count($selected_segment_id)
    { $query="";
      if(empty($selected_segment_id)){
        $query="SELECT
        COUNT(CASE WHEN is_active = '1' THEN '1' END) AS total,
        COUNT(CASE WHEN is_active = '1' AND status = '0' THEN '1' END) AS pending,
        COUNT(CASE WHEN is_active = '1' AND status = '1' THEN '1' END) AS inprogress,
        COUNT(CASE WHEN is_active = '1' AND status = '2' THEN '1' END) AS cancle
    FROM
        training_schedule
    WHERE
        is_active = 1 and segment_id='$selected_segment_id'";
      }else{
        $query="SELECT
        COUNT(CASE WHEN is_active = '1' THEN '1' END) AS total,
        COUNT(CASE WHEN is_active = '1' AND status = '0' THEN '1' END) AS pending,
        COUNT(CASE WHEN is_active = '1' AND status = '1' THEN '1' END) AS inprogress,
        COUNT(CASE WHEN is_active = '1' AND status = '2' THEN '1' END) AS cancle
    FROM
        training_schedule
    WHERE
        is_active = '1'";
      }
      $result = $this->db->query($query);//ORDER BY module_name ASC
      return $result->fetch_all(MYSQLI_ASSOC);
    }
    
}