/// <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; }
private static ValueSingleVariable CreateNoDataValue(DateTime time) { ValueSingleVariable v = new ValueSingleVariable(); v.censorCode = "nc"; v.dateTime = Convert.ToDateTime(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 = 0.0M; 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 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; }
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 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; }
//This function fills any gaps in the ValuesList private static ValueSingleVariable[] FillValuesList(List<ValueSingleVariable> valuesList) { DateTime startDate = valuesList[0].dateTime; DateTime endDate = valuesList[valuesList.Count - 1].dateTime; int numDays = (int)(Math.Round((endDate - startDate).TotalDays)); ValueSingleVariable[] result = new ValueSingleVariable[numDays]; //1-add the valid dates foreach (ValueSingleVariable val in valuesList) { int index = (int)(val.dateTime.Subtract(startDate).TotalDays); if (index >= result.Length) break; result[index] = val; } //2-add the other dates for (int i = 0; i < result.Length; i++) { if (result[i] == null) { DateTime t = startDate.AddDays(i); result[i] = CreateNoDataValue(t); } } return result; }
/// <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; }