Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports CharterDotCom.CommonEntities.Library
Imports CharterDotCom.CommonEntities.Library.Enumerations
Imports System.Net.Mail

<WebService(Namespace:="http://CharterDotComAdminServices/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class CMSService

    Inherits System.Web.Services.WebService

    Private contentProvider As IContentProvider

    Public Sub New()
        InitializeComponent()
        contentProvider = New CharterDotCom.CommonEntities.Library.ContentProvider.ProviderManager("CharterDotCom_CMSConnectionString")
    End Sub

#Region "Version Maintenance Functions"

    <WebMethod()> _
    Public Function GetContentID(ByVal UserControl As String, ByVal OwnerID As String, ByVal Ownertype As String) As Integer
        Try
            Dim lOwnerType As OwnerType = CharterDotCom.CommonEntities.Library.Common.ConvertToOwnerType(Ownertype)
            Dim intReturn As Integer
            intReturn = GetContentID_Business(UserControl, OwnerID, lOwnerType)
            If intReturn = Nothing Then
                Return Nothing
            Else
                Return intReturn
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Private Function GetContentID_Business(ByVal UserControl As String, ByVal OwnerID As String, ByVal Ownertype As OwnerType) As Integer
        Try
            Dim intLocaleID As Integer
            Dim intRegionID As Integer
            Dim intKMAId As Integer
            Dim intBillingStationID As Integer
            Dim strFTAID As String
            Dim intTemp As Integer

            intLocaleID = 1
            intRegionID = Nothing
            intKMAId = Nothing
            intBillingStationID = Nothing
            strFTAID = Nothing

            'Currently test parameters are used, needs to change to refer to actual parameters
            'Select Case Ownertype
            '    Case CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.Bundle, Ownertype.Feature, Ownertype.ServicePlan
            '        If Len(CStr(OwnerID)) > 1 Then
            '            OwnerID = Right(OwnerID, Len(OwnerID) - 1)
            '        End If
            'End Select

            Select Case Ownertype
                Case Ownertype.Bundle, Ownertype.ServicePlan
                    Dim objOwnerList As OWNERLIST
                    objOwnerList = GetEPCIDList(OwnerID)
                    If Not objOwnerList.m_OWNERDATA Is Nothing Then
                        For Each item As OWNERDATA In objOwnerList.m_OWNERDATA
                            OwnerID = item.ID
                            Exit For
                        Next
                    End If
            End Select

            'To check if only Billing code and FTA are available, then display content at the corresponding level
            If intRegionID = 0 And intKMAId = 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, Nothing, Nothing, intBillingStationID, strFTAID)
            Else
                'To check if none of the Organization details are available, then display National level content.
                If intRegionID = 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                    intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, Nothing, Nothing, Nothing, Nothing)
                Else
                    'To check if localized until region, then display the Region level content
                    If intRegionID <> 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                        intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, intRegionID, Nothing, Nothing, Nothing)
                    End If
                    'To check if localized until KMA, then display the KMA level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                        intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, intRegionID, intKMAId, Nothing, Nothing)
                    End If
                    'To check if localized until Billing station, then display the Billing station level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID = String.Empty Then
                        intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, intRegionID, intKMAId, intBillingStationID, Nothing)
                    End If
                    'To check if localized until FTA, then display the FTA level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                        intTemp = contentProvider.GetContentID(UserControl, intLocaleID, OwnerID, Ownertype, intRegionID, intKMAId, intBillingStationID, strFTAID)
                    End If

                End If
            End If

            Return intTemp


        Catch ex As Exception
            Throw ex
        Finally

        End Try

        Return Nothing

    End Function

    <WebMethod()> _
  Public Function GetVersions(ByVal ContentID As Integer) As ContentSchema.VERSION()

        'Function level variables which are destroyed in the Finally block
        Dim objRoot As New XmlRootAttribute("CONTENT")
        Dim versionlist As New List(Of ContentSchema.VERSION)
        'Get object to be returned to UI layer
        Dim objContentData As New ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer

        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetContentVersionsByContentID(ContentID) ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            If Not objContentData.m_version Is Nothing Then
                versionlist.AddRange(objContentData.m_version)
                Return versionlist.ToArray()
            Else
                Return Nothing
            End If


        Catch ex As Exception
            Throw ex
        Finally
            versionlist = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
  Public Sub UpdateVersionText(ByVal VersionId As Integer, ByVal VersionText As String, ByVal VersionName As String, ByVal SearchResultDisplayable As Boolean)
        Try

            contentProvider.UpdateVersion(VersionId, VersionText, VersionName, SearchResultDisplayable)
        Catch ex As Exception
            Throw ex
        Finally
            'objDA = Nothing
        End Try
    End Sub

    <WebMethod()> _
  Public Sub DeleteVersion(ByVal versionID As Integer)
        'Dim objDA As New ContentData
        Try

            contentProvider.DeleteVersion(versionID)
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Sub

    <WebMethod()> _
  Public Function AddVersion(ByVal UserControl As String, ByVal OwnerID As String, ByVal OwnertypeID As OwnerType, ByVal ContentID As Integer, ByVal VersionName As String, ByVal VersionText As String) As Boolean



        Dim lContentID As Integer
        Dim intLocaleID As Integer
        Dim intRegionID As Integer
        Dim intKMAId As Integer
        Dim intBillingStationID As Integer
        Dim strFTAID As String
        Dim strTemp As String = Nothing

        intLocaleID = 1
        intRegionID = Nothing
        intKMAId = Nothing
        intBillingStationID = Nothing
        strFTAID = Nothing

        'Currently test parameters are used, needs to change to refer to actual parameters

        Try
            If ContentID = 0 Then
                Select Case CType(OwnertypeID, OwnerType)
                    Case OwnerType.Bundle, OwnerType.ServicePlan


                        Dim objOwnerList As OWNERLIST
                        objOwnerList = GetEPCIDList(OwnerID)
                        If Not objOwnerList.m_OWNERDATA Is Nothing Then
                            Dim intcount As Integer = 0
                            For Each item As OWNERDATA In objOwnerList.m_OWNERDATA
                                If intcount = 0 Then

                                    'To check if only Billing code and FTA are available, then display content at the corresponding level
                                    'To check if none of the Organization details are available, then display National level content.
                                    If intRegionID = 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                        lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, item.ID, OwnertypeID, Nothing, Nothing, Nothing, Nothing, ContentID, 1, VersionText, VersionName, Now()))
                                    Else
                                        'To check if localized until region, then display the Region level content
                                        If intRegionID <> 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                            lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, Nothing, Nothing, Nothing, ContentID, 1, VersionText, VersionName, Now()))
                                        End If
                                        'To check if localized until KMA, then display the KMA level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                            lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, Nothing, Nothing, ContentID, 1, VersionText, VersionName, Now()))
                                        End If
                                        'To check if localized until Billing station, then display the Billing station level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID = String.Empty Then
                                            lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, intBillingStationID, Nothing, ContentID, 1, VersionText, VersionName, Now()))
                                        End If
                                        'To check if localized until FTA, then display the FTA level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                                            lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, intBillingStationID, strFTAID, ContentID, 1, VersionText, VersionName, Now()))
                                        End If
                                    End If

                                Else

                                    'To check if only Billing code and FTA are available, then display content at the corresponding level
                                    'To check if none of the Organization details are available, then display National level content.
                                    If intRegionID = 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                        contentProvider.AddContent(UserControl, intLocaleID, item.ID, OwnertypeID, Nothing, Nothing, Nothing, Nothing, lContentID)
                                    Else
                                        'To check if localized until region, then display the Region level content
                                        If intRegionID <> 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                            contentProvider.AddContent(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, Nothing, Nothing, Nothing, lContentID)
                                        End If
                                        'To check if localized until KMA, then display the KMA level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                                            contentProvider.AddContent(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, Nothing, Nothing, lContentID)
                                        End If
                                        'To check if localized until Billing station, then display the Billing station level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID = String.Empty Then
                                            contentProvider.AddContent(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, intBillingStationID, Nothing, lContentID)
                                        End If
                                        'To check if localized until FTA, then display the FTA level content
                                        If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                                            contentProvider.AddContent(UserControl, intLocaleID, item.ID, OwnertypeID, intRegionID, intKMAId, intBillingStationID, strFTAID, lContentID)
                                        End If
                                    End If

                                End If
                                intcount = 1
                            Next
                        End If


                    Case Else
                        Dim itemid As String = Nothing
                        Select Case CType(OwnertypeID, OwnerType)
                            Case OwnerType.Feature
                                If Len(CStr(OwnerID)) > 1 Then
                                    itemid = Right(OwnerID, Len(OwnerID) - 1)
                                End If
                            Case Else
                                itemid = OwnerID
                        End Select
                        lContentID = CInt(contentProvider.AddContentAndContentVersion(UserControl, intLocaleID, itemid, OwnertypeID, Nothing, Nothing, Nothing, Nothing, ContentID, 1, VersionText, VersionName, Now()))

                End Select


            Else
                contentProvider.AddVersion(ContentID, 1, Now(), VersionText, VersionName)
            End If

        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Function

    Private Function GetVersionByVersionID(ByVal VersionID As Integer) As String
        'Function level variables which are destroyed in the Finally block
        Dim objRoot As New XmlRootAttribute("CONTENT")
        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        Dim context As System.Web.HttpContext = System.Web.HttpContext.Current

        Try
            'Currently test parameters are used, needs to change to refer to actual parameters
            ''If System.Web.HttpContext.Current.Cache("Content" & VersionID) IsNot Nothing Then
            ''  Return CStr(context.Cache("Content" & VersionID))
            ''End If
            Dim strtemp As String = contentProvider.GetContentVersionRecord(VersionID) ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            If Not objContentData.m_version Is Nothing AndAlso objContentData.m_version.Length > 0 Then
                ''Dim expiredate As DateTime
                ''If Now() > Date.Today.AddHours(3) Then
                ''  expiredate = Date.Today.AddDays(1).AddHours(3)
                ''Else
                ''  expiredate = Date.Today.AddHours(3)
                ''End If
                ''If objContentData.m_version(0).ContentText Is Nothing Then
                ''  context.Cache.Add("Content" & VersionID, "", Nothing, expiredate, System.Web.Caching.Cache.NoSlidingExpiration, Web.Caching.CacheItemPriority.Default, Nothing)
                ''Else
                ''  context.Cache.Add("Content" & VersionID, objContentData.m_version(0).ContentText, Nothing, expiredate, System.Web.Caching.Cache.NoSlidingExpiration, Web.Caching.CacheItemPriority.Default, Nothing)
                ''End If
                Return objContentData.m_version(0).ContentText
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        Finally
            objRoot = Nothing
        End Try
    End Function

