Connector

A CSV connector enables to export data using CSV files. It enables to configure data which need to be exported while transformation rules are configurable with a "mapping". It is also possible to develop a specific connector to take into account specific job rules and to send data in a specific format.

beCPG supplies a standard and user friendly connector which extracts entities and its data lists (and export it to csv files) or documents (images, technical files) in their native format.

Installation


Install Java SDK 8 on your machine. Download and unzip the file: becpg-connector-3.2.0-distribution.zip

Configuration


It is necessary to edit the file below to configure the connector: becpg-connector.properties

  • First, specify server information:

    remote.scheme=http remote.host=localhost remote.port=8080 remote.login=admin remote.password=becpg

  • Then, specify where the CSV files have to be extracted:

    connector.dest.path=

To do so, make sure that the path format in Windows operating systems respects the following criteria:

 c:\\Temp\\becpg-connector or c:/Temp/becpg-connector
  • It is possible to send an email notification at the end of the data extraction:

    notify.enabled=false connector.notify.from=support@becpg.fr connector.notify.to=support@becpg.fr

    mail.smtp.host=localhost mail.smtp.port=25

  • It is also possible to upload extracted files to your FTP server. For that purpose, you just need to add your FTP server information:

    tp.enabled=false ftp.host= ftp.port=21 ftp.login= ftp.password= ftp.destFolder= ftp.deleteOnSuccess=false

Standard configuration

