<?php
defined('PUBLIC_ACCESS') or die('Restricted Access');

require_once($_SERVER['DOCUMENT_ROOT'] . '/sys/db.php');

class modCrew {

  private $pw_id;
  private $wo_id;
  private $wo_ts;
  private $avail_crew;
  private $assign_crew;
  private $absent_crew;
  private $team_roles;
  private $status;
  private $to_vehicle;
  private $to_ve_id;
  private $from_vehicle;
  private $from_ve_id;
  private $no_tovehicle;
  private $no_fromvehicle;
  private $in_tovehicle;
  private $in_fromvehicle;
  private $hour_range = "0,24";
  private $dur_range = "0,9";
  private $min_list  = "00,15,30,45";
  private $frac_list = "00,25,50,75";

  public function __construct($pw_id, $wo_id) {

    $this->pw_id = isset($pw_id) ? $pw_id : 0;
    $this->wo_id = isset($wo_id) ? $wo_id : 0;

    $dal = new DAL();
      
    $sql = "SELECT PW.id, FW.start_time, FW.end_time, PWP.personnel_id " .
           "FROM projectworkday PW, fieldworkday FW, " . 
           "projectworkdaypersonnel PWP " .
           "WHERE PW.workday_id = $this->wo_id AND PW.id <> $this->pw_id " .
           "AND FW.projectworkday_id = PW.id AND PWP.projectworkday_id = PW.id " .
           "ORDER By PWP.id, FW.start_time  " ;
    $timeSpans = $dal->query($sql);


    $sql = "SELECT PW.id, FW.start_time, FW.end_time, WO.date " . 
           "FROM projectworkday PW, fieldworkday FW, workday WO " .
           "WHERE PW.id = $this->pw_id AND FW.projectworkday_id = PW.id " .
           "AND WO.id = PW.workday_id LIMIT 1 " ;
    $pw = $dal->query($sql);

    $start_time = $pw->start_time;
    $end_time = $pw->end_time;
    $date = $pw->date;

    $sql = "SELECT PE.id, " .
           "(CASE WHEN (LENGTH(surname) < 10 && LENGTH(firstname) < 10) " .
           "THEN CONCAT(surname,', ',firstname) " .
           "ELSE CONCAT(surname,', ',SUBSTRING(firstname,1,1)) END) AS text " .
           "FROM personnel PE " .
           "ORDER BY text " ;
    $this->avail_crew = $dal->query($sql);

//    $sql = "SELECT PE.id, FW.start_time, FW.end_time, WO.textdate, " .
//           "FW.break_dur, PTR.moduration, " .
//           "(CASE WHEN (LENGTH(surname) < 10 && LENGTH(firstname) < 10) " .
//           "THEN CONCAT(surname,', ',firstname) " .
//           "ELSE CONCAT(surname,', ',SUBSTRING(firstname,1,1)) END) AS text, " .
//           "(CASE WHEN PWP.id IS NULL THEN 'Yes' ELSE 'No' END) AS available " .
//           "FROM personnel PE " .
//           "LEFT JOIN (projectworkdaypersonnel PWP, projectteamrole PTR, " .
//           "projectworkday PW, fieldworkday FW, workday WO) " .
//           "ON PWP.personnel_id = PE.id AND PW.id = PWP.projectworkday_id " .
//           "AND FW.projectworkday_id = PW.id AND WO.id = PW.workday_id " .
//           "AND WO.date = '$date' AND PTR.projectworkdaypersonnel_id = PWP.id ";
//    $this->avail_crew = $dal->query($sql);

//    foreach ($this->avail_crew as $pw=>$key) {
//      if ($key->available == 'No') {
          
//        if ($key->moduration == 0)
//          $end_mod = $key->end_time;
//        else {
//          $end_dec = (substr($key->start_time, 0, 2) + 
//                     substr($key->start_time, 3, 2)/60) +
//                     $key->moduration + $key->break_dur;
//          $hours = floor($end_dec);
//          $minutes = ($end_dec % 1) * 60;
//          $end_mod = sprintf("%02d", $hours) . ':' .
//                     sprintf("%02d", $minutes);
//        }  
//        if ($end_mod >= $end_time)
//         unset ($this->avail_crew[$pw]);
//        if ($key->id == $previousId)
//         unset ($this->avail_crew[$pw]);
//      }
//    }


    $sql = "SELECT PE.id, " .
           "CASE WHEN " .
              "(LENGTH(surname) < 10 && LENGTH(firstname) < 10) " . 
                "THEN CONCAT(surname,', ',firstname) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(firstname,1,1)) " . 
            "END AS text " .
           "FROM projectworkdaypersonnel PWP, personnel PE, projectteamrole PTR " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND PE.id = PWP.personnel_id " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id <> 7 " .
           "GROUP BY PE.id ORDER BY surname";
    $this->assign_crew = $dal->query($sql);

    $sql = "SELECT PE.id, " .
           "CASE WHEN " .
              "(LENGTH(surname) < 10 && LENGTH(firstname) < 10) " . 
                "THEN CONCAT(surname,', ',firstname) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(firstname,1,1)) " . 
            "END AS text " .
           "FROM projectworkdaypersonnel PWP, personnel PE, projectteamrole PTR " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND PWP.personnel_id = PE.id " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id = 7 " .
           "ORDER BY surname ";
    $this->absent_crew = $dal->query($sql);

    $sql = "SELECT PTR.id AS id, PTR.teamrole_id AS role_id, PE.id AS pe_id, " .
           "CASE WHEN " .
              "(LENGTH(surname) < 10 && LENGTH(firstname) < 10) " . 
                "THEN CONCAT(surname,', ',firstname) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(firstname,1,1)) " . 
           "END AS text, " .
           "CONCAT(PE.id, '-', PTR.id) as roleorder " .
           "FROM projectworkdaypersonnel PWP, personnel PE, projectteamrole PTR " .
           "WHERE PWP.projectworkday_id = $this->pw_id " .
           "AND PE.id = PWP.personnel_id AND PTR.projectworkdaypersonnel_id = PWP.id " .
           "ORDER BY text, PTR.id ";
    $this->crew_role = $dal->query($sql);
    $currid = 0;
    foreach ($this->crew_role as $role) {
      if ($currid == $role->pe_id)
        $role->addedFlag = 1;
      else {
        $role->addedFlag = 0;
        $currid = $role->pe_id;
      }    
    }

    $sql = "SELECT id, name as text FROM teamrole " .
           "ORDER BY name ";
    $this->team_roles = $dal->query($sql);

    $this->team_roledef = 2;

    $sql = "SELECT id, name as text FROM travelrole " .
           "ORDER BY name ";
    $this->travel_roles = $dal->query($sql);

    $this->travel_roledef = 3;
          
    $sql = "SELECT TR.id, CONCAT (VE.name, ' (', VE.seating, ') ', VE.descr) text " .
           "FROM (travel TR, vehicle VE, projectworkdaypersonnel PP) " .
           "LEFT JOIN personneltravel PT ON PT.travel_id = TR.id " .
           "WHERE PT.projectworkdaypersonnel_id = PP.id " .
           "AND PP.projectworkday_id = $this->pw_id AND TR.destination = 'to' " .
           "AND VE.id = TR.vehicle_id " .
           "GROUP BY TR.id ";
    $this->to_vehicle = $dal->query($sql);
 
    // Send first travel id (default) in Vehicle selector to in_tovehicle query
    if (count($this->to_vehicle) > 0)
      $this->to_tr_id = reset($this->to_vehicle)->id;
    else
      $this->to_tr_id = 0;
    
    $sql = "SELECT TR.id, CONCAT (VE.name, ' (', VE.seating, ') ', VE.descr) text  " .
           "FROM (travel TR, vehicle VE, projectworkdaypersonnel PP) " .
           "LEFT JOIN personneltravel PT ON PT.travel_id = TR.id " .
           "WHERE PT.projectworkdaypersonnel_id = PP.id " .
           "AND PP.projectworkday_id = $this->pw_id AND TR.destination = 'from' " .
           "AND VE.id = TR.vehicle_id " .
           "GROUP BY TR.id ";
    $this->from_vehicle = $dal->query($sql); 
    // Send first travel id (default) in Vehicle selector to in_from vehicle query
    if (count($this->from_vehicle) > 0)
      $this->from_tr_id = reset($this->from_vehicle)->id;
    else
      $this->from_tr_id = 0;
  
    $sql = "SELECT PWP.id, " .
           "CASE WHEN " .
              "(LENGTH(PE.surname) < 10 && LENGTH(PE.firstname) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.firstname) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.firstname,1,1)) " . 
           "END AS text, " .
           "PT.travelrole_id AS role_id, PT.id as pt_id " .
           "FROM projectworkdaypersonnel PWP, projectteamrole PTR, " .
           "personnel  PE, travel TR, personneltravel PT " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND TR.id = $this->to_tr_id " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id <> 7 " .
           "AND PWP.personnel_id = PE.id AND PT.projectworkdaypersonnel_id = PWP.id " .
           "AND PT.travel_id = TR.id AND TR.destination = 'to' " . 
           "GROUP BY PWP.id ORDER BY surname ";
    $this->in_tovehicle =  $dal->query($sql);

    $sql = "SELECT PWP.id, " .
           "CASE WHEN " .
              "(LENGTH(PE.surname) < 10 && LENGTH(PE.firstname) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.firstname) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.firstname,1,1)) " . 
           "END AS text, " .
           "PT.travelrole_id AS role_id, PT.id as pt_id " .
           "FROM projectworkdaypersonnel PWP, projectteamrole PTR, " .
           "personnel  PE, travel TR, personneltravel PT  " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND TR.id = $this->from_tr_id " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id <> 7 " .
           "AND PWP.personnel_id = PE.id AND PT.projectworkdaypersonnel_id = PWP.id " .
           "AND PT.travel_id = TR.id AND TR.destination = 'from' " . 
           "GROUP BY PWP.id ORDER BY surname ";
    $this->in_fromvehicle =  $dal->query($sql);

    $sql = "SELECT PWP.id, " .
           "CASE WHEN " .
              "(LENGTH(PE.surname) < 10 && LENGTH(PE.firstname) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.firstname) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.firstname,1,1)) " . 
            "END AS text, " .
           "PT.projectworkdaypersonnel_id " .
           "FROM (projectworkdaypersonnel PWP, personnel PE, projectteamrole PTR) " .
           "LEFT JOIN (personneltravel PT, travel TR) " .
           "ON PWP.id = PT.projectworkdaypersonnel_id " .
           "AND TR.id = PT.travel_id AND TR.destination = 'to' " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND PT.projectworkdaypersonnel_id IS NULL " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id <> 7 " .
           "AND PE.id = PWP.personnel_id " .
           "GROUP BY PWP.id ORDER BY PE.surname ";
    $this->no_tovehicle = $dal->query($sql);
    
    $sql = "SELECT PWP.id, " .
           "CASE WHEN " .
              "(LENGTH(PE.surname) < 10 && LENGTH(PE.firstname) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.firstname) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.firstname,1,1)) " . 
            "END AS text, " .
           "PT.projectworkdaypersonnel_id " .
           "FROM (projectworkdaypersonnel PWP, personnel PE, projectteamrole PTR) " .
           "LEFT JOIN (personneltravel PT, travel TR) " .
           "ON PWP.id = PT.projectworkdaypersonnel_id " .
           "AND TR.id = PT.travel_id AND TR.destination = 'from' " .
           "WHERE PWP.projectworkday_id = $this->pw_id AND PT.projectworkdaypersonnel_id IS NULL " .
           "AND PTR.projectworkdaypersonnel_id = PWP.id AND PTR.teamrole_id <> 7 " .
           "AND PE.id = PWP.personnel_id " .
           "GROUP BY PWP.id ORDER BY PE.surname ";
    $this->no_fromvehicle = $dal->query($sql);

    $sql = "SELECT TR.id AS tr_idto, type AS to_type, start_time AS to_start_time, " .
           "PT.duration AS to_duration, mileage AS to_mileage " .
           "FROM travel TR, personneltravel PT " .
           "WHERE TR.id = $this->to_tr_id AND TR.id = PT.travel_id " .
           "AND PT.travelrole_id = 1 LIMIT 1 ";
    $this->to_measures = $dal->query($sql);
    
    if (count($this->to_measures) == 0)
      $this->to_measures = (object)array("tr_idto"=>"$this->to_tr_id", "to_type"=>"Day",
        "to_start_time"=>"07:30", "to_duration"=>"00.50", "to_mileage"=>"0.00");

    $sql = "SELECT TR.id AS tr_idfrom, type AS from_type, start_time AS from_start_time, " .
           "PT.duration AS from_duration, mileage AS from_mileage " .
           "FROM travel TR, personneltravel PT " .
           "WHERE TR.id = $this->from_tr_id AND TR.id = PT.travel_id " .
           "AND PT.travelrole_id = 1 LIMIT 1 ";
    $this->from_measures = $dal->query($sql);
    
    if (count($this->from_measures) == 0)
      $this->from_measures = (object)array("tr_idfrom"=>"$this->from_tr_id", "from_type"=>"Day",
        "from_start_time"=>"07:30", "from_duration"=>"00.50", "from_mileage"=>"0.00");
                                           
  }

  public function get($prop) {
    return $this->$prop;
  }

}

?>
