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