package br.com.elotech.saude.scripts;

import br.com.elotech.saude.model.filters.vacinas.ControleVacinadosPorVacinaFilter;
import lombok.Data;

@Data
public abstract class ControleVacinadosPorVacinaScript {
    private static StringBuilder sb;

    public static String getSql(ControleVacinadosPorVacinaFilter filter) {
        sb = new StringBuilder();
        sb.append(" SELECT DISTINCT")
          .append("   vac.vac_data                              	  AS data_aplicacao,")
          .append("   est.tve_descricao                         	  AS estrategia,")
          .append("   COALESCE(fab.fab_descricao, 'NÃO INFORMADO')  AS laboratorio,")
          .append("   COALESCE(ite_lote, 'NÃO INFORMADO')       	  AS lote,")
          .append("   usu.usu_nome                              	  AS paciente,")
          .append("   usu.usu_datanasc                          	  AS data_nascimento,")
          .append("   extract(YEAR from age(usu.usu_datanasc))      AS idade, ")
          .append("   efv.efv_gestante                          	  AS gestante,")
          .append("   efv.efv_puerpera								              AS puerpera,")
          .append("   efv.efv_hanseniase                        	  AS hanseniase,")
          .append("   dose.tvd_descricao                        	  AS dose,")
          .append("   pro2.pro_nome                             	  AS vacina,")
          .append("   uni.uni_desc                              	  AS unidade,")
          .append("   usr.usr_nome                              	  AS profissional")
          .append(" FROM vacina_usuario AS vac")
          .append("   JOIN controlefracionado AS cont ON cont.cont_codigo = vac.cont_codigo")
          .append("   JOIN itens_movimento AS ite ON ite.ite_codigo = cont.ite_codigo")
          .append("   JOIN movimento AS mov ON ite.mov_codigo = mov.mov_codigo")
          .append("   JOIN produto AS pro2 ON pro2.pro_codigo = vac.pro_codigo")
          .append("   LEFT JOIN tb_vacina_dose AS dose ON vac.vac_dose = dose.tvd_codigo")
          .append("   JOIN unidade AS uni ON uni.uni_codigo = to_number(vac.vac_unidade,'999999999')")
          .append("   JOIN usuarios AS usr ON usr.usr_codigo = vac.usr_codigo")
          .append("   LEFT JOIN fabricante AS fab ON ite.fab_codigo = fab.fab_codigo")
          .append("   JOIN usuario AS usu ON vac.usu_codigo = usu.usu_codigo")
          .append("   LEFT JOIN esus_ficha_vacinacao efv ON efv.usu_codigo = usu.usu_codigo AND efv.efv_data = vac.vac_data")
          .append("   LEFT JOIN tb_vacina_estrategia AS est ON vac.est_codigo = est.tve_codigo")
          .append(" WHERE vac.vac_acao = 'A'")
          .append("   AND uni.uni_codigo = :unidade")
          .append("   AND vac.vac_data >=:dt_inicial")
          .append("   AND vac.vac_data <=:dt_final");
        if (filter.getProfissional() != null) {
            sb.append(" AND usr.usr_codigo = :profissional");
        }
        if (filter.getVacina() != null) {
            sb.append(" AND vac.pro_codigo = :vacina");
        }
        sb.append("  ORDER BY pro2.pro_nome ,uni.uni_desc, usr.usr_nome, vac.vac_data ASC ");
        return sb.toString();
    }

}