private function buildSurveyBuilderHeaders($flat_fields = array(), $flat_fields_entity = array())
{
$survey_header_query = <<<SQL
SELECT SS.Name, Q.ID AS QuestionID, Q.Name, Q.ClassName FROM SurveyTemplate S
INNER JOIN SurveyStepTemplate SS ON SS.SurveyTemplateID = S.ID
INNER JOIN SurveyQuestionTemplate Q ON Q.StepID = SS.ID
WHERE S.ClassName = 'SurveyTemplate' AND Q.ClassName <> 'SurveyLiteralContentQuestionTemplate'
ORDER BY SS.`Order`, Q.`Order`;
SQL;
$res = DB::query($survey_header_query);
$template_1 = array();
$template_1['SurveyID'] = null;
$template_1['CreatedByID'] = null;
$template_1['Email'] = null;
$template_1['Created'] = null;
$template_1['LastEdited'] = null;
foreach ($res as $row) {
$name = $row['Name'];
if (in_array($name, $flat_fields)) {
$q = SurveyMultiValueQuestionTemplate::get()->byID(intval($row['QuestionID']));
if (is_null($q)) {
continue;
}
foreach ($q->Values() as $v) {
$header = sprintf('%s - %s', $name, $v->Value);
$template_1[$header] = null;
}
} else {
$template_1[$name] = null;
}
}
$entity_survey_header_query = <<<SQL
SELECT SS.Name, Q.ID AS QuestionID, Q.Name, Q.ClassName FROM SurveyTemplate S
INNER JOIN EntitySurveyTemplate ES ON ES.ID = S.ID
INNER JOIN SurveyStepTemplate SS ON SS.SurveyTemplateID = S.ID
INNER JOIN SurveyQuestionTemplate Q ON Q.StepID = SS.ID
WHERE
S.ClassName = 'EntitySurveyTemplate'
AND Q.ClassName <> 'SurveyLiteralContentQuestionTemplate'
AND ES.EntityName = 'Deployment'
ORDER BY SS.`Order`, Q.`Order`;
SQL;
$res = DB::query($entity_survey_header_query);
$template_2 = array('DeploymentID' => null);
foreach ($res as $row) {
$name = $row['Name'];
if (in_array($name, $flat_fields_entity)) {
$q = SurveyMultiValueQuestionTemplate::get()->byID(intval($row['QuestionID']));
if (is_null($q)) {
continue;
}
foreach ($q->Values() as $v) {
$header = sprintf('%s - %s', $name, $v->Value);
$template_2[$header] = null;
}
} else {
if ($row['ClassName'] === 'SurveyRadioButtonMatrixTemplateQuestion') {
$q = SurveyRadioButtonMatrixTemplateQuestion::get()->byID(intval($row['QuestionID']));
if (is_null($q)) {
continue;
}
foreach ($q->Rows() as $r) {
$header = sprintf('%s - %s', $name, $r->Value);
$template_2[$header] = null;
}
} else {
$template_2[$name] = null;
}
}
}
return array($template_1, $template_2);
}
public function getProjectsUsedCombined()
{
$template_id = Session::get("SurveyBuilder.EntitySurveyTemplateStatistics.TemplateId");
$filters_where = $this->generateFilters();
$pu_questions_query = " SELECT QT.ID FROM SurveyQuestionTemplate QT\n LEFT JOIN SurveyStepTemplate ST ON ST.ID = QT.StepID\n WHERE ST.SurveyTemplateID = {$template_id} AND (QT.Name = 'ProjectsUsed' OR QT.Name = 'ProjectsUsedPoC')";
$pu_question_ids = DB::query($pu_questions_query)->column();
$answers_query = " SELECT ANS.`Value` FROM SurveyAnswer ANS\n INNER JOIN SurveyStep STP ON STP.ID = ANS.StepID\n INNER JOIN Survey I ON I.ID = STP.SurveyID\n WHERE I.IsTest = 0 AND ANS.QuestionID IN (" . implode(',', $pu_question_ids) . ")\n AND ANS.`Value` IS NOT NULL {$filters_where}";
$answers = DB::query($answers_query);
//die($answers_query);
$question_values = SurveyQuestionValueTemplate::get()->where("OwnerID IN (" . implode(',', $pu_question_ids) . ")")->map('ID', 'Value')->toArray();
// set question labels
$values = array();
$row_values_array = array();
$total_answers = 0;
foreach ($pu_question_ids as $pu_question_id) {
$pu_question = SurveyRadioButtonMatrixTemplateQuestion::get_by_id('SurveyRadioButtonMatrixTemplateQuestion', $pu_question_id);
foreach ($pu_question->Rows() as $row_value) {
$row_values_array[$row_value->Value] = 0;
}
foreach ($pu_question->Columns() as $col_value) {
$values[$col_value->Value] = $row_values_array;
}
// calculate total answers
$total_answers += $this->SurveyBuilderSurveyCountByQuestion($pu_question_id);
}
// count answers
foreach ($answers as $answer) {
$multi_answer = explode(',', $answer['Value']);
foreach ($multi_answer as $single_answer) {
if (!$single_answer) {
continue;
}
$matrix = explode(':', $single_answer);
$col = $matrix[0];
$row = $matrix[1];
if (!$col || !$row) {
continue;
}
$row_value = $question_values[$row];
$col_value = $question_values[$col];
$values[$row_value][$col_value]++;
}
}
foreach ($values as $key => $val) {
foreach ($val as $key2 => $val2) {
$values[$key][$key2] = round($val2 / $total_answers * 100);
}
}
return json_encode($values);
}