HSKIN-MIS-SYS-SIU/GRAMS-SYS/05、業務管理/業績獎金計算表.vb

Option Strict Off
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
Public Class 業績獎金計算表
    ReadOnly ds, ds1 As New DataSet
    Private Sub SET_客戶交易明細表()
        交易明細_dgv.DataSource = Nothing : ds.Clear()
        交易明細_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        交易明細_dgv.ColumnHeadersHeight = 40 : 交易明細_dgv.AllowUserToAddRows = False
        SQL_客戶交易明細表_清單()
        da.Fill(ds) : 交易明細_dgv.DataSource = ds.Tables(0) : conn.Close()

        交易明細_dgv.Columns(0).Visible = False : 交易明細_dgv.Columns(2).Visible = False : 交易明細_dgv.Columns(4).Visible = False : 交易明細_dgv.Columns(5).Visible = False
        交易明細_dgv.Columns(13).Visible = False : 交易明細_dgv.Columns(18).Visible = False : 交易明細_dgv.Columns(19).Visible = False
        交易明細_dgv.Columns(6).FillWeight = 300 : 交易明細_dgv.Columns(12).FillWeight = 150

        For I As Integer = 0 To 20 : 交易明細_dgv.Columns(I).ReadOnly = True : Next

        交易明細_dgv.Columns(7).DefaultCellStyle.Format = "#,##0" : 交易明細_dgv.Columns(8).DefaultCellStyle.Format = "#,##0"
        交易明細_dgv.Columns(9).DefaultCellStyle.Format = "#,##0.00" : 交易明細_dgv.Columns(10).DefaultCellStyle.Format = "#,##0.0"
        交易明細_dgv.Columns(11).DefaultCellStyle.Format = "#,##0"
        交易明細_dgv.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(9).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(10).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        交易明細_dgv.Columns(11).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        SET_獎金計算()
    End Sub
    Private Sub SET_獎金計算()
        獎金計算_dgv.DataSource = Nothing : ds1.Clear()
        獎金計算_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        獎金計算_dgv.ColumnHeadersHeight = 40 : 獎金計算_dgv.AllowUserToAddRows = False
        SQL_獎金計算_清單()
        da.Fill(ds1) : 獎金計算_dgv.DataSource = ds1.Tables(0) : conn.Close()

        For I As Integer = 2 To 27
            獎金計算_dgv.Columns(I).DefaultCellStyle.Format = "#,##0"
            獎金計算_dgv.Columns(I).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        Next
        For I As Integer = 0 To 28 : 獎金計算_dgv.Columns(I).ReadOnly = True : Next
        獎金計算_dgv.Columns(0).FillWeight = 75 : 獎金計算_dgv.Columns(1).FillWeight = 60
        獎金計算_dgv.Columns(28).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        For no As Integer = 1 To 26
            Dim TextBoxS As TextBox = CType(Me.Controls.Find("TextBox" + no.ToString(), True)(0), TextBox) : TextBoxS.Text = "0"
        Next

        For I As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            獎金計算_dgv("一般產品業績", I).Value = 0 : 獎金計算_dgv("美療排程", I).Value = 0 : 獎金計算_dgv("美療操作", I).Value = 0
            獎金計算_dgv("排程業績", I).Value = 0 : 獎金計算_dgv("操作業績", I).Value = 0 : 獎金計算_dgv("手術排程", I).Value = 0
            獎金計算_dgv("手術操作", I).Value = 0 : 獎金計算_dgv("植髮/電波業績", I).Value = 0 : 獎金計算_dgv("預購療程", I).Value = 0
            獎金計算_dgv("償還業績", I).Value = 0 : 獎金計算_dgv("一般產品", I).Value = 0 : 獎金計算_dgv("美療", I).Value = 0
            獎金計算_dgv("雷射/治療", I).Value = 0 : 獎金計算_dgv("手術", I).Value = 0 : 獎金計算_dgv("植髮/電波", I).Value = 0
            For ii As Integer = 0 To 交易明細_dgv.Rows.Count - 1
                '------一般產品，人工皮-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("產品銷售", ii).Value.ToString, 4, 5) And
                  (交易明細_dgv("類別", ii).Value.ToString = "一般產品" Or 交易明細_dgv("類別", ii).Value.ToString = "人工皮") Then
                    獎金計算_dgv("一般產品業績", I).Value = 獎金計算_dgv("一般產品業績", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("產品銷售", ii).Value.ToString, 4, 5) And
                  (交易明細_dgv("類別", ii).Value.ToString = "一般產品" Or 交易明細_dgv("類別", ii).Value.ToString = "人工皮") Then
                    獎金計算_dgv("一般產品", I).Value = 獎金計算_dgv("一般產品", I).Value + 交易明細_dgv("業績", ii).Value
                End If

                '------美療排程-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                    交易明細_dgv("類別", ii).Value.ToString = "美療" And 交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("美療排程", I).Value = 獎金計算_dgv("美療排程", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------美療操作-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("操作人員", ii).Value.ToString, 4, 5) And
                    交易明細_dgv("類別", ii).Value.ToString = "美療" And 交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("美療操作", I).Value = 獎金計算_dgv("美療操作", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------美療-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                    交易明細_dgv("類別", ii).Value.ToString = "美療" And (交易明細_dgv("備註", ii).Value.ToString = "預購療程" Or
                    交易明細_dgv("備註", ii).Value.ToString = ".") Then
                    獎金計算_dgv("美療", I).Value = 獎金計算_dgv("美療", I).Value + 交易明細_dgv("業績", ii).Value
                End If

                '------雷射、治療排程-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                  (交易明細_dgv("類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("類別", ii).Value.ToString = "治療") And
                   交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("排程業績", I).Value = 獎金計算_dgv("排程業績", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------雷射、治療操作-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("操作人員", ii).Value.ToString, 4, 5) And
                  (交易明細_dgv("類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("類別", ii).Value.ToString = "治療") And
                   交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("操作業績", I).Value = 獎金計算_dgv("操作業績", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------雷射、治療-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                    (交易明細_dgv("類別", ii).Value.ToString = "雷射" Or 交易明細_dgv("類別", ii).Value.ToString = "治療") And
                    (交易明細_dgv("備註", ii).Value.ToString = "預購療程" Or 交易明細_dgv("備註", ii).Value.ToString = ".") Then
                    獎金計算_dgv("雷射/治療", I).Value = 獎金計算_dgv("雷射/治療", I).Value + 交易明細_dgv("業績", ii).Value
                End If

                '------手術排程-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                  交易明細_dgv("類別", ii).Value.ToString = "手術" And 交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("手術排程", I).Value = 獎金計算_dgv("手術排程", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------手術操作-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("操作人員", ii).Value.ToString, 4, 5) And
                   交易明細_dgv("類別", ii).Value.ToString = "手術" And 交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("手術操作", I).Value = 獎金計算_dgv("手術操作", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                '------手術-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                   交易明細_dgv("類別", ii).Value.ToString = "手術" And
                    (交易明細_dgv("備註", ii).Value.ToString = "預購療程" Or 交易明細_dgv("備註", ii).Value.ToString = ".") Then
                    獎金計算_dgv("手術", I).Value = 獎金計算_dgv("手術", I).Value + 交易明細_dgv("業績", ii).Value
                End If

                '------植髮/電波-------------------------------------------------------------------------------------------
                If (獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) Or
                    獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("操作人員", ii).Value.ToString, 4, 5)) And
                    (交易明細_dgv("類別", ii).Value.ToString = "植髮" Or 交易明細_dgv("類別", ii).Value.ToString = "電波") And
                    交易明細_dgv("備註", ii).Value.ToString <> "預購療程" Then
                    獎金計算_dgv("植髮/電波業績", I).Value = 獎金計算_dgv("植髮/電波業績", I).Value + 交易明細_dgv("業績", ii).Value
                End If
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                    (交易明細_dgv("類別", ii).Value.ToString = "植髮" Or 交易明細_dgv("類別", ii).Value.ToString = "電波") And
                    (交易明細_dgv("備註", ii).Value.ToString = "預購療程" Or 交易明細_dgv("備註", ii).Value.ToString = ".") Then
                    獎金計算_dgv("植髮/電波", I).Value = 獎金計算_dgv("植髮/電波", I).Value + 交易明細_dgv("業績", ii).Value
                End If

                '------預購療程-------------------------------------------------------------------------------------------
                If 獎金計算_dgv("人員", I).Value.ToString = Strings.Mid(交易明細_dgv("排程人員", ii).Value.ToString, 4, 5) And
                   交易明細_dgv("備註", ii).Value.ToString = "預購療程" Then
                    獎金計算_dgv("預購療程", I).Value = 獎金計算_dgv("預購療程", I).Value + 交易明細_dgv("業績", ii).Value
                End If
            Next
            '------一般產品，人工皮-------------------------------------------------------------------------------------------
            If 獎金計算_dgv("時薪", I).Value.ToString = "時薪" Then
                If 獎金計算_dgv("一般產品業績", I).Value < 產品_時薪_nud.Value Then
                    獎金計算_dgv("一般產品獎金", I).Value = 獎金計算_dgv("一般產品業績", I).Value / 100 * 業績_未達標1_nud.Value
                Else
                    獎金計算_dgv("一般產品獎金", I).Value = 獎金計算_dgv("一般產品業績", I).Value / 100 * 業績_達標1_nud.Value
                End If
            Else
                If 獎金計算_dgv("一般產品業績", I).Value < 產品_月薪_nud.Value Then
                    獎金計算_dgv("一般產品獎金", I).Value = 獎金計算_dgv("一般產品業績", I).Value / 100 * 業績_未達標1_nud.Value
                Else
                    獎金計算_dgv("一般產品獎金", I).Value = 獎金計算_dgv("一般產品業績", I).Value / 100 * 業績_達標1_nud.Value
                End If
            End If
            '------美療操作-------------------------------------------------------------------------------------------
            獎金計算_dgv("美療獎金", I).Value = (獎金計算_dgv("美療排程", I).Value / 100 * 美療獎金_nud.Value) + (獎金計算_dgv("美療操作", I).Value / 100 * 美療獎金_nud.Value)
            '------雷射、治療排程-------------------------------------------------------------------------------------------
            If 獎金計算_dgv("排程業績", I).Value < 排程業績_nud.Value Then
                獎金計算_dgv("排程獎金", I).Value = 獎金計算_dgv("排程業績", I).Value / 100 * 業績_未達標2_nud.Value
            Else
                獎金計算_dgv("排程獎金", I).Value = 獎金計算_dgv("排程業績", I).Value / 100 * 業績_達標2_nud.Value
            End If
            '------雷射、治療操作-------------------------------------------------------------------------------------------
            獎金計算_dgv("操作獎金", I).Value = 獎金計算_dgv("操作業績", I).Value / 100 * 操作獎金_nud.Value
            '------手術排程-------------------------------------------------------------------------------------------
            獎金計算_dgv("手術排程獎金", I).Value = 獎金計算_dgv("手術排程", I).Value / 100 * 手術排程_nud.Value
            '------手術操作-------------------------------------------------------------------------------------------
            獎金計算_dgv("手術操作獎金", I).Value = 獎金計算_dgv("手術操作", I).Value / 100 * 手術操作_nud.Value
            '------植髮-------------------------------------------------------------------------------------------
            獎金計算_dgv("植髮/電波獎金", I).Value = 獎金計算_dgv("植髮/電波業績", I).Value / 100 * 植髮獎金_nud.Value
            '------預購療程-------------------------------------------------------------------------------------------
            If 獎金計算_dgv("預購療程", I).Value < 預售_nud.Value Then
                獎金計算_dgv("預購獎金", I).Value = 0
            Else
                獎金計算_dgv("預購獎金", I).Value = 獎金計算_dgv("預購療程", I).Value / 100 * 預售獎金_nud.Value
            End If
            '------彙總-------------------------------------------------------------------------------------------
            獎金計算_dgv("總獎金", I).Value = 獎金計算_dgv("預購獎金", I).Value + 獎金計算_dgv("植髮/電波獎金", I).Value + 獎金計算_dgv("手術操作獎金", I).Value +
                                              獎金計算_dgv("手術排程獎金", I).Value + 獎金計算_dgv("操作獎金", I).Value + 獎金計算_dgv("排程獎金", I).Value +
                                              獎金計算_dgv("美療獎金", I).Value + 獎金計算_dgv("一般產品獎金", I).Value
            獎金計算_dgv("實收業績", I).Value = 獎金計算_dgv("植髮/電波", I).Value + 獎金計算_dgv("手術", I).Value + 獎金計算_dgv("雷射/治療", I).Value +
                                                 獎金計算_dgv("美療", I).Value + 獎金計算_dgv("一般產品", I).Value
            獎金計算_dgv("償還業績", I).Value = (獎金計算_dgv("一般產品業績", I).Value + 獎金計算_dgv("美療排程", I).Value + 獎金計算_dgv("排程業績", I).Value +
                                                 獎金計算_dgv("手術排程", I).Value + 獎金計算_dgv("植髮/電波業績", I).Value + 獎金計算_dgv("預購療程", I).Value -
                                                 獎金計算_dgv("實收業績", I).Value)
            獎金計算_dgv("總業績", I).Value = 獎金計算_dgv("償還業績", I).Value + 獎金計算_dgv("實收業績", I).Value
            If 獎金計算_dgv("總業績", I).Value = 0 Then
                獎金計算_dgv("獎金比", I).Value = "0 %"
            Else
                獎金計算_dgv("獎金比", I).Value = Math.Round(獎金計算_dgv("總獎金", I).Value / 獎金計算_dgv("總業績", I).Value * 100, 2) & " %"
            End If

            TextBox1.Text = Val(TextBox1.Text) + 獎金計算_dgv("一般產品業績", I).Value : TextBox2.Text = Val(TextBox2.Text) + 獎金計算_dgv("美療排程", I).Value
            TextBox3.Text = Val(TextBox3.Text) + 獎金計算_dgv("美療操作", I).Value : TextBox4.Text = Val(TextBox4.Text) + 獎金計算_dgv("排程業績", I).Value
            TextBox5.Text = Val(TextBox5.Text) + 獎金計算_dgv("操作業績", I).Value : TextBox6.Text = Val(TextBox6.Text) + 獎金計算_dgv("手術排程", I).Value
            TextBox7.Text = Val(TextBox7.Text) + 獎金計算_dgv("手術操作", I).Value : TextBox8.Text = Val(TextBox8.Text) + 獎金計算_dgv("植髮/電波業績", I).Value
            TextBox9.Text = Val(TextBox9.Text) + 獎金計算_dgv("預購療程", I).Value : TextBox10.Text = Val(TextBox10.Text) + 獎金計算_dgv("一般產品獎金", I).Value
            TextBox11.Text = Val(TextBox11.Text) + 獎金計算_dgv("美療獎金", I).Value : TextBox12.Text = Val(TextBox12.Text) + 獎金計算_dgv("排程獎金", I).Value
            TextBox13.Text = Val(TextBox13.Text) + 獎金計算_dgv("操作獎金", I).Value : TextBox14.Text = Val(TextBox14.Text) + 獎金計算_dgv("手術排程獎金", I).Value
            TextBox15.Text = Val(TextBox15.Text) + 獎金計算_dgv("手術操作獎金", I).Value : TextBox16.Text = Val(TextBox16.Text) + 獎金計算_dgv("植髮/電波獎金", I).Value
            TextBox17.Text = Val(TextBox17.Text) + 獎金計算_dgv("預購獎金", I).Value : TextBox18.Text = Val(TextBox18.Text) + 獎金計算_dgv("總獎金", I).Value
            TextBox19.Text = Val(TextBox19.Text) + 獎金計算_dgv("一般產品", I).Value : TextBox20.Text = Val(TextBox20.Text) + 獎金計算_dgv("美療", I).Value
            TextBox21.Text = Val(TextBox21.Text) + 獎金計算_dgv("雷射/治療", I).Value : TextBox22.Text = Val(TextBox22.Text) + 獎金計算_dgv("手術", I).Value
            TextBox23.Text = Val(TextBox23.Text) + 獎金計算_dgv("植髮/電波", I).Value : TextBox24.Text = Val(TextBox24.Text) + 獎金計算_dgv("實收業績", I).Value
            TextBox25.Text = Val(TextBox25.Text) + 獎金計算_dgv("償還業績", I).Value : TextBox26.Text = Val(TextBox26.Text) + 獎金計算_dgv("總業績", I).Value
        Next
        For no As Integer = 1 To 26
            Dim TextBoxS As TextBox = CType(Me.Controls.Find("TextBox" + no.ToString(), True)(0), TextBox) : TextBoxS.Text = Strings.Format(Val(TextBoxS.Text), "#,##0")
        Next

        獎金計算_dgv.Columns(2).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(3).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(4).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(5).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(6).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(7).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(8).DefaultCellStyle.BackColor = Color.LightGreen : 獎金計算_dgv.Columns(9).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(10).DefaultCellStyle.BackColor = Color.LightGreen
        獎金計算_dgv.Columns(11).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(12).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(13).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(14).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(15).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(16).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(17).DefaultCellStyle.BackColor = Color.LightBlue : 獎金計算_dgv.Columns(18).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(19).DefaultCellStyle.BackColor = Color.LightBlue
        獎金計算_dgv.Columns(20).DefaultCellStyle.BackColor = Color.LightPink : 獎金計算_dgv.Columns(21).DefaultCellStyle.BackColor = Color.LightPink
        獎金計算_dgv.Columns(22).DefaultCellStyle.BackColor = Color.LightPink : 獎金計算_dgv.Columns(23).DefaultCellStyle.BackColor = Color.LightPink
        獎金計算_dgv.Columns(24).DefaultCellStyle.BackColor = Color.LightPink : 獎金計算_dgv.Columns(25).DefaultCellStyle.BackColor = Color.LightPink
        獎金計算_dgv.Columns(26).DefaultCellStyle.BackColor = Color.OrangeRed
    End Sub
    Private Sub SET_使用者下拉清單載入()
        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
        SET_使用者下拉清單載入() : 月份_cb.SelectedIndex = 月份_cb.Items.Count - 1
        SET_客戶交易明細表()
    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 交易明細_dgv("銷售日期", e.RowIndex).Value.ToString <> 交易明細_dgv("銷售日期", e.RowIndex + 1).Value.ToString 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
        SET_客戶交易明細表()
    End Sub
    Private Sub 存檔_bt_Click(sender As Object, e As EventArgs) Handles 存檔_bt.Click
        PA13 = 月份_cb.Text : PA12 = "0" : PA11 = "0" : PA10 = "0"
        For I As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            PA12 = Strings.Format(Val(PA12) + 獎金計算_dgv("實收業績", I).Value, "###0")
            PA11 = Strings.Format(Val(PA11) + 獎金計算_dgv("償還業績", I).Value, "###0")
            PA10 = Strings.Format(Val(PA10) + 獎金計算_dgv("總業績", I).Value, "###0")
        Next
        SQL_業機彙總判斷()
        If dr.Read() Then : SQL_業機彙總_修改() : Else : SQL_業機彙總_新增() : End If : conn.Close() : MsgBox("存檔完成")
    End Sub

    Dim RT As Integer = 0
    Private Sub 列印_bt_Click(sender As Object, e As EventArgs) Handles 列印_bt.Click
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

        xlApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
        xlBook = xlApp.Workbooks.Add
        xlApp.DisplayAlerts = True
        xlBook.Activate()
        xlSheet = NewMethod(xlBook)
        xlSheet.Activate()
        xlApp.Visible = True
        xlApp.Application.WindowState = xlMaximized

        AA(xlApp, xlSheet) : RT = 0
        For I As Integer = 0 To 獎金計算_dgv.Rows.Count - 1
            If 獎金計算_dgv("人員", I).Value.ToString = "" Or 獎金計算_dgv("總獎金", I).Value = 0 Then : Else
                xlSheet.Cells(1 + RT, 2) = "獎金月份" : xlSheet.Cells(2 + RT, 2) = "姓名" : xlSheet.Cells(3 + RT, 2) = "個人應發獎金小計(不含預購獎金)"
                xlSheet.Cells(4 + RT, 2) = "預購獎金" : xlSheet.Cells(5 + RT, 2) = "其他" : xlSheet.Cells(6 + RT, 2) = "備註" : xlSheet.Cells(7 + RT, 2) = "總獎金"
                xlSheet.Cells(8 + RT, 2) = "公司留存"
                xlSheet.Cells(1 + RT, 3) = Strings.Left(月份_cb.Text, 3) & " 年 " & Strings.Right(月份_cb.Text, 2) & " 月"
                xlSheet.Cells(2 + RT, 3) = 獎金計算_dgv("人員", I).Value.ToString : xlSheet.Cells(3 + RT, 3) = 獎金計算_dgv("總獎金", I).Value - 獎金計算_dgv("預購獎金", I).Value
                xlSheet.Cells(4 + RT, 3) = 獎金計算_dgv("預購獎金", I).Value : xlSheet.Cells(5 + RT, 3) = "0" : xlSheet.Cells(6 + RT, 3) = ""
                xlSheet.Cells(7 + RT, 3) = 獎金計算_dgv("總獎金", I).Value
                xlSheet.Cells(1 + RT, 5) = "獎金月份" : xlSheet.Cells(2 + RT, 5) = "姓名" : xlSheet.Cells(3 + RT, 5) = "個人應發獎金小計(不含預購獎金)"
                xlSheet.Cells(4 + RT, 5) = "預購獎金" : xlSheet.Cells(5 + RT, 5) = "其他" : xlSheet.Cells(6 + RT, 5) = "備註" : xlSheet.Cells(7 + RT, 5) = "總獎金"
                xlSheet.Cells(8 + RT, 5) = "同仁留存"
                xlSheet.Cells(1 + RT, 6) = Strings.Left(月份_cb.Text, 3) & " 年 " & Strings.Right(月份_cb.Text, 2) & " 月"
                xlSheet.Cells(2 + RT, 6) = 獎金計算_dgv("人員", I).Value.ToString : xlSheet.Cells(3 + RT, 6) = 獎金計算_dgv("總獎金", I).Value - 獎金計算_dgv("預購獎金", I).Value
                xlSheet.Cells(4 + RT, 6) = 獎金計算_dgv("預購獎金", I).Value : xlSheet.Cells(5 + RT, 6) = "0" : xlSheet.Cells(6 + RT, 6) = ""
                xlSheet.Cells(7 + RT, 6) = 獎金計算_dgv("總獎金", I).Value
                BB(xlApp, xlSheet) : RT += 9
            End If
        Next

        xlSheet.PageSetup.PrintArea = ""
        xlApp.Cells.Select()
        xlSheet.Range("B1").Select()
        xlApp.Application.WindowState = xlMinimized
        MsgBox("列印完成")
    End Sub
    Private Shared Function NewMethod(xlBook As Microsoft.Office.Interop.Excel.Workbook) As Microsoft.Office.Interop.Excel.Worksheet
        Return CType(xlBook.Worksheets.Add, Microsoft.Office.Interop.Excel.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 = 14 : .Strikethrough = False : .Superscript = False : .Subscript = False : .OutlineFont = False
            .Shadow = False : .Underline = xlUnderlineStyleNone : .ThemeColor = xlThemeColorLight1 : .TintAndShade = 0 : .ThemeFont = xlThemeFontNone
        End With

        xlSheet.Columns("A:A").Select : myExcel.Selection.ColumnWidth = 1 : xlSheet.Columns("D:D").Select : myExcel.Selection.ColumnWidth = 2
        xlSheet.Columns("G:G").Select : myExcel.Selection.ColumnWidth = 1 : xlSheet.Columns("B:C").Select : myExcel.Selection.ColumnWidth = 40
        xlSheet.Columns("E:F").Select : myExcel.Selection.ColumnWidth = 40
        xlSheet.Columns("C:C").Select : myExcel.Selection.Style = "Comma" : myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
        xlSheet.Columns("F:F").Select : myExcel.Selection.Style = "Comma" : myExcel.Selection.NumberFormatLocal = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
        xlSheet.Columns("C:C").Select
        With myExcel.Selection
            .HorizontalAlignment = xlRight : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
            .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False
        End With
        xlSheet.Columns("F:F").Select
        With myExcel.Selection
            .HorizontalAlignment = xlRight : .VerticalAlignment = xlCenter : .WrapText = False : .Orientation = 0 : .AddIndent = False : .IndentLevel = 0
            .ShrinkToFit = False : .ReadingOrder = xlContext : .MergeCells = False
        End With
    End Sub
    Private Sub BB(ByVal myExcel As Microsoft.Office.Interop.Excel.Application, ByVal xlSheet As Microsoft.Office.Interop.Excel.Worksheet)
        xlSheet.Rows(1 + RT & ":" & 7 + RT).Select : myExcel.Selection.RowHeight = 28
        xlSheet.Rows(8 + RT & ":" & 8 + RT).Select : myExcel.Selection.RowHeight = 60
        xlSheet.Rows(9 + RT & ":" & 9 + RT).Select : myExcel.Selection.RowHeight = 20

        xlSheet.Range("B" & 1 + RT & ":C" & 7 + RT).Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlThin : End With

        xlSheet.Range("E" & 1 + RT & ":F" & 7 + RT).Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeLeft) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeRight) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlInsideVertical) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlThin : End With
        With myExcel.Selection.Borders(xlInsideHorizontal) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlThin : End With

        xlSheet.Range("B" & 8 + RT & ":C" & 8 + RT).Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        myExcel.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        myExcel.Selection.Borders(xlEdgeRight).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
        myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

        xlSheet.Range("E" & 8 + RT & ":F" & 8 + RT).Select()
        myExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone : myExcel.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        myExcel.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With myExcel.Selection.Borders(xlEdgeTop) : .LineStyle = xlContinuous : .ColorIndex = xlAutomatic : .TintAndShade = 0 : .Weight = xlMedium : End With
        With myExcel.Selection.Borders(xlEdgeBottom) : .LineStyle = xlContinuous : .ColorIndex = 0 : .TintAndShade = 0 : .Weight = xlThin : End With
        myExcel.Selection.Borders(xlEdgeRight).LineStyle = xlNone : myExcel.Selection.Borders(xlInsideVertical).LineStyle = xlNone
        myExcel.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    End Sub
End Class