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);
}