/src/php/subsets/read.php

<?php
/* Copyright 2012 Aditi Muralidharan. See the file "LICENSE" for the full license governing this code. */

/**  read.php
	Called by subsets.js in service of all the main .php pages.
	Returns the contents of subsets
	And lists all the subsets made by a user
**/


/** Returns the contents of the collection with the given ID
@param {Number} subsetID The ID of the collection to list.
@return {Array} The contents of the subject, an associative array with the
following fields:
	- date: The creation date of the collection
	- text: The name of the collection
	- type: The type of the collection
	- id: The id of the collection
	- words: If this is a collection of type 'word', a space-separated list of
	the words in this collection
	- ids: If this is not a collection of words, an array of arrays listing the
	item ID's in the collection. The list has the format [{id: <id>}, ...].
*/
function listSubsetContents($subsetID){
	$contents = array();
	$sql = "SELECT * from working_set WHERE id = $subsetID;";
	$result = mysql_query($sql);
	$row = mysql_fetch_assoc($result);
	$type = $row['type'];
	$name = $row['name'];
	$date = $row['date'];

	$contents['text'] = $name;
	$contents['id'] = $subsetID;
	$contents['date'] = $date;
	$contents['type'] = $type;

	if($type == 'word'){// Read contents of a word set
		$sql = "SELECT DISTINCT word
		FROM working_set_contents, word
		WHERE working_set_id = $subsetID
		AND item_table_name = 'word'
		AND word.id = item_id;";
		$result = mysql_query($sql) or die("
				read.php l.23<br>
				<b>A fatal MySQL error occured</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$words = '';
		while($row = mysql_fetch_assoc($result)){
			$words = $words.' '.$row['word'];
		}
		$contents['words'] = $words;
	}
	else {
	    $sql = "SELECT DISTINCT `item_id`
	    FROM `working_set_contents` 
	    WHERE `working_set_id` = $subsetID 
	    AND `item_table_name`='".mysql_real_escape_string($type)."'";
	    $result = mysql_query($sql) or die("
				read.php l.37<br/>
				<b>A fatal MySQL error occured</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$unitIDs = array();
		while($row = mysql_fetch_assoc($result)){
			array_push($unitIDs, array("id"=>$row['item_id']));
		}
		$contents['ids'] = $unitIDs;
	}
	return  $contents;	
}

function getAllChildIDs($id) {
	$ids = array();
	$sql = "SELECT id from working_set where parent = $id;";
	$result = mysql_query($sql);
	while ($row = mysql_fetch_assoc($result)) {
		array_push($ids, $row['id']);
		$grandchildren = getAllChildIDs($row['id']);
		foreach ($grandchildren as $grandchild) {
			array_push($ids, $grandchild);
		}
	}
	return $ids;
}

function getAllParentIDs($id) {
	$ids = array();
	$sql = "SELECT parent from working_set where id = $id;";
	$result = mysql_query($sql);
	while ($row = mysql_fetch_assoc($result)) {
		array_push($ids, $row['parent']);
		$grandchildren = getAllParentIDs($row['parent']);
		foreach ($grandchildren as $grandchild) {
			array_push($ids, $grandchild);
		}
	}
	return $ids;
}

function addHighlightContents($subsetID){
	$contents = array();
	$sql = "SELECT highlight.document_id, start_index, end_index, end, start, full, title, date, item_id from working_set_contents, highlight, document, author_xref_document as axn, author
			WHERE item_table_name = 'highlight'
			AND highlight.id = item_id
			AND axn.author_id = author.id
			AND axn.document_id = document.id
			AND highlight.document_id = document.id
			AND working_set_id = ".$subsetID.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	while($row = mysql_fetch_array($result)){
		//item metadata
		$highlightInfo = array();
		$highlightInfo['id'] = $row['item_id'];
		$highlightInfo['text'] = getHighlightText($row['item_id'],  	
			$row['start'],
			$row['start_index'], 
			$row['end'],
			$row['end_index']);
		$highlightInfo['type'] = "highlight";
		// item notes and tags
		$highlightInfo['notes'] = getHighlightNotes($row['item_id']);
		$highlightInfo['tags'] = getHighlightTags($row['item_id']);
		// document metadata
		$highlightInfo['title'] = $row['title'];
		$highlightInfo['date'] = $row['date'];
		$highlightInfo['author'] = $row['full'];
		$highlightInfo['document_id'] = $row['document_id'];
		array_push($contents, $highlightInfo);
	}
	return $contents;
}

function getHighlightText($highlightID, $start, $startIndex, $end, $endIndex){
	$sql = "SELECT surface FROM sentence_xref_word
	WHERE (sentence_id > ".$start." AND sentence_id < ".$end.")
	OR (sentence_id = ".$start." AND position >= ".$startIndex.")
	OR (sentence_id = ".$end." AND position <= ".$endIndex.")
	ORDER BY sentence_id asc, position asc;";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	$text = "";
	while($row = mysql_fetch_array($result)){
		if(!strstr(".,?!@#$%^&*:()_llre-+t='\"`s", $row['surface']) && !(substr($row['surface'], 0, 1) === "'") && !strstr($row['surface'], "'")){
			$text = $text.' '; // don't put spaces in front of punctuation
		}
		$text = $text.replaceWeirdCharacters($row['surface']);
	}
	return $text;
}

function addDocumentContents($subsetID){
	$contents = array();
	$sql = "SELECT full, title, date, item_id 
		FROM working_set_contents, 
			document, 
			author_xref_document as axn, 
			author
		WHERE item_table_name = 'document'
		AND document.id = item_id
		AND axn.document_id = document.id
		AND axn.author_id = author.id
		AND working_set_id = ".$subsetID.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	while($row = mysql_fetch_array($result)){
		// item info
		$documentInfo['id'] = $row['item_id'];
		$documentInfo['type'] = "document";
		// notes and tags
		$documentInfo['notes'] = getItemNotes($row['item_id'], "document");
		$documentInfo['tags'] = getItemTags($row['item_id'], "document");
		// document metadata
		$documentInfo['title'] = $row['title'];
		$documentInfo['date'] = $row['date'];
		$documentInfo['author'] = $row['full'];
		$documentInfo['document_id'] = $row['item_id'];
		array_push($contents, $documentInfo);
	}
	return $contents;
}