#End Region

#Region "Organization structure Functions"
    'This sub-routine is called on changing Organization level 
    <WebMethod()> _
  Public Function GetOrganization(ByVal TrackingId As String) As String
        Try

            Dim strTemp As String = contentProvider.GetOrgStructure()
            If strTemp = "" Then
                Return Nothing
            Else
                Return strTemp
            End If
        Catch ex As Exception

            Throw ex
        End Try

    End Function

    <WebMethod()> _
  Public Function GetOwnerTypes() As OWNERDATA()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData

        Dim objRoot As New XmlRootAttribute("OWNERLIST")
        Dim ownerlist As Dictionary(Of Integer, String)
        'Get object to be returned to UI layer
        Dim objOwnerList As OWNERLIST
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer

        Try

            Dim strtemp As String = contentProvider.GetOwnerTypeList

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(OWNERLIST), objRoot)

            'De-serialize the XML data onto an instance
            objOwnerList = CType(objXMLSerializer_Content.Deserialize(objReader), OWNERLIST)

            'return the object to UI layer
            If Not objOwnerList.m_OWNERDATA Is Nothing Then
                Return objOwnerList.m_OWNERDATA
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Throw ex
        Finally
            ownerlist = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
  Public Function GetOwners(ByVal OwnerTypeID As Integer) As OWNERDATA()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData

        Dim objRoot As New XmlRootAttribute("OWNERLIST")
        Dim ownerlist As Dictionary(Of Integer, String)
        'Get object to be returned to UI layer
        Dim objOwnerList As OWNERLIST
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer

        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetOwnerIDList(OwnerTypeID)

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(OWNERLIST), objRoot)

            'De-serialize the XML data onto an instance
            objOwnerList = CType(objXMLSerializer_Content.Deserialize(objReader), OWNERLIST)

            'return the object to UI layer
            If Not objOwnerList.m_OWNERDATA Is Nothing Then
                Return objOwnerList.m_OWNERDATA
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Throw ex
        Finally
            ownerlist = Nothing
            objRoot = Nothing
        End Try
    End Function

    Private Function GetEPCIDList(ByVal PlayBookID As String) As OWNERLIST
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData
        Dim objRoot As New XmlRootAttribute("OWNERLIST")
        Dim ownerlist As Dictionary(Of Integer, String)
        'Get object to be returned to UI layer
        Dim objOwnerList As OWNERLIST
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer

        Try
            Dim strtemp As String
            'Currently test parameters are used, needs to change to refer to actual parameters

            strtemp = contentProvider.GetEPCIDList(PlayBookID)

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(OWNERLIST), objRoot)

            'De-serialize the XML data onto an instance
            objOwnerList = CType(objXMLSerializer_Content.Deserialize(objReader), OWNERLIST)

            'return the object to UI layer
            Return objOwnerList

        Catch ex As Exception
            Throw ex
        Finally
            ownerlist = Nothing
            objRoot = Nothing
        End Try
    End Function
#End Region

#Region "Schedule Maintenance Functions"

    <WebMethod()> _
  Public Function GetSchedules(ByVal VersionID As Integer) As ContentSchema.SCHEDULE()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData


        Dim objRoot As New XmlRootAttribute("CONTENT")
        Dim schedulelist As New List(Of ContentSchema.SCHEDULE)
        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer

        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetScheduleRecordsByVersionID(VersionID) ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            'Return objContentData._schedule
            If Not objContentData.m_version Is Nothing Then
                If Not objContentData.m_version(0).m_SCHEDULE Is Nothing Then
                    schedulelist.AddRange(objContentData.m_version(0).m_SCHEDULE)
                    Return schedulelist.ToArray()
                Else
                    Return Nothing
                End If
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Throw ex
        Finally
            schedulelist = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
  Public Sub UpdateSchedule(ByVal ScheduleID As Integer, ByVal Startdate As Date, ByVal EndDate As Date)
        Try

            contentProvider.UpdateSchedule(ScheduleID, Startdate, EndDate)
        Catch ex As Exception

            Throw ex
        Finally
            'objDA = Nothing
        End Try
    End Sub

    <WebMethod()> _
  Public Sub AddSchedule(ByVal VersionID As Integer, ByVal Startdate As Date, ByVal EndDate As Date)
        'Dim objDA As New ContentData
        Try

            contentProvider.AddSchedule(VersionID, Startdate, EndDate, 1, Now())
        Catch ex As Exception

            Throw ex
        Finally
            'objDA = Nothing
        End Try
    End Sub

    <WebMethod()> _
  Public Sub DeleteSchedule(ByVal ScheduleID As Integer)
        'Dim objDA As New ContentData
        Try

            contentProvider.DeleteSchedule(ScheduleID)
        Catch ex As Exception

            Throw ex
        Finally
        End Try
    End Sub

#End Region

#Region "BinaryFile Maintenance Functions"

    <WebMethod()> _
    Public Function GetBinaryFiles(ByVal BinaryFileType As String) As String()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData

        Dim objRoot As New XmlRootAttribute("CONTENT")
        Dim binaryFileList As New List(Of String)
        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        contentProvider = New ContentProvider.ProviderManager()
        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetBinaryFiles(BinaryFileType) ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            If Not objContentData.m_BinaryFile Is Nothing Then
                For Each i As ContentSchema.BINARYFILE In objContentData.m_BinaryFile
                    binaryFileList.Add(i.BinaryFileName)
                Next
                Return binaryFileList.ToArray()
            End If
            Return Nothing
        Catch ex As Exception

            Throw ex
        Finally
            binaryFileList = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Function AddBinaryFile(ByVal Name As String, ByVal BinaryFileData() As Byte, ByVal BinaryFileExtension As String, ByVal BinaryFileType As String) As Boolean
        Try
            contentProvider = New ContentProvider.ProviderManager()
            contentProvider.AddBinaryFile(1, Now(), BinaryFileData, Nothing, Name, Name, BinaryFileExtension, BinaryFileType)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
    Public Function GetBinaryFile(ByVal BinaryFileName As String, ByVal BinaryFileExtension As String) As ContentSchema.BINARYFILE

        '
        'Function level variables which are destroyed in the Finally block
        Dim objRoot As New XmlRootAttribute("CONTENT")

        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        contentProvider = New ContentProvider.ProviderManager()
        Try
            'Currently test parameters are used, needs to change to refer to actual parameters
            Dim strtemp As String = contentProvider.GetBinaryFileByBinaryFileName(BinaryFileName, BinaryFileExtension)

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)
            If Not objContentData.m_BinaryFile Is Nothing Then
                'return the object to UI layer
                Return objContentData.m_BinaryFile(0)
            Else
                Return Nothing
            End If
        Catch ex As Exception

            Throw ex
        Finally
            contentProvider = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Function UpdateBinaryFile(ByVal lBinaryFile As ContentSchema.BINARYFILE) As Boolean
        contentProvider = New ContentProvider.ProviderManager()

        Try
            contentProvider.UpdateBinaryFile(lBinaryFile.BinaryFileID, lBinaryFile.m_CreateByUserID, lBinaryFile.m_CreateDate, lBinaryFile.m_BinaryFileData, lBinaryFile.m_Thumbnail, lBinaryFile.m_Caption, lBinaryFile.m_ModifyByUserID, lBinaryFile.m_ModifyDate, lBinaryFile.m_ApprovedByUserID, lBinaryFile.m_ApprovedDate, lBinaryFile.BinaryFileExtension, lBinaryFile.BinaryFileType)

            Return True

        Catch ex As Exception
            Return False
        Finally
            contentProvider = Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Sub DeleteBinaryFile(ByVal BinaryFileName As String, ByVal BinaryFileExtension As String)
        Try
            contentProvider.DeleteBinaryFileByBinaryFileName(BinaryFileName, BinaryFileExtension)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#End Region

