Blog de développement

On a vu comment télécharger l'annuaire Chorus Pro de façon régulière ( Lire... →) : on obtient un fichier Excel XLSX de 27 Mb. Dans un premier temps, on veut juste consulter pour savoir si la facture doit (ou peut) être deposée sur Chorus. C'est simple... Si l'identifiant, en fait le SIRET Client, est dans la liste c'est obligatoirement pour Chorus, Sinon... non...

On envisage 3 stratégies, sans jamais utiliser Excel, qu'on n'a pas sur le serveur: par DTS (MSSQL) (c'est ici), par le Driver ODBC de Microsoft ( Lire... →) ou par un utilitaire opensource, multi-plateforme ( Lire... →)

2 difficultés sont face à nous: il y a 2 feuilles dans le XLSX, et, ce fichier est quand même bien gros. Moins que son alter-ego XML, proposé aussi par Chorus et qui fait... 200 Mb !

Pourquoi utiliser DTS de MSSQL ?

En commerce électronique, l'utilisation d'une base de données est inévitable. J'utilise MSSQL (2016). Je sais... C'est historique... Evidemment, Excel n'est pas adapté: on passe à un outil supérieur. Utiliser Data Transformation Services (ou un équivalent) est l'outil qui va avec. En plus on peut l'automatiser

Il faut disposer de SSMS, qui en est à sa version 2019. C'est gratuit. De même que des version simplifiées de MSSQL. On le télécharge ici. Il faut aussi les driver Excel, qui n'est plus installé par défaut. Il est gratuit et fait parti du package : Moteur de base de données Microsoft Access 2016 redistribuable

Lors de l'installation, SSMS installe des fonctions sans objet pour nous (Azure). Les instructions pour installer sans ces fioritures sont ici.installer sans ces fioritures sont ici. Pour le télécharger, c'est ici.

Interroger en local, automatiquement et rapidement

A la main, c'est fastidieux: il faut automatiser. C'est possible avec l'API, mais l'obtention de la clé de développeur est d'une complexité ! Alors que je ne souhaite utiliser que 2 fonctions: savoir si le client exige Chorus et y déposer ma facture...

On pourrait interroger la page avec un simple requete GET dont la forme serait:
https://communaute.chorus-pro.gouv.fr/annuaire-cpro/?nompage=11000201100044&envoyer=
Là encore, ce serait fastidieux...

Pour l'automatisation, sans avoir recours à l'API, c'est simple: on va télécharger l'annuaire, régulièrement et automatiquement et le conditionner pour une interrogation sur une simple liste en format texte

Télécharger les listes simples depuis mon archive

Je vais expliquer plus avant comment faire sans dépendre de personne. En fait, j'archive les 2 listes régulièrement, sous forme simple. Il suffit de télécharger mon archive régulièrement, par exemple chaque Mardi, pour être à jour, sans effort.

Voici la commande CURL. Mieux vaut installer la toute dernière version, disponible ici: https://curl.se/docs/manpage.html, et installer 7Zip depuis son site officiel:

Télécharger l'annuaire

L'annuaire Chorus est mis à jour régulièrement, le Lundi (sauf exception, comme un jour férié): on va donc télécharger l'annuaire, en entier, à ce même rythme.

On télécharge la page d'acceuil pour déterminer le nom du fichier .xlsx à télécharger

					    
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")

sAcceuilFilePath = "C:\Users\Moi\Documents\" & "Acceuil.htm"	' // a personaliser
sURL = "https://communaute.chorus-pro.gouv.fr/annuaire-cpro/?"

objWinHttp.open "GET", sURL, False
objWinHttp.send ""
objWinHttp.waitForResponse
If objWinHttp.Status = 200 Then 
	result = objWinHttp.responseText
	' // on cherche la date de derniere mise à jour qui est dans le nom du fichier XLS. Par exemple:
	' // https://communaute.chorus-pro.gouv.fr/wp-content/uploads/2022/12/AIFE-Chorus-Pro-Annuaire_20221219.xlsx
Else
	'  // MsgBox "objWinHttp.Status a une erreur: " & objWinHttp.Status  '// pour debugger
End If

Set objOpen = objFSO.OpenTextFile(sAcceuilFilePath, 2)   ' // ForWriting = 2
objOpen.Write result
objOpen.Close
Set objOpen = Nothing
					    
				    

Déterminer l'URL de l'annuaire à télécharger

On utilise ici un outil peu courant mais assez intuitif: MSHTML. On cherche juste à déterminer l'URL de l'annuaire le plus récent.

					    
Set html = CreateObject("htmlfile")   ' // MSHTML reference: https://tinyurl.com/GRA-HTML-REF
If InStr(result, "charger XLS")> 0 then   ' // la reponse contient telecharger XLS
	html.open
	html.close
	html.body.innerHTML = result
	Set objResult = html.GetElementsByTagName("input")  ' // cree une collection avec tous les tags input
	For i= 0 to objResult.Length - 1
		if InStr (objResult(i).Attributes.getNamedItem("value").value,  "charger XLS") > 0 then
			sURL = objResult(i).Attributes.getNamedItem("onClick").value
			sURL = replace (sURL, "window.location.href='", "")
			sURL = replace (sURL, "'", "")
			i = objResult.Length	' // on a ce que l'on cherchait, donc on sort...
		End If 	
	Next
End If  	' // J'ai fini la determination de URL						
						
				    

On télécharge alors l'annuaire

Il nous reste à télécharger l'annuaire sous la forme de son fichier .xlsx ; bien que le format .xlsx soit de type texte, il semble qu'il faille enregistrer comme Binary

					    					
If sURL <> "" then 
	pos = InstrRev (sURL, "/")
	sFileName = right(sURL, Len (sURL) - pos + 0) ' // on determine le nom du fichier .xlsx
	sPath= sDossierAnnuaire & sFileName
	If NOT objFSO.FileExists (sPath) then 
		objWinHttp.open "GET", sURL,  true   'le troisième paramètre spécifie que WinHTTP soit exécuté de manière asynchrone.
		objWinHttp.send ""
		objWinHttp.waitForResponse
		If objWinHttp.Status = 200 Then 
			SaveBinaryData sPath, objWinHttp.responseBody	' // Save the Binary data to Disk
			Do until objFSO.FileExists (sPath) = True
				WScript.Sleep 100
			Loop
		Else
			'  // MsgBox "objWinHttp.Status a une erreur: " & objWinHttp.Status  '// pour debugger
		End If
	End If
End If

Function SaveBinaryData(FileName, Data)
	Const adTypeText = 1	' // adTypeText for binary = 1
	Const adSaveCreateOverWrite = 2	
	Dim BinaryStream
	Set BinaryStream = CreateObject("ADODB.Stream") '  // ' Create Stream object	
	BinaryStream.Type = adTypeText ' // Specify stream type - we want To save Data/string data.	
	BinaryStream.Open		' // Open the stream And write binary data To the object
	BinaryStream.Write Data	
	BinaryStream.SaveToFile FileName, adSaveCreateOverWrite	' // Save binary data To disk
End Function

				    

Avec Curl, c'est encore plus simple...

					    	
sCommand = """curl.exe"" -k -L -o """ & sPath & """  --url """ & sUrl & """ "	
ReturnError = oShell.Run (sCommand, 0, True)
	' // if ReturnError <> 0 then MsgBox ReturnError  '// pour debugger
						
				    

Convertir le fichier Excel pour une consultation rapide

On dispose du fichier localement, sans plus dépendre d'Internet, sans compte API. Il y a une stratégie à adopter pour pouvoir consulter aisément l'annuaire ainsi téléchargé.

Le fichier Excel ainsi téléchargé est assez gros: 27 Mb. Il contient les Structures, et pour celles qui sont subdivisées en Services, la liste des Services. Chorus gère 200.000 structures! et env. 80.000 Services...
Le fichier contient beaucoup de champs qui ne nous interessent pas trop: on veut juste savoir si le client est inscrit dans Chorus et, si oui, quels sont ses services. Ainsi on pourra éviter des erreurs lors de la saisie de commande et de facture. 27 Mb, c'est lourd! Ca charge difficilement, alors qu'un simple petit fichier texte nous suffirait.

Stratégies possibles de transformation de l'annuaire:

• - Entrée en base de données classique (MSSQL, Access, LibreOffice Base, ...)
• - Driver permettant la connexion directe à Excel
• - Extraction de 2 fichiers CSV, simples et légers

Chaque stratégie fait l'objet d'un billet de blog propre