frmcreatedatabasenewyear.frm

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

    Dim Script As clsScript
    Dim CreateDB As clsCreateDB
    Set CreateDB = New clsCreateDB
    CreateDB.cnnVirtual = cnnORA
    CreateDB.sYear = sBeginYear
    
    If g_FLAT = "SQL" Then
       sPath = App.Path & "\DB\SQL\"
    Else
       sPath = App.Path & "\DB\ORACLE\"
    End If
    
    '创建年表
    '1
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "BB.sql"
    CreateDB.Append Script

    '2
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "CF.sql"
    CreateDB.Append Script

    '3
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "FX.sql"
    CreateDB.Append Script

    
    '4
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "FZ.sql"
    CreateDB.Append Script
    
    '5
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "GD.sql"
    CreateDB.Append Script
    
    '6
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "GZ.sql"
    CreateDB.Append Script
    
    '7
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "RS.sql"
    CreateDB.Append Script
    
    '8
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "SF.sql"
    CreateDB.Append Script
    
    '9
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "TY.sql"
    CreateDB.Append Script
    
    '10
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "ZW.sql"
    CreateDB.Append Script
    
    '11
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "MR.sql"
    CreateDB.Append Script
    
    '12
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "RA.sql"
    CreateDB.Append Script

   Call CreateDB.DoExecute(True, False)  '年表 insert into 语句无效
    Set CreateDB = Nothing
    Set Script = Nothing
End Sub


'创建新年度的表
Private Sub CreateDatabaseNewYear(ByRef bOK As Boolean)

    Dim adoCnnAccount As ADODB.Connection
    Dim adoCmd As ADODB.Command
    Dim sSql() As String
    Dim bExist As Boolean                    '判断需要建的年度账是否存在
    Dim i As Integer
    
'    On Error Resume Next
    
    bOK = False
    bExist = False
    ReDim sSql(72)
    
    
    '判断新年度账是否已经建过;
    '如果建过, 则询问是否重建;
    adoSQL = "SELECT MAX(year) maxYear FROM tSYS_Period" & _
            " WHERE AccountID = '" & usAccountID & "'"
    With adoRst
        .Open adoSQL, gloSys.cnnSys, adOpenStatic, adLockReadOnly
        If .BOF Then
            .Close
            Exit Sub
        ElseIf IsNull(.Fields("maxYear").Value) Then
            .Close
            Exit Sub
        Else
            If sNewYear = Trim(.Fields("maxYear").Value) Then
                If MsgBox(usAccountName & "账套" & sNewYear & "年度账已经建立，是否重建？", _
                    vbQuestion + vbYesNo + vbDefaultButton1) = vbNo Then
                    .Close
                    Exit Sub
                Else
                    bExist = True
                End If
            End If
            .Close
        End If
    End With
    
    Set adoCnnAccount = New ADODB.Connection

    Select Case g_FLAT
        Case "SQL"
            adoCnnAccount.Open GetConnectString(g_FLAT, gloSys.sServer, _
                gloSys.sUser, s.decrypt(gloSys.sPassword), "cwDB" & usAccountID)
        Case "ORACLE"
            adoCnnAccount.CommandTimeout = 300
            adoCnnAccount.CursorLocation = adUseClient
            adoCnnAccount.Open GetConnectString(g_FLAT, gloSys.sServer, _
                "cwDB" & usAccountID, "ykcwDB" & usAccountID)
    End Select
    Dim sPath As String
    Dim Script As clsScript
    Dim CreateDB As clsCreateDB

    If g_FLAT = "SQL" Then
       sPath = App.Path & "\DB\SQL\"
    Else
       sPath = App.Path & "\DB\ORACLE\"
    End If
    '创建一个账套库里的年表
'    If bExist = True And g_FLAT = "ORACLE" Then
        '删除年表
        Set CreateDB = New clsCreateDB
        CreateDB.cnnVirtual = adoCnnAccount
        CreateDB.sYear = sNewYear
        Set Script = New clsScript
        Script.usFileType = "Sql"
        Script.usScriptFilename = sPath & "DropNB.sql"
        CreateDB.Append Script
        Call CreateDB.DoExecute
        Set CreateDB = Nothing
        Set Script = Nothing
'    End If
    '建年度表
    Call CreateYearTables(adoCnnAccount, sNewYear)
    '转年度数据
    Set CreateDB = New clsCreateDB
    CreateDB.cnnVirtual = adoCnnAccount
    CreateDB.sYear = sNewYear
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "NB.sql"
    CreateDB.Append Script
    Call CreateDB.DoExecute(True, True) '插入语句有效
    Set CreateDB = Nothing
    Set Script = Nothing
'''1
'''会计科目表
''     sSql(1) = "CREATE TABLE tZW_km" & sNewYear & "(" & _
''        "    kmdm      varchar(40)   NOT NULL PRIMARY KEY," & _
''        "    kmmc      varchar(40)   NULL,          kmmcEng   varchar(80)   NULL,       zjm       varchar(4)    NULL,       kmlx      varchar(20)   NOT NULL," & _
''        "    kmjc      smallint      NOT NULL,      IsEndkm   smallint      default -1," & _
''        "    yefx      char(4)       NOT NULL,      zygs      varchar(10)   default '三栏式'," & _
''        "    hzdykm    varchar(18)   Null,          sldw      varchar(10)   NULL,       wbdw      varchar(10)   NULL  ," & _
''        "    IsRjz     smallint      default 0,     IsYhz     smallint      default 0,  IsGrwlhs  smallint      default 0,  IsKhwlhs  smallint      default 0," & _
''        "    IsGyswlhs smallint      default 0,     IsBmhs    smallint      default 0,  IsXmhs    smallint      default 0,  IsXjllkm  smallint      default 0," & _
''        "    Xjlllb    varchar(10)   NULL,          JfKze     decimal(15,2) NULL,       DfKze     decimal(15,2) NULL," & _
''        "    YeKze     decimal(15,2) NULL,          kmqx      smallint      default 0," & _
''        "    CwfxLx    varchar(20)   NULL,          IsFc      smallint      default 0," & _
''        "    bUse      smallint      default 0,     bAdd      smallint      default -1, cLawless  varchar(255)  NULL)"
''
''  '科目余额表
''   sSql(2) = "CREATE TABLE tZW_Balance" & sNewYear & "(" & _
''        "    kmdm      varchar(40)   NOT NULL ," & _
''        "    kmmc      varchar(40)   NOT NULL , " & _
''        "    yefx      char(4)       NOT NULL , " & _
''        "    bz        varchar(10)   default '人民币' NOT NULL , " & _
''        "    ljjsl00   decimal(15, 3) default 0 not null,ljjwb00   decimal(15, 2)  default 0 not null,ljj00     decimal(15, 2)  default 0 not null,ljdsl00   decimal(15, 3)  default 0 not null,ljdwb00   decimal(15, 2)  default 0 not null, ljd00     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl01   decimal(15, 3) default 0 not null,ljjwb01   decimal(15, 2)  default 0 not null,ljj01     decimal(15, 2)  default 0 not null,ljdsl01   decimal(15, 3)  default 0 not null,ljdwb01   decimal(15, 2)  default 0 not null, ljd01     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl02   decimal(15, 3) default 0 not null,ljjwb02   decimal(15, 2)  default 0 not null,ljj02     decimal(15, 2)  default 0 not null,ljdsl02   decimal(15, 3)  default 0 not null,ljdwb02   decimal(15, 2)  default 0 not null, ljd02     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl03   decimal(15, 3) default 0 not null,ljjwb03   decimal(15, 2)  default 0 not null,ljj03     decimal(15, 2)  default 0 not null,ljdsl03   decimal(15, 3)  default 0 not null,ljdwb03   decimal(15, 2)  default 0 not null, ljd03     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl04   decimal(15, 3) default 0 not null,ljjwb04   decimal(15, 2)  default 0 not null,ljj04     decimal(15, 2)  default 0 not null,ljdsl04   decimal(15, 3)  default 0 not null,ljdwb04   decimal(15, 2)  default 0 not null, ljd04     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl05   decimal(15, 3) default 0 not null,ljjwb05   decimal(15, 2)  default 0 not null,ljj05     decimal(15, 2)  default 0 not null,ljdsl05   decimal(15, 3)  default 0 not null,ljdwb05   decimal(15, 2)  default 0 not null, ljd05     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl06   decimal(15, 3) default 0 not null,ljjwb06   decimal(15, 2)  default 0 not null,ljj06     decimal(15, 2)  default 0 not null,ljdsl06   decimal(15, 3)  default 0 not null,ljdwb06   decimal(15, 2)  default 0 not null, ljd06     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl07   decimal(15, 3) default 0 not null,ljjwb07   decimal(15, 2)  default 0 not null,ljj07     decimal(15, 2)  default 0 not null,ljdsl07   decimal(15, 3)  default 0 not null,ljdwb07   decimal(15, 2)  default 0 not null, ljd07     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl08   decimal(15, 3) default 0 not null,ljjwb08   decimal(15, 2)  default 0 not null,ljj08     decimal(15, 2)  default 0 not null,ljdsl08   decimal(15, 3)  default 0 not null,ljdwb08   decimal(15, 2)  default 0 not null, ljd08     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl09   decimal(15, 3) default 0 not null,ljjwb09   decimal(15, 2)  default 0 not null,ljj09     decimal(15, 2)  default 0 not null,ljdsl09   decimal(15, 3)  default 0 not null,ljdwb09   decimal(15, 2)  default 0 not null, ljd09     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl10   decimal(15, 3) default 0 not null,ljjwb10   decimal(15, 2)  default 0 not null,ljj10     decimal(15, 2)  default 0 not null,ljdsl10   decimal(15, 3)  default 0 not null,ljdwb10   decimal(15, 2)  default 0 not null, ljd10     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl11   decimal(15, 3) default 0 not null,ljjwb11   decimal(15, 2)  default 0 not null,ljj11     decimal(15, 2)  default 0 not null,ljdsl11   decimal(15, 3)  default 0 not null,ljdwb11   decimal(15, 2)  default 0 not null, ljd11     decimal(15, 2)  default 0 not null," & _
''        "    ljjsl12   decimal(15, 3) default 0 not null,ljjwb12   decimal(15, 2)  default 0 not null,ljj12     decimal(15, 2)  default 0 not null,ljdsl12   decimal(15, 3)  default 0 not null,ljdwb12   decimal(15, 2)  default 0 not null, ljd12     decimal(15, 2)  default 0 not null)"
''
'''2