Generating reports using Top Connect in Advpl

Navigation
3

When creating reports, using the Advpl language, the most practical is to select this data via Top Connect which is a software interface between the application and the DBMS. You create a query that is processed and the Top returns a temporary file that will be printed.

Here's an example: 

 Static Function RunReport(Cabec1,Cabec2,Titulo,nLin)
 Local cQuery       := ""
 Local _nCount      := 0.00 
 Private _nTotVlr   := 0.00 
 Private _nTotSal   := 0.00 

 cQuery := "SELECT SE1.E1_FILIAL, SE1.E1_NUMDP, SE1.E1_TIPO, SE1.E1_PREFIXO, SE1.E1_PARCELA, SE1.E1_EMISSAO, SE1.E1_VENCREA, SE1.E1_NOMCLI, SE1.E1_VALOR, SE1.E1_SALDO, SE1.D_E_L_E_T_ AS SITU, SE1.E1_NUM"+chr(13)
 cQuery += "FROM SE1010 SE1 "+chr(13)
 cQuery += "WHERE SE1.E1_TIPO='NF' AND SE1.E1_NUMDP<>'' "+chr(13)
 cQuery += "AND (SE1.E1_MSFIL='"+mv_par01+"')"+chr(13) 
 cQuery += "AND (SE1.E1_EMISSAO>='"+DTOS(mv_par02)+"'AND SE1.E1_EMISSAO<='"+DTOS(mv_par03)+"')"+chr(13)
 cQuery += "AND (SE1.E1_CLIENTE>='"+mv_par04+"'AND SE1.E1_CLIENTE<='"+mv_par05+"')"+chr(13)  
 cQuery += "AND SE1.R_E_C_N_O_= (SELECT MAX(SE1_2.R_E_C_N_O_) FROM SE1010 AS SE1_2 WHERE SE1.E1_FILIAL=SE1_2.E1_FILIAL AND SE1.E1_NUMDP=SE1_2.E1_NUMDP AND SE1.E1_NUM=SE1_2.E1_NUM AND SE1.E1_PARCELA=SE1_2.E1_PARCELA)"+chr(13)

 cQuery += "ORDER BY SE1.E1_NUMDP"+chr(13)

 cQuery := ChangeQuery(cQuery)

 DbUseArea(.T., "TOPCONN", TCGenQry(,,cQuery), "TEMPDUPL", .F., .T.)

 DbSelectArea("TEMPDUPL")

The problem with this code is in the name of the temporary file (TEMPDUPL) because if one user is generating the report and another is generating the same report the system does not allow it.

I know there is a function to solve this, but I'm not locating.

    
asked by anonymous 04.06.2015 / 19:27

2 answers

4

You can use the GetNextAlias() function, it will return a name to be used as an alias.

The changed font would look like this: 

Static Function RunReport(Cabec1,Cabec2,Titulo,nLin)
Local cQuery       := ""
Local _nCount      := 0.00 
Local cAlias       := GetNextAlias()

Private _nTotVlr   := 0.00 
Private _nTotSal   := 0.00 

cQuery := "SELECT SE1.E1_FILIAL, SE1.E1_NUMDP, SE1.E1_TIPO, SE1.E1_PREFIXO,    SE1.E1_PARCELA, SE1.E1_EMISSAO, SE1.E1_VENCREA, SE1.E1_NOMCLI, SE1.E1_VALOR,    SE1.E1_SALDO, SE1.D_E_L_E_T_ AS SITU, SE1.E1_NUM"+chr(13)
cQuery += "FROM SE1010 SE1 "+chr(13)
cQuery += "WHERE SE1.E1_TIPO='NF' AND SE1.E1_NUMDP<>'' "+chr(13)
cQuery += "AND (SE1.E1_MSFIL='"+mv_par01+"')"+chr(13) 
cQuery += "AND (SE1.E1_EMISSAO>='"+DTOS(mv_par02)+"'AND    SE1.E1_EMISSAO<='"+DTOS(mv_par03)+"')"+chr(13)
cQuery += "AND (SE1.E1_CLIENTE>='"+mv_par04+"'AND    SE1.E1_CLIENTE<='"+mv_par05+"')"+chr(13)  
cQuery += "AND SE1.R_E_C_N_O_= (SELECT MAX(SE1_2.R_E_C_N_O_) FROM SE1010 AS    SE1_2 WHERE SE1.E1_FILIAL=SE1_2.E1_FILIAL AND SE1.E1_NUMDP=SE1_2.E1_NUMDP AND   SE1.E1_NUM=SE1_2.E1_NUM AND SE1.E1_PARCELA=SE1_2.E1_PARCELA)"+chr(13)

cQuery += "ORDER BY SE1.E1_NUMDP"+chr(13)

cQuery := ChangeQuery(cQuery)

DbUseArea(.T., "TOPCONN", TCGenQry(,,cQuery), cAlias, .F., .T.)

DbSelectArea(cAlias)
    
05.06.2015 / 15:18
0

You can also validate if the area is open before executing the query and closing it. 

Static Function RunReport(Cabec1,Cabec2,Titulo,nLin)
Local cQuery       := ""
Local _nCount      := 0.00 
Local cAlias       := GetNextAlias()

Private _nTotVlr   := 0.00 
Private _nTotSal   := 0.00 

cQuery := "SELECT SE1.E1_FILIAL, SE1.E1_NUMDP, SE1.E1_TIPO, SE1.E1_PREFIXO,    SE1.E1_PARCELA, SE1.E1_EMISSAO, SE1.E1_VENCREA, SE1.E1_NOMCLI, SE1.E1_VALOR,    SE1.E1_SALDO, SE1.D_E_L_E_T_ AS SITU, SE1.E1_NUM"+chr(13)
cQuery += "FROM SE1010 SE1 "+chr(13)
cQuery += "WHERE SE1.E1_TIPO='NF' AND SE1.E1_NUMDP<>'' "+chr(13)
cQuery += "AND (SE1.E1_MSFIL='"+mv_par01+"')"+chr(13) 
cQuery += "AND (SE1.E1_EMISSAO>='"+DTOS(mv_par02)+"'AND    SE1.E1_EMISSAO<='"+DTOS(mv_par03)+"')"+chr(13)
cQuery += "AND (SE1.E1_CLIENTE>='"+mv_par04+"'AND    SE1.E1_CLIENTE<='"+mv_par05+"')"+chr(13)  
cQuery += "AND SE1.R_E_C_N_O_= (SELECT MAX(SE1_2.R_E_C_N_O_) FROM SE1010 AS    SE1_2 WHERE SE1.E1_FILIAL=SE1_2.E1_FILIAL AND SE1.E1_NUMDP=SE1_2.E1_NUMDP AND   SE1.E1_NUM=SE1_2.E1_NUM AND SE1.E1_PARCELA=SE1_2.E1_PARCELA)"+chr(13)

cQuery += "ORDER BY SE1.E1_NUMDP"+chr(13)

cQuery := ChangeQuery(cQuery)

// se area aberta
if Select(cAlias) > 0
    // fecha a area
    (cAlias)->( DbCloseArea() )
endif

DbUseArea(.T., "TOPCONN", TCGenQry(,,cQuery), cAlias, .F., .T.)

DbSelectArea(cAlias)
    
05.09.2018 / 21:23
Android studio has ndk support? Scroll through LUA tables