Imports System.Data.SqlClient

Public Class Database
#Region "Setup"
    Private _CS As String
    Private _SID As Integer
    Dim sqlConnection As New SqlConnection
    Dim sqlCommand As New SqlCommand
    Dim sqlDataAdapter As SqlDataAdapter
    Public Property ConnectionString() As String
        Get
            Return _CS
        End Get
        Set(ByVal value As String)
            _CS = value
        End Set
    End Property

    Public Property StudentID() As Integer
        Get
            Return _SID
        End Get
        Set(ByVal value As Integer)
            _SID = value
        End Set
    End Property

    Public Sub New(ByVal _ConnectionString As String)
        ConnectionString = _ConnectionString
        sqlConnection.ConnectionString = ConnectionString
        sqlCommand.Connection = sqlConnection
    End Sub
#End Region

    Public Function LogIn(ByVal User As String, ByVal Pass As String)
        Dim sqlQuery As String = String.Format("Select * from [dbo].[login] WHERE username = '{0}' AND password = '{1}'", User, Pass)
        Dim dataTable As New DataTable
        Try
            sqlCommand = New SqlCommand(sqlQuery, sqlConnection)
            sqlCommand.CommandTimeout = 3000
            sqlDataAdapter = New SqlDataAdapter(sqlCommand)
            sqlDataAdapter.Fill(dataTable)
            Return dataTable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
            Return Nothing
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
    End Function
    Public Function ShowLogsLogin()
        Dim sqlQuery As String = String.Format("Select [dbo].[loginLogs].[Id],[dbo].[loginLogs].[adminID],[dbo].[login].[username],[dbo].[loginLogs].[date] from [dbo].[loginLogs] INNER JOIN [dbo].[login] on [dbo].[loginLogs].[adminID] = [dbo].[login].[Id]")
        Dim dataTable As New DataTable
        Try
            sqlCommand = New SqlCommand(sqlQuery, sqlConnection)
            sqlCommand.CommandTimeout = 3000
            sqlDataAdapter = New SqlDataAdapter(sqlCommand)
            sqlDataAdapter.Fill(dataTable)
            Return dataTable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
            Return Nothing
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
    End Function
    Public Function ShowLogs()
        Dim sqlQuery As String = String.Format("Select Date,Type,LastDigit,ChargeMinutes from [dbo].[log]")
        Dim dataTable As New DataTable
        Try
            sqlCommand = New SqlCommand(sqlQuery, sqlConnection)
            sqlCommand.CommandTimeout = 3000
            sqlDataAdapter = New SqlDataAdapter(sqlCommand)
            sqlDataAdapter.Fill(dataTable)
            Return dataTable
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
            Return Nothing
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
    End Function
    Public Function AddLoginLog(ByVal adminID As Int16)
        sqlCommand.CommandText = "INSERT INTO [dbo].[loginLogs] ([adminID],[date]) Values" &
          " (@aid,@date)"
        sqlCommand.Parameters.AddWithValue("@aid", adminID)
        sqlCommand.Parameters.AddWithValue("@date", Today.Date)
        sqlCommand.CommandTimeout = 3000
        sqlConnection.Open()
        Dim Result As Integer
        Try
            Result = sqlCommand.ExecuteNonQuery
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
        Return Result
    End Function
    Public Function AddLogSMS(ByVal LastDigit As String)
        sqlCommand.CommandText = "INSERT INTO [dbo].[log] ([Date],[Type],[LastDigit]) Values" &
          " (@date,@type,@last)"
        sqlCommand.Parameters.AddWithValue("@type", "SMS")
        sqlCommand.Parameters.AddWithValue("@last", LastDigit)
        sqlCommand.Parameters.AddWithValue("@date", Today.Date)

        sqlCommand.CommandTimeout = 3000
        sqlConnection.Open()
        Dim Result As Integer
        Try
            Result = sqlCommand.ExecuteNonQuery
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
        Return Result
    End Function
    Public Function AddLogCharge()
        sqlCommand.CommandText = "INSERT INTO [dbo].[log] ([Date],[Type],[ChargeMinutes]) Values" &
          " (@date,@type,@last)"
        sqlCommand.Parameters.AddWithValue("@type", "Charge")
        sqlCommand.Parameters.AddWithValue("@last", 10)
        sqlCommand.Parameters.AddWithValue("@date", Today.Date)

        sqlCommand.CommandTimeout = 3000
        sqlConnection.Open()
        Dim Result As Integer
        Try
            Result = sqlCommand.ExecuteNonQuery
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
        End Try
        Return Result
    End Function
End Class