public object RunQuery(Guid _portletID)
{
CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
if (portlet.ParentPage.CanView(PortalUser.Current))
{
try
{
NHSimpleQueryMapper mapper = new NHSimpleQueryMapper();
NHSimpleQuery NHS = mapper.GetById(_portletID);
if (NHS.ConfigFile.Contains(".config"))
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + NHS.ConfigFile);
}
else
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(NHS.ConfigFile);
}
odbcConn.ConnectionTest();
Exception exError = null;
try
{
DataTable dt = new DataTable();
if (NHS.QueryTimeout > 0)
{
dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError, NHS.QueryTimeout);
}
else
{
dt = odbcConn.ConnectToERP(NHS.FilledQueryString(), ref exError);
}
StoreExportData(NHS, dt, _portletID, PortletUtilities.GetSettingValue(portlet, "DisplayColumnHeadings") == "Checked", portlet);
JSDataTableConverter jsdtc = new JSDataTableConverter(dt, NHS.ExpandedColumns.Split(','), NHS.ColumnLabels.Split(','));
JSDataTable data = jsdtc.GetJsDataTable();
if (exError != null)
{
throw exError;
}
return(new
{
success = true,
data = data.data,
columns = data.columns
});
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Query Failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "")
});
}
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Connection failed before query was executed." + (PortalUser.Current.IsSiteAdmin ? ": " + ex : "")
});
}
}
else
{
return(new
{
success = false,
message = "You do not have permissions to view this portlet"
});
}
}
private void loadControlOptions(Portlet portlet)
{
string server = PortletUtilities.GetSettingValue(portlet, "SSRSReportServer");
string location = PortletUtilities.GetSettingValue(portlet, "SSRSReportLocation");
string height = PortletUtilities.GetSettingValue(portlet, "SSRSMaxFrameHeight");
string tmp_displayControls = PortletUtilities.GetSettingValue(portlet, "SSRShideControls");
string[] displayControls;
if (tmp_displayControls != null && tmp_displayControls != "")
{
displayControls = tmp_displayControls.Split(';');
// If set in admin settings, show/hide controls.
if (displayControls.Count() >= 12)
{
foreach (string control in displayControls)
{
string[] tmp = control.Split(',');
tmp[0] = tmp[0].TrimStart('[');
tmp[1] = tmp[1].TrimEnd(']');
bool visibility = Boolean.Parse(tmp[1]);
switch (tmp[0])
{
case "ShowBackButton":
if (visibility)
{
ReportViewer1.ShowBackButton = true;
}
else
{
ReportViewer1.ShowBackButton = false;
}
break;
case "ShowCredentialPrompts":
if (visibility)
{
ReportViewer1.ShowCredentialPrompts = true;
}
else
{
ReportViewer1.ShowCredentialPrompts = false;
}
break;
case "ShowDocumentMapButton":
if (visibility)
{
ReportViewer1.ShowDocumentMapButton = true;
}
else
{
ReportViewer1.ShowDocumentMapButton = false;
}
break;
case "ShowExportControls":
if (visibility)
{
ReportViewer1.ShowExportControls = true;
}
else
{
ReportViewer1.ShowExportControls = false;
}
break;
case "ShowFindControls":
if (visibility)
{
ReportViewer1.ShowFindControls = true;
}
else
{
ReportViewer1.ShowFindControls = false;
}
break;
case "ShowPageNavigationControls":
if (visibility)
{
ReportViewer1.ShowPageNavigationControls = true;
}
else
{
ReportViewer1.ShowPageNavigationControls = false;
}
break;
case "ShowParameterPrompts":
if (visibility)
{
ReportViewer1.ShowParameterPrompts = true;
}
else
{
ReportViewer1.ShowParameterPrompts = false;
}
break;
case "ShowPrintButton":
if (visibility)
{
ReportViewer1.ShowPrintButton = true;
}
else
{
ReportViewer1.ShowPrintButton = false;
}
break;
case "ShowPromptAreaButton":
if (visibility)
{
ReportViewer1.ShowPromptAreaButton = true;
}
else
{
ReportViewer1.ShowPromptAreaButton = false;
}
break;
case "ShowRefreshButton":
if (visibility)
{
ReportViewer1.ShowRefreshButton = true;
}
else
{
ReportViewer1.ShowRefreshButton = false;
}
break;
case "ShowToolBar":
if (visibility)
{
ReportViewer1.ShowToolBar = true;
}
else
{
ReportViewer1.ShowToolBar = false;
}
break;
case "ShowZoomControl":
if (visibility)
{
ReportViewer1.ShowZoomControl = true;
}
else
{
ReportViewer1.ShowZoomControl = false;
}
break;
}
}
}
}
if (server != "" && location != "")
{
if (Uri.IsWellFormedUriString(server, UriKind.RelativeOrAbsolute))
{
UriBuilder uri = new UriBuilder(server);
ReportViewer1.ServerReport.ReportServerUrl = uri.Uri;
ReportViewer1.ServerReport.ReportPath = location;
try
{
if (height != "")
{
ReportViewer1.Height = Unit.Pixel(Convert.ToInt32(height));
}
}
catch (Exception ex)
{
litMessage.Text = "The static portlet height is defined incorrectly. Please make sure that the value is an integer.";
Panel1.Visible = false;
pnlMessage.Visible = true;
}
}
else
{
ReportViewer1.Visible = false;
litMessage.Text = "Malformed Report Server URL in portlet settings.";
Panel1.Visible = false;
pnlMessage.Visible = true;
}
}
else
{
litMessage.Text = "This portlet has not yet been configured by an administrator.";
Panel1.Visible = false;
pnlMessage.Visible = true;
}
}
private void StoreExportData(NHSimpleQuery _nhs, DataTable _dt, Guid _key, bool _showHeader, Portlet _portlet)
{
String strkey = Jenzabar.Portal.Framework.PortalUser.Current.ID.AsGuid.ToString() + _key.ToString();
String strQueryTitle = "";
if (PortletUtilities.GetSettingValue(_portlet, "QueryTitle").Trim().Length > 0)
{
strQueryTitle = PortletUtilities.GetSettingValue(_portlet, "QueryTitle").Trim();
}
if (strQueryTitle.Trim().Length > 0)
{
HttpContext.Current.Session["sqfilename+" + strkey] = Regex.Replace(strQueryTitle, @"\W", "");//remove non-alphanumeric characters from filename
}
else
{
HttpContext.Current.Session["sqfilename+" + strkey] = "ExportedData";
}
switch (_nhs.ResultFormat)
{
case "xml":
Encoding encoding = Encoding.Unicode; // other encoding are possible, e.g., utf-8
string xml = String.Empty;
for (int i = 0; i < _dt.Rows.Count; i++)
{
xml = xml + _dt.Rows[i][0].ToString();
}
// pretty print the xml
XmlDocument doc = new XmlDocument();
try
{
doc.LoadXml(xml);
xml = FormatXml.Format(doc, encoding);
}
catch
{
xml = "<xmlroot>" + xml + "</xmlroot>";
doc.LoadXml(xml);
xml = FormatXml.Format(doc, encoding);
}
HttpContext.Current.Session["sqhtml+" + strkey] = xml;
break;
case "csv":
string csv = String.Empty;
if (_showHeader)
{
for (int i = 0; i < _dt.Columns.Count; i++)
{
csv = csv + _dt.Columns[i].ColumnName;
if (i + 1 < _dt.Columns.Count)
{
csv = csv + ",";
}
}
csv = csv + "\n";
}
for (int i = 0; i < _dt.Rows.Count; i++)
{
for (int j = 0; j < _dt.Rows[i].ItemArray.Length; j++)
{
csv = csv + _dt.Rows[i].ItemArray[j].ToString().Trim();
if (j + 1 < _dt.Rows[i].ItemArray.Length)
{
csv = csv + ",";
}
}
csv = csv + "\n";
}
HttpContext.Current.Session["sqhtml+" + strkey] = csv;
break;
default:
string txt = "Format as ." + _nhs.ResultFormat + " is not implemented";
HttpContext.Current.Session["sqhtml+" + strkey] = txt;
break;
}
}