TPA8/TPA8_SCADA/ignition/named-query/Statistics/EXPORT_ISSUES_ANALYSIS.md

Statistics Export Script - Issues Analysis

Overview

The export script attempts to export data from 13 different tabs (indices 0-12), but there are several critical issues preventing proper export for all tabs.

Tab Structure (from view.json)

Index 0:  Induct Details
Index 1:  Scanner Details
Index 2:  Sorter Summary
Index 3:  Sorter Details
Index 4:  Lane Details
Index 5:  Hourly Induct
Index 6:  Hourly Scanner
Index 7:  Hourly Sorter Summary
Index 8:  Hourly Sorter Details
Index 9:  Hourly Lane
Index 10: Lane Total Full (component name: "Total Full1")
Index 11: Jam by Area (component name: "Jam Area")
Index 12: Dumper cycles

Critical Issues Found

1. Index 0: Induct Details - MISSING FIELDS

Problem: Script expects fields that don't exist in the query

Script expects:

• singlecarrier_count / SingleCarrier_perc / SingleCarrier_rate
• doublecarrier_count / DoubleCarrier_perc / DoubleCarrier_rate

Query actually returns (from Induct Details/query.sql):

• SorterName
• InductName
• Total_count
• Total_perc
• Total_rate
• Startstamp
• Endtstamp

Impact: Export will fail with KeyError when trying to access single/double carrier fields

Note: The TestQ/query.sql file contains commented-out code showing this functionality was planned but never implemented

---

2. Index 9: Hourly Lane - WRONG FIELD NAMES

Problem: Script uses completely wrong field names for filtered results

Script uses (for filtered results):

• result['endt']
• result['rownumber']
• And then various count/perc/rate fields

Query actually returns (from Hourly Lane Count/query.sql):

• StartTimestamp
• Hour
• Total_count
• Diverted
• DivertFail
• DestinationFull
• DestinationJam
• etc.

Script uses (for unfiltered data):

• result['Start Timestamp'] ✓
• result['Hour'] ✓
• result['Total (#)'] / result['Total (%)'] / result['Total (pph)']
• etc.

Impact: Export from filtered results will fail with KeyError

---

3. Index 10: Lane Total Full - WRONG DATA/FIELDS

Problem: Script tries to export Dumper Cycles data instead of Total Full data

Script expects:

• Cycles of ULGL1 / Cycles of ULGL2 / Cycles of ULGL3
• With (#), (%), and (cph) variations

Query actually returns (from Total Full Count/query.sql):

• Lane
• Total_count
• DestFull_count

Impact: Export will use wrong headers and fail to find expected fields. The script logic for index 10 appears to be copied from index 12 (Dumper cycles)

---

4. Index 11: Jam by Area - QUERY IN DIFFERENT FOLDER + CASE MISMATCH

Problem: Query exists in different project folder and field names have case issues

Query location: Jam_Area/Jam (NOT in Statistics folder)

Script expects:

• Total_Jam ❌ (wrong case!)
• Dumpers_jam
• Inbound_jam
• Runout_jam
• Sorter_Recirc_jam

Query actually returns:

• Total_jam ✓ (lowercase 'j')
• Dumpers_jam ✓
• Inbound_jam ✓
• Runout_jam ✓
• Sorter_Recirc_jam ✓

Impact: Export will fail on Total_Jam (capital J) - Python is case-sensitive. Only Count and Percentage modes are implemented (no Rate mode)

---

5. Index 12: Dumper cycles - QUERY IN DIFFERENT FOLDER + WRONG FIELD NAMES

Problem: Query exists in different project folder and script uses wrong field names

Query location: Dumper/Dumper Lane Count, Dumper/Dumper Lane Percent, Dumper/Dumper Lane Rate (NOT in Statistics folder)

Script expects:

• Cycles of ULGL1 / ULGL2 / ULGL3 ❌

Query actually returns (based on view headers):

• Cycles of ULC1 through ULC8 ✓ (8 lanes, not 3!)
• Field names in table: ulc1, ulc2, ... ulc8

Impact: Export will fail completely - wrong field names and wrong number of lanes. Table structure in view.json shows confusing configuration with misaligned columns

---

6. Index 5-9: Hourly Tabs - POTENTIAL QUERY MISMATCH

Problem: Component names use underscores (e.g., "Hourly_Induct") but the script correctly references them

Note: These appear to be working correctly in the script based on component hierarchy

---

7. Index 3: Sorter Details - TYPO IN ELSE BLOCK

Problem: Line has duplicate field access

data.append([..., result['sorted_count'], result['sorted_count'], ...])
                                           ^^^^ should be assigned_count

Should be:

data.append([..., result['sorted_count'], result['assigned_count'], ...])

Impact: Exported "Assigned (#)" column will contain wrong data (Sorted count instead)

---

Summary of Working vs Broken Tabs

✅ LIKELY WORKING (with minor issues):

• Index 1: Scanner Details
• Index 2: Sorter Summary
• Index 3: Sorter Details (has typo but won't crash)
• Index 4: Lane Details
• Index 5: Hourly Induct
• Index 6: Hourly Scanner
• Index 7: Hourly Sorter Summary
• Index 8: Hourly Sorter Details

❌ BROKEN:

• Index 0: Induct Details - Missing carrier fields in query
• Index 9: Hourly Lane - Wrong field names for filtered results
• Index 10: Lane Total Full - Using wrong data structure (Dumper Cycles instead)
• Index 11: Jam by Area - May work if data in props.data, but query doesn't exist
• Index 12: Dumper cycles - May work if data in props.data, but query doesn't exist

---

Recommendations

1. Induct Details (Index 0):

   • OPTION A: Update Statistics/Induct Details query to include single/double carrier fields (use TestQ as template)
   • OPTION B: Simplify export script to only export Total fields (remove carrier columns)

2. Hourly Lane (Index 9):

   • Fix field names in the filtered results section
   • The unfiltered data path works correctly, but filtered results path uses wrong field names

3. Lane Total Full (Index 10):

   • CRITICAL: Completely rewrite logic
   • Current logic is copied from Dumper Cycles tab (wrong!)
   • Should export: Lane, Total (#/%), DestFull (#/%)
   • No Rate mode available for this query

4. Jam by Area (Index 11):

   • MINOR: Fix case: Total_Jam → Total_jam (lowercase 'j')
   • Note: This tab only supports Count and Percentage modes (no Rate)

5. Dumper cycles (Index 12):

   • CRITICAL: Update to use correct field names and number of lanes
   • Change from ULGL1/2/3 to ULC1-8 (8 lanes total)
   • Field names in data appear to be ulc1, ulc2, etc. (need to verify actual query output)
   • Note: Query is in Dumper/ folder, not Statistics/

6. Sorter Details (Index 3):

   • MINOR: Fix typo in else block line
   • Change second result['sorted_count'] to result['assigned_count']