webAdministracion/processes/insertQuestionnaire.php

<?php 

	require_once 'config.php';
	require_once 'dbh.inc.php';
	require_once 'checkLogin.php';	
	
	include_once '../../funciones.php'; // para console_log()
	console_log($_POST);
	
// 	http_response_code(400);
// 	echo json_encode(array("error" => "Currently under development. \n".var_export($_POST, true)));
// 	exit();
	
	
	// EXAMPLE INPUT...
	// array(39) { ["id_experience"]=> string(1) "1" ["q_title"]=> string(1) "t" ["description"]=> string(1) "d" ["referencia"]=> string(1) "r" ["q_categories"]=> string(4) "a, b" ["q_subcategories"]=> string(10) "c, d, e, f" ["q_premise1"]=> string(2) "p1" ["q_type1"]=> string(1) "2" ["min_val1"]=> string(0) "" ["min_text1"]=> string(0) "" ["max_val1"]=> string(0) "" ["max_text1"]=> string(0) "" ["q_category1"]=> string(1) "a" ["q_subcategory1"]=> string(1) "c" ["q_premise2"]=> string(2) "p2" ["q_type2"]=> string(1) "1" ["min_val2"]=> string(1) "1" ["min_text2"]=> string(5) "mint2" ["max_val2"]=> string(1) "5" ["max_text2"]=> string(5) "maxt2" ["q_category2"]=> string(1) "a" ["q_subcategory2"]=> string(1) "d" ["q_premise3"]=> string(2) "p3" ["q_type3"]=> string(1) "1" ["min_val3"]=> string(1) "1" ["min_text3"]=> string(5) "mint3" ["max_val3"]=> string(2) "10" ["max_text3"]=> string(5) "maxt3" ["q_category3"]=> string(1) "b" ["q_subcategory3"]=> string(1) "e" ["q_premise4"]=> string(2) "p4" ["q_type4"]=> string(1) "1" ["min_val4"]=> string(1) "5" ["min_text4"]=> string(5) "mint4" ["max_val4"]=> string(1) "7" ["max_text4"]=> string(5) "maxt4" ["q_category4"]=> string(1) "b" ["q_subcategory4"]=> string(1) "f" ["createQuestionnaire"]=> string(0) "" }

	if(isset($_POST['createQuestionnaire'])) {
	
	

		/*** FETCH THE BASIC INFO OF THE NEW QUESTIONNAIRE ***/
		$experienceID = mysqli_real_escape_string($connection, trim($_POST['id_experience']));
		$questionnaireTitle = mysqli_real_escape_string($connection, trim($_POST['q_title']));
		$questionnaireDescription = mysqli_real_escape_string($connection, trim($_POST['description']));
		$questionnaireReference = mysqli_real_escape_string($connection, trim($_POST['referencia']));
		$questionnaireCategories = explode(',', mysqli_real_escape_string($connection, trim($_POST['q_categories'])));
		$questionnaireSubcategories = explode(',', mysqli_real_escape_string($connection, trim($_POST['q_subcategories'])));

	
	
		/*** (START) FIRST BLOCK OF INPUT VALIDATION ***/
	
		// Check that experienceID is not an empty string
		// And that experienceID is in the database
		if($experienceID === "") {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify experience ID."));
			exit();
		} else if(mysqli_query($connection, "SELECT * FROM experience WHERE id = '$experienceID';")->num_rows !== 1) {
			http_response_code(400);
			echo json_encode(array("error" => "Given experience ID ($experienceID) not in database."));
			exit();
		}
		
		
		// Check that questionnaireTitle is not an empty string
		// And that it doesn't exceed 100 characters (database limit)
		if($questionnaireTitle === "") {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify questionnaire title."));
			exit();
		} else if(mb_strlen($questionnaireTitle) > 100) {
			http_response_code(400);
			echo json_encode(array("error" => "Questionnaire title too long (max. is 100 characters)."));
			exit();
		}
		
		
		// Check that questionnaireDescription is not an empty string
		if($questionnaireDescription === "") {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify questionnaire description."));
			exit();
		} else if(mb_strlen($questionnaireDescription) > 200) {
			http_response_code(400);
			echo json_encode(array("error" => "Questionnaire description too long (max. is 200 characters)."));
			exit();
		}
		
		
		// Check that questionnaireReference is not an empty string
		if($questionnaireReference === "") {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify questionnaire reference."));
			exit();
		} else if(mb_strlen($questionnaireReference) > 60) {
			http_response_code(400);
			echo json_encode(array("error" => "Questionnaire reference too long (max. is 60 characters)."));
			exit();
		}
		
		
		// Check that questionnaireCategories is not an empty string (explode returns FALSE if empty string)
		// And that user has at least one category
		// WARNING: "   , , ,,   " goes through
		if($questionnaireCategories === FALSE || count($questionnaireCategories) === 0) {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify questionnaire categories (go back with 'Previous' button)."));
			exit();
		}
		
		
		// Check that questionnaireSubcategories is not an empty string (explode returns FALSE if empty string)
		// And that user have at least one subcategory
		// WARNING: "   , , ,,   " goes through
		if($questionnaireSubcategories === FALSE || count($questionnaireSubcategories) === 0) {
			http_response_code(400);
			echo json_encode(array("error" => "Please specify questionnaire subcategories (go back with 'Previous' button)."));
			exit();		
		}
		
		/*** (END) FIRST BLOCK OF INPUT VALIDATION ***/
		
		
		
		

		/*** CLEAN UP CATEGORIES STRING ***/
		// Validate (make sure every category is not an empty string)
		// REMINDER: Here we can deal with "   , , ,,   "
		for($i = 0; $i < count($questionnaireCategories); $i++) {
		
			$questionnaireCategories[$i] = trim($questionnaireCategories[$i]);	
			
			// Check that none of the categories are empty strings
			if($questionnaireCategories[$i] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid category (category #".($i+1).")."));
				exit();
			} else if(mb_strlen($questionnaireCategories[$i]) > 60) {
				http_response_code(400);
				echo json_encode(array("error" => "Category '".$questionnaireCategories[$i]."' too long (max. is 60 characters)."));
				exit();
			}

		}


		
		/*** CLEAN UP SUBCATEGORIES STRING ***/
		// Validate (make sure every subcategory is not an empty string)
		// REMINDER: Here we can deal with "   , , ,,   "
		for($i = 0; $i < count($questionnaireSubcategories); $i++) {
		
			$questionnaireSubcategories[$i] = trim($questionnaireSubcategories[$i]);
			
			// Check that none of the subcategories are empty strings
			if($questionnaireSubcategories[$i] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid subcategory (subcategory #".($i+1).")."));
				exit();
			} else if(mb_strlen($questionnaireSubcategories[$i]) > 60) {
				http_response_code(400);
				echo json_encode(array("error" => "Subcategory '".$questionnaireSubcategories[$i]."' too long (max. is 60 characters)."));
				exit();
			}
			
		}


		
		// Check that the number of subcategories is greater or equal than that of categories
		if(count($questionnaireSubcategories) < count($questionnaireCategories)) {
			http_response_code(400);
			echo json_encode(array("error" => "The number of subcategories must be greater or equal than the number of categories (go back with 'Previous' button)."));
			exit();	
		}




		/*** INITIALIZE OTHER VARIABLES ***/
		$questionPremises = array();
		$questionTypes = array();
		$questionMinVal = array();
		$questionMinText = array();
		$questionMaxVal = array();
		$questionMaxText = array();
		$questionCategories = array();
		$questionSubcategories = array();
		$numberOfQuestions = 0;



		/*** STORE THE QUESTIONS' DATA ***/
		/*** FINAL BLOCK OF INPUT VALIDATION ***/
		// WARNING: $start IS VERY SENSITIVE TO NUMBER OF INPUTS GIVEN //
		// REMINDER: THE -1 IS FOR THE createQuestionnaire SUBMIT BUTTON //
		$start = 6;
		for($i = $start; $i < count($_POST) - 1 - $start; $i += 8) {
		
			$questionPremises[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i]));
			$questionTypes[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+1]));
			$questionMinVal[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+2]));
			$questionMinText[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+3]));
			$questionMaxVal[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+4]));
			$questionMaxText[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+5]));
			$questionCategories[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+6]));
			$questionSubcategories[] = mysqli_real_escape_string($connection, trim(array_values($_POST)[$i+7]));
			$numberOfQuestions++;
			
			
			// Check that none of the premises are empty strings
			if($questionPremises[$numberOfQuestions - 1] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid premise (in Question #$numberOfQuestions)."));
				exit();
			} else if(mb_strlen($questionPremises[$numberOfQuestions - 1]) > 600) {
				http_response_code(400);
				echo json_encode(array("error" => "Premise too long (max. is 600 characters) (in Question #$numberOfQuestions)."));
				exit();
			}
			
			
			// Check that none of the assigned categories are empty strings
			if($questionCategories[$numberOfQuestions - 1] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid category (in Question #$numberOfQuestions)."));
				exit();
			} else if(mb_strlen($questionCategories[$numberOfQuestions - 1]) > 60) {
				http_response_code(400);
				echo json_encode(array("error" => "Category '".$questionCategories[$numberOfQuestions - 1]."' too long (max. is 60 characters) (in Question #$numberOfQuestions)."));
				exit();
			}
			

			// Check that none of the assigned subcategories are empty strings
			if($questionSubcategories[$numberOfQuestions - 1] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid subcategory (in Question #$numberOfQuestions)."));
				exit();
			} else if(mb_strlen($questionSubcategories[$numberOfQuestions - 1]) > 60) {
				http_response_code(400);
				echo json_encode(array("error" => "Subcategory '".$questionSubcategories[$numberOfQuestions - 1]."' too long (max. is 60 characters) (in Question #$numberOfQuestions)."));
				exit();
			}

			
			// Check that none of the types are empty strings
			// If the question type is scaled (e.g. value of "1"), make sure all the min/max settings are valid
			// If the question type is open, we don't have to check for min/max settings
			// If the question type is neither (invalid; nor "1" nor "2"), let user know (user probably tampered with client-side)
			if($questionTypes[$numberOfQuestions - 1] === "") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a type (1 for scaled, 2 for open) (in Question #$numberOfQuestions)."));
				exit();
			} else if($questionTypes[$numberOfQuestions - 1] === "1") {
			
			
				// Check if minVal is equal to 1 (we decided it should be like that)
				if($questionMinVal[$numberOfQuestions - 1] !== "1") {
					http_response_code(400);
					echo json_encode(array("error" => "Minimum value has to be 1 (in Question #$numberOfQuestions)."));
					exit();
				}


				// Check if minText is not an empty string
				if($questionMinText[$numberOfQuestions - 1] === "") {
					http_response_code(400);
					echo json_encode(array("error" => "Please specify a valid minimum text (in Question #$numberOfQuestions)."));
					exit();
				} else if(mb_strlen($questionMinText[$numberOfQuestions - 1]) > 40) {
					http_response_code(400);
					echo json_encode(array("error" => "Minimum text '".$questionMinText[$numberOfQuestions - 1]."' too long (max. is 40 characters) (in Question #$numberOfQuestions)."));
					exit();
				}
				
				
				// Check if maxVal is greater or equal to 2 (we decided it should be like that)
				// REMINDERS:
					// is_numeric() ensures the string is a number
					// intval() returns truncates "starting numeric-like" numbers (e.g. 1234asdf is 1234)
					// intval() returns 0 if it detects "normal string" (e.g. asdf1234 is 0)
				if(!is_numeric($questionMaxVal[$numberOfQuestions - 1])) {
					http_response_code(400);
					echo json_encode(array("error" => "Maximum value has to be numeric (in Question #$numberOfQuestions)."));
					exit();
				} else if(intval($questionMaxVal[$numberOfQuestions - 1]) < 2) {
					http_response_code(400);
					echo json_encode(array("error" => "Maximum value has to be greater or equal to 2 (in Question #$numberOfQuestions)."));
					exit();
				}
				
				
				// Check if maxText is not an empty string
				if($questionMaxText[$numberOfQuestions - 1] === "") {
					http_response_code(400);
					echo json_encode(array("error" => "Please specify a valid maximum text (in Question #$numberOfQuestions)."));
					exit();
				} else if(mb_strlen($questionMaxText[$numberOfQuestions - 1]) > 40) {
					http_response_code(400);
					echo json_encode(array("error" => "Maximum text '".$questionMaxText[$numberOfQuestions - 1]."' too long (max. is 40 characters) (in Question #$numberOfQuestions)."));
					exit();
				}
				
				
				// Check that maxText and minText are different strings
				if(mb_strtolower($questionMaxText[$numberOfQuestions - 1]) === mb_strtolower($questionMinText[$numberOfQuestions - 1])) {
					http_response_code(400);
					echo json_encode(array("error" => "Labels must be different (in Question #$numberOfQuestions)."));
					exit();
				}
				
			
			} else if($questionTypes[$numberOfQuestions - 1] !== "2") {
				http_response_code(400);
				echo json_encode(array("error" => "Please specify a valid type (1 for scaled, 2 for open) (in Question #$numberOfQuestions)."));
				exit();
			}
			
		}




		// Check that all the categories are used
		for($i = 0; $i < count($questionnaireCategories); $i++) {
		
			if(!in_array($questionnaireCategories[$i], $questionCategories)) {
				http_response_code(400);
				echo json_encode(array("error" => "Category '" . $questionnaireCategories[$i] . "' is unused."));
				exit();
			}
		
		}



		// Check that all the subcategories are used
		for($i = 0; $i < count($questionnaireSubcategories); $i++) {
		
			if(!in_array($questionnaireSubcategories[$i], $questionSubcategories)) {
				http_response_code(400);
				echo json_encode(array("error" => "Subcategory '" . $questionnaireSubcategories[$i] . "' is unused."));
				exit();
			}
		
		}



		/*** (END) FINAL BLOCK OF INPUT VALIDATION ***/




		
		/*** START OF DB QUERIES ***/
// 		exit();
// 		while(TRUE) {
// 			exit();
// 		}
// 		exit();
		/*** IF IT GOT THROUGH VALIDATION, IT'S TOO LATE ***/



		
		
		/*** CREATE QUESTIONNAIRE, RETRIEVE ITS ID (TO USE IT IN TABLES experience_questionnair AND questionnair_question) AND HOOK TO CURRENT EXPERIENCE ***/
		$queryQuestionnaire = "INSERT INTO questionnair (`q_title`, `description`) VALUES ('$questionnaireTitle', '$questionnaireDescription');";
		if(!mysqli_query($connection, $queryQuestionnaire)) {
			http_response_code(400);
			die(json_encode(array("error" => "Error: Couldn't create questionnaire<br>".mysqli_error($connection)."<br>Query: $queryQuestionnaire")));
		}

		$questionnaireID = mysqli_insert_id($connection) or die(json_encode(array("error" => "Error: Couldn't fetch questionnaire id after creating it<br>".mysqli_error($connection))));

		$queryHookQuestionnaireToExperience = "INSERT INTO experience_questionnair (`id_experience`, `id_questionnair`) VALUES ('$experienceID', '$questionnaireID');";
		if(!mysqli_query($connection, $queryHookQuestionnaireToExperience)) {
			http_response_code(400);
			die(json_encode(array("error" => "Error: Couldn't hook questionnaire to experience<br>".mysqli_error($connection)."<br>Query: $queryHookQuestionnaireToExperience")));
		}



		/*** CREATE REFERENCE AND RETRIEVE ITS ID (TO USE IT IN TABLE question) ***/
		$queryReference = "INSERT INTO reference (`referencia`) VALUES ('$questionnaireReference');";
		if(!mysqli_query($connection, $queryReference)) {
			http_response_code(400);
			die(json_encode(array("error" => "Error: Couldn't create questionnaire reference<br>".mysqli_error($connection)."<br>Query: $queryReference")));
		}

		$referenceID = mysqli_insert_id($connection) or die(json_encode(array("error" => "Error: Couldn't fetch reference id after creating it<br>".mysqli_error($connection))));



		/*** CREATE CATEGORIES ONE BY ONE AND RETRIEVE THEIR RESPECTIVE IDs (TO USE IN TABLES subcategory AND question) ***/
		for($i = 0; $i < count($questionnaireCategories); $i++) {
			$queryCategory = "INSERT INTO category (`categoria`) VALUES ('".$questionnaireCategories[$i]."');";
			if(!mysqli_query($connection, $queryCategory)) {
				http_response_code(400);
				die(json_encode(array("error" => "Error: Couldn't create category '".$questionnaireCategories[$i]."'<br>".mysqli_error($connection)."<br>Query: $queryCategory")));
			}

			$categoryIDs[$questionnaireCategories[$i]] = mysqli_insert_id($connection) or die(json_encode(array("error" => "Error: Couldn't fetch category id ('".$questionnaireCategories[$i]."') after creating it<br>".mysqli_error($connection))));
		}

	console_log($categoryIDs);

		/*** CREATE ASSOCIATIVE ARRAY THAT GOES FROM SUBCATEGORY NAME TO ITS CORRESPONDING CATEGORY'S ID ***/
		// WARNING: ESTO ES ASUMIENDO QUE LA RELACIÓN SUBCATEGORY A CATEGORY ES UNA FUNCIÓN (una subcategoría corresponde solamente a una categoría)
		for($i = 0; $i < count($questionnaireCategories); $i++) {
			for($j = 0; $j < count($questionnaireSubcategories); $j++) {
				for($k = 0; $k < $numberOfQuestions; $k++) {
					if($questionSubcategories[$k] == $questionnaireSubcategories[$j] && $questionCategories[$k] == $questionnaireCategories[$i]) {
						$linkSubcategoryToCategoryID[$questionnaireSubcategories[$j]] = $categoryIDs[$questionnaireCategories[$i]];
					}
				}
			}
		}

	console_log($linkSubcategoryToCategoryID);

		/*** CREATE SUBCATEGORIES ONE BY ONE AND RETRIEVE THEIR RESPECTIVE IDs (TO USE IN TABLE question) ***/
		// NOTE: $subcategoryIDs and $questionnaireSubcategories compartirán el mismo índice
		for($i = 0; $i < count($questionnaireSubcategories); $i++) {
			$querySubcategory = "INSERT INTO subcategory (`subcategoria`, `id_categoria`) VALUES ('".$questionnaireSubcategories[$i]."', '".$linkSubcategoryToCategoryID[$questionnaireSubcategories[$i]]."');";
			if(!mysqli_query($connection, $querySubcategory)) {
				http_response_code(400);
				die(json_encode(array("error" => "Error: Couldn't create subcategory '".$questionnaireSubcategories[$i]."', which is linked to category ID ".$linkSubcategoryToCategoryID[$questionnaireSubcategories[$i]]."<br>".mysqli_error($connection)."<br>Query: $querySubcategory<br>Dump: \n".var_export($linkSubcategoryToCategoryID, true))));
			}

			$subcategoryIDs[$questionnaireSubcategories[$i]] = mysqli_insert_id($connection) or die(json_encode(array("error" => "Error: Couldn't fetch subcategory id after creating it ('".$questionnaireSubcategories[$i]."', which is linked to category ID ".$linkSubcategoryToCategoryID[$questionnaireSubcategories[$i]].")<br>".mysqli_error($connection))));
		}
		

		
// DO WE HAVE TO DO SOMETHING FOR OPEN ANSWER CASE? I SEE THERE'S NO OPEN ANSWER QUESTION THAT IS STORED IN THE TABLE question
		/*** CREATE QUESTIONS ONE BY ONE, RETRIEVE THEIR RESPECTIVE IDs (TO USE IN TABLES question_type AND questionnair_question), ENTER THE MIN/MAX VALUES/TEXTS, AND HOOK QUESTION TO QUESTIONNAIR ***/
		for($i = 0; $i < $numberOfQuestions; $i++) {
				
			$queryQuestion = "INSERT INTO question (`premise`, `id_category`, `id_subcategory`, `id_type`, `id_referencia`) VALUES ('".$questionPremises[$i]."', '".$categoryIDs[$questionCategories[$i]]."', '".$subcategoryIDs[$questionSubcategories[$i]]."', '".$questionTypes[$i]."', '$referenceID');";
			if(!mysqli_query($connection, $queryQuestion)) {
				http_response_code(400);
				die(json_encode(array("error" => "Error: Couldn't insert question '".$questionPremises[$i]."'<br>".mysqli_error($connection)."<br>Query: $queryQuestion")));
			}

 			$questionID = mysqli_insert_id($connection) or die(json_encode(array("error" => "Error: Couldn't fetch question ID (from '".$questionPremises[$i]."')<br>".mysqli_error($connection))));

			// IF QUESTION IS SCALED, INSERT MIN/MAX VALUES/TEXT
			if($questionTypes[$i] == '1') {
		
				$queryMinVal = "INSERT INTO question_type (`id_type`, `id_question`, `label`, `value`) VALUES ('".$questionTypes[$i]."', '$questionID', 'min_val', '".$questionMinVal[$i]."');";
				if(!mysqli_query($connection, $queryMinVal)) {
					http_response_code(400);
					die(json_encode(array("error" => "Error: Couldn't insert question minimum value (from '".$questionPremises[$i]."')<br>".mysqli_error($connection)."<br>Query: $queryMinVal")));
				}

				$queryMinText = "INSERT INTO question_type (`id_type`, `id_question`, `label`, `value`) VALUES ('".$questionTypes[$i]."', '$questionID', 'min_texto', '".$questionMinText[$i]."');";
				if(!mysqli_query($connection, $queryMinText)) {
					http_response_code(400);
					die(json_encode(array("error" => "Error: Couldn't insert question minimum text (from '".$questionPremises[$i]."')<br>".mysqli_error($connection)."<br>Query: $queryMinText")));
				}

				$queryMaxVal = "INSERT INTO question_type (`id_type`, `id_question`, `label`, `value`) VALUES ('".$questionTypes[$i]."', '$questionID', 'max_val', '".$questionMaxVal[$i]."');";
				if(!mysqli_query($connection, $queryMaxVal)) {
					http_response_code(400);
					die(json_encode(array("error" => "Error: Couldn't insert question maximum value (from '".$questionPremises[$i]."')<br>".mysqli_error($connection)."<br>Query: $queryMaxVal")));
				}

				$queryMaxText = "INSERT INTO question_type (`id_type`, `id_question`, `label`, `value`) VALUES ('".$questionTypes[$i]."', '$questionID', 'max_texto', '".$questionMaxText[$i]."');";
				if(!mysqli_query($connection, $queryMaxText)) {
					http_response_code(400);
					die(json_encode(array("error" => "Error: Couldn't insert question maximum text (from '".$questionPremises[$i]."')<br>".mysqli_error($connection)."<br>Query: $queryMaxText")));
				}
				
			}
			
			$queryHookQuestionToQuestionnaire = "INSERT INTO questionnair_question (`id_questionnair`, `id_question`) VALUES ('$questionnaireID', '$questionID');";
			if(!mysqli_query($connection, $queryHookQuestionToQuestionnaire)) {
				http_response_code(400);
				die("Error: Couldn't hook question to questionnaire  (Question: '".$questionPremises[$i]."')<br>".mysqli_error($connection)."<br>Query: $queryHookQuestionToQuestionnaire");
			}
			
		}

		
		



	}



?>