frmselectperiod.frm

一个用VB写的财务软件源码

VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "msflxgrd.ocx"
Begin VB.Form frmSelectPeriod 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "设置会计期间"
   ClientHeight    =   3180
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   5820
   ControlBox      =   0   'False
   LinkTopic       =   "Form1"
   LockControls    =   -1  'True
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3180
   ScaleWidth      =   5820
   StartUpPosition =   2  '屏幕中心
   Begin VB.CommandButton cmdHelp 
      Caption         =   "帮助(&H)"
      Height          =   345
      Left            =   4650
      TabIndex        =   4
      Top             =   2640
      Width           =   1065
   End
   Begin VB.CommandButton cmdFinish 
      Caption         =   "完成(&F)"
      Default         =   -1  'True
      Height          =   345
      Left            =   4650
      TabIndex        =   3
      Top             =   2010
      Width           =   1065
   End
   Begin VB.ComboBox cboMonthEnd 
      Height          =   300
      Left            =   4650
      Style           =   2  'Dropdown List
      TabIndex        =   1
      Top             =   420
      Width           =   1005
   End
   Begin MSFlexGridLib.MSFlexGrid mfgPeriod 
      Height          =   3045
      Left            =   60
      TabIndex        =   0
      Top             =   90
      Width           =   4455
      _ExtentX        =   7858
      _ExtentY        =   5371
      _Version        =   393216
      WordWrap        =   -1  'True
      AllowUserResizing=   1
   End
   Begin VB.Label Label17 
      Caption         =   "月末结算日："
      Height          =   180
      Left            =   4650
      TabIndex        =   2
      Top             =   120
      Width           =   1080
   End
End
Attribute VB_Name = "frmSelectPeriod"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public usAccountID As String           '账套号
Public usNewYear As String             '新建年度账的年份


Private Sub cmdFinish_Click()
    Call AddPeriod
    Unload Me
End Sub

Private Sub Form_Load()
    Dim i As Integer

    '设置会计期间表格
    With mfgPeriod
        .Rows = 13
        .Cols = 3
        .FormatString = "期号|<起始日期|<截止日期"
        .ColAlignment(0) = 4
        .ColWidth(0) = 600
        .ColWidth(1) = 1200
        .ColWidth(2) = 1200
    End With
    
    '装入月末结算日
    cboMonthEnd.Clear
    For i = 21 To 31
        cboMonthEnd.AddItem CStr(i)
    Next i
    cboMonthEnd.ListIndex = cboMonthEnd.ListCount - 1
    

End Sub


Private Sub cboMonthEnd_Click()
    Dim sBegin As String, sEnd As String
    Dim i As Integer
            
    mfgPeriod.Redraw = False
    For i = 1 To 12
        mfgPeriod.TextMatrix(i, 0) = i
        Call GetPeriod(i, Val(cboMonthEnd.text), sBegin, sEnd)
        mfgPeriod.TextMatrix(i, 1) = sBegin
        mfgPeriod.TextMatrix(i, 2) = sEnd
    Next i
    mfgPeriod.TextMatrix(1, 1) = usNewYear & "-01-01"
    mfgPeriod.TextMatrix(12, 2) = usNewYear & "-12-31"
    mfgPeriod.Redraw = True

End Sub

Private Sub AddPeriod()
    Dim adoCmd As ADODB.Command
    Dim rstTemp As ADODB.Recordset
    Dim i As Integer
    Dim sSql As String
    
    Set adoCmd = New ADODB.Command
    With adoCmd
        .CommandType = adCmdText
        .ActiveConnection = gloSys.cnnSys
        .CommandText = "DELETE FROM tSYS_Period" & _
                        " WHERE AccountID = '" & usAccountID & _
                        "' AND Year = '" & usNewYear & "'"
        .Execute
    End With

    Set rstTemp = New ADODB.Recordset
    rstTemp.CursorLocation = adUseClient
    
    '1:添加该账套的 tSYS_Period（会计期）记录
    sSql = "select * from tSYS_Period"
    With rstTemp
        .CursorLocation = adUseClient
        .Open sSql, gloSys.cnnSys, adOpenStatic, adLockOptimistic
        For i = 1 To 12
            .AddNew
            .Fields("AccountID").Value = Trim$(usAccountID)
            .Fields("Year").Value = usNewYear
            .Fields("periodID").Value = i
            .Fields("fromDate").Value = mfgPeriod.TextMatrix(i, 1)
            .Fields("toDate").Value = mfgPeriod.TextMatrix(i, 2)
            .Update
        Next i
        .Close
    End With
End Sub


'根据月份和月末结算日确定一个业务期的起止日期。
Private Sub GetPeriod(ByVal Month As Integer, ByVal EndDay As Integer, _
                ByRef sBegin As String, ByRef sEnd As String)
    
    '注：月末结算日的取值在21－31之间。
    
    Dim PreMonth As Integer
    Dim PreEndDay As Integer
    Dim FebEnd As Integer    '二月份的最后一天。
    
    '判断启用年度是否闰年。
    If IsDate(usNewYear & "-02-29") Then
        FebEnd = 29
    Else
        FebEnd = 28
    End If
    
    '求上一月份。
    If Month = 1 Then
        PreMonth = 12
    Else
        PreMonth = Month - 1
    End If
    '上一个月的起始日期是汇总日期＋1。
    PreEndDay = EndDay + 1
    
    Select Case PreMonth
        Case 2
            If PreEndDay > FebEnd Then
                PreMonth = 3
                PreEndDay = 1
            End If
        Case 12
            If PreEndDay > 31 Then
                PreMonth = 1
                PreEndDay = 1
            End If
        Case 1, 3, 5, 7, 8, 10
            If PreEndDay > 31 Then
                PreMonth = PreMonth + 1
                PreEndDay = 1
            End If
        Case 4, 6, 9, 11
            If PreEndDay > 30 Then
                PreMonth = PreMonth + 1
                PreEndDay = 1
            End If
    End Select
    
    Select Case Month
        Case 2
            If EndDay > FebEnd Then
                EndDay = FebEnd
            End If
        Case 1, 3, 5, 7, 8, 10, 12
            If EndDay > 31 Then
                EndDay = 31
            End If
        Case 4, 6, 9, 11
            If EndDay > 30 Then
                EndDay = 30
            End If
    End Select
    
    If EndDay < 31 And Month = 1 Then
        sBegin = CStr(Val(usNewYear) - 1) & "-" & _
            Format(PreMonth, "00") & "-" & Format(PreEndDay, "00")
    Else
        sBegin = usNewYear & "-" & _
            Format(PreMonth, "00") & "-" & Format(PreEndDay, "00")
    End If
    
    sEnd = usNewYear & "-" & Format(Month, "00") & "-" & Format(EndDay, "00")
    
End Sub