/src/php/subsets/update.php

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

/*************************************************************************
update.php

Update subsets -- facilities for adding, modifying, and deleting items
*************************************************************************/

/** Adds the given id's to the set with the given ID.

@param {Integer} id The id of the set to which the items should be added.
@param {String} items A space-separated list of integer IDs for the items that
should be added to the set.
*/
function addItemToSubset($id, $items){
	$sql = "SELECT type from working_set where id = $id;";
	$result = mysql_query($sql);
	$row = mysql_fetch_assoc($result);
	$type = $row['type'];
	$units = explode(' ', trim($items));
	foreach ($units as $unit) {
	    $unit_ids = array($unit);
	    if ($type == 'word') {
	        $unit_ids = getWordIDs($unit);
	    }
	    foreach ($unit_ids as $unit_id) {
	        $sql = "INSERT IGNORE INTO working_set_contents
					(working_set_id, item_id, item_table_name)
					VALUES ($id, $unit_id, '$type');";
			$result = mysql_query($sql) or die("update.php l.19<br>
				<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	    	addMetadataForSubset($unit_id, $id, $type);
	    }
	}
	$parents = getAllParentIDs($id);
	foreach ($parents as $parent) {
		if ($parent != 0) {
			addItemToSubset($parent, $items);
		}
	}
	return array("status"=>"ok");
}

/** Adds the sentence metadata records that mark that an item has been added to
a set.
*/
function addMetadataForSubset($item_id, $set_id, $type) {
	$property_name = "";
	if ($type == "sentence") {
		$property_name = "sentence_set";
	} else if ($type == "word") {
		$property_name = "word_set";
	} else if ($type == "document") {
		$property_name = "document_set";
	}
	$property_id = updateMetadataBacklog($set_id, $type, $property_name);
	$sql = "SELECT property_id from metadata_structure 
	where property_name = '$property_name';";
	if ($type == "sentence") {
		$sql = "SELECT document_id from sentence where id = $item_id;";
		$result = mysql_query($sql);
		$row = mysql_fetch_assoc($result);
		$document_id = $row['document_id'];
		$sql = "INSERT IGNORE INTO metadata 
		(unit_id, unit_name, property_name, property_id, value, document_id)
		VALUES ($item_id, 'sentence', '$property_name', $property_id, '$set_id', 
			$document_id);";
		$result = mysql_query($sql) or die("error inserting sentence set metadata
		at update.php l.61<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	} else if ($type == "document") {
		$document_id = $item_id;
		$sql = "INSERT IGNORE INTO metadata
		(unit_id, unit_name, property_name, property_id, value, document_id)
		VALUES
		($document_id, 'document', '$property_name', $property_id, 
			'$set_id', $document_id);";
		$result = mysql_query($sql) or die("error inserting 
			document set metadata
		at update.php l.79<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	} else if ($type == "word") {
		$sql = "INSERT IGNORE INTO metadata
		(unit_id, unit_name, property_name, property_id, value, document_id)
		SELECT
		sentence_id, 'sentence', '$property_name', $property_id, '$set_id', document_id
		FROM sentence_xref_word 
		WHERE word_id = $item_id;";
		$result = mysql_query($sql) or die("error inserting 
			document set metadata
		at update.php l.79<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	}
	// update the main metadata count for this set
	updateMainMetadataCounts($set_id, $property_name);
}

function updateMainMetadataCounts($set_id, $property_name) {
	include_once "../document/get-metadata.php";
	global $main_metadata_counts_table;

	$sql = "DELETE from $main_metadata_counts_table
	WHERE property_name = '$property_name'
	AND (value = '$set_id' or value = 'undefined' or value = '');";
	$result = mysql_query($sql) or die(mysql_error()."<br>
	on query<br>
	$sql
	<br> made on update.php line 115");

	if ($property_name == "document_set") {
		$sql = "INSERT IGNORE INTO $main_metadata_counts_table 
		(property_name, value, count)
		SELECT '$property_name', 
		'$set_id',
		count(*)
		FROM metadata as m,
		sentence_xref_unit as s 
		WHERE
		m.property_name = '$property_name'
		AND m.value = '$set_id'
		AND s.unit_id = m.unit_id;";
		$result = mysql_query($sql) or die(mysql_error()."<br>
		on query<br>
		$sql
		<br> made on update.php line 133");

	} else {
		$sql = "INSERT IGNORE INTO $main_metadata_counts_table 
		(property_name, value, count)
		SELECT '$property_name', 
		'$set_id',
		count(*)
		FROM metadata as m
		WHERE
		m.property_name = '$property_name'
		AND m.value = '$set_id'
		AND m.unit_name = 'sentence';";
		$result = mysql_query($sql) or die(mysql_error()."<br>
		on query<br>
		$sql
		<br> made on update.php line 150");
	}
}

if (strstr($_SERVER['REQUEST_URI'], 'update.php')) {
	include_once "../util.php";
	$wordseer_instance = getGetParam('instance');
	$path = '../../../instances/'.$wordseer_instance.'/config.php';
	include_once $path;

	updateMetadataBacklog("", "", "sentence_set");
	echo "sentence_set backlog updated ";
	
	updateMetadataBacklog("", "", "document_set");
	echo " document_set backlog updated";
	
	updateMetadataBacklog("", "", "word_set");
	echo " word_set backlog updated";
	
}

/** Adds metadatata records for items in the set that may not have been recorded
as metadata, but only if the property doesn't yet exist in the metadata_structure
table.

@return property_id The ID of the metadata property for this set type.
*/
function updateMetadataBacklog($set_id, $type, $property_name) {
	include_once "../document/get-metadata.php";
	global $main_metadata_counts_table;
	$sql = "SELECT property_id from metadata_structure 
	WHERE property_name = '$property_name'";
	$unit_name = 'sentence';
	if ($type == "document") {
		$unit_name = "document";
	}
	$result = mysql_query($sql) or die("error clearing metadata backlog
		at update.php l.61<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	if (mysql_num_rows($result) == 0) {
		// Create the new property
		$sql = "INSERT INTO metadata_structure 
		(property_name, unit_name, value_is_displayed, name_is_displayed, type, is_category)
		VALUES
		('$property_name', '$unit_name', 0, 0, 'string', 1);";
		$result = mysql_query($sql) or die("error clearing metadata backlog
			at update.php l.72<br>
			<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		
		// Get the new property ID
		$sql = "SELECT property_id from metadata_structure 
		WHERE property_name = '$property_name'";
		$result = mysql_query($sql) or die("error clearing metadata backlog
			at update.php l.82<br>
			<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$row = mysql_fetch_assoc($result);
		$property_id = $row['property_id'];

		$sql = "SELECT id, item_table_name as type from working_set;";
		$res = mysql_query($sql);
		while ($r = mysql_fetch_assoc($res)) {
			// Insert the backlog for each working set.
			$set_id = $r['id'];
			$type = $r['type'];
			$sql = "SELECT item_id from working_set_contents 
			WHERE working_set_id = $set_id;";
			$result = mysql_query($sql) or die("error clearing metadata backlog
				at update.php l.90<br>
				<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
			while ($row = mysql_fetch_assoc($result)) {
				addMetadataForSubset($row['item_id'], $set_id, $type);
			}
		}
		return $property_id;

	} else {
		$row = mysql_fetch_assoc($result);
		return $row['property_id'];
	}
}


/** Removes the given id's from the set with the given ID.

@param {Integer} id The id of the set from which the items should be removed.
@param {String} items A space-separated list of integer IDs for the items that
should be removed to the set.
*/
function removeItemFromSubset($id, $items){
	$sql = "SELECT type from working_set where id = $id;";
	$result = mysql_query($sql);
	$row = mysql_fetch_assoc($result);
	$type = $row['type'];
	$units = explode(' ', $items);
	foreach ($units as $unit) {
	    $unit_ids = array($unit);
	    if ($type == 'word') {
	        $unit_ids = getWordIDs($unit);
	    }
	    foreach ($unit_ids as $unit_id) {
	        $sql = "DELETE FROM working_set_contents
					WHERE working_set_id = $id
					AND item_id = $unit_id
					AND item_table_name = '$type';";
				$result = mysql_query($sql) or die("update.php l.19<br>
				<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
				removeMetadataForSubset($unit_id, $id, $type);
	    }
	}
	$parents = getAllParentIDs($id);
	foreach ($parents as $parent) {
		if ($parent != 0) {
			removeItemFromSubset($parent, $items);
		}
	}
	return array("status"=>"ok");
}

/** Removes the sentence metadata records that mark that an item was added to
a set.
*/
function removeMetadataForSubset($item_id, $set_id, $type) {
	$property_name = "";
	if ($type == "sentence") {
		$property_name = "sentence_set";
	} else if ($type == "word") {
		$property_name = "_word_set";
	} else if ($type == "document") {
		$property_name = "document_set";
	}
	$property_id = updateMetadataBacklog($set_id, $type, $property_name);
	$sql = "SELECT property_id from metadata_structure 
	where property_name = '$property_name';";
	if ($type == "sentence" || $type == "document") {
		$sql = "DELETE FROM metadata 
		where unit_id = $item_id
		AND property_name = '$property_name'
		AND value = '$set_id';";
		$result = mysql_query($sql) or die("error inserting sentence set metadata
		at update.php l.61<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	}  else if ($type == "word") {
		$sql = "SELECT
		sentence_id FROM sentence_xref_word 
		WHERE sentence.word_id = $item_id;";
		$result = mysql_query($sql) or die("error inserting 
			document set metadata
		at update.php l.79<br>
		<b>Fatal MySQL error</b>.
		<br/> Query: " . $sql . "
		<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		while ($row = mysql_fetch_assoc($result)) {
			$sql = "DELETE from metadata WHERE unit_id = ".$row['id']."
			AND property_name = '$property_name'
			AND value = '$set_id'";
			$result = mysql_query($sql) or die("error inserting 
				document set metadata
			at update.php l.79<br>
			<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		}
	}
	// update the main metadata count for this set
	updateMainMetadataCounts($set_id, $property_name);
}

function addAnnotationToItem($username, $id, $itemType, $annotation_type, $a){
	$tableName = mysql_escape_string($itemType);
	if($itemType == "document"){
		$tableName = "narrative";
	}
	$documentID = -1;
	if($tableName == "highlight"){
		$sql = "SELECT * from highlight WHERE id = ".$id.";";
		$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		while($row = mysql_fetch_array($result)){
			$documentID = $row['narrative_id'];
		}
	}
	$annotation = mysql_escape_string($a);
	if($annotation_type == "note"){
		$sql = "INSERT INTO note
				(text, date, user)
				VALUES ('".$annotation."', CURDATE(), '".$username."');";
		$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$note_id = mysql_insert_id();
		if($tableName == "highlight"){
			$sql = "INSERT INTO highlight_xref_note
					(highlight_id, note_id, narrative)
					VALUES (".$id.", ".$note_id.", ".$documentID.");";
		}else{
			$sql = "INSERT INTO item_notes_and_tags
					(item_id, item_table_name, annotation_table_name, annotation_id)
					VALUES (".$id.", '".$tableName."','note', ".$note_id.");";
		}
	}else{
		$sql = "INSERT IGNORE INTO tag
				(name)
				VALUES ('".$annotation."');";
		$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$sql = "SELECT id from tag where name = '".$annotation."';";
		$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
		$tag_id = -1;
		while($row = mysql_fetch_array($result)){
			$tag_id = $row['id'];
		}
		if($tableName == "highlight"){
			$sql = "INSERT INTO highlight_xref_tag
					(highlight_id, tag_id, narrative, user)
					VALUES (".$id.", ".$tag_id.", ".$documentID.", '".$username."');";
		}else{
			$sql = "INSERT INTO item_notes_and_tags
					(item_id, item_table_name, annotation_table_name, annotation_id)
					VALUES (".$id.", '".$tableName."','tag', ".$tag_id.");";
		}	
	}
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return array("status"=>"ok");
}

function deleteAnnotation($itemid, $itemtype, $annotationid, $annotationtype){
	$itemID = mysql_escape_string($itemid);
	$itemType = mysql_escape_string($itemtype);
	$itemType = ($itemType == "document")? "narrative":$itemType;
	$annotationID = mysql_escape_string($annotationid);
	$annotationType = mysql_escape_string($annotationtype);
	$highlight_table_name = "highlight_xref_".$annotationType;
	$annotation_id_field = $annotationType."_id";
	//delete the cross-reference
	if($itemType == "highlight"){
		$sql = "DELETE FROM ".$highlight_table_name." 
			WHERE ".$annotation_id_field." = ".$annotationID."
			AND highlight_id = ".$itemID." ;";
	}
	else{
		$sql = "DELETE FROM item_notes_and_tags 
			WHERE annotation_id = ".$annotationID."
			AND annotation_table_name = '".$annotationType."'
			AND item_id = ".$itemID." 
			AND item_table_name = '".$itemType."';";
	}
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());

	// delete the annotation itself, if it's a note;
	if($itemType == "note"){
		$sql = "DELETE from note WHERE id = ".$annotationID.";";
		$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
				<br/> Query: " . $sql . "
				<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	}
	return array("status"=>"ok");
}

function editNote($noteID, $newText){
	$sql = "UPDATE note SET text = '".mysql_escape_string($newText)."'
			WHERE id = ".mysql_escape_string($noteID).";";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return array("status"=>"ok");
}

function renameSubset($id, $newName){
	$sql = "UPDATE working_set SET name = '".mysql_escape_string($newName)."' WHERE id = ".mysql_escape_string($id).";";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return array("status"=>"ok");
}

function moveSubset($id, $newParent){
	$sql = "UPDATE working_set SET parent = '".mysql_escape_string($newParent)."' WHERE id = ".mysql_escape_string($id).";";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	
	$sql = "SELECT type from working_set where id = $id;";
	$result = mysql_query($sql);
	$row = mysql_fetch_assoc($result);
	$type = $row['type'];

	$sql = "SELECT * from working_set_contents = where working_set_id = $id";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			update.php l. 415<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	while ($row = mysql_fetch_assoc($result)) {
		$item = $row['item_id'];
		if ($type == 'word') {
			$item = getWord($item);
		}
		removeItemFromSubset($id, $item);
		addItemToSubset($id, $item);
	}
	return array("status"=>"ok");	
}

function mergeSubsetInto($id, $mergeInto){
	$sql = "UPDATE working_set_contents SET working_set_id =".mysql_escape_string($mergeInto)."
	WHERE working_set_id = ".mysql_escape_string($id).";";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	$sql = "DELETE from working_set WHERE id = ".mysql_escape_string($id).";";
	$result = mysql_query($sql) or die("<b>Fatal MySQL error</b>.
			<br/> Query: " . $sql . "
			<br/> Error: (" . mysql_errno() . ") " . mysql_error());
	return array("status"=>"ok");
}
?>