package br.com.elotech.social.scripts;

import br.com.elotech.enuns.TipoRelEnum;
import br.com.elotech.social.model.filters.AtendimentoBasicoFilter;
import br.com.elotech.social.model.filters.DesligamentoFamiliaAcolhedoraFilter;

public class DesligamentoFamiliaAcolhedoraScript {

    public static String getSql(DesligamentoFamiliaAcolhedoraFilter filter) {

      StringBuilder sb = new StringBuilder()
        .append("SELECT ")
        .append("      usu.usu_nome as responsavelPrincipal, ")
        .append("      usu.usu_rg as rgPrincipal, ")
        .append("      usu.usu_datanasc as nascPrincipal, ")
        .append("      log.logra_logradouro as logradouro, ")
        .append("      rua.rua_nome as rua, ")
        .append("      dom.dom_numero as numero, ")
        .append("      bai.bai_nome as bairro, ")
        .append("      cid.cid_nome as cidade, ")
        .append("      cid.uf_sigla as uf, ")
        .append("      usu2.usu_nome as responsavelSecundario, ")
        .append("      usu2.usu_rg as rgSecundario, ")
        .append("      usu2.usu_datanasc as nascSecundario, ")
        .append("      aspsai.aspsai_motivo_situacao as motivo, ")
        .append("      aspsai.aspsai_data_desligamento as dataDesligamento ")
        .append(" FROM asps_acolhimento_inscricao as aspsai ")
        .append(" JOIN usuario usu ON usu.usu_codigo = aspsai.aspsai_responsavel_principal ")
        .append(" JOIN domicilio dom ON usu.dom_codigo = dom.dom_codigo ")
        .append(" JOIN rua ON rua.rua_codigo=dom.rua_codigo ")
        .append(" JOIN logradouro log ON rua.co_tipo_logradouro = log.logra_cod ")
        .append(" JOIN bairro bai ON rua.bai_codigo=bai.bai_codigo ")
        .append(" JOIN cidade cid ON cid.cid_codigo=bai.cid_codigo ")
        .append(" JOIN estado est ON cid.uf_codigo=est.uf_codigo ")
        .append(" LEFT JOIN usuario usu2 ON usu2.usu_codigo = aspsai.aspsai_responsavel_secundario ")
        .append("WHERE aspsai.aspsai_codigo = :aspsaiCodigo");
        return sb.toString();
    }
}