frmsumsalary.frm

企业工资管理

VERSION 5.00
Begin VB.Form frmSumSalary 
   Caption         =   "计算工资"
   ClientHeight    =   3720
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   5925
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3720
   ScaleWidth      =   5925
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton cmaCancel 
      Caption         =   "取  消"
      Height          =   495
      Left            =   3360
      TabIndex        =   3
      Top             =   2400
      Width           =   1455
   End
   Begin VB.CommandButton cmdOK 
      Caption         =   "开始统计"
      Height          =   495
      Left            =   1320
      TabIndex        =   2
      Top             =   2400
      Width           =   1455
   End
   Begin VB.ComboBox comMonth 
      BeginProperty Font 
         Name            =   "楷体_GB2312"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   360
      Left            =   2640
      TabIndex        =   1
      Top             =   1080
      Width           =   1575
   End
   Begin VB.Label Label1 
      Caption         =   "统计月份"
      BeginProperty Font 
         Name            =   "楷体_GB2312"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   255
      Left            =   1320
      TabIndex        =   0
      Top             =   1080
      Width           =   1095
   End
End
Attribute VB_Name = "frmSumSalary"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Private firstday As String
Private lastday As String

Private Sub cmaCancel_Click()
    Unload Me
    Exit Sub
End Sub

Public Sub reSumSalary(recorddate As Date)                 '修改后重新统计工资
    Dim sql As String
    Dim days As Integer
    Dim rs As New ADODB.Recordset
    
    firstday = Year(recorddate) & "-" & Month(recorddate) & "-1"
    days = DateDiff("d", Year(recorddate) & "-" & Month(recorddate) & "-1", _
                            Year(recorddate) & "-" & Month(recorddate) + 1 & "-1")
    lastday = Year(recorddate) & "-" & Month(recorddate) & "-" & days
    
    sql = "select * from SalaryStatistics where YearMonth between #" & firstday & "# and #"
    sql = sql & lastday & "#"
    Set rs = getRS(sql, "Salary")
    If rs.EOF = False Then                               '判断是否存在记录
        sql = "delete from SalaryStatistics where YearMonth between #" & firstday & "# and #"
        sql = sql & lastday & "#"
        Call TransactSQL(sql, "Salary")
    End If
    rs.Close
    sql = "select * from SalaryStatistics"
    Call sumSalary(firstday, lastday)
    Call frmResult.ListTopic
    Call frmResult.ShowData(sql)
    frmResult.ZOrder 0
End Sub

