Querying results using SQL or XAILER programming tips

Question

Querying results using SQL or XAILER programming tips

Navigation

#1 by (1 votes)

1

I have the following relationship between 3 tables:

'SELECT contareceber.codcontareceber, contareceber.codorganizacao, 
contareceber.dtdocumento, contareceber.dtvencimento, 
contareceber.valortotalbruto, contareceber.valortotalrecebido, 
contareceber.dtcancelamento, contareceberquitacao.dtquitacao 
FROM contareceber 
LEFT JOIN contareceberquitacaoitem ON 
contareceberquitacaoitem.codcontareceber = contareceber.codcontareceber 
LEFT JOIN contareceberquitacao ON 
contareceberquitacao.codcontareceberquitacao = 
contareceberquitacaoitem.codcontareceberquitacao 
WHERE contareceber.dtdocumento>='+ValToSQL(::oDataInicial:Value)+' and 
contareceber.dtdocumento<='+ValToSQL(::oDataFinal:Value)+' 
ORDER BY contareceber.codorganizacao'

Being this:: InitialDate: Value and :: oDataFinal: Value are the date values that the user will enter in my program, I just made a program to execute this SELECT and bring me the information which I need in a temporary table.

After I have this information I need to submit the following data: (either through another SQL command or some other way.)

TITLES
PAYMENT TITLES
OPEN TITLES
PAYMENTS
PAYMENTS UP TO 30 DAYS
PAYMENTS BETWEEN 30 AND 60 DAYS
PAYMENTS BETWEEN 60 AND 90 DAYS
PAYMENTS BETWEEN 90 AND 120 DAYS
PAYMENTS BETWEEN 120 AND 180 DAYS
PAYMENTS WITH MORE THAN 180 DAYS

Temporary is created this way:

DBCreate('TEMPORARIO.DBF',{{'CODRECEB'   ,'C',12,0},; //codigo do contas a receber
                           {'CODORG'     ,'C', 3,0},; //codigo da empresa
                           {'DTDOC'      ,'D', 8,0},; //data de emissao
                           {'DTVENC'     ,'D', 8,0},; //data de vencimento
                           {'VLRTOT'     ,'N',10,2},; //valor total bruto
                           {'VLRTREC'    ,'N',12,2},; //valor total recebido
                           {'DTCANC'     ,'D', 8,0},; //data cancelamento
                           {'DTQUIT'     ,'D', 8,0},; //data quitação
                           {'PRAZO'      ,'N',10,0},; //prazo em dias para quitar
                           {'DIASATEPAG' ,'N',10,0},; //qtd de dias até pagar
                           {'EXCEDENTE'  ,'N',10,0}}) //quantos dias passou do prazo

I have this information to compare and bring the results of the dates. About the tables I'm looking for, they have this structure:

CREATE TABLE CONTARECEBER (
CODCONTARECEBER          VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODORGANIZACAO           VARCHAR(3) COLLATE WIN_PTBR,
STTIPOCONTARECEBER       VARCHAR(1) COLLATE WIN_PTBR,
DTDOCUMENTO              DATE,
DTVENCIMENTO             DATE,
SEQUENCIALPARCELA        INTEGER,
STMANUALSISTEMA          VARCHAR(1) COLLATE WIN_PTBR,
NUMERODOCUMENTO          VARCHAR(20) COLLATE WIN_PTBR,
VALORTOTALBRUTO          NUMERIC(10,2),
VALORTOTALJUROS          NUMERIC(8,2),
VALORTOTALDESCONTO       NUMERIC(8,2),
VALORTOTALRECEBIDO       NUMERIC(12,2),
OBSERVACAO               VARCHAR(50) COLLATE WIN_PTBR,
CODVENDA                 VARCHAR(15) COLLATE WIN_PTBR,
CODCLIENTE               VARCHAR(10) COLLATE WIN_PTBR,
CPFCNPJ                  VARCHAR(14) COLLATE WIN_PTBR,
CODCHEQUE                VARCHAR(10) COLLATE WIN_PTBR,
CODCONVENIO              VARCHAR(8) COLLATE WIN_PTBR,
CODCARTAO                VARCHAR(6) COLLATE WIN_PTBR,
NUMEROTEF                VARCHAR(20) COLLATE WIN_PTBR,
CODUSUARIO               VARCHAR(8) COLLATE WIN_PTBR,
ST_EXPORT                VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO      DATE,
HRULTIMAATUALIZACAO      TIME,
STCANCELADA              VARCHAR(1) COLLATE WIN_PTBR,
CODUSUARIOCANCELAMENTO   VARCHAR(8) COLLATE WIN_PTBR,
DTCANCELAMENTO           DATE,
HRCANCELAMENTO           TIME,
STVENDAFINANCIADA        VARCHAR(1) COLLATE WIN_PTBR,
CODFORMAPAGTO            VARCHAR(2) COLLATE WIN_PTBR,
OBSDEVOLUCAOPRODUTO      BLOB SUB_TYPE 1 SEGMENT SIZE 80,
ORGORIGEMATUALIZACAO     VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM            BIGINT
);


CREATE TABLE CONTARECEBERQUITACAOITEM (
CODCONTARECEBERQUITACAO  VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODCONTARECEBER          VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
VALORJUROS               NUMERIC(8,2),
VALORRECEBIDO            NUMERIC(8,2),
VALORDESCONTO            NUMERIC(8,2),
ST_EXPORT                VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO      DATE,
HRULTIMAATUALIZACAO      TIME,
ORGORIGEMATUALIZACAO     VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM            BIGINT
);

