//update series
public void updateSeries(int siteID, int varID, DateTime beginDateTime, DateTime endDateTime, DateTime beginDateTimeUTC, DateTime endDateTimeUTC, int valueCount)
{
try
{
SeriesCatalog sc = (from r in this.SeriesCatalog where r.SiteID == siteID && r.VariableID == varID select r).First();
Sites s = (from S in this.Sites where S.SiteID == siteID select S).First();
//sc.SiteID = s.SiteID;
sc.SiteCode = s.SiteCode;
sc.SiteName = s.SiteName;
sc.BeginDateTime = beginDateTime;
sc.EndDateTime = endDateTime;
sc.BeginDateTimeUTC = beginDateTimeUTC;
sc.EndDateTimeUTC = endDateTimeUTC;
sc.ValueCount = valueCount;
this.SaveChanges();
}
catch (Exception e)
{
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Error", "clsDatabase" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", e.Message);
}
}
public void AgencyDataFill(L1HarvestList variable, string start, string end, int count)
{
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_1" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", String.Format("{0} {1} {2}-{3} {4}\t{5}-{6} {7}/{8}", "Level1", variable.SiteName, start.Replace('-', '/'), end.Replace('-', '/'), variable.SiteCode, variable.VariableCode, "Level1", count, db.SummaryDB.Count));
TimeSeriesType dataVals = this.GetData(variable, start, end);
if (dataVals.values.Count() >= 1)
{
int siteID = this.SiteData(variable);
if (variable.SiteID == 0)
{
this.db.SummaryDB.UpdateHarvestSite(variable, siteID);
}
this.noDataValue = Convert.ToDouble(dataVals.variable.noDataValue);
TsValuesSingleVariableType vals = dataVals.values[0];
if (vals.value != null)
{
DateTime startDate = (from n in vals.value select Convert.ToDateTime(n.dateTime)).Min();
DateTime endDate = (from n in vals.value select Convert.ToDateTime(n.dateTime)).Max();
var data = from n in vals.value select n;
foreach (ValueSingleVariable member in data)
{
TimeSpan utcoffset = member.dateTime - member.dateTime.ToUniversalTime();
this.values.Rows.Add(SqlInt32.Null, Convert.ToDouble(member.Value), (member.accuracyStdDev == 0 ? SqlDouble.Null : member.accuracyStdDev), member.dateTime, utcoffset.Hours, member.dateTime.ToUniversalTime(), siteID, variable.VariableID, (member.offsetValue == 0 ? SqlDouble.Null : member.offsetValue), (Convert.ToInt32(member.offsetTypeID) == 0 ? SqlInt32.Null : Convert.ToInt32(member.offsetTypeID)), member.censorCode.ToString(), SqlInt32.Null, variable.MethodID, variable.SourceID, (Convert.ToInt32(member.sampleID) == 0 ? SqlInt32.Null : Convert.ToInt32(member.sampleID)), SqlInt32.Null, variable.QualityControlLevelID);
}
this.db.TooDB.InsertBulk(this.values);
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_1" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", values.Rows.Count + " Rows Saved. " + "Level1" + " " + variable.SiteName + " " + variable.SiteCode + "\t" + variable.VariableCode + "-" + "Level1");
this.db.TooDB.SaveSeries(siteID, variable);
}
}
else
{
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "Level1Data1_1" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "No Values Found. " + "Level" + " " + variable.SiteName + " " + variable.SiteCode + "\t" + variable.VariableCode + "-" + "Level1");
}
}
private bool fillTable(WebRequest[] request)
{
int count = 0;
while (count < 5 && keepgoing)
{
count++;
try
{
WebResponse[] response = new WebResponse[request.Length];
Stream[] dataStream = new Stream[request.Length];
StreamReader[] reader = new StreamReader[request.Length];
string[] responseFromServer = new string[request.Length];
for (int i = 0; i < request.Length; i++)
{
response[i] = request[i].GetResponse();
dataStream[i] = response[i].GetResponseStream();
reader[i] = new StreamReader(dataStream[i]);
responseFromServer[i] = reader[i].ReadToEnd();
}
Regex[] regex = new Regex[request.Length + 1];
Match[] oM = new Match[request.Length + 1];
//get the columns of the Dataset
regex[0] = new Regex("-.*\n");
oM[0] = regex[0].Match(responseFromServer[0]);
createColumns(oM[0]);
if (request.Length < 2)
{
regex[1] = new Regex("\n(.|\n)*", RegexOptions.IgnoreCase);
}
else
{
regex[1] = new Regex("\n(.|\n)*", RegexOptions.IgnoreCase);
regex[2] = new Regex("\n(.|\n)*" + DateTime.Now.ToString("MMddyy"), RegexOptions.IgnoreCase);
}
for (int i = 0; i < request.Length; i++)
{
//Here we apply our regular expression to our string using the Match object.
oM[i + 1] = regex[i + 1].Match(responseFromServer[i]);
reader[i].Close();
response[i].Close();
createRows(oM[i + 1]);
}
return(true);
}
catch (Exception ex)
{
if (ex.Message.Contains("Unable to read data"))
{
keepgoing = false;
}
if (count == 5)
{
DBLogging.WriteLog(projectName, "Error", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", ex.Message + ". " + request[0].RequestUri.AbsoluteUri + " StackTrace:" + ex.StackTrace);
return(false);
//throw new Exception("SNOTEL Website is Offline");
}
}
}
return(false);
}
private void fillData()
{
numOfSites = db.getNumOfSites();
DBLogging.WriteLog(projectName, "Log", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Updating Series Catalog Table: {0} sites", numOfSites));
//loop through all of the sites and pull down the information from the website
for (siteID = beginSite; siteID <= numOfSites && keepgoing; siteID++)
{
string siteName = db.getSiteName(siteID);
string state = db.getState(siteID);
string siteCode = db.getSiteCode(siteID);
int timeZone = Convert.ToInt16(db.getTimeZone(siteID));
if (getVariables(siteCode, state))
{
//attach variables to columns
//for every variable
numOfVars = numOfCols - 1;
//check to see if there is information in the database about the variable if true continue, if false skip
for (int i = 1; i <= numOfVars; i++)
{
//int i = 1;
int variableID;
try
{
variableID = (int)db.getVariableID(oDS.Tables[0].Columns[i].ColumnName.Trim().ToUpper());
}
catch { variableID = -9999; }
if (variableID > 0)
{
int startIndex = 0;
int endIndex = 0;
//calculate begin and end time
String endDT = FindEnd(oDS, i, ref endIndex);
String beginDT = FindStart(oDS, i, ref startIndex);
DateTime beginDateTimeUTC = format.toDateTime(beginDT, timeZone);
DateTime endDateTimeUTC = format.toDateTime(endDT, timeZone);
DateTime endDateTime = format.toDateTime(endDT, 0);
DateTime beginDateTime = format.toDateTime(beginDT, 0);
//endDateTimeUTC = endDateTime.ToUniversalTime();
int valueCount = calcValues(startIndex, endIndex, i);
if (db.isNewSeries(siteID, variableID))
{
DBLogging.WriteLog(projectName, "Log", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Adding New Series. Site:{0}/{1} SiteName: {2} Variable ID:{3}, {4} {5}/{6}", siteID + 1, numOfSites, siteName, state, variableID, i, numOfVars));
Variables v = db.getVariableInfo(variableID);
Units vu = db.getUnits(Convert.ToInt32(v.UnitsReference.EntityKey.EntityKeyValues[0].Value));
Units tu = db.getUnits(Convert.ToInt32(v.Units1Reference.EntityKey.EntityKeyValues[0].Value));
Methods m = db.getMethod(variableID);
Sources s = db.getSourceData(1);
int qualityControlLevelID = 1;
string qualityControlLevelCode = qualityControlLevelID.ToString();
if (siteName.Contains('\''))
{
string[] list = siteName.Split('\'');
siteName = list[0] + "\'\'" + list[1];
}
db.insertSeries(siteID, siteCode, siteName, v.VariableID, v.VariableCode, v.VariableName, v.Speciation, vu.UnitsID, vu.UnitsName, v.SampleMedium, v.ValueType, v.TimeSupport, tu.UnitsID, tu.UnitsName, v.DataType, v.GeneralCategory, m.MethodID, m.MethodDescription, s.SourceID, s.Organization, s.SourceDescription, s.Citation, qualityControlLevelID, qualityControlLevelCode, beginDateTime, endDateTime, beginDateTimeUTC, endDateTimeUTC, valueCount);
}
else
{
DBLogging.WriteLog(projectName, "Log", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Updating Series. Site:{0}/{1} SiteName: {2} Variable ID:{3}, {4} {5}/{6}", siteID, numOfSites, siteName, state, variableID, i, numOfVars));
//DBLogging.WriteLog(projectName, "Log", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Updating Series. Site:{0}/{1} SiteName: {2} Variable ID:{3}, {4}/{5}", siteID, numOfSites, siteName,, variableID, i, numOfVars));
db.updateSeries(siteID, variableID, beginDateTime, endDateTime, beginDateTimeUTC, endDateTimeUTC, valueCount);
}
}
}
}
else
{
DBLogging.WriteLog(projectName, "Error", "UpdateDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("No Data Found. Site:{0}/{1} SiteName: {2}, {3} ", siteID, numOfSites, siteName, state));
}
numOfCols = 0;
}
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main(string[] args)
{
ClsDBAccessor dba = new ClsDBAccessor();
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "DataSummary Running" + listAgencies(args));
var options = new Options();
if (CommandLineParser.Default.ParseArguments(args, options))
{
// consume Options type properties
if (options.Levels == null)
{
options.Levels = new List <string>();
options.Levels.Add("1");
options.Levels.Add("2");
options.Levels.Add("3");
options.Levels.Add("4");
}
}
foreach (string level in options.Levels)
{
switch (level)
{
case "1":
if (options.Agency == null)
{
new getOriginalAgency();
}
else
{
new getOriginalAgency(options.Agency.ToArray());
}
// dba.SummaryDB.updateSeriesCatalag();
break;
case "2":
if (options.Agency == null)
{
new TimeAggregated();
}
else
{
new TimeAggregated(options.Agency.ToArray());
}
// dba.SummaryDB.updateSeriesCatalag();
break;
case "3":
if (options.Agency == null)
{
new IndexVariables();
}
else
{
new IndexVariables(options.Agency.ToArray());
}
// dba.SummaryDB.updateSeriesCatalag();
break;
case "4":
if (options.Agency == null)
{
new IndexValues();
}
else
{
new IndexValues(options.Agency.ToArray());
}
// dba.SummaryDB.updateSeriesCatalag();
break;
}
}
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "Updating SeriesCatalog of Summary Database");
dba.SummaryDB.updateSeriesCatalag();
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "DataSummary Completed Running" + listAgencies(args));
SendEmail.SendMessage("DataSummary Completed", "DataSummary has completed running, view the attached file for details", Properties.Settings.Default.projectName, new TimeSpan(7, 0, 0, 0));
}
public int SaveSite(
string siteCode,
string siteName,
double lng,
double lat,
int lldatumID,
double elev,
string vertDatum,
int localx,
int localy,
int lprojid,
int posAcc,
string state,
string county,
string comments,
string siteType)
{
Site s;
try
{
s = (from S in this.Sites where S.SiteCode == siteCode /*&& S.SiteType == siteType/* && S.SiteName == siteName && S.Longitude == lng*/ select S).First();
}
catch
{
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "clsTooDB" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Saving new Site: {0}", siteName));
s = new Site();
s.SiteCode = siteCode;
s.SiteName = siteName;
s.Longitude = lng;
s.Latitude = lat;
s.LatLongDatumID = lldatumID;
s.Elevation_m = this.TestInput(elev);
s.VerticalDatum = this.TestInput(vertDatum);
if (localx != 0)
{
s.LocalX = localx;
}
if (localy != 0)
{
s.LocalY = localy;
}
if (lprojid != 0)
{
s.LocalProjectionID = lprojid;
}
if (posAcc != 0)
{
s.PosAccuracy_m = posAcc;
}
s.State = this.TestInput(state);
s.County = this.TestInput(county);
s.Comments = this.TestInput(comments);
// s.SiteType = siteType;
this.AddToSites(s);
this.SaveChanges();
}
return(s.SiteID);
}
private void downloadOriginal()
{
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "getOriginalAgency" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "Level 1 Data Harvesting Has Begun Running");
int count = 0;
foreach (L1HarvestList variable in aggregatelist)
{
{
count++;
ClsDBAccessor dba = new ClsDBAccessor(variable, db);
DateTime start = new DateTime(1900, 01, 01);
DateTime end;
//test to see if the series exits.
int sID = dba.TooDB.SeriesExists(variable.SiteID, variable.VariableID);
DateTime today = DateTime.Today;
if (sID > 0)
{
DateTime s = dba.TooDB.GetLastDateOfSeries(sID);
start = s.AddDays(1);
}
// set end date to yesterday
end = new DateTime(today.Year, today.Month, today.Day).AddDays(-1);
TimeSpan span = Convert.ToDateTime(end).Subtract(Convert.ToDateTime(start));
if (span > new TimeSpan(1, 0, 0, 0))
{
try
{
if (variable.WebServiceURL.Contains("1_1"))
{
Level1Data1_1 m = new Level1Data1_1(dba);
m.AgencyDataFill(variable, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), count);
}
else
{
try
{
Level1Data1_0 m = new Level1Data1_0(dba);
m.AgencyDataFill(variable, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), count);
}
catch (Exception ex)
{
if (ex.Message.Contains("Server did not recognize the value of HTTP Header SOAPAction"))
{
Level1Data1_1 m = new Level1Data1_1(dba);
m.AgencyDataFill(variable, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"), count);
}
else
{
throw;
}
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("See the InnerException"))
{
DBLogging.WriteLog(this.projectName, "Error", ex.StackTrace, String.Format("{0}. {3} {1}, {2}-{4}. ", ex.InnerException.Message, variable.SiteCode, variable.VariableCode, variable.SiteName, "Level1"));
}
else
{
try
{
DBLogging.WriteLog(this.projectName, "Error", ex.StackTrace, String.Format("{1}: {0}. {4} {2}, {3}-{5}. ", ex.Message, ex.InnerException.Message, variable.SiteCode, variable.VariableCode, variable.SiteName, "Level1"));
}
catch
{
DBLogging.WriteLog(this.projectName, "Error", ex.StackTrace, String.Format("{0}. {3} {1}, {2}-{4}. ", ex.Message, variable.SiteCode, variable.VariableCode, variable.SiteName, "Level1"));
}
}
}
}
}
Thread.Sleep(100);
// variable = this.db.GetNextAggregateEntry(variable.AggregateID);
}
db.updateSeriesCatalag();
DBLogging.WriteLog(Properties.Settings.Default.projectName, "Log", "getOriginalAgency" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "Level 1 Data Harvesting Has Completed Running");
}
public void loadData()
{
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "SNODAS Harvester Has Begun Running");
List <int> hucs = new List <int> {
8, 0, 2
};
foreach (int huc in hucs)
{
int sweID = 1;
int coverID = 8;
int depthID = 2;
db = new clsDatabase(huc);
//DateTime Date = db.getLastDate(sweID).Add(new TimeSpan(1,0,0,0));
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "Gathering Huc " + huc + " data. ");
for (DateTime Date = db.getLastDate(sweID).Add(new TimeSpan(1, 0, 0, 0)); Date < DateTime.Now; Date = Date.Add(new TimeSpan(1, 0, 0, 0)))
{
string SWE = "";
try
{
//string SWE = "ftp://ftp.nohrsc.nws.gov/products/collaborators/h8_ssmv11034tS__T0001TTNATS2012021905HP001.txt";//snow water equivalent
SWE = string.Format("ftp://ftp.nohrsc.nws.gov/products/collaborators/h{0}_ssmv11034tS__T0001TTNATS{1}{2}{3}05HP001.txt", huc /*hucNumber*/, Date.Year /*year*/, Date.ToString("MM") /*Month*/, Date.ToString("dd") /*Day*/);//snow water equivalent
//SWE = string.Format("C:/DEV/NIDIS/SNODAS/2012 data/Huc{0}/h{0}_ssmv11034tS__T0001TTNATS{1}{2}{3}05HP001.txt", huc/*8hucNumber*/, Date.Year/*year*/, Date.ToString("MM")/*Month*/, Date.ToString("dd")/*Day*/);//snow water equivalent
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Water Equivelant loading file: {0}", SWE));
readData(SWE, sweID);
}
catch (Exception ex)
{
DBLogging.WriteLog(projectName, "Error", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Water Equivelant file load error . {0}. {1}", SWE, ex.Message));
}
}
for (DateTime Date = db.getLastDate(coverID).Add(new TimeSpan(1, 0, 0, 0)); Date < DateTime.Now; Date = Date.Add(new TimeSpan(1, 0, 0, 0)))
{
string snowCover = "";
try
{
snowCover = string.Format("ftp://ftp.nohrsc.nws.gov/products/collaborators/h{0}_ssmv11034tS__T0001TTNATS{1}{2}{3}05HP001_s.txt", huc /*8hucNumber*/, Date.Year /*year*/, Date.ToString("MM") /*Month*/, Date.ToString("dd") /*Day*/);//Percent snow Cover
//snowCover = string.Format("C:/DEV/NIDIS/SNODAS/2012 data/Huc{0}/h{0}_ssmv11034tS__T0001TTNATS{1}{2}{3}05HP001_s.txt", huc/*8hucNumber*/, Date.Year/*year*/, Date.ToString("MM")/*Month*/, Date.ToString("dd")/*Day*/);//Percent snow Cover
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Cover loading file: {0}", snowCover));
readData(snowCover, coverID);
}
catch (Exception ex)
{
DBLogging.WriteLog(projectName, "Error", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Cover file load error . {0}. {1}", snowCover, ex.Message));
}
}
for (DateTime Date = db.getLastDate(coverID).Add(new TimeSpan(1, 0, 0, 0)); Date < DateTime.Now; Date = Date.Add(new TimeSpan(1, 0, 0, 0)))
{
string snowDepth = "";
try
{
//string snowDepth = "ftp://ftp.nohrsc.nws.gov/products/collaborators/h8_ssmv11036tS__T0001TTNATS2012022805HP001.txt";//snow depth data
snowDepth = string.Format("ftp://ftp.nohrsc.nws.gov/products/collaborators/h{0}_ssmv11036tS__T0001TTNATS{1}{2}{3}05HP001.txt", huc /*8hucNumber*/, Date.Year /*year*/, Date.ToString("MM") /*Month*/, Date.ToString("dd") /*Day*/);//snow depth data
//snowDepth = string.Format("C:/DEV/NIDIS/SNODAS/2012 data/Huc{0}/h{0}_ssmv11036tS__T0001TTNATS{1}{2}{3}05HP001.txt", huc/*8hucNumber*/, Date.Year/*year*/, Date.ToString("MM")/*Month*/, Date.ToString("dd")/*Day*/);//snow depth data
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Depth loading file: {0}", snowDepth));
readData(snowDepth, depthID);
}
catch (Exception ex)
{
DBLogging.WriteLog(projectName, "Error", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", string.Format("Snow Depth file load error . {0}. {1}", snowDepth, ex.Message));
}
}
db.createSeriesCatalog();
}
DBLogging.WriteLog(projectName, "Log", "clsLoadSNODAS" + "." + (new StackTrace(true)).GetFrame(0).GetMethod().Name + "()", "SNODAS Harvester Has Completed Running");
Thread.Sleep(30);
SendEmail.SendMessage(projectName + " Completed", projectName + " has completed running, view the attached file for details", projectName, new TimeSpan(7, 0, 0));
}