public Teacher GetByTeacherID(int teacherID, int schoolID)
{
SqlConnection conn = null;
SqlDataReader drUsers;
String strSqlUser;
SqlCommand cmdUser;
SqlParameter prmteacherID = null;
SqlParameter prmSchoolID = null;
try
{
Teacher objUser = new Teacher();
conn = new SqlConnection(Functions.GetConnectionString());
strSqlUser = "******";
cmdUser = new SqlCommand(strSqlUser, conn);
cmdUser.CommandType = CommandType.StoredProcedure;
prmteacherID = new SqlParameter();
prmteacherID.ParameterName = "@teacherID";
prmteacherID.SqlDbType = SqlDbType.Int;
prmteacherID.Value = teacherID;
cmdUser.Parameters.Add(prmteacherID);
prmSchoolID = new SqlParameter();
prmSchoolID.ParameterName = "@schoolID";
prmSchoolID.SqlDbType = SqlDbType.Int;
prmSchoolID.Value = schoolID;
cmdUser.Parameters.Add(prmSchoolID);
cmdUser.Connection.Open();
drUsers = cmdUser.ExecuteReader();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
if (drUsers.Read())
{
objUser = new Teacher();
objUser.teacherID = drUsers.GetInt32(drUsers.GetOrdinal("TeacherID"));
objUser.name = drUsers.GetString(drUsers.GetOrdinal("Name"));
objUser.shortName = drUsers.GetString(drUsers.GetOrdinal("shortName"));
objUser.roleID = drUsers.GetInt32(drUsers.GetOrdinal("RoleID"));
objUser.schoolID = drUsers.GetInt32(drUsers.GetOrdinal("SchoolID"));
objUser.Dni = drUsers.GetString(drUsers.GetOrdinal("Dni"));
objUser.ImageKey = drUsers.GetString(drUsers.GetOrdinal("ImageKey"));
objUser.endPoint = s3.getTeacherEndPoint(objUser.schoolID, objUser.ImageKey);
}
cmdUser.Connection.Close();
conn.Dispose();
return(objUser);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}
public TeacherDto Obtain(int TeacherID)
{
SqlConnection conn = null;
SqlDataReader drTeachers;
String strSqlTeacher;
SqlCommand cmdTeacher;
SqlParameter prmTeacherID = null;
try
{
TeacherDto objTeacherBe;
objTeacherBe = new TeacherDto();
conn = new SqlConnection(Functions.GetConnectionString());
strSqlTeacher = "GetTeacherByTeacherID";
cmdTeacher = new SqlCommand(strSqlTeacher, conn);
cmdTeacher.CommandType = CommandType.StoredProcedure;
prmTeacherID = new SqlParameter();
prmTeacherID.ParameterName = "@TeacherID";
prmTeacherID.SqlDbType = SqlDbType.Int;
prmTeacherID.Value = TeacherID;
cmdTeacher.Parameters.Add(prmTeacherID);
cmdTeacher.Connection.Open();
drTeachers = cmdTeacher.ExecuteReader();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
if (drTeachers.Read())
{
//Instancio el objeto y le agrego cada uno de sus campos
objTeacherBe = new TeacherDto();
objTeacherBe.TeacherID = drTeachers.GetInt32(drTeachers.GetOrdinal("TeacherID"));
objTeacherBe.Name = drTeachers.GetString(drTeachers.GetOrdinal("Name"));
objTeacherBe.ShortName = drTeachers.GetString(drTeachers.GetOrdinal("ShortName"));
objTeacherBe.Gender = drTeachers.GetString(drTeachers.GetOrdinal("Gender"));
objTeacherBe.SchoolID = drTeachers.GetInt32(drTeachers.GetOrdinal("SchoolID"));
objTeacherBe.Active = drTeachers.GetBoolean(drTeachers.GetOrdinal("Active"));
objTeacherBe.Role_Name = drTeachers.GetString(drTeachers.GetOrdinal("Role_Name"));
objTeacherBe.Email = drTeachers.GetString(drTeachers.GetOrdinal("Email"));
objTeacherBe.AlternativeMail = drTeachers.GetString(drTeachers.GetOrdinal("AlternativeMail"));
objTeacherBe.HomeAddress = drTeachers.GetString(drTeachers.GetOrdinal("HomeAddress"));
objTeacherBe.Phone = drTeachers.GetString(drTeachers.GetOrdinal("Phone"));
objTeacherBe.LastLoginDate = drTeachers.GetDateTime(drTeachers.GetOrdinal("LastLoginDate"));
objTeacherBe.LastPasswordChangedDate = drTeachers.GetDateTime(drTeachers.GetOrdinal("LastPasswordChangedDate"));
objTeacherBe.LastLoginDateShow = TimerAgo.TimeShow(objTeacherBe.LastLoginDate, Formater.ShortDateTime());
objTeacherBe.LastPasswordChangedDateShow = TimerAgo.TimeShow(objTeacherBe.LastPasswordChangedDate, Formater.ShortDateTime());
objTeacherBe.LastLogoutDateShow = TimerAgo.TimeShow(objTeacherBe.LastLogoutDate, Formater.ShortDateTime());
objTeacherBe.LastLoginDateAgo = TimerAgo.TimeAgo(objTeacherBe.LastLoginDate);
objTeacherBe.LastPasswordChangedDateAgo = TimerAgo.TimeAgo(objTeacherBe.LastPasswordChangedDate);
objTeacherBe.LastLogoutDateAgo = TimerAgo.TimeAgo(objTeacherBe.LastLogoutDate);
objTeacherBe.School_Name = drTeachers.GetString(drTeachers.GetOrdinal("School_Name"));
objTeacherBe.ImageKey = drTeachers.GetString(drTeachers.GetOrdinal("ImageKey"));
objTeacherBe.EndPoint = s3.getTeacherEndPoint(objTeacherBe.SchoolID, objTeacherBe.ImageKey);
}
cmdTeacher.Connection.Close();
conn.Dispose();
return(objTeacherBe);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}
public List <EnrollmentDetailListDto> GetStudentsByTutorID(Int32 schoolID, int tutorID, bool isCurrentPeriod, Boolean active, int skip, int pageSize)
{
SqlConnection conn = null;
SqlDataReader reader;
String strSqlUser;
SqlCommand cmdUser;
SqlParameter prmtutorID = null;
SqlParameter prmschoolID = null;
SqlParameter prmIsCurrentPeriod = null;
SqlParameter prmactive = null;
SqlParameter prmskip = null;
SqlParameter prmpageSize = null;
try
{
EnrollmentDetailListDto enrollmentDetail;
List <EnrollmentDetailListDto> lstEnrollmentDetails;
conn = new SqlConnection(Functions.GetConnectionString());
strSqlUser = "******";
cmdUser = new SqlCommand(strSqlUser, conn);
cmdUser.CommandType = CommandType.StoredProcedure;
prmtutorID = new SqlParameter();
prmtutorID.ParameterName = "@tutorID";
prmtutorID.SqlDbType = SqlDbType.Int;
prmtutorID.Value = tutorID;
cmdUser.Parameters.Add(prmtutorID);
prmschoolID = new SqlParameter();
prmschoolID.ParameterName = "@schoolID";
prmschoolID.SqlDbType = SqlDbType.Int;
prmschoolID.Value = schoolID;
cmdUser.Parameters.Add(prmschoolID);
prmactive = new SqlParameter();
prmactive.ParameterName = "@active";
prmactive.SqlDbType = SqlDbType.Bit;
prmactive.Value = active;
cmdUser.Parameters.Add(prmactive);
prmIsCurrentPeriod = new SqlParameter();
prmIsCurrentPeriod.ParameterName = "@isCurrentPeriod";
prmIsCurrentPeriod.SqlDbType = SqlDbType.Bit;
prmIsCurrentPeriod.Value = isCurrentPeriod;
cmdUser.Parameters.Add(prmIsCurrentPeriod);
prmskip = new SqlParameter();
prmskip.ParameterName = "@skip";
prmskip.SqlDbType = SqlDbType.Int;
prmskip.Value = skip;
cmdUser.Parameters.Add(prmskip);
prmpageSize = new SqlParameter();
prmpageSize.ParameterName = "@pageSize";
prmpageSize.SqlDbType = SqlDbType.Int;
prmpageSize.Value = pageSize;
cmdUser.Parameters.Add(prmpageSize);
cmdUser.Connection.Open();
reader = cmdUser.ExecuteReader();
lstEnrollmentDetails = new List <EnrollmentDetailListDto>();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
while (reader.Read())
{
enrollmentDetail = new EnrollmentDetailListDto();
enrollmentDetail.studentID = reader.GetInt32(reader.GetOrdinal("studentID"));
enrollmentDetail.enrollmentID = reader.GetInt32(reader.GetOrdinal("enrollmentID"));
enrollmentDetail.student_code = reader.GetString(reader.GetOrdinal("student_code"));
enrollmentDetail.student_name = reader.GetString(reader.GetOrdinal("student_name"));
enrollmentDetail.student_firstName = reader.GetString(reader.GetOrdinal("student_firstName"));
enrollmentDetail.student_lastName = reader.GetString(reader.GetOrdinal("student_lastName"));
enrollmentDetail.ImageKey = reader.GetString(reader.GetOrdinal("student_imageKey"));
enrollmentDetail.EndPoint = s3.getStudentEndPoint(schoolID, enrollmentDetail.ImageKey);
lstEnrollmentDetails.Add(enrollmentDetail);
}
cmdUser.Connection.Close();
conn.Dispose();
return(lstEnrollmentDetails);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}
public List <EnrollmentDetailListDto> GetStudentsByCourse(Int32 programmingID, Int32 schoolID, Boolean active)
{
SqlConnection conn = null;
SqlDataReader reader;
String sql;
SqlCommand command;
SqlParameter prmprogrammingID = null;
SqlParameter prmschoolID = null;
SqlParameter prmactive = null;
try
{
EnrollmentDetailListDto enrollmentDetail;
List <EnrollmentDetailListDto> lstEnrollmentDetails;
conn = new SqlConnection(Functions.GetConnectionString());
sql = "GetEnrollmentDetailByprogrammingIDByschoolID";
command = new SqlCommand(sql, conn);
command.CommandType = CommandType.StoredProcedure;
prmprogrammingID = new SqlParameter();
prmprogrammingID.ParameterName = "@programmingID";
prmprogrammingID.SqlDbType = SqlDbType.Int;
prmprogrammingID.Value = programmingID;
command.Parameters.Add(prmprogrammingID);
prmschoolID = new SqlParameter();
prmschoolID.ParameterName = "@schoolID";
prmschoolID.SqlDbType = SqlDbType.Int;
prmschoolID.Value = schoolID;
command.Parameters.Add(prmschoolID);
prmactive = new SqlParameter();
prmactive.ParameterName = "@active";
prmactive.SqlDbType = SqlDbType.Bit;
prmactive.Value = active;
command.Parameters.Add(prmactive);
command.Connection.Open();
reader = command.ExecuteReader();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
lstEnrollmentDetails = new List <EnrollmentDetailListDto>();
while (reader.Read())
{
enrollmentDetail = new EnrollmentDetailListDto();
enrollmentDetail.enrollmentDetailID = reader.GetInt32(reader.GetOrdinal("enrollmentDetailID"));
enrollmentDetail.schoolID = reader.GetInt32(reader.GetOrdinal("schoolID"));
enrollmentDetail.enrollmentID = reader.GetInt32(reader.GetOrdinal("enrollmentID"));
enrollmentDetail.studentID = reader.GetInt32(reader.GetOrdinal("studentID"));
enrollmentDetail.student_code = reader.GetString(reader.GetOrdinal("student_code"));
enrollmentDetail.student_name = reader.GetString(reader.GetOrdinal("student_name"));
enrollmentDetail.student_firstName = reader.GetString(reader.GetOrdinal("student_firstName"));
enrollmentDetail.student_lastName = reader.GetString(reader.GetOrdinal("student_lastName"));
enrollmentDetail.ImageKey = reader.GetString(reader.GetOrdinal("student_imageKey"));
enrollmentDetail.EndPoint = s3.getStudentEndPoint(enrollmentDetail.schoolID, enrollmentDetail.ImageKey);
lstEnrollmentDetails.Add(enrollmentDetail);
}
command.Connection.Close();
conn.Dispose();
return(lstEnrollmentDetails);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}
public List <ProgrammingListDto> getCurrentCoursesByTutorID(Int32 schoolID, Int32 tutorID, Boolean active, Boolean isCurrentPeriod)
{
SqlConnection conn = null;
SqlDataReader reader;
String sql;
SqlCommand command;
SqlParameter prmschoolID = null;
SqlParameter prmisCurrentPeriod = null;
SqlParameter prmtutorID = null;
SqlParameter prmactive = null;
try
{
ProgrammingListDto programming;
List <ProgrammingListDto> lstProgrammings;
conn = new SqlConnection(Functions.GetConnectionString());
sql = "GetCurrentCoursesByTutorID";
command = new SqlCommand(sql, conn);
command.CommandType = CommandType.StoredProcedure;
prmschoolID = new SqlParameter();
prmschoolID.ParameterName = "@schoolID";
prmschoolID.SqlDbType = SqlDbType.Int;
prmschoolID.Value = schoolID;
command.Parameters.Add(prmschoolID);
prmisCurrentPeriod = new SqlParameter();
prmisCurrentPeriod.ParameterName = "@isCurrentPeriod";
prmisCurrentPeriod.SqlDbType = SqlDbType.Bit;
prmisCurrentPeriod.Value = isCurrentPeriod;
command.Parameters.Add(prmisCurrentPeriod);
prmtutorID = new SqlParameter();
prmtutorID.ParameterName = "@tutorID";
prmtutorID.SqlDbType = SqlDbType.Int;
prmtutorID.Value = tutorID;
command.Parameters.Add(prmtutorID);
prmactive = new SqlParameter();
prmactive.ParameterName = "@active";
prmactive.SqlDbType = SqlDbType.Bit;
prmactive.Value = active;
command.Parameters.Add(prmactive);
command.Connection.Open();
reader = command.ExecuteReader();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
lstProgrammings = new List <ProgrammingListDto>();
while (reader.Read())
{
programming = new ProgrammingListDto();
programming.programmingID = reader.GetInt32(reader.GetOrdinal("programmingID"));
programming.evaluationFormulaID = reader.GetInt32(reader.GetOrdinal("evaluationFormulaID"));
programming.career_name = reader.GetString(reader.GetOrdinal("career_name"));
programming.schoolID = schoolID;
programming.classroom_name = reader.GetString(reader.GetOrdinal("classroom_name"));
programming.course_name = reader.GetString(reader.GetOrdinal("course_name"));
programming.course_credits = reader.GetInt32(reader.GetOrdinal("course_credits"));
programming.grade = reader.GetInt32(reader.GetOrdinal("grade"));
programming.evaluationPeriod_name = reader.GetString(reader.GetOrdinal("evaluationPeriod_name"));
programming.section_name = reader.GetInt32(reader.GetOrdinal("grade")) + "" + reader.GetString(reader.GetOrdinal("section_name"));
programming.turn_name = reader.GetString(reader.GetOrdinal("turn_name"));
programming.courseID = reader.GetInt32(reader.GetOrdinal("courseID"));
programming.schoolYearID = reader.GetInt32(reader.GetOrdinal("schoolYearID"));
programming.schoolID = reader.GetInt32(reader.GetOrdinal("schoolID"));
programming.careerID = reader.GetInt32(reader.GetOrdinal("careerID"));
programming.sectionID = reader.GetInt32(reader.GetOrdinal("sectionID"));
programming.turnID = reader.GetInt32(reader.GetOrdinal("turnID"));
programming.periodTypeID = reader.GetInt32(reader.GetOrdinal("periodTypeID"));
programming.evaluationPeriodID = reader.GetInt32(reader.GetOrdinal("evaluationPeriodID"));
programming.headquartersID = reader.GetInt32(reader.GetOrdinal("headquartersID"));
programming.grade = reader.GetInt32(reader.GetOrdinal("grade"));
programming.teacherID = reader.GetInt32(reader.GetOrdinal("teacherID"));;
programming.imageKey = reader.GetString(reader.GetOrdinal("imageKey"));
programming.endPoint = s3.getTeacherEndPoint(programming.schoolID, programming.imageKey);
programming.teacher_name = reader.GetString(reader.GetOrdinal("teacher_name"));
programming.teacher_firstName = reader.GetString(reader.GetOrdinal("teacher_firstName"));
programming.teacher_lastName = reader.GetString(reader.GetOrdinal("teacher_lastName"));
lstProgrammings.Add(programming);
}
command.Connection.Close();
conn.Dispose();
return(lstProgrammings);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}
public StudentListDto GetBystudentIDByactive(Int32 studentID, Boolean active)
{
SqlConnection conn = null;
SqlDataReader reader;
String sql;
SqlCommand command;
SqlParameter prmstudentID = null;
SqlParameter prmactive = null;
try
{
StudentListDto student;
conn = new SqlConnection(Functions.GetConnectionString());
sql = "GetStudentBystudentIDByactive";
command = new SqlCommand(sql, conn);
command.CommandType = CommandType.StoredProcedure;
prmstudentID = new SqlParameter();
prmstudentID.ParameterName = "@studentID";
prmstudentID.SqlDbType = SqlDbType.Int;
prmstudentID.Value = studentID;
command.Parameters.Add(prmstudentID);
prmactive = new SqlParameter();
prmactive.ParameterName = "@active";
prmactive.SqlDbType = SqlDbType.Bit;
prmactive.Value = active;
command.Parameters.Add(prmactive);
command.Connection.Open();
reader = command.ExecuteReader();
student = new StudentListDto();
AmazonS3 s3 = new AmazonS3();
s3 = AmazonS3Factory.setAmazonS3();
while (reader.Read())
{
student.code = reader.GetString(reader.GetOrdinal("code"));
student.name = reader.GetString(reader.GetOrdinal("name"));
student.otherName = reader.GetString(reader.GetOrdinal("otherName"));
student.firstName = reader.GetString(reader.GetOrdinal("firstName"));
student.lastName = reader.GetString(reader.GetOrdinal("lastName"));
student.imageKey = reader.GetString(reader.GetOrdinal("imageKey"));
student.dni = reader.GetString(reader.GetOrdinal("dni"));
student.schoolID = reader.GetInt32(reader.GetOrdinal("schoolID"));
student.phone = reader.GetString(reader.GetOrdinal("phone"));
student.email = reader.GetString(reader.GetOrdinal("email"));
student.alternativeMail = reader.GetString(reader.GetOrdinal("alternativeMail"));
student.homeAddress = reader.GetString(reader.GetOrdinal("homeAddress"));
student.career_name = reader.GetString(reader.GetOrdinal("career_name"));
student.endPoint = s3.getStudentEndPoint(student.schoolID, student.imageKey);
}
command.Connection.Close();
conn.Dispose();
return(student);
}
catch (Exception ex)
{
conn.Dispose();
throw ex;
}
}