from project
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
CREATE DEFINER=`root`@`localhost` FUNCTION `isCorrect_ManyCorrectAnswers`(aEid integer,aTid integer, aUid integer, aQid integer) RETURNS tinyint(1) BEGIN declare r tinyint(1) default(0); declare answersID integer; Declare done tinyint(1) default(0); # is correct if all answers correct # isCorrect if at least one answer correct declare isCorrectIfAllAnswersCorrect tinyint(1); SELECT isCorrectIfAllAnswersCorrect FROM coffeetest_db.testquestions WHERE id = aQid INTO isCorrectIfAllAnswersCorrect; if isCorrectIfAllAnswersCorrect then begin # check if all answers correct DECLARE answersCursor CURSOR FOR SELECT iftable.testAnswers_id as respondentAnswer FROM coffeetest_db.answersonquestions aq, testQuestions tq, ifoneormanycorrectanswers iftable where aq.testQuestions_id=tq.id and iftable.answersonquestions_id=aq.id and aq.events_id=aEid and aq.users_id=aUid and aq.tests_id=aTid and aq.testQuestions_id=aQid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; set r=1; # assume all variants where checked correct Open answersCursor; whileLoop:while done=false do fetch answersCursor into answersId; # do something if isAnswerCorrect(aQid,answersId)=0 then begin set r=0; leave whileLoop; end; end if; end while; Close answersCursor; end; else begin # check if at least one answer correct DECLARE answersCursor CURSOR FOR SELECT iftable.testAnswers_id as respondentAnswer FROM coffeetest_db.answersonquestions aq, testQuestions tq, ifoneormanycorrectanswers iftable where aq.testQuestions_id=tq.id and iftable.answersonquestions_id=aq.id and aq.events_id=aEid and aq.users_id=aUid and aq.tests_id=aTid and aq.testQuestions_id=aQid; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true; Open answersCursor; whileLoop:while done=false do fetch answersCursor into answersId; # do something if isAnswerCorrect(aQid,answersId)=1 then begin set r=1; leave whileLoop; end; end if; end while; Close answersCursor; end; end if; RETURN r; END |