/*------ 26/11/2016 01:32:45 --------*/
UPDATE CONFIGURACOES SET COMPILACAO='1.3.6.8';


/*------ 14/01/2017 01:23:08 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'UNIDADEMEDIDA') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 14/01/2017 01:23:08 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'UNIDADEMEDIDA') AND 
(RDB$FIELD_NAME= 'SIGLA');


/*------ 14/01/2017 01:23:08 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'UNIDADEMEDIDA') AND 
(RDB$FIELD_NAME= 'EQUIVALENCIA');


/*------ 14/01/2017 01:23:08 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 4
WHERE (RDB$RELATION_NAME = 'UNIDADEMEDIDA') AND 
(RDB$FIELD_NAME= 'PADRAO');


/*------ 14/01/2017 01:23:08 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 5
WHERE (RDB$RELATION_NAME = 'UNIDADEMEDIDA') AND 
(RDB$FIELD_NAME= 'CODFAMILIA');


/*------ 14/01/2017 01:23:23 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'CIDADES') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 14/01/2017 01:23:23 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'CIDADES') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 14/01/2017 01:23:23 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'CIDADES') AND 
(RDB$FIELD_NAME= 'CODUF');


/*------ 14/01/2017 01:30:00 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'BANCOS') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 14/01/2017 01:30:00 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'BANCOS') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 24/01/2017 18:06:44 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'FABRICANTE') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:06:44 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'FABRICANTE') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 24/01/2017 18:06:55 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 0
WHERE (RDB$RELATION_NAME = 'GRUPO') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 24/01/2017 18:06:55 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'GRUPO') AND 
(RDB$FIELD_NAME= 'CODGRUPO');


/*------ 24/01/2017 18:08:16 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'CARGOS') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:08:16 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'CARGOS') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 24/01/2017 18:09:49 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'TABNCM') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:09:49 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'TABNCM') AND 
(RDB$FIELD_NAME= 'NCM');


/*------ 24/01/2017 18:10:52 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'TABCEST') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:10:52 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'TABCEST') AND 
(RDB$FIELD_NAME= 'CEST');


/*------ 24/01/2017 18:10:52 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'TABCEST') AND 
(RDB$FIELD_NAME= 'NCM');


/*------ 24/01/2017 18:11:19 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'TIPOMOVCAIXA') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:11:19 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'TIPOMOVCAIXA') AND 
(RDB$FIELD_NAME= 'CODCONFIGURACOES');


/*------ 24/01/2017 18:11:19 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'TIPOMOVCAIXA') AND 
(RDB$FIELD_NAME= 'TIPO');


/*------ 24/01/2017 18:11:31 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'TIPOMOVIMENTO') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 24/01/2017 18:11:31 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'TIPOMOVIMENTO') AND 
(RDB$FIELD_NAME= 'CODTIPOCONTA');


/*------ 24/01/2017 18:11:31 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 3
WHERE (RDB$RELATION_NAME = 'TIPOMOVIMENTO') AND 
(RDB$FIELD_NAME= 'CODSUBGRUPOMOV');


/*------ 24/01/2017 18:11:31 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 4
WHERE (RDB$RELATION_NAME = 'TIPOMOVIMENTO') AND 
(RDB$FIELD_NAME= 'CODGRUPOMOV');


/*------ 25/01/2017 13:01:09 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 0
WHERE (RDB$RELATION_NAME = 'GRUPO') AND 
(RDB$FIELD_NAME= 'CODGRUPO');


/*------ 25/01/2017 13:01:09 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 1
WHERE (RDB$RELATION_NAME = 'GRUPO') AND 
(RDB$FIELD_NAME= 'DESCRICAO');


/*------ 25/01/2017 13:01:09 --------*/

UPDATE RDB$RELATION_FIELDS SET RDB$FIELD_POSITION = 2
WHERE (RDB$RELATION_NAME = 'GRUPO') AND 
(RDB$FIELD_NAME= 'CODEMPRESA');


