Objective
Quick query is responsible for showing the user data of indicators to be defined by the business areas for each of the TOTVS ERPs. This information is obtained through REST services implemented by each of the segments. The quick query widget will be similar to the figure below:
Registering queries
Quick queries must be registered through the form FRM0015 - Indicators. This registration must be filled out with the query information.
The name of the query must follow the standard composed of the Logix system code, the letter "i" and the indicator identification sequence.
Development
After creation, the 4GL source must be developed with the default functions for quick query execution on Fluig. Currently, three functions are required for each indicator:
- START: function responsible for returning initial quick query data;
- SEARCH: function responsible for returning query data; and
- AUTO_COMPLETE: function
responsible for returning the auto-complete information of the widget
in Fluig.
START Function
In the 4GL, the name of this function must have the suffix _indicator_start, cannot have parameters and must return a RECORD variable to Fluig with the assembly information for the quick query widget, such as:
- the filters that can be performed;
- the columns that will compose the grid with the query data;
- the list of predefined filters that can be used; and
- a list with the names of the programs that can be run from the query.
Example
#------------------------------# FUNCTION logi1_indicator_start() #------------------------------# DEFINE lr_start RECORD filters ARRAY[2] OF RECORD table_name LIKE frm_column.table_name, column_name LIKE frm_column.column_name END RECORD, columns ARRAY[3] OF RECORD table_name LIKE frm_column.table_name, column_name LIKE frm_column.column_name, can_order SMALLINT END RECORD, selection ARRAY[3] OF RECORD code CHAR(03), label CHAR(50) END RECORD, actions ARRAY[2] OF RECORD code CHAR(08), label CHAR(50) END RECORD END RECORD #Defines the quick query filter columns LET lr_start.filters[1].table_name = 'log_grupos' LET lr_start.filters[1].column_name = 'grupo' LET lr_start.filters[2].table_name = 'log_grupos' LET lr_start.filters[2].column_name = 'des_grupo' #Defines the quick query columns LET lr_start.columns[1].table_name = 'usuarios' LET lr_start.columns[1].column_name = 'cod_usuario' LET lr_start.columns[1].can_order = TRUE LET lr_start.columns[2].table_name = 'usuarios' LET lr_start.columns[2].column_name = 'nom_funcionario' LET lr_start.columns[2].can_order = TRUE LET lr_start.columns[3].table_name = 'usuarios' LET lr_start.columns[3].column_name = 'ind_admlog' LET lr_start.columns[3].can_order = FALSE #Defines which values will be in filter selection LET lr_start.selection[1].code = '*' LET lr_start.selection[1].label = 'Todos os usuários' LET lr_start.selection[2].code = 'S' LET lr_start.selection[2].label = 'Somente administradores' LET lr_start.selection[3].code = 'N' LET lr_start.selection[3].label = 'Somente usuários comuns' #Defines which programs can be called by the query LET lr_start.actions[1].code = 'log02700' LET lr_start.actions[1].label = 'Usuários' LET lr_start.actions[2].code = 'log02720' LET lr_start.actions[2].label = 'Grupos do usuário' RETURN lr_start END FUNCTION
Execution of the above function will allow the initialization of the quick query widget and will create it according to the screen below:
SEARCH Function
In 4GL, the name of this function must have the suffix _indicator_search, which must have the following parameters:
- name of the filter column selected by user;
- value of filter entered by user;
- value of pre-defined filter selected;
- name of sorting column;
- sorting type;
- amount of lines per page; and
- number of pages of current query.
All these parameters must be considered when performing a query on the data bank and the return must be a RECORD variable with:
- query data;
- total amount of records found in the data bank;
- total amount of records that will be returned to Fluig;
- total number of pages according to the query made; and
- page number of current query.
Example
#---------------------------------------------------------------------------------------------------------------# FUNCTION logi1_indicator_search(l_filter_column,l_filter_value,l_selection,l_sort_col,l_sort_order,l_rows,l_page) #---------------------------------------------------------------------------------------------------------------# DEFINE l_filter_column CHAR(50), #Filter column (optional) l_filter_value CHAR(300), #Value of filter column above (optional) l_selection CHAR(01), #Selected value of filter list (optional) l_sort_col CHAR(50), #Sorting column (optional) l_sort_order CHAR(04), #Sorting type ASC or DESC (optional) l_rows SMALLINT, #Maximum records per page l_page SMALLINT #Current search page DEFINE l_sql_stmt CHAR(500), l_sql_count CHAR(500), l_where_clause CHAR(250) DEFINE l_ind SMALLINT, l_position INTEGER DEFINE lr_search RECORD data ARRAY[60] OF RECORD #List with query results cod_usuario LIKE usuarios.cod_usuario, nom_funcionario LIKE usuarios.nom_funcionario, ind_admlog LIKE usuarios.ind_admlog END RECORD, count SMALLINT, #Total amount of records that will be returned total INTEGER, #Total amount of records found in query (without page number) cpage INTEGER, #Current records page pages SMALLINT #Total number of pages resulting from query END RECORD INITIALIZE lr_search.* TO NULL #Checks column and filter entered for parameter IF l_filter_column IS NOT NULL AND l_filter_value IS NOT NULL THEN LET l_where_clause = "UPPER(g."||l_filter_column CLIPPED||") LIKE '%"||UPSHIFT(l_filter_value) CLIPPED||"%'" ELSE LET l_where_clause = "1=1" END IF #Adds a selected filter IF l_selection IS NOT NULL AND l_selection <> "*" THEN LET l_where_clause = l_where_clause CLIPPED," AND u.ind_admlog = '",l_selection CLIPPED,"'" END IF #Assembles the SQL of the quick query LET l_sql_stmt = "SELECT DISTINCT", " u.cod_usuario,", " u.nom_funcionario,", " u.ind_admlog", " FROM usuarios u", " LEFT OUTER JOIN log_usu_grupos l", " ON l.usuario = u.cod_usuario", " LEFT OUTER JOIN log_grupos g", " ON g.grupo = l.grupo", " WHERE ",l_where_clause #Assembles the COUNT SQL (cannot contain ORDER BY) LET l_sql_count = "SELECT COUNT(*) FROM ("||l_sql_stmt CLIPPED||") t" #Attributes ORDER BY for the quick query SQL IF l_sort_col IS NOT NULL AND l_sort_order IS NOT NULL THEN LET l_sql_stmt = l_sql_stmt CLIPPED||" ORDER BY "||l_sort_col CLIPPED||" "||l_sort_order ELSE LET l_sql_stmt = l_sql_stmt CLIPPED||" ORDER BY 1" END IF WHENEVER ERROR CONTINUE PREPARE var_indicator_count FROM l_sql_count WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL QS_consoleDebugMessage(l_sql_count) CALL log0030_processa_err_sql("PREPARE SQL","var_indicator_count",1) RETURN NULL END IF WHENEVER ERROR CONTINUE EXECUTE var_indicator_count INTO lr_search.total WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL log0030_processa_err_sql("EXECUTE SQL","var_indicator_count",1) FREE var_indicator_count RETURN NULL END IF #Counts the number of pages that the query produced LET lr_search.pages = LOG_round(lr_search.total/l_rows,0) #Checks of the page informed exceeded the number of pages IF lr_search.pages > 0 THEN IF l_page > lr_search.pages THEN LET lr_search.cpage = lr_search.pages ELSE LET lr_search.cpage = l_page END IF ELSE LET lr_search.pages = 1 LET lr_search.cpage = l_page END IF WHENEVER ERROR CONTINUE FREE var_indicator_count PREPARE var_indicator_search FROM l_sql_stmt WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL QS_consoleDebugMessage(l_sql_stmt) CALL log0030_processa_err_sql("PREPARE SQL","var_indicator_search",1) RETURN NULL END IF WHENEVER ERROR CONTINUE DECLARE cq_indicator_search CURSOR FOR var_indicator_search WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL log0030_processa_err_sql("DECLARE CURSOR","cq_indicator_search",1) FREE var_indicator_search RETURN NULL END IF WHENEVER ERROR CONTINUE OPEN cq_indicator_search WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL log0030_processa_err_sql("OPEN CURSOR","cq_indicator_search",1) FREE var_indicator_search RETURN NULL END IF #Calculates the position of the current record according to the page and the amount of #lines per page LET l_ind = 1 LET l_position = ((l_rows * l_page) - l_rows) + 1 WHILE TRUE WHENEVER ERROR CONTINUE FETCH ABSOLUTE l_position cq_indicator_search INTO lr_search.data[l_ind].* WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN IF sqlca.sqlcode <> NOTFOUND THEN CALL log0030_processa_err_sql("FETCH CURSOR","cq_indicator_search",1) END IF EXIT WHILE END IF LET l_ind = l_ind + 1 LET l_position = l_position + 1 #Cannot exceed the limit of records of page IF l_ind > l_rows THEN EXIT WHILE END IF END WHILE WHENEVER ERROR CONTINUE CLOSE cq_indicator_search FREE cq_indicator_search WHENEVER ERROR STOP LET lr_search.count = l_ind - 1 RETURN lr_search END FUNCTION
AUTO-COMPLETE Function
In 4GL, the name of this function must have the suffix _indicator_autocomplete, which must have the following parameters:
- name of column selected for the filter; and
- value of filter entered by user.
The return must be a RECORD variable with:
- query data; and
- total amount of records found in the data bank..
Example
#-------------------------------------------------------------------# FUNCTION logi1_indicator_autocomplete(l_filter_column,l_filter_value) #-------------------------------------------------------------------# DEFINE l_filter_column CHAR(50), #Filter column (optional) l_filter_value CHAR(300) #Value of filter column above (optional) DEFINE l_sql_stmt CHAR(500), l_sql_count CHAR(500), l_where_clause CHAR(250) DEFINE l_ind SMALLINT, l_position INTEGER DEFINE lr_autocomplete RECORD data ARRAY[500] OF CHAR(200), count SMALLINT END RECORD INITIALIZE lr_autocomplete.* TO NULL #Checks column and filter entered for parameter IF l_filter_column IS NOT NULL AND l_filter_value IS NOT NULL THEN LET l_where_clause = "UPPER(g."||l_filter_column CLIPPED||") LIKE '%"||UPSHIFT(l_filter_value) CLIPPED||"%'" ELSE LET l_where_clause = "1=1" END IF #Assembles the auto-complete of the SQL LET l_sql_stmt = "SELECT g.",l_filter_column CLIPPED, " FROM log_grupos g", " WHERE ",l_where_clause CLIPPED, " ORDER BY 1" WHENEVER ERROR CONTINUE PREPARE var_indicator_autocomplete FROM l_sql_stmt WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL QS_consoleDebugMessage(l_sql_stmt) CALL log0030_processa_err_sql("PREPARE SQL","var_indicator_autocomplete",1) RETURN NULL END IF WHENEVER ERROR CONTINUE DECLARE cq_indicator_autocomplete CURSOR FOR var_indicator_autocomplete WHENEVER ERROR STOP IF sqlca.sqlcode <> 0 THEN CALL log0030_processa_err_sql("DECLARE CURSOR","cq_indicator_autocomplete",1) FREE var_indicator_autocomplete RETURN NULL END IF WHENEVER ERROR CONTINUE LET l_ind = 1 FOREACH cq_indicator_autocomplete INTO lr_autocomplete.data[l_ind] IF sqlca.sqlcode <> 0 THEN CALL log0030_processa_err_sql("FOREACH CURSOR","cq_indicator_autocomplete",1) EXIT FOREACH END IF LET l_ind = l_ind + 1 IF l_ind > 500 THEN EXIT FOREACH END IF END FOREACH FREE cq_indicator_autocomplete WHENEVER ERROR STOP LET lr_autocomplete.count = l_ind - 1 RETURN lr_autocomplete END FUNCTION
This function is executed when the user enters a query in the filter, as shown in the example below:
Automatic positioning in the selected record
When the user, in the quick query widget, selects a record in the grid and clicks one of the actions selected, the execution of the selected Logix program must already display the selected record has been queried.
For that to occur, the programs that are reported as actions in the START function must be changed. These programs must include a verification to obtain the execution parameters, using the APPLICATION_getNumArgs function, already performing a query operation.
Example
#-----------------# FUNCTION log02720() #-----------------# DEFINE l_usuario LIKE log_usu_grupos.usuario IF LOG_initApp("PADRAO") > 0 THEN RETURN END IF LET m_form_reference = _ADVPL_create_component(NULL,"LFORMMETADATA",m_container_reference) CALL _ADVPL_set_property(m_form_reference,"FORM","log02720",mr_log_usu_grupos,ma_log_usu_grupos) #Checks if there is an execution argument for the program IF APPLICATION_getNumArgs() > 0 THEN IF APPLICATION_getArg("action") = "INDICATOR" AND UPSHIFT(APPLICATION_getArg("indicator")) = "LOGI1" THEN LET l_usuario = DOWNSHIFT(APPLICATION_getArg("cod_usuario")) END IF END IF IF l_usuario IS NOT NULL THEN CALL _ADVPL_get_property(m_form_reference,"EXECUTE_OPERATION","FIND","a.usuario='"||l_usuario CLIPPED||"'") ELSE CALL _ADVPL_set_property(m_form_reference,"ACTIVATE",TRUE) END IF END FUNCTION
When executed through the Fluig quick query widget, the APPLICATION_getNumArgs function will always return at least three parameters, which are:
- ACTION: the word "INDICATOR", to indicate that it is an action by an indicator;
- INDICATOR: code of indicator registered in FRM0015.
Starting with the second parameter, the columns are sent with the values selected by the user.