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);
}