package requestHandlers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.HashMap;
import java.util.Map;

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

import org.json.JSONArray;
import org.json.JSONObject;

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

public class getItems implements RequestHandler{
	private String sql = "SELECT ItemName, ItemPrice, ItemQty, ItemTaxRate, round(ItemPrice*ItemQty*(1+ItemTaxRate), 2) AS total" +
			             " FROM R2LineItem" +
			             " WHERE ReceiptID=? AND R2LineItem.UserID = (SELECT UserID FROM R2User WHERE Email=?)";
	
	private static Map<String, String> ops = new HashMap<String, String>();
	private static Map<String, String> colAlias = new HashMap<String, String>();
	
	static
	{
		ops.put("eq", "="); //equal
		ops.put("ne", "<>");//not equal
		ops.put("lt", "<");//less than
		ops.put("le", "<=");//less than or equal
		ops.put("gt", ">");//greater than
		ops.put("ge", ">=");//greater than or equal
		ops.put("bw", "LIKE");//begins with
		ops.put("bn", "NOT LIKNE");//doesn't begin with
		ops.put("in", "LIKE");//is in
		ops.put("ni", "NOT LIKE");//is not in
		ops.put("ew", "LIKE");//ends with
		ops.put("en", "NOT LIKE");//doesn't end with
		ops.put("cn", "LIKE");// contains
		ops.put("nc", "NOT LIKE");//doesn't contain
		
		colAlias.put("item", "ItemName");
		colAlias.put("price", "ItemPrice");
		colAlias.put("qty", "ItemQty");
		colAlias.put("taxRate", "ItemTaxRate");
		colAlias.put("total", "round(ItemPrice*ItemQty*(1+ItemTaxRate), 2)");
	}
	
	@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();
		JSONObject ret = new JSONObject();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		JSONArray rows = new JSONArray();
		JSONArray temp = new JSONArray();
		ret.put("ret", false);
		ret.put("page", 1);
		ret.put("total", 0);
		ret.put("records", 0);
		ret.put("rows", rows);
		try
		{
			HttpSession session = req.getSession(false);
			if(session != null)
			{
				R2UserBean userbean = (R2UserBean)session.getAttribute("currentSessionUser");
				if(userbean != null)
				{
					int rid = -1;
					int page = 1;
					int limit = 5;
					try
					{
						rid = req.getParameter("rid") == null || req.getParameter("rid").equals("") ? -1 : Integer.parseInt(req.getParameter("rid"));
						page = req.getParameter("page") == null || req.getParameter("page").equals("") ? 1 : Integer.parseInt(req.getParameter("page")); // get the requested page
						limit = req.getParameter("rows") == null || req.getParameter("rows").equals("") ? 5 : Integer.parseInt(req.getParameter("rows"));
					}catch(NumberFormatException nfe)
					{
						System.err.println(nfe);
					}
					
					String sidx = req.getParameter("sidx") == null || req.getParameter("sidx").equals("") ? "date" : req.getParameter("sidx");
					String sord = req.getParameter("sord") == null || req.getParameter("sord").equals("") ? "asc" : req.getParameter("sord");

					System.out.println("page = " + req.getParameter("page"));
					System.out.println("limit = " + req.getParameter("rows"));
					System.out.println("sidx = " + req.getParameter("sidx"));
					System.out.println("sord = " + req.getParameter("sord"));
					
					// search request
					boolean _search = req.getParameter("_search") == null || req.getParameter("_search").equals("") ? false : Boolean.parseBoolean(req.getParameter("_search"));
					String field = req.getParameter("searchField");
					String oper = req.getParameter("searchOper");
					String str = req.getParameter("searchString");
					
					
					con = ConnectionManager.getConnection();
					if(!_search)
					{
						stmt = con.prepareStatement(sql + " ORDER BY " + colAlias.get(sidx) + " " + sord);
					}
					else
					{
						stmt = con.prepareStatement(sql + " AND " + getSearchClause(colAlias.get(field), oper, str) + "ORDER BY " + colAlias.get(sidx) + " " + sord);
					}
					int index = 1;
					stmt.setInt(index++, rid);
					stmt.setString(index++, userbean.getEmail());
					System.out.println("sql = " + stmt.toString());
					rs = stmt.executeQuery();
					if(rs != null)
					{
						
						ResultSetMetaData rsmd = rs.getMetaData();
						while(rs.next())
						{
							JSONArray record = new JSONArray();
							/*for(int i = 1; i <= rsmd.getColumnCount(); ++i)
							{
								String colname = rsmd.getColumnLabel(i);
								if(colname.endsWith("date"))
								{
									record.put(colname, rs.getDate(colname).toString());
									System.out.println(colname + " : " + rs.getDate(colname));
								}
								else
								{
									record.put(colname, rs.getObject(colname));
									System.out.println(colname + " : " + rs.getObject(colname));
								}
							}*/
							for(int i = 1; i <= rsmd.getColumnCount(); ++i)
							{
								record.put(rs.getObject(i).toString());
							}
							//System.out.println("record : " + record.toString());
							temp.put(record);
						}
						
						int count = temp.length();
						int total_pages = 1;
						if(count > 0 && limit > 0)
						{
							total_pages = count / limit;
							if(count % limit > 0)
								total_pages++;
						}
						if(page > total_pages)
						{
							page = total_pages;
						}
						int start = limit * page - limit;
						if(start < 0)
						{
							start = 0;
						}
						int end = start + limit;
						if(end > temp.length())
							end = temp.length();
						/*System.out.println("count = " + count);
						System.out.println("total_pages = " + total_pages);
						System.out.println("page = " + page);
						System.out.println("start = " + start);*/
						
						
						for(int i=start; i<end; ++i)
						{
							rows.put(temp.get(i));
						}

						ret.put("page", page);
						ret.put("total", total_pages);
						ret.put("records", count);
						ret.put("rows", rows);
						ret.put("ret", true);
					}
				}
			}
		}
		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();
		}
	}
	private String getSearchClause(final String field, final String oper, String val)
	{
		StringBuilder sb = new StringBuilder();
		if(oper.equalsIgnoreCase("bw") || oper.equalsIgnoreCase("bn")) val += "%";
	    if(oper.equalsIgnoreCase("ew") || oper.equalsIgnoreCase("en") ) val = "%" + val;
	    if(oper.equalsIgnoreCase("cn") || oper.equalsIgnoreCase("nc") || oper.equalsIgnoreCase("in") || oper.equalsIgnoreCase("ni")) val = "%" + val + "%";
	    sb.append(" ").
	    append(field).
	    append(" ").
	    append(ops.get(oper)).
	    append(" ");
	    if(isNumeric(val))
	    {
	    	sb.append(val);
	    }
	    else
	    {
	    	sb.append("'").append(val).append("'");
	    }
	    sb.append(" ");
	    return sb.toString();
	}
	private boolean isNumeric(String str)  
	{  
	  try  
	  {  
	    double d = Double.parseDouble(str);  
	  }  
	  catch(NumberFormatException nfe)  
	  {  
	    return false;  
	  }  
	  return true;  
	}
}
