API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 27th, 2020
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.92 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.  
  31. Function ReturnAmIn(ByVal timeSeconds As Integer) As Integer
  32. Dim temp As Integer = 0
  33.  
  34. If timeSeconds < 25260 Then '07:01:00
  35. temp = 25200 ' 06:30:00
  36. Console.WriteLine("7:00")
  37. End If
  38.  
  39. If timeSeconds < 23460 Then '06:31:00
  40. temp = 23400 ' 06:30:00
  41. Console.WriteLine("30")
  42. End If
  43.  
  44. If timeSeconds < 22260 Then '06:11:00
  45. temp = 21600
  46. End If
  47. timeSeconds = temp
  48. Return timeSeconds
  49. End Function
  50.  
  51. Function ReturnBreakOut(ByVal timeSeconds As Integer) As Integer
  52. If timeSeconds > 43200 Then '12:00:00
  53. timeSeconds = 43200 ' 12:00:00
  54. End If
  55.  
  56. Return timeSeconds
  57. End Function
  58.  
  59. Function ReturnBreakIn(ByVal timeSeconds As Integer) As Integer
  60. If timeSeconds < 47460 Then '13:11:00
  61. timeSeconds = 46800 ' 13:00:00
  62. End If
  63.  
  64. Return timeSeconds
  65. End Function
  66.  
  67. Function ReturnPmOut(ByVal timeSeconds As Integer) As Integer
  68. If timeSeconds > 53999 And timeSeconds < 57660 Then ' 14:59:59 16:01:00.
  69. timeSeconds = 54000 ' 3PM
  70. End If
  71.  
  72. If getTimeOutPMHours > 64799 And getTimeOutPMHours < 65700 Then 'HERO? 6pm to 6:30pm
  73. timeSeconds = 64800 ' reset to 6PM
  74. End If
  75. Return timeSeconds
  76. End Function
  77.  
  78. Function ReturnHalfAfterNoonIn(ByVal timeSeconds As Integer) As Integer
  79. If timeSeconds > 32400 And timeSeconds < 40260 Then '09:00:00 11:11:00
  80. timeSeconds = 39600 ' 11:00:00
  81. End If
  82. Return timeSeconds
  83. End Function
  84.  
  85. Function ReturnHalfAfterNoonOut(ByVal timeSeconds As Integer) As Integer
  86. If timeSeconds > 32400 And timeSeconds < 40260 Then '09:00:00 11:11:00
  87. timeSeconds = 39600 ' 11:00:00
  88. End If
  89. Return timeSeconds
  90. End Function
  91.  
  92. Function ReturnHalfAmOut(ByVal timeSeconds As Integer) As Integer
  93. If timeSeconds < 53999 Then 'less than 3PM
  94. timeSeconds = 36000 ' 10:00
  95. End If
  96.  
  97. If timeSeconds > 53999 And timeSeconds < 57660 Then
  98. timeSeconds = 54000 ' 3PM
  99. End If
  100. Return timeSeconds
  101. End Function
  102.  
  103. ' get the worker's details
  104. Private Sub ViewWorkerRecords()
  105.  
  106. 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 ='" & _
  107. 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"
  108. getCommand = New MySqlCommand(getQuery, MySQLConnection)
  109. getReader = getCommand.ExecuteReader
  110. Dim temp4 As Integer = 0
  111.  
  112. While getReader.Read
  113. temp4 += 1
  114. ' getting the time in and time out of the worker
  115. getTimeInAMHours = If(IsDBNull(getReader.Item("cAMIN")) = True, 0, getReader.Item("cAMIN"))
  116. getTimeOutAMHours = If(IsDBNull(getReader.Item("cAMOUT")) = True, 0, getReader.Item("cAMOUT"))
  117. getTimeInPMHours = If(IsDBNull(getReader.Item("cPMIN")) = True, 0, getReader.Item("cPMIN"))
  118. getTimeOutPMHours = If(IsDBNull(getReader.Item("cPMOUT")) = True, 0, getReader.Item("cPMOUT"))
  119.  
  120.  
  121. getTimeInAMHours = If(IsDBNull(getReader.Item("cAMIN")) = True, 0, getReader.Item("cAMIN"))
  122. getTimeOutAMHours = If(IsDBNull(getReader.Item("cAMOUT")) = True, 0, getReader.Item("cAMOUT"))
  123. getTimeInPMHours = If(IsDBNull(getReader.Item("cPMIN")) = True, 0, getReader.Item("cPMIN"))
  124. getTimeOutPMHours = If(IsDBNull(getReader.Item("cPMOUT")) = True, 0, getReader.Item("cPMOUT"))
  125.  
  126. Console.Write(" AMIN:" & getTimeInAMHours)
  127. Console.Write(" AMOUT:" & getTimeOutAMHours)
  128. Console.Write(" PMIN:" & getTimeInPMHours)
  129. Console.Write(" PMOUT:" & getTimeOutPMHours)
  130.  
  131. If getTimeInAMHours <> 0 And getTimeOutAMHours <> 0 Then
  132. 'VALID
  133.  
  134. If getTimeInPMHours = 0 Then
  135. 'HALF DAY
  136. Console.WriteLine("HALF DAY!")
  137. If getTimeInAMHours < 28800 Then
  138. 'HALF DAY MORNING
  139.  
  140. getTimeInAMHours = returnAmIn(getTimeInAMHours)
  141.  
  142.  
  143. Else
  144. 'HALFDAY AFTERNOON
  145.  
  146. getTimeInAMHours = ReturnHalfAfterNoonIn(getTimeInAMHours)
  147.  
  148. End If
  149.  
  150. getTimeOutAMHours = ReturnHalfAmOut(getTimeOutAMHours)
  151.  
  152.  
  153.  
  154. Else
  155. 'WHOLE DAY
  156. getTimeInAMHours = ReturnAmIn(getTimeInAMHours)
  157. getTimeOutAMHours = ReturnBreakOut(getTimeOutAMHours)
  158. getTimeInPMHours = ReturnBreakIn(getTimeInPMHours)
  159. getTimeOutPMHours = ReturnPmOut(getTimeOutPMHours)
  160.  
  161. End If
  162.  
  163.  
  164. If getTimeInAMHours < 36000 Then
  165.  
  166. lateSummary += If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 21599, getTimeInAMHours - 21600, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 21599, getTimeInPMHours - 46800, 0))
  167. Else
  168.  
  169. lateSummary += getTimeInAMHours - 39600
  170. End If
  171.  
  172.  
  173. If getTimeInPMHours <> 0 Then
  174.  
  175.  
  176. 'For earylOut
  177. iLate = If(getTimeInAMHours = 0, 0, If(getTimeInAMHours > 32399, getTimeInAMHours - 28800, 0)) + If(getTimeInPMHours = 0, 0, If(getTimeInPMHours > 46799, getTimeInPMHours - 46800, 0))
  178. lateSummary += iLate
  179. Else
  180. iLate = getTimeInAMHours - 39600
  181. lateSummary += iLate
  182. End If
  183.  
  184.  
  185. iWorkingHours = (getTimeOutAMHours - getTimeInAMHours) + (getTimeOutPMHours - getTimeInPMHours)
  186. getWorkingHours += If(iWorkingHours > 28800, 28800, iWorkingHours)
  187. iOverTime = If(iWorkingHours > 28800, iWorkingHours - 28800, 0) 'if working hours > 8hours then OT yan
  188. getOverTimeHours += iOverTime
  189.  
  190. Else
  191. iWorkingHours = 0
  192. getWorkingHours += 0
  193. iOverTime = 0
  194. getOverTimeHours += 0
  195.  
  196. End If
  197.  
  198. Console.WriteLine("AMIN:" & getTimeInAMHours)
  199. Console.WriteLine("AMOUT:" & getTimeOutAMHours)
  200. Console.WriteLine("PMIN:" & getTimeInPMHours)
  201. Console.WriteLine("PMOUT:" & getTimeOutPMHours)
  202. Console.WriteLine("lateSummary:" & lateSummary)
  203. Console.WriteLine("getWorkingHours:" & getWorkingHours)
  204. Console.WriteLine("Overtime:" & getOverTimeHours)
  205.  
  206. With ListView1.Items.Add(Format(getReader.Item("spdate"), "MMM dd, yyyy"))
  207.  
  208. .SubItems.Add(If(IsDBNull(getReader.Item("cAMIN")) = True, "-", Convert.ToDateTime(getReader.Item("AM_IN").ToString).ToString("hh:mm:ss tt")))
  209. .SubItems.Add(If(IsDBNull(getReader.Item("cAMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("AM_OUT").ToString).ToString("hh:mm:ss tt")))
  210. .SubItems.Add(If(IsDBNull(getReader.Item("cPMIN")) = True, "-", Convert.ToDateTime(getReader.Item("PM_IN").ToString).ToString("hh:mm:ss tt")))
  211. .SubItems.Add(If(IsDBNull(getReader.Item("cPMOUT")) = True, "-", Convert.ToDateTime(getReader.Item("PM_OUT").ToString).ToString("hh:mm:ss tt")))
  212. .SubItems.Add(FormatNumber(iLate / 3600))
  213. .SubItems.Add(FormatNumber(If(iWorkingHours > 28800, 28800, iWorkingHours) / 3600))
  214. .SubItems.Add(FormatNumber(iOverTime / 3600))
  215.  
  216. End With
  217. ' set to nothing
  218. getStartingTimeIN = Nothing
  219. getEndingTimeOUT = Nothing
  220. getLateTimeHOurs = Nothing
  221.  
  222.  
  223. iWorkingHours = 0
  224. iLate = 0
  225. iOverTime = 0
  226.  
  227. End While
  228.  
  229. getReader.Close()
  230.  
  231. txtTotalLateHours.Text = Decimal.Round(lateSummary / 3600, 2).ToString("f2")
  232. txtTotalOverTimeHours.Text = Decimal.Round(getOverTimeHours / 3600, 2).ToString("f2")
  233. txtTotalWorkHours.Text = Decimal.Round(getWorkingHours / 3600, 2).ToString("f2")
  234.  
  235. End Sub
  236.  
  237. Private Sub frmViewWorkerRecords_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  238. Try
  239. ViewWorkerRecords()
  240. Catch ex As Exception
  241.  
  242. End Try
  243. End Sub
  244. 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!