

SELECT
   statuslcto,
   tipolcto,
   CASE
      WHEN
         fat_docto_c.status_conferencia = 'CONFERIDO' 
      THEN
         'OK' 
      ELSE
         '  '
   END
   AS status_conferencia, 
   (
      SELECT
         fat_docto_c_controle 
      FROM
         financ_rp_baixas 
      WHERE
         fat_docto_c_controle = fat_docto_c.controle LIMIT 1
   )
   > 0 AS contem_baixa,
   controle,
   emissao,
   cad_filial_id,
   fat_docto_c.cad_cadastro_id AS cad_cadastro_id,
   nome,
   cadastro_vendedor_id,
   (
      SELECT
         nomefantasia 
      FROM
         cad_cadastro 
      WHERE
         id = cadastro_vendedor_id
   )
   AS nome_vendedor,
   (
      SELECT
         STRING_AGG('R:' || lpad(cast(fat_romaneio_id as varchar), 5, '0') || ' ATENDIDO PELO DOCTO:' || lpad(cast(fat_docto_c_controle_g as varchar), 10, '0'), ';')
      FROM
         fat_romaneio_pedido
      WHERE
         fat_docto_c_controle = fat_docto_c.controle
   )
   AS romaneio_pedido,
   (
      SELECT
        STRING_AGG( 'R:' || lpad(cast(fat_romaneio_id as varchar), 5, '0') || ' ATENDEU PED:' || lpad(cast(fat_docto_c_controle as varchar), 10, '0'), ';')
      FROM
         fat_romaneio_pedido
      WHERE
         fat_docto_c_controle_g = fat_docto_c.controle
   )
   AS romaneio_docto_g,
   cnpj_cpf,
   seriedocto_codigo,
   numero_docto,
   serie_natureza,
   valortotal_docto,
   fat_transacao_id,
   fat_transacao.descricao,
   seriedocto_id,
   fat_docto_c.fat_condpg_id,
   fat_docto_c.fat_listapre_id,
   fat_docto_c.fat_listapre_tabela_tabela,
   totalliquidoprodutos,
   descontodocto_perc,
   descontodocto_valor,
   desconto_valor,
   totalprodutos,
   icms_basecalculo,
   icms_valor,
   ipi_valor,
   funrural_perc,
   funrural_valor,
   nfechaveacesso,
   nfeprotocolo,
   statusdocto,
   frete_valor,
   despesa_valor,
   seguro_valor,
   icmssubst_valor,
   icmssubst_basecalculo,
   dataentradasaida,
   fat_serie.seriemodelo AS seriemodelo,
   (
      select
         propriedade 
      FROM
         view_transacao 
      WHERE
         codigo_transacao = fat_docto_c.fat_transacao_id 
         AND diretiva = 122
   )
   AS operacao 
FROM
   fat_docto_c 
   LEFT JOIN
      cad_cadastro 
      ON cad_cadastro.id = fat_docto_c.cad_cadastro_id 
   LEFT JOIN
      fat_transacao 
      ON fat_transacao.id = fat_docto_c.fat_transacao_id 
   LEFT JOIN
      fat_serie 
      ON fat_serie.id = fat_docto_c.seriedocto_id 
WHERE
   CASE
      WHEN
         (
            COALESCE(0, 0) > 0
         )
      THEN
         controle = 0 
      WHEN
         (
            COALESCE(NULL, 0) > 0
         )
      THEN
         numero_docto = NULL 
      WHEN
         (
            COALESCE(NULL, 0) > 0
         )
      THEN
         fat_docto_c.cad_cadastro_id = NULL 
         AND emissao BETWEEN CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) AND CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) 
      WHEN
         (
            COALESCE(NULL, 0) > 0
         )
      THEN
         fat_docto_c.fat_transacao_id = NULL 
         AND emissao BETWEEN CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) AND CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) 
      ELSE
         emissao BETWEEN CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) AND CAST(COALESCE(CAST(CURRENT_DATE AS VARCHAR(10)), CAST(CURRENT_DATE AS VARCHAR)) AS DATE) 
   END
   AND 
   CASE
      WHEN
         coalesce(TRIM(NULL), '') = '' 
      THEN
         fat_docto_c.fat_transacao_id in
         (
            select
               fat_transacao.id 
            from
               fat_transacao
         )
      ELSE
         fat_docto_c.fat_transacao_id = any(CAST(regexp_split_to_array(NULL, ',') AS int[])) 
   END
   AND 
   CASE
      WHEN
         (
            NOT(true) 
            AND -1 >- 1
         )
      THEN
         fat_docto_c.cadastro_vendedor_id = -1 
      ELSE
         COALESCE(fat_docto_c.cadastro_vendedor_id, 0) >- 1 
   END
   AND COALESCE(NULL, ' ') = ' ' 
ORDER BY
   emissao DESC, controle DESC LIMIT 100 OFFSET(1 - 1) * 100

