📄 dbcarsys1.sql
字号:
select * from tblClient
go
--//厂商信息(2家厂商)
insert into tblFactory values('CS1001','上海福特汽车公司','Mr.Ford','021-12345678','上海埔东新区','200000',1,'上海福特汽车公司')
insert into tblFactory values('CS1002','上海大众汽车公司','Mr.Csvw','021-11223344','上海埔东新区','200000',1,'上海大众汽车公司')
select * from tblFactory
go
--//车型信息(4部汽车资料)
insert into tblCarType values('MONEDO 2.0Chia','蒙迪欧','小轿车','上海福特','上海福特(四速自排)真皮','CS1001',179800,189800)
insert into tblCarType values('MONEDO 2.5 V6 GLX','蒙迪欧','小轿车','上海福特','上海福特(手排/自动)真皮','CS1001',180000,190000)
insert into tblCarType values('PASSAT 1.8T','帕萨特','小轿车','上海大众','上海帕萨特(手排/自动)真皮','CS1002',248000,255000)
insert into tblCarType values('PASSAT 2.0','帕萨特','小轿车','上海大众','上海帕萨特(手排)真皮','CS1002',350000,360000)
select * from tblCarType
delete tblCarType where ctCode='PASSAT 2.0'
go
--//仓库信息(2间仓库)
insert into tblDepotInfo values('CK1001','1号仓库','仓库员1','厦门湖里区','0592-5051111')
insert into tblDepotInfo values('CK1002','2号仓库','仓库员2','厦门思明区','0592-5052222')
select * from tblDepotInfo
go
--//代办项目(3个代办项)
insert into tblCommissionItem values('DBS1001','上牌',500,'仅限厦门地区')
insert into tblCommissionItem values('DBS1002','VCD',800,'仅限厦门地区')
insert into tblCommissionItem values('DBS1003','车底灯',1000,'仅限厦门地区')
select * from tblCommissionItem
go
--//回访规则(根据目前现有汽车资料，每种汽车使用15天与60天的访问方式)
insert into tblVisitInfo values('HF1001','拜访','MONEDO 2.0Chia',15)
insert into tblVisitInfo values('HF1002','拜访','MONEDO 2.0Chia',60)
insert into tblVisitInfo values('HF1003','拜访','MONEDO 2.5 V6 GLX',15)
insert into tblVisitInfo values('HF1004','拜访','MONEDO 2.5 V6 GLX',60)
insert into tblVisitInfo values('HF1005','拜访','PASSAT 1.8T',15)
insert into tblVisitInfo values('HF1006','拜访','PASSAT 1.8T',60)
insert into tblVisitInfo values('HF1007','拜访','PASSAT 2.0',15)
insert into tblVisitInfo values('HF1008','拜访','PASSAT 2.0',60)
select * from tblVisitInfo
go
--//系统初始(所有单据编号使用中文拼音大写开头,例如'采购单'——CG开头；起始编号统一为100001,其它编号同理)
insert into tblOsInit values('SYS1001','厦门T77汽车销售公司','福建厦门','0592-8888888','www.xmt77.com','RK100001','YK100001','XS100001','SK100001','DB100001','FK100001','YS100001','CG100001')
select * from tblOsInit
go
--//采购信息(2部车未审核，2部车已经完成,采购时不产生付款单)
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100001','2006-07-01','MONEDO 2.0Chia',0,'蓝色',179800,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100002','2006-07-01','MONEDO 2.5 V6 GLX',0,'紫色',180000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100003','2006-07-01','PASSAT 1.8T',3,'银白色',248000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100004','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100005','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1002','emp1001')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100006','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1003','emp1002')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100007','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1002','emp1003')
insert into tblStock(stoCode,stoDate,ctCode,stoState,stoColor,stoPrice,optCode,checkCode) values('CG100008','2006-07-01','PASSAT 2.0',3,'黑色',350000,'emp1002',null)
select * from tblStock
go
--//入库信息(2部车分别在2个不同仓库中,并且2部车状态:已审核已通过,生成付款单)
insert into tblInDepot(indCode,indDate,diCode,stoCode,indColor,indButtom,indEngine,indEli,indImport,indVerify,indSelf,indKey,indKilm,indOutDate,indPrice,indTake,optCode,indState)
values('RK100001','2006-07-05','CK1001','CG100003','银白色','DP123','FDJ123','HG123','JK123','SJ123','ZB123','YS123','100','2006-07-15',248000,'TD123','emp1002',1)
insert into tblInDepot(indCode,indDate,diCode,stoCode,indColor,indButtom,indEngine,indEli,indImport,indVerify,indSelf,indKey,indKilm,indOutDate,indPrice,indTake,optCode,indState)
values('RK100002','2006-07-05','CK1002','CG100004','黑色','DP321','FDJ321','HG321','JK321','SJ321','ZB321','YS321','100','2006-07-15',350000,'TD321','emp1003',1)
select * from tblInDepot
go
--//移库信息(注：简单的业务不添加数据了)
--//预售信息(2个客户订购仓库内不同的车,并且2张预售单状态都是'已审核已通过',不产生收款单,只开出定金收据)
insert into tblPresell values('YD100001','2006-07-20','KH100001','PASSAT 1.8T','银白色',10000,'emp1002','已支付定金10000 RMB',2,'emp1001','无意见')
insert into tblPresell values('YD100002','2006-07-20','KH100002','PASSAT 2.0','黑色',10000,'emp1003','已支付定金10000 RMB',2,'emp1001','无意见')
select * from tblPresell
go
--//销售信息(根据2个客户的预售单信息产生销售单,并且2张销售单状态都是'销售已完成',生成收款单,生成客户回访)
insert into tblSell values('XS100001','YD100001','2006-07-25','KH100001','RK100001','本地','家用',255000,'无','emp1002',3,'emp1001','无意见')
insert into tblSell values('XS100002','YD100002','2006-07-25','KH100002','RK100002','本地','商用',360000,'无','emp1002',3,'emp1001','无意见')
select * from tblSell
go
--//代办信息(根据销售信息中的2个客户分别代办不同的项目,并修改收款单中的应付款金额)
insert into tblCommission values('DB100001','2006-07-25','XS100001','DBS1001',500)
insert into tblCommission values('DB100002','2006-07-25','XS100002','DBS1002',800)
select * from tblCommission
go
--//客户跟踪(跟踪1个没有销售记录的客户,状态——属实)
insert into tblTail values('GZ100001','2006-07-20','电话跟踪','KH100003','MONEDO 2.0Chia','果然是好车啊！可惜没钱买','emp1002','emp1001',1,'无意见')
insert into tblTail values('GZ100002','2006-07-25','电话跟踪','KH100003','MONEDO 2.0Chia','明年打算买','emp1002','emp1001',1,'无意见')
insert into tblTail values('GZ100003','2006-07-25','电话跟踪','KH100004','MONEDO 2.0Chia','暂时没钱','emp1003','emp1001',1,'无意见')
select * from tblTail
go
--//客户回访(回访已购买车的2个客户,类型属于购车后15天回访,状态——属实)
insert into tblVisit values('HF100001','2006-08-10','2006-08-10','拜访','HF1005','KH100001','贵公司服务质量一流','emp1002','emp1001',1,'无意见')
insert into tblVisit(vCode,vDate,viCode,cliCode) values('HF100002','2006-10-10','HF1006','KH100001')
insert into tblVisit values('HF100003','2006-08-10','2006-08-10','拜访','HF1007','KH100002','贵公司服务质量一般','emp1002','emp1001',1,'无意见')
insert into tblVisit(vCode,vDate,viCode,cliCode) values('HF100004','2006-10-10','HF1008','KH100002')
select * from tblVisit
go
--//付款信息
insert into tblShouldPayment values('FK100001','RK100001',0,248000,1,'aaa')
insert into tblShouldPayment values('FK100002','RK100002',0,350000,1,'aaa')
--//付款明细信息
insert into tblPayment values('FKM100001','FK100001','2006-10-10','aaa',248000,'emp1002','emp1001',1,'bbb')
insert into tblPayment values('FKM100002','FK100002','2006-10-10','aaa',350000,'emp1002','emp1001',1,'bbb')
--//收款信息
/*-----------------------------------------------------------*/
/* 添加存储过程 */
/* (需要存储过程请写在此处,存储过程名以proc_开头) */
/*-----------------------------------------------------------*/
--//客户视图(显示未购买过车的客户信息)
if exists(select * from sysobjects where name='view_TailCli')
drop view view_TailCli
go
create view view_TailCli
as
select * from tblClient where cliCode not in(select cliCode from tblPresell)
go
--//select * from view_TailCli
--◆◆◆◆◆◆◆◆◆◆◆删除车辆信息存储过程◆◆◆◆◆◆◆◆◆◆◆(采购部)
if exists(select *from sysobjects where name='proc_DelCarType')
drop proc proc_DelCarType
go
create proc proc_DelCarType
@carType varchar(40)
as
if((select count(ctCode) from tblStock where ctCode=@carType)!=0)
begin
raiserror('车辆信息已被采购表使用，不能删除',15,1)
return
end
if((select count(ctCode) from tblPresell where ctCode=@carType)!=0)
begin
raiserror('车辆信息已被预售订单使用，不能删除',15,1)
return
end
if((select count(ctCode) from tblVisitInfo where ctCode=@carType)!=0)
begin
raiserror('车辆信息已被回访规则表使用，不能删除',15,1)
return
end
if((select count(ctCode) from tblTail where ctCode=@carType)!=0)
begin
raiserror('车辆信息已被客户更综表使用，不能删除',15,1)
return
end
go
/*-----------------------------------------------------------*/
/* 添加视图 */
/* (需要视图请写在此处,视图名以view_开头) */
/*-----------------------------------------------------------*/
--◆◆◆◆◆◆◆◆◆◆◆◆◆车辆信息视图◆◆◆◆◆◆◆◆◆◆◆◆◆(采购部)
if exists (select *from sysobjects where name='view_CarInfo')
drop view view_CarInfo
go
create view view_CarInfo
as
select ctCode,ctName,ctType,ctPlace,ctCarFac,ctRePrice,ctSellPrice,tblFactory.facCode,facName,facLinkMan,facTel,facAdd,
facPos,facState,facRemark
from tblCarType inner join tblFactory
on tblCarType.facCode=tblFactory.facCode
go
select *from view_CarInfo
---◆◆◆◆◆◆◆◆◆◆◆◆◆采购单信息视图◆◆◆◆◆◆◆◆◆◆◆◆◆(采购部)
if exists(select *from sysobjects where name='view_Stock')
drop view view_Stock
go
create view view_Stock
as
select k.*,tblFactory.facName,tblFactory.facLinkMan,tblFactory.facTel,tblFactory.facAdd,tblFactory.facPos,tblFactory.facState,tblFactory.facRemark
from
(
select s.*,tblCarType.ctPlace,tblCarType.ctName,tblCarType.ctType,tblCarType.ctCarFac,tblCarType.facCode,tblCarType.ctRePrice,tblCarType.ctSellPrice
from
(
select u.*,tblUser.uName as checkName
from
(
select stoCode,stoDate,ctCode,stoColor,stoAdvance,stoPrice,stoNum='1',stoSum=stoPrice,sotRemark, tblStock.stoIdea,optCode,checkCode,
case stoState
when 0 then '未审核'
when 1 then '审核通过'
when 2 then '审核未通过'
when 3 then '已完成'
end as stocState, tbluser.uname
from tblStock inner join tbluser on tblUser.ucode=tblStock.optcode
) as u
inner join tblUser on tblUser.ucode=u.checkcode
) as s
inner join tblCarType on tblCarType.ctCode=s.ctCode
) as k
inner join tblFactory on tblFactory.facCode=k.facCode
go
select *from view_Stock where datepart(month,stoDate)='7'
---◆◆◆◆◆◆◆◆◆◆◆◆◆车辆类型信息视图◆◆◆◆◆◆◆◆◆◆◆◆◆(采购部)
if exists(select *from sysobjects where name='view_CarType')
drop view view_CarType
go
create view view_CarType
as
go
select *from tblFactory
select *from tblUser
select *from tblStock
select *from tblCarType
select *from tblUser
select *from view_Stock
select stocState from view_Stock where stoCode='CG100001'
go
--查询采购单车辆相关信息
if exists(select * from sysobjects where name='view_GetStockInfo' and type='V')
drop view view_GetStockInfo
go
create view view_GetStockInfo
as
select tblStock.stoCode,tblFactory.facCode,tblFactory.facName,tblStock.ctCode,tblCarType.ctType,tblCarType.ctPlace,tblCarType.ctCarFac,tblStock.stoColor from tblStock,tblCarType,tblFactory where tblStock.ctCode=tblCarType.ctCode
and tblCarType.facCode=tblFactory.facCode and stoState=1
go
/*项目过程中数据库若有改动，请及时通知其他人更新*/
/*每个项目模块制作一份相关的前台控件名称表，以便最后进行权限的设置和项目整合*/
/*2006年7月25日*/
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -