Public Class P5DataManager

    Public Shared Sub Insert(ByVal home_team_id As Integer, ByVal away_team_id As Integer, ByVal game_datetime As DateTime)
        Dim con As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Try
            con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.Connection = con
            cmd.CommandText = "INSERT INTO GAMES(home_team_id,away_team_id,game_datetime)VALUES(@home_team_id,@away_team_id,@game_datetime)"
            cmd.Parameters.AddWithValue("@home_team_id", home_team_id)
            cmd.Parameters.AddWithValue("@away_team_id", away_team_id)
            cmd.Parameters.AddWithValue("@game_datetime", game_datetime)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then
                    con.Close()

                End If
                con.Dispose()
            End If

        End Try
       

    End Sub
    Public Shared Sub DeleteGameInfo(ByVal id As String)
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString

            cmd.Connection = conn
            cmd.CommandText = "DELETE FROM GAMES WHERE id=@id"
            cmd.Parameters.AddWithValue("@id", id)

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try

        

    End Sub
    Public Shared Sub UpdateGamesInfo(ByVal id As String, ByVal game_datetime As String, ByVal home_team_id As Integer, ByVal away_team_id As Integer)
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand

        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString

            cmd.Connection = conn
            cmd.CommandText = "UPDATE GAMES SET   game_datetime=@gameTime, " & _
                                                 "home_team_id=@HomeTeamID , " & _
                                                 "away_team_id=@AwayTeamID " & _
                                                 "WHERE id=@id"

            cmd.Parameters.AddWithValue("@gameTime", game_datetime)
            cmd.Parameters.AddWithValue("@homeTeamID", home_team_id)
            cmd.Parameters.AddWithValue("@awayTeamID", away_team_id)
            cmd.Parameters.AddWithValue("@id", id)

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try

      


    End Sub
    Public Shared Sub UpdateScores(ByVal game_id As String, ByVal home_team_score As String, ByVal away_team_score As String)
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("SCORES")
        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.Connection = conn
            cmd.CommandText = "SELECT * FROM SCORES WHERE game_id = @gameid"
            cmd.Parameters.AddWithValue("@gameid", game_id)
            da.SelectCommand = cmd
            da.Fill(ds)
            If ds.CreateDataReader.HasRows Then
                cmd.CommandText = "UPDATE SCORES SET home_team_score=@HomeTeamScore, away_team_score=@AwayTeamScore WHERE game_id=@gameid"
            Else
                cmd.CommandText = "Insert INTO SCORES(game_id, home_team_score, away_team_score) values (@gameid, @HomeTeamScore, @AwayTeamScore)"
            End If
            cmd.Parameters.AddWithValue("@HomeTeamScore", home_team_score)
            cmd.Parameters.AddWithValue("@AwayTeamScore", away_team_score)

            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try

      
    End Sub
    Public Shared Function getTeamsRecord() As DataSet

        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("TEAMS")
        Try
            cmd.Connection = conn
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.CommandText = "SELECT * FROM TEAMS"
            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            Debug.WriteLine(ex)  
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try


        Return ds
    End Function

    Public Shared Function getGamesShcedule() As DataSet

        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("GAMES")

      
        Try
            cmd.Connection = conn
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString

            cmd.CommandText = " SELECT G.id, G.game_datetime," & _
                              " HT.team_name AS home_team,AT.team_name AS away_team," & _
                              " G.home_team_id,G.away_team_id " & _
                              " FROM GAMES as G INNER JOIN TEAMS as HT ON G.home_team_id=HT.id " & _
                              " INNER JOIN TEAMS as AT ON G.away_team_id = AT.id" & _
                              " WHERE G.game_datetime > GETDATE() ORDER BY ID"


            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try
        Return ds


    End Function
    Public Shared Function getGamesShceduleInfo(ByVal team As Integer) As DataSet

        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("GAMESInfo")


        Try
            cmd.Connection = conn
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString

            cmd.CommandText = " SELECT  game_datetime " & _
                              " FROM GAMES " & _
                              " WHERE home_team_id = @team OR away_team_id=@team"
            cmd.Parameters.AddWithValue("@team", team)
            da.SelectCommand = cmd
            da.Fill(ds)

        Catch ex As Exception
            Debug.WriteLine(ex)
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If

        End Try
        Return ds


    End Function
    Public Shared Function getGameScoreInfo() As DataSet

        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("SCORES")


        cmd.Connection = conn
        conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString

        cmd.CommandText = "SELECT G.id, G.game_datetime, HT.team_name AS HomeTeam, AT.team_name AS AwayTeam, S.home_team_score, S.away_team_score " & _
                              "FROM GAMES G INNER JOIN " & _
                                   "TEAMS HT ON G.home_team_id = HT.id INNER JOIN " & _
                                   "TEAMS AT ON G.away_team_id = AT.id LEFT OUTER JOIN " & _
                                   "SCORES S ON G.id = S.game_id " & _
        "WHERE ((G.game_datetime > (GETDATE() -7)) AND (G.game_datetime < = GETDATE()))"
      
        da.SelectCommand = cmd
        da.Fill(ds)
        Return ds


    End Function
    Public Shared Function getCheckGame(ByVal team_id As String) As DataSet
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet("TEAME")
        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.Connection = conn
            cmd.CommandText = "SELECT * FROM TEAMS WHERE id=@team_id"
            cmd.Parameters.AddWithValue("@team_id", team_id)
            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            Throw ex
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()

                End If
                conn.Dispose()
            End If
        End Try
        Return ds
    End Function

    Public Shared Function getPastGame() As DataSet
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.Connection = conn
            cmd.CommandText = "SELECT G.id,G.game_datetime, H.team_name as home_team, A.team_name as away_team, " & _
            "s.home_team_score, s.away_team_score " & _
            "FROM GAME G INNER JOIN " & _
            "TEAMS H ON G.home_team_id = H.id INNER JOIN " & _
            "TEAMS A ON G.away_team_id = A.id LEFT OUTER JOIN " & _
            "SCORES S ON G.id = S.game_id " & _
            "WHERE (((GETDATE() - G.game_datetime) <=7) AND (G.game_dateime <GETDATE()))"

            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            Throw ex
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                conn.Dispose()
            End If
        End Try
        Return ds
    End Function
    Public Shared Function getGameDate(ByVal game_datetime As String, ByVal team_id As String, ByVal skGameId As String) As DataSet
        Dim conn As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim da As New SqlClient.SqlDataAdapter
        Dim ds As New DataSet
        Try
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("P5CS").ConnectionString
            cmd.Connection = conn
            cmd.CommandText = "SELECT * FROM GAMES WHERE DATEDIFF(d,game_datetime,@game_datetime) =0 " & _
            "AND((home_team_id = @team_id) OR (away_team_id = @team_id))"
            If skGameId <> "" Then
                cmd.CommandText = cmd.CommandText & " AND (id <> @skGameId)"
                cmd.Parameters.AddWithValue("@skGameId", skGameId)
            End If

            cmd.Parameters.AddWithValue("@game_datetime", game_datetime)
            cmd.Parameters.AddWithValue("@team_id", team_id)

            da.SelectCommand = cmd
            da.Fill(ds)
        Catch ex As Exception
            Throw ex
        Finally
            If conn IsNot Nothing Then
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                conn.Dispose()
            End If
        End Try
        Return ds
    End Function

End Class