package br.com.elotech.saude.scripts;

import br.com.elotech.saude.model.filters.ProducaoFilaDeEsperaFilter;

public abstract class ProducaoFilaDeEsperaScript {

  private static StringBuilder sb;

  public static String getSql(ProducaoFilaDeEsperaFilter filter) {
    sb = new StringBuilder();
    sb.append(" SELECT fila_nome, usr_nome, fihi_status, count(fihi_status) as quantidade")
      .append("   FROM filas_historicos fh")
      .append("  INNER JOIN filas ON fh.fila_codigo = filas.fila_codigo AND fila_regulacao = 'S'")
      .append("  INNER JOIN filas_reguladores fr ON fr.fila_codigo = filas.fila_codigo AND fire_regulador = 'S' ")
      .append("  INNER JOIN usuarios usr ON usr.usr_codigo = fr.usr_codigo AND fh.fihi_usr_historico = usr.usr_codigo")
      .append("  WHERE fihi_ordem_historico <> 1");

    if (filter.getFilaCodigo() != null) {
      sb.append(" AND filas.fila_codigo = :fila_codigo");
    }

    if (filter.getStatus() != null) {
      sb.append(" AND fihi_status = :status");
    }

    if (filter.getUsrCodigo() != null) {
      sb.append(" AND fr.usr_codigo = :usr_codigo");
    }

    if (filter.getDataInicial() != null) {
      sb.append(" AND date(fihi_datahora_historico) >= :data_inicial");
    }

    if (filter.getDataFinal() != null) {
      sb.append(" AND date(fihi_datahora_historico) <= :data_final");
    }

    sb.append(" GROUP BY fila_nome, usr_nome, fihi_status")
      .append(" ORDER BY fila_nome, usr_nome, fihi_status");

    return sb.toString();
  }
}