/*------ 25/01/2017 18:10:04 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPCONTASRECEBER
RETURNS (
    CODMOVIMENTO INTEGER,
    CODFILIAL INTEGER,
    CODFUNCIONARIO INTEGER,
    DATA DATE,
    HORA TIME,
    TIPO VARCHAR(2),
    CODPLANOPGTO INTEGER,
    CODPARCELA INTEGER,
    NRPARCELA INTEGER,
    DOCUMENTO VARCHAR(20),
    PAGO CHAR(1),
    CODCLIENTE INTEGER,
    CLIENTE VARCHAR(70),
    PLANOCONTAS VARCHAR(40),
    VALORPARCELA FLOAT,
    VENCIMENTO DATE,
    VALORPAGO FLOAT,
    RESTANTE FLOAT,
    RESTANTECOMJUROS FLOAT,
    DIASDEATRAZO INTEGER,
    NRDOCUMENTO VARCHAR(20),
    OBS BLOB sub_type 0 segment size 80,
    STATUS CHAR(1),
    SPC CHAR(1),
    CODSITUACAO INTEGER,
    SITUACAO VARCHAR(50),
    STATUSCLIENTE CHAR(1),
    NRBOLETO VARCHAR(30),
    DEPENDENTE VARCHAR(70),
    FILIAL VARCHAR(40),
    EXTENSO VARCHAR(250),
    OBSPARCELA VARCHAR(50),
    NRNFE VARCHAR(20),
    PLANOPGTO VARCHAR(40),
    TIPODOC VARCHAR(20),
    CODEMPRESA INTEGER)
AS
  declare variable PARCELA        INTEGER;
  declare variable DIASATRAZO     INTEGER;
  declare variable CREDITO        FLOAT;
  declare variable VALOR          FLOAT;
  declare variable CARENCIA       INTEGER;
  declare variable JUROS          FLOAT;
  declare variable JUROSCALCULO   FLOAT;
  declare variable TIPOJUROS      CHAR(1);
  declare variable JUROSSOBRE     CHAR(1);
  declare variable DIAS           INTEGER;
  declare variable VENC           DATE;
  declare variable HOJE           DATE;
  declare variable VALORRESTANTE  FLOAT;
  declare variable TCODPARCELA    INTEGER;
  declare variable CODIGOCONTABIL VARCHAR(20);
  declare variable CODCONTA       INTEGER;
begin
    SELECT CF.CARENCIAJUROSPORATRAZO, CF.JUROSPORATRAZO, CF.TIPPOJUROSPORATRAZO,
    CF.JUROSSOBRE
    FROM CONFIGURACOES CF
    INTO  :CARENCIA, :JUROS, :TIPOJUROS, :JUROSSOBRE;
        if (JUROS IS NULL) then
            JUROS = 0;
        if (CARENCIA IS NULL) then
            CARENCIA = 0;

 FOR SELECT
    PC.CODPARCELA, PC.CODPARCELA, PC.CODPARCELA, PC.NRPARCELA, PC.VALORPARCELA, PC.VENCIMENTO,
    PC.DOCUMENTO, PC.PAGO,  PC.STATUS, PC.CODSITUACAO, PC.CODCONTA, PC.OBSPARCELA, CPR.CODPLANOPGTO, CPR.CODMOVIMENTO
    FROM PARCELASCONTAS PC
      JOIN CONTASPAGARRECEBER CPR ON (CPR.CODCONTA=PC.CODCONTA)
      WHERE PC.PAGO='N' AND CPR.TIPO IN ('VE', 'RE','S')
       INTO :TCODPARCELA, :PARCELA, :CODPARCELA, :NRPARCELA, :VALOR, :VENC,
       :DOCUMENTO, :PAGO, :STATUS, :CODSITUACAO, :CODCONTA, :OBSPARCELA, :CODPLANOPGTO, :CODMOVIMENTO DO
       BEGIN

            /*SELECT CPR.CODPLANOPGTO, CPR.CODMOVIMENTO FROM CONTASPAGARRECEBER CPR
            WHERE CPR.CODCONTA=:CODCONTA
            INTO :CODPLANOPGTO, :CODMOVIMENTO;  */

            SELECT M.CODFILIAL, M.DATA, M.HORA, M.TIPO, M.CODIGOCONTABIL, M.CODFUNCIONARIO, M.CODEMPRESA
            FROM MOVIMENTOSPRODUTOS M
            WHERE M.CODMOVIMENTO=:CODMOVIMENTO
            INTO :CODFILIAL, :DATA, :HORA, :TIPO, :CODIGOCONTABIL, :CODFUNCIONARIO, :CODEMPRESA;

            NRDOCUMENTO = NULL;
            TIPODOC = NULL;
            SELECT DM.TIPODOC, DM.NRDOCUMENTO
            FROM DOCUMENTOMOV DM
            WHERE DM.CODMOVIMENTO=:CODMOVIMENTO
            INTO :TIPODOC, :NRDOCUMENTO;

            NRNFE = NULL;
            SELECT N.NRDOCUMENTO FROM NFEMOV N
            WHERE N.CODMOVIMENTO=:CODMOVIMENTO
            INTO :NRNFE;

            CODCLIENTE = NULL;

            SELECT CM.CODCLIENTE FROM CLIENTEMOVIMENTO CM
            WHERE CM.CODMOVIMENTO=:CODMOVIMENTO
            INTO :CODCLIENTE;

            SELECT PL.DESCRICAO  FROM PLANOSDECONTAS  PL
            WHERE PL.CODIGOCONTABIL=:CODIGOCONTABIL
            INTO :PLANOCONTAS;

            NRBOLETO=NULL;
            SELECT BP.NRBOLETO FROM BOLETOPARCELA BP
            WHERE BP.CODPARCELA=:TCODPARCELA
            INTO :NRBOLETO;

            PLANOPGTO='';
            SELECT PG.DESCRICAO FROM PLANOSPAGAMENTO PG
            WHERE PG.CODPLANOPGTO=:CODPLANOPGTO
            INTO :PLANOPGTO;

            SELECT CAST(C.NOME AS VARCHAR(70)), C.SPC, C.STATUS
            FROM CLIENTES C
            WHERE C.CODCLIENTE=:CODCLIENTE
            INTO :CLIENTE, :SPC, :STATUSCLIENTE;

            SITUACAO = NULL;
            SELECT TS.DESCRICAO FROM TABELASSIMPLES TS
            WHERE TS.CODTABELASIMPLES=:CODSITUACAO
            INTO :SITUACAO;

            OBS = NULL;
            SELECT OB.OBS FROM OBSPARCELASCONTAS OB
            WHERE OB.CODPARCELA=:TCODPARCELA
            INTO :OBS;

            DEPENDENTE = NULL;
            SELECT DC.NOME FROM DEPENDENTECLIMOV DCM
            JOIN DEPENDENTESCLI DC ON (DC.CODDEPENDENTE = DCM.CODDEPENDENTE)
            WHERE DCM.CODMOVIMENTO=:CODMOVIMENTO
            INTO :DEPENDENTE;

            SELECT FI.NOME FROM FILIAIS FI
            WHERE FI.CODFILIAL=:CODFILIAL
            INTO :FILIAL;

           SELECT SUM(PG.VALOR) FROM  PARCELASPGTO  PG
           WHERE PG.CODPARCELA      = :PARCELA
           INTO :CREDITO;
             if (CREDITO IS NULL ) then
                 CREDITO = 0;
             CODPARCELA     = PARCELA;
             VALORPAGO      = CREDITO;
             VALORPARCELA   = VALOR;
             VENCIMENTO     = VENC;
             RESTANTE       = VALOR - CREDITO;
             VALORRESTANTE  = VALOR - CREDITO;
             HOJE           = CURRENT_DATE;
             DIASATRAZO     = HOJE - VENC;
             DIASDEATRAZO   = DIASATRAZO;

             IF (NOT TIPOJUROS IS NULL) then
             BEGIN
               IF (DIASATRAZO>=CARENCIA) then
               BEGIN
                 IF (TIPOJUROS ='M') then
                 BEGIN
                  JUROSCALCULO     = (JUROS / 30) * DIASATRAZO;

                  IF (JUROSSOBRE = 'P') then
                     RESTANTECOMJUROS = (VALORPARCELA + ((VALORPARCELA * JUROSCALCULO)/100)) - CREDITO;
                  ELSE
                    RESTANTECOMJUROS = ((VALORPARCELA  - CREDITO ) + (((VALORPARCELA  - CREDITO)* JUROSCALCULO)/100));

                 END
                 IF (TIPOJUROS ='D') then
                 BEGIN
                    JUROSCALCULO     = JUROS * DIASATRAZO;
                    IF (JUROSSOBRE = 'P') then
                        RESTANTECOMJUROS = (VALORPARCELA + ((VALORPARCELA * JUROSCALCULO)/100)) - CREDITO;
                    ELSE
                       RESTANTECOMJUROS = ((VALORPARCELA - CREDITO )+ (((VALORPARCELA - CREDITO)* JUROSCALCULO)/100));
                 END
               END ELSE
                   BEGIN
                      RESTANTECOMJUROS = VALORPARCELA - CREDITO;
                   END

             END ELSE
                  BEGIN
                   RESTANTECOMJUROS = VALORPARCELA - CREDITO;
                  END

                  IF (DIASATRAZO < 0) then
                  BEGIN
                    DIASATRAZO = 0;
                    DIASDEATRAZO   = DIASATRAZO;
                  END
             EXTENSO=NULL;
             if (RESTANTECOMJUROS>0) then
             begin
               SELECT VALOREXTENSO FROM EXTENSO(:RESTANTECOMJUROS,'S')
               INTO :EXTENSO;
             end

           suspend;
       END
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE RELFLUXODECAIXA (
    DATAI DATE,
    DATAF DATE)
