<?php
	include("../wp-config.php");
	global $wpdb;

	$PSY_EXAMS = $wpdb->prefix . PSY_EXAMS;
    $PSY_ABILITY = $wpdb->prefix . PSY_ABILITY;
    $PSY_SP_QUESTION = $wpdb->prefix . PSY_SP_QUESTION;
    $PSY_USERS_DETAILS = $wpdb->prefix . PSY_USERS_DETAILS;
    $PSY_ABILITY_RAW_SCORE = $wpdb->prefix . PSY_ABILITY_RAW_SCORE;
    $PSY_TEST_GEN_TEST_TAKEN = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN;
    $PSY_INTEREST_TEST_OPTION_MEAN = $wpdb->prefix . PSY_INTEREST_TEST_OPTION_MEAN;
    $PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER = $wpdb->prefix . PSY_TEST_GEN_TEST_TAKEN_QUESTION_ANSWER;

	$type = $_REQUEST['type'];
	if($type == "APT"){
		$file  = "Aptitude Test";
	} else if($type == "PER"){
		$file  = "Personality Test";
	}
	$csv_output = 'Sr. No., Student name, Student Email, Student Mobile, Date of Birth ,Gender, Test Date , Verbal Ability, Clerical Ability, Reasoning Ablility, Numerical Ability, Spatial Ability, Closure Ability, Mechanical Ability';
	$csv_output .= "\n";

	$getDataQuery = "SELECT t.test_taken_id, t.user_id, t.class_id,t.exam_category_id, t.total_questions, t.answer_selected, t.time_taken_in_seconds, t.start_date, t.test_type, u.first_name, u.mobile_number, u.email_address, e.category_name, u.date_of_birth, u.gender_id,e.parent_id FROM $PSY_TEST_GEN_TEST_TAKEN as t LEFT JOIN $PSY_USERS_DETAILS as u ON u.user_id=t.user_id LEFT JOIN $PSY_EXAMS as e ON e.exam_category_id=t.exam_category_id where t.status='Y' AND t.test_type='APT' order by t.test_taken_id desc";
	$getData = $wpdb->get_results($getDataQuery);

	if( sizeof($getData) != 0)
	{
		$i = 1;
		foreach ($getData as $vd)
		{
			
			$s = $vd->date_of_birth;
			$dt = new DateTime($s);
			$date = $dt->format('d-m-Y');
			$srno=$i+$indexN;

			$csv_output .= $i . ',';
			$csv_output .= $vd->first_name . ',';
			$csv_output .= $vd->email_address . ',';
			$csv_output .= $vd->mobile_number . ',';
			$csv_output .= $date . ',';
			$csv_output .= $vd->gender_id . ',';
			$csv_output .= $vd->start_date . ',';

			$getsubject = $wpdb->get_results("SELECT e.category_name,e.exam_category_id from $PSY_EXAMS as e LEFT JOIN $PSY_ABILITY as ab on ab.ref_id=e.exam_category_id where e.parent_id='$vd->exam_category_id' order by e.exam_category_id");

            foreach ($getsubject as $sub) {
                $subject = $sub->exam_category_id;
                $class_id = $vd->class_id;

                $abilityData = $wpdb->get_row("SELECT ability_id,instruction FROM $PSY_ABILITY where ref_id='$subject'");
                $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='$vd->test_taken_id' and ttqat.subject_id='$subject'");

    			$correctQn = (int) $getTestSummary->correctQn;
    			$inCorrectQn = $getTestSummary->inCorrectQn;
        		$unAttemptQn = $getTestSummary->unAttemptQn;

                $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'");

                $csv_output .= $rawScore . ',';
            }
			
			$csv_output .= "\n";
			$contents = strip_tags($csv_output);
			$i++;
		}
	} else {
	    $csv_output = "No Record Found!";
	}

	$filename = $file;
	header("Content-type: application/vnd.ms-excel");
	header("Content-disposition:   filename=" . $filename . ".csv");
	print chr(255) . chr(254) . mb_convert_encoding($csv_output, 'UTF-16LE', 'UTF-8');
	//print $csv_output;
	exit;
?>