<?php
/*********************************************************************************************************
  "Ensemble" is the proprietary property of The Regents of the University of California ("The Regents.")

  Copyright (c) 2005-10 The Regents of the University of California, Davis campus. All Rights Reserved.

  Redistribution and use in source and binary forms, with or without modification, are permitted by
  nonprofit, research institutions for research use only, provided the conditions in the included
  license agreement are met.

  Refer to the file "ensemble_license.txt" for the license agreement,
  located in the top level directory of this distribution.

**********************************************************************************************************/
//
// This utility exports records associated with an experiment to an SQL text file, so that the data can
// be imported to another ensemble database
//
// NOTE: doesn't export trials from the trial table yet. This should be an easy add-on
//
//Author(s): Stefan Tomic
//Janata Lab
//Center for Mind and Brain
//Developed for the Ensemble Project
//
// Aug. 3 2009, ST - initial version


include_once('../include/researcher_includes.php');

$_SESSION['editor_calling_page'] = $_SERVER['PHP_SELF'];
$_SESSION['editor_title'] = "Export Experiment";

function interpret_fieldtype($type) {
// interprets the mysql type and returns a type string 
// that can be interpreted by the GetSQLValueString function

	if(strncmp($type,"int",3) == 0)
		return "int";
	elseif(strncmp($type,"varchar",7) == 0)
		return "text";
	elseif(strcmp($type,"date") == 0)
		return "date";
	elseif(strcmp($type,"time") == 0)
		return "time";
	elseif(strncmp($type,"enum",4) == 0)
		return "text";
	elseif(strcmp($type,"float") == 0)
		return "float";
	elseif(strcmp($type,"double") == 0)
		return "double";
	else
		return "text";

}


function dump_table()
{

	$numargs = func_num_args();
	$arglist = func_get_args();

	for ($iarg = 0; $iarg < $numargs; $iarg += 2) {
		switch($arglist[$iarg]) {
			case 'table':
				$table_name = $arglist[$iarg+1];
				break;
			case 'key_fld':
				$key_fld = $arglist[$iarg+1];
				break;
			case 'key_val':
				$key_val = $arglist[$iarg+1];
				break;
			case 'get_vals':
				$get_val_fieldname =  $arglist[$iarg+1];
				break;
			default:
				echo sprintf("function dump_table: unrecognized tag '%s'",$arglist[$iarg+1]);
		
		}
	}

	$sql_get_fieldnames = sprintf('describe `%s`',$table_name);
	$result_get_fieldnames = mysql_query($sql_get_fieldnames) or die(mysql_error());
	while($row_get_fieldnames = mysql_fetch_assoc($result_get_fieldnames)) {
		$fld = $row_get_fieldnames['Field'];
		$fieldnames[] = $fld;
		$quoted_fieldnames[] = '`' . $fld . '`';
		$types[$fld] = interpret_fieldtype($row_get_fieldnames['Type']);
	}
	
	if(isset($key_fld))
		$sql_get_records = sprintf('select * from `%s` where `%s` in (%s)',$table_name,$key_fld,$key_val);
	else
		$sql_get_records = sprintf('select * from `%s`',$table_name);
		
	$result_records = mysql_query($sql_get_records) or die(mysql_error());


	while($next_row = mysql_fetch_assoc($result_records)) {
		unset($vals);
		//we could just assume that the fields are going to be in the same order as listed in $fieldnames, but
		//it's better to be safe about this.
		foreach($fieldnames as $field) {
			$new_col = addslashes($next_row[$field]);
			$vals[] = GetSQLValueString($new_col,$types[$field]);
		}
		
		//$insert_vals contains the values of all the fields for a single record
		$insert_vals[] = '(' . implode(',',$vals) . ')';
		if(isset($get_val_fieldname)) {
			$new_val = GetSQLValueString($next_row[$get_val_fieldname],$types[$get_val_fieldname]);
			if(!isset($get_val_array) || !in_array($new_val,$get_val_array))
				$get_val_array[] = $new_val;
		}
	}
	
	// using insert ignore here, so that if a duplicate key exists, it won't be overwritten, but the query will still proceed and a warning will be issued instead
	$sql_insert = 'insert ignore into `' . $table_name . '` (' . implode(',',$quoted_fieldnames) . ') values ' . implode(',',$insert_vals) . ';';

	if(isset($get_val_fieldname)) {
		$get_val_array = array_unique($get_val_array);
		$return_vals = implode(',',$get_val_array);
		return array($sql_insert,$return_vals);
	}
	else
		return $sql_insert;
}

