mdlinittable.bas

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

"    vcItemName  varchar(100)    NOT NULL,   /*项目名称*/" & _
"    bMerge      smallint    DEFAULT -1, /*是否合并*/" & _
"    bAnalysis   smallint    DEFAULT -1,  /*是否财务分析*/" & _
"    siItemClass     smallint        NULL,        /*项目级次*/" & _
"    vcItemBm    varchar(10) NULL        /*项目编码*/" & _
")"

    sSql(6) = _
"ALTER TABLE tBB_Item ADD CONSTRAINT PK_BB_Item" & _
"    PRIMARY KEY (cYear,vcReportCode,siRowLabel,siColLabel)"

'/*4.报表项目数据区域表*/
    sSql(7) = _
"CREATE TABLE tBB_Area(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    siStartRow  smallint    NOT NULL,   /*开始行*/" & _
"    siStartCol  smallint    NOT NULL,   /*开始列*/" & _
"    siEndRow    smallint    NOT NULL,   /*结束行*/" & _
"    siEndCol    smallint    NOT NULL,   /*结束列*/" & _
"    cAreaType   char(4)     NOT NULL    /*区域类型(分为项目、数据两种)*/" & _
")"

    sSql(8) = _
"ALTER TABLE tBB_Area ADD CONSTRAINT PK_BB_Area" & _
"    PRIMARY KEY (cYear,vcReportCode,siStartRow,siStartCol,siEndRow,siEndCol)"


'/*5.报表公式表*/
    sSql(9) = _
"CREATE TABLE tBB_Formula(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    siRowLabel  smallint    NOT NULL,   /*行标*/" & _
"    siColLabel  smallint    NOT NULL,   /*列标*/" & _
"    vcFormula   varchar(2000)    NOT NULL    /*单元格公式*/" & _
")"

    sSql(10) = _
"ALTER TABLE tBB_Formula ADD CONSTRAINT PK_BB_Formula" & _
"    PRIMARY KEY (cYear,vcReportCode,siRowLabel,siColLabel)"

'/*6.单位代码表*/
    sSql(11) = _
"CREATE TABLE tBB_Company(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcCode      varchar(10) NOT NULL,   /*单位代码*/" & _
"    vcName      varchar(50) NOT NULL,   /*单位名称*/" & _
"    cType       char(4)     NOT NULL,   /*单位类型:二级、本部、调整*/" & _
"    bMerge      smallint    DEFAULT -1  /*是否合并*/" & _
")"

    sSql(12) = _
"ALTER TABLE tBB_Company ADD CONSTRAINT PK_BB_Company" & _
"    PRIMARY KEY (cYear,vcCode)"

'/*7.汇总方式代码表*/
    sSql(13) = _
"CREATE TABLE tBB_TotalMode(" & _
"    cYear           char(4)     NOT NULL,   /*年份*/" & _
"    vcTotalCode     varchar(10) NOT NULL,   /*汇总方式代码，系统预装900汇总方式*/" & _
"    vcTotalName     varchar(50) NOT NULL,   /*汇总方式名称*/" & _
"    vcTotalCompanies    varchar(255)    NULL,       /*需汇总的单位(用逗号分隔)*/" & _
"    vcTotalCodes        varchar(255)    NULL        /*需汇总的方式(用逗号分隔)*/" & _
")"

    sSql(14) = _
"ALTER TABLE tBB_TotalMode ADD CONSTRAINT PK_BB_TotalMode" & _
"    PRIMARY KEY (cYear,vcTotalCode)"

'/*8.报表汇总单位表*/
    sSql(15) = _
"CREATE TABLE tBB_TotalCompany(" & _
"    cYear       char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode    varchar(10) NOT NULL,   /*报表代码*/" & _
"    vcCompanies varchar(255)    NOT NULL,   /*需汇总的单位(单位代码之间用逗号分隔)*/" & _
"    vcTotalCode     varchar(10) NOT NULL    /*汇总方式代码，缺省使用系统预装900汇总方式*/" & _
")"

    sSql(16) = _
