extend queries for transactions

1 files changed, 269 insertions, 33 deletions

diff --git a/routes/admin.js b/routes/admin.js
index 9c0aef48..9bd6bbef 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -166,7 +166,7 @@ router.get(
 );
 
 /**
- * @api {get} /user_transactions Retrieve summary of user transactions
+ * @api {get} /transactions Retrieve summary of user transactions
  * 
  * @apiName user_transactions
  * @apiGroup Admin
@@ -185,12 +185,13 @@ router.get(
  */
 router.get(
   "/transactions",
-  // auth,
+  auth,
   ah(async (req, res) => {
     const {type, since} = req.query
 
     var transactions = knex
       .select(
+        'id',
         'users.username',
         'users.email',
         // knex.raw('LEFT(created_at, 10) as created')
@@ -200,49 +201,284 @@ router.get(
         // knex.raw('COUNT(orders.id) as orders')
       )
       .from('users')
-      .groupBy('users.id')
 
-      if (type) {
+      const types = ['deposits', 'withdrawals', 'orders', 'payments', 'invoices']
+      for (var i=0; i<types.length; i++) {
+        var stamp = types[i].replace(/s$/,'') + '_date'
+
         transactions = transactions
         .select(
-          knex.raw('COUNT(' + type + '.id) as ' + type),
-          // knex.raw('LEFT(' + type + '.updatedAt, 10) as updated')
+          knex.raw('COUNT(' + types[i] + '.id) as ' + types[i]),
+          knex.raw('LEFT(MAX(' + types[i] + '.updatedAt), 10) as ' + stamp)
         )
-        .leftJoin(type, 'users.id', type + '.user_id')
-        .havingRaw(type + ' > ?', [0])
+        .leftJoin(types[i], 'users.id', types[i] + '.user_id')
+        // .havingRaw(types[i] + ' > ?', [0])
+        
+        if (since) {
+          transactions = transactions
+            .havingRaw('(' + stamp + ' >= ? OR ' + stamp + ' IS NULL)', [since])
+          debug(types[i] + ' since ' + since)
+        }
       }
+      if (since) transactions = transactions.havingRaw('invoices + payments + deposits + orders > ?', [0])
+      
+      transactions = transactions
+        .groupBy('users.id')
+
+    var found = await transactions
+    debug('transactions: ' + JSON.stringify(found))
+    return res.send({transactions: found})
+  })
+);
+
+/**
+ * @api {get} /orders Retrieve summary of orders
+ * 
+ * @apiName orders
+ * @apiGroup Admin
+ *
+ * @apiPermission admin
+ * 
+ * @apiSuccess returns list of orders
+ * @apiSuccessExample {json} Success-Response:
+ *     HTTP/1.1 200 OK
+ *     {
+ *       "orders" : [
+ *         { "seller": 'Adam', "buyer": 'bob' ... },
+         ...
+ *        ]
+ *     }
+ */
+router.get(
+  "/orders",
+  auth,
+  ah(async (req, res) => {
+    const {type, since} = req.query
+
+    var orders = knex
+      .select(
+        'u1.username as seller',
+        'u2.username as buyer',
+        'v1 as sell_amount',
+        'v2 as buy_amount',
+        'rate',
+        'a1.name as from',
+        'a2.name as to',
+        knex.raw('Left(completedAt,16) as completed')
+      )
+      .from('orders')
+      .leftJoin('accounts as a1', 'a1.id', 'orders.a1_id')
+      .leftJoin('accounts as a2', 'a2.id', 'orders.a2_id')
+      .leftJoin('users as u1', 'a1.user_id', 'u1.id')
+      .leftJoin('users as u2', 'a2.user_id', 'u2.id')
 
       if (since) {
-        transactions = transactions
-        .where(type + '.updatedAt', '>=', since)
+        orders = orders
+        .where('orders.updatedAt', '>=', since)
       }
 
-      debug(type + ' since ' + since)
-      // .leftJoin('accounts', 'users.id', 'accounts.user_id')
-      // .leftJoin('invoices', 'invoices.account_id', 'accounts.id')
-      // .leftJoin('payments', 'payments.account_id', 'accounts.id')
-      // .leftJoin('orders', 'orders.user_id', 'users.id')
-      // .groupBy('users.id')
+      debug('orders since ' + since)
 
-    // Alternative Sequelize query syntax (returns sub-tables as hash)
-    // var referrals = await db.Referral.findAll({
-    //   include: [ 
-    //     {model: db.User, as: 'user', attributes: [['username', 'user']]},
-    //     {model: db.User, as: 'sponsor', attributes: [['username', 'sponsor']]}
-    //   ]
-    // })
+    var found = await orders
+    debug('orders: ' + JSON.stringify(found))
+    return res.send({orders: found})
+  })
+);
 
-    // var transactions = await db.User.findAll({
-    //   include: [
-    //     { model: db.Invoice, as: 'invoices' },
-    //     { model: db.Payment, as: 'payments' },
-    //     { model: db.Order, as: 'orders'}
-    //   ]
-    // })
+/**
+ * @api {get} /invoices Retrieve summary of invoices
+ * 
+ * @apiName invoices
+ * @apiGroup Admin
+ *
+ * @apiPermission admin
+ * 
+ * @apiSuccess returns list of invoices
+ * @apiSuccessExample {json} Success-Response:
+ *     HTTP/1.1 200 OK
+ *     {
+ *       "invoices" : [
+ *         { "seller": 'Adam', "buyer": 'bob' ... },
+         ...
+ *        ]
+ *     }
+ */
+router.get(
+  "/invoices",
+  auth,
+  ah(async (req, res) => {
+    const {type, since} = req.query
 
-    var found = await transactions
-    debug('transactions: ' + JSON.stringify(found))
-    return res.send({transactions: found})
+    var invoices = knex
+      .select(
+        'username',
+        'users.email',
+        'invoices.currency',
+        'invoices.amount',
+        'accounts.network',
+        knex.raw('Left(invoices.updatedAt,16) as updated')
+      )
+      .from('invoices')
+      .leftJoin('accounts', 'invoices.account_id', 'accounts.id')
+      .leftJoin('users', 'accounts.user_id', 'users.id')
+
+      if (since) {
+        invoices = invoices
+        .where('invoices.updatedAt', '>=', since)
+      }
+
+      debug('invoices since ' + since)
+
+    var found = await invoices
+    debug('invoices: ' + JSON.stringify(found))
+    return res.send({invoices: found})
+  })
+);
+
+
+/**
+ * @api {get} /deposits Retrieve summary of deposits
+ * 
+ * @apiName deposits
+ * @apiGroup Admin
+ *
+ * @apiPermission admin
+ * 
+ * @apiSuccess returns list of deposits
+ * @apiSuccessExample {json} Success-Response:
+ *     HTTP/1.1 200 OK
+ *     {
+ *       "deposits" : [
+ *         { "seller": 'Adam', "buyer": 'bob' ... },
+         ...
+ *        ]
+ *     }
+ */
+router.get(
+  "/deposits",
+  auth,
+  ah(async (req, res) => {
+    const {type, since} = req.query
+
+    var deposits = knex
+      .select(
+        'username',
+        'users.email',
+        'amount',
+        'credited',
+        knex.raw('Left(deposits.updatedAt,16) as deposited')
+      )
+      .from('deposits')
+      .leftJoin('users', 'deposits.user_id', 'users.id')
+
+      if (since) {
+        deposits = deposits
+        .where('deposits.updatedAt', '>=', since)
+      }
+
+      debug('deposits since ' + since)
+
+    var found = await deposits
+    debug('deposits: ' + JSON.stringify(found))
+    return res.send({deposits: found})
+  })
+);
+
+/**
+ * @api {get} /withdrawals Retrieve summary of withdrawals
+ * 
+ * @apiName withdrawals
+ * @apiGroup Admin
+ *
+ * @apiPermission admin
+ * 
+ * @apiSuccess returns list of withdrawals
+ * @apiSuccessExample {json} Success-Response:
+ *     HTTP/1.1 200 OK
+ *     {
+ *       "withdrawals" : [
+ *         { "seller": 'Adam', "buyer": 'bob' ... },
+         ...
+ *        ]
+ *     }
+ */
+router.get(
+  "/withdrawals",
+  auth,
+  ah(async (req, res) => {
+    const {type, since} = req.query
+
+    var withdrawals = knex
+      .select(
+        'username',
+        'users.email',
+        'amount',
+        'notes',
+        knex.raw('Left(withdrawals.updatedAt,16) as withdrawn')
+      )
+      .from('withdrawals')
+      .leftJoin('users', 'withdrawals.user_id', 'users.id')
+
+      if (since) {
+        withdrawals = withdrawals
+        .where('withdrawals.updatedAt', '>=', since)
+      }
+
+      debug('withdrawals since ' + since)
+
+    var found = await withdrawals
+    debug('withdrawals: ' + JSON.stringify(found))
+    return res.send({withdrawals: found})
+  })
+);
+
+/**
+ * @api {get} /payments Retrieve summary of payments
+ * 
+ * @apiName payments
+ * @apiGroup Admin
+ *
+ * @apiPermission admin
+ * 
+ * @apiSuccess returns list of payments
+ * @apiSuccessExample {json} Success-Response:
+ *     HTTP/1.1 200 OK
+ *     {
+ *       "payments" : [
+ *         { "seller": 'Adam', "buyer": 'bob' ... },
+         ...
+ *        ]
+ *     }
+**/
+router.get(
+  "/payments",
+  auth,
+  ah(async (req, res) => {
+    const {type, since} = req.query
+
+    var payments = knex
+      .select(
+        'username',
+        'users.email',
+        'amount',
+        'payments.network',
+        knex.raw('LEFT(hash,20) as hash'),
+        knex.raw('Left(payments.updatedAt,16) as deposited')
+      )
+      .from('payments')
+      .leftJoin('accounts', 'payments.account_id', 'accounts.id')
+      .leftJoin('users', 'accounts.user_id', 'users.id')
+
+      if (since) {
+        payments = payments
+        .where('payments.updatedAt', '>=', since)
+      }
+
+      debug('payments since ' + since)
+
+    var found = await payments
+    debug('payments: ' + JSON.stringify(found))
+    return res.send({payments: found})
   })
 );