/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSites = "SELECT Stations.st_id, st_name, altitude, location_id FROM Stations WHERE Stations.st_id IN (SELECT st_id FROM StationsVariables WHERE var_id=8 OR var_id=1)";
string sql = "SELECT st.st_id, st_name, lat, lon, altitude FROM " +
"(" + sqlSites + ") st INNER JOIN Locations ON st.location_id = Locations.loc_id";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
si.note = new NoteType[1];
si.note[0] = new NoteType();
si.note[0].title = "my note";
si.note[0].type = "custom";
si.note[0].Value = "CZSNW";
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
public static SiteInfoResponseTypeSite GetSiteFromDb(string siteId, bool includeSeriesCatalog)
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
newSite.siteInfo = GetSiteFromDb2(siteId);
//to add the catalog
if (includeSeriesCatalog)
{
List<int> variableIdList = GetVariablesForSite(Convert.ToInt32(siteId));
int numVariables = variableIdList.Count;
newSite.seriesCatalog = new seriesCatalogType[1];
newSite.seriesCatalog[0] = new seriesCatalogType();
newSite.seriesCatalog[0].series = new seriesCatalogTypeSeries[numVariables];
for (int i = 0; i < numVariables; i++)
{
newSite.seriesCatalog[0].series[i] = GetSeriesCatalogFromDb(Convert.ToInt32(siteId), variableIdList[i]);
}
}
return newSite;
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sql = Resources.SqlQueries.query_sites_hourly;
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
//si.note = new NoteType[1];
//si.note[0] = new NoteType();
//si.note[0].title = "my note";
//si.note[0].type = "custom";
//si.note[0].Value = "CHMI-D";
si.verticalDatum = "MSL";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesByBox(box queryBox, bool includeSeries)
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSites = "SELECT plaveninycz.Stations.st_id, st_name, altitude, location_id, lat, lon FROM plaveninycz.Stations INNER JOIN StationsVariables stv ON Stations.st_id = stv.st_id " +
"WHERE var_id in (1, 4, 5, 16) AND lat IS NOT NULL";
cmd.CommandText = sqlSites;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["altitude"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["altitude"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["Lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["st_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["st_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
/// <summary>
/// Gets the sites, in XML format [test for SNOW]
/// </summary>
public static SiteInfoResponseTypeSite[] GetSitesFromDb()
{
List<SiteInfoResponseTypeSite> siteList = new List<SiteInfoResponseTypeSite>();
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sql = "SELECT st_id, st_name, wmo_id, lat, lon, elev, country FROM st " +
"WHERE st.st_id IN (SELECT st_id FROM series WHERE value_count > 1000) ORDER BY country, st_id";
cmd.CommandText = sql;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
SiteInfoType si = new SiteInfoType();
if (dr["elev"] != DBNull.Value)
{
si.elevation_m = Convert.ToDouble(dr["elev"]);
si.elevation_mSpecified = true;
}
else
{
si.elevation_m = 0;
si.elevation_mSpecified = true;
}
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType latLon = new LatLonPointType();
latLon.latitude = Convert.ToDouble(dr["lat"]);
latLon.longitude = Convert.ToDouble(dr["lon"]);
latLon.srs = "EPSG:4326";
si.geoLocation.geogLocation = latLon;
si.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
si.geoLocation.localSiteXY[0] = new SiteInfoTypeGeoLocationLocalSiteXY();
si.geoLocation.localSiteXY[0].X = latLon.longitude;
si.geoLocation.localSiteXY[0].Y = latLon.latitude;
si.geoLocation.localSiteXY[0].ZSpecified = false;
si.geoLocation.localSiteXY[0].projectionInformation = si.geoLocation.geogLocation.srs;
si.metadataTimeSpecified = false;
//si.oid = Convert.ToString(dr["st_id"]);
si.note = new NoteType[2];
si.note[0] = new NoteType();
si.note[0].title = "Country";
si.note[0].type = "custom";
si.note[0].Value = dr["country"].ToString();
si.note[1] = new NoteType();
si.note[1].title = "db_id";
si.note[1].type = "custom";
si.note[1].Value = dr["st_id"].ToString();
si.verticalDatum = "Unknown";
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].network = serviceCode;
si.siteCode[0].siteID = Convert.ToInt32(dr["wmo_id"]);
si.siteCode[0].siteIDSpecified = true;
si.siteCode[0].Value = Convert.ToString(dr["wmo_id"]);
si.siteName = Convert.ToString(dr["st_name"]);
newSite.siteInfo = si;
siteList.Add(newSite);
}
}
}
return siteList.ToArray();
}
public static SiteInfoResponseTypeSite GetSiteFromDb(string siteId, bool includeSeriesCatalog)
{
WriteLog("Executing GetSiteFromDb(" + siteId + ", " + includeSeriesCatalog + ")");
try
{
SiteInfoResponseTypeSite newSite = new SiteInfoResponseTypeSite();
newSite.siteInfo = GetSiteFromDb2(siteId);
//db site id
int dbSiteId = Convert.ToInt32(newSite.siteInfo.note[1].Value);
WriteLog("DbSiteID: " + dbSiteId);
//to add the catalog
if (includeSeriesCatalog)
{
//first, get the variables for the site
string sqlVariables = String.Format("SELECT var_id FROM series WHERE st_id = {0} AND value_count > 1000", dbSiteId);
List<int> variableIDs = new List<int>();
string connStr = GetConnectionString();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sqlVariables, conn))
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int varID = Convert.ToInt32(dr["var_id"]);
variableIDs.Add(varID);
}
conn.Close();
}
}
int numVariables = variableIDs.Count;
WriteLog("numVariables: " + numVariables);
newSite.seriesCatalog = new seriesCatalogType[1];
newSite.seriesCatalog[0] = new seriesCatalogType();
newSite.seriesCatalog[0].series = new seriesCatalogTypeSeries[numVariables];
for (int i = 0; i < numVariables; i++)
{
newSite.seriesCatalog[0].series[i] = GetSeriesCatalogFromDb(dbSiteId, variableIDs[i]);
}
}
return newSite;
}
catch (Exception ex)
{
WriteLog("WebServiceUtils.GetSiteFromDb " + ex.Message);
return null;
}
}
