INSERT INTO Exam_Result(
[DID] 
, [DeptName] 
, [FeeItemName] 
, [RptItemName]
, [TextResult] 
, [Unit]
, [Range]
, [UnusualFlag]
, [ItemClass]
, [RptItemCode]
, [Checker]
, [Reporter]
, [ReportTime])
 select distinct * from (
-- 普通项目
SELECT -- TOP	20 
X.EnrollmentId AS [DID] 
, X.DepartmentName AS [DeptName] 
, X.Name AS [FeeItemName] 
, Y.Name AS [RptItemName]
, Y.TextValue AS [TextResult] 
, Y.ValueUnit AS [Unit]
, [Range]=NULL
, [UnusualFlag]=NULL
, CASE X.ItemType WHEN 1 THEN '普通' ELSE convert(varchar, X.ItemType,10)  END AS [ItemClass]
, [RptItemCode]=NULL
, [Checker]=NULL
, [Reporter]=NULL
, [ReportTime]=NULL  
FROM
	PEIS.DBO.EnrollmentFeeItem X 
	JOIN PEIS.DBO.EnrollmentExamItem Y ON X.Id = Y.EnrollmentFeeItemId 
WHERE
	X.SendOperDate IS NOT NULL AND X.RemoveOperDate IS NULL AND X.GiveUpOperDate IS NULL 
	AND Y.RemoveOperDate IS NULL AND Y.GiveUpOperDate IS NULL 
-- 检验项目
UNION
SELECT -- TOP	20  
A.EnrollmentId AS [DID] 
,A.DepartmentName AS [DeptName] 
,A.Name AS  [FeeItemName] 
,B.Name AS  [RptItemName]
,B.TextValue AS  [TextResult] 
,B.ValueUnit AS  [Unit]
,A.ItemType AS  [Range]
,B.LisFlag AS  [UnusualFlag] 

, CASE a.ItemType WHEN 3 THEN '检验' ELSE convert(varchar, a.ItemType,10)  END AS [ItemClass]
,B.LisCode AS  [RptItemCode]
, [Checker]=NULL
,B.LisVerifyOperName AS  [Reporter]
,B.LisVerifyOperDate AS  [ReportTime] 
FROM
	PEIS.DBO.EnrollmentApplicationSheetFeeItem A  
	JOIN PEIS.DBO.EnrollmentExamItem B ON A.Id = B.EnrollmentApplicationSheetFeeItemId 
WHERE
	A.SendOperDate IS NOT NULL AND A.RemoveOperDate IS NULL AND A.GiveUpOperDate IS NULL 
	AND B.RemoveOperDate IS NULL AND B.GiveUpOperDate IS NULL
-- 检查项目
UNION
SELECT -- TOP	20 
A.EnrollmentId AS [DID] 
,A.DepartmentName AS [DeptName] 
,B.Name AS  [FeeItemName] 
,A.NAME AS  [RptItemName]
,A.TextValue AS  [TextResult] 
, [Unit]=NULL
, [Range]=NULL
, [UnusualFlag]=NULL
, [ItemClass]='检查'
, [RptItemCode]=NULL
, [Checker]=NULL
,A.PacsVerifyOperName  [Reporter]
,A.PacsVerifyOperDate AS  [ReportTime] 
FROM
	PEIS.DBO.EnrollmentApplicationSheet A
	LEFT JOIN PEIS.DBO.EnrollmentApplicationSheetFeeItem B ON A.ID= B.EnrollmentApplicationSheetId 
WHERE
	A.TextValue IS NOT NULL AND A.RemoveOperDate IS NULL AND B.RemoveOperDate IS NULL
	) T ORDER BY DID,DeptName,FeeItemName
	
	
	
	

-- 更新科室code
UPDATE EXAM_PART SET DEPTCODE=T.HISCODE FROM 
(
SELECT Name	,HISCode  FROM DICT_DEPT) T
WHERE T.NAME=EXAM_PART.DeptName AND EXAM_PART.DeptCode=0141

-- 更新fid,rid,sid
UPDATE Exam_Result SET FID=T.FID FROM 
(SELECT ID AS FID, FeeItemName FROM vi_FeeItem)T WHERE Exam_Result.FeeItemName=T.FeeItemName

UPDATE Exam_Result SET rid=T.RID FROM 
(SELECT A.ID AS FID,A.FeeItemName,B.ID AS RID,B.RptItemName FROM vi_FeeItem a
left join Dict_ReportItem b ON A.ID=B.FID )T WHERE Exam_Result.fid=T.fid AND  Exam_Result.RptItemName=T.RptItemName 

-- PID
UPDATE Exam_Result
SET PID = A.ID
FROM Exam_Part A
WHERE A.EID = Exam_Result.DID
AND A.DeptName = Exam_Result.DeptName;


-- 收费项目
UPDATE Enrollment_FeeItem SET FID=T.FID FROM 
(SELECT ID AS FID, FeeItemName FROM vi_FeeItem)T WHERE Enrollment_FeeItem.FeeItemName=T.FeeItemName