Location: PHPKode > projects > NCD Toolkit > ncdtoolkit1.2/ncd/import_nodit.php
<?php
// zet timeout naar oneindig
	set_time_limit(0);
// test of gebruiker bij ETI vandaan komt, zo nee, geef fake warschuwing dat document niet bestaat ;)
/*	$ip = getenv("REMOTE_ADDR");
	if (strncmp($ip, "145.18.162", 10) <> 0)
	{
	 echo "<!DOCTYPE HTML PUBLIC \"-//IETF//DTD HTML 2.0//EN\">";
	 echo "<HTML><HEAD> <TITLE>404 Not Found</TITLE> </HEAD><BODY>";
	 echo "<H1>Not Found</H1>";
	 echo "The requested URL /bis/createwbd.php was not found on this server.<P>";
	 echo "<P>Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.<HR>";
	 echo "<ADDRESS>Apache/1.3.33 Server at eti.uva.nl Port 80</ADDRESS></BODY></HTML>";
	 return;
	}
*/?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
  <meta http-equiv="imagetoolbar" content="no">
  <link href="wbd.css" rel="stylesheet" media="screen" type="text/css">
  <title></title>
</head>
<body>

<?php
	$metadatabase = "metadatabase_test";
	$noditdatabase = "nodit-ruud";
    include("connect.inc.php");
	
	echo "<b>Preparing database...</b><br>";
	flush();

	// Voeg landenlijst toe
	echo "Filling table of countries...<br>";
	include("insert_countries.inc.php");
	flush();

	// Voeg talenlijst toe
	echo "Filling table of languages...<br>";
	include("insert_languages.inc.php");
	flush();

	// Voeg predetermined keywords toe
	echo "Filling table of keywords...<br>";
	include("insert_keywords.inc.php");
	flush();

	echo "Emptying database...<br><br>";
	include("insert_languages.inc.php");
	flush();

	// Maak eerst relevante tabellen leeg in metadatabase
	mysql_select_db($metadatabase, $conn) or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE Collections") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE CollectionExtents") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE CollectionDataByLanguage") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE IPRStatements") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE Keywords") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE KeywordTypes") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE KeywordQualifiers") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE RelatedMaterials") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE RelatedResources") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE ResourceTypes") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE Organisations") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE OrganisationDataByLanguage") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE OrganisationClassesToOrganisation") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE OrganisationClasses") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE Persons") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE PersonRoleToPerson") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE PersonRoles") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE Users") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE JobTitles") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE CollectionsToOrganisations") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE PersonToCollections") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE PersonToOrganisations") or die(mysql_error());
	mysql_db_query($metadatabase, "TRUNCATE TABLE UnitOfMeasures") or die(mysql_error());
	
	// Twee units of measure staan al vast: soorten en specimens
	mysql_db_query($metadatabase, "INSERT INTO UnitOfMeasures (UnitOfMeasure, LanguageID) VALUES ('Specimens', 14)") or die(mysql_error());
	mysql_db_query($metadatabase, "INSERT INTO UnitOfMeasures (UnitOfMeasure, LanguageID) VALUES ('Species', 14)") or die(mysql_error());
	
	// Variabelen
    $a = 0;
    $b = 0;
    $c = 0;
    $d = 0;
    $e = 0;
    $f = 0;
    $g = 0;
    $h = 0;
    $users = array();
   	$keywordtypes = array();
   	$keywordqualifiers = array();
   	$personroles = array();
   	$organisationclasses = array();
   	//$jobtitles = array();
   	$noditcheckarray = array();
   	$organisations = array("id" => array(), "name" => array(), "subname" => array());
   	$check_organisations = array();
   	$fullnames = array("id" => array(), "familyname" => array(), "givenname" => array(), "personrole" => array());
   	$count_persontoorganisations = array();
   	$persontocollections = array("personid" => array(), "collectionid" => array());

	echo "<b>Compiling collection data...</b><br>";
	flush();

	// *** Haal collectiegegevens op (body type = 3) ***
	mysql_select_db($noditdatabase, $conn) or die(mysql_error());
	$query_collections = "SELECT * FROM body WHERE body_type_fk = 3";
	$result_collections = mysql_query($query_collections);
    while($row_collections = mysql_fetch_array($result_collections))
    {
    	$a++;
    	$collectionid = $row_collections["body_id"];
    	$address_fk = $row_collections["address_fk"];
    	
		$query_name = "SELECT institution_name FROM address WHERE address_id = $address_fk";
		$row_name = mysql_fetch_array(mysql_query($query_name));
		$collectionname = $row_name["institution_name"];
		
    	$nodit_communication_fk = $row_collections["communication_fk"];
    	$citation = addslashes($row_collections["citation"]);
    	$accessrestrictions = $row_collections["access_restriction"];
    	$usagerestrictions = $row_collections["usage_restriction"];
    	$collectionfocus = $row_collections["strength"];
    	$collectionpurpose = $row_collections["purpose"];
    	$iprstatement = addslashes($row_collections["disclaimer"]);
    	$digitalstorageformat = $row_collections["doc_state"];
    	$includestypes = $row_collections["includes_types"];
    	$numberofspecimens = $row_collections["number_of_subunits"];
    	$numberofspecies = $row_collections["number_of_species"];
    	$percentdatabased = $row_collections["percent_database"];
    	$notes = addslashes($row_collections["notes"]);
    	$recordcreatedby = $row_collections["created_who"];
    	if (!in_array($recordcreatedby, $users))
    		array_push($users, $recordcreatedby);
    	$recordcreatedbyid = array_search($recordcreatedby, $users);
    	$recordcreatedbyid++;
    	$recordcreated = $row_collections["created_when"];
    	$recordeditedby = $row_collections["updated_who"];
    	if (!in_array($recordeditedby, $users))
    		array_push($users, $recordeditedby);
    	$recordeditedbyid = array_search($recordeditedby, $users);
    	$recordeditedbyid++;
    	$recordeditdate = $row_collections["updated_when"];

		// Schrijf data weg naar Collections tabel
		$insert = "INSERT INTO Collections (
			ID,
			RecordCreated,
			RecordCreatedBy,
			RecordEditDate,
			RecordEditedBy,
			IncludesTypes,
			PercentageDatabased)
		VALUES (
			$collectionid,
			'$recordcreated',
			$recordcreatedbyid,
			'$recordeditdate',
			$recordeditedbyid,
			'$includestypes',
			'$percentdatabased')";
		mysql_db_query($metadatabase, $insert) or die(mysql_error()); 
		
		// Schrijf data weg naar CollectionDataByLanguage tabel
		$insert = "INSERT INTO CollectionDataByLanguage (
			CollectionID,
			Name,
			Focus,
			Purpose,
			Notes,
			AccessRestrictions,
			UsageRestrictions)
		VALUES (
			$collectionid,
			'$collectionname',
			'$collectionfocus',
			'$collectionpurpose',
			'$notes',
			'$accessrestrictions',
			'$usagerestrictions')";
		mysql_db_query($metadatabase, $insert) or die(mysql_error());
		
		// Schrijf aantal specimens weg naar CollectionExtents
		if ($numberofspecimens != "")
		{
			$insert = "INSERT INTO CollectionExtents (CollectionID, Size, UnitOfMeasureID) VALUES ($collectionid, '$numberofspecimens', 1)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		}
		
		// Schrijf aantal soorten weg naar CollectionExtents
		if ($numberofspecies != "")
		{
			$insert = "INSERT INTO CollectionExtents (CollectionID, Size, UnitOfMeasureID) VALUES ($collectionid, '$numberofspecies', 2)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		}
		
		// Schrijf data weg naar IPRStatements
		if ($iprstatement != "")
		{
			$insert = "INSERT INTO IPRStatements (CollectionID, IPRStatement) VALUES ($collectionid, '$iprstatement')";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		}
		
    	// Haal keywords op voor collectie
    	// Qualifiers mogen NULL zijn dus deze moeten met een aparte query opgehaald worden
		mysql_select_db($noditdatabase, $conn) or die(mysql_error());
		$query_keywords = "SELECT t2.keyword, t3.keyword_type, t1.qualifier_fk, t1.bodykeyword_id FROM bodykeyword t1, keyword t2, keywordtype t3 WHERE t2.keyword_id = t1.keyword_fk AND t2.keyword_type_fk = t3.keyword_type_id AND t1.body_fk = $collectionid";
		$result_keywords = mysql_query($query_keywords) or die(mysql_error());
		while($row_keywords = mysql_fetch_array($result_keywords))
		{
			$b++;
			$keywordtype = $row_keywords["keyword_type"];
    		$keyword = $row_keywords["keyword"];
    		$qualifierid = $row_keywords["qualifier_fk"];
			if (!in_array($keywordtype, $keywordtypes))
			{
				array_push($keywordtypes, $keywordtype);
				$insert_keywordtype = TRUE;
			}
			$keywordtypeid = array_search($keywordtype, $keywordtypes);
			$keywordtypeid++;
			
			// Bepaal qualifier als $qualifierid niet leeg is
			if ($qualifierid != "")
			{
				mysql_select_db($noditdatabase, $conn) or die(mysql_error());
				$query_qualifier = "SELECT t2.qualifier FROM bodykeyword t1, qualifier t2 WHERE t2.qualifier_id = $qualifierid";
				$result_qualifier = mysql_query($query_qualifier) or die(mysql_error());
				$row_qualifier = mysql_fetch_array($result_qualifier) or die(mysql_error());
				$keywordqualifier = $row_qualifier["qualifier"];
				if (!in_array($keywordqualifier, $keywordqualifiers))
				{
					array_push($keywordqualifiers, $keywordqualifier);
					$insert_keywordqualifier = TRUE;
				}
				$keywordqualifierid = array_search($keywordqualifier, $keywordqualifiers);
				$keywordqualifierid++;
			}
			else
				$keywordqualifierid = "";
		
    		// Vul Keywords tabel
    		$insert = "INSERT INTO Keywords (CollectionID, Keyword, KeywordTypeID, KeywordQualifierID, LanguageID)
							VALUES ($collectionid, '$keyword', $keywordtypeid, '$keywordqualifierid', 14)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
    		
     		// Vul KeywordTypes tabel
	   		if ($insert_keywordtype == 1)
	   		{
				$insert = "INSERT INTO KeywordTypes (ID, KeywordType, LanguageID)
								VALUES ($keywordtypeid, '$keywordtype', 14)";
				mysql_db_query($metadatabase, $insert) or die(mysql_error());
			}
    		
    		// Vul KeywordQualifiers tabel
    		if ($insert_keywordqualifier == TRUE)
    		{
				$insert = "INSERT INTO KeywordQualifiers (ID, Qualifier, LanguageID)
								VALUES ($keywordqualifierid, '$keywordqualifier', 14)";
				mysql_db_query($metadatabase, $insert) or die(mysql_error());
			}
			
			$insert_keywordtype = FALSE;
			$insert_keywordqualifier = FALSE;
		}
		
		// Haal related materials op voor collectie
		mysql_select_db($noditdatabase, $conn) or die(mysql_error());
		$query_materials = "SELECT related_material, related_material_type_fk FROM relatedmaterial WHERE body_fk = $collectionid";
		$result_materials = mysql_query($query_materials) or die(mysql_error());
		while($row_materials = mysql_fetch_array($result_materials))
		{
			$c++;
			$resource = $row_materials["related_material"];
			$resourcetypeid = $row_materials["related_material_type_fk"];
			
			// Type 1 is publication, type 3 is website; id van laatste omzetten naar 2
			if ($resourcetypeid == 3)
				$resourcetypeid = 2;

    		// Vul RelatedMaterials tabel
    		$insert = "INSERT INTO RelatedMaterials (CollectionID)
							VALUES ($collectionid)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
    		
    		// Vul RelatedResources tabel
    		$insert = "INSERT INTO RelatedResources (RelatedMaterialsID, Resource, ResourceTypeID)
							VALUES ($c, '$resource', $resourcetypeid)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
    		
    		// Vul eenmalig de ResourceTypes tabel
    		if ($c == 1)
    		{
				$insert = "INSERT INTO ResourceTypes (ResourceType, LanguageID)
								VALUES ('Publication', 14)";
				mysql_db_query($metadatabase, $insert) or die(mysql_error());
				$insert = "INSERT INTO ResourceTypes (ResourceType, LanguageID)
								VALUES ('Website', 14)";
				mysql_db_query($metadatabase, $insert) or die(mysql_error());
    		}
		}
	}
	echo "Added $a collections with $b related keywords and $c related resources<br><br>";
	flush();
	
	echo "<b>Compiling organisation data...</b><br>";
	flush();

	// *** Haal organisatiegegevens op ***
	// Dit is niet simpelweg een query met bodytype = 2, omdat voor elke collectie in de NoDit database opnieuw de instituutsgegevens ingevuld moesten worden. In theorie kan dus alleen instituutsinformatie opgenomen zijn onder collectieinformatie. De naam en "subnaam" zijn opgeschoond in de NoDIT database om duplicaten als Zoological Museum Amsterdam, Zoologisch Museum Amsterdam en Zo├Âlogisch Museum Amsterdam te vermijden. De query is daarna als volgt opgebouwd:
	
	// 1. De hele body tabel wordt doorzocht, gesorteerd op body type = 2 (organisatiegegevens hebben dus prioriteit)
	// 2. ID, naam en "subnaam" van het instituut worden opgeslagen in een multidimensionale array
	// 3. Voor ieder record wordt gecheckt of naam van het instituut en "subnaam" al weggeschreven zijn, zo niet dan pas wordt de organisatie opgeslagen. Anders zouden er veel duplicaten optreden. 

	// Oude query (incompleet) was: $query_organisations = "SELECT * FROM body t1, address t2, communication t3 WHERE t1.address_fk = t2.address_id AND body_type_fk = 2 AND t3.communication_id = t1.communication_fk";
	
	mysql_select_db($noditdatabase, $conn) or die(mysql_error());
	$query_organisations = "SELECT * FROM body t1, address t2, communication t3 WHERE t1.address_fk = t2.address_id AND t3.communication_id = t1.communication_fk ORDER BY t1.body_type_fk, body_id";
	$result_organisations = mysql_query($query_organisations);
    while($row_organisations = mysql_fetch_array($result_organisations))
    {
		$organisationid = $row_organisations["body_id"];
		$accessrestrictions = addslashes($row_organisations["access_restriction"]);
		$strength = addslashes($row_organisations["strength"]);
		$purpose = addslashes($row_organisations["purpose"]);
		$notes = addslashes($row_organisations["notes"]);
    	$recordcreatedby = $row_organisations["created_who"];
    	if (!in_array($recordcreatedby, $users))
    		array_push($users, $recordcreatedby);
		$recordcreatedbyid = array_search($recordcreatedby, $users);
		$recordcreatedbyid++;
    	$recordcreated = $row_organisations["created_when"];
    	$recordeditedby = $row_organisations["updated_who"];
    	if (!in_array($recordeditedby, $users))
    		array_push($users, $recordeditedby);
		$recordeditedbyid = array_search($recordeditedby, $users);
		$recordeditedbyid++;
    	$recordeditdate = $row_organisations["updated_when"];
		$noditname = trim(addslashes($row_organisations["institution_name"]));
		$subunitname = addslashes($row_organisations["sub_unit_name"]);
		$noditsubname = trim($subunitname);
		$postaladdresstext = $row_organisations["address_line_1"];
		if ($row_organisations["address_line_2"] != "")
			$postaladdresstext .= ", ".$row_organisations["address_line_2"];
		if ($row_organisations["address_line_3"] != "")
			$postaladdresstext .= ", ".$row_organisations["address_line_3"];
		if ($row_organisations["address_line_4"] != "")
			$postaladdresstext .= ", ".$row_organisations["address_line_4"];
		$postaladdresstext = addslashes($postaladdresstext);
		$postaltown = $row_organisations["town"];
		$postregion = $row_organisations["region"];
		$postalzipcode = $row_organisations["zip"];
		$telephone = $row_organisations["telephone"];
		$fax = $row_organisations["fax"];
		$email = $row_organisations["email"];
		$logourl = $row_organisations["logo_url"];
		if ($logourl == "http://")
			$logourl = "";
		
		// Voeg naam organisatie toe als deze nog niet in de $organisations array staat
		if (!in_array($noditname, $organisations["name"]))
			$addtocollections = TRUE;
		else
		// Check subnaam als de naam wel voorkomt in de $organisations array
		{
			// Haal alle keys op van de organisatie die in de $organisations array en check in een loop of de subnaam overeen komt
			$organisations_keys = array_keys($organisations["name"], $noditname);
			for ($i = 0; $i < count($organisations_keys); $i++)
			{	
				$check_collectionid = $organisations_keys[$i];
				$check_subunitname = $organisations["subname"][$check_collectionid];
				$check_name = $organisations["name"][$check_collectionid];
				//echo"<span style=\"color:red\">Check ".($i+1).": $noditname - $noditsubname vs<br>$check_name - $check_subunitname</span><br><br>";flush();
				// Stop de subnamen in een aparte array
				array_push($check_organisations, $check_subunitname);
			}
			// Check of the subnaam overeenkomt met een van de subnamen in de check array; zo niet voeg dan organisatie toe
			if (!in_array($subunitname, $check_organisations))
				$addtocollections = TRUE;
			// Maak check array weer leeg
			$check_organisations = array();
		}
		
		if ($addtocollections == TRUE)
		{
			array_push($organisations["id"], $organisationid);
			array_push($organisations["name"], $noditname);
			array_push($organisations["subname"], $noditsubname);
			$d++;

			// Zoek beschrijving, url en naam op
			mysql_select_db($noditdatabase, $conn) or die(mysql_error());
			$query_extension = "SELECT t2.extension, t2.iso_language, t2.ext_cat_fk FROM body t1, body_ext t2 WHERE t1.body_id = t2.body_fk AND t1.body_id = $organisationid" or die(mysql_error());
			$result_extension = mysql_query($query_extension) or die(mysql_error());
			while($row_extension = mysql_fetch_array($result_extension))
			{
				$extension = addslashes($row_extension["extension"]);
				$extensionlanguage = $row_extension["iso_language"];
				$extensiontype = $row_extension["ext_cat_fk"];
				if ($extensiontype == 1)
				{
					if ($extensionlanguage == "EN")
						$abstractuk = $extension;
					else
						$abstractnl = $extension;
				}
				if ($extensiontype == 2)
				{
					$websiteurl = $extension;
					if ($websiteurl == "http://")
						$websiteurl = "";

				}
				if ($extensiontype == 3)
				{
					if ($extensionlanguage == "EN")
						$nameuk = $extension;
					else
						$namenl = $extension;
				}
				// Onderaan de loop moeten deze variabelen weer leeggemaakt worden
			}
			
			// Zoek organisatie klasse (organisation class is keyword type 9)
			mysql_select_db($noditdatabase, $conn) or die(mysql_error());
			$query_classes = "SELECT t3.keyword FROM body t1, bodykeyword t2, keyword t3 WHERE t1.body_id = t2.body_fk AND t2.keyword_fk = t3.keyword_id AND t3.keyword_type_fk = 9 AND t1.body_id = $organisationid" or die(mysql_error());
			$result_classes = mysql_query($query_classes);
			while($row_classes = mysql_fetch_array($result_classes))
			{
				$organisationclass = $row_classes["keyword"];
				if ($organisationclass != "" && !in_array($organisationclass, $organisationclasses))
					array_push($organisationclasses, $organisationclass);
				$organisationclassid = array_search($organisationclass, $organisationclasses);
				$organisationclassid++;
				
				// Vul tussentabel OrganisationClassesToOrganisation
				if ($organisationclass != "")
				{
					$insert = "INSERT INTO OrganisationClassesToOrganisation (OrganisationID, OrganisationClassID) VALUES ($organisationid, $organisationclassid)";
					mysql_db_query($metadatabase, $insert) or die(mysql_error());
				}
			}
	
			// Zoek de rol op van degene die als communicatie opgegeven is
			mysql_select_db($noditdatabase, $conn) or die(mysql_error());
			$query_contactpost = "SELECT t3.person_role FROM body t1, personinbody t2, personrole t3 WHERE t1.body_id = $organisationid AND t1.body_id = t2.body_fk AND t2.person_role_fk = t3.person_role_id";
			$result_contactpost = mysql_query($query_contactpost);
			$row_contactpost = mysql_fetch_array($result_contactpost);
			$personrole = $row_contactpost["person_role"];
			if ($personrole != "" && !in_array($personrole, $personroles))
				array_push($personroles, $personrole);
			$personroleid = array_search($personrole, $personroles);
			$personroleid++;
	
			// Schrijf data weg naar Organisations tabel
			$insert = "INSERT INTO Organisations (
				ID,
				RecordCreated,
				RecordCreatedBy,
				RecordEditDate,
				RecordEditedBy,
				OrganisationContactPostID,
				PostalAddressText,
				PostalZipCode,
				PostalTown,
				PostalRegion,
				CountryID,
				Telephone,
				Fax,
				Email,
				LogoURL,
				WebsiteURL)
			VALUES (
				$organisationid,
				'$recordcreated',
				$recordcreatedbyid,
				'$recordeditdate',
				$recordeditedbyid,
				'$personroleid',
				'$postaladdresstext',
				'$postalzipcode',
				'$postaltown',
				'$postalregion',
				151,
				'$telephone',
				'$fax',
				'$email',
				'$logourl',
				'$websiteurl')";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
	
			// Schrijf data in het Nederlands weg naar OrganisationDataByLanguage tabel
			$insert = "INSERT INTO OrganisationDataByLanguage (
				Organisationid,
				Name,
				SubUnitName,
				Strength,
				Purpose,
				Abstract,
				Notes,
				LanguageID)
			VALUES (
				$organisationid,
				'$namenl',
				'$subunitname',
				'$strength',
				'$purpose',
				'$abstractnl',
				'$notes',
				47)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		
			// Schrijf data in het Engels weg naar OrganisationDataByLanguage tabel
			$insert = "INSERT INTO OrganisationDataByLanguage (
				Organisationid,
				Name,
				Abstract,
				Notes,
				LanguageID)
			VALUES (
				$organisationid,
				'$nameuk',
				'$abstractuk',
				'$notes',
				14)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
			
			// Maak de variabelen $addtocollections, $nameuk, $namenl, $websiteurl, $abstractuk en $abstractnl false/leeg
			$nameuk = $namenl = $websiteurl = $abstractuk = $abstractnl = "";
			$addtocollections = FALSE;
		}
	}
	
	echo "Added $d organisations<br>";
	flush();

	// Vul OrganisationClasses tabel met gegevens uit $organisationclasses array
	foreach ($organisationclasses as $organisationclass)
	{
		mysql_db_query($metadatabase, "INSERT INTO OrganisationClasses (OrganisationClass, LanguageID) VALUES ('$organisationclass', 14)") or die(mysql_error());
	}
	$number_of_organisationclasses = count($organisationclasses);
	echo "Added $number_of_organisationclasses organisation classes<br><br>";
	flush();
	
	
	// *** Haal persoonsgegevens op ***
	// Dit is opnieuw een ingewikkelde query omdat een persoon zowel aan een instituut als aan een collectie gelinkt kan zijn. Door de opzet van de NoDIT database kunnen er veel duplicaten optreden maar het hoeft niet. De hele body tabel wordt doorlopen waarbij de dubbele person_id verwijderd worden mbv een GROUP BY
	// Hierna worden de voor- en achternaam en rol in een multidimensionale array gezet. Als een naam al opgenomen is wordt gecheckt of deze verbonden is aan hetzelfde instituut met dezelfde rol. Als dat niet het geval is wordt PersonToOrganisations tabel met deze gegevens ingevuld.	
	
	echo "<b>Compiling person data...</b><br>";
	flush();

	mysql_select_db($noditdatabase, $conn) or die(mysql_error());
	$query_persons = "SELECT t3.person_id, t1.body_type_fk, t1.body_id, t3.name_prefix, t3.family_name, t3.given_name, t3.name_suffix, t3.additional_names, t3.job_title, t4.institution_name, t4.sub_unit_name, t4.address_line_1, t4.address_line_2, t4.address_line_3, t4.address_line_4, t4.zip, t4.town, t4.region, t6.telephone, t6.fax, t6.email, t5.person_role FROM body t1, personinbody t2, person t3, address t4, personrole t5, communication t6 WHERE t1.body_id = t2.body_fk AND t2.person_fk = t3.person_id AND t1.address_fk = t4.address_id AND t2.person_role_fk = t5.person_role_id AND t3.communication_fk = t6.communication_id GROUP BY t3.person_id ORDER BY t1.body_type_fk";
	
	$result_persons = mysql_query($query_persons);
    while($row_persons = mysql_fetch_array($result_persons))
    {
		$personid = $row_persons["person_id"];
		$bodyid = $row_persons["body_id"];
		$noditbodytype = $row_persons["body_type_fk"];
		$instituteforperson = $row_persons["institution_name"]; 
		$subunitforperson = $row_persons["sub_unit_name"];

		$organisations_keys = array_keys($organisations["name"], $instituteforperson);
		for ($i = 0; $i < count($organisations_keys); $i++)
		{	
			$check_collectionid = $organisations_keys[$i];
			$check_subunitname = $organisations["subname"][$check_collectionid];
			if ($subunitforperson == $check_subunitname)
			{
				$organisationid = $organisations["id"][$check_collectionid];
				if (!in_array($organisationid, $count_persontoorganisations))
					array_push($count_persontoorganisations, $organisationid);
			}
		}

		$nameprefix = $row_persons["name_prefix"];
		$familyname = $row_persons["family_name"];
		$givenname = $row_persons["given_name"];
		$namesuffix = $row_persons["name_suffix"];
		$additionalnames = $row_persons["additional_names"];
		$jobtitle = $row_persons["job_title"];
    	/*if ($jobtitle != "" && !in_array($jobtitle, $jobtitles))
    		array_push($jobtitles, $jobtitle);
    	$jobtitleid = array_search($jobtitle, $jobtitles);
    	$jobtitleid++;*/
		$personpostaladdresstext = $row_persons["address_line_1"];
		if ($row_organisations["address_line_2"] != "")
			$personpostaladdresstext .= ", ".$row_persons["address_line_2"];
		if ($row_organisations["address_line_3"] != "")
			$personpostaladdresstext .= ", ".$row_persons["address_line_3"];
		if ($row_organisations["address_line_4"] != "")
			$personpostaladdresstext .= ", ".$row_persons["address_line_4"];
		$personpostaladdresstext = addslashes($personpostaladdresstext);
		$personzip = $row_persons["zip"];
		$persontown = $row_persons["town"];
		$personregion = $row_persons["region"];
		$persontelephone = $row_persons["telephone"];
		$personfax = $row_persons["fax"];
		$personemail = $row_persons["email"];
		$personrole = $row_persons["person_role"];
    	if ($personrole != "" && !in_array($personrole, $personroles))
    		array_push($personroles, $personrole);
    	$personroleid = array_search($personrole, $personroles);
    	$personroleid++;
    	
    	// Zoek apart created gegevens op (anders kunnen dubbele entries niet uitgefilterd worden)
		mysql_select_db($noditdatabase, $conn) or die(mysql_error());
		$query_created = "SELECT t1.created_who, t1.created_when, t1.updated_who, t1.updated_when FROM body t1, personinbody t2, person t3, personrole t4 WHERE t1.body_id = t2.body_fk AND t2.person_fk = t3.person_id AND t2.person_role_fk = t4.person_role_id AND t3.family_name = '$familyname' AND t3.given_name = '$givenname' AND t4.person_role = '$personrole'";
		$result_created = mysql_query($query_created) or die(mysql_error());
		$row_created = mysql_fetch_array($result_created) or die(mysql_error());
    	$recordcreatedby = $row_created["created_who"];
    	if (!in_array($recordcreatedby, $users))
    		array_push($users, $recordcreatedby);
    	$recordcreatedbyid = array_search($recordcreatedby, $users);
    	$recordcreatedbyid++;
    	$recordcreated = $row_created["created_when"];
    	$recordeditedby = $row_created["updated_who"];
    	if (!in_array($recordeditedby, $users))
    		array_push($users, $recordeditedby);
    	$recordeditedbyid = array_search($recordeditedby, $users);
    	$recordeditedbyid++;
    	$recordeditdate = $row_created["updated_when"];
    	
		// Check of deze persoon al bestaat, zo niet voeg toe
		if ($familyname == "")
			$familyname = "$instituteforperson contact";
		if (!in_array($familyname, $fullnames["familyname"]))
			$addtopersons = TRUE;
		else
		{
			$key = array_search($givenname, $fullnames["givenname"]);
			if ($fullnames["givenname"][$key] != $givenname)
				$addtopersons = TRUE;
			else
			{
				$thispersonid = $fullnames["id"][$key];
				$thispersonrole = $fullnames["personrole"][$key];
				if ($thispersonrole != $personrole)
					$addtopersons = TRUE;

				// Persoon bestaat al met dezelde rol en wordt verder overgeslagen; gegevens moeten wel naar
				// PersonToOrganisations en PersonToCollections geschreven worden.
				else
				{
					// Schrijf data weg naar PersonToOrganisations tabel
					$e++;
					$insert = "INSERT INTO PersonToOrganisations (
						ID,
						PersonID,
						OrganisationID,
						RoleID,
						PostalAddressText,
						ZipCode,
						Town,
						Region,
						CountryID,
						JobTitleID,
						Telephone,
						Fax,
						Email)
					VALUES (
						$e,
						$thispersonid,
						$organisationid,
						$personroleid,
						'$personpostaladdresstext',
						'$personzip',
						'$persontown',
						'$personregion',
						151,
						'$jobtitleid',
						'$persontelephone',
						'$personfax',
						'$personemail')";
					mysql_db_query($metadatabase, $insert) or die(mysql_error());
					
					$insert = "INSERT INTO JobTitles (PersonToOrganisationID, JobTitle, LanguageID) VALUES ($e, '$jobtitle', 14)";
					mysql_db_query($metadatabase, $insert) or die(mysql_error());
					
					// Vul de $persontocollections array om aan het eind deze tussentabel mee te vullen
					// Dit geldt alleen als body type = 3
					if ($noditbodytype == 3)
					{
						$h++;
						array_push($persontocollections["personid"], $thispersonid);
						array_push($persontocollections["collectionid"], $bodyid);						
					}
				}
			}
		}

		// Schrijf data weg naar Persons, PersonToOrganisations en PersonRoleToPerson tabellen
		if ($addtopersons == TRUE)
		{
			$e++;
			// Voeg gegevens toe aan $fullnames array
			array_push($fullnames["id"], $personid);
			array_push($fullnames["familyname"], $familyname);
			array_push($fullnames["givenname"], $givenname);
			array_push($fullnames["personrole"], $personrole);
			
			// Voeg gegevens toe aan $persontocollections array
			if ($noditbodytype == 3)
			{
				array_push($persontocollections["personid"], $personid);
				array_push($persontocollections["collectionid"], $bodyid);
			}

			// Voeg gegevens toe aan database
			$insert = "INSERT INTO Persons (
				ID,
				RecordCreated,
				RecordCreatedBy,
				RecordEditDate,
				RecordEditedBy,
				NamePrefix,
				FamilyName,
				GivenNames,
				NameSuffix,
				AdditionalNames)
			VALUES (
				$personid,
				'$recordcreated',
				$recordcreatedbyid,
				'$recordeditdate',
				$recordeditedbyid,
				'$nameprefix',
				'$familyname',
				'$givenname',
				'$namesuffix',
				'$additionalnames')";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());

			// Schrijf data weg naar PersonToOrganisations tabel
			$insert = "INSERT INTO PersonToOrganisations (
				ID,
				PersonID,
				OrganisationID,
				RoleID,
				PostalAddressText,
				ZipCode,
				Town,
				Region,
				CountryID,
				JobTitleID,
				Telephone,
				Fax,
				Email)
			VALUES (
				$e,
				$personid,
				$organisationid,
				$personroleid,
				'$personpostaladdresstext',
				'$personzip',
				'$persontown',
				'$personregion',
				151,
				'$jobtitleid',
				'$persontelephone',
				'$personfax',
				'$personemail')";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
	
			$insert = "INSERT INTO JobTitles (PersonToOrganisationID, JobTitle, LanguageID) VALUES ($e, '$jobtitle', 14)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
					
			// Schrijf data weg naar PersonRoleToPerson tussentabel
			$insert = "INSERT INTO PersonRoleToPerson (PersonID, PersonRoleID) VALUES ($personid, $personroleid)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		}
		
		// Leeg variabelen die gebruikt worden voor array check
		$thispersonrole = "";
		$addtopersons = FALSE;
	}

	echo "Added $e persons<br>";
	flush();

	// Vul PersonRoles tabel met gegevens uit $personroles array
	foreach ($personroles as $role)
	{
		mysql_db_query($metadatabase, "INSERT INTO PersonRoles (Role, LanguageID) VALUES ('$role', 14)") or die(mysql_error());
	}
	$number_of_personroles = count($personroles);
	echo "Added $number_of_personroles roles for persons<br>";
	flush();
	
	// Vul JobTitles tabel met gegevens uit $jobtitles array
	/*foreach ($jobtitles as $jobtitle)
	{
		mysql_db_query($metadatabase, "INSERT INTO JobTitles (JobTitle, LanguageID) VALUES ('$jobtitle', 14)") or die(mysql_error());
	}
	$number_of_jobtitles = count($jobtitles);
	echo "Added $number_of_jobtitles job titles<br>";
	flush();*/
	
	// Vul Users tabel met gegevens uit $users array
	echo "<br><b>Compiling user data...</b><br>";
	flush();

	// Voeg Tekla en Huub toe aan array
	array_push ($users, "tekla");
	array_push ($users, "huub");
	foreach ($users as $username)
	{
		mysql_db_query($metadatabase, "INSERT INTO Users (UserName) VALUES ('$username')") or die(mysql_error());
	}
	$number_of_users = count($users);
	echo "Added $number_of_users users<br><br>";
	flush();
	
	// Collections koppelen aan organisaties
	echo "<b>Connecting collections to organisations...</b><br>";
	flush();
	for ($i = 0; $i < count($organisations["id"]); $i++)
	{	
		$organisationid = $organisations["id"][$i];
		$institutename = $organisations["name"][$i];
		$subunitname = $organisations["subname"][$i];
		
		mysql_select_db($noditdatabase, $conn) or die(mysql_error());
		$query = "SELECT t1.body_id FROM body t1, address t2 WHERE t1.address_fk = t2.address_id AND t1.body_type_fk = 3 AND t2.institution_name LIKE '$institutename' AND t2.sub_unit_name LIKE '$subunitname'" or die(mysql_error());
		$result = mysql_query($query);
		while($row = mysql_fetch_array($result))
		{
			$f++;
			$collectionid = $row["body_id"];
			$insert = "INSERT INTO CollectionsToOrganisations (CollectionID, OrganisationID) VALUES ($collectionid, $organisationid)";
			mysql_db_query($metadatabase, $insert) or die(mysql_error());
		}
	}
	echo "Connected $f collections to $d organisations<br><br>";
	flush();
	
	// Resultaten personen naar organisaties
	echo "<b>Connecting persons to organisations...</b><br>";
	echo "Connected $e persons to ".count($count_persontoorganisations)." organisations<br><br>";
	
	
	// Personen koppelen aan collecties
	echo "<b>Connecting persons to collections...</b><br>";
	$count_personstocollections = count($persontocollections["personid"]);
	for ($i = 0; $i < $count_personstocollections; $i++)
	{	
		$personid = $persontocollections["personid"][$i];
		$collectionid = $persontocollections["collectionid"][$i];
		$insert = "INSERT INTO PersonToCollections (PersonID, CollectionID) VALUES ($personid, $collectionid)";
		mysql_db_query($metadatabase, $insert) or die(mysql_error());
	}
	echo "Connected $count_personstocollections persons to ".($count_personstocollections + $h)." collections<br><br>";
	flush();
	
	echo "<b>Post-processing new metadatabase</b><br>";
	echo "Removing duplicate organisations...<br>";
	flush();
	// Array met te verwijderen organisaties ($removearray). In deze multidimensionale array staan
	// 1. Het te verwijderen OrganisationID, 2. het OrganisationID waarnaar in de tussentabellen verwezen moet worden//
	// en 3. de persoon die aan de te verwijderen organisatie gekoppeld was en dus overzet moet worden naar het id 2.
	include("remove_organisations.inc.php");	
	for ($i = 0; $i < count($removearray["removeid"]); $i++)
	{
		$removeid = $removearray["removeid"][$i];
		$copytoid = $removearray["copytoid"][$i];
		$personid = $removearray["personid"][$i];
		
		$delete = "DELETE FROM OrganisationClassesToOrganisation WHERE OrganisationID = $removeid";
		mysql_db_query($metadatabase, $delete) or die(mysql_error());
		
		$delete = "DELETE FROM OrganisationDataByLanguage WHERE OrganisationID = $removeid";
		mysql_db_query($metadatabase, $delete) or die(mysql_error());
		
		$delete = "DELETE FROM Organisations WHERE ID = $removeid";
		mysql_db_query($metadatabase, $delete) or die(mysql_error());
		
		$update = "UPDATE CollectionsToOrganisations SET OrganisationID = $copytoid WHERE OrganisationID = $removeid";
		mysql_db_query($metadatabase, $update) or die(mysql_error());
		
		if ($personid != "" && $personid != "\n")
		{
			$update = "UPDATE PersonToOrganisations SET OrganisationID = $copytoid WHERE PersonID = $personid AND OrganisationID = $removeid";
			mysql_db_query($metadatabase, $update) or die(mysql_error());
		}
	}
	echo "Removed ".count($removearray["removeid"])." duplicate organisations<br>";
	flush();

	echo "Renaming organisations (with collection names)...<br>";
	flush();
	// $renamearray vergelijkbaar met hierboven. 1. Organisation ID, 2. Nieuwe naam, 3. Nieuwe subunit naam
	include("rename_organisations.inc.php");	
	for ($i = 0; $i < count($renamearray["renameid"]); $i++)
	{
		$renameid = $renamearray["renameid"][$i];
		$name = $renamearray["name"][$i];
		$subunitname = $renamearray["subunitname"][$i];
		
		$update = "UPDATE OrganisationDataByLanguage SET Name = '$name', SubUnitName = '$subunitname' WHERE OrganisationID = $renameid AND LanguageID = 47";
		mysql_db_query($metadatabase, $update) or die(mysql_error());
		
		$update = "UPDATE OrganisationDataByLanguage SET Name = '', SubUnitName = '' WHERE OrganisationID = $renameid AND LanguageID = 14";
		mysql_db_query($metadatabase, $update) or die(mysql_error());
	}
	echo "Renamed ".count($renamearray["renameid"])." organisations<br><br>";
	flush();

	$query = "SELECT ID, Street, Pcode, Locality FROM organisations WHERE Pobox = '' AND Street != '' AND VisitorPcode = ''";
	$result = mysql_query($query);
	while($row = mysql_fetch_array($result))
	{
		$id = $row["ID"];
		$street = addslashes($row["Street"]);
		$Pcode = $row["Pcode"];
		$postallocality = addslashes($row["Locality"]);
		$update = "UPDATE Institutions SET VisitorStreet = '$Street', VisitorPcode = '$Pcode', VisitorLocality = '$postallocality'
					WHERE ID = $id";
		mysql_query($update) or die(mysql_error());
	}
?>
</body>
</html>
Return current item: NCD Toolkit