Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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 (35 = 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 (35 = 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 (35 = 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
- lblstatus.Text = "failed to send email with the following error: '" & 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
- lblstatus.Text = "failed to send email with the following error: '" & ep.Message & "' ."
- End Try
- tmpString = ""
- End Sub
- #End Region
- End Class
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement