Árvore de páginas

Versões comparadas

Chave

  • Esta linha foi adicionada.
  • Esta linha foi removida.
  • A formatação mudou.

Manipula saldo por entidades

Características do Requisito

Linha de Produto:

Protheus

Segmento:

Serviços

Módulo:

Contabilidade Gerencial

Rotina:

Rotina

Nome Técnico

SaldoCTU

Saldo Totalizador por Entidade.

Rotina(s) envolvida(s)

Nome Técnico

CTBR265

Balancete Comparativo de Movim. de Contas x 12 Colunas

Ponto de Entrada:

CTBSldLP

Chamados relacionados

TSZRJJ

País(es):

Todos

Banco(s) de Dados:

Todos

Sistema(s) operacional(is):

Todos

Versões/Release:

11.80 / 12.1.17 / 12.1.23 / 12.1.25

Ponto de Entrada

Descrição:

O ponto de entrada CTBSldLP permite que o sistema enxergue a data da última e penúltima apuração mesmo se o parâmetro Posição Ant. L/P esteja com o conteúdo em Sim, na emissão do relatório Balancete Comparativo de Movimento de Contas x 12 meses.


Eventos:

Geração de relatorio Balancete Comparativo de Movim. de Contas x 12 Colunas

Programa Fonte:

CTBXSAL.PRW

Função:

CTBSldLP()

Parâmetros:
ParamIXBNome Variável ConteúdoTipo
1lImpAntLPConsidera Saldo Antes ApuraçãoL
2dDataLPData de Lucros/Perdas ApuraçãoD
3aMesesArray de Meses para RelatorioA
4lAcumSaldo AcumuladoL
5cMoedaMoedaC
6cTpSaldoTipo de Saldo C
7nOpc

1=Parte 1 do relatório

2=Parte 2 do relatorio

N

Retorno:

Nome

Tipo

Descrição

Obrigatório

 cQuery

Caracter

Query com o resultado desejado

Sim


//-----------------------------------------------------------------------------------------------------------------------------------------//

Exemplo Protheus  Versão 11

//-----------------------------------------------------------------------------------------------------------------------------------------//


#include 'Protheus.ch'
#include 'rwmake.ch'

User Function CTBSldLP()

Local lImpAntLP := PARAMIXB[1]
Local dDataLP := PARAMIXB[2]
Local aMeses := PARAMIXB[3]
Local lAcum := PARAMIXB[4]
Local cMoeda := PARAMIXB[5]
Local cTpSaldo := PARAMIXB[6]
Local nOpc := PARAMIXB[7]
Local cQuery := ''
Local nColunas := 0
Local lREL12Mes := ((Alltrim(FunName()) == 'CTBR265') .Or. (Alltrim(FunName()) == 'ABCTR265'))
Local lRel6Mes := ((Alltrim(FunName()) == 'CTBR260') .Or. (Alltrim(FunName()) == 'ABCTR260'))
Local nMes := 0

If nOpc == 1
If lREL12Mes .And. !lImpAntLP // só para comparativo 12 meses

For nColunas := 1 to Len(aMeses)
cQuery += " (SELECT SUM(CT7_CREDIT) - SUM(CT7_DEBITO) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nColunas <> 12 /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nColunas == 12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND ( "
cQuery += " (CT7_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery += " OR "
cQuery += " (CT7_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP, "CT7_LP <> 'Z'", "") + " ) "
cQuery += " ) "

Else /// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If !lImpAntLP .and. nColunas == 12
cQuery += " AND CT7_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery += " AND CT7_LP <> 'Z' "
Endif

cQuery += " AND CT7.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <> Len(aMeses)
cQuery += ", "
EndIf
Next

Elseif lRel6Mes .And. !lImpAntLP // só para comparativo 6 meses

For nColunas := 1 to Len(aMeses)
nMes := Month(aMeses[nColunas][3])

cQuery += " (SELECT SUM(CT7_CREDIT) - SUM(CT7_DEBITO) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nMes <> 12 /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nMes == 12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND ( "
cQuery += " (CT7_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery += " OR "
cQuery += " (CT7_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP, "CT7_LP <> 'Z'", "") + " ) "
cQuery += " ) "

