📄 -
字号:
If Trim(.LrText(1).Text) <> "" Then
Str_Wl = Str_Wl & " and invsortcode LIKE '" & Trim(.LrText(1).Tag) & "%'"
Sqlstr = Sqlstr & " and invsortcode LIKE '" & Trim(.LrText(1).Tag) & "%'"
Str_Sort = " and invsortcode LIKE '" & Trim(.LrText(1).Tag) & "%'"
Else
Str_Sort = ""
End If
Case 4 '材料
If Trim(.LrText(2).Text) <> "" Then
Str_Mate = " and mnumber='" & Trim(.LrText(2).Text) & "'"
Sqlstr = Sqlstr & " and mnumber='" & Trim(.LrText(2).Text) & "'"
Str_Wl = Str_Wl & " and mnumber='" & Trim(.LrText(2).Text) & "'"
Else
Str_Mate = ""
End If
Case 5 '批次
If Trim(.LrText(3).Text) <> "" Then
Str_Batch = " and batchnum='" & Trim(.LrText(3).Text) & "'"
Sqlstr = Sqlstr & " and batchnum='" & Trim(.LrText(3).Text) & "'"
Str_Wl = Str_Wl & " and batchnum='" & Trim(.LrText(3).Text) & "'"
Else
Str_Batch = ""
End If
End Select
Next jsqte
End With
Dim Str_Query As String
Label1 = "日期范围: " & Trim(KF_FrmMateBatchQuery.Combo_Kjqj1.Text) & "----" & Trim(KF_FrmMateBatchQuery.Combo_Kjqj2.Text)
Str_Wl = Sqlstr + Str_Wl + " order by mnumber"
Set Rec_Mate = Cw_DataEnvi.DataConnect.Execute(Str_Wl)
CxbbGrid.Rows = CxbbGrid.FixedRows
jsqte = CxbbGrid.FixedRows
Dim D_QC, D_In, D_Out, D_End As Double
D_QC = 0
D_In = 0
D_Out = 0
D_End = 0
Dim temp_batch As String
Dim S_QC, S_In, S_Out, S_End As Single
Dim judge_flag As Boolean
S_QC = 0
S_In = 0
S_Out = 0
S_End = 0
Do While Not Rec_Mate.EOF
judge_flag = True
temp_batch = Rec_Mate.Fields("mnumber")
Str_Query = ""
Str_Query = Str_QueryCondi & Str_WH & Str_Sort & Str_Mate & " and batchnum='" & Trim(Rec_Mate.Fields("batchnum")) & "' and WhCode='" & Trim(Rec_Mate.Fields("WhCode")) & "' and mnumber='" & Trim(Rec_Mate.Fields("mnumber")) & "' GROUP BY mnumber, WhCode, batchnum"
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Str_Query)
Dim str_query1 As String
str_query1 = "select SUM(startquan) AS Qc, mnumber, batchnum, WhCode From kf_V_ledger where 1=1" & Str_WH & Str_Sort & Str_Mate & " and mnumber='" & Trim(Rec_Mate.Fields("mnumber")) & "' and period =" & Val(Mid(Trim(KF_FrmMateBatchQuery.Combo_Kjqj1.Text), 6)) & " and batchnum<>'' GROUP BY mnumber, batchnum, WhCode"
If Rec_Query.EOF Then
Dim rec_start As New ADODB.Recordset
Set rec_start = Cw_DataEnvi.DataConnect.Execute(str_query1)
With rec_start
If rec_start.EOF Then
judge_flag = False
Else
Do While Not .EOF
CxbbGrid.AddItem ""
CxbbGrid.TextMatrix(jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("batchnum")) '批号
CxbbGrid.TextMatrix(jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("mnumber")) '物料编码
Set Rec_WH = Cw_DataEnvi.DataConnect.Execute("SELECT whname from gy_warehouse where WhCode='" & Trim(.Fields("WhCode")) & "'")
CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Rec_WH.Fields("whname")) '仓库
Set Rec_Unit = Cw_DataEnvi.DataConnect.Execute("select mname,model ,PrimaryUnitName from gy_material where mnumber='" & Trim(.Fields("mnumber")) & "'")
CxbbGrid.TextMatrix(jsqte, Sydz("002", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("mname")) '物料名称
CxbbGrid.TextMatrix(jsqte, Sydz("003", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("model"))
CxbbGrid.TextMatrix(jsqte, Sydz("004", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("PrimaryUnitName")) '计量单位
Set Rec_QC = Cw_DataEnvi.DataConnect.Execute("SELECT SUM(startquan) AS Qc, mnumber, batchnum, WhCode From kf_V_ledger where mnumber='" & Trim(.Fields("mnumber")) & "' and batchnum='" & Trim(.Fields("batchnum")) & "' and WhCode='" & Trim(.Fields("WhCode")) & "'and kjyear=" & Xtyear & " and period =" & Val(Mid(Trim(KF_FrmMateBatchQuery.Combo_Kjqj1.Text), 6)) & " GROUP BY mnumber, batchnum, WhCode")
If Not Rec_QC.EOF Then
If Val(Rec_QC.Fields("qc")) Then
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = Val(Rec_QC.Fields("qc")) '期初数量
Else
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""
End If
Else
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""
End If
S_QC = S_QC + Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)))
If Val(Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + Val(CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)))) Then
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + Val(CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)))
Else
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = ""
End If
S_End = S_End + Val(CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)))
CxbbGrid.RowHeight(jsqte) = Sjhgd
jsqte = jsqte + 1
.MoveNext
Loop
End If
End With
Else
With Rec_Query
Do While Not .EOF
CxbbGrid.AddItem ""
CxbbGrid.TextMatrix(jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("batchnum")) '批号
CxbbGrid.TextMatrix(jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("mnumber")) '物料编码
Set Rec_WH = Cw_DataEnvi.DataConnect.Execute("SELECT whname from gy_warehouse where WhCode='" & Trim(.Fields("WhCode")) & "'")
CxbbGrid.TextMatrix(jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Rec_WH.Fields("whname")) '仓库
If Val(.Fields("expr1")) <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls)) = Val(.Fields("expr1"))
End If
S_In = S_In + Val(.Fields("expr1"))
If Val(.Fields("expr2")) <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)) = Val(.Fields("expr2"))
End If
S_Out = S_Out + Val(.Fields("expr2"))
Set Rec_Unit = Cw_DataEnvi.DataConnect.Execute("select mname,model ,PrimaryUnitName from gy_material where mnumber='" & Trim(.Fields("mnumber")) & "'")
CxbbGrid.TextMatrix(jsqte, Sydz("002", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("mname")) '物料编码
CxbbGrid.TextMatrix(jsqte, Sydz("003", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("model")) '规格型号
CxbbGrid.TextMatrix(jsqte, Sydz("004", GridStr(), Szzls)) = Trim(Rec_Unit.Fields("PrimaryUnitName")) '计量单位
Set Rec_QC = Cw_DataEnvi.DataConnect.Execute("SELECT SUM(startquan) AS Qc, mnumber, batchnum, WhCode From kf_V_ledger where mnumber='" & Trim(.Fields("mnumber")) & "' and batchnum='" & Trim(.Fields("batchnum")) & "' and WhCode='" & Trim(.Fields("WhCode")) & "'and kjyear=" & Xtyear & " and period =" & Val(Mid(Trim(KF_FrmMateBatchQuery.Combo_Kjqj1.Text), 6)) & " GROUP BY mnumber, batchnum, WhCode")
If Not Rec_QC.EOF Then
If Val(Rec_QC.Fields("qc")) Then
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = Val(Rec_QC.Fields("qc")) '期初数量
Else
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""
End If
Else
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = ""
End If
S_QC = S_QC + Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)))
If Val(Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + Val(CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)))) Then
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = Val(CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls))) + Val(CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls))) - Val(CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)))
Else
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = ""
End If
S_End = S_End + Val(CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)))
CxbbGrid.RowHeight(jsqte) = Sjhgd
jsqte = jsqte + 1
.MoveNext
Loop
End With
End If
Rec_Mate.MoveNext
If Rec_Mate.EOF Then
If judge_flag Then
CxbbGrid.AddItem ""
CxbbGrid.TextMatrix(jsqte, Sydz("001", GridStr(), Szzls)) = "小计"
If S_QC <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = S_QC
End If
If S_In <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls)) = S_In
End If
If S_Out <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)) = S_Out
End If
If S_End <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = S_End
End If
D_In = D_In + S_In
D_Out = D_Out + S_Out
D_QC = D_QC + S_QC
D_End = D_End + S_End
CxbbGrid.RowHeight(jsqte) = Sjhgd
jsqte = jsqte + 1
End If
ElseIf temp_batch <> Rec_Mate.Fields("mnumber") Then
If judge_flag Then
CxbbGrid.AddItem ""
CxbbGrid.TextMatrix(jsqte, Sydz("001", GridStr(), Szzls)) = "小计"
If S_QC <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = S_QC
End If
If S_In <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls)) = S_In
End If
If S_Out <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)) = S_Out
End If
If S_End <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = S_End
End If
D_In = D_In + S_In
D_Out = D_Out + S_Out
D_QC = D_QC + S_QC
D_End = D_End + S_End
S_QC = 0
S_In = 0
S_Out = 0
S_End = 0
CxbbGrid.RowHeight(jsqte) = Sjhgd
jsqte = jsqte + 1
End If
End If
Loop
CxbbGrid.MergeCells = flexMergeFree
For jsq = Qslz To CxbbGrid.Cols - 1
CxbbGrid.MergeCol(jsq) = False
Next jsq
CxbbGrid.MergeCol(Sydz("001", GridStr(), Szzls)) = True
CxbbGrid.AddItem ""
CxbbGrid.TextMatrix(jsqte, Qslz) = "合计"
If D_QC <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("007", GridStr(), Szzls)) = D_QC
End If
If D_In <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("008", GridStr(), Szzls)) = D_In
End If
If D_Out <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("009", GridStr(), Szzls)) = D_Out
End If
If D_End <> 0 Then
CxbbGrid.TextMatrix(jsqte, Sydz("010", GridStr(), Szzls)) = D_End
End If
CxbbGrid.RowHeight(CxbbGrid.Rows - 1) = Sjhgd
']以上为用户自定义部分
End Sub
Private Sub CxbbGrid_BeforeMoveColumn(ByVal Col As Long, Position As Long)
'如果结束列小于用户定义网格开始列，则结束列=用户定义网格开始列
'因为开始列以前的列都是隐藏列，由于要把当前开始移动列移动到隐藏列上
'所以控件自动把隐藏列变为显示列，这样在刷新数据时，会把隐藏列上的数据
'显示出来，并且，由于开始列以前的隐藏列在XT_Grid中，不对应逻辑值，所以在保存
'网格格式时会出错
If Col > Position Then
If Position < GridInf(1) Then Position = GridInf(1)
Else
If Col < GridInf(1) Then Col = GridInf(1)
End If
Call FnBln_RefreshArray(Col, Position, GridStr(), GridInf())
End Sub
Private Sub bbyl(bbylte As Boolean) '报表打印预览
Dim Bbzbt$, Bbxbt() As String, bbxbtzzxs() As Integer, Bbxbtgs As Integer
Dim Bbbwh() As String, Bbbwhzzxs() As Integer, Bbbwhgs As Integer
Bbxbtgs = 2 '报 表 小 标 题 行 数
Bbbwhgs = 0 '报 表 表 尾 行 数
ReDim Bbxbt(1 To Bbxbtgs)
ReDim bbxbtzzxs(1 To Bbxbtgs)
If Bbbwhgs <> 0 Then
ReDim Bbbwh(1 To Bbbwhgs)
ReDim Bbbwhzzxs(1 To Bbbwhgs)
End If
Bbzbt = ReportTitle
Bbxbt(2) = Space(5) + Fun_FormatOutPut(Trim(Label1), 30)
Bbxbt(1) = " "
bbxbtzzxs(1) = 0 '报表行组织形式(0-居左 1-居中 2-居右)
Call Scyxsjb(CxbbGrid) '生成报表数据
Call Scdybb(Dyymctbl, Bbzbt, Bbxbt(), bbxbtzzxs(), Bbxbtgs, Bbbwh(), Bbbwhzzxs(), Bbbwhgs, bbylte)
If Not bbylte Then
Unload DY_Tybbyldy
End If
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -