CryCupboard 2.0 The New Face to Grocery Shopping Cheralyn Cofer CS491 5.29.06

1. CryCupboard 2.0 The New Face to Grocery Shopping Cheralyn Cofer CS491 5.29.06

2. cry cupboard? • Definition • Origin • Why use it as a website name?

3. Recap • What is CryCupboard.com’s service? • The 3 C’s: • Centralization • Cross-referencing • Coordination • Why do we need it?

4. The Larger Aspiration • Combat the fastfood phenomena • Internet niche that’s still untapped • Tool to help career parents

5. The Personal Aspiration

6. What we’ll discuss • Rethinking the orthography • The grocery list as a “smart list” • Some SQL insights

7. Rethinking the orthography • The traditional one-to-many organization of a recipe box • Problem: Is Chicken Caesar Salad a chicken dish or a salad dish? • It can be both! Recipe Meal Type

8. Rethinking the orthography • The virtual recipe box allows many-to-many organization • There is no “shelf” • Chicken Caesar Salad is both a chicken dish and a salad • Restructuring Recipe RecipeTypeLine Meal Type

9. The grocery list as a “smart list” • Step 1: The big picture SELECT[recipe ingredients]… UNION SELECT[ad-hoc items]…

10. The grocery list as a “smart list” • Step 2: User considerations • Grocery list could be 20+ items • Tedious to un-check the same items Solution  Find a way to “remember” what user has in-stock

11. The grocery list as a “smart list” • Step 3: Record finalized list items Procedure  • Record checked items • Record un-checked items in not in database

12. The grocery list as a “smart list” • Step 3 (cont): Record finalized list items Implementation  • The 3 scenarios • Actions: Insert / Update / Do nothing • Data value: New timestamp / Keep existing timestamp • Data structure: use Set vs. Map Example

13. The grocery list as a “smart list” • Step 4: Now put the smart in “smart list”  Automate some of the user’s un-checking How: • Compare the recorded ingredient history to the average shelf-life of each ingredient to evaluate ingredient status Purpose

14. The grocery list as a “smart list” • Step 4 (cont): Now put the smart in “smart list” Implementation: • Create a view that does the calculation on the fly • Left-join the data set with this view results • Mark each ingredient as either ok, overdue, or n/a

15. The grocery list as a “smart list” • Step 4 (cont): Now put the smart in “smart list” CREATE VIEW view_smart_items AS SELECT f.u_id, f.item_id, f.date_finalized, g.shelf_life_days, datediff(now(), f.date_finalized) AS diff, CASE 1 WHEN (g.shelf_life_days IS NULL) THEN 'na' WHEN (datediff(now(), f.date_finalized) > g.shelf_life_days) THEN'overdue' ELSE 'ok' END AS status FROM finalized_grocery_items f, groceryitemscategorymember g WHERE f.item_id=g.item_id

16. The grocery list as a “smart list” • Step 5: Putting it all together query = (SELECT tmp.a_id, tmp.aisle, tmp.ic_id, tmp.aislecategory, tmp.unit, tmp.quantity, tmp.g_id, tmp.g_name, tmp.item_id, tmp.itemname, tmp.r_id, tmp.r_name, tmp.item_brand, tmp.details, tmp.sequence, CAST(tmp.ingrec_id AS CHAR), v.status, v.date_finalized FROM ( SELECT a.a_id, a.a_name AS aisle, ic.ic_id, ic.ic_name aislecategory, ir.unit, ir.quantity, gs.g_id, gs.g_name, g.item_id, g.item_name itemname, r.r_id, r.r_name, ir.item_brand, ir.details, gsla.location_seq AS sequence, ir.ingrec_id FROM recipes r, ingredientsinrecipe ir, itemcategories ic, grocerystoreaislecategory gsac, aisle a, grocerystores gs, grocerystorelocatedaisles gsla, groceryitemscategorymember g WHERE r.r_id=ir.r_id AND ir.item_id=g.item_id AND g.ic_id=ic.ic_id AND gsac.g_id=gs.g_id AND gsac.a_id=a.a_id AND gsac.ic_id=ic.ic_id AND gsla.g_id=gs.g_id AND gsla.a_id=a.a_id AND r.r_id in (SELECT r_id FROM recipescomposingmealplans where mp_id= ? ) AND gs.g_id = ? ) tmp LEFT JOIN view_smart_items v on (tmp.item_id=v.item_id AND v.u_id= ?) ) UNION (SELECT tmp.a_id, tmp.a_name, tmp.ic_id, tmp.ic_name,'','',tmp.g_id,tmp.g_name,tmp.item_id, tmp.item_name,'','','','',tmp.location_seq,'', v.status, v.date_finalized FROM (SELECT a.a_id, a.a_name, ic.ic_id, ic.ic_name,gs.g_id,gs.g_name,g.item_id, g.item_name,gsla.location_seq FROM groceryitemscategorymember g, itemcategories ic, grocerystoreaislecategory gsac, aisle a, grocerystores gs, grocerystorelocatedaisles gsla WHERE g.ic_id=ic.ic_id AND gsac.g_id=gs.g_id AND gsac.a_id=a.a_id AND gsac.ic_id=ic.ic_id AND gsla.g_id=gs.g_id AND gsla.a_id=a.a_id AND gs.g_id = ? AND g.item_id IN ( " + itemsQueryIn + " ) ) tmp LEFT JOIN view_smart_items v on (tmp.item_id=v.item_id AND v.u_id= ?) ) ORDER BY sequence, aisle, aislecategory, itemname ;

17. Some SQL insights • Cast operation • Similar to Oracle’s To_char and To_Date functions • When to use • Why I used SELECT CAST(tmp.ingrec_id AS CHAR)…

18. Some SQL insights • Using hexidecimal • When to use • Why I used SELECT r.r_id, r.r_name, r.r_source, replace(r.r_directions,0xD,'<br>'), r.r_serving_size, r.r_scope, r.added FROM … • #0xD represents a carriage return

19. Some SQL…questions • An SQL question • Is there a function that takes an array and outputs a list such as: WHERE (r.r_type like ‘%Appetizer%’ OR r.r_type like ‘%Dessert%’ OR r.r_type like ‘%Side dish%’) • How do you use the Prepared Statement ? with a comma-separated list such as: WHERE r.r_id IN (23,45,88)

20. In Conclusion • What we discussed • Rethinking the orthography • The grocery list as a “smart list” • Some SQL insights

21. In Conclusion • Some fun things we can do next • Automate a user’s “favorite recipes” list • Suggest recommended meals with consideration to “in stock” items • Functionality to download grocery list to PDA