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.
562 lines
18 KiB
562 lines
18 KiB
|
|
|
|
-- 【 Base_Org 】#########################################################
|
|
INSERT INTO [Base_Org]([ID], [Name], [SpellCode], [Contactor], [Tel1], [Tel2], [Address], [CreateTime], [CreatorCode], [Creator])
|
|
SELECT
|
|
Id AS ID,
|
|
NAME AS Name,
|
|
PYM AS SpellCode,
|
|
Contactor AS Contactor,
|
|
Tel1 AS Tel1,
|
|
Tel2 AS Tel2,
|
|
Address AS Address,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator
|
|
FROM PEIS.dbo.Org
|
|
|
|
|
|
|
|
-- 【 Base_OrgPatient 】#########################################################
|
|
|
|
INSERT INTO [Base_OrgPatient]([ID], [OID], [PID], [Seq], [DeptName])
|
|
SELECT
|
|
Id AS ID,
|
|
OrgId AS OID,
|
|
PatientId AS PID,
|
|
SortNo AS Seq,
|
|
Branch AS DeptName
|
|
FROM PEIS.dbo.OrgPatient
|
|
|
|
|
|
-- 【 Base_Patient 】#########################################################
|
|
|
|
INSERT INTO [Base_Patient]([ID], [Name], [Sex], [Birthday], [CardType], [CardNo], [Nation], [Tel1], [Tel2], [Contactor1], [Address1], [Marriage], [Education], [Occupation], [Type], [CreateTime], [CreatorCode], [Creator])
|
|
SELECT
|
|
Id AS ID,
|
|
NAME AS Name,
|
|
Gender AS Sex,
|
|
Birthday AS Birthday,
|
|
'居民身份证' AS CardType,
|
|
IDCard AS CardNo,
|
|
NationName AS Nation,
|
|
Tel1 AS Tel1,
|
|
Tel2 AS Tel2,
|
|
NAME AS Contactor1,
|
|
Address AS Address1,
|
|
CASE Marriage
|
|
WHEN 1 THEN '未婚'
|
|
WHEN 2 THEN '已婚'
|
|
ELSE '未知'
|
|
END AS Marriage,
|
|
EducationName AS Education,
|
|
OccupationName AS Occupation,
|
|
PatientTypeName AS Type,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator
|
|
FROM PEIS.dbo.Patient WHERE StopOperDate IS NULL AND IDCard IS NOT NULL
|
|
|
|
-- 【 Enrollment_CheckCost 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_CheckCost]([ID], [EID], [OEID],[Sum], [Discount], [Fee], [CreateTime], [CreatorCode], [Creator], [CostTime], [CosterCode], [Coster], [ChargeTime], [ChargerCode], [Charger], [SendTime], [SenderCode], [Sender],[OriginOrderNo])
|
|
SELECT
|
|
Id AS ID,
|
|
EnrollmentId AS EID,
|
|
OrgEnrollmentId AS OEID,
|
|
SummaryHISPrice AS [Sum],
|
|
SaleDiscount AS Discount,
|
|
SellingPrice AS Fee,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator,
|
|
ChargeOperDate as CostTime,
|
|
ChargeOperCode AS CosterCode,
|
|
ChargeOperName AS Coster,
|
|
AccountedOperDate AS ChargeTime,
|
|
AccountedOperCode AS ChargerCode,
|
|
AccountedOperName AS Charger,
|
|
SendOperDate AS SendTime,
|
|
SendOperCode AS SenderCode,
|
|
SendOperName AS Sender,
|
|
RefundOrderSheetId AS OriginOrderNo
|
|
FROM peis.dbo.OrderSheet
|
|
WHERE OrgEnrollmentId IS NULL
|
|
|
|
-- 更新团体id
|
|
UPDATE a SET a.OEID = b.OEID
|
|
FROM Enrollment_CheckCost a
|
|
LEFT JOIN Enrollment_Patient b ON a.EID = b.ID
|
|
WHERE b.OEID IS NOT NULL
|
|
|
|
|
|
-- 【 Enrollment_FeeItem 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_FeeItem]([OEID], [GroupID], [EID], [PackID], [PackName], [FID], [FeeItemCode], [FeeItemName], [Quantity], [Unit], [Price], [SettlePrice], [ItemClass], [CreateTime], [CreatorCode], [Creator], [OrderNo], [DeptCode], [DeptName], [Seq], [GiveUpTime], [GiveUpUser], [GiveUpCode], [IsSend], [OldId])
|
|
SELECT
|
|
OrgEnrollmentId AS OEID,
|
|
OrgEnrollmentGroupId AS GroupID,
|
|
EnrollmentId AS EID,
|
|
ExamPackId AS PackID,
|
|
ExamPackName AS PackName,
|
|
FeeItemId AS FID,
|
|
HISCheckCode AS FeeItemCode,
|
|
HISCheckName AS FeeItemName,
|
|
Quantity,
|
|
UnitName AS Unit,
|
|
HISPrice AS Price,
|
|
CostPrice AS SettlePrice,
|
|
CASE ItemType
|
|
WHEN 2 THEN '检查'
|
|
WHEN 3 THEN '检验'
|
|
ELSE '普通'
|
|
END AS ItemClass,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator,
|
|
OrderSheetId AS OrderNo,
|
|
DepartmentId AS DeptCode,
|
|
DepartmentName AS DeptName,
|
|
SortNo AS Seq,
|
|
GiveUpOperDate AS GiveUpTime,
|
|
GiveUpOperName AS GiveUpUser,
|
|
GiveUpOperCode AS GiveUpCode,
|
|
1 AS IsSend,
|
|
Id AS OldId
|
|
FROM PEIS.dbo.EnrollmentApplicationSheetFeeItem
|
|
UNION ALL
|
|
SELECT
|
|
OrgEnrollmentId AS OEID,
|
|
OrgEnrollmentGroupId AS GroupID,
|
|
EnrollmentId AS EID,
|
|
ExamPackId AS PackID,
|
|
ExamPackName AS PackName,
|
|
FeeItemId AS FID,
|
|
HISCheckCode AS FeeItemCode,
|
|
HISCheckName AS FeeItemName,
|
|
Quantity,
|
|
UnitName AS Unit,
|
|
HISPrice AS Price,
|
|
CostPrice AS SettlePrice,
|
|
CASE ItemType
|
|
WHEN 2 THEN '检查'
|
|
WHEN 3 THEN '检验'
|
|
ELSE '普通'
|
|
END AS ItemClass,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator,
|
|
OrderSheetId AS OrderNo,
|
|
DepartmentId AS DeptCode,
|
|
DepartmentName AS DeptName,
|
|
SortNo AS Seq,
|
|
GiveUpOperDate AS GiveUpTime,
|
|
GiveUpOperName AS GiveUpUser,
|
|
GiveUpOperCode AS GiveUpCode,
|
|
1 AS IsSend,
|
|
Id AS OldId
|
|
FROM PEIS.dbo.EnrollmentFeeItem
|
|
|
|
--更新科室
|
|
UPDATE a SET a.DeptCode = b.HISCode
|
|
FROM Enrollment_FeeItem a
|
|
LEFT JOIN Dict_Dept b ON a.DeptName = b.Name
|
|
|
|
-- 【 Enrollment_Org 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_Org]([ID], [Name], [Contactor1], [Tel1], [Tel2], [Address], [ExamDate], [CreateTime], [CreatorCode], [Creator], [OID])
|
|
SELECT
|
|
Id AS ID,
|
|
OrgName AS Name,
|
|
Contactor AS Contactor1,
|
|
Tel1 AS Tel1,
|
|
Tel2 AS Tel2,
|
|
Address,
|
|
ExamDate,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode ,
|
|
CreateOperName AS Creator ,
|
|
OrgId AS OID
|
|
FROM PEIS.dbo.OrgEnrollment
|
|
|
|
-- 【 Enrollment_OrgFeeItem 】#########################################################
|
|
|
|
INSERT INTO [dbo].[Enrollment_OrgFeeItem]([OEID], [GroupID], [PackID], [PackName], [FID], [FeeItemCode], [FeeItemName], [Quantity], [Unit], [Price], [SettlePrice], [ItemClass], [CreateTime], [CreatorCode], [Creator], [DeptCode], [DeptName], [Seq], [OID])
|
|
SELECT
|
|
OrgEnrollmentId AS OEID,
|
|
OrgEnrollmentGroupId AS GroupID,
|
|
ExamPackId AS PackID,
|
|
ExamPackName AS PackName,
|
|
FeeItemId AS FID,
|
|
HISCheckCode AS FeeItemCode,
|
|
HISCheckName AS FeeItemName,
|
|
Quantity,
|
|
UnitName AS Unit,
|
|
HISPrice AS Price,
|
|
CostPrice AS SettlePrice,
|
|
CASE ItemType
|
|
WHEN 2 THEN '检查'
|
|
WHEN 3 THEN '检验'
|
|
ELSE '普通'
|
|
END AS ItemClass,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator,
|
|
DepartmentId AS DeptCode,
|
|
DepartmentName AS DeptName,
|
|
SortNo AS Seq,
|
|
Id AS OldId
|
|
FROM PEIS.dbo.EnrollmentApplicationSheetFeeItem
|
|
WHERE OrgEnrollmentId IS NOT NULL
|
|
UNION ALL
|
|
SELECT
|
|
OrgEnrollmentId AS OEID,
|
|
OrgEnrollmentGroupId AS GroupID,
|
|
ExamPackId AS PackID,
|
|
ExamPackName AS PackName,
|
|
FeeItemId AS FID,
|
|
HISCheckCode AS FeeItemCode,
|
|
HISCheckName AS FeeItemName,
|
|
Quantity,
|
|
UnitName AS Unit,
|
|
HISPrice AS Price,
|
|
CostPrice AS SettlePrice,
|
|
CASE ItemType
|
|
WHEN 2 THEN '检查'
|
|
WHEN 3 THEN '检验'
|
|
ELSE '普通'
|
|
END AS ItemClass,
|
|
CreateOperDate AS CreateTime,
|
|
CreateOperCode AS CreatorCode,
|
|
CreateOperName AS Creator,
|
|
DepartmentId AS DeptCode,
|
|
DepartmentName AS DeptName,
|
|
SortNo AS Seq,
|
|
Id AS OldId
|
|
FROM PEIS.dbo.EnrollmentFeeItem
|
|
WHERE OrgEnrollmentId IS NOT NULL
|
|
|
|
--更新科室
|
|
UPDATE a SET a.DeptCode = b.HISCode
|
|
FROM Enrollment_OrgFeeItem a
|
|
LEFT JOIN Dict_Dept b ON a.DeptName = b.Name
|
|
|
|
-- 【 Enrollment_OrgGroup 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_OrgGroup]([ID], [Name], [Sex], [Marriage], [AgeMin], [AgeMax], [Type], [CheckTime], [CheckerCode], [Checker], [OID])
|
|
SELECT
|
|
Id AS ID,
|
|
Name,
|
|
SuitableGender AS Sex,
|
|
CASE SuitableMarriage
|
|
WHEN 1 THEN '未婚'
|
|
when 2 THEN '已婚'
|
|
ELSE '所有'
|
|
END AS Marriage,
|
|
SuitableAgeBegin AS AgeMin,
|
|
SuitableAgeEnd AS AgeMax,
|
|
ExamTypeName AS Type,
|
|
SyncOperDate AS CheckTime,
|
|
SyncOperCode AS CheckerCode,
|
|
SyncOperName as Checker,
|
|
OrgId AS OID
|
|
FROM PEIS.dbo.OrgEnrollmentGroup
|
|
|
|
-- 【 Enrollment_OrgPatient 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_OrgPatient]([ID], [GroupID], [PID], [OID])
|
|
SELECT
|
|
Id AS ID,
|
|
OrgEnrollmentGroupId AS GroupID,
|
|
PatientId AS PID,
|
|
OrgEnrollmentId AS OID
|
|
FROM PEIS.dbo.OEGroupPatient
|
|
|
|
-- 【 Enrollment_Patient 】#########################################################
|
|
|
|
INSERT INTO [Enrollment_Patient]([ID], [Name], [Sex], [Age], [AgeClass], [CardType], [CardNo], [Tel1], [Tel2], [Contactor1], [Address1], [Marriage], [Education], [Occupation], [ExamDate], [Type], [PID], [OID], [OEID], [OrgName], [GroupID], [GroupName], [CreateTime], [CreatorCode], [Creator], [SignTime], [SignerCode], [Signer], [FinishTime], [FinisherCode], [Finisher], [WeChatStatus], [PrintTime], [PrinterCode], [Printer], [LastPrintTime], [LastPrinterCode], [LastPrinter], [Nation], [IsWeChatView])
|
|
SELECT
|
|
a.Id AS ID,
|
|
a.PatientName AS Name,
|
|
b.Gender AS Sex,
|
|
a.Age AS Age,
|
|
a.AgeUnit AS AgeClass,
|
|
'居民身份证' AS CardType,
|
|
b.IDCard AS CardNo,
|
|
a.Tel1 AS Tel1,
|
|
a.Tel2 AS Tel2,
|
|
a.PatientName AS Contactor1,
|
|
a.Address AS Address1,
|
|
CASE a.Marriage
|
|
WHEN 1 THEN '未婚'
|
|
WHEN 2 THEN '已婚'
|
|
ELSE '未知'
|
|
END AS Marriage,
|
|
a.EducationName AS Education,
|
|
a.OccupationName AS Occupation,
|
|
a.ExamDate AS ExamDate,
|
|
a.PatientTypeName AS Type,
|
|
a.PatientId AS PID,
|
|
a.OrgId AS OID,
|
|
a.OrgEnrollmentId AS OEID,
|
|
a.OrgName AS OrgName,
|
|
a.OrgEnrollmentId AS GroupID,
|
|
a.OrgEnrollmentGroupName AS GroupName,
|
|
a.CreateOperDate AS CreateTime,
|
|
a.CreateOperCode AS CreatorCode,
|
|
a.CreateOperName AS Creator,
|
|
a.SignInOperDate AS SignTime,
|
|
a.SignInOperCode AS SignerCode,
|
|
a.SignInOperName AS Signer,
|
|
a.FinishOperDate AS FinishTime,
|
|
a.FinishOperCode AS FinisherCode,
|
|
a.FinishOperName AS Finisher,
|
|
0 AS WeChatStatus,
|
|
a.FirstPrintOperDate AS PrintTime,
|
|
a.FirstPrintOperCode AS PrinterCode,
|
|
a.FirstPrintOperName AS Printer,
|
|
a.SecondPrintOperDate AS LastPrintTime,
|
|
a.SecondPrintOperCode AS LastPrinterCode,
|
|
a.SecondPrintOperName AS LastPrinter,
|
|
b.NationName AS Nation,
|
|
0 AS IsWeChatView
|
|
FROM PEIS.dbo.Enrollment a
|
|
LEFT JOIN PEIS.dbo.Patient b ON a.PatientId = b.Id
|
|
WHERE a.RemoveOperDate IS NULL
|
|
|
|
|
|
-- -- -- -- -- -- --
|
|
|
|
-- 【1、Dict_Conclusion】#########################################################
|
|
|
|
INSERT INTO Dict_Conclusion(ID, ConclusionName, SpellCode, Description, Instructions, Suggestion, Advice, DietaryGuidance, Sex )
|
|
SELECT
|
|
Id,
|
|
NAME AS ConclusionName,
|
|
PYM AS SpellCode,
|
|
Description,
|
|
Instructions,
|
|
Suggestion ,
|
|
Advice,
|
|
DietaryGuidance,
|
|
DiseasesGender AS SEX
|
|
FROM
|
|
PEIS.DBO.Conclusion
|
|
|
|
-- 【2、Dict_Dept】#########################################################
|
|
|
|
INSERT INTO [dbo].[Dict_Dept]([ID], [Name], [HISCode], [Address])
|
|
SELECT Id ,Name,HISCode,HISName ,Address FROM PEIS.DBO.Department
|
|
|
|
-- 【3、Dict_FeeItem】#########################################################
|
|
|
|
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
|
|
|
|
-- 与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 a.KeyNo is null and b.DeptCode in ( select DISTINCT HISCode from Dict_Dept )
|
|
|
|
-- 【4、Dict_Pack】#########################################################
|
|
|
|
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
|
|
|
|
-- 【5、Dict_PackToFeeItem】#########################################################
|
|
|
|
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]
|
|
|
|
-- 【6、Dict_PeisFeeItem】#########################################################
|
|
-- 手动添加HIS中未开展的项目
|
|
|
|
-- 【7、Dict_ReportItem】#########################################################
|
|
-- 插入不重复部分
|
|
INSERT INTO [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
|
|
-- select DISTINCT [FeeItemId], [ExamItemId], [SortNo] from PEIS.dbo.feeitemexamitem where ExamItemId=1
|
|
-- 更新 FeeItemId 和 SortNo
|
|
UPDATE Dict_ReportItem
|
|
SET FId = CASE WHEN Dict_ReportItem.FId IS NULL THEN feeitemexamitem.FeeItemId ELSE new_record.FId END,
|
|
Seq = feeitemexamitem.SortNo
|
|
FROM Dict_ReportItem
|
|
JOIN PEIS.dbo.feeitemexamitem ON Dict_ReportItem.ID = feeitemexamitem.ExamItemId
|
|
OUTER APPLY (
|
|
SELECT TOP 1 FId
|
|
FROM Dict_ReportItem AS dr
|
|
WHERE Dict_ReportItem.ID = dr.ID AND dr.FId IS NOT NULL
|
|
) AS new_record (FId)
|
|
|
|
-- 插入重复部分 多对多
|
|
INSERT INTO [Dict_ReportItem]([RptItemName], [SpellCode], [Unit], [RptItemCode],fid,seq )
|
|
select DISTINCT
|
|
NAME as RptItemName,
|
|
PYM as SpellCode,
|
|
ValueUnit as Unit,
|
|
LisCode as RptItemCode,
|
|
FeeItemId as fid,
|
|
SortNo as seq
|
|
from
|
|
PEIS.dbo.feeitemexamitem a
|
|
left join PEIS.dbo.examitem b on a.ExamItemId=b.id
|
|
where not EXISTS (select 1 from Dict_ReportItem c where c.id=a.ExamItemId and c.fid=a.FeeItemId)
|
|
ORDER BY fid,seq
|
|
|
|
-- 【8、Dict_Sign】#########################################################
|
|
INSERT INTO Dict_Sign(ID, RID, SignName, SpellCode, Description, SevereLevel, IsDefault, IsSummary, Group1, Group2, Seq, CID)
|
|
SELECT Id,
|
|
ExamItemId AS RID,
|
|
NAME AS SignName,
|
|
PYM AS SpellCode,
|
|
SignDetail AS Description,
|
|
DiseaseLevel AS SevereLevel,
|
|
IsDefaultOption AS IsDefault,
|
|
SummaryInclude AS IsSummary,
|
|
FirstGroupNo AS Group1,
|
|
SecondGroupNo AS Group2,
|
|
SortNo AS Seq ,
|
|
ConclusionId AS CID
|
|
FROM
|
|
PEIS.dbo.ExamSign
|
|
|
|
-- 【9、Exam_Part】#########################################################
|
|
|
|
|
|
INSERT INTO [EXAM_PART] ([ID],[EID],[DeptName],[DeptCode],[InputTime],[InputerCode],[Inputer],[CheckTime],[CheckerCode],[Checker],[VerifyTime],[VerifyCode],[Verifier],[Summary],[CreateTime],[CreatorCode],[Creator], [GiveUpTime],[GiveUpUser],[GiveUpCode] )
|
|
|
|
SELECT
|
|
A.[ID],
|
|
A.EnrollmentId AS [EID],
|
|
A.DepartmentName AS [DeptName],
|
|
B.HISCODE AS [DeptCode],
|
|
A.GiveUpOperDate AS [InputTime],
|
|
A.InputOperCode AS [InputerCode],
|
|
A.InputOperName AS [Inputer],
|
|
A.CheckOperDate AS [CheckTime],
|
|
A.CheckOperCode AS [CheckerCode],
|
|
A.CheckOperName AS [Checker],
|
|
A.VerifyOperDate AS [VerifyTime],
|
|
A.VerifyOperCode AS [VerifyCode],
|
|
A.VerifyOperName AS [Verifier],
|
|
A.SummaryContent AS [Summary],
|
|
A.CreateOperDate AS [CreateTime],
|
|
A.CreateOperCode AS [CreatorCode],
|
|
A.CreateOperName AS [Creator],
|
|
A.GiveUpOperDate AS [GiveUpTime],
|
|
A.GiveUpOperName AS [GiveUpUser],
|
|
A.GiveUpOperCode AS [GiveUpCode]
|
|
FROM PEIS.DBO.ExamDepartment A
|
|
LEFT JOIN PEIS.DBO.Department B ON A.DepartmentId=B.ID
|
|
WHERE A.RemoveOperDate IS NULL
|
|
|
|
|
|
-- END
|
|
|
|
-- 【10、Exam_Result】#########################################################
|
|
|
|
-- SELECT top 20 * FROM LisResult
|
|
-- 普通项目和心电
|
|
SELECT count(*) FROM peisdb.dbo.[Exam_Result]
|
|
TRUNCATE TABLE peisdb.dbo.[Exam_Result]
|
|
begin Transaction
|
|
|
|
INSERT INTO peisdb.[dbo].[Exam_Result]( [PID],[DID],[DeptName] , [FID], [FeeItemName], [RID], [RptItemName], [TextResult], [Unit], [ItemClass] )
|
|
SELECT
|
|
Z.Id AS PID
|
|
,Y.DepartmentId AS [DID]
|
|
,X.DepartmentName AS [DeptName]
|
|
,X.[FeeItemId] AS [FID]
|
|
,X.Name AS FeeItemName
|
|
,Y.[ExamItemId] AS [RID]
|
|
,Y.[Name] AS [RptItemName]
|
|
,Y.[TextValue] AS [TextResult]
|
|
,Y.[ValueUnit] AS [Unit]
|
|
,X.ItemType AS [ItemClass]
|
|
FROM PEIS.DBO.[EnrollmentFeeItem] X (NOLOCK)
|
|
JOIN PEIS.DBO.[EnrollmentExamItem] Y (NOLOCK) ON X.Id = Y.EnrollmentFeeItemId
|
|
JOIN PEIS.DBO.[ExamDepartment] Z(NOLOCK) ON Y.DepartmentId = Z.DepartmentId AND Y.EnrollmentId = Z.EnrollmentId AND X.EnrollmentId = Z.EnrollmentId
|
|
WHERE X.SendOperDate IS NOT NULL AND X.RemoveOperDate IS NULL AND Y.RemoveOperDate IS NULL AND Z.RemoveOperDate IS NULL
|
|
AND X.GiveUpOperDate IS NULL AND Y.GiveUpOperDate IS NULL
|
|
-- 检验项目
|
|
INSERT INTO peisdb.[dbo].[Exam_Result]( [PID],[DID],[DeptName] , [FID], [FeeItemName], [RID], [RptItemName], [TextResult], [Unit], [ItemClass] ,RPTITEMCODE,[Range],REPORTER,REPORTTIME,UnusualFlag)
|
|
SELECT
|
|
C.Id AS PID
|
|
,B.DepartmentId AS [DID]
|
|
,A.DepartmentName AS [DeptName]
|
|
,A.[FeeItemId] AS [FID]
|
|
,A.Name AS FeeItemName
|
|
,B.[ExamItemId] AS [RID]
|
|
,B.[Name] AS [RptItemName]
|
|
,B.[TextValue] AS [TextResult]
|
|
,B.[ValueUnit] AS [Unit]
|
|
,A.ItemType AS [ItemClass]
|
|
,B.LisCode AS RPTITEMCODE
|
|
,B.LisRange AS [Range]
|
|
,B.LisVerifyOperName AS REPORTER
|
|
,B.LisVerifyOperDate AS REPORTTIME
|
|
,B. LisFlag AS UnusualFlag
|
|
FROM PEIS.DBO.[EnrollmentApplicationSheetFeeItem] A (NOLOCK)
|
|
JOIN PEIS.DBO.[EnrollmentExamItem] B (NOLOCK) ON A.Id = B.EnrollmentApplicationSheetFeeItemId
|
|
JOIN PEIS.DBO.[ExamDepartment] C(NOLOCK) ON B.DepartmentId = C.DepartmentId AND B.EnrollmentId = C.EnrollmentId AND A.EnrollmentId =C.EnrollmentId
|
|
WHERE A.SendOperDate IS NOT NULL AND A.RemoveOperDate IS NULL AND B.RemoveOperDate IS NULL AND C.RemoveOperDate IS NULL
|
|
AND A.GiveUpOperDate IS NULL AND B.GiveUpOperDate IS NULL
|
|
order by a.id desc
|
|
|
|
if(@@error<>0) -- 判断是否报错如果报错就回滚信息否则提交事务
|
|
rollback transaction
|
|
else
|
|
commit transaction
|
|
|
|
-- 检查
|
|
INSERT INTO [Exam_Result]( [PID], [DeptName], [FID], [FeeItemName], [RptItemName], [TextResult], [ItemClass], [Reporter], [ReportTime])
|
|
SELECT
|
|
C.ID AS PID,C.DeptName,
|
|
D.FeeItemId AS FID,D.Name AS FeeItemName,A.NAME AS RptItemName,
|
|
A.TextValue AS TextResult,ItemClass='检查',A.PacsVerifyOperName AS Reporter, A.PacsVerifyOperDate AS ReportTime
|
|
FROM PEIS.DBO.EnrollmentApplicationSheet A
|
|
LEFT JOIN PEIS.DBO.DEPARTMENT B ON A.DepartmentId=B.ID
|
|
LEFT JOIN Exam_Part C ON A.EnrollmentId=C.EID AND B.HISCODE=C.DeptCode AND B.NAME=C.DeptName
|
|
LEFT JOIN PEIS.DBO.EnrollmentApplicationSheetFeeItem D ON A.ID=D.EnrollmentApplicationSheetId
|
|
WHERE A. TextValue IS NOT NULL
|
|
|
|
|
|
|
|
-- 【11、Exam_Conclusion】#########################################################
|
|
|
|
INSERT INTO [dbo].[Exam_Conclusion]([EID],[FID], [RID], [CID], [Conclusion], [Suggestion] , [Seq])
|
|
SELECT
|
|
[EnrollmentId] AS EID,
|
|
[FeeItemId] AS FID,
|
|
[ExamItemId] AS RID,
|
|
[ConclusionId] AS CID,
|
|
[Name] AS Conclusion,
|
|
[Suggestion] AS Suggestion ,
|
|
SORTNO AS SEQ
|
|
FROM PEIS.DBO.ExamConclusion |