<?php
	use classes\lib\CGRewards;

	include('mod/_mod_security.php');

	$criteria = '';
	$filter = '';
	$sql_concierges = "
		SELECT 
			s.id AS survey_id,
			s.description,
			s.send_date,
			sr.responder_type,
			srq.survey_responder_id,
			sr.concierge_id,
			sr.guest_id,
			c.first_name,
			c.last_name,
			c.title,
			(CASE WHEN c.primary_email > ' ' THEN c.primary_email ELSE c.secondary_email END) AS email,
			h.hotel_id,
			h.hotel_name AS org,
			sr.sent_date,
			sr.responded_date,
			srq.seq,
			srq.question_type,
			srq.question_text,
			srq.answer_value
		FROM surveys s
		JOIN surveys_responders sr ON (s.id = sr.survey_id)
		JOIN surveys_responders_questions srq ON (sr.id = srq.survey_responder_id)
		JOIN concierges c ON (c.concierge_id = sr.concierge_id)
		LEFT JOIN hotels h ON (h.hotel_id = c.hotel_id)
	";
	$sql_guests = "
		SELECT 
			s.id AS survey_id,
			s.description,
			s.send_date,
			sr.responder_type,
			srq.survey_responder_id,
			sr.concierge_id,
			sr.guest_id,
			g.first_name,
			g.last_name,
			g.title,
			g.email,
			NULL AS hotel_id,
			g.company AS org,
			sr.sent_date,
			sr.responded_date,
			srq.seq,
			srq.question_type,
			srq.question_text,
			srq.answer_value
		FROM surveys s
		JOIN surveys_responders sr ON (s.id = sr.survey_id)
		JOIN surveys_responders_questions srq ON (sr.id = srq.survey_responder_id)
		JOIN guests g ON (g.guest_id = sr.guest_id)
	";
	$sql = 'SELECT * FROM ((' . $sql_concierges . ') UNION (' . $sql_guests . ')) AS srr '.
			"WHERE survey_id = " . nz($_POST['src_survey_id'], '0') . " ".
			"ORDER BY survey_id, org, first_name, last_name, seq";
	//error_log($sql);
	$result = $db->query($sql) or die('Database Error!');
	$row_count = $result->rowCount();
	if ($row_count <= 0) :
		echo 'No data for report!';
		exit();
	endif;

	$sql = "
		SELECT stq.* FROM survey_template_questions stq
		JOIN surveys s ON (stq.survey_template_id = s.survey_template_id)
		WHERE s.id = " . nz($_POST['src_survey_id'], '0') . "
		ORDER BY stq.seq
	";
	$result_q = $db->query($sql) or die('Database Error!');
	$questions_data = $result_q->fetchAll();
	$questions = [];
	foreach ($questions_data as $question_data) :
		$questions[] = snake_case($question_data['question_text']);
	endforeach;
	
	$rpt = get_report_class($_POST['output_format']);
	$rpt->set_report_title('Survey Responses Crosstab');
	$rpt->report_start();
	
	$rpt->header_start();
	if (!empty($filter)) :
		$rpt->header_row_start();
		$rpt->header_cell_add('Filters: '.$filter, ['colspan'=>'18', 'class'=>'large']);
		$rpt->header_row_end();
	endif;
	$rpt->header_row_start();
	$rpt->header_cell_add('First Name', ['width'=>'70']);
	$rpt->header_cell_add('Last Name', ['width'=>'70']);
	$rpt->header_cell_add('Email', ['width'=>'70']);
	$rpt->header_cell_add('Title', ['width'=>'70']);
	$rpt->header_cell_add('Hotel/Company', ['width'=>'70']);
	$rpt->header_cell_add('Responder Type', ['width'=>'70']);
	$rpt->header_cell_add('Responded Date', ['width'=>'70']);
	foreach ($questions as $question) :
		$rpt->header_cell_add($question, ['width'=>'70']);
	endforeach;
	$rpt->header_row_end();
	$rpt->header_end();
	
	$rpt->data_start();
	$responder_id_hold = 0;
	while($row = $result->fetch(PDO::FETCH_OBJ)) :
		if ($responder_id_hold != $row->survey_responder_id) :
			if ($responder_id_hold != 0) :
				foreach ($questions as $question) :
					$rpt->data_cell_add($answers[$question] ?? null);
				endforeach;
				$rpt->data_row_end();
			endif;
			$responder_id_hold = $row->survey_responder_id;
			$answers = [];
			$rpt->data_row_start();
			$rpt->data_cell_add($row->first_name);
			$rpt->data_cell_add($row->last_name);
			$rpt->data_cell_add($row->email);
			$rpt->data_cell_add($row->title);
			$rpt->data_cell_add($row->org);
			$rpt->data_cell_add($row->responder_type);
			$rpt->data_cell_add($row->responded_date);
		endif;
		$answers[$row->question_text] = $row->answer_value;
	endwhile;
	if ($responder_id_hold != 0) :
		foreach ($questions as $question) :
			$rpt->data_cell_add($answers[$question] ?? null);
		endforeach;
		$rpt->data_row_end();
	endif;
	$rpt->data_end();
	
	$rpt->report_end();
	$rpt->output_report();