2. Creating a view to map users (PlugSales)

PlugSales users must have the following information:

• ID
  • Numeric field of type inteiro. This field will be returned in the request as USER_ID.
• NAME
  • Field of texto. This field will be listed on the application login screen.
• PASSWORD
  • texto field containing the user’s password. This password can be a natural value, that is, without encryption, but you can send it encrypted in MD5 or SHA-1, just select which type of encryption you are using.
• MAXIMUM_DISCOUNT
  • Number field of type real(float/decimal). This field is used to limit the maximum discount on the order.

To adapt to the PlugSales format, it is not necessary for you to create a new user table with these columns and start registering your users 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, a 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 Users view for PlugSales

Example: MySQL

To create the customer 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 users, you can write a SQL query with several inner joins, which will be executed every time PlugBot needs to read information about your users.

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

To create the view of PlugSales users, with the tables above, the SQL would be as follows:

CREATE VIEW vwUsuariosPlugSales AS SELECT u.id AS id, u.nome AS name, u.password AS password, d.desconto_maximo AS maximo_desconto FROM tbUsuarios AS u INNER JOIN tbTabelaDescontos AS d ON u.id = d.id_usuario;

Once the vwUsuariosPlugSales view is created, you can select that view in the panel and PlugBot can simply perform a simple select * from vwUsuariosPlugSales to obtain the required data.

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