Configuration of fields to extract

  • Specify the search query: the list of entities to extract is defined in the search query where %s is replaced by the last import date which is stored in lastImport file:

    connector.query.template=(@cm\:created:[%s TO MAX] OR @cm\:modified:[%s TO MAX]) AND (ASPECT:\"bcpg:productAspect\" OR TYPE:\"bcpg:client\" OR TYPE:\"bcpg:supplier\")

In this example, all the entities of the product, including client and supplier type which were modified or created after the last import operation (data extraction), are extracted.

Configuration of extracted fields

For each retrieved entity type, it’s mandatory to specify extracted fields in the CSV file.

Example of finished Product type:

mapping.csv.bcpg_finishedProduct=bcpg:code,bcpg:erpCode,cm:name,cm:title,bcpg:eanCode,bcpg:code,bcpg:legalName,cm:description,bcpg:entityTplRef\
                    ,bcpg:productHierarchy1,bcpg:productHierarchy2,bcpg:bestBeforeDate,bcpg:certificateDate,bcpg:contractDate\
                    ,bcpg:foodContact,bcpg:netWeightUnit,bcpg:numberOfServings,bcpg:packagingDescription,bcpg:periodAfterOpening\
                    ,bcpg:precautionOfUseRef,bcpg:preparationTips,bcpg:priceCurrency,bcpg:productComments,bcpg:productDensity\
                    ,bcpg:productMicrobioCriteriaRef,bcpg:productQty,bcpg:productSpecifications,bcpg:productState,bcpg:productUnit\
                    ,bcpg:projectedQty,bcpg:servingSize,bcpg:startEffectivity,bcpg:storageConditionsRef\
                    ,bcpg:supplierPlants,bcpg:suppliers,bcpg:suppliers|bcpg:code,bcpg:suppliers|bcpg:erpCode\
                    ,bcpg:clients,bcpg:clients|bcpg:code,bcpg:clients|bcpg:erpCode,bcpg:unitPrice,bcpg:useByDate

Finally, you need to configure the exported data lists.

Example for nutrients and packagings lists:

mapping.csv.nutList=entity|bcpg:code,entity|bcpg:erpCode,bcpg:nutListNut,bcpg:nutListValue,bcpg:nutListFormulatedValue,bcpg:nutListUnit
mapping.csv.packagingList=entity|bcpg:code,entity|bcpg:erpCode,bcpg:packagingListProduct,bcpg:packagingListProduct|bcpg:erpCode,bcpg:packagingListQty,bcpg:packagingListUnit,bcpg:packagingListLossPerc,bcpg:packagingListPkgLevel,bcpg:packagingListIsMaster

Configuration of multi languages fields

The standard connector enables to export multi languages data fields "d:mltext". To export the values of the field, you just need to add the code of the language at the end of the name.

Example:

cm:description_en, to export a descrition in English.

bcpg:legalName_ja_JP, to export legal name value in Japanese.

Configuration of export for geoOrigin association of ISO codes.

You can export the ISO code of the geographical origin association in using the format: assoc_name|bcpg:geoOriginISOCode

Example:

To export ISO codes for the association bcpg:ingListGeoOrigin in the list of ingredients.

mapping.csv.ingList=entity|bcpg:code,entity|bcpg:erpCode,bcpg:ingListQtyPerc,bcpg:ingListGeoOrigin|bcpg:geoOriginISOCode

Configuration of the document connector

The document connector is a plugin of the standard connector. It can be activated by typing the following line in the becpg-connector.properties file.

connector.plugin.class=fr.becpg.connector.plugin.StandardDocumentVisitor

This line is supplied by default in the configuration file. Simply uncomment the line by deleting # symbol at the beginning of the sentence.

Note: you can choose which files will be generated or extracted for each entity type (finished product, semi-finished product, etc.).

Each entity has a mapping as below:

mapping.document.bcpg_<entity type> = <fields to be extracted>

Default mappings can be used to extract technical files and images for raw-materials, semi-finished products and finished products:

mapping.document.bcpg_finishedProduct=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf
mapping.document.bcpg_rawMaterial=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf
mapping.document.bcpg_semiFinishedProduct=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf

We can see that each entity to extract is configured the same way:

  • the path on the instance of the file to extract
  • a vertical line |
  • followed by the name (and path) of the extracted file. This enables to choose how will be named your file and if it must be saved in another file.

Then, it is important to note that each entity to extract is separated from the next by semi colons ; Two variables are encrypted and usable in the name of the source file (on beCPG's system) and the name of the target(on your system). These two variables are the ERP code of the product and its name. Therefore, it is possible to extract technical sheets in a folder whose name would be "ERP code - entity name":

/Documents/${cm:name} - Customer Technical Sheet.pdf|${bcpg:erpCode} - ${cm:name}/FT_client${bcpg:erpCode}.pdf

Configuration of workflow connectors:

The workflow connector enables to export workflows and their tasks in CSV format. You can activate it in typing the following line in becpg-connector.properties

connector.plugin.class=fr.becpg.connector.plugin.WorkflowCSVVisitor

The following mappings are available to configure fields to extract:

mapping.csv.workflow_instances=id,name,title,description\ ,package|cm:name,formula|props["package\|path"]!=null ?props["package\|path"].split("/")[props["package\|path"].split("/").length-1]:""\ ,isActive,startDate,priority,message,endDate,dueDate,initiator.userName,initiator.firstName,initiator.lastName

mapping.csv.workflow_tasks=workflowInstance|id,workflowInstance|name,id,state,name,title,description,bpm:completionDate,bpm:workflowDueDate

The property connector.query.template enables the filtration of workflows:

    connector.query.template=startedAfter=%sZ

Possible values are:

state,initiator,priority
 ,dueBefore,dueAfter,
 ,definitionId,definitionName,
 ,startedBefore,startedAfter
 ,completedBefore,completedAfter,exclude

Multilevel Connector configuration

The multilevel connector is a plugin of the standard connector, which allows you to extract entities in CSV format and also their composition data lists , packaging data lists and other data lists.

Its name « multilevel connector » indicates that this tool is capable of showing level of information for each composition item.

In other words, this plugin extracts the composition of a semi-finished product in a finished product or the composition of a semi-finished product in another semi-finished product and so on It also enables you to see the level of each component.

Example:

Composition of a finished product:

Calculation of the quantity of semi-finished in a product:

The level of semi-finished C is equal to 3. So, to calculate its quantity at product level, we must first calculate the quantity of semi-finished B at product level and in using a rule of three:

Semi-finished B QTY = (Semi-finished B Weight / Semi-finished A Weight) * Semi-finished A Weight at the product level 
  • Weight of semi-finished A = sum of its components which can be found in formulated characteristics

  • Semi finished B QTY = (0.31 / 0.66) * 5 = 2,348 Kg.

That result is the quantity you will use to calculate the quantity of semi-finished C, and so on as you go down in the composition level.

In the Example:

  • Quantity of semi-finished B at product level => after calculation = 2.348 kg.

  • Semi-finished B weight = 0.17 kg;

  • Semi-finished C quantity used in the composition of semi-finished B = 0.1 kg.

  • The quantity of semi-finished C used at product level = (0.1 / 0.17) * 2.348 = 1.381 kg.

The reasoning to calculate the quantity of raw material 3 at product level follows the same logic in using this time 1.381 kg.

The multilevel plugin also calculates packaging values :

  • Number of products per PCB packaging;
  • Number of boxes per pallet (primary, secondary, tertiary) ;
  • Quantity of packaging used for each product.

This tool also extracts dynamic characteristics (dynalic characterisitics). Documentation on formulated characteristics

These characteristics are extracted and can be directly displayed or used in other more complex calculation as explained in the SPEL formulas section.

Note: property names are the same as written in the application, you just need to replace the white space with a dash ( _ ) and add the prefix *dyn_ at the beginning of a property name.

For example :

property name : « qty multilevel »\ extract name: « dyn_qty_multilevel »

Multilevel Connector Configuration

As in the standard connector, to activate the multilevel connector, you need to paste the following line.

connector.plugin.class=fr.becpg.connector.plugin.MultiLevelCSVVisitor

This line is available in becpg-connector.properties file (you just have to delete the # symbol).

Once the plugin is loaded, you can modify which files you would like to extract for each entity type (Finished Product, SemiFinished Product, Raw-material, etc.).

Each entity will have a defined mapping as followed:

mapping.document.bcpg_<Entity type> = <Field to extract>

Configuration of entities to extract

Sachant que le plugin est dédié aux 2 type d’entités (produit fini et produit semi-fini), les fichiers csv sont indisponibles pour les autres types d’entités (clients, fournisseurs, ext.).

Ainsi, la requête de recherche est assez similaire à la requête du connecteur standard. En revanche, dans ce cas, il n’y a pas besoin d’importer les entités de type client et fournisseur (supplier).

As the plugin is only dedicated to two types of entities (finished and semi-finished products), CSV files won't work with the other types of entities (clients, suppliers and so on). This is also valid for the search request. However, in this situation, we won’t need to extract other entities such as suppliers and clients.

connector.query.template=(@cm\\:created:[%s TO MAX] OR @cm\\:modified:[%s TO MAX]) AND ( TYPE:\"bcpg:finishedProduct\" OR TYPE:\"bcpg:semiFinishedProduct\")

SPEL Formulas

Spring Expression Language (SPEL in short) is an expression language that supports querying and manipulating Java objects. With SPEL you can customize extracted columns which will be displayed. The formulas and expressions used for calculations or tests are present in the CSV file mapping, whether in the entity mapping or in the data list mapping.

Example

To calculate the quantity of a pallet item, we use the following formula:

formula|props["nbProductsPkgLevel3"]!=null ? props["nbProductsPkgLevel3"] * props["qty"] : props["qty"]

This means : if the product quantity in the pallet is not null, multiply this value by the quantity of the item at product level. Otherwise, just display the value of the item composition.

SPEL Formula Syntax

To use SPEL formulas you have to consider 3 parts :

Part 1: the prefix formula (static word) used to state that that column is a SPEL formula ;\ Part 2: Vertical bar or pipe ( | ) ;\ Part 3: The actual SPEL formula.

The simplest SPEL consists in displaying standard, dynamic or calculated property. For example, to display ERP code property :

formula|props["bcpg:erpCode"]

Props is a list which contains all the extracted properties. Consequently, to get a property value from this list you need to write the property name between 2 brackets :

props [“EXTRACTED_PROPERTY_NAME”]

As explained in the previous section, you can use all properties within a SPEL formula. To discover more in-depth information about SPEL capabilities, let’s show an example:

formula|props["qty"] != null ? props["qty"] / prop["bcpg:compoListQty"] : props["qty"]

The meaning of the formula is the following: In this formula we use a condition ( ? ) to test if the qty property is not null, a simple mathematic operation ( / ) will calculate the percentage qty in compoListQty. Otherwise, it will just display the qty.

It should be remembered that some functions like sum, avg , … are available.

For more information about SPEL language: http://docs.spring.io/spring/docs/3.2.x/spring-framework-reference/html/expressions.html

Use

On Linux, run following command: ./becpg-connector.sh\ On Windows, run the becpg-connector.bat file by double click on it.\ CSV files are created under the connector.dest.path path folder

Example of generated files:\

csv/:
bcpg_client.csv           bcpg_semiFinishedProduct.csv  compoList.csv  organoList.csv     physicoChemList.csv
bcpg_finishedProduct.csv  bcpg_supplier.csv             nutList.csv    packagingList.csv

results matching ""

    No results matching ""