frmmonthpayment.frm

传销工资管理 请验证一下哈

       If .RecordCount <> 0 Then
          cmdSQL = "update 员工资料 set 担保人等级= 'C' where 担保人工号= '" & RecognizorNo & "'"
          cmdBackup.CommandText = cmdSQL
          cmdBackup.Execute
       End If
       
       If rs员工资料.State = adStateOpen Then
          rs员工资料.Close
       End If
     End With
     
     rs购货清单.MoveNext
  Loop
  
  If rs员工资料.State = adStateOpen Then
     rs员工资料.Close
  End If
  
  rs购货清单.Close
  
  'cmdSQL = "delete * from 产品统计 where 提成=0 and 个人绩效奖=0 and 网络绩效奖=0 and 伙食津贴=0 and 差旅津贴=0"
  'cmdBackup.CommandText = cmdSQL
  'cmdBackup.Execute
  
 '******************************************************************************************'
 '*                                                                                       * '
 '*                       将员工资料表里的数据复制到员工资料副本里                        * '
 '*                                                                                       * '
 '******************************************************************************************'
  
  EmployeeInfoSQL = "select * from 员工资料"
  rs员工资料.Open EmployeeInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
  
  cmdSQL = "update 员工资料 set 结算日期= #" & EndPaymentDay & "#"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  cmdSQL = "delete * from 结算员工资料"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  cmdSQL = "insert into 结算员工资料 select * from 员工资料"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  'MsgBox "复制员工资料副本完毕！"
 '******************************************************************************************'
 '*                                                                                       * '
 '*                       C级别以上员工级别晋升、数据清零                                 * '
 '*                                                                                       * '
 '******************************************************************************************'
  '结算完后员工等级为 D 的当月积分数据清零
                          
  cmdSQL = "update 员工资料 set 当月积分=0 where 等级= 'D'"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  '结算完后员工等级为 C 的数据判断升级 并把当月积分数据清零
  
  cmdSQL = "update 员工资料 set 当月积分=0 where 等级= 'C' and 累计积分 < 21000"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  cmdSQL = "update 员工资料 set 当月积分=0,累计积分=0,等级= 'B',晋升日期 = #" & EndPaymentDay & "# where 等级= 'C' and 累计积分 >= 21000"
  cmdBackup.CommandText = cmdSQL
  cmdBackup.Execute
  
  
  
  '结算完后员工等级为 B 的数据清零并判断升级
  If rs员工资料.State = adStateOpen Then
     rs员工资料.Close
  End If
  
  If rs担保人.State = adStateOpen Then
     rs担保人.Close
  End If
  
  rs担保人.CursorLocation = adUseClient
  
  EmployeeInfoSQL = "select * from 员工资料 where 等级= 'B'"
  rs员工资料.Open EmployeeInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
  
  If rs员工资料.RecordCount <> 0 Then
     With rs员工资料
       .MoveFirst
       Do While Not .EOF
          !当月积分 = 0
          EmployeeNo = !工号
          EmployeeName = !姓名
          EmployeeLevel = !等级
                                            
          If !累计积分 >= 112000 Then
             ' 寻找下级等级为B的记录
             RecognizorInfoSQL = "select * from 员工资料 where 等级= 'B' and 担保人工号= '" & EmployeeNo & "'"
             rs担保人.Open RecognizorInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
                                  
             If rs担保人.RecordCount >= 4 Then   '判断下级等级为B的记录是否>=4
                If rs担保人.State = adStateOpen Then
                   rs担保人.Close
                End If
                        
                !累计积分 = 0
                !等级 = "A"
                !晋升日期 = EndPaymentDay
                RecognizorNo = !担保人工号
