public static seriesCatalogTypeSeries GetSeriesCatalogFromDb(int siteId, int variableId) { seriesCatalogTypeSeries s = new seriesCatalogTypeSeries(); s.dataType = "Average"; s.generalCategory = "Climate"; //method s.method = new MethodType(); if (variableId == 8) { s.method.methodCode = "1"; s.method.methodID = 1; s.method.methodDescription = "Snow measured at 6:00Z on open ground"; s.method.methodLink = "hydro.chmi.cz/hpps"; } else { s.method.methodCode = "2"; s.method.methodID = 2; s.method.methodDescription = "Precipitation measured by tipping-bucket raingauge and aggregated to daily"; s.method.methodLink = "hydro.chmi.cz/hpps"; } //qc level s.qualityControlLevel = new QualityControlLevelType(); s.qualityControlLevel.definition = "raw data"; s.qualityControlLevel.explanation = "raw data"; s.qualityControlLevel.qualityControlLevelCode = "1"; s.qualityControlLevel.qualityControlLevelID = 1; s.qualityControlLevel.qualityControlLevelIDSpecified = true; //source - accommodate for POH int operatorId = 1; string connStr = GetConnectionString(); string sqlSource = string.Format(Resources.SqlQueries.query_operatorid, siteId); { using (SqlConnection con01 = new SqlConnection(connStr)) { using (SqlCommand cmd01 = new SqlCommand(sqlSource, con01)) { con01.Open(); object obj = cmd01.ExecuteScalar(); if (obj != null) { operatorId = Convert.ToInt32(obj); } } } } if (variableId == 8) { s.sampleMedium = "Snow"; } else { s.sampleMedium = "Precipitation"; } s.source = new SourceType(); if (operatorId == 1) { s.source.citation = "CHMI"; s.source.organization = "CHMI"; s.source.sourceCode = "1"; if (variableId == 8) s.source.sourceDescription = "snow depth measured by CHMI professional stations"; else s.source.sourceDescription = "CHMI automated stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else if (operatorId == 3) { s.source.citation = "POH"; s.source.organization = "Ohře Watershed Authority"; s.source.sourceCode = "1"; s.source.sourceDescription = "measured by Ohře watershed authority stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else if (operatorId == 2) { s.source.citation = "PVL"; s.source.organization = "Povodí Vltavy"; s.source.sourceCode = "1"; s.source.sourceDescription = "measured by Vltava watershed authority professional stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else if (operatorId == 4) { s.source.citation = "PLA"; s.source.organization = "Povodí Labe"; s.source.sourceCode = "1"; s.source.sourceDescription = "measured by Labe watershed authority professional stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else if (operatorId == 5) { s.source.citation = "PMO"; s.source.organization = "Povodí Moravy"; s.source.sourceCode = "1"; s.source.sourceDescription = "measured by Morava watershed authority professional stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else if (operatorId == 6) { s.source.citation = "POD"; s.source.organization = "Povodí Odry"; s.source.sourceCode = "1"; s.source.sourceDescription = "measured by Odra watershed authority professional stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } else { s.source.citation = "DWD"; s.source.organization = "Deutscher Wetterdienst"; s.source.sourceCode = "1"; s.source.sourceDescription = "snow depth measured by Deutscher Wetterdienst professional stations"; s.source.sourceID = operatorId; s.source.sourceIDSpecified = true; } //value count, begin time, end time string sql = string.Format(Resources.SqlQueries.query_seriescatalog, siteId, variableId); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = Convert.ToInt32(dr["ValueCount"]); s.valueType = "Field Observation"; s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(dr["BeginDate"]); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(dr["EndDate"]); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; } } } //variable if (variableId == 8) { s.variable = GetVariableInfoFromDb("CZSNW:8"); } else { s.variable = GetVariableInfoFromDb("CZSNW:1"); } return s; }
public static seriesCatalogTypeSeries GetSeriesCatalogFromDb(int siteId, int variableId) { seriesCatalogTypeSeries s = new seriesCatalogTypeSeries(); string connStr = GetConnectionString(); s.generalCategory = "Climate"; s.valueType = "Field Observation"; //method s.method = GetMethodForVariable(variableId); //qc level s.qualityControlLevel = new QualityControlLevelType(); s.qualityControlLevel.definition = "raw data"; s.qualityControlLevel.explanation = "raw data"; s.qualityControlLevel.qualityControlLevelCode = "1"; s.qualityControlLevel.qualityControlLevelID = 1; s.qualityControlLevel.qualityControlLevelIDSpecified = true; //source s.source = GetSourceForSite(siteId); //table name string tableName = "plaveninycz." + GetTableName(variableId); //value count, begin time, end time string sql = string.Format(Resources.SqlQueries.query_seriescatalog_new, tableName, siteId); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); //check for DbNull object beginTimeObj = dr["BeginDate"]; object endTimeObj = dr["EndDate"]; if (beginTimeObj == DBNull.Value || endTimeObj == DBNull.Value) { //in this case database has no data. return null; } s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(beginTimeObj); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(endTimeObj); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; double totalHours = (s.variableTimeInterval.endDateTime.Subtract(s.variableTimeInterval.beginDateTime)).TotalHours; s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = (int)(Math.Round(totalHours)); //if no values --> series doesn't exist if (s.valueCount.Value == 0) { return null; } } } } //variable s.variable = GetVariableInfoFromDb(VariableIDToCode(variableId)); //data type, sample medium s.dataType = s.variable.dataType; s.sampleMedium = s.variable.sampleMedium; s.generalCategory = s.variable.generalCategory; return s; }
public static seriesCatalogTypeSeries GetSeriesCatalogFromDb(int siteId, int variableId) { seriesCatalogTypeSeries s = new seriesCatalogTypeSeries(); string connStr = GetConnectionString(); s.generalCategory = "Climate"; s.valueType = "Field Observation"; //method s.method = GetMethodForVariable(variableId); s.method = new MethodType(); //qc level s.qualityControlLevel = new QualityControlLevelType(); s.qualityControlLevel.definition = "raw data"; s.qualityControlLevel.explanation = "raw data"; s.qualityControlLevel.qualityControlLevelCode = "1"; s.qualityControlLevel.qualityControlLevelID = 1; s.qualityControlLevel.qualityControlLevelIDSpecified = true; //source s.source = new SourceType(); s.source.citation = "CHMI"; s.source.organization = "CHMI"; s.source.sourceCode = "1"; s.source.sourceDescription = "original data source is from CHMI website, measured by CHMI professional stations"; s.source.sourceID = 1; s.source.sourceIDSpecified = true; //value count, begin time, end time //precipitation or snow if (variableId == 1 || variableId == 8) { string sql = string.Format(Resources.SqlQueries.query_seriescatalog, siteId, variableId); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = Convert.ToInt32(dr["ValueCount"]); s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(dr["BeginDate"]); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(dr["EndDate"]); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; } } } } else if (variableId == 16 || variableId == 17 || variableId == 18) { //air temperature string sql = string.Format(Resources.SqlQueries.query_seriescatalog_temperature, siteId); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); //check for DbNull object beginTimeObj = dr["BeginDate"]; object endTimeObj = dr["EndDate"]; if (beginTimeObj == DBNull.Value || endTimeObj == DBNull.Value) { //in this case database has no data. return null; } s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(beginTimeObj); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(endTimeObj); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; double totalDays = (s.variableTimeInterval.endDateTime.Subtract(s.variableTimeInterval.beginDateTime)).TotalDays; s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = (int)(Math.Round(totalDays)); //if no values --> series doesn't exist if (s.valueCount.Value == 0) { return null; } } else { return null; } } } } else if (variableId == 5 || variableId == 4) { //discharge string sql = string.Format(Resources.SqlQueries.query_seriescatalog_discharge, siteId); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(dr["BeginDate"]); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(dr["EndDate"]); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; double totalDays = (s.variableTimeInterval.endDateTime.Subtract(s.variableTimeInterval.beginDateTime)).TotalDays; s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = (int)(Math.Round(totalDays)); //if no values --> series doesn't exist if (s.valueCount.Value == 0) { return null; } } else { return null; } } } } //variable s.variable = GetVariableInfoFromDb(VariableIDToCode(variableId)); //data type, sample medium s.dataType = s.variable.dataType; s.sampleMedium = s.variable.sampleMedium; s.generalCategory = s.variable.generalCategory; return s; }
public static seriesCatalogTypeSeries GetSeriesCatalogFromDb(int siteId, int variableId) { seriesCatalogTypeSeries s = new seriesCatalogTypeSeries(); string connStr = GetConnectionString(); s.generalCategory = "Climate"; s.valueType = "Field Observation"; //method s.method = GetMethodForVariable(variableId); //qc level s.qualityControlLevel = new QualityControlLevelType(); s.qualityControlLevel.definition = "raw data"; s.qualityControlLevel.explanation = "raw data"; s.qualityControlLevel.qualityControlLevelCode = "1"; s.qualityControlLevel.qualityControlLevelID = 1; s.qualityControlLevel.qualityControlLevelIDSpecified = true; //source s.source = GetSourceForSite(siteId); //table name //foldername string variableFolder = "prutok"; switch (variableId) { case 1: variableFolder = "srazky"; break; case 2: variableFolder = "srayky"; break; case 4: variableFolder = "vodstav"; break; case 5: variableFolder = "prutok"; break; case 8: variableFolder = "snih"; break; case 16: variableFolder = "teplota"; break; } //value count, begin time, end time string binFileName = BinaryFileHelper.GetBinaryFileName(siteId, variableFolder, "h"); DateRange beginEndTime = BinaryFileHelper.BinaryFileDateRange(binFileName, "h"); if (beginEndTime.Start == null || beginEndTime.End == null) { return null; } s.variableTimeInterval = new TimeIntervalType(); s.variableTimeInterval.beginDateTime = beginEndTime.Start; s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime.AddHours(-1); s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = beginEndTime.End; s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime.AddHours(-1); s.variableTimeInterval.endDateTimeUTCSpecified = true; double totalHours = (s.variableTimeInterval.endDateTime.Subtract(s.variableTimeInterval.beginDateTime)).TotalHours; s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = (int)(Math.Round(totalHours)); //if no values --> series doesn't exist if (s.valueCount.Value == 0) { return null; } //variable s.variable = GetVariableInfoFromDb(VariableIDToCode(variableId)); //data type, sample medium s.dataType = s.variable.dataType; s.sampleMedium = s.variable.sampleMedium; s.generalCategory = s.variable.generalCategory; return s; }
public static seriesCatalogTypeSeries GetSeriesCatalogFromDb(int dbSiteId, int variableId) { WriteLog("Executing GetSeriesCatalogFromDB(" + dbSiteId + ", " + variableId + ")"); try { seriesCatalogTypeSeries s = new seriesCatalogTypeSeries(); //method s.method = new MethodType(); s.method.methodCode = "1"; s.method.methodID = 1; s.method.methodDescription = "Global Climate Data"; s.method.methodLink = "http://eca.knmi.nl"; //qc level s.qualityControlLevel = new QualityControlLevelType(); s.qualityControlLevel.definition = "Quality Controlled Data"; s.qualityControlLevel.explanation = "Quality Controlled Data"; s.qualityControlLevel.qualityControlLevelCode = "1"; s.qualityControlLevel.qualityControlLevelID = 1; s.qualityControlLevel.qualityControlLevelIDSpecified = true; //value count, begin time, end time string sql = string.Format(Resources.SqlQueries.query_seriescatalog, dbSiteId, variableId); string connStr = GetConnectionString(); using (SqlConnection conn = new SqlConnection(connStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); s.valueCount = new seriesCatalogTypeSeriesValueCount(); s.valueCount.Value = Convert.ToInt32(dr["value_count"]); s.variableTimeInterval = new TimePeriodType(); s.variableTimeInterval.beginDateTime = Convert.ToDateTime(dr["start_date"]); s.variableTimeInterval.beginDateTimeUTC = s.variableTimeInterval.beginDateTime; s.variableTimeInterval.beginDateTimeUTCSpecified = true; s.variableTimeInterval.endDateTime = Convert.ToDateTime(dr["end_date"]); s.variableTimeInterval.endDateTimeUTC = s.variableTimeInterval.endDateTime; s.variableTimeInterval.endDateTimeUTCSpecified = true; } } //variable s.variable = GetVariableInfoByID(variableId); s.dataType = s.variable.dataType; s.generalCategory = s.variable.generalCategory; s.valueType = s.variable.valueType; //source s.sampleMedium = s.variable.sampleMedium; s.source = new SourceType(); s.source.citation = "NCDC World Climate Data Online"; s.source.organization = "NCDC"; s.source.sourceCode = "1"; s.source.sourceDescription = "NCDC Climate Data Online"; s.source.sourceID = 1; s.source.sourceIDSpecified = true; s.source.sourceLink = new string[] { "http://www7.ncdc.noaa.gov/rest" }; return s; } catch (Exception ex) { WriteLog("GetSeriesCatalogFromDb - " + ex.Message); return null; } }