/services/Controllers/DataActionController.cs
C# | 1744 lines | 1173 code | 389 blank | 182 comment | 173 complexity | 8b8f3b21207a715e78aa91cc0c7e57c5 MD5 | raw file
Possible License(s): MPL-2.0-no-copyleft-exception
Large files files are truncated, but you can click here to view the full file
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Configuration;
- using System.Data;
- using System.Transactions;
- using System.Data.Common;
- using System.Data.SqlClient;
- using System.IO;
- using System.Linq;
- using System.Linq.Dynamic;
- using System.Net;
- using System.Net.Http;
- using System.Text;
- using System.Threading.Tasks;
- using System.Web;
- using System.Web.Http;
- using CsvHelper;
- using Newtonsoft.Json;
- using Newtonsoft.Json.Linq;
- using NLog;
- using services.Models;
- using services.Models.Data;
- using services.Resources;
- namespace services.Controllers
- {
- [Authorize]
- public partial class DataActionController : ApiController
- {
- private static Logger logger = LogManager.GetCurrentClassLogger();
- public DataTable GetHeadersDataForDataset(int id)
- {
- var db = ServicesContext.Current;
- Dataset dataset = db.Datasets.Find(id);
- if (dataset == null)
- {
- throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
- }
- string query = "SELECT h.* FROM " + dataset.Datastore.TablePrefix + "_Header_VW h JOIN Activities a on a.Id = h.ActivityId WHERE a.DatasetId = " + dataset.Id;
- DataTable dt = new DataTable();
- using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString))
- {
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- con.Open();
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- da.Fill(dt);
- }
- }
- return dt;
- }
- [HttpPost]
- public IEnumerable<MetadataValue> GetMetadataFor(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- User me = AuthorizationManager.getCurrentUser();
- Project project = db.Projects.Find(json.ProjectId.ToObject<int>());
- int EntityTypeId = json.EntityTypeId.ToObject<int>();
- if (project == null || me == null)
- throw new Exception("Configuration error. Please try again.");
- return MetadataHelper.getMetadata(project.Id, EntityTypeId).AsEnumerable();
- }
- [HttpGet]
- public IEnumerable<Dataset> GetMyDatasets()
- {
- var db = ServicesContext.Current;
- User me = AuthorizationManager.getCurrentUser();
- var mydatasets = "";
- try
- {
- mydatasets = me.UserPreferences.Where(o => o.Name == UserPreference.DATASETS).FirstOrDefault().Value;
- }
- catch (Exception e)
- {
- logger.Debug("Couldn't get your datasets -- probably don't have any favorites.");
- logger.Debug(e);
- }
- var datasets = db.Datasets.SqlQuery("SELECT * FROM Datasets WHERE Id in (" + mydatasets +") ORDER BY Name");
- return datasets;
- }
- [HttpGet]
- public IEnumerable<Project> GetMyProjects()
- {
- var db = ServicesContext.Current;
- User me = AuthorizationManager.getCurrentUser();
- var my_projects = "";
- try
- {
- my_projects = me.UserPreferences.Where(o => o.Name == UserPreference.PROJECTS).FirstOrDefault().Value;
- }
- catch (Exception e)
- {
- logger.Debug("Couldn't get your projects -- probably don't have any favorites.");
- logger.Debug(e);
- }
- var myprojects = db.Projects.SqlQuery("SELECT * FROM Projects WHERE Id in (" + my_projects + ") ORDER BY Name");
- return myprojects;
- }
- [HttpPost]
- public HttpResponseMessage SetQaStatus(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- User me = AuthorizationManager.getCurrentUser();
- Activity activity = db.Activities.Find(json.ActivityId.ToObject<int>());
- if (activity == null || me == null)
- throw new Exception("Configuration error. Please try again.");
- logger.Debug("Userid = " + me.Id + " Activity = " + activity.Id);
- //TODO: verify that the user is authorized to modify this activity? -- yes, that would be nice. (or add Authorized annotation) TODO!
- var aq = new ActivityQA();
- aq.ActivityId = activity.Id;
- aq.EffDt = DateTime.Now;
- aq.Comments = json.Comments;
- aq.UserId = me.Id; //current user.
- aq.QAStatusId = json.QAStatusId;
-
- db.ActivityQAs.Add(aq);
- db.SaveChanges();
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- [HttpPost]
- public HttpResponseMessage SetProjectEditors(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- Project project = db.Projects.Find(json.ProjectId.ToObject<int>());
- if (project == null)
- throw new Exception("Configuration error.");
- User me = AuthorizationManager.getCurrentUser();
- if (me == null)
- throw new Exception("Configuration error.");
- //verify that the sender is the project owner.
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Authorization error.");
- //First -- remove all editors from this project.
- project.Editors.RemoveAll(o => o.Id > 0);
- db.SaveChanges();
- foreach (var item in json.Editors)
- {
- User user = db.User.Find(item.Id.ToObject<int>());
- if (user == null)
- logger.Debug("Wow -- user not found!: " + item.Id);
- else
- {
- logger.Debug("Adding: " + item.Id);
- project.Editors.Add(user);
- }
- }
- db.SaveChanges();
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- [HttpPost]
- public HttpResponseMessage SetDatasetMetadata(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- if (dataset == null)
- throw new Exception("Configuration error.");
- Project project = db.Projects.Find(dataset.ProjectId);
-
- User me = AuthorizationManager.getCurrentUser();
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Configuration error.");
- //Now save metadata
- List<MetadataValue> metadata = new List<MetadataValue>();
- foreach (var jmv in json.Metadata)
- {
- var mv = jmv.ToObject<MetadataValue>();
- mv.UserId = me.Id;
- metadata.Add(mv);
- logger.Debug("Found new metadata: " + mv.MetadataPropertyId + " + + " + mv.Values);
- }
- //fire setMetdata which will handle persisting the metadata
- dataset.Metadata = metadata;
- db.SaveChanges();
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- [HttpPost]
- public HttpResponseMessage DeleteDatasetActivities(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- Project project = db.Projects.Find(dataset.ProjectId);
- if (project == null)
- throw new Exception("Configuration error. Please try again.");
- User me = AuthorizationManager.getCurrentUser();
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Configuration error.");
- var Activities = new List<string>();
- foreach (var item in json.Activities)
- {
- Activities.Add(""+item.Id);
- }
- var ActivityIds = string.Join(",", Activities);
- var DataTable = dataset.Datastore.TablePrefix;
- //open a raw database connection...
- using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString))
- {
- con.Open();
- var query = "DELETE FROM " + DataTable + "_Detail WHERE ActivityId in ("+ ActivityIds + ")";
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- query = "DELETE FROM " + DataTable + "_Header WHERE ActivityId in (" + ActivityIds + ")";
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- query = "DELETE FROM ActivityQAs WHERE ActivityId in (" + ActivityIds + ")";
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- query = "DELETE FROM Activities WHERE DatasetId = " + dataset.Id + " AND Id in (" + ActivityIds + ")";
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- }
-
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- //QUERY
- //QueryDatasetActivities -- can call with a datasetId or a datastoreId
- [HttpPost]
- public DataTable QueryDatasetActivities(JObject jsonData)
- {
- var db = ServicesContext.Current;
- DataTable datatable = null;
- dynamic json = jsonData;
-
- //let's see if we're dealing with a dataset
- if (json["DatasetId"] is JToken)
- {
- //grab a reference to this dataset so we can parse incoming fields
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- if (dataset == null)
- throw new Exception("Configuration error. Please try again.");
- logger.Debug("Alright! we are working with dataset: " + dataset.Id);
- datatable = getQueryResults(dataset, json);
- }
- //let's see if we're dealing with a datastore.
- if (json["DatastoreId"] is JToken)
- {
- //grab a reference to this dataset so we can parse incoming fields
- Datastore datastore = db.Datastores.Find(json.DatastoreId.ToObject<int>());
- if (datastore == null)
- throw new Exception("Configuration error. Please try again.");
- logger.Debug("Alright! we are working with datastore: " + datastore.Id);
- datatable = getQueryResults(datastore, json);
- }
-
- return datatable;
-
- }
- //Writes csv export file out to a file and returns the url.
- [HttpPost]
- public ExportResult DownloadDatasetActivities(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- User me = AuthorizationManager.getCurrentUser();
- //grab a reference to this dataset so we can parse incoming fields
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- if (dataset == null || me == null)
- throw new Exception("Configuration error. Please try again.");
- logger.Debug("Alright! we are working with dataset: " + dataset.Id);
- DataTable dt = getQueryResults(dataset, json);
- logger.Debug("Download data -- we have a result back.");
- string Filename = json.Filename;
- Filename = Filename.Replace("\"", string.Empty);
- Filename = Filename.Replace("\\", string.Empty);
- Filename = Filename.Replace("/", string.Empty);
-
- logger.Debug("Incomfing filename specified: " + Filename);
- string root = System.Web.HttpContext.Current.Server.MapPath("~/exports");
- string the_file = root + @"\" + dataset.Id + @"_" + me.Id + @"\" + Filename;
- logger.Debug("saving file to location: " + the_file);
- System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(the_file)); //will create if necessary.
- string rootUrl = Request.RequestUri.AbsoluteUri.Replace(Request.RequestUri.AbsolutePath, String.Empty);
- rootUrl += "/services/exports/" + dataset.Id + "_" + me.Id + "/" + Filename;
- using (TextWriter writer = System.IO.File.CreateText(the_file)) //will overwrite = good
- {
- using (var csv = new CsvWriter(writer))
- {
- IEnumerable<string> columnNames = dataset.getExportLabelsList();//dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName);
- //columns
- foreach (var header in columnNames)
- {
- csv.WriteField(header);
- }
- csv.NextRecord();
- //fields
- foreach (DataRow row in dt.Rows)
- {
- IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
- foreach (var field in fields)
- {
- //replace out the multiselect array stuff.
- var f = field.Replace("[]", string.Empty).Replace("[\"", string.Empty).Replace("\"]", string.Empty).Replace("\",\"", ",");
- csv.WriteField(f);
- }
- csv.NextRecord();
- }
- }
- }
- //TODO-- error handling?
- ExportResult result = new ExportResult();
- result.success = true;
- result.file = rootUrl;
- result.errors = null;
-
- return result;
- }
- [HttpPost]
- public HttpResponseMessage UpdateDatasetActivities(JObject jsonData)
- {
- var db = ServicesContext.Current;
- dynamic json = jsonData;
- User me = AuthorizationManager.getCurrentUser();
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- if (dataset == null)
- throw new Exception("Configuration Error.");
- Project project = db.Projects.Find(dataset.ProjectId);
- if (project == null)
- throw new Exception("Configuration Error");
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Authorization error.");
- //setup our generic data stuff
- var data_header_name = dataset.Datastore.TablePrefix + "_Header";
- var data_detail_name = dataset.Datastore.TablePrefix + "_Detail";
- var dbset_header = db.GetDbSet(data_header_name);
- var dbset_detail = db.GetDbSet(data_detail_name);
- var dbset_header_type = db.GetTypeFor(data_header_name);
- var dbset_detail_type = db.GetTypeFor(data_detail_name);
- foreach (var item in json.activities)
- {
-
- if (item is JProperty)
- {
- var prop = item as JProperty;
- dynamic activity_json = prop.Value;
- logger.Debug("Updating activity id: " + json.ActivityId);
- Activity activity = db.Activities.Find(json.ActivityId.ToObject<int>());
- if (activity == null)
- {
- throw new Exception("Invalid Activity.");
- }
- activity.LocationId = activity_json.LocationId;
- try
- {
- activity.ActivityDate = activity_json.ActivityDate;
- }
- catch (Exception e)
- {
- logger.Debug("Ooops had an error converting date: " + activity_json.ActivityDate);
- logger.Debug(e.ToString());
- throw (e);
- }
- //activity.DatasetId = json.DatasetId;
- activity.UserId = me.Id;
- activity.SourceId = 1; // TODO get from data
- activity.ActivityTypeId = 1;
- activity.InstrumentId = activity_json.InstrumentId;
- activity.AccuracyCheckId = activity_json.AccuracyCheckId;
- activity.PostAccuracyCheckId = activity_json.PostAccuracyCheckId;
- activity.Timezone = activity_json.Timezone;
- //activity.CreateDate = DateTime.Now;
- db.Entry(activity).State = EntityState.Modified;
- db.SaveChanges();
- //updated the activity
-
- logger.Debug("Updated an activity: ");
- logger.Debug(" LocationID = " + activity_json.LocationId);
- logger.Debug(" ActivityDate = " + activity_json.ActivityDate);
- logger.Debug(" ID = " + activity.Id);
- //now check our activity status -- update it if we've changed.
- //if(activity.ActivityQAStatus.ActivityId != )
- dynamic activityqastatus = activity_json.ActivityQAStatus;
- //logger.Debug(activity_json.ActivityQAStatus);
-
- //logger.Debug(activityqastatus.QAStatusId.ToObject<int>());
- ActivityQA newQA = new ActivityQA();
- newQA.ActivityId = activity.Id;
- newQA.QAStatusId = activityqastatus.QAStatusID.ToObject<int>();
- newQA.Comments = activityqastatus.Comments;
- newQA.EffDt = DateTime.Now;
- newQA.UserId = activity.UserId;
- db.ActivityQAs.Add(newQA);
- db.SaveChanges();
- //get our last header and then check against incoming header field values to see if anything has changed.
- var last_header_list = dbset_header.SqlQuery("SELECT * FROM " + data_header_name + " WHERE ActivityId = " + activity.Id + " ORDER BY EffDt DESC");
- //.SqlQuery("SELECT * FROM " + data_header_name + " WHERE ActivityId = " + activity.Id + " ORDER BY EffDt DESC").AsQueryable().f;
- //db.AdultWeir_Header.Where(o => o.ActivityId == activity.Id).OrderByDescending(d => d.EffDt).FirstOrDefault();
- var last_header = this.getFirstItem(last_header_list);
- logger.Debug("Ok -- here we are with our lastheader:");
- logger.Debug(last_header);
- if (last_header == null)
- throw new Exception("Somehow there is no previous header even though we are trying to update.");
- bool header_updated = false;
- //spin through and check the header fields for changes...
- foreach (JProperty header_field in activity_json.Header)
- {
- logger.Debug("Checking last header value of field : '" + header_field.Name + "' with incoming value + '" + header_field.Value+"'");
- var objval = last_header.GetType().GetProperty(header_field.Name).GetValue(last_header, null);
- if (objval != null)
- {
- logger.Debug("Checking last header value of field : " + header_field.Name + " with value " + objval + " + with incoming value + " + header_field.Value);
- if (objval.ToString() != header_field.Value.ToString())
- {
- logger.Debug("a different value! we'll save a header then...");
- header_updated = true;
- break;
- }
- }
- else
- {
- if (header_field.Value.ToString() != "")
- {
- logger.Debug("Dunno why, but objval was null." + header_field + " we are going to save a new one.");
- header_updated = true;
- break;
- }
- }
- }
- if (header_updated)
- {
- logger.Debug("Saving a new header then");
- var header = activity_json.Header.ToObject(dbset_header_type);
- //now do the saving! -- this works the exact same way for update as it does for new
- header.ActivityId = activity.Id;
- header.ByUserId = activity.UserId;
- header.EffDt = DateTime.Now;
- dbset_header.Add(header);
- db.SaveChanges();
- }
- //there are three possible cases of changes:
- // 1) updated row (has ID and is in "updatedRowIds" list)
- // 2) new row (has no ID)
- // 3) deleted row (is not in the list, ID is in "deletedRowIds" list)
- //we ALWAYS make such indication by INSERTING a new row with a matching rowid + activityid + new current effective date.
- // exception is NEW row which gets and incremented rowid
- //first, lets lookup our last row id so we have a place to start if we add rows.
- int rowid = 1;
- var last_row_list = dbset_detail.SqlQuery("SELECT * FROM " + data_detail_name + " WHERE ActivityId = "+ activity.Id + " AND RowStatusId = " + DataDetail.ROWSTATUS_ACTIVE + " ORDER BY RowId DESC");
- //db.AdultWeir_Detail.Where(o => o.ActivityId == activity.Id).Where(o => o.RowStatusId == DataDetail.ROWSTATUS_ACTIVE).OrderByDescending(d => d.RowId).FirstOrDefault();
-
- var last_row = this.getFirstItem(last_row_list);
- if (last_row != null)
- {
- rowid = last_row.RowId+1;
- }
- else
- logger.Debug("Hmm there were no previous details rows for activity : " + activity.Id + " so we are starting at 1.");
- //now lets iterate our incoming rows and see what we've got.
- var details = new List<DataDetail>();
- List<int> updated_rows = new List<int>();
- foreach (var updated_row in json.updatedRowIds)
- {
- logger.Debug("Found an updated row: " + updated_row);
- updated_rows.Add(updated_row.ToObject<int>());
- }
- List<int> deleted_rows = new List<int>();
- foreach(var deleted_row in json.deletedRowIds)
- {
- logger.Debug("Found a deleted row: " + deleted_row);
- deleted_rows.Add(deleted_row.ToObject<int>());
- if (updated_rows.Contains(deleted_row.ToObject<int>()))
- updated_rows.Remove(deleted_row.ToObject<int>());
- }
- foreach (var detailitem in activity_json.Details)
- {
- var adw = detailitem.ToObject(dbset_detail_type);
- logger.Debug("spinning through incoming details: " + adw.Id);
- if (adw.Id == 0)
- {
- //new record
- adw.RowId = rowid; rowid++;
- details.Add(adw);
- }
- else
- {
- //deleted or updated?
- if (updated_rows.Contains(adw.Id))
- {
- //updated
- adw.Id = 0;
- details.Add(adw);
- }
- else if (deleted_rows.Contains(adw.Id))
- {
- //deleted
- adw.Id = 0;
- adw.RowStatusId = DataDetail.ROWSTATUS_DELETED;
- details.Add(adw);
- }
- //otherwise nothing.
- }
- }
- foreach (var detail in details)
- {
- detail.ActivityId = activity.Id;
- detail.ByUserId = activity.UserId;
- detail.EffDt = DateTime.Now;
-
- //TODO: activity QA ID + comment
- dbset_detail.Add(detail);
- }
- db.SaveChanges();
- //If there is a ReadingDateTime field in use, set the activity description to be the range of reading dates for this activity.
- if (dataset.Datastore.TablePrefix == "WaterTemp") // others with readingdatetime?
- {
- using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString))
- {
- con.Open();
- var query = "update Activities set Description = (select concat(convert(varchar,min(ReadingDateTime),111), ' - ', convert(varchar,max(ReadingDateTime),111)) from " + dataset.Datastore.TablePrefix + "_Detail_VW where ActivityId = " + activity.Id + ") where Id = " + activity.Id;
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- }
- }
- }
- }
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- private dynamic getFirstItem(dynamic list)
- {
- dynamic first = null;
- foreach (var item in list)
- {
- first = item;
- break;
- }
- return first;
- }
- public HttpResponseMessage SaveDatasetActivities(JObject jsonData)
- {
- return SaveDatasetActivitiesEFF(jsonData);
- }
- //so we'll build one that generates sql directly since the EFF way has mediocre performance.
- [HttpPost]
- private HttpResponseMessage SaveDatasetActivitiesSQL(JObject jsonData)
- {
- var db = ServicesContext.Current;
- User me = AuthorizationManager.getCurrentUser();
- dynamic json = jsonData;
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
-
- if (dataset == null)
- throw new Exception("Configuration Error.");
- Project project = db.Projects.Find(dataset.ProjectId);
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Authorization error.");
- var data_header_name = dataset.Datastore.TablePrefix + "_Header";
- var data_detail_name = dataset.Datastore.TablePrefix + "_Detail";
- //these will get loaded once and then stay the same every time.
- var query_header = "INSERT INTO " + data_header_name + " (";
- var query_detail = "INSERT INTO " + data_detail_name + " (";
- var headerFields = new List<string>();
- var detailFields = new List<string>();
- using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString))
- {
- con.Open();
- foreach (var item in json.activities)
- {
- int newActivityId = 0;
- //each activity in its own scope...
-
- var trans = con.BeginTransaction();
- if (item is JProperty)
- {
- var prop = item as JProperty;
- dynamic activity_json = prop.Value;
- Activity activity = new Activity();
- activity.LocationId = activity_json.LocationId;
- try
- {
- activity.ActivityDate = activity_json.ActivityDate;
- }
- catch (Exception e)
- {
- //TODO -- this is a very bad idea if the date is wrong...
- logger.Debug("Ooops had an error converting date: " + activity_json.ActivityDate);
- logger.Debug(e.ToString());
- throw e;
- }
- activity.DatasetId = json.DatasetId;
- activity.UserId = me.Id;
- activity.SourceId = 1; // TODO
- activity.ActivityTypeId = 1;
- activity.CreateDate = DateTime.Now;
- activity.InstrumentId = activity_json.InstrumentId;
- activity.AccuracyCheckId = activity_json.AccuracyCheckId;
- activity.PostAccuracyCheckId = activity_json.PostAccuracyCheckId;
- activity.Timezone = (activity_json.Timezone != null) ? activity_json.Timezone.Replace("'","''") : "";
-
- var activity_query = "INSERT INTO Activities (LocationId, ActivityDate, DatasetId, UserId, SourceId, ActivityTypeId, CreateDate, Timezone) VALUES (";
- activity_query +=
- activity.LocationId + ",'" +
- activity.ActivityDate + "'," +
- activity.DatasetId + "," +
- activity.UserId + "," +
- activity.SourceId + "," +
- activity.ActivityTypeId + "," +
- "'" + activity.CreateDate + "'," +
- //activity.InstrumentId + "," +
- //activity.AccuracyCheckId + "," +
- //activity.PostAccuracyCheckId + "," +
- "'" + activity.Timezone + "');";
- activity_query += "SELECT SCOPE_IDENTITY();";
- logger.Debug(activity_query);
- using (SqlCommand cmd = new SqlCommand(activity_query, con,trans))
- {
- var result = cmd.ExecuteScalar();
- //logger.Debug(result + result.GetType().ToString()); = System.Decimal?!
- newActivityId = Convert.ToInt32(result.ToString());
- }
- //db.Activities.Add(activity);
- //db.SaveChanges();
- dynamic activityqastatus = activity_json.ActivityQAStatus;
- activity.Id = newActivityId;
- logger.Debug("Hey! we have a new activity id the ol' fashioned way: " + activity.Id);
-
- var newQA_query = "INSERT INTO ActivityQAs (ActivityId, QAStatusId, Comments, EffDt, UserId) VALUES (";
- ActivityQA newQA = new ActivityQA();
- newQA.ActivityId = newActivityId;
- newQA.QAStatusId = activityqastatus.QAStatusID.ToObject<int>();
- newQA.Comments = activityqastatus.Comments.Replace("'","''");
- newQA.EffDt = DateTime.Now;
- newQA.UserId = activity.UserId;
- newQA_query += newQA.ActivityId + "," +
- newQA.QAStatusId + "," +
- "'" + newQA.Comments + "','" +
- newQA.EffDt + "'," +
- newQA.UserId + ");";
- logger.Debug(newQA_query);
- using (SqlCommand cmd = new SqlCommand(newQA_query, con, trans))
- {
- if (cmd.ExecuteNonQuery() == 0)
- {
- logger.Debug("Failed to execute query: " + newQA_query);
- throw new Exception("Failed to execute qa query. See log.");
- }
- }
- //db.ActivityQAs.Add(newQA);
- //db.SaveChanges();
- //get these ready for a new set of values
- var query_header_values = " VALUES (";
- var query_detail_values = " VALUES (";
- var headerValues = new List<string>();
- var detailValues = new List<string>();
- //have our headers been populated yet? we only have to do it once.
- if (headerFields.Count == 0)
- {
- //first the ones we always have
- headerFields.Add("ActivityId");
- headerFields.Add("ByUserId");
- headerFields.Add("EffDt");
- //now spin through and add any incoming ones from our JSON.
- var the_header = activity_json.Header as JObject;
- IList<string> propertyNames = the_header.Properties().Select(p => p.Name).ToList();
- foreach (var prop_field in propertyNames)
- {
- headerFields.Add(prop_field);
- }
- }
- headerValues.Add(activity.Id.ToString());
- headerValues.Add(activity.UserId.ToString());
- headerValues.Add("'" + DateTime.Now.ToString() + "'");
- //now populate header values
- foreach (var prop_field in headerFields)
- {
- if (prop_field != "ActivityId" && prop_field != "ByUserId" && prop_field != "EffDt") //these are already done.
- {
- var control_type = dataset.Fields.Where(o => o.Field.DbColumnName == prop_field).Single().ControlType;
- var objVal = activity_json.Header.GetValue(prop_field);
- if (objVal == null)
- headerValues.Add("null");
- else
- {
- headerValues.Add(getStringValueByControlType(control_type, objVal.ToString()));
- }
- }
- }
- var the_query = query_header + string.Join(",", headerFields) + ") " + query_header_values + string.Join(",", headerValues) + ")";
- logger.Debug(the_query);
- using (SqlCommand cmd = new SqlCommand(the_query, con, trans))
- {
- if (cmd.ExecuteNonQuery() == 0)
- {
- logger.Debug("Failed to execute query: " + the_query);
- throw new Exception("Failed to execute header query. See log.");
- }
- }
- //---------------- now for the details...
- int rowid = 1;
- foreach (JObject detail in activity_json.Details)
- {
- //have our detail fields been populated yet? we only have to do it once.
- if (detailFields.Count == 0)
- {
- //first the ones we always have
- detailFields.Add("ActivityId");
- detailFields.Add("ByUserId");
- detailFields.Add("EffDt");
- detailFields.Add("RowStatusId");
- detailFields.Add("RowId");
- detailFields.Add("QAStatusId");
- //now spin through and add any incoming ones from our JSON.
- IList<string> propertyNames = detail.Properties().Select(p => p.Name).ToList();
- foreach (var prop_field in propertyNames)
- {
- DatasetField the_field = dataset.Fields.Where(o => o.Field.DbColumnName == prop_field && o.FieldRoleId == 2).SingleOrDefault();
- if (the_field != null)
- detailFields.Add(prop_field);
- }
- }
- detailValues.Add(activity.Id.ToString());
- detailValues.Add(activity.UserId.ToString());
- detailValues.Add("'" + DateTime.Now.ToString() + "'");
- detailValues.Add(DataDetail.ROWSTATUS_ACTIVE.ToString());
- detailValues.Add(rowid.ToString());
- detailValues.Add(detail.GetValue("QAStatusId").ToString());
- //now populate detail values
- foreach (var prop_field in detailFields)
- {
- if (prop_field != "QAStatusId" && prop_field != "ActivityId" && prop_field != "ByUserId" && prop_field != "EffDt" && prop_field != "RowId" && prop_field != "RowStatusId") //these are already done.
- {
- var control_type = dataset.Fields.Where(o => o.Field.DbColumnName == prop_field).SingleOrDefault().ControlType;
- var objVal = detail.GetValue(prop_field);
- if (objVal == null)
- detailValues.Add("null");
- else
- {
- detailValues.Add(getStringValueByControlType(control_type, objVal.ToString()));
- }
- }
- }
- rowid++;
- var the_detail_query = query_detail + string.Join(",", detailFields) + ") " + query_detail_values + string.Join(",", detailValues) + ")";
- //logger.Debug(the_detail_query);
- using (SqlCommand cmd = new SqlCommand(the_detail_query, con, trans))
- {
- if (cmd.ExecuteNonQuery() == 0)
- {
- logger.Debug("Problem executing: " + the_detail_query);
- throw new Exception("Failed to execute detail query!");
- }
- }
- detailValues = new List<string>();
- }//foreach detail
- //If there is a ReadingDateTime field in use, set the activity description to be the range of reading dates for this activity.
- if (newActivityId != 0 && dataset.Datastore.TablePrefix == "WaterTemp") // others with readingdatetime?
- {
- var query = "update Activities set Description = (select concat(convert(varchar,min(ReadingDateTime),111), ' - ', convert(varchar,max(ReadingDateTime),111)) from " + dataset.Datastore.TablePrefix + "_Detail_VW where ActivityId = " + newActivityId + ") where Id = " + newActivityId;
- using (SqlCommand cmd = new SqlCommand(query, con))
- {
- logger.Debug(query);
- cmd.ExecuteNonQuery();
- }
- }
- }//if is a jproperty
- trans.Commit();
- }//foreach activity
- }//connection
- return new HttpResponseMessage(HttpStatusCode.OK);
- }
- private string getStringValueByControlType(string control_type, string in_val)
- {
- string retval = null;
- switch (control_type)
- {
- case "text":
- case "textarea":
- case "multiselect":
- case "select":
- case "date":
- case "datetime":
- retval = "'" + in_val.Replace("'", "''") + "'";
- break;
- default:
- retval = in_val;
- break;
- }
- return retval;
- }
- [HttpPost]
- private HttpResponseMessage SaveDatasetActivitiesEFF(JObject jsonData)
- {
- logger.Debug("Saving dataset activities: ");
- var db = ServicesContext.Current;
- User me = AuthorizationManager.getCurrentUser();
- dynamic json = jsonData;
- //COPY PASTE -- TODO -- reduce code smell!
- Dataset dataset = db.Datasets.Find(json.DatasetId.ToObject<int>());
- if (dataset == null)
- throw new Exception("Configuration Error.");
- Project project = db.Projects.Find(dataset.ProjectId);
- if (!project.isOwnerOrEditor(me))
- throw new Exception("Authorization error.");
- //setup our generic data stuff
- var data_header_name = dataset.Datastore.TablePrefix + "_Header";
- var data_detail_name = dataset.Datastore.TablePrefix + "_Detail";
- var dbset_header = db.GetDbSet(data_header_name);
- var dbset_detail = db.GetDbSet(data_detail_name);
- var dbset_header_type = db.GetTypeFor(data_header_name);
- var dbset_detail_type = db.GetTypeFor(data_detail_name);
- //var duplicateActivities = new List<Activity>();
- var new_records = new List<Activity>();
- //wrap this in a transaction
-
-
- foreach (var item in json.activities)
- {
- var scope = new TransactionScope(
- TransactionScopeOption.RequiresNew,
- new TransactionOptions()
- {
- IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
- });
- int newActivityId = 0 ;
- using (scope)
- {
- if (item is JProperty)
- {
- var prop = item as JProperty;
- dynamic activity_json = prop.Value;
- Activity activity = new Activity();
- activity.LocationId = activity_json.LocationId;
-
- try
- {
- activity.ActivityDate = activity_json.ActivityDate;
- }
- catch (Exception e)
- {
- //TODO -- this is a very bad idea if the date is wrong...
- logger.Debug("Ooops had an error converting date: " + activity_json.ActivityDate);
- logger.Debug(e.ToString());
- throw e;
- }
- try
- {
- activity.DatasetId = json.DatasetId;
- activity.UserId = me.Id;
- activity.SourceId = 1; // TODO
- activity.ActivityTypeId = 1;
- activity.CreateDate = DateTime.Now;
- activity.InstrumentId = activity_json.InstrumentId;
- activity.AccuracyCheckId = activity_json.AccuracyCheckId;
- activity.PostAccuracyCheckId = activity_json.PostAccuracyCheckId;
- activity.Timezone = activity_json.Timezone;
- logger.Debug("and we have finished parameters.");
- /*
- //check for duplicates. If it is a duplicate, add it to our list and bail out.
- if (activity.isDuplicate())
- {
- duplicateActivities.Add(activity);
- }
- */
- db.Activities.Add(activity);
- db.SaveChanges();
- dynamic activityqastatus = activity_json.ActivityQAStatus;
- //logger.Debug(activity_json.ActivityQAStatus);
- //logger.Debug(activityqastatus.QAStatusId.ToObject<int>());
- newActivityId = activity.Id;
- ActivityQA newQA = new ActivityQA();
- newQA.ActivityId = activity.Id;
- newQA.QAStatusId = activityqastatus.QAStatusID.ToObject<int>();
- newQA.Comments = activityqastatus.Comments;
- newQA.EffDt = DateTime.Now;
- newQA.UserId = activity.UserId;
- db.ActivityQAs.Add(newQA);
- db.SaveChanges();
- // logger.Debug("Created a new activity: ");
- // logger.Debug(" LocationID = " + activity_json.LocationId);
- // logger.Debug(" ActivityDate = " + activity_json.ActivityDate);
- // logger.Debug(" ID = " + activity.Id);
- var header = activity_json.Header.ToObject(dbset_header_type);
- var details = new List<DataDetail>();
- foreach (var detailitem in activity_json.Details)
- {
- details.Add(detailitem.ToObject(dbset_detail_type));
- }
- //now do the saving!
- header.ActivityId = activity.Id;
- header.ByUserId = activity.UserId;
- header.EffDt = DateTime.Now;
- dbset_header.Add(header);
- //details
- int rowid = 1;
- foreach (var detail in details)
- {
- detail.RowId = rowid;
- detail.RowStatusId = DataDetail.ROWSTATUS_ACTIVE;
- …
Large files files are truncated, but you can click here to view the full file