public static void AddPortlet(int reportID, int col)
{
Portlet[] portlets = GetPortlets();
Report report = Reports.GetReport(UserSession.LoginUser, reportID);
if (report.OrganizationID != UserSession.LoginUser.OrganizationID && report.OrganizationID != null)
{
return;
}
Portlet portlet = new Portlet();
portlet.Caption = report.Name;
portlet.ID = GetPortletID(reportID);
portlet.ReportID = reportID;
portlet.IsOpen = true;
portlet.X = col;
portlet.Y = GetPortletColumnMaxY(portlets, col) + 1;
portlet.Height = 250;
SavePortlet(portlet);
int[] portletIDs = Settings.UserDB.ReadIntArray("DashboardPortlets");
List <int> list = new List <int>(portletIDs);
list.Add(reportID);
Settings.UserDB.WriteIntArray("DashboardPortlets", list.ToArray());
}
public static void PortletNew()
{
Portlet item = new Portlet();
item.Id = Guid.NewGuid();
PSCDialog.DataShare = new PortletArgs(item, false);
}
public object TestConnection(string _connectionFile, Guid _portletID)
{
Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
if (portlet.AccessCheck("CanAdminQueries"))
{
if (_connectionFile.EndsWith(".config") &&
!System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile)))
{
return(new
{
success = false,
message = "Connection File specified does not exist in ClientConfig folder.",
looked = HttpContext.Current.Server.MapPath("~/ClientConfig/" + _connectionFile).ToString()
});
}
else
{
try
{
CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
if (_connectionFile.EndsWith(".config"))
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile);
}
else
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile);
}
odbcConn.ConnectionTest();
return(new
{
success = true,
message = "Connection test was successful."
});
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Connection could not be established using the Connection provided.",
exception = ex.ToString()
});
}
}
}
else
{
return(new
{
success = false,
message = "No Permissions to Modify Queries"
});
}
}
public static void UpdatePortletVisibility(string id, bool isOpen)
{
Portlet portlet = LoadPortlet(id);
if (portlet != null)
{
portlet.IsOpen = isOpen;
SavePortlet(portlet);
}
}
public static void UpdatePortletHeight(string id, int height)
{
Portlet portlet = LoadPortlet(id);
if (portlet != null)
{
portlet.Height = height;
SavePortlet(portlet);
}
}
public override void PortletRemovedFromPage(Portlet portlet, PortalPageInfo page)
{
try
{
var mapper = new Mappers.NHSimpleQuerySettingsMapper();
mapper.Delete(mapper.GetSettings(portlet.ID).ToList());
}
catch (Exception ex)
{
ExceptionManager.Publish(ex);
}
}
public bool IsQuerySafeEnough(string strQueryText, Portlet portlet)
{
if (portlet.AccessCheck("CanAdminAdvQueries") || PortalUser.Current.IsSiteAdmin == true)
return true;
else
{
if (Regex.Match(strQueryText, "DELETE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "INSERT", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "EXECUTE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "DROP", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "UPDATE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0)
return false;
else
return true;
}
}
public static void UpdatePortletPositions(string[][] positions)
{
for (int x = 0; x < positions.Length; x++)
{
for (int y = 0; y < positions[x].Length; y++)
{
Portlet portlet = LoadPortlet(positions[x][y]);
if (portlet != null)
{
portlet.X = x;
portlet.Y = y;
SavePortlet(portlet);
}
}
}
}
/// <summary>
/// </summary>
/// <param name="portlet"></param>
/// <param name="parameters"></param>
/// <returns></returns>
///
//--DRB
//private void setParameters(Portlet portlet)
//{
// try
// {
// string parameters = PortletUtilities.GetSettingValue(portlet, "SSRSParameters"); //viewerconfig.ParameterList;
// if (parameters != null && parameters != "")
// {
// parameters = insertReplacementParameters(portlet, parameters);
// List<GCReportParameter> defaultParameters = GCReportParameter.buildParameterList(parameters);
// //Parameter list to be populated
// List<ReportParameter> lstParams = new List<ReportParameter>();
// //Parameter list from the report
// ReportParameterInfoCollection rptParams = ReportViewer1.ServerReport.GetParameters();
// foreach (ReportParameterInfo rptparam in rptParams)
// {
// string val = String.Empty;
// int indx = defaultParameters.FindIndex(x => x.Name == rptparam.Name);
// if (indx >= 0)
// {
// ReportParameter p = new ReportParameter(rptparam.Name, defaultParameters[indx].Value);
// lstParams.Add(p);
// }
// else
// {
// ReportParameter p = new ReportParameter(rptparam.Name, rptparam.Values.ToArray());
// lstParams.Add(p);
// }
// }
// ReportViewer1.ServerReport.SetParameters(lstParams.ToArray());
// }
// }
// catch (Exception ex)
// {
// litMessage.Text = ex.Message;
// Panel1.Visible = false;
// pnlMessage.Visible = true;
// }
//}
private string insertReplacementParameters(Portlet portlet, string parameters)
{
// Replace @@ placeholders with correct values with actual content.
var literalStringReplacer = ObjectFactoryWrapper.GetInstance <ILiteralStringReplacer>();
parameters = literalStringReplacer.Process(parameters, portlet.Portlet);
StringBuilder sbParameters = new StringBuilder(parameters);
//Replace Fields from URL Parameters
Regex r1 = new Regex("@@URL_(\\w|\\$)*&?");
MatchCollection mc = r1.Matches(parameters);
foreach (Match m in mc)
{
try
{
Regex r2 = new Regex(m.Value + "=(\\w)+(&|$)");
var val = r2.Match(HttpUtility.UrlDecode(Request.QueryString.ToString())).Value;
val = val.Substring(val.IndexOf("=") + 1).TrimEnd('&');
if (val != "" && val != null)
{
string fieldValue = val;
sbParameters.Replace(m.Value, fieldValue);
}
else
{
sbParameters.Replace(m.Value, String.Empty);
}
}
catch (Exception ex)
{
string erMsg = PortalUser.Current.IsSiteAdmin ? "Unable to perform field replacement from querystring. " + ex.Message : "Unable to perform field replacement from querystring";
Panel1.Visible = false;
litMessage.Text = erMsg;
pnlMessage.Visible = true;
}
}
return(sbParameters.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
IPortalContextFacade contextFacade = ObjectFactoryWrapper.GetInstance <IPortalContextFacade>();
IPortletFacade portletFacade = ObjectFactoryWrapper.GetInstance <IPortletFacade>();
if (Request.QueryString["DN"] != null)
{
String dn = HttpUtility.UrlDecode(Request.QueryString["DN"].ToString());
Portlet p = portletFacade.FindByDN(dn);
PortletGuid = p.PortletTemplate.Guid.ToString();
if (p.ParentPage.CanView(PortalUser.Current))
{
Panel1.Visible = true;
if (!IsPostBack)
{
loadControlOptions(p);
loadCredentials();
//--DRB //setParameters(p);
}
}
else
{
//Response.Write("You do not have access to this page");
litMessage.Text = "You do not have access to this page";
Panel1.Visible = false;
pnlMessage.Visible = true;
}
}
else
{
litMessage.Text = "DN Not Defined";
Panel1.Visible = false;
pnlMessage.Visible = true;
}
}
public bool IsQuerySafeEnough(string strQueryText, Portlet portlet)
{
if (portlet.AccessCheck("CanAdminAdvQueries") || PortalUser.Current.IsSiteAdmin == true)
{
return(true);
}
else
{
if (Regex.Match(strQueryText, "DELETE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "INSERT", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "EXECUTE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "DROP", RegexOptions.IgnoreCase).ToString().Trim().Length > 0 ||
Regex.Match(strQueryText, "UPDATE", RegexOptions.IgnoreCase).ToString().Trim().Length > 0)
{
return(false);
}
else
{
return(true);
}
}
}
public static string GetPortletHtml(Portlet portlet, Report report)
{
string caption = portlet.Caption.Length >= 35 ? portlet.Caption.Substring(0, 32) + "..." : portlet.Caption;
if (string.IsNullOrEmpty(report.ExternalURL))
{
DataTable table = GetReportDataTable(report);
string data = table.Rows.Count < 1 ? "<div class=\"noRecords\">There are no items to display.</div>" : DataTableToHtml(table);
StringBuilder builder = new StringBuilder();
builder.Append("<div class=\"portlet\" id=\"{0}\">");
builder.Append("<div class=\"portlet-header {4}\">{1}<span class=\"portlet-close portlet-icon ts-icon ts-icon-close-small\"></span><span class=\"portlet-state portlet-icon ts-icon ts-icon-triangle-{3}\"></span></div>");
builder.Append("<div class=\"portlet-body ui-corner-bottom\"><div class=\"portlet-content ui-corner-bottom\">{2}</div><div class=\"viewMore ui-corner-bottom\"><a href=\"#\" class=\"ts-link\">View Report</a></div></div></div>");
return(string.Format(builder.ToString(), portlet.ID, caption, data, portlet.IsOpen ? "s" : "w", portlet.IsOpen ? "ui-corner-top" : "ui-corner-all"));
}
else
{
StringBuilder builder = new StringBuilder();
builder.Append("<div class=\"portlet\" id=\"{0}\">");
builder.Append("<div class=\"portlet-header {4}\">{1}<span class=\"portlet-close portlet-icon ts-icon ts-icon-close-small\"></span><span class=\"portlet-state portlet-icon ts-icon ts-icon-triangle-{3}\"></span></div>");
builder.Append("<div class=\"portlet-body ui-corner-bottom externalReport\"><div class=\"portlet-content ui-corner-bottom\"><iframe height=\"250px\" width=\"100%\" scrolling=\"yes\" frameborder=\"0\" src=\"{2}\"></iframe></div></div></div>");
return(string.Format(builder.ToString(), portlet.ID, caption, report.ExternalURL, portlet.IsOpen ? "s" : "w", portlet.IsOpen ? "ui-corner-top" : "ui-corner-all"));
}
}
public override void CollectValues()
{
int skip;
if (GetIntFromRequest(Portlet.GetPortletSpecificParamName("Skip"), out skip))
{
Skip = Portlet.SkipFirst != 0 ? skip + Portlet.SkipFirst : skip;
}
string sortCol;
if (GetStringFromRequest(Portlet.GetPortletSpecificParamName("SortColumn"), out sortCol))
{
_sortColumn = sortCol;
}
bool sortDesc;
if (GetBoolFromRequest(Portlet.GetPortletSpecificParamName("SortDescending"), out sortDesc))
{
_sortDescending = sortDesc;
}
}
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;
}
}
public object TestQuery(string _connectionFile, string _queryString, Guid _portletID, string _expandedColumns, string _columnLabels, string queryTimeout)
{
CUS.OdbcConnectionClass3.OdbcConnectionClass3 odbcConn;
Portlet portlet = Jenzabar.Common.ObjectFactoryWrapper.GetInstance <IPortletFacade>().FindByGuid(_portletID);
if (portlet.AccessCheck("CanAdminQueries"))
{
try
{
if (_connectionFile.Contains(".config"))
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3("~/ClientConfig/" + _connectionFile);
}
else
{
odbcConn = new CUS.OdbcConnectionClass3.OdbcConnectionClass3(_connectionFile);
}
odbcConn.ConnectionTest();
Exception exError = null;
try
{
QuerySafe QS = new QuerySafe();
if (QS.IsQuerySafeEnough(_queryString, portlet))
{
FillQueryString FQS = new FillQueryString(_queryString);
DataTable dt = new DataTable();
int qt = 0;
if (Int32.TryParse(queryTimeout, out qt) && qt > 0)
{
dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError, qt);
}
else
{
dt = odbcConn.ConnectToERP(FQS.FilledQueryString, ref exError);
}
List <String> expandedColumns = new List <String>();
List <String> columnLabels = new List <String>();
if (_expandedColumns.Trim().Length > 0)
{
if (_expandedColumns.Contains(','))
{
foreach (String column in _expandedColumns.Split(','))
{
expandedColumns.Add(column.Trim());
}
}
else
{
expandedColumns.Add(_expandedColumns.Trim());
}
}
if (_columnLabels.Trim().Length > 0)
{
if (_columnLabels.Contains(','))
{
foreach (String label in _columnLabels.Split(','))
{
columnLabels.Add(label.Trim());
}
}
else
{
columnLabels.Add(_columnLabels.Trim());
}
}
JSDataTableConverter jsdtc = new JSDataTableConverter(dt, expandedColumns.ToArray(), columnLabels.ToArray());
JSDataTable data = jsdtc.GetJsDataTable();
if (exError != null)
{
throw exError;
}
return(new
{
success = true,
message = "Query test was successful. " + dt.Rows.Count.ToString() + " rows returned. ",
data = data.data,
columns = data.columns,
a = expandedColumns.ToArray(),
b = columnLabels.ToArray()
});
}
else
{
return(new
{
success = false,
message = "You do not have permissions to create advanced queries that use Update, Delete, Insert, or Execute."
});
}
}
catch (Exception ex)
{
return(new
{
success = false,
message = "Query Failed. Test your query using an external tool and paste your corrected version into place. <br>Error:<br>" + ex.Message
});
}
}
catch
{
return(new
{
success = false,
message = "Connection failed before query was executed."
});
}
}
else
{
return(new
{
success = false,
message = "You do not have permissions to create queries."
});
}
}
private static int ComparePortletY(Portlet portlet1, Portlet portlet2)
{
return(portlet1.Y - portlet2.Y);
}
public static void SavePortlet(Portlet portlet)
{
Settings.UserDB.WriteJson <Portlet>("DashboardPortlet-" + portlet.ID, portlet);
}
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 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;
}
}
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"
});
}
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;
}
}
/// <summary>
///
/// </summary>
//protected void BindColumn()
//{
// var customer = CRMContext.Current.User;
// if (customer != null)
// {
// var porColumnNumbers = base.PortalService.GetPortalColumnNumberList(customer.CustomerID);
// for (int i = 0; i < porColumnNumbers.Count; i++)
// {
// PortalColumn portalColumn = new PortalColumn();
// portalColumn.ID = "PortalColumn" + porColumnNumbers[i].ID;
// portalColumn.Cls = "x-column-padding";
// if (i == customer.SPortalColumnNumbers.Count - 1)
// {
// portalColumn.Cls = "x-column-padding1";
// }
// double columnWidth = 0.3;
// columnWidth = Convert.ToDouble(porColumnNumbers[i].Width) / 100.00;
// if (columnWidth <= 0)
// {
// columnWidth = 0.3;
// }
// portalColumn.ColumnWidth = columnWidth;
// this.Portal1.Items.Add(portalColumn);
// if (porColumnNumbers[i].SPortalColumns != null)
// {
// var columns = porColumnNumbers[i].SPortalColumns.OrderBy(p=>p.SortIndex).ToList();
// if (columns.Count > 0)
// {
// for (int j = 0; j < columns.Count(); j++)
// {
// Portlet portlet = new Portlet();
// //portlet.ID = "myPortlet" + columns[j].ID.ToString();
// //portlet.Tools.Add(new Tool(ToolType.Refresh, "Ext.getCmp('" + portlet.ID + "').reload(true);", "Refresh Portlet"));
// //portlet.Tools.Add(new Tool(ToolType.Close, "Ext.getCmp('" + portlet.ID + "').hide();", "Close Portlet"));
// portlet.CloseAction = CloseAction.Hide;
// portlet.Title = columns[j].SPortal.Name;
// portlet.IconCls = columns[j].SPortal.iconCls;
// portlet.AutoHeight = true;
// portlet.DirectEvents.Hide.Event += new ComponentDirectEvent.DirectEventHandler(Hide_Event);
// portlet.DirectEvents.Hide.ExtraParams.Add(new Ext.Net.Parameter("ID", columns[j].ID.ToString()));
// portlet.DirectEvents.Hide.ExtraParams.Add(new Ext.Net.Parameter("Name", columns[j].SPortal.Name));
// portlet.AutoWidth = true;
// portlet.ID = "portlet" + columns[j].SPortal.Url.Substring(columns[j].SPortal.Url.LastIndexOf("/") + 1,
// columns[j].SPortal.Url.LastIndexOf(".aspx") - columns[j].SPortal.Url.LastIndexOf("/") - 1);
// if (!string.IsNullOrEmpty(columns[j].SPortal.Url))
// {
// switch (portlet.Title)
// {
// //case "公告通知":
// // {
// // //portlet.ID = "portletBullet";
// // portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// // }
// // break;
// //case "充值警告":
// // {
// // //portlet.ID = "portletWarnDetail";
// // portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// // }
// // break;
// case "入账审批":
// {
// //portlet.ID = "portletRelatedContractPayment";
// portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url + "?date=" + cbYear.Value + "-" + cbMonth.Value + "-" + "01";
// string handler = "var year = Ext.getCmp('cbYear').getValue();var month = Ext.getCmp('cbMonth').getValue();var date = year + \"-\" + month + \"-\" + \"01\";window.frames[\"" + portlet.ID + "_IFrame\"].location = '" + columns[j].SPortal.Url + "?date=' + date;";
// portlet.Tools.Add(new Tool(ToolType.Refresh, handler, "Refresh Portlet"));
// }
// break;
// //case "待审批":
// // {
// // //portlet.ID = "portletApproval";
// // portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// // }
// // break;
// //case "客户类型分析":
// // {
// // //portlet.ID = "portletCustomerTypeAnalyse";
// // portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// // }
// // break;
// default:
// {
// portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url + "?date=" + cbYear.Value + "-" + cbMonth.Value + "-" + "01";
// string handler = "var year = Ext.getCmp('cbYear').getValue();var month = Ext.getCmp('cbMonth').getValue();var date = year + \"-\" + month + \"-\" + \"01\";window.frames[\"" + portlet.ID + "_IFrame\"].location = '" + columns[j].SPortal.Url + "?date=' + date;";
// portlet.Tools.Add(new Tool(ToolType.Refresh, handler, "Refresh Portlet"));
// //portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// //portlet.Tools.Add(new Tool(ToolType.Refresh, "Ext.getCmp('" + portlet.ID + "').reload(true);", "Refresh Portlet"));
// }
// break;
// }
// //portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
// //portlet.Tools.Add(new Tool(ToolType.Refresh, "Ext.getCmp('" + portlet.ID + "').reload(true);", "Refresh Portlet"));
// portlet.Tools.Add(new Tool(ToolType.Close, "Ext.getCmp('" + portlet.ID + "').hide();", "Close Portlet"));
// portlet.AutoLoad.Mode = LoadMode.IFrame;
// portlet.AutoLoad.ShowMask = true;
// portlet.AutoLoad.MaskMsg = "正在初始化 " + columns[j].SPortal.Name + "，请稍等...";
// }
// portalColumn.AutoScroll = false;
// portalColumn.Items.Add(portlet);
// portalColumn.DoLayout();
// }
// }
// }
// }
// }
//}
protected void BindColumn()
{
var customer = HozestERPContext.Current.User;
if (customer != null)
{
var porColumnNumbers = base.PortalService.GetPortalColumnNumberList(customer.CustomerID);
for (int i = 0; i < porColumnNumbers.Count; i++)
{
PortalColumn portalColumn = new PortalColumn();
portalColumn.ID = "PortalColumn" + porColumnNumbers[i].ID;
portalColumn.Cls = "x-column-padding";
if (i == customer.SPortalColumnNumbers.Count - 1)
{
portalColumn.Cls = "x-column-padding1";
}
double columnWidth = 0.3;
columnWidth = Convert.ToDouble(porColumnNumbers[i].Width) / 100.00;
if (columnWidth <= 0)
{
columnWidth = 0.3;
}
portalColumn.ColumnWidth = columnWidth;
this.Portal1.Items.Add(portalColumn);
if (porColumnNumbers[i].SPortalColumns != null)
{
var columns = porColumnNumbers[i].SPortalColumns.OrderBy(p => p.SortIndex).ToList();
if (columns.Count > 0)
{
for (int j = 0; j < columns.Count(); j++)
{
Portlet portlet = new Portlet();
//portlet.ID = "myPortlet" + columns[j].ID.ToString();
portlet.ID = "portlet" + columns[j].SPortal.Url.Substring(columns[j].SPortal.Url.LastIndexOf("/") + 1,
columns[j].SPortal.Url.LastIndexOf(".aspx") - columns[j].SPortal.Url.LastIndexOf("/") - 1);
portlet.Tools.Add(new Tool(ToolType.Refresh, "Ext.getCmp('" + portlet.ID + "').reload(true);", "Refresh Portlet"));
portlet.Tools.Add(new Tool(ToolType.Close, "Ext.getCmp('" + portlet.ID + "').hide();", "Close Portlet"));
portlet.CloseAction = CloseAction.Hide;
portlet.Title = columns[j].SPortal.Name;
portlet.IconCls = columns[j].SPortal.iconCls;
portlet.DirectEvents.Hide.Event += new ComponentDirectEvent.DirectEventHandler(Hide_Event);
portlet.DirectEvents.Hide.ExtraParams.Add(new Ext.Net.Parameter("ID", columns[j].ID.ToString()));
portlet.DirectEvents.Hide.ExtraParams.Add(new Ext.Net.Parameter("Name", columns[j].SPortal.Name));
portlet.AutoWidth = true;
if (!string.IsNullOrEmpty(columns[j].SPortal.Url))
{
portlet.AutoLoad.Url = CommonHelper.GetStoreLocation() + columns[j].SPortal.Url;
portlet.AutoLoad.Mode = LoadMode.IFrame;
portlet.AutoLoad.ShowMask = true;
portlet.AutoLoad.MaskMsg = "正在初始化 " + columns[j].SPortal.Name + "，请稍等...";
}
portalColumn.AutoScroll = false;
portalColumn.Items.Add(portlet);
portalColumn.DoLayout();
}
}
}
}
}
}
public static Portlet[] GetPortlets()
{
int[] portletIDs = Settings.UserDB.ReadIntArray("DashboardPortlets");
if (portletIDs.Length < 1)
{
portletIDs = Settings.SystemDB.ReadIntArray("DefaultPortletIDs");
Settings.UserDB.WriteIntArray("DashboardPortlets", portletIDs);
}
int maxCol = GetColumnCount() - 1;
List <Portlet> portlets = new List <Portlet>();
foreach (int id in portletIDs)
{
try
{
Portlet portlet = LoadPortlet(GetPortletID(id));
Report report = Reports.GetReport(UserSession.LoginUser, id);
if (report != null)
{
if (portlet == null)
{
portlet = new Portlet();
portlet.Caption = report.Name;
portlet.ID = GetPortletID(id);
portlet.ReportID = id;
portlet.IsOpen = true;
portlet.X = 1;
portlet.Y = -1;
portlet.Height = 250;
if (portlet.Caption.IndexOf("My Open Ticket Summary") > -1 || portlet.Caption.IndexOf("Waiting On Customer Tickets") > -1 || portlet.Caption.IndexOf("My Unassigned Group Tickets") > -1)
{
portlet.X = 0;
}
SavePortlet(portlet);
}
if (portlet.X - maxCol < 0)
{
portlet.X = 0;
}
if (maxCol - portlet.X < 0)
{
portlet.X = maxCol;
}
portlets.Add(portlet);
portlet.Html = GetPortletHtml(portlet, report);
}
}
catch (Exception e)
{
}
}
FixPortletYs(portlets, 0);
FixPortletYs(portlets, 1);
FixPortletYs(portlets, 2);
FixPortletYs(portlets, 3);
FixPortletYs(portlets, 4);
portlets.Sort(ComparePortletY);
return(portlets.ToArray());
}
public override void PortletRemovedFromPage(Portlet portlet, PortalPageInfo page)
{
try
{
var mapper = new Mappers.NHSimpleQuerySettingsMapper();
mapper.Delete(mapper.GetSettings(portlet.ID).ToList());
}
catch (Exception ex)
{
ExceptionManager.Publish(ex);
}
}