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); }