Else /// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If !lImpAntLP .and. nColunas == 12
cQuery += " AND CT7_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery += " AND CT7_LP <> 'Z' "
Endif

cQuery += " AND CT7.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <> Len(aMeses)
cQuery += ", "
EndIf
Next

Else

For nColunas := 1 to Len(aMeses)
cQuery += " (SELECT SUM(CT7_CREDIT) - SUM(CT7_DEBITO) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "
If lAcum //.and. nColunas == 1/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO
cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else /// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)
cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
Endif
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery += " AND CT7_LP <> 'Z' "
Endif
cQuery += " AND CT7.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <> Len(aMeses)
cQuery += ", "
EndIf
Next

Endif


Elseif nOpc == 2 /// Trecho para o passo "considera valor zerado"


cQuery += U_VlrZero(lImpAntLP,dDataLP,aMeses,lAcum,cMoeda,cTpSaldo)

Endif

Return cQuery


User Function VlrZero(lImpAntLP,dDataLP,aMeses,lAcum,cMoeda,cTpSaldo)

Local cQuery := ''
Local nColunas := 0
Local lREL12Mes := ((Alltrim(FunName()) == 'CTBR265') .Or. (Alltrim(FunName()) == 'ABCTR265'))
Local lRel6Mes := ((Alltrim(FunName()) == 'CTBR260') .Or. (Alltrim(FunName()) == 'ABCTR260'))
Local nMes := 0

If lREL12Mes .And. !lImpAntLP // só para comparativo 12 meses

cQuery += " AND ( "
For nColunas := 1 to Len(aMeses)
cQuery += " (SELECT ROUND(SUM(CT7_CREDIT),2) - ROUND(SUM(CT7_DEBITO),2) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nColunas <> 12 /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nColunas == 12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND ( "
cQuery += " (CT7_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery += " OR "
cQuery += " (CT7_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP, "CT7_LP <> 'Z'", "") + " ) "
cQuery += " ) "

Else /// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If !lImpAntLP .and. nColunas == 12
cQuery += " AND CT7_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery += " AND CT7_LP <> 'Z' "
Endif
cQuery += " AND CT7.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <> Len(aMeses)
cQuery += " OR "
EndIf
Next
cQuery += " ) "

Elseif lRel6Mes .And. !lImpAntLP // só para comparativo 6 meses


For nColunas := 1 to Len(aMeses)
nMes := Month(aMeses[nColunas][3])

cQuery += " (SELECT SUM(CT7_CREDIT) - SUM(CT7_DEBITO) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nMes <> 12 /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nMes == 12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery += " AND ( "
cQuery += " (CT7_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery += " OR "
cQuery += " (CT7_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP, "CT7_LP <> 'Z'", "") + " ) "
cQuery += " ) "

Else /// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If !lImpAntLP .and. nColunas == 12
cQuery += " AND CT7_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery += " AND CT7_LP <> 'Z' "
Endif

cQuery += " AND CT7.D_E_L_E_T_ = ' ') <> 0 "

If nColunas <> Len(aMeses)
cQuery += " OR "
EndIf
Next


cQuery += " AND ( "
For nColunas := 1 to Len(aMeses)
cQuery += " (SELECT ROUND(SUM(CT7_CREDIT),2) - ROUND(SUM(CT7_DEBITO),2) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "
If lAcum
cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else
cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
EndIf
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery += " AND CT7_LP <> 'Z' "
Endif
cQuery += " AND CT7.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <> Len(aMeses)
cQuery += " OR "
EndIf
Next
cQuery += " ) "

Else

cQuery += " AND ( "
For nColunas := 1 to Len(aMeses)
cQuery += " (SELECT ROUND(SUM(CT7_CREDIT),2) - ROUND(SUM(CT7_DEBITO),2) "
cQuery += " FROM "+RetSqlName("CT7")+" CT7 "
cQuery += " WHERE CT7.CT7_FILIAL = '"+xFilial("CT7")+"' "
cQuery += " AND ARQ.CT1_CONTA = CT7_CONTA "
cQuery += " AND CT7_MOEDA = '"+cMoeda+"' "
cQuery += " AND CT7_TPSALD = '"+cTpSaldo+"' "
If lAcum
cQuery += " AND CT7_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else
cQuery += " AND CT7_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
EndIf
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery += " AND CT7_LP <> 'Z' "
Endif
cQuery += " AND CT7.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <> Len(aMeses)
cQuery += " OR "

EndIf

Next
cQuery += " ) "

Endif

Return cQuery


//-----------------------------------------------------------------------------------------------------------------------------------------//

Exemplo Protheus Versao 12

//-----------------------------------------------------------------------------------------------------------------------------------------//


#include'Protheus.ch'
#include'rwmake.ch'



UserFunctionCTBSldLP()

Local lImpAntLP := PARAMIXB[1]
Local dDataLP := PARAMIXB[2]
Local aMeses := PARAMIXB[3]
Local lAcum := PARAMIXB[4]
Local cMoeda := PARAMIXB[5]
Local cTpSaldo := PARAMIXB[6]
Local nOpc := PARAMIXB[7]

Local cQuery :=''
Local nColunas :=0
Local lREL12Mes := ((Alltrim(FunName()) =='CTBR265') .Or. (Alltrim(FunName()) =='ABCTR265'))
Local lRel6Mes := ((Alltrim(FunName()) =='CTBR260') .Or. (Alltrim(FunName()) =='ABCTR260'))
Local nMes :=0

If nOpc ==1
If lREL12Mes .And.!lImpAntLP // só para comparativo 12 meses

For nColunas :=1toLen(aMeses)
cQuery +=" (SELECT SUM(CQ1_CREDIT) - SUM(CQ1_DEBITO) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nColunas <>12/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nColunas ==12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND ( "
cQuery +=" (CQ1_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery +=" OR "
cQuery +=" (CQ1_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP,"CQ1_LP <> 'Z'","") +" ) "
cQuery +=" ) "

Else/// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If!lImpAntLP .and. nColunas ==12
cQuery +=" AND CQ1_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery +=" AND CQ1_LP <> 'Z' "
Endif

cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <>Len(aMeses)
cQuery +=", "
EndIf
Next

Elseif lRel6Mes .And.!lImpAntLP // só para comparativo 6 meses

For nColunas :=1toLen(aMeses)
nMes :=Month(aMeses[nColunas][3])

cQuery +=" (SELECT SUM(CQ1_CREDIT) - SUM(CQ1_DEBITO) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nMes <>12/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nMes ==12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND ( "
cQuery +=" (CQ1_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery +=" OR "
cQuery +=" (CQ1_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP,"CQ1_LP <> 'Z'","") +" ) "
cQuery +=" ) "

Else/// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If!lImpAntLP .and. nColunas ==12
cQuery +=" AND CQ1_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery +=" AND CQ1_LP <> 'Z' "
Endif

cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <>Len(aMeses)
cQuery +=", "
EndIf
Next

Else

For nColunas :=1toLen(aMeses)
cQuery +=" (SELECT SUM(CQ1_CREDIT) - SUM(CQ1_DEBITO) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "
If lAcum //.and. nColunas == 1/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO
cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else/// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)
cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
Endif
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery +=" AND CQ1_LP <> 'Z' "
Endif
cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') COLUNA"+Str(nColunas,Iif(nColunas>9,2,1))+" "

If nColunas <>Len(aMeses)
cQuery +=", "
EndIf
Next

Endif

Elseif nOpc ==2/// Trecho para o passo "considera valor zerado"


cQuery +=U_VlrZero(lImpAntLP,dDataLP,aMeses,lAcum,cMoeda,cTpSaldo)

Endif

Return cQuery


UserFunctionVlrZero(lImpAntLP,dDataLP,aMeses,lAcum,cMoeda,cTpSaldo)

Local cQuery :=''
Local nColunas :=0
Local lREL12Mes := ((Alltrim(FunName()) =='CTBR265') .Or. (Alltrim(FunName()) =='ABCTR265'))
Local lRel6Mes := ((Alltrim(FunName()) =='CTBR260') .Or. (Alltrim(FunName()) =='ABCTR260'))
Local nMes :=0