#Region "Image Maintenance Functions"

    <WebMethod()> _
  Public Function GetImagesByKeyword(ByVal Keyword As String) As String()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData

        '#####################################################
        'THIS IS USED FOR THE SEARCH CAPABILITY BASED ON 
        'KEYWORDS/ID. -NAQIB 9/23/08  FOR ISSUE ID: 2362   
        '#####################################################

        Dim objRoot As New XmlRootAttribute("CONTENT")
        Dim imagelist As New List(Of String)
        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        contentProvider = New ContentProvider.ProviderManager()
        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetImagesByKeyword(Keyword) ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            If Not objContentData.m_Image Is Nothing Then
                For Each i As ContentSchema.IMAGE In objContentData.m_Image
                    imagelist.Add(i.ImageName)
                Next
                Return imagelist.ToArray()
            End If
            Return Nothing
        Catch ex As Exception

            Throw ex
        Finally
            imagelist = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
  Public Function GetImages() As String()
        'Function level variables which are destroyed in the Finally block
        'Dim objDA As New ContentData

        Dim objRoot As New XmlRootAttribute("CONTENT")
        Dim imagelist As New List(Of String)
        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        contentProvider = New ContentProvider.ProviderManager()
        Try
            'Currently test parameters are used, needs to change to refer to actual parameters

            Dim strtemp As String = contentProvider.GetAllImageIDList() ' GetContentNormalized("Test2", 0, 0, 12, 0) 'ContentData.GetContentNormalized("WEBCONTROL_00", EntityType, Right(ItemID, Len(ItemID) - 1), Session("FTAID"), Session("LocaleID"))

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)

            'return the object to UI layer
            If Not objContentData.m_Image Is Nothing Then
                For Each i As ContentSchema.IMAGE In objContentData.m_Image
                    imagelist.Add(i.ImageName)
                Next
                Return imagelist.ToArray()
            End If
            Return Nothing
        Catch ex As Exception

            Throw ex
        Finally
            imagelist = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Function AddImage(ByVal Name As String, ByVal ImageData() As Byte) As Boolean
        Try
            contentProvider = New ContentProvider.ProviderManager()
            contentProvider.AddImage(1, Now(), ImageData, Nothing, Name, Name)
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    <WebMethod()> _
   Public Function GetImage(ByVal ImageName As String) As ContentSchema.IMAGE

        '
        'Function level variables which are destroyed in the Finally block
        Dim objRoot As New XmlRootAttribute("CONTENT")

        'Get object to be returned to UI layer
        Dim objContentData As ContentSchema.CONTENT
        'Get serializer for De-serializing content xml to Content object
        Dim objXMLSerializer_Content As XmlSerializer
        contentProvider = New ContentProvider.ProviderManager()
        Try
            'Currently test parameters are used, needs to change to refer to actual parameters
            Dim strtemp As String = contentProvider.GetImageDataRecordByImageName(ImageName)

            'Load the string reader with the XML string returned by Data Access layer
            Dim objReader As New System.IO.StringReader(strtemp)

            'Instantiate XML serializer of the corresponding content object type
            objXMLSerializer_Content = New XmlSerializer(GetType(ContentSchema.CONTENT), objRoot)

            'De-serialize the XML data onto an instance
            objContentData = CType(objXMLSerializer_Content.Deserialize(objReader), ContentSchema.CONTENT)
            If Not objContentData.m_Image Is Nothing Then
                'return the object to UI layer
                Return objContentData.m_Image(0)
            Else
                Return Nothing
            End If
        Catch ex As Exception

            Throw ex
        Finally
            contentProvider = Nothing
            objRoot = Nothing
        End Try
    End Function

    <WebMethod()> _
   Public Function UpdateImage(ByVal lImage As ContentSchema.IMAGE) As Boolean
        contentProvider = New ContentProvider.ProviderManager()

        Try
            contentProvider.UpdateImage(lImage.ImageID, lImage.m_CreateBy_UserID, lImage.m_CreateDate, lImage.m_ImageData, lImage.m_Thumbnail, lImage.m_Caption, lImage.m_ModifyBy_UserID, lImage.m_ModifyDate, lImage.m_ApprovedBy_UserID, lImage.m_ApprovedDate)

            Return True

        Catch ex As Exception
            Return False
        Finally
            contentProvider = Nothing
        End Try
    End Function

    <WebMethod()> _
    Public Sub DeleteImage(ByVal Name As String)
        Try
            contentProvider.DeleteImageByImageName(Name, Nothing)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

#End Region

#Region "Product catalog Content display Functions"

    <WebMethod()> _
  Public Function GetContent(ByVal UserControl As String, ByVal OwnerID As String, ByVal Ownertype As String) As String


        ' Function level variables which are destroyed in the Finally block
        Dim objRoot As New XmlRootAttribute("CONTENT")
        'Get object to be returned to UI layer
        Dim lOwnerType As OwnerType
        Try
            Dim intLocaleID As Integer
            Dim intRegionID As Integer
            Dim intKMAId As Integer
            Dim intBillingStationID As Integer
            Dim strFTAID As String
            Dim strTemp As String
            Dim lContentID As Integer
            Dim lVersionID As Integer
            Dim charContentSeparator() As Char = {"*"c, "@"c, "*"c}

            Dim VarList As New Dictionary(Of String, Object)
            VarList.Add("account", New BLAccount())
            VarList.Add("gwyShoppingCart", Nothing)
            VarList.Add("LocaleID", 1)
            VarList.Add("CookieID", Nothing)
            VarList.Add("addresses", Nothing)
            VarList.Add("serviceabilityResults", Nothing)
            VarList.Add("entityid", Nothing)
            VarList.Add("houseref", Nothing)
            VarList.Add("_sysPrin", Nothing)

            intLocaleID = 1
            intRegionID = Nothing
            intKMAId = Nothing
            intBillingStationID = Nothing
            strFTAID = Nothing

            lOwnerType = CharterDotCom.CommonEntities.Library.Common.ConvertToOwnerType(Ownertype)

            Select Case lOwnerType
                Case CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.Bundle, CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.Feature, CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.ServicePlan
                    If Len(CStr(OwnerID)) > 1 Then
                        OwnerID = Right(OwnerID, Len(OwnerID) - 1)
                    End If
            End Select

            Select Case lOwnerType
                Case CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.Bundle, CharterDotCom.CommonEntities.Library.Enumerations.OwnerType.ServicePlan
                    Dim objOwnerList As OWNERLIST
                    objOwnerList = GetEPCIDList(OwnerID)
                    If Not objOwnerList.m_OWNERDATA Is Nothing Then
                        For Each item As OWNERDATA In objOwnerList.m_OWNERDATA
                            OwnerID = item.ID
                            Exit For
                        Next
                    End If
            End Select

            'To check if only Billing code and FTA are available, then display content at the corresponding level
            If intRegionID = 0 And intKMAId = 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, Nothing, Nothing, intBillingStationID, strFTAID, lContentID, lVersionID)
            Else
                'To check if none of the Organization details are available, then display National level content.
                If intRegionID = 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                    strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, Nothing, Nothing, Nothing, Nothing, lContentID, lVersionID)
                Else
                    'To check if localized until region, then display the Region level content
                    If intRegionID <> 0 And intKMAId = 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                        strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, intRegionID, Nothing, Nothing, Nothing, lContentID, lVersionID)
                    End If
                    'To check if localized until KMA, then display the KMA level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID = 0 And strFTAID = String.Empty Then
                        strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, intRegionID, intKMAId, Nothing, Nothing, lContentID, lVersionID)
                    End If
                    'To check if localized until Billing station, then display the Billing station level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID = String.Empty Then
                        strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, intRegionID, intKMAId, intBillingStationID, Nothing, lContentID, lVersionID)
                    End If
                    'To check if localized until FTA, then display the FTA level content
                    If intRegionID <> 0 And intKMAId <> 0 And intBillingStationID <> 0 And strFTAID <> String.Empty Then
                        strTemp = contentProvider.GetContentNormalized(UserControl, intLocaleID, OwnerID, lOwnerType, intRegionID, intKMAId, intBillingStationID, strFTAID, lContentID, lVersionID)
                    End If

                End If
            End If

            ' return the object to UI layer
            strTemp = Nothing
            If Not strTemp Is Nothing Then
                Return strTemp
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Throw ex
        Finally
            objRoot = Nothing
        End Try

    End Function

#End Region

