grocery-manager/app/price_view.py

#
# Copyright (c) Daniel Sheffield 2021 - 2022
#
# All rights reserved
#
# THIS SOFTWARE IS PROVIDED AS IS WITHOUT WARRANTY
from psycopg2.sql import (
    Identifier,
    SQL,
    Literal,
    Placeholder,
    Composed,
)
from collections import (
    OrderedDict,
)

def get_where(product, unit, organic=None, limit='90 days'):
    where = [ ]
    where.append(SQL(' ').join([
        Identifier('products', 'name'),
        SQL('='),
        Literal(product)
    ]))
    if organic is not None:
        where.append(SQL(' ').join([
            Identifier('organic'),
            SQL('='),
            Literal(organic),
        ]))
    where.append(
        SQL("{ts} at time zone 'utc' BETWEEN now() - {interval} AND now()").format(
            ts=Identifier('ts'),
            interval=SQL("{literal}::interval").format(literal=Literal(limit))
        )
    )
    where.append(
        SQL("(SELECT id FROM units WHERE name = {unit}) = conv._to").format(
            unit=Literal(unit),
        )
    )
    return SQL('').join([
        SQL("WHERE"
            "\n      "),
        SQL("\n  AND ").join(where),
    ])

def get_historic_prices_statement(sort, product, unit, organic=None, limit='90 days'):
    partition = f"(PARTITION BY {'organic,' if organic is not None else ''} product_id, unit_id)"
    organic_sort = f"{'organic,' if organic is not None else ''}"
    _with = SQL("""WITH conv AS (
    SELECT
        _1._from, _1._to, factor FROM conversions AS _1
    UNION
    SELECT _2._to, _2._from, 1/_2.factor FROM conversions AS _2
    UNION
    SELECT _3._from, _3._from, 1 FROM conversions AS _3
    UNION
    SELECT _4.id AS _from, _4.id AS _to, 1 FROM units AS _4
)""")
    select = OrderedDict([
        ('id', Identifier('transactions','id')),
        #('ts', SQL('{identifier}::date').format(
        #    identifier=Identifier('ts'),
        #)),
        ('%d/%m %_I%P', Identifier('transactions', 'ts')),
        #('store', Identifier('stores', 'name')),
        ('code', Identifier('stores', 'code')),
        #('description', Identifier('transactions','description')),
        #('volume', Identifier('quantity')),
        #('unit', Identifier('units', 'name')),
        #('price', Identifier('price')),
        ('$/unit', SQL("""TRUNC(price / (quantity * factor), 4)""")),
        ('avg', SQL(f"""TRUNC(sum(price) OVER {partition} / sum(quantity * factor) OVER {partition}, 4)""")),
        ('min', SQL(f"""TRUNC(min(price / (quantity * factor)) OVER {partition}, 4)""")),
        ('max', SQL(f"""TRUNC(max(price / (quantity * factor)) OVER {partition}, 4)""")),
        #('total', SQL("""sum(transactions.price) OVER (PARTITION BY transactions.ts::date ORDER BY transactions.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)""")),
        #('group', Identifier('groups','name')),
        #('category', Identifier('categories','name')),
        #('product', Identifier('products','name')),
        ('organic', Identifier('organic')),
        
        #('price_sum', SQL(f"""sum(price) OVER {partition}""")),
        #('quantity_sum', SQL(f"""sum(quantity) OVER {partition}""")),
        #('price_min', SQL(f"""min(price/quantity) OVER {partition}""")),
        #('price_max', SQL(f"""max(price/quantity) OVER {partition}""")),
    ])
    statement = SQL('\n').join([
        _with,
        SQL('').join([
            SQL("SELECT"
                "\n  "),
            SQL(','
                "\n  ").join([
                SQL(' ').join([v, SQL('AS'), Identifier(f'{k}')]) for k, v in select.items()
            ])
        ]),
        SQL('').join([
            SQL("FROM"
                "\n       "),
            SQL("\n  JOIN ").join([
                SQL("transactions"),
                SQL("{table} ON ({table}.{key} = {index})").format(
                    table=Identifier('conv'),
                    key=Identifier('_from'),
                    index=Identifier('unit_id'),
                ),
                SQL("{table} ON {table}.{key} = {index}").format(
                    table=Identifier('units'),
                    key=Identifier('id'),
                    index=Identifier('unit_id'),
                ),
                SQL("{table} ON {table}.{key} = {index}").format(
                    table=Identifier('stores'),
                    key=Identifier('id'),
                    index=Identifier('store_id')
                ),
                SQL("{table} ON {table}.{key} = {index}").format(
                    table=Identifier('products'),
                    key=Identifier('id'),
                    index=Identifier('product_id')
                ),
                SQL("{table} ON {table}.{key} = {index}").format(
                    table=Identifier('categories'),
                    key=Identifier('id'),
                    index=Identifier('category_id')
                ),
                SQL("{table} ON {table}.{key} = {index}").format(
                    table=Identifier('groups'),
                    key=Identifier('id'),
                    index=Identifier('group_id')
                ),
                
            ]),
        ]),
        get_where(product, unit, organic=organic, limit=limit),
        SQL('ORDER BY {organic_sort} {sort} {direction}, code, "$/unit" ASC, ts DESC').format(
            sort=Identifier(sort),
            direction=SQL('DESC' if sort == 'ts' else 'ASC'),
            organic_sort=SQL(organic_sort),
        ),
    ])
    return statement
    # cols = ('product', 'unit', 'organic', 'store', '$/unit',)
    # aggs = {
        # 'avg': SQL("""TRUNC(price_sum/quantity_sum, 4)"""),
        # 'min': SQL("""TRUNC(price_min, 4)"""),
        # 'max': SQL("""TRUNC(price_max, 4)"""),
        # #'$/unit': SQL("""sum({price})/sum({quantity})""").format(
        # #    price=Identifier("$/unit"),
        # #    quantity=Identifier("quantity"),
        # #)
    # }
    # groups = ( 'price_sum', 'quantity_sum', 'price_min', 'price_max')
    # ret = SQL('\n').join([
        # SQL('').join([
            # SQL("SELECT"
                # "\n  "),
            # SQL(','
                # "\n  ").join([Identifier(f'{k}') for k in groups ])
            # ])
        # SQL('').join([
            # SQL("SELECT"
                # "\n  "),
            # SQL(','
                # "\n  ").join([Identifier(f'{k}') for k in select ])
            # ])
        # SQL('ORDER BY {_id} DESC').format(_id=Identifier('transactions','id')),
        # ])
    #SELECT product, organic, store, unit, TRUNC("$/unit",2) AS "$/unit", TRUNC(price_sum/quantity_sum, 2) AS "avg", TRUNC(price_min,2) AS "min", TRUNC(price_max,2) AS "max", date 
#FROM  (
#sub
#) AS subq
#WHERE product = 'Apples'
#GROUP BY organic, product, unit, "$/unit", price_sum, quantity_sum, price_min, price_max, date, store ;