mdlinittable.bas

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

"   Gyswl_Name  char(60)        NULL ,                             Gyswl_Bill  char(12)        NULL,                               Gyswl_Date " & gloSys.sDateType & " NULL ,                          Gyswl_CBDcode   char(20)        NULL ," & _
" Gyswl_CBDname char(20)        NULL ,                             Gyswl_Lqflg smallint        NULL,                              Gyswl_Qcflg  smallint        default 0     ,                         bmdm            char(12)        NULL ,      /*部门代码*/            bmmc    char(20)        NULL ,  /*部门名称*/" & _
"   xmdm        char(30)        NULL ,      /*项目代码*/            xmmc        char(50)        NULL        /*项目名称*/" & _
")"
    
    Select Case g_FLAT
        Case "SQL"
            sSql(24) = _
                "ALTER TABLE tZW_TempletDetail" & m_sBeginYear & " WITH NOCHECK ADD " & _
                "   CONSTRAINT PK_ZW_TempletDetail" & m_sBeginYear & " PRIMARY KEY  NONCLUSTERED " & _
                "   (" & _
                "       cCode," & _
                "       Jlhm" & _
                "   )"
        Case "ORACLE"
            sSql(24) = "CREATE UNIQUE INDEX PK_ZW_TempletDetail" & m_sBeginYear & " ON " & _
                "tZW_TempletDetail" & m_sBeginYear & "(cCode,Jlhm)"
    End Select
    
'21
    sSql(25) = _
"/*转账凭证设置表*/" & _
"CREATE TABLE tZW_ZzpzSet" & m_sBeginYear & "(" & _
"    ID  char (4) NOT NULL ,        /*转账序号*/" & _
"    siJlhm smallint NOT NULL ,     /*分录号*/" & _
"    cPzlb varchar (4) NOT NULL ,      /*凭证类别*/" & _
"    cZzsm varchar (40) NOT NULL ,  /*转账说明*/" & _
"    cKmdm varchar (40) NOT NULL ,  /*科目代码*/" & _
"    cFx char (2) NOT NULL ,        /*方向*/" & _
"    cJegs varchar (200) NOT NULL , /*金额公式*/" & _
"    cZy varchar (200) NULL,         /*摘要*/" & _
"    zzrq  char(10)  NULL,           /*转账日期*/" & _
"    cPzType varchar(12) NOT NULL   /*凭证类型*/" & _
")"

'/brb add  2002.4.28
  Select Case g_FLAT
        Case "SQL"
            sSql(26) = _
               "ALTER TABLE TZW_ZzpzSet" & m_sBeginYear & _
               " ADD CONSTRAINT PK_ZW_ZzpzSet" & m_sBeginYear & " PRIMARY KEY (cPzType,ID,siJlhm)"
        Case "ORACLE"
            sSql(26) = "CREATE UNIQUE INDEX PK_ZW_ZzpzSet" & m_sBeginYear & " ON " & _
                " TZW_ZzpzSet" & m_sBeginYear & "(cPzType,ID ,siJlhm)"
    End Select

'/*汇兑凭证设置表*/
sSql(27) = "CREATE TABLE tZW_Zzhdset" & m_sBeginYear & " (" & _
"    ID      char (4) NOT NULL ,        /*汇兑转账序号*/" & _
"    Pzlb    varchar (4) NOT NULL ,        /*凭证类别*/" & _
"    sykmdm  varchar (40)   NOT NULL ,  /*收益科目代码*/ " & _
"    sykmmc  varchar (255)   NOT NULL ,  /*收益科目名称*/ " & _
"    sskmdm  varchar (40)   NOT NULL ,  /*损失科目代码*/ " & _
"    sskmmc  varchar (255)   NOT NULL ,  /*损失科目名称*/ " & _
"    zy      varchar (200) NOT NULL ,  /*摘要*/         " & _
"    thzq    varchar (10)  NOT NULL ,  /*汇兑周期*/     " & _
"    schdrq  char (10)              ,   /*上次汇兑日期*/" & _
"    jlfl    smallint    NOT NULL           ,  /*分录号*/       " & _
"    kmdm    varchar (40)  NOT NULL  ,  /*科目代码*/     " & _
"    kmmc    varchar (255)  NOT NULL  ,  /*科目名称*/ " & _
"    Fx      char (2) NOT NULL ,        /*方向*/" & _
"    bz      varchar (10) NOT NULL  /*币种名称*/" & _
")"
           
  Select Case g_FLAT
        Case "SQL"
            sSql(28) = _
               "ALTER TABLE tZW_Zzhdset" & m_sBeginYear & _
               " ADD CONSTRAINT PK_ZW_tZW_Zzhdset" & m_sBeginYear & " PRIMARY KEY (ID,Jlfl)"
        Case "ORACLE"
            sSql(28) = "CREATE UNIQUE INDEX PK_ZW_tZW_Zzhdset" & m_sBeginYear & " ON " & _
                " tZW_Zzhdset" & m_sBeginYear & "(ID ,Jlfl)"
    End Select


