mdlinitialize.bas

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

Attribute VB_Name = "mdlInitialize"
Option Explicit
Public Declare Function GetFileVersionInfo Lib "version.dll" Alias "GetFileVersionInfoA" (ByVal lptstrFilename As String, ByVal dwHandle As Long, ByVal dwLen As Long, lpData As Any) As Long
Public Declare Function GetFileVersionInfoSize Lib "version.dll" Alias "GetFileVersionInfoSizeA" (ByVal lptstrFilename As String, lpdwHandle As Long) As Long
Option Base 1
'安装演示账套
Public Sub CreateDemoDataBase(cnnCreate As ADODB.Connection)
    Dim adoCmd As ADODB.Command
    Dim lVersion As Long
    Dim abytBuffer() As Byte
    Dim lngBufferLen As Long
    Dim lngDummy As Long

    Set adoCmd = New ADODB.Command
    adoCmd.ActiveConnection = cnnCreate
    adoCmd.CommandType = adCmdText
'    lngBufferLen = GetFileVersionInfoSize(App.Path & "\DemoSetup.exe", lngDummy)
'    If lngBufferLen < 1 Then
'         Exit Sub
'    End If
'
'      ' Set up the byte array
'    ReDim abytBuffer(lngBufferLen)
'
'    lVersion = GetFileVersionInfo(App.Path & "\DemoSetup.exe", 0&, lngBufferLen, abytBuffer)
    Select Case g_FLAT
        Case "SQL"
            Shell App.Path & "\DemoSetup.exe So6fI_R7v6:6", vbNormalFocus
        Case "ORACLE"
            With adoCmd
                .CommandTimeout = 300
                .CommandText = "CREATE TABLESPACE CW_ts" & _
                                " DATAFILE 'CW_ts_1.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED," & _
                                " 'CW_ts_2.dat' SIZE 10M REUSE  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED" & _
                                " DEFAULT STORAGE (PCTINCREASE 10)"
                .Execute
                .CommandText = "CREATE TABLESPACE CW_tsTmp" & _
                               " DATAFILE 'CW_tsTmp.dat' SIZE 10M  REUSE AUTOEXTEND ON NEXT 5M " & _
                                " DEFAULT STORAGE (PCTINCREASE 10)  TEMPORARY"
                .Execute
            End With
            Shell App.Path & "\DemoSetup.exe Oo6fI_R7v6:6", vbNormalFocus
        Case Else
            Err.Raise 5
    End Select
    Set adoCmd = Nothing
End Sub
'cnnCreate:        Oracle 平台上的新建用户的数据环境
Public Sub CreateSystemDataBase(cnnCreate As ADODB.Connection)
    Dim adoCmd As ADODB.Command
    Dim sSql() As String, i As Long, sValues() As String
    
    glo.frmProg.SetMsg "正在创建初始化环境..."
    glo.frmProg.ShowProgress 5
    glo.frmProg.Show
    
'一、建立系统数据库
    Set adoCmd = New ADODB.Command
    adoCmd.ActiveConnection = cnnCreate
    adoCmd.CommandType = adCmdText
    glo.frmProg.ShowProgress 10
    glo.frmProg.SetMsg "正在创建系统库..."
    
    Select Case g_FLAT
        Case "SQL"
            '1:打开一个虚数据环境（不指向任何数据库）
            
            '2:新建'YkcwSysDB'库
            adoCmd.CommandText = "CREATE DATABASE " & g_SYSDBNAME
            adoCmd.Execute
            
        Case "ORACLE"
            '2:新建'YkcwSysDb'用户(密码为"horse"，授予 DBA 角色)
            With adoCmd
                .CommandTimeout = 300
                .CommandText = "CREATE USER " & g_SYSDBNAME & " IDENTIFIED BY " & s.decrypt(gloSys.sPassword)
                .Execute
                .CommandText = "ALTER USER " & g_SYSDBNAME & " IDENTIFIED BY " & g_SYSPASSWORD
                .Execute
                .CommandText = "GRANT DBA TO " & g_SYSDBNAME
                .Execute
                .CommandText = "CREATE TABLESPACE CW_ts" & _
                                " DATAFILE 'CW_ts_1.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED," & _
                                " 'CW_ts_2.dat' SIZE 10M REUSE  AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED" & _
                                " DEFAULT STORAGE (PCTINCREASE 10)"
                .Execute
                .CommandText = "CREATE TABLESPACE CW_tsTmp" & _
                               " DATAFILE 'CW_tsTmp.dat' SIZE 10M  REUSE AUTOEXTEND ON NEXT 5M" & _
                                " DEFAULT STORAGE (PCTINCREASE 10)  TEMPORARY"
                .Execute
                
            End With
            
        Case Else
            Err.Raise 5
    End Select
    
    Set adoCmd = Nothing
    
    
'二、建立所有表
    glo.frmProg.SetMsg "正在创建表..."
    glo.frmProg.ShowProgress 15
    
    '1:打开新建的数据环境('YkcwSysDB')
    cnnCreate.Close
    Select Case g_FLAT
        Case "SQL"
            cnnCreate.Open GetConnectString(g_FLAT, gloSys.sServer, _
                gloSys.sUser, s.decrypt(gloSys.sPassword), g_SYSDBNAME)
        Case "ORACLE"
            cnnCreate.Open GetConnectString(g_FLAT, gloSys.sServer, _
                g_SYSDBNAME, g_SYSPASSWORD)
    End Select
     
    '2:创建表
    Dim sPath As String
    Dim Script As clsScript
    Dim CreateDB As clsCreateDB
    Set CreateDB = New clsCreateDB
    CreateDB.cnnVirtual = cnnCreate
'    CreateDB.sYear = sBeginYear
    
    If g_FLAT = "SQL" Then
       sPath = App.Path & "\DB\SQL\"
    Else
       sPath = App.Path & "\DB\ORACLE\"
    End If
    
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "XT.sql"
    CreateDB.Append Script
    CreateDB.DoExecute
    Set CreateDB = Nothing
    Set Script = Nothing
''    ReDim sSql(27)
'''1
''    sSql(1) = "/*系统账套管理表*/" & _
''"CREATE TABLE tSYS_Account(" & _
''"    AccountID          varchar(4)          NOT NULL primary key,   /*账套号*/" & _
''"    AccountName        varchar(20)         NOT NULL,               /*账套名称*/" & _
''"    BeginYear          char(4)             NOT NULL,               /*启用年份*/" & _
''"    BeginMonth         char(2)             NOT NULL,               /*启用月份*/" & _
''"    Master             varchar(8)          NULL,                   /*账套主管*/" & _
''"    CurrencyName       varchar(10)         NULL,                   /*本位币名称*/" & _
''"    EnterName          varchar(60)         NOT NULL,               /*单位名称*/" & _
''"    TelCode            varchar(20)         NULL,                   /*电话号码*/" & _
''"    Address            varchar(80)         NULL,                   /*单位地址*/" & _
''"    Zip                char(6)             NULL,                   /*邮政编码*/" & _
''"    EMail              varchar(40)         NULL,                   /*EMail*/" & _
''"    TaxNo              varchar(20)         NULL,                   /*税号*/" & _
''"    Law                varchar(20)         NULL,                   /*法人*/" & _
''"    EconomyProperty    varchar(10)         NULL,                   /*经济性质*/" & _
''"    EnterType          varchar(10)         NULL,                   /*企业类别*/" & _
''"    TradeID            smallint            NOT NULL,               /*行业编号*/" & _
''"    VoucherPrintMode   varchar(8)          NOT NULL,               /*凭证打印方式*/" & _
''"    VoucherNumberMode  smallint            DEFAULT -1,             /*凭证编号方式*/" & _
''"    SubjectOnFront     smallint            DEFAULT 0,              /*科目是否在摘要前*/" & _
''"    SeparateChar       varchar(1)          DEFAULT 0,              /*0为定长,其它都为分隔符*/" & _
''"    SeparateSubject    varchar(1)          DEFAULT 0               /*在定长情况下 ：0为不分隔,1代表分隔 */ " & _
''")"
''
'''2
''    sSql(2) = _
''"/*子系统一览表*/" & _
''"CREATE TABLE tSYS_SubSys(" & _
''"    SubSysID           varchar(2)        NOT NULL primary key,     /*子系统代码*/" & _
''"    SubSysName         varchar(20)       NULL                      /*子系统名称*/" & _
''")"
'''3
''    sSql(3) = _
''"/*子系统启用表*/" & _
''"CREATE TABLE tSYS_SubSysUsed(" & _
''"    AccountID     varchar(4)        NOT NULL," & _
''"    SubSysID      varchar(2)        NOT NULL," & _
''"    BeginYear     char(4)           NOT NULL," & _
''"    BeginMonth    smallint          NOT NULL," & _
''"    ModiYear      char(4)           NULL,              /*已结账到的年*/" & _
''"    ModiMonth     smallint          NULL               /*已结账到的月*/" & _
''")"
''
''    '“子系统启用表”的主键'PK_SYS_SubSysUsed'
''    Select Case g_FLAT
''        Case "SQL"
''            sSql(4) = _
''                "Alter TABLE tSYS_SubSysUsed WITH NOCHECK ADD" & _
''                "    CONSTRAINT PK_SYS_SubSysUsed PRIMARY KEY  NONCLUSTERED" & _
''                "    (" & _
''                "        AccountID," & _
''                "        SubSysID" & _
''                "    )"
''        Case "ORACLE"
''            sSql(4) = "CREATE UNIQUE INDEX PK_SYS_SubSysUsed ON " & _
''                "tSYS_SubSysUsed(AccountID,SubSysID)"
''        Case Else
''            Err.Raise 5
''    End Select
'''4
''    sSql(5) = _
''"/*系统会计期表*/" & _
''"CREATE TABLE tSYS_Period(" & _
''"    AccountID     varchar(4)    NOT NULL," & _
''"    Year          char(4)       NOT NULL," & _
''"    PeriodID      smallint      NOT NULL,       /*期号*/" & _
''"    FromDate      " & gloSys.sDateType & "      NOT NULL,       /*起始日期*/" & _
''"    ToDate        " & gloSys.sDateType & "      NOT NULL        /*截止日期*/" & _
''")"
''
''    '“系统会计期表”的主键'PK_SYS_Period'
''    Select Case g_FLAT
''        Case "SQL"
''            sSql(6) = _
''                "Alter TABLE tSYS_Period WITH NOCHECK ADD" & _
''                "    CONSTRAINT PK_SYS_Period PRIMARY KEY  NONCLUSTERED" & _
''                "    (" & _
''                "        AccountID," & _
''                "        [Year]," & _
''                "        PeriodID" & _
''                "    )"
''        Case "ORACLE"
''            sSql(6) = "CREATE UNIQUE INDEX PK_SYS_Period ON " & _
''                "tSYS_Period(AccountID,Year,PeriodID)"
''        Case Else
''            Err.Raise 5
''    End Select
'''5
''        sSql(7) = _
''    "/*行业表*/" & _
''    "CREATE TABLE tSYS_Trade(" & _
''    "    ID        smallint         NOT NULL primary key,   /*行业编号*/" & _
''    "    Name      varchar(40)      NULL,                   /*行业名称*/" & _
''    "    NameEng   varchar(80)      NULL                    /*行业英文名称*/" & _
''    ")"
'''6
''    sSql(8) = _
''"/*行业科目类型表*/" & _
''"CREATE TABLE tSYS_TradeCodeClass(" & _
''"    TradeID        smallint        NOT NULL,           /*行业编号*/" & _
''"    ClassSerial    smallint        NOT NULL,           /*科目类型顺序号*/" & _
'' _
''"    ClassName      varchar(20)     NULL,               /*科目类型名称*/" & _
''"    ClassNameEng   varchar(40)     NULL,               /*科目类型英文名称*/" & _
''"    yefx           char(4)         NOT NULL            /*余额方向*/" & _
''")"
''
''    '“行业科目类型表”的主键'PK_SYS_TradeCodeClass'
''    Select Case g_FLAT
''        Case "SQL"
''            sSql(9) = _
''                "Alter TABLE tSYS_TradeCodeClass WITH NOCHECK ADD" & _
''                "    CONSTRAINT PK_SYS_TradeCodeClass PRIMARY KEY NONCLUSTERED" & _
''                "    (" & _
''                "        TradeID," & _
''                "        ClassSerial" & _