Learn, how to configure the IAI Bridge Universal Mode

Integration with use of the Universal Mode allows to synchronize data between the IdoSell.com and any FA program. To update stock quantities and prices, all you need to do is to change, add and delete records from iai_brdge_exchange_db database tables. IAI Bridge handles detection of changes, event and the communication process. It is very comfortable solution, even for beginner programmers, who - with use of any programming language they want, e.g. PHP or .NET - want to create simple applications, that control stock quantities, prices and download information on order.

The iai_brdge_exchange_db database can be of Microsoft SQL or MySQL type. Database selection can be made during configuration of synchronized application. External database, that is used to exchange data between IAI Bridge and FA program, can be located on local or remote server.

After selecting database and configuring DB server - on which external database is installed - connection, the system checks whether such base already exists on the server. If no database is detected on indicated server, the system will install new one (clean) with iai_bridge_exchange_db name. Otherwise (if the system detects existing database), two options are to be chosen: leave or remove data from the existing database.

After successful configuration of remaining program parameters and ending configuration creator, IAI Bridge is ready to work in Universal Mode.

Learn more about Universal Mode database structure.

Operations performed on additional database

Adding and updating data on products

To begin the integration process between the FA program and the IdoSell Shop administration panel, by means of IAI Bridge in Universal Mode, fill in the products data table with data of your offer, available in the FA program.

Enter data on all products from FA program into the table. If prices in IdoSell Shop administration panel should be automatically updated on basis of the FA program, remember to fill in adequate fields with prices and add the VAT rate. If not, fields from vat to distinguished_price do not have to be filled in with data from the FA application.

Download databases from FA application (by SQL query) or export corresponding data to file:

  • product ID in FA program;
  • additional ID, like catalog or trade index, product code, manufacturer code. If there is no additional unique ID, only product ID will be used;
  • product name;
  • product quantity (stock quantity).

If you plan to update prices, also include their data:

  • VAT rate,
  • gross prices.

Products data should be placed in products table of additional IAI Bridge database - iai_bridge_exchange_db.

SQL instruction below shows adding new product to database:

INSERT INTO products (id, ident, name, quantity, vat, retail_price)
VALUES(1, 'BRE000001', 'FIAT KEYRING.', 200, 22, 99.99);

Performing the above instruction will add new product to database. This product will be visible in IAI Bridge on the list of unsynchronized products. To be able to synchronize it, enter additional product ID from the FA program into "External system code" field. Place it into the ident field from products table in additional database. In the example above, the FIAT KEYRING should have the FIAT KEYRING value entered into the external system code field.
Products, that will be synchronized, are moved to a list of synchronized products and their prices and stock quantities will be updated in IdoSell Shop administration panel every set time (15-60 minutes) during the automatic synchronization process.
In addition to adding products from the FA program, remember to update prices and stock quantities periodically.
Frequency of updates in the products tables should be shorter than the synchronization set in IAI Bridge. For example, frequency of updates in IAI Bridge was set to 30 minutes. Data in table should be updated e.g. every 28 minutes, so the data updated by IAI Bridge could be up-to-date.

Downloading data on orders
IAI Bridge - during the automatic synchronization - downloads new orders from the synchronized administration IdoSell Shop administration panel and, if all products meet the requirements (i.e. products are being synchronized, stock quantity in products table is sufficient and products are assigned to the synchronized stock), order is added to IAI Bridge as correct order and all of its data are added to adequate tables: orders_products, orders, clients in additional iai_bridge_exchange_db database. Moreover, IAI Bridge updates stock quantities in products table, subtracting products from order. In administration IdoSell Shop administration panel orders downloaded by IAI Bridge and marked as correct, order status will be changed to "handled in FA application", which means, that order completion has been transferred from the external FA program.
If there is at least one product present in panel order, that does not meet the above-mentioned criteria (e.g. is not synchronized by IAI Bridge), orders are marked as incorrect in IAI Bridge and moved do corresponding list.
Under such circumstances, order status in store is not modified, order and product data are not added to additional database nor the stock quantity in product table is decreased. With every synchronization process incorrect orders are checked and - if anything changed - are added to iai_bridge_exchange_db additional database.
Data of ordered products and orders can be downloaded from the iai_bridge_exchange_db additional database in a few ways.
The easiest method is downloading data only from orders_products table, in which ordered products data are stored. If orders should be automatically sent to FA program, also additional data from orders and clients tables should be downloaded.
There are also multiple methods of "flagging" downloaded orders. You can save the ID of last order downloaded database and download orders with ID greater than the one saved. You can also download all order data and - after downloading it - delete from orders_products and orders tables, using the DELETE FROM table_name or TRUNCATE TABLE table_name instructions.

Sample SQL instruction, that shows, how to download data on ordered products with use of the last order ID:

SELECT * FROM orders_products WHERE order_id > 0 ORDER BY order_id

As a result of performing the above query data on ordered products from orders with ID greater than 0 will be returned.
Sample data returned as a result of this query can look like this:

example