Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Data
Imports System.Globalization
Imports Superbowl_Daily_2.SystemFunctions
Imports Superbowl_Daily_2.ProjectFunctions

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

Public Class db

#If DEBUG Then
  Public Shared conn As New SqlConnection(My.Settings.SQLConnectionDebug.ToString())
#Else
   Public Shared conn As New SqlConnection(My.Settings.SQLConnectionRelease.ToString())
#End If

  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 'timeout error
               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)
      sqlcmd.CommandTimeout = 0
      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 sqlEx As SqlException
            '   Throw New SqlException(sqlEx.Message)
      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
      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
    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
        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
      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
    Dim arrHeadings As New Collection
    Dim sqlNew, sqlFrom, sqlGroupBy, comma, lastKey, 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