工资结算_sc.frm

适合于中小型企业管理

Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim i As Integer
Dim TS(20) As String
Dim TxtSql As String
Dim RsFamc As ADODB.Recordset
Dim Rstmp As ADODB.Recordset

Private Sub CmbBmmc_Click()
    If CmbBmmc.ListIndex > -1 And ChkMx.Value = 0 Then
      TxtSql = "select 部门名称,工序名称,图号,品名,规格,硝材,sum(一级品数) as 一级品,sum(二级品数) as 二级品,sum(留用数) as 留用,sum(料质数) as 料质,一级品价,sum(金额1) as 金额1,sum(领一级品) as 领一级品,sum(领二级品) as 领二级品,sum(应交废品) as 应交废品,sum(实际废品) as 实际废品,sum(差额1) as 差额1,原材料价,sum(奖赔) as 奖赔,sum(送检数) as 送检数,sum(应交正品) as 应交正品,sum(金额2) as 金额2,sum(废品数) as 交废品,sum(欠交数) as 欠交数,sum(金额3) as 金额3,sum(合计) as 合计 from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "' and 部门名称 ='" & CmbBmmc.Text & "' group by 部门名称 ,工序名称,图号,品名,规格,硝材,一级品价,原材料价 order by 工序名称,图号,品名,规格,硝材"
      OutTxt.Text = "C:\" & CmbJsfa.Text & "-" & CmbBmmc.Text & "1.xls"
    ElseIf CmbBmmc.ListIndex > -1 And ChkMx.Value = 1 Then
      TxtSql = "select * from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "' and 部门名称 ='" & CmbBmmc.Text & "' order by 员工姓名,工序名称,图号,品名,规格,硝材"
      OutTxt.Text = "C:\" & CmbJsfa.Text & "-" & CmbBmmc.Text & "2.xls"
    End If
      Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
      Set DGList.DataSource = Rstmp
End Sub

Private Sub CmdDlg_Click()
    Cdlg.DialogTitle = "另存为Excel文件："
    Cdlg.Filter = "Excel文件|*.Xls|所有文件|*.*"
    Cdlg.ShowSave
    If Cdlg.FileName = "" Then Exit Sub
         OutTxt.Text = Cdlg.FileName
End Sub

Private Sub CmdSave_Click()
On Error GoTo errs
    Dim Rs As ADODB.Recordset
    Dim ExcelApp As Excel.Application
    Dim ExcelBook As Excel.Workbook
    Dim ExcelSheet As Excel.Worksheet

    Set ExcelApp = New Excel.Application
    ExcelApp.Visible = False
    Set ExcelBook = ExcelApp.Workbooks.Add
    Set ExcelSheet = ExcelBook.Worksheets.Item(1)
    
    Set Rs = New ADODB.Recordset
     
    Rs.Open TxtSql, Cw_DataEnvi.DataConnect, , adLockPessimistic, adCmdText
    RecordsetToExcel Rs, ExcelSheet
    If OutTxt.Text = "" Then
      MsgBox "请指定输出文件位置和文件名！", 16, "严重错误"
      Exit Sub
    End If
    On Error GoTo ErrSave
     ExcelBook.Close True, OutTxt.Text
     MsgBox "输出成功！文件位于" & OutTxt.Text
     Rs.Close

Exit Sub
errs:
    MsgBox "Select 语句错误！", 16, "严重错误"
    ExcelBook.Close False
     Exit Sub
ErrSave:
    MsgBox "输出错误！", 16, "严重错误"
End Sub

Private Sub CmdZnjs_Click()
CmdZnjs.Caption = "结算中..."
CmdZnjs.Enabled = False
Dim Rs01 As ADODB.Recordset
Dim Rs02 As ADODB.Recordset
Dim Rs03 As ADODB.Recordset
Dim Cmdtmp As ADODB.Command
Dim Count As Double

Set Rs01 = New ADODB.Recordset
Set Rs02 = New ADODB.Recordset
Set Rs03 = New ADODB.Recordset
Set Cmdtmp = New ADODB.Command

Rs01.Open "select * from Gz_基础数据", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
If Rs01.EOF And Rs01.BOF Then
    MsgBox "请到“基础数据-方案参数”中添加并保存！", vbInformation, "无法进行工资结算"
    Exit Sub
End If
Cmdtmp.ActiveConnection = Cw_DataEnvi.DataConnect
Cmdtmp.CommandText = "delete from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "'"
Cmdtmp.Execute
Rs02.Open "select * from Gz_产量核对SC where 方案名称= '" & CmbJsfa.Text & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
Rs03.Open "select * from Gz_工资结算Sc", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
If Rs02.EOF And Rs02.BOF Then
    MsgBox "请到“产量核对-产量核对1”中重新计算并保存！", vbInformation, "无法进行工资结算"
    Exit Sub
