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