Notes

from django.contrib.postgres.aggregates import JSONAgg
from django.db.models import Count, F, Value
from django.db.models.functions import Coalesce


@login_required
def pipeline_report(request):

statuses = [
'FGI In 3PL',
'Crated',
'In Dollup',
'In FGI',
'In Final Test',
'Ready for FT',
'In Frame',
'Not Started'
]

# ---------------------------------------------------------
# 🔥 ONE SINGLE QUERY: Counts + Serial list in one SQL call
# ---------------------------------------------------------
systems_data = (
ScitonSystem.objects
.filter(current_status__name__in=statuses)
.values(
"system_item__item_number",
"system_item__item_description",
"system_item__itmUf_SCI_ProductType",
"current_status__name"
)
.annotate(
count=Count("id"),
serials=Coalesce(
JSONAgg({
"id": F("id"),
"serial": F("system_serial")
}),
Value([])
)
)
)

# systems_data result example:
# [
# {
# 'system_item__item_number': '1234',
# 'system_item__item_description': 'ABC Laser',
# 'system_item__itmUf_SCI_ProductType': 'Laser',
# 'current_status__name': 'In Frame',
# 'count': 3,
# 'serials': [
# {"id": 5, "serial": "SYS001"},
# {"id": 8, "serial": "SYS002"},
# {"id": 9, "serial": "SYS003"},
# ]
# },
# ...
# ]

# ---------------------------------------------------------
# STEP 2: Convert aggregated ScitonSystem into final structure
# ---------------------------------------------------------
data = {}

for row in systems_data:
item_number = row["system_item__item_number"] or "N/A"
status_name = row["current_status__name"]

if item_number not in data:
data[item_number] = {
"item_description": row["system_item__item_description"] or "N/A",
"product_type": row["system_item__itmUf_SCI_ProductType"] or "N/A",
**{s: 0 for s in statuses}, # counts
"serials": {s: [] for s in statuses} # serial lists
}

# Fill the count + serials for this particular status
data[item_number][status_name] = row["count"]
data[item_number]["serials"][status_name] = row["serials"]

# ---------------------------------------------------------
# STEP 3: Load PipelineReport data (same as before)
# ---------------------------------------------------------
extra_lookup = {
e['item_number']: {
'pipeline_report_id' : e['id'],
'short_description': e['short_description'] or 'N/A',
'MSBR_Bookings': e['MSBR_Bookings'] or 0,
'Target_at_3PL': e['Target_at_3PL'] or 0,
'Target_HQ_FGI': e['Target_HQ_FGI'] or 0,
'kit_hours': e['kit_hours'] or 0,
'frame_hours': e['frame_hours'] or 0,
'FT_setup_hours': e['FT_setup_hours'] or 0,
'test_hours': e['test_hours'] or 0,
'dollup_hours': e['dollup_hours'] or 0,
}
for e in PipelineReport.objects.values(
'id','item_number', 'short_description',
'MSBR_Bookings', 'Target_at_3PL', 'Target_HQ_FGI', 'kit_hours',
'frame_hours', 'FT_setup_hours', 'test_hours', 'dollup_hours'
)
}

# ---------------------------------------------------------
# STEP 4: Fetch Items that are in PipelineReport but not in ScitonSystem
# ---------------------------------------------------------
missing_items = set(extra_lookup.keys()) - set(data.keys())
item_lookup = {
item['item_number']: item
for item in Item.objects.filter(item_number__in=missing_items).values(
'item_number', 'item_description', 'itmUf_SCI_ProductType'
)
}

# ---------------------------------------------------------
# STEP 5: Merge everything into final report rows
# ---------------------------------------------------------
report_data = []
all_numbers = set(data.keys()) | set(extra_lookup.keys())

for item_number in all_numbers:
info = data.get(item_number)
extra = extra_lookup.get(item_number)
item = item_lookup.get(item_number)

# Identify product type & description
product_type = (
(info and info["product_type"]) or
(item and item["itmUf_SCI_ProductType"]) or
"N/A"
)

description = (
(info and info["item_description"]) or
(item and item["item_description"]) or
"N/A"
)

# counts
counts = {s: (info[s] if info else 0) for s in statuses}
serials = (info["serials"] if info else {s: [] for s in statuses})
total = sum(counts.values())

# Pipeline report values
pipeline_report_id = extra['pipeline_report_id'] if extra else 'N/A'
booking = extra['MSBR_Bookings'] if extra else 0
target_3pl = extra['Target_at_3PL'] if extra else 0
target_fgi = extra['Target_HQ_FGI'] if extra else 0
short_description = extra['short_description'] if extra else 'N/A'
kit_hours = extra['kit_hours'] if extra else 0
frame_hours = extra['frame_hours'] if extra else 0
FT_setup_hours = extra['FT_setup_hours'] if extra else 0
test_hours = extra['test_hours'] if extra else 0
dollup_hours = extra['dollup_hours'] if extra else 0

total_hours = kit_hours + frame_hours + FT_setup_hours + test_hours + dollup_hours
release_jobs = (booking + target_3pl + target_fgi) - total

row = {
"pipeline_report_id": pipeline_report_id,
"product_type": product_type,
"item_number": item_number,
"item_description": description,
"short_description": short_description,
"booking": booking,
"target_at_3PL": target_3pl,
"target_HQ_FGI": target_fgi,
"Total": total,
"release_jobs": release_jobs,
**counts,
"serials": serials,
"kit_hours": kit_hours,
"frame_hours": frame_hours,
"FT_setup_hours": FT_setup_hours,
"test_hours": test_hours,
"dollup_hours": dollup_hours,
"total_hours": total_hours,
}

report_data.append(row)

# ---------------------------------------------------------
# CSV EXPORT (unchanged)
# ---------------------------------------------------------
if request.GET.get("download") == "csv":
...
(same CSV code you already had)

# AJAX Response
if request.headers.get("x-requested-with") == "XMLHttpRequest":
return JsonResponse({'data': report_data})

return render(
request,
"pipeline_report.html",
{"statuses": statuses, "queryset": report_data}
)