FileConverter

Deze service is ontwikkeld om voor de distributie omgeving bestanden met orders te kunnen verwerken naar een generiek .csv formaat.
Dit generieke .csv formaat kan door de RGB.ImportService verwerkt worden tot orders.

De FileConverter is te openen door in TMS naar een klant te gaan die al een bestaande import heeft en daar op "Configureer generieke import" te klikken. Als er een config voor een nieuwe klant gemaakt dient te worden dan graag eerst de stappen volgen op RGB.ImportService

Nieuwe import maken

Basis instellingen

In de FileConverter kan via File → New een nieuw import worden gemaakt

In de daarop volgende popup kan de naam van de import configuratie worden ingevoerd.

Configuratie kopiëren / verplaatsen van test naar prod

In de FileConverter kan via File → Export een .xml dump worden gemaakt van de bestaande config. Dit kan handig zijn als de configuratie gedeeld moet worden of als deze moet worden overgezet van Test naar Prod.
De .xml dump kan geopend worden in notepad(++), vervolgens kunnen er in de node MainSettings de ImportFolder en ExportFolder aangepast worden zodat deze aansluiten op de mappenstructuur voor de nieuwe omgeving.
Om op Prod een configuratie in te lezen kan er via File → Import de .xml dump worden geselecteerd. Het is mogelijk om meerdere configs tegelijk te importeren waardoor geimporteerde de config niet automatisch wordt geopend, dit moet de gebruiker nog doen via File → Open

File configuration

In dit tabblad kunnen een aantal algemene instellingen worden ingesteld voor de import

Import name → De naam van de import, deze is puur bedoeld zodat de gebruiker deze kan herkennen.
Importfolder → De locatie waaruit de bestanden moeten worden opgepakt. Bij voorkeur een netwerkmap en als er al import bestaan is het verstandig om dezelfde structuur aan te houden als bij de bestaande imports
Exportfolder → De locatie waar de generieke .csv bestanden naar toe worden geschreven, deze map is belangrijk omdat deze later aan de TMS klant gekoppeld moet worden.
Type → Geeft het type bestand aan
XSLT → Hier kan de locatie van het xslt bestand worden aangegeven waarmee het import berstand moet worden ingelezen. (optioneel)
Import type → We ondersteunen via deze import alleen het aanmaken van orders.
Skip row count → Meestal heeft een bestand dat geïmporteerd moet worden op de eerste regel de definities van de kolom staan, als er echter nog meer regels aan het begin van de import moeten worden overgeslagen dan kan dat hiermee worden aangegeven
Column headers → Zoals hierboven staat, als de eerste regel de kolom definities bevat dan kan je hier True instellen.
Delimiter → Het teken dat aangeeft waarmee de waarden uit het import bestand gescheiden moet worden.
Output file prefix → Hier kan een tekst worden ingevoerd die wordt toegevoegd aan de bestandsnaam van het output bestand. Dit kan handig zijn als er meerdere imports worden gemaakt voor dezelfde klant, deze zullen dan dezelfde Exportfolder hebben. Om duidelijk te kunnen zien waar het bestand vandaan komt kan hier een waarde worden ingevoerd.
Inactief → Bepaald of bestanden uit de import folder worden opgepakt
Mege sheets → Soms staat de data die moet worden verwerkt op meerdere sheets of op een 2e sheet in een excel bestand, door deze optie aan te zetten wordt alle data van alle sheets ingelezen. Overbodige data kan worden weg gefilterd m.b.v. queries

Load test data → Hiermee wordt het bestand ingevoerd in de File name ingelezen.

Field definitions

Als er nog geen field definitions bekend zijn, bij bijvoorbeeld een nieuwe import dan kan er het beste een bestand worden ingelezen via de Load test data knop. Deze zal voor alle kolommen op de eerste regel die hij tegenkomt een field definition aanmaken.
Aan de field definitions kunnen onbeperkt kolommen worden toegevoegd, deze kunnen in de verder in de applicatie gebruikt worden, de omschrijving moet wel uniek zijn. De properties StartPosition, Length en Type worden niet meer gebruikt.

