def fetch_tasks():
with db.connect() as conn:
stmt = sqlalchemy.text('SELECT task_id, requests.contents, tasks.contents FROM tasks INNER JOIN requests USING (request_id) '
'WHERE tasks.user_id=:user_id AND 0 < (SELECT COUNT(*) FROM payments WHERE payments.request_id=requests.request_id AND '
'deadline > current_timestamp(6) AND canceled IS NULL) ORDER BY tasks.created DESC')
tasks = conn.execute(stmt, user_id=user_id()).fetchall()
return jsonify([{'task_id': task[0], 'request_contents': task[1], 'task_contents': task[2]} for task in tasks])
def cancel_payment():
payment_id = req.values.get('payment_id')
canceled = datetime.datetime.utcnow()
stripe.api_key = stripe_api_key
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT request_id FROM payments WHERE payment_id=:payment_id')
request_id = conn.execute(stmt, payment_id=payment_id).fetchone()[0]
stmt = sqlalchemy.text(
'SELECT user_id FROM requests WHERE request_id=:request_id')
user_has_payment = conn.execute(
stmt, request_id=request_id).fetchone()[0] == user_id()
if not user_has_payment: return 'Bad request', 400
stmt = sqlalchemy.text(
'SELECT amount, created, deadline, charge_id from payments WHERE payment_id=:payment_id LIMIT 1'
)
amount, created, deadline, charge_id = conn.execute(
stmt, payment_id=payment_id).fetchone()
if not deadline > canceled > created: return 'Bad request', 400
amount = int(amount * (deadline - canceled) / (deadline - created))
refund = stripe.Refund.create(charge=charge_id, amount=amount)
stmt = sqlalchemy.text(
'UPDATE payments SET canceled=:canceled, refund_id=:refund_id WHERE payment_id=:payment_id LIMIT 1'
)
conn.execute(stmt,
canceled=canceled,
refund_id=refund.id,
payment_id=payment_id)
return jsonify([{'status': 'successful'}])
def delete_account():
with db.connect() as conn:
stmt = sqlalchemy.text(
'UPDATE users SET deleted=CURRENT_TIMESTAMP(6) WHERE user_id=:user_id LIMIT 1'
)
conn.execute(stmt, user_id=user_id())
return jsonify([{'status': 'successful'}])
def update_pair():
which = req.values.get('which')
pair_id = req.values.get('pair_id')
with db.connect() as conn:
stmt = sqlalchemy.text('UPDATE pairs SET which=:which WHERE pair_id=:pair_id AND user_id=:user_id LIMIT 1')
conn.execute(stmt, which=which, user_id=user_id(), pair_id=pair_id)
return jsonify([{'status': 'successful'}])
def create_request():
with db.connect() as conn:
stmt = sqlalchemy.text(
'INSERT INTO requests(request_id, user_id) VALUES (:request_id, :user_id)'
)
conn.execute(stmt, request_id=uuid(), user_id=user_id())
return jsonify(success=True)
def update_task():
contents = req.values.get('contents')
contents = safe(contents) if contents.strip() else None
task_id = req.values.get('item_id')
with db.connect() as conn:
stmt = sqlalchemy.text('UPDATE tasks SET contents=:contents WHERE task_id=:task_id AND user_id=:user_id LIMIT 1')
conn.execute(stmt, contents=contents, user_id=user_id(), task_id=task_id)
return jsonify([{'status': 'successful'}])
def submit_payment():
payment_id = uuid()
request_id = req.values.get('request_id')
usd = int(req.values.get('usd'))
hour = int(req.values.get('hour'))
if usd not in [1, 2, 5, 10, 20] or hour not in [1, 3, 6, 12, 24]:
return 'Bad request', 400
amount = usd * 100
created = datetime.datetime.utcnow()
deadline = created + datetime.timedelta(hours=hour)
created = created.strftime('%Y-%m-%d %H:%M:%S.%f')
deadline = deadline.strftime('%Y-%m-%d %H:%M:%S.%f')
currency = 'usd'
weight = amount * 24 // hour
stripe.api_key = stripe_api_key
metadata = {
'payment_id': payment_id,
'user_id': user_id(),
'request_id': request_id
}
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT user_id FROM requests WHERE request_id=:request_id')
user_has_request = conn.execute(
stmt, request_id=request_id).fetchone()[0] == user_id()
if not user_has_request: return 'Bad request', 400
stmt = sqlalchemy.text(
'SELECT customer_id FROM users WHERE user_id=:user_id LIMIT 1')
result = conn.execute(stmt, user_id=user_id()).fetchone()
if not result: return 'Bad request', 400
customer_id = result[0]
stmt = sqlalchemy.text(
'INSERT INTO payments(payment_id, request_id, user_id, created, deadline, amount, hour, currency, weight) '
'VALUES (:payment_id, :request_id, :user_id, :created, :deadline, :amount, :hour, :currency, :weight)'
)
conn.execute(stmt,
payment_id=payment_id,
request_id=request_id,
user_id=user_id(),
created=created,
deadline=deadline,
amount=amount,
hour=hour,
currency=currency,
weight=weight)
charge = stripe.Charge.create(amount=amount,
currency='usd',
customer=customer_id,
metadata=metadata)
stmt = sqlalchemy.text(
'UPDATE payments SET charge_id=:charge_id WHERE payment_id=:payment_id LIMIT 1'
)
conn.execute(stmt, charge_id=charge.id, payment_id=payment_id)
return jsonify([{'status': 'successful'}])
def active_requests(): # not used
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT request_id, contents, created FROM requests WHERE user_id=:user_id AND removed IS NULL AND deleted IS NULL '
'AND 0 < (SELECT COUNT(*) FROM payments WHERE payments.request_id=requests.request_id AND deadline > current_timestamp(6) '
'AND canceled IS NULL) ORDER BY created DESC')
requests = conn.execute(stmt, user_id=user_id()).fetchall()
return jsonify([{
'request_id': request[0],
'contents': request[1],
'created': request[2].strftime('%Y-%m-%dT%H:%M:%S.%fZ')
} for request in requests])
def search_requests():
query = req.values.get('query').replace(" ", "").split(' ')
query = ' '.join(['+' + q for q in query if len(q) > 1])
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT request_id, contents, created FROM requests WHERE user_id=:user_id AND deleted IS NULL '
'AND MATCH(contents) AGAINST(:query IN BOOLEAN MODE) ORDER BY created DESC'
)
requests = conn.execute(stmt, user_id=user_id(),
query=query).fetchall()
return jsonify([{
'request_id': request[0],
'contents': request[1],
'created': request[2].strftime('%Y-%m-%dT%H:%M:%S.%fZ')
} for request in requests])
def fetch_requests():
before = req.values.get('before')
before = before.replace('T', ' ').replace('Z', '')
source = req.values.get('source')
source = '!(removed IS NULL)' if source == 'trash' else 'removed IS NULL'
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT request_id, contents, created FROM requests WHERE user_id=:user_id AND '
+ source + ' AND deleted IS NULL '
'AND created < :before ORDER BY created DESC LIMIT 50')
requests = conn.execute(stmt, user_id=user_id(),
before=before).fetchall()
return jsonify([{
'request_id': request[0],
'contents': request[1],
'created': request[2].strftime('%Y-%m-%dT%H:%M:%S.%fZ')
} for request in requests])
def create_pair():
if not user_id(): return 'Unauthorized', 401
# if the user is suspicious, reject it.
with db.connect() as conn:
stmt = sqlalchemy.text('SELECT task_id FROM tasks WHERE contents IS NOT NULL AND 0 < (SELECT COUNT(*) FROM payments '
'WHERE payments.request_id=tasks.request_id AND deadline > current_timestamp(6) '
'AND canceled IS NULL) ORDER BY RAND() LIMIT 10')
zero_tasks = conn.execute(stmt, user_id=user_id()).fetchall()
zero_task_id = random.choice(zero_tasks)[0]
stmt = sqlalchemy.text('SELECT task_id FROM tasks WHERE contents IS NOT NULL AND 0 < (SELECT COUNT(*) FROM payments '
'WHERE request_id=request_id AND deadline > current_timestamp(6) AND canceled IS NULL) AND task_id!=:zero_task_id '
'ORDER BY RAND() LIMIT 10')
one_tasks = conn.execute(stmt, user_id=user_id(), zero_task_id=zero_task_id).fetchall()
one_task_id = random.choice(one_tasks)[0]
stmt = sqlalchemy.text('INSERT INTO pairs(pair_id, user_id, zero_task_id, one_task_id) VALUES (:pair_id, :user_id, :zero_task_id, :one_task_id)')
conn.execute(stmt, pair_id=uuid(), user_id=user_id(), zero_task_id=zero_task_id, one_task_id=one_task_id)
return jsonify([{'status': 'successful'}])
def create_task():
if not user_id(): return 'Unauthorized', 401
# if the user is suspicious, reject it.
with db.connect() as conn:
currency = random.choices(['twc', 'usd'], weights=[1, 2])[0]
currency = 'usd'
stmt = sqlalchemy.text('SELECT weight, COUNT(*) FROM payments WHERE deadline > current_timestamp(6) AND canceled IS NULL GROUP BY weight')
data = conn.execute(stmt).fetchall()
weights, counts = np.array(data).astype(float).T
weight = int(random.choices(weights, weights*counts)[0])
stmt = sqlalchemy.text('SELECT request_id FROM payments WHERE weight=:weight AND '
'deadline > current_timestamp(6) AND canceled IS NULL ORDER BY RAND() LIMIT 10')
requests = conn.execute(stmt, weight=weight).fetchall()
request_id = random.choice(requests)[0]
stmt = sqlalchemy.text('INSERT INTO tasks(task_id, user_id, request_id) VALUES (:task_id, :user_id, :request_id)')
conn.execute(stmt, task_id=uuid(), user_id=user_id(), request_id=request_id)
return jsonify([{'status': 'successful'}])
def fetch_payments():
request_id = req.values.get('request_id')
now = datetime.datetime.utcnow()
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT payment_id, created, amount, hour, deadline, canceled from payments WHERE request_id=:request_id AND '
':user_id IN (SELECT user_id FROM requests WHERE request_id=:request_id) ORDER BY created'
)
data = conn.execute(stmt, request_id=request_id,
user_id=user_id()).fetchall()
payments = [{
'payment_id': item[0],
'created': item[1].strftime('%Y-%m-%dT%H:%M:%S.%fZ'),
'twc': item[2] // 100,
'hour': item[3],
'refundable': item[4] > now and not item[5]
} for item in data]
return jsonify(payments)
def fetch_pairs():
with db.connect() as conn:
stmt = sqlalchemy.text('SELECT pair_id FROM pairs WHERE user_id=:user_id AND 0 < (SELECT COUNT(*) FROM payments WHERE request_id IN '
'(SELECT request_id FROM tasks WHERE task_id=zero_task_id) AND deadline > current_timestamp(6) AND canceled IS NULL) '
'AND 0 < (SELECT COUNT(*) FROM payments WHERE request_id IN (SELECT request_id FROM tasks WHERE task_id=one_task_id) '
'AND deadline > current_timestamp(6) AND canceled IS NULL) ORDER BY created DESC')
pairs = conn.execute(stmt, user_id=user_id()).fetchall()
pairs = [row[0] for row in pairs]
if not pairs: return jsonify([])
stmt = sqlalchemy.text('SELECT pair_id, zero_requests.contents, zero_tasks.contents, zero_requests.contents, one_tasks.contents, which '
'FROM pairs INNER JOIN tasks as zero_tasks ON pairs.zero_task_id=zero_tasks.task_id '
'INNER JOIN tasks as one_tasks ON pairs.one_task_id=one_tasks.task_id '
'INNER JOIN requests as zero_requests ON zero_tasks.request_id=zero_requests.request_id '
'INNER JOIN requests as one_requests ON one_tasks.request_id=one_requests.request_id '
'WHERE pair_id IN :pairs')
pairs = conn.execute(stmt, pairs=pairs).fetchall()
pairs = [{'pair_id': row[0], 'zero_request_contents': row[1], 'zero_task_contents': row[2],
'one_request_contents': row[3], 'one_task_contents': row[4], 'which': row[5]} for row in pairs]
return jsonify(pairs)
def save_card():
token = req.values.get('token')
id_token = req.headers['Authorization'].split(' ').pop()
email = auth.verify_id_token(id_token)['email']
stripe.api_key = stripe_api_key
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT customer_id FROM users WHERE user_id=:user_id LIMIT 1')
result = conn.execute(stmt, user_id=user_id()).fetchone()
customer_id = result[0] if result else None
if customer_id is None:
customer = stripe.Customer.create(source=token,
email=email,
metadata={'user_id': user_id()})
stmt = sqlalchemy.text(
'INSERT INTO users(user_id, customer_id) VALUES (:user_id, :customer_id) '
'ON DUPLICATE KEY UPDATE customer_id=:customer_id')
conn.execute(stmt, user_id=user_id(), customer_id=customer.id)
else:
stripe.Customer.modify(customer_id, source=token)
return jsonify({'status': 'successful'})
def download_data():
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT user_id, api_key, customer_id FROM users WHERE user_id=:user_id LIMIT 1'
)
user_data = conn.execute(stmt, user_id=user_id()).fetchall()
stmt = sqlalchemy.text(
'SELECT request_id, contents, created, removed FROM requests '
'WHERE user_id=:user_id AND deleted IS NULL ORDER BY created DESC')
request_data = conn.execute(stmt, user_id=user_id()).fetchall()
user_data = pd.DataFrame(list(user_data),
columns=['user_id', 'api_key',
'customer_id']).to_csv(index=False)
time = '%Y-%m-%dT%H:%M:%S.%fZ'
request_data = [(row[0], row[1], row[2].strftime(time),
row[3].strftime(time) if row[3] else None)
for row in request_data]
request_data = pd.DataFrame(
request_data, columns=['request_id', 'contents', 'created',
'removed']).to_csv(index=False)
return jsonify({'user_data': user_data, 'request_data': request_data})
def cancel():
payment_id = req.values.get('payment_id')
canceled = datetime.datetime.utcnow()
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT user_id FROM requests WHERE request_id IN (SELECT request_id FROM payments WHERE payment_id=:payment_id) LIMIT 1'
)
user_has_payment = conn.execute(
stmt, payment_id=payment_id).fetchone()[0] == user_id()
if not user_has_payment: return 'Bad request', 400
stmt = sqlalchemy.text(
'SELECT created, deadline FROM payments WHERE payment_id=:payment_id LIMIT 1'
)
created, deadline = conn.execute(stmt,
payment_id=payment_id).fetchone()
if not deadline > canceled > created: return 'Bad request', 400
stmt = sqlalchemy.text(
'UPDATE payments SET canceled=:canceled WHERE payment_id=:payment_id LIMIT 1'
)
conn.execute(stmt, canceled=canceled, payment_id=payment_id)
return jsonify([{'status': 'successful'}])
def submit():
request_id = req.values.get('request_id')
twc = int(req.values.get('twc'))
hour = int(req.values.get('hour'))
if twc not in [1, 2, 5, 10, 20] or hour not in [1, 3, 6, 12, 24]:
return 'Bad request', 400
amount = twc * 100
created = datetime.datetime.utcnow()
deadline = created + datetime.timedelta(hours=hour)
created = created.strftime('%Y-%m-%d %H:%M:%S.%f')
deadline = deadline.strftime('%Y-%m-%d %H:%M:%S.%f')
weight = amount * 24 // hour
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT user_id FROM requests WHERE request_id=:request_id LIMIT 1'
)
user_has_request = conn.execute(
stmt, request_id=request_id).fetchone()[0] == user_id()
if not user_has_request: return 'Bad request', 400
stmt = sqlalchemy.text(
'SELECT balance FROM users WHERE user_id=:user_id')
balance = conn.execute(stmt, user_id=user_id()).fetchone()[0]
if type(balance) is not int or amount > balance:
return 'Bad request', 400
stmt = sqlalchemy.text(
'INSERT INTO payments (payment_id, request_id, created, deadline, amount, hour, weight) '
'VALUES (:payment_id, :request_id, :created, :deadline, :amount, :hour, :weight)'
)
conn.execute(stmt,
payment_id=uuid(),
request_id=request_id,
created=created,
deadline=deadline,
amount=amount,
hour=hour,
weight=weight)
stmt = sqlalchemy.text(
'UPDATE users SET balance=:balance WHERE user_id=:user_id LIMIT 1')
conn.execute(stmt, balance=balance - amount, user_id=user_id())
return jsonify([{'status': 'successful'}])
def customer_id():
with db.connect() as conn:
stmt = sqlalchemy.text(
'SELECT customer_id FROM users WHERE user_id=:user_id LIMIT 1')
result = conn.execute(stmt, user_id=user_id()).fetchone()
return jsonify({'customer_id': result[0] if result else None})