forked from dsutanto/chicken-counting-sukawarna-det
651 lines
32 KiB
Python
651 lines
32 KiB
Python
import sqlite3
|
|
import re
|
|
from pathlib import Path
|
|
import pandas as pd
|
|
import openpyxl
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.utils import get_column_letter
|
|
from openpyxl.chart import BarChart, LineChart, Reference
|
|
|
|
from chicken_counter.weight.growth_model import STANDARD_BROILER_WEIGHT_TABLE, gompertz_baseline_weight
|
|
|
|
|
|
def migrate_db_if_needed(conn: sqlite3.Connection) -> None:
|
|
"""Ensure batch_runs table has all required weight & dimensional columns."""
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
cursor = conn.execute("PRAGMA table_info(batch_runs)")
|
|
existing_cols = {row[1] for row in cursor.fetchall()}
|
|
new_cols = [
|
|
("cycle_day", "INTEGER NOT NULL DEFAULT 0"),
|
|
("area_cm2_median", "REAL NOT NULL DEFAULT 0.0"),
|
|
("minor_axis_cm_median", "REAL NOT NULL DEFAULT 0.0"),
|
|
("major_axis_cm_median", "REAL NOT NULL DEFAULT 0.0"),
|
|
("predicted_weight_g", "REAL NOT NULL DEFAULT 0.0"),
|
|
("gompertz_baseline_g", "REAL NOT NULL DEFAULT 0.0"),
|
|
("vision_gain_g", "REAL NOT NULL DEFAULT 0.0"),
|
|
("n_weight_samples", "INTEGER NOT NULL DEFAULT 0"),
|
|
("rejection_rate", "REAL NOT NULL DEFAULT 0.0"),
|
|
]
|
|
for col_name, col_type in new_cols:
|
|
if col_name not in existing_cols:
|
|
try:
|
|
conn.execute(f"ALTER TABLE batch_runs ADD COLUMN {col_name} {col_type}")
|
|
except Exception:
|
|
pass
|
|
|
|
|
|
def enrich_missing_weights_from_filesystem(df: pd.DataFrame, base_dir: Path) -> pd.DataFrame:
|
|
"""If DB rows have 0 weight metrics, try loading from companion prediction parquet/csv files."""
|
|
if df.empty:
|
|
return df
|
|
|
|
candidate_roots = [
|
|
base_dir / "VIDEOS" / "cycle7" / "FULL_Cycle_Kandang_Atas_Test",
|
|
base_dir.parent / "VIDEOS" / "cycle7" / "FULL_Cycle_Kandang_Atas_Test",
|
|
base_dir.parent / "VIDEOS" / "cycle7" / "kandang-atas",
|
|
base_dir.parent / "VIDEOS" / "cycle7",
|
|
]
|
|
|
|
for idx, row in df.iterrows():
|
|
if float(row.get("predicted_weight_g", 0.0)) > 0:
|
|
continue
|
|
date_str = str(row["date"])
|
|
cam_id = str(row["camera_id"])
|
|
|
|
# Search candidate roots for predictions or cage features
|
|
for root in candidate_roots:
|
|
if not root.is_dir():
|
|
continue
|
|
pred_file = root / date_str / "output" / f"predictions_{date_str}.parquet"
|
|
if not pred_file.is_file():
|
|
pred_file = root / date_str / "output" / f"predictions_{date_str}.csv"
|
|
|
|
if pred_file.is_file():
|
|
try:
|
|
pdf = pd.read_parquet(pred_file) if pred_file.suffix == ".parquet" else pd.read_csv(pred_file)
|
|
cam_row = pdf[pdf["camera_id"] == cam_id]
|
|
if cam_row.empty:
|
|
cam_row = pdf[pdf["camera_id"] == "FUSED"]
|
|
if not cam_row.empty:
|
|
c_r = cam_row.iloc[0]
|
|
df.at[idx, "cycle_day"] = int(c_r.get("cycle_day", df.at[idx, "cycle_day"]))
|
|
df.at[idx, "area_cm2_median"] = float(c_r.get("area_cm2_median", df.at[idx, "area_cm2_median"]))
|
|
df.at[idx, "minor_axis_cm_median"] = float(c_r.get("minor_axis_cm_median", df.at[idx, "minor_axis_cm_median"]))
|
|
df.at[idx, "major_axis_cm_median"] = float(c_r.get("major_axis_cm_median", df.at[idx, "major_axis_cm_median"]))
|
|
df.at[idx, "predicted_weight_g"] = float(c_r.get("predicted_weight_g", 0.0))
|
|
df.at[idx, "gompertz_baseline_g"] = float(c_r.get("gompertz_baseline_g", 0.0))
|
|
df.at[idx, "vision_gain_g"] = float(c_r.get("vision_gain_g", 0.0))
|
|
df.at[idx, "n_weight_samples"] = int(c_r.get("n_valid_samples", 0))
|
|
df.at[idx, "rejection_rate"] = float(c_r.get("rejection_rate", 0.0))
|
|
break
|
|
except Exception:
|
|
pass
|
|
|
|
return df
|
|
|
|
|
|
def generate_excel_report(db_path, output_excel_path, target_date=None):
|
|
conn = sqlite3.connect(db_path)
|
|
migrate_db_if_needed(conn)
|
|
if target_date:
|
|
df = pd.read_sql_query("SELECT * FROM batch_runs WHERE date = ? ORDER BY date ASC, camera_id ASC", conn, params=(target_date,))
|
|
else:
|
|
df = pd.read_sql_query("SELECT * FROM batch_runs ORDER BY date ASC, camera_id ASC", conn)
|
|
conn.close()
|
|
|
|
base_dir = Path(db_path).resolve().parent.parent
|
|
df = enrich_missing_weights_from_filesystem(df, base_dir)
|
|
|
|
wb = openpyxl.Workbook()
|
|
wb.remove(wb.active) # Remove default sheet
|
|
|
|
font_family = "Segoe UI"
|
|
if df.empty:
|
|
ws_empty = wb.create_sheet(title="Daily Summary")
|
|
ws_empty["A1"] = "Chicken Counter - Summary Report"
|
|
ws_empty["A1"].font = Font(name=font_family, size=16, bold=True, color="1F4E78")
|
|
target_info = f" for date {target_date}" if target_date else ""
|
|
ws_empty["A3"] = f"No batch runs found in database{target_info}."
|
|
ws_empty["A3"].font = Font(name=font_family, size=11, italic=True)
|
|
wb.save(output_excel_path)
|
|
print(f"No records found. Empty report saved to: {output_excel_path}")
|
|
return
|
|
|
|
# Standard Theme Colors
|
|
header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid") # Dark Navy
|
|
header_font = Font(name=font_family, size=11, bold=True, color="FFFFFF")
|
|
|
|
accent_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid") # Soft Blue
|
|
total_fill = PatternFill(start_color="B4C6E7", end_color="B4C6E7", fill_type="solid")
|
|
total_font = Font(name=font_family, size=11, bold=True, color="000000")
|
|
|
|
kpi_title_font = Font(name=font_family, size=9, bold=False, color="595959")
|
|
kpi_value_font = Font(name=font_family, size=18, bold=True, color="1F4E78")
|
|
|
|
thin_border = Border(
|
|
left=Side(style='thin', color='D9D9D9'),
|
|
right=Side(style='thin', color='D9D9D9'),
|
|
top=Side(style='thin', color='D9D9D9'),
|
|
bottom=Side(style='thin', color='D9D9D9')
|
|
)
|
|
|
|
top_thick_bottom_double = Border(
|
|
top=Side(style='thin', color='000000'),
|
|
bottom=Side(style='double', color='000000')
|
|
)
|
|
|
|
# -------------------------------------------------------------
|
|
# SHEET 1: Daily Summary
|
|
# -------------------------------------------------------------
|
|
ws_summary = wb.create_sheet(title="Daily Summary")
|
|
ws_summary.views.sheetView[0].showGridLines = True
|
|
|
|
# Title Block
|
|
ws_summary["A1"] = "Chicken Counter - Cycle 7 Summary Report"
|
|
ws_summary["A1"].font = Font(name=font_family, size=16, bold=True, color="1F4E78")
|
|
ws_summary["A2"] = f"Location: {df['location'].iloc[0]} | Date Range: {df['date'].min()} to {df['date'].max()}"
|
|
ws_summary["A2"].font = Font(name=font_family, size=10, italic=True, color="595959")
|
|
|
|
# Weight subset for cycle days >= 1
|
|
weight_sub = df[(df["cycle_day"] >= 1) & (df["predicted_weight_g"] > 0)]
|
|
avg_flock_weight = float(weight_sub["predicted_weight_g"].mean()) if not weight_sub.empty else 0.0
|
|
median_body_area = float(weight_sub["area_cm2_median"].median()) if not weight_sub.empty else 0.0
|
|
|
|
# KPI Cards Block (Rows 4 to 6)
|
|
kpis = [
|
|
("TOTAL CHICKENS COUNTED", df['total_entered'].sum(), "#,##0"),
|
|
("AVG FLOCK WEIGHT (g)", avg_flock_weight, "#,##0.0"),
|
|
("MEDIAN BIRD AREA (cm²)", median_body_area, "#,##0.0"),
|
|
("TOTAL RUNS", len(df), "#,##0"),
|
|
("TOTAL TIME (MINUTES)", round(df['elapsed_seconds'].sum() / 60, 1), "#,##0.0")
|
|
]
|
|
|
|
col_starts = [1, 3, 5, 7, 9]
|
|
for (title, val, num_fmt), col_idx in zip(kpis, col_starts):
|
|
c1 = ws_summary.cell(row=4, column=col_idx, value=title)
|
|
c1.font = kpi_title_font
|
|
c1.fill = accent_fill
|
|
ws_summary.merge_cells(start_row=4, start_column=col_idx, end_row=4, end_column=col_idx+1)
|
|
|
|
c2 = ws_summary.cell(row=5, column=col_idx, value=val)
|
|
c2.font = kpi_value_font
|
|
c2.number_format = num_fmt
|
|
c2.alignment = Alignment(horizontal='center', vertical='center')
|
|
ws_summary.merge_cells(start_row=5, start_column=col_idx, end_row=6, end_column=col_idx+1)
|
|
|
|
# Pivot Data: Date vs Camera
|
|
pivot = df.pivot_table(index='date', columns='camera_id', values='total_entered', aggfunc='sum', fill_value=0)
|
|
cameras = sorted(list(pivot.columns))
|
|
|
|
start_row = 9
|
|
headers_s1 = ["Date", "Cycle Day"] + cameras + ["Daily Total", "Avg Weight (g)", "Median Area (cm²)"]
|
|
for c_idx, h_name in enumerate(headers_s1, 1):
|
|
cell = ws_summary.cell(row=start_row, column=c_idx, value=h_name)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
current_r = start_row + 1
|
|
# Group weight metrics by date
|
|
daily_weight_grp = df.groupby("date").agg(
|
|
cycle_day=("cycle_day", "max"),
|
|
avg_weight=("predicted_weight_g", lambda s: s[s > 0].mean() if len(s[s > 0]) > 0 else 0.0),
|
|
med_area=("area_cm2_median", lambda s: s[s > 0].median() if len(s[s > 0]) > 0 else 0.0),
|
|
).to_dict(orient="index")
|
|
|
|
for date_val, row_data in pivot.iterrows():
|
|
d_str = str(date_val)
|
|
w_info = daily_weight_grp.get(d_str, {"cycle_day": 0, "avg_weight": 0.0, "med_area": 0.0})
|
|
c_day = int(w_info["cycle_day"])
|
|
|
|
ws_summary.cell(row=current_r, column=1, value=d_str).font = Font(name=font_family, size=11)
|
|
ws_summary.cell(row=current_r, column=1).alignment = Alignment(horizontal='center')
|
|
ws_summary.cell(row=current_r, column=1).border = thin_border
|
|
|
|
# Cycle Day
|
|
c_day_cell = ws_summary.cell(row=current_r, column=2, value=c_day)
|
|
c_day_cell.font = Font(name=font_family, size=11)
|
|
c_day_cell.alignment = Alignment(horizontal='center')
|
|
c_day_cell.border = thin_border
|
|
|
|
for idx, cam in enumerate(cameras):
|
|
c = ws_summary.cell(row=current_r, column=idx+3, value=int(row_data[cam]))
|
|
c.font = Font(name=font_family, size=11)
|
|
c.number_format = "#,##0"
|
|
c.alignment = Alignment(horizontal='right')
|
|
c.border = thin_border
|
|
|
|
# Excel SUM Formula for Daily Total
|
|
start_col_let = get_column_letter(3)
|
|
end_col_let = get_column_letter(len(cameras) + 2)
|
|
tot_col_idx = len(cameras) + 3
|
|
tot_c = ws_summary.cell(row=current_r, column=tot_col_idx, value=f"=SUM({start_col_let}{current_r}:{end_col_let}{current_r})")
|
|
tot_c.font = Font(name=font_family, size=11, bold=True)
|
|
tot_c.number_format = "#,##0"
|
|
tot_c.alignment = Alignment(horizontal='right')
|
|
tot_c.border = thin_border
|
|
|
|
# Daily Avg Weight (g)
|
|
w_val = round(float(w_info["avg_weight"]), 1) if c_day >= 1 else 0.0
|
|
w_c = ws_summary.cell(row=current_r, column=tot_col_idx+1, value=w_val if w_val > 0 else "-")
|
|
w_c.font = Font(name=font_family, size=11)
|
|
if w_val > 0:
|
|
w_c.number_format = "#,##0.0"
|
|
w_c.alignment = Alignment(horizontal='right')
|
|
w_c.border = thin_border
|
|
|
|
# Daily Median Area (cm²)
|
|
a_val = round(float(w_info["med_area"]), 1) if c_day >= 1 else 0.0
|
|
a_c = ws_summary.cell(row=current_r, column=tot_col_idx+2, value=a_val if a_val > 0 else "-")
|
|
a_c.font = Font(name=font_family, size=11)
|
|
if a_val > 0:
|
|
a_c.number_format = "#,##0.0"
|
|
a_c.alignment = Alignment(horizontal='right')
|
|
a_c.border = thin_border
|
|
|
|
current_r += 1
|
|
|
|
# Total Row at Bottom of Sheet 1
|
|
ws_summary.cell(row=current_r, column=1, value="Total").font = total_font
|
|
ws_summary.cell(row=current_r, column=1).fill = total_fill
|
|
ws_summary.cell(row=current_r, column=1).alignment = Alignment(horizontal='center')
|
|
ws_summary.cell(row=current_r, column=1).border = top_thick_bottom_double
|
|
|
|
ws_summary.cell(row=current_r, column=2, value="").fill = total_fill
|
|
ws_summary.cell(row=current_r, column=2).border = top_thick_bottom_double
|
|
|
|
for idx, cam in enumerate(cameras):
|
|
col_let = get_column_letter(idx + 3)
|
|
c = ws_summary.cell(row=current_r, column=idx+3, value=f"=SUM({col_let}{start_row+1}:{col_let}{current_r-1})")
|
|
c.font = total_font
|
|
c.fill = total_fill
|
|
c.number_format = "#,##0"
|
|
c.alignment = Alignment(horizontal='right')
|
|
c.border = top_thick_bottom_double
|
|
|
|
final_tot_col = len(cameras) + 3
|
|
tot_final = ws_summary.cell(row=current_r, column=final_tot_col, value=f"=SUM({get_column_letter(final_tot_col)}{start_row+1}:{get_column_letter(final_tot_col)}{current_r-1})")
|
|
tot_final.font = total_font
|
|
tot_final.fill = total_fill
|
|
tot_final.number_format = "#,##0"
|
|
tot_final.alignment = Alignment(horizontal='right')
|
|
tot_final.border = top_thick_bottom_double
|
|
|
|
# Average weight summary
|
|
w_avg_cell = ws_summary.cell(row=current_r, column=final_tot_col+1, value=round(avg_flock_weight, 1) if avg_flock_weight > 0 else "-")
|
|
w_avg_cell.font = total_font
|
|
w_avg_cell.fill = total_fill
|
|
if avg_flock_weight > 0:
|
|
w_avg_cell.number_format = "#,##0.0"
|
|
w_avg_cell.alignment = Alignment(horizontal='right')
|
|
w_avg_cell.border = top_thick_bottom_double
|
|
|
|
# Median area summary
|
|
a_med_cell = ws_summary.cell(row=current_r, column=final_tot_col+2, value=round(median_body_area, 1) if median_body_area > 0 else "-")
|
|
a_med_cell.font = total_font
|
|
a_med_cell.fill = total_fill
|
|
if median_body_area > 0:
|
|
a_med_cell.number_format = "#,##0.0"
|
|
a_med_cell.alignment = Alignment(horizontal='right')
|
|
a_med_cell.border = top_thick_bottom_double
|
|
|
|
# Add Bar Chart to Summary Sheet
|
|
chart = BarChart()
|
|
chart.type = "col"
|
|
chart.style = 10
|
|
chart.title = "Daily Chicken Counts by Camera"
|
|
chart.y_axis.title = "Chicken Count"
|
|
chart.x_axis.title = "Date"
|
|
chart.width = 16
|
|
chart.height = 10
|
|
|
|
data_ref = Reference(ws_summary, min_col=3, min_row=start_row, max_col=len(cameras)+2, max_row=current_r-1)
|
|
cats_ref = Reference(ws_summary, min_col=1, min_row=start_row+1, max_row=current_r-1)
|
|
chart.add_data(data_ref, titles_from_data=True)
|
|
chart.set_categories(cats_ref)
|
|
|
|
ws_summary.add_chart(chart, f"{get_column_letter(len(headers_s1)+2)}9")
|
|
|
|
# -------------------------------------------------------------
|
|
# SHEET 2: Camera Summary
|
|
# -------------------------------------------------------------
|
|
ws_cam = wb.create_sheet(title="Camera Summary")
|
|
ws_cam.views.sheetView[0].showGridLines = True
|
|
|
|
ws_cam["A1"] = "Camera Performance & Dimension Summary"
|
|
ws_cam["A1"].font = Font(name=font_family, size=14, bold=True, color="1F4E78")
|
|
|
|
cam_pivot = df.groupby('camera_id').agg(
|
|
total_entered=('total_entered', 'sum'),
|
|
avg_entered=('total_entered', 'mean'),
|
|
avg_weight=('predicted_weight_g', lambda s: s[s > 0].mean() if len(s[s > 0]) > 0 else 0.0),
|
|
med_area=('area_cm2_median', lambda s: s[s > 0].median() if len(s[s > 0]) > 0 else 0.0),
|
|
med_minor=('minor_axis_cm_median', lambda s: s[s > 0].median() if len(s[s > 0]) > 0 else 0.0),
|
|
med_major=('major_axis_cm_median', lambda s: s[s > 0].median() if len(s[s > 0]) > 0 else 0.0),
|
|
valid_samples=('n_weight_samples', 'sum'),
|
|
total_frames=('frames_processed', 'sum'),
|
|
total_elapsed_sec=('elapsed_seconds', 'sum'),
|
|
total_runs=('id', 'count')
|
|
).reset_index()
|
|
|
|
cam_headers = [
|
|
"Camera ID", "Total Count", "Avg Count / Run",
|
|
"Avg Pred Weight (g)", "Median Area (cm²)", "Median Minor Axis (cm)", "Median Major Axis (cm)",
|
|
"Valid Samples", "Total Frames", "Total Time (Minutes)", "Total Runs"
|
|
]
|
|
for c_idx, h_text in enumerate(cam_headers, 1):
|
|
cell = ws_cam.cell(row=3, column=c_idx, value=h_text)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
for r_idx, row in cam_pivot.iterrows():
|
|
row_num = 4 + r_idx
|
|
ws_cam.cell(row=row_num, column=1, value=row['camera_id']).alignment = Alignment(horizontal='center')
|
|
ws_cam.cell(row=row_num, column=2, value=int(row['total_entered'])).number_format = "#,##0"
|
|
ws_cam.cell(row=row_num, column=3, value=round(row['avg_entered'], 1)).number_format = "#,##0.0"
|
|
|
|
# Weight & metric columns
|
|
ws_cam.cell(row=row_num, column=4, value=round(row['avg_weight'], 1) if row['avg_weight'] > 0 else "-")
|
|
if row['avg_weight'] > 0:
|
|
ws_cam.cell(row=row_num, column=4).number_format = "#,##0.0"
|
|
|
|
ws_cam.cell(row=row_num, column=5, value=round(row['med_area'], 1) if row['med_area'] > 0 else "-")
|
|
if row['med_area'] > 0:
|
|
ws_cam.cell(row=row_num, column=5).number_format = "#,##0.0"
|
|
|
|
ws_cam.cell(row=row_num, column=6, value=round(row['med_minor'], 1) if row['med_minor'] > 0 else "-")
|
|
if row['med_minor'] > 0:
|
|
ws_cam.cell(row=row_num, column=6).number_format = "#,##0.0"
|
|
|
|
ws_cam.cell(row=row_num, column=7, value=round(row['med_major'], 1) if row['med_major'] > 0 else "-")
|
|
if row['med_major'] > 0:
|
|
ws_cam.cell(row=row_num, column=7).number_format = "#,##0.0"
|
|
|
|
ws_cam.cell(row=row_num, column=8, value=int(row['valid_samples'])).number_format = "#,##0"
|
|
ws_cam.cell(row=row_num, column=9, value=int(row['total_frames'])).number_format = "#,##0"
|
|
ws_cam.cell(row=row_num, column=10, value=round(row['total_elapsed_sec'] / 60, 2)).number_format = "#,##0.00"
|
|
ws_cam.cell(row=row_num, column=11, value=int(row['total_runs'])).number_format = "#,##0"
|
|
|
|
for c_idx in range(1, len(cam_headers) + 1):
|
|
ws_cam.cell(row=row_num, column=c_idx).font = Font(name=font_family, size=11)
|
|
ws_cam.cell(row=row_num, column=c_idx).border = thin_border
|
|
|
|
# Total Row for Camera Summary
|
|
tot_row_cam = 4 + len(cam_pivot)
|
|
ws_cam.cell(row=tot_row_cam, column=1, value="Total").font = total_font
|
|
ws_cam.cell(row=tot_row_cam, column=1).fill = total_fill
|
|
ws_cam.cell(row=tot_row_cam, column=1).alignment = Alignment(horizontal='center')
|
|
ws_cam.cell(row=tot_row_cam, column=1).border = top_thick_bottom_double
|
|
|
|
# Sum total count, total frames, total runs, total valid samples
|
|
for c_idx in [2, 8, 9, 11]:
|
|
col_let = get_column_letter(c_idx)
|
|
c = ws_cam.cell(row=tot_row_cam, column=c_idx, value=f"=SUM({col_let}4:{col_let}{tot_row_cam-1})")
|
|
c.font = total_font
|
|
c.fill = total_fill
|
|
c.number_format = "#,##0"
|
|
c.border = top_thick_bottom_double
|
|
|
|
# Averages for Avg Count, Avg Weight, Med Area, Med Minor, Med Major
|
|
for c_idx, num_f in [(3, "#,##0.0"), (4, "#,##0.0"), (5, "#,##0.0"), (6, "#,##0.0"), (7, "#,##0.0")]:
|
|
col_let = get_column_letter(c_idx)
|
|
c = ws_cam.cell(row=tot_row_cam, column=c_idx, value=f"=AVERAGE({col_let}4:{col_let}{tot_row_cam-1})")
|
|
c.font = total_font
|
|
c.fill = total_fill
|
|
c.number_format = num_f
|
|
c.border = top_thick_bottom_double
|
|
|
|
# Sum for elapsed minutes
|
|
c_time = ws_cam.cell(row=tot_row_cam, column=10, value=f"=SUM(J4:J{tot_row_cam-1})")
|
|
c_time.font = total_font
|
|
c_time.fill = total_fill
|
|
c_time.number_format = "#,##0.00"
|
|
c_time.border = top_thick_bottom_double
|
|
|
|
# -------------------------------------------------------------
|
|
# SHEET 3: Weight & Growth Analytics (NEW DEDICATED SHEET)
|
|
# -------------------------------------------------------------
|
|
ws_growth = wb.create_sheet(title="Weight & Growth Analytics")
|
|
ws_growth.views.sheetView[0].showGridLines = True
|
|
|
|
ws_growth["A1"] = "Flock Weight & Growth Trajectory Analytics (Cycle 7)"
|
|
ws_growth["A1"].font = Font(name=font_family, size=14, bold=True, color="1F4E78")
|
|
ws_growth["A2"] = "Growth curve tracking starts at Cycle Day 1 (Day 0: DOC Arrival Headcount Only)"
|
|
ws_growth["A2"].font = Font(name=font_family, size=10, italic=True, color="595959")
|
|
|
|
growth_headers = [
|
|
"Date", "Cycle Day", "Flock Headcount", "Weight Samples",
|
|
"Median Area (cm²)", "Median Minor Axis (cm)", "Median Major Axis (cm)",
|
|
"Daily Pred Weight (g)", "Gompertz Baseline (g)", "Vision Gain / Residual (g)",
|
|
"Rejection Rate (%)", "Growth Phase"
|
|
]
|
|
for c_idx, h_text in enumerate(growth_headers, 1):
|
|
cell = ws_growth.cell(row=4, column=c_idx, value=h_text)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
# Aggregate daily rows starting from Cycle Day >= 1
|
|
daily_growth_data = []
|
|
for d_val, grp in df.groupby("date"):
|
|
c_day = int(grp["cycle_day"].max())
|
|
if c_day < 1:
|
|
continue # Day 0 ignored for weight analytics table as instructed
|
|
|
|
valid_grp = grp[grp["predicted_weight_g"] > 0]
|
|
pred_w = float(valid_grp["predicted_weight_g"].mean()) if not valid_grp.empty else float(grp["predicted_weight_g"].max())
|
|
med_a = float(valid_grp["area_cm2_median"].median()) if not valid_grp.empty else float(grp["area_cm2_median"].median())
|
|
med_min = float(valid_grp["minor_axis_cm_median"].median()) if not valid_grp.empty else float(grp["minor_axis_cm_median"].median())
|
|
med_maj = float(valid_grp["major_axis_cm_median"].median()) if not valid_grp.empty else float(grp["major_axis_cm_median"].median())
|
|
samples = int(grp["n_weight_samples"].sum())
|
|
headcount = int(grp["total_entered"].sum())
|
|
rej_rate = float(grp["rejection_rate"].mean()) * 100.0
|
|
|
|
gomp_b = gompertz_baseline_weight(c_day)
|
|
vis_gain = round(pred_w - gomp_b, 1) if pred_w > 0 else 0.0
|
|
|
|
if c_day <= 10:
|
|
phase = "Starter / Brooding"
|
|
elif c_day <= 24:
|
|
phase = "Grower / Mid-Stage"
|
|
else:
|
|
phase = "Finisher / Mature"
|
|
|
|
daily_growth_data.append({
|
|
"date": str(d_val),
|
|
"cycle_day": c_day,
|
|
"headcount": headcount,
|
|
"samples": samples,
|
|
"area_cm2": round(med_a, 1),
|
|
"minor_cm": round(med_min, 1),
|
|
"major_cm": round(med_maj, 1),
|
|
"pred_w": round(pred_w, 1),
|
|
"gomp_b": round(gomp_b, 1),
|
|
"vis_gain": vis_gain,
|
|
"rej_rate": round(rej_rate, 2),
|
|
"phase": phase,
|
|
})
|
|
|
|
growth_start_r = 5
|
|
curr_g_r = growth_start_r
|
|
for g_row in sorted(daily_growth_data, key=lambda x: x["cycle_day"]):
|
|
ws_growth.cell(row=curr_g_r, column=1, value=g_row["date"]).alignment = Alignment(horizontal='center')
|
|
ws_growth.cell(row=curr_g_r, column=2, value=g_row["cycle_day"]).alignment = Alignment(horizontal='center')
|
|
ws_growth.cell(row=curr_g_r, column=3, value=g_row["headcount"]).number_format = "#,##0"
|
|
ws_growth.cell(row=curr_g_r, column=4, value=g_row["samples"]).number_format = "#,##0"
|
|
ws_growth.cell(row=curr_g_r, column=5, value=g_row["area_cm2"]).number_format = "#,##0.0"
|
|
ws_growth.cell(row=curr_g_r, column=6, value=g_row["minor_cm"]).number_format = "#,##0.0"
|
|
ws_growth.cell(row=curr_g_r, column=7, value=g_row["major_cm"]).number_format = "#,##0.0"
|
|
ws_growth.cell(row=curr_g_r, column=8, value=g_row["pred_w"]).number_format = "#,##0.0"
|
|
ws_growth.cell(row=curr_g_r, column=9, value=g_row["gomp_b"]).number_format = "#,##0.0"
|
|
ws_growth.cell(row=curr_g_r, column=10, value=g_row["vis_gain"]).number_format = "+#,##0.0;-#,##0.0;0.0"
|
|
ws_growth.cell(row=curr_g_r, column=11, value=g_row["rej_rate"]).number_format = "0.00"
|
|
ws_growth.cell(row=curr_g_r, column=12, value=g_row["phase"]).alignment = Alignment(horizontal='center')
|
|
|
|
for c_i in range(1, len(growth_headers) + 1):
|
|
ws_growth.cell(row=curr_g_r, column=c_i).font = Font(name=font_family, size=11)
|
|
ws_growth.cell(row=curr_g_r, column=c_i).border = thin_border
|
|
curr_g_r += 1
|
|
|
|
# Add Growth Comparison Line Chart if data exists
|
|
if len(daily_growth_data) > 0:
|
|
g_chart = LineChart()
|
|
g_chart.title = "Flock Weight Growth: Predicted vs. Gompertz Baseline"
|
|
g_chart.style = 13
|
|
g_chart.y_axis.title = "Weight (grams)"
|
|
g_chart.x_axis.title = "Cycle Day"
|
|
g_chart.width = 18
|
|
g_chart.height = 11
|
|
|
|
g_data_ref = Reference(ws_growth, min_col=8, min_row=4, max_col=9, max_row=curr_g_r-1)
|
|
g_cats_ref = Reference(ws_growth, min_col=2, min_row=5, max_row=curr_g_r-1)
|
|
g_chart.add_data(g_data_ref, titles_from_data=True)
|
|
g_chart.set_categories(g_cats_ref)
|
|
ws_growth.add_chart(g_chart, "N4")
|
|
|
|
# -------------------------------------------------------------
|
|
# SHEET 4: Raw Batch Runs
|
|
# -------------------------------------------------------------
|
|
ws_raw = wb.create_sheet(title="Raw Batch Runs")
|
|
ws_raw.views.sheetView[0].showGridLines = True
|
|
|
|
raw_headers = [
|
|
"ID", "Date", "Location", "Camera ID", "Total Entered", "Frames Processed", "Elapsed (s)",
|
|
"Cycle Day", "Median Area (cm²)", "Minor Axis (cm)", "Major Axis (cm)",
|
|
"Predicted Weight (g)", "Gompertz Baseline (g)", "Vision Gain (g)", "Weight Samples", "Rejection Rate (%)",
|
|
"Stopped Reason", "Source Video", "Generated At"
|
|
]
|
|
for c_idx, h_text in enumerate(raw_headers, 1):
|
|
cell = ws_raw.cell(row=1, column=c_idx, value=h_text)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = Alignment(horizontal='center')
|
|
|
|
for r_idx, row in df.iterrows():
|
|
row_num = 2 + r_idx
|
|
ws_raw.cell(row=row_num, column=1, value=int(row['id'])).alignment = Alignment(horizontal='center')
|
|
ws_raw.cell(row=row_num, column=2, value=str(row['date'])).alignment = Alignment(horizontal='center')
|
|
ws_raw.cell(row=row_num, column=3, value=str(row['location'])).alignment = Alignment(horizontal='center')
|
|
ws_raw.cell(row=row_num, column=4, value=str(row['camera_id'])).alignment = Alignment(horizontal='center')
|
|
|
|
ws_raw.cell(row=row_num, column=5, value=int(row['total_entered'])).number_format = "#,##0"
|
|
ws_raw.cell(row=row_num, column=6, value=int(row['frames_processed'])).number_format = "#,##0"
|
|
ws_raw.cell(row=row_num, column=7, value=float(row['elapsed_seconds'])).number_format = "#,##0.0"
|
|
|
|
ws_raw.cell(row=row_num, column=8, value=int(row.get('cycle_day', 0))).alignment = Alignment(horizontal='center')
|
|
ws_raw.cell(row=row_num, column=9, value=float(row.get('area_cm2_median', 0.0))).number_format = "#,##0.0"
|
|
ws_raw.cell(row=row_num, column=10, value=float(row.get('minor_axis_cm_median', 0.0))).number_format = "#,##0.0"
|
|
ws_raw.cell(row=row_num, column=11, value=float(row.get('major_axis_cm_median', 0.0))).number_format = "#,##0.0"
|
|
ws_raw.cell(row=row_num, column=12, value=float(row.get('predicted_weight_g', 0.0))).number_format = "#,##0.0"
|
|
ws_raw.cell(row=row_num, column=13, value=float(row.get('gompertz_baseline_g', 0.0))).number_format = "#,##0.0"
|
|
ws_raw.cell(row=row_num, column=14, value=float(row.get('vision_gain_g', 0.0))).number_format = "+#,##0.0;-#,##0.0;0.0"
|
|
ws_raw.cell(row=row_num, column=15, value=int(row.get('n_weight_samples', 0))).number_format = "#,##0"
|
|
ws_raw.cell(row=row_num, column=16, value=float(row.get('rejection_rate', 0.0)) * 100.0).number_format = "0.00"
|
|
|
|
ws_raw.cell(row=row_num, column=17, value=str(row['stopped_reason'])).alignment = Alignment(horizontal='center')
|
|
ws_raw.cell(row=row_num, column=18, value=str(row['source_video']))
|
|
ws_raw.cell(row=row_num, column=19, value=str(row['generated_at']))
|
|
|
|
for c_idx in range(1, len(raw_headers) + 1):
|
|
ws_raw.cell(row=row_num, column=c_idx).font = Font(name=font_family, size=10)
|
|
ws_raw.cell(row=row_num, column=c_idx).border = thin_border
|
|
|
|
# -------------------------------------------------------------
|
|
# SHEET 5: Configurations
|
|
# -------------------------------------------------------------
|
|
ws_cfg = wb.create_sheet(title="Configurations")
|
|
ws_cfg.views.sheetView[0].showGridLines = True
|
|
|
|
ws_cfg["A1"] = "Pipeline, Weight Estimation & Model Configurations"
|
|
ws_cfg["A1"].font = Font(name=font_family, size=14, bold=True, color="1F4E78")
|
|
|
|
# Global Settings Table
|
|
ws_cfg["A3"] = "Global Pipeline, Detection & Weight Estimation Settings"
|
|
ws_cfg["A3"].font = Font(name=font_family, size=11, bold=True, color="1F4E78")
|
|
|
|
global_configs = [
|
|
("Hardware / Target Platform", "ASUS NUC (AMD Ryzen 9 9955HX + NVIDIA GeForce RTX 5070 8GB)"),
|
|
("Model Path (Weights)", "models/chicken-detection-model-v26n-300e-best-2026-05-02-NEW.onnx"),
|
|
("Model Architecture", "YOLOv8/v26n ONNX Model (.onnx, CUDAExecutionProvider)"),
|
|
("Inference Image Size (imgsz)", "640 x 640"),
|
|
("IoU Threshold", "0.55"),
|
|
("Default Confidence Threshold (conf)", "0.40"),
|
|
("Target Classes", "[0] (Ignored: [1, 2])"),
|
|
("Default Min Box Area (px)", "3000"),
|
|
("Tracker Architecture", "BoT-SORT (persist=True, track_buffer=90)"),
|
|
("Gate Counting Mode", "two_line (lines_y: [420, 730], direction: bottom_to_up)"),
|
|
("Weight Counting Start Rule", "Cycle Day 1+ (Day 0 = DOC arrival counting only)"),
|
|
("Camera Metric Calibration", "px_per_cm: CC1=19.5, CC2=20.2, CC3=19.8, CC4=19.2 (Default: 20.0)"),
|
|
("Outlier Rejection Model", "FatChicken MAD Z-score (Day <=10: z<=3.0, Day 11-24: z<=2.5, Day >24: z<=2.0)"),
|
|
("Weight Growth Baseline", "Gompertz Ross 308 / Cobb 500 (W_max=4650g, k=0.076, t_i=25.8)"),
|
|
("Vision Weight Allometry", "W_vision = 0.285 * (Area_cm2)^1.52"),
|
|
("Execution Mode", "parallel_processes (MAX_JOBS=4)")
|
|
]
|
|
|
|
ws_cfg.cell(row=4, column=1, value="Configuration Parameter").font = header_font
|
|
ws_cfg.cell(row=4, column=1).fill = header_fill
|
|
ws_cfg.cell(row=4, column=2, value="Setting / Value").font = header_font
|
|
ws_cfg.cell(row=4, column=2).fill = header_fill
|
|
|
|
for idx, (param, val) in enumerate(global_configs, start=5):
|
|
c1 = ws_cfg.cell(row=idx, column=1, value=param)
|
|
c2 = ws_cfg.cell(row=idx, column=2, value=val)
|
|
c1.font = Font(name=font_family, size=10, bold=True)
|
|
c2.font = Font(name=font_family, size=10)
|
|
c1.border = thin_border
|
|
c2.border = thin_border
|
|
c1.fill = accent_fill
|
|
|
|
# Stages Table
|
|
stage_start_row = 5 + len(global_configs) + 2
|
|
ws_cfg.cell(row=stage_start_row-1, column=1, value="Cycle Stage Adaptation Rules").font = Font(name=font_family, size=11, bold=True, color="1F4E78")
|
|
|
|
stage_configs = [
|
|
("early_cycle (Day 0 - 15)", "conf: 0.10, min_box_area_px: 100, min_overlap_ratio: 0.10 (Chicks adaptation, weight starts Day 1)"),
|
|
("mid_cycle (Day 16+)", "conf: 0.40, min_box_area_px: 3000, default filters (Grown chicken standard)")
|
|
]
|
|
|
|
ws_cfg.cell(row=stage_start_row, column=1, value="Cycle Stage").font = header_font
|
|
ws_cfg.cell(row=stage_start_row, column=1).fill = header_fill
|
|
ws_cfg.cell(row=stage_start_row, column=2, value="Applied Overrides").font = header_font
|
|
ws_cfg.cell(row=stage_start_row, column=2).fill = header_fill
|
|
|
|
for idx, (stg, desc) in enumerate(stage_configs, start=stage_start_row+1):
|
|
c1 = ws_cfg.cell(row=idx, column=1, value=stg)
|
|
c2 = ws_cfg.cell(row=idx, column=2, value=desc)
|
|
c1.font = Font(name=font_family, size=10, bold=True)
|
|
c2.font = Font(name=font_family, size=10)
|
|
c1.border = thin_border
|
|
c2.border = thin_border
|
|
c1.fill = accent_fill
|
|
|
|
# Auto-adjust column widths across all sheets
|
|
for ws in wb.worksheets:
|
|
for col in ws.columns:
|
|
max_len = 0
|
|
col_letter = get_column_letter(col[0].column)
|
|
for cell in col:
|
|
if cell.row < 3 and ws.title in ("Daily Summary", "Weight & Growth Analytics"):
|
|
continue
|
|
val_str = str(cell.value or '')
|
|
if len(val_str) > max_len:
|
|
max_len = len(val_str)
|
|
ws.column_dimensions[col_letter].width = max(max_len + 4, 12)
|
|
|
|
ws_summary.column_dimensions['A'].width = 16
|
|
ws_summary.column_dimensions['B'].width = 14
|
|
ws_summary.column_dimensions['C'].width = 14
|
|
ws_summary.column_dimensions['D'].width = 14
|
|
ws_summary.column_dimensions['E'].width = 14
|
|
|
|
wb.save(output_excel_path)
|
|
print(f"Excel report successfully generated: {output_excel_path}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
BASE_DIR = Path(__file__).resolve().parent
|
|
db_file = str(BASE_DIR / "db" / "chicken_counts.db")
|
|
out_file = str(BASE_DIR / "db" / "chicken_counts_report.xlsx")
|
|
generate_excel_report(db_file, out_file)
|