End If

'更新欠交数等信息 可以更新为存储过程1
'========================================
Do While Not Rs02.EOF
    Rs03.AddNew
    Rs03!方案名称 = Rs02!方案名称
    Rs03!部门名称 = Rs02!部门名称
    Rs03!员工姓名 = Rs02!员工姓名
    Rs03!工序名称 = Rs02!工序名称
    Rs03!品名 = Rs02!品名
    Rs03!规格 = Rs02!规格
    Rs03!图号 = Rs02!图号
    Rs03!硝材 = Rs02!硝材
    Rs03!一级品数 = IIf(IsNull(Rs02!一级品) = True, "0", Rs02!一级品)
    Rs03!二级品数 = IIf(IsNull(Rs02!二级品) = True, "0", Rs02!二级品)
    Rs03!料质数 = IIf(IsNull(Rs02!料质数) = True, "0", Rs02!料质数)
    Rs03!领一级品 = IIf(IsNull(Rs02!领一级) = True, "0", Rs02!领一级)
    Rs03!领二级品 = IIf(IsNull(Rs02!领二级) = True, "0", Rs02!领二级)
    Rs03!实际废品 = IIf(IsNull(Rs02!领一级) = True, "0", Rs02!领一级) + IIf(IsNull(Rs02!领二级) = True, "0", Rs02!领二级) - IIf(IsNull(Rs02!一级品) = True, "0", Rs02!一级品) - CLng((IIf(IsNull(Rs02!二级品) = True, "0", Rs02!二级品) * 0.9)) - IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数) - IIf(IsNull(Rs02!料质数) = True, "0", Rs02!料质数)
    Rs03!送检数 = IIf(IsNull(Rs02!送检数) = True, "0", Rs02!送检数)
    Rs03!废品数 = IIf(IsNull(Rs02!废品数) = True, "0", Rs02!废品数)
    Rs03!留用数 = IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数)
    Rs03!欠交数 = IIf(IsNull(Rs02!欠交数) = True, IIf(IsNull(Rs02!领一级) = True, "0", Rs02!领一级) + IIf(IsNull(Rs02!领二级) = True, "0", Rs02!领二级) - IIf(IsNull(Rs02!一级品) = True, "0", Rs02!一级品) - IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数) - IIf(IsNull(Rs02!二级品) = True, "0", Rs02!二级品) - IIf(IsNull(Rs02!料质数) = True, "0", Rs02!料质数) - IIf(IsNull(Rs02!废品数) = True, "0", Rs02!废品数), Rs02!欠交数)
    Rs03.Update
    
    Rs02.MoveNext
Loop
    Rs02.Close
    Rs03.Close
'===========================================