RETURNS (
    DATA DATE,
    CONTASPAGAR FLOAT,
    CHEQUESPAGAR FLOAT,
    CONTASRECEBER FLOAT,
    CHEQUESRECEBER FLOAT,
    TOTAL FLOAT)
AS
declare variable I INTEGER;
declare variable DIAS INTEGER;
begin
    DATA = DATAI;
    DIAS = DATAF-DATAI;
    I = 1;
    WHILE (I <= (DIAS+1))  DO
    BEGIN
        CONTASPAGAR =0;
        CHEQUESPAGAR=0;
        CONTASRECEBER =0;
        CHEQUESRECEBER =0;
        TOTAL =0;
        SELECT PC.VENCIMENTO, SUM(PC.RESTANTECOMJUROS) AS CONTASARECEBER
        FROM SPCONTASRECEBER PC
        WHERE PC.PAGO ='N'
        AND (PC.STATUS='A' OR PC.STATUS IS NULL)
        AND PC.VENCIMENTO=:DATA
        GROUP BY VENCIMENTO
        INTO :DATA, :CONTASRECEBER;
        SELECT CP.VENCIMENTO, SUM(CP.RESTANTE) AS CONTASAPAGAR
        FROM SPCONTASPAGAR CP
        WHERE CP.PAGO ='N'
        AND CP.VENCIMENTO=:DATA
        GROUP BY CP.VENCIMENTO
        INTO :DATA, :CONTASPAGAR;
        SELECT SP.VENCIMENTO, SUM(SP.VALOR) FROM SPCHEQUESPAGAR SP
        WHERE (NOT SP.TIPO IS NULL)
        AND SP.VENCIMENTO=:DATA
        GROUP BY VENCIMENTO
        INTO :DATA, :CHEQUESPAGAR;
        SELECT SP.VENCIMENTO, SUM(SP.VALOR) FROM SPCHEQUESRECEBER SP
        WHERE (NOT SP.TIPO IS NULL)
        AND SP.VENCIMENTO=:DATA
        GROUP BY VENCIMENTO
        INTO :DATA, :CHEQUESRECEBER;
        TOTAL = (CONTASRECEBER + CHEQUESRECEBER) - (CONTASPAGAR + CHEQUESPAGAR);
        suspend;
        DATA = DATA + 1;
        I = I + 1;
    END
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPHISTORICO (
    CODCLIENTE INTEGER)
