<commit_before><?php
	/*
	@license magnet:?xt=urn:btih:0b31508aeb0634b347b8270c7bee4d411b5d4109&dn=agpl-3.0.txt
	
	Copyright (C) 2016 SabineWren
	
	GNU AFFERO GENERAL PUBLIC LICENSE Version 3, 19 November 2007
	https://www.gnu.org/licenses/agpl-3.0.html
	
	@license-end
CAVEATS:
Activity (front end) == Focus (database)
Members  (front end) == Size  (database)
	*/
	
	mb_internal_encoding("UTF-8");
	
	$connection = new mysqli("192.168.0.105","publicselect","public", "cognitiondb");
	if( mysqli_connect_errno() ){
		die( "Connection failed: " . mysqli_connect_error() );
	}
	
	if( !$connection->set_charset("utf8") )echo "Error changing connection character set\n";
	
	//get parameters from query string
	$pageNum = (int)$_GET['pagenum'];
	if($pageNum < 0){
		exit("invalid page offset"); 
	}
	$pageSize = 12;
	$offset = $pageNum * $pageSize;
	
	//init
	$parameters = array();
	$types = '';
	$conjunction = ' WHERE (';
	
	$sql = "
SELECT orgs.SID as SID, orgs.Name as Name, orgs.Size as Size, orgs.Main as Main, orgs.GrowthRate as GrowthRate, orgs.CustomIcon as CustomIcon, orgs.URL as URL,
Performs.PrimaryFocus as PrimaryFocus, Performs.SecondaryFocus as SecondaryFocus, Commitment, Language, Archetype,
	CASE
		WHEN Roleplay.Organization IS NOT NULL then 'Yes'
		ELSE 'No'
		END AS Roleplay,
	CASE
		WHEN FullOrgs.Organization IS NOT NULL then 'No'
		WHEN ExclOrgs.Organization IS NOT NULL then 'Excl.'
		ELSE 'Yes'
		END AS Recruiting
