public ActionResult AttendanceList()
{
Session["Class"] = Session["Class"];
String PuzzleID = TempData["Puzzle"].ToString();
String Class = TempData["Class"].ToString();
String StartDate = TempData["StartDate"].ToString();
String EndDate = TempData["EndDate"].ToString();
string connectionString = @"Data Source=msi;Initial Catalog=SEFASSIGNMENT;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
sqlConnection.Open();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT STU_ID FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"PUZZLE_ID\"='" + PuzzleID + "' AND CONVERT(VARCHAR(25), datetime_stamp, 126) BETWEEN '" + StartDate + "%' AND '" + EndDate + "%'");
sqlCommand.Connection = sqlConnection;
SqlDataReader myreader = sqlCommand.ExecuteReader();
List <String> StudentIDList = new List <String>();
while (myreader.Read())
{
StudentIDList.Add(myreader[0].ToString());
}
myreader.Close();
sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT Attempt_Score FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"PUZZLE_ID\"='" + PuzzleID + "' AND CONVERT(VARCHAR(25), datetime_stamp, 126) BETWEEN '" + StartDate + "%' AND '" + EndDate + "%'");
sqlCommand.Connection = sqlConnection;
SqlDataReader newreader = sqlCommand.ExecuteReader();
List <String> AttemptScore = new List <String>();
while (newreader.Read())
{
AttemptScore.Add(newreader[0].ToString());
}
newreader.Close();
sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT CONVERT(VARCHAR(25), datetime_stamp, 126) DateTime_Stamp FROM [SEFASSIGNMENT].[dbo].[Attempt] WHERE \"PUZZLE_ID\"='" + PuzzleID + "' AND CONVERT(VARCHAR(25), datetime_stamp, 126) BETWEEN '" + StartDate + "%' AND '" + EndDate + "%'");
sqlCommand.Connection = sqlConnection;
SqlDataReader oldreader = sqlCommand.ExecuteReader();
List <String> DateTime = new List <String>();
while (oldreader.Read())
{
DateTime.Add(oldreader[0].ToString());
}
oldreader.Close();
SqlDataReader newmyreader;
List <String> StudentNameList = new List <String>();
for (int i = 0; i < StudentIDList.Count; i++)
{
sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT STU_NAME FROM [SEFASSIGNMENT].[dbo].[Student] WHERE \"Stu_ID\"='" + StudentIDList[i] + "'");
sqlCommand.Connection = sqlConnection;
newmyreader = sqlCommand.ExecuteReader();
while (newmyreader.Read())
{
StudentNameList.Add(newmyreader[0].ToString());
}
newmyreader.Close();
}
SqlDataReader reader;
List <String> ClassNameList = new List <String>();
for (int i = 0; i < StudentIDList.Count; i++)
{
sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT CLASS_ID FROM [SEFASSIGNMENT].[dbo].[Student] WHERE \"Stu_ID\"='" + StudentIDList[i] + "'");
sqlCommand.Connection = sqlConnection;
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
ClassNameList.Add(reader[0].ToString());
}
reader.Close();
}
for (int i = StudentIDList.Count - 1; i >= 0; --i)
{
ClassNameList[i] = ClassNameList[i].ToUpper();
Class = Class.ToUpper();
if (ClassNameList[i].Equals(Class))
{
}
else
{
ClassNameList.RemoveAt(i);
StudentNameList.RemoveAt(i);
DateTime.RemoveAt(i);
StudentIDList.RemoveAt(i);
AttemptScore.RemoveAt(i);
}
}
Session["Class"] = Class;
List <AttendanceList> List = new List <AttendanceList>();
for (int i = 0; i < StudentNameList.Count; i++)
{
List.Add(new AttendanceList()
{
StudentID = StudentIDList[i], StudentName = StudentNameList[i], DateTime = DateTime[i], Score = AttemptScore[i]
});
}
AttendancesList CL = new AttendancesList();
CL.ViewAttendanceList = List;
return(View(CL));
}
public ActionResult AttendanceList(String action, AttendancesList cl)
{
var doc1 = new Document();
string path = String.Format("D:\\Users\\Sam\\Desktop\\Projects\\SEF\\SEF Assignment\\PDF");
Session["LecID"] = Session["LecID"];
TempData["Class"] = TempData["Class"];
Session["Class"] = Session["Class"];
String LecID = Session["LecID"].ToString();
String selectedclass = Session["Class"].ToString();
PdfWriter.GetInstance(doc1,
new FileStream(path + "\\" + selectedclass + "_AttendanceList.pdf", FileMode.Create));
LecID = LecID.ToString();
if (action.Equals("home"))
{
return(RedirectToAction("HomeLec", "HomeLec"));
}
else if (action.Equals("back"))
{
return(RedirectToAction("ManageClass"));
}
else if (action.Equals("download"))
{
List <String> StudentIDList = new List <String>();
List <String> StudentNameList = new List <String>();
List <String> ScoreList = new List <String>();
List <String> DateList = new List <string>();
try
{
foreach (var item in cl.ViewAttendanceList)
{
StudentIDList.Add(item.StudentID);
StudentNameList.Add(item.StudentName);
ScoreList.Add(item.Score);
DateList.Add(item.DateTime);
}
doc1.Open();
PdfPTable table = new PdfPTable(5);
table.TotalWidth = 500f;
table.LockedWidth = true;
float[] widths = new float[] { 50f, 100f, 150f, 100f, 100f };
table.SetWidths(widths);
table.HorizontalAlignment = 0;
table.SpacingBefore = 20f;
table.SpacingAfter = 30f;
int counter = 1;
PdfPCell cell = new PdfPCell(new Phrase("Attendance List for Class: " + selectedclass));
String stcounter;
cell.Colspan = 5;
cell.Border = 0;
cell.HorizontalAlignment = 1;
table.AddCell(cell);
for (int i = 0; i < StudentIDList.Count; i++)
{
stcounter = counter.ToString();
table.AddCell(stcounter);
counter++;
table.AddCell(StudentIDList[i]);
table.AddCell(StudentNameList[i]);
table.AddCell(ScoreList[i]);
table.AddCell(DateList[i]);
}
doc1.Add(table);
doc1.Close();
string filename = (selectedclass + "_AttendanceList.pdf");
Response.ContentType = "application/octet-stream";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
string filepath =
String.Format("D:\\Users\\Sam\\Desktop\\Projects\\SEF\\SEF Assignment\\PDF\\" + filename);
// string filepath = Server.MapPath("~/SavedFolder/" + filename);
Response.TransmitFile(filepath);
Response.End();
return(RedirectToAction("ManageClass"));
}
catch (Exception ex)
{
return(RedirectToAction("Error"));
}
}
return(View());
}
public void AddAttendance(Attendance attendance)
{
AttendancesList.Add(attendance);
}
