<?php
/*********************************************************************************************************
  "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")

  Copyright (c) 2005-10 The Regents of the University of California, Davis campus. All Rights Reserved.

  Redistribution and use in source and binary forms, with or without modification, are permitted by
  nonprofit, research institutions for research use only, provided the conditions in the included
  license agreement are met.

  Refer to the file "ensemble_license.txt" for the license agreement,
  located in the top level directory of this distribution.

**********************************************************************************************************/
//Uploads the meta-data from an itunes mp3 playlist to the ensemble stimulus table
//includes the path information of the mp3 files so that they can be copied directly to
//the server and then queried by the QPI php scripts for presentation with the flash player.

//Original version downloaded from the web
//Original version by Richard James Kendall 
//iTunes XML Playlist Reader v1.01

//Modified by Stefan Tomic for Ensemble
//11/17/2009 - S.T. Removed include for conn_researcher.php and added code to prompt for researcher username and password

require_once('../../include/variables.php');

////////////////////////////////////////////////

//VARIABLES THAT NEED TO BE CHANGED FOR EACH RUN


//the playlist files exported as an XML file
//if multiple files, separate filename strings by commas
//to be passed to the array function. Otherwise, just pass one
//string to the array function
$xml_files = array("/home/bmk/downloaded_00s_itunes_essentials.xml");

//the path to your extracted audio files
//make sure the folders under this path are copies of what was originally directly under your Audio Hijack directory
//make sure to place a forward slash at the end of dir name
//it won't need a backslash, however to escape spaces
$sfpath = "/home/bmk/Audio Hijack/";


//error log file - change this for each run to keep a history of errors
$error_fname = "/home/bmk/00_itunes_essentials_import_errors.txt";

//The database you wish to connect to (e.g. 'ensemble_tarp' or 'ensemble_main')
$database_experiment_admin = 'ensemble_main';

////////////////////////////////////////////////

//VARIABLES THAT WILL LIKELY NOT NEED TO BE CHANGED

//the name of the relative directory where audio files were ripped
//only need the highest directory level unless the dir name is repeated
//in the absolute path (make sure to add a slash at the end)
$location_tag_root= "Audio Hijack/";

//destination of mp3 files and log files
$new_location_root = "audio/itunes_music"; // "audio/itunes_music"
$dest_path = $stimuli_dir.$new_location_root;
$log_filename = $questionnaire_dir."stimuli/stim_upload/stim_upload_errors/".$error_fname;
/////////////////////////////////////////////

//Establishing database connection

print "ESTABLISHING DATABASE CONNECTION\n";
print "Hostname of database server ";
$hostname_experiment_admin = getUserInput($MYSQL_HOSTNAME);
print "Database ";
$database_experiment_admin = getUserInput($DATABASE_NAME);
print "Researcher User ";
$username_experiment_admin = getUserInput();
print "Researcher Password ";
$password_experiment_admin = getUserInput();
$experiment_admin = mysql_connect($hostname_experiment_admin, $username_experiment_admin, $password_experiment_admin) or trigger_error(mysql_error(),E_USER_ERROR); 

mysql_select_db($database_experiment_admin, $experiment_admin);

/////////////////////////////////////////////

$FORMAT_AS_HTML = FALSE;

/*  iTunes XML Playlist Reader v1.01
    By Richard James Kendall 
    Bugs to richard@richardjameskendall.com 
    Free to use, please acknowledge me 
    
    You need to change the $file variable to point to the right XML file.
   	
   	To get iTunes to generate this XML file select the playlist you want and goto
   	File > Export Song List and select the XML format.  This DOES NOT WORK on full
   	library exports from iTunes but it may do in the future.
   	
   	It loads the data into an array structure as follows:
   	
   	Array {
   		[0] => Array {
   				[TRACK ID] =>
   				[NAME] =>
   				[ARTIST] => (may not exist)
   				[ALBUM] => (may not exist)
   				[GENRE] => (may not exist)
   				[KIND] =>
   				[SIZE] =>
   				[TOTAL TIME] =>
   				[BIT RATE] =>
   				[SAMPLE RATE] =>
   				[PLAY COUNT] =>
   			}
   		
   		[1] => Array {
   				.
   				.
   				.
   			}
   		
   		...
   	}
   	
   	I have provided code to format it as HTML (very simple formatting) and a function
   	to get the track length in mm:ss instead of milliseconds.
	
	S.T. 1/31/05 Added code to perform SQL queries
	
	
*/

