public Exception GetCareerByMatter(ref Matter matter)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_R_CAREER_MATTER.GET_CAREER_BY_MATTER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Byte).Value = matter.MatterId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Career career = new Career();
career = MappingCareer(oracleDataReader);
matter.Career.Add(career);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public bool PasswordRecovery(string email, out byte[] passwordHash, out byte[] salt)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PASSWORD_RECUPERATION";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = email;
oracleCommand.Parameters.Add("PASSWORD_OUT_PARAMETER", OracleDbType.Blob).Direction = ParameterDirection.Output;
oracleCommand.Parameters.Add("SALT_OUT_PARAMETER", OracleDbType.Blob).Direction = ParameterDirection.Output;
passwordHash = null;
salt = null;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
passwordHash = (byte[])(oracleCommand.Parameters["PASSWORD_OUT_PARAMETER"].Value);
salt = (byte[])(oracleCommand.Parameters["SALT_OUT_PARAMETER"].Value);
return(true);
}
catch (Exception)
{
return(false);
}
}
}
public Exception GetMattersByTeacherFilterBySemester(ref Teacher teacher, byte semesterLogId)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_R_TEACHER_MATTER.GET_MATTERS_BY_TEACHER_FILTER_BY_SEMESTER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId;
oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLogId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Matter matter = new Matter();
matter = MappingMatter(oracleDataReader);
teacher.Matter.Add(matter);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetCareersByFaculty(ref Faculty faculty)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_CAREER.GET_CAREERS_BY_FACULTY";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("FACULTY_ID_PARAMETER", OracleDbType.Byte).Value = faculty.FacultyId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Career career = new Career();
career = Mapping(oracleDataReader);
faculty.Career.Add(career);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetActiveFacultiesList(ref ICollection <Faculty> faculties)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_FACULTY.GET_ACTIVE_FACULTIES_LIST";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Faculty faculty = new Faculty();
faculty = Mapping(oracleDataReader);
faculties.Add(faculty);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetActiveCareersList(ref ICollection <Career> careers)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_CAREER.GET_ACTIVE_CAREERS_LIST";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Career career = new Career();
career = Mapping(oracleDataReader);
careers.Add(career);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetScores(ref ICollection <Score> scores)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_SCORE.GET_SCORES";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Score score = new Score();
score = Mapping(oracleDataReader);
scores.Add(score);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetScoresBySemester(ref SemesterLog semesterLog)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_SCORE.GET_SCORES_BY_SEMESTER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLog.SemesterLogId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Score score = new Score();
score = Mapping(oracleDataReader);
semesterLog.Score.Add(score);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception AddTeacher(Teacher teacher)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_TEACHER.ADD_TEACHER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId;
oracleCommand.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.FirstName;
oracleCommand.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.SecondName;
oracleCommand.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.FirstSurname;
oracleCommand.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = teacher.SecondSurname;
oracleCommand.Parameters.Add("IS_UPC_TEACHER_PARAMETER", OracleDbType.Boolean).Value = teacher.IsUpcTeacher;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetAllTeachersList(ref ICollection <Teacher> teachers)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_TEACHER.GET_ALL_TEACHERS_LIST";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Teacher teacher = new Teacher();
teacher = Mapping(oracleDataReader);
teachers.Add(teacher);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetReviewsByTeacher(ref Teacher teacher)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_REVIEW.GET_REVIEW_BY_TEACHER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacher.TeacherId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Review review = new Review();
review = Mapping(oracleDataReader);
teacher.Review.Add(review);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetSemesterLog(ref ICollection <SemesterLog> semesterLogs)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_SEMESTER_LOG.GET_SEMESTER_LOG";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
SemesterLog semesterLog = new SemesterLog();
semesterLog = Mapping(oracleDataReader);
semesterLogs.Add(semesterLog);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception AddRTeacherMatter(string matterId, byte semesterLogId, byte teacherId)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_R_TEACHER_MATTER.ADD_R_TEACHER_MATTER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = teacherId;
oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Varchar2).Value = matterId;
oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = semesterLogId;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetRecentReviews(ref ICollection <Review> reviews)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_REVIEW.GET_RECENT_REVIEW";
oracleCommand.CommandType = CommandType.StoredProcedure;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Review review = new Review();
review = Mapping(oracleDataReader);
reviews.Add(review);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception GetReviewsByCommonUser(ref CommonUser commonUser)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_REVIEW.GET_REVIEW_BY_COMMON_USER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("COMMON_USER_ID", OracleDbType.Long).Value = commonUser.CommonUserId;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
Review review = new Review();
review = Mapping(oracleDataReader);
commonUser.Review.Add(review);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public bool UserNotExist(string email)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_COMMON_USER.CHECK_USER_IS_REGISTERED";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = email;
oracleCommand.Parameters.Add("RETURN_VALUE", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
oracleConnection.Close();
}
catch (Exception ex)
{
oracleConnection.Close();
}
if (oracleCommand.Parameters["RETURN_VALUE"].Value == null)
{
return(false);
}
else
{
return(true);
}
}
}
public Exception Register(ref CommonUser commonUser)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_COMMON_USER.REGISTER_COMMON_USER";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstName;
oracleCommand.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondName;
oracleCommand.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstSurname;
oracleCommand.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondSurname;
oracleCommand.Parameters.Add("EMAIL_PARAMETER", OracleDbType.Varchar2).Value = commonUser.Email;
oracleCommand.Parameters.Add("PASSWORD_PARAMETER", OracleDbType.Blob).Value = commonUser.Password;
oracleCommand.Parameters.Add("SALT_PARAMETER", OracleDbType.Blob).Value = commonUser.Salt;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
oracleConnection.Close();
return(exceptionToReturn);
}
catch (Exception exeption)
{
exceptionToReturn = exeption;
oracleConnection.Close();
return(exceptionToReturn);
}
}
}
public Exception Login(string email, ref CommonUser commonUser)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_COMMON_USER.LOGIN";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("EMAIL", OracleDbType.Varchar2).Value = email;
OracleDataReader oracleDataReader;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleDataReader = oracleCommand.ExecuteReader();
while (oracleDataReader.Read())
{
commonUser = Mapping(oracleDataReader);
}
return(exceptionToReturn);
}
catch (Exception exception)
{
exceptionToReturn = exception;
return(exceptionToReturn);
}
}
}
public Exception AddReview(Review review)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommand = new OracleCommand();
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PACK_REVIEW.ADD_REVIEW";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.Parameters.Add("TEACHER_ID_PARAMETER", OracleDbType.Byte).Value = review.TeacherId;
oracleCommand.Parameters.Add("COMMON_USER_ID_PARAMETER", OracleDbType.Long).Value = review.CommonUserId;
oracleCommand.Parameters.Add("MATTER_ID_PARAMETER", OracleDbType.Varchar2).Value = review.MatterId;
oracleCommand.Parameters.Add("SEMESTER_ID_PARAMETER", OracleDbType.Byte).Value = review.SemesterId;
oracleCommand.Parameters.Add("CONTENT_PARAMETER", OracleDbType.NClob).Value = review.Content;
oracleCommand.Parameters.Add("KNOWLEDGE_PARAMETER", OracleDbType.Byte).Value = review.Knowledge;
oracleCommand.Parameters.Add("NATURALNESS_PARAMETER", OracleDbType.Byte).Value = review.Naturalness;
oracleCommand.Parameters.Add("ATTITUDE_PARAMETER", OracleDbType.Byte).Value = review.Attitude;
oracleCommand.Parameters.Add("MOTIVATION_PRODUCE_PARAMETER", OracleDbType.Byte).Value = review.MotivationProduce;
oracleCommand.Parameters.Add("MATTER_GUIDELINES_PARAMETER", OracleDbType.Byte).Value = review.MatterGuidelines;
oracleCommand.Parameters.Add("WEIGHTED_AVERAGE_PARAMETER", OracleDbType.Decimal).Value = review.WeightedAverage;
oracleCommand.Parameters.Add("IS_ANONYMOUS_PARAMETER", OracleDbType.Boolean).Value = review.IsAnonymous;
oracleCommand.Parameters.Add("FAIL_ANY_MATTER_PARAMETER", OracleDbType.Boolean).Value = review.FailAnyMatter;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleCommand.ExecuteNonQuery();
oracleConnection.Close();
return(exceptionToReturn);
}
catch (Exception exeption)
{
exceptionToReturn = exeption;
oracleConnection.Close();
return(exceptionToReturn);
}
}
}
public Exception UpdateUserInformation(string email, CommonUser commonUser)
{
using (OracleConnection oracleConnection = Connection.GetInstance().ConnectionDB())
{
OracleCommand oracleCommandObtain = new OracleCommand();
OracleCommand oracleCommandUpdate = new OracleCommand();
oracleCommandObtain.Connection = oracleConnection;
oracleCommandObtain.CommandText = "PACK_COMMON_USER.UPDATE_USER_INFORMARTION";
oracleCommandObtain.CommandType = CommandType.StoredProcedure;
oracleCommandUpdate.Parameters.Add("EMAIL_OLD_PARAMETER", OracleDbType.Varchar2).Value = email;
oracleCommandUpdate.Connection = oracleConnection;
oracleCommandUpdate.CommandText = "PACK_COMMON_USER.UPDATE_USER_INFORMARTION";
oracleCommandUpdate.CommandType = CommandType.StoredProcedure;
oracleCommandUpdate.Parameters.Add("FIRST_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstName;
oracleCommandUpdate.Parameters.Add("SECOND_NAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondName;
oracleCommandUpdate.Parameters.Add("FIRST_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.FirstSurname;
oracleCommandUpdate.Parameters.Add("SECOND_SURNAME_PARAMETER", OracleDbType.Varchar2).Value = commonUser.SecondSurname;
oracleCommandUpdate.Parameters.Add("EMAIL_NEW_PARAMETER", OracleDbType.Varchar2).Value = commonUser.Email;
oracleCommandUpdate.Parameters.Add("PASSWORD_PARAMETER", OracleDbType.Blob).Value = commonUser.Password;
oracleCommandUpdate.Parameters.Add("SALT_PARAMETER", OracleDbType.Blob).Value = commonUser.Salt;
Exception exceptionToReturn = null;
try
{
oracleConnection.Open();
oracleCommandObtain.ExecuteNonQuery();
oracleCommandUpdate.ExecuteNonQuery();
oracleConnection.Close();
return(exceptionToReturn);
}
catch (Exception exeption)
{
exceptionToReturn = exeption;
oracleConnection.Close();
return(exceptionToReturn);
}
}
}