function addSentenceContents($subsetID){
	$contents = array();
	$sql = "SELECT sentence.document_id, sentence, full, title, date, item_id 
			FROM 
				working_set_contents, 
				sentence, 
				document, 
				author_xref_document as axn, 
				author
			WHERE item_table_name = 'sentence'
			AND sentence.id = item_id
			AND sentence.document_id = document.id
			AND axn.document_id = document.id
			AND axn.author_id = author.id
			AND working_set_id = ".$subsetID.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	while($row = mysql_fetch_array($result)){
		//item metadata
		$sentenceInfo = array();
		$sentenceInfo['id'] = $row['item_id'];
		$sentenceInfo['sentence'] = $row['sentence'];
		$sentenceInfo['type'] = "sentence";
		// item notes and tags
		$sentenceInfo['notes'] = getItemNotes($row['item_id'], "sentence");
		$sentenceInfo['tags'] = getItemTags($row['item_id'], "sentence");
		// document metadata
		$sentenceInfo['title'] = $row['title'];
		$sentenceInfo['date'] = $row['date'];
		$sentenceInfo['author'] = $row['full'];
		$sentenceInfo['document_id'] = $row['document_id'];
		array_push($contents, $sentenceInfo);
	}
	return $contents;
}

function addWordContents($subsetID, $contents){
	$contents = array();
	$sql = "SELECT word, pos, item_id 
			FROM 
				working_set_contents, 
				word
			WHERE item_table_name = 'word'
			AND word.id = item_id
			AND working_set_id = ".$subsetID.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	while($row = mysql_fetch_array($result)){
		//item metadata
		$wordInfo = array();
		$wordInfo['id'] = $row['item_id'];
		$wordInfo['word'] = $row['word'];
		$wordInfo['pos'] = $row['pos'];
		$wordInfo['type'] = "word";
		// item notes and tags
		$wordInfo['notes'] = getItemNotes($row['item_id'], "word");
		$wordInfo['tags'] = getItemTags($row['item_id'], "word");
		array_push($contents, $wordInfo);
	}
	return $contents;
}

function getHighlightNotes($id){
	$sql = "SELECT * from highlight_xref_note, note
	 WHERE note.id = note_id
	 AND highlight_id = ".$id.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return extractNotes($result);
}

function getItemNotes($id, $tableName){
	$sql = "SELECT * from item_notes_and_tags, note
	 WHERE annotation_table_name = 'note'
	 AND note.id = annotation_id
	 AND item_table_name = '".$tableName."'
	 AND item_id = ".$id.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return extractNotes($result);
}

function extractNotes($result){
	$notes = array();
	while($row = mysql_fetch_array($result)){
		$notesInfo = array();
		$notesInfo['text'] = $row['text'];
		$notesInfo['user'] = $row['user'];
		$notesInfo['id'] = $row['id'];
		$notesInfo['date'] = $row['date'];
		array_push($notes, $notesInfo);
	}
	return $notes;
}

function getHighlightTags($id){
	$sql = "SELECT * from highlight_xref_tag, tag
	 WHERE tag.id = tag_id
	 AND highlight_id = ".$id.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return extractTags($result);
}

function getItemTags($id, $tableName){
	$sql = "SELECT * from item_notes_and_tags, tag
	 WHERE annotation_table_name = 'tag'
	 AND tag.id = annotation_id
	 AND item_table_name = '".$tableName."'
	 AND item_id = ".$id.";";
	$result = mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return extractTags($result);
}

function extractTags($result){
	$tags = array();
	while($row = mysql_fetch_array($result)){
		$tagsInfo = array();
		$tagsInfo['tag'] = $row['name'];
		$tagsInfo['user'] = $row['user'];
		$tagsInfo['id'] = $row['id'];
		array_push($tags, $tagsInfo);
	}
	return $tags;	
}

function getCollectionID($collectionName){
	$q = "SELECT id from working_set where name = '".mysql_escape_string($collectionName)."';";
	$result = mysql_query($q) or die("<b>A fatal MySQL error occured</b>.
		<br/> Query: " . $q . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	if(mysql_num_rows($result) > 0){
		$row = mysql_fetch_array($result);
		return $row['id'];
	}else{
		return -1;
	}
}

