<?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) {
    if (isset($pw_id) && isset($wo_id)) {
      $this->pw_id = $pw_id;
      $this->wo_id = $wo_id;
    }  
    else {
      $this->pw_id = 0; 
      $this->wo_id = 0; 
	}
    
    $dal = new DAL();
      
    $sql = "SELECT PE.id, " .
           "CASE WHEN " .
              "(LENGTH(surname) < 10 && LENGTH(first_name) < 10) " . 
                "THEN CONCAT(surname,', ',first_name) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(first_name,1,1)) " . 
            "END AS text " .
           "FROM  personnel PE  " .
           "LEFT JOIN (projectworkdaypersonnel PWP, projectworkday PW) " .
           "ON (PE.id = PWP.personnel_id AND PWP.projectworkday_id = PW.id " .
           "AND PW.workday_id = $this->wo_id) " .
           "WHERE PWP.personnel_id IS NULL " .
           "ORDER BY surname ";
    $this->avail_crew = $dal->query($sql);

    $sql = "SELECT PE.id, " .
           "CASE WHEN " .
              "(LENGTH(surname) < 10 && LENGTH(first_name) < 10) " . 
                "THEN CONCAT(surname,', ',first_name) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(first_name,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(first_name) < 10) " . 
                "THEN CONCAT(surname,', ',first_name) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(first_name,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(first_name) < 10) " . 
                "THEN CONCAT(surname,', ',first_name) " . 
                "ELSE CONCAT(surname,', ',SUBSTRING(first_name,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.first_name) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.first_name) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.first_name,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.first_name) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.first_name) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.first_name,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.first_name) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.first_name) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.first_name,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.first_name) < 10) " . 
                "THEN CONCAT(PE.surname,', ',PE.first_name) " . 
                "ELSE CONCAT(PE.surname,', ',SUBSTRING(PE.first_name,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;
  }

}

?>
