

CREATE TABLE R2User (
	UserID      INT NOT NULL PRIMARY KEY, /* Internal Use */
	Email       VARCHAR(50), /* Used as User Name, logon */
	FirstName   VARCHAR(50),
	LastName    VARCHAR(50),
	Address1    VARCHAR(50),
	Address2    VARCHAR(50),
	City        VARCHAR(50),
	State       VARCHAR(50),
	Zip         VARCHAR(50),
	Country     VARCHAR(50)
);


CREATE TABLE R2Card (
	CardID    VARCHAR(24) NOT NULL PRIMARY KEY,
	UserID 	  INT NOT NULL
);

CREATE TABLE R2UserPass (
	UserName      VARCHAR(50) NOT NULL PRIMARY KEY, /* Use User EMail to login */
	PassWord      VARCHAR(15) NOT NULL
);


CREATE TABLE R2Receipt (
	ReceiptID    INT NOT NULL,
	UserID       INT NOT NULL,
	CardID       VARCHAR(24) NOT NULL,
	MerchantID   INT NOT NULL,
	PurchaseDate TIMESTAMP NOT NULL,
	Img          VARCHAR(100) NOT NULL DEFAULT './receiptImgs/no_photo.jpg',
        PRIMARY KEY(ReceiptID, UserID)
);


CREATE TABLE R2LineItem(
	ReceiptID INT NOT NULL,
	UserID INT NOT NULL,
	ItemName VARCHAR(36) NOT NULL,
	ItemPrice  FLOAT NOT NULL,
	ItemQty   INT NOT NULL,
	ItemTaxRate FLOAT NOT NULL,
    PRIMARY KEY(ReceiptID, UserID, ItemName)
);

CREATE TABLE R2Merchant (
	MerchantID INT NOT NULL PRIMARY KEY,
	MerchantName VARCHAR(24),
	CategoryID INT,

	Address1    VARCHAR(50),
	Address2    VARCHAR(50),
	City        VARCHAR(50),
	State       VARCHAR(50),
	Zip         VARCHAR(50),
	Country     VARCHAR(50),
	Phone        VARCHAR(24)
);

CREATE TABLE R2Category (
	CategoryID	INT NOT NULL PRIMARY KEY,
	CategoryName VARCHAR(50),
	CategoryDesc VARCHAR(50)
);

SELECT ItemName, ItemPrice, ItemQty, ItemTaxRate, MerchantName, PurchaseDate FROM R2LineItem, R2Receipt, R2Merchant, R2User, R2Card WHERE R2User.UserID=R2Card.UserID AND R2Receipt.UserID=R2User.UserID AND R2User.Email='song.x.guo@oracle.com' AND R2Receipt.ReceiptID=R2LineItem.ReceiptID AND R2Receipt.UserID=R2User.UserID;

SELECT ItemName, ItemPrice, ItemQty, ItemTaxRate, round(ItemPrice*ItemQty*(1+ItemTaxRate), 2) AS total FROM R2LineItem WHERE ReceiptID IN (SELECT ReceiptID FROM R2Receipt WHERE UserID = (SELECT UserID FROM R2User WHERE Email='song.x.guo@oracle.com'));



SELECT ItemName, ItemPrice, ItemQty, ItemTaxRate, round(ItemPrice*ItemQty*(1+ItemTaxRate), 2) AS total FROM R2LineItem, R2Receipt, R2user WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND Email='song.x.guo@oracle.com';


SELECT R2LineItem.ReceiptID AS rid, ItemName AS item, ItemPrice AS price, ItemQty AS qty, ItemTaxRate AS taxRate, round(ItemPrice*ItemQty*(1+ItemTaxRate), 2) AS total, R2Merchant.MerchantName AS merchantName, PurchaseDate AS date, CardID AS card FROM R2LineItem, R2Receipt, R2user, R2Merchant WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND Email='song.x.guo@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID ORDER BY qty ASC;



SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS sum, R2Category.CategoryName AS category FROM R2LineItem, R2Receipt, R2user, R2Merchant, R2Category WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND Email='song.x.guo@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID AND R2Category.CategoryID=R2Merchant.CategoryID AND Year(PurchaseDate) IN (2012, 2013) GROUP BY R2Category.CategoryName;


SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS sum, R2Category.CategoryName AS category, Month(PurchaseDate) AS month, CategoryName 
FROM R2LineItem, R2Receipt, R2user, R2Merchant, R2Category 
WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND 
R2Receipt.UserID=R2User.UserID AND 
R2User.UserID=1 AND 
R2Merchant.MerchantID=R2Receipt.MerchantID AND 
R2Category.CategoryID=R2Merchant.CategoryID AND
Year(PurchaseDate)=2012
GROUP BY R2Category.CategoryName, month
ORDER BY month;


SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS total, DATEPART(Month, PurchaseDate) AS month 
FROM R2LineItem, R2Receipt 
WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND 
R2LineItem.UserID = (SELECT UserID FROM R2User WHERE Email=?) AND 
DATEPART(Year, PurchaseDate)=? 
GROUP BY DATEPART(Month, PurchaseDate) 
ORDER BY month




SELECT R2LineItem.ReceiptID AS rid, ItemName AS item, R2Merchant.MerchantName AS merchantName, ItemPrice AS price, ItemQty AS qty, ItemTaxRate AS taxRate, round(ItemPrice*ItemQty*(1+ItemTaxRate), 2) AS total, CardID AS card, PurchaseDate AS date FROM R2LineItem, R2Receipt, R2user, R2Merchant WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND Email='song.x.guo@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID AND  qty < 2 ORDER BY date asc

SELECT SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2))  AS total, ReceiptID from R2LineItem WHERE UserID=1 GROUP BY ReceiptID;


SELECT DISTINCT R2Receipt.ReceiptID AS rid, R2Merchant.MerchantName AS merchantName, SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS total, CardID AS card, PurchaseDate AS date FROM R2LineItem, R2Receipt, R2user, R2Merchant WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND R2LineItem.UserID=R2Receipt.UserID AND Email='song.x.guo@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID GROUP BY R2LineItem.ReceiptID ORDER BY date asc;


SELECT DISTINCT R2Receipt.ReceiptID AS rid, R2Merchant.MerchantName AS merchantName, SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS total, CardID AS card, PurchaseDate AS date FROM R2LineItem, R2Receipt, R2user, R2Merchant WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND R2LineItem.UserID=R2Receipt.UserID AND Email='song.x.guo@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID GROUP BY R2LineItem.ReceiptID ORDER BY date asc;


SELECT DISTINCT R2Receipt.ReceiptID AS rid, R2Merchant.MerchantName AS merchantName, SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) AS total, CardID AS card, PurchaseDate AS date FROM R2LineItem, R2Receipt, R2user, R2Merchant WHERE R2LineItem.ReceiptID=R2Receipt.ReceiptID AND R2Receipt.UserID=R2User.UserID AND R2LineItem.UserID=R2Receipt.UserID AND Email='si.meng@oracle.com' AND R2Merchant.MerchantID=R2Receipt.MerchantID GROUP BY R2LineItem.ReceiptID



SELECT r2receipt.receiptid AS rid, merchantName, SUM(round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)) as total, cardid AS card, PurchaseDate AS date FROM R2Merchant, r2receipt, r2lineitem where r2receipt.userid=r2lineitem.userid and r2receipt.receiptid=r2lineitem.receiptid and r2receipt.userid=(SELECT UserID FROM R2User WHERE Email='song.x.guo@oracle.com') and R2Merchant.merchantid=r2receipt.merchantid group by r2receipt.receiptid;





























