<?
/*
filename: ingest.php
description:

ingest takes an observation record and inserts it into the database in two tables.
The first insert is to the raw table, the second to the validated table for that station.
The raw insert saves the input record. The station insert converts the Julian date to an SQL
datetime value.

functions:

insertRaw()
insertValidated()
buildDatetime()

sample observation data:

999, 2002, 233, 1400, 32.89, 29.11, 102.4, 28.0, 19.0, 54.43, 134.7, 200.1, 23.6, 80.8,783,4.869, 8967.9, 876.9, 45.8, 87.9, 67.0, 4.78, 6.45, 8.98, 110, 1020.0, 130.0, 120.0, 1.3,0.4, 0.7, 1.2, 8.9, 34, 11.0, 13.0

problems that will cause an error:
no data in query string
wrong number of data items
unable to connect to database server
unable to select database
error inserting into database

when an error occurs, log error, return ERROR string, and save record to failure file.

*/

require('../inc/db_conn.inc');

if (!isset( $_GET['observation']) )
{
//return NO DATA error
echo "ERROR: NO_OBSERVATION_DATA.<br>";
}
else
{

if (!($conn_aiei = @ mysql_pconnect($hostname, $username, $password)))
die(mysql_error());

if (!mysql_select_db($database, $conn_aiei))
die(mysql_error());

$argcnt = 36;
$observation = $_GET['observation'];
$queryData= preg_split('/,/', $observation);
// echo "argc= ".count($queryData);
// echo "<br>obs=".$observation;

if (count($queryData)>=$argcnt)
{
$insertValues = array( "id" => $queryData[0],
"year" => $queryData[1],
"day"=> $queryData[2],
"time" =>$queryData[3] ,
"mean_ta_2m_c"=> $queryData[4],
"min_ta_2m_c"=> $queryData[5],
"max_ta_2m_c"=> $queryData[6],
"mean_ts_10cm_c" => $queryData[7],
"min_ts_10cm_c" => $queryData[8],
"max_ts_10cm_c" => $queryData[9],
"mean_rh_2m_pct"=> $queryData[10],
"min_rh_2m_pct"=> $queryData[11],
"max_rh_2m_pct"=> $queryData[12],
"mean_ws_3m_mps"=> $queryData[13],
"mean_wd_3m_deg" => $queryData[14],
"sd_wd_deg"=> $queryData[15],
"min_ws_3m_mps"=> $queryData[16],
"max_ws_3m_mps"=> $queryData[17],
"rain_tot_mm"=> $queryData[18],
"rain_can_mm" => $queryData[19],
"rad_flx_kjpm2" => $queryData[20],
"mean_dew_c" => $queryData[21],
"mean_vap_kpa" => $queryData[22],
"mean_vap_sat_kpa" => $queryData[23],
"mean_vap_def_kpa" => $queryData[24],
"tot_et_mm" => $queryData[25],
"sm_10cm_pct" => $queryData[26],
"sm_20cm_pct" => $queryData[27],
"sm_30cm_pct" => $queryData[28],
"sm_50cm_pct" => $queryData[29],
"ssf_10cm_real" => $queryData[30],
"ssf_20cm_real" => $queryData[31],
"ssf_30cm_real" => $queryData[32],
"ssf_50cm_real" => $queryData[33],
"it_int" => $queryData[34],
"bv_vot_real" => $queryData[35]);


// must create date_time string before insert

$in_date = buildDatetime($queryData[1],$queryData[2],$queryData[3],$mo,$dd,$hh,$mm,$ss);

echo $in_date."\n";

$valueStr = "id = ".$insertValues['id'].
", yyyy = ".$insertValues['year'].
", jul_day = ".$insertValues['day'].
", hhmm = ".$insertValues['time'].
", date_time = '".$in_date."'".
", mean_ta_2m_c = ".$insertValues['mean_ta_2m_c'].
", min_ta_2m_c = ".$insertValues['min_ta_2m_c'].
", max_ta_2m_c = ".$insertValues['max_ta_2m_c'].
", mean_ts_10cm_c = ".$insertValues['mean_ts_10cm_c'].
", min_ts_10cm_c = ".$insertValues['min_ts_10cm_c'].
", max_ts_10cm_c = ".$insertValues['max_ts_10cm_c'].
", mean_rh_2m_pct = ".$insertValues['mean_rh_2m_pct'].
", min_rh_2m_pct = ".$insertValues['min_rh_2m_pct'].
", max_rh_2m_pct = ".$insertValues['max_rh_2m_pct'].
", mean_ws_3m_mps = ".$insertValues['mean_ws_3m_mps'].
", mean_wd_3m_deg = ".$insertValues['mean_wd_3m_deg'].
", sd_wd_deg = ".$insertValues['sd_wd_deg'].
", min_ws_3m_mps = ".$insertValues['min_ws_3m_mps'].
", max_ws_3m_mps = ".$insertValues['max_ws_3m_mps'].
", rain_tot_mm = ".$insertValues['rain_tot_mm'].
", rain_can_mm = ".$insertValues['rain_can_mm'].
", rad_flx_kjpm2 = ".$insertValues['rad_flx_kjpm2'].
", mean_dew_c = ".$insertValues['mean_dew_c'].
", mean_vap_kpa = ".$insertValues['mean_vap_kpa'].
", mean_vap_sat_kpa = ".$insertValues['mean_vap_sat_kpa'].
", mean_vap_def_kpa = ".$insertValues['mean_vap_def_kpa'].
", tot_et_mm = ".$insertValues['tot_et_mm'].
", sm_10cm_pct = ".$insertValues['sm_10cm_pct'].
", sm_20cm_pct = ".$insertValues['sm_20cm_pct'].
", sm_30cm_pct = ".$insertValues['sm_30cm_pct'].
", sm_50cm_pct = ".$insertValues['sm_50cm_pct'].
", ssf_10cm_real = ".$insertValues['ssf_10cm_real'].
", ssf_20cm_real = ".$insertValues['ssf_20cm_real'].
", ssf_30cm_real = ".$insertValues['ssf_30cm_real'].
", ssf_50cm_real = ".$insertValues['ssf_50cm_real'].
", it_int = ".$insertValues['it_int'].
", bv_vot_real = ".$insertValues['bv_vot_real'];

$insert = "INSERT INTO raw set ".$valueStr;

$result = mysql_query($insert);

if ($result) // if there is a result
{
echo "<br>Number of Inserted Rows: ".mysql_affected_rows();
echo "<br>Data for ".$in_date." has been inserted successfully into the raw table.";
}
else // empty result
{
echo "<br>ERROR:<br>";
echo "<br>".mysql_errno()."<br>";
echo "<br>".mysql_error()."<br>";
}
}
elseif (count($queryData) < $argcnt)
echo "ERROR: wrong number of arguments";

}