"ALTER TABLE tBB_TotalCompany ADD CONSTRAINT PK_BB_TotalCompany" & _
"    PRIMARY KEY (cYear,vcReportCode)"


'/*9.报表汇编表*/
    sSql(17) = _
"CREATE TABLE tBB_CollectTable(" & _
"    cYear           char(4)     NOT NULL,   /*年份*/" & _
"    vcCollectReportCode varChar(10) NOT NULL,   /*汇编报表代码*/" & _
"    vcCollectReportName varchar(50) NOT NULL,   /*汇编报表名称*/" & _
"    vcReportCode        varchar(10) NOT NULL,   /*所属报表代码*/" & _
"    siColName       Varchar(50) NOT NULL    /*报表需要汇编的列号名称(栏目名)*/" & _
")"

    sSql(18) = _
"ALTER TABLE tBB_CollectTable ADD CONSTRAINT PK_BB_CollectTable" & _
"    PRIMARY KEY (cYear,vcCollectReportCode)"

'/*10.报表表内勾稽平衡关系*/
    sSql(19) = _
"CREATE TABLE tBB_EqualIn(" & _
"    cYear           char(4)     NOT NULL,   /*年份*/" & _
"    vcReportCode        varchar(10) NOT NULL,   /*报表代码*/" & _
"    iSortId         int     NOT NULL,   /*顺序号*/" & _
"    siRowLabel      smallint    NOT NULL,   /*行标*/" & _
"    siColLabel      smallint    NOT NULL,   /*列标*/" & _
"    cCalculateSign      char(1)     NOT NULL,   /*运算符*/" & _
"    siStartRowLabel     smallint    NOT NULL,   /*起始行标*/" & _
"    siEndRowLabel       smallint    NOT NULL,   /*结束行标*/" & _
"    siStartColLabel     smallint    NOT NULL,   /*起始列标*/" & _
"    siEndColLabel       smallint    NOT NULL    /*结束列标*/" & _
")"

    sSql(20) = _
"ALTER TABLE tBB_EqualIn ADD CONSTRAINT PK_BB_EqualIn" & _
"    PRIMARY KEY (cYear,vcReportCode,iSortId)"

'/*11.报表表间勾稽平衡关系*/

    sSql(21) = "CREATE TABLE tBB_EqualOut(" & _
"   cYear           char(4)     NOT NULL,   /*年份*/ " & _
"   vcReportCode        varchar(10) NOT NULL,   /*报表代码*/" & _
"   iSortId         int     NOT NULL,   /*顺序号*/" & _
"   siRowLabel      smallint    NOT NULL,   /*行标(实际)*/" & _
"   siColLabel      smallint    NOT NULL,   /*列标(实际)*/" & _
"   cCalculateSign      char(2)     NOT NULL,   /*运算符(=、+、*、-、>、>=、<、<=)*/" & _
"   vcReportCode2           varchar(10)     NOT NULL,       /*对应报表代码*/" & _
"   vcGetTime       char(20)    NOT NULL,   /*时间(同期，上月，上年同期，上年年末数)*/" & _
"   siRowLabel2     smallint    NOT NULL,   /*对应报表行标(实际)*/" & _
"   siColLabel2     smallint    NOT NULL   /*对应报表列标(实际)*/" & _
")"

    sSql(22) = "ALTER TABLE tBB_EqualOut ADD CONSTRAINT PK_BB_EqualOut" & _
             "  PRIMARY KEY (cYear,vcReportCode,iSortId)"

'/*12.报表权限设置表*/
    sSql(23) = _
"CREATE TABLE tBB_Authority(" & _
"    vcReportCode        varchar(10) NOT NULL,   /*报表代码*/" & _
"    vcUserID        varchar(4)  NOT NULL,   /*操作员ID*/" & _
"    cAuthority      char(4)     NULL        /*报表读写权限: 禁止、只读、编制*/" & _
")"

    sSql(24) = _
"ALTER TABLE tBB_Authority ADD CONSTRAINT PK_BB_Authority" & _
"    PRIMARY KEY (vcReportCode,vcUserID)"

