frmscbb1.frm

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

VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form frmscbb1 
   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 = "frmscbb1"
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 '定义本期初所在年度
'Dim Qx1(), Qx3(), Qx6() As String  '定义期限
Dim Qx1(), Qx2(), Qx3(), Qx6(), Qx7() As Double
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)
ReDim Qx1(Size), Qx3(Size), Qx6(Size), Qx2(Size), Qx7(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)
Qcrq2 = Format(frmcdsqr1.DTPicker1.Value, "yyyy-mm-dd") '本期初日期
Qcrq1 = Format(DateSerial(Year(Qcrq2), 1, 1), "yyyy-mm-dd") '年初日期
Qcrq3 = Format(frmcdsqr1.DTPicker2.Value, "yyyy-mm-dd") '本期未日期
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 Double
Pro = 9 / Size
For i = 1 To Size
If Me.ProgressBar1.Value = 100 Then
GoTo ed
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed:
.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 ed1
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed1:
.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 '求期余额
'开始求本期新增额
If Me.ProgressBar1.Value = 100 Then
GoTo ed2
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed2:
.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 ed3
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed3:
.RecordSource = "select 申请经办人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 申请经办人=" & "'" & Cdsqr(i) & "'" & " and 期限=" & "'" & "一个月" & "'" & " group by 申请经办人"
.Refresh
If .Recordset.RecordCount = 1 Then
Qx1(i) = .Recordset.Fields(1).Value '本期新增额
ElseIf .Recordset.RecordCount = 0 Then
Qx1(i) = 0
End If
'开始求本期期限为二个月的新增额
If Me.ProgressBar1.Value = 100 Then
GoTo ed4
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed4:
.RecordSource = "select 申请经办人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 申请经办人=" & "'" & Cdsqr(i) & "'" & " and 期限=" & "'" & "二个月" & "'" & " group by 申请经办人"
.Refresh
If .Recordset.RecordCount = 1 Then
Qx2(i) = .Recordset.Fields(1).Value '本期新增额
ElseIf .Recordset.RecordCount = 0 Then
Qx2(i) = 0
End If
'开始求本期期限为三个月的新增额
If Me.ProgressBar1.Value = 100 Then
GoTo ed5
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed5:
.RecordSource = "select 申请经办人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 申请经办人=" & "'" & Cdsqr(i) & "'" & " and 期限=" & "'" & "三个月" & "'" & " group by 申请经办人"
.Refresh
If .Recordset.RecordCount = 1 Then
Qx3(i) = .Recordset.Fields(1).Value '本期新增额
ElseIf .Recordset.RecordCount = 0 Then
Qx3(i) = 0
End If
'开始求本期期限为六个月的新增额
If Me.ProgressBar1.Value = 100 Then
GoTo ed6
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed6:
.RecordSource = "select 申请经办人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 申请经办人=" & "'" & Cdsqr(i) & "'" & " and 期限=" & "'" & "六个月" & "'" & " group by 申请经办人"
.Refresh
If .Recordset.RecordCount = 1 Then
Qx6(i) = .Recordset.Fields(1).Value '本期新增额
ElseIf .Recordset.RecordCount = 0 Then
Qx6(i) = 0
End If

'开始求本期期限为其它的新增额
If Me.ProgressBar1.Value = 100 Then
GoTo ed7
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed7:
.RecordSource = "select 申请经办人,sum(承兑金额) as 新增金额 from 台帐数据 where 出票日期 between#" & Qcrq2 & "#and#" & Qcrq3 & "#" & " and 申请经办人=" & "'" & Cdsqr(i) & "'" & " and 期限=" & "'" & "其它" & "'" & " group by 申请经办人"
.Refresh
If .Recordset.RecordCount = 1 Then
Qx7(i) = .Recordset.Fields(1).Value '本期新增额
ElseIf .Recordset.RecordCount = 0 Then
Qx7(i) = 0
End If
'开始求本期到期额
If Me.ProgressBar1.Value = 100 Then
GoTo ed8
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed8:
.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 ed9
End If
Me.ProgressBar1.Value = Me.ProgressBar1.Value + Pro
ed9:
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.Fields(6).Value = Qx1(i)
.Recordset.Fields(7).Value = Qx3(i)
.Recordset.Fields(8).Value = Qx6(i)
.Recordset.Fields(9).Value = Qx2(i)
.Recordset.Fields(10).Value = Qx7(i)
.Recordset.Update
Next i
End With
Me.ProgressBar1.Value = 100
Timer1.Enabled = False
Unload Me
Unload frmcdsqr1
frmshcheng1.Label1.Caption = "申请经办人报告表已经生成(" & Qcrq2 & "至" & Qcrq3 & ")，请选择下一步要进行的操作。"
frmshcheng1.Show 1
End Sub