<?php
$phpgw_info["flags"] = array ("currentapp" => "musiclib",
							 "enable_nextmatchs_class" => True);
							 
include ("../header.inc.php"); #PHP Groupware header

# updateDB.php
#
# Dumps all filenames in table to file, does a dir-list of /mp3 and compares the lists with diffs. 
# Then the table gets updated.
# timesPlayed is not affected by this!

require ("inc/config.inc.php");
include ("inc/functions.inc.php");
include ("inc/sidebar.inc.php");
$title = "Updating Database";
include ("inc/header.inc.php");

global $musiclib;
$to_be_added = array();
$to_be_deleted = array();

$nrOfArtistsBeforeUpdate = count_table(1,$musiclib['tables']['artists']);
$nrOfAlbumsBeforeUpdate  = count_table(1,$musiclib['tables']['albums']);
$nrOfSongsBeforeUpdate   = count_table(1,$musiclib['tables']['songs']);
$nrOfMiscBeforeUpdate 	 = count_table(2,$musiclib['tables']['songs']);

echo "<PRE>";
echo "<h2>* Now updating Albums *</h2><br>";

// get album listing from database
$files_from_db_array = get_list_from_db('album',1,$musiclib['config']['albums_subdir']);

// get list of file formats from $musiclib['config']['file_formats'] in config.inc.php
$grep_string = grep_string($musiclib['config']['file_formats']);

// get file list from filesystem
$files_from_fs_array = get_list_from_fs($musiclib['config']['albums_subdir'],$grep_string);

$to_be_added   = music_to_add($files_from_db_array,$files_from_fs_array);
$to_be_deleted = music_to_del($files_from_db_array,$files_from_fs_array);

echo "<br /><h2>To Be Added: </h2>";
print_r($to_be_added);

echo "<br /><h2>To Be Deleted: </h2>";
print_r($to_be_deleted);

