// Return number of records inserted or found
public static int HandleSiteInfo(SqlConnection sqlConn, WaterML11.SiteInfoResponseType rt)
{
SitesTableAdapter stAdapter = new SitesTableAdapter();
stAdapter.Connection = sqlConn;
WaterML11.SiteInfoType stinfo;
OD_1_1_DataSet.SitesDataTable tblSites = new OD_1_1_DataSet.SitesDataTable();
Console.WriteLine("Parsing and inserting site info.");
for (int i = 0; i < rt.site.Count(); i++)
{
stinfo = rt.site[i].siteInfo;
try
{
if (InsertOneSite(tblSites, stinfo, sqlConn))
{
stAdapter.Update(tblSites);
}
}
catch (Exception e)
{
Console.WriteLine("Failed to insert site {0}, completed {1} of {2}: {3}.",
stinfo.siteCode, i, rt.site.Count(), e.Message);
return(i);
}
}
// PrintTable(stAdapter, tblSites);
return(rt.site.Count());
}
public static ValuesDataSet GetValuesDataSet(int siteID, W3CDateTime BeginDateTime, W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
//valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
//try
//{
// valuesTableAdapter.FillBySiteIDBetweenDates(ds.DataValues, siteID, BeginDateTime.DateTime,
// EndDateTime.DateTime);
//}
//catch (Exception e)
//{
// log.Fatal("Cannot retrieve information from database: " + e.Message);
// //+ valuesTableAdapter.Connection.DataSource
//}
//return ds;
siteAdapter.FillBySiteID(ds.Sites, siteID);
vAdapter.FillBySiteID(ds.Variables, ds.Sites[0].SiteID);
foreach (VariableInfoType variable in ds.Variables.Rows)
{
ds.DataValues.Rows.Add(getDataValues(ds, BeginDateTime.DateTime, BeginDateTime.DateTime));
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID, W3CDateTime BeginDateTime,
W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//if (!siteID.HasValue || !VariableID.HasValue) return ds;
//DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
//valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
//valuesTableAdapter.FillBySiteIdVariableIDBetweenDates(ds.DataValues, siteID.Value, VariableID.Value, BeginDateTime.DateTime, EndDateTime.DateTime);
//return ds;
siteAdapter.FillBySiteID(ds.Sites, siteID.Value);
vAdapter.FillByVariableID(ds.Variables, VariableID.Value);
return(getDataValues(ds, BeginDateTime.DateTime, EndDateTime.DateTime));
}
public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//if (!siteID.HasValue || !VariableID.HasValue) return ds;
//DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
//valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
//valuesTableAdapter.FillBySiteIDVariableID(ds.DataValues, siteID.Value, VariableID.Value);
//return ds;
siteAdapter.FillBySiteID(ds.Sites, siteID.Value);
vAdapter.FillBySiteID(ds.Variables, VariableID.Value);
return(getDataValues(ds, new DateTime(1900, 01, 01), DateTime.Now));
}
static void PrintTable(SitesTableAdapter stAdapter, OD_1_1_DataSet.SitesDataTable dt)
{
stAdapter.Fill(dt);
// Print out the column names.
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName + "\t");
}
Console.WriteLine("\n----------------------------------");
// Print the DataTable.
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
}
Console.WriteLine();
}
}
/*
* newTable
* creates the Data Table required by rest of program to create the XML form
*/
private ValuesDataSet newTable(seriesCatalogDataSet.SeriesCatalogDataTable series, ref DataSet oDS)
{
ValuesDataSet ds = basicValuesDataSet();
DataTable variableTable = ds.DataValues;
SitesTableAdapter sitesAdapter = new SitesTableAdapter();
siteInfoDataSet sites = new siteInfoDataSet();
sitesAdapter.Connection.ConnectionString = odws.Config.ODDB();
sitesAdapter.FillBySiteID(sites.sites, series[0].SiteID);
int ValueID = 0;
DataRow[] rows = oDS.Tables["Variables"].Select();
//DataRow siteTable = sites.sites[0];
foreach (DataRow row in rows)
{
ValueID++;
int timezone = sites.sites[0].TimeZone;
variableTable.Rows.Add(ValueID, series[0].SiteID, series[0].VariableID, DBNull.Value, DBNull.Value, "nc", 0, series[0].MethodID, series[0].SourceID, 0, 0, series[0].QualityControlLevelID, (row[0] == "") ? (-9999) : row[series[0].VariableCode.ToLower()], 0, convertDate(row[0].ToString(), 0), convertDate(row[0].ToString(), timezone), timezone);
//"ValueID","SiteID","VariableID",""OffsetValue", "OffsetTypeID","CensorCode","QualifierID","MethodID","SourceID","SampleID","DerivedFromID","QualityControlLevelID", "Value", "AccuracyStdDev","DateTime", "DateTimeUTC", "UTCOffset"*/
}
return(ds);
}
private DataSet getValuesWork(seriesCatalogDataSet.SeriesCatalogDataTable series, DateTime startDate, DateTime endDate)
{
string siteCode = series[0].SiteCode;
string curryear = ((Convert.ToInt32(getCurrWaterYear()) - 1).ToString()).Substring(2);
int endresult = endDate.CompareTo(series[0].EndDateTime);
int startresult = startDate.CompareTo(series[0].BeginDateTime);
/*Less than zero This instance is earlier than value.
* begin: change to values[0].
* end: use enddatetime*/
/*check to see if the start date requested is before or after the start datd in the
* database. if after use given date, if before use date in database.*/
DateTime startdate;
if (startresult < 0)
{
startdate = (series[0].BeginDateTime);
}
else
{
startdate = startDate;
}
/*check to see if the end date requested is before or after the end datd in the
* database. if it is after after use date in database, if before use given date. */
DateTime enddate;
if (endresult < 0)
{
enddate = endDate;
}
else
{
enddate = ((DateTime)series[0].EndDateTime);
}
string endOfYear = "0930" + curryear;
DateTime eoy = convertDate(endOfYear, 0);
//reset result to get data for which files to open
endresult = DateTime.Compare(eoy, endDate);
startresult = DateTime.Compare(eoy, startDate);
SitesTableAdapter sitesAdapter = new SitesTableAdapter();
siteInfoDataSet sites = new siteInfoDataSet();
sitesAdapter.Connection.ConnectionString = odws.Config.ODDB();
sitesAdapter.FillBySiteID(sites.sites, series[0].SiteID);
String state = sites.sites[0].State.ToLower().Trim().Replace(' ', '_');
String ftpAll = "ftp://ftp.wcc.nrcs.usda.gov/data/snow/snotel/cards/{0}/{1}_all.txt";
String ftpYear = "ftp://ftp.wcc.nrcs.usda.gov/data/snow/snotel/cards/{0}/{1}_{2}.tab";
//case 1: all dates are in the 'archive' file
if (startresult >= 0 && endresult > 0)
{
WebRequest[] objRequest = new WebRequest[1];
objRequest[0] = WebRequest.Create(String.Format(ftpAll, state.ToLower(), siteCode.ToLower()));
return(fillTable(objRequest, startdate.ToString(dateFormat), enddate.ToString(dateFormat)));
}
//case 2: some of the dates are in the 'archiv'e file and the others are in the 'current' file
else if (startresult >= 0 && endresult < 0)
{
WebRequest[] objRequest = new WebRequest[2];
objRequest[0] = WebRequest.Create(String.Format(ftpAll, state.ToLower(), siteCode.ToLower()));
objRequest[1] = WebRequest.Create(String.Format(ftpYear, state.ToLower(), siteCode.ToLower(), getCurrWaterYear()));
return(fillTable(objRequest, startdate.ToString(dateFormat), enddate.ToString(dateFormat)));
}
//case 3: all dates are in the 'current' file
else//startresult>=0&& endresult>=0
{
WebRequest[] objRequest = new WebRequest[1];
objRequest[0] = WebRequest.Create(String.Format(ftpYear, state.ToLower(), siteCode.ToLower(), getCurrWaterYear()));
return(fillTable(objRequest, startdate.ToString(dateFormat), enddate.ToString(dateFormat)));
}
}
