预约会议sql
CREATE proc sp_MeetingCheck_Test
@serialno varchar(max)='', ---- 主档serialno
@title nvarchar(200)='',--会议主题
@buser nvarchar(50)='' --建档人
as
declare @Result varchar(max)
declare @sdate datetime
declare @edate datetime
declare @stime datetime
declare @etime datetime
declare @shour nvarchar(50)
declare @ehour nvarchar(50)
declare @sdate1 datetime
declare @edate1 datetime
declare @stime1 datetime
declare @etime1 datetime
declare @shour1 nvarchar(50)
declare @ehour1 nvarchar(50)
declare @adress nvarchar(100)
--INSERT INTO A1(A1) VALUES('1')
set xact_abort on
begin transaction --select * from A1 delete from A1
begin
set @Result='SUCCESS'
-----------获取当前单据相关数据------
select
@sdate=MeetingDate,
@edate=MeetingDate2,
@stime=cast(convert(varchar(50),MeetingDate,23)+' '+startDatetime as datetime),
@etime=cast(convert(varchar(50),MeetingDate2,23)+' '+endDatetime as datetime),
@shour=startDatetime,
@ehour=endDatetime,
@adress=MeetingAdress
from t_OA_meetingApply where serialno=@serialno
------------对比最前已审核10张单,用游标做对比-----
select top 10
MeetingDate,isnull(MeetingDate2,MeetingDate) MeetingDate2,
cast(convert(varchar(50),MeetingDate,23)+' '+startDatetime as datetime) stime1,
cast(convert(varchar(50),isnull(MeetingDate2,MeetingDate),23)+' '+endDatetime as datetime) etime1,
startDatetime,endDatetime
into #mttmp1 from t_OA_meetingApply where MeetingAdress=@adress
and checked=1 order by stime1 desc
--------------------------------------
declare cur cursor for
select MeetingDate,MeetingDate2,stime1,etime1,startDatetime,endDatetime from #mttmp1
for read only
open cur
fetch cur into @sdate1,@edate1,@stime1,@etime1,@shour1,@ehour1
while @@fetch_status=0
begin
--set @Result='SUCCESS'
--@sdate1,@edate1 不带时间
--@stime1,@etime1 带时间
--@shour1,@ehour1 小时与分钟 字串 如09:30
if(@sdate=@edate) --日期相同
begin
--if(@edate=@sdate1 or @edate=@edate1)
--begin
if(@stime=@etime1 or @etime=@etime1) --相等时间
begin
set @Result='会议时间有重复,不能预定!'
end
else if((@stime>@stime1 and @stime<@etime1) and (@etime>@stime1 and @etime<@etime1)) --中间时间
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@stime1 and @etime>@etime1) --两边时间
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@stime1 and @etime>@stime1)
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@etime1 and @etime>@etime1)
begin
set @Result='会议时间有包含,不能预定!'
end
else if((@stime<@stime1 and @etime<@stime1) or (@stime>@etime1 and @etime>@etime1))
begin
set @Result='SUCCESS'
end
else
begin
set @Result='SUCCESS'
end
if(@Result<>'SUCCESS')
begin
--return
break
end
--end
end
@serialno varchar(max)='', ---- 主档serialno
@title nvarchar(200)='',--会议主题
@buser nvarchar(50)='' --建档人
as
declare @Result varchar(max)
declare @sdate datetime
declare @edate datetime
declare @stime datetime
declare @etime datetime
declare @shour nvarchar(50)
declare @ehour nvarchar(50)
declare @sdate1 datetime
declare @edate1 datetime
declare @stime1 datetime
declare @etime1 datetime
declare @shour1 nvarchar(50)
declare @ehour1 nvarchar(50)
declare @adress nvarchar(100)
--INSERT INTO A1(A1) VALUES('1')
set xact_abort on
begin transaction --select * from A1 delete from A1
begin
set @Result='SUCCESS'
-----------获取当前单据相关数据------
select
@sdate=MeetingDate,
@edate=MeetingDate2,
@stime=cast(convert(varchar(50),MeetingDate,23)+' '+startDatetime as datetime),
@etime=cast(convert(varchar(50),MeetingDate2,23)+' '+endDatetime as datetime),
@shour=startDatetime,
@ehour=endDatetime,
@adress=MeetingAdress
from t_OA_meetingApply where serialno=@serialno
------------对比最前已审核10张单,用游标做对比-----
select top 10
MeetingDate,isnull(MeetingDate2,MeetingDate) MeetingDate2,
cast(convert(varchar(50),MeetingDate,23)+' '+startDatetime as datetime) stime1,
cast(convert(varchar(50),isnull(MeetingDate2,MeetingDate),23)+' '+endDatetime as datetime) etime1,
startDatetime,endDatetime
into #mttmp1 from t_OA_meetingApply where MeetingAdress=@adress
and checked=1 order by stime1 desc
--------------------------------------
declare cur cursor for
select MeetingDate,MeetingDate2,stime1,etime1,startDatetime,endDatetime from #mttmp1
for read only
open cur
fetch cur into @sdate1,@edate1,@stime1,@etime1,@shour1,@ehour1
while @@fetch_status=0
begin
--set @Result='SUCCESS'
--@sdate1,@edate1 不带时间
--@stime1,@etime1 带时间
--@shour1,@ehour1 小时与分钟 字串 如09:30
if(@sdate=@edate) --日期相同
begin
--if(@edate=@sdate1 or @edate=@edate1)
--begin
if(@stime=@etime1 or @etime=@etime1) --相等时间
begin
set @Result='会议时间有重复,不能预定!'
end
else if((@stime>@stime1 and @stime<@etime1) and (@etime>@stime1 and @etime<@etime1)) --中间时间
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@stime1 and @etime>@etime1) --两边时间
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@stime1 and @etime>@stime1)
begin
set @Result='会议时间有包含,不能预定!'
end
else if(@stime<@etime1 and @etime>@etime1)
begin
set @Result='会议时间有包含,不能预定!'
end
else if((@stime<@stime1 and @etime<@stime1) or (@stime>@etime1 and @etime>@etime1))
begin
set @Result='SUCCESS'
end
else
begin
set @Result='SUCCESS'
end
if(@Result<>'SUCCESS')
begin
--return
break
end
--end
end
posted on 2015-01-15 16:37 XiNanHeiShao 阅读(259) 评论(0) 编辑 收藏 举报