VERSION 5.00
Begin VB.Form frmsumsalary 
   Caption         =   "計算工資"
   ClientHeight    =   3525
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   5610
   LinkTopic       =   "Form2"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3525
   ScaleWidth      =   5610
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton cmdcancel 
      Caption         =   "取 消"
      BeginProperty Font 
         Name            =   "楷體_GB2312"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   3120
      TabIndex        =   3
      Top             =   2280
      Width           =   1455
   End
   Begin VB.CommandButton cmdok 
      Caption         =   "開始統(tǒng)計"
      BeginProperty Font 
         Name            =   "楷體_GB2312"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   1080
      TabIndex        =   2
      Top             =   2280
      Width           =   1455
   End
   Begin VB.ComboBox commonth 
      BeginProperty Font 
         Name            =   "宋體"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   360
      Left            =   2520
      TabIndex        =   1
      Top             =   600
      Width           =   2175
   End
   Begin VB.Label Label1 
      Caption         =   "統(tǒng)計月份"
      BeginProperty Font 
         Name            =   "楷體_GB2312"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   495
      Left            =   960
      TabIndex        =   0
      Top             =   600
      Width           =   1215
   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 cmdcancel_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
Private Sub cmdok_Click()
  Dim sql As String
  Dim days As Integer
  If Me.commonth.Text = 4 Then
   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 = "員工工資統(tǒng)計列表"
   Call frmresult.listtopic
   Call frmresult.showdata(sql)
   frmresult.Caption = "統(tǒng)計結(jié)果列表"
   frmresult.ZOrder 0
   Unload Me
  Else
   MsgBox "這個月的工資未統(tǒng)計", vbOKOnly + vbExclamation, "提示"
  End If
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 "已經(jīng)統(tǒng)計", 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.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 "統(tǒng)計完畢", vbOKOnly + vbExclamation, "提示"
  End If
  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