HSKIN-MIS-SYS-SIU/GRAMS-SYS/04、考勤管理/考勤明細表.vb

Imports Microsoft.Office.Interop.Excel.XlUnderlineStyle
Imports Microsoft.Office.Interop.Excel.Constants
Imports Microsoft.Office.Interop.Excel.XlBordersIndex
Imports Microsoft.Office.Interop.Excel.XlLineStyle
Imports Microsoft.Office.Interop.Excel.XlBorderWeight
Imports Microsoft.Office.Interop.Excel.XlThemeFont
Imports Microsoft.Office.Interop.Excel.XlThemeColor
Imports Microsoft.Office.Interop.Excel.XlWindowState
Imports Microsoft.Office.Interop.Excel.XlPageOrientation
Imports Microsoft.Office.Interop.Excel
Public Class 考勤明細表
    ReadOnly ds As New DataSet : ReadOnly ds1 As New DataSet : ReadOnly ds2 As New DataSet : ReadOnly ds3 As New DataSet : ReadOnly ds4 As New DataSet : ReadOnly ds5 As New DataSet
    ReadOnly ds6 As New DataSet : ReadOnly ds7 As New DataSet : ReadOnly ds8 As New DataSet
    Dim 編號, 到職日 As String
    Dim xlApp As Application : Dim xlBook As Workbook : Dim xlSheet As Worksheet
    Dim T, S, F, M, T1, S1, TT, SS, FF, MM, FFF, MMM, 平日班, 假日班, 假加, 假加一, 假加二, 假加時1, 假加分1, 假加時2, 假加分2, 國假, 國假1, 國假2, 平日加, 平日加1, 平日加2,
            正常時, 正常時1, 正常時2, 正常時3, 正常時4, 加班時, 加班時1, 加班時2, 加班時3, 加班時4, 加班時5, 加班時6, 加班時7, 加班時8, 加班時9, 加班時10, 加班時11, 加班時12,
            加班時13, 加班時14, 加班時15, 加班時16, 加班時17, 加班時18, 加班時19, 加班時20, 加班時21, 加班時22, 加班時23, 加班時24, 加班時25, 加班時26, 加班時27, 加班時28, 加班時29,
            當天總分, 彈性扣時, 請假日 As Double
    Private Sub 讀取人員資料表()
        人員_dgv.DataSource = Nothing : ds.Clear()
        人員_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        人員_dgv.ColumnHeadersHeight = 25
        人員_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_人員()
        da.Fill(ds) : 人員_dgv.DataSource = ds.Tables(0) : conn.Close()
        人員_dgv.Columns(0).FillWeight = 70 : 人員_dgv.Columns(1).Visible = False : 人員_dgv.Columns(2).FillWeight = 100 : 人員_dgv.Columns(3).Visible = False
        人員_dgv.Columns(4).Visible = False : 人員_dgv.Columns(5).Visible = False : 人員_dgv.Columns(6).Visible = False : 人員_dgv.Columns(7).Visible = False
        人員_dgv.Columns(8).Visible = False : 人員_dgv.Columns(9).Visible = False : 人員_dgv.Columns("到職日").Visible = False
        For i As Integer = 0 To 人員_dgv.Rows.Count - 1
            人員_dgv.Rows(i).Cells("No.").Value = i + 1
        Next
    End Sub
    Private Sub Set_考勤明細()
        月清單_dgv.DataSource = Nothing : ds1.Clear()
        月清單_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        月清單_dgv.ColumnHeadersHeight = 40
        月清單_dgv.AllowUserToAddRows = False
        If CC(16) = True Then : 月清單_dgv.RowTemplate.Height = 25 : Else : 月清單_dgv.RowTemplate.Height = 23 : End If
        PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : SQL_考勤明細表_月份考勤()
        da.Fill(ds1) : 月清單_dgv.DataSource = ds1.Tables(0) : conn.Close()

        月清單_dgv.Columns(0).FillWeight = 100 : 月清單_dgv.Columns(1).FillWeight = 60 : 月清單_dgv.Columns(2).FillWeight = 90 : 月清單_dgv.Columns(3).FillWeight = 100
        月清單_dgv.Columns(4).FillWeight = 80 : 月清單_dgv.Columns(5).Visible = False : 月清單_dgv.Columns(6).Visible = False : 月清單_dgv.Columns(7).Visible = False
        月清單_dgv.Columns(8).FillWeight = 80 : 月清單_dgv.Columns(9).FillWeight = 80 : 月清單_dgv.Columns(10).FillWeight = 60 : 月清單_dgv.Columns(11).FillWeight = 80
        月清單_dgv.Columns(12).FillWeight = 80 : 月清單_dgv.Columns(13).FillWeight = 60 : 月清單_dgv.Columns(14).FillWeight = 80 : 月清單_dgv.Columns(15).FillWeight = 80
        月清單_dgv.Columns(16).FillWeight = 60 : 月清單_dgv.Columns(17).FillWeight = 60 : 月清單_dgv.Columns(18).Visible = False : 月清單_dgv.Columns(19).FillWeight = 60
        月清單_dgv.Columns(20).FillWeight = 60 : 月清單_dgv.Columns(21).FillWeight = 40 : 月清單_dgv.Columns(22).FillWeight = 40 : 月清單_dgv.Columns(23).FillWeight = 100
        月清單_dgv.Columns(24).FillWeight = 80 : 月清單_dgv.Columns(25).FillWeight = 150 : 月清單_dgv.Columns("休息1").Visible = False : 月清單_dgv.Columns("休息2").Visible = False
        月清單_dgv.Columns("半天").Visible = False : 月清單_dgv.Columns("班別").Visible = False : 月清單_dgv.Columns("最後下診").Visible = False
        月清單_dgv.Columns("申請時數").Visible = False
        讀取打卡時間1() : 讀取打卡時間2()
        Dim S2, F2 As String
        For I As Integer = 0 To 月清單_dgv.Rows.Count - 1
            MyModule1.進度條()
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And
                月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString = "" Then
                月清單_dgv.Rows(I).Cells("申請狀態").Value = "."
            End If
            月清單_dgv.Rows(I).Cells("一段工時").Value = "" : 月清單_dgv.Rows(I).Cells("二段工時").Value = "" : 月清單_dgv.Rows(I).Cells("加班工時").Value = ""
            月清單_dgv.Rows(I).Cells("休息1").Value = "" : 月清單_dgv.Rows(I).Cells("休息2").Value = "" : 月清單_dgv.Rows(I).Cells("一段加班").Value = ""
            月清單_dgv.Rows(I).Cells("二段加班").Value = "" : 月清單_dgv.Rows(I).Cells("加班下班").Value = "" : 月清單_dgv.Rows(I).Cells("加班上班").Value = ""
            月清單_dgv.Rows(I).Cells("二段下班").Value = "" : 月清單_dgv.Rows(I).Cells("二段上班").Value = "" : 月清單_dgv.Rows(I).Cells("一段下班").Value = ""
            If IsDBNull(月清單_dgv.Rows(I).Cells("半天").Value) = True Then : 月清單_dgv.Rows(I).Cells("半天").Value = False : End If
            If IsDBNull(月清單_dgv.Rows(I).Cells("班別").Value) = True Then : 月清單_dgv.Rows(I).Cells("班別").Value = "" : End If
            If 月清單_dgv.Rows(I).Cells("班別").Value.ToString = "休" Then : Else : 月清單_dgv.Rows(I).Cells("班別").Value = Strings.Right(月清單_dgv.Rows(I).Cells("班別").Value, 2) : End If
            '--------------------------讀入資料------------------------------------------------------------------------------------------------------------------------------------
            PA3 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("日期").Value, 2))
            PA4 = 月清單_dgv.Rows(I).Cells("日期").Value.ToString
            If 時間讀取1_dgv.Rows.Count > 0 Then
                For II As Integer = 0 To 時間讀取1_dgv.Rows.Count - 1
                    If PA4 = 時間讀取1_dgv.Rows(II).Cells("日期").Value.ToString Then
                        月清單_dgv.Rows(I).Cells("一段上班").Value = 時間讀取1_dgv.Rows(II).Cells("打卡時間").Value.ToString : Exit For
                    Else : 月清單_dgv.Rows(I).Cells("一段上班").Value = ""
                    End If
                Next
                For II As Integer = 0 To 班別_dgv.Rows.Count - 1
                    If 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString <> "" Then
                        If FormatDateTime(班別_dgv.Rows(II).Cells("時段1").Value.ToString, DateFormat.ShortTime) <
                           FormatDateTime(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DateFormat.ShortTime) And
                           FormatDateTime(班別_dgv.Rows(II).Cells("時段2").Value.ToString, DateFormat.ShortTime) >
                           FormatDateTime(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DateFormat.ShortTime) Then
                            月清單_dgv.Rows(I).Cells("班別判斷").Value = 班別_dgv.Rows(II).Cells("時段").Value.ToString
                            月清單_dgv.Rows(I).Cells("休息1").Value = 班別_dgv.Rows(II).Cells("一段休息").Value.ToString
                            月清單_dgv.Rows(I).Cells("休息2").Value = 班別_dgv.Rows(II).Cells("二段休息").Value.ToString
                            DTPS(0) = 班別_dgv.Rows(II).Cells("時段2").Value.ToString : DTPS(1) = 班別_dgv.Rows(II).Cells("時段4").Value.ToString()
                            DTPS(2) = 班別_dgv.Rows(II).Cells("時段4").Value.ToString : DTPS(3) = 班別_dgv.Rows(II).Cells("時段6").Value.ToString()
                            DTPS(4) = 班別_dgv.Rows(II).Cells("時段6").Value.ToString
                            Exit For
                        End If
                    End If
                    If II = 班別_dgv.Rows.Count - 1 Then
                        For III As Integer = 0 To 班別_dgv.Rows.Count - 1
                            If Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, 2)) <= Val(Strings.Left(班別_dgv.Rows(III).Cells("時段").Value.ToString, 2)) Then
                                月清單_dgv.Rows(I).Cells("班別判斷").Value = 班別_dgv.Rows(III).Cells("時段").Value.ToString
                                月清單_dgv.Rows(I).Cells("休息1").Value = 班別_dgv.Rows(II).Cells("一段休息").Value.ToString
                                月清單_dgv.Rows(I).Cells("休息2").Value = 班別_dgv.Rows(II).Cells("二段休息").Value.ToString
                                DTPS(0) = 班別_dgv.Rows(III).Cells("時段2").Value.ToString : DTPS(1) = 班別_dgv.Rows(III).Cells("時段4").Value.ToString()
                                DTPS(2) = 班別_dgv.Rows(III).Cells("時段4").Value.ToString : DTPS(3) = 班別_dgv.Rows(III).Cells("時段6").Value.ToString()
                                DTPS(4) = 班別_dgv.Rows(III).Cells("時段6").Value.ToString
                                Exit For
                            End If
                            月清單_dgv.Rows(I).Cells("班別判斷").Value = "打卡異常"
                        Next
                    End If
                Next
            Else
                月清單_dgv.Rows(I).Cells("一段上班").Value = "" : 月清單_dgv.Rows(I).Cells("班別判斷").Value = ""
            End If
            月清單_dgv.Rows(I).Cells("一段下班").Value = "" : 月清單_dgv.Rows(I).Cells("二段上班").Value = ""
            Dim SD(2), 位置, 數量 As Integer : 數量 = 0
            For II As Integer = 0 To 時間讀取2_dgv.Rows.Count - 1
                If PA4 = 時間讀取2_dgv.Rows(II).Cells("日期").Value.ToString Then
                    If 數量 = 0 Then
                    ElseIf 數量 = 1 Then
                        月清單_dgv.Rows(I).Cells("一段下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value
                    ElseIf 數量 = 2 Then
                        月清單_dgv.Rows(I).Cells("二段上班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value
                    ElseIf 數量 = 3 Then
                        月清單_dgv.Rows(I).Cells("二段下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value
                    ElseIf 數量 = 4 Then
                        月清單_dgv.Rows(I).Cells("加班上班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value
                    ElseIf 數量 = 5 Then
                        月清單_dgv.Rows(I).Cells("加班下班").Value = 時間讀取2_dgv.Rows(II).Cells("打卡時間").Value
                    End If : 數量 += 1
                End If
            Next
            '--------------------------打卡位置調整------------------------------------------------------------------------------------------------------------------------------------
            If 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString = 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString Then
                月清單_dgv.Rows(I).Cells("一段下班").Value = ""
            End If
            For d As Integer = 1 To 4
                If 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString <> "" Then
                    月清單_dgv.Rows(I).Cells("加班上班").Value = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : 月清單_dgv.Rows(I).Cells("加班下班").Value = ""
                End If
                If 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString <> "" Then
                    月清單_dgv.Rows(I).Cells("二段下班").Value = 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString : 月清單_dgv.Rows(I).Cells("加班上班").Value = ""
                End If
                If 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString <> "" Then
                    月清單_dgv.Rows(I).Cells("二段上班").Value = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : 月清單_dgv.Rows(I).Cells("二段下班").Value = ""
                End If
                If 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString = "" And 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString <> "" Then
                    月清單_dgv.Rows(I).Cells("一段下班").Value = 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString : 月清單_dgv.Rows(I).Cells("二段上班").Value = ""
                End If
            Next

            '--------------------------工時計算------------------------------------------------------------------------------------------------------------------------------------
            Dim QASW1 As Integer : Dim QASW2 As Integer : Dim QASW3 As Integer
            Dim DD As String
            '-如果下班時間超過晚上12點時的分段計算------------------------------------------
            If Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "01" Or
                Strings.Left(月清單_dgv.Rows(I).Cells("一段下班").Value.ToString, 2) = "02" Then
                DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("一段下班").Value) : QASW1 = TSFM
            Else : DD = 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString : QASW1 = 0 : End If
            '-第一段上班時間正常打卡計算------------------------------------------
            If 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString <> "" And DD <> "" Then
                Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString, DD)
                TSFM += QASW1
                If 時薪制_ch.Checked = False Then
                    If 月清單_dgv.Rows(I).Cells("半天").Value = False Then
                        '-第二段上下班都沒打卡，但是累計工時大於4小時(14400)，先扣除一小時休息，第一段工時等於4小時，在把時數放到第二段工時------------------------------------------
                        If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then
                            QASW2 = TSFM - 14400 - (Val(月清單_dgv.Rows(I).Cells("休息1").Value) * 3600)
                            If QASW2 < 0 Then : QASW2 = 0 : End If
                            TSFM = 14400
                            '-如果加班上下班都沒打卡，但扣除第一段時數的累計工時還是大於4小時(14400)，先扣除一小時休息，第二段工時等於4小時，在把時數放到加班段工時------------------------------------------
                            If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then
                                QASW3 = QASW2 - 14400 - (Val(月清單_dgv.Rows(I).Cells("休息2").Value) * 3600)
                                If QASW3 < 0 Then : QASW3 = 0 : End If
                                QASW2 = 14400
                                T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                                S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                                月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2
                            End If
                            T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                            S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                            月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2
                        End If
                    Else
                        If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then
                            QASW2 = TSFM - 14400
                            If QASW2 < 0 Then : QASW2 = 0 : End If
                            TSFM = 14400
                            '-如果加班上下班都沒打卡，但扣除第一段時數的累計工時還是大於4小時(14400)，先扣除一小時休息，第二段工時等於4小時，在把時數放到加班段工時------------------------------------------
                            If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then
                                QASW3 = QASW2 - 14400
                                If QASW3 < 0 Then : QASW3 = 0 : End If
                                QASW2 = 14400
                                T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                                S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                                月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2
                            End If
                            T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                            S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                            月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2
                        End If
                    End If
                Else
                    If (月清單_dgv.Rows(I).Cells("二段上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("二段下班").Value = "") And TSFM > 14400 Then
                        QASW2 = TSFM - 14400
                        If QASW2 < 0 Then : QASW2 = 0 : End If
                        TSFM = 14400
                        If (月清單_dgv.Rows(I).Cells("加班上班").Value.ToString = "" Or 月清單_dgv.Rows(I).Cells("加班下班").Value = "") And QASW2 > 14400 Then
                            QASW3 = QASW2 - 14400
                            If QASW3 < 0 Then : QASW3 = 0 : End If
                            QASW2 = 14400
                            T = QASW3 \ 86400 : T1 = QASW3 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                            S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                            月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2
                        End If
                        T = QASW2 \ 86400 : T1 = QASW2 Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                        S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                        月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2
                    End If
                End If
                T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                月清單_dgv.Rows(I).Cells("一段工時").Value = S2 & " : " & F2
            End If

            '-如果下班時間超過晚上12點時的分段計算------------------------------------------
            If Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "01" Or
                Strings.Left(月清單_dgv.Rows(I).Cells("二段下班").Value.ToString, 2) = "02" Then
                DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("二段下班").Value) : QASW1 = TSFM
            Else : DD = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : QASW1 = 0 : End If
            '-第二段上班時間正常打卡計算------------------------------------------
            If 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString <> "" And DD <> "" Then
                Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString, DD)
                TSFM += QASW1
                T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                月清單_dgv.Rows(I).Cells("二段工時").Value = S2 & " : " & F2
            End If

            '-如果下班時間超過晚上12點時的分段計算------------------------------------------
            If Strings.Left(月清單_dgv.Rows(I).Cells("加班下班").Value.ToString, 2) = "00" Then
                DD = "23:59:59" : Dim TSFM As Integer = DateDiff("s", "00:00:00", 月清單_dgv.Rows(I).Cells("二段下班").Value) : QASW1 = TSFM
            Else : DD = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : QASW1 = 0 : End If
            '-加班上班時間正常打卡計算------------------------------------------
            If 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString <> "" And DD <> "" Then
                Dim TSFM As Integer = DateDiff("s", 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString, DD)
                TSFM += QASW1
                T = TSFM \ 86400 : T1 = TSFM Mod 86400 : S = T1 \ 3600 : S1 = T1 Mod 3600 : F = S1 \ 60 : M = S1 Mod 60
                S2 = T * 24 + S : If M > 30 Then : F += 1 : End If : F2 = F
                月清單_dgv.Rows(I).Cells("加班工時").Value = S2 & " : " & F2
            End If

            當天總分 = (Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) +
                        Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) +
                        Val(Strings.Left(月清單_dgv.Rows(I).Cells("加班工時").Value.ToString, 2))) * 60 +
                       (Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) +
                        Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) +
                        Val(Strings.Right(月清單_dgv.Rows(I).Cells("加班工時").Value.ToString, 2)))
            If 當天總分 <= 240 Then
                月清單_dgv.Rows(I).Cells("一段工時").Value = 當天總分 \ 60 & " : " & 當天總分 Mod 60
                月清單_dgv.Rows(I).Cells("二段工時").Value = "" : 月清單_dgv.Rows(I).Cells("加班工時").Value = ""
            ElseIf 當天總分 > 240 Then
                月清單_dgv.Rows(I).Cells("一段工時").Value = "4 : 0"
                月清單_dgv.Rows(I).Cells("二段工時").Value = (當天總分 - 240) \ 60 & " : " & (當天總分 - 240) Mod 60
                If (當天總分 - 240) <= 240 Then : Else
                    月清單_dgv.Rows(I).Cells("二段工時").Value = "4 : 0"
                    月清單_dgv.Rows(I).Cells("一段加班").Value = (當天總分 - 240 - 240) \ 60 & " : " & (當天總分 - 240 - 240) Mod 60
                    If (當天總分 - 240 - 240) <= 120 Then : Else
                        月清單_dgv.Rows(I).Cells("一段加班").Value = "2 : 0"
                        月清單_dgv.Rows(I).Cells("二段加班").Value = (當天總分 - 240 - 240 - 120) \ 60 & " : " & (當天總分 - 240 - 240 - 120) Mod 60
                        If (當天總分 - 240 - 240 - 120) <= 120 Then : Else
                            月清單_dgv.Rows(I).Cells("二段加班").Value = "2 : 0"
                        End If
                    End If
                End If
            End If
            If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString = "0 : 0" Then
                月清單_dgv.Rows(I).Cells("一段工時").Value = ""
            End If
            '--------------------------假日標色------------------------------------------------------------------------------------------------------------------------------------
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightYellow
            End If

            '--------------------------加班未申請去除------------------------------------------------------------------------------------------------------------------------------------
            If 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString <> "已核准" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And
                月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And
                月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString <> "." Then
                月清單_dgv.Rows(I).Cells("一段加班").Value = "" : 月清單_dgv.Rows(I).Cells("二段加班").Value = ""
            End If
        Next

        平日班 = 0 : 正常時1 = 0 : 正常時2 = 0 : 正常時3 = 0 : 正常時4 = 0 : 加班時9 = 0 : 加班時10 = 0 : 加班時11 = 0 : 加班時12 = 0 : 加班時8 = 0 : 加班時21 = 0
        加班時17 = 0 : 加班時18 = 0 : 加班時19 = 0 : 加班時20 = 0 : 加班時23 = 0 : 加班時24 = 0 : 加班時25 = 0 : 加班時26 = 0 : 假日班 = 0 : 加班時27 = 0 : 請假日 = 0
        For I As Integer = 0 To 月清單_dgv.Rows.Count - 1
            If 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" And
               月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "特休" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "未到" And
               月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "離職" Then
                請假日 += 1
            End If
            '---------------------------平日
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期六" And 月清單_dgv.Rows(I).Cells("星期").Value.ToString <> "星期日" And
               月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "國定假日" And 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "" Then
                加班時27 += 1
                If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" Then
                    平日班 += 1
                    正常時1 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 正常時2 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" Then
                    正常時3 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 正常時4 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" Then
                    加班時9 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時10 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" Then
                    加班時11 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時12 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2))
                End If
            End If
            '---------------------------星期六
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then
                If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "0 : 0" Then
                    假日班 += 1
                    加班時3 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 加班時4 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2))
                    加班時7 = 加班時3 + (加班時4 / 60)
                    If 加班時7 > 2 Then
                        加班時8 += 2 : 加班時21 += (加班時7 - 2)
                    Else
                        加班時8 += 加班時7
                    End If
                End If
                If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "0 : 0" Then
                    加班時5 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 加班時6 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2))
                    加班時21 += 加班時5 + (加班時6 / 60)
                End If
                If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "0 : 0" Then
                    加班時13 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時14 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2))
                    加班時21 += (加班時13 + (加班時14 / 60))
                End If
                If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "0 : 0" Then
                    加班時15 = Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時16 = Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2))
                    加班時21 += (加班時15 + (加班時16 / 60))
                End If
            End If

            '--------------------------星期日或國定假日
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Or 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then
                If 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString <> "0 : 0" Then
                    假日班 += 1
                    加班時17 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2)) : 加班時18 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段工時").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString <> "0 : 0" Then
                    加班時19 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2)) : 加班時20 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段工時").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString <> "0 : 0" Then
                    月清單_dgv.Rows(I).Cells("一段加班").Value = "0 : 0"
                    加班時23 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2)) : 加班時24 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("一段加班").Value.ToString, 2))
                End If
                If 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "" And 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString <> "0 : 0" Then
                    月清單_dgv.Rows(I).Cells("二段加班").Value = "0 : 0"
                    加班時25 += Val(Strings.Left(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2)) : 加班時26 += Val(Strings.Right(月清單_dgv.Rows(I).Cells("二段加班").Value.ToString, 2))
                End If
            End If

        Next
        FF = (正常時2 + 正常時4) / 60
        正常時 = 正常時1 + 正常時3 + FF
        當月平日天_tb.Text = 平日班 + 假日班 : 當月假日天_tb.Text = 請假日
        常日總_tb.Text = Strings.Format(正常時, "#,##0.00")
        當月平日一段_tb.Text = Strings.Format((加班時9 + (加班時10 / 60)), "#,##0.00")
        當月平日二段_tb.Text = Strings.Format((加班時11 + (加班時12 / 60)), "#,##0.00")
        一段假日加_tb.Text = Strings.Format(加班時8, "#,##0.00")
        二段假日加_tb.Text = Strings.Format(加班時21, "#,##0.00")
        國假日加_tb.Text = Strings.Format(加班時17 + 加班時19 + 加班時23 + 加班時25 + (加班時18 + 加班時20 + 加班時24 + 加班時26) / 60, "#,##0.00")
        Set_時薪制() : Set_彈性工時()
        加班總_tb.Text = Strings.Format((加班時9 + (加班時10 / 60)) + (加班時11 + (加班時12 / 60)) + 加班時8 + 加班時21 +
                                 (加班時17 + 加班時19 + 加班時23 + 加班時25 + (加班時18 + 加班時20 + 加班時24 + 加班時26) / 60), "#,##0.00")
        特修補錢_tb.Text = "0" : 未到離職_tb.Text = "0" : 事假_tb.Text = "0" : 婚假_tb.Text = "0" : 喪假_tb.Text = "0" : 特休_tb.Text = "0" : 特休未休_nud.Value = 0
        薪資計算()
    End Sub
    Private Sub 薪資計算()
        If CC(16) = True Then
            If 時薪制_ch.Checked = True Then
                月薪_tb.Text = Strings.Format((CDbl(時薪_tb.Text) * CDbl(常日總_tb.Text)), "#,##0.0")
            Else
                時薪_tb.Text = Strings.Format((CDbl(月薪_tb.Text) / 30 / 8), "#,##0.0")
            End If
            Dim ds As New DataSet
            假期統計_dgv.DataSource = Nothing : ds.Clear()
            假期統計_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
            假期統計_dgv.ColumnHeadersHeight = 25
            假期統計_dgv.AllowUserToAddRows = False
            PA = 編號 : PA1 = 年份_cb.Text & "/" & 月份_cb.Text : SQL_休假統計1()
            da.Fill(ds) : 假期統計_dgv.DataSource = ds.Tables(0) : conn.Close()

            Dim 到離, 事假, 病假, 特休, 婚假, 喪假 As Double : 到離 = 0 : 事假 = 0 : 病假 = 0 : 特休 = 0 : 婚假 = 0 : 喪假 = 0
            For I As Integer = 0 To 假期統計_dgv.Rows.Count - 1
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "未到" Or 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "離職" Then
                    到離 += 假期統計_dgv.Rows(I).Cells("天數").Value.ToString
                End If
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "事假" Then : 事假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "病假" Then : 病假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "特休" Then : 特休 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "婚假" Then : 婚假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If
                If 假期統計_dgv.Rows(I).Cells(0).Value.ToString = "喪假" Then : 喪假 += 假期統計_dgv.Rows(I).Cells("總時數").Value.ToString : End If
            Next

            病假_tb.Text = Strings.Format((病假 * CDbl(時薪_tb.Text) / 2), "#,##0.0")
            If 時薪制_ch.Checked = False Then
                未到離職_tb.Text = Strings.Format((到離 * 8 * CDbl(時薪_tb.Text)), "#,##0.0")
                事假_tb.Text = Strings.Format((事假 * CDbl(時薪_tb.Text)), "#,##0.0")
            Else
                婚假_tb.Text = Strings.Format((婚假 * CDbl(時薪_tb.Text)), "#,##0.0")
                喪假_tb.Text = Strings.Format((喪假 * CDbl(時薪_tb.Text)), "#,##0.0")
                特休_tb.Text = Strings.Format((特休 * CDbl(時薪_tb.Text)), "#,##0.0")
            End If
            '--------------------------第一種應付小計計算---------------------------------------------------
            第1段加班費1_tb.Text = Strings.Format(((CDbl(當月平日一段_tb.Text) + CDbl(一段假日加_tb.Text)) * CDbl(時薪_tb.Text) * 1.34), "#,##0.0")
            第2段加班費1_tb.Text = Strings.Format(((CDbl(當月平日二段_tb.Text) + CDbl(二段假日加_tb.Text)) * CDbl(時薪_tb.Text) * 1.67), "#,##0.0")
            第3段加班費1_tb.Text = Strings.Format((CDbl(國假日加_tb.Text) * CDbl(時薪_tb.Text) * 2), "#,##0.0")
            加班費1_tb.Text = Strings.Format((CDbl(第1段加班費1_tb.Text) + CDbl(第2段加班費1_tb.Text) + CDbl(第3段加班費1_tb.Text)), "#,##0.0")
            If 時薪制_ch.Checked = False Then
                應付小計1_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費1_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) +
                                    CDbl(特修補錢_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0")
            Else
                應付小計1_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費1_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) +
                                    CDbl(特修補錢_tb.Text) + (CDbl(病假_tb.Text) * 2) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + CDbl(特休_tb.Text) +
                                    其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0")
            End If
            '--------------------------第二種應付小計計算-----------------------------------------------------
            第1段加班費2_tb.Text = Strings.Format(((CDbl(當月平日一段1_tb.Text) + CDbl(一段假日加1_tb.Text)) * CDbl(時薪_tb.Text) * 1.34), "#,##0.0")
            第2段加班費2_tb.Text = Strings.Format(((CDbl(當月平日二段1_tb.Text) + CDbl(二段假日加1_tb.Text)) * CDbl(時薪_tb.Text) * 1.67), "#,##0.0")
            第3段加班費2_tb.Text = Strings.Format((CDbl(國假日加1_tb.Text) * CDbl(時薪_tb.Text) * 2), "#,##0.0")
            加班費2_tb.Text = Strings.Format((CDbl(第1段加班費2_tb.Text) + CDbl(第2段加班費2_tb.Text) + CDbl(第3段加班費2_tb.Text)), "#,##0.0")
            If 時薪制_ch.Checked = False Then
                應付小計2_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費2_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) +
                                    CDbl(特修補錢_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0")
            Else
                應付小計2_tb.Text = Strings.Format((CDbl(月薪_tb.Text) + CDbl(加班費2_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) +
                                    CDbl(特修補錢_tb.Text) + (CDbl(病假_tb.Text) * 2) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + CDbl(特休_tb.Text) +
                                    其他加_nud.Value - CDbl(未到離職_tb.Text)), "#,##0.0")
            End If
            '--------------------------第一種實付小計計算---------------------------------------------------
            If 時薪制_ch.Checked = False Then
                實付小計1_tb.Text = Strings.Format((CDbl(應付小計1_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) -
                                    CDbl(事假_tb.Text) - 其他減_nud.Value), "#,##0.0")
            Else
                實付小計1_tb.Text = Strings.Format((CDbl(應付小計1_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) -
                                    其他減_nud.Value), "#,##0.0")
            End If
            '--------------------------第二種實付小計計算---------------------------------------------------
            If 時薪制_ch.Checked = False Then
                實付小計2_tb.Text = Strings.Format((CDbl(應付小計2_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) -
                                    CDbl(事假_tb.Text) - 其他減_nud.Value), "#,##0.0")
            Else
                實付小計2_tb.Text = Strings.Format((CDbl(應付小計2_tb.Text) - CDbl(健保費_tb.Text) - CDbl(勞保費_tb.Text) - CDbl(病假_tb.Text) -
                                    其他減_nud.Value), "#,##0.0")
            End If
        End If
    End Sub
    Private Sub 特休未休_NUD_ValueChanged(sender As Object, e As EventArgs) Handles 特休未休_nud.ValueChanged
        特修補錢_tb.Text = Strings.Format((特休未休_nud.Value * 8 * CDbl(時薪_tb.Text)), "#,##0.0") : 薪資計算()
    End Sub
    Private Sub 其他_nud_ValueChanged(sender As Object, e As EventArgs) Handles 其他減_nud.ValueChanged
        薪資計算()
    End Sub
    Private Sub 時薪彈性_bt_Click(sender As Object, e As EventArgs) Handles 時薪彈性_bt.Click
        當月應上天數_tb.Text = 加班時27 : 上班總工時_tb.Text = Strings.Format(加班時27 * 8, "#,##0.00") : 應上未上時數_tb.Text = Strings.Format((加班時27 * 8) - 正常時, "#,##0.00")
        Set_彈性工時()
        特修補錢_tb.Text = "0" : 未到離職_tb.Text = "0" : 事假_tb.Text = "0" : 婚假_tb.Text = "0" : 喪假_tb.Text = "0" : 特休_tb.Text = "0" : 特休未休_nud.Value = 0
        薪資計算()
    End Sub
    Private Sub Set_時薪制()
        If 時薪制_ch.Checked = False Then
            當月應上天數_tb.Text = 加班時27
            上班總工時_tb.Text = Strings.Format(加班時27 * 8, "#,##0.00")
            應上未上時數_tb.Text = Strings.Format((加班時27 * 8) - 正常時, "#,##0.00")
        Else
            當月應上天數_tb.Text = "0" : 上班總工時_tb.Text = "0.00" : 應上未上時數_tb.Text = "0.00"
        End If
    End Sub
    Private Sub Set_彈性工時()
        If 彈性工時_ch.Checked = False Then
            當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text
            二段假日加1_tb.Text = 二段假日加_tb.Text : 國假日加1_tb.Text = 國假日加_tb.Text
        Else
            彈性扣時 = 應上未上時數_tb.Text
            If Val(國假日加_tb.Text) >= 彈性扣時 Then
                國假日加1_tb.Text = Strings.Format(Val(國假日加_tb.Text) - 彈性扣時, "#,##0.00")
                當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text
                二段假日加1_tb.Text = 二段假日加_tb.Text
            ElseIf Val(國假日加_tb.Text) < 彈性扣時 Then
                彈性扣時 -= Val(國假日加_tb.Text) : 國假日加1_tb.Text = "0.00"
                If Val(二段假日加_tb.Text) >= 彈性扣時 Then
                    二段假日加1_tb.Text = Strings.Format(Val(二段假日加_tb.Text) - 彈性扣時, "#,##0.00")
                    當月平日一段1_tb.Text = 當月平日一段_tb.Text : 當月平日二段1_tb.Text = 當月平日二段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text
                ElseIf Val(二段假日加_tb.Text) < 彈性扣時 Then
                    彈性扣時 -= Val(二段假日加_tb.Text) : 二段假日加1_tb.Text = "0.00"
                    If Val(當月平日二段_tb.Text) >= 彈性扣時 Then
                        當月平日二段1_tb.Text = Strings.Format(Val(當月平日二段_tb.Text) - 彈性扣時, "#,##0.00")
                        當月平日一段1_tb.Text = 當月平日一段_tb.Text : 一段假日加1_tb.Text = 一段假日加_tb.Text
                    ElseIf Val(當月平日二段_tb.Text) < 彈性扣時 Then
                        彈性扣時 -= Val(當月平日二段_tb.Text) : 當月平日二段1_tb.Text = "0.00"
                        If Val(一段假日加_tb.Text) >= 彈性扣時 Then
                            一段假日加1_tb.Text = Strings.Format(Val(一段假日加_tb.Text) - 彈性扣時, "#,##0.00")
                            當月平日一段1_tb.Text = 當月平日一段_tb.Text
                        ElseIf Val(一段假日加_tb.Text) < 彈性扣時 Then
                            彈性扣時 -= Val(一段假日加_tb.Text) : 一段假日加1_tb.Text = "0.00"
                            If Val(當月平日一段_tb.Text) >= 彈性扣時 Then
                                當月平日一段1_tb.Text = Strings.Format(Val(當月平日一段_tb.Text) - 彈性扣時, "#,##0.00")
                            ElseIf Val(當月平日一段_tb.Text) < 彈性扣時 Then
                                當月平日一段1_tb.Text = "0.00"
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End Sub
    Private Sub Set_考勤流水()
        考勤流水_dgv.DataSource = Nothing : ds5.Clear()
        考勤流水_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        考勤流水_dgv.ColumnHeadersHeight = 25
        考勤流水_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_考勤流水()
        da.Fill(ds5) : 考勤流水_dgv.DataSource = ds5.Tables(0) : conn.Close()
        考勤流水_dgv.Columns(0).FillWeight = 30 : 考勤流水_dgv.Columns(1).FillWeight = 30 : 考勤流水_dgv.Columns(2).FillWeight = 100
        For i As Integer = 0 To 考勤流水_dgv.Rows.Count - 1
            考勤流水_dgv.Rows(i).Cells("No.").Value = i + 1
        Next
    End Sub
    Private Sub 讀取班別()
        班別_dgv.DataSource = Nothing : ds7.Clear()
        班別_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        班別_dgv.ColumnHeadersHeight = 25
        班別_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_班別()
        da.Fill(ds7) : 班別_dgv.DataSource = ds7.Tables(0) : conn.Close()
        班別_dgv.Columns(0).FillWeight = 80 : 班別_dgv.Columns(1).FillWeight = 130 : 班別_dgv.Columns(2).FillWeight = 130 : 班別_dgv.Columns(3).FillWeight = 100
        班別_dgv.Columns(4).FillWeight = 130 : 班別_dgv.Columns(5).FillWeight = 100 : 班別_dgv.Columns(6).FillWeight = 100 : 班別_dgv.Columns(7).FillWeight = 100
        班別_dgv.Columns(8).FillWeight = 100 : 班別_dgv.Columns(9).Visible = False : 班別_dgv.Columns(10).FillWeight = 100 : 班別_dgv.Columns(11).Visible = False
        班別_dgv.Columns(12).FillWeight = 100 : 班別_dgv.Columns(13).Visible = False : 班別_dgv.Columns(14).Visible = False
    End Sub
    Private Sub 讀取打卡時間1()
        時間讀取1_dgv.DataSource = Nothing : ds2.Clear()
        時間讀取1_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        時間讀取1_dgv.ColumnHeadersHeight = 25
        時間讀取1_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_打卡讀取_上午上班()
        da.Fill(ds2) : 時間讀取1_dgv.DataSource = ds2.Tables(0) : conn.Close()
    End Sub
    Private Sub 讀取打卡時間2()
        時間讀取2_dgv.DataSource = Nothing : ds3.Clear()
        時間讀取2_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        時間讀取2_dgv.ColumnHeadersHeight = 25
        時間讀取2_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_打卡讀取_中午下上班()
        da.Fill(ds3) : 時間讀取2_dgv.DataSource = ds3.Tables(0) : conn.Close()
    End Sub
    Private Sub 讀取打卡時間3()
        時間讀取3_dgv.DataSource = Nothing : ds4.Clear()
        時間讀取3_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        時間讀取3_dgv.ColumnHeadersHeight = 25
        時間讀取3_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_打卡讀取_晚上下上班()
        da.Fill(ds4) : 時間讀取3_dgv.DataSource = ds4.Tables(0) : conn.Close()
    End Sub
    Private Sub 讀取打卡時間4()
        時間讀取4_dgv.DataSource = Nothing : ds6.Clear()
        時間讀取4_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        時間讀取4_dgv.ColumnHeadersHeight = 25
        時間讀取4_dgv.AllowUserToAddRows = False
        SQL_考勤明細表_打卡讀取_加班下班()
        da.Fill(ds6) : 時間讀取4_dgv.DataSource = ds6.Tables(0) : conn.Close()
    End Sub
    Private Sub 讀取日期()
        日期_dgv.DataSource = Nothing : ds8.Clear()
        日期_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        日期_dgv.ColumnHeadersHeight = 25
        日期_dgv.AllowUserToAddRows = False
        SQL_讀取日期()
        da.Fill(ds8) : 日期_dgv.DataSource = ds8.Tables(0) : conn.Close()
    End Sub
    Private Sub 年份_下拉表單資料載入()
        SQL_考勤明細表_年份_下拉()
        年份_cb.Items.Clear()
        While (dr.Read()) : 年份_cb.Items.Add(dr("年份")) : End While
        conn.Close()
    End Sub
    Private Sub 考勤明細表_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Me.MdiParent = GRAMS_SYS : Me.WindowState = 2 : Me.AutoScroll = True
        年份_下拉表單資料載入() : 讀取班別()
        年份_cb.Text = Year(Today) : 月份_cb.SelectedIndex = Month(Today) - 1
        PA1 = 年份_cb.Text : PA2 = 月份_cb.Text
        讀取日期()
        If CC(16) = False Then
            考勤資料編輯_bt.Enabled = False : 時薪制_ch.Enabled = False : 彈性工時_ch.Enabled = False
            If gUserName = "測試號" Then : 人員_dgv.Enabled = True : Else : 人員_dgv.Enabled = False : End If
            Panel5.Visible = False
            姓名_tb.Text = gUserName : 資料數 = 31
            For i As Integer = 0 To 人員_dgv.Rows.Count - 1 : If 姓名_tb.Text = 人員_dgv("姓名", i).Value.ToString Then : 時薪制_ch.Checked = 人員_dgv("CC18", i).Value : End If : Next
            SQL_考勤明細表_讀取編號() : If dr.Read() Then : 編號 = dr("DIN") : End If
            Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
            月清單_dgv.Location = New System.Drawing.Point(91, 33) : 月清單_dgv.Size = New Size(1395, 790)
        Else
            月清單_dgv.Location = New System.Drawing.Point(420, 2) : 月清單_dgv.Size = New Size(1066, 821)
        End If
    End Sub
    Private Sub 人員_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 人員_dgv.CellClick
        If e.RowIndex = -1 Then : Else
            編號 = 人員_dgv("編號", e.RowIndex).Value.ToString : 姓名_tb.Text = 人員_dgv("姓名", e.RowIndex).Value.ToString
            時薪制_ch.Checked = 人員_dgv("CC18", e.RowIndex).Value : 到職日 = 人員_dgv("到職日", e.RowIndex).Value.ToString
            If IsDBNull(人員_dgv("月薪", e.RowIndex).Value) Then : 人員_dgv("月薪", e.RowIndex).Value = "0" : End If
            If IsDBNull(人員_dgv("時薪", e.RowIndex).Value) Then : 人員_dgv("時薪", e.RowIndex).Value = "0" : End If
            If IsDBNull(人員_dgv("主管加級", e.RowIndex).Value) Then : 人員_dgv("主管加級", e.RowIndex).Value = "0" : End If
            If IsDBNull(人員_dgv("職務加級", e.RowIndex).Value) Then : 人員_dgv("職務加級", e.RowIndex).Value = "0" : End If
            If IsDBNull(人員_dgv("健保費", e.RowIndex).Value) Then : 人員_dgv("健保費", e.RowIndex).Value = "0" : End If
            If IsDBNull(人員_dgv("勞保費", e.RowIndex).Value) Then : 人員_dgv("勞保費", e.RowIndex).Value = "0" : End If
            月薪_tb.Text = Strings.Format(CDbl(人員_dgv("月薪", e.RowIndex).Value.ToString), "#,##0.0")
            時薪_tb.Text = Strings.Format(CDbl(人員_dgv("時薪", e.RowIndex).Value.ToString), "#,##0.0")
            主管加級_tb.Text = Strings.Format(CDbl(人員_dgv("主管加級", e.RowIndex).Value.ToString), "#,##0.0")
            職務加級_tb.Text = Strings.Format(CDbl(人員_dgv("職務加級", e.RowIndex).Value.ToString), "#,##0.0")
            健保費_tb.Text = Strings.Format(CDbl(人員_dgv("健保費", e.RowIndex).Value.ToString), "#,##0.0")
            勞保費_tb.Text = Strings.Format(CDbl(人員_dgv("勞保費", e.RowIndex).Value.ToString), "#,##0.0")
            資料數 = 31 : Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
        End If
    End Sub
    Private Sub 考勤流水_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 考勤流水_dgv.CellClick
        If e.RowIndex = -1 Then : Else
            ID_tb.Text = 考勤流水_dgv("ID", e.RowIndex).Value.ToString : 時間_tb.Text = 考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString
            PA6 = 考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString : PA3 = 考勤流水_dgv("ID", e.RowIndex).Value.ToString
        End If
    End Sub
    Private Sub 月清單_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 月清單_dgv.CellClick
        If e.RowIndex = -1 Then : Else
            ID_tb.Text = "" : 時間_tb.Text = ""
            '-如果下班時間超過晚上12點時的分段計算------------------------------------------
            If Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "00" Or Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "01" Or
                Strings.Left(月清單_dgv(e.ColumnIndex, e.RowIndex).Value.ToString, 2) = "02" Then
                時間_tb.Text = Strings.Left(月清單_dgv("日期", e.RowIndex).Value, 4) & "-" & Strings.Mid(月清單_dgv("日期", e.RowIndex).Value, 6, 2) &
                           "-" & Strings.Right(月清單_dgv("日期", e.RowIndex).Value, 2)
                Dim dateString As String = 時間_tb.Text : Dim parsedDate As DateTime
                If DateTime.TryParseExact(dateString, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
                    parsedDate = parsedDate.AddDays(1) : 時間_tb.Text = parsedDate.ToString("yyyy-MM-dd")
                End If
            Else
                時間_tb.Text = Strings.Left(月清單_dgv("日期", e.RowIndex).Value, 4) & "-" & Strings.Mid(月清單_dgv("日期", e.RowIndex).Value, 6, 2) &
                           "-" & Strings.Right(月清單_dgv("日期", e.RowIndex).Value, 2)
            End If
            If e.ColumnIndex = 8 Or e.ColumnIndex = 9 Or e.ColumnIndex = 11 Or e.ColumnIndex = 12 Or e.ColumnIndex = 14 Or e.ColumnIndex = 15 Then
                If 月清單_dgv(e.ColumnIndex, e.RowIndex).Value <> "" Then
                    時間_tb.Text = 時間_tb.Text & " " & 月清單_dgv(e.ColumnIndex, e.RowIndex).Value
                    For i As Integer = 0 To 考勤流水_dgv.Rows.Count - 1
                        If Len(時間_tb.Text) > 11 Then
                            If 時間_tb.Text = 考勤流水_dgv("打卡時間", i).Value.ToString Then
                                ID_tb.Text = 考勤流水_dgv("ID", i).Value : PA3 = 考勤流水_dgv("ID", i).Value : PA6 = 考勤流水_dgv("打卡時間", i).Value.ToString : Exit For
                            End If
                        ElseIf Len(時間_tb.Text) = 11 Then
                            If Strings.Left(時間_tb.Text, 10) = Strings.Left(考勤流水_dgv("打卡時間", i).Value.ToString, 10) Then
                                ID_tb.Text = 考勤流水_dgv("ID", i).Value : PA3 = 考勤流水_dgv("ID", i).Value : PA6 = 考勤流水_dgv("打卡時間", i).Value.ToString : Exit For
                            End If
                        End If
                    Next
                    If ID_tb.Text = "" Then
                        Dim A As String = 時間_tb.Text : Dim format1 As String = "yyyy-MM-dd HH:mm:ss" : Dim format2 As String = "yyyy-MM-dd" : Dim parsedDate As DateTime
                        If DateTime.TryParseExact(A, format1, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
                        ElseIf DateTime.TryParseExact(A, format2, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
                            If e.ColumnIndex = 8 Then : PA6 = 時間_tb.Text & " 08:00:00"
                            ElseIf e.ColumnIndex = 9 Then : PA6 = 時間_tb.Text & " 12:00:00"
                            ElseIf e.ColumnIndex = 11 Then : PA6 = 時間_tb.Text & " 13:00:00"
                            ElseIf e.ColumnIndex = 12 Then : PA6 = 時間_tb.Text & " 17:00:00"
                            ElseIf e.ColumnIndex = 14 Then : PA6 = 時間_tb.Text & " 18:00:00"
                            ElseIf e.ColumnIndex = 15 Then : PA6 = 時間_tb.Text & " 20:00:00"
                            End If : PA3 = ""
                        Else : MsgBox("資料有問題，請通知系統管理員 [ " & 時間_tb.Text & " ]") : End If
                    End If
                Else
                    If ID_tb.Text = "" Then
                        Dim A As String = 時間_tb.Text : Dim format1 As String = "yyyy-MM-dd HH:mm:ss" : Dim format2 As String = "yyyy-MM-dd" : Dim parsedDate As DateTime
                        If DateTime.TryParseExact(A, format1, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
                        ElseIf DateTime.TryParseExact(A, format2, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, parsedDate) Then
                            If e.ColumnIndex = 8 Then : PA6 = 時間_tb.Text & " 08:00:00"
                            ElseIf e.ColumnIndex = 9 Then : PA6 = 時間_tb.Text & " 12:00:00"
                            ElseIf e.ColumnIndex = 11 Then : PA6 = 時間_tb.Text & " 13:00:00"
                            ElseIf e.ColumnIndex = 12 Then : PA6 = 時間_tb.Text & " 17:00:00"
                            ElseIf e.ColumnIndex = 14 Then : PA6 = 時間_tb.Text & " 18:00:00"
                            ElseIf e.ColumnIndex = 15 Then : PA6 = 時間_tb.Text & " 20:00:00"
                            End If : PA3 = ""
                        Else : MsgBox("資料有問題，請通知系統管理員 [ " & 時間_tb.Text & " ]") : End If
                    End If
                End If
                考勤明細表_編輯.時間_dtp.Value = PA6 : 考勤明細表_編輯.姓名_tb.Text = 姓名_tb.Text
                考勤明細表_編輯.ID_lb.Text = PA3 : 考勤明細表_編輯.ID1_lb.Text = 編號
            Else
                時間_tb.Text = "" : ID_tb.Text = ""
            End If
        End If : 考勤明細表_編輯.BringToFront()
    End Sub
    Private Sub 月清單_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 月清單_dgv.RowPostPaint
        For I As Integer = 0 To 月清單_dgv.Rows.Count - 1
            If 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期六" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("星期").Value.ToString = "星期日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString = "國定假日" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightPink
            ElseIf 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString <> "" Then : 月清單_dgv.Rows(I).DefaultCellStyle.BackColor = Color.LightYellow
            End If
        Next
    End Sub
    Private Sub 考勤流水_dgv_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles 考勤流水_dgv.RowPostPaint
        Dim linePen As New Pen(Color.Blue, 2)
        If e.RowIndex = 考勤流水_dgv.Rows.Count - 1 Then : Exit Sub : Else
            If Strings.Left(考勤流水_dgv("打卡時間", e.RowIndex).Value.ToString, 10) <> Strings.Left(考勤流水_dgv("打卡時間", e.RowIndex + 1).Value.ToString, 10) Then
                Dim startX As Integer = IIf(考勤流水_dgv.RowHeadersVisible, 考勤流水_dgv.RowHeadersWidth, 0) : Dim startY As Integer = e.RowBounds.Top + e.RowBounds.Height - 1
                Dim endX As Integer = startX + 考勤流水_dgv.Columns.GetColumnsWidth(DataGridViewElementStates.Visible) - 考勤流水_dgv.HorizontalScrollingOffset
                e.Graphics.DrawLine(linePen, startX, startY, endX, startY) : Exit Sub
            End If
        End If
    End Sub
    Private Sub 月份_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 月份_cb.SelectedIndexChanged
        PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : 讀取人員資料表()
        If 編號 <> "" Then
            資料數 = 31 : Set_考勤明細() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
            讀取日期()
        End If
    End Sub
    Private Sub 考勤資料編輯_bt_Click(sender As Object, e As EventArgs) Handles 考勤資料編輯_bt.Click
        If PA6 <> "" Then
            PA4 = PA6 : PA = 年份_cb.Text : PA1 = 月份_cb.Text : PA2 = Strings.Mid(PA6, 9, 2)
            PA5 = 姓名_tb.Text : PA7 = 編號 : 考勤明細表_編輯.Show() : 考勤重讀()
        Else
            MsgBox("請選擇右下方的時間，做為修改的依據!!")
        End If
    End Sub
    Private Sub 考勤重讀()
        PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text
        Set_考勤流水() : 資料數 = 31 : Set_考勤明細() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
    End Sub
    Private Sub R_bt_Click(sender As Object, e As EventArgs) Handles R_bt.Click
        考勤重讀()
    End Sub
    Private Sub 排班資料_bt_Click(sender As Object, e As EventArgs) Handles 排班資料_bt.Click
        PA = 年份_cb.Text : PA1 = 月份_cb.Text : PA2 = 姓名_tb.Text : 個人班表.Close() : 個人班表.Show()
    End Sub
    Private Sub 時薪制_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 時薪制_ch.Click
        Set_時薪制()
    End Sub
    Private Sub 彈性工時_ch_CheckedChanged(sender As Object, e As EventArgs) Handles 彈性工時_ch.Click
        Set_彈性工時()
    End Sub
    Private Sub 薪資表存檔_bt_Click(sender As Object, e As EventArgs) Handles 薪資表存檔_bt.Click
        If 姓名_tb.Text = "" Then : MsgBox("沒有資料無法存檔!!") : Else
            Dim 流水號 As String = "" : Dim 流水 As Integer : Dim 薪資 As Double
            PA1 = CInt(年份_cb.Text) - 1911 & "-" & 月份_cb.Text : PA2 = 到職日 : PA3 = 姓名_tb.Text : PA4 = 月清單_dgv.Rows.Count
            PA5 = 當月平日天_tb.Text : PA6 = 常日總_tb.Text : PA7 = 月薪_tb.Text : PA8 = 時薪_tb.Text : PA9 = "0" : PA10 = 主管加級_tb.Text : PA11 = 職務加級_tb.Text
            PA12 = 特休未休_nud.Value : PA13 = 特修補錢_tb.Text : PA14 = 特休_tb.Text : PA15 = 婚假_tb.Text : PA16 = 喪假_tb.Text
            PA17 = 其他加項_tb.Text : PA18 = 其他加_nud.Value : PA19 = 未到離職_tb.Text
            薪資 = CDbl(月薪_tb.Text) + CDbl(主管加級_tb.Text) + CDbl(職務加級_tb.Text) + CDbl(特修補錢_tb.Text) +
                   CDbl(特休_tb.Text) + CDbl(婚假_tb.Text) + CDbl(喪假_tb.Text) + 其他加_nud.Value - CDbl(未到離職_tb.Text)
            If 時薪制_ch.Checked = False Then : PA20 = 薪資 : PA21 = "0" : PA22 = 薪資 : Else : PA20 = "0" : PA21 = 薪資 : PA22 = 薪資 : End If
            PA23 = CDbl(當月平日一段_tb.Text) + CDbl(一段假日加_tb.Text) : PA24 = CDbl(當月平日二段_tb.Text) + CDbl(二段假日加_tb.Text) : PA25 = 國假日加_tb.Text
            PA26 = 加班費1_tb.Text : PA27 = 應付小計1_tb.Text
            PA28 = CDbl(當月平日一段1_tb.Text) + CDbl(一段假日加1_tb.Text) : PA29 = CDbl(當月平日二段1_tb.Text) + CDbl(二段假日加1_tb.Text) : PA30 = 國假日加1_tb.Text
            PA31 = 加班費2_tb.Text : PA32 = 應付小計2_tb.Text
            PA33 = 勞保費_tb.Text : PA34 = 健保費_tb.Text : PA35 = 事假_tb.Text : PA36 = 病假_tb.Text : PA37 = 其他減項_tb.Text : PA38 = 其他減_nud.Value
            PA39 = CDbl(勞保費_tb.Text) + CDbl(健保費_tb.Text) + CDbl(事假_tb.Text) + CDbl(病假_tb.Text) + 其他減_nud.Value : PA40 = "0"
            PA41 = 實付小計1_tb.Text : PA42 = 實付小計2_tb.Text

            SQL_薪資表確認()
            If dr.Read() Then
                PA = dr("流水號").ToString : SQL_薪資表修改() : MsgBox("薪資資料修改完成!!")
            Else
                SQL_薪資表流水號()
                If dr.Read() Then : 流水 = Double.Parse(Strings.Right(dr("流水號").ToString, 8)) : Else : 流水 = 0 : End If : conn.Close() : 流水 += 1
                If 流水 < 10 Then : 流水號 = "PO" & "0000000" & 流水
                ElseIf 流水 > 9 And 流水 < 100 Then : 流水號 = "PO" & "000000" & 流水
                ElseIf 流水 > 99 And 流水 < 1000 Then : 流水號 = "PO" & "00000" & 流水
                ElseIf 流水 > 999 And 流水 < 10000 Then : 流水號 = "PO" & "0000" & 流水
                ElseIf 流水 > 9999 And 流水 < 100000 Then : 流水號 = "PO" & "000" & 流水
                ElseIf 流水 > 99999 And 流水 < 1000000 Then : 流水號 = "PO" & "00" & 流水
                ElseIf 流水 > 999999 And 流水 < 10000000 Then : 流水號 = "PO" & "0" & 流水
                ElseIf 流水 > 9999999 Then : 流水號 = "PO" & 流水 : End If : PA = 流水號
                SQL_薪資表新增() : MsgBox("薪資資料新增完成!!")
            End If
        End If
    End Sub
    Private Sub 列印工時表_bt_Click(sender As Object, e As EventArgs) Handles 列印工時表_bt.Click
        If 姓名_tb.Text = "" Then : PA = "全體" : Else : 考勤表列印選擇.ShowDialog() : End If
        xlApp = CType(CreateObject("Excel.Application"), Application)
        xlBook = xlApp.Workbooks.Add
        xlApp.DisplayAlerts = True
        xlApp.Visible = True
        xlApp.Application.WindowState = xlMaximized
        If PA = "單人" Then
            列印工時表() : xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("考勤表列印完成!!")
        Else
            For I As Integer = 0 To 人員_dgv.Rows.Count - 1
                編號 = 人員_dgv("編號", I).Value.ToString : 姓名_tb.Text = 人員_dgv("姓名", I).Value.ToString : 時薪制_ch.Checked = 人員_dgv("CC18", I).Value
                到職日 = 人員_dgv("到職日", I).Value.ToString
                If 人員_dgv("月薪", I).Value.ToString = "" Then : 月薪_tb.Text = "0" : Else : 月薪_tb.Text = Strings.Format(CDbl(人員_dgv("月薪", I).Value.ToString), "#,##0.0") : End If
                If 人員_dgv("時薪", I).Value.ToString = "" Then : 時薪_tb.Text = "0" : Else : 時薪_tb.Text = Strings.Format(CDbl(人員_dgv("時薪", I).Value.ToString), "#,##0.0") : End If
                If 人員_dgv("時薪", I).Value.ToString = "" Then : 主管加級_tb.Text = "0" : Else : 主管加級_tb.Text = Strings.Format(CDbl(人員_dgv("主管加級", I).Value.ToString), "#,##0.0") : End If
                If 人員_dgv("時薪", I).Value.ToString = "" Then : 職務加級_tb.Text = "0" : Else : 職務加級_tb.Text = Strings.Format(CDbl(人員_dgv("職務加級", I).Value.ToString), "#,##0.0") : End If
                If 人員_dgv("時薪", I).Value.ToString = "" Then : 健保費_tb.Text = "0" : Else : 健保費_tb.Text = Strings.Format(CDbl(人員_dgv("健保費", I).Value.ToString), "#,##0.0") : End If
                If 人員_dgv("時薪", I).Value.ToString = "" Then : 勞保費_tb.Text = "0" : Else : 勞保費_tb.Text = Strings.Format(CDbl(人員_dgv("勞保費", I).Value.ToString), "#,##0.0") : End If
                資料數 = 31 : Set_考勤明細() : PA = 編號 : PA1 = 年份_cb.Text : PA2 = 月份_cb.Text : Set_考勤流水() : GRAMS_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0
                列印工時表()
            Next
            xlSheet.PageSetup.PrintArea = "" : xlApp.Cells.Select() : xlSheet.Range("B1").Select() : xlApp.Application.WindowState = xlMinimized : MsgBox("全體考勤表列印完成!!")
        End If
    End Sub
    Private Sub 列印工時表()
        xlSheet = NewMethod(xlBook) : xlApp.Sheets(1).Select
        xlApp.Sheets(1).Name = 姓名_tb.Text : xlBook.Activate() : xlSheet.Activate()

        xlSheet.Cells(1, 1) = "黃柏翰皮膚科診所考勤明細表" : xlSheet.Cells(2, 1) = CInt(年份_cb.Text) - 1911 & "年" & 月份_cb.Text & "月 - " & 姓名_tb.Text & " 全月考勤"
        xlSheet.Cells(3, 1) = "日期" : xlSheet.Cells(3, 2) = "星期" : xlSheet.Cells(3, 3) = "假別" : xlSheet.Cells(3, 4) = "內容" : xlSheet.Cells(3, 5) = "假時數"
        xlSheet.Cells(3, 6) = "一段上班" : xlSheet.Cells(3, 7) = "一段下班" : xlSheet.Cells(3, 8) = "二段上班" : xlSheet.Cells(3, 9) = "二段下班"
        xlSheet.Cells(3, 10) = "加班上班" : xlSheet.Cells(3, 11) = "加班下班" : xlSheet.Cells(3, 12) = "一段工時" : xlSheet.Cells(3, 13) = "二段工時"
        xlSheet.Cells(3, 14) = "一段加班" : xlSheet.Cells(3, 15) = "二段加班" : xlSheet.Cells(3, 16) = "加班申請" : xlSheet.Cells(3, 17) = "加班內容"
        For I As Integer = 0 To 月清單_dgv.Rows.Count - 1
            xlSheet.Cells(4 + I, 1) = 月清單_dgv.Rows(I).Cells("日期").Value.ToString : xlSheet.Cells(4 + I, 2) = 月清單_dgv.Rows(I).Cells("星期").Value.ToString
            xlSheet.Cells(4 + I, 3) = 月清單_dgv.Rows(I).Cells("休假類別").Value.ToString : xlSheet.Cells(4 + I, 4) = 月清單_dgv.Rows(I).Cells("假期內容").Value.ToString
            xlSheet.Cells(4 + I, 5) = 月清單_dgv.Rows(I).Cells("申請時數").Value.ToString : xlSheet.Cells(4 + I, 6) = 月清單_dgv.Rows(I).Cells("一段上班").Value.ToString
            xlSheet.Cells(4 + I, 7) = 月清單_dgv.Rows(I).Cells("一段下班").Value.ToString : xlSheet.Cells(4 + I, 8) = 月清單_dgv.Rows(I).Cells("二段上班").Value.ToString
            xlSheet.Cells(4 + I, 9) = 月清單_dgv.Rows(I).Cells("二段下班").Value.ToString : xlSheet.Cells(4 + I, 10) = 月清單_dgv.Rows(I).Cells("加班上班").Value.ToString
            xlSheet.Cells(4 + I, 11) = 月清單_dgv.Rows(I).Cells("加班下班").Value.ToString : xlSheet.Cells(4 + I, 12) = 月清單_dgv.Rows(I).Cells("一段工時").Value.ToString
            xlSheet.Cells(4 + I, 13) = 月清單_dgv.Rows(I).Cells("二段工時").Value.ToString : xlSheet.Cells(4 + I, 14) = 月清單_dgv.Rows(I).Cells("一段加班").Value.ToString
            xlSheet.Cells(4 + I, 15) = 月清單_dgv.Rows(I).Cells("二段加班").Value.ToString : xlSheet.Cells(4 + I, 16) = 月清單_dgv.Rows(I).Cells("申請狀態").Value.ToString
            xlSheet.Cells(4 + I, 17) = 月清單_dgv.Rows(I).Cells("加班狀態").Value.ToString
        Next : AA(xlApp, xlSheet)
    End Sub
    Private Shared Function NewMethod(xlBook As Workbook) As Worksheet
        Return CType(xlBook.Worksheets.Add, Worksheet)
    End Function
    Private Sub AA(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Microsoft.Office.Interop.Excel.Worksheet)
        xlSheet.Cells.Select()
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 10 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
        xlSheet.Range("A3:Q36").Select()
        With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
            .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With
        xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 10
        xlSheet.Columns("B:Q").Select : myExcel.Columns("B:Q").EntireColumn.AutoFit
        xlSheet.Rows("3:34").Select : myExcel.Selection.RowHeight = 20
        xlSheet.Rows("4:4").Select : myExcel.ActiveWindow.FreezePanes = True
        xlSheet.Range("A3:Q34").Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        xlSheet.Range("A3:Q3").Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent1 : .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0 : End With
        For I As Integer = 4 To 36
            If Strings.Left(xlSheet.Cells(I, 2).value, 3) = "星期六" Or Strings.Left(xlSheet.Cells(I, 2).value, 3) = "星期日" Then
                xlSheet.Range("A" & I & ":Q" & I).Select()
                With myExcel.Selection.Interior : .Pattern = xlSolid : .PatternColorIndex = xlAutomatic : .ThemeColor = xlThemeColorAccent2 : .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0 : End With
            End If
        Next
        xlSheet.Range("A2:Q2").Select()
        With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
            .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 12 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
        myExcel.Selection.Font.Bold = True
        xlSheet.Range("A1:Q1").Select()
        With myExcel.Selection : .HorizontalAlignment = xlCenter : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
            .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False : End With : myExcel.Selection.Merge
        With myExcel.Selection.Font : .Name = "微軟正黑體" : .Size = 16 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone : End With
        myExcel.Selection.Font.Bold = True
        xlSheet.Range("Q4").Select() : myExcel.ActiveWindow.DisplayGridlines = False
    End Sub
End Class