285 lines
15 KiB
JavaScript
285 lines
15 KiB
JavaScript
const db = require("../models");
|
|
const API = require('../helper/API_Response');
|
|
const db_helper = require("../helper/db_helper");
|
|
const helper = require("../helper/helper");
|
|
const { ClientCohort, Cohort, User } = require("../models");
|
|
|
|
exports.addclientcohort = async (req, res) => {
|
|
var data = await helper.decryptRequest(req.body.data);
|
|
let usr = {
|
|
"user_id": data.user_id
|
|
}
|
|
var err = [];
|
|
var c = [];
|
|
const cc = await db_helper.selectByWhere("ClientCohort", usr);
|
|
if (cc.length > 0) {
|
|
const coh = await db_helper.selectByPk("ClientCohort", cc[0].id);
|
|
if (data.user_id) coh.user_id = data.user_id;
|
|
if (data.cohortIds) coh.cohortIds = data.cohortIds;
|
|
c = coh.save();
|
|
} else {
|
|
c = await ClientCohort.create(data).catch((ex) => err = ex.errors[0]);
|
|
}
|
|
if (err.length > 0) {
|
|
res.status(400).send(API._400({ message: err.message }));
|
|
}
|
|
res.status(200).send(API._200(await c));
|
|
};
|
|
exports.admin_add_strudent_cohort = async (req, res) => {
|
|
var input = await helper.decryptRequest(req.body.data);
|
|
var result = [];
|
|
const allcohort = await Cohort.findAll({
|
|
where: {
|
|
status: 0
|
|
}
|
|
});
|
|
for (let c = 0; c < allcohort.length; c++) {
|
|
let u = "";
|
|
u = allcohort[c].userslist;
|
|
let ids = "";
|
|
if (u[0] > 0) {
|
|
for (let ui = 0; ui < u.length; ui++) {
|
|
if (u[ui] != input.user_id) {
|
|
ids += u[ui] + ","
|
|
}
|
|
}
|
|
}
|
|
if (ids.endsWith(",")) {
|
|
ids = ids.substring(0, ids.length - 1);
|
|
}
|
|
if (ids.startsWith(",")) {
|
|
ids = ids.substring(1, ids.length);
|
|
}
|
|
if (ids != "") {
|
|
const coh = await Cohort.findByPk(allcohort[c].id);
|
|
coh.userslist = ids;
|
|
await coh.save();
|
|
}
|
|
}
|
|
if (input.cohortIds) {
|
|
var id = input.cohortIds;
|
|
if (id.includes(",")) {
|
|
var cohortIds = id.split(",");
|
|
for (let i = 0; i < cohortIds.length; i++) {
|
|
const cohort = await Cohort.findByPk(cohortIds[i]);
|
|
let uid = cohort.userslist;
|
|
if (uid[0] > 0) {
|
|
if (!uid.includes(input.user_id))
|
|
cohort.userslist = uid + "," + input.user_id;
|
|
} else {
|
|
cohort.userslist = input.user_id;
|
|
}
|
|
let success = await cohort.save();
|
|
result.push(success);
|
|
}
|
|
} else {
|
|
const cohort = await Cohort.findByPk(id);
|
|
let uid = cohort.userslist;
|
|
if (uid[0] > 0) {
|
|
if (!uid.includes(input.user_id))
|
|
cohort.userslist = uid + "," + input.user_id;
|
|
} else {
|
|
cohort.userslist = input.user_id;
|
|
}
|
|
let success = await cohort.save();
|
|
result.push(success);
|
|
}
|
|
}
|
|
|
|
res.status(200).send(API._200(await result));
|
|
};
|
|
exports.allclient = async (req, res) => {
|
|
const clients = await User.findAll({
|
|
where: {
|
|
status: 0,
|
|
roll_id: 4
|
|
}
|
|
});
|
|
let all = [];
|
|
for (let i = 0; i < clients.length; i++) {
|
|
const cl = await ClientCohort.findAll({
|
|
where: {
|
|
status: 0,
|
|
user_id: clients[i].id
|
|
}
|
|
});
|
|
let cohort = [];
|
|
let ccid = 0;
|
|
if (cl.length > 0) {
|
|
cohort = await db_helper.query("select * from cohorts where id in(" + cl[0].cohortIds + ")");
|
|
ccid = cl[0].id;
|
|
}
|
|
if (cohort.length > 0) {
|
|
cohort = cohort[0];
|
|
}
|
|
all.push({
|
|
clientcohortId: ccid,
|
|
id: clients[i].id,
|
|
name: clients[i].name,
|
|
email: clients[i].email,
|
|
roll_id: clients[i].roll_id,
|
|
email_verified_at: clients[i].email_verified_at,
|
|
email_verified: clients[i].email_verified,
|
|
calling_code: clients[i].calling_code,
|
|
mobile: clients[i].mobile,
|
|
profile_img: clients[i].profile_img,
|
|
permissions: clients[i].permissions,
|
|
password: clients[i].password,
|
|
remember_token: clients[i].remember_token,
|
|
free_flashcard: clients[i].free_flashcard,
|
|
free_practque: clients[i].free_practque,
|
|
free_flashccsp: clients[i].free_flashccsp,
|
|
pass_rest_code: clients[i].pass_rest_code,
|
|
avatar_url: clients[i].avatar_url,
|
|
is_first: clients[i].is_first,
|
|
aliasname: clients[i].aliasname,
|
|
is_temp: clients[i].is_temp,
|
|
notes: clients[i].notes,
|
|
address: clients[i].address,
|
|
status: clients[i].status,
|
|
cohorts: cohort
|
|
})
|
|
}
|
|
res.status(200).send(API._200(await all));
|
|
};
|
|
exports.cohortdetailview = async (req, res) => {
|
|
var input = await helper.decryptRequest(req.body.data);
|
|
const all = await Cohort.findAll({
|
|
where: {
|
|
id: input.id,
|
|
status: 0
|
|
}
|
|
});
|
|
let std = [];
|
|
let co = [];
|
|
for (let i = 0; i < all.length; i++) {
|
|
let element = all[i].userslist;
|
|
let ids = element.splice(",");
|
|
if (ids.length > 0) {
|
|
for (let j = 0; j < ids.length; j++) {
|
|
let ttlkp = 0;
|
|
let ttlcp = 0;
|
|
let ttlpp = 0;
|
|
let kp = 0;
|
|
let cp = 0;
|
|
let pp = 0;
|
|
|
|
let overdueClassWork = 0;
|
|
let knowOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='KA' and overdue_date < now() and type_id not in (select ka_id from knowcompletes where cohort_id=" + input.id + " and user_id=" + ids[j] + ")");
|
|
if (knowOverdue[0].length <= 0) {
|
|
let practiceOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='PRACTICE' and overdue_date < now() and type_id not in (select pt_id from ptestcomplates where cohort_id=" + input.id + " and user_id=" + ids[j] + ")");
|
|
if (practiceOverdue[0].length <= 0) {
|
|
let catOverdue = await db_helper.query("select * from cohort_schedules where cohort_id=" + input.id + " and type='CAT' and overdue_date < now() and type_id not in (select ct_id from ctestcomplates where cohort_id=" + input.id + " and user_id=" + ids[j] + ")");
|
|
if (catOverdue[0].length <= 0) {
|
|
overdueClassWork = 0;
|
|
} else {
|
|
overdueClassWork = 1;
|
|
}
|
|
} else {
|
|
overdueClassWork = 1;
|
|
}
|
|
} else {
|
|
overdueClassWork = 1;
|
|
}
|
|
let usr = await db_helper.query("select id,name,email,avatar_url,aliasname from users where id=" + ids[j]);
|
|
let ttlkque = await db_helper.query("select count(*) as ttlques from know_ass_ques where ass_id in(SELECT ka.id from knowledge_asses as ka inner join cohorts as c on find_in_set(ka.id,c.knowassessments) where find_in_set(" + ids[j] + ",c.userslist) and ka.status=0 and c.id=" + input.id + " group by ka.id) and status=0");
|
|
ttlkp = parseInt(ttlkque[0][0].ttlques);
|
|
const know = await db_helper.query("SELECT count(*) AS kpoint FROM know_ass_ques_ans LEFT JOIN knowledge_asses ka ON ass_id = ka.id INNER JOIN cohorts c ON Find_in_set(ka.id, c.knowassessments) AND Find_in_set(user_id, c.userslist) AND cohort_id = c.id WHERE user_id = " + ids[j] + " and cohort_id=" + input.id + " and active=1");
|
|
kp = parseInt(know[0][0].kpoint);
|
|
|
|
let ttlcatrque = await db_helper.query("SELECT count(*) as ttlcatrque from ketexamtests kt left join ketexamques kq on find_in_set(kt.id,kq.exam_id) inner join cohorts as c on find_in_set(kt.id,c.ketexamtests) where find_in_set(" + ids[j] + ",c.userslist) and find_in_set(kt.id,kq.exam_id) and kt.status=0 and kq.status=0 and c.id=" + input.id);
|
|
ttlcp = parseInt(ttlcatrque[0][0].ttlcatrque);
|
|
const cohortscoreCat = await db_helper.query("SELECT count(kn.id) AS cpoint FROM ketexam_ans kn inner join ketexamques kq on find_in_set(kn.test_id,kq.exam_id) and kq.id=kn.KEQ_id WHERE user_id = " + ids[j] + " and cohort_id=" + input.id);
|
|
cp = parseInt(cohortscoreCat[0][0].cpoint);
|
|
|
|
let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where test_id in(SELECT pt.id from practicetests as pt inner join cohorts as c on find_in_set(pt.id,c.practicetests) where find_in_set(" + ids[j] + ",c.userslist) and pt.status=0 and c.id=" + input.id + " group by pt.id) and status=0");
|
|
ttlpp = parseInt(ttlprque[0][0].ttlprques);
|
|
const cohortscorePra = await db_helper.query("SELECT count(id) AS ppoint FROM practice_ans WHERE user_id = " + ids[j] + " and cohort_id=" + input.id + " and active=1");
|
|
pp = parseInt(cohortscorePra[0][0].ppoint);
|
|
|
|
let avgpercetage = 0;
|
|
|
|
if (usr[0].length > 0)
|
|
std.push({ "id": ids[j], "name": usr[0][0].name, "email": usr[0][0].email, "avatar_url": usr[0][0].avatar_url, "aliasname": usr[0][0].aliasname, "kpoint": kp, "ttlkpoint": ttlkp, "cpoint": cp, "ttlcpoint": ttlcp, "ppoint": pp, "ttlppoint": ttlpp, "overdueClassWork": overdueClassWork, "avgpercetage": avgpercetage });
|
|
}
|
|
}
|
|
let loc = "-"
|
|
if (all[i].location != null)
|
|
loc = all[i].location;
|
|
co.push({ "cohort_name": all[i].cohort_name, "location": loc, "id": all[i].id, "start_date": all[i].createdAt, "No_Of_Student": ids.length, "Student": std })
|
|
std = [];
|
|
}
|
|
res.status(200).send(API._200(await co));
|
|
};
|
|
exports.cohortreport = async (req, res) => {
|
|
var input = await helper.decryptRequest(req.body.data);
|
|
const coh = await db.sequelize.query("select userslist,certi_id,id from cohorts where id= :cohort_id", { replacements: { cohort_id: input.cohort_id } })
|
|
.then(function (data) {
|
|
return Array.from(new Set(data));
|
|
})
|
|
if (coh[0].length > 0) {
|
|
if (coh[0][0].userslist != '') {
|
|
|
|
const students = await db_helper.query("select id,name,email,avatar_url from users where id in(" + coh[0][0].userslist + ")");
|
|
let data = [];
|
|
for (let i = 0; i < students[0].length; i++) {
|
|
const know = await db_helper.query("(select sum(case when kaqa.correct=1 then 1 else 0 end) ttlpointget,ka.id,ka.knowass_name,(select count(*) from know_ass_ques where ass_id=ka.id) as ttlpoint from know_ass_ques_ans as kaqa left join knowledge_asses as ka on ka.id=kaqa.ass_id where kaqa.active=1 and kaqa.cohort_id=" + input.cohort_id + " and kaqa.user_id=" + students[0][i].id + " group by ka.id)");
|
|
const flashcarddata = await db_helper.query("select * from flashans_offlines where UserId=" + students[0][i].id + " and cohort_id=" + input.cohort_id + " limit 1");
|
|
var flash;
|
|
if (flashcarddata[0].length > 0) {
|
|
flash = await db_helper.query("select count(distinct (case when user_res = 1 then cid end)) as ttlright,count(distinct (case when user_res = 2 then cid end)) as ttlreview,count(distinct (case when mark = 1 then cid end)) as ttlbookmark, count(distinct (case when(notes = 1 && user_res=0) then cid end)) as ttlnotes, ifnull(ttlcards,0)ttlcards from flashans_offlines fo right join (SELECT COUNT(*) as ttlcards FROM flashcards WHERE certi_id in(select certi_id from cohorts where id=" + input.cohort_id + ") and status=0)fl on 1=1 where UserId=" + students[0][i].id + " and cohort_id=" + input.cohort_id);
|
|
}
|
|
else {
|
|
// flash = await db_helper.query("select count(distinct (case when user_res = 1 then cid end)) as ttlright,count(distinct (case when user_res = 2 then cid end)) as ttlreview,count(distinct (case when mark = 1 then cid end)) as ttlbookmark, count(distinct (case when(notes = 1 && user_res=0) then cid end)) as ttlnotes, ifnull(ttlcards,0)ttlcards from flashans_offlines fo right join (SELECT COUNT(*) as ttlcards FROM flashcards WHERE certi_id in(select certi_id from cohorts where id=" + input.cohort_id + ") and status=0)fl on 1=1 where UserId=" + students[0][i].id);
|
|
flash = await db_helper.query("SELECT COUNT(DISTINCT CASE WHEN user_res = 1 THEN cid END) AS ttlright, COUNT(DISTINCT CASE WHEN user_res = 2 THEN cid END) AS ttlreview, COUNT(DISTINCT CASE WHEN mark = 1 THEN cid END) AS ttlbookmark, COUNT(DISTINCT CASE WHEN (notes = 1 AND user_res = 0) THEN cid END) AS ttlnotes, IFNULL((SELECT COUNT(*) FROM flashcards WHERE certi_id IN (SELECT certi_id FROM cohorts WHERE id = " + input.cohort_id + ") AND status = 0), 0) AS ttlcards FROM flashans_offlines fo WHERE UserId =" + students[0][i].id);
|
|
}
|
|
let ttlprque = await db_helper.query("select count(*) as ttlprques from practiceques where test_id in(SELECT pt.id from practicetests as pt inner join cohorts as c on find_in_set(pt.id,c.practicetests) where c.id=" + coh[0][0].id + " and pt.status=0 group by pt.id) and certi_id = " + coh[0][0].certi_id);
|
|
let ttlprques = parseInt(ttlprque[0][0].ttlprques);
|
|
const cohortscorePra = await db_helper.query(`
|
|
SELECT
|
|
CASE WHEN (Sum( CASE WHEN correct = 1 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN correct = 1 THEN 1 ELSE 0 END) END AS ppoint,
|
|
CASE WHEN (Sum( CASE WHEN correct = 0 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN correct = 0 THEN 1 ELSE 0 END) END AS wppoint,
|
|
CASE WHEN (Sum( CASE WHEN flag_review = 1 THEN 1 ELSE 0 END)) IS NULL THEN 0 ELSE Sum(CASE WHEN flag_review = 1 THEN 1 ELSE 0 END) END AS marked
|
|
FROM practice_ans
|
|
WHERE active=1 and cohort_id=${input.cohort_id} and user_id =${students[0][i].id}`);
|
|
let right = parseInt(cohortscorePra[0][0].ppoint);
|
|
let wrong = parseInt(cohortscorePra[0][0].wppoint);
|
|
let marked = parseInt(cohortscorePra[0][0].marked);
|
|
let prac = { "ttlprques": ttlprques ? ttlprques : 0, "right": right ? right : 0, "wrong": wrong ? wrong : 0, "marked": marked ? marked : 0 };
|
|
await data.push({ "id": students[0][i].id, "name": students[0][i].name, "email": students[0][i].email, "avatar_url": students[0][i].avatar_url, "KnowAss": know[0], "Flashcards": flash[0], "Practice": prac })
|
|
}
|
|
res.status(200).send(API._200(await data));
|
|
}
|
|
else {
|
|
res.status(200).send(API._200([]));
|
|
}
|
|
}
|
|
else {
|
|
res.status(200).send(API._200([]));
|
|
}
|
|
|
|
};
|
|
exports.countdash = async (req, res) => {
|
|
let data = await db_helper.query("SELECT SUM(CASE WHEN roll_id=3 THEN 1 ELSE 0 END) as TotalStudents,SUM(CASE WHEN roll_id=5 THEN 1 ELSE 0 END) as TotalTeachers,SUM(CASE WHEN roll_id=4 THEN 1 ELSE 0 END) as TotalClients FROM users where status=0");
|
|
let course = await db_helper.query("SELECT COUNT(*) as TotalCourse FROM certificates WHERE status=0");
|
|
let std = { "TotalStudents": data[0][0].TotalStudents, "TotalTeachers": data[0][0].TotalTeachers, "TotalCourse": course[0][0].TotalCourse, "TotalClients": data[0][0].TotalClients };
|
|
res.status(200).send(API._200(await std));
|
|
};
|
|
exports.deleteclientcohort = async (req, res) => {
|
|
var input = await helper.decryptRequest(req.body.data);
|
|
const cohort = await ClientCohort.findByPk(input.id)
|
|
if (!cohort) {
|
|
res.status(404).send(API._404({ message: `Cohort with id: ${input.id} was not found` }));
|
|
}
|
|
cohort.status = 1
|
|
let c = cohort.save();
|
|
res.status(200).send(API._200(await c));
|
|
};
|
|
exports.emptyclientcohort = async (req, res) => {
|
|
var input = await helper.decryptRequest(req.body.data);
|
|
let c = await db_helper.query("delete from clientcohorts where user_id=" + input.user_id);
|
|
res.status(200).send(API._200(await c));
|
|
};
|
|
exports.clientlogs_all = async (req, res) => {
|
|
res.status(200).send(API._200(await db_helper.allData('ClientLogs')));
|
|
}; |