mdlinittable.bas

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

Attribute VB_Name = "mdlInitTable"
Option Explicit
Option Base 1
Dim adoCmd As ADODB.Command
Dim i As Long
Dim sSql() As String
Dim sValues() As String
Public Sub CreateCWDBTables(ByRef cnnORA As ADODB.Connection, _
        ByVal sBeginYear As String, _
        ByRef pBr As ProgressBar, ByRef lbpro As Label)
    Dim sPath As String
    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
    pBr.Value = 13
    lbpro.Caption = "完成：13%"
    
    '2
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "CF.sql"
    CreateDB.Append Script
    pBr.Value = 15
    lbpro.Caption = ""
    lbpro.Caption = "完成：15%"
    
    '3
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "FX.sql"
    CreateDB.Append Script
    pBr.Value = 17
    lbpro.Caption = ""
    lbpro.Caption = "完成：17%"
    
    '4
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "FZ.sql"
    CreateDB.Append Script
    pBr.Value = 19
    lbpro.Caption = ""
    lbpro.Caption = "完成：19%"
    
    '5
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "GD.sql"
    CreateDB.Append Script
    pBr.Value = 21
    lbpro.Caption = ""
    lbpro.Caption = "完成：21%"
    
    '6
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "GZ.sql"
    CreateDB.Append Script
    pBr.Value = 22
    lbpro.Caption = ""
    lbpro.Caption = "完成：22%"
    
    '7
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "RS.sql"
    CreateDB.Append Script
    pBr.Value = 24
    lbpro.Caption = ""
    lbpro.Caption = "完成：24%"
    
    '8
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "SF.sql"
    CreateDB.Append Script
    pBr.Value = 26
    lbpro.Caption = ""
    lbpro.Caption = "完成：26%"
    
    '9
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "TY.sql"
    CreateDB.Append Script
    pBr.Value = 28
    lbpro.Caption = ""
    lbpro.Caption = "完成：28%"
    
    '10
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "ZW.sql"
    CreateDB.Append Script
    pBr.Value = 30
    lbpro.Caption = ""
    lbpro.Caption = "完成：30%"
    
    '11
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "MR.sql"
    CreateDB.Append Script
    pBr.Value = 31
    lbpro.Caption = ""
    lbpro.Caption = "完成：31%"
    '12
    Set Script = New clsScript
    Script.usFileType = "Sql"
    Script.usScriptFilename = sPath & "RA.sql"
    CreateDB.Append Script
    pBr.Value = 32
    lbpro.Caption = ""
    lbpro.Caption = "完成：32%"
    
    CreateDB.DoExecute
    Set CreateDB = Nothing
    Set Script = Nothing
End Sub

'创建通用表
Private Sub CreateTables_USU(Optional ByVal m_sBeginYear As String)
    
    ReDim sSql(22)

'1
'"/*(USU)1.代码级数表*/"
    sSql(1) = _
"CREATE TABLE tUSU_DMJS(" & _
"    Type      varchar(4)       NOT NULL,       /*类型：科目、结算、部门、项目、客户、供应*/" & _
"    JC        smallint         NOT NULL,       /*代码级次：" & _
"                       科目6/18、结算3/12、部门5/12、项目8/40、客供5/12*/" & _
"    WS        smallint         default 0       /*位数*/" & _
")"
    
    Select Case g_FLAT
        Case "SQL"
                sSql(2) = _
            "Alter TABLE tUSU_DMJS WITH NOCHECK ADD" & _
            "    CONSTRAINT PK_USU_DMJS PRIMARY KEY  NONCLUSTERED" & _
            "    (" & _
            "        Type," & _
            "        JC" & _
            "    )"
        Case "ORACLE"
            sSql(2) = "CREATE UNIQUE INDEX PK_USU_DMJS ON " & _
                "tUSU_DMJS(Type,JC)"
        Case Else
            Err.Raise 5
    End Select
   
'2
'"/*(USU)2.部门表*/"
    sSql(3) = _
"CREATE TABLE tUSU_Department" & m_sBeginYear & "(" & _
"    cDepCode      CHAR(12)  NOT NULL PRIMARY KEY,      /*部门编号*/" & _
"    cDepName      CHAR(20)  NOT NULL,                  /*部门名称*/" & _
"    iDepGrade     smallint  NULL,                      /*编码级次*/" & _
"    bDepEnd       smallint  default -1,                /*末级标志*/" & _
"    cDepPerson    CHAR(20)  NULL,                      /*部门负责人*/" & _
"    cDepPhone     CHAR(20)  NULL,                      /*部门电话*/" & _
"    cDepMemo      CHAR(40)  null,                      /*备注*/" & _
"    bOtherUse     smallint  default 0,                 /*是否使用*/" & _
"    cGDkm         CHAR(40)  NULL,                      /*固定资产中对应折旧科目*/" & _
"    IsHaveZD      smallint  default 0                  /*工资核算转账时是否已制单*/" & _
")"

'
'XX.2.
'"/*(RS)1.籍贯维护表*/"
    sSql(4) = _
"CREATE TABLE tRS_TJGDM (" & _
"    CODE      char (6)     NOT NULL PRIMARY KEY," & _
"    NAME      char (40)    NULL," & _
"    USED      smallint     default 0" & _
")"

'XX.3.
'/*(RS)2.人员信息表*/
    sSql(5) = "CREATE TABLE tRS_TZZRY(" & _
"    ZGBH  varchar(20)     NOT NULL Primary Key,   BMBH  char(12)      NULL ,          BMMC    char(20)        NULL , AccountYear   char(4)   NULL,AccountPeriod  smallint    NULL," & _
"    ZGXM  char(20)     NULL ,                  GZZH  char(20)      NULL ,          ZZ      char(4)         NULL , " & _
"    SFZH  char(18)     NULL ,                  XB    char(2)       NULL ,          MZ      char(20)        NULL ,          JGBH    char(6)        NULL,            JG char(40)  NULL," & _
"    CSRQ  " & gloSys.sDateType & "  NULL ,     NL    smallint      default 0,      HY      char(10)        NULL ,          GRSF    char(10)       NULL ," & _
"    ZZMM  char(10)     NULL ,                  SHBZH char(20)      NULL ,          YLZHH   char(20)        NULL ,          GRYHZH  char(20)       NULL ,           GRYHDH  char(2) NULL," & _
"    WHCD  char(10)     NULL ,                  BYYX  char(40)      NULL ,          BYSJ   " & gloSys.sDateType & " NULL ,  ZY      char(20)       NULL ," & _
"    XW    char(10)     NULL ,                  YZ    char(10)      NULL ,          CJGZSJ " & gloSys.sDateType & " NULL ,  JRBDWSJ " & gloSys.sDateType & " NULL ," & _
"    GL    smallint     default 0,              RYFL  char(20)      NULL ,          GWGZ    char(20)        NULL ,          JSZC    char(10)       NULL ," & _
"    GZJB  char(10)     NULL ,                  XZZW  char(20)      NULL ,          JTZZ    char(40)        NULL ,          YB      char(6)        NULL ," & _
"    DHHM  char(40)     NULL ,                  HKSZD char(40)      NULL ,          HKXZ    char(10)        NULL ,          GADQS   char(10)       NULL ," & _
"    LGQK  char(10)     NULL ,                  LGSJ " & gloSys.sDateType & " NULL, XGFL    char(10)        NULL ,          XGSJ " & gloSys.sDateType & " NULL ," & _
"    BQYGL smallint     default 0,              HTBZ  smallint      default 0,      GZLBid  int             NULL," & _
"    KHH   char(25)     NULL,                  ZPfile char(80)      NULL," & _
"    dadh  char(20)     NULL,                   KMDM  char(40)      NULL)"

'3.
'/*“集成账务”凭证输入互斥控制表*/
    sSql(6) = "CREATE TABLE tUSU_PzMutex(" & _
"    iPeriod     smallint    NOT NULL," & _
"    cPzzl       varchar(4)     NOT NULL," & _
"    cComputer   varchar(100)    NULL" & _
")"
    
    Select Case g_FLAT
        Case "SQL"
            sSql(7) = "Alter TABLE tUSU_PzMutex WITH NOCHECK ADD " & _
                "CONSTRAINT PK_USU_PzMutex PRIMARY KEY  NONCLUSTERED" & _
                "(" & _
                "iPeriod," & _
                "cPzzl" & _
                ")"
        Case "ORACLE"
            sSql(7) = "CREATE UNIQUE INDEX PK_USU_PzMutex ON " & _
                "tUSU_PzMutex(iPeriod,cPzzl)"
    End Select

'4.
'/*子公司目录表*/
    sSql(8) = _
"CREATE TABLE tUSU_SubCompany(" & _