<?php require_once __DIR__.'/inc.php';

$id=isset($_GET['id'])?$_GET['id']+0:0;
if(!$id){die('need id');}

$exam_end_datetime_yyyymmddHHMM = get_post_meta($id, 'exam_end_datetime', true);
$exam_end_datetime_yyyymmddHHMMSS = $exam_end_datetime_yyyymmddHHMM.':00';

$sql0 = 'SELECT tester_email FROM '.TBL_EXAM.' WHERE exam_post_id='.$id.' AND tester_email<>"" GROUP BY tester_email';
$ary = $wpdb->get_results($sql0);
$ary_tester_email = array();
foreach($ary as$v){
  $ary_tester_email[] = $v->tester_email;
}

$ary_email_VS_exam_id = array();
foreach($ary_tester_email as$email){
  $_sql='SELECT * FROM '.TBL_EXAM.' WHERE exam_post_id='.$id
          .' AND tester_email="'.$email
          .'" AND create_datetime<="'.$exam_end_datetime_yyyymmddHHMMSS
          .'" ORDER BY create_datetime DESC'
          .' LIMIT 1';
  $row = $wpdb->get_row($_sql);
  $ary_email_VS_exam_id[$row->tester_email] = array($row->id, $row->create_datetime,$row->tester_email,$row->tester_name);
}

$s=implode(",",array('姓名','Email','考試截止前之最後交卷時間','選擇題總分','問答題總分','總分','評分人','評分時間'))."\n";
foreach($ary_email_VS_exam_id as$email=>$exam_id_AND_create_datetime){
  list($exam_id,$create_datetime,$tester_email,$tester_name) = $exam_id_AND_create_datetime;
  # get last give_score as score for this email this 考卷 this examanswer
  $last_score_row = $wpdb->get_row('SELECT * FROM '.TBL_EXAM_SCORE
                  .' WHERE exam_id='.$id
                  .' AND answer_id='.$exam_id
                  .' AND tester_email="'.$email
                  .'" ORDER BY give_score_datetime DESC LIMIT 1');
  $tester_name = str_replace(',','',$tester_name);
  $s.=implode(",",array($tester_name,
                  $tester_email,
                  $create_datetime,
                  $last_score_row->optscore,
                  $last_score_row->textareascore,
                  $last_score_row->totalscore,
                  $last_score_row->give_score_by,
                  $last_score_row->give_score_datetime))."\n";
}

// print_r($a);
header('Content-Encoding: UTF-8');
header('Content-type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="exam_'.$id.'_score.csv"');
die($s);