Data Processing in Control Forms: Using Macros to query and insert into Database

Data processing can be done at the Database level (with Stored Procedures, Triggers, Functions and so on) or Application level. Depending on how big the volume of data to handle are and the interaction that the user must have, one or the other may stand as the best option in some cases.

The data processing that we will show as an example here is the one done at the Application level, to execute this example we will take certain parameters from the Database.

On the other hand the user will enter some parameters and then complete the calculation and insert the result into the Database.

 

We will use the following Scriptcase Macros to make our work easier:

In terms of Database structure, we have two tables which are shown below:

The table “Parameter” in a PostgreSQL database.

image01

The table “Payment” in a MySQL database.

image02

The process that we will do will calculate the percentage of interest that the user will have to pay based on the amount desired and the number of installments (months) that he needs to pay back.

Within the table “Parameter” we have the percentage of interest that the user will pay based on the number of months that he chooses in our application.

image03

Once inside our project in the Scriptcase, we create the connections that we will need to consult the table in PostgreSQL (conn_Parameter) and then insert into the MySQL table (conn Sales).

image04

We create a Control application in order to add the fields we want to show the user and from which we will then carry out the process that will calculate how much the user will have to pay and how much he will pay each month including interest.

image05

The next step is to create the necessary fields for the user to enter their name, desired amount and number of installments. Once we have these data the application will query the table Parameter and return the percentage of interest to apply for the amount of quotas selected and the total to be paid.

According to the above the application will have 5 (five) fields, for this we click Control -> Fields -> “New Field”, enter the number of fields to create and click “Next”.

image06

Enter the data for each field:

User:

  • Type: Text
  • Name: User
  • Label: User

Amount:

  • Type: Decimal
  • Name: Amount
  • Label: Amount

Months:

  • Type: Select
  • Name: Months
  • Label: Months

Interest:

  • Type: Decimal
  • Name: Interest
  • Label: Interest

Total:

  • Type: Decimal
  • Name: Total
  • Label: Total

Finally, we click on “Create” in order to save the configurations of each created field.

image07

The next step is to mark as required the fields: User, Amount and Months; And as read-only the fields: Interest and Total (option located in Control -> Edit Fields).

image08

In the field {Months} we must show the available months so that the user can choose how many months he wants to return the amount, so we selected the field type SELECT.  

image09

In order to display the data you must configure the section “Lookup Settings” firstly indicating in “Choose connection” the database connection that we want to use to obtain the data. Then click on “Create Select” and choose the table, the id and the field to display.

image10

With this we have finished the visual part and we can start working with the processes.

Obtain Percentage of Interest to Apply:

In the section Programming -> PHP Methods click on “New Method”, we indicate the name that we want to give (in our example “ObtainParameter”) and click on “Create”.

image11

Once we have the coding window open, to avoid writing all the code we need we can insert the sample code that most closely matches our goal. As you can see in the following image, we used the code “Selecting fields from another table” using the SC_LOOKUP macro and made the modifications based on it.

image12

To the code that Scriptcase provides, we modify the SQL Statement that will get the months and related interest, in addition to adding as filter the field {Months} that refers to the month chosen by the user in our application.

The parameters that the SC_LOOKUP macro receives are:

  • Name of the Dataset to create (in the example rsMonth).
  • SQL statement (in the example the statement is stored in the $check_sql variable).
  • Database Connection to use (in the example we need to connect to Postgres, so the connection must be conn_Parameter).

The next step is to rename the variables and fields to be assigned after the macro returns the values and stores them inside the Dataset (rsMonth).

We create a session variable ([NumofMonths]) in order to store the number of quotas or months chosen, because we can not use the field {Months} since it contains the id of the table.

We also assign to the field {Interest} the percentage of interest that will be applied according to the month that the user chose.

In case the query does not return values we will assign values by default foreseeing any malfunction not contemplated.

image13

Calculate Total Amount with Interest:

For this it is not necessary to make queries to the Database, since we assign the values previously obtained to the fields of our application.

image14

Having the two PHP Methods that allow us to perform the desired calculations, we have to call them when the user changes the amount and / or the number of months desired. This is achieved using the Ajax Events and choosing the desired event, in our case the event is “onChange” which will allow the calculation to be performed after any modification.

image15

Then select the desired field: {Amount} and the event: onChange, click on “Create Event” in order to allow us to edit it. Inside we add the call to both Methods so that it realizes both processes.

The same thing is repeated with the field {Months} so that it updates the data every time the number of months changes.

image16

The last step is to code the event onValidateSuccess so that it inserts the payments that the user will have to make taking into account the months and the amount with interest to pay during that period.

To do this we first calculate and assign to the session variable [Amount] the amount to be paid each month.

With a for loop we must go through the insertion process as many times as the user has selected months.

In the following image you can see that we also have an example code for insertion. Taking that code as a base, we update the table name and column data according to our structure. All this we do inside an if loop that will allow us to control that it is not the last month.

The parameters received by the SC_EXEC_SQL macro used for the insertion are:

  • SQL statement (in the example the statement is stored in the $insert_sql variable).
  • Database connection to use (in the example Payment is in the MySQL table, so the connection should be conn_Sales).

It should be mentioned that this macro allows us to execute any SQL Statement on the Database Server, so it is very useful for executing stored procedures or even maintenance tasks.

image17

For the last installment (month) we will give special treatment because the rounding of the division sometimes may not be exact, which would cause in the end that the Total to be paid and the sum of quotas are not equal.

In order to ensure that the sum of the quotas and the total to be paid are equal we apply to the last quota the difference between the Total to be paid and the sum of quotas previously inserted.

image18

After finishing writing the code, if we execute it will appear the white screen requesting the assignment of values to variables used in the application.

image19

Because our variables are for internal use and we do not expect to receive them, we must change the Variable Type to “Out”. That will avoid requesting the variables when running the application.

image20

Now, when running the application should be similar to the following image where we will be asked to enter User, Amount and select the number of months.

image21

When clicking on the confirmation button the process must insert in the table Payment a row for each monthly payment that the user must make, as we see in the following image.

image22

You might also like…

Deep Data and Scriptcase: Revealing what goes on in a Business

Deep learning can give rise to something called Deep Data. Useful ways you can utilize Scriptcase to...

Tips on facilitators for developers – Part II

Hello! On the latest post i’ve shown some hints on facilitators for developers. On today’s post ...

Tips on facilitators for developers – Part I

Check out some options within Scriptcase that will work as facilitators that developers can use befo...

Comment this post

Get new posts, resources, offers and more each week.