'/*(fz)辅助核算数据表（新增）*/   2003.2.25增加
sSql(29) = "CREATE TABLE TFZ_XMBMPZSJ" & m_sBeginYear & " (" & _
    " ID       char (8)     NULL ,        /*辅助记录顺序号*/      kjqj     smallint      NOT NULL ,    /*会计期间*/ " & _
    " pzrq    " & gloSys.sDateType & "    NULL,         /*凭证日期*/       pzzl     varchar (4)   NOT NULL ,    /*凭证种类(简称)*/ " & _
    " pzbh     char (4)     NOT NULL ,    /*凭证编号*/            jlhm     smallint      NOT NULL ,    /*凭证分录序号*/ " & _
    " pzzy     varchar (200)  NULL ,      /*凭证摘要*/            kmdm     varchar (40)  NULL ,        /*科目代码*/" & _
    " kmmc     varchar (255)  NULL ,      /*科目名称*/            fx       char (2)      NOT NULL ,    /*凭证记账借贷方向*/" & _
    " je       numeric(15, 2) NULL ,      /*金额*/                sl       numeric(15, 4) NULL ,       /*数量*/ " & _
    " dj       numeric(15, 2) NULL ,      /*单价*/                wb       numeric(15, 2) NULL ,       /*外币*/ " & _
    " hl       numeric(15, 8) NULL ,      /*汇率*/                xgbz     char (1)       NOT NULL ,   /*修改标志*/ " & _
    " zdrmCode varchar(4)      NULL ,     /*制单人代码*/          zdrm     char(8)        NULL ,       /*制单人名*/ " & _
    " fhrmCode varchar(4)      NULL ,     /*复核人代码*/          fhrm     char(8)        NULL ,       /*复核人名*/ " & _
    " zgrmCode varchar(4)      NULL ,     /*主管人代码*/          zgrm     char(8)        NULL ,       /*主管人名*/ " & _
    " bz       varchar (10)    NULL ,     /*币种, 缺省为人民币*/  bmdm     char (12)      NULL ,       /*部门代码*/ " & _
    " bmmc       char (20)     NULL ,     /*部门名称*/            xmdm     char (30)      NULL ,       /*项目代码*/ " & _
    " xmmc       char (50)     NULL  ,     /*项目名称*/            xmID     char (1)       NULL         /*项目序号 ,用于多项目核算*/ )"
   
'/*科目项目对应表*/
sSql(30) = "CREATE TABLE Tusu_kmToXmClass" & m_sBeginYear & " (" & _
     "   kmdm       varchar(40)    ,   /*科目代码*/" & _
     "   itemid     INT            ,   /*项目分类序号*/ " & _
     "   itemClass  varchar(40)  NULL  /*项目分类代码*/ )"
'/*预算代码表*/
sSql(31) = "create table TZW_BUDGET" & m_sBeginYear & " " & _
    "(" & _
    "ID        smallint not null  /*标示*/," & _
    "KMDM      VARCHAR(40) not null /*科目代码*/," & _
    "ITEMCLASS CHAR(40) /*项目分类*/," & _
    "ITEM      CHAR(30) /*项目代码*/," & _
    "BMDM      CHAR(12) /*部门代码*/," & _
    "GRDM      CHAR(20) /*个人代码*/," & _
    "GYSDM     CHAR(12) /*供应商代码*/," & _
    "KHDM      CHAR(12) /*客户代码*/" & _
    ")"
Select Case g_FLAT
Case "SQL"
    sSql(32) = _
       "ALTER TABLE TZW_BUDGET" & m_sBeginYear & _
       " ADD CONSTRAINT PK_ZW_tZW_BUDGET" & m_sBeginYear & " PRIMARY KEY (ID)"
Case "ORACLE"
    sSql(32) = "CREATE UNIQUE INDEX TZW_BUDGET" & m_sBeginYear & " ON " & _
        " tZW_BUDGET" & m_sBeginYear & "(ID)"
End Select
'  /*预算控制表*/
sSql(33) = "create table TZW_BUDGETCONTROL" & m_sBeginYear & "" & _
        "(" & _
        "ID             smallint not null /*标示*/," & _
        "CONTROLFLAG    CHAR(1) default '0'  /*有效标志*/," & _
        "CONTROLFORMULA VARCHAR(200) /*限制公式*/," & _
        "ALERTFLAG      CHAR(1) default '0'  /*有效标志*/," & _
        "ALERTFORMULA   VARCHAR(200) /*预警公式*/" & _
        ")"