//unless "Export" button was pressed, send the HTTP headers
if(!isset($_POST['action']) || ($_POST['action'] != "Submit")) {
?>
	<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
	<html>
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<style type="text/css">
	<!--
	@import url("../include/qei_stylesheet.css");
	
	-->
	</style>
	<title>Export Experiment</title>
	</head>
	
	<body>
	<h1>Ensemble Experiment Export Utility</h1>
<?php
}


if(isset($_POST['action']) && ($_POST['action'] == "Submit")) {

	$export_experiment_id = $_POST['export_experiment_id'];


	list($dump_result[],$response_table) = dump_table('table','experiment','key_fld','experiment_id','key_val',$export_experiment_id,'get_vals','response_table');
	//$response_table is one string that we don't want enclosed in single quotes so we're getting rid of them here.
	$response_table = str_replace("'","",$response_table);
	list($dump_result[],$form_ids) = dump_table('table','experiment_x_form','key_fld','experiment_id','key_val',$export_experiment_id,'get_vals','form_id');
	$dump_result[] = dump_table('table','form','key_fld','form_id','key_val',$form_ids);
	list($dump_result[],$question_ids) = dump_table('table','form_x_question','key_fld','form_id','key_val',$form_ids,'get_vals','question_id');
	$dump_result[] = dump_table('table','question','key_fld','question_id','key_val',$question_ids);
	list($dump_result[],$data_format_ids) = dump_table('table','question_x_data_format','key_fld','question_id','key_val',$question_ids,'get_vals','answer_format_id');
	$dump_result[] = dump_table('table','data_format','key_fld','data_format_id','key_val',$data_format_ids);
	
	if($_POST['export_responses']) {
		list($dump_result[],$subject_ids) = dump_table('table',$response_table,'get_vals','subject_id');
		$dump_result[] = dump_table('table','subject','key_fld','subject_id','key_val',$subject_ids);
		$dump_result[] = dump_table('table','session','key_fld','experiment_id','key_val',$export_experiment_id);
	}
	
	$filename = ($_POST['filename'] == "")? "ensemble_export.sql" : $_POST['filename'].".sql";
	text_download_header($filename);
	foreach($dump_result as $value)
		printf("%s\n\n",$value);
	
}

else {

	$sql_get_experiments = sprintf("select * from experiment");
	$experiment_name = mysql_query($sql_get_experiments) or die(mysql_error());
	
	printf("<form name=select_experiment method=POST action=\"%s\">\n",$_SERVER['PHP_SELF']);
	
	printf("<p>Select Experiment\n");
	printf("<select name=\"export_experiment_id\"  onChange=\"document.forms.select_criteria.submit()\">\n");
	
	while($row_experiment_name = mysql_fetch_assoc($experiment_name)) {
		$option_experiment_id = $row_experiment_name['experiment_id'];
		printf("<option value=%d %s>%s</option>",$option_experiment_id,($export_experiment_id == $option_experiment_id)? "selected" : "",$row_experiment_name['experiment_title']);
	}
	printf("</select></p>\n");
	
	printf("Export Responses: ");
	printf("No<input type=\"radio\" name=\"export_responses\" checked value=\"0\">");
	printf("Yes<input type=\"radio\" name=\"export_responses\" value=\"1\">");
	printf("<br>");
	printf("Filestub: <input type=\"text\" name=\"filename\">");
	
	printf("<input type=submit name=action value=\"Submit\">");
	printf("</form>\n");
}


?>