/*
* newTable
* creates the Data Table required by rest of program to create the XML form
* use this if no variable or datetime information is provided
*/
//private static ValuesDataSet newTable(int siteID)
//{
// ValuesDataSet ds = basicValuesDataSet();
// DataTable variableTable = ds.Tables[0];
// int ValueID = 0;
// DataRow[] rows = oDS.Tables[0].Select();
// foreach (DataRow row in rows)
// {
// for (int i = 1; i < oDS.Tables["Variables"].Columns.Count; i++)//(string column in oDS.Tables["Variables"].Columns)
// {
// //SqlCommand cmd = new SqlCommand("SELECT MethodID, SourceID, QualityControllevelID, TimeZone, VariableID FROM dbo.SeriesCatalog INNER JOIN dbo.Sites ON dbo.SeriesCatalog.SiteID=dbo.Sites.SiteID WHERE dbo.SeriesCatalog.SiteID = '" + siteID + "' AND dbo.SeriesCatalog.VariableCode= '" + oDS.Tables["Variables"].Columns[i].ColumnName + "'", SnotelConnection);
// //SqlDataReader objReader = cmd.ExecuteReader();
// object[] Values = new object[5];
// // objReader.Read();
// // objReader.GetValues(Values);
// // variableTable.Rows.Add(ValueID, siteID, Values[4], "nc", 0, Values[0], Values[1], 0, 0, Values[2], row[0] == "" ? -9999 : row[Convert.ToInt32(Values[4])], 0, convertDate(row[0].ToString(), 0), convertDate(row[0].ToString(), Convert.ToInt32(Values[3])), Convert.ToInt32(Values[3]));
// ValueID++;
// }
// }
// return ds;
//}
#region odm 1 series based
public static ValuesDataSet GetValuesDataSet(int siteID)
{
ValuesDataSet ds = basicValuesDataSet();
//ValuesDataSetTableAdapters.DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
//valuesTableAdapter.Connection.ConnectionString = Config.ODDB();
//try
//{
// valuesTableAdapter.FillBySiteID(ds.DataValues, siteID);
//}
//catch (Exception e)
//{
// log.Fatal("Cannot retrieve information from database: " + e.Message); //+ valuesTableAdapter.Connection.DataSource
//}
//return ds;
//style 2
//List<string> list = db.getVariablesList(siteID);
//foreach (string var in list)
//{
// ds=getDataValues(db.getSiteCode(siteID), var, new DateTime(1900, 01, 01), DateTime.Now);
// return ds;
//}
//return ds;
siteAdapter.FillBySiteID(ds.Sites, siteID);
vAdapter.FillBySiteID(ds.Variables, siteID);
foreach (VariableInfoType variable in ds.Variables.Rows)
{
ds.DataValues.Rows.Add(getDataValues(ds, new DateTime(1900, 01, 01), DateTime.Now));
}
return(ds);
}
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));
}
/*
* 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)));
}
}