payfrit-works/api/menu/items.cfm

<cfsetting showdebugoutput="false">
<cfsetting enablecfoutputonly="true">

<cffunction name="readJsonBody" access="public" returntype="struct" output="false">
  <cfset var raw = getHttpRequestData().content>
  <cfif isNull(raw) OR len(trim(raw)) EQ 0>
    <cfreturn {}>
  </cfif>
  <cftry>
    <cfset var data = deserializeJSON(raw)>
    <cfif isStruct(data)>
      <cfreturn data>
    <cfelse>
      <cfreturn {}>
    </cfif>
    <cfcatch>
      <cfreturn {}>
    </cfcatch>
  </cftry>
</cffunction>

<cffunction name="apiAbort" access="public" returntype="void" output="true">
  <cfargument name="payload" type="struct" required="true">
  <cfcontent type="application/json; charset=utf-8">
  <cfoutput>#serializeJSON(arguments.payload)#</cfoutput>
  <cfabort>
</cffunction>

<cfset data = readJsonBody()>
<cfset BusinessID = 0>
<cfif structKeyExists(data, "BusinessID")>
  <cfset BusinessID = val(data.BusinessID)>
</cfif>

<!--- Optional OrderTypeID for channel filtering (1=Dine-In, 2=Takeaway, 3=Delivery) --->
<cfset OrderTypeID = 0>
<cfif structKeyExists(data, "OrderTypeID")>
  <cfset OrderTypeID = val(data.OrderTypeID)>
</cfif>

<cfif BusinessID LTE 0>
  <cfset apiAbort({ "OK": false, "ERROR": "missing_businessid", "MESSAGE": "BusinessID is required.", "DETAIL": "" })>
</cfif>

<!--- Get current time and day for schedule filtering --->
<cfset currentTime = timeFormat(now(), "HH:mm:ss")>
<cfset currentDayID = dayOfWeek(now())>

<cftry>
  <!--- Check if new schema is active (ItemBusinessID column exists and has data) --->
  <cfset newSchemaActive = false>
  <cftry>
    <cfset qCheck = queryExecute(
      "SELECT COUNT(*) as cnt FROM Items WHERE ItemBusinessID = ? AND ItemBusinessID > 0",
      [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
      { datasource = "payfrit" }
    )>
    <cfset newSchemaActive = (qCheck.cnt GT 0)>
    <cfcatch>
      <!--- Column doesn't exist yet, use old schema --->
      <cfset newSchemaActive = false>
    </cfcatch>
  </cftry>

  <cfif newSchemaActive>
    <!--- NEW SCHEMA: Items have ItemBusinessID, try Categories table first, fallback to parent Items --->
    <!--- Check if Categories table has data for this business --->
    <cfset hasCategoriesData = false>
    <cftry>
      <cfset qCatCheck = queryExecute(
        "SELECT COUNT(*) as cnt FROM Categories WHERE CategoryBusinessID = ?",
        [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
        { datasource = "payfrit" }
      )>
      <cfset hasCategoriesData = (qCatCheck.cnt GT 0)>
      <cfcatch>
        <cfset hasCategoriesData = false>
      </cfcatch>
    </cftry>

    <cfif hasCategoriesData>
      <!--- Use Categories table with ItemCategoryID --->
      <!--- First, find which menus are currently active based on day/time --->
      <cfset activeMenuIds = "">
      <cftry>
        <cfset qActiveMenus = queryExecute(
          "
            SELECT MenuID FROM Menus
            WHERE MenuBusinessID = :bizId
              AND MenuIsActive = 1
              AND (MenuDaysActive & :dayBit) > 0
              AND (
                (MenuStartTime IS NULL OR MenuEndTime IS NULL)
                OR (TIME(:currentTime) >= MenuStartTime AND TIME(:currentTime) <= MenuEndTime)
              )
          ",
          {
            bizId: { value = BusinessID, cfsqltype = "cf_sql_integer" },
            dayBit: { value = 2 ^ (currentDayID - 1), cfsqltype = "cf_sql_integer" },
            currentTime: { value = currentTime, cfsqltype = "cf_sql_varchar" }
          },
          { datasource = "payfrit" }
        )>
        <cfset activeMenuIds = valueList(qActiveMenus.MenuID)>
        <cfcatch>
          <!--- Menus table might not exist yet --->
        </cfcatch>
      </cftry>

      <!--- Get category headers as virtual items --->
      <!--- Apply schedule filtering, order type filtering, and menu filtering --->
      <cfset qCategories = queryExecute(
        "
          SELECT
            CategoryID,
            CategoryName,
            CategorySortOrder,
            CategoryOrderTypes,
            CategoryScheduleStart,
            CategoryScheduleEnd,
            CategoryScheduleDays,
            CategoryMenuID
          FROM Categories
          WHERE CategoryBusinessID = :bizId
            AND (
              :orderTypeId = 0
              OR FIND_IN_SET(:orderTypeId, CategoryOrderTypes) > 0
            )
            AND (
              CategoryScheduleStart IS NULL
              OR CategoryScheduleEnd IS NULL
              OR (
                TIME(:currentTime) >= CategoryScheduleStart
                AND TIME(:currentTime) <= CategoryScheduleEnd
              )
            )
            AND (
              CategoryScheduleDays IS NULL
              OR CategoryScheduleDays = ''
              OR FIND_IN_SET(:currentDay, CategoryScheduleDays) > 0
            )
            AND (
              CategoryMenuID IS NULL
              OR CategoryMenuID = 0
              #len(activeMenuIds) ? "OR CategoryMenuID IN (#activeMenuIds#)" : ""#
            )
          ORDER BY CategorySortOrder
        ",
        {
          bizId: { value = BusinessID, cfsqltype = "cf_sql_integer" },
          orderTypeId: { value = OrderTypeID, cfsqltype = "cf_sql_integer" },
          currentTime: { value = currentTime, cfsqltype = "cf_sql_varchar" },
          currentDay: { value = currentDayID, cfsqltype = "cf_sql_integer" }
        },
        { datasource = "payfrit" }
      )>

      <!--- Get menu items --->
      <!--- Exclude old-style category headers (ParentID=0 AND CategoryID=0 AND no children with CategoryID>0) --->
      <!--- These are legacy category headers that should be replaced by Categories table entries --->
      <!--- Only include items from visible categories (after schedule/channel filtering) --->
      <cfset visibleCategoryIds = valueList(qCategories.CategoryID)>
      <cfif len(trim(visibleCategoryIds)) EQ 0>
        <cfset visibleCategoryIds = "0">
      </cfif>
      <cfset q = queryExecute(
        "
          SELECT
            i.ItemID,
            i.ItemCategoryID,
            c.CategoryName,
            i.ItemName,
            i.ItemDescription,
            i.ItemParentItemID,
            i.ItemPrice,
            i.ItemIsActive,
            i.ItemIsCheckedByDefault,
            i.ItemRequiresChildSelection,
            i.ItemMaxNumSelectionReq,
            i.ItemIsCollapsible,
            i.ItemSortOrder,
            i.ItemStationID,
            s.StationName,
            s.StationColor
          FROM Items i
          LEFT JOIN Categories c ON c.CategoryID = i.ItemCategoryID
          LEFT JOIN Stations s ON s.StationID = i.ItemStationID
          WHERE i.ItemBusinessID = :bizId
            AND i.ItemIsActive = 1
            AND i.ItemCategoryID IN (#visibleCategoryIds#)
            AND NOT EXISTS (SELECT 1 FROM ItemTemplateLinks tl WHERE tl.TemplateItemID = i.ItemID)
            AND NOT EXISTS (SELECT 1 FROM ItemTemplateLinks tl2 WHERE tl2.TemplateItemID = i.ItemParentItemID)
            AND NOT (i.ItemParentItemID = 0 AND i.ItemCategoryID = 0 AND i.ItemPrice = 0)
          ORDER BY COALESCE(c.CategorySortOrder, 999), i.ItemSortOrder, i.ItemID
        ",
        { bizId: { value = BusinessID, cfsqltype = "cf_sql_integer" } },
        { datasource = "payfrit" }
      )>
    <cfelse>
      <!--- Fallback: Derive categories from parent Items --->
      <cfset q = queryExecute(
        "
          SELECT
            i.ItemID,
            CASE
              WHEN i.ItemParentItemID = 0 AND i.ItemIsCollapsible = 0 THEN i.ItemID
              ELSE COALESCE(
                (SELECT cat.ItemID FROM Items cat
                 WHERE cat.ItemID = i.ItemParentItemID
                   AND cat.ItemParentItemID = 0
                   AND cat.ItemIsCollapsible = 0),
                0
              )
            END as ItemCategoryID,
            CASE
              WHEN i.ItemParentItemID = 0 AND i.ItemIsCollapsible = 0 THEN i.ItemName
              ELSE COALESCE(
                (SELECT cat.ItemName FROM Items cat
                 WHERE cat.ItemID = i.ItemParentItemID
                   AND cat.ItemParentItemID = 0
                   AND cat.ItemIsCollapsible = 0),
                ''
              )
            END as CategoryName,
            i.ItemName,
            i.ItemDescription,
            i.ItemParentItemID,
            i.ItemPrice,
            i.ItemIsActive,
            i.ItemIsCheckedByDefault,
            i.ItemRequiresChildSelection,
            i.ItemMaxNumSelectionReq,
            i.ItemIsCollapsible,
            i.ItemSortOrder,
            i.ItemStationID,
            s.StationName,
            s.StationColor
          FROM Items i
          LEFT JOIN Stations s ON s.StationID = i.ItemStationID
          WHERE i.ItemBusinessID = ?
            AND i.ItemIsActive = 1
            AND NOT EXISTS (SELECT 1 FROM ItemTemplateLinks tl WHERE tl.TemplateItemID = i.ItemID)
            AND NOT EXISTS (SELECT 1 FROM ItemTemplateLinks tl2 WHERE tl2.TemplateItemID = i.ItemParentItemID)
            AND (
              i.ItemParentItemID > 0
              OR (i.ItemParentItemID = 0 AND i.ItemIsCollapsible = 0)
            )
          ORDER BY i.ItemParentItemID, i.ItemSortOrder, i.ItemID
        ",
        [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
        { datasource = "payfrit" }
      )>
    </cfif>
  <cfelse>
    <!--- OLD SCHEMA: Use Categories table --->
    <cfset q = queryExecute(
      "
        SELECT
          i.ItemID,
          i.ItemCategoryID,
          c.CategoryName,
          i.ItemName,
          i.ItemDescription,
          i.ItemParentItemID,
          i.ItemPrice,
          i.ItemIsActive,
          i.ItemIsCheckedByDefault,
          i.ItemRequiresChildSelection,
          i.ItemMaxNumSelectionReq,
          i.ItemIsCollapsible,
          i.ItemSortOrder,
          i.ItemStationID,
          s.StationName,
          s.StationColor
        FROM Items i
        INNER JOIN Categories c ON c.CategoryID = i.ItemCategoryID
        LEFT JOIN Stations s ON s.StationID = i.ItemStationID
        WHERE c.CategoryBusinessID = ?
        ORDER BY i.ItemParentItemID, i.ItemSortOrder, i.ItemID
      ",
      [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
      { datasource = "payfrit" }
    )>
  </cfif>

  <cfset rows = []>

  <!--- For unified schema with Categories table, add category headers first --->
  <cfif newSchemaActive AND isDefined("qCategories")>
    <cfloop query="qCategories">
      <!--- Add category as a virtual parent item --->
      <!--- Use CategoryID as ItemID, and set ItemCategoryID to same value --->
      <!--- Set ItemParentItemID to 0 to mark as root level --->
      <cfset arrayAppend(rows, {
        "ItemID": qCategories.CategoryID,
        "ItemCategoryID": qCategories.CategoryID,
        "ItemCategoryName": qCategories.CategoryName,
        "ItemName": qCategories.CategoryName,
        "ItemDescription": "",
        "ItemParentItemID": 0,
        "ItemPrice": 0,
        "ItemIsActive": 1,
        "ItemIsCheckedByDefault": 0,
        "ItemRequiresChildSelection": 0,
        "ItemMaxNumSelectionReq": 0,
        "ItemIsCollapsible": 0,
        "ItemSortOrder": qCategories.CategorySortOrder,
        "ItemStationID": "",
        "ItemStationName": "",
        "ItemStationColor": ""
      })>
    </cfloop>
  </cfif>

  <!--- Build a set of category IDs for quick lookup --->
  <cfset categoryIdSet = {}>
  <cfif isDefined("qCategories")>
    <cfloop query="qCategories">
      <cfset categoryIdSet[qCategories.CategoryID] = true>
    </cfloop>
  </cfif>

  <cfloop query="q">
    <!--- For unified schema with Categories: set ParentItemID to CategoryID for top-level items --->
    <!--- Remap old parent IDs to category IDs --->
    <cfset effectiveParentID = q.ItemParentItemID>
    <cfif newSchemaActive AND isDefined("qCategories") AND q.ItemCategoryID GT 0>
      <cfif q.ItemParentItemID EQ 0>
        <!--- Item has no parent but has a category - link to category --->
        <cfset effectiveParentID = q.ItemCategoryID>
      <cfelseif structKeyExists(categoryIdSet, q.ItemParentItemID)>
        <!--- Item's parent IS a category ID - this is correct, keep it --->
        <cfset effectiveParentID = q.ItemParentItemID>
      <cfelseif NOT structKeyExists(categoryIdSet, q.ItemParentItemID)>
        <!--- Parent ID is an old-style category header - remap to CategoryID --->
        <cfset effectiveParentID = q.ItemCategoryID>
      </cfif>
    </cfif>

    <cfset arrayAppend(rows, {
      "ItemID": q.ItemID,
      "ItemCategoryID": q.ItemCategoryID,
      "ItemCategoryName": len(trim(q.CategoryName)) ? q.CategoryName : "",
      "ItemName": q.ItemName,
      "ItemDescription": q.ItemDescription,
      "ItemParentItemID": effectiveParentID,
      "ItemPrice": q.ItemPrice,
      "ItemIsActive": q.ItemIsActive,
      "ItemIsCheckedByDefault": q.ItemIsCheckedByDefault,
      "ItemRequiresChildSelection": q.ItemRequiresChildSelection,
      "ItemMaxNumSelectionReq": q.ItemMaxNumSelectionReq,
      "ItemIsCollapsible": q.ItemIsCollapsible,
      "ItemSortOrder": q.ItemSortOrder,
      "ItemStationID": len(trim(q.ItemStationID)) ? q.ItemStationID : "",
      "ItemStationName": len(trim(q.StationName)) ? q.StationName : "",
      "ItemStationColor": len(trim(q.StationColor)) ? q.StationColor : ""
    })>
  </cfloop>

  <!--- For unified schema: Add template-linked modifiers as virtual children of menu items --->
  <cfif newSchemaActive>
    <!--- Get template links: which menu items use which templates --->
    <cfset qTemplateLinks = queryExecute(
      "
        SELECT
          tl.ItemID as MenuItemID,
          tmpl.ItemID as TemplateItemID,
          tmpl.ItemName as TemplateName,
          tmpl.ItemDescription as TemplateDescription,
          tmpl.ItemRequiresChildSelection as TemplateRequired,
          tmpl.ItemMaxNumSelectionReq as TemplateMaxSelections,
          tmpl.ItemIsCollapsible as TemplateIsCollapsible,
          tl.SortOrder as TemplateSortOrder
        FROM ItemTemplateLinks tl
        INNER JOIN Items tmpl ON tmpl.ItemID = tl.TemplateItemID AND tmpl.ItemIsActive = 1
        INNER JOIN Items menuItem ON menuItem.ItemID = tl.ItemID
        WHERE menuItem.ItemBusinessID = ?
          AND menuItem.ItemIsActive = 1
        ORDER BY tl.ItemID, tl.SortOrder
      ",
      [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
      { datasource = "payfrit" }
    )>

    <!--- Get template options --->
    <cfset qTemplateOptions = queryExecute(
      "
        SELECT DISTINCT
          opt.ItemID as OptionItemID,
          opt.ItemParentItemID as TemplateItemID,
          opt.ItemName as OptionName,
          opt.ItemDescription as OptionDescription,
          opt.ItemPrice as OptionPrice,
          opt.ItemIsCheckedByDefault as OptionIsDefault,
          opt.ItemSortOrder as OptionSortOrder
        FROM Items opt
        INNER JOIN ItemTemplateLinks tl ON tl.TemplateItemID = opt.ItemParentItemID
        INNER JOIN Items menuItem ON menuItem.ItemID = tl.ItemID
        WHERE menuItem.ItemBusinessID = ?
          AND menuItem.ItemIsActive = 1
          AND opt.ItemIsActive = 1
        ORDER BY opt.ItemParentItemID, opt.ItemSortOrder
      ",
      [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
      { datasource = "payfrit" }
    )>

    <!--- Build template options map: templateID -> [options] --->
    <cfset templateOptionsMap = {}>
    <cfloop query="qTemplateOptions">
      <cfif NOT structKeyExists(templateOptionsMap, qTemplateOptions.TemplateItemID)>
        <cfset templateOptionsMap[qTemplateOptions.TemplateItemID] = []>
      </cfif>
      <cfset arrayAppend(templateOptionsMap[qTemplateOptions.TemplateItemID], {
        "ItemID": qTemplateOptions.OptionItemID,
        "ItemName": qTemplateOptions.OptionName,
        "ItemDescription": qTemplateOptions.OptionDescription,
        "ItemPrice": qTemplateOptions.OptionPrice,
        "ItemIsCheckedByDefault": qTemplateOptions.OptionIsDefault,
        "ItemSortOrder": qTemplateOptions.OptionSortOrder
      })>
    </cfloop>

    <!--- Add templates and their options as virtual children --->
    <!--- Use virtual IDs to make each template instance unique per menu item --->
    <!--- Virtual ID format: menuItemID * 100000 + templateID for templates --->
    <!---                    menuItemID * 100000 + optionID for options --->
    <cfset addedTemplates = {}>
    <cfloop query="qTemplateLinks">
      <cfset menuItemID = qTemplateLinks.MenuItemID>
      <cfset templateID = qTemplateLinks.TemplateItemID>
      <cfset linkKey = menuItemID & "_" & templateID>

      <!--- Skip duplicates --->
      <cfif structKeyExists(addedTemplates, linkKey)>
        <cfcontinue>
      </cfif>
      <cfset addedTemplates[linkKey] = true>

      <!--- Generate unique virtual ID for this template instance --->
      <cfset virtualTemplateID = menuItemID * 100000 + templateID>

      <!--- Add template as modifier group (child of menu item) --->
      <cfset arrayAppend(rows, {
        "ItemID": virtualTemplateID,
        "ItemCategoryID": 0,
        "ItemCategoryName": "",
        "ItemName": qTemplateLinks.TemplateName,
        "ItemDescription": qTemplateLinks.TemplateDescription,
        "ItemParentItemID": menuItemID,
        "ItemPrice": 0,
        "ItemIsActive": 1,
        "ItemIsCheckedByDefault": 0,
        "ItemRequiresChildSelection": qTemplateLinks.TemplateRequired,
        "ItemMaxNumSelectionReq": qTemplateLinks.TemplateMaxSelections,
        "ItemIsCollapsible": qTemplateLinks.TemplateIsCollapsible,
        "ItemSortOrder": qTemplateLinks.TemplateSortOrder,
        "ItemStationID": "",
        "ItemStationName": "",
        "ItemStationColor": ""
      })>

      <!--- Add template options as children of virtual template --->
      <cfif structKeyExists(templateOptionsMap, templateID)>
        <cfloop array="#templateOptionsMap[templateID]#" index="opt">
          <!--- Generate unique virtual ID for this option instance --->
          <cfset virtualOptionID = menuItemID * 100000 + opt.ItemID>
          <cfset arrayAppend(rows, {
            "ItemID": virtualOptionID,
            "ItemCategoryID": 0,
            "ItemCategoryName": "",
            "ItemName": opt.ItemName,
            "ItemDescription": opt.ItemDescription,
            "ItemParentItemID": virtualTemplateID,
            "ItemPrice": opt.ItemPrice,
            "ItemIsActive": 1,
            "ItemIsCheckedByDefault": opt.ItemIsCheckedByDefault,
            "ItemRequiresChildSelection": 0,
            "ItemMaxNumSelectionReq": 0,
            "ItemIsCollapsible": 0,
            "ItemSortOrder": opt.ItemSortOrder,
            "ItemStationID": "",
            "ItemStationName": "",
            "ItemStationColor": ""
          })>
        </cfloop>
      </cfif>
    </cfloop>
  </cfif>

  <!--- Get brand color for this business --->
  <cfset brandColor = "">
  <cftry>
    <cfset qBrand = queryExecute(
      "SELECT BusinessBrandColor FROM Businesses WHERE BusinessID = ?",
      [ { value = BusinessID, cfsqltype = "cf_sql_integer" } ],
      { datasource = "payfrit" }
    )>
    <cfif qBrand.recordCount GT 0 AND len(trim(qBrand.BusinessBrandColor))>
      <cfset brandColor = qBrand.BusinessBrandColor>
    </cfif>
    <cfcatch>
      <!--- Column may not exist yet, ignore --->
    </cfcatch>
  </cftry>

  <cfset apiAbort({
    "OK": true,
    "ERROR": "",
    "Items": rows,
    "COUNT": arrayLen(rows),
    "SCHEMA": newSchemaActive ? "unified" : "legacy",
    "BRANDCOLOR": brandColor
  })>

  <cfcatch>
    <cfset apiAbort({
      "OK": false,
      "ERROR": "server_error",
      "MESSAGE": "DB error loading items",
      "DETAIL": cfcatch.message
    })>
  </cfcatch>
</cftry>