def insert_coke(message):
"""Function to add ingested coke.
Parameters
----------
message : dict
The message object.
Returns
-------
msg : success or failure message - to be displayed at Telegram's chat.
"""
message_text = message.get('message').lower().split(' ')
try:
value = float(message_text[-1])
except ValueError:
return 'Cannot add non-numeric values!'
client = helpers.start_connection()
user_id = message.get('sender_id')
username = '******'.format(
message.get('first_name'),
message.get('last_name')
)
client.query("""
INSERT INTO
`mooncake-304003.misc.coca-cola`
VALUES
({}, CURRENT_DATETIME("America/Sao_Paulo"), {}, "{}")
""".format(value, user_id, username))
return '{} successfully inserted {} ml!'.format(username, value)
def drop_me(message):
"""This function removes user/chat id into a database.
Parameters
----------
message : dict
The message object.
Returns
-------
msg : str
User/Chat alert list addition/removal.
"""
helpers.start_connection().query("""
DELETE FROM
`mooncake-304003.misc.ps5-broadcast-list`
WHERE
CHAT_ID = {}
""".format(message.get('chat_id')))
return 'User/chat removed from PS5 alert list!'
def add_me(message):
"""This function adds user/chat id into a database.
Parameters
----------
message : dict
The message object.
Returns
-------
msg : str
User/Chat alert list addition/removal.
"""
helpers.start_connection().query(f"""
INSERT INTO
`mooncake-304003.misc.ps5-broadcast-list`
VALUES
({message.get('chat_id')},
"{message.get('chat_title')}")
""")
return 'User/chat added to PS5 alert list!'
def __init__(self, message):
self.message_text = message.get('message').lower().split(' ')
self.fees = self.extract_fees()
self.operation = self.message_text[2]
self.query = """
UPDATE
`mooncake-304003.trading.btc-fees`
SET
BOTTOM_FEE = {},
TOP_FEE = {}
WHERE
OPERATION = "{}"
"""
self.client = helpers.start_connection()
def __init__(self, message):
self.message_text = message.get('message').lower().split(' ')
self.username = self.format_user_name(message)
self.user_id = message.get('sender_id')
self.price = self.extract_price()
self.query = """
INSERT INTO
`mooncake-304003.trading.btc-trade`
VALUES
({}, "{}", {}, CURRENT_DATETIME("America/Sao_Paulo"))
"""
self.client = helpers.start_connection()
def __init__(self):
self.url = 'https://api.coinext.com.br:8443/AP/GetL2Snapshot'
self.payload = '{"OMSId": 1, "InstrumentId": 1, "Depth": 1}'
self.response = self.requesting()
self.operation = {
'operation': [],
'price': [],
'spread': [],
}
self.query = """
INSERT INTO
`mooncake-304003.trading.btc-historical`
VALUES
("{}", {}, {}, CURRENT_DATETIME("America/Sao_Paulo"))
"""
helpers.set_path()
self.client = helpers.start_connection()
def reset(message):
"""Function to reset all coke ingested by a user.
Parameters
----------
message : dict
The message object.
Returns
-------
msg: success message.
"""
client = helpers.start_connection()
client.query("""
DELETE
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
""".format(message.get('sender_id')))
return 'Statistics successfully reset!'
def __init__(self):
self.now = datetime.datetime.now()
self.date_times = self.fill_date_times()
self.queries = {
'buy':
"""
SELECT
*
FROM (
SELECT
OPERATION,
PRICE,
DATETIME,
EXTRACT(YEAR FROM DATETIME) AS YEAR,
EXTRACT(MONTH FROM DATETIME) AS MONTH,
EXTRACT(DAY FROM DATETIME) AS DAY,
EXTRACT(HOUR FROM DATETIME) AS HOUR,
FROM
`mooncake-304003.trading.btc-historical`)
WHERE
OPERATION = "sell"
AND YEAR = {}
AND MONTH = {}
AND DAY = {}
AND HOUR = {}
ORDER BY
DATETIME DESC
""",
'sell':
"""
SELECT
*
FROM (
SELECT
OPERATION,
PRICE,
DATETIME,
EXTRACT(YEAR FROM DATETIME) AS YEAR,
EXTRACT(MONTH FROM DATETIME) AS MONTH,
EXTRACT(DAY FROM DATETIME) AS DAY,
EXTRACT(HOUR FROM DATETIME) AS HOUR,
FROM
`mooncake-304003.trading.btc-historical`)
WHERE
OPERATION = "buy"
AND YEAR = {}
AND MONTH = {}
AND DAY = {}
AND HOUR = {}
ORDER BY
DATETIME DESC
""",
'purchase_price':
"""
SELECT
*
FROM
`mooncake-304003.trading.btc-trade`
WHERE
DATETIME = (
SELECT
MAX(DATETIME) AS DATETIME
FROM
`mooncake-304003.trading.btc-trade`
WHERE
USERNAME = "******"
)
""",
'fees':
"""
SELECT
*
FROM
`mooncake-304003.trading.btc-fees`
ORDER BY
OPERATION DESC
"""
}
helpers.set_path()
self.client = helpers.start_connection()
self.date_values = self.fill_date_values()
self.fees = self.fill_fees()
self.message = ''
self.index = {
'buy': {
1: '1 hora',
2: '5 horas',
3: '12 horas',
},
'sell': {
1: '0 horas',
2: '1 hora',
3: '5 horas',
4: '12 horas',
}
}
self.operation = {
'buy': 'Compra',
'sell': 'Venda',
'sell_purchased': 'Venda'
}
self.users = ('Bruno Paes', 'Diego None')
self.purchase_prices = self.querying_purchase_price()
def aggregate(message):
"""Function to check the sum of ingested coke grouped by periodicity.
Parameters
----------
message : dict
The message object.
Returns
-------
msg: Query result - to be displayed at Telegram's chat.
"""
client = helpers.start_connection()
query_dict = {
'day': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY,
MIN(DATETIME) AS DATETIME,
CONCAT(EXTRACT(YEAR FROM DATETIME),
"-", EXTRACT(MONTH FROM DATETIME),
"-", EXTRACT(DAY FROM DATETIME)) AS SAFRA
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
GROUP BY
SAFRA,
USERNAME
ORDER BY
DATETIME DESC
""",
'week': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY,
MIN(DATETIME) AS DATETIME,
CONCAT(EXTRACT(YEAR FROM DATETIME),
"-", EXTRACT(WEEK FROM DATETIME)) AS SAFRA
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
GROUP BY
SAFRA,
USERNAME
ORDER BY
DATETIME DESC
""",
'month': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY,
MIN(DATETIME) AS DATETIME,
CONCAT(EXTRACT(YEAR FROM DATETIME),
"-", EXTRACT(MONTH FROM DATETIME)) AS SAFRA
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
GROUP BY
SAFRA,
USERNAME
ORDER BY
DATETIME DESC
""",
'quarter': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY,
MIN(DATETIME) AS DATETIME,
CONCAT(EXTRACT(YEAR FROM DATETIME),
"-", EXTRACT(QUARTER FROM DATETIME)) AS SAFRA
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
GROUP BY
SAFRA,
USERNAME
ORDER BY
DATETIME DESC
""",
'year': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY,
MIN(DATETIME) AS DATETIME,
CONCAT(EXTRACT(YEAR FROM DATETIME)) AS SAFRA
FROM
`mooncake-304003.misc.coca-cola`
WHERE
USER_ID = {}
GROUP BY
SAFRA,
USERNAME
ORDER BY
DATETIME DESC
""",
'top': """
SELECT
USERNAME,
SUM(MILLILITERS) AS SUMMARY
FROM
`mooncake-304003.misc.coca-cola`
GROUP BY
USERNAME
ORDER BY
SUMMARY DESC
"""
}
periodicity = message.get('message').lower().split(' ')[-1]
user_id = message.get('sender_id')
query_result = \
[i for i in client.query(query_dict.get(periodicity).format(user_id))]
top_drinkers = ''
if periodicity == 'top':
for row in query_result:
top_drinkers += \
'{}: {}\n'.format(row.values()[0], row.values()[1])
return top_drinkers
else:
try:
return 'In {}: {} ingested {} ml'.format(
query_result[0].values()[3],
query_result[0].values()[0],
query_result[0].values()[1]
)
except IndexError:
return 'Impossible, perhaps the archives are incomplete!'