Imports MySql.Data.MySqlClient
Imports EASendMail
Imports System.IO
Imports System.Text

Public Class App

#Region "connectors"

    Public axCZKEM1 As New zkemkeeper.CZKEM
    Dim sqlconn As New MySqlConnection
    Dim sqlComm As New MySqlCommand
    Dim sqlReader As MySqlDataReader

#End Region

#Region "global variables"

    Dim appName As String = "Automated Fingerprint Scanner Email Notifier"

    Dim myConnectionString As String = "Server=localhost;Database=time_attendance;Uid=root;Pwd=;"

    Dim timestring As String
    Dim acmShift As String
    Dim acmFlag As Integer
    Dim sdwEnrollNumber As String = ""
    Dim idwInOutMode As Integer

    Dim _mailFrom As String = "opc-ta@g-able.com"
    Dim _mailTo As String = ""
    Dim _mailHead As String = "parawee.t@g-able.com , pornlert.w@g-able.com , anek.r@g-able.com , phorntip.m@g-able.com"
    Dim tmpString As String = ""

    Dim _shift_out As String
    Dim shift_out_A As String = "16.00 กะเช้า"
    Dim shift_out_B As String = "24.00 กะบ่าย"
    Dim shift_out_C As String = "8.00 กะดึก"

    Dim _report_time As String
    Dim reportInA As String = "7.15 กะเช้า"
    Dim reportInB As String = "12.15 กะบ่าย"
    Dim reportInC As String = "23.15 กะดึก"

    Dim reportOutA As String = "16.20 กะเช้า"
    Dim reportOutB As String = "24.20 กะบ่าย"
    Dim reportOutC As String = "8.20 กะดึก"

    Dim _report_status As String
    Dim _count As String

    Dim pri As Integer

    Dim _userid As String
    Dim _username As String

    Dim csvFile As String = ""

    '------------------------------------------------------------------

    'shiftA at 7.00-16.00

    'earlyA at 4.00
    'lateA at 8.00
    Dim earlyA As DateTime
    Dim lateA As DateTime

    'earlyOutA at 15:30
    'lateOutA at 16.20
    Dim earlyOutA As DateTime
    Dim lateOutA As DateTime

    '------------------------------------------------------------------

    'shiftB at 15.00-24.00

    'earlyB at 12.00
    'lateB at 16.00
    Dim earlyB As DateTime
    Dim lateB As DateTime

    'earlyOutB at 23.30
    'lateOutB at 24.20
    Dim earlyOutB As DateTime
    Dim lateOutB As DateTime

    '------------------------------------------------------------------

    'shiftC at 23.00-8.00

    'earlyC at 20.00
    'lateC at 24.00
    Dim earlyC As DateTime
    Dim lateC As DateTime

    'earlyOutC at 7:30
    'lateOutC at 8:20
    Dim earlyOutC As DateTime
    Dim lateOutC As DateTime

    '------------------------------------------------------------------

#End Region

#Region "Communicate with device"

    Private bIsConnected = False 'the boolean value identifies whether the device is connected
    Private iMachineNumber As Integer 'the serial number of the device.After connecting the device ,this value will be changed.

    'If your device supports the TCP/IP communications, you can refer to this.
    'when you are using the tcp/ip communication,you can distinguish different devices by their IP address.

    Private Sub btnConnect_Click(sender As System.Object, e As System.EventArgs) Handles btnConnect.Click
        If txtIP.Text.Trim() = "" Or txtPort.Text.Trim() = "" Then
            MsgBox("IP and Port cannot be null", MsgBoxStyle.Exclamation, "Error")
            Return
        End If
        Cursor = Cursors.WaitCursor
        If MachineOnlineIcon.Visible = True Then
            axCZKEM1.Disconnect()
            bIsConnected = False
            btnConnect.Text = "Connect"
            status_MachineOffline()
            Cursor = Cursors.Default
            Return
        End If

        Dim idwErrorCode As Integer
        bIsConnected = axCZKEM1.Connect_Net(txtIP.Text.Trim(), Convert.ToInt32(txtPort.Text.Trim()))
        If bIsConnected = True Then
            btnConnect.Text = "Disconnect"
            btnConnect.Refresh()
            status_MachineOnline()
            iMachineNumber = 1 'In fact,when you are using the tcp/ip communication,this parameter will be ignored,that is any integer will all right.Here we use 1.
            axCZKEM1.RegEvent(iMachineNumber, 65535) 'Here you can register the realtime events that you want to be triggered(the parameters 65535 means registering all)
        Else
            axCZKEM1.GetLastError(idwErrorCode)
            lblstatus.Text = "Unable to connect the device,ErrorCode= '" & idwErrorCode & "' "
        End If
        Cursor = Cursors.Default
    End Sub

