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_trgbc

#If DEBUG Then
   Public Shared conn As New SqlConnection(My.Settings.SQLConnectionDebug.ToString())
#Else
   Public Shared conn As New SqlConnection(My.Settings.SQLConnectionTRGBCRelease.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