<?php
    include_once('../wp-config.php');
    

    function aptitudeScores($subject,$ability_id,$class_id,$test_id){
        global $wpdb;
        $PSY_SP_QUESTION = $wpdb->prefix . PSY_SP_QUESTION;
        $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER;
        $PSY_ABILITY_RAW_SCORE = $wpdb->prefix . PSY_ABILITY_RAW_SCORE;

        $getTestSummary = $wpdb->get_row("SELECT count(case when ttqt.correct_answer=ttqat.answer then 1 end) as correctQn from $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER as ttqat Inner join $PSY_SP_QUESTION as ttqt on ttqt.question_id = ttqat.test_question_id where ttqat.test_taken_id='$test_id' and ttqat.subject_id='$subject'");
        $correctQn = (int) $getTestSummary->correctQn;

        $rawScore = $wpdb->get_var("SELECT sten_score FROM $PSY_ABILITY_RAW_SCORE where $correctQn BETWEEN raw_score_start and raw_score_end and class_id='$class_id' and ability_id='$ability_id'");

        return $rawScore;
        
    }

    function emuAptitudeTest($subject, $class_id, $test_id)
{
    global $wpdb;
    $PSY_ABILITY = $wpdb->prefix . PSY_ABILITY;
    $PSY_SP_QUESTION = $wpdb->prefix . PSY_SP_QUESTION;
    $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER;
    $PSY_ABILITY_RAW_SCORE = $wpdb->prefix . PSY_ABILITY_RAW_SCORE;

    $abilityData = $wpdb->get_row("SELECT ability_id, description, color FROM $PSY_ABILITY where FIND_IN_SET($subject,ref_id)");
    $ability_id = $abilityData->ability_id;

    $getTestSummary = $wpdb->get_row("SELECT count(case when ttqt.correct_answer=ttqat.answer then 1 end) as correctQn, count(case when ttqt.correct_answer!=ttqat.answer and ttqat.answer!=0 then 1 end) as inCorrectQn, count(case when ttqat.answer=0 then 1 end) as unAttemptQn from $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER as ttqat Inner join $PSY_SP_QUESTION as ttqt on ttqt.question_id = ttqat.test_question_id where ttqat.test_taken_id='$test_id' and ttqat.subject_id='$subject'");
    $correctQn = (int) $getTestSummary->correctQn;

    $rawScore = $wpdb->get_row("SELECT sten_score FROM $PSY_ABILITY_RAW_SCORE where $correctQn BETWEEN raw_score_start and raw_score_end and class_id='$class_id' and ability_id='$ability_id'");

    $stenScore = (int) $rawScore->sten_score;

    return $stenScore;
}

    function personalityTeen($subject,$ability_id,$class_id,$test_id){
        global $wpdb;
        $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER;
        $PSY_ABILITY_RAW_SCORE = $wpdb->prefix . PSY_ABILITY_RAW_SCORE;
        $PSY_SP_OPTION_VALUE = $wpdb->prefix . PSY_SP_OPTION_VALUE;

        $getTestSummary = $wpdb->get_row("SELECT SUM(CASE WHEN qa.answer=1 THEN so.option_1 WHEN qa.answer=2 THEN so.option_2 WHEN qa.answer=3 THEN so.option_3 END) as correctQn from $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER as qa Inner join $PSY_SP_OPTION_VALUE as so on so.question_id = qa.test_question_id where qa.test_taken_id='$test_id' and qa.module_id='$subject'");
        $correctQn = (int) $getTestSummary->correctQn;

        $rawScore = $wpdb->get_var("SELECT sten_score FROM $PSY_ABILITY_RAW_SCORE where $correctQn BETWEEN raw_score_start and raw_score_end and class_id='$class_id' and ability_id='$ability_id'");

        return $rawScore;
    }

    function personalityAdult($subject,$ability_id,$class_id,$test_id){
        global $wpdb;
        $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER;
        $PSY_ABILITY_RAW_SCORE = $wpdb->prefix . PSY_ABILITY_RAW_SCORE;
        $PSY_SP_OPTION_VALUE = $wpdb->prefix . PSY_SP_OPTION_VALUE;

        $getTestSummary = $wpdb->get_row("SELECT SUM(CASE WHEN qa.answer=1 THEN so.option_1 WHEN qa.answer=2 THEN so.option_2 WHEN qa.answer=3 THEN so.option_3 END) as correctQn from $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER as qa Inner join $PSY_SP_OPTION_VALUE as so on so.question_id = qa.test_question_id where qa.test_taken_id='$test_id' and qa.module_id='$subject'");
        $correctQn = (int) $getTestSummary->correctQn;

        $rawScore = $wpdb->get_var("SELECT sten_score FROM $PSY_ABILITY_RAW_SCORE where $correctQn BETWEEN raw_score_start and raw_score_end and class_id='$class_id' and ability_id='$ability_id'");

        return $rawScore;
    }

    function interestScore($test_id){
        global $wpdb;
        $PSY_INTEREST_TEST_OPTION_MEAN = $wpdb->prefix . PSY_INTEREST_TEST_OPTION_MEAN;
        $PSY_INTEREST_TEST_TAKEN_QUSTION_ANSWER = $wpdb->prefix . PSY_INTEREST_TEST_TAKEN_QUSTION_ANSWER;

        $getOption = $wpdb->get_var("SELECT GROUP_CONCAT(answer) FROM $PSY_INTEREST_TEST_TAKEN_QUSTION_ANSWER where test_taken_id='$test_id'");

        $array = array("a","b","c","d","e","f","g","h","i","j","k");
        $answers = explode(",", $getOption);
        $countValues = array_count_values($answers);
        print_r($countValues);
        echo "<br>";
        //sort array value in alphabetical order
        ksort($countValues);
        foreach ($countValues as $key => $val) {
            

            echo '<td>'.$val.'</td>';
        }
    }