<commit_before><?php
include('header.php');
include('../inc/functions.php');

if (isset($_REQUEST['submitted'])){

	// establish reporting location, or select all
	/* ----------------------------------------------------------------------------------------------------- */

	$selected_locations = array();
	// default to ALL
	if ( isset($_REQUEST['locations']) && $_REQUEST['locations'] == array("ALL")){
		$location_where = "location = ANY(select location from ref_stats)";
		$selected_locations = $simple_location_array; // from config.php
	}

	elseif ( isset($_REQUEST['locations']) ) {
		
		// prepare SQL clause
		$location_where = "location IN ('".implode("', '",$_REQUEST['locations'])."')";

		// // prepare selected_locations
		foreach($_REQUEST['locations'] as $location){
			if ($location != "NOPE" && $location != "ALL" && $location != "MAIN_CAMPUS"){
				array_push($selected_locations, $location);
			}
		}

		// adjust for combined locations
		if ( in_array("PK_COMB", $_REQUEST['locations'])){
			$location_where = str_replace("'PK_COMB'", "'PK1','PK2'", $location_where);
			array_push($selected_locations, "PK1","PK2");
		}
		if ( in_array("MAIN_CAMPUS", $_REQUEST['locations'])){
			$location_where = str_replace("'MAIN_CAMPUS'", "'PK1','PK2','UGL'", $location_where);
			array_push($selected_locations, "PK1", "PK2", "UGL");
		}
	}

	else {
		$location_where = "location = {$_COOKIE['location']}";
		$selected_locations = array($_COOKIE['location']);
	}	

	// finish cleaning $selected_locations
	$selected_locations = array_unique($selected_locations);

	// get date limitiers
	$date_start = date("Y-m-d", strtotime($_REQUEST['date_start']));
	$date_end = date("Y-m-d", strtotime($_REQUEST['date_end']));


	// All transactions in date range (appropriate for csv export)
	/* ----------------------------------------------------------------------------------------------------- */
	$full_query = "SELECT ref_type, location, user_group, DAYNAME(timestamp) as day_of_week, DATE(timestamp) AS simple_date, timestamp AS ordering_timestamp FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where ORDER BY ordering_timestamp DESC";
	// echo $full_query;
	$full_result = mysqli_query($link, $full_query) or trigger_error(mysqli_error());
	$total_date_range_results = mysqli_num_rows($full_result);
	
	
	// Query for Location Totals
	/* ----------------------------------------------------------------------------------------------------- */
	// create locations array for case calls
	$location_cases = '';
	foreach($selected_locations as $location) {
		$location_cases .= ", COUNT(CASE WHEN location = '$location' THEN DATE(timestamp) END) AS $location";
	}
	$locations_total_query = "SELECT DATE(timestamp) AS date_string $location_cases FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where GROUP BY DATE(timestamp) ORDER BY date_string DESC";
	// echo $locations_total_query;
	$locations_total_result = mysqli_query($link, $locations_total_query) or trigger_error(mysqli_error());

	// creat arrays for each location
	$locations_total_sorted = array();
	foreach($selected_locations as $location) {
		if (!array_key_exists($location, $locations_total_sorted)) {
			$locations_total_sorted[$location] = array();
		}
	}

	// loop through rows
	while ($row = mysqli_fetch_assoc($locations_total_result)) {	
		foreach($selected_locations as $location) {
			array_push($locations_total_sorted[$location], array( $row['date_string'], $row[$location] ) );
		}
		
	}
	


	// Transaction counts
	/* ----------------------------------------------------------------------------------------------------- */
	$type_query = "SELECT ref_type, COUNT(ref_type) AS ref_type_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where GROUP BY ref_type";
	// echo $type_query;
	$type_result = mysqli_query($link, $type_query) or trigger_error(mysqli_error());
	$type_counts = array();
	while($row = mysqli_fetch_assoc($type_result)) {		
		$type_counts[$ref_type_hash[$row['ref_type']]] = $row['ref_type_count'];
	}


	// Busiest Day-of-the-week (dow)
	/* ----------------------------------------------------------------------------------------------------- */
	$dow_query = "SELECT DAYNAME(timestamp) AS dow_name, DAYOFWEEK(timestamp) AS dow_index, count(DAYOFWEEK(timestamp)) AS dow_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where GROUP BY dow_index ORDER BY dow_index;";
	$dow_result = mysqli_query($link, $dow_query) or trigger_error(mysqli_error());
	$dow_counts = array();
	while($row = mysqli_fetch_assoc($dow_result)) {		
		$dow_counts[$row['dow_name']] = $row['dow_count'];
	}


	// Busiest Hours
	/* ----------------------------------------------------------------------------------------------------- */
	$hour_query = "SELECT HOUR(timestamp) AS hour, COUNT(CASE WHEN ref_type = 1 THEN ref_type END) AS Directional, COUNT(CASE WHEN ref_type = 2 THEN ref_type END) AS Brief, COUNT(CASE WHEN ref_type = 3 THEN ref_type END) AS Extended, COUNT(CASE WHEN ref_type = 4 THEN ref_type END) AS Consultation FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where GROUP BY hour;";
	$hour_result = mysqli_query($link, $hour_query) or trigger_error(mysqli_error());
	$hour_counts = array();
	while($row = mysqli_fetch_assoc($hour_result)) {		
		$hour_counts[$row['hour']] = array(
			"Directional" => $row['Directional'],
			"Brief" => $row['Brief'],
			"Extended" => $row['Extended'],
			"Consultation" => $row['Consultation'],
		);
	}


	// Busiest Single Days
	/* ----------------------------------------------------------------------------------------------------- */
	$single_query = "SELECT DAYNAME(timestamp) as dow_name, DATE(timestamp) AS date, count(ref_type) AS ref_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' AND $location_where GROUP BY date ORDER BY ref_count DESC limit 5;";
	$single_result = mysqli_query($link, $single_query) or trigger_error(mysqli_error());

	/* Data Explanations:
	Recommended to use json_encode() for each array to use with charts.js

	$full_result = MySQL result set for all queries
	$total_date_range_results = Total transactions from all locations.

	$locations_total_result_sorted = array of arrays, with daily counts from locations

	$location_totals = array of locations, each an array full of timestamp.  Can be used to create graph.

	$trans_result = MySQL result set for counts of transaction types
	$type_counts = An associative array from ALL locations - key is type as string, value is count from database

	$dow_counts = Associative array with Day-of-the-Week (dow) names and total transactions counts for that day

	$hour_counts = Nested Associative array with hours of the day, and numbers for "Directional", "Brief", "Extended", and "Consultation"

	$busiest_day_counts 
	*/

	// set display flags
	$export_display = "block";
	$quickstats_display = "block";

}


