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 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 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 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 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 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 string Note(string note_key)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
$@"SELECT note
FROM case_notes cn
where cn.note_key = {note_key}";
SADataReader myDataReader = myCommand.ExecuteReader();
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Tables.Add("CaseNotes");
ds.Tables[0].Load(myDataReader);
myDataReader.Close();
return(ds.Tables[0].Rows[0]["note"].ToString());
}
/// <summary>
/// Devuelve una GenericObservableCollection con los valores recuperados de la DB según la EOpcion pasada por params
/// </summary>
/// <param name="opcion"></param>
/// <returns></returns>
public static GenericObservableCollection GetMaestrosAuxiliares(EOpcion opcion)
{
//Se recupera el nombre de la tabla de la BBDD(nombretabladb), el List<TemplateInfoDB> (templateinfodb)
//y el object (obj) según el EOpcion pasado por params
string nombretabladb = ribbonbuttondictionary.Where(z => z.Key == opcion).FirstOrDefault().Value.nombretabladb;
List <TemplateInfoDB> templateinfodb = ribbonbuttondictionary.Where(z => z.Key == opcion).FirstOrDefault().Value.templateinfodb;
object obj = ribbonbuttondictionary.Where(z => z.Key == opcion).FirstOrDefault().Value.obj;
//Se crea una conexión a la DB
//string enginename = "DBRENT_NET16";
//string databasename = "DBRENT_NET16";
//string uid = "cv";
//string pwd = "1929";
//string host = "172.26.0.45";
//SAConnection conn = new SAConnection(string.Format(ScriptsSQL.CONNECTION_STRING, enginename, databasename, uid, pwd, host));
SAConnection conn = new DBConnect().GetConnection(new DBConnect("DBRENT_NET16", "DBRENT_NET16", "cv", "1929", "172.26.0.45"));
//SAConnection conn = new SAConnection(new DBConnect().ConnexionString());
string sql = string.Format(ScriptsSQL.SELECT_ALL_BASICA, nombretabladb);
//Se crea una ObservableCollection del tipo de dato recibido por params
GenericObservableCollection auxobscollection = new GenericObservableCollection();
try
{
conn.Open();
SACommand cmd = new SACommand(sql, conn);
SADataReader dr = cmd.ExecuteReader();
auxobscollection = ManageGenericObject.GetObservableCollectionFromSADataReader(dr, templateinfodb, obj);
dr.Close();
}
catch (SAException e)
{
ErrorsDB.MessageError(e);
}
finally
{
conn.Close();
}
return(auxobscollection); //Se devuelve la ObservableCollection del tipo recibido por params
}
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 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));
}
}
private NeedlesModel GetOpenCheckList(SAConnection myConnection)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
@"select cl.case_id as 'case'
, names.last_long_name + ', ' + names.prefix + ' ' + names.first_name as 'party_name'
, cl.code
, cl.description
, cl.staff_assigned as assigned
, cl.due_date
, cl.status
, cl_d.repeat_period
, cases.lim_stat
from case_checklist cl
inner join checklist_dir cl_d on cl_d.matcode = cl.matcode and cl_d.code = cl.code
inner join cases on cases.casenum = cl.case_id
inner join party on party.case_id = cases.casenum
inner join names on names.names_id = party.party_id and names.name_location = party.party_id_location and party.our_client = 'Y'
where cl.staff_assigned = 'KALAI' and cl.status = 'Open' //and cl.code = 'FEE'
order by cl.due_date asc";
SADataReader myDataReader = myCommand.ExecuteReader();
DataSet dsChecklist = new DataSet();
dsChecklist.Tables.Add("Checklist");
dsChecklist.Tables[0].Load(myDataReader);
NeedlesModel model = new NeedlesModel
{
CheckListCount = dsChecklist.Tables[0].Rows.Count,
CheckList = JsonConvert.SerializeObject(dsChecklist)
};
myDataReader.Close();
return(model);
}
public static void DataReaderToDataTableTest()
{
PFSQLAnywhere db = new PFSQLAnywhere();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.DatabasePath = _frm.txtDatabaseFile.Text;
db.DataSourceName = _frm.txtDataSource.Text;
db.ServerName = _frm.txtServerName.Text;
db.DatabaseName = _frm.txtDatabaseName.Text;
db.Username = _frm.txtUsername.Text;
db.Password = _frm.txtPassword.Text;
db.DatabaseKey = _frm.txtDatabaseKey.Text;
db.EncryptedPassword = _frm.txtEncryptedPassword.Text;
db.Encryption = _frm.txtEncryption.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (_frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = _frm.txtSQLQuery.Text;
if (_frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
SADataReader rdr = (SADataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
_frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
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 NeedlesModel GetModel(string case_no)
{
SACommand myCommand = myConnection.CreateCommand();
myCommand.CommandText =
$@"SELECT *
FROM cases c
inner join user_case_data ucd on c.casenum = ucd.casenum
inner join insurance ins on c.casenum = ins.case_num
inner join names party on ins.party_id = party.names_id
where c.casenum = {case_no}";
SADataReader myDataReader = myCommand.ExecuteReader();
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
ds.Tables.Add("Case");
ds.Tables[0].Load(myDataReader);
NeedlesModel model = new NeedlesModel
{
Case = new Needles_Case(ds.Tables[0].Rows[0]),
Case_Data = new User_Case_Data(ds.Tables[0].Rows[0]),
Party = new Party(ds.Tables[0].Rows[0])
};
myDataReader.Close();
myCommand.CommandText =
$@"SELECT *
FROM insurance ins
inner join names party on ins.party_id = party.names_id
inner join names insurer on ins.insurer_id = insurer.names_id
inner join names adjuster on ins.adjuster_id = adjuster.names_id
inner join multi_addresses a on ins.insurer_id = a.names_id and default_addr = 'Y'
where ins.case_num = {case_no}";
myDataReader = myCommand.ExecuteReader();
ds = new DataSet();
ds.Tables.Add("Insurance");
ds.Tables[0].Load(myDataReader);
model.Insurances = new Insurances(ds.Tables[0].Rows);
model.Partys = new Partys(ds.Tables[0].Rows);
model.Insurers = new Insurers(ds.Tables[0].Rows);
model.Adjusters = new Adjusters(ds.Tables[0].Rows);
myDataReader.Close();
myCommand.CommandText =
$@"SELECT names.*
FROM names
inner join party on names.names_id = party.party_id
where case_id = {case_no} and role = 'DEFENDANT'";
myDataReader = myCommand.ExecuteReader();
ds = new DataSet();
ds.Tables.Add("Employer");
ds.Tables[0].Load(myDataReader);
model.Employer = new Party(ds.Tables[0].Rows[0]);
myDataReader.Close();
myCommand.CommandText =
$@"SELECT cn.note_key
, cn.note_date
, cn.note_time
, cn.staff_id
, cn.topic
, cn.note
, cn.case_status
FROM case_notes cn
where cn.case_num = {case_no}
order by cn.note_date desc";
myDataReader = myCommand.ExecuteReader();
ds = new DataSet();
ds.Tables.Add("CaseNotes");
ds.Tables[0].Load(myDataReader);
model.CaseNotes = JsonConvert.SerializeObject(ds);
myDataReader.Close();
return(model);
}
// 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 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));
}
}
// 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 { }
}
public static void DataReaderTest()
{
PFSQLAnywhere db = new PFSQLAnywhere();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.DatabasePath = _frm.txtDatabaseFile.Text;
db.DataSourceName = _frm.txtDataSource.Text;
db.ServerName = _frm.txtServerName.Text;
db.DatabaseName = _frm.txtDatabaseName.Text;
db.Username = _frm.txtUsername.Text;
db.Password = _frm.txtPassword.Text;
db.DatabaseKey = _frm.txtDatabaseKey.Text;
db.EncryptedPassword = _frm.txtEncryptedPassword.Text;
db.Encryption = _frm.txtEncryption.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (_frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = _frm.txtSQLQuery.Text;
if (_frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
db.returnResultAsString += new PFSQLAnywhere.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
sw.Start();
SADataReader rdr = (SADataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderFixedLengthTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
rdr.Close();
try
{
sw.Start();
rdr = (SADataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
_msg.Length = 0;
_msg.Append("ERROR: Unable to create fixed width output file.");
_msg.Append(Environment.NewLine);
_msg.Append("Error Message:");
_msg.Append(Environment.NewLine);
_msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex));
Program._messageLog.WriteLine(_msg.ToString());
AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog);
}
finally
{
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr.Close();
}
rdr = (SADataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\Testrdr.xml");
rdr.Close();
rdr = (SADataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\Testrdrplus.xml");
rdr.Close();
rdr = (SADataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\Testrdr.xsd");
rdr.Close();
rdr = (SADataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
}
catch (System.Exception ex)
{
_frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
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));
}
}
