📄 frmlastyearcarryforward.frm
字号:
If Trim(.Fields("kmdm").Value) Like sKmdm(i) & "*" Then
If .Fields("yefx").Value = "借方" Then
dBalanceAmount = .Fields("ljjsl12").Value - .Fields("ljdsl12").Value
dBalanceForeign = .Fields("ljjwb12").Value - .Fields("ljdwb12").Value
dBalanceMoney = .Fields("ljj12").Value - .Fields("ljd12").Value
For m = 0 To 12
sMonth = Format(m, "00")
sSql = sSql & "ljjsl" & sMonth & " = " & dBalanceAmount & _
", ljjwb" & sMonth & " = " & dBalanceForeign & _
", ljj" & sMonth & " = " & dBalanceMoney & ","
Next m
sSql = Left(sSql, Len(sSql) - 1)
sCmdText = "UPDATE tZW_balance" & sCarryForwardYear & " set " & sSql
'如果该科目是末级科目, 则该科目为不可增加下级科目;
adoCmd.CommandText = sCmdText & " WHERE kmdm = '" & _
.Fields("kmdm").Value & "'"
Else
dBalanceAmount = .Fields("ljdsl12").Value - .Fields("ljjsl12").Value
dBalanceForeign = .Fields("ljdsl12").Value - .Fields("ljjsl12").Value
dBalanceMoney = .Fields("ljd12").Value - .Fields("ljj12").Value
For m = 0 To 12
sMonth = Format(m, "00")
sSql = sSql & " ljdsl" & sMonth & " =" & dBalanceAmount & _
", ljdwb" & sMonth & " = " & dBalanceForeign & _
", ljd" & sMonth & " = " & dBalanceMoney & ","
Next m
sSql = Left(sSql, Len(sSql) - 1)
sCmdText = "UPDATE tZW_balance" & sCarryForwardYear & " set " & sSql
adoCmd.CommandText = sCmdText & " WHERE kmdm = '" & _
.Fields("kmdm").Value & "'"
End If
adoCmd.Execute
Exit For
End If
Next i
.MoveNext
Loop
End If
.Close
End With
End If
End If
Case "辅助核算科目余额"
glo.frmProg.SetMsg "正在准备辅助核算科目余额数据, 请稍候..."
'从结转年份的科目表中查找是否存在记录,
'如果存在, 则可结转上年科目余额;
'否则, 不结转
adoSQL = "SELECT COUNT(*) FROM tZW_km" & sCarryForwardYear
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .Fields(0).Value > 0 Then
bExistKm = True
End If
.Close
End With
If bExistKm Then
bFound = False
'从上年科目表中取出有余额的一级科目代码
adoSQL = "SELECT A.kmdm,B.kmjc FROM tZW_balance" & sCarryForwardYear - 1 & " A ,tzw_km" & sCarryForwardYear - 1 & _
" B WHERE A.ljj12 <> A.ljd12 AND B.kmjc = 1 and A.kmdm=B.kmdm"
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
bFound = True
ReDim sKmdm(1 To .RecordCount)
.MoveFirst
i = 0
Do Until .EOF
i = i + 1
sKmdm(i) = Trim(.Fields("kmdm").Value)
.MoveNext
Loop
End If
.Close
End With
End If
If bFound Then
adoCmd.CommandText = "DELETE FROM tUSU_Fz" & sCarryForwardYear
adoCmd.Execute
adoSQL = "SELECT A.kmdm,A.Grwl_Code,A.Khwl_Code,A.Gyswl_Code,A.Bmdm,A.Xmdm," & _
"A.ljjsl12,A.ljjwb12,A.ljj12,A.ljdsl12,A.ljdwb12,A.ljd12,B.yefx" & _
" FROM tUSU_Fz" & sCarryForwardYear - 1 & " A,tZW_Km" & sCarryForwardYear - 1 & " B" & _
" WHERE rtrim(A.kmdm) = rtrim(B.kmdm)" & _
" AND A.ljj12 <> A.ljd12" & _
" AND (B.IsGrwlhs = -1 OR B.IsKhwlhs = -1 OR B.IsGyswlhs = -1" & _
" OR B.IsBmhs = -1 OR B.IsXmhs = -1)"
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
iTotalNum = .RecordCount
If .RecordCount > PrecentOfFz Then
IsBig = True
pgrNum = .RecordCount / PrecentOfFz
Else
IsBig = False
pgrNum = PrecentOfFz / .RecordCount
End If
CurNum = 0
CurPgrNum = glo.frmProg.pbr.Value
.MoveFirst
Do Until .EOF
CurNum = CurNum + 1
If IsBig Then
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / pgrNum)
Else
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum * pgrNum)
End If
glo.frmProg.SetMsg "正在结转辅助核算科目余额数据... 第" & CurNum & "/" & iTotalNum & "笔"
For i = LBound(sKmdm) To UBound(sKmdm)
'如果该科目的一级科目存在余额,则结转该科目的上年余额
If Trim(.Fields("kmdm").Value) Like sKmdm(i) & "*" Then
If .Fields("yefx").Value = "借方" Then
dBalanceAmount = .Fields("ljjsl12").Value - .Fields("ljdsl12").Value
dBalanceForeign = .Fields("ljjwb12").Value - .Fields("ljdwb12").Value
dBalanceMoney = .Fields("ljj12").Value - .Fields("ljd12").Value
adoCmd.CommandText = "INSERT INTO tUSU_Fz" & sCarryForwardYear & _
"(ID,kmdm,Grwl_Code,Khwl_Code,Gyswl_Code,Bmdm,Xmdm," & _
"ljjsl00,ljjwb00,ljj00)" & _
" VALUES(" & CurNum & ",'" & .Fields("kmdm").Value & "','" & _
.Fields("Grwl_Code").Value & "','" & _
.Fields("Khwl_Code").Value & "','" & _
.Fields("Gyswl_Code").Value & "','" & _
.Fields("Bmdm").Value & "','" & _
.Fields("Xmdm").Value & "'," & _
dBalanceAmount & "," & dBalanceForeign & "," & dBalanceMoney & ")"
Else
dBalanceAmount = .Fields("ljdsl12").Value - .Fields("ljjsl12").Value
dBalanceForeign = .Fields("ljdsl12").Value - .Fields("ljjsl12").Value
dBalanceMoney = .Fields("ljd12").Value - .Fields("ljj12").Value
adoCmd.CommandText = "INSERT INTO tUSU_Fz" & sCarryForwardYear & _
"(ID,kmdm,Grwl_Code,Khwl_Code,Gyswl_Code,Bmdm,Xmdm," & _
"ljdsl00,ljdwb00,ljd00)" & _
" VALUES(" & CurNum & ",'" & .Fields("kmdm").Value & "','" & _
.Fields("Grwl_Code").Value & "','" & _
.Fields("Khwl_Code").Value & "','" & _
.Fields("Gyswl_Code").Value & "','" & _
.Fields("Bmdm").Value & "','" & _
.Fields("Xmdm").Value & "'," & _
dBalanceAmount & "," & dBalanceForeign & "," & dBalanceMoney & ")"
End If
adoCmd.Execute
Exit For
End If
Next i
.MoveNext
Loop
End If
.Close
End With
End If
Case "汇率数据"
Case "未两清单位银行账"
glo.frmProg.SetMsg "正在准备未两清单位银行账, 请稍候..."
IsExistCarryForwardData = False
adoSQL = "SELECT COUNT(*) FROM tZW_Km" & sCarryForwardYear - 1 & _
" WHERE IsYhz = -1 AND IsEndKm = -1"
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .Fields(0).Value = 0 Then
MsgBox "未设置银行账科目, 不能结转未两清单位银行账!", vbInformation
.Close
Exit Sub
End If
.Close
End With
adoSQL = "SELECT A.kmdm,qyrq FROM tZW_Yhdzqyrq A,tZW_Km" & sCarryForwardYear - 1 & " B" & _
" WHERE rtrim(A.kmdm) = rtrim(B.kmdm) AND B.IsYhz = -1 AND B.IsEndKm = -1 " & _
" ORDER BY A.kmdm"
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount = 0 Then
MsgBox "银行对账尚未启用, 不能结转未两清单位银行账!", vbInformation
.Close
Exit Sub
Else
ReDim sKmdm(1 To .RecordCount)
ReDim sYhdzqyrq(.RecordCount)
.MoveFirst
i = 0
Do Until .EOF
i = i + 1
sKmdm(i) = Trim(.Fields("kmdm").Value)
If Year(.Fields("qyrq").Value) = CInt(sCarryForwardYear) - 1 Then
sYhdzqyrq(i) = Format(.Fields("qyrq").Value, "yyyy-mm-dd")
Else
sYhdzqyrq(i) = sCarryForwardYear - 1 & "-01-01"
End If
.MoveNext
Loop
For i = LBound(sKmdm) To UBound(sKmdm)
If i = LBound(sKmdm) Then
sYhKmdm = sKmdm(i)
Else
sYhKmdm = sYhKmdm & "," & sKmdm(i)
End If
Next i
End If
.Close
End With
adoCmd.CommandText = "DELETE FROM tZW_Pzsj" & sCarryForwardYear & _
" WHERE kjqj = 20 OR kjqj = 21"
adoCmd.Execute
iMaxJlhm = 0
'依次结转各个科目的调整前余额、未两清银行账
For iKmNum = LBound(sKmdm) To UBound(sKmdm)
Select Case g_FLAT
Case "SQL"
adoSQL = "SELECT * FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & _
"' AND (kjqj = 21 OR (kjqj < 20 AND" & _
" pzrq >= '" & sYhdzqyrq(iKmNum) & "'))" & _
" AND yhdz_lqbz Is Null AND xgbz = '2'"
Case "ORACLE"
adoSQL = "SELECT * FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & _
"' AND (kjqj = 21 OR (kjqj < 20 AND" & _
" pzrq >= TO_DATE('" & sYhdzqyrq(iKmNum) & _
"','YYYY-MM-DD')))" & _
" AND yhdz_lqbz Is Null AND xgbz = '2'"
End Select
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
IsExistCarryForwardData = True
iTotalNum = .RecordCount
If .RecordCount > PrecentOfPz Then
IsBig = True
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -