package br.com.elotech.social.scripts;

import br.com.elotech.enuns.TipoRelEnum;
import br.com.elotech.social.model.filters.AtividadeColetivaProfissionalFilter;

public class AtividadeColetivaProfissionalScript {

    public static String getSql(AtividadeColetivaProfissionalFilter filter) {
      StringBuilder sb = new StringBuilder(" SELECT")
                .append("     upper(uni.uni_desc)                          AS unidade,")
                .append("     upper(asag.asag_titulo)                      AS grupo,")
                .append("     upper(usr.usr_nome)                          AS profissional,")
                .append("     upper(asae.asae_titulo)                      AS encontro,")
                .append("     asae.asae_dtinicio                           AS dt_encontro,")
                .append("     asae.asae_dtinicio                           AS hr_inicial,")
                .append("     asae.asae_dtfinal                            AS hr_final,")
                .append("     COUNT(usu.usu_nome)                          AS qtd_participantes");
        if (filter.getTpRel().equals(TipoRelEnum.ANALITICO)) {
            sb.append("     ,upper(usu.usu_nome)                        AS cidadao,")
              .append("     usu.usu_datanasc                            AS dt_nasc,")
              .append("     COALESCE(usu.usu_nis, 'NÃO INFORMADO')      AS nis");
        }
        sb.append("   FROM as_atendimentocol_grupo asag")
          .append("   JOIN as_atendimentocol_encontros asae      ON asag.asag_codigo = asae.asag_codigo")
          .append("   JOIN as_atendimentocol_respencontro asar   ON asar.asae_codigo = asae.asae_codigo")
          .append("   JOIN as_estabelecimentos ase               ON asae.ase_codigo = ase.ase_codigo")
          .append("   JOIN as_atendimentocol_participantes asap  ON asag.asag_codigo = asap.asag_codigo AND asapar_status IS TRUE ")
          .append("   JOIN as_atendimentocol_encontroshist asaeh ON asaeh.asae_codigo = asae.asae_codigo AND asaeh.usu_codigo = asap.usu_codigo AND asaeh.asaeh_presenca IS TRUE ")
          .append("   JOIN unidade uni                           ON asag.uni_codigo  = uni.uni_codigo")
          .append("   JOIN usuarios usr                          ON usr.usr_codigo   = asar.usr_codigo")
          .append("   JOIN usuario usu                           ON usu.usu_codigo   = asap.usu_codigo")
          .append("  WHERE (CAST(asae.asae_dtinicio AS DATE) BETWEEN :dtInicial AND :dtFinal OR")
          .append("          CAST(asae.asae_dtfinal AS DATE) BETWEEN :dtInicial AND :dtFinal)")
          .append("    AND uni.uni_codigo = :unidade");
        if (filter.getAtivo() != null) {
            sb.append("  AND asag.asag_status = :ativo");
        }
        if (filter.getProfissional() != null) {
            sb.append("  AND usr.usr_codigo = :profissional");
        }
        sb.append("    AND asag.asag_status IS TRUE");
        if (filter.getTpRel().equals(TipoRelEnum.ANALITICO)) {
            sb.append("  GROUP BY 1,2,3,4,5,6,7,9,10,11");
        }else {
            sb.append("  GROUP BY 1,2,3,4,5,6,7");
        }
        sb.append("  ORDER BY 1,2,3,4,5,8;");
        return sb.toString();
    }

}