'/*13.报表跨年结转设置表*/
    sSql(25) = _
"CREATE TABLE tBB_CarryForward(" & _
"    cYear           char(4)     NOT NULL primary key,       /*当前年份*/" & _
"    iCarryForwardYear   smallint    NOT NULL,       /*结转年份*/" & _
"    iCarryForwardMonth  smallint    NOT NULL,       /*结转月份*/" & _
"    bCarryForward       smallint    DEFAULT 0       /*结转标志*/" & _
")"

'/*14.汇编报表打印设置表*/
    sSql(26) = _
"CREATE TABLE tBB_CollectPrintSet(" & _
"    siGroupCols     smallint    NOT NULL        /*汇编打印分组列数*/" & _
")"

'/*15.财务报表样式表*/
If g_FLAT = "SQL" Then
    sSql(27) = _
     "CREATE TABLE tBB_ReportMode(" & _
       "    cYear               char(4)      NOT NULL,           /*年份*/" & _
       "    cMonth              char(2)      NOT NULL,           /*月份*/" & _
       "    vcReportCode        varchar(50)  NOT NULL,           /*报表代码*/" & _
       "    lContent            image        NULL,               /*报表样式*/" & _
       "    Length              int          not null default 0  /*文件大小*/" & _
       ")"
Else
    sSql(27) = _
     "CREATE TABLE tBB_ReportMode(" & _
       "    cYear               char(4)      NOT NULL,           /*年份*/" & _
       "    cMonth              char(2)      NOT NULL,           /*月份*/" & _
       "    vcReportCode        varchar(50)  NOT NULL,           /*报表代码*/" & _
       "    lContent            long raw         NULL,           /*报表样式*/" & _
       "    Length              number(10,0)     default 0  not null   /*文件大小*/" & _
       ")"
End If
   sSql(28) = _
"ALTER TABLE tBB_ReportMode ADD CONSTRAINT PK_BB_ReportMode" & _
"   PRIMARY KEY (cYear,cMonth,vcReportCode)"

'/*16.财务报表数据表*/
  sSql(29) = _
 "CREATE TABLE tBB_ReportData" & m_sBeginYear & "(" & _
    "    ReportCode          varchar(10)     NOT NULL,           /*报表代码*/" & _
    "    EnterCode           varchar(25)     NOT NULL,           /*单位代码*/" & _
    "    Month               smallint        NOT NULL,           /*月份*/" & _
    "    iRowLabel           int             NOT NULL,           /*行标*/" & _
    "    ColLabel            int             NOT NULL,           /*列标*/" & _
    "    CellData            decimal(15,4)   default 0           /*单元数据*/" & _
    ")"
   sSql(30) = _
"ALTER TABLE  tBB_ReportData" & m_sBeginYear & " ADD CONSTRAINT PK_BB_ReportData" & m_sBeginYear & _
"    PRIMARY KEY (ReportCode,EnterCode,Month,iRowLabel,ColLabel)"
    
For i = LBound(sSql) To UBound(sSql)
        adoCmd.CommandText = sSql(i)
        adoCmd.Execute
Next i
End Sub





'创建表－－财务分析
Private Sub CreateTables_FX(Optional ByVal m_sBeginYear As String)

'一、创建表
    ReDim sSql(34)
    sSql(1) = _
"/*1指标表*/" & _
"CREATE TABLE tFX_Rate(" & _
"    cYear      char(4)          NOT NULL," & _
"    TypeCode  char(2)           NOT NULL,       /*类型代码*/" & _
"    RateCode  varchar(20)           NOT NULL,       /*指标代码*/" & _
"    RateName  varchar(40)       NULL,           /*指标名称*/" & _
"    Unit      varchar(10)       NULL,           /*单位：%、天、次*/" & _
"    Formula   varchar(200)      NULL,           /*计算公式*/" & _
"    IsSysDef  smallint          default 0,       /*是否系统定义*/" & _
"    QSFormula varchar(200)          NULL            /*取数公式*/" & _
")"
    
    Select Case g_FLAT
        Case "SQL"
            sSql(2) = _
                "ALTER TAB