$fid_log = fopen($log_filename,"w");
fwrite($fid_log,sprintf("ARTIST,NAME,PLAYLIST\t\tREASON FAILED\n"));

$num_updated_records = 0;
foreach($xml_files as $file) {

$tracks = array();
$track_counter = -1;
$action = "";
$k = "";
$content = "";
$mode = "GETTRACKINFO";
$play_order = array();
$playlist = array();


$xml_parser = xml_parser_create();
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
if (!($fp = fopen($file, "r"))) {
	die("could not open XML input");
}

while ($data = fread($fp, 4096)) {
	if (!xml_parse($xml_parser, $data, feof($fp))) {
		die(sprintf("XML error: %s at line %d",
					xml_error_string(xml_get_error_code($xml_parser)),
					xml_get_current_line_number($xml_parser)));
	}
}
xml_parser_free($xml_parser);


//insert values into database

usort($tracks, "track_cmp");
$tr_num = 0;

mysql_select_db($database_experiment_admin, $experiment_admin);

while($tr_num != count($tracks)) {

  $location_field = GetSQLValueString($tracks[$tr_num]["LOCATION"],"file");


  $vals = "";

  $vals = $vals."(".
    GetSQLValueString($tracks[$tr_num]["NAME"],"text").",".
    GetSQLValueString("iTunes Music Fragment","text").",".
    GetSQLValueString($playlist["NAME"],"text").",".
    GetSQLValueString($tracks[$tr_num]["ARTIST"],"text").",".
    GetSQLValueString($tracks[$tr_num]["ALBUM"],"text").",".
    GetSQLValueString($tracks[$tr_num]["GENRE"],"text").",".
    GetSQLValueString("mp3","text").",".
    GetSQLValueString($tracks[$tr_num]["SIZE"],"int").",".
    GetSQLValueString($tracks[$tr_num]["TOTAL TIME"],"time").",".
    GetSQLValueString($tracks[$tr_num]["YEAR"],"int").",".
    GetSQLValueString($tracks[$tr_num]["BIT RATE"],"int").",".
    GetSQLValueString($tracks[$tr_num]["SAMPLE RATE"],"int").",".
    $location_field."),";
  

  //get rid of the trailing comma
  $vals = substr($vals,0,strlen($vals)-1);
  
  //do the sql
  $sql = "INSERT IGNORE INTO stimulus (name,description,playlist,artist,album,genre,file_format,size,duration,year,compression_bit_rate,sample_rate,location) VALUES ".$vals.";";

  mysql_select_db($database_experiment_admin,$experiment_admin);
  mysql_query($sql) or die(mysql_error());
  
  $insert_worked = mysql_affected_rows();
  $success = TRUE;
  if($insert_worked) {
  
    //remove the new root location from the location string
    //so that source and destination strings can be built for copying
    //also remove quotes inserted into the string for SQL
    $relative_path_loc = substr($location_field,strlen($new_location_root)+1);
    $relative_path_loc = substr($relative_path_loc,0,strlen($relative_path_loc)-1);
    

    //copy the mp3 from directory
    $artist_folder = strtok($relative_path_loc,'/');
    $album_folder = strtok('/');
    $song = strtok('/');
    
    $dir_exists = is_dir($dest_path.$artist_folder);
    if(!$dir_exists) {
      $dir_exists = mkdir($dest_path.$artist_folder);
      if(!$dir_exists) {
	$sucess = FALSE;
	$reason = "couldn't make artist directory";
      }
    }
    if($dir_exists && !is_dir($dest_path.$artist_folder."/".$album_folder)) {
      $dir_exists = mkdir($dest_path.$artist_folder."/".$album_folder);
      if(!$dir_exists) {
	$success = FALSE;
	$reason = "couldn't make album directory";
      }
    }
    if($dir_exists) {
      $success = copy($sfpath.$relative_path_loc,$dest_path.$relative_path_loc);
      if(!$success)
	$reason = "couldn't copy the audio file";
    }
  }
  else {
    $success = FALSE;
    $reason = "couldn't insert record to table (duplicate entry?)";
  }
  
  //if file copy didn't work, but a new record was inserted
  //delete that record
  if(!$success && $insert_worked) {
    $new_id = mysql_insert_id();
    //remove the record from the database and output the unsuccessful record to a log file
    $sql = sprintf("delete from stimulus where stimulus_id = %d",$new_id);
    mysql_select_db($database_experiment_admin,$experiment_admin);
    mysql_query($sql) or die(mysql_error());

  }

  if(!$success)
    fwrite($fid_log,sprintf("%s,%s,%s\t\t%s\n",$tracks[$tr_num]["ARTIST"],$tracks[$tr_num]["NAME"],$playlist["NAME"],$reason));
  else
    $num_updated_records++;
  
  $tr_num++;
}


if($FORMAT_AS_HTML) {
  // simple HTML formatting

  print ("<html><head><title>iTunes XML Playlist Reader</title></head>\n");
  print ("<body>\n");
  print ("<h1>" . $playlist["NAME"] . "</h1>\n");
  print ("<table width=\"100%\" cellspacing=\"0\" cellpadding=\"1\" border=\"1\">\n");
  print ("<tr>\n");
  print ("<td align=\"center\"><b>Name</b></td>\n");
  print ("<td align=\"center\"><b>Album</b></td>\n");
  print ("<td align=\"center\"><b>Artist</b></td>\n");
  print ("<td align=\"center\"><b>Play Count</b></td>\n");
  print ("<td align=\"center\"><b>Track Length</b></td>\n");
  print ("<td align=\"center\"><b>File Size</b></td>\n");
  print ("</tr>\n");
  for($i = 0;$i < count($tracks);$i++) {
    print ("<tr>\n");
    print ("<td>" . $tracks[$i]["NAME"] . "</td>");
    if (isset($tracks[$i]["ALBUM"])) {
      print ("<td>" . $tracks[$i]["ALBUM"] . "</td>");
    } else {
      print ("<td>~</td>");
    }
    if (isset($tracks[$i]["ARTIST"])) {
      print ("<td>" . $tracks[$i]["ARTIST"] . "</td>");
    } else {
      print ("<td>~</td>");
    }
    print ("<td>" . $tracks[$i]["PLAY COUNT"] . "</td>");
    print ("<td>" . secs2minsasecs($tracks[$i]["TOTAL TIME"]) . "</td>");
    print ("<td>" . $tracks[$i]["SIZE"] . " bytes</td>");
    print ("</tr>\n");
  }
  print ("</table>\n");
  print ("</body>\n</html>\n");
}


} //foreach $xmlfile as file

