Imports Microsoft.VisualBasic

Public Class CmsAdapter

    Public Sub CmsAdapter()
    End Sub

    Public _connectionString As String = ConfigurationManager.ConnectionStrings("PrintItConnectionString").ConnectionString


    Public Function GetAllPagesByStoreId(ByVal _storeId As Integer) As System.Collections.Generic.List(Of PageDetails)
        Dim _pageDetailsList As New System.Collections.Generic.List(Of PageDetails)

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "Select * From PI_WebPages Where StoreId=@StoreId Order by PageOrder"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@StoreId", _storeId)
            dbConnection.Open()
            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    Dim _pageDetails As New PageDetails()
                    _pageDetails.PageId = dbReader("PageId")
                    _pageDetails.StoreId = dbReader("StoreId")
                    _pageDetails.PageName = dbReader("PageName")
                    _pageDetails.PageTitle = dbReader("PageTitle")
                    _pageDetails.PageContent = dbReader("PageContent")
                    _pageDetails.PageOrder = dbReader("PageOrder")
                    _pageDetails.AllowRobots = dbReader("AllowRobots")
                    _pageDetails.MetaTagDetailsList = Me.GetMetaTagsByPageId(dbReader("PageId"))
                    _pageDetailsList.Add(_pageDetails)
                End While
            End Using
            dbConnection.Close()
        End Using
        ' using
        Return _pageDetailsList
    End Function

    Public Function UpdateAllPages(ByVal _pageDetailsList As System.Collections.Generic.List(Of PageDetails)) As Boolean
        Dim _pageDetails As PageDetails
        Dim _result As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            For Each _pageDetails In _pageDetailsList
                Dim _command As New System.Data.SqlClient.SqlCommand()
                _command.CommandText = "UPDATE PI_WebPages SET StoreId=@StoreId,PageName=@PageName,PageTitle=@PageTitle,PageContent=@PageContent,PageOrder=@PageOrder, AllowRobots=@AllowRobots WHERE PageId=@PageId"
                _command.Connection = _connection
                _command.Parameters.AddWithValue("@PageId", _pageDetails.PageId)
                _command.Parameters.AddWithValue("@StoreId", _pageDetails.StoreId)
                _command.Parameters.AddWithValue("@PageName", _pageDetails.PageName)
                _command.Parameters.AddWithValue("@PageTitle", _pageDetails.PageTitle)
                _command.Parameters.AddWithValue("@PageContent", _pageDetails.PageContent)
                _command.Parameters.AddWithValue("@PageOrder", _pageDetails.PageOrder)
                _command.Parameters.AddWithValue("@AllowRobots", _pageDetails.AllowRobots)
                Try
                    _command.ExecuteNonQuery()
                    _result = True
                Catch ex As System.Exception
                    _result = False
                End Try
            Next
            _connection.Close()
        End Using

        Return _result

    End Function


    Public Function GetPageByPageId(ByVal _pageId As Integer) As PageDetails
        Dim _pageDetails As New PageDetails()
        _pageDetails.PageId = _pageId

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "Select * From PI_WebPages Where PageId=@PageId"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@PageId", _pageId)
            dbConnection.Open()
            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    ' Create Object from dbReader.
                    _pageDetails.StoreId = dbReader("StoreId")
                    _pageDetails.PageName = dbReader("PageName")
                    _pageDetails.PageTitle = dbReader("PageTitle")
                    _pageDetails.PageContent = dbReader("PageContent")
                    _pageDetails.PageOrder = dbReader("PageOrder")
                    _pageDetails.AllowRobots = dbReader("AllowRobots")
                End While
            End Using
            dbConnection.Close()
        End Using
        ' using
        'If _pageId <> 0 Then
        _pageDetails.MetaTagDetailsList = Me.GetMetaTagsByPageId(_pageId)
        'End If
        Return _pageDetails
    End Function

    Public Function UpdatePage(ByVal _pageDetails As PageDetails) As Boolean

        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()

            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandText = "UPDATE PI_WebPages SET StoreId=@StoreId,PageName=@PageName,PageTitle=@PageTitle,PageContent=@PageContent,PageOrder=@PageOrder,AllowRobots=@AllowRobots WHERE PageId=@PageId"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@PageId", _pageDetails.PageId)
            _command.Parameters.AddWithValue("@StoreId", _pageDetails.StoreId)
            _command.Parameters.AddWithValue("@PageName", _pageDetails.PageName)
            _command.Parameters.AddWithValue("@PageTitle", _pageDetails.PageTitle)
            _command.Parameters.AddWithValue("@PageContent", _pageDetails.PageContent)
            _command.Parameters.AddWithValue("@PageOrder", _pageDetails.PageOrder)
            _command.Parameters.AddWithValue("@AllowRobots", _pageDetails.AllowRobots)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    Public Function DeletePage(ByVal _pageDetails As PageDetails) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandText = "Begin Tran " & _
                                   "DELETE FROM PI_MetaTags WHERE PageId = @PageId " & _
                                   "DELETE FROM PI_WebPages WHERE PageId = @PageId " & _
                                   "Commit Tran"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@PageId", _pageDetails.PageId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As System.Exception
                Return False
            End Try
            _connection.Close()
        End Using

    End Function

    Public Function InsertPage(ByVal _pageDetails As PageDetails) As PageDetails
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertWebPage"
            _command.Parameters.AddWithValue("@StoreId", _pageDetails.StoreId)
            _command.Parameters.AddWithValue("@PageName", _pageDetails.PageName)
            _command.Parameters.AddWithValue("@PageTitle", _pageDetails.PageTitle)
            _command.Parameters.AddWithValue("@PageContent", _pageDetails.PageContent)
            _command.Parameters.AddWithValue("@PageOrder", _pageDetails.PageOrder)
            _command.Parameters.AddWithValue("@AllowRobots", _pageDetails.AllowRobots)
            Try
                _pageDetails.PageId = _command.ExecuteScalar()
            Catch ex As Exception
                _pageDetails.PageId = 0
            End Try
            _connection.Close()
        End Using
        Return _pageDetails
    End Function

    Public Function DeleteWebPage(ByVal pageId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "DELETE FROM PI_WebPages WHERE PageId=@PageId"
            _command.Parameters.AddWithValue("@PageId", pageId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function



    Public Function GetMetaTagsByPageId(ByVal _pageId As Integer) As System.Collections.Generic.List(Of MetaTagDetails)
        Dim _metaTagDetailsList As New System.Collections.Generic.List(Of MetaTagDetails)

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "SELECT * FROM PI_MetaTags WHERE PageId = @PageId"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@PageId", _pageId)
            dbConnection.Open()
            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    ' Create Object from dbReader.
                    Dim _metaTagDetails As New MetaTagDetails()
                    _metaTagDetails.MetaTagId = dbReader("MetaTagId")
                    _metaTagDetails.PageId = dbReader("PageId")
                    _metaTagDetails.MetaName = dbReader("MetaName")
                    _metaTagDetails.MetaContent = dbReader("MetaContent")
                    _metaTagDetailsList.Add(_metaTagDetails)
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using
        ' using
        Return _metaTagDetailsList
    End Function

    Public Function GetMetaTagByMetaTagId(ByVal _metaTagId As Integer) As MetaTagDetails
        Dim _metaTagDetails As New MetaTagDetails()
        _metaTagDetails.MetaTagId = _metaTagId

        Using dbConnection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim sqlCommand As New System.Data.SqlClient.SqlCommand()
            sqlCommand.CommandText = "SELECT * FROM PI_MetaTags WHERE MetaTagId = @MetaTagId"
            sqlCommand.Connection = dbConnection
            sqlCommand.Parameters.AddWithValue("@MetaTagId", _metaTagId)
            dbConnection.Open()
            Using dbReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.SingleResult And System.Data.CommandBehavior.CloseConnection)
                While dbReader.Read()
                    ' Create Object from dbReader.
                    _metaTagDetails.PageId = dbReader("PageId")
                    _metaTagDetails.MetaName = dbReader("MetaName")
                    _metaTagDetails.MetaContent = dbReader("MetaContent")
                End While
            End Using
            ' using
            dbConnection.Dispose()
        End Using
        ' using
        Return _metaTagDetails
    End Function

    Public Function DeleteMetaTag(ByVal metaTagId As Integer) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "DELETE FROM PI_MetaTags WHERE MetaTagId=@MetaTagId"
            _command.Parameters.AddWithValue("@MetaTagId", metaTagId)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function


    Public Function InsertMetaTag(ByVal pageId As Integer, ByVal metaName As String, ByVal metaContent As String) As Integer
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.StoredProcedure
            _command.Connection = _connection
            _command.CommandText = "InsertMetaTag"
            _command.Parameters.AddWithValue("@PageId", pageId)
            _command.Parameters.AddWithValue("@MetaName", metaName)
            _command.Parameters.AddWithValue("@MetaContent", metaContent)
            Try
                Return _command.ExecuteScalar()
            Catch ex As Exception
                Return 0
            End Try
            _connection.Close()
        End Using
    End Function


    Public Function UpdateMetaTag(ByVal metaTagId As Integer, ByVal metaName As String, ByVal metaContent As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Update PI_MetaTags Set MetaName=@MetaName, MetaContent=@MetaContent Where MetaTagId=@MetaTagId"
            _command.Parameters.AddWithValue("@MetaTagId", metaTagId)
            _command.Parameters.AddWithValue("@MetaName", metaName)
            _command.Parameters.AddWithValue("@MetaContent", metaContent)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

    Public Function GetECardBuilderByStoreId(ByVal storeId As Integer) As System.Data.DataSet
        Dim _dataSet As New System.Data.DataSet
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.Connection = _connection
            _command.CommandText = "Select * From PI_ECardBuilderMapping Where StoreId=@StoreId"
            _command.Parameters.AddWithValue("@StoreId", storeId)
            Dim _dataAdapter As New System.Data.SqlClient.SqlDataAdapter(_command)
            _dataAdapter.Fill(_dataSet, "ECardBuilder")
        End Using
        Return _dataSet
    End Function

    Public Function UpdateIsEnabled(ByVal storeId As Integer, ByVal isEnabled As Boolean) As Boolean
        Dim _result As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Update PI_ECardBuilderMapping Set IsEnabled=@IsEnabled Where StoreId=@StoreId"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@IsEnabled", isEnabled)
            Try
                _command.ExecuteNonQuery()
                _result = True
            Catch ex As System.Exception
                _result = False
            End Try
            _connection.Close()
        End Using
        Return _result
    End Function

    Public Function InsertECardBuilder(ByVal storeId As Integer, ByVal siteId As Integer, ByVal isEnabled As Boolean, ByVal url As String, ByVal password As String, ByVal eCardBuilderResult As String) As Boolean
        Using _connection As New System.Data.SqlClient.SqlConnection(_connectionString)
            _connection.Open()
            Dim _command As New System.Data.SqlClient.SqlCommand()
            _command.CommandType = Data.CommandType.Text
            _command.CommandText = "Insert Into PI_ECardBuilderMapping (StoreId, SiteId, IsEnabled, Url, Password) Values (@StoreId, @SiteId, @IsEnabled, @Url, @Password)"
            _command.Connection = _connection
            _command.Parameters.AddWithValue("@StoreId", storeId)
            _command.Parameters.AddWithValue("@SiteId", siteId)
            _command.Parameters.AddWithValue("@IsEnabled", isEnabled)
            _command.Parameters.AddWithValue("@Url", url)
            _command.Parameters.AddWithValue("@Password", password)
            _command.Parameters.AddWithValue("@ECardBuilderResult", eCardBuilderResult)
            Try
                _command.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False
            End Try
            _connection.Close()
        End Using
    End Function

End Class