echo "<br /><h2>Processing: </h2>";
foreach($to_be_added as $file) 
{

	$music_data = music_data_file_split($file);
#	print_r($music_data);

    if ($music_data)
    {
    #	echo "Entering insert mode<br>";
    	if ($musiclib['config']['validation'] == '1')
    	{
    		$validation = validate_filename($musiclib['config']['albums_subdir']
    								   ,stripslashes($music_data['artist'])
    								   ,stripslashes($music_data['album'])
    								   ,$music_data['track']
    								   ,stripslashes($music_data['song_title'])
    								   ,$music_data['file_extension']);
    	}
    	
    	if (($validation == true) or $musiclib['config']['validation'] == '2') 
    	{
    			$query = "SELECT id FROM ".$musiclib['tables']['artists']
    					." WHERE artist_name='".$music_data['artist']."'";
    			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
    		
    			$GLOBALS['phpgw']->db->next_record();
    			$artist_id = $GLOBALS['phpgw']->db->f('id');
    		
    			if (!$artist_id) 
    			{
    				echo "Adding artist: ".stripslashes($music_data['artist'])."<br>";
    
    				$query = "INSERT INTO ".$musiclib['tables']['artists']." 
    					  	  (artist_name) VALUES ('"
    					  	 .$music_data['artist']."')";
    				$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
    				$artist_id = $GLOBALS['phpgw']->db->get_last_insert_id($musiclib['tables']['artists'],'id');
    			}
    
    			$query = construct_album_select("WHERE album_title='".$music_data['album']
    										   ."' AND artist_name='".$music_data['artist']."'");
    #			$query = "SELECT id FROM ".$musiclib['tables']['albums']
    #					." WHERE album_title='".$music_data['album']."'";
    			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
    		
    			$GLOBALS['phpgw']->db->next_record();
    			$album_id = $GLOBALS['phpgw']->db->f('id');
    		
    			if (!$album_id) 
    			{
    				echo "Adding album: ".stripslashes($music_data['album'])."<br>";
    			
    				$query = "INSERT INTO ".$musiclib['tables']['albums']." 
    						(artist_id,album_title) 
    						VALUES ($artist_id,'".$music_data['album']."')";
    				$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
    				$album_id = $GLOBALS['phpgw']->db->get_last_insert_id($musiclib['tables']['albums'],'id');
    			}
    		
    			echo "Adding song: ".stripslashes($music_data['song_title'])."<br>";
    			$query = "INSERT INTO ".$musiclib['tables']['songs']." 
    						(artist_id,album_id,track,song_title,file_extension,path,filename,repository) 
    						VALUES ($artist_id,$album_id,'"
    								.$music_data['track']
    								."','".$music_data['song_title']
    								."','".$music_data['file_extension']
    								."','".$music_data['path']
    								."','".$music_data['filename']
    								."',1)";
    			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
    		}
    }
} 

foreach($to_be_deleted as $file) 
{

	$music_data = music_data_file_split($file);

	if ($music_data)
	{
#		print_r($music_data);
#		echo "Entering delete mode<br>";
		
		# get songid
		$query = construct_song_select("WHERE artist_name='".$music_data['artist']
									  ."' AND album_title='".$music_data['album']
									  ."' AND song_title='".$music_data['song_title']
									  ."' AND track='".$music_data['track']
									  ."' AND file_extension='".$music_data['file_extension']
									  ."' AND repository=1"
									  );
#		echo $query."<br>";
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		$GLOBALS['phpgw']->db->next_record();
		$row = $GLOBALS['phpgw']->db->Record;
	#	$field = $musiclib['tables']['songs'].'id';

		$del_song_id   = $row['id'];
		$del_album_id  = $row['album_id'];
		$del_artist_id = $row['artist_id'];
#		print_r($row);

		echo "Deleting song: ".stripslashes($music_data['song_title'])." (".$del_song_id.") <br>";
		$query = "DELETE FROM ".$musiclib['tables']['songs']
				." WHERE id=".$del_song_id;
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		
		# check if album has any more tracks
		$query = "SELECT * from ".$musiclib['tables']['songs']
				." WHERE album_id=".$del_album_id;
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
#		$GLOBALS['phpgw']->db->next_record();
		$album_song_count = $GLOBALS['phpgw']->db->num_rows();
		
		# delete album if no more tracks
		if ($album_song_count == 0) 
		{
			echo "Deleting album: ".stripslashes($music_data['album'])."<br>";
			$query = "DELETE FROM ".$musiclib['tables']['albums']
					." WHERE id=".$del_album_id;
			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		}
		
		# check if artist has any more albums
		$query = "SELECT * from ".$musiclib['tables']['albums']
				." WHERE artist_id=".$del_artist_id;
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
#		$GLOBALS['phpgw']->db->next_record();
		$artist_album_count = $GLOBALS['phpgw']->db->num_rows();

		# delete artist if no more albums
		if ($artist_album_count == 0) 
		{
			echo "Deleting artist: ".stripslashes($music_data['artist'])."<br>";
			$query = "DELETE FROM ".$musiclib['tables']['artists']
					." WHERE id=".$del_artist_id;
			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		}
	}
}


// Begin Misc section
echo "<h2>* Now updating Misc *</h2><br>";

// get album listing from database
$files_from_db_array = get_list_from_db('misc',2,$musiclib['config']['misc_subdir']);

// get file list from filesystem
$files_from_fs_array = get_list_from_fs($musiclib['config']['misc_subdir'],$grep_string);

#print_r($files_from_db_array);
#print_r($files_from_fs_array);

$to_be_added   = music_to_add($files_from_db_array,$files_from_fs_array);
$to_be_deleted = music_to_del($files_from_db_array,$files_from_fs_array);


echo "<br /><h2>To Be Added: </h2>";
print_r($to_be_added);

echo "<br /><h2>To Be Deleted: </h2>";
print_r($to_be_deleted);

echo "<br /><h2>Processing: </h2>";
foreach($to_be_added as $file) 
{

	$music_data = misc_data_file_split($file);
#	print_r($music_data);

	if ($musiclib['config']['validation'] == '1')
	{
		$validation = validate_misc_filename($musiclib['config']['misc_subdir']
								   ,stripslashes($music_data['path'])
								   ,stripslashes($music_data['filename'])
								   ,$music_data['file_extension']);
	}
	
	if (($validation == true) or $musiclib['config']['validation'] == '2') 
	{
			
			$query = "SELECT id FROM ".$musiclib['tables']['songs']
					." WHERE path='".$music_data['path']."'"
					." AND filename='".$music_data['filename']."'"
					." AND file_extension='".$music_data['file_extension']."'"
					." AND repository=2";
			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		
			$GLOBALS['phpgw']->db->next_record();
			$misc_id = $GLOBALS['phpgw']->db->f('id');
			
#			echo $misc_id;
			if (!$misc_id) 
			{
				echo "Adding misc song: ".stripslashes($music_data['filename'])."<br>";

				$query = "INSERT INTO ".$musiclib['tables']['songs']." 
					  	  (path,filename,file_extension,repository) VALUES ('"
					  	 .$music_data['path']."','"
					  	 .$music_data['filename']."','"
					  	 .$music_data['file_extension']."',2)";
				$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
				$misc_id = $GLOBALS['phpgw']->db->get_last_insert_id($musiclib['tables']['songs'],'id');
			}

	}
}
foreach($to_be_deleted as $file) 
{

	$music_data = misc_data_file_split($file);

		# get songid
		$query = "SELECT id FROM ".$musiclib['tables']['songs']
				." WHERE path='".$music_data['path']."'"
				." AND filename='".$music_data['filename']."'"
				." AND file_extension='".$music_data['file_extension']."'"
				." AND repository=2";

#		echo $query."<br>";
		$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		$GLOBALS['phpgw']->db->next_record();

		$misc_id = $GLOBALS['phpgw']->db->f('id');
		
		if ($misc_id) 
		{
			echo "Deleting misc song: ".stripslashes($music_data['filename'])." (".$misc_id.") <br>";
			$query = "DELETE FROM ".$musiclib['tables']['songs']
					." WHERE id=".$misc_id;
			$result = $GLOBALS['phpgw']->db->query($query,__LINE__,__FILE__);
		}
}

// Begin Playlist Validation
echo "<b>Validating Playlists ...</b><br>";
# this checks if song in playlist is missing in database
# if it is missing, it attempts to find it
# if it finds it, it will repair the id

$broken_playlist_count = 0;
$fixed_playlist_count  = 0;

$qry = "SELECT * FROM ".$musiclib['tables']['playlist_contents'];
$result = $GLOBALS['phpgw']->db->query($qry,__LINE__,__FILE__);

while($GLOBALS['phpgw']->db->next_record()) {
	$row = $GLOBALS['phpgw']->db->Record;

	$id 	 = $row['id'];
	$song_id = $row['song_id'];
	$artist  = $row['artist_name'];
	$album   = $row['album_title'];
	$track 	 = $row['track'];

#	echo "record: ".$id;
	
	$GLOBALS['musiclib_query'] = createobject('phpgwapi.db');
	$GLOBALS['musiclib_query']->Database = $GLOBALS['phpgw_info']['server']['db_name'];
	$GLOBALS['musiclib_query']->Host 	 = $GLOBALS['phpgw_info']['server']['db_host'];
	$GLOBALS['musiclib_query']->User 	 = $GLOBALS['phpgw_info']['server']['db_user'];
	$GLOBALS['musiclib_query']->Password = $GLOBALS['phpgw_info']['server']['db_pass'];
	$GLOBALS['musiclib_query']->connect();

	$query = "SELECT id FROM ".$musiclib['tables']['songs']." WHERE id=".$song_id; 
	
#	echo $query;
    $result = $GLOBALS['musiclib_query']->query($query,__LINE__,__FILE__);
#    $result_id = $GLOBALS['musiclib']->f('id');
#    echo $result_id."<br>";
	$NumberRows = $GLOBALS['musiclib_query']->num_rows();
#	echo $NumberRows."<br>";
	if ($NumberRows == 0) {
		$broken_playlist_count++;
		echo "Broken playlist entry found. Attempting to find song ...<br>";

		if ($track) {
			$qry = construct_song_select("WHERE artist_name='".addslashes($artist)."'"
									." AND album_title='".addslashes($album)."'"
									." AND track='".$track."'"
									." AND repository=1"); 
		} else {
			$qry = construct_song_select("WHERE path LIKE '%".addslashes($artist)."%'"
									." AND filename LIKE '%".addslashes($album)."%'" 
									." AND repository=2"); 
		}

		$result = $GLOBALS['musiclib_query']->query($qry,__LINE__,__FILE__);
#		$NumberRows = $GLOBALS['phpgw']->db->num_rows();
	    $GLOBALS['musiclib_query']->next_record();
		$song_id = $GLOBALS['musiclib_query']->f('id');
		$new_filename = $GLOBALS['musiclib_query']->f('filename');
	
		if ($song_id) {
			echo "Found song. Repairing playlist ...<br>";
			
			if ($track) {
				$qry = "UPDATE ".$musiclib['tables']['playlist_contents']
					 ." SET song_id=".$song_id." WHERE id=".$id;
			} else {
				$qry = "UPDATE ".$musiclib['tables']['playlist_contents']
					 ." SET song_id=".$song_id.",album_title='".addslashes($new_filename)
					 ."' WHERE id=".$id;
			}
			
		    $result = $GLOBALS['musiclib_query']->query($qry,__LINE__,__FILE__);
			echo "Playlist repaired.<br>";
			$fixed_playlist_count++;
		}
	}
}

if ($broken_playlist_count == 0) {
	echo "<b>Playlists look good.</b>";
} else {

	echo "<b>".$broken_playlist_count." playlist songs broken.</b><br>";
	echo "<b>".$fixed_playlist_count." playlist songs repaired.</b><br>";
	echo "<b>".($broken_playlist_count - $fixed_playlist_count)." playlist songs unrepaired.</b><br>";
}

// delete the temp file
#del_tmp_files();

$nrOfArtistsAfterUpdate = count_table(1,$musiclib['tables']['artists']);
$nrOfAlbumsAfterUpdate  = count_table(1,$musiclib['tables']['albums']);
$nrOfSongsAfterUpdate   = count_table(1,$musiclib['tables']['songs']);
$nrOfMiscAfterUpdate 	= count_table(2,$musiclib['tables']['songs']);

showBox ("RESULTS<br>", 
		 "Before Update: $nrOfArtistsBeforeUpdate artists, "
		."$nrOfAlbumsBeforeUpdate albums, "
		."$nrOfSongsBeforeUpdate songs "
		."and $nrOfMiscBeforeUpdate misc<br>"
		."After Update: $nrOfArtistsAfterUpdate artists, "
		."$nrOfAlbumsAfterUpdate albums, $nrOfSongsAfterUpdate songs "
		."and $nrOfMiscAfterUpdate misc<br>");

?>