Untitled
unknown
typescript
a year ago
23 kB
2
Indexable
Never
import { json } from "express"; import _ from "lodash"; import { STATUS_DELETED, STATUS_PRIVATE, STATUS_PUBLIC, TOPIC_CONTENT_TYPE_CARD, TOPIC_TYPE_CHILD_CARD, TOPIC_TYPE_CHILD_TOPIC, TOPIC_TYPE_EXERCISE, TOPIC_TYPE_LESSON } from "../constraint"; import { CardModel } from "../database/mongo/card"; import { TopicModel } from "../database/mongo/topic"; import { TopicExerciseDocument, TopicExerciseModel } from "../database/mongo/topicExercise"; import { Card, CardAnswer, CardQuestion } from "../models/model/card"; import Topic from "../models/model/topic"; import TopicExercise from "../models/model/topicExercise"; import { TopicQuestion } from "../models/model/topicQuestion"; import { BasicService } from "./basicService"; import { CardService } from "./cardService"; import PQueue from "p-queue"; import { TopicQuestionModel } from "../database/mongo/topicQuestion"; import { DELETE_ACTION } from "../models/roleConfig"; import { encryptQuestion } from "../utils/encryptQuestion"; import AdmZip from 'adm-zip'; import fs from 'fs'; import logger from "../utils/logger"; var sqlite3 = require('sqlite3').verbose(); // var sqlite3 = require('@journeyapps/sqlcipher').verbose(); var db; class ExportConfig { static TOPIC_TABLE = "Topic"; static COLUMN_ID = "id"; static COLUMN_PARENTID = "parentId"; static COLUMN_FRONT_TEXT = "frontText"; static COLUMN_NAME = "name"; static COLUMN_TYPE = "type"; static COLUMN_STATUS = "status"; static COLUMN_DESCRIPTION = "description"; static COLUMN_SDESCRIPTON = "shortDescription"; static COLUMN_START_TIME = "startTime"; static COLUMN_LAST_UPDATE = "lastUpdate"; static COLUMN_END_TIME = "endTime"; static COLUMN_SLUG = "slug"; static COLUMN_ORDER_INDEX = "orderIndex"; static COLUMN_CHILD_TYPE = "childType"; static COLUMN_AVATAR = "avatar"; static COLUMN_VIDEO_URL = "videoUrl"; static COLUMN_USER_ID = "userId"; static COLUMN_HAS_CHILD = "hasChild"; static COLUMN_CODE = "code"; static COLUMN_ANSWER = "answer"; static COLUMN_QUESTION = "question"; static COLUMN_SETTING = "setting"; static COLUMN_DIFFCULTY_LEVEL = "difficultyLevel" } const cardService = new CardService(); class ExportAppDbService extends BasicService { changeSqliteDbToMongo = async (props: { appId: string, url: string }) => { logger.debug("changeSqliteDbToMongo toeic"); const lastRootTopicId = 0; const { appId, url } = props; var db = new sqlite3.Database(url, (err) => { if (err) { logger.error(err.message); } else { logger.debug("connected db") } }); // 62a305aa17d6ebe546bd2413 --- courseId var dbTopics: any[] = []; var dbTExs: any[] = []; var dbCards: any[] = []; var dbTQues: any[] = []; const queue = new PQueue({ concurrency: 1000 }); queue.on("next", () => { logger.debug("Queue: pending: ", queue.pending, ", size: ", queue.size); if (!queue.pending && !queue.size) { logger.debug("Complete"); } }); console.log("runnnnnnnn"); var yyy = (await TopicModel.deleteMany({password: `assmin-kien-${appId}`})).deletedCount; var xxx = (await TopicExerciseModel.deleteMany({additionalInfo: `assmin-kien-${appId}`})).deletedCount; console.log("XXXXXXX", xxx); console.log("XXXXXXX", yyy); // TopicModel.deleteMany({password: `assmin-kien-${appId}`}); // TopicExerciseModel.deleteMany({additionalInfo: `assmin-kien-${appId}`}); // const _topics = await TopicModel.find({ courseId: appId }); // logger.debug(_topics.length); // const _topicIds = _topics.map(_e => `${_e._id}`); // const deltedTopicQuestion = await (await TopicQuestionModel.deleteMany({ topicId: { $in: _topicIds } })).deletedCount; // console.log("delted topic Quétion", deltedTopicQuestion); // const deltedTopicEx = await (await TopicExerciseModel.deleteMany({ _id: { $in: _topicIds } })).deletedCount; // console.log("delted deltedTopicEx", deltedTopicEx); // // // TODO - CONVERT TOPIC QUESTION TO CARD PARENTID // const _tqs = await TopicQuestionModel.find({ topicId: { $in: _topicIds } }); // var mapTqs = {}; // _tqs.forEach(_item => { // mapTqs[`${_item.cardId}`] = `${_item.topicId}`; // }) // console.log(Object.keys(mapTqs).length); // const _cards = await CardModel.find({ code: `assmin-kien-${appId}` }); // _cards.forEach(_cardItem => { // _cardItem.parentId = mapTqs[`${_cardItem._id}`] ?? null // // _cardItem.save(); // }) // db.all("SELECT * FROM State", function (err, rows) { // rows.forEach(function (row) { // console.log(row.mid + ": --- " + row.name); // let topicState = new Topic(); // topicState._id = row.mid; // topicState.password = `assmin-kien-${appId}`; // topicState.name = row.name; // topicState.description = row.governmentAgency; // topicState.shortDescription = row.shortName; // topicState.type = TOPIC_TYPE_LESSON; // topicState.courseId = appId; // topicState.parentId = null; // topicState.childType = TOPIC_TYPE_CHILD_TOPIC; // dbTopics.push(topicState); // }); // TopicModel.insertMany(dbTopics); // }); // db.all("SELECT * FROM Topic", async (err, rows) => { // rows.forEach(function (row) { // let dbParentId = parseInt(row.parentId); // let topicState = new Topic(); // topicState._id = row.mId; // topicState.password = `assmin-kien-${appId}`; // topicState.name = row.content; // topicState.description = row.description; // topicState.type = dbParentId <= lastRootTopicId ? TOPIC_TYPE_LESSON : TOPIC_TYPE_EXERCISE; // topicState.courseId = appId; // topicState.parentId = row.mParentId; // topicState.childType = dbParentId <= lastRootTopicId ? TOPIC_TYPE_CHILD_TOPIC : TOPIC_TYPE_CHILD_CARD; // logger.info(`xxx--${topicState.name} --- ${dbParentId} ---- ${topicState.type} ---- ${topicState.childType}`); // dbTopics.push(topicState); // if (dbParentId > lastRootTopicId) { // let tEx = new TopicExercise({ // _id: topicState._id, // contentType: TOPIC_CONTENT_TYPE_CARD, // questionsNum: row.totalQuestion, // additionalInfo: `assmin-kien-${appId}` // key for remove if tool run wrong. // }) // dbTExs.push(tEx); // } // }); // var exs = dbTExs.map(_e => _e._id); // var exs2 = dbTopics.map(_e => `${_e._id}`); // logger.debug(exs2) // // await TopicExerciseModel.deleteMany({ // // _id: { $in: dbTExs.map(_e => _e._id) } // // }); // // await TopicModel.deleteMany({ // // _id: { $in: dbTopics.map(_e => _e._id) } // // }); // await TopicExerciseModel.insertMany(dbTExs); // await TopicModel.insertMany(dbTopics); // }); var xxx2 = (await CardModel.deleteMany({code: `assmin-kien-${appId}`})).deletedCount; console.log(xxx2); // db.all("SELECT * FROM Question", async (err, rows) => { // rows.forEach(function (row) { // let _card = new Card(); // let _cardAnswer = new CardAnswer(); // let _cardQuestion = new CardQuestion(); // _card._id = row.mId; // _card.parentId = row.mParentId; // _card.code = `assmin-kien-${appId}`; // _cardAnswer.texts = JSON.parse(row.correctAnswer); // _cardAnswer.choices = JSON.parse(row.incorrectAnswer); // _cardAnswer.hint = row.explanation; // _card.answer = _cardAnswer; // _cardQuestion.image = row.image; // _cardQuestion.sound = row.audio; // _cardQuestion.text = row.content; // _card.question = _cardQuestion; // _card.hasChild = row.hasChild ?? 0; // _card.status = STATUS_PUBLIC; // dbCards.push(_card); // }); // await CardModel.insertMany(dbCards); // }); // // await CardModel.deleteMany({ code: `assmin-kien-${appId}` }) // db.all("SELECT * FROM Question", async (err, rows) => { // rows.forEach(function (row) { // let _card = new Card(); // let _cardAnswer = new CardAnswer(); // let _cardQuestion = new CardQuestion(); // _card._id = row.mid; // _card.code = `assmin-kien-${appId}`; // _cardAnswer.texts = JSON.parse(row.correctAnswer); // _cardAnswer.choices = JSON.parse(row.incorrectAnswer); // _cardAnswer.hint = row.explanation; // _card.answer = _cardAnswer; // _cardQuestion.image = row.image; // _cardQuestion.sound = row.audio; // _cardQuestion.text = row.content; // _card.question = _cardQuestion; // _card.parentId = row.mParentId; // _card.hasChild = row.hasChild || 0; // _card.status = STATUS_PUBLIC; // dbCards.push(_card); // }); // await CardModel.insertMany(dbCards); // }); // logger.debug("done"); // dbCards.forEach((item) => { // queue.add(() => { // return CardModel.create({ // ...item // }); // }); // }); // db.all("SELECT * FROM TopicQuestion", async (err, rows) => { // rows.forEach(function (row) { // let _tques = new TopicQuestion(); // _tques._id = row.mid; // _tques.topicId = row.mParentId; // _tques.cardId = row.mCardId; // _tques.status = STATUS_PUBLIC; // dbTQues.push(_tques); // }); // console.log("dbTQues length" + dbTQues.length.toString()); // // await CardModel.insertMany(dbCards); // // TODO - INSERT ALL TOPIC QUESTION HERE // dbTQues.forEach((item) => { // queue.add(() => { // return TopicQuestionModel.create({ // ...item // }); // }); // }); // }); } fixTopicExercise = async (params: { hasState: boolean, courseId: string, appName: string }) => { const { courseId } = <{ courseId: string }>params; let _deletedTopic: string[] = []; let _openTopic: Topic[] = []; const topics = await TopicModel.find({ courseId: '62c2ae013972dff792028037' }, '_id status type parentId'); topics.forEach(_e => { if (_e.status === STATUS_DELETED || _e.status === STATUS_PRIVATE) { _deletedTopic.push(`${_e._id}`); } }); topics.forEach(_tp => { let _tpParentId = `${_tp.parentId}`; if (![STATUS_DELETED, STATUS_PRIVATE].includes(_tp.status) && !_deletedTopic.includes(_tpParentId)) { _openTopic.push(_tp); } }); _openTopic = _openTopic.filter(_otp => _otp['type'] !== 1); let _openTpEx = await TopicExerciseModel.find({ _id: { $in: _openTopic.map(_e => _e['_id']) } }); let mapTpEx: Map<string, TopicExerciseDocument> = new Map<string, TopicExerciseDocument>(); _openTpEx.forEach(_item => { mapTpEx.set(`${_item._id}`, _item); }) await Promise.all(_openTopic.map(async (_otp) => { const _cardNum = await CardModel.find({ parentId: _otp['_id'], status: 1 }).countDocuments(); const _itemTpEx = mapTpEx.get(`${_otp['_id']}`); if (_itemTpEx) { logger.debug(`Save --- ${_otp._id} ---- ${_itemTpEx.questionsNum} ---- ${_cardNum} done`); _itemTpEx.questionsNum = _cardNum; await _itemTpEx.save(); } })) logger.debug("open topics: ", _openTopic.length); } exportAppDb = async (params: { hasState: boolean, courseId: string, appName: string, isFAQ: boolean }) => { // Export Topic const { hasState, courseId, appName, isFAQ } = <{ hasState?: boolean, courseId: string, appName: string, isFAQ: boolean }>params; let topicIds: string[] = []; let topicChildCards: string[] = []; let cards: Card[] = []; let _deletedTopic: string[] = []; let _openTopic: Topic[] = []; let _f1DeleteTopic: string[] = []; await this.createDb(appName, isFAQ); const topics = await TopicModel.find({ courseId, // status: { $nin: [-1, 0] } }); logger.debug("all topic in db", topics.length); topics.forEach(_e => { if (_e.status === STATUS_DELETED || _e.status === STATUS_PRIVATE) { _deletedTopic.push(`${_e._id}`); } }); topics.forEach(_e => { let _tpParentId = `${_e.parentId}`; if (_deletedTopic.includes(_tpParentId)) { _f1DeleteTopic.push(`${_e._id}`); } }); logger.debug("_deleted topic", _deletedTopic.length); topics.forEach(_tp => { let _tpParentId = `${_tp.parentId}`; if (![STATUS_DELETED, STATUS_PRIVATE].includes(_tp.status) && !_deletedTopic.includes(_tpParentId) && !_f1DeleteTopic.includes(_tpParentId)) { _openTopic.push(_tp); } else { } }) _openTopic.forEach((_item) => { logger.debug(`${_item.name} ----- ${_item._id}`); }) logger.debug("open topics: ", _openTopic.length); // Export card if (!hasState) { if (_openTopic) { let _index: number = 0; for await (const _item of _openTopic) { var topicItem = new Topic(_item); topicIds.push(`${_item._id}`); await this.insertTopic(topicItem); if (topicItem.childType === 0 || topicItem.childType === 2) { _index++; topicChildCards.push(`${topicItem._id}`); const _cardDbs: Card[] = await cardService.getCardsByParentId({ parentId: `${topicItem._id}` }); logger.debug(`---${_index} --- cards: ${_cardDbs.length}`); cards = cards.concat(_cardDbs); _cardDbs.forEach((_item) => { if (_item.childCards && _item.childCards.length) { cards = cards.concat(_item.childCards); } }) } } } const tExercises = await TopicExerciseModel.find({ _id: { $in: topicIds } }); let _indexInserCard2 = 0; for await (const _tEx of tExercises) { _indexInserCard2++; // logger.debug(`---${_indexInserCard2} --- insert topic Ex: ${_tEx._id}`); await this.insertTExercise(_tEx); } _indexInserCard2 = 0; for await (const _cardItem of cards) { _indexInserCard2++; // logger.debug(`---${_indexInserCard2} --- insert cards: ${_cardItem._id}`); await this.insertCard(_cardItem); } } else { logger.debug("hahhaha", topics.length); await this.exportHasStateDb({ topics: _openTopic }); } logger.debug("all questions", cards.length); this.closeDb(); setTimeout(() => { var zipFile = new AdmZip(); // /Users/kiennpt/Dropbox/estudyme_apps/${appName}/data/new_data2.db if (isFAQ) { zipFile.addLocalFile('./faq.db'); zipFile.writeZip(`/Users/kiennpt/Dropbox/estudyme_apps/${appName}/data/faq.zip`) } else { zipFile.addLocalFile('./new_data.db'); zipFile.writeZip(`/Users/kiennpt/Dropbox/estudyme_apps/${appName}/data/new_data.zip`) } }, 10000) return cards.length; } exportHasStateDb = async (params: { topics: Topic[] }) => { const { topics } = params; const mapTopic: Map<string, string> = new Map<string, string>(); const listState = topics.filter((_tp: Topic) => _tp.parentId == null); const listStateIds: string[] = listState.map(_t => `${_t._id}`); const rootIds: string[] = []; const secondTpIds: string[] = []; const topicIds = topics.map(_tp => _tp._id); topics.forEach(_tp => { if (_tp.parentId === null) { mapTopic.set(`${_tp._id}`, `${_tp._id}`); // _tp.courseId = _tp._id; } else { } }); topics.forEach(_tp => { if (listStateIds.includes(`${_tp.parentId}`)) { const _parentId = `${_tp.parentId}`; // _tp.courseId = mapTopic.get(_parentId); // logger.debug(`run rootTopicsssss ${_tp.name} `); rootIds.push(`${_tp._id}`); mapTopic.set(`${_tp._id}`, _parentId); } }) topics.forEach(_tp => { if (rootIds.includes(`${_tp.parentId}`)) { secondTpIds.push(`${_tp._id}`); const _stateId = mapTopic.get(`${_tp.parentId}`) mapTopic.set(`${_tp._id}`, _stateId ?? "NULL"); } }) const queue = new PQueue({ concurrency: 1000 }); queue.on("next", () => { logger.debug("Queue: pending: ", queue.pending, ", size: ", queue.size); if (!queue.pending && !queue.size) { logger.debug("Complete"); } }); // todo - insert topic exercise const tExercises = await TopicExerciseModel.find({ _id: { $in: topicIds } }); const _tquestions = await TopicQuestionModel.find({ topicId: { $in: topicIds } }) const _cardIds = _tquestions.map(_tq => _tq.cardId); logger.debug("Topic Questions length", _tquestions.length); const _cards = await CardModel.find({ _id: { $in: _cardIds }, status: { $nin: [STATUS_DELETED, STATUS_PRIVATE] } }) logger.debug("cards length", _cards.length); // _cards.forEach((item) => { // queue.add(() => { // return this.insertCard(item); // }); // }); _tquestions.forEach((item) => { const _stateId = mapTopic.get(`${item.topicId}`) ?? null; item.stateId = _stateId; queue.add(() => { return this.insertTQuestions(item); }); }); // tExercises.forEach((item) => { // queue.add(() => { // return this.insertTExercise(item); // }); // }); // topics.forEach((item) => { // queue.add(() => { // return this.insertTopic(item); // }); // }); } async createDb(appName: string, isFAQ: boolean) { logger.debug("createDb appName"); fs.rmSync('./new_data.db', { force: true }) fs.rmSync('./faq.db', { force: true }) // db = new sqlite3.Database(`/Users/kiennpt/Dropbox/estudyme_apps/${appName}/data/new_data2.db`, this.createTable); if (isFAQ) { db = new sqlite3.Database(`./faq.db`, this.createTable); } else { db = new sqlite3.Database(`./new_data.db`, this.createTable); } } async createTable() { logger.debug("createTable Topic"); // db.serialize(() => { // db.run("PRAGMA cipher_compatibility = 4"); // db.run("PRAGMA key = 'koolsoft@4312'"); db.run(`CREATE TABLE IF NOT EXISTS Topic ( ${ExportConfig.COLUMN_ID} TEXT PRIMARY KEY, ${ExportConfig.COLUMN_PARENTID} TEXT, ${ExportConfig.COLUMN_NAME} TEXT, ${ExportConfig.COLUMN_TYPE} INTEGER, ${ExportConfig.COLUMN_CHILD_TYPE} INTEGER, ${ExportConfig.COLUMN_STATUS} INTEGER, ${ExportConfig.COLUMN_START_TIME} INTEGER, ${ExportConfig.COLUMN_END_TIME} INTEGER, ${ExportConfig.COLUMN_LAST_UPDATE} INTEGER, ${ExportConfig.COLUMN_DESCRIPTION} TEXT, ${ExportConfig.COLUMN_SDESCRIPTON} TEXT, ${ExportConfig.COLUMN_SLUG} TEXT, ${ExportConfig.COLUMN_ORDER_INDEX} INTEGER, ${ExportConfig.COLUMN_AVATAR} TEXT, ${ExportConfig.COLUMN_USER_ID} TEXT, ${ExportConfig.COLUMN_VIDEO_URL} TEXT) `); db.run(`CREATE TABLE IF NOT EXISTS Card ( ${ExportConfig.COLUMN_ID} TEXT PRIMARY KEY, ${ExportConfig.COLUMN_PARENTID} TEXT, ${ExportConfig.COLUMN_HAS_CHILD} INTEGER, ${ExportConfig.COLUMN_QUESTION} TEXT, ${ExportConfig.COLUMN_ANSWER} TEXT, ${ExportConfig.COLUMN_CODE} TEXT, ${ExportConfig.COLUMN_SETTING} TEXT, ${ExportConfig.COLUMN_STATUS} INTEGER, ${ExportConfig.COLUMN_ORDER_INDEX} INTEGER, ${ExportConfig.COLUMN_LAST_UPDATE} INTEGER, ${ExportConfig.COLUMN_DIFFCULTY_LEVEL} INTEGER, ${ExportConfig.COLUMN_TYPE} INTEGER) `); db.run(`CREATE TABLE IF NOT EXISTS TopicExercise ( ${ExportConfig.COLUMN_ID} TEXT PRIMARY KEY, questionsNum INTEGER, questionsPlayNum INTEGER, mode INTEGER, duration INTEGER, pass INTEGER, replay INTEGER, contentType INTEGER, baremScore INTEGER, pauseTimes INTEGER) `); db.run(`CREATE TABLE IF NOT EXISTS TopicQuestion ( ${ExportConfig.COLUMN_ID} TEXT PRIMARY KEY, topicId TEXT, cardId TEXT, stateId TEXT, ${ExportConfig.COLUMN_STATUS} INTEGER) `); // this.createCardTable(); // }) } createTableTopicExercise() { } createCardTable() { logger.debug("createTable Card"); // db.run(`CREATE TABLE IF NOT EXISTS Card ( // ${ExportConfig.COLUMN_ID} TEXT, // ${ExportConfig.COLUMN_PARENTID} TEXT, // ${ExportConfig.COLUMN_HAS_CHILD} INTEGER, // ${ExportConfig.COLUMN_QUESTION} TEXT, // ${ExportConfig.COLUMN_ANSWER} TEXT, // ${ExportConfig.COLUMN_CODE} TEXT, // ${ExportConfig.COLUMN_SETTING} TEXT, // ${ExportConfig.COLUMN_STATUS} INTEGER, // ${ExportConfig.COLUMN_ORDER_INDEX} INTEGER, // ${ExportConfig.COLUMN_LAST_UPDATE} INTEGER, // ${ExportConfig.COLUMN_DIFFCULTY_LEVEL} INTEGER, // ${ExportConfig.COLUMN_TYPE} INTEGER) // `); } async insertTopic(_topic: Topic) { var stmt = db.prepare("INSERT INTO Topic VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); stmt.run([ `${_topic._id}`, `${_topic.parentId}`, _topic.name, _topic.type, _topic.childType, _topic.status, _topic.startTime, _topic.endTime, _topic.lastUpdate, _topic.description, _topic.shortDescription, _topic.slug, _topic.orderIndex, _topic.avatar, _topic.userId, _topic.videoUrl ]); stmt.finalize(); } async insertTExercise(_tX: TopicExercise) { var stmt = db.prepare("INSERT INTO TopicExercise VALUES (?,?,?,?,?,?,?,?,?,?)"); stmt.run([ `${_tX._id}`, `${_tX.questionsNum}`, _tX.questionsPlayNum, _tX.mode, _tX.duration, _tX.pass, _tX.replay, _tX.contentType, _tX.baremScore, _tX.pauseTimes ]); stmt.finalize(); } async insertTQuestions(_tX: TopicQuestion) { var stmt = db.prepare("INSERT INTO TopicQuestion VALUES (?,?,?,?,?)"); stmt.run([ `${_tX._id}`, `${_tX.topicId}`, _tX.cardId, _tX.stateId, _tX.status ]); stmt.finalize(); } async insertCard(_card: Card) { _card.question.text = encryptQuestion(_card.question.text); var stmt = db.prepare("INSERT INTO Card VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"); stmt.run([ `${_card._id}`, `${_card.parentId}`, _card.hasChild, JSON.stringify(_card.question), JSON.stringify(_card.answer), _card.code, JSON.stringify(_card.setting), _card.status, _card.orderIndex, _card.lastUpdate, _card.difficultyLevel, _card.type ]); stmt.finalize(); } readAllRows() { logger.debug("readAllRows lorem"); db.all("SELECT rowid AS id, info FROM lorem", function (err, rows) { rows.forEach(function (row) { logger.debug(row.id + ": " + row.info); }); }); } closeDb() { logger.debug("closeDb"); db.close(); } } export { ExportAppDbService };