Imports System.Data
Imports System.Xml

Public Class clsHymnal
    Implements ICloneable

    Private mguidHymnalID As Guid
    Private mstrHymnalName As String
    Private mstrHymnalAbbreviation As String
    Private m_blnObjectAlreadyPersisted As Boolean

    Public Sub New()
        mguidHymnalID = Guid.NewGuid
        mstrHymnalName = ""
        mstrHymnalAbbreviation = ""
        m_blnObjectAlreadyPersisted = False
    End Sub

    Public Sub New(ByVal lstrHymnalName As String)
        Dim ldbcmdCommand As New SqlClient.SqlCommand
        ldbcmdCommand.CommandText = "sel_hymnal_by_name"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure
        ' Set up parameter for stored procedure 
        Dim prmHymnalName As New SqlClient.SqlParameter
        prmHymnalName.ParameterName = "@hymnal_name"
        prmHymnalName.SqlDbType = SqlDbType.VarChar
        prmHymnalName.Size = MyGlobals.HYMNAL_NAME_SIZE
        prmHymnalName.Value = lstrHymnalName
        ldbcmdCommand.Parameters.Add(prmHymnalName)

        Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
            Dim ldbrwRow As DataRow
            ldbrwRow = ldbdsHymnals.Tables(0).Rows(0)
            PopulateFromDBRow(ldbrwRow)

            ldbdsHymnals.Clear()
            m_blnObjectAlreadyPersisted = True
        Else
            ldbdsHymnals.Clear()
            mguidHymnalID = Guid.NewGuid
            mstrHymnalName = lstrHymnalName.Trim
            m_blnObjectAlreadyPersisted = False
        End If
    End Sub

    Public Sub New(ByVal lguidHymnalID As Guid)
        mguidHymnalID = lguidHymnalID

        Dim ldbcmdCommand As New SqlClient.SqlCommand
        ldbcmdCommand.CommandText = "sel_hymnal"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure
        ' Set up parameter for stored procedure 
        Dim prmHymnalID As New SqlClient.SqlParameter
        prmHymnalID.ParameterName = "@hymnal_id"
        prmHymnalID.SqlDbType = SqlDbType.UniqueIdentifier
        'prmHymnalID.Size = 5
        prmHymnalID.Value = lguidHymnalID
        ldbcmdCommand.Parameters.Add(prmHymnalID)

        Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
            Dim ldbrwRow As DataRow
            ldbrwRow = ldbdsHymnals.Tables(0).Rows(0)
            PopulateFromDBRow(ldbrwRow)
            m_blnObjectAlreadyPersisted = True
            MyGlobals.AddCachedObject(Me, Me.HymnalID.ToString)
        End If
        ldbdsHymnals.Clear()
    End Sub

    Public Sub New(ByVal ldbrwRow As DataRow)
        PopulateFromDBRow(ldbrwRow)

        m_blnObjectAlreadyPersisted = True
        MyGlobals.AddCachedObject(Me, Me.HymnalID.ToString)
    End Sub

    Private Sub PopulateFromDBRow(ByVal ldbrwRow As DataRow)
        mguidHymnalID = CType(ldbrwRow.Item("hymnal_id"), Guid)

        mstrHymnalName = CType(ldbrwRow.Item("hymnal_name"), String)
        mstrHymnalAbbreviation = CType(ldbrwRow.Item("hymnal_abbreviation"), String)
    End Sub

    Public Function Delete() As Boolean
        Try
            MyGlobals.RemoveCachedObject(Me.HymnalID.ToString)

            Dim ldbcmdCommand As New SqlClient.SqlCommand
            ldbcmdCommand.CommandText = "del_hymnal"

            ldbcmdCommand.CommandType = CommandType.StoredProcedure
            ' Set up parameter for stored procedure 
            Dim prmHymnalID As New SqlClient.SqlParameter
            prmHymnalID.ParameterName = "@hymnal_id"
            prmHymnalID.SqlDbType = SqlDbType.UniqueIdentifier
            'prmHymnalID.Size = 5
            prmHymnalID.Value = mguidHymnalID
            ldbcmdCommand.Parameters.Add(prmHymnalID)

            Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
            If lintRowsAffected > 0 Then
                m_blnObjectAlreadyPersisted = False
                Return True
            Else
                Return False
            End If
        Catch
            Return False
        End Try

    End Function

    Public Function Save() As Boolean
        Try
            MyGlobals.AddCachedObject(Me, Me.HymnalID.ToString)

            If m_blnObjectAlreadyPersisted Then
                Dim ldbcmdCommand As New SqlClient.SqlCommand
                ldbcmdCommand.CommandText = "upd_hymnal"

                ldbcmdCommand.CommandType = CommandType.StoredProcedure
                ' Set up parameter for stored procedure 
                Dim prmHymnalID As New SqlClient.SqlParameter
                prmHymnalID.ParameterName = "@hymnal_id"
                prmHymnalID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmHymnalID.Size = 5
                prmHymnalID.Value = mguidHymnalID
                ldbcmdCommand.Parameters.Add(prmHymnalID)
                ' Set up parameter for stored procedure 
                Dim prmHymnalName As New SqlClient.SqlParameter
                prmHymnalName.ParameterName = "@hymnal_name"
                prmHymnalName.SqlDbType = SqlDbType.VarChar
                prmHymnalName.Size = MyGlobals.HYMNAL_NAME_SIZE
                prmHymnalName.Value = mstrHymnalName
                Dim prmHymnalAbbreviation As New SqlClient.SqlParameter
                prmHymnalAbbreviation.ParameterName = "@hymnal_abbreviation"
                prmHymnalAbbreviation.SqlDbType = SqlDbType.VarChar
                prmHymnalAbbreviation.Size = MyGlobals.HYMNAL_ABBREVIATION_SIZE
                prmHymnalAbbreviation.Value = mstrHymnalAbbreviation
                ldbcmdCommand.Parameters.Add(prmHymnalAbbreviation)

                Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
                If lintRowsAffected > 0 Then
                    Return True
                Else
                    Return False
                End If
            Else
                Dim ldbcmdCommand As New SqlClient.SqlCommand
                ldbcmdCommand.CommandText = "ins_hymnal"

                ldbcmdCommand.CommandType = CommandType.StoredProcedure
                ' Set up parameter for stored procedure 
                Dim prmHymnalID As New SqlClient.SqlParameter
                prmHymnalID.ParameterName = "@hymnal_id"
                prmHymnalID.SqlDbType = SqlDbType.UniqueIdentifier
                'prmHymnalID.Size = 5
                prmHymnalID.Value = mguidHymnalID
                ldbcmdCommand.Parameters.Add(prmHymnalID)
                ' Set up parameter for stored procedure 
                Dim prmHymnalName As New SqlClient.SqlParameter
                prmHymnalName.ParameterName = "@hymnal_name"
                prmHymnalName.SqlDbType = SqlDbType.VarChar
                prmHymnalName.Size = MyGlobals.HYMNAL_NAME_SIZE
                prmHymnalName.Value = mstrHymnalName
                ldbcmdCommand.Parameters.Add(prmHymnalName)
                Dim prmHymnalAbbreviation As New SqlClient.SqlParameter
                prmHymnalAbbreviation.ParameterName = "@hymnal_abbreviation"
                prmHymnalAbbreviation.SqlDbType = SqlDbType.VarChar
                prmHymnalAbbreviation.Size = MyGlobals.HYMNAL_ABBREVIATION_SIZE
                prmHymnalAbbreviation.Value = mstrHymnalAbbreviation
                ldbcmdCommand.Parameters.Add(prmHymnalAbbreviation)

                Dim lintRowsAffected As Integer = MyGlobals.g_objDatabaseLayer.ExecuteNonQuery(ldbcmdCommand)
                If lintRowsAffected > 0 Then
                    m_blnObjectAlreadyPersisted = True
                    Return True
                Else
                    Return False
                End If
            End If
        Catch
            Return False
        End Try

    End Function

    Public Function DuplicateHymnalNameExists(ByVal lstrHymnalNameToCheck As String) As Boolean
        Try
            Dim ldbcmdCommand As New SqlClient.SqlCommand
            ldbcmdCommand.CommandText = "sel_hymnal_by_name"

            ldbcmdCommand.CommandType = CommandType.StoredProcedure
            ' Set up parameter for stored procedure 
            Dim prmHymnalName As New SqlClient.SqlParameter
            prmHymnalName.ParameterName = "@hymnal_name"
            prmHymnalName.SqlDbType = SqlDbType.VarChar
            prmHymnalName.Size = MyGlobals.HYMNAL_NAME_SIZE
            prmHymnalName.Value = lstrHymnalNameToCheck
            ldbcmdCommand.Parameters.Add(prmHymnalName)

            Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

            If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
                Dim ldbrwRow As DataRow
                For Each ldbrwRow In ldbdsHymnals.Tables(0).Rows
                    If mguidHymnalID.Equals(CType(ldbrwRow.Item("hymnal_id"), Guid)) Then
                        'OK
                    Else
                        ldbdsHymnals.Clear()
                        Return True
                    End If
                Next
            End If
            ldbdsHymnals.Clear()
            Return False
        Catch
            Return False
        End Try

    End Function

    Public Function DuplicateHymnalAbbreviationExists(ByVal lstrHymnalAbbreviationToCheck As String) As Boolean
        Try
            Dim ldbcmdCommand As New SqlClient.SqlCommand
            ldbcmdCommand.CommandText = "sel_hymnal_by_abbreviation"

            ldbcmdCommand.CommandType = CommandType.StoredProcedure
            ' Set up parameter for stored procedure 
            Dim prmHymnalAbbreviation As New SqlClient.SqlParameter
            prmHymnalAbbreviation.ParameterName = "@hymnal_abbreviation"
            prmHymnalAbbreviation.SqlDbType = SqlDbType.VarChar
            prmHymnalAbbreviation.Size = MyGlobals.HYMNAL_ABBREVIATION_SIZE
            prmHymnalAbbreviation.Value = mstrHymnalAbbreviation
            ldbcmdCommand.Parameters.Add(prmHymnalAbbreviation)

            Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

            If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
                Dim ldbrwRow As DataRow
                For Each ldbrwRow In ldbdsHymnals.Tables(0).Rows
                    If mguidHymnalID.Equals(CType(ldbrwRow.Item("hymnal_id"), Guid)) Then
                        'OK
                    Else
                        ldbdsHymnals.Clear()
                        Return True
                    End If
                Next
            End If
            ldbdsHymnals.Clear()
            Return False
        Catch
            Return False
        End Try

    End Function

    Public Property HymnalID() As Guid
        Get
            Return mguidHymnalID
        End Get

        Set(ByVal lguidValue As Guid)
            mguidHymnalID = lguidValue
        End Set
    End Property

    Public Property HymnalName() As String
        Get
            Return mstrHymnalName
        End Get

        Set(ByVal sValue As String)
            mstrHymnalName = sValue.Trim
        End Set
    End Property

    Public Property HymnalAbbreviation() As String
        Get
            Return mstrHymnalAbbreviation
        End Get

        Set(ByVal sValue As String)
            mstrHymnalAbbreviation = sValue.Trim
        End Set
    End Property

    Public Property ItemAlreadyPersisted() As Boolean
        Get
            Return m_blnObjectAlreadyPersisted
        End Get
        Set(ByVal Value As Boolean)
            m_blnObjectAlreadyPersisted = Value
        End Set
    End Property

    Public Function Clone() As Object Implements ICloneable.Clone
        Dim lobjReturnValue As New clsHymnal
        lobjReturnValue.HymnalID = Me.HymnalID
        lobjReturnValue.HymnalName = Me.HymnalName
        lobjReturnValue.HymnalAbbreviation = Me.HymnalAbbreviation
        lobjReturnValue.ItemAlreadyPersisted = Me.ItemAlreadyPersisted

        Return lobjReturnValue
    End Function

    Public Function StateIsIdentical(ByVal lobjOtherHymnal As clsHymnal) As Boolean
        If Me.HymnalID.Equals(lobjOtherHymnal.HymnalID) _
        AndAlso Me.HymnalName = lobjOtherHymnal.HymnalName _
        AndAlso Me.HymnalAbbreviation = lobjOtherHymnal.HymnalAbbreviation Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Validate(ByRef lstrErrorString As String) As Boolean
        lstrErrorString = ""
        If Me.HymnalName.Length = 0 Then
            lstrErrorString = "Hymnal Name is required"
            Return False
        End If

        If Me.DuplicateHymnalNameExists(Me.HymnalName) Then
            lstrErrorString = "Given Hymnal Name already exists"
            Return False
        End If

        If Me.DuplicateHymnalAbbreviationExists(Me.HymnalAbbreviation) Then
            lstrErrorString = "Given Hymnal Abbreviation already exists"
            Return False
        End If

        Return True
    End Function

    Public Function OKToDelete(ByRef lstrReferencedByString As String) As Boolean
        Dim lblnReturnValue As Boolean

        lblnReturnValue = True

        lstrReferencedByString = ""


        Dim ldbcmdCommand As New SqlClient.SqlCommand
        ldbcmdCommand.CommandText = "sel_hymnal_isreferenced"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure
        ' Set up parameter for stored procedure 
        Dim prmHymnalID As New SqlClient.SqlParameter
        prmHymnalID.ParameterName = "@hymnal_id"
        prmHymnalID.SqlDbType = SqlDbType.UniqueIdentifier
        'prmHymnalID.Size = 5
        prmHymnalID.Value = mguidHymnalID
        ldbcmdCommand.Parameters.Add(prmHymnalID)

        Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
            lblnReturnValue = False

            Dim ldbrwRow As DataRow
            For Each ldbrwRow In ldbdsHymnals.Tables(0).Rows
                If Not ldbrwRow.IsNull("table_referencing_instance") Then
                    If lstrReferencedByString.Length = 0 Then
                        lstrReferencedByString = CType(ldbrwRow.Item("table_referencing_instance"), String) + "(s)"
                    Else
                        lstrReferencedByString = lstrReferencedByString + " and " + CType(ldbrwRow.Item("table_referencing_instance"), String) + "(s)"
                    End If
                End If
            Next

        End If
        ldbdsHymnals.Clear()

        Return lblnReturnValue

    End Function

    Public Overrides Function ToString() As String
        Return mstrHymnalName
    End Function

    Public Sub WriteXML(ByRef objXMLWriter As XmlTextWriter)
        objXMLWriter.WriteStartElement("Hymnal")

        objXMLWriter.WriteElementString("HymnalID", Me.HymnalID.ToString)
        objXMLWriter.WriteElementString("HymnalName", Me.HymnalName)
        objXMLWriter.WriteElementString("HymnalAbbreviation", Me.HymnalAbbreviation)

        objXMLWriter.WriteEndElement()
    End Sub

    Public Shared Function GetAllHymnals() As System.Collections.Generic.List(Of clsHymnal)
        Dim lguidHymnalID As Guid
        Dim ldbcmdCommand As New SqlClient.SqlCommand
        Dim lobjHymnal As clsHymnal
        Dim lcolHymnals As New System.Collections.Generic.List(Of clsHymnal)

        ldbcmdCommand.CommandText = "sel_hymnal_all"

        ldbcmdCommand.CommandType = CommandType.StoredProcedure
        Dim ldbdsHymnals As DataSet = MyGlobals.g_objDatabaseLayer.ExecuteDataSet(ldbcmdCommand)

        If ldbdsHymnals.Tables(0).Rows.Count > 0 Then
            Dim ldbrwRow As DataRow
            For Each ldbrwRow In ldbdsHymnals.Tables(0).Rows
                lguidHymnalID = CType(ldbrwRow.Item("hymnal_id"), Guid)

                If MyGlobals.CachedObjectExists(lguidHymnalID.ToString) Then
                    lobjHymnal = CType(MyGlobals.CachedObjectRetrieve(lguidHymnalID.ToString), clsHymnal)
                Else
                    'lobjHymnal = New clsHymnal(lguidHymnalID)
                    lobjHymnal = New clsHymnal(ldbrwRow)
                End If
                lcolHymnals.Add(lobjHymnal)
            Next
        End If
        ldbdsHymnals.Clear()

        Return lcolHymnals
    End Function

    Public Shared Sub AddAllHymnalsToCache()
        If MyGlobals.g_objObjectTypesCached.Contains("Hymnals") = False Then
            GetAllHymnals()
            MyGlobals.g_objObjectTypesCached.Add("Hymnals")
        End If
    End Sub

End Class