<?php
$COLLECTION_ID = 110;
$TEMP_TBL_NAME = '110Kenneth';
$TotalCount = 0;

if(empty($COLLECTION_ID)||empty($TEMP_TBL_NAME)){
	print 'id and tblname query Parameters Required';
	exit;
}
$SERIES_CONTAINER_LEVEL_ID = 1; $SUBSERIES_CONTAINER_LEVEL_ID = 4; $BOX_CONTAINER_LEVEL_ID = 2; $FOLDER_CONTAINER_LEVEL_ID = 3; $ITEM_CONTAINER_LEVEL_ID = 5;

$LevelContainers = array(
	"Series" 			=> 1,
	"Sub-Series" 		=> 2,
	"Box" 				=> 3,
	"Folder" 			=> 4,
	"Item" 				=> 5,
);
$Enabled = 1;
$containerHierarchy = array();
$ParentID = '';

$i = 1;
//Get all series
$query = 'SELECT Series FROM '.$TEMP_TBL_NAME.' GROUP BY Series ORDER BY RowID';
//echo $query.'<br/>';
$resultSeries = executeTempDBQuery($query);
while($row = mysql_fetch_assoc($resultSeries)){
	//Get Series name
	$Series = mysql_real_escape_string(trim($row{'Series'}));

	//Insert Series
	$query = 'INSERT INTO tblCollections_Content(
		CollectionID, LevelContainerID, LevelContainerIdentifier, Title, PrivateTitle, RootContentID, ParentID, ContainsContent, SortOrder, Enabled)
		VALUES ('.$COLLECTION_ID.', '.$SERIES_CONTAINER_LEVEL_ID.', "'.$i.'", "'.$Series.'", "'.$Series.'", 0, 0, 1, '.$i.', '.$Enabled.')';
	//echo $query.'<br/>';
	$status	= executeArchonDBQuery($query);
	$SeriesID			= mysql_insert_id();

	if($containerHierarchy[sizeof($containerHierarchy)-1]!=$LevelContainers["Series"]){
		array_push($containerHierarchy,	$LevelContainers["Series"]);
		// echo print_r($containerHierarchy).'<br/>';
	}
	$ParentID	= $SeriesID;

	//Update Series.RootContentID with Series.ID
	$query  = 'UPDATE tblCollections_Content SET RootContentID='.$SeriesID.' WHERE ID='.$SeriesID;
	$status	= executeArchonDBQuery($query);
	if ($status==0){echo '<b>Update Series  -'.$SeriesID.'-'.$Series.' SET RootContentID Failed</b> <br/>';}

	if ($status==0){
		echo '<b>Insert Series  -'.$Series.' Failed</b> <br/>';
	}else{
		echo 'Insert Series [ID-'.$SeriesID.'] -'.$Series.' Success. <br/>';
		$TotalCount++;
	}

	$j = 1;
	//Get all Sub-Series per Folder
	$query = 'SELECT Subseries FROM '.$TEMP_TBL_NAME.' WHERE Series="'.$Series.'" GROUP BY Subseries ORDER BY RowID';
	//echo $query.'<br/>';
	$resultSubseries = executeTempDBQuery($query);

	while($row = mysql_fetch_assoc($resultSubseries)){
		//Get Sub-Series
		$Subseries 		= mysql_real_escape_string(trim($row{'Subseries'}));

		//Check if sub-series is not empty
		if(!empty($Subseries)){
			resetParentID($LevelContainers["Sub-Series"]);
			//Insert Sub-Series
			$query = 'INSERT INTO tblCollections_Content(
				CollectionID, LevelContainerID, LevelContainerIdentifier, Title, PrivateTitle, RootContentID, ParentID, ContainsContent, SortOrder, Enabled)
				VALUES ('.$COLLECTION_ID.', '.$SUBSERIES_CONTAINER_LEVEL_ID.', "'.$j.'", "'.$Subseries.'", "'.$Subseries.'", '.$SeriesID.', '.$SeriesID.', 1, '.$j.', '.$Enabled.')';
			//echo $query.'<br/>';
			$status	= executeArchonDBQuery($query);
			$SubseriesID		= mysql_insert_id();

			if($containerHierarchy[sizeof($containerHierarchy)-1]!=$LevelContainers["Sub-Series"]){
				array_push($containerHierarchy,	$LevelContainers["Sub-Series"]);
				// echo print_r($containerHierarchy).'<br/>';
			}

			if ($status==0){
				echo '---<b>Insert Sub-Series  -'.$Subseries.' Failed</b> <br/>';
			}else{
				echo '---Insert Sub-Series [ID-'.$SubseriesID.' Root ID-'.$SeriesID.' Parent ID-'.$SeriesID.'] -'.$Subseries.' Success. <br/>';
				$TotalCount++;
			}
		}

		$k = 1;
		//Get all boxes per sub-series
		$query = 'SELECT Box, BoxNumber FROM '.$TEMP_TBL_NAME.' WHERE Series="'.$Series.'" AND Subseries="'.$Subseries.'" GROUP BY BoxNumber ORDER BY RowID';
		//echo $query.'<br/>';
		$resultBoxes = executeTempDBQuery($query);
		while($row = mysql_fetch_assoc($resultBoxes)){
			//Get Box Number
			$BoxNumber 	= trim($row{'BoxNumber'});
			$Box 		= mysql_real_escape_string(trim($row{'Box'}));

			//Check if BoxNumber is not empty
			if(!empty($BoxNumber)){
				resetParentID($LevelContainers["Box"]);
				//Insert Box
				$query = 'INSERT INTO tblCollections_Content(
					CollectionID, LevelContainerID, LevelContainerIdentifier, Title, PrivateTitle, RootContentID, ParentID, ContainsContent, SortOrder, Enabled)
					VALUES ('.$COLLECTION_ID.', '.$BOX_CONTAINER_LEVEL_ID.', "'.$BoxNumber.'", "'.$Box.'", "'.$Box.'-'.$BoxNumber.'", '.$SeriesID.', '.$ParentID.', 1, '.$k.', '.$Enabled.')';
				//echo $query.'<br/>';
				$status	= executeArchonDBQuery($query);
				$BoxID				= mysql_insert_id();

				if($containerHierarchy[sizeof($containerHierarchy)-1]!=$LevelContainers["Box"]){
					array_push($containerHierarchy, $LevelContainers["Box"]);
					// echo print_r($containerHierarchy).'<br/>';
				}

				if ($status==0){
					echo '--- ---<b>Insert Box  -'.$BoxNumber.'-'.$Box.' Failed</b> <br/>';
				}else{
					echo '--- ---Insert Box [ID-'.$BoxID.' Root ID-'.$SeriesID.' Parent ID-'.$ParentID.'] -'.$BoxNumber.'-'.$Box.' Success. <br/>';
					$TotalCount++;
				}
			}

			$m = 1;
			//Get all Folders per Box
			$query = 'SELECT Folder, FolderNumber FROM '.$TEMP_TBL_NAME.' WHERE Series="'.$Series.'" AND Subseries="'.$Subseries.'" AND BoxNumber="'.$BoxNumber.'" GROUP BY FolderNumber  ORDER BY RowID';
			//echo $query.'<br/>';
			$resultFolders = executeTempDBQuery($query);
			while($row = mysql_fetch_assoc($resultFolders)){
				//Get Folder Number
				$FolderNumber 	= trim($row{'FolderNumber'});
				$Folder 		= mysql_real_escape_string(trim($row{'Folder'}));

				//Check if FolderNumber is not empty
				if(!empty($FolderNumber)){
					resetParentID($LevelContainers["Folder"]);
					//Insert Folder
					$query = 'INSERT INTO tblCollections_Content(
						CollectionID, LevelContainerID, LevelContainerIdentifier, Title, PrivateTitle, RootContentID, ParentID, ContainsContent, SortOrder, Enabled)
						VALUES ('.$COLLECTION_ID.', '.$FOLDER_CONTAINER_LEVEL_ID.', "'.$FolderNumber.'", "'.$Folder.'", "'.$Folder.'", '.$SeriesID.', '.$ParentID.', 1, '.$m.', '.$Enabled.')';
					$status	= executeArchonDBQuery($query);
					$FolderID			= mysql_insert_id();

					if($containerHierarchy[sizeof($containerHierarchy)-1]!=$LevelContainers["Folder"]){
						array_push($containerHierarchy, $LevelContainers["Folder"]);
						// echo print_r($containerHierarchy).'<br/>';
					}

					if ($status==0){
						echo '<b>--- --- ---Insert Folder  -'.$FolderNumber.'-'.$Folder.' Failed</b> <br/>';
					}else{
						echo '--- --- ---Insert Folder [ID-'.$FolderID.' Root ID-'.$SeriesID.' Parent ID-'.$ParentID.'] -'.$FolderNumber.'-'.$Folder.' Success. <br/>';
						$TotalCount++;
					}
				}

				$n = 1;
				//Get all Items per Box
				$query = 'SELECT Item, ItemNumber, Title, Date, Description FROM '.$TEMP_TBL_NAME.' WHERE Series="'.$Series.'" AND BoxNumber="'.$BoxNumber.'" AND FolderNumber="'.$FolderNumber.'" AND Subseries="'.$Subseries.'"';
				//echo $query.'<br/>';
				$resultItems = executeTempDBQuery($query);
				//if(mysql_num_rows($resultItems)>0){}
				while($row = mysql_fetch_assoc($resultItems)){
					//Get Item Number
					$Item 			= mysql_real_escape_string(trim($row{'Item'}));
					$ItemNumber 	= trim($row{'ItemNumber'});
					$Title 			= mysql_real_escape_string(trim($row{'Title'}));
					$Date 			= trim($row{'Date'});
					$Description 	= mysql_real_escape_string(trim($row{'Description'}));

					//Check if ItemNumber is not empty
					if(!empty($ItemNumber)){
						resetParentID($LevelContainers["Item"]);
						//Insert Item
						$query = 'INSERT INTO tblCollections_Content(
							CollectionID, LevelContainerID, LevelContainerIdentifier, Title, PrivateTitle, Date, Description, RootContentID, ParentID, ContainsContent, SortOrder, Enabled)
							VALUES ('.$COLLECTION_ID.', '.$ITEM_CONTAINER_LEVEL_ID.', "'.$ItemNumber.'", "'.$Item.'", "'.$Title.'", "'.$Date.'", "'.$Description.'", '.$SeriesID.', '.$ParentID.', 0, '.$n.', '.$Enabled.')';
						//echo $query.'<br/>';
						$status	= executeArchonDBQuery($query);
						$ItemID	= mysql_insert_id();

						if($containerHierarchy[sizeof($containerHierarchy)-1]!=$LevelContainers["Item"]){
							array_push($containerHierarchy, $LevelContainers["Item"]);
							// echo print_r($containerHierarchy).'<br/>';
						}

						if ($status==0){
							echo '<b>--- --- --- ---Insert Item  -'.$ItemNumber.'-'.$Title.'-'.$Item.' Failed</b> <br/>';
						}else{
							echo '--- --- --- ---Insert Item [ID-'.$SubseriesID.' Root ID-'.$SeriesID.' Parent ID-'.$ParentID.'] -'.$ItemNumber.'-'.$Title.'-'.$Item.' Success. <br/>';
							$TotalCount++;
						}
					}
					//$rowsProcessed++;
					unset($ItemNumber);
					$n++;
				}
				unset($FolderNumber);
				$m++;
			}
			unset($BoxNumber);
			$k++;
		}
		unset($Subseries);
		$j++;
	}
	unset($Series);
	$i++;
}

