📄
字号:
'生成查询结果
Call Sub_Query
CxbbGrid.Redraw = True
Xt_Wait.Hide
End Sub
Private Sub Sub_Query() '生成查询结果
Dim Rec_Query As New ADODB.Recordset '查询结果动态集
Dim RecTemp As New ADODB.Recordset '临时使用动态集
Dim Str_QueryCondi As String '用户录入查询条件
Dim Sqlstr As String '查询字符串
Dim Coljsq As Long '网格列计数器
Dim Jsqte As Long '临时动态计数器
Dim Int_LevTe As Integer '编码级数计数器
Dim Str_Parent As String '上级科目号
Dim Lng_ParCol As Long '上级科目所在列
Dim Str_CclassIndex As String '科目类别索引号
Dim Int_VouchNoMax As Integer '实际最大凭证号
Dim Int_VouchNoMin As Integer '实际最小凭证号
'以下为用户自定义部分[
With PZ_FrmPzhztj
'显示查询条件
Lab_TitleText(0).Caption = .Combo_Kjqj.Text '会计期间
If Trim(.Imgebo_VouchClass.Text) <> "" Then '凭证范围
Lab_TitleText(1).Caption = .Combo_Pzfw.Text & Mid(Trim(.Imgebo_VouchClass.Text), Len(Trim(GetComboKey(.Imgebo_VouchClass, 0))) + 1, Len(Trim(.Imgebo_VouchClass.Text)))
Else
Lab_TitleText(1).Caption = .Combo_Pzfw.Text
End If
Str_QueryCondi = " where 1=1 "
For Jsqte = 1 To 26
Select Case Jsqte
Case 1 '凭证范围
Select Case Trim(.Combo_Pzfw.Text)
Case "未记帐凭证"
Str_QueryCondi = Str_QueryCondi & " and BookFlag=0"
Case "记帐凭证"
Str_QueryCondi = Str_QueryCondi & " and BookFlag=1"
End Select
Case 2 '凭证类别
If GetComboKey(.Imgebo_VouchClass, 0) <> "" Then
Str_QueryCondi = Str_QueryCondi & " and VouchClassCode='" & Trim(GetComboKey(.Imgebo_VouchClass, 0)) & "'"
End If
Case 3 '会计期间
Str_QueryCondi = Str_QueryCondi & " and Year='" & Mid(Trim(.Combo_Kjqj.Text), 1, 4) & "' and Period='" & Mid(Trim(.Combo_Kjqj.Text), 6, 2) & "'"
Case 4 '凭证号范围(起始)
Str_QueryCondi = Str_QueryCondi & " and VouchNo>= " & Val(.LrText(0).Text)
Case 5 '凭证号范围(终止)
If Val(.LrText(1)) <> 0 Then
Str_QueryCondi = Str_QueryCondi & " and VouchNo<= " & Val(.LrText(1).Text)
End If
End Select
Next Jsqte
End With
'计算并显示实际最大、最小凭证号
Sqlstr = "SELECT MAX(VouchNo) VouchNoMax,Min(VouchNo) VouchNoMin From Cwzz_V_AccVouch" & Str_QueryCondi
Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
If Not IsNull(RecTemp.Fields("VouchNoMax")) Then
Int_VouchNoMax = RecTemp.Fields("VouchNoMax")
End If
If Not IsNull(RecTemp.Fields("VouchNoMin")) Then
Int_VouchNoMin = RecTemp.Fields("VouchNoMin")
End If
'显示实际凭证号范围
Lab_TitleText(2).Caption = ""
If Int_VouchNoMin <> 0 Then
Lab_TitleText(2).Caption = Mid(Trim(Str(10000 + Int_VouchNoMin)), 2, 4) + "-"
End If
If Int_VouchNoMax <> 0 Then
Lab_TitleText(2).Caption = Lab_TitleText(2).Caption + Mid(Trim(Str(10000 + Int_VouchNoMax)), 2, 4)
End If
'显示查询结果
Sqlstr = "SELECT c.*, d.ForeignCurrName FROM " & _
" (SELECT A.*, B.Cname,B.ForeignCurrCode, B.measure,B.CodeLevel,B.Cclass FROM " & _
" (SELECT SUM(Jfje) Jfjehj, SUM(Dfje) Dfjehj,SUM(WbJfje) WbJfjehj,SUM(WbDfje) WbDfjehj,SUM(Jfsl) Jfslhj,SUM(Dfsl) Dfslhj, Ccode From Cwzz_V_AccVouch " & _
Str_QueryCondi & " GROUP BY Ccode) A LEFT OUTER JOIN " & _
" Cwzz_AccCode B ON A.Ccode = B.Ccode) C " & _
" LEFT OUTER JOIN Gy_ForeignCurrency d ON c.ForeignCurrCode = d.ForeignCurrCode Order by C.Ccode"
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
With Rec_Query
CxbbGrid.Rows = CxbbGrid.FixedRows
CxbbGrid.Rows = CxbbGrid.FixedRows + .RecordCount
Jsqte = CxbbGrid.FixedRows
Do While Not .EOF
Str_CclassIndex = Fun_GetIndex("Cwzz_kmlx", Trim(.Fields("Cclass")))
'1.添加末级科目合计数据
If .Fields("CodeLevel") <= Int(PZ_FrmPzhztj.LrText(2).Text) Then
If Jsqte >= CxbbGrid.Rows Then
CxbbGrid.AddItem ""
End If
'添加科目类别和科目编码（目的为了排序）
CxbbGrid.TextMatrix(Jsqte, 0) = Str_CclassIndex + Trim(.Fields("Ccode"))
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Trim(.Fields("Ccode"))
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("Cname"))
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Trim(.Fields("ForeignCurrName") & "")
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Trim(.Fields("Measure") & "")
If .Fields("Jfjehj") <> 0 Then '借方金额合计
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(Str(.Fields("Jfjehj")))
End If
If .Fields("Dfjehj") <> 0 Then '贷方金额合计
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Str(.Fields("Dfjehj")))
End If
If .Fields("WbJfjehj") <> 0 And Trim(.Fields("ForeignCurrName") & "") <> "" Then '外币借方金额合计
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = Trim(Str(.Fields("WbJfjehj")))
End If
If .Fields("WbDfjehj") <> 0 And Trim(.Fields("ForeignCurrName") & "") <> "" Then '外币贷方金额合计
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = Trim(Str(.Fields("WbDfjehj")))
End If
If .Fields("Jfslhj") <> 0 Then '借方数量合计
CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = Trim(Str(.Fields("Jfslhj")))
End If
If .Fields("Dfslhj") <> 0 Then '贷方数量合计
CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = Trim(Str(.Fields("Dfslhj")))
End If
CxbbGrid.RowHeight(Jsqte) = Sjhgd
Jsqte = Jsqte + 1
End If
'2.累计上级科目数据
For Int_LevTe = 1 To Int(PZ_FrmPzhztj.LrText(2).Text)
If Mid(Trim(.Fields("Ccode")), 1, Int_CodeScheme(Int_LevTe)) <> Trim(.Fields("Ccode")) Then
Str_Parent = Trim(Mid(Trim(.Fields("Ccode")), 1, Int_CodeScheme(Int_LevTe)))
Lng_ParCol = CxbbGrid.FindRow(Str_Parent, , Sydz("001", GridStr(), Szzls))
If Lng_ParCol = -1 Then
If Jsqte >= CxbbGrid.Rows Then
CxbbGrid.AddItem ""
End If
CxbbGrid.RowHeight(Jsqte) = Sjhgd
CxbbGrid.TextMatrix(Jsqte, 0) = Str_CclassIndex + Str_Parent
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = Str_Parent
Set RecTemp = Cw_DataEnvi.DataConnect.Execute("Select Cwzz_AccCode.Cname FROM Cwzz_AccCode Where Ccode='" & Str_Parent & "'")
If Not RecTemp.EOF Then
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(RecTemp.Fields("Cname"))
End If
If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj")))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = ""
End If
If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj")))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = ""
End If
Jsqte = Jsqte + 1
Else
If Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj") <> 0 Then
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj")))
Else
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls)) = ""
End If
If Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj") <> 0 Then
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj")))
Else
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls)) = ""
End If
End If
Else
Exit For
End If
Next Int_LevTe
'3.按科目类别累计
Lng_ParCol = CxbbGrid.FindRow(Trim(.Fields("Cclass")) + "类合计", , Sydz("002", GridStr(), Szzls))
If Lng_ParCol = -1 Then
If Jsqte >= CxbbGrid.Rows Then
CxbbGrid.AddItem ""
End If
CxbbGrid.RowHeight(Jsqte) = Sjhgd
CxbbGrid.TextMatrix(Jsqte, 0) = Str_CclassIndex + "合计"
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("Cclass")) + "类合计"
If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj")))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = ""
End If
If Val(CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj")))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = ""
End If
CxbbGrid.Cell(flexcpBackColor, Jsqte, 0, , CxbbGrid.Cols - 1) = Lab_Color(0).BackColor
Jsqte = Jsqte + 1
Else
If Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj") <> 0 Then
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls))) + .Fields("Jfjehj")))
Else
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("005", GridStr(), Szzls)) = ""
End If
If Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj") <> 0 Then
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls)) = Trim(Str(Val(CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls))) + .Fields("Dfjehj")))
Else
CxbbGrid.TextMatrix(Lng_ParCol, Sydz("006", GridStr(), Szzls)) = ""
End If
End If
.MoveNext
Loop
End With
'截取无用行
CxbbGrid.Rows = Jsqte
'将网格按科目编码排序
With CxbbGrid
.Col = 0
.Sort = flexSortStringAscending
End With
'计算本币发生总合计
Sqlstr = "SELECT SUM(Jfje) Jfjehj, SUM(Dfje) Dfjehj From Cwzz_V_AccVouch " & _
Str_QueryCondi
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
With Rec_Query
If Not .EOF Then
CxbbGrid.AddItem ""
Jsqte = CxbbGrid.Rows - 1
CxbbGrid.RowHeight(Jsqte) = Sjhgd
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = "合计"
If .Fields("Jfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = .Fields("Jfjehj")
End If
If .Fields("Dfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = .Fields("Dfjehj")
End If
End If
End With
'计算各外币发生合计(去掉本位币)
Sqlstr = "Select b.*,c.ForeignCurrName From (SELECT SUM(WbJfje) WbJfjehj, SUM(WbDfje) WbDfjehj,a.ForeignCurrCode From Cwzz_V_AccVouch a" & _
Str_QueryCondi & "Group By a.ForeignCurrCode) b LEFT OUTER JOIN Gy_ForeignCurrency c ON b.ForeignCurrCode = c.ForeignCurrCode"
Set Rec_Query = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
With Rec_Query
Do While Not .EOF
If Trim(.Fields("ForeignCurrCode")) <> "" And Trim(.Fields("ForeignCurrCode")) <> XtSCurrCode Then
CxbbGrid.AddItem ""
Jsqte = CxbbGrid.Rows - 1
CxbbGrid.RowHeight(Jsqte) = Sjhgd
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("ForeignCurrName")) & "合计"
If .Fields("WbJfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = .Fields("WbJfjehj")
End If
If .Fields("WbDfjehj") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = .Fields("WbDfjehj")
End If
End If
.MoveNext
Loop
End With
']以上为用户自定义部分
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 = 1 '报 表 小 标 题 行 数
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(1) = Space(2) + "会计期间:" + Lab_TitleText(0) + Space(5) + "凭证范围:" + Lab_TitleText(1) + Space(5) + "凭证号:" + Lab_TitleText(2)
bbxbtzzxs(1) = 1 '报表行组织形式(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 + -