public string VerificaPacote(long pacote, long usuario, long atividade, long tarefa)
{
SqlConnection sqlConn1 = null;
SqlConnection sqlConn2 = null;
SqlCommand sqlCmd = null;
object Retorno;
string retorno;
try
{
sqlConn1 = new SqlConnection(connectionString);
sqlConn1.Open();
sqlCmd = new SqlCommand(string.Format("select 'p' + cast(a1.Id_pacote as varchar) + " +
"'a' + cast(a1.Id as varchar) as Votacao " +
"from AtividadePacote a1 with(nolock) " +
"inner join EquipePacote e1 with(nolock) on e1.Id_pacote = a1.Id_pacote and e1.Id_usuario = {0} " +
"where a1.Status = '0' and a1.Tempo_previsto = 0 and a1.Possui_tarefas = 0 and a1.Id <> {1} and a1.Id_pacote = {2} " +
"and not exists (select 1 from VotacaoAtividade va with(nolock) where va.Id_atividade = a1.Id and va.Id_usuario = {0})" + Environment.NewLine +
"union" + Environment.NewLine +
"select 'p' + cast(a2.Id_pacote as varchar) + " +
"'a' + cast(t1.Id_atividade as varchar) + 't' + cast(t1.Id as varchar) as Votacao " +
"from AtividadePacote a2 with(nolock) " +
"inner join TarefaAtividade t1 with(nolock) on t1.Id_atividade = a2.Id and t1.Status = '0' and t1.Tempo_previsto = 0 and t1.Id <> {3} " +
"inner join EquipePacote e2 with(nolock) on e2.Id_pacote = a2.Id_pacote and e2.Id_usuario = {0} " +
"where a2.Possui_tarefas = 1 and a2.Id_pacote = {2} " +
"and not exists (select 1 from VotacaoTarefa vt with(nolock) where vt.Id_tarefa = t1.Id and vt.Id_usuario = {0})",
usuario.ToString(),
atividade.ToString(),
pacote.ToString(),
tarefa.ToString()
), sqlConn1);
Retorno = sqlCmd.ExecuteScalar();
if (string.IsNullOrWhiteSpace(Convert.ToString(Retorno)))
{
sqlConn2 = new SqlConnection(connectionString);
sqlConn2.Open();
sqlCmd = new SqlCommand(string.Format("select p.Status from Pacote p with(nolock) where p.Id = {0}",
pacote.ToString()
), sqlConn2);
Retorno = sqlCmd.ExecuteScalar();
}
if (string.IsNullOrWhiteSpace(Convert.ToString(Retorno)))
{
retorno = string.Empty;
}
else
{
retorno = Retorno.ToString();
}
}
catch (Exception e)
{
mensagem += " | " + e.Message;
retorno = string.Empty;
}
finally
{
if (sqlConn1 != null)
{
sqlConn1.Dispose();
}
if (sqlCmd != null)
{
sqlCmd.Dispose();
}
if (sqlConn2 != null)
{
sqlConn2.Dispose();
}
}
return(retorno);
}
