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
|
IF OBJECTPROPERTY( OBJECT_ID('P_A_ImportPcVue'), 'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.P_A_ImportPcVue
DELETE FROM dbo.T_PROCEDURE_PRC WHERE PRC_NAME = 'P_A_ImportPcVue'
END
GO
CREATE PROCEDURE dbo.P_A_ImportPcVue
@LaDate DATETIME
AS
SET NOCOUNT ON
DECLARE @FileExist INT,
@Fichier Varchar(128),
@Chemin Varchar(255),
@Mnemo Varchar(128),
@GROUPE CHAR(1),
@TRONCON VARCHAR(6),
@EQUIPEMENT VARCHAR(30),
@ORGANE VARCHAR(10),
@DEFAUT VARCHAR(50),
@MAX INT,
@SQLSTRING VARCHAR(255)
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tmppcvue' AND type = 'U')
DROP Table tempdb.dbo.tmppcvue
Create Table tempdb.dbo.tmppcvue (
Ladate VARCHAR (14) NOT NULL ,
Heure VARCHAR (8) NOT NULL ,
Mnemo VARCHAR (128) NOT NULL ,
Detail VARCHAR (128) NOT NULL ,
Etat VARCHAR (128) NOT NULL
)
--EXEC master.dbo.xp_cmdshell 'NET USE "\\172.28.21.1\e$" /USER:user mdp, NO_OUTPUT
--EXEC master.dbo.xp_cmdshell 'NET USE "\\172.28.21.4\e$" /USER:user mdp, NO_OUTPUT
SET @Fichier = CONVERT(VARCHAR(8),@LaDate,112) + ' Journal PcVue.txt'
SET @Chemin = '\\172.28.21.1\e$\' + RTRIM(@Fichier)
EXEC master.dbo.xp_fileexist @Chemin , @FileExist OUTPUT
if (@FileExist = 1)
BEGIN
SET @SQLSTRING = 'BULK INSERT tempdb.dbo.tmpPcvue'+
' FROM ''' + RTRIM(@Chemin) + ''''+
' WITH (FIELDTERMINATOR = '';'',ROWTERMINATOR = ''\n'',CODEPAGE = ''ACP'' )'
EXEC master.dbo.sp_sqlexec @SQLSTRING
END
ELSE
BEGIN
SET @Chemin = '\\172.28.21.4\e$\' + RTRIM(@Fichier)
EXEC master.dbo.xp_fileexist @Chemin , @FileExist OUTPUT
if (@FileExist = 1)
BEGIN
SET @SQLSTRING = 'BULK INSERT tempdb.dbo.tmpPcvue'+
' FROM ''' + RTRIM(@Chemin) + ''''+
' WITH (FIELDTERMINATOR = '';'',ROWTERMINATOR = ''\n'',CODEPAGE = ''ACP'' )'
EXEC master.dbo.sp_sqlexec @SQLSTRING
END
ELSE
Print RTRIM(@Fichier) + ' n''est pas présent sur les serveurs.'
END
IF @@Error <> 0
GOTO Gestion_Erreur
DECLARE CursField CURSOR
FOR
SELECT MNEMO
FROM tempdb.dbo.tmppcvue
WHERE MNEMO not in (SELECT MNEMO FROM dbo.T_IMPORTMNEMO_IMN)
GROUP BY Mnemo
OPEN CursField
FETCH CursField INTO @Mnemo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @GROUPE = ''
SET @TRONCON = ''
SET @EQUIPEMENT = ''
SET @ORGANE = ''
SET @DEFAUT = ''
SELECT @MAX = MAX(position)
FROM dbo.fn_SplitShort(@Mnemo,'.')
SELECT @DEFAUT = value
FROM dbo.fn_SplitShort(@Mnemo,'.')
WHERE position = @MAX
SELECT @GROUPE = value
FROM dbo.fn_SplitShort(@Mnemo,'.')
WHERE position <> @MAX AND position = 1
SELECT @TRONCON = value
FROM dbo.fn_SplitShort(@Mnemo,'.')
WHERE position <> @MAX AND position = 2
SELECT @EQUIPEMENT = value
FROM dbo.fn_SplitShort(@Mnemo,'.')
WHERE position <> @MAX AND position = 3
SELECT @ORGANE = value
FROM dbo.fn_SplitShort(@Mnemo,'.')
WHERE position <> @MAX AND position = 4
INSERT INTO dbo.T_IMPORTMNEMO_IMN (GROUPE,TRONCON,EQUIPEMENT,ORGANE,DEFAUT,MNEMO)
VALUES (@GROUPE,@TRONCON,@EQUIPEMENT,@ORGANE,@DEFAUT,@MNEMO)
FETCH CursField INTO @Mnemo
END
CLOSE CursField
DEALLOCATE CursField
IF @@Error <> 0
GOTO Gestion_Erreur
INSERT INTO dbo.T_EVENEMENT_EVT
SELECT e.EQP_ID,CONVERT(datetime, ladate + ' ' + Heure ),l.LEV_ID
FROM tempdb.dbo.tmppcvue t
LEFT OUTER JOIN dbo.T_IMPORTMNEMO_IMN i ON t.MNEMO = i.MNEMO
LEFT OUTER JOIN dbo.T_LIBELLEEVENT_LEV l ON t.ETAT = l.LEV_LIBELLE
LEFT OUTER JOIN dbo.T_EQUIPEMENT_EQP e ON e.GROUPE = i.GROUPE AND e.TRONCON = i.TRONCON AND e.DEFAUT = i.DEFAUT
AND e.EQUIPEMENT = i.EQUIPEMENT AND e.ORGANE = i.ORGANE
IF @@Error <> 0
GOTO Gestion_Erreur
IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tmppcvue' AND type = 'U')
DROP Table tempdb.dbo.tmppcvue
RETURN
Gestion_Erreur:
Declare @LibErreur as Varchar(255)
Select @LibErreur = description from master.dbo.sysmessages where error = @@Error
Print 'Erreur dans P_A_ImportPcVue : N° '+LTRIM(STR(@@Error)) + ' ' + @LibErreur
GO
INSERT INTO dbo.T_PROCEDURE_PRC(PRC_NAME, PRC_COMMENT)
VALUES('P_A_ImportPcVue', 'Importation des Journaux PCVUE')
GO |
Partager