#End Region

#Region "Application"

    'Download the attendance records from the device, sync to database
    Private Sub btnSyncData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSyncData.Click
        Syncing()
    End Sub

    'Get the count of attendance records in from ternimal.
    Private Sub btnGetRecordCount_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetRecordCount.Click
        If bIsConnected = False Then
            MsgBox("Please connect the device first", MsgBoxStyle.Exclamation, "Error")
            Return
        End If
        Dim idwErrorCode As Integer
        Dim iValue = 0

        axCZKEM1.EnableDevice(iMachineNumber, False) 'disable the device
        If axCZKEM1.GetDeviceStatus(iMachineNumber, 6, iValue) = True Then 'Here we use the function "GetDeviceStatus" to get the record's count.The parameter "Status" is 6.
            MsgBox("The count of the AttLogs in the device is " + iValue.ToString(), MsgBoxStyle.Information, "Success")
        Else
            axCZKEM1.GetLastError(idwErrorCode)
            MsgBox("Operation failed,ErrorCode=" & idwErrorCode, MsgBoxStyle.Exclamation, "Error")
        End If

        axCZKEM1.EnableDevice(iMachineNumber, True) 'enable the device
        status_MachineOnline()
    End Sub

    'database connection check
    Public Sub connectMysqlCheck()
        If Not sqlconn Is Nothing Then
            sqlconn.ConnectionString = myConnectionString
            Try
                sqlconn.Open()
                MsgBox("Connected to MySQL Database.", 64, appName)
                lblstatus.Text = "Status : Connected to MySQL Database."
                sqlconn.Close()
            Catch
                MsgBox("Unable to connect to MySQL Database, returning...", 48, appName)
                lblstatus.Text = "Status : Unable to connect to MySQL Database ."
                Return
            End Try
        End If
    End Sub
    'machine/device connection check
    Private Sub connectMachineCheck()
        If bIsConnected = False Then
            MsgBox("Please connect the device", MsgBoxStyle.Exclamation, "Error")
            status_MachineOffline()
            Return
        Else
            status_MachineOnline()
        End If
    End Sub

#End Region

