We have a client that needs to query the database in a short time, something close to 40 thousand requests in less than 2 hours.
I created a program that bridges the client's ratchets and our database to validate accesses, but the response time is horrible and many times the turnstiles fall and become inoperative (Henry 7x Ratchets).
p>I need to find a way to operate locally, copying the accesses of the external bank and making them available locally in real time (or almost). However, it must be secure and reliable, and valid access data must be returned to the external server to find out which tickets were used.
What do they tell me?
-------------------------------- EDIT ----------- ---------------------
Ratchets - > Database
Code that communicates the turnstiles with the database:
public partial class CatracasVNJ : Form
{
private Alternativo kernel7x; //Declarando Kernel
private DBConnect ConexaoBanco; //Instanciando Kernel
int eventIndex;
delegate void ViewLine(TextBox textBox, string texto);
public CatracasVNJ()
{
InitializeComponent();
kernel7x = new Alternativo(); //Instanciando Kernel
ConexaoBanco = new DBConnect(); //Instanciando Kernel
kernel7x.OnRegistro += onlineRegistryEventHandler;
kernel7x.OnProgresso += this.progressEventHandler;
this.eventIndex = -1;
}
/*=======================================================================
*
* ROTINAS AUXILIARES
*
*=======================================================================*/
private short booltoshort(bool value) {
if (value) return 1; else return 0;
}
private void preeche(TextBox textBox, string texto)
{
addViewLine(texto);
return;
}
private void addViewLine(String linha)
{
var data = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
txtMemo.AppendText(data + " - " + linha + "\r\n");
}
/*=======================================================================
*
* ROTINAS DO FORM
*
*=======================================================================*/
public static string[] explode(string separator, string source)
{
return source.Split(new string[] { separator }, StringSplitOptions.None);
}
//BOTÕES DA INTERFACE
private void btnIniciarCatracas_Click(object sender, EventArgs e)
{
// ações para preparar o botão e as rotas do inicia/para
this.btnIniciarCatracas.Enabled = false;
this.btnIniciarCatracas.Text = "PARAR";
this.btnIniciarCatracas.Click -= new System.EventHandler(this.btnIniciarCatracas_Click);
this.btnIniciarCatracas.Click += new System.EventHandler(this.btnPararCatracas_Click);
// ação original do botão
int i = 3000;
string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
foreach (string index in ListaIPS)
{
string[] resultado = explode(":", index);
SComConfig _rConfig;
_rConfig.IsCatraca = 0;
_rConfig.ModoComunicacao = SModoComunicacao.cmcOnOff;
_rConfig.TipoComunicacao = STipoComunicacao.ctcTcpIp;
_rConfig.Tcp.Ip = resultado[0].ToString();
_rConfig.Tcp.MAC = "";
_rConfig.Tcp.Porta = i;
_rConfig.Serial.NumeroRelogio = 1;
int indexAux = kernel7x.get_AdicionaCardTcpIp(_rConfig.Tcp.Ip,
_rConfig.Tcp.MAC, _rConfig.Tcp.Porta, false, _rConfig.ModoComunicacao);
if (indexAux >= 0)
{
kernel7x.SetSincronizar(indexAux, false);
addViewLine("Catraca adicionada: " + indexAux + " IP: " + resultado[0] + " Porta: " + i + "\n\r");
listaCatracas.Items.Add("Catraca " + indexAux, 3);
}
else
{
addViewLine("Falha: " +
kernel7x.ErrorDescription(kernel7x.KernelLastError));
}
i++;
}
this.btnIniciarCatracas.Enabled = true;
}
private void btnPararCatracas_Click(object sender, EventArgs e)
{
this.btnIniciarCatracas.Enabled = false;
string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
int i = 0;
foreach (string index in ListaIPS)
{
string[] resultado = explode(":", index);
kernel7x.get_RemoveCard(i);
addViewLine("Catraca removida: " + resultado[0].ToString() + "\n\r");
i++;
}
this.btnIniciarCatracas.Text = "INICIAR";
this.btnIniciarCatracas.Click -= new System.EventHandler(this.btnPararCatracas_Click);
this.btnIniciarCatracas.Click += new System.EventHandler(this.btnIniciarCatracas_Click);
this.btnIniciarCatracas.Enabled = true;
}
private void escreveListaIPS(String linha)
{
//textBox1.AppendText(linha + "\r\n");
}
/*=======================================================================
*
* TRATAMENTO DE EVENTOS DO KERNEL
*
*=======================================================================*/
public void progressEventHandler(int pThreadIndex, int pByte, int pByteMax, int pBuffer, int pBufferMax)
{
Application.DoEvents();
}
private void onlineRegistryEventHandler(int pThreadIndex) {
if (this.eventIndex == -1)
{
this.eventIndex = pThreadIndex;
Thread regThread = new Thread(this.onlineRegistryEventHandlerThd);
regThread.Start();
}
}
private void onlineRegistryEventHandlerThd()
{
int pThreadIndex = eventIndex;
ViewLine viewL = new ViewLine(this.preeche);
try
{
//Recebe solicitação do kernel
SRegistro registro;
/*
* Variáveis do Sregistros
*/
bool Saida = false;
bool MasterLiberou = false;
bool FuncaoLiberou = false;
bool AcessoNegado = false;
//txtMemo.Invoke(viewL, txtMemo, "Pedido de acesso recebido : " + pThreadIndex);
//Seu tratamento de acesso e ponto
SResposta resposta = new SResposta();
//Recebe do relógio ou catraca em tempo real
this.kernel7x.RegistroOn(pThreadIndex,
out registro.NumeroRelogio,
out registro.Funcao,
out registro.Matricula,
out registro.DataHora,
out registro.Flag,
out Saida,
out MasterLiberou,
out FuncaoLiberou,
out AcessoNegado,
out registro.Tipo.FonteEntrada,
out registro.Tipo.TipoNegado);
registro.Tipo.Saida = booltoshort(Saida);
registro.Tipo.MasterLiberou = booltoshort(MasterLiberou);
registro.Tipo.FuncaoLiberou = booltoshort(FuncaoLiberou);
registro.Tipo.AcessoNegado = booltoshort(AcessoNegado);
resposta.Mensagem = "***** VNJ ***** Acesso Liberado!";
resposta.Tempo = Convert.ToByte(1);
int catraca = pThreadIndex;
int arena;
int.TryParse("5", out arena);
long acessoConvertido;
var acesso = registro.Matricula;
try
{
acessoConvertido = Convert.ToInt64(acesso);
//txtMemo.Invoke(viewL, txtMemo, "Catraca : " + catraca + "Acesso : " + acessoConvertido + "Arena : " + arena);
string[] RespostaAcesso = ConexaoBanco.LiberaCatracaUnificada(catraca, arena, acessoConvertido);
if (Int32.Parse(RespostaAcesso[0]) == 0)
{
kernel7x.RespostaOn(pThreadIndex, SAcessoOnline.canLibEntrada, resposta.Mensagem, resposta.Tempo);
// Retorno pro programa
txtMemo.Invoke(viewL, txtMemo, "Acesso LIBERADO para : " + acesso);
}
else
{
kernel7x.RespostaOn(pThreadIndex, SAcessoOnline.canNegado, RespostaAcesso[1].ToString(), resposta.Tempo);
// Retorno pro programa
txtMemo.Invoke(viewL, txtMemo, "Acesso bloqueado para : " + acesso);
}
}
catch (OverflowException)
{
Console.WriteLine("{0} Esta fora da range permitida para este tipo de variável.", acesso);
}
catch (FormatException)
{
Console.WriteLine("Não foi possível converter.");
}
}
catch (Exception e) {
txtMemo.Invoke(viewL, txtMemo, "Exceção: " + e.Message);
}
eventIndex = -1;
}
private void button1_Click(object sender, EventArgs e)
{
// TESTA STORED PROCEDURE
string[] RespostaAcesso = ConexaoBanco.LiberaCatracaUnificada(1, Int32.Parse("5"), 1);
addViewLine(RespostaAcesso[0].ToString() + "\n\r");
addViewLine(RespostaAcesso[1].ToString() + "\n\r");
/// SEPARADOR //////////////////////////////////////
txtMemo.AppendText("=========================================================== \n");
///////////////////////////////////////////////////
// TESTA CONVERSÃO DE VARIAVEL
long acessoConvertido;
var acesso = "000000088973229041253";
try
{
acessoConvertido = Convert.ToInt64(acesso);
addViewLine("Convertio com sucesso: " + acessoConvertido);
Console.WriteLine("Converted the {0} acesso '{1}' to the {2} acesso {3}.", acesso.GetType().Name, acesso, acessoConvertido.GetType().Name, acessoConvertido);
}
catch (OverflowException)
{
Console.WriteLine("{0} Esta fora da range permitida para este tipo de variável.", acesso);
}
catch (FormatException)
{
Console.WriteLine("A {0} acesso '{1}' não está disponível para conversão.", acesso.GetType().Name, acesso);
}
// TESTA COMUNICAÇÃO COM A CATRACA
string[] ListaIPS = ConexaoBanco.ListaCatracas(Int32.Parse("5"));
int i = 0;
foreach (string index in ListaIPS)
{
txtMemo.AppendText("=========================================================== \n");
//
kernel7x.RecebeTipoCatraca(i, out SStatusGiro pStatusGiro, out byte pTempoLiberacao);
kernel7x.RecebeConfigDSP(i, out int pNivelSeguranca, out int pVelocidade, out int p2, out int p3, out bool p4, out bool pb);
addViewLine("Catraca número: " + i);
addViewLine("-");
addViewLine("Status giro: " + pStatusGiro + " Tempo de liberação: " + pTempoLiberacao);
addViewLine("Nivel de segurança: " + pNivelSeguranca + " Status: " + pb);
i++;
}
}
}
Function that connects to the bank
Function code that is used to validate ticket access or check cards on the turnstiles:
//////////////////////////////////////////////
// LiberaCatracaUnificada( Nrdispositivo , arena , acesso );
//
// Parametros:
// @ pThreadIndex: Número da catraca
// @ Arena: Número de identificação da arena
// @ Acesso: Número do ingresso ou mifare
//
// Retornos da função:
// [0] = Resposta da Stored Procedures
// [1] = Mensagem de resposta da Stored Procedure
//////////////////////////////////////////////
public string[] LiberaCatracaUnificada(int pThreadIndex, int arena, long acesso)
{
// Pega o NrDispositivo da catraca
var ListaIPs = ListaCatracas(arena);
string[] IP = ListaIPs[pThreadIndex].TrimStart(':').Split(':');
int NrDispositivo = Int32.Parse(IP[1]);
// Pega a data atual
var data = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// Prepara a query
string query = "CALL permite_acesso('" + arena + "', '" + acesso + "', '" + NrDispositivo + "', '" + data + "')";
// Abre conexão com o db
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
string[] retornoStoredProcedure = new string[2];
retornoStoredProcedure[0] = dataReader["RESPOSTA"].ToString();
retornoStoredProcedure[1] = dataReader["MENSAGEM"].ToString();
dataReader.Close();
this.CloseConnection();
return retornoStoredProcedure;
}
}
else
{
string[] resposta = new string[] { "99", "FALHA AO CONSULTAR STORED PROCEDURE" };
return resposta;
}
string[] resposta1 = new string[] { "98", "FALHA ENCONTRADA" };
return resposta1;
}
-------------------------------- EDIT 2 ---------- ----------------------
Thanks for the feedback, so friend at first it is not possible to change the database at the moment, we already feel the need to change it but due to several factors it is not possible to do this at the moment.
I was thinking of doing something like that comment above, creating a sort of queue and going getting the data and after the game sends them back to the server.
However, the ideal scenario was to work fully online, but if it is possible to effectively create this "queue" it can solve the problem I think.
I'll post below the Stored Procedure code and the access table architecture.
Stored Procedure
BEGIN
DECLARE RESPOSTA INT DEFAULT -1;
DECLARE _tpingresso_id INT DEFAULT 0;
DECLARE _data INT DEFAULT 0;
DECLARE _tituloso_id INT DEFAULT 0;
DECLARE _ingressocategoria_id INT DEFAULT 0;
DECLARE _acesso_id INT DEFAULT 0;
DECLARE _acesso_status INT DEFAULT -1;
DECLARE _titulosociedade_acessostatus varchar(10) default '';
DECLARE _evento_id INT DEFAULT 0;
DECLARE _entrada_nome VARCHAR(100);
DECLARE _catraca_id INT DEFAULT 0;
DECLARE _entrada_id INT DEFAULT 0;
DECLARE _titulosociedade_id INT DEFAULT 0;
DECLARE _observacao varchar(200) default '';
DECLARE _num_acessos_permitidos int;
DECLARE _num_acessos_atual int;
SET _acesso_id = 0;
set _catraca_id = 0;
set _num_acessos_permitidos = 1;
-- BUSCA DADOS DO EVENTO
select evento.evento_id into _evento_id from evento WHERE DATA_REF BETWEEN evento.iniAcesso AND evento.fimAcesso AND evento.arena_id = ARENA limit 1;
-- SE EXISTIR O EVENTO
IF(_evento_id > 0) THEN
-- BUSCA DADOS DA CATRACA
SELECT arena_catraca.catraca_id, trim(arena_entrada.nome) into _catraca_id, _entrada_nome FROM arena_catraca inner join arena_entrada on arena_entrada.entrada_id = arena_catraca.entrada_id WHERE arena_catraca.nrDispositivo = CATRACA AND arena_catraca.arena_id = ARENA limit 1;
-- SE ENCONTRADA A CATRACA
IF(_catraca_id > 0) THEN
-- BUSCA ACESSO DO TIPO INGRESSO
SELECT acesso_id INTO _acesso_id FROM acesso WHERE acesso.chaveAcesso = CODIGO and acesso.evento_id = _evento_id order by status limit 1;
-- select count(*) into _num_acessos_atual from acesso where acesso.evento_id = _evento_id and acesso.chaveAcesso = CODIGO limit 1;
-- BUSCA DADOS DE SOCIEDADE
IF (COALESCE(_acesso_id,0) <= 0) THEN
-- BUSCA A CARTEIRINHA
SELECT titulosociedade.tituloso_id,tpsocio.tpingresso_id
INTO _tituloso_id, _tpingresso_id
FROM titulosocar
INNER JOIN titulosociedade on titulosocar.tituloso_id = titulosociedade.tituloso_id
INNER JOIN tpsocio on tpsocio.tpsocio_id = titulosociedade.tpsocio_id
WHERE titulosocar.nroMifare = CODIGO
LIMIT 1;
IF(_tituloso_id > 0) THEN
-- INSERE ACESSO DO SÓCIO
insert into acesso (evento_id, tpingresso_id, tituloso_id, chaveAcesso, entidade_id, nroCarteira,status)
values(_evento_id, _tpingresso_id, _tituloso_id, CODIGO, 10, CODIGO,0);
-- SELECIONA O ACESSO INSERIDO
SELECT acesso_id INTO _acesso_id FROM acesso WHERE acesso.chaveAcesso = CODIGO and acesso.evento_id = _evento_id order by status limit 1;
END IF;
END IF;
IF (COALESCE(_acesso_id,0) > 0) THEN
-- BUSCA STATUS DO ACESSO
select distinct case when coalesce(titulosociedade.statusAcesso,'L') in ('L') then coalesce(acesso.status,0) else 5 end, acesso.tpingresso_id
into _acesso_status, _tpingresso_id
from acesso
left join titulosociedade on titulosociedade.tituloso_id = acesso.tituloso_id
where acesso.acesso_id = _acesso_id;
-- VERIFICA O STATUS DO ACESSO
if (coalesce(_acesso_status,-1) >= 0 ) THEN
CASE
-- LIBERADO
WHEN _acesso_status = 0 THEN
SET _entrada_id = 0;
-- BUSCA ENTRADA - SE PERMITIDO O ACESSO RETORNA UM INTEIRO
SELECT distinct arena_catraca_setor.entrada_id
INTO _entrada_id
FROM ( SELECT arena_catraca.* FROM arena_catraca WHERE arena_catraca.nrDispositivo = CATRACA AND arena_catraca.arena_id = ARENA) arena_catraca
inner join arena_entrada on arena_entrada.entrada_id = arena_catraca.entrada_id
inner join arena_catraca_setor on arena_catraca_setor.catraca_id = arena_catraca.catraca_id and arena_catraca_setor.setor_id in (select setor_id from arena_setortpingresso where tpingresso_id = _tpingresso_id);
-- SE ACESSO PERMITIDO
IF(coalesce(_entrada_id,0) > 0 ) THEN
SET _observacao = CONCAT( _observacao,'Acesso Permitido');
SET RESPOSTA = 0;
update acesso set status = 1 where acesso_id = _acesso_id;
-- SE ACESSO NEGADO PARA ESSE SETOR
else
SET _observacao = CONCAT(_observacao,'Acesso negado para esse setor');
SET RESPOSTA = 15;
end if;
-- TENTATIVA DUPLICADA DE ACESSO
WHEN _acesso_status = 1 THEN
set _observacao = CONCAT(_observacao, 'Tentantiva duplicada de acesso');
set RESPOSTA = 1;
WHEN _acesso_status = 5 THEN
set _observacao = CONCAT(_observacao, 'Pendências Financeiras');
set RESPOSTA = 5;
ELSE
set _observacao = CONCAT(_observacao, 'Erro de leitura');
set RESPOSTA = 99;
END CASE;
ELSE
SET _observacao = CONCAT( _observacao,'Status de Acesso inválido!');
SET RESPOSTA = 22;
END IF;
ELSE
IF(CODIGO = 3579281461 OR CODIGO = 82282900 OR CODIGO = 82282596 OR CODIGO = 82282580 OR CODIGO = 82282868 OR CODIGO = 82282564 OR CODIGO = 82282852 OR CODIGO = 82282548 OR CODIGO = 82282884) THEN
INSERT INTO outrosacessos(evento_id) VALUES(_evento_id);
SET _observacao = CONCAT( _observacao,'ACESSO MASTER');
SET RESPOSTA = 0;
ELSE
SET _observacao = CONCAT( _observacao,'Acesso Não Encontrado!');
SET RESPOSTA = 22;
END IF;
END IF;
IF(_acesso_id > 0 and _catraca_id > 0 and _evento_id > 0) THEN
insert into acesso_log (acesso_id, observacao, data, catraca_id, evento_id, resultado) values(_acesso_id, _observacao, DATA_REF, _catraca_id, _evento_id, RESPOSTA);
END IF;
ELSE
set _observacao = 'Catraca não encontrada!';
set RESPOSTA = 20;
END IF;
ELSE
set _observacao = 'Evento não encontrado!';
set RESPOSTA = 20;
END IF;
SELECT RESPOSTA RESPOSTA, _observacao MENSAGEM, coalesce(_acesso_id,0) ACESSO_ID;
Table
DROP TABLE IF EXISTS
acesso
; CREATE TABLEacesso
(acesso_id
int (11) NOT NULL AUTO_INCREMENT,tpingresso_id
int (11) DEFAULT NULL,tpcategoria_id
int (11) DEFAULT NULL,voucherkey
int (11) DEFAULT NULL,chaveAcesso
bigint (200) DEFAULT NULL,pedidoiteming_id
int (11) DEFAULT NULL,entidade_id
int (11) DEFAULT NULL,cadeira_id
int (11) DEFAULT NULL,operador_id
int (11) DEFAULT NULL,dtUtilizacao
datetime DEFAULT NULL,nroCarteira
int (11) DEFAULT NULL,valor
decimal (15,2) DEFAULT NULL,vlDesconto
decimal (15,4) DEFAULT NULL,vlTaxa
decimal (15,4) DEFAULT NULL,vlTotal
decimal (15,4) DEFAULT NULL,status
int (11) DEFAULT NULL,evento_id
int (11) DEFAULT NULL,tituloso_id
int (11) DEFAULT NULL,tipoGeracao
varchar (1) CHARACTER SET utf8 DEFAULT NULL,motivo
varchar (200) CHARACTER SET utf8 DEFAULT NULL,nroMifare
varchar (30) COLLATE utf8_unicode_ci DEFAULT NULL,caixa_id
int (11) DEFAULT NULL,pedidoitem_id
int (11) DEFAULT NULL,ingressocategoria_id
int (11) DEFAULT NULL, PRIMARY KEY (acesso_id
), KEYtpingresso_id
(tpingresso_id
), KEYentidade_id
(entidade_id
), KEYcadeira_id
(cadeira_id
), KEYevento_id
(evento_id
), CONSTRAINTacesso_ibfk_1
FOREIGN KEY (tpingresso_id
) REFERENCESarena_tipoingresso
(tpingresso_id
), CONSTRAINTacesso_ibfk_4
FOREIGN KEY (entidade_id
) REFERENCESentidade
(entidade_id
), CONSTRAINTacesso_ibfk_5
FOREIGN KEY (cadeira_id
) REFERENCESarena
(arena_id
), CONSTRAINTacesso_ibfk_7
FOREIGN KEY (evento_id
) REFERENCESevento
(evento_id
) ) ENGINE = InnoDB AUTO_INCREMENT = 4624 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;