package br.com.elotech.social.scripts;


public class AtendimentoMensalCentroPopScript {

  private static final String fromEInnerDefault = " FROM as_atendimento_basicocras asab" +
    " INNER JOIN usuario usu ON asab.usu_codigo = usu.usu_codigo";

  private static final String innerBloco2E2aE5 = " INNER JOIN as_prontuario_suas_usuario aspsu ON usu.usu_codigo = aspsu.usu_codigo" +
    " INNER JOIN as_prontuario_suas asps ON aspsu.asps_codigo = asps.asps_codigo";

  private static final String whereDefault = " WHERE asab.uni_codigo = :uniCodigo" +
    " AND EXTRACT(MONTH FROM asab.asab_data) = :mes" +
    " AND EXTRACT(YEAR FROM asab.asab_data) = :ano";

  private static final String whereDefaultBloco1 = " AND usu.usu_sit_rua = 'S'" +
    " AND asab.abordagem_social = 'N'";

  private static final String whereDefaultBloco2 = " AND usu.usu_sit_rua = 'N' OR usu.usu_sit_rua IS NULL" +
    " AND asab.abordagem_social = 'S'";

  private static final String fromEWhereDefaultParaBloco1 = fromEInnerDefault +
    whereDefault +
    whereDefaultBloco1;

  private static final String fromEWhereDefaultParaBloco2 = fromEInnerDefault +
    whereDefault +
    whereDefaultBloco2;

  private static final String fromEWhereParaBloco2E2aE3 = fromEInnerDefault +
    innerBloco2E2aE5 +
    " INNER JOIN as_registros_violacao asrv ON asrv.asps_codigo = asps.asps_codigo" +
    " INNER JOIN as_situacoes_violacao assv ON asrv.assv_codigo = assv.assv_codigo" +
    whereDefault +
    whereDefaultBloco2;

  private static final String fromEWhereParaBloco2E4aE5 = fromEInnerDefault +
    innerBloco2E2aE5 +
    " INNER JOIN asps_anot_saude_cs aspsas ON aspsas.asps_codigo = asps.asps_codigo" +
    whereDefault +
    whereDefaultBloco2;

