public List <TimeTable> GetAllTimeTableList(long ID)
{
List <TimeTable> Result = new List <TimeTable>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("[teacher].[SP_GetTimeTable]", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@ID", ID);
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
TimeTable temp = new TimeTable();
temp.ID = Convert.ToInt32(reader["ID"]);
temp.TeacherID = Convert.ToInt32(reader["TeacherID"]);
temp.WeekID = Convert.ToInt32(reader["WeekID"]);
temp.TimeID = Convert.ToInt32(reader["TimeID"]);
temp.Day = Convert.ToString(reader["Day"]);
temp.SubID = Convert.ToInt32(reader["SubID"]);
temp.Period = Convert.ToString(reader["Period"]);
temp.Sub = Convert.ToString(reader["Sub"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <TeacherNote> GetTeacherNotes(long TeacherID_FK)
{
List <TeacherNote> Result = new List <TeacherNote>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("[teacher].[SP_GetTeacherNotes]", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@TeacherID", TeacherID_FK);
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
TeacherNote temp = new TeacherNote();
temp.ID = Convert.ToInt32(reader["ID"]);
temp.Note = Convert.ToString(reader["Note"]);
temp.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public EditProfileTeacher EditProfile(long TID)
{
EditProfileTeacher result = new EditProfileTeacher();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
string Query = @"select IsNull(ID,'') AS TeacherID, IsNull(Name,'') As Name,IsNull(Email,'') AS Email,IsNull(Password,'') As Password,IsNull(Joining_Date,'') AS Joining_Date,IsNull(MObile_No,'') AS Mobile_NO,IsNUll(Profile_Image,'') AS Profile_Image from teacher.TeachersDetails where ID=@TeacherID";
SqlCommand com = new SqlCommand(Query, con);
com.Parameters.AddWithValue("@TeacherID", TID);
// com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
result.Teacher_Name = Convert.ToString(reader["Name"]);
result.Email = Convert.ToString(reader["Email"]);
result.Password = Convert.ToString(reader["Password"]);
result.JoiningDate = Convert.ToDateTime(reader["Joining_Date"]);
result.MobileNo = Convert.ToString(reader["Mobile_NO"]);
result.ProfileImage = Convert.ToString(reader["Profile_Image"]);
result.ID = Convert.ToInt32(reader["TeacherID"]);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(result);
}
public List <AssignmentDueDate> GetAssignmentDetails(long TeacherID_FK)
{
List <AssignmentDueDate> Result = new List <AssignmentDueDate>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("[teacher].[SP_GetAssignmentDetails]", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@TeacherID_FK", TeacherID_FK);
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
AssignmentDueDate temp = new AssignmentDueDate();
//temp.ID = Convert.ToInt32(reader["ID"]);
temp.Subject = Convert.ToString(reader["Subject"]);
temp.Chapter = Convert.ToString(reader["Chapter"]);
temp.Standard = Convert.ToInt32(reader["Standard"]);
temp.SetDate = Convert.ToDateTime(reader["SetDate"]);
temp.DueDate = Convert.ToDateTime(reader["DueDate"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public string InsertTeacherNote(TeacherNote Info)
{
string Result = string.Empty;
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("[teacher].[SP_InsertTeacherNote]", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@TeacherID_FK", Info.TeacherID_FK);
com.Parameters.AddWithValue("@Note", Info.Note);
con.Open();
int LastRow = Convert.ToInt32(com.ExecuteScalar());
if (LastRow > 0)
{
Result = "Success!" + LastRow;;
}
else
{
Result = "Failed! ";
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <CollegeDay> GetAllNextCollegeUpcoming()
{
List <CollegeDay> Result = new List <CollegeDay>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("SP_GetAllNextCollegeUpcoming", con);
com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
CollegeDay temp = new CollegeDay();
temp.ID = Convert.ToInt16(reader["NxtCollege_ID"]);
temp.CollegeName = Convert.ToString(reader["NxtCollege_Name"]);
temp.Event_Date = Convert.ToString(reader["NxtCollege_AddedDate"]);
temp.IsUpcoming = Convert.ToBoolean(reader["IsUpcoming"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <ExamDetails_Newsfeed> ExamForNewsfeed()
{
List <ExamDetails_Newsfeed> Result = new List <ExamDetails_Newsfeed>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
string query = @"select ID,Exam,ExamDate from admin.ExamDetails where ExamDate>=getdate() and Status=1 order by ExamDate asc";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ExamDetails_Newsfeed temp = new ExamDetails_Newsfeed();
temp.ID = Convert.ToInt32(reader["ID"]);
temp.Exam = Convert.ToString(reader["Exam"]);
temp.tempdate = Convert.ToString(reader["ExamDate"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public string InsertNote(Notes Info)
{
string Result = string.Empty;
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("[dbo].[Insert_Note]", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@UID", Info.UserID_Fk);
com.Parameters.AddWithValue("@Note", Info.Note);
com.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
con.Open();
if (com.ExecuteNonQuery() > 0)
{
string id = com.Parameters["@ID"].Value.ToString();
Result = "Success!" + id;
}
else
{
Result = "Failed! ";
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <EventComment> GetEventComments(long id)
{
List <EventComment> Result = new List <EventComment>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("SP_GetAllEventCommentByEventID", con);
com.Parameters.AddWithValue("@EventID", id);
com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
EventComment temp = new EventComment();
temp.CommentID = Convert.ToInt16(reader["ID"]);
temp.Text = Convert.ToString(reader["Text"]);
temp.CreatedDate = Convert.ToDateTime(reader["InsertedDate"]);
temp.StudentName = Convert.ToString(reader["Student_Name"]);
temp.TeacherName = Convert.ToString(reader["TeacherName"]);
temp.IsStudent = Convert.ToBoolean(reader["IsStudent"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public Event GetEventDetailsByID(long id)
{
Event Result = new Event();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("SP_GetEventDetails", con);
com.Parameters.AddWithValue("@id", id);
com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Result.ID = Convert.ToInt16(reader["ID"]);
Result.Title = Convert.ToString(reader["Title"]);
Result.EventDate = Convert.ToDateTime(reader["EventDate"]);
Result.IsActive = Convert.ToBoolean(reader["IsActive"]);
Result.EventData = Convert.ToString(reader["EventData"]);
break;
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public CollegeDay GetCollegeEventDetailsByID(long id)
{
CollegeDay Result = new CollegeDay();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("SP_GetCollegeEventDetails", con);
com.Parameters.AddWithValue("@id", id);
com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Result.ID = Convert.ToInt16(reader["NxtCollege_ID"]);
Result.video = Convert.ToString(reader["NxtCollege_Video"]);
Result.CollegeName = Convert.ToString(reader["NxtCollege_Name"]);
Result.EventDate = Convert.ToDateTime(reader["NxtCollege_AddedDate"]);
break;
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <Event> GetEventPastData()
{
List <Event> Result = new List <Event>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
SqlCommand com = new SqlCommand("SP_GetAllEventPast", con);
com.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Event temp = new Event();
temp.ID = Convert.ToInt16(reader["ID"]);
temp.Title = Convert.ToString(reader["Title"]);
temp.Event_Date = Convert.ToString(reader["EventDate"]);
temp.IsActive = Convert.ToBoolean(reader["IsActive"]);
temp.EventData = Convert.ToString(reader["EventData"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public string UpdateTeacherProfile(EditProfileTeacher Data)
{
string Result = string.Empty;
try
{
using (SqlConnection con = new SqlConnection(conn))
{
string Query = string.Empty;
if (Data.ProfileImage != null && Data.ProfileImage != "")
{
Query = @" update [teacher].TeachersDetails set Name=@TeacherName ,Email=@Email, Password=@Password, Joining_Date=@JoiningDate, MObile_No=@MobileNo, Profile_Image=@ProfileImage where ID=@TeacherID ";
}
else
{
Query = @" update [teacher].TeachersDetails set Name=@TeacherName ,Email=@Email, Password=@Password, Joining_Date=@JoiningDate, MObile_No=@MobileNo where ID=@TeacherID ";
}
SqlCommand com = new SqlCommand(Query, con);
//com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@TeacherID", Data.ID);
com.Parameters.AddWithValue("@TeacherName", Data.Teacher_Name);
com.Parameters.AddWithValue("@Email", Data.Email);
com.Parameters.AddWithValue("@Password", Data.Password);
com.Parameters.AddWithValue("@JoiningDate", Data.JoiningDate);
com.Parameters.AddWithValue("@MobileNo", Data.MobileNo);
if (Data.ProfileImage != null && Data.ProfileImage != "")
{
com.Parameters.AddWithValue("@ProfileImage", Data.ProfileImage);
}
con.Open();
if (com.ExecuteNonQuery() > 0)
{
Result = "Success!Updated";
}
else
{
Result = "Failed!Process Failed ";
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <Data_Calender> DataForCalender(long UserID)
{
List <Data_Calender> Result = new List <Data_Calender>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
string Query = @" select ID as id,CONVERT(VARCHAR(10),EventDate,101) as Date,Title as Text,'Event' as Type from collegeEvents.Events
union
select NxtCollege_ID as id,CONVERT(VARCHAR(8),NxtCollege_AddedDate,1) as Date,NxtCollege_name as Text,'College' as Type from collegeEvents.NextCollege
union
select ID as id ,CONVERT(VARCHAR(8),ExamDate,1) as Date ,Exam as Text,'Exam' as Type from admin.ExamDetails
union
select studentinfo.Goal.ID,CONVERT(VARCHAR(8),studentinfo.Goal.StartDate,1),
(studentinfo.ExamType.ExamType+'-'+studentinfo.Examsubjects.Subject+'-'+studentinfo.ExamTopics.Topic) as Text,
'Goal' as Type from studentinfo.Goal
inner join studentinfo.ExamTopics on studentinfo.Goal.ExamTopicID_FK=studentinfo.ExamTopics.ID
inner join studentinfo.Examsubjects on studentinfo.ExamTopics.SubjectID=studentinfo.Examsubjects.ID
inner join studentinfo.ExamType on studentinfo.Examtopics.ExamID=studentinfo.ExamType.ID
where studentinfo.Goal.UserID_FK=@UID";
SqlCommand com = new SqlCommand(Query, con);
com.Parameters.AddWithValue("@UID", UserID);
con.Open();
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Data_Calender temp = new Data_Calender();
temp.id = Convert.ToInt32(reader["id"]);
temp.TempDate = Convert.ToString(reader["Date"]);
temp.Text = Convert.ToString(reader["Text"]);
temp.Type = Convert.ToString(reader["Type"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}
public List <GoalDetails> goaldetailsForNewsfeed(long SID)
{
List <GoalDetails> Result = new List <GoalDetails>();
try
{
using (SqlConnection con = new SqlConnection(conn))
{
string query = @"select top 4 studentinfo.Goal.ID,studentinfo.Goal.StartDate,studentinfo.Goal.EndDate,studentinfo.Goal.UserID_FK,studentinfo.Goal.ExamTopicID_FK,
studentinfo.ExamTopics.Topic,studentinfo.ExamTopics.SubjectID,studentinfo.Examsubjects.Subject,studentinfo.ExamTopics.ExamID,studentinfo.ExamType.ExamType from studentinfo.Goal
inner join studentinfo.ExamTopics on studentinfo.Goal.ExamTopicID_FK=studentinfo.ExamTopics.ID
inner join studentinfo.Examsubjects on studentinfo.ExamTopics.SubjectID=studentinfo.Examsubjects.ID
inner join studentinfo.ExamType on studentinfo.ExamTopics.ExamID=studentinfo.ExamType.ID
where StartDate>= GETDATE() and UserID_Fk=@UID order by StartDate asc";
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@UID", SID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
GoalDetails temp = new GoalDetails();
temp.TempStartDate = Convert.ToString(reader["StartDate"]);
temp.TempEndDate = Convert.ToString(reader["EndDate"]);
temp.UserID_FK = Convert.ToInt32(reader["UserID_FK"]);
temp.ExamTopicID_FK = Convert.ToInt32(reader["ExamTopicID_FK"]);
temp.Topic = Convert.ToString(reader["Topic"]);
temp.SubjectID = Convert.ToInt32(reader["ExamTopicID_FK"]);
temp.Subject = Convert.ToString(reader["Subject"]);
temp.ExamTypeID = Convert.ToInt32(reader["ExamID"]);
temp.ExamType = Convert.ToString(reader["ExamType"]);
Result.Add(temp);
}
con.Close();
}
}
catch (Exception ex)
{
WriteLogFile.WriteErrorLog("Error.txt", ex.Message);
}
return(Result);
}