You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
180 lines
6.4 KiB
180 lines
6.4 KiB
[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 ='体重指数'
|
|
|