PEIS/体检相关文档/数据库/2.收费项目-报告项目-体检套餐.sql

[1.]

TRUNCATE TABLE Dict_FeeItem
SET IDENTITY_INSERT dbo.Dict_FeeItem ON

 INSERT INTO Dict_FeeItem(ID, FeeItemCode, SpellCode,  Unit) 
 SELECT  
	A.Id 
	, A.HISCheckCode AS FeeItemCode,  
	A.PYM AS SpellCode, 
	A.UnitName AS Unit 
FROM
	peis.dbo.FeeItem  A 
	
-- 同步keyno	
[2.]
UPDATE Dict_FeeItem SET KeyNo=B.KEYNO
FROM Dict_FeeItem A 
LEFT JOIN  vi_HisFeeItem B ON A.FeeItemCode=B.FeeItemCode
WHERE A.FeeItemCode<>'' AND B. DeptCode in (SELECT DISTINCT HISCODE FROM peis.dbo.[Department] WHERE  StopOperDate IS NULL)

SET IDENTITY_INSERT [dbo].[Dict_FeeItem] OFF

-- HIS未开展项目
[3.]
TRUNCATE TABLE Dict_PeisFeeItem
SET IDENTITY_INSERT [dbo].[Dict_PeisFeeItem] OFF

INSERT INTO [Dict_PeisFeeItem]([ID], [FeeItemCode], [FeeItemName], [SpellCode], [Price],  [DeptName], [UnitName], [ItemClass], [Unit] )  
SELECT 
A.[ID]
	, A.HISCheckCode AS FeeItemCode
	,  A.Name AS [FeeItemName]
	, A.PYM AS [SpellCode]
	, A.CostPrice AS  [Price] 
	, A.DepartmentName AS [DeptName]
	, A.[UnitName]
	, A.ItemType AS  [ItemClass]
	, A.[UnitName] AS [Unit]
FROM
	peis.dbo.FeeItem A
	left JOIN  Dict_FeeItem B ON A.ID=B.ID 
	WHERE B.KeyNo IS NULL AND A.StopOperDate IS NULL
	
-- 更新FeeItemCode和keyno
[4.]
UPDATE Dict_PeisFeeItem SET FeeItemCode='F'+ CAST(ID as varchar) WHERE FeeItemCode IS NULL
UPDATE Dict_PeisFeeItem SET keyno ='T'+ CAST(ID as varchar)
UPDATE Dict_PeisFeeItem SET DeptCode = a. HISCode FROM Dict_Dept A WHERE Dict_PeisFeeItem.DeptName=A.Name
update Dict_FeeItem set FeeItemCode=A.FeeItemCode,keyno=A.keyno from Dict_PeisFeeItem A where A.ID=Dict_FeeItem.ID
[5.]
-- 查询重复收费项目
SELECT FeeItemCode,COUNT(FeeItemCode) FROM Dict_FeeItem GROUP BY FeeItemCode HAVING COUNT(FeeItemCode)>1
SELECT FeeItemName,COUNT(FeeItemName) FROM vi_FeeItem GROUP BY FeeItemName HAVING COUNT(FeeItemName)>1

[6.]
-- 删除重复收费项目
DELETE FROM Dict_FeeItem WHERE ID NOT IN ( SELECT MAX(ID) AS ID FROM Dict_FeeItem WHERE FeeItemCode IN (SELECT FeeItemCode FROM Dict_FeeItem GROUP BY FeeItemCode HAVING COUNT(FeeItemCode)>1) GROUP BY FeeItemCode ) AND FeeItemCode IN (SELECT FeeItemCode FROM Dict_FeeItem GROUP BY FeeItemCode HAVING COUNT(FeeItemCode)>1) 

-- 套餐同步 
[7.]
TRUNCATE TABLE Dict_Pack
SET IDENTITY_INSERT [dbo].[Dict_Pack] OFF

INSERT INTO Dict_Pack
(ID, Name, SpellCode,   Sex, Marriage, Seq) 
SELECT
Id,
	NAME ,
	PYM AS SpellCode,
	SuitableGender AS Sex,
	SuitableMarriage AS Marriage, 
	SortNo AS Seq 
FROM
	PEIS.dbo.ExamPack WHERE StopOperDate IS NULL
	
-- 套餐同步后同步对应的收费项目
[8.]
TRUNCATE TABLE Dict_PackToFeeItem
SET IDENTITY_INSERT [dbo].[Dict_PackToFeeItem] OFF
-- 
INSERT INTO [Dict_PackToFeeItem]([ID], [PID], [FID], [Seq], [CreateTime], [CreatorCode], [Creator])  
SELECT A.[Id], A.[ExamPackId] AS PID, C.ID AS FID,   A.[SortNo] AS SEQ,CreateTime= '2024-06-15', CreatorCode='0513',Creator= '赵翠回' 
FROM PEIS. [dbo].[ExamPackFeeItem] A
LEFT JOIN PEIS. [dbo].FEEITEM B ON A.FeeItemId=B.ID
LEFT JOIN vi_FeeItem C ON B.Name=C.FeeItemName AND B.HISCheckCode = C.FeeItemCode
WHERE C.ID IS NOT NULL
UNION
SELECT A.[Id], A.[ExamPackId] AS PID, C.ID AS FID,   A.[SortNo] AS SEQ,CreateTime= '2024-06-15', CreatorCode='0513',Creator= '赵翠回'
FROM PEIS. [dbo].[ExamPackFeeItem] A
LEFT JOIN PEIS. [dbo].FEEITEM B ON A.FeeItemId=B.ID
LEFT JOIN vi_FeeItem C ON B.Name=C.FeeItemName 
WHERE C.FeeItemCode LIKE 'F%'
 
