public Func <HtmlHelper, IHtmlString> DisplayResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
var lDocs = (from r in context.DataMartResponses
from doc in r.Documents
orderby r.DataMart.ID
select new
{
Name = doc.Name,
ID = doc.ID,
doc = doc,
DataMartName = r.DataMart.Name,
RevisionVersion = doc.RevisionVersion
});
var docs = (from r in lDocs
group r by r.ID into grp
select new FileDistributionResponse
(
grp.FirstOrDefault().DataMartName,
grp.OrderByDescending(p => p.RevisionVersion).FirstOrDefault().doc
)).ToList();
return(html => html.Partial <FileDistribution.Views.FileDistribution.Display>().WithModel(new Models.FileDistributionResponseModel
{
ResponseFileList = docs.ToList()
}));
}
private DataSet UnaggregatesDataSet(DataSet dataSet, IDnsResponseContext context)
{
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
DataSet ds = new DataSet();
using (var docStream = new DocumentStream(db, doc.ID))
{
Random rnd = new Random(); //
int n = rnd.Next(); /////////
ds.ReadXml(docStream);
ds.Tables[0].TableName = r.DataMart.Name + "--" + n;//
ds.Tables[0].Columns.Add("DataMart");
ds.Tables[0].Columns["DataMart"].SetOrdinal(0);
foreach (DataRow row in ds.Tables[0].Rows)
{
row["DataMart"] = r.DataMart.Name;
}
DataView v = new DataView(ds.Tables[0]);
DataTable dt = v.ToTable();
dataSet.Tables.Add(dt);
}
}
}
}
return(dataSet);
}
private DataSet UnaggregateDataSet(DataSet _ds, IDnsResponseContext context)
{
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
if (doc.Viewable)
{
DataSet ds = new DataSet();
ds.ReadXml(doc.GetStream(db));
ds.Tables[0].TableName = r.DataMart.Name;
ds.Tables[0].Columns.Add("DataMart");
ds.Tables[0].Columns["DataMart"].SetOrdinal(0);
foreach (DataRow row in ds.Tables[0].Rows)
{
row["DataMart"] = r.DataMart.Name;
}
DataView v = new DataView(ds.Tables[0]);
DataTable dt = v.ToTable();
_ds.Tables.Add(dt);
}
}
}
}
return(_ds);
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format, string args)
{
using (StringWriter sw = new StringWriter())
{
DataSet ds = GetResponseDataSet(context, aggregationMode);
string filename = EXPORT_BASENAME + "_" + context.Request.Model.Name + "_" + context.Request.RequestID.ToString() + "." + format.ID;
switch (format.ID)
{
case "xlsx":
ExcelHelper.ToExcel(ds, filename, HttpContext.Current.Response);
break;
case "csv":
ExcelHelper.ToCSV(ds, sw);
break;
}
return(Dns.Document(
name: filename,
mimeType: GetMimeType(filename),
isViewable: false,
kind: DocumentKind.User,
Data: Encoding.UTF8.GetBytes(sw.ToString())
));
}
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format, string args)
{
using (StringWriter sw = new StringWriter())
{
DataSet ds = GetResponseDataSet(context);
switch (format.ID)
{
case "xls":
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htw);
}
break;
case "csv":
DataTable dt = ds.Tables[0];
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
// Now write all the rows.
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
break;
}
string filename = EXPORT_BASENAME + "_" + context.Request.Model.Name + "_" + context.Request.RequestID.ToString() + "." + format.ID;
return(Dns.Document(
name: filename,
mimeType: GetMimeType(filename),
isViewable: false,
kind: DocumentKind.User,
Data: Encoding.UTF8.GetBytes(sw.ToString())
));
}
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format, string args)
{
using (var db = new DataContext())
{
string reqDoc = FixDocumentContent(System.Text.UTF8Encoding.UTF8.GetString(context.Request.Documents.Where(d => d.Name == REQUEST_ARGS_FILENAME).FirstOrDefault().GetData(db)));
using (StringWriter sw = new StringWriter())
{
DataSet ds = GetResponseDataSet(context, reqDoc, aggregationMode);
string filename = EXPORT_BASENAME + "_" + context.Request.Model.Name + "_" + context.Request.RequestID.ToString() + "." + format.ID;
switch (format.ID)
{
case "xlsx":
ExcelHelper.ToExcel(ds, filename, HttpContext.Current.Response);
break;
case "csv":
ExcelHelper.ToCSV(ds, sw);
break;
}
return(Dns.Document(
name: filename,
mimeType: GetMimeType(filename),
isViewable: false,
kind: DocumentKind.User,
Data: Encoding.UTF8.GetBytes(sw.ToString())
));
}
}
}
public IEnumerable <IDnsResponseExportFormat> GetExportFormats(IDnsResponseContext context)
{
return(new[] {
Dns.ExportFormat("xlsx", "Excel"),
Dns.ExportFormat("csv", "CSV")
});
}
private DataSet GroupDataSet(DataSet _ds, IDnsResponseContext context, DataTable dataTable, string name)
{
#region GENERATE_DISTINCT_ROWS
// Get the columns to do a distinct selection on.
string[] colNames = (from DataColumn c in dataTable.Columns
where c.ColumnName != "Patients" && c.ColumnName != "Population_Count" && c.ColumnName != "DataMart"
select c.ColumnName).ToArray <string>();
// Get a view of the current table and create a table of distinct rows based on the column names above.
DataView v = new DataView(dataTable);
DataTable dt = v.ToTable(true, colNames); // Add only distinct rows.
if (colNames.Length == 0) // If no column to be distinct on, collapse to one cell.
{
dt.Clear();
dt.Rows.Add(dt.NewRow());
}
// Add the non-distinct columns back.
if (!dt.Columns.Contains("Patients"))
{
dt.Columns.Add("Patients", typeof(long));
}
if (!dt.Columns.Contains("DataMart"))
{
DataColumn col = dt.Columns.Add("DataMart", typeof(string));
col.SetOrdinal(0);
}
#endregion
#region COMPUTE_AGGREGATION_COLUMNS
// For each row, if the distinct column values match, add up the aggregating columns.
foreach (DataRow row in dt.Rows)
{
// Create the select distinct filter based on the non-aggregating columns determined above.
string filter = "";
foreach (string colName in colNames)
{
filter += string.Format("[{0}]='{1}' ", colName, row[colName].ToString());
if (colName != colNames.Last <string>())
{
filter += "and ";
}
}
// Compute the aggregate patients value.
row["Patients"] = dataTable.Compute("Sum(" + "Patients" + ")", filter);
row["DataMart"] = name;
}
#endregion
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return(ds);
}
private DataSet GetResponseDataSet(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
DataSet _ds = new DataSet();
_ds = aggregationMode == SQLViewOptions.AggregateView || aggregationMode == null?AggregatedDataSet(_ds, context) : IndividualDataSet(_ds, context);
return(_ds);
}
private DataSet IndividualDataSet(DataSet _ds, IDnsResponseContext context)
{
using (var db = new DataContext())
{
var requests = db.Requests.Where(r => r.ID == context.Request.RequestID);
var individualResponses = requests.SelectMany(rq => rq.DataMarts.SelectMany(dm => dm.Responses).Where(r => r.ResponseGroupID == null)).ToArray();
var groupedResponses = requests.SelectMany(rq => rq.DataMarts.SelectMany(dm => dm.Responses).Where(r => r.ResponseGroupID != null).GroupBy(g => g.ResponseGroupID));
foreach (var vr in individualResponses)
{
var rdmID = vr.RequestDataMartID;
var dmIDs = db.RequestDataMarts.Where(rdm => rdmID == rdm.ID).Select(rdm => rdm.DataMartID);
var dmrs = context.DataMartResponses.Where(dmr => dmIDs.Contains(dmr.DataMart.ID));
DataSet ds = MergeUnaggregatedDataSet(new DataSet(), dmrs);
if (ds.Tables.Count <= 0)
{
continue;
}
string dmName = dmrs.Select(dm => dm).FirstOrDefault().DataMart.Name;
ds.Tables[0].TableName = dmName;
DataView v = new DataView(ds.Tables[0]);
DataTable dt = v.ToTable();
_ds.Tables.Add(dt);
}
foreach (var gr in groupedResponses)
{
var rdmIDs = gr.Select(resp => resp.RequestDataMartID);
var dmIDs = db.RequestDataMarts.Where(rdm => rdmIDs.Contains(rdm.ID)).Select(rdm => rdm.DataMartID);
var dmrs = context.DataMartResponses.Where(dmr => dmIDs.Contains(dmr.DataMart.ID));
DataSet ds = MergeUnaggregatedDataSet(new DataSet(), dmrs);
if (ds.Tables.Count <= 0)
{
continue;
}
string dmName = dmrs.Select(dm => dm).FirstOrDefault().DataMart.Name;
var respGroupID = gr.Select(resp => resp).FirstOrDefault().ResponseGroupID;
string name = respGroupID == null ? dmName : db.ResponseGroups.Where(rg => rg.ID == respGroupID).FirstOrDefault().Name;
ds = GroupDataSet(new DataSet(), context, ds.Tables[0], name);
ds.Tables[0].TableName = name;
DataView v = new DataView(ds.Tables[0]);
DataTable dt = v.ToTable();
_ds.Tables.Add(dt);
}
return(_ds);
}
}
private DataSet GetResponseDataSet(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
DataSet _ds = new DataSet();
_ds = aggregationMode == ConditionsAggregationModes.AggregateView || aggregationMode == null?
AggregateDataSet(_ds, context) : UnaggregateDataSet(_ds, context);
return(_ds);
}
private DataSet GetResponseDataSet(IDnsResponseContext context, string reqDoc, IDnsResponseAggregationMode aggregationMode)
{
var termData = RequestCriteriaHelper.ToServerModel(reqDoc).Criterias.First().Terms;
var mdData = termData.Where(t => t.TermType == RequestCriteria.Models.TermTypes.MetaDataTableTerm).FirstOrDefault() as MetaDataTableData;
DataSet ds = new DataSet();
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
try
{
ds.ReadXml(doc.GetStream(_dataContext.Value));
}
catch (System.Xml.XmlException)
{
string content = FixDocumentContent(doc.ReadStreamAsString(_dataContext.Value));
using (var ms = new MemoryStream(System.Text.Encoding.Default.GetBytes(content)))
{
ds.ReadXml(ms);
}
}
if (doc.Name == "DataCheckerResponse.xml")
{
if (context.Request.RequestType.ID == Guid.Parse(DataCheckerRequestType.DATA_CHECKER_METADATA_COMPLETENESS))
{
foreach (string unselectedMetadataTable in GetUnselectedMetaDataTables(mdData.Tables))
{
if (ds.Tables[ds.Tables.Count - 1].Columns.Contains(unselectedMetadataTable))
{
ds.Tables[ds.Tables.Count - 1].Columns.Remove(unselectedMetadataTable);
}
}
}
foreach (DataRow row in ds.Tables[ds.Tables.Count - 1].Rows)
{
if (context.Request.RequestType.ID == Guid.Parse(DataCheckerRequestType.DATA_CHECKER_DISPENSING_RXAMT))
{
row["RxAmt"] = FormatRxAmt((string)row["RxAmt"]);
}
else if (context.Request.RequestType.ID == Guid.Parse(DataCheckerRequestType.DATA_CHECKER_DISPENSING_RXSUP))
{
row["RxSup"] = FormatRxSup((string)row["RxSup"]);
}
}
}
}
}
return(ds);
}
public Func <HtmlHelper, IHtmlString> DisplayResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
try
{
return(html => html.Partial <Grid>().WithModel(GetResponseDataSet(context, aggregationMode)));
}
catch (Exception ex)
{
return(html => html.Partial <Views.Error>().WithModel(new InvalidDataSetException(ex)));
}
}
static public DataSet UnaggregateQueryResults(IDnsResponseContext context)
{
StringReader sr;
DataSet _ds = new DataSet();
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
//Query q = Query.Load(queryId);
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
DataSet ds = new DataSet();
bool dataMartResponsesExist = false; // Assume false until proven true
foreach (var doc in r.Documents)
{
var responseXmlString = doc.ReadStreamAsString(db).Replace("Results", "Table");
if (!string.IsNullOrEmpty(responseXmlString))
{
dataMartResponsesExist = true;
responseXmlString = responseXmlString.Replace("Total_x0020_Enrollment_x0020_in_x0020_Strata_x0028_Members_x0029_", "Total_Enrollment_in_Strata_Members");
responseXmlString = responseXmlString.Replace("Days_x0020_Covered", "Days_Covered");
responseXmlString = responseXmlString.Replace("Prevalence_x0020_Rate_x0020__x0028_Users_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Prevalence_Rate_Users_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Dispensing_x0020_Rate_x0020__x0028_Dispensings_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Dispensing_Rate_Dispensings_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Days_x0020_Per_x0020_Dispensing", "Days_Per_Dispensing");
responseXmlString = responseXmlString.Replace("Days_x0020_Per_x0020_user", "Days_Per_user");
responseXmlString = responseXmlString.Replace("Event_x0020_Rate_x0020__x0028_Events_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Event_Rate_Events_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Events_x0020_Per_x0020_member", "Events_Per_member");
sr = new StringReader(responseXmlString);
ds.ReadXml(sr);
}
}
if (dataMartResponsesExist && ds.Tables.Count > 0)
{
DataView v = new DataView(GetQueryResults(context, true, ds).Tables[0]);
DataTable dt = v.ToTable();
dt.TableName = r.DataMart.Name;
dt.Columns.Add("DataMart");
dt.Columns["DataMart"].SetOrdinal(0);
foreach (DataRow row in dt.Rows)
{
row["DataMart"] = r.DataMart.Name;
}
_ds.Tables.Add(dt);
}
}
}
return(_ds);
}
static private DataSet GetQueryResults(IDnsResponseContext context, bool ExpandResults, DataSet ds)
{
DataSetHelper dsHelper = new DataSetHelper(ref ds);
//string GenderColumnName =(ds.Tables[0].Columns.Contains("gender"))? "gender Sex" : "Sex";
//string SortColumnName = (ds.Tables[0].Columns.Contains("gender"))? "gender" : "Sex";
SummaryRequestType requestType = SummaryRequestType.All.FirstOrDefault(rt => rt.ID == context.Request.RequestType.ID);
GroupResults(ref ds, requestType);
string aggregatedXmlString = ds.GetXml();
//reading the xml in to a dataset does not allow spaces for the columns. So replace with apprpriate hex values.
aggregatedXmlString = aggregatedXmlString.Replace("Total_Enrollment_in_Strata_Members", "Total_x0020_Enrollment_x0020_in_x0020_Strata_x0028_Members_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Covered", "Days_x0020_Covered");
aggregatedXmlString = aggregatedXmlString.Replace("Prevalence_Rate_Users_per_1000_enrollees", "Prevalence_x0020_Rate_x0020__x0028_Users_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Dispensing_Rate_Dispensings_per_1000_enrollees", "Dispensing_x0020_Rate_x0020__x0028_Dispensings_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Per_Dispensing", "Days_x0020_Per_x0020_Dispensing");
aggregatedXmlString = aggregatedXmlString.Replace("Days_Per_user", "Days_x0020_Per_x0020_user");
aggregatedXmlString = aggregatedXmlString.Replace("Event_Rate_Events_per_1000_enrollees", "Event_x0020_Rate_x0020__x0028_Events_x0020_per_x0020_1000_x0020_enrollees_x0029_");
aggregatedXmlString = aggregatedXmlString.Replace("Events_Per_member", "Events_x0020_Per_x0020_member");
ds = new DataSet();
StringReader sr = new StringReader(aggregatedXmlString);
ds.ReadXml(sr);
#region "Expand the result to contain all combinations of Period,AgeGroup,Sex,Setting,Code"
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
string summaryRequestArgsXml = "";
if (ExpandResults)
{
DataSet dsExpandedResult = new DataSet();
if (ds.Tables.Count > 0)
{
DataTable dtMerged = QueryUtil.ExpandSummaryResults(ds.Tables[0], summaryRequestArgsXml, context);
if (dtMerged != null && dtMerged.Rows.Count > 0)
{
dsExpandedResult.Tables.Clear();
dsExpandedResult.Tables.Add(dtMerged);
ds = dsExpandedResult;
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
//AddComputedColumnsIfNotExist(q, ref ds);
}
}
}
#endregion
return(ds);
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format, string args)
{
using (StringWriter sw = new StringWriter())
{
string filename = EXPORT_BASENAME + "_" + context.Request.Model.Name + "_" + context.Request.RequestID.ToString() + "." + format.ID;
var DMDocs = new SortedDictionary <string, List <Document> >();
#region "Generate a List of Documents Keyed on DataMarts for Aggregation"
//IEnumerable<IDnsPersistentDocument> docs = null;
var docs = from r in context.DataMartResponses
from doc in r.Documents
orderby r.DataMart.ID
select new { Id = r.DataMart.ID, Document = doc };
foreach (var item in docs)
{
if (!DMDocs.ContainsKey(item.Id.ToString()))
{
DMDocs.Add(item.Id.ToString(), new List <Document>());
}
DMDocs[item.Id.ToString()].Add(item.Document);
}
#endregion
//Aggregate and Download
Workbook AggregatedWorkBook = null;
try
{
AggregatedWorkBook = AggregateDocuments(DMDocs);
if (AggregatedWorkBook != null)
{
//present for download.
string DownloadFileName = filename;
//string DownloadPath = System.Web.Configuration.WebConfigurationManager.AppSettings["DownloadFolder"]+ @"\" + DownloadFileName;
AggregatedWorkBook.Save(HttpContext.Current.Response, DownloadFileName, ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
catch (Exception ex)
{
sw.WriteLine("Error Encountered During Aggregation Process.");
sw.WriteLine(ex.Message);
sw.WriteLine(ex.StackTrace);
}
return(null);
}
}
private DataSet GetResponseDataSet(IDnsResponseContext context)
{
DataSet _ds = new DataSet();
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
using (var docStream = new DocumentStream(db, doc.ID))
{
_ds.ReadXml(docStream);
}
}
}
// Get a data view with the non-aggregating columns.
IEnumerable <string> colNames = from DataColumn c in _ds.Tables[0].Columns
where c.ColumnName != "Patients"
select c.ColumnName;
string[] colFilter = colNames.ToArray <string>();
DataView v = new DataView(_ds.Tables[0]);
DataTable dt = v.ToTable(true, colFilter);
// Add the aggregating columns back.
dt.Columns.Add("Patients");
// For each row, if the non-aggregating column values match, add up the aggregating column.
foreach (DataRow row in dt.Rows)
{
string filter = "";
foreach (string colName in colNames)
{
filter += string.Format("[{0}]='{1}' ", colName, row[colName].ToString());
if (colName != colNames.Last <string>())
{
filter += "and ";
}
}
row["Patients"] = _ds.Tables[0].Compute("Sum(Patients)", filter);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return(ds);
}
}
/// <summary>
/// Get results for specified Query. Performs result aggregation.
/// </summary>
static public DataSet GetQueryResults(IDnsResponseContext context, bool ExpandResults)
{
bool dataMartResponsesExist = false; // Assume false until proven true
StringReader sr;
// TODO: Replace with code that does not use deprecated DRN 2.x classes!
//Query q = Query.Load(queryId);
DataSet ds = new DataSet();
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
string responseXmlString = new StreamReader(doc.GetStream(db)).ReadToEnd().Replace("Results", "Table");
if (!string.IsNullOrEmpty(responseXmlString))
{
dataMartResponsesExist = true;
responseXmlString = responseXmlString.Replace("Total_x0020_Enrollment_x0020_in_x0020_Strata_x0028_Members_x0029_", "Total_Enrollment_in_Strata_Members");
responseXmlString = responseXmlString.Replace("Days_x0020_Covered", "Days_Covered");
responseXmlString = responseXmlString.Replace("Prevalence_x0020_Rate_x0020__x0028_Users_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Prevalence_Rate_Users_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Dispensing_x0020_Rate_x0020__x0028_Dispensings_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Dispensing_Rate_Dispensings_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Days_x0020_Per_x0020_Dispensing", "Days_Per_Dispensing");
responseXmlString = responseXmlString.Replace("Days_x0020_Per_x0020_user", "Days_Per_user");
responseXmlString = responseXmlString.Replace("Event_x0020_Rate_x0020__x0028_Events_x0020_per_x0020_1000_x0020_enrollees_x0029_", "Event_Rate_Events_per_1000_enrollees");
responseXmlString = responseXmlString.Replace("Events_x0020_Per_x0020_member", "Events_Per_member");
sr = new StringReader(responseXmlString);
ds.ReadXml(sr); // TODO: merge!
}
}
}
if (!dataMartResponsesExist)
{
return(null);
}
if (ds.Tables.Count < 1)
{
return(ds);
}
return(GetQueryResults(context, ExpandResults, ds));
}
}
public Func <HtmlHelper, IHtmlString> DisplayResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
var m = new ModularProgramModel();
IList <SignatureDatum> signatureDataList = new List <SignatureDatum>();
using (var db = new DataContext())
{
db.RequestSearchTerms.Where(term => term.RequestID == context.Request.RequestID).ForEach(term =>
signatureDataList.Add(new SignatureDatum
{
Variable = Enum.GetName(typeof(RequestSearchTermType), term.Type),
Value = term.StringValue
}));
m.SignatureData = JsonConvert.SerializeObject(signatureDataList);
m.HasResponses = ResponseService.GetVirtualResponses(context.Request.RequestID, true).Count() > 0;
//PMNDEV-4421
var lDocs = (from r in context.DataMartResponses
from doc in r.Documents
orderby r.DataMart.ID
select new
{
Name = doc.Name,
Length = doc.Length,
ID = doc.ID,
DataMartName = r.DataMart.Name,
RevisionVersion = doc.RevisionVersion
});
var docs = from r in lDocs
group r by r.DataMartName into grp
select new FileSelection
(
grp.FirstOrDefault().Name,
grp.OrderByDescending(p => p.RevisionVersion).FirstOrDefault().Length,
grp.OrderByDescending(p => p.RevisionVersion).FirstOrDefault().ID,
grp.FirstOrDefault().DataMartName,
FileHelper.GetMimeType(grp.FirstOrDefault().Name)
);
m.RequestFileList = docs.ToList();
return(html => html
.Partial <Views.ModularProgram.DisplayResponse>()
.WithModel(m));
}
}
private DataSet AggregatedDataSet(DataSet dataSet, IDnsResponseContext context)
{
DataSet ds = new DataSet();
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
using (var docStream = new DocumentStream(db, doc.ID))
{
ds.ReadXml(docStream);
}
}
}
}
return(ds);
}
public Func <HtmlHelper, IHtmlString> DisplayResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
throw new Lpp.Utilities.CodeToBeUpdatedException();
//var docs = from d in context.DataMartResponses
// from doc in d.Documents
// select doc;
//var dm = (
// from m in Models.All
// from r in m.RequestTypes
// where r.LocalId == context.Request.RequestType.Id
// select new { m, r }
// ).FirstOrDefault();
//return html => html.Partial<Views.Bridge.ResponseView>().WithModel(new Models.ResponseDetailModel
//{
// Context = context,
// Model = dm.m,
// RequestType = dm.r
//});
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format, string args)
{
using (StringWriter sw = new StringWriter())
{
string filename = EXPORT_BASENAME + "_" + context.Request.Model.Name + "_" + context.Request.RequestID.ToString() + "." + format.ID;
var DMDocs = new SortedDictionary <string, List <Document> >();
var lDocs = (from r in context.DataMartResponses
from doc in r.Documents
orderby r.DataMart.ID
select new
{
Doc = doc,
Name = doc.Name,
ID = doc.ID,
DataMartName = r.DataMart.Name,
RevisionVersion = doc.RevisionVersion
});
var docs = from r in lDocs
group r by r.DataMartName into grp
select new KeyValuePair <string, Document>
(
grp.FirstOrDefault().DataMartName,
grp.OrderByDescending(p => p.RevisionVersion).FirstOrDefault().Doc
);
ZipHelper.DownloadZipToBrowser(HttpContext.Current, docs, filename);
return(Dns.Document(
name: filename,
mimeType: FileHelper.GetMimeType(filename),
isViewable: false,
kind: DocumentKind.User,
Data: Encoding.UTF8.GetBytes(sw.ToString())
));
}
}
public IDnsDocument ExportResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode, IDnsResponseExportFormat format)
{
throw new NotImplementedException();
}
public Func <HtmlHelper, IHtmlString> DisplayResponse(IDnsResponseContext context, IDnsResponseAggregationMode aggregationMode)
{
return(null);
}
public IEnumerable <IDnsResponseExportFormat> GetExportFormats(IDnsResponseContext context)
{
return(null);
}
private DataSet AggregateDataSet(DataSet _ds, IDnsResponseContext context)
{
string POPULATION_COUNT = "Population_Count";
//string POPULATION_PERCENT = "Population_Percent";
//double totalPopulation = 0.0;
DataSet _demoDS = new DataSet();
using (var db = new DataContext())
{
foreach (var r in context.DataMartResponses)
{
foreach (var doc in r.Documents)
{
if (doc.Name.LastIndexOf(".") > 0 && doc.Name.Substring(doc.Name.LastIndexOf(".") + 1) != "json")
{
if (doc.Viewable)
{
_ds.ReadXml(doc.GetStream(db));
}
else
{
_demoDS.ReadXml(doc.GetStream(db));
}
}
}
}
}
// Get a data view with the non-aggregating columns.
// Get the columns to do a distinct selection on.
string[] colNames = (from DataColumn c in _ds.Tables[0].Columns
where c.ColumnName != "Patients" && c.ColumnName != POPULATION_COUNT /* && c.ColumnName != "Description" */
select c.ColumnName).ToArray <string>();
// Get a view of the current table and create a table of distinct rows based on the column names above.
DataView v = new DataView(_ds.Tables[0]);
DataTable dt = v.ToTable(true, colNames); // Add only distinct rows.
// Add the non-distinct columns back.
//dt.Columns.Add("Description");
//dt.Columns["Description"].SetOrdinal(1);
if (!dt.Columns.Contains("Patients"))
{
dt.Columns.Add("Patients");
}
// For each row, if the distinct column values match,
// copy the first value of the non-distinct column and add up the aggregating column.
foreach (DataRow row in dt.Rows)
{
// Create the select filter based on the distinct columns above.
string filter = "";
foreach (string colName in colNames)
{
filter += string.Format("[{0}]='{1}' ", colName, row[colName].ToString());
if (colName != colNames.Last <string>())
{
filter += "and ";
}
}
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
return(ds);
}
public IEnumerable <IDnsResponseExportFormat> GetExportFormats(IDnsResponseContext context)
{
return(new[] {
Dns.ExportFormat(".zip", "Zip"),
});
}
//[Import] public static IRepository<SummaryDomain, StratificationAgeRangeMapping> StratificationAgeRanges { get; set; }
//[Import] public static IRepository<SummaryDomain, LookupListValue> LookupListValues { get; set; }
private static DataTable GenerateSummaryQueryResultCombinations(string summaryRequestArgsXml, IDnsResponseContext context)
{
SummaryRequestType requestType = SummaryRequestType.All.FirstOrDefault(rt => rt.ID == context.Request.RequestType.ID);
DataTable dt = new DataTable("Results");
if (!string.IsNullOrEmpty(summaryRequestArgsXml))
{
DataColumn col;
DataRow dr;
string PeriodColumn = "Period";
string SettingColumn = "Setting";
string SexColumn = "Sex";
string AgeColumn = "AgeGroup";
string CodeColumn = string.Empty;
string NameColumn = string.Empty;
bool IsQualifiedForExpansion = false;
bool IsDrugOrGenericName = false;
Lists? CodeList = null;
col = new DataColumn(PeriodColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(SexColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(AgeColumn, "string".GetType());
dt.Columns.Add(col);
col = new DataColumn(SettingColumn, "string".GetType());
dt.Columns.Add(col);
#region "Read Query.XML and populate rows"
XmlDataDocument summaryRequestArgsDoc = new XmlDataDocument();
summaryRequestArgsDoc.LoadXml(summaryRequestArgsXml);
XPathNavigator Nav = summaryRequestArgsDoc.CreateNavigator();
XPathExpression Expr;
XPathNodeIterator INode = null;
string nodeValue = string.Empty;
string[] Years = new string[] { };
IEnumerable <StratificationAgeRangeMapping> SelectedStratificationAgeRanges = null;
List <string> Genders = new List <string>();
List <string> Settings = new List <string>();
string[] Codes = new string[] { };
List <string> Names = new List <string>();
switch (requestType.StringId)
{
case SummaryRequestType.GenericName:
case SummaryRequestType.Incident_GenericName:
CodeList = Lists.GenericName;
break;
case SummaryRequestType.DrugClass:
case SummaryRequestType.Incident_DrugClass:
CodeList = Lists.DrugClass;
break;
//case SummaryRequestType.NDC:
// CodeList = Lists.DrugCode;
// break;
case SummaryRequestType.ICD9Diagnosis:
case SummaryRequestType.Incident_ICD9Diagnosis:
CodeList = Lists.ICD9Diagnosis;
break;
case SummaryRequestType.ICD9Diagnosis_4_digit:
CodeList = Lists.ICD9Diagnosis4Digits;
break;
case SummaryRequestType.ICD9Diagnosis_5_digit:
CodeList = Lists.ICD9Diagnosis5Digits;
break;
case SummaryRequestType.ICD9Procedures:
CodeList = Lists.ICD9Procedures;
break;
case SummaryRequestType.ICD9Procedures_4_digit:
CodeList = Lists.ICD9Procedures4Digits;
break;
case SummaryRequestType.HCPCSProcedures:
CodeList = Lists.HCPCSProcedures;
break;
}
#region "Get Names for code column e.g. pxcode,dxcode,drugclass,etc..and name column e.g. pxname,dxname,etc.."
//The Generation of Combination Rows and Subsequent Expansion Of results applicable only to Prevalence,Incidence Queries.
switch (requestType.StringId)
{
case SummaryRequestType.ICD9Diagnosis:
case SummaryRequestType.ICD9Diagnosis_4_digit:
case SummaryRequestType.ICD9Diagnosis_5_digit:
case SummaryRequestType.Incident_ICD9Diagnosis:
CodeColumn = "DxCode";
NameColumn = "DxName";
IsQualifiedForExpansion = true;
break;
case SummaryRequestType.ICD9Procedures:
case SummaryRequestType.ICD9Procedures_4_digit:
case SummaryRequestType.HCPCSProcedures:
CodeColumn = "PxCode";
NameColumn = "PxName";
IsQualifiedForExpansion = true;
break;
case SummaryRequestType.GenericName:
case SummaryRequestType.Incident_GenericName:
CodeColumn = string.Empty;
NameColumn = "GenericName";
IsQualifiedForExpansion = true;
IsDrugOrGenericName = true;
break;
case SummaryRequestType.DrugClass:
case SummaryRequestType.Incident_DrugClass:
CodeColumn = string.Empty;
NameColumn = "DrugClass";
IsQualifiedForExpansion = true;
IsDrugOrGenericName = true;
break;
}
#endregion
if (IsQualifiedForExpansion)
{
if (!string.IsNullOrEmpty(CodeColumn))
{
col = new DataColumn(CodeColumn, "string".GetType());
dt.Columns.Add(col);
}
if (!string.IsNullOrEmpty(NameColumn))
{
col = new DataColumn(NameColumn, "string".GetType());
dt.Columns.Add(col);
}
//Read Selected Periods e.g. 2016,2017, etc.
Expr = Nav.Compile("SummaryRequestModel/Period");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
// The Period node contains a list of SINGLE-QUOTED years or quarters (for direct insertion into a SQL statement).
// We need to remove the quotes, along with any whitespace, for our purposes here.
nodeValue = INode.Current.Value.Replace("'", "").Replace(" ", "");
if (!string.IsNullOrEmpty(nodeValue))
{
Years = nodeValue.Split(',');
}
}
//Read Selected Sex stratifications
Expr = Nav.Compile("SummaryRequestModel/SexStratification");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
if (!string.IsNullOrEmpty(nodeValue))
{
switch (nodeValue)
{
case "1": //Female only
Genders.Add("F");
break;
case "2": //Male only
Genders.Add("M");
break;
case "3": //Male and Female
Genders.Add("F");
Genders.Add("M");
break;
case "4": //Male,Female Aggregated
Genders.Add("All");
break;
}
}
}
//Read Selected Age stratifications
Expr = Nav.Compile("SummaryRequestModel/AgeStratification");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
int ageStratificationCategoryId;
if (Int32.TryParse(nodeValue, out ageStratificationCategoryId))
{
throw new Lpp.Utilities.CodeToBeUpdatedException();
//SelectedStratificationAgeRanges = StratificationAgeRanges.All.Where(s => s.AgeStratificationCategoryId == ageStratificationCategoryId).ToList();
}
}
//Read Selected setting
Expr = Nav.Compile("SummaryRequestModel/Setting");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
nodeValue = INode.Current.Value.Trim();
if (!string.IsNullOrEmpty(nodeValue))
{
//The value 'NotSpecified' means no setting selected i.e. all settings considerred.
if (nodeValue.Trim().ToLower() != "notspecified")
{
Settings.Add(nodeValue);
}
else
{
Settings.Add("All Settings");
}
}
}
//Read Selected Codes and their names.
if (CodeList != null)
{
Expr = Nav.Compile("SummaryRequestModel/Codes");
INode = Nav.Select(Expr);
if (INode.MoveNext())
{
// Remove any spaces in the comma-delimited list of codes.
nodeValue = INode.Current.Value.Replace(" ", "");
if (!string.IsNullOrEmpty(nodeValue))
{
Codes = nodeValue.Split(',');
}
// Look up the names from the codes.
LookupListValue matchingNames;
foreach (string code in Codes)
{
// TODO: Verify this works properly if there's no match.
throw new Lpp.Utilities.CodeToBeUpdatedException();
//matchingNames = LookupListValues.All.FirstOrDefault(v => v.ListId == (int)CodeList && v.ItemCode == code);
//if (matchingNames != null)
//{
// Names.Add(matchingNames.ItemName);
//}
//else
//{
// Names.Add(string.Empty);
//}
}
}
}
//Create Combination Rows based on selected Period,AgeGroup,Sex,Setting and Codes.
if (Years.Length > 0 && SelectedStratificationAgeRanges != null && SelectedStratificationAgeRanges.Count() > 0 && Genders.Count > 0 && Settings.Count > 0)
{
foreach (string year in Years)
{
foreach (string sex in Genders)
{
foreach (StratificationAgeRangeMapping ageRange in SelectedStratificationAgeRanges)
{
foreach (string setting in Settings)
{
//If Code exists for Non-drug/generic Prevalence/Incidence queries.
if (Codes.Length > 0)
{
for (int ctr = 0; ctr < Codes.Length; ctr++)
{
dr = dt.NewRow();
dr[PeriodColumn] = year;
dr[SexColumn] = sex;
dr[AgeColumn] = ageRange.AgeClassification;
dr[SettingColumn] = setting;
dr[CodeColumn] = Codes[ctr];
if (Names.Count > ctr)
{
dr[NameColumn] = Names[ctr];
}
dt.Rows.Add(dr);
}
}
else
{
//Code does not exist for Non-drug/generic Prevalence/Incidence queries.
for (int ctr = 0; ctr < Names.Count; ctr++)
{
dr = dt.NewRow();
dr[PeriodColumn] = year;
dr[SexColumn] = sex;
dr[AgeColumn] = ageRange.AgeClassification;
dr[SettingColumn] = setting;
if (Codes.Length > ctr)
{
dr[CodeColumn] = Codes[ctr];
}
dr[NameColumn] = Names[ctr];
dt.Rows.Add(dr);
}
}
}
}
}
}
}
//For Drug or Generic Query No need of Settings Columns.
if (IsDrugOrGenericName)
{
if (dt.Columns.Contains(SettingColumn))
{
dt.Columns.Remove(SettingColumn);
}
}
}
#endregion
}
return(dt);
}
public static DataTable ExpandSummaryResults(DataTable RowsWithData, string QueryXML, IDnsResponseContext context)
{
DataTable CombinationRows = GenerateSummaryQueryResultCombinations(QueryXML, context);
if (CombinationRows != null && CombinationRows.Rows.Count > 0)
{
return(ExpandSummaryResults(CombinationRows, RowsWithData, context));
}
else
{
return(null);
}
}
public static DataTable ExpandSummaryResults(DataTable CombinationRows, DataTable RowsWithData, IDnsResponseContext context)
{
DataTable MergedTable = null;
string AgeColumn = "AgeGroup";
string SexColumn = "Sex";
string PeriodColumn = "Period";
string EnrollmentColumn = "Total Enrollment in Strata(Members)";
if (CombinationRows != null && CombinationRows.Rows.Count > 0)
{
string FilterExpression = string.Empty;
#region "Create Table Structure For Merged Results"
//MergedTable gets the structure of CombinationRows.
//Then Columns from RowsWithData are Copied into MergedTable structure.
MergedTable = CombinationRows.Copy();
foreach (DataColumn dcol in RowsWithData.Columns)
{
if (!MergedTable.Columns.Contains(dcol.ColumnName))
{
DataColumn newCol = MergedTable.Columns.Add(dcol.ColumnName, "string".GetType());
}
}
//For all new columns copied from RowsWithData, initialize with value "0"
foreach (DataRow dr in MergedTable.Rows)
{
foreach (DataColumn dc in MergedTable.Columns)
{
if (!CombinationRows.Columns.Contains(dc.ColumnName))
{
dr[dc.ColumnName] = "0";
}
}
}
#endregion
#region "Merge RowsWithData into MergedTable."
/*LOGIC
* For Each Row Containing Result Data in RowsWithData, Find Matching Combination Rows in MergedTable.
* Copy the data From RowsWithData row into matching combination rows.
*/
foreach (DataRow drow in RowsWithData.Rows)
{
FilterExpression = string.Empty;
foreach (DataColumn dcol in CombinationRows.Columns)
{
//Do not match with pxname and dxname, they can differ in data mart client database than what is selected in portal. Matching with pxcode,dxcode only makes sense.
if (dcol.ColumnName.ToLower() != "pxname" && dcol.ColumnName.ToLower() != "dxname" && RowsWithData.Columns.Contains(dcol.ColumnName) &&
drow[dcol.ColumnName] != null && !string.IsNullOrEmpty(drow[dcol.ColumnName].ToString()))
{
FilterExpression = FilterExpression + (string.IsNullOrEmpty(FilterExpression) ? "" : " AND ") + "Trim(" + dcol.ColumnName + ")='" + drow[dcol.ColumnName].ToString().Trim() + "'";
}
}
DataRow[] MatchingRows = MergedTable.Select(FilterExpression);
//Copy the data from Matching Row to the Associated MergedTable.
if (MatchingRows != null && MatchingRows.Length > 0)
{
foreach (DataRow drMerged in MatchingRows)
{
foreach (DataColumn dc in drow.Table.Columns)
{
if (!CombinationRows.Columns.Contains(dc.ColumnName))
{
if ((drow[dc.ColumnName] != null && !string.IsNullOrEmpty(drow[dc.ColumnName].ToString())))
{
drMerged[dc.ColumnName] = drow[dc.ColumnName];
}
}
}
}
}
}
#region "Copy Enrollment Totals as they are based on AgeGroup, Sex, Period only"
if (RowsWithData.Columns.Contains(EnrollmentColumn) && RowsWithData.Columns.Contains(AgeColumn) && RowsWithData.Columns.Contains(SexColumn) && RowsWithData.Columns.Contains(PeriodColumn))
{
foreach (DataRow drow in RowsWithData.Rows)
{
FilterExpression = "Trim(" + AgeColumn + ")='" + drow[AgeColumn].ToString().Trim() + "' AND Trim(" + SexColumn + ")='" + drow[SexColumn].ToString().Trim() + "' AND Trim(" + PeriodColumn + ")='" + drow[PeriodColumn].ToString().Trim() + "'";
DataRow[] MatchingRows = MergedTable.Select(FilterExpression);
//Copy the data from Matching Row to the Associated MergedTable.
if (MatchingRows != null && MatchingRows.Length > 0)
{
foreach (DataRow drMerged in MatchingRows)
{
drMerged[EnrollmentColumn] = drow[EnrollmentColumn];
}
}
}
}
#endregion
#endregion
}
else
{
MergedTable = RowsWithData;
}
return(MergedTable);
}