CREATE TABLE CONTARECEBERQUITACAO (
CODCONTARECEBERQUITACAO       VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODUSUARIO                    VARCHAR(8) COLLATE WIN_PTBR,
CODORGANIZACAO                VARCHAR(3) COLLATE WIN_PTBR,
STTIPOQUITACAO                VARCHAR(1) COLLATE WIN_PTBR,
STFORMAPAGTO                  VARCHAR(1) COLLATE WIN_PTBR,
CODFUNCIONARIO                VARCHAR(7) COLLATE WIN_PTBR,
CODCLIENTE                    VARCHAR(10) COLLATE WIN_PTBR,
CODCONVENIO                   VARCHAR(8) COLLATE WIN_PTBR,
CODCARTAO                     VARCHAR(6) COLLATE WIN_PTBR,
DTQUITACAO                    DATE,
HRQUITACAO                    TIME,
STCOBRARJUROS                 VARCHAR(1) COLLATE WIN_PTBR,
PERCJUROS                     NUMERIC(5,2),
PERCMULTA                     NUMERIC(5,2),
STACERTOAUTOMATICO            VARCHAR(1) COLLATE WIN_PTBR,
STATUALIZARPRECOATUAL         VARCHAR(1) COLLATE WIN_PTBR,
VALORTOTAL                    NUMERIC(10,2),
VALORDESCONTO                 NUMERIC(10,2),
VALORTOTALJUROS               NUMERIC(10,2),
STCANCELADA                   VARCHAR(1) COLLATE WIN_PTBR,
DTCANCELAMENTO                DATE,
HRCANCELAMENTO                TIME,
CODUSUARIOCANCELAMENTO        VARCHAR(8) COLLATE WIN_PTBR,
CODLANCAMENTOCONTAFINANCEIRA  VARCHAR(10) COLLATE WIN_PTBR,
ST_EXPORT                     VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO           DATE,
HRULTIMAATUALIZACAO           TIME,
STCHEQUETERCEIROS             VARCHAR(1) COLLATE WIN_PTBR,
OBSCANCELAMENTO               BLOB SUB_TYPE 1 SEGMENT SIZE 80,
NR_ACERTO                     VARCHAR(8) COLLATE WIN_PTBR,
CODECF                        VARCHAR(8) COLLATE WIN_PTBR,
NUMEROCOO                     VARCHAR(6) COLLATE WIN_PTBR,
NUMEROGNF                     VARCHAR(6) COLLATE WIN_PTBR,
CODSESSAOECFTEF               VARCHAR(15) COLLATE WIN_PTBR,
STCUPOMECFCANCELADO           VARCHAR(1) COLLATE WIN_PTBR,
ORGORIGEMATUALIZACAO          VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM                 BIGINT
);

sql clipper firebird

asked by anonymous 08.06.2017 / 18:30

1 answer

How to implement the Parcelable class? C ++: Instances of the same class

score 1 · Accepted Answer

Well I'll do a translation here of what you said you need:

TITLES - All the titles, within the filter you have already determined.
PAYMENT TITLES - All titles, within the filter, and that have the DTQUITATION other than null
TITLES IN OPEN - All titles would be inside the filter, and with DTQUITATION with null value
PAYMENTS - It would be all the titles, inside the filter, and with the DTQUITACAO less than or equal to DTVENCIMENTO.
PAYMENTS UNTIL ... BETWEEN ... WITH MORE - It would be all the titles, inside the filter, that have the DIFFICULTY different from null and with the amount of days within a period referring to DTVENCIMENTO. (This was not very clear if that's what you'd like, correct me if I'm wrong.)

In front of this the respective SQLs within what you could get from your database would be:

TITLES:

SELECT CONTARECEBER.CODCONTARECEBER,
       CONTARECEBER.CODORGANIZACAO,
       CONTARECEBER.DTDOCUMENTO,
       CONTARECEBER.DTVENCIMENTO,
       CONTARECEBER.VALORTOTALBRUTO,
       CONTARECEBER.VALORTOTALRECEBIDO,
       CONTARECEBER.DTCANCELAMENTO,
       CONTARECEBERQUITACAO.DTQUITACAO
FROM CONTARECEBER
LEFT OUTER JOIN CONTARECEBERQUITACAOITEM ON
       CONTARECEBER.CODCONTARECEBER = CONTARECEBERQUITACAOITEM.CODCONTARECEBER
LEFT OUTER JOIN CONTARECEBERQUITACAO ON
       CONTARECEBERQUITACAOITEM.CODCONTARECEBERQUITACAO = CONTARECEBERQUITACAO.CODCONTARECEBERQUITACAO
WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
ORDER BY CONTARECEBER.CODORGANIZACAO

The others will change only the WHERE to simplify.

TITLES PAID:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NOT NULL

OPEN TITLES:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NULL

PAYMENTS IN DAY:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO <= CONTARECEBER.DTVENCIMENTO

PAYMENTS UNTIL ... BETWEEN ... WITH MORE

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NOT NULL 
AND CONTARECEBER.DTVENCIMENTO BETWEEN '2017-06-08' AND '2017-07-08'

Being that last example searching today for up to 30 days forward, simply adapt it to the situations you quoted. This is clear if I have understood this your last need.