public ProjetoDAO(String tipoUtilizador = null)
{
m_projetos = new List<ProjetoDTO>(50);
//ligar a base de dados e fazer a query para obter todos os utilizadores da BD.
m_db = new SQLiteDatabase();
DataTable dt;
//getting the sequencial number
String query = "SELECT MAX(num_projeto) AS max FROM Projeto";
dt = m_db.GetDataTable(query);
DataRow row = dt.Rows[0];
try
{
m_sequencialNumber = (Int64)row[0];
}
catch (Exception e)
{
String g = e.ToString();
m_sequencialNumber = 0;
}
switch(tipoUtilizador)
{
case "tecnico":
query = "SELECT * FROM VW_ProjetoEstado WHERE num_estado = '1'";//ESTADO 1 = CANDIDATURA
break;
case "gestor":
query = "SELECT * FROM VW_ProjetoEstadoUtilizador WHERE nome_utilizador = '" + GestorLogin.Instance.Utilizador.Nome + "'";
break;
default:
query = "SELECT * FROM VW_ProjetoEstado";
break;
}
dt = m_db.GetDataTable(query);
ProjetoDTO p;
Promotor prom;
//Contacto cont;
foreach (DataRow r in dt.Rows)
{
p = new ProjetoDTO();
p.Numero = (Int64)r["num_projeto"];
p.Nome = (String)r["nome"];
prom = new Promotor();
prom.Nome = (String)r["promotor_nome"];
p.Promotor = prom;
p.Data = (String)r["data_criacao"];
p.MontantePedido = (Int64)r["montante_pedido"];
p.Estado = (String)r["nome_estado"];
m_projetos.Add(p);
}
}
private void getEmailFromID(int mailID)
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", TimeReceived \"TimeReceived\"";
query += "from Mails;";
query += "where ID = " + mailID;
Mails = db.GetDataTable(query);
foreach (DataRow r in Mails.Rows)
{
textBoxBody.Text = r["Body"].ToString();
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
public void test()
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable recipe;
String query = "select NAME \"Name\", DESCRIPTION \"Description\",";
query += "PREP_TIME \"Prep Time\", COOKING_TIME \"Cooking Time\"";
query += "from RECIPE;";
recipe = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
/*
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
MessageBox.Show(r["Name"].ToString());
MessageBox.Show(r["Description"].ToString());
MessageBox.Show(r["Prep Time"].ToString());
MessageBox.Show(r["Cooking Time"].ToString());
}
*/
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
// Mail - Delete
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "delete from Mails where ID = " + selectedMail + ";";
Mails = db.GetDataTable(query);
// Delete from the listView...
listMails();
}
public void GroupRun(SteamFriends.ChatMsgCallback callback, Bot bot, object[] args = null)
{
string query = "select * from Jokes";
SQLiteDatabase db = new SQLiteDatabase();
DataTable jokes;
jokes = db.GetDataTable(query);
List<string> alljokes = new List<string>();
foreach (DataRow dr in jokes.Rows)
{
alljokes.Add(dr["JokeBody"].ToString());
}
bot.ChatroomMessage(bot.chatRoomID, System.Text.RegularExpressions.Regex.Unescape(Util.RandomChoice(alljokes)));
}
public static Dictionary<string, int> getNetworkAliases(FileInfo sqlDB)
{
SQLiteDatabase db = new SQLiteDatabase(sqlDB);
Dictionary<string, int> aliases = new Dictionary<string, int>();
DataTable table = db.GetDataTable("SELECT * FROM " + SQLFieldNames.LOCATION_ALIASES);
foreach (DataRow row in table.Rows)
{
string name = row[SQLFieldNames.LOCATION_ALIASES_NAME].ToString();
string id = row[SQLFieldNames.LOCATION_ALIASES_LOCATION_ID].ToString();
int id_value = int.Parse(id);
if (!aliases.ContainsKey(name))
{
aliases.Add(name, id_value);
}
}
return aliases;
}
public UtilizadorDAO()
{
m_utilizadores = new Dictionary<String, Utilizador>(10);
//ligar a base de dados e fazer a query para obter todos os utilizadores da BD.
m_db = new SQLiteDatabase();
DataTable dt;
String query = "SELECT * FROM Utilizador";
dt = m_db.GetDataTable(query);
//Para cada utilizador vamos introduzir no diccionário para assim termos acesso direto a ele.
Utilizador u;
foreach (DataRow r in dt.Rows)
{
u = new Utilizador((String)r["nome"], (String)r["pass"]);
u.Tipo = (String)r["tipo"];
m_utilizadores.Add(u.Nome, u);
}
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
InsertTesti();
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable recipe;
String query = "select * FROM Unit";
recipe = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
Console.WriteLine("Data:");
Console.WriteLine(recipe.PrimaryKey);
// Or looped through for some other reason
foreach (DataRow r in recipe.Rows)
{
tBox.Text += (r["UnitName"].ToString()) + "\n";
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
/*
var connectionString = @"Data Source=C:\Users\ville\Documents\GitHub\InventoryManagementApp\bin\Debug\inventory.s3db; Version=3";
var connection = new SQLiteConnection(connectionString);
connection.Open();
Console.WriteLine(connection.Database);
Console.WriteLine(connection.LastInsertRowId);
// Execute query on database
string insertSQL = "INSERT INTO Unit (UnitName)values(7, 'nakkimakkara')";
SQLiteCommand insertCommand = new SQLiteCommand(insertSQL, connection);
insertCommand.ExecuteNonQuery();
// Execute query on database
string selectSQL = "SELECT * FROM Unit";
SQLiteCommand selectCommand = new SQLiteCommand(selectSQL, connection);
SQLiteDataReader dataReader = selectCommand.ExecuteReader();
// Iterate every record in the AppUser table
while (!dataReader.Read())
{
tBox.Text+=("User: "******" Username: "******"/n");
}
dataReader.Close();
connection.Close();
/*Material mat = new Material(materialT.Text, Double.Parse(quantityT.Text));
shoplist.AddToList(mat);
tBox.Clear();
List<Material> content = shoplist.Content;
for (int i = 0; i < content.Count; i++)
{
tBox.Text += content[i] + "\n";
}*/
}
private void loadItems(string where="")
{
listView1.Items.Clear();
toolStripButton1.Enabled = false;
toolStripButton2.Enabled = false;
toolStripStatusLabel2.Text = "";
webBrowser1.Navigate("");
try
{
SQLiteDatabase db = new SQLiteDatabase();
String query;
if (where!="")
query = "select * from `history` WHERE "+where+" ORDER BY `date` DESC;";
else query = "select * from `history` ORDER BY `date` DESC;";
DataTable history = db.GetDataTable(query);
foreach (DataRow r in history.Rows)
{
ListViewItem item = new ListViewItem();
item.Text = r["label"].ToString();
item.SubItems.Add(r["link"].ToString());
item.SubItems.Add(r["date"].ToString());
item.SubItems.Add(r["id"].ToString());
listView1.Items.Add(item);
}
toolStripStatusLabel1.Text = history.Rows.Count.ToString() + " item(s) in history";
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
fallyToast.Toaster alertdb = new fallyToast.Toaster();
alertdb.Show("fallyGrab", error, -1, "Fade", "Up", "", "", "error");
toolStripStatusLabel1.Text = "0 item(s) in history";
commonFunctions.writeLog(fail.Message, fail.StackTrace);
}
}
private void GetMails()
{
try
{
textBoxBody.Text = "test";
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", TimeReceived \"TimeReceived\"";
query += "from Mails;";
Mails = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
//textBoxBody.Text = recipe.ToString();
// Or looped through for some other reason
foreach (DataRow r in Mails.Rows)
{
MessageBox.Show(r["Sender"].ToString());
MessageBox.Show(r["Subject"].ToString());
MessageBox.Show(r["Body"].ToString());
MessageBox.Show(r["TimeReceived"].ToString());
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void GetMails()
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", TimeReceived \"TimeReceived\"";
query += "from Mails;";
Mails = db.GetDataTable(query);
foreach (DataRow r in Mails.Rows)
{
//MessageBox.Show(r["Sender"].ToString());
//MessageBox.Show(r["Subject"].ToString());
//MessageBox.Show(r["Body"].ToString());
//MessageBox.Show(r["TimeReceived"].ToString());
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void listMails()
{
// Function to list all mails from our database...
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select ID \"ID\", Subject \"Subject\"";
query += "from Mails;";
//String query = "select Sender \"Sender\", Subject \"Subject\",";
//query += "Body \"Body\", TimeReceived \"TimeReceived\"";
//query += "from Mails;";
Mails = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
//recipeDataGrid.DataSource = recipe;
//textBoxBody.Text = recipe.ToString();
// Or looped through for some other reason
//listViewInbox.Width = 270;
//listViewInbox.Location = new System.Drawing.Point(10, 10);
// Declare and construct the ColumnHeader objects.
ColumnHeader header1, header2;
header1 = new ColumnHeader();
header2 = new ColumnHeader();
// Set the text, alignment and width for each column header.
header1.Text = "File name";
header1.TextAlign = HorizontalAlignment.Left;
header1.Width = 10;
header2.TextAlign = HorizontalAlignment.Left;
header2.Text = "Location";
header2.Width = 20;
// Add the headers to the ListView control.
listViewInbox.Columns.Add(header1);
listViewInbox.Columns.Add(header2);
listViewInbox.View = View.Details;
int test = (3 + 0) / 6 * 100;
MessageBox.Show(test.ToString());
foreach (DataRow r in Mails.Rows)
{
/** /
ListViewItem newItem = new ListViewItem();
newItem.SubItems.Add(r["ID"].ToString());
newItem.SubItems.Add(r["Subject"].ToString());
listViewInbox.Items.Add(newItem);
/**/
//////////
ListViewItem newItem = new ListViewItem(r["Subject"].ToString());
newItem.SubItems.Add(r["ID"].ToString());
listViewInbox.Items.Add(newItem);
/**/
//MessageBox.Show(r["ID"].ToString());
//listViewInbox.Items.Add(r["Subject"].ToString() + " - " + r["Sender"].ToString());
//listViewInbox.Items.Add(r["ID"].ToString());
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void getEmailFromID(int mailID, bool TypeIsRsa, bool decrypt)
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select Sender \"Sender\", Subject \"Subject\",";
query += "Body \"Body\", Timestamp \"Timestamp\"";
query += "from Mails ";
query += "where ID = " + mailID + ";";
Mails = db.GetDataTable(query);
foreach (DataRow r in Mails.Rows)
{
textBoxFrom.Text = r["Sender"].ToString();
textBoxSubject.Text = r["Subject"].ToString();
string bodyText = r["Body"].ToString();
if (TypeIsRsa == false && decrypt == true)
{
try
{
// Decrypt
bodyText = RijndaelSimple.Decrypt(r["Body"].ToString(), // Text to decrypt
textBoxDecrypt.Text, // Any string
"MySaltValue234", // Any string
"SHA1", // SHA1 or MD5
2, // Any number
"p6mdJ84b#siO1JsS", // Must be 16 bytes
256); // 128, 192 or 256
}
catch (Exception e)
{
MessageBox.Show("Incorrect password!");
}
}
else if (TypeIsRsa == true && decrypt == true)
{
UnicodeEncoding byteConverted = new UnicodeEncoding();
byte[] encryptedData = Convert.FromBase64String(r["Body"].ToString());
using (RSACryptoServiceProvider RSA = new RSACryptoServiceProvider())
{
RSA.FromXmlString(Properties.Settings.Default.RSAPrivate);
byte[] decryptedData = RSA.Decrypt(encryptedData, false);
bodyText = byteConverted.GetString(decryptedData);
}
}
else
bodyText = r["Body"].ToString();
textBoxBody.Text = bodyText;
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private void listMails()
{
try
{
listViewInbox.Items.Clear();
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select ID \"ID\", Sender \"Sender\", Subject \"Subject\", Timestamp \"Timestamp\"";
query += "from Mails;";
Mails = db.GetDataTable(query);
foreach (DataRow r in Mails.Rows)
{
ListViewItem newItem = new ListViewItem(r["ID"].ToString());
newItem.SubItems.Add(r["Subject"].ToString());
newItem.SubItems.Add(r["Sender"].ToString());
newItem.SubItems.Add(r["Timestamp"].ToString());
listViewInbox.Items.Add(newItem);
}
}
catch (Exception fail)
{
String error = "The following error has occurred:\n\n";
error += fail.Message.ToString() + "\n\n";
MessageBox.Show(error);
this.Close();
}
}
private bool mailExistFunction(string newMessageID)
{
SQLiteDatabase db = new SQLiteDatabase();
DataTable Mails;
String query = "select ID \"ID\" from Mails where gmailID = '" + newMessageID + "';";
Mails = db.GetDataTable(query);
if (Mails == null || Mails.Rows.Count == 0)
{
return false;
}
return true;
}
