当前位置 : 技术 / 集团公司监控中心针对安全例检修改

集团公司监控中心针对安全例检修改

修改从安全例检系统中获取车辆安检状态

USE [SDCTMonitor]
GO
/****** Object: StoredProcedure [dbo].[spRayBusCheckInStation] Script Date: 09/06/2013 08:45:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER procedure [dbo].[spRayBusCheckInStation]
@bus varchar(20),
@date datetime,
@result varchar(500) output
AS
begin
 
SET @result=''
declare @cnt int
declare @busid uniqueidentifier
declare @strbusRunType varchar(10)
declare @busUseType varchar(50)
SELECT @busid = a.sysId,@strbusRunType=b.bdname,@busUseType=c.bdName FROM tbBus a
JOIN tbbaseData b ON b.sysID=a.busRunType
JOIN tbBaseData c ON a.busUseType = c.sysID
WHERE a.busCardNo=@bus
 
IF NOT EXISTS (SELECT * FROM tbBus WHERE busCardNo=@bus)
SET @result = '车辆不存在'
else IF EXISTS(SELECT * FROM tbBus WHERE busCardNo=@bus AND busIsSale=1)
SET @result ='车辆已转卖'
else IF EXISTS(SELECT * FROM tbBus WHERE busCardNo=@bus AND busIsScrap=1)
SET @result = '车辆已报废'
else IF @busUseType <> '营运客车'
SET @result = ''
else
begin
SELECT @cnt=count(*)
FROM tbBusStop a
JOIN tbBus b ON a.bsBus = b.sysID
WHERE (@date >=bsStopDate AND @date < bsRunDate AND b.busIsStop=0 AND bsBus=@busid)
OR (@date >=bsStopDate AND bsRunDate IS NULL AND b.busIsStop=1 AND bsBus=@busid)
IF @cnt >0
SET @result = '车辆已停运'
else
begin
declare @lastcm datetime,@nextcm datetime,@lastMCheck datetime
SELECT @lastcm=busLastCm,@nextcm=busNextcm,@lastMCheck=busLastMonthCheck
FROM tbBus
WHERE sysid=@busid
SET @result = ''
IF @lastcm IS NULL
SET @result = @result+'车辆没有进行过二级维护' + char(13)
else IF @nextcm <=@date
SET @result = @result +'车辆二级维护已过期'+ char(13)
-- if @lastmcheck is null
-- set @result = @result+'车辆没有进行过月检' + char(13)
--else
begin
SELECT @cnt = count(*)
FROM tbBusMonthCheck
WHERE LEFT(convert(varchar,bmcDate,12),4)=LEFT(convert(varchar,dateadd(m,-1,@date),12),4)
AND bmcBus=@busid
IF @cnt = 0
SET @result = @result+'车辆上月没有进行过月检' + char(13)
end
else
BEGIN
DECLARE @shbdc bit
EXEC OPENDATASOURCE('SQLOLEDB','Data Source=192.168.160.24;User ID=bdc;Password=bdc').SHRemote001..spGetPDAChkResult
@bus,@date,@shbdc
IF @shbdc=0 --如果驷惠软件中不合格则判断现有系统中的
begin
IF @strbusRunType='集约' -- 原有的安全例检
begin
SELECT *
FROM tbBusDayCheck a
LEFT JOIN tbBus b ON a.bdcBus=b.sysID
WHERE bdcBus=@busid AND bdcDate = @date
IF @@rowcount<=0
SET @result = @result + '车辆未进行安全例检'+char(13)
else
begin
declare @s varchar(400)
SET @s = ''
SELECT DISTINCT @s=@s+','+b.bdName
FROM tbBusDayCheck a
LEFT JOIN tbBus d ON a.bdcBus=d.sysid
LEFT JOIN tbBaseData b ON a.bdcType=b.sysid
LEFT JOIN tbBaseData c ON d.busRunType=c.sysID
WHERE bdcBus=@busID AND bdcDate = @date AND bdcFlag = 0 AND c.bdName='集约'
IF @s <> ''
SET @result = @result + '车辆安全例检为未通过项目:'+@s
end
end
end
END
end
end
print @result
end

标签: 私密, work, sql, 公司

添加新评论