SQL conversions

In dit scherm staan de queries die uitgevoerd worden op de data uit het voorbeeld bestand.

Disabled → Geeft aan of de query wel of niet actief is
Sort → Geeft de volgorde aan waarop de queries uitgevoerd moeten worden, dit gebeurd van laag naar hoog.
Description → Optionele omschrijving van de query
Statement → De daadwerkelijke query
Delete → Optie om de query te verwijderen

Het is mogelijk om hier de queries te maken en aan te passen, maar een betere plek is om dit in het tabblad SQL designer te doen en hier alleen de query volgorde en enabled/disabled aan te passen, een query te vervangen of een delete uit te voeren.

Field mappings

In dit scherm wordt aangegeven welke kolom naar welk veld wordt gezet in het output bestand. Omdat het output bestand een vaste indeling heeft staan de Doel kolommen hier vast.
Het output bestand bevat data voor zowel de orderkop als de orderregels, het is gebruikelijk om de data voor de orderkop voor alle orderregels met dezelfde waarde te vullen.
Het meest gebruikelijke is om de kolommen die je wilt gebruiken toe te voegen als nieuw veld aan de Field Definitions en vervolgens met queries deze met de juiste data vuld

OrderOmschrijving → Omschrijving van de order
OrderReferentie → De externe referentie van de order
TransportDatum → Datum van de order (format dd/MM/jjjj)
LaadLosCode → De laadloscode van de stop. Te beheren in de mappingen op de klantenkaart → Mappingen → EDI instellingen → Mappingtype laadloscodes
AdresCode → Wordt gebruikt om het adres aan bestaande adressen te mappen in Transplan, moet uniek zijn per adres per klantimport
AdresNaam → Wordt gebruikt om een adres aan te maken als adres niet gekoppeld kan worden op adrescode
AdresAdres → Wordt gebruikt om een adres aan te maken als adres niet gekoppeld kan worden op adrescode
AdresPostcode → Wordt gebruikt om een adres aan te maken als adres niet gekoppeld kan worden op adrescode
AdresPlaats → Wordt gebruikt om een adres aan te maken als adres niet gekoppeld kan worden op adrescode
AdresLand → Wordt gebruikt om een adres aan te maken als adres niet gekoppeld kan worden op adrescode
Route → Het routenummer van de order, obv dit nummer en de TransportDatum wordt een order ook geüpdatet als deze al bestaat
Stop → Het stopnummer van de regel, wordt automatisch met *10 verhoogd
TijdVan → Dit is de bloktijden van, van de orderregel (format HH.mm)
TijdTot → Dit is de bloktijden tot, van de orderregel (format HH.mm)
RegelReferentie → De orderregel referentie
Product → Tekst veld product van orderregel
SpecifiekeOrdersoort → Gebruikt om in TMS 3.7 deze tekst waarde te koppelen aan een bestaande ordersoort uit TMS van de klant
RegelOpmerking → De orderregel opmerking
Palletplaatsen → Aantal palletplaatsen van de orderregel
ExterneReferentie → Wordt niet meer gebruikt
NormUren → De orderregel norm uren, wordt bij klanten gebruikt om vergelijking te maken tussen de norm/aangeleverde uren en werkelijke uren bij afmelden (format HH.mm waarbij minuten o.b.v. 0 tm 100 is i.p.v. 0 tm 60. Een norm van 6 uur en 30 minuten moet worden aangeleverd als 6.50)
NormKilometers→ De orderregel kilometers, wordt bij klanten gebruikt om vergelijking te maken tussen de norm/aangeleverde kilomters en werkelijke uren bij afmelden
StopklasseVast → De orderregel stoptijdklasse vaste tijd
StopklasseVariabel → De orderregel stoptijdklasse variabele tijd, wordt berekend met palletplaatsen
ExtraOpmerking → De order opmerking
emballage code → Wordt gebruikt om de palletplaatsen te berekenen als deze leeg zijn. Te beheren in de mappingen op de klantenkaart → Mappingen → EDI instellingen → Mappingtype emballagecode. Als palletplaatsen geen waarde heeft dan wordt via deze gekoppelde emballage de PalletPlaatsEquivalenten opgehaald deze wordt vermenigvuldigd met emballage aantal en dat resultaat wordt gebruikt als PalletPlaatsen.
emballage aantal → Wordt gebruikt in berekening met emballage code om PalletPlaatsen te berekenen
emballage bonnummer → Wordt niet meer gebruikt
DefaultAankomsttijd → Eindtijd van de order
DefaultVertrektijd → Starttijd van de order
EDIReferentie → Klantreferentie van de order
Aankomsttijd → De orderregel geplande aankomsttijd
Vertrektijd → De orderregel geplande vertrektijd
Gewicht → Het gewicht van de orderregel
VastePrijs → De vaste prijs van de order
Planafdeling → De planafdeling van de order, wordt gemapt in TMS op de klantenkaart → Mappingen → EDI instellingen → Mappingtype planafdelingen
OrderRouteNummer → Kan gebruikt worden als overkoepelend nummer. Orders worden dan wel aangemaakt obv Transportdatum en Route, maar alle orders met hetzelfde OrderRouteNummer komen in dezelfde rit

