public ActionResult UploadCsvGetStubRecords()
{
string result = "USE [forms]\nGO\nSET IDENTITY_INSERT [dbo].[def_FormResults] ON\nGO\n";
using (var csvr = new CsvReader(new StreamReader(Request.InputStream)))
{
csvr.ReadHeaderRecord();
//organize csv headers into a heirarchy similar to the response data schema
//So for each ItemId, we have a set of ItemVariableIDs, each with one csvHeader.
Dictionary <int, Dictionary <int, string> > csvHeadersByIvByItem = new Dictionary <int, Dictionary <int, string> >();
foreach (string csvHeader in csvr.HeaderRecord)
{
string ivIdent = getItemVariableIdentifierFromCsvHeader(csvHeader);
def_ItemVariables iv = formsRepo.GetItemVariableByIdentifier(ivIdent);
if (iv == null)
{
continue;
}
int itmId = iv.itemId;
if (!csvHeadersByIvByItem.ContainsKey(itmId))
{
csvHeadersByIvByItem.Add(itmId, new Dictionary <int, string>());
}
csvHeadersByIvByItem[itmId].Add(iv.itemVariableId, csvHeader);
}
////determine which header is present for assigning uploaded formResults to groups
//bool useOrgNames;
//if( csvr.HeaderRecord.Contains( "sub_id" ) )
// useOrgNames = false;
//else if (csvr.HeaderRecord.Contains("org_name"))
// useOrgNames = true;
//else
// throw new Exception( "Could not find any headers to link assessments to groups" );
//iterate through the uploaded csv rows
while (csvr.HasMoreRecords)
{
AssmntRecord acl = new AssmntRecord(csvr.ReadDataRecord());
int formId = 1;
int subject = 0;// acl.getInt("ClientId");
int entId = acl.getInt("ent_id");
//int groupId = 460;//one-off 9/2/15////////////////////////////////
//if (useOrgNames)
//{
// string orgName = acl["org_name"];
// Group group = auth.GetGroupsByGroupNameAndEnterpriseID(orgName, entId).FirstOrDefault();
// if (group == null)
// throw new Exception("could not find group for org_name \"" + orgName + "\"");
// groupId = group.GroupID;
//}
//else
//{
int groupId = acl.getInt("sub_id");
//}
//add new form result in database
//int formRsltId = formsRepo.AddFormResult(formResult);
int formRsltId = acl.getInt("sis_id");
def_FormResults formResult = formsRepo.GetFormResultById(formRsltId);
// if the formResult doesn't already exist, append to the sql script for identity-inserting stub records
if (formResult == null)
{
def_FormResults fr = new def_FormResults();
fr.formId = formId;
fr.EnterpriseID = entId;
fr.GroupID = groupId;
fr.subject = subject;
fr.dateUpdated = DateTime.Now;
fr.formStatus = GetStatus(acl["Status"]);
fr.locked = GetLocked(acl["Status"]);
fr.sessionStatus = 0;
fr.reviewStatus = 255;
fr.training = false;
string sql = "INSERT INTO [dbo].[def_FormResults]([formResultId],[formId],[formStatus],[sessionStatus],[dateUpdated],[deleted],[locked],"
+ "[archived],[EnterpriseID],[GroupID],[subject],[interviewer],[assigned],[training],[reviewStatus],[statusChangeDate])\n"
+ "VALUES( '" + formRsltId + "','" + fr.formId + "','" + fr.formStatus + "','" + fr.sessionStatus + "','" + fr.dateUpdated + "','"
+ fr.deleted + "','" + fr.locked + "','" + fr.archived + "','" + fr.EnterpriseID + "','" + fr.GroupID + "','" + fr.subject + "','"
+ fr.interviewer + "','" + fr.assigned + "','" + fr.training + "','" + fr.reviewStatus + "','" + fr.statusChangeDate + "')\n";
result += sql;
}
// // Null out the locally allocated objects so the garbage collector disposes of them.
// fr = null;
// acl = null;
// formResult = null;
//}
////saveAssessmentScript(formResult);
//formsRepo.SaveFormResults(formResult);
//formsRepo.GetContext().Dispose();
//formsRepo = new FormsRepository();
//// Null out the locally allocated objects so the garbage collector disposes of them.
//fr = null;
//acl = null;
//formResult = null;
}
}
result += "SET IDENTITY_INSERT [dbo].[def_FormResults] OFF\nGO\n";
return(Content(result));
}
public ActionResult UploadAssmntCsv(int rowCount)
{
//* * * OT 2-4-16 This will be updated so that when rowCount is large, a smaller batchSize is used.
int batchSize = rowCount;
using (var csvr = new CsvReader(new StreamReader(Request.InputStream)))
{
csvr.ReadHeaderRecord();
//organize csv headers into a heirarchy that mirrors the response data schema (itemResults->responseVariables)
//So for each ItemId, we have a set of ItemVariableIDs, each with one csvHeader.
//This will be used to organize responses in a similar heirarchy and ultimately perform a batch insert
//all without having to read any more meta-data
Dictionary <int, Dictionary <int, string> > csvHeadersByIvByItem = new Dictionary <int, Dictionary <int, string> >();
foreach (string csvHeader in csvr.HeaderRecord)
{
string ivIdent = getItemVariableIdentifierFromCsvHeader(csvHeader);
def_ItemVariables iv = formsRepo.GetItemVariableByIdentifier(ivIdent);
if (iv == null)
{
continue;
}
int itmId = iv.itemId;
if (!csvHeadersByIvByItem.ContainsKey(itmId))
{
csvHeadersByIvByItem.Add(itmId, new Dictionary <int, string>());
}
csvHeadersByIvByItem[itmId].Add(iv.itemVariableId, csvHeader);
}
//NO READS AFTER THIS POINT
//create a structure matching csvHeadersByIvByItem
//this one will contain responses
//for each ItemId, we have a set of ItemVariableIDs, each with an empty array to hold a batch of responses
Dictionary <int, Dictionary <int, string[]> > rspValsByIvByItem = new Dictionary <int, Dictionary <int, string[]> >();
foreach (int itemId in csvHeadersByIvByItem.Keys)
{
Dictionary <int, string> csvHeadersByIv = csvHeadersByIvByItem[itemId];
Dictionary <int, string[]> rspValsByIv = new Dictionary <int, string[]>();
foreach (int ivId in csvHeadersByIv.Keys)
{
rspValsByIv.Add(ivId, new string[batchSize]);
}
rspValsByIvByItem.Add(itemId, rspValsByIv);
}
int[] formResultIds = new int[batchSize]; //formResultId for each row of the csv
//* * * OT 2-4-16 The code below (including call to SaveBatchResponses)
//* * * will be wrapped in another loop to handle large uploads in multiple batches
//iterate through the uploaded csv rows,
//use the heirarchy of headers to populate the heirarchy of responses with input data
int rowIndex = 0;
while (csvr.HasMoreRecords && rowIndex < rowCount)
{
AssmntRecord acl = new AssmntRecord(csvr.ReadDataRecord());
int formRsltId = acl.getInt("sis_id");
foreach (int itemId in csvHeadersByIvByItem.Keys)
{
Dictionary <int, string> csvHeadersByIv = csvHeadersByIvByItem[itemId];
Dictionary <int, string[]> rspValsByIv = rspValsByIvByItem[itemId];
foreach (int ivId in csvHeadersByIv.Keys)
{
rspValsByIv[ivId][rowIndex] = acl[csvHeadersByIv[ivId]];
}
}
formResultIds[rowIndex] = formRsltId;
rowIndex++;
}
//push all the data into the database at once.
//this does not require any reads, because the response heirarchy matches the DB schema (itemResults->responseVariables)
try
{
formsRepo.SaveBatchResponses(rspValsByIvByItem, formResultIds);
}
catch (Exception e)
{
Debug.WriteLine("* * * UploadAssmntCsv: failed to save formResultIds " + String.Join(", ", formResultIds));
}
}
return(Content("Upload Successful!"));
}