public void updateLocker(Locker l)
{
string query = "UPDATE locker set keys=@keys,status=@status where id=@id";
using (SQLiteCommand cmd = new SQLiteCommand(
query, con))
{
cmd.Parameters.AddWithValue("@keys", l.Keys);
cmd.Parameters.AddWithValue("@status", l.Status);
cmd.Parameters.AddWithValue("@id", l.Id);
cmd.ExecuteNonQuery();
}
}
private void InitTabs(UniformGrid ug)
{
for (int i = 0; i < ug.Children.Count; i++)
{
if (ug.Children[i].GetType() == typeof(Border))
{
try
{
Border b = (Border)ug.Children[i];
TextBlock tb = (TextBlock)b.Child;
tb.FontWeight = FontWeights.Bold;
Locker l = MainWindow.sql.getLocker(int.Parse(tb.Text));
if (l != null)
{
if (l.Keys > 2)
{
tb.Foreground = new SolidColorBrush(Color.FromRgb(0x00, 0x00, 0xAA));
}
else if (l.Keys <= 1)
{
tb.Foreground = new SolidColorBrush(Color.FromRgb(0xAA, 0x00, 0x00));
}
//Tooltip
Pupil p = MainWindow.sql.getPupil(l.Owner_id);
ToolTipService.SetShowDuration(tb, int.MaxValue);
ToolTipService.SetInitialShowDelay(tb, 0);
tb.ToolTip =
"==Nycklar==\n" + l.Keys
+ "\n==Historia==\n" + l.HistoryShort
+ "\n==Status==\n" + l.StatusText;
if (p != null)
{
tb.ToolTip +=
"\n==Elev==\n" + p.Firstname + " " + p.Lastname + ", " + p.Grade + p.Class + ", " + p.Year;
}
}
}
catch { }
}
}
}
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 Locker getLocker(int number)
{
Locker l = null;
using (SQLiteCommand cmd = new SQLiteCommand(
"SELECT * from locker where number=@number", con))
{
cmd.Parameters.AddWithValue("@number", number);
SQLiteDataReader reader = cmd.ExecuteReader();
if (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);
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);
}
}
return(l);
}
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);
}