Imports System.Data
Imports Microsoft.VisualBasic
Imports LYB.VB

Namespace LYB.VB.CLASSES
    Public Class Member
        Private _memberid As Integer
        Private _firstName As String
        Private _lastName As String
        Private _fullName As String
        Private _mailAddressHome As String
        Private _mailAddressWork As String
        Private _phoneNumberHome As String
        Private _phoneNumberWork As String
        Private _cellPhoneNumber As String
        Private _postAddress As String
        Private _zipCode As String
        Private _city As String
        Private _groupStartPage As String
        Private _currentLoginUserID As String
        Private _currentLoginGroup As Integer
        Private _fontFamilyID As Integer
        Private _fontFamily As String
        Private _fontSize As String

        Private data As New DataUtils

        Public ReadOnly Property MemberID() As Integer
            Get
                Return _memberid
            End Get
        End Property
        Public Property FirstName() As String
            Get
                Return _firstName
            End Get
            Set(ByVal value As String)
                _firstName = value
                _fullName = _firstName & " " & _lastName
            End Set
        End Property
        Public Property LastName() As String
            Get
                Return _lastName
            End Get
            Set(ByVal value As String)
                _lastName = value
                _fullName = _firstName & " " & _lastName
            End Set
        End Property
        Public ReadOnly Property FullName() As String
            Get
                Return _fullName
            End Get
        End Property
        Public Property MailAddressHome() As String
            Get
                Return _mailAddressHome
            End Get
            Set(ByVal value As String)
                _mailAddressHome = value
            End Set
        End Property
        Public Property MailAddressWork() As String
            Get
                Return _mailAddressWork
            End Get
            Set(ByVal value As String)
                _mailAddressWork = value
            End Set
        End Property
        Public Property PhoneNumberHome() As String
            Get
                Return _phoneNumberHome
            End Get
            Set(ByVal value As String)
                _phoneNumberHome = value
            End Set
        End Property
        Public Property PhoneNumberWork() As String
            Get
                Return _phoneNumberWork
            End Get
            Set(ByVal value As String)
                _phoneNumberWork = value
            End Set
        End Property
        Public Property CellPhoneNumber() As String
            Get
                Return _cellPhoneNumber
            End Get
            Set(ByVal value As String)
                _cellPhoneNumber = value
            End Set
        End Property
        Public Property PostAddress() As String
            Get
                Return _postAddress
            End Get
            Set(ByVal value As String)
                _postAddress = value
            End Set
        End Property
        Public Property ZipCode() As String
            Get
                Return _zipCode
            End Get
            Set(ByVal value As String)
                _zipCode = value
            End Set
        End Property
        Public Property City() As String
            Get
                Return _city
            End Get
            Set(ByVal value As String)
                _city = value
            End Set
        End Property
        Public ReadOnly Property GroupStartPage() As String
            Get
                Return _groupStartPage
            End Get
        End Property
        Public ReadOnly Property CurrentLoginUserID() As String
            Get
                Return _currentLoginUserID
            End Get
        End Property
        Public Property CurrentLoginGroup() As Integer
            Get
                Return _currentLoginGroup
            End Get
            Set(ByVal value As Integer)
                _currentLoginGroup = value
            End Set
        End Property
        Public Property FontFamilyID() As Integer
            Get
                Return _fontFamilyID
            End Get
            Set(ByVal value As Integer)
                _fontFamilyID = value
            End Set
        End Property
        Public Property FontFamily() As String
            Get
                Return _fontFamily
            End Get
            Set(ByVal value As String)
                _fontFamily = value
            End Set
        End Property
        Public Property FontSize() As String
            Get
                Return _fontSize
            End Get
            Set(ByVal value As String)
                _fontSize = value
            End Set
        End Property
        Public Sub New(ByVal loginID As String, ByVal password As String, ByVal sessionID As String)
            Dim result As String = ValidatePassword(loginID, password)
            If Left(result, 5) = "ERROR" Then
                data.AddToErrorLog(loginID, 0, 0, result, sessionID, 1)
                Throw New System.Exception(result)
            Else
                _currentLoginUserID = loginID
                _groupStartPage = result
                _currentLoginGroup = CType(Mid(result, 6, 1), Integer)
            End If

        End Sub
        Public Sub New(ByVal memberID As Integer)
            Dim ds As DataSet
            Dim sql As String

            sql = "SELECT m.MemberID, m.firstname, m.lastname, " & _
                    "m.mailaddresshome, m.mailaddresswork, " & _
                    "m.phonenumberhome, m.phonenumberwork, m.cellphonenumber, " & _
                    "m.postaddress, m.zipcode, m.city, " & _
                    "m.memberhash, g.groupstartpageLYB2, g.GroupSystemDownPage, " & _
                    "m.fontfamilyid, m.fontsize, ff.fontfamily " & _
                    "FROM member m " & _
                    "INNER JOIN group_member gm ON gm.memberid = m.memberid " & _
                    "INNER JOIN [group] g ON g.groupid = gm.groupid " & _
                    "LEFT OUTER JOIN fontfamily ff ON ff.fontfamilyid = m.fontfamilyid " & _
                "WHERE (m.memberid = " & CStr(memberID) & ")"
            Try
                ds = data.GetBySQL(sql, "member")
                If ds.Tables("member").Rows.Count > 0 Then
                    Me._memberid = ds.Tables("member").Rows(0).Item("memberid")
                    Me.FirstName = ds.Tables("member").Rows(0).Item("firstname")
                    Me.LastName = ds.Tables("member").Rows(0).Item("lastname")
                    If Not ds.Tables("member").Rows(0).Item("mailaddresswork") Is System.DBNull.Value Then
                        If ds.Tables("member").Rows(0).Item("mailaddresswork") <> "" Then
                            Me.MailAddressWork = ds.Tables("member").Rows(0).Item("mailaddresswork")
                            _currentLoginUserID = Me.MailAddressWork
                        End If
                    End If
                    If Not ds.Tables("member").Rows(0).Item("mailaddresshome") Is System.DBNull.Value Then
                        If ds.Tables("member").Rows(0).Item("mailaddresshome") <> "" Then
                            Me.MailAddressHome = ds.Tables("member").Rows(0).Item("mailaddresshome")
                            _currentLoginUserID = Me.MailAddressHome
                        End If
                    End If
                    If Not ds.Tables("member").Rows(0).Item("phonenumberhome") Is System.DBNull.Value Then
                        Me.PhoneNumberHome = ds.Tables("member").Rows(0).Item("phonenumberhome")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("phonenumberwork") Is System.DBNull.Value Then
                        Me.PhoneNumberWork = ds.Tables("member").Rows(0).Item("phonenumberwork")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("cellphonenumber") Is System.DBNull.Value Then
                        Me.CellPhoneNumber = ds.Tables("member").Rows(0).Item("cellphonenumber")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("postaddress") Is System.DBNull.Value Then
                        Me.PostAddress = ds.Tables("member").Rows(0).Item("postaddress")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("zipcode") Is System.DBNull.Value Then
                        Me.ZipCode = ds.Tables("member").Rows(0).Item("zipcode")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("city") Is System.DBNull.Value Then
                        Me.City = ds.Tables("member").Rows(0).Item("city")
                    End If
                    If Not ds.Tables("member").Rows(0).Item("fontfamilyid") Is System.DBNull.Value Then
                        Me.FontFamilyID = ds.Tables("member").Rows(0).Item("fontfamilyid")
                    Else
                        Me.FontFamilyID = 0
                    End If
                    If Not ds.Tables("member").Rows(0).Item("fontfamily") Is System.DBNull.Value Then
                        Me.FontFamily = ds.Tables("member").Rows(0).Item("fontfamily")
                    Else
                        Me.FontFamily = ""
                    End If
                    If Not ds.Tables("member").Rows(0).Item("fontsize") Is System.DBNull.Value Then
                        Me.FontSize = ds.Tables("member").Rows(0).Item("fontsize")
                    Else
                        Me.FontSize = ""
                    End If
                    _groupStartPage = ds.Tables("member").Rows(0).Item("groupstartpageLYB2")
                End If
            Catch ex As System.Exception
                Throw New System.Exception(ex.Message)
            End Try

        End Sub
        Public Function Save(ByVal memberid As Integer) As String
            Dim ds As DataSet
            Dim sql As String = "SELECT * FROM member WHERE memberid = " & CStr(memberid)
            ds = data.GetBySQL(sql, "member")
            With ds.Tables("member").Rows(0)
                .Item("firstname") = Me.FirstName
                .Item("lastname") = Me.LastName
                .Item("mailaddresshome") = Me.MailAddressHome
                .Item("mailaddresswork") = Me.MailAddressWork
                .Item("phonenumberhome") = Me.PhoneNumberHome
                .Item("phonenumberwork") = Me.PhoneNumberWork
                .Item("cellphonenumber") = Me.CellPhoneNumber
                .Item("postaddress") = Me.PostAddress
                .Item("zipcode") = Me.ZipCode
                .Item("city") = Me.City
                .Item("fontfamilyid") = Me.FontFamilyID
                .Item("fontsize") = Me.FontSize
            End With
            Dim result As String = data.UpdateBatch(ds, "member")
            Return result
        End Function
        Private Function ValidatePassword(ByVal emailAddress As String, ByVal password As String) As String
            Dim ds As DataSet
            Dim sql As String
            Dim passwordHash As String

            sql = "SELECT m.MemberID, m.firstname, m.lastname, " & _
                    "m.mailaddresshome, m.mailaddresswork, " & _
                    "m.phonenumberhome, m.phonenumberwork, m.cellphonenumber, " & _
                    "m.postaddress, m.zipcode, m.city, " & _
                    "m.memberhash, g.groupstartpageLYB2, g.GroupSystemDownPage, " & _
                    "m.fontfamilyid, m.fontsize, ff.fontfamily " & _
                    "FROM member m " & _
                    "INNER JOIN group_member gm ON gm.memberid = m.memberid " & _
                    "INNER JOIN [group] g ON g.groupid = gm.groupid " & _
                    "LEFT OUTER JOIN fontfamily ff ON ff.fontfamilyid = m.fontfamilyid " & _
                "WHERE (m.mailaddresshome = '" & emailAddress & "' OR m.mailaddresswork = '" & emailAddress & "')"
            Try
                ds = data.GetBySQL(sql, "member")
                If ds.Tables("member").Rows.Count > 0 Then
                    If Not ds.Tables("member").Rows(0).Item("memberhash") Is System.DBNull.Value Then
                        passwordHash = ds.Tables("member").Rows(0).Item("memberhash")
                        If data.CreateHash(password) = passwordHash Then
                            If Not ds.Tables("member").Rows(0).Item("GroupSystemDownPage") Is System.DBNull.Value Then
                                If ds.Tables("member").Rows(0).Item("memberid") = 1 Then
                                    Return ds.Tables("member").Rows(0).Item("groupstartpageLYB2")
                                Else
                                    Return ds.Tables("member").Rows(0).Item("GroupSystemDownPage")
                                End If
                            Else
                                Me._memberid = ds.Tables("member").Rows(0).Item("memberid")
                                Me.FirstName = ds.Tables("member").Rows(0).Item("firstname")
                                Me.LastName = ds.Tables("member").Rows(0).Item("lastname")
                                If Not ds.Tables("member").Rows(0).Item("mailaddresshome") Is System.DBNull.Value Then
                                    Me.MailAddressHome = ds.Tables("member").Rows(0).Item("mailaddresshome")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("mailaddresswork") Is System.DBNull.Value Then
                                    Me.MailAddressWork = ds.Tables("member").Rows(0).Item("mailaddresswork")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("phonenumberhome") Is System.DBNull.Value Then
                                    Me.PhoneNumberHome = ds.Tables("member").Rows(0).Item("phonenumberhome")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("phonenumberwork") Is System.DBNull.Value Then
                                    Me.PhoneNumberWork = ds.Tables("member").Rows(0).Item("phonenumberwork")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("cellphonenumber") Is System.DBNull.Value Then
                                    Me.CellPhoneNumber = ds.Tables("member").Rows(0).Item("cellphonenumber")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("postaddress") Is System.DBNull.Value Then
                                    Me.PostAddress = ds.Tables("member").Rows(0).Item("postaddress")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("zipcode") Is System.DBNull.Value Then
                                    Me.ZipCode = ds.Tables("member").Rows(0).Item("zipcode")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("city") Is System.DBNull.Value Then
                                    Me.City = ds.Tables("member").Rows(0).Item("city")
                                End If
                                If Not ds.Tables("member").Rows(0).Item("fontfamilyid") Is System.DBNull.Value Then
                                    Me.FontFamilyID = ds.Tables("member").Rows(0).Item("fontfamilyid")
                                Else
                                    Me.FontFamilyID = 0
                                End If
                                If Not ds.Tables("member").Rows(0).Item("fontfamily") Is System.DBNull.Value Then
                                    Me.FontFamily = ds.Tables("member").Rows(0).Item("fontfamily")
                                Else
                                    Me.FontFamily = ""
                                End If
                                If Not ds.Tables("member").Rows(0).Item("fontsize") Is System.DBNull.Value Then
                                    Me.FontSize = ds.Tables("member").Rows(0).Item("fontsize")
                                Else
                                    Me.FontSize = ""
                                End If
                                Return ds.Tables("member").Rows(0).Item("groupstartpageLYB2")
                            End If
                        Else
                            Return "ERROR hash mismatch (wrong password entered)"
                        End If
                    Else
                        Return "ERROR no hash value in db"
                    End If
                Else
                    Return "ERROR unknown user"
                End If
            Catch ex As System.Exception
                Return "ERROR in ValidatePassword: " & ex.ToString
            End Try
        End Function

    End Class
End Namespace