2. Creating a view to map financial data (PlugSales)

Financial data in PlugSales must have the following information:

• ID
  • Numeric field of type inteiro that identifies the customer’s payable account, this field is not returned in the application - this information is for consultation purposes only.
• CLIENT
  • Number field of type inteiro, must be the same as that entered in Customer mapping as this field will be used to create a relationship between these records.
• VALUE
  • Numeric field of type real(float/decimal) with the amount of the bill to be paid.
• DUE_DATE
  • texto field with the account due date in the format:YYYY-MM-DD.
• PAYMENT_FORM
  • Numeric field of type inteiro with the ID of the payment method, must be the same as that entered in Payment Method mapping as this field will be used to create a relationship between these records.
• SITUATION
  • texto field that will be displayed as document status. Examples: Paid, Open.

To adapt to the PlugSales format, it is not necessary for you to create a new payment methods 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 Financial Data view for PlugSales

Example: MySQL

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

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

To resolve this and give PlugSales the information it needs about the financial data, you can write a SQL query using aliases and inner joins, which will need to be executed every time PlugBot needs to read your data.

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 OR REPLACE VIEW VwDadosFinanceirosPlugSales AS SELECT documents.id, documents.client_id customer, orders.total_order value, documents.due_date, payment forms.name payment_form, situation.description situation FROM TbDocuments documents INNER JOIN tbsituation documents situation ON documents.situation_id = situation.id INNER JOIN tbpedidos orders ON documents.order_id = orders.id INNER JOIN vwformaspagamentoplugsales formaspagamento ON orders.id_forma_pagamento = formaspagamento.id;

Once the VwDadosFinanceirosPlugSales view is created, you can select this view in the dashboard and PlugBot can simply perform a simple SELECT * FROM VwDadosFinanceirosPlugSales 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!