81 lines
2.6 KiB
JavaScript
81 lines
2.6 KiB
JavaScript
const pg = require('pg');
|
|
|
|
class Database {
|
|
constructor(connectionStr) {
|
|
this.pool = new pg.Pool({connectionString: connectionStr});
|
|
}
|
|
|
|
async checkAndRegisterUser(id, name) {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT * FROM qr_users WHERE user_id = $1", [id]);
|
|
if (result.rowCount === 0) {
|
|
await client.query("INSERT INTO qr_users (user_id, name) VALUES ($1, $2)", [id, name]);
|
|
}
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async codeExists(code) {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT * FROM qr_codes WHERE id = $1", [code]);
|
|
return result.rowCount === 1;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async useCode(code, user) {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT * FROM qr_codes WHERE id = $1", [code]);
|
|
if (result.rowCount === 1) {
|
|
result = await client.query("SELECT * FROM qr_gots WHERE code_id = $1 AND user_id = $2", [code, user]);
|
|
if (result.rowCount === 0) {
|
|
await client.query("INSERT INTO qr_gots (code_id, user_id) VALUES ($1, $2)", [code, user]);
|
|
return true;
|
|
} else {
|
|
return false;
|
|
}
|
|
} else {
|
|
return false;
|
|
}
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async getUserCodes(user) {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT * FROM qr_gots WHERE user_id = $1", [user]);
|
|
return result.rows.length;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async getTotalCodes() {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT * FROM qr_codes");
|
|
return result.rowCount;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async getTop() {
|
|
let client = await this.pool.connect();
|
|
try {
|
|
let result = await client.query("SELECT qr_users.name as name, COUNT(qr_gots.user_id) AS count, qr_users.user_id as id FROM qr_gots INNER JOIN qr_users ON qr_gots.user_id = qr_users.user_id GROUP BY qr_users.user_id ORDER BY count DESC LIMIT 10");
|
|
return result.rows;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
}
|
|
|
|
module.exports = Database; |