function getDocumentIDsInCollection($collectionID){
	$type = getCollectionType($collectionID);
	$property_name = "";
	if ($type == "word") {
		$property_name = "word_set";
	} else if ($type == "document") {
		$property_name = "document_set";
	} if ($type == "sentence") {
		$property_name = "sentence_set";
	}
	
	$sql = "SELECT distinct document_id 
	FROM metadata
	WHERE property_name = '$property_name'
	AND value = '$collectionID';";
	$result = mysql_query($sql) or die(mysql_error()."
	<br> on query $sql
	<br> at subsets/read.php line 316");
	$document_ids = array();
	while ($row = mysql_fetch_array($result)) {
		array_push($document_ids, $row['document_id']);
	}
	return $document_ids;
}

function getCollectionType($collectionID) {
	$sql = "SELECT * from working_set where id = $collectionID";
	$result = mysql_query($sql) or die ("SQL error 
		<br>".mysql_error()."
		<br> on read.php l. 318
		<br> on query:
		<br> ".$sql);
	$row = mysql_fetch_assoc($result);
	return $row['type'];
}

/** Returns an array of the sentence ID's that are contained in this collection,
both indirectly (through their parent units) and directly (in the case of
sentence collections). Consults the GET parameter document_id to determine
whether the results should be restricted to a particular document.

@return {Array} A list of sentence ID's in this collection.
*/
function getSentenceIDsInCollection($collectionID) {
	$table_identifier = 'filtered_sent_ids';
	$insertion_fields = '(id, document_id)';
	$field_identifier = "DISTINCT unit_id, document_id ";
	$query_id_where = '';
	global $num_filter_conditions;
	global $cache_results;
	global $query_id;
	if ($cache_results || $query_id) {
	    $table_identifier = 'cached_filtered_sent_ids';
	    $insertion_fields = '(id, document_id, query_id)';
	    $field_identifier = "DISTINCT unit_id, document_id, $query_id";
	    $query_id_where = " AND query_id = $query_id ";
	}
	if (!$query_id || $cache_results) {
		$type = getCollectionType($collectionID);
		$property_name = "";
		if ($type == "word") {
			$property_name = "word_set";
		} else if ($type == "document") {
			$property_name = "document_set";
		} if ($type == "sentence") {
			$property_name = "sentence_set";
		}
		$sql = "";
		if ($property_name == "word_set" ||
			$property_name == "sentence_set") {
			$sql = "INSERT IGNORE into $table_identifier $insertion_fields
			SELECT $field_identifier from metadata 
			WHERE property_name = '$property_name'
			AND value = '$collectionID';";			
		} else {
			$field_identifier = str_replace("unit_id", "sentence.id", 
				$field_identifier);
			$field_identifier = str_replace("document_id", 
				"sentence.document_id", 
				$field_identifier);
			$sql = "INSERT IGNORE into $table_identifier $insertion_fields
			SELECT $field_identifier 
			FROM metadata, sentence
			WHERE property_name = 'document_set'
			AND value = '$collectionID'
			AND sentence.document_id = unit_id;";
		}

		$result = mysql_query($sql) or die(mysql_error()."
		<br> on query $sql
		<br> at subsets/read.php line 316");
		
		$num_filter_conditions += 1;
		updateSentenceFilterTable();
 	}
 	if (!cache_results) {
 		$sql = "SELECT * from $table_identifier $query_id_where;";
 		$result = mysql_query($sql);
 		while ($row = mysql_fetch_assoc($result)) {
 			array_push($sentence_ids, $row['id']);
 		}
 	}
	return $sentence_ids;
}

function listCollections($username, $type, $parent){
	$sql = "SELECT id, name, date FROM working_set
			WHERE parent = '$parent'
			AND type = '$type'
			AND username = '$username';";
	$result = mysql_query($sql) or die("
				read.php line 304 <br>
				<b>A fatal MySQL error occured</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	$subsets = array();
	while($row = mysql_fetch_array($result)){
		$subsetInfo = listSubsetContents($row['id']);
		$subsetInfo['children'] = listCollections($username, $type, $row['id']);
		array_push($subsets, $subsetInfo); 
	}
	return $subsets;
}

function listCollectionsFlat($username, $type){
	$sql = 'SELECT id, name, date FROM working_set
			WHERE type = "'.$type.'"
			AND username = "'.$username.'"
			ORDER BY name asc;';
	$result = mysql_query($sql) or die("
				read.php line 304 <br>
				<b>A fatal MySQL error occured</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	$subsets = array();
	if($type == 'document'){
			$subsetInfo = array();
			$subsetInfo['text'] = "all";
			$subsetInfo['date'] = "";
			$subsetInfo['id'] = 0;
			array_push($subsets, $subsetInfo);
	}
	while($row = mysql_fetch_array($result)){
		$subsetInfo = listSubsetContents($row['id']);
		array_push($subsets, $subsetInfo); 
	}
	return $subsets;
}
?>