<commit_before><%@page import="org.postgresql.util.PSQLException"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="t" tagdir="/WEB-INF/tags"%>

<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*"%>

<t:header title="Sales Analytics Precomputed" />
<%

Connection conn=null;
Statement stmt1=null,stmt2=null,stmt3=null;
ResultSet rs1=null,rs2=null,rs3=null;
String sql1 =null, sql2 = null, sql3 = null;


try
{
	try{Class.forName("org.postgresql.Driver");}catch(Exception e){System.out.println("Driver error");}
	conn = DriverManager.getConnection("jdbc:postgresql://ec2-23-21-185-168.compute-1.amazonaws.com:5432/ddbj4k4uieorq7?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory",
	"qwovydljafffgl", "cGdGZam7xcem_isgwfV3FQ_jxs");
	
	/*conn = DriverManager.getConnection(
    	"jdbc:postgresql://localhost/CSE135?" +
    	"user=Bonnie"); */
	
	conn.setAutoCommit(false);
	
	stmt1 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	stmt2 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	stmt3 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	
	String scope = ""+request.getParameter("scope");
	String state = request.getParameter("state");
	String category = request.getParameter("category");
	
	//sql1: Products and Totals for column labels
	
	if(category == null || category.equals("all") || category.equals("null"))
	{
		//category - off
		sql1 = "SELECT * FROM products_total ORDER BY total DESC LIMIT 10";	
	}
	else
	{
		//category - on
		sql1 = "SELECT * FROM products_total WHERE pid IN (SELECT id FROM products WHERE cid = " + category + ") ORDER BY total DESC LIMIT 10";
	}
	
	//sql2: Users/states and totals for row labels
	//sql3: per product per user/state for table data
	
	if (scope.equals("states"))
	{
		if (category == null || category.equals("all") || category.equals("null"))
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - off, state - off
				sql2 = "SELECT * FROM states_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.pid IN (SELECT pt.pid FROM products_total as pt ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - off, state - on
				sql2 = "SELECT * FROM states_total WHERE state = '" + state + "' ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.state = '" + state + "' AND spt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total DESC LIMIT 10)";
			}
		}
		else
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - on, state - off
				sql2 = "SELECT state, total FROM states_categories_total WHERE cid = " + category + " ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT spt.state, spt.total FROM states_products_total AS spt WHERE spt.pid IN (SELECT pt.pid FROM products_total AS pt WHERE pt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - on, state - on
				sql2 = "SELECT * FROM states_categories_total WHERE cid = " + category + " AND state = '" + state + "' ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT spt.state, spt.total FROM states_products_total AS spt WHERE spt.state = '" + state + "' AND spt.pid IN (SELECT pt.pid FROM products_total AS pt WHERE pt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY pt.total DESC LIMIT 10)";
			}
		}
	}
	else //scope = users
	{
		if(category == null || category.equals("all") || category.equals("null"))
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - off, state - off
				sql2 = "SELECT * FROM users_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - off, state - on
				sql2 = "SELECT * FROM users_total AS ut WHERE ut.uid IN (SELECT u.uid FROM users AS u WHERE u.state = '" + state + "') ORDER BY ut.total LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.uid IN (SELECT u.uid FROM users u WHERE u.state = '" + state + "') AND upt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total LIMIT 10)";
			}
		}
		else
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - on, state - off
				sql2 = "SELECT uid, total FROM users_categories_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY upt.total DESC LIMIT 10";
			}
			else
			{
				//category - on, state - on
				sql2 = "SELECT * FROM users_categories_total AS uct WHERE uct.uid IN (SELECT u.uid FROM users AS u WHERE u.state = '" + state + "') ORDER BY uct.total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.uid IN (SELECT u.uid FROM users WHERE u.state = '" + state + "') AND upt.pid in (SELECT p.pid FROM products AS p WHERE p.cid = " + category + ") ORDER BY upt.total DESC LIMIT 20";
			}
		}
	}
	
	//rs1 = products row
	//rs2 = user/state column
	//rs3 = table cells
	rs1 = stmt1.executeQuery(sql1); //1 = pid, 2 = total
	rs2 = stmt2.executeQuery(sql2); //1 = uid OR state, 2 = total
	rs3 = stmt3.executeQuery(sql3);	//1 = uid OR state, 2 = pid, 3 = total
	<commit_msg>Done!<commit_after><%@page import="org.postgresql.util.PSQLException"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="t" tagdir="/WEB-INF/tags"%>

<%@ page import="java.util.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*"%>

<t:header title="Sales Analytics Precomputed" />
<%

Connection conn=null;
Statement stmt1=null,stmt2=null,stmt3=null;
ResultSet rs1=null,rs2=null,rs3=null;
String sql1 =null, sql2 = null, sql3 = null;


try
{
	try{Class.forName("org.postgresql.Driver");}catch(Exception e){System.out.println("Driver error");}
	conn = DriverManager.getConnection("jdbc:postgresql://ec2-23-21-185-168.compute-1.amazonaws.com:5432/ddbj4k4uieorq7?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory",
	"qwovydljafffgl", "cGdGZam7xcem_isgwfV3FQ_jxs");
	
	/*conn = DriverManager.getConnection(
    	"jdbc:postgresql://localhost/CSE135?" +
    	"user=Bonnie"); */
	
	conn.setAutoCommit(false);
	
	stmt1 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	stmt2 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	stmt3 =conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	
	String scope = ""+request.getParameter("scope");
	String state = request.getParameter("state");
	String category = request.getParameter("category");
	
	//sql1: Products and Totals for column labels
	
	if(category == null || category.equals("all") || category.equals("null"))
	{
		//category - off
		sql1 = "SELECT * FROM products_total ORDER BY total DESC LIMIT 10";	
	}
	else
	{
		//category - on
		sql1 = "SELECT * FROM products_total WHERE pid IN (SELECT id FROM products WHERE cid = " + category + ") ORDER BY total DESC LIMIT 10";
	}
	
	//sql2: Users/states and totals for row labels
	//sql3: per product per user/state for table data
	
	if (scope.equals("states"))
	{
		if (category == null || category.equals("all") || category.equals("null"))
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - off, state - off
				sql2 = "SELECT * FROM states_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.pid IN (SELECT pt.pid FROM products_total as pt ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - off, state - on
				sql2 = "SELECT * FROM states_total WHERE state = '" + state + "' ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.state = '" + state + "' AND spt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total DESC LIMIT 10)";
			}
		}
		else
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - on, state - off
				sql2 = "SELECT * FROM states_categories_total WHERE cid = " + category + " ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.pid IN (SELECT pt.pid FROM products_total AS pt WHERE pt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - on, state - on
				sql2 = "SELECT * FROM states_categories_total WHERE cid = " + category + " AND state = '" + state + "' ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM states_products_total AS spt WHERE spt.state = '" + state + "' AND spt.pid IN (SELECT pt.pid FROM products_total AS pt WHERE pt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY pt.total DESC LIMIT 10)";
			}
		}
	}
	else //scope = users
	{
		if(category == null || category.equals("all") || category.equals("null"))
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - off, state - off
				sql2 = "SELECT * FROM users_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total DESC LIMIT 10)";
			}
			else
			{
				//category - off, state - on
				sql2 = "SELECT * FROM users_total AS ut WHERE ut.uid IN (SELECT u.uid FROM users AS u WHERE u.state = '" + state + "') ORDER BY ut.total LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.uid IN (SELECT u.uid FROM users u WHERE u.state = '" + state + "') AND upt.pid IN (SELECT pt.pid FROM products_total AS pt ORDER BY pt.total LIMIT 10)";
			}
		}
		else
		{
			if (state == null || state.equals("all") || state.equals("null"))
			{
				//category - on, state - off
				sql2 = "SELECT * FROM users_categories_total ORDER BY total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.pid IN (SELECT p.id FROM products AS p WHERE p.cid = " + category + ") ORDER BY upt.total DESC LIMIT 10";
			}
			else
			{
				//category - on, state - on
				sql2 = "SELECT * FROM users_categories_total AS uct WHERE uct.uid IN (SELECT u.uid FROM users AS u WHERE u.state = '" + state + "') ORDER BY uct.total DESC LIMIT 20";
				sql3 = "SELECT * FROM users_products_total AS upt WHERE upt.uid IN (SELECT u.uid FROM users WHERE u.state = '" + state + "') AND upt.pid in (SELECT p.pid FROM products AS p WHERE p.cid = " + category + ") ORDER BY upt.total DESC LIMIT 20";
			}
		}
	}
	
	//rs1 = products row
	//rs2 = user/state column
	//rs3 = table cells
	rs1 = stmt1.executeQuery(sql1); //1 = pid, 2 = total
	rs2 = stmt2.executeQuery(sql2); //1 = uid OR state, 2 = total
	rs3 = stmt3.executeQuery(sql3);	//1 = uid OR state, 2 = pid, 3 = total
	
{'fraction_non_alphanumeric': 0.13660858631219688, 'fraction_numerical': 0.020298185737380996, 'mean_word_length': 3.509923045767517, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2819760', 'n_tokens_mistral': 4180, 'n_tokens_neox': 3748, 'n_words': 1150}