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) { 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; } }