List Files | Just Commands | Repo | Logs

← back | Copy

export const all = `
SELECT 
    'TierSet' as type,
    ts.name as name,
    ts.refId as refId,
    NULL as parent_refId,
    NULL as rank,
    NULL as hours,
    NULL as quantity
FROM tierSets ts

UNION ALL

SELECT 
    'Tier' as type,
    t.name as name, 
    t.refId as refId,
    (SELECT ts.refId FROM tierSets ts WHERE ts.id = JSON_EXTRACT(t.tierSets, '$[0]')) as parent_refId,
    t.rank,
    NULL as hours,
    NULL as quantity
FROM tiers t

UNION ALL

SELECT 
    'Menu' as type,
    m.name as name,
    m.refId as refId, 
    NULL as parent_refId,
    NULL as rank,
    NULL as hours,
    NULL as quantity
FROM menus m

UNION ALL

SELECT 
    'Offer' as type,
    o.name as name,
    o.refId as refId,
    NULL as parent_refId,
    NULL as rank,
    NULL as hours,
    NULL as quantity  
FROM offers o

UNION ALL

SELECT 
    'CostTime' as type,
    ct.name as name,
    ct.refId as refId,
    NULL as parent_refId,
    NULL as rank,
    ct.hours,
    NULL as quantity
FROM costsTime ct

UNION ALL

SELECT 
    'CostMaterial' as type,
    cm.name as name,
    cm.refId as refId,
    NULL as parent_refId, 
    NULL as rank,
    NULL as hours,
    cm.quantity
FROM costsMaterial cm

ORDER BY type, rank, name;
`;

export const getAll = `
SELECT 'tierSets' as collection, COUNT(*) as count FROM tierSets 
UNION ALL
SELECT 'tiers' as collection, COUNT(*) as count FROM tiers 
UNION ALL
SELECT 'menus' as collection, COUNT(*) as count FROM menus 
UNION ALL  
SELECT 'offers' as collection, COUNT(*) as count FROM offers 
UNION ALL
SELECT 'costsTime' as collection, COUNT(*) as count FROM costsTime 
UNION ALL
SELECT 'costsMaterial' as collection, COUNT(*) as count FROM costsMaterial 
UNION ALL
SELECT 'menuTiers' as collection, COUNT(*) as count FROM menuTiers 
`;

export const getAllx = `
SELECT 
    mt.id as menuTier_id,
    mt.refId as menuTier_refId,
    
    -- Menu information
    m.id as menu_id,
    m.name as menu_name,
    m.refId as menu_refId,
    
    -- Offer information  
    o.id as offer_id,
    o.name as offer_name,
    o.refId as offer_refId,
    
    -- Tier information
    t.id as tier_id,
    t.name as tier_name,
    t.refId as tier_refId,
    t.rank as tier_rank,
    
    -- Tier Set information
    ts.id as tierSet_id,
    ts.name as tierSet_name,
    ts.refId as tierSet_refId,
    
    -- Time costs (JSON field containing IDs)
    o.costsTime as offer_costsTime_ids,
    
    -- Material costs (JSON field containing IDs)  
    o.costsMaterial as offer_costsMaterial_ids,
    
    -- Menu copy (JSON field containing IDs)
    mt.menuCopy as menuTier_menuCopy_ids,
    
    -- Books (JSON field containing IDs)
    m.books as menu_books_ids

FROM menuTiers mt
LEFT JOIN menus m ON JSON_EXTRACT(mt.menus, '$[0]') = m.id
LEFT JOIN offers o ON JSON_EXTRACT(mt.offers, '$[0]') = o.id  
LEFT JOIN tiers t ON JSON_EXTRACT(mt.tiers, '$[0]') = t.id
LEFT JOIN tierSets ts ON JSON_EXTRACT(t.tierSets, '$[0]') = ts.id
WHERE mt.org = 'YOUR_ORG_ID'
ORDER BY t.rank;
`;