Imports System.Data.Common
Imports System.Web.HttpContext
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Xml
Imports CharterDotCom.CommonEntities.Library
Imports CharterDotCom.CommonEntities.Library.EntityContent
Imports CharterDotCom.CommonEntities.Library.Enumerations
Imports System.Deployment
' Imports CharterDotCom.Common.Logging


Public Class ProviderManager
  Inherits IContentProvider

  Private _cmd As DbCommand
  Private _db As Sql.SqlDatabase
  Public Sub New()
    Me.DB = DatabaseFactory.CreateDatabase("Charter.Website.WS.CommerceEngineConnectionString")
  End Sub

  Public Sub New(ByVal ConnectionStringKey As String)
    Me.DB = DatabaseFactory.CreateDatabase(ConnectionStringKey)
  End Sub
  'Public Sub New(ByVal configData As String)
  '    Dim args As XmlNode
  '    Dim xDoc As XmlDocument
  '    Try
  '        xDoc = New XmlDocument()
  '        xDoc.LoadXml(configData)
  '        args = xDoc.DocumentElement()
  '        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
  '    Catch ex As Exception
  '        Throw ex
  '    End Try
  'End Sub

  'Public Sub New(ByVal args As XmlNode)
  '  ''Try
  '  ''  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
  '  ''Catch ex As Exception
  '  ''  Throw ex
  '  ''Finally

  '  ''End Try
  '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
    'Finally

        'End Try
        Return Nothing
    End Function


  Private Property DB() As Sql.SqlDatabase
    Get
      Return _db
    End Get
    Set(ByVal value As Sql.SqlDatabase)
      _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 Function GetTemplateHeaderInfo() As String
        CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETTEMPLATEHEADERINFO)
        DB.AddOutParameter(CMD, "@xmlHeaderInfo", DbType.String, Int32.MaxValue)
        DB.ExecuteNonQuery(CMD)
        Dim data As String = CMD.Parameters("@xmlHeaderInfo").Value
        Return data
    End Function


    Public Overrides Function GetTemplateContent(ByVal templateID As Integer) As String
        CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETTEMPLATECONTENT)
        DB.AddInParameter(CMD, "@templateID", SqlDbType.Int, templateID)
        DB.AddOutParameter(CMD, "@templateContent", DbType.String, Int32.MaxValue)
        DB.ExecuteNonQuery(CMD)
        Dim data As String = CMD.Parameters("@templateContent").Value
        Return data
    End Function

    Public Overrides Function GetSiteContent() As String
        CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETSITECONTENT)
        DB.AddOutParameter(CMD, "@siteContent", DbType.String, Int32.MaxValue)
        DB.ExecuteNonQuery(CMD)
        Dim data As String = CMD.Parameters("@siteContent").Value
        Return data
    End Function

  Public Overrides Function GetOrgStructure() As String
    Dim cmd As DbCommand = Nothing
    Dim sb As New Text.StringBuilder()
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETORG_STRUCTURE)
      Dim objReader As XmlReader = DB.ExecuteXmlReader(cmd)
      While Not objReader.EOF
        If objReader.IsStartElement() Then
          sb.Append(objReader.ReadOuterXml())
          sb.Append(Environment.NewLine)
        End If
      End While
      If sb.Length > 0 Then
        Return (sb.ToString())
      Else
        Return Nothing
      End If
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      sb = Nothing
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetOwnerTypeList() As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETOWNER_TYPE)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

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

  Public Overrides Function GetOwnerIDList(ByVal OwnerTypeID As Integer) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETOWNER_ID)
      DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnerTypeID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

      Return ds.GetXml()
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetContentNormalized(ByVal ContentID As Integer) As String
    Dim ds As DataSet
    Dim cmd As DbCommand = Nothing
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT_NORMALIZED)
      DB.AddInParameter(cmd, "@CONTENTID", DbType.Int32, ContentID)
      ds = DB.ExecuteDataSet(cmd)

      ds.DataSetName = "CONTENT"

      If ds.Tables.Count <> 0 Then

        Select Case ds.Tables.Count
          Case 1
            ds.Tables(0).TableName = "VERSION"
          Case 2
            ds.Tables(0).TableName = "VERSION"
            ds.Tables(1).TableName = "IMAGE"

            Dim relation As DataRelation = ds.Relations.Add("FK_Version_Image", ds.Tables("VERSION").Columns("VERSIONID"), _
                                            ds.Tables("IMAGE").Columns("VERSIONID"))
            relation.Nested = True
        End Select

      End If



      Return ds.GetXml()
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function

  Public Overrides Function GetContentNormalized(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As String, ByRef ContentID As Integer, ByRef VersionID As Integer) As String
    Dim ds As DataSet = Nothing
    Dim cmd As DbCommand = Nothing
    Dim strTemp As String = Nothing
        Try
            ' Logger.ServiceLog("GetContentNormalized()")

            If UsercontrolID.Trim.ToUpper = Constant.USERCONTROL_CAMPAIGN Then
                cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT_NORMALIZED_CAMPAIGN)
            Else
                cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT_NORMALIZED)
                'cmd = DB.GetStoredProcCommand("dbo.getusercontrolcontent")
            End If
            DB.AddInParameter(cmd, "@USERCONTROLID", DbType.String, UsercontrolID)
            DB.AddInParameter(cmd, "@LOCALEID", DbType.Int32, LocaleID)
            DB.AddInParameter(cmd, "@OWNERID", DbType.String, OwnerID)
            DB.AddInParameter(cmd, "@OWNERTYPEID", DbType.Int32, OwnertypeID)
            If REGIONID <> Nothing Then
                DB.AddInParameter(cmd, "@REGIONID", DbType.Int32, REGIONID)
            End If
            If AreaID <> Nothing Then
                DB.AddInParameter(cmd, "@AREAID", DbType.Int32, AreaID)
            End If
            If BILLINGSTATIONID <> Nothing Then
                DB.AddInParameter(cmd, "@BILLINGSTATIONID", DbType.Int32, BILLINGSTATIONID)
            End If
            If SOURCEFTACODE <> Nothing Then
                DB.AddInParameter(cmd, "@SOURCEFTACODE", DbType.String, SOURCEFTACODE)
            End If

            ds = DB.ExecuteDataSet(cmd)

            ds.DataSetName = "CONTENT"

            If ds.Tables.Count <> 0 Then

                Select Case ds.Tables.Count
                    Case 1
                        ds.Tables(0).TableName = "VERSION"
                        VersionID = ds.Tables(0).Rows(0).Item("VersionID")
                        ContentID = ds.Tables(0).Rows(0).Item("ContentID")
                        strTemp = ds.Tables(0).Rows(0).Item("ContentText")
                    Case 2
                        ds.Tables(0).TableName = "VERSION"
                        ds.Tables(1).TableName = "IMAGE"

                        Dim relation As DataRelation = ds.Relations.Add("FK_Version_Image", ds.Tables("VERSION").Columns("VERSIONID"), _
                                                        ds.Tables("IMAGE").Columns("VERSIONID"))
                        relation.Nested = True
                End Select
            End If

            Return strTemp
        Catch ex As Exception
            Throw ex
            Return Nothing
        Finally
            If Not cmd Is Nothing Then
                cmd.Dispose()
            End If
        End Try
  End Function


  Public Overrides Function GetMultipleContent(ByVal contentparams As String) As DataTable
    Dim ds As DataSet = Nothing
    Dim cmd As DbCommand = Nothing
    Dim strTemp As String = Nothing
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETMULTIPLE_CONTENT)
      DB.AddInParameter(cmd, "@ContentParams", DbType.String, contentparams)

      ds = DB.ExecuteDataSet(cmd)

      ds.DataSetName = "CONTENT"

      If ds.Tables.Count <> 0 Then

        Return ds.Tables(0)
      End If
      Return Nothing
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function

  Public Overrides Function GetContentByTemplateID(ByVal TemplateID As Integer, ByVal RegionID As Integer, ByVal KMAID As Integer, ByVal BillingCode As Integer, ByVal FTA As String) As DataTable
    Dim ds As DataSet = Nothing
    Dim cmd As DbCommand = Nothing
    Dim strTemp As String = Nothing
    Try
            ' Logger.ServiceLog("\ECommerceGateway.Providers\ContentProvider\ProviderManager\GetContentByTemplateID")

      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GET_CONTENT_BY_TEMPLATE_ID)
      DB.AddInParameter(cmd, "@TemplateID", DbType.Int32, TemplateID)
      DB.AddInParameter(cmd, "@REGIONID", DbType.Int32, RegionID)
      DB.AddInParameter(cmd, "@KMAID", DbType.Int32, KMAID)
      DB.AddInParameter(cmd, "@BillingCode", DbType.Int32, BillingCode)
      DB.AddInParameter(cmd, "@FTA", DbType.String, FTA)

      ds = DB.ExecuteDataSet(cmd)
      If ds.Tables.Count <> 0 Then

        Return ds.Tables(0)
      End If
      Return Nothing
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function


    Public Overrides Function GetAllImageIDList() As String
        Dim ds As DataSet
        Try
            CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETALLIMAGEIDLIST)
            ds = DB.ExecuteDataSet(CMD)

            ds.DataSetName = "CONTENT"
            ds.Tables(0).TableName = "IMAGE"

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

    Public Overrides Function GetBinaryFiles(ByVal BinaryFileType As String) As String
        Dim ds As DataSet
        Try
            CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETBINARYFILES)
            If Not BinaryFileType Is Nothing AndAlso BinaryFileType.Length > 0 Then
                DB.AddInParameter(CMD, "@BINARYFILETYPE", DbType.String, BinaryFileType)
            End If
            ds = DB.ExecuteDataSet(CMD)

            ds.DataSetName = "CONTENT"
            ds.Tables(0).TableName = "BINARYFILE"

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

    Public Overrides Function GetImageDataRecordByImageName(ByVal ImageName As String) As String
        Dim ds As DataSet
        Try
            CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETIMAGEDATARECORDBYIMAGEName)
            DB.AddInParameter(CMD, "@ImageName", DbType.String, ImageName)
            ds = DB.ExecuteDataSet(CMD)

            ds.DataSetName = "CONTENT"
            ds.Tables(0).TableName = "IMAGE"

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

    Public Overrides Function GetBinaryFileByBinaryFileName(ByVal BinaryFileName As String, ByVal BinaryFileExtension As String) As String
        Dim ds As DataSet
        Try
            CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETBINARYFILEBYBINARYFILENAME)
            DB.AddInParameter(CMD, "@BinaryFileName", DbType.String, BinaryFileName)
            DB.AddInParameter(CMD, "@BinaryFileExtension", DbType.String, BinaryFileExtension)
            ds = DB.ExecuteDataSet(CMD)

            ds.DataSetName = "CONTENT"
            ds.Tables(0).TableName = "BINARYFILE"

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


    '#####################################################
    'THIS IS USED FOR THE SEARCH CAPABILITY BASED ON 
    'KEYWORDS. Issue: 2362
    'NAQIB 10/9/08    
    '#####################################################
    Public Overrides Function GetImagesByKeyword(ByVal Keyword As String) As String
        Dim ds As DataSet
        Try
            CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETIMAGESBYKEYWORD)
            DB.AddInParameter(CMD, "@KeyWord", DbType.String, Keyword)
            ds = DB.ExecuteDataSet(CMD)

            ds.DataSetName = "CONTENT"
            ds.Tables(0).TableName = "IMAGE"

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



  Public Overrides Function GetContentVersionsByContentID(ByVal ContentID As Integer) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENTVERSIONSBYCONTENTID)
      DB.AddInParameter(CMD, "@ContentID", DbType.Int32, ContentID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "CONTENT"
      ds.Tables(0).TableName = "VERSION"
      Return ds.GetXml()

    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetContentVersionIDList(ByVal ContentID As Integer) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENTVERSIONIDLIST)
      DB.AddInParameter(CMD, "@ContentID", DbType.Int32, ContentID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "VERSIONIDLIST"
      ds.Tables(0).TableName = "VERSIONIDRECORD"
      Return ds.GetXml()

    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetScheduleRecordsByVersionID(ByVal VersionID As Integer) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETSCHEDULERECORDSBYVERSIONID)
      DB.AddInParameter(CMD, "@versionID", DbType.Int32, VersionID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "CONTENT"

      If ds.Tables.Count <> 0 Then
        Select Case ds.Tables.Count
          Case 1
            ds.Tables(0).TableName = "VERSION"
          Case 2
            ds.Tables(0).TableName = "VERSION"
            ds.Tables(1).TableName = "SCHEDULE"

            Dim relation As DataRelation = ds.Relations.Add("FK_Version_SCHEUDLE", ds.Tables("VERSION").Columns("VERSIONID"), _
                                            ds.Tables("SCHEDULE").Columns("VERSIONID"))
            relation.Nested = True
        End Select
      End If


      Return ds.GetXml()
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetContentVersionRecord(ByVal VersionID As Integer) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENTVERSIONRECORD)
      DB.AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "CONTENT"
      ds.Tables(0).TableName = "VERSION"

      Return ds.GetXml()
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetBillingStationID(ByVal BillingSystem As String) As Integer
    Dim cmd As DbCommand = Nothing
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETBILLINGSTATION)
      With Me.DB
        .AddInParameter(cmd, "@BillingCode", DbType.String, BillingSystem)
      End With
      Return DB.ExecuteScalar(cmd)
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetContentID(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As String) As Integer
    Dim ds As DataSet
    Dim intTemp As Integer
    Try
      If UsercontrolID.Trim.ToUpper = Constant.USERCONTROL_CAMPAIGN Then
        CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT_ID_CAMPAIGN)
      Else
        CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT_ID)
      End If
      DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
      DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
      DB.AddInParameter(CMD, "@OWNERID", DbType.String, OwnerID)
      DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnertypeID)
      If REGIONID <> Nothing Then
        DB.AddInParameter(CMD, "@REGIONID", DbType.Int32, REGIONID)
      End If
      If AreaID <> Nothing Then
        DB.AddInParameter(CMD, "@AREAID", DbType.Int32, AreaID)
      End If
      If BILLINGSTATIONID <> Nothing Then
        DB.AddInParameter(CMD, "@BILLINGSTATIONID", DbType.Int32, BILLINGSTATIONID)
      End If
      If SOURCEFTACODE <> Nothing Then
        DB.AddInParameter(CMD, "@SOURCEFTACODE", DbType.String, SOURCEFTACODE)
      End If

      ds = DB.ExecuteDataSet(CMD)
      If Not ds.Tables(0) Is Nothing Then
        intTemp = ds.Tables(0).Rows(0).Item(0)
      Else
        intTemp = 0
      End If
      Return intTemp
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Private Sub SynchronizeContentStatus()
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATECONTENT_STATUS)
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  ''  Public Function GetThumbNailRecordByImageID(ByVal ImageID As Integer) As String Implements IContentProvider.GetThumbNailRecordByImageID
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETTHUMBNAILRECORDBYIMAGEID)
  ''          DB.AddInParameter(CMD, "@ImageID", DbType.String, ImageID)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "IMAGE"

  ''          Return ds.GetXml()
  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function

  ''  Public Function GetThumbNailRecordByImageName(ByVal ImageName As String) As String Implements IContentProvider.GetThumbNailRecordByImageName
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETTHUMBNAILRECORDBYIMAGEname)
  ''          DB.AddInParameter(CMD, "@ImageName", DbType.String, ImageName)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "IMAGE"

  ''          Return ds.GetXml()
  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function

  ''  Public Function GetImageDataRecordByImageID(ByVal ImageID As Integer) As String Implements IContentProvider.GetImageDataRecordByImageID
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETIMAGEDATARECORDBYIMAGEID)
  ''          DB.AddInParameter(CMD, "@ImageID", DbType.String, ImageID)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "IMAGE"

  ''          Return ds.GetXml()
  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function
  ''
  ''Public Function GetImageRecordsByVersionID(ByVal VersionID As Integer) As String Implements IContentProvider.GetImageRecordsByVersionID
  ''  Dim ds As DataSet
  ''  Try
  ''    CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETIMAGERECORDSBYVERSIONID)
  ''    DB.AddInParameter(CMD, "@versionID", DbType.Int32, VersionID)
  ''    ds = DB.ExecuteDataSet(CMD)

  ''    ds.DataSetName = "CONTENT"

  ''    Select Case ds.Tables.Count
  ''      Case 1
  ''        ds.Tables(0).TableName = "VERSION"
  ''      Case 2
  ''        ds.Tables(0).TableName = "VERSION"
  ''        ds.Tables(1).TableName = "IMAGE"

  ''        Dim relation As DataRelation = ds.Relations.Add("FK_Version_Image", ds.Tables("VERSION").Columns("VERSIONID"), _
  ''                                        ds.Tables("IMAGE").Columns("VERSIONID"))
  ''        relation.Nested = True
  ''    End Select


  ''    Return ds.GetXml()
  ''  Catch ex As Exception
  ''    Throw ex
  ''    Return Nothing
  ''  Finally
  ''    If Not CMD Is Nothing Then
  ''      CMD.Dispose()
  ''    End If
  ''  End Try
  ''End Function
  ''
  ''  Public Function GetContentIDList() As String Implements IContentProvider.GetContentIDList
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETALLCONTENTID)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "CONTENTIDRECORDS"

  ''          Return ds.GetXml()
  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function

  ''  Public Function GetContentRecord(ByVal ContentID As Integer) As String Implements IContentProvider.GetContentRecord
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENTRECORD)
  ''          DB.AddInParameter(CMD, "@CONTENTID", DbType.Int32, ContentID)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "CONTENTRECORD"

  ''          Return ds.GetXml()
  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function

  ''  Public Function GetAllContentRecords() As String Implements IContentProvider.GetAllContentRecords
  ''      Dim ds As DataSet
  ''      Try
  ''          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETALLCONTENTRECORDS)
  ''          ds = DB.ExecuteDataSet(CMD)

  ''          ds.DataSetName = "CONTENT"
  ''          ds.Tables(0).TableName = "CONTENTRECORD"
  ''          Return ds.GetXml()

  ''      Catch ex As Exception
  ''          Throw ex
  ''          Return Nothing
  ''      Finally
  ''          If Not CMD Is Nothing Then
  ''              CMD.Dispose()
  ''          End If
  ''      End Try
  ''End Function
  ''
  ''Public Function GetContent(ByVal ContentID As Integer) As String Implements IContentProvider.GetContent
  ''  Dim ds As DataSet
  ''  Try
  ''    CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCONTENT)
  ''    DB.AddInParameter(CMD, "@CONTENTID", DbType.Int32, ContentID)
  ''    ds = DB.ExecuteDataSet(CMD)
  ''    Return ds.GetXml()
  ''  Catch ex As Exception
  ''    Throw ex
  ''    Return Nothing
  ''  Finally
  ''    If Not CMD Is Nothing Then
  ''      CMD.Dispose()
  ''    End If
  ''  End Try
  ''End Function
  Public Overrides Sub AddVersion(ByVal ContentID As Integer, ByVal CreateByUserID As Integer, ByVal CreateDate As Date, ByVal ContentText As String, ByVal ContentName As String)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEVERSION)
      With Me.DB
        .AddInParameter(CMD, "@CONTENTID", DbType.Int32, ContentID)
        .AddInParameter(CMD, "@CONTENT_VERSION_CREATEBY_USERID", DbType.Int32, CreateByUserID)
        .AddInParameter(CMD, "@CONTENT_VERSION_CREATEDATE", DbType.DateTime, CreateDate)
        .AddInParameter(CMD, "@CONTENTTEXT", DbType.String, ContentText)
        .AddInParameter(CMD, "@CONTENTName", DbType.String, ContentName)
      End With
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
    Public Overrides Sub AddImage(ByVal ImageCreatedBy As Integer, ByVal ImageCreateDate As Date, ByVal ImageData() As Byte, ByVal Thumbnail() As Byte, ByVal Caption As String, ByVal ImageName As String)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEIMAGE)
            With Me.DB
                .AddInParameter(CMD, "@IMAGE_CREATEBY_USERID", DbType.Int32, ImageCreatedBy)
                .AddInParameter(CMD, "@IMAGE_CREATEDATE", DbType.DateTime, ImageCreateDate)
                .AddInParameter(CMD, "@ImageData", DbType.Binary, ImageData)
                .AddInParameter(CMD, "@Thumbnail", DbType.Binary, Thumbnail)
                .AddInParameter(CMD, "@Caption", DbType.String, Caption)
                .AddInParameter(CMD, "@ImageName", DbType.String, ImageName)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub AddBinaryFile(ByVal CreatedBy As Integer, ByVal CreateDate As Date, ByVal BinaryFileData() As Byte, ByVal Thumbnail() As Byte, ByVal Caption As String, ByVal BinaryFileName As String, ByVal BinaryFileExtension As String, ByVal BinaryFileType As String)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEBINARYFILE)
            With Me.DB
                .AddInParameter(CMD, "@CREATEBYUSERID", DbType.Int32, CreatedBy)
                .AddInParameter(CMD, "@CREATEDATE", DbType.DateTime, CreateDate)
                .AddInParameter(CMD, "@BinaryFileData", DbType.Binary, BinaryFileData)
                .AddInParameter(CMD, "@Thumbnail", DbType.Binary, Thumbnail)
                .AddInParameter(CMD, "@Caption", DbType.String, Caption)
                .AddInParameter(CMD, "@BinaryFileName", DbType.String, BinaryFileName)
                .AddInParameter(CMD, "@BinaryFileExtension", DbType.String, BinaryFileExtension.ToLower())
                .AddInParameter(CMD, "@BinaryFileType", DbType.String, BinaryFileType)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub AddImageToContent(ByVal ImageID As Integer, ByVal VersionID As Integer)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEVERSION_IMAGE)
            With Me.DB
                .AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
                .AddInParameter(CMD, "@ImageID", DbType.Int32, ImageID)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub
  Public Overrides Sub AddSchedule(ByVal VersionID As Integer, ByVal StartDate As Date, ByVal EndDate As Date, ByVal CreatedBy As Integer, ByVal CreateDate As Date)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATESCHEDULE)
      With Me.DB
        .AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
        .AddInParameter(CMD, "@CONTENT_SCHEDULE_STARTDATE", DbType.DateTime, StartDate)
        If EndDate > Date.Today Then
          .AddInParameter(CMD, "@CONTENT_SCHEDULE_ENDDATE", DbType.DateTime, EndDate)
        End If
        .AddInParameter(CMD, "@CONTENT_SCHEDULE_CREATEBY_USERID", DbType.Int32, CreatedBy)
        .AddInParameter(CMD, "@CONTENT_SCHEDULE_CREATEDATE", DbType.DateTime, CreateDate)
      End With
      retCode = DB.ExecuteNonQuery(CMD)

      SynchronizeContentStatus()
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Function AddContentAndContentVersion(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As String, ByVal CONTENTID As Integer, ByVal CONTENT_VERSION_CREATEBY_USERID As Integer, ByVal ContentText As String, ByVal CONTENT_NAME As String, ByVal CONTENT_VERSION_CREATEDATE As Date) As String
    Dim retCode As String
    Try
      If UsercontrolID.Trim.ToUpper = Constant.USERCONTROL_CAMPAIGN Then
        Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECONTENTANDCONTENTVERSION_CAMPAIGN)
      Else
        Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECONTENTANDCONTENTVERSION)
      End If
      With Me.DB
        DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
        DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
        DB.AddInParameter(CMD, "@OWNERID", DbType.String, OwnerID)
        DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnertypeID)

        If REGIONID <> Nothing Then
          DB.AddInParameter(CMD, "@REGION_ID", DbType.Int32, REGIONID)
        End If
        If AreaID <> Nothing Then
          DB.AddInParameter(CMD, "@AREA_ID", DbType.Int32, AreaID)
        End If
        If BILLINGSTATIONID <> Nothing Then
          DB.AddInParameter(CMD, "@BILLING_STATION_ID", DbType.Int32, BILLINGSTATIONID)
        End If
        If SOURCEFTACODE <> Nothing Then
          DB.AddInParameter(CMD, "@SOURCE_FTA_CODE", DbType.String, SOURCEFTACODE)
        End If

        '.AddInParameter(CMD, "@CONTENTID", DbType.Int32, CONTENTID)

        .AddInParameter(CMD, "@CONTENT_VERSION_CREATEBY_USERID", DbType.Int32, CONTENT_VERSION_CREATEBY_USERID)
        .AddInParameter(CMD, "@CONTENTTEXT", DbType.String, ContentText)
        .AddInParameter(CMD, "@CONTENTNAME", DbType.String, CONTENT_NAME)
        .AddInParameter(CMD, "@CONTENT_VERSION_CREATEDATE", DbType.DateTime, CONTENT_VERSION_CREATEDATE)

      End With
      retCode = DB.ExecuteScalar(CMD)
      Return retCode
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Sub AddContent(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As String, ByVal contentID As Integer)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECONTENT)
      With Me.DB
        DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
        DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
        DB.AddInParameter(CMD, "@OWNERID", DbType.String, OwnerID)
        DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnertypeID)

        If REGIONID <> Nothing Then
          DB.AddInParameter(CMD, "@REGION_ID", DbType.Int32, REGIONID)
        End If
        If AreaID <> Nothing Then
          DB.AddInParameter(CMD, "@AREA_ID", DbType.Int32, AreaID)
        End If
        If BILLINGSTATIONID <> Nothing Then
          DB.AddInParameter(CMD, "@BILLING_STATION_ID", DbType.Int32, BILLINGSTATIONID)
        End If
        If SOURCEFTACODE <> Nothing Then
          DB.AddInParameter(CMD, "@SOURCE_FTA_CODE", DbType.String, SOURCEFTACODE)
        End If

        DB.AddInParameter(CMD, "@CONTENTID", DbType.Int32, contentID)

      End With
      retCode = DB.ExecuteNonQuery(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 PersistCart(ByVal obj As IProviderParameters) As Integer
    '#######################################
    'TEMP LOCAL VARIABLES FOR REQUEST OBJECT
    '#######################################
    Dim objRequest As PersistCartRequest



    Dim ds As DataSet
    Dim VISITOR_TABLE_ID As Integer = 0
    Dim BUNDLE_TABLE_ID As Integer = 0
    Dim SP_TABLE_ID As Integer = 0
    Dim CUST_TABLE_ID As Integer = 0
    Dim CHOICE_TABLE_ID As Integer = 0


    Try
      objRequest = CType(obj, PersistCartRequest)


      '#####################
      'DO INSERT FOR VISITOR
      '#####################
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEVISITOR)

      If IsNumeric(objRequest.OrderID) Then
        Me.DB.AddInParameter(CMD, "@ORDER_ID", DbType.Int32, objRequest.OrderID)
      End If
      If Not (objRequest.GUID Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@GUID", DbType.String, objRequest.GUID)
      End If
      If Not (objRequest.aAddress.Street1 Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@ADDRESS1", DbType.String, objRequest.aAddress.Street1)
      End If
      If Not (objRequest.aAddress.Street2 Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@ADDRESS2", DbType.String, objRequest.aAddress.Street2)
      End If
      If Not (objRequest.aAddress.City Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@CITY", DbType.String, objRequest.aAddress.City)
      End If
      If Not (objRequest.aAddress.State Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@STATE", DbType.String, objRequest.aAddress.State)
      End If
      If Not (objRequest.aAddress.Zip Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@ZIP", DbType.String, objRequest.aAddress.Zip)
      End If
      If Not (objRequest.aAddress.Zip4 Is Nothing) Then
        Me.DB.AddInParameter(CMD, "@ZIP4", DbType.String, objRequest.aAddress.Zip4)
      End If

      ds = DB.ExecuteDataSet(CMD)

      VISITOR_TABLE_ID = ds.Tables(0).Rows(0)(0)


      For Each lObj As Items In objRequest.sShoppingCart.Offers.Values
        If lObj.Type = GwyItemType.ServicePlan Then
          '###########################
          'DO INSERT FOR SERVICE PLAN
          '###########################
          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_CREATESERVICEPLAN)
          DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
          DB.AddInParameter(CMD, "@ID", DbType.Int32, lObj.ID)
          DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, lObj.Description)

          ds = DB.ExecuteDataSet(CMD)

          SP_TABLE_ID = ds.Tables(0).Rows(0)(0)

          If Not lObj.SubItems Is Nothing Then
            For Each objCustomizations As Items In lObj.SubItems.Values
              '###########################
              'DO INSERT FOR CUSTOMIZATIONS 
              '###########################
              Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECUSTOMIZATION)
              Me.DB.AddInParameter(CMD, "@SP_TABLE_ID", DbType.Int32, SP_TABLE_ID)
              Me.DB.AddInParameter(CMD, "@ID", DbType.Int32, objCustomizations.ID)
              Me.DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, objCustomizations.Description)
              Me.DB.AddInParameter(CMD, "@NUMBER", DbType.Int32, objCustomizations.Number)
              Me.DB.AddInParameter(CMD, "@TEXT", DbType.String, IIf(objCustomizations.Text Is Nothing, "", objCustomizations.Text))
              Me.DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
              ds = DB.ExecuteDataSet(CMD)

              CUST_TABLE_ID = ds.Tables(0).Rows(0)(0)

              If Not objCustomizations.SubItems Is Nothing Then
                For Each objChoices As Items In objCustomizations.SubItems.Values
                  '###########################
                  'DO INSERT FOR CHOICES
                  '###########################
                  Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECHOICE)
                  Me.DB.AddInParameter(CMD, "@CUST_TABLE_ID", DbType.Int32, CUST_TABLE_ID)
                  Me.DB.AddInParameter(CMD, "@ID", DbType.Int32, objChoices.ID)
                  Me.DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, objChoices.Description)
                  Me.DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
                  ds = DB.ExecuteDataSet(CMD)

                  CHOICE_TABLE_ID = ds.Tables(0).Rows(0)(0)
                Next
              End If
            Next
          End If
        Else
          '###########################
          'DO INSERT FOR BUNDLE
          '###########################
          CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_CREATEBUNDLE)
          DB.AddInParameter(CMD, "@ID", DbType.Int32, lObj.ID)
          DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, lObj.Description)
          DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
          ds = DB.ExecuteDataSet(CMD)

          BUNDLE_TABLE_ID = ds.Tables(0).Rows(0)(0)

          If Not lObj.SubItems Is Nothing Then
            For Each objsp As Items In lObj.SubItems.Values
              '##########################
              'DO INSERT FOR SERVICE PLAN
              '##########################
              CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_CREATESERVICEPLAN)
              DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
              DB.AddInParameter(CMD, "@BUNDLE_TABLE_ID", DbType.Int32, BUNDLE_TABLE_ID)
              DB.AddInParameter(CMD, "@ID", DbType.Int32, objsp.ID)
              DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, objsp.Description)

              ds = DB.ExecuteDataSet(CMD)

              SP_TABLE_ID = ds.Tables(0).Rows(0)(0)

              If Not objsp.SubItems Is Nothing Then
                For Each objCustomizations As Items In objsp.SubItems.Values
                  '############################
                  'DO INSERT FOR CUSTOMIZATIONS 
                  '############################
                  Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECUSTOMIZATION)
                  Me.DB.AddInParameter(CMD, "@SP_TABLE_ID", DbType.Int32, SP_TABLE_ID)
                  Me.DB.AddInParameter(CMD, "@ID", DbType.Int32, objCustomizations.ID)
                  Me.DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, objCustomizations.Description)
                  Me.DB.AddInParameter(CMD, "@NUMBER", DbType.Int32, objCustomizations.Number)
                  Me.DB.AddInParameter(CMD, "@TEXT", DbType.String, IIf(objCustomizations.Text Is Nothing, "", objCustomizations.Text))
                  Me.DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
                  ds = DB.ExecuteDataSet(CMD)

                  CUST_TABLE_ID = ds.Tables(0).Rows(0)(0)

                  If Not objCustomizations.SubItems Is Nothing Then
                    For Each objChoices As Items In objCustomizations.SubItems.Values
                      '#####################
                      'DO INSERT FOR CHOICES
                      '#####################
                      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_CREATECHOICE)
                      Me.DB.AddInParameter(CMD, "@CUST_TABLE_ID", DbType.Int32, CUST_TABLE_ID)
                      Me.DB.AddInParameter(CMD, "@ID", DbType.Int32, objChoices.ID)
                      Me.DB.AddInParameter(CMD, "@DESCRIPTION", DbType.String, objChoices.Description)
                      Me.DB.AddInParameter(CMD, "@VISITOR_TABLE_ID", DbType.Int32, VISITOR_TABLE_ID)
                      ds = DB.ExecuteDataSet(CMD)

                      CHOICE_TABLE_ID = ds.Tables(0).Rows(0)(0)

                    Next
                  End If
                Next
              End If
            Next
          End If
          Exit For
        End If
      Next


      Return VISITOR_TABLE_ID


    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetCart(ByVal GUID As String) As IProviderResults
    Dim ds As DataSet

    Dim objReponse As New GetCartReponse

    Try

      '#####################
      'GET THE SHOPPING CART
      '#####################
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_GETCART)

      If GUID.Trim() <> "" Then
        Me.DB.AddInParameter(CMD, "@GUID", DbType.String, GUID)
      End If

      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "SHOPPINGCART"
      ds.Tables(0).TableName = "VISITOR"
      ds.Tables(1).TableName = "BUNDLES"
      ds.Tables(2).TableName = "SERVICEPLANS"
      ds.Tables(3).TableName = "CUSTOMIZATIONS"
      ds.Tables(4).TableName = "CHOICES"



      If ds.Tables("VISITOR").Rows.Count() > 0 Then

        For Each row As DataRow In ds.Tables("VISITOR").Rows
          objReponse.VisitorID = row("VISITOR_ID")
          objReponse.GUID = row("GUID").ToString()
          objReponse.OrderID = row("Order_ID")

          Dim tempAddress As New Address
          tempAddress.City = row("City").ToString()
          tempAddress.State = row("State").ToString()
          tempAddress.Street1 = row("ADDRESS1").ToString()
          tempAddress.Street2 = row("ADDRESS2").ToString()
          tempAddress.Zip = row("ZIP").ToString()
          tempAddress.Zip4 = row("ZIP4").ToString()
          objReponse.aAddress = tempAddress
        Next


        Dim choicesTEMP As New Dictionary(Of String, Items)
        Dim customizationsTEMP As New Dictionary(Of String, Items)
        Dim servicePlansTEMP As New Dictionary(Of String, Items)

        Dim customizationsOUT As New Dictionary(Of String, Items)
        Dim servicePlansOUT As New Dictionary(Of String, Items)
        Dim bundlesOUT As New Dictionary(Of String, Items)


        For Each CustomizationRow As DataRow In ds.Tables("CUSTOMIZATIONS").Rows
          choicesTEMP = New Dictionary(Of String, Items)
          For Each ChoiceRow As DataRow In ds.Tables("CHOICES").Rows
            If ChoiceRow("CUSTOMIZATION_TABLE_ID") = CustomizationRow("CUSTOMIZATION_TABLE_ID") Then
              choicesTEMP.Add(ChoiceRow("ID"), New Items(ChoiceRow("ID"), ChoiceRow("DESCRIPTION"), GwyItemType.Choice, 0, Nothing, Nothing))
            End If
          Next
          customizationsOUT.Add(CustomizationRow("ID"), New Items(CustomizationRow("ID"), CustomizationRow("DESCRIPTION"), GwyItemType.Customization, 0, Nothing, choicesTEMP))
        Next

        For Each ServicePlanRow As DataRow In ds.Tables("SERVICEPLANS").Rows
          customizationsTEMP = New Dictionary(Of String, Items)
          For Each Item As Items In customizationsOUT.Values
            Dim lTableID As Integer = GetTableID(ds.Tables("CUSTOMIZATIONS"), Item.ID, "SP_TABLE_ID")
            If lTableID = ServicePlanRow("TABLE_ID") Then
              customizationsTEMP.Add(Item.ID, New Items(Item.ID, Item.Description, Item.Type, 0, Nothing, Item.SubItems))
            End If
          Next
          servicePlansOUT.Add(ServicePlanRow("ID"), New Items(ServicePlanRow("ID"), ServicePlanRow("DESCRIPTION"), GwyItemType.ServicePlan, 0, Nothing, customizationsTEMP))
        Next

        For Each BundleRow As DataRow In ds.Tables("BUNDLES").Rows
          servicePlansTEMP = New Dictionary(Of String, Items)
          For Each Item As Items In servicePlansOUT.Values
            Dim lTableID As Integer = GetTableID(ds.Tables("SERVICEPLANS"), Item.ID, "BUNDLE_TABLE_ID")
            If lTableID = BundleRow("BUNDLE_TABLE_ID") Then
              servicePlansTEMP.Add(Item.ID, New Items(Item.ID, Item.Description, Item.Type, 0, Nothing, Item.SubItems))
            End If
          Next
          bundlesOUT.Add(BundleRow("ID"), New Items(BundleRow("ID"), BundleRow("DESCRIPTION"), GwyItemType.Bundle, 0, Nothing, servicePlansTEMP))
        Next



        Select Case True
          Case bundlesOUT.Count > 0
            '#############################################################################
            'ADD ANY SERVICE PLANS TO THE CART THAT ARE NOT PART OF A BUNDLE ALREADY.
            '#############################################################################
            objReponse.sShoppingCart = New ShoppingCart(New Dictionary(Of String, Items))
            For Each objBundle As Items In bundlesOUT.Values
              For Each objBundleSP As Items In objBundle.SubItems.Values
                Dim lBundleID As Integer = GetTableID(ds.Tables("SERVICEPLANS"), objBundleSP.ID, "BUNDLE_TABLE_ID")
                For Each objSp As Items In servicePlansOUT.Values
                  Dim lSpID As Integer = GetTableID(ds.Tables("SERVICEPLANS"), objSp.ID, "BUNDLE_TABLE_ID")
                  If lBundleID <> lSpID Then
                    If DoesNotExist(objReponse.sShoppingCart.Offers, objSp.ID) Then
                      objReponse.sShoppingCart.Offers.Add(objSp.ID, objSp)
                    End If
                  End If
                Next
              Next
            Next

            '####################################
            'ADD ANY EXISTING BUNDLES TO THE CART
            '####################################
            For Each obj As Items In bundlesOUT.Values
              objReponse.sShoppingCart.Offers.Add(obj.ID, obj)
            Next
          Case servicePlansOUT.Count > 0
            objReponse.sShoppingCart = New ShoppingCart(servicePlansOUT)
          Case customizationsOUT.Count > 0
            objReponse.sShoppingCart = New ShoppingCart(customizationsOUT)
        End Select


      Else
        objReponse = Nothing
      End If



      Return objReponse



    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Private Function DoesNotExist(ByVal lList As Dictionary(Of String, Items), ByVal lLookUpID As Integer) As Boolean
    Try
      Dim results As Boolean = True

      For Each item As Items In lList.Values
        If item.ID = lLookUpID Then
          results = False
        End If
      Next

      Return results

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  Private Function GetTableID(ByVal lTable As DataTable, ByVal lLookUpID As Integer, ByVal lColumnName As String) As Integer
    Try
      For Each Row As DataRow In lTable.Rows
        If lLookUpID = Row("ID") Then

          If IsNumeric(Row(lColumnName)) Then
            Return Row(lColumnName)
          Else
            Return 0
          End If

        End If
      Next

    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
  'Public Sub PersistContentData(ByVal ds As DataSet) Implements IContentProvider.PersistContentData
  '    Dim dictParameter As Dictionary(Of String, Object)
  '    Try
  '        For Each dr As DataRow In ds.Tables(0).Rows
  '            Select Case dr.RowState
  '                Case DataRowState.Added
  '                    dictParameter = BuildParameter(ds, dr)
  '                    AddContent2(dictParameter, dr)
  '                Case DataRowState.Deleted
  '                    dictParameter = BuildParameter(ds, dr)

  '                Case DataRowState.Modified
  '                    dictParameter = BuildParameter(ds, dr)

  '            End Select
  '        Next
  '    Catch ex As Exception
  '        Throw ex
  '    End Try
  'End Sub
  'Private Sub AddContent2(ByVal dictParameter As Dictionary(Of String, Object), ByVal dr As DataRow)
  '    Dim cmd As DbCommand
  '    Dim db As Database = DatabaseFactory.CreateDatabase(Constant.COMMERCEENGINE_CONNECTIONSTRING)
  '    Dim strSplit As String()
  '    Dim retCode As Integer
  '    Dim MethodFlag As String
  '    Try

  '        cmd = db.GetStoredProcCommand(Constant.STOREDPROC_CREATENEWCONTENT)
  '        For Each str As String In dictParameter.Keys
  '            strSplit = str.Split(CType(",", Char))
  '            db.AddInParameter(cmd, strSplit(0), MapType(strSplit(1)), dictParameter(str))
  '        Next
  '        MethodFlag = SetMethodFlag(dr)
  '        db.AddInParameter(cmd, "@Method", DbType.String, MethodFlag)
  '        retCode = db.ExecuteNonQuery(cmd)
  '    Catch ex As Exception
  '        Throw ex
  '    Finally
  '        If Not cmd Is Nothing Then
  '            cmd.Dispose()
  '        End If
  '    End Try
  'End Sub
  Private Function BuildParameter(ByVal ds As DataSet, ByVal dr As DataRow) As Dictionary(Of String, Object)
    Dim dictParameter As New Dictionary(Of String, Object)
    Try


      For Each dc As DataColumn In ds.Tables(0).Columns
        dictParameter.Add("@" + dc.ColumnName + "," + dc.DataType.ToString, dr(dc.ColumnName))
      Next
      Return dictParameter
    Catch ex As Exception
      Throw ex

    Finally

    End Try
  End Function
  Private Function MapType(ByVal InputType As String) As DbType
    Try


      Select Case Type.GetType(InputType).FullName
        Case "System.Int32"
          Return DbType.Int32
        Case "System.Int16"
          Return DbType.Int16
        Case "System.Int64"
          Return DbType.Int64
        Case "System.Boolean"
          Return DbType.Boolean
        Case "System.Byte"
          Return DbType.Byte
        Case "System.Byte[]"
          Return DbType.Binary
        Case "System.Double"
          Return DbType.Double
        Case "System.DateTime"
          Return DbType.DateTime
        Case "System.Decimal"
          Return DbType.Decimal
        Case "System.Guid"
          Return DbType.Guid
        Case "System.String"
          Return DbType.String
        Case Else
          Throw New Exception("Data Type not defined")
      End Select
    Catch ex As Exception
      Throw ex
    Finally

    End Try
  End Function
    Public Overrides Sub UpdateImage(ByVal ImageID As Integer, ByVal ImageCreatedBy As Integer, ByVal ImageCreateDate As Date, ByVal ImageData() As Byte, ByVal Thumbnail() As Byte, ByVal Caption As String, ByVal ImageModifiedBy As Integer, ByVal ImageModifyDate As Date, ByVal ImageApprovedBy As Integer, ByVal ImageApprovedDate As Date)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATEIMAGE)
            With Me.DB
                .AddInParameter(CMD, "@ImageID", DbType.Int32, ImageID)
                .AddInParameter(CMD, "@IMAGE_CREATEBY_USERID", DbType.Int32, ImageCreatedBy)
                .AddInParameter(CMD, "@IMAGE_CREATEDATE", DbType.DateTime, ImageCreateDate)
                .AddInParameter(CMD, "@IMAGE_ModifyBy_UserID", DbType.Int32, ImageModifiedBy)
                .AddInParameter(CMD, "@IMAGE_ModifyDate", DbType.DateTime, ImageModifyDate)
                .AddInParameter(CMD, "@IMAGE_ApprovedBy_UserID", DbType.Int32, ImageApprovedBy)
                .AddInParameter(CMD, "@IMAGE_ApprovedDate", DbType.DateTime, ImageApprovedDate)
                .AddInParameter(CMD, "@ImageData", DbType.Binary, ImageData)
                .AddInParameter(CMD, "@Thumbnail", DbType.Binary, Thumbnail)
                .AddInParameter(CMD, "@Caption", DbType.String, Caption)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub UpdateImage(ByVal ImageName As String, ByVal ImageData() As Byte)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATE_IMAGE_BY_IMAGENAME)
            With Me.DB
                .AddInParameter(CMD, "@ImageName", DbType.String, ImageName)
                .AddInParameter(CMD, "@ImageData", DbType.Binary, ImageData)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub UpdateBinaryFile(ByVal BinaryFileID As Integer, ByVal CreatedBy As Integer, ByVal CreateDate As Date, ByVal BinaryFileData() As Byte, ByVal Thumbnail() As Byte, ByVal Caption As String, ByVal ModifiedBy As Integer, ByVal ModifyDate As Date, ByVal ApprovedBy As Integer, ByVal ApprovedDate As Date, ByVal BinaryFileExtension As String, ByVal BinaryFileType As String)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATEBINARYFILE)
            With Me.DB
                .AddInParameter(CMD, "@BinaryFileID", DbType.Int32, BinaryFileID)
                .AddInParameter(CMD, "@CREATEBYUSERID", DbType.Int32, CreatedBy)
                .AddInParameter(CMD, "@CREATEDATE", DbType.DateTime, CreateDate)
                .AddInParameter(CMD, "@ModifyByUserID", DbType.Int32, ModifiedBy)
                .AddInParameter(CMD, "@ModifyDate", DbType.DateTime, ModifyDate)
                .AddInParameter(CMD, "@ApprovedByUserID", DbType.Int32, ApprovedBy)
                .AddInParameter(CMD, "@ApprovedDate", DbType.DateTime, ApprovedDate)
                .AddInParameter(CMD, "@BinaryFileData", DbType.Binary, BinaryFileData)
                .AddInParameter(CMD, "@Thumbnail", DbType.Binary, Thumbnail)
                .AddInParameter(CMD, "@Caption", DbType.String, Caption)
                .AddInParameter(CMD, "@BinaryFileExtension", DbType.String, BinaryFileExtension.ToLower())
                .AddInParameter(CMD, "@BinaryFileType", DbType.String, BinaryFileType)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub UpdateBinaryFile(ByVal BinaryFileName As String, ByVal BinaryFileExtension As String, ByVal BinaryFileData() As Byte)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATEBINARYFILEBYBINARYFILENAME)
            With Me.DB
                .AddInParameter(CMD, "@BinaryFileName", DbType.String, BinaryFileName)
                .AddInParameter(CMD, "@BinaryFileExtension", DbType.String, BinaryFileExtension)
                .AddInParameter(CMD, "@BinaryFileData", DbType.Binary, BinaryFileData)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

  Public Overrides Sub UpdateSchedule(ByVal ScheduleID As Integer, ByVal StartDate As Date, ByVal EndDate As Date)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATESCHEDULE)
      With Me.DB
        .AddInParameter(CMD, "@ScheduleID", DbType.Int32, ScheduleID)
        .AddInParameter(CMD, "@CONTENT_SCHEDULE_STARTDATE", DbType.DateTime, StartDate)
        .AddInParameter(CMD, "@CONTENT_SCHEDULE_ENDDATE", DbType.DateTime, EndDate)
      End With
      retCode = DB.ExecuteNonQuery(CMD)
      SynchronizeContentStatus()
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Sub UpdateVersion(ByVal VersionID As Integer, ByVal ContentText As String, ByVal VersionName As String, ByVal SearchResultDisplayable As Boolean)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATEVERSION)
      With Me.DB
        .AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
        .AddInParameter(CMD, "@ContentText", DbType.String, ContentText)
        .AddInParameter(CMD, "@VersionName", DbType.String, VersionName)
        .AddInParameter(CMD, "@SearchResultDisplayable", DbType.Boolean, SearchResultDisplayable)
      End With
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Sub UpdateContent(ByVal ContentID As Integer, ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As Integer)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_UPDATECONTENT)
      With Me.DB
        DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
        DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
        DB.AddInParameter(CMD, "@OWNERID", DbType.String, OwnerID)
        DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnertypeID)

        If REGIONID <> Nothing Then
          DB.AddInParameter(CMD, "@REGION_ID", DbType.Int32, REGIONID)
        End If
        If AreaID <> Nothing Then
          DB.AddInParameter(CMD, "@AREA_ID", DbType.Int32, AreaID)
        End If
        If BILLINGSTATIONID <> Nothing Then
          DB.AddInParameter(CMD, "@BILLING_STATION_ID", DbType.Int32, BILLINGSTATIONID)
        End If
        If SOURCEFTACODE <> Nothing Then
          DB.AddInParameter(CMD, "@SOURCE_FTA_CODE", DbType.Int32, SOURCEFTACODE)
        End If

        DB.AddInParameter(CMD, "@CONTENT_NATIONAL", DbType.Int32, OwnertypeID)
        DB.AddInParameter(CMD, "@CONTENT_DIVISION", DbType.Int32, REGIONID)
        DB.AddInParameter(CMD, "@CONTENT_KMA", DbType.Int32, AreaID)
        DB.AddInParameter(CMD, "@CONTENT_BILLINGCODE", DbType.Int32, BILLINGSTATIONID)
        DB.AddInParameter(CMD, "@CONTENT_FTA", DbType.Int32, SOURCEFTACODE)

      End With
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
    Public Overrides Sub DeleteImageByImageName(ByVal ImageName As Integer)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEIMAGEBYIMAGENAME)
            With Me.DB
                .AddInParameter(CMD, "@ImageName", DbType.Int32, ImageName)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub DeleteImageByImageName(ByVal ImageName As String, ByVal filler As String)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEIMAGEBYIMAGENAME)
            With Me.DB
                .AddInParameter(CMD, "@ImageName", DbType.String, ImageName)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub DeleteBinaryFile(ByVal BinaryFileID As Integer)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEBINARYFILE)
            With Me.DB
                .AddInParameter(CMD, "@BinaryFileID", DbType.Int32, BinaryFileID)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub DeleteBinaryFileByBinaryFileName(ByVal BinaryFileName As String, ByVal BinaryFileExtension As String)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEBINARYFILEBYBINARYFILENAME)
            With Me.DB
                .AddInParameter(CMD, "@BinaryFileName", DbType.String, BinaryFileName)
                .AddInParameter(CMD, "@BinaryFileExtension", DbType.String, BinaryFileExtension)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub

    Public Overrides Sub DeleteImageFromContent(ByVal ImageID As Integer, ByVal VersionID As Integer)
        Dim retCode As Integer
        Try
            Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEVERSION_IMAGE)
            With Me.DB
                .AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
                .AddInParameter(CMD, "@ImageID", DbType.Int32, ImageID)
            End With
            retCode = DB.ExecuteNonQuery(CMD)
        Catch ex As Exception
            Throw ex
        Finally
            If Not CMD Is Nothing Then
                CMD.Dispose()
            End If
        End Try
    End Sub
  Public Overrides Sub DeleteSchedule(ByVal ScheduleID As Integer)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETESCHEDULE)
      With Me.DB
        .AddInParameter(CMD, "@ScheduleID", DbType.Int32, ScheduleID)
      End With
      retCode = DB.ExecuteNonQuery(CMD)
      SynchronizeContentStatus()
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Sub DeleteContent(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal OwnerID As String, ByVal OwnertypeID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As Integer)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETECONTENTANDRELATEDRECORDS)
      DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
      DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
      DB.AddInParameter(CMD, "@OWNERID", DbType.String, OwnerID)
      DB.AddInParameter(CMD, "@OWNERTYPEID", DbType.Int32, OwnertypeID)
      If REGIONID <> Nothing Then
        DB.AddInParameter(CMD, "@REGION_ID", DbType.Int32, REGIONID)
      End If
      If AreaID <> Nothing Then
        DB.AddInParameter(CMD, "@AREA_ID", DbType.Int32, AreaID)
      End If
      If BILLINGSTATIONID <> Nothing Then
        DB.AddInParameter(CMD, "@BILLING_STATION_ID", DbType.Int32, BILLINGSTATIONID)
      End If
      If SOURCEFTACODE <> Nothing Then
        DB.AddInParameter(CMD, "@SOURCE_FTA_CODE", DbType.Int32, SOURCEFTACODE)
      End If
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  Public Overrides Sub DeleteVersion(ByVal VersionID As Integer)
    Dim retCode As Integer
    Try
      Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETEVERSIONANDRELATEDRECORDS)
      With Me.DB
        .AddInParameter(CMD, "@VersionID", DbType.Int32, VersionID)
      End With
      retCode = DB.ExecuteNonQuery(CMD)
    Catch ex As Exception
      Throw ex
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Sub
  'Public Sub DeleteContent(ByVal ContentID As Integer) Implements IContentProvider.DeleteContent
  '    Dim retCode As Integer
  '    Try
  '        Me.CMD = Me.DB.GetStoredProcCommand(Constant.STOREDPROC_DELETECONTENTANDRELATEDRECORDS)
  '        With Me.DB
  '            .AddInParameter(CMD, "@ContentID", DbType.Int32, ContentID)
  '        End With
  '        retCode = DB.ExecuteNonQuery(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 GetAreaID(ByVal ZipCode As String) As String
    Dim cmd As DbCommand = Nothing
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_GETAREA_ID)
      With Me.DB
        .AddInParameter(cmd, "@ZIPCODE", DbType.String, ZipCode)
      End With
      Return DB.ExecuteScalar(cmd)
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function GetCharterMessage(ByVal UsercontrolID As String, ByVal LocaleID As Integer, ByVal REGIONID As Integer, ByVal AreaID As Integer, ByVal BILLINGSTATIONID As Integer, ByVal SOURCEFTACODE As String) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETCHARTER_MESSAGES)
      DB.AddInParameter(CMD, "@USERCONTROLID", DbType.String, UsercontrolID)
      DB.AddInParameter(CMD, "@LOCALEID", DbType.Int32, LocaleID)
      If REGIONID <> Nothing Then
        DB.AddInParameter(CMD, "@REGIONID", DbType.Int32, REGIONID)
      End If
      If AreaID <> Nothing Then
        DB.AddInParameter(CMD, "@AREAID", DbType.Int32, AreaID)
      End If
      If BILLINGSTATIONID <> Nothing Then
        DB.AddInParameter(CMD, "@BILLINGSTATIONID", DbType.Int32, BILLINGSTATIONID)
      End If
      If SOURCEFTACODE <> Nothing Then
        DB.AddInParameter(CMD, "@SOURCEFTACODE", DbType.String, SOURCEFTACODE)
      End If

      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "CONTENT"
      ds.Tables(0).TableName = "VERSION"
      Return ds.GetXml()

    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not CMD Is Nothing Then
        CMD.Dispose()
      End If
    End Try
  End Function
  Public Overrides Function SignIn(ByVal Username As String, ByVal Password As String) As Boolean
    Dim cmd As DbCommand = Nothing
    Try
      cmd = DB.GetStoredProcCommand(Constant.STOREDPROC_CHECK_USER)
      With Me.DB
        .AddInParameter(cmd, "@USERNAME", DbType.String, Username)
        .AddInParameter(cmd, "@PASSWORD", DbType.String, Password)
      End With
      Return DB.ExecuteScalar(cmd)
    Catch ex As Exception
      Throw ex
      Return Nothing
    Finally
      If Not cmd Is Nothing Then
        cmd.Dispose()
      End If
    End Try
  End Function


  Public Overrides Function GetServicePlanList() As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETSERVICEPLANLIST)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

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

  Public Overrides Function GetFeatureList() As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETFEATURELIST)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

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

  Public Overrides Function GetBundleList() As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETBUNDLELIST)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"



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

  Public Overrides Function GetEPCIDList(ByVal PlayBookID As String) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETEPC_ID_BY_PLAYBOOK_ID)
      DB.AddInParameter(CMD, "@PLAYBOOKID", DbType.Int32, PlayBookID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

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

  Public Overrides Function GetServicePlanIDByBundleList(ByVal PlayBookID As String) As String
    Dim ds As DataSet
    Try
      CMD = DB.GetStoredProcCommand(Constant.STOREDPROC_GETSERVICEPLAN_ID_BY_BUNDLE_ID)
      DB.AddInParameter(CMD, "@PLAYBOOKID", DbType.Int32, PlayBookID)
      ds = DB.ExecuteDataSet(CMD)

      ds.DataSetName = "OWNERLIST"
      ds.Tables(0).TableName = "OWNERDATA"

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


End Class