#Region "Algs"

    'method for sync
    Private Sub Syncing()
        Cursor = Cursors.WaitCursor
        Timer1.Enabled = False

        'truncate(shift_a, shift_b, shift_c,raw)
        Try
            sqlconn.Open()
            sqlComm.Connection = sqlconn

            'Dim cmd As MySqlCommand = New MySqlCommand
            'Dim mysqlBackup As MySqlBackup = New MySqlBackup(cmd)
            'cmd.Connection = sqlconn
            'My.Computer.FileSystem.CreateDirectory("D:\TimeAttendanceBackup\")
            'Dim exportFile As String = "D:\TimeAttendanceBackup\backup.sql"
            'exportFile = exportFile.Replace(".sql", "_" & System.DateTime.Now.ToString("yyyyMMddhhmmss") & ".sql")
            'mysqlBackup.ExportToFile(exportFile)

            Dim SQL As String
            SQL = "truncate shift_a; truncate shift_b; truncate shift_c; truncate raw"
            sqlComm.CommandText = SQL
            sqlComm.ExecuteNonQuery()
            sqlconn.Close()
        Catch
            lblstatus.Text = "Unable to connect to MySQL Database, returning..."
            Cursor = Cursors.Default
            Return
        End Try

        'sync
        pull_machine()
        Cursor = Cursors.Default
        Timer1.Enabled = True
    End Sub
    '[sub method for sync] pull data
    Private Sub pull_machine()

        Dim iGLCount = 0
        pri = 0

        Dim idwVerifyMode As Integer
        Dim idwYear As Integer
        Dim idwMonth As Integer
        Dim idwDay As Integer
        Dim idwHour As Integer
        Dim idwMinute As Integer
        Dim idwSecond As Integer
        Dim idwWorkcode As Integer

        Dim idwErrorCode As Integer
        Dim lvItem As New ListViewItem("Items", 0)

        axCZKEM1.EnableDevice(iMachineNumber, False) 'disable the device
        If axCZKEM1.ReadGeneralLogData(iMachineNumber) Then 'read all the attendance records to the memory
            'get records from the memory
            While axCZKEM1.SSR_GetGeneralLogData(iMachineNumber, sdwEnrollNumber, idwVerifyMode, idwInOutMode, idwYear, idwMonth, idwDay, idwHour, idwMinute, idwSecond, idwWorkcode)
                iGLCount += 1
                pri += 1
                'moved variable
                timestring = idwYear.ToString("0000") & "/" & idwMonth.ToString("00") & "/" & idwDay.ToString("00") & " " & idwHour.ToString("00") & ":" & idwMinute.ToString("00") & ":" & idwSecond.ToString("00")

                Dim dateToCheck As New DateTime(idwYear, idwMonth, idwDay) 'any date you want to check here
                Dim Check As DateTime = dateToCheck.AddDays(1)

                'earlyA at 5.00
                'lateA at 9.00
                earlyA = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "05:00:00"
                lateA = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "09:00:00"

                'earlyB at 13.00
                'lateB at 17.00
                earlyB = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "13:00:00"
                lateB = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "17:00:00"

                'earlyC at 21.00
                'lateC at 1.00
                earlyC = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "21:00:00"
                If dateToCheck.Month = Check.Month Then
                    lateC = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() + 1 & " " & "01:00:00"
                Else
                    lateC = idwYear.ToString() & "/" & idwMonth.ToString() + 1 & "/" & "01" & " " & "01:00:00"
                End If

                'earlyOutA at 15:30
                'lateOutA at 18.00
                earlyOutA = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "15:30:00"
                lateOutA = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "18:00:00"

                'earlyOutB at 23.30
                'lateOutB at 02.00
                earlyOutB = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "23:30:00"
                If dateToCheck.Month = Check.Month Then
                    lateOutB = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() + 1 & " " & "02:00:00"
                Else
                    lateOutB = idwYear.ToString() & "/" & idwMonth.ToString() + 1 & "/" & "1" & " " & "02:00:00"
                End If

                'earlyOutC at 7:30
                'lateOutC at 8:30
                earlyOutC = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "07:30:00"
                lateOutC = idwYear.ToString() & "/" & idwMonth.ToString() & "/" & idwDay.ToString() & " " & "08:30:00"

                shift_Manage()

                '---------------------------------------------------------------------------
                'push to mysql
                sqlconn.Open()
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "insert into raw (iGLCount,iMachineNumber,sdwEnrollNumber,idwVerifyMode,idwInOutMode,datetime,idwWorkcode,acmShift) " & _
                    "values ('" & iGLCount & "','" & iMachineNumber.ToString & "', '" & sdwEnrollNumber.ToString & "', '" & idwVerifyMode.ToString & "', '" & idwInOutMode.ToString & "', '" & timestring & "', '" & idwWorkcode.ToString & "', '" & acmShift & "')"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()
                sqlconn.Close()

            End While
        Else
            axCZKEM1.GetLastError(idwErrorCode)
            If idwErrorCode <> 0 Then
                MsgBox("Reading data from terminal failed,ErrorCode: " & idwErrorCode, MsgBoxStyle.Exclamation, "Error")
            Else
                MsgBox("No data from terminal returns!", MsgBoxStyle.Exclamation, "Error")
            End If
        End If
        axCZKEM1.EnableDevice(iMachineNumber, True) 'enable the device
    End Sub
    '[sub method for sync] shift manipulate
    Private Sub shift_Manage()

        Dim timestringparse As DateTime = DateTime.ParseExact(timestring, "yyyy/MM/dd HH:mm:ss", Nothing)

        '------------------------------------------------------------------
        'login
        If idwInOutMode.ToString = 0 Then
            sqlconn.Open()
            acmFlag = "0"
            If earlyA < timestringparse And timestringparse < lateA Then
                acmShift = "A"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "insert into shift_a (pri,id_om,datetime,io_mode,flag)" & _
                "values ('" & pri & "','" & sdwEnrollNumber.ToString & "', '" & timestring & "','" & idwInOutMode.ToString & "','" & acmFlag & "')"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()

            ElseIf earlyB < timestringparse And timestringparse < lateB Then
                acmShift = "B"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "insert into shift_b (pri,id_om,datetime,io_mode,flag)" & _
                "values ('" & pri & "','" & sdwEnrollNumber.ToString & "', '" & timestring & "','" & idwInOutMode.ToString & "','" & acmFlag & "')"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()

            ElseIf earlyC < timestringparse And timestringparse < lateC Then
                acmShift = "C"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "insert into shift_c (pri,id_om,datetime,io_mode,flag)" & _
                "values ('" & pri & "','" & sdwEnrollNumber.ToString & "', '" & timestring & "','" & idwInOutMode.ToString & "','" & acmFlag & "')"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()

            End If
            sqlconn.Close()

            '------------------------------------------------------------------

            'logout
        ElseIf idwInOutMode.ToString = 1 Then
            sqlconn.Open()
            acmFlag = "1"
            If earlyOutA < timestringparse And timestringparse < lateOutA Then
                acmShift = "A"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "update shift_a set flag='" & acmFlag & "'" & _
                "where '" & sdwEnrollNumber.ToString & "'=id_om and Flag=0"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()

            ElseIf earlyOutB < timestringparse And timestringparse < lateOutB Then
                acmShift = "B"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "update shift_b set flag='" & acmFlag & "'" & _
               "where '" & sdwEnrollNumber.ToString & "'=id_om and Flag=0"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()

            ElseIf earlyOutC < timestringparse And timestringparse < lateOutC Then
                acmShift = "C"
                sqlComm.Connection = sqlconn
                Dim SQL As String
                SQL = "update shift_c set flag='" & acmFlag & "'" & _
               "where '" & sdwEnrollNumber.ToString & "'=id_om and Flag=0"
                sqlComm.CommandText = SQL
                sqlComm.ExecuteNonQuery()
            End If
            sqlconn.Close()
        End If
    End Sub

    '---------------------------------------------------------------------------
    'update csv button
    Private Sub btnCSV_Click(sender As System.Object, e As System.EventArgs) Handles btnCSV.Click

        OpenFileDialog1.Title = "Please Select a File"
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.InitialDirectory = ""
        OpenFileDialog1.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*"
        OpenFileDialog1.ShowDialog()

    End Sub
    'edit
    Private Sub OpenFileDialog1_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk

        Dim readfile As String = OpenFileDialog1.FileName

        Dim tempdir As String = Path.GetDirectoryName(readfile)
        Dim tempfile As String = "tmp_file.csv"
        Dim destinationFile = Path.Combine(tempdir, tempfile)

        Dim fileContent As String = File.ReadAllText(readfile, Encoding.Default)
        File.WriteAllText(destinationFile, fileContent, Encoding.UTF8)

        csvFile = destinationFile.Replace("\", "/")
        Dim SQL As String
        SQL = "truncate user;" & _
               "LOAD DATA LOCAL INFILE '" & csvFile & "' " & _
               "INTO TABLE user " & _
               "FIELDS TERMINATED BY ',' IGNORE 2 LINES " & _
               "(no,id_om,id_user,firstname,lastname,nickname,mobile,email)"
        Try
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            sqlComm.CommandText = SQL
            sqlComm.ExecuteNonQuery()
            File.Delete(destinationFile)
        Catch ex As MySqlException
            MsgBox(ex.ToString)
            File.Delete(destinationFile)
            sqlconn.Close()
        End Try
        sqlconn.Close()

        lblFilename.Text = "Updated user with '" & OpenFileDialog1.FileName & "' "
        lblFilename.Visible = True
    End Sub

    '---------------------------------------------------------------------------

    '---------------------------------------------------------------------------
    'for lights indicator
    Private Sub status_MachineOnline()
        lblMachineState.Text = "Machine State:Connected"
        MachineOnlineIcon.Visible = True
        MachineOfflineIcon.Visible = False
    End Sub
    Private Sub status_MachineOffline()
        lblMachineState.Text = "Machine State:Disconnected"
        MachineOnlineIcon.Visible = False
        MachineOfflineIcon.Visible = True
    End Sub

    '---------------------------------------------------------------------------

    'loading
    Private Sub MyBase_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Text = appName
        Timer1.Enabled = True
        connectMysqlCheck()
    End Sub
    'tick 1000ms
    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        lblClock.Text = Format(Now, "yyyy-MM-dd HH:mm:ss")
        If DateTime.Now.Minute = 0 Or DateTime.Now.Minute = 30 Then
            Syncing()
        End If
        time_Alert()
    End Sub
#End Region

#Region "notifier"

    'WIP
    'need polishing reportin/out

    Private Sub time_Alert()
        Dim now As DateTime = DateTime.Now
        If (15 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 1A
            _shift_out = shift_out_A
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_a s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True

        ElseIf (15 = now.Hour) And (50 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 2A
            _shift_out = shift_out_A
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_a s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True

        ElseIf (16 = now.Hour) And (10 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 3A
            _shift_out = shift_out_A
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_a s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
            '--------------------------------------------------------
        ElseIf (23 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 1B
            _shift_out = shift_out_B
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_b s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (23 = now.Hour) And (50 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 2B
            _shift_out = shift_out_B
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_b s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (0 = now.Hour) And (10 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 3B
            _shift_out = shift_out_B
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_b s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
            '--------------------------------------------------------

        ElseIf (7 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 1C
            _shift_out = shift_out_C
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_c s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (7 = now.Hour) And (50 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 2C
            _shift_out = shift_out_C
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_c s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (8 = now.Hour) And (10 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'noti 3C
            _shift_out = shift_out_C
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.email as useremail, u.id_user as userid , u.firstname as username " & _
                "from shift_c s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    _mailTo = sqlReader.Item("useremail")
                    _userid = sqlReader.Item("userid")
                    _username = sqlReader.Item("username")
                    sendMailNoti()
                Loop
            End If
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
            '--------------------------------------------------------

            'reportin; report to head who tap in
        ElseIf (7 = now.Hour) And (25 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportin A
            'select id from shift_a where io_mode=1
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username " & _
                "from shift_a s, user u " & _
                "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & vbCrLf
                Loop
            End If
            _report_status = "แตะเครื่องเข้างาน"
            _report_time = reportInA
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (12 = now.Hour) And (25 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportin B
            'select id from shift_b where io_mode=1
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username " & _
                    "from shift_b, user u " & _
                    "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & vbCrLf
                Loop
            End If
            _report_status = "แตะเครื่องเข้างาน"
            _report_time = reportInB
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (23 = now.Hour) And (25 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportin C
            'select id from shift_c where io_mode=1
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username " & _
                    "from shift_c s, user u " & _
                    "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & vbCrLf
                Loop
            End If
            _report_status = "แตะเครื่องเข้างาน"
            _report_time = reportInC
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
            '--------------------------------------------------------
            'reportout; report to head who didn't tap out
        ElseIf (16 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportout A
            'select id from shift_a where flag=0
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username, u.mobile as mobile " & _
                    "from shift_a s, user u " & _
                    "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE())"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & "เบอร์โทร " & sqlReader.Item("mobile") & vbCrLf
                Loop
            End If
            _report_status = "ยังไม่แตะเครื่องออกจากงาน"
            _report_time = reportOutA
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        ElseIf (0 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportout B
            'select id from shift_b where flag=0
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username " & _
                    "from shift_b s, user u " & _
                    "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & "เบอร์โทร " & sqlReader.Item("mobile") & vbCrLf
                Loop
            End If
            _report_status = "ยังไม่แตะเครื่องออกจากงาน"
            _report_time = reportOutB
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True

        ElseIf (8 = now.Hour) And (35 = now.Minute) Then
            Syncing()
            Timer1.Enabled = False
            'reportout C
            'select id from shift_c where flag=0
            sqlconn.Open()
            sqlComm.Connection = sqlconn
            Dim SQL As String
            SQL = "select u.firstname as username " & _
                    "from shift_c s, user u " & _
                    "where s.flag=0 and s.id_om=u.id_om and DATE(`datetime`) = DATE(CURDATE()) OR DATE(`datetime`) = DATE_ADD(CURDATE(), INTERVAL -1 DAY)"
            Dim lvItem As New ListViewItem("Items", 0)
            sqlComm.CommandText = SQL
            sqlReader = sqlComm.ExecuteReader()
            If sqlReader.HasRows Then
                Do While sqlReader.Read()
                    'need to do sth here
                    tmpString += sqlReader.Item("username") & "เบอร์โทร " & sqlReader.Item("mobile") & vbCrLf
                Loop
            End If
            _report_status = "ยังไม่แตะเครื่องออกจากงาน"
            _report_time = reportOutC
            sendMailReport()
            sqlReader.Close()
            sqlconn.Close()
            Timer1.Enabled = True
        End If
    End Sub
    'need polishing mail body
    Private Sub sendMailNoti()
        Dim oMail As New SmtpMail("TryIt")
        Dim oSmtp As New SmtpClient()
        ' Your office 365 email address
        oMail.From = "opc-ta@g-able.com"
        ' Set recipient email address, please change it to yours
        oMail.To = _mailTo

        ' Set email subject
        oMail.Subject = "แจ้งเตือน : '" & _userid & "' '" & _username & "' กรุณาแตะลายนิ้วมือที่เครื่องเพื่อออกงาน เวลา '" & _shift_out & "' ."

        ' Set email body
        oMail.TextBody = "แจ้งเตือน : '" & _userid & "' '" & _username & "' กรุณาแตะลายนิ้วมือที่เครื่องเพื่อออกงาน เวลา '" & _shift_out & "' ." & _
            " '" & vbCrLf & "' โปรแกรมแจ้งเตือนอัตโนมัติ"

        ' Your SMTP server address,
        Dim oServer As New SmtpServer("mail.g-able.com")

        oServer.Protocol = ServerProtocol.ExchangeEWS

        ' user authentication should use your
        ' email address as the user name.
        oServer.User = "opc-ta@g-able.com"
        oServer.Password = "opc-t@99"

        ' detect SSL/TLS connection automatically
        oServer.ConnectType = SmtpConnectType.ConnectSSLAuto
        Try
            lblstatus.Text = "start to send email over SSL ..."
            oSmtp.SendMail(oServer, oMail)
            lblstatus.Text = "email was sent successfully!"
        Catch ep As Exception
            MsgBox("failed to send email with the following error:")
            MsgBox(ep.Message)
        End Try

    End Sub
    Private Sub sendMailReport()
        Dim oMail As New SmtpMail("TryIt")
        Dim oSmtp As New SmtpClient()
        ' Your office 365 email address
        oMail.From = "opc-ta@g-able.com"
        ' Set recipient email address, please change it to yours
        oMail.To = ""
        oMail.To.Add("parawee.t@g-able.com")
        oMail.To.Add("pornlert.w@g-able.com")
        oMail.To.Add("anek.r@g-able.com")
        oMail.To.Add("phorntip.m@g-able.com")

        ' Set email subject
        oMail.Subject = "อีเมลรายงานสรุป : รายชื่อพนักงานที่ '" & _report_status & "' เวลา '" & _report_time & "' ."

        ' Set email body
        oMail.TextBody = "รายชื่อ '" & vbCrLf & "' '" & tmpString & "' " & _
            "โปรแกรมแจ้งเตือนอัตโนมัติ"

        ' Your SMTP server address,
        Dim oServer As New SmtpServer("mail.g-able.com")

        oServer.Protocol = ServerProtocol.ExchangeEWS

        ' user authentication should use your
        ' email address as the user name.
        oServer.User = "opc-ta@g-able.com"
        oServer.Password = "opc-t@99"

        ' detect SSL/TLS connection automatically
        oServer.ConnectType = SmtpConnectType.ConnectSSLAuto
        Try
            lblstatus.Text = "start to send email over SSL ..."
            oSmtp.SendMail(oServer, oMail)
            lblstatus.Text = "email was sent successfully!"
        Catch ep As Exception
            MsgBox("failed to send email with the following error:")
            MsgBox(ep.Message)
        End Try
        tmpString = ""
    End Sub

#End Region
End Class