// 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);
}
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);
}
// 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);
}
// Return number of records inserted or found.
public static int HandleMethodsInfo(SqlConnection sqlConn, MethodType m)
{
Console.WriteLine(">>>Parsing and inserting METHODS");
// Check if MethodID is already in the table or not
string cond = "MethodID = " + m.methodID;
if (OD_Utils.Exists("Methods", cond, sqlConn))
{
return(1);
}
string sql = string.Format(@"SET IDENTITY_INSERT [Methods] ON;
INSERT INTO Methods (MethodID, MethodDescription, MethodLink)
VALUES ({0}, '{1}', '{2}');
SET IDENTITY_INSERT [Methods] OFF",
m.methodID,
m.methodDescription,
m.methodLink);
return(OD_Utils.RunNonQuery(sql, sqlConn));
}
static int InsertOneMetadata(MetaDataType m, SqlConnection sqlConn)
{
string sql;
if (m != null)
{
sql = string.Format(@"INSERT INTO ISOMetadata ([TopicCategory],[Title],[Abstract],[ProfileVersion],[MetadataLink])
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')",
m.topicCategory,
m.title,
m.@abstract,
m.profileVersion,
m.metadataLink);
}
else
{
sql = @"INSERT INTO ISOMetadata ([TopicCategory],[Title],[Abstract],[ProfileVersion],[MetadataLink])
VALUES ('Unknown', 'Unknown', 'Unknown', 'Unknown', 'Unkown')";
}
return(OD_Utils.RunNonQuery(sql, sqlConn));
}
static void InsertOneVariable(OD_1_1_1DataSet.VariablesDataTable tblVariables,
VariableInfoType varInfo, SqlConnection sqlConn)
{
string cond;
OD_1_1_1DataSet.VariablesRow row = tblVariables.NewVariablesRow();
string[] vars = Regex.Split(varInfo.variableCode[0].Value, "/");
row.DataType = varInfo.dataType;
//row.DataType = OD_Utils.ConvertToString(varInfo.dataType);
row.VariableCode = vars[0] + "_" + row.DataType;
// Check if the variable is already in the table or not
//??? need to add ValueType filter
cond = "VariableCode = '" + row.VariableCode + "'";
if (OD_Utils.Exists(row.Table.TableName, cond, sqlConn))
{
return;
}
// VariableName is a tricky one too
//??? I think VariableCode is enough to differentiate different variables
cond = "Term = '" + varInfo.variableName + "'";
if (OD_Utils.Exists("VariableNameCV", cond, sqlConn))
{
// Found it and use it
row.VariableName = varInfo.variableName;
}
else
{
string myString = varInfo.variableName;
System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"\bas\b");
string[] splitString = reg.Split(myString);
// Refer to the document, e.g.,
// including "as", eg., "Nitrogen, nitrate (NO3) as N, filtered"
// fix ing "Calcium, filtered as Ca"
if (splitString.Length == 2)
{
row.VariableName = splitString[0];
}
else
{
// Remove the string after ","
vars = Regex.Split(varInfo.variableName, ",");
row.VariableName = vars[0];
}
}
row.Speciation = varInfo.speciation; // "Not Applicable"; //?
// Note: in DavtaValue Response Type units.unitsCode is actually unitsAbbreviation
if (varInfo.unit.unitCode != null)
{
//cond = "UnitsName = '" + varInfo.unit.unitName + "'";
//row.VariableUnitsID = OD_Utils.GetPrimaryKey("Units", "UnitsID", cond, sqlConn);
//if (row.VariableUnitsID >= 143) row.VariableUnitsID = row.VariableUnitsID + 1;
row.VariableUnitsID = Convert.ToInt32(varInfo.unit.unitCode);
}
else
{
row.VariableUnitsID = varInfo.unit.unitID;
}
row.SampleMedium = varInfo.sampleMedium;
row.ValueType = varInfo.valueType;
row.IsRegular = varInfo.timeScale.isRegular;
row.TimeSupport = varInfo.timeScale.timeSupport;
if (varInfo.timeScale.unit.unitCode != null)
{
row.TimeUnitsID = Convert.ToInt32(varInfo.timeScale.unit.unitCode);
}
else
{
row.TimeUnitsID = varInfo.timeScale.unit.unitID;
}
row.GeneralCategory = varInfo.generalCategory;
if (varInfo.noDataValue != null)
{
row.NoDataValue = varInfo.noDataValue;
}
else
{
row.NoDataValue = -9999;
}
tblVariables.AddVariablesRow(row);
}
static void HandleSites_1_1(string url, SqlConnection sqlConn)
{
string network, siteCode, varCode;
string queryParam = string.Empty;
string errMsg = string.Empty;
int count;
WaterML11.WaterOneFlowClient WofClient = new WaterOneFlowClient("WaterOneFlow", url);
string[] blank = new string[0];
WaterML11.SiteInfoResponseType siteRt = null;
try
{
siteRt = WofClient.GetSitesObject(blank, "");
}
catch (Exception e)
{
errMsg = "Exception@HandleSites: " + e.Message;
Console.WriteLine(errMsg);
}
if (siteRt == null)
{
if (errMsg.Length == 0)
{
errMsg = "Info: no site was returned from the URL.";
}
Console.WriteLine(errMsg);
return;
}
// Write into "Sites"
//siteRt = WofClient.GetSiteInfoObject(siteCode, "");
if (OD_SiteInfo.HandleSiteInfo(sqlConn, siteRt) == 0)
{
OD_Utils.Exit(1);
}
int nsites = siteRt.site.Count();
Console.WriteLine("Reading {0} sites from {1}..", nsites, url);
WaterML11.seriesCatalogType[] sctAll;
OD_SeriesCatalog odSC = new OD_SeriesCatalog(sqlConn);
#region Loop for each site
for (int i = 0; i < nsites; i++)
{
network = siteRt.site[i].siteInfo.siteCode[0].network;
siteCode = network + ":" + siteRt.site[i].siteInfo.siteCode[0].Value;
if (i % 10 == 0)
{
// Do this check and refresh for after every 100 sites
CheckAndRefreshResources();
sqlConn = SqlConnOD;
odSC.Adapter.Connection = sqlConn;
}
sctAll = LoadOnesiteInfo(WofClient, url, siteCode, ref errMsg);
if (sctAll == null)
{
if (errMsg.Length == 0)
{
errMsg = "Error: LoadOneSiteInfo() returned NULL.";
}
Console.WriteLine(errMsg);
continue;
}
if ((i + 1) % 50 == 0)
{
Console.WriteLine("..Site[{0}] {1} with {2} catalogs from {3} Time {4:HH:mm:ss}.",
i, siteCode, sctAll.Count(), url, DateTime.Now);
}
else
{
Console.WriteLine("..Site[{0}] {1} with {2} catalogs Time {3:HH:mm:ss}.",
i, siteCode, sctAll.Count(), DateTime.Now);
}
string sourceOrg = null;
int methodID = 0;
#region Loop for each seriesCatalogType
for (int j = 0; j < sctAll.Count(); j++)
{
WaterML11.seriesCatalogType sct = sctAll[j];
if (sct.series == null)
{
errMsg = string.Format("Error: WaterML11.seriesCatalogType[{0}].series is NULL.", j);
continue;
}
#region Loop for each seriesCatalogTypeSeries
for (int k = 0; k < sct.series.Count(); k++)
{
WaterML11.seriesCatalogTypeSeries scts = sct.series[k];
WaterML11.TimeIntervalType ti = (WaterML11.TimeIntervalType)scts.variableTimeInterval;
DateTime beginDateTime = ti.beginDateTime;
DateTime endDateTime = ti.endDateTime;
string code = scts.variable.variableCode[0].Value;
varCode = network + ":" + code;
//valueCount = scts.valueCount.Value;
Console.WriteLine("");
Console.WriteLine("....Series[{0}] var: {1} of site[{2}]", k, code, i);
// Use variable info from siteInfoResponseType
// instead of from variablesResponseType
// since the former includes the latter and contains more infomration
if (OD_VariableInfo.HandleVariableInfo(sqlConn, scts.variable) == 0)
{
Console.WriteLine("Failed to insert variable. Give up!");
continue;
}
// Add to SeriesCatalog table if not there
// Check SeriesCatalog table to get the newest data time
OD_1_1_1DataSet.SeriesCatalogRow scRow = odSC.GetOrCreateSeriesCatalog(
siteRt.site[i].siteInfo.siteCode[0].Value,
scts.variable.variableCode[0].Value,
scts);
if (scRow.EndDateTime >= ti.endDateTime)
{
Console.WriteLine("No further action since database has most recent ending date time.");
Console.WriteLine("Web service reported {0} values from <{1}> to <{2}>.",
scts.valueCount.Value, ti.beginDateTime.ToString(), ti.endDateTime.ToString());
Console.WriteLine("Database has {0} values from <{1}> to <{2}>.",
scRow.ValueCount, scRow.BeginDateTime.ToString(),
scRow.EndDateTime.ToString());
if (ti.beginDateTime != scRow.BeginDateTime)
{
Console.WriteLine("WARNING: Web server has older data not in database! Please double check!");
}
if (scts.valueCount.Value != scRow.ValueCount)
{
Console.WriteLine("WARNING: data value counts mismatch, maybe due to duplicate values from web service!");
}
continue;
}
// Should we use UTC time?
if (scRow.ValueCount > 0)
{
beginDateTime = OD_Utils.GetDateTime(
scRow.EndDateTime, scRow.TimeUnitsID, 1);
}
// Update DataValue table
//try
//{
WaterML11.TimeSeriesResponseType tsrt = null;
Console.WriteLine("......Getting {0} values from <{1}> to <{2}>",
scts.valueCount.Value, beginDateTime, endDateTime);
tsrt = LoadDataValueInfo(siteCode, varCode, WofClient,
beginDateTime, endDateTime);
if (tsrt == null)
{
continue;
}
if ((sourceOrg == null) || (!string.Equals(sourceOrg, scts.source.organization)))
{
// Insert a new source
sourceOrg = scts.source.organization;
OD_SourceInfo.HandleSourceInfo(sqlConn,
siteRt.site[i].siteInfo, scts, tsrt.timeSeries[0]);
}
//if ((methodCode == null) || (!string.Equals(methodCode, scts.method.methodCode)))
if ((methodID == 0) || (methodID != scts.method.methodID))
{
// Insert a new method
methodID = scts.method.methodID;
OD_Methods.HandleMethodsInfo(sqlConn, scts.method);
}
count = OD_DataValues.HandleDataValueInfo(sqlConn,
odSC, scRow, siteRt.site[i].siteInfo, scts, tsrt);
Console.WriteLine(" -------->>>>>>> Inserted {0} records. Database has {1} values from <{2}> to <{3}>.",
count, scRow.ValueCount, scRow.BeginDateTime.ToString(),
scRow.EndDateTime.ToString());
//}
//catch (Exception ex)
//{
// errMsg = "Exception@HandleDataValueInfo: " + ex.Message;
// Console.WriteLine(errMsg);
//}
}
#endregion Loop for each seriesCatalogTypeSeries
}
#endregion Loop for each seriesCatalogType
}
#endregion Loop for each site
} // end of HandleSites_1_1()
// 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);
}
