public JsonResult GetByCourse(long CourseId, long BatchId, long OrgId)
{
var dateAndTime = DateTime.Now;
var date = dateAndTime.Date;
var HomeworkDetail = (from tableAssignments in db.tblAssignments
join tableAssignmentQuestion in db.tblAssignmentQuestions on tableAssignments.Id equals tableAssignmentQuestion.AssignmentId
join tableSubjects in db.tblSubjects on tableAssignments.SubjectId equals tableSubjects.Id
where tableAssignments.CourseId == CourseId && tableAssignments.BatchId == BatchId && tableAssignments.OrgId == OrgId && tableAssignments.DateOfAssignment == date
select new { tableAssignments.Id, SubjectId = tableSubjects.Id, tableSubjects.Name, tableAssignments.DateOfAssignment, tableAssignments.AssignmentName, tableAssignmentQuestion.Question }).ToList();
Homeworkobj.assignmentsList = new List <assignments>();
foreach (var temp in HomeworkDetail)
{
var HomeTemp = new assignments();
var Questiontemp = new Questions();
if (Homeworkobj.assignmentsList.Count() != 0)
{
var stu = Homeworkobj.assignmentsList.Where(x => x.SubjectId == temp.SubjectId).SingleOrDefault();
if (stu == null)
{
HomeTemp.AssignmentId = temp.Id;
HomeTemp.SubjectId = temp.SubjectId;
HomeTemp.SubjectName = temp.Name;
HomeTemp.AssignmentName = temp.AssignmentName;
HomeTemp.Date = ConvertToJavascriptDate(new DateTime(temp.DateOfAssignment.Year, temp.DateOfAssignment.Month, temp.DateOfAssignment.Day));
Questiontemp.QuestionsName = temp.Question;
Homeworkobj.assignmentsList.Add(HomeTemp);
HomeTemp.MultipleQuestion.Add(Questiontemp);
}
else
{
var stuobj = Homeworkobj.assignmentsList.Where(x => x.SubjectId == temp.SubjectId).SingleOrDefault();
Questiontemp.QuestionsName = temp.Question;
Homeworkobj.assignmentsList.Remove(Homeworkobj.assignmentsList.Where(x => x.SubjectId == temp.SubjectId).Single());
Homeworkobj.assignmentsList.Add(stuobj);
stuobj.MultipleQuestion.Add(Questiontemp);
}
}
else
{
HomeTemp.AssignmentId = temp.Id;
HomeTemp.SubjectId = temp.SubjectId;
HomeTemp.SubjectName = temp.Name;
HomeTemp.AssignmentName = temp.AssignmentName;
HomeTemp.Date = ConvertToJavascriptDate(new DateTime(temp.DateOfAssignment.Year, temp.DateOfAssignment.Month, temp.DateOfAssignment.Day));
Questiontemp.QuestionsName = temp.Question;
Homeworkobj.assignmentsList.Add(HomeTemp);
HomeTemp.MultipleQuestion.Add(Questiontemp);
}
}
return(Json(Homeworkobj, JsonRequestBehavior.AllowGet));
}
public void LoadAll()
{
my_assignments.Clear();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
using (SqlConnection _con = new SqlConnection(connectionString))
{
string queryStatement = @"SELECT distinct store_name, u1.order_id, order_placed_date, u1.order_placed_time, u1.delivery_time, sum_items, total_price FROM(
(SELECT store_name, t1.order_id, order_placed_date, order_placed_time, t1.delivery_time, is_delivered, sum_items FROM(
(SELECT o.order_id, order_placed_date, order_placed_time, store_name, listed_price, s.quantity, o.delivery_time, is_delivered FROM[ORDERR] o
INNER JOIN ORDERED_BY c ON c.order_id = o.order_id
INNER JOIN SELECTITEM s ON s.order_id = o.order_id
INNER JOIN ORDERFROM os ON os.order_id = o.order_id
INNER JOIN GROCERYSTORE g ON g.store_id = os.store_address_id
INNER JOIN item i ON s.item_id = i.item_id
INNER JOIN deliveredBy d ON d.order_id = o.order_id
WHERE deliverer_username = @username and is_delivered = 0) t1
INNER JOIN
(SELECT o2.order_id, SUM(s2.quantity) sum_items FROM[ORDERR] o2
INNER JOIN ORDERED_BY c2 ON c2.order_id = o2.order_id
INNER JOIN SELECTITEM s2 ON s2.order_id = o2.order_id
INNER JOIN ORDERFROM os2 ON os2.order_id = o2.order_id
INNER JOIN GROCERYSTORE g2 ON g2.store_id = os2.store_address_id
INNER JOIN item i2 ON s2.item_id = i2.item_id
INNER JOIN deliveredBy d ON d.order_id = o2.order_id
WHERE deliverer_username = @username and is_delivered = 0
GROUP BY o2.order_id) t2 on t1.order_id = t2.order_id)) u1
INNER JOIN
(SELECT o.order_id, SUM(listed_price * s.quantity) as total_price FROM[ORDERR] o
INNER JOIN ORDERED_BY c ON c.order_id = o.order_id
INNER JOIN SELECTITEM s ON s.order_id = o.order_id
INNER JOIN ORDERFROM os ON os.order_id = o.order_id
INNER JOIN GROCERYSTORE g ON g.store_id = os.store_address_id
INNER JOIN item i ON s.item_id = i.item_id
INNER JOIN deliveredBy d ON d.order_id = o.order_id
WHERE deliverer_username = @username and is_delivered = 0
GROUP BY o.order_id) u2 on u1.order_id = u2.order_id) ORDER BY order_id";
using (SqlCommand _cmd = new SqlCommand(queryStatement, _con))
{
_cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Globals.Persistent_Current.username;
DataTable tb = new DataTable();
SqlDataAdapter _dap = new SqlDataAdapter(_cmd);
_con.Open();
_dap.Fill(tb);
_con.Close();
foreach (DataRow dr in tb.Rows)
{
assignments o = new assignments();
o.date = dr["order_placed_date"].ToString();
o.delivery_time = dr["delivery_time"].ToString();
o.order_time = dr["order_placed_time"].ToString();
o.num_items = Int32.Parse(dr["sum_items"].ToString());
o.store_name = dr["store_name"].ToString();
o.total_price = dr["total_price"].ToString();
o.orderId = Int32.Parse(dr["order_id"].ToString());
my_assignments.Add(o);
}
}
}
}