📄
字号:
" Bydfljslhj=Isnull(Sum(Bydfljsl-Mdsl),0),Byjfljwbhj=Isnull(Sum(Byjfljwb-Mjwb),0),Bydfljwbhj=Isnull(Sum(Bydfljwb-Mdwb),0) " & _
" From Cwzz_AccSumassi Where Ccode like '" & Str_Ccode & "%' And PersonCode='" & Str_FzCode & "' And Year=" & Int_Year & _
" And Period=" & Int_BPeriod
Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
If Not RecTemp.EOF Then
Dbl_Qcyete = RecTemp.Fields("Qcyehj")
Dbl_Qcslte = RecTemp.Fields("Qcslhj")
Dbl_Qcwbte = RecTemp.Fields("Qcwbhj")
'截止累计数据需要减掉本会计期间相应记帐数据
Dbl_Jejflj = RecTemp.Fields("Byjfljjehj")
Dbl_Jedflj = RecTemp.Fields("Bydfljjehj")
Dbl_Sljflj = RecTemp.Fields("Byjfljslhj")
Dbl_Sldflj = RecTemp.Fields("Bydfljslhj")
Dbl_Wbjflj = RecTemp.Fields("Byjfljwbhj")
Dbl_Wbdflj = RecTemp.Fields("Bydfljwbhj")
End If
'如果包含未记帐凭证则计算小于查询起始会计期间包含科目借贷方合计
If Bln_IncluNotBook Then
Sqlstr = "SELECT Jfjehj=Isnull(Sum(Jfje),0),Dfjehj=Isnull(Sum(Dfje),0),Jfslhj=Isnull(Sum(Jfsl),0),Dfslhj=Isnull(Sum(Dfsl),0) " & _
",Wbjfjehj=Isnull(Sum(Wbjfje),0),Wbdfjehj=Isnull(Sum(Wbdfje),0) From Cwzz_V_AccVouch Where BookFlag=0 And Ccode like '" & Str_Ccode & "%' And PersonCode='" & Str_FzCode & "' And Year=" & Int_Year & _
" And Period<" & Int_BPeriod
Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
If Not RecTemp.EOF Then
Dbl_Qcyete = Dbl_Qcyete + RecTemp.Fields("Jfjehj") - RecTemp.Fields("Dfjehj")
Dbl_Qcslte = Dbl_Qcslte + RecTemp.Fields("Jfslhj") - RecTemp.Fields("Dfslhj")
Dbl_Qcwbte = Dbl_Qcwbte + RecTemp.Fields("Wbjfjehj") - RecTemp.Fields("Wbdfjehj")
'计算相应截止查询起始会计期间累计数据
Dbl_Jejflj = Dbl_Jejflj + RecTemp.Fields("Jfjehj")
Dbl_Jedflj = Dbl_Jedflj + RecTemp.Fields("Dfjehj")
Dbl_Sljflj = Dbl_Sljflj + RecTemp.Fields("Jfslhj")
Dbl_Sldflj = Dbl_Sldflj + RecTemp.Fields("Dfslhj")
Dbl_Wbjflj = Dbl_Wbjflj + RecTemp.Fields("Wbjfjehj")
Dbl_Wbdflj = Dbl_Wbdflj + RecTemp.Fields("Wbdfjehj")
End If
End If
If (Dbl_Qcyete <> 0 Or (Bln_EndFlag And (Dbl_Qcslte <> 0 Or dnl_qcwbte <> 0))) Or Bln_QcZeroShow Then '如果期初数据为零,且用户选择不输出则不显示此行数据
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'将期初数据写入网格(第1会计期间为上年结转,其余会计期间为期初余额)
If Int_BPeriod = 1 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "上年结转"
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "期初余额"
End If
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
'2.读取查询会计期间范围内所有符合条件明细帐数据(以年度+会计期间),并写入网格
Bln_NotFirst = False
'筛选所有符合条件数据
Sqlstr = "SELECT *" & _
" From Cwzz_V_AccVouchAss Where Ccode like '" & Str_Ccode & "%' And PersonCode='" & Str_FzCode & "' And Year=" & Int_Year & _
" And Period>=" & Int_BPeriod & " And Period<=" & Int_EPeriod
If Not Bln_IncluNotBook Then
Sqlstr = Sqlstr & " And BookFlag=1"
End If
Sqlstr = Sqlstr & " Order By Year,Period,Ddate,VouchID,SerialID"
Set RecTemp = Cw_DataEnvi.DataConnect.Execute(Sqlstr)
With RecTemp
Do While Not .EOF
If Bln_NotFirst Then
'当前进行会计期间发生变化则添加本月合计及本年累计
If .Fields("Period") <> Int_DqPeriod Then
'显示本月合计
If Dbl_Jejfhj <> 0 Or Dbl_Jedfhj <> 0 Or (Bln_EndFlag And (Dbl_Sljfhj <> 0 Or Dbl_Sldfhj <> 0 Or Dbl_Wbjfhj <> 0 Or Dbl_Wbdfhj <> 0)) Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'输出本月合计
Call Sub_OutPeriodSum(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Int_DqPeriod, Dbl_Jejfhj, Dbl_Jedfhj, Dbl_Sljfhj, Dbl_Sldfhj, Dbl_Wbjfhj, Dbl_Wbdfhj)
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
'重新初始化本月合计数据
Dbl_Jejfhj = 0
Dbl_Jedfhj = 0
Dbl_Sljfhj = 0
Dbl_Sldfhj = 0
Dbl_Wbjfhj = 0
Dbl_Wbdfhj = 0
'显示本年累计
If Dbl_Jejflj <> 0 Or Dbl_Jedflj <> 0 Or (Bln_EndFlag And (Dbl_Sljflj <> 0 Or Dbl_Sldflj <> 0 Or Dbl_Wbjflj <> 0 Or Dbl_Wbdflj <> 0)) Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'输出本年累计
Call Sub_OutYearSum(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Int_DqPeriod, Dbl_Jejflj, Dbl_Jedflj, Dbl_Sljflj, Dbl_Sldflj, Dbl_Wbjflj, Dbl_Wbdflj)
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
End If '显示本月合计,本年累计完毕
End If
Bln_NotFirst = True
'显示明细数据
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'未记帐明细分录变色显示
If .Fields("BookFlag") = 0 Then
CxbbGrid.Cell(flexcpBackColor, Jsqte, 0, , CxbbGrid.Cols - 1) = Lab_Color(0).BackColor
End If
'修改当前会计期间
Int_DqPeriod = .Fields("Period")
CxbbGrid.TextMatrix(Jsqte, 0) = .Fields("VouchId") '凭证ID
If IsDate(.Fields("Ddate")) Then
CxbbGrid.TextMatrix(Jsqte, Sydz("001", GridStr(), Szzls)) = .Fields("Ddate") '日期
End If
CxbbGrid.TextMatrix(Jsqte, Sydz("002", GridStr(), Szzls)) = Trim(.Fields("VouchClassCode") & "") + "-" + Mid(Trim(Str(10000 + .Fields("VouchNo"))), 2, 4) '凭证字号
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = Trim(.Fields("Digest") & "") '摘要
'客户
If Trim(.Fields("CusName") & "") <> "" Then
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) + " " + Trim(.Fields("CusName") & "")
End If
'项目
If Trim(.Fields("ItemName") & "") <> "" Then
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) + " " + Trim(.Fields("ItemName") & "")
End If
If .Fields("Jfje") <> 0 Then '借方金额
CxbbGrid.TextMatrix(Jsqte, Sydz("006", GridStr(), Szzls)) = .Fields("Jfje")
CxbbGrid.TextMatrix(Jsqte, Sydz("009", GridStr(), Szzls)) = .Fields("Jfje")
End If
If .Fields("Dfje") <> 0 Then '贷方金额
CxbbGrid.TextMatrix(Jsqte, Sydz("010", GridStr(), Szzls)) = .Fields("Dfje")
CxbbGrid.TextMatrix(Jsqte, Sydz("013", GridStr(), Szzls)) = .Fields("Dfje")
End If
'只有末级科目才显示数量和外币,数量不为零则计算单价
If Bln_EndFlag Then
If .Fields("Jfsl") <> 0 Then '借方数量
CxbbGrid.TextMatrix(Jsqte, Sydz("007", GridStr(), Szzls)) = .Fields("Jfsl")
If .Fields("Jfje") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Format(.Fields("Jfje") / .Fields("Jfsl"), "##." + String(Xtdjxsws, "0"))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Format(.Fields("Dfje") / .Fields("Jfsl"), "##." + String(Xtdjxsws, "0"))
End If
End If
If .Fields("Dfsl") <> 0 Then '贷方数量
CxbbGrid.TextMatrix(Jsqte, Sydz("011", GridStr(), Szzls)) = .Fields("Dfsl")
If .Fields("Jfje") <> 0 Then
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Format(.Fields("Jfje") / .Fields("Dfsl"), "##." + String(Xtdjxsws, "0"))
Else
CxbbGrid.TextMatrix(Jsqte, Sydz("004", GridStr(), Szzls)) = Format(.Fields("Dfje") / .Fields("Dfsl"), "##." + String(Xtdjxsws, "0"))
End If
End If
End If
If Bln_EndFlag And Bln_ForeignFlag Then
If .Fields("Wbjfje") <> 0 Then '借方外币
CxbbGrid.TextMatrix(Jsqte, Sydz("008", GridStr(), Szzls)) = .Fields("Wbjfje")
End If
If .Fields("Wbdfje") <> 0 Then '贷方外币
CxbbGrid.TextMatrix(Jsqte, Sydz("012", GridStr(), Szzls)) = .Fields("Wbdfje")
End If
If .Fields("AccRate") <> 0 Then '记帐汇率
CxbbGrid.TextMatrix(Jsqte, Sydz("005", GridStr(), Szzls)) = Format(.Fields("AccRate"), "##." + String(Int_Wbhlxsws, "0"))
End If
End If
'累加本月合计,本年累计数据
Dbl_Jejfhj = Dbl_Jejfhj + .Fields("Jfje")
Dbl_Jedfhj = Dbl_Jedfhj + .Fields("Dfje")
Dbl_Sljfhj = Dbl_Sljfhj + .Fields("Jfsl")
Dbl_Sldfhj = Dbl_Sldfhj + .Fields("Dfsl")
Dbl_Wbjfhj = Dbl_Wbjfhj + .Fields("Wbjfje")
Dbl_Wbdfhj = Dbl_Wbdfhj + .Fields("Wbdfje")
Dbl_Jejflj = Dbl_Jejflj + .Fields("Jfje")
Dbl_Jedflj = Dbl_Jedflj + .Fields("Dfje")
Dbl_Sljflj = Dbl_Sljflj + .Fields("Jfsl")
Dbl_Sldflj = Dbl_Sldflj + .Fields("Dfsl")
Dbl_Wbjflj = Dbl_Wbjflj + .Fields("Wbjfje")
Dbl_Wbdflj = Dbl_Wbdflj + .Fields("Wbdfje")
'3.计算余额
Dbl_Qcyete = Dbl_Qcyete + .Fields("Jfje") - .Fields("Dfje")
Dbl_Qcslte = Dbl_Qcslte + .Fields("Jfsl") - .Fields("Dfsl")
Dbl_Qcwbte = Dbl_Qcwbte + .Fields("Wbjfje") - .Fields("Wbdfje")
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
.MoveNext
Loop
'添加本月合计及本年累计
If Bln_NotFirst Then
If Dbl_Jejfhj <> 0 Or Dbl_Jedfhj <> 0 Or (Bln_EndFlag And (Dbl_Sljfhj <> 0 Or Dbl_Sldfhj <> 0 Or Dbl_Wbjfhj <> 0 Or Dbl_Wbdfhj <> 0)) Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'输出本月合计
Call Sub_OutPeriodSum(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Int_DqPeriod, Dbl_Jejfhj, Dbl_Jedfhj, Dbl_Sljfhj, Dbl_Sldfhj, Dbl_Wbjfhj, Dbl_Wbdfhj)
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
'显示本年累计
If Dbl_Jejflj <> 0 Or Dbl_Jedflj <> 0 Or (Bln_EndFlag And (Dbl_Sljflj <> 0 Or Dbl_Sldflj <> 0 Or Dbl_Wbjflj <> 0 Or Dbl_Wbdflj <> 0)) Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
'输出本年累计
Call Sub_OutYearSum(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Int_DqPeriod, Dbl_Jejflj, Dbl_Jedflj, Dbl_Sljflj, Dbl_Sldflj, Dbl_Wbjflj, Dbl_Wbdflj)
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
'如果会计期间为第12会计期间则输出结转下年
If Int_DqPeriod = 12 Then
CxbbGrid.AddItem ""
Jsqte = Jsqte + 1
CxbbGrid.TextMatrix(Jsqte, Sydz("003", GridStr(), Szzls)) = "结转下年"
'输出余额
Call Sub_OutBalance(Jsqte, Bln_EndFlag, Bln_ForeignFlag, Dbl_Qcyete, Dbl_Qcslte, Dbl_Qcwbte)
End If
End If
End With
'重置网格行高
For Jsqte = CxbbGrid.FixedRows To CxbbGrid.Rows - 1
CxbbGrid.RowHeight(Jsqte) = Sjhgd
Next Jsqte
End Sub
Private Sub Combo_AccFormat_Click() '用户选择不同帐页格式
Dim Int_Coljsq As Integer '网格列临时计数器
'为了加快显示速度
CxbbGrid.Redraw = False
For Int_Coljsq = Qslz To CxbbGrid.Cols - 1
CxbbGrid.ColHidden(Int_Coljsq) = True
Next Int_Coljsq
Select Case Combo_AccFormat
Case "金额式"
CxbbGrid.ColHidden(Sydz("001", GridStr(), Szzls)) = False '日期
CxbbGrid.ColHidden(Sydz("002", GridStr(), Szzls)) = False '凭证字号
CxbbGrid.ColHidden(Sydz("003", GridStr(), Szzls)) = False '摘要
CxbbGrid.ColHidden(Sydz("006", GridStr(), Szzls)) = False '借方.借方
CxbbGrid.ColHidden(Sydz("010", GridStr(), Szzls)) = False '贷方.贷方
CxbbGrid.ColHidden(Sydz("014", GridStr(), Szzls)) = False '方向.方向
CxbbGrid.ColHidden(Sydz("020", GridStr(), Szzls)) = False '余额.余额
Case "外币金额式"
CxbbGrid.ColHidden(Sydz("001", GridStr(), Szzls)) = False '日期
CxbbGrid.ColHidden(Sydz("002", GridStr(), Szzls)) = False '凭证字号
CxbbGrid.ColHidden(Sydz("003", GridStr(), Szzls)) = False '摘要
CxbbGrid.ColHidden(Sydz("005", GridStr(), Szzls)) = False '汇率
CxbbGrid.ColHidden(Sydz("008", GridStr(), Szzls)) = False '借方.外币
CxbbGrid.ColHidden(Sydz("009", GridStr(), Szzls)) = False '借方.金额
CxbbGrid.ColHidden(Sydz("012", GridStr(), Szzls)) = False '贷方.外币
CxbbGrid.ColHidden(Sydz("013", GridStr(), Szzls)) = False '贷方.金额
CxbbGrid.ColHidden(Sydz("014", GridStr(), Szzls)) = False '方向.方向
CxbbGrid.ColHidden(Sydz("017", GridStr(), Szzls)) = False '余额.外币
CxbbGrid.ColHidden(Sydz("018", GridStr(), Szzls)) = False '余额.汇率
CxbbGrid.ColHidden(Sydz("019", GridStr(), Szzls)) = False '余额.金额
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -