Line 1: '
Line 2: ' DotNetNuke® - http://www.dotnetnuke.com
Line 3: ' Copyright (c) 2002-2006
Line 4: ' by Perpetual Motion Interactive Systems Inc. ( http://www.perpetualmotion.ca )
Line 5: '
Line 6: ' Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
Line 7: ' documentation files (the "Software"), to deal in the Software without restriction, including without limitation
Line 8: ' the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and
Line 9: ' to permit persons to whom the Software is furnished to do so, subject to the following conditions:
Line 10: '
Line 11: ' The above copyright notice and this permission notice shall be included in all copies or substantial portions
Line 12: ' of the Software.
Line 13: '
Line 14: ' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
Line 15: ' TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
Line 16: ' THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
Line 17: ' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
Line 18: ' DEALINGS IN THE SOFTWARE.
Line 19: '
Line 20:
Line 21: Imports System
Line 22: Imports System.Configuration
Line 23: Imports System.Data
Line 24: Imports System.Xml
Line 25: Imports System.Web
Line 26: Imports System.Collections.Generic
Line 27: Imports DotNetNuke
Line 28: Imports DotNetNuke.Services.Search
Line 29: Imports DotNetNuke.Common.Utilities.XmlUtils
Line 30:
Line 31: Namespace DotNetNuke.Modules.Reports
Line 32:
Line 33: ''' <summary>
Line 34: ''' The Controller class for Reports
Line 35: ''' </summary>
Line 36: ''' <remarks>
Line 37: ''' </remarks>
Line 38: ''' <history>
Line 39: ''' [anurse] 06/16/2006 Created
Line 40: ''' </history>
Line 41: ''' -----------------------------------------------------------------------------
Line 42: Public Class ReportsController
Line 43: Implements Entities.Modules.ISearchable
Line 44: Implements Entities.Modules.IPortable
Line 45:
Line 46: #Region " Public Constants "
Line 47:
Line 48: Public Const SETTING_ReportTitle As String = "dnn_ReportTitle"
Line 49: Public Const SETTING_ReportDescription As String = "dnn_ReportDescription"
Line 50: Public Const SETTING_ReportQuery As String = "dnn_ReportQuery"
Line 51: Public Const SETTING_ReportCreatedBy As String = "dnn_ReportCreatedBy"
Line 52: Public Const SETTING_ReportCreatedOn As String = "dnn_ReportCreatedOn"
Line 53: Public Const SETTING_EnablePaging As String = "dnn_ReportEnablePaging"
Line 54: Public Const SETTING_EnableSorting As String = "dnn_ReportEnableSorting"
Line 55: Public Const SETTING_PageSize As String = "dnn_ReportPageSize"
Line 56: Public Const SETTING_ShowHeader As String = "dnn_ReportShowHeader"
Line 57:
Line 58: Public Const CACHEKEY_Reports As String = "dnn_ReportCache"
Line 59:
Line 60: Public Const MODULE_VERSION As String = "04.03.01"
Line 61:
Line 62: #End Region
Line 63:
Line 64: #Region " Public Methods "
Line 65:
Line 66: ''' -----------------------------------------------------------------------------
Line 67: ''' <summary>
Line 68: ''' Gets the report associated with a reports module
Line 69: ''' </summary>
Line 70: ''' <remarks>
Line 71: ''' </remarks>
Line 72: ''' <param name="ModuleId">The Id of the module</param>
Line 73: ''' <history>
Line 74: ''' [anurse] 06/16/2006 Created
Line 75: ''' </history>
Line 76: ''' -----------------------------------------------------------------------------
Line 77: Public Shared Function GetReport(ByVal ModuleId As Integer) As ReportInfo
Line 78:
Line 79: ' Extract the Title, Description and Query from the settings
Line 80: Dim objModuleController As New DotNetNuke.Entities.Modules.ModuleController
Line 81: Dim objSettings As Hashtable = objModuleController.GetModuleSettings(ModuleId)
Line 82: Dim objReport As New ReportInfo()
Line 83: Dim strNotSpecified As String = String.Format("<{0}>", Localization.GetString("Not_Specified.Text"))
Line 84:
Line 85: objReport.Query = ExtractString(objSettings(ReportsController.SETTING_ReportQuery), Null.NullString)
Line 86: If String.IsNullOrEmpty(objReport.Query) Then
Line 87: Return Nothing ' If there's no query, there's no report
Line 88: End If
Line 89:
Line 90: objReport.Title = ExtractString(objSettings(ReportsController.SETTING_ReportTitle), strNotSpecified)
Line 91: objReport.Description = ExtractString(objSettings(ReportsController.SETTING_ReportDescription), strNotSpecified)
Line 92: objReport.CreatedOn = ExtractDate(objSettings(ReportsController.SETTING_ReportCreatedOn))
Line 93: objReport.CreatedBy = ExtractInteger(objSettings(ReportsController.SETTING_ReportCreatedBy))
Line 94: objReport.ModuleID = ModuleId
Line 95: Return objReport
Line 96:
Line 97: End Function
Line 98:
Line 99: ''' -----------------------------------------------------------------------------
Line 100: ''' <summary>
Line 101: ''' Sets the report associated with a reports module
Line 102: ''' </summary>
Line 103: ''' <remarks>
Line 104: ''' </remarks>
Line 105: ''' <param name="ModuleId">The ModuleId to set the report for</param>
Line 106: ''' <param name="objReport">The ReportInfo object</param>
Line 107: ''' <history>
Line 108: ''' [anurse] 06/16/2006 Created
Line 109: ''' </history>
Line 110: ''' -----------------------------------------------------------------------------
Line 111: Public Shared Sub SetReport(ByVal ModuleId As Integer, ByVal objReport As ReportInfo)
Line 112:
Line 113: ' Update the module settings with the data from the report
Line 114: Dim objModuleController As New DotNetNuke.Entities.Modules.ModuleController
Line 115: objModuleController.UpdateModuleSetting(ModuleId, ReportsController.SETTING_ReportTitle, objReport.Title)
Line 116: objModuleController.UpdateModuleSetting(ModuleId, ReportsController.SETTING_ReportDescription, objReport.Description)
Line 117: objModuleController.UpdateModuleSetting(ModuleId, ReportsController.SETTING_ReportQuery, objReport.Query)
Line 118: objModuleController.UpdateModuleSetting(ModuleId, ReportsController.SETTING_ReportCreatedOn, objReport.CreatedOn)
Line 119: objModuleController.UpdateModuleSetting(ModuleId, ReportsController.SETTING_ReportCreatedBy, objReport.CreatedBy)
Line 120:
Line 121: End Sub
Line 122:
Line 123: ''' -----------------------------------------------------------------------------
Line 124: ''' <summary>
Line 125: ''' Executes a report and returns the results
Line 126: ''' </summary>
Line 127: ''' <remarks>
Line 128: ''' </remarks>
Line 129: ''' <param name="objReport">The ReportInfo object</param>
Line 130: ''' <exception cref="System.ArgumentNullException">
Line 131: ''' The value of <paramref name="objReport"/> was null (Nothing in Visual Basic)
Line 132: ''' </exception>
Line 133: ''' <history>
Line 134: ''' [anurse] 06/16/2006 Created
Line 135: ''' </history>
Line 136: ''' -----------------------------------------------------------------------------
Line 137: Public Shared Function ExecuteReport(ByVal objReport As ReportInfo) As DataTable
Line 138:
Line 139: If objReport Is Nothing Then Throw New ArgumentNullException("objReport")
Line 140:
Line 141: If String.IsNullOrEmpty(objReport.Query) Then
Line 142: Return Nothing ' If there's no query, there's no report
Line 143: Else
Line 144: Dim strCacheKey As String = String.Concat(CACHEKEY_Reports, objReport.ModuleID)
Line 145: Dim objCache As Object = DataCache.GetCache(strCacheKey)
Line 146: Dim dataTable As DataTable = Nothing
Line 147: If objCache IsNot Nothing AndAlso TypeOf objCache Is DataTable Then
Line 148: dataTable = DirectCast(objCache, DataTable)
Line 149: Else
Line 150: Dim dr As IDataReader = DataProvider.Instance().ExecuteSQL(objReport.Query)
Line 151: If dr Is Nothing Then Return Nothing
Line 152: dataTable = New DataTable("QueryResults")
Line 153: dataTable.Load(dr)
Line 154: DataCache.SetCache(strCacheKey, dataTable)
Line 155: End If
Line 156: Return dataTable
Line 157: End If
Line 158:
Line 159: End Function
Line 160:
Line 161: '''-----------------------------------------------------------------------------
Line 162: ''' <summary>
Line 163: ''' Removes "Bad" SQL Commands from the specified string
Line 164: ''' </summary>
Line 165: ''' <param name="strSQL">This is the string to be filtered</param>
Line 166: ''' <returns>A filtered version of <paramref name="strSQL" /> with commands such as INSERT or DELETE removed</returns>
Line 167: ''' <history>
Line 168: ''' [anurse] 6/20/2006 Created
Line 169: ''' </history>
Line 170: '''-----------------------------------------------------------------------------
Line 171: Public Shared Function FormatRemoveSQL(ByVal strSQL As String) As String
Line 172:
Line 173: Dim strCleanSQL As String = strSQL
Line 174:
Line 175: If strSQL <> Nothing Then
Line 176:
Line 177: ' each string in this array is one that must be removed from the SQL
Line 178: Dim BadSQL As String() = New String() {";", "--", "create", "drop", "insert", "delete", "update", "sp_", "xp_"}
Line 179:
Line 180: ' strip any dangerous SQL commands
Line 181: Dim intCommand As Integer
Line 182: For intCommand = 0 To BadSQL.Length - 1
Line 183: ' remove the current item in the "Bad SQL" list from the string by replacing it with a space
Line 184: strCleanSQL = Regex.Replace(strCleanSQL, Convert.ToString(BadSQL.GetValue(intCommand)), " ", _
Line 185: RegexOptions.IgnoreCase)
Line 186: Next
Line 187: End If
Line 188:
Line 189: ' return the clean SQL
Line 190: Return strCleanSQL
Line 191:
Line 192: End Function
Line 193:
Line 194: '''-----------------------------------------------------------------------------
Line 195: ''' <summary>
Line 196: ''' Clears the results that have been cached by the module.
Line 197: ''' </summary>
Line 198: ''' <param name="ModuleID">The ID of the module to clear cached results for</param>
Line 199: ''' <history>
Line 200: ''' [anurse] 6/20/2006 Created
Line 201: ''' [anurse] 6/21/2006 Documented
Line 202: ''' </history>
Line 203: '''-----------------------------------------------------------------------------
Line 204: Public Shared Sub ClearCachedResults(ByVal ModuleID As Integer)
Line 205: DataCache.RemoveCache(String.Concat(CACHEKEY_Reports, ModuleID))
Line 206: End Sub
Line 207:
Line 208: #End Region
Line 209:
Line 210: #Region " Optional Interfaces "
Line 211:
Line 212: ''' -----------------------------------------------------------------------------
Line 213: ''' <summary>
Line 214: ''' GetSearchItems implements the ISearchable Interface
Line 215: ''' </summary>
Line 216: ''' <remarks>
Line 217: ''' </remarks>
Line 218: ''' <param name="ModInfo">The ModuleInfo for the module to be Indexed</param>
Line 219: ''' <history>
Line 220: ''' [anurse] 06/16/2006 Created
Line 221: ''' </history>
Line 222: ''' -----------------------------------------------------------------------------
Line 223: Public Function GetSearchItems(ByVal ModInfo As Entities.Modules.ModuleInfo) As DotNetNuke.Services.Search.SearchItemInfoCollection Implements DotNetNuke.Entities.Modules.ISearchable.GetSearchItems
Line 224:
Line 225: ' Get the report
Line 226: Dim objReport As ReportInfo = GetReport(ModInfo.ModuleID)
Line 227:
Line 228: ' Execute the report and serialize it to Xml
Line 229: Dim objTable As DataTable = ExecuteReport(objReport)
Line 230: Dim sbContent As New StringBuilder
Line 231: If objTable IsNot Nothing Then
Line 232: objTable.WriteXml(New System.IO.StringWriter(sbContent))
Line 233: End If
Line 234:
Line 235: ' Build a search item
Line 236: Dim objPortalSec As New PortalSecurity
Line 237: Dim objSearchItem As New SearchItemInfo(objReport.Title, _
Line 238: objPortalSec.InputFilter(objReport.Description, PortalSecurity.FilterFlag.NoMarkup), objReport.CreatedBy, _
Line 239: objReport.CreatedOn, ModInfo.ModuleID, String.Empty, sbContent.ToString())
Line 240:
Line 241: ' Add it to the collection and return
Line 242: Dim objSearchItems As New SearchItemInfoCollection()
Line 243: objSearchItems.Add(objSearchItem)
Line 244: Return objSearchItems
Line 245:
Line 246: End Function
Line 247:
Line 248: ''' -----------------------------------------------------------------------------
Line 249: ''' <summary>
Line 250: ''' ExportModule implements the IPortable ExportModule Interface
Line 251: ''' </summary>
Line 252: ''' <remarks>
Line 253: ''' </remarks>
Line 254: ''' <param name="ModuleID">The Id of the module to be exported</param>
Line 255: ''' <history>
Line 256: ''' [anurse] 06/16/2006 Created
Line 257: ''' </history>
Line 258: ''' -----------------------------------------------------------------------------
Line 259: Public Function ExportModule(ByVal ModuleID As Integer) As String Implements DotNetNuke.Entities.Modules.IPortable.ExportModule
Line 260: Dim objReport As ReportInfo = GetReport(ModuleID)
Line 261: If objReport Is Nothing Then
Line 262: Return String.Empty
Line 263: End If
Line 264:
Line 265: Dim xmlBuilder As New StringBuilder
Line 266: xmlBuilder.AppendFormat("<title><![CDATA[{0}]]></title>", objReport.Title)
Line 267: xmlBuilder.AppendFormat("<description><![CDATA[{0}]]></description>", objReport.Description)
Line 268:
Line 269: ' Obscure the query...it isn't much, but it will help if Hosts want to protect their queries from prying eyes
Line 270: Dim queryBytes As Byte() = Encoding.Default.GetBytes(objReport.Query)
Line 271: xmlBuilder.AppendFormat("<query><![CDATA[{0}]]></query>", Convert.ToBase64String(queryBytes))
Line 272: Return xmlBuilder.ToString()
Line 273: End Function
Line 274:
Line 275: ''' -----------------------------------------------------------------------------
Line 276: ''' <summary>
Line 277: ''' ImportModule implements the IPortable ImportModule Interface
Line 278: ''' </summary>
Line 279: ''' <remarks>
Line 280: ''' </remarks>
Line 281: ''' <param name="ModuleID">The Id of the module to be imported</param>
Line 282: ''' <param name="Content">The content to be imported</param>
Line 283: ''' <param name="Version">The version of the module to be imported</param>
Line 284: ''' <param name="UserId">The Id of the user performing the import</param>
Line 285: ''' <history>
Line 286: ''' [anurse] 06/16/2006 Created
Line 287: ''' </history>
Line 288: ''' -----------------------------------------------------------------------------
Line 289: Public Sub ImportModule(ByVal ModuleID As Integer, ByVal Content As String, ByVal Version As String, ByVal UserId As Integer) Implements DotNetNuke.Entities.Modules.IPortable.ImportModule
Line 290:
Line 291: ' Check Access and Version
Line 292: Dim objUser As UserInfo = UserController.GetUser(-1, UserId, False)
Line 293: If objUser Is Nothing OrElse Not objUser.IsSuperUser OrElse Not Version.Equals(MODULE_VERSION) Then Return
Line 294:
Line 295: Dim objNewReport As New ReportInfo
Line 296: Dim xmlDoc As New XmlDocument
Line 297: xmlDoc.LoadXml(String.Format("<content>{0}</content>", Content))
Line 298: Dim xmlRoot As XmlElement = xmlDoc.DocumentElement
Line 299:
Line 300: objNewReport.Title = XmlUtils.GetNodeValue(xmlRoot, "title", String.Empty)
Line 301: objNewReport.Description = XmlUtils.GetNodeValue(xmlRoot, "description", String.Empty)
Line 302: objNewReport.CreatedOn = DateTime.Now
Line 303: objNewReport.CreatedBy = UserId
Line 304:
Line 305: ' Deobscure the query
Line 306: Dim b64Query As String = XmlUtils.GetNodeValue(xmlRoot, "query", String.Empty)
Line 307: Dim query As String
Line 308: If String.IsNullOrEmpty(b64Query) Then
Line 309: query = String.Empty
Line 310: Else
Line 311: Dim queryBytes As Byte() = Convert.FromBase64String(b64Query)
Line 312: query = Encoding.Default.GetString(queryBytes)
Line 313: End If
Line 314: objNewReport.Query = query
Line 315:
Line 316: SetReport(ModuleID, objNewReport)
Line 317: ClearCachedResults(ModuleID)
Line 318: End Sub
Line 319:
Line 320: #End Region
Line 321:
Line 322: #Region " Private Helper Functions "
Line 323:
Line 324: Private Shared Function ExtractString(ByVal obj As Object, ByVal strNullString As String) As String
Line 325: If obj Is Nothing Then
Line 326: Return strNullString
Line 327: Else
Line 328: Return obj.ToString()
Line 329: End If
Line 330: End Function
Line 331:
Line 332: Private Shared Function ExtractDate(ByVal obj As Object) As DateTime
Line 333: Dim dateRet As DateTime = Null.NullDate
Line 334: If obj IsNot Nothing Then
Line 335: Try
Line 336: dateRet = DateTime.Parse(obj.ToString())
Line 337: Catch ex As FormatException
Line 338: ' make sure dateRet is set to null
Line 339: dateRet = Null.NullDate
Line 340: End Try
Line 341: End If
Line 342: Return dateRet
Line 343: End Function
Line 344:
Line 345: Private Shared Function ExtractInteger(ByVal obj As Object) As Integer
Line 346: Dim intRet As Integer = Null.NullInteger
Line 347: If obj IsNot Nothing Then
Line 348: Try
Line 349: intRet = Integer.Parse(obj.ToString())
Line 350: Catch ex As FormatException
Line 351: ' make sure intRet is set to null
Line 352: intRet = Null.NullInteger
Line 353: End Try
Line 354: End If
Line 355: Return intRet
Line 356: End Function
Line 357:
Line 358: #End Region
Line 359:
Line 360: End Class
Line 361: End Namespace
Line 362:
|