FTP Connector - ItemUpdate

Overview

This function synchronizes item data and item filter data from CSV files located on an FTP server to the App4Sales platform. It updates item details, sales prices (including support for multiple price lists), item classes, and item class values. The synchronization runs based on the connector's update schedule.

Data Source Configuration

The connector downloads two CSV files from the configured FTP server:

items.csv: Contains the main item data.
itemFilters.csv (or Assortiment.csv as a fallback): Contains item classification data, used to create Item Classes and Item Class Values.

Both files are expected to be semicolon (;) delimited and use UTF-8 encoding. Trailing semicolons in lines are automatically removed. If Settings.UseCsvItemSyncLogic is enabled, the files are processed through a streamlined internal CSV reader. If disabled, an older processing logic is used. Authentication details (username, password, host, port, and security protocol like SFTP, TLS, or SSL) are configured in the connector settings.

Data Mapping Table (Item)

App4Sales Field	Source Field (CSV Column in items.csv)	Logic/Notes
`ItemCode`	`itemcode`	Direct mapping. Items with empty `itemcode` are skipped.
`SalesPrice`	`salesprice`	Converted to decimal. If not present, defaults to 0.
`Currency`	`currency`	If empty, defaults to "EUR". Otherwise, direct mapping.
`PurchasePackageSize`	`purchasepackagesize`	Converted to decimal. If empty or 0, defaults to 1.
`VatPercentage`	`vatpercentage`	Converted to decimal.
`EanCode`	`eancode`	Direct mapping. Trailing/leading whitespace is trimmed.
`LastAvailableStock`	`lastavailablestock`	Converted to decimal.
`VatIncluded`	`vatincluded`	Converted to char. '1' is mapped to 'I' (Included), '0' to 'E' (Excluded). Other invalid values default to 'E'. Value is converted to uppercase.
`Description`	`description`	Direct mapping.
`Description2`	`description2`	Direct mapping.
`Description3`	`description3`	Direct mapping.
`Description4`	`description4`	Direct mapping.
`Description5`	`description5`	Direct mapping. (Only used if `Settings.UseCsvItemSyncLogic` is enabled)
`Unit`	`unit`	Direct mapping.
`SearchDescription`	`searchdescription`	Direct mapping.
`FreeSortField`	`freesortfield`	Converted to integer. (Only used if `Settings.UseCsvItemSyncLogic` is disabled)
`FreeItemFields`	`freefields` or `freeitemfields`	If `freefields` is not found, `freeitemfields` is used. The content is transformed to XML. If `Settings.UseDeprecatedFreeFieldsFormat` is enabled, commas are replaced with dots and a fixed caption "Vrij veld" is used. Otherwise, it's parsed from an internal format.
`MinimumOrderQuantity`	`minimumorderquantity`	Converted to decimal. If 0, it's set to null.
`NextDelivery`	`nextdelivery`	Parsed as DateTime in "yyyy-MM-dd" format. Can be null.
`CreatedDate`	`createddate`	Parsed as DateTime. If parsing fails, defaults to current date/time.
`StockIndicator`	Derived	Calculated by the system based on `LastAvailableStock` unless `Settings.UseStockIndicatorFromConnector` is true.
`ItemClasses` (`In stock`)	Derived	An `In stock` ItemClass is automatically added with value "Yes" if `LastAvailableStock` > 0, otherwise "No". ID -666.

Data Mapping Table (Item Prices)

App4Sales Field	Source Field (CSV Column in items.csv)	Logic/Notes
`PriceList.Id`	Derived from `salespriceX` column name	Dynamically created based on the numeric suffix of `salespriceX` columns (e.g., `salesprice2` maps to PriceList ID 2).
`PriceList.Code`	Derived from `salespriceX` column name	Same as `PriceList.Id`.
`PriceList.Description`	Derived from `salespriceX` column name	"PriceList X" where X is the numeric suffix from `salespriceX`.
`PriceListPrice.ItemCode`	`itemcode`	Links the price to the item.
`PriceListPrice.Price`	`salespriceX`	Value from `salespriceX` column, converted to decimal. Prices must be non-zero and non-empty.
`PriceListPrice.PriceListId`	Derived from `salespriceX` column name	Links the price to the dynamically created price list.

Note: When Settings.UseCsvItemSyncLogic is disabled, default price lists (Default, Pricelist 2, Pricelist 3, Pricelist 4) are hardcoded with IDs 1, 2, 3, 4 respectively. salesprice2, salesprice3, and salesprice4 columns map directly to these hardcoded price lists.

Data Mapping Table (Item Filters - ItemClass and ItemClassValue)

App4Sales Field	Source Field (CSV Column in itemFilters.csv or Assortiment.csv)	Logic/Notes
`ItemClass.Text`	`ItemClass`, `FilterName`, `ItemFilter`, or `Niveau1` (in order of precedence)	The first non-empty column value is used as the Item Class name. Only item filters linked to existing item codes are processed.
`ItemClass.Id`	Derived	If the Item Class is new, the ID is generated as a hash of its name. Otherwise, an existing ID is used.
`ItemClassValue.ItemCode`	`ItemCode`	Links the item class value to the item.
`ItemClassValue.Value`	`Value`, `FilterValue`, or `Niveau2` (in order of precedence)	The first non-empty column value is used as the Item Class Value. If all are empty, it defaults to "Ja".
`ItemClassValue.ItemClass`	Derived from the Item Class Name	Links the item class value to its parent item class.
`ItemClassValue.Sequence`	`Sequence`	Direct mapping.

Special Logic & Filters

Conditional Sync Logic: The connector uses Settings.UseCsvItemSyncLogic to switch between an older and newer item synchronization method. The newer method (PerformNewCsvItemSyncLogic) processes files via streams and delegates to base CsvConnector methods (DoItemUpdate, DoItemFiltersUpdate). The older method (PerformOldCsvItemSyncLogic) processes the CSV files directly within the FTP connector and performs direct data updates.
Data Cleanup: If Settings.CleanData is enabled and the new item sync logic is used, the following tables are truncated (all existing data deleted) before new item data is imported: itemclassvalues, itemclasses, items, itemprices, pricelists. This is a destructive operation.
Error Handling: Exceptions during file download or processing are logged, and the synchronization process for the affected file may be aborted.
Script Execution: Pre- and post-update scripts (Jint scripts) can be executed for items, price lists, and item prices using BindingFactory<IScriptingService>.Instance.ExecuteScript.
SQL Overflow Prevention: Before updating items in the database, the system checks decimal properties (SalesPrice, PurchasePackageSize, VatPercentage, LastAvailableStock, MinimumOrderQuantity) to prevent SQL arithmetic overflows based on their defined precision and scale. Items causing an overflow are logged and removed from the update batch.

Domain Specifics (Expanded)

Price Logic

The system supports dynamic price lists. Any column in items.csv named salespriceX (where X is a number) will automatically create a new price list with ID X and description "PriceList X". The value in this column will be associated with the corresponding item and price list. If Settings.UseCsvItemSyncLogic is disabled, price lists are pre-defined as Default (1), Pricelist 2 (2), Pricelist 3 (3), and Pricelist 4 (4), with corresponding salespriceX columns mapping to these fixed IDs.

Item prices that are identical to the default sales price of an item are typically filtered out to reduce data redundancy, unless they belong to an "Action Price List" linked to a customer.

Image Handling

The ItemUpdate method itself does not directly handle image files from the FTP. However, it passes FTP connection details to the session settings, implying a separate process (likely a picture sync) uses these settings to download item pictures. The UpdateItemPictures method (in CsvConnector) processes item pictures from a stream (likely a ZIP archive) containing .jpeg, .jpg, and .png files. Images are resized, hashed, and stored in the database. Filenames are used to derive ItemCode and Sequence for linking images to items.

Stock & Availability

The LastAvailableStock column from items.csv is directly mapped. An additional item class "In stock" is automatically generated based on whether LastAvailableStock is greater than 0.

Matrix & Attributes

Item Classes and Item Class Values are primarily created from the itemFilters.csv (or Assortiment.csv) file. The system dynamically creates or updates Item Classes based on the content of these files, linking them to specific item codes. The logic for deriving the class name and value is flexible, using a precedence order of several CSV columns (e.g., ItemClass, FilterName, Value, FilterValue).

Free item fields are processed from the FreeItemFields or freefields column in items.csv and converted into an XML format, with behavior influenced by the Settings.UseDeprecatedFreeFieldsFormat setting.

Related Settings & Prerequisites

UseSFTP: Determines whether SFTP (secure) or standard FTP is used for file transfers.
UseTLS: Enables TLS encryption for FTP.
UseSSL: Enables SSL encryption for FTP.
UseCsvItemSyncLogic: Boolean flag to switch between the old and new item synchronization logic.
CleanData: If true, item- related tables (itemclassvalues, itemclasses, items, itemprices, pricelists) are truncated before import (only for new sync logic).
UseDeprecatedFreeFieldsFormat: Affects how the FreeItemFields column is parsed and converted to XML.
HideFreeFieldCaption: If true, the caption for free fields will be hidden in the generated XML.
ReplaceLineFeedsWith: (Mentioned in SendOrderToBackoffice, but relevant for any multiline text fields). Specifies how line feeds should be replaced in multiline text fields (e.g., notes).
UseStockIndicatorFromConnector: If true, the stock indicator for an item is taken directly from the connector's data rather than being calculated by App4Sales.
FTP Server Access: Configured FTP host, username, and password in the connector administration.
File Paths: The connector expects items.csv and itemFilters.csv (or Assortiment.csv) to be present in the root of the configured FTP folder.

Known Limitations

The ItemUpdate method itself does not directly handle image file processing; it relies on a separate picture synchronization process.
If Settings.UseCsvItemSyncLogic is true, the itemFilters.csv file is read twice, once via the UpdateSource mechanism and once directly by UpdateItemFilters. While functionally correct, this could be inefficient.
Items with decimal values that would cause SQL arithmetic overflow for specific database column definitions will be skipped during synchronization.
Item Class descriptions are truncated to 50 characters before being used to find or create item classes.

FTP - Item sync