USE [HWG_V8]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetViewWaitsByDeviceId]    Script Date: 08/23/2023 17:13:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*诊室屏刷新候诊队列信息调用*/
ALTER PROCEDURE [dbo].[sp_GetViewWaitsByDeviceId](@DeviceId VARCHAR(50),@DeptId VARCHAR(50),@RoomId VARCHAR(50),@DocId VARCHAR(50),@Type VARCHAR(50))
AS
-- 增加对诊室设置中ModelID和IsShowOrdinary的支持 by 迁移PLGSK医院 20201208 1/2
DECLARE @ModelID INT;
SELECT @ModelID = (CASE WHEN r.ModelID = 1 THEN 1 when r.ModelID = 2 THEN 2 ELSE 3 END) 
FROM Room r WHERE r.RoomID = @RoomId;

-- 调整对1300005开关关联的SQL语句；增加对该开关2、3、4、5号值的兼容；调整对times字段NULL值的兼容；为字段增加表别名，防止再读错字段 by 2020.04.26
-- 将"隐藏患者姓名第二个字"开关，处理成按科室配置 by 20200427
SELECT DISTINCT q.DepartmentName,q.RoomName,q.DoctorName,q.times, q.EmergencyLevel, q.CheckInTime,
  (
			--隐藏患者姓名第二个字;0:不隐藏;1:隐藏;2:名字+号数(不隐藏);3:名字+号数(隐藏);4:号数+姓名(不隐藏);5:号数+姓名(隐藏)
		--CASE (SELECT ParamValue FROM Configs WHERE ParamCode = '1300005')
		-- 将"隐藏患者姓名第二个字"开关，处理成按科室配置 by 20200427
		CASE (SELECT TOP 1 CASE WHEN cf.ParamValue IN ('0', '2', '4') THEN '0' /* 下面不好处理，统一在这里处理，ParamValue只输出0和1 */
		                   WHEN cf.ParamValue IN ('1', '3', '5') THEN '1'
		                   ELSE cf.ParamValue END AS ParamValue
		            FROM Configs cf WHERE cf.ParamCode = '1300005' and (cf.DepartmentID='' OR cf.DepartmentID=q.DepartmentID) ORDER BY cf.DepartmentID DESC
		      )
			WHEN '0' THEN 
			(CASE q.StateID  
			  --WHEN '0' THEN '已约('+ISNULL(q.times,' ')+')'
			  WHEN '0' THEN (CASE q.times WHEN '' THEN '已约' ELSE '已约('+q.times+')' END) 
			  ELSE ISNULL(q.PatiInfoName+(CASE q.times WHEN '' THEN '' ELSE '('+q.times+')' END), q.PatiInfoName) END)
			
			WHEN '1' THEN 
			(CASE q.StateID  
			  --WHEN '0' THEN '已约('+ISNULL(q.times,'')+')'
			  WHEN '0' THEN (CASE q.times WHEN '' THEN '已约' ELSE '已约('+q.times+')' END)
			  ELSE STUFF(
			  ISNULL(q.PatiInfoName+(CASE q.times WHEN '' THEN '' ELSE '('+q.times+')' END), q.PatiInfoName)
			  , 2, 1, '*')END)
			ELSE (q.PatiInfoName) END
	 ) AS PatiInfoName, 
q.PrintQueueNO,
q.StateName,q. StateID, CAST(q.RegDate AS DATE) AS RegDate, q.QueueNO, q.QueueOrder, q.SerialNumber,
(
	CASE q.SourceID     --(SELECT SourceID FROM Queue WHERE SerialNumber=q.SerialNumber)
	WHEN '040100101' THEN '老人'
	WHEN '040100102' THEN '军人'
	WHEN '040100103' THEN '复诊'
	WHEN '040100104' THEN '转诊'
	WHEN '040100105' THEN '加急'
	WHEN '040100106' THEN '绿色通道'
	WHEN '040100107' THEN '憋尿'
	WHEN '040100108' THEN '过号'
	ELSE '' END 
	) sStateID
FROM Queue q with (nolock)
left JOIN MRoom  mr ON mr.DoctorID =q.DoctorID-- 找到登录的医生
left JOIN  Room r ON r.RoomID = mr.RoomID -- 找到登录医生的诊室id
--INNER JOIN MTerminal mt ON (mt.AddressID = q.DepartmentID or mt.AddressID = r.RoomID)
-- INNER JOIN Room r ON r.RoomID=q.RoomID
WHERE 1=1
--mt.DeviceId=@DeviceId 
--(r.RoomID = @RoomId or q.DoctorID = @DocId) 

-- 屏蔽下面一行关于RoomID和DoctorID的判断，改为下面2行，合并自PLGSK医院 by 20201208 1/2
AND (((@ModelID=1 AND q.DoctorID=@DocId) OR (@ModelID!=1 AND ISNULL(q.DoctorID,'-1')='-1'))
OR ((@ModelID=2 AND q.RoomID=@RoomId) OR (@ModelID!=2 AND ISNULL(q.RoomID,'-1')='-1')))
AND (q.RoomID = @RoomId or q.DoctorID = @DocId or (q.RoomID is null and q.DoctorID is null)) 
--AND (q.RoomID =@RoomID OR q.DoctorID =@DocID OR (q.RoomID IS NULL AND q.DoctorID IS NULL ) OR (q.DepartmentID=@DeptID AND q.DoctorID IS NULL ))



AND (StateID IN ( 1,2,5) ----诊室屏是否显示预约未报到的患者(1500005)
  OR StateID=(CASE (SELECT ParamValue FROM Configs WHERE ParamCode = '1500005') when '1' then 0 else 1 end))
--AND Q.StateID IN (0,1,2,3,4,5,6) 
AND q.DepartmentID =@DeptId
AND q.ValidDate = CONVERT(DATE, GETDATE())
AND q.IsDelete=0
-- 排序的条件与医生呼叫端不一致，少了两个条件 20201214
-- ORDER BY CAST(q.RegDate AS DATE), CONVERT(INT, q.QueueNO) ASC, q.QueueOrder DESC
ORDER BY CAST(q.RegDate AS DATE), q.EmergencyLevel asc, CONVERT(INT, q.QueueNO) ASC, q.CheckInTime, q.QueueOrder DESC