📄 工资结算_dm.frm
字号:
BeginProperty Column00
ColumnWidth = 1635.024
EndProperty
BeginProperty Column01
ColumnWidth = 1635.024
EndProperty
EndProperty
End
Begin VB.Label Label2
Alignment = 2 'Center
AutoSize = -1 'True
BackStyle = 0 'Transparent
Caption = "方案名称"
BeginProperty Font
Name = "宋体"
Size = 18
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
ForeColor = &H00000000&
Height = 360
Left = 3240
TabIndex = 12
Top = 300
Width = 1515
End
Begin VB.Line Line1
BorderStyle = 5 'Dash-Dot-Dot
X1 = 3000
X2 = 13080
Y1 = 960
Y2 = 960
End
Begin vsElasticLightLibCtl.vsElasticLight vsElasticLight1
Left = 3240
OleObjectBlob = "工资结算_Dm.frx":2141
Top = 480
End
Begin VB.Label Label3
Alignment = 2 'Center
AutoSize = -1 'True
BackStyle = 0 'Transparent
Caption = "车间名称"
BeginProperty Font
Name = "宋体"
Size = 15
Charset = 134
Weight = 700
Underline = 0 'False
Italic = 0 'False
Strikethrough = 0 'False
EndProperty
Height = 300
Left = 930
TabIndex = 11
Top = 240
Width = 1275
End
End
Attribute VB_Name = "FrmGzjs_Dm"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim i As Integer
Dim TS(24) 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(擦二级) as 擦二级,sum(擦报废) as 擦报废,一级品价,sum(金额1) as 金额1,sum(领一级) as 领一级,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(金额3) as 金额3,sum(合计) as 合计 from Gz_工资结算Dm 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_工资结算Dm 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
' Rstmp.Close
' Set Rstmp = Nothing
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
'纪录导出到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 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_工资结算Dm where 方案名称='" & CmbJsfa.Text & "'"
Cmdtmp.Execute
Rs02.Open "select * from Gz_产量核对Dm where 方案名称= '" & CmbJsfa.Text & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockReadOnly, adCmdText
Rs03.Open "select * from Gz_工资结算Dm", Cw_DataEnvi.DataConnect, adOpenStatic, adLockPessimistic, adCmdText
If Rs02.EOF And Rs02.BOF Then
MsgBox "请到“产量核对-产量核对2”中重新计算并保存！", vbInformation, "无法进行工资结算"
Exit Sub
End If
'更新欠交数等信息 可以更新为存储过程1
'========================================
Do While Not Rs02.EOF
If Rs02!工序名称 = "站机" Then
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!领一级)
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) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级)) - CLng(IIf(IsNull(Rs02!擦报废) = True, "0", Rs02!擦报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数)) - (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!站二级)) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级)) - CLng(IIf(IsNull(Rs02!站报废) = True, "0", Rs02!站报废)) - CLng(IIf(IsNull(Rs02!擦报废) = True, "0", Rs02!擦报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数))) / 2
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!站二级)) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级)) - CLng(IIf(IsNull(Rs02!站报废) = True, "0", Rs02!站报废)) - CLng(IIf(IsNull(Rs02!擦报废) = True, "0", Rs02!擦报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数))) / 2
Rs03.Update
Else
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!领一级)
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!站二级)) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级) * 0.9) - CLng(IIf(IsNull(Rs02!站报废) = True, "0", Rs02!站报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数)) - (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!站二级)) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级)) - CLng(IIf(IsNull(Rs02!站报废) = True, "0", Rs02!站报废)) - CLng(IIf(IsNull(Rs02!擦报废) = True, "0", Rs02!擦报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数))) / 2
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!站二级)) - CLng(IIf(IsNull(Rs02!擦二级) = True, "0", Rs02!擦二级)) - CLng(IIf(IsNull(Rs02!站报废) = True, "0", Rs02!站报废)) - CLng(IIf(IsNull(Rs02!擦报废) = True, "0", Rs02!擦报废)) - CLng(IIf(IsNull(Rs02!留用数) = True, "0", Rs02!留用数))) / 2
Rs03.Update
End If
Rs02.MoveNext
Loop
Rs02.Close
Rs03.Close
'========================================
'工资结算
'========================================
Do While Not Rs01.EOF
Rs03.Open "select * from Gz_工资结算Dm where 工序名称='" & Rs01!工序名称 & "' and 图号='" & Rs01!图号 & "'", Cw_DataEnvi.DataConnect, adOpenStatic, adLockOptimistic, adCmdText
Do While Not Rs03.EOF
If Rs01!工序名称 = "擦片" Then
Count = "0.0"
Rs03!一级品价 = Rs01!一级品价
Rs03!二级品价 = Rs01!二级品价
Rs03!料质价 = Rs01!料质价
Rs03!金额1 = Rs01!一级品价 * Rs03!一级品 + Rs03!留用数 * Rs01!一级品价 + Rs01!二级品价 * Rs03!站二级 + Rs01!二级品价 * Rs03!站报废
Count = Rs01!一级品价 * Rs03!一级品 + Rs03!留用数 * 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!站二级 - 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!擦二级 * 0.9
Rs03!差额1 = CLng(Rs01!一废品率 * Rs03!领一级 / 100) + CLng(Rs01!二废品率 * Rs03!领二级 / 100) - (Rs03!领一级 + Rs03!领二级 - Rs03!一级品数 - Rs03!留用数 - Rs03!站二级 - Rs03!站报废 - Rs03!擦二级 * 0.9 - Rs03!欠交数)
If Rs01!原材料价 = 3 Or Rs01!原材料价 = 5 Then
Rs03!奖赔 = Rs01!原材料价 * (CLng(Rs01!一废品率 * Rs03!领一级 / 100) + CLng(Rs01!二废品率 * Rs03!领二级 / 100) - (Rs03!领一级 + Rs03!领二级 - Rs03!一级品数 - Rs03!留用数 - Rs03!站二级 - Rs03!站报废 - Rs03!擦二级 * 0.9 - Rs03!欠交数))
Count = Count + Rs01!原材料价 * (CLng(Rs01!一废品率 * Rs03!领一级 / 100) + CLng(Rs01!二废品率 * Rs03!领二级 / 100) - (Rs03!领一级 + Rs03!领二级 - Rs03!一级品数 - Rs03!留用数 - Rs03!站二级 - Rs03!站报废 - Rs03!擦二级 * 0.9 - Rs03!欠交数))
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -