private void loadComboios(object sender = null, EventArgs e = null)
{
if (!Connection.verifySGBDConnection())
{
return;
}
boxFabricante = textBoxSearchCombFabricante.Text;
boxTipo = textBoxSearchCombTipo.Text;
boxID = textBoxSearchCombID.Text;
if (boxFabricante.Length == 0)
{
boxFabricante = "null";
}
if (boxTipo.Length == 0)
{
boxTipo = "null";
}
if (boxID.Length == 0)
{
boxID = "null";
}
String query = "exec getComboios @id=" + boxID + ", @tipo=" + boxTipo + ", @fabricante=" + boxFabricante;
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxComboios.Items.Clear();
while (reader.Read())
{
Comboio c = new Comboio();
c.Carruagens = reader["num_carruagens"].ToString();
c.Fabricante = reader["fabricante"].ToString();
c.Id = reader["id_comboio"].ToString();
c.Lugares = reader["num_lug_comboio"].ToString();
c.Tipo = reader["tipo"].ToString();
listBoxComboios.Items.Add(c);
}
currentComboio = 0;
if (listBoxComboios.Items.Count == 0)
{
listBoxComboios.Items.Add("Sem comboios a apresentar");
currentComboio = -1;
}
Connection.close();
ShowComboio();
}
private void loadBilhetes(object sender = null, EventArgs e = null)
{
if (!Connection.verifySGBDConnection())
{
return;
}
String queryDate = "@data=" + datePicked;
if (datePicked != "null")
{
queryDate = "@data='" + datePicked + "'";
}
String query = "exec getClientBilhetes @cc=" + Cliente.Escolhido.CC + "," + queryDate + ", @estacao_" +
"partida=" + EstacaoPartida + ", @estacao_chegada=" + EstacaoChegada;
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxBilhetes.Items.Clear();
while (reader.Read())
{
Bilhete b = new Bilhete();
b.BilheteID = reader["bilheteID"].ToString();
b.Preco = reader["preco"].ToString();
b.Data = reader["data"].ToString();
b.ParagemPartida = reader["paragem_partida"].ToString();
b.ParagemChegada = reader["paragem_chegada"].ToString();
b.Comboio = reader["lugar_comboio"].ToString();
b.Carruagem = reader["lugar_carruagem"].ToString();
b.Lugar = reader["lugar_n"].ToString();
b.Partida = reader["partida"].ToString();
b.HoraPartida = reader["hora_partida"].ToString();
b.Chegada = reader["chegada"].ToString();
b.HoraChegada = reader["hora_chegada"].ToString();
listBoxBilhetes.Items.Add(b);
}
currentBilhete = 0;
if (listBoxBilhetes.Items.Count == 0)
{
listBoxBilhetes.Items.Add("Sem bilhetes a apresentar");
currentBilhete = -1;
}
Connection.close();
ShowBilhete();
}
void comprarBilhete(object sender, EventArgs e)
{
String carr = textBoxInfoCarruagem.Text;
String lugar = textBoxInfoLugar.Text;
if (carr.Length == 0 || lugar.Length == 0)
{
MessageBox.Show("Por favor preencha todos os campos!");
return;
}
if (!Connection.verifySGBDConnection())
{
return;
}
Horario h = (Horario)listBoxHorarios.Items[currentHorario];
String query = "declare @tmp viagem_array; insert into @tmp values(" + h.Viagem + ",'" +
h.Partida + "','" + h.Chegada + "'," + carr + "," + lugar + ");exec comprar_bilhete " +
"@cc=" + Cliente.Escolhido.CC + ",@viagens=@tmp,@latitude=40.64528,@longitude=-8.639841,@metodo='Compra com saldo'," +
"@data='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
if (!Clean.IsClean(query, "-"))
{
MessageBox.Show(Clean.Err());
MessageBox.Show(query);
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
var returnParameter = cmd.Parameters.Add("@ReturnVal", System.Data.SqlDbType.Int);
returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int result = (int)returnParameter.Value;
if (result == 1)
{
MessageBox.Show("Lugar indisponível!");
}
else if (result == 2)
{
MessageBox.Show("Saldo insuficiente!");
}
else
{
MessageBox.Show("Bilhete comprado com sucesso!");
}
Connection.close();
}
private void loadClientesCC(object sender, EventArgs e)
{
if (!Connection.verifySGBDConnection())
{
return;
}
String boxCC = textBoxSearchCcc.Text;
String query = "exec getCliente @cc='" + boxCC + "'";
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxClientes.Items.Clear();
while (reader.Read())
{
Cliente C = new Cliente();
C.CC = reader["CC"].ToString();
C.NIF = reader["nif"].ToString();
C.UserID = reader["userID"].ToString();
C.Nome_proprio = reader["nome_proprio"].ToString();
C.Apelido = reader["apelido"].ToString();
C.Email = reader["email"].ToString();
double tmp;
Double.TryParse(reader["saldo"].ToString(), out tmp);
C.Saldo = tmp;
C.Tel = reader["contacto_telefonico"].ToString();
listBoxClientes.Items.Add(C);
}
Connection.close();
currentClient = 0;
if (listBoxClientes.Items.Count == 0)
{
listBoxClientes.Items.Add("Sem clientes a apresentar");
currentClient = -1;
}
ShowClient();
}
private void addCliente(object sender, EventArgs e)
{
if (fieldsCompleted())
{
if (!Connection.verifySGBDConnection())
{
return;
}
String query = "exec addCliente @cc = " + boxCC.Text + ", @nome = " + boxNome.Text +
", @apelido = " + boxApelido.Text + ", @genero = " + comboGenero.SelectedItem.ToString() +
", @email = " + boxEmail.Text + ", @telefone = " + boxTel.Text + ", @nif = " + boxNIF.Text +
", @userId = " + boxUserID.Text + ", @password = "******",@cartao = " + boxCartaoCredito.Text;
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("Failed to add client to database. \n ERROR MESSAGE: \n" + ex.Message);
}
finally
{
MessageBox.Show("Cliente adicionado à base de dados com sucesso");
Connection.close();
this.Close();
}
}
else
{
MessageBox.Show("Por favor preencha todos os campos");
}
}
private void loadClientesNome(object sender, EventArgs e)
{
if (!Connection.verifySGBDConnection())
{
return;
}
String boxNome = textBoxSearchCnome.Text;
// default search: lista todos os clientes na BD
if (boxNome.Length == 0)
{
String query = "exec getCliente @nome=''";
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxClientes.Items.Clear();
while (reader.Read())
{
Cliente C = new Cliente();
C.CC = reader["CC"].ToString();
C.NIF = reader["nif"].ToString();
C.UserID = reader["userID"].ToString();
C.Nome_proprio = reader["nome_proprio"].ToString();
C.Apelido = reader["apelido"].ToString();
C.Email = reader["email"].ToString();
double tmp;
Double.TryParse(reader["saldo"].ToString(), out tmp);
C.Saldo = tmp;
C.Tel = reader["contacto_telefonico"].ToString();
listBoxClientes.Items.Add(C);
}
}
else
{
String apelido = "";
String nome = "";
if (boxNome.Contains(" "))
{
nome = boxNome.Split(' ')[0];
apelido = boxNome.Split(' ')[1];
}
else
{
nome = boxNome;
}
String query = "exec getCliente @nome='" + nome + "', @apelido='" + apelido + "'";
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxClientes.Items.Clear();
while (reader.Read())
{
Cliente C = new Cliente();
C.CC = reader["CC"].ToString();
C.NIF = reader["nif"].ToString();
C.UserID = reader["userID"].ToString();
C.Nome_proprio = reader["nome_proprio"].ToString();
C.Apelido = reader["apelido"].ToString();
C.Email = reader["email"].ToString();
double tmp;
Double.TryParse(reader["saldo"].ToString(), out tmp);
C.Saldo = tmp;
C.Tel = reader["contacto_telefonico"].ToString();
listBoxClientes.Items.Add(C);
}
}
Connection.close();
currentClient = 0;
if (listBoxClientes.Items.Count == 0)
{
listBoxClientes.Items.Add("Sem clientes a apresentar");
currentClient = -1;
}
ShowClient();
}
private void loadRevisores(object sender = null, EventArgs e = null)
{
if (!Connection.verifySGBDConnection())
{
return;
}
boxNome = textBoxSearchRevisorNome.Text;
boxApelido = "null";
if (boxNome.Length == 0)
{
boxNome = "null";
}
else if (boxNome.Contains(" "))
{
String[] tmp = boxNome.Split(' ');
boxNome = tmp[0];
boxApelido = tmp[1];
}
boxCC = textBoxSearchRevisorCC.Text;
if (boxCC.Length == 0)
{
boxCC = "null";
}
boxSalarioMin = textBoxSearchRevisorMin.Text;
if (boxSalarioMin.Length == 0)
{
boxSalarioMin = "null";
}
boxSalarioMax = textBoxSearchRevisorMax.Text;
if (boxSalarioMax.Length == 0)
{
boxSalarioMax = "null";
}
String query = "exec getRevisor @cc=" + boxCC + ", @nome=" + boxNome + ", @apelido=" + boxApelido + ", " +
"@salario_min=" + boxSalarioMin + ", @salario_max=" + boxSalarioMax;
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxRevisores.Items.Clear();
while (reader.Read())
{
Revisor r = new Revisor();
r.Apelido = reader["apelido"].ToString();
r.Cc = reader["CC"].ToString();
r.Email = reader["email"].ToString();
r.NomeProprio = reader["nome_proprio"].ToString();
r.Salario = reader["salario"].ToString();
r.Telefone = reader["telefone"].ToString();
listBoxRevisores.Items.Add(r);
}
currentRevisor = 0;
if (listBoxRevisores.Items.Count == 0)
{
listBoxRevisores.Items.Add("Sem revisores a apresentar");
currentRevisor = -1;
}
Connection.close();
ShowRevisor();
}
private void loadFaturas(object sender = null, EventArgs e = null)
{
if (!Connection.verifySGBDConnection())
{
return;
}
String minVal, maxVal, query;
if (textBoxSearchFaturaMin.Text.Length == 0)
{
minVal = "null";
}
else
{
minVal = textBoxSearchFaturaMin.Text;
}
if (textBoxSearchFaturaMax.Text.Length == 0)
{
maxVal = "null";
}
else
{
maxVal = textBoxSearchFaturaMax.Text;
}
String queryDate = "@data=" + datePicked;
if (datePicked != "null")
{
queryDate = "@data='" + datePicked + "'";
}
if (minVal == "null")
{
query = "exec getFaturas @cc=" + Cliente.Escolhido.CC.ToString() + ", " + queryDate + ", @valor_max=" + maxVal;
}
else
{
query = "exec getFaturas @cc=" + Cliente.Escolhido.CC.ToString() + ", " + queryDate + ", @valor_min=" + minVal + ", @valor_max=" + maxVal;
}
if (!Clean.IsClean(query))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxFaturas.Items.Clear();
while (reader.Read())
{
Fatura f = new Fatura();
f.Data = reader["data"].ToString();
f.Descricao = reader["descricao"].ToString();
f.Valor = reader["valor"].ToString();
listBoxFaturas.Items.Add(f);
}
currentFatura = 0;
if (listBoxFaturas.Items.Count == 0)
{
listBoxFaturas.Items.Add("Sem faturas a apresentar");
currentFatura = -1;
}
Connection.close();
ShowFatura();
}
private void loadHorarios(object sender, EventArgs e)
{
if (!Connection.verifySGBDConnection())
{
return;
}
bool PartidaEChegada = true;
if (comboBoxPartida.SelectedItem == null)
{
MessageBox.Show("Partida inválida!");
return;
}
String hora_partida = "null";
String hora_chegada = "null";
String chegada = "null";
String partida = comboBoxPartida.SelectedItem.ToString();
if (comboBoxChegada.SelectedItem == null)
{
PartidaEChegada = false;
}
else
{
chegada = comboBoxChegada.SelectedItem.ToString();
}
if (textBoxSearchHoraPartida.Text.Length > 0)
{
hora_partida = textBoxSearchHoraPartida.Text;
}
if (textBoxSearchHoraChegada.Text.Length > 0)
{
hora_chegada = textBoxSearchHoraChegada.Text;
}
String query;
if (PartidaEChegada)
{
query = "exec horariosPC @partida='" + partida + "', @chegada='" + chegada + "', " +
"@hora_partida=" + hora_partida + ", @hora_chegada=" + hora_chegada;
}
else
{
query = "exec horariosC @partida='" + partida + "', @hora_partida=" + hora_partida;
}
if (!Clean.IsClean(query, "-"))
{
MessageBox.Show(Clean.Err());
return;
}
SqlCommand cmd = new SqlCommand(query, Connection.get());
SqlDataReader reader = cmd.ExecuteReader();
listBoxHorarios.Items.Clear();
while (reader.Read())
{
Horario h = new Horario();
h.Chegada = reader["estacaoC"].ToString();
h.Comboio = reader["comboio"].ToString();
h.HoraChegada = reader["horaC"].ToString();
h.HoraPartida = reader["horaP"].ToString();
h.Linha = reader["linha"].ToString();
h.ParagemChegada = reader["id2"].ToString();
h.ParagemPartida = reader["id1"].ToString();
h.Partida = reader["estacaoP"].ToString();
h.Viagem = reader["viagem"].ToString();
h.Preco = reader["preco"].ToString();
listBoxHorarios.Items.Add(h);
}
currentHorario = 0;
if (listBoxHorarios.Items.Count == 0)
{
listBoxBilhetes.Items.Add("Sem horarios a apresentar");
currentHorario = -1;
}
Connection.close();
ShowHorario();
}