'工资结算
'===========================================
Do While Not Rs01.EOF
    Rs03.Open "select * from Gz_工资结算Sc where 工序名称='" & Rs01!工序名称 & "' and 图号='" & Rs01!图号 & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockOptimistic, adCmdText
    Do While Not Rs03.EOF
        Count = "0.0"
        Rs03!一级品价 = Rs01!一级品价
        Rs03!二级品价 = Rs01!二级品价
        Rs03!料质价 = Rs01!料质价
        Rs03!金额1 = Rs01!一级品价 * Rs03!一级品数 + Rs03!留用数 * 0.9 * Rs01!一级品价 + Rs01!二级品价 * Rs03!二级品数 + Rs01!料质价 * Rs03!料质数
        Count = Rs01!一级品价 * Rs03!一级品数 + Rs03!留用数 * 0.9 * Rs01!一级品价 + Rs01!二级品价 * Rs03!二级品数 + Rs01!料质价 * Rs03!料质数
        Rs03!应交废品 = CLng(Rs01!一废品率 * Rs03!领一级品 / 100) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100)
        Rs03!原材料价 = Rs01!原材料价
        Rs03!应交正品 = CLng(Rs03!送检数 * Rs01!成品率 / 100)
        Rs03!差额2 = CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数
        If Rs01!成品率 = TS(0) * 1 Or Rs01!成品率 = TS(1) * 1 Or Rs01!成品率 = TS(2) * 1 Or Rs01!成品率 = TS(3) * 1 Or Rs01!成品率 = TS(4) * 1 Or Rs01!成品率 = TS(5) * 1 Or Rs01!成品率 = TS(6) * 1 Or Rs01!成品率 = TS(7) * 1 Or Rs01!成品率 = TS(8) * 1 Or Rs01!成品率 = TS(9) * 1 Then
                Rs03!实际废品 = Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数
                Rs03!差额1 = CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - (Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数)
            If Rs01!原材料价 = 3 Or Rs01!原材料价 = 5 Then
                Rs03!奖赔 = Rs01!原材料价 * (CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - (Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数))
                Count = Count + Rs01!原材料价 * (CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - (Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数))
            Else
                Rs03!奖赔 = Rs01!原材料价 * 0.5 * (CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - (Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数))
                Count = Count + Rs01!原材料价 * 0.5 * (CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - (Rs03!领一级品 + Rs03!领二级品 - Rs03!一级品数 - Rs03!留用数 - Rs03!二级品数 - Rs03!料质数))
            End If
        Else
            Rs03!差额1 = CLng(Rs01!一废品率 / 100 * Rs03!领一级品) + CLng(Rs01!二废品率 * Rs03!领二级品 / 100) - Rs03!实际废品
            If Rs01!原材料价 = 3 Or Rs01!原材料价 = 5 Then
                Rs03!奖赔 = Rs01!原材料价 * (CLng(Rs01!一废品率 * Rs03!领一级品 / 100) + CLng(Rs01!二废品率 / 100 * Rs03!领二级品) - Rs03!实际废品)
                Count = Count + Rs01!原材料价 * (CLng(Rs01!一废品率 * Rs03!领一级品 / 100) + CLng(Rs01!二废品率 / 100 * Rs03!领二级品) - Rs03!实际废品)
            Else
                Rs03!奖赔 = Rs01!原材料价 * 0.5 * (CLng(Rs01!一废品率 * Rs03!领一级品 / 100) + CLng(Rs01!二废品率 / 100 * Rs03!领二级品) - Rs03!实际废品)
                Count = Count + Rs01!原材料价 * 0.5 * (CLng(Rs01!一废品率 * Rs03!领一级品 / 100) + CLng(Rs01!二废品率 / 100 * Rs03!领二级品) - Rs03!实际废品)
            End If
        End If
            
        If Rs01!成品率 = TS(10) * 1 Or Rs01!成品率 = TS(11) * 1 Or Rs01!成品率 = TS(12) * 1 Or Rs01!成品率 = TS(13) * 1 Or Rs01!成品率 = TS(14) * 1 Or Rs01!成品率 = TS(15) * 1 Or Rs01!成品率 = TS(16) * 1 Or Rs01!成品率 = TS(17) * 1 Or Rs01!成品率 = TS(18) * 1 Or Rs01!成品率 = TS(19) * 1 Then
            If Rs01!一级品价 * 2 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数) >= 0 Then
                Rs03!金额2 = Rs01!一级品价 * 2 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数) * (-1)
                Count = Count - Rs01!一级品价 * 2 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数)
            Else
                Rs03!金额2 = Rs01!一级品价 * 2 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数) * (-1)
                Count = Count - Rs01!一级品价 * 2 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数)
            End If

        Else
            If Rs01!一级品价 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数) >= 0 Then
                Rs03!金额2 = Rs01!一级品价 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数) * (-1)
                Count = Count - Rs01!一级品价 * (CLng(Rs03!送检数 * Rs01!成品率 / 100) - Rs03!一级品数 - Rs03!留用数 - Rs03!料质数)
            Else
                Rs03!金额2 = "0"
            End If
        End If
         
            
    If ChkJe3.Value = 0 Then
        If Rs01!原材料价 * Rs03!欠交数 >= 0 Then
            If Rs01!原材料价 = 3 Or Rs01!原材料价 = 5 Then
                Rs03!金额3 = Rs01!原材料价 * 2 * Rs03!欠交数 * (-1)
                Count = Count - Rs01!原材料价 * 2 * Rs03!欠交数
            Else
                Rs03!金额3 = Rs01!原材料价 * 0.5 * Rs03!欠交数 * (-1)
                Count = Count - Rs01!原材料价 * 0.5 * Rs03!欠交数
            End If
        Else
            Rs03!金额3 = "0"
        End If
    Else
            If Rs01!原材料价 = 3 Or Rs01!原材料价 = 5 Then
                Rs03!金额3 = Rs01!原材料价 * 2 * Rs03!欠交数 * (-1)
                Count = Count - Rs01!原材料价 * 2 * Rs03!欠交数
            Else
                Rs03!金额3 = Rs01!原材料价 * 0.5 * Rs03!欠交数 * (-1)
                Count = Count - Rs01!原材料价 * 0.5 * Rs03!欠交数
            End If
    End If
        Rs03!合计 = Count
        Rs03.Update
        Rs03.MoveNext
    Loop
    Rs03.Close
    Rs01.MoveNext
