📄 mdlinittable.bas
字号:
" cCode char(4) NOT NULL," & _
" vcName varchar(50) NOT NULL," & _
" vcShortName varchar(20) NULL, /*简称*/" & _
" siOrder smallint NULL, /*在作合并报表底稿时所处序号*/" & _
" vcTrade varchar(20) NULL, /*子公司的行业名称*/" & _
" vcEconomy varchar(20) NULL, /*子公司的经济类别*/" & _
" vcFile varchar(120) NULL, /*上报数据文件名(含路径)*/" & _
" bMerge smallint DEFAULT -1 /*是否合并报表*/" & _
")"
sSql(9) = _
"ALTER TABLE tUSU_SubCompany ADD CONSTRAINT PK_USU_SubCompany" & _
" PRIMARY KEY (cCode)"
'5
'/*行业性质维护表*/
sSql(10) = _
"CREATE TABLE tUSU_Trade(" & _
" vcName varchar(20) NOT NULL" & _
")"
sSql(11) = _
"ALTER TABLE tUSU_Trade ADD CONSTRAINT PK_USU_Trade" & _
" PRIMARY KEY (vcName)"
'6
'/*经济类别维护表*/
sSql(12) = _
"CREATE TABLE tUSU_Economy(" & _
" vcName VarChar(20) NOT NULL" & _
")"
sSql(13) = _
"ALTER TABLE tUSU_Economy ADD CONSTRAINT PK_USU_Economy" & _
" PRIMARY KEY (vcName)"
'7
'账页列宽格式表
sSql(14) = _
"CREATE TABLE tUSU_AccountFormat(" & _
" AccountType varchar(40) NOT NULL, /*账的类别*/" & _
" AccountFormat varchar(20) NOT NULL, /*账页的格式*/" & _
" ColWidth varchar(150) NULL /*账页中各列的宽度*/" & _
")"
sSql(15) = _
"ALTER TABLE tUSU_AccountFormat ADD CONSTRAINT PK_USU_AccountFormat" & _
" PRIMARY KEY(AccountType, AccountFormat)"
'8
'/*(USU)1.辅助核算表*/
sSql(16) = _
"CREATE TABLE tUSU_Fz" & m_sBeginYear & "(" & _
" ID int NOT NULL Primary key, /*唯一序号*/" & _
" kmdm varchar(40) NOT NULL, /*科目代码*/" & _
" Grwl_Code char(20) NULL, /*个人代码*/" & _
" Khwl_Code char(12) NULL, /*客户代码*/" & _
" Gyswl_Code char(12) NULL, /*供应商代码*/" & _
" bmdm char(12) NULL, /*部门代码 */" & _
" xmdm char(30) NULL, /*项目代码*/" & _
" ljjsl00 decimal(15, 3) default 0,ljjwb00 decimal(15, 2) default 0,ljj00 decimal(15, 2) default 0,ljdsl00 decimal(15, 3) default 0,ljdwb00 decimal(15, 2) default 0, ljd00 decimal(15, 2) default 0," & _
" ljjsl01 decimal(15, 3) default 0,ljjwb01 decimal(15, 2) default 0,ljj01 decimal(15, 2) default 0,ljdsl01 decimal(15, 3) default 0,ljdwb01 decimal(15, 2) default 0, ljd01 decimal(15, 2) default 0," & _
" ljjsl02 decimal(15, 3) default 0,ljjwb02 decimal(15, 2) default 0,ljj02 decimal(15, 2) default 0,ljdsl02 decimal(15, 3) default 0,ljdwb02 decimal(15, 2) default 0, ljd02 decimal(15, 2) default 0," & _
" ljjsl03 decimal(15, 3) default 0,ljjwb03 decimal(15, 2) default 0,ljj03 decimal(15, 2) default 0,ljdsl03 decimal(15, 3) default 0,ljdwb03 decimal(15, 2) default 0, ljd03 decimal(15, 2) default 0," & _
" ljjsl04 decimal(15, 3) default 0,ljjwb04 decimal(15, 2) default 0,ljj04 decimal(15, 2) default 0,ljdsl04 decimal(15, 3) default 0,ljdwb04 decimal(15, 2) default 0, ljd04 decimal(15, 2) default 0," & _
" ljjsl05 decimal(15, 3) default 0,ljjwb05 decimal(15, 2) default 0,ljj05 decimal(15, 2) default 0,ljdsl05 decimal(15, 3) default 0,ljdwb05 decimal(15, 2) default 0, ljd05 decimal(15, 2) default 0," & _
" ljjsl06 decimal(15, 3) default 0,ljjwb06 decimal(15, 2) default 0,ljj06 decimal(15, 2) default 0,ljdsl06 decimal(15, 3) default 0,ljdwb06 decimal(15, 2) default 0, ljd06 decimal(15, 2) default 0," & _
" ljjsl07 decimal(15, 3) default 0,ljjwb07 decimal(15, 2) default 0,ljj07 decimal(15, 2) default 0,ljdsl07 decimal(15, 3) default 0,ljdwb07 decimal(15, 2) default 0, ljd07 decimal(15, 2) default 0," & _
" ljjsl08 decimal(15, 3) default 0,ljjwb08 decimal(15, 2) default 0,ljj08 decimal(15, 2) default 0,ljdsl08 decimal(15, 3) default 0,ljdwb08 decimal(15, 2) default 0, ljd08 decimal(15, 2) default 0," & _
" ljjsl09 decimal(15, 3) default 0,ljjwb09 decimal(15, 2) default 0,ljj09 decimal(15, 2) default 0,ljdsl09 decimal(15, 3) default 0,ljdwb09 decimal(15, 2) default 0, ljd09 decimal(15, 2) default 0," & _
" ljjsl10 decimal(15, 3) default 0,ljjwb10 decimal(15, 2) default 0,ljj10 decimal(15, 2) default 0,ljdsl10 decimal(15, 3) default 0,ljdwb10 decimal(15, 2) default 0, ljd10 decimal(15, 2) default 0," & _
" ljjsl11 decimal(15, 3) default 0,ljjwb11 decimal(15, 2) default 0,ljj11 decimal(15, 2) default 0,ljdsl11 decimal(15, 3) default 0,ljdwb11 decimal(15, 2) default 0, ljd11 decimal(15, 2) default 0," & _
" ljjsl12 decimal(15, 3) default 0,ljjwb12 decimal(15, 2) default 0,ljj12 decimal(15, 2) default 0,ljdsl12 decimal(15, 3) default 0,ljdwb12 decimal(15, 2) default 0, ljd12 decimal(15, 2) default 0)"
'/*用点来分隔科目代码，并且把科目表拆分成科目表和科目余额表*/
sSql(17) = "CREATE TABLE tZW_km" & m_sBeginYear & "(" & _
" 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(18) = "CREATE TABLE tZW_Balance" & m_sBeginYear & "(" & _
" 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)"
Select Case g_FLAT
Case "SQL"
sSql(19) = "Alter TABLE tZW_Balance" & m_sBeginYear & " WITH NOCHECK ADD " & _
"CONSTRAINT PK_ZW_balance" & m_sBeginYear & " PRIMARY KEY NONCLUSTERED" & _
"(" & _
"kmdm," & _
"bz" & _
")"
Case "ORACLE"
sSql(19) = "CREATE UNIQUE INDEX PK_ZW_Balance" & m_sBeginYear & " ON " & _
"tZW_Balance" & m_sBeginYear & "(kmdm,bz)"
End Select
'/*查询项目对照表*/
sSql(20) = "CREATE TABLE tUSU_TableItem(" & _
" cModule varchar(3) NOT NULL , /*模块名*/" & _
" VcItemCHI varchar(30) NOT NULL , /*项目中文名称*/ " & _
" VcItemEng varchar(30) NOT NULL , /*项目英文名称*/ " & _
" VcItemType varchar(10) NOT NULL /*项目类型 2：数字 1：字符 3：日期*/ " & _
")"
'/*查询表达式设置表*/
sSql(21) = "CREATE TABLE tUSU_QuerySet(" & _
" cModule varchar(3) NOT NULL , /*模块名*/" & _
" VcQueryName varchar(30) NOT NULL , /*查询名称*/ " & _
" VcRelation varchar(10) , /*与上式关系符*/ " & _
" VcLeft varchar(30) , /*左包括*/ " & _
" VcItemEng varchar(20) NOT NULL , /*项目英文名称*/" & _
" VcComPare varchar(10) NOT NULL , /*比较符*/ " & _
" VcValues varchar(20) , /*比较数值*/" & _
" VcRight varchar(10) , /*右包括*/ " & _
" iOrderNum varchar(8) NOT NULL /*顺序号*/ " & _
")"
'/*数据备份-数据表清单*/
sSql(22) = "CREATE TABLE TCON_DAITEM (" & _
"cItmSysCoding char(2) NOT NULL ," & _
"cItmDaCoding varchar (8) NOT NULL , " & _
"vcItmDaCaption varchar (30) NULL , " & _
" vcItmDaName varchar (30) NULL ," & _
"cItmDaEdition char(6) NULL , " & _
"vcItmSynosis varchar(50) NULL ," & _
"dItmRemove " & gloSys.sDateType & " NULL)"
'执行sql 建表语句
For i = LBound(sSql) To UBound(sSql)
adoCmd.CommandText = sSql(i)
adoCmd.Execute
Next i
End Sub
'创建表－－集成账务
Private Sub CreateTables_ZW(Optional ByVal m_sBeginYear As String)
'一、创建表
ReDim sSql(36)
'1
sSql(1) = _
"/*(ZW)1.往来账账龄区间表*/" & _
"CREATE TABLE tZW_AccountAge" & m_sBeginYear & "(" & _
" cCode char(10) NOT NULL, /*类别标识代码*/" & _
" iNum int NULL, /*账龄分析区间编号*/" & _
" iLength int NULL /*账龄分析区间天数*/" & _
")"
'2
sSql(2) = _
"/*(ZW)2.支票登记簿表*/" & _
"CREATE TABLE tZW_Check" & m_sBeginYear & "(" & _
" kmdm varchar(40) NOT NULL, /*科目代码*/" & _
" checkNo varchar(10) NOT NULL, /*支票号*/" & _
" usedate " & gloSys.sDateType & " NULL, /*领用日期*/" & _
" DepartCode varchar(12) NULL, /*领用部门代码*/" & _
" PersonCode varchar(20) NULL, /*领用人编号*/" & _
" money decimal(15,2) default 0, /*限领*/" & _
" usertext varchar(30) NULL, /*用途*/" & _
" outdate " & gloSys.sDateType & " NULL, /*报销日期*/" & _
" text varchar(30) NULL /*备注*/" & _
")"
Select Case g_FLAT
Case "SQL"
sSql(3) = _
"Alter TABLE tZW_Check" & m_sBeginYear & " WITH NOCHECK ADD " & _
" CONSTRAINT PK_ZW_Check" & m_sBeginYear & " PRIMARY KEY NONCLUSTERED " & _
" (" & _
" kmdm," & _
" checkNo " & _
" )"
Case "ORACLE"
sSql(3) = "CREATE UNIQUE INDEX PK_ZW_Check" & m_sBeginYear & " ON " & _
"tZW_Check" & m_sBeginYear & "(kmdm,checkNo)"
End Select
'3
sSql(4) = _
"/*(ZW)3.客户档案*/" & _
"CREATE TABLE tZW_Customer" & m_sBeginYear & "(" & _
" cCusCode CHAR(12) NOT NULL PRIMARY KEY, cCusName CHAR(60) NULL, /*客户名称*/ cCusAbbName CHAR(20) NULL, /*客户简称*/" & _
" cCCCode CHAR(12) NULL, /*所属分类码*/ cCDCode CHAR(6) NULL, /*所属地区码*/ cCusHeadCode CHAR(12) NULL, /*客户总公司*/ cTrade CHAR(40) NULL, /*所属行业*/" & _
" cCusRegCode CHAR(18) NULL, /*税号*/ cCusLPerson CHAR(10) NULL, /*法人*/ cCusBank CHAR(30) NULL, /*开户银行*/ cCusAccount CHAR(40) NULL, /*银行账号*/" & _
" cCusAddress CHAR(80) NULL, /*地址*/ cCusPostCode CHAR(6) NULL, /*邮政编码*/ cCusPerson CHAR(10) NULL, /*联系人*/ cCusEmail CHAR(30) NULL, /*Email地址*/" & _
" cCusPhone CHAR(20) NULL, /*电话*/ cCusFax CHAR(20) NULL, /*传真*/ cCusBP CHAR(20) NULL, /*呼机*/ cCusHand CHAR(20) NULL, /*手机*/" & _
" cCusOAddress CHAR(40) NULL, /*发货地址*/ cCusOType CHAR(10) NULL, /*发货方式*/ cCusWhCode CHAR(20) NULL, /*发货仓库*/ iARmoney DECIMAL(9,2) NULL, /*应收金额*/" & _
" iCusDisRate DECIMAL(9,2) default 0, /*扣率*/ cCusCreGrade CHAR(6) NULL, /*信用等级*/ iCusCreLine DECIMAL(9,1) NULL, /*信用额度*/ iCusCreDate DECIMAL(9,1) NULL, /*作用期限*/" & _
" cCusPayCond CHAR(20) NULL, /*付款条件*/ dLastDate " & gloSys.sDateType & " NULL, /*最后交易日期*/iLastmoney DECIMAL(9,2) NULL, /*最后交易金额*/ dLRDate " & gloSys.sDateType & " NULL, /*最后收款日期*/" & _
" iLRmoney DECIMAL(9,2) default 0, /*最后收款金额*/ cCusDepart CHAR(12) NULL, /*分管部门*/ cCusPPerson CHAR(10) NULL, /*专营业务员*/" & _
" dCusDevDate " & gloSys.sDateType & " NULL,/*发展日期*/ dEndDate " & gloSys.sDateType & " NULL, /*停止日期*/ iFrequency INT NULL /*使用频度*/" & _
")"
'4
sSql(5) = _
"/*(ZW)4.客户分类*/" & _
"CREATE TABLE tZW_CustomerClass" & m_sBeginYear & "(" & _
" cCCCode CHAR (12) NOT NULL PRIMARY KEY, /*类别编码*/" & _
" cCCName CHAR (20) NULL, /*类别名称*/" & _
" iCCGrade INT NULL, /*编码级次*/" & _
" bCCEnd smallint default 0 /*末级标志*/" & _
")"
'5
sSql(6) = _
"/*(ZW)5.客户档案显示设置*/" & _
"CREATE TABLE tZW_CustomerShowSet" & m_sBeginYear & "(" & _
" ID INT NOT NULL, /*识别号*/" & _
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -