fz.sql

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

CREATE TABLE [dbo].[TFZ_KMXMFILTER<<YEAR>>] (
	[KMDM] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[KMMC] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[XMDM] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[XMMC] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[CCLSCODE] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CCLSNAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[TFZ_MULACCOUNTBOOK] (
	[CID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CNAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[CMODE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[CMONTHFROM] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[CMONTHTO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[BINCLUDENOTRECORDVOUCHER] [numeric](38, 0) NULL ,
	[BSHOWNOTMONEYCOL] [numeric](38, 0) NULL ,
	[CSUBJECTCODE] [varchar] (400) COLLATE Chinese_PRC_CI_AS NULL ,
	[CDEPTCODE] [varchar] (400) COLLATE Chinese_PRC_CI_AS NULL ,
	[CSITEMCODECOLLECT] [varchar] (400) COLLATE Chinese_PRC_CI_AS NULL ,
	[BAMOUNT] [numeric](38, 0) NULL ,
	[BFOREIGN] [numeric](38, 0) NULL ,
	[ILEVEL] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[CACCOUNTTYPE] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[BISMONEY] [numeric](38, 0) NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[TFZ_MULACCOUNTBOOKCOL] (
	[CID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[CCOLCODE] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[CCOLNAME] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[ICOLLEVEL] [numeric](38, 0) NULL ,
	[CCOLDIRECT] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[CNUM] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[TFZ_XMBMPZSJ<<YEAR>>] (
	[ID] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[kjqj] [int] NOT NULL ,
	[pzrq] [datetime] NULL ,
	[pzzl] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[pzbh] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[jlhm] [smallint] NOT NULL ,
	[pzzy] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[kmdm] [varchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
	[kmmc] [varchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
	[fx] [char] (2) COLLATE Chinese_PRC_CI_AS 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) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[zdrmCode] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[zdrm] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[fhrmCode] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[fhrm] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[zgrmCode] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[zgrm] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[bz] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[bmdm] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
	[bmmc] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[xmdm] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[xmmc] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[xmID] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
;

CREATE TABLE [dbo].[TZW_IGENERALDATA<<YEAR>>] (
	[ITEM] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[FIELDCODE] [numeric](38, 0) NOT NULL ,
	[VALUE] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ,
	[ITEMCLASS] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
;
CREATE TABLE [dbo].[TZW_IGENERALSTRUCT<<YEAR>>] (
	[VCTITLE] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[NTYPE] [numeric](38, 0) NOT NULL ,
	[NLENGTH] [numeric](38, 0) NOT NULL ,
	[ITEMCLASS] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[ISNEED] [numeric](38, 0) NOT NULL ,
	[ID] [numeric](38, 0) NOT NULL ,
	[SORTED] [numeric](38, 0) NOT NULL 
) ON [PRIMARY]
;
CREATE TABLE [dbo].[tFZ_ExpandPzsj<<YEAR>>] (
	[ID] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[KJQJ] [int] NOT NULL ,
	[PZZL] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[PZBH] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[JLHM] [smallint] NOT NULL 
) ON [PRIMARY]
;
ALTER TABLE [dbo].[tFZ_ExpandPzsj<<YEAR>>] WITH NOCHECK ADD 
	CONSTRAINT [PK_FZ_EXPANDPZSJ<<YEAR>>] PRIMARY KEY  CLUSTERED 
	(
		[ID],
		[KJQJ],
		[PZZL],
		[PZBH],
		[JLHM]
	)  ON [PRIMARY] 
;
ALTER TABLE [dbo].[TFZ_KMXMFILTER<<YEAR>>] WITH NOCHECK ADD 
	CONSTRAINT [PK_TFZ_KMXMFILTER<<YEAR>>] PRIMARY KEY  CLUSTERED 
	(
		[KMDM],
		[XMDM],
		[CCLSCODE]
	)  ON [PRIMARY] 
;
ALTER TABLE [dbo].[TFZ_XMBMPZSJ<<YEAR>>] WITH NOCHECK ADD 
	CONSTRAINT [PK_TFZ_XMBMPZSJ<<YEAR>>] PRIMARY KEY  CLUSTERED 
	(
		[ID],
		[kjqj],
		[pzzl],
		[pzbh],
		[jlhm],
		[xmID]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[TZW_IGENERALDATA<<YEAR>>] WITH NOCHECK ADD 
	CONSTRAINT [PK_TZW_IGENERALDATA<<YEAR>>] PRIMARY KEY  CLUSTERED 
	(
		[ITEM],
		[FIELDCODE]
	)  ON [PRIMARY] 
;

ALTER TABLE [dbo].[TZW_IGENERALSTRUCT<<YEAR>>] WITH NOCHECK ADD 
	CONSTRAINT [PK_TZW_IGENERALSTRUCT<<YEAR>>] PRIMARY KEY  CLUSTERED 
	(
		[ITEMCLASS],
		[ID]
	)  ON [PRIMARY] 
;
create view VFZ_EXPANDPZSJ<<YEAR>> as Select bmdm,bmmc,bz,dj,fhrm,fhrmCode,fx,hl,a.ID,je,a.jlhm,a.kjqj,kmdm,kmmc,a.pzbh,pzrq,a.PZZL,pzzy,sl,wb,xgbz,xmdm,xmID,xmmc,zdrm,zdrmCode,zgrm,zgrmCode from TFZ_XMBMPZSJ<<YEAR>> a,tFZ_ExpandPzsj<<YEAR>> b where a.Kjqj=b.Kjqj and a.pzzl*=b.pzzl and a.pzbh*=b.pzbh and a.jlhm*=b.jlhm and a.ID*=b.ID;