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.

Icon

For the query to be viewed in Fluig, it must be released, that is, its development must be completed through the GOLD tool.

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.

Icon

So far, it will not be possible to open a default security LOG call of LOGIX in the quick query functions. These queries are not registered on the menu and are not given permissions in the ERP, only via Fluig Identity. Therefore, if the functions have LOG security calls, query execution will be interrupted.

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.

Árvore de páginas

Platform | 5. Quick query widget

Objective

Registering queries

Development

START Function

Example

SEARCH Function

Example

AUTO-COMPLETE Function

Example

Automatic positioning in the selected record

Example