<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
	
	* @Description:
	* 1) Connect to DB
	* 2) Prepare statements
	* OUTER LOOP:
	*		3) Query SC-API (all orgs)
	*		INNER LOOP:
	*			4) Sub-query Org (more data)
	*			5) Bind data to statement
	*			6) Execute Database Transaction
	* 7) Close Statements
	* 8) Recluster Tables
	* 9) rebuild growth
	* 10) Close connection
	*/
	mb_internal_encoding("UTF-8");
	
	//Obviously this is not safe for user input,
	//but the html data comes from RSI via sc-api,
	//so it 'should' be safe if RSI purified it.
	//Long term we should purify it ourselves and allow whitelisted URLs.
	$AllowedTags = '<a><br><p><h2><h3><h4><h5><span>';
	$LotsOfNewlines = "/\n\n\n+/";
	$ChopLongDashes = "/(-|—){10,}/";
	
	function getOrgSize(&$SID, &$connection){
//THIS IS A POSSIBLE SECURITY VULNERABILITY (SQL injection)
//but the input is from the sc-api, not from a regular user
		$rows = $connection->query("SELECT Size, Main, GrowthRate FROM tbl_Organizations WHERE SID = '$SID'");
		$row = $rows->fetch_assoc();
		//IN THE FUTURE, USE DATABASE CONSTRAINTS INSTEAD OF COMPARING THINGS!!!!!!
		if($row === null){
			echo "NOT FOUND Org SID = $SID\n";
			$result = ['Size' => 0];
			return $result;
		}
		if($row['Main'] === null){
			echo "Main is null\n";
			$result = ['Size' => 0];
			return $result;
		}
		if($row['Main'] > $row['Size']){
			echo "Data integrity error (Main > Size) for $SID; fixing\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$GrowthRate = $row['GrowthRate'];
		
		$rows = $connection->query("SELECT * FROM tbl_OrgDescription WHERE SID = '$SID'");
		$row = $rows->fetch_assoc();
		if($row === null){
			echo "NO DESCRIPTION Org SID = $SID\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$rows = $connection->query("SELECT Size, Main, Affiliate, Hidden FROM tbl_OrgMemberHistory WHERE Organization = '$SID' ORDER BY ScrapeDate DESC LIMIT 1");
		
		if($rows == null){
			echo "Error: existing org not in history; treating it as new\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$row = $rows->fetch_assoc();
		$result = [
			'Size'      => $row['Size'],
			'Main'      => $row['Main'],
			'Affiliate' => $row['Affiliate'],
			'Hidden'    => $row['Hidden'],
			'GrowthRate'=> $GrowthRate
		];
		return $result;
	}
	
	function attemptInsert(&$SID, $Value, &$statement, &$connection){
		if( !$statement->execute() ){
			echo "Error Inserting for SID: $SID debug: $Value\n";
			echo $connection->error . "\n";
		}
	}
	
	function queryAPI(&$queryString){
		for($failCounter = 0; $failCounter < 4; ++$failCounter){
			$lines = file_get_contents($queryString);
			if(!$lines){
				sleep(1);
				continue;//try again
			}
			
			$dataArray = json_decode($lines, true);//json to php associated array
			if($dataArray == false){
				echo "failed to decode\n";
				return -1;
			}
			unset($lines);
			
			if($dataArray["data"] == null){
				echo "Query returned null; failCounter == $failCounter\n";
				continue;//try again; we might be done
			}
			break;
		}
		
		if($failCounter >= 4)return -1;
		return $dataArray;
	}
	
	//1) Connect to DB
	if( sizeof($argv) < 3){
		echo "Correct usage: php " . $argv[0] . " <db username> <db password> <optional: full (counts members and their types)>\n";
		exit();
	}
	
	if( sizeof($argv) >= 4 && $argv[3] == 'full')$getFullMemberInfo = true;
	else $getFullMemberInfo = false;
	
	$connection = new mysqli("localhost",$argv[1],$argv[2], "cognitiondb");
	if( mysqli_connect_errno() ){
		die( "Connection failed: " . mysqli_connect_error() );
	}
	if( !$connection->set_charset("utf8") )echo "Error changing connection character set\n";
	
	$connection->autocommit(FALSE);
	
	//2) Prepare statements
	$prepared_insert_org  = $connection->prepare("INSERT INTO tbl_Organizations (SID, Name, Size, Main, CustomIcon, GrowthRate) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE Name = ?, Size = ?, Main = ?, CustomIcon = ?, GrowthRate = ?");
	$prepared_update_org  = $connection->prepare("UPDATE tbl_Organizations SET Size = ?, Main = ? WHERE SID = ?");
	$prepared_insert_org ->bind_param("ssddddsdddd", $SID, $Name, $Size, $Main, $CustomIcon, $GrowthRate, $Name, $Size, $Main, $CustomIcon, $GrowthRate);
	$prepared_update_org ->bind_param("dds", $Size, $Main, $SID);
	
	$prepared_insert_date  = $connection->prepare("INSERT INTO tbl_OrgMemberHistory (Organization, ScrapeDate, Size, Main, Affiliate, Hidden) VALUES (?, CURDATE(), ?, ?, ?, ?) ON DUPLICATE KEY UPDATE ScrapeDate = CURDATE(), Size = ?, Main = ?, Affiliate = ?, Hidden = ?");
	$prepared_insert_date ->bind_param("sdddddddd", $SID, $Size, $Main, $Affiliate, $Hidden, $Size, $Main, $Affiliate, $Hidden);
	
	$prepared_insert_icon = $connection->prepare("INSERT INTO tbl_IconURLs(Organization, Icon) VALUES (?, ?)");
	$prepared_insert_icon->bind_param("ss", $SID, $IconURL);
	
	$prepared_insert_commits = $connection->prepare("INSERT INTO tbl_Commits(Organization, Commitment) VALUES (?, ?) ON DUPLICATE KEY UPDATE Commitment = ?");
	$prepared_insert_commits->bind_param("sss", $SID, $Commitment, $Commitment);
	
	$prepared_insert_full = $connection->prepare("INSERT INTO tbl_FullOrgs(Organization) VALUES (?) ON DUPLICATE KEY UPDATE Organization = ?");
	$prepared_delete_full = $connection->prepare("DELETE from tbl_FullOrgs WHERE Organization = ?");
	$prepared_insert_full->bind_param("ss", $SID, $SID);
	$prepared_delete_full->bind_param("s", $SID);
	
	$prepared_insert_primary   = $connection->prepare("INSERT INTO tbl_PrimaryFocus  (PrimaryFocus,   Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE PrimaryFocus = ?");
	$prepared_insert_secondary = $connection->prepare("INSERT INTO tbl_SecondaryFocus(SecondaryFocus, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE SecondaryFocus = ?");
	$prepared_insert_performs  = $connection->prepare("INSERT INTO tbl_Performs(PrimaryFocus, SecondaryFocus, Organization) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE PrimaryFocus = ?, SecondaryFocus = ?");
	$prepared_insert_primary  ->bind_param("sss",  $PrimaryFocus, $SID, $PrimaryFocus);
	$prepared_insert_secondary->bind_param("sss",  $SecondaryFocus, $SID, $SecondaryFocus);
	$prepared_insert_performs ->bind_param("sssss", $PrimaryFocus, $SecondaryFocus, $SID, $PrimaryFocus, $SecondaryFocus);
	
	$prepared_insert_archetype  = $connection->prepare("INSERT INTO tbl_OrgArchetypes(Organization, Archetype) VALUES (?, ?) ON DUPLICATE KEY UPDATE Archetype = ?");
	$prepared_insert_filterarch = $connection->prepare("INSERT INTO tbl_FilterArchetypes(Archetype, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE Archetype = ?");
	$prepared_insert_archetype  ->bind_param("sss", $SID, $Archetype, $Archetype);
	$prepared_insert_filterarch ->bind_param("sss", $Archetype, $SID, $Archetype);
	
	$prepared_insert_roleplay = $connection->prepare("INSERT INTO tbl_RolePlayOrgs(Organization) VALUES (?) ON DUPLICATE KEY UPDATE Organization = ?");
	$prepared_delete_roleplay = $connection->prepare("DELETE from tbl_RolePlayOrgs WHERE Organization = ?");
	$prepared_insert_roleplay->bind_param("ss", $SID, $SID);
	$prepared_delete_roleplay->bind_param("s", $SID);
	
	$prepared_insert_language = $connection->prepare("INSERT INTO tbl_OrgFluencies(Organization, Language) VALUES (?, ?) ON DUPLICATE KEY UPDATE Language = ?");
	$prepared_insert_filterlang = $connection->prepare("INSERT INTO tbl_FilterFluencies(Language, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE Language = ?");
	$prepared_insert_language->bind_param("sss", $SID, $Language, $Language);
	$prepared_insert_filterlang->bind_param("sss", $Language, $SID, $Language);
	
	$prepared_insert_description = $connection->prepare("INSERT INTO tbl_OrgDescription(SID, Headline, Manifesto) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE Headline = ?, Manifesto = ?");
	$prepared_insert_description->bind_param("sssss", $SID, $Headline, $Manifesto, $Headline, $Manifesto);
	
	$numberInserted = 0;
	$numberUpdated  = 0;
	
	for($x = 1;; $x = $x + 8){//$x is current page number in query string
		//3) Query SC-API (all orgs)
		//the +3 means query eight pages at a time
		$queryString  = "http://sc-api.com/?api_source=live&system=organizations&action=all_organizations&source=rsi&start_page=$x";
		$queryString .="&end_page=" . ($x+7) . "&items_per_page=1&sort_method=&sort_direction=ascending&expedite=0&format=raw";
		$dataArray = queryAPI($queryString);
		unset($queryString);
		if($dataArray == -1)break;
		
		//4) Sub-query Org (more data)
		foreach ($dataArray["data"] as $org){
			//5a) Bind data from outer query
			$SID         = strtoupper( $org['sid'] );
			$Size        = intval( $org['member_count'] );
			
			$savedSize = getOrgSize($SID, $connection);
			
			//if "full inserting" or if org is new/updating, we need to get main/affiliate/hidden
			if($getFullMemberInfo || $savedSize["Size"] != $Size){
				//get member info<commit_msg>lowered number of orgs scraped per query to reduce failure rate<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
	
	* @Description:
	* 1) Connect to DB
	* 2) Prepare statements
	* OUTER LOOP:
	*		3) Query SC-API (all orgs)
	*		INNER LOOP:
	*			4) Sub-query Org (more data)
	*			5) Bind data to statement
	*			6) Execute Database Transaction
	* 7) Close Statements
	* 8) Recluster Tables
	* 9) rebuild growth
	* 10) Close connection
	*/
	mb_internal_encoding("UTF-8");
	
	//Obviously this is not safe for user input,
	//but the html data comes from RSI via sc-api,
	//so it 'should' be safe if RSI purified it.
	//Long term we should purify it ourselves and allow whitelisted URLs.
	$AllowedTags = '<a><br><p><h2><h3><h4><h5><span>';
	$LotsOfNewlines = "/\n\n\n+/";
	$ChopLongDashes = "/(-|—){10,}/";
	
	function getOrgSize(&$SID, &$connection){
//THIS IS A POSSIBLE SECURITY VULNERABILITY (SQL injection)
//but the input is from the sc-api, not from a regular user
		$rows = $connection->query("SELECT Size, Main, GrowthRate FROM tbl_Organizations WHERE SID = '$SID'");
		$row = $rows->fetch_assoc();
		//IN THE FUTURE, USE DATABASE CONSTRAINTS INSTEAD OF COMPARING THINGS!!!!!!
		if($row === null){
			echo "NOT FOUND Org SID = $SID\n";
			$result = ['Size' => 0];
			return $result;
		}
		if($row['Main'] === null){
			echo "Main is null\n";
			$result = ['Size' => 0];
			return $result;
		}
		if($row['Main'] > $row['Size']){
			echo "Data integrity error (Main > Size) for $SID; fixing\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$GrowthRate = $row['GrowthRate'];
		
		$rows = $connection->query("SELECT * FROM tbl_OrgDescription WHERE SID = '$SID'");
		$row = $rows->fetch_assoc();
		if($row === null){
			echo "NO DESCRIPTION Org SID = $SID\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$rows = $connection->query("SELECT Size, Main, Affiliate, Hidden FROM tbl_OrgMemberHistory WHERE Organization = '$SID' ORDER BY ScrapeDate DESC LIMIT 1");
		
		if($rows == null){
			echo "Error: existing org not in history; treating it as new\n";
			$result = ['Size' => 0];
			return $result;
		}
		
		$row = $rows->fetch_assoc();
		$result = [
			'Size'      => $row['Size'],
			'Main'      => $row['Main'],
			'Affiliate' => $row['Affiliate'],
			'Hidden'    => $row['Hidden'],
			'GrowthRate'=> $GrowthRate
		];
		return $result;
	}
	
	function attemptInsert(&$SID, $Value, &$statement, &$connection){
		if( !$statement->execute() ){
			echo "Error Inserting for SID: $SID debug: $Value\n";
			echo $connection->error . "\n";
		}
	}
	
	function queryAPI(&$queryString){
		for($failCounter = 0; $failCounter < 4; ++$failCounter){
			$lines = file_get_contents($queryString);
			if(!$lines){
				sleep(4);
				continue;//try again
			}
			
			$dataArray = json_decode($lines, true);//json to php associated array
			if($dataArray == false){
				echo "failed to decode\n";
				return -1;
			}
			unset($lines);
			
			if($dataArray["data"] == null){
				echo "Query returned null; failCounter == $failCounter\n";
				continue;//try again; we might be done
			}
			break;
		}
		
		if($failCounter >= 4)return -1;
		return $dataArray;
	}
	
	//1) Connect to DB
	if( sizeof($argv) < 3){
		echo "Correct usage: php " . $argv[0] . " <db username> <db password> <optional: full (counts members and their types)>\n";
		exit();
	}
	
	if( sizeof($argv) >= 4 && $argv[3] == 'full')$getFullMemberInfo = true;
	else $getFullMemberInfo = false;
	
	$connection = new mysqli("localhost",$argv[1],$argv[2], "cognitiondb");
	if( mysqli_connect_errno() ){
		die( "Connection failed: " . mysqli_connect_error() );
	}
	if( !$connection->set_charset("utf8") )echo "Error changing connection character set\n";
	
	$connection->autocommit(FALSE);
	
	//2) Prepare statements
	$prepared_insert_org  = $connection->prepare("INSERT INTO tbl_Organizations (SID, Name, Size, Main, CustomIcon, GrowthRate) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE Name = ?, Size = ?, Main = ?, CustomIcon = ?, GrowthRate = ?");
	$prepared_update_org  = $connection->prepare("UPDATE tbl_Organizations SET Size = ?, Main = ? WHERE SID = ?");
	$prepared_insert_org ->bind_param("ssddddsdddd", $SID, $Name, $Size, $Main, $CustomIcon, $GrowthRate, $Name, $Size, $Main, $CustomIcon, $GrowthRate);
	$prepared_update_org ->bind_param("dds", $Size, $Main, $SID);
	
	$prepared_insert_date  = $connection->prepare("INSERT INTO tbl_OrgMemberHistory (Organization, ScrapeDate, Size, Main, Affiliate, Hidden) VALUES (?, CURDATE(), ?, ?, ?, ?) ON DUPLICATE KEY UPDATE ScrapeDate = CURDATE(), Size = ?, Main = ?, Affiliate = ?, Hidden = ?");
	$prepared_insert_date ->bind_param("sdddddddd", $SID, $Size, $Main, $Affiliate, $Hidden, $Size, $Main, $Affiliate, $Hidden);
	
	$prepared_insert_icon = $connection->prepare("INSERT INTO tbl_IconURLs(Organization, Icon) VALUES (?, ?)");
	$prepared_insert_icon->bind_param("ss", $SID, $IconURL);
	
	$prepared_insert_commits = $connection->prepare("INSERT INTO tbl_Commits(Organization, Commitment) VALUES (?, ?) ON DUPLICATE KEY UPDATE Commitment = ?");
	$prepared_insert_commits->bind_param("sss", $SID, $Commitment, $Commitment);
	
	$prepared_insert_full = $connection->prepare("INSERT INTO tbl_FullOrgs(Organization) VALUES (?) ON DUPLICATE KEY UPDATE Organization = ?");
	$prepared_delete_full = $connection->prepare("DELETE from tbl_FullOrgs WHERE Organization = ?");
	$prepared_insert_full->bind_param("ss", $SID, $SID);
	$prepared_delete_full->bind_param("s", $SID);
	
	$prepared_insert_primary   = $connection->prepare("INSERT INTO tbl_PrimaryFocus  (PrimaryFocus,   Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE PrimaryFocus = ?");
	$prepared_insert_secondary = $connection->prepare("INSERT INTO tbl_SecondaryFocus(SecondaryFocus, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE SecondaryFocus = ?");
	$prepared_insert_performs  = $connection->prepare("INSERT INTO tbl_Performs(PrimaryFocus, SecondaryFocus, Organization) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE PrimaryFocus = ?, SecondaryFocus = ?");
	$prepared_insert_primary  ->bind_param("sss",  $PrimaryFocus, $SID, $PrimaryFocus);
	$prepared_insert_secondary->bind_param("sss",  $SecondaryFocus, $SID, $SecondaryFocus);
	$prepared_insert_performs ->bind_param("sssss", $PrimaryFocus, $SecondaryFocus, $SID, $PrimaryFocus, $SecondaryFocus);
	
	$prepared_insert_archetype  = $connection->prepare("INSERT INTO tbl_OrgArchetypes(Organization, Archetype) VALUES (?, ?) ON DUPLICATE KEY UPDATE Archetype = ?");
	$prepared_insert_filterarch = $connection->prepare("INSERT INTO tbl_FilterArchetypes(Archetype, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE Archetype = ?");
	$prepared_insert_archetype  ->bind_param("sss", $SID, $Archetype, $Archetype);
	$prepared_insert_filterarch ->bind_param("sss", $Archetype, $SID, $Archetype);
	
	$prepared_insert_roleplay = $connection->prepare("INSERT INTO tbl_RolePlayOrgs(Organization) VALUES (?) ON DUPLICATE KEY UPDATE Organization = ?");
	$prepared_delete_roleplay = $connection->prepare("DELETE from tbl_RolePlayOrgs WHERE Organization = ?");
	$prepared_insert_roleplay->bind_param("ss", $SID, $SID);
	$prepared_delete_roleplay->bind_param("s", $SID);
	
	$prepared_insert_language = $connection->prepare("INSERT INTO tbl_OrgFluencies(Organization, Language) VALUES (?, ?) ON DUPLICATE KEY UPDATE Language = ?");
	$prepared_insert_filterlang = $connection->prepare("INSERT INTO tbl_FilterFluencies(Language, Organization) VALUES (?, ?) ON DUPLICATE KEY UPDATE Language = ?");
	$prepared_insert_language->bind_param("sss", $SID, $Language, $Language);
	$prepared_insert_filterlang->bind_param("sss", $Language, $SID, $Language);
	
	$prepared_insert_description = $connection->prepare("INSERT INTO tbl_OrgDescription(SID, Headline, Manifesto) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE Headline = ?, Manifesto = ?");
	$prepared_insert_description->bind_param("sssss", $SID, $Headline, $Manifesto, $Headline, $Manifesto);
	
	$numberInserted = 0;
	$numberUpdated  = 0;
	
	for($x = 1;; $x = $x + 4){//$x is current page number in query string
		//3) Query SC-API (all orgs)
		//the +3 means query four pages at a time
		$queryString  = "http://sc-api.com/?api_source=live&system=organizations&action=all_organizations&source=rsi&start_page=$x";
		$queryString .="&end_page=" . ($x+3) . "&items_per_page=1&sort_method=&sort_direction=ascending&expedite=0&format=raw";
		$dataArray = queryAPI($queryString);
		unset($queryString);
		if($dataArray == -1)break;
		
		//4) Sub-query Org (more data)
		foreach ($dataArray["data"] as $org){
			//5a) Bind data from outer query
			$SID         = strtoupper( $org['sid'] );
			$Size        = intval( $org['member_count'] );
			
			$savedSize = getOrgSize($SID, $connection);
			
			//if "full inserting" or if org is new/updating, we need to get main/affiliate/hidden
			if($getFullMemberInfo || $savedSize["Size"] != $Size){
				//get member info
{'fraction_non_alphanumeric': 0.16233730572793673, 'fraction_numerical': 0.010214728980174639, 'mean_word_length': 4.8647342995169085, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 141, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, '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': '2813906', 'n_tokens_mistral': 7030, 'n_tokens_neox': 6093, 'n_words': 1507}