/*
* This routine is to add SQL WHERE clause to limit records to be loaded.
* Be sure to copy this routine to SeriesCatalogTableAdapter class when
* hiscentralDataSet.Designer.cs is regenerated to make GetRow() work.
* Copied from http://www.codeproject.com/Articles/17324/Extending-TableAdapters-for-Dynamic-SQL.
* public int FillWhere(hiscentralDataSet.SeriesCatalogDataTable dataTable, string whereExpression)
* {
* string text1 = this._commandCollection[0].CommandText;
* try
* {
* this._commandCollection[0].CommandText += " WHERE " + whereExpression;
* return this.Fill(dataTable);
* }
* finally { this._commandCollection[0].CommandText = text1; }
* }
*/
public hiscentralDataSet.SeriesCatalogRow GetRow(string siteCode, string varCode,
WaterML10.seriesCatalogTypeSeries scts)
{
string cond = "SiteCode = '" + siteCode + "' and ";
cond += "VariableCode = '" + varCode + "'";
Adapter.FillWhere(Table, cond);
if (Table.Rows.Count == 0)
{
return(null);
}
else
{
if (Table.Rows.Count > 1)
{
string filter = string.Format("SeriesCode like '%||{0}||{1}||{2}'",
scts.Method.methodID, scts.Source.sourceID,
scts.QualityControlLevel.QualityControlLevelID);
DataRow[] rows = Table.Select(filter);
if (rows.Length == 1)
{
return((hiscentralDataSet.SeriesCatalogRow)rows[0]);
}
else
{
Console.WriteLine(
"SeriesCatalog table contains {0} records for site {1} var {2}, got {3} records with filter '{4}'.",
Table.Rows.Count, siteCode, varCode, rows.Length, filter);
}
}
return((hiscentralDataSet.SeriesCatalogRow)Table.Rows[0]);
}
}
static void HandleSites_1_0(string url)
{
string network, siteCode, varCode;
int seriesCatalogID, newSCID;
string queryParam = string.Empty;
string errMsg = string.Empty;
WaterML10.WaterOneFlowClient WofClient = new WaterOneFlowClient("WaterOneFlow", url);
string[] blank = new string[0];
WaterML10.SiteInfoResponseType sitesAll = null;
try
{
// WaterML1.0
sitesAll = WofClient.GetSites(blank, "");
// WaterML1.1
//sitesAll = WofClient.GetSitesObject(blank, "");
}
catch (Exception e)
{
errMsg = "Exception@HandleSites: " + e.Message;
Console.WriteLine(errMsg);
}
if (sitesAll == null)
{
if (errMsg.Length == 0)
{
errMsg = "Info: no site was returned from the URL.";
}
Console.WriteLine(errMsg);
HQT.HandleQueryTimeseries("", "", -1, -1, -1, null, url, errMsg);
return;
}
WaterML10.SiteInfoResponseTypeSite[] sitesRt = sitesAll.site;
int nsites = sitesRt.Count();
Console.WriteLine(".........................................");
Console.WriteLine("Reading {0} sites from {1}", nsites, url);
WaterML10.seriesCatalogType[] sctAll;
#region Loop for each site
for (int i = 0; i < nsites; i++)
{
if (CPEnabled)
{
if (CP.siteIndex > i)
{
continue;
}
}
if (i % 100 == 0)
{
// Do this check and refresh for after every 100 sites
CheckAndRefreshResources();
}
network = sitesRt[i].siteInfo.siteCode[0].network;
siteCode = network + ":" + sitesRt[i].siteInfo.siteCode[0].Value;
if (CPEnabled)
{
CP.siteIndex = i;
CP.siteCode = siteCode;
SaveCheckPoint();
}
//queryParam = string.Format("QueryID = (SELECT TOP 1 QueryID FROM dbo.QueryTimeseries where SiteCode = '{0}'" +
// " ORDER BY QueryID DESC) and DATEDIFF(HOUR, QueryDateTime, GETDATE()) <= {1}",
// siteCode, NoCheckHours);
//if (OD_Utils.Exists("dbo.QueryTimeseries", queryParam, HQT.Adapter.Connection))
//{
// Console.WriteLine("..Skip Site {0} which was already checked in last {1} hours.",
// siteCode, NoCheckHours);
// continue;
//}
if (sitesRt[i].seriesCatalog != null)
{
sctAll = sitesRt[i].seriesCatalog;
}
else
{
sctAll = LoadOnesiteInfo(WofClient, url, siteCode, ref errMsg);
}
if (sctAll == null)
{
if (errMsg.Length == 0)
{
errMsg = "Error: LoadOneSiteInfo() returned NULL.";
}
Console.WriteLine(errMsg);
HQT.HandleQueryTimeseries(siteCode, "", -1, -1, -1, null, url, errMsg);
continue;
}
if ((i + 1) % 100 == 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);
}
// network = SRBHOS, sites[i].seriesCatalog=null
// My assumption is that
// Under that situation, need to get SiteInfoResponseType one site by one site
// Here is the one example siteCode for testing
//WaterML11.SiteInfoResponseType rt = new WaterML11.SiteInfoResponseType();
//rt = WofClient.GetSiteInfoObject("SRBHOS:RTHNet", "");
#region Loop for each seriesCatalogType
for (int j = 0; j < sctAll.Count(); j++)
{
int valueCount;
WaterML10.seriesCatalogType sct = sctAll[j];
if (sct.series == null)
{
errMsg = string.Format("Error: WaterML10.seriesCatalogType[{0}].series is NULL.", j);
HQT.HandleQueryTimeseries(siteCode, "", -1, -1, -1, null, url, errMsg);
continue;
}
#region Loop for each seriesCatalogTypeSeries
for (int k = 0; k < sct.series.Count(); k++)
{
WaterML10.seriesCatalogTypeSeries scts = sct.series[k];
WaterML10.TimeIntervalType ti = (WaterML10.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;
if (valueCount <= MinValueCount)
{
Console.WriteLine(
"....Series[{0}] varCode: {1} valCount: {2}, too small, record in Query table, no further action.",
k, code, valueCount);
queryParam = String.Format(" <{0}--{1}> ", beginDateTime, endDateTime);
errMsg = "Info: value count is too small, no further action.";
HQT.HandleQueryTimeseries(siteCode, varCode, -1, -1, valueCount, null,
queryParam, errMsg);
continue;
}
// Check hiscentral/SeriesCatalog table to get seriesCatalogID.
// Note: beginDateTime and endDateTime are obtained from web service.
seriesCatalogID = newSCID = 0;
hiscentralDataSet.SeriesCatalogRow hscRow = HisSC.GetRow(siteCode, varCode, scts);
if (hscRow != null)
{
seriesCatalogID = hscRow.SeriesID;
if (hscRow.SeriesCode != null)
{
// Don't want to repeatedly print siteCode and network
int idx = hscRow.SeriesCode.IndexOf(code);
code = hscRow.SeriesCode.Substring(idx);
}
Console.WriteLine("....Series[{0}] var: {1}, ID: {2}, valCount: {3}",
k, code, seriesCatalogID, valueCount);
}
else
{
newSCID = HSC.GetOrCreateSeriesID(siteCode, varCode, scts);
Console.WriteLine("....Series[{0}] var: {1}||{2}||{3}||{4}, newID: {5}, valCount: {6}",
k, code,
// Where WaterML1.1 and WaterML1.0 differ
scts.Method.methodID,
scts.Source.sourceID,
scts.QualityControlLevel.QualityControlLevelID,
newSCID, valueCount);
}
// Update HealthQuery/DataValue table if datavalue changed
int[] hasChanged = null;
try
{
if (valueCount > 5000)
{
WaterML10.TimeSeriesResponseType[] tsRtAll = null;
tsRtAll = LoadDataValueInfoSplit(siteCode, varCode, WofClient,
valueCount, beginDateTime, endDateTime, ref queryParam, ref errMsg);
if (tsRtAll != null)
{
hasChanged = HDV.HandleDataValueInfoSplit(seriesCatalogID, newSCID, tsRtAll);
}
}
else
{
WaterML10.TimeSeriesResponseType tsRt = null;
tsRt = LoadDataValueInfo(siteCode, varCode, WofClient,
beginDateTime, endDateTime, ref queryParam, ref errMsg);
if (tsRt != null)
{
hasChanged = HDV.HandleDataValueInfo(seriesCatalogID, newSCID, tsRt);
}
}
}
catch (Exception ex)
{
errMsg = "Exception@HandleSites1: " + ex.Message;
Console.WriteLine(errMsg);
}
// Add to HealthQuery/QueryTimeseries table.
HQT.HandleQueryTimeseries(siteCode, varCode, seriesCatalogID, newSCID,
valueCount, hasChanged, queryParam, errMsg);
}
#endregion Loop for each seriesCatalogTypeSeries
// We delay inserting records to Query table to improve performance, check and do it now
if (HQT.Table.Count > Program.DbUpdateBatchSize / 2)
{
HQT.Adapter.Update(HQT.Table);
HQT.Table.Clear();
}
}
#endregion Loop for each seriesCatalogType
}
#endregion Loop for each site
if (HQT.Table.Count > 0)
{
HQT.Adapter.Update(HQT.Table);
HQT.Table.Clear();
}
} // end of HandleSites_1_0()
// The seriesCatalogID is not found in hiscental database, create one here before
// hiscental database adopts the new variable.
public int GetOrCreateSeriesID(string siteCode, string varCode,
WaterML10.seriesCatalogTypeSeries scts)
{
int scID;
string query;
int undefined = -99;
// Console.WriteLine(@"......hiscentral.SeriesCatalog has no record for site {0} and
// variable {1}, dataType '{2}' methodID {3} qualityID {4} sourceID {5}.",
// siteCode, varCode, scts.dataType, scts.method.methodID,
// scts.qualityControlLevel.qualityControlLevelID,
// scts.source.sourceID);
if (scts.Method.methodID < 0)
{
scts.Method.methodID = undefined;
}
if (scts.QualityControlLevel.QualityControlLevelID < 0)
{
scts.QualityControlLevel.QualityControlLevelID = undefined;
}
if (scts.Source.sourceID < 0)
{
scts.Source.sourceID = undefined;
}
query = string.Format(@"SiteCode='{0}' and VariableCode='{1}' and
MethodID = {2} and QualityControlLevelID = {3} and
SourceID = {4}", siteCode, varCode,
scts.Method.methodID,
scts.QualityControlLevel.QualityControlLevelID,
scts.Source.sourceID);
scID = OD_Utils.GetPrimaryKey("dbo.SeriesCatalog", "SeriesID", query, Adapter.Connection);
if (scID >= 0)
{
return(scID);
}
HealthQueryDataSet.SeriesCatalogRow row = Table.NewSeriesCatalogRow();
row.SiteCode = siteCode;
row.VariableCode = varCode;
row.MethodID = scts.Method.methodID;
row.SourceID = scts.Source.sourceID;
row.QualityControlLevelID = scts.QualityControlLevel.QualityControlLevelID;
if (OD_Utils.ConvertToString(scts.variable.dataType) != null)
{
row.DataType = OD_Utils.ConvertToString(scts.variable.dataType);
}
row.ValueCount = scts.valueCount.Value;
WaterML10.TimeIntervalType ti = (WaterML10.TimeIntervalType)scts.variableTimeInterval;
row.BeginDateTimeUTC = ti.beginDateTime.ToUniversalTime();
row.EndDateTimeUTC = ti.endDateTime.ToUniversalTime();
Table.AddSeriesCatalogRow(row);
Adapter.Update(Table);
Table.Clear();
scID = OD_Utils.GetPrimaryKey("dbo.SeriesCatalog", "SeriesID", query, Adapter.Connection);
Console.WriteLine(
"....Site {0} and variable {1} with ID {2} is saved in a private catalog table.",
siteCode, varCode, scID);
return(scID);
}