390 lines
17 KiB
Python
390 lines
17 KiB
Python
import csv
|
|
import math
|
|
import time
|
|
import traceback
|
|
from datetime import datetime
|
|
from sqlite3 import OperationalError
|
|
|
|
from django.db import transaction, OperationalError
|
|
from django.db.backends import sqlite3
|
|
from django.db.models import F
|
|
from django.db.models.aggregates import Sum
|
|
from django.db.models.query_utils import Q
|
|
from threading import Thread
|
|
|
|
import numpy
|
|
|
|
from main.models import Consumption, Order, Inventory, Product, OutgoingInvoice, \
|
|
OutgoingInvoiceProductPosition, OutgoingInvoiceProductUserPosition, ProductInventory
|
|
from tallybill.tally_settings import PROFIT_FACTOR, PROFIT_FIXED_CENTS
|
|
|
|
|
|
def _get_total_consumption_until(product, date):
|
|
# total consumption of all recorded data, including loss
|
|
inventory_count = 0
|
|
try:
|
|
inventory_count = ProductInventory.objects.get(inventory__date=date, product=product).count
|
|
except ProductInventory.DoesNotExist as e:
|
|
pass
|
|
orders_until = (Order.objects.filter(product=product, incoming_invoice__date__lte=date)
|
|
.aggregate(Sum("count"))["count__sum"] or 0)
|
|
return orders_until - inventory_count
|
|
|
|
|
|
class BillingPeriod(object):
|
|
|
|
def __init__(self, inventory, previous_inventory=None):
|
|
# if inventory is None: nothing to inventory - a total of zero products are assumed
|
|
self._inventory = inventory
|
|
self._previous_inventory = previous_inventory
|
|
if inventory is not None and previous_inventory is None:
|
|
previous_inventories = Inventory.objects.filter(date__lt=inventory.date).order_by("-date")
|
|
if previous_inventories.count() > 0:
|
|
self._previous_inventory = previous_inventories.first()
|
|
|
|
@property
|
|
def inventory(self):
|
|
return self._inventory
|
|
|
|
@property
|
|
def products(self):
|
|
query = Q(productinventory__inventory=self._inventory)
|
|
if self._previous_inventory is not None:
|
|
query |= Q(productinventory__inventory=self._previous_inventory)
|
|
return [ProductInPeriod(self, p) for p in Product.objects.filter(query).distinct()]
|
|
|
|
@property
|
|
def date_from(self):
|
|
if self._previous_inventory:
|
|
return self._previous_inventory.date
|
|
# just some random time long ago
|
|
return None
|
|
|
|
@property
|
|
def date_until(self):
|
|
return self._inventory.date
|
|
|
|
@property
|
|
def previous_billing_period(self):
|
|
if self._previous_inventory:
|
|
return BillingPeriod(self._previous_inventory)
|
|
return None
|
|
|
|
@staticmethod
|
|
def all():
|
|
return (BillingPeriod(inventory) for inventory in Inventory.objects.all())
|
|
|
|
@property
|
|
def invoices(self):
|
|
return OutgoingInvoice.objects_all.filter(inventory=self._inventory).order_by("date")
|
|
|
|
@transaction.atomic
|
|
def recalculate_temporary_invoices(self):
|
|
# there should always only be one temporary invoice
|
|
try:
|
|
invoice = self.invoices.get(is_frozen=False)
|
|
except OutgoingInvoice.DoesNotExist:
|
|
try:
|
|
correction_of = self._inventory.outgoinginvoice_set.get()
|
|
except OutgoingInvoice.DoesNotExist:
|
|
correction_of = None
|
|
invoice = OutgoingInvoice(inventory=self._inventory, correction_of=correction_of)
|
|
invoice.save()
|
|
invoice.outgoinginvoiceproductposition_set.all().delete()
|
|
|
|
# recalculate shit
|
|
invoice_total = 0
|
|
invoice_profit = 0
|
|
for product in self.products:
|
|
pos_loss_factor = float(max(1.0, product.get_loss_factor()))
|
|
avg_price = product.get_avg_price_for_consumed()
|
|
if pos_loss_factor == math.inf:
|
|
each_cents = 0
|
|
each_no_profit = 0
|
|
else:
|
|
each_cents = int(avg_price * pos_loss_factor * PROFIT_FACTOR + PROFIT_FIXED_CENTS)
|
|
each_no_profit = int(avg_price * pos_loss_factor)
|
|
|
|
position = OutgoingInvoiceProductPosition.objects.create(
|
|
product=product.product, invoice=invoice,
|
|
loss=product.get_loss(), price_each=each_cents, total=0, profit=0)
|
|
product_total = 0
|
|
for user, count in product.get_user_consumptions():
|
|
product_total += count
|
|
OutgoingInvoiceProductUserPosition.objects.create(user_id=user, count=count, productinvoice=position)
|
|
invoice_total += product_total * each_cents
|
|
invoice_profit += product_total * each_cents - product_total * each_no_profit
|
|
position.total = product_total * each_cents
|
|
position.profit = product_total * each_cents - product_total * each_no_profit
|
|
position.save()
|
|
|
|
invoice.total = invoice_total
|
|
invoice.profit = invoice_profit
|
|
invoice.save()
|
|
self._inventory.may_have_changed = False
|
|
self._inventory.save(fast=True)
|
|
|
|
|
|
class ProductInPeriod(object):
|
|
|
|
def __init__(self, billing_period, product):
|
|
self._billing_period = billing_period
|
|
self._product = product
|
|
|
|
@property
|
|
def product(self):
|
|
return self._product
|
|
|
|
@property
|
|
def billing_period(self):
|
|
return self._billing_period
|
|
|
|
def get_total_orders(self):
|
|
q = Q(incoming_invoice__date__lte=self._billing_period.date_until, product=self._product)
|
|
if self._billing_period.date_from is not None:
|
|
q &= Q(incoming_invoice__date__gt=self._billing_period.date_from)
|
|
return Order.objects.filter(q).aggregate(Sum("count"))["count__sum"] or 0
|
|
|
|
@staticmethod
|
|
def get_total_orders_table(inventories_qs, product_qs):
|
|
inventories = inventories_qs.values_list("pk", "date")
|
|
product_ids = list(product_qs.values_list("pk", flat=True))
|
|
orders = (Order.objects.values("incoming_invoice__date", "product")
|
|
.annotate(count=Sum("count")).order_by("incoming_invoice__date")
|
|
.values_list("incoming_invoice__date", "product", "count").iterator())
|
|
table = numpy.zeros((inventories.count(), Product.objects.count()), dtype=int)
|
|
|
|
try:
|
|
order_date, order_product_id, count = next(orders)
|
|
for i, (pk, date) in enumerate(inventories):
|
|
while order_date <= date:
|
|
table[i][product_ids.index(order_product_id)] += count
|
|
order_date, order_product_id, count = next(orders)
|
|
except StopIteration:
|
|
pass
|
|
return table
|
|
|
|
@staticmethod
|
|
def get_product_inventory_count_table(inventories_qs, product_qs):
|
|
# TODO what if inventories_qs is not all inventories ordered by date... fail
|
|
inventories = list(inventories_qs.values_list("pk", flat=True))
|
|
product_ids = list(product_qs.values_list("pk", flat=True))
|
|
product_inventories = ProductInventory.objects.values_list("inventory", "product", "count")
|
|
table = numpy.zeros((len(inventories), len(product_ids)), dtype=int)
|
|
|
|
for inventory, product, count in product_inventories:
|
|
table[inventories.index(inventory)][product_ids.index(product)] += count
|
|
return table, inventories, product_ids
|
|
|
|
@classmethod
|
|
def get_real_consumption_list(cls, inventories_qs, product_qs):
|
|
# TODO what if inventories_qs is not all inventories ordered by date... fail
|
|
product_inventory_table, inventory_ids, product_ids = cls.get_product_inventory_count_table(inventories_qs, product_qs)
|
|
product_order_table = cls.get_total_orders_table(inventories_qs, product_qs)
|
|
|
|
result_table = numpy.zeros(product_order_table.shape, dtype=int)
|
|
for inventory_idx in range(len(inventory_ids)):
|
|
for product_idx in range(len(product_ids)):
|
|
if inventory_idx > 0:
|
|
previous_inventory_count = product_inventory_table[inventory_idx - 1, product_idx]
|
|
else:
|
|
previous_inventory_count = 0
|
|
result_table[inventory_idx, product_idx] = (previous_inventory_count +
|
|
product_order_table[inventory_idx, product_idx] -
|
|
product_inventory_table[inventory_idx, product_idx])
|
|
return result_table
|
|
|
|
@staticmethod
|
|
def get_listed_consumptions_table(inventories_qs, product_qs, consumptions=None):
|
|
# TODO what if inventories_qs is not all inventories ordered by date... fail
|
|
inventories = inventories_qs.values_list("pk", "date").order_by("date")
|
|
product_ids = list(product_qs.values_list("pk", flat=True))
|
|
|
|
consumptions_iter = ((Consumption.objects if consumptions is None else consumptions)
|
|
.values_list("date", "product_id", "count").order_by("date").iterator())
|
|
|
|
table = numpy.zeros((inventories.count(), len(product_ids)), dtype=int)
|
|
try:
|
|
consumption_date, product_id, count = next(consumptions_iter)
|
|
for i, (pk, date) in enumerate(inventories):
|
|
while consumption_date <= date:
|
|
table[i][product_ids.index(product_id)] += count
|
|
consumption_date, product_id, count = next(consumptions_iter)
|
|
except StopIteration:
|
|
pass
|
|
return table
|
|
|
|
def get_real_consumption(self):
|
|
assert isinstance(self._billing_period.inventory, Inventory)
|
|
previous_inventory_count = 0
|
|
if self._billing_period.previous_billing_period is not None:
|
|
try:
|
|
previous_inventory_count = (self._billing_period.previous_billing_period.inventory
|
|
.productinventory_set.get(product=self._product).count)
|
|
except ProductInventory.DoesNotExist as e:
|
|
pass
|
|
|
|
inventory_count = 0
|
|
try:
|
|
inventory_count = self._billing_period.inventory.productinventory_set.get(product=self._product).count
|
|
except ProductInventory.DoesNotExist as e:
|
|
pass
|
|
|
|
consumption = (previous_inventory_count - inventory_count + self.get_total_orders())
|
|
|
|
# ------------------------ validating ---------------------------------
|
|
# consumption2 = (_get_total_consumption_until(self._product, self._billing_period.date_until) -
|
|
# _get_total_consumption_until(self._product, self._billing_period.date_from))
|
|
# assert consumption == consumption2, (consumption, consumption2)
|
|
# ------------------------- end valdiating -----------------------------
|
|
|
|
return consumption
|
|
|
|
def get_listed_consumptions(self):
|
|
assert isinstance(self._product, Product)
|
|
date_from = datetime.min if self._billing_period.date_from is None else self._billing_period.date_from
|
|
return (self._product.consumption_set.filter(date__gt=date_from,
|
|
date__lte=self._billing_period.date_until)
|
|
.aggregate(Sum("count"))["count__sum"] or 0)
|
|
|
|
def get_avg_price_for_consumed(self):
|
|
# avg price of products that where consumed
|
|
# i assumed a product really is a single product
|
|
# TODO bissl umstaendlich umgesetzt
|
|
|
|
date_from = datetime.min if self._billing_period.date_from is None else self._billing_period.date_from
|
|
consumed_before_period = _get_total_consumption_until(self._product, date_from)
|
|
|
|
real_consumptions = max(self.get_real_consumption(), 0)
|
|
remaining_consumptions = real_consumptions
|
|
|
|
orders = self._product.order_set.order_by("incoming_invoice__date").values_list("count", "each_cents")
|
|
if orders.count() == 0:
|
|
quantities, prices = [], []
|
|
else:
|
|
quantities, prices = zip(*orders)
|
|
quantities, prices = list(quantities), list(prices)
|
|
# filter out all already billed quantities
|
|
for i in range(len(quantities)):
|
|
if consumed_before_period > 0:
|
|
v = min(consumed_before_period, quantities[i])
|
|
consumed_before_period -= v
|
|
quantities[i] -= v
|
|
if consumed_before_period == 0:
|
|
v = min(quantities[i], remaining_consumptions)
|
|
quantities[i] = v
|
|
remaining_consumptions -= v
|
|
|
|
assert(sum(quantities) == real_consumptions)
|
|
if sum(quantities) == 0:
|
|
return 0
|
|
return sum([quantities[i] * prices[i] for i in range(len(prices))]) / sum(quantities)
|
|
|
|
def get_loss_factor(self):
|
|
# return factor to compensate loss
|
|
real_consumptions = self.get_real_consumption()
|
|
listed_consumptions = self.get_listed_consumptions()
|
|
if listed_consumptions == 0:
|
|
# loss cannot be compensated, since (apparently) nobody consumed nothing
|
|
# (even though real_consumption may indicate consumptions)
|
|
if real_consumptions != 0:
|
|
return math.inf
|
|
return 1.
|
|
return real_consumptions / listed_consumptions
|
|
|
|
def get_loss(self):
|
|
lf = self.get_loss_factor()
|
|
if lf > 0:
|
|
return - (1. - lf) * 100.
|
|
else:
|
|
# negative real consumptions
|
|
# loss not really interpretable
|
|
# TODO: better options?
|
|
return -math.inf
|
|
|
|
def get_user_consumptions(self, user=None):
|
|
date_from = datetime.min if self._billing_period.date_from is None else self._billing_period.date_from
|
|
consumptions = Consumption.objects.filter(date__gt=date_from,
|
|
date__lte=self._billing_period.date_until, product=self._product)
|
|
if user is not None:
|
|
consumptions = consumptions.filter(user=user)
|
|
|
|
return (consumptions.values("user").annotate(consumed=Sum("count"))
|
|
.order_by("user").values_list("user", "consumed"))
|
|
|
|
|
|
class RecalculateThread(Thread):
|
|
|
|
def __init__(self, *args, **kwargs):
|
|
super(RecalculateThread, self).__init__(*args, **kwargs)
|
|
self.running = True
|
|
|
|
def run(self):
|
|
print("Started Recalculation Thread.")
|
|
while self.running:
|
|
try:
|
|
inventories = Inventory.objects.filter(may_have_changed=True)
|
|
for inventory in inventories:
|
|
print("Recalculating: %s" % str(inventory))
|
|
BillingPeriod(inventory).recalculate_temporary_invoices()
|
|
except OperationalError:
|
|
pass
|
|
except:
|
|
traceback.print_exc()
|
|
time.sleep(1.0)
|
|
|
|
print("End Recalculation Thread.")
|
|
|
|
def outgoing_to_csv(outgoing, f, difference=False):
|
|
|
|
def get_user_sum(outgoing_invoice):
|
|
values = OutgoingInvoiceProductUserPosition.objects \
|
|
.filter(productinvoice__invoice=outgoing_invoice).values_list("user__pk", "user__username") \
|
|
.annotate(total=Sum(F("count") * F("productinvoice__price_each"))).order_by("user__username")
|
|
return dict(((x, (y, z)) for x, y, z in values))
|
|
|
|
def get_diff_positions():
|
|
current_positions = get_user_sum(outgoing)
|
|
|
|
if outgoing.correction_of is not None and difference:
|
|
previous_positions = get_user_sum(outgoing.correction_of)
|
|
else:
|
|
previous_positions = {}
|
|
|
|
updated_positions = [((current_positions[k][0], current_positions[k][1] - previous_positions[k][1])
|
|
if k in previous_positions else current_positions[k])
|
|
for k in current_positions]
|
|
removed_positions = [(previous_positions[k][0], -previous_positions[k][1])
|
|
for k in set(previous_positions.keys()).difference(set(current_positions))]
|
|
|
|
return updated_positions + removed_positions
|
|
|
|
positions = get_diff_positions()
|
|
|
|
writer = csv.writer(f)
|
|
writer.writerow(["Datum", datetime.strftime(outgoing.inventory.date, "%d.%m.%Y")])
|
|
writer.writerow(["Beschreibung", datetime.strftime(outgoing.inventory.date, "Getränkeabrechnung %B")])
|
|
writer.writerow([])
|
|
writer.writerow(["Account", "Wert (Positiv = belastend)", "Notizen"])
|
|
|
|
sum = 0
|
|
for name, amount in positions:
|
|
writer.writerow([name, amount / 100.0])
|
|
sum -= amount
|
|
writer.writerow(["Getränke.Erträge", sum / 100.0])
|
|
print(outgoing.inventory.date, sum / 100.)
|
|
|
|
|
|
import sys
|
|
is_runserver_command = False
|
|
for idx, value in enumerate(sys.argv):
|
|
if value.endswith("manage.py"):
|
|
is_runserver_command = sys.argv[idx + 1] == "runserver"
|
|
break
|
|
|
|
if is_runserver_command:
|
|
recalculate_thread = RecalculateThread()
|
|
recalculate_thread.daemon = True
|
|
recalculate_thread.start()
|
|
|