1156 lines
45 KiB
Python
1156 lines
45 KiB
Python
# sheets/services/halfyear_calc.py
|
||
from __future__ import annotations
|
||
from django.db.models.functions import Coalesce
|
||
from decimal import Decimal
|
||
from typing import Dict, Any
|
||
from django.shortcuts import redirect, render
|
||
from decimal import Decimal
|
||
from sheets.models import (
|
||
Client, SecondTableEntry, Institute, ExcelEntry,
|
||
Betriebskosten, MonthlySheet, Cell, CellReference, MonthlySummary ,BetriebskostenSummary,AbrechnungCell
|
||
)
|
||
from django.db.models import Sum
|
||
from django.db.models.functions import Coalesce
|
||
from django.db.models import DecimalField, Value
|
||
HALFYEAR_CLIENTS = ["AG Vogel", "AG Halfm", "IKP"]
|
||
TR_RUECKF_FLUESSIG_ROW = 2 # confirmed by your March value 172.840560
|
||
TR_BESTAND_KANNEN_ROW = 5
|
||
def get_top_right_value(sheet, client_name: str, row_index: int) -> Decimal:
|
||
"""
|
||
Read a numeric value from the top_right table of a MonthlySheet for
|
||
a given client (by column) and row_index.
|
||
|
||
top_right cells are keyed by (sheet, table_type='top_right',
|
||
row_index, column_index), where column_index is the position of the
|
||
client in HALFYEAR_RIGHT_CLIENTS.
|
||
"""
|
||
if sheet is None:
|
||
return Decimal('0')
|
||
|
||
col_index = RIGHT_CLIENT_INDEX.get(client_name)
|
||
if col_index is None:
|
||
return Decimal('0')
|
||
|
||
cell = Cell.objects.filter(
|
||
sheet=sheet,
|
||
table_type='top_right',
|
||
row_index=row_index,
|
||
column_index=col_index,
|
||
).first()
|
||
|
||
if cell is None or cell.value in (None, ''):
|
||
return Decimal('0')
|
||
|
||
try:
|
||
return Decimal(str(cell.value))
|
||
except Exception:
|
||
return Decimal('0')
|
||
|
||
def get_bottom2_value(sheet, row_index: int, col_index: int) -> Decimal:
|
||
"""Get numeric value from bottom_2 or 0 if missing."""
|
||
if sheet is None:
|
||
return Decimal("0")
|
||
cell = Cell.objects.filter(
|
||
sheet=sheet,
|
||
table_type="bottom_2",
|
||
row_index=row_index,
|
||
column_index=col_index,
|
||
).first()
|
||
if cell is None or cell.value in (None, ""):
|
||
return Decimal("0")
|
||
try:
|
||
return Decimal(str(cell.value))
|
||
except Exception:
|
||
return Decimal("0")
|
||
def get_bottom1_value(sheet, row_index: int, col_index: int) -> Decimal:
|
||
"""Get a numeric value from bottom_1, or 0 if missing."""
|
||
if sheet is None:
|
||
return Decimal('0')
|
||
|
||
cell = Cell.objects.filter(
|
||
sheet=sheet,
|
||
table_type='bottom_1',
|
||
row_index=row_index,
|
||
column_index=col_index,
|
||
).first()
|
||
|
||
if cell is None or cell.value in (None, ''):
|
||
return Decimal('0')
|
||
|
||
try:
|
||
return Decimal(str(cell.value))
|
||
except Exception:
|
||
return Decimal('0')
|
||
def get_top_left_value(sheet, client_name: str, row_index: int) -> Decimal:
|
||
"""
|
||
Read a numeric value from the top_left table for a given month, client and row.
|
||
Does NOT use column_index, because top_left is keyed only by client + row_index.
|
||
"""
|
||
if sheet is None:
|
||
return Decimal('0')
|
||
|
||
client_obj = Client.objects.filter(name=client_name).first()
|
||
if not client_obj:
|
||
return Decimal('0')
|
||
|
||
cell = Cell.objects.filter(
|
||
sheet=sheet,
|
||
table_type='top_left',
|
||
client=client_obj,
|
||
row_index=row_index,
|
||
).first()
|
||
|
||
if cell is None or cell.value in (None, ''):
|
||
return Decimal('0')
|
||
|
||
try:
|
||
return Decimal(str(cell.value))
|
||
except Exception:
|
||
return Decimal('0')
|
||
def get_bestand_kannen_for_month(sheet, client_name: str) -> Decimal:
|
||
"""
|
||
'B9' in your description: Bestand in Kannen-1 (Lit. L-He)
|
||
For this implementation we take it from top_left row_index = 5 for that client.
|
||
"""
|
||
return get_top_left_value(sheet, client_name, row_index=BESTAND_KANNEN_ROW_INDEX)
|
||
|
||
def pick_sheet_by_gasbestand(window, sheets_by_ym, prev_sheet):
|
||
"""
|
||
Returns the last sheet in the window whose Gasbestand (J36, Nm³ column) != 0.
|
||
If none found, returns prev_sheet (Übertrag_Dez__Vorjahr equivalent).
|
||
"""
|
||
for (y, m) in reversed(window):
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if not sheet:
|
||
continue
|
||
gasbestand_nm3 = get_bottom1_value(sheet, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3)
|
||
if gasbestand_nm3 != 0:
|
||
return sheet
|
||
return prev_sheet
|
||
# NEW: clients for the top-right half-year table
|
||
GASBESTAND_ROW_INDEX = 9 # <-- adjust if your bottom_1 has a different row index
|
||
GASBESTAND_COL_NM3 = 3 # <-- adjust to the column index for Nm³ in bottom_1
|
||
|
||
# In top_left / top_right, "Bestand in Kannen-1 (Lit. L-He)" is row_index 5
|
||
BESTAND_KANNEN_ROW_INDEX = 5
|
||
HALFYEAR_RIGHT_CLIENTS = [
|
||
"Dr. Fohrer",
|
||
"AG Buntk.",
|
||
"AG Alff",
|
||
"AG Gutfl.",
|
||
"M3 Thiele",
|
||
"M3 Buntkowsky",
|
||
"M3 Gutfleisch",
|
||
]
|
||
BOTTOM1_COL_VOLUME = 0
|
||
BOTTOM1_COL_BAR = 1
|
||
BOTTOM1_COL_KORR = 2
|
||
BOTTOM1_COL_NM3 = 3
|
||
BOTTOM1_COL_LHE = 4
|
||
BOTTOM2_ROW_ANLAGE = 0
|
||
BOTTOM2_COL_G39 = 0 # "Gefäss 2,5" (cell id shows column_index=0)
|
||
BOTTOM2_COL_I39 = 1 # "Gefäss 1,0" (cell id shows column_index=1)
|
||
BOTTOM2_ROW_INPUTS = {
|
||
"g39": (0, 0), # row_index=0, column_index=0 (your G39)
|
||
"i39": (0, 1), # row_index=0, column_index=1 (your I39)
|
||
}
|
||
FACTOR_NM3_TO_LHE = Decimal("0.75")
|
||
RIGHT_CLIENT_INDEX = {name: idx for idx, name in enumerate(HALFYEAR_RIGHT_CLIENTS)}
|
||
|
||
def build_halfyear_window(interval_year: int, start_month: int):
|
||
"""
|
||
Build a list of (year, month) for the 6-month interval, possibly crossing into the next year.
|
||
Example: (2025, 10) -> [(2025,10), (2025,11), (2025,12), (2026,1), (2026,2), (2026,3)]
|
||
"""
|
||
window = []
|
||
for offset in range(6):
|
||
total_index = (start_month - 1) + offset # 0-based
|
||
y = interval_year + (total_index // 12)
|
||
m = (total_index % 12) + 1
|
||
window.append((y, m))
|
||
return window
|
||
# Import ONLY models + pure helpers here
|
||
from sheets.models import MonthlySheet, Cell, Client, SecondTableEntry, BetriebskostenSummary
|
||
|
||
def compute_halfyear_context(interval_year: int, interval_start: int) -> Dict[str, Any]:
|
||
"""
|
||
Returns a context dict with the SAME keys your current halfyear_balance.html expects.
|
||
"""
|
||
# ✅ Paste the pure calculation logic here in Step 2
|
||
window = build_halfyear_window(interval_year, interval_start)
|
||
# window = [(y1, m1), (y2, m2), ..., (y6, m6)]
|
||
|
||
# (Year, month) of the first month
|
||
start_year, start_month = window[0]
|
||
|
||
# Previous month (for "Stand ... (Vorjahr)" and "Best. in Kannen Vormonat")
|
||
prev_total_index = (start_month - 1) - 1 # one month back, 0-based
|
||
if prev_total_index >= 0:
|
||
prev_year = start_year + (prev_total_index // 12)
|
||
prev_month = (prev_total_index % 12) + 1
|
||
else:
|
||
prev_year = start_year - 1
|
||
prev_month = 12
|
||
|
||
# Load MonthlySheet objects for the window and for the previous month
|
||
sheets_by_ym = {}
|
||
for (y, m) in window:
|
||
sheet = MonthlySheet.objects.filter(year=y, month=m).first()
|
||
sheets_by_ym[(y, m)] = sheet
|
||
|
||
prev_sheet = MonthlySheet.objects.filter(year=prev_year, month=prev_month).first()
|
||
def pick_bottom2_from_window(window, sheets_by_ym, prev_sheet):
|
||
# choose sheet (same logic you already use)
|
||
chosen = None
|
||
for (y, m) in reversed(window):
|
||
s = sheets_by_ym.get((y, m))
|
||
# use your existing condition for choosing month
|
||
if s:
|
||
chosen = s
|
||
break
|
||
if chosen is None:
|
||
chosen = prev_sheet
|
||
|
||
# Now read the two inputs safely
|
||
bottom2_inputs = {}
|
||
for key, (row_idx, col_idx) in BOTTOM2_ROW_INPUTS.items():
|
||
bottom2_inputs[key] = get_bottom2_value(chosen, row_idx, col_idx)
|
||
|
||
return chosen, bottom2_inputs
|
||
|
||
|
||
chosen_sheet_bottom2, bottom2_inputs = pick_bottom2_from_window(window, sheets_by_ym, prev_sheet)
|
||
bottom2_g39 = bottom2_inputs["g39"]
|
||
bottom2_i39 = bottom2_inputs["i39"]
|
||
# ----------------------------
|
||
# HALF-YEAR BOTTOM TABLE 1 (Bilanz) - Read only
|
||
# ----------------------------
|
||
chosen_sheet_bottom1 = pick_sheet_by_gasbestand(window, sheets_by_ym, prev_sheet)
|
||
|
||
# IMPORTANT: define which bottom_1 row_index corresponds to Excel rows 27..35
|
||
# If your bottom_1 starts at Excel row 27 => row_index 0 == Excel 27
|
||
# then row_index = excel_row - 27
|
||
BOTTOM1_EXCEL_START_ROW = 27
|
||
|
||
bottom1_excel_rows = list(range(27, 37)) # 27..36
|
||
BOTTOM1_LABELS = [
|
||
"Batterie 1",
|
||
"2",
|
||
"3",
|
||
"4",
|
||
"5",
|
||
"Batterie Links",
|
||
"2 Bündel",
|
||
"2 Ballone",
|
||
"Reingasspeicher",
|
||
"Gasbestand",
|
||
]
|
||
|
||
BOTTOM1_VOLUMES = [
|
||
Decimal("2.4"),
|
||
Decimal("5.1"),
|
||
Decimal("4.0"),
|
||
Decimal("1.0"),
|
||
Decimal("4.0"),
|
||
Decimal("0.6"),
|
||
Decimal("1.2"),
|
||
Decimal("20.0"),
|
||
Decimal("5.0"),
|
||
None, # Gasbestand row has no volume
|
||
]
|
||
nm3_sum_27_35 = Decimal("0")
|
||
lhe_sum_27_35 = Decimal("0")
|
||
bottom1_rows = []
|
||
|
||
for excel_row in bottom1_excel_rows:
|
||
row_index = excel_row - BOTTOM1_EXCEL_START_ROW
|
||
|
||
chosen_sheet_bottom1 = None
|
||
for (y, m) in reversed(window):
|
||
s = sheets_by_ym.get((y, m))
|
||
gasbestand = get_bottom1_value(s, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3) # J36 (Nm3)
|
||
if gasbestand != 0:
|
||
chosen_sheet_bottom1 = s
|
||
break
|
||
|
||
if chosen_sheet_bottom1 is None:
|
||
chosen_sheet_bottom1 = prev_sheet
|
||
|
||
# Normal rows (27..35): read from chosen sheet and accumulate sums
|
||
if excel_row != 36:
|
||
nm3_val = get_bottom1_value(chosen_sheet_bottom1, row_index, BOTTOM1_COL_NM3)
|
||
lhe_val = get_bottom1_value(chosen_sheet_bottom1, row_index, BOTTOM1_COL_LHE)
|
||
|
||
nm3_sum_27_35 += nm3_val
|
||
lhe_sum_27_35 += lhe_val
|
||
|
||
bottom1_rows.append({
|
||
"label": BOTTOM1_LABELS[row_index],
|
||
"volume": BOTTOM1_VOLUMES[row_index],
|
||
"bar": get_bottom1_value(chosen_sheet_bottom1, row_index, BOTTOM1_COL_BAR),
|
||
"korr": get_bottom1_value(chosen_sheet_bottom1, row_index, BOTTOM1_COL_KORR),
|
||
"nm3": nm3_val,
|
||
"lhe": lhe_val,
|
||
})
|
||
|
||
# Gasbestand row (36): show sums (J36 = SUM(J27:J35), K36 = SUM(K27:K35))
|
||
else:
|
||
bottom1_rows.append({
|
||
"label": "Gasbestand",
|
||
"volume": "",
|
||
"bar": "",
|
||
"korr": "",
|
||
"nm3": nm3_sum_27_35,
|
||
"lhe": lhe_sum_27_35,
|
||
})
|
||
start_sheet = sheets_by_ym.get((start_year, start_month))
|
||
# ------------------------------------------------------------
|
||
# Bottom Table 2 (Halbjahres Bilanz) – server-side recalcBottom2()
|
||
# ------------------------------------------------------------
|
||
|
||
FACTOR_BT2 = Decimal("0.75")
|
||
|
||
# 1) Helper: pick last-nonzero value of bottom_2 row0 col0/col1 from the window (fallback: prev_sheet)
|
||
def pick_last_nonzero_bottom2(row_index: int, col_index: int) -> Decimal:
|
||
# Scan from last month in window backwards
|
||
for (y, m) in reversed(window):
|
||
s = sheets_by_ym.get((y, m))
|
||
if not s:
|
||
continue
|
||
v = get_bottom2_value(s, row_index, col_index)
|
||
if v is not None and v != 0:
|
||
return v
|
||
# fallback to month before window
|
||
v_prev = get_bottom2_value(prev_sheet, row_index, col_index)
|
||
return v_prev if v_prev is not None else Decimal("0")
|
||
|
||
# 2) K38 comes from Overall Summary: "Summe Bestand (Lit. L-He)"
|
||
# Find it from your already built overall summary rows list.
|
||
|
||
k38 = Decimal("0")
|
||
j38 = Decimal("0")
|
||
# 3) Inputs G39 / I39 (picked from last non-zero month in window)
|
||
g39 = pick_last_nonzero_bottom2(row_index=0, col_index=0) # G39
|
||
i39 = pick_last_nonzero_bottom2(row_index=0, col_index=1) # I39
|
||
|
||
k39 = (g39 or Decimal("0")) + (i39 or Decimal("0"))
|
||
j39 = k39 * FACTOR_BT2
|
||
|
||
# 4) +Kaltgas (row 40)
|
||
# JS:
|
||
# g40 = (2500 - g39)/100*10
|
||
# i40 = (1000 - i39)/100*10
|
||
g40 = None
|
||
i40 = None
|
||
if g39 is not None:
|
||
g40 = (Decimal("2500") - g39) / Decimal("100") * Decimal("10")
|
||
if i39 is not None:
|
||
i40 = (Decimal("1000") - i39) / Decimal("100") * Decimal("10")
|
||
|
||
k40 = (g40 or Decimal("0")) + (i40 or Decimal("0"))
|
||
j40 = k40 * FACTOR_BT2
|
||
|
||
# 5) Bestand flüssig He (row 43)
|
||
k43 = (
|
||
(k38 or Decimal("0")) +
|
||
(k39 or Decimal("0")) +
|
||
(k40 or Decimal("0"))
|
||
)
|
||
j43 = k43 * FACTOR_BT2
|
||
|
||
# 6) Gesamtbestand neu (row 44) = Gasbestand(Lit) from Bottom Table 1 + k43
|
||
gasbestand_lit = Decimal("0")
|
||
for r in bottom1_rows:
|
||
if (r.get("label") or "").strip().startswith("Gasbestand"):
|
||
gasbestand_lit = r.get("lhe") or Decimal("0")
|
||
break
|
||
|
||
k44 = (gasbestand_lit or Decimal("0")) + (k43 or Decimal("0"))
|
||
j44 = k44 * FACTOR_BT2
|
||
|
||
bottom2 = {
|
||
"j38": j38, "k38": k38,
|
||
"g39": g39, "i39": i39, "j39": j39, "k39": k39,
|
||
"g40": g40, "i40": i40, "j40": j40, "k40": k40,
|
||
"j43": j43, "k43": k43,
|
||
"j44": j44, "k44": k44,
|
||
}
|
||
|
||
# ------------------------------------------------------------------
|
||
# 2) LEFT TABLE (your existing, working logic)
|
||
# ------------------------------------------------------------------
|
||
HALFYEAR_CLIENTS_LEFT = ["AG Vogel", "AG Halfm", "IKP"]
|
||
|
||
# We'll collect client-wise values first for clarity.
|
||
client_data_left = {name: {} for name in HALFYEAR_CLIENTS_LEFT}
|
||
|
||
# --- Row B3: Stand der Gaszähler (Nm³)
|
||
# = MAX(B3 from previous month, and B3 from each of the 6 months in the window)
|
||
# row_index 0 in top_left = "Stand der Gaszähler (Nm³)"
|
||
months_for_max = [(prev_year, prev_month)] + window
|
||
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
max_val = Decimal('0')
|
||
for (y, m) in months_for_max:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet is None and (y, m) == (prev_year, prev_month):
|
||
sheet = prev_sheet
|
||
val_b3 = get_top_left_value(sheet, cname, row_index=0)
|
||
if val_b3 > max_val:
|
||
max_val = val_b3
|
||
client_data_left[cname]['stand_gas'] = max_val
|
||
|
||
# --- Row B4: Stand der Gaszähler (Vorjahr) (Nm³) -> previous month same row ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
val_b4 = get_top_left_value(prev_sheet, cname, row_index=0)
|
||
client_data_left[cname]['stand_gas_prev'] = val_b4
|
||
|
||
# --- Row B5: Gasrückführung (Nm³) = B3 - B4 ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b3 = client_data_left[cname]['stand_gas']
|
||
b4 = client_data_left[cname]['stand_gas_prev']
|
||
client_data_left[cname]['gasrueckf'] = b3 - b4
|
||
|
||
# --- Row B6: Rückführung flüssig (Lit. L-He) = B5 / 0.75 ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b5 = client_data_left[cname]['gasrueckf']
|
||
client_data_left[cname]['rueckf_fluessig'] = (b5 / Decimal('0.75')) if b5 != 0 else Decimal('0')
|
||
|
||
# --- Row B7: Sonderrückführungen (Lit. L-He) = sum over 6 months of that row ---
|
||
# That row index is 4 in your top_left table.
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
sonder_total = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
sonder_total += get_top_left_value(sheet, cname, row_index=4)
|
||
client_data_left[cname]['sonder'] = sonder_total
|
||
|
||
# --- Row B8: Bestand in Kannen-1 (Lit. L-He) ---
|
||
# Excel-style logic with Gasbestand (J36) and fallback to previous month.
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
chosen_value = None
|
||
|
||
# Go from last month (window[5]) backwards to first (window[0])
|
||
for (y, m) in reversed(window):
|
||
sheet = sheets_by_ym.get((y, m))
|
||
gasbestand = get_bottom1_value(sheet, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3)
|
||
if gasbestand != 0:
|
||
chosen_value = get_bestand_kannen_for_month(sheet, cname)
|
||
break
|
||
|
||
# If still None -> use previous month (Übertrag_Dez__Vorjahr equivalent)
|
||
if chosen_value is None:
|
||
sheet_prev = prev_sheet
|
||
chosen_value = get_bestand_kannen_for_month(sheet_prev, cname)
|
||
|
||
client_data_left[cname]['bestand_kannen'] = chosen_value if chosen_value is not None else Decimal('0')
|
||
|
||
# --- Row B9: Summe Bestand (Lit. L-He) = equal to previous row ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
client_data_left[cname]['summe_bestand'] = client_data_left[cname]['bestand_kannen']
|
||
|
||
# --- Row B10: Best. in Kannen Vormonat (Lit. L-He)
|
||
# = Bestand in Kannen-1 from the month BEFORE the window (prev_year, prev_month)
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
client_data_left[cname]['best_kannen_vormonat'] = get_bestand_kannen_for_month(prev_sheet, cname)
|
||
|
||
# --- Row B13: Bezug (Liter L-He) ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
total_bezug = Decimal('0')
|
||
for (y, m) in window:
|
||
qs = SecondTableEntry.objects.filter(
|
||
client__name=cname,
|
||
date__year=y,
|
||
date__month=m,
|
||
).aggregate(
|
||
total=Coalesce(Sum('lhe_output'), Value(0, output_field=DecimalField()))
|
||
)
|
||
total_bezug += Decimal(str(qs['total']))
|
||
client_data_left[cname]['bezug'] = total_bezug
|
||
|
||
# --- Row B14: Rückführ. Soll (Lit. L-He) = Bezug - Summe Bestand + Best. in Kannen Vormonat ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b13 = client_data_left[cname]['bezug']
|
||
b11 = client_data_left[cname]['summe_bestand']
|
||
b12 = client_data_left[cname]['best_kannen_vormonat']
|
||
client_data_left[cname]['rueckf_soll'] = b13 - b11 + b12
|
||
|
||
# --- Row B15: Verluste (Soll-Rückf.) (Lit. L-He)
|
||
# AG Vogel, AG Halfm: B14 - B6
|
||
# IKP: B14 - B6 - B7 (Sonderrückführungen)
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b14 = client_data_left[cname]['rueckf_soll']
|
||
b6 = client_data_left[cname]['rueckf_fluessig']
|
||
|
||
if (cname or "").strip() == "IKP":
|
||
b7 = client_data_left[cname].get('sonder', Decimal('0'))
|
||
client_data_left[cname]['verluste'] = b14 - b6 - b7
|
||
else:
|
||
client_data_left[cname]['verluste'] = b14 - b6
|
||
# --- Row B16: Füllungen warm (Lit. L-He) = sum over 6 months (row_index=11) ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
total_warm = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
total_warm += get_top_left_value(sheet, cname, row_index=11)
|
||
client_data_left[cname]['fuellungen_warm'] = total_warm
|
||
|
||
# --- Row B17: Kaltgas Rückgabe (Lit. L-He) = Bezug * 0.06 ---
|
||
factor = Decimal('0.06')
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b13 = client_data_left[cname]['bezug']
|
||
client_data_left[cname]['kaltgas_rueckgabe'] = b13 * factor
|
||
|
||
# --- Row B18: Verbraucherverluste (Liter L-He) = Verluste - Kaltgas Rückgabe ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
b15 = client_data_left[cname]['verluste']
|
||
b17 = client_data_left[cname]['kaltgas_rueckgabe']
|
||
client_data_left[cname]['verbraucherverluste'] = b15 - b17
|
||
|
||
# --- Row B19: % = Verbraucherverluste / Bezug ---
|
||
for cname in HALFYEAR_CLIENTS_LEFT:
|
||
bezug = client_data_left[cname]['bezug']
|
||
verb = client_data_left[cname]['verbraucherverluste']
|
||
if bezug != 0:
|
||
client_data_left[cname]['percent'] = verb / bezug
|
||
else:
|
||
client_data_left[cname]['percent'] = None
|
||
|
||
# Build LEFT rows structure
|
||
left_row_defs = [
|
||
('Stand der Gaszähler (Nm³)', 'stand_gas'),
|
||
('Stand der Gaszähler (Vorjahr) (Nm³)', 'stand_gas_prev'),
|
||
('Gasrückführung (Nm³)', 'gasrueckf'),
|
||
('Rückführung flüssig (Lit. L-He)', 'rueckf_fluessig'),
|
||
('Sonderrückführungen (Lit. L-He)', 'sonder'),
|
||
('Bestand in Kannen-1 (Lit. L-He)', 'bestand_kannen'),
|
||
('Summe Bestand (Lit. L-He)', 'summe_bestand'),
|
||
('Best. in Kannen Vormonat (Lit. L-He)', 'best_kannen_vormonat'),
|
||
('Bezug (Liter L-He)', 'bezug'),
|
||
('Rückführ. Soll (Lit. L-He)', 'rueckf_soll'),
|
||
('Verluste (Soll-Rückf.) (Lit. L-He)', 'verluste'),
|
||
('Füllungen warm (Lit. L-He)', 'fuellungen_warm'),
|
||
('Kaltgas Rückgabe (Lit. L-He) – Faktor', 'kaltgas_rueckgabe'),
|
||
('Verbraucherverluste (Liter L-He)', 'verbraucherverluste'),
|
||
('%', 'percent'),
|
||
]
|
||
|
||
rows_left = []
|
||
for label, key in left_row_defs:
|
||
values = [client_data_left[cname][key] for cname in HALFYEAR_CLIENTS_LEFT]
|
||
if key == 'percent':
|
||
total_bezug = sum((client_data_left[c]['bezug'] for c in HALFYEAR_CLIENTS_LEFT), Decimal('0'))
|
||
total_verb = sum((client_data_left[c]['verbraucherverluste'] for c in HALFYEAR_CLIENTS_LEFT), Decimal('0'))
|
||
total = (total_verb / total_bezug) if total_bezug != 0 else None
|
||
else:
|
||
total = sum((v for v in values if v is not None), Decimal('0'))
|
||
rows_left.append({
|
||
'label': label,
|
||
'values': values,
|
||
'total': total,
|
||
'is_percent': key == 'percent',
|
||
})
|
||
|
||
# ------------------------------------------------------------------
|
||
# 3) RIGHT TABLE (top-right half-year aggregation)
|
||
# ------------------------------------------------------------------
|
||
RIGHT_CLIENTS = HALFYEAR_RIGHT_CLIENTS # for brevity
|
||
|
||
right_data = {name: {} for name in RIGHT_CLIENTS}
|
||
|
||
# --- Bezug (Liter L-He) for each right client (same as for left) ---
|
||
for cname in RIGHT_CLIENTS:
|
||
total_bezug = Decimal('0')
|
||
for (y, m) in window:
|
||
qs = SecondTableEntry.objects.filter(
|
||
client__name=cname,
|
||
date__year=y,
|
||
date__month=m,
|
||
).aggregate(
|
||
total=Coalesce(Sum('lhe_output'), Value(0, output_field=DecimalField()))
|
||
)
|
||
total_bezug += Decimal(str(qs['total']))
|
||
right_data[cname]['bezug'] = total_bezug
|
||
def find_bestand_from_window(reference_client: str) -> Decimal:
|
||
"""
|
||
Implements:
|
||
WENN(last_month!J36=0; WENN(prev_month!J36=0; ...; prev_sheet!<col>9); last_month!<col>9)
|
||
reference_client decides which column (L/N/P/Q/R) we read from monthly top_right row_index=5.
|
||
"""
|
||
# scan backward through window
|
||
for (y, m) in reversed(window):
|
||
sh = sheets_by_ym.get((y, m))
|
||
if not sh:
|
||
continue
|
||
gasbestand = get_bottom1_value(sh, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3)
|
||
if gasbestand != 0:
|
||
return get_top_right_value(sh, reference_client, TR_BESTAND_KANNEN_ROW)
|
||
|
||
# fallback to previous month (Übertrag_Dez__Vorjahr equivalent)
|
||
return get_top_right_value(prev_sheet, reference_client, TR_BESTAND_KANNEN_ROW)
|
||
|
||
# Fohrer+Buntk merged: BOTH use Fohrer column (L9)
|
||
val_L = find_bestand_from_window("Dr. Fohrer")
|
||
right_data["Dr. Fohrer"]["bestand_kannen"] = val_L
|
||
right_data["AG Buntk."]["bestand_kannen"] = val_L
|
||
|
||
# Alff+Gutfl merged: BOTH use Alff column (N9)
|
||
val_N = find_bestand_from_window("AG Alff")
|
||
right_data["AG Alff"]["bestand_kannen"] = val_N
|
||
right_data["AG Gutfl."]["bestand_kannen"] = val_N
|
||
|
||
# M3 each uses its own column (P9/Q9/R9)
|
||
right_data["M3 Thiele"]["bestand_kannen"] = find_bestand_from_window("M3 Thiele")
|
||
right_data["M3 Buntkowsky"]["bestand_kannen"] = find_bestand_from_window("M3 Buntkowsky")
|
||
right_data["M3 Gutfleisch"]["bestand_kannen"] = find_bestand_from_window("M3 Gutfleisch")
|
||
# Helper for pair shares (L13/($L13+$M13), etc.)
|
||
def pair_share(c1, c2):
|
||
total = right_data[c1]['bezug'] + right_data[c2]['bezug']
|
||
if total == 0:
|
||
return (Decimal('0'), Decimal('0'))
|
||
return (
|
||
right_data[c1]['bezug'] / total,
|
||
right_data[c2]['bezug'] / total,
|
||
)
|
||
|
||
# --- "Stand der Gaszähler (Vorjahr) (Nm³)" row: share based on Bezug ---
|
||
# Dr. Fohrer / AG Buntk.
|
||
s_fohrer, s_buntk = pair_share("Dr. Fohrer", "AG Buntk.")
|
||
right_data["Dr. Fohrer"]['stand_prev_share'] = s_fohrer
|
||
right_data["AG Buntk."]['stand_prev_share'] = s_buntk
|
||
|
||
# AG Alff / AG Gutfl.
|
||
s_alff, s_gutfl = pair_share("AG Alff", "AG Gutfl.")
|
||
right_data["AG Alff"]['stand_prev_share'] = s_alff
|
||
right_data["AG Gutfl."]['stand_prev_share'] = s_gutfl
|
||
|
||
# M3 Thiele / M3 Buntkowsky / M3 Gutfleisch → empty in Excel → None
|
||
for cname in ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"]:
|
||
right_data[cname]['stand_prev_share'] = None
|
||
|
||
# --- Rückführung flüssig per month (raw sums) ---
|
||
# top_right row_index=2 is "Rückführung flüssig (Lit. L-He)"
|
||
|
||
# --- Sonderrückführungen (row_index=3 in top_right) ---
|
||
for cname in RIGHT_CLIENTS:
|
||
sonder_total = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
sonder_total += get_top_right_value(sheet, cname, row_index=3)
|
||
right_data[cname]['sonder'] = sonder_total
|
||
|
||
# --- Sammelrückführung (row_index=4 in top_right), grouped & merged ---
|
||
# Group 1: Dr. Fohrer + AG Buntk.
|
||
group1_total = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
group1_total += get_top_right_value(sheet, "Dr. Fohrer", row_index=4)
|
||
right_data["Dr. Fohrer"]['sammel'] = group1_total
|
||
right_data["AG Buntk."]['sammel'] = group1_total
|
||
|
||
# Group 2: AG Alff + AG Gutfl.
|
||
group2_total = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
group2_total += get_top_right_value(sheet, "AG Alff", row_index=4)
|
||
right_data["AG Alff"]['sammel'] = group2_total
|
||
right_data["AG Gutfl."]['sammel'] = group2_total
|
||
|
||
# Group 3: M3 Thiele + M3 Buntkowsky + M3 Gutfleisch
|
||
group3_total = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
group3_total += get_top_right_value(sheet, "M3 Thiele", row_index=4)
|
||
right_data["M3 Thiele"]['sammel'] = group3_total
|
||
right_data["M3 Buntkowsky"]['sammel'] = group3_total
|
||
right_data["M3 Gutfleisch"]['sammel'] = group3_total
|
||
def safe_div(a: Decimal, b: Decimal) -> Decimal:
|
||
return (a / b) if b != 0 else Decimal("0")
|
||
|
||
# --- Rückführung flüssig (Lit. L-He) for Halbjahres-Bilanz top-right ---
|
||
# Uses your exact formulas.
|
||
|
||
# 1) Fohrer / Buntk split by BEZUG share times group SAMMEL (L8)
|
||
L13 = right_data["Dr. Fohrer"]["bezug"]
|
||
M13 = right_data["AG Buntk."]["bezug"]
|
||
L8 = right_data["Dr. Fohrer"]["sammel"] # merged group total
|
||
|
||
den = (L13 + M13)
|
||
right_data["Dr. Fohrer"]["rueckf_fluessig"] = (safe_div(L13, den) * L8) if den != 0 else Decimal("0")
|
||
right_data["AG Buntk."]["rueckf_fluessig"] = (safe_div(M13, den) * L8) if den != 0 else Decimal("0")
|
||
|
||
# 2) Alff / Gutfl split by BEZUG share times group SAMMEL (N8)
|
||
N13 = right_data["AG Alff"]["bezug"]
|
||
O13 = right_data["AG Gutfl."]["bezug"]
|
||
N8 = right_data["AG Alff"]["sammel"] # merged group total
|
||
|
||
den = (N13 + O13)
|
||
right_data["AG Alff"]["rueckf_fluessig"] = (safe_div(N13, den) * N8) if den != 0 else Decimal("0")
|
||
right_data["AG Gutfl."]["rueckf_fluessig"] = (safe_div(O13, den) * N8) if den != 0 else Decimal("0")
|
||
|
||
# 3) M3 Thiele = sum of monthly Rückführung flüssig (monthly top_right row_index=2) over window
|
||
P6_sum = Decimal("0")
|
||
for (y, m) in window:
|
||
sh = sheets_by_ym.get((y, m))
|
||
P6_sum += get_top_right_value(sh, "M3 Thiele", TR_RUECKF_FLUESSIG_ROW)
|
||
right_data["M3 Thiele"]["rueckf_fluessig"] = P6_sum
|
||
|
||
# 4) M3 Buntkowsky / M3 Gutfleisch split by BEZUG share times M3-group SAMMEL (P8)
|
||
P13 = right_data["M3 Thiele"]["bezug"]
|
||
Q13 = right_data["M3 Buntkowsky"]["bezug"]
|
||
R13 = right_data["M3 Gutfleisch"]["bezug"]
|
||
P8 = right_data["M3 Thiele"]["sammel"] # merged group total
|
||
|
||
den = (P13 + Q13 + R13)
|
||
right_data["M3 Buntkowsky"]["rueckf_fluessig"] = (safe_div(Q13, den) * P8) if den != 0 else Decimal("0")
|
||
right_data["M3 Gutfleisch"]["rueckf_fluessig"] = (safe_div(R13, den) * P8) if den != 0 else Decimal("0")
|
||
# --- Bestand in Kannen-1 (Lit. L-He) for right table (grouped) ---
|
||
# Use Gasbestand (J36) and fallback logic, but now reading top_right B9 for each group.
|
||
TOP_RIGHT_ROW_BESTAND_KANNEN = TR_BESTAND_KANNEN_ROW # <-- most likely correct in your setup
|
||
|
||
def pick_bestand_top_right(base_client: str) -> Decimal:
|
||
# Go from last month in window backwards: if Gasbestand != 0, use that month's Bestand in Kannen
|
||
for (y, m) in reversed(window):
|
||
sh = sheets_by_ym.get((y, m))
|
||
if not sh:
|
||
continue
|
||
|
||
gasbestand = get_bottom1_value(sh, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3)
|
||
if gasbestand != 0:
|
||
return get_top_right_value(sh, base_client, TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
|
||
# Fallback to previous month (Übertrag_Dez__Vorjahr equivalent)
|
||
return get_top_right_value(prev_sheet, base_client, TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
|
||
# Group 1 merged (Fohrer + Buntk.)
|
||
g1_best = pick_bestand_top_right("Dr. Fohrer")
|
||
right_data["Dr. Fohrer"]["bestand_kannen"] = g1_best
|
||
right_data["AG Buntk."]["bestand_kannen"] = g1_best
|
||
|
||
# Group 2 merged (Alff + Gutfl.)
|
||
g2_best = pick_bestand_top_right("AG Alff")
|
||
right_data["AG Alff"]["bestand_kannen"] = g2_best
|
||
right_data["AG Gutfl."]["bestand_kannen"] = g2_best
|
||
|
||
# Group 3 NOT merged: each M3 client uses its own most recent nonzero Bestand in Kannen-1
|
||
right_data["M3 Thiele"]["bestand_kannen"] = pick_bestand_top_right("M3 Thiele")
|
||
right_data["M3 Buntkowsky"]["bestand_kannen"] = pick_bestand_top_right("M3 Buntkowsky")
|
||
right_data["M3 Gutfleisch"]["bestand_kannen"] = pick_bestand_top_right("M3 Gutfleisch")
|
||
|
||
# Summe Bestand = same as previous row
|
||
for cname in RIGHT_CLIENTS:
|
||
right_data[cname]['summe_bestand'] = right_data[cname]['bestand_kannen']
|
||
|
||
# Best. in Kannen Vormonat (Lit. L-He) from previous month top_right row_index=7
|
||
g1_prev = get_top_right_value(prev_sheet, "Dr. Fohrer", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["Dr. Fohrer"]['best_kannen_vormonat'] = g1_prev
|
||
right_data["AG Buntk."]['best_kannen_vormonat'] = g1_prev
|
||
|
||
g2_prev = get_top_right_value(prev_sheet, "AG Alff", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["AG Alff"]['best_kannen_vormonat'] = g2_prev
|
||
right_data["AG Gutfl."]['best_kannen_vormonat'] = g2_prev
|
||
|
||
|
||
# Group 1 merged (Fohrer + Buntk.)
|
||
g1_prev = get_top_right_value(prev_sheet, "Dr. Fohrer", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["Dr. Fohrer"]["best_kannen_vormonat"] = g1_prev
|
||
right_data["AG Buntk."]["best_kannen_vormonat"] = g1_prev
|
||
|
||
# Group 2 merged (Alff + Gutfl.)
|
||
g2_prev = get_top_right_value(prev_sheet, "AG Alff", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["AG Alff"]["best_kannen_vormonat"] = g2_prev
|
||
right_data["AG Gutfl."]["best_kannen_vormonat"] = g2_prev
|
||
|
||
# Group 3 UNMERGED (each one reads its own cell)
|
||
right_data["M3 Thiele"]["best_kannen_vormonat"] = get_top_right_value(prev_sheet, "M3 Thiele", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["M3 Buntkowsky"]["best_kannen_vormonat"] = get_top_right_value(prev_sheet, "M3 Buntkowsky", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
right_data["M3 Gutfleisch"]["best_kannen_vormonat"] = get_top_right_value(prev_sheet, "M3 Gutfleisch", TOP_RIGHT_ROW_BESTAND_KANNEN)
|
||
|
||
# --- Rückführ. Soll (Lit. L-He) according to your formulas ---
|
||
|
||
# Group 1: Dr. Fohrer / AG Buntk.
|
||
total_bestand_1 = right_data["Dr. Fohrer"]['summe_bestand']
|
||
best_vormonat_1 = right_data["Dr. Fohrer"]['best_kannen_vormonat']
|
||
diff1 = total_bestand_1 - best_vormonat_1
|
||
share_fohrer = right_data["Dr. Fohrer"]['stand_prev_share'] or Decimal('0')
|
||
|
||
right_data["Dr. Fohrer"]['rueckf_soll'] = (
|
||
right_data["Dr. Fohrer"]['bezug'] - diff1 * share_fohrer
|
||
)
|
||
right_data["AG Buntk."]['rueckf_soll'] = (
|
||
right_data["AG Buntk."]['bezug'] - total_bestand_1 + best_vormonat_1
|
||
)
|
||
|
||
# Group 2: AG Alff / AG Gutfl.
|
||
total_bestand_2 = right_data["AG Alff"]['summe_bestand']
|
||
best_vormonat_2 = right_data["AG Alff"]['best_kannen_vormonat']
|
||
diff2 = total_bestand_2 - best_vormonat_2
|
||
share_alff = right_data["AG Alff"]['stand_prev_share'] or Decimal('0')
|
||
share_gutfl = right_data["AG Gutfl."]['stand_prev_share'] or Decimal('0')
|
||
|
||
right_data["AG Alff"]['rueckf_soll'] = (
|
||
right_data["AG Alff"]['bezug'] - diff2 * share_alff
|
||
)
|
||
right_data["AG Gutfl."]['rueckf_soll'] = (
|
||
right_data["AG Gutfl."]['bezug'] - diff2 * share_gutfl
|
||
)
|
||
|
||
# Group 3: M3 Thiele / M3 Buntkowsky / M3 Gutfleisch
|
||
for cname in ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"]:
|
||
b13 = right_data[cname]['bezug']
|
||
b12 = right_data[cname]['best_kannen_vormonat']
|
||
b11 = right_data[cname]['summe_bestand']
|
||
# Excel: P13+P12-P11 etc.
|
||
right_data[cname]['rueckf_soll'] = b13 + b12 - b11
|
||
|
||
# --- Verluste (Soll-Rückf.) (Lit. L-He) = B14 - B6 - B7 ---
|
||
for cname in RIGHT_CLIENTS:
|
||
b14 = right_data[cname]['rueckf_soll']
|
||
b6 = right_data[cname]['rueckf_fluessig']
|
||
b7 = right_data[cname]['sonder']
|
||
right_data[cname]['verluste'] = b14 - b6 - b7
|
||
|
||
# --- Füllungen warm (Lit. L-He) = sum of monthly 'Füllungen warm' (row_index=11 top_right) ---
|
||
for cname in RIGHT_CLIENTS:
|
||
total_warm = Decimal('0')
|
||
for (y, m) in window:
|
||
sheet = sheets_by_ym.get((y, m))
|
||
if sheet:
|
||
total_warm += get_top_right_value(sheet, cname, row_index=11)
|
||
right_data[cname]['fuellungen_warm'] = total_warm
|
||
|
||
# --- Kaltgas Rückgabe (Lit. L-He) – Faktor = Bezug * 0.06 ---
|
||
for cname in RIGHT_CLIENTS:
|
||
b13 = right_data[cname]['bezug']
|
||
right_data[cname]['kaltgas_rueckgabe'] = b13 * factor
|
||
|
||
# --- Verbraucherverluste (Liter L-He) = Verluste - Kaltgas Rückgabe ---
|
||
for cname in RIGHT_CLIENTS:
|
||
b15 = right_data[cname]['verluste']
|
||
b17 = right_data[cname]['kaltgas_rueckgabe']
|
||
right_data[cname]['verbraucherverluste'] = b15 - b17
|
||
|
||
# --- % = Verbraucherverluste / Bezug ---
|
||
for cname in RIGHT_CLIENTS:
|
||
bezug = right_data[cname]['bezug']
|
||
verb = right_data[cname]['verbraucherverluste']
|
||
if bezug != 0:
|
||
right_data[cname]['percent'] = verb / bezug
|
||
else:
|
||
right_data[cname]['percent'] = None
|
||
|
||
# Build RIGHT rows structure
|
||
right_row_defs = [
|
||
('Stand der Gaszähler (Vorjahr) (Nm³)', 'stand_prev_share'),
|
||
# We skip the pure-text "Gasrückführung (Nm³)" line here,
|
||
# because it’s only text (Aufteilung nach Verbrauch / Gaszähler)
|
||
# and easier to render directly in the template if needed.
|
||
('Rückführung flüssig (Lit. L-He)', 'rueckf_fluessig'),
|
||
('Sonderrückführungen (Lit. L-He)', 'sonder'),
|
||
('Sammelrückführung (Lit. L-He)', 'sammel'),
|
||
('Bestand in Kannen-1 (Lit. L-He)', 'bestand_kannen'),
|
||
('Summe Bestand (Lit. L-He)', 'summe_bestand'),
|
||
('Best. in Kannen Vormonat (Lit. L-He)', 'best_kannen_vormonat'),
|
||
('Bezug (Liter L-He)', 'bezug'),
|
||
('Rückführ. Soll (Lit. L-He)', 'rueckf_soll'),
|
||
('Verluste (Soll-Rückf.) (Lit. L-He)', 'verluste'),
|
||
('Füllungen warm (Lit. L-He)', 'fuellungen_warm'),
|
||
('Kaltgas Rückgabe (Lit. L-He) – Faktor', 'kaltgas_rueckgabe'),
|
||
('Verbraucherverluste (Liter L-He)', 'verbraucherverluste'),
|
||
('%', 'percent'),
|
||
]
|
||
|
||
rows_right = []
|
||
for label, key in right_row_defs:
|
||
values = [right_data[cname].get(key) for cname in RIGHT_CLIENTS]
|
||
|
||
if key == 'percent':
|
||
total_bezug = sum((right_data[c]['bezug'] for c in RIGHT_CLIENTS), Decimal('0'))
|
||
total_verb = sum((right_data[c]['verbraucherverluste'] for c in RIGHT_CLIENTS), Decimal('0'))
|
||
total = (total_verb / total_bezug) if total_bezug != 0 else None
|
||
else:
|
||
total = sum((v for v in values if isinstance(v, Decimal)), Decimal('0'))
|
||
|
||
rows_right.append({
|
||
'label': label,
|
||
'values': values,
|
||
'total': total,
|
||
'is_percent': key == 'percent',
|
||
})
|
||
SUM_TABLE_ROWS = [
|
||
("Rückführung flüssig (Lit. L-He)", "rueckf_fluessig"),
|
||
("Sonderrückführungen (Lit. L-He)", "sonder"),
|
||
("Sammelrückführungen (Lit. L-He)", "sammel"),
|
||
("Bestand in Kannen-1 (Lit. L-He)", "bestand_kannen"),
|
||
("Summe Bestand (Lit. L-He)", "summe_bestand"),
|
||
("Best. in Kannen Vormonat (Lit. L-He)", "best_kannen_vormonat"),
|
||
("Bezug (Liter L-He)", "bezug"),
|
||
("Rückführ. Soll (Lit. L-He)", "rueckf_soll"),
|
||
("Verluste (Soll-Rückf.) (Lit. L-He)", "verluste"),
|
||
("Füllungen warm (Lit. L-He)", "fuellungen_warm"),
|
||
("Kaltgas Rückgabe (Lit. L-He) – Faktor", "kaltgas_rueckgabe"),
|
||
("Faktor 0.06", "factor_row"),
|
||
("Verbraucherverluste (Liter L-He)", "verbraucherverluste"),
|
||
("%", "percent"),
|
||
]
|
||
|
||
RIGHT_GROUPS = {
|
||
"chemie": ["Dr. Fohrer", "AG Buntk."],
|
||
"mawi": ["AG Alff", "AG Gutfl."],
|
||
"m3": ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"],
|
||
}
|
||
|
||
RIGHT_ALL = ["Dr. Fohrer", "AG Buntk.", "AG Alff", "AG Gutfl.", "M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"]
|
||
LEFT_ALL = HALFYEAR_CLIENTS_LEFT
|
||
|
||
def safe_pct(verb, bez):
|
||
return (verb / bez) if bez != 0 else None
|
||
rows_sum = []
|
||
def d(x):
|
||
return x if isinstance(x, Decimal) else Decimal("0")
|
||
for label, key in SUM_TABLE_ROWS:
|
||
|
||
if key == "factor_row":
|
||
lichtwiese = chemie = mawi = m3 = total = Decimal("0.06")
|
||
|
||
elif key == "percent":
|
||
# Right totals
|
||
rw_bez = sum(d(right_data[c].get("bezug")) for c in RIGHT_ALL)
|
||
rw_verb = sum(d(right_data[c].get("verbraucherverluste")) for c in RIGHT_ALL)
|
||
lichtwiese = safe_pct(rw_verb, rw_bez)
|
||
|
||
# Chemie
|
||
ch_bez = sum(d(right_data[c].get("bezug")) for c in RIGHT_GROUPS["chemie"])
|
||
ch_verb = sum(d(right_data[c].get("verbraucherverluste")) for c in RIGHT_GROUPS["chemie"])
|
||
chemie = safe_pct(ch_verb, ch_bez)
|
||
|
||
# MaWi
|
||
mw_bez = sum(d(right_data[c].get("bezug")) for c in RIGHT_GROUPS["mawi"])
|
||
mw_verb = sum(d(right_data[c].get("verbraucherverluste")) for c in RIGHT_GROUPS["mawi"])
|
||
mawi = safe_pct(mw_verb, mw_bez)
|
||
|
||
# M3
|
||
m3_bez = sum(d(right_data[c].get("bezug")) for c in RIGHT_GROUPS["m3"])
|
||
m3_verb = sum(d(right_data[c].get("verbraucherverluste")) for c in RIGHT_GROUPS["m3"])
|
||
m3 = safe_pct(m3_verb, m3_bez)
|
||
|
||
# Σ column = (left verb + right verb) / (left bez + right bez)
|
||
left_bez = sum(d(client_data_left[c].get("bezug")) for c in LEFT_ALL)
|
||
left_verb = sum(d(client_data_left[c].get("verbraucherverluste")) for c in LEFT_ALL)
|
||
total = safe_pct(left_verb + rw_verb, left_bez + rw_bez)
|
||
|
||
else:
|
||
# normal rows = sums
|
||
lichtwiese = sum(d(right_data[c].get(key)) for c in RIGHT_ALL)
|
||
chemie = sum(d(right_data[c].get(key)) for c in RIGHT_GROUPS["chemie"])
|
||
mawi = sum(d(right_data[c].get(key)) for c in RIGHT_GROUPS["mawi"])
|
||
m3 = sum(d(right_data[c].get(key)) for c in RIGHT_GROUPS["m3"])
|
||
|
||
left_total = sum(d(client_data_left[c].get(key)) for c in LEFT_ALL)
|
||
total = left_total + lichtwiese
|
||
|
||
rows_sum.append({
|
||
"row_index": row_index,
|
||
"label": label,
|
||
"total": total,
|
||
"lichtwiese": lichtwiese,
|
||
"chemie": chemie,
|
||
"mawi": mawi,
|
||
"m3": m3,
|
||
"is_percent": (key == "percent"),
|
||
})
|
||
def find_sum_row(rows, label_startswith: str):
|
||
for r in rows:
|
||
if str(r.get("label", "")).strip().startswith(label_startswith):
|
||
return r
|
||
return None
|
||
|
||
summe_bestand_row = find_sum_row(rows_sum, "Summe Bestand")
|
||
k38 = (summe_bestand_row.get("total") if summe_bestand_row else Decimal("0")) or Decimal("0")
|
||
j38 = k38 * Decimal("0.75")
|
||
# --- FIX: now that k38 is known, update bottom2 + recompute dependent rows ---
|
||
bottom2["k38"] = k38
|
||
bottom2["j38"] = j38
|
||
|
||
k39 = bottom2.get("k39") or Decimal("0")
|
||
k40 = bottom2.get("k40") or Decimal("0")
|
||
|
||
# Row 43: Bestand flüssig He = SUMME(K38:K40)
|
||
k43 = (k38 or Decimal("0")) + k39 + k40
|
||
j43 = k43 * Decimal("0.75")
|
||
|
||
bottom2["k43"] = k43
|
||
bottom2["j43"] = j43
|
||
|
||
# Row 44: Gesamtbestand neu = Gasbestand(Lit) from bottom table 1 + k43
|
||
gasbestand_lit = Decimal("0")
|
||
for r in bottom1_rows:
|
||
if (r.get("label") or "").strip().startswith("Gasbestand"):
|
||
gasbestand_lit = r.get("lhe") or Decimal("0")
|
||
break
|
||
|
||
k44 = gasbestand_lit + k43
|
||
j44 = k44 * Decimal("0.75")
|
||
|
||
bottom2["k44"] = k44
|
||
bottom2["j44"] = j44
|
||
def d(x):
|
||
return x if isinstance(x, Decimal) else Decimal("0")
|
||
|
||
# ---- Bottom2: J38/K38 depend on rows_sum (overall summary), so do it HERE ----
|
||
k38 = Decimal("0")
|
||
for r in rows_sum:
|
||
if r.get("label") == "Summe Bestand (Lit. L-He)":
|
||
k38 = r.get("total") or Decimal("0")
|
||
break
|
||
|
||
j38 = k38 * FACTOR_NM3_TO_LHE # 0.75
|
||
bottom2["k38"] = k38
|
||
bottom2["j38"] = j38
|
||
factor = Decimal("0.75")
|
||
|
||
# window = the 6-month list you already build in this view: [(y,m), (y,m), ...]
|
||
# bottom2 = dict with "k44" already computed in your view
|
||
# rows_sum = overall sum group rows list (your existing halfyear logic)
|
||
|
||
# 1) K46 = K44 from the month BEFORE the global month (interval start)
|
||
start_year = interval_year
|
||
start_month = interval_start # whatever you named your start month variable
|
||
|
||
if start_month == 1:
|
||
prev_year, prev_month = start_year - 1, 12
|
||
else:
|
||
prev_year, prev_month = start_year, start_month - 1
|
||
|
||
prev_sheet = MonthlySheet.objects.filter(year=prev_year, month=prev_month).first()
|
||
|
||
# This assumes you have MonthlySummary.gesamtbestand_neu_lhe as K44 equivalent.
|
||
# If your field name differs, tell me your MonthlySummary model fields.
|
||
prev_k44 = Decimal("0")
|
||
if prev_sheet:
|
||
prev_sum = MonthlySummary.objects.filter(sheet=prev_sheet).first()
|
||
if prev_sum and prev_sum.gesamtbestand_neu_lhe is not None:
|
||
prev_k44 = Decimal(str(prev_sum.gesamtbestand_neu_lhe))
|
||
|
||
# helpers: read bottom_3 values for a given sheet/month
|
||
def get_bottom3_value(sheet, row_index, col_index):
|
||
if not sheet:
|
||
return Decimal("0")
|
||
c = Cell.objects.filter(
|
||
sheet=sheet, table_type="bottom_3",
|
||
row_index=row_index, column_index=col_index
|
||
).first()
|
||
if not c or c.value in (None, "", "None"):
|
||
return Decimal("0")
|
||
try:
|
||
return Decimal(str(c.value))
|
||
except Exception:
|
||
return Decimal("0")
|
||
|
||
# 2) Sum rows across the 6-month window
|
||
g47_sum = Decimal("0")
|
||
i47_sum = Decimal("0")
|
||
j47_sum = Decimal("0")
|
||
|
||
g50_sum = Decimal("0")
|
||
i50_sum = Decimal("0")
|
||
|
||
for (yy, mm) in window:
|
||
s = MonthlySheet.objects.filter(year=yy, month=mm).first()
|
||
## Excel row 47 maps to bottom_3 row_index=1 in DB (see monthly_sheet.html)
|
||
g = get_bottom3_value(s, 1, 1) # G47 editable
|
||
i = get_bottom3_value(s, 1, 2) # I47 editable
|
||
|
||
g47_sum += g
|
||
i47_sum += i
|
||
|
||
# In monthly_sheet, J47 is CALCULATED as (G47 + I47), not stored as an editable cell
|
||
j47_sum += (g + i)
|
||
|
||
# row 50: G(2), I(4)
|
||
g50_sum += get_bottom3_value(s, 50, 2)
|
||
i50_sum += get_bottom3_value(s, 50, 4)
|
||
|
||
# 3) K52 = Verbraucherverlust from overall sum group first column (global Σ)
|
||
k52 = Decimal("0")
|
||
for r in rows_sum:
|
||
label = (r.get("label") or "")
|
||
if label.startswith("Verbraucherverluste"):
|
||
k52 = r.get("total") or Decimal("0")
|
||
break
|
||
|
||
# --- apply the SAME monthly formulas, with your overrides ---
|
||
# Row 46
|
||
k46 = prev_k44
|
||
j46 = k46 * factor
|
||
|
||
# Row 47
|
||
g47 = g47_sum
|
||
i47 = i47_sum
|
||
j47 = j47_sum
|
||
k47 = (j47 / factor) + g47 if (j47 != 0 or g47 != 0) else Decimal("0")
|
||
|
||
# Row 48
|
||
k48 = k46 + k47
|
||
j48 = k48 * factor
|
||
|
||
# Row 49 (akt. Monat) -> in halfyear we use current bottom2 K44
|
||
k49 = bottom2.get("k44") or Decimal("0")
|
||
j49 = k49 * factor
|
||
|
||
# Row 50
|
||
g50 = g50_sum
|
||
i50 = i50_sum
|
||
j50 = i50
|
||
k50 = (j50 / factor) if j50 != 0 else Decimal("0")
|
||
|
||
# Row 51
|
||
k51 = k48 - k49 - k50
|
||
j51 = k51 * factor
|
||
|
||
# Row 52
|
||
j52 = k52 * factor
|
||
|
||
# Row 53
|
||
k53 = k51 - k52
|
||
j53 = j51 - j52
|
||
|
||
bottom3 = {
|
||
"j46": j46, "k46": k46,
|
||
"g47": g47, "i47": i47, "j47": j47, "k47": k47,
|
||
"j48": j48, "k48": k48,
|
||
"j49": j49, "k49": k49,
|
||
"g50": g50, "i50": i50, "j50": j50, "k50": k50,
|
||
"j51": j51, "k51": k51,
|
||
"j52": j52, "k52": k52,
|
||
"j53": j53, "k53": k53,
|
||
}
|
||
# ------------------------------------------------------------------
|
||
# 4) Context – keep old keys AND new ones
|
||
# ------------------------------------------------------------------
|
||
context = {
|
||
'interval_year': interval_year,
|
||
'interval_start_month': interval_start,
|
||
'window': window,
|
||
|
||
'clients': HALFYEAR_CLIENTS_LEFT,
|
||
'rows': rows_left,
|
||
|
||
'clients_left': HALFYEAR_CLIENTS_LEFT,
|
||
'rows_left': rows_left,
|
||
|
||
'clients_right': RIGHT_CLIENTS,
|
||
'rows_right': rows_right,
|
||
'rows_sum': rows_sum,
|
||
'bottom1_rows': bottom1_rows,
|
||
}
|
||
context["bottom2"] = bottom2
|
||
context["bottom3"] = bottom3
|
||
context["context_bottom2_g39"] = bottom2_inputs["g39"]
|
||
context["context_bottom2_i39"] = bottom2_inputs["i39"]
|
||
return context |