1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
| DECLARE @TespSP bit = 1
DECLARE @SourceFolder nvarchar(128)
DECLARE @DestinationFolder nvarchar(128)
DECLARE @SourceFile nvarchar(128)
DECLARE @BULK nvarchar(max)
DECLARE @fileExist int
DECLARE @MasqueNomFichier nvarchar(249)
DECLARE @FichierCheminComplet nvarchar(249)
DECLARE @LigneFichier nvarchar(max)
DECLARE @CaractereFinLigne nvarchar(249)
DECLARE @CMD varchar(500)
DECLARE @NomFICHIER nvarchar(255)
DECLARE @NumCEGID nvarchar(55)
DECLARE @DateStatut datetime
DECLARE @Code nvarchar(55)
DECLARE @EnvoiEmail_id nvarchar(max)
DECLARE @DateCree datetime
DECLARE @DateModif datetime
DECLARE @Jour datetime
DECLARE @Annee datetime
DECLARE @Mois Datetime
SET @SourceFolder = 'D:\DataFlow\Heppner\In\'
SET @DestinationFolder = 'D:\DataFlow\Heppner\done\'
/**********************************************************************************/
/*************** 1) CREATION DES TABLES TEMPORAIRES ***************/
/**********************************************************************************/
/***Creation de la table temporaire #ListeFiles listant les fichiers a integrer***/
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [Name] like '#ListeFiles%' AND type in (N'U')) DROP TABLE #ListeFiles
CREATE TABLE #ListeFiles (FichierCheminComplet nvarchar(249),NomFichier nvarchar(249))
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [Name] like '#ImportHeppner%' AND type in (N'U')) DROP TABLE #ImportHeppner
CREATE TABLE #ImportHeppner ([Ligne] [nvarchar](max))
/**********************************************************************************/
/*************** 2) RECUPERATION DE LA LISTE DES FICHIERS HEPPNER A TRAITER ***************/
/**********************************************************************************/
SET @MasqueNomFichier = 'REP%'
/**Creation d'une table temporaire #DirectoryTree qui va contenir la liste de tous les fichiers qui se trouve dans un dossier**/
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [Name] like '#DirectoryTree%' AND type in (N'U')) DROP TABLE #DirectoryTree
CREATE TABLE #DirectoryTree ( id int IDENTITY(1,1), SubDirectory nvarchar(249),Depth int,IsFile bit)
/**Injection dans #DirectoryTree avec la liste des fichiers se trouvant dans le dossier contenant la copie des fichier Heppner pour le fichier courant**/
INSERT INTO #DirectoryTree (SubDirectory, Depth, IsFile) EXEC master.sys.xp_dirtree @SourceFolder,1,1
/**Boucle permettant de récupérer uniquement les fichiers se trouvant au niveau du dossier courant (pas les sous dossiers) et dont le nom ressemble au masque adapté à la situation**/
DECLARE CSUBLISTEFICHIER CURSOR
READ_ONLY FORWARD_ONLY
FOR
SELECT SubDirectory FROM #DirectoryTree WHERE SubDirectory like @MasqueNomFichier AND IsFile = 1 AND Depth = 1
OPEN CSUBLISTEFICHIER
FETCH NEXT FROM CSUBLISTEFICHIER INTO @NomFichier
WHILE @@FETCH_STATUS = 0
BEGIN
IF (RIGHT(@SourceFolder,1) = '\') SET @FichierCheminComplet = @SourceFolder + @NomFichier
INSERT INTO #ListeFiles VALUES (@FichierCheminComplet, @NomFICHIER)
FETCH NEXT FROM CSUBLISTEFICHIER INTO @NomFichier
-- select * from #ListeFiles
END
CLOSE CSUBLISTEFICHIER
DEALLOCATE CSUBLISTEFICHIER
/**Fin Boucle**/
/**********************************************************/
/*************** 3) TRAITEMENT DES FICHIERS ***************/
/**********************************************************/
DECLARE CLISTEFICHIER CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT FichierCheminComplet, NomFichier FROM #ListeFiles
OPEN CLISTEFICHIER
FETCH NEXT FROM CLISTEFICHIER INTO @FichierCheminComplet, @NomFICHIER
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TespSP =1 select 'new'
/***Creation de la table temporaire #ImportHeppner listant les données a envoyer vers la table tblHeppner***/
-- select * from #ImportHeppner
BEGIN
SET @BULK = 'BULK '
SET @BULK += 'INSERT #ImportHeppner '
SET @BULK += 'FROM ''' + @FichierCheminComplet +''' '
SET @BULK += 'WITH ( '
SET @BULK += ' FIRSTROW = 1, '
SET @BULK += ' ROWTERMINATOR = ''0x0a'', '
SET @BULK += ' CODEPAGE = ''ACP'' '
SET @BULK += ')'
EXEC (@BULK)
-- INSERT INTO #ImportHeppner(NomFichier) Values(@NomFICHIER)
-- UPDATE dlHeppnerFiles SET NomFichier= SUBSTRING(@FichierCheminComplet,23,37) WHERE NomFICHIER IS NULL
print @NomFICHIER
-- SET @CMD = 'move "' + @FichierCheminComplet + '" "' + @DestinationFolder + '"';
-- EXEC xp_cmdshell @CMD
end
FETCH NEXT FROM CLISTEFICHIER INTO @FichierCheminComplet, @NomFICHIER
END
CLOSE CLISTEFICHIER
DEALLOCATE CLISTEFICHIER
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
-- CREATE INDEX Code_Heppner ON [Pivot].[dbo].[dlCodeHeppner]([SIT],[JUS])
SELECT [ligne] FROM #ImportHeppner
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @LigneFichier
WHILE @@FETCH_STATUS = 0
BEGIN
IF( SELECT COUNT(*) FROM [Pivot].[dbo].[dlHeppnerFiles] WHERE NomFICHIER = @LigneFichier and NumCEGID is NULL ) =0
BEGIN
INSERT INTO Pivot.dbo.dlHeppnerFiles
SELECT DISTINCT @NomFICHIER
,SUBSTRING(ligne,1,6) AS NumCEGID
,convert (datetime2, stuff(stuff(SUBSTRING(Ligne,38,6), 5, 0, '/'), 3, 0, '/'), 3) AS DateStatut
,SUBSTRING(ligne,44,3) AS SIT
,SUBSTRING(ligne,47,3) AS JUS
, Libelle_fr
,GETDATE()
,DateModif=NULL
,DateTrsCRM=NULL
FROM #ImportHeppner
inner JOIN [Pivot].[dbo].[dlCodeHeppner] as CH on SUBSTRING(#ImportHeppner.Ligne,44,3)=CH.SIT AND SUBSTRING(#ImportHeppner.Ligne,47,3)=CH.JUS
WHERE @LigneFichier = Ligne AND SUBSTRING(ligne,1,6) LIKE '2%' OR SUBSTRING(ligne,1,6) LIKE '1%'
END
-- truncate table dlHeppnerFiles
-- select * from dlHeppnerFiles
FETCH NEXT FROM MY_CURSOR INTO @LigneFichier
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR |
