def select_outcome_category(chat_id):
cur = con.cursor()
try:
cur.execute(
'SELECT name, id, is_default from outcome_category WHERE chat_id = %s AND is_deleted IS FALSE OR is_default = TRUE',
(chat_id, ))
categories_name = cur.fetchall()
con.commit()
return categories_name
except Exception as e:
logger.error(e)
return False
finally:
cur.close()
def delete_outcome_category(outcome_id):
cur = con.cursor()
try:
cur.execute(
'UPDATE outcome_category SET is_deleted = TRUE WHERE id = %s AND is_default = FALSE RETURNING id;',
(outcome_id, ))
con.commit()
category_id = cur.fetchall()
return category_id
except Exception as e:
logger.error(e)
return False
finally:
cur.close()
def add_outcome_category(name, chat_id):
cur = con.cursor()
try:
cur.execute(
'INSERT INTO outcome_category (name, chat_id) VALUES (%s, %s);', (
name,
chat_id,
))
con.commit()
return True
except Exception as e:
logger.error(e)
return False
finally:
cur.close()
def add_income(amount, chat_id, username):
cur = con.cursor()
try:
cur.execute(
'INSERT INTO income (amount, chat_id, username) VALUES (%s, %s, %s);',
(
amount,
chat_id,
username,
))
con.commit()
return True
except Exception as e:
logger.error(e)
return False
finally:
cur.close()
def get_report_json(created_at_from, created_at_to, chat_id):
cur = con.cursor()
try:
cur.execute(
"""
SELECT json_build_object('incomes', income, 'incomes_total_amount', incomes_total_amount, 'outcome', outcome,
'outcome_total_amount', outcome_total_amount)
FROM (
SELECT (
SELECT json_agg(base)
FROM (
SELECT sum(inc.amount) user_amount,
inc.username username,
concat(
round(
100 * sum(inc.amount) /
(
SELECT sum(amount)
FROM income
WHERE chat_id IN (%s)
AND created_at >= %s
AND created_at <= %s
)), '%%') percentage
FROM income inc
WHERE inc.chat_id = %s
AND inc.created_at >= %s
AND inc.created_at <= %s
GROUP BY inc.username
) base
)
income,
(
SELECT sum(amount)
FROM income
WHERE chat_id IN (%s)
AND created_at >= %s
AND created_at <= %s
) incomes_total_amount,
(
SELECT json_agg(base)
FROM (
SELECT sum(o.amount) category_amount,
oc.name category_name,
concat(
round(
100 * sum(o.amount) /
(
SELECT sum(amount)
FROM outcome
WHERE chat_id IN (%s)
AND created_at >= %s
AND created_at <= %s
)), '%%') percentage
FROM outcome o
LEFT JOIN outcome_category oc on o.category_id = oc.id
WHERE o.chat_id = %s
AND o.created_at >= %s
AND o.created_at <= %s
GROUP BY oc.name
) base
) outcome,
(
SELECT sum(amount)
FROM outcome
WHERE chat_id IN (%s)
AND created_at >= %s
AND created_at <= %s
) outcome_total_amount
) a
""", (
chat_id,
created_at_from,
created_at_to,
chat_id,
created_at_from,
created_at_to,
chat_id,
created_at_from,
created_at_to,
chat_id,
created_at_from,
created_at_to,
chat_id,
created_at_from,
created_at_to,
chat_id,
created_at_from,
created_at_to,
))
argument = cur.fetchall()
con.commit()
return argument[0][0]
except Exception as e:
logger.error(e)
return False
finally:
cur.close()