frmscbb.frm

金融机构用于银行承兑票台帐的登记以及统计打印各种报表

VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form frmscbb 
   BorderStyle     =   3  'Fixed Dialog
   ClientHeight    =   1050
   ClientLeft      =   5610
   ClientTop       =   5580
   ClientWidth     =   5685
   ControlBox      =   0   'False
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   1050
   ScaleWidth      =   5685
   ShowInTaskbar   =   0   'False
   Begin VB.Timer Timer1 
      Enabled         =   0   'False
      Interval        =   1
      Left            =   4080
      Top             =   240
   End
   Begin MSComctlLib.ProgressBar ProgressBar1 
      Height          =   255
      Left            =   195
      TabIndex        =   0
      Top             =   600
      Width           =   5295
      _ExtentX        =   9340
      _ExtentY        =   450
      _Version        =   393216
      Appearance      =   1
   End
   Begin VB.Label Label1 
      Caption         =   "正在生成承兑申请人报告表…………"
      Height          =   255
      Left            =   240
      TabIndex        =   1
      Top             =   240
      Width           =   3495
   End
End
Attribute VB_Name = "frmscbb"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Base 1
Dim Cdsqr() As String '定义承兑申请人
Dim Cd1(), Cd2(), Cd3(), Cd4(), Cd5(), Cd6(), Cd7() As Double   '定义期初额、期到期额、期新增额、期余额（期指年初至到期日期）\本期到期额、本期新增额、本期初额
Dim Size As Integer '定义承兑申请人数量
Dim Qcrq1, Qcrq2, Qcrq3 As String '定义年初日期、本期初日期、本期未日期
Dim Niandu As Integer '定义本期初所在年度

Private Sub Form_Activate()
Timer1.Enabled = True
End Sub

Private Sub Form_Click()
Unload Me
End Sub

Private Sub Timer1_Timer()
With ycdck.Adodc1
.RecordSource = "select 承兑申请人 from 承兑申请人"
.Refresh
Size = .Recordset.RecordCount
If Size = 0 Then
MsgBox "还未进行任何承兑申请人设置，报表无法生成", vbOKOnly Or vbInformation, "系统信息"
Unload Me
Exit Sub
End If
ReDim Cdsqr(Size)
For i = 1 To Size   '求承兑申请人
Cdsqr(i) = .Recordset.Fields(0).Value
.Recordset.MoveNext
Next i
ReDim Cd1(Size), Cd2(Size), Cd3(Size), Cd4(Size), Cd5(Size), Cd6(Size), Cd7(Size) As Double
Qcrq3 = Format(frmcdsqr.DTPicker2.Value, "yyyy-m-d") '本期未日期
Qcrq2 = Format(frmcdsqr.DTPicker1.Value, "yyyy-m-d") '本期初日期
Qcrq1 = Format(DateSerial(Year(Qcrq2), 1, 1), "yyyy-m-d") '年初日期
Niandu = Val(Qcrq2)
For i = 1 To Size '求每个承兑申请人的年初余额
.RecordSource = "select * from 承兑申请人年初余额 where 承兑申请人=" & "'" & Cdsqr(i) & "'" & " and 年度=" & Niandu
.Refresh
If .Recordset.RecordCount = 1 Then
Cd1(i) = .Recordset.Fields(3).Value
ElseIf .Recordset.RecordCount = 0 Then
Cd1(i) = 0
End If
Next i
Me.ProgressBar1.Value = 0
Dim Pro As Integer
Pro = 17 / Size

For i = 1 To Size
If Me.ProgressBar1.Value = 100 Then
GoTo Ee
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
Ee:
.RecordSource = "select 承兑申请人,sum(承兑金额) as 到期金额 from 台帐数据 where 到期日期 between#" & Qcrq1 & "#and#" & Qcrq3 & "#" & " and 承兑申请人=" & "'" & Cdsqr(i) & "'" & " group by 承兑申请人"
.Refresh
If .Recordset.RecordCount = 1 Then
Cd2(i) = .Recordset.Fields(1).Value  '年初到本期未到期额
ElseIf .Recordset.RecordCount = 0 Then
Cd2(i) = 0
End If
If Me.ProgressBar1.Value = 100 Then
GoTo EE1
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
EE1:
.RecordSource = "select 承兑申请人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq1 & "#and#" & Qcrq3 & "#" & " and 承兑申请人=" & "'" & Cdsqr(i) & "'" & " group by 承兑申请人"
.Refresh
If .Recordset.RecordCount = 1 Then
Cd3(i) = .Recordset.Fields(1).Value '年初到本期末新增额
ElseIf .Recordset.RecordCount = 0 Then
Cd3(i) = 0
End If
Dim a0 As Double
Dim a As Currency
Dim b As Double
a0 = Cd1(i) + Cd3(i) - Cd2(i)
a0 = a0 * 100
a = CCur(a0)
b = CDbl(a)
b = b / 100
Cd4(i) = b  '求期余额
'Cd4(i) = Cd1(i) + Cd3(i) - Cd2(i)
'开始求本期新增额
If Me.ProgressBar1.Value = 100 Then
GoTo EE2
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
EE2:
.RecordSource = "select 承兑申请人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 承兑申请人=" & "'" & Cdsqr(i) & "'" & " group by 承兑申请人"
.Refresh
If .Recordset.RecordCount = 1 Then
Cd6(i) = .Recordset.Fields(1).Value '年初到本期末新增额
ElseIf .Recordset.RecordCount = 0 Then
Cd6(i) = 0
End If
'开始求本期到期额
If Me.ProgressBar1.Value = 100 Then
GoTo EE3
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
EE3:
.RecordSource = "select 承兑申请人,sum(承兑金额) as 到期金额 from 台帐数据 where 到期日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 承兑申请人=" & "'" & Cdsqr(i) & "'" & " group by 承兑申请人"
.Refresh
If .Recordset.RecordCount = 1 Then
Cd5(i) = .Recordset.Fields(1).Value '年初到本期末新增额
ElseIf .Recordset.RecordCount = 0 Then
Cd5(i) = 0
End If
'开始求本期初额
If Me.ProgressBar1.Value = 100 Then
GoTo EE4
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
EE4:
a0 = Cd4(i) + Cd5(i) - Cd6(i)
a0 = a0 * 100
a = CCur(a0)
b = CDbl(a)
b = b / 100
Cd7(i) = b
'Cd7(i) = Cd4(i) + Cd5(i) - Cd6(i)
'将数据加入到数据库
.RecordSource = "select * from 承兑申请人报告表"
.Refresh
.Recordset.AddNew
.Recordset.Fields(1).Value = Cdsqr(i)
.Recordset.Fields(2).Value = Cd7(i)
.Recordset.Fields(3).Value = Cd6(i)
.Recordset.Fields(4).Value = Cd5(i)
.Recordset.Fields(5).Value = Cd4(i)
.Recordset.Update
Next i
End With
Me.ProgressBar1.Value = 100
Timer1.Enabled = False
Unload Me
Unload frmcdsqr
frmshcheng.Label1.Caption = "承兑申请人报告表已经生成(" & Qcrq2 & "至" & Qcrq3 & ")，请选择下一步要进行的操作。"
frmshcheng.Show 1
End Sub