Generating reports using Top Connect in Advpl

Question

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

To follow 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 of this code is in the temporary file name (TEMPDUPL), because if one user is generating the report and another is generating the same report the system does not allow.

I know there’s a function to solve this, but I’m not locating.

Answer 1

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

The altered source 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)

Answer 2

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)