//check allseries
public bool isNewSeries(int id, int varID)
{
try
{
SeriesCatalog sc = (from r in this.SeriesCatalog where r.SiteID == id && r.VariableID == varID select r).First();
return(false);
}
catch //(Exception e)
{
return(true);
}
}
public System.Linq.IQueryable <DataValue> GetListDataValues(int seriesID, DateTime startDate, DateTime endDate)
{
try
{
SeriesCatalog s = (from sc in this.SeriesCatalogs where sc.SeriesID == seriesID select sc).First();
return(from d in this.DataValues where d.Site.SiteID == s.SiteID && d.Variable.VariableID == s.VariableID && d.LocalDateTime >= startDate && d.LocalDateTime <= endDate orderby d.LocalDateTime select d);
}
catch (Exception ex)
{
throw new Exception("clsFromDB.GetListDataValues(" + seriesID + ", " + startDate + ", " + endDate + ")", ex);
}
}
public int saveSeries(int siteID, string siteCode, string siteName, int varID, string varCode, string varName, string speciation, int varUnitsID,
string varUnitsName, string sampleMedium, string valType, int timeSupport, int timeUnitsID, string timeUnitsName, string dataType, string generalCategory,
int methodID, string methodDescription, int sourceID, string organization, string sourceDescription, string citation, int qclID,
string qclCode, DateTime beginDT, DateTime endDT, DateTime beginDTUTC, DateTime endDTUTC, int valCount)
{
SeriesCatalog sc;
try
{
sc = (from S in this.SeriesCatalog where S.SiteCode == siteCode && S.VariableCode == varCode && S.MethodID == methodID select S).First();
sc.EndDateTime = endDT;
sc.EndDateTimeUTC = endDTUTC;
sc.ValueCount = sc.ValueCount + valCount;
this.SaveChanges();
}
catch
{
sc = new SeriesCatalog();
sc.SiteID = siteID;
sc.SiteCode = siteCode;
sc.SiteName = siteName;
sc.VariableID = varID;
sc.VariableName = varName;
sc.VariableCode = varCode;
sc.Speciation = speciation;
sc.VariableUnitsID = varUnitsID;
sc.VariableUnitsName = varUnitsName;
sc.SampleMedium = sampleMedium;
sc.ValueType = valType;
sc.TimeSupport = timeSupport;
sc.TimeUnitsID = timeUnitsID;
sc.TimeUnitsName = timeUnitsName;
sc.DataType = dataType;
sc.GeneralCategory = generalCategory;
sc.MethodID = methodID;
sc.MethodDescription = methodDescription;
sc.SourceID = sourceID;
sc.Organization = organization;
sc.SourceDescription = sourceDescription;
sc.Citation = citation;
sc.QualityControlLevelID = qclID;
sc.QualityControlLevelCode = qclCode;
sc.BeginDateTime = beginDT;
sc.BeginDateTimeUTC = beginDTUTC;
sc.EndDateTime = endDT;
sc.EndDateTimeUTC = endDTUTC;
sc.ValueCount = valCount;
this.AddToSeriesCatalog(sc);
this.SaveChanges();
}
return(sc.SeriesID);
}
public int saveDataValues(DataRow[] Rows, Variables variable, sitedata site)
{
DataTable values = createDVTable("DataValues");
int count = 0;
try
{
foreach (DataRow row in Rows)
{
DateTime localDateTime;
localDateTime = (DateTime)row["Date"];
double utcOffset = -7;
int qualifierID;
try
{
string qualCode = row["MFlag"].ToString() + row["QFlag"].ToString() + row["SFlag"].ToString();
qualCode = qualCode.Replace(' ', '_');
qualifierID = GetQualifierID(qualCode);
}
catch
{
qualifierID = -99;
}
DateTime dateTimeUTC = localDateTime.Add(new TimeSpan((int)utcOffset, 0, 0));
try
{
SeriesCatalog sc = (from series in this.SeriesCatalog where series.SiteID == site.SiteID && series.VariableID == variable.VariableID select series).First();
values.Rows.Add(SqlInt64.Null, Convert.ToDouble(row["Value"]), SqlDouble.Null, localDateTime, utcOffset, dateTimeUTC, site.SiteID, variable.VariableID, SqlDouble.Null, SqlInt32.Null, "nc", (qualifierID < 0 ? SqlInt32.Null : qualifierID), sc.MethodID, sc.SourceID, SqlInt32.Null, SqlInt32.Null, sc.QualityControlLevelID);
count++;
}
catch (Exception ex)
{
values.Rows.Add(SqlInt64.Null, Convert.ToDouble(row["Value"]), SqlDouble.Null, localDateTime, utcOffset, dateTimeUTC, site.SiteID, variable.VariableID, SqlDouble.Null, SqlInt32.Null, "nc", (qualifierID < 0 ? SqlInt32.Null : qualifierID), 0, 1, SqlInt32.Null, SqlInt32.Null, 1);
count++;
}
}
this.InsertBulk(values);
}
catch (Exception ex)
{
throw new Exception("clsDatabase.SaveSeries( DataTable, " + site.SiteID + ", " + variable.VariableID + ") on row " + count, ex);
}
return(count);
}
//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);
}
}
private void btnConectODM_Click(object sender, EventArgs e)
{
if (viewModel.Value.ODMSource != null)
{
if (_SeriesCatalog == null)
{
_SeriesCatalog = new SeriesCatalog(viewModel.Value.ODMSource);
btnRefresh_Click(null, null);
}
else
{
MessageBox.Show("An ODM database has been connected. If you want to connect to another database, please removec connection to current database at first"
, "Database Connection", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
OpenFileDialog form = new OpenFileDialog();
form.Filter = "access 2003 file |*.mdb|access 2007-2013 file|*.accdb";
if (form.ShowDialog() == DialogResult.OK)
{
ODMSource odm = new ODMSource();
string msg = "";
if (odm.Open(form.FileName, ref msg))
{
viewModel.Value.ProjectService.Project.ODMSource = odm;
viewModel.Value.ODMSource = odm;
_SeriesCatalog = new SeriesCatalog(odm);
btnRefresh_Click(null, null);
}
else
{
MessageBox.Show("Failed to connect to the database. Error message: " + msg, "Database Connection", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
}
//insert series
public void insertSeries(int siteID, string siteCode, string siteName, int variableID, string variableCode, string variableName, string speciation, int variableUnitsID, string variableUnitsName, string sampleMedium, string valueType, double timeSupport, int timeUnitsID, string timeUnitsName, string dataType, string generalCategory, int methodID, string methodDescription, int sourceID, string organization, string sourceDescription, string citation, int qualityControlLevelID, string qualityControlLevelCode, DateTime beginDateTime, DateTime endDateTime, DateTime beginDateTimeUTC, DateTime endDateTimeUTC, int valueCount)
{
SeriesCatalog sc = new SeriesCatalog();
sc.SiteID = siteID;
sc.SiteCode = siteCode;
sc.SiteName = siteName;
sc.VariableID = variableID;
sc.VariableCode = variableCode;
sc.VariableName = variableName;
sc.Speciation = speciation;
sc.VariableUnitsID = variableUnitsID;
sc.VariableUnitsName = variableUnitsName;
sc.SampleMedium = sampleMedium;
sc.ValueType = valueType;
sc.TimeSupport = timeSupport;
sc.TimeUnitsID = timeUnitsID;
sc.TimeUnitsName = timeUnitsName;
sc.DataType = dataType;
sc.GeneralCategory = generalCategory;
sc.MethodID = methodID;
sc.MethodDescription = methodDescription;
sc.SourceID = sourceID;
sc.Organization = organization;
sc.SourceDescription = sourceDescription;
sc.Citation = citation;
sc.QualityControlLevelID = qualityControlLevelID;
sc.QualityControlLevelCode = qualityControlLevelCode;
sc.BeginDateTime = beginDateTime;
sc.EndDateTime = endDateTime;
sc.BeginDateTimeUTC = beginDateTimeUTC;
sc.EndDateTimeUTC = endDateTimeUTC;
sc.ValueCount = valueCount;
this.AddToSeriesCatalog(sc);
this.SaveChanges();
}
public static SeriesCatalog CreateSeriesCatalog(int seriesID)
{
SeriesCatalog seriesCatalog = new SeriesCatalog();
seriesCatalog.SeriesID = seriesID;
return seriesCatalog;
}
public void AddToSeriesCatalog(SeriesCatalog seriesCatalog)
{
base.AddObject("SeriesCatalog", seriesCatalog);
}
public int saveSeries(Variable variable, Site site, DateTime beginDT, DateTime endDT, int valCount, string vunitname)
{
SeriesCatalog sc;
try
{
sc = (from SC in this.SeriesCatalog where SC.VariableName == variable.VariableName && SC.SiteCode == site.SiteCode select SC).First();
//sc.BeginDateTime = beginDT;
//sc.BeginDateTimeUTC = beginDT.ToUniversalTime();
sc.EndDateTime = endDT;
sc.EndDateTimeUTC = endDT.ToUniversalTime();
TimeSpan val = sc.EndDateTime.Value.Subtract(sc.BeginDateTime.Value);
sc.ValueCount = val.Days;//sc.ValueCount + valCount;
this.SaveChanges();
}
catch (Exception ex)
{
Sites s = (from S in this.Sites where S.SiteCode == site.SiteCode select S).First();
Variables v = (from V in this.Variables where V.VariableName == variable.VariableName select V).First();
Methods m = (from M in this.Methods where M.MethodID == 0 select M).First();
Sources so = (from S in this.Sources where S.SourceID == 1 select S).First();
QualityControlLevels qcl = (from Q in this.QualityControlLevels where Q.QualityControlLevelID == -9999 select Q).First();
Units tU = (from U in this.Units where U.UnitsID == 104 select U).First();
Units vU = (from U in this.Units where U.UnitsName == vunitname select U).First();;
sc = new SeriesCatalog();
sc.SiteID = s.SiteID;
sc.SiteCode = s.SiteCode;
sc.SiteName = s.SiteName;
sc.VariableID = v.VariableID;
sc.VariableName = v.VariableName;
sc.VariableCode = v.VariableCode;
sc.Speciation = v.Speciation;
sc.VariableUnitsID = vU.UnitsID; //v.Units.UnitsID;
sc.VariableUnitsName = vU.UnitsName; //v.Units.UnitsName;
sc.SampleMedium = v.SampleMedium;
sc.ValueType = v.ValueType;
sc.TimeSupport = v.TimeSupport;
sc.TimeUnitsID = tU.UnitsID; //v.Units1.UnitsID;
sc.TimeUnitsName = tU.UnitsName; //v.Units1.UnitsName;
sc.DataType = v.DataType;
sc.GeneralCategory = v.GeneralCategory;
sc.MethodID = m.MethodID;
sc.MethodDescription = m.MethodDescription;
sc.SourceID = so.SourceID;
sc.Organization = so.Organization;
sc.SourceDescription = so.SourceDescription;
sc.Citation = so.Citation;
sc.QualityControlLevelID = qcl.QualityControlLevelID;
sc.QualityControlLevelCode = qcl.QualityControlLevelCode;
sc.BeginDateTime = beginDT;
sc.BeginDateTimeUTC = beginDT.ToUniversalTime();
sc.EndDateTime = endDT;
sc.EndDateTimeUTC = endDT.ToUniversalTime();
sc.ValueCount = valCount;
this.AddToSeriesCatalog(sc);
this.SaveChanges();
}
return(sc.SeriesID);
}
//public int SaveSeries(int[] meta, int qclid)
//{
// SeriesCatalog sc;
// int siteID = meta[0];
// int methodID = meta[1];
// int varID = meta[2];
// int sourceID = meta[3];
// IQueryable<DataValue> dataList = from DV in this.DataValues where DV.Site.SiteID == siteID && DV.Variable.VariableID == varID && DV.Method.MethodID == methodID && DV.Source.SourceID == sourceID select DV;
// try
// {
// sc = (from S in this.SeriesCatalogs where S.SiteID == siteID && S.VariableID == varID && S.MethodID == methodID && S.SourceID == sourceID select S).First();
// sc.ValueCount = dataList.Count();
// sc.EndDateTime = (from D in dataList select D.LocalDateTime).Max();
// sc.EndDateTimeUTC = (from D in dataList select D.DateTimeUTC).Max();
// this.SaveChanges();
// }
// catch
// {
// sc = new SeriesCatalog();
// DataValue data = dataList.First();
// sc = new SeriesCatalog();
// sc.SiteID = data.Site.SiteID;
// sc.SiteCode = data.Site.SiteCode;
// sc.SiteName = data.Site.SiteName;
// sc.VariableID = data.Variable.VariableID;
// sc.VariableName = data.Variable.VariableName;
// sc.VariableCode = data.Variable.VariableCode;
// sc.Speciation = data.Variable.Speciation;
// sc.VariableUnitsID = data.Variable.Unit.UnitsID;
// sc.VariableUnitsName = this.GetUnits(data.Variable.Unit.UnitsID);
// sc.SampleMedium = data.Variable.SampleMedium;
// sc.ValueType = data.Variable.ValueType;
// sc.TimeSupport = data.Variable.TimeSupport;
// sc.TimeUnitsID = data.Variable.Unit1.UnitsID;
// sc.TimeUnitsName = this.GetUnits(data.Variable.Unit1.UnitsID);
// sc.DataType = data.Variable.DataType;
// sc.GeneralCategory = data.Variable.GeneralCategory;
// sc.MethodID = data.Method.MethodID;
// sc.MethodDescription = data.Method.MethodDescription;
// sc.SourceID = data.Source.SourceID;
// sc.Organization = data.Source.Organization;
// sc.SourceDescription = data.Source.SourceDescription;
// sc.Citation = data.Source.Citation;
// sc.QualityControlLevelID = qclid;
// sc.QualityControlLevelCode = this.GetQCL(qclid);
// sc.BeginDateTime = (from DL in dataList select DL.LocalDateTime).Min();
// sc.BeginDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Min();
// sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max();
// sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max();
// sc.ValueCount = dataList.Count();
// this.AddToSeriesCatalogs(sc);
// this.SaveChanges();
// }
// return sc.SeriesID;
//}
public int SaveSeries(int siteID, L1HarvestList variable)
{
SeriesCatalog sc;
int methodID = variable.MethodID;
int varID = variable.VariableID;
int sourceID = variable.SourceID;
IQueryable <DataValue> dataList = from DV in this.DataValues where DV.Site.SiteID == siteID && DV.Variable.VariableID == varID && DV.Method.MethodID == methodID && DV.Source.SourceID == sourceID select DV;
try
{
// site, Method, variable, Source
sc = (from S in this.SeriesCatalogs where S.SiteID == siteID && S.VariableID == varID && S.MethodID == methodID && S.SourceID == sourceID select S).First();
sc.ValueCount = dataList.Count();
//// sc.BeginDateTime = StartDate;
//// sc.BeginDateTimeUTC = StartDate.ToUniversalTime();
sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max();
sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max();
this.SaveChanges();
}
catch (Exception ex)
{
try
{
DataValue data = dataList.First();
//// DataValues D = (from DV in this.DataValues where DV.ValueID == DataValID select DV).First();
sc = new SeriesCatalog();
if (data.Site != null)
{
sc.SiteID = data.Site.SiteID;
sc.SiteCode = data.Site.SiteCode;
sc.SiteName = data.Site.SiteName;
}
else
{
Site 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;
}
if (data.Variable != null)
{
sc.VariableID = data.Variable.VariableID;
sc.VariableName = data.Variable.VariableName;
sc.VariableCode = data.Variable.VariableCode;
sc.Speciation = data.Variable.Speciation;
//if (data.Variable.Unit != null)
//{
sc.VariableUnitsID = data.Variable.Unit.UnitsID;
sc.VariableUnitsName = data.Variable.Unit.UnitsName;
//}
//else
//{ //Units u = (from U in this.Units where U.UnitsID==1 select U).First();
//sc.VariableUnitsID = Convert.ToInt16(data.Variable UnitsReference.EntityKey.EntityKeyValues[0].Value);
//sc.VariableUnitsName = this.GetUnits(sc.VariableUnitsID.Value);
//}
sc.SampleMedium = data.Variable.SampleMedium;
sc.ValueType = data.Variable.ValueType;
sc.TimeSupport = data.Variable.TimeSupport;
//if (data.Variable.Unit != null)
//{
sc.TimeUnitsID = data.Variable.Unit1.UnitsID;
sc.TimeUnitsName = this.GetUnits(data.Variable.Unit1.UnitsID);
//}
//else
//{ // Units u = (from U in this.Units where U.UnitsID==1 select U).First();
// sc.TimeUnitsID = Convert.ToInt16(data.Variable.Units1Reference.EntityKey.EntityKeyValues[0].Value);
// sc.TimeUnitsName = this.GetUnits(sc.TimeUnitsID.Value);
//}
sc.DataType = data.Variable.DataType;
sc.GeneralCategory = data.Variable.GeneralCategory;
}
else
{
Variable v = (from V in this.Variables where V.VariableID == varID select V).First();
sc.VariableID = v.VariableID;
sc.VariableName = v.VariableName;
sc.VariableCode = v.VariableCode;
sc.Speciation = v.Speciation;
if (v.Unit != null)
{
sc.VariableUnitsID = v.Unit.UnitsID;
sc.VariableUnitsName = this.GetUnits(v.Unit.UnitsID);
}
else
{
sc.VariableUnitsID = Convert.ToInt16(v.UnitReference.EntityKey.EntityKeyValues[0].Value);
sc.VariableUnitsName = this.GetUnits(sc.VariableUnitsID.Value);
}
sc.SampleMedium = v.SampleMedium;
sc.ValueType = v.ValueType;
sc.TimeSupport = v.TimeSupport;
if (v.Unit1 != null)
{
sc.TimeUnitsID = v.Unit1.UnitsID;
sc.TimeUnitsName = v.Unit1.UnitsName;
}
else
{
sc.TimeUnitsID = Convert.ToInt16(v.Unit1Reference.EntityKey.EntityKeyValues[0].Value);
sc.TimeUnitsName = this.GetUnits(sc.TimeUnitsID.Value);
}
sc.DataType = v.DataType;
sc.GeneralCategory = v.GeneralCategory;
}
if (data.Method != null)
{
sc.MethodID = data.Method.MethodID;
sc.MethodDescription = data.Method.MethodDescription;
}
else
{
Method m = (from M in this.Methods where M.MethodID == methodID select M).First();
sc.MethodID = m.MethodID;
sc.MethodDescription = m.MethodDescription;
}
if (data.Source != null)
{
sc.SourceID = data.Source.SourceID;
sc.Organization = data.Source.Organization;
sc.SourceDescription = data.Source.SourceDescription;
sc.Citation = data.Source.Citation;
}
else
{
Source s = (from S in this.Sources where S.SourceID == sourceID select S).First();
sc.SourceID = s.SourceID;
sc.Organization = s.Organization;
sc.SourceDescription = s.SourceDescription;
sc.Citation = s.Citation;
}
sc.QualityControlLevelID = variable.QualityControlLevelID;
sc.QualityControlLevelCode = this.GetQCL(variable.QualityControlLevelID);
sc.BeginDateTime = (from DL in dataList select DL.LocalDateTime).Min();
sc.BeginDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Min();
sc.EndDateTime = (from DL in dataList select DL.LocalDateTime).Max();
sc.EndDateTimeUTC = (from DL in dataList select DL.DateTimeUTC).Max();
sc.ValueCount = dataList.Count();
this.AddToSeriesCatalogs(sc);
this.SaveChanges();
}
catch (Exception exception)
{
throw new Exception("SaveSeries(int " + siteID + ", AggregateSeries " + variable.SiteCode + ")", exception);
}
}
return(sc.SeriesID);
}