package ws;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import Util.ConnectionManager;
import Util.DBUtil;

public class R2SystemWS {
	private String addReceiptSql = "INSERT INTO R2Receipt(ReceiptID, UserID, CardID, MerchantID, PurchaseDate) " +
			                   "SELECT MAX(R2Receipt.ReceiptID)+1, R2User.UserID, ?, R2Merchant.MerchantID, ? " +
			                   "FROM R2User, R2Merchant, R2Receipt " +
			                   "WHERE R2User.Email=? AND " +
			                   "R2Merchant.MerchantName=? AND " +
			                   "R2Receipt.UserID=R2User.UserID";
	private String addItemSql = "INSERT INTO R2LineItem(ReceiptID, UserID, ItemName, ItemPrice, ItemQty, ItemTaxRate) " +
			                    "SELECT ?, UserID, ?, ?, ?, ? " +
			                    "FROM R2User " +
			                    "WHERE Email=?";
	public boolean addReceipt(final String User, final String Card, final String Merchant, final String PurchaseDate)
	{
		boolean ret = false;
		Connection con = null;
		PreparedStatement stmt = null;
		if(User == null || Card == null || Merchant == null || PurchaseDate == null)
		{
			return ret;
		}
		try
		{
			con = ConnectionManager.getConnection();
			stmt = con.prepareStatement(addReceiptSql);
			int index = 1;
			stmt.setString(index++, Card);
			stmt.setString(index++, PurchaseDate);
			stmt.setString(index++, User);
			stmt.setString(index++, Merchant);
			System.out.println(stmt);
			if(stmt.execute())
			{
				ret = true;
			}
		}catch(SQLException sqle)
		{
			System.err.println(sqle);
		}
		finally
		{
			DBUtil.closeStmt(stmt);
			DBUtil.closeConnection(con);
		}
		return ret;
	}
	
	public boolean addItem(final int ReceiptID, final String User, final String ItemName, final double price, final int qty, final double taxRate)
	{
		boolean ret = false;
		Connection con = null;
		PreparedStatement stmt = null;
		if(User == null || ReceiptID < 0 || ItemName == null || price < 0.0 || qty < 0 || taxRate < 0.0)
		{
			return ret;
		}
		try
		{
			con = ConnectionManager.getConnection();
			stmt = con.prepareStatement(addItemSql);
			int index = 1;
			stmt.setInt(index++, ReceiptID);
			stmt.setString(index++, ItemName);
			stmt.setDouble(index++, price);
			stmt.setInt(index++, qty);
			stmt.setDouble(index++, taxRate);
			stmt.setString(index++, User);
			System.out.println(stmt);
			if(stmt.execute())
			{
				ret = true;
			}
		}catch(SQLException sqle)
		{
			System.err.println(sqle);
		}
		finally
		{
			DBUtil.closeStmt(stmt);
			DBUtil.closeConnection(con);
		}
		return ret;
	}
	
	public static void main(final String[] args)
	{
		R2SystemWS ws = new R2SystemWS();
		//ws.addReceipt("song.x.guo@oracle.com", "939320203", "Costco", "2002-06-21 19:32:21");
		ws.addItem(456,"song.x.guo@oracle.com", "LED TV", 449.0, 1, 0.07);
	}
}
