db.from('capcodes') .select('id', 'ignore') // TODO: test this doesn't break other DBs - there's a lot of quote changes here .modify(function (queryBuilder) { if (dbtype == 'oracledb') { queryBuilder.whereRaw(`'${address}' LIKE "address"`) queryBuilder.orderByRaw(`REPLACE("address", '_', '%') DESC`); } else { queryBuilder.whereRaw(`"${address}" LIKE address`) queryBuilder.orderByRaw(`REPLACE(address, '_', '%') DESC`) } })
async function getUser(id) { const [user] = await knex('users') .where('id', id) .select('email', 'name'); return user; }
//save a new cat, cat is an object so I can insert directly function saveOne(cat) { return db("cats") .insert(cat) .then(data => { // bring some data back otherwise 'data' logs as the ID of our newly saved cat return db("cats").select(); }); }
router.route('/capcodes/agency/:id') .get(authHelper.isAdmin, function (req, res, next) { var id = req.params.id; db.from('capcodes') .select('*') .where('agency', 'like', id) .then((rows) => { res.status(200); res.json(rows); }) .catch((err) => { logger.main.error(err); return next(err); }) });
async function getUserSubscription(userId, appType) { if (!appType) return undefined; const row = await knex('subscriptions') .where({ user_id: userId, app: appType }) .select(['start_date', 'end_date', 'product_id', 'is_cancelled']) .orderBy('start_date', 'desc') .first(); if (!row) return undefined; return { startDate: row.start_date, endDate: row.end_date, productId: row.product_id, isCancelled: !!row.is_cancelled, type: 'iap', }; }
//delete cat by ID function deleteOne(id) { return db("cats") .where({ id }) .del() .then(data => { // the data callback here doesn't return any cats as we've deleted the cat. So returning whole DB. return db("cats").select(); }); }
async function getUserForLoginData(email, password) { const [user] = await knex('users') .select() .where({ email }) .limit(1); if (!user) { return null; } const isPasswordValid = await bcrypt.compare(password, user.password); if (!isPasswordValid) { return null; } return { id: user.id, username: user.email, created_at: user.created_at, }; }
// get all cats from DB function getAll() { return db("cats").select(); }
.select("short", "url") .where("short", short) .then(resp => {
router.route('/capcodeExport') .post(authHelper.isAdmin, function (req, res, next) { nconf.load(); var dbtype = nconf.get('database:type'); var filename = 'export.csv' db.from('capcodes') .select('*') .modify(function (queryBuilder) { if (dbtype == 'oracledb') queryBuilder.orderByRaw(`REPLACE("address", '_', '%')`); else queryBuilder.orderByRaw(`REPLACE(address, '_', '%')`) }) .then((rows) => { converter.json2csv(rows, function (err, data) { if (err) { res.status(500).send(err); } else { res.status(200).send({ 'status': 'ok', 'data': data }) } }) }) .catch((err) => { logger.main.error(err); return next(err); }) });
router.route('/userCheck/username/:id').get(bruteforcedupe.prevent, function(req, res, next) { const { id } = req.params; db.from('users') .select('username') .where('username', id) .then(row => { if (row.length > 0) { const rowsend = row[0]; res.status(200); res.json(rowsend); } else { const rowsend = { username: '', password: '', givenname: '', surname: '', email: '', role: 'user', status: 'active', }; res.status(200); res.json(rowsend); } }) .catch(err => { logger.main.error(err); return next(err); }); });
async function getUser(query) { const [user] = await knex('users') .select(['id', 'email', 'name', 'created_at']) .where(query) .limit(1); return user; }
async function getActiveSubscriptions() { return knex('subscriptions') .where('end_date', '>=', new Date()) .where('fake', false) .select(['id', 'latest_receipt', 'user_id', 'app']); }
router.route('/userCheck/email/:id').get(bruteforcedupe.prevent, function(req, res, next) { const { id } = req.params; db.from('users') .select('email') .where('email', id) .then(row => { if (row.length > 0) { const rowsend = row[0]; res.status(200); res.json(rowsend); } else { const rowsend = { username: '', password: '', givenname: '', surname: '', email: '', role: 'user', status: 'active', }; res.status(200); res.json(rowsend); } }) .catch(err => { logger.main.error(err); return next(err); }); });