📄
字号:
Case 2 '供应商编码范围(终止供应商编码)
If Trim(.LrText(1).Text) <> "" Then
Str_QueryCondi = Str_QueryCondi & " and b.SupplierCode<='" & Trim(.LrText(1).Text) & "'"
End If
End Select
Next Jsqte
End With
'计算所有用户选中辅助项目数据(供应商)
If Bln_IncluNotBook Then
Sqlstr = " Select b.*,Gy_Supplier.SupplierName From (Select a.SupplierCode,QcyeHJ=Sum(a.qcyehj),Qcslhj=Sum(Qcslhj),QcwbHj=Sum(QcwbHj),Mjjehj=Sum(Mjjehj),Mdjehj=Sum(Mdjehj),MjslHJ=Sum(Mjslhj),MdslHJ=Sum(Mdslhj),Mjwbhj=Sum(Mjwbhj),Mdwbhj=Sum(Mdwbhj),ByjfljjeHj=Sum(ByjfljjeHj),Bydfljjehj=Sum(BydfljjeHj),Byjfljslhj=Sum(ByjfljslHj),Bydfljslhj=Sum(Bydfljslhj),Byjfljwbhj=Sum(Byjfljwbhj),Bydfljwbhj=Sum(Bydfljwbhj) From " & _
" ((SELECT SupplierCode,QcyeHj=Sum(Qcye),Qcslhj=Sum(Qcsl),QcwbHj=Sum(Qcwb),Mjjehj=0,Mdjehj=0,MjslHJ=0,MdslHJ=0,Mjwbhj=0,Mdwbhj=0,ByjfljjeHj=0,Bydfljjehj=0,Byjfljslhj=0,Bydfljslhj=0,Byjfljwbhj=0,Bydfljwbhj=0 From Cwzz_AccSumAssi Where Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period=" & Int_BPeriod & " Group By Cwzz_AccSumAssi.SupplierCode) union all " & _
" (SELECT SupplierCode,Qcyehj=Sum(Jfje-Dfje),Qcslhj=Sum(Jfsl-Dfsl),QcwbHj=Sum(Wbjfje-Wbdfje),Mjjehj=0,Mdjehj=0,MjslHJ=0,MdslHJ=0,Mjwbhj=0,Mdwbhj=0,ByjfljjeHj=0,Bydfljjehj=0,Byjfljslhj=0,Bydfljslhj=0,Byjfljwbhj=0,Bydfljwbhj=0 From Cwzz_V_AccVouch Where BookFlag=0 And Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period<" & Int_BPeriod & " Group By Cwzz_V_AccVouch.SupplierCode) union all " & _
" (SELECT SupplierCode,QcyeHJ=0,Qcslhj=0,QcwbHj=0,Mjjehj=Sum(Jfje),Mdjehj=Sum(Dfje),Mjslhj=Sum(Jfsl),Mdslhj=Sum(Dfsl),Mjwbhj=Sum(Wbjfje),Mdwbhj=Sum(Wbdfje),ByjfljjeHj=0,Bydfljjehj=0,Byjfljslhj=0,Bydfljslhj=0,Byjfljwbhj=0,Bydfljwbhj=0 From Cwzz_V_AccVouch Where Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period>=" & Int_BPeriod & " And Period <=" & Int_EPeriod & " Group By SupplierCode) Union All " & _
" (SELECT SupplierCode,QcyeHJ=0,Qcslhj=0,QcwbHj=0,Mjjehj=0,Mdjehj=0,Mjslhj=0,Mdslhj=0,Mjwbhj=0,Mdwbhj=0,ByjfljjeHj=Sum(Jfje),Bydfljjehj=Sum(Dfje),Byjfljslhj=Sum(Jfsl),Bydfljslhj=Sum(Dfsl),Byjfljwbhj=Sum(Wbjfje),Bydfljwbhj=Sum(Wbdfje) From Cwzz_V_AccVouch Where Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period<= " & Int_EPeriod & " Group By SupplierCode)) a Group By A.SupplierCode) b JOIN" & _
" Gy_Supplier ON b.SupplierCode = Gy_Supplier.SupplierCode" & Str_QueryCondi
Else
Sqlstr = " Select b.*,Gy_Supplier.SupplierName From (Select a.SupplierCode,QcyeHJ=Sum(a.qcyehj),Qcslhj=Sum(Qcslhj),QcwbHj=Sum(QcwbHj),Mjjehj=Sum(Mjjehj),Mdjehj=Sum(Mdjehj),MjslHJ=Sum(Mjslhj),MdslHJ=Sum(Mdslhj),Mjwbhj=Sum(Mjwbhj),Mdwbhj=Sum(Mdwbhj),ByjfljjeHj=Sum(ByjfljjeHj),Bydfljjehj=Sum(BydfljjeHj),Byjfljslhj=Sum(ByjfljslHj),Bydfljslhj=Sum(Bydfljslhj),Byjfljwbhj=Sum(Byjfljwbhj),Bydfljwbhj=Sum(Bydfljwbhj) From " & _
" ((SELECT SupplierCode,QcyeHj=Sum(Qcye),Qcslhj=Sum(Qcsl),QcwbHj=Sum(Qcwb),Mjjehj=0,Mdjehj=0,MjslHJ=0,MdslHJ=0,Mjwbhj=0,Mdwbhj=0,ByjfljjeHj=0,Bydfljjehj=0,Byjfljslhj=0,Bydfljslhj=0,Byjfljwbhj=0,Bydfljwbhj=0 From Cwzz_AccSumAssi Where Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period=" & Int_BPeriod & " Group By Cwzz_AccSumAssi.SupplierCode) union all " & _
" (SELECT SupplierCode,QcyeHJ=0,Qcslhj=0,QcwbHj=0,Mjjehj=Sum(Jfje),Mdjehj=Sum(Dfje),Mjslhj=Sum(Jfsl),Mdslhj=Sum(Dfsl),Mjwbhj=Sum(Wbjfje),Mdwbhj=Sum(Wbdfje),ByjfljjeHj=0,Bydfljjehj=0,Byjfljslhj=0,Bydfljslhj=0,Byjfljwbhj=0,Bydfljwbhj=0 From Cwzz_V_AccVouch Where BookFlag=1 And Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period>=" & Int_BPeriod & " And Period <=" & Int_EPeriod & " Group By SupplierCode) Union All " & _
" (SELECT SupplierCode,QcyeHJ=0,Qcslhj=0,QcwbHj=0,Mjjehj=0,Mdjehj=0,Mjslhj=0,Mdslhj=0,Mjwbhj=0,Mdwbhj=0,ByjfljjeHj=Sum(Jfje),Bydfljjehj=Sum(Dfje),Byjfljslhj=Sum(Jfsl),Bydfljslhj=Sum(Dfsl),Byjfljwbhj=Sum(Wbjfje),Bydfljwbhj=Sum(Wbdfje) From Cwzz_V_AccVouch Where BookFlag=1 And Ccode like '" & Str_Ccode & "%' And Year=" & Int_Year & " And Period<= " & Int_EPeriod & " Group By SupplierCode)) a Group By A.SupplierCode) b JOIN" & _
" Gy_Supplier ON b.SupplierCode = Gy_Supplier.SupplierCode" & Str_QueryCondi
End If
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
With Rec_Query
CxbbGrid.Rows = CxbbGrid.FixedRows
Jsqte = CxbbGrid.FixedRows - 1
Do While Not .EOF
Dbl_Qcyete = Rec_Query.Fields("QcyeHj")
Dbl_Qcslte = Rec_Query.Fields("QcslHj")
Dbl_Qcwbte = Rec_Query.Fields("QcwbHj")
Dbl_Jejfhj = Rec_Query.Fields("Mjjehj")
Dbl_Jedfhj = Rec_Query.Fields("Mdjehj")
Dbl_Sljfhj = Rec_Query.Fields("Mjslhj")
Dbl_Sldfhj = Rec_Query.Fields("Mdslhj")
Dbl_Wbjfhj = Rec_Query.Fields("Mjwbhj")
Dbl_Wbdfhj = Rec_Query.Fields("Mdwbhj")
Dbl_Jejflj = Rec_Query.Fields("Byjfljjehj")
Dbl_Jedflj = Rec_Query.Fields("Bydfljjehj")
Dbl_Sljflj = Rec_Query.Fields("Byjfljslhj")
Dbl_Sldflj = Rec_Query.Fields("Bydfljslhj")
Dbl_Wbjflj = Rec_Query.Fields("Byjfljwbhj")
Dbl_Wbdflj = Rec_Query.Fields("Bydfljwbhj")
Dbl_Qmyete = Dbl_Qcyete + Dbl_Jejfhj - Dbl_Jedfhj
Dbl_Qmslte = Dbl_Qcslte + Dbl_Sljfhj - Dbl_Sldfhj
Dbl_Qmwbte = Dbl_Qcwbte + Dbl_Wbjfhj - Dbl_Wbdfhj
'判断是否输出数据
Bln_Fhxstj = False
If Bln_NotShowEmpty Then
If Dbl_Qcyete <> 0 Or Dbl_Qcslte <> 0 Or Dbl_Qcwbte <> 0 Then
Bln_Fhxstj = True
End If
If Dbl_Jejfhj <> 0 Or Dbl_Jedfhj <> 0 Or Dbl_Sljfhj <> 0 Or Dbl_Sldfhj <> 0 Or Dbl_Wbjfhj <> 0 Or Dbl_Wbdfhj <> 0 Then
Bln_Fhxstj = True
End If
If Dbl_Jejflj <> 0 Or Dbl_Jedflj <> 0 Or Dbl_Sljflj <> 0 Or Dbl_Sldflj <> 0 Or Dbl_Wbjflj <> 0 Or Dbl_Wbdflj <> 0 Then
Bln_Fhxstj = True
End If
Else
Bln_Fhxstj = True
End If
If Bln_Fhxstj Then
Bln_Fhxstj = False
'判断期末余额是否符合查询条件
Select Case Int_Tjfx
Case 0
If Abs(Dbl_Qmyete) >= Dbl_BeginYe Then
If Dbl_EndYe <> 0 Then
If Abs(Dbl_Qmyete) <= Dbl_EndYe Then
Bln_Fhxstj = True
End If
Else
Bln_Fhxstj = True
End If
End If
Case 1
If Dbl_Qmyete >= 0 And Abs(Dbl_Qmyete) >= Dbl_BeginYe Then
If Dbl_EndYe <> 0 Then
If Abs(Dbl_Qmyete) <= Dbl_EndYe Then
Bln_Fhxstj = True
End If
Else
Bln_Fhxstj = True
End If
End If
Case 2
If Dbl_Qmyete <= 0 And Abs(Dbl_Qmyete) >= Dbl_BeginYe Then
If Dbl_EndYe <> 0 Then
If Abs(Dbl_Qmyete) <= Dbl_EndYe Then
Bln_Fhxstj = True
End If
Else
Bln_Fhxstj = True
End If
End If
End Select
End If
'数据输出
If Bln_Fhxstj Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'数据总计
Dbl_Sjzj(1) = Dbl_Sjzj(1) + Dbl_Qcyete
Dbl_Sjzj(2) = Dbl_Sjzj(2) + Dbl_Qcslte
Dbl_Sjzj(3) = Dbl_Sjzj(3) + Dbl_Qcwbte
Dbl_Sjzj(4) = Dbl_Sjzj(4) + Dbl_Jejfhj
Dbl_Sjzj(5) = Dbl_Sjzj(5) + Dbl_Jedfhj
Dbl_Sjzj(6) = Dbl_Sjzj(6) + Dbl_Sljfhj
Dbl_Sjzj(7) = Dbl_Sjzj(7) + Dbl_Sldfhj
Dbl_Sjzj(8) = Dbl_Sjzj(8) + Dbl_Wbjfhj
Dbl_Sjzj(9) = Dbl_Sjzj(9) + Dbl_Wbdfhj
Dbl_Sjzj(10) = Dbl_Sjzj(10) + Dbl_Jejflj
Dbl_Sjzj(11) = Dbl_Sjzj(11) + Dbl_Jedflj
Dbl_Sjzj(12) = Dbl_Sjzj(12) + Dbl_Sljflj
Dbl_Sjzj(13) = Dbl_Sjzj(13) + Dbl_Sldflj
Dbl_Sjzj(14) = Dbl_Sjzj(14) + Dbl_Wbjflj
Dbl_Sjzj(15) = Dbl_Sjzj(15) + Dbl_Wbdflj
Dbl_Sjzj(16) = Dbl_Sjzj(16) + Dbl_Qmyete
Dbl_Sjzj(17) = Dbl_Sjzj(17) + Dbl_Qmslte
Dbl_Sjzj(18) = Dbl_Sjzj(18) + Dbl_Qmwbte
CxbbGrid.RowHeight(Jsqte) = Sjhgd
CxbbGrid.TextMatrix(Jsqte, 0) = "0" + Trim(.Fields("SupplierCode")) '供应商编码
CxbbGrid.TextMatrix(Jsqte, 1) = "0" + Trim(.Fields("SupplierName") & "") '供应商名称
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("SupplierCode")) '供应商编码
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("SupplierName") & "") '供应商名称
'将期初数据写入网格
If Dbl_Qcyete = 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "平" '期初方向
If Bln_EndFlag And Dbl_Qcslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Dbl_Qcslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qcwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Dbl_Qcwbte
End If
Else
If Dbl_Qcyete > 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "借"
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Dbl_Qcyete
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Dbl_Qcyete
If Bln_EndFlag And Dbl_Qcslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Dbl_Qcslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qcwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Dbl_Qcwbte
End If
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "贷"
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = -Dbl_Qcyete
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = -Dbl_Qcyete
If Bln_EndFlag And Dbl_Qcslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = -Dbl_Qcslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qcwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = -Dbl_Qcwbte
End If
End If
End If
'将期间合计数据写入网格
If Dbl_Jejfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Dbl_Jejfhj '本期发生.借方
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = Dbl_Jejfhj '本期借方发生.金额
End If
If Dbl_Jedfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Dbl_Jedfhj '本期发生.贷方
CxbbGrid.TextMatrix(Jsqte, Sydz("016", GridStr(), Szzls)) = Dbl_Jedfhj '本期贷方发生.金额
End If
'仅有末级科目才显示数量，外币
If Bln_EndFlag Then
If Dbl_Sljfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = Dbl_Sljfhj '本期借方发生.数量
End If
If Dbl_Sldfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("014", GridStr(), Szzls)) = Dbl_Sldfhj '本期贷方发生.数量
End If
End If
If Bln_EndFlag And Bln_ForeignFlag Then
If Dbl_Wbjfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = Dbl_Wbjfhj '本期借方发生.外币
End If
If Dbl_Wbdfhj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("015", GridStr(), Szzls)) = Dbl_Wbdfhj '本期贷方发生.外币
End If
End If
'将截止累计数据写入网格
If Dbl_Jejflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("017", GridStr(), Szzls)) = Dbl_Jejflj '累计发生.借方
CxbbGrid.TextMatrix(Jsqte, Sydz("021", GridStr(), Szzls)) = Dbl_Jejflj '累计借方发生.金额
End If
If Dbl_Jedflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("018", GridStr(), Szzls)) = Dbl_Jedflj '累计发生.贷方
CxbbGrid.TextMatrix(Jsqte, Sydz("024", GridStr(), Szzls)) = Dbl_Jedflj '累计贷方发生.金额
End If
'仅有末级科目才显示数量，外币
If Bln_EndFlag Then
If Dbl_Sljflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("019", GridStr(), Szzls)) = Dbl_Sljflj '累计借方发生.数量
End If
If Dbl_Sldflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("022", GridStr(), Szzls)) = Dbl_Sldflj '累计贷方发生.数量
End If
End If
If Bln_EndFlag And Bln_ForeignFlag Then
If Dbl_Wbjflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("020", GridStr(), Szzls)) = Dbl_Wbjflj '累计借方发生.外币
End If
If Dbl_Wbdflj <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("023", GridStr(), Szzls)) = Dbl_Wbdflj '累计贷方发生.外币
End If
End If
'4.将期末数据写入网格
If Dbl_Qmyete = 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("025", GridStr(), Szzls)) = "平" '期初方向
If Bln_EndFlag And Dbl_Qmslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("028", GridStr(), Szzls)) = Dbl_Qmslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qmwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("029", GridStr(), Szzls)) = Dbl_Qmwbte
End If
Else
If Dbl_Qmyete > 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("025", GridStr(), Szzls)) = "借"
CxbbGrid.TextMatrix(Jsqte, Sydz("026", GridStr(), Szzls)) = Dbl_Qmyete
CxbbGrid.TextMatrix(Jsqte, Sydz("030", GridStr(), Szzls)) = Dbl_Qmyete
If Bln_EndFlag And Dbl_Qmslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("028", GridStr(), Szzls)) = Dbl_Qmslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qmwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("029", GridStr(), Szzls)) = Dbl_Qmwbte
End If
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("025", GridStr(), Szzls)) = "贷"
CxbbGrid.TextMatrix(Jsqte, Sydz("027", GridStr(), Szzls)) = -Dbl_Qmyete
CxbbGrid.TextMatrix(Jsqte, Sydz("030", GridStr(), Szzls)) = -Dbl_Qmyete
If Bln_EndFlag And Dbl_Qmslte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("028", GridStr(), Szzls)) = -Dbl_Qmslte
End If
If Bln_EndFlag And Bln_ForeignFlag And Dbl_Qmwbte <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("029", GridStr(), Szzls)) = -Dbl_Qmwbte
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -