Boa tarde pessoal, Gostaria de ver se alguem poderia me ajudar, criei um relatorio aqui e estou tendo um serio problema de performance nele, sou iniciante na logico progress, tipo preciso ver todos os titulo do ems5 que tiveram um determinado movimento chamado alteração de vencimento e o historico referente a este movimento,fiz relacionamento de 3 tabelas tit_acr - movto_ocor_bcia e histor_movto_tit_acr dei EACH nas tres acho que pode estar ai o problema da performance mas nao sei como colocar um codigo para que ele seja mais rapido. Não sei se pode ser o campo des_text_histor da tabela histor_movto_tit_acr pois ela tem 2000 caracteres, isto e padrao datasul. abaixo esta o codigo do .p Código: /***************************************************************************** ** ** Programa: ES112A.p ** ** Data....: 17/04/2009 ** ** Autor...: Diego Carvalho de Freitas ** ** Objetivo: Relatorio prorroga‡äes de Titulos EMS505 ** ** VersÆo..: 1.00.000 - super ** ** OBS.....: Este fonte foi gerado pelo Data Viewer ** *******************************************************************************/ define variable c-prog-gerado as character no-undo initial "ES102A". def new global shared var c-arquivo-log as char format "x(60)"no-undo. def new global shared var c-prg-vrs as char no-undo. def new global shared var c-prg-obj as char no-undo. run grapi/gr2002.p (input c-prog-gerado, input "2.00.00.000"). /****************** Defini‡Æo de Tabelas Tempor rias do Relat¢rio **********************/ def new shared TEMP-TABLE tt-titulo FIELD cod-cliente AS INT FIELD nome-abrev AS CHAR FIELD cod-matriz AS INT FIELD nome-abrev-matriz AS CHAR FIELD cod-repre AS INT FIELD cod-titulo AS CHAR FIELD cod-parcela AS CHAR FIELD val-renegociado AS DEC FIELD cond-pag AS CHAR FIELD data-transacao AS DATE FIELD data-vencimento AS DATE FIELD data-novo-vencimento AS DATE FIELD historico-titulo AS CHAR. define temp-table tt-raw-digita field raw-digita as raw. define temp-table tt-param field destino as integer field arquivo as char field usuario as char field data-exec as date field hora-exec as integer field parametro as logical field formato as integer field v_num_tip_aces_usuar as integer field ep-codigo as integer /* campos de sele‡Æo */ field c-cod-cliente-ini like tt-titulo.cod-cliente field c-cod-cliente-fim like tt-titulo.cod-cliente FIELD c-cod-matriz-ini LIKE tt-titulo.cod-matriz FIELD c-cod-matriz-fim LIKE tt-titulo.cod-matriz FIELD c-cod-repre-ini LIKE tt-titulo.cod-repre FIELD c-cod-repre-fim LIKE tt-titulo.cod-repre FIELD c-data-transacao-ini LIKE tt-titulo.data-transacao FIELD c-data-transacao-fim LIKE tt-titulo.data-transacao FIELD c-data-vencimento-ini LIKE tt-titulo.data-vencimento FIELD c-data-vencimento-fim LIKE tt-titulo.data-vencimento . /****************** INCLUDE COM VARIµVEIS GLOBAIS *********************/ def new global shared var i-ep-codigo-usuario like mguni.empresa.ep-codigo no-undo. def new Global shared var l-implanta as logical init no. def new Global shared var c-seg-usuario as char format "x(12)" no-undo. def new global shared var i-num-ped-exec-rpw as integer no-undo. def new global shared var i-pais-impto-usuario as integer format ">>9" no-undo. def new global shared var l-rpc as logical no-undo. def new global shared var r-registro-atual as rowid no-undo. def new global shared var c-arquivo-log as char format "x(60)"no-undo. def new global shared var i-num-ped as integer no-undo. def new global shared var v_cdn_empres_usuar like mguni.empresa.ep-codigo no-undo. def new global shared var v_cod_usuar_corren like mguni.usuar_mestre.cod_usuario no-undo. def new global shared var h_prog_segur_estab as handle no-undo. def new global shared var v_cod_grp_usuar_lst as char no-undo. def new global shared var v_num_tip_aces_usuar as int no-undo. def new global shared var rw-log-exec as rowid no-undo. /****************** FIM INCLUDE COM VARIµVEIS GLOBAIS *********************/ /****************** Defini‡ao de Parƒmetros do Relat¢rio *********************/ /****************** Defini‡ao de Vari veis de Sele‡Æo do Relat¢rio *********************/ def new shared var c-cod-cliente-ini like tt-titulo.cod-cliente format ">>>,>>>,>>9" initial "0" no-undo. def new shared VAR c-cod-cliente-fim like tt-titulo.cod-cliente format ">>>,>>>,>>9" initial "999999999" no-undo. def new shared var c-cod-matriz-ini like tt-titulo.cod-matriz format ">>>,>>>,>>9" initial "0" no-undo. def new shared var c-cod-matriz-fim like tt-titulo.cod-matriz format ">>>,>>>,>>9" initial "999999999" no-undo. def new shared var c-cod-repre-ini like tt-titulo.cod-repre format ">>>,>>9" initial "0" no-undo. def new shared var c-cod-repre-fim like tt-titulo.cod-repre format ">>>,>>9" initial "999" no-undo. def new shared var c-data-transacao-ini like tt-titulo.data-transacao format "99/99/9999" initial "01/01/2010" no-undo. def new shared var c-data-transacao-fim like tt-titulo.data-transacao format "99/99/9999" initial "12/31/9999" no-undo. def new shared var c-data-vencimento-ini like tt-titulo.data-vencimento FORMAT "99/99/9999" initial "01/01/2010" no-undo. def new shared var c-data-vencimento-fim like tt-titulo.data-vencimento format "99/99/9999" initial "12/31/9999" no-undo. /****************** Defini‡ao de Vari veis p/ Campos Virtuais do Relat¢rio *******************/ DEF VAR cod-cliente AS INT. DEF VAR nome-abrev AS CHAR. DEF VAR cod-matriz AS INT. DEF VAR nome-abrev-matriz AS CHAR. DEF VAR cod-repre AS INT. DEF VAR cod-titulo AS CHAR. DEF VAR cod-parcela AS CHAR. DEF VAR val-renegociado AS DEC. DEF VAR cond-pag AS CHAR. DEF VAR data-transacao AS DATE. DEF VAR data-vencimento AS DATE. DEF VAR data-novo-vencimento AS DATE. DEF VAR historico-titulo AS CHAR. /****************** Defini‡ao de Vari veis Campo do Layout do Relat¢rio **********************/ /****************** Defini‡ao de Vari veis do Relat¢rio NÆo Pedidas em Tela ******************/ /****************** Defini‡ao de Vari veis de Total do Relat¢rio *****************************/ /*DEF VAR de-tot-qtd-vendido as dec no-undo. DEF VAR de-tot-fat-liq as dec no-undo. DEF VAR de-tot-pre-tab-total as dec no-undo. def var de-total-devol as dec no-undo. DEF VAR de-dif-tot AS DEC NO-UNDO.*/ /****************** Defini‡ao de Vari veis dos Calculos do Relat¢rio *************************/ def input param raw-param as raw no-undo. def input param table for tt-raw-digita. /***************** Defini‡ao de Vari veis de Processamento do Relat¢rio *********************/ def var h-acomp as handle no-undo. def var v-cod-destino-impres as char no-undo. def var v-num-reg-lidos as int no-undo. def var v-num-point as int no-undo. def var v-num-set as int no-undo. def var v-num-linha as int no-undo. /****************** Defini‡ao de Forms do Relat¢rio 132 Colunas ***************************************/ form tt-titulo.cod-cliente column-label "CLIENTE" /* at 001 */ tt-titulo.cod-matriz column-label "MATRIZ" /* at 010 */ tt-titulo.cod-repre column-label "REPRES." /*format ">>>,>>9.9999" at 072*/ tt-titulo.cod-titulo column-label "TITULO" /*format ">>>,>>9.9999" at 084 */ tt-titulo.cod-parcela column-label "PARC" /*format ">>>,>>9.9999" at 096 */ tt-titulo.val-renegociado column-label "VALOR" /*format ">>>,>>9.9999" at 108 */ tt-titulo.cond-pag column-label "COND-PAG" /*format ">>>,>>9.9999" at 118 */ tt-titulo.data-transacao column-label "TRANS." /*format ">>>,>>9.9999" at 132 */ tt-titulo.data-vencimento column-label "VENC." /*format ">>>,>>9.9999" at 132 */ tt-titulo.data-novo-vencimento column-label "NOVO VENC." /*format ">>>,>>9.9999" at 132 */ tt-titulo.historico-titulo column-label "HISTORICO" /*format ">>>,>>9.9999" at 132 */ with down width 132 no-box stream-io frame f-relat-09-172. create tt-param. raw-transfer raw-param to tt-param. def temp-table tt-editor no-undo field linha as integer field conteudo as character format "x(80)" index editor-id is primary unique linha. def var rw-log-exec as rowid no-undo. def var c-erro-rpc as character format "x(60)" initial " " no-undo. def var c-erro-aux as character format "x(60)" initial " " no-undo. def var c-ret-temp as char no-undo. def var h-servid-rpc as handle no-undo. define var c-empresa as character format "x(40)" no-undo. define var c-titulo-relat as character format "x(50)" no-undo. define var i-numper-x as integer format "ZZ" no-undo. define var da-iniper-x as date format "99/99/9999" no-undo. define var da-fimper-x as date format "99/99/9999" no-undo. define var i-page-size-rel as integer no-undo. define var c-programa as character format "x(08)" no-undo. define var c-versao as character format "x(04)" no-undo. define var c-revisao as character format "999" no-undo. define new shared var c-impressora as character no-undo. define new shared var c-layout as character no-undo. define new shared var v_num_count as integer no-undo. define new shared var c-arq-control as character no-undo. define new shared var c-sistema as character format "x(25)" no-undo. define new shared var c-rodape as character no-undo. define new shared buffer b_ped_exec_style for mguni.ped_exec. define new shared buffer b_servid_exec_style for mguni.servid_exec. define new shared stream str-rp. assign c-programa = "es116" c-versao = "2.00" c-revisao = ".00.000" c-titulo-relat = "RELATORIO HISTORICO DE TITULOS" c-sistema = "espc". if tt-param.formato = 2 then do: define var data-cabecalho as char. form header fill("-", 132) format "x(132)" skip c-empresa c-titulo-relat at 50 format "x(30)" data-cabecalho format "x(20)" at 085 "Folha:" at 122 page-number(str-rp) at 128 format ">>>>9" skip fill("-", 112) format "x(110)" today format "99/99/9999" "-" string(time, "HH:MM:SS")skip(1) /*"FAMILIA COMERCIAL: " familia-cab */SKIP(01) " CLIENTE MATRIZ REP. TITULO P/ VALOR COND-PAG TRANSACAO. VENC. NOVO-VENC HISTORICO" SKIP "-------- --------- --- ------- -- -------------- --------- --------- --------- --------- ------------------" SKIP /* 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890*/ /* 1 2 3 4 5 6 7 8 9 10 11 12*/ with stream-io width 132 no-labels no-box page-top frame f-cabec. form header fill("-", 132) format "x(132)" skip c-empresa c-titulo-relat at 50 "Folha:" at 122 page-number(str-rp) at 128 format ">>>>9" skip "Periodo:" i-numper-x at 08 "-" da-iniper-x at 14 "to" da-fimper-x fill("-", 74) format "x(72)" today format "99/99/9999" "-" string(time, "HH:MM:SS") skip(2) with stream-io width 132 no-labels no-box page-top frame f-cabper. run grapi/gr2004.p. form header c-rodape format "x(132)" with stream-io width 132 no-labels no-box page-bottom frame f-rodape. end. /* tt-param.formato = 2 */ run grapi/gr2009.p (input tt-param.destino, input tt-param.arquivo, input tt-param.usuario, input no). assign i-ep-codigo-usuario = tt-param.ep-codigo v_cdn_empres_usuar = i-ep-codigo-usuario. assign c-cod-cliente-ini = tt-param.c-cod-cliente-ini c-cod-cliente-fim = tt-param.c-cod-cliente-fim c-cod-matriz-ini = tt-param.c-cod-matriz-ini c-cod-matriz-fim = tt-param.c-cod-matriz-fim c-cod-repre-ini = tt-param.c-cod-repre-ini c-cod-repre-fim = tt-param.c-cod-repre-fim c-data-transacao-ini = tt-param.c-data-transacao-ini c-data-transacao-fim = tt-param.c-data-transacao-fim c-data-vencimento-ini = tt-param.c-data-vencimento-ini c-data-vencimento-fim = tt-param.c-data-vencimento-fim. find first mguni.empresa no-lock where mguni.empresa.ep-codigo = i-ep-codigo-usuario no-error. if avail mguni.empresa then assign c-empresa = mguni.empresa.razao-social. else assign c-empresa = "". /* for each e disp */ def var l-imprime as logical no-undo. assign l-imprime = no. if tt-param.destino = 1 then assign v-cod-destino-impres = "Impressora". else if tt-param.destino = 2 then assign v-cod-destino-impres = "Arquivo". else assign v-cod-destino-impres = "Terminal". /**********************************************************************************************************************************/ run utp/ut-acomp.p persistent set h-acomp. run pi-inicializar in h-acomp(input "Gerando Relatorio ... Aguarde ...."). assign v-num-reg-lidos = 0. run pi-carrega-tabela. view stream str-rp frame f-cabec. VIEW stream str-rp frame f-rodape. assign l-imprime = yes. assign v-num-reg-lidos = v-num-reg-lidos + 1. run pi-acompanhar in h-acomp(input string(v-num-reg-lidos)). FOR EACH tt-titulo NO-LOCK break by tt-titulo.data-transacao: put stream str-rp tt-titulo.cod-cliente FORMAT ">>>>>>>>9" AT 01 tt-titulo.nome-abrev FORMAT "x(12)" AT 13 tt-titulo.cod-repre FORMAT ">>9" AT 26 tt-titulo.cod-titulo FORMAT "X(10)" AT 31 tt-titulo.cod-parcela FORMAT "X(02)" AT 43 tt-titulo.val-renegociado FORMAT "->>,>>>,>>9.99" AT 46 tt-titulo.cond-pag FORMAT "X(08)" AT 61 tt-titulo.data-transacao FORMAT "99/99/9999" AT 70 tt-titulo.data-vencimento FORMAT "99/99/9999" AT 82 tt-titulo.data-novo-vencimento FORMAT "99/99/9999" AT 94 tt-titulo.historico-titulo FORMAT "X(2000)" AT 106. END. run pi-finalizar in h-acomp. if tt-param.destino <> 1 then page stream str-rp. else do: if tt-param.parametro = yes then page stream str-rp. end. /************************************* PROCEDURE CARREGA TABELA *********************/ procedure pi-carrega-tabela: FOR EACH tit_acr USE-INDEX es_dat_venc_origin WHERE tit_acr.cdn_cliente >= c-cod-cliente-ini AND tit_acr.cdn_cliente <= c-cod-cliente-FIM AND tit_acr.cdn_repres >= c-cod-repre-ini AND tit_acr.cdn_repres <= c-cod-repre-fim AND tit_acr.dat_vencto_origin_tit_acr >= c-data-vencimento-ini AND tit_acr.dat_vencto_origin_tit_acr <= c-data-vencimento-fim NO-LOCK, EACH movto_ocor_bcia USE-INDEX es_dat_gerac_movto WHERE movto_ocor_bcia.dat_gerac_movto_ocor_bcia >= c-data-transacao-ini AND movto_ocor_bcia.dat_gerac_movto_ocor_bcia <= c-data-transacao-fim AND movto_ocor_bcia.ind_tip_ocor_bcia BEGINS 'ALTERA€ÇO VENCIMENTO' AND movto_ocor_bcia.cod_estab = tit_acr.cod_estab AND movto_ocor_bcia.num_id_tit_acr = tit_acr.num_id_tit_acr NO-LOCK, EACH histor_movto_tit_acr USE-INDEX hstrmvtt_id WHERE histor_movto_tit_acr.cod_estab = movto_ocor_bcia.cod_estab AND histor_movto_tit_acr.num_id_tit_acr = movto_ocor_bcia.num_id_tit_acr AND histor_movto_tit_acr.num_id_movto_tit_acr = movto_ocor_bcia.num_id_movto_tit_acr /*AND (LOOKUP("EDNEIA",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("PAULA",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("ALEX",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("SOLICITADO",histor_movto_tit_acr.des_text_histor," ") > 0) NO-LOCK*/. if avail tit_acr then do: create tt-titulo. end. ASSIGN tt-titulo.cod-cliente = tit_acr.cdn_cliente tt-titulo.nome-abrev = tit_acr.nom_abrev tt-titulo.cod-repre = tit_acr.cdn_repres tt-titulo.cod-titulo = tit_acr.cod_tit_acr tt-titulo.cod-parcela = tit_acr.cod_parcela tt-titulo.data-vencimento = tit_acr.dat_vencto_origin_tit_acr tt-titulo.data-novo-vencimento = tit_acr.dat_vencto_tit_acr tt-titulo.cond-pag = tit_acr.cod_cond_pagto tt-titulo.val-renegociado = movto_ocor_bcia.val_movto_ocor_bcia tt-titulo.data-transacao = movto_ocor_bcia.dat_movto_ocor_bcia tt-titulo.historico-titulo = histor_movto_tit_acr.des_text_histor. END. end procedure. procedure pi-print-editor: def input param c-editor as char no-undo. def input param i-len as integer no-undo. def var i-linha as integer no-undo. def var i-aux as integer no-undo. def var c-aux as char no-undo. def var c-ret as char no-undo. for each tt-editor: delete tt-editor. end. /**********************************************************************************************************/ assign c-ret = chr(255) + chr(255). do while c-editor <> "": if c-editor <> "" then do: assign i-aux = index(c-editor, chr(10)). if i-aux > i-len or (i-aux = 0 and length(c-editor) > i-len) then assign i-aux = r-index(c-editor, " ", i-len + 1). if i-aux = 0 then assign c-aux = substr(c-editor, 1, i-len) c-editor = substr(c-editor, i-len + 1). else assign c-aux = substr(c-editor, 1, i-aux - 1) c-editor = substr(c-editor, i-aux + 1). if i-len = 0 then assign entry(1, c-ret, chr(255)) = c-aux. else do: assign i-linha = i-linha + 1. create tt-editor. assign tt-editor.linha = i-linha tt-editor.conteudo = c-aux. end. end. if i-len = 0 then return c-ret. end. return c-ret. end procedure. IF VALID-HANDLE(h-acomp) THEN /*gr9030g*/ RUN pi-finalizar IN h-acomp NO-ERROR. return 'OK'. /* fim do programa */
Diego boa tarde. Dá uma olhada com as modificações que eu fiz e vê se vai melhorar a performance. Favor ler os comentários para você entender. Código: /***************************************************************************** ** ** Programa: ES112A.p ** ** Data....: 17/04/2009 ** ** Autor...: Diego Carvalho de Freitas ** ** Objetivo: Relatorio prorroga‡äes de Titulos EMS505 ** ** VersÆo..: 1.00.000 - super ** ** OBS.....: Este fonte foi gerado pelo Data Viewer ** *******************************************************************************/ define variable c-prog-gerado as character no-undo initial "ES102A". def new global shared var c-arquivo-log as char format "x(60)"no-undo. def new global shared var c-prg-vrs as char no-undo. def new global shared var c-prg-obj as char no-undo. run grapi/gr2002.p (input c-prog-gerado, input "2.00.00.000"). /****************** Defini‡Æo de Tabelas Tempor rias do Relat¢rio **********************/ def new shared TEMP-TABLE tt-titulo FIELD cod-cliente AS INT FIELD nome-abrev AS CHAR FIELD cod-matriz AS INT FIELD nome-abrev-matriz AS CHAR FIELD cod-repre AS INT FIELD cod-titulo AS CHAR FIELD cod-parcela AS CHAR FIELD val-renegociado AS DEC FIELD cond-pag AS CHAR FIELD data-transacao AS DATE FIELD data-vencimento AS DATE FIELD data-novo-vencimento AS DATE FIELD historico-titulo AS CHAR. define temp-table tt-raw-digita field raw-digita as raw. define temp-table tt-param field destino as integer field arquivo as char field usuario as char field data-exec as date field hora-exec as integer field parametro as logical field formato as integer field v_num_tip_aces_usuar as integer field ep-codigo as integer /* campos de sele‡Æo */ field c-cod-cliente-ini like tt-titulo.cod-cliente field c-cod-cliente-fim like tt-titulo.cod-cliente FIELD c-cod-matriz-ini LIKE tt-titulo.cod-matriz FIELD c-cod-matriz-fim LIKE tt-titulo.cod-matriz FIELD c-cod-repre-ini LIKE tt-titulo.cod-repre FIELD c-cod-repre-fim LIKE tt-titulo.cod-repre FIELD c-data-transacao-ini LIKE tt-titulo.data-transacao FIELD c-data-transacao-fim LIKE tt-titulo.data-transacao FIELD c-data-vencimento-ini LIKE tt-titulo.data-vencimento FIELD c-data-vencimento-fim LIKE tt-titulo.data-vencimento . /****************** INCLUDE COM VARIµVEIS GLOBAIS *********************/ def new global shared var i-ep-codigo-usuario like mguni.empresa.ep-codigo no-undo. def new Global shared var l-implanta as logical init no. def new Global shared var c-seg-usuario as char format "x(12)" no-undo. def new global shared var i-num-ped-exec-rpw as integer no-undo. def new global shared var i-pais-impto-usuario as integer format ">>9" no-undo. def new global shared var l-rpc as logical no-undo. def new global shared var r-registro-atual as rowid no-undo. def new global shared var c-arquivo-log as char format "x(60)"no-undo. def new global shared var i-num-ped as integer no-undo. def new global shared var v_cdn_empres_usuar like mguni.empresa.ep-codigo no-undo. def new global shared var v_cod_usuar_corren like mguni.usuar_mestre.cod_usuario no-undo. def new global shared var h_prog_segur_estab as handle no-undo. def new global shared var v_cod_grp_usuar_lst as char no-undo. def new global shared var v_num_tip_aces_usuar as int no-undo. def new global shared var rw-log-exec as rowid no-undo. /****************** FIM INCLUDE COM VARIµVEIS GLOBAIS *********************/ /****************** Defini‡ao de Parƒmetros do Relat¢rio *********************/ /****************** Defini‡ao de Vari veis de Sele‡Æo do Relat¢rio *********************/ def new shared var c-cod-cliente-ini like tt-titulo.cod-cliente format ">>>,>>>,>>9" initial "0" no-undo. def new shared VAR c-cod-cliente-fim like tt-titulo.cod-cliente format ">>>,>>>,>>9" initial "999999999" no-undo. def new shared var c-cod-matriz-ini like tt-titulo.cod-matriz format ">>>,>>>,>>9" initial "0" no-undo. def new shared var c-cod-matriz-fim like tt-titulo.cod-matriz format ">>>,>>>,>>9" initial "999999999" no-undo. def new shared var c-cod-repre-ini like tt-titulo.cod-repre format ">>>,>>9" initial "0" no-undo. def new shared var c-cod-repre-fim like tt-titulo.cod-repre format ">>>,>>9" initial "999" no-undo. def new shared var c-data-transacao-ini like tt-titulo.data-transacao format "99/99/9999" initial "01/01/2010" no-undo. def new shared var c-data-transacao-fim like tt-titulo.data-transacao format "99/99/9999" initial "12/31/9999" no-undo. def new shared var c-data-vencimento-ini like tt-titulo.data-vencimento FORMAT "99/99/9999" initial "01/01/2010" no-undo. def new shared var c-data-vencimento-fim like tt-titulo.data-vencimento format "99/99/9999" initial "12/31/9999" no-undo. /****************** Defini‡ao de Vari veis p/ Campos Virtuais do Relat¢rio *******************/ DEF VAR cod-cliente AS INT. DEF VAR nome-abrev AS CHAR. DEF VAR cod-matriz AS INT. DEF VAR nome-abrev-matriz AS CHAR. DEF VAR cod-repre AS INT. DEF VAR cod-titulo AS CHAR. DEF VAR cod-parcela AS CHAR. DEF VAR val-renegociado AS DEC. DEF VAR cond-pag AS CHAR. DEF VAR data-transacao AS DATE. DEF VAR data-vencimento AS DATE. DEF VAR data-novo-vencimento AS DATE. DEF VAR historico-titulo AS CHAR. /****************** Defini‡ao de Vari veis Campo do Layout do Relat¢rio **********************/ /****************** Defini‡ao de Vari veis do Relat¢rio NÆo Pedidas em Tela ******************/ /****************** Defini‡ao de Vari veis de Total do Relat¢rio *****************************/ /*DEF VAR de-tot-qtd-vendido as dec no-undo. DEF VAR de-tot-fat-liq as dec no-undo. DEF VAR de-tot-pre-tab-total as dec no-undo. def var de-total-devol as dec no-undo. DEF VAR de-dif-tot AS DEC NO-UNDO.*/ /****************** Defini‡ao de Vari veis dos Calculos do Relat¢rio *************************/ def input param raw-param as raw no-undo. def input param table for tt-raw-digita. /***************** Defini‡ao de Vari veis de Processamento do Relat¢rio *********************/ def var h-acomp as handle no-undo. def var v-cod-destino-impres as char no-undo. def var v-num-reg-lidos as int no-undo. def var v-num-point as int no-undo. def var v-num-set as int no-undo. def var v-num-linha as int no-undo. /****************** Defini‡ao de Forms do Relat¢rio 132 Colunas ***************************************/ form tt-titulo.cod-cliente column-label "CLIENTE" /* at 001 */ tt-titulo.cod-matriz column-label "MATRIZ" /* at 010 */ tt-titulo.cod-repre column-label "REPRES." /*format ">>>,>>9.9999" at 072*/ tt-titulo.cod-titulo column-label "TITULO" /*format ">>>,>>9.9999" at 084 */ tt-titulo.cod-parcela column-label "PARC" /*format ">>>,>>9.9999" at 096 */ tt-titulo.val-renegociado column-label "VALOR" /*format ">>>,>>9.9999" at 108 */ tt-titulo.cond-pag column-label "COND-PAG" /*format ">>>,>>9.9999" at 118 */ tt-titulo.data-transacao column-label "TRANS." /*format ">>>,>>9.9999" at 132 */ tt-titulo.data-vencimento column-label "VENC." /*format ">>>,>>9.9999" at 132 */ tt-titulo.data-novo-vencimento column-label "NOVO VENC." /*format ">>>,>>9.9999" at 132 */ tt-titulo.historico-titulo column-label "HISTORICO" /*format ">>>,>>9.9999" at 132 */ with down width 132 no-box stream-io frame f-relat-09-172. create tt-param. raw-transfer raw-param to tt-param. def temp-table tt-editor no-undo field linha as integer field conteudo as character format "x(80)" index editor-id is primary unique linha. def var rw-log-exec as rowid no-undo. def var c-erro-rpc as character format "x(60)" initial " " no-undo. def var c-erro-aux as character format "x(60)" initial " " no-undo. def var c-ret-temp as char no-undo. def var h-servid-rpc as handle no-undo. define var c-empresa as character format "x(40)" no-undo. define var c-titulo-relat as character format "x(50)" no-undo. define var i-numper-x as integer format "ZZ" no-undo. define var da-iniper-x as date format "99/99/9999" no-undo. define var da-fimper-x as date format "99/99/9999" no-undo. define var i-page-size-rel as integer no-undo. define var c-programa as character format "x(08)" no-undo. define var c-versao as character format "x(04)" no-undo. define var c-revisao as character format "999" no-undo. define new shared var c-impressora as character no-undo. define new shared var c-layout as character no-undo. define new shared var v_num_count as integer no-undo. define new shared var c-arq-control as character no-undo. define new shared var c-sistema as character format "x(25)" no-undo. define new shared var c-rodape as character no-undo. define new shared buffer b_ped_exec_style for mguni.ped_exec. define new shared buffer b_servid_exec_style for mguni.servid_exec. define new shared stream str-rp. assign c-programa = "es116" c-versao = "2.00" c-revisao = ".00.000" c-titulo-relat = "RELATORIO HISTORICO DE TITULOS" c-sistema = "espc". if tt-param.formato = 2 then do: define var data-cabecalho as char. form header fill("-", 132) format "x(132)" skip c-empresa c-titulo-relat at 50 format "x(30)" data-cabecalho format "x(20)" at 085 "Folha:" at 122 page-number(str-rp) at 128 format ">>>>9" skip fill("-", 112) format "x(110)" today format "99/99/9999" "-" string(time, "HH:MM:SS")skip(1) /*"FAMILIA COMERCIAL: " familia-cab */SKIP(01) " CLIENTE MATRIZ REP. TITULO P/ VALOR COND-PAG TRANSACAO. VENC. NOVO-VENC HISTORICO" SKIP "-------- --------- --- ------- -- -------------- --------- --------- --------- --------- ------------------" SKIP /* 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890*/ /* 1 2 3 4 5 6 7 8 9 10 11 12*/ with stream-io width 132 no-labels no-box page-top frame f-cabec. form header fill("-", 132) format "x(132)" skip c-empresa c-titulo-relat at 50 "Folha:" at 122 page-number(str-rp) at 128 format ">>>>9" skip "Periodo:" i-numper-x at 08 "-" da-iniper-x at 14 "to" da-fimper-x fill("-", 74) format "x(72)" today format "99/99/9999" "-" string(time, "HH:MM:SS") skip(2) with stream-io width 132 no-labels no-box page-top frame f-cabper. run grapi/gr2004.p. form header c-rodape format "x(132)" with stream-io width 132 no-labels no-box page-bottom frame f-rodape. end. /* tt-param.formato = 2 */ run grapi/gr2009.p (input tt-param.destino, input tt-param.arquivo, input tt-param.usuario, input no). assign i-ep-codigo-usuario = tt-param.ep-codigo v_cdn_empres_usuar = i-ep-codigo-usuario. assign c-cod-cliente-ini = tt-param.c-cod-cliente-ini c-cod-cliente-fim = tt-param.c-cod-cliente-fim c-cod-matriz-ini = tt-param.c-cod-matriz-ini c-cod-matriz-fim = tt-param.c-cod-matriz-fim c-cod-repre-ini = tt-param.c-cod-repre-ini c-cod-repre-fim = tt-param.c-cod-repre-fim c-data-transacao-ini = tt-param.c-data-transacao-ini c-data-transacao-fim = tt-param.c-data-transacao-fim c-data-vencimento-ini = tt-param.c-data-vencimento-ini c-data-vencimento-fim = tt-param.c-data-vencimento-fim. find first mguni.empresa no-lock where mguni.empresa.ep-codigo = i-ep-codigo-usuario no-error. if avail mguni.empresa then assign c-empresa = mguni.empresa.razao-social. else assign c-empresa = "". /* for each e disp */ def var l-imprime as logical no-undo. assign l-imprime = no. if tt-param.destino = 1 then assign v-cod-destino-impres = "Impressora". else if tt-param.destino = 2 then assign v-cod-destino-impres = "Arquivo". else assign v-cod-destino-impres = "Terminal". /**********************************************************************************************************************************/ run utp/ut-acomp.p persistent set h-acomp. run pi-inicializar in h-acomp(input "Gerando Relatorio ... Aguarde ...."). assign v-num-reg-lidos = 0. run pi-carrega-tabela. view stream str-rp frame f-cabec. VIEW stream str-rp frame f-rodape. assign l-imprime = yes. assign v-num-reg-lidos = v-num-reg-lidos + 1. run pi-acompanhar in h-acomp(input string(v-num-reg-lidos)). FOR EACH tt-titulo NO-LOCK break by tt-titulo.data-transacao: put stream str-rp tt-titulo.cod-cliente FORMAT ">>>>>>>>9" AT 01 tt-titulo.nome-abrev FORMAT "x(12)" AT 13 tt-titulo.cod-repre FORMAT ">>9" AT 26 tt-titulo.cod-titulo FORMAT "X(10)" AT 31 tt-titulo.cod-parcela FORMAT "X(02)" AT 43 tt-titulo.val-renegociado FORMAT "->>,>>>,>>9.99" AT 46 tt-titulo.cond-pag FORMAT "X(08)" AT 61 tt-titulo.data-transacao FORMAT "99/99/9999" AT 70 tt-titulo.data-vencimento FORMAT "99/99/9999" AT 82 tt-titulo.data-novo-vencimento FORMAT "99/99/9999" AT 94 tt-titulo.historico-titulo FORMAT "X(2000)" AT 106. END. run pi-finalizar in h-acomp. if tt-param.destino <> 1 then page stream str-rp. else do: if tt-param.parametro = yes then page stream str-rp. end. /************************************* PROCEDURE CARREGA TABELA *********************/ procedure pi-carrega-tabela: /* Antes */ /*FOR EACH tit_acr USE-INDEX es_dat_venc_origin WHERE tit_acr.cdn_cliente >= c-cod-cliente-ini AND tit_acr.cdn_cliente <= c-cod-cliente-FIM AND tit_acr.cdn_repres >= c-cod-repre-ini AND tit_acr.cdn_repres <= c-cod-repre-fim AND tit_acr.dat_vencto_origin_tit_acr >= c-data-vencimento-ini AND tit_acr.dat_vencto_origin_tit_acr <= c-data-vencimento-fim NO-LOCK,*/ /* Depois */ /* 01 - Colocar o índice que mais se aproxima dos seus filtros e na sequência dos campos */ /* 02 - Colocar a clásula Fields somente com os campos que você precisa para o relatório. PAra que não venha todos os campos da tabela. */ FOR EACH tit_acr fields( tit_acr.cod_estab tit_acr.dat_vencto_origin_tit_acr tit_acr.cdn_cliente tit_acr.cdn_repres tit_acr.num_id_tit_acr tit_acr.nom_abrev tit_acr.cod_tit_acr tit_acr.cod_parcela tit_acr.dat_vencto_tit_acr tit_acr.cod_cond_pagto ) USE-INDEX titacr_vencto WHERE tit_acr.cod_estab >= '' and /* campo para usar no índice */ tit_acr.dat_vencto_origin_tit_acr >= c-data-vencimento-ini AND /* campo para usar no índice */ tit_acr.dat_vencto_origin_tit_acr <= c-data-vencimento-fim and /* campo para usar no índice */ tit_acr.cdn_cliente >= c-cod-cliente-ini AND tit_acr.cdn_cliente <= c-cod-cliente-FIM AND tit_acr.cdn_repres >= c-cod-repre-ini AND tit_acr.cdn_repres <= c-cod-repre-fim NO-LOCK, /*EACH movto_ocor_bcia USE-INDEX es_dat_gerac_movto WHERE movto_ocor_bcia.dat_gerac_movto_ocor_bcia >= c-data-transacao-ini AND movto_ocor_bcia.dat_gerac_movto_ocor_bcia <= c-data-transacao-fim AND movto_ocor_bcia.ind_tip_ocor_bcia BEGINS 'ALTERA€ÇO VENCIMENTO' AND movto_ocor_bcia.cod_estab = tit_acr.cod_estab AND movto_ocor_bcia.num_id_tit_acr = tit_acr.num_id_tit_acr NO-LOCK,*/ EACH movto_ocor_bcia fields( movto_ocor_bcia.cod_estab movto_ocor_bcia.num_id_tit_acr movto_ocor_bcia.num_seq_movto_ocor_bcia movto_ocor_bcia.dat_gerac_movto_ocor_bcia movto_ocor_bcia.ind_tip_ocor_bcia movto_ocor_bcia.num_id_movto_tit_acr movto_ocor_bcia.val_movto_ocor_bcia movto_ocor_bcia.dat_movto_ocor_bcia ) USE-INDEX mvtcrbc_id WHERE movto_ocor_bcia.cod_estab = tit_acr.cod_estab and /* campo para usar no índice */ movto_ocor_bcia.num_id_tit_acr = tit_acr.num_id_tit_acr and /* campo para usar no índice */ movto_ocor_bcia.num_seq_movto_ocor_bcia >= 0 and /* campo para usar no índice */ movto_ocor_bcia.dat_gerac_movto_ocor_bcia >= c-data-transacao-ini AND movto_ocor_bcia.dat_gerac_movto_ocor_bcia <= c-data-transacao-fim AND movto_ocor_bcia.ind_tip_ocor_bcia BEGINS 'ALTERAÇÃO VENCIMENTO' NO-LOCK, EACH histor_movto_tit_acr fields( ) USE-INDEX hstrmvtt_id WHERE histor_movto_tit_acr.cod_estab = movto_ocor_bcia.cod_estab AND /* campo para usar no índice */ histor_movto_tit_acr.num_id_tit_acr = movto_ocor_bcia.num_id_tit_acr AND /* campo para usar no índice */ histor_movto_tit_acr.num_id_movto_tit_acr = movto_ocor_bcia.num_id_movto_tit_acr and /* campo para usar no índice */ histor_movto_tit_acr.num_seq_histor_movto_acr >= 0 no-lock: /*AND (LOOKUP("EDNEIA",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("PAULA",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("ALEX",histor_movto_tit_acr.des_text_histor," ") > 0 OR LOOKUP("SOLICITADO",histor_movto_tit_acr.des_text_histor," ") > 0) NO-LOCK*/ if avail tit_acr then do: create tt-titulo. end. ASSIGN tt-titulo.cod-cliente = tit_acr.cdn_cliente tt-titulo.nome-abrev = tit_acr.nom_abrev tt-titulo.cod-repre = tit_acr.cdn_repres tt-titulo.cod-titulo = tit_acr.cod_tit_acr tt-titulo.cod-parcela = tit_acr.cod_parcela tt-titulo.data-vencimento = tit_acr.dat_vencto_origin_tit_acr tt-titulo.data-novo-vencimento = tit_acr.dat_vencto_tit_acr tt-titulo.cond-pag = tit_acr.cod_cond_pagto tt-titulo.val-renegociado = movto_ocor_bcia.val_movto_ocor_bcia tt-titulo.data-transacao = movto_ocor_bcia.dat_movto_ocor_bcia tt-titulo.historico-titulo = histor_movto_tit_acr.des_text_histor. END. end procedure. procedure pi-print-editor: def input param c-editor as char no-undo. def input param i-len as integer no-undo. def var i-linha as integer no-undo. def var i-aux as integer no-undo. def var c-aux as char no-undo. def var c-ret as char no-undo. for each tt-editor: delete tt-editor. end. /**********************************************************************************************************/ assign c-ret = chr(255) + chr(255). do while c-editor <> "": if c-editor <> "" then do: assign i-aux = index(c-editor, chr(10)). if i-aux > i-len or (i-aux = 0 and length(c-editor) > i-len) then assign i-aux = r-index(c-editor, " ", i-len + 1). if i-aux = 0 then assign c-aux = substr(c-editor, 1, i-len) c-editor = substr(c-editor, i-len + 1). else assign c-aux = substr(c-editor, 1, i-aux - 1) c-editor = substr(c-editor, i-aux + 1). if i-len = 0 then assign entry(1, c-ret, chr(255)) = c-aux. else do: assign i-linha = i-linha + 1. create tt-editor. assign tt-editor.linha = i-linha tt-editor.conteudo = c-aux. end. end. if i-len = 0 then return c-ret. end. return c-ret. end procedure. IF VALID-HANDLE(h-acomp) THEN /*gr9030g*/ RUN pi-finalizar IN h-acomp NO-ERROR. return 'OK'. /* fim do programa */
Leia esse manual, eu também comecei a trabalhar com Progress a pouco tempo e ele ajudou a deixar meu relatório bem melhor. viewtopic.php?f=17&t=634 Outra coisa que você pode fazer é rodar a pi-acompanhar em cada for each pra ver onde está o gargalo, para ficou bem mais fácil encontrar o problema desse jeito.
