Imports System.Data.SqlClient
Module Module1
    Public cn As New SqlConnection
    Public cm As New SqlCommand
    Public dr As SqlDataReader
    Public lst As ListViewItem
    Public uID, loginDate, logID, ID, loginID, daya, username, password As String

    Sub connection()
        cn = New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Personal-01\Desktop\demo\Ticketing Management System\database\ticketDB.mdf;Integrated Security=True;Connect Timeout=30")
        cn.Open()
    End Sub

    Function is_empty(ByVal txtEmpty As TextBox) As Boolean
        If txtEmpty.Text = String.Empty Then
            MessageBox.Show("Required Missing Failed", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtEmpty.BackColor = Color.LemonChiffon
            txtEmpty.Focus()
            is_empty = True
        Else
            txtEmpty.BackColor = Color.White
            txtEmpty.Focus()
            is_empty = False
        End If
    End Function

    Function is_empty_combo(ByVal txtEmpty As ComboBox) As Boolean
        If txtEmpty.Text = String.Empty Then
            MessageBox.Show("Required Missing Failed", "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtEmpty.BackColor = Color.LemonChiffon
            txtEmpty.Focus()
            is_empty_combo = True
        Else
            txtEmpty.BackColor = Color.White
            txtEmpty.Focus()
            is_empty_combo = False
        End If
    End Function

    Function recordCount(ByVal sql As String) As String
        cm = New SqlCommand(sql, cn)
        dr = cm.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            recordCount = dr.Item(0).ToString
        Else
            recordCount = 0
        End If
        dr.Close()
    End Function

    'Public Sub auditrail(ByVal ID As String, ByVal action As String)
    '    cm = New SqlCommand("insert into tblAuditTrail(uID,actionmade,datemade) values('" & ID & "','" & action & "','" & DateTime.Now & "'", cn)
    '    cm.ExecuteNonQuery()
    'End Sub

    'Public Sub loginAccount()
    '    uID = ID
    '    loginDate = loginID
    '    cm = New SqlCommand("select * from tblLoginTrail where uID like '" & uID & "' and loginDate like '" & loginDate & "'", cn)
    '    dr = cm.ExecuteReader
    '    dr.Read()
    '    If dr.HasRows Then
    '        logID = dr.GetValue(0).ToString()
    '    End If
    '    dr.Close()
    'End Sub

    Public Sub insetLogin(ByVal user As String, ByVal pass As String)
        loginDate = Convert.ToString(DateTime.Now)
        cm = New SqlCommand("select * from tblUser where username like '" & user & "' and password like '" & pass & "'", cn)
        dr = cm.ExecuteReader
        dr.Read()
        If dr.HasRows Then
            uID = dr.GetValue(0).ToString()
        End If
        dr.Close()
        cm = New SqlCommand("insert into tblLoginTrail(uID,username,logindate) values('" & uID & "','" & user & "','" & loginDate & "')", cn)
        cm.ExecuteNonQuery()
        dr.Close()
        cm = New SqlCommand("update tblUser set status = 'online' where username like '" & user & "' and password like '" & pass & "'", cn)
        cm.ExecuteNonQuery()
    End Sub
End Module
