<?php

/* Quick and dirty command line PHP program to create a master table
   for homework #5 of CSCI 5333.4 Fall 2011. Will be uesd only once and
   thus is lightly documented and not optimized.
*/

include('dbconfig.php');

function keyValue($string, $arr)
{	return (array_key_exists($string, $arr)? $arr[$string] : "\\N");
}

function keyDbValue($string, $arr)
{	return (array_key_exists($string, $arr)? $arr[$string] : null);
}

/* use data of Primary education table to populate master array. */
if ($result = $mysqli->query('SELECT country, year, gender, prim_number FROM f1h5_primary')) {    /* Fetch results */
    while ( $row = $result->fetch_assoc() ){
		if ($row['gender'] == 'F') {
			$data[$row['country']][$row['year']]['female_primary'] = $row['prim_number'];
		}
		else {
			$data[$row['country']][$row['year']]['all_primary'] = $row['prim_number'];
		}
    }
    /* Destroy the result set and free the memory used for it */
    $result->close();
}

/* use data of Secondary education table to populate master array. */
if ($result = $mysqli->query('SELECT country, year, gender, second_percent FROM f1h5_secondary')) {    /* Fetch results */
    while ( $row = $result->fetch_assoc() ){
		if ($row['gender'] == 'F') {
			$data[$row['country']][$row['year']]['female_secondary'] = $row['second_percent'];
		}
		else if ($row['gender'] == 'M') {
			$data[$row['country']][$row['year']]['male_secondary'] = $row['second_percent'];
		} else {
			$data[$row['country']][$row['year']]['all_secondary'] = $row['second_percent'];		
		}
    }
    /* Destroy the result set and free the memory used for it */
    $result->close();
}

/* use data of Tertiary education table to populate master array. */
if ($result = $mysqli->query('SELECT country, year, gender, tert_number FROM f1h5_tertiary')) {    /* Fetch results */
    while ( $row = $result->fetch_assoc() ){
		if ($row['gender'] == 'F') {
			$data[$row['country']][$row['year']]['female_tertiary'] = $row['tert_number'];
		}
		else {
			$data[$row['country']][$row['year']]['all_tertiary'] = $row['tert_number'];
		}
    }
    /* Destroy the result set and free the memory used for it */
    $result->close();
}

/* use data of GNI table to populate master array. */
if ($result = $mysqli->query('SELECT country, year, gni_percent FROM f1h5_gni')) {    /* Fetch results */
    while ( $row = $result->fetch_assoc() ){
		$data[$row['country']][$row['year']]['gni'] = $row['gni_percent'];
    }
    /* Destroy the result set and free the memory used for it */
    $result->close();
}

/* use data of expenditure table to populate master array. */
if ($result = $mysqli->query('SELECT country, year, exp_percent FROM f1h5_expenditure')) {    /* Fetch results */
    while ( $row = $result->fetch_assoc() ){
		$data[$row['country']][$row['year']]['expenditure'] = $row['exp_percent'];
    }
    /* Destroy the result set and free the memory used for it */
    $result->close();
}
/* print result in a pipe separated list. */
$myFile = "h5out.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, "Country|Year|N Female Primary|N All Primary|% Female Secondary|% Male Secondary|% All Secondary|N Female Tertiary|N All Tertiary|GNI|Expenditure\n");

/*  Populate result in the master table */
$query = <<<__QUERY
INSERT INTO f1h5 (country, year, female_primary, all_primary, female_secondary, male_secondary, 
   all_secondary,female_tertiary, all_tertiary, gni_percent, exp_percent)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
__QUERY;
if ($stmt = $mysqli->prepare($query))

foreach($data as $country => $dValue) {
      foreach ($dValue as $year => $array) {

	  /* print result in a pipe separated list. */
		$female_primary = keyValue('female_primary', $array);
		$all_primary = keyValue('all_primary', $array);
		$female_secondary = keyValue('female_secondary', $array);
		$male_secondary = keyValue('male_secondary', $array);
		$all_secondary = keyValue('all_secondary', $array);
		$female_tertiary = keyValue('female_tertiary', $array);
		$all_tertiary = keyValue('all_tertiary', $array);
		$gni = keyValue('gni', $array);
		$expenditure = keyValue('expenditure', $array);
		
		fwrite($fh, "$country|$year|$female_primary|$all_primary|$female_secondary|$male_secondary|$all_secondary|$female_tertiary|$all_tertiary|$gni|$expenditure\n");

		/*  Populate result in the master table */
		$female_primary = keyDbValue('female_primary', $array);
		$all_primary = keyDbValue('all_primary', $array);
		$female_secondary = keyDbValue('female_secondary', $array);
		$male_secondary = keyDbValue('male_secondary', $array);
		$all_secondary = keyDbValue('all_secondary', $array);
		$female_tertiary = keyDbValue('female_tertiary', $array);
		$all_tertiary = keyDbValue('all_tertiary', $array);
		$gni = keyDbValue('gni', $array);
		$expenditure = keyDbValue('expenditure', $array);
		
		$stmt->bind_param('siiidddiidd', $country, $year, $female_primary, $all_primary, $female_secondary, $male_secondary, $all_secondary, $female_tertiary, $all_tertiary, $gni, $expenditure);
		$stmt->execute();
	}
}
fclose($fh);
$mysqli->close();
?>