#Region "Content WorkFlow"

    <WebMethod()> _
    Public Sub EditContent(ByVal versionID As Integer, ByVal content As String, ByVal VERSIONNAME As String, ByVal SearchResultDisplayable As Boolean, ByVal TemplateID As Integer, ByVal UserName As String, ByVal TemplateName As String)
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("ContentEdit.INSERT_CONTENT_VERSION")
            With DB
                .AddInParameter(CMD, "@VersionID", DbType.Int32, versionID)
                .AddInParameter(CMD, "@CONTENT_VERSION_CreateBy_UserID", DbType.String, UserName)
                .AddInParameter(CMD, "@CONTENT_VERSION_CreateDate", DbType.DateTime, Now())
                .AddInParameter(CMD, "@ContentText", DbType.String, content)
                .AddInParameter(CMD, "@VersionName", DbType.String, VERSIONNAME)
                .AddInParameter(CMD, "@SearchResultDisplayable", DbType.Boolean, SearchResultDisplayable)
                .AddInParameter(CMD, "@TemplateID", DbType.Int32, TemplateID)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

        Try
            Dim strTo As String = GetEmailAddress(TemplateID, 2)
            If strTo <> String.Empty Then
                Dim strBOdy As String = ConfigurationManager.AppSettings("EMailApproval_Body")
                strBOdy = strBOdy.Replace("{tempname}", "<B>" & TemplateName & "</B>")
                strBOdy = strBOdy.Replace("{br}", "<BR>")
                strBOdy = strBOdy.Replace("{link}", "<A Href='http://" & ConfigurationManager.AppSettings("CMSWebServer") & "/Admin/CharterContentManagement/Approve.aspx'>this link</A>")
                SendEMail(ConfigurationManager.AppSettings("EMail_From"), strTo, ConfigurationManager.AppSettings("EMailApproval_Subject"), strBOdy, True)
            End If
        Catch ex As Exception

        End Try
    End Sub

    <WebMethod()> _
  Public Function GetUserControlList(ByVal TemplateID As Integer) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT UserControlName FROM [content].UserControl WHERE (TemplateID = " & TemplateID & ") order by UserControlName")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
  Public Function GetTemplate(ByVal TemplateID As String) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("CONTENT.GET_TEMPLATEID")
            DB.AddInParameter(CMD, "@TemplateID", DbType.String, TemplateID)
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
   Public Sub DeleteEdits(ByVal ID As Integer)
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("Delete FROM ContentEdit.Content_Version WHERE [ID] = " & ID)
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    <WebMethod()> _
  Public Function GetPendingVersions(ByVal TemplateID As Integer) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT VersionID, ContentID, ID, CONTENT_NAME,  SEARCH_RESULT_DISPLAYABLE , content.fnGetUserName(CONTENT_VERSION_CreateBy_UserID) as [CreatedBY],CONTENT_VERSION_CreateDate as [CreatedDate] FROM ContentEdit.Content_Version WHERE (TemplateID = " & TemplateID & ")")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
  Public Function GetApprovedVersions(ByVal TemplateID As Integer) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT VersionID, CONTENT_NAME , content.fnGetUserName(CONTENT_VERSION_ModifyBy_UserID) AS [ApprovedBy], CONTENT_VERSION_ModifyDate as [ApprovedDate] FROM [content].Content_Version WHERE (TemplateID = " & TemplateID & ") AND (Approved = 1)")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
    Public Function ValidateUser(ByVal username As String, ByVal RoleID As Integer) As String
        Dim ds As Data.DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Dim TemplateIds As String = Nothing
        Dim Rolename As String

        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("content.CHECK_CONTENTUSERS")
            DB.AddInParameter(CMD, "@UserName", Data.DbType.String, username.Trim().ToLower())
            DB.AddInParameter(CMD, "@Password", Data.DbType.String, "")
            DB.AddInParameter(CMD, "@RoleID", Data.DbType.Int32, RoleID)
            ds = DB.ExecuteDataSet(CMD)
            If Not ds Is Nothing AndAlso ds.Tables.Count = 1 Then
                For Each dr As Data.DataRow In ds.Tables(0).Rows
                    Rolename = dr.Item(0)
                    TemplateIds = TemplateIds & dr.Item(1) & ","
                Next
                If TemplateIds.Length > 0 Then
                    TemplateIds = Left(TemplateIds, Len(TemplateIds) - 1)
                End If
            End If
            Return TemplateIds
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing

            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
  Public Function PublishContent(ByVal TemplateID As Integer, ByVal username As String, ByVal templatename As String) As Boolean
        Dim retCode As String
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            UpdateApprovedVersions(TemplateID, username)

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("content.PublishContent")
            DB.AddInParameter(CMD, "@TemplateID", DbType.Int32, TemplateID)
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

        Try
            Dim obj As Object = GetLiveServer()
            If Not obj Is Nothing Then
                Dim resetCache As New CharterDotComServices.CharterDotComServices
                Dim ls As List(Of String) = obj
                For Each str As String In ls
                    resetCache.Url = "http://" & str & "/_SITESETTINGS_WS/CharterDotComServices.asmx"
                    resetCache.ResetCache(TemplateID, ConfigurationManager.AppSettings("SiteSettings"))

                Next
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        End Try

    End Function

    <WebMethod()> _
    Public Function GetEditContent(ByVal ID As Integer) As String
        Dim retCode As String
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT ContentText FROM ContentEdit.Content_Version WHERE ID = " & ID & "")
            retCode = DB.ExecuteScalar(CMD)
            Return retCode
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
    Public Function ApproveContent(ByVal TemplateID As Integer, ByVal username As String, ByVal TemplateName As String) As Boolean
        Dim retCode As String
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            UpdatePendingVersions(TemplateID, username)

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("content.ApproveVersions")
            DB.AddInParameter(CMD, "@TemplateID", DbType.Int32, TemplateID)
            retCode = DB.ExecuteNonQuery(CMD)
            Return True
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try


        '################################################################################################
        '#############  M O D I F I C A T I O N   4/24/07
        '################################################################################################
        'THIS WAS COMMENTED BY ROD COPELAND.  THE SCRATCH SITE WAS ABANDONED DUE TO SECURITY ISSUES. 
        'ACTUALLY, IT WAS NEVER SUPPOSED TO BE TURNED ON IN PRODUCTION BECAUSE OF SECURITY REASONS.  
        'SO THE NEED OF THE RESETTING OF CACHE ON THE SCRATCH SITE IS UNNEEDED.
        '################################################################################################
        'Try
        '    Dim obj As Object = GetScratchServer()
        '    If Not obj Is Nothing Then
        '        Dim resetCache As New CharterDotComServices.CharterDotComServices
        '        Dim ls As List(Of String) = obj
        '        For Each str As String In ls
        '            resetCache.Url = "http://" & str & "/CharterDotComServices.asmx"
        '            resetCache.ResetCache(TemplateID, ConfigurationManager.AppSettings("SiteSettings"))
        '        Next

        '        Try
        '            Dim strTo As String = GetEmailAddress(TemplateID, 3)
        '            If strTo <> String.Empty Then
        '                Dim strBOdy As String = ConfigurationManager.AppSettings("EMailPublish_Body")
        '                strBOdy = strBOdy.Replace("{tempname}", "<B>" & TemplateName & "</B>")
        '                strBOdy = strBOdy.Replace("{br}", "<BR>")
        '                strBOdy = strBOdy.Replace("{link}", "<A Href='http://" & ConfigurationManager.AppSettings("CMSWebServer") & "/Admin/CharterContentManagement/Publish.aspx'>this link</A>")
        '                SendEMail(ConfigurationManager.AppSettings("EMail_From"), strTo, ConfigurationManager.AppSettings("EMailPublish_Subject"), strBOdy, True)
        '            End If
        '        Catch ex As Exception

        '        End Try
        '        Return True
        '    Else
        '        Return False
        '    End If
        'Catch ex As Exception
        '    'NO REASON TO CATCH STUFF FOR SCRATCH SITE.
        'End Try
    End Function

    <WebMethod()> _
    Public Function GetApprovedContent(ByVal versionID As Integer) As String
        Dim retCode As String
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT ContentText FROM Content.Content_Version WHERE VersionID = " & versionID & "")
            retCode = DB.ExecuteScalar(CMD)
            Return retCode
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    Public Function GetScratchServer() As List(Of String)
        Dim lsWebScratchServer As New List(Of String)

        Dim scratchwebserverkey As String = ConfigurationManager.AppSettings(ApplicationKey.SCRATCHWEBSERVER)

        Dim ds As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("Charter.Website.WS.CommerceEngineConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT ServerLocation,ServerName FROM ADMIN.SERVERCONFIG WHERE ServerLocation IN ('" & scratchwebserverkey & "')")
            ds = DB.ExecuteDataSet(CMD)
            If Not ds Is Nothing AndAlso ds.Tables.Count = 1 Then
                For Each dr As DataRow In ds.Tables(0).Rows
                    lsWebScratchServer.Add(dr.Item(1))
                Next
                Return lsWebScratchServer
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            lsWebScratchServer = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
        Return Nothing
    End Function

    Public Function GetLiveServer() As List(Of String)
        Dim lsWebServer As New List(Of String)

        Dim webserverkey As String = ConfigurationManager.AppSettings(ApplicationKey.WebServers)

        Dim ds As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("Charter.Website.WS.CommerceEngineConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT ServerLocation,ServerName FROM ADMIN.SERVERCONFIG WHERE ServerLocation IN ('" & webserverkey & "')")
            ds = DB.ExecuteDataSet(CMD)
            If Not ds Is Nothing AndAlso ds.Tables.Count = 1 Then
                For Each dr As DataRow In ds.Tables(0).Rows
                    lsWebServer.Add(dr.Item(1))
                Next
                Return lsWebServer
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            lsWebServer = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If

        End Try
        Return Nothing
    End Function

    Private Sub UpdatePendingVersions(ByVal TemplateID As Integer, ByVal UserName As String)
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            Dim strSQL As String
            strSQL = "UPDATE ContentEdit.Content_Version SET Modify_By_UserID = content.fnGetUserID('" & UserName & "') , Modify_By_Date = GetDate() WHERE (TemplateID = " & TemplateID & ")"
            CMD = DB.GetSqlStringCommand(strSQL)
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Private Sub UpdateApprovedVersions(ByVal TemplateID As Integer, ByVal UserName As String)
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            Dim strsql As String
            strsql = "UPDATE [content].Content_Version SET CONTENT_VERSION_ApprovedBy_UserID =  content.fnGetUserID('" & UserName & "') ,CONTENT_VERSION_ApprovedDate = GetDate()  WHERE (TemplateID = " & TemplateID & ") AND (Approved = 1)"
            CMD = DB.GetSqlStringCommand(strsql)
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Private 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 = ConfigurationManager.AppSettings("SMTPSERVER")
            Dim client As New SmtpClient(from_Config)
            Dim from As String = FromMailID
            Dim strSPlit() As String
            strSPlit = ToMailID.Split(";")
            For i As Integer = 0 To strSPlit.Length - 1
                Dim mailMsg As New MailMessage(from, strSPlit(i))
                With mailMsg
                    .Subject = Subject
                    .Body = Body
                End With
                mailMsg.IsBodyHtml = isHTML
                client.Send(mailMsg)
            Next

            client = Nothing
        Catch ex As Exception

        End Try
    End Sub

    <WebMethod()> _
   Public Function VerifyNewUser(ByVal username As String, ByVal Password As String) As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand

        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("content.VERIFY_NEWUSER")
            DB.AddInParameter(CMD, "@UserName", Data.DbType.String, username.Trim().ToLower())
            DB.AddInParameter(CMD, "@Password", Data.DbType.String, Password)
            Return DB.ExecuteScalar(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
    Public Function DisableUser(ByVal UserID As Integer) As Boolean
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            Dim strsql As String
            strsql = "UPDATE [content].Users SET IsDisabled = 1 WHERE UserID =  " & UserID
            CMD = DB.GetSqlStringCommand(strsql)
            retCode = DB.ExecuteNonQuery(CMD)
            Return True
        Catch ex As Exception
            Return False
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

    <WebMethod()> _
    Public Function EnableUser(ByVal UserID As Integer) As Boolean
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            Dim strsql As String
            strsql = "UPDATE [content].Users SET IsDisabled = 0 WHERE UserID =  " & UserID
            CMD = DB.GetSqlStringCommand(strsql)
            retCode = DB.ExecuteNonQuery(CMD)
            Return True
        Catch ex As Exception
            Return False
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function

#End Region

    Private Sub InitializeComponent()

    End Sub

    <WebMethod()> _
        Public Function Authenticate(ByVal UserName As String, ByVal Password As String) As UserInfo

        Dim ds As Data.DataSet

        Dim DB As Database

        Dim CMD As Data.Common.DbCommand = Nothing

        'Dim roleNames As String

        Dim _roleList As List(Of String) = New List(Of String)

        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            Dim str = DB.ConnectionStringWithoutCredentials

            CMD = DB.GetStoredProcCommand("dbo.GetUserRoles")

            DB.AddInParameter(CMD, "@UserName", Data.DbType.String, UserName)

            DB.AddInParameter(CMD, "@Password", Data.DbType.String, Password)

            DB.AddOutParameter(CMD, "@id", DbType.Int32, Int32.MaxValue)

            ds = DB.ExecuteDataSet(CMD)

            'retrieve the user's userid
            Dim userid As Int32 = CMD.Parameters("@id").Value

            Dim info As UserInfo = New UserInfo
            info.UserID = userid

            If Not ds Is Nothing AndAlso ds.Tables.Count = 1 Then

                For Each dr As Data.DataRow In ds.Tables(0).Rows

                    'roleNames = roleNames & dr.Item(0) & ";"
                    _roleList.Add(CType(dr.Item(0), String))

                Next

            End If

            info.USerRoles = _roleList.ToArray()
            Return info

        Catch ex As Exception

            Throw ex

        Finally

            If Not CMD Is Nothing Then

                CMD.Dispose()

            End If

        End Try

    End Function

#Region "Content Administration"

    <WebMethod()> _
    Public Function createNewUser(ByVal username As String, ByVal Password As String, ByVal firstname As String, ByVal lastname As String, ByVal EmailAddress As String, ByVal CreatedBy As String) As Boolean
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[CREATE_NEWUSER]")
            With DB
                .AddInParameter(CMD, "@USERNAME", DbType.String, username)
                .AddInParameter(CMD, "@PASSWORD", DbType.String, Password)
                .AddInParameter(CMD, "@FIRSTNAME", DbType.String, firstname)
                .AddInParameter(CMD, "@LASTNAME", DbType.String, lastname)
                .AddInParameter(CMD, "@EMAIL", DbType.String, EmailAddress)
                .AddInParameter(CMD, "@CREATEDDATE", DbType.DateTime, Now())
                .AddInParameter(CMD, "@CREATEDBY", DbType.String, CreatedBy)
            End With
            retCode = DB.ExecuteNonQuery(CMD)

            Try
                If EmailAddress <> String.Empty Then
                    Dim strBOdy As String = ConfigurationManager.AppSettings("NewAccount_Body")
                    strBOdy = strBOdy.Replace("{username}", "<B>" & username & "</B>")
                    strBOdy = strBOdy.Replace("{password}", "<B>" & Password & "</B>")
                    strBOdy = strBOdy.Replace("{br}", "<BR>")
                    strBOdy = strBOdy.Replace("{server}", "http://" & ConfigurationManager.AppSettings("CMSWebServer"))
                    SendEMail(ConfigurationManager.AppSettings("EMail_From"), EmailAddress, ConfigurationManager.AppSettings("NewAccount_Subject"), strBOdy, True)
                End If
            Catch ex As Exception

            End Try

            Return True
        Catch ex As Exception
            Return False
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
    Public Function saveSelectedTemplate(ByVal userid As Integer, ByVal RoleID As Integer, ByVal selectedTemplates() As String) As Boolean

        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[DELETE_PREVIOUSTEMPLATE]")
            With DB
                .AddInParameter(CMD, "@USERID", DbType.Int32, userid)
                .AddInParameter(CMD, "@RoleID", DbType.Int32, RoleID)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Return False
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

        Dim CMDUpdate As Data.Common.DbCommand
        Dim intLoop As Integer
        Try
            CMDUpdate = DB.GetStoredProcCommand("[content].[UPDATE_TEMPLATE]")
            With DB
                .AddInParameter(CMDUpdate, "@USERID", DbType.Int32)
                .AddInParameter(CMDUpdate, "@RoleID", DbType.Int32)
                .AddInParameter(CMDUpdate, "@TEMPLATEID", DbType.Int32)
            End With

            For intLoop = 0 To selectedTemplates.Length - 1
                CMDUpdate.Parameters(0).Value = userid
                CMDUpdate.Parameters(1).Value = RoleID
                CMDUpdate.Parameters(2).Value = selectedTemplates(intLoop)
                DB.ExecuteNonQuery(CMDUpdate)
            Next
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

    <WebMethod()> _
    Public Function GetUsers() As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[GET_USER]")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
    Public Function GetTemplates() As DataSet

        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[GET_TEMPLATE]")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
    Public Function GetTemplatesForUser(ByVal userid As Integer, ByVal RoleID As Integer) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[GET_TEMPLATE_FOR_USER]")
            DB.AddInParameter(CMD, "@userID", DbType.Int32, userid)
            DB.AddInParameter(CMD, "@RoleID", DbType.Int32, RoleID)
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
    Public Function GetUserID(ByVal username As String) As Integer
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[GET_USERID]")
            DB.AddInParameter(CMD, "@USERNAME", DbType.String, username)
            retCode = DB.ExecuteScalar(CMD)
            Return retCode

        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
    Public Function GetContentAuthoringRoles() As DataSet

        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT RoleID, RoleDescription FROM [content].[Role] WHERE RoleID < 4 and roleid > 0 ")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    <WebMethod()> _
Public Function GetRoles() As DataSet

        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetSqlStringCommand("SELECT RoleID, RoleDescription FROM [content].[Role] WHERE RoleID <> 0 ")
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Function

    Private Function GetEmailAddress(ByVal TemplateID As Integer, ByVal RoleID As Integer) As String
        Dim retCode As DataSet
        Dim emails As String = Nothing
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[GET_Emails_FOR_Template_ByRole]")
            DB.AddInParameter(CMD, "@TemplateID", DbType.Int32, TemplateID)
            DB.AddInParameter(CMD, "@RoleID", DbType.Int32, RoleID)
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                For Each dr As Data.DataRow In retCode.Tables(0).Rows
                    If dr.Item(0) <> "" Then
                        emails = emails & dr.Item(0) & ";"
                    End If
                Next
                If emails.Length > 0 Then
                    emails = Left(emails, emails.Length - 1)
                End If
            End If
            Return emails
        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function


    <WebMethod()> _
Public Function GetUserName(ByVal userid As Integer) As DataSet
        Dim retCode As DataSet
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try
            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[Get_UserName]")
            DB.AddInParameter(CMD, "@USERID", DbType.Int32, userid)
            retCode = DB.ExecuteDataSet(CMD)
            If Not retCode Is Nothing AndAlso retCode.Tables.Count > 0 Then
                Return retCode
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function


    <WebMethod()> _
     Public Function UpdatePassword(ByVal userid As Integer, ByVal password As String) As Boolean
        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("CharterDotCom_CMSConnectionString")
            CMD = DB.GetStoredProcCommand("[content].[UPDATE_USER_PASSWORD]")
            With DB
                .AddInParameter(CMD, "@USERID", DbType.Int32, userid)
                .AddInParameter(CMD, "@NEWPASSWORD", DbType.String, password)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
            Return True
        Catch ex As Exception
            Return False
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Function
#End Region

End Class

<Serializable()> _
public class UserInfo
    public UserID as Int32
    public USerRoles as String()
End Class

<WebService(Namespace:="http://CharterDotComAdminServices/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
Public Class DocumentManagerBL

    Inherits System.Web.Services.WebService


    Private _DocumentManagerDA As DocumentManagerDA

    Sub New()
        _DocumentManagerDA = New DocumentManagerDA
    End Sub


    <WebMethod()> _
    Public Function GetRelatedDocuments(ByVal ID As Integer) As BLHTMLDocument()
        Return _DocumentManagerDA.GetRelatedDocuments(ID)
    End Function
    <WebMethod()> _
    Public Sub RemoveDocumentRelation(ByVal ID As Integer, ByVal IDtoRemove As Integer, ByVal userid As Int32)
        _DocumentManagerDA.RemoveDocumentRelation(ID, IDtoRemove, userid)
    End Sub
    <WebMethod()> _
    Public Function GetAllDocuments() As BLHTMLDocument()
        Return _DocumentManagerDA.GetAllDocuments()

    End Function

    <WebMethod()> _
    Public Sub RelateDocument(ByVal ID As Integer, ByVal IDtoRelate As Integer, ByVal userid As Int32)
        _DocumentManagerDA.RelateDocument(ID, IDtoRelate, userid)
    End Sub

    <WebMethod()> _
    Public Function AddDocument(ByVal folderID As Integer, ByVal document As BLHTMLDocument, ByVal userid As Int32) As Integer
        Return _DocumentManagerDA.AddDocument(folderID, document, userid)
    End Function

    <WebMethod()> _
    Public Function AddFolder(ByVal folder As DocumentFolder, ByVal userid As Int32) As Integer
        Return _DocumentManagerDA.AddFolder(folder, userid)
    End Function

    <WebMethod()> _
    Public Sub DeleteDocument(ByVal documentID As Integer, ByVal userid As Int32)
        _DocumentManagerDA.DeleteDocument(documentID, userid)
    End Sub

    <WebMethod()> _
    Public Sub DeleteFolder(ByVal folderID As Integer, ByVal userid As Int32)
        _DocumentManagerDA.DeleteFolder(folderID, userid)
    End Sub

    <WebMethod()> _
    Public Function GetDistinctKeyWords() As Object()
        Dim objArrList As ArrayList
        objArrList = _DocumentManagerDA.GetDistinctKeyWords()
        If Not objArrList Is Nothing Then
            Return objArrList.ToArray()
        Else
            Return Nothing
        End If
    End Function


    '#####################################################
    'THIS IS USED FOR THE SEARCH CAPABILITY BASED ON 
    'KEYWORDS/ID. -NAQIB 9/23/08  FOR ISSUE ID: 2362   
    '#####################################################
    <WebMethod()> _
    Public Function GetDocumentByKeyword(ByVal keyword As String) As BLHTMLDocument()
        Return _DocumentManagerDA.GetDocumentByKeyword(keyword)
    End Function
    <WebMethod()> _
    Public Function GetDocumentFolderIDByDocumentID(ByVal documentID As Integer) As Integer
        Return _DocumentManagerDA.GetDocumentFolderIDByDocumentID(documentID)
    End Function

    <WebMethod()> _
    Public Function GetDocument(ByVal documentID As Integer) As BLHTMLDocument
        Return _DocumentManagerDA.GetDocument(documentID)
    End Function

    <WebMethod()> _
    Public Function GetDocumentFolders() As DocumentFolder()
        Return _DocumentManagerDA.GetDocumentFolders()

    End Function

    <WebMethod()> _
    Public Function GetDocumentFoldersByParentFolderID(ByVal parentFolderID As Integer) As DocumentFolder()
        Return _DocumentManagerDA.GetDocumentFolders(parentFolderID)

    End Function

    <WebMethod()> _
    Public Function GetFolder(ByVal folderID As Integer) As DocumentFolder
        Return _DocumentManagerDA.GetFolder(folderID)
    End Function

    <WebMethod()> _
    Public Function GetFolderDocuments(ByVal folderID As Integer) As BLHTMLDocument()
        Return _DocumentManagerDA.GetFolderDocuments(folderID)
    End Function

    <WebMethod()> _
    Public Sub MoveDocument(ByVal documentID As Integer, ByVal newFolderID As Integer, ByVal userid As Int32)
        _DocumentManagerDA.MoveDocument(documentID, newFolderID, userid)
    End Sub

    <WebMethod()> _
    Public Sub UpdateDocument(ByVal document As BLHTMLDocument, ByVal userid As Int32)
        _DocumentManagerDA.UpdateDocument(document, userid)
    End Sub

    <WebMethod()> _
    Public Sub UpdateFolder(ByVal folder As DocumentFolder, ByVal userid As Int32)
        _DocumentManagerDA.UpdateFolder(folder, userid)
    End Sub

    <WebMethod()> _
    Public Sub UpdateUploadedFile(ByVal documentID As Integer, ByVal binaryDocument() As Byte, ByVal userid As Integer)
        _DocumentManagerDA.UpdateUploadedFile(documentID, binaryDocument, userid)
    End Sub



    <WebMethod()> _
    Public Sub UploadFile(ByVal buffer() As Byte, ByVal folderId As Integer, ByVal title As String, ByVal IsBinary As Boolean, ByVal userid As Int32)

        Dim retCode As Integer
        Dim DB As Database
        Dim CMD As Data.Common.DbCommand
        Try

            DB = DatabaseFactory.CreateDatabase("Charter.Website.WS.CommerceEngineConnectionString")
            CMD = DB.GetStoredProcCommand("[SUPPORT].[INSERT_DOCUMENT]")

            DB.AddInParameter(CMD, "@DOCUMENTFOLDERID", DbType.Int32)
            DB.AddInParameter(CMD, "@TITLE", DbType.String)
            DB.AddInParameter(CMD, "@SUMMARY", DbType.String)
            DB.AddInParameter(CMD, "@HTML", DbType.String)
            DB.AddInParameter(CMD, "@BINARYDOCUMENT", DbType.Binary)
            DB.AddInParameter(CMD, "@BINARYDOCUMENTMIMETYPE", DbType.String)
            DB.AddInParameter(CMD, "@MIMETYPE", DbType.String)
            DB.AddInParameter(CMD, "@DELETED", DbType.Boolean)
            DB.AddInParameter(CMD, "@KEYWORDS", DbType.String)
            DB.AddInParameter(CMD, "@SEARCHTYPE", DbType.Int32)
            DB.AddInParameter(CMD, "@CREATEDATE", DbType.DateTime)
            DB.AddInParameter(CMD, "@CREATORID", DbType.Int32)


            If IsBinary Then
                CMD.Parameters(0).Value = folderId
                CMD.Parameters(1).Value = title
                CMD.Parameters(2).Value = ""
                CMD.Parameters(3).Value = ""
                CMD.Parameters(4).Value = buffer
                CMD.Parameters(5).Value = ".pdf"
                CMD.Parameters(6).Value = "application/pdf"
                CMD.Parameters(7).Value = 0
                CMD.Parameters(8).Value = ""
                CMD.Parameters(9).Value = 5
                CMD.Parameters(10).Value = Now()
                CMD.Parameters(11).Value = userid

            Else
                Dim html As String = System.Text.Encoding.ASCII.GetString(buffer)
                CMD.Parameters(0).Value = folderId
                CMD.Parameters(1).Value = title
                CMD.Parameters(2).Value = ""
                CMD.Parameters(3).Value = html
                CMD.Parameters(4).Value = Nothing
                CMD.Parameters(5).Value = ""
                CMD.Parameters(6).Value = System.DBNull.Value
                CMD.Parameters(7).Value = 0
                CMD.Parameters(8).Value = ""
                CMD.Parameters(9).Value = 1
                CMD.Parameters(10).Value = Now()
                CMD.Parameters(11).Value = userid

            End If

            retCode = DB.ExecuteNonQuery(CMD)

        Catch ex As Exception
            Throw ex
        Finally
            CMD = Nothing
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try

    End Sub


End Class
