www.pudn.com > MainPrj_CarShop.rar > dbCarsys.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,
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
/*-----------------------------------------------------------*/
/* 仓库信息表 */
/*-----------------------------------------------------------*/
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,
diIsStop nvarchar(1) 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) not null,
stoIdea varchar(200),
)
go
/*-----------------------------------------------------------*/
/* 入库单表 */
/*-----------------------------------------------------------*/
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) ,
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,
prePrice 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 tblInDepot(indCode) 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')
insert into tblUser(uCode,uPwd,uName,uPinYin,uSex,uPid,uBranch,uJob,uIncumbency,uState,uTel) values('emp1008','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('emp1009','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('emp10010','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('emp10011','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('emp10012','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('emp10013','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('emp10014','123456','客户经理','laoer',0,'350203198212034444','客户部','客户经理','2006-07-01',1,'0592-5583388')
select * from tblUser
go
--//权限信息(注：项目最后在处理)
--//客户信息(3个客户)
insert into tblClient values('KH100000','null',0,'000000000000000000','1980-01-01','null','null','null','null','0592-1111111','null','null')
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
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('DBS1000','null',0,'null')
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')
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
--//移库信息(注：简单的业务不添加数据了)
insert into tblMove values('YK10001','2006-7-20','RK100001','CK1001','CK1002','emp1002','没办法');
insert into tblMove values('YK10002','2006-7-23','RK100002','CK1002','CK1001','emp1001','没出息');
go
--//预售信息(2个客户订购仓库内不同的车,并且2张预售单状态都是'已审核已通过',不产生收款单,只开出定金收据)
insert into tblPresell values('YD100000','2006-07-20','KH100000','PASSAT 1.8T','null',0,0,'emp1000','null',0,'emp1000','null')
insert into tblPresell values('YD100001','2006-07-20','KH100001','PASSAT 1.8T','银白色',10000,250000,'emp1002','已支付定金10000 RMB',2,'emp1001','无意见')
insert into tblPresell values('YD100002','2006-07-20','KH100002','PASSAT 2.0','黑色',10000,350000,'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
/*-----------------------------------------------------------*/
/* 添加视图 */
/* (需要视图请写在此处,视图名以view_开头) */
/*-----------------------------------------------------------*/
--◆◆◆◆◆◆◆◆◆◆◆删除车辆信息存储过程◆◆◆◆◆◆◆◆◆◆◆(采购部)
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
select distinct ctType from tblCarType
go
--//付款视图
if exists(select * from sysobjects where name='view_payment')
drop view view_payment
go
create view view_payment
as
select payDate,payBillCode,paySummary,spMoney,payMoney from tblShouldPayment
inner join tblInDepot on tblShouldPayment.indCode=tblInDepot.indCode
inner join tblPayment on tblShouldPayment.spCode=tblPayment.payCode
go
---◆◆◆◆◆◆◆◆◆◆◆◆◆车辆库存信息视图◆◆◆◆◆◆◆◆◆◆◆◆◆(采购部)
if exists(select *from sysobjects where name='view_CarKuCun')
drop view view_CarKuCun
go
create view view_CarKuCun
as
SELECT tblInDepot.indCode AS 库存单号, tblInDepot.indDate AS 入库日期,
tblStock.stoCode AS 采购单号, tblStock.stoDate AS 采购日期,
tblFactory.facName AS 厂商名称, tblStock.ctCode AS 车型代码,
tblCarType.ctName AS 车辆名称, tblCarType.ctType AS 车辆类型,
tblCarType.ctPlace AS 产地, tblCarType.ctCarFac AS 车辆厂牌名称,
tblInDepot.indColor AS 车辆颜色, tblInDepot.indButtom AS 底盘号,
tblInDepot.indEngine AS 发动机号, tblInDepot.indEli AS 合格证号,
tblInDepot.indImport AS 进口证号, tblInDepot.indVerify AS 商检单号,
tblInDepot.indSelf AS 自编号, tblInDepot.indKey AS 钥匙号,
tblInDepot.indKilm AS 行驶里程, tblInDepot.indOutDate AS 出厂日期,
tblInDepot.indTake AS 提单号, tblInDepot.indPrice AS 进价,
tblCarType.ctRePrice AS 采购参考价, tblCarType.ctSellPrice AS 销售参考价,
tblInDepot.indRemark AS 备注
FROM tblInDepot INNER JOIN
tblStock ON tblInDepot.stoCode = tblStock.stoCode INNER JOIN
tblCarType ON tblStock.ctCode = tblCarType.ctCode INNER JOIN
tblFactory ON tblCarType.facCode = tblFactory.facCode
WHERE (tblInDepot.indState = 1)
GROUP BY tblFactory.facName, tblStock.ctCode, tblInDepot.indPrice, tblCarType.ctRePrice,
tblCarType.ctSellPrice, tblInDepot.indCode, tblInDepot.indDate, tblStock.stoCode,
tblStock.stoDate, tblCarType.ctName, tblCarType.ctType, tblCarType.ctPlace,
tblCarType.ctCarFac, tblInDepot.indColor, tblInDepot.indButtom, tblInDepot.indEngine,
tblInDepot.indEli, tblInDepot.indImport, tblInDepot.indVerify, tblInDepot.indSelf,
tblInDepot.indKey, tblInDepot.indKilm, tblInDepot.indOutDate, tblInDepot.indTake,
tblInDepot.indRemark
go
select * from view_CarKuCun
go
--/*/////////////////////////////////////////客户跟踪，回访/////////////////////////////////////////*/
--//客户视图(显示未购买过车的客户信息)
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
--//客户视图(显示已购买过车的客户信息)
if exists(select * from sysobjects where name='view_VisCli')
drop view view_VisCli
go
create view view_VisCli
as
select * from tblClient where cliCode in(select cliCode from tblSell where selState=3)
go
--//跟踪记录高级查询视图
if exists(select * from sysobjects where name='view_TailSearch')
drop view view_TailSearch
go
create view view_TailSearch
as
select taiCode,taiDate,taiFashion,ctCode,taiNote,tblClient.cliCode,cliName,cliSex,cliHeadShip,cliTel,cliAdd from tblTail inner join tblClient on tblTail.cliCode=tblClient.cliCode
go
--/////////////////////////////////////////////预售，销售///////////////////////////////////////
--//预售管理中需要的视图
if exists(select * from sysobjects where name='view_preSell')
drop view view_preSell
go
create view view_preSell
as
select tblPresell.preDate,tblPresell.preCode,tblClient.cliCode,tblClient.cliName,tblClient.cliPid,
cliSex=case
when tblClient.cliSex=1 then '男'
else '女'
end
,tblClient.cliBirthday,tblClient.cliIncCharacter,
tblClient.cliIncCalling,tblClient.cliHeadship,tblClient.cliTel,tblClient.cliPoss,tblClient.cliAdd,tblCarType.ctCode,tblPresell.ctColor,tblCarType.ctCarFac,
tblCarType.ctType,tblCarType.ctPlace,tblPresell.prePrice,tblPresell.preEarnest,tblUser.uName,tblPresell.preRemark,
preState=case
when tblPresell.preState=1 then '已审核未通过'
when tblPresell.preState=2 then '已审核已通过'
when tblPresell.preState=3 then '已完成'
else '未审核'
end
,tblPresell.preIdea
from tblPresell
inner join tblClient
on tblPresell.cliCode = tblClient.cliCode
inner join tblCarType
on tblPresell.ctCode = tblCartype.ctCode
inner join tblUser
on tblPresell.optCode = tblUser.uCode
go
select * from view_preSell
go
--//车辆信息
if exists(select * from sysobjects where name='view_PreCarInfo')
drop view view_PreCarInfo
go
create view view_PreCarInfo
as
select tblCarType.ctCode,tblCarType.ctType,tblCarType.ctPlace,tblCarType.ctCarFac,tblStock.stoColor
from tblCarType
inner join tblStock
on tblCarType.ctCode = tblStock.ctCode
go
--//视图:显示[已审核已通过]的预售信息
if exists(select * from sysobjects where name='view_getReadySell')
drop view view_getReadySell
go
create view view_getReadySell
as
select * from tblPresell where preState=2
go
select * from view_getReadySell
go
--//视图：销售查询
if exists(select * from sysobjects where name='view_SellSearch')
drop view view_SellSearch
go
create view view_SellSearch
as
select tblClient.cliCode,cliName,cliSex=
case when cliSex = 1 then '男'
else '女'
end
,cliPid,cliHeadship,cliTel,cliAdd,
selCode,preCode=
case when preCode='YD100000' then '空'
else preCode
end
,selDate,tblSell.indCode,tblStock.ctCode,selPrice
from tblClient
inner join tblSell on tblClient.cliCode=tblSell.cliCode
inner join tblInDepot on tblSell.indCode=tblInDepot.indCode
inner join tblStock on tblInDepot.stoCode=tblStock.stoCode
go
select * from view_SellSearch
go
--//视图:仓库信息
if exists(select * from sysobjects where name='view_IndInfo')
drop view view_IndInfo
go
create view view_IndInfo
as
select tblInDepot.indCode,tblStock.ctCode,tblCarType.ctType,tblCarType.ctPlace,tblCarType.ctCarFac,
tblInDepot.indColor,indButtom,indEngine,indEli,indImport,indVerify,indTake,indKey,indKilm,indOutDate,indPrice
from tblInDepot
inner join tblStock on tblInDepot.stoCode=tblStock.stoCode
inner join tblCarType on tblCarType.ctCode=tblStock.ctCode
where indState=1
go
select * from view_IndInfo
go
--销售表-连接-入库表的视图
if exists(select * from sysobjects where name='view_tblSell_Join_tblInDepot' and type='V')
drop view view_tblSell_Join_tblInDepot;
go
create view view_tblSell_Join_tblInDepot
as
select tblSell.selCode,tblInDepot.indCode from tblSell inner join tblInDepot
on tblInDepot.indCode=tblSell.indCode
go
select * from view_tblSell_Join_tblInDepot
--查询采购单车辆相关信息
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日*/