SELECT 
    `Customer Name`, 
    `Sales Order Date`, 
    `Sales Order No`, 
    `Item Code`, 
    FORMAT(`Qty`, 2) AS `Qty`, 
    FORMAT(`Delivered Qty`, 2) AS `Delivered Qty`, 
    FORMAT(`Pending Qty`, 2) AS `Pending Qty`
FROM (
    -- Main sales order data
    SELECT 
        so.customer AS `Customer Name`, 
        so.transaction_date AS `Sales Order Date`, 
        so.name AS `Sales Order No`, 
        so_item.item_code AS `Item Code`,
        so_item.qty AS `Qty`, 
        IFNULL(so_item.delivered_qty, 0) AS `Delivered Qty`, 
        (so_item.qty - IFNULL(so_item.delivered_qty, 0)) AS `Pending Qty`,
        0 AS sort_order
    FROM 
        `tabSales Order` so
    JOIN 
        `tabSales Order Item` so_item ON so.name = so_item.parent
    WHERE 
        so.company = 'Cotton Craft Pvt Ltd'
        AND so.docstatus != 2  -- Exclude cancelled sales orders
        AND (%(from_date)s IS NULL OR so.transaction_date >= %(from_date)s)
        AND (%(to_date)s IS NULL OR so.transaction_date <= %(to_date)s)
        AND (so_item.qty - IFNULL(so_item.delivered_qty, 0)) > 0  -- Exclude rows where Pending Qty = 0
    
    UNION ALL
    
    -- Placeholder rows for customer grouping
    SELECT 
        so.customer AS `Customer Name`, 
        NULL AS `Sales Order Date`, 
        NULL AS `Sales Order No`, 
        NULL AS `Item Code`,
        NULL AS `Qty`, 
        NULL AS `Delivered Qty`, 
        NULL AS `Pending Qty`,
        1 AS sort_order
    FROM 
        `tabSales Order` so
    WHERE 
        so.company = 'Cotton Craft Pvt Ltd'
        AND so.docstatus != 2  -- Exclude cancelled sales orders
        AND (%(from_date)s IS NULL OR so.transaction_date >= %(from_date)s)
        AND (%(to_date)s IS NULL OR so.transaction_date <= %(to_date)s)
        AND EXISTS ( -- Only include customers that have at least one pending item
            SELECT 1 FROM `tabSales Order Item` soi 
            WHERE soi.parent = so.name 
            AND (soi.qty - IFNULL(soi.delivered_qty, 0)) > 0
        )
    GROUP BY 
        so.customer
) grouped_data
ORDER BY 
    `Customer Name`, sort_order, `Sales Order Date`, `Sales Order No`, `Item Code`;