using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Windows;
namespace MagusEvents
{
class Database
{
public SQLiteConnection myConnection;
public Database()
{
try
{
string rootPath = @"c:\temp_acc\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
string path = @"c:\temp_acc\database.sqlite3";
myConnection = new SQLiteConnection("Data Source="+ path);
if (!File.Exists(path))
{
SQLiteConnection.CreateFile(path);
CreateTables_Events();
}
else
{
}
}
catch //(Exception e)
{
//MessageBox.Show(e.ToString());
}
}
public bool ExecuteData(string SQL)
{
try
{
//MessageBox.Show("String = " + SQL);
myConnection.Open();
SQLiteCommand command = new SQLiteCommand(SQL, myConnection);
int row= command.ExecuteNonQuery();
myConnection.Close();
if (row > 0)
{
return true;
}
else
{
return false;
}
}
catch // (Exception e)
{
//MessageBox.Show(e.ToString());
return false;
}
}
///
/// THE IS SECTION IS TO DATA
///
public Person GetImageUpdates(string SQL)
{
Person profile = new Person();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
profile.AASID = Convert.ToInt32(DataReader["AASID"].ToString());
profile.Ref = DataReader["Ref"].ToString();
profile.AAID = Convert.ToInt32(DataReader["AAID"].ToString());
}
}
myConnection.Close();
}
catch// (Exception e)
{
//MessageBox.Show(e.ToString());
}
return profile;
}
public Meals GetFoodProfile(string SQL)
{
Meals profile = new Meals();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
profile.AASID = Convert.ToInt32(DataReader["AASID"].ToString());
profile.Day = Convert.ToInt32(DataReader["Day"].ToString());
profile.Season = Convert.ToInt32(DataReader["Day"].ToString());
profile.Time = DataReader["Time"].ToString();
}
}
myConnection.Close();
}
catch { }
return profile;
}
public string GetList(string SQL)
{
string profile = "";
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
profile += Convert.ToInt32(DataReader["AASID"].ToString()).ToString() +"|";
}
}
myConnection.Close();
}
catch { }
return profile;
}
public Person GetPersonProfile(string SQL)
{
Person profile = new Person ();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
profile.AASID = Convert.ToInt32(DataReader["AASID"].ToString());
profile.AAID = Convert.ToInt32(DataReader["AAID"].ToString());
profile.AC = Convert.ToInt32(DataReader["AC"].ToString());
profile.Ref = DataReader["Ref"].ToString();
profile.FirstName = DataReader["FirstName"].ToString();
profile.Surname = DataReader["Surname"].ToString();
profile.organisation = DataReader["organisation"].ToString();
profile.Role = DataReader["Role"].ToString();
profile.Barcode = DataReader["Barcode"].ToString();
profile.aZones = DataReader["aZones"].ToString();
profile.Category = DataReader["Category"].ToString();
profile.RoleColor = DataReader["RoleColor"].ToString();
profile.User_pic = DataReader["User_pic"].ToString();
}
}
myConnection.Close();
}
catch { }
return profile;
}
public Zones GetZone(string SQL)
{
Zones Zone = new Zones();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Zone.ZID = Convert.ToInt32(DataReader["ZID"].ToString());
Zone.AID = Convert.ToInt32(DataReader["AID"].ToString());
Zone.Number = Convert.ToInt32(DataReader["Number"].ToString());
Zone.Hidden = Convert.ToInt32(DataReader["Hidden"].ToString());
Zone.Lock = Convert.ToInt32(DataReader["Lock"].ToString());
Zone.Active = Convert.ToInt32(DataReader["Active"].ToString());
Zone.ZTID = Convert.ToInt32(DataReader["ZTID"].ToString());
Zone.Name = DataReader["Name"].ToString();
Zone.Color = DataReader["Color"].ToString();
Zone.FromTime = DataReader["FromTime"].ToString();
Zone.ToTime = DataReader["ToTime"].ToString();
}
}
myConnection.Close();
}
catch { }
return Zone;
}
public int GetLockedCount(string AID)
{
int Count = 0;
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
string TIME = DateTime.Now.ToString("HHmm");
string query = "SELECT * FROM Zones WHERE FromTime <='" + TIME + "' AND ToTime >='" + TIME + "' AND Lock ='1' AND Active ='1' AND AID='" + AID + "' ";
sqlite_cmd.CommandText = query;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Count++;
}
}
myConnection.Close();
}
catch { }
return Count;
}
public List GetActiveZonesByArrayLocked(string IDs, string AID)
{
List Zones = new List();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Zones WHERE Lock ='1' AND Active ='1' AND AID='" + AID + "' AND ZID IN(" + IDs + ")";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Zones Zone = new Zones();
Zone.ZID = Convert.ToInt32(DataReader["ZID"].ToString());
Zone.AID = Convert.ToInt32(DataReader["AID"].ToString());
Zone.Number = Convert.ToInt32(DataReader["Number"].ToString());
Zone.Hidden = Convert.ToInt32(DataReader["Hidden"].ToString());
Zone.Lock = Convert.ToInt32(DataReader["Lock"].ToString());
Zone.Active = Convert.ToInt32(DataReader["Active"].ToString());
Zone.Icon = DataReader["Icon"].ToString();
Zone.ZTID = Convert.ToInt32(DataReader["ZTID"].ToString());
Zone.Name = DataReader["Name"].ToString();
Zone.Color = DataReader["Color"].ToString();
Zone.FromTime = DataReader["FromTime"].ToString();
Zone.ToTime = DataReader["ToTime"].ToString();
Zones.Add(Zone);
}
}
myConnection.Close();
}
catch { }
return Zones;
}
public List GetActiveZonesByArray(string IDs,string AID)
{
List Zones = new List();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Zones WHERE Active ='1' AND AID='" + AID + "' AND ZID IN(" + IDs + ")";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Zones Zone = new Zones();
Zone.ZID = Convert.ToInt32(DataReader["ZID"].ToString());
Zone.AID = Convert.ToInt32(DataReader["AID"].ToString());
Zone.Number = Convert.ToInt32(DataReader["Number"].ToString());
Zone.Hidden = Convert.ToInt32(DataReader["Hidden"].ToString());
Zone.Lock = Convert.ToInt32(DataReader["Lock"].ToString());
Zone.Active = Convert.ToInt32(DataReader["Active"].ToString());
Zone.Icon = DataReader["Icon"].ToString();
Zone.ZTID = Convert.ToInt32(DataReader["ZTID"].ToString());
Zone.Name = DataReader["Name"].ToString();
Zone.Color = DataReader["Color"].ToString();
Zone.FromTime = DataReader["FromTime"].ToString();
Zone.ToTime = DataReader["ToTime"].ToString();
Zones.Add(Zone);
}
}
myConnection.Close();
}
catch { }
return Zones;
}
public List GetActiveZones(string ID)
{
List Zones = new List();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Zones WHERE Active ='1' AND AID ='" + ID + "'";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Zones Zone = new Zones();
Zone.ZID = Convert.ToInt32(DataReader["ZID"].ToString());
Zone.AID = Convert.ToInt32(DataReader["AID"].ToString());
Zone.Number = Convert.ToInt32(DataReader["Number"].ToString());
Zone.Hidden = Convert.ToInt32(DataReader["Hidden"].ToString());
Zone.Lock = Convert.ToInt32(DataReader["Lock"].ToString());
Zone.Icon = DataReader["Icon"].ToString();
Zone.ZTID = Convert.ToInt32(DataReader["ZTID"].ToString());
Zone.Active = Convert.ToInt32(DataReader["Active"].ToString());
Zone.Name = DataReader["Name"].ToString();
Zone.Color = DataReader["Color"].ToString();
Zone.FromTime = DataReader["FromTime"].ToString();
Zone.ToTime = DataReader["ToTime"].ToString();
Zones.Add(Zone);
}
}
myConnection.Close();
}
catch
{
}
return Zones;
}
public List GetZonesByArray(string IDs,int hidden)
{
List Zones = new List();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Zones WHERE ZID IN(" + IDs + ")";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Zones Zone = new Zones();
Zone.ZID = Convert.ToInt32(DataReader["ZID"].ToString());
Zone.AID = Convert.ToInt32(DataReader["AID"].ToString());
Zone.Number = Convert.ToInt32(DataReader["Number"].ToString());
Zone.Icon = DataReader["Icon"].ToString();
Zone.ZTID = Convert.ToInt32(DataReader["ZTID"].ToString());
Zone.Hidden = Convert.ToInt32(DataReader["Hidden"].ToString());
Zone.Lock = Convert.ToInt32(DataReader["Lock"].ToString());
Zone.Active = Convert.ToInt32(DataReader["Active"].ToString());
Zone.Name = DataReader["Name"].ToString();
Zone.Color = DataReader["Color"].ToString();
Zone.FromTime = DataReader["FromTime"].ToString();
Zone.ToTime = DataReader["ToTime"].ToString();
Zones.Add(Zone);
}
}
myConnection.Close();
}
catch
{
}
return Zones;
}
public Events GetEvent(string AC)
{
Events Evet = new Events();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Events WHERE AC ='"+ AC +"'";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Evet.AC = Convert.ToInt32(DataReader["AC"].ToString());
Evet.AccreditationName = DataReader["AccreditationName"].ToString();
Evet.Active = Convert.ToInt32(DataReader["Active"].ToString());
Evet.DateFrom = DataReader["DateFrom"].ToString();
Evet.DateTo = DataReader["DateTo"].ToString();
Evet.Location = DataReader["Location"].ToString();
Evet.Logo = DataReader["Logo"].ToString();
Evet.DayNumber = Convert.ToInt32(DataReader["DayNumber"].ToString());
Evet.SessionNumer = Convert.ToInt32(DataReader["SessionNumer"].ToString());
Evet.MealDays = Convert.ToInt32(DataReader["MealDays"].ToString());
Evet.MealsPerDay = Convert.ToInt32(DataReader["MealsPerDay"].ToString());
Evet.IsMealZone = Convert.ToInt32(DataReader["IsMealZone"].ToString());
}
}
myConnection.Close();
}
catch
{
}
return Evet;
}
public List GetEvents(string SQL)
{
List Evets = new List();
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = SQL;
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
Events Evet = new Events();
Evet.AC = Convert.ToInt32(DataReader["AC"].ToString());
Evet.AccreditationName = DataReader["AccreditationName"].ToString();
Evet.Active = Convert.ToInt32(DataReader["Active"].ToString());
Evet.DateFrom = DataReader["DateFrom"].ToString();
Evet.DateTo = DataReader["DateTo"].ToString();
Evet.Location = DataReader["Location"].ToString();
Evets.Add(Evet);
}
}
myConnection.Close();
}
catch
{
}
return Evets;
}
private void CreateTables_Food()
{
try
{
myConnection.Open();
string sql = "CREATE TABLE Food (ID INTEGER PRIMARY KEY AUTOINCREMENT ," +
"AASID INTEGER(11)," +
"Day INTEGER(11)," +
"Season INTEGER(11)," +
"Time VARCHAR(200));";
SQLiteCommand command = new SQLiteCommand(sql, myConnection);
command.ExecuteNonQuery();
myConnection.Close();
}
catch //(Exception Error)
{
//MessageBox.Show(Error.ToString());
}
}
private void CreateTables_Events()
{
try{
myConnection.Open();
string sql = "CREATE TABLE Events (AC INTEGER(11) UNIQUE," +
"AccreditationName Text," +
"DateFrom VARCHAR(200)," +
"DateTo VARCHAR(200)," +
"Location VARCHAR(200)," +
"Logo Text," +
"BarcodeLength INTEGER(11)," +
"EventDate VARCHAR(11)," +
"EventTime VARCHAR(11)," +
"UpdateTriggerOne VARCHAR(11)," +
"UpdateTimeOne VARCHAR(11)," +
"UpdateTriggerTwo VARCHAR(11)," +
"UpdateTimeTwo VARCHAR(11)," +
"IsMealZone INTEGER(11)," +
"DayNumber INTEGER(11)," +
"SessionNumer INTEGER(11)," +
"MealDays INTEGER(11)," +
"MealsPerDay INTEGER(11)," +
"Active INTEGER(11));";
SQLiteCommand command = new SQLiteCommand(sql, myConnection);
command.ExecuteNonQuery();
myConnection.Close();
CreateTables_Zones();
}
catch
{
}
}
private void CreateTables_Zones()
{
try{
myConnection.Open();
string sql = "CREATE TABLE Zones (ZID INTEGER(11) UNIQUE," +
"AID INTEGER(11)," +
"Number INTEGER(11)," +
"Hidden INTEGER(11)," +
"Lock INTEGER(11)," +
"Active INTEGER(11)," +
"Icon TEXT," +
"ZTID INTEGER(11)," +
"Name VARCHAR(200)," +
"Color VARCHAR(200)," +
"FromTime INTEGER(4)," +
"ToTime INTEGER(4));";
SQLiteCommand command = new SQLiteCommand(sql, myConnection);
command.ExecuteNonQuery();
myConnection.Close();
CreateTables_Persons();
}
catch
{
}
}
private void CreateTables_Persons()
{
try{
myConnection.Open();
string sql = "CREATE TABLE Persons (AASID INTEGER(11) UNIQUE," +
"AAID INTEGER(11)," +
"AC INTEGER(11)," +
"Ref VARCHAR(200)," +
"LastUpdate VARCHAR(200)," +
"FirstName VARCHAR(200)," +
"Surname VARCHAR(200)," +
"User_pic TEXT," +
"Category VARCHAR(200)," +
"IDnumber VARCHAR(200)," +
"RoleColor VARCHAR(200)," +
"organisation VARCHAR(200)," +
"Role VARCHAR(200)," +
"Barcode VARCHAR(200)," +
"SCANID VARCHAR(200)," +
"aZones VARCHAR(200));";
SQLiteCommand command = new SQLiteCommand(sql, myConnection);
command.ExecuteNonQuery();
myConnection.Close();
CreateTables_Food();
}
catch
{
}
}
public int GetAccreditationID()
{
int ID = 0;
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Events Where Active =1";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
ID = Convert.ToInt32(DataReader["AC"].ToString());
}
}
myConnection.Close();
}
catch
{
}
return ID;
}
public int GetBarcodeLength()
{
int ID = 0;
try
{
myConnection.Open();
SQLiteCommand sqlite_cmd;
SQLiteDataReader DataReader = null;
sqlite_cmd = myConnection.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM Events Where Active =1";
using (DataReader = sqlite_cmd.ExecuteReader())
{
while (DataReader.Read())
{
ID = Convert.ToInt32(DataReader["BarcodeLength"].ToString());
}
}
myConnection.Close();
}
catch
{
}
return ID;
}
}
}