RETURNS (
    MENORCOMPRA FLOAT,
    MAIORCOMPRA FLOAT,
    MEDIACOMPRA FLOAT,
    PRIMEIRACOMPRA DATE,
    ULTIMACOMPRA DATE,
    DIASUMLTIMACOMPRA INTEGER,
    NRCOMPRA INTEGER,
    PAGASATRAZADAS INTEGER,
    ATRAZOMIN INTEGER,
    ATRAZOMAX INTEGER,
    MEDIAATRAZO INTEGER,
    PAGASADIANTADAS INTEGER,
    ADIANTOMIN INTEGER,
    ADIANTOMAX INTEGER,
    MEDIAADIANTO INTEGER,
    ATRAZADAS INTEGER,
    ATRAZADAMIN INTEGER,
    ATRAZADAMAX INTEGER,
    DEBITO FLOAT)
AS
declare variable CARENCIA INTEGER;
begin
    SELECT CF.CARENCIAJUROSPORATRAZO FROM CONFIGURACOES CF
    INTO :CARENCIA;
    if (CARENCIA IS NULL) then
    CARENCIA =1;

SELECT MIN(M.VALORTOTAL) AS MENORCOMPRA, MAX(M.VALORTOTAL) AS MAIORCOMPRA,
AVG(M.VALORTOTAL) AS MEDIACOMPRA, MIN(M.DATA) AS PRIMEIRACOMPRA, MAX(M.DATA) AS ULTIMACOMPRA,  CURRENT_DATE - MAX(M.DATA) AS DIAS, COUNT(CM.CODCLIENTE) AS NRCOMPRA FROM CLIENTEMOVIMENTO CM
JOIN MOVIMENTOSPRODUTOS M ON (M.CODMOVIMENTO = CM.CODMOVIMENTO)
WHERE CM.CODCLIENTE=:CODCLIENTE AND M.TIPO IN ('VE','RE')
INTO :MENORCOMPRA, :MAIORCOMPRA, :MEDIACOMPRA, :PRIMEIRACOMPRA, :ULTIMACOMPRA,
     :DIASUMLTIMACOMPRA , :NRCOMPRA;

