SQL-SERVER PROC. SEND_FTP

-- =============================================
-- Author:		Yannick Laussanne
-- Create date: 26/06/2012
-- Description:	Envoi d'un fichier existant sur le srv de BDD vers un FTP Unix ou Windows
--
--	On construit un fichier de commandes ftp temporaire et on exécute
--	la commande console ftp -s:"fichier_de_cmd" serveurFtp
--
--	Attention : si le param @rep_fait ne commence pas par [DISQUE]:\ on le concatène à @path
--	sinon on le prend tel quel
-- =============================================
CREATE PROCEDURE [dbo].[util_SendFTP]
	@serveurFTP nvarchar(300),		/*serveur FTP sans préfixe*/
	@loginFTP nvarchar(150),		/*login ftp*/
	@passFTP nvarchar(150),			/*pass ftp*/
	@workingDirFTP nvarchar(150),	/*doit-on se positionner sur un ou plusieurs sous répertoire du ftp*/
	@path varchar(255),				/*Répertoire physique de travail sur le srv de BDD*/
	@file varchar(255),				/*Fichier dans le "@path" à envoyer sur le ftp*/
	@rep_fait varchar(255),			/*Répertoire de move final pour les fichiers envoyés, si vide, supprime le fichier source*/
	@loginBCP varchar(50),			/*login de la connexion SQL pour BCP*/
	@passBCP varchar(50),			/*pass de la connexion SQL pour BCP*/
	@msgRet varchar(500) output,	/*Eventuellemnt, retourne un msg d'erreur*/
	@debug bit = 0					/*Affiche les commandes*/
