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));
}
/// <summary>
/// Creates the time series object.
/// Use CreateTimeSeriesObjectSingleValue to get an object populated with values object
/// </summary>
/// <returns></returns>
public static TimeSeriesResponseType CreateTimeSeriesObject(int numberOfSeries)
{
TimeSeriesResponseType result = new TimeSeriesResponseType();
result.queryInfo = new QueryInfoType();
result.queryInfo.criteria = new QueryInfoTypeCriteria();
result.queryInfo.criteria.timeParam = new QueryInfoTypeCriteriaTimeParam();
result.timeSeries = new TimeSeriesType[numberOfSeries];
for (int i = 0; i < result.timeSeries.Length; i++)
{
result.timeSeries[i] = TimeSeriesObjectSingleValue();
}
SiteInfoType site = new SiteInfoType();
site.siteCode = new SiteInfoTypeSiteCode[1];
SiteInfoTypeSiteCode siteId = new SiteInfoTypeSiteCode();
site.siteCode[0] = siteId;
result.timeSeries[0].sourceInfo = site;
result.timeSeries[0].variable = CreateVariableDescriptionType();//new VariablesTypeVariable[1];
//result.timeseries.variable[0] = new VariableDescriptionType(); // new VariablesTypeVariable();
//result.timeseries.variable.variableCode = new VariableDescriptionTypeVariableID[1];//added to simplify population
//result.timeseries.variable.variableCode[0] = new VariableDescriptionTypeVariableID();
return(result);
}
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(int numberOfLocationsPerSite)
{
SiteInfoType si = new SiteInfoType();
si.geoLocation = new SiteInfoTypeGeoLocation();
si.geoLocation.geogLocation = new LatLonPointType();
si.siteCode = new SiteInfoTypeSiteCode[numberOfLocationsPerSite];
for (int siteCodeCount = 0; siteCodeCount < numberOfLocationsPerSite; siteCodeCount++)
{
si.siteCode[siteCodeCount] = new SiteInfoTypeSiteCode();
}
return si;
}
public static SiteInfoResponseTypeSite CreateSite(SiteInfoType sit, Boolean addSeriesCatalog)
{
SiteInfoResponseTypeSite site = new SiteInfoResponseTypeSite();
if (sit != null)
{
site.siteInfo = sit;
if (addSeriesCatalog)
{
int?aSiteID = null;
Nullable <int> siteIDint = null;
try
{
aSiteID = sit.siteCode[0].siteID;
}
catch (NullReferenceException e)
{
String error = " no site code returned for sitecode" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
try
{
siteIDint = Convert.ToInt32(aSiteID);
}
catch (InvalidCastException e)
{
String error = " siteID was not an integer" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
try
{
site.seriesCatalog = CreateSeriesCatalogRecord(siteIDint);
}
catch (InvalidCastException e)
{
String error = " Error creating series record for " + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
}
}
else
{
String error = " no site code returned for sitecode" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;;
log.Warn(error);
}
return(site);
}
public static SiteInfoType CreateASiteInfoTypeWithLatLongPoint(int numberOfLocationsPerSite)
{
SiteInfoType si = new SiteInfoType();
si.geoLocation = new SiteInfoTypeGeoLocation();
si.geoLocation.geogLocation = new LatLonPointType();
si.siteCode = new SiteInfoTypeSiteCode[numberOfLocationsPerSite];
for (int siteCodeCount = 0; siteCodeCount < numberOfLocationsPerSite; siteCodeCount++)
{
si.siteCode[siteCodeCount] = new SiteInfoTypeSiteCode();
}
return(si);
}
public IEnumerable <TimeSeriesType> GetTimesSeriesTypeForSiteVariable(string SiteNumber, string StartDate, string EndDate)
{
W3CDateTime?BeginDateTime;
W3CDateTime?EndDateTime;
int? variableId = null;
int? siteID;
VariableInfoType varInfoType = null;
SiteInfoType siteType = null;
BeginDateTime = GetBeginDateTime(StartDate);
EndDateTime = GetEndDateTime(EndDate);
locationParam sq = GetLocationParameter(SiteNumber);
siteInfoDataSet sitDs = ODSiteInfo.GetSiteInfoDataSet(sq);
if (sitDs != null && sitDs.sites.Count > 0)
{
siteID = sitDs.sites[0].SiteID;
ValuesDataSet valuesDs = getValuesDataset(siteID, null, BeginDateTime, EndDateTime);
DataTable variableTable = DataSetHelper.SelectDistinct("variableIds", valuesDs.DataValues, "VariableID");
if (variableTable.Rows.Count == 0)
{
throw new WaterOneFlowException("No Data Available for Time Period.");
}
foreach (DataRow dataRow in variableTable.Rows)
{
TimeSeriesType timeSeries = new TimeSeriesType();
timeSeries.sourceInfo = ODSiteInfo.row2SiteInfoElement(sitDs.sites[0], sitDs);
int id = (int)dataRow[0];
VariableInfoType vit = ODvariables.GetVariableByID(id, variableDs);
timeSeries.variable = vit;
// DataView view = new DataView(valuesDs.DataValues, "VariableId = " + id, " DateTime ASC", DataViewRowState.CurrentRows);
timeSeries.values = getValues(valuesDs, null, id);
yield return(timeSeries);
}
}
}
public TimeSeriesResponseType getValues(string SiteNumber, string Variable, string StartDate, string EndDate)
{
// convert dates
// get site info
// get site ID
// return value dataset
TimeSeriesResponseType response;
W3CDateTime? BeginDateTime;
W3CDateTime? EndDateTime;
int?variableId = null;
int?siteID;
VariableInfoType varInfoType = null;
SiteInfoType siteType = null;
BeginDateTime = GetBeginDateTime(StartDate);
EndDateTime = GetEndDateTime(EndDate);
VariableParam vp = GetVariableParameter(Variable, ref variableId, ref varInfoType);
locationParam sq = GetLocationParameter(SiteNumber);
siteInfoDataSet sitDs = ODSiteInfo.GetSiteInfoDataSet(sq);
response = CuahsiBuilder.CreateTimeSeriesObjectSingleValue(1);
if (sitDs != null && sitDs.sites.Count > 0)
{
siteID = sitDs.sites[0].SiteID;
ValuesDataSet valuesDs = getValuesDataset(siteID, variableId, BeginDateTime, EndDateTime);
response.timeSeries[0].values = getValues(valuesDs, vp, variableId);
// above is the values, add the site, and variables
response.timeSeries[0].variable = varInfoType;
response.timeSeries[0].sourceInfo = ODSiteInfo.row2SiteInfoElement(sitDs.sites[0], sitDs);
}
// AddQueryInfo(StartDate, EndDate, Variable, SiteNumber, response);
response.queryInfo = CuahsiBuilder.CreateQueryInfoType("GetValues",
new string[] { SiteNumber }, null, new string[] { Variable }, StartDate, EndDate);
return(response);
}
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);
}
private static SiteInfoType row2SiteInfoType(UsgsDbDailyValues.sitesRow row)
{
SiteInfoType sit = null;
if (row == null || row.IsSiteCodeNull() || row.IsSiteNameNull())
{
return(null);
}
//string siteCode = row.SiteCode;
//string siteName = row.SiteName;
//float? lat = null, lon=null;
//string srs;
//if (! row.IsLatitudeNull() ) lat = row.Latitude;
//if(!row.IsLatitudeNull()) lon = row.Longitude;
//sit = CoreBuilder.CreateASiteInfoTypeWithLatLongPoint(
// "NWIS", siteCode, siteName, lat, lon
// );
sit = CoreBuilder.createSiteInfoRecord(row);
//TODO: USGS items as notes
// add agency note
if (!row.Isusgs_agencyNull())
{
note n = new note();
n.title = "agency";
n.Value = row.usgs_agency;
sit.note = CoreBuilder.addNote(sit.note, n);
}
// add siteType note
if (!row.Isusgs_station_typeNull())
{
note n = new note();
n.title = "SiteType";
n.Value = row.usgs_station_type;
sit.note = CoreBuilder.addNote(sit.note, n);
}
return(sit);
}
public static IEnumerable <SiteInfoType> GetSitesByBox(box queryBox)
{
siteInfoDataSet sDS;
sDS = GetSiteInfoDataSet(queryBox);
if (sDS.sites.Count == 0)
{
throw new WaterOneFlowException("No Sites found in specified region: " + queryBox.ToString());
}
else
{
List <SiteInfoType> sites = new List <SiteInfoType>(sDS.sites.Count);
foreach (siteInfoDataSet.sitesRow s in sDS.sites)
{
SiteInfoType sit = row2SiteInfoElement(s, sDS);
yield return(sit);
}
}
}
/// <summary>
/// Creates the time series object.
/// Use CreateTimeSeriesObjectSingleValue to get an object populated with values object
/// </summary>
/// <returns></returns>
public static TimeSeriesResponseType CreateTimeSeriesObject()
{
TimeSeriesResponseType result = new TimeSeriesResponseType();
result.queryInfo = new QueryInfoType();
result.queryInfo.criteria = new QueryInfoTypeCriteria();
result.queryInfo.criteria.timeParam = new QueryInfoTypeCriteriaTimeParam();
result.timeSeries = new TimeSeriesType();
SiteInfoType site = new SiteInfoType();
site.siteCode = new SiteInfoTypeSiteCode[1];
SiteInfoTypeSiteCode siteId = new SiteInfoTypeSiteCode();
site.siteCode[0] = siteId;
result.timeSeries.sourceInfo = site;
result.timeSeries.variable = CreateVariableDescriptionType();//new VariablesTypeVariable[1];
//result.timeseries.variable[0] = new VariableDescriptionType(); // new VariablesTypeVariable();
//result.timeseries.variable.variableCode = new VariableDescriptionTypeVariableID[1];//added to simplify population
//result.timeseries.variable.variableCode[0] = new VariableDescriptionTypeVariableID();
return(result);
}
// No one single data source provides enough data for Sources table,
// so try all resources to put them together.
public static void HandleSourceInfo(SqlConnection sqlConn,
SiteInfoType stinfo, seriesCatalogTypeSeries scts, TimeSeriesType tst)
{
string cond = "SourceID = " + scts.source.sourceID;
if (OD_Utils.Exists("Sources", cond, sqlConn))
{
return;
}
SourcesTableAdapter srcAdapter = new SourcesTableAdapter();
srcAdapter.Connection = sqlConn;
int metadataID = 0;
string title = "Unknown";
OD_1_1_1DataSet.SourcesDataTable tblSources = new OD_1_1_1DataSet.SourcesDataTable();
// We currently don't have any information about ISOMetaDataTable. Just create
// an unkown entry to resolve foreign key dependency.
if (scts.source.metadata != null)
{
title = scts.source.metadata.title;
}
cond = string.Format("Title = '{0}'", title);
metadataID = OD_Utils.GetPrimaryKey("ISOMetadata", "MetadataID", cond, sqlConn);
if (metadataID < 0)
{
InsertOneMetadata(scts.source.metadata, sqlConn);
metadataID = OD_Utils.GetPrimaryKey("ISOMetadata", "MetadataID", cond, sqlConn);
}
Console.WriteLine(">>>Parsing and inserting SOURCES");
InsertOneSource(tblSources, stinfo, scts, tst, metadataID, sqlConn);
//srcAdapter.Update(tblSources);
//PrintTable(srcAdapter, tblSites);
}
public SiteInfoType[] GetSites(locationParam[] lp)
{
List <SiteInfoType> sites = new List <SiteInfoType>();
UsgsDbDailyValues.sitesDataTable sDs = new UsgsDbDailyValues.sitesDataTable();
UsgsDbDailyValuesTableAdapters.sitesTableAdapter tableAdaptor = new sitesTableAdapter();
if (lp == null || lp.Length == 0)
{
// get all sites
tableAdaptor.Fill(sDs);
}
else
{
foreach (locationParam loc in lp)
{
tableAdaptor.FillBySiteCode(sDs, loc.SiteCode);
}
}
foreach (UsgsDbDailyValues.sitesRow row in sDs.Rows)
{
SiteInfoType sit = row2SiteInfoType(row);
if (sit != null)
{
sites.Add(sit);
}
}
if (sites.Count > 0)
{
return(sites.ToArray());
}
else
{
throw new WaterOneFlowException("No Sites Found");
}
}
public static SiteInfoType[] GetSitesByLocationParameters(locationParam[] locationParameters)
{
siteInfoDataSet sDS;
if (locationParameters.Length > 0)
{
sDS = GetSiteInfoDataSet(locationParameters);
}
else
{
sDS = GetSiteInfoDataSet();
}
List <SiteInfoType> sites = new List <SiteInfoType>(locationParameters.Length);
foreach (siteInfoDataSet.sitesRow s in sDS.sites)
{
SiteInfoType sit = row2SiteInfoElement(s, sDS);
sites.Add(sit);
}
return(sites.ToArray());
}
public override object GetTimeSeries(
locationParam lp,
variableParamVersion vp,
W3CDateTime?startDate,
W3CDateTime?endDate)
{
if ((endDate.HasValue && startDate.HasValue) && endDate.Value.DateTime < startDate.Value.DateTime)
{
throw new WaterOneFlowException("No Data. End Date must be greater than Start Date");
}
TimeSeriesResponseType result = null;
result = CuahsiBuilder.CreateTimeSeriesObject();
string[] StationsList = new string[] { lp.SiteCode };
SiteInfoType sit = null;
try
{
sit = DataInfoService.GetSite(lp);
result.timeSeries.sourceInfo = sit;
}
catch (WaterOneFlowException ex)
{
log.Info("EPA Bad Site Request" + vp.ToString());
throw ex;
}
catch (WaterOneFlowServerException ex)
{
log.Error("EPA server issued with Site Request" + vp.ToString());
throw ex;
}
catch (WaterOneFlowSourceException ex)
{
log.Error("EPA source issued with Site Request" + vp.ToString());
throw ex;
}
catch (Exception ex)
{
log.Error("EPA source issued with Site Request" + vp.ToString());
throw new WaterOneFlowServerException("Uncaught Exception in EPA Time series site reuqest", ex);
}
result.queryInfo.criteria.locationParam = lp.ToString();
result.queryInfo.criteria.variableParam = vp.ToString();
// not fully correct, but just choose the first one.
VariableInfoType[] vits = null;
try {
vits = DataInfoService.GetVariableInfoObject(vp);
result.timeSeries.variable = vits[0];
} catch (WaterOneFlowException ex)
{
log.Info("EPA Bad variable Request" + vp.ToString());
throw ex;
}
catch (WaterOneFlowServerException ex)
{
log.Error("EPA server issued with variable Request" + vp.ToString());
throw ex;
} catch (WaterOneFlowSourceException ex)
{
log.Error("EPA source issued with variable Request" + vp.ToString());
throw ex;
} catch (Exception ex)
{
log.Error("EPA source issued with variable Request" + vp.ToString());
throw new WaterOneFlowServerException("Uncaught Exception in EPA Time series site reuqest", ex);
}
String variableName = result.timeSeries.variable.variableName;
string agency = GetAgencyEpaSiteCode(lp);
string siteCode = GetSiteIDEpaSiteCode(lp);
EPAResults.StoretResultFlatDataTable table =
WqxResultsToDataset.GetStationResults(
agency, siteCode,
variableName, startDate, endDate);
if (table.Rows.Count == 0)
{ // no retults, just go back
result.timeSeries.values = new TsValuesSingleVariableType();
result.timeSeries.values.count = "0";
return(result);
}
DataRowCollection unitNameRows = EPAResponseUnits(table);
// for now
if (unitNameRows.Count > 1)
{
//throw new WaterOneFlowSourceException(
// " EPA Returned more than one unit for request."
// + " This is not supported at the present time" );
// no longer want an error. return data
log.Info("EPA STATION WITH MORE THAN ONE UNIT: '" + lp.ToString());
}
/* Logic neded for multiple units
* select one with max count or latest one?
* if max get count for each unit
* For the selected unit,
* * create a subtable,
* * populate values
*
* Future:
* for each unit
* *collect a subtable, populate values
* */
/* need to creat a units element
* The units returned, are not necessarily the ones in the series catalog
*/
//string unitName = unitNameRows[0].ItemArray[0].ToString().Trim();
//result.timeSeries.variable.units = CuahsiBuilder.CreateUnitsElement(null, null, unitName, unitName);
foreach (DataRow row in unitNameRows)
{
string unitName = row.ItemArray[0].ToString().Trim();
result.timeSeries.variable.units = CuahsiBuilder.CreateUnitsElement(null, null, unitName, unitName);
StringBuilder select = new StringBuilder();
select.AppendFormat("[VariableUnitsAbbreviation]='{0}'", WaterOneFlowImpl.WSUtils.SqlEncode(unitName));
if (startDate.HasValue)
{
select.AppendFormat(" AND [localDateTime] > '{0}' ", startDate.Value.DateTime.ToString("s"));
}
if (endDate.HasValue)
{
select.AppendFormat(" AND [localDateTime] < '{0}' ", endDate.Value.DateTime.AddDays(1).ToString("s"));
}
EPAResults.StoretResultFlatRow[] rowsWithUnit = (EPAResults.StoretResultFlatRow[])table.Select(select.ToString());
result.timeSeries.values = DataValuesBuilder.CreateValuesElement(rowsWithUnit);
result.timeSeries.values.unitsAbbreviation = unitName; // abbreviated name is presently returned
}
/* for now, just adding the methods, without the reference to the
* value they came from. That will take a bit of work.
* need to generate unique methodID's for distinct methods
* then populate the methodID in the table,
* then send to the value generator
* */
/***** This can cause more method, if nore than one parameter is returned
* eg pH presently returns phosphorous
* */
DataRowCollection methodNameRows = EPAResponseMethods(table);
if (methodNameRows != null && methodNameRows.Count > 0)
{
List <MethodType> methods;
if (result.timeSeries.values.method == null)
{
methods = new List <MethodType>();
}
else
{
methods = new List <MethodType>(result.timeSeries.values.method);
}
foreach (DataRow row in methodNameRows)
{
MethodType method = new MethodType();
method.MethodDescription = row.ItemArray[0].ToString();
methods.Add(method);
}
result.timeSeries.values.method = methods.ToArray();
}
return(result);
}
/// <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;
}
// Return number of records inserted or found
public static int HandleDataValueInfo(SqlConnection sqlConn,
OD_SeriesCatalog odSC, OD_1_1_1DataSet.SeriesCatalogRow scRow,
SiteInfoType siteInfo, seriesCatalogTypeSeries scts, TimeSeriesResponseType tsRt)
{
DataValuesTableAdapter dvAdapter = new DataValuesTableAdapter();
dvAdapter.Connection = sqlConn;
dvAdapter.Adapter.UpdateBatchSize = Program.DbUpdateBatchSize;
dvAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // required to do batch insertion
string siteCode = siteInfo.siteCode[0].network + "|" + siteInfo.siteCode[0].Value;
string varCode = scts.variable.variableCode[0].Value;
string cond;
OD_1_1_1DataSet.DataValuesDataTable tblDataValues = new OD_1_1_1DataSet.DataValuesDataTable();
Console.WriteLine(">>>Parsing and inserting DATAVALUE");
// Get site ID
if (scRow.SiteID == -1)
{
cond = "SiteCode = '" + siteCode + "'";
scRow.SiteID = OD_Utils.GetPrimaryKey("Sites", "SiteID", cond, sqlConn);
}
// Get variable ID
if (scRow.VariableID == -1)
{
string[] vars = Regex.Split(varCode, "/");
string dataType = tsRt.timeSeries[0].variable.dataType;
cond = "VariableCode = '" + vars[0] + "_" + dataType + "'";
scRow.VariableID = OD_Utils.GetPrimaryKey("Variables", "VariableID", cond, sqlConn);
if (scRow.VariableID == -1)
{
Console.WriteLine("Failed to get variable ID from WS TimeSeries info (code: {0} type: {1}).",
varCode, dataType);
cond = "VariableCode = '" + vars[0] + "_" + scRow.DataType + "'";
scRow.VariableID = OD_Utils.GetPrimaryKey("Variables", "VariableID", cond, sqlConn);
if (scRow.VariableID == -1)
{
Console.WriteLine("Also failed to get variable ID with code: {0} and type: {1}. Give up.",
varCode, scRow.DataType);
return(0);
}
else
{
Console.WriteLine("Found variable ID {0} with code: {1} and type: {2} from database.",
scRow.VariableID, varCode, scRow.DataType);
}
}
}
// Update IDs if modified OD_SeriesCatalog odSC,
if (scRow.RowState == DataRowState.Modified)
{
odSC.Adapter.Update(scRow);
}
// Walk through each data value
if ((tsRt.timeSeries[0].values[0].value == null) || (tsRt.timeSeries[0].values[0].value.Count() == 0))
{
Console.WriteLine("No values in WS response.");
return(0);
}
int valueCount = tsRt.timeSeries[0].values[0].value.Count();
int currCount = 0, idx0 = 0, dupCount = 0;
ValueSingleVariable dvInfo0 = null;
bool dup;
// Begin database transaction to make sure the end data time and value count
// in SeriesCatalog and DataValues tables are consistent.
SqlTransaction sqlTrans = sqlConn.BeginTransaction();
for (int i = 0; i < valueCount; i++)
{
dup = false;
ValueSingleVariable dvInfo = tsRt.timeSeries[0].values[0].value[i];
if (dvInfo0 != null)
{
// We have seen many duplicate dvInfo which caused following DataValue insertion failure.
// "Violation of UNIQUE KEY constraint 'UNIQUE_DataValues'. Cannot insert duplicate key in object 'dbo.DataValues'"
// Have to skip the duplicate to avoid the whole batch insertion failure.
// Simplify the dup check by only comparing dateTime.
if (dvInfo0.dateTime == dvInfo.dateTime)
{
dupCount++;
Console.WriteLine("* Index {0} has duplicate time {1:s} with {2}, skip count {3}!",
i, dvInfo.dateTime, idx0, dupCount);
dup = true;
}
}
if (!dup)
{
InsertOneDataValue(tblDataValues, scRow.SiteID, scRow.VariableID, dvInfo);
idx0 = i;
dvInfo0 = dvInfo;
currCount++;
}
if ((currCount == Program.DbUpdateBatchSize) || (i + 1 == valueCount))
{
try
{
dvAdapter.Transaction = sqlTrans;
dvAdapter.Update(tblDataValues);
scRow.EndDateTime = dvInfo.dateTime;
scRow.EndDateTimeUTC = scRow.EndDateTime.ToUniversalTime();
scRow.ValueCount += currCount;
odSC.Adapter.Transaction = sqlTrans;
odSC.Adapter.Update(scRow);
sqlTrans.Commit();
}
catch (Exception e)
{
Console.WriteLine("!!!!!! Got exception: {0}.", e.Message);
Console.WriteLine("* Inserted {0} of {1} records for site {2} variable {3}",
i + 1 - currCount, valueCount, siteCode, varCode);
Console.WriteLine("* Rollback {0} records with {0} of {1} completed!",
currCount, i + 1 - currCount, valueCount);
sqlTrans.Rollback();
tblDataValues.Clear();
return(i + 1 - currCount);
}
currCount = 0;
tblDataValues.Clear();
if (i + 1 != valueCount)
{
sqlTrans = sqlConn.BeginTransaction();
}
}
}
dvAdapter.Transaction = null;
odSC.Adapter.Transaction = null;
return(valueCount - dupCount);
}
public static SiteInfoResponseTypeSite CreateSite(SiteInfoType sit, Boolean addSeriesCatalog)
{
SiteInfoResponseTypeSite site = new SiteInfoResponseTypeSite();
if (sit != null)
{
site.siteInfo = sit;
if (addSeriesCatalog)
{
int? aSiteID = null;
Nullable<int> siteIDint = null;
try
{
aSiteID = sit.siteCode[0].siteID;
}
catch (NullReferenceException e)
{
String error = " no site code returned for sitecode" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
try
{
siteIDint = Convert.ToInt32(aSiteID);
}
catch (InvalidCastException e)
{
String error = " siteID was not an integer" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
try
{
site.seriesCatalog = CreateSeriesCatalogRecord(siteIDint);
}
catch (InvalidCastException e)
{
String error = " Error creating series record for " + sit.siteCode[0].network + ":" + sit.siteCode[0].Value;
log.Warn(error);
site.seriesCatalog = CreateSeriesCatalogRecord(null);
}
}
}
else
{
String error = " no site code returned for sitecode" + sit.siteCode[0].network + ":" + sit.siteCode[0].Value; ;
log.Warn(error);
}
return site;
}
/// <summary>
/// Creates the time series object.
/// Use CreateTimeSeriesObjectSingleValue to get an object populated with values object
/// </summary>
/// <returns></returns>
public static TimeSeriesResponseType CreateTimeSeriesObject(int numberOfSeries)
{
TimeSeriesResponseType result = new TimeSeriesResponseType();
result.queryInfo = new QueryInfoType();
result.queryInfo.criteria = new QueryInfoTypeCriteria();
result.queryInfo.criteria.timeParam = new QueryInfoTypeCriteriaTimeParam();
result.timeSeries = new TimeSeriesType[numberOfSeries];
for (int i = 0; i < result.timeSeries.Length; i++)
{
result.timeSeries[i] = TimeSeriesObjectSingleValue();
}
SiteInfoType site = new SiteInfoType();
site.siteCode = new SiteInfoTypeSiteCode[1];
SiteInfoTypeSiteCode siteId = new SiteInfoTypeSiteCode();
site.siteCode[0] = siteId;
result.timeSeries[0].sourceInfo = site;
result.timeSeries[0].variable = CreateVariableDescriptionType();//new VariablesTypeVariable[1];
//result.timeseries.variable[0] = new VariableDescriptionType(); // new VariablesTypeVariable();
//result.timeseries.variable.variableCode = new VariableDescriptionTypeVariableID[1];//added to simplify population
//result.timeseries.variable.variableCode[0] = new VariableDescriptionTypeVariableID();
return result;
}
public static SiteInfoType row2SiteInfoElement(siteInfoDataSet.sitesRow row, siteInfoDataSet ds)
{
SiteInfoType sit = CuahsiBuilder.CreateASiteInfoTypeWithLatLongPoint();
sit.siteName = !String.IsNullOrEmpty(row.SiteName) ? row.SiteName : null;
// sit created with a single siteCode
sit.siteCode[0].siteID = row.SiteID.ToString();
sit.siteCode[0].Value = !String.IsNullOrEmpty(row.SiteCode) ? row.SiteCode : null;
string siteNetwork = System.Configuration.ConfigurationManager.AppSettings["network"];
sit.siteCode[0].network = siteNetwork;
// we have a point
((LatLonPointType)sit.geoLocation.geogLocation).latitude = row.Latitude;
((LatLonPointType)sit.geoLocation.geogLocation).longitude = row.Longitude;
if (row.LatLongDatumID == 0)
{
((LatLonPointType)sit.geoLocation.geogLocation).srs =
ConfigurationManager.AppSettings["defaultSpatialReferenceSystemSRS"];
}
else
{
siteInfoDataSet.SpatialReferencesRow[] datum =
(siteInfoDataSet.SpatialReferencesRow[])ds.SpatialReferences.Select("SpatialReferenceID = " + row.LatLongDatumID);
if (datum.Length > 0 && !datum[0].IsSRSIDNull())
{
((LatLonPointType)sit.geoLocation.geogLocation).srs =
ConfigurationManager.AppSettings["SRSPrefix"] + datum[0].SRSID;
}
}
if (!row.IsElevation_mNull())
{
sit.elevation_m = row.Elevation_m;
sit.elevation_mSpecified = true;
if (!row.IsVerticalDatumNull())
{
sit.verticalDatum = row.VerticalDatum;
}
}
if (!row.IsLocalXNull() && !row.IsLocalYNull() && !row.IsLocalProjectionIDNull())
{
sit.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
SiteInfoTypeGeoLocationLocalSiteXY xy = new SiteInfoTypeGeoLocationLocalSiteXY();
xy.X = row.LocalX;
xy.Y = row.LocalY;
//@TODO local XY
siteInfoDataSet.SpatialReferencesRow[] datum =
(siteInfoDataSet.SpatialReferencesRow[])ds.SpatialReferences.Select("SpatialReferenceID = " + row.LocalProjectionID);
if (datum.Length > 0)
{
if (!String.IsNullOrEmpty(datum[0].SRSName))
{
xy.projectionInformation = datum[0].SRSName;
}
else
{
xy.projectionInformation = ConfigurationManager.AppSettings["SRSPrefix"] + datum[0].SRSID;
}
}
sit.geoLocation.localSiteXY[0] = xy;
}
if (!row.IsCountyNull())
{
NoteType aNote =
CuahsiBuilder.createNote(row.County, "County", null);
sit.note = CuahsiBuilder.addNote(sit.note, aNote);
}
if (!row.IsStateNull())
{
NoteType aNote =
CuahsiBuilder.createNote(row.State, "State", null);
sit.note = CuahsiBuilder.addNote(sit.note, aNote);
}
if (!row.IsCommentsNull())
{
NoteType aNote =
CuahsiBuilder.createNote(row.Comments, "Site Comments", null);
sit.note = CuahsiBuilder.addNote(sit.note, aNote);
}
return(sit);
}
public TimeSeriesResponseType getValues(string SiteNumber, string Variable, string StartDate, string EndDate)
{
// convert dates
// get site info
// get site ID
// return value dataset
TimeSeriesResponseType response;
W3CDateTime? BeginDateTime;
W3CDateTime? EndDateTime;
int?variableId = null;
int?siteID;
VariableInfoType varInfoType = null;
SiteInfoType siteType = null;
if (!String.IsNullOrEmpty(StartDate))
{
try
{
BeginDateTime = W3CDateTime.Parse(StartDate);
}
catch
{
throw new WaterOneFlowException("Improper BeginDate '" +
StartDate + "' Must be YYYY-MM-DD");
}
}
else
{
BeginDateTime = null;
}
if (!String.IsNullOrEmpty(EndDate))
{
try
{
EndDateTime = W3CDateTime.Parse(EndDate);
}
catch
{
throw new WaterOneFlowException("Improper EndDate '" +
EndDate + "' Must be YYYY-MM-DD");
}
}
else
{
EndDateTime = null;
}
VariableParam vp;
if (Variable != null)
{
vp = new VariableParam(Variable);
VariableInfoType[] v = ODvariables.getVariable(vp, variableDs);
if (v != null && v.Length > 0)
{
variableId = Convert.ToInt16(v[0].variableCode[0].variableID);
varInfoType = v[0];
}
else
{
throw new WaterOneFlowException("Variable parameter not found: " + Variable);
}
}
else
{
throw new WaterOneFlowException("Variable parameter is required ");
}
locationParam sq;
sq = new locationParam(SiteNumber);
if (sq.isGeometry)
{
throw new WaterOneFlowException("Location by Geometry not accepted: " + SiteNumber);
}
siteInfoDataSet sitDs = ODSiteInfo.GetSiteInfoDataSet(sq);
if (sitDs != null && sitDs.sites.Count > 0)
{
siteID = sitDs.sites[0].SiteID;
ValuesDataSet valuesDs = getValuesDataset(siteID, variableId, BeginDateTime, EndDateTime, vp);
response = getValues(valuesDs, vp);
// above is the values, add the site, and variables
response.timeSeries.variable = varInfoType;
if (varInfoType != null)
{
if (varInfoType.units != null)
{
response.timeSeries.values.unitsAbbreviation = varInfoType.units.unitsAbbreviation;
response.timeSeries.values.unitsCode = varInfoType.units.unitsCode;
if (varInfoType.units.unitsType != null)
{
response.timeSeries.values.unitsType = varInfoType.units.unitsType;
response.timeSeries.values.unitsTypeSpecified = true;
}
response.timeSeries.values.unitsAreConverted = false;
}
}
response.timeSeries.sourceInfo = ODSiteInfo.row2SiteInfoElement(sitDs.sites[0], sitDs);
}
else
{
response = CuahsiBuilder.CreateTimeSeriesObject();
}
// add query info
response.queryInfo.creationTime = DateTime.UtcNow;
//response.queryInfo.creationTime = DateTimeOffset.UtcNow;
response.queryInfo.creationTimeSpecified = true;
response.queryInfo.criteria.locationParam = SiteNumber;
response.queryInfo.criteria.variableParam = Variable;
response.queryInfo.criteria.timeParam = CuahsiBuilder.createQueryInfoTimeCriteria(StartDate, EndDate);
NoteType sourceNote = CuahsiBuilder.createNote("OD Web Service");
response.queryInfo.note = CuahsiBuilder.addNote(response.queryInfo.note,
sourceNote);
return(response);
}
/// <summary>
/// Creates the time series object.
/// Use CreateTimeSeriesObjectSingleValue to get an object populated with values object
/// </summary>
/// <returns></returns>
public static TimeSeriesResponseType CreateTimeSeriesObject()
{
TimeSeriesResponseType result = new TimeSeriesResponseType();
result.queryInfo = new QueryInfoType();
result.queryInfo.criteria = new QueryInfoTypeCriteria();
result.queryInfo.criteria.timeParam = new QueryInfoTypeCriteriaTimeParam();
result.timeSeries = new TimeSeriesType();
SiteInfoType site = new SiteInfoType();
site.siteCode = new SiteInfoTypeSiteCode[1];
SiteInfoTypeSiteCode siteId = new SiteInfoTypeSiteCode();
site.siteCode[0] = siteId;
result.timeSeries.sourceInfo = site;
result.timeSeries.variable = CreateVariableDescriptionType();//new VariablesTypeVariable[1];
//result.timeseries.variable[0] = new VariableDescriptionType(); // new VariablesTypeVariable();
//result.timeseries.variable.variableCode = new VariableDescriptionTypeVariableID[1];//added to simplify population
//result.timeseries.variable.variableCode[0] = new VariableDescriptionTypeVariableID();
return result;
}
public static SiteInfoType createSiteInfoRecord(DataRow aRow)
{
CoreTables.SitesDataTable sDs = new CoreTables.SitesDataTable();
sDs.ImportRow(aRow);
sDs.AcceptChanges();
SiteInfoType si = new SiteInfoType();
if (sDs.Rows.Count > 0)
{
CoreTables.SitesRow row =
(CoreTables.SitesRow)sDs.Rows[0];
string siteVocabulary;
string siteCode;
string siteName;
float? latitude = null;
float? longitude = null;
string spatialReferenceSystem = null;
siteName = row.IsSiteNameNull() ? null : row.SiteName;
siteCode = row.SiteCode;
siteVocabulary = row.IsSiteVocabularyNull() ? null : row.SiteVocabulary;
if (!row.IsLatitudeNull())
{
latitude = row.Latitude;
}
if (!row.IsLongitudeNull())
{
longitude = row.Longitude;
}
if (!row.IsLatLongDatumNameNull())
{
spatialReferenceSystem = row.LatLongDatumName;
}
si =
CreateASiteInfoTypeWithLatLongPoint(siteVocabulary, siteCode, siteName, latitude, longitude,
spatialReferenceSystem);
// add other standard items
if (!row.IsElevation_mNull())
{
si.elevation_m = row.Elevation_m;
si.elevation_mSpecified = true;
if (!row.IsVerticalDatumNull())
{
si.verticalDatum = row.VerticalDatum;
}
}
if (!row.IsCommentsNull())
{
NoteType n = new NoteType();
n.type = "comment";
n.title = "comment";
n.Value = row.Comments;
si.note = addNote(si.note, n);
}
if (!row.IsStateNull())
{
NoteType n = new NoteType();
n.title = "State";
n.Value = row.State;
si.note = addNote(si.note, n);
}
if (!row.IsCountyNull())
{
NoteType n = new NoteType();
n.title = "County";
n.Value = row.County;
si.note = addNote(si.note, n);
}
// assuming that if you have an enumated name, that it will work
if (!row.Isodws_TimeZoneAbbreviationNull())
{
row.TimeZoneAbbreviation = row.odws_TimeZoneAbbreviation;
}
if (!row.Isodws_TimeZoneOffsetNull())
{
row.TimeZoneOffset = row.odws_TimeZoneOffset;
}
// time zone
if (!row.IsTimeZoneAbbreviationNull() || !row.IsTimeZoneOffsetNull())
{
SiteInfoTypeTimeZoneInfo tz = new SiteInfoTypeTimeZoneInfo();
tz.defaultTimeZone = new SiteInfoTypeTimeZoneInfoDefaultTimeZone();
tz.defaultTimeZone.zoneOffset = row.IsTimeZoneOffsetNull() ?
null : row.TimeZoneOffset;
// if TZ abbreviation. figure out the offset
if (!row.IsTimeZoneAbbreviationNull())
{
tz.defaultTimeZone.zoneAbbreviation = row.TimeZoneAbbreviation;
if (tz.defaultTimeZone.zoneOffset == null)
{
// calc timezone
// right now this returns null
tz.defaultTimeZone.zoneOffset = timeZoneOffset(row.TimeZoneAbbreviation);
}
}
si.timeZoneInfo = tz;
}
}
return(si);
}
static void InsertOneSource(OD_1_1_1DataSet.SourcesDataTable srcTable,
SiteInfoType stinfo, seriesCatalogTypeSeries scts, TimeSeriesType tst,
int metadataID, SqlConnection sqlConn)
{
OD_1_1_1DataSet.SourcesRow row = srcTable.NewSourcesRow();
row.SourceID = scts.source.sourceID;
row.Organization = scts.source.organization;
row.SourceDescription = scts.source.sourceDescription;
row.MetadataID = metadataID;
string tbd = "TBD";
row.SourceLink = tbd;
if (scts.source.sourceLink != null)
{
row.SourceLink = scts.source.sourceLink[0];
}
row.ContactName = row.Phone = row.Email =
row.Address = row.City = row.State =
row.ZipCode = row.Citation = tbd;
for (int i = 0; (stinfo.note != null) && (i < stinfo.note.Count()); i++)
{
NoteType note = stinfo.note[i];
switch (note.title)
{
case "ContactName":
row.ContactName = note.Value;
break;
case "Phone":
row.Phone = note.Value;
break;
case "Email":
row.Email = note.Value;
break;
case "Address":
row.Address = note.Value;
break;
case "City":
row.City = note.Value;
break;
case "State":
row.State = note.Value;
break;
case "ZipCode":
row.ZipCode = note.Value;
break;
case "Citation":
row.Citation = note.Value;
break;
}
}
//srcTable.AddSourcesRow(row);
string sql = string.Format(@"SET IDENTITY_INSERT [Sources] ON;
INSERT INTO [Sources]
([SourceID], [Organization], [SourceDescription], [SourceLink],
[ContactName], [Phone], [Email], [Address], [City], [State], [ZipCode],
[Citation], [MetadataID])
VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', {12});
SET IDENTITY_INSERT [Sources] OFF",
row.SourceID, row.Organization, row.SourceDescription, row.SourceLink,
row.ContactName, row.Phone, row.Email, row.Address, row.City, row.State, row.ZipCode,
row.Citation, row.MetadataID);
OD_Utils.RunNonQuery(sql, sqlConn);
}
public static SiteInfoType row2SiteInfoElement(siteInfoDataSet.sitesRow row, siteInfoDataSet ds)
{
SiteInfoType sit = CuahsiBuilder.CreateASiteInfoTypeWithLatLongPoint();
sit.siteName = row.SiteName;
// sit created with a single siteCode
sit.siteCode[0].siteID = row.SiteID;
sit.siteCode[0].siteIDSpecified = true;
sit.siteCode[0].Value = row.SiteCode;
string siteNetwork = System.Configuration.ConfigurationManager.AppSettings["network"];
sit.siteCode[0].network = siteNetwork;
// we ALWAYS have a point in ODM 1.1
/* just check to make sure that they are not the defaults
* Should validate thet they are inwithin +-180/+-90
* */
if (!row.Longitude.Equals(0) && !row.Latitude.Equals(0))
{
((LatLonPointType)sit.geoLocation.geogLocation).latitude = row.Latitude;
((LatLonPointType)sit.geoLocation.geogLocation).longitude = row.Longitude;
if (row.LatLongDatumID.Equals(0))
{
((LatLonPointType)sit.geoLocation.geogLocation).srs =
ConfigurationManager.AppSettings["defaultSpatialReferenceSystemSRS"];
}
else
{
siteInfoDataSet.SpatialReferencesRow[] datum =
(siteInfoDataSet.SpatialReferencesRow[])ds.SpatialReferences.Select("SpatialReferenceID = " + row.LatLongDatumID);
if (datum.Length > 0)
{
if (!datum[0].IsSRSIDNull())
{
((LatLonPointType)sit.geoLocation.geogLocation).srs =
ConfigurationManager.AppSettings["SRSPrefix"] + datum[0].SRSID;
}
else if (!string.IsNullOrEmpty(datum[0].SRSName))
{
((LatLonPointType)sit.geoLocation.geogLocation).srs = datum[0].SRSName;
}
}
}
}
else
{
sit.geoLocation.geogLocation = null;
}
if (!row.IsElevation_mNull())
{
sit.elevation_m = row.Elevation_m;
sit.elevation_mSpecified = true;
if (!row.IsVerticalDatumNull())
{
sit.verticalDatum = row.VerticalDatum;
}
}
if (!row.IsLocalXNull() && !row.IsLocalYNull() && !row.IsLocalProjectionIDNull())
{
sit.geoLocation.localSiteXY = new SiteInfoTypeGeoLocationLocalSiteXY[1];
SiteInfoTypeGeoLocationLocalSiteXY xy = new SiteInfoTypeGeoLocationLocalSiteXY();
xy.X = row.LocalX;
xy.Y = row.LocalY;
//@TODO local XY
siteInfoDataSet.SpatialReferencesRow[] datum =
(siteInfoDataSet.SpatialReferencesRow[])ds.SpatialReferences.Select("SpatialReferenceID = " + row.LocalProjectionID);
if (datum.Length > 0)
{
if (!String.IsNullOrEmpty(datum[0].SRSName))
{
xy.projectionInformation = datum[0].SRSName;
}
else
{
xy.projectionInformation = "Not Specified";
}
}
sit.geoLocation.localSiteXY[0] = xy;
}
if (!row.IsCountyNull())
{
PropertyType aNote =
CuahsiBuilder.createProperty(row.County, "County", null);
sit.siteProperty = CuahsiBuilder.addProperty(sit.siteProperty, aNote);
}
if (!row.IsStateNull())
{
PropertyType aNote =
CuahsiBuilder.createProperty(row.State, "State", null);
sit.siteProperty = CuahsiBuilder.addProperty(sit.siteProperty, aNote);
}
if (!row.IsCommentsNull())
{
PropertyType aNote =
CuahsiBuilder.createProperty(row.Comments, "Site Comments", null);
sit.siteProperty = CuahsiBuilder.addProperty(sit.siteProperty, aNote);
}
if (!row.IsPosAccuracy_mNull())
{
PropertyType aNote =
CuahsiBuilder.createProperty(row.PosAccuracy_m.ToString(), "PosAccuracy_m", null);
sit.siteProperty = CuahsiBuilder.addProperty(sit.siteProperty, aNote);
}
return(sit);
}
