//uppdatera mail på Staff public static void UpdateStaffProperties(string email) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"UPDATE staff SET email = @Email WHERE id = @StaffID", new { Email = email, StaffID = Activestaff.Id }); } }
// Markera att barn gått hem public static void SetChildGoneHome() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"UPDATE attendance SET category_attendance_id = 4, staff_id = @StaffID WHERE id = @AttendanceID", new { StaffID = Activestaff.Id, AttendanceID = ActiveAttendance.Id }); } }
public TEXCSITRAuthentication(ConnString enumConnString, int intSessionTimeoutMins) { SessionTimeOutMins = intSessionTimeoutMins; this.SQLConn = new System.Data.SqlClient.SqlConnection(); switch (enumConnString) { case ConnString.TEST: this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_TEMP3_EX_Itr_Authenticator_itr_user); // TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_TEMP3; break; case ConnString.EX: this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_EX_Itr_Authenticator_itr_user); //TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_EX; break; default: this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_TEMP3_EX_Itr_Authenticator_itr_user); // TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_TEMP3; break; } this.SQLCmd = new System.Data.SqlClient.SqlCommand(); this.SQLCmd.CommandType = CommandType.StoredProcedure; this.SQLCmd.Connection = this.SQLConn; this.SQLConn.Open(); }
protected void DeleteAnnoucement_Click(object sender, EventArgs e) { AnnouncementTable announcement = new AnnouncementTable() { announce_id = Convert.ToInt32(deleteAnnouncementIdUser.Text) }; ConnString db = new ConnString(); try { db.Delete(announcement); adminChangeMessage.Text = db.Message; } catch (OracleException ex) { if (ex.Message.Contains("ORA-00000")) { adminChangeMessage.Text = "We've got a situation"; } adminChangeMessage.Text = ex.Message; } Show(); }
// Koppla ihop vårdnadshavare och barn public static void ConnectChildAndGuardian() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"INSERT INTO guardian_child(guardian_id, child_id) VALUES(@GuardianID, @ChildID); INSERT INTO meals (name, guardian_id, child_id) VALUES('frukost', @GuardianID, @ChildID)", new { GuardianID = Activeguardian.Id, ChildID = Activechild.Id }); } }
protected void UpdateAnnouncement_Click(object sender, EventArgs e) { AnnouncementTable announcement = new AnnouncementTable() { announce_id = Convert.ToInt32(updateAnnouncementIdUser.Text), announce_text = updateAnnouncementTextUser.Text, emp_id = Convert.ToInt32(empNum.Text) }; ConnString db = new ConnString(); try { db.Update(announcement); adminChangeMessage.Text = db.Message; } catch (OracleException ex) { if (ex.Message.Contains("ORA-01407")) { adminChangeMessage.Text = "Announcement Text cannot be left Blank."; } else if (ex.Message.Contains("ORA-12899")) { adminChangeMessage.Text = "Announcement text must be under 200 Characters."; } else { adminChangeMessage.Text = ex.Message; } } Show(); }
//Hämta category attendances public static List <Attendancecategory> GetAttendances() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Attendancecategory>($@"SELECT * FROM category_attendance WHERE present = false").ToList(); return(output); } }
//uppdatera mail och/eller telefon på förälder public static void UpdateGuardianProperties(int phone, string email, string firstname, string lastname) { var id = Activeguardian.Id; using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"UPDATE guardian SET firstname = @Firstname, lastname = @Lastname, email = @Email, phone = @Phone WHERE id = @Id", new { Firstname = firstname, Lastname = lastname, Email = email, Phone = phone, Id = id }); } }
// ta bort koppling mellan vårdnadshavare och barn public static void DeleteConnection() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"DELETE FROM guardian_child WHERE guardian_id = @GuardianID AND child_id = @ChildID; DELETE FROM meals WHERE meals.id = @ChildMealsID", new { GuardianID = Activeguardian.Id, ChildID = Activechild.Id, ChildMealsID = Activechild.Mealsid }); } }
//Hämtar alla klasser public static List <Class> GetAllClasses() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Class>($@"SELECT * FROM CLASS").ToList(); return(output); } }
// Tar bort vårdnadshavare public static void DeleteGuardian() { var id = Activeguardian.Id; using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"DELETE FROM guardian WHERE id = @Id", new { Id = id }); } }
// tar bort barn public static void DeleteChild() { var id = Activechild.Id; using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"DELETE FROM child WHERE id = @Id;", new { Id = id }); } }
//Hämtar alla föräldrar public static List <Guardian> GetAllGuardians() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Guardian>($@"SELECT * FROM guardian").ToList(); return(output); } }
//Hämtar föräldrar efter sökning av för - och efternamn public static List <Guardian> GetGuardian(string name) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Guardian>($@"SELECT * FROM guardian WHERE firstname LIKE @Firstname OR lastname LIKE @Lastname", new { Firstname = '%' + name + '%', Lastname = '%' + name + '%' }).ToList(); return(output); } }
//Hämtar alla Veckor public static List <Weeks> GetWeek() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Weeks>($@"SELECT week FROM dates GROUP BY week ORDER BY week ASC").ToList(); return(output); } }
// Hämtar vårdnadshavare till barn public static List <Guardian> GetGuardianOfChild(int id) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Guardian>($@"SELECT * FROM guardian_child INNER JOIN guardian ON guardian_id = guardian.id WHERE child_id = @Id", new { Id = id }).ToList(); return(output); } }
// lägg till nytt barn public static void AddNewChild(string firstname, string lastname, int age, int classid) { InputHandler inputhandler = new InputHandler(); var a = inputhandler.Uppercase(firstname); var b = inputhandler.Uppercase(lastname); using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"INSERT INTO child (firstname, lastname, age, class_id) VALUES (@Firstname, @Lastname, @Age, @Classid)", new { Firstname = firstname, Lastname = lastname, Age = age, Classid = classid }); } }
//Hämtar Avdelning och Telefonnummer public static List <Department> ContactDepartment() { List <Department> departments = new List <Department>(); using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Department>($@"SELECT * FROM Department").ToList(); return(output); } }
//Hämtar alla barn public static List <Child> GetAllChildren() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Child>($@"SELECT child.id, child.firstname, child.lastname, child.age, child.leavealone, class.name AS Class FROM(child INNER JOIN class ON class_id = class.id) ORDER BY class.name DESC").ToList(); return(output); } }
//Hämtar Fritids morgon/kväll public static List <Attendancecategory> GetFritidsMorningEvening() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Attendancecategory>($@"SELECT * FROM category_attendance WHERE (id = 3 OR id = 7 OR id = 8) ORDER BY category_attendance.id DESC").ToList(); return(output); } }
// hämtar årkurser public static List <Child> GetFirstGraders(int departmentid) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Child>($@"SELECT child.id, child.firstname, child.lastname, child.leavealone, class.name AS Class, department.id AS avdelning FROM((child INNER JOIN class ON class_id = class.id) INNER JOIN department ON department_id = department.id) WHERE department_id = @DepartmentID ORDER BY class_id DESC;", new { DepartmentID = departmentid }).ToList(); return(output); } }
// lägg till ny vårdnadshavare public static void AddNewGuardian(int phone, string firstname, string lastname, string email) { InputHandler inputhandler = new InputHandler(); var a = inputhandler.Uppercase(firstname); var b = inputhandler.Uppercase(lastname); using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"INSERT INTO guardian (firstname, lastname, phone, email) VALUES (@Firstname, @Lastname, @Phone, @Email)", new { Firstname = firstname, Lastname = lastname, Phone = phone, Email = email }); } }
//Lägg till fritids som Guardian utan frukost public static void GuardianReportFritids(string comment, int attendanceid) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"INSERT INTO attendance (child_id, guardian_id, category_attendance_id, comment) VALUES (@ChildID, @GuardianID, @AttendanceID, @Comment); INSERT INTO attendance_dates (attendance_id, dates_id) SELECT attendance.id, dates.id FROM attendance, dates WHERE attendance.id = (SELECT MAX(attendance.id) FROM attendance) AND dates.id = @DateID", new { ChildID = Activechild.Id, GuardianID = Activeguardian.Id, AttendanceID = attendanceid, Comment = comment, DateID = ActiveDate.Id }); } }
// Hämtar tabellen guardian_child med namn för att se kopplingar mellan barn och vårdnadshavare public static List <Connections> GetChildGuardian() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { //LÄGG TILL I TABELL GUARDIAN_CHILD var output = connection.Query <Connections> ($@"SELECT guardian_id, guardian.firstname ||' '|| guardian.lastname AS Guardian, child_id, child.firstname ||' '|| child.lastname AS Child FROM((guardian_child INNER JOIN child ON child_id = child.id AND child.firstname = child.firstname AND child.lastname = child.lastname) INNER JOIN guardian ON guardian_id = guardian.id AND guardian.firstname = guardian.firstname AND guardian.lastname = guardian.lastname);").ToList(); return(output); } }
// rapportera frånvaro som staff public static void StaffReportAttendance(string comment) { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"INSERT INTO attendance(child_id, staff_id, category_attendance_id, comment) VALUES(@ChildID, @StaffID, @AttendanceCategoryID, @Comment); INSERT INTO attendance_dates(attendance_id, dates_id) SELECT attendance.id, dates.id FROM attendance, dates WHERE attendance.id = (SELECT MAX(attendance.id) FROM attendance) AND dates.id = @DateID", new { ChildID = Activechild.Id, StaffID = Activestaff.Id, AttendanceCategoryID = ActiveAttendancecategory.Id, Comment = comment, DateID = ActiveDate.Id }); } }
// ersatt med hämtar årkurs // hämtar årkurs 2 //public static List<Child> GetSecondGraders() //{ // using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) // { // var output = connection.Query<Child>($@"SELECT child.id, child.firstname, child.lastname, child.leavealone, class.name AS Class, department.id AS avdelning // FROM((child INNER JOIN class ON class_id = class.id) INNER JOIN department ON department_id = department.id) // WHERE department_id = 2 ORDER BY class_id DESC;").ToList(); // return output; // } //} //// hämtar årkurs 3 //public static List<Child> GetThirdGraders() //{ // using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) // { // var output = connection.Query<Child>($@"SELECT child.id, child.firstname, child.lastname, child.leavealone, class.name AS Class, department.id AS avdelning // FROM((child INNER JOIN class ON class_id = class.id) // INNER JOIN department ON department_id = department.id) // WHERE department_id = 4 ORDER BY class_id DESC;").ToList(); // return output; // } //} //Hämtar alla anställda public static List <Staff> GetAllStaff() { using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Staff>($@"SELECT staff.id, firstname, lastname, email, department.name AS department FROM ((staff INNER JOIN department_staff on id = department_staff.staff_id) INNER JOIN department on department.id = department_staff.department_id) WHERE department.id = 1 OR department.id = 2 OR department.id = 4" ).ToList(); return(output); } }
//Hämtar alla datum public static List <Date> GetDays(Weeks week) { var weeks = week.Week; using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Date>($@"SELECT dates.id, week, dates.day FROM dates WHERE week=@Week GROUP BY dates.id, week, dates.day ORDER BY dates.id ASC", new { Week = weeks }).ToList(); return(output); } }
//Hämtar specifikt barn SÖK för- och efternamn. public static List <Child> GetChildren(string input) { InputHandler inputhandler = new InputHandler(); var a = inputhandler.Uppercase(input); using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { var output = connection.Query <Child>($@"SELECT child.id, child.firstname, child.lastname, child.leavealone, class_id, class.name AS Class FROM(child INNER JOIN class on class_id = class.id) WHERE child.firstname LIKE @A OR child.lastname LIKE @A", new { A = '%' + a + '%' }).ToList(); return(output); } }
// Uppdatera barnuppgifter public static void UpdateChildProperties(string firstname, string lastname, int age, int classid) { var id = Activechild.Id; InputHandler inputhandler = new InputHandler(); var a = inputhandler.Uppercase(firstname); var b = inputhandler.Uppercase(lastname); using (IDbConnection connection = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { connection.Execute($@"UPDATE child SET firstname = @Firstname, lastname = @Lastname, age = @Age, class_id = @Classid WHERE id = @Id", new { Firstname = firstname, Lastname = lastname, Age = age, Classid = classid, Id = id }); } }
//Hämta scheman för barn Vill ha med category attendance ID för dag/barnet public static List <Schedule> GetSchedule(string day) { var Id = Activechild.Id; Schedule s = new Schedule(); List <Schedule> schedules = new List <Schedule>(); using (var conn = new NpgsqlConnection(ConnString.ConnVal("dbConn"))) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = $@"SELECT lecture.name AS Lecturename, dates.day AS Day, time.timestart AS Timestart, time.timefinish AS Timefinish FROM ((((((child INNER JOIN schedule ON child.id = child_id) INNER JOIN schedule_lecture ON schedule.id = schedule_id) INNER JOIN lecture ON lecture__id = lecture.id) INNER JOIN lecture_dates_time ON lecture.id = lecture_id) INNER JOIN dates ON dates_id = dates.id) INNER JOIN time ON time_id = time.id) WHERE child.id = @Id AND dates.day=@Day ORDER BY time.timestart ASC"; cmd.Parameters.AddWithValue("Id", Id); cmd.Parameters.AddWithValue("Day", day); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { s = new Schedule() { Lecturename = reader["Lecturename"].ToString(), Day = reader["Day"].ToString(), Timestart = Convert.ToDateTime((reader["Timestart"]).ToString()), Timefinish = Convert.ToDateTime((reader["Timefinish"]).ToString()) }; schedules.Add(s); s.changeDateTime(); } } } } return(schedules); }
public TEXCSITRAuthentication(ConnString enumConnString, int intSessionTimeoutMins) { SessionTimeOutMins = intSessionTimeoutMins; this.SQLConn = new System.Data.SqlClient.SqlConnection(); switch (enumConnString) { case ConnString.TEST : this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_TEMP3_EX_Itr_Authenticator_itr_user); // TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_TEMP3; break; case ConnString.EX : this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_EX_Itr_Authenticator_itr_user); //TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_EX; break; default: this.SQLConn.ConnectionString = TEXCSDBUtil.DBGetConnectionStr(EXCSDBUtil.TEXCSDBUtil.enmdb_EXDBApplUser.db_EXSQL06_TEMP3_EX_Itr_Authenticator_itr_user); // TEXCSDBUtil.C_CONSTR_ITR_EXSQL06_TEMP3; break; } this.SQLCmd = new System.Data.SqlClient.SqlCommand(); this.SQLCmd.CommandType = CommandType.StoredProcedure; this.SQLCmd.Connection = this.SQLConn; this.SQLConn.Open(); }