fwrite($fid_log,sprintf("\n\nNumber of uploaded tunes: %d",$num_updated_records));
fclose($fid_log);


//FUNCTIONS


//function returns SQL formatted time string from milliseconds value
function ms2SqlTime($theValue) {
	//convert from milliseconds to seconds
	$theValue = $theValue / 1000;

	$hrs = (int) ($theValue / 3600);
	$min = (int) (($theValue - $hrs*3600) /60);
	$sec = (int) ($theValue - $min*60);
	
	return (sprintf("%d:%d:%d",$hrs,$min,$sec));

}


function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{

  global $location_tag_root;
  global $new_location_root;

  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  $theValue = urldecode($theValue);

  switch ($theType) {

    case "file": //if $theType == "file", replace the hostname in addition to regular text processing
      $theValue = utf8_decode($theValue);
      $theValue = preg_replace("|'|","\'",$theValue); //escape the apostrophes
      //find the index into $theValue of the relative path string
      $relative_loc_idx = strpos($theValue,$location_tag_root)+strlen($location_tag_root);
      $theValue = $new_location_root.substr($theValue,$relative_loc_idx);
      $theValue = preg_replace("|'|","\'",$theValue); //escape the apostrophes
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
   
      break;
    case "text":
      $theValue = utf8_decode($theValue);
      $theValue = preg_replace("|'|","\'",$theValue); //escape the apostrophes
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "time":
      $theValue = ($theValue != "") ? "'" . ms2SqlTime($theValue) . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}


function startElement($parser, $name, $attrs) {
	global $action, $k;
   	switch($name) {
   		case "KEY":
   			$k = "";
   			$action = "GETKEY";
   			break;
   		case "STRING":
   		case "INTEGER":
   		case "DATE":
   			$action = "CONTENT";
   			break;
   		default:
   			$action = "";
   			break;
	}
}

function endElement($parser, $name) {
   	global $action, $k, $content, $track_counter, $mode, $tracks, $play_order, $playlist;
   	switch(strtoupper($k)) {
   		case "TRACK ID":
   			if ($mode == "GETTRACKINFO" && $action == "CONTENT") {
   				//print ($action . " - " . $k . " - " . $content . " - " . $mode . "<br>");
   				$track_counter++;
   				$tracks[$track_counter][strtoupper($k)] = $content;
   			}
   			if ($mode == "PLAYORDER" && $action == "CONTENT") {
   				$play_order[$content] = count($play_order);
   				//print ($content . "<br>");
   			}
   			break;
   		case "PLAYLISTS":
   			$mode = "PLAYORDER";
   			break;
   		case "NAME":
   		case "ARTIST":
   		case "ALBUM":
   		case "GENRE":
   		case "KIND":
   		case "SIZE":
   		case "TOTAL TIME":
		case "STOP TIME":
		case "TRACK NUMBER":
		case "TRACK COUNT":
		case "YEAR":
   		case "BIT RATE":
   		case "SAMPLE RATE":
		case "LOCATION":
   			if ($mode == "GETTRACKINFO") {
   				$tracks[$track_counter][strtoupper($k)] = $content;
   			} else {
   				$playlist[strtoupper($k)] = $content;
   			}
   			break;
   	}
   	if ($action != "GETKEY") {
   		$k = "";
   	}
   	$content = "";
   	$action = "";
}

function characterData($parser, $data) {
	global $action, $k, $content;
	if ($action != "") {
		switch($action) {
			case "GETKEY":
				$k .= $data;
				break;
			case "CONTENT":
				$content .= $data;
				//print ($k . " => " . $content . "<br>");
				break;
		}
	}
}

// this is the comparison utility funtion used by usort to order the tracks in play count order.
function track_cmp($a, $b) {
	global $play_order;
	if ($play_order[$a["TRACK ID"]] > $play_order[$b["TRACK ID"]]) {
		return 1;
	} else {
		if ($play_order[$a["TRACK ID"]] == $play_order[$b["TRACK ID"]]) {
			return 0;
		} else {
			return -1;
		}
	}
}

// turns a real number into an integer
function getint($number) {
	if (strpos($number, ".")) {
		$bits = explode(".", $number);
		return $bits[0];
	} else {
		return $number;
	}
}

// formats seconds as mins:secs
function secs2minsasecs($secs) {
	$secs /= 1000;
	$minutes = $secs / 60;
	$seconds = $secs % 60;
	return getint($minutes) . ":" . $seconds;
}


?>