NANJIN-AAS-SYS-LEEHSIEHJUI/楠晉鞋業AAS/考勤管理-依人員.vb

Option Strict Off
Imports System.Data.SqlClient
Public Class 考勤管理_依人員
    Dim conn As New SqlConnection
    Dim da As New SqlDataAdapter
    Dim cmd As New SqlCommand
    Dim ds As New DataSet : Dim ds1 As New DataSet
    Dim dr As SqlDataReader
    Dim STR1 As String
    Dim dt As DateTime
    Dim DTP As String
    Dim aa As MsgBoxResult
    Private Sub Set_人員清單()
        DataGridView2.DataSource = Nothing : ds1.Clear()
        DataGridView2.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        DataGridView2.ColumnHeadersHeight = 25
        DataGridView2.AllowUserToAddRows = False
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        If PA = "" Then
            SQL1 = "SELECT 資料流水號 AS DataID, 序號, 姓名 FROM 人員名單 ORDER BY DataID"
        Else
            SQL1 = "SELECT 資料流水號 AS DataID, 序號, 姓名 FROM 人員名單 WHERE 姓名 LIKE '%" & PA & "%' ORDER BY DataID"
        End If
        cmd.Connection = conn : cmd.CommandText = SQL1 : da.SelectCommand = cmd : da.Fill(ds1) : DataGridView2.DataSource = ds1.Tables(0) : conn.Close()
        Set_grid1()
    End Sub
    Private Sub Set_grid1()
        DataGridView2.Columns(0).Visible = False : DataGridView2.Columns(1).Width = 70 : DataGridView2.Columns(2).Width = 80
    End Sub
    Private Sub Set_清單()
        DataGridView1.DataSource = Nothing : ds.Clear()
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        DataGridView1.ColumnHeadersHeight = 25
        DataGridView1.AllowUserToAddRows = False
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        SQL1 = "SELECT 資料流水號, DataID, 員工名稱, 出勤日期, 星期, 第1班上班, 第1班下班, 第1班合計, 第2班上班, 第2班下班, 第2班合計, 第3班上班, 第3班下班, 第3班合計, " &
               "第1班合計 + 第2班合計 + 第3班合計 AS 上班時長, 第1班合計 + 第2班合計 AS 應上班 FROM 考勤資料庫 " &
               "WHERE DataID LIKE '" & TextBox1.Text & "' AND  (出勤日期 LIKE '" & ComboBox3.Text & "-" & ComboBox1.Text & "%') ORDER BY   出勤日期"
        列印用SQL = SQL1
        cmd.Connection = conn : cmd.CommandText = SQL1 : da.SelectCommand = cmd : da.Fill(ds) : DataGridView1.DataSource = ds.Tables(0) : conn.Close()
        Set_grid()
    End Sub
    Private Sub Set_grid()
        DataGridView1.Columns(0).Visible = False : DataGridView1.Columns(1).Visible = False : DataGridView1.Columns(2).Visible = False
        DataGridView1.Columns(3).Width = 80 : DataGridView1.Columns(4).Width = 60 : DataGridView1.Columns(5).Width = 70 : DataGridView1.Columns(6).Width = 70
        DataGridView1.Columns(7).Width = 70 : DataGridView1.Columns(8).Width = 70 : DataGridView1.Columns(9).Width = 80 : DataGridView1.Columns(10).Width = 80
        DataGridView1.Columns(11).Width = 80 : DataGridView1.Columns(12).Width = 80 : DataGridView1.Columns(13).Width = 80 : DataGridView1.Columns(14).Width = 80
        DataGridView1.Columns(15).Width = 80 : Set_自動彙總1()
    End Sub
    Private Sub Set_自動彙總1()
        Dim 加總(4) As Double : Dim EU(11) As Double
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            加總(0) += DataGridView1.Rows(i).Cells("第1班合計").Value : 加總(1) += DataGridView1.Rows(i).Cells("第2班合計").Value : 加總(2) += DataGridView1.Rows(i).Cells("第3班合計").Value
            加總(3) += DataGridView1.Rows(i).Cells("上班時長").Value : 加總(4) += DataGridView1.Rows(i).Cells("應上班").Value
        Next i
        TextBox3.Text = Format(Val(加總(0)), "#,##0.0") : TextBox4.Text = Format(Val(加總(1)), "#,##0.0") : TextBox5.Text = Format(Val(加總(2)), "#,##0.0")
        TextBox6.Text = Format(Val(加總(3)), "#,##0.0") : TextBox7.Text = Format(Val(加總(4)), "#,##0.0")
    End Sub
    Private Sub 考勤管理_依人員_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.MdiParent = 楠晉鞋業AAS : Me.AutoScroll = True
        Set_人員清單()
        TextBox1.Visible = False
        ComboBox3.Text = Year(Today) : ComboBox1.SelectedIndex = Month(Today) - 1
    End Sub
    Private Sub DataGridView2_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        TextBox1.Text = DataGridView2.Rows(e.RowIndex).Cells("DataID").Value.ToString
        TextBox2.Text = DataGridView2.Rows(e.RowIndex).Cells("姓名").Value.ToString
        Set_清單()
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        PA = InputBox("請輸入要查詢的人員姓名") : Set_人員清單() : PA = ""
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        DataGridView1.DataSource = Nothing : ds.Clear()
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
        DataGridView1.ColumnHeadersHeight = 25
        DataGridView1.AllowUserToAddRows = False
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        SQL1 = "SELECT 資料流水號, DataID, 員工名稱, 出勤日期, 星期, 第1班上班, 第1班下班, 第1班合計, 第2班上班, 第2班下班, 第2班合計, 第3班上班, 第3班下班, 第3班合計, " &
               "第1班合計 + 第2班合計 + 第3班合計 AS 上班時長, 第1班合計 + 第2班合計 AS 應上班 FROM 考勤資料庫 " &
               "WHERE DataID LIKE '" & TextBox1.Text & "' ORDER BY   出勤日期"
        列印用SQL = SQL1
        cmd.Connection = conn : cmd.CommandText = SQL1 : da.SelectCommand = cmd : da.Fill(ds) : DataGridView1.DataSource = ds.Tables(0) : conn.Close()
        Set_grid()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        導出成EXCEL.Show()
    End Sub
End Class