Select Case g_FLAT
Case "SQL"
    sSql(34) = _
       "ALTER TABLE TZW_BUDGETCONTROL" & m_sBeginYear & _
       " ADD CONSTRAINT PK_ZW_tZW_BUDGETCONTROL" & m_sBeginYear & " PRIMARY KEY (ID)"
Case "ORACLE"
    sSql(34) = "ALTER TABLE TZW_BUDGETCONTROL" & m_sBeginYear & _
       " ADD CONSTRAINT PK_ZW_tZW_BUDGETCONTROL" & m_sBeginYear & " PRIMARY KEY (ID)"
End Select
'/*预算计划表*/
sSql(35) = "create table TZW_PLAN" & m_sBeginYear & _
        "(" & _
        "ID          Smallint not null PRIMARY KEY /*标示*/," & _
        "BSELE       Smallint default 0 /**/," & _
        "DLMONPLAN1  DECIMAL(15,2) default 0  /*1月计划*/," & _
        "DLMONPLUS1  DECIMAL(15,2) default 0  /*1月计划追加*/," & _
        "DLMONPLAN2  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS2  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN3  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS3  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN4  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS4  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN5  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS5  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN6  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS6  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN7  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS7  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN8  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS8  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN9  DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS9  DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN10 DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS10 DECIMAL(15,2) default 0 ,"
sSql(35) = sSql(35) & _
        "DLMONPLAN11 DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS11 DECIMAL(15,2) default 0 ," & _
        "DLMONPLAN12 DECIMAL(15,2) default 0 ," & _
        "DLMONPLUS12 DECIMAL(15,2) default 0 " & _
        ")"
'/*预算追加表*/
 sSql(36) = "create table TZW_PLUS" & m_sBeginYear & _
        "(" & _
        "ID             smallint not null PRIMARY KEY /*标示*/," & _
        "IACCOUNTPERIOD smallint default 1 /*追加期间*/," & _
        "DMAKEDATE      " + gloSys.sDateType + " not null   /*填制日期*/," & _
        "CSUMMARY       VARCHAR(60) not null /*摘要*/," & _
        "DEPLUS         DECIMAL(15,2) default 0 /*金额*/," & _
        "ISTATE         smallint default 0 /*状态*/" & _
        ")"
'end
    For i = LBound(sSql) To UBound(sSql)
        adoCmd.CommandText = sSql(i)
        adoCmd.Execute
    Next i

End Sub

'创建表－－财务报表
Private Sub CreateTables_BB(Optional ByVal m_sBeginYear As String)

'一、创建数据表
    ReDim sSql(30)
    

'/*1.报表代码表*/
    sSql(1) = _
"CREATE TABLE tBB_Report(" & _
"    cYear           char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    vcReportName    varchar(50) NOT NULL,   /*报表名称*/" & _
"    cReportType char(4)     NOT NULL,   /*报表类型:月报、季报、年报*/" & _
"    bTotal      smallint    DEFAULT -1,  /*是否汇总*/" & _
"    bCollect    smallint    DEFAULT -1,  /*是否汇编*/" & _
"    bMerge      smallint    DEFAULT -1,  /*是否合并*/" & _
"    bAnalysis   smallint    DEFAULT 0,  /*是否财务分析*/" & _
"    siSerial    smallint    NOT NULL ,    /*报表计算顺序*/" & _
"    ID          smallint    DEFAULT 0      /*报表显示顺序*/  " & _
")"

    sSql(2) = _
"ALTER TABLE tBB_Report ADD CONSTRAINT PK_BB_Report" & _
"    PRIMARY KEY (cYear,vcReportCode)"

'/*2.报表表头表*/
    sSql(3) = _
"CREATE TABLE tBB_Head(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    siColLabel  smallint    NOT NULL,   /*列标*/" & _
"    siBanister  smallint    NOT NULL,   /*栏次*/" & _
"    siColSerial smallint    NOT NULL,   /*实际报表的列号*/" & _
"    vcColName   varchar(30) NOT NULL,   /*栏目名*/" & _
"    cDataType   char(4)     NOT NULL,    /*数据类型(项目、其他、数据)*/" & _
"    bGetDataCol     smallint        DEFAULT 0   /*财务分析是否从表头各列取数*/" & _
")"

    sSql(4) = _
"ALTER TABLE tBB_Head ADD CONSTRAINT PK_BB_Head" & _
"    PRIMARY KEY (cYear,vcReportCode,siColLabel)"

'/*3.报表项目表*/
    sSql(5) = _
"CREATE TABLE tBB_Item(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    siRowLabel  smallint    NOT NULL,   /*行标*/" & _
"    siColLabel  smallint    NOT NULL,   /*列标*/" & _