fMfXFyaq

1USE [Retraite]
2GO
3/****** Object:  StoredProcedure [dbo].[PAS_ImportTaux]    Script Date: 30/10/2018 09:38:14 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9-- EXECUTE dbo.PAS_ImportTaux @xmlFilePath = 'F:\DSN-TEST\2018-10-09T205810_DGFIP-A8tt3vWBFAinK6RA-xaofNg-1-2-OK-RG-0038700659_20181009174620_77567546500014.xml'
10
11ALTER   PROCEDURE [dbo].[PAS_ImportTaux] 
12	@xmlFilePath VARCHAR(200)
13AS
14  DECLARE @xmlFileName AS VARCHAR(MAX) = RIGHT(@xmlFilePath, CHARINDEX('\', REVERSE(@xmlFilePath)) - 1)
15  DECLARE @xmlDate AS DATE = CONVERT(DATE, LEFT(@xmlFileName, 10))
16	DECLARE @xmlContent XML;
17	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PAS_Taux')
18	BEGIN
19		CREATE TABLE PAS_Taux (
20			id_PAS INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
21			NIR_PAS VARCHAR(17),
22			matricule_PAS VARCHAR(8),
23			taux_PAS DECIMAL(5, 2),
24			date_PAS DATE,
25      anomalie_PAS VARCHAR(30)
26		);
27	END
28
29  --déclaration table temporaire data
30  CREATE TABLE #TempXML (
31		NIR_PAS VARCHAR(17),
32		matricule_PAS VARCHAR(8),
33		taux_PAS DECIMAL(5, 2),
34  );
35
36  --déclaration,table temporaire erreurs
37  CREATE TABLE #TempError (
38    anomalie_PAS VARCHAR(30),
39    matricule_PAS VARCHAR(8)
40  );
41
42	DECLARE @sql AS VARCHAR(MAX) = 'SELECT * FROM OPENROWSET(BULK ''' + @xmlFilePath + ''', SINGLE_CLOB) AS SRC;'; --OPENROWSET prend un string natif, 
43	                                                                                                               -- il faut exec la query pour intérpreter la variable
44	DECLARE @T TABLE (BulkColumn VARCHAR(MAX) NOT NULL) --on récupere la query dans une table, pas le choix car on utilise EXEC
45	INSERT @T EXEC(@sql) --on insert dans la table
46	SET @xmlContent = (SELECT BulkColumn FROM @T); --on a récuperer le fichier on a la charger en tant que XML en local
47
48	DECLARE @handle INT
49	DECLARE @PrepareXmlStatus INT
50
51	EXEC @PrepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @xmlContent, '<ns2:rapport xmlns:ns2="http://www.gip-mds.fr/"/>' --namespace de l'xml
52
53  INSERT INTO #TempXML
54    SELECT NIR_PAS, matricule_PAS, taux_PAS --on insert les données dans la table
55		FROM OPENXML(@handle, '/ns2:rapport/declaration/declaration_bilan/salarie', 1)
56		  WITH (NIR_PAS VARCHAR(17) 'NIR/text()',
57            matricule_PAS VARCHAR(8) 'matricule/text()',
58            taux_PAS DECIMAL(5, 2) 'taux_imposition_PAS/text()')
59
60  INSERT INTO #TempError
61    SELECT anomalie_PAS, matricule_PAS
62    FROM OPENXML(@handle, '/ns2:rapport/declaration/declaration_anomalie', 1)
63      WITH (anomalie_PAS VARCHAR(30) 'valeurs/rubrique_declenchante/identifiant/text()',
64            matricule_PAS VARCHAR(8) 'environnement/salarie/matricule/text()')
65
66	EXEC sp_xml_removedocument @handle
67
68  --vérification des doublons en requête ensembliste
69	INSERT INTO PAS_Taux
70    SELECT T2.nir_PAS, T2.matricule_PAS, T2.Taux_PAS, @XmlDate, T3.anomalie_PAS
71    FROM #TempXML AS T2
72    FULL OUTER JOIN #TempError AS T3
73      ON T2.matricule_PAS = T3.matricule_PAS
74    WHERE NOT EXISTS(
75	    SELECT *
76	    FROM PAS_Taux T3
77	    WHERE T3.matricule_PAS = T2.matricule_PAS
78		    AND T3.date_PAS = @XmlDate)
79
80/*
81  --vérification des doublons en déroulant les lignes
82  DECLARE @NIR_PAS VARCHAR(17)
83  DECLARE @matricule_PAS VARCHAR(8)
84  DECLARE @taux_PAS DECIMAL(5, 2)
85
86  DECLARE XmlCursor CURSOR
87    FOR SELECT NIR_PAS, matricule_PAS, taux_PAS FROM #TempXML
88  OPEN XmlCursor
89  FETCH NEXT FROM XmlCursor INTO @NIR_PAS, @matricule_PAS, @taux_PAS
90
91  WHILE @@FETCH_STATUS = 0
92  BEGIN
93    IF NOT EXISTS (SELECT * FROM PAS_Taux WHERE matricule_PAS = @matricule_PAS AND date_PAS = @xmlDate)
94    BEGIN
95      INSERT INTO PAS_Taux (nir_PAS, matricule_PAS, taux_PAS, Date_PAS) VALUES (@NIR_PAS, @matricule_PAS, @taux_PAS, @xmlDate)
96    END
97    FETCH NEXT FROM XmlCursor INTO @NIR_PAS, @matricule_PAS, @taux_PAS
98  END
99
100CLOSE XmlCursor
101DEALLOCATE XmlCursor
102*/