public int InsertEvent(Event ev, IEnumerable<EventProperty> properties, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = new Transaction(db))
{
db.Insert(ev);
var geoString = SerializeLocalization(ev.Latitude, ev.Longitude, ev.Altitude);
foreach (var loc in ev.GetLocalisations(login))
{
db.Insert(loc);
if (!string.IsNullOrWhiteSpace(geoString))
{
db.Execute("UPDATE [dbo].[CollectionEventLocalisation] SET geography=GEOGRAPHY::STGeomFromText(@0, 4326) WHERE CollectionEventID=@1 AND LocalisationSystemID=@2", geoString, loc.CollectionEventID, loc.LocalisationSystemID);
}
}
if (properties != null)
foreach (var p in properties)
{
p.CollectionEventID = ev.CollectionEventID;
db.Insert(p);
}
t.Complete();
return ev.CollectionEventID;
}
}
public int InsertIdentificationUnit(IdentificationUnit iu, IEnumerable<IdentificationUnitAnalysis> analyses, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = db.GetTransaction())
{
db.Insert(iu);
db.Insert(iu.GetIdentification(login));
db.Insert(iu.GetGeoAnalysis(login));
var geoString = SerializeLocalization(iu.Latitude, iu.Longitude, iu.Altitude);
if (!string.IsNullOrWhiteSpace(geoString))
{
db.Execute("UPDATE [dbo].[IdentificationUnitGeoAnalysis] SET Geography=GEOGRAPHY::STGeomFromText(@0, 4326) WHERE CollectionSpecimenID=@1 AND IdentificationUnitID=@2", geoString, iu.CollectionSpecimenID, iu.CollectionUnitID);
}
if (analyses != null)
foreach (var a in analyses)
{
a.CollectionUnitID = iu.CollectionUnitID;
a.CollectionSpecimenID = iu.CollectionSpecimenID;
db.Insert(a);
}
t.Complete();
return iu.CollectionUnitID;
}
}
public IEnumerable<EventProperty> PropertiesForEvent(int collectionEventID, UserCredentials login)
{
using (var db = login.GetConnection())
{
return db.Query<EventProperty>("WHERE CollectionEventID=@0", collectionEventID).ToList();
}
}
public IEnumerable<Specimen> SpecimenForEvent(int collectionEventID, UserCredentials login)
{
using (var db = login.GetConnection())
{
return db.Query<Specimen>("WHERE CollectionEventID=@0", collectionEventID).ToList();
}
}
public IEnumerable<Event> EventsByLocality(string locality, UserCredentials login)
{
using (var db = login.GetConnection())
{
return db.Query<Event>("FROM [dbo].[DiversityMobile_EventsForProject] (@0, @1) as [CollectionEvent]", login.ProjectID, locality).Take(15).ToList();
}
}
public EventSeries EventSeriesByID(int collectionSeriesID, UserCredentials login)
{
using (var db = login.GetConnection())
{
return db.Single<EventSeries>(collectionSeriesID);
}
}
public IEnumerable<Model.AnalysisResult> GetAnalysisResultsForProject(int projectID, UserCredentials login)
{
using (var db = login.GetConnection())
{
return analysisResultsForProject(projectID, db).ToList();
}
}
public IEnumerable<AnalysisTaxonomicGroup> GetAnalysisTaxonomicGroupsForProject(int projectID, UserCredentials login)
{
using (var db = login.GetConnection())
{
var atgs = new Queue<AnalysisTaxonomicGroup>(analysisTaxonomicGroupsForProject(projectID,db));
var flattened = new HashSet<AnalysisTaxonomicGroup>();
var analyses = analysesForProject(projectID,db).ToLookup(an => an.AnalysisParentID);
while (atgs.Any())
{
var atg = atgs.Dequeue();
if (flattened.Add(atg)) //added just now -> queue children
{
if (analyses.Contains(atg.AnalysisID))
foreach (var child in analyses[atg.AnalysisID])
atgs.Enqueue(
new AnalysisTaxonomicGroup()
{
AnalysisID = child.AnalysisID,
TaxonomicGroup = atg.TaxonomicGroup
});
}
}
return flattened;
}
}
public IEnumerable<Model.Analysis> GetAnalysesForProject(int projectID, UserCredentials login)
{
using (var db = login.GetConnection())
{
var res = analysesForProject(projectID, db).ToList();
return res;
}
}
public IEnumerable<IdentificationUnitAnalysis> AnalysesForIU(int collectionUnitID, UserCredentials login)
{
using (var db = login.GetConnection())
{
var analyses = db.Query<IdentificationUnitAnalysis>("WHERE IdentificationUnitID=@0", collectionUnitID).ToList();
return analyses;
}
}
public IEnumerable<Model.PropertyValue> DownloadPropertyNames(Property p, int page, UserCredentials login)
{
var propsForUser = propertyListsForUser(login).ToDictionary(pl => pl.PropertyID);
PropertyList list;
if (propsForUser.TryGetValue(p.PropertyID, out list))
{
return loadTablePaged<Model.PropertyValue>(list.Table, page, login.GetConnection(CATALOG_DIVERSITYMOBILE));
}
else
return Enumerable.Empty<Model.PropertyValue>();
}
public IEnumerable<TaxonName> DownloadTaxonList(TaxonList list, int page, UserCredentials login)
{
Diversity db;
if (list.IsPublicList)
{
var taxa = ServiceConfiguration.PublicTaxa;
db = new Diversity(taxa.Login, taxa.Server, taxa.Catalog);
}
else
db = login.GetConnection(CATALOG_DIVERSITYMOBILE);
return loadTablePaged<Model.TaxonName>(list.Table, page, db);
}
public IEnumerable<IdentificationUnit> UnitsForSpecimen(int collectionSpecimenID, UserCredentials login)
{
using (var db = login.GetConnection())
{
var ius = db.Query<IdentificationUnit>("WHERE CollectionSpecimenID=@0", collectionSpecimenID).ToList();
foreach (var iu in ius)
{
AddUnitExternalInformation(iu, db);
}
return ius;
}
}
public IEnumerable<Event> EventsByLocality(string locality, UserCredentials login)
{
using (var db = login.GetConnection())
{
var events = db.Query<Event>("FROM [dbo].[DiversityMobile_EventsForProject] (@0, @1) as [CollectionEvent]", login.ProjectID, locality).Take(50).ToList();
foreach (var ev in events)
{
AddLocalization(ev, db);
}
return events;
}
}
public int InsertSpecimen(Specimen s, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = new Transaction(db))
{
db.Insert(s);
db.Insert(s.GetProject(login.ProjectID));
db.Insert(s.GetAgent(login));
t.Complete();
return s.CollectionSpecimenID;
}
}
public int InsertEventSeries(EventSeries series, IEnumerable<Localization> localizations, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = new Transaction(db))
{
db.Insert(series);
var geoString = SerializeLocalizations(localizations);
if (!string.IsNullOrWhiteSpace(geoString))
db.Execute("UPDATE [dbo].[CollectionEventSeries] SET geography=GEOGRAPHY::STGeomFromText(@0, 4326) WHERE SeriesID=@1", geoString, series.CollectionEventSeriesID);
t.Complete();
return series.CollectionEventSeriesID;
}
}
public int InsertEventSeries(EventSeries series, IEnumerable<Localization> localizations, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = new Transaction(db))
{
db.Insert(series);
var geoString = SerializeLocalizations(localizations);
if (!string.IsNullOrWhiteSpace(geoString))
db.Execute("Update [dbo].[CollectionEventSeries] Set geography=@0 Where SeriesID=@1", geoString, series.CollectionEventSeriesID);
t.Complete();
return series.CollectionEventSeriesID;
}
}
public IEnumerable<Term> GetStandardVocabulary(UserCredentials login)
{
IEnumerable<Term> linqTerms;
using (var db = login.GetConnection())
{
linqTerms =
Enumerable.Concat(
db.Query<Term>("FROM [dbo].[DiversityMobile_TaxonomicGroups]() as Term")
.Select(t => { t.Source = DiversityPhone.Model.TermList.TaxonomicGroups; return t; }),
db.Query<Term>("FROM [dbo].[DiversityMobile_UnitRelationTypes]() as Term")
.Select(t => { t.Source = DiversityPhone.Model.TermList.RelationshipTypes; return t; })
).ToList();
}
return linqTerms;
}
public IEnumerable<Project> GetProjectsForUser(UserCredentials login)
{
if(string.IsNullOrWhiteSpace(login.Repository))
return Enumerable.Empty<Project>();
using (var db = login.GetConnection())
{
try
{
return db.Query<Project>("FROM [dbo].[DiversityMobile_ProjectList] () AS [Project]")
.Select(p =>
{
p.DisplayText = p.DisplayText ?? "No Description";
return p;
})
.ToList(); //TODO Use credential DB
}
catch
{
return Enumerable.Empty<Project>();
}
}
}
public int InsertIdentificationUnit(IdentificationUnit iu, IEnumerable<IdentificationUnitAnalysis> analyses, UserCredentials login)
{
using (var db = login.GetConnection())
using (var t = db.GetTransaction())
{
db.Insert(iu);
db.Insert(iu.GetIdentification(login));
db.Insert(iu.GetGeoAnalysis(login));
if (analyses != null)
foreach (var a in analyses)
{
a.CollectionUnitID = iu.CollectionUnitID;
a.CollectionSpecimenID = iu.CollectionSpecimenID;
db.Insert(a);
}
t.Complete();
return iu.CollectionUnitID;
}
}
public void InsertMMO(MultimediaObject mmo, UserCredentials login)
{
using (var db = login.GetConnection())
{
try
{
switch (mmo.OwnerType)
{
case MultimediaOwner.EventSeries:
CollectionEventSeriesImage cesi = mmo.ToSeriesImage();
db.Insert(cesi);
break;
case MultimediaOwner.Event:
CollectionEventImage cei = mmo.ToEventImage();
db.Insert(cei);
break;
case MultimediaOwner.Specimen:
case MultimediaOwner.IdentificationUnit:
CollectionSpecimenImage csi = mmo.ToSpecimenImage(db);
db.Insert(csi);
break;
default:
throw new ArgumentException("unknown type");
}
}
catch (SqlException)
{
// Presumably Multiple Insert, meaning the object already exists
// -> Success
}
}
}
public UserProfile GetUserInfo(UserCredentials login)
{
try
{
using (var db = login.GetConnection())
{
return db.Query<UserProfile>("FROM [DiversityMobile_UserInfo]() AS [UserProfile]").Single();
}
}
catch
{
return null;
}
}
public IEnumerable<Qualification> GetQualifications(UserCredentials login)
{
using (var db = login.GetConnection())
{
return getQualifications(db)
.Select(q =>
{
if(string.IsNullOrWhiteSpace(q.DisplayText))
{
q.DisplayText = "no qualifier";
}
return q;
})
.ToList();
}
}
public IEnumerable<Model.Property> GetPropertiesForUser(UserCredentials login)
{
var propsForUser = propertyListsForUser(login).ToDictionary(pl => pl.PropertyID);
using (var db = login.GetConnection())
{
return getProperties(db).Where(p => propsForUser.ContainsKey(p.PropertyID)).ToList();
}
}
public IEnumerable<Model.TaxonList> GetTaxonListsForUser(UserCredentials login)
{
List<Model.TaxonList> result = new List<TaxonList>();
using (var db = login.GetConnection(CATALOG_DIVERSITYMOBILE))
{
result.AddRange(
taxonListsForUser(login.LoginName,db)
.Select(l => {l.IsPublicList = false; return l;})
);
}
var publicTaxa = ServiceConfiguration.PublicTaxa;
using (var db = new DiversityORM.Diversity(publicTaxa.Login, publicTaxa.Server, publicTaxa.Catalog))
{
result.AddRange(
taxonListsForUser(db)
.Select(l => { l.IsPublicList = true; return l; })
);
}
return result;
}
