CreateValuesElement(DataRow[] rows)
{
/* logic, look into datatable to get
* - variableUnitsAbbreviation
* if all the same, create values
* - values
* Then get qualifiers
*
* Future
* if no MethodID and SourceID exists, but MethodDescript, and SourceDescrition exists
* - create MethodID, and SourceID, and
*/
TsValuesSingleVariableType valuesElement = new TsValuesSingleVariableType();
List <ValueSingleVariable> valuesList = new List <ValueSingleVariable>();
foreach (DataRow row in rows)
{
ValueSingleVariable value = row2Value(row);
if (value != null)
{
valuesList.Add(value);
}
}
valuesElement.value = valuesList.ToArray();
valuesElement.count = valuesList.Count.ToString();
return(valuesElement);
}
row2Value(DataRow aRow)
{
DataValuesTables.DataValuesDataTable dvT = new DataValuesTables.DataValuesDataTable();
dvT.ImportRow(aRow);
dvT.AcceptChanges();
DataValuesTables.DataValuesRow row = dvT[0];
ValueSingleVariable valueElement = new ValueSingleVariable();
try
{
if (!row.IsLocalDateTimeNull())
{
valueElement.dateTime = row.LocalDateTime;
}
else
{
log.Error("Value with No DateTime provided to CreateValuesElement");
return(null);
}
if (!row.IsQualfiersNull())
{
valueElement.qualifiers = row.Qualfiers;
}
if (!row.IsQualityControlLevelTermNull())
{
}
if (!row.IsMethodIDNull())
{
valueElement.methodID = row.MethodID;
}
if (!row.IsSourceIDNull())
{
valueElement.sourceID = row.SourceID;
}
if (!row.IsDataValueNull())
{
valueElement.Value = Convert.ToDecimal(row.DataValue);;
}
else
{
log.Error("No DataValue");
valueElement.Value = 0;
valueElement.censorCode = CensorCodeEnum.lt;
valueElement.censorCodeSpecified = true;
}
}
catch
{
log.Error("Could not convert value");
return(null);
}
return(valueElement);
}
private string CensorCodeToString(ValueSingleVariable dataValue)
{
if (dataValue.censorCodeSpecified)
{
return(dataValue.censorCode.ToString());
}
else
{
return(CensorCodeEnum.nc.ToString());
}
}
public void accuracyStdDevTest()
{
ValueSingleVariable target = new ValueSingleVariable();
double val = 0; // TODO: Assign to an appropriate value for the property
target.accuracyStdDev = val;
Assert.AreEqual(val, target.accuracyStdDev, "WaterOneFlow.Schema.v1.ValueSingleVariable.accuracyStdDev was not set correctly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void sourceIDTest()
{
ValueSingleVariable target = new ValueSingleVariable();
int val = 0; // TODO: Assign to an appropriate value for the property
target.sourceID = val;
Assert.AreEqual(val, target.sourceID, "WaterOneFlow.Schema.v1.ValueSingleVariable.sourceID was not set correctly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void qualifiersTest()
{
ValueSingleVariable target = new ValueSingleVariable();
string val = null; // TODO: Assign to an appropriate value for the property
target.qualifiers = val;
Assert.AreEqual(val, target.qualifiers, "WaterOneFlow.Schema.v1.ValueSingleVariable.qualifiers was not set correctly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void dateTimeTest()
{
ValueSingleVariable target = new ValueSingleVariable();
DateTime val = new DateTime(); // TODO: Assign to an appropriate value for the property
target.dateTime = val;
Assert.AreEqual(val, target.dateTime, "WaterOneFlow.Schema.v1.ValueSingleVariable.dateTime was not set correctly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void censorCodeTest()
{
ValueSingleVariable target = new ValueSingleVariable();
CensorCodeEnum val = CensorCodeEnum.lt; // TODO: Assign to an appropriate value for the property
target.censorCode = val;
Assert.AreEqual(val, target.censorCode, "WaterOneFlow.Schema.v1.ValueSingleVariable.censorCode was not set correctly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void sampleIDSpecifiedTest()
{
ValueSingleVariable target = new ValueSingleVariable();
bool val = false; // TODO: Assign to an appropriate value for the property
target.sampleIDSpecified = val;
Assert.AreEqual(val, target.sampleIDSpecified, "WaterOneFlow.Schema.v1.ValueSingleVariable.sampleIDSpecified was not set correctl" +
"y.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void offsetUnitsAbbreviationTest()
{
ValueSingleVariable target = new ValueSingleVariable();
string val = null; // TODO: Assign to an appropriate value for the property
target.offsetUnitsAbbreviation = val;
Assert.AreEqual(val, target.offsetUnitsAbbreviation, "WaterOneFlow.Schema.v1.ValueSingleVariable.offsetUnitsAbbreviation was not set co" +
"rrectly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void codedVocabularyTermTest()
{
ValueSingleVariable target = new ValueSingleVariable();
string val = null; // TODO: Assign to an appropriate value for the property
target.codedVocabularyTerm = val;
Assert.AreEqual(val, target.codedVocabularyTerm, "WaterOneFlow.Schema.v1.ValueSingleVariable.codedVocabularyTerm was not set correc" +
"tly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
public void qualityControlLevelTest()
{
ValueSingleVariable target = new ValueSingleVariable();
QualityControlLevelEnum val = QualityControlLevelEnum.Rawdata; // TODO: Assign to an appropriate value for the property
target.qualityControlLevel = val;
Assert.AreEqual(val, target.qualityControlLevel, "WaterOneFlow.Schema.v1.ValueSingleVariable.qualityControlLevel was not set correc" +
"tly.");
Assert.Inconclusive("Verify the correctness of this test method.");
}
static void Main(string[] args)
{
XmlSchemaSet xmlSchemaSet = new XmlSchemaSet();
xmlSchemaSet.XmlResolver = new XmlUrlResolver();
//XmlSchema xmlSchema = WaterOneFlow.Schema.GetSchema.SchemaV1_0(); ;
String xsd = WaterOneFlow.Schema.GetSchema.SchemaXmlV1_0();
XmlSchema xmlSchema = XmlSchema.Read(new StringReader(xsd), new ValidationEventHandler(ValidationCallBack));
xmlSchemaSet.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
xmlSchemaSet.Add(xmlSchema);
XmlSerializer serializer =
new XmlSerializer(typeof(ValueSingleVariable));
XmlRootAttribute root = new XmlRootAttribute("value");
XmlSerializer serializerRoot =
new XmlSerializer(typeof(ValueSingleVariable), root);
ValueSingleVariable value = new ValueSingleVariable();
value.dateTime = DateTime.Now;
value.Value = 1;
value.qualifiers = "AD:CD ab:ab";
System.Console.WriteLine("NoRoot\n-----------------");
serializer.Serialize(System.Console.Out, value);
System.Console.WriteLine("\nWithRoot\n-----------------");
serializerRoot.Serialize(System.Console.Out, value);
serializer =
new XmlSerializer(typeof(TsValuesSingleVariableType));
TsValuesSingleVariableType ts = new TsValuesSingleVariableType();
ts.value = new ValueSingleVariable[1];
ts.value[0] = value;
ts.method = new MethodType[1];
ts.method[0] = new MethodType();
ts.method[0].MethodDescription = "method";
ts.count = ts.value.Length.ToString();
System.Console.WriteLine("\nTimeSeries\n-----------------");
serializer.Serialize(System.Console.Out, ts);
System.Console.WriteLine("\nHit AnyKey to continue");
System.Console.ReadKey();
}
public static ValueSingleVariable CreateValue(
double DataValue,
WaterOneFlowImpl.W3CDateTime localDateTime,
string CensorCode,
string Qualifiers,
string MethodID,
string SourceID,
string QualityControlLevel
)
{
ValueSingleVariable valueElement = new ValueSingleVariable();
// make as datarow
return(valueElement);
}
public static void TimeSeriesFromRDB(DataTable aTable, int time, int aValue, int qualifier, List <ValueSingleVariable> tsTypeList, bool provisional, int sourceId, int methodId, string qualityControlLevel)
{
foreach (DataRow aRow in aTable.Rows)
{
try
{
ValueSingleVariable tsTypeValue = new ValueSingleVariable();
tsTypeValue.dateTime = Convert.ToDateTime(aRow[time]);
//tsTypeValue.dateTimeSpecified = true;
//tsTypeValue.censored = string.Empty;
if (string.IsNullOrEmpty(aRow[aValue].ToString()))
{
continue;
}
else
{
tsTypeValue.Value = Convert.ToDecimal(aRow[aValue]);
}
parseQualifiersForCensorCode(tsTypeValue, aRow[qualifier].ToString()); // this will add censored, if appropariate
if (provisional)
{
if (tsTypeValue.qualifiers != null)
{
if (string.IsNullOrEmpty(tsTypeValue.qualifiers))
{
tsTypeValue.qualifiers = "P";
}
else
{
tsTypeValue.qualifiers += "P";
}
}
else
{
tsTypeValue.qualifiers = "P";
}
}
tsTypeList.Add(tsTypeValue);
}
catch (Exception e)
{
// just ignore any value errors
}
}
}
/// <summary>
/// This will add a qualifer, and if appropriate censorCode value to the tsTypeValue type, if appropriate.
///
/// </summary>
/// <param name="tsTypeValue">tsValuesTypeValue to populate</param>
/// <param name="qualifier">qualifier field to parse, and look for tags that indicate that an item should be censored.</param>
public static void parseQualifiersForCensorCode(ValueSingleVariable tsTypeValue, String qualifier)
{
if (!string.IsNullOrEmpty(qualifier))
{
if (qualifier.Contains("<"))
{
tsTypeValue.censorCode = CensorCodeEnum.lt;
tsTypeValue.censorCodeSpecified = true;
qualifier = qualifier.Replace("<", "");
}
if (qualifier.Contains(">"))
{
tsTypeValue.censorCode = CensorCodeEnum.gt;
tsTypeValue.censorCodeSpecified = true;
qualifier = qualifier.Replace(">", "");
}
if (!string.IsNullOrEmpty(qualifier))
{
tsTypeValue.qualifiers = qualifier;
}
}
}
static void InsertOneDataValue(OD_1_1_1DataSet.DataValuesDataTable tblDataValues,
int siteID, int varID, ValueSingleVariable dvInfo)
{
OD_1_1_1DataSet.DataValuesRow row = tblDataValues.NewDataValuesRow();
row.DataValue = (double)dvInfo.Value;
row.ValueAccuracy = dvInfo.accuracyStdDev;
string[] vars = Regex.Split(dvInfo.timeOffset, ":");
double v = 0, f = 1;
for (int i = 0; i < vars.Count(); i++)
{
v += int.Parse(vars[i]) / f;
f *= 60.0;
}
row.UTCOffset = v;
row.LocalDateTime = dvInfo.dateTime;
row.DateTimeUTC = dvInfo.dateTimeUTC;
row.SiteID = siteID;
row.VariableID = varID;
row.OffsetValue = dvInfo.offsetValue;
row.SetOffsetTypeIDNull();
row.CensorCode = dvInfo.censorCode;
row.SetQualifierIDNull();
row.MethodID = Convert.ToInt32(dvInfo.methodCode);
row.SourceID = Convert.ToInt32(dvInfo.sourceCode);
row.SetSampleIDNull();
row.SetDerivedFromIDNull();
row.QualityControlLevelID = Convert.ToInt32(dvInfo.qualityControlLevelCode);
tblDataValues.AddDataValuesRow(row);
}
public static IEnumerable <ValueSingleVariable> dataset2ValuesList(ValuesDataSet ds, VariableParam variable, int?VariableId, VariablesDataset variablesDs)
{
Boolean variableIsCategorical = false;
VariableInfoType variableInfoType = ODvariables.GetVariableByID(VariableId, variablesDs);
if (variableInfoType != null && variableInfoType.dataType.Equals("Categorical"))
{
variableIsCategorical = true;
}
/* logic
* if there is a variable that has options, then get a set of datarows
* using a select clause
* use an enumerator, since it is generic
* */
IEnumerator dataValuesEnumerator; // = ds.DataValues.GetEnumerator();
ValuesDataSet.DataValuesRow[] dvRows; // if we find options, we need to use this.
String select = OdValuesCommon.CreateValuesWhereClause(variable, VariableId);
if (select.Length > 0)
{
dvRows = (ValuesDataSet.DataValuesRow[])ds.DataValues.Select(select.ToString());
dataValuesEnumerator = dvRows.GetEnumerator();
}
else
{
dataValuesEnumerator = ds.DataValues.GetEnumerator();
}
while (dataValuesEnumerator.MoveNext())
{
ValuesDataSet.DataValuesRow aRow = (ValuesDataSet.DataValuesRow)dataValuesEnumerator.Current;
ValueSingleVariable tsTypeValue = new ValueSingleVariable();
Boolean goodValue = false;
try
{
tsTypeValue.dateTime = Convert.ToDateTime(aRow.DateTime);
DateTime temprealdate;
TimeSpan zone = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
Double offset = Convert.ToDouble(aRow.UTCOffset);
if (zone.TotalHours.Equals(offset))
{
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime),
DateTimeKind.Local);
}
else
{
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime), DateTimeKind.Utc);
temprealdate = temprealdate.AddHours(offset);
}
temprealdate = Convert.ToDateTime(aRow.DateTime);
tsTypeValue.dateTime = temprealdate;
tsTypeValue.dateTimeUTC = aRow.DateTimeUTC;
tsTypeValue.dateTimeUTCSpecified = true;
tsTypeValue.timeOffset = OffsetDoubleToHoursMinutesString(aRow.UTCOffset);
//tsTypeValue.dateTime = new DateTimeOffset(temprealdate);
//tsTypeValue.dateTimeSpecified = true;
if (string.IsNullOrEmpty(aRow.Value.ToString()))
{
continue;
}
else
{
tsTypeValue.Value = Convert.ToDecimal(aRow.Value);
}
try
{
tsTypeValue.censorCode =
aRow.CensorCode;
if (!aRow.IsOffsetTypeIDNull())
{
//tsTypeValue.offsetTypeID = aRow.OffsetTypeID.ToString();
tsTypeValue.offsetTypeCode = aRow.OffsetTypeID.ToString();
// HIS-97 moved to OffsetUnitsType
// ValuesDataSet.OffsetTypesRow off =
// ds.OffsetTypes.FindByOffsetTypeID(aRow.OffsetTypeID);
// ValuesDataSet.UnitsRow offUnit = ds.Units.FindByUnitsID(off.OffsetUnitsID);
// tsTypeValue.offsetUnitsCode = offUnit.UnitsID.ToString();
// tsTypeValue.offsetUnitsAbbreviation = offUnit.UnitsAbbreviation;
}
// offset value may be separate from the units... anticpating changes for USGS
if (!aRow.IsOffsetValueNull())
{
tsTypeValue.offsetValue = aRow.OffsetValue;
tsTypeValue.offsetValueSpecified = true;
}
ValuesDataSet.MethodsRow meth = ds.Methods.FindByMethodID(aRow.MethodID);
// tsTypeValue.methodID = aRow.MethodID.ToString();
tsTypeValue.methodCode = aRow.MethodID.ToString();
// qualifiers
if (!aRow.IsQualifierIDNull())
{
ValuesDataSet.QualifiersRow qual = ds.Qualifiers.FindByQualifierID(aRow.QualifierID);
if (qual != null)
{
tsTypeValue.qualifiers = qual.QualifierCode;
}
}
// quality control level
ValuesDataSet.QualityControlLevelsRow qcl =
ds.QualityControlLevels.FindByQualityControlLevelID(aRow.QualityControlLevelID);
string qlName = qcl.Definition.Replace(" ", "");
tsTypeValue.qualityControlLevelCode = qcl.QualityControlLevelCode;
//if (!String.IsNullOrEmpty(qlName))
//{
// tsTypeValue.qualityControlLevel = qlName;
//}
// tsTypeValue.sourceID = aRow.SourceID.ToString();
tsTypeValue.sourceCode = aRow.SourceID.ToString();
if (!aRow.IsSampleIDNull())
{
//tsTypeValue.sampleID = aRow.SampleID.ToString();
ValuesDataSet.SamplesRow lsc = ds.Samples.FindBySampleID(aRow.SampleID);
tsTypeValue.labSampleCode = lsc.LabSampleCode;
}
// categorical
if (variableIsCategorical && VariableId.HasValue)
{
tsTypeValue.codedVocabularyTerm = ODvariables.GetCategoryTerm(VariableId.Value, tsTypeValue.Value, variablesDs);
if (!String.IsNullOrEmpty(tsTypeValue.codedVocabularyTerm))
{
tsTypeValue.codedVocabulary = true;
}
tsTypeValue.codedVocabularySpecified = true;
}
}
catch (Exception e)
{
log.Debug("Error generating a value " + e.Message);
// non fatal exceptions
}
goodValue = true;
}
catch (Exception e)
{
goodValue = false;
// If there is an error, we do not add it.
}
if (goodValue)
{
yield return(tsTypeValue);
}
}
}
/// <summary>
/// DataValue creation. Converts a ValuesDataSet to the XML schema ValueSingleVariable
/// If variable is null, it will return all
/// If variable has extra options (variable:code/Value=Key/Value=Key)
///
/// </summary>
/// <param name="ds">Dataset that you want converted</param>
/// <param name="variable">Variable that you want to use to place limits on the returned data</param>
/// <returns></returns>
public static List <ValueSingleVariable> dataset2ValuesList(ValuesDataSet ds, VariableParam variable)
{
List <ValueSingleVariable> tsTypeList = new List <ValueSingleVariable>();
/* logic
* if there is a variable that has options, then get a set of datarows
* using a select clause
* use an enumerator, since it is generic
* */
IEnumerator dataValuesEnumerator;
ValuesDataSet.DataValuesRow[] dvRows; // if we find options, we need to use this.
String select = OdValuesCommon.CreateValuesWhereClause(variable, null);
if (select.Length > 0)
{
dvRows = (ValuesDataSet.DataValuesRow[])ds.DataValues.Select(select.ToString());
dataValuesEnumerator = dvRows.GetEnumerator();
}
else
{
dataValuesEnumerator = ds.DataValues.GetEnumerator();
}
// foreach (ValuesDataSet.DataValuesRow aRow in ds.DataValues){
while (dataValuesEnumerator.MoveNext())
{
ValuesDataSet.DataValuesRow aRow = (ValuesDataSet.DataValuesRow)dataValuesEnumerator.Current;
try
{
ValueSingleVariable tsTypeValue = new ValueSingleVariable();
#region DateTime Standard
tsTypeValue.dateTime = Convert.ToDateTime(aRow.DateTime);
//tsTypeValue.dateTimeSpecified = true;
DateTime temprealdate;
//<add key="returnUndefinedUTCorLocal" value="Undefined"/>
if (ConfigurationManager.AppSettings["returnUndefinedUTCorLocal"].Equals("Undefined"))
{
temprealdate = Convert.ToDateTime(aRow.DateTime); // not time zone shift
}
else if (ConfigurationManager.AppSettings["returnUndefinedUTCorLocal"].Equals("Local"))
{
TimeSpan zone = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
Double offset = Convert.ToDouble(aRow.UTCOffset);
if (zone.TotalHours.Equals(offset))
{
// zone is the same as server. Shift
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime), DateTimeKind.Local);
}
else
{
//// zone is not the same. Output in UTC.
//temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime), DateTimeKind.Utc);
//// correct time with shift.
//temprealdate = temprealdate.AddHours(offset);
// just use the DateTime UTC
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTimeUTC), DateTimeKind.Utc);
}
}
else if (ConfigurationManager.AppSettings["returnUndefinedUTCorLocal"].Equals("UTC"))
{
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTimeUTC), DateTimeKind.Utc);
}
else
{
temprealdate = Convert.ToDateTime(aRow.DateTime); // not time zone shift
}
temprealdate = Convert.ToDateTime(aRow.DateTime); // not time zone shift
#endregion
#region DateTimeOffset Failed
/// using XML overrides
// Attemp to use DateTimeOffset in xml Schema
//TimeSpan zone = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
//Double offset = Convert.ToDouble(aRow.UTCOffset);
//DateTimeOffset temprealdate;
//temprealdate = new DateTimeOffset(Convert.ToDateTime(aRow.DateTime),
// new TimeSpan(Convert.ToInt32(offset),0,0));
//DateTimeOffset temprealdate;
//temprealdate = new DateTimeOffset(Convert.ToDateTime(aRow.DateTime),
// new TimeSpan(Convert.ToInt32(offset), 0, 0));
//tsTypeValue.dateTime = temprealdate;
//tsTypeValue.dateTimeSpecified = true;
#endregion
//tsTypeValue.censored = string.Empty;
if (string.IsNullOrEmpty(aRow.Value.ToString()))
{
continue;
}
else
{
tsTypeValue.Value = Convert.ToDecimal(aRow.Value);
}
try
{
tsTypeValue.censorCode = (CensorCodeEnum)Enum.Parse(typeof(CensorCodeEnum), aRow.CensorCode, true);
tsTypeValue.censorCodeSpecified = true;
if (!aRow.IsOffsetTypeIDNull())
{
tsTypeValue.offsetTypeID = aRow.OffsetTypeID;
tsTypeValue.offsetTypeIDSpecified = true;
// enabled to fix issue with hydroobjects
ValuesDataSet.OffsetTypesRow off = ds.OffsetTypes.FindByOffsetTypeID(aRow.OffsetTypeID);
ValuesDataSet.UnitsRow offUnit = ds.Units.FindByUnitsID(off.OffsetUnitsID);
tsTypeValue.offsetUnitsCode = offUnit.UnitsID.ToString();
tsTypeValue.offsetUnitsAbbreviation = offUnit.UnitsAbbreviation;
}
// offset value may be separate from the units... anticpating changes for USGS
if (!aRow.IsOffsetValueNull())
{
tsTypeValue.offsetValue = aRow.OffsetValue;
tsTypeValue.offsetValueSpecified = true;
}
ValuesDataSet.MethodsRow meth = ds.Methods.FindByMethodID(aRow.MethodID);
tsTypeValue.methodID = aRow.MethodID;
tsTypeValue.methodIDSpecified = true;
if (!aRow.IsQualifierIDNull())
{
ValuesDataSet.QualifiersRow qual = ds.Qualifiers.FindByQualifierID(aRow.QualifierID);
if (qual != null)
{
tsTypeValue.qualifiers = qual.QualifierCode;
}
}
ValuesDataSet.QualityControlLevelsRow qcl =
ds.QualityControlLevels.FindByQualityControlLevelID(aRow.QualityControlLevelID);
string qlName = qcl.Definition.Replace(" ", "");
if (Enum.IsDefined(typeof(QualityControlLevelEnum), qlName))
{
tsTypeValue.qualityControlLevel = (QualityControlLevelEnum)
Enum.Parse(
typeof(QualityControlLevelEnum), qlName, true);
if (tsTypeValue.qualityControlLevel != QualityControlLevelEnum.Unknown)
{
tsTypeValue.qualityControlLevelSpecified = true;
}
}
//}
tsTypeValue.sourceID = aRow.SourceID;
tsTypeValue.sourceIDSpecified = true;
if (!aRow.IsSampleIDNull())
{
tsTypeValue.sampleID = aRow.SampleID;
tsTypeValue.sampleIDSpecified = true;
}
}
catch (Exception e)
{
log.Debug("Error generating a value " + e.Message);
// non fatal exceptions
}
tsTypeList.Add(tsTypeValue);
}
catch (Exception e)
{
// ignore any value errors
}
}
return(tsTypeList);
}
private TsValuesSingleVariableType CreateWQTimeSeriesObject(VariableParam vp, string aURL)
{
// download the iformation
String resultFile = USGSCommon.GetHTTPFile(aURL, 10);
TsValuesSingleVariableType values = new TsValuesSingleVariableType();
//result.TimeSeries.Values.valueUnits = units; // this needs to be done earlier
DataTable aTable = NWISDelimitedTextParser.ParseFileIntoDT(resultFile);
// dwv add code to get the code, and use that to find the correct columns
// time and date are separate
int date = 2; // present location of time column
int time = 3;
String code = vp.Code;
int codeCol;
int aValue;
int qualifier; // there are many qa columns. what do we want to do about them
/* 20070909
* add
# meth_cd - Method code
# dqi_cd - Data-quality indicator code
# rpt_lev_va - Reporting level
# rpt_lev_cd - Reporting level type
# lab_std_va - Lab standard deviation
# anl_ent_cd - Analyzing entity code
*/
int method;
try
{
/* 20070909 - USGS column name changes
* parameter_cd to param_cd
* APPSETTING: ConfigurationManager.AppSettings["parameterColumn"];
* qualifiers now in val_qual_tx
* also:
# meth_cd - Method code
# dqi_cd - Data-quality indicator code
# rpt_lev_va - Reporting level
# rpt_lev_cd - Reporting level type
# lab_std_va - Lab standard deviation
# anl_ent_cd - Analyzing entity code
*/
//codeCol = getVarQualifiersColumn(aTable, "parameter_cd", null);
string parmameterColName = (string)Properties.Settings.Default["parameterColumn"];
codeCol = USGSCommon.getVarQualifiersColumn(aTable, parmameterColName, null);
aValue = USGSCommon.getVarColumn(aTable, "result_va", null);
qualifier = USGSCommon.getVarQualifiersColumn(aTable, "remark_cd", null); // there are many qa columns. what do we want to do about them
// qualifier = getVarColumn(aTable, "val_qual_tx", null); //Do Not use at present
}
catch (WaterOneFlowException we)
{
throw new WaterOneFlowException("URL: '" + aURL, we);
}
List <ValueSingleVariable> tsTypeList = new List <ValueSingleVariable>();
foreach (DataRow aRow in aTable.Rows)
{
if (aRow[codeCol].Equals(code))
{ // only do this if this is the correct value
ValueSingleVariable tsTypeValue = new ValueSingleVariable();
//tsTypeValue.time= Convert.ToDateTime(aRow[time]);
tsTypeValue.dateTime = Convert.ToDateTime(
aRow[date].ToString() +
" " + aRow[time].ToString()
);
// tsTypeValue.dateTimeSpecified = true;
//tsTypeValue.censored = string.Empty;
if (string.IsNullOrEmpty(aRow[aValue].ToString()))
{
continue;
}
else
{
tsTypeValue.Value = Convert.ToDecimal(aRow[aValue]);
}
USGSCommon.parseQualifiersForCensorCode(tsTypeValue, aRow[qualifier].ToString()); // this will add censored, if appropariate
// added for hydrodesktop
tsTypeValue.qualityControlLevel = QualityControlLevelEnum.Unknown;
tsTypeValue.qualityControlLevelSpecified = true;
tsTypeValue.methodID = 0;
tsTypeValue.methodIDSpecified = true;
tsTypeValue.sourceID = 1;
tsTypeValue.sourceIDSpecified = true;
tsTypeList.Add(tsTypeValue);
}
}
values.count = tsTypeList.Count.ToString();
values.value = tsTypeList.ToArray();
// add added qualifiers
values.source = new SourceType[1];
values.source[0] = new SourceType();
values.source[0].sourceID = 1;
values.source[0].sourceIDSpecified = true;
values.source[0].Organization = "USGS";
values.source[0].SourceDescription = "US Geological Survey National Water Information System";
values.source[0].SourceLink = "http://waterdata.usgs.gov/";
values.qualityControlLevel = new qualityControlLevel[1];
values.qualityControlLevel[0] = new qualityControlLevel();
values.qualityControlLevel[0].qualityControlLevelID = "0";
values.qualityControlLevel[0].qualityControlLevelCode = "Unknown";
values.method = new MethodType[1];
values.method[0] = new MethodType();
values.method[0].methodID = 0;
values.method[0].methodIDSpecified = true;
values.method[0].MethodDescription = "Unknown";
return(values);
}
public static IEnumerable<ValueSingleVariable> dataset2ValuesList(ValuesDataSet ds, VariableParam variable, int? VariableId, VariablesDataset variablesDs)
{
Boolean variableIsCategorical = false;
VariableInfoType variableInfoType = ODvariables.GetVariableByID(VariableId, variablesDs);
if (variableInfoType != null && variableInfoType.dataType.Equals("Categorical"))
{
variableIsCategorical = true;
}
/* logic
* if there is a variable that has options, then get a set of datarows
* using a select clause
* use an enumerator, since it is generic
* */
IEnumerator dataValuesEnumerator; // = ds.DataValues.GetEnumerator();
ValuesDataSet.DataValuesRow[] dvRows; // if we find options, we need to use this.
String select = OdValuesCommon.CreateValuesWhereClause(variable, VariableId);
if (select.Length > 0)
{
dvRows = (ValuesDataSet.DataValuesRow[]) ds.DataValues.Select(select.ToString());
dataValuesEnumerator = dvRows.GetEnumerator();
}
else
{
dataValuesEnumerator = ds.DataValues.GetEnumerator();
}
while (dataValuesEnumerator.MoveNext())
{
ValuesDataSet.DataValuesRow aRow = (ValuesDataSet.DataValuesRow) dataValuesEnumerator.Current;
ValueSingleVariable tsTypeValue = new ValueSingleVariable();
Boolean goodValue = false;
try
{
tsTypeValue.dateTime = Convert.ToDateTime(aRow.DateTime);
DateTime temprealdate;
TimeSpan zone = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
Double offset = Convert.ToDouble(aRow.UTCOffset);
if (zone.TotalHours.Equals(offset))
{
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime),
DateTimeKind.Local);
}
else
{
temprealdate = DateTime.SpecifyKind(Convert.ToDateTime(aRow.DateTime), DateTimeKind.Utc);
temprealdate = temprealdate.AddHours(offset);
}
temprealdate = Convert.ToDateTime(aRow.DateTime);
tsTypeValue.dateTime = temprealdate;
tsTypeValue.dateTimeUTC = aRow.DateTimeUTC;
tsTypeValue.dateTimeUTCSpecified = true;
tsTypeValue.timeOffset = OffsetDoubleToHoursMinutesString(aRow.UTCOffset);
//tsTypeValue.dateTime = new DateTimeOffset(temprealdate);
//tsTypeValue.dateTimeSpecified = true;
if (string.IsNullOrEmpty(aRow.Value.ToString()))
continue;
else
tsTypeValue.Value = Convert.ToDecimal(aRow.Value);
try
{
tsTypeValue.censorCode =
aRow.CensorCode;
if (!aRow.IsOffsetTypeIDNull())
{
//tsTypeValue.offsetTypeID = aRow.OffsetTypeID.ToString();
tsTypeValue.offsetTypeCode = aRow.OffsetTypeID.ToString();
// HIS-97 moved to OffsetUnitsType
// ValuesDataSet.OffsetTypesRow off =
// ds.OffsetTypes.FindByOffsetTypeID(aRow.OffsetTypeID);
// ValuesDataSet.UnitsRow offUnit = ds.Units.FindByUnitsID(off.OffsetUnitsID);
// tsTypeValue.offsetUnitsCode = offUnit.UnitsID.ToString();
// tsTypeValue.offsetUnitsAbbreviation = offUnit.UnitsAbbreviation;
}
// offset value may be separate from the units... anticpating changes for USGS
if (!aRow.IsOffsetValueNull())
{
tsTypeValue.offsetValue = aRow.OffsetValue;
tsTypeValue.offsetValueSpecified = true;
}
ValuesDataSet.MethodsRow meth = ds.Methods.FindByMethodID(aRow.MethodID);
// tsTypeValue.methodID = aRow.MethodID.ToString();
tsTypeValue.methodCode = aRow.MethodID.ToString();
// qualifiers
if (!aRow.IsQualifierIDNull())
{
ValuesDataSet.QualifiersRow qual = ds.Qualifiers.FindByQualifierID(aRow.QualifierID);
if (qual != null)
{
tsTypeValue.qualifiers = qual.QualifierCode;
}
}
// quality control level
ValuesDataSet.QualityControlLevelsRow qcl =
ds.QualityControlLevels.FindByQualityControlLevelID(aRow.QualityControlLevelID);
string qlName = qcl.Definition.Replace(" ", "");
tsTypeValue.qualityControlLevelCode = qcl.QualityControlLevelCode;
//if (!String.IsNullOrEmpty(qlName))
//{
// tsTypeValue.qualityControlLevel = qlName;
//}
// tsTypeValue.sourceID = aRow.SourceID.ToString();
tsTypeValue.sourceCode = aRow.SourceID.ToString();
if (!aRow.IsSampleIDNull())
{
//tsTypeValue.sampleID = aRow.SampleID.ToString();
ValuesDataSet.SamplesRow lsc = ds.Samples.FindBySampleID(aRow.SampleID);
tsTypeValue.labSampleCode = lsc.LabSampleCode;
}
// categorical
if (variableIsCategorical && VariableId.HasValue)
{
tsTypeValue.codedVocabularyTerm = ODvariables.GetCategoryTerm(VariableId.Value, tsTypeValue.Value, variablesDs);
if (!String.IsNullOrEmpty(tsTypeValue.codedVocabularyTerm) )
{
tsTypeValue.codedVocabulary = true;
}
tsTypeValue.codedVocabularySpecified = true;
}
}
catch (Exception e)
{
log.Debug("Error generating a value " + e.Message);
// non fatal exceptions
}
goodValue = true;
}
catch (Exception e)
{
goodValue = false;
// If there is an error, we do not add it.
}
if (goodValue)
{
yield return tsTypeValue;
}
}
}
private static ValueSingleVariable CreateNoDataValue(DateTime time, TsValuesSingleVariableType s, int variableId)
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(time);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
switch (variableId)
{
case 1:
//for precipitation, set 'no data' to zero
v.Value = 0.0M;
break;
case 4:
v.Value = -9999.0M;
break;
case 5:
v.Value = -9999.0M;
break;
case 8:
v.Value = 0.0M;
break;
case 16:
v.Value = -9999.0M;
break;
default:
v.Value = -9999.0M;
break;
}
return v;
}
/// <summary>
/// Get the values, from the Db
/// </summary>
/// <param name="siteId">site id (local database id)</param>
/// <param name="variableId">variable id (local database id)</param>
/// <param name="startDateTime"></param>
/// <param name="endDateTime"></param>
/// <returns></returns>
internal static TsValuesSingleVariableType GetValuesFromDb(string siteId, string variableCode, DateTime startDateTime, DateTime endDateTime)
{
//numeric variable id
int varId = VariableCodeToID(variableCode);
//to get values, from the db
TsValuesSingleVariableType s = new TsValuesSingleVariableType();
s.censorCode = new CensorCodeType[1];
s.censorCode[0] = new CensorCodeType();
s.censorCode[0].censorCode = "nc";
s.censorCode[0].censorCodeDescription = "not censored";
s.censorCode[0].censorCodeID = 1;
s.censorCode[0].censorCodeIDSpecified = true;
//method
s.method = new MethodType[1];
s.method[0] = GetMethodForVariable(varId);
string timeStep = "hour";
//time units
s.units = new UnitsType();
s.units.unitAbbreviation = "hr";
s.units.unitCode = "103";
s.units.unitID = 104;
s.units.unitName = "hour";
s.units.unitType = "Time";
timeStep = "hour";
//method
s.method[0] = GetMethodForVariable(varId);
//qc level
s.qualityControlLevel = new QualityControlLevelType[1];
s.qualityControlLevel[0] = new QualityControlLevelType();
s.qualityControlLevel[0].definition = "raw data";
s.qualityControlLevel[0].explanation = "raw data";
s.qualityControlLevel[0].qualityControlLevelCode = "1";
s.qualityControlLevel[0].qualityControlLevelID = 1;
s.qualityControlLevel[0].qualityControlLevelIDSpecified = true;
//source
//TODO: read the correct source
s.source = new SourceType[1];
s.source[0] = GetSourceForSite(Convert.ToInt32(siteId));
s.source[0].citation = "CHMI";
s.source[0].organization = "CHMI";
s.source[0].sourceCode = "1";
s.source[0].sourceDescription = " measured by CHMI professional stations";
s.source[0].sourceID = 1;
s.source[0].sourceIDSpecified = true;
//values: get from database...
string connStr = GetConnectionString();
List<ValueSingleVariable> valuesList = new List<ValueSingleVariable>();
using (SqlConnection cnn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("plaveninycz.new_query_observations", cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@st_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@var_id", SqlDbType.SmallInt));
cmd.Parameters.Add(new SqlParameter("@start_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@end_time", SqlDbType.SmallDateTime));
cmd.Parameters.Add(new SqlParameter("@time_step", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@group_function", SqlDbType.VarChar));
cmd.Parameters["@st_id"].Value = Convert.ToInt32(siteId);
cmd.Parameters["@var_id"].Value = varId;
cmd.Parameters["@start_time"].Value = startDateTime;
cmd.Parameters["@end_time"].Value = endDateTime;
cmd.Parameters["@time_step"].Value = timeStep;
cmd.Parameters["@group_function"].Value = "sum";
cnn.Open();
SqlDataReader r = cmd.ExecuteReader();
int obsTimeIndex = r.GetOrdinal("obs_time");
int obsValueIndex = r.GetOrdinal("obs_value");
while (r.Read())
{
ValueSingleVariable v = new ValueSingleVariable();
v.censorCode = "nc";
v.dateTime = Convert.ToDateTime(r["obs_time"]);
v.dateTimeUTC = v.dateTime.AddHours(-1);
v.dateTimeUTCSpecified = true;
v.methodCode = s.method[0].methodCode;
v.methodID = v.methodCode;
v.offsetValueSpecified = false;
v.qualityControlLevelCode = "1";
v.sourceCode = "1";
v.sourceID = "1";
v.timeOffset = "01:00";
v.Value = convertValue(r["obs_value"], varId);
valuesList.Add(v);
}
}
}
s.value = valuesList.ToArray();
////convert list to array for temperature
//if (varId == 16)
//{
// s.value = valuesList.ToArray();
//}
//else
//{
// //convert list to array - for precip, snow, discharge, stage
// DateTime beginDate = valuesList[0].dateTime;
// DateTime endDate = valuesList[valuesList.Count - 1].dateTime;
// int numHours = endDate.Subtract(beginDate).Hours;
// ValueSingleVariable[] valuesArray = new ValueSingleVariable[numHours];
// int valueIndex = 0;
//
// DateTime curDate = beginDate;
// foreach (ValueSingleVariable val in valuesList)
// {
// if (valueIndex >= valuesArray.Length) break;
//
// while (curDate < val.dateTime)
// {
// valuesArray[valueIndex] = CreateNoDataValue(curDate,s, varId);
// curDate = curDate.AddHours(1);
// valueIndex++;
// }
//
// if (valueIndex >= valuesArray.Length) break;
// valuesArray[valueIndex] = val;
// curDate = val.dateTime.AddHours(1);
// valueIndex++;
// }
// s.value = valuesArray;
//}
return s;
}
// 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);
}
