private SettingsHelper GetHelper(Guid portletId)
{
// Get the NHS object
try
{
var settings = _mapper.GetSettings(portletId).ToList();
return(new SettingsHelper(settings, portletId, _mapper));
}
catch (Exception ex)
{
ViewBag.Error = ex.ToString();
throw;
}
}
private DataTable GetData(Guid _portletID)
{
var mapper = new NHSimpleQuerySettingsMapper();
var settings = mapper.GetSettings(_portletID).ToList();
var _helper = new SettingsHelper(settings, _portletID, mapper);
var portlet = ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config"))
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value);
}
else
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value);
}
odbcConn.ConnectionTest();
Exception ex = null;
DataTable dt;
var literalStringReplacer = ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>();
var queryString = literalStringReplacer.Process(_helper.GetSetting("QueryText").Value, portlet);
if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0)
{
dt = odbcConn.ConnectToERP(queryString, ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value));
}
else
{
dt = odbcConn.ConnectToERP(queryString, ref ex);
}
if (ex != null)
{
throw ex;
}
return(dt);
}
protected void Page_Load(object sender, EventArgs e)
{
_portletId = this.ParentPortlet.PortletDisplay.Portlet.ID.AsGuid;
_mapper = new NHSimpleQuerySettingsMapper();
_settings = _mapper.GetSettings(_portletId).ToList();
_helper = new SettingsHelper(_settings, _portletId, _mapper);
_jicsOutputTypes = new[] { rbJICSOutputCSV, rbJICSOutputDataTables, rbJICSOutputGrid, rbJICSOutputXML, rbJICSOutputLiteral };
_goOutputTypes = new[] { rbGOOutputXML, rbGOOutputNone, rbGOOutputMasterDetail, rbGOOutputGrid, rbGOOutputCSV, rbGOOutputLiteral };
// Don't show the Go settings unless it is version 1.2 or greater.
if (new InstalledApplicationService().IsApplicationAtLeastThisVersion("JICS Go", "1.2"))
{
pnlGoSettings.Visible = true;
}
if (IsFirstLoad)
{
SetDdlConfigFiles();
ShowCurrentValues();
}
}
protected void Page_Load(object sender, EventArgs e)
{
_portletId = this.ParentPortlet.PortletDisplay.Portlet.ID.AsGuid;
_mapper = new NHSimpleQuerySettingsMapper();
_settings = _mapper.GetSettings(_portletId).ToList();
_helper = new SettingsHelper(_settings, _portletId, _mapper);
_jicsOutputTypes = new[] { rbJICSOutputCSV, rbJICSOutputDataTables, rbJICSOutputGrid, rbJICSOutputXML, rbJICSOutputLiteral };
_goOutputTypes = new[] { rbGOOutputXML, rbGOOutputNone, rbGOOutputMasterDetail, rbGOOutputGrid, rbGOOutputCSV, rbGOOutputLiteral };
// Don't show the Go settings unless it is version 1.2 or greater.
if (new InstalledApplicationService().IsApplicationAtLeastThisVersion("JICS Go", "1.2"))
{
pnlGoSettings.Visible = true;
}
if (IsFirstLoad)
{
SetDdlConfigFiles();
ShowCurrentValues();
var literalStringReplacer = ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>();
rptLiteralStringReplacementsAvailable.DataSource = literalStringReplacer.GetAvailableLiterals().Where(x => new[] { "@@HostID", "@@FirstName", "@@LastName", "@@Username", "@@EmailAddress", "@@DisplayName", "@@CurrentYear", "@@CurrentSession" }.Contains(x.Key));
rptLiteralStringReplacementsAvailable.DataBind();
}
}
private void Save()
{
_mapper.UpdateSetting(_helper.GetSetting("QueryTitle"), tbQueryTitle.Text);
_mapper.UpdateSetting(_helper.GetSetting("QueryText"), txtQuery.Text);
if (ddlConfigFile.SelectedValue == "") //Set to Other
{
_mapper.UpdateSetting(_helper.GetSetting("ConfigFile"), tbConfigFile.Text);
}
else
{
tbConfigFile.Attributes.CssStyle.Remove("display");
_mapper.UpdateSetting(_helper.GetSetting("ConfigFile"), ddlConfigFile.SelectedValue);
tbConfigFile.Text = "";
}
var i = 0;
_mapper.UpdateSetting(_helper.GetSetting("QueryTimeout", 0),
Int32.TryParse(tbQueryTimeout.Text, out i) ? i.ToString() : "0");
i = 0;
_mapper.UpdateSetting(_helper.GetSetting("RowLimit", 0),
Int32.TryParse(tbRowLimit.Text, out i) ? i.ToString() : "0");
_mapper.UpdateSetting(_helper.GetSetting("JICSDisplayResultsMinimized", false), chkDisplayResultsMinimized.Checked);
var columnLabels = new List <String>();
if (txtColumnLabels.Text.Trim().Length > 0)
{
if (txtColumnLabels.Text.Contains(","))
{
columnLabels.AddRange(txtColumnLabels.Text.Split(',').Select(column => column.Trim()));
}
else
{
columnLabels.Add(txtColumnLabels.Text.Trim());
}
}
_mapper.UpdateSetting(_helper.GetSetting("ColumnLabels"), String.Join(",", columnLabels.ToArray()));
txtColumnLabels.Text = String.Join(",", columnLabels.ToArray());
_mapper.UpdateSetting(_helper.GetSetting("ExportXls"), chkExportXls.Checked);
_mapper.UpdateSetting(_helper.GetSetting("ExportXml"), chkExportXml.Checked);
_mapper.UpdateSetting(_helper.GetSetting("ExportCsv"), chkExportCsv.Checked);
_mapper.UpdateSetting(_helper.GetSetting("ExportLiteral"), chkExportLiteral.Checked);
_mapper.UpdateSetting(_helper.GetSetting("ExportLiteralPattern"), tbExportLiteralPattern.Text);
_mapper.UpdateSetting(_helper.GetSetting("JICSAllowExports"), chkJICSAllowExports.Checked);
_mapper.UpdateSetting(_helper.GetSetting("JICSLinkText"), tbLinkText.Text);
_mapper.UpdateSetting(_helper.GetSetting("JICSDescription"), tbDescription.Text);
_mapper.UpdateSetting(_helper.GetSetting("JICSAsync"), chkJICSAsync.Checked);
using (var htmlInputRadioButton = _jicsOutputTypes.SingleOrDefault(x => x.Checked))
{
if (htmlInputRadioButton != null)
{
_mapper.UpdateSetting(_helper.GetSetting("JICSOutput"), htmlInputRadioButton.Value);
switch (htmlInputRadioButton.Value)
{
case "grid":
_mapper.UpdateSetting(_helper.GetSetting("JICSGridShowGridlines"), chkJICSGridShowGridlines.Checked);
_mapper.UpdateSetting(_helper.GetSetting("JICSGridCellPadding"), tbJICSGridCellPadding.Text.Trim() == "" ? "0" : tbJICSGridCellPadding.Text);
_mapper.UpdateSetting(_helper.GetSetting("JICSGridAltRowColors"), chkJICSGridAltRowColors.Checked);
_mapper.UpdateSetting(_helper.GetSetting("JICSGridShowColumnHeadings"), chkJICSShowColumnHeadings.Checked);
break;
case "datatables":
var expandedColumn = new List <string>();
if (tbJICSDataTablesExpandedColumns.Text.Trim().Length > 0)
{
if (tbJICSDataTablesExpandedColumns.Text.Contains(","))
{
expandedColumn.AddRange(tbJICSDataTablesExpandedColumns.Text.Split(',').Select(column => column.Trim()));
}
else
{
expandedColumn.Add(tbJICSDataTablesExpandedColumns.Text.Trim());
}
}
_mapper.UpdateSetting(_helper.GetSetting("JICSDataTablesExpandedColumns"), String.Join(",", expandedColumn.ToArray()));
tbJICSDataTablesExpandedColumns.Text = String.Join(",", expandedColumn.ToArray());
break;
}
}
else
{
_mapper.UpdateSetting(_helper.GetSetting("JICSOutput"), "grid");
}
}
_mapper.UpdateSetting(_helper.GetSetting("GOAllowExports"), chkGOAllowExports.Checked);
using (var htmlInputRadioButton = _goOutputTypes.SingleOrDefault(x => x.Checked))
{
if (htmlInputRadioButton != null)
{
_mapper.UpdateSetting(_helper.GetSetting("GOOutput"), htmlInputRadioButton.Value);
switch (htmlInputRadioButton.Value)
{
case "grid":
_mapper.UpdateSetting(_helper.GetSetting("GOGridShowGridlines"), chkGOGridShowGridlines.Checked);
_mapper.UpdateSetting(_helper.GetSetting("GOSGridCellPadding"), tbGOGridCellPadding.Text.Trim() == "" ? "0" : tbGOGridCellPadding.Text);
_mapper.UpdateSetting(_helper.GetSetting("GOGridAltRowColors"), chkGOGridAltRowColors.Checked);
_mapper.UpdateSetting(_helper.GetSetting("GOGridShowColumnHeadings"), chkGOGridShowColumnHeadings.Checked);
break;
case "masterdetail":
var expandedColumn = new List <string>();
if (tbGOOutputMasterDetailDisplayColumns.Text.Trim().Length > 0)
{
if (tbGOOutputMasterDetailDisplayColumns.Text.Contains(","))
{
expandedColumn.AddRange(tbGOOutputMasterDetailDisplayColumns.Text.Split(',').Select(column => column.Trim()));
}
else
{
expandedColumn.Add(tbGOOutputMasterDetailDisplayColumns.Text.Trim());
}
}
_mapper.UpdateSetting(_helper.GetSetting("GOMasterDetailDisplayColumns"), String.Join(",", expandedColumn.ToArray()));
tbGOOutputMasterDetailDisplayColumns.Text = String.Join(",", expandedColumn.ToArray());
break;
}
}
else
{
_mapper.UpdateSetting(_helper.GetSetting("GOOutput"), "none");
}
}
_settings = _mapper.GetSettings(_portletId).ToList();
}
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();
}
public object RunQuery(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);
if (dt == null)
{
return(new
{
success = false,
message = "No results returned."
});
}
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();
}
var jsdtc = new JSDataTableConverter(dt, helper.GetSetting("JICSDataTablesExpandedColumns").Value.Split(','), helper.GetSetting("ColumnLabels").Value.Split(','));
var data = jsdtc.GetJsDataTable();
return(new
{
success = true,
data.data,
data.columns
});
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Query results handling failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.Message + " " + ex.StackTrace : "")
});
}
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Connection failed before query was executed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.Message + " " + ex.StackTrace : "")
});
}
}
else
{
return(new
{
success = false,
message = "You do not have permissions to view this portlet"
});
}
}
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"
});
}
protected void Page_Load(object sender, EventArgs e)
{
_portletId = this.ParentPortlet.PortletDisplay.Portlet.ID.AsGuid;
_mapper = new NHSimpleQuerySettingsMapper();
_settings = _mapper.GetSettings(_portletId).ToList();
_helper = new SettingsHelper(_settings, _portletId, _mapper);
pnlResults.Visible = false;
pnlLinkDescription.Visible = false;
pnlDataTableResults.Visible = false;
pnlQueryTitle.Visible = false;
String strQueryTitle = String.Empty;
if (_settings.Count == 0 || _settings.Find(x => x.Name == "QueryText") == null || _settings.Find(x => x.Name == "QueryText").Value == String.Empty)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Message, "This portlet is not yet configured. Contact portal administrator.");
return;
}
if (_helper.GetSetting("JICSAllowExports", false).BoolValue)
{
if (_helper.GetSetting("ExportXls", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportExcel.Visible = true;
lnkSQExportExcel.NavigateUrl = "Export_Data.aspx?format=xls&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportCsv", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportCsv.Visible = true;
lnkSQExportCsv.NavigateUrl = "Export_Data.aspx?format=csv&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportXml", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportXml.Visible = true;
lnkSQExportXml.NavigateUrl = "Export_Data.aspx?format=xml&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportLiteral", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportLiteral.Visible = true;
lnkSQExportLiteral.NavigateUrl = "Export_Data.aspx?format=literal&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
}
if (_helper.GetSetting("QueryTitle").Value.Trim().Length > 0)
{
lblQueryTitle.Text = _helper.GetSetting("QueryTitle").Value;
pnlQueryTitle.Visible = true;
}
if (_helper.GetSetting("JICSOutput", "grid").Value != "datatables")
{
if (_helper.GetSetting("JICSAsync", false).BoolValue)
{
if (ShouldRenderOutput())
{
hdnUseAJAX.Value = "Y";
lnbGetData.Visible = true;
}
}
else
{
if (ShouldRenderOutput())
{
RenderOutput();
}
}
}
else
{
if (ShouldRenderOutput())
{
pnlDataTableResults.Visible = true;
}
}
}
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
{
var mapper = new NHSimpleQuerySettingsMapper();
var settings = mapper.GetSettings(_portletID).ToList();
var helper = new SettingsHelper(settings, _portletID, mapper);
try
{
DataTable dt = GetData(_portletID);
if (dt == null)
{
return new
{
success = false,
message = "No results returned."
};
}
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();
var jsdtc = new JSDataTableConverter(dt, helper.GetSetting("JICSDataTablesExpandedColumns").Value.Split(','), helper.GetSetting("ColumnLabels").Value.Split(','));
var data = jsdtc.GetJsDataTable();
return new
{
success = true,
data.data,
data.columns
};
}
catch (Exception ex)
{
return new
{
success = false,
message = "Query results handling failed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.Message + " " + ex.StackTrace : "")
};
}
}
catch (Exception ex)
{
return new
{
success = false,
message = "Connection failed before query was executed. " + (PortalUser.Current.IsSiteAdmin ? ": " + ex.Message + " " + ex.StackTrace : "")
};
}
}
else
{
return new
{
success = false,
message = "You do not have permissions to view this portlet"
};
}
}
private DataTable GetData( Guid _portletID)
{
var mapper = new NHSimpleQuerySettingsMapper();
var settings = mapper.GetSettings(_portletID).ToList();
var _helper = new SettingsHelper(settings, _portletID, mapper);
OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
if (_helper.GetSetting("ConfigFile").Value.EndsWith(".config"))
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _helper.GetSetting("ConfigFile").Value);
else
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_helper.GetSetting("ConfigFile").Value);
odbcConn.ConnectionTest();
Exception ex = null;
DataTable dt;
var queryStringFiller = new FillQueryString(_helper.GetSetting("QueryText").Value);
if (Convert.ToInt16(_helper.GetSetting("QueryTimeout", 0).Value) > 0)
dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex, Convert.ToInt16(_helper.GetSetting("QueryTimeout").Value));
else
dt = odbcConn.ConnectToERP(queryStringFiller.FilledQueryString(), ref ex);
if (ex != null)
{
throw ex;
}
return dt;
}
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
{
Exception ex = null;
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"
};
}
protected void Page_Load(object sender, EventArgs e)
{
_portletId = this.ParentPortlet.PortletDisplay.Portlet.ID.AsGuid;
_mapper = new NHSimpleQuerySettingsMapper();
_settings = _mapper.GetSettings(_portletId).ToList();
_helper = new SettingsHelper(_settings, _portletId, _mapper);
pnlResults.Visible = false;
pnlLinkDescription.Visible = false;
pnlDataTableResults.Visible = false;
pnlQueryTitle.Visible = false;
String strQueryTitle = String.Empty;
if (_settings.Count == 0 || _settings.Find(x => x.Name == "QueryText") == null || _settings.Find(x => x.Name == "QueryText").Value == String.Empty)
{
this.ParentPortlet.ShowFeedback(FeedbackType.Message, "This portlet is not yet configured. Contact portal administrator.");
return;
}
if (_helper.GetSetting("JICSAllowExports", false).BoolValue)
{
if (_helper.GetSetting("ExportXls", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportExcel.Visible = true;
lnkSQExportExcel.NavigateUrl = "Export_Data.aspx?format=xls&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportCsv", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportCsv.Visible = true;
lnkSQExportCsv.NavigateUrl = "Export_Data.aspx?format=csv&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportXml", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportXml.Visible = true;
lnkSQExportXml.NavigateUrl = "Export_Data.aspx?format=xml&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
if (_helper.GetSetting("ExportLiteral", false).BoolValue)
{
pnlExport.Visible = true;
lnkSQExportLiteral.Visible = true;
lnkSQExportLiteral.NavigateUrl = "Export_Data.aspx?format=literal&sqkey=" + this.ParentPortlet.Portlet.ID.AsGuid;
}
}
if (_helper.GetSetting("QueryTitle").Value.Trim().Length > 0)
{
lblQueryTitle.Text = _helper.GetSetting("QueryTitle").Value;
pnlQueryTitle.Visible = true;
}
if (_helper.GetSetting("JICSOutput", "grid").Value != "datatables")
{
if (_helper.GetSetting("JICSAsync", false).BoolValue)
{
if (ShouldRenderOutput())
{
hdnUseAJAX.Value = "Y";
lnbGetData.Visible = true;
}
}
else
{
if (ShouldRenderOutput())
{
RenderOutput();
}
}
}else
{
if(ShouldRenderOutput())
pnlDataTableResults.Visible = true;
}
}
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();
}