private static seriesCatalogType createSeriesCatalog(
seriesCatalogDataSet ds,
VariablesDataset vds)
{
if (ds.SeriesCatalog.Count > 0)
{
Boolean useOD;
String siteServiceURL;
String siteServiceName;
try
{
useOD = Boolean.Parse(ConfigurationManager.AppSettings["UseODForValues"]);
if (!useOD)
{
siteServiceURL = ConfigurationManager.AppSettings["externalGetValuesService"];
siteServiceName = ConfigurationManager.AppSettings["externalGetValuesName"];
}
else
{
siteServiceURL = "http://localhost/";
siteServiceName = "OD Web Services";
}
}
catch
{
useOD = true; // should be caught earlier
siteServiceURL = "http://locahost/";
siteServiceName = "Fix UseODForValues, externalGetValuesService, externalGetValuesName";
}
seriesCatalogType catalog = CuahsiBuilder.CreateSeriesCatalog(
ds.SeriesCatalog.Count,
siteServiceName, // menu name (aka OD name
siteServiceURL // web service location
);
List <seriesCatalogTypeSeries> seriesRecords = new List <seriesCatalogTypeSeries>();
foreach (seriesCatalogDataSet.SeriesCatalogRow row in ds.SeriesCatalog.Rows)
{
seriesCatalogTypeSeries aRecord = row2SeriesCatalogElement(
row, ds, vds);
seriesRecords.Add(aRecord);
}
catalog.series = seriesRecords.ToArray();
return(catalog);
}
else
{
seriesCatalogType catalog = CuahsiBuilder.CreateSeriesCatalog(0,
null, // menu name (aka OD name
String.Empty // web service location
);
return(catalog);
}
}
// This routine is to fill in the data what stinfo and scts have. The IDs will
// be updated when handling DataValues.
OD_1_1_1DataSet.SeriesCatalogRow CreateNewSeriesCatalog(
string siteCode, string varCode,
seriesCatalogTypeSeries scts)
{
OD_1_1_1DataSet.SeriesCatalogRow row = Table.NewSeriesCatalogRow();
VariableInfoType varInfo = scts.variable;
row.SiteID = -1;
row.SiteCode = siteCode;
row.SiteName = null;
row.VariableID = -1;
row.VariableCode = varInfo.variableCode[0].Value;
row.VariableName = varInfo.variableName;
row.Speciation = varInfo.speciation;
row.VariableUnitsID = Convert.ToInt32(varInfo.unit.unitCode);
row.VariableUnitsName = varInfo.unit.unitName;
row.SampleMedium = varInfo.sampleMedium;
row.ValueType = varInfo.valueType;
row.TimeSupport = varInfo.timeScale.timeSupport;
row.TimeUnitsID = Convert.ToInt32(varInfo.timeScale.unit.unitCode);
row.TimeUnitsName = varInfo.timeScale.unit.unitName;
row.DataType = varInfo.dataType;
row.GeneralCategory = varInfo.generalCategory;
if (scts.method != null)
{
row.MethodID = scts.method.methodID;
row.MethodDescription = scts.method.methodDescription;
}
row.SourceID = scts.source.sourceID;
row.Organization = scts.source.organization;
row.SourceDescription = scts.source.sourceDescription;
row.SetCitationNull();
row.QualityControlLevelID = scts.qualityControlLevel.qualityControlLevelID;
row.QualityControlLevelCode = scts.qualityControlLevel.qualityControlLevelCode;
// This table is to track what we have in DataValues table.
// We don't have anything in DataValues table at this moment.
TimeIntervalType ti = (TimeIntervalType)scts.variableTimeInterval;
row.BeginDateTime = ti.beginDateTime;
row.EndDateTime = OD_Utils.GetDateTime(row.BeginDateTime, row.TimeUnitsID, -1);
row.BeginDateTimeUTC = ti.beginDateTime.ToUniversalTime();
row.EndDateTimeUTC = row.EndDateTime.ToUniversalTime();
row.ValueCount = 0;
return(row);
}
// 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);
}
// do one at a time
public seriesCatalogType[] GetSeries(locationParam lp)
{
// for this we are only doing one, but multiple can come back
List <seriesCatalogType> seriesCatalogs = new List <seriesCatalogType>();
// seriesCatalog is built from array of series
#region build series
{
List <seriesCatalogTypeSeries> series = new List <seriesCatalogTypeSeries>();
UsgsDbDailyValues.seriesCatalogDataTable scDs = new UsgsDbDailyValues.seriesCatalogDataTable();
UsgsDbDailyValuesTableAdapters.seriesCatalogTableAdapter tableAdaptor = new seriesCatalogTableAdapter();
if (lp != null)
{
if (lp.SiteCode != null)
{
tableAdaptor.FillBySiteCode(scDs, lp.SiteCode);
}
}
foreach (UsgsDbDailyValues.seriesCatalogRow row in scDs.Rows)
{
seriesCatalogTypeSeries s = row2Series(row);
if (s != null)
{
series.Add(s);
}
}
// add the list of series to the
seriesCatalogType aCatalog = new seriesCatalogType();
aCatalog.series = series.ToArray();
seriesCatalogs.Add(aCatalog);
}
#endregion
return(seriesCatalogs.ToArray());
}
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;
}
private static seriesCatalogTypeSeries row2Series(UsgsDbDailyValues.seriesCatalogRow aRow)
{
if (aRow.IsVariableCodeNull())
{
return(null);
}
string VariableCode = aRow.VariableCode;
string VariableName = null;
string VariableUnitName = null;
string VariableUnitAbrreviation = null;
string VariableUnitCode = null;
string sampleMedium = null;
string dataType = null;
string valueType = null;
string generalCategory = null;
W3CDateTime?beginDateTime = null, endDateTime = null;
int? valueCount = null;
bool? valueCountIsEstimated = null;
int? TimeInterval = null;
string TimeIntervalUnits = null;
bool isRealTime = false;
string QualityControlLevelTerm = null;
string methodName = null;
string organization = null;
string sourceDescription = null;
if (!aRow.IsVariableNameNull())
{
VariableName = aRow.VariableName;
}
if (!aRow.IsVariableUnitsAbbreviationNull())
{
VariableUnitAbrreviation = aRow.VariableUnitsAbbreviation;
}
if (!aRow.IsVariableUnitsNameNull())
{
VariableUnitName = aRow.VariableUnitsName;
}
if (!aRow.IsDataTypeNull())
{
dataType = aRow.DataType;
}
if (!aRow.IsValueTypeNull())
{
valueType = aRow.ValueType;
}
if (!aRow.IsGeneralCategoryNull())
{
generalCategory = aRow.GeneralCategory;
}
if (!aRow.IsSampleMediumNull())
{
sampleMedium = aRow.SampleMedium;
}
if (!aRow.IsMethodNameNull())
{
methodName = aRow.MethodName;
}
if (!aRow.IsSourceDescriptionNull())
{
sourceDescription = aRow.SourceDescription;
}
if (!aRow.IsOrganizationNull())
{
organization = aRow.Organization;
}
if (!aRow.IsValueCountNull())
{
int v;
if (Int32.TryParse(aRow.ValueCount, out v))
{
valueCount = v;
}
}
if (!aRow.IsBeginDateTimeNull())
{
beginDateTime = new W3CDateTime(aRow.BeginDateTime);
}
if (!aRow.IsEndDateTimeNull())
{
endDateTime = new W3CDateTime(aRow.EndDateTime);
}
if (!aRow.IsTimeSupportNull())
{
TimeInterval = aRow.TimeSupport;
if (!aRow.IsTimeUnitsNameNull())
{
TimeIntervalUnits = aRow.TimeUnitsName;
}
}
// public static seriesCatalogTypeSeries CreateSeriesRecord(
// string VariableCode,
// string VariableName,
// string VariableUnitName,
// string VariableUnitAbrreviation,
// string VariableUnitCode,
// string sampleMedium,
// string dataType,
// string valueType,
// string generalCategory,
// W3CDateTime? beginDateTime, W3CDateTime? endDateTime,
// int? valueCount, bool? valueCountIsEstimated,
// string TimeInterval,
// string TimeIntervalUnits,
// bool isRealTime,
// string QualityControlLevelTerm,
// string methodName,
// string organization, string sourceDescription
//)
seriesCatalogTypeSeries aSeries = CoreBuilder.CreateSeriesRecord(
VariableCode,
VariableName,
VariableUnitName,
VariableUnitAbrreviation,
VariableUnitCode,
sampleMedium,
dataType,
valueType,
generalCategory,
beginDateTime, endDateTime,
valueCount, valueCountIsEstimated,
TimeInterval,
TimeIntervalUnits,
isRealTime,
QualityControlLevelTerm,
methodName,
organization,
sourceDescription, "NWIS");
// add usgs statistic code options
if (!aRow.Isusgs_stat_cdNull())
{
List <option> opts = new List <option>(1);
option opt = new option();
opt.name = "statistic";
opt.optionCode = aRow.usgs_stat_cd;
opts.Add(opt);
aSeries.variable.options = opts.ToArray();
}
return(aSeries);
}
public static seriesCatalogTypeSeries CreateSeriesRecord(VariableInfoType variable, string sampleMedium, W3CDateTime?beginDateTime, W3CDateTime?endDateTime, int?valueCount, bool?valueCountIsEstimated, string dataType, string valueType, string generalCategory, bool realTime, string realTimeInterval, string QualityControlLevelTerm, int?qualityControlLevelid, string methodName, int?methodID, string organization, string sourceDescription, int?sourceID)
{
/* don't forget to check the VariableInfoType for the
* dataType,ValueType and General Category
*
*/
/*
* logic
* create seriesCatalogTypeSeries
* if variable != null use it. if null, make an empty variable
* if variable != null
* and sampleMedium,dataType,valueType or generalCategory is null
* try to get value from variable
* add datTime interface logic
*
*/
seriesCatalogTypeSeries series = new seriesCatalogTypeSeries();
series.variable = variable != null ? variable : CreateVariableDescriptionType();
if (valueCount.HasValue)
{
series.valueCount = new seriesCatalogTypeSeriesValueCount();
series.valueCount.Value = valueCount.Value;
}
// if begin time and end time are the same use single
if (beginDateTime.HasValue)
{
TimeIntervalType tm = new TimeIntervalType();
tm.beginDateTime = beginDateTime.Value.DateTime;
if (endDateTime.HasValue)
{
tm.endDateTime = endDateTime.Value.DateTime;
}
else
{
tm.endDateTime = tm.beginDateTime;
}
series.variableTimeInterval = tm;
}
if (realTime)
{
TimePeriodRealTimeType rt = new TimePeriodRealTimeType();
rt.realTimeDataPeriod = realTimeInterval; // 31 day Period
rt.beginDateTime = DateTime.Today.AddDays(-31);
rt.endDateTime = DateTime.Today;
series.variableTimeInterval = rt;
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (qualityControlLevelid.HasValue || !String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.QualityControlLevel = new QualityControlLevelType();
// need to have a dataset with an enum so we can
// SetEnumFromText(qsds,row,series.QualityControlLevel, typeof(QualityControlLevelEnum));
if (qualityControlLevelid.HasValue)
{
series.QualityControlLevel.qualityControlLevelID = qualityControlLevelid.Value;
series.QualityControlLevel.qualityControlLevelIDSpecified = true;
}
if (!String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.QualityControlLevel.Value = QualityControlLevelTerm;
// originally defined as an enum, but enums cannot be empty, and we have an id
// QualityControlLevelTerm = QualityControlLevelTerm.Replace(" ", "");
// if (Enum.IsDefined(typeof(QualityControlLevelEnum), QualityControlLevelTerm))
// {
// object aEnum = Enum.Parse(typeof(QualityControlLevelEnum), QualityControlLevelTerm);
// series.QualityControlLevel.Value = (QualityControlLevelEnum)aEnum;
// }
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!String.IsNullOrEmpty(methodName) || methodID.HasValue)
{
MethodType method = new MethodType();
if (!String.IsNullOrEmpty(methodName))
{
method.MethodDescription = methodName;
}
if (methodID.HasValue)
{
method.methodID = methodID.Value;
}
series.Method = method;
}
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization) ||
sourceID.HasValue)
{
SourceType source = new SourceType();
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization))
{
source.SourceDescription = sourceDescription;
source.Organization = organization;
}
if (sourceID.HasValue)
{
source.sourceID = sourceID.Value;
}
series.Source = source;
}
return(series);
}
public static seriesCatalogTypeSeries row2SeriesCatalogElement(seriesCatalogDataSet.SeriesCatalogRow row, seriesCatalogDataSet ds, VariablesDataset vds, ControlledVocabularyDataset vocabularyDataset)
{
int variableID = row.VariableID;
VariableInfoType variable = ODvariables.GetVariableByID(variableID, vds);
Nullable <W3CDateTime> beginDateTime = null;
if (!row.IsBeginDateTimeNull())
{
TimeSpan timeSpan = row.BeginDateTime.Subtract(row.BeginDateTimeUTC);
beginDateTime = new W3CDateTime(row.BeginDateTime, timeSpan);
}
Nullable <W3CDateTime> endDateTime = null;
if (!row.IsEndDateTimeNull())
{
TimeSpan timeSpan = row.EndDateTime.Subtract(row.EndDateTimeUTC);
endDateTime = new W3CDateTime(row.EndDateTime, timeSpan);
}
Nullable <int> valueCount = null;
if (!row.IsValueCountNull())
{
valueCount = row.ValueCount;
}
string qualityControlLevelTerm = null;
int? QualityControlLevelid = null;
if (!row.IsQualityControlLevelIDNull())
{
QualityControlLevelid = row.QualityControlLevelID;
ControlledVocabularyDataset.QualityControlLevelsRow qcRow =
vocabularyDataset.QualityControlLevels.FindByQualityControlLevelID(QualityControlLevelid.Value);
if (qcRow != null)
{
qualityControlLevelTerm = qcRow.Definition;
}
}
int?MethodID = null;
if (!row.IsMethodIDNull())
{
MethodID = row.MethodID;
}
int?SourceID = null;
if (!row.IsSourceIDNull())
{
SourceID = row.SourceID;
}
Nullable <Boolean> valueCountIsEstimated = false;
/* public static seriesCatalogTypeSeries createSeriesCatalogRecord(
* VariableInfoType variable,
* string sampleMedium,
* Nullable<W3CDateTime> beginDateTime,
* Nullable<W3CDateTime> endDateTime,
* Nullable<int> valueCount,
* Nullable<Boolean> valueCountIsEstimated,
* string dataType,
* string valueType,
* string generalCategory
* )
*/
seriesCatalogTypeSeries record = CuahsiBuilder.CreateSeriesRecord(
variable,
variable.sampleMedium.ToString(),
beginDateTime,
endDateTime,
valueCount,
valueCountIsEstimated,
null,
null,
null,
false, // real time
null, // string if real time
row.QualityControlLevelCode,
QualityControlLevelid,
row.MethodDescription,
MethodID, row.Organization,
row.SourceDescription,
SourceID,
row.Citation,
true, // include UTC Time
qualityControlLevelTerm
);
return(record);
}
public static seriesCatalogTypeSeries row2SeriesCatalogElement(
seriesCatalogDataSet.SeriesCatalogRow row,
seriesCatalogDataSet ds, VariablesDataset vds)
{
int variableID = row.VariableID;
VariableInfoType variable = ODvariables.GetVariableByID(variableID, vds);
Nullable <W3CDateTime> beginDateTime = null;
if (!row.IsBeginDateTimeNull())
{
beginDateTime = new W3CDateTime(row.BeginDateTime);
}
Nullable <W3CDateTime> endDateTime = null;
if (!row.IsEndDateTimeNull())
{
endDateTime = new W3CDateTime(row.EndDateTime);
}
Nullable <int> valueCount = null;
if (!row.IsValueCountNull())
{
valueCount = row.ValueCount;
}
int?QualityControlLevelid = null;
if (!row.IsQualityControlLevelIDNull())
{
QualityControlLevelid = row.QualityControlLevelID;
}
int?MethodID = null;
if (!row.IsMethodIDNull())
{
MethodID = row.MethodID;
}
int?SourceID = null;
if (!row.IsSourceIDNull())
{
SourceID = row.SourceID;
}
Nullable <Boolean> valueCountIsEstimated = false;
seriesCatalogTypeSeries record = CuahsiBuilder.CreateSeriesRecord(
variable,
variable.sampleMedium.ToString(),
beginDateTime,
endDateTime,
valueCount,
valueCountIsEstimated,
null,
null,
null,
false, // real time
null, // string if real time
null,
QualityControlLevelid,
row.MethodDescription, MethodID,
row.Organization, row.SourceDescription,
SourceID,
row.Citation);
return(record);
}
public static seriesCatalogTypeSeries CreateSeriesRecord(string VariableCode, string VariableName, string VariableUnitName, string VariableUnitAbrreviation, string VariableUnitCode, string sampleMedium, string dataType, string valueType, string generalCategory, W3CDateTime?beginDateTime, W3CDateTime?endDateTime, int?valueCount, bool?valueCountIsEstimated, int?TimeInterval, string TimeIntervalUnits, bool isRealTime, string QualityControlLevelTerm, string methodName, string organization, string sourceDescription, string VariableVocabulary)
{
/* don't forget to check the VariableInfoType for the
* dataType,ValueType and General Category
*
*/
/*
* logic
* create seriesCatalogTypeSeries
* if variable != null use it. if null, make an empty variable
* if variable != null
* and sampleMedium,dataType,valueType or generalCategory is null
* try to get value from variable
* add datTime interface logic
*
*/
seriesCatalogTypeSeries series = new seriesCatalogTypeSeries();
//series.variable = variable != null ? variable : CreateVariableDescriptionType();
//VariableInfoType vdt = new VariableInfoType();
//variableCode[] var = new variableCode[1];
//var[0] = new variableCode();
//vdt.variableCode = var;
//vdt.units = new units();
//return vdt;
// create a VariablesRow, fill it. pass it to row2Variable()
CoreTables.VariablesDataTable vDs = new CoreTables.VariablesDataTable();
CoreTables.VariablesRow row = vDs.NewVariablesRow();
row.VariableCode = VariableCode;
row.VariableName = VariableName;
row.VariableUnitsName = VariableUnitName;
row.VariableUnitsAbbreviation = VariableUnitAbrreviation;
row.ValueType = valueType;
row.DataType = dataType;
row.SampleMedium = sampleMedium;
row.GeneralCategory = generalCategory;
row.VariableVocabulary = VariableVocabulary;
vDs.Rows.Add(row);
vDs.AcceptChanges();
series.variable = CreateVariableRecord(row);
if (valueCount.HasValue)
{
series.valueCount = new seriesCatalogTypeSeriesValueCount();
series.valueCount.Value = valueCount.Value;
}
//set the time interval
if (isRealTime)
{
TimePeriodRealTimeType rt = new TimePeriodRealTimeType();
rt.realTimeDataPeriod = TimeInterval.ToString(); // 31 day Period
rt.beginDateTime = DateTime.Today.AddDays(-31);
rt.endDateTime = DateTime.Today;
series.variableTimeInterval = rt;
}
else
{
if (beginDateTime.HasValue)
{
TimeIntervalType tm = new TimeIntervalType();
tm.beginDateTime = beginDateTime.Value.DateTime;
if (endDateTime.HasValue)
{
tm.endDateTime = endDateTime.Value.DateTime;
}
else
{
tm.endDateTime = tm.beginDateTime;
}
series.variableTimeInterval = tm;
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.qualityControlLevel = new QualityControlLevelType();
// need to have a dataset with an enum so we can
// SetEnumFromText(qsds,row,series.QualityControlLevel, typeof(QualityControlLevelEnum));
if (!String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.qualityControlLevel.definition = QualityControlLevelTerm;
// originally defined as an enum, but enums cannot be empty, and we have an id
// QualityControlLevelTerm = QualityControlLevelTerm.Replace(" ", "");
// if (Enum.IsDefined(typeof(QualityControlLevelEnum), QualityControlLevelTerm))
// {
// object aEnum = Enum.Parse(typeof(QualityControlLevelEnum), QualityControlLevelTerm);
// series.QualityControlLevel.Value = (QualityControlLevelEnum)aEnum;
// }
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!String.IsNullOrEmpty(methodName))
{
MethodType method = new MethodType();
if (!String.IsNullOrEmpty(methodName))
{
method.methodDescription = methodName;
}
series.method = method;
}
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization))
{
SourceType source = new SourceType();
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization))
{
source.sourceDescription = sourceDescription;
source.organization = organization;
}
series.source = source;
}
return(series);
}
public static seriesCatalogTypeSeries CreateSeriesRecord(DataRow row)
{
seriesCatalogTypeSeries series = new seriesCatalogTypeSeries();
CoreTables.SeriesCatalogDataTable scDt = new CoreTables.SeriesCatalogDataTable();
scDt.ImportRow(row);
CoreTables.SeriesCatalogRow scRow = (CoreTables.SeriesCatalogRow)scDt.Rows[0];
//series.variable = variable != null ? variable : CreateVariableDescriptionType();
//VariableInfoType vdt = new VariableInfoType();
//variableCode[] var = new variableCode[1];
//var[0] = new variableCode();
//vdt.variableCode = var;
//vdt.units = new units();
//return vdt;
// create a VariablesRow, fill it. pass it to row2Variable()
CoreTables.VariablesDataTable vDt = new CoreTables.VariablesDataTable();
vDt.ImportRow(row);
series.variable = CreateVariableRecord(row);
if (!scRow.IsValueCountNull())
{
series.valueCount = new seriesCatalogTypeSeriesValueCount();
series.valueCount.Value = scRow.ValueCount;
}
//set the time interval
// if (isRealTime)
if (!scRow.IsRealTimeStationNull() &&
scRow.RealTimeStation)
{
TimePeriodRealTimeType rt = new TimePeriodRealTimeType();
int timeInterval = 0;
if (!scRow.IsRealTimeAvailabilityInDaysNull())
{
rt.realTimeDataPeriod = scRow.RealTimeAvailabilityInDays.ToString(); // 31 day Period
rt.beginDateTime = DateTime.Today.AddDays((-1) * scRow.RealTimeAvailabilityInDays);
}
else
{
rt.realTimeDataPeriod = "31"; // 31 day Period
rt.beginDateTime = DateTime.Today.AddDays(-31);
}
rt.endDateTime = DateTime.Today;
series.variableTimeInterval = rt;
}
else
{
if (!scRow.IsBeginDateTimeNull())
{
TimeIntervalType tm = new TimeIntervalType();
tm.beginDateTime = DateTime.Parse(scRow.BeginDateTime);
if (!scRow.IsEndDateTimeNull())
{
tm.endDateTime = DateTime.Parse(scRow.EndDateTime);;
}
else
{
tm.endDateTime = tm.beginDateTime;
}
series.variableTimeInterval = tm;
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
// if (!String.IsNullOrEmpty(QualityControlLevelTerm))
if (!scRow.IsQualityControlLevelIDNull() ||
!scRow.IsQualityControlLevelTermNull())
{
series.qualityControlLevel = new QualityControlLevelType();
// need to have a dataset with an enum so we can
// SetEnumFromText(qsds,row,series.QualityControlLevel, typeof(QualityControlLevelEnum));
if (!scRow.IsQualityControlLevelTermNull())
{
series.qualityControlLevel.definition = scRow.QualityControlLevelTerm;
// originally defined as an enum, but enums cannot be empty, and we have an id
// QualityControlLevelTerm = QualityControlLevelTerm.Replace(" ", "");
// if (Enum.IsDefined(typeof(QualityControlLevelEnum), QualityControlLevelTerm))
// {
// object aEnum = Enum.Parse(typeof(QualityControlLevelEnum), QualityControlLevelTerm);
// series.QualityControlLevel.Value = (QualityControlLevelEnum)aEnum;
// }
}
else
{
// hard code conversion to term
int qcId = scRow.QualityControlLevelID;
switch (qcId)
{
case 0:
series.qualityControlLevel.definition = "Raw data";
break;
case 1:
series.qualityControlLevel.definition = "Quality controlled data";
break;
case 2:
series.qualityControlLevel.definition = "Derived products";
break;
case 3:
series.qualityControlLevel.definition = "Interpreted products";
break;
case 4:
series.qualityControlLevel.definition = "Knowledge products";
break;
case -9999:
default:
series.qualityControlLevel.definition = "Unknown";
break;
}
}
}
else
{
series.qualityControlLevel = new QualityControlLevelType();
series.qualityControlLevel.definition = "Unknown";
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!scRow.IsMethodNameNull())
{
MethodType method = new MethodType();
if (!String.IsNullOrEmpty(scRow.MethodName))
{
method.methodDescription = scRow.MethodName;
}
series.method = method;
}
if (!scRow.IsSourceDescriptionNull() ||
!scRow.IsOrganizationNull())
{
SourceType source = new SourceType();
if (!scRow.IsSourceDescriptionNull())
{
source.sourceDescription = scRow.SourceDescription;
}
if (!scRow.IsOrganizationNull())
{
source.organization = scRow.Organization;
}
series.source = source;
}
return(series);
}
public static seriesCatalogTypeSeries CreateSeriesRecord(VariableInfoType variable, string sampleMedium, W3CDateTime? beginDateTime, W3CDateTime? endDateTime, int? valueCount, bool? valueCountIsEstimated, string dataType, string valueType, string generalCategory, bool realTime, string realTimeInterval, string QualityControlLevelTerm, int? qualityControlLevelid, string methodName, int? methodID, string organization, string sourceDescription, int? sourceID, string citation)
{
/* don't forget to check the VariableInfoType for the
* dataType,ValueType and General Category
*
*/
/*
* logic
* create seriesCatalogTypeSeries
* if variable != null use it. if null, make an empty variable
* if variable != null
* and sampleMedium,dataType,valueType or generalCategory is null
* try to get value from variable
* add datTime interface logic
*
*/
seriesCatalogTypeSeries series = new seriesCatalogTypeSeries();
series.variable = variable != null ? variable : CreateVariableDescriptionType();
if (valueCount.HasValue)
{
series.valueCount = new seriesCatalogTypeSeriesValueCount();
series.valueCount.Value = valueCount.Value;
}
// if begin time and end time are the same use single
if (beginDateTime.HasValue)
{
TimeIntervalType tm = new TimeIntervalType();
// tm.beginDateTime = new DateTimeOffset(beginDateTime.Value.DateTime);
tm.beginDateTime = beginDateTime.Value.DateTime;
if (endDateTime.HasValue)
{
// tm.endDateTime = new DateTimeOffset(endDateTime.Value.DateTime);
tm.endDateTime = endDateTime.Value.DateTime;
}
else
{
tm.endDateTime = tm.beginDateTime;
}
series.variableTimeInterval = tm;
}
if (realTime)
{
TimePeriodRealTimeType rt = new TimePeriodRealTimeType();
rt.realTimeDataPeriod = realTimeInterval; // 31 day Period
//rt.beginDateTime = new DateTimeOffset(DateTime.Today.AddDays(-31));
//rt.endDateTime = new DateTimeOffset(DateTime.Today);
rt.beginDateTime = DateTime.Today.AddDays(-31);
rt.endDateTime = DateTime.Today;
series.variableTimeInterval = rt;
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (qualityControlLevelid.HasValue || !String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.QualityControlLevel = new QualityControlLevelType();
// need to have a dataset with an enum so we can
// SetEnumFromText(qsds,row,series.QualityControlLevel, typeof(QualityControlLevelEnum));
if (qualityControlLevelid.HasValue)
{
series.QualityControlLevel.qualityControlLevelID = qualityControlLevelid.Value;
series.QualityControlLevel.qualityControlLevelIDSpecified = true;
}
if (!String.IsNullOrEmpty(QualityControlLevelTerm))
{
series.QualityControlLevel.Value = QualityControlLevelTerm;
// originally defined as an enum, but enums cannot be empty, and we have an id
// QualityControlLevelTerm = QualityControlLevelTerm.Replace(" ", "");
// if (Enum.IsDefined(typeof(QualityControlLevelEnum), QualityControlLevelTerm))
// {
// object aEnum = Enum.Parse(typeof(QualityControlLevelEnum), QualityControlLevelTerm);
// series.QualityControlLevel.Value = (QualityControlLevelEnum)aEnum;
// }
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!String.IsNullOrEmpty(methodName) || methodID.HasValue)
{
MethodType method = new MethodType();
if ( !String.IsNullOrEmpty(methodName) )
{ method.MethodDescription = methodName;
}
if (methodID.HasValue) {
method.methodID = methodID.Value;
method.methodIDSpecified = true;
}
series.Method = method;
}
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization) ||
sourceID.HasValue)
{
SourceType source = new SourceType();
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization) )
{
source.SourceDescription = sourceDescription;
source.Organization = organization;
}
if (sourceID.HasValue)
{
source.sourceID = sourceID.Value;
source.sourceIDSpecified = true;
}
series.Source = source;
}
return series;
}
public static seriesCatalogTypeSeries CreateSeriesRecord(VariableInfoType variable, string sampleMedium, W3CDateTime?beginDateTime, W3CDateTime?endDateTime, int?valueCount, bool?valueCountIsEstimated, string dataType, string valueType, string generalCategory, bool realTime, string realTimeInterval, string qualityControlLevelCode, int?qualityControlLevelid, string methodName, int?methodID, string organization, string sourceDescription, int?sourceID, string citation, bool addUtCDateTime, string qualityControlLevelTerm)
{
/* don't forget to check the VariableInfoType for the
* dataType,ValueType and General Category
*
*/
/*
* logic
* create seriesCatalogTypeSeries
* if variable != null use it. if null, make an empty variable
* if variable != null
* and sampleMedium,dataType,valueType or generalCategory is null
* try to get value from variable
* add datTime interface logic
*
*/
seriesCatalogTypeSeries series = new seriesCatalogTypeSeries();
series.variable = variable != null ? variable : CreateVariableDescriptionType();
if (valueCount.HasValue)
{
series.valueCount = new seriesCatalogTypeSeriesValueCount();
series.valueCount.Value = valueCount.Value;
}
// if begin time and end time are the same use single
if (beginDateTime.HasValue)
{
TimeIntervalType tm = new TimeIntervalType();
//tm.beginDateTime = new DateTimeOffset(beginDateTime.Value.DateTime);
tm.beginDateTime = beginDateTime.Value.DateTime;
if (addUtCDateTime)
{
tm.beginDateTimeUTC = beginDateTime.Value.UtcTime;
tm.beginDateTimeUTCSpecified = true;
}
if (endDateTime.HasValue)
{
//tm.endDateTime = new DateTimeOffset(endDateTime.Value.DateTime);
tm.endDateTime = endDateTime.Value.DateTime;
if (addUtCDateTime)
{
tm.endDateTimeUTC = endDateTime.Value.UtcTime;
tm.endDateTimeUTCSpecified = true;
}
}
else
{
tm.endDateTime = tm.beginDateTime;
if (addUtCDateTime)
{
tm.endDateTimeUTC = tm.beginDateTimeUTC;
tm.endDateTimeUTCSpecified = true;
}
}
series.variableTimeInterval = tm;
}
if (realTime)
{
TimePeriodRealTimeType rt = new TimePeriodRealTimeType();
rt.realTimeDataPeriod = realTimeInterval; // 31 day Period
//rt.beginDateTime = new DateTimeOffset(DateTime.Today.AddDays(-31));
//rt.endDateTime = new DateTimeOffset(DateTime.Today);
rt.beginDateTime = DateTime.Today.AddDays(-31);
rt.endDateTime = DateTime.Today;
series.variableTimeInterval = rt;
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (qualityControlLevelid.HasValue ||
!String.IsNullOrEmpty(qualityControlLevelCode))
{
series.qualityControlLevel = new QualityControlLevelType();
// need to have a dataset with an enum so we can
// SetEnumFromText(qsds,row,series.QualityControlLevel, typeof(QualityControlLevelEnum));
if (qualityControlLevelid.HasValue)
{
series.qualityControlLevel.qualityControlLevelID = qualityControlLevelid.Value;
series.qualityControlLevel.qualityControlLevelIDSpecified = true;
}
if (!String.IsNullOrEmpty(qualityControlLevelCode))
{
if (!String.IsNullOrEmpty(qualityControlLevelCode))
{
series.qualityControlLevel.qualityControlLevelCode = qualityControlLevelCode;
}
}
if (!String.IsNullOrEmpty(qualityControlLevelTerm))
{
series.qualityControlLevel.definition = qualityControlLevelTerm;
}
}
/* TODO: fully populate
* These need to use ID< and fully populat from database
*/
if (!String.IsNullOrEmpty(methodName) || methodID.HasValue)
{
MethodType method = new MethodType();
if (!String.IsNullOrEmpty(methodName))
{
method.methodDescription = methodName;
}
if (methodID.HasValue)
{
method.methodID = methodID.Value;
method.methodIDSpecified = true;
}
series.method = method;
}
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization) ||
!String.IsNullOrEmpty(citation) ||
sourceID.HasValue)
{
SourceType source = new SourceType();
if (!String.IsNullOrEmpty(sourceDescription) ||
!String.IsNullOrEmpty(organization))
{
source.sourceDescription = sourceDescription;
source.organization = organization;
source.citation = citation;
}
if (sourceID.HasValue)
{
source.sourceID = sourceID.Value;
source.sourceIDSpecified = true;
}
series.source = source;
}
return(series);
}
// 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);
}
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);
}