SELECT COUNT(PC.CODPARCELA) AS PAGASATRAZADAS, MIN(PG.DATA - PC.VENCIMENTO) ATRAZOMIN, MAX(PG.DATA - PC.VENCIMENTO ) AS ATRAZOMAX,  AVG(PG.DATA - PC.VENCIMENTO) AS MEDIAATRAZO  FROM CLIENTEMOVIMENTO CM
JOIN CONTASPAGARRECEBER CPR ON (CPR.CODMOVIMENTO = CM.CODMOVIMENTO)
JOIN PARCELASCONTAS     PC  ON (PC.CODCONTA      = CPR.CODCONTA)
JOIN PARCELASPGTO       PG  ON (PG.CODPARCELA    = PC.CODPARCELA)
WHERE CM.CODCLIENTE=:CODCLIENTE AND  PC.PAGO='S' AND PG.DATA>(PC.VENCIMENTO + :CARENCIA)
INTO :PAGASATRAZADAS, :ATRAZOMIN, :ATRAZOMAX, :MEDIAATRAZO;

SELECT COUNT(PC.CODPARCELA) AS PAGASADIANTADAS, MIN(PC.VENCIMENTO - PG.DATA) ADIANTOMIN, MAX(PC.VENCIMENTO - PG.DATA) AS ADIANTOMAX,  AVG(PC.VENCIMENTO - PG.DATA) AS MEDIAADIANTO  FROM CLIENTEMOVIMENTO CM
JOIN CONTASPAGARRECEBER CPR ON (CPR.CODMOVIMENTO = CM.CODMOVIMENTO)
JOIN PARCELASCONTAS     PC  ON (PC.CODCONTA      = CPR.CODCONTA)
JOIN PARCELASPGTO       PG  ON (PG.CODPARCELA    = PC.CODPARCELA)
WHERE CM.CODCLIENTE=:CODCLIENTE AND  PC.PAGO='S' AND PG.DATA<PC.VENCIMENTO
INTO :PAGASADIANTADAS, :ADIANTOMIN, :ADIANTOMAX, :MEDIAADIANTO;


SELECT COUNT(PC.CODPARCELA) AS ATRAZADAS, MIN(CURRENT_DATE - PC.VENCIMENTO) ATRAZADAMIN, MAX(CURRENT_DATE - PC.VENCIMENTO) AS ATRAZADAMAX FROM CLIENTEMOVIMENTO CM
JOIN CONTASPAGARRECEBER CPR ON (CPR.CODMOVIMENTO = CM.CODMOVIMENTO)
JOIN PARCELASCONTAS     PC  ON (PC.CODCONTA      = CPR.CODCONTA)
WHERE CM.CODCLIENTE=:CODCLIENTE AND  PC.PAGO='N'   AND PC.VENCIMENTO <CURRENT_DATE
INTO :ATRAZADAS, :ATRAZADAMIN, :ATRAZADAMAX;

SELECT SUM(RESTANTECOMJUROS) FROM SPCONTASRECEBER
WHERE CODCLIENTE=:CODCLIENTE
AND PAGO='N'
INTO :DEBITO;


  suspend;
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPLEMBRETES
RETURNS (
    DESCRICAO VARCHAR(40),
    TIPO VARCHAR(5),
    TOTAL NUMERIC(10,5))
AS
    declare variable TTOTAL NUMERIC(10,5);