Line50:
                'If !担保人工号 <> "" Then
                If RecognizorNo <> "" Then
                   '判断担保人等级是否为A，如果是，那么升级员工的担保人工号为该工号，否则继续往上搜索
                   RecognizorNo = !担保人工号
                   
                   If rs担保人.State = adStateOpen Then
                      rs担保人.Close
                   End If
                   
                   RecognizorInfoSQL = "select * from 员工资料 where 工号= '" & RecognizorNo & "'"
                   rs担保人.Open RecognizorInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
                   
                   If rs担保人("等级") = "A" Then
                      !担保人工号 = rs担保人("工号")
                      !担保人姓名 = rs担保人("姓名")
                      !担保人等级 = rs担保人("等级")
                      
                      If rs担保人.State = adStateOpen Then
                         rs担保人.Close
                      End If
                      
                      RecognizorInfoSQL = "select * from 员工资料 where 等级= 'A' and 原始担保人工号= '" & EmployeeNo & "'"
                      rs担保人.Open RecognizorInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
                      
                      If rs担保人.RecordCount <> 0 Then
                         rs担保人.MoveFirst
                         Do While Not rs担保人.EOF
                            rs担保人("担保人工号") = EmployeeNo
                            rs担保人("担保人姓名") = EmployeeName
                            rs担保人("担保人等级") = EmployeeLevel
                            
                            rs担保人.Update
                            
                            rs担保人.MoveNext
                         Loop
                      End If
                   Else
                      RecognizorNo = rs担保人("担保人")
                      GoTo Line50
                   End If
                Else
                   !担保人工号 = ""
                   !担保人姓名 = ""
                   !担保人等级 = ""
                   
                   If rs担保人.State = adStateOpen Then
                      rs担保人.Close
                   End If
                      
                   RecognizorInfoSQL = "select * from 员工资料 where 等级= 'A' and 原始担保人工号= '" & EmployeeNo & "'"
                   rs担保人.Open RecognizorInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
                      
                   If rs担保人.RecordCount <> 0 Then
                      rs担保人.MoveFirst
                      Do While Not rs担保人.EOF
                         rs担保人("担保人工号") = EmployeeNo
                         rs担保人("担保人姓名") = EmployeeName
                         rs担保人("担保人等级") = EmployeeLevel
                            
                         rs担保人.Update
                            
                         rs担保人.MoveNext
                      Loop
                   End If
                End If
                   
                If rs担保人.State = adStateOpen Then
                   rs担保人.Close
                End If
                
             End If
          End If
          If rs担保人.State = adStateOpen Then
             rs担保人.Close
          End If
          
          .Update
          
          .MoveNext
       Loop
     End With
  End If
                
  '结算完后员工等级为 A 的数据清零
  If rs员工资料.State = adStateOpen Then
     rs员工资料.Close
  End If
  
  EmployeeInfoSQL = "select * from 员工资料 where 等级= 'A'"
  rs员工资料.Open EmployeeInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
  
  If rs员工资料.RecordCount <> 0 Then
     With rs员工资料
       .MoveFirst
       Do While Not .EOF
          !当月积分 = 0
          rs员工资料.Update
          .MoveNext
       Loop
     End With
  End If
  
  rs员工资料.Close
  
'****************************************************************************'
'                                                                            '
'                            更改担保人等级                                  '
'                                                                            '
'****************************************************************************'
  If rs员工资料.State = adStateOpen Then
     rs员工资料.Close
  End If
  
  If rs担保人.State = adStateOpen Then
     rs担保人.Close
  End If
  
  EmployeeInfoSQL = "select * from 员工资料"
  rs员工资料.Open EmployeeInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
  
  With rs员工资料
    .MoveFirst
    Do While Not .EOF
       If !担保人工号 <> "" Then
          RecognizorNo = !担保人工号
          RecognizorInfoSQL = "select * from 员工资料 where 工号= '" & RecognizorNo & "'"
          rs担保人.Open RecognizorInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
          
          If rs担保人("等级") <> !担保人等级 Then
             !担保人等级 = rs担保人("等级")
          End If
          
          rs担保人.Close
       End If
       .MoveNext
    Loop
  End With
'****************************************************************************'
'                                                                            '
'                            追加记录到员工资料报表中                        '
'                                                                            '
'****************************************************************************'
  Sql = "insert into 员工资料报表备份 select * from 员工资料报表"
  
  cmdBackup.CommandText = Sql
  cmdBackup.Execute
  
  Sql = "INSERT INTO 员工资料报表 SELECT 员工资料.工号, 员工资料.姓名, 员工资料.等级, 员工资料.担保人工号, 员工资料.担保人姓名, " & _
        "员工资料.担保人等级, 结算员工资料.当月积分, 员工资料.累计积分, 员工资料.提成, " & _
        "员工资料.个人绩效奖, 员工资料.网络绩效奖, " & _
        "[员工资料]![个人绩效奖]+[员工资料]![网络绩效奖] AS 绩效奖金, " & _
        "员工资料.伙食津贴, 员工资料.差旅津贴, 员工资料.结算日期, 员工详细资料.填表聘用日期 as 应聘日期, " & _
        "员工详细资料.性别, 员工详细资料.家庭住址, 员工详细资料.身份证号码 " & _
        "FROM (员工资料 INNER JOIN 结算员工资料 ON 员工资料.工号 = 结算员工资料.工号) " & _
        "INNER JOIN 员工详细资料 ON 结算员工资料.工号 = 员工详细资料.工号;"
         
  cmdBackup.CommandText = Sql
  cmdBackup.Execute
   
  Sql = "update 员工资料报表 set 税金=0,实际收入=0"
  cmdBackup.CommandText = Sql
  cmdBackup.Execute
   
  Sql = "update 产品统计 set 绩效奖金=个人绩效奖+网络绩效奖"
  cmdBackup.CommandText = Sql
  cmdBackup.Execute
