📄
字号:
'期初单据
SqlStr = "SELECT * FROM Chhs_V_StartBill a " & _
Trim(Str_QueryCondi) & " and a.checker<>'' Order By a.Whcode,a.StartInputMainId,a.StartInputSubId"
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(SqlStr)
With Rec_Query
CxbbGrid.Rows = CxbbGrid.FixedRows
Jsqte = CxbbGrid.FixedRows
Do While Not .EOF
CxbbGrid.AddItem ""
'[>>自定义填充内容
CxbbGrid.TextMatrix(Jsqte, 0) = .Fields("StartInputMainId") 'ID(主要用来查询定位用)
CxbbGrid.TextMatrix(Jsqte, 1) = .Fields("BillCode") 'ID(主要用来查询定位用)
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("BillNum") & "") '单据号
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("WhName") & "") '仓库名称
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Format(.Fields("BillDate"), "yyyy-mm-dd") '入库日期
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(.Fields("SupplierName") & "") '供应商
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("Deptname") & "") '部门
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(.Fields("InoutClassName") & "") '收发类别
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(.Fields("PersonName") & "") '业务员
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(.Fields("Maker") & "") '制单人
CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(.Fields("Checker") & "") '审核人
CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(.Fields("ChalkitupMan") & "") '记帐人
CxbbGrid.TextMatrix(Jsqte, Sydz("019", GridStr(), Szzls)) = Trim(.Fields("Mnumber") & "") '存货编码
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(.Fields("MName") & "") '存货名称
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(.Fields("Model") & "") '规格型号
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(.Fields("PrimaryUnitName") & "") '主计量单位
CxbbGrid.TextMatrix(Jsqte, Sydz("020", GridStr(), Szzls)) = Trim(.Fields("kjyear") & "") '年度
CxbbGrid.TextMatrix(Jsqte, Sydz("021", GridStr(), Szzls)) = Trim(.Fields("period") & "") '月份
If .Fields("Quan") <> 0 Then '数量
CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = .Fields("Quan")
End If
If .Fields("PlanPrice") <> 0 Then '计划单价
CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = .Fields("PlanPrice")
End If
If .Fields("PlanMoney") <> 0 Then '计划金额
CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = .Fields("PlanMoney")
End If
If .Fields("Price") <> 0 Then '实际单价
CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = .Fields("Price")
End If
If .Fields("Emoney") <> 0 Then '实际金额
CxbbGrid.TextMatrix(Jsqte, Sydz("018", GridStr(), Szzls)) = .Fields("EMoney")
Else
CxbbGrid.Cell(flexcpBackColor, CxbbGrid.Row, Qslz, CxbbGrid.Row, CxbbGrid.Cols - 1) = &HFFFFC0
End If
'<<]
'设置数据行高度(Fixed)
CxbbGrid.RowHeight(Jsqte) = Sjhgd
'动态集指针加1,同时将计数器加1(Fixed)
.MoveNext
Jsqte = Jsqte + 1
Loop
End With
'暂估单据
If Xtclzg Then
'筛选暂估条件
mStr = ""
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute("select * from chhs_evalu order by whcode")
If Not Rec_Query.EOF Then
For Jsqte = 1 To Rec_Query.RecordCount
Str1 = "1=1"
If Trim(Rec_Query.Fields("whcode") & "") <> "" Then
Str1 = Str1 + " and a.whcode='" & Trim(Rec_Query.Fields("whcode")) & "'"
End If
If Trim(Rec_Query.Fields("msort") & "") <> "" Then
Str1 = Str1 + " and a.invsortcode like '" & Trim(Rec_Query.Fields("msort")) & "%'"
End If
If Trim(Rec_Query.Fields("mnumber") & "") <> "" Then
Str1 = Str1 + " and a.mnumber='" & Trim(Rec_Query.Fields("mnumber")) & "'"
End If
If mStr = "" Then
mStr = "(" + Str1 + ")"
Else
mStr = mStr + " or (" + Str1 + ")"
End If
Rec_Query.MoveNext
Next
mStr = "(" + mStr + ")"
'筛选期初暂估单
SqlStr = "SELECT * FROM Chhs_V_StartEval a " & Trim(Str_QueryCondi) & " and " & Trim(mStr) & _
" and a.billcode='1211' and Emoney<>0 Order By a.BillDate,a.InoutMainId,a.InoutSubId"
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(SqlStr)
With Rec_Query
Do While Not .EOF
CxbbGrid.AddItem ""
Jsqte = CxbbGrid.Rows - 1
'[>>自定义填充内容
CxbbGrid.TextMatrix(Jsqte, 0) = .Fields("InoutMainId") 'ID(主要用来查询定位用)
CxbbGrid.TextMatrix(Jsqte, 1) = .Fields("BillCode") 'ID(主要用来查询定位用)
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("BillNum") & "") '单据号
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("WhName") & "") '仓库名称
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Format(.Fields("BillDate"), "yyyy-mm-dd") '入库日期
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(.Fields("SupplierName") & "") '供应商
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(.Fields("Deptname") & "") '部门
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(.Fields("InoutClassName") & "") '收发类别
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(.Fields("PersonName") & "") '业务员
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(.Fields("Maker") & "") '制单人
CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(.Fields("chhsChecker") & "") '审核人
CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(.Fields("ChalkitupMan") & "") '记帐人
CxbbGrid.TextMatrix(Jsqte, Sydz("019", GridStr(), Szzls)) = Trim(.Fields("Mnumber") & "") '存货编码
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(.Fields("MName") & "") '存货名称
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(.Fields("Model") & "") '规格型号
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(.Fields("PrimaryUnitName") & "") '主计量单位
CxbbGrid.TextMatrix(Jsqte, Sydz("020", GridStr(), Szzls)) = Trim(.Fields("kjyear") & "") '年度
CxbbGrid.TextMatrix(Jsqte, Sydz("021", GridStr(), Szzls)) = Trim(.Fields("period") & "") '月份
If .Fields("FactReceiptQuan") <> 0 Then '数量
CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = .Fields("FactReceiptQuan")
End If
If .Fields("PlanPrice") <> 0 Then '计划单价
CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = .Fields("PlanPrice")
End If
If .Fields("PlanMoney") <> 0 Then '计划金额
CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = .Fields("PlanMoney")
End If
If .Fields("Price") <> 0 Then '实际单价
CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = .Fields("Price")
End If
If .Fields("Emoney") <> 0 Then '实际金额
CxbbGrid.TextMatrix(Jsqte, Sydz("018", GridStr(), Szzls)) = .Fields("EMoney")
Else
CxbbGrid.Cell(flexcpBackColor, CxbbGrid.Row, Qslz, CxbbGrid.Row, CxbbGrid.Cols - 1) = &HFFFFC0
End If
'<<]
'设置数据行高度(Fixed)
CxbbGrid.RowHeight(Jsqte) = Sjhgd
'动态集指针加1,同时将计数器加1(Fixed)
.MoveNext
Jsqte = Jsqte + 1
Loop
End With
End If
End If
'输入合计
With CxbbGrid
If Start_BillChalkitupCond.Chk_Sum.Value <> 0 Then
.SubtotalPosition = flexSTBelow
For Jsqte = Qslz To .Cols - 1
If GridBoolean(Jsqte, 4) Then
.Subtotal flexSTSum, 0, Jsqte, , &HF7F3EC, , , , , True
End If
Next
For Jsqte = .FixedRows To .Rows - 1
If .IsSubtotal(Jsqte) Then
For Coljsq = Qslz To .Cols - 1
If Val(.TextMatrix(Jsqte, Coljsq)) = 0 And GridBoolean(Coljsq, 4) Then
.TextMatrix(Jsqte, Coljsq) = ""
End If
Next
.TextMatrix(Jsqte, 0) = .TextMatrix(Jsqte - 1, 0)
.TextMatrix(Jsqte, 1) = .TextMatrix(Jsqte - 1, 1)
.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = " 合计 "
.RowHeight(Jsqte) = Sjhgd
End If
Next
End If
End With
']以上为用户自定义部分
Set Rec_Query = Nothing
End Sub
Private Sub Sub_QueryHz(Int_QueryType As Integer) '生成查询结果(Define)
'过程参数:Int_QueryType 0-"点确定按钮"查询 1-"刷新"查询
Dim Rec_Query As New ADODB.Recordset '查询结果动态集
Dim Coljsq As Long '网格列计数器
Dim Jsqte As Long '临时动态计数器
Dim Str As String
Dim FindRow As Integer
'以下为自定义部分[
CxbbGrid.ColHidden(Sydz("001", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("003", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("004", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("005", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("006", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("007", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("008", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("009", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("010", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("020", GridStr(), Szzls)) = True
CxbbGrid.ColHidden(Sydz("021", GridStr(), Szzls)) = True
SqlStr = "SELECT a.Whcode, WhName, MNumber, MName, Model, PrimaryUnitName, SUM(Quan) AS SumQuan, " & _
" SUM(PlanMoney) AS SumPlanMoney, SUM(EMoney) AS SumEMoney FROM Chhs_V_StartBill a " & _
" LEFT OUTER JOIN Gy_Whlimit ON a.WhCode = Gy_Whlimit.WhCode " & Trim(Str_QueryCondi) & _
" and Gy_Whlimit.Czybm = '" & Xtczybm & "' and kjyear=" & Xtyear & " and period=" & StartMon & _
" GROUP BY a.WhName, a.MNumber, " & _
" a.MName, a.Model, a.PrimaryUnitName, a.WhCode ORDER BY a.WhCode, a.MNumber "
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(SqlStr)
With Rec_Query
CxbbGrid.Rows = CxbbGrid.FixedRows
Jsqte = CxbbGrid.FixedRows
Do While Not .EOF
CxbbGrid.AddItem ""
'[>>自定义填充内容
CxbbGrid.TextMatrix(Jsqte, 1) = Trim(.Fields("WhCode") & "") + Trim(.Fields("Mnumber") & "") '仓库+存货
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("WhName") & "") '仓库
CxbbGrid.TextMatrix(Jsqte, Sydz("019", GridStr(), Szzls)) = Trim(.Fields("Mnumber") & "") '存货编码
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(.Fields("MName") & "") '存货名称
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(.Fields("Model") & "") '规格型号
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(.Fields("PrimaryUnitName") & "") '主计量单位
If .Fields("SumQuan") <> 0 Then '数量
CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = .Fields("SumQuan")
End If
If .Fields("SumQuan") <> 0 And .Fields("SumPlanMoney") <> 0 Then '计划单价
CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = .Fields("SumPlanMoney") / .Fields("SumQuan")
End If
If .Fields("SumPlanMoney") <> 0 Then '计划金额
CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = .Fields("SumPlanMoney")
End If
If .Fields("SumQuan") <> 0 Then '实际金额
CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = .Fields("SumEMoney") / .Fields("SumQuan")
End If
If .Fields("SumEmoney") <> 0 Then '实际金额
CxbbGrid.TextMatrix(Jsqte, Sydz("018", GridStr(), Szzls)) = .Fields("SumEMoney")
End If
'<<]
'设置数据行高度(Fixed)
CxbbGrid.RowHeight(Jsqte) = Sjhgd
'动态集指针加1,同时将计数器加1(Fixed)
.MoveNext
Jsqte = Jsqte + 1
Loop
End With
'期初暂估单
If Xtclzg And mStr <> "" Then
SqlStr = "SELECT a.Whcode,WhName, MNumber, MName, Model, PrimaryUnitName, SUM(FactReceiptQuan) AS SumQuan, " & _
" SUM(PlanMoney) AS SumPlanMoney, SUM(EMoney) AS SumEMoney FROM Chhs_V_StartEval a " & _
" LEFT OUTER JOIN Gy_Whlimit ON a.WhCode = Gy_Whlimit.WhCode " & Trim(Str_QueryCondi) & _
" and Gy_Whlimit.Czybm = '" & Xtczybm & "'and kjyear=" & Xtyear & " and period=" & StartMon & _
" and " & Trim(mStr) & "GROUP BY a.WhName, a.MNumber, " & _
" a.MName, a.Model, a.PrimaryUnitName, a.WhCode ORDER BY a.WhCode, a.MNumber "
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(SqlStr)
With Rec_Query
Jsqte = CxbbGrid.Rows - 1
Do While Not .EOF
'[>>自定义填充内容
FindRow = CxbbGrid.FindRow(Trim(.Fields("WhCode") & "") + Trim(.Fields("Mnumber") & ""), , 1)
If Not FindRow > 0 Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
CxbbGrid.TextMatrix(Jsqte, 1) = Trim(.Fields("WhCode") & "") + Trim(.Fields("Mnumber") & "") '仓库+存货
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("WhName") & "") '仓库
CxbbGrid.TextMatrix(Jsqte, Sydz("019", GridStr(), Szzls)) = Trim(.Fields("Mnumber") & "") '存货编码
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Trim(.Fields("MName") & "") '存货名称
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Trim(.Fields("Model") & "") '规格型号
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Trim(.Fields("PrimaryUnitName") & "") '主计量单位
If .Fields("SumQuan") <> 0 Then '数量
CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = .Fields("SumQuan")
End If
If .Fields("SumQuan") <> 0 Then '计划单价
CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = .Fields("SumPlanMoney") / .Fields("SumQuan")
End If
If .Fields("SumPlanMoney") <> 0 Then '计划金额
CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = .Fields("SumPlanMoney")
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -