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); }
public HttpResponseMessage SaveDatasetActivities(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; detail.ActivityId = activity.Id; detail.ByUserId = activity.UserId; detail.EffDt = DateTime.Now; dbset_detail.Add(detail); rowid++; } db.SaveChanges(); scope.Complete(); //complete the transaction since nothing blew up! //logger.Debug(((JObject)JToken.FromObject(data)).ToString()); new_records.Add(activity); } catch (Exception e) { logger.Debug("An error occurred: ", e.ToString()); throw e; //rethrow so that it'll come back as an error in the client. } } //if } //using //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? { 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 = " + newActivityId + ") where Id = " + newActivityId; using (SqlCommand cmd = new SqlCommand(query, con)) { logger.Debug(query); cmd.ExecuteNonQuery(); } } } } //foreach /* logger.Debug(duplicateActivities); var importResult = new ImportResult(); importResult.duplicates = duplicateActivities; importResult.success = true; */ string result = JsonConvert.SerializeObject(new_records); HttpResponseMessage resp = new HttpResponseMessage(System.Net.HttpStatusCode.OK); resp.Content = new System.Net.Http.StringContent(result, System.Text.Encoding.UTF8, "text/plain"); //to stop IE from being stupid. //return resp; return new HttpResponseMessage(HttpStatusCode.OK); }
private HttpResponseMessage SaveDatasetActivitiesEFF(JObject jsonData) { logger.Debug("Saving dataset activities: "); var db = ServicesContext.RestartCurrent; 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>(); //get a list of the fields that are GRID types (relations) var grid_fields = dataset.Fields.Where(o => o.ControlType == "grid"); var new_records = new List<Activity>(); db.Configuration.AutoDetectChangesEnabled = false; db.Configuration.ValidateOnSaveEnabled = false; foreach (var item in json.activities) { int newActivityId = 0; if (!(item is JProperty)) { throw new Exception("There is a problem with your request. Format error."); } 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) { logger.Debug("Ooops had an error converting activity 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.LaboratoryId = activity_json.LaboratoryId; activity.AccuracyCheckId = activity_json.AccuracyCheckId; activity.PostAccuracyCheckId = activity_json.PostAccuracyCheckId; activity.Timezone = activity_json.Timezone; /* //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; 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>(); Dictionary<string, JArray> grids = new Dictionary<string, JArray>(); foreach (var detailitem in activity_json.Details) { //copy this json object into a EFF object // this is probably slow. details.Add(detailitem.ToObject(dbset_detail_type)); //does this field have a relation/grid field? If so then save those, too. if (grid_fields != null) { foreach (var grid_field in grid_fields) { logger.Debug("Found a grid field: " + grid_field.DbColumnName); grids.Add(grid_field.DbColumnName, detailitem[grid_field.DbColumnName]); } } } //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; detail.ActivityId = activity.Id; detail.ByUserId = activity.UserId; detail.EffDt = DateTime.Now; dbset_detail.Add(detail); //logger.Debug("added a detail"); //relation grids? if (grids.Count > 0) { logger.Debug("We have grids in our data to save..."); foreach (KeyValuePair<string, JArray> grid_item in grids) { int grid_rowid = 1; //new grid field var grid_type = dataset.Datastore.TablePrefix + "_" + grid_item.Key; logger.Debug(" Hey we have a relation of type: " + grid_type); //get objecttype of this type var dbset_grid_type = db.GetTypeFor(grid_type); var dbset_relation = db.GetDbSet(grid_type); //logger.Debug("saving items in : " + grid_item.Key ); foreach (dynamic relation_row in grid_item.Value) { //logger.Debug("Relationrow: " + relation_row); var relationObj = relation_row.ToObject(dbset_grid_type); relationObj.EffDt = DateTime.Now; relationObj.ParentRowId = rowid; relationObj.RowId = grid_rowid; relationObj.RowStatusId = DataDetail.ROWSTATUS_ACTIVE; relationObj.ByUserId = activity.UserId; relationObj.ActivityId = activity.Id; relationObj.QAStatusId = dataset.DefaultRowQAStatusId; //TODO? //logger.Debug("woot saving a grid row!"); //logger.Debug(JsonConvert.SerializeObject( relationObj, Formatting.Indented)); dbset_relation.Add(relationObj); grid_rowid++; } } } rowid++; } db.SaveChanges(); //save all details for this activity, then iterate to the next activity. } catch (Exception e) { logger.Debug("An error occurred saving the activity or details: "+newActivityId, e.Message); db = ServicesContext.RestartCurrent; db.Configuration.AutoDetectChangesEnabled = true; db.Configuration.ValidateOnSaveEnabled = true; //ok, lets try to delete the activity that went bad. db.Activities.Remove(db.Activities.Find(newActivityId)); db.SaveChanges(); logger.Debug("ok so we auto-deleted the activity we created: " + newActivityId); throw e; //rethrow so that it'll come back as an error in the client. } finally{ db.Configuration.AutoDetectChangesEnabled = true; db.Configuration.ValidateOnSaveEnabled = true; } //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? { 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 = " + newActivityId + ") where Id = " + newActivityId; using (SqlCommand cmd = new SqlCommand(query, con)) { logger.Debug(query); cmd.ExecuteNonQuery(); } } } else if(newActivityId != 0 && (dataset.Datastore.TablePrefix == "WaterQuality")) // 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(SampleDate),111), ' - ', convert(varchar,max(SampleDate),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(); } } } } //foreach activity return new HttpResponseMessage(HttpStatusCode.OK); }