dbcarsys1.sql

汽车销售管理系统,进销存系统典范,值得初学者多多学习

/*			移库单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblMove')
	drop table tblMove
go
create table tblMove
(
	movCode		Varchar(10) primary key,
	movData		Varchar(10) default getDate(),
	indCode		Varchar(10) foreign key references tblInDepot(indCode) not null,
	formCode	Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	toCode		Varchar(10) foreign key references tblDepotInfo(diCode) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	movRemark	varchar(100),
)
go

/*-----------------------------------------------------------*/
/*			预售订单表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblPresell')
	drop table tblPresell
go
create table tblPresell
(
	preCode		Varchar(10) primary key,
	preDate		Datetime default getDate(),
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	ctCode		Varchar(20) foreign key references tblCarType(ctCode) not null,
	ctColor		varchar(20) not null,
	preEarnest	Money,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	preRemark	Varchar(200),
	preState	Tinyint default 0 not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	preIdea		Varchar(200),
)
go

/*-----------------------------------------------------------*/
/*			销售单表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblSell')
	drop table tblSell
go
create table tblSell
(
	selCode		Varchar(10) primary key,
	preCode		Varchar(10) foreign key references tblPresell(preCode),
	selDate		Datetime default getDate() not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	indCode		Varchar(10) foreign key references tblInDepot(indCode) not null,
	selWay		Varchar(20),
	selPurpose	Varchar(20),
	selPrice	Money not null,
	selRemark	Varchar(200),
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	selState	Tinyint default 0 not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	selIdea		Varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			代办表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCommission')
	drop table tblCommission
go
create table tblCommission
(
	CommissionCode	Varchar(10) primary key,
	CommissionDate	Datetime default getDate() not null,
	selCode		Varchar(10) foreign key references tblSell(selCode) not null,
	comCode		Varchar(10) foreign key references tblCommissionItem(comCode) not null,
	comMoney	Money not null,
)
go

/*-----------------------------------------------------------*/
/*			回访规则表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblVisitInfo')
	drop table tblVisitInfo
go
create table tblVisitInfo
(
	viCode		Varchar(10) primary key,
	viName		Varchar(50) not null,
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,
	viDay		int not null,
)
go

/*-----------------------------------------------------------*/
/*			客户回访表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblVisit')
	drop table tblVisit
go
create table tblVisit
(
	vCode		Varchar(10) primary key,
	vDate		Datetime not null,
	vPDate		datetime,
	vFashion	Varchar(10),
	viCode		Varchar(10) foreign key references tblVisitInfo(viCode) not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	vNote		Varchar(500),
	optCode		Varchar(10) foreign key references tblUser(uCode),
	checkCode	Varchar(10) foreign key references tblUser(uCode),
	vState		Tinyint default 0 not null,
	vIdea		Varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			客户跟踪表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblTail')
	drop table tblTail
go
create table tblTail
(
	taiCode		Varchar(10) primary key,
	taiDate		Datetime default getDate() not null,
	taiFashion	Varchar(20) not null,
	cliCode		Varchar(10) foreign key references tblClient(cliCode) not null,
	ctCode		varchar(20) foreign key references tblCarType(ctCode) not null,
	taiNote		Varchar(500) not null,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	taiState	Tinyint default 0 not null,
	taiIdea		Varchar(200)
)
go

/*-----------------------------------------------------------*/
/*			应付款表				     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblShouldPayment')
	drop table tblShouldPayment
go
create table tblShouldPayment
(
	spCode		Varchar(10) primary key,
	indCode		Varchar(10) foreign key references tblStock(stoCode) not null,
	spPMoney	Money default 0,
	spMoney		Money not null,
	spState		bit default 0 not null,
	spRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			付款单明细表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblPayment')
	drop table tblPayment
go
create table tblPayment
(
	payBillCode	Varchar(10) primary key,
	payCode		Varchar(10) foreign key references tblShouldPayment(spCode) not null,
	payDate		Datetime not null,
	paySummary	Varchar(200),
	payMoney	Money default 0,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	payState	bit default 0,
	payRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			应收款单表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblShouldGathering')
	drop table tblShouldGathering
go
create table tblShouldGathering
(
	sgCode		Varchar(10) primary key,
	sgMoney		Money not null,
	selCode		Varchar(10) foreign key references tblSell(selCode) not null,
	sgState		bit default 0,
	sgRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			收款单明细表			     */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblGathering')
	drop table tblGathering
go
create table tblGathering
(
	gatCode		Varchar(10) primary key,
	sgCode		Varchar(10) foreign key references tblShouldGathering(sgCode) not null,
	gatDate		Datetime not null,
	gatMoney	Money default 0,
	optCode		Varchar(10) foreign key references tblUser(uCode) not null,
	checkCode	Varchar(10) foreign key references tblUser(uCode) not null,
	gatState	bit default 0,
	gatRemark	Varchar(500)
)
go

/*-----------------------------------------------------------*/
/*			添加测试数据			     */
/*	    (完整的流程数据测试,除了权限表与财务模块)	     */
/*-----------------------------------------------------------*/
--//用户信息(1位管理员，2位职员)
--insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1000','123456','null','null',1,'null','null','null','2006-01-01',1,'null')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1001','123456','小白','xiaobai',1,'350203198210114020','安全部','管理员','2006-01-01',1,'0592-5581188')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1002','123456','小黑','xiaohei',1,'350203198210104020','信息部','操作员','2006-06-01',1,'0592-5582288')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1003','123456','小兰','xiaolan',0,'350203198212014020','信息部','操作员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1004','123456','老大','laoda',0,'350203198212033333','采购部','采购经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1005','123456','老二','laoer',0,'350203198212034444','采购部','经理','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1006','123456','小王','laoer',0,'350203198212034444','采购部','采购员','2006-07-01',1,'0592-5583388')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1007','123456','小张','laoer',0,'350203198212034444','采购部','采购员','2006-07-01',1,'0592-5583388')
select * from tblUser
go
--//权限信息(注：项目最后在处理)

--//客户信息(3个客户)
insert into tblClient values('KH100001','张三',1,'350203198010104020','1980-01-01','厦门三五互联信息技术有限公司','私营','IT行业','业务经理','0592-1111111','361000','福建厦门')
insert into tblClient values('KH100002','李四',0,'350203198010104020','1980-10-10','厦门三五互联信息技术有限公司','私营','IT行业','业务经理','0592-2222222','361000','福建厦门')
insert into tblClient values('KH100003','王五',1,'350203198011114020','1980-11-11','厦门三五互联信息技术有限公司','私营','IT行业','公司职员','0592-3333333','361000','福建厦门')
insert into tblClient values('KH100004','张逢君',1,'350203198209154016','1982-09-15','北大青鸟厦门邦初培训中心','私营','教育机构','学员','13696906040','361000','福建厦门')