Creating a view to map products (PlugSales)

PlugSales products must have the following information:

• ID
  • Numeric field of type inteiro. This field will be returned in order items.
• NAME
  • texto field with the product name.
• DESCRIPTION(Optional Field)
  • texto field with the product description.
• CATEGORY
  • texto field with the name of the product category.
• STOCK(Optional Field)
  • Number field of type inteiro with the current stock of the product.
• PRICE
  • Numeric field like real(float/decimal) with the price of the product.
  • If you use the price table: access this link:How does the price table work?
• MAXIMUM_DISCOUNT
  • Numeric field of type real(float/decimal) with the maximum discount that this product can have.
• IMAGE(Optional Field)
  • String field with a url or base64 of a valid image.
• ADD CUSTOM FIELD
  • In this field you can determine which type and name should be received according to your database.

To adapt to the PlugSales format, it is not necessary for you to create a new product table with these columns and start registering from scratch.

Just create a view in your database, which will return records with the columns needed by PlugSales.

What is a view?

In practice, an view is a virtual table in which its records are the results of a SELECT defined at the time of its creation.

For example, to create a view that simulates a table that displays only VIP customers:

CREATE VIEW ClientesVIP AS SELECT FirstName1, LastName1 FROM Customers WHERE VIP = ‘S’;

And the query from VIP customers about this view would simply be:

SELECT * FROMClientsVIP;

Creating a Product view for PlugSales

Example: MySQL

To create the product view that PlugSales needs, imagine that you have the following table structure in your database:

PlugSales needs information that is, in this scenario, in different tables.

To solve this and give PlugSales the information it needs about the products, you can write a SQL query with several inner joins, which will be executed every time PlugBot needs to read information about your products.

Or you can create an view that will bring the data in the correct format all at once to PlugSales.

To create view that serves PlugSales in this table structure, the SQL would be as follows:

CREATE VIEW VwProdutosPlugSales AS SELECT p.id, p.nome, p.descricao, p.preco_unitario, c.nome as category, e.qtde_estoque, d.maximo_desconto FROM TbProdutos p INNER JOIN TbCategorias c ON p.id_categoria = c.id INNER JOIN TbDescontosPorProduto d ON p.id = d.id_produto INNER JOIN TbEstoqueProduto e ON p.id = e.id_produto;

Once the VwProdutosPlugSales view is created, you can select that view in the dashboard and PlugBot can simply perform a simple SELECT * FROM VwProdutosPlugSales to obtain the necessary data.

And when loading the data from this view, whether directly from the table, or using an SQL query, the data will already be formatted correctly in the columns that PlugSales expects!