-- 套餐中的收费项目无重复
 

-- 设置套餐的报告项目
[9.]
TRUNCATE TABLE Dict_ReportItem  

INSERT INTO [Dict_ReportItem]( [RptItemName], [SpellCode], [Unit], [RptItemCode],seq,FID )   
SELECT DISTINCT * FROM (
SELECT DISTINCT
	B.NAME AS RptItemName,
	B.PYM AS SpellCode,
	B.ValueUnit AS Unit,
	B.LisCode AS RptItemCode, 
	A.SortNo AS seq, 
	D.ID AS FID
FROM
	PEIS.dbo.feeitemexamitem A
	LEFT JOIN PEIS.dbo.examitem B ON A.ExamItemId= B.id
	LEFT JOIN PEIS.dbo.FEEITEM C ON A.FeeItemId=C.ID
	LEFT JOIN vi_FeeItem D ON C.Name=D.FeeItemName AND C.HISCheckCode = D.FeeItemCode
	WHERE B.StopOperDate IS NULL AND C.StopOperDate IS NULL AND D.ID IS NOT NULL
 UNION
SELECT DISTINCT
	B.NAME AS RptItemName,
	B.PYM AS SpellCode,
	B.ValueUnit AS Unit,
	B.LisCode AS RptItemCode, 
	SortNo AS seq, 
	D.ID AS FID
FROM
	PEIS.dbo.feeitemexamitem A
	LEFT JOIN PEIS.dbo.examitem B ON A.ExamItemId= B.id
	LEFT JOIN PEIS.dbo.FEEITEM C ON A.FeeItemId=C.ID
LEFT JOIN vi_FeeItem D ON C.Name=D.FeeItemName 
	WHERE B.StopOperDate IS NULL AND C.StopOperDate IS NULL AND D.ID IS NOT NULL AND D.FeeItemCode LIKE 'F%'
	) TT ORDER BY FID,Seq
	
	
	
	-- 体征词
	
	[10.]
TRUNCATE TABLE Dict_Sign
SET IDENTITY_INSERT [dbo].[Dict_Sign] ON 
INSERT INTO Dict_Sign(RID, SignName, SpellCode, Description, SevereLevel, IsDefault, IsSummary, Group1, Group2, Seq, CID)  

SELECT DISTINCT
	A.ID AS RID, 
	C.NAME AS SignName,
	C.PYM AS SpellCode,
	C.SignDetail AS Description,
	C.DiseaseLevel AS SevereLevel,
	C.IsDefaultOption AS IsDefault, 
	C.SummaryInclude AS IsSummary,
	C.FirstGroupNo AS Group1,
	C.SecondGroupNo AS Group2,
	C.SortNo AS Seq ,
	C.ConclusionId AS CID
FROM 
Dict_ReportItem A 
LEFT JOIN PEIS.DBO.ExamItem B ON B.Name=A.RptItemName 
LEFT JOIN PEIS.dbo.ExamSign C ON C.ExamItemId=B.ID 
WHERE C.NAME<>'' AND B.STOPOPERDATE IS NULL AND C.STOPOPERDATE IS NULL
ORDER BY A.ID,Seq 

SET IDENTITY_INSERT [dbo].[Dict_Sign] OFF

 [11.]
 -- 小于18.5为消瘦，体重指数位于24-27.9为超重，体重指数等于或大于28%为肥胖
 -- 体重指数30-34.9%为轻度肥胖，体重指数35-39.9%为中度肥胖，体重指数≥40%为重度肥胖
 Round((10000*[12] / ([11] *[11])), 1)	
 -- ([VALUE] < 18.5 ) ? ( 69 ) : (([VALUE] >= 18.5 && [VALUE] < 24 ) ? 68 : (([VALUE] >= 24 && [VALUE] < 28 ) ? 70 : (([VALUE] >= 28 && [VALUE] < 30 ) ? 71 : (([VALUE] >= 30 && [VALUE] < 35 ) ? 72 : (([VALUE] >= 35 && [VALUE] < 40 ) ? 73 : ( ( [VALUE] >= 40 ) ? 74 : 69 ) )))))
 
( [ VALUE ] < 18.5 ) ? ( 69 ) : (
( [ VALUE ] >= 18.5 && [ VALUE ] < 24 ) ? 68 : (
( [ VALUE ] >= 24 && [ VALUE ] < 28 ) ? 70 : (
( [ VALUE ] >= 28 && [ VALUE ] < 30 ) ? 71 : (
( [ VALUE ] >= 30 && [ VALUE ] < 35 ) ? 72 : (
( [ VALUE ] >= 35 && [ VALUE ] < 40 ) ? 73 : ( ( [ VALUE ] >= 40 ) ? 74 : 69 ) 
) ) ) ) ) 

UPDATE [Dict_ReportItem] SET
[ValueFormat] = 'Round((10000*[12] / ([11] *[11])), 1)'
, [SignFormat] = '([VALUE] < 18.5 ) ? ( 69 ) : (([VALUE] >= 18.5 && [VALUE] < 24 ) ? 68 : (([VALUE] >= 24 && [VALUE] < 28 ) ? 70 : (([VALUE] >= 28 && [VALUE] < 30 ) ? 71 : (([VALUE] >= 30 && [VALUE] < 35 ) ? 72 : (([VALUE] >= 35 && [VALUE] < 40 ) ? 73 : ( ( [VALUE] >= 40 ) ? 74 : 69 ) )))))'
where RptItemName ='体重指数'