frmquerysalary.frm

数据库课程设计

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "Msflxgrd.ocx"
Begin VB.Form frmQuerySalary 
   Caption         =   "工资查询"
   ClientHeight    =   4215
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   8175
   LinkTopic       =   "Form1"
   ScaleHeight     =   4215
   ScaleWidth      =   8175
   StartUpPosition =   3  'Windows Default
   Begin MSFlexGridLib.MSFlexGrid fgSalary 
      Height          =   1935
      Left            =   120
      TabIndex        =   7
      Top             =   2160
      Width           =   7935
      _ExtentX        =   13996
      _ExtentY        =   3413
      _Version        =   393216
   End
   Begin VB.CommandButton cmdCancel 
      Caption         =   "返回(&N)"
      Height          =   375
      Left            =   4560
      TabIndex        =   6
      Top             =   1560
      Width           =   1335
   End
   Begin VB.CommandButton cmdOK 
      Caption         =   "查询(&Y)"
      Height          =   375
      Left            =   2520
      TabIndex        =   5
      Top             =   1560
      Width           =   1455
   End
   Begin VB.Frame Frame1 
      Caption         =   "查询条件"
      Height          =   1215
      Left            =   360
      TabIndex        =   0
      Top             =   120
      Width           =   7575
      Begin VB.ComboBox cboDate 
         Height          =   315
         Left            =   1920
         TabIndex        =   8
         Text            =   "Combo1"
         Top             =   720
         Width           =   1935
      End
      Begin VB.OptionButton optType 
         Caption         =   "浏览全部工资单"
         Height          =   375
         Index           =   2
         Left            =   4560
         TabIndex        =   4
         Top             =   240
         Width           =   1935
      End
      Begin VB.OptionButton optType 
         Caption         =   "按发放日期查询"
         Height          =   375
         Index           =   1
         Left            =   360
         TabIndex        =   3
         Top             =   720
         Width           =   1575
      End
      Begin VB.TextBox txtNo 
         Height          =   285
         Left            =   1920
         TabIndex        =   2
         Text            =   "Text1"
         Top             =   240
         Width           =   1935
      End
      Begin VB.OptionButton optType 
         Caption         =   "按员工号查询"
         Height          =   375
         Index           =   0
         Left            =   360
         TabIndex        =   1
         Top             =   240
         Width           =   1575
      End
   End
End
Attribute VB_Name = "frmQuerySalary"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public sqlStr As String
Public msgText As String

Private Sub cboDate_Click()
optType(1).Value = True
End Sub

Private Sub cmdCancel_Click()
Unload Me

End Sub

Private Sub cmdOK_Click()
querySalary
End Sub

Private Sub Form_Load()
initDate
txtNo = ""
optType(0).Value = True
End Sub

Sub initDate()
Dim rstDate As ADODB.Recordset

    '从数据库中读取所有薪水方法日期并添加到组合列表框中
    sqlStr = "select DISTINCT payDate from salary"
    Set rstDate = ExecuteSQL(sqlStr, msgText)
    cboDate.Clear
    
    If Not rstDate.EOF Then
        
            Do While Not rstDate.EOF
                cboDate.AddItem Trim(rstDate.Fields(0))
                rstDate.MoveNext
            Loop
            cboDate.ListIndex = 0
        
    Else
        MsgBox "没有薪水发放日期信息，请添加！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstDate.Close

End Sub

Private Sub txtNo_GotFocus()
optType(0).Value = True
End Sub

Private Sub querySalary()
Dim rs As ADODB.Recordset
Dim i As Integer
Dim j As Integer

If optType(0).Value = True Then
    sqlStr = "select * from salary where employeeNo='" & txtNo.Text & "'"
End If

If optType(1).Value = True Then
    sqlStr = "select * from salary where payDate=#" & cboDate.Text & "#"
End If

If optType(2).Value = True Then
    sqlStr = "select * from salary"
End If


Set rs = ExecuteSQL(sqlStr, msgText)

If rs.RecordCount = 0 Then

    MsgBox "没有查找满足条件的数据！", vbExclamation, "提示"
    fgSalary.Rows = 1
   
Else
    fgSalary.Rows = rs.RecordCount + 1
    
    fgSalary.Cols = 14
    
    
    '设定行高
    For i = 0 To fgSalary.Rows - 1
       fgSalary.RowHeight(i) = 280
    Next i
    
    '设定列的属性
    fgSalary.Row = 0
    
    For i = 0 To fgSalary.Cols - 1
       fgSalary.Col = i  '指定当前列为第i列
       fgSalary.FixedAlignment(i) = 4  '每列内容居中显示
       Select Case i
           Case 0
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "员工编号"
           Case 1
              fgSalary.ColWidth(i) = 720  '设定列宽
              fgSalary.Text = "底薪"
           Case 2
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "补贴"
           Case 3
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "奖金"
           Case 4
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "加班费"
           Case 5
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "扣考核"
           Case 6
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "房贴"
           Case 7
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "养老金"
           Case 8
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "医疗保险"
           Case 9
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "住房公积金"
           Case 10
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "税前"
           Case 11
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "所得税"
           Case 12
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "实发工资"
           Case 13
              fgSalary.ColWidth(i) = 1000  '设定列宽
              fgSalary.Text = "记发时间"
        End Select
           
    Next i
       
       
    'rs.MoveFirst
    
    i = 1
    
    While (Not rs.EOF)
    
       fgSalary.Row = i
       For j = 0 To fgSalary.Cols - 1
       
        fgSalary.Col = j '设置当前为列为第j列
        fgSalary.CellAlignment = 4  '每列内容居中显示
    
        
        Select Case j
           Case 0
              fgSalary.Text = rs.Fields("employeeNo")
           Case 1
              fgSalary.Text = rs.Fields("basicSalary")
           Case 2
              fgSalary.Text = rs.Fields("allowance")
           Case 3
              fgSalary.Text = rs.Fields("prize")
           Case 4
              fgSalary.Text = rs.Fields("callbackPay")
           Case 5
              fgSalary.Text = rs.Fields("cut")
           Case 6
              fgSalary.Text = rs.Fields("houseAllowance")
           Case 7
              fgSalary.Text = rs.Fields("annuity")
           Case 8
              fgSalary.Text = rs.Fields("medicare")
           Case 9
              fgSalary.Text = rs.Fields("housingAccFund")
           Case 10
              fgSalary.Text = rs.Fields("beforeTax")
           Case 11
              fgSalary.Text = rs.Fields("tax")
           Case 12
              fgSalary.Text = rs.Fields("finalPay")
           Case 13
              fgSalary.Text = rs.Fields("payDate")
        End Select
      Next j
    
        rs.MoveNext
        i = i + 1
    Wend

End If

rs.Close

End Sub