Imports System.Data
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.Collections
Imports System.Configuration
Imports System.Web.Mobile
Imports System.Security.Cryptography
Imports System.IO
Imports VB = Microsoft.VisualBasic
Imports System.Text
Imports System.Runtime.InteropServices

Namespace LYB.VB

    Public Class DataUtils
        Private connectionstringName As String = "MyLocalSqlServer"
        Private conn As SqlConnection
        Private adapter As New SqlDataAdapter
        Private data As New DataSet
        Private selectString As String
        Private errString As String
        Private hits As Integer

        Public Function Authenticate(ByVal userID As String, ByVal passWord As String, ByVal sessionID As String) As String
            Dim result As String = ValidatePassword(userID, passWord)
            If Left(result, 5) = "ERROR" Then
                AddToErrorLog(userID, 0, 0, result, sessionID, 1)
                Throw New System.Exception(result)
            Else
                Return result
            End If
        End Function
        Public Function FindComposerID(ByVal composerName As String) As Integer
            Dim SQL As String = "SELECT ComposerID FROM Composer WHERE ComposerName = '" + composerName + "'"
            Dim ds As DataSet = GetBySQL(SQL, "composer")
            If ds.Tables(0).Rows.Count = 1 Then
                Return ds.Tables(0).Rows(0).Item("ComposerID")
            Else
                Return 0
            End If
        End Function
        Public Function FindWebPageData(ByVal webPageDataView As DataView, ByVal pageID As String) As String
            Dim dw As DataView = webPageDataView
            Dim dateLastChanged As String = ""
            For Each row As DataRowView In dw
                If row.Item("pageid") = pageID Then
                    dateLastChanged = Left(row.Item("contentlastchanged").ToString(), 10)
                    Exit For
                End If
            Next
            Return dateLastChanged
        End Function
        Public Function GetBoardMembers(ByVal groupID As Integer) As DataView
            'Dim groupArray As ArrayList = GetGroupsForLoginUser(emailAddress)
            Dim sql As String
            sql = "SELECT m.firstname, m.lastname, m.mailaddresshome, m.mailaddresswork, " & _
                "m.phonenumberhome, m.phonenumberwork, m.cellphonenumber, " & _
                "f.functionname, f.functionnameshort, " & _
                "mf.startdate, mf.enddate " & _
                "FROM member m " & _
                "INNER JOIN Group_Member gm ON gm.MemberID = m.MemberID " & _
                "INNER JOIN Member_GroupMemberFunction mf ON mf.GroupID = gm.GroupID " & _
                "AND mf.MemberID = m.MemberID " & _
                "INNER JOIN GroupMemberFunction f ON f.GroupMemberFunctionID = mf.GroupMemberFunctionID " & _
                "WHERE gm.groupid = " & CStr(groupID) & " " & _
                "AND f.functiontype = 'S' " & _
                "AND mf.startdate <= GETDATE() " & _
                "AND mf.enddate >= GETDATE() " & _
                "ORDER BY f.sortorder"
            Dim ds As DataSet = GetBySQL(sql, "boardmember")
            If ds.Tables("boardmember").Rows.Count > 0 Then
                Dim dw As New DataView(ds.Tables("boardmember"))
                Return dw
            Else
                Return Nothing
            End If

        End Function
        Public Function GetBySQL(ByVal sqlQuery As String, ByVal ReturnDataSetName As String) As DataSet

            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text

                data.Tables.Clear()
                adapter.Fill(data, ReturnDataSetName)
            Catch e1 As System.NullReferenceException
                Throw New SQLServerConfigException(e1.Message().ToString)
            Catch e2 As System.Data.SqlClient.SqlException
                conn.Close()
                errString = e2.ToString
                If InStr(errString, "Login failed") > 0 Then
                    Throw New SQLServerLoginException(e2.Message().ToString)
                ElseIf InStr(errString, "SQL Server does not exist or access denied") > 0 Then
                    Throw New SQLServerAccessException(e2.Message().ToString)
                ElseIf InStr(errString, "a connection to the server") > 0 Then
                    Throw New SQLServerAccessException(e2.Message().ToString)
                Else
                    Throw New SQLServerConfigException(e2.Message().ToString)
                End If

            Catch ex As Exception
                conn.Close()
                Throw New System.Exception("Fel vid läsning av databasen: " & ex.Message)
            End Try
            Return data

        End Function
        Public Function GetBySQL(ByVal sqlQuery As String, ByVal ReturnDataSetName As String, ByVal keepTables As Boolean) As DataSet

            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text

                adapter.Fill(data, ReturnDataSetName)
            Catch e1 As System.NullReferenceException
                Throw New SQLServerConfigException(e1.Message().ToString)
            Catch e2 As System.Data.SqlClient.SqlException
                conn.Close()
                errString = e2.ToString
                If InStr(errString, "Login failed") > 0 Then
                    Throw New SQLServerLoginException(e2.Message().ToString)
                ElseIf InStr(errString, "SQL Server does not exist or access denied") > 0 Then
                    Throw New SQLServerAccessException(e2.Message().ToString)
                ElseIf InStr(errString, "a connection to the server") > 0 Then
                    Throw New SQLServerAccessException(e2.Message().ToString)
                Else
                    Throw New SQLServerConfigException(e2.Message().ToString)
                End If

            Catch ex As Exception
                conn.Close()
                Throw New System.Exception("Fel vid läsning av databasen: " & ex.Message)
            End Try
            Return data

        End Function
        Public Function UpdateBatch(ByVal dataSet As DataSet, ByVal TableName As String) As String
            Dim ds As New DataSet
            Dim selectString As String, count As Integer
            Dim cmdBuilder As SqlCommandBuilder
            Dim returnString As String

            conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)

            selectString = "select * from " + TableName + " where 0 = 1"
            Try
                adapter.SelectCommand = New SqlCommand(selectString, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.SelectCommand.Connection.Open()
                cmdBuilder = New SqlCommandBuilder(adapter)
                count = adapter.Update(dataSet, TableName)
                returnString = "OK " + Format(count, "00000000") + " Records Updated/Deleted"
            Catch ex As Exception
                Throw New System.Exception("Error in UpdateBatch(" + TableName + "): " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
            Return returnString

        End Function
        Public Sub UpdateComposerID(ByVal composerID As Integer, ByVal recordIndex As Integer)
            Dim SQL As String = "SELECT * FROM Composition WHERE 1=1 ORDER BY CompositionName"
            Dim ds As DataSet = GetBySQL(SQL, "composition")
            Dim row As DataRow
            Dim recordCount As Integer = 0
            For Each row In ds.Tables("composition").Rows
                If recordIndex = recordCount Then
                    row("ComposerID") = composerID
                    Exit For
                End If
                recordCount += 1
            Next
            Dim result As String = UpdateBatch(ds, "composition")

        End Sub
        Public Sub UpdatePageChangedDate(ByVal groupID As Integer, ByVal pageName As String)
            Dim sql As String = "SELECT * FROM webpage WHERE groupid = " & CStr(groupID) & " AND pageid = '" & pageName & "'"
            Try
                Dim ds As DataSet = GetBySQL(sql, "webpage")
                If ds.Tables("webpage").Rows.Count = 1 Then
                    ds.Tables("webpage").Rows(0).Item("contentlastchanged") = Now()
                    Dim result As String = UpdateBatch(ds, "webpage")
                End If
            Catch ex As Exception
                ' no action
            End Try

        End Sub

        Public Function UpdatePassword(ByVal memberid As Integer, ByVal password As String) As String
            Dim ds As DataSet
            Dim sql As String
            'Dim passwordHash As String

            sql = "SELECT * FROM member " & _
                "WHERE (memberid = " & CStr(memberid) & ")"
            Try
                ds = GetBySQL(sql, "member")
                If ds.Tables("member").Rows.Count = 1 Then
                    Dim hashedPassword As String = CreateHash(password)
                    If Left(hashedPassword, 5) = "ERROR" Then
                        Throw New System.Exception(hashedPassword)
                    End If
                    ds.Tables("member").Rows(0).Item("memberhash") = hashedPassword
                    ds.Tables("member").Rows(0).Item("membertemp") = ""
                    Dim result As String = UpdateBatch(ds, "member")
                    If Left(result, 2) = "OK" Then
                        Return ""
                    Else
                        Return "ERROR in UpdateBatch in UpdatePassword:" & result
                    End If
                Else
                    Return "ERROR in UpdatePassword: " & CStr(ds.Tables("member").Rows.Count) & " member rows found"
                End If
            Catch ex As System.Exception
                Return "ERROR in UpdatePassword: " & ex.ToString
            End Try

        End Function
        Public 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 member.MemberID, MemberHash, groupstartpageLYB2, GroupSystemDownPage FROM member, group_member, [group] " & _
                "WHERE (mailaddresshome = '" & emailAddress & "' OR mailaddresswork = '" & emailAddress & "') " & _
                "AND group_member.memberid = member.memberid and group_member.groupid = [group].groupid"
            Try
                ds = 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 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
                                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
        Public Function VerifyUser(ByVal emailAddress As String) As String
            Dim selectString As String
            'Dim count As Integer
            selectString = "SELECT member.mailaddresshome, member.mailaddresswork, group_member.groupid, [group].groupname FROM member, group_member, [group] " & _
                            "WHERE (member.mailaddresshome = '" & emailAddress & "' OR member.mailaddresswork = '" & emailAddress & "') " & _
                            "AND group_member.memberid = member.memberid AND [group].groupid = group_member.groupid"
            Try
                Dim ds As DataSet = GetBySQL(selectString, "member_group")
                If ds.Tables("member_group").Rows.Count > 0 Then
                    Return ds.Tables("member_group").Rows(0).Item("groupname")
                Else
                    Return ""
                End If
            Catch ex As System.Exception
                Return "ERROR in VerifyUser: " + ex.ToString
            End Try
        End Function
        Public Function VerifyUser(ByVal memberid As Integer) As String
            Dim selectString As String
            'Dim count As Integer
            selectString = "SELECT member.mailaddresshome, member.mailaddresswork, group_member.groupid, [group].groupname FROM member, group_member, [group] " & _
                            "WHERE member.memberid = " & CStr(memberid) & " " & _
                            "AND group_member.memberid = member.memberid AND [group].groupid = group_member.groupid"
            Try
                Dim ds As DataSet = GetBySQL(selectString, "member_group")
                If ds.Tables("member_group").Rows.Count > 0 Then
                    Return ds.Tables("member_group").Rows(0).Item("groupname")
                Else
                    Return ""
                End If
            Catch ex As System.Exception
                Return "ERROR in VerifyUser: " + ex.ToString
            End Try
        End Function
        Public Function CreateHash(ByVal password As String) As String
            Try
                Dim bytHash As Byte()
                Dim uEncode As New UnicodeEncoding
                Dim bytSource() As Byte = uEncode.GetBytes(password)
                Dim sha1 As New SHA1CryptoServiceProvider
                bytHash = sha1.ComputeHash(bytSource)
                Dim hashedPassword As String = Convert.ToBase64String(bytHash)
                Return hashedPassword
            Catch ex As System.Exception
                Return "ERROR: " + ex.ToString
            End Try
        End Function
        Public Function CreatePassword(ByVal emailAddress As String) As String
            Dim lettersBig As String = "ABCDEFGHIJKLMNOPQRSTUVXYZ"
            Dim lettersSmall As String = "abcdefghijklmnopqrstuvxyz"
            Dim digits As String = "0123456789"
            Dim newPassword As String = ""
            Dim ptr As Integer

            Try
                Randomize()
                For r As Integer = 1 To 3
                    ptr = CInt(Int((25 * Rnd()) + 1))
                    newPassword = newPassword & Mid(lettersBig, ptr, 1)
                    ptr = CInt(Int((25 * Rnd()) + 1))
                    newPassword = newPassword & Mid(lettersSmall, ptr, 1)
                    ptr = CInt(Int((10 * Rnd()) + 1))
                    newPassword = newPassword & Mid(digits, ptr, 1)
                Next r
                ' Create a Hash Digest
                Dim hashedPassword As String = CreateHash(newPassword)
                If Left(hashedPassword, 5) = "ERROR" Then
                    Throw New System.Exception(hashedPassword)
                End If
                ' Save hashed password in member record
                Dim selectString As String
                'Dim count As Integer
                selectString = "SELECT * FROM member " & _
                                "WHERE (member.mailaddresshome = '" & emailAddress & "' OR member.mailaddresswork = '" & emailAddress & "')"
                Dim ds As DataSet = GetBySQL(selectString, "member")
                If ds.Tables("member").Rows.Count <> 1 Then
                    Return "no unique hit"
                Else
                    ds.Tables("member").Rows(0).Item("memberhash") = hashedPassword
                    ds.Tables("member").Rows(0).Item("membertemp") = newPassword
                    Dim result As String = UpdateBatch(ds, "member")
                    If Left(result, 2) = "OK" Then
                        Return newPassword & "\" & hashedPassword
                    Else
                        Return "ERROR in UpdateBatch in CreatePassword:" & result
                    End If
                End If
            Catch ex As System.Exception
                Return "ERROR in CreatePassword: " & ex.ToString
            End Try

        End Function
        Public Function DeleteCommand(ByVal table As String, ByVal selectSql As String, ByVal deleteSql As String) As Integer
            Dim deleteResult As Integer = 0
            Dim result As Integer = 0
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                Dim da As New SqlDataAdapter
                Dim ds As New DataSet
                da.SelectCommand = New SqlCommand(selectSql, conn)
                da.Fill(ds, table)
                Dim deleteCmd As New SqlCommand(deleteSql, conn)
                For Each row As DataRow In ds.Tables(table).Rows
                    row.Delete()
                    deleteResult = deleteResult + 1
                Next
                If deleteResult > 0 Then
                    da.DeleteCommand = deleteCmd
                    result = da.Update(ds, table)
                End If
            Catch ex As System.Exception
                Dim er As String = ex.Message
                Dim a As Integer = 0
                'Throw New System.Exception("Error in DeleteCommand for " & deleteSql & ": " & ex.Message)
            Finally
                conn.Close()
            End Try

            Return deleteResult
        End Function
        Public Function GetFont() As DataSet
            Dim sql As String = "SELECT * FROM fontfamily WHERE fontfamilyid > 0 ORDER BY fontfamily"
            Dim ds As DataSet = GetBySQL(sql, "fontfamily")
            Return ds
        End Function
        Public Function GetForMaintenance(ByVal SQL As String, ByVal dataTableName As String) As DataSet
            Dim ds As DataSet = GetBySQL(SQL, dataTableName)
            Return ds
        End Function
        Public Function GetMember(ByVal memberID As Integer) As DataSet
            Dim sql As String = "SELECT m.memberid, m.firstname, m.lastname, m.mailaddresshome, m.mailaddresswork, " & _
                "m.phonenumberhome, m.phonenumberwork, m.cellphonenumber, " & _
                "m.postaddress, m.zipcode, m.city, m.fontfamilyid, m.fontsize, " & _
                "m.memberhash, m.membertemp, m.ipaddress1, m.ipaddress2, m.ipaddress3, ff.fontfamily " & _
                "FROM member m " & _
                "LEFT OUTER JOIN fontfamily ff ON ff.fontfamilyid = m.fontfamilyid " & _
                "WHERE m.memberid = " & CStr(memberID) & " "
            Dim ds As DataSet = GetBySQL(sql, "member")
            Return ds
        End Function
        Public Function GetMembers(ByVal memberid As Integer) As DataView
            Dim groupArray As ArrayList = GetGroupsForLoginUser(memberid)
            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, mf.notenumber, " & _
                "f.functiontype, f.functionname, f.functionnameshort, f.sortorder, " & _
                "mf.startdate, mf.enddate " & _
                "FROM member m " & _
                "INNER JOIN Group_Member gm ON gm.memberid = m.memberid " & _
                "INNER JOIN Member_GroupMemberFunction mf ON mf.GroupID = gm.GroupID " & _
                "AND mf.MemberID = m.MemberID " & _
                "INNER JOIN GroupMemberFunction f ON f.GroupMemberFunctionID = mf.GroupMemberFunctionID " & _
                "WHERE gm.groupid = " & CStr(groupArray(0)) & " " & _
                "ORDER BY f.sortorder, m.lastname"
            '"AND f.functiontype <> 'S' " & _
            Dim ds As DataSet = GetBySQL(sql, "member")
            If ds.Tables("member").Rows.Count > 0 Then
                Dim dw As New DataView(ds.Tables("member"))
                Return dw
            Else
                Return Nothing
            End If

        End Function
        Public Function GetMemberID(ByVal emailAddress As String, ByVal dw As DataView) As Integer
            Dim returnValue As Integer = 0
            For Each row As DataRowView In dw
                If Not row.Item("MailAddressHome") Is System.DBNull.Value Then
                    If Trim(row.Item("MailAddressHome")) <> "" Then
                        If emailAddress = row.Item("MailAddressHome") Then
                            returnValue = row.Item("memberid")
                            Exit For
                        End If
                    ElseIf Not row.Item("MailAddressWork") Is System.DBNull.Value Then
                        If Trim(row.Item("MailAddressWork")) <> "" Then
                            If emailAddress = row.Item("MailAddressWork") Then
                                returnValue = row.Item("memberid")
                                Exit For
                            End If
                        End If
                    End If
                ElseIf Not row.Item("MailAddressWork") Is System.DBNull.Value Then
                    If Trim(row.Item("MailAddressWork")) <> "" Then
                        If emailAddress = row.Item("MailAddressWork") Then
                            returnValue = row.Item("memberid")
                            Exit For
                        End If
                    End If
                End If
            Next
            Return returnValue

        End Function
        Public Function GetMemberName(ByVal memberid As Integer) As String
            Dim name As String = ""
            Dim sql As String = "SELECT firstname, lastname FROM member WHERE memberid = " + CStr(memberid)
            Dim ds As DataSet = GetBySQL(sql, "member")
            If ds.Tables("member").Rows.Count > 0 Then
                name = ds.Tables("member").Rows(0).Item("firstname") & " " & ds.Tables("member").Rows(0).Item("lastname")
            End If
            Return name
        End Function
        Public Function GetMemberName(ByVal emailAddress As String) As String
            Dim name As String = ""
            Dim sql As String = "SELECT firstname, lastname FROM member WHERE mailaddresshome = '" + emailAddress + "' OR mailaddresswork = '" + emailAddress + "'"
            Dim ds As DataSet = GetBySQL(sql, "member")
            If ds.Tables("member").Rows.Count > 0 Then
                name = ds.Tables("member").Rows(0).Item("firstname") & " " & ds.Tables("member").Rows(0).Item("lastname")
            End If
            Return name
        End Function
        'Public Function GetMembersForMobile(ByVal emailAddress As String) As DataView

        'End Function
        Public Function GetNextID(ByVal tableName As String) As Integer
            Dim SQL As String
            Dim ds As DataSet
            Select Case LCase(tableName)
                Case "composer"
                    SQL = "SELECT MAX(composerid) FROM composer"
                    ds = GetBySQL(SQL, "composerid")
                    Return ds.Tables("composerid").Rows(0).Item(0)
                Case "composition"
                    SQL = "SELECT MAX(compositionid) FROM composition"
                    ds = GetBySQL(SQL, "compositionid")
                    Return ds.Tables("compositionid").Rows(0).Item(0)
            End Select
            Return 0
        End Function
        Public Function GetNumberOfActualDocumentsForGroup(ByVal groupID As Integer) As Integer
            Dim SQL As String
            Dim ds As DataSet
            SQL = "SELECT cdl.Composition_DocumentPath_ListID FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "WHERE l.GroupID = " & CStr(groupID) & " " & _
                    "AND l.ListType = 'AR'"

            ds = GetBySQL(SQL, "actualdocuments")
            Dim actualdocumentsCount As Integer
            actualdocumentsCount = ds.Tables("actualdocuments").Rows.Count
            Return actualdocumentsCount
        End Function
        Public Function GetNumberOfConcertsDocumentsForGroup(ByVal groupID As Integer) As Integer
            Dim SQL As String
            Dim ds As DataSet
            SQL = "SELECT cdl.Composition_DocumentPath_ListID FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "WHERE l.GroupID = " & CStr(groupID) & " " & _
                    "AND l.ListType = 'NC'"

            ds = GetBySQL(SQL, "concertsdocuments")
            Dim concertsdocumentsCount As Integer
            concertsdocumentsCount = ds.Tables("concertsdocuments").Rows.Count
            Return concertsdocumentsCount
        End Function
        Public Function GetNumberOfLibraryDocumentsForGroup(ByVal groupID As Integer) As Integer
            Dim SQL As String
            Dim ds As DataSet
            SQL = "SELECT cdl.Composition_DocumentPath_ListID FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "WHERE l.GroupID = " & CStr(groupID) & " " & _
                    "AND l.ListType = 'NL'"

            ds = GetBySQL(SQL, "librarydocuments")
            Dim librarydocumentsCount As Integer
            librarydocumentsCount = ds.Tables("librarydocuments").Rows.Count
            Return librarydocumentsCount
        End Function
        Public Function GetNumberOfListsForGroup(ByVal groupID As Integer) As Integer
            Dim SQL As String
            Dim ds As DataSet
            SQL = "SELECT l.ListType FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "WHERE l.GroupID = " & CStr(groupID) & " " & _
                    "ORDER BY l.ListType"

            ds = GetBySQL(SQL, "lists")
            Dim listType As String = ""
            Dim typeCount As Integer = 0
            If ds.Tables("lists").Rows.Count > 0 Then
                For Each row As DataRow In ds.Tables("lists").Rows
                    If row.Item("ListType").ToString() <> listType Then
                        typeCount = typeCount + 1
                        listType = row.Item("ListType").ToString()
                    End If
                Next
            End If
            Return typeCount
        End Function
        Public Function GetUserIDCookie(ByVal request As HttpRequest) As String()
            Dim returnArray As String() = {"", ""}
            Try
                Dim userIDCookie As HttpCookie = request.Cookies.Get("LYB2ASPNET2USERID")
                Dim pos As Integer = InStr(userIDCookie.Value, "?") - 1
                Dim posHash As Integer = InStr(userIDCookie.Value, "#") - 1
                Dim groupName As String = VerifyUser(CType(userIDCookie.Value.Substring(0, pos), Integer))
                Dim memberid As Integer = 0
                Dim groupStartPage As String = ""
                Dim cookieHash As String = ""
                If groupName <> "" Then
                    memberid = userIDCookie.Value.Substring(0, pos)
                    groupStartPage = userIDCookie.Value.Substring(pos + 1, posHash - (pos + 1))
                    cookieHash = userIDCookie.Value.Substring(posHash + 1)
                    If cookieHash <> CreateHash(Left(userIDCookie.Value, InStr(userIDCookie.Value, "#"))) Then
                        Throw New System.Exception("userid cookie has been hacked#" + userIDCookie.Value.ToString())
                    End If
                    returnArray(0) = memberid
                    returnArray(1) = groupStartPage
                Else
                    Throw New System.Exception("unknown user")
                End If
            Catch ex As Exception
                Throw New System.Exception(ex.Message)
            End Try
            Return returnArray
        End Function
        Public Function GetWebPagesData(ByVal groupID As Integer) As DataView
            Dim sql As String = "SELECT * FROM webpage WHERE groupid = " & CStr(groupID)
            Try
                Dim ds As DataSet = GetBySQL(sql, "webpage")
                Dim dw As New DataView(ds.Tables("webpage"))
                Return dw
            Catch ex As System.Exception
                Throw New System.Exception("Fel vid GetWebPagesData: " & ex.Message)
            End Try
        End Function
        Public Function IsMemberAdministrator(ByVal memberid As Integer, ByVal dw As DataView) As Boolean
            Dim returnValue As Boolean = False
            For Each row As DataRowView In dw
                If CType(row.Item("memberid"), Integer) = memberid Then
                    If row.Item("functiontype") = "A" Then
                        returnValue = True
                        Exit For
                    End If
                End If
            Next
            Return returnValue
        End Function
        Public Function IsMemberOfGroup(ByVal groupID As Integer, ByVal memberID As Integer) As Boolean
            Dim sql As String = "SELECT m.memberid, gm.groupid FROM member m INNER JOIN Group_Member gm ON gm.memberid = m.memberid WHERE m.memberid = " & CStr(memberID)
            Dim ds As DataSet = GetBySQL(sql, "group_member")
            Dim isMember As Boolean = False
            For Each row As DataRow In ds.Tables("group_member").Rows
                If row.Item("groupid") = groupID Then
                    isMember = True
                    Exit For
                End If
            Next
            Return isMember
        End Function
        Public Function MailPassword(ByVal newPassword As String, ByVal emailAddress As String, ByVal groupName As String) As String
            Dim data As New DataUtils
            Dim email As System.Net.Mail.MailMessage
            Dim returnValue As String = ""
            Try
                Dim SMTPServer As String = ""
                Dim emailSender As String = ""
                GetMailParam(SMTPServer, emailSender)
                If Left(SMTPServer, 5) = "ERROR" Then
                    returnValue = "ERROR in MailPassword: " & SMTPServer
                Else
                    email = New System.Net.Mail.MailMessage(emailSender, _
                                                            emailAddress, _
                                                            "Välkommen att logga in! Här är ditt nya lösenord", _
                            "Ditt nya lösenord för inloggning till " & groupName & " är " & newPassword & ". " & _
                            "Kopiera det och klistra in det i lösenordsfältet. Du kan byta lösenord när du har loggat in." & _
                             vbCrLf & vbCrLf & "Hälsningar" & vbCrLf & "Leif")
                    'email.To = emailAddress
                    'email.From = emailSender
                    'email.Body = "Ditt nya lösenord för inloggning till " & groupName & " är " & newPassword & ". " & _
                    '        "Kopiera det och klistra in det i lösenordsfältet. Du kan byta lösenord när du har loggat in." & _
                    '         vbCrLf & vbCrLf & "Hälsningar" & vbCrLf & "Leif"
                    'email.Subject = "Välkommen att logga in! Här är ditt nya lösenord"
                    email.IsBodyHtml = True     'BodyFormat = System.Net.Mail.MailMessage.IsBodyHtml
                    Dim smtp As New System.Net.Mail.SmtpClient(SMTPServer, 25)
                    'smtp.EnableSsl = True
                    'smtp.Credentials = New Net.NetworkCredential("u68602919", "xQeCZZQM")
                    smtp.Credentials = New Net.NetworkCredential("noreply@filipstadsbacken.net", "52ZSkgJp")
                    AddToErrorLog(emailAddress, 0, 0, "sending from " & emailSender & " to " & emailAddress & " with SMTPserver " & SMTPServer, "?", 0)
                    smtp.Send(email)
                    AddToErrorLog(emailAddress, 0, 0, "Called SMTPserver " & SMTPServer, "?", 0)
                End If
            Catch ex As System.Exception
                If InStr(ex.Message, "The virus processing server was not available") > 0 Then
                    returnValue = "ERROR in MailPassword: VPS not available"
                Else
                    returnValue = "ERROR in MailPassword: " & ex.ToString
                End If
            End Try
            Return returnValue
        End Function
        Public Function CollectUserData(ByVal session As System.Web.SessionState.HttpSessionState, ByVal request As HttpRequest) As String
            Dim data As New DataUtils
            Dim ds As DataSet
            Dim row As DataRow
            Dim result As String
            Dim host As String
            Dim browser As String
            Dim returnValue As String = ""

            Try
                Dim mobCaps As MobileCapabilities = request.Browser
                Dim renderingType As String = mobCaps.PreferredRenderingType
                Dim bc As HttpBrowserCapabilities = request.Browser
                returnValue = bc.Browser
                ds = data.GetBySQL("select * from UserParam where SessionID = '" + session.SessionID + "'", "UserParam")
                If ds.Tables("UserParam").Rows.Count = 0 Then
                    row = ds.Tables("UserParam").NewRow()
                    row.Item("UserID") = request.UserHostAddress
                    row.Item("Host") = request.UserAgent
                    host = request.UserAgent
                    row.Item("FullPath") = request.PhysicalPath
                    row.Item("Browser") = bc.Browser + bc.Version
                    browser = bc.Browser + bc.Version
                    row.Item("PreferredLanguage") = request.UserLanguages(0)
                    row.Item("JavaScript") = bc.EcmaScriptVersion.ToString
                    row.Item("Platform") = bc.Platform
                    row.Item("PreferredRenderingType") = renderingType
                    row.Item("ActiveX") = bc.ActiveXControls
                    row.Item("Crawler") = bc.Crawler
                    row.Item("Cookies") = bc.Cookies
                    row.Item("Tables") = bc.Tables
                    row.Item("VBScript") = bc.VBScript
                    row.Item("BackgroundSounds") = bc.BackgroundSounds
                    row.Item("DateLogged") = Now
                    row.Item("SessionID") = session.SessionID
                    ds.Tables("UserParam").Rows.Add(row)
                    SearchUpdateKnownHosts(host, browser, renderingType)
                    result = data.UpdateBatch(ds, "UserParam")
                End If
            Catch e1 As SQLServerConfigException
                Throw New Exception("Databasen kan inte adresseras, sannolikt pga konfigurationsfel.<br><br>Teknisk detaljinformation:<br>" & e1.Message)
            Catch e2 As SQLServerLoginException
                Throw New Exception("Inloggning till databasen misslyckades, sannolikt pga konfigurationsfel.<br><br>Teknisk detaljinformation:<br>" & e2.Message)
            Catch e3 As SQLServerAccessException
                Throw New Exception("Databasen kan inte adresseras, sannolikt pga databasservern har stannat.<br><br>Teknisk detaljinformation:<br>" & e3.Message)
            Catch ex As System.Exception
                Throw New Exception("Fel vid CollectUserData: " & ex.Message)
            End Try
            Return returnValue
        End Function
        Public Function GetCalendar(ByVal memberid As Integer) As DataView
            Dim sql As String
            sql = "SELECT c.calendardate, c.activitydescription, c.activitycomment " & _
                "FROM Calendar c " & _
                "INNER JOIN Group_Member gm ON gm.groupid = c.groupid " & _
                "INNER JOIN Member m ON m.memberid = gm.memberid " & _
                "WHERE (m.memberid = " & CStr(memberid) & ")" & _
                "AND c.calendardate >= GETDATE() " & _
                "ORDER BY c.calendardate"
            Dim ds As DataSet = GetBySQL(sql, "calendar")
            If ds.Tables("calendar").Rows.Count > 0 Then
                Dim dw As New DataView(ds.Tables("calendar"))
                Return dw
            Else
                Return Nothing
            End If

        End Function
        Public Function GetCalendar(ByVal emailAddress As String) As DataView
            Dim sql As String
            sql = "SELECT c.calendardate, c.activitydescription, c.activitycomment " & _
                "FROM Calendar c " & _
                "INNER JOIN Group_Member gm ON gm.groupid = c.groupid " & _
                "INNER JOIN Member m ON m.memberid = gm.memberid " & _
                "WHERE (m.MailAddressHome = '" & emailAddress & "' OR m.MailAddressWork = '" & emailAddress & "') " & _
                "AND c.calendardate >= GETDATE() " & _
                "ORDER BY c.calendardate"
            Dim ds As DataSet = GetBySQL(sql, "calendar")
            If ds.Tables("calendar").Rows.Count > 0 Then
                Dim dw As New DataView(ds.Tables("calendar"))
                Return dw
            Else
                Return Nothing
            End If

        End Function
        Private Function GetDefaultHost() As DataTable
            Dim sqlQuery As String
            Dim tb As DataTable
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                sqlQuery = "select * from knownhost where host = 'Default'"
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(data, "defaulthost")
                tb = data.Tables("defaulthost")
            Catch ex As Exception
                Throw New System.Exception("Error in GetDefaultHost: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
            Return tb

        End Function
        Public Function GetDefaultDocumentURL() As String
            Dim sqlQuery As String
            Dim tb As DataTable
            Dim defaultDocumentURL As String = ""
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                sqlQuery = "select defaultdocumenturl from globalconfig where ID = 1"
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(data, "defaultdocumenturl")
                tb = data.Tables("defaultdocumenturl")
                If tb.Rows.Count > 0 Then
                    defaultDocumentURL = tb.Rows(0).Item("defaultdocumenturl").ToString()
                Else
                    Throw New System.Exception("Error in GetDefaultDocumentURL: no document URL found")
                End If
            Catch ex As Exception
                Throw New System.Exception("Error in GetDefaultDocumentURL: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
            Return defaultDocumentURL

        End Function
        Public Function GetDocuments(ByVal groupID As Integer, ByVal listType As String, ByVal noteCategory As String) As DataView
            Dim sql As String
            sql = "SELECT cn.CompositionName, " & _
                    "cr.ComposerName, dp.FileName, dp.Type, dp.URL " & _
                    "FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "FULL OUTER JOIN Composition cn ON cn.CompositionID = cdl.CompositionID " & _
                    "FULL OUTER JOIN Composer cr ON cr.ComposerID = cn.ComposerID " & _
                    "FULL OUTER JOIN DocumentPath dp ON dp.DocumentPathID = cdl.DocumentPathID " & _
                    "WHERE l.ListType = '" & listType & "' " & _
                    "AND l.GroupID = " & CStr(groupID) & " " & _
                    "AND (NOT cn.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
                    "ORDER BY cdl.SortOrder"
            'sql = "SELECT " & _
            '    "c.CompositionName, " & _
            '    "a.ComposerName, " & _
            '    "p.FileName, p.Type, p.URL " & _
            '    "FROM List l " & _
            '    "INNER JOIN List_DocumentList ldl ON ldl.listid = l.listid " & _
            '    "FULL OUTER JOIN DocumentList d ON d.documentlistid = ldl.documentlistid " & _
            '    "FULL OUTER JOIN Composition c ON d.CompositionID = c.CompositionID " & _
            '    "FULL OUTER JOIN Composer a ON c.ComposerID = a.ComposerID " & _
            '    "FULL OUTER JOIN DocumentList_DocumentPath dp ON d.DocumentListID = dp.DocumentListID AND d.GroupID = dp.GroupID " & _
            '    "FULL OUTER JOIN DocumentPath p ON dp.DocumentPathID = p.DocumentPathID " & _
            '    "WHERE (l.groupID = " & CStr(groupID) & ") " & _
            '    "AND l.listtype = '" & listType & "' " & _
            '    "AND (NOT d.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
            '    "ORDER BY ldl.sortorder"
            '"AND (NOT h.HeadingID is NULL OR NOT d.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
            '"h.HeadingText, h.HeadingFontSize, " & _
            '"FULL OUTER JOIN Heading h ON d.HeadingID = h.HeadingID " & _
            Try
                Dim ds As DataSet = GetBySQL(sql, "documentpath")
                If ds.Tables("documentpath").Rows.Count > 0 Then
                    Dim dw As New DataView(ds.Tables("documentpath"))
                    Return dw
                Else
                    Return Nothing
                End If
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in GetDocuments: " & ex.Message)
            End Try
        End Function
        Public Function GetDocuments(ByVal groupID As Integer, ByVal listType As String) As DataView
            Dim sql As String
            sql = "SELECT cdl.CompositionID, cn.CompositionName, " & _
                    "cn.ComposerID, cn.CommentBeforeComposer, cn.CommentAfterComposer, " & _
                    "cr.ComposerName, dp.FileName, dp.Type, dp.URL, cdl.SortOrder " & _
                    "FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "FULL OUTER JOIN Composition cn ON cn.CompositionID = cdl.CompositionID " & _
                    "FULL OUTER JOIN Composer cr ON cr.ComposerID = cn.ComposerID " & _
                    "FULL OUTER JOIN DocumentPath dp ON dp.DocumentPathID = cdl.DocumentPathID " & _
                    "WHERE l.ListType = '" & listType & "' " & _
                    "AND l.GroupID = " & CStr(groupID) & " " & _
                    "AND (NOT cn.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
                    "ORDER BY cdl.SortOrder"
            'sql = "SELECT d.HeadingID, d.CompositionID, " & _
            '    "c.CompositionName, c.ComposerID, c.CommentBeforeComposer, c.CommentAfterComposer, " & _
            '    "a.ComposerName, " & _
            '    "p.FileName, p.Type, p.URL, d.SortOrder " & _
            '    "FROM List l " & _
            '    "INNER JOIN List_DocumentList ldl ON ldl.listid = l.listid " & _
            '    "FULL OUTER JOIN DocumentList d ON d.documentlistid = ldl.documentlistid " & _
            '    "FULL OUTER JOIN Composition c ON d.CompositionID = c.CompositionID " & _
            '    "FULL OUTER JOIN Composer a ON c.ComposerID = a.ComposerID " & _
            '    "FULL OUTER JOIN DocumentList_DocumentPath dp ON d.DocumentListID = dp.DocumentListID AND d.GroupID = dp.GroupID " & _
            '    "FULL OUTER JOIN DocumentPath p ON dp.DocumentPathID = p.DocumentPathID " & _
            '    "WHERE (l.groupID = " & CStr(groupID) & ") " & _
            '    "AND l.listtype = '" & listType & "' " & _
            '    "AND (NOT d.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
            '    "ORDER BY ldl.sortorder"
            '"AND (NOT h.HeadingID is NULL OR NOT d.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
            '"h.HeadingText, h.HeadingFontSize, " & _
            '"FULL OUTER JOIN Heading h ON d.HeadingID = h.HeadingID " & _
            Try
                Dim ds As DataSet = GetBySQL(Sql, "documentpath")
                If ds.Tables("documentpath").Rows.Count > 0 Then
                    Dim dw As New DataView(ds.Tables("documentpath"))
                    Return dw
                Else
                    Return Nothing
                End If
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in GetDocuments: " & ex.Message)
            End Try
        End Function
        Public Function GetDocuments(ByVal emailAddress As String) As DataView
            Dim sql As String
            sql = "SELECT cdl.CompositionID, cn.CompositionName, " & _
                    "cn.ComposerID, cn.CommentBeforeComposer, cn.CommentAfterComposer, " & _
                    "cr.ComposerName, dp.FileName, dp.Type, dp.URL, cdl.SortOrder " & _
                    "FROM Composition_DocumentPath_List cdl " & _
                    "INNER JOIN List l ON l.ListID = cdl.ListID " & _
                    "INNER JOIN Group_Member gm ON gm.GroupID = l.GroupID " & _
                    "INNER JOIN Member m ON m.MemberID = gm.MemberID " & _
                    "FULL OUTER JOIN Composition cn ON cn.CompositionID = cdl.CompositionID " & _
                    "FULL OUTER JOIN Composer cr ON cr.ComposerID = cn.ComposerID " & _
                    "FULL OUTER JOIN DocumentPath dp ON dp.DocumentPathID = cdl.DocumentPathID " & _
                    "WHERE (m.MailAddressHome = '" & emailAddress & "' OR m.MailAddressWork = '" & emailAddress & "') " & _
                    "AND (NOT cn.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
                    "ORDER BY cdl.SortOrder"
            'sql = "SELECT d.HeadingID, d.CompositionID, " & _
            '    "h.HeadingText, h.HeadingFontSize, " & _
            '    "c.CompositionName, c.ComposerID, c.CommentBeforeComposer, c.CommentAfterComposer, " & _
            '    "a.ComposerName, " & _
            '    "p.FileName, p.Type, p.URL, d.SortOrder " & _
            '    "FROM DocumentList d " & _
            '    "INNER JOIN Group_Member gm ON gm.memberid = m.memberid " & _
            '    "INNER JOIN Member m ON d.GroupID = gm.GroupID " & _
            '    "FULL OUTER JOIN Heading h ON d.HeadingID = h.HeadingID " & _
            '    "FULL OUTER JOIN Composition c ON d.CompositionID = c.CompositionID " & _
            '    "FULL OUTER JOIN Composer a ON c.ComposerID = a.ComposerID " & _
            '    "FULL OUTER JOIN DocumentList_DocumentPath dp ON d.DocumentListID = dp.DocumentListID AND d.GroupID = dp.GroupID " & _
            '    "FULL OUTER JOIN DocumentPath p ON dp.DocumentPathID = p.DocumentPathID " & _
            '    "WHERE (m.MailAddressHome = '" & emailAddress & "' OR m.MailAddressWork = '" & emailAddress & "') " & _
            '    "AND d.GroupID = gm.GroupID " & _
            '    "AND (NOT h.HeadingID is NULL OR NOT d.CompositionID IS NULL OR NOT dp.DocumentPathID IS NULL) " & _
            '    "ORDER BY d.sortorder"
            Try
                Dim ds As DataSet = GetBySQL(sql, "documentpath")
                If ds.Tables("documentpath").Rows.Count > 0 Then
                    Dim dw As New DataView(ds.Tables("documentpath"))
                    Return dw
                Else
                    Return Nothing
                End If
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in GetDocuments: " & ex.Message)
            End Try
        End Function
        Public Function GetGlobalConfig() As DataTable
            Dim sqlQuery As String
            Dim tb As DataTable
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                sqlQuery = "select * from globalconfig where id = " + CStr(1)
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(data, "globalconfig")
                If data.Tables("globalconfig").Rows.Count > 0 Then
                    tb = data.Tables("globalconfig")
                Else
                    Throw New System.Exception("no global config found.")
                End If
            Catch ex As System.Exception
                Throw New System.Exception("Error in GetGlobalConfig: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
            Return tb
        End Function
        Public Function GetGroupsForLoginUser(ByVal memberid As Integer) As ArrayList
            Dim returnArray As New ArrayList()
            Dim sql As String
            sql = "SELECT gm.GroupID, g.GroupName, g.GroupStartPageLYB2 " & _
                "FROM Member AS m " & _
                "INNER JOIN Group_Member gm ON gm.memberid = m.memberid " & _
                "INNER JOIN [Group] AS g ON g.GroupID = gm.GroupID " & _
                "WHERE (m.memberid = " & CStr(memberid) & ") " & _
                "ORDER BY g.GroupName"
            Try
                Dim ds As DataSet = GetBySQL(sql, "groupid")
                For Each row As DataRow In ds.Tables("groupid").Rows
                    returnArray.Add(row.Item("groupid"))
                    returnArray.Add(row.Item("groupname"))
                    returnArray.Add(row.Item("groupstartpageLYB2"))
                Next
                Return returnArray
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in GetGroupsForLoginUser: " & ex.Message)
            End Try

        End Function
        Public Function GetGroupsForLoginUser(ByVal emailAddress As String) As ArrayList
            Dim returnArray As New ArrayList()
            Dim sql As String
            sql = "SELECT gm.GroupID, g.GroupName, g.GroupStartPageLYB2 " & _
                "FROM Member AS m " & _
                "INNER JOIN Group_Member gm ON gm.memberid = m.memberid " & _
                "INNER JOIN [Group] AS g ON g.GroupID = gm.GroupID " & _
                "WHERE (m.MailAddressHome = '" & emailAddress & "' OR m.MailAddressWork = '" & emailAddress & "') " & _
                "ORDER BY g.GroupName"
            Try
                Dim ds As DataSet = GetBySQL(sql, "groupid")
                For Each row As DataRow In ds.Tables("groupid").Rows
                    returnArray.Add(row.Item("groupid"))
                    returnArray.Add(row.Item("groupname"))
                    returnArray.Add(row.Item("groupstartpageLYB2"))
                Next
                Return returnArray
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in GetGroupsForLoginUser: " & ex.Message)
            End Try

        End Function
        Public Function GetKnownHosts(ByVal host As String) As DataTable
            Dim sqlQuery As String
            Dim tb As DataTable
            Try
                conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)
                sqlQuery = "select * from knownhost where host = '" + host + "'"
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(data, "knownhost")
                If data.Tables("knownhost").Rows.Count > 0 Then
                    tb = data.Tables("knownhost")
                    'If tb.Rows(0).Item("pixelsHeight") Is System.DBNull.Value _
                    '    Or tb.Rows(0).Item("pixelsWidth") Is System.DBNull.Value Then
                    '    tb.Clear()
                    '    tb = GetDefaultHost()
                    'End If
                Else
                    tb = GetDefaultHost()
                End If
            Catch ex As Exception
                Throw New System.Exception("Error in GetKnownHosts: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
            Return tb

        End Function
        Public Function GetLinks(ByVal emailAddress As String) As DataView
            Dim groupArray As ArrayList = GetGroupsForLoginUser(emailAddress)
            Dim sql As String
            sql = "SELECT l.linktext, l.linkurl, l.linkstyle " & _
                "FROM linklist l " & _
                "WHERE l.groupid = " & CStr(groupArray(0)) & " " & _
                "ORDER BY l.sortorder"
            Dim ds As DataSet = GetBySQL(sql, "link")
            If ds.Tables("link").Rows.Count > 0 Then
                Dim dw As New DataView(ds.Tables("link"))
                Return dw
            Else
                Return Nothing
            End If

        End Function
        Private Sub GetMailParam(ByRef SMTPServer As String, ByRef emailSender As String)
            Dim data As New DataUtils
            Dim sql As String
            sql = "SELECT SMTPServer, emailSender FROM GlobalConfig WHERE ID = 1"
            Try
                Dim ds As DataSet = GetBySQL(sql, "config")
                If ds.Tables("config").Rows.Count = 1 Then
                    SMTPServer = ds.Tables("config").Rows(0).Item("smtpserver")
                    emailSender = ds.Tables("config").Rows(0).Item("emailSender")
                Else
                    SMTPServer = "ERROR: no datarow"
                End If
            Catch ex As System.Exception
                SMTPServer = "ERROR in GetMailParam: " & ex.ToString
            End Try
        End Sub
        Public Function RetrievePageData(ByVal session As System.Web.SessionState.HttpSessionState, ByVal pageID As String) As DataTable
            Dim ds As DataSet
            Dim language As String
            Try
                language = RetrieveUserData(session, "PreferredLanguage")
                If language.Length > 2 Then
                    If InStr(language, "en") > 0 Then
                        language = "en"
                    ElseIf InStr(language, "sv") > 0 Then
                        language = "sv"
                    Else
                        language = "en"
                    End If
                End If
                ds = GetBySQL("select * from TextLibrary where PageID = '" + pageID + "' and languagecode = '" + language + "'", "PageData" + pageID)
                Return ds.Tables("PageData" + pageID)
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in RetrievePageData : " + ex.ToString)
            End Try

        End Function
        Public Function RetrievePictureData(ByVal pictureGroup As Integer) As DataTable
            Dim ds As DataSet
            Try
                ds = GetBySQL("select * from DynamicPicture where PictureGroup = " + CStr(pictureGroup), "DynamicPicture")
                Return ds.Tables("DynamicPicture")
            Catch ex As System.Exception
                Throw New System.Exception("ERROR in RetrievePictureData : " + ex.ToString)
            End Try

        End Function
        Public Function RetrieveUserData(ByVal session As System.Web.SessionState.HttpSessionState, ByVal userProperty As String) As Object
            Dim ds As DataSet
            Try
                ds = GetBySQL("select " + userProperty + " from UserParam where SessionID = '" + session.SessionID + "'", "UserData")
                If ds.Tables(0).Rows.Count > 0 Then
                    Return ds.Tables("UserData").Rows(0).Item(userProperty)
                Else
                    Return "ERROR: No user data"
                End If
            Catch ex As System.Exception
                Return "ERROR: " + ex.Message
            End Try
        End Function
        Public Sub AddToErrorLog(ByVal emailAddress As String, ByVal memberID As Integer, ByVal groupID As Integer, ByVal errorDescription As String, ByVal sessionID As String, ByVal severity As Integer)
            Dim data As New DataUtils
            Dim ds As New DataSet
            Dim sqlQuery As String
            'Dim count As Integer
            Dim row As DataRow
            Dim result As String

            conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)

            sqlQuery = "select * from errorlog where errorlogid = " + CStr("0") ' get empty dataset
            Try
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(ds, "errorlog")
                If ds.Tables("errorlog").Rows.Count = 0 Then
                    row = ds.Tables("errorlog").NewRow()
                    row.Item("memberID") = memberID
                    row.Item("groupID") = groupID
                    row.Item("mailaddressused") = emailAddress
                    row.Item("errordescription") = errorDescription
                    row.Item("DateLogged") = Now
                    row.Item("SessionID") = sessionID
                    row.Item("severity") = severity
                    ds.Tables("errorlog").Rows.Add(row)
                    result = data.UpdateBatch(ds, "errorlog")
                End If
            Catch ex As Exception
                Throw New System.Exception("Error in AddToErrorLog: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try

        End Sub
        Public Sub SaveUserData(ByVal session As System.Web.SessionState.HttpSessionState, ByVal userName As String, ByVal userPhone As String)
            Dim data As New DataUtils
            Dim ds As New DataSet
            Dim sqlQuery As String
            'Dim count As Integer
            Dim row As DataRow
            Dim result As String
            conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)

            sqlQuery = "select * from userparam where SessionID = '" + session.SessionID + "'"

            Try
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(ds, "userparam")
                If ds.Tables("userparam").Rows.Count = 1 Then
                    row = ds.Tables("userparam").Rows(0)
                    row.Item("UserName") = userName
                    row.Item("UserPhone") = userPhone
                    result = data.UpdateBatch(ds, "userparam")
                End If
            Catch ex As Exception
                Throw New System.Exception("Error in SaveUserData: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
        End Sub
        Private Sub SearchUpdateKnownHosts(ByVal host As String, ByVal browser As String, ByVal renderingType As String)
            Dim data As New DataUtils
            Dim ds As New DataSet
            Dim sqlQuery As String
            'Dim count As Integer
            Dim row As DataRow
            Dim result As String

            conn = New SqlConnection(ConfigurationManager.ConnectionStrings(connectionstringName).ToString)

            sqlQuery = "select * from knownhost where host = '" + host + "'"
            Try
                adapter.SelectCommand = New SqlCommand(sqlQuery, conn)
                adapter.SelectCommand.CommandType = CommandType.Text
                adapter.Fill(ds, "knownhost")
                If ds.Tables("knownhost").Rows.Count = 0 Then
                    row = ds.Tables("knownhost").NewRow()
                    row.Item("Browser") = browser
                    row.Item("PreferredRenderingType") = renderingType
                    row.Item("Host") = host
                    row.Item("DateLogged") = Now
                    ds.Tables("knownhost").Rows.Add(row)
                    result = data.UpdateBatch(ds, "knownhost")
                End If
            Catch ex As Exception
                Throw New System.Exception("Error in SearchUpdateKnownHosts: " + ex.Message().ToString)
            Finally
                conn.Close()
            End Try
        End Sub
        Public Function SetPanelBorderColor(ByVal target As System.Web.UI.WebControls.Panel, ByVal infoCategory As Short) As System.Web.UI.WebControls.Panel
            Select Case infoCategory
                Case 1      ' info
                    target.BorderColor = System.Drawing.Color.Green
                Case 2      ' warning
                    target.BorderColor = System.Drawing.Color.Yellow
                Case 3      ' error
                    target.BorderColor = System.Drawing.Color.Red
                Case Else
            End Select
            target.BorderWidth = 3
            target.BorderStyle = BorderStyle.Solid
            Return target
        End Function

        Public Sub New()

        End Sub
    End Class
End Namespace