drinks-manager/app/db_queries.py

#from datetime import datetime

from django.conf import settings
from django.db import connection


COMBINE_ALPHABET = "abcdefghijklmnopqrstuvwxyz"


def _db_select(sql_select:str):
    result = None
    with connection.cursor() as cursor:
        cursor.execute(sql_select)
        result = cursor.fetchall()
    return result


def _combine_results(results:list) -> dict:
    '''
    e.g.
    input: [
        [("x", 12), ("y", 13)],
        [("y", 10), ("z", 42)]
    ]
    output: {
        "x": {"a": 12},
        "y": {"a": 13, "b": 10},
        "z": {"b": 42}
    }
    '''
    result = {}
    for i, d in enumerate(results):
        a = COMBINE_ALPHABET[i]
        for r in d:
            r_0 = r[0]
            if r_0 not in result:
                result[r_0] = {}
            result[r_0][a] = r[1]
    return result


def select_history(user, language_code="en") -> list:
    # select order history and deposits
    user_id = user.pk
    result = _db_select(f"""
    select
        concat(
        product_name, ' (',
        content_litres::real, -- converting to real removes trailing zeros
        'l) x ', amount, ' - ', price_sum, '{settings.CURRENCY_SUFFIX}') as "text",
        datetime
    from app_order
    where user_id = {user_id}

    union 

    select
        concat('Deposit: +', transaction_sum, '{settings.CURRENCY_SUFFIX}') as "text",
        datetime
    from app_userdeposits_view
    where user_id = {user_id}

    order by datetime desc
    fetch first 30 rows only;
    """)
    result = [list(row) for row in result]
    if language_code == "de": # reformat for german translation
        for row in result:
            row[0] = row[0].replace(".", ",")
    return result


def orders_per_month(user) -> list:
    # number of orders per month (last 12 months)
    result_user = _db_select(f"""
    select
        to_char(date_trunc('month', datetime), 'YYYY-MM') as "month",
        sum(amount) as "count"
    from app_order
        where user_id = {user.pk}
        and date_trunc('month', datetime) > date_trunc('month', now() - '12 months'::interval)
    group by "month"
    order by "month" desc;
    """)
    result_all = _db_select(f"""
    select
        to_char(date_trunc('month', datetime), 'YYYY-MM') as "month",
        sum(amount) as "count"
    from app_order
        where date_trunc('month', datetime) > date_trunc('month', now() - '12 months'::interval)
    group by "month"
    order by "month" desc;
    """)
    return _combine_results([result_user, result_all])


def orders_per_weekday(user) -> list:
    # number of orders per weekday (all time)
    result_user = _db_select(f"""
    select
        to_char(datetime, 'Day') as "day",
        sum(amount) as "count"
    from app_order
        where user_id = {user.pk}
    group by "day"
    order by "count" desc;
    """)
    result_all = _db_select(f"""
    select
        to_char(datetime, 'Day') as "day",
        sum(amount) as "count"
    from app_order
    group by "day"
    order by "count" desc;
    """)
    return _combine_results([result_user, result_all])


def orders_per_drink(user) -> list:
    # number of orders per drink (all time)
    result_user = _db_select(f"""
    select
        d.product_name as "label",
        sum(o.amount) as "data"
    from app_drink d
        join app_order o on (d.id = o.drink_id)
    where o.user_id = {user.pk}
    group by d.product_name
    order by "data" desc;
    """)
    result_all = _db_select(f"""
    select
        d.product_name as "label",
        sum(o.amount) as "data"
    from app_drink d
        join app_order o on (d.id = o.drink_id)
    group by d.product_name
    order by "data" desc;
    """)
    return _combine_results([result_user, result_all])