Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient


Public Class functions

    Public Shared ProfilePicturePath As String = "../assets/images/avatars/"
    Public Shared ProfilePictureUnknown As String = "unknown-profile.jpg"
    Public Shared AvatarUploadPath As String = "../avatar/uploads/"


    Public Shared Function GetConnectionString() As String
        Return ConfigurationManager.ConnectionStrings("Pernod_ConnectionString").ConnectionString
    End Function

    Public Shared Function GetAppSettings(key As String) As String
        Return ConfigurationManager.AppSettings(key)
    End Function

    Public Shared Function CheckNull(obj As Object, defaultvalue As Object)
        If IsNothing(obj) Then
            Return defaultvalue
        Else
            Return obj
        End If
    End Function

    Public Shared Function CheckDBNull(obj As Object, defaultvalue As Object)
        If IsDBNull(obj) Then
            Return defaultvalue
        Else
            Return obj
        End If
    End Function

    Public Shared Function ToDateString(obj As Object, format As String) As String
        If IsDBNull(obj) Then
            Return ""
        ElseIf IsDate(obj) Then
            Return CDate(obj).ToString(format)
        Else
            Return ""
        End If
    End Function

    Public Shared Function JSON_write_boolean_response(value As Boolean, message As String, Optional data As DataTable = Nothing) As DataTable
        Dim dt As New DataTable

        dt.Columns.Add("result")
        dt.Columns.Add("message")
        dt.Columns.Add("data")

        Dim dr As DataRow = dt.NewRow

        dr("result") = value
        dr("message") = message
        If Not data Is Nothing Then
            dr("data") = Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented)
        Else
            dr("data") = ""
        End If

        dt.Rows.Add(dr)

        Return dt

    End Function

    Public Shared Function JSON_write_boolean_response2(value As Boolean, message As String, Optional data As DataTable = Nothing, Optional data2 As DataTable = Nothing) As DataTable
        Dim dt As New DataTable

        dt.Columns.Add("result")
        dt.Columns.Add("message")
        dt.Columns.Add("data")
        dt.Columns.Add("data2")

        Dim dr As DataRow = dt.NewRow

        dr("result") = value
        dr("message") = message
        If Not data Is Nothing Then
            dr("data") = Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented)
        Else
            dr("data") = ""
        End If
        'dt.Rows.Add(dr)
        If Not data2 Is Nothing Then
            dr("data2") = Newtonsoft.Json.JsonConvert.SerializeObject(data2, Newtonsoft.Json.Formatting.Indented)
        Else
            dr("data2") = ""
        End If

        dt.Rows.Add(dr)

        Return dt

    End Function

    Public Shared Function Write_JSON(value As Boolean, message As String, Optional data As DataTable = Nothing) As String
        Dim dt As New DataTable

        dt.Columns.Add("result")
        dt.Columns.Add("message")
        dt.Columns.Add("data")

        Dim dr As DataRow = dt.NewRow

        dr("result") = value
        dr("message") = message
        If Not data Is Nothing Then
            dr("data") = Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented)
        Else
            dr("data") = ""
        End If

        dt.Rows.Add(dr)

        Return Newtonsoft.Json.JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented)

    End Function

    Public Shared Function Write_JSON_Datatable(data As DataTable) As String

        Return Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented)

    End Function

    Public Class Parameter
        Public Name As String
        Public Value As Object
        Public Sub New(ParName As String, ParValue As Object)
            Name = ParName
            Value = ParValue
        End Sub
    End Class
    Public Shared Function SP_SELECT(STORE_PROCEDURE As String, Parameters As List(Of Parameter)) As DataTable
        'Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@PK", PK)}
        Try
            Dim dt As New DataTable
            Using con As SqlConnection = New SqlConnection(GetConnectionString)
                Using cmd As SqlCommand = New SqlCommand(STORE_PROCEDURE, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    If Not IsNothing(Parameters) Then
                        For Each par In Parameters
                            cmd.Parameters.AddWithValue(par.Name, par.Value)
                        Next
                    End If

                    If con.State = ConnectionState.Closed Then con.Open()

                    Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                        da.Fill(dt)

                    End Using
                End Using
            End Using
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Shared Function SP_SELECT_DS(STORE_PROCEDURE As String, Parameters As List(Of Parameter)) As DataSet
        'Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@PK", PK)}
        Try
            Dim dt As New DataSet
            Using con As SqlConnection = New SqlConnection(GetConnectionString)
                Using cmd As SqlCommand = New SqlCommand(STORE_PROCEDURE, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    For Each par In Parameters
                        cmd.Parameters.AddWithValue(par.Name, par.Value)
                    Next
                    If con.State = ConnectionState.Closed Then con.Open()

                    Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                        da.Fill(dt)

                    End Using
                End Using
            End Using
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Shared Function SP_UPDATE(STORE_PROCEDURE As String, Parameters As List(Of Parameter)) As Long
        Try
            Dim dt As New DataTable
            Using con As SqlConnection = New SqlConnection(GetConnectionString)
                Using cmd As SqlCommand = New SqlCommand(STORE_PROCEDURE, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    For Each par In Parameters
                        cmd.Parameters.AddWithValue(par.Name, par.Value)
                    Next
                    If con.State = ConnectionState.Closed Then con.Open()

                    Return cmd.ExecuteNonQuery
                End Using
            End Using
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function SP_INSERT(STORE_PROCEDURE As String, Parameters As List(Of Parameter)) As Long
        Try
            Dim dt As New DataTable
            Using con As SqlConnection = New SqlConnection(GetConnectionString)
                Using cmd As SqlCommand = New SqlCommand(STORE_PROCEDURE, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    For Each par In Parameters
                        cmd.Parameters.AddWithValue(par.Name, par.Value)
                    Next
                    If con.State = ConnectionState.Closed Then con.Open()

                    Return cmd.ExecuteScalar
                End Using
            End Using
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Shared Function Options_Event_Type(Optional AddNewOption As Boolean = False) As String
        Dim str_HTML As String = ""
        'Dim pars As List(Of functions.Parameter) = New List(Of functions.Parameter) From {New functions.Parameter("@VAR", VALUE)}

        Dim dt As DataTable = functions.SP_SELECT("ROADSHOW_spSELECT", Nothing)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                Dim EVENT_TYPE_PK As Integer = GetEVENT_TYPE_PK()
                For Each dr As DataRow In dt.Rows
                    str_HTML &= "<option " & If(EVENT_TYPE_PK = dr("ROADSHOW_PK"), "selected", "") & " value='" & dr("ROADSHOW_PK").ToString & "'>" & dr("ROADSHOW_NAME").ToString & "</option>"
                Next
            End If
        End If

        If AddNewOption Then
            str_HTML &= "<b><option " & " value='-1'>" & "Add New Roadshow..." & "</option></b>"
        End If

        Return str_HTML


        'Using con As SqlConnection = New SqlConnection(GetConnectionString)
        '    Dim sql As String = "EVENT_TYPE_spSELECT"
        '    Using cmd As SqlCommand = New SqlCommand(sql, con)
        '        cmd.CommandType = CommandType.StoredProcedure

        '        If con.State = ConnectionState.Closed Then con.Open()
        '        Dim dt As New DataTable
        '        Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
        '            da.Fill(dt)
        '            If Not dt Is Nothing Then
        '                If dt.Rows.Count > 0 Then
        '                    Dim EVENT_TYPE_PK As Integer = GetEVENT_TYPE_PK()
        '                    For Each dr As DataRow In dt.Rows
        '                        str_HTML &= "<option " & If(EVENT_TYPE_PK = dr("EVENT_TYPE_PK"), "selected", "") & " value='" & dr("EVENT_TYPE_PK").ToString & "'>" & dr("EVENT_TYPE").ToString & "</option>"
        '                    Next
        '                End If
        '            End If
        '        End Using
        '    End Using
        'End Using

    End Function


    Private Shared Function GetEVENT_TYPE_PK() As Integer
        If IsNothing(HttpContext.Current.Session("EVENT_PK")) Then
            Return 0
        End If
        Dim value As Integer = 0
        Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@EVENT_PK", HttpContext.Current.Session("EVENT_PK"))}
        Dim dt As DataTable = SP_SELECT("EVENTS_spSELECT", pars)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                value = dt.Rows(0)("EVENT_TYPE_FK")
            End If
        End If
        Return value
    End Function

    Public Shared Function Options_Locations(Optional LOCATION_PK As Integer = 0) As String
        Dim str_HTML As String = ""
        Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@EVENT_PK", HttpContext.Current.Session("EVENT_PK"))}
        Dim dt As DataTable = SP_SELECT("LOCATIONS_spSELECT", pars)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                For Each dr As DataRow In dt.Rows
                    str_HTML &= "<option " & If(LOCATION_PK = dr("LOCATION_PK"), "selected", "") & " value='" & dr("LOCATION_PK").ToString & "'>" & dr("LOCATION_NAME").ToString & "</option>"
                Next
            End If
        End If

        Return str_HTML
    End Function

    Public Shared Function Options_Rooms(LOCATION_FK As Integer, Optional ROOM_PK As Integer = 0) As String
        Dim str_HTML As String = "<option value='0'>-Select Room-</option>"
        Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@LOCATION_FK", LOCATION_FK)}
        Dim dt As DataTable = SP_SELECT("ROOMS_spSELECT", pars)
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then

                For Each dr As DataRow In dt.Rows
                    str_HTML &= "<option " & If(ROOM_PK = dr("ROOM_PK"), "selected", "") & " value='" & dr("ROOM_PK").ToString & "'>" & dr("ROOM_NAME").ToString & "</option>"
                Next
            End If
        End If

        Return str_HTML
    End Function

    Public Shared Function Options_Private_Event() As String
        Dim str_HTML As String = ""
        Dim pars As List(Of Parameter) = New List(Of Parameter) From {New Parameter("@EVENT_PK", HttpContext.Current.Session("EVENT_PK"))}
        Dim dt As DataTable = SP_SELECT("EVENTS_spSELECT", pars)
        Dim Priv As Boolean = True
        If Not dt Is Nothing AndAlso dt.Rows.Count > 0 Then
            If IsDBNull(dt.Rows(0)("PRIVATE_EVENT")) OrElse Not dt.Rows(0)("PRIVATE_EVENT") Then
                Priv = False
            End If
        End If
        str_HTML &= "<option value='0'>Public</option>"
        str_HTML &= "<option " & If(Priv, "selected", "") & " value='1'>Private</option>"
        Return str_HTML
    End Function

    Public Shared Function Table_Events_Upcoming() As String
        If IsNothing(HttpContext.Current.Session("COMPANY_PK")) Then
            Return ""
        End If
        Dim str_HTML As String = ""
        Using con As SqlConnection = New SqlConnection(functions.GetConnectionString)

            Using cmd As SqlCommand = New SqlCommand("spSELECT_MyEvents_Upcoming", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@COMPANY_FK", HttpContext.Current.Session("COMPANY_PK"))
                If con.State = ConnectionState.Closed Then con.Open()
                Dim dt As New DataTable
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    da.Fill(dt)
                    If Not dt Is Nothing Then
                        If dt.Rows.Count > 0 Then
                            Dim EventType As String = ""
                            For Each dr As DataRow In dt.Rows
                                If IsDBNull(dr("PRIVATE_EVENT")) OrElse Not dr("PRIVATE_EVENT") Then
                                    EventType = "Public " & dr("EVENT_TYPE").ToString
                                Else
                                    EventType = "Private " & dr("EVENT_TYPE").ToString
                                End If
                                str_HTML &= "<tr data-pk='" & dr("EVENT_PK").ToString() & "'>"
                                str_HTML &= " <td scope='row' style='white-space:nowrap'>" & dr("EVENT_NAME").ToString() & "</td>"
                                str_HTML &= " <td style='white-space:nowrap'>" & EventType & "</td>"
                                str_HTML &= " <td style='white-space:nowrap'>" & dr("VENUE_NAME").ToString() & "</td>"
                                str_HTML &= " <td style='white-space:nowrap'>" & ToDateString(dr("START_DATE"), "yyyy-MM-dd HH:mm") & "</td>"
                                str_HTML &= " <td style='white-space:nowrap'>" & ToDateString(dr("END_DATE"), "yyyy-MM-dd HH:mm") & "</td>"
                                str_HTML &= " <td style='white-space:nowrap; text-align:center;' class='tdEdit'><a class='edit' href='#'><i class='fa fa-pencil'></i> Edit</a></td>"
                                str_HTML &= " <td style='white-space:nowrap; text-align:center;' class='tdView'><a class='view' href='#'><i class='fa fa-eye'></i> View</a></td>"
                                str_HTML &= "</tr>"
                            Next
                        End If
                    End If
                End Using
            End Using
        End Using
        Return str_HTML
    End Function

    Public Shared Sub ClearSessions()
        With HttpContext.Current
            .Session("userid") = Nothing
            .Session("DisplayName") = Nothing
            .Session("DSVAccount") = Nothing
            .Session("DSVAccountStatus") = Nothing
        End With
    End Sub

    Public Shared Sub ClearAdminSessions()
        With HttpContext.Current
            For i As Integer = 0 To HttpContext.Current.Session.Count - 1
                If .Session.Keys(i).StartsWith("ADMIN.") Then .Session(i) = Nothing
            Next
        End With
    End Sub

    Public Shared Function CompareDates(ByVal sSmallDate As String, ByVal sBigDate As String) As Boolean

        Dim SmallDate As Date = Date.Parse(sSmallDate)
        Dim BigDate As Date = Date.Parse(sBigDate)

        If SmallDate < BigDate Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Shared Function Today(Optional AddDays As Integer = 0, Optional ReturnDateTime As Boolean = False) As String





        If ReturnDateTime Then

            Return DateTime.Now.ToString
        Else
            Dim aDate As New DateTime
            aDate = DateTime.Now

            aDate.AddDays(AddDays)
            Return aDate.Year.ToString & "-" & aDate.Month.ToString.PadLeft(2, "0") & "-" & aDate.Day.ToString.PadLeft(2, "0")
        End If
    End Function

End Class