Loop
Rs01.Close
'===========================================
CmdZnjs.Caption = "完成结算"
Call LoadTree
    
End Sub

Private Sub LoadTree()
    '添加部门列表
    Dim topNode As Node
    
    Dim Rsb As ADODB.Recordset
    Set Rsb = New ADODB.Recordset
    Rsb.Open "select distinct 部门名称 from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
    TV.Nodes.Clear
    CmbBmmc.Clear
    i = 0
    Do While Not Rsb.EOF
        Set topNode = TV.Nodes.Add(, , "A" & CStr(Rsb!部门名称), Rsb!部门名称, "Root")
        topNode.Tag = Rsb!部门名称
        CmbBmmc.AddItem Rsb!部门名称
        LoadChild (Rsb!部门名称)
        Rsb.MoveNext
    Loop
    If Rsb.State <> adStateClosed Then Rsb.Close
End Sub

Private Sub LoadChild(Lbj As String)
        Dim child As Node
        Dim Rsyg As ADODB.Recordset
        Set Rsyg = New ADODB.Recordset
    
        Rsyg.Open "select distinct 员工姓名 from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "' and 部门名称 = '" & Lbj & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
        Do While Not Rsyg.EOF
            Set child = TV.Nodes.Add("A" & Lbj, tvwChild, "B" & CStr(i), Rsyg!员工姓名, "Child")
                child.Tag = Rsyg!员工姓名
                i = i + 1
            Rsyg.MoveNext
        Loop
    If Rsyg.State <> adStateClosed Then Rsyg.Close
End Sub

Private Sub Form_Load()
    Set RsFamc = New ADODB.Recordset
    RsFamc.Open "select * from Gz_结算方案 order by 创建日期 desc", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
    CmbJsfa.Clear
    Do While Not RsFamc.EOF
        CmbJsfa.AddItem RsFamc!方案名称
        RsFamc.MoveNext
    Loop
    If RsFamc.State <> adStateClosed Then RsFamc.Close

    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset
    Rs.Open "Gz_特征数据", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdTable
    If Rs.BOF And Rs.EOF Then
        MsgBox "工资_特征数据为空！", vbCritical, "错误提示"
    Else
       For i = 0 To 19
        TS(i) = Rs.Fields(i)
       Next i
    
    End If
    Rs.Close
End Sub

'纪录导出到Execl
Public Sub RecordsetToExcel(Rs As ADODB.Recordset, excel_sheet As Excel.Worksheet)
    Dim i As Long, j As Long
    Dim excel_range As Excel.Range
    Dim col_count As Long

    If Rs.RecordCount = 0 Then
        Exit Sub
    End If

    Set excel_range = excel_sheet.Cells
    col_count = Rs.Fields.Count
                
    For i = 0 To col_count - 1
        excel_sheet.Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next
    excel_sheet.Range(excel_sheet.Cells(1, 1), _
                      excel_sheet.Cells(1, col_count)).Font.Bold = True
        
    excel_sheet.Range("A2").CopyFromRecordset Rs
            
End Sub

Private Sub tv_NodeClick(ByVal Node As MSComctlLib.Node)
   On Error Resume Next
   Dim txtSQLBJ As String
    If Left(Node.Key, 1) = "B" Then
        YGXM = Node.Tag
        CJMC = Node.Parent.Tag
              
      TxtSql = "select * from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "' and 部门名称 = '" & CJMC & "' and 员工姓名= '" & YGXM & "' order by 工序名称,图号,品名,规格"
      Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
      Set DGList.DataSource = Rstmp
'      Rstmp.Close
'      Set Rstmp = Nothing
      OutTxt.Text = "C:\" & CmbJsfa.Text & CJMC & "-" & YGXM & "2.xls"
    ElseIf Left(Node.Key, 1) = "A" Then
        CJMC = Node.Tag
         
      TxtSql = "select * from Gz_工资结算Sc where 方案名称='" & CmbJsfa.Text & "' and 部门名称 = '" & CJMC & "' order by 工序名称,图号,品名,规格"
      Set Rstmp = New ADODB.Recordset
    Rstmp.Open TxtSql, Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
      Set DGList.DataSource = Rstmp
'      Rstmp.Close
'      Set Rstmp = Nothing
      OutTxt.Text = "C:\" & CmbJsfa.Text & "-" & combo2.Text & "2.xls"
    End If

End Sub