<?php

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

foreach ($_POST as $paramName => $paramValue) {
  if ($paramName == "selectid")
    $wd_id = $paramValue;
  else if ($paramName == "column")
    $column = $paramValue;
}

$dal = new DAL();

if ($column == "left") {
  $sql = "SELECT PE.id, " .
         "(CASE WHEN (LENGTH(lastname) < 10 && LENGTH(firstname) < 10) " .
         "THEN CONCAT(lastname,', ',firstname) " .
         "ELSE CONCAT(lastname,', ',SUBSTRING(firstname,1,1)) END) AS text " .
         "FROM personnel PE " .
         "LEFT JOIN shiftcrew SC ON (SC.personnel_id = PE.id AND SC.workday_id = $wd_id) " .
         "WHERe SC.id IS NULL " .
         "ORDER BY text " ;
  $result = $dal->query($sql);
}

else if ($column == "middle") {
  $sql = "SELECT SC.id, " .
         "(CASE WHEN (LENGTH(lastname) < 10 && LENGTH(firstname) < 10) " .
         "THEN CONCAT(lastname,', ',firstname) " .
         "ELSE CONCAT(lastname,', ', SUBSTRING(firstname,1,1)) END) AS text " .
         "FROM personnel PE, shiftcrew SC " .
         "WHERE SC.personnel_id = PE.id AND SC.workday_id = $wd_id AND SC.supervisor = 0 " .
         "ORDER BY text " ;
  $result = $dal->query($sql);
}

else if ($column == "right") {
  $sql = "SELECT SC.id, " .
         "(CASE WHEN (LENGTH(lastname) < 10 && LENGTH(firstname) < 10) " .
         "THEN CONCAT(lastname,', ',firstname) " .
         "ELSE CONCAT(lastname,', ',SUBSTRING(firstname,1,1)) END) AS text " .
         "FROM personnel PE, shiftcrew SC " .
         "WHERE SC.personnel_id = PE.id AND SC.workday_id = $wd_id AND SC.supervisor = 1 " .
         "ORDER BY text " ;
    $result = $dal->query($sql);
}

echo json_encode($result);

?>

