public JsonResult mostrarListaPartidos()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarPartidos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Partido> listaPartidos = new List <Partido>();
while (reader.Read())
{
listaPartidos.Add(new Partido {
id = reader.GetInt32(0), nome = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(listaPartidos));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
public JsonResult listarCaderno()
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarCaderno", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <cadernoEleitoral> cadernoEleitorals = new List <cadernoEleitoral>();
while (reader.Read())
{
cadernoEleitorals.Add(new cadernoEleitoral {
id = Int32.Parse(reader.GetString(0)), periodo = reader.GetString(1)
});
}
reader.Close();
conn.Close();
return(Json(cadernoEleitorals));
}
catch (SAException ex)
{
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 guardarPartido(string nomePartido)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("CALL guardarPartido(\"nomePartido\" = ?)", conn);
cmd.CommandType = CommandType.Text;
SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Text;
param.Direction = ParameterDirection.Input;
param.Value = nomePartido;
cmd.Parameters.Add(param);
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 listarEleicoes(bool isActive)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("listarEleicoes", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Eleicao> listaEleicoes = new List <Eleicao>();
DateTime tmpInicio;
DateTime tmpFim;
DateTime today = DateTime.Now.Date;
while (reader.Read())
{
tmpInicio = reader.GetDateTime(2);
tmpFim = reader.GetDateTime(3);
if (isActive)
{
if (today >= tmpInicio && today < tmpFim)
{
listaEleicoes.Add(new Eleicao {
id = reader.GetInt32(0), nome = reader.GetString(1), inicio = reader.GetDateTime(2), fim = reader.GetDateTime(3)
});
}
}
else
{
listaEleicoes.Add(new Eleicao {
id = reader.GetInt32(0), nome = reader.GetString(1), inicio = reader.GetDateTime(2), fim = reader.GetDateTime(3)
});
}
}
reader.Close();
conn.Close();
return(Json(listaEleicoes));
}
catch (SAException ex)
{
conn.Close();
return(Json(false));
}
}
public ActionResult electionMain()
{
if (Session["UserID"] != null)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
conn.Open();
SACommand cmd = new SACommand("listarCandidatos", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
List <Candidato> listaCandidatos = new List <Candidato>();
while (reader.Read())
{
listaCandidatos.Add(new Candidato {
id = reader.GetInt32(0), nomeCandidato = reader.GetString(1), nomePartido = reader.GetString(2)
});
}
reader.Close();
conn.Close();
ViewBag.listaCandidatos = listaCandidatos;
return(View());
}
else
{
return(RedirectToAction("Login"));
}
}
public bool Close()
{
try
{
if (_currentState != ConnectionState.Closed)
{
_connection.Close();
_currentState = _connection.State;
}
}
catch (System.Exception e)
{
return(false);
}
return(true);
}
//Added for CSharp Station Report Wizard CR13
public static bool TestDBConnection(bool bLocalDB)
{
SAConnection _conn = null;
bool RetVal = false;
try
{
_conn = bLocalDB ? MainClass.GetConnection() : MainClass.GetMasterConnection();
_conn.Open();
RetVal = (ConnectionState.Open == _conn.State);
}
catch (Exception ex)
{
RetVal = false;
}
finally
{
if (_conn != null)
{
_conn.Close();
}
}
return(RetVal);
}
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 ActionResult Login(string login, string password)
{
SAConnection conn = new SAConnection("Data Source=eleicoes_conn");
try
{
conn.Open();
SACommand cmd = new SACommand("getAdmin", conn);
cmd.CommandType = CommandType.StoredProcedure;
SADataReader reader = cmd.ExecuteReader();
string dbUserName = "";
string dbPassword = "";
//List<Partido> listaPartidos = new List<Partido>();
while (reader.Read())
{
dbUserName = reader.GetString(0);
dbPassword = reader.GetString(1);
}
reader.Close();
conn.Close();
if (dbUserName == login && dbPassword == password)
{
Session["UserID"] = 1;
Session["UserName"] = "******";
ViewBag.Message = "";
return(RedirectToAction("Index"));
}
else
{
ViewBag.Message = "Login inválido!";
return(View());
}
// return Json(listaPartidos);
}
catch (SAException ex)
{
conn.Close();
return(RedirectToAction("Login"));
}
}
public override bool Close()
{
try
{
if (_currentState != ConnectionState.Closed)
{
_connection.Close();
_currentState = _connection.State;
}
}
catch (System.Exception e)
{
_logger.Log(LogLevel.Error, e);
return(false);
}
return(true);
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
myConnection.Close();
}
base.Dispose(disposing);
}
private void Dispose(SAConnection _conn)
{
if (_conn != null)
{
if (_conn.State == ConnectionState.Open)
{
_conn.Close();
}
_conn.Dispose();
}
}
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 void Disconnect()
{
if ((_connection != null) && (_connection.State != ConnectionState.Closed))
{
_connection.Close();
}
if (_connection != null)
{
_connection.Dispose();
}
if (_transaction != null)
{
_transaction.Dispose();
}
_transaction = null;
_connection = null;
}
// Execute a SQL statement & return the connection
// Return value: true if successful
static bool ExecuteSql(bool ReturnConn, string SqlStatement, bool LogErrMsg,
string table, string CallingClass, string CallingFunction,
out SAConnection _conn)
{
bool RetVal = true;
_conn = null;
try
{
if (OpenDB(CallingClass, CallingFunction, out _conn))
{
SACommand cmd = new SACommand(SqlStatement, _conn);
cmd.ExecuteNonQuery();
}
else
{
RetVal = false;
}
}
catch (Exception ex)
{
RetVal = false;
if (LogErrMsg)
{
string err = String.Format(StringTable.GetString("DatabaseError"),
table,
ex.Message.ToString() + "(" + SqlStatement + ")");
ServiceMessages.InsertRec(MainClass.AppName, CallingClass, CallingFunction, err);
}
}
finally
{
if (!ReturnConn && _conn != null)
{
_conn.Close();
}
}
return(RetVal);
}
DataTable IPlugin.GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
DataTable fks = cn.GetSchema(iAnywhere.Data.SQLAnywhere.SAMetaDataCollectionNames.MetaDataCollections);//, new string[] { table });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
DataTable dt = new DataTable();
string query = "select * from sys.sysforeignkeys where primary_creator = '{0}' and (primary_tname = '{1}' OR foreign_tname = '{2}')";
query = string.Format(query, schema, table, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateForeignKeysDataTable();
foreach (DataRow dtRow in dt.Rows)
{
string cols = (string)dtRow["columns"];
cols = cols.Replace(" IS ", ";");
string[] fkColumns = cols.Split(',');
foreach (string fkCol in fkColumns)
{
if (fkCol.Length == 0)
{
break;
}
string[] fkCols = fkCol.Split(';');
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["FK_NAME"] = dtRow["role"];
row["PK_NAME"] = "Primary Key";
row["PK_TABLE_CATALOG"] = cn.Database;
row["PK_TABLE_SCHEMA"] = dtRow["primary_creator"];
row["PK_TABLE_NAME"] = dtRow["primary_tname"];
row["FK_TABLE_CATALOG"] = cn.Database;
row["FK_TABLE_SCHEMA"] = dtRow["foreign_creator"];
row["FK_TABLE_NAME"] = dtRow["foreign_tname"];
row["FK_COLUMN_NAME"] = fkCols[0];
row["PK_COLUMN_NAME"] = fkCols[1];
string pkQuery = "select iname from sys.sysindexes where creator = '{0}' and indextype = 'Primary key' and tname = '{1}'";
pkQuery = string.Format(pkQuery, schema, dtRow["primary_tname"]);
cn.Open();
using (SACommand pkCmd = new SACommand(pkQuery, cn))
{
row["PK_NAME"] = (string)pkCmd.ExecuteScalar();
cn.Close();
}
}
}
}
}
catch { }
return(metaData);
}
// return distinct list of all records for specified field/table. Assumes field is a string
private static bool GetDistinctValsFromTable(DataTypeEnum DataType,
string table, string column, string filter, bool logErr, List <string> data, bool bLocalDB = true)
{
string filterQuery = "";
bool retval = false;
#if !NO_ASA
SAConnection conn = null;
SADataReader myReader = null;
// build query if string is not empty
if (filter.Length > 0)
{
filterQuery = string.Format("WHERE {0} ", filter);
}
string selectStr = string.Format("SELECT DISTINCT {0} FROM {1} {2}ORDER BY {0} ASC", column, table, filterQuery);
if (ExecuteSelect(selectStr, logErr, table, "MainClass", "GetDistinctVals", out conn, out myReader, bLocalDB))
{
int i;
DateTime dt;
decimal dec;
Type t;
try
{
retval = true;
while (myReader.Read())
{
switch (DataType)
{
case DataTypeEnum.String:
data.Add(myReader[column].ToString());
break;
case DataTypeEnum.Int:
// integers and decimal numbers are the same type in reports
// have to check the data type before converting
t = myReader[column].GetType();
// check for null value
if (Type.GetTypeCode(t) == TypeCode.DBNull)
{
break;
}
// check for decimal or int
if (Type.GetTypeCode(t) == TypeCode.Decimal)
{
dec = BBPyxisDB.MainClass.ToDecimal(table, myReader[column]);
data.Add(dec.ToString());
}
else
{
i = BBPyxisDB.MainClass.ToInt(table, myReader[column]);
data.Add(i.ToString());
}
break;
case DataTypeEnum.DateTime:
dt = BBPyxisDB.MainClass.ToDate(table, myReader[column]);
data.Add(dt.ToString("yyyy/MM/dd HH:mm:ss"));
break;
default:
System.Diagnostics.Debug.Assert(true, "Bad data type " + DataType + " in BPyxisDB.MainClass.GetDistinctValsFromTable");
break;
}
}
}
catch (Exception ex)
{
string err = String.Format(StringTable.GetString("DatabaseCastError"),
table, ex.Message.ToString());
ServiceMessages.InsertRec(MainClass.AppName, "MainClass", "GetDistinctValsFromTable", err);
}
}
if (myReader != null)
{
myReader.Close();
}
if (conn != null)
{
conn.Close();
}
#endif
return(retval);
}
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));
}
}
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 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
}
DataTable IPlugin.GetForeignKeys(string database, string table)
{
DataTable metaData = new DataTable();
try
{
using (SAConnection cn = new SAConnection(this.context.ConnectionString))
{
DataTable theTable = cn.GetSchema("Tables", new string[] { null, table, "BASE" });
DataTable fks = cn.GetSchema(iAnywhere.Data.SQLAnywhere.SAMetaDataCollectionNames.MetaDataCollections);//, new string[] { table });
string schema = "DBA";
if (theTable != null && theTable.Rows.Count == 1)
{
schema = (string)theTable.Rows[0]["TABLE_SCHEMA"];
}
DataTable dt = new DataTable();
string query = "select * from sys.sysforeignkeys where primary_creator = '{0}' and (primary_tname = '{1}' OR foreign_tname = '{2}')";
query = string.Format(query, schema, table, table);
SADataAdapter ad = new SADataAdapter(query, cn);
ad.Fill(dt);
metaData = context.CreateForeignKeysDataTable();
foreach (DataRow dtRow in dt.Rows)
{
string cols = (string)dtRow["columns"];
cols = cols.Replace(" IS ", ";");
string[] fkColumns = cols.Split(',');
foreach (string fkCol in fkColumns)
{
if (fkCol.Length == 0) break;
string[] fkCols = fkCol.Split(';');
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["FK_NAME"] = dtRow["role"];
row["PK_NAME"] = "Primary Key";
row["PK_TABLE_CATALOG"] = cn.Database;
row["PK_TABLE_SCHEMA"] = dtRow["primary_creator"];
row["PK_TABLE_NAME"] = dtRow["primary_tname"];
row["FK_TABLE_CATALOG"] = cn.Database;
row["FK_TABLE_SCHEMA"] = dtRow["foreign_creator"];
row["FK_TABLE_NAME"] = dtRow["foreign_tname"];
row["FK_COLUMN_NAME"] = fkCols[0];
row["PK_COLUMN_NAME"] = fkCols[1];
string pkQuery = "select iname from sys.sysindexes where creator = '{0}' and indextype = 'Primary key' and tname = '{1}'";
pkQuery = string.Format(pkQuery, schema, dtRow["primary_tname"]);
cn.Open();
using (SACommand pkCmd = new SACommand(pkQuery, cn))
{
row["PK_NAME"] = (string)pkCmd.ExecuteScalar();
cn.Close();
}
}
}
}
}
catch { }
return metaData;
}
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));
}
}