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);
}