public Pupil getPupil(int id)
{
Pupil p = null;
String query = "SELECT * from pupil where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@id", id);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
p = readPupil(id, reader);
}
}
return(p);
}
public void assignPupilToLocker(int id, int pupilID)
{
Locker l = getLockerID(id);
Pupil p = getPupil(pupilID);
//pupil history
InsertHistory(0, p.Id, "locker", "->" + l.Number, DateTime.Now.Ticks);
//locker history
InsertHistory(1, l.Id, "comment", "->" + p.ToString, DateTime.Now.Ticks);
string query = "UPDATE locker set owner_id=@owner_id,status=0 where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@owner_id", pupilID);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
}
}
public void removePupilFromLocker(int id)
{
Locker l = getLockerID(id);
Pupil p = getPupil(l.Owner_id);
Console.WriteLine(id);
Console.WriteLine(l.Number);
//pupil histopry
InsertHistory(0, p.Id, "locker", l.Number + "->", DateTime.Now.Ticks);
//locker history
InsertHistory(1, l.Id, "comment", p.ToString + "->", DateTime.Now.Ticks);
//w
string query = "UPDATE locker set owner_id=null,status=1 where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
}
}
public long addPupil(String FirstName, String LastName, String Class, String Grade, String Year)
{
sem.WaitOne();
long lastID = -1;
Pupil p = new Pupil(-1, Grade, Class, Year, FirstName, LastName, "");
InsertHistory(-1, -1, "added", p.ToString, DateTime.Now.Ticks);
using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO pupil (firstname,lastname,classP,grade,year) VALUES (@firstname,@lastname,@class,@grade,@year)", con))
{
cmd.Parameters.Add("@firstname", DbType.String).Value = FirstName;
cmd.Parameters.Add("@lastname", DbType.String).Value = LastName;
cmd.Parameters.Add("@class", DbType.String).Value = Class;
cmd.Parameters.Add("@grade", DbType.String).Value = Grade;
cmd.Parameters.Add("@year", DbType.String).Value = Year;
cmd.ExecuteNonQuery();
lastID = con.LastInsertRowId;
}
sem.Release();
return(lastID);
}
//TODO add history
public void removePCFromPupil(int id)
{
Computer c = getComputer(id);
Pupil p = getPupil(c.Owner_id);
if (p != null)
{
InsertHistory(0, p.Id, "computer", c.Serielnumber + "->", DateTime.Now.Ticks);
}
if (c != null)
{
InsertHistory(2, c.Id, "comment", p.ToString + "->", DateTime.Now.Ticks);
}
string query = "UPDATE computer set owner_id=null,status=0 where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
}
}
public void UpdatePupil(Pupil p, bool comment)
{
if (comment)
{
//string[] com = p.Comment.Split('\n');
// String history = String.Format("{0} | {1}, Komment: {2}", DateTime.Now.ToString("yyyy-MM-dd_HHmm"), p.ToString, com[com.Length - 1]);
//InsertHistory("pupil", p.Id, "comment", com[com.Length - 1], DateTime.Now.ToString("yyyy-MM-dd_HHmm"));
}
string query = "UPDATE pupil set firstname=@firstname,lastname=@lastname,grade=@grade,classP=@classP where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@firstname", p.Firstname);
cmd.Parameters.AddWithValue("@lastname", p.Lastname);
cmd.Parameters.AddWithValue("@grade", p.Grade);
cmd.Parameters.AddWithValue("@classP", p.Class);
cmd.Parameters.AddWithValue("@id", p.Id);
cmd.ExecuteNonQuery();
}
}
public List <MyItem> getAllLockers(String search, string grade, string classP, string floor, string status)
{
List <MyItem> list = new List <MyItem>();
string lockerQuery = "";
if (floor != "" || status != "")
{
lockerQuery += " where ";
}
bool addAND = false;;
if (floor != "")
{
lockerQuery += " floor=\"" + floor + "\" ";
addAND = true;
}
if (status != "")
{
if (addAND)
{
lockerQuery += " and ";
}
addAND = true;
lockerQuery += " status=" + status + " ";
}
//if (grade != "")
//{
// if (addAND)
// lockerQuery += " and ";
// addAND = true;
// lockerQuery += " grade=\"" + grade + "\"";
//}
//if (classP != "")
//{
// if (addAND)
// lockerQuery += " and ";
// addAND = true;
// lockerQuery += " classP=\"" + classP + "\" ";
//}
string query = "SELECT * from locker " + lockerQuery;
Console.WriteLine(query);
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int owner;
if (!int.TryParse(reader.GetValue(reader.GetOrdinal("owner_id")) + "", out owner))
{
owner = -1;
}
int id = reader.GetInt32(reader.GetOrdinal("id"));
List <History> histories = GetHistory(1, id);
string comment = getHistoryShort(histories, 2);
Pupil p = null;
Locker l = new Locker(
id,
reader.GetInt32(reader.GetOrdinal("number")),
reader.GetInt32(reader.GetOrdinal("keys")),
reader.GetString(reader.GetOrdinal("floor")),
reader.GetInt32(reader.GetOrdinal("status")),
owner,
comment);
// Console.WriteLine(l.Owner_id);
Computer c = new MyItem().C;
if (owner == -1)
{
p = new Pupil(-1, "", "", "", "", "", "");
}
else
{
p = getPupil(owner);
Computer cTmp = getComputerByOwner(owner);
if (cTmp != null)
{
c = cTmp;
}
}
//Console.WriteLine(reader.GetOrdinal("locker.owner_id"));
search = search.ToLower();
string[] parts = search.Split(' ');
bool check = true;
int tmp;
for (int i = 0; i < parts.Length; i++)
{
if (int.TryParse(parts[i], out tmp))
{
if (!l.Number.ToString().Equals(parts[i]))
{
check = false;
}
}
else if (!p.HistoryShort.ToLower().Contains(parts[i]) && !p.Firstname.ToLower().Contains(parts[i]) && !p.Lastname.ToLower().Contains(parts[i]))
{
check = false;
}
}
if (classP != "" && !classP.Equals(p.Class))
{
check = false;
}
// Console.WriteLine(grade + " " + p.Grade);
if (grade != "" && !grade.Equals(p.Grade))
{
check = false;
}
if ((search == "" && grade == "" && classP == "") || check)
{
list.Add(new MyItem
{
P = p,
L = l,
C = c
});
}
}
}
return(list);
}
// origin: pupil=0,locker=1,computer=2,general=-1
public List <History> GetHistory()
{
List <History> list = new List <History>();
using (SQLiteCommand cmd = new SQLiteCommand(
"SELECT * from history ORDER BY date DESC", con))
{
SQLiteDataReader reader = cmd.ExecuteReader();
int cnt = 0;
while (reader.Read() && cnt < 2000)
{
try
{
string type, comment, owner = "";
int owner_id, origin, id;
long date;
id = reader.GetInt32(reader.GetOrdinal("id"));
origin = reader.GetInt32(reader.GetOrdinal("origin"));
type = reader.GetString(reader.GetOrdinal("type"));
comment = reader.GetString(reader.GetOrdinal("comment"));
date = reader.GetInt64(reader.GetOrdinal("date"));
owner_id = reader.GetInt32(reader.GetOrdinal("owner_id"));
if (origin == 0)
{
Pupil p = getPupil(owner_id);
if (p == null)
{
owner = "null";
}
else
{
owner = getPupil(owner_id).ToString;
}
}
else if (origin == 1)
{
owner = getLockerID(owner_id).Number + "";
}
else if (origin == 2)
{
Computer c = getComputer(owner_id);
if (c == null)
{
owner = "null";
}
else
{
owner = c.Serielnumber + "";
}
}
History h = new History(id, origin, type, comment, date, owner);
list.Add(h);
cnt++;
}
catch { }
}
}
return(list);
}
