/*
RoomCode_time
RoomCode_BringID	
drop table RoomCode	
sp_GetRoomCode '177.177.0.13',1
select * from RoomCode
select * from businesslogsnew 
select * from rooms
delete from RoomCode
*/

ALTER proc sp_GetRoomCode
	@room_ip varchar(20),	--房台IP	
	@CanSong int = 0		--不开台就能点歌 1：不开台就能点歌
as

declare @RoomIsValid int --房间状态
declare @LogID int --活动账单号
declare @RoomCode varchar(60) 
declare @BringID int --带客单号
set @RoomCode = ''

if @CanSong = 1 --不开台就能点歌曲
begin
	--删除超期的RoomCode，默认24小时
	delete from RoomCode where datediff(hh,RoomCode_time,getdate())>=24 

	if not exists(select * from RoomCode where RoomIP = @room_ip) --如果不存在Roomcode
	begin
		--生成随机码
		while len(@RoomCode) = 0
		begin
			select @RoomCode = cast( floor(rand()*1000000) as int)
			if exists(select * from RoomCode where RoomCode = @RoomCode)	--如果生成随机数重复，重新生成
				set @RoomCode = ''
		end

		insert into RoomCode values(@RoomCode,@room_ip,null,null,null,getdate(),null)
	end	

	select * from RoomCode where RoomIP = @room_ip
	return
end


--begin需要开台才能点歌

--读取房间状态
select @RoomIsValid = Room_IsValid from rooms where room_ip = @room_ip and Room_DelState = 0
if len(@RoomIsValid) = 0 return -1 --房间不不存

--房台状态 0：空闲，1：预订，2:带客，3：使用，4：余时，5：超时，6：满低消，7：已付，8：打单，9：结账，10：清扫，11：禁用
if @RoomIsValid in (0,1,10,11)--非活动账单
begin
	delete from RoomCode where RoomIP = @room_ip
	return -1 --房间状态不可用。
end

if @RoomIsValid in(3,4,5,6,7,8,9) --正常活动账单
begin
	--取出账单号
	select @LogID = Businesslog_ID,@BringID = BusinessLog_BringID from rooms,BusinessLogsnew
		where Room_ID = Businesslog_Roomid and room_ip = @room_ip and Businesslog_IsValid in(1,2) and Room_DelState = 0
	if len(@LogID) = 0 return -2 --房间状态错误

	--判断是否已经存在房台码
	if not exists(select * from RoomCode where RoomIP = @room_ip and (RoomCode_BringID = @BringID or Room_LogID = @LogID))
	begin
		--删除过期roomcode
		delete from RoomCode where RoomIP = @room_ip
		--生成随机码
		while len(@RoomCode) = 0
		begin
			select @RoomCode = cast( floor(rand()*1000000) as int)
			if exists(select * from RoomCode where RoomCode = @RoomCode)	--如果生成随机数重复，重新生成
				set @RoomCode = ''
		end

		insert into RoomCode values(@RoomCode,@room_ip,@LogID,null,null,getdate(),null)
	end

end

if @RoomIsValid = 2 --带客单
begin
	select @RoomIsValid = Room_IsValid,@BringID = Bringinfo_ID from rooms,Bringinfo
	where Room_ID = Bringinfo_RoomID and room_ip = @room_ip and Bringinfo_State = 0 and Room_DelState = 0

	if @RoomIsValid is null return -1 --房间不不存在或不存在活动账单

	if not exists(select * from RoomCode where RoomIP = @room_ip and RoomCode_BringID = @BringID) --如果活动账单下不存在Roomcode
	begin
		--删除过期roomcode
		delete from RoomCode where RoomIP = @room_ip
		--生成随机码
		while len(@RoomCode) = 0
		begin
			select @RoomCode = cast( floor(rand()*1000000) as int)
			if exists(select * from RoomCode where RoomCode = @RoomCode)	--如果生成随机数重复，重新生成
				set @RoomCode = ''
		end
		insert into RoomCode values(@RoomCode,@room_ip,null,null,null,getdate(),@BringID)
	end
end

--返回房台码
select * from RoomCode where RoomIP = @room_ip