📄 frmlastyearcarryforward.frm
字号:
End Select
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
IsExistCarryForwardData = True
iTotalNum = .RecordCount
If .RecordCount > PrecentOfYhdzd Then
IsBig = True
pgrNum = .RecordCount / PrecentOfYhdzd
Else
IsBig = False
pgrNum = PrecentOfYhdzd / .RecordCount
End If
'动态分配存放字段值
ReDim sSingleRecord(1 To .Fields.Count)
'动态分配存放记录
ReDim vTotalRecord(1 To .RecordCount)
.MoveFirst
j = 0
CurNum = 0
CurPgrNum = glo.frmProg.pbr.Value
CurNum = CurNum + 1
If IsBig Then
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 / pgrNum)
Else
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 * pgrNum)
End If
glo.frmProg.SetMsg "正在结转未两清银行对账单... 第" & Int(CurNum / 2) & "/" & iTotalNum & "笔"
Do Until .EOF
For i = 1 To .Fields.Count
If IsNull(.Fields(i - 1).Value) Then
Select Case .Fields(i - 1).Type
Case adChar, adVarChar
sSingleRecord(i) = "Null"
Case adDBTimeStamp
sSingleRecord(i) = "Null"
Case adDecimal, adDouble, adInteger, adCurrency, _
adSingle, adSmallInt, adTinyInt, adNumeric
sSingleRecord(i) = "Null"
End Select
Else
If .Fields(i - 1).Type = adDBTimeStamp Then
Select Case g_FLAT
Case "SQL"
sSingleRecord(i) = Format(.Fields(i - 1).Value, "yyyy-mm-dd")
Case "ORACLE"
sSingleRecord(i) = "TO_DATE('" & Format(.Fields(i - 1).Value, "yyyy-mm-dd") & "','YYYY-MM-DD')"
End Select
Else
'如果是第一个字段, 则设置其值为1,表示期初未两清的对账单
If UCase(.Fields(i - 1).Name) = UCase("id") Then
iMaxId = iMaxId + 1
sSingleRecord(i) = iMaxId
ElseIf UCase(.Fields(i - 1).Name) = UCase("qcbz") Then
sSingleRecord(i) = 1
Else
sSingleRecord(i) = .Fields(i - 1).Value
End If
End If
End If
Next i
j = j + 1
vTotalRecord(j) = sSingleRecord
.MoveNext
Loop
Else
IsExistCarryForwardData = False
End If
.Close
End With
If IsExistCarryForwardData Then
For i = LBound(vTotalRecord) To UBound(vTotalRecord)
CurNum = CurNum + 1
If IsBig Then
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 / pgrNum)
Else
glo.frmProg.ShowProgress CurPgrNum + Int(CurNum / 2 * pgrNum)
End If
glo.frmProg.SetMsg "正在结转未两清银行对账单... 第" & Int(CurNum / 2) & "/" & iTotalNum & "笔"
insertStr = ""
For j = LBound(vTotalRecord(i)) To UBound(vTotalRecord(i))
If j = UBound(vTotalRecord(i)) Then
If vTotalRecord(i)(j) = "Null" Then
insertStr = insertStr & vTotalRecord(i)(j)
ElseIf vTotalRecord(i)(j) Like "TO_DATE*" Then
insertStr = insertStr & vTotalRecord(i)(j)
Else
insertStr = insertStr & "'" & vTotalRecord(i)(j) & "'"
End If
Else
If vTotalRecord(i)(j) = "Null" Then
insertStr = insertStr & vTotalRecord(i)(j) & ","
ElseIf vTotalRecord(i)(j) Like "TO_DATE*" Then
insertStr = insertStr & vTotalRecord(i)(j) & ","
Else
insertStr = insertStr & "'" & vTotalRecord(i)(j) & "',"
End If
End If
Next j
adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
" VALUES(" & insertStr & ")"
adoCmd.Execute
Next i
End If
'求出银行方上年调整前余额
glo.frmProg.SetMsg "正在计算银行对账单调整前余额, 请稍候..."
Select Case g_FLAT
Case "SQL"
adoSQL = "SELECT fx,SUM(je) sumMoney FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (qcbz = 0 OR (qcbz = 2 AND rq >= '" & sYhdzqyrq(iKmNum) & "'))" & _
" GROUP BY fx"
Case "ORACLE"
adoSQL = "SELECT fx,SUM(je) sumMoney FROM tZW_Yhdzd" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (qcbz = 0 OR (qcbz = 2 AND" & _
" rq >= TO_DATE('" & sYhdzqyrq(iKmNum) & "','YYYY-MM-DD')))" & _
" GROUP BY fx"
End Select
dYhtzqye = 0
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .BOF And .EOF Then
dYhtzqye = 0
ElseIf IsNull(.Fields("sumMoney").Value) Then
dYhtzqye = 0
Else
.MoveFirst
Do Until .EOF
If .Fields("fx").Value = "贷" Then
dYhtzqye = dYhtzqye + .Fields("sumMoney").Value
Else
dYhtzqye = dYhtzqye - .Fields("sumMoney").Value
End If
.MoveNext
Loop
iMaxId = iMaxId + 1
Select Case g_FLAT
Case "SQL"
adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
"(id,rq,kmdm,fx,je,qcbz) " & _
"VALUES(" & iMaxId & ",'" & _
Format(sCarryForwardYear & "-01-01", "yyyy-mm-dd") & "','" & _
sKmdm(iKmNum) & "','贷'," & _
dYhtzqye & ",0)"
Case "ORACLE"
adoCmd.CommandText = "INSERT INTO tZW_Yhdzd" & sCarryForwardYear & _
"(id,rq,kmdm,fx,je,qcbz) " & _
"VALUES(" & iMaxId & _
",TO_DATE('" & sCarryForwardYear & "-01-01','YYYY-MM-DD'),'" & _
sKmdm(iKmNum) & "','贷'," & _
dYhtzqye & ",0)"
End Select
adoCmd.Execute
End If
.Close
End With
Next iKmNum
Case "转账凭证设置"
'删除结转年份的转账设置表
adoCmd.CommandText = "DELETE FROM tZW_ZzpzSet" & sCarryForwardYear
adoCmd.Execute
IsExistCarryForwardData = False
'从结转年份上一年中取出记录
adoSQL = "SELECT * FROM tZW_ZzpzSet" & sCarryForwardYear - 1
With adoRst
.Open adoSQL, adoCnnAccount, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
IsExistCarryForwardData = True
'动态分配存放字段值
ReDim sSingleRecord(1 To .Fields.Count)
'动态分配存放记录
ReDim vTotalRecord(1 To .RecordCount)
.MoveFirst
j = 0
Do Until .EOF
glo.frmProg.SetMsg "正在结转转账凭证设置表"
For i = 1 To 8
If IsNull(.Fields(i - 1).Value) Then
Select Case .Fields(i - 1).Type
Case adChar, adVarChar
sSingleRecord(i) = "Null"
Case adDBTimeStamp
sSingleRecord(i) = "Null"
Case adDecimal, adDouble, adInteger, adCurrency, _
adSingle, adSmallInt, adTinyInt, adNumeric
sSingleRecord(i) = "Null"
End Select
Else
sSingleRecord(i) = .Fields(i - 1).Value
End If
Next i
j = j + 1
vTotalRecord(j) = sSingleRecord
.MoveNext
Loop
End If
.Close
End With
If IsExistCarryForwardData Then
'结转转账凭证设置记录
For i = LBound(vTotalRecord) To UBound(vTotalRecord)
glo.frmProg.SetMsg "正在结转转账凭证设置表"
insertStr = ""
For j = LBound(vTotalRecord(i)) To UBound(vTotalRecord(i))
If j = UBound(vTotalRecord(i)) Then
If vTotalRecord(i)(j) = "Null" Then
insertStr = insertStr & vTotalRecord(i)(j)
Else
insertStr = insertStr & "'" & vTotalRecord(i)(j) & "'"
End If
Else
If vTotalRecord(i)(j) = "Null" Then
insertStr = insertStr & vTotalRecord(i)(j) & ","
Else
insertStr = insertStr & "'" & vTotalRecord(i)(j) & "',"
End If
End If
Next j
adoCmd.CommandText = "INSERT INTO tZW_ZzpzSet" & sCarryForwardYear & _
" VALUES(" & insertStr & ")"
adoCmd.Execute
Next i
End If
Case "现金流量项目"
Case "项目数据来源"
End Select
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -