static WaterML11.TimeSeriesResponseType LoadDataValueInfo(string siteCode, string varCode, WaterML11.WaterOneFlowClient WofClient, DateTime beginDateTime, DateTime endDateTime, ref string queryParam, ref string errMsg) { string beginDT = string.Format("{0:s}", beginDateTime); string endDT = string.Format("{0:s}", endDateTime); errMsg = ""; queryParam = String.Format(" <{0}--{1}> ", beginDT, endDT); Console.WriteLine("......Getting values from <{0}> to <{1}>.", beginDT, endDT); WaterML11.TimeSeriesResponseType tsRt = null; try { tsRt = WofClient.GetValuesObject(siteCode, varCode, beginDT, endDT, ""); } catch (Exception e) { errMsg = "Exception@LoadData: " + e.Message; Console.WriteLine(errMsg); } return(tsRt); }
static void HandleSites_1_1(string url) { string network, siteCode, varCode; int seriesCatalogID, newSCID; string queryParam = string.Empty; string errMsg = string.Empty; WaterML11.WaterOneFlowClient WofClient = new WaterOneFlowClient("WaterOneFlow", url); string[] blank = new string[0]; WaterML11.SiteInfoResponseType sitesAll = null; try { 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; } WaterML11.SiteInfoResponseTypeSite[] sitesRt = sitesAll.site; int nsites = sitesRt.Count(); Console.WriteLine("........................................."); Console.WriteLine("Reading {0} sites from {1}", nsites, url); WaterML11.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(); } //// Testing //if (siteCode != "BENTHIC:Bnthc_S3058") // continue; //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; WaterML11.seriesCatalogType sct = sctAll[j]; if (sct.series == null) { errMsg = string.Format("Error: WaterML11.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++) { 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; 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."; // Testing // WaterML11.TimeSeriesResponseType tsRt = null; // tsRt = LoadDataValueInfo(siteCode, varCode, WofClient, //beginDateTime, endDateTime, ref queryParam, ref errMsg); 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, 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) { WaterML11.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 { WaterML11.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_1()
// Get DataValueInfo for 3 time periods: begin, middle, end. // As corresponding to the first, mid, and last 30 data values static WaterML11.TimeSeriesResponseType[] LoadDataValueInfoSplit(string siteCode, string varCode, WaterML11.WaterOneFlowClient WofClient, int valuecount, DateTime beginDateTime, DateTime endDateTime, ref string queryParam, ref string errMsg) { queryParam = ""; errMsg = ""; WaterML11.TimeSeriesResponseType[] tsRtAll = new WaterML11.TimeSeriesResponseType[3]; string beginDT = string.Format("{0:s}", beginDateTime); string endDT = string.Format("{0:s}", endDateTime); Console.WriteLine("......Getting values from <{0}> to <{1}>.", beginDT, endDT); DateTime[] fromDateTime = new DateTime[3]; DateTime[] toDateTime = new DateTime[3]; string fromDT, toDT; int[] count = new int[3] { 0, 0, 0 }; // Timespan between beginDT and endDT TimeSpan delta = endDateTime - beginDateTime; double step0 = delta.TotalSeconds * Program.CompareRecordCount * 2 / valuecount; // The first 30 records fromDateTime[0] = beginDateTime; toDateTime[0] = beginDateTime.AddSeconds(step0); // The middle 30 records fromDateTime[1] = beginDateTime.AddSeconds(delta.TotalSeconds / 2); toDateTime[1] = fromDateTime[1].AddSeconds(step0); // The last 30 records fromDateTime[2] = endDateTime.AddSeconds(-step0); toDateTime[2] = endDateTime; //...Site[25] UCRB_USBR:USBR_LNH with 1 catalogs from http://drought.usu.edu/usbrreservoirs/cuahsi_1_1.asmx?WSDL. //......Series[0] varCode: UCRB_USBR:STOR valCount: 995 //......Getting values from <2009-06-09T00:00:00> to <2012-02-29T00:00:00>. //... //......Getting values from <2008-04-16T00:00:00> to <2012-02-29T00:00:00>. //......Series[3] varCode: UCRB_USBR:ELEV valCount: 7048 //......Getting values from <1992-11-12T00:00:00> to <2012-02-29T00:00:00>. //......Getting values, Segment <1>: from <1992-11-12T00:00:00> to <1993-01-11T00:00:00>. //......Getting values, Segment <2>: from <2002-07-07T00:00:00> to <2002-09-05T00:00:00>. //......Getting values, Segment <3>: from <2011-12-31T00:00:00> to <2012-02-29T00:00:00>. for (int i = 0; i < 3; i++) { bool lastTry = false; tsRtAll[i] = null; double step = step0; while (!lastTry) { if ((i < 2) && (DateTime.Compare(toDateTime[i], endDateTime) >= 0)) { toDateTime[i] = endDateTime; lastTry = true; } else if ((i == 2) && (DateTime.Compare(fromDateTime[i], beginDateTime) <= 0)) { fromDateTime[i] = beginDateTime; lastTry = true; } fromDT = string.Format("{0:s}", fromDateTime[i]); toDT = string.Format("{0:s}", toDateTime[i]); Console.WriteLine("......Getting values, Segment <{0}>: from <{1}> to <{2}>.", i + 1, fromDT, toDT); try { tsRtAll[i] = WofClient.GetValuesObject(siteCode, varCode, fromDT, toDT, ""); } catch (Exception e) { errMsg = "Exception@LoadDataSplit: " + e.Message; Console.WriteLine(errMsg); return(null); } if (tsRtAll[i].timeSeries[0].values[0].value != null) { count[i] = tsRtAll[i].timeSeries[0].values[0].value.Count(); } if (count[i] >= Program.CompareRecordCount) { queryParam += String.Format(" <{0}--{1}> ", fromDT, toDT); break; } else { step *= 2; if (i < 2) { toDateTime[i] = toDateTime[i].AddSeconds(step); } else { fromDateTime[i] = fromDateTime[i].AddSeconds(-step); } } } } return(tsRtAll); }
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()
public int[] HandleDataValueInfo(int scID, int newID, WaterML11.TimeSeriesResponseType tsRt) { //Console.WriteLine("Parsing and inserting data value info."); WaterML11.ValueSingleVariable dvInfo; HealthQueryDataSet.DataValueRow row; int[] hasChanged = new int[3] { 0, 0, 0 }; //0 or 1 int valueCount = tsRt.timeSeries[0].values[0].value.Count(); if (valueCount == 1 && tsRt.timeSeries[0].values[0].value[0].Value == -99) { Console.WriteLine("No values in WS response."); // there are values in SC, but not in the response from WS } string cond; if (scID > 0) { cond = string.Format("SeriesCatalogID = {0} order by RecordID", scID); } else { cond = string.Format("NewSCID = {0} order by RecordID", newID); } Adapter.FillWhere(Table, cond); int[] indices = new int[] { 0, valueCount / 2, valueCount - Program.CompareRecordCount }; for (int i = 0; i < 3; i++) { int recordID = i * Program.CompareRecordCount + 1; int idx = indices[i]; int limit = Math.Min(Program.CompareRecordCount, valueCount - indices[i]); if ((i > 0) && (indices[i] < Program.CompareRecordCount * i)) { // Record overlap, all records are put into table, no further action break; } for (int j = 0; j < limit; j++) { dvInfo = tsRt.timeSeries[0].values[0].value[idx + j]; if (recordID + j > Table.Rows.Count) { row = Table.NewDataValueRow(); row.DataValue = (double)dvInfo.Value; row.ValueAccuracy = dvInfo.accuracyStdDev; row.DateTimeUTC = dvInfo.dateTime.ToUniversalTime(); row.SeriesCatalogID = scID; row.NewSCID = newID; row.RecordID = recordID + j; Table.AddDataValueRow(row); hasChanged[i] = 1; continue; } row = Table.ElementAt(recordID - 1 + j); if ((row.DataValue != (double)dvInfo.Value) || (row.ValueAccuracy != dvInfo.accuracyStdDev) || (DateTime.Compare(row.DateTimeUTC, dvInfo.dateTime.ToUniversalTime()) != 0)) { row.DataValue = (double)dvInfo.Value; row.ValueAccuracy = dvInfo.accuracyStdDev; row.DateTimeUTC = dvInfo.dateTime.ToUniversalTime(); hasChanged[i] = 1; } } } Adapter.Update(Table); return(hasChanged); }