HX-PGS-ERP-SYS-SIU/05、財務管理系統/薪資轉帳表.vb

Option Strict Off
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
    Dim 資料新增判斷 As Boolean : Dim 第一次開啟判斷 As Boolean = False : Dim EDR As Integer = 0 : Dim ESTR As String : Dim EDR1 As Integer = 0
    Dim ESTR1 As String : Dim 首次開啟 As Boolean = False
    Private Sub Set_清單()
        薪轉_dgv.DataSource = Nothing : ds1.Clear()
        薪轉_dgv.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        薪轉_dgv.ColumnHeadersHeight = 40
        薪轉_dgv.AllowUserToAddRows = False

        薪資轉帳表_月份清單()
        da.Fill(ds1)
        薪轉_dgv.DataSource = ds1.Tables(0)
        conn.Close()

        薪轉_dgv.Columns(10).Visible = False
        薪轉_dgv.Columns(0).ReadOnly = True : 薪轉_dgv.Columns(1).ReadOnly = True : 薪轉_dgv.Columns(2).ReadOnly = True : 薪轉_dgv.Columns(3).ReadOnly = True
        薪轉_dgv.Columns(4).ReadOnly = False : 薪轉_dgv.Columns(5).ReadOnly = False : 薪轉_dgv.Columns(6).ReadOnly = False : 薪轉_dgv.Columns(7).ReadOnly = False
        薪轉_dgv.Columns(8).ReadOnly = False : 薪轉_dgv.Columns(9).ReadOnly = True
        薪轉_dgv.Columns("獎金").DefaultCellStyle.Format = "#,##0.0"
        薪轉_dgv.Columns("薪資").DefaultCellStyle.Format = "#,##0.0"
        薪轉_dgv.Columns("領現合計").DefaultCellStyle.Format = "#,##0.0"
        薪轉_dgv.Columns("薪資合計").DefaultCellStyle.Format = "#,##0.0"
        薪轉_dgv.Columns("領現").DefaultCellStyle.Format = "#,##0.0"
        '---------------------------------------欄位置中-----------------------------------------------------------'
        薪轉_dgv.Columns("流水號").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("流水號").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("姓名").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("姓名").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("身分證").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("身分證").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("結算日期").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("結算日期").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("給付方式").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("給付方式").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("薪資帳號").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("薪資帳號").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("職務").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("職務").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("電話").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("電話").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("手機").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("手機").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        '---------------------------------------欄位靠右-----------------------------------------------------------'
        薪轉_dgv.Columns("獎金").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        薪轉_dgv.Columns("獎金").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("薪資").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        薪轉_dgv.Columns("薪資").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("領現").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        薪轉_dgv.Columns("領現").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("薪資合計").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        薪轉_dgv.Columns("薪資合計").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.Columns("領現合計").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        薪轉_dgv.Columns("領現合計").HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        薪轉_dgv.EditMode = DataGridViewEditMode.EditOnEnter
        Set_合記計算()
    End Sub
    Private Sub Set_合記計算()
        薪資總金額_tb.Text = "0" : 領現總金額_tb.Text = "0" : 合計總金額_tb.Text = "0"
        For i As Integer = 0 To 薪轉_dgv.Rows.Count - 1
            薪轉_dgv("薪資合計", i).Value = 薪轉_dgv("獎金", i).Value + 薪轉_dgv("薪資", i).Value
            薪資總金額_tb.Text = Val(薪資總金額_tb.Text) + 薪轉_dgv("薪資合計", i).Value
            薪轉_dgv("領現合計", i).Value = 薪轉_dgv("領現", i).Value
            領現總金額_tb.Text = Val(領現總金額_tb.Text) + 薪轉_dgv("領現合計", i).Value
            合計總金額_tb.Text = Val(領現總金額_tb.Text) + Val(薪資總金額_tb.Text)
        Next
        薪資總金額_tb.Text = Format(Val(薪資總金額_tb.Text), "#,##0")
        領現總金額_tb.Text = Format(Val(領現總金額_tb.Text), "#,##0")
        合計總金額_tb.Text = Format(Val(合計總金額_tb.Text), "#,##0")
    End Sub
    Private Sub 日期格式()
        If 會計月_ch.Checked = True Then
            '-------------日期一----------------------------------------------------------------------------------------
            DTP = Strings.Left(Format(開始日期_dtp.Value, "yyyy/MM/dd"), 8) & "01" : 開始日期_dtp.Value = DTP
            開始日期_dtp.Value = 開始日期_dtp.Value.AddMonths(-1)
            DTP = Strings.Left(Format(開始日期_dtp.Value, "yyyy/MM/dd"), 8) & "26" : 開始日期_dtp.Value = DTP
            '-------------日期二----------------------------------------------------------------------------------------
            DTP = Strings.Left(Format(結束日期_dtp.Value, "yyyy/MM/dd"), 8) & "25" : 結束日期_dtp.Value = DTP
        Else
            '-------------日期一----------------------------------------------------------------------------------------
            開始日期_dtp.Value = 開始日期_dtp.Value.AddMonths(1)
            DTP = Strings.Left(Format(開始日期_dtp.Value, "yyyy/MM/dd"), 8) & "01" : 開始日期_dtp.Value = DTP
            '-------------日期二----------------------------------------------------------------------------------------
            DTP = Strings.Left(Strings.Format(結束日期_dtp.Value, "yyyy/MM/dd"), 8) & "01" : 結束日期_dtp.Value = DTP
            結束日期_dtp.Value = 結束日期_dtp.Value.AddMonths(1) : 結束日期_dtp.Value = 結束日期_dtp.Value.AddDays(-1)
        End If
    End Sub
    Private Sub 薪資轉帳表_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = HX_PGS_ERP_SYS : Me.WindowState = 2 : Me.AutoScroll = True
        狀態_lb.Visible = False
        流水號_tb.Visible = False : 編號_tb.Enabled = False : 第一次開啟判斷 = True
        If 第一次開啟判斷 = True Then
            薪資轉帳表_姓名選擇()
            While dr.Read()
                姓名_cb.Items.Add(dr("姓名").ToString())
            End While
        End If
        conn.Close()
        首次開啟 = False : 日期格式() : Set_清單() : 首次開啟 = True '--------------------資料讀取
    End Sub
    Private Sub 薪資轉帳表_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
        第一次開啟判斷 = False
    End Sub
    Private Sub 薪轉_dgv_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles 薪轉_dgv.CellClick
        If 首次開啟 = True Then
            If e.RowIndex = -1 Then : Else
                流水號_tb.Text = 薪轉_dgv(0, e.RowIndex).Value : 姓名_cb.Text = 薪轉_dgv(1, e.RowIndex).Value.ToString
                身分證_tb.Text = 薪轉_dgv(2, e.RowIndex).Value.ToString : 結束日期_dtp.Value = 薪轉_dgv(3, e.RowIndex).Value.ToString
                獎金_tb.Text = 薪轉_dgv(4, e.RowIndex).Value.ToString : 薪資_tb.Text = 薪轉_dgv(5, e.RowIndex).Value.ToString
                領現_tb.Text = 薪轉_dgv(6, e.RowIndex).Value.ToString : 給付方式_cb.Text = 薪轉_dgv(9, e.RowIndex).Value.ToString
                人事流水號_tb.Text = 薪轉_dgv(10, e.RowIndex).Value.ToString
                獎金_tb.Text = Format(Val(獎金_tb.Text), "#,##0.0")
                薪資_tb.Text = Format(Val(薪資_tb.Text), "#,##0.0")
                領現_tb.Text = Format(Val(領現_tb.Text), "#,##0.0")

            End If
        End If
    End Sub
    Private Sub 薪轉_dgv_CellEndEdit(ByVal sender As System.Object, ByVal e As DataGridViewCellEventArgs) Handles 薪轉_dgv.CellEndEdit
        薪資總金額_tb.Text = "0" : 領現總金額_tb.Text = "0" : 合計總金額_tb.Text = "0"
        For i As Integer = 0 To 薪轉_dgv.Rows.Count - 1
            薪轉_dgv("薪資合計", i).Value = 薪轉_dgv("獎金", i).Value + 薪轉_dgv("薪資", i).Value
            薪資總金額_tb.Text = Val(薪資總金額_tb.Text) + 薪轉_dgv("薪資合計", i).Value
            薪轉_dgv("領現合計", i).Value = 薪轉_dgv("領現", i).Value
            領現總金額_tb.Text = Val(領現總金額_tb.Text) + 薪轉_dgv("領現合計", i).Value
            合計總金額_tb.Text = Val(領現總金額_tb.Text) + Val(薪資總金額_tb.Text)
        Next
        薪資總金額_tb.Text = Format(Val(薪資總金額_tb.Text), "#,##0")
        領現總金額_tb.Text = Format(Val(領現總金額_tb.Text), "#,##0")
        合計總金額_tb.Text = Format(Val(合計總金額_tb.Text), "#,##0")
    End Sub
    Private Sub 新增準備_bt_Click(sender As Object, e As EventArgs) Handles 新增準備_bt.Click
        Timer1.Enabled = True
        流水號_tb.Text = "" : 獎金_tb.Text = "" : 薪資_tb.Text = "" : 領現_tb.Text = ""     '--------------------控件清空
        資料新增判斷 = True '--------------------目前的編輯狀態
        新增準備_bt.Enabled = False  '--------------------關閉點選控制功能
        ': 刪除_bt.Enabled = False : 批量存檔_bt.Enabled = False
        薪轉_dgv.Enabled = False '--------------------關閉DGV控件的點選控制功能

        SQL_薪資轉帳表_流水號_最後一筆資料() '--------------------自動取得新流水號
        If dr.Read() Then : EDR = Double.Parse(Strings.Right(dr("流水號").ToString, 9)) : Else : EDR = 0 : End If
        conn.Close() : EDR += 1
        If EDR < 10 Then : ESTR = "M" & "00000000" & EDR
        ElseIf EDR > 9 And EDR < 100 Then : ESTR = "M" & "0000000" & EDR
        ElseIf EDR > 99 And EDR < 1000 Then : ESTR = "M" & "000000" & EDR
        ElseIf EDR > 999 And EDR < 10000 Then : ESTR = "M" & "00000" & EDR
        ElseIf EDR > 9999 And EDR < 100000 Then : ESTR = "M" & "0000" & EDR
        ElseIf EDR > 99999 And EDR < 100000 Then : ESTR = "M" & "000" & EDR
        ElseIf EDR > 999999 And EDR < 1000000 Then : ESTR = "M" & "00" & EDR
        ElseIf EDR > 9999999 And EDR < 10000000 Then : ESTR = "M" & "0" & EDR
        ElseIf EDR > 99999999 Then : ESTR = "M" & EDR
        End If : 流水號_tb.Text = ESTR

        'Set_日期格式轉換() '--------------------到職日期格式傳換
        SQL_薪資轉帳表_新增() '--------------------薪資轉帳表中寫入一筆資料
        Set_清單() '--------------------資料修改後重新讀取清單
    End Sub
    Private Sub 存檔_bt_Click(sender As Object, e As EventArgs) Handles 存檔_bt.Click
        If 流水號_tb.Text = "" Then '--------------------設定條件讓流水號控件不為空
            MsgBox("沒有選擇要存檔的資料")
        Else
            If 姓名_cb.Text = "" Then
                MsgBox("沒有名字難不成要撒錢，請填上他的名字")
            Else
                If 給付方式_cb.Text = "" Then
                    MsgBox("還是選個給薪的方式巴")
                Else
                    資料新增判斷 = False '--------------------目前的編輯狀態
                    狀態_lb.Visible = False
                    薪轉_dgv.Enabled = True '--------------------開啟DGV控件的點選控制功能
                    'Set_日期格式轉換() '--------------------到職日期格式傳換
                    SQL_薪資轉帳表_修改() '--------------------資料修改
                    Set_清單() '--------------------資料修改後重新讀取清單
                    新增準備_bt.Enabled = True
                End If
            End If
            Timer1.Enabled = False
        End If
    End Sub
    Private Sub 刪除_bt_Click(sender As Object, e As EventArgs) Handles 刪除_bt.Click
        If 流水號_tb.Text = "" Then '--------------------設定條件讓流水號控件不為空
            MsgBox("沒有選擇要刪除的資料")
        Else
            Dim aa1 As MsgBoxResult = MsgBox("確定要執行刪除指令嗎?", MsgBoxStyle.OkCancel)
            If aa1 = MsgBoxResult.Ok Then
                SQL_薪資轉帳表_刪除() '--------------------資料刪除
                Set_清單() '--------------------資料修改後重新讀取清單
            End If
        End If
    End Sub
    Private Sub 姓名_cb_SelectedIndexChanged(sender As Object, e As EventArgs) Handles 姓名_cb.SelectedIndexChanged
        ConnOpen() : SQL1 = "SELECT 流水號 FROM 人事資料表 WHERE (姓名 = N'" & Me.姓名_cb.Text & "')"
        CmdSet_For_dr()
        dr.Read()
        人事流水號_tb.Text = dr("流水號").ToString()
        conn.Close()
        ConnOpen() : SQL1 = "SELECT 身分證, 舊編, 薪資帳號 FROM 人事資料表 WHERE (姓名 = N'" & Me.姓名_cb.Text & "')"
        CmdSet_For_dr()
        dr.Read()
        身分證_tb.Text = dr("身分證").ToString()
        編號_tb.Text = dr("舊編").ToString()
        薪資帳號_tb.Text = dr("薪資帳號").ToString()
        conn.Close()
    End Sub
    Private Sub 開始日期_dtp_ValueChanged(sender As Object, e As EventArgs) Handles 開始日期_dtp.ValueChanged
        Set_清單()
    End Sub
    Private Sub 結束日期_dtp_ValueChanged(sender As Object, e As EventArgs) Handles 結束日期_dtp.ValueChanged
        Set_清單()
    End Sub
    Private Sub 會計月_CheckedChanged(sender As Object, e As EventArgs) Handles 會計月_ch.Click
        會計月_ch.Checked = True : 一般月_ch.Checked = False : 日期格式()
    End Sub
    Private Sub 一般月_CheckedChanged(sender As Object, e As EventArgs) Handles 一般月_ch.Click
        會計月_ch.Checked = False : 一般月_ch.Checked = True : 日期格式()
    End Sub
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        If 狀態_lb.Visible = True Then : 狀態_lb.Visible = False : Else : 狀態_lb.Visible = True : End If
    End Sub
    Private Sub 上個月_bt_Click(sender As Object, e As EventArgs) Handles 上個月_bt.Click
        If 會計月_ch.Checked = True Then
            中間日期_dtp.Value = 中間日期_dtp.Value.AddMonths(-1)
            開始日期_dtp.Value = 中間日期_dtp.Value
            結束日期_dtp.Value = 中間日期_dtp.Value
        Else
            中間日期_dtp.Value = 中間日期_dtp.Value.AddMonths(-1)
            開始日期_dtp.Value = 中間日期_dtp.Value.AddMonths(-1)
            結束日期_dtp.Value = 中間日期_dtp.Value
        End If
        日期格式()
    End Sub
    Private Sub 下個月_bt_Click(sender As Object, e As EventArgs) Handles 下個月_bt.Click
        If 會計月_ch.Checked = True Then
            中間日期_dtp.Value = 中間日期_dtp.Value.AddMonths(1)
            開始日期_dtp.Value = 中間日期_dtp.Value
            結束日期_dtp.Value = 中間日期_dtp.Value
        Else
            中間日期_dtp.Value = 中間日期_dtp.Value.AddMonths(1)
            開始日期_dtp.Value = 中間日期_dtp.Value.AddMonths(-1)
            結束日期_dtp.Value = 中間日期_dtp.Value
        End If
        日期格式()
    End Sub
    Private Sub 重新整理ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 重新整理ToolStripMenuItem.Click
        Set_清單()
    End Sub
    Private Sub 新增準備ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 新增準備ToolStripMenuItem.Click
        新增準備_bt.PerformClick()
    End Sub
    Private Sub 存檔ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 存檔ToolStripMenuItem.Click
        存檔_bt.PerformClick()
    End Sub
    Private Sub 刪除ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 刪除ToolStripMenuItem.Click
        刪除_bt.PerformClick()
    End Sub
    Private Sub 匯入EXCEL_bt_Click(sender As Object, e As EventArgs) Handles 匯入EXCEL_bt.Click
        PA = "" : 匯入EXCEL.ShowDialog() : 匯入EXCEL.BringToFront()
    End Sub
    Private Sub 匯出EXCEL_bt_Click(sender As Object, e As EventArgs) Handles 匯出EXCEL_bt.Click
        PA = "" : 匯出EXCEL.ShowDialog() : 匯出EXCEL.BringToFront()
    End Sub
    Private Sub 批量存檔_Click(sender As Object, e As EventArgs) Handles 批量存檔_bt.Click
        For i As Integer = 0 To 薪轉_dgv.Rows.Count - 1
            資料數 = 薪轉_dgv.Rows.Count : MyModule1.進度條() : HX_PGS_ERP_SYS.ToolStripProgressBar1.Value = 最終數
            PA1 = 薪轉_dgv.Rows(i).Cells("流水號").Value : PA2 = 薪轉_dgv.Rows(i).Cells("姓名").Value.ToString : PA3 = 薪轉_dgv.Rows(i).Cells("身分證").Value.ToString
            PA4 = 薪轉_dgv.Rows(i).Cells("結算日期").Value : PA5 = 薪轉_dgv.Rows(i).Cells("獎金").Value : PA6 = 薪轉_dgv.Rows(i).Cells("薪資").Value
            PA7 = 薪轉_dgv.Rows(i).Cells("領現").Value.ToString : PA8 = 薪轉_dgv.Rows(i).Cells("薪資合計").Value : PA9 = 薪轉_dgv.Rows(i).Cells("領現合計").Value
            PA10 = 薪轉_dgv.Rows(i).Cells("給付方式").Value : PA11 = 薪轉_dgv.Rows(i).Cells("薪資帳號").Value.ToString : PA12 = 薪轉_dgv.Rows(i).Cells("職務").Value
            PA13 = 薪轉_dgv.Rows(i).Cells("電話").Value : PA14 = 薪轉_dgv.Rows(i).Cells("手機").Value : PA15 = 薪轉_dgv.Rows(i).Cells("流水號").Value
            SQL_薪資轉帳表_批量存檔薪轉資料() : conn.Close()
        Next : HX_PGS_ERP_SYS.ToolStripProgressBar1.Value = 0 : 最終數 = 0 : MsgBox("批量存檔完成")
    End Sub
End Class