  public static String getSqlHeader() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" uni.uni_desc AS unidade,")
      .append(" uni.uni_asnumero AS numeroUnidade,")
      .append(" uni.uni_endereco,")
      .append(" uni.uni_numero,")
      .append(" uni.cnes_bairro,")
      .append(" uni.uni_asbairro")
      .append(" FROM unidade AS uni")
      .append(" WHERE uni.uni_codigo = :uniCodigo");

    return sb.toString();
  }

  public static String getSqlUfUnidade() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" uf_sigla")
      .append(" FROM cidade")
      .append(" WHERE cast(cid_codigo_ibge AS integer) = ")
      .append(" (SELECT conf_valor_int FROM config WHERE conf_chave = 'CID_CODIGO_IBGE')");

    return sb.toString();
  }

  public static String getSqlCidadeUnidade() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" conf_valor_string AS nome_cidade")
      .append(" FROM config")
      .append(" WHERE conf_chave = 'NOME_CIDADE'");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRua() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRua")
      .append(fromEWhereDefaultParaBloco1);

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaMasc0A12() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaMasc0A12")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 0 AND 12");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaMasc13A17() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaMasc13A17")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 13 AND 17");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaMasc18A39() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaMasc18A39")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 18 AND 39");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaMasc40A59() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaMasc40A59")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 40 AND 59");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaMasc60OuMais() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaMasc60OuMais")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) >= 60");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaFem0A12() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaFem0A12")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 0 AND 12");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaFem13A17() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaFem13A17")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 13 AND 17");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaFem18A39() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaFem18A39")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 18 AND 39");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaFem40A59() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaFem40A59")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 40 AND 59");

    return sb.toString();
  }

  public static String getSqlA1TotalSituacaoRuaFem60OuMais() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS a1TotalSituacaoRuaFem60OuMais")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) >= 60");

    return sb.toString();
  }

  public static String getSqlB1TotalUsuariosCrack() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS b1TotalUsuariosCrack")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_as_drogado = true");

    return sb.toString();
  }

  public static String getSqlB2TotalMigrantes() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS b2TotalMigrantes")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_tipo_deslocamento = 'MIGRANTE'");

    return sb.toString();
  }

  public static String getSqlB3TotalTranstornoMental() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS b3TotalTranstornoMental")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND usu.usu_asdeficiencia = true")
      .append(" AND usu.usu_deficiencia_transtorno = 'S'");

    return sb.toString();
  }

  public static String getSqlC1TotalIncluidosCadUnico() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS c1TotalIncluidosCadUnico")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND asab.asat_codigo = 4")
      .append(" AND asab.cadastro_unico = 'I'");

    return sb.toString();
  }

  public static String getSqlC2TotalAtualizadosCadUnico() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS c2TotalAtualizadosCadUnico")
      .append(fromEWhereDefaultParaBloco1)
      .append(" AND asab.asat_codigo = 4")
      .append(" AND asab.cadastro_unico = 'A'");

    return sb.toString();
  }

  public static String getSqlD1TotalAtendimentosRealizados() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.asab_codigo)) AS d1TotalAtendimentosRealizados")
      .append(fromEWhereDefaultParaBloco1);

    return sb.toString();
  }

  public static String getSqlNaoRealizaOfertaServico() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" uni_oferta_servico_especializado AS naoRealizaOfertaServico")
      .append(" FROM unidade")
      .append(" WHERE uni_codigo = :uniCodigo");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocial() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocial")
      .append(fromEWhereDefaultParaBloco2);

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialMasc0A12() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialMasc0A12")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 0 AND 12");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialMasc13A17() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialMasc13A17")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 13 AND 17");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialMasc18A39() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialMasc18A39")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 18 AND 39");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialMasc40A59() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialMasc40A59")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 40 AND 59");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialMasc60OuMais() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialMasc60OuMais")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'M'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) >= 60");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialFem0A12() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialFem0A12")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 0 AND 12");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialFem13A17() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialFem13A17")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 13 AND 17");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialFem18A39() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialFem18A39")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 18 AND 39");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialFem40A59() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialFem40A59")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) BETWEEN 40 AND 59");

    return sb.toString();
  }

  public static String getSqlE1TotalAbordagemSocialFem60OuMais() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e1TotalAbordagemSocialFem60OuMais")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_sexo = 'F'")
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) >= 60");

    return sb.toString();
  }

  public static String getSqlE2TotalCriancasAdolescentesTrabFnfantil() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e2TotalCriancasAdolescentesTrabFnfantil")
      .append(fromEWhereParaBloco2E2aE3)
      .append(" AND asrv.asrv_dtfinal IS NULL OR (EXTRACT(MONTH FROM asrv.asrv_dtfinal) = :mes AND EXTRACT(YEAR FROM asrv.asrv_dtfinal) = :ano)")
      .append(" AND assv.assv_codigo = 1");

    return sb.toString();
  }

  public static String getSqlE3TotalCriancasAdolescentesExplorSexual() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS E3TotalCriancasAdolescentesExplorSexual")
      .append(fromEWhereParaBloco2E2aE3)
      .append(" AND asrv.asrv_dtfinal IS NULL OR (EXTRACT(MONTH FROM asrv.asrv_dtfinal) = :mes AND EXTRACT(YEAR FROM asrv.asrv_dtfinal) = :ano)")
      .append(" AND assv.assv_codigo IN (2, 14)");

    return sb.toString();
  }

  public static String getSqlE4TotalCriancasAdolescentesUsuarioCrack() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e4TotalCriancasAdolescentesUsuarioCrack")
      .append(fromEWhereParaBloco2E4aE5)
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) <= 17")
      .append(" AND (aspsas.aspsas_alcoolatra = true OR aspsas.aspsas_drogado = true)");

    return sb.toString();
  }

  public static String getSqlE5TotalAdultosUsuarioCrack() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e5TotalAdultosUsuarioCrack")
      .append(fromEWhereParaBloco2E4aE5)
      .append(" AND EXTRACT(YEAR FROM AGE(usu.usu_datanasc)) > 17")
      .append(" AND aspsas.aspsas_drogado = true");

    return sb.toString();
  }

  public static String getSqlE6TotalMigrantes() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.usu_codigo)) AS e6TotalMigrantes")
      .append(fromEWhereDefaultParaBloco2)
      .append(" AND usu.usu_tipo_deslocamento = 'MIGRANTE'");

    return sb.toString();
  }

  public static String getSqlF1TotalAbordagensRealizadas() {
    StringBuilder sb = new StringBuilder("SELECT ")
      .append(" COUNT(DISTINCT(asab.asab_codigo)) AS f1TotalAbordagensRealizadas")
      .append(fromEWhereDefaultParaBloco2);

    return sb.toString();
  }
}