public bool inserirCandidatoEleicao(int idEleicao, int idCandidato)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL inserirCandidatoEleicao(\"idCandidato\" = ?, \"idEleicao\"= ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Integer;
param1.Direction = ParameterDirection.Input;
param1.Value = idCandidato;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = idEleicao;
cmd.Parameters.Add(param2);
SADataReader reader = cmd.ExecuteReader();
return(true);
}
catch (Exception ex)
{
conn.Close();
return(false);
}
}
public JsonResult guardarCandidato(string nome, int partido)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarCandidato(\"nomeCandidato\" = ?, \"idPartido\"= ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = nome;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = partido;
cmd.Parameters.Add(param2);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public JsonResult apagarEleicao(int idEleicao)
{
// CALL "dev"."listarEleitores"("idCaderno" = 4)
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL apagarEleicao(\"idEleicao\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idEleicao;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
reader.Close();
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
conn.Close();
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
return(Json(false));
}
}
public JsonResult loadBoletim(int idEleicao)
{
//carregarBoletim
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL carregarBoletim(\"idEleicao\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idEleicao;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
List <Boletim> itensBoletim = new List <Boletim>();
while (reader.Read())
{
itensBoletim.Add(new Boletim {
idCandidato = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2), idEleicao = idEleicao
});
}
reader.Close();
conn.Close();
return(Json(itensBoletim));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public JsonResult listarEleitores(int idCaderno)
{
// CALL "dev"."listarEleitores"("idCaderno" = 4)
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL listarEleitores(\"idCaderno\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Integer;
param.Direction = ParameterDirection.Input;
param.Value = idCaderno;
cmd.Parameters.Add(param);
SADataReader reader = cmd.ExecuteReader();
List <cadernoEleitor> cadernoEleitores = new List <cadernoEleitor>();
while (reader.Read())
{
cadernoEleitores.Add(new cadernoEleitor {
nome = reader.GetString(0), cc = reader.GetString(1), isVoted = reader.GetBoolean(2)
});
}
reader.Close();
conn.Close();
return(Json(cadernoEleitores));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public JsonResult guardarVoto(int idEleicao, int votos)
{
var escolha = new Voto();
escolha.idEleicao = idEleicao;
escolha.opcao = votos;
var json = JsonConvert.SerializeObject(escolha);
var source = json;
using (MD5 md5Hash = MD5.Create())
{
string hash = GetMd5Hash(md5Hash, source);
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarBoletim(\"boletimTxt\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = json;
cmd.Parameters.Add(param1);
//SAParameter param2 = cmd.CreateParameter();
//param2.SADbType = SADbType.Integer;
//param2.Direction = ParameterDirection.Input;
//param2.Value = idEleicao;
//cmd.Parameters.Add(param2);
SAParameter outP = cmd.CreateParameter();
outP.SADbType = SADbType.Integer;
outP.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outP);
SADataReader reader = cmd.ExecuteReader();
int idBoletim = (int)outP.Value;
reader.Close();
SACommand cmd2 = new SACommand("CALL guardarIntegridade(\"validCheck\" = ?, \"idBoletim\"= ? )", conn);
cmd2.CommandType = CommandType.Text;
SAParameter param3 = cmd.CreateParameter();
param3.SADbType = SADbType.Text;
param3.Direction = ParameterDirection.Input;
param3.Value = hash;
cmd2.Parameters.Add(param3);
SAParameter param4 = cmd.CreateParameter();
param4.SADbType = SADbType.Integer;
param4.Direction = ParameterDirection.Input;
param4.Value = idBoletim;
cmd2.Parameters.Add(param4);
SADataReader reader2 = cmd2.ExecuteReader();
reader2.Close();
conn.Close();
}
catch (SAException ex)
{
return(Json(false));
}
//Console.WriteLine("The MD5 hash of " + source + " is: " + hash + ".");
//Console.WriteLine("Verifying the hash...");
//if (VerifyMd5Hash(md5Hash, source, hash))
//{
// Console.WriteLine("The hashes are the same.");
//}
//else
//{
// Console.WriteLine("The hashes are not same.");
//}
}
return(Json(true));
}
public JsonResult checkEleitor(string nrCC, int idEleicao, string pin)
{
//checkEleitor"(IN nrCC VARCHAR(80), IN idEleicao INTEGER, OUT flag INTEGER
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL checkEleitor(\"nrCC\" = ?, \"idEleicao\"= ? , \"pinIN\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = nrCC;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.Integer;
param2.Direction = ParameterDirection.Input;
param2.Value = idEleicao;
cmd.Parameters.Add(param2);
SAParameter param3 = cmd.CreateParameter();
param3.SADbType = SADbType.Text;
param3.Direction = ParameterDirection.Input;
param3.Value = pin;
cmd.Parameters.Add(param3);
SAParameter _flag = cmd.CreateParameter();
_flag.SADbType = SADbType.Integer;
_flag.Direction = ParameterDirection.Output;
cmd.Parameters.Add(_flag);
SADataReader reader = cmd.ExecuteReader();
int flag = (int)_flag.Value;
reader.Close();
conn.Close();
if (flag == 1)
{
return(Json(true));
}
else
{
return(Json(false));
}
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public JsonResult criarEleicao(string nomeEleicao, DateTime dataInicio, DateTime dataFim, int cadernoEleitoral, int[] candidatos)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL inserirEleicao(\"nomeEleicao\" = ?, \"inicioEleicao\"= ? , \"fimEleicao\" = ? , \"idCadernoEleitoral\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Text;
param1.Direction = ParameterDirection.Input;
param1.Value = nomeEleicao;
cmd.Parameters.Add(param1);
SAParameter param2 = cmd.CreateParameter();
param2.SADbType = SADbType.DateTime;
param2.Direction = ParameterDirection.Input;
param2.Value = dataInicio;
cmd.Parameters.Add(param2);
SAParameter param3 = cmd.CreateParameter();
param3.SADbType = SADbType.DateTime;
param3.Direction = ParameterDirection.Input;
param3.Value = dataFim;
cmd.Parameters.Add(param3);
SAParameter param4 = cmd.CreateParameter();
param4.SADbType = SADbType.Integer;
param4.Direction = ParameterDirection.Input;
param4.Value = cadernoEleitoral;
cmd.Parameters.Add(param4);
SAParameter outId = cmd.CreateParameter();
outId.SADbType = SADbType.Integer;
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
SADataReader reader = cmd.ExecuteReader();
int idEleicao = (int)outId.Value;
reader.Close();
conn.Close();
bool flag = false;
foreach (int i in candidatos)
{
if (inserirCandidatoEleicao(idEleicao, i))
{
flag = true;
}
else
{
flag = false;
break;
}
}
if (flag)
{
return(Json(true));
}
else
{
return(Json(false));
}
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
}
public JsonResult gerarEleitores(int n, int ano, int mes)
{
List <Eleitor> listaEleitores = randomNames(n);
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
//criar caderno
SACommand cmdC = new SACommand("CALL inserirCaderno(\"periodoIn\" = ?)", conn);
cmdC.CommandType = CommandType.Text;
SAParameter periodoIn = cmdC.CreateParameter();
SAParameter idCadernoOut = cmdC.CreateParameter();
periodoIn.SADbType = SADbType.Text;
periodoIn.Direction = ParameterDirection.Input;
periodoIn.Value = ano.ToString() + "_" + mes.ToString();
cmdC.Parameters.Add(periodoIn);
idCadernoOut.SADbType = SADbType.Integer;
idCadernoOut.Direction = ParameterDirection.Output;
cmdC.Parameters.Add(idCadernoOut);
SADataReader readerC = cmdC.ExecuteReader();
int idCaderno = (int)idCadernoOut.Value;
//inserir eleitores //random
foreach (var eleitor in listaEleitores)
{
SACommand cmd = new SACommand("CALL inserirEleitor(\"nomeEleitor\" = ? , \"CC\"= ?, \"ZZ\" = ?, \"pinIn\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter paramNome = cmd.CreateParameter();
SAParameter paramCC = cmd.CreateParameter();
SAParameter paramZona = cmd.CreateParameter();
SAParameter paramPin = cmd.CreateParameter();
SAParameter outId = cmd.CreateParameter();
paramNome.SADbType = SADbType.Text;
paramNome.Direction = ParameterDirection.Input;
paramNome.Value = eleitor.nome;
cmd.Parameters.Add(paramNome);
paramCC.SADbType = SADbType.Text;
paramCC.Direction = ParameterDirection.Input;
paramCC.Value = eleitor.cartaoCidadao;
cmd.Parameters.Add(paramCC);
paramZona.SADbType = SADbType.Text;
paramZona.Direction = ParameterDirection.Input;
paramZona.Value = "N.A";
cmd.Parameters.Add(paramZona);
paramPin.SADbType = SADbType.Integer;
paramPin.Direction = ParameterDirection.Input;
paramPin.Value = eleitor.pin;
cmd.Parameters.Add(paramPin);
outId.SADbType = SADbType.Integer;
outId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outId);
SADataReader reader = cmd.ExecuteReader();
int idEleitor = (int)outId.Value;
//criar caderno
//caderno eleitoral
SACommand cmd2 = new SACommand("CALL inserirCadernoEleitoral(\"idEleitor\" = ?, \"idCaderno\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter idEleitorIn = cmd.CreateParameter();
SAParameter cadernoIN = cmd.CreateParameter();
idEleitorIn.SADbType = SADbType.Integer;
idEleitorIn.Direction = ParameterDirection.Input;
idEleitorIn.Value = idEleitor;
cmd2.Parameters.Add(idEleitorIn);
cadernoIN.SADbType = SADbType.Integer;
cadernoIN.Direction = ParameterDirection.Input;
cadernoIN.Value = idCaderno;
cmd2.Parameters.Add(cadernoIN);
SADataReader reader2 = cmd2.ExecuteReader();
reader.Close();
reader2.Close();
}
conn.Close();
return(Json(true));
}
catch (SAException ex)
{
//Console.WriteLine(ex.Errors[0].Source + " : " + ex.Errors[0].Message + " (" + ex.Errors[0].NativeError.ToString() + ")", "Failed to connect");
conn.Close();
return(Json(false));
}
//TODO PROCEDIMENTO SQLANYWHERE --> https://answers.sap.com/questions/12049111/json-in-sql-anywhere16.html
}
public JsonResult contarVotos(int idEleicao)
{
string outMessage = "";
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL contarVotos(\"idEleicao\" = ? )", conn);
cmd.CommandType = CommandType.Text;
SAParameter param1 = cmd.CreateParameter();
param1.SADbType = SADbType.Integer;
param1.Direction = ParameterDirection.Input;
param1.Value = idEleicao;
cmd.Parameters.Add(param1);
SAParameter outP = cmd.CreateParameter();
outP.SADbType = SADbType.Integer;
outP.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outP);
SADataReader reader = cmd.ExecuteReader();
int flagResultado = (int)outP.Value;//1-> integridade dos resultados afectada 2->eleição ainda a decorrer 0 -> ok!!!!
List <Resultado> resultados = new List <Resultado>();
int winnerId = 0;
if (flagResultado == 0)
{
while (reader.Read())
{
resultados.Add(new Resultado {
id = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2), nomeEleicao = reader.GetString(3), nrVotos = reader.GetInt32(4)
});
}
if (resultados.Count > 0)
{
winnerId = resultados.Where(x => x.nrVotos == resultados.Max(y => y.nrVotos)).SingleOrDefault().id;
}
reader.Close();
}
else if (flagResultado == 1)
{
outMessage = "Integridade dos resultados corrompida!!!!";
}
else
{
outMessage = "A eleição ainda está a decorrer";
}
if (outMessage.Length > 0)
{
conn.Close();
return(Json(outMessage));
}
else
{
conn.Close();
return(Json(new { R = resultados, W = winnerId }));
}
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}