CryCupboard 2'0

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.coms service?
• The 3 Cs
• Centralization
• Cross-referencing
• Coordination
• Why do we need it?

4
The Larger Aspiration

• Combat the fastfood phenomena
• Internet niche thats still untapped
• Tool to help career parents

5
The Personal Aspiration
6
What well 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
Meal Type
RecipeTypeLine
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 users 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) gt
  g.shelf_life_days) THEN'overdue'
• ELSE 'ok' END AS status
• FROM finalized_grocery_items f,
  groceryitemscategorymember g
• WHERE f.item_idg.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_idir.r_id
• AND ir.item_idg.item_id
• AND g.ic_idic.ic_id
• AND gsac.g_idgs.g_id
• AND gsac.a_ida.a_id
• AND gsac.ic_idic.ic_id
• AND gsla.g_idgs.g_id
• AND gsla.a_ida.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_idv.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

17
Some SQL insights

• Cast operation
• Similar to Oracles 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,'ltbrgt'),
  r.r_serving_size, r.r_scope, r.added
• FROM
• 0xD represents a carriage return

19
Some SQLquestions

• 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 users favorite recipes list
• Suggest recommended meals with consideration to
  in stock items
• Functionality to download grocery list to PDA