Imports MySql.Data
Imports MySql.Data.MySqlClient



Public Class MainForm

    Dim dbCon As MySqlConnection
    Dim strQuery As String = ""
    Dim SQLCmd As MySqlCommand
    Dim DA As New MySqlDataAdapter
    Dim bSource As New BindingSource
    Dim strDBCon = "Server=178.33.89.130;Database=zenrelo1_login;Uid=zenrelo1_secure;Pwd=hailzr0097"

    Sub UpdateTable()
        Try
            tblData.RowsDefaultCellStyle.BackColor = Color.FromArgb(50, 50, 50)

            Dim tblPub As New DataTable
            dbCon = New MySqlConnection(strDBCon)
            strQuery = "SELECT * FROM zenrelo1_login.users;"

            SQLCmd = New MySqlCommand(strQuery, dbCon)
            dbCon.Open()

            With SQLCmd
                .Connection = dbCon
                .CommandText = strQuery
            End With
            DA.SelectCommand = SQLCmd
            DA.Fill(tblPub)
            bSource.DataSource = tblPub
            tblData.DataSource = bSource

            tblData.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        Catch ex As Exception
            MessageBox.Show("Error occurred while deleting record on table!" & vbNewLine & "Error: " & ex.Message, "ZR_Admin")
        End Try

    End Sub

    Private Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click
        If lblUsername.Text = "customerUsername" Or lblPassword.Text = "customerPassword" Or lblHWID.Text = "customerHWID" Or lblExpiryDate.Text = "customerExpiryDate" Then
            MsgBox("Details are not completed!")
        Else
            Dim res As MsgBoxResult = MsgBoxResult.Yes
            If MsgBox("Confirm add credentials?" & vbNewLine & "Username: " & lblUsername.Text & vbNewLine & "Password: " & lblPassword.Text & vbNewLine & "HWID: " & lblHWID.Text & vbNewLine & "Expiry Date: " & lblExpiryDate.Text, MsgBoxStyle.YesNo, "ZR_Admin") = res Then
                Try
                    dbCon = New MySqlConnection(strDBCon)
                    SQLCmd = New MySqlCommand(strQuery, dbCon)

                    dbCon.Open()
                    SQLCmd.Connection = dbCon
                    SQLCmd.CommandText = "INSERT INTO users_sdo (`username`, `password`, `hwid`, `userlevel`, `creationdate`, `expirydate`) VALUES ('" & lblUsername.Text & "', '" & lblPassword.Text & "', '" & lblHWID.Text & "', 'SDO', '" & timenowPicker.Text & "', '" & lblExpiryDate.Text & "');"
                    SQLCmd.ExecuteNonQuery()
                    UpdateTable()
                    MsgBox("[Credentials added successfully]" & vbNewLine & "Username: " & lblUsername.Text & vbNewLine & "Password: " & lblPassword.Text & vbNewLine & "HWID: " & lblHWID.Text & vbNewLine & "Expiry Date: " & lblExpiryDate.Text, MsgBoxStyle.Information, "ZR_Admin")

                Catch ex As Exception
                    MessageBox.Show("Error occurred while inserting record on table!" & vbNewLine & "Error: " & ex.Message, "ZR_Admin")
                End Try
            End If

        End If
    End Sub

    Private Sub BtnRemove_Click(sender As Object, e As EventArgs) Handles BtnRemove.Click
        Try
            If MsgBox("Confirm remove credentials?" & vbNewLine & "UserID: " & txtID.Text & vbNewLine & "Username: " & txtUser.Text, MsgBoxStyle.YesNo, "ZR_Admin") = MsgBoxResult.Yes Then

                dbCon = New MySqlConnection(strDBCon)
                SQLCmd = New MySqlCommand(strQuery, dbCon)
                dbCon.Open()
                SQLCmd.Connection = dbCon
                SQLCmd.CommandText = "DELETE FROM uers WHERE `id`=' " & txtID.Text & "';"
                SQLCmd.ExecuteNonQuery()
                UpdateTable()
                MsgBox("[Credentials removed successfully]" & vbNewLine & "UserID: " & txtID.Text & vbNewLine & "Username: " & txtUser.Text, MsgBoxStyle.Information, "ZR_Admin")

            End If
        Catch ex As Exception
            MessageBox.Show("Error occurred while deleting record on table!" & vbNewLine & "Error: " & ex.Message, "ZR_Admin")
        Finally
            dbCon.Close()
        End Try

    End Sub

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles Me.Load
        UpdateTable()
        timePicker.ShowUpDown = True
    End Sub

    Private Sub cbOptions_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbOptions.SelectedIndexChanged
        If cbOptions.Text = "Expiry Date" Then
            timePicker.BringToFront()
        Else
            timePicker.SendToBack()
        End If
    End Sub

    Private Sub tmrUpdateTime_Tick(sender As Object, e As EventArgs) Handles tmrUpdateTime.Tick
        timenowPicker.Value = DateTime.Now
    End Sub


    Private Sub BtnExtend_Click(sender As Object, e As EventArgs) Handles BtnExtend.Click
        If txtID.Text = "" Or lblExpiryDate.Text = "customerExpiryDate" Then
            MsgBox("Details are not completed!")
        Else
            Dim res As MsgBoxResult = MsgBoxResult.Yes
            If MsgBox("Confirm extend period?" & vbNewLine & "UserID: " & txtID.Text & vbNewLine & "Username: " & txtUser.Text & vbNewLine & "Expiry Date: " & lblExpiryDate.Text, MsgBoxStyle.YesNo, "ZR_Admin") = res Then
                Try

                    dbCon = New MySqlConnection(strDBCon)
                    SQLCmd = New MySqlCommand(strQuery, dbCon)

                    dbCon.Open()
                    SQLCmd.Connection = dbCon
                    SQLCmd.CommandText = "UPDATE memberships SET `expirydate`='" & lblExpiryDate.Text & "' WHERE `id`='" & txtID.Text & "';"
                    SQLCmd.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show("Error occurred while inserting record on table!" & vbNewLine & "Error: " & ex.Message, "ZR_Admin")
                Finally
                    dbCon.Close()
                End Try

            End If
        End If
    End Sub
End Class