frmgzxz.frm

朋友给的

                        SFWC = 1
                        BF = 0
                    Else
                        
                        If SFWC1 = 0 And SFWC2 = 0 Then
                           JBGZ = YGJBGZ * 0.5 * 0.5 * 0.5
                        ElseIf SFWC1 = 0 And SFWC2 = 1 Then
                            JBGZ = YGJBGZ * 0.5
                        ElseIf SFWC1 = 1 And SFWC2 = 0 Then
                            JBGZ = YGJBGZ * 0.5 * 0.5
                        End If
                        BF = 0
                    
                    End If
                    
                    
                    
                    
                    
                End If
            End If
            
            
            
            
                If rs2.State = 1 Then rs2.Close
                rs2.Open "select sum(kk) as kk from ygkq where year(rq)='" & Year(DTPicker1) & "' and month(rq)='" & Month(DTPicker1) & "' and gh='" & rs1(0) & "'", gCnn, adOpenStatic, adLockReadOnly
                
                gCnn.Execute "insert into T_工资表(工号,姓名,部门,月份,基本工资,佣金,考勤扣款,是否完成,完成情况) values('" & rs1(0) & "','" & rs1(1) & "','" & rs1(2) & "','" & Format(DTPicker1, "yyyy-mm") & "','" & JBGZ & "','" & YGYJ & "','" & rs2(0) & "','" & SFWC & "','" & KHYJZS & "')"
                rs1.MoveNext
            Next
        End If
    Else
        If rs1.State = 1 Then rs1.Close
        rs1.Open "select 工号,姓名,部门,业务职级 from VIEWYGXX where 员工状态='0' order by 工号", gCnn, adOpenStatic, adLockReadOnly
        If rs1.RecordCount > 0 Then
            rs1.MoveFirst
            For i = 0 To rs1.RecordCount - 1
                If rs2.State = 1 Then rs2.Close
                rs2.Open "select sum(kk) as kk from ygkq where year(rq)='" & Year(DTPicker1) & "' and month(rq)='" & Month(DTPicker1) & "' and gh='" & rs1(0) & "'", gCnn, adOpenStatic, adLockReadOnly
                If rs3.State = 1 Then rs3.Close
                rs3.Open "select 工号 from T_工资表 where 月份='" & Format(DTPicker1, "yyyy-mm") & "' and 工号='" & rs1("工号") & "'", gCnn, adOpenStatic, adLockReadOnly
                If rs3.EOF Then
                
  '//开始
                
                    If rs2.State = 1 Then rs2.Close
                    rs2.Open "select sum(保险费*业务员佣金*0.01) as yj  from viewbxyw  where 业务员工号='" & Trim(rs1("工号")) & "' and year(投保日期)='" & Year(DTPicker1) & "' and month(投保日期)='" & Month(DTPicker1) & "'", gCnn, adOpenStatic, adLockReadOnly
                    YGYJ = IIf(IsNull(rs2(0)), 0, rs2(0))
                    '二计算基本工资，一年4个季度，每个季度3个月，分别为：1，4，7，10月开始
                    '先算出佣金折算比例
                    If rs2.State = 1 Then rs2.Close
                    rs2.Open "select sum(保险费*业务员佣金*考核佣金折算比例*0.01*0.01) as khyjzs  from viewbxyw  where 业务员工号='" & Trim(rs1("工号")) & "' and year(投保日期)='" & Year(DTPicker1) & "' and month(投保日期)='" & Month(DTPicker1) & "'", gCnn, adOpenStatic, adLockReadOnly
                    KHYJZS = IIf(IsNull(rs2(0)), 0, rs2(0))
                    '分3个月来对比,第一个月的时候
                    '第一个月的时候，如果完成指标，基本工资为100%，没完成为50%
                    If Month(DTPicker1) = 1 Or Month(DTPicker1) = 4 Or Month(DTPicker1) = 7 Or Month(DTPicker1) = 10 Then
                        If rs2.State = 1 Then rs2.Close
                        rs2.Open "select * from khsz where ywzj='" & Trim(rs1("业务职级")) & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            YGJBGZ = rs2("jbgz")
                            YGKHBZ = rs2("ykhbz")
                        End If
                        If Val(KHYJZS) > Val(YGKHBZ) Or Val(KHYJZS) = Val(YGKHBZ) Then
                            JBGZ = YGJBGZ
                            SFWC = 1
                        Else
                            JBGZ = Val(YGJBGZ) * 0.5
                        End If
                    '第二个月，如果完成，基本工资为100%，还有看是否超额，如果2个月的加起来，超过标准的2倍，那么补发上一个月扣除的，也就是50%，如果未完成，在前一个月的基础上*50%，也就是25%，
                    ElseIf Month(DTPicker1) = 2 Or Month(DTPicker1) = 5 Or Month(DTPicker1) = 8 Or Month(DTPicker1) = 11 Then
                    '算第一个月的情况
                        yf = Format(Year(DTPicker1) & "-" & (Month(DTPicker1) - 1), "yyyy-mm")
                        If rs2.State = 1 Then rs2.Close
                        rs2.Open "select 是否完成,完成情况,基本工资 from T_工资表 where 月份='" & yf & "' and 工号='" & rs1("工号") & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            SFWC1 = IIf(IsNull(rs2(0)), 0, rs2(0))
                            WCQK1 = IIf(IsNull(rs2(1)), 0, rs2(1))
                            JBGZ1 = IIf(IsNull(rs2(2)), 0, rs2(2))
                        End If
                        If rs2.State = 1 Then rs2.Close
                        rs2.Open "select * from khsz where ywzj='" & Trim(rs1("业务职级")) & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            YGJBGZ = rs2("jbgz")
                            YGKHBZ = rs2("ykhbz")
                        End If
                    '如果本月完成了，如果本月完成，上个月也完成，补发为0，如果本月完成，上个月未完成，和上个月的完成情况相加，看是否总数大于2倍基本考核，如果大于或等于，补发上个月扣的也就是50%的基本工资
                        If Val(KHYJZS) > Val(YGKHBZ) Or Val(KHYJZS) = Val(YGKHBZ) Then
                            JBGZ = YGJBGZ
                            SFWC = 1
                            If SFWC1 = 0 Then
                                If (KHYJZS + WCQK1) > 2 * YGKHBZ Or (KHYJZS + WCQK1) = 2 * YGKHBZ Then
                                    BF = YGKHBZ * 0.5
                                    
                                Else
                                    BF = 0
                                End If
                            End If
                            
                        Else
                            If (KHYJZS + WCQK1) > 2 * YGKHBZ Then
                                JBGZ = YGKHBZ
                                BF = 0
                                SFWC = 1
                            Else
                                JBGZ = Val(JBGZ1) * 0.5
                                BF = 0
                                SFWC = 0
                            End If
                        End If
                    ElseIf Month(DTPicker1) = 3 Or Month(DTPicker1) = 6 Or Month(DTPicker1) = 9 Or Month(DTPicker1) = 12 Then
                        If rs2.State = 1 Then rs2.Close
                    '算第一个月的情况
                        yf = Format(Year(DTPicker1) & "-" & (Month(DTPicker1) - 2), "yyyy-mm")
                        If rs2.State = 1 Then rs2.Close
                        rs2.Open "select 是否完成,完成情况,基本工资 from T_工资表 where 月份='" & yf & "' and 工号='" & rs1("工号") & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            SFWC1 = IIf(IsNull(rs2(0)), 0, rs2(0))
                            WCQK1 = IIf(IsNull(rs2(1)), 0, rs2(1))
                            JBGZ1 = IIf(IsNull(rs2(2)), 0, rs2(2))
                        End If
                    '算第二个月的情况
                        If rs2.State = 1 Then rs2.Close
                        yf = Format(Year(DTPicker1) & "-" & (Month(DTPicker1) - 1), "yyyy-mm")
                        rs2.Open "select 是否完成,完成情况,基本工资 from T_工资表 where 月份='" & yf & "' and 工号='" & rs1("工号") & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            SFWC2 = IIf(IsNull(rs2(0)), 0, rs2(0))
                            WCQK2 = IIf(IsNull(rs2(1)), 0, rs2(1))
                            JBGZ2 = IIf(IsNull(rs2(2)), 0, rs2(2))
                        End If
                    '算出标准工资和标准考核
                        If rs2.State = 1 Then rs2.Close
                        rs2.Open "select * from khsz where ywzj='" & Trim(rs1("业务职级")) & "'", gCnn, adOpenStatic, adLockReadOnly
                        If Not rs2.EOF Then
                            YGJBGZ = rs2("jbgz")
                            YGKHBZ = rs2("ykhbz")
                        End If
                    '第3个月如果完成了，基本工资为100%，然后3个月的完成情况相加，如果是基本考核标准的3倍或大于，则看头2个月的完成情况，如果未完成，第一个月补发50%，第二个月未完成，补发75%，第3个月如未完成，则看3个月的完成情况相加，达到标准的3倍或大于则发100%
                        If KHYJZS > YGKHBZ Or KHYJZS = YGKHBZ Then
                            JBGZ = YGJBGZ
                            SFWC = 1
                            If (KHYJZS + WCQK1 + WCQK2) > 3 * YGKHBZ Or (KHYJZS + WCQK1 + WCQK2) = 3 * YGKHBZ Then
                                If SFWC1 = 0 And SFWC2 = 0 Then
                                    BF = YGJBGZ * 0.5 + YGJBGZ * 0.75
                                ElseIf SFWC1 = 0 And SFWC2 = 1 Then
                                    BF = YGJBGZ * 0.5
                                ElseIf SFWC1 = 1 And SFWC2 = 0 Then
                                    BF = YGJBGZ * 0.5
                                Else
                                    BF = 0
                                End If
                            Else
                                BF = 0
                            
                            End If
                            
                            
                        Else
                            If (KHYJZS + WCQK1 + WCQK2) > 3 * YGKHBZ Or (KHYJZS + WCQK1 + WCQK2) = 3 * YGKHBZ Then
                                JBGZ = YGJBGZ
                                SFWC = 1
                                BF = 0
                            Else
                                
                                If SFWC1 = 0 And SFWC2 = 0 Then
                                   JBGZ = YGJBGZ * 0.5 * 0.5 * 0.5
                                ElseIf SFWC1 = 0 And SFWC2 = 1 Then
                                    JBGZ = YGJBGZ * 0.5
                                ElseIf SFWC1 = 1 And SFWC2 = 0 Then
                                    JBGZ = YGJBGZ * 0.5 * 0.5
                                End If
                                BF = 0
                            
                            End If
                        
                        
                        
                        
                        
                        End If
                    End If
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
   '///结束
                
                    gCnn.Execute "insert into T_工资表(工号,姓名,部门,月份,考勤扣款) values('" & rs1(0) & "','" & rs1(1) & "','" & rs1(2) & "','" & Format(DTPicker1, "yyyy-mm") & "','" & rs2(0) & "')"
                End If
                rs1.MoveNext
            Next
        End If
    
    
    
    
    End If
    
    If rs.State = 1 Then rs.Close
    If rs1.State = 1 Then rs1.Close
    gGzyf = Format(DTPicker1, "yyyy-mm")
    frmGongzi.Show vbModal
    Unload Me
End Sub

Private Sub Command2_Click()
    Unload Me
End Sub

Private Sub DTPicker1_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then SendKeys "{tab}"
End Sub

Private Sub Form_Load()
'    strGzfs = ""
    If Me.WindowState = 0 Then Me.Move 0, 0
    
    gSQL = ""
    DTPicker1 = Date
End Sub