Public Sub sumSalary(begintime As String, endtime As String)  '统计工资
    Dim sql As String
    Dim rs As New ADODB.Recordset
    Dim rsFormula As New ADODB.Recordset
    Dim rsOthers As New ADODB.Recordset
    Dim rsBasic As New ADODB.Recordset
    Dim rsNew As New ADODB.Recordset
    Dim dBonus As Double                                 '奖金
    Dim dWelfare As Double                               '福利
    Dim dAllowance As Double                             '津贴
    Dim dSub As Double                                   '扣发
    Dim dOthers As Double                                '其他
    Dim dTotal As Double                                 '总额
    
    
    sql = "select * from SalaryStatistics where YearMonth between #" & begintime & "# and #"
    sql = sql & endtime & "#"
    Set rs = getRS(sql, "Salary")
    If rs.EOF = False Then                               '判断是否存在记录
        MsgBox "已经统计！", vbOKOnly + vbExclamation, "提示！"
        frmResult.Show
        frmResult.ZOrder 0
        Unload Me
        Exit Sub
    End If
    
    sql = "select * from AttendanceStatistics where RecordMonth between #" & begintime & "# and #"
    sql = sql & endtime & "#"
    Set rs = getRS(sql, "Salary")
    If rs.EOF = False Then
        While Not rs.EOF
            dBonus = 0
            dWelfare = 0
            dAllowance = 0
            dSub = 0
            dOthers = 0
            dTotal = 0
            
            sql = "select * from SalarySetting where StuffID='"
            sql = sql & rs(1) & "'"
            Set rsBasic = getRS(sql, "Salary")
            
            sql = "select * from SalaryOther where YearMonth between #" & begintime & "# and #"
            sql = sql & endtime & "# and StuffID='" & rs(1) & "'"
            Set rsOthers = getRS(sql, "Salary")
            While Not rsOthers.EOF
                If rsOthers(3) = 1 Then
                    dBonus = rsOthers(5) + dBonus
                End If
                If rsOthers(3) = 2 Then
                    dAllowance = rsOthers(5) + dAllowance
                End If
                If rsOthers(3) = 3 Then
                    dWelfare = rsOthers(5) + dWelfare
                End If
                If rsOthers(3) = 4 Then
                    dSub = rsOthers(5) + dSub
                End If
                If rsOthers(3) = 5 Then
                    dOthers = rsOthers(5) + dOthers
                End If
                rsOthers.MoveNext
            Wend
            
            sql = "select * from FormulaSetting"
            Set rsFormula = getRS(sql, "Salary")
            
            dTotal = rsBasic(3) * rs(4) * 8 + dBonus + dWelfare + dAllowance + dOthers - dSub
            dTotal = dTotal - rs(5) * rsFormula(4) - rs(6) * rsFormula(3)
            dTotal = dTotal + rs(7) * (rsFormula(0) / 100) * rsBasic(3) * 8
            dTotal = dTotal + rs(8) * (rsFormula(1) / 100) * rsBasic(3) * 8
            dTotal = dTotal + rs(9) * rsFormula(2)
            
            sql = "select * from SalaryStatistics"
            Set rsNew = getRS(sql, "Salary")
            rsNew.AddNew                                           '添加记录
            rsNew.Fields(1) = rs(1)
            rsNew.Fields(2) = rs(2)
            rsNew.Fields(3) = rs(3)
            rsNew.Fields(4) = rsBasic(3) * rs(4) * 8
            rsNew.Fields(5) = dBonus
            rsNew.Fields(6) = dWelfare
            rsNew.Fields(7) = dAllowance
            rsNew.Fields(8) = dSub
            rsNew.Fields(9) = rs(5) * rsFormula(4)
            rsNew.Fields(10) = rs(6) * rsFormula(3)
            rsNew.Fields(11) = rs(7) * (rsFormula(0) / 100) * rsBasic(3) * 8 + _
                               rs(8) * (rsFormula(1) / 100) * rsBasic(3) * 8
            rsNew.Fields(12) = rs(9) * rsFormula(2)
            rsNew.Fields(13) = dOthers
            rsNew.Fields(14) = dTotal
            rsNew.Update
            rsNew.Close
            
            rsOthers.Close
            
            rs.MoveNext
        Wend
        rs.Close
        rsFormula.Close
        rsBasic.Close
        MsgBox "统计完毕！", vbOKOnly + vbExclamation, "提示！"
    End If
End Sub

Private Sub cmdOK_Click()
    Dim sql As String
    Dim days As Integer
    
    firstday = Year(Date) & "-" & Me.comMonth.Text & "-1"
    days = DateDiff("d", Year(Date) & "-" & Me.comMonth.Text & "-1", _
                            Year(Date) & "-" & Me.comMonth.Text + 1 & "-1")
    lastday = Year(Date) & "-" & Me.comMonth.Text & "-" & days
    
    Call sumSalary(firstday, lastday)
    sql = "select * from SalaryStatistics"
    frmResult.SSTab1.Caption = "员工工资统计列表"
    Call frmResult.ListTopic
    Call frmResult.ShowData(sql)
    frmResult.Caption = "统计结果列表"
    frmResult.ZOrder 0
    Unload Me
End Sub

Private Sub Form_Load()
    Dim i As Integer
    For i = 1 To 12
        Me.comMonth.AddItem i
    Next i
    Me.comMonth.ListIndex = 0
End Sub