www.pudn.com > MainPrj_CarShop.rar > dbCarsys1.sql
/*-----------------------------------------------------------*/
/* 创建数据库 */
/*-----------------------------------------------------------*/
use master
go
if exists(select * from sysdatabases where name='dbCarsys')
drop database dbCarsys
go
create database dbCarsys
go
use dbCarsys
go
/*-----------------------------------------------------------*/
/* 客户信息表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblClient')
drop table tblClient
go
create table tblClient
(
cliCode Varchar(10) primary key,
cliName Varchar(10) not null,
cliSex Bit not null,
cliPid Varchar(18) not null,
cliBirthday Datetime,
cliIncName Varchar(50),
cliIncCharacter Varchar(20),
cliIncCalling Varchar(20),
cliHeadship Varchar(20),
cliTel Varchar(13) not null,
cliPoss Varchar(6) not null,
cliAdd Varchar(100) not null
)
go
/*-----------------------------------------------------------*/
/* 代办项目表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCommissionItem')
drop table tblCommissionItem
go
create table tblCommissionItem
(
comCode Varchar(10) primary key,
comName Varchar(20) not null,
comMoney Money not null,
comRemark varchar(200)
)
go
/*-----------------------------------------------------------*/
/* 厂商信息表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblFactory')
drop table tblFactory
go
create table tblFactory
(
facCode Varchar(10) primary key, --厂商编号
facName Varchar(100) not null, --厂商名称
facLinkMan Varchar(15) not null, --联系人
facTel Varchar(13) not null, --联系电话
facAdd Varchar(50) not null, --地址
facPos Varchar(6), --邮编
facState bit, --状态（0代表暂停，1代表未暂停）
facRemark Varchar(500) --备注
)
go
/*-----------------------------------------------------------*/
/* 车辆信息表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblCarType')
drop table tblCarType
go
create table tblCarType
(
ctCode Varchar(20) primary key, --车型代码
ctName Varchar(20) not null, --车辆名称
ctType Varchar(30) not null, --车辆类型
ctPlace Varchar(100) not null, --车辆产地
ctCarFac Varchar(100) not null, --厂牌名称
facCode Varchar(10) foreign key references tblFactory(facCode),--厂商编号
ctRePrice Money not null, --采购参考价
ctSellPrice Money not null --销售参考价
)
go
select *from tblCarType
/*-----------------------------------------------------------*/
/* 仓库信息表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblDepotInfo')
drop table tblDepotInfo
go
create table tblDepotInfo
(
diCode Varchar(10) primary key,
diName Varchar(10) UNIQUE not null,
diUser Varchar(10) not null,
diAdd varchar(100) not null,
diTel Varchar(13) not null
)
go
/*-----------------------------------------------------------*/
/* 员工表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblUser')
drop table tblUser
go
create table tblUser
(
uCode Varchar(10) primary key,
uPwd Varchar(10) not null,
uName Varchar(10) not null,
uPinYin Varchar(50) not null,
uSex Bit not null,
uPid Varchar(18) not null,
uBranch Varchar(20) not null,
uJob Varchar(20) not null,
uIncumbency datetime not null,
uDimission datetime,
uState Tinyint not null,
uTel Varchar(13) not null,
uAdd Varchar(100)
)
go
/*-----------------------------------------------------------*/
/* 权限规则表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblAccess')
drop table tblAccess
go
create table tblAccess
(
uCode Varchar(10) foreign key references tblUser(uCode),
formName Varchar(20),
controlName Varchar(20) constraint pk_tblAccess_uCode_formName_controlName primary key(ucode,formName,controlName),
aaction Bit,
accInfo varchar(500)
)
go
/*-----------------------------------------------------------*/
/* 系统初始表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblOsInit')
drop table tblOsInit
go
create table tblOsInit
(
osiCode varchar(10) primary key,
osiName varchar(100) not null,
osiAdd varchar(100) not null,
osiTel varchar(100) not null,
osiWeb varchar(100) not null,
osiInDepotCode varchar(10) not null,
osiMoveCode varchar(10) not null,
osiSellCode varchar(10) not null,
osiGatheringCode varchar(10) not null,
osiCommissionCode varchar(10) not null,
osiPayCode varchar(10) not null,
osiPreCode varchar(10) not null,
osiStoCode varchar(10) not null
)
go
/*-----------------------------------------------------------*/
/* 采购单表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblStock')
drop table tblStock
go
create table tblStock
(
stoCode varchar(10) primary key, --采购编号
stoDate datetime not null, --采购日期
ctCode varchar(20) foreign key references tblCarType(ctCode) not null, --车型代码
stoState Tinyint default 0, --是否审核
stoColor varchar(20) not null, --车辆颜色
stoAdvance Money default 0, --预付款
stoPrice Money not null, --车辆价格
sotRemark varchar(500), --备注
optCode varchar(10) foreign key references tblUser(uCode) not null, --采购人员编
checkCode varchar(10) foreign key references tblUser(uCode) null, --审核人编号
stoIdea varchar(200) --审核意见
)
go
select *from tblStock
/*--------------------7.29修改采购单表------------------------*/
/*--------------------修改内容为审核人员编号和审核人意见可以为空*/
/*-----------------------------------------------------------*/
/* 入库单表 */
/*-----------------------------------------------------------*/
if exists(select * from sysobjects where name='tblInDepot')
drop table tblInDepot
go
create table tblInDepot
(
indCode Varchar(10) primary key,
indDate Varchar(20) not null,
diCode Varchar(10) foreign key references tblDepotInfo(diCode) not null,
stoCode Varchar(10) foreign key references tblStock(stoCode) not null,
indColor varchar(10) not null,
indButtom Varchar(20) not null,
indEngine Varchar(20) not null,
indEli Varchar(20) not null,
indImport Varchar(20) not null,
indVerify Varchar(20) not null,
indSelf Varchar(20) not null,
indKey Varchar(20) not null,
indKilm Varchar(20) not null,
indOutDate Varchar(20) not null,
indPrice money not null,
indTake Varchar(20) not null,
optCode Varchar(10) foreign key references tblUser(uCode) not null,
checkCode Varchar(10) foreign key references tblUser(uCode),
indState Tinyint not null,
indOutStore datetime,
indOutMan varchar(10) foreign key references tblUser(uCode),
indInfo varchar(100),
indRemark varchar(100),
)
go
/*-----------------------------------------------------------*/
/* 移库单表 */
/*-----------------------------------------------------------*/
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','福建厦门')
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日*/