public object AllSlotsOfATeacher(int Id)
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
try
{
Connection.Open();
var IsTeacherAvailable = _userRepo.GetById("Users", Id);
if (IsTeacherAvailable == null)
{
return("Teacher Not Available");
}
string query = "SELECT DISTINCT S.*, sb.Name as Subject_Name,T.Class_Id, C.Name AS Class_Name " +
"from Slot S INNER JOIN Time_Table T ON S.Time_Table_Id=T.Id INNER JOIN Subject sb ON S.Subject_Id=sb.Id INNER JOIN Class C ON T.Class_Id = C.Id" +
" WHERE S.Teacher_Id = @Teacher_Id";
SqlCommand queryCMD = new SqlCommand(query, Connection);
queryCMD.Parameters.AddWithValue("Teacher_Id", Id);
SqlDataReader reader = queryCMD.ExecuteReader();
List <SlotVM> slotVMs = new List <SlotVM>().ToList();
while (reader.Read())
{
SlotVM slot = new SlotVM()
{
Id = Convert.ToInt32(reader["Id"]),
Day = reader["Day"].ToString(),
Period_No = reader["Period_No"].ToString(),
Time_Table_Id = Convert.ToInt32(reader["Time_Table_Id"]),
Teacher_Id = Convert.ToInt32(reader["Teacher_Id"]),
Subject_Id = Convert.ToInt32(reader["Subject_Id"]),
Subject_Name = reader["Subject_Name"].ToString(),
Class_Id = Convert.ToInt32(reader["Class_Id"]),
Class_Name = reader["Class_Name"].ToString()
};
if (reader["Resource_Id"] == DBNull.Value)
{
slot.Resource_Id = 0;
}
else
{
slot.Resource_Id = Convert.ToInt32(reader["Resource_Id"]);
}
slotVMs.Add(slot);
}
return(slotVMs);
}
catch (Exception e)
{
return(e.Message);
}
finally
{
Connection.Close();
}
}
}
public ActionResult UpdateSlot([FromBody] SlotVM model)
{
var test = _slotService.GetSlot(model.Id);
if (test == null)
{
return(NotFound(400));
}
_slotService.UpdateSlot(model.Adapt(test));
_slotService.Save();
return(Ok(200));
}
public object GetDetailsOfATimeTableByClassId(int ClassId)
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
try
{
Connection.Open();
string TimeTableDetails = "select * from Time_Table where Class_Id=@ClassId ";
SqlCommand QueryCommand = new SqlCommand(TimeTableDetails, Connection);
QueryCommand.Parameters.AddWithValue("@ClassId", ClassId);
SqlDataReader reader = QueryCommand.ExecuteReader();
reader.Read();
if (!reader.HasRows)
{
reader.Close();
return("Time Table Not Found");
}
Time_Table time_Table = new Time_Table()
{
Id = Convert.ToInt32(reader["Id"]),
Name = Convert.ToString(reader["Name"]),
Grade = Convert.ToInt32(reader["Grade"]),
Admin_Id = Convert.ToInt32(reader["Admin_Id"]),
Class_Id = ClassId
};
reader.Close();
string checkSlot = "select Id from Time_Table where Class_Id=@Class_Id";
SqlCommand checkSlotCommand = new SqlCommand(checkSlot, Connection);
checkSlotCommand.Parameters.AddWithValue("@Class_Id", ClassId);
SqlDataReader checkSlotReader = checkSlotCommand.ExecuteReader();
checkSlotReader.Read();
string query = "SELECT distinct s.Id,s.Day,s.Period_No,s.Time_Table_Id,s.Resource_Id,s.Teacher_Id,u.Name AS Teacher_Name,s.Subject_Id,sb.Name AS Subject_Name" +
" FROM Slot s INNER JOIN Subject sb ON s.Subject_Id=sb.Id INNER JOIN users u ON s.Teacher_Id=u.Id WHERE s.Time_Table_Id=@S_Id";
using (SqlCommand QueryCMD = new SqlCommand(query, Connection))
{
QueryCMD.Parameters.AddWithValue("@S_Id", Convert.ToInt32(checkSlotReader["Id"]));
checkSlotReader.Close();
SqlDataReader sreader = QueryCMD.ExecuteReader();
List <SlotVM> slotVMs = new List <SlotVM>().ToList();
while (sreader.Read())
{
SlotVM slot = new SlotVM()
{
Id = Convert.ToInt32(sreader["Id"]),
Day = sreader["Day"].ToString(),
Period_No = sreader["Period_No"].ToString(),
Time_Table_Id = Convert.ToInt32(sreader["Time_Table_Id"]),
Teacher_Id = Convert.ToInt32(sreader["Teacher_Id"]),
Teacher_Name = sreader["Teacher_Name"].ToString(),
Subject_Id = Convert.ToInt32(sreader["Subject_Id"]),
Subject_Name = sreader["Subject_Name"].ToString()
};
if (sreader["Resource_Id"] == DBNull.Value)
{
slot.Resource_Id = 0;
}
else
{
slot.Resource_Id = Convert.ToInt32(sreader["Resource_Id"]);
}
slotVMs.Add(slot);
}
TableData td = new TableData()
{
Id = time_Table.Id,
Name = time_Table.Name,
Grade = time_Table.Grade,
Class_id = time_Table.Class_Id,
Admin_Id = time_Table.Admin_Id,
slot = slotVMs
};
sreader.Close();
return(td);
}
}catch (Exception e)
{
return(e.Message);
}
finally
{
Connection.Close();
}
}
}
public Object GetTimeTableDetails(int Id)
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
try
{
Connection.Open();
var TableDetails = _timetableRepo.GetById("Time_Table", Id);
if (TableDetails == null)
{
return("Time Table Not Found");
}
var AllSlotsOFATimeTable = _slotRepo.GetByOneParameter("Slot", "Time_Table_Id", Convert.ToString(Id));
string query = "SELECT distinct s.Id,s.Day,s.Period_No,s.Time_Table_Id,s.Resource_Id,s.Teacher_Id,u.Name AS Teacher_Name,s.Subject_Id,sb.Name AS Subject_Name" +
" FROM Slot s INNER JOIN Subject sb ON s.Subject_Id=sb.Id INNER JOIN users u ON s.Teacher_Id=u.Id WHERE s.Time_Table_Id=@S_Id";
using (SqlCommand QueryCommand = new SqlCommand(query, Connection))
{
QueryCommand.Parameters.AddWithValue("@S_Id", Id);
SqlDataReader reader = QueryCommand.ExecuteReader();
List <SlotVM> slotVMs = new List <SlotVM>().ToList();
while (reader.Read())
{
SlotVM slot = new SlotVM()
{
Id = Convert.ToInt32(reader["Id"]),
Day = reader["Day"].ToString(),
Period_No = reader["Period_No"].ToString(),
Time_Table_Id = Convert.ToInt32(reader["Time_Table_Id"]),
Teacher_Id = Convert.ToInt32(reader["Teacher_Id"]),
Teacher_Name = reader["Teacher_Name"].ToString(),
Subject_Id = Convert.ToInt32(reader["Subject_Id"]),
Subject_Name = reader["Subject_Name"].ToString()
};
if (reader["Resource_Id"] == DBNull.Value)
{
slot.Resource_Id = 0;
}
else
{
slot.Resource_Id = Convert.ToInt32(reader["Resource_Id"]);
}
slotVMs.Add(slot);
}
TableData td = new TableData()
{
Id = TableDetails.Id,
Name = TableDetails.Name,
Grade = TableDetails.Grade,
Class_id = TableDetails.Class_Id,
Admin_Id = TableDetails.Admin_Id,
slot = slotVMs
};
reader.Close();
return(td);
}
}catch (Exception e)
{
return(e.Message);
}
finally
{
Connection.Close();
}
}
}
public object FindVanantSlotsInADay()
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
try
{
var day = DateTime.Today.DayOfWeek.ToString();
Connection.Open();
string Query = "SELECT distinct S.*,SB.Name as Subject_Name " +
"FROM SLOT S INNER JOIN ATTENDANCE A ON S.Teacher_Id=A.User_Id INNER JOIN Subject SB on SB.Id=S.Subject_Id " +
"WHERE S.Day LIKE @Day and A.Status = 0 and a.DATE = CONVERT(date, GETDATE()) ";
SqlCommand QueryCMD = new SqlCommand(Query, Connection);
QueryCMD.Parameters.AddWithValue("@Date", DateTime.Today);
QueryCMD.Parameters.AddWithValue("@Day", "Monday");
SqlDataReader reader = QueryCMD.ExecuteReader();
List <SlotVM> slots = new List <SlotVM>();
while (reader.Read())
{
SlotVM slot = new SlotVM()
{
Id = Convert.ToInt32(reader["Id"]),
Day = Convert.ToString(reader["Day"]),
//Start_Time = reader.GetTimeSpan(2),
//End_Time = reader.GetTimeSpan(3),
Period_No = Convert.ToString(reader["Period_No"]),
Time_Table_Id = Convert.ToInt32(reader["Time_Table_Id"]),
//Resource_Id = Convert.ToInt32(reader["Resource_Id"]),
Teacher_Id = Convert.ToInt32(reader["Teacher_Id"]),
Subject_Id = Convert.ToInt32(reader["Subject_Id"]),
Subject_Name = reader["Subject_Name"].ToString(),
};
slots.Add(slot);
}
reader.Close();
string getAllocatePeriods = "SELECT Slot_Id from Updates where Date= CONVERT(date, GETDATE())";
SqlCommand command = new SqlCommand(getAllocatePeriods, Connection);
SqlDataReader SlotReader = command.ExecuteReader();
while (SlotReader.Read())
{
var result = slots.Find(x => x.Id == Convert.ToInt32(SlotReader["Slot_Id"]));
//var result = slots.Find(x => x.Id == 6);
slots.Remove(result);
}
SlotReader.Close();
if (slots.Any())
{
return(slots);
}
else
{
return("No slots not covered");
}
}
catch (Exception e)
{
return(e.Message);
}
finally
{
Connection.Close();
}
}
}
