<?php
require_once "../../config/settings.inc.php";
require_once "../../include/myview.php";
require_once "../../include/database.inc.php";
require_once "../../include/forms.php";

$t = new MyView();
$dbconn = iemdb("iem");
$sortcol = isset($_GET['sortcol']) ? xssafe($_GET['sortcol']) : 'valid';
$vals = array(
    "sname" => "Station Name",
    "valid" => "Observation Time",
    "tmpf_1in" => "1 inch Depth Temperature",
    "tmpf_3in" => "3 inch Depth Temperature",
    "tmpf_6in" => "6 inch Depth Temperature",
    "tmpf_9in" => "9 inch Depth Temperature",
    "tmpf_12in" => "12 inch Depth Temperature",
    "tmpf_18in" => "18 inch Depth Temperature",
    "tmpf_24in" => "24 inch Depth Temperature",
    "tmpf_30in" => "30 inch Depth Temperature",
    "tmpf_36in" => "36 inch Depth Temperature",
    "tmpf_42in" => "42 inch Depth Temperature",
    "tmpf_48in" => "48 inch Depth Temperature",
    "tmpf_54in" => "54 inch Depth Temperature",
    "tmpf_60in" => "60 inch Depth Temperature",
    "tmpf_66in" => "66 inch Depth Temperature",
    "tmpf_72in" => "72 inch Depth Temperature",
);
if (!array_key_exists($sortcol, $vals)){
    die("Invalid sortcol provided");
}

/* Query out the data */
$stname = iem_pg_prepare($dbconn, "select t.valid, s.name as sname,
    max(case when sensor_id = 1 then temp else null end) as tmpf_1in,
    max(case when sensor_id = 3 then temp else null end) as tmpf_3in,
    max(case when sensor_id = 6 then temp else null end) as tmpf_6in,
    max(case when sensor_id = 9 then temp else null end) as tmpf_9in,
    max(case when sensor_id = 12 then temp else null end) as tmpf_12in,
    max(case when sensor_id = 18 then temp else null end) as tmpf_18in,
    max(case when sensor_id = 24 then temp else null end) as tmpf_24in,
    max(case when sensor_id = 30 then temp else null end) as tmpf_30in,
    max(case when sensor_id = 36 then temp else null end) as tmpf_36in,
    max(case when sensor_id = 42 then temp else null end) as tmpf_42in,
    max(case when sensor_id = 48 then temp else null end) as tmpf_48in,
    max(case when sensor_id = 54 then temp else null end) as tmpf_54in,
    max(case when sensor_id = 60 then temp else null end) as tmpf_60in,
    max(case when sensor_id = 66 then temp else null end) as tmpf_66in,
    max(case when sensor_id = 72 then temp else null end) as tmpf_72in
      from rwis_soil_data t, stations s
      where s.remote_id = t.location_id GROUP by sname, valid
      ORDER by $sortcol DESC NULLS LAST");
$rs = pg_execute($dbconn, $stname, array());

$t->title = "RWIS Current Soil Probe Data";
$t->refresh = 600;

$t->current_network = "RWIS Soil";

function fancytime($strtime)
{
    $now = time();
    $ts = strtotime($strtime);
    if (($now - $ts) < 600) {
        return date("h:i A", $ts);
    } else {
        return date("M d h:i A", $ts);
    }
}
function f($val)
{
    return $val;
}


$data = array();
$table  = "";
while ($row = pg_fetch_assoc($rs)) {
    $table .= sprintf(
        "<tr><th>%s</th><th>%s</th>
   <td>%s</td><td>%s</td><td>%s</td><td>%s</td>
   <td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>
   <td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>
   <td>%s</td></tr>\n",
        $row["sname"],
        fancytime($row["valid"]),
        f($row["tmpf_1in"]),
        f($row["tmpf_3in"]),
        f($row["tmpf_6in"]),
        f($row["tmpf_9in"]),
        f($row["tmpf_12in"]),
        f($row["tmpf_18in"]),
        f($row["tmpf_24in"]),
        f($row["tmpf_30in"]),
        f($row["tmpf_36in"]),
        f($row["tmpf_42in"]),
        f($row["tmpf_48in"]),
        f($row["tmpf_54in"]),
        f($row["tmpf_60in"]),
        f($row["tmpf_66in"]),
        f($row["tmpf_72in"])
    );
}

$t->content = <<<EOM
<h3>Iowa RWIS Soil Probe Data</h3>

<p>** This page will automatically refresh every 10 minutes.
<br>Click on a column to sort it.</p>

<p>Sorted by: <b>{$vals[$sortcol]}</b><br>

<table style="width: 100%; font-size: 10pt;" cellpadding="1" cellspacing="0"
  border="1">
<thead>
<tr>
  <th rowspan="2"><a href="soil.phtml?sortcol=sname">Station:</a></th>
  <th rowspan="2"><a href="soil.phtml?sortcol=valid">Ob Time</a></th>
  <th colspan="15">Soil Problems Located at Specified Depth</th>
</tr>
<tr>
  <th><a href="soil.phtml?sortcol=tmpf_1in">1</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_3in">3</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_6in">6</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_9in">9</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_12in">12</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_18in">18</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_24in">24</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_30in">30</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_36in">36</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_42in">42</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_48in">48</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_54in">54</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_60in">60</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_66in">66</a></th>
  <th><a href="soil.phtml?sortcol=tmpf_72in">72</a></th>
</tr>
</thead>
<tbody>
{$table}
</tbody>
</table>

</form>

<br><br>
EOM;
$t->render('sortables.phtml');
