frmcheckinfo2.frm

员工信息管理系统

VERSION 5.00
Object = "{C932BA88-4374-101B-A56C-00AA003668DC}#1.1#0"; "MSMASK32.OCX"
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Begin VB.Form frmCheckInfo2 
   BorderStyle     =   3  'Fixed Dialog
   Caption         =   "员工考勤查询"
   ClientHeight    =   6795
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   9480
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   6795
   ScaleWidth      =   9480
   ShowInTaskbar   =   0   'False
   StartUpPosition =   1  '所有者中心
   Begin VB.Frame Frame1 
      Caption         =   "查询条件"
      Height          =   1935
      Left            =   0
      TabIndex        =   3
      Top             =   0
      Width           =   8775
      Begin VB.CommandButton Command3 
         Caption         =   "开始搜索"
         Default         =   -1  'True
         Height          =   375
         Left            =   7080
         TabIndex        =   9
         Top             =   1320
         Width           =   975
      End
      Begin VB.ComboBox cboDept 
         Height          =   300
         Left            =   1200
         TabIndex        =   8
         Top             =   1320
         Width           =   2775
      End
      Begin VB.ComboBox cboCheckYear 
         Height          =   300
         Left            =   5400
         TabIndex        =   7
         Top             =   360
         Width           =   1335
      End
      Begin VB.TextBox Text1 
         Height          =   390
         Left            =   1200
         TabIndex        =   6
         Top             =   840
         Width           =   2775
      End
      Begin VB.ComboBox cboCheckMonth 
         Height          =   300
         Left            =   7200
         TabIndex        =   5
         Top             =   360
         Width           =   855
      End
      Begin MSMask.MaskEdBox MskId 
         Height          =   375
         Left            =   1200
         TabIndex        =   4
         Top             =   360
         Width           =   2775
         _ExtentX        =   4895
         _ExtentY        =   661
         _Version        =   393216
         PromptChar      =   "_"
      End
      Begin VB.Label Label1 
         AutoSize        =   -1  'True
         Caption         =   "工  号："
         Height          =   180
         Left            =   360
         TabIndex        =   16
         Top             =   480
         Width           =   720
      End
      Begin VB.Label Label2 
         AutoSize        =   -1  'True
         Caption         =   "姓  名："
         Height          =   180
         Left            =   360
         TabIndex        =   15
         Top             =   960
         Width           =   720
      End
      Begin VB.Label Label3 
         AutoSize        =   -1  'True
         Caption         =   "部  门："
         Height          =   180
         Left            =   360
         TabIndex        =   14
         Top             =   1440
         Width           =   720
      End
      Begin VB.Label Label4 
         AutoSize        =   -1  'True
         Caption         =   "工资年月："
         Height          =   180
         Left            =   4440
         TabIndex        =   13
         Top             =   480
         Width           =   900
      End
      Begin VB.Label Label5 
         AutoSize        =   -1  'True
         Caption         =   "以上条件均为可选项"
         Height          =   180
         Left            =   4920
         TabIndex        =   12
         Top             =   1440
         Width           =   1620
      End
      Begin VB.Label Label6 
         AutoSize        =   -1  'True
         Caption         =   "年"
         Height          =   180
         Left            =   6840
         TabIndex        =   11
         Top             =   480
         Width           =   180
      End
      Begin VB.Label Label7 
         AutoSize        =   -1  'True
         Caption         =   "月"
         Height          =   180
         Left            =   8160
         TabIndex        =   10
         Top             =   480
         Width           =   180
      End
   End
   Begin VB.CommandButton Command1 
      Caption         =   "返  回"
      Height          =   375
      Left            =   7800
      TabIndex        =   2
      Top             =   5880
      Width           =   975
   End
   Begin MSFlexGridLib.MSFlexGrid flxShow 
      Height          =   3255
      Left            =   360
      TabIndex        =   1
      Top             =   2280
      Width           =   8775
      _ExtentX        =   15478
      _ExtentY        =   5741
      _Version        =   393216
      Redraw          =   -1  'True
      ScrollBars      =   2
   End
   Begin VB.Label Label8 
      AutoSize        =   -1  'True
      Height          =   180
      Left            =   600
      TabIndex        =   0
      Top             =   5760
      Width           =   90
   End
End
Attribute VB_Name = "frmCheckInfo2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i As Integer

Private Sub Command1_Click()
    Unload Me
End Sub


