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