public static void CheckForAssignmentSubDateDATABASE()
{
// Enter the Sub Date time you wanna check
DateTime subDueAssignment = PrivateSchool.SubDateTime();
DateTime subDueAssignmentFrwd = subDueAssignment;
DateTime subDueAssignmentBack = subDueAssignment;
// Make the Date the user want to chake, have the desired range
try
{
// Put the checks for the days here - minus and plus days to check the full week
if (subDueAssignment.DayOfWeek == DayOfWeek.Monday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(4);
subDueAssignmentBack = subDueAssignment.AddDays(0);
}
else if (subDueAssignment.DayOfWeek == DayOfWeek.Tuesday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(3);
subDueAssignmentBack = subDueAssignment.AddDays(-1);
}
else if (subDueAssignment.DayOfWeek == DayOfWeek.Wednesday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(2);
subDueAssignmentBack = subDueAssignment.AddDays(-2);
}
else if (subDueAssignment.DayOfWeek == DayOfWeek.Thursday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(1);
subDueAssignmentBack = subDueAssignment.AddDays(-3);
}
else if (subDueAssignment.DayOfWeek == DayOfWeek.Friday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(0);
subDueAssignmentBack = subDueAssignment.AddDays(-4);
}
else if (subDueAssignment.DayOfWeek == DayOfWeek.Saturday)
{
subDueAssignmentFrwd = subDueAssignment.AddDays(-1);
subDueAssignmentBack = subDueAssignment.AddDays(-5);
}
else
{
subDueAssignmentFrwd = subDueAssignment.AddDays(-2);
subDueAssignmentBack = subDueAssignment.AddDays(-6);
}
}
catch (Exception ex)
{
Console.WriteLine($"{ex.Message}");
}
// Here we will take the Data we need from the Server
try
{
// Start Sql Connection
sqlConnection.Open();
// select multiple Assignments from DataBase and put them in a list so we can retrieve the Sub Date afterwards
List <Assignment> assignmentListDataBase = new List <Assignment>();
SqlCommand cmdSelectAssignments = new SqlCommand("SELECT DISTINCT A_SubDate, A_Title, A_Description, Count(AssignmentPerStudent.StudentID) FROM AssignmentPerStudent INNER JOIN Assignment on AssignmentPerStudent.AssignmentID = Assignment.ID INNER JOIN Student on Student.ID = AssignmentPerStudent.StudentID GROUP BY A_SubDate, A_Title, A_Description ORDER BY Count(AssignmentPerStudent.StudentID) desc", sqlConnection);
// Start SqlReader so we can retrieve and read our data from the server
SqlDataReader dataReader = cmdSelectAssignments.ExecuteReader();
while (dataReader.Read())
{
// Create a mock object with the data we want to retrieve from the server put the instance in a list so we can use it later
Assignment assignmentDB = new Assignment()
{
A_Title = dataReader.GetString(1),
A_Description = dataReader.GetString(2),
// Maybe i m goint to need something like this in the future
A_StudentCount = dataReader.GetInt32(3),
A_SubDateTime = dataReader.GetDateTime(0)
};
//Put the instance in a list so we can use it later
assignmentListDataBase.Add(assignmentDB);
}
// Data Reader must Close
dataReader.Close();
// Sql Connection must Close
sqlConnection.Close();
// We will guarantee that the date for sub in the server is a day from Monday to Friday
foreach (Assignment assignmentDB in assignmentListDataBase)
{
if (assignmentDB.a_SubDateTime.DayOfWeek == DayOfWeek.Saturday)
{
assignmentDB.a_SubDateTime = assignmentDB.a_SubDateTime.AddDays(-1);
}
else if (assignmentDB.a_SubDateTime.DayOfWeek == DayOfWeek.Sunday)
{
assignmentDB.a_SubDateTime = assignmentDB.a_SubDateTime.AddDays(-2);
}
else
{
// We make the check, if the date from server is within range of week the user entered, then the message will triger
if (assignmentDB.a_SubDateTime <= subDueAssignmentFrwd && assignmentDB.a_SubDateTime >= subDueAssignmentBack)
{
Console.WriteLine($"The assignment {assignmentDB.a_Title} {assignmentDB.a_Description} must be submited up to {assignmentDB.a_SubDateTime.ToString("dd/MM/yyyy")}");
}
else
{
Console.WriteLine($"There are no assignments to be subbed between {subDueAssignmentBack.ToString("dd/MM/yyyy")} and {subDueAssignmentFrwd.ToString("dd/MM/yyyy")}");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine($"{ex.Message} something was wrong with you Date insertion to C# from DataBase");;
}
}
