private static dataBase getDataBase()
{
dataBase db = new dataBase();
if (!db.DatabaseExists())
{
db.CreateDatabase();
}
return db;
}
public UserOperations(dataBase db)
{
this.db = db;
}
private void load_student_btn_Click(object sender, EventArgs e)
{
bool exsit_flag = false;
for (int i = 0; i < student_lst.Items.Count; i++)
{
if (student_lst.Items[i].ToString().ToLower() == student_name_txt.Text.Trim().ToLower())
{
exsit_flag = true;
}
}
if (exsit_flag == true)
{
profilePic_img.Image = null;
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string queryTogetStudentID = "Select id from connection_details Where userName = '******'";
SqlDataAdapter sda6 = new SqlDataAdapter(queryTogetStudentID, con);
DataTable dtb6 = new DataTable();
sda6.Fill(dtb6);
if (dtb6.Rows.Count > 0)
{
monthCalendar1.Visible = true;
student_id = dtb6.Rows[0][0].ToString().Trim();
User student = new User();
student = student.GetUser(student_id);
address_txt.Text = student.getAddress();
birthdate_pckr.Text = student.getBirthDate();
freetext_txt.Text = student.getFreeTxt();
hobies_txt.Text = student.getHobies();
Email_1st.Text = student.getEmail();
phone_txt.Text = student.getPhoneNumber();
PEmail_txt.Text = student.getParentEmail();
/*
* picture
*/
con.Open();
string query2 = "select picture from users where id='" + student_id + "'";
SqlCommand cmd = new SqlCommand(query2, con);
SqlDataReader dataRead = cmd.ExecuteReader();
dataRead.Read();
try
{
byte[] img = null;
if (dataRead.HasRows)
{
img = (byte[])dataRead[0];
}
if (img == null)
{
profilePic_img.Image = null;
}
else
{
MemoryStream memoryStream = new MemoryStream(img);
profilePic_img.Image = Image.FromStream(memoryStream);
}
}
catch
{
}
con.Close();
}
else
{
MessageBox.Show("There is no such a student.");
monthCalendar1.Visible = false;
return;
}
List <ListViewItem> itemlist = new List <ListViewItem>();
listView1.Items.Clear();
int thisday = monthCalendar1.SelectionRange.Start.Day;
int thismonth = monthCalendar1.SelectionRange.Start.Month;
int thisyear = monthCalendar1.SelectionRange.Start.Year;
string dayOfWeek = monthCalendar1.SelectionRange.Start.DayOfWeek.ToString();
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
string id = "'" + student_id + "'";
string query = "Select Event_ID from Events_to_Users where User_ID=" + id;
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtb = new DataTable();
sda.Fill(dtb);
for (int i = 0; i < dtb.Rows.Count; i++)
{
string event_id = "'" + dtb.Rows[i][0].ToString() + "'";
string query2 = "Select * from Events where Event_id=" + event_id + " and event_privacy = 'Public'";
SqlDataAdapter sda2 = new SqlDataAdapter(query2, conn);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
/* 1/15/2019 00:00:00*/
if (dtb2.Rows.Count > 0)
{
if (dtb2.Rows[0]["date"].ToString() == monthCalendar1.SelectionRange.Start.ToString())
{
string hours_end = dtb2.Rows[0]["hours_end"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb2.Rows[0]["hours_start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = dtb2.Rows[0]["minutes_start"].ToString().Trim();
if (minutes_start.Length < 2)
{
minutes_start = "0" + minutes_start;
}
string minutes_end = dtb2.Rows[0]["minutes_end"].ToString().Trim();
if (minutes_end.Length < 2)
{
minutes_end = "0" + minutes_end;
}
ListViewItem item = new ListViewItem(dtb2.Rows[0]["Event_name"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
/*
* choosing color -- need to update if updated.
*/
switch (dtb2.Rows[0]["event_kind"].ToString().Trim())
{
case "Muliplayer":
item.BackColor = Color.DeepSkyBlue;
break;
case "School":
item.BackColor = Color.Red;
break;
case "Regular":
item.BackColor = Color.SteelBlue;
break;
case "system-public":
item.BackColor = Color.Red;
break;
case "HW":
item.BackColor = Color.YellowGreen;
break;
default:
break;
}
//listView1.Items.Add(item);
itemlist.Add(item);
}
}
}
string user_class;
User user = new User();
user = user.GetUser(student_id);
user_class = user.getGrade() + "-" + user.getClassNumber();
string query3 = "Select * from weekly_events where day_in_week='" + dayOfWeek + "' and user_id_OR_class='" + user_class + "'";
SqlDataAdapter sda3 = new SqlDataAdapter(query3, conn);
DataTable dtb3 = new DataTable();
sda3.Fill(dtb3);
for (int i = 0; i < dtb3.Rows.Count; i++)
{
string hours_end = dtb3.Rows[i]["ends"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb3.Rows[i]["start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = "00";
string minutes_end = "00";
ListViewItem item = new ListViewItem(dtb3.Rows[i]["title"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
item.BackColor = Color.Orange;
//listView1.Items.Add(item);
itemlist.Add(item);
}
//MessageBox.Show(itemlist[0].SubItems[1].ToString());
for (int i = 0; i < itemlist.Count - 2; i++)
{
for (int j = 0; j < itemlist.Count - 2; j++)
{
string firsthour = itemlist[j].SubItems[1].ToString();
string secondhour = itemlist[j + 1].SubItems[1].ToString();
if (compairhours(firsthour, secondhour))
{
ListViewItem temp = new ListViewItem();
temp = itemlist[j];
itemlist[j] = itemlist[j + 1];
itemlist[j + 1] = temp;
}
}
}
for (int i = 0; i < itemlist.Count; i++)
{
listView1.Items.Add(itemlist[i]);
}
}
}
private void populate()
{
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
con.Open();
string fullName = friend_name.Text.ToString();
var names = fullName.Split(' ');
if (names.Length != 2)
{
friend_name.Clear();
MessageBox.Show("illegal enter first name and last name separate.");
con.Close();
}
else
{
string firstName = names[0];
string lastName = names[1];
string query = "Select * from users Where name = '" + firstName + "' and LastName = '" + lastName + "'";
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
int flag = 1;
string help_full_name;
for (int i = listView1.Items.Count - 1; i >= 0; i--)
{
help_full_name = listView1.Items[i].Text.ToString().Trim();
if (help_full_name == fullName)
{
flag = 0;
break;
}
}
if (flag == 1 && dtbl.Rows.Count > 0 && dtbl.Rows[0][3].ToString().Trim() == "student" && (dtbl.Rows[0][1].ToString().Trim() + dtbl.Rows[0][2].ToString().Trim()).ToUpper() != log_in_page.loginUserName.ToUpper())
{
byte[] img = null;
if (!dtbl.Rows[0].IsNull("picture"))
{
img = (byte[])dtbl.Rows[0][13];
MemoryStream memoryStream = new MemoryStream(img);
imgs.Images.Add(friend_name.Text.Trim(), Image.FromStream(memoryStream));
}
else
{
listView1.SmallImageList = null;
}
con.Close();
listView1.SmallImageList = imgs;
listView1.Items.Add(friend_name.Text.Trim(), count);
count++;
}
else if (dtbl.Rows.Count > 0 && (dtbl.Rows[0][1].ToString().Trim() + dtbl.Rows[0][2].ToString().Trim()).ToUpper() == log_in_page.loginUserName.ToUpper())
{
MessageBox.Show("You are alreary invented.");
}
else if (dtbl.Rows.Count == 0)
{
MessageBox.Show(firstName + " " + lastName + " dosent exist, try again.");
}
else if (flag == 0)
{
MessageBox.Show("you already invent" + fullName + " .");
}
else
{
MessageBox.Show("You can Invents only your friends.");
}
con.Close();
}
}
private void submit_Click(object sender, EventArgs e)
{
Table.Controls.Clear();
Table.RowStyles.Clear();
Table.Location = new Point(50, 200);
Table.Size = new Size(536, 253);
Table.AutoSize = true;
Table.Name = "Desk";
Table.ColumnCount = 7;
Table.RowCount = 7;
Table.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
Table.GrowStyle = System.Windows.Forms.TableLayoutPanelGrowStyle.AddRows;
this.Controls.Add(Table);
for (int i = 0; i < 6; i++)
{
labels[i] = new Label {
BackColor = Color.Black, ForeColor = Color.White, Dock = DockStyle.Fill
};
}
labels[0].Text = "Sunday";
Table.Controls.Add(labels[0], 1, 0);
labels[1].Text = "Monday";
Table.Controls.Add(labels[1], 2, 0);
labels[2].Text = "Tuesday";
Table.Controls.Add(labels[2], 3, 0);
labels[3].Text = "Wednesday";
Table.Controls.Add(labels[3], 4, 0);
labels[4].Text = "Thursday";
Table.Controls.Add(labels[4], 5, 0);
labels[5].Text = "Friday";
Table.Controls.Add(labels[5], 6, 0);
if (dt.DayOfWeek == DayOfWeek.Sunday)
{
labels[0].Name = (int.Parse(dt.Day.ToString())).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Monday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString())).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Tuesday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString())).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Wednesday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString())).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Thursday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString())).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Friday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString())).ToString();
}
else if (dt.DayOfWeek == DayOfWeek.Saturday)
{
labels[0].Name = (int.Parse(dt.Day.ToString()) + 1).ToString();
labels[1].Name = (int.Parse(dt.Day.ToString()) + 2).ToString();
labels[2].Name = (int.Parse(dt.Day.ToString()) + 3).ToString();
labels[3].Name = (int.Parse(dt.Day.ToString()) + 4).ToString();
labels[4].Name = (int.Parse(dt.Day.ToString()) + 5).ToString();
labels[5].Name = (int.Parse(dt.Day.ToString()) + 6).ToString();
}
for (int i = 6; i < 12; i++)
{
labels[i] = new Label {
BackColor = Color.DarkBlue, ForeColor = Color.White, Dock = DockStyle.Fill
};
}
labels[6].Text = "8-10";
Table.Controls.Add(labels[6], 0, 1);
labels[7].Text = "10-12";
Table.Controls.Add(labels[7], 0, 2);
labels[8].Text = "12-14";
Table.Controls.Add(labels[8], 0, 3);
labels[9].Text = "14-16";
Table.Controls.Add(labels[9], 0, 4);
labels[10].Text = "16-18";
Table.Controls.Add(labels[10], 0, 5);
labels[11].Text = "18-20";
Table.Controls.Add(labels[11], 0, 6);
string fullName = teacher_name.Text.ToString();
var names = fullName.Split(' ');
if (names.Length != 2)
{
teacher_name.Clear();
MessageBox.Show("illegal name, enter first name and last name separate.");
return;
}
global_teacher_name = fullName;
string firstName = names[0];
string lastName = names[1];
string queryteach = "Select * from users Where name = '" + firstName + "' and LastName = '" + lastName + "'";//Take all the teacher information
dataBase dataBaseteach = new dataBase();
SqlConnection conteach = dataBaseteach.connect_to_scheduluz_DB();
SqlDataAdapter sdateach;
DataTable dtblteach;
conteach.Open();
sdateach = new SqlDataAdapter(queryteach, conteach);
dtblteach = new DataTable();
sdateach.Fill(dtblteach);//Teacher user all details
if (dtblteach.Rows.Count == 0)
{
teacher_name.Clear();
MessageBox.Show(fullName + " Does not exist. ");
return;
}
if (dtblteach.Rows[0]["permission"].ToString().Trim() != "teacher")
{
teacher_name.Clear();
MessageBox.Show(fullName + " Not a teacher. ");
return;
}
//the next code take the teacher reception time
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl;
conn.Open();
string query = "Select * from weekly_events where user_id_OR_class=" + dtblteach.Rows[0]["Id"] + "and event_kind='" + "reception_hours" + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
string hours;
int k = 12;
for (int i = 1; i < 7; i++)
{
for (int j = 1; j < 7; j++)
{
labels[k] = new Label {
BackColor = Color.DarkRed, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[k], j, i);
labels[k].Text = labels[i + 5].Text;//the hours text
labels[k].TextAlign = ContentAlignment.MiddleCenter;
labels[k].ForeColor = Color.Black;
for (int m = 0; m < dtbl.Rows.Count; m++)
{
hours = dtbl.Rows[m]["start"].ToString().Trim() + "-" + dtbl.Rows[m]["ends"].ToString().Trim();
if (labels[k].Text.Trim() == hours && labels[k % 6].Text.Trim() == dtbl.Rows[m]["day_in_week"].ToString().Trim())
{
labels[k].BackColor = Color.LightGreen;
labels[k].ForeColor = Color.Black;
}
}
if (labels[k].BackColor == Color.LightGreen)
{/* + "and event_kind='" + "reception_hours" + "'"*/
labels[k].Click += new System.EventHandler(LabelClick);
SqlDataAdapter sda0;
DataTable dtbl0, dtbl1;
string already_exist;
string query0 = "Select * from Events_to_Users where User_Id='" + log_in_page.userId + "'";
sda0 = new SqlDataAdapter(query0, conn);
dtbl0 = new DataTable();
sda0.Fill(dtbl0);
bool flag = false;
for (int m = 0; m < dtbl0.Rows.Count; m++) //***CHECK IF THE EVENT IS ALREADY EXIST.
{ //labels[i].Text.ToString().Trim() == already_exist &&
query0 = "Select * from Events where Event_id = '" + dtbl0.Rows[m]["Event_ID"] + "'";
sda0 = new SqlDataAdapter(query0, conn);
dtbl1 = new DataTable();
sda0.Fill(dtbl1);
already_exist = dtbl1.Rows[0]["hours_start"].ToString().Trim() + "-" + dtbl1.Rows[0]["hours_end"].ToString().Trim();
DateTime.TryParse(dtbl1.Rows[0]["date"].ToString().Trim(), out dt);
if (dtbl1.Rows[0]["event_kind"].ToString().Trim() == "reception_hour" && labels[i + 5].Text.ToString().Trim() == already_exist && labels[k % 6].Name == dt.Day.ToString().Trim())
{
flag = true;
break;
}
}
if (flag)//IF the event already exist.
{
flag = false;
labels[k].BackColor = Color.YellowGreen;
}
//cmd.ExecuteNonQuery();
}
k++;
}
}
}
private void reception_hours_Load(object sender, EventArgs e)
{
TableLayoutPanel Table = new TableLayoutPanel();
Table.Location = new Point(50, 120);
Table.Size = new Size(536, 253);
Table.AutoSize = true;
Table.Name = "Desk";
Table.ColumnCount = 7;
Table.RowCount = 7;
Table.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
Table.GrowStyle = System.Windows.Forms.TableLayoutPanelGrowStyle.AddRows;
this.Controls.Add(Table);
for (int i = 0; i < 6; i++)
{
labels[i] = new Label {
BackColor = Color.Black, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], i + 1, 0);
}
labels[0].Text = "Sunday";
labels[1].Text = "Monday";
labels[2].Text = "Tuesday";
labels[3].Text = "Wednesday";
labels[4].Text = "Thursday";
labels[5].Text = "Friday";
for (int i = 6; i < 12; i++)
{
labels[i] = new Label {
BackColor = Color.DarkBlue, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], 0, i - 5);
}
labels[6].Text = "8-10";
labels[7].Text = "10-12";
labels[8].Text = "12-14";
labels[9].Text = "14-16";
labels[10].Text = "16-18";
labels[11].Text = "18-20";
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl;
conn.Open();
string query = "Select * from weekly_events where user_id_OR_class=" + log_in_page.userId + "and event_kind='" + "reception_hours" + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
string hours;
int k = 12;
for (int i = 1; i < 7; i++)
{
for (int j = 1; j < 7; j++)
{
labels[k] = new Label {
BackColor = Color.DarkRed, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[k], j, i);
labels[k].Click += new System.EventHandler(LabelClick);
labels[k].Text = labels[i + 5].Text;
labels[k].TextAlign = ContentAlignment.MiddleCenter;
labels[k].ForeColor = Color.Black;
for (int m = 0; m < dtbl.Rows.Count; m++)
{
hours = dtbl.Rows[m]["start"].ToString().Trim() + "-" + dtbl.Rows[m]["ends"].ToString().Trim();
if (labels[k].Text.Trim() == hours && labels[k % 6].Text.Trim() == dtbl.Rows[m]["day_in_week"].ToString().Trim())
{
labels[k].BackColor = Color.LightGreen;
}
}
k++;
}
}
}
public bool updateEvent(bool isweekly, string eventid, string title, string place, string day_in_week, DateTime date, string start_hours,
string end_hours, string start_minutes, string end_minutes, string details, string privacy)
{
//try
//{
title.Replace("'", "");
place.Replace("'", "");
details.Replace("'", "");
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
if (isweekly)
{
SqlCommand cmd1 = new SqlCommand("UPDATE weekly_events SET title ='" + title + "' WHERE wEvent_id ='" + eventid + "'", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE weekly_events SET day_in_week ='" + day_in_week + "' WHERE wEvent_id ='" + eventid + "'", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE weekly_events SET start ='" + start_hours + "' WHERE wEvent_id ='" + eventid + "'", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE weekly_events SET end ='" + end_hours + "' WHERE wEvent_id ='" + eventid + "'", conn);
cmd1.ExecuteNonQuery();
}
string detailsForReplacing = details;
detailsForReplacing = detailsForReplacing.Replace("'", string.Empty);
if (!isweekly)
{
SqlCommand cmd1 = new SqlCommand("UPDATE Events SET Event_name ='" + title + "' WHERE Event_id =" + eventid, conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET Event_name ='" + title + "' WHERE Event_id =" + eventid, conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET Event_details ='" + detailsForReplacing + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET event_place ='" + place + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET event_privacy ='" + privacy + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET date ='" + date.ToShortDateString() + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET hours_start ='" + start_hours + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET hours_end ='" + end_hours + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET minutes_start ='" + start_minutes + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("UPDATE Events SET minutes_end ='" + end_minutes + "' WHERE Event_id =" + eventid + "", conn);
cmd1.ExecuteNonQuery();
}
return(true);
//}
//catch
//{
// return false;
//}
}
public User GetUser(string userId)
{
id = userId;
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query_name = "Select name from users Where id = '" + userId + "'";
SqlDataAdapter sda = new SqlDataAdapter(query_name, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
name = dtbl.Rows[0][0].ToString().Trim();
query_name = "Select lastName from users Where id = '" + userId + "'";
sda = new SqlDataAdapter(query_name, con);
dtbl = new DataTable();
sda.Fill(dtbl);
lastName = dtbl.Rows[0][0].ToString().Trim();
string query2 = "Select address from users Where id = '" + userId + "'";
SqlDataAdapter sda2 = new SqlDataAdapter(query2, con);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
address = dtb2.Rows[0][0].ToString().Trim();
string query3 = "Select permission from users Where id = '" + userId + "'";
SqlDataAdapter sda3 = new SqlDataAdapter(query3, con);
DataTable dtb3 = new DataTable();
sda3.Fill(dtb3);
permission = dtb3.Rows[0][0].ToString().Trim();
string query4 = "Select phoneNumber from users Where id = '" + userId + "'";
SqlDataAdapter sda4 = new SqlDataAdapter(query4, con);
DataTable dtb4 = new DataTable();
sda4.Fill(dtb4);
phoneNumber = dtb4.Rows[0][0].ToString().Trim();
string query5 = "Select Email from users Where id = '" + userId + "'";
SqlDataAdapter sda5 = new SqlDataAdapter(query5, con);
DataTable dtb5 = new DataTable();
sda5.Fill(dtb5);
Email = dtb5.Rows[0][0].ToString().Trim();
if (permission != "manager")
{
string query6 = "Select ParentEmail from users Where id = '" + userId + "'";
SqlDataAdapter sda6 = new SqlDataAdapter(query6, con);
DataTable dtb6 = new DataTable();
sda6.Fill(dtb6);
ParentEmail = dtb6.Rows[0][0].ToString().Trim();
string query8 = "Select grade from users Where id = '" + userId + "'";
SqlDataAdapter sda8 = new SqlDataAdapter(query8, con);
DataTable dtb8 = new DataTable();
sda8.Fill(dtb8);
grade = dtb8.Rows[0][0].ToString().Trim();
string query9 = "Select classNumber from users Where id = '" + userId + "'";
SqlDataAdapter sda9 = new SqlDataAdapter(query9, con);
DataTable dtb9 = new DataTable();
sda9.Fill(dtb9);
classNumber = dtb9.Rows[0][0].ToString().Trim();
}
string query7 = "Select birthDate from users Where id = '" + userId + "'";
SqlDataAdapter sda7 = new SqlDataAdapter(query7, con);
DataTable dtb7 = new DataTable();
sda7.Fill(dtb7);
birthDate = dtb7.Rows[0][0].ToString().Trim();
string query10 = "Select hobies from users Where id = '" + userId + "'";
SqlDataAdapter sda10 = new SqlDataAdapter(query10, con);
DataTable dtb10 = new DataTable();
sda10.Fill(dtb10);
hobies = dtb10.Rows[0][0].ToString().Trim();
string query11 = "Select freeTxt from users Where id = '" + userId + "'";
SqlDataAdapter sda11 = new SqlDataAdapter(query11, con);
DataTable dtb11 = new DataTable();
sda11.Fill(dtb11);
freeTxt = dtb11.Rows[0][0].ToString().Trim();
string query12 = "Select userName from connection_details Where id = '" + userId + "'";
SqlDataAdapter sda12 = new SqlDataAdapter(query12, con);
DataTable dtb12 = new DataTable();
sda12.Fill(dtb12);
username = dtb12.Rows[0][0].ToString().Trim();
string query13 = "Select password from connection_details Where id = '" + userId + "'";
SqlDataAdapter sda13 = new SqlDataAdapter(query13, con);
DataTable dtb13 = new DataTable();
sda13.Fill(dtb13);
password = dtb13.Rows[0][0].ToString().Trim();
return(this);
}
public void DeleteUser(string userId)
{
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl, dtbl1;
con.Open();
string query = "Select * from Events_to_Users where User_ID='" + userId + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)//Delete regular events
{
int eve_id = int.Parse(dtbl.Rows[i]["Event_ID"].ToString().Trim());
SqlCommand cmd = new SqlCommand("DELETE FROM Events_to_Users WHERE Event_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
query = "Select * from Events_to_Users where Event_ID='" + eve_id + "'";
sda = new SqlDataAdapter(query, con);
dtbl1 = new DataTable();
sda.Fill(dtbl1);
if (dtbl1.Rows.Count == 0)
{
cmd = new SqlCommand("DELETE FROM Events WHERE Event_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
}
}
query = "Select * from weekly_events_to_users where User_ID='" + userId + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)//Delete reception hour weekly events
{
int eve_id = int.Parse(dtbl.Rows[i]["wEvent_id"].ToString().Trim());
SqlCommand cmd = new SqlCommand("DELETE FROM weekly_events_to_Users WHERE wEvent_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM Events WHERE Event_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
}
query = "Select * from weekly_events where user_id_OR_class='" + userId + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)//Delete all weekly events
{
int eve_id = int.Parse(dtbl.Rows[i]["wEvent_id"].ToString().Trim());
SqlCommand cmd = new SqlCommand("DELETE FROM weekly_events WHERE wEvent_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
}
query = "Select * from weekly_events where user_id_OR_class='" + userId + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)//Delete all weekly events
{
int eve_id = int.Parse(dtbl.Rows[i]["wEvent_id"].ToString().Trim());
SqlCommand cmd = new SqlCommand("DELETE FROM weekly_Events WHERE Event_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
}
query = "Select * from Request_to_cancel where user_id='" + userId + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)//Delete all Request to cancel
{
int eve_id = int.Parse(dtbl.Rows[i]["wEvent_id"].ToString().Trim());
SqlCommand cmd = new SqlCommand("DELETE FROM Request_to_cancel WHERE wEvent_ID =" + eve_id, con);
cmd.ExecuteNonQuery();
}
SqlCommand cmd1 = new SqlCommand("DELETE FROM users WHERE Id ='" + userId + "'", con);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("DELETE FROM connection_details WHERE Id ='" + userId + "'", con);
cmd1.ExecuteNonQuery();
}
private void ask_for_cancelation_Load(object sender, EventArgs e)
{
Table.Controls.Clear();
Table.RowStyles.Clear();
Table.Location = new Point(70, 90);
Table.Size = new Size(536, 250);
Table.AutoSize = true;
Table.Name = "Desk";
Table.ColumnCount = 7;
Table.RowCount = 13;
Table.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
Table.GrowStyle = System.Windows.Forms.TableLayoutPanelGrowStyle.AddRows;
Table.CellBorderStyle = TableLayoutPanelCellBorderStyle.InsetDouble;
this.Controls.Add(Table);
for (int i = 0; i < 6; i++)
{
labels[i] = new Label {
BackColor = Color.DarkGreen, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], i + 1, 0);
}
labels[0].Text = "Sunday";
labels[1].Text = "Monday";
labels[2].Text = "Tuesday";
labels[3].Text = "Wednesday";
labels[4].Text = "Thursday";
labels[5].Text = "Friday";
for (int i = 6; i < 18; i++)
{
labels[i] = new Label {
BackColor = Color.Green, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], 0, i - 5);
}
labels[6].Text = "8-9";
labels[7].Text = "9-10";
labels[8].Text = "10-11";
labels[9].Text = "11-12";
labels[10].Text = "12-13";
labels[11].Text = "13-14";
labels[12].Text = "14-15";
labels[13].Text = "15-16";
labels[14].Text = "16-17";
labels[15].Text = "17-18";
labels[16].Text = "18-19";
labels[17].Text = "19-20";
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl, dt_reqest, db_delete;
conn.Open();
string query = "Select * from weekly_events where user_id_OR_class='" + log_in_page.userId + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
query = "Select * from Request_to_cancel";
sda = new SqlDataAdapter(query, conn);
dt_reqest = new DataTable();
sda.Fill(dt_reqest);
string hours;
int k = 18;
for (int i = 1; i < 13; i++)
{
for (int j = 1; j < 7; j++)
{
labels[k] = new Label {
Text = "", BackColor = Color.LightGreen, Dock = DockStyle.Fill, TextAlign = ContentAlignment.MiddleCenter
};
Table.Controls.Add(labels[k], j, i);
for (int m = 0; m < dtbl.Rows.Count; m++)
{
hours = dtbl.Rows[m]["start"].ToString().Trim() + "-" + dtbl.Rows[m]["ends"].ToString().Trim();
if (labels[i + 5].Text.Trim() == hours && labels[k % 6].Text.Trim() == dtbl.Rows[m]["day_in_week"].ToString().Trim())
{
labels[k].Text = dtbl.Rows[m]["title"].ToString().Trim();
for (int l = 0; l < dt_reqest.Rows.Count; l++)
{
if (dtbl.Rows[m]["wEvent_id"].ToString().Trim() == dt_reqest.Rows[l]["wEvent_id"].ToString().Trim() && dt_reqest.Rows[l]["approved_condition"].ToString().Trim() != "approved")
{
if (dt_reqest.Rows[l]["approved_condition"].ToString().Trim() == "rejected")
{
labels[k].BackColor = Color.Red;
}
else if (dt_reqest.Rows[l]["approved_condition"].ToString().Trim() == "approved")
{
labels[k].Text = "approved";
labels[k].BackColor = Color.LimeGreen;
}
else if (dt_reqest.Rows[l]["approved_condition"].ToString().Trim() == "send")
{
labels[k].Text = "Request sent";
labels[k].BackColor = Color.Yellow;
}
}
}
for (int l = 0; l < dt_reqest.Rows.Count; l++)
{
query = "Select * from Request_to_cancel where approved_condition= '" + "approved" + "'";
sda = new SqlDataAdapter(query, conn);
db_delete = new DataTable();
sda.Fill(db_delete);
if (db_delete.Rows.Count != 0 && dt_reqest.Rows[l]["approved_condition"].ToString().Trim() == "approved")
{
labels[k].Text = "approved";
labels[k].BackColor = Color.LimeGreen;
labels[k].Click += new System.EventHandler(LabelClick);
SqlCommand cmd = new SqlCommand("DELETE FROM Request_to_cancel WHERE wEvent_id ='" + dt_reqest.Rows[l]["wEvent_id"] + "'", conn);
cmd.ExecuteNonQuery();
}
}
string id = dtbl.Rows[m]["wEvent_id"].ToString().Trim();
labels[k].Click += new System.EventHandler(LabelClick);
}
}
k++;
}
}
}
private void My_schedule_Load(object sender, EventArgs e)
{
Table.Controls.Clear();
Table.RowStyles.Clear();
Table.Location = new Point(70, 90);
Table.Size = new Size(536, 250);
Table.AutoSize = true;
Table.Name = "Desk";
Table.ColumnCount = 7;
Table.RowCount = 13;
Table.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
Table.GrowStyle = System.Windows.Forms.TableLayoutPanelGrowStyle.AddRows;
Table.CellBorderStyle = TableLayoutPanelCellBorderStyle.InsetDouble;
this.Controls.Add(Table);
for (int i = 0; i < 6; i++)
{
labels[i] = new Label {
BackColor = Color.DodgerBlue, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], i + 1, 0);
}
labels[0].Text = "Sunday";
labels[1].Text = "Monday";
labels[2].Text = "Tuesday";
labels[3].Text = "Wednesday";
labels[4].Text = "Thursday";
labels[5].Text = "Friday";
for (int i = 6; i < 18; i++)
{
labels[i] = new Label {
BackColor = Color.DodgerBlue, ForeColor = Color.White, Dock = DockStyle.Fill
};
Table.Controls.Add(labels[i], 0, i - 5);
}
labels[6].Text = "8-9";
labels[7].Text = "9-10";
labels[8].Text = "10-11";
labels[9].Text = "11-12";
labels[10].Text = "12-13";
labels[11].Text = "13-14";
labels[12].Text = "14-15";
labels[13].Text = "15-16";
labels[14].Text = "16-17";
labels[15].Text = "17-18";
labels[16].Text = "18-19";
labels[17].Text = "19-20";
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl;
conn.Open();
string query = "Select * from users where Id='" + log_in_page.userId + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
query = "Select * from weekly_events where user_id_OR_class='" + dtbl.Rows[0]["grade"] + '-' + dtbl.Rows[0]["classNumber"] + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
string hours;
int k = 18;
for (int i = 1; i < 13; i++)
{
for (int j = 1; j < 7; j++)
{
labels[k] = new Label {
BackColor = Color.LightBlue, Dock = DockStyle.Fill, TextAlign = ContentAlignment.MiddleCenter /*, Size = new Size(30,10)*/
};
if (i % 2 == 0)
{
labels[k].BackColor = Color.LightSkyBlue;
}
Table.Controls.Add(labels[k], j, i);
for (int m = 0; m < dtbl.Rows.Count; m++)
{
hours = dtbl.Rows[m]["start"].ToString().Trim() + "-" + dtbl.Rows[m]["ends"].ToString().Trim();
if (labels[i + 5].Text.Trim() == hours && labels[k % 6].Text.Trim() == dtbl.Rows[m]["day_in_week"].ToString().Trim())
{
labels[k].Text = dtbl.Rows[m]["title"].ToString().Trim();
labels[k].BorderStyle = BorderStyle.Fixed3D;
}
}
k++;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string HobiesForReplacing = hobies_txt.Text;
HobiesForReplacing = HobiesForReplacing.Replace("'", string.Empty);
string FreeTextForReplacing = freetext_txt.Text;
FreeTextForReplacing = FreeTextForReplacing.Replace("'", string.Empty);
string AddressForReplace = address_txt.Text;
AddressForReplace = AddressForReplace.Replace("'", string.Empty);
string PhoneForReplace = phone_txt.Text;
PhoneForReplace = PhoneForReplace.Replace("'", string.Empty);
string EmailForReplace = Email_1st.Text;
EmailForReplace = EmailForReplace.Replace("'", string.Empty);
string PEmailForReplace = PEmail_txt.Text;
PEmailForReplace = PEmailForReplace.Replace("'", string.Empty);
string PasswordForReplace = new_pass_txt.Text;
PasswordForReplace = PasswordForReplace.Replace("'", string.Empty);
CheckDetails check = new CheckDetails();
if (new_pass_txt.Text == userId)
{
MessageBox.Show("Your password is the same as your id, please change it.");
}
else if (new_pass_txt.Text != confirm_new_pass_txt.Text)
{
MessageBox.Show("Passwords do not match.");
}
else if (!check.CheckEmail(Email_1st.Text))
{
MessageBox.Show("Email is not valid!");
}
else if (phone_txt.Text == "")
{
MessageBox.Show("Please update your phone number.");
}
else if (!check.Check_onlyNums(phone_txt.Text) || phone_txt.Text.Length != 10)
{
MessageBox.Show("Invalid phone number.");
}
else if (!check.CheckEmail(PEmail_txt.Text) && log_in_page.permission == "student")
{
MessageBox.Show("Parent Email is not valid!");
}
else if (PEmail_txt.Text == Email_1st.Text)
{
MessageBox.Show("Parent Email is the same as yours.");
}
else
{
try
{
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("UPDATE users SET phoneNumber ='" + PhoneForReplace + "' WHERE id ='" + userId + "'", conn);
cmd1.ExecuteNonQuery();
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd2 = new SqlCommand("UPDATE users SET Email ='" + EmailForReplace + "' WHERE id ='" + userId + "'", conn);
cmd2.ExecuteNonQuery();
if (log_in_page.permission == "student")
{
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd3 = new SqlCommand("UPDATE users SET ParentEmail ='" + PEmailForReplace + "' WHERE id ='" + userId + "'", conn);
cmd3.ExecuteNonQuery();
}
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd6 = new SqlCommand("UPDATE users SET address ='" + AddressForReplace + "' WHERE id ='" + userId + "'", conn);
cmd6.ExecuteNonQuery();
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd7 = new SqlCommand("UPDATE users SET birthDate ='" + birthdate_pckr.Text + "' WHERE id ='" + userId + "'", conn);
cmd7.ExecuteNonQuery();
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd8 = new SqlCommand("UPDATE users SET freeTxt ='" + FreeTextForReplacing + "' WHERE id ='" + userId + "'", conn);
cmd8.ExecuteNonQuery();
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd9 = new SqlCommand("UPDATE users SET hobies ='" + HobiesForReplacing + "' WHERE id ='" + userId + "'", conn);
cmd9.ExecuteNonQuery();
if (new_pass_txt.Text != "")
{
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd10 = new SqlCommand("UPDATE connection_details SET password ='******' WHERE id ='" + userId + "'", conn);
cmd10.ExecuteNonQuery();
}
/* adding the image - dont ask*/
if (imgLocation != "")
{
byte[] images = null;
FileStream stream = new FileStream(imgLocation, FileMode.Open, FileAccess.Read);
BinaryReader brs = new BinaryReader(stream);
images = brs.ReadBytes((int)stream.Length);
conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
string sqlQuery = "UPDATE users SET picture =@images WHERE id ='" + userId + "'";
cmd = new SqlCommand(sqlQuery, conn);
cmd.Parameters.Add(new SqlParameter("@images", images));
int N = cmd.ExecuteNonQuery();
}
MessageBox.Show("Your Details has been updated." + userId);
if (per == "manager")
{
ManagerCalander managerCalander = new ManagerCalander();
managerCalander.Show();
Visible = false;
}
else if (per == "teacher")
{
TeacherCalander teacherCalander = new TeacherCalander();
teacherCalander.Show();
Visible = false;
}
else
{
StudentCalander frmCal = new StudentCalander();
frmCal.Show();
Visible = false;
}
}
catch
{
MessageBox.Show("Problem detected. please contact your Scheduluz Guide for more information.");
}
}
}
private void button1_Click_2(object sender, EventArgs e)
{
bool user_exsist = false;
try
{
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query = "Select * from connection_details Where userName = '******' or userName = '******'";
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
if (dtbl.Rows.Count > 0)
{
user_exsist = true;
}
}
catch
{
MessageBox.Show("Problem detected. please contact your Scheduluz Guide for more information.");
}
string per;
if (Student.Checked)
{
per = "student";
}
else if (Teacher.Checked)
{
per = "teacher";
}
else if (Manager.Checked)
{
per = "manager";
}
else
{
per = "none";
}
try
{
if (userIDtextBox.Text[0] == '0')
{
throw new System.ArgumentException("Parameter cannot be null", "original");
}
if (userIDtextBox.Text.Length == 0 || userFirstNameTextBox.Text.Length == 0 || userLastNameTextBox.Text.Length == 0)
{
MessageBox.Show("please fill all the fileds");
}
else if (user_exsist == false)
{
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
con.Open();
SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO connection_details (id,userName,password,permission) VALUES (@id,@userName,@password,@permission)", con);
cmd.Parameters.Add("@id", userIDtextBox.Text);
cmd.Parameters.Add("@userName", userFirstNameTextBox.Text + userLastNameTextBox.Text);
cmd.Parameters.Add("@password", userIDtextBox.Text);
cmd.Parameters.Add("@permission", per);
cmd.ExecuteNonQuery();
dataBase dataBase2 = new dataBase();
SqlConnection con2 = dataBase.connect_to_scheduluz_DB();
con2.Open();
SqlCommand cmd2;
cmd2 = new SqlCommand("INSERT INTO users (id,name,lastName,permission) VALUES (@id,@name,@lastName,@permission)", con2);
cmd2.Parameters.Add("@id", userIDtextBox.Text);
cmd2.Parameters.Add("@name", userFirstNameTextBox.Text);
cmd2.Parameters.Add("@lastName", userLastNameTextBox.Text);
//cmd2.Parameters.Add("@password", userIDtextBox.Text);
cmd2.Parameters.Add("@permission", per);
cmd2.ExecuteNonQuery();
MessageBox.Show("has added");
userFirstNameTextBox.Clear();
userLastNameTextBox.Clear();
userIDtextBox.Clear();
}
else
{
MessageBox.Show(userFirstNameTextBox.Text.Trim() + " " + userLastNameTextBox.Text.Trim() + " already exsists in the system");
}
}
catch (System.ArgumentException)
{
MessageBox.Show("ID can't start with 0.");
}
catch
{
MessageBox.Show("Problem detected. this id might be registered already.");
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query = "Select permission from connection_details Where userName = '******' and Password = '******'";
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
SqlConnection con2 = dataBase.connect_to_scheduluz_DB();
string query2 = "Select id from connection_details Where userName = '******' and Password = '******'";
SqlDataAdapter sda2 = new SqlDataAdapter(query2, con2);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
/*creating text file for later use.
* for permission only
* M for manager
* T for teacher
* S for student
*/
// StreamWriter perFile = new StreamWriter("permissionFile.txt");
// StreamWriter userFile = new StreamWriter("userFile.txt");
if (dtbl.Rows.Count > 0)
{
/*
* the next code part is storing the user permission for later use
*/
User user = new User();
user.insertID(dtb2.Rows[0][0].ToString().Trim());
user.insertPermission(dtbl.Rows[0][0].ToString().Trim());
user.insertUsername(textBox1.Text.Trim());
user.insertPassword(textBox2.Text.Trim());
userId = user.getID();
permission = user.getPermission();
if (user.getPassword() != user.getID())
{
string per = user.getPermission();
if (per == "manager")
{
ManagerCalander Mc = new ManagerCalander();
this.Hide();
// perFile.Write("M");
loginUserName = textBox1.Text;
insert_logins_report();
//perFile.Close();
Mc.Show();
}
if (per == "teacher")
{
TeacherCalander Tc = new TeacherCalander();
this.Hide();
// perFile.Write("T");
loginUserName = textBox1.Text;
insert_logins_report();
//perFile.Close();
Tc.Show();
}
if (per == "student")
{
StudentCalander Sc = new StudentCalander();
this.Hide();
// perFile.Write("S");
loginUserName = textBox1.Text;
insert_logins_report();
// perFile.Close();
Sc.Show();
}
/* userFile.WriteLine(user.getID());
* userFile.WriteLine(user.getPassword());
* userFile.WriteLine(user.getPermission());
* userFile.WriteLine("username: "******"username: "******"Invalid username or password.");
// perFile.Close();
// userFile.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("Connection problem detected. please contact your ScheduLuz guide with this code:" + ex.GetType().ToString());
}
}
private void Submit_Click(object sender, EventArgs e)
{
if (question_txt.Text == "")
{
MessageBox.Show("please fill the title");
}
else if (answersOpA.Text == "")
{
MessageBox.Show("please fill the answers A");
}
else if (answersOpB.Text == "")
{
MessageBox.Show("please fill the answers B");
}
else if (answersOpC.Text == "")
{
MessageBox.Show("please fill the answers C");
}
else if (answersOpD.Text == "")
{
MessageBox.Show("please fill the answers D");
}
else
{
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
//SqlCommand cmd = new SqlCommand("INSERT INTO Events(Event_name,event_kind,date,hours_start,minutes_start,hours_end,minutes_end,Event_details,event_place,up_for_cancellation) VALUES(@Event_name,@event_kind,@date,@hours_start,@minutes_start,@hours_end,@minutes_end,@Event_details,@Place,@up_for_cancellation) ", conn);
SqlCommand cmd = new SqlCommand("INSERT INTO surveys(sender_id,date,grade,class,question,answerA,answerB,answerC,answerD,resultA,resultB,resultC,resultD) VALUES(@sender_id,@date,@grade,@class,@question,@answerA,@answerB,@answerC,@answerD,@resultA,@resultB,@resultC,@resultD) ", conn);
cmd.Parameters.Add("@sender_id", int.Parse(log_in_page.userId));
DateTime dt = DateTime.Today;
cmd.Parameters.Add("@date", dt);
cmd.Parameters.Add("@grade", grade_cb.Text);
string clas = class_num_cb.Text.ToString();
cmd.Parameters.Add("@class", int.Parse(clas));
cmd.Parameters.Add("@question", question_txt.Text);
cmd.Parameters.Add("@answerA", answersOpA.Text);
cmd.Parameters.Add("@answerB", answersOpB.Text);
cmd.Parameters.Add("@answerC", answersOpC.Text);
cmd.Parameters.Add("@answerD", answersOpD.Text);
cmd.Parameters.Add("@resultA", int.Parse("0"));
cmd.Parameters.Add("@resultB", int.Parse("0"));
cmd.Parameters.Add("@resultC", int.Parse("0"));
cmd.Parameters.Add("@resultD", int.Parse("0"));
cmd.ExecuteNonQuery();
string query = "Select Max(surveys_id) from surveys";
SqlDataAdapter sda;
DataTable dtbl;
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
int surveys_id = int.Parse(dtbl.Rows[0][0].ToString());
query = "Select Id from users where grade='" + grade_cb.Text + "'" + "And classNumber ='" + clas + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
for (int i = 0; i < dtbl.Rows.Count; i++)
{
cmd = new SqlCommand("INSERT INTO surveys_to_users(surveys_id,user_id,answer) VALUES(@surveys_id,@user_id,@answer)", conn);
cmd.Parameters.Add("@surveys_id", surveys_id);
cmd.Parameters.Add("@user_id", dtbl.Rows[i][0]);
cmd.Parameters.Add("@answer", false);
cmd.ExecuteNonQuery();
}
}
question_txt.Clear();
answersOpA.Clear();
answersOpB.Clear();
answersOpC.Clear();
answersOpD.Clear();
}
private void button3_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to remove all the 12 grades student and remove all schedule?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// user clicked yes
if (MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
// user clicked yes again
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dt_lastyearusers, dt_students;
conn.Open();
string query = "Select Id from users where grade= " + 12;
sda = new SqlDataAdapter(query, conn);
dt_lastyearusers = new DataTable();
sda.Fill(dt_lastyearusers);
for (int i = 0; i < dt_lastyearusers.Rows.Count; i++)
{
string sendToId = dt_lastyearusers.Rows[i]["Id"].ToString().Trim();
User n = new User();
n.DeleteUser(sendToId);
}
for (int i = 7; i <= 12; i++)
{
SqlCommand cmd = new SqlCommand("DELETE FROM weekly_events WHERE user_id_OR_class ='" + i.ToString() + "'", conn);
cmd.ExecuteNonQuery();
for (int j = 1; j <= 3; j++)
{
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE user_id_OR_class ='" + i.ToString() + "-" + j.ToString() + "'", conn);
cmd1.ExecuteNonQuery();
}
}
/*
* SqlCommand cmd = new SqlCommand("DELETE FROM weekly_events WHERE user_id_OR_class ='" +"12-1"+"'", conn);
* cmd.ExecuteNonQuery();
* cmd = new SqlCommand("DELETE FROM weekly_events WHERE user_id_OR_class ='" + "12-2" + "'", conn);
* cmd.ExecuteNonQuery();
* cmd = new SqlCommand("DELETE FROM weekly_events WHERE user_id_OR_class ='" + "12-3" + "'", conn);
* cmd.ExecuteNonQuery();
*/
query = "Select * from users where permission = '" + "student" + "'";
sda = new SqlDataAdapter(query, conn);
dt_students = new DataTable();
sda.Fill(dt_students);
for (int i = 0; i < dt_students.Rows.Count; i++)
{
SqlCommand cmd2 = new SqlCommand("UPDATE users SET grade ='" + (int.Parse(dt_students.Rows[i]["grade"].ToString().Trim()) + 1).ToString() + "' WHERE id ='" + dt_students.Rows[i]["Id"] + "'", conn);
cmd2.ExecuteNonQuery();
}
if (dt_lastyearusers.Rows.Count > 0)
{
MessageBox.Show("All student at 12 grade are deleted and all the schedule.");
}
else
{
MessageBox.Show("There not student at 12 grade.");
}
}
else
{
// user clicked no
// ManagerCalander n = new ManagerCalander();
//n.Show();
// this.Hide();
}
}
else
{
// user clicked no
// ManagerCalander n = new ManagerCalander();
// n.Show();
//this.Hide();
}
}
private void button3_Click(object sender, EventArgs e)
{
string day;
string start, end;
int int_start, int_end;
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda0, sda2;
DataTable dtbl0, dtbl2;
conn.Open();
string query0 = "Select * from weekly_events where user_id_OR_class='" + log_in_page.userId + "'";/* + "and event_kind='" + "reception_hours" + "'"*/
sda0 = new SqlDataAdapter(query0, conn);
dtbl0 = new DataTable();
sda0.Fill(dtbl0);
string already_exist;
bool flag = false;
bool flag2 = false;
bool addeve = false, deleve = false;
for (int i = 12; i < 48; i++)
{
if (labels[i].BackColor == Color.LightGreen)
{
SqlCommand cmd = new SqlCommand("INSERT INTO weekly_events(user_id_OR_class,day_in_week,start,ends,event_kind,title) VALUES(@user_id_OR_class,@day_in_week,@start,@ends,@event_kind,@title) ", conn);
User user = new User();
user = user.GetUser(log_in_page.userId);
string helpstring = user.getName() + " " + user.getLastName() + " reception hours.";
cmd.Parameters.Add("@title", helpstring);
cmd.Parameters.Add("@user_id_OR_class", log_in_page.userId);
day = labels[i % 6].Text.ToString(); //i%6 get the day
cmd.Parameters.Add("@day_in_week", day);
var hours = (labels[i].Text.ToString()).Split('-'); //label text = hours start and end.
start = hours[0];
end = hours[1];
int_start = int.Parse(start.ToString());
int_end = int.Parse(end.ToString());
cmd.Parameters.Add("@start", int_start);
cmd.Parameters.Add("@ends", int_end);
cmd.Parameters.Add("@event_kind", "reception_hours");
for (int m = 0; m < dtbl0.Rows.Count; m++) //***CHECK IF THE EVENT IS ALREADY EXIST.
{ //labels[i].Text.ToString().Trim() == already_exist &&
already_exist = dtbl0.Rows[m]["start"].ToString().Trim() + "-" + dtbl0.Rows[m]["ends"].ToString().Trim();
if ((int_start == int.Parse(dtbl0.Rows[m]["start"].ToString().Trim()) || int_end == int.Parse(dtbl0.Rows[m]["ends"].ToString().Trim())) && labels[i % 6].Text.Trim() == dtbl0.Rows[m]["day_in_week"].ToString().Trim())
{
if (dtbl0.Rows[m]["event_kind"].ToString().Trim() != "reception_hours")
{
flag2 = true;
}
flag = true;
break;
}
}
if (flag)//IF the event already exist.
{
flag = false;
if (flag2 == true)
{
labels[i].BackColor = Color.DarkRed;
MessageBox.Show("You have other system event at " + labels[i % 6].Text.Trim() + " between " + int_start + " to " + int_end + " choose other time.");
flag2 = false;
}
continue;
}
cmd.ExecuteNonQuery();
//take the max id event from Event table
string query = "Select MAX(wEvent_id) from weekly_events";
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
int eve_id = int.Parse(dtbl.Rows[0][0].ToString().Trim());
//insert the max id event and the event id to weekly events to users table
cmd = new SqlCommand("INSERT INTO weekly_events_to_users (wEvent_id,user_id) VALUES(@wEvent_id,@user_id) ", conn);
cmd.Parameters.Add("@wEvent_id", eve_id);
cmd.Parameters.Add("@user_id", log_in_page.userId);
cmd.ExecuteNonQuery();
addeve = true;
}
else
{
for (int m = 0; m < dtbl0.Rows.Count; m++)//***CHECK IF THE EVENT IS ALREADY EXIST AND HS TO BE DELETED.
{
already_exist = dtbl0.Rows[m]["start"].ToString().Trim() + "-" + dtbl0.Rows[m]["ends"].ToString().Trim();
if (labels[i].Text.ToString().Trim() == already_exist && labels[i % 6].Text.Trim() == dtbl0.Rows[m]["day_in_week"].ToString().Trim())
{
string query2 = "Select wEvent_id from weekly_events where user_id_OR_class = '" + log_in_page.userId + "' and start='" + dtbl0.Rows[m]["start"] + "' and event_kind='" + "reception_hours" + "' and day_in_week ='" + labels[i % 6].Text.Trim() + "'";
sda2 = new SqlDataAdapter(query2, conn);
dtbl2 = new DataTable();
sda2.Fill(dtbl2);
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events_to_users WHERE wEvent_id ='" + dtbl2.Rows[0][0] + "'", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE wEvent_id ='" + dtbl2.Rows[0][0] + "'", conn);
cmd1.ExecuteNonQuery();
deleve = true;
}
}
}
}
if (addeve == true && deleve == true)
{
MessageBox.Show("Your reception hours added and removed successfully");
}
else if (addeve == true)
{
MessageBox.Show("Your reception hours added successfully");
}
else if (deleve == true)
{
MessageBox.Show("Your reception hours removed successfully");
}
conn.Close();
}
private void ManagerCalander_Load(object sender, EventArgs e)
{
DateTime now_dt = DateTime.Now;//END YEAR button visible or invisible according the date.
DateTime start = new DateTime(int.Parse(now_dt.Year.ToString().Trim()), 6, 20);
DateTime end = new DateTime(int.Parse(now_dt.Year.ToString().Trim()), 9, 1);
if (now_dt.Ticks >= start.Ticks && now_dt.Ticks <= end.Ticks)
{
EndYear_btn.Visible = true;
}
List <ListViewItem> itemlist = new List <ListViewItem>();
listView1.Items.Clear();
int thisday = monthCalendar1.SelectionRange.Start.Day;
int thismonth = monthCalendar1.SelectionRange.Start.Month;
int thisyear = monthCalendar1.SelectionRange.Start.Year;
string dayOfWeek = monthCalendar1.SelectionRange.Start.DayOfWeek.ToString();
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
string id = "'" + log_in_page.userId.ToString() + "'";
string query = "Select Event_ID from Events_to_Users where User_ID=" + id;
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtb = new DataTable();
sda.Fill(dtb);
for (int i = 0; i < dtb.Rows.Count; i++)
{
string event_id = "'" + dtb.Rows[i][0].ToString() + "'";
string query2 = "Select * from Events where Event_id=" + event_id;
SqlDataAdapter sda2 = new SqlDataAdapter(query2, conn);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
/* 1/15/2019 00:00:00*/
if (dtb2.Rows.Count > 0)
{
if (dtb2.Rows[0]["date"].ToString() == monthCalendar1.SelectionRange.Start.ToString())
{
string hours_end = dtb2.Rows[0]["hours_end"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb2.Rows[0]["hours_start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = dtb2.Rows[0]["minutes_start"].ToString().Trim();
if (minutes_start.Length < 2)
{
minutes_start = "0" + minutes_start;
}
string minutes_end = dtb2.Rows[0]["minutes_end"].ToString().Trim();
if (minutes_end.Length < 2)
{
minutes_end = "0" + minutes_end;
}
ListViewItem item = new ListViewItem(dtb2.Rows[0]["Event_name"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
item.SubItems.Add(event_id);
/*
* choosing color -- need to update if updated.
*/
switch (dtb2.Rows[0]["event_kind"].ToString().Trim())
{
case "Muliplayer":
item.BackColor = Color.DeepSkyBlue;
break;
case "School":
item.BackColor = Color.Red;
break;
case "Regular":
item.BackColor = Color.SteelBlue;
break;
case "system-public":
item.BackColor = Color.Red;
break;
case "HW":
item.BackColor = Color.YellowGreen;
break;
default:
break;
}
//listView1.Items.Add(item);
itemlist.Add(item);
}
}
}
string user_class;
User user = new User();
user = user.GetUser(log_in_page.userId);
user_class = user.getGrade() + "-" + user.getClassNumber();
string query3 = "Select * from weekly_events where day_in_week='" + dayOfWeek + "' and user_id_OR_class='" + user_class + "'";
SqlDataAdapter sda3 = new SqlDataAdapter(query3, conn);
DataTable dtb3 = new DataTable();
sda3.Fill(dtb3);
for (int i = 0; i < dtb3.Rows.Count; i++)
{
string hours_end = dtb3.Rows[i]["ends"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb3.Rows[i]["start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = "00";
string minutes_end = "00";
ListViewItem item = new ListViewItem(dtb3.Rows[i]["title"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
item.SubItems.Add(dtb3.Rows[i]["wEvent_id"].ToString().Trim());
item.BackColor = Color.Orange;
//listView1.Items.Add(item);
itemlist.Add(item);
}
//MessageBox.Show(itemlist[0].SubItems[1].ToString());
for (int i = 0; i < itemlist.Count - 1; i++)
{
for (int j = 0; j < itemlist.Count - 1; j++)
{
string firsthour = itemlist[j].SubItems[1].ToString();
string secondhour = itemlist[j + 1].SubItems[1].ToString();
if (compairhours(firsthour, secondhour))
{
ListViewItem temp = new ListViewItem();
temp = itemlist[j];
itemlist[j] = itemlist[j + 1];
itemlist[j + 1] = temp;
}
}
}
for (int i = 0; i < itemlist.Count; i++)
{
listView1.Items.Add(itemlist[i]);
}
dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string rownumOfMax = "0";
con.Open();
query = "Select MAX(msg_id) from bulletin_board";
sda = new SqlDataAdapter(query, con);
dtb = new DataTable();
sda.Fill(dtb);
if (dtb.Rows.Count > 0)
{
rownumOfMax = dtb.Rows[0][0].ToString();
query = "Select msg from bulletin_board where msg_id ='" + rownumOfMax + "'";
sda = new SqlDataAdapter(query, con);
dtb = new DataTable();
sda.Fill(dtb);
if (dtb.Rows.Count > 0)
{
motd_txt.Text = dtb.Rows[0][0].ToString();
}
}
}
public Event getEvent(string eventid, bool isweekly)
{
Event n = new Event();
n.usersID = new List <string>();
n.eventID = eventid;
n.weekly = isweekly;
if (!n.weekly)
{
n.day_in_week = null;
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query_name = "Select * from Events Where Event_id = " + n.eventID;
SqlDataAdapter sda = new SqlDataAdapter(query_name, con);
DataTable dtb = new DataTable();
sda.Fill(dtb);
if (dtb.Rows.Count > 0)
{
string query_name2 = "Select User_id from Events_to_Users Where Event_ID = " + n.eventID;
SqlDataAdapter sda2 = new SqlDataAdapter(query_name, con);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
if (dtb2.Rows.Count == 0)
{
return(n);
}
else
{
n.usersID.Add(dtb2.Rows[0][0].ToString().Trim());
}
n.title = dtb.Rows[0]["Event_name"].ToString().Trim();
n.details = dtb.Rows[0]["Event_details"].ToString().Trim();
n.kind = dtb.Rows[0]["Event_kind"].ToString().Trim();
n.place = dtb.Rows[0]["Event_place"].ToString().Trim();
n.date = DateTime.Parse(dtb.Rows[0]["date"].ToString().Trim());
n.hours_start = dtb.Rows[0]["hours_start"].ToString().Trim();
n.hours_end = dtb.Rows[0]["hours_end"].ToString().Trim();
n.minute_start = dtb.Rows[0]["minutes_start"].ToString().Trim();
n.minute_end = dtb.Rows[0]["minutes_end"].ToString().Trim();
if (dtb.Rows[0]["Event_privacy"].ToString().Trim().ToLower() == ("public"))
{
n.isPrivate = false;
}
else if (dtb.Rows[0]["Event_privacy"].ToString().Trim().ToLower() == ("private"))
{
n.isPrivate = true;
}
if (dtb.Rows[0]["up_for_cancellation"].ToString().Trim().ToLower() == ("yes"))
{
n.isPrivate = true;
}
else if (dtb.Rows[0]["up_for_cancellation"].ToString().Trim().ToLower() == ("no"))
{
n.isPrivate = false;
}
switch (n.kind)
{
case "Muliplayer":
color = Color.DeepSkyBlue;
break;
case "School":
color = Color.Red;
break;
case "Regular":
color = Color.SteelBlue;
break;
case "system-public":
color = Color.Red;
break;
case "HW":
color = Color.YellowGreen;
break;
default:
break;
}
}
}
else if (n.weekly)
{
n.cancelable = true;
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query_name = "Select * from weekly_events Where wEvent_id = " + n.eventID;
SqlDataAdapter sda = new SqlDataAdapter(query_name, con);
DataTable dtb = new DataTable();
sda.Fill(dtb);
if (dtb.Rows.Count > 0)
{
string tempusers = dtb.Rows[0]["user_id_OR_class"].ToString().Trim();
if (tempusers.Contains("-")) // if its a class not a teacher
{
string tempClass = "";
string tempGrade = "";
int i = 0;
while (tempusers[i] != '-')
{
tempGrade = tempGrade + tempusers[i];
i++;
}
i++;
tempClass = tempusers[i].ToString();
string query_name3 = "Select id from users Where grade = '" + tempGrade + "' and classNumber ='" + tempClass + "' and permission = 'student'";
SqlDataAdapter sda3 = new SqlDataAdapter(query_name, con);
DataTable dtb3 = new DataTable();
sda3.Fill(dtb3);
for (i = 0; i < dtb3.Rows.Count; i++)
{
n.usersID.Add(dtb3.Rows[i][0].ToString().Trim());
}
}
else
{
n.usersID.Add(tempusers);
}
n.title = dtb.Rows[0]["title"].ToString().Trim();
n.details = "";
n.kind = dtb.Rows[0]["event_kind"].ToString().Trim();
n.place = "School";
n.day_in_week = dtb.Rows[0]["day_in_week"].ToString().Trim();
n.hours_start = dtb.Rows[0]["start"].ToString().Trim();
n.hours_end = dtb.Rows[0]["ends"].ToString().Trim();
n.minute_start = "0";
n.minute_end = "0";
n.isPrivate = false;
color = Color.Orange;
}
}
return(n);
}
private void update_btn_Click(object sender, EventArgs e)
{
/*
* for teachers we will use their ID
* for classes we will use the fromat class-classNum:
* like this : 10-3
* wich means 10th year class number 3
*
*/
try
{
if (teacher_or_class_flag == 't')
{
/*
* for teacher:
*/
string username = teacher_name_txt.Text.Replace(" ", "");
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query = "Select id from connection_details Where userName = '******'";
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
String validDay = days.Text;
if (dtbl.Rows.Count == 0)
{
MessageBox.Show("there is no such a teacher.");
}
else if (!(days.Items.Contains(validDay)))
{
MessageBox.Show("there is no such a day.");
}
else
{
string userID = dtbl.Rows[0][0].ToString().Trim(); // getting the userID
query = "Select wEvent_id from weekly_events Where user_id_OR_class = '" + userID + "' and day_in_week='" + days.Text + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
con.Close(); //gets all the event this day
if (dtbl.Rows.Count >= 0)
{
for (int i = 0; i < 8; i++) // max 8 hours in the schedule
{
dataBase = new dataBase();
con = dataBase.connect_to_scheduluz_DB();
query = "Select wEvent_id from weekly_events Where user_id_OR_class = '" + userID + "' and day_in_week='" + days.Text + "' and start='" + schedule["startTime", i].Value.ToString().Trim() + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
if (schedule[2, i].Value != null)
{
if (dtbl.Rows.Count > 0)
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("UPDATE weekly_events SET title ='" + schedule[2, i].Value.ToString() + "' WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + userID + "'", conn);
cmd1.ExecuteNonQuery();
conn.Close();
}
else
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO weekly_events(user_id_OR_class,day_in_week,start,ends,title) VALUES(@user_id_OR_class,@day_in_week,@start,@ends,@title) ", con);
cmd.Parameters.Add("@user_id_OR_class", userID);
cmd.Parameters.Add("@day_in_week", days.Text);
cmd.Parameters.Add("@start", schedule[0, i].Value.ToString());
if (schedule[2, i].Value != null)
{
cmd.Parameters.Add("@ends", schedule[1, i].Value.ToString());
}
cmd.Parameters.Add("@title", schedule[2, i].Value.ToString());
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
//delete the event from the DB
try
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + userID + "'", conn);
cmd1.ExecuteNonQuery();
}
catch
{
}
}
}
//// the problem is that its inserting OR updating not both.
}
else
{
for (int i = 0; i < 8; i++) // max 8 hours in the schedule
{
if (schedule[2, i].Value != null)
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("UPDATE weekly_events SET title ='" + schedule[2, i].Value.ToString() + "' WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + userID + "'", conn);
cmd1.ExecuteNonQuery();
}
else
{
try
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + userID + "'", conn);
cmd1.ExecuteNonQuery();
}
catch
{
}
}
}
}
User user = new User();
user = user.GetUser(userID);
MessageBox.Show(user.getName() + " " + user.getLastName() + ": Schedule updated.");
}
}
if (teacher_or_class_flag == 'c')
{
string classID = grade_cb.Text + "-" + classnum_cb.Text;
dataBase dataBase = new dataBase();
SqlConnection con = dataBase.connect_to_scheduluz_DB();
string query = "Select wEvent_id from weekly_events Where user_id_OR_class = '" + classID + "' and day_in_week='" + days.Text + "'";
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
con.Close();
if (dtbl.Rows.Count >= 0)
{
for (int i = 0; i < 8; i++) // max 8 hours in the schedule
{
dataBase = new dataBase();
con = dataBase.connect_to_scheduluz_DB();
query = "Select wEvent_id from weekly_events Where user_id_OR_class = '" + classID + "' and day_in_week='" + days.Text + "' and start='" + schedule["startTime", i].Value.ToString().Trim() + "'";
sda = new SqlDataAdapter(query, con);
dtbl = new DataTable();
sda.Fill(dtbl);
if (schedule[2, i].Value != null)
{
if (dtbl.Rows.Count > 0)
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("UPDATE weekly_events SET title ='" + schedule[2, i].Value.ToString() + "' WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + classID + "'", conn);
cmd1.ExecuteNonQuery();
conn.Close();
}
else
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO weekly_events(user_id_OR_class,day_in_week,start,ends,title) VALUES(@user_id_OR_class,@day_in_week,@start,@ends,@title) ", con);
cmd.Parameters.Add("@user_id_OR_class", classID);
cmd.Parameters.Add("@day_in_week", days.Text);
cmd.Parameters.Add("@start", schedule[0, i].Value.ToString());
if (schedule[2, i].Value != null)
{
cmd.Parameters.Add("@ends", schedule[1, i].Value.ToString());
}
cmd.Parameters.Add("@title", schedule[2, i].Value.ToString());
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
//delete the event from the DB
try
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + classID + "'", conn);
cmd1.ExecuteNonQuery();
}
catch
{
}
}
}
}
else
{
for (int i = 0; i < 8; i++) // max 8 hours in the schedule
{
if (schedule[2, i].Value != null)
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("UPDATE weekly_events SET title ='" + schedule[2, i].Value.ToString() + "' WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + classID + "'", conn);
cmd1.ExecuteNonQuery();
}
else
{
try
{
dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd1 = new SqlCommand("DELETE FROM weekly_events WHERE day_in_week ='" + days.Text + "' and start='" + schedule[0, i].Value.ToString() + "' and user_id_OR_class='" + classID + "'", conn);
cmd1.ExecuteNonQuery();
}
catch
{
}
}
}
}
MessageBox.Show("Schedule updated.");
}
}
catch
{
MessageBox.Show("Problem detected. please contact your Scheduluz Guide for more information.");
}
}
private void button3_Click(object sender, EventArgs e)
{
if (text_title.Text == "")
{
MessageBox.Show("please fill the title");
}
else if (Place.Text == "")
{
MessageBox.Show("please fill the place");
}
else if (hours_start.Value > hours_end.Value)
{
MessageBox.Show("your start hours is later than your end hours.");
}
else if (hours_start.Value == hours_end.Value && minutes_start.Value >= minutes_end.Value)
{
MessageBox.Show("your start hour is later or equal to your end hour.");
}
else if (listView1.Items.Count == 0)
{
MessageBox.Show("there are no student to invite.");
}
else
{
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO Events(Event_name,event_kind,date,hours_start,minutes_start,hours_end,minutes_end,Event_details,event_place,up_for_cancellation,event_privacy) VALUES(@Event_name,@event_kind,@date,@hours_start,@minutes_start,@hours_end,@minutes_end,@Event_details,@Place,@up_for_cancellation,@event_privacy) ", conn);
cmd.Parameters.Add("@event_privacy", "Private");
cmd.Parameters.Add("@up_for_cancellation", "YES");
cmd.Parameters.Add("@event_kind", "Muliplayer");
cmd.Parameters.Add("@Event_name", text_title.Text.ToString());
cmd.Parameters.Add("@date", date.Text);
cmd.Parameters.Add("@hours_start", hours_start.Value);
cmd.Parameters.Add("@minutes_start", minutes_start.Text);
cmd.Parameters.Add("@hours_end", hours_end.Text);
cmd.Parameters.Add("@minutes_end", minutes_end.Text);
cmd.Parameters.Add("@Event_details", richTextBox1.Text.ToString());
cmd.Parameters.Add("@Place", Place.Text.ToString());
cmd.ExecuteNonQuery();
//take the max id event from Event table
string query = "Select MAX(Event_id) from Events";
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
int eve_id = int.Parse(dtbl.Rows[0][0].ToString().Trim());
int usr_id;
String fullName;
//insert the max id event and the event id to Events_to_Users table
for (int i = listView1.Items.Count - 1; i >= 0; i--)
{
fullName = listView1.Items[i].Text.ToString().Trim();
var names = fullName.Split(' ');
if (names.Length != 2)
{
friend_name.Clear();
MessageBox.Show("illegal enter first name and last name separate.");
conn.Close();
}
else
{
string firstName = names[0];
string lastName = names[1];
query = "Select * from users Where name = '" + firstName + "' and LastName = '" + lastName + "'"; sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
usr_id = int.Parse(dtbl.Rows[0][0].ToString().Trim());
cmd = new SqlCommand("INSERT INTO Events_to_Users (User_ID,Event_ID) VALUES(@User_ID,@Event_ID) ", conn);
cmd.Parameters.Add("@User_ID", usr_id);
cmd.Parameters.Add("@Event_ID", eve_id);
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("Your event added successfully");
StudentCalander n = new StudentCalander();
n.Show();
this.Hide();
conn.Close();
text_title.Clear();
richTextBox1.Clear();
Place.Clear();
listView1.Clear();
}
}
private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
{
List <ListViewItem> itemlist = new List <ListViewItem>();
listView1.Items.Clear();
int thisday = monthCalendar1.SelectionRange.Start.Day;
int thismonth = monthCalendar1.SelectionRange.Start.Month;
int thisyear = monthCalendar1.SelectionRange.Start.Year;
string dayOfWeek = monthCalendar1.SelectionRange.Start.DayOfWeek.ToString();
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
conn.Open();
string id = "'" + student_id + "'";
string query = "Select Event_ID from Events_to_Users where User_ID=" + id;
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtb = new DataTable();
sda.Fill(dtb);
for (int i = 0; i < dtb.Rows.Count; i++)
{
string event_id = "'" + dtb.Rows[i][0].ToString() + "'";
string query2 = "Select * from Events where Event_id=" + event_id + " and event_privacy = 'Public'";
SqlDataAdapter sda2 = new SqlDataAdapter(query2, conn);
DataTable dtb2 = new DataTable();
sda2.Fill(dtb2);
/* 1/15/2019 00:00:00*/
if (dtb2.Rows.Count > 0)
{
/*
* string day= thisday.ToString();
* string month= thismonth.ToString();
* if (day.Length < 2)
* {
* day = "0" + day;
* }
* if (month.Length < 2)
* {
* month = "0" + month;
* }dtb2.Rows[0]["date"].ToString() == thismonth.ToString() + "/" + thisday.ToString() + "/" + thisyear.ToString() + " 00:00:00"
*/
if (dtb2.Rows[0]["date"].ToString() == monthCalendar1.SelectionRange.Start.ToString())
{
string hours_end = dtb2.Rows[0]["hours_end"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb2.Rows[0]["hours_start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = dtb2.Rows[0]["minutes_start"].ToString().Trim();
if (minutes_start.Length < 2)
{
minutes_start = "0" + minutes_start;
}
string minutes_end = dtb2.Rows[0]["minutes_end"].ToString().Trim();
if (minutes_end.Length < 2)
{
minutes_end = "0" + minutes_end;
}
ListViewItem item = new ListViewItem(dtb2.Rows[0]["Event_name"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
/*
* choosing color -- need to update if updated.
*/
switch (dtb2.Rows[0]["event_kind"].ToString().Trim())
{
case "Muliplayer":
item.BackColor = Color.DeepSkyBlue;
break;
case "School":
item.BackColor = Color.Red;
break;
case "Regular":
item.BackColor = Color.SteelBlue;
break;
case "system-public":
item.BackColor = Color.Red;
break;
case "HW":
item.BackColor = Color.YellowGreen;
break;
default:
break;
}
//listView1.Items.Add(item);
itemlist.Add(item);
}
}
}
string user_class;
User user = new User();
user = user.GetUser(student_id);//problem if student not choosen
user_class = user.getGrade() + "-" + user.getClassNumber();
string query3 = "Select * from weekly_events where day_in_week='" + dayOfWeek + "' and user_id_OR_class='" + user_class + "'";
SqlDataAdapter sda3 = new SqlDataAdapter(query3, conn);
DataTable dtb3 = new DataTable();
sda3.Fill(dtb3);
for (int i = 0; i < dtb3.Rows.Count; i++)
{
string hours_end = dtb3.Rows[i]["ends"].ToString().Trim();
if (hours_end.Length < 2)
{
hours_end = "0" + hours_end;
}
string hours_start = dtb3.Rows[i]["start"].ToString().Trim();
if (hours_start.Length < 2)
{
hours_start = "0" + hours_start;
}
string minutes_start = "00";
string minutes_end = "00";
ListViewItem item = new ListViewItem(dtb3.Rows[i]["title"].ToString().Trim());
item.SubItems.Add(hours_start + ":" + minutes_start);
item.SubItems.Add(hours_end + ":" + minutes_end);
item.BackColor = Color.Orange;
//listView1.Items.Add(item);
itemlist.Add(item);
}
//MessageBox.Show(itemlist[0].SubItems[1].ToString());
for (int i = 0; i < itemlist.Count - 2; i++)
{
for (int j = 0; j < itemlist.Count - 2; j++)
{
string firsthour = itemlist[j].SubItems[1].ToString();
string secondhour = itemlist[j + 1].SubItems[1].ToString();
if (compairhours(firsthour, secondhour))
{
ListViewItem temp = new ListViewItem();
temp = itemlist[j];
itemlist[j] = itemlist[j + 1];
itemlist[j + 1] = temp;
}
}
}
for (int i = 0; i < itemlist.Count; i++)
{
listView1.Items.Add(itemlist[i]);
}
}
private void listView_SelectedIndexChanged(object sender, EventArgs e)
{
if (listView.SelectedIndices.Count > 0)
{
Table.Controls.Clear();
Table.RowStyles.Clear();
Table.Location = new Point(406, 60);
Table.Size = new Size(400, 115);
Table.AutoSize = false;
Table.Name = "Desk";
Table.ColumnCount = 1;
Table.RowCount = 5;
Table.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink;
Table.GrowStyle = System.Windows.Forms.TableLayoutPanelGrowStyle.AddRows;
this.Controls.Add(Table);
labels[0] = new Label {
BackColor = Color.Black, ForeColor = Color.White, Dock = DockStyle.Fill
};
for (int i = 1; i < 5; i++)
{
labels[i] = new Label {
BackColor = Color.White, ForeColor = Color.Black, Dock = DockStyle.Fill
};
labels[i].Click += new System.EventHandler(LabelClick);
}
int selectedIndex = listView.SelectedIndices[0];
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda;
DataTable dtbl, dtbl2;
conn.Open();
string query = "Select surveys_id from surveys_to_users where user_id='" + int.Parse(log_in_page.userId) + "'" + "And answer ='" + false + "'";
sda = new SqlDataAdapter(query, conn);
dtbl = new DataTable();
sda.Fill(dtbl);
query = "Select * from surveys where surveys_id='" + int.Parse(dtbl.Rows[selectedIndex][0].ToString()) + "'";
sda = new SqlDataAdapter(query, conn);
dtbl2 = new DataTable();
sda.Fill(dtbl2);
string quest = dtbl2.Rows[0]["question"].ToString();
string ans1 = dtbl2.Rows[0]["answerA"].ToString();
string ans2 = dtbl2.Rows[0]["answerB"].ToString();
string ans3 = dtbl2.Rows[0]["answerC"].ToString();
string ans4 = dtbl2.Rows[0]["answerD"].ToString();
labels[0].Text = quest;
Table.Controls.Add(labels[0], 0, 0);
labels[1].Text = ans1;
Table.Controls.Add(labels[1], 1, 0);
labels[2].Text = ans2;
Table.Controls.Add(labels[2], 2, 0);
labels[3].Text = ans3;
Table.Controls.Add(labels[3], 3, 0);
labels[4].Text = ans4;
Table.Controls.Add(labels[4], 4, 0);
}
}
private void button3_Click(object sender, EventArgs e)
{
string start, end;
int int_start, int_end;
dataBase dataBase = new dataBase();
SqlConnection conn = dataBase.connect_to_scheduluz_DB();
SqlDataAdapter sda0;
DataTable dtbl0, dtbl1;
conn.Open();
//string find_
string query0 = "Select * from Events_to_Users where User_Id='" + log_in_page.userId + "'";/* + "and event_kind='" + "reception_hours" + "'"*/
sda0 = new SqlDataAdapter(query0, conn);
dtbl0 = new DataTable();
sda0.Fill(dtbl0);
string already_exist;
bool flag = false;
for (int i = 12; i < 48; i++)
{
if (labels[i].BackColor == Color.YellowGreen)
{
SqlCommand cmd = new SqlCommand("INSERT INTO Events(Event_name,event_kind,date,hours_start,minutes_start,hours_end,minutes_end,Event_details,event_place,up_for_cancellation) VALUES(@Event_name,@event_kind,@date,@hours_start,@minutes_start,@hours_end,@minutes_end,@Event_details,@Place,@up_for_cancellation) ", conn);
cmd.Parameters.Add("@up_for_cancellation", "NO");
cmd.Parameters.Add("@event_kind", "reception_hour");
cmd.Parameters.Add("@Event_name", "reception hour");
dt = dt.AddDays(int.Parse(labels[i % 6].Name) - dt.Day);//caculate the event date.
cmd.Parameters.Add("@date", dt);
var hours = (labels[i].Text.ToString()).Split('-');//label text = hours start and end.
start = hours[0];
end = hours[1];
int_start = int.Parse(start);
int_end = int.Parse(end);
cmd.Parameters.Add("@hours_start", int_start);
cmd.Parameters.Add("@minutes_start", "0");
cmd.Parameters.Add("@hours_end", int_end);
cmd.Parameters.Add("@minutes_end", "0");
cmd.Parameters.Add("@Event_details", "reception hour of: " + global_teacher_name);
cmd.Parameters.Add("@Place", "at school.");
for (int m = 0; m < dtbl0.Rows.Count; m++) //***CHECK IF THE EVENT IS ALREADY EXIST.
{ //labels[i].Text.ToString().Trim() == already_exist &&
query0 = "Select * from Events where Event_id = '" + dtbl0.Rows[m]["Event_ID"] + "'";
sda0 = new SqlDataAdapter(query0, conn);
dtbl1 = new DataTable();
sda0.Fill(dtbl1);
if (dtbl1.Rows.Count > 0)
{
already_exist = dtbl1.Rows[0]["hours_start"].ToString().Trim() + "-" + dtbl1.Rows[0]["hours_end"].ToString().Trim();
DateTime.TryParse(dtbl1.Rows[0]["date"].ToString().Trim(), out dt);
if (dtbl1.Rows[0]["event_kind"].ToString().Trim() == "reception_hour" && labels[i].Text.ToString().Trim() == already_exist && labels[i % 6].Name == dt.Day.ToString().Trim())
{
flag = true;
break;
}
}
}
if (flag)//IF the event already exist.
{
flag = false;
continue;
}
cmd.ExecuteNonQuery();//the event not exist then added.
//take the max id event from Event table
string query = "Select MAX(Event_ID) from Events";
SqlDataAdapter sda = new SqlDataAdapter(query, conn);
DataTable dtbl = new DataTable();
sda.Fill(dtbl);
int eve_id = int.Parse(dtbl.Rows[0][0].ToString().Trim());
//insert the max id event and the event id to events to users table
cmd = new SqlCommand("INSERT INTO Events_to_Users (Event_ID,User_ID) VALUES(@Event_ID,@User_ID) ", conn);
cmd.Parameters.Add("@Event_ID", eve_id);
cmd.Parameters.Add("@User_ID", log_in_page.userId);
cmd.ExecuteNonQuery();
}
else if (labels[i].BackColor == Color.LightGreen)
{
for (int m = 0; m < dtbl0.Rows.Count; m++)//***CHECK IF THE EVENT IS ALREADY EXIST AND HAS TO BE DELETED.
{
query0 = "Select * from Events where Event_id = '" + dtbl0.Rows[m]["Event_ID"] + "'";
sda0 = new SqlDataAdapter(query0, conn);
dtbl1 = new DataTable();
sda0.Fill(dtbl1);
if (dtbl1.Rows.Count > 0)
{
already_exist = dtbl1.Rows[0]["hours_start"].ToString().Trim() + "-" + dtbl1.Rows[0]["hours_end"].ToString().Trim();
//DateTime day_on_week;
DateTime.TryParse(dtbl1.Rows[0]["date"].ToString().Trim(), out dt);
if (dtbl1.Rows[0]["event_kind"].ToString().Trim() == "reception_hour" && labels[i].Text.ToString().Trim() == already_exist && labels[i % 6].Name == dt.Day.ToString().Trim())
{
SqlCommand cmd1 = new SqlCommand("DELETE FROM Events WHERE Event_id ='" + dtbl1.Rows[0]["Event_id"] + "'", conn);
cmd1.ExecuteNonQuery();
cmd1 = new SqlCommand("DELETE FROM Events_to_Users WHERE Event_ID ='" + dtbl1.Rows[0]["Event_id"] + "'", conn);
cmd1.ExecuteNonQuery();
}
}
}
}
}
//MessageBox.Show("Your event added successfully");
conn.Close();
}