checkform.vb

这是一个人事工资管理系统

'文件名：CheckForm.vb
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop.Excel
Imports System.Reflection
Imports Microsoft.Office.Core
Public Class CheckForm
    Public MyCompany As String
    Private Sub 考勤记录BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 考勤记录BindingNavigatorSaveItem.Click
        Me.Validate()
        Me.考勤记录BindingSource.EndEdit()
        Me.考勤记录TableAdapter.Update(Me.MyPersonnelDataSet.考勤记录)
    End Sub
    Private Sub CheckForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.考勤记录TableAdapter.Fill(Me.MyPersonnelDataSet.考勤记录)
        Me.MyPersonnelDataSet.考勤记录.Rows.Clear()
        For i As Integer = 2006 To 2100 Step 1
            Me.考勤年份ToolStripComboBox.Items.Add(i.ToString())
        Next
        For i As Integer = 1 To 12 Step 1
            Me.月份ToolStripComboBox.Items.Add(i.ToString())
        Next
        '获取公司的部门信息            
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyDepartmentTable As New System.Data.DataTable()
        Dim MySQL As String = "Select 部门名称 From 公司部门"
        Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
        MyAdapter.Fill(MyDepartmentTable)
        For Each MyRow As DataRow In MyDepartmentTable.Rows
            Me.部门ToolStripComboBox.Items.Add(MyRow(0).ToString())
        Next
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
    End Sub
    Private Sub 查询ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 查询ToolStripButton.Click
        If (Me.考勤年份ToolStripComboBox.Text.Length < 1 Or Me.月份ToolStripComboBox.Text.Length < 1 Or Me.部门ToolStripComboBox.Text.Length < 1) Then
            Return
        End If
        Try
            Me.考勤记录TableAdapter.FillBy(Me.MyPersonnelDataSet.考勤记录, Convert.ToInt16(Me.考勤年份ToolStripComboBox.Text), Convert.ToInt16(Me.月份ToolStripComboBox.Text), Me.部门ToolStripComboBox.Text)
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        If (Me.MyPersonnelDataSet.考勤记录.Rows.Count < 1) Then
            Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
            Dim MyConnection As New SqlConnection(MySQLConnectionString)
            MyConnection.Open()
            Dim MyQueryTable As New System.Data.DataTable()
            Dim MySQL As String = "Select * From 在职员工视图 WHERE 部门='" + Me.部门ToolStripComboBox.Text + "'"
            Dim MyAdapter As New SqlDataAdapter(MySQL, MyConnection)
            MyAdapter.Fill(MyQueryTable)
            For Each MyRow As DataRow In MyQueryTable.Rows
                Dim My自编号 As String = Me.GetMyID()
                Dim My员工编号 As String = MyRow(1).ToString()
                Dim My员工姓名 As String = MyRow(3).ToString()
                Dim My考勤年份 As Integer = Convert.ToInt16(Me.考勤年份ToolStripComboBox.Text)
                Dim My考勤月份 As Integer = Convert.ToInt16(Me.月份ToolStripComboBox.Text)
                MySQL = "INSERT INTO 考勤记录((自编号) ,(员工编号), (员工姓名), (考勤年份), (考勤月份)) VALUES (@自编号 ,@员工编号, @员工姓名, @考勤年份, @考勤月份)"
                MyConnection = New SqlConnection(MySQLConnectionString)
                MyConnection.Open()
                Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
                MyCommand.CommandText = MySQL
                MyCommand.Parameters.Add(New SqlParameter("@自编号", SqlDbType.VarChar))
                MyCommand.Parameters.Add(New SqlParameter("@员工编号", SqlDbType.VarChar))
                MyCommand.Parameters.Add(New SqlParameter("@员工姓名", SqlDbType.VarChar))
                MyCommand.Parameters.Add(New SqlParameter("@考勤年份", SqlDbType.Int))
                MyCommand.Parameters.Add(New SqlParameter("@考勤月份", SqlDbType.Int))
                MyCommand.Parameters("@自编号").Value = My自编号
                MyCommand.Parameters("@员工编号").Value = My员工编号
                MyCommand.Parameters("@员工姓名").Value = My员工姓名
                MyCommand.Parameters("@考勤年份").Value = My考勤年份
                MyCommand.Parameters("@考勤月份").Value = My考勤月份
                MyCommand.ExecuteNonQuery()
            Next
            If (MyConnection.State = ConnectionState.Open) Then
                MyConnection.Close()
            End If
            查询ToolStripButton_Click(Nothing, Nothing)
        End If
    End Sub
    Private Function GetMyID() As String
        '自动计算自编号
        Dim MySQLConnectionString As String = My.Settings.MyPersonnelConnectionString
        Dim MyConnection As New SqlConnection(MySQLConnectionString)
        MyConnection.Open()
        Dim MyCommand As SqlCommand = MyConnection.CreateCommand()
        MyCommand.CommandText = "Select max(自编号) 最大编号 From 考勤记录"
        Dim MyResult As Object = MyCommand.ExecuteScalar()
        Dim MyID As System.Int64 = 1
        If (Not (MyResult Is System.DBNull.Value)) Then
            Dim MyMaxID As String = MyResult.ToString().Trim()
            MyMaxID = MyMaxID.Substring(2, MyMaxID.Length - 2)
            MyID = Convert.ToInt64(MyMaxID) + 1
        End If
        Dim MyLength As Integer = MyID.ToString().Length
        Dim MyNewID As String = ""
        Select Case (MyLength)
            Case 1
                    MyNewID = "KQ0000000" + MyID.ToString()
            Case 2
                    MyNewID = "KQ000000" + MyID.ToString()
            Case 3
                    MyNewID = "KQ00000" + MyID.ToString()
            Case 4
                    MyNewID = "KQ0000" + MyID.ToString()
            Case 5
                    MyNewID = "KQ000" + MyID.ToString()
            Case 6
                    MyNewID = "KQ00" + MyID.ToString()
            Case 7
                MyNewID = "KQ0" + MyID.ToString()
        End Select
        If (MyConnection.State = ConnectionState.Open) Then
            MyConnection.Close()
        End If
        Return MyNewID
    End Function
    Private Sub 出勤ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 出勤ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "/"
    End Sub
    Private Sub 迟到ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 迟到ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = ">"
    End Sub
    Private Sub 产假ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 产假ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "√"
    End Sub
    Private Sub 早退ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 早退ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "<"
    End Sub
    Private Sub 事假ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 事假ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "#"
    End Sub
    Private Sub 病假ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 病假ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "+"
    End Sub
    Private Sub 婚假ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 婚假ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "△"
    End Sub
    Private Sub 旷工ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 旷工ToolStripButton.Click
        If ((Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 2) Or (Me.考勤记录DataGridView.CurrentCell.ColumnIndex = 1)) Then
            Return
        End If
        Me.考勤记录DataGridView.CurrentCell.Value = "×"
    End Sub
    Private Sub 打印ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 打印ToolStripButton.Click
        Dim MyQueryTable As New System.Data.DataTable()
        MyQueryTable = Me.MyPersonnelDataSet.考勤记录.Copy()
        If (MyQueryTable.Rows.Count < 1) Then
            Return
        End If
          '导出Excel表格数据文件
        Dim MyExcel As ApplicationClass
        Dim MyWorkBooks As Workbooks
        Dim MyWorkBook As Workbook
        Dim MyWorkSheet As Worksheet
        Dim MyRange As Range
        Dim MyData(500, 35) As Object
        Dim i, j As Integer
        MyExcel = New ApplicationClass()
        MyExcel.Visible = True
        If (MyExcel Is Nothing) Then
            MessageBox.Show("Excel程序无法启动！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Return
        End If
        MyWorkBooks = MyExcel.Workbooks
        MyWorkBook = MyWorkBooks.Add(Missing.Value)
        MyWorkSheet = MyWorkBook.Worksheets(1)
        MyRange = MyWorkSheet.Range("A5", "AF5")
        MyData(0, 0) = "姓名"
        For i = 1 To 31 Step 1
            MyData(0, i) = i.ToString()
        Next
        j = 1
        '输出数据库记录
        For Each MyRow As DataRow In MyQueryTable.Rows
            For i = 2 To MyQueryTable.Columns.Count - 2 Step 1
                MyData(j, i - 2) = MyRow(i).ToString()
            Next
            j = j + 1
        Next
        MyRange = MyRange.Resize(MyQueryTable.Rows.Count + 1, 32)
        MyRange.Value2 = MyData
        MyRange.EntireColumn.AutoFit()
        MyWorkSheet.Cells(2, 2) = Me.MyCompany + Me.部门ToolStripComboBox.Text + "考勤表"
        MyWorkSheet.Cells(4, 1) = "考勤日期：" + Me.考勤年份ToolStripComboBox.Text + "年" + Me.月份ToolStripComboBox.Text + "月份"
        MyWorkSheet.Cells(MyQueryTable.Rows.Count + 7, 1) = "考勤符号说明：出勤 /， 迟到>， 早退<， 产假√，事假#，病假+，婚假△，旷工×"
    End Sub
End Class