private int SiteData(L1HarvestList series)
{
this.ws.Url = series.WebServiceURL;
// save siteInfo
SiteInfoResponseType responsesite = this.ws.GetSiteInfoObject(series.SiteCode, string.Empty);
SiteInfoType site = responsesite.site[0].siteInfo;
LatLonPointType latlon = (LatLonPointType)site.geoLocation.geogLocation;
int localx = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].X; /*(int)DBNull.Value*/
int localy = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].Y;
string state = string.Empty;
string county = string.Empty;
if (site.siteProperty != null)
{
foreach (PropertyType s in site.siteProperty)
{
if (s.name == "State")
{
state = s.Value;
}
else if (s.name == "County")
{
county = s.Value;
}
}
}
return(this.db.TooDB.SaveSite(site.siteCode[0].Value, site.siteName.ToString(), Convert.ToDouble(latlon.longitude), Convert.ToDouble(latlon.latitude), 0, Convert.ToDouble(site.elevation_m), site.verticalDatum, localx, localy, 0, 0, state, county /*"county"*/, string.Empty /*"comments"*/, series.SiteType));
}
static bool InsertOneSite(OD_1_1_1DataSet.SitesDataTable sitesTable,
SiteInfoType stinfo, SqlConnection sqlConn)
{
OD_1_1_1DataSet.SitesRow row = sitesTable.NewSitesRow();
row.SiteCode = stinfo.siteCode[0].network + "|" + stinfo.siteCode[0].Value;
string cond = "SiteCode = '" + row.SiteCode + "'";
if (OD_Utils.Exists(row.Table.TableName, cond, sqlConn))
{
return(false);
}
row.SiteName = stinfo.siteName;
LatLonPointType glt = (LatLonPointType)stinfo.geoLocation.geogLocation;
row.Latitude = glt.latitude;
row.Longitude = glt.longitude;
row.LatLongDatumID = 0;
row.Elevation_m = stinfo.elevation_m;
if (stinfo.verticalDatum != null)
{
row.VerticalDatum = stinfo.verticalDatum;
}
row.LocalProjectionID = 0;
//row.LocalX = 0;
//row.LocalY = 0;
//row.LocalProjectionID = 0;
//row.PosAccuracy_m = 0;
if (stinfo.note != null)
{
for (int i = 0; i < stinfo.note.Count(); i++)
{
NoteType note = stinfo.note[i];
switch (note.title)
{
case "State":
row.State = note.Value;
break;
case "County":
row.County = note.Value;
break;
case "agency":
row.Comments = note.Value;
break;
}
}
}
sitesTable.AddSitesRow(row);
return(true);
}
public static SiteInfoType CreateASiteInfoTypeWithLatLongPoint(
string SiteVocabulary,
string SiteCode,
string SiteName,
float?Latitude,
float?Longitude,
String spatialReferenceSystem)
{
// if we don't have a code or name, that is really not good.
if (String.IsNullOrEmpty(SiteCode))
{
return(null);
}
SiteInfoType si = new SiteInfoType();
si.siteName = SiteName;
if (Latitude.HasValue && Longitude.HasValue)
{
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType point = new LatLonPointType();
point.latitude = Latitude.Value;
point.longitude = Longitude.Value;
point.srs = spatialReferenceSystem;
si.geoLocation.geogLocation = point;
}
else
{
// just add an empty one
si.geoLocation = new SiteInfoTypeGeoLocation();
LatLonPointType point = new LatLonPointType();
}
si.siteCode = new SiteInfoTypeSiteCode[1];
si.siteCode[0] = new SiteInfoTypeSiteCode();
si.siteCode[0].Value = SiteCode;
si.siteCode[0].network = SiteVocabulary;
si.siteCode[0].@default = true;
return(si);
}
/// <summary>
/// saves or gathers site information SiteData
/// </summary>
/// <param name="series">object from the database that contains all the details of the series we are trying to gather data for</param>
/// <returns>the SiteId from the database</returns>
private int SiteData(L1HarvestList series)
{
SiteInfoType site;
this.ws.Url = series.WebServiceURL;
try
{
// save siteInfo
SiteInfoResponseType responsesite = this.ws.GetSiteInfoObject(series.SiteCode, string.Empty);
site = responsesite.site[0].siteInfo;
}
catch
{
TimeSeriesResponseType response = this.ws.GetValuesObject(series.SiteCode, series.VariableCode, "1900-01-01", "1900-01-01", string.Empty);
site = (SiteInfoType)response.timeSeries.sourceInfo; // site[0].siteInfo;
}
LatLonPointType latlon = (LatLonPointType)site.geoLocation.geogLocation;
int localx = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].X; /*(int)DBNull.Value*/
int localy = site.geoLocation.localSiteXY == null ? 0 : (int)site.geoLocation.localSiteXY[0].Y;
// note 1 contains county information but not all sites contain a note 1.
return(this.db.TooDB.SaveSite(site.siteCode[0].Value, site.siteName.ToString(), Convert.ToDouble(latlon.longitude), Convert.ToDouble(latlon.latitude), 0 /*lat long datum id*/, Convert.ToDouble(site.elevation_m), site.verticalDatum, localx, localy, 0 /*local projection id*/, 0 /*Position Accuracy*/, site.note[0].Value.Length == 2 ? site.note[0].Value : string.Empty /*"state"*/, string.Empty /*"county"*/, string.Empty /*"comments"*/, series.SiteType));
}
/// <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();
}
public static SiteInfoType GetSiteFromDb2(string siteId)
{
string cnn = GetConnectionString();
string serviceCode = ConfigurationManager.AppSettings["network"];
SiteInfoType si = new SiteInfoType();
using (SqlConnection conn = new SqlConnection(cnn))
{
using (SqlCommand cmd = new SqlCommand())
{
string sqlSite = string.Format(Resources.SqlQueries.query_sitebyid2, siteId);
cmd.CommandText = sqlSite;
cmd.Connection = conn;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
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.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"]);
}
}
}
return si;
}
