SUREYES-AM-SYS-LEEHSIEHJUI/SUREYES-AM01-SYS/薪資確認系統.vb

Option Strict Off
Imports System.Data.SqlClient
Imports System.IO
Imports System.Net
Public Class 薪資確認系統
    Dim conn As New SqlConnection
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Dim 月 As String
    Private Sub ComboBox1下拉表單資料載入()
        conn.Close()
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        SQL1 = "SELECT  年份 FROM 年份清單 ORDER BY 年份 DESC"
        cmd.CommandText = SQL1 : cmd.Connection = conn : dr = cmd.ExecuteReader
        ComboBox1.Items.Clear()
        While (dr.Read()) : ComboBox1.Items.Add(dr("年份")) : End While
        conn.Close()
    End Sub
    Private Sub 薪資確認系統_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        FormBorderStyle = FormBorderStyle.SizableToolWindow : ControlBox = False
        ComboBox1下拉表單資料載入() : ComboBox1.Text = Year(Today)
        TextBox2.Enabled = False : TextBox3.Enabled = False : TextBox4.Enabled = False : TextBox5.Enabled = False : TextBox6.Enabled = False : TextBox7.Enabled = False
    End Sub
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        PictureBox1.Image = Nothing
        TextBox2.Text = "" : TextBox3.Text = "" : TextBox4.Text = "" : TextBox5.Text = "" : TextBox6.Text = "" : TextBox7.Text = ""
        Label36.Text = "0.00" : Label37.Text = "0.00" : Label38.Text = "0.00" : Label39.Text = "0.00" : Label40.Text = "0.00" : Label41.Text = "0.00" : Label42.Text = "0.00"
        Label43.Text = "0.00" : Label44.Text = "0.00" : Label45.Text = "0.00" : Label46.Text = "0.00" : Label47.Text = "0.00" : Label48.Text = "0.00" : Label49.Text = "0.00"
        Label50.Text = "0.00" : Label51.Text = "0.00" : Label52.Text = "0.00" : Label53.Text = "0.00" : Label54.Text = "0.00" : Label55.Text = "0.00" : Label56.Text = "0.00"
        Label57.Text = "0.00" : Label58.Text = "0.00" : Label59.Text = "0.00" : Label60.Text = "0.00" : Label61.Text = "0.00"
        Label62.Text = "未簽"
        Timer1.Enabled = False
    End Sub
    Private Sub UsernameTextBox_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        If IsNumeric(TextBox1.Text) = True Then
            If TextBox1.Text.Length = 10 Then
                If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
                SQL1 = "SELECT 姓名, 部門, 課別, 組別, 職稱, 員工編號 FROM 使用者權限管理表 WHERE (ID卡號 = '" & TextBox1.Text & "')"
                cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
                If dr.Read() Then
                    TextBox2.Text = dr("姓名").ToString : TextBox3.Text = dr("部門").ToString : TextBox4.Text = dr("課別").ToString : TextBox5.Text = dr("組別").ToString : TextBox6.Text = dr("職稱").ToString
                    TextBox7.Text = dr("員工編號").ToString
                    conn.Close() : TextBox1.Text = ""
                End If

                If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
                SQL1 = "SELECT 簽名檔 FROM 人事職員簽名檔 WHERE 員工編號 LIKE '" & TextBox7.Text & "'"
                cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
                While dr.Read() = True
                    Dim bytes As Byte() = New Byte(-1) {}
                    bytes = DirectCast(dr.Item("簽名檔"), Byte())
                    Dim oStream As New MemoryStream(bytes)
                    PictureBox1.Image = Bitmap.FromStream(oStream)
                End While
                conn.Close()
                PictureBox1.SizeMode = 4
                TextBox1.Focus()
                Timer1.Enabled = True
            End If
        End If
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Me.Close()
    End Sub
    Private Sub 讀取薪資條()
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        SQL1 = "SELECT 底薪, 職務等類, 職務等類工資, 上班天數, 正班工資, 全勤獎, 平時加班工時, 平時加班工資, 週末加班工時, 
                       週末加班工資, 假日加班工時, 假日加班工資, 職務級數, 單位級數工資, 職務工資, 往年年資, 去年年資, 年資工資, 
                       [夜班/其他], 應發工資, 社保費, 代扣稅, 借款, 其他, 扣款合計, 實發合計
                FROM 人員薪資表
                WHERE (姓名 LIKE N'" & TextBox2.Text & "') AND (年份 LIKE '" & ComboBox1.Text & "') AND (月份 LIKE '" & 月 & "')"
        cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
        If dr.Read() Then
            Label36.Text = Format(Val(dr("底薪").ToString), "#,##0.00") : Label37.Text = Format(Val(dr("職務等類").ToString), "#,##0.00")
            Label38.Text = Format(Val(dr("職務等類工資").ToString), "#,##0.00") : Label39.Text = Format(Val(dr("上班天數").ToString), "#,##0.00")
            Label40.Text = Format(Val(dr("正班工資").ToString), "#,##0.00") : Label41.Text = Format(Val(dr("全勤獎").ToString), "#,##0.00")
            Label42.Text = Format(Val(dr("平時加班工時").ToString), "#,##0.00") : Label43.Text = Format(Val(dr("平時加班工資").ToString), "#,##0.00")
            Label44.Text = Format(Val(dr("週末加班工時").ToString), "#,##0.00") : Label45.Text = Format(Val(dr("週末加班工資").ToString), "#,##0.00")
            Label46.Text = Format(Val(dr("假日加班工時").ToString), "#,##0.00") : Label47.Text = Format(Val(dr("假日加班工資").ToString), "#,##0.00")
            Label48.Text = Format(Val(dr("職務級數").ToString), "#,##0.00") : Label49.Text = Format(Val(dr("單位級數工資").ToString), "#,##0.00")
            Label50.Text = Format(Val(dr("職務工資").ToString), "#,##0.00") : Label51.Text = Format(Val(dr("往年年資").ToString), "#,##0.00")
            Label52.Text = Format(Val(dr("去年年資").ToString), "#,##0.00") : Label53.Text = Format(Val(dr("年資工資").ToString), "#,##0.00")
            Label54.Text = Format(Val(dr("夜班/其他").ToString), "#,##0.00") : Label55.Text = Format(Val(dr("應發工資").ToString), "#,##0.00")
            Label56.Text = Format(Val(dr("社保費").ToString), "#,##0.00") : Label57.Text = Format(Val(dr("代扣稅").ToString), "#,##0.00")
            Label58.Text = Format(Val(dr("借款").ToString), "#,##0.00") : Label59.Text = Format(Val(dr("其他").ToString), "#,##0.00")
            Label60.Text = Format(Val(dr("扣款合計").ToString), "#,##0.00") : Label61.Text = Format(Val(dr("實發合計").ToString), "#,##0.00")
        Else
            Label36.Text = "0.00" : Label37.Text = "0.00" : Label38.Text = "0.00" : Label39.Text = "0.00" : Label40.Text = "0.00" : Label41.Text = "0.00" : Label42.Text = "0.00"
            Label43.Text = "0.00" : Label44.Text = "0.00" : Label45.Text = "0.00" : Label46.Text = "0.00" : Label47.Text = "0.00" : Label48.Text = "0.00" : Label49.Text = "0.00"
            Label50.Text = "0.00" : Label51.Text = "0.00" : Label52.Text = "0.00" : Label53.Text = "0.00" : Label54.Text = "0.00" : Label55.Text = "0.00" : Label56.Text = "0.00"
            Label57.Text = "0.00" : Label58.Text = "0.00" : Label59.Text = "0.00" : Label60.Text = "0.00" : Label61.Text = "0.00"
        End If
        conn.Close()
        If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
        SQL1 = "SELECT 核實 FROM 人員薪資表簽名核實 WHERE (姓名 LIKE N'" & TextBox2.Text & "') AND (工號 LIKE N'" & TextBox7.Text & "') AND (年份 LIKE '" & ComboBox1.Text & "') AND (月份 LIKE '" & 月 & "')"
        cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
        If dr.Read() Then
            Label62.Text = "已簽"
        Else
            Label62.Text = "未簽"
        End If
        conn.Close()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        月 = "01" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        月 = "02" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        月 = "03" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        月 = "04" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        月 = "05" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        月 = "06" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        月 = "07" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
        月 = "08" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
        月 = "09" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
        月 = "10" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
        月 = "11" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
        月 = "12" : 讀取薪資條() : TextBox1.Focus() : Timer1.Interval = 60000
    End Sub
    Private Sub Button14_Click(sender As Object, e As EventArgs) Handles Button14.Click
        If 月 = "" Then
            MsgBox("簽核前請先確定資料正確")
        Else
            If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
            SQL1 = "SELECT 實發合計   FROM 人員薪資表  WHERE (姓名 LIKE N'" & TextBox2.Text & "') AND (年份 LIKE '" & ComboBox1.Text & "') AND (月份 LIKE '" & 月 & "')"
            cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
            If dr.Read() Then
                conn.Close()
                If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
                SQL1 = "SELECT 核實 FROM 人員薪資表簽名核實  
                        WHERE (姓名 LIKE N'" & TextBox2.Text & "') AND (工號 LIKE N'" & TextBox7.Text & "') AND (年份 LIKE '" & ComboBox1.Text & "') AND (月份 LIKE '" & 月 & "')"
                cmd.Connection = conn : cmd.CommandText = SQL1 : dr = cmd.ExecuteReader
                If dr.Read() Then
                    MsgBox("已簽核過") : conn.Close()
                Else
                    conn.Close()
                    If conn.State = ConnectionState.Closed Then : conn.ConnectionString = ConString : conn.Open() : End If
                    SQL1 = "INSERT INTO 人員薪資表簽名核實 ( 姓名, 工號, 年份, 月份, 核實) 
                    VALUES (N'" & TextBox2.Text & "',N'" & TextBox7.Text & "',N'" & ComboBox1.Text & "',N'" & 月 & "',N'1')"
                    cmd.Connection = conn : cmd.CommandText = SQL1 : cmd.ExecuteNonQuery() : conn.Close()
                    Label62.Text = "已簽"
                End If
            Else
                MsgBox("簽核前請先確定資料正確") : conn.Close()
            End If
        End If
        月 = "" : TextBox1.Focus()
    End Sub
End Class