begin
    SELECT CAST('Necessidade de Compra' as VARCHAR(40)), CAST('Qtd' as VARCHAR(5)), COUNT(*) FROM SPPESQPRODUTOS E
    WHERE (E.ESTOQUE<=E.ESTOQUEMINIMO or (E.ESTOQUE IS NULL))
    AND E.CODIGO=E.CODPRODUTO
    INTO :DESCRICAO, :TIPO, :TOTAL;
    suspend;

    /*SELECT  CAST('Contas a Receber' as VARCHAR(40)), CAST('R$' as VARCHAR(5)), SP.TOTAL
     FROM  SPSOMACONTASRECEBER SP
    INTO :DESCRICAO, :TIPO, :TOTAL;
    IF (TOTAL IS NULL) then
    BEGIN
       DESCRICAO = 'Contas a Receber';
       TIPO      = 'R$';
       TOTAL     = 0;
    END
    suspend;*/

    SELECT SUM(CR.RESTANTECOMJUROS)
    FROM SPCONTASRECEBER CR
    WHERE CR.PAGO='N'  AND CR.TIPO IN ('VE',  'S' , 'RE' ) AND CR.VENCIMENTO<=current_date
    INTO :TTOTAL;
    IF (TTOTAL IS NULL) then
    BEGIN
       TTOTAL     = 0;
    END

    DESCRICAO = 'Contas a Receber';
    TIPO      = 'R$';
    TOTAL = TTOTAL;
    SUSPEND;

    SELECT CAST('Contas a Pagar' as VARCHAR(40)), CAST('R$' as VARCHAR(5)), SUM(P.RESTANTE)
     FROM SPCONTASPAGAR P
     WHERE P.PAGO='N' AND P.VENCIMENTO<=current_date
     INTO :DESCRICAO,:TIPO, :TOTAL;
     IF (TOTAL IS NULL) then
     BEGIN
       DESCRICAO = 'Contas a Pagar';
       TIPO      = 'R$';
       TOTAL     = 0;
     END
     suspend;
       /*
       SELECT CAST('Cheques a Receber' as VARCHAR(40)), CAST('R$' as VARCHAR(5)), SUM(C.VALOR)
       FROM SPCHEQUESRECEBER C
       GROUP BY C.CODCLIENTE, C.NOME, C.AGENCIA, C.BANCO, C.CODCHEQUE,
       C.CONTA, C.CPFCNPJ, C.DATAEMISSAO, C.NRCHEQUE, C.TIPO, C.VALOR, C.VENCIMENTO ,C.ALINEA
       INTO :DESCRICAO,:TIPO, :TOTAL;
       IF (TOTAL IS NULL) then
       BEGIN
         DESCRICAO = 'Cheques a Receber';
         TIPO      = 'R$';
         TOTAL     = 0;
       END
       suspend;
       /* cheques a Pagar
       SELECT CAST('Cheques a Pagar' as VARCHAR(40)), CAST('R$' as VARCHAR(5)), SUM(CP.VALOR)
       FROM SPCHEQUESPAGAR  CP
       GROUP BY CP.CODFORNECEDOR, CP.NOME, CP.AGENCIA, CP.BANCO, CP.CODCHEQUE,
       CP.CONTA, CP.CPFCNPJ, CP.DATAEMISSAO, CP.NRCHEQUE, CP.TIPO, CP.VALOR, CP.VENCIMENTO , CP.ALINEA
       INTO :DESCRICAO,:TIPO, :TOTAL;
       IF (TOTAL IS NULL) then
       BEGIN
         DESCRICAO = 'Cheques a Pagar';
         TIPO      = 'R$';
         TOTAL     = 0;
       END
       suspend;*/
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPNOTIFICACOES (
    DATAI DATE,
    DATAF DATE)
RETURNS (
    CONTASPAGAR INTEGER,
    CHEQUESPAGAR INTEGER,
    CONTASRECEBER INTEGER,
    CHEQUESRECEBER INTEGER)
AS
begin
        CONTASPAGAR =0;
        CHEQUESPAGAR=0;
        CONTASRECEBER =0;
        CHEQUESRECEBER =0;

        SELECT COUNT(*)
        FROM SPCONTASRECEBER PC
        WHERE PC.PAGO ='N'
        AND (PC.STATUS='A' OR PC.STATUS IS NULL)
        AND PC.VENCIMENTO>=:DATAI AND PC.VENCIMENTO<=:DATAF
        INTO :CONTASRECEBER;

        SELECT COUNT(*)
        FROM SPCONTASPAGAR CP
        WHERE CP.PAGO ='N'
        AND CP.VENCIMENTO>=:DATAI AND CP.VENCIMENTO<=:DATAF
        INTO :CONTASPAGAR;

        SELECT COUNT(*) FROM SPCHEQUESPAGAR SP
        WHERE SP.TIPO='P'
        AND SP.VENCIMENTO>=:DATAI AND SP.VENCIMENTO<=:DATAF
        INTO :CHEQUESPAGAR;

        SELECT COUNT(*) FROM SPCHEQUESRECEBER SP
        WHERE SP.TIPO='R' AND SP.VENCIMENTO>=:DATAI AND SP.VENCIMENTO<=:DATAF
        INTO :CHEQUESRECEBER;

        suspend;

end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPRESUMOCR (
    DATAI DATE,
    DATAF DATE)
RETURNS (
    CODFILIAL INTEGER,
    FILIAL VARCHAR(40),
    CONTASRECEBERNOPERIODO FLOAT,
    CONTASVENCIDAS FLOAT,
    CONTASRECEBERTOTAL FLOAT)
