private static void CreateMeasurementsCsv(CsvDb db, Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict,
Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList)
{
string[] cols = new string[] { "DataKey", "Deployment", "DeployDesc", "SamplingEventKey", "SiteKey", "DeployStart",
"DeployEnd", "SampleDate", "SurfaceElevation", "Temperature", "pH", "DissolvedOxygen", "Conductivity", "Salinity", "Velocity" };
ITable measurements = db.Create("WaterQualityMeasurements", cols);
var orderedMeasurements = measurementList.OrderBy(x => x.Item1);
foreach (var meas in orderedMeasurements)
{
WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2;
WaterQualityMeasurement measurement = meas.Item2;
int eventIndex = eventDict[deploy.SampleEventId].Item1;
//deploy.SiteId could be a dangling reference
string siteFK = na;
if (siteDict.ContainsKey(deploy.SiteId))
{
siteFK = siteDict[deploy.SiteId].Item1.ToString();
}
IRow r = measurements.CreateRow();
r[0] = meas.Item1.ToString();
r[1] = deploy.Name;
r[2] = deploy.Description;
r[3] = eventIndex.ToString();
r[4] = siteFK;
r[5] = deploy.Range.StartDate.ToString();
r[6] = deploy.Range.EndDate.ToString();
r[7] = measurement.SampleDate.ToString();
r[8] = measurement.SurfaceElevation.ToString();
r[9] = measurement.Temperature.ToString();
r[10] = measurement.pH.ToString();
r[11] = measurement.DissolvedOxygen.ToString();
r[12] = measurement.Conductivity.ToString();
r[13] = measurement.Salinity.ToString();
r[14] = measurement.Velocity.ToString();
measurements.AddRow(r);
}
measurements.Flush();
return;
}
public bool Delete(WaterQualityMeasurement item)
{
if (item != null && this.CanDelete(item))
{
try
{
NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString);
cmd.CommandText = Db.DeleteMeasurement;
cmd.Parameters.AddWithValue("dsid", item.DeploymentId.DataStoreIdentity);
cmd.Parameters.AddWithValue("did", item.DeploymentId.Identity);
Db.ExecuteNonQuery(cmd);
return(true);
}
catch
{ }
}
return(false);
}
public IEnumerable <WaterQualityMeasurement> Get(CompoundIdentity deploymentId)
{
if (!deploymentId.IsNullOrEmpty() && this.CanGet())
{
NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString);
cmd.CommandText = Db.SelectMeasurement + Db.Where + Db.WhereDeploy;
cmd.Parameters.AddWithValue("dsid", deploymentId.DataStoreIdentity);
cmd.Parameters.AddWithValue("did", deploymentId.Identity);
NpgsqlDataReader rdr = Db.ExecuteReader(cmd);
WaterQualityMeasurement o = null;
List <WaterQualityMeasurement> permissions = new List <WaterQualityMeasurement>();
if (rdr != null)
{
try
{
while (rdr.Read())
{
o = this.builder.Build(rdr);
if (o != null)
{
permissions.Add(o);
}
}
if (cmd.Connection.State == System.Data.ConnectionState.Open)
{
cmd.Connection.Close();
}
}
catch
{ }
finally
{
cmd.Dispose();
}
}
return(permissions);
}
return(null);
}
public bool CanDelete(WaterQualityMeasurement item)
{
if (item != null && this.CanDelete())
{
if (!item.DeploymentId.Equals(lastDeploymentId))
{
if (this.helperProvider == null)
{
this.helperProvider = PgWaterQualityProviderFactory.Instance.GetDeploymentProvider(this.Context);
}
if (this.helperProvider != null)
{
WaterQualityDeployment depl = this.helperProvider.Get(item.DeploymentId);
if (depl != null)
{
CompoundIdentity se = depl.SampleEventId;
if (se != this.lastSampleEventId)
{
UserProvider prov = UserAffilationSecurityManager.Instance.GetProvider(this.Context);
if (prov != null)
{
this.lastEditPermission = prov.HasAffiliationForSampleEvent(se, true);
this.lastGetPermission = prov.HasAffiliationForSampleEvent(se, false);
this.lastSampleEventId = se;
this.lastDeploymentId = item.DeploymentId;
}
}
else //same sample event, different deployment
{
this.lastDeploymentId = item.DeploymentId; //don't need to re-check the sampleevent controls permission
}
}
}
}
}
return(false);
}
public static JObject ToJson(WaterQualityMeasurement measurement)
{
if (measurement != null)
{
JObject o = new JObject();
o.Add("deploymentid", JsonUtils.ToJson(measurement.DeploymentId));
o.Add("sampledate", measurement.SampleDate);
o.Add("surfaceelevation", measurement.SurfaceElevation);
if (measurement.Temperature != null)
{
o.Add("temperature", measurement.Temperature);
}
if (measurement.pH != null)
{
o.Add("ph", measurement.pH);
}
if (measurement.DissolvedOxygen != null)
{
o.Add("dissolvedoxygen", measurement.DissolvedOxygen);
}
if (measurement.Conductivity != null)
{
o.Add("conductivity", measurement.Conductivity);
}
if (measurement.Salinity != null)
{
o.Add("salinity", measurement.Salinity);
}
if (measurement.Velocity != null)
{
o.Add("velocity", measurement.Velocity);
}
return(o);
}
return(null);
}
private Guid CreateExcelFile(Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict,
Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList,
Dictionary <CompoundIdentity, Organization> orgDict, Dictionary <CompoundIdentity, FieldTrip> fieldTripDict,
Dictionary <CompoundIdentity, FieldActivity> fieldActivityDict, Dictionary <CompoundIdentity, Project> projectDict)
{
IFileStoreProvider provider = FileStoreManager.Instance.GetProvider();
//Setting up file and Excel Workbook
FilestoreFile deployFile = provider.MakeTemp(DateTime.UtcNow.AddHours(4));
XlWorkbook book = new XlWorkbook();
XlWorksheets sheets = book.Worksheets;
//Generating Sampling Event Sheet
XlSchema eventSchema = GetSampleEventSchema();
XlWorksheet eventSheet = sheets.AddWorksheet("SamplingEvents", XlColor.White, eventSchema);
XlRows eventRows = eventSheet.Rows;
var orderedEvents = eventDict.OrderBy(x => x.Value.Item1);
foreach (var evt in orderedEvents)
{
string orgName = na;
if (orgDict.ContainsKey(evt.Value.Item2.PrincipalOrgId))
{
orgName = orgDict[evt.Value.Item2.PrincipalOrgId].Name;
}
string ftripName = na;
string factivityName = na;
string projName = na;
if (fieldTripDict.ContainsKey(evt.Value.Item2.FieldTripId))
{
FieldTrip ftrip = fieldTripDict[evt.Value.Item2.FieldTripId];
ftripName = ftrip.Name;
if (fieldActivityDict.ContainsKey(ftrip.FieldActivityId))
{
FieldActivity factivity = fieldActivityDict[ftrip.FieldActivityId];
factivityName = factivity.Name;
if (projectDict.ContainsKey(factivity.ProjectId))
{
projName = projectDict[factivity.ProjectId].Name;
}
}
}
List <string> evtItems = new List <string>();
evtItems.Add(orgName);
evtItems.Add(projName);
evtItems.Add(factivityName);
evtItems.Add(ftripName);
evtItems.Add(evt.Value.Item2.Name);
evtItems.Add(evt.Value.Item1.ToString());
evtItems.Add(evt.Value.Item2.Description);
//evtItems.Add(evt.Value.Item2.DateRange.Min.ToString());
//evtItems.Add(evt.Value.Item2.DateRange.Max.ToString());
SchemaRowData row = new SchemaRowData(eventSchema, evtItems);
eventRows.AddRow(row);
}
//Generating Deployment/Measurement Sheet
XlSchema measSchema = GetDeployMeasurementSchema();
XlWorksheet measSheet = sheets.AddWorksheet("WaterQualityMeasurements", XlColor.White, measSchema);
XlRows measRows = measSheet.Rows;
var orderedMeasurements = measurementList.OrderBy(x => x.Item1);
foreach (var meas in orderedMeasurements)
{
WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2;
WaterQualityMeasurement measurement = meas.Item2;
int eventIndex = eventDict[deploy.SampleEventId].Item1;
//deploy.SiteId could be a dangling reference
string siteFK = na;
if (siteDict.ContainsKey(deploy.SiteId))
{
siteFK = siteDict[deploy.SiteId].Item1.ToString();
}
List <string> measItems = new List <string>();
measItems.Add(meas.Item1.ToString());
measItems.Add(deploy.Name);
measItems.Add(deploy.Description);
measItems.Add(eventIndex.ToString());
measItems.Add(siteFK);
measItems.Add(deploy.Range.StartDate.ToString());
measItems.Add(deploy.Range.EndDate.ToString());
measItems.Add(measurement.SampleDate.ToString());
measItems.Add(measurement.SurfaceElevation.ToString());
measItems.Add(measurement.Temperature.ToString());
measItems.Add(measurement.pH.ToString());
measItems.Add(measurement.DissolvedOxygen.ToString());
measItems.Add(measurement.Conductivity.ToString());
measItems.Add(measurement.Salinity.ToString());
measItems.Add(measurement.Velocity.ToString());
SchemaRowData row = new SchemaRowData(measSchema, measItems);
measRows.AddRow(row);
}
//Generating Site Sheet
XlSchema siteSchema = GetSiteSchema();
XlWorksheet siteSheet = sheets.AddWorksheet("Sites", XlColor.White, siteSchema);
XlRows siteRows = siteSheet.Rows;
var orderedSites = siteDict.OrderBy(x => x.Value.Item1);
foreach (var site in orderedSites)
{
Site s = site.Value.Item2;
List <string> siteItems = new List <string>();
siteItems.Add(site.Value.Item1.ToString());
siteItems.Add(s.Name);
siteItems.Add(s.Description);
IGeometry2 <double> geom = s.Location;
if (geom != null)
{
if (geom is PolygonBag2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as PolygonBag2 <double>).ToString());
}
else if (geom is Polygon2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Polygon2 <double>).ToString());
}
else if (geom is Polyline2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Polyline2 <double>).ToString());
}
else if (geom is PolylineBag2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as PolylineBag2 <double>).ToString());
}
else if (geom is Point2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Point2 <double>).ToString());
}
}
else
{
siteItems.Add("");
}
Point2 <double> geom2 = s.LocationMark;
if (geom2 != null)
{
siteItems.Add(WktUtils.ToWkt(geom2 as Point2 <double>).ToString());
}
else
{
siteItems.Add("");
}
SchemaRowData row = new SchemaRowData(siteSchema, siteItems);
siteRows.AddRow(row);
}
book.Save(deployFile);
deployFile.Flush();
deployFile.Close();
deployFile.Dispose();
return(deployFile.FileId);
}
public IEnumerable <WaterQualityMeasurement> Get(CompoundIdentity deploymentId, DateTime start, DateTime end)
{
if (!deploymentId.IsNullOrEmpty() && this.CanGet())
{
DateTime now = DateTime.UtcNow;
string sql = null;
if (start > WQUtils.GlobalMinDate)
{
sql = Db.Where + Db.WhereStart;
}
if (end < now)
{
if (sql == null)
{
sql = Db.Where + Db.WhereEnd;
}
else //already have start
{
sql = sql + " AND " + Db.WhereEnd;
}
}
if (sql != null)
{
NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString);
cmd.CommandText = Db.SelectMeasurement + sql + " AND " + Db.WhereDeploy;
cmd.Parameters.AddWithValue("dsid", deploymentId.DataStoreIdentity);
cmd.Parameters.AddWithValue("did", deploymentId.Identity);
if (start > WQUtils.GlobalMinDate)
{
cmd.Parameters.AddWithValue("start", start);
}
if (end < now)
{
cmd.Parameters.AddWithValue("end", end);
}
NpgsqlDataReader rdr = Db.ExecuteReader(cmd);
WaterQualityMeasurement o = null;
List <WaterQualityMeasurement> permissions = new List <WaterQualityMeasurement>();
if (rdr != null)
{
try
{
while (rdr.Read())
{
o = this.builder.Build(rdr);
if (o != null)
{
permissions.Add(o);
}
}
if (cmd.Connection.State == System.Data.ConnectionState.Open)
{
cmd.Connection.Close();
}
}
catch
{ }
finally
{
cmd.Dispose();
}
}
return(permissions);
}
}
return(null);
}