echo $TotalCount.' records inserted.';

function resetParentID($currentLevelContainer){
	global $LevelContainers;
	global $containerHierarchy;

	if (($currentLevelContainer != $containerHierarchy[sizeof($containerHierarchy)-1])){

		if(($currentLevelContainer > $containerHierarchy[sizeof($containerHierarchy)-1])){
			//Current container is the child of the last container (traveling down)
			$lastParent = $containerHierarchy[sizeof($containerHierarchy)-1];
			switch ($lastParent) {
				case $LevelContainers["Series"]:
					$GLOBALS[ParentID] = $GLOBALS[SeriesID];
					//echo 'Reset Parent to Series<br/>';
					break;
				case $LevelContainers["Sub-Series"]:
					$GLOBALS[ParentID] = $GLOBALS[SubseriesID];
					//echo 'Reset Parent to Sub Series<br/>';
					break;
				case $LevelContainers["Box"]:
					$GLOBALS[ParentID] = $GLOBALS[BoxID];
					//echo 'Reset Parent to Box<br/>';
					break;
				case $LevelContainers["Folder"]:
					$GLOBALS[ParentID] = $GLOBALS[FolderID];
					//echo 'Reset Parent to Folder<br/>';
					break;
			}
		}
		else{
			//Traveling back up
			$lastContainerType = array_pop($containerHierarchy);

			while($lastContainerType >= $currentLevelContainer){
				$lastContainerType = array_pop($containerHierarchy);
			}

			switch ($lastContainerType) {
				case $LevelContainers["Series"]:
					$GLOBALS[ParentID] = $GLOBALS[SeriesID];
					//echo 'Reset Parent to Series<br/>';
					break;
				case $LevelContainers["Sub-Series"]:
					$GLOBALS[ParentID] = $GLOBALS[SubseriesID];
					//echo 'Reset Parent to Sub Series<br/>';
					break;
				case $LevelContainers["Box"]:
					$GLOBALS[ParentID] = $GLOBALS[BoxID];
					//echo 'Reset Parent to Box<br/>';
					break;
				case $LevelContainers["Folder"]:
					$GLOBALS[ParentID] = $GLOBALS[FolderID];
					//echo 'Reset Parent to Folder<br/>';
					break;
			}
		}

	}
}

//Archon DB
function executeArchonDBQuery($query){
	$dbuser = '';
	$dbpass = '';
	$dbname = 'Archon';
	$dbhost = 'localhost';

	$dbh = mysql_connect($dbhost,$dbuser,$dbpass) or die (mysql_error());
	$selected = mysql_select_db($dbname,$dbh) or die (mysql_error());
	return mysql_query($query);
}

//Temp DB
function executeTempDBQuery($query){
	$dbuser = '';
	$dbpass = '';
	$dbname = 'ArchonTemp';
	$dbhost = 'localhost';

	$dbh = mysql_connect($dbhost,$dbuser,$dbpass) or die (mysql_error());
	$selected = mysql_select_db($dbname,$dbh) or die (mysql_error());
	return mysql_query($query);
}
?>