If lREL12Mes .And.!lImpAntLP // só para comparativo 12 meses

cQuery +=" AND ( "
For nColunas :=1toLen(aMeses)
cQuery +=" (SELECT ROUND(SUM(CQ1_CREDIT),2) - ROUND(SUM(CQ1_DEBITO),2) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nColunas <>12/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nColunas ==12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND ( "
cQuery +=" (CQ1_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery +=" OR "
cQuery +=" (CQ1_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP,"CQ1_LP <> 'Z'","") +" ) "
cQuery +=" ) "

Else/// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If!lImpAntLP .and. nColunas ==12
cQuery +=" AND CQ1_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery +=" AND CQ1_LP <> 'Z' "
Endif
cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <>Len(aMeses)
cQuery +=" OR "
EndIf
Next
cQuery +=" ) "

Elseif lRel6Mes .And.!lImpAntLP // só para comparativo 6 meses


For nColunas :=1toLen(aMeses)
nMes :=Month(aMeses[nColunas][3])

cQuery +=" (SELECT SUM(CQ1_CREDIT) - SUM(CQ1_DEBITO) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "

If lAcum .and. nMes <>12/// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "

ElseIf lAcum .and. (nMes ==12) /// SE FOR ACUMULADO, A PRIMEIRA COLUNA TERA O SALDO ATE O FINAL DO PERIODO

cQuery +=" AND ( "
cQuery +=" (CQ1_DATA <= '"+DTOS(aMeses[nColunas][3]-1)+"') "
cQuery +=" OR "
cQuery +=" (CQ1_DATA = '"+DTOS(aMeses[nColunas][3])+"' AND "+IIF(!lImpAntLP,"CQ1_LP <> 'Z'","") +" ) "
cQuery +=" ) "

Else/// AS DEMAIS COLUNAS SEMPRE SOMAM O MOVIMENTO NO PERIODO. (CALCULO NO RELATORIO)

cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "

If!lImpAntLP .and. nColunas ==12
cQuery +=" AND CQ1_LP <> 'Z' "
EndIf

Endif

If lImpAntLP .and. (dDataLP >= aMeses[nColunas][2])
cQuery +=" AND CQ1_LP <> 'Z' "
Endif

cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') <> 0 "

If nColunas <>Len(aMeses)
cQuery +=" OR "
EndIf
Next

cQuery +=" AND ( "
For nColunas :=1toLen(aMeses)
cQuery +=" (SELECT ROUND(SUM(CQ1_CREDIT),2) - ROUND(SUM(CQ1_DEBITO),2) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "
If lAcum
cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else
cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
EndIf
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery +=" AND CQ1_LP <> 'Z' "
Endif
cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <>Len(aMeses)
cQuery +=" OR "
EndIf
Next
cQuery +=" ) "

Else

cQuery +=" AND ( "
For nColunas :=1toLen(aMeses)
cQuery +=" (SELECT ROUND(SUM(CQ1_CREDIT),2) - ROUND(SUM(CQ1_DEBITO),2) "
cQuery +=" FROM "+RetSqlName("CQ1")+" CQ1 "
cQuery +=" WHERE CQ1.CQ1_FILIAL = '"+xFilial("CQ1")+"' "
cQuery +=" AND ARQ.CT1_CONTA = CQ1_CONTA "
cQuery +=" AND CQ1_MOEDA = '"+cMoeda+"' "
cQuery +=" AND CQ1_TPSALD = '"+cTpSaldo+"' "
If lAcum
cQuery +=" AND CQ1_DATA <= '"+DTOS(aMeses[nColunas][3])+"' "
Else
cQuery +=" AND CQ1_DATA BETWEEN '"+DTOS(aMeses[nColunas][2])+"' AND '"+DTOS(aMeses[nColunas][3])+"' "
EndIf
If lImpAntLP .and. dDataLP >= aMeses[nColunas][2]
cQuery +=" AND CQ1_LP <> 'Z' "
Endif
cQuery +=" AND CQ1.D_E_L_E_T_ = ' ') <> 0 "
If nColunas <>Len(aMeses)
cQuery +=" OR "
EndIf
Next
cQuery +=" ) "
Endif

Return cQuery