protected void LoadAnimals(UInt32 ClientID)
{
SQLDatabaseManager db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.ANIMAL WHERE IDCLIENTE='{0}'", ClientID);
DataTable temp;
DataTable temp2;
String photoPath = null;
temp = db.GetDataTable(query);
if(temp.Rows.Count < 1)
{
Response.Write("<div class='col-md-4'>");
Response.Write("<h2>Não existem animais</h2>");
Response.Write("<p>Pretende <a href='NewAnimal'>adicionar</a> um novo animal ?</p>");
Response.Write("</div>");
return;
}
foreach (DataRow r in temp.Rows)
{
query = String.Format("SELECT * FROM dbo.FOTOS WHERE IDANIMAL='{0}'", r["IDANIMAL"].ToString());
temp2 = db.GetDataTable(query);
foreach (DataRow x in temp2.Rows)
{
photoPath = x["URL"].ToString();
break;
}
Animal.DrawAnimal(Response, photoPath, r["NOME"].ToString(), r["RACA"].ToString(), Convert.ToUInt32(r["IDADE"].ToString()), Convert.ToUInt32(r["IDANIMAL"].ToString()));
}
}
public static void FillPaymentFields(String PaymentID, TextBox Data, TextBox Metodo, TextBox Montante, TextBox Movimentacao, TextBox Estado)
{
db = new SQLDatabaseManager();
String query = String.Format("UPDATE dbo.PAGAMENTO SET ESTADO='1'WHERE IDPAGAMENTO='{0}'", PaymentID);
db.ExecuteNonQuery(query);
query = String.Format("UPDATE dbo.MARCACAO SET ESTADO='4' WHERE IDPAGAMENTO='{0}'", PaymentID);
db.ExecuteNonQuery(query);
query = String.Format("SELECT * FROM dbo.PAGAMENTO WHERE IDPAGAMENTO='{0}'", PaymentID);
DataTable temp;
temp = db.GetDataTable(query);
foreach(DataRow r in temp.Rows)
{
Data.Text = r["DATA_LIMITE"].ToString();
Metodo.Text = r["METODO"].ToString();
Movimentacao.Text = r["NUMEROMOVIMENTACAO"].ToString();
Montante.Text = r["MONTANTE"].ToString();
if(r["ESTADO"].ToString() == "0")
{
Estado.Text = "Falta pagar";
}
else
{
Estado.Text = "Pago";
}
break;
}
}
public static Boolean RegisterVeterinary(String nome, UInt32 idade, String morada, String especialidade, String email, Double contacto, String password)
{
String SaltKey = SEM.GetSerialKeyAlphaNumaric(8);
db = new SQLDatabaseManager();
Dictionary <String, String> InsertData = new Dictionary <String, String>()
{
{ "NOME", nome },
{ "PASSWORD", SEM.Encrypt(password, SaltKey) },
{ "EMAIL", email },
{ "CONTACTO", Convert.ToString(contacto) },
{ "MORADA", morada },
{ "SEXO", "M" },
{ "HASH", SaltKey },
{ "ESPECIALIDADE", especialidade },
{ "IDADE", Convert.ToString(idade) }
};
if (db.Insert("dbo.VETERINARIO", InsertData))
{
String query = String.Format("SELECT IDVETERINARIO FROM dbo.VETERINARIO WHERE NOME='{0}' AND HASH='{1}' AND EMAIL='{2}'",
nome, SaltKey, email);
DataTable temp;
temp = db.GetDataTable(query);
String LastInsertID = null;
foreach (DataRow r in temp.Rows)
{
LastInsertID = r["IDVETERINARIO"].ToString();
break;
}
InsertData = new Dictionary <String, String>()
{
{ "IDUSER", LastInsertID },
{ "IDPERMISSAO", "2" },
{ "EMAIL", email }
};
if (db.Insert("dbo.ACESSOS", InsertData))
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
public static void LoadAnimals(DropDownList AnimalList, UInt32 ClientID)
{
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable("SELECT IDANIMAL, NOME FROM dbo.ANIMAL WHERE IDCLIENTE='" + ClientID + "'");
AnimalList.DataSource = temp;
AnimalList.DataTextField = temp.Columns["NOME"].ToString();
AnimalList.DataValueField = temp.Columns["IDANIMAL"].ToString();
AnimalList.DataBind();
}
public Boolean SaveChangesToDatabase()
{
db = new SQLDatabaseManager();
String query = String.Format("UPDATE dbo.CLIENTE SET MORADA='{0}', CONTRIBUINTE='{1}', CONTACTO='{2}', BI='{3}', EMAIL='{4}', PASSWORD='******' WHERE IDCLIENTE='{6}'", _address, _fiscal, _contact, _cc, _email, _encryptedpassword, _id);
if (db.ExecuteNonQuery(query) == 1)
{
return(true);
}
return(false);
}
public Boolean HasAnimals()
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.ANIMAL WHERE IDCLIENTE='{0}'", _id);
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
return(false);
}
return(true);
}
public static Boolean AccountExists(String Email, String BI, String Contribuinte)
{
if (Email.Length == 0 && BI.Length == 00 && Contribuinte.Length == 00)
return true;
String query = String.Format("SELECT * FROM dbo.CLIENTE WHERE EMAIL='{0}' OR BI='{1}' OR CONTRIBUINTE='{2}'", Email, BI, Contribuinte);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
return true;
}
return false;
}
public Vehicle(String marca, String modelo, Literal message)
{
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"MARCA", marca},
{"MODELO", modelo},
};
if(db.Insert("dbo.VEICULOS", InsertData))
{
message.Text = "Veículo adicionado com sucesso!";
message.Visible = true;
}
else
{
message.Text = "Erro ao adicionar veículo!";
message.Visible = true;
}
}
public static String ID2Email(UInt32 id)
{
String query = String.Format("SELECT * FROM dbo.CLIENTE WHERE IDCLIENTE='{0}'", id);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count < 1)
{
return(null);
}
foreach (DataRow r in temp.Rows)
{
return(r["EMAIL"].ToString());
}
return(null);
}
public Vehicle(String marca, String modelo, Literal message)
{
db = new SQLDatabaseManager();
Dictionary <String, String> InsertData = new Dictionary <String, String>()
{
{ "MARCA", marca },
{ "MODELO", modelo },
};
if (db.Insert("dbo.VEICULOS", InsertData))
{
message.Text = "Veículo adicionado com sucesso!";
message.Visible = true;
}
else
{
message.Text = "Erro ao adicionar veículo!";
message.Visible = true;
}
}
public Animal(UInt32 IDAnimal)
{
db = new SQLDatabaseManager();
String query = "SELECT * FROM dbo.ANIMAL WHERE IDANIMAL='" + IDAnimal + "'";
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
this.Name = r["NOME"].ToString();
this.Age = Convert.ToUInt32(r["IDADE"].ToString());
this.OwnerID = Convert.ToUInt32(r["IDCLIENTE"].ToString());
this.Race = r["RACA"].ToString();
this.Species = r["ESPECIE"].ToString();
this.Temperature = Convert.ToUInt32(r["TEMPERATURA"].ToString());
this.Type = Convert.ToUInt32(r["TIPO"].ToString());
this.Weight = Convert.ToUInt32(r["PESO"].ToString());
break;
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string value = e.Row.Cells[1].Text;
SQLDatabaseManager db = new SQLDatabaseManager();
String query = String.Format("SELECT NOME FROM dbo.MEDICAMENTOS WHERE IDMEDICAMENTO='{0}'", value);
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
e.Row.Cells[1].Text = r["NOME"].ToString();
break;
}
}
}
public static Boolean AccountExists(String Email, String BI, String Contribuinte)
{
if (Email.Length == 0 && BI.Length == 00 && Contribuinte.Length == 00)
{
return(true);
}
String query = String.Format("SELECT * FROM dbo.CLIENTE WHERE EMAIL='{0}' OR BI='{1}' OR CONTRIBUINTE='{2}'", Email, BI, Contribuinte);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
return(true);
}
return(false);
}
public static Boolean ValidEmail(String Email)
{
if (Email.Length == 0)
{
return(false);
}
String query = String.Format("SELECT * FROM dbo.ACESSOS WHERE EMAIL='{0}'", Email);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
return(true);
}
return(false);
}
public Boolean SaveChangesToDatabase()
{
db = new SQLDatabaseManager();
String query = String.Format("UPDATE dbo.ANIMAL SET NOME='{0}', ESPECIE='{1}', RACA='{2}', IDADE='{3}', PESO='{4}', TEMPERATURA='{5}', TIPO='{6}' WHERE IDANIMAL='{7}'", _name, _species, _race, _age, _weight, _temperature, _type, animalID);
if (db.ExecuteNonQuery(query) == 1)
{
return true;
}
return false;
}
public static void LoadConsultas(UInt32 IDClient, HttpResponse Response)
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.MARCACAO WHERE IDCLIENTE='{0}' AND ESTADO<>'2'", IDClient);
DataTable temp;
temp = db.GetDataTable(query);
if(temp.Rows.Count == 0)
{
Response.Write("Não existem consultas agendadas.");
return;
}
}
public static UInt32 Login(String Email, String Password, HttpResponse Resp)
{
db = new SQLDatabaseManager();
String query = "SELECT * FROM dbo.ACESSOS WHERE EMAIL='" + Email + "'";
DataTable temp;
UInt32 accesslevel = 50;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
return(0);
}
foreach (DataRow r in temp.Rows)
{
accesslevel = Convert.ToUInt32(r["IDPERMISSAO"].ToString());
break;
}
switch (accesslevel)
{
case 1: //Cliente
String SaltKey = null;
query = "SELECT HASH FROM dbo.CLIENTE WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.CLIENTE WHERE EMAIL='" + Email + "' AND PASSWORD='******' AND ESTADO <> -1";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
return(0);
}
foreach (DataRow r in temp.Rows)
{
if (r["ESTADO"].ToString() == "0")
{
Resp.Redirect("~/Account/Activate?Email=" + Email);
return(0);
}
}
return(1);
case 2: //Veterinario
SaltKey = null;
query = "SELECT HASH FROM dbo.VETERINARIO WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.VETERINARIO WHERE EMAIL='" + Email + "' AND PASSWORD='******'";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
return(0);
}
return(2);
case 3: //Admin
SaltKey = null;
query = "SELECT HASH FROM dbo.ADMIN WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.ADMIN WHERE EMAIL='" + Email + "' AND PASSWORD='******'";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
return(0);
}
return(3);
default: //Desconhecido
return(0);
}
}
public static Boolean LoadDrugs(DropDownList DrugsList)
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT NOME, IDMEDICAMENTO FROM dbo.MEDICAMENTOS WHERE DOSE>0");
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return false;
DrugsList.DataSource = temp;
DrugsList.DataTextField = temp.Columns["NOME"].ToString();
DrugsList.DataValueField = temp.Columns["IDMEDICAMENTO"].ToString();
DrugsList.Items.Add("Nenhum medicamento");
DrugsList.DataBind();
return true;
}
public static String UpdateConsult(UInt32 idMarcacao, UInt32 novoEstado, ref GridViewUpdateEventArgs e)
{
db = new SQLDatabaseManager();
switch(novoEstado)
{
case 2:
String query = String.Format("UPDATE dbo.MARCACAO SET ESTADO='{0}' WHERE IDMARCACAO='{1}'", novoEstado, idMarcacao);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
return "Estado alterado com sucesso. Pode prosseguir para o local.";
case 3:
query = String.Format("UPDATE dbo.MARCACAO SET ESTADO='{0}' WHERE IDMARCACAO='{1}'", novoEstado, idMarcacao);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
query = String.Format("SELECT * FROM dbo.VEICULOS WHERE EMUSO = '{0}'", idMarcacao);
DataTable temp;
temp = db.GetDataTable(query);
UInt32 avaiableCar = 0;
foreach(DataRow r in temp.Rows)
{
avaiableCar = Convert.ToUInt32(r["IDVEICULO"].ToString());
break;
}
query = String.Format("UPDATE dbo.VEICULOS SET EMUSO='0' WHERE IDVEICULO='{1}'","0", avaiableCar);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
query = String.Format("SELECT IDCLIENTE, URGENCIA FROM dbo.MARCACAO WHERE IDMARCACAO='{0}'", idMarcacao);
temp = db.GetDataTable(query);
String idClient = null;
String urg = null;
foreach(DataRow r in temp.Rows)
{
idClient = r["IDCLIENTE"].ToString();
urg = r["URGENCIA"].ToString();
break;
}
UInt32 montante = 0;
DateTime dt = DateTime.Now.AddDays(7);
Random random = new Random();
int randomNumber = random.Next(23535, 67456);
if (urg != String.Empty)
montante = 40;
else
montante = 30;
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"MONTANTE", Convert.ToString(montante)},
{"METODO", "Multibanco"},
{"DATA_LIMITE", dt.ToString()},
{"NUMEROMOVIMENTACAO", randomNumber.ToString() },
{"IDMARCACAO", Convert.ToString(idMarcacao)},
{"IDCLIENTE", idClient},
{"ESTADO", "0"}
};
if (!db.Insert("dbo.PAGAMENTO", InsertData))
return "Erro ao processar nota de pagamento.";
query = String.Format("SELECT * FROM dbo.PAGAMENTO WHERE IDMARCACAO='{0}'", idMarcacao);
temp = db.GetDataTable(query);
String idPayment = null;
foreach (DataRow r in temp.Rows)
{
idPayment = r["IDPAGAMENTO"].ToString();
break;
}
query = String.Format("UPDATE dbo.MARCACAO SET IDPAGAMENTO='{0}' WHERE IDMARCACAO='{1}'", idPayment, idMarcacao);
if (db.ExecuteNonQuery(query) > 0)
return "A nota de pagamento foi criada com sucesso e enviada para o cliente em questão.";
else
return "Erro ao processar nota de pagamento.";
case 4:
query = String.Format("UPDATE dbo.MARCACAO SET ESTADO='{0}' WHERE IDMARCACAO='{1}'", novoEstado, idMarcacao);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
return "Todo o processo da consulta foi concluído com sucesso.";
}
return "Erro desconhecido.";
}
public static Boolean isValidOwner(WebsiteUser obj, UInt32 AnimalID)
{
db = new SQLDatabaseManager();
String query = "SELECT * FROM dbo.ANIMAL WHERE IDCLIENTE='" + obj.ClientID + "' AND IDANIMAL='" + AnimalID + "'";
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count > 0)
return true;
return false;
}
public static Boolean Register(DropDownList DropDownTipo, Literal ErrorMessage, params String[] Parameter)
{
if (Parameter.Length != 8)
{
ErrorMessage.Text = "";
return(false);
}
if (DropDownTipo.SelectedValue == "-1")
{
ErrorMessage.Text = "Escolha o tipo de cliente.";
return(false);
}
if (DropDownTipo.SelectedValue == "1")
{
if (Parameter[6].Length != 8)
{
ErrorMessage.Text = "Cartão de Cidadão inválido";
return(false);
}
if (AccountExists(Parameter[2], Parameter[6], "-1"))
{
ErrorMessage.Text = "Conta já existente";
return(false);
}
}
else if (DropDownTipo.SelectedValue == "2")
{
if (Parameter[7].Length != 9)
{
ErrorMessage.Text = "Contribuinte inválido";
return(false);
}
if (AccountExists(Parameter[2], "-1", Parameter[7]))
{
ErrorMessage.Text = "Conta já existente";
return(false);
}
}
if (Parameter[3].Length < 6 || Parameter[3].Length > 15)
{
ErrorMessage.Text = "Contacto inválido";
return(false);
}
String SaltKey = SEM.GetSerialKeyAlphaNumaric(8);
String ActivationCode = SEM.GetSerialKeyAlphaNumaric(16);
db = new SQLDatabaseManager();
Dictionary <String, String> InsertData = new Dictionary <String, String>()
{
{ "NOME", Parameter[0] },
{ "PASSWORD", SEM.Encrypt(Parameter[1], SaltKey) },
{ "EMAIL", Parameter[2] },
{ "CONTACTO", Parameter[3] },
{ "MORADA", Parameter[4] },
{ "SEXO", Parameter[5] },
{ "TIPO", DropDownTipo.SelectedValue.ToString() },
{ "HASH", SaltKey },
{ "CODIGO", ActivationCode },
{ "ESTADO", "0" },
{ "BI", Parameter[6] },
{ "CONTRIBUINTE", Parameter[7] }
};
if (db.Insert("dbo.CLIENTE", InsertData))
{
String query = String.Format("SELECT IDCLIENTE FROM dbo.CLIENTE WHERE NOME='{0}' AND HASH='{1}' AND EMAIL='{2}'",
Parameter[0], SaltKey, Parameter[2]);
DataTable temp;
temp = db.GetDataTable(query);
String LastInsertID = null;
foreach (DataRow r in temp.Rows)
{
LastInsertID = r["IDCLIENTE"].ToString();
break;
}
InsertData = new Dictionary <String, String>()
{
{ "IDUSER", LastInsertID },
{ "IDPERMISSAO", "1" },
{ "EMAIL", Parameter[2] }
};
db.Insert("dbo.ACESSOS", InsertData);
if (Mailer.SendMail(Mailer.CreateMail(Parameter[2], ActivationCode)))
{
ErrorMessage.Text = "Conta criada com sucesso!";
}
else
{
ErrorMessage.Text = "Erro ao enviar email!";
}
}
else
{
ErrorMessage.Text = "Erro ao criar conta!";
return(false);
}
return(true);
}
public static String Id2Name(UInt32 AnimalID)
{
db = new SQLDatabaseManager();
String query = "SELECT NOME FROM dbo.ANIMAL WHERE IDANIMAL='" + AnimalID + "'";
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
return r["NOME"].ToString();
}
return null;
}
public WebsiteUser(String mail, UInt32 level)
{
_email = mail;
_level = level;
db = new SQLDatabaseManager();
DataTable temp;
switch(level)
{
case 1: //Cliente
String query = "SELECT * FROM dbo.CLIENTE WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_type = r["TIPO"].ToString();
_id = Convert.ToUInt32(r["IDCLIENTE"].ToString());
_contact = Convert.ToDouble(r["CONTACTO"].ToString());
_address = r["MORADA"].ToString();
_fiscal = Convert.ToUInt32(r["CONTRIBUINTE"].ToString());
_cc = Convert.ToUInt32(r["BI"].ToString());
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 1;
break;
case 2: //Veterinario
query = "SELECT * FROM dbo.VETERINARIO WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_type = r["ESPECIALIDADE"].ToString();
_id = Convert.ToUInt32(r["IDVETERINARIO"].ToString());
_contact = Convert.ToDouble(r["CONTACTO"].ToString());
_address = r["MORADA"].ToString();
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 2;
break;
case 3: //Admin
query = "SELECT * FROM dbo.ADMIN WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_id = Convert.ToUInt32(r["IDADMIN"].ToString());
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 3;
break;
}
}
public Boolean HasAnimals()
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.ANIMAL WHERE IDCLIENTE='{0}'", _id);
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return false;
return true;
}
public static Boolean RegisterClinicHistory(String idAnimal, String idMedicamento, String ocorrencia)
{
DateTime dt = DateTime.Now;
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"IDMEDICAMENTO", idMedicamento},
{"IDANIMAL", idAnimal},
{"OCORRENCIA", ocorrencia},
{"DATA", dt.ToString() }
};
if (!db.Insert("dbo.HISTORICOCLINICO", InsertData))
return false;
String query = String.Format("UPDATE dbo.MARCACAO SET ESTADO='4' WHERE IDANIMAL='{0}'", idAnimal);
if (db.ExecuteNonQuery(query) == 0)
return false;
return true;
}
public static String RegisterConsult(String data, UInt32 idAnimal, UInt32 urgencia, String morada, UInt32 idCliente, String obs="")
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.MARCACAO WHERE DATA = '{0}'", data);
DataTable temp;
temp = db.GetDataTable(query);
if(temp.Rows.Count > 0)
{
return "Já existe uma marcação nessa hora.";
}
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"DATA", data},
{"LOCAL", morada},
{"URGENCIA", Convert.ToString(urgencia)},
{"IDCLIENTE", Convert.ToString(idCliente)},
{"IDANIMAL", Convert.ToString(idAnimal)},
{"ESTADO", "0"},
{"OBSERVACOES", obs}
};
if (db.Insert("dbo.MARCACAO", InsertData))
return "Marcação efectuada com sucesso.";
else
return "Erro ao efectuar marcação.";
}
public static Boolean RegisterDrug(String nome, String marca, String dose, String principio, String preco)
{
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"NOME", nome},
{"MARCA", marca},
{"DOSE", dose},
{"PRINCIPIO_ACTIVO", principio},
{"PRECO", preco}
};
if (db.Insert("dbo.MEDICAMENTOS", InsertData))
return true;
else
return false;
}
public static String UseVehicle(UInt32 idMarcacao, UInt32 idVet, ref GridViewUpdateEventArgs e)
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT * FROM dbo.VEICULOS WHERE EMUSO = '0'");
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
{
e.Cancel = true;
return "Não existem veículos disponíveis.";
}
UInt32 avaiableCar = 0;
foreach(DataRow r in temp.Rows)
{
avaiableCar = Convert.ToUInt32(r["IDVEICULO"].ToString());
break;
}
query = String.Format("UPDATE dbo.VEICULOS SET EMUSO='{0}' WHERE IDVEICULO='{1}'", idMarcacao, avaiableCar);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
query = String.Format("UPDATE dbo.MARCACAO SET IDVEICULO='{0}', IDVETERINARIO='{1}' WHERE IDMARCACAO='{2}'", avaiableCar, idVet, idMarcacao);
if (db.ExecuteNonQuery(query) < 1)
{
e.Cancel = true;
return "Erro Desconhecido";
}
return "Foi atribuido com sucesso à consulta Nº " + idMarcacao;
}
public static Boolean RegisterVeterinary(String nome, UInt32 idade, String morada, String especialidade, String email, Double contacto, String password)
{
String SaltKey = SEM.GetSerialKeyAlphaNumaric(8);
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"NOME", nome},
{"PASSWORD", SEM.Encrypt(password, SaltKey)},
{"EMAIL", email},
{"CONTACTO", Convert.ToString(contacto)},
{"MORADA", morada},
{"SEXO", "M"},
{"HASH", SaltKey},
{"ESPECIALIDADE", especialidade},
{"IDADE", Convert.ToString(idade)}
};
if (db.Insert("dbo.VETERINARIO", InsertData))
{
String query = String.Format("SELECT IDVETERINARIO FROM dbo.VETERINARIO WHERE NOME='{0}' AND HASH='{1}' AND EMAIL='{2}'",
nome, SaltKey, email);
DataTable temp;
temp = db.GetDataTable(query);
String LastInsertID = null;
foreach (DataRow r in temp.Rows)
{
LastInsertID = r["IDVETERINARIO"].ToString();
break;
}
InsertData = new Dictionary<String, String>()
{
{"IDUSER", LastInsertID},
{"IDPERMISSAO", "2"},
{"EMAIL", email}
};
if (db.Insert("dbo.ACESSOS", InsertData))
return true;
else
return false;
}
else
return false;
}
public static Boolean PaymentExists(String PaymentID)
{
db = new SQLDatabaseManager();
String query = "SELECT IDPAGAMENTO FROM dbo.PAGAMENTO WHERE IDPAGAMENTO='" + PaymentID + "'";
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count > 0)
return true;
return false;
}
public static void LoadSimpleList(UInt32 OwnerID, HttpResponse Response)
{
db = new SQLDatabaseManager();
String query = "SELECT * FROM dbo.ANIMAL WHERE IDCLIENTE='" + OwnerID + "'";
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count < 1)
return;
foreach (DataRow r in temp.Rows)
{
Response.Write("<p> » <a href='\\Animal\\Animal?AnimalID=" + r["IDANIMAL"].ToString() + "'>" + r["NOME"].ToString() + "</a></p>");
}
}
public static Boolean ValidEmail(String Email)
{
if (Email.Length == 0)
return false;
String query = String.Format("SELECT * FROM dbo.ACESSOS WHERE EMAIL='{0}'", Email);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
return true;
}
return false;
}
public static Boolean LoadAnimalsInProgress(DropDownList AnimalList)
{
db = new SQLDatabaseManager();
String query = String.Format("SELECT dbo.ANIMAL.NOME, dbo.ANIMAL.IDANIMAL FROM dbo.ANIMAL, dbo.MARCACAO WHERE dbo.MARCACAO.ESTADO='3' AND dbo.MARCACAO.IDANIMAL=dbo.ANIMAL.IDANIMAL");
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return false;
AnimalList.DataSource = temp;
AnimalList.DataTextField = temp.Columns["NOME"].ToString();
AnimalList.DataValueField = temp.Columns["IDANIMAL"].ToString();
AnimalList.DataBind();
return true;
}
public static Boolean RegisterVehicle(String marca, String modelo)
{
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"MARCA", marca},
{"MODELO", modelo}
};
if (db.Insert("dbo.VEICULOS", InsertData))
return true;
else
return false;
}
public WebsiteUser(String mail, UInt32 level)
{
_email = mail;
_level = level;
db = new SQLDatabaseManager();
DataTable temp;
switch (level)
{
case 1: //Cliente
String query = "SELECT * FROM dbo.CLIENTE WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_type = r["TIPO"].ToString();
_id = Convert.ToUInt32(r["IDCLIENTE"].ToString());
_contact = Convert.ToDouble(r["CONTACTO"].ToString());
_address = r["MORADA"].ToString();
_fiscal = Convert.ToUInt32(r["CONTRIBUINTE"].ToString());
_cc = Convert.ToUInt32(r["BI"].ToString());
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 1;
break;
case 2: //Veterinario
query = "SELECT * FROM dbo.VETERINARIO WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_type = r["ESPECIALIDADE"].ToString();
_id = Convert.ToUInt32(r["IDVETERINARIO"].ToString());
_contact = Convert.ToDouble(r["CONTACTO"].ToString());
_address = r["MORADA"].ToString();
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 2;
break;
case 3: //Admin
query = "SELECT * FROM dbo.ADMIN WHERE EMAIL='" + _email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
_username = r["NOME"].ToString();
_id = Convert.ToUInt32(r["IDADMIN"].ToString());
_encryptedpassword = r["PASSWORD"].ToString();
break;
}
_level = 3;
break;
}
}
public static String ID2Email(UInt32 id)
{
String query = String.Format("SELECT * FROM dbo.CLIENTE WHERE IDCLIENTE='{0}'", id);
db = new SQLDatabaseManager();
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count < 1)
return null;
foreach(DataRow r in temp.Rows)
{
return r["EMAIL"].ToString();
}
return null;
}
public Boolean SaveChangesToDatabase()
{
db = new SQLDatabaseManager();
String query = String.Format("UPDATE dbo.CLIENTE SET MORADA='{0}', CONTRIBUINTE='{1}', CONTACTO='{2}', BI='{3}', EMAIL='{4}', PASSWORD='******' WHERE IDCLIENTE='{6}'", _address, _fiscal, _contact, _cc, _email, _encryptedpassword, _id);
if(db.ExecuteNonQuery(query) == 1)
{
return true;
}
return false;
}
public static UInt32 Login(String Email, String Password, HttpResponse Resp)
{
db = new SQLDatabaseManager();
String query = "SELECT * FROM dbo.ACESSOS WHERE EMAIL='" + Email + "'";
DataTable temp;
UInt32 accesslevel = 50;
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return 0;
foreach (DataRow r in temp.Rows)
{
accesslevel = Convert.ToUInt32(r["IDPERMISSAO"].ToString());
break;
}
switch (accesslevel)
{
case 1: //Cliente
String SaltKey = null;
query = "SELECT HASH FROM dbo.CLIENTE WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.CLIENTE WHERE EMAIL='" + Email + "' AND PASSWORD='******' AND ESTADO <> -1";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return 0;
foreach (DataRow r in temp.Rows)
{
if (r["ESTADO"].ToString() == "0")
{
Resp.Redirect("~/Account/Activate?Email=" + Email);
return 0;
}
}
return 1;
case 2: //Veterinario
SaltKey = null;
query = "SELECT HASH FROM dbo.VETERINARIO WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.VETERINARIO WHERE EMAIL='" + Email + "' AND PASSWORD='******'";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return 0;
return 2;
case 3: //Admin
SaltKey = null;
query = "SELECT HASH FROM dbo.ADMIN WHERE EMAIL='" + Email + "'";
temp = db.GetDataTable(query);
foreach (DataRow r in temp.Rows)
{
SaltKey = r["HASH"].ToString();
break;
}
query = "SELECT * FROM dbo.ADMIN WHERE EMAIL='" + Email + "' AND PASSWORD='******'";
temp = db.GetDataTable(query);
if (temp.Rows.Count == 0)
return 0;
return 3;
default: //Desconhecido
return 0;
}
}
public static Boolean Register(DropDownList DropDownTipo, Literal ErrorMessage, params String[] Parameter)
{
if (Parameter.Length != 8)
{
ErrorMessage.Text = "";
return false;
}
if (DropDownTipo.SelectedValue == "-1")
{
ErrorMessage.Text = "Escolha o tipo de cliente.";
return false;
}
if (DropDownTipo.SelectedValue == "1")
{
if (Parameter[6].Length != 8)
{
ErrorMessage.Text = "Cartão de Cidadão inválido";
return false;
}
if (AccountExists(Parameter[2], Parameter[6], "-1"))
{
ErrorMessage.Text = "Conta já existente";
return false;
}
}
else if (DropDownTipo.SelectedValue == "2")
{
if (Parameter[7].Length != 9)
{
ErrorMessage.Text = "Contribuinte inválido";
return false;
}
if (AccountExists(Parameter[2], "-1", Parameter[7]))
{
ErrorMessage.Text = "Conta já existente";
return false;
}
}
if (Parameter[3].Length < 6 || Parameter[3].Length > 15)
{
ErrorMessage.Text = "Contacto inválido";
return false;
}
String SaltKey = SEM.GetSerialKeyAlphaNumaric(8);
String ActivationCode = SEM.GetSerialKeyAlphaNumaric(16);
db = new SQLDatabaseManager();
Dictionary<String, String> InsertData = new Dictionary<String, String>()
{
{"NOME", Parameter[0]},
{"PASSWORD", SEM.Encrypt(Parameter[1], SaltKey)},
{"EMAIL", Parameter[2]},
{"CONTACTO", Parameter[3]},
{"MORADA", Parameter[4]},
{"SEXO", Parameter[5]},
{"TIPO", DropDownTipo.SelectedValue.ToString()},
{"HASH", SaltKey},
{"CODIGO", ActivationCode},
{"ESTADO", "0"},
{"BI", Parameter[6]},
{"CONTRIBUINTE", Parameter[7]}
};
if (db.Insert("dbo.CLIENTE", InsertData))
{
String query = String.Format("SELECT IDCLIENTE FROM dbo.CLIENTE WHERE NOME='{0}' AND HASH='{1}' AND EMAIL='{2}'",
Parameter[0], SaltKey, Parameter[2]);
DataTable temp;
temp = db.GetDataTable(query);
String LastInsertID = null;
foreach (DataRow r in temp.Rows)
{
LastInsertID = r["IDCLIENTE"].ToString();
break;
}
InsertData = new Dictionary<String, String>()
{
{"IDUSER", LastInsertID},
{"IDPERMISSAO", "1"},
{"EMAIL", Parameter[2]}
};
db.Insert("dbo.ACESSOS", InsertData);
if (Mailer.SendMail(Mailer.CreateMail(Parameter[2], ActivationCode)))
ErrorMessage.Text = "Conta criada com sucesso!";
else
ErrorMessage.Text = "Erro ao enviar email!";
}
else
{
ErrorMessage.Text = "Erro ao criar conta!";
return false;
}
return true;
}
public static Boolean Exists(UInt32 AnimalID)
{
db = new SQLDatabaseManager();
String query = "SELECT NOME FROM dbo.ANIMAL WHERE IDANIMAL='" + AnimalID + "'";
DataTable temp;
temp = db.GetDataTable(query);
if (temp.Rows.Count > 0)
return true;
return false;
}