AS
begin
      FOR SELECT F.CODFILIAL, F.NOME FROM FILIAIS F
      INTO :CODFILIAL, :FILIAL
      DO
      BEGIN

      SELECT SUM(PC.RESTANTECOMJUROS) AS VALOR FROM SPCONTASRECEBER PC
      WHERE PC.PAGO ='N'
      AND PC.VENCIMENTO>=:DATAI AND  PC.VENCIMENTO<=:DATAF AND PC.CODFILIAL=:CODFILIAL
      INTO :CONTASRECEBERNOPERIODO;

      SELECT SUM(CPR.RESTANTECOMJUROS) AS Valor FROM SPCONTASRECEBER CPR
      WHERE CPR.PAGO ='N'
      AND CPR.VENCIMENTO<=CURRENT_DATE AND CPR.CODFILIAL=:CODFILIAL
      INTO :CONTASVENCIDAS;

      SELECT SUM(CPR.RESTANTECOMJUROS) AS Valor FROM SPCONTASRECEBER CPR
      WHERE CPR.PAGO ='N'
      AND CPR.CODFILIAL=:CODFILIAL
      INTO :CONTASRECEBERTOTAL;

      suspend;
      END
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPRESUMODEBITOCLIENTE (
    CODIGO INTEGER)
RETURNS (
    CODCLIENTE INTEGER,
    CLIENTE VARCHAR(70),
    TOTALDEPARCELAS FLOAT,
    TOTALPAGO FLOAT,
    RESTANTE FLOAT,
    RESTANTECOMJUROS FLOAT,
    VENCIDAS INTEGER,
    TOTALVENCIDAS FLOAT)
AS
begin

SELECT PC.CODCLIENTE, PC.CLIENTE, SUM(PC.VALORPARCELA) AS TOTALDEPARCELAS,
SUM(PC.VALORPAGO) AS TOTALPAGO, SUM(PC.RESTANTE) AS RESTANTE, SUM(PC.RESTANTECOMJUROS) AS RESTANTECOMJUROS

FROM SPCONTASRECEBER PC
WHERE PC.PAGO='N' AND PC.CODCLIENTE=:CODIGO
GROUP BY PC.CODCLIENTE, PC.CLIENTE
INTO :CODCLIENTE, :CLIENTE, :TOTALDEPARCELAS, :TOTALPAGO ,:RESTANTE ,:RESTANTECOMJUROS;

SELECT COUNT(*),SUM(PC.RESTANTECOMJUROS)

FROM SPCONTASRECEBER PC
WHERE PC.PAGO='N' AND PC.CODCLIENTE=:CODIGO
AND PC.VENCIMENTO<=CURRENT_DATE
INTO :VENCIDAS,:TOTALVENCIDAS;

suspend;
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPSITUACAOCLIENTE (
    CODCLIENTE INTEGER)
RETURNS (
    CODIGO INTEGER,
    NOME VARCHAR(70),
    LIMITE FLOAT,
    DEBITO FLOAT,
    SALDO FLOAT,
    VALORPAGO FLOAT,
    VENCIDAS INTEGER)
AS
begin
    SELECT C.LIMITE, C.CODCLIENTE, C.NOME
    FROM CLIENTES C
    WHERE C.CODCLIENTE=:CODCLIENTE
    INTO :LIMITE, :CODIGO, :NOME;

    SELECT SUM(SP.RESTANTECOMJUROS)
    FROM SPCONTASRECEBER SP
    WHERE SP.PAGO='N' AND SP.CODCLIENTE=:CODCLIENTE
    INTO :DEBITO;

    if (LIMITE    IS NULL) then LIMITE =0;
    if (DEBITO    IS NULL) then DEBITO =0;
    SALDO = LIMITE  - DEBITO;

    SELECT COUNT(PC.CODPARCELA)
    FROM PARCELASCONTAS     PC  
      JOIN CONTASPAGARRECEBER CPR ON (CPR.CODCONTA   = PC.CODCONTA)
      JOIN CLIENTEMOVIMENTO   CM  ON (CM.CODMOVIMENTO    = CPR.CODMOVIMENTO)
      WHERE CM.CODCLIENTE=:CODCLIENTE AND PC.PAGO='N'
      AND PC.VENCIMENTO<=CURRENT_DATE
      INTO :VENCIDAS;

  suspend;
end
^

SET TERM ; ^




/*------ 25/01/2017 18:10:10 --------*/

SET TERM ^ ;

ALTER PROCEDURE SPSOMACONTASRECEBER
RETURNS (
    TOTAL FLOAT)
AS
  declare variable VALORRECEBER  FLOAT;
  declare variable VALORRECEBIDO FLOAT;
begin
  SELECT SUM(CR.RESTANTECOMJUROS)
    FROM SPCONTASRECEBER CR
    WHERE CR.PAGO='N'  AND CR.TIPO IN ('VE',  'S' , 'RE' )
    INTO :VALORRECEBER;
    if (VALORRECEBER IS NULL) then
        VALORRECEBER =0;
    TOTAL = VALORRECEBER;
    suspend;
end
^

SET TERM ; ^




/*------ 17/02/2017 01:15:11 --------*/

ALTER TABLE CONTADOR
ADD EMAIL VARCHAR_100;


/*------ 17/02/2017 01:17:23 --------*/

