Example #1
0
        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;
        }
Example #2
0
        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;
        }
Example #3
0
        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;
        }
Example #4
0
        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;
        }
Example #5
0
        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;
            }
        }