private string getCSVSecondRowLabelForFormResultTagName(FormResultExportTagName tagName) { switch (tagName) { case FormResultExportTagName.formResultId: return("SIS_ID"); case FormResultExportTagName.identifier: return("Form Name"); default: return(tagName.ToString()); } }
private FileContentResult BuildFormResultsCSV(List <string> leadingHeaders) { //debug Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " started"); string includeParts = Request["includeParts"] as string; Dictionary <string, int> formPartMap = (Dictionary <string, int>)Session["formPartMap"]; string formId = Request["formId"] as string; ExportModel exportModel = (ExportModel)Session["ExportModel"]; int iFormId = Convert.ToInt32(formId); List <def_Forms> forms = new List <def_Forms>(); if (iFormId == 0) { foreach (int?id in exportModel.formIds) { forms.Add(formsRepo.GetFormById(Convert.ToInt32(id))); } } else { forms.Add(formsRepo.GetFormById(iFormId)); } string outpath = ControllerContext.HttpContext.Server.MapPath("../Content/formResults_" + System.DateTime.Now.Ticks + ".csv"); Stream stream = new FileStream(outpath, FileMode.Create); CsvWriter writer = new CsvWriter(stream); // Build a map of with all relevent itemVariable identifiers Dictionary <int, string> ivIdentifiersById = new Dictionary <int, string>(); foreach (string leadingIdent in leadingHeaders) { int id = leadingIdent.StartsWith("FORMRESULT_") ? -1 - ivIdentifiersById.Count : formsRepo.GetItemVariableByIdentifier(leadingIdent).itemVariableId; ivIdentifiersById.Add(id, leadingIdent); } int i = 0; Dictionary <int, List <string> > formIdentMap = new Dictionary <int, List <string> >(); foreach (def_Forms form in forms) { List <string> map = new List <string>(); // Use the formPartMap when its not empty. i = (formPartMap.Count() > 0) ? formPartMap[form.formId + " start"] : 0; foreach (def_Parts prt in formsRepo.GetFormParts(form)) { if (prt.identifier.Contains("Scores")) { continue; } if (includeParts[i++] == '1') { //formPartMap.Add(form.formId + " " + prt.partId + " ident start", ivIdentifiersById.Count()); foreach (def_Sections sctn in formsRepo.GetSectionsInPart(prt)) { formsRepo.CollectItemVariableIdentifiersById(sctn, ivIdentifiersById); foreach (def_Items item in formsRepo.GetAllItemsForSection(sctn)) { map.AddRange(formsRepo.GetItemVariablesByItemId(item.itemId).Select(iv => iv.identifier).ToList()); } } Debug.WriteLine("Added more sections to ivIdentifiersById, form: " + form.formId + " part: " + prt.partId + " new Count: " + ivIdentifiersById.Count()); //formPartMap.Add(form.formId + " " + prt.partId + " ident end", ivIdentifiersById.Count()); } } formIdentMap.Add(form.formId, map); } //add additional columns for any unhandled data the comes back from CommonExport.GetFormResultValues foreach (FormResultExportTagName tagName in Enum.GetValues(typeof(FormResultExportTagName))) { string header = "FORMRESULT_" + tagName.ToString(); if (!ivIdentifiersById.Values.Contains(header)) { int id = -1 - ivIdentifiersById.Count; //not used, but must be unique to act as dictionary key ivIdentifiersById.Add(id, header); } } //debug Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished Build a map of with all column identifiers"); // Build a list of formresults to export List <int?> formResultIds = exportModel.formResultIds; List <def_FormResults> formResultsToExport; if (iFormId == 0) { formResultsToExport = new List <def_FormResults>(); foreach (def_Forms form in forms) { formResultsToExport.AddRange(formsRepo.GetFormResultsByFormId(form.formId).Where(r => formResultIds.Contains(r.formResultId)).ToList()); } } else { if (formResultIds == null) { formResultsToExport = formsRepo.GetFormResultsByFormId(iFormId).ToList(); } else { formResultsToExport = formsRepo.GetFormResultsByFormId(iFormId).Where(r => formResultIds.Contains(r.formResultId)).ToList(); } } Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished building a list of formresults to export"); //build a header record with identifiers int n = ivIdentifiersById.Count; List <int> ivIds = new List <int>(); List <string> identifiers = new List <string>(); List <string> headerText = new List <string>(); int j = 0; foreach (KeyValuePair <int, string> de in ivIdentifiersById) { ivIds.Add(de.Key); identifiers.Add(de.Value); headerText.Add(de.Value.Replace("FORMRESULT_", "")); j++; } HeaderRecord hr = new HeaderRecord(true, headerText); writer.WriteRecord(hr); Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished build a header record with identifiers"); //Build a DataRecord with item labels (second row in the output file) string[] values = new string[n]; //used to temporarily store the values for each row in the export for (int k = 0; k < n; k++) { if (identifiers[k].StartsWith("FORMRESULT_")) { FormResultExportTagName tag = (FormResultExportTagName)Enum.Parse(typeof(FormResultExportTagName), headerText[k]); values[k] = getCSVSecondRowLabelForFormResultTagName(tag); } else { values[k] = formsRepo.GetItemVariableById(ivIds[k]).def_Items.label; } } writer.WriteRecord(new DataRecord(hr, values)); Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished Build a DataRecord with item labels"); //insert access logs (one for each assessment being exported) int[] arrFrIds = formResultIds.Where(id => id.HasValue).Select(id => id.Value).ToArray(); AccessLogging.InsertMultipleAccessLogRecords(formsRepo, arrFrIds, (int)AccessLogging.accessLogFunctions.EXPORT, "Export CSV of assessment"); // Build a DataRecord for each form result int count = 0, total = formResultsToExport.Count(); foreach (def_FormResults fr in formResultsToExport) { //pull all necessary data for this formResult List <ValuePair> rspValues = CommonExport.GetDataByFormResultId(fr.formResultId); List <ValuePair> frValues = CommonExport.GetFormResultValues(fr, forms.Where(f => f.formId == fr.formId).FirstOrDefault()); //fill in values from formResult for (int k = 0; k < n; k++) { if (identifiers[k].StartsWith("FORMRESULT_")) { //values[k] = GetFormResultValue(fr, headerText[k]); ValuePair vp = frValues.Where(vpt => vpt.identifier == headerText[k]).FirstOrDefault(); if (vp != null) { values[k] = vp.rspValue; } else { values[k] = ""; } } } //replace reponse values (numbers) with meaningful text for certain cells //FormResults_formStatus status; //Enum.TryParse(values[colIndex_status], out status); //values[colIndex_status] = status.ToString(); //values[colIndex_sis_why] = GetDropdownText("assmtReason", values[colIndex_sis_why]); //fill in values from responseVariables for (i = 0; i < n; i++) { if (identifiers[i].StartsWith("FORMRESULT_")) { continue; } ValuePair vp = null; if (identifiers[i].StartsWith("FORMRESULT_") || formIdentMap[fr.formId].Contains(identifiers[i])) { vp = rspValues.Where(vpt => vpt.identifier == identifiers[i]).FirstOrDefault(); } values[i] = vp == null ? "" : vp.rspValue; } writer.WriteRecord(new DataRecord(hr, values)); //debug Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished " + (++count) + "/" + total + " records"); } writer.Close(); //<- calls stream.Close() //debug Debug.WriteLine(" * * * BuildFormResultsCSV - " + DateTime.Now.Ticks + " finished everything"); return(File(System.IO.File.ReadAllBytes(outpath), "text/csv", "results.csv")); }