ALTER TABLE CONTADOR
ADD SENHA VC500;


/*------ 17/02/2017 01:20:20 --------*/

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'VAR_CHAR10'
where (RDB$FIELD_NAME = 'PREFIXO') and
(RDB$RELATION_NAME = 'CONTATOSCLI')
;


/*------ 17/02/2017 01:20:32 --------*/

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'VAR_CHAR10'
where (RDB$FIELD_NAME = 'SUFIXO') and
(RDB$RELATION_NAME = 'CONTATOSCLI')
;


/*------ 19/02/2017 14:04:27 --------*/

alter table "CONTADOR" drop "CODEMPRESA";


/*------ 19/02/2017 14:05:08 --------*/

ALTER TABLE EMPRESA
ADD CODCONTADOR INTEIRO_NULL;


/*------ 02/03/2017 10:01:29 --------*/

SET TERM ^ ;

CREATE PROCEDURE SPPESQNF (
    DATAI DATE,
    DATAF DATE)
RETURNS (
    CODMOVIMENTO INTEGER,
    DATA DATE,
    NRDOCUMENTO VARCHAR(20),
    CFOP VARCHAR(40),
    TIPO CHAR(1),
    TIPOMOV VARCHAR(2),
    NATUREZA VARCHAR(10),
    TOTAL FLOAT,
    DESCSITUACAO VARCHAR(30),
    SITUACAO VARCHAR(2),
    CODFILIAL INTEGER,
    FILIAL VARCHAR(40),
    NFNFEMOV VARCHAR(20),
    NOMEPESSOA VARCHAR(70),
    TIPODOC VARCHAR(20),
    RETORNONFE VARCHAR(50))
AS
begin
  FOR SELECT M.CODMOVIMENTO, M.DATA, CF.DESCRICAO , CF.NATUREZA, CF.TIPO,
  DM.NRDOCUMENTO, M.TIPO, M.SITUACAO, M.CODFILIAL, NF.NRDOCUMENTO, DM.TIPODOC, DM.RETORNONFE
  FROM MOVIMENTOSPRODUTOS M
  JOIN DOCUMENTOMOV DM ON (DM.CODMOVIMENTO = M.CODMOVIMENTO)
  LEFT JOIN CFOP         CF ON (CF.CODCFOP      = DM.CODCFOP)
  LEFT JOIN NFEMOV  NF ON (NF.CODMOVIMENTO = M.CODMOVIMENTO)
  WHERE M.DATA>=:DATAI AND M.DATA<=:DATAF AND DM.tipodoc IN('NFE', 'NFCE')
  INTO :CODMOVIMENTO, :DATA, :CFOP, :NATUREZA, :TIPO, :NRDOCUMENTO, :TIPOMOV, :SITUACAO, :CODFILIAL, :NFNFEMOV, :TIPODOC, :RETORNONFE DO
  BEGIN

      SELECT F.NOME FROM FILIAIS F
      WHERE CODFILIAL=:CODFILIAL
      INTO :FILIAL;
      NOMEPESSOA=NULL;
      select NOME FROM sppessoamov(:CODMOVIMENTO, :TIPOMOV)
      INTO :NOMEPESSOA;

      IF (SITUACAO IS NULL) then DESCSITUACAO='';
      IF (SITUACAO='CO') then DESCSITUACAO='CONFERIDA';
      IF (SITUACAO='C')  then DESCSITUACAO='CANCELADA';
      IF (SITUACAO='A') then DESCSITUACAO='ABERTA';
      IF (SITUACAO='I') then DESCSITUACAO='INICIADA';
      IF (SITUACAO='F') then DESCSITUACAO='FINALIZADA';
      IF (SITUACAO='NF') then DESCSITUACAO='NF CONFIRMADA';
      SELECT TOTALGERAL FROM TOTALMOV(:CODMOVIMENTO)
      INTO :TOTAL;
  suspend;
  END
end
^

SET TERM ; ^




/*------ 10/03/2017 00:08:27 --------*/

SET TERM ^ ;

CREATE TRIGGER PARCELASPGTO_DELETE FOR PARCELASPGTO
ACTIVE BEFORE DELETE POSITION 0
as
begin
  DELETE FROM caixaparcelas C WHERE C.codparcelapgto=CODPARCELAPGTO;
end;
^

SET TERM ; ^




/*------ 10/03/2017 00:10:36 --------*/

SET TERM ^ ;

ALTER TRIGGER PARCELASPGTO_DELETE
ACTIVE BEFORE DELETE POSITION 0
as
begin
  DELETE FROM caixaparcelas C WHERE C.codparcelapgto=OLD.CODPARCELAPGTO;
end;
^

SET TERM ; ^