USE DXS
GO
/****** Object: StoredProcedure dbo.ImportDiseaseScoresTest Script Date: 05/05/2022 17:12:17 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.ImportDiseaseScoresTest') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.ImportDiseaseScoresTest
GO
USE DXS
GO
/****** Object: StoredProcedure dbo.ImportDiseaseScoresTest Script Date: 05/05/2022 17:12:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*============================================================
Description: SQL Server 使用OpenDataSource导入Excel文件信息到数据库表，并使用OpenQuery同步Oracle表
CreatedOn: xxxx/xx/xx
Author: xx
日期 修改人 修改说明
--------------------------------------------------------------
==============================================================
执行须知：
1.数据源Excel文件必须保证存在的列：诊断编码,诊断名称,操作编码,操作名称,分值,次均费用
执行语句：
EXEC DXS.dbo.ImportDiseaseScoresTest
@In_ExcelUrl = N'\\192.168.111.111\data\Excel文件.xls'
------------------------------------------------------------*/
CREATE PROCEDURE dbo.ImportDiseaseScoresTest
@In_ExcelUrl NVARCHAR(MAX) = '' --Excel文件网路完整路径
AS
BEGIN TRY
SET NOCOUNT ON;
----打开高级配置选项
--EXEC sp_configure 'show advanced options',1
--RECONFIGURE
--GO
----启用分布式查询
--EXEC sp_configure 'Ad Hoc Distributed Queries',1
--RECONFIGURE
--GO
DECLARE @Sql NVARCHAR(MAX); --SQL文
--1.入参校验
DECLARE @Path NVARCHAR(200) = @In_ExcelUrl; --Excel文件网路完整路径
DECLARE @Result INT = 0; --校验结果
----A.Excel文件网路完整路径非空校验
IF LEN(@In_ExcelUrl) = @Result
BEGIN
PRINT '入参路径不可为空！';
RETURN;
END;
----B.Excel文件是否存在校验
EXECUTE MASTER.dbo.xp_fileexist @Path ,@Result OUTPUT
IF @Result <> 1
BEGIN
PRINT '入参路径下没有找到需导入的Excel文件。';
RETURN;
END;
--2.导入SQL Server库
----A.创建临时表【#数据库表】，以备重新导入新数据
CREATE TABLE #数据库表
(
ID FLOAT NULL
,诊断编码 NVARCHAR(255) NULL
,诊断名称 NVARCHAR(255) NULL
,操作编码 NVARCHAR(255) NULL
,操作名称 NVARCHAR(255) NULL
,分值 FLOAT NULL
,支付标准 FLOAT NULL
,备注 NVARCHAR(255) NULL
,k1 NVARCHAR(255) NULL
,k2 NVARCHAR(255) NULL
,k3 NVARCHAR(255) NULL
,k4 NVARCHAR(255) NULL
,k5 NVARCHAR(255) NULL
,k6 NVARCHAR(255) NULL
,k7 NVARCHAR(255) NULL
)
----B.导入Excel文件【眼科部分】Sheet的信息
SET @Sql = N'
INSERT INTO #数据库表(ID,诊断编码,诊断名称,操作编码,操作名称,分值,支付标准)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ID,ISNULL(诊断编码, '''')
,ISNULL(诊断名称, ''''),ISNULL(操作编码, ''''),ISNULL(操作名称, ''''),分值,次均费用
FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source="'+ @Path +'";Extended Properties=''''Excel 12.0 XML;HDR=YES; IMEX=1'''''')...眼科部分$
';
EXEC (@Sql);
----C.导入Excel文件【综合病种】Sheet的信息
DECLARE @MaxId FLOAT; --现存【数据库表】表的"眼科部分"最大ID
SELECT @MaxId = MAX(ID) FROM #数据库表;
SET @Sql = N'
INSERT INTO #数据库表(ID,诊断编码,诊断名称,操作编码,操作名称,分值,支付标准)
SELECT ' + CAST(@MaxId AS NVARCHAR) + ' + (ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS ID,ISNULL(诊断编码, '''')
,ISNULL(诊断名称, ''''),ISNULL(操作编码, ''''),ISNULL(操作名称, ''''),分值,次均费用
FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source="'+ @Path +'";Extended Properties=''''Excel 12.0 XML;HDR=YES; IMEX=1'''''')...综合病种$
';
EXEC (@Sql);
----D.创建游标循环病种分值库记录，拆分【操作编码】为术式【k1】~【k7】，填充【k1】~【k7】内容
DECLARE @DiseaseScoreId INT;
DECLARE @操作编码 NVARCHAR(MAX) = '';
DECLARE @K NVARCHAR(255) = '';
DECLARE @Number INT = 0;
DECLARE @Index INT;
DECLARE diseaseScores CURSOR LOCAL FOR SELECT T.ID,T.操作编码 FROM #数据库表 T;
OPEN diseaseScores;
FETCH NEXT FROM diseaseScores INTO @DiseaseScoreId,@操作编码;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql =
N'UPDATE T SET ';
SET @Number = 0;
WHILE LEN(@操作编码) > 0
BEGIN
SET @Index = CHARINDEX('+',@操作编码);
IF @Index = 0
SELECT @K = @操作编码, @操作编码 = '';
ELSE
SELECT @K = SUBSTRING(@操作编码,0,@Index), @操作编码 = SUBSTRING(@操作编码,@Index + 1,LEN(@操作编码) - @Index);
IF LEN(@K) > 0 --AND @K <> 'n(y)' --需要'n(y)'填充【k1】；如果不需，则释放注释脚本
BEGIN
SET @Number += 1;
SET @Sql = @Sql + CASE WHEN @Number = 1 THEN 'T.k' ELSE ',T.k' END + CONVERT(NVARCHAR,@Number) + ' = '''+ @K +''' ';
END;
END;
SET @Sql = @Sql + '
FROM #数据库表 T
WHERE T.ID = '+ CONVERT(NVARCHAR,@DiseaseScoreId) +'';
IF @Number > 0
EXEC sp_executesql @Sql;
FETCH NEXT FROM diseaseScores INTO @DiseaseScoreId, @操作编码;
END;
CLOSE diseaseScores;
DEALLOCATE diseaseScores;
--3.导入Oracle库
----A.填充【统合诊断编码匹配的全部病种分值Id集合】临时表
CREATE TABLE #tmpScore --统合诊断编码匹配的全部病种分值Id集合
(
诊断编码 VARCHAR(MAX) --诊断编码
,DiseaseScoreIds VARCHAR(MAX) --病种分值Ids
);
INSERT INTO #tmpScore(诊断编码,DiseaseScoreIds)
SELECT 诊断编码,DiseaseScoreIds = (STUFF((SELECT ',' + CAST(ID AS NVARCHAR) FROM #数据库表 G1 WHERE G1.诊断编码 = T.诊断编码 FOR XML PATH('')), 1, 1, ''))
FROM #数据库表 T
GROUP BY T.诊断编码;
----B.填充【统合疾病ID匹配的全部病种分值ID集合】临时表
CREATE TABLE #tmpDisease --统合疾病ID匹配的全部病种分值ID集合
(
ID INT --Oracle疾病ID
,Ids VARCHAR(MAX) --病种分值ID集合
);
INSERT INTO #tmpDisease(ID,Ids)
SELECT T.DiseaseId,S.DiseaseScoreIds
FROM OPENQUERY(clinicdb, 'SELECT DiseaseId,IcdCode,DiseaseScoreIds FROM CONCEPT.Disease') T
INNER JOIN #tmpScore S ON CHARINDEX(S.诊断编码, T.IcdCode) > 0 AND LEN(S.诊断编码) > 4;
----C.填充【统合病种分值ID匹配的手术ID】临时表
CREATE TABLE #tmpProcedure --统合病种分值ID匹配的手术ID
(
ID INT --病种分值ID
,ProcedureId INT --Oracle手术ID
);
INSERT INTO #tmpProcedure(ID,ProcedureId)
SELECT D.ID,P.ProcedureId
FROM (
SELECT X.ID, N.CodeItem
FROM (SELECT ID, CodeItem = CAST('<v>' + REPLACE(操作编码, '+', '</v><v>') + '</v>' AS XML) FROM #数据库表) X
OUTER APPLY (SELECT CodeItem = T.C.value('.','varchar(50)') FROM X.CodeItem.nodes('/v') AS T(C)) N
WHERE ISNULL(N.CodeItem,'') <> ''
) D
INNER JOIN OPENQUERY(clinicdb, 'SELECT ProcedureId,Code FROM CONCEPT.Procedure WHERE IsDeleted = 0') P ON P.Code = D.CodeItem;
----D.填充【统合病种分值ID匹配的所有手术ID集合】临时表
CREATE TABLE #tmpProcedureIds --统合病种分值ID匹配的所有手术ID集合
(
ID INT --病种分值ID
,Ids VARCHAR(MAX) --Oracle手术ID集合
);
INSERT INTO #tmpProcedureIds(ID,Ids)
SELECT ID,Ids = (STUFF((SELECT ',' + CAST(ProcedureId AS NVARCHAR) FROM #tmpProcedure G1 WHERE G1.ID = T.ID FOR XML PATH('')), 1, 1, ''))
FROM #tmpProcedure T
GROUP BY T.Id;
----E.清空【DATA.DiseaseScore_20211228】表，以备重新导入新数据
DELETE FROM OPENQUERY(clinicdb, 'SELECT * FROM DATA.DiseaseScore_20211228');
----F.导入DXS.#数据库表表的信息
INSERT INTO OPENQUERY(clinicdb, 'SELECT * FROM DATA.DiseaseScore_20211228')
SELECT T.ID,NULL AS OrganizationName,T.诊断编码 AS ICDCodePre4,T.诊断名称 AS DiseaseName,ISNULL(P.Ids,'') AS ProcedureID
,T.操作编码 AS ProcedureICD,T.操作名称 AS ProcedureName,T.支付标准 AS CapitaCost, NULL AS PersonTime
,T.分值 AS Score,GETDATE() AS RowVersion,0 AS IsDeleted,NULL AS DrugsAmount,NULL AS DrugsProportion
,NULL AS MaterialAmount,NULL AS MaterialProportion
FROM #数据库表 T
LEFT JOIN #tmpProcedureIds P ON P.ID = T.ID;
--G.更新【CONCEPT.Disease】表信息
UPDATE OPENQUERY(clinicdb, 'SELECT DiseaseId,IsMedicalInsuranceKind,DiseaseScoreIds FROM CONCEPT.Disease') SET
IsMedicalInsuranceKind = 1
,DiseaseScoreIds = (SELECT TOP 1 D.Ids FROM #tmpDisease D WHERE D.ID = DiseaseId)
WHERE EXISTS (SELECT D.* FROM #tmpDisease D WHERE D.ID = DiseaseId);
----4.查看导入后数据结果
------SQL Server的数据
--SELECT T.* FROM #数据库表 T;
------Oracle的数据
--SELECT * FROM OPENQUERY(clinicdb, 'SELECT * FROM DATA.DiseaseScore_20211228');
----禁用分布式查询
--EXEC sp_configure 'Ad Hoc Distributed Queries',0
--RECONFIGURE
--GO
----关闭高级配置选项
--EXEC sp_configure 'show advanced options',0
--RECONFIGURE
--GO
DROP TABLE #数据库表;
DROP TABLE #tmpScore;
DROP TABLE #tmpDisease;
DROP TABLE #tmpProcedure;
DROP TABLE #tmpProcedureIds;
SET NOCOUNT OFF;
END TRY
BEGIN CATCH
DROP TABLE #数据库表;
DROP TABLE #tmpScore;
DROP TABLE #tmpDisease;
DROP TABLE #tmpProcedure;
DROP TABLE #tmpProcedureIds;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ISNULL(ERROR_PROCEDURE(),'') + '执行失败:' + ERROR_MESSAGE();
RAISERROR(@msg,16,1);
END CATCH
GO