Private Sub Command3_Click()
    Dim strId As String
    Dim strName As String
    Dim strDept As String
    Dim strCheck As String
    '按工号查询
    If Len(Trim(MskId)) = 0 Then
        strId = ""
    Else
        strId = "and a.emp_id= CLng('" & MskId & "')"
    End If
    '按姓名查询
    If Len(Trim(Text1)) = 0 Then
        strName = ""
    Else
        strName = "and b.emp_name='" & Trim(Text1) & "'"
    End If
    '按部门查询
    If cboDept = "" Then
        strDept = ""
    Else
        strDept = "and c.dept_name='" & cboDept & "'"
    End If
    '按时间查询
    If cboCheckYear = "" Then
        If cboCheckMonth = "" Then   '年月都为空时
            strCheck = ""
        Else                         '任意年份的指定月
            strCheck = "and a.check_ym in ('" & Year(Date) & cboCheckMonth & "','" & Year(Date) - 1 & cboCheckMonth & "')"
        End If
    Else
        If cboCheckMonth = "" Then   '指定年份的任意月
            strCheck = "and a.check_ym in ('" & cboCheckYear & "01" & "','" & cboCheckYear & "02" & "','" & cboCheckYear & "03" & "','" & cboCheckYear & "04" & "','" & cboCheckYear & "05" & "','" & cboCheckYear & "06" & "','" & cboCheckYear & "03" & "','" & cboCheckYear & "07" & "','" & cboCheckYear & "08" & "','" & cboCheckYear & "09" & "','" & cboCheckYear & "10" & "','" & cboCheckYear & "11" & "','" & cboCheckYear & "12" & "' )"
        Else                         '指定的年月
            strCheck = "and a.check_ym in('" & cboCheckYear & cboCheckMonth & "')"
        End If
    End If
     '打开一个数据集
    strSql = "select a.emp_id,b.emp_name,c.dept_name,a.check_ym,a.w_days,a.l_nums,a.e_nums,a.h_days,a.n_days from checkin a ,employee b,department c where  a.emp_id = b.emp_id and c.dept_id=b.dept_id " & strCheck & " " & strDept & " " & strName & " " & strId & " order by b.emp_id"
    rs.Open strSql, dbConn, adOpenForwardOnly, adLockReadOnly
    flxShow.Rows = 1
    If rs.EOF Then
       
    Else
        '填写数据

    Do While Not rs.EOF
        flxShow.Rows = flxShow.Rows + 1

        flxShow.TextMatrix(flxShow.Rows - 1, 0) = rs.Fields(0).Value
        For i = 2 To rs.Fields.Count
            flxShow.TextMatrix(flxShow.Rows - 1, i) = rs.Fields(i - 1).Value
        Next i

        rs.MoveNext
    Loop

    End If
    Label8 = "找到" & flxShow.Rows - 1 & "条记录"
    rs.Close
    
End Sub

Private Sub Form_Load()
     Me.Icon = LoadPicture(App.Path & "\Graph07.ico")
'    设置列数
    flxShow.Cols = 10
    '列标题
    flxShow.TextMatrix(0, 0) = "工号"
    flxShow.TextMatrix(0, 1) = ""
    flxShow.TextMatrix(0, 2) = "姓名"
    flxShow.TextMatrix(0, 3) = "部门"
    flxShow.TextMatrix(0, 4) = "考勤时间"
    flxShow.TextMatrix(0, 5) = "应出勤"
    flxShow.TextMatrix(0, 6) = "迟到"
    flxShow.TextMatrix(0, 7) = "早退"
    flxShow.TextMatrix(0, 8) = "请假"
    flxShow.TextMatrix(0, 9) = "旷工"
    
    
    '设置列宽
    flxShow.ColWidth(0) = 1000
    flxShow.ColWidth(1) = 0
    flxShow.ColWidth(2) = 1200
    flxShow.ColWidth(3) = 1200
    flxShow.ColWidth(4) = 1200
    flxShow.ColWidth(5) = 820
    flxShow.ColWidth(6) = 820
    flxShow.ColWidth(7) = 820
    flxShow.ColWidth(8) = 820
    flxShow.ColWidth(9) = 900
    
    '设置各列的对齐方式
     For i = 0 To 9
        flxShow.ColAlignment(i) = 0
     Next i

     '表头项居中
     flxShow.FillStyle = flexFillRepeat
     flxShow.Col = 0
     flxShow.Row = 0
     flxShow.RowSel = 1
     flxShow.ColSel = flxShow.Cols - 1
     flxShow.CellAlignment = 4
    '强制跨越整个行选择
    flxShow.SelectionMode = flexSelectionByRow
    flxShow.Rows = 1
    
    cboCheckYear.AddItem ""
    cboCheckYear.AddItem Year(Date)
    cboCheckYear.AddItem Year(Date) - 1
    cboCheckYear.ListIndex = 1
    cboCheckMonth.AddItem ""
    For i = 1 To 12
        If i < 10 Then
            cboCheckMonth.AddItem "0" & i
        Else
            cboCheckMonth.AddItem i
        End If
        
        If i = Month(Date) Then
            cboCheckMonth.ListIndex = cboCheckMonth.NewIndex
        End If
    Next i
    '部门
    strSql = "Select dept_id,dept_name from department Order By dept_id"
    rs.Open strSql, dbConn, adOpenForwardOnly, adLockReadOnly
    cboDept.AddItem ""
    Do While Not rs.EOF
        cboDept.AddItem (rs.Fields("dept_name").Value)
        cboDept.ItemData(cboDept.NewIndex) = rs.Fields("dept_id").Value
        rs.MoveNext
    Loop
    rs.Close
    cboDept.ListIndex = 0

    
End Sub