<?php
define("DB_ERROR_REPORTING", true);
define("FOR_REAL", true);

require_once("../../common/common_global.php");
$filename = "/export/WWW/bnhra.org/site/sql/reporting_dictionary.xml";
$table_names = array();
$inserts = array();
$search_prefix = "reporting";

print("<pre>");

// Open the XML Document
$doc = domxml_open_file($filename);
$root_elem = $doc->document_element();

// Enter the dictionary definitions
if (!$root_elem->get_attribute("dictionary_table")) {
	$data_dictionary = DB_NAME . ".data_dictionary";
	print("\nUsing COMS database and default data_dictionary table: $data_dictionary\n\n");
} else {
	$data_dictionary = $root_elem->get_attribute("dictionary_table");
	print("\nUsing User Defined Data Dictionary Table: $data_dictionary\n\n");
}
if (!$root_elem->get_attribute("dictionary_structure")) {
	$data_structure = DB_NAME . ".data_structure";
	print("\nUsing COMS database and default data_structure table: $data_structure\n\n");
} else {
	$data_structure = $root_elem->get_attribute("dictionary_structure");
	print("\nUsing User Defined Data Structure Table: $data_structure\n\n");
}
if (FOR_REAL) {			// Use this part to delete previous dictionary definitions, if desired
	$db->Query("DELETE FROM $data_dictionary WHERE info_name LIKE '%.$search_prefix%'");
	//$db->Query("TRUNCATE $data_structure");   //WHERE first_table LIKE '%.crm_%'");
	//$db->Query("TRUNCATE data_lookup WHERE type LIKE '");
}
$databases = $root_elem->get_elements_by_tagname("database");
foreach ($databases as $database) {
	$dbname = $database->get_attribute("name");
	print("\nTraversing $dbname...\n");
	$tables = $database->get_elements_by_tagname("table");
	foreach ($tables as $table) {
		$tablename = $table->get_attribute("name");
		if (!in_array("$dbname.$tablename", $table_names)) {
			$table_names[] = "$dbname.$tablename";
		}
		print("\tTraversing $dbname.$tablename...\n");
		$fields = $table->get_elements_by_tagname("field");
		foreach ($fields as $field) {
			$fieldname = $field->get_attribute("name");
			$inserts = array();
			$inserts['info_display_label'] = $field->get_attribute("info_display_label");
			$inserts['info_name'] = "$dbname.$tablename.$fieldname";
			$inserts['is_required'] = $field->get_attribute("is_required");
			$inserts['info_type'] = $field->get_attribute("info_type");
			$inserts['info_lookup_field'] = $field->get_attribute("info_lookup_field");
			$inserts['info_blacklist'] = $field->get_attribute("info_blacklist");
			$keys = "`" . join("`, `", array_keys($inserts)) . "`";
			foreach ($inserts as $key => $value) {
				$inserts[$key] = addslashes($value);
			}
			$values = "'" . join("', '", $inserts) . "'";
			$insert_sql = "INSERT INTO $data_dictionary ($keys) VALUES ($values)";
			if (FOR_REAL) {
				$db->Query($insert_sql);
				print("\t\tInserted $inserts[info_name] as $inserts[info_display_label]\n");
			} else {
				print("\t\tWould Have Inserted: $insert_sql\n");
			}
			// Check for relationships
			$relationships = $field->get_elements_by_tagname("relationship");
			foreach ($relationships as $relationship) {
				$inserts = array();
				$inserts['first_table'] = "$dbname.$tablename";
				$inserts['second_table'] = $relationship->get_attribute("second_table");
				$inserts['relationship'] = $relationship->get_attribute("relationship");
				$inserts['first_field'] = $fieldname;
				$inserts['second_field'] = $relationship->get_attribute("second_field");
				$inserts['display_field'] = $relationship->get_attribute("display_field");
				$keys = "`" . join("`, `", array_keys($inserts)) . "`";
				foreach ($inserts as $key => $value) {
					$inserts[$key] = addslashes($value);
				}
				$values = "'" . join("', '", $inserts) . "'";
				$insert_sql = "INSERT INTO $data_structure ($keys) VALUES ($values)";
				if (FOR_REAL) {
					$db->Query($insert_sql);
					print("\t\t\tInserted $inserts[relationship] Relationship to $inserts[second_table].$inserts[second_field]\n");
				} else {
					print("\t\t\tWould Have Inserted: $insert_sql\n");
				}
			}
		}
	}
}

// Add the CSV file of table names
if (FOR_REAL) {
	$db_names = $db->GetRow(false, "SELECT db_name_or_names_csv AS table_names FROM coms_setup");
	$old_tables = split(",", $db_names['table_names']);   // Use this one to append
	//$old_tables = array();   // Use this one to overwrite
	$both = array_merge($old_tables, $table_names);
	$union = array_unique($both);
	$db_names = join(",", $union);
	print("\nInserting CSV list of tables into coms_setup table:\n");
	pretty_print_r($union);
	$db->Query("UPDATE coms_setup SET db_name_or_names_csv = '$db_names' WHERE 1");
}

// Insert the data lookups
if (!$root_elem->get_attribute("data_lookup_table")) {
	$data_lookup_table = DB_NAME . ".data_lookup";
	print("\nUsing COMS database and default data_lookup table: $data_lookup_table\n");
} else {
	$data_lookup_table = $root_elem->get_attribute("data_lookup_table");
	print("\nUsing User Defined Data Lookup Table: $data_lookup_table\n");
}
$data_lookups = $root_elem->get_elements_by_tagname("data_lookup");
print("\nInserting Data Lookups...\n");
foreach ($data_lookups as $data_lookup) {
	$types = $data_lookup->get_elements_by_tagname("type");
	foreach ($types as $type) {
		$typename = $type->get_attribute("name");
		print("\tTraversing $typename lookup type...\n");
		$options = $type->get_elements_by_tagname("option");
		foreach ($options as $option) {
			$inserts = array();
			$inserts['value'] = $option->get_attribute('value');
			$inserts['display'] = $option->get_attribute('display');
			$inserts['disp_order'] = $option->get_attribute('disp_order');
			$inserts['type'] = $typename;
			$keys = "`" . join("`, `", array_keys($inserts)) . "`";
			foreach ($inserts as $key => $value) {
				$inserts[$key] = addslashes($value);
			}
			$values = "'" . join("', '", $inserts) . "'";
			$insert_sql = "INSERT INTO $data_lookup_table ($keys) VALUES ($values)";
			if (FOR_REAL) {
				$db->Query($insert_sql);
				print("\t\tInserted $inserts[display] into $inserts[type] type.\n");
			} else {
				print("\t\tWould Have Inserted: $insert_sql\n");
			}
		}
	}
}

print("</pre>");