More insight into the Foodsoft database.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
foodsoftstats/stats.py

73 lines
2.1 KiB

1 year ago
from os import environ
from sys import exit
from bindings import Article, GroupOrder, Order, OrderArticle, Supplier
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
def never_ordered(session):
"""List all articles never ordered by supplier."""
suppliers = session.query(Supplier)
order_articles = session.query(OrderArticle)
articles = session.query(Article)
ordered_articles = []
for order in session.query(Order).all():
supplier = suppliers.filter(Supplier.id == order.supplier_id).first()
if not supplier:
continue
all_order_articles = order_articles.filter(
OrderArticle.order_id == order.id
).all()
for order_article in all_order_articles:
if not order_article.units_received:
continue
if order_article.article_id not in ordered_articles:
ordered_articles.append(order_article.article_id)
never_ordered = articles.filter(Article.id.notin_(ordered_articles)).all()
suppliers_articles = {}
for article in never_ordered:
supplier = suppliers.filter(Supplier.id == article.supplier_id).first()
if supplier.name not in suppliers_articles:
suppliers_articles[supplier.name] = []
suppliers_articles[supplier.name].append(article)
for supplier in suppliers_articles:
articles = suppliers_articles[supplier]
articles.sort(key=lambda a: a.created_at, reverse=True)
if not articles:
continue
print(f"Supplier: {supplier}, # articles never ordered: {len(articles)}")
for artc in articles:
print(f"Article: {artc.name}, Added {artc.created_at}")
print("")
DB_USER = environ.get("DB_USER", "root")
DB_PASSWORD = environ.get("DB_PASSWORD")
DB_HOST = environ.get("DB_HOST", "localhost")
DB_NAME = environ.get("DB_NAME", "foodsoft")
if not DB_PASSWORD:
exit("Please set a DB_PASSWORD env var")
connection = f"mysql+pymysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}/{DB_NAME}"
session = Session(create_engine(connection))
never_ordered(session)