?><commit_msg>tweaking MySQL queries<commit_after><?php
include('header.php');
include('../inc/functions.php');

if (isset($_REQUEST['submitted'])){

	// establish reporting location, or select all (note: contains 'AND' from actual query to avoid altogether)
	/* ----------------------------------------------------------------------------------------------------- */

	$selected_locations = array();
	// default to ALL
	if ( isset($_REQUEST['locations']) && $_REQUEST['locations'] == array("ALL")){
		// $location_where = "AND location = ANY(select location from ref_stats)";
		$location_where = "";
		$selected_locations = $simple_location_array; // from config.php
	}

	elseif ( isset($_REQUEST['locations']) ) {
		
		// prepare SQL clause
		$location_where = "AND location IN ('".implode("', '",$_REQUEST['locations'])."')";

		// // prepare selected_locations
		foreach($_REQUEST['locations'] as $location){
			if ($location != "NOPE" && $location != "ALL" && $location != "MAIN_CAMPUS"){
				array_push($selected_locations, $location);
			}
		}

		// adjust for combined locations
		if ( in_array("PK_COMB", $_REQUEST['locations'])){
			$location_where = str_replace("'PK_COMB'", "'PK1','PK2'", $location_where);
			array_push($selected_locations, "PK1","PK2");
		}
		if ( in_array("MAIN_CAMPUS", $_REQUEST['locations'])){
			$location_where = str_replace("'MAIN_CAMPUS'", "'PK1','PK2','UGL'", $location_where);
			array_push($selected_locations, "PK1", "PK2", "UGL");
		}
	}

	else {
		$location_where = "AND location = {$_COOKIE['location']}";
		$selected_locations = array($_COOKIE['location']);
	}	

	// finish cleaning $selected_locations
	$selected_locations = array_unique($selected_locations);

	// get date limitiers
	$date_start = date("Y-m-d", strtotime($_REQUEST['date_start']));
	$date_end = date("Y-m-d", strtotime($_REQUEST['date_end']));


	// All transactions in date range (appropriate for csv export)
	/* ----------------------------------------------------------------------------------------------------- */
	$full_query = "SELECT ref_type, location, user_group, DAYNAME(timestamp) as day_of_week, DATE(timestamp) AS simple_date, timestamp AS ordering_timestamp FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where ORDER BY ordering_timestamp DESC";
	// echo $full_query;
	$full_result = mysqli_query($link, $full_query) or trigger_error(mysqli_error());
	$total_date_range_results = mysqli_num_rows($full_result);
	
	
	// Query for Location Totals
	/* ----------------------------------------------------------------------------------------------------- */
	// create locations array for case calls
	$location_cases = '';
	foreach($selected_locations as $location) {
		$location_cases .= ", COUNT(CASE WHEN location = '$location' THEN DATE(timestamp) END) AS $location";
	}
	$locations_total_query = "SELECT DATE(timestamp) AS date_string $location_cases FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where GROUP BY DATE(timestamp) ORDER BY date_string DESC";
	// echo $locations_total_query;
	$locations_total_result = mysqli_query($link, $locations_total_query) or trigger_error(mysqli_error());

	// creat arrays for each location
	$locations_total_sorted = array();
	foreach($selected_locations as $location) {
		if (!array_key_exists($location, $locations_total_sorted)) {
			$locations_total_sorted[$location] = array();
		}
	}

	// loop through rows
	while ($row = mysqli_fetch_assoc($locations_total_result)) {	
		foreach($selected_locations as $location) {
			array_push($locations_total_sorted[$location], array( $row['date_string'], $row[$location] ) );
		}
		
	}
	


	// Transaction counts
	/* ----------------------------------------------------------------------------------------------------- */
	$type_query = "SELECT ref_type, COUNT(ref_type) AS ref_type_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where GROUP BY ref_type";
	// echo $type_query;
	$type_result = mysqli_query($link, $type_query) or trigger_error(mysqli_error());
	$type_counts = array();
	while($row = mysqli_fetch_assoc($type_result)) {		
		$type_counts[$ref_type_hash[$row['ref_type']]] = $row['ref_type_count'];
	}


	// Busiest Day-of-the-week (dow)
	/* ----------------------------------------------------------------------------------------------------- */
	$dow_query = "SELECT DAYNAME(timestamp) AS dow_name, DAYOFWEEK(timestamp) AS dow_index, count(DAYOFWEEK(timestamp)) AS dow_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where GROUP BY dow_index ORDER BY dow_index;";
	$dow_result = mysqli_query($link, $dow_query) or trigger_error(mysqli_error());
	$dow_counts = array();
	while($row = mysqli_fetch_assoc($dow_result)) {		
		$dow_counts[$row['dow_name']] = $row['dow_count'];
	}


	// Busiest Hours
	/* ----------------------------------------------------------------------------------------------------- */
	$hour_query = "SELECT HOUR(timestamp) AS hour, COUNT(CASE WHEN ref_type = 1 THEN ref_type END) AS Directional, COUNT(CASE WHEN ref_type = 2 THEN ref_type END) AS Brief, COUNT(CASE WHEN ref_type = 3 THEN ref_type END) AS Extended, COUNT(CASE WHEN ref_type = 4 THEN ref_type END) AS Consultation FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where GROUP BY hour;";
	$hour_result = mysqli_query($link, $hour_query) or trigger_error(mysqli_error());
	$hour_counts = array();
	while($row = mysqli_fetch_assoc($hour_result)) {		
		$hour_counts[$row['hour']] = array(
			"Directional" => $row['Directional'],
			"Brief" => $row['Brief'],
			"Extended" => $row['Extended'],
			"Consultation" => $row['Consultation'],
		);
	}


	// Busiest Single Days
	/* ----------------------------------------------------------------------------------------------------- */
	$single_query = "SELECT DAYNAME(timestamp) as dow_name, DATE(timestamp) AS date, count(ref_type) AS ref_count FROM ref_stats WHERE DATE(timestamp) >= '$date_start' AND DATE(timestamp) <= '$date_end' $location_where GROUP BY date ORDER BY ref_count DESC limit 5;";
	$single_result = mysqli_query($link, $single_query) or trigger_error(mysqli_error());

	/* Data Explanations:
	Recommended to use json_encode() for each array to use with charts.js

	$full_result = MySQL result set for all queries
	$total_date_range_results = Total transactions from all locations.

	$locations_total_result_sorted = array of arrays, with daily counts from locations

	$location_totals = array of locations, each an array full of timestamp.  Can be used to create graph.

	$trans_result = MySQL result set for counts of transaction types
	$type_counts = An associative array from ALL locations - key is type as string, value is count from database

	$dow_counts = Associative array with Day-of-the-Week (dow) names and total transactions counts for that day

	$hour_counts = Nested Associative array with hours of the day, and numbers for "Directional", "Brief", "Extended", and "Consultation"

	$busiest_day_counts 
	*/

	// set display flags
	$export_display = "block";
	$quickstats_display = "block";

}


?>
{'fraction_non_alphanumeric': 0.22773033395820316, 'fraction_numerical': 0.0018051794764979518, 'mean_word_length': 3.2129277566539924, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 25, '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': '2307331', 'n_tokens_mistral': 4567, 'n_tokens_neox': 4166, 'n_words': 1069}