📄 frmlastyearcarryforward.frm
字号:
" AND yhdz_lqbz Is Null AND xgbz = '2' " + IIf(GetKmWbdw(sKmdm(iKmNum)) <> "", "and wb<>0", "")
End Select
With adoRst
.Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
If .RecordCount > 0 Then
IsExistCarryForwardData = True
iTotalNum = .RecordCount
If .RecordCount > PrecentOfPz Then
IsBig = True
pgrNum = .RecordCount / PrecentOfPz
Else
IsBig = False
pgrNum = PrecentOfPz / .RecordCount
End If
'===================================2002.8.22==yao add===================================================
sFieldName = ""
For j = 1 To .Fields.Count
sFieldName = sFieldName & .Fields(j - 1).Name & ","
Next j
If sFieldName <> "" Then
sFieldName = Mid(sFieldName, 1, Len(sFieldName) - 1)
End If
'========================================================================================================
'动态分配存放字段值
ReDim sSingleRecord(1 To .Fields.Count)
'动态分配存放记录
ReDim vTotalRecord(1 To .RecordCount)
'动态分配存放字段类型
.MoveFirst
j = 0
CurNum = 0
CurPgrNum = glo.frmProg.pBr.Value
Do Until .EOF
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 & "笔"
For i = 1 To .Fields.Count
'=====================================2002.8.22===yao revise=================================================
' 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
If IsNull(.Fields(i - 1).Value) Or Trim("" & .Fields(i - 1).Value) = "" Then
sSingleRecord(i) = "Null"
Else
Select Case .Fields(i - 1).Type
Case adDBTimeStamp
' 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
Case adDecimal, adDouble, adInteger, adCurrency, adSingle, adSmallInt, adTinyInt, adNumeric, adVarNumeric
If UCase(.Fields(i - 1).Name) = UCase("kjqj") Then
sSingleRecord(i) = "adNum_21"
ElseIf UCase(.Fields(i - 1).Name) = UCase("jlhm") Then
iMaxJlhm = iMaxJlhm + 1
sSingleRecord(i) = "adNum_" & iMaxJlhm
Else
sSingleRecord(i) = "adNum_" & .Fields(i - 1).Value
End If
Case adChar, adVarChar
' Else
If UCase(.Fields(i - 1).Name) = UCase("id") Then
iID = iID + 1
sSingleRecord(i) = Format(iID, "00000000")
Else
' sSingleRecord(i) = 21
' '如果是记录号码字段, 则设置为当前最大号码加1
' ElseIf UCase(.Fields(i - 1).Name) = UCase("jlhm") Then
' iMaxJlhm = iMaxJlhm + 1
' sSingleRecord(i) = iMaxJlhm
' Else
sSingleRecord(i) = Trim("" & .Fields(i - 1).Value)
End If
' End If
End Select
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)
ElseIf vTotalRecord(i)(j) Like "adNum_*" Then
insertStr = insertStr & Replace(vTotalRecord(i)(j), "adNum_", "")
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) & ","
ElseIf vTotalRecord(i)(j) Like "adNum_*" Then
insertStr = insertStr & Replace(vTotalRecord(i)(j), "adNum_", "") & ","
Else
insertStr = insertStr & "'" & vTotalRecord(i)(j) & "',"
End If
End If
Next j
adoCmd.CommandText = "INSERT INTO tZW_Pzsj" & sCarryForwardYear & "(" & sFieldName & _
") VALUES(" & insertStr & ")"
adoCmd.Execute
Next i
End If
'求出单位方上年调整前余额
glo.frmProg.SetMsg "正在计算单位银行账调整前余额, 请稍候..."
dDwtzqye = 0
dDwtzqwb = 0
Select Case g_FLAT
Case "SQL"
adoSQL = "SELECT fx,SUM(je) sumMoney,Sum(wb) sumWB FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (kjqj = 20 ) AND xgbz = '2'" & _
" GROUP BY fx"
Case "ORACLE"
adoSQL = "SELECT fx,SUM(je) sumMoney,Sum(wb) sumWB FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (kjqj = 20 " & _
" ) AND xgbz = '2'" & _
" GROUP BY fx"
End Select
adoRst.Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
With adoRst
While adoRst.EOF = False
If adoRst.Fields("fx").Value = "借" Then
dDwtzqye = dDwtzqye + FormatToDouble(.Fields("sumMoney").Value)
dDwtzqwb = dDwtzqwb + FormatToDouble(.Fields("sumMoney").Value)
Else
dDwtzqye = dDwtzqye - FormatToDouble(.Fields("sumMoney").Value)
dDwtzqwb = dDwtzqwb - FormatToDouble(.Fields("sumMoney").Value)
End If
adoRst.MoveNext
Wend
End With
adoRst.Close
Select Case g_FLAT
Case "SQL"
adoSQL = "SELECT fx,SUM(je) sumMoney,Sum(wb) sumWB FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (pzrq >= '" & sYhdzqyrq(iKmNum) & _
"' AND kjqj < 20) AND xgbz = '2'" & _
" GROUP BY fx"
Case "ORACLE"
adoSQL = "SELECT fx,SUM(je) sumMoney,Sum(wb) sumWB FROM tZW_Pzsj" & sCarryForwardYear - 1 & _
" WHERE kmdm = '" & sKmdm(iKmNum) & "'" & _
" AND (pzrq >= TO_DATE('" & sYhdzqyrq(iKmNum) & "','YYYY-MM-DD')" & _
" AND kjqj < 20) AND xgbz = '2'" & _
" GROUP BY fx"
End Select
With adoRst
.Open adoSQL, glo.cnnMain, adOpenStatic, adLockReadOnly
If .BOF And .EOF Then
dDwtzqye = 0
dDwtzqwb = 0
.Close
Else
.MoveFirst
Do Until .EOF
If .Fields("fx").Value = "借" Then
dDwtzqye = dDwtzqye + FormatToDouble(.Fields("sumMoney").Value)
dDwtzqwb = dDwtzqwb + FormatToDouble(.Fields("sumWB").Value)
Else
dDwtzqye = dDwtzqye - FormatToDouble(.Fields("sumMoney").Value)
dDwtzqwb = dDwtzqwb - FormatToDouble(.Fields("sumWB").Value)
End If
.MoveNext
Loop
.Close
If dDwtzqye = 0 Then
Else
iMaxJlhm = iMaxJlhm + 1
iID = iID + 1
Select Case g_FLAT
Case "SQL"
If GetKmWbdw(sKmdm(iKmNum)) = "" Then
adoCmd.CommandText = "INSERT INTO tZW_pzsj" & sCarryForwardYear & _
"(ID,kjqj,pzzl,pzbh,jlhm,pzrq,kmdm,fx,je,wb,xgbz) " & _
"VALUES('" & Format(iID, "00000000") & "', 20,'银','0000'," & iMaxJlhm & ",'" & _
Format(sCarryForwardYear & "-01-01", "yyyy-mm-dd") & _
"','" & sKmdm(iKmNum) & "','借'," & _
CStr(dDwtzqye) + "," + CStr(dDwtzqwb) & ",2)"
Else
adoCmd.CommandText = "INSERT INTO tZW_pzsj" & sCarryForwardYear & _
"(ID,kjqj,pzzl,pzbh,jlhm,pzrq,kmdm,fx,je,wb,xgbz) " & _
"VALUES('" & Format(iID, "00000000") & "', 20,'银','0000'," & iMaxJlhm & ",'" & _
Format(sCarryForwardYear & "-01-01", "yyyy-mm-dd") & _
"','" & sKmdm(iKmNum) & "','借'," & _
CStr(dDwtzqwb) + "," + CStr(dDwtzqye) & ",2)"
End If
Case "ORACLE"
If GetKmWbdw(sKmdm(iKmNum)) = "" Then
adoCmd.CommandText = "INSERT INTO tZW_pzsj" & sCarryForwardYear & _
"(ID,kjqj,pzzl,pzbh,jlhm,pzrq,kmdm,fx,je,wb,xgbz) " & _
"VALUES('" & Format(iID, "00000000") & "',20,'银','0000'," & iMaxJlhm & ",TO_DATE('" & _
sCarryForwardYear & "-01-01','YYYY-MM-DD'),'" & _
sKmdm(iKmNum) & "','借'," & _
CStr(dDwtzqye) + "," + CStr(dDwtzqwb) & ",2)"
Else
adoCmd.CommandText = "INSERT INTO tZW_pzsj" & sCarryForwardYear & _
"(ID,kjqj,pzzl,pzbh,jlhm,pzrq,kmdm,fx,je,wb,xgbz) " & _
"VALUES('" & Format(iID, "00000000") & "',20,'银','0000'," & iMaxJlhm & ",TO_DATE('" & _
sCarryForwardYear & "-01-01','YYYY-MM-DD'),'" & _
sKmdm(iKmNum) & "','借'," & _
CStr(dDwtzqwb) + "," + CStr(dDwtzqye) & ",2)"
End If
End Select
adoCmd.Execute
End If
End If
End With
Next iKmNum
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -