Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Web.HttpContext
Imports System.Web
Imports System.Net.Mail
Imports System.Xml
Imports CharterDotCom.CommonEntities.Library
Imports CharterDotCom.CommonEntities.Library.EntitySupport
Imports CheckFirmwareUpgradeEligibility

Public Class ProviderManager
  Inherits ISupportProvider


  Private _cmd As DbCommand
  Private _db As Database

  Public Sub New()
    Me.DB = DatabaseFactory.CreateDatabase("Charter.Website.WS.CommerceEngineConnectionString")
        'Dim d As DALTableAdapters.WebConfigTableAdapter = New DALTableAdapters.WebConfigTableAdapter
        ' d.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("Charter.Website.WS.CommerceEngineConnectionString").ConnectionString
  End Sub

  'Public Sub New(ByVal args As XmlNode)
  '  ''If (Not args Is Nothing) AndAlso (Not args.ChildNodes(0) Is Nothing) AndAlso _
  '  ''                (args.ChildNodes(0).LocalName = Constant.CONFIG_PROVIDERDATA) Then
  '  ''  For Each providerData As XmlNode In args.ChildNodes(0).ChildNodes
  '  ''    Select Case providerData.LocalName.ToUpper
  '  ''      Case Constant.CONFIG_CONNECTIONSTRING
  '  ''        Me.DB = DatabaseFactory.CreateDatabase(providerData.InnerText)
  '  ''      Case Else
  '  ''    End Select
  '  ''  Next
  '  ''Else
  '  ''  Throw New Exception(Constant.EXCEPTION_CONFIGURATION)
  '  ''End If
  'End Sub


  Public Shared Function Instance() As ProviderManager
    'Try
    '    If Current.Session("ContentData") Is Nothing Then
    '        'If user instance does not exist, create one
    '        Current.Session("ContentData") = New ProviderManager()
    '        Return CType(Current.Session("ProviderManager"), ProviderManager)
    '    Else
    '        'If user instance already exists, return that
    '        Return CType(Current.Session("ProviderManager"), ProviderManager)
    '    End If
    'Catch ex As Exception
    '    Throw ex
    'End Try
    Return Nothing
  End Function

  Private Property DB() As Database
    Get
      Return _db
    End Get
    Set(ByVal value As Database)
      _db = value
    End Set
  End Property
  Private Property CMD() As DbCommand
    Get
      Return _cmd
    End Get
    Set(ByVal value As DbCommand)
      _cmd = value
    End Set
  End Property

  Public Overrides Sub AddDocumentComments(ByVal obj As IProviderParameters, ByVal userid As Int32)
    Try


      Dim objRequest As New AddDocumentCommentsRequest
      objRequest = CType(obj, AddDocumentCommentsRequest)


      '############################################################
      CMD = DB.GetStoredProcCommand("SUPPORT.ADD_DOCUMENT_COMMENT")
      '############################################################


      If objRequest.DocumentID > 0 Then
        DB.AddInParameter(CMD, "@DOCUMENTID", DbType.Int32, objRequest.DocumentID)
      End If

      DB.AddInParameter(CMD, "@HELPFUL", DbType.Boolean, objRequest.Helpful)
      DB.AddInParameter(CMD, "@RATING", DbType.Int32, objRequest.Rating)


      If objRequest.Comments.Trim() <> "" Then
        DB.AddInParameter(CMD, "@COMMENTS", DbType.String, objRequest.Comments.Trim())
      End If

      If Not (objRequest.CreatedBy Is Nothing) Then
        DB.AddInParameter(CMD, "@CREATEDBY", DbType.String, objRequest.CreatedBy)
      End If


      DB.ExecuteDataSet(CMD)

    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Function GetRelatedDocuments(ByVal ID As Integer) As Dictionary(Of String, Object)
    Try
      Dim docs As New Dictionary(Of String, Object)
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_RELATED_DOCUMENTS")

      Dim Parameter1 As DbParameter = command.CreateParameter()
      With Parameter1
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = HttpUtility.HtmlEncode(ID)
      End With

      With command.Parameters
        .Add(Parameter1)
      End With


      Using dataReader As IDataReader = DB.ExecuteReader(command)
        Dim lCnt As Integer = 0
        While (dataReader.Read())
          Dim lrow As New Dictionary(Of String, Object)
          lrow.Add("HTMLDOcumentID", HttpUtility.HtmlDecode(dataReader("HTMLDOcumentID")))
          lrow.Add("Title", HttpUtility.HtmlDecode(dataReader("Title")))
          docs.Add(lCnt, lrow)
          lCnt = lCnt + 1
        End While

        dataReader.Close()
      End Using

      Return docs

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Sub RemoveDocumentRelation(ByVal ID As Integer, ByRef IDtoRemove As Integer, ByVal userid As Int32)
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.REMOVE_RELATED_ARTICLES")

      Dim ArticleIDParameter As DbParameter = command.CreateParameter()
      With ArticleIDParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@ArticleID"
        .Value = ID
      End With

      Dim RelatedArticleIDParameter As DbParameter = command.CreateParameter()
      With RelatedArticleIDParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@RelatedArticleID"
        .Value = IDtoRemove
      End With

      With command.Parameters
        .Add(ArticleIDParameter)
        .Add(RelatedArticleIDParameter)
      End With

      DB.ExecuteNonQuery(command)


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Function GetAllDocuments() As Dictionary(Of String, Object)
    Try
      Dim docs As New Dictionary(Of String, Object)
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_ALL_DOCUMENTS")

      Using dataReader As IDataReader = DB.ExecuteReader(command)
        Dim lCnt As Integer = 0
        While (dataReader.Read())
          Dim lrow As New Dictionary(Of String, Object)
          lrow.Add("HTMLDOcumentID", HttpUtility.HtmlDecode(dataReader("HTMLDOcumentID")))
          lrow.Add("Title", HttpUtility.HtmlDecode(dataReader("Title")))
          docs.Add(lCnt, lrow)
          lCnt = lCnt + 1
        End While


        ' clean up
        dataReader.Close()
      End Using

      Return docs

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Sub RelateDocument(ByVal ID As Integer, ByRef IDtoRelate As Integer, ByVal userid As Int32)
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.ADD_RELATED_ARTICLE")

      Dim ArticleIDParameter As DbParameter = command.CreateParameter()
      With ArticleIDParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@ArticleID"
        .Value = ID
      End With

      Dim RelatedArticleIDParameter As DbParameter = command.CreateParameter()
      With RelatedArticleIDParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@RelatedArticleID"
        .Value = IDtoRelate
      End With

      With command.Parameters
        .Add(ArticleIDParameter)
        .Add(RelatedArticleIDParameter)
      End With

      DB.ExecuteNonQuery(command)


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Function AddDocument(ByVal folderID As Integer, ByVal HTML As String, ByVal ID As Integer, ByVal KeyWords As String, ByVal Summary As String, ByVal Title As String, ByVal userid As Int32) As Integer
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.ADD_DOCUMENT")

      Dim folderParameter As DbParameter = command.CreateParameter()
      With folderParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = folderID
      End With

      Dim titleParameter As DbParameter = command.CreateParameter()
      With titleParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@title"
        .Value = HttpUtility.HtmlEncode(Title)
      End With

      Dim summaryParameter As DbParameter = command.CreateParameter()
      With summaryParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@summary"
        .Value = HttpUtility.HtmlEncode(Summary)
      End With

      Dim htmlParameter As DbParameter = command.CreateParameter()
      With htmlParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@HTML"
        .Value = HttpUtility.HtmlEncode(HTML)
      End With

      Dim keywordsParameter As DbParameter = command.CreateParameter()
      With keywordsParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@KeyWords"
        .Value = HttpUtility.HtmlEncode(KeyWords)
      End With


      Dim documentParameter As DbParameter = command.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Output
        .ParameterName = "@documentID"
      End With

      With command.Parameters
        .Add(folderParameter)
        .Add(titleParameter)
        .Add(summaryParameter)
        .Add(htmlParameter)
        .Add(keywordsParameter)
        .Add(documentParameter)
      End With

      DB.ExecuteNonQuery(command)

      ID = CInt(documentParameter.Value)

      Return ID

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Function AddFolder(ByVal ID As Integer, ByVal Name As String, ByVal NumberOfChildren As Integer, ByVal ParentFolderID As Integer, ByVal userid As Int32) As Integer
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.ADD_DOCUMENT_FOLDER")

      Dim titleParameter As DbParameter = command.CreateParameter()
      With titleParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@name"
        .Value = HttpUtility.HtmlEncode(Name)
      End With

      Dim parentParameter As DbParameter = command.CreateParameter()
      With parentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@parent"
        .Value = ParentFolderID
      End With

      Dim documentParameter As DbParameter = command.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Output
        .ParameterName = "@folderID"
      End With

      With command.Parameters
        .Add(titleParameter)
        .Add(parentParameter)
        .Add(documentParameter)
      End With

      DB.ExecuteNonQuery(command)

      Return documentParameter.Value

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Sub DeleteDocument(ByVal documentID As Integer, ByVal userid As Int32)
    Try
      Dim documentCommand As DbCommand = DB.GetStoredProcCommand("SUPPORT.REMOVE_DOCUMENT")

      Dim documentParameter As DbParameter = documentCommand.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@documentID"
        .Value = documentID
      End With

      documentCommand.Parameters.Add(documentParameter)

      DB.ExecuteNonQuery(documentCommand)

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Sub DeleteFolder(ByVal FolderID As Integer, ByVal userid As Int32)
    Try
      ' move any documents in the folder to the unassigned folder
      Dim moveCommand As DbCommand = DB.GetStoredProcCommand("SUPPORT.REMOVE_FOLDER")

      Dim moveUnassignedParameter As DbParameter = moveCommand.CreateParameter()
      With moveUnassignedParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@unassignedID"
        .Value = 1 'Global.Constants.Folders.Unassigned
      End With

      Dim moveDeleteParameter As DbParameter = moveCommand.CreateParameter()
      With moveDeleteParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = FolderID
      End With

      With moveCommand.Parameters
        .Add(moveUnassignedParameter)
        .Add(moveDeleteParameter)
      End With

      DB.ExecuteNonQuery(moveCommand)

      ' clear the parent folder ID of any child folders
      Dim clearSqlStatement As String = _
          "Update support.DocumentFolder Set ParentFolderID = 0 Where ParentFolderID = @folderID"
      Dim clearCommand As DbCommand = DB.GetSqlStringCommand(clearSqlStatement)

      Dim clearParentParameter As DbParameter = clearCommand.CreateParameter()
      With clearParentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = FolderID
      End With

      clearCommand.Parameters.Add(clearParentParameter)

      DB.ExecuteNonQuery(clearCommand)

      ' delete the folder
      Dim deleteSqlStatement As String = "Delete From support.DocumentFolder Where DocumentFolderID = @folderID"
      Dim deleteCommand As DbCommand = DB.GetSqlStringCommand(deleteSqlStatement)

      Dim deleteParameter As DbParameter = deleteCommand.CreateParameter()
      With deleteParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = FolderID
      End With

      deleteCommand.Parameters.Add(deleteParameter)

      DB.ExecuteNonQuery(deleteCommand)

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Function GetDistinctKeyWords() As Dictionary(Of String, Object)
    'Try
    '    Dim keyWords As New Dictionary(Of String, Object)

    '    Dim sqlStatement As String = "Select Distinct Word From support.KeyWord Order By Word"
    '    Dim command As DbCommand = DB.GetSqlStringCommand(sqlStatement)
    '    Using dataReader As IDataReader = DB.ExecuteReader(command)

    '        Dim lCnt As Integer = 0
    '        While (dataReader.Read())
    '            keyWords.Add(lcnt, HttpUtility.HtmlDecode(dataReader("Word")))
    '            lCnt = lCnt + 1
    '        End While

    '        ' clean up
    '        dataReader.Close()
    '    End Using

    '    Return keyWords

    'Catch ex As Exception
    '    Throw ex
    'Finally

    ' End Try
        Return Nothing
    End Function

    '#####################################################
    'THIS IS USED FOR THE SEARCH CAPABILITY BASED ON 
    'KEYWORDS/ID. -NAQIB 9/23/08  FOR ISSUE ID: 2362   
    '#####################################################
    Public Overrides Function GetDocumentByKeyword(ByVal Keyword As String) As Dictionary(Of String, Object)
        Try

            Dim documents As New Dictionary(Of String, Object)
            Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_DOCUMENT_BYKEYWORD")

            Dim parameter As DbParameter = command.CreateParameter()
            With parameter
                .DbType = DbType.String
                .Direction = ParameterDirection.Input
                .ParameterName = "@KeyWord"
                .Value = Keyword
            End With

            command.Parameters.Add(parameter)

            Using dataReader As IDataReader = DB.ExecuteReader(command)
                Dim lCnt As Integer = 0
                Dim obj2 As Object = Nothing
                While (dataReader.Read())
                    Dim lrow As New Dictionary(Of String, Object)
                    obj2 = HttpUtility.HtmlDecode(dataReader("HTMLDOcumentID"))
                    Try
                        Dim docid As Int32 = CType(obj2, Int32)
                        Dim title As Object = dataReader("Title")
                        Dim summaryu As Object = dataReader("Title")
                        Dim summary As Object = dataReader("Title")
                        Dim html As Object = dataReader("Title")
                        lrow.Add("HTMLDOcumentID", HttpUtility.HtmlDecode(IIf(dataReader("HTMLDOcumentID") Is DBNull.Value, -1, dataReader("HTMLDOcumentID"))))
                        lrow.Add("Title", HttpUtility.HtmlDecode(IIf(dataReader("Title") Is DBNull.Value, "", dataReader("Title"))))
                        lrow.Add("Summary", HttpUtility.HtmlDecode(IIf(dataReader("Summary") Is DBNull.Value, "", dataReader("Summary"))))
                        lrow.Add("HTML", HttpUtility.HtmlDecode(IIf(dataReader("HTML") Is DBNull.Value, "", dataReader("HTML"))))
                        'lrow.Add("KEYWORDS", "")
                        lrow.Add("KEYWORDS", HttpUtility.HtmlDecode(IIf(dataReader("KEYWORDS") Is DBNull.Value, "", dataReader("KEYWORDS"))))
                        documents.Add(lCnt, lrow)
                        lCnt = lCnt + 1
                    Catch ex As Exception
                        Console.WriteLine("message")
                    End Try
                End While
                ' clean up
                dataReader.Close()
            End Using

            Return documents

        Catch ex As Exception
            Throw ex
        Finally

        End Try
    End Function

    '#####################################################
    'THIS IS USED FOR THE SEARCH CAPABILITY BASED ON 
    'KEYWORDS.
    'Adapted from GetFolderDocuments. -NAQIB 9/23/08    
    '#####################################################
    Public Overrides Function GetDocumentFolderIDByDocumentID(ByVal DocumentID As Integer) As Dictionary(Of String, Object)
        Try
            Dim folder As New Dictionary(Of String, Object)

            Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_FOLDERID_BY_DOCUMENTID")

            Dim parameter As DbParameter = command.CreateParameter()
            With parameter
                .DbType = DbType.Int32
                .Direction = ParameterDirection.Input
                .ParameterName = "@DocumentID"
                .Value = DocumentID
            End With

            command.Parameters.Add(parameter)
            Using dataReader As IDataReader = DB.ExecuteReader(command)

                Dim lCnt As Integer = 0
                While (dataReader.Read())
                    Dim lrow As New Dictionary(Of String, Object)
                    lrow.Add("DocumentFolderID", dataReader("DocumentFolderID"))
                    lrow.Add("DocumentFolderName", HttpUtility.HtmlDecode(dataReader("DocumentFolderName")))
                    'lrow.Add("NumberOfChildren", HttpUtility.HtmlDecode(dataReader("NumberOfChildren")))
                    folder.Add(lCnt, lrow)
                    lCnt = lCnt + 1
                End While
                'clean up
                dataReader.Close()
            End Using

            Return folder

        Catch ex As Exception
            Throw ex
        Finally

        End Try
    End Function

  Public Overrides Function GetDocument(ByVal documentID As Integer) As Dictionary(Of String, Object)
    Try

      Dim document As New HTMLDocument
      Dim documentCommand As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_DOCUMENT")

      Dim documentParameter As DbParameter = documentCommand.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@documentID"
        .Value = documentID
      End With

      documentCommand.Parameters.Add(documentParameter)

      Dim result As New Dictionary(Of String, Object)
      Using documentDataReader As IDataReader = DB.ExecuteReader(documentCommand)
        While (documentDataReader.Read())
          result.Add("HTML", HttpUtility.HtmlDecode(documentDataReader("HTML")))
          result.Add("HTMLDocumentID", documentDataReader("HTMLDocumentID"))
          result.Add("Keywords", HttpUtility.HtmlDecode(IIf(documentDataReader("KEYWORDS") Is DBNull.Value, "", documentDataReader("KEYWORDS"))))
          result.Add("Summary", HttpUtility.HtmlDecode(documentDataReader("Summary")))
          result.Add("Title", HttpUtility.HtmlDecode(documentDataReader("Title")))
        End While

        ' clean up
        documentDataReader.Close()
      End Using


      Return result


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function

  Public Overrides Function GetEligibility(ByVal account As String, ByVal mac As String, ByVal phone As String, ByVal zipAccount As String, ByVal zipPhone As String) As String
      Try
          ' Dim account As String = "abc"
          ' Dim mac As String = "555555555555"
          ' Dim phone As String = "abc"
          ' Dim zipAccount As String = "abc"
          ' Dim zipPhone As String = "abc"

          Dim checkLegacyWIFI As ClsCheckLegacyWIFI = New ClsCheckLegacyWIFI()

          checkLegacyWIFI.AccountNumber = account
          checkLegacyWIFI.MacAddress = mac
          checkLegacyWIFI.PhoneNumber = phone
          checkLegacyWIFI.ZipCodeAccount = zipAccount
          checkLegacyWIFI.ZipCodePhone = zipPhone

          Dim eligibilityStatus As String
          eligibilityStatus = checkLegacyWIFI.checkEligibilityStatus(checkLegacyWIFI)

          Return eligibilityStatus
      Catch ex As Exception
          Throw ex
      Finally

      End Try
  End Function


  Public Overrides Function GetAllFolders() As System.Data.DataTable
    Try

      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_All_FOLDERS")

      Using ds As DataSet = DB.ExecuteDataSet(command)
        If Not ds Is Nothing AndAlso ds.Tables.Count Then
          Return ds.Tables(0)
        End If
      End Using

      Return Nothing
    Catch ex As Exception
      Throw ex
    End Try
  End Function

  Public Overrides Function GetDocumentFolders() As Dictionary(Of String, Object)
    Try

      Dim folders As New Dictionary(Of String, Object)

      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_DOCUMENTFOLDERS")

      Using dataReader As IDataReader = DB.ExecuteReader(command)

        Dim lCnt As Integer = 0
        While (dataReader.Read())
          Dim lrow As New Dictionary(Of String, Object)
          lrow.Add("DocumentFolderID", dataReader("DocumentFolderID"))
          lrow.Add("DocumentFolderName", HttpUtility.HtmlDecode(dataReader("DocumentFolderName")))
          folders.Add(lCnt, lrow)
          lCnt = lCnt + 1
        End While


        ' clean up
        dataReader.Close()
      End Using

      Return folders


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Function GetDocumentFolders(ByVal parentFolderID As Integer) As Dictionary(Of String, Object)
    Try
      Dim folders As New Dictionary(Of String, Object)

      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_SUBDOCUMENTFOLDERS")

      Dim parameter As DbParameter = command.CreateParameter()
      With parameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@ParentFolderID"
        .Value = parentFolderID
      End With

      command.Parameters.Add(parameter)
      Using dataReader As IDataReader = DB.ExecuteReader(command)

        Dim lCnt As Integer = 0
        While (dataReader.Read())
          Dim lrow As New Dictionary(Of String, Object)
          lrow.Add("DocumentFolderID", dataReader("DocumentFolderID"))
          lrow.Add("DocumentFolderName", HttpUtility.HtmlDecode(dataReader("DocumentFolderName")))
          lrow.Add("NumberOfChildren", HttpUtility.HtmlDecode(dataReader("NumberOfChildren")))
          folders.Add(lCnt, lrow)
          lCnt = lCnt + 1
        End While


        ' clean up
        dataReader.Close()
      End Using

      Return folders

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Function GetFolder(ByVal folderID As Integer) As Dictionary(Of String, Object)
    Try

      Dim folder As New DocumentFolder
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_FOLDER")

      Dim parameter As DbParameter = command.CreateParameter()
      With parameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = folderID
      End With

      command.Parameters.Add(parameter)

      Dim result As New Dictionary(Of String, Object)
      Using dataReader As IDataReader = DB.ExecuteReader(command)

        While (dataReader.Read())
          result.Add("DocumentFolderID", dataReader("DocumentFolderID"))
          result.Add("DocumentFolderName", dataReader("DocumentFolderName"))
          result.Add("ParentFolderID", dataReader("ParentFolderID"))
        End While

        ' clean up
        dataReader.Close()
      End Using

      Return result


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Function GetFolderDocuments(ByVal folderID As Integer) As Dictionary(Of String, Object)
    Try

      Dim documents As New Dictionary(Of String, Object)
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.GET_DOCUMENTS")

      Dim parameter As DbParameter = command.CreateParameter()
      With parameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@folderID"
        .Value = folderID
      End With

      command.Parameters.Add(parameter)

      Using dataReader As IDataReader = DB.ExecuteReader(command)
        Dim lCnt As Integer = 0
        Dim obj2 As Object = Nothing
        While (dataReader.Read())
          Dim lrow As New Dictionary(Of String, Object)
          obj2 = HttpUtility.HtmlDecode(dataReader("HTMLDOcumentID"))
            Try
             Dim docid As Int32 = CType(obj2, Int32)
             Dim title As Object = dataReader("Title")
             Dim summaryu As Object = dataReader("Title")
             Dim summary As Object = dataReader("Title")
             Dim html As Object = dataReader("Title")
             lrow.Add("HTMLDOcumentID", HttpUtility.HtmlDecode(IIf(dataReader("HTMLDOcumentID") Is DBNull.Value, -1, dataReader("HTMLDOcumentID"))))
             lrow.Add("Title", HttpUtility.HtmlDecode(IIf(dataReader("Title") Is DBNull.Value, "", dataReader("Title"))))
             lrow.Add("Summary", HttpUtility.HtmlDecode(IIf(dataReader("Summary") Is DBNull.Value, "", dataReader("Summary"))))
             lrow.Add("HTML", HttpUtility.HtmlDecode(IIf(dataReader("HTML") Is DBNull.Value, "", dataReader("HTML"))))
             'lrow.Add("KEYWORDS", "")
             lrow.Add("KEYWORDS", HttpUtility.HtmlDecode(IIf(dataReader("KEYWORDS") Is DBNull.Value, "", dataReader("KEYWORDS"))))
             documents.Add(lCnt, lrow)
             lCnt = lCnt + 1
            Catch ex As Exception
                Console.WriteLine("message")

            End Try
         
        End While


        ' clean up
        dataReader.Close()
      End Using

      Return documents

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Public Overrides Sub MoveDocument(ByVal documentID As Integer, ByVal newFolderID As Integer, ByVal userid As Int32)
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.MOVE_DOCUMENT")

      Dim documentParameter As DbParameter = command.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@documentID"
        .Value = documentID 'Global.Constants.Folders.Unassigned
      End With

      Dim newFolderParameter As DbParameter = command.CreateParameter()
      With newFolderParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@newFolderID"
        .Value = newFolderID
      End With

      With command.Parameters
        .Add(documentParameter)
        .Add(newFolderParameter)
      End With

      DB.ExecuteNonQuery(command)

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Sub UpdateDocument(ByVal HTML As String, ByVal ID As Integer, ByVal KeyWords As String, ByVal Summary As String, ByVal Title As String, ByVal userid As Int32)
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.UPDATE_DOCUMENT")

      Dim htmlParameter As DbParameter = command.CreateParameter()
      With htmlParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@HTML"
        .Value = HttpUtility.HtmlEncode(HTML)
      End With

      Dim documentParameter As DbParameter = command.CreateParameter()
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@documentID"
        .Value = ID
      End With

      Dim KeywordsParameter As DbParameter = command.CreateParameter()
      With KeywordsParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@keywords"
        .Value = HttpUtility.HtmlEncode(KeyWords)
      End With

      Dim summaryParameter As DbParameter = command.CreateParameter()
      With summaryParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@summary"
        .Value = HttpUtility.HtmlEncode(Summary)
      End With

      Dim titleParameter As DbParameter = command.CreateParameter()
      With titleParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@title"
        .Value = HttpUtility.HtmlEncode(Title)
      End With


      With command.Parameters
        .Add(titleParameter)
        .Add(KeywordsParameter)
        .Add(summaryParameter)
        .Add(htmlParameter)
        .Add(documentParameter)
      End With

      DB.ExecuteNonQuery(command)



    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub
  Public Overrides Sub UpdateFolder(ByVal ID As Integer, ByVal Name As String, ByVal NumberOfChildren As Integer, ByVal ParentFolderID As Integer, ByVal userid As Int32)
    Try
      Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.UPDATE_FOLDER")

      Dim titleParameter As DbParameter = command.CreateParameter()
      With titleParameter
        .DbType = DbType.String
        .Direction = ParameterDirection.Input
        .ParameterName = "@Name"
        .Value = HttpUtility.HtmlEncode(Name)
      End With

      Dim parentParameter As DbParameter = command.CreateParameter()
      With parentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@ParentID"
        .Value = ParentFolderID
      End With

      Dim folderParameter As DbParameter = command.CreateParameter()
      With folderParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@FolderID"
        .Value = ID
      End With

      With command.Parameters
        .Add(titleParameter)
        .Add(parentParameter)
        .Add(folderParameter)
      End With

      DB.ExecuteNonQuery(command)


    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Sub

  Public Overrides Function GetBinaryDocument(ByVal ID As Integer) As KeyValuePair(Of String, Byte())
    Dim sqlStatement As String = "Support.GET_SUPPORTDOCUMENT_BY_DOCUMENTID"

    Dim documentCommand As DbCommand = DB.GetStoredProcCommand(sqlStatement)
    Dim documentParameter As DbParameter = documentCommand.CreateParameter()

    Try
      With documentParameter
        .DbType = DbType.Int32
        .Direction = ParameterDirection.Input
        .ParameterName = "@HTMLDocumentID"
        .Value = ID
      End With

      documentCommand.Parameters.Add(documentParameter)

      Dim ds As DataSet
      Dim result As KeyValuePair(Of String, Byte())

      ds = DB.ExecuteDataSet(documentCommand)
      If Not (ds Is Nothing OrElse ds.Tables.Count = 0) Then
        If Not ds.Tables(0).Rows.Count = 0 Then
          result = New KeyValuePair(Of String, Byte())(ds.Tables(0).Rows(0).Item(0), ds.Tables(0).Rows(0).Item(1))
          Return result
        End If
      End If
      Return Nothing
    Catch ex As Exception
      Throw New ApplicationException("Error retriving Document")
    End Try
  End Function

	Public Overrides Sub SendEMail(ByVal FromMailID As String, ByVal ToMailID As String, ByVal Subject As String, ByVal Body As String, Optional ByVal isHTML As Boolean = False)
		Try
			Dim from_Config As String = CType(HttpContext.Current.Application("SMTP_SERVER"), String)
			Dim client As New SmtpClient(from_Config)
			Dim mailMsg As New MailMessage(FromMailID, ToMailID)
			With mailMsg
				.Subject = Subject
				.Body = Body
			End With
			mailMsg.IsBodyHtml = isHTML
			client.Send(mailMsg)

		Catch ex As Exception

		End Try
    End Sub

    Public Overrides Sub UpdateUploadedFile(ByVal ID As Integer, ByVal binaryDocument() As Byte, ByVal userid As Int32)
        Try
            Dim command As DbCommand = DB.GetStoredProcCommand("SUPPORT.uspUpdateUploadedFile")

            Dim documentParameter As DbParameter = command.CreateParameter()
            With documentParameter
                .DbType = DbType.Int32
                .Direction = ParameterDirection.Input
                .ParameterName = "@DocumentID"
                .Value = ID
            End With

            Dim binaryParameter As DbParameter = command.CreateParameter()
            With binaryParameter
                .DbType = DbType.Binary
                .Direction = ParameterDirection.Input
                .ParameterName = "@BinaryDocument"
                .Value = binaryDocument
            End With

            Dim updatedByParameter As DbParameter = command.CreateParameter()
            With updatedByParameter
                .DbType = DbType.Int32
                .Direction = ParameterDirection.Input
                .ParameterName = "@UpdatedBy"
                .Value = userid
            End With

            With command.Parameters
                .Add(updatedByParameter)
                .Add(binaryParameter)
                .Add(documentParameter)
            End With

            DB.ExecuteNonQuery(command)

        Catch ex As Exception
            Throw ex
        Finally

        End Try
    End Sub
End Class
