2936 lines
92 KiB
JavaScript
2936 lines
92 KiB
JavaScript
const express = require('express');
|
|
const session = require('express-session');
|
|
const fs = require('fs');
|
|
const log4js = require('log4js');
|
|
const http = require('http');
|
|
const https = require('https');
|
|
const date_format = require('date-format');
|
|
|
|
const { Configuration, OpenAI } = require('openai');
|
|
|
|
require('dotenv').config();
|
|
const { env } = require("process");
|
|
|
|
const { v4: uuidv4 } = require('uuid');
|
|
|
|
var SqlString = require('sqlstring');
|
|
const e = require('express');
|
|
const { stringify } = require('querystring');
|
|
const { get } = require('express/lib/response');
|
|
const res = require('express/lib/response');
|
|
|
|
OPENAI_API_KEY = process.env.OPENAI_API_KEY;
|
|
const openai = new OpenAI(OPENAI_API_KEY);
|
|
|
|
const reg_timeout = process.env.REG_LINK_TIMEOUT || 15;
|
|
const invite_timeout = process.env.INVITE_LINK_TIMEOUT || 20;
|
|
|
|
var timer;
|
|
if(timer)clearInterval(timer);
|
|
timer = setInterval(function(){
|
|
clearTimeoutSessions();
|
|
},
|
|
60*1000);
|
|
|
|
async function clearTimeoutSessions(){
|
|
var rtn = false;
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('delete from registration_sessions where TIMESTAMPDIFF(MINUTE, created, NOW()) > ? ',
|
|
[reg_timeout]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('delete from invite_user_sessions where TIMESTAMPDIFF(MINUTE, created, NOW()) > ? ',
|
|
[invite_timeout]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}catch (err) {
|
|
logger.error('DB err:'+err)
|
|
} finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
app.get('/', (req, res) => {
|
|
res.redirect('/top.html');
|
|
});
|
|
|
|
app.get('/working', async (req, res) => {
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from accounts limit 1 ',
|
|
[reg_timeout]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
res.send("sisai is working on "+getLocalIP());
|
|
}catch (err) {
|
|
logger.error('DB err:'+err)
|
|
res.send("sisai is BREAKING");
|
|
} finally {
|
|
if(connection)connection.release()
|
|
}
|
|
});
|
|
|
|
function getLocalIP(){
|
|
var ip = null;
|
|
const os = require('os');
|
|
|
|
// Get network interfaces
|
|
const networkInterfaces = os.networkInterfaces();
|
|
|
|
for (const interfaceName in networkInterfaces) {
|
|
const networkInterface = networkInterfaces[interfaceName];
|
|
for (const alias of networkInterface) {
|
|
// Check if it's IPv4 and not internal (i.e., not a loopback address)
|
|
if (alias.family === 'IPv4' && !alias.internal) {
|
|
ip=alias.address;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
return ip;
|
|
}
|
|
|
|
app.post('/login', async (req, res) => {
|
|
const body = await loadJsonBody(req, false)
|
|
const email = body.email.trim().toLowerCase();
|
|
logger.debug('login: '+email);
|
|
|
|
if(body.email && body.pwd){
|
|
const uinfo = await login(email, body.pwd);
|
|
if(uinfo){
|
|
req.session.uid = uinfo.id;
|
|
req.session.email = email;
|
|
req.session.fetchtime = new Date().getTime();
|
|
//set user session timeout
|
|
|
|
//req.session.abort_stream == 0;
|
|
//req.session.stop_time = 0;
|
|
//req.session.ask_time = req.session.stop_time+100;
|
|
res.send(JSON.stringify({status:'OK', uid:uinfo.id, nickname:uinfo.name}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
});
|
|
|
|
app.post('/getusrinf', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const user = await getUserInfo(req.session.uid);
|
|
if(user)user.status = 'OK';
|
|
res.send(user?JSON.stringify(user):JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
function checkUserSession(req){
|
|
logger.debug('check User-Session...');
|
|
var rtn = false;
|
|
if(!req.session.uid || !req.session.fetchtime)return rtn;
|
|
else if(new Date().getTime() - req.session.fetchtime > process.env.USER_SESSION_TIMEOUT*1000*60){//minutes
|
|
req.session.destroy();
|
|
rtn = false;
|
|
}else{
|
|
req.session.fetchtime = new Date().getTime();
|
|
rtn = true;
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
app.post('/renmspc', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
const body = await loadJsonBody(req)
|
|
|
|
if(body.space_id && body.space_name){
|
|
const space_id = await renameSpace(body.space_id, body.space_name, req.session.uid, body.language);
|
|
res.send(JSON.stringify(space_id?{status:'OK',space_id:space_id}:{status:'error'}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
});
|
|
|
|
app.post('/chksession', async (req, res) => {
|
|
if(checkUserSession(req)){
|
|
res.send(JSON.stringify({status:'OK', user_id:req.session.uid}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
});
|
|
|
|
app.post('/invtinguser', async (req, res) => {
|
|
const body = await loadJsonBody(req, false)
|
|
if(body.session){
|
|
const json = await inviteExistAccount(body.session);
|
|
if(null!=json['exist']){
|
|
if(json['exist'])res.send(JSON.stringify({status:'OK', exist:true}));
|
|
else res.send(JSON.stringify({status:'OK', exist:false, user_email:json['email']}));
|
|
return;
|
|
}
|
|
}
|
|
return res.send(JSON.stringify({status:'error'}));
|
|
});
|
|
|
|
|
|
app.post('/logout', async (req, res) => {
|
|
req.session.destroy();
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
});
|
|
|
|
app.post('/createspace', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
const body = await loadJsonBody(req)
|
|
|
|
if(body.spacetemp_id && body.space_name){
|
|
const space_id = await insertSpace(body.spacetemp_id, body.space_name, req.session.uid);
|
|
res.send(JSON.stringify(space_id?{status:'OK',space_id:space_id}:{status:'error'}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
});
|
|
|
|
app.post('/ask', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
logger.debug('asking');
|
|
const body = await loadJsonBody(req);
|
|
|
|
session_time.set(req.session.uid+"_ask_time", body.time);
|
|
|
|
if(body.new_my_prompt && body.space_id && body.question.length > 0){
|
|
//Insert new my prompt
|
|
const prompt = decodeURIComponent(body.question);
|
|
await insertAccountPrompt(req.session.uid, body.space_id, prompt, body.language);
|
|
}
|
|
|
|
//Call OpenAI API
|
|
res.setHeader('Content-Type', 'text/event-stream');
|
|
res.setHeader('Cache-Control', 'no-cache');
|
|
res.setHeader('Connection', 'keep-alive');
|
|
askOpenAPI(req, req.session.uid, body.space_id, body.conversation_id, body.question, body.advice_prompt_id, res, body.language);
|
|
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/stop', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
session_time.set(req.session.uid+"_stop_time", body.time);
|
|
|
|
res.send(JSON.stringify({status:'OK', reps:""}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/unspcusr', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
if(await unassignSpaceUser(req.session.uid, body.space_id, body.user_id)){
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/newconversation', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
const acc_session = await insertAccSpaceSession(req.session.uid, body.space_id, body.prompt, body.language);
|
|
res.send(JSON.stringify({status:'OK', conv_id:acc_session.session_id, short_name:acc_session.short_name}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/settmplbasic', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
if(body.tmpl_id){
|
|
await updateTemplateBasic(req.session.uid, body.tmpl_id, body.tmpl_name, body.tmpl_cmt, body.language);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}else{
|
|
const tmpl_id = await insertTemplateBasic(req.session.uid, body.tmpl_name, body.tmpl_cmt, body.language);
|
|
if(tmpl_id >=0 )res.send(JSON.stringify({status:'OK', tmpl_id:tmpl_id}));
|
|
else res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/deltmplinf', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
await deleteTemplateInfo(req.session.uid, body.tmpl_id, body.info_id);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
|
|
});
|
|
|
|
app.post('/deltmplprmt', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
await deleteTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_id);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/delspc', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
await deleteSpace(req.session.uid, body.space_id);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/settmplpub', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
const tmpl_id = await updateTemplatePublic(req.session.uid, body.tmpl_id, parseInt(body.is_public));
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/settmplinf', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
if(body.info_id){
|
|
await updateTemplateInfo(req.session.uid, body.tmpl_id, body.info_id, body.info_name, body.info_cmt, body.language);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}else{
|
|
const info_id = await insertTemplateInfo(req.session.uid, body.tmpl_id, body.info_name, body.info_cmt, body.language);
|
|
if(info_id >=0 )res.send(JSON.stringify({status:'OK', info_id:info_id}));
|
|
else res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/settmplprmt', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
if(body.prompt_id){
|
|
await updateTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_id, body.prompt_hint, body.prompt_head,
|
|
body.prompt_show, body.prompt_foot, body.language);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}else{
|
|
const prmt_id = await insertTemplatePrompt(req.session.uid, body.tmpl_id, body.prompt_hint, body.prompt_head,
|
|
body.prompt_show, body.prompt_foot, body.language);
|
|
if(prmt_id >=0 )res.send(JSON.stringify({status:'OK', prompt_id:prmt_id}));
|
|
else res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/chksamenm', async (req, res) => {
|
|
const body = await loadJsonBody(req);
|
|
if(body.type!='nick' && !req.session.uid){res.status(440).end();return;}
|
|
|
|
try{
|
|
var same = false;
|
|
switch(body.type){
|
|
case 'space':
|
|
var nm = body.space_name.trim().toLowerCase();
|
|
same = await checkSameSpaceName(req.session.uid, body.space_id, nm);
|
|
break;
|
|
case 'tmpl':
|
|
nm = body.tmpl_name.trim().toLowerCase();
|
|
same = await checkSameTmplName(body.tmpl_id, nm);
|
|
break;
|
|
case 'info':
|
|
nm = body.info_name.trim().toLowerCase();
|
|
same = await checkSameInfoName(body.tmpl_id, body.info_id, nm);
|
|
break;
|
|
case 'nick':
|
|
nm = body.nickname.trim().toLowerCase();
|
|
same = await checkSameNickName(nm);
|
|
break;
|
|
default:
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
res.send(JSON.stringify({status:'OK', same:same}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/setuser', async (req, res) => {
|
|
const body = await loadJsonBody(req, false);
|
|
const uname= body.user_name.toLowerCase();
|
|
logger.debug('setuser: '+uname);
|
|
if(!body.invt_session_id && !body.reg_session_id && !req.session.uid){res.status(440).end();return;}
|
|
|
|
try{
|
|
if(body.reg_session_id || body.invt_session_id){
|
|
const user_id = await setUser(body.reg_session_id, body.invt_session_id, uname, body.password, body.language);
|
|
if(user_id >=0 )res.send(JSON.stringify({status:'OK', user_id:user_id}));
|
|
else res.send(JSON.stringify({status:'error'}));
|
|
}else{
|
|
//TODO: Update user
|
|
//await updateUser(req.session.uid, body.user_id, body.password);
|
|
//res.send(JSON.stringify({status:'OK'}));
|
|
}
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/sendregmail', async (req, res) => {
|
|
try{
|
|
//Need not to check session
|
|
const body = await loadJsonBody(req)
|
|
const sid = await makeRegSession(body.email);
|
|
if(!sid){
|
|
res.send({status:'error'});
|
|
return;
|
|
}
|
|
|
|
const reg_link = process.env.REG_LINK_PREFIX + sid
|
|
const language = body.language?body.language:req.headers["accept-language"].substring(0,2)
|
|
const msg = makeMessage('reg_email_temp', language, [reg_timeout, reg_link])
|
|
const sent = await sendEmail(body.email, makeMessage('reg_email_subject', language), msg)
|
|
if(sent)res.send({status:'OK'});
|
|
else res.send({status:'error'});
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/sendinvtmail', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
if(!body.email || body.role == null || !(await isManager(req.session.uid, body.space_id))){
|
|
res.send({status:'error'});
|
|
return;
|
|
}
|
|
|
|
const sid = await generateInviteUserSession(req.session.uid, body.email, body.space_id, parseInt(body.role));
|
|
if(!sid){
|
|
res.send({status:'error'});
|
|
return;
|
|
}
|
|
|
|
const reg_link = process.env.INVITE_LINK_PREFIX + sid
|
|
const language = body.language?body.language:req.headers["accept-language"].substring(0,2)
|
|
const msg = makeMessage('invt_email_temp', language, [invite_timeout, reg_link])
|
|
const sent = await sendEmail(body.email, makeMessage('invt_email_subject', language), msg)
|
|
if(sent)res.send({status:'OK'});
|
|
else res.send({status:'error'});
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/invt', async (req, res) => {//Invite user
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
if(!body.session){
|
|
res.send(JSON.stringify({status:'error'}));
|
|
return;
|
|
}
|
|
const user_email = await processInviteMgrSession(body.session);
|
|
|
|
res.send(JSON.stringify({status:'OK', user_email:user_email}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/reguser', async (req, res) => {
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
if(!body.user_email){
|
|
res.send('');
|
|
return;
|
|
}
|
|
const sid = body.user_email
|
|
if(!sid){
|
|
res.send('');
|
|
return;
|
|
}
|
|
|
|
await setUser(sid);
|
|
res.json({ status: 'OK'});
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/listspacenms', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
var body=null;
|
|
try{body = await loadJsonBody(req);}catch(err){/*Do nothing*/}
|
|
|
|
const spaces = await listSpaceNames(req.session.uid, body&&body.role_type?parseInt(body.role_type):0);
|
|
if(spaces){//} && 0 < spaces.length){
|
|
const rtnJson = {}
|
|
rtnJson['spaces'] = spaces
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({spaces:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/listconv', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
|
|
const convs = await listAccSpcSessions(req.session.uid, body.space_id);
|
|
if(convs && 0 < convs.length){
|
|
const rtnJson = {}
|
|
rtnJson['convs'] = convs
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({status:'OK', convs:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/listhistory', async (req, res) => {await processModule.reqHistory(req, res);});
|
|
module.exports.reqHistory = async (req, res) =>{
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req);
|
|
|
|
const history = await processModule.listHistory(req.session.uid, body.space_id, body.conversation_id, body.language);
|
|
if(history && 0 < history.length){
|
|
const rtnJson = {}
|
|
rtnJson['history'] = history
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({status:'OK', history:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
};
|
|
|
|
app.post('/listspcusrs', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const list = await listSpaceUsers(req.session.uid);
|
|
if(list && 0 < list.length){
|
|
const rtnJson = {}
|
|
rtnJson['list'] = list
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({users:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/chgrlusr', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
|
|
if(await changeRoleUser(req.session.uid, body.space_id, body.user_id, parseInt(body.role))){
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
});
|
|
|
|
app.post('/listtmplnms', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
|
|
const tmpls = await listTmplNames(body.all_user?null:req.session.uid);
|
|
if(tmpls && 0 < tmpls.length){
|
|
const rtnJson = {}
|
|
rtnJson['tmpls'] = tmpls
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({tmpl:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/gettmplbasic', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
|
|
const tmpl = await getTemplateBasic(req.session.uid, body.tmpl_id, body.language);
|
|
if(tmpl){
|
|
tmpl.status = 'OK';
|
|
res.send(JSON.stringify(tmpl));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/gettmplother', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
|
|
const tmpl = await getTemplateOther(req.session.uid, body.tmpl_id, body.language);
|
|
if(tmpl){
|
|
tmpl.status = 'OK';
|
|
res.send(JSON.stringify(tmpl));
|
|
}else res.send(JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/getspace', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
const space = await getSpaceShow(body.space_id, req.session.uid, body.language);
|
|
if(space)space.status = 'OK';
|
|
res.send(space?JSON.stringify(space):JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/listspacetmpls', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
|
|
const spaces = await listSpaceTemplates(req.session.uid, body.type, body.page, body.per_page);
|
|
if(spaces && 0 < spaces.length){
|
|
const rtnJson = {}
|
|
rtnJson['tmpls'] = spaces
|
|
rtnJson['status'] = 'OK'
|
|
res.send(JSON.stringify(rtnJson));
|
|
}else res.send(JSON.stringify({tmpls:[]}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/setspcinf', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
await setSpaceInfo(req.session.uid, body.space_id, body.info_id, body.info, body.language);
|
|
res.send(JSON.stringify({status:'OK'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/getspcinf', async (req, res) => {
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
var rtn = await getSpaceInfo(req.session.uid, body.space_id, body.info_id, body.language);
|
|
res.send(rtn?JSON.stringify({status:'OK', info:rtn}):JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/getreguser', async (req, res) => {
|
|
try{
|
|
const body = await loadJsonBody(req)
|
|
const user = await getRegUserBySession(body.session);
|
|
res.send(user?JSON.stringify({status:'OK', user:user}):JSON.stringify({status:'error'}));
|
|
}catch(err){
|
|
logger.error(err);
|
|
}
|
|
});
|
|
|
|
app.post('/listmgrs', async (req, res) => {
|
|
const body = await loadJsonBody(req)
|
|
|
|
if(!body.email || !(await validSession(body.session_id))){
|
|
res.send('');
|
|
return;
|
|
}
|
|
|
|
const search_where = body.search && body.search.length >0 ? ' user_email like "%'+body.search+'%" ':' 1=1 '
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select u.user_id, role, g.created, u.user_email, g.group_id from user_groups g, users u '+
|
|
'where g.user_id = u.user_id and u.user_email<>? '+
|
|
'and group_id in (select group_id from user_groups, users where users.user_email=? and users.user_id=user_groups.user_id and role=0 and users.deleted is null and user_groups.deleted is null) '+
|
|
'and '+search_where+' and u.deleted is null and g.deleted is null limit 1000',
|
|
[body.email, body.email]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
var resp_body = {status:'OK'}
|
|
var users = []
|
|
if(results && 0 < results.length){
|
|
const urlObject = new URL(process.env.REG_LINK_PREFIX);
|
|
const hostName = urlObject.hostname;
|
|
|
|
const portalResilt = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select domain_id from domains where domain = ? and deleted is null',
|
|
[hostName]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
resp_body['domain_id'] = portalResilt[0].domain_id
|
|
resp_body['domain'] = hostName
|
|
for(const row of results){
|
|
users.push({user_id:row.user_id, role:row.role, created:row.created, user_email:row.user_email, group_id:row.group_id})
|
|
}
|
|
}
|
|
|
|
resp_body['users'] = users
|
|
res.send(JSON.stringify(resp_body))
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
res.send({status:'error'});
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
});
|
|
|
|
/** Hot reload support
|
|
* 1. Add url if in module.exports.processReq
|
|
* 2. Call function as module.exports.doTest
|
|
* 3. Add function as module.exports.doTest
|
|
*/
|
|
app.post('/api/*', async (req, res) => {await processModule.processReq(req, res);});
|
|
|
|
module.exports.processReq = async (req, res) =>{
|
|
if(!checkUserSession(req)){res.status(440).end();return;}
|
|
|
|
const url = req.url;
|
|
|
|
if(url == '/api/test'){
|
|
const rtn = await processModule.doTest();
|
|
if(rtn)res.send(JSON.stringify({status:'OK', msg:'Test OK-A'}));
|
|
else res.send(JSON.stringify({status:'error'}));
|
|
}
|
|
}
|
|
|
|
/** Process functions and DB access functions */
|
|
|
|
module.exports.doTest = async () => {
|
|
return true;
|
|
}
|
|
|
|
/** End of hot reload support */
|
|
|
|
/** DB access functions without hot reload support*/
|
|
async function connectDB(){
|
|
var rtn = null;
|
|
try {
|
|
rtn = await new Promise((resolve, reject) => {
|
|
mysql_pool.getConnection((error, connection) => {
|
|
if (error) reject(error)
|
|
resolve(connection)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('Connecting DB err: '+err)
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function getUserInfo(uid){
|
|
var rtn = null;
|
|
if(typeof uid != "number") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_name, account_email from accounts where account_id = ? and deleted is null ',
|
|
[uid]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
rtn = {uid: uid, email:results[0].account_email, name:results[0].account_name};
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
|
|
}
|
|
|
|
async function getRegUserBySession(session_id){
|
|
var rtn = null;
|
|
if(typeof session_id != "string") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select username from registration_sessions where session_id = ? ',
|
|
[session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
rtn = results[0].username;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function login(email, pwd) {
|
|
let rtn = null;
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_id, account_name from accounts where account_email = ? and account_pw = ? and deleted is null ',
|
|
[email, pwd]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
rtn = {id:results[0].account_id, name:results[0].account_name};
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function sendEmail(email, subject, message) {
|
|
let rtn=false;
|
|
const nodemailer = require('nodemailer');
|
|
const transporter = nodemailer.createTransport({
|
|
name: process.env.SMTP_HOST,
|
|
host: process.env.SMTP_HOST,
|
|
port: process.env.SMTP_PORT,
|
|
secure: true,
|
|
auth: {
|
|
user: process.env.SMTP_USER,
|
|
pass: process.env.SMTP_PASS
|
|
},
|
|
tls: {
|
|
rejectUnauthorized: false,
|
|
}
|
|
});
|
|
const mailOptions = {
|
|
from: process.env.SMTP_FROM,
|
|
to: email,
|
|
subject: subject,
|
|
html: message
|
|
};
|
|
const result = await transporter.sendMail(mailOptions);
|
|
if(result.response.startsWith('250')){
|
|
logger.info('Email sent: ' + result.response + '; email=' + mailOptions.to
|
|
+ '; subject=' + mailOptions.subject + '; message=' + mailOptions.text);
|
|
rtn = true;
|
|
}else{
|
|
logger.error(result.response);
|
|
rtn = false;
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
function makeMessage(msg_key, language, args = null) {
|
|
let msgMap = appMsgs.get(language);
|
|
if (!msgMap) msgMap = appMsgs.get('en-US');
|
|
msg = msgMap.get(msg_key);
|
|
if (args) {
|
|
for (let i = 0; i < args.length; i++) {
|
|
msg = msg.replaceAll('{' + i + '}', args[i]);
|
|
}
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
async function renameSpace(space_id, space_name, uid, language) {
|
|
if(typeof space_id != "string" || typeof space_name != "string" ||
|
|
typeof uid != "number" || typeof language != "string") return;
|
|
|
|
var rtn = null;
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select space_name from spaces where space_id = ? and deleted is null ',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
var jsonName={};
|
|
if(results.length > 0){
|
|
jsonName = JSON.parse(results[0].space_name);
|
|
}
|
|
jsonName[language] = space_name;
|
|
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spaces set space_name = ? where space_id = ? and deleted is null ',
|
|
[JSON.stringify(jsonName), space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
rtn = space_id;
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function checkSameSpaceName(uid, space_id, space_name){
|
|
if(typeof uid != "number" || typeof space_name != "string") return true;
|
|
var rtn = true;
|
|
const connection = await connectDB();;
|
|
try {
|
|
const txt = '%"'+space_name+'"%';
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
|
|
'where s.space_id=a.space_id and a.account_id = ? and s.space_id <> ? and s.space_name like ? and s.deleted is null and a.deleted is null ',
|
|
[uid, space_id, txt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0){
|
|
rtn = false;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = true;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function checkSameTmplName(template_id, template_name){
|
|
if(typeof template_name != "string") return true;
|
|
var rtn = true;
|
|
const connection = await connectDB();;
|
|
try {
|
|
const txt = '%"'+template_name+'"%';
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select spacetemp_id from space_templates where spacetemp_id<>? and spacetemp_name like ? and deleted is null ',
|
|
[template_id, txt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0){
|
|
rtn = false;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = true;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function checkSameInfoName(template_id, info_id, info_name){
|
|
if(typeof template_id != "string" || typeof info_name != "string") return true;
|
|
var rtn = true;
|
|
const connection = await connectDB();;
|
|
try {
|
|
const txt = '%"'+info_name+'"%';
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from spacetplt_info_blocks where spacetemp_id = ? and info_block_id<>? and block_name like ? and deleted is null ',
|
|
[template_id, info_id, txt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0){
|
|
rtn = false;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = true;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function inviteExistAccount(invite_session_id){
|
|
var rtn = {};
|
|
if(typeof invite_session_id != "string") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select n.user_email, a.account_id from invite_user_sessions n left join accounts a on a.account_email=n.user_email and a.deleted is null '+
|
|
'where n.session_id = ? ',
|
|
[invite_session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
if(results[0].account_id && results[0].account_id > 0)rtn["exist"] = true;
|
|
else{
|
|
rtn["exist"] = false;
|
|
rtn["email"] = results[0].user_email;
|
|
}
|
|
}else rtn = {};
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = {};
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function checkSameNickName(nickname){
|
|
if(typeof nickname != "string") return true;
|
|
var rtn = true;
|
|
const connection = await connectDB();
|
|
try {
|
|
const txt = '%"'+nickname+'"%';
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from accounts where account_name like ? and deleted is null ',
|
|
[txt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
});
|
|
});
|
|
if(results.length == 0){
|
|
rtn = false;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = true;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function setSpaceInfo(uid, space_id, info_id, info, language) {
|
|
if(typeof uid != "number" || typeof space_id != "string" || typeof info_id != "string"
|
|
|| typeof info != "string" || typeof language != "string") return;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from accspace_info where account_id = ? and space_id = ? and info_id=? and deleted is null ',
|
|
[uid, space_id, info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length == 0){
|
|
var json = {};
|
|
json[language] = info;
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('insert into accspace_info(account_id, space_id, info_id, info) values(?,?,?,?) ',
|
|
[uid, space_id, info_id, JSON.stringify(json)]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}else {
|
|
var json = JSON.parse(results[0].info);
|
|
json[language] = info;
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_info set info = ? where account_id = ? and space_id = ? and info_id=? and deleted is null ',
|
|
[JSON.stringify(json), uid, space_id, info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
function parseLangText(text, language){
|
|
try {
|
|
var json = JSON.parse(text);
|
|
if(typeof json!= "object") return text;
|
|
else if(json[language]!=null) return json[language];
|
|
else{
|
|
var rtn = json['en-US']
|
|
var keys = Object.keys(json);
|
|
if(!rtn && keys.length > 0)rtn = json[keys[0]];
|
|
return rtn?rtn:'';
|
|
}
|
|
} catch (e) {
|
|
return text;
|
|
}
|
|
}
|
|
|
|
async function changeRoleUser(mgr_id, space_id, uid, role) {
|
|
if(typeof mgr_id != "number" || typeof space_id != "number" || typeof uid != "number" || typeof role != "number") return false;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
|
|
[mgr_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0 || results[0].role != 1)return false;
|
|
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update space_users set role = ? where account_id = ? and space_id = ? and deleted is null ',
|
|
[role, uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
return true;
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return false;
|
|
}
|
|
|
|
async function getSpaceInfo(uid, space_id, info_id, language) {
|
|
if(typeof uid != "number" || typeof space_id != "string" || typeof info_id != "string" || typeof language != "string") return;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from accspace_info where account_id = ? and space_id = ? and info_id=? and deleted is null ',
|
|
[uid, space_id, info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length > 0){
|
|
return parseLangText(results[0].info, language);
|
|
}else return '';
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
async function makeRegSession(email) {
|
|
if(typeof email != "string") return null;
|
|
const session_id = uuidv4();
|
|
const connection = await connectDB();
|
|
try {
|
|
const result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('insert into registration_sessions(session_id, username) values(?,?) ',
|
|
[session_id, email]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
session_id = null;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return session_id;
|
|
}
|
|
|
|
async function setUser(reg_session_id, invt_session_id, user_name, password, language) {
|
|
if((invt_session_id &&typeof invt_session_id != "string") || (reg_session_id &&typeof reg_session_id != "string") ||
|
|
typeof user_name != "string" || typeof password != "string" || typeof language != "string") return null;
|
|
|
|
var rtn = -1;
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var sql;
|
|
if(reg_session_id)sql = SqlString.format('select username from registration_sessions where session_id = ? ', [reg_session_id]);
|
|
else if(invt_session_id)sql = SqlString.format('select user_email from invite_user_sessions where session_id = ? ', [invt_session_id]);
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(sql,
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
if(result.length > 0){
|
|
const email = result[0].username?result[0].username:result[0].user_email;
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_id from accounts where account_email = ? and deleted is null',
|
|
[email]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
const unmtxt = await makeJsonTxt(JSON.stringify({}), user_name, language);
|
|
if(result.length > 0){
|
|
rtn = result[0].account_id;
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('UPDATE accounts set account_email = ?, account_pw = ?, account_name = ? where account_id = ?',
|
|
[email, password, unmtxt, rtn]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}else{
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into accounts(account_email, account_pw, account_name) values(?,?,?) ',
|
|
[email, password, unmtxt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
rtn = result.insertId;
|
|
}
|
|
if(reg_session_id){
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('delete from registration_sessions where session_id = ? ',
|
|
[reg_session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
}
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function setSpaceUser(user_id, role, space_id){
|
|
rtn = -1;
|
|
if(typeof user_id != "number" || typeof role != "number" || typeof space_id != "number") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_id from space_users where account_id = ? and space_id =? and deleted is null',
|
|
[user_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length == 0){
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into space_users( account_id, role, space_id) values(?, ?, ?) ',
|
|
[user_id, role, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
rtn = results.insertId;
|
|
}else{
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update space_users set role = ? where account_id = ? and space_id = ? and deleted is null',
|
|
[role, user_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_id from account_permission where account_id = ? and space_id =? and deleted is null',
|
|
[user_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length == 0){
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into account_permission( account_id, space_id, is_admin ) values(?,?,?) ',
|
|
[user_id, space_id, 0]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function insertSpace(spacetemp_id, space_name, uid){
|
|
var rtn = null;
|
|
if(typeof spacetemp_id != "string" || typeof uid != "number" || typeof space_name != "object") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into spaces( spacetemp_id, space_name ) values(?,?) ',
|
|
[spacetemp_id, JSON.stringify(space_name)]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
const space_id = result.insertId
|
|
if(space_id > 0){
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into account_permission( account_id, space_id, is_admin ) values(?,?,?) ',
|
|
[uid, result.insertId, 1]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
rtn = space_id;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function insertAccountPrompt(uid, space_id, prompt, language){
|
|
if(typeof space_id != "string" || typeof uid != "number" || typeof prompt != "string"
|
|
|| typeof language != "string") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var json = {};
|
|
json[language] = prompt;
|
|
const txt = JSON.stringify(json);
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into accspace_prompts( account_id, space_id, prompt) values(?,?,?) ',
|
|
[uid, space_id, txt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
async function insertTemplateBasic(uid, name, cmt, language){
|
|
if(typeof name != "string" || typeof uid != "number" || typeof cmt != "string"
|
|
|| typeof language != "string") return rtn;
|
|
|
|
var rtn = -1;
|
|
const connection = await connectDB();
|
|
try {
|
|
var jsonName = {};
|
|
jsonName[language] = name;
|
|
var jsonCmt = {};
|
|
jsonCmt[language] = cmt;
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into space_templates( account_id, spacetemp_name, spacetemp_intro) values(?,?,?) ',
|
|
[uid, JSON.stringify(jsonName), JSON.stringify(jsonCmt)]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
if(result.insertId > 0){
|
|
rtn = result.insertId;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function updateTemplateBasic(uid, tmpl_id, name, cmt, language){
|
|
if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
|
|
|| typeof language != "string") return rtn;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select spacetemp_name, spacetemp_intro from space_templates where spacetemp_id = ? and deleted is null',
|
|
[tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
var jsonName = {}; var jsonCmt = {};
|
|
if(result.length > 0){
|
|
jsonName = JSON.parse(result[0].spacetemp_name);
|
|
jsonCmt = JSON.parse(result[0].spacetemp_intro);
|
|
}
|
|
jsonName[language] = name;
|
|
jsonCmt[language] = cmt;
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update space_templates set spacetemp_name = ?, spacetemp_intro = ? where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[JSON.stringify(jsonName), JSON.stringify(jsonCmt), uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
async function unassignSpaceUser(mgr_id, space_id, user_id){
|
|
var rtn = false;
|
|
if(typeof mgr_id != "number" || typeof space_id != "number" || typeof user_id != "number") return rtn;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
|
|
[mgr_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0 || results[0].role != 1)return false;
|
|
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update space_users set deleted = now() where account_id = ? and space_id = ? and deleted is null',
|
|
[user_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update account_permission set deleted = now() where account_id = ? and space_id = ? and deleted is null',
|
|
[user_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
rtn = true;
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function updateTemplatePublic(uid,tmpl_id, is_public){
|
|
if(typeof uid != "number" || typeof is_public != "number" || typeof tmpl_id != "string") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update space_templates set shared = ? where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[is_public, uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
async function deleteTemplateInfo(uid, tmpl_id, info_id){
|
|
if(typeof uid != "number" || typeof tmpl_id != "string" || typeof info_id != "string") return false;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return false;
|
|
}
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spacetplt_info_blocks set deleted = now() where info_block_id = ? and deleted is null',
|
|
[info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
return false;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return true;
|
|
}
|
|
|
|
async function deleteTemplatePrompt(uid, tmpl_id, prompt_id){
|
|
if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_id != "string") return false;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return false;
|
|
}
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spacetplt_prompts set deleted = now() where prompt_id = ? and deleted is null',
|
|
[prompt_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
return false;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return true;
|
|
|
|
}
|
|
|
|
async function deleteSpace(uid, space_id){
|
|
if(typeof uid != "number" || typeof space_id != "string") return false;
|
|
|
|
var rtn = false;
|
|
const connection = await connectDB();;
|
|
|
|
try {
|
|
await new Promise((resolve, reject) => {
|
|
connection.beginTransaction();
|
|
resolve();
|
|
});
|
|
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
|
|
'where s.space_id=a.space_id and a.account_id = ? and s.space_id = ? and s.deleted is null and a.deleted is null ',
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length == 0){
|
|
rtn = false;
|
|
}
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spaces set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
result= await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update account_permission set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
result= await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_prompts set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_info set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_sessions set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_history set deleted = now() where space_id = ? and deleted is null',
|
|
[space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
await new Promise((resolve, reject) => {
|
|
connection.commit();
|
|
resolve();
|
|
});
|
|
rtn = true;
|
|
}catch (err) {
|
|
await connection.rollback();
|
|
logger.error('DB err: '+err)
|
|
rtn = false;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
|
|
async function insertTemplateInfo(uid, tmpl_id, name, cmt, language){
|
|
var rtn = -1;
|
|
|
|
if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
|
|
|| typeof language != "string") return rtn;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
//Only the creator can update the template
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return rtn;
|
|
}
|
|
|
|
var jsonName = {};
|
|
jsonName[language] = name;
|
|
var cmttxt = '';
|
|
if(cmt && cmt.length > 0){
|
|
var jsonCmt = {};
|
|
jsonCmt[language] = cmt;
|
|
cmttxt = JSON.stringify(jsonCmt);
|
|
}
|
|
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into spacetplt_info_blocks( spacetemp_id, block_name, block_cmmt) values(?,?,?) ',
|
|
[tmpl_id, JSON.stringify(jsonName), cmttxt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
|
|
if(result.insertId > 0){
|
|
rtn = result.insertId;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = -1;
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function insertTemplatePrompt(uid, tmpl_id, prompt_hint, prompt_head, prompt_show, prompt_foot, language){
|
|
var rtn = -1;
|
|
if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_hint != "string" || typeof prompt_head != "string"
|
|
|| typeof prompt_show != "string" || typeof prompt_foot != "string" || typeof language != "string") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
try {
|
|
//Only the creator can update the template
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return rtn;
|
|
}
|
|
|
|
const hint = await makeJsonTxt(JSON.stringify({}), prompt_hint, language);
|
|
const head = await makeJsonTxt(JSON.stringify({}), prompt_head, language);
|
|
const show = await makeJsonTxt(JSON.stringify({}), prompt_show, language);
|
|
const foot = await makeJsonTxt(JSON.stringify({}), prompt_foot, language);
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into spacetplt_prompts(spacetemp_id, prompt_hint, prompt_head, prompt_show, prompt_foot) values(?,?,?,?,?) ',
|
|
[tmpl_id, hint, head, show, foot]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.insertId > 0){
|
|
rtn = result.insertId;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = -1;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function updateTemplateInfo(uid, tmpl_id, info_id, name, cmt, language){
|
|
if(typeof uid != "number" || typeof name != "string" || typeof tmpl_id != "string" || typeof cmt != "string"
|
|
|| typeof language != "string") return rtn;
|
|
|
|
var rtn = -1;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
//Only the creator can update the template
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return rtn;
|
|
}
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select block_name, block_cmmt from spacetplt_info_blocks where info_block_id = ? and deleted is null',
|
|
[info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
var jsonName = {}; var jsonCmt = {};
|
|
if(result.length > 0){
|
|
jsonName = JSON.parse(result[0].block_name);
|
|
if(result[0].block_cmmt&&result[0].block_cmmt.length>0)jsonCmt = JSON.parse(result[0].block_cmmt);
|
|
}
|
|
jsonName[language] = name;
|
|
jsonCmt[language] = cmt;
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spacetplt_info_blocks set block_name = ?, block_cmmt = ? where info_block_id = ? and deleted is null',
|
|
[JSON.stringify(jsonName), JSON.stringify(jsonCmt) , info_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
}
|
|
|
|
async function updateTemplatePrompt(uid, tmpl_id, prompt_id, prompt_hint, prompt_head, prompt_show, prompt_foot, language){
|
|
var rtn = false;
|
|
if(typeof uid != "number" || typeof tmpl_id != "string" || typeof prompt_hint != "string" || typeof prompt_head != "string"
|
|
|| typeof prompt_show != "string" || typeof prompt_foot != "string" || typeof language != "string") return rtn;
|
|
|
|
const connection = await connectDB();;
|
|
try {
|
|
//Only the creator can update the template
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, tmpl_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return rtn;
|
|
}
|
|
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select prompt_hint, prompt_head, prompt_show, prompt_foot from spacetplt_prompts '+
|
|
'where prompt_id = ? and deleted is null',
|
|
[prompt_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
var hint = '', head = '', show = '', foot = '';
|
|
if(result.length > 0){
|
|
hint = result[0].prompt_hint;
|
|
head = result[0].prompt_head;
|
|
show = result[0].prompt_show;
|
|
foot = result[0].prompt_foot;
|
|
}
|
|
|
|
hint = await makeJsonTxt(hint, prompt_hint, language);
|
|
head = await makeJsonTxt(head, prompt_head, language);
|
|
show = await makeJsonTxt(show, prompt_show, language);
|
|
foot = await makeJsonTxt(foot, prompt_foot, language);
|
|
result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update spacetplt_prompts set prompt_hint = ?, prompt_head = ?, prompt_show = ?, prompt_foot = ? where prompt_id = ? and deleted is null',
|
|
[hint, head, show, foot, prompt_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
rtn = true;
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = false;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function insertAccountHistory(uid, space_id, accspc_session_id, prompt, answer, language){
|
|
if(typeof space_id != "string" || typeof uid != "number" || typeof accspc_session_id != "string"
|
|
|| typeof prompt != "string" || typeof answer != "string" || typeof language != "string") return;
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var json = {};
|
|
json[language] = prompt;
|
|
const txt = JSON.stringify(json);
|
|
json = {};
|
|
json[language] = answer;
|
|
const answerTxt = JSON.stringify(json);
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into accspace_history( account_id, space_id, accspace_sessions_id, prompt, answer) values(?,?,?,?,?) ',
|
|
[uid, space_id, accspc_session_id, txt, answerTxt]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.insertId > 0){
|
|
//update updated column of accspace_sessions
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('update accspace_sessions set updated = now() where accspace_sessions_id = ? and deleted is null',
|
|
[accspc_session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return;
|
|
}
|
|
|
|
async function insertAccSpaceSession(uid, space_id, prompt, language){
|
|
if(typeof space_id != "string" || typeof uid != "number" || typeof prompt != "string" || typeof language != "string") return rtn;
|
|
|
|
accspace_sessions_id = -1;
|
|
|
|
const connection = await connectDB();
|
|
var txtName;
|
|
try {
|
|
var json = {};
|
|
json[language] = prompt.substring(0, 30);
|
|
txtName = JSON.stringify(json);
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into accspace_sessions( account_id, space_id, short_name) values(?,?,?) ',
|
|
[uid, space_id, txtName]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
accspace_sessions_id = result.insertId;
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return {session_id:accspace_sessions_id, short_name:txtName};
|
|
}
|
|
|
|
async function processInviteMgrSession(invite_session_id){
|
|
if(typeof invite_session_id != "string") return;
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select user_email, role, sender_id, space_id from invite_user_sessions where session_id = ? ',
|
|
[invite_session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
const sender_id = results[0].sender_id
|
|
const role = results[0].role
|
|
const user_email = results[0].user_email
|
|
const space_id = results[0].space_id
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select spcuser_id, role from space_users where account_id = ? and space_id = ? and deleted is null order by created limit 1',
|
|
[sender_id, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length > 0){
|
|
if(results[0].role == 1)spcuser_id = results[0].spcuser_id
|
|
else return null
|
|
}else{
|
|
spcuser_id = uuidv4()
|
|
if(0 > setSpaceUser(sender_id, 1, space_id)) return null;//Sender must be mgr
|
|
}
|
|
|
|
const new_uid = await findAccountID(user_email)
|
|
if(new_uid < 0) return null
|
|
|
|
if(0 > setSpaceUser(new_uid, role, space_id))return null;
|
|
|
|
await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('delete from invite_user_sessions where session_id = ?',
|
|
[invite_session_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
return user_email
|
|
}else return null
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
}
|
|
|
|
async function findAccountID(email){
|
|
if(typeof email != "string") return;
|
|
var rtn = -1;
|
|
const connection = await connectDB();
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select account_id from accounts where account_email = ? and deleted is null ',
|
|
[email]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
if(results.length > 0){
|
|
rtn = results[0].account_id;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function listSpaceNames(uid, type=0){//0: all related, 1: include managed, 2: created only
|
|
if(typeof uid != "number") return;
|
|
|
|
var rtn = [];
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select s.space_id, s.space_name from account_permission a, spaces s where a.account_id = ? and a.space_id=s.space_id '+
|
|
' and is_admin=1 and a.deleted is null and s.deleted is null order by s.created desc ',
|
|
[uid]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
const rowJson = {};
|
|
rowJson['space_id'] = row.space_id;
|
|
rowJson['space_name'] = row.space_name;
|
|
rtn.push(rowJson);
|
|
}
|
|
|
|
if(type == 2) return rtn;
|
|
|
|
var role_part;
|
|
switch(type){
|
|
case 1:
|
|
role_part = ' role = 1 ';
|
|
break;
|
|
default:
|
|
role_part = ' 1=1 ';
|
|
break;
|
|
}
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select s.space_id, s.space_name from spaces s, space_users u '+
|
|
'where s.space_id=u.space_id and '+ role_part +' and u.account_id = ? and s.deleted is null and u.deleted is null order by s.created desc ',
|
|
[uid]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
if(rtn.findIndex(x => x.space_id == row.space_id) < 0){
|
|
const rowJson = {};
|
|
rowJson['space_id'] = row.space_id;
|
|
rowJson['space_name'] = row.space_name;
|
|
rtn.push(rowJson);
|
|
}
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function listTmplNames(uid=-1){
|
|
if(typeof uid != "number") return;
|
|
|
|
var rtn = [];
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select spacetemp_id, spacetemp_name from space_templates where deleted is null and '+
|
|
(uid>=0?' account_id = ? ':' 0 <= ? ')+' order by created desc ',
|
|
[uid]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
const rowJson = {};
|
|
rowJson['tmpl_id'] = row.spacetemp_id;
|
|
rowJson['tmpl_name'] = row.spacetemp_name;
|
|
rtn.push(rowJson);
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function listSpaceUsers(uid){
|
|
if(typeof uid != "number") return;
|
|
|
|
var rtn = [];
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var sql = 'select s.space_id, s.space_name, u.role, u.account_id, a.account_name, a.account_email '+
|
|
'from space_users u, spaces s, accounts a '+
|
|
'where u.space_id=s.space_id and u.account_id=a.account_id and u.space_id in (select space_id from space_users where account_id=? and deleted is null) '+
|
|
'and u.deleted is null and s.deleted is null and a.deleted is null '+
|
|
'order by s.space_id, s.created desc ';
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format(sql,
|
|
[uid]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
//Same space id users into one JSON arry
|
|
var cur_space_id = -1;
|
|
for (const row of results) {
|
|
if(cur_space_id != row.space_id){
|
|
if(cur_space_id >= 0){
|
|
rtn.push(spaceJson);
|
|
}
|
|
cur_space_id = row.space_id;
|
|
var spaceJson = {};
|
|
spaceJson['space_id'] = row.space_id;
|
|
spaceJson['space_name'] = row.space_name;
|
|
spaceJson['users'] = [];
|
|
}
|
|
const userJson = {};
|
|
userJson['account_id'] = row.account_id;
|
|
userJson['account_name'] = row.account_name;
|
|
userJson['account_email'] = row.account_email;
|
|
userJson['role'] = row.role;
|
|
spaceJson['users'].push(userJson);
|
|
}
|
|
if(cur_space_id >= 0){
|
|
rtn.push(spaceJson);
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
|
|
}
|
|
|
|
async function listAccSpcSessions(uid, space_id){
|
|
if(typeof uid != "number" || typeof space_id != "string") return;
|
|
|
|
var rtn = [];
|
|
const connection = await connectDB();;
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select accspace_sessions_id, short_name from accspace_sessions where deleted is null and '+
|
|
'account_id = ? and space_id = ? order by created desc limit 30',//TODO: paging
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
const rowJson = {};
|
|
rowJson['id'] = row.accspace_sessions_id;
|
|
rowJson['name'] = row.short_name;
|
|
rtn.push(rowJson);
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
|
|
}
|
|
|
|
async function getSpaceShow(space_id, uid, language){
|
|
if(typeof space_id != "string" || typeof uid != "number" || typeof language != "string") return;
|
|
|
|
var rtn = null;
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select s.space_id, s.space_name, a.permission, a.is_admin, t.spacetemp_id '+
|
|
' from account_permission a, spaces s, space_templates t '+
|
|
' where a.account_id = ? and s.space_id=? and a.space_id=s.space_id and s.spacetemp_id=t.spacetemp_id '+
|
|
' and a.deleted is null and s.deleted is null and t.deleted is null '+
|
|
' order by s.created desc ',
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length > 0){
|
|
const rowJson = {};
|
|
rowJson['space_id'] = results[0].space_id;
|
|
rowJson['space_name'] = parseLangText(results[0].space_name, language);
|
|
rowJson['is_admin'] = results[0].is_admin;
|
|
rowJson['permission'] = results[0].permission;
|
|
|
|
const template_id = results[0].spacetemp_id;
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select `info_block_id`, `block_name`, `block_cmmt` from spacetplt_info_blocks '+
|
|
' where spacetemp_id = ? and deleted is null '+
|
|
' order by info_block_id ',
|
|
[template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const blocks = [];
|
|
for (const row of results) {
|
|
const blockJson = {};
|
|
blockJson['id'] = row.info_block_id;
|
|
blockJson['name'] = parseLangText(row.block_name, language);
|
|
blockJson['cmmt'] = row.block_cmmt?parseLangText(row.block_cmmt, language):'';
|
|
blocks.push(blockJson);
|
|
}
|
|
rowJson['blocks'] = blocks;
|
|
}
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select `prompt_id`, `prompt_hint`, `prompt_show` from spacetplt_prompts '+
|
|
' where spacetemp_id = ? and deleted is null '+
|
|
' order by prompt_id ',
|
|
[template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const prompts = [];
|
|
for (const row of results) {
|
|
const promptJson = {};
|
|
promptJson['prompt_id'] = row.prompt_id;
|
|
// no head for show: promptJson['prompt_head'] = row.prompt_head?parseLangText(row.prompt_head, language):'';
|
|
promptJson['prompt_hint'] = row.prompt_hint?parseLangText(row.prompt_hint, language):'';
|
|
promptJson['prompt_show'] = row.prompt_show?parseLangText(row.prompt_show, language):'';
|
|
// no foot for show: promptJson['prompt_foot'] = row.prompt_foot?parseLangText(row.prompt_foot, language):'';
|
|
prompts.push(promptJson);
|
|
}
|
|
rowJson['prompts'] = prompts;
|
|
}
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select `prompt` from accspace_prompts where account_id = ? and space_id = ? and deleted is null '+
|
|
' order by created desc limit 100', //TODO: paging
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const prompts = [];
|
|
for (const row of results) {
|
|
prompts.push(row.prompt?parseLangText(row.prompt, language):'');
|
|
}
|
|
rowJson['my_prompts'] = prompts;
|
|
}
|
|
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select accspace_sessions_id, short_name from accspace_sessions where deleted is null and '+
|
|
'account_id = ? and space_id = ? order by updated desc limit 30',//TODO: paging
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length > 0){
|
|
const convs = [];
|
|
for (const row of results) {
|
|
const historyJson = {};
|
|
historyJson['id'] = row.accspace_sessions_id;
|
|
historyJson['name'] = row.short_name;
|
|
convs.push(historyJson);
|
|
}
|
|
rowJson['convs'] = convs;
|
|
}
|
|
|
|
rtn = rowJson;
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
module.exports.listHistory = async (uid, space_id, accspc_session_id, language, limit=20) => {
|
|
if(typeof uid != "number" || typeof space_id != "string" || typeof accspc_session_id != "string" || typeof language != "string") return;
|
|
|
|
var rtn = [];
|
|
const connection = await connectDB();;
|
|
try{
|
|
const results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select prompt, answer, created from accspace_history where account_id = ? and space_id = ? and accspace_sessions_id=? and deleted is null '+
|
|
' order by created desc limit ?', //TODO: paging
|
|
[uid, space_id, accspc_session_id, limit]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const history = [];
|
|
for (const row of results) {
|
|
const askJson = {};
|
|
askJson['prompt'] = row.prompt?parseLangText(row.prompt, language):'';
|
|
askJson['answer'] = row.answer?parseLangText(row.answer, language):'';
|
|
//Format date
|
|
askJson['created'] = date_format.asString('yyyy-MM-dd hh:mm:ss', row.created);
|
|
history.push(askJson);
|
|
}
|
|
rtn = history;
|
|
}
|
|
}catch (err) {
|
|
logger.error('Sys err: '+err)
|
|
rtn = [];
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
|
|
return rtn;
|
|
}
|
|
|
|
async function listSpaceTemplates(uid, type, page, per_page){
|
|
var rtn = [];
|
|
|
|
if(typeof type != "string" || typeof page != "number" || typeof per_page != "number") return rtn;
|
|
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
if(page < 1) page = 1;
|
|
if(per_page < 1) per_page = 10;
|
|
else if(per_page > 100) per_page = 100;
|
|
var sql;
|
|
if(type == 'myown'){
|
|
sql = SqlString.format('select t.*, a.account_email, a.account_name from space_templates t, accounts a where t.account_id=a.account_id and '+
|
|
' a.deleted is null and t.deleted is null and t.account_id=? order by created desc limit ? offset ?',
|
|
[uid, per_page, (page-1)*per_page]);
|
|
}else{//public
|
|
sql = SqlString.format('select t.*, a.account_email, a.account_name from space_templates t, accounts a where t.account_id=a.account_id and '+
|
|
' a.deleted is null and t.deleted is null and shared=1 order by created desc limit ? offset ?',
|
|
[per_page, (page-1)*per_page]);
|
|
}
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(sql,
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
const rowJson = {};
|
|
rowJson['spacetemp_id'] = row.spacetemp_id;
|
|
rowJson['tenmplate'] = row.tenmplate;
|
|
rowJson['spacetemp_name'] = row.spacetemp_name;
|
|
rowJson['spacetemp_intro'] = row.spacetemp_intro;
|
|
rowJson['creator_name'] = (row.account_name && 0<row.account_name.length)?row.account_name:row.account_email;
|
|
rtn.push(rowJson);
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = [];
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function getTemplateBasic(uid, template_id, language){
|
|
if(typeof uid != "number" || typeof template_id != "string" || typeof language != "string") return;
|
|
|
|
var rtn = null;
|
|
const connection = await connectDB();;
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select spacetemp_id, spacetemp_name, spacetemp_intro, shared from space_templates where account_id = ? and spacetemp_id=? and deleted is null ',
|
|
[uid, template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const row = results[0];
|
|
rtn = {};
|
|
rtn['tmpl_id'] = row.spacetemp_id;
|
|
rtn['tmpl_name'] = parseLangText(row.spacetemp_name, language);
|
|
rtn['tmpl_cmt'] = parseLangText(row.spacetemp_intro, language);
|
|
rtn['is_public'] = row.shared;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
}
|
|
|
|
async function getTemplateOther(uid, template_id, language){
|
|
if(typeof uid != "number" || typeof template_id != "string" || typeof language != "string") return;
|
|
|
|
var rtn = {};
|
|
const connection = await connectDB();;
|
|
try {
|
|
//Only the creator can check the template
|
|
var result = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select * from space_templates where account_id = ? and spacetemp_id = ? and deleted is null',
|
|
[uid, template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(result.length == 0){
|
|
return rtn;
|
|
}
|
|
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select info_block_id, block_name, block_cmmt from spacetplt_info_blocks where spacetemp_id = ? and deleted is null ',
|
|
[template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const blocks = [];
|
|
for (const row of results) {
|
|
const blockJson = {};
|
|
blockJson['id'] = row.info_block_id;
|
|
blockJson['name'] = parseLangText(row.block_name, language);
|
|
blockJson['cmmt'] = row.block_cmmt?parseLangText(row.block_cmmt, language):'';
|
|
blocks.push(blockJson);
|
|
}
|
|
rtn['info_blocks'] = blocks;
|
|
}
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select prompt_id, prompt_hint, prompt_show, prompt_head, prompt_foot from spacetplt_prompts where spacetemp_id = ? and deleted is null ',
|
|
[template_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
const prompts = [];
|
|
for (const row of results) {
|
|
const promptJson = {};
|
|
promptJson['id'] = row.prompt_id;
|
|
promptJson['hint'] = row.prompt_hint?parseLangText(row.prompt_hint, language):'';
|
|
promptJson['show'] = row.prompt_show?parseLangText(row.prompt_show, language):'';
|
|
promptJson['head'] = row.prompt_head?parseLangText(row.prompt_head, language):'';
|
|
promptJson['foot'] = row.prompt_foot?parseLangText(row.prompt_foot, language):'';
|
|
prompts.push(promptJson);
|
|
}
|
|
rtn['prompts'] = prompts;
|
|
}
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = {};
|
|
}finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn;
|
|
|
|
}
|
|
|
|
async function getInfoBlocksContent(uid, space_id, language){
|
|
var rtn = [];
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select t.block_name, a.info from spacetplt_info_blocks t, accspace_info a where t.info_block_id=a.info_id and '+
|
|
' a.account_id=? and a.space_id=? and a.deleted is null and t.deleted is null order by a.created ',
|
|
[uid, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
for (const row of results) {
|
|
const rowJson = {};
|
|
rowJson['block_name'] = parseLangText(row.block_name, language);
|
|
rowJson['info'] = parseLangText(row.info, language);
|
|
rtn.push(rowJson);
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = [];
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function getPromptHeadFoot(advice_prompt_id, language){
|
|
var rtn = null;
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(
|
|
SqlString.format('select `prompt_head`, `prompt_foot` from spacetplt_prompts where prompt_id=? and '+
|
|
' deleted is null ',
|
|
[advice_prompt_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
if(results.length > 0){
|
|
rtn = {};
|
|
const row = results[0];
|
|
rtn['head'] = row.prompt_head && 0<row.prompt_head.length?parseLangText(row.prompt_head, language):'';
|
|
rtn['foot'] = row.prompt_foot && 0<row.prompt_foot.length?parseLangText(row.prompt_foot, language):'';
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = null;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function isManager(uid, spcae_id){
|
|
if(typeof uid != "number" || typeof spcae_id != "string") return;
|
|
|
|
var rtn = false;
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
var results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select role from space_users where account_id = ? and space_id = ? and deleted is null ',
|
|
[uid, spcae_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
if(results.length > 0){
|
|
rtn = results[0].role == 1;
|
|
}else{
|
|
//Check if the user is the creator of the space
|
|
results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('select s.space_id from spaces s, account_permission a '+
|
|
'where s.space_id=a.space_id and a.account_id = ? and s.space_id = ? and s.deleted is null and a.deleted is null ',
|
|
[uid, spcae_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
});
|
|
rtn = results.length > 0;
|
|
}
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
rtn = false;
|
|
}
|
|
finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return rtn
|
|
}
|
|
|
|
async function generateInviteUserSession(sender_uid, new_user_email, space_id, role){
|
|
if(typeof space_id != "string" || typeof new_user_email != "string" || typeof role != "number"
|
|
|| typeof sender_uid != "number" ) return;
|
|
|
|
var session_id = null;
|
|
const connection = await connectDB();
|
|
|
|
try {
|
|
session_id = uuidv4()
|
|
|
|
const results = await new Promise((resolve, reject) => {
|
|
connection.query(SqlString.format('INSERT into invite_user_sessions( session_id, sender_id, user_email, role, space_id ) values(?,?,?,?,?) ',
|
|
[session_id, sender_uid, new_user_email, role, space_id]),
|
|
(error, results) => {
|
|
if (error) reject(error)
|
|
resolve(results)
|
|
})
|
|
})
|
|
|
|
}catch (err) {
|
|
logger.error('DB err: '+err)
|
|
session_id = null
|
|
} finally {
|
|
if(connection)connection.release()
|
|
}
|
|
return session_id
|
|
}
|
|
|
|
async function askOpenAPI(req, uid, space_id, accspace_sessions_id, question, advice_prompt_id=null, response, language){
|
|
if(typeof uid != "number" || typeof space_id != "string" || typeof accspace_sessions_id !="string" || typeof language != "string") return;
|
|
|
|
var rtn = null;
|
|
|
|
const assistant = await openai.beta.assistants.create({
|
|
model: "gpt-4o"
|
|
});
|
|
const thread = await openai.beta.threads.create();
|
|
|
|
const histories = await processModule.listHistory(uid, space_id, accspace_sessions_id, language, 10);
|
|
histories.reverse();
|
|
var text_len = 0;
|
|
for(const history of histories){
|
|
if(text_len > 6000) break;
|
|
text_len += history.prompt.length + history.answer.length;
|
|
await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "user",
|
|
content: history.prompt
|
|
}
|
|
);
|
|
await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "assistant",
|
|
content: history.answer
|
|
}
|
|
);
|
|
}
|
|
|
|
const infos =await getInfoBlocksContent(uid, space_id, language);
|
|
if(infos){
|
|
for(const info of infos){
|
|
const message = await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "user",
|
|
content: '#' + info.block_name + ':' + info.info
|
|
}
|
|
);
|
|
}
|
|
}
|
|
|
|
var promptFoot = null;
|
|
if(advice_prompt_id){
|
|
const promptHeadFoot = await getPromptHeadFoot(advice_prompt_id, language);
|
|
if(promptHeadFoot['head']){
|
|
const message = await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "user",
|
|
content: promptHeadFoot['head']
|
|
}
|
|
);
|
|
}
|
|
promptFoot = promptHeadFoot['foot'];
|
|
}
|
|
await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "user",
|
|
content: question
|
|
}
|
|
);
|
|
|
|
if(promptFoot){
|
|
const message = await openai.beta.threads.messages.create(
|
|
thread.id,
|
|
{
|
|
role: "user",
|
|
content: promptFoot
|
|
}
|
|
);
|
|
}
|
|
|
|
const cur_stream = await openai.beta.threads.runs.create(
|
|
thread.id,
|
|
{assistant_id: assistant.id, stream: true}
|
|
);
|
|
|
|
var answer = '';
|
|
for await (const event of cur_stream ) {
|
|
const stop_time = session_time.get(req.session.uid+"_stop_time")??0;
|
|
const ask_time = session_time.get(req.session.uid+"_ask_time")??0;
|
|
if(stop_time >= ask_time){
|
|
cur_stream.controller.abort();
|
|
//req.session.abort_stream = 0;
|
|
break;
|
|
}
|
|
/*else{
|
|
logger.debug('session time:'+stop_time + ' ' + ask_time);
|
|
}*/
|
|
if (event.event === 'thread.message.delta') {
|
|
const chunk = event.data.delta.content?.[0];
|
|
if (chunk && chunk.type === 'text') {
|
|
const txt=chunk.text?.value ?? '';
|
|
//logger.debug(txt);
|
|
answer += txt;
|
|
response.write(txt);
|
|
}
|
|
}
|
|
}
|
|
|
|
insertAccountHistory(uid, space_id, accspace_sessions_id, question, answer, language);
|
|
|
|
response.end();
|
|
|
|
}
|
|
|
|
async function makeJsonTxt(json_txt, txt, language){
|
|
var json = await JSON.parse(json_txt);
|
|
if(!json || typeof json!= "object") json = {};
|
|
json[language] = txt;
|
|
return JSON.stringify(json);
|
|
}
|
|
|
|
async function loadJsonBody(request, log=true){
|
|
const buffers = [];
|
|
|
|
for await (const chunk of request) {
|
|
buffers.push(chunk);
|
|
}
|
|
|
|
const bodytxt = Buffer.concat(buffers).toString();
|
|
|
|
const body = JSON.parse(bodytxt);
|
|
if(log)logger.debug(body);
|
|
|
|
return body;
|
|
}
|
|
|
|
const appMsgs = new Map();
|
|
const appMsgLanguages = {
|
|
english: 'en-US',
|
|
japanese: 'ja',
|
|
chinese_cn: 'zh-CN',
|
|
//chinese_tw: 'zh-TW',
|
|
}
|
|
appMsgs.set(appMsgLanguages.english, new Map());
|
|
appMsgs.set(appMsgLanguages.japanese, new Map());
|
|
appMsgs.set(appMsgLanguages.chinese_cn, new Map());
|
|
|
|
appMsgs.get(appMsgLanguages.chinese_cn).set('reg_email_temp', '<br>感谢您注册SisAi World服务。'+
|
|
'<br><br>请在{0}分钟内点击下面的链接完成注册。<br><br><a href="{1}">{1}</a><br><br>SisAi支持团队');
|
|
appMsgs.get(appMsgLanguages.english).set('reg_email_temp', '<br>Thank you for registering to SisAi World.'+
|
|
'<br><br>Click the link below to complete your registration in {0} minutes.<br><br><a href="{1}">{1}</a><br><br>Thank you,<br>SisAi support team');
|
|
appMsgs.get(appMsgLanguages.japanese).set('reg_email_temp', '<br>SisAi Worldへの登録ありがとうございます。'+
|
|
'<br><br>以下のリンクを{0}分以内にクリックして登録を完了してください。<br><br><a href="{1}">{1}</a><br><br>SisAi サポートチーム');
|
|
|
|
appMsgs.get(appMsgLanguages.chinese_cn).set('invt_email_temp', '<br>感谢您接受SisAi World服务邀请。'+
|
|
'<br><br>请在{0}分钟内点击下面的链接接受邀请。<br><br><a href="{1}">{1}</a><br><br>SisAi支持团队');
|
|
appMsgs.get(appMsgLanguages.english).set('invt_email_temp', '<br>Thank you for accepting the SisAi World service invitation.'+
|
|
'<br><br>Click the link below to accept the invitation in {0} minutes.<br><br><a href="{1}">{1}</a><br><br>Thank you,<br>SisAi support team');
|
|
appMsgs.get(appMsgLanguages.japanese).set('invt_email_temp', '<br>SisAi Worldサービスの招待を受け入れていただきありがとうございます。'+
|
|
'<br><br>以下のリンクを{0}分以内にクリックして招待を受け入れてください。<br><br><a href="{1}">{1}</a><br><br>SisAi サポートチーム');
|
|
|
|
|
|
appMsgs.get(appMsgLanguages.english).set('reg_email_subject', 'SisAi World free account registration');
|
|
appMsgs.get(appMsgLanguages.japanese).set('reg_email_subject', 'SisAi World無料アカウント登録');
|
|
appMsgs.get(appMsgLanguages.chinese_cn).set('reg_email_subject', 'SisAi World免费帐户注册');
|
|
|
|
appMsgs.get(appMsgLanguages.english).set('invt_email_subject', 'SisAi World service invitation');
|
|
appMsgs.get(appMsgLanguages.japanese).set('invt_email_subject', 'SisAi Worldサービス招待');
|
|
appMsgs.get(appMsgLanguages.chinese_cn).set('invt_email_subject', 'SisAi World服务邀请');
|