Let op! Als de onderstaande velden worden gevuld in de import dan zal de order altijd van type groupage zijn omdat er dan mogelijk meerdere orders in dezelfde rit kunnen worden gezet.
RitOmschrijving → Optionele ritomschrijving voor rit wanneer OrderRouteNummer is gevuld
RitDatum→ Optionele rit datumvoor rit wanneer OrderRouteNummer is gevuld
RitStarttijd → Optionele rit starttijd voor rit wanneer OrderRouteNummer is gevuld
Riteindtijd → Optionele rit eindtijd voor rit wanneer OrderRouteNummer is gevuld

SQL designer

In dit scherm kan met queries schrijven en testen om de data in het bovenste grid te bewerken.

Test → Voert alle queries uit die in dit scherm in het SQL veld staan EN alle queries die actief zijn in de SQL conversions tab
Load test data → Laad het geselecteerde bestand aangegeven in de File configuration tab in, in het bovenste grid. Dit grid is het de bron tabel
Add query to conversions → Verplaatst de query van het SQL veld naar het SQL conversions tab. Let wel op dat deze een standaard sortering krijgt van 10 en mogelijk nog op volgorde moet worden gezet.
Show mapping output → Laat zien hoe het generieke .csv bestand eruit komt te zien na uitvoeren van alle queries en mappingen. Dit werkt pas als er minimaal 1 query en 1 mapping is ingesteld.

In het veld SQL kan een query worden geschreven om de data aan te formatteren naar de gewenste output. Zoals al eerder beschreven moet de aanpassing worden gedaan in het bovenste grid, de bron tabel.
De queries maken gebruik van SQLlite en dienen ook in dat format geschreven te worden.
Een voorbeeld query zou kunnen zijn: update bron set OrderOmschrijving = 'Test' || [LaadPlaats] waarbij het veld OrderOmschrijving een veld uit het tab Field mappings moet zijn en tijdens deze bewerking wordt dit veld aangepast naar de waarde "Test" en daaraan wordt met de SQLite || operator het veld LaadPlaats toegevoegd, waarbij ook  LaadPlaats als Field defenitie bekend moet zijn.
Daarna kan op de Field mapping tab worden bepaald in welk veld van de csv de data moet komen. In dit geval zou er een mapping moeten worden gemaakt dat het veld OrderOmschrijving moet worden gekoppeld aan OrderOmschrijving.

