272 lines
20 KiB
JavaScript
272 lines
20 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 { User, Version, Navigation, Onbording } = require("../models");
|
|
|
|
exports.usp_getenrollcohorts = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
const time = await db_helper.query("call usp_getenrollcohorts(" + input.user_id + ")")
|
|
for (let i = 0; i < time.length; i++) {
|
|
time[i].access = JSON.parse(time[i].access);
|
|
time[i].overdue_date = new Date(time[i].overdue_date);
|
|
}
|
|
let uniqueArray = time.reduce((accumulator, currentObject) => {
|
|
if (!accumulator.some(obj => obj.cohort_id === currentObject.cohort_id)) {
|
|
accumulator.push(currentObject);
|
|
}
|
|
return accumulator;
|
|
}, []);
|
|
res.status(200).send(API._200(await uniqueArray));
|
|
|
|
};
|
|
exports.activity_tracker = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let ttlkque = await db.sequelize.query("select count(*) as ttlques,ass_id 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) and c.id= :cohort_id where find_in_set(" + input.user_id + ",userslist) and ka.status=0 group by ka.id) and status=0 group by ass_id", { replacements: { cohort_id: input.cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const activity = await db.sequelize.query("select type,description,createdAt from activity_trackers where (email=:email or user_id=:user_id) and (cohort_id= :cohort_id or cohort_id=0)", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id, email: input.email } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const know = await db.sequelize.query("select main.ttlcomplete,main.dt,know.knowass_name,know.id from(SELECT count(*) as ttlcomplete,createdAt as dt,ass_id FROM know_ass_ques_ans WHERE active=1 and cohort_id= :cohort_id and user_id = :user_id and ass_id in(SELECT ka.id from knowledge_asses as ka inner join cohorts as c on find_in_set(ka.id,c.knowassessments) and c.id= :cohort_id where find_in_set(" + input.user_id + ",c.userslist) and ka.status=0 group by ka.id) group by CAST(createdAt as Date), ass_id)as main left join knowledge_asses as know on know.id=main.ass_id", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
let ttlpque = await db.sequelize.query("select count(*) as ttlques,test_id from practiceques where test_id in(SELECT ka.id from practicetests as ka inner join cohorts as c on find_in_set(ka.id,c.practicetests) and c.id= :cohort_id where find_in_set(" + input.user_id + ",userslist) and ka.status=0 group by ka.id) and status=0 group by test_id", { replacements: { cohort_id: input.cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const practice = await db.sequelize.query("select main.ttlcomplete,main.dt,know.practice_name,know.id from(SELECT count(*) as ttlcomplete,createdAt as dt,test_id FROM practice_ans WHERE active=1 and cohort_id= :cohort_id and user_id = :user_id and test_id in(SELECT ka.id from practicetests as ka inner join cohorts as c on find_in_set(ka.id,c.practicetests) and c.id= :cohort_id where find_in_set(" + input.user_id + ",c.userslist) and ka.status=0 group by ka.id) group by CAST(createdAt as Date), test_id)as main left join practicetests as know on know.id=main.test_id", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const flash = await db.sequelize.query("select 'flashcard',createdAt as activity_date,sum(case when user_res=1 then 1 else 0 end) as ttlright,sum(case when user_res=2 then 1 else 0 end) as ttlreview,sum(case when mark=1 then 1 else 0 end) as ttlbookmark,sum(case when (notes=1 && user_res=0) then 1 else 0 end) as ttlnotes from flashans_offlines where UserId= :user_id group by createdAt", { replacements: { user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const ttlvideo = await db.sequelize.query("select count(*) as ttlvideo from videos where certi_id in(select certi_id from cohorts where id= :cohort_id) and status=0", { replacements: { cohort_id: input.cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const video = await db.sequelize.query("select count(*) as ttlview,createdAt from video_mind_views where user_id= :user_id and cohort_id= :cohort_id and type='VIDEO' and status=0 group by CAST(createdAt as Date)", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const ttlmind = await db.sequelize.query("select count(*) as ttlmind from mind_maps where certi_id in(select certi_id from cohorts where id= :cohort_id) and status=0", { replacements: { cohort_id: input.cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const mindmap = await db.sequelize.query("select count(*) as ttlview,createdAt from video_mind_views where user_id= :user_id and cohort_id= :cohort_id and type='MIND' and status=0 group by CAST(createdAt as Date)", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
let data = [];
|
|
for (let i = 0; i < ttlkque[0].length; i++) {
|
|
for (let j = 0; j < know[0].length; j++) {
|
|
if (ttlkque[0][i].ass_id == know[0][j].id) {
|
|
let ttlques = ttlkque[0][i].ttlques;
|
|
let ttlcomplete = know[0][j].ttlcomplete;
|
|
let per = (parseInt(ttlcomplete) * 100) / (parseInt(ttlques));
|
|
data.push({
|
|
activity_date: know[0][j].dt,
|
|
test_name: know[0][j].knowass_name,
|
|
percentage: Math.round(per)
|
|
});
|
|
}
|
|
}
|
|
}
|
|
for (let i = 0; i < ttlpque[0].length; i++) {
|
|
for (let j = 0; j < practice[0].length; j++) {
|
|
if (ttlpque[0][i].test_id == practice[0][j].id) {
|
|
let ttlques = ttlpque[0][i].ttlques;
|
|
let ttlcomplete = practice[0][j].ttlcomplete;
|
|
let per = (parseInt(ttlcomplete) * 100) / (parseInt(ttlques));
|
|
data.push({
|
|
activity_date: practice[0][j].dt,
|
|
test_name: practice[0][j].practice_name,
|
|
percentage: Math.round(per)
|
|
});
|
|
}
|
|
}
|
|
}
|
|
for (let f = 0; f < flash[0].length; f++) {
|
|
data.push(flash[0][f]);
|
|
}
|
|
for (let v = 0; v < video[0].length; v++) {
|
|
let per = (parseInt(video[0][v].ttlview) * 100) / (parseInt(ttlvideo[0][0].ttlvideo));
|
|
data.push({
|
|
activity_date: video[0][v].createdAt,
|
|
test_name: 'Video Lesson',
|
|
percentage: Math.round(per)
|
|
});
|
|
}
|
|
for (let v = 0; v < mindmap[0].length; v++) {
|
|
let per = (parseInt(mindmap[0][v].ttlview) * 100) / (parseInt(ttlmind[0][0].ttlmind));
|
|
data.push({
|
|
activity_date: mindmap[0][v].createdAt,
|
|
test_name: 'Mind Maps',
|
|
percentage: Math.round(per)
|
|
});
|
|
}
|
|
for (let a = 0; a < activity[0].length; a++) {
|
|
data.push({
|
|
other: "other",
|
|
activity_date: activity[0][a].createdAt,
|
|
test_name: activity[0][a].description,
|
|
percentage: 0
|
|
});
|
|
}
|
|
res.status(200).send(API._200(await data.sort((a, b) => new Date(b.activity_date).getTime() - new Date(a.activity_date).getTime())));
|
|
|
|
};
|
|
|
|
exports.add = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
res.status(200).send(API._200(await db_helper.addData('TestLogs', input)));
|
|
};
|
|
exports.cohortbyIds = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let result = await db_helper.query("SELECT id,cohort_name,knowassessments,practicetests,ketexamtests from cohorts where id in(" + input.cohortIds + ")")
|
|
res.status(200).send(API._200(await result[0]));
|
|
};
|
|
exports.kareset = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
await db.sequelize.query("DELETE FROM knowcompletes WHERE ka_id = :ass_id and cohort_id= :cohort_id and user_id= :user_id", { replacements: { ass_id: input.ass_id, cohort_id: input.cohort_id, user_id: input.user_id } })
|
|
|
|
const result = await db.sequelize.query("UPDATE know_ass_ques_ans SET active = 0 WHERE ass_id= :ass_id and cohort_id= :cohort_id and user_id= :user_id", { replacements: { ass_id: input.ass_id, cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result));
|
|
};
|
|
exports.practicereset = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
await db.sequelize.query("DELETE FROM ptestcomplates WHERE pt_id = :pt_id and cohort_id = :cohort_id and user_id = :user_id", { replacements: { pt_id: input.pt_id, cohort_id: input.cohort_id, user_id: input.user_id } })
|
|
|
|
const result = await db.sequelize.query("UPDATE practice_ans SET active=0 WHERE test_id= :pt_id and cohort_id= :cohort_id and user_id= :user_id", { replacements: { pt_id: input.pt_id, cohort_id: input.cohort_id, user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result));
|
|
};
|
|
exports.studentreport = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let user_id = input.user_id;
|
|
let cohort_id = input.cohort_id;
|
|
|
|
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));
|
|
})
|
|
|
|
const log = await db.sequelize.query("select createdAt from login_logs where user_id= :user_id order by createdAt ASC", { replacements: { user_id: user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
|
|
const student = await db.sequelize.query("select * from users where id= :user_id", { replacements: { user_id: user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
|
|
const know = await db.sequelize.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 status=0 and ass_id=ka.id) as ttlpoint,(case when kc.createdAt is null then '-' else kc.createdAt end) as CompletedOn from know_ass_ques_ans as kaqa left join knowledge_asses as ka on ka.id=kaqa.ass_id left join knowcompletes as kc on kc.ka_id=ka.id and kc.user_id= :user_id and kc.cohort_id= :cohort_id where kaqa.active=1 and kaqa.cohort_id= :cohort_id and kaqa.user_id= :user_id group by ka.id)", { replacements: { user_id: user_id, cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
|
|
const ptest = await db.sequelize.query("(select sum(case when ptq.correct=1 then 1 else 0 end) ttlpointget,pt.id,pt.practice_name,(select count(*) from practiceques where status=0 and test_id=pt.id) as ttlpoint,(case when kc.createdAt is null then '-' else kc.createdAt end) as CompletedOn from practice_ans as ptq left join practicetests as pt on pt.id=ptq.test_id left join ptestcomplates as kc on kc.pt_id=pt.id and kc.user_id= :user_id and kc.cohort_id= :cohort_id where ptq.active=1 and ptq.cohort_id= :cohort_id and ptq.user_id= :user_id group by pt.id)", { replacements: { user_id: user_id, cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
const flashcarddata = await db_helper.query("select * from flashans_offlines where UserId=" + user_id + " and cohort_id=" + input.cohort_id + " limit 1");
|
|
var flash;
|
|
if (flashcarddata[0].length > 0) {
|
|
flash = await sequelize.query("select f.ttlcards,case when count(distinct (case when user_res = 1 then cid end))> f.ttlcards then f.ttlcards else count(distinct (case when user_res = 1 then cid end)) 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 from flashans_offlines right join(select count(*) ttlcards from flashcards where certi_id in (select certi_id from cohorts where id = :cohort_id) and status = 0)f on 1 = 1 where UserId = :user_id and cohort_id=:cohort_id", { replacements: { user_id: user_id, cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
}
|
|
else {
|
|
flash = await sequelize.query("select f.ttlcards,case when count(distinct (case when user_res = 1 then cid end))> f.ttlcards then f.ttlcards else count(distinct (case when user_res = 1 then cid end)) 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 from flashans_offlines right join(select count(*) ttlcards from flashcards where certi_id in (select certi_id from cohorts where id = :cohort_id) and status = 0)f on 1 = 1 where UserId = :user_id", { replacements: { user_id: user_id, cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
}
|
|
|
|
const ttlFlash = await sequelize.query("(SELECT COUNT(*) as ttlcards FROM flashcards WHERE certi_id in(select certi_id from cohorts where id= :cohort_id) and status=0)", { replacements: { cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
|
|
const ttlprque = await sequelize.query("select count(*) as ttlprques from practiceques where test_id in(select type_id from cohort_schedules where type='PRACTICE' and cohort_id= :cohort_id and due_date <> '') and certi_id=" + coh[0][0].certi_id, { replacements: { cohort_id: cohort_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
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=${cohort_id} and user_id =${user_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, "right": right, "wrong": wrong, "marked": marked };
|
|
|
|
let SurveyCompleted = [];
|
|
let Examdate = [];
|
|
if (student.length > 0)
|
|
Examdate = student[0][0].schedule_date;
|
|
if (student.length > 0)
|
|
SurveyCompleted = student[0][0].is_first;
|
|
|
|
let response = { "Name": student[0][0].name, "onbording_date": student[0][0].onbording_date, "Avatar": student[0][0].avatar_url, "login_logs": log[0], "Examdate": Examdate, "SurveyCompleted": SurveyCompleted, "Knowledge": know[0], "PracticeTest": ptest[0], "Flashcards": flash[0], "FlashTotal": ttlFlash[0][0].ttlcards, "PracticeQuestions": prac };
|
|
res.status(200).send(API._200(await response));
|
|
};
|
|
exports.previous_result_ka = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let result = await db.sequelize.query("SELECT sum(currect) correct,sum(dknow) dknow,sum(incorrect) incorrect,ass_id test_id,cohort_id,DATE_FORMAT(createdAt,'%d/%m/%Y') examdate,TIME_FORMAT(createdAt, '%h:%i:%s %p')examtime,RowNumber from(SELECT *,case when correct=1 then 1 else 0 end currect , case when correct=0 then 1 else 0 end incorrect ,case when correct=2 then 1 else 0 end dknow, @cur:= IF(KAQ_id=@id, @cur+1, 1) AS RowNumber, @id := KAQ_id FROM know_ass_ques_ans t CROSS JOIN(SELECT @id:=(SELECT MIN(KAQ_id) FROM know_ass_ques_ans where active=0 and cohort_id= :cohort_id and ass_id= :test_id and user_id= :user_id), @cur:=0) AS init where active=0 and cohort_id= :cohort_id and ass_id= :test_id and user_id= :user_id ORDER BY t.KAQ_id) A group by RowNumber", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id, test_id: input.test_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result[0]));
|
|
};
|
|
exports.previous_result = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let result = await sequelize.query("SELECT sum(correct) correct,test_id,cohort_id,DATE_FORMAT(createdAt,'%d/%m/%Y') examdate,TIME_FORMAT(createdAt, '%h:%i:%s %p')examtime,RowNumber from(SELECT *, @cur:= IF(PTQ_id=@id, @cur+1, 1) AS RowNumber, @id := PTQ_id FROM practice_ans t CROSS JOIN(SELECT @id:=(SELECT MIN(PTQ_id) FROM practice_ans where active=0 and cohort_id= :cohort_id and test_id= :test_id and user_id= :user_id), @cur:=0) AS init where active=0 and cohort_id= :cohort_id and test_id= :test_id and user_id= :user_id ORDER BY t.PTQ_id) A group by RowNumber", { replacements: { cohort_id: input.cohort_id, user_id: input.user_id, test_id: input.test_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result[0]));
|
|
};
|
|
exports.history = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
|
|
const result = await db.sequelize.query(`
|
|
SELECT t.*,
|
|
( CASE
|
|
WHEN ( CASE
|
|
WHEN cat.ketexam_name IS NULL THEN KA.knowass_name
|
|
ELSE cat.ketexam_name
|
|
END ) IS NULL THEN p.practice_name
|
|
ELSE ( CASE
|
|
WHEN cat.ketexam_name IS NULL THEN KA.knowass_name
|
|
ELSE cat.ketexam_name
|
|
END )
|
|
END )AS test_name
|
|
FROM test_logs AS t
|
|
LEFT JOIN ketexamtests AS cat
|
|
ON t.test_id = cat.id
|
|
AND t.test_type = 'CAT'
|
|
LEFT JOIN knowledge_asses AS KA
|
|
ON t.test_id = KA.id
|
|
AND t.test_type = 'KA'
|
|
LEFT JOIN practicetests AS p
|
|
ON t.test_id = p.id
|
|
AND t.test_type = 'PRACTICE' where t.user_id= :user_id`, { replacements: { user_id: input.user_id } }).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result[0]));
|
|
};
|
|
exports.reset_progress_by_class = async (req, res) => {
|
|
const input = await helper.decryptRequest(req.body.data);
|
|
let result = await db.sequelize.query(`
|
|
delete from activity_trackers where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from ctestcomplates where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from ketexam_ans where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from video_section_views where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from know_ass_ques_ans where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from knowcompletes where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from ptestcomplates where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from practice_ans where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from test_logs where user_id=${input.user_id};
|
|
delete from video_mind_views where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
delete from video_notes where user_id=${input.user_id};
|
|
delete from card_views where user_id=${input.user_id} and cohort_id=${input.cohort_id};
|
|
`).then((res) => {
|
|
return Array.from(new Set(res));
|
|
})
|
|
res.status(200).send(API._200(await result[0]));
|
|
}; |