public void SaveVariable(IVariable varb)
{
string sql = "";
sql = string.Format("select * from Variables where VariableID={0}", varb.VariableID);
if (ODMDB.Exists(sql))
{
sql = string.Format("update Variables set VariableCode='{1}', VariableName='{2}', Specification='{3}', ValueType='{4}', DataType='{5}', SampleMedium='{6}',"+
"TimeUnitsID={7}, VariableUnitsID={8}, TimeSupport={9}, GeneralCategory='{10}', NoDataValue='{11}')"+
" where VariableID={0}",
varb.VariableID, varb.Code, varb.Name, varb.Specification, varb.ValueType, varb.DataType, varb.SampleMedium, varb.TimeUnitsID,
varb.VariableUnitsID, varb.TimeSupport, varb.GeneralCategory, varb.NoDataValue
);
}
else
{
//VariableID VariableCode VariableName Specification ValueType DataType SampleMedium TimeUnitsID VariableUnitsID TimeSupport GeneralCategory NoDataValue
sql = string.Format("insert into Variables (VariableID VariableCode VariableName Specification ValueType DataType SampleMedium"+
"TimeUnitsID VariableUnitsID TimeSupport GeneralCategory NoDataValue)"+
"values ({0}, '{1}', '{2}','{3}', '{4}', '{5}','{6}', {7}, {8},{9}, '{10}',{11})",
varb.VariableID, varb.Code, varb.Name, varb.Specification, varb.ValueType, varb.DataType, varb.SampleMedium, varb.TimeUnitsID,
varb.VariableUnitsID, varb.TimeSupport, varb.GeneralCategory, varb.NoDataValue);
}
ODMDB.CreateNonQueryCommand(sql);
}
public virtual void SaveDataValues(IObservationsSite[] site, int varID)
{
string sql = "";
foreach (var s in site)
{
var ts = s.TimeSeries;
// update DataValue table
for (int d = 0; d < s.TimeSeries.DateTimes.Length; d++)
{
sql = string.Format("select * from DataValues where SiteID={0} and VariableID = {1} and DateTimeUTC= #{2}#", s.ID, varID, s.TimeSeries.DateTimes[d].ToString("yyyy/MM/dd"));
if (ODMDB.Exists(sql))
{
// sql = string.Format("delete * from DataValues where SiteID={0} and VariableID = {1} and DateTimeUTC= #{2}#", s.ID, varID, s.TimeSeries.DateTimeVector[d].ToString("yyyy/MM/dd"));
sql = string.Format("update DataValues set datavalue={0} where SiteID={1} and variableid={2} and datetimeutc=#{3}#", ts[0, d, 0],
s.ID, varID, s.TimeSeries.DateTimes[d].ToString("yyyy/MM/dd HH:mm:ss"));
ODMDB.CreateNonQueryCommand(sql);
}
else
{
sql = string.Format("insert into DataValues (SiteID,VariableID,DateTimeUTC,DataValue) values ({0}, {1}, #{2}#,{3})",
s.ID, varID, s.TimeSeries.DateTimes[d].ToString("yyyy/MM/dd HH:mm:ss"), ts[0, d, 0]);
ODMDB.CreateNonQueryCommand(sql);
}
}
// update SeriesCatalog table
sql = string.Format("select * from SeriesCatalog where SiteID={0} and VariableID = {1}", s.ID, varID);
if (ODMDB.Exists(sql))
{
sql = string.Format("delete * from SeriesCatalog where SiteID={0} and VariableID = {1}", s.ID, varID);
ODMDB.CreateNonQueryCommand(sql);
}
sql = string.Format("select DateTimeUTC from DataValues where SiteID={0} and VariableID = {1}", s.ID, varID);
var dt = ODMDB.QueryDataTable(sql);
if (dt != null)
{
var dates = from r in dt.AsEnumerable() select r.Field <DateTime>("DateTimeUTC");
sql = string.Format("insert into SeriesCatalog (SiteID,VariableID,BeginDateTime,EndDateTime,ValueCount) values ({0}, {1}, #{2}#, #{3}#, {4})",
s.ID, varID, dates.Min().ToString("yyyy/MM/dd"), dates.Max().ToString("yyyy/MM/dd"), dates.Count());
ODMDB.CreateNonQueryCommand(sql);
}
}
}
public virtual void SaveDataValue(Site site, int varID, DateTime date, double datavalue)
{
string sql = string.Format("select * from DataValues where SiteID={0} and VariableID = {1} and DateTimeUTC= #{2}#", site.ID, varID, date.ToString("yyyy/MM/dd"));
if (ODMDB.Exists(sql))
{
// sql = string.Format("delete * from DataValues where SiteID={0} and VariableID = {1} and DateTimeUTC= #{2}#", s.ID, varID, s.TimeSeries.DateTimeVector[d].ToString("yyyy/MM/dd"));
sql = string.Format("update DataValues set datavalue={0} where SiteID={1} and variableid={2} and datetimeutc=#{3}#", datavalue,
site.ID, varID, date.ToString("yyyy/MM/dd HH:mm:ss"));
ODMDB.CreateNonQueryCommand(sql);
}
else
{
sql = string.Format("insert into DataValues (SiteID,VariableID,DateTimeUTC,DataValue) values ({0}, {1}, #{2}#,{3})",
site.ID, varID, date.ToString("yyyy/MM/dd HH:mm:ss"), datavalue);
ODMDB.CreateNonQueryCommand(sql);
}
}
public void UpdateSeriesCatalog()
{
string sql = "SELECT DISTINCT siteid AS sid, variableid AS vid, datetimeutc AS dt FROM datavalues;";
var dt = ODMDB.QueryDataTable(sql);
var dten = dt.AsEnumerable();
var buf = (from dr in dten select dr.Field <int>("sid")).Distinct();
int progress = 0;
int total = buf.Count();
int i = 1;
foreach (var s in buf)
{
var varb = from dr in dten
where dr.Field <int>("sid") == s
group dr by dr.Field <int>("vid") into m
select new
{
vid = m.Key,
min = m.Select(n => n.Field <DateTime>("dt")).Min(),
max = m.Select(n => n.Field <DateTime>("dt")).Max(),
count = m.Select(n => n.Field <DateTime>("dt")).Count(),
};
foreach (var vb in varb)
{
sql = string.Format("select * from SeriesCatalog where SiteID={0} and VariableID = {1}", s, vb.vid);
if (ODMDB.Exists(sql))
{
sql = string.Format("delete * from SeriesCatalog where SiteID={0} and VariableID = {1}", s, vb.vid);
ODMDB.CreateNonQueryCommand(sql);
}
sql = string.Format("insert into SeriesCatalog (SiteID,VariableID,BeginDateTime,EndDateTime,ValueCount) values ({0}, {1}, #{2}#, #{3}#, {4})",
s, vb.vid, vb.min.ToString("yyyy/MM/dd HH:mm:ss"), vb.max.ToString("yyyy/MM/dd HH:mm:ss"), vb.count);
ODMDB.CreateNonQueryCommand(sql);
}
progress = i * 100 / total;
i++;
OnProgressChanged(progress);
}
OnFinished();
}
public void SaveSite(IObservationsSite s)
{
string sql = "";
sql = string.Format("select * from Sites where SiteID={0}", s.ID);
if (ODMDB.Exists(sql))
{
sql = string.Format("update Sites set SiteName='{1}', SiteCode='{2}', SiteType='{3}',Longitude={4},Latitude={5},Elevation_m={6}," +
"State='{7}',Country='{8}',MonitorType={9},Comments='{10}' where SiteID={0}",
s.ID, s.Name, s.Code, s.SiteType, s.Longitude, s.Latitude, s.Elevation, s.State, s.Country, s.MonitorType, s.Comments
);
}
else
{
sql = string.Format("insert into Sites (SiteID, SiteName,SiteCode,SiteType,Longitude,Latitude,Elevation_m,State,Country,MonitorType,Comments)" +
"values ({0}, '{1}', '{2}','{3}', {4}, {5},{6}, '{7}', '{8}','{9}', '{10}')",
s.ID, s.Name, s.Code, s.SiteType, s.Longitude, s.Latitude, s.Elevation, s.State, s.Country, s.MonitorType, s.Comments);
}
ODMDB.CreateNonQueryCommand(sql);
}