dbcarsys2.sql

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

	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
--/////////////////////////////////////////////预售，销售///////////////////////////////////////

--//预售管理中需要的视图
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
--//车辆信息
create view view_CarInfo
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日*/