PEIS/体检相关文档/数据库/收费项目数据.sql

-- 收费项目
-- 0、全部导入
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  
SET IDENTITY_INSERT dbo.Dict_FeeItem OFF
-- 套餐收费项目
TRUNCATE TABLE [Dict_PackToFeeItem] 
SET IDENTITY_INSERT dbo.[Dict_PackToFeeItem] ON   
 INSERT INTO [Dict_PackToFeeItem]
([ID], [PID], [FID], [Seq], [CreateTime], [CreatorCode], [Creator])  
SELECT[Id], [ExamPackId] AS PID, [FeeItemId] AS FID,   [SortNo] AS SEQ,CreateTime= '2024-05-24', CreatorCode='0513',Creator= '赵翠回'
FROM PEIS. [dbo].[ExamPackFeeItem]
SET IDENTITY_INSERT dbo.[Dict_PackToFeeItem] OFF

-- 数据重置完成后
-- 根据FeeItemCode对照HIS 同步keyno
update [Dict_FeeItem] set keyno =b.keyno  -- select * 
from Dict_FeeItem a 
left join vi_HisFeeItem b on a.FeeItemCode=b.feeitemcode 
where  b.DeptCode in ( select DISTINCT HISCode from Dict_Dept  ) -- and a.KeyNo is null 

-- 1、 HIS中未开展的项目-改为自定义项目，修改自定义编码
TRUNCATE TABLE [Dict_PeisFeeItem] 
SET IDENTITY_INSERT dbo.[Dict_PeisFeeItem] ON   
 
INSERT INTO [dbo].[Dict_PeisFeeItem]([ID], [FeeItemCode], [FeeItemName], [SpellCode], [Price], [DeptCode], [DeptName], [UnitName], [ItemClass], [Unit], [KeyNo] ) 
select ID
,  'F'+ CAST(ID as varchar) AS [FeeItemCode]
, name as [FeeItemName]
,PYM as [SpellCode]
,CostPrice as [Price]
,'' as [DeptCode]
,DepartmentName  as [DeptName]
,UnitName
,ItemType  as [ItemClass]
,UnitName as [Unit]
,'T'+ CAST(ID as varchar) AS  [KeyNo] 
FROM  PEIS.dbo.FeeItem  WHERE ID IN (SELECT id FROM [Dict_FeeItem] WHERE KeyNo is null or keyno='')
SET IDENTITY_INSERT dbo.[Dict_PeisFeeItem] OFF   
-- 更改keyno
update [Dict_FeeItem] set keyno ='T'+ CAST(ID as varchar) ,FeeItemCode='F'+ CAST(ID as varchar)
WHERE KeyNo is null or keyno=''
 -- 更新 ItemClass
 UPDATE [Dict_PeisFeeItem] SET ItemClass='普通' WHERE ItemClass='1' or ItemClass is null
 UPDATE [Dict_PeisFeeItem] SET ItemClass='检查' WHERE ItemClass='2'
 UPDATE [Dict_PeisFeeItem] SET ItemClass='检验' WHERE ItemClass='3'

-- 2、 收费项目对应的报告项目
-- 收费项目对应的报告项目
TRUNCATE TABLE [Dict_ReportItem] 
SET IDENTITY_INSERT dbo.[Dict_ReportItem] ON  

INSERT INTO [dbo].[Dict_ReportItem]
([ID], [RptItemName], [SpellCode], [Unit],  RptItemCode)   
SELECT  
	ID
	,NAME as RptItemName
	,PYM as SpellCode
	,ValueUnit as Unit
	,LisCode as RptItemCode  
FROM  PEIS.dbo.ExamItem 
SET IDENTITY_INSERT dbo.[Dict_ReportItem] OFF
-- 更新FID
UPDATE [Dict_ReportItem] SET  FID= TA.FEEITEMID FROM 
(
SELECT MIN(FEEITEMID) AS FEEITEMID,A.ID  FROM
	PEIS.dbo.ExamItem A
	 JOIN PEIS.dbo.FeeItemExamItem   B ON A.ID=B.ExamItemID 
	 GROUP BY  A.ID) AS TA
	 WHERE [Dict_ReportItem].ID=TA.ID
-- 查找未存在的FID对应的报告项目插入
INSERT INTO [dbo].[Dict_ReportItem]
([FID], [RptItemName], [SpellCode], [Unit],  RptItemCode)  
SELECT 
	A.FeeItemId AS FID
	,NAME as RptItemName
	,PYM as SpellCode
	,ValueUnit as Unit
	,LisCode as RptItemCode 
FROM
	PEIS.dbo.FeeItemExamItem A 
	 JOIN PEIS.dbo.ExamItem   B ON A.ExamItemID =B.ID
	 WHERE NOT   EXISTS (SELECT 1 FROM PEISDB.DBO.Dict_ReportItem WHERE FID=A.FEEITEMID AND RptItemName=B.NAME)

-- 3、 套餐对应收费项目关系修改
 
TRUNCATE TABLE Dict_Pack
SET IDENTITY_INSERT [dbo].[Dict_Pack] On 
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
SET IDENTITY_INSERT [dbo].[Dict_Pack] Off

TRUNCATE TABLE Dict_PackToFeeItem
SET IDENTITY_INSERT [dbo].[Dict_PackToFeeItem] On 
INSERT INTO [Dict_PackToFeeItem]
([ID], [PID], [FID], [Seq], [CreateTime], [CreatorCode], [Creator])  
SELECT[Id], [ExamPackId] AS PID, [FeeItemId] AS FID,   [SortNo] AS SEQ,CreateTime= '2024-05-24', CreatorCode='0513',Creator= '赵翠回'
FROM PEIS. [dbo].[ExamPackFeeItem]
SET IDENTITY_INSERT [dbo].[Dict_PackToFeeItem] OFF


UPDATE Exam_Result SET RID = TA.RID  
FROM (
SELECT A.ID AS FID,B.RptItemName,B.ID AS RID FROM Dict_FeeItem  A  
                LEFT JOIN Dict_ReportItem B ON B.FID = A. ID) TA
				WHERE Exam_Result.FID=TA.FID AND Exam_Result.RptItemName=TA.RptItemName
--SELECT * FROM  vi_FeeItem

-- 4、 收费项目重复 ，删除重复项目 ，修改全部对应表的FID








SELECT * FROM dbo.Dict_FeeItem   -- where FeeItemCode<>''
SELECT top 200 * FROM peis.dbo.FeeItem -- where HISCheckCode<>''

select * from Dict_FeeItem a left join Dict_ReportItem b on a.id=b.fid where b.fid=44


--
SELECT * FROM Dict_FeeItem  WHERE FeeItemCode='110500001A'
UPDATE Dict_FeeItem SET KeyNo=B.KEYNO
FROM Dict_FeeItem A 
LEFT JOIN  vi_HisFeeItem B ON A.FeeItemCode=B.FeeItemCode
WHERE  B. DeptCode in (
'3204',
'3203',
'3202',
'3201',
'3101',
'3001',
'2601',
'2101',
'1102',
'0501',
'0307')