Item Info Popup with DC-wise Drilldown - Implementation Summary

Overview

Implemented a new enhanced item information popup that displays quantities drilled down by DC level, showing how many items have been completed in each individual Distribution Center.

New Components

1. New API Endpoint: item-info-drilldown.php

Location: /public/api/item-info-drilldown.php

Purpose: Fetch item details with DC-wise breakdown of quantities

Parameters:

• itemId (required): The ID of the item to fetch details for

Response Structure:

{
  "success": true,
  "data": {
    "item": {
      "itemId": 1,
      "itemCode": "CODE123",
      "itemName": "Item Name",
      "uom": "Unit",
      "imagePath": "path/to/image.jpg",
      "imageExists": true
    },
    "dcWiseQuantities": [
      {
        "dcId": 1,
        "dcName": "DC New Delhi",
        "boqQuantity": 100,
        "doneQuantity": 45,
        "completionPercentage": 45.0
      },
      {
        "dcId": 2,
        "dcName": "DC Mumbai",
        "boqQuantity": 80,
        "doneQuantity": 65,
        "completionPercentage": 81.25
      }
    ],
    "summary": {
      "totalBoqQuantity": 180,
      "totalDoneQuantity": 110,
      "completionPercentage": 61.11
    }
  }
}

Query Logic:

1. Item Details Query: Fetches basic item information from workpro.item table

2. DC-wise Breakdown Query:

   • Groups by dcId and joins with workpro.dc to get DC names
   • For each DC:
     • BOQ Qty: SUM(boq.totalQty) where itemId = :itemId and dcId = current_dc
     • Done Qty: SUM(work.doneQty) via subquery joining work → boq
   • Calculation: Completion % = (doneQuantity / boqQuantity) * 100

3. Summary Totals: Aggregates all DC data for overall completion percentage

2. Updated Function: showItemInfo(itemId, dcId = null)

Location: /public/all-work.php (lines 654-800+)

Changes:

• Now uses the new item-info-drilldown.php API instead of item-info.php
• Accepts optional dcId parameter (for backward compatibility, though not currently used)
• Processes three data sources from API response:
  • response.data.item - Basic item details
  • response.data.dcWiseQuantities - Array of DC-wise breakdowns
  • response.data.summary - Overall totals

3. Enhanced Popup UI

Displays:

Section 1: Item Header (3-column layout)

• Item Code (cyan)
• Item ID
• UOM (Unit of Measurement)

Section 2: Item Name (Full width)

• Complete item name

Section 3: DC-wise Quantities Table A responsive table showing: | DC Name | BOQ Qty (Gold) | Done Qty (Green) | Completion % (Cyan) | |---------|---|---|---| | DC New Delhi | 100 | 45 | 45% | | DC Mumbai | 80 | 65 | 81.3% |

Section 4: Summary Totals (3-column layout)

• Total BOQ Qty across all DCs (Gold)
• Total Done Qty across all DCs (Green)
• Overall Completion % (Cyan)

Data Flow

User clicks Item Code in DataTable
        ↓
showItemInfo(itemId, dcId) called
        ↓
AJAX POST to api/item-info-drilldown.php
        ↓
API Query Groups by DC
        ↓
Returns item + dcWiseQuantities array
        ↓
JavaScript builds table from array
        ↓
Display in modal with DC-wise breakdown

Visual Hierarchy & Styling

Colors:

• Cyan (#0dcaf0): Item Code, DC Name header, Completion %
• Gold (#ffc107): BOQ Quantities, emphasizing planned amounts
• Green (#28a745): Done Quantities, emphasizing completed work
• Blue (#17a2b8): Percentage values

Table Styling:

• Dark theme background (#0d1117)
• Hover effect on rows
• Header with 2px cyan border
• Row dividers for clarity
• Right-aligned numeric values

Key Differences from Previous Implementation

Database Dependencies

Tables Used:

• workpro.item - Basic item information
• workpro.boq - BOQ entries with itemId, dcId, totalQty
• workpro.dc - DC master data (dcName)
• workpro.work - Work entries with doneQty, boqId

Join Path for Quantities:

work.boqId → boq.boqId
boq.itemId → item.itemId
boq.dcId → dc.dcId

Usage Example

// Click handler automatically passes itemId and dcId
$(document).on('click', '.item-info-link', function(e) {
    e.preventDefault();
    const itemId = $(this).data('item-id');
    const dcId = $(this).data('dc-id');  // DC context from current row
    showItemInfo(itemId, dcId);  // dcId optional for future enhancement
});

Error Handling

• 401 Unauthorized: User not logged in - redirects to login
• 400 Bad Request: Item not found or invalid parameters
• Invalid itemId: Shows "No item found" message
• No DC data: Shows "No DC data available" in table

Performance Notes

• Single API call retrieves all DC data at once
• Efficient grouping by DC at database level
• No N+1 query problem
• Scales well with number of DCs

Files Modified

1. /public/all-work.php

   • Updated showItemInfo() function
   • Changed API endpoint from item-info.php to item-info-drilldown.php
   • Enhanced HTML template with DC-wise table

2. /public/api/item-info-drilldown.php (NEW)

   • New API endpoint for DC-wise breakdown
   • Handles authentication, parameter validation
   • Returns comprehensive item + DC data

Testing Checklist

• [ ] Click Item Code in work table → Modal opens
• [ ] Table displays all DCs for the item
• [ ] BOQ Qty column shows correct totals per DC
• [ ] Done Qty column shows correct completed amounts
• [ ] Completion % calculates correctly (Done/BOQ * 100)
• [ ] Summary totals match sum of all DC rows
• [ ] Hover effects work on table rows
• [ ] Colors are correctly applied (cyan, gold, green)
• [ ] Modal closes properly with X button
• [ ] No JavaScript console errors

Future Enhancements

1. DC-level Filtering: Use dcId parameter to show only selected DC
2. Export: Add button to export DC-wise data as CSV/Excel
3. Charts: Add charts showing DC-wise completion rates
4. Pagination: If many DCs, add pagination to table
5. Search/Filter: Add ability to search/filter DCs by name