function buildDatetime($yyyy,$julian,$hhmm,&$mo,&$dd,&$hh,&$mm,&$ss) {

// the year is set when passed in
// we need to know the month, day, and hour.

$mo_length = array(31,28,31,30,31,30,31,31,30,31,30,31);

// adjust for leap year
if ($yyyy % 4) {
$mo_length[1]=29;
}
$x = $julian;
$x2 = 0;
$i = 0;
while ($x > 0) {
$x2 = $x;
$x -= $mo_length[$i++];
}
$mo = $i;
$dd = $x2;

if (strlen($mo)==1) {
$mo = '0'.$mo;
}
if (strlen($dd)==1) {
$dd = '0'.$dd;
}
/*
if (strlen($hhmm)==3) {
$hh = '0'.substr($hhmm,1);
$mm = ''.substr($hhmm,-2);
}
else {
$hh = substr($hhmm,2);
if ($hh=='24') {
// increment the day
// set hh=00

}
$mm = ''.substr($hhmm,-2);
}
*/
$hh = $hhmm / 100; // [1-24] needs adjustment
$hh -= 1;
if ($hh < 10) $hh = '0'.$hh;
$mm = '00';
$ss = '00';

return $yyyy."-".$mo."-".$dd." ".$hh.":".$mm.":".$ss;

}
?>