private static ValueSingleVariable CreateNoDataValue(DateTime time, TsValuesSingleVariableType s, int variableId)
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(time);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
switch (variableId)
{
case 1:
//for precipitation, set 'no data' to zero
v.Value = 0.0M;
break;
case 4:
v.Value = -9999.0M;
break;
case 5:
v.Value = -9999.0M;
break;
case 8:
v.Value = 0.0M;
break;
case 16:
v.Value = -9999.0M;
break;
default:
v.Value = -9999.0M;
break;
}
return v;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableId, DateTime startDateTime, DateTime endDateTime)
{
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = new MethodType();
int varId = Convert.ToInt32(variableId);
if (varId == 8)
{
s.method[0].methodCode = "1";
s.method[0].methodID = 1;
s.method[0].methodDescription = "Snow measured at 6:00Z on open ground";
s.method[0].methodLink = "hydro.chmi.cz/hpps";
}
else if (varId == 1)
{
s.method[0].methodCode = "2";
s.method[0].methodID = 2;
s.method[0].methodDescription = "Precipitation measured by tipping bucket";
s.method[0].methodLink = "hydro.chmi.cz/hpps";
}
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
s.source = new SourceType[1];
s.source[0] = new SourceType();
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "d";
s.units.unitCode = "d";
s.units.unitID = 0;
s.units.unitName = "day";
s.units.unitType = "Time";
//values: get from database...
string connStr = GetConnectionString();
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("qry_observations", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@st_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@var_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@start_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@time_step", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@group_function", SqlDbType.VarChar));
cmd.Parameters["@st_id"].Value = Convert.ToInt32(siteId);
cmd.Parameters["@var_id"].Value = Convert.ToInt32(variableId);
cmd.Parameters["@start_time"].Value = startDateTime;
cmd.Parameters["@end_time"].Value = endDateTime;
cmd.Parameters["@time_step"].Value = "day";
cmd.Parameters["@group_function"].Value = "max";
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
if (varId == 8)
{
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = "1";
v.methodID = "1";
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = Convert.ToDecimal(r["obs_value"]);
if (v.Value == -1) v.Value = 0.1M;
if (v.Value == -2) v.Value = 0.2M;
valuesList.Add(v);
}
}
else
{
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]).AddHours(-19);
//v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = "2";
v.methodID = "2";
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = Convert.ToDecimal(Convert.ToDouble(r["obs_value"]) * 0.1);
if (v.Value == -1) v.Value = 0.1M;
if (v.Value == -2) v.Value = 0.2M;
valuesList.Add(v);
}
}
}
}
//convert list to array
DateTime beginDate = valuesList[0].dateTime;
DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
int numDays = endDate.Subtract(beginDate).Days;
ValueSingleVariable[] valuesArray = new ValueSingleVariable[numDays];
int valueIndex = 0;
DateTime curDate = beginDate;
foreach(ValueSingleVariable val in valuesList)
{
if (valueIndex >= valuesArray.Length) break;
while (curDate < val.dateTime)
{
valuesArray[valueIndex] = CreateNoDataValue(curDate);
curDate = curDate.AddDays(1);
valueIndex++;
}
if (valueIndex >= valuesArray.Length) break;
valuesArray[valueIndex] = val;
curDate = val.dateTime.AddDays(1);
valueIndex++;
}
s.value = valuesArray;
return s;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableCode, DateTime startDateTime, DateTime endDateTime)
{
//numeric variable id
int varId = VariableCodeToID(variableCode);
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = GetMethodForVariable(varId);
string timeStep = "hour";
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "hr";
s.units.unitCode = "103";
s.units.unitID = 104;
s.units.unitName = "hour";
s.units.unitType = "Time";
timeStep = "hour";
//method
s.method[0] = GetMethodForVariable(varId);
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
//TODO: read the correct source
s.source = new SourceType[1];
s.source[0] = GetSourceForSite(Convert.ToInt32(siteId));
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//values: get from database...
string connStr = GetConnectionString();
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("plaveninycz.new_query_observations", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@st_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@var_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@start_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@time_step", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@group_function", SqlDbType.VarChar));
cmd.Parameters["@st_id"].Value = Convert.ToInt32(siteId);
cmd.Parameters["@var_id"].Value = varId;
cmd.Parameters["@start_time"].Value = startDateTime;
cmd.Parameters["@end_time"].Value = endDateTime;
cmd.Parameters["@time_step"].Value = timeStep;
cmd.Parameters["@group_function"].Value = "sum";
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
int obsTimeIndex = r.GetOrdinal("obs_time");
int obsValueIndex = r.GetOrdinal("obs_value");
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(r["obs_value"], varId);
valuesList.Add(v);
}
}
}
s.value = valuesList.ToArray();
////convert list to array for temperature
//if (varId == 16)
//{
// s.value = valuesList.ToArray();
//}
//else
//{
// //convert list to array - for precip, snow, discharge, stage
// DateTime beginDate = valuesList[0].dateTime;
// DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
// int numHours = endDate.Subtract(beginDate).Hours;
// ValueSingleVariable[] valuesArray = new ValueSingleVariable[numHours];
// int valueIndex = 0;
//
// DateTime curDate = beginDate;
// foreach (ValueSingleVariable val in valuesList)
// {
// if (valueIndex >= valuesArray.Length) break;
//
// while (curDate < val.dateTime)
// {
// valuesArray[valueIndex] = CreateNoDataValue(curDate,s, varId);
// curDate = curDate.AddHours(1);
// valueIndex++;
// }
//
// if (valueIndex >= valuesArray.Length) break;
// valuesArray[valueIndex] = val;
// curDate = val.dateTime.AddHours(1);
// valueIndex++;
// }
// s.value = valuesArray;
//}
return s;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableCode, DateTime startDateTime, DateTime endDateTime)
{
//numeric variable id
int varId = VariableCodeToID(variableCode);
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = GetMethodForVariable(varId);
string timeStep = "hour";
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "hr";
s.units.unitCode = "103";
s.units.unitID = 104;
s.units.unitName = "hour";
s.units.unitType = "Time";
timeStep = "hour";
//method
s.method[0] = GetMethodForVariable(varId);
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
//TODO: read the correct source
s.source = new SourceType[1];
s.source[0] = GetSourceForSite(Convert.ToInt32(siteId));
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//values: get from database...
string binFileName = BinaryFileHelper.GetBinaryFileName(Convert.ToInt32(siteId), variableCode.ToLower(), "h");
BinaryFileData dataValues = BinaryFileHelper.ReadBinaryFileHourly(binFileName, startDateTime, endDateTime, true);
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
int N = dataValues.Data.Length;
DateTime startValueDate = dataValues.BeginDateTime;
for (int i = 0; i < N; i++)
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = startValueDate.AddHours(i);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(dataValues.Data[i], varId);
valuesList.Add(v);
}
s.value = valuesList.ToArray();
return s;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableCode, DateTime startDateTime, DateTime endDateTime)
{
//numeric variable id
int varId = VariableCodeToID(variableCode);
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = GetMethodForVariable(varId);
string timeStep = "day";
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "d";
s.units.unitCode = "104";
s.units.unitID = 104;
s.units.unitName = "day";
s.units.unitType = "Time";
timeStep = "day";
//method
//TODO: re-factor into a new file
s.method[0] = GetMethodForVariable(varId);
//switch(varId)
//{
// case 8:
// s.method[0].methodCode = "1";
// s.method[0].methodID = 1;
// s.method[0].methodDescription = "Snow measured at 6:00Z on open ground";
// s.method[0].methodLink = "hydro.chmi.cz/hpps";
// break;
// case 1:
// s.method[0].methodCode = "2";
// s.method[0].methodID = 2;
// s.method[0].methodDescription = "Precipitation measured by tipping bucket";
// s.method[0].methodLink = "hydro.chmi.cz/hpps";
// break;
// case 16:
// s.method[0].methodCode = "3";
// s.method[0].methodID = 3;
// s.method[0].methodDescription = "Automated Meteorological station temperature sensor";
// s.method[0].methodLink = "portal.chmi.cz/files/portal/docs/poboc/OS/stanice/ShowStations_CZ.html";
// break;
// case 5:
// s.method[0].methodCode = "4";
// s.method[0].methodID = 4;
// s.method[0].methodDescription = "Water level sensor (ultrasound or pressure), discharge derived from rating curve";
// s.method[0].methodLink = "hydro.chmi.cz/hpps";
// break;
//}
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
s.source = new SourceType[1];
s.source[0] = new SourceType();
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//values: get from database...
string connStr = GetConnectionString();
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("qry_observations", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@st_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@var_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@start_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@time_step", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@group_function", SqlDbType.VarChar));
cmd.Parameters["@st_id"].Value = Convert.ToInt32(siteId);
cmd.Parameters["@var_id"].Value = varId;
cmd.Parameters["@start_time"].Value = startDateTime;
cmd.Parameters["@end_time"].Value = endDateTime;
cmd.Parameters["@time_step"].Value = timeStep;
cmd.Parameters["@group_function"].Value = "sum";
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
int obsTimeIndex = r.GetOrdinal("obs_time");
int obsValueIndex = r.GetOrdinal("obs_value");
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(r["obs_value"], varId);
valuesList.Add(v);
}
}
}
//convert list to array for temperature
if (varId == 16 || varId == 17 || varId == 18)
{
s.value = valuesList.ToArray();
}
else
{
//convert list to array - for precip, snow, discharge
DateTime beginDate = valuesList[0].dateTime;
DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
int numDays = endDate.Subtract(beginDate).Days;
ValueSingleVariable[] valuesArray = new ValueSingleVariable[numDays];
int valueIndex = 0;
DateTime curDate = beginDate;
foreach (ValueSingleVariable val in valuesList)
{
if (valueIndex >= valuesArray.Length) break;
while (curDate < val.dateTime)
{
valuesArray[valueIndex] = CreateNoDataValue(curDate,s, varId);
curDate = curDate.AddDays(1);
valueIndex++;
}
if (valueIndex >= valuesArray.Length) break;
valuesArray[valueIndex] = val;
curDate = val.dateTime.AddDays(1);
valueIndex++;
}
s.value = valuesArray;
}
return s;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site code (networkPrefix:SiteCode)</param>
/// <param name="variableId">variable code (vocabularyPrefix:VariableCode)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableId, DateTime startDateTime, DateTime endDateTime)
{
WriteLog(String.Format("GetValuesFromDb({0},{1},{2},{3}", siteId, variableId, startDateTime, endDateTime));
try
{
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = new MethodType();
s.method[0].methodCode = "1";
s.method[0].methodID = 1;
s.method[0].methodDescription = "NCDC METAR observation";
s.method[0].methodLink = "http://www7.ncdc.noaa.gov";
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
s.source = new SourceType[1];
s.source[0] = new SourceType();
s.source[0].citation = "NCDC";
s.source[0].organization = "NCDC";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = "NCDC Climate Data Online Web Service";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "d";
s.units.unitCode = "d";
s.units.unitID = 0;
s.units.unitName = "day";
s.units.unitType = "Time";
//get db site id
int dbSiteId = GetDbSiteId(Convert.ToInt32(siteId));
//values: get from database...
string connStr = GetConnectionString();
string sqlQuery = "SELECT obs_date, obs_value FROM pcp WHERE st_id = @p1 AND obs_date > @p2 AND obs_date < @p3 ORDER BY obs_date";
switch (variableId)
{
case "TMIN":
sqlQuery = "SELECT obs_date, tmin FROM tmp WHERE st_id=@p1 AND obs_date > @p2 AND obs_date < @p3 ORDER BY obs_date";
break;
case "TAVG":
sqlQuery = "SELECT obs_date, tavg FROM tmp WHERE st_id=@p1 AND obs_date > @p2 AND obs_date < @p3 ORDER BY obs_date";
break;
case "TMAX":
sqlQuery = "SELECT obs_date, tmax FROM tmp WHERE st_id=@p1 AND obs_date > @p2 AND obs_date < @p3 ORDER BY obs_date";
break;
}
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sqlQuery, cnn))
{
cmd.Parameters.Add("@p1", SqlDbType.SmallInt);
cmd.Parameters.Add("@p2", SqlDbType.Date);
cmd.Parameters.Add("@p3", SqlDbType.Date);
cmd.Parameters["@p1"].Value = dbSiteId;
cmd.Parameters["@p2"].Value = startDateTime;
cmd.Parameters["@p3"].Value = endDateTime;
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_date"]).AddHours(7);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = "1";
v.methodID = "1";
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
double iValue = Convert.ToDouble(r[1]);
if (iValue > -8000 && iValue < 2000)
{
v.Value = Convert.ToDecimal(Math.Round(iValue / 10.0, 1));
}
else
{
v.Value = Convert.ToDecimal(-9999.0);
}
valuesList.Add(v);
}
}
}
//convert list to array
ValueSingleVariable[] valuesArray = FillValuesList(valuesList);
//DateTime beginDate = valuesList[0].dateTime;
//DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
//int numDays = endDate.Subtract(beginDate).Days;
//ValueSingleVariable[] valuesArray = new ValueSingleVariable[numDays];
//int valueIndex = 0;
//DateTime curDate = beginDate;
//foreach (ValueSingleVariable val in valuesList)
//{
// if (valueIndex >= valuesArray.Length) break;
// while (curDate < val.dateTime)
// {
// valuesArray[valueIndex] = CreateNoDataValue(curDate);
// curDate = curDate.AddDays(1);
// valueIndex++;
// }
// if (valueIndex >= valuesArray.Length) break;
// valuesArray[valueIndex] = val;
// curDate = val.dateTime.AddDays(1);
// valueIndex++;
//}
s.value = valuesArray;
return s;
}
catch (Exception ex)
{
WriteLog(ex.Message);
return new TsValuesSingleVariableType();
}
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableCode, DateTime startDateTime, DateTime endDateTime)
{
//numeric variable id
int varId = VariableCodeToID(variableCode);
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = GetMethodForVariable(varId);
string timeStep = "day";
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "d";
s.units.unitCode = "104";
s.units.unitID = 104;
s.units.unitName = "day";
s.units.unitType = "Time";
timeStep = "day";
//method
//TODO: re-factor into a new file
s.method[0] = GetMethodForVariable(varId);
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
s.source = new SourceType[1];
s.source[0] = new SourceType();
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
string variableFolder = "prutok";
switch (varId)
{
case 1:
variableFolder = "srazky";
break;
case 2:
variableFolder = "srazky";
break;
case 4:
variableFolder = "vodstav";
break;
case 5:
variableFolder = "prutok";
break;
case 8:
variableFolder = "snih";
break;
case 16:
variableFolder = "teplota";
break;
case 17:
variableFolder = "tmin";
break;
case 18:
variableFolder = "tmax";
break;
}
//special case for SNOW: read values from database
if (varId == 8)
{
//values: get from database...
string connStr = GetConnectionString();
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("plaveninycz.new_query_observations", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@st_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@var_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@start_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@time_step", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@group_function", SqlDbType.VarChar));
cmd.Parameters["@st_id"].Value = Convert.ToInt32(siteId);
cmd.Parameters["@var_id"].Value = varId;
cmd.Parameters["@start_time"].Value = startDateTime;
cmd.Parameters["@end_time"].Value = endDateTime;
cmd.Parameters["@time_step"].Value = timeStep;
cmd.Parameters["@group_function"].Value = "sum";
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
int obsTimeIndex = r.GetOrdinal("obs_time");
int obsValueIndex = r.GetOrdinal("obs_value");
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(r["obs_value"], varId);
valuesList.Add(v);
}
}
}
//convert list to array - for precip, snow, discharge, stage
DateTime beginDate = valuesList[0].dateTime;
DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
int numDays = endDate.Subtract(beginDate).Days;
ValueSingleVariable[] valuesArray = new ValueSingleVariable[numDays];
int valueIndex = 0;
DateTime curDate = beginDate;
foreach (ValueSingleVariable val in valuesList)
{
if (valueIndex >= valuesArray.Length) break;
while (curDate < val.dateTime)
{
valuesArray[valueIndex] = CreateNoDataValue(curDate, s, varId);
curDate = curDate.AddDays(1);
valueIndex++;
}
if (valueIndex >= valuesArray.Length) break;
valuesArray[valueIndex] = val;
curDate = val.dateTime.AddDays(1);
valueIndex++;
}
s.value = valuesArray;
}
else
{
//values: get from binary file ...
string binFileName = BinaryFileHelper.GetBinaryFileName(Convert.ToInt32(siteId), variableFolder, "d");
BinaryFileData dataValues = BinaryFileHelper.ReadBinaryFileDaily(binFileName, startDateTime, endDateTime, true);
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
int N = dataValues.Data.Length;
DateTime startValueDate = dataValues.BeginDateTime;
for (int i = 0; i < N; i++)
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = startValueDate.AddDays(i);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(dataValues.Data[i], varId);
valuesList.Add(v);
}
s.value = valuesList.ToArray();
}
return s;
}