AS
BEGIN
	SET NOCOUNT ON;

	SET @msgRet = 'OK'

	DECLARE 
		@cmd varchar(4000),
		@retShell int,
		@retShellFTP int,
		@req varchar(2500),
		@tmpCMDFTP varchar(100),		/* Fichier de cmd FTP temporaire */
		@fileToSend varchar(255)		/* path + file */	
	
	/* Normalement, les paramètres de chemin ont été validés par l'appelant */		
	IF RIGHT(@path,1) != '\'
		SET @path = @path + '\'
			
	/* Le fichier à envoyer */
	SET @fileToSend = @path + @file

	/* Le Fichier de cmd FTP temporaire */
	SET @tmpCMDFTP = 'tmpCMDFTP_' + replace(CONVERT(varchar,GETDATE(),114),':','') + '.txt'


	/* Crée et rempli le fichier de cmd FTP tmp en passant par une table de travail */
	DECLARE @tableTravail nvarchar(150) ; SET @tableTravail = db_name() + N'.dbo.W_CMDFTP_TMP'
	IF OBJECT_ID(@tableTravail, N'U') is null
		CREATE TABLE W_CMDFTP_TMP (
			_cpt int identity(1,1), 
			_ligne nvarchar(1024)
		);
	ELSE 
		TRUNCATE TABLE W_CMDFTP_TMP;	

	/* Construit les commandes selon le protocole ftp -s */
	INSERT INTO W_CMDFTP_TMP VALUES(@loginFTP);					/*login*/
	INSERT INTO W_CMDFTP_TMP VALUES(@passFTP);					/*pass*/
	IF ISNULL(@workingDirFTP,'') != ''							/*se place sur un sous-rep*/
		INSERT INTO W_CMDFTP_TMP VALUES('cd ' + @workingDirFTP);
	IF CHARINDEX('/', @loginFTP)>0								/*si login unix/EDI*/
	BEGIN							
		INSERT INTO W_CMDFTP_TMP VALUES('prompt');				/*obligatoire pour faire marcher le mode interractif*/
		INSERT INTO W_CMDFTP_TMP VALUES('prompt');				/*obligatoire pour faire marcher le mode interractif*/
	END
	INSERT INTO W_CMDFTP_TMP VALUES(N'send "'+@fileToSend+'"');	/*cmd FTP send OU mput si binary*/
	INSERT INTO W_CMDFTP_TMP VALUES(N'bye');					/*ferme ftp*/


	/* Requete d'écriture ligne à ligne */
	SET @req = 'SELECT _ligne FROM '+db_name()+'..W_CMDFTP_TMP ORDER BY _cpt'


	/* Création et remplissage du fichier de cmd tmp */
	SET @cmd = 'BCP "' + @req + '" queryout "' + @path + @tmpCMDFTP + '" -c -CACP -U'+@loginBCP+' -P'+@passBCP+';'
	IF @debug = 1
		print @cmd
	EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
	IF @retShell != 0
	BEGIN
		SET @msgRet = 'Erreur bloquante sur la commande BCP : ' + @cmd
		RETURN 1;
	END


	/* Ouvre le ftp en donnant le fichier de params créé ci-dessus */
	/* Ce qui exécutera le "send" du fichier donné vers le répertoire FTP ouvert */
	SET @retShellFTP = 0
	DECLARE @OUT TABLE(_id int identity(1,1), _out varchar(1024))

	SET @cmd = 'ftp -s:"' + @path + @tmpCMDFTP + '" ' + @serveurFTP
	IF @debug = 1
		print @cmd

	INSERT INTO @OUT(_out)
	EXEC @retShellFTP = xp_cmdshell @cmd




	/* Essaye de renvoyer un message d'erreur cohérent */
	IF @retShellFTP != 0
	BEGIN
		SET @msgRet = 'ERREUR INCONNUE: ' + @cmd
	END
	ELSE IF exists(SELECT 1 FROM @OUT WHERE _out like '%cannot log in%' or _out like '%Please login with%') or 
			not exists(SELECT 1 FROM @OUT WHERE _out is not null)
	BEGIN
		SET @retShellFTP = 99
		SET @msgRet = 'ERREUR CONNEXION: ' + @cmd + char(13) + isnull((
			SELECT top 1 replace(replace(_out,'¶','o'), 'Ú','e') FROM @OUT 
			WHERE isnull(_out,'') like '%cannot log in%' or isnull(_out,'') like '%Please login with%'),'?')
	END
	ELSE IF exists(SELECT 1 FROM @OUT WHERE _out like '%H_te inconnu%' or _out like '%Commande non valide%' or _out like '%Non connect_%')
	BEGIN
		SET @retShellFTP = 99
		SET @msgRet = 'ERREUR SERVEUR: ' + @cmd + char(13) + isnull((
			SELECT top 1 replace(replace(_out,'¶','o'), 'Ú','e') FROM @OUT 
			WHERE isnull(_out,'') like '%H_te inconnu%' or isnull(_out,'') like '%Commande non valide%' or isnull(_out,'') like '%Non connect_%'),'?')
	END


	/* Supprime le fichier de cmd FTP tmp */
	SET @cmd = 'del "' + @path + @tmpCMDFTP + '"'
	IF @debug = 1
		print @cmd
	EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT
	IF @retShell != 0
	BEGIN
		IF LEN(@msgRet)>0
			SET @msgRet = @msgRet + char(13)
		SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande DEL : ' + @cmd
	END


	/* Si demandé et aucune erreur, déplace ou supprime le fichier qui vient d'etre envoyé vers le ftp */
	IF @retShellFTP = 0
	BEGIN
		IF len(ISNULL(@rep_fait,'')) > 0
		BEGIN
			IF RIGHT(@rep_fait,1) != '\'
				SET @rep_fait = @rep_fait + '\'
		
			IF @rep_fait like '[A-Z]:%\'
				SET @cmd = 'MOVE /Y "' + @fileToSend + '" "' + @rep_fait + @file + '"'
			ELSE
				SET @cmd = 'MOVE /Y "' + @fileToSend + '" "' + @path + @rep_fait + @file + '"'
				
			IF @debug = 1
				print @cmd

			EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT	
			IF @retShell != 0
			BEGIN
				IF LEN(@msgRet)>0
					SET @msgRet = @msgRet + char(13)
				SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande MOVE : ' + @cmd
			END
		END
		ELSE
		BEGIN
			SET @cmd = 'del "' + @fileToSend + '"'
			IF @debug = 1
				print @cmd
			EXEC @retShell = xp_cmdshell @cmd, NO_OUTPUT	
			IF @retShell != 0
			BEGIN
				IF LEN(@msgRet)>0
					SET @msgRet = @msgRet + char(13)
				SET @msgRet = @msgRet + 'Erreur non bloquante sur la commande DEL : ' + @cmd
			END
		END	
	END
		
	IF @debug = 1
		print char(13) + @msgRet

	RETURN @retShellFTP;
END