<?php
require_once "../../config/settings.inc.php";
require_once "../../include/myview.php";
require_once "../../include/forms.php";
require_once "../../include/database.inc.php";
require_once "../../include/mlib.php";
$t = new MyView();

define("IEM_APPID", 56);
$network = get_str404('network', 'IA_COCORAHS');
$sortcol = get_str404('sortcol', 'ts');

$nselect = selectNetworkType("COCORAHS", $network);

$t->title = "CoCoRaHS Last Observation";
$t->current_network = "CoCoRaHS";

$dbconn = iemdb("coop");
$year = date("Y");
$sql = <<<EOM
with today as (
    select t.id, c.*, t.tzname, t.name, t.county
    from cocorahs_$year c JOIN stations t on (c.iemid = t.iemid)
    WHERE day = 'TODAY' and t.network = $1),
month as (
    SELECT iemid,
    sum(case when precip is not null then 1 else 0 end) as count,
    sum(precip) as pmonth from
    cocorahs_$year s WHERE day >= $2 GROUP by iemid)

select t.*, m.pmonth, m.count,
obvalid at time zone t.tzname as local_valid
from today t JOIN month m on (t.iemid = m.iemid)
ORDER by t.id asc
EOM;
$day1 = new DateTime();
$days_this_month = intval($day1->format("d"));
$stname = iem_pg_prepare($dbconn, $sql);
$rs = pg_execute($dbconn, $stname, array($network, $day1->format("Y-m") . '-01'));
$db = array();
while ($row = pg_fetch_assoc($rs)) {
    $site = $row["id"];
    $db[$site] = array(
        'sid' => $site,
        'ts' => strtotime($row["local_valid"]),
        'name' => $row["name"],
        'county' => $row["county"],
        'pmonth' => $row["pmonth"],
        'count' => $row["count"],
        'snow' => ($row["snow"] >= 0) ? $row["snow"] : " ",
        'snowd' => ($row["snowd"] >= 0) ? $row["snowd"] : " ",
        'ratio' => -1,
        'pday' => $row["precip"],
        'pmiss' => $days_this_month - $row["count"],
    );
    if ($db[$site]["snow"] > 0.0001 && $db[$site]["pday"] > 0.0001) {
        $db[$site]["ratio"] = intval($db[$site]["snow"] / $db[$site]["pday"]);
    }
}
$db = aSortBySecondIndex($db, $sortcol, "desc");

$cols = array(
    "ts" => "Valid",
    "county" => "County",
    "sid" => "Site ID",
    "name" => "Station Name",
    "tmpf" => "Ob Temperature",
    "max_tmpf" => "24 hour High",
    "min_tmpf" => "24 hour Low",
    "snow" => "24 hour Snowfall",
    "snowd" => "Snowfall Depth",
    "pday" => "24 hour rainfall",
    "pmonth" => "Precipitation for Month"
);

$baseurl2 = "current.phtml?sortcol=";
$content = <<<EOM
<p>Sorted by: {$cols[$sortcol]}.
The number of observations missing for this month is shown in parenthesis.

<form method="GET" action="current.phtml" name="stctrl">
<input type="hidden" name="sortcol" value="{$sortcol}">
Option 1: <strong>View by State:</strong> {$nselect}
<input type="submit" value="Go!">
</form>


<form name="st" action="/my/current.phtml" method="GET">
<table class="table table-striped table-sm table-bordered">
<thead class="sticky">
<tr>
  <th rowspan="2">Add:</th>
  <th rowspan="2"><a href="{$baseurl2}sid">SiteID:</a></th>
  <th rowspan="2"><a href="{$baseurl2}name">Station Name:</a></th>
  <th rowspan="2"><a href="{$baseurl2}county">County:</a></th>
  <th rowspan="2"><a href="{$baseurl2}ts">Valid:</a></th>
  <th colspan="5">Hydro</th></tr>

<tr>
  <th><a href="{$baseurl2}pday">24hour Precip</a></th>
  <th><a href="{$baseurl2}pmonth">Month Precip</a></th>
  <th><a href="{$baseurl2}snow">Snowfall</a></th>
  <th><a href="{$baseurl2}ratio">Ratio</a></th>
  <th><a href="{$baseurl2}snowd">Snow Depth</a></th>
</tr></thead>
<tbody>
EOM;
$oddrow = true;
$now = time();
foreach ($db as $site => $value) {
    $tdiff = $now - $value["ts"];
    $obslink = sprintf("obs.phtml?station=%s&network=%s", $site, $network);
    $content .= <<<EOM
<tr>
<th><input type="checkbox" name="st[]" value="{$site}"></th>
<td>
<a href="{$obslink}" alt="View Obs"><i class="bi bi-list"></i></a>
<a href="/sites/site.php?station={$site}&network={$network}">$site</a></td>
<td>{$value["name"]}</td>
<td>{$value["county"]}</td>
EOM;
    $content .= "<td>" . date("h:i A", $value["ts"]) . "</td>";

    if ($value["pday"] == 0.0001) $value["pday"] = "T";
    if ($value["pmonth"] == 0.0001) $value["pmonth"] = "T";
    if ($value["pday"] < 0) $value["pday"] = "M";
    if ($value["snow"] == 0.0001) $value["snow"] = "T";
    if ($value["snowd"] == 0.0001) $value["snowd"] = "T";
    if ($value["snow"] < 0) $value["snow"] = "M";

    $content .= "<td>" . $value["pday"] . "</td>";
    $content .= "<td>" . precip2str($value["pmonth"]);
    if ($value["pmiss"] > 0) $content .= " (" . $value["pmiss"] . ")";
    $content .= "</td>";
    $content .= "<td>" . snow2str($value["snow"]) . "</td>";
    if ($value["ratio"] > 0) $content .= "<td>" . $value["ratio"] . "</td>";
    else $content .= "<td></td>";
    $content .= "<td>" . snow2str($value["snowd"]) . "</td>";
    $content .= "</tr>";
}
$content .= <<<EOM
</tbody>
</table>
<input type="submit" value="Add to Favorites">
</form>
EOM;
$t->content = $content;
$t->render('sortables.phtml');
