aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--db/init/update.sql123
-rw-r--r--db/migrations/20210612181508_init_referrals.js43
-rw-r--r--db/migrations/20210612192804_init_waiting_list.js.js27
-rw-r--r--db/migrations/20210714123455_data_fixes_merge.js5
-rw-r--r--routes/admin.js12
-rw-r--r--scripts/apiParse.js42
6 files changed, 93 insertions, 159 deletions
diff --git a/db/init/update.sql b/db/init/update.sql
deleted file mode 100644
index 9412c1ee..00000000
--- a/db/init/update.sql
+++ /dev/null
@@ -1,123 +0,0 @@
-
-DROP TABLE IF EXISTS `waiting_list`;
-CREATE TABLE `waiting_list` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `email` varchar(255) DEFAULT NULL,
- `phone` varchar(255) DEFAULT NULL,
- `created_at` datetime DEFAULT NULL,
- `updated_at` datetime DEFAULT NULL,
- `status` enum('pending','contacted','registered', 'cancelled') DEFAULT NULL,
- `notes` varchar(255),
- `user_id` int(11),
- KEY `user` (`user_id`),
- FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
- PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
-
-DROP TABLE IF EXISTS `referrals`;
-CREATE TABLE `referrals` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `token` varchar(255) DEFAULT NULL,
- `expiry` date DEFAULT NULL,
- `status` enum('available', 'used', 'expired', 'cancelled') DEFAULT NULL,
- `sponsor_id` int(11) NOT NULL,
- `user_id` int(11) DEFAULT NULL,
- `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- UNIQUE KEY `token` (`token`),
- CONSTRAINT `sponsor` FOREIGN KEY (sponsor_id) REFERENCES users (`id`),
- CONSTRAINT `sponsored` FOREIGN KEY (`user_id`) REFERENCES users (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
-
-ALTER TABLE invoices ADD CONSTRAINT `invoice_user` FOREIGN KEY (user_id) REFERENCES users (id);
-ALTER TABLE invoices ADD CONSTRAINT `invoice_account` FOREIGN KEY (account_id) REFERENCES accounts (id);
-
-ALTER TABLE orders ADD CONSTRAINT `order_user` FOREIGN KEY (user_id) REFERENCES users (id);
-
-ALTER TABLE payments ADD CONSTRAINT `payment_user` FOREIGN KEY (user_id) REFERENCES users (id);
-ALTER TABLE payments ADD CONSTRAINT `payment_account` FOREIGN KEY (account_id) REFERENCES accounts (id);
-
-ALTER TABLE deposits ADD CONSTRAINT `deposit_user` FOREIGN KEY (user_id) REFERENCES users (id);
-
-ALTER TABLE withdrawals ADD CONSTRAINT `withdrawal_user` FOREIGN KEY (user_id) REFERENCES users (id);
-
-ALTER TABLE accounts ADD CONSTRAINT `account_user` FOREIGN KEY (user_id) REFERENCES users (id);
-ALTER TABLE users ADD CONSTRAINT `user_account` FOREIGN KEY (account_id) REFERENCES accounts (id);
-
-ALTER TABLE users ADD email varchar(255);
-ALTER TABLE users ADD phone varchar(255);
-ALTER TABLE users ADD admin tinyint default false;
-
-DROP TABLE IF EXISTS `reset`;
-DROP TABLE IF EXISTS `naughty`;
-DROP TABLE IF EXISTS `cheaters`;
-
---
--- Table structure for table `migrations`
---
-
-DROP TABLE IF EXISTS `migrations`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `migrations` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `name` varchar(255) DEFAULT NULL,
- `batch` int(11) DEFAULT NULL,
- `migration_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
- PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
---
--- Table structure for table `migrations_lock`
---
-
-DROP TABLE IF EXISTS `migrations_lock`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `migrations_lock` (
- `index` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `is_locked` int(11) DEFAULT NULL,
- PRIMARY KEY (`index`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
-
---
--- Table structure for table `urls`
---
-
-DROP TABLE IF EXISTS `urls`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `urls` (
- `hash` varchar(255) DEFAULT NULL,
- `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
- UNIQUE KEY `urls_hash_unique` (`hash`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
----
---- New field changes...
----
-
-UPDATE accounts set createdAt = '2020-01-01' where createdAt < '2020-01-01';
-UPDATE accounts set updatedAt = '2020-01-01' where createdAt < '2020-01-01';
-ALTER TABLE accounts modify `contract` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
-
-ALTER TABLE invoices modify `tip` double NOT NULL DEFAULT 0;
-ALTER TABLE invoices ADD `webhook` text DEFAULT NULL;
-
-ALTER TABLE payments ADD `invoice_id` int(11) DEFAULT NULL;
-ALTER TABLE payments modify `tip` double NOT NULL DEFAULT 0;
-
-ALTER TABLE orders DROP a1;
-ALTER TABLE orders DROP a2;
-
-ALTER TABLE users MODIFY `unit` varchar(255) DEFAULT NULL;
-ALTER TABLE users ADD `authyId` varchar(255) DEFAULT NULL;
-ALTER TABLE users MODIFY `subscriptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;
-ALTER TABLE users DROP symbol;
-
-
diff --git a/db/migrations/20210612181508_init_referrals.js b/db/migrations/20210612181508_init_referrals.js
index 875d167f..f9107e15 100644
--- a/db/migrations/20210612181508_init_referrals.js
+++ b/db/migrations/20210612181508_init_referrals.js
@@ -1,21 +1,24 @@
exports.up = function(knex) {
- return knex.schema.createTable('referrals', function(t) {
- t.increments('id').unsigned().primary();
- t.integer('user_id').nullable();
- t.foreign('user_id').references('users.id');
-
- t.integer('sponsor_id').notNull();
- t.foreign('sponsor_id').references('users.id');
-
- t.string('token').notNull();
- t.string('expiry').nullable();
- t.enum('status', ['available', 'used', 'expired', 'cancelled']).notNull().defaultTo('available');
-
- t.timestamps();
- })
- };
-
- exports.down = function(knex) {
- return knex.schema.dropTable('referrals');
- };
- \ No newline at end of file
+ return Promise.all([
+ knex.schema.dropTableIfExists('referrals'),
+
+ knex.schema.createTable('referrals', function(t) {
+ t.increments('id').unsigned().primary();
+ t.integer('user_id').nullable();
+ t.foreign('user_id').references('users.id');
+
+ t.integer('sponsor_id').notNull();
+ t.foreign('sponsor_id').references('users.id');
+
+ t.string('token').notNull();
+ t.string('expiry').nullable();
+ t.enum('status', ['available', 'used', 'expired', 'cancelled']).notNull().defaultTo('available');
+
+ t.timestamps();
+ })
+ ])
+};
+
+exports.down = function(knex) {
+ return knex.schema.dropTable('referrals');
+};
diff --git a/db/migrations/20210612192804_init_waiting_list.js.js b/db/migrations/20210612192804_init_waiting_list.js.js
index 65d3eb42..67762ff1 100644
--- a/db/migrations/20210612192804_init_waiting_list.js.js
+++ b/db/migrations/20210612192804_init_waiting_list.js.js
@@ -1,17 +1,22 @@
exports.up = function(knex) {
- return knex.schema.createTable('waiting_list', function(t) {
- t.increments('id').unsigned().primary();
+
+ return Promise.all([
+ knex.schema.dropTableIfExists('waiting_list'),
+
+ knex.schema.createTable('waiting_list', function(t) {
+ t.increments('id').unsigned().primary();
+
+ t.string('email').notNull();
+ t.string('phone').notNull();
+ t.enu('status', ['pending', 'activated', 'expired', 'cancelled']).notNull();
- t.string('email').notNull();
- t.string('phone').notNull();
- t.enu('status', ['pending', 'activated', 'expired', 'cancelled']).notNull();
-
- t.integer('user_id').nullable();
- t.foreign('user_id').references('users.id');
- t.text('notes').nullable();
+ t.integer('user_id').nullable();
+ t.foreign('user_id').references('users.id');
+ t.text('notes').nullable();
- t.timestamps();
- })
+ t.timestamps();
+ })
+ ])
}
exports.down = function(knex) {
diff --git a/db/migrations/20210714123455_data_fixes_merge.js b/db/migrations/20210714123455_data_fixes_merge.js
index 70740ce8..eb873554 100644
--- a/db/migrations/20210714123455_data_fixes_merge.js
+++ b/db/migrations/20210714123455_data_fixes_merge.js
@@ -7,6 +7,11 @@ exports.up = function(knex) {
knex.raw("alter table orders drop rate"),
knex.raw("alter table orders add rate double"),
knex.raw("update orders set rate = v1/v2"),
+
+ knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where length(username) > 32'),
+ knex.raw('update accounts left join users on accounts.user_id = users.id set user_id = null where users.id is null'),
+ knex.raw('delete from accounts where user_id is null'),
+ knex.raw('delete from users where length(username) > 32')
])
}
diff --git a/routes/admin.js b/routes/admin.js
index fd65474a..c401c724 100644
--- a/routes/admin.js
+++ b/routes/admin.js
@@ -646,14 +646,16 @@ router.get(
ah(async (req, res) => {
var {since, search, starts_with, contains} = req.query
+ var threshold = '0.2'
+
var kyc = knex
.select(
knex.raw("CONCAT( FORMAT(Max(amount)/1000000, 1), ' M') as max"),
'username',
'users.email',
'users.verified as kyc_verified',
- 'payments.currency',
- 'payments.network',
+ // 'payments.currency',
+ // 'payments.network',
// knex.raw('LEFT(hash,20) as hash'),
// knex.raw('Left(payments.updatedAt,16) as transferred')
)
@@ -661,8 +663,8 @@ router.get(
.leftJoin('accounts', 'payments.account_id', 'accounts.id')
.leftJoin('users', 'payments.user_id', 'users.id') // should really point to accounts
.groupBy('users.id')
- .groupBy('payments.currency')
- .groupBy('payments.network')
+ // .groupBy('payments.currency')
+ // .groupBy('payments.network')
if (since) {
kyc = kyc
@@ -684,7 +686,7 @@ router.get(
this.orWhere('users.username', 'like', search)
})
}
- var found = await kyc.having('max', '>=', 2.1)
+ var found = await kyc.having('max', '>=', threshold)
debug('payments: ' + JSON.stringify(found))
return res.send({kyc_transactions: found})
diff --git a/scripts/apiParse.js b/scripts/apiParse.js
new file mode 100644
index 00000000..427f34cd
--- /dev/null
+++ b/scripts/apiParse.js
@@ -0,0 +1,42 @@
+var debug = require('debug')('debug')
+
+const addUserSearch = async function (input) {
+ debug('add User Search')
+ var {search, starts_with, contains} = input
+
+ // if (since) cmd = cmd.havingRaw('invoices + payments + deposits + orders > ?', [0])
+
+ if (search) {
+ if (starts_with) {
+ search = search + '%'
+ } else if (contains) {
+ search = '%' + search + '%'
+ }
+
+ var condition = "(Users.email like '" + search + " OR Users.username like '" + search + "'"
+
+ debug('condition: ' + condition)
+ return condition
+ } else {
+ return ''
+ }
+}
+
+const addTimeSearch = async function (input, field) {
+ debug('add time Search')
+ if (!field) { field = 'updatedAt' }
+
+ var {since} = input
+ if (since) {
+
+ condition = "WHERE " + field + " >= " + since
+ debug('condition: ' + condition)
+
+ return condition
+ } else {
+ return ''
+ }
+}
+
+module.exports = {addUserSearch: addUserSearch, addTimeSearch: addTimeSearch}
+
generated by cgit v1.2.3 (git 2.51.0) at 2026-10-15 15:21:18 +0000