frmcreatesalary.frm

数据库课程设计

      Begin VB.TextBox txtItem 
         Enabled         =   0   'False
         Height          =   270
         Index           =   11
         Left            =   1320
         MaxLength       =   8
         TabIndex        =   4
         Top             =   720
         Width           =   1455
      End
      Begin VB.TextBox txtItem 
         Enabled         =   0   'False
         Height          =   270
         Index           =   12
         Left            =   3840
         MaxLength       =   10
         TabIndex        =   3
         Top             =   720
         Width           =   1575
      End
      Begin VB.TextBox txtItem 
         Enabled         =   0   'False
         Height          =   270
         Index           =   9
         Left            =   1320
         MaxLength       =   8
         TabIndex        =   2
         Top             =   360
         Width           =   1455
      End
      Begin VB.TextBox txtItem 
         Enabled         =   0   'False
         Height          =   270
         Index           =   10
         Left            =   3840
         MaxLength       =   8
         TabIndex        =   1
         Top             =   360
         Width           =   1575
      End
      Begin VB.Label Label2 
         Caption         =   "税前小计："
         Height          =   255
         Index           =   12
         Left            =   360
         TabIndex        =   8
         Top             =   360
         Width           =   1215
      End
      Begin VB.Label Label2 
         Caption         =   "所  得  税："
         Height          =   255
         Index           =   13
         Left            =   2880
         TabIndex        =   7
         Top             =   360
         Width           =   975
      End
      Begin VB.Label Label2 
         Caption         =   "实发工资："
         Height          =   255
         Index           =   14
         Left            =   360
         TabIndex        =   6
         Top             =   720
         Width           =   1215
      End
      Begin VB.Label Label2 
         Caption         =   "计发时间："
         Height          =   255
         Index           =   15
         Left            =   2880
         TabIndex        =   5
         Top             =   720
         Width           =   975
      End
   End
End
Attribute VB_Name = "frmCreateSalary"
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 cboNo_Click()
Dim rstEmployeeName As ADODB.Recordset

    '通过员工编号查找员工姓名
    sqlStr = "select name from employeeBasic where employeeNo='" & cboNo.Text & "'"
    Set rstEmployeeName = ExecuteSQL(sqlStr, msgText)
    
    If Not rstEmployeeName.EOF Then
        txtName.Text = Trim(rstEmployeeName.Fields(0))
    Else
        MsgBox "没有员工信息，请添加员工信息！", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    rstEmployeeName.Close

End Sub

Private Sub cmdCaculate_Click()
Dim total As Double
Dim netPay As Double
Dim tax As Double
Dim i As Integer
total = Val(txtItem(0)) + Val(txtItem(1)) + Val(txtItem(2)) _
       + Val(txtItem(3)) - Val(txtItem(4)) + Val(txtItem(5)) _
       - Val(txtItem(6)) - Val(txtItem(7)) - Val(txtItem(8))
tax = getTax(total)
netPay = total - tax

txtItem(9).Text = total
txtItem(10).Text = tax
txtItem(11).Text = netPay
End Sub

Private Sub cmdExit_Click()
Unload Me

End Sub

Private Sub cmdSave_Click()
   Dim rstSalary As ADODB.Recordset
   Dim i As Integer
   
   For i = 0 To 12
      If (txtItem(i).Text = "") Then
      MsgBox "请将信息填写完整", vbOKOnly + vbExclamation, "警告"
      Exit Sub
      End If
   Next i
   
    '添加新记录
    sqlStr = "select * from salary"
    Set rstSalary = ExecuteSQL(sqlStr, msgText)
    rstSalary.addNew
    rstSalary.Fields("employeeNo") = cboNo.Text
    rstSalary.Fields("basicSalary") = txtItem(0).Text
    rstSalary.Fields("allowance") = txtItem(1).Text
    rstSalary.Fields("prize") = txtItem(2).Text
    rstSalary.Fields("callbackpay") = txtItem(3).Text
    rstSalary.Fields("cut") = txtItem(4).Text
    rstSalary.Fields("houseAllowance") = txtItem(5).Text
    rstSalary.Fields("annuity") = txtItem(6).Text
    rstSalary.Fields("medicare") = txtItem(7).Text
    rstSalary.Fields("housingAccFund") = txtItem(8).Text
    rstSalary.Fields("beforeTax") = txtItem(9).Text
    rstSalary.Fields("tax") = txtItem(10).Text
    rstSalary.Fields("finalPay") = txtItem(11).Text
    rstSalary.Fields("payDate") = txtItem(12).Text
    
    rstSalary.Update
    rstSalary.Close
    
        
    MsgBox "工资单添加完成！", vbOKOnly + vbExclamation, "警告"
    
    initTextBox
    initEmployeeNo
    
End Sub

Private Sub Form_Load()

'清空文本框内容
initTextBox

'初始化员工编号信息
initEmployeeNo
End Sub

Sub initEmployeeNo()
Dim rstEmployeeNo As ADODB.Recordset

    '从数据库中读取所有员工编号并添加到组合列表框中
    sqlStr = "select employeeNo from employeeBasic"
    Set rstEmployeeNo = ExecuteSQL(sqlStr, msgText)
    cboNo.Clear
    
    If Not rstEmployeeNo.EOF Then
        
            Do While Not rstEmployeeNo.EOF
                cboNo.AddItem Trim(rstEmployeeNo.Fields(0))
                rstEmployeeNo.MoveNext
            Loop
            cboNo.ListIndex = 0
        
    Else
        MsgBox "没有员工信息，请添加员工信息！", vbOKOnly + vbExclamation, "警告"
        
        Exit Sub
    End If
    rstEmployeeNo.Close

End Sub

Sub initTextBox()
Dim i As Integer
For i = 0 To 11
   txtItem(i).Text = ""
Next i

txtItem(12).Text = Date

End Sub

Function getTax(salary As Double) As Double

        Dim start As Double
        Dim netSalary As Double
        Dim taxPaid As Double
        
        start = 800
        netSalary = salary - start
        
        If netSalary >= 100000 Then
            taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + 15000 * 0.2 + 20000 * (0.25 + 0.3 + 0.35 + 0.4) + (netSalary - 100000) * 0.45
        Else
            If netSalary >= 80000 Then
                taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + 15000 * 0.2 + 20000 * (0.25 + 0.3 + 0.35) + (netSalary - 80000) * 0.4
            Else
                If netSalary >= 60000 Then
                    taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + 15000 * 0.2 + 20000 * (0.25 + 0.3) + (netSalary - 60000) * 0.35
                Else
                  If netSalary >= 40000 Then
                    taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + 15000 * 0.2 + 20000 * 0.25 + (netSalary - 40000) * 0.3
                  Else
                    If netSalary >= 20000 Then
                        taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + 15000 * 0.2 + (netSalary - 20000) * 0.25
                    Else
                        If netSalary >= 5000 Then
                            taxPaid = 500 * 0.05 + 1500 * 0.1 + 3000 * 0.15 + (netSalary - 5000) * 0.2
                        Else
                            If netSalary >= 2000 Then
                                taxPaid = 500 * 0.05 + 1500 * 0.1 + (netSalary - 2000) * 0.15
                            Else
                                If netSalary >= 500 Then
                                    taxPaid = 500 * 0.05 + (netSalary - 500) * 0.1
                                Else
                                    If netSalary > 0 Then
                                        taxPaid = netSalary * 0.05
                                    Else
                                        taxPaid = 0
                                    End If
                                End If
                            End If
                        End If
                    End If
                 End If
               End If
            End If
        End If
        
        getTax = taxPaid
        
End Function