package requestHandlers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.json.JSONArray;

import Beans.R2UserBean;
import Util.ConnectionManager;
import Util.DBUtil;

public class getStatPie implements RequestHandler{
	private final String sql = "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=? "
			                 + "AND R2Merchant.MerchantID=R2Receipt.MerchantID AND R2Category.CategoryID=R2Merchant.CategoryID ";
	@Override
	public void processRequest(Object user, HttpServletRequest req,
			HttpServletResponse res, Object[] services, Object log)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		PrintWriter out = res.getWriter();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		JSONArray ret = new JSONArray();
		
		try
		{
			HttpSession session = req.getSession(false);
			if(session != null)
			{
				R2UserBean userbean = (R2UserBean)session.getAttribute("currentSessionUser");
				if(userbean != null)
				{
					con = ConnectionManager.getConnection();
					String year = req.getParameter("year");
					//System.out.println("year = " + year);
					if(year == null || year.equals("") || year.equalsIgnoreCase("all"))
					{
						stmt = con.prepareStatement(sql + "GROUP BY R2Category.CategoryName");
					}
					else
					{
						int y = Calendar.getInstance().get(Calendar.YEAR);
						try
						{
							y = Integer.parseInt(year);
							stmt = con.prepareStatement(sql + "AND Year(PurchaseDate)=" + y + " GROUP BY R2Category.CategoryName");
						}
						catch(NumberFormatException nfe)
						{
							stmt = con.prepareStatement(sql + "GROUP BY R2Category.CategoryName");
							System.err.println(nfe);
						}
					}
					stmt.setString(1, userbean.getEmail());
					System.out.println(stmt);
					rs = stmt.executeQuery();
					if(rs != null)
					{
						
						while(rs.next())
						{
							JSONArray arr = new JSONArray();
							arr.put(rs.getString("category"));
							arr.put(rs.getDouble("sum"));
							ret.put(arr);
						}
					}
				}
			}
		}
		catch (Throwable theException) 	    
		{
		     System.out.println(theException); 
		}
		finally
		{
			/*System.out.println("ret json:");
			System.out.println(ret);*/
			DBUtil.closeResultSet(rs);
			DBUtil.closeStmt(stmt);
			DBUtil.closeConnection(con);
			out.print(ret);
			out.close();
		}
	}

}
