/** * @param int $question_id * @param ArrayList $answers * @return ArrayList */ public function mapAnswers($question_id, $answers) { $answer_values = new ArrayList(); $question_values = SurveyQuestionValueTemplate::get()->where('OwnerID = ' . $question_id)->map('ID', 'Value')->toArray(); $question = SurveyQuestionTemplate::get_by_id('SurveyQuestionTemplate', $question_id); foreach ($answers as $answer) { $multi_answer = explode(',', $answer['Value']); foreach ($multi_answer as $single_answer) { if (!$single_answer) { continue; } if ($question->ClassName == 'SurveyRadioButtonMatrixTemplateQuestion') { $matrix = explode(':', $single_answer); $col = $matrix[0]; $row = $matrix[1]; if (!$col || !$row) { continue; } $answer_value = array('col' => $question_values[$col], 'row' => $question_values[$row]); $answer_values->push($answer_value); } else { if ($question->Name == 'NetPromoter') { $answer_value = $question_values[$single_answer]; if ($answer_value < 7) { $answer_value = 'Detractor'; } else { if ($answer_value < 9) { $answer_value = 'Neutral'; } else { $answer_value = 'Promoter'; } } $answer_values->push($answer_value); } else { if (isset($question_values[$single_answer])) { $answer_value = $question_values[$single_answer]; $answer_values->push($answer_value); } else { $answer_values->push($single_answer); } } } } } return $answer_values; }
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); }