exports.setCalendarTokenForUser = function (meetingId, email, token) {
var query = squel.update()
.table("Sessions")
.set("google_cal_token", token)
.where("session_id = ?", meetingId)
.where("email = ?", email)
.toParam();
return new Promise(function (resolve, reject) {
dbQuery(query.text, query.values)
.then(function (data) {
if (data.affectedRows > 0) {
resolve();
} else {
reject();
}
})
.catch(reject);
});
};
function update(request, reply){
let data = request.payload;
data.Id = data.id;
data.EndDate = moment(data.EndDate).format('YYYY-MM-DD HH:mm:ss');
delete data.id;
delete data.oper;
let updateSql = sql.update()
.table('Items')
.where('Id = ?', data.Id)
.setFields(data)
.toParam();
db.open().then(
function(ctx) {
ctx.query('START TRANSACTION').then(function(trans){
ctx.query(updateSql.text, updateSql.values).then(
function(result) {
//update complete, so commit and call it a day.
ctx.query('COMMIT').then(function(end){
ctx.end();
debugger;
//save worked!
reply(result);
});
}
)
.catch(function(err) {
//update failed!
ctx.query('ROLLBACK').then(function(end){
ctx.end();
reply({
error: err
});
});
});
});
});
}
controller.registerTrigger("serverCommunication::websocket::ichequeUpdate", "icheques::pushUpdate", function(data, callback) {
callback();
var dbResponse = controller.database.exec(squel
.select()
.from("ICHEQUES_CHECKS")
.where("PUSH_ID = ?", data.pushId).toString());
if (!dbResponse[0].values[0]) {
controller.call("icheques::insertDatabase", data);
return;
}
controller.database.exec(squel
.update()
.table("ICHEQUES_CHECKS")
.where("PUSH_ID = ?", data.pushId)
.setFields(controller.call("icheques::databaseObject", data)).toString());
controller.call("icheques::item::upgrade", data);
});
router.post('/setup', async (ctx, next) => {
const { name, username, newPassword, newPasswordConfirm } = ctx.request.body
let roomId
// must be first run
const prefs = await Prefs.get()
if (prefs.isFirstRun !== true) {
ctx.throw(403)
}
// check presence of all fields
if (!name || !username || !newPassword || !newPasswordConfirm) {
ctx.throw(422, 'All fields are required')
}
// check that passwords match
if (newPassword !== newPasswordConfirm) {
ctx.throw(422, 'Passwords do not match')
}
// create admin user
{
const q = squel.insert()
.into('users')
.set('username', username.trim())
.set('password', await bcrypt.hash(newPassword, 12))
.set('name', name.trim())
.set('isAdmin', 1)
.set('dateCreated', Math.floor(Date.now() / 1000))
const { text, values } = q.toParam()
await db.run(text, values)
}
// create default room
{
const q = squel.insert()
.into('rooms')
.set('name', 'Room 1')
.set('status', 'open')
.set('dateCreated', Math.floor(Date.now() / 1000))
const { text, values } = q.toParam()
const res = await db.run(text, values)
// sign in to room
roomId = res.stmt.lastID
}
// unset isFirstRun
{
const q = squel.update()
.table('prefs')
.where('key = ?', 'isFirstRun')
.set('data', squel.select().field(`json('false')`))
const { text, values } = q.toParam()
await db.run(text, values)
}
await _login(ctx, { username, password: newPassword, roomId })
})
router.put('/account', async (ctx, next) => {
const user = await User.getById(ctx.user.userId, true)
if (!user) {
ctx.throw(401)
}
let { name, username, password, newPassword, newPasswordConfirm } = ctx.request.body
// validate current password
if (!password) {
ctx.throw(422, 'Current password is required')
}
if (!await bcrypt.compare(password, user.password)) {
ctx.throw(401, 'Current password is incorrect')
}
// begin query
const q = squel.update()
.table('users')
.where('userId = ?', ctx.user.userId)
if (name && name.trim()) {
// @todo check length
q.set('name', name.trim())
}
// changing username?
if (username && username.trim()) {
// check for duplicate
if (await User.getByUsername(username.trim())) {
ctx.throw(401, 'Username or email is not available')
}
// @todo check length
q.set('username', username.trim())
} else {
// use current username to log in
username = user.username
}
// changing password?
if (newPassword) {
// @todo check length
if (newPassword !== newPasswordConfirm) {
ctx.throw(422, 'New passwords do not match')
}
q.set('password', await bcrypt.hash(newPassword, 10))
password = newPassword
}
// changing user image?
if (ctx.request.files.image) {
q.set('image', await readFile(ctx.request.files.image.path))
await deleteFile(ctx.request.files.image.path)
} else if (ctx.request.body.image === 'null') {
q.set('image', null)
}
q.set('dateUpdated', Math.floor(Date.now() / 1000))
const { text, values } = q.toParam()
await db.run(text, values)
// notify room?
if (ctx.user.roomId) {
ctx.io.to(ctx.user.roomId).emit('action', {
type: QUEUE_PUSH,
payload: await Queue.getQueue(ctx.user.roomId)
})
}
// get updated token
await _login(ctx, { username, password, roomId: ctx.user.roomId })
})
/**
* Given an operation, a table name, an entity object and an an options object (if required)
* this functions attempts to build an ANSI SQL statement
* @param operation the required operation (INSERT, FIND, DELETE, UPDATE)
* @param table The table object
* @param entity the entity object (In example: in INSERT, the entity to be added)
* @param options (an options object, containing additional query information)
* @returns {*}
*/
function buildQuery(operation, table, entity, options) {
var query;
var opts = options || {};
/**
* Oracle requires invocation of specific function to correctly format values.
* In case of dates, TO_DATE function needs to be invoked. In order to
* support function call from SQL, we will need to wrap provided date value in a Oracle's TO_DATE function call
* @param value the date value
* @returns {*}
* @private
*/
var _transformNonSupportedTypeToString = function(value){
var _isDate = function(value){
return value && typeof value === "object" && value.setUTCMilliseconds;
};
//TODO: Maybe designer would provide date format somewhere?
if (_isDate(value)) return "TO_DATE('" + value.toISOString().slice(0,10) + " " + value.toISOString().slice(11, 19) + "', 'yyyy-mm-dd hh24:mi:ss')";
return value;
};
var _mapColumnType = function(table, columnName){
var columnType = table.def[columnName].type;
var isPK = table.def[columnName].primaryKey;
var mappedAttribute = "";
switch(columnType){
case "date":
mappedAttribute += columnName + " DATE ";
break;
case "datetime":
mappedAttribute += columnName + " TIMESTAMP ";
break;
case "integer":
mappedAttribute += columnName + " NUMBER(5) ";
break;
default:
mappedAttribute += columnName + " VARCHAR2(256) ";
break;
}
if(isPK) mappedAttribute += " PRIMARY KEY";
return mappedAttribute;
}
/* Data definition language is out of scope of squel library, so we will need to handle this case apart */
var _createTableQuery = function(table){
var columns = " ( ";
Object.keys(table.def).forEach(function(col) {
columns += _mapColumnType(table, col);
columns += ",";
});
columns = columns.substring(0, columns.length - 1); //Removes trailing comma
columns += " ) ";
var createQuery = "CREATE TABLE " + table.name + columns;
return createQuery;
}
/* Data definition language is out of scope of squel library, so we will need to handle this case apart */
var _alterTableQuery = function(table) {
var query = "";
Object.keys(table.def).forEach(function (col) {
var alterQuery = "ALTER TABLE " + table.name + " ADD ( " + _mapColumnType(table, col) + " ) ";
query += alterQuery;
});
return query;
}
switch (operation) {
case 1:
query = sql.insert().into(table);
break;
case 2:
if (opts.limit || opts.skip) {
return _createOracleSpecificQuery(table, opts.skip, opts.limit, options.where);
} else {
query = sql.select().from(table);
}
break;
case 3:
query = sql.delete().from(table);
break;
case 4:
query = sql.update().table(table);
break;
case 5:
query = _createTableQuery(table);
break;
case 6:
query = _alterTableQuery(table);
break;
}
if (entity && operation != 3) {
Object.keys(entity).forEach(function (fieldName) {
query.set(fieldName, _transformNonSupportedTypeToString(entity[fieldName]));
});
}
if (options && options.where) {
var where = "(1=1) ";
var opts = options.where;
Object.keys(options.where).forEach(function (key) {
where += "and " + key + " ='" + opts[key] + "' ";
});
query.where(where);
}
return query.toString().split("'TO_DATE").join("TO_DATE").split("')'").join("')");
}