public object Add(Time_Table timeTable)
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
Connection.Open();
string checkTimeTableAvaiblity = "SELECT * from Time_Table where Class_Id=@Class_Id";
SqlCommand checkCommand = new SqlCommand(checkTimeTableAvaiblity, Connection);
checkCommand.Parameters.AddWithValue("@Class_Id", timeTable.Class_Id);
SqlDataReader reader = checkCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Close();
return("Time Table already Available For Class");
}
reader.Close();
try
{
using (SqlCommand insertCommand = new SqlCommand("INSERT INTO Time_Table(Name, Grade, Admin_Id, Class_Id) output inserted.Id VALUES(@Name, @Grade, @Admin_Id, @Class_Id)", Connection))
{
insertCommand.Parameters.AddWithValue("@Name", timeTable.Name);
insertCommand.Parameters.AddWithValue("@Grade", timeTable.Grade);
insertCommand.Parameters.AddWithValue("@Admin_Id", timeTable.Admin_Id);
insertCommand.Parameters.AddWithValue("@Class_Id", timeTable.Class_Id);
int Id = (int)insertCommand.ExecuteScalar();
if (Id > 0)
{
Time_Table time_Table = new Time_Table()
{
Id = Id,
Name = timeTable.Name,
Grade = timeTable.Grade,
Admin_Id = timeTable.Grade,
Class_Id = timeTable.Class_Id
};
return(time_Table);
}
else
{
return(false);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
Connection.Close();
}
}
}
public IActionResult UpdateATimeTable([FromBody] Time_Table time_Table)
{
var Result = _timeTableServices.Add(time_Table);
if (Result.GetType() == typeof(Time_Table))
{
return(Ok(Result));
}
else
{
return(BadRequest(Result));
}
}
public object Update(Time_Table time_Table)
{
using (SqlConnection Connection = new SqlConnection(ConnectionInformation))
{
Connection.Open();
string InsertCommand = "UPDATE Time_Table SET Name=@Name,Grade=@Grade,Admin_Id=@Admin_Id,Class_Id=@Class_Id WHERE Id=" + time_Table.Id;
try
{
using (SqlCommand insertCommand = new SqlCommand(InsertCommand, Connection))
{
insertCommand.Parameters.AddWithValue("@Name", time_Table.Name);
insertCommand.Parameters.AddWithValue("@Grade", time_Table.Grade);
insertCommand.Parameters.AddWithValue("@Admin_Id", time_Table.Admin_Id);
insertCommand.Parameters.AddWithValue("@Class_Id", time_Table.Class_Id);
int Id = (int)insertCommand.ExecuteScalar();
if (Id > 0)
{
Time_Table timeTable = new Time_Table()
{
Id = Id,
Name = time_Table.Name,
Grade = time_Table.Grade,
Admin_Id = time_Table.Grade,
Class_Id = time_Table.Class_Id
};
return(timeTable);
}
else
{
return("Time Table Update Not done");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
finally
{
Connection.Close();
}
}
}
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();
}
}
}