FROM (
	SELECT SID, Name, Size, Main, GrowthRate, CustomIcon, URL
	FROM tbl_Organizations";

	//we use a bound param so guarantee at least one param in our statement (otherwise the function call breaks)
	$sql .= " LIMIT $pageSize OFFSET ?";
	array_push($parameters, $offset);
	$types .= 'd';

$sql .=  ") as orgs
LEFT JOIN tbl_Performs       Performs  ON orgs.SID = Performs.Organization
     JOIN tbl_Commits        Commits   ON orgs.SID = Commits.Organization
LEFT JOIN tbl_OrgFluencies   Language  ON orgs.SID = Language.Organization
LEFT JOIN tbl_OrgArchetypes  Archetype ON orgs.SID = Archetype.Organization
LEFT JOIN tbl_RolePlayOrgs   Roleplay  ON orgs.SID = Roleplay.Organization
LEFT JOIN tbl_FullOrgs       FullOrgs  ON orgs.SID = FullOrgs.Organization
LEFT JOIN tbl_ExclusiveOrgs  ExclOrgs  ON orgs.SID = ExclOrgs.Organization
	";
	
	function addParamsToQuery($Attribute, $Values, &$types, &$sql, &$conjunction, &$parameters){
		foreach($Values as $Value){
			if($Value == '')continue;//We might have 0 Params to add
			$sql .= $conjunction . $Attribute . ' = ?';
			array_push($parameters, $Value);
			$types .= 's';
			$conjunction = ' OR ';
		}
		if($conjunction === ' OR ')$conjunction = ' AND (';
	}
	
	$Values = explode( ',', $_GET['Commitment'] );
	addParamsToQuery('Commitment', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$Values = explode( ',', $_GET['Recruiting'] );
	addParamsToQuery('Recruiting', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$Values = explode( ',', $_GET['Roleplay'] );
	addParamsToQuery('Roleplay', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$lang = $_GET['Lang'];
	if($lang !== "Any"){
		$sql .= "$conjunction SID in (SELECT Organization FROM tbl_FilterFluencies WHERE Language = ?))";
		$conjunction = ' AND (';
		$types .= 's';
		array_push($parameters, $lang);
	}
	unset($lang);
	
	if(isset($_GET['Min'])){
		$min = (int)$_GET['Min'];
		$sql .= "$conjunction Size >= ?)";
		$conjunction = ' AND(';
		$types .= 'd';
		array_push($parameters, $min);
		unset($min);
	}
	
	if(isset($_GET['Max'])){
		$max = (int)$_GET['Max'];
		$sql .= "$conjunction Size <= ?)";
		$conjunction = ' AND(';
		$types .= 'd';
		array_push($parameters, $max);
		unset($max);
	}
	
	//if org in Cognition Corp
	if((int)$_GET['Cog']){
		$sql .= "$conjunction SID IN (SELECT SID FROM tbl_RepresentsCog))";
		$conjunction = ' AND(';
	}
	
	//WHERE SID LIKE Value and subselect using Name
	$Values = explode( ',', $_GET['NameOrSID'] );
	if( strlen($Values[0]) > 0 ){
		$Value = '%' . rawurldecode( $Values[0]) . '%';//mysql->real_escape_string and html_entity_decode do not decode %20 (space)
		$temp = $Value . "\n" . $Values[0];
		$sql .= $conjunction . "SID LIKE UPPER(?) OR SID IN (
			SELECT SID FROM tbl_Organizations WHERE Name LIKE UPPER(?)
		))";
		array_push($parameters, $Value);
		array_push($parameters, $Value);
		$types .= 'ss';
		$conjunction = ' AND (';
	}
	unset($Values);
	
	//subselect to filter using Activity
	$Activities = explode( ',', $_GET['Activity'] );
	if(strlen($Activities[0]) > 0){		
		$sql .= $conjunction;
			//add filter and join for primary focus (activity1)
			$sql .= 'SID IN (SELECT Organization FROM tbl_PrimaryFocus';
			$conjunction = ' WHERE ';
			addParamsToQuery('PrimaryFocus', $Activities, $types, $sql, $conjunction, $parameters);
		
			//add filter and join for secondary focus (activity2)
			$sql .= ') OR SID IN (SELECT Organization from tbl_SecondaryFocus';
			$conjunction = ' WHERE ';
			addParamsToQuery('SecondaryFocus', $Activities, $types, $sql, $conjunction, $parameters);
		$sql .= ')) ';
	}
	unset($Activities);
	
	//subselect to filter using Archetype
	$Archetypes = explode( ',', $_GET['Archetype'] );
	if(strlen($Archetypes[0]) > 0){
		$sql .= $conjunction;
		
		//add filter and join for primary focus (activity1)
		$sql .= 'SID IN (
			SELECT Organization FROM tbl_FilterArchetypes';
			$conjunction = ' WHERE ';
			addParamsToQuery('Archetype', $Archetypes, $types, $sql, $conjunction, $parameters);
		$sql .= '))';
	}
	unset($Archetypes);
	
	//apply sorting
	if( isset($_GET['nameDir']) ){
		$nameDir = $_GET['nameDir'];
		//for some reason 'Name' has to be in quotes for DESC to work, but ASC works without!?!?
		if($nameDir == 'down')    $sql .= " ORDER BY Name DESC";
		else if($nameDir == 'up') $sql .= " ORDER BY Name ASC";
		unset($nameDir);
	}
	else if( isset($_GET['sizeDir']) ){
		$sizeDir = $_GET['sizeDir'];
		if($sizeDir == 'down')    $sql .= " ORDER BY Size DESC";
		else if($sizeDir == 'up') $sql .= " ORDER BY Size ASC";
		unset($sizeDir);
	}
	else if( isset($_GET['mainDir']) ){
		$mainDir = $_GET['mainDir'];
		if($mainDir == 'down')    $sql .= " ORDER BY Main DESC";
		else if($mainDir == 'up') $sql .= " ORDER BY Main ASC";
		unset($mainDir);
	}
	
	//require references to array elements to bind
	$bindParams = array();
	foreach($parameters as $key => $param){
		$bindParams[$key] = &$parameters[$key];
	}
	
	//amalgamate the types and values for callback function argument
	//we do not need references to the first element<commit_msg>moved all the query params into the new subselect (filter before limit)<commit_after><?php
	/*
	@license magnet:?xt=urn:btih:0b31508aeb0634b347b8270c7bee4d411b5d4109&dn=agpl-3.0.txt
	
	Copyright (C) 2016 SabineWren
	
	GNU AFFERO GENERAL PUBLIC LICENSE Version 3, 19 November 2007
	https://www.gnu.org/licenses/agpl-3.0.html
	
	@license-end
CAVEATS:
Activity (front end) == Focus (database)
Members  (front end) == Size  (database)
	*/
	
	mb_internal_encoding("UTF-8");
	
	$connection = new mysqli("192.168.0.105","publicselect","public", "cognitiondb");
	if( mysqli_connect_errno() ){
		die( "Connection failed: " . mysqli_connect_error() );
	}
	
	if( !$connection->set_charset("utf8") )echo "Error changing connection character set\n";
	
	//get parameters from query string
	$pageNum = (int)$_GET['pagenum'];
	if($pageNum < 0){
		exit("invalid page offset"); 
	}
	$pageSize = 12;
	$offset = $pageNum * $pageSize;
	
	function addParamsToQuery($Attribute, $Values, &$types, &$sql, &$conjunction, &$parameters){
		foreach($Values as $Value){
			if($Value == '')continue;//We might have 0 Params to add
			$sql .= $conjunction . $Attribute . ' = ?';
			array_push($parameters, $Value);
			$types .= 's';
			$conjunction = ' OR ';
		}
		if($conjunction === ' OR ')$conjunction = ' AND (';
	}
	
	//init
	$parameters = array();
	$types = '';
	$conjunction = ' WHERE (';
	
	$sql = "
SELECT orgs.SID as SID, orgs.Name as Name, orgs.Size as Size, orgs.Main as Main, orgs.GrowthRate as GrowthRate, orgs.CustomIcon as CustomIcon, orgs.URL as URL,
Performs.PrimaryFocus as PrimaryFocus, Performs.SecondaryFocus as SecondaryFocus, Commitment, Language, Archetype,
	CASE
		WHEN Roleplay.Organization IS NOT NULL then 'Yes'
		ELSE 'No'
		END AS Roleplay,
	CASE
		WHEN FullOrgs.Organization IS NOT NULL then 'No'
		WHEN ExclOrgs.Organization IS NOT NULL then 'Excl.'
		ELSE 'Yes'
		END AS Recruiting
FROM (
	SELECT SID, Name, Size, Main, GrowthRate, CustomIcon, URL
	FROM tbl_Organizations";
	
	$Values = explode( ',', $_GET['Commitment'] );
	addParamsToQuery('Commitment', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$Values = explode( ',', $_GET['Recruiting'] );
	addParamsToQuery('Recruiting', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$Values = explode( ',', $_GET['Roleplay'] );
	addParamsToQuery('Roleplay', $Values, $types, $sql, $conjunction, $parameters);
	if(strlen($Values[0]) > 0)$sql .= ')';
	unset($Values);
	
	$lang = $_GET['Lang'];
	if($lang !== "Any"){
		$sql .= "$conjunction SID in (SELECT Organization FROM tbl_FilterFluencies WHERE Language = ?))";
		$conjunction = ' AND (';
		$types .= 's';
		array_push($parameters, $lang);
	}
	unset($lang);
	
	if(isset($_GET['Min'])){
		$min = (int)$_GET['Min'];
		$sql .= "$conjunction Size >= ?)";
		$conjunction = ' AND(';
		$types .= 'd';
		array_push($parameters, $min);
		unset($min);
	}
	
	if(isset($_GET['Max'])){
		$max = (int)$_GET['Max'];
		$sql .= "$conjunction Size <= ?)";
		$conjunction = ' AND(';
		$types .= 'd';
		array_push($parameters, $max);
		unset($max);
	}
	
	//if org in Cognition Corp
	if((int)$_GET['Cog']){
		$sql .= "$conjunction SID IN (SELECT SID FROM tbl_RepresentsCog))";
		$conjunction = ' AND(';
	}
	
	//WHERE SID LIKE Value and subselect using Name
	$Values = explode( ',', $_GET['NameOrSID'] );
	if( strlen($Values[0]) > 0 ){
		$Value = '%' . rawurldecode( $Values[0]) . '%';//mysql->real_escape_string and html_entity_decode do not decode %20 (space)
		$temp = $Value . "\n" . $Values[0];
		$sql .= $conjunction . "SID LIKE UPPER(?) OR SID IN (
			SELECT SID FROM tbl_Organizations WHERE Name LIKE UPPER(?)
		))";
		array_push($parameters, $Value);
		array_push($parameters, $Value);
		$types .= 'ss';
		$conjunction = ' AND (';
	}
	unset($Values);
	
	//subselect to filter using Activity
	$Activities = explode( ',', $_GET['Activity'] );
	if(strlen($Activities[0]) > 0){		
		$sql .= $conjunction;
			//add filter and join for primary focus (activity1)
			$sql .= 'SID IN (SELECT Organization FROM tbl_PrimaryFocus';
			$conjunction = ' WHERE ';
			addParamsToQuery('PrimaryFocus', $Activities, $types, $sql, $conjunction, $parameters);
		
			//add filter and join for secondary focus (activity2)
			$sql .= ') OR SID IN (SELECT Organization from tbl_SecondaryFocus';
			$conjunction = ' WHERE ';
			addParamsToQuery('SecondaryFocus', $Activities, $types, $sql, $conjunction, $parameters);
		$sql .= ')) ';
	}
	unset($Activities);
	
	//subselect to filter using Archetype
	$Archetypes = explode( ',', $_GET['Archetype'] );
	if(strlen($Archetypes[0]) > 0){
		$sql .= $conjunction;
		
		//add filter and join for primary focus (activity1)
		$sql .= 'SID IN (
			SELECT Organization FROM tbl_FilterArchetypes';
			$conjunction = ' WHERE ';
			addParamsToQuery('Archetype', $Archetypes, $types, $sql, $conjunction, $parameters);
		$sql .= '))';
	}
	unset($Archetypes);
	
	//apply sorting
	if( isset($_GET['nameDir']) ){
		$nameDir = $_GET['nameDir'];
		//for some reason 'Name' has to be in quotes for DESC to work, but ASC works without!?!?
		if($nameDir == 'down')    $sql .= " ORDER BY Name DESC";
		else if($nameDir == 'up') $sql .= " ORDER BY Name ASC";
		unset($nameDir);
	}
	else if( isset($_GET['sizeDir']) ){
		$sizeDir = $_GET['sizeDir'];
		if($sizeDir == 'down')    $sql .= " ORDER BY Size DESC";
		else if($sizeDir == 'up') $sql .= " ORDER BY Size ASC";
		unset($sizeDir);
	}
	else if( isset($_GET['mainDir']) ){
		$mainDir = $_GET['mainDir'];
		if($mainDir == 'down')    $sql .= " ORDER BY Main DESC";
		else if($mainDir == 'up') $sql .= " ORDER BY Main ASC";
		unset($mainDir);
	}

	//we use a bound param so guarantee at least one param in our statement (otherwise the function call breaks)
	$sql .= " LIMIT $pageSize OFFSET ?";
	array_push($parameters, $offset);
	$types .= 'd';

$sql .=  ") as orgs
LEFT JOIN tbl_Performs       Performs  ON orgs.SID = Performs.Organization
     JOIN tbl_Commits        Commits   ON orgs.SID = Commits.Organization
LEFT JOIN tbl_OrgFluencies   Language  ON orgs.SID = Language.Organization
LEFT JOIN tbl_OrgArchetypes  Archetype ON orgs.SID = Archetype.Organization
LEFT JOIN tbl_RolePlayOrgs   Roleplay  ON orgs.SID = Roleplay.Organization
LEFT JOIN tbl_FullOrgs       FullOrgs  ON orgs.SID = FullOrgs.Organization
LEFT JOIN tbl_ExclusiveOrgs  ExclOrgs  ON orgs.SID = ExclOrgs.Organization
	";
	
	//require references to array elements to bind
	$bindParams = array();
	foreach($parameters as $key => $param){
		$bindParams[$key] = &$parameters[$key];
	}
	
	//amalgamate the types and values for callback function argument
	//we do not need references to the first element
{'fraction_non_alphanumeric': 0.16984789740530867, 'fraction_numerical': 0.011482254697286013, 'mean_word_length': 4.178764478764479, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 23, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 2, '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': '4397037', 'n_tokens_mistral': 5172, 'n_tokens_neox': 4612, 'n_words': 1254}