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 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 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 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 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 static bool GetRecord(string tagId, out TableData data)
{
bool Retval = false;
data = null;
#if !NO_ASA
string SqlStatement = "SELECT * FROM LOT_SERIAL where tagIDDisplay = :tagId";
try
{
using (SAConnection con = MainClass.GetConnection())
{
con.Open();
using (SACommand cmd = new SACommand(SqlStatement, con))
{
cmd.Parameters.Add(new SAParameter("tagId", SADbType.VarChar, 64)).Value = tagId;
using (SADataReader sa = cmd.ExecuteReader())
{
if (sa.Read())
{
MakeDataRec(sa, out data);
Retval = true;
}
}
}
}
}
catch (Exception ex)
{
Retval = false;
string err = String.Format(MainClass.StringTable.GetString("DatabaseError"), TableName, ex.Message.ToString() + "(" + SqlStatement + "=" + tagId + ")");
ServiceMessages.InsertRec(MainClass.AppName, TableName, "GetRecord", err);
}
#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));
}
}
/// <summary>
/// Devuelve una GenericObservableCollection con la info recibida de la BBDD (SADataReader dr), teniendo en cuenta la
/// info (List<TemplateInfoDB> templateinfodb) del tipo de dato (object obj) recibidos por params.
/// También valida el tipo de dato
/// </summary>
/// <param name="dr"></param>
/// <param name="templateinfodb"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static GenericObservableCollection GetObservableCollectionFromSADataReader(SADataReader dr, List <TemplateInfoDB> templateinfodb, object obj)
{ //Se crea el GenericObservableCollection auxiliar que se devolverá, donde se irá añadiendo la info recuperada de la BBDD (SADataReader dr)
GenericObservableCollection auxobscollection = new GenericObservableCollection();
//Se recorre el SADataReader para obtener sus valores según el tipo de objeto recibido por params
while (dr.Read())
{ //Se recuperan las propiedades e instanciamos un nuevo objeto del tipo del objeto recibido por params
var properties = GetProperties(obj);
object newobj = CreateObject(obj);
//Se recorre la lista de propiedades del objeto recibido por params
foreach (var prop in properties)
{ //De cada propiedad del objeto recibido por params, se recorre su List<TemplateInfoDB> templateinfodb
foreach (var item in templateinfodb)
{ //Se comprueba que el tipo de la propiedad del objeto recibido por params esté incluida en el List<TemplateInfoDB> templateinfodb
if (item.nombrepropiedadobj == prop.Name)
{ //Se añade el dato recuperado de la DB mediante el SADataReader a la propiedad(item.nombrepropiedadobj)
//del nuevo objeto(newobj), validando el dato según su tipo(ValidateData.***)
switch (item.tipodatocolumnadb)
{
case ETiposDatoColumnaDB.DBstring:
PropertySetValue(newobj, item.nombrepropiedadobj, ValidateData.GetString(dr[item.nombrecolumnadb] as string));
break;
case ETiposDatoColumnaDB.DBbool:
break;
case ETiposDatoColumnaDB.DBbyte: //byte en C# = tinyint en la DB
PropertySetValue(newobj, item.nombrepropiedadobj, ValidateData.GetByte(dr[item.nombrecolumnadb] as byte?));
break;
case ETiposDatoColumnaDB.DBshort:
PropertySetValue(newobj, item.nombrepropiedadobj, ValidateData.GetShort(dr[item.nombrecolumnadb] as short?));
break;
case ETiposDatoColumnaDB.DBint:
PropertySetValue(newobj, item.nombrepropiedadobj, ValidateData.GetInt(dr[item.nombrecolumnadb] as int?));
break;
case ETiposDatoColumnaDB.DBlong:
PropertySetValue(newobj, item.nombrepropiedadobj, ValidateData.GetLong(dr[item.nombrecolumnadb] as long?));
break;
case ETiposDatoColumnaDB.DBdecimal:
break;
case ETiposDatoColumnaDB.DBdouble:
break;
case ETiposDatoColumnaDB.DBdate:
break;
case ETiposDatoColumnaDB.DBdatetime:
break;
case ETiposDatoColumnaDB.DBsmalldatetime:
break;
case ETiposDatoColumnaDB.DBtime:
break;
default:
break;
}
}
}
}
auxobscollection.GenericObsCollection.Add(newobj); //Se añade el nuevo objeto del tipo recibido por params, a la ObservableCollection
}
return(auxobscollection);
}
// 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);
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected static void OnRowUpdated(object sender, SARowUpdatedEventArgs e)
{
try
{
PropertyCollection props = e.Row.Table.ExtendedProperties;
if (props.ContainsKey("props"))
{
props = (PropertyCollection)props["props"];
}
if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update))
{
esDataRequest request = props["esDataRequest"] as esDataRequest;
esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"];
if (e.StatementType == StatementType.Insert)
{
if (props.Contains("AutoInc"))
{
string autoInc = props["AutoInc"] as string;
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
cmd.CommandText = "SELECT @@IDENTITY;";
cmd.CommandType = CommandType.Text;
object o = null;
o = cmd.ExecuteScalar();
if (o != null)
{
packet.CurrentValues[autoInc] = o;
e.Row[autoInc] = o;
}
}
if (props.Contains("EntitySpacesConcurrency"))
{
string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string;
packet.CurrentValues[esConcurrencyColumn] = 1;
}
}
//-------------------------------------------------------------------------------------------------
// Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way
//-------------------------------------------------------------------------------------------------
if (props.Contains("Defaults"))
{
// Build the Where parameter and parameters
SACommand cmd = new SACommand();
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
string select = (string)props["Defaults"];
string[] whereParameters = ((string)props["Where"]).Split(',');
string comma = String.Empty;
string where = String.Empty;
int i = 1;
foreach (string parameter in whereParameters)
{
SAParameter p = new SAParameter(":p" + i++.ToString(), e.Row[parameter]);
cmd.Parameters.Add(p);
where += comma + "[" + parameter + "]=" + p.ParameterName;
comma = " AND ";
}
// Okay, now we can execute the sql and get any values that have defaults that were
// null at the time of the insert and/or our timestamp
cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";";
SADataReader rdr = null;
try
{
rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
if (rdr.Read())
{
select = select.Replace("[", String.Empty).Replace("]", String.Empty);
string[] selectCols = select.Split(',');
for (int k = 0; k < selectCols.Length; k++)
{
packet.CurrentValues[selectCols[k]] = rdr.GetValue(k);
}
}
}
finally
{
// Make sure we close the reader no matter what
if (rdr != null)
{
rdr.Close();
}
}
}
if (e.StatementType == StatementType.Update)
{
string colName = props["EntitySpacesConcurrency"] as string;
object o = e.Row[colName];
switch (Type.GetTypeCode(o.GetType()))
{
case TypeCode.Int16: packet.CurrentValues[colName] = ((System.Int16)o) + 1; break;
case TypeCode.Int32: packet.CurrentValues[colName] = ((System.Int32)o) + 1; break;
case TypeCode.Int64: packet.CurrentValues[colName] = ((System.Int64)o) + 1; break;
case TypeCode.UInt16: packet.CurrentValues[colName] = ((System.UInt16)o) + 1; break;
case TypeCode.UInt32: packet.CurrentValues[colName] = ((System.UInt32)o) + 1; break;
case TypeCode.UInt64: packet.CurrentValues[colName] = ((System.UInt64)o) + 1; break;
}
}
}
}
catch { }
}
