//A method that gets office staff from the database and returns list of office staff
public static List <ClassEmployee> EmployeeList()
{
string querry = "SELECT [Employee_id],[Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],[User_id] FROM [db_Clinic].[dbo].[tbl_Employee]";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
List <ClassEmployee> employeeList = new List <ClassEmployee>();
while (dr.Read())
{
ClassEmployee employee = new ClassEmployee();
employee.EmployeeId = dr.GetInt32("Employee_id");
employee.Name = dr.GetString("Name");
employee.Surname = dr.GetString("Surname");
employee.PhoneNumber = dr.GetString("Phone_number");
employee.DateOfBirth = dr.GetDateTime("Date_of_birth");
employee.PersonalIdentityNumber = dr.GetString("Personal_identity_number");
employee.User_id = dr.GetInt32("User_id");
employeeList.Add(employee);
}
sqlCon.Close();
return(employeeList);
}
//Uses Method NotSpecifiedUserIndex and additionally gets logins, emails, and permission id of that users and returns everything in list
public static List <ClassEmployee> NotSpecifiedEmployee()
{
List <int> indexList = NotSpecifiedEmployeeIndex();
List <ClassEmployee> employeeList = new List <ClassEmployee>();
if (indexList.Count == 0)
{
return(employeeList);
}
string querry = "USE [db_Clinic] SELECT [Employee_id],[Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],tbl_Employee.[User_id],tbl_User.Permission_id,tbl_Permission.Type_of_permission FROM [dbo].[tbl_Employee],tbl_User, dbo.tbl_Permission where dbo.tbl_Permission.Permission_id=tbl_User.Permission_id and tbl_Employee.User_id=tbl_User.User_id and [tbl_Employee].Employee_id in(";
string lastItem = "";
foreach (int item in indexList)
{
querry = querry + item.ToString() + ",";
lastItem = item.ToString();
}
querry = querry + lastItem + ")";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
SqlDataReader dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
ClassEmployee employee = new ClassEmployee();
employee.EmployeeId = dr.GetInt32("Employee_id");
employee.Name = dr.GetString("Name");
employee.Surname = dr.GetString("Surname");
employee.PhoneNumber = dr.GetString("Phone_number");
employee.DateOfBirth = dr.GetDateTime("Date_of_birth");
employee.PersonalIdentityNumber = dr.GetString("Personal_identity_number");
employee.User_id = dr.GetInt32("User_id");
ClassPermission permission = new ClassPermission();
permission.PermissionId = dr.GetInt32("Permission_id");
permission.Permission = dr.GetString("Type_of_permission");
employee.Permission = permission;
employeeList.Add(employee);
}
sqlCon.Close();
return(employeeList);
}
//Method that updates office staff in database table when you edit office staff in program
public static void UpdateEmployee(ClassEmployee employee)
{
string querry = "USE [db_Clinic] UPDATE tbl_employee SET[Name] = @Name,[Surname]= @Surname,[Phone_number]= @Phone_number, Date_of_birth = @Date_of_birth WHERE Employee_id = @Employee_id";
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
sqlCommand.Parameters.AddWithValue("@Employee_id", employee.EmployeeId);
sqlCommand.Parameters.AddWithValue("@Name", employee.Name);
sqlCommand.Parameters.AddWithValue("@Surname", employee.Surname);
sqlCommand.Parameters.AddWithValue("@Phone_number", employee.PhoneNumber);
sqlCommand.Parameters.AddWithValue("@Date_of_birth", employee.DateOfBirth);
SqlDataReader dr = sqlCommand.ExecuteReader();
sqlCon.Close();
}
//Method that adds office staff to database table when you add office staff member in program
public static void AddNewEmployee(ClassEmployee employee)
{
//Add office staff
string querryp1 = "USE[db_Clinic] INSERT INTO [dbo].tbl_Employee ([Name],[Surname],[Phone_number],[Date_of_birth],[Personal_identity_number],[User_id]) ";
string querryp2 = "VALUES(@Name,@Surname,@Phone_number,@Date_of_birth,@Personal_identity_number,@User_id)";
string querry = querryp1 + querryp2;
SqlConnection sqlCon = new SqlConnection(ConString);
if (sqlCon.State == ConnectionState.Closed)
{
sqlCon.Open();
}
SqlCommand sqlCommand = new SqlCommand(querry, sqlCon);
sqlCommand.Parameters.AddWithValue("@Name", employee.Name);
sqlCommand.Parameters.AddWithValue("@Surname", employee.Surname);
sqlCommand.Parameters.AddWithValue("@Phone_number", employee.PhoneNumber);
sqlCommand.Parameters.AddWithValue("@Date_of_birth", employee.DateOfBirth);
sqlCommand.Parameters.AddWithValue("@Personal_identity_number", employee.PersonalIdentityNumber);
sqlCommand.Parameters.AddWithValue("@User_id", employee.User_id);
SqlDataReader dr = sqlCommand.ExecuteReader();
sqlCon.Close();
}