payfrit-works/api/admin/debugBigDeansMenu.cfm

<cfsetting showdebugoutput="false">
<cfsetting enablecfoutputonly="true">
<cfcontent type="application/json; charset=utf-8" reset="true">

<cfscript>
businessID = 27;

// Run the EXACT query from getForBuilder.cfm
qCategories = queryExecute("
    SELECT DISTINCT
        p.ItemID as CategoryID,
        p.ItemName as CategoryName,
        p.ItemSortOrder
    FROM Items p
    INNER JOIN Items c ON c.ItemParentItemID = p.ItemID
    WHERE p.ItemBusinessID = :businessID
      AND p.ItemParentItemID = 0
      AND p.ItemIsActive = 1
      AND NOT EXISTS (
          SELECT 1 FROM ItemTemplateLinks tl WHERE tl.TemplateItemID = p.ItemID
      )
    ORDER BY p.ItemSortOrder, p.ItemName
", { businessID: businessID });

cats = [];
for (c in qCategories) {
    arrayAppend(cats, {
        "CategoryID": c.CategoryID,
        "CategoryName": c.CategoryName
    });
}

// Also check raw counts
rawCount = queryExecute("
    SELECT COUNT(*) as cnt FROM Items
    WHERE ItemBusinessID = :bizId AND ItemParentItemID = 0 AND ItemIsActive = 1
", { bizId: businessID });

childrenCount = queryExecute("
    SELECT COUNT(DISTINCT c.ItemParentItemID) as cnt
    FROM Items c
    INNER JOIN Items p ON p.ItemID = c.ItemParentItemID
    WHERE p.ItemBusinessID = :bizId AND p.ItemParentItemID = 0
", { bizId: businessID });

writeOutput(serializeJSON({
    "OK": true,
    "CategoriesFromQuery": cats,
    "CategoryCount": arrayLen(cats),
    "TotalTopLevelItems": rawCount.cnt,
    "TopLevelItemsWithChildren": childrenCount.cnt
}));
</cfscript>