Als de import is afgerond dan kunnen de bestanden worden geplaats in de aangegeven map in de import folder van de File configuration tab.
Als alles goed wordt verwerkt dan wordt het bestand omgezet naar het generieke .csv bestand en geplaats in de export folder map. Het origineel aangeleverde bestand komt in de import folder map + //Processed

Als er nog geen klant is geconfigureerd dan moet dat nog gedaan worden, hoe dit moet staat op de RGB.ImportService pagina.

Voorbeeldqueries

Simpele queries

update bron set stop = 1
update bron set omschrijving = 'Import Test ' || [Laadplaats] || ' ' || [LosPlaats] // De || operator betekend dat de waarde links en rechts aan elkaar moeten worden geplakt, omdat er een spatie tussen laad en losplaats moet komen staat || ' ' || er tussen
update bron set TransportDatum = REPLACE(TransportDatum, '-', '/') // Het - teken wordt vervangen door een / teken zodat deze voldoet aan het gewenste format

Geavanceerde queries

update bron set LaadLosCode = CASE WHEN LENGTH(LaadTijd) > 0 THEN 'LA' ELSE 'LO' END // De CASE WHEN THEN END constructie is te lezen als een ALS DAN functie waarbij hier wordt gecontroleerd op LENGHT(LaadTijd) >0. De meest makkelijke manier om te controleren of er een waarde in veld LaadTijd staat is door de lengte op te vragen, als deze groter is dan 0 dan moet hier geladen worden en kan de LaadLosCode op 'LA' worden gezet
update bron set TransportDatum = SUBSTR(TransportDatum, 4,3) || substr(TransportDatum,1,3) || Substr(TransportDatum,7,5) // Om de datum om te zetten naar een dd/MM/jjjj om te zetten kan de huidige datum worden opgeknipt met SubStr waarbij de eerste parameter het veld is waarvan een stukje moet worden gepakt, daarna de startIndex van de string, en daarna de lengte van het stukje dat geknipt moet worden. Een voorbeeld Transport datum van '01/31/2021' wordt met de eerste substring 31/ en daarna 01/ en daarna 2021 omgezet tot 31/01/2021

update bron set StartTijd = (SELECT StartTijd FROM bron as b2 WHERE b2.Route = bron.Route) // Vaak is het het makkelijkst om op de eerste regel de Starttijd van de Order goed te zetten, omdat dit meestal ook de starttijd van de eerste stop is. Daarnaast is het belangrijk om een veld te hebben waarmee kan worden bepaald welke regels er bij 1 order horen, dat is hier het Route veld. In deze query wordt de StartTijd gezet met de eerst gevonden StartTijd in de bron tabel waarbij de waarde van Route gelijk is aan de waarde van Route in de huidge regel.

insert into bron (Omschrijving, Stop) SELECT OmschrijvingDcStop, '1' FROM bron as b2 where Stop = 2 // Soms moet er een stop bij een DC worden toegevoegd aan het begin en/of einde van een rit. In dit voorbeeld wordt er een nieuwe regel/stop toegevoegd voor alle orders obv de bestaande stop met waarde Stop = '2'. Om deze query te laten werken moet er dus al een regel bestaan waarbij Stop waarde 2 heeft. Vervolgens wordt er in de bron tabel veld Omschrijving de waarde gekopieerd uit het veld OmschrijvingDcStop en het veld Stop wordt gevuld met de waarde '1'. Realistischer zou zijn om in de insert query uit te breiden met alle velden die ook nodig zijn in de mapping tab zodat de regel alle waarde bevat.

Handige specifieke queries

update bron set omschrijving = replace(replace(replace(replace(replace(omschrijving, '\n', ''), '\r', '') , X'0A', ''), x'0D0A', x'0A'), x'0D', x'0A'); // Soms bevat een bestand (vaak Excel) onzichtbare enters of tekens in een omschrijving veld, deze zorgen voor problemen bij het aanmaken van de .csv. Deze waarden kunnen met deze query worden verwijderd.