public List<Item> generateDailySalesReport(Date d) throws SQLException, ParseException {
		List<Item> soldItems = new ArrayList<Item>();		
		PreparedStatement ps = con.prepareStatement(
				"SELECT I.upc, item_category, item_price, SUM(quantity) AS units " +
				"FROM Item I, Purchase P, PurchaseItem PI " +
				"WHERE I.upc = PI.upc AND P.receiptId = PI.receiptId AND P.purchase_date = ? " +
				"GROUP BY I.upc, item_category, item_price " +
				"ORDER BY item_category");
		
		java.sql.Date jday = new java.sql.Date(d.getTime());
		ps.setDate(1, jday);

		ResultSet rs = ps.executeQuery();		// get the list of sold items
		
		String last_category = "";
		int cat_qty = 0;
		float cat_sales = 0;
		
		// convert each result to item
		while (rs.next()) {
			
			try {
				String upc = rs.getString("upc");
				String categ = rs.getString("item_category");
				int qty = rs.getInt("units");
				float price = rs.getFloat("item_price");
				
				// get the total sales for that category
				if (last_category == "" || last_category.equalsIgnoreCase(categ)) {
					last_category = categ;	
					cat_qty += qty;
					cat_sales += price * qty;
					
				} else {
					Item ni = new Item();
					ni.setCategory("Total");
					ni.setQuantity(cat_qty);
					ni.setTotalPrice(cat_sales);
					
					soldItems.add(ni);
					
					last_category = categ;	
					cat_qty = qty;
					cat_sales = price * qty;
				}
				
				Item i = new Item();
				i.setUPC(upc);
				i.setCategory(categ);
				i.setQuantity(qty);
				i.setPrice(price);
				i.setTotalPrice(price * qty);
				
				soldItems.add(i);
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
		
		// add the last one
		Item ni = new Item();
		ni.setCategory("Total");
		ni.setQuantity(cat_qty);
		ni.setTotalPrice(cat_sales);
		
		soldItems.add(ni);
		
		return soldItems;
	}