Imports Microsoft.VisualBasic
Imports System.Web.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Globalization
Imports RGBC_Forecast.SystemFunctions
Imports RGBC_Forecast.ProjectFunctions
Imports RGBC_Forecast

'my bd class. 
'phase 1: provides basic db conn & sql
'phase 2: advance db support

Public Class db

  ' Connection string: config first if non-empty, else env (SecureConfig). No Web.config write needed.
  Public Shared Function GetConnectionString() As String
    Dim cs As ConnectionStringSettings = WebConfigurationManager.ConnectionStrings("SuperbowlConnectionString")
    If cs IsNot Nothing AndAlso Not String.IsNullOrEmpty(cs.ConnectionString) Then
      Return cs.ConnectionString
    End If
    Return Global.RGBC_Forecast.RGBC_Forecast.SecureConfig.GetConnectionString("SuperbowlConnectionString")
  End Function

  ' Lazy-initialized shared connection (uses GetConnectionString so works without Web.config write).
  Private Shared _conn As SqlConnection = Nothing
  Public Shared ReadOnly Property conn As SqlConnection
    Get
      If _conn Is Nothing Then
        _conn = New SqlConnection(GetConnectionString())
      End If
      Return _conn
    End Get
  End Property

    Public Shared intRows As Integer = 0
    Public Shared strExecutionLog As String = ""
    Public Shared Debug As Boolean = False

    'GENERIC FUNCTIONS
    Public Shared Function nz(ByVal v As Object, ByVal d As Object) As Object
        ' pass in the value to check as v and the default as d
        ' d will be returned if v is null or empty

        If v Is Nothing Then
            Return d
        End If

        If v.Equals(DBNull.Value) Then
            Return d
        End If

        Try
            If CStr(v) = "" Then Return d
        Catch ex As Exception

        End Try

        Try
            If CDbl(v) = 0 Then Return d
        Catch ex As Exception

        End Try
        Return v

    End Function

    Public Shared Function CleanString(ByVal strUnClean As String) As String
        Dim strClean As String
        'strClean = strUnClean.Replace(Chr(13), "'r")
        'strClean = strUnClean.Replace("'", "''")
        strUnClean = CStr(nz(strUnClean, ""))
        strClean = strUnClean.Replace(Chr(34), "'") 'replace " with '
        strClean = strClean.Replace(Chr(39), "''") ' replace ' with ''
        Return strClean.Trim
    End Function

    Public Shared Function doQuery(ByVal strSQL As String) As Object
        Dim adp As New SqlDataAdapter(strSQL, conn)
        Dim ds As New DataSet("QUERY")

        If strSQL.Trim.ToUpper.IndexOf("SELECT") = 0 Then
            Try
                adp.Fill(ds)
                intRows = ds.Tables(0).Rows.Count

                Return ds
            Catch ioorEx As IndexOutOfRangeException
                'expected for intRows = ds.Tables(0).Rows.Count if not an SELECT statement
                Return ds
            Catch sqlEx As SqlException
                If sqlEx.ErrorCode = -2146232060 Then
                    Return ds
                Else
                    Return ds
                End If
            Catch ex As Exception

                My.Response.Write(strSQL & "<BR/>" & ex.Message)

                adp.Fill(ds)
                intRows = ds.Tables(0).Rows.Count

                Return ds
                Return Nothing
            End Try
        Else
            Dim sqlcmd As New SqlCommand(strSQL, conn)
            Try
                If conn.State = ConnectionState.Open Then conn.Close() 'lame
            Catch ex As Exception
            End Try

            Try
                conn.Open()
                intRows = sqlcmd.ExecuteNonQuery()
                conn.Close()
                Return intRows
            Catch ex As Exception
                My.Response.Write("<BR><BR>" & strSQL & "<BR/>" & ex.Message)
                Return Nothing
            Finally
                conn.Close()
            End Try
        End If


    End Function

    Public Shared Function getRow(ByVal strSQL As String, Optional ByVal overrideDebug As Boolean = False) As DataRow

        Try
            'If overrideDebug = True Then System.Web.HttpResponse.Write(strSQL) 'response.write(strSQL)
            Return doQuery(strSQL).Tables(0).Rows(0)
        Catch ex As Exception

            Return Nothing
        End Try

    End Function

    Public Shared Function countRows(ByVal strSQL As String) As Integer

        Try
            Return doQuery(strSQL).Tables(0).Rows.Count

        Catch ex As Exception
            Return 0
        End Try

    End Function

    Public Shared Function MergeDataSets(ByVal dsInput As DataSet, Optional ByVal key As Object = "") As DataTable
        Dim ds As New DataSet

        Dim arrData(0, dsInput.Tables(0).Columns.Count - 1) As String
        Dim arrMOFO() As String
        Dim keys(0) As String

        If key = "" Then 'use index=0
            key = CInt(0)
        End If

        'build(columns)
        ds.Tables.Add(dsInput.Tables(0).Clone())


        'scan keys
        For idxRow As Integer = 0 To dsInput.Tables(0).Rows.Count - 1
            Try
                If Array.IndexOf(keys, CStr(dsInput.Tables(0).Rows(idxRow).Item(key))) >= 0 Then

                Else
                    keys(keys.Count - 1) = CStr(dsInput.Tables(0).Rows(idxRow).Item(key))
                    ReDim Preserve keys(keys.Count)
                End If
            Catch ex As Exception

            End Try
        Next
        If keys(keys.Count - 1) = Nothing Then ReDim Preserve keys(keys.Count - 2)

        ReDim arrData(keys.Count - 1, dsInput.Tables(0).Columns.Count - 1)
        ReDim arrMOFO(dsInput.Tables(0).Columns.Count - 1)

        'unify rows
        For idxRow As Integer = 0 To dsInput.Tables(0).Rows.Count - 1
            For idxCol As Integer = 0 To ds.Tables(0).Columns.Count - 1
                Try
                    If CStr(nz(dsInput.Tables(0).Rows(idxRow).Item(idxCol), "")) = "" Then
                        'do nothing
                    Else
                        arrData(Array.IndexOf(keys, CStr(dsInput.Tables(0).Rows(idxRow).Item(key))), idxCol) = dsInput.Tables(0).Rows(idxRow).Item(idxCol)
                    End If
                Catch ex As Exception

                End Try
            Next
        Next

        'add motherf*cking rows to datatable 'what a f*cking stupid language! vb sucks!
        Dim dr As DataRow

        For idxRow As Integer = 0 To keys.Count - 1
            dr = ds.Tables(0).NewRow

            For idxCol As Integer = 0 To ds.Tables(0).Columns.Count - 1

                If ds.Tables(0).Columns(idxCol).ColumnName = "#" Then arrData(idxRow, idxCol) = CStr(idxRow + 1)

                If ds.Tables(0).Columns(idxCol).DataType.Name = "String" Then
                    'arrMOFO(idxCol) = nz(arrData(idxRow, idxCol), "")
                    dr.Item(idxCol) = nz(arrData(idxRow, idxCol), "")
                Else
                    'arrMOFO(idxCol) = nz(arrData(idxRow, idxCol), 0.0)
                    dr.Item(idxCol) = nz(arrData(idxRow, idxCol), 0.0)
                End If
            Next

            'ds.Tables(0).Rows.Add(arrMOFO)
            ds.Tables(0).Rows.Add(dr)

        Next


        Return ds.Tables(0).Copy()

    End Function

    'ASYNC TESTS
    Public Shared Sub doQueryAsync(ByVal strSQL As String, Optional ByVal blnGooi As Boolean = True)
        Dim conn As New SqlConnection(db.conn.ConnectionString & "Asynchronous Processing=true;")
        'Dim tStart, tEnd As TimeSpan

        If strSQL.ToUpper.IndexOf("SELECT") = 0 Then
            Try
                Dim adp As New SqlDataAdapter(strSQL, conn)
                Dim ds As New DataSet

            Catch
            End Try

        Else
            Dim myCommand As New SqlCommand(strSQL, conn)
            Dim intRows As Integer = 0



            'start
            Try
                'tStart = New TimeSpan(Date.Now.Ticks)
                conn.Open()
                Dim myResult As IAsyncResult = myCommand.BeginExecuteNonQuery()
                'strExecutionLog &= "<BR>\nExcute query [" & tStart.ToString() & "] in progress"
                While Not myResult.IsCompleted
                    If blnGooi Then Return
                    Threading.Thread.Sleep(10)
                    'strExecutionLog &= "."
                End While
                'tEnd = New TimeSpan(Date.Now.Ticks)
                intRows = myCommand.EndExecuteNonQuery(myResult)

                'strExecutionLog &= "<BR>\nOperation complete [" & tStart.ToString() & "][" & tEnd.ToString() & "]. Rows Affected: " & intRows

            Catch ex As Exception
                My.Response.Write("<BR/>" & strSQL & "<BR/>" & ex.Message)
                'strExecutionLog &= "<BR>\nError during execution: " & ex.Message & ex.StackTrace

            Finally
                conn.Close()
            End Try
        End If

    End Sub

    Public Shared Function CleanData(ByVal dr As DataRow) As DataRow

        For i = 0 To dr.ItemArray.Count - 1
            dr(i) = nz(dr(i), 0.0)
        Next

        Return dr
    End Function

    Public Shared Function CleanData(ByVal dt As DataTable) As DataTable

        For Each dr As DataRow In dt.Rows
            dr = CleanData(dr)
        Next

        Return dt.Copy
    End Function

    Public Shared Function doQueryXTab(ByVal sql As String, ByVal rowHeading As Collection, ByVal columnHeading As String, ByVal value As Collection, Optional ByVal rowHeadingAlias As Collection = Nothing) As DataSet
        Dim ds, dsMerge As DataSet
        Dim dtTemplate As DataTable
        Dim drNew As DataRow = Nothing
        Dim arrHeadings As New Collection
        Dim sqlNew, sqlFrom, sqlGroupBy, comma As String
        Dim lastKey As String = ""
        Dim currentKey As String
        'notes: 
        'value(1) = "SUM(dwfSales.dbl9lTotal)"
        'value(2) = "dbl9L"

        'build headings
        ds = db.doQuery(sql)
    For Each dr In ds.Tables(0).Rows
      Try
        arrHeadings.Add(CStr(dr.item(columnHeading)), CStr(dr.item(columnHeading)))
      Catch ex As Exception
      End Try
    Next

    'recomp sql
    sqlNew = "SELECT "
    sqlGroupBy = ""
    comma = ""
    For i = 1 To rowHeading.Count
      Try
        sqlNew &= comma & rowHeadingAlias(rowHeading(i)) & " AS '" & rowHeading(i) & "'"
        If Not rowHeading(i).ToString.Contains("#") Then sqlGroupBy &= comma & rowHeadingAlias(rowHeading(i))
      Catch ex As Exception 'arg/indexooRange Ex expected for rowHeadingAlias(rowHeading(i))
        sqlNew &= comma & rowHeading(i)
        If Not rowHeading(i).ToString.Contains("#") Then sqlGroupBy &= comma & rowHeading(i)
      Finally
        comma = ","
      End Try
    Next
    For i = 1 To arrHeadings.Count
      sqlNew &= comma & " " & value.Item(1) & " AS '" & arrHeadings(i) & "'"
      comma = ","
    Next

    'isolate FROM
    sqlFrom = " " & sql.Substring(sql.ToUpper.IndexOf("FROM"))
    If sql.ToUpper.IndexOf("ORDER BY ") > 0 Then sqlFrom = " " & sql.Substring(sql.ToUpper.IndexOf("FROM"), sql.ToUpper.IndexOf("ORDER BY ") - sql.ToUpper.IndexOf("FROM"))
    If sql.ToUpper.IndexOf("GROUP BY ") > 0 Then sqlFrom = " " & sql.Substring(sql.ToUpper.IndexOf("FROM"), sql.ToUpper.IndexOf("GROUP BY ") - sql.ToUpper.IndexOf("FROM"))
    If sql.ToUpper.IndexOf("WHERE ") > 0 Then sqlFrom = " " & sql.Substring(sql.ToUpper.IndexOf("FROM"), sql.ToUpper.IndexOf("WHERE ") - sql.ToUpper.IndexOf("FROM"))
    If sqlGroupBy.IndexOf(",") = 0 Then sqlGroupBy = sqlGroupBy.Substring(1)
    'make template dt
    sqlNew = sqlNew & sqlFrom & " WHERE 1=0 GROUP BY " & sqlGroupBy
    dsMerge = db.doQuery(sqlNew)
    dtTemplate = dsMerge.Tables(0)

    For Each dr In ds.Tables(0).Rows
      Try
        currentKey = ""
        For i = 1 To rowHeading.Count
          currentKey &= dr.item(rowHeading(i))
        Next

        If lastKey <> currentKey Then
          If Not drNew Is Nothing Then dsMerge.Tables(0).Rows.Add(db.CleanData(drNew))

          drNew = dtTemplate.NewRow
          lastKey = ""
          For i = 1 To rowHeading.Count
            drNew.Item(rowHeading(i)) = dr.item(rowHeading(i))
            lastKey &= dr.item(rowHeading(i))
          Next
        End If

        drNew.Item(arrHeadings(dr.item(columnHeading))) = dr.item(value(2))

      Catch ex As Exception
      End Try
    Next

    'post read
    If Not drNew Is Nothing Then dsMerge.Tables(0).Rows.Add(db.CleanData(drNew))

    Return dsMerge
  End Function

End Class