API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 27th, 2020
86
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.90 KB | None | 0 0
raw download clone embed print report
  1. Imports MySql.Data.MySqlClient
  2.  
  3. Public Class frmViewWorkerRecords
  4. ' declaring variables
  5. Private getQuery As String = Nothing
  6. Private getCommand As MySqlCommand
  7. Private getReader As MySqlDataReader
  8. Private getDay As Date = Nothing
  9. Private getWorkingHours As Decimal = Nothing
  10. Private getStartingTimeIN As Integer = Nothing
  11. Private getEndingTimeOUT As Integer = Nothing
  12. Private getTimeInFormat As DateTime = Nothing
  13. Private getTimeOutFormat As DateTime = Nothing
  14. Private getOverTimeHours As Decimal = Nothing
  15. Private getLateTimeHOurs As Integer = Nothing
  16. Private getStartingOvertime As Integer = 17
  17. Private getEndingOvertime As Integer = Nothing
  18. Private getStartingLateTime As Integer = 8
  19. Private getEndingLateTime As Integer = Nothing
  20. Private getTimeInAMHours As Integer = Nothing
  21. Private getTimeOutAMHours As Integer = Nothing
  22. Private getTimeInPMHours As Integer = Nothing
  23. Private getTimeOutPMHours As Integer = Nothing
  24. Private lateSummary As Decimal = 0
  25. Private iWorkingHours = 0, iLate = 0, iOverTime As Integer = 0
  26.  
  27. Public workerID As Integer = 0
  28. Public sDate As Date
  29. Public eDate As Date
  30. Function ReturnAmIn(ByVal timeSeconds As Integer) As Integer
  31. Dim temp As Integer = 0
  32.  
  33. If timeSeconds < 32460 Then '09:01:00
  34. temp = 32400 ' 08:30:00
  35. End If
  36.  
  37. If timeSeconds < 30660 Then '08:31:00
  38. temp = 30600 ' 08:30:00
  39. End If
  40.  
  41. If timeSeconds < 29460 Then '08:11:00
  42. temp = 28800 '8:00:00
  43. End If
  44. timeSeconds = temp
  45. Return timeSeconds
  46. End Function
  47.  
  48. Function ReturnBreakOut(ByVal timeSeconds As Integer) As Integer
  49. If timeSeconds > 43200 Then '12:00:00
  50. timeSeconds = 43200 ' 12:00:00
  51. End If
  52.  
  53. Return timeSeconds
  54. End Function
  55.  
  56. Function ReturnBreakIn(ByVal timeSeconds As Integer) As Integer
  57. If timeSeconds < 47460 Then '13:11:00
  58. timeSeconds = 46800 ' 13:00:00
  59. End If
  60.  
  61. Return timeSeconds
  62. End Function
  63.  
  64. Function ReturnPmOut(ByVal timeSeconds As Integer) As Integer
  65. If timeSeconds > 61199 And timeSeconds < 64799 Then ' 16:59:59 18:01:00.
  66. timeSeconds = 61200 ' 5PM
  67. End If
  68.  
  69. 'If getTimeOutPMHours > 64799 And getTimeOutPMHours < 65700 Then 'HERO? 6pm to 6:30pm
  70. ' timeSeconds = 64800 ' reset to 6PM
  71. 'End If
  72. Return timeSeconds
  73. End Function
  74.  
  75. Function ReturnHalfAfterNoonIn(ByVal timeSeconds As Integer) As Integer
  76. If timeSeconds > 39600 And timeSeconds < 47460 Then '11:00:00 13:11:00
  77. timeSeconds = 46800 ' 13:00:00
  78. End If
  79. Return timeSeconds
  80. End Function
  81.  
  82. Function ReturnHalfAfterNoonOut(ByVal timeSeconds As Integer) As Integer
  83. If timeSeconds > 39600 And timeSeconds < 47460 Then '11:00:00 13:11:00
  84. timeSeconds = 46800 ' 13:00:00
  85. End If
  86. Return timeSeconds
  87. End Function
  88.  
  89. Function ReturnHalfAmOut(ByVal timeSeconds As Integer) As Integer
  90. If timeSeconds < 46800 Then 'less than 10Am
  91. timeSeconds = 43200 ' 12:00:00
  92. End If
  93.  
  94. If timeSeconds > 46800 And timeSeconds < 64800 Then
  95. timeSeconds = 61200 ' 5PM
  96. End If
  97. Return timeSeconds
  98. End Function
  99.  
  100. ' get the worker's details
  101. Private Sub ViewWorkerRecords()
  102.  
  103. getQuery = "SELECT TIME_TO_SEC(`AM_IN`)'cAMIN',TIME_TO_SEC(`AM_OUT`)'cAMOUT',TIME_TO_SEC(`PM_IN`)'cPMIN',TIME_TO_SEC(`PM_OUT`)'cPMOUT', coalesce(TIME_TO_SEC(`OverTime`),0)'cOverTime', `AM_IN`, `AM_OUT`, `PM_IN`, `PM_OUT`, `spdate`, `OverTime` FROM dtr, worker WHERE worker.worker_id ='" & _
  104. workerID & "' AND dtr.worker_id ='" & workerID & "' AND dtr.spdate BETWEEN '" & Format(sDate.ToString("yyyy-MM-dd")) & "' AND '" & Format(eDate.ToString("yyyy-MM-dd")) & "' ORDER BY dtr.spdate"
  105. getCommand = New MySqlCommand(getQuery, MySQLConnection)
  106. getReader = getCommand.ExecuteReader
  107. Dim temp4 As Integer = 0
  108.  
  109. While getReader.Read
  110. temp4 += 1
  111. ' getting the time in and time out of the worker
  112. getTimeInAMHours = If(IsDBNull(getReader.Item("cAMIN")) = True, 0, getReader.Item("cAMIN"))
  113. getTimeOutAMHours = If(IsDBNull(getReader.Item("cAMOUT")) = True, 0, getReader.Item("cAMOUT"))
  114. getTimeInPMHours = If(IsDBNull(getReader.Item("cPMIN")) = True, 0, getReader.Item("cPMIN"))
  115. getTimeOutPMHours = If(IsDBNull(getReader.Item("cPMOUT")) = True, 0, getReader.Item("cPMOUT"))
  116.  
  117.  
  118. getTimeInAMHours = If(IsDBNull(getReader.Item("cAMIN")) = True, 0, getReader.Item("cAMIN"))
  119. getTimeOutAMHours = If(IsDBNull(getReader.Item("cAMOUT")) = True, 0, getReader.Item("cAMOUT"))
  120. getTimeInPMHours = If(IsDBNull(getReader.Item("cPMIN")) = True, 0, getReader.Item("cPMIN"))
  121. getTimeOutPMHours = If(IsDBNull(getReader.Item("cPMOUT")) = True, 0, getReader.Item("cPMOUT"))
  122.  
  123. Console.Write(" AMIN:" & getTimeInAMHours)
  124. Console.Write(" AMOUT:" & getTimeOutAMHours)
  125. Console.Write(" PMIN:" & getTimeInPMHours)
  126. Console.Write(" PMOUT:" & getTimeOutPMHours)
  127.  
  128. If getTimeInAMHours <> 0 And getTimeOutAMHours <> 0 Then
  129. 'VALID
  130.  
  131. If getTimeInPMHours = 0 Then
  132. 'HALF DAY
  133. Console.WriteLine("HALF DAY!")
  134. If getTimeInAMHours < 36000 Then
  135. 'HALF DAY MORNING
  136.  
  137. getTimeInAMHours = ReturnAmIn(getTimeInAMHours)
  138.  
  139.  
  140. Else
  141. 'HALFDAY AFTERNOON
  142.  
  143. getTimeInAMHours = ReturnHalfAfterNoonIn(getTimeInAMHours)
  144.  
  145. End If
  146.  
  147. getTimeOutAMHours = ReturnHalfAmOut(getTimeOutAMHours)
  148.  
  149.  
  150.  
  151. Else
  152. 'WHOLE DAY
  153. getTimeInAMHours = ReturnAmIn(getTimeInAMHours)
  154. getTimeOutAMHours = ReturnBreakOut(getTimeOutAMHours)
  155. getTimeInPMHours = ReturnBreakIn(getTimeInPMHours)
  156. getTimeOutPMHours = ReturnPmOut(getTimeOutPMHours)
  157.  
  158. End If
  159.  
  160.  
  161. If getTimeInAMHours < 36000 Then
  162.  
  163. iLate = If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 28799, getTimeInAMHours - 28800, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 46799, getTimeInPMHours - 46800, 0))
  164. Else
  165.  
  166. iLate = getTimeInAMHours - 46800
  167. End If
  168.  
  169. lateSummary += iLate
  170.  
  171. ' If getTimeInPMHours <> 0 Then
  172.  
  173.  
  174. ' 'For earylOut
  175. ' iLate = If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 32399, getTimeInAMHours - 28800, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 46799, getTimeInPMHours - 46800, 0))
  176. ' lateSummary += iLate
  177. 'Else
  178. ' iLate = getTimeInAMHours - 39600
  179. ' lateSummary += iLate
  180. 'End If
  181.  
  182.  
  183. iWorkingHours = (getTimeOutAMHours - getTimeInAMHours) + (getTimeOutPMHours - getTimeInPMHours)
  184. getWorkingHours += If(iWorkingHours > 28800, 28800, iWorkingHours)
  185. iOverTime = If(iWorkingHours > 28800, iWorkingHours - 28800, 0) 'if working hours > 8hours then OT yan
  186. getOverTimeHours += iOverTime
  187.  
  188. Else
  189. iWorkingHours = 0
  190. getWorkingHours += 0
  191. iOverTime = 0
  192. getOverTimeHours += 0
  193.  
  194. End If
  195.  
  196. Console.WriteLine("AMIN:" & getTimeInAMHours)
  197. Console.WriteLine("AMOUT:" & getTimeOutAMHours)
  198. Console.WriteLine("PMIN:" & getTimeInPMHours)
  199. Console.WriteLine("PMOUT:" & getTimeOutPMHours)
  200. Console.WriteLine("lateSummary:" & lateSummary)
  201. Console.WriteLine("getWorkingHours:" & getWorkingHours)
  202. Console.WriteLine("Overtime:" & getOverTimeHours)
  203.  
  204. With ListView1.Items.Add(Format(getReader.Item("spdate"), "MMM dd, yyyy"))
  205.  
  206. .SubItems.Add(If(IsDBNull(getReader.Item("cAMIN")) = True, "-", Convert.ToDateTime(getReader.Item("AM_IN").ToString).ToString("hh:mm:ss tt")))
  207. .SubItems.Add(If(IsDBNull(getReader.Item("cAMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("AM_OUT").ToString).ToString("hh:mm:ss tt")))
  208. .SubItems.Add(If(IsDBNull(getReader.Item("cPMIN")) = True, "-", Convert.ToDateTime(getReader.Item("PM_IN").ToString).ToString("hh:mm:ss tt")))
  209. .SubItems.Add(If(IsDBNull(getReader.Item("cPMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("PM_OUT").ToString).ToString("hh:mm:ss tt")))
  210. .SubItems.Add(FormatNumber(iLate / 3600))
  211. .SubItems.Add(FormatNumber(If(iWorkingHours > 28800, 28800, iWorkingHours) / 3600))
  212. .SubItems.Add(FormatNumber(iOverTime / 3600))
  213.  
  214. End With
  215. ' set to nothing
  216. getStartingTimeIN = Nothing
  217. getEndingTimeOUT = Nothing
  218. getLateTimeHOurs = Nothing
  219.  
  220.  
  221. iWorkingHours = 0
  222. iLate = 0
  223. iOverTime = 0
  224.  
  225. End While
  226.  
  227. getReader.Close()
  228.  
  229. txtTotalLateHours.Text = Decimal.Round(lateSummary / 3600, 2).ToString("f2")
  230. txtTotalOverTimeHours.Text = Decimal.Round(getOverTimeHours / 3600, 2).ToString("f2")
  231. txtTotalWorkHours.Text = Decimal.Round(getWorkingHours / 3600, 2).ToString("f2")
  232.  
  233. End Sub
  234.  
  235. Private Sub frmViewWorkerRecords_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  236. Try
  237. ViewWorkerRecords()
  238. Catch ex As Exception
  239.  
  240. End Try
  241. End Sub
  242. End Class
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!