public object RunQueryHTML(Guid _portletID)
{
Portlet portlet = ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
if (portlet.ParentPage.CanView(PortalUser.Current))
{
try
{
var mapper = new NHSimpleQuerySettingsMapper();
var settings = mapper.GetSettings(_portletID).ToList();
var _helper = new SettingsHelper(settings, _portletID, mapper);
try
{
DataTable dt = GetData(_portletID);
String html = string.Empty;
if (dt != null && dt.Rows.Count > 0)
{
if (_helper.GetSetting("JICSAllowExports").BoolValue)
{
HttpContext.Current.Session["sqhtml+" + PortalUser.Current.ID.AsGuid + _portletID] = dt;
}
if (Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value) > 0)
{
dt = dt.AsEnumerable().Take(Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value)).CopyToDataTable();
}
switch (_helper.GetSetting("JICSOutput", "grid").Value)
{
case "grid":
var dgResults = OutputHelper.CreateDataGrid();
OutputHelper.ConfigureDataGrid(ref dgResults,
dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("JICSGridAltRowColors", false).BoolValue,
_helper.GetSetting("JICSGridShowGridlines", false).BoolValue,
Convert.ToInt16(_helper.GetSetting("JICSGridCellPadding", 5).Value),
_helper.GetSetting("ColumnLabels").Value);
dgResults.DataSource = dt;
dgResults.DataBind();
var stringWrite = new StringWriter();
var htmlWrite = new HtmlTextWriter(stringWrite);
dgResults.RenderControl(htmlWrite);
htmlWrite.Flush();
html = stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", "");
break;
case "xml":
html = "<pre>" + HttpUtility.HtmlEncode(OutputHelper.RenderXml(dt)) + "</pre>";
break;
case "csv":
html = "<pre>" + OutputHelper.RenderCsv(dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("ColumnLabels").Value) + "</pre>";
break;
case "literal":
html = OutputHelper.RenderLiteral(dt, _helper.GetSetting("ExportLiteralPattern", "{0}").Value);
break;
}
return(new
{
success = true,
resultFormat = _helper.GetSetting("JICSOutput", "grid").Value,
html
});
}
return(new
{
success = true,
resultFormat = _helper.GetSetting("JICSOutput", "grid").Value,
html,
query =
(PortalUser.Current.IsSiteAdmin
? _helper.GetSetting("QueryText").Value
: "")
});
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Query Failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.StackTrace : "")
});
}
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Connection failed before query was executed." + (PortalUser.Current.IsSiteAdmin ? ": " + ex.StackTrace : "")
});
}
}
return(new
{
success = false,
message = "You do not have permissions to view this portlet"
});
}
public ActionResult ExportFile(String fileType, ContentIdentifier cid, User user)
{
var helper = GetHelper(cid.PortletId.Value);
List <String> allowedTypes = AllowedExports(helper);
if (allowedTypes.Contains(fileType))
{
DataTable dt;
try
{
dt = GetData(helper);
}
catch (Exception ex)
{
return(View("Error",
new Error
{
ErrorMessage = "An error occurred while querying the database.",
Exception =
_userFacade.FindByUsername(user.Username).IsSiteAdmin
? ex.ToString().Replace("\n", "<br />")
: String.Empty
}));
}
var strContentType = "text/plain"; // these defaults will be overwritten if we're successful
var strFilename = "ErrorOutput.txt";
byte[] byteArray;
using (var mstream = new MemoryStream())
{
using (var sw = new StreamWriter(mstream))
{
string fileName;
if (helper.GetSetting("QueryTitle").Value.Trim().Length > 0)
{
fileName = Regex.Replace(helper.GetSetting("QueryTitle").Value.Trim(), @"\W", "");
}
//remove non-alphanumeric characters from filename
else
{
fileName = "ExportedData";
}
switch (fileType)
{
case "Xls":
var dgResults = OutputHelper.CreateDataGrid();
OutputHelper.ConfigureDataGrid(ref dgResults,
dt,
helper.GetSetting("GOGridShowColumnHeadings", false).
BoolValue,
helper.GetSetting("GOGridAltRowColors", false).BoolValue,
helper.GetSetting("GOGridShowGridlines", false).BoolValue,
Convert.ToInt16(
helper.GetSetting("GOGridCellPadding", 5).Value),
helper.GetSetting("ColumnLabels").Value);
dgResults.DataSource = dt;
dgResults.DataBind();
var stringWrite = new StringWriter();
var htmlWrite = new HtmlTextWriter(stringWrite);
dgResults.RenderControl(htmlWrite);
htmlWrite.Flush();
sw.WriteLine(stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", ""));
strContentType = "application/vnd.ms-excel";
strFilename = fileName + ".xls";
break;
case "Xml":
sw.WriteLine(OutputHelper.RenderXml(dt));
strContentType = "text/xml";
strFilename = fileName + ".xml";
break;
case "Csv":
sw.WriteLine(OutputHelper.RenderCsv(dt,
helper.GetSetting("GOGridShowColumnHeadings", false)
.
BoolValue,
helper.GetSetting("ColumnLabels").Value));
strContentType = "text/csv";
strFilename = fileName + ".csv";
break;
case "Literal":
sw.WriteLine(OutputHelper.RenderLiteral(dt,
helper.GetSetting("ExportLiteralPattern", "{0}")
.
Value));
strContentType = "text/plain";
strFilename = fileName + ".txt";
break;
}
sw.Flush();
sw.Close();
byteArray = mstream.ToArray();
mstream.Flush();
mstream.Close();
}
}
return(File(byteArray, strContentType, strFilename));
}
return(View("Error", new Error {
ErrorMessage = "File Export Type Not Allowed"
}));
}
private void RenderOutput()
{
if (!ShouldRenderOutput())
{
return;
}
DataTable dt = null;
try
{
dt = GetData();
}catch (Exception ex)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Error, "Query Failed. Contact portal administrator. " + ex);
return;
}
//if the below is true then at least one row was returned
if (dt != null && dt.Rows.Count > 0)
{
if (_helper.GetSetting("JICSAllowExports").BoolValue)
{
HttpContext.Current.Session["sqhtml+" + PortalUser.Current.ID.AsGuid + this.ParentPortlet.Portlet.ID.AsGuid] = dt;
}
if (Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value) > 0)
{
dt = dt.AsEnumerable().Take(Convert.ToInt32(_helper.GetSetting("RowLimit", 0).Value)).CopyToDataTable();
}
switch (_helper.GetSetting("JICSOutput", "grid").Value)
{
case "grid":
OutputHelper.ConfigureDataGrid(ref dgResults,
dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("JICSGridAltRowColors", false).BoolValue,
_helper.GetSetting("JICSGridShowGridlines", false).BoolValue,
Convert.ToInt16(_helper.GetSetting("JICSGridCellPadding", 5).Value),
_helper.GetSetting("ColumnLabels").Value);
dgResults.DataSource = dt;
dgResults.DataBind();
dgResults.Visible = true;
break;
case "xml":
preformattedResults.Text = "<pre>" + HttpUtility.HtmlEncode(OutputHelper.RenderXml(dt)) + "</pre>";
break;
case "csv":
preformattedResults.Text = "<pre>" + OutputHelper.RenderCsv(dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("ColumnLabels").Value) + "</pre>";
break;
case "literal":
preformattedResults.Text = OutputHelper.RenderLiteral(dt, _helper.GetSetting("ExportLiteralPattern", "{0}").Value);
break;
}
pnlResults.Visible = true;
}
else
{
message2.Visible = true;
//If no matching records are found, they are shown a message
if (ParentPortlet.AccessCheck("CanAdminQueries") || PortalUser.Current.IsSiteAdmin == true)
{
message2.InnerHtml = "No results returned by this query.<br />Query text (visible by Query Admin only):<br />" + Regex.Replace(Regex.Replace(_helper.GetSetting("QueryText").Value, "<", "<", RegexOptions.Multiline), ">", ">", RegexOptions.Multiline);
}
else
{
message2.InnerHtml = "No results returned by this query.";
}
return;
}
}
private void Page_Load(object sender, System.EventArgs e)
{
var strContentType = "text/plain"; // these defaults will be overwritten if we're successful
var strFilename = "ErrorOutput.txt";
var mstream = new MemoryStream();
var sw = new StreamWriter(mstream);
if (Request.QueryString["sqkey"] != null &&
IsGuidFormat(Request.QueryString["sqkey"].ToString()))
{
var strKey = PortalUser.Current.ID.AsGuid + Request.QueryString["sqkey"];
var strFormat = Request.QueryString["format"];
if (HttpContext.Current.Session["sqhtml+" + strKey] != null && strFormat != null)
{
try
{
DataTable dt = (DataTable)HttpContext.Current.Session["sqhtml+" + strKey];
var mapper = new NHSimpleQuerySettingsMapper();
var settings = mapper.GetSettings(new Guid(Request.QueryString["sqkey"])).ToList();
var _helper = new SettingsHelper(settings, new Guid(Request.QueryString["sqkey"]), mapper);
string fileName;
if (_helper.GetSetting("QueryTitle").Value.Trim().Length > 0)
{
fileName = Regex.Replace(_helper.GetSetting("QueryTitle").Value.Trim(), @"\W", "");//remove non-alphanumeric characters from filename
}
else
{
fileName = "ExportedData";
}
switch (strFormat)
{
case "xls":
var dgResults = OutputHelper.CreateDataGrid();
OutputHelper.ConfigureDataGrid(ref dgResults,
dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("JICSGridAltRowColors", false).BoolValue,
_helper.GetSetting("JICSGridShowGridlines", false).BoolValue,
Convert.ToInt16(_helper.GetSetting("JICSGridCellPadding", 5).Value),
_helper.GetSetting("ColumnLabels").Value);
dgResults.DataSource = dt;
dgResults.DataBind();
var stringWrite = new StringWriter();
var htmlWrite = new HtmlTextWriter(stringWrite);
dgResults.RenderControl(htmlWrite);
htmlWrite.Flush();
sw.WriteLine(stringWrite.ToString().Replace("\n", "").Replace("\r", "").Replace(" ", ""));
strContentType = "application/vnd.ms-excel";
strFilename = fileName + ".xls";
break;
case "xml":
sw.WriteLine(OutputHelper.RenderXml(dt));
strContentType = "text/xml";
strFilename = fileName + ".xml";
break;
case "csv":
sw.WriteLine(OutputHelper.RenderCsv(dt,
_helper.GetSetting("JICSGridShowColumnHeadings", false).BoolValue,
_helper.GetSetting("ColumnLabels").Value));
strContentType = "text/csv";
strFilename = fileName + ".csv";
break;
case "literal":
sw.WriteLine(OutputHelper.RenderLiteral(dt, _helper.GetSetting("ExportLiteralPattern", "{0}").Value));
strContentType = "text/plain";
strFilename = fileName + ".txt";
break;
}
}
catch (Exception ex)
{
sw.WriteLine("Export failed. Please contact site adminstrator. (cache corrupted)" + (PortalUser.Current.IsSiteAdmin ? ex.ToString() : ""));
}
}
else
{
sw.WriteLine("Export failed. Please contact site adminstrator. (cache empty)");
}
}
else
{
sw.WriteLine("Export failed. Please contact site adminstrator. (bad key)");
}
sw.Flush();
sw.Close();
byte[] byteArray = mstream.ToArray();
mstream.Flush();
mstream.Close();
Response.Clear();
Response.AddHeader("Content-Type", strContentType);
Response.AddHeader("Content-Disposition", "attachment; filename=" + strFilename);
Response.AddHeader("Content-Length", byteArray.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(byteArray);
Response.End();
}