'****************************************************************************'
'                                                                            '
'                      更新员工资料报表中税金、实际收入字段                  '
'                                                                            '
'****************************************************************************'
  If rs员工资料.State = adStateOpen Then
     rs员工资料.Close
  End If
  
  EmployeeInfoSQL = "select * from 员工资料报表 where 结算日期= #" & EndPaymentDay & "#"
  rs员工资料.Open EmployeeInfoSQL, Conn员工资料, adOpenKeyset, adLockPessimistic
                      
  With rs员工资料
    .MoveFirst
    Do While Not .EOF
       Tax = !提成 + !绩效奖金 + !伙食津贴 + !差旅津贴
       Income = Tax
       !税金 = Round((Tax - 800) * TaxRate(Tax), 2)
       !实际收入 = Round((Income - !税金), 2)
       .Update
       .MoveNext
    Loop
  End With
  
  rs员工资料.Close
'****************************************************************************'
'                                                                            '
'                            显示工资结算后的报表                            '
'                                                                            '
'****************************************************************************'
  If chkShowReport.Value = 1 Then
     CrystalReport1.Destination = crptToWindow
     CrystalReport1.PrintReport
  End If
'****************************************************************************'
'                                                                            '
'                            关闭进度条并提示                            '
'                                                                            '
'****************************************************************************'
  Label3.Visible = True
  Label4.Visible = False
  Frame2.Visible = False
  
  MsgBox "每月工资结算成功，注意：每月" & vbCrLf & "只能结算一次！"
  
  cmdOk.Enabled = False
End Sub

Private Sub Form_Load()
  Dim ConnStr购货清单 As String
  Dim ConnStr员工资料 As String
  
  
  Label3.Visible = True
  Label4.Visible = False
  Frame2.Visible = False
  
  chkBackup.Value = 1
  chkShowReport.Value = 0
  
  DTPicker1.Value = Date
  If DTPicker1.Month = 1 Then
     StartYear = DTPicker1.Year - 1
     StartMonth = 12
     StartDay = PaymentDay
     StartPaymentDay = StartYear & "-" & StartMonth & "-" & StartDay
     StartPaymentDay = CDate(StartPaymentDay)
     
     EndYear = DTPicker1.Year - 1
     EndMonth = DTPicker1.Month
     EndDay = PaymentDay
     EndPaymentDay = EndYear & "-" & EndMonth & "-" & EndDay
     EndPaymentDay = CDate(EndPaymentDay)
  Else
     StartYear = DTPicker1.Year
     StartMonth = Str(Val(DTPicker1.Month) - 1)
     StartDay = PaymentDay
     StartPaymentDay = StartYear & "-" & StartMonth & "-" & StartDay
     StartPaymentDay = CDate(StartPaymentDay)
     
     EndYear = DTPicker1.Year
     EndMonth = DTPicker1.Month
     EndDay = PaymentDay
     EndPaymentDay = EndYear & "-" & EndMonth & "-" & EndDay
     EndPaymentDay = CDate(EndPaymentDay)
  End If
  
  '打开购货清单数据集
  ConnStr购货清单 = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\system\database\system.mdb"
  Conn购货清单.Open ConnStr购货清单
  
  Sql = "select * from 购货清单 where 购货时间 between #" & StartPaymentDay & "# and #" & EndPaymentDay & "#  order by 订单号码"
  
  rs购货清单.CursorLocation = adUseClient
  rs购货清单.Open Sql, Conn购货清单, adOpenKeyset, adLockPessimistic
  
  Sql = "select * from 产品统计"
  rs产品统计.CursorLocation = adUseClient
  rs产品统计.Open Sql, Conn购货清单, adOpenKeyset, adLockPessimistic
  
  Sql = "select * from 公司代号"
  rs公司代号.CursorLocation = adUseClient
  rs公司代号.Open Sql, Conn购货清单, adOpenKeyset, adLockPessimistic
  
  '打开员工资料数据集
  ConnStr员工资料 = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\system\database\system.mdb"
  Conn员工资料.Open ConnStr员工资料
  
  Set cmdBackup.ActiveConnection = Conn员工资料
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Conn购货清单.Close
  Conn员工资料.Close
End Sub