public JsonResult getPersonByClass()
{
int id_class = int.Parse(Request.QueryString["id_class"]);
tndaEntities db = new tndaEntities();
List <object> json = new List <object>();
List <Person> people = db.People.Where(p => p.ID_role == 4 && p.ID_Class == id_class).OrderBy(p => p.Name).ToList();
foreach (Person p in people)
{
var ob = new
{
id = p.ID,
ch_name = p.ChristianName,
fname = p.FirstName,
name = p.Name,
pclass = p.Class.Grade.GradeName + " " + p.Class.ClassName,
birth = p.Birth != null?p.Birth.Value.ToString("yyyy-MM-dd") : "",
role = p.Role.RoleName
};
//
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
private bool auth(string username, string password)
{
ACC account = null;
using (tndaEntities db = new tndaEntities())
{
account = db.ACCs.Where(acc => acc.UserName.ToUpper().Equals(username.ToUpper())).FirstOrDefault();
if (account != null)
{
if (account.Pwd.Trim().Equals(Tools.encodeBase64(password)))
{
Person p = account.Person;
Session.Add("personId", p.ID);
Session.Add("person", p);
Session.Add("acc", account);
if (p.ID_Class != null)
{
Session.Add("classId", p.Class.ID);
}
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
}
public JsonResult getGLV()
{
tndaEntities db = new tndaEntities();
List <object> json = new List <object>();
List <Person> people = db.People.Where(p => p.ID_role == 1).ToList();
foreach (Person p in people)
{
string birthString = p.Birth != null?p.Birth.Value.ToString("yyyy-MM-dd") : "";
string classString = p.Class != null ? (p.Class.Grade.GradeName + " " + p.Class.ClassName) : "";
var ob = new
{
id = p.ID,
ch_name = p.ChristianName,
fname = p.FirstName,
name = p.Name,
pclass = classString,
birth = birthString
};
//
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult getAllClasses()
{
tndaEntities db = new tndaEntities();
List <object> list = new List <object>();
foreach (Class cl in db.Classes.ToList())
{
string name = cl.Grade.GradeName + " " + cl.ClassName;
string color = "";
switch (cl.ID_Grade)
{
case 1:
color = "#EEB0AC";
break;
case 2:
color = "#9BF14F";
break;
case 3:
color = "#7BA6EF";
break;
case 4:
color = "#ECC100";
break;
}
//int total = cl.People.Where(p => p.ID_role == 4).Count();
var ob = new { name = name.Trim(), id = cl.ID, color = color, glv = cl.teacher_names, total = cl.students_count /*tn_total = total*/ };
list.Add(ob);
}
return(Json(list, JsonRequestBehavior.AllowGet));
}
public JsonResult getPersonByQuery()
{
string query = Request.QueryString["query"];
query = Tools.convert(query).ToUpper().Replace(" ", String.Empty);
//
using (tndaEntities db = new tndaEntities())
{
const string sql =
"SELECT * FROM Person p WHERE p.for_search LIKE CONCAT('%',@query,'%') AND p.ID_role = 4 ";
List <Person> list = db.People.SqlQuery(sql, new SqlParameter("@query", query)).ToList();
List <object> objects = new List <object>();
foreach (Person p in list)
{
string birthString = p.Birth != null?p.Birth.Value.ToString("yyyy-MM-dd") : "";
string classString = p.Class != null ? (p.Class.Grade.GradeName + " " + p.Class.ClassName) : "";
var ob = new
{
id = p.ID,
ch_name = p.ChristianName,
fname = p.FirstName,
name = p.Name,
pclass = classString,
birth = birthString,
role = p.Role.RoleName
};
//
objects.Add(ob);
}
return(Json(objects, JsonRequestBehavior.AllowGet));
}
}
//
public JsonResult getNewPerson()
{
tndaEntities db = new tndaEntities();
List <Person> people = db.People.Where(p => p.ID_role == 7).ToList();
List <object> json = new List <object>();
foreach (Person child in people)
{
string img = child.Image;
if (string.IsNullOrEmpty(img))
{
img = "https://cdn.pixabay.com/photo/2016/08/08/09/17/avatar-1577909_1280.png";
}
string birthString = child.Birth != null?child.Birth.Value.ToString("yyyy-MM-dd") : "";
var ob = new
{
id = child.ID,
ch_name = child.ChristianName,
fname = child.FirstName,
name = child.Name,
birth = birthString
};
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult getAllGrades()
{
tndaEntities db = new tndaEntities();
List <object> grades = new List <object>();
foreach (Grade g in db.Grades.ToList())
{
var ob = new { ID = g.ID, gradeName = g.GradeName.Trim() };
grades.Add(ob);
}
return(Json(grades, JsonRequestBehavior.AllowGet));
}
public JsonResult getClassById()
{
int id = int.Parse(Request.QueryString["id_class"]);
tndaEntities db = new tndaEntities();
Class c = db.Classes.Find(id);
Grade g = db.Grades.Find(c.ID_Grade);
Person glv = db.People.Where(p => p.ID_role == 1 && p.ID_Class == c.ID).FirstOrDefault();
string glv_name = glv != null ? (glv.ChristianName + " " + glv.FirstName + " " + glv.Name) : "";
//
var json = new { className = g.GradeName + " " + c.ClassName, glv_name = glv_name };
return(Json(json, JsonRequestBehavior.AllowGet));
}
public void EditImage()
{
tndaEntities db = new tndaEntities();
List <Person> list = db.People.ToList();
string id = Request.QueryString["query"];
string img = Request.QueryString["query"];
foreach (Person p in list)
{
if (p.ID == int.Parse(id))
{
p.Image = img;
db.SaveChanges();
}
}
}
public JsonResult countByClass()
{
int idGr = int.Parse(Request.QueryString["id_grade"]);
tndaEntities db = new tndaEntities();
List <object> json = new List <object>();
List <Person> people = db.People.Where(p => p.ID_role == 4 && p.Class.ID_Grade == idGr).ToList();
List <Class> classes = db.Classes.Where(c => c.ID_Grade == idGr).ToList();
foreach (Class c in classes)
{
int count = people.Where(p => p.ID_Class == c.ID).Count();
var ob = new { id = c.ID, name = c.ClassName, total = count };
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult getClassByGrade()
{
int grade_id = int.Parse(Request.QueryString["id"]);
List <object> classes = new List <object>();
using (tndaEntities db = new tndaEntities())
{
Grade grade = db.Grades.Find(grade_id);
foreach (Class c in db.Classes.Where(c => c.ID_Grade == grade_id).ToList())
{
var ob = new { ID = c.ID, className = c.ClassName.Trim(), gradeName = grade.GradeName };
classes.Add(ob);
}
}
//
return(Json(classes, JsonRequestBehavior.AllowGet));
}
public JsonResult countTN()
{
List <object> json = new List <object>();
using (tndaEntities db = new tndaEntities())
{
string sql = "SELECT * FROM Person p JOIN Class c ON p.ID_Class = c.ID JOIN Grade g ON c.ID_Grade = g.ID WHERE g.ID = @gId AND p.ID_role = 4";
int slKT, slRL, slTS, slSD;
slKT = db.People.SqlQuery(sql, new SqlParameter("@gId", 1)).Count();
slRL = db.People.SqlQuery(sql, new SqlParameter("@gId", 2)).Count();
slTS = db.People.SqlQuery(sql, new SqlParameter("@gId", 3)).Count();
slSD = db.People.SqlQuery(sql, new SqlParameter("@gId", 4)).Count();
int count = slKT + slRL + slTS + slSD;
int ot = 0;
var ob = new { total = count, KT = slKT, RL = slRL, TS = slTS, SD = slSD, other = ot };
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
//[ValidateAntiForgeryToken]
public ActionResult EditClass(FormCollection form)
{
tndaEntities db = new tndaEntities();
int id = int.Parse(form["child-id"]);
Person p = db.People.Find(id);
if (p != null)
{
Class newClass = db.Classes.Find(int.Parse(form["child-class"]));
if (p.ID_role == 1 || p.ID_role == 2)
{
if (newClass != null)
{
string tName = p.ChristianName + " " + p.FirstName + " " + p.Name;
newClass.teacher_names = tName;
if (p.ID_Class.HasValue)
{
Class oldClass = db.Classes.Find(p.ID_Class.Value);
oldClass.teacher_names = "";
}
p.ID_Class = int.Parse(form["child-class"]);
}
}
else if (p.ID_role == 4 || p.ID_role == 7)
{
if (newClass != null)
{
newClass.students_count += 1;
if (p.ID_Class.HasValue)
{
Class oldClass = db.Classes.Find(p.ID_Class.Value);
oldClass.students_count -= 1;
}
p.ID_Class = int.Parse(form["child-class"]);
}
}
db.SaveChanges();
}
return(Redirect(form["current_location"].ToString()));
}
public JsonResult allTN()
{
tndaEntities db = new tndaEntities();
List <Class> classes = db.Classes.ToList();
List <object> json = new List <object>();
foreach (Class c in classes)
{
List <Person> children = db.People.Where(p => p.ID_role == 4 && p.ID_Class == c.ID).ToList();
if (children.Count == 0)
{
continue;
}
List <object> ob = new List <object>();
foreach (Person p in children)
{
Family family = db.Families.Find(p.ID_Farmily);
//
Person father = db.People.Find(family.ID_Dad);
//
Person mother = db.People.Where(mo => mo.ID_Farmily == family.ID && mo.ID != father.ID && mo.ID != p.ID).FirstOrDefault();
//
Person gv = db.People.Where(glv => glv.ID_Class == p.ID_Class && (glv.ID_role == 1 || glv.ID_role == 2) && glv.ID_Class != null).FirstOrDefault();
string glv_name = gv != null ? (gv.ChristianName + " " + gv.FirstName + " " + gv.Name) : "";
string birthString = p.Birth != null?p.Birth.Value.ToString("dd.MM.yyy") : "";
string classString = p.Class != null ? (p.Class.Grade.GradeName + " " + p.Class.ClassName) : "";
//
var fatherJson = new { fa_id = father.ID, ch_name = father.ChristianName, fname = father.FirstName, name = father.Name, role = father.Role.RoleName, phone = father.Phone };
var motherJson = new { mo_id = mother.ID, ch_name = mother.ChristianName, fname = mother.FirstName, name = mother.Name, role = mother.Role.RoleName, phone = mother.Phone, role_id = p.ID_role };
int glvId = gv != null ? gv.ID : 91;
var child = new { id = p.ID, ch_name = p.ChristianName, fname = p.FirstName, name = p.Name, pclass = classString, role = p.Role.RoleName, glv = glv_name, id_class = p.ID_Class, birth = birthString, address = p.Address, father = fatherJson, mother = motherJson, role_id = p.ID_role, glv_id = glvId, note = p.Note };
//
ob.Add(child);
}
json.Add(ob);
}
return(Json(json, JsonRequestBehavior.AllowGet));
}
public JsonResult setImg(FormCollection form)
{
try
{
int id = int.Parse(form["id"]);
HttpPostedFileBase file = Request.Files[0];
//
string _FileName = Guid.NewGuid().ToString() + Path.GetExtension(file.FileName);
string _path = Path.Combine(Server.MapPath("~/img/upload"), _FileName);
string result = Tools.uploadAndResizeImg(file, _path, _FileName);
//
tndaEntities db = new tndaEntities();
db.People.Find(id).Image = result;
db.SaveChanges();
return(Json(new { success = true }, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
return(Json(new { success = false, message = e.Message }, JsonRequestBehavior.AllowGet));
}
//
}
public bool ReportPerson(FormCollection form)
{
int idOldPerson = int.Parse(Request.QueryString["query"]);
tndaEntities db = new tndaEntities();
Person p = new Person
{
ChristianName = form["child-ch-name"],
FirstName = form["fa-fname"],
Name = form["fa-name"],
Birth = Convert.ToDateTime(form["child-birth"]), //
Address = form["child-address"],
ID_Class = int.Parse(form["child-class"]),
//ID_Farmily = form["child-address"],
ID_role = 1,
//Image = "",
Note = "",
Phone = "",
Status = true,
Gender = bool.Parse(form["child-gender"]),
CreateDate = DateTime.Now
};
db.People.Add(p);
//return Json(form["child-class"], JsonRequestBehavior.AllowGet);
//db.People.Add(p);
//db.SaveChanges();
Report rp = new Report
{
ID_Person = idOldPerson,
ID_NewPerson = p.ID,
Date = DateTime.Now,
Status = 0
};
db.Reports.Add(rp);
db.SaveChanges();
return(true);
}
public List <Person> checkDuplicate()
{
tndaEntities db = new tndaEntities();
List <Person> ps = new List <Person>();
List <Person> people = db.People.Where(p => p.ID_role == 4).ToList();
for (int i = 0; i < people.Count(); i++)
{
for (int j = i + 1; j < people.Count(); j++)
{
if (people[i].ChristianName == people[j].ChristianName)
{
if (people.FirstOrDefault(p => p.ID == people[i].ID) != null)
{
ps.Add(people[i]);
}
ps.Add(people[j]);
}
}
}
return(ps);
}
//[ValidateAntiForgeryToken]
public ActionResult DelPerson(FormCollection form)
{
tndaEntities db = new tndaEntities();
Person p = db.People.Find(int.Parse(form["child-id"]));
if (p != null)
{
if (p.ID_role == 1 || p.ID_role == 2)
{
if (p.ID_Class != null)
{
Class c = db.Classes.Find(p.ID_Class);
c.teacher_names = null;
}
}
if (p.ID_role == 4 || p.ID_role == 7)
{
if (p.ID_Class != null)
{
Class c = db.Classes.Find(p.ID_Class);
c.students_count -= 1;
}
if (p.ID_Farmily != null)
{
Family fam = db.Families.Find(p.ID_Farmily);
Person father = db.People.Find(fam.ID_Dad);
Person mother = db.People.FirstOrDefault(per => per.ID_Farmily == fam.ID && per.ID != father.ID && per.ID != p.ID);
db.Families.Remove(fam);
db.People.Remove(father);
db.People.Remove(mother);
}
}
db.People.Remove(p);
db.SaveChanges();
}
return(Redirect(form["current_location"].ToString()));
}
public string upClass()
{
string result = "";
int current_class_id = int.Parse(Request.QueryString["id"]);
string current_class_name, current_grade_name, new_class_name, new_grade_name;
int current_grade_id, new_class_id, new_grade_id;
//
//entity
tndaEntities db = new tndaEntities();
//
Class cur_class = db.Classes.Find(current_class_id);
Grade cur_grade = db.Grades.Find(cur_class.ID_Grade);
//
current_grade_id = cur_grade.ID;
//
current_class_name = cur_class.ClassName;
current_grade_name = cur_grade.GradeName;
try
{
char[] charArray = current_class_name.ToCharArray();
int temp = int.Parse(charArray[0].ToString());
if (temp != 3)
{
new_class_name = temp + 1 + charArray[1].ToString();
new_grade_name = current_grade_name;
//
new_grade_id = current_grade_id;
new_class_id = db.Classes.Where(c => c.ID_Grade == new_grade_id && c.ClassName == new_class_name).FirstOrDefault().ID;
}
else
{
if (current_grade_id == 4)
{
return("Không thể lên lớp, đã kết thúc chương trình học");
}
//
new_grade_id = current_grade_id + 1;
new_grade_name = db.Grades.Find(new_grade_id).GradeName;
//
new_class_name = 1 + charArray[1].ToString();
new_class_id = db.Classes.Where(c => c.ID_Grade == new_grade_id && c.ClassName == new_class_name).FirstOrDefault().ID;
}
}
catch (Exception e)
{
return(e.Message);
}
result += "Khối hiện tại: " + current_grade_name;
result += "| ";
result += "ID Khối hiện tại: " + current_grade_id;
result += "| ";
result += "Lớp hiện tại: " + current_class_name;
result += "| ";
result += "ID lớp hiện tại: " + current_class_id;
result += "| ";
result += "| ";
result += "Khối mới: " + new_grade_name;
result += "| ";
result += "ID khối mới: " + new_grade_id;
result += "| ";
result += "Lớp mới " + new_class_name;
result += "| ";
result += "ID lớp mới " + new_class_id;
return(result);
}
public JsonResult getPersonDetailWithArg(int id)
{
using (tndaEntities db = new tndaEntities())
{
Person child = db.People.Find(id);
//
if (child.ID_role == 4 || child.ID_role == 7)
{
Family family = db.Families.Find(child.ID_Farmily);
//
Person father = db.People.Find(family.ID_Dad);
//
Person mother = db.People.FirstOrDefault(p =>
p.ID_Farmily == family.ID && p.ID != father.ID && p.ID != child.ID);
//
Person glv = db.People.FirstOrDefault(p =>
p.ID_Class == child.ID_Class && (p.ID_role == 1 || p.ID_role == 2) && p.ID_Class != null);
string glv_name = glv != null ? (glv.ChristianName + " " + glv.FirstName + " " + glv.Name) : "";
string img = child.Image;
if (string.IsNullOrEmpty(img))
{
img = "https://cdn.pixabay.com/photo/2016/08/08/09/17/avatar-1577909_1280.png";
}
string birthString = child.Birth != null?child.Birth.Value.ToString("yyyy-MM-dd") : "";
string classString = child.Class != null
? (child.Class.Grade.GradeName + " " + child.Class.ClassName)
: "";
//
var fatherJson = new
{
fa_id = father.ID,
ch_name = father.ChristianName,
fname = father.FirstName,
name = father.Name,
role = father.Role.RoleName,
phone = father.Phone
};
var motherJson = new
{
mo_id = mother.ID,
ch_name = mother.ChristianName,
fname = mother.FirstName,
name = mother.Name,
role = mother.Role.RoleName,
phone = mother.Phone,
role_id = child.ID_role
};
int glvId = glv?.ID ?? 91;
var json = new
{
id = child.ID,
ch_name = child.ChristianName,
fname = child.FirstName,
name = child.Name,
pclass = classString,
role = child.Role.RoleName,
glv = glv_name,
id_class = child.ID_Class,
birth = birthString,
address = child.Address,
father = fatherJson,
mother = motherJson,
role_id = child.ID_role,
glv_id = glvId,
img = img,
note = child.Note
};
//
return(Json(json, JsonRequestBehavior.AllowGet));
}
else
{
string image = child.Image;
if (string.IsNullOrEmpty(image))
{
image = "https://cdn.pixabay.com/photo/2016/08/08/09/17/avatar-1577909_1280.png";
}
string birthString = child.Birth != null?child.Birth.Value.ToString("yyyy-MM-dd") : "";
string classString = child.Class != null
? (child.Class.Grade.GradeName + " " + child.Class.ClassName)
: "";
string gender = child.Gender.Value ? "Nam" : "Nữ";
var json = new
{
id = child.ID,
ch_name = child.ChristianName,
fname = child.FirstName,
name = child.Name,
pclass = classString,
id_class = child.ID_Class,
id_grade = child.Class.ID_Grade,
role = child.Role.RoleName,
birth = birthString,
address = child.Address,
phone = child.Phone,
role_id = child.ID_role,
img = image,
gender = gender,
genderValue = child.Gender.Value
};
return(Json(json, JsonRequestBehavior.AllowGet));
}
}
}
