/**
* Handles creating or replacing the view for this survey
*
* @param \Gems_Tracker_Survey $viewName
* @param \MUtil_Model_ModelAbstract $answerModel
*/
protected function replaceCreateView(\Gems_Tracker_Survey $survey, \MUtil_Model_ModelAbstract $answerModel)
{
$viewName = $this->getViewName($survey);
$responseDb = $this->project->getResponseDatabase();
$fieldSql = '';
foreach ($answerModel->getItemsOrdered() as $name) {
if (true === $answerModel->get($name, 'survey_question') && !in_array($name, array('submitdate', 'startdate', 'datestamp')) && !$answerModel->is($name, 'type', \MUtil_Model::TYPE_NOVALUE)) {
// Only real answers
$fieldSql .= ',MAX(IF(gdr_answer_id = ' . $responseDb->quote($name) . ', gdr_response, NULL)) AS ' . $responseDb->quoteIdentifier($name);
}
}
if ($fieldSql > '') {
$dbConfig = $this->db->getConfig();
$tokenTable = $this->db->quoteIdentifier($dbConfig['dbname'] . '.gems__tokens');
$createViewSql = 'CREATE OR REPLACE VIEW ' . $responseDb->quoteIdentifier($viewName) . ' AS SELECT gdr_id_token';
$createViewSql .= $fieldSql;
$createViewSql .= "FROM gemsdata__responses join " . $tokenTable . " on (gto_id_token=gdr_id_token and gto_id_survey=" . $survey->getSurveyId() . ") GROUP BY gdr_id_token;";
try {
$responseDb->query($createViewSql)->execute();
} catch (Exception $exc) {
$responseConfig = $responseDb->getConfig();
$dbUser = $this->db->quoteIdentifier($responseConfig['username']) . '@' . $this->db->quoteIdentifier($responseConfig['host']);
$statement = "GRANT SELECT ON " . $tokenTable . " TO " . $dbUser;
$this->getBatch()->addMessage(sprintf($this->_("Creating view failed, try adding rights using the following statement: %s"), $statement));
}
}
}
protected function _addResponseDatabaseForm($form, &$data, &$elements)
{
// A little hack to get the form to align nice... at least for my layout. Do we actually use the small max-with that is currently set?
$this->view->HeadStyle()->appendStyle('.tab-displaygroup input, .tab-displaygroup select { max-width: 39em; }');
if (isset($data['tid']) && !empty($data['tid'])) {
// If we have a responsedatabase and a track id, try something cool ;-)
$responseDb = $this->project->getResponseDatabase();
if ($this->db === $responseDb) {
// We are in the same database, now put that to use by allowing to filter respondents based on an answer in any survey
$empty = $this->util->getTranslated()->getEmptyDropdownArray();
$allSurveys = $empty + $this->util->getDbLookup()->getSurveysForExport();
$element = new \Zend_Form_Element_Select('filter_sid');
$element->setLabel($this->_('Survey'))->setMultiOptions($allSurveys);
$groupElements = array($element);
if (isset($data['filter_sid']) && !empty($data['filter_sid'])) {
$filterSurvey = $this->loader->getTracker()->getSurvey($data['filter_sid']);
$filterQuestions = $empty + $filterSurvey->getQuestionList($this->locale->getLanguage());
$element = new \Zend_Form_Element_Select('filter_answer');
$element->setLabel($this->_('Question'))->setMultiOptions($filterQuestions);
$groupElements[] = $element;
}
if (isset($filterSurvey) && isset($data['filter_answer']) && !empty($data['filter_answer'])) {
$questionInfo = $filterSurvey->getQuestionInformation($this->locale->getLanguage());
if (array_key_exists($data['filter_answer'], $questionInfo)) {
$questionInfo = $questionInfo[$data['filter_answer']];
} else {
$questionInfo = array();
}
if (array_key_exists('answers', $questionInfo) && is_array($questionInfo['answers']) && count($questionInfo['answers']) > 1) {
$element = new \Zend_Form_Element_Multiselect('filter_value');
$element->setMultiOptions($empty + $questionInfo['answers']);
$element->setAttrib('size', count($questionInfo['answers']) + 1);
} else {
$element = new \Zend_Form_Element_Text('filter_value');
}
$element->setLabel($this->_('Value'));
$groupElements[] = $element;
}
$form->addDisplayGroup($groupElements, 'filter', array('showLabels' => true, 'Description' => $this->_('Filter')));
array_shift($elements);
}
}
}
/**
* Handle sending responses to the response database (if used)
*
* Triggered by checkTokenCompletion
*
* @param int $userId The id of the gems user
*/
protected function toResponseDatabase($userId)
{
$db = $this->project->getResponseDatabase();
// WHY EXPLANATION!!
//
// For some reason mysql prepared parameters do nothing with a \Zend_Db_Expr
// object and that causes an error when using CURRENT_TIMESTAMP
$current = \MUtil_Date::now()->toString(\Gems_Tracker::DB_DATETIME_FORMAT);
$rValues = array('gdr_id_token' => $this->_tokenId, 'gdr_changed' => $current, 'gdr_changed_by' => $userId, 'gdr_created' => $current, 'gdr_created_by' => $userId);
$responses = $this->getRawAnswers();
unset($responses['token'], $responses['id'], $responses['lastpage'], $responses['startlanguage'], $responses['submitdate'], $responses['startdate'], $responses['datestamp']);
// first read current responses to differentiate between insert and update
$responseSelect = $db->select()->from('gemsdata__responses', array('gdr_answer_id', 'gdr_response'))->where('gdr_id_token = ?', $this->_tokenId);
$currentResponses = $db->fetchPairs($responseSelect);
if (!$currentResponses) {
$currentResponses = array();
}
// \MUtil_Echo::track($currentResponses, $responses);
// Prepare sql
$sql = "UPDATE gemsdata__responses\n SET `gdr_response` = ?, `gdr_changed` = ?, `gdr_changed_by` = ?\n WHERE gdr_id_token = ? AND gdr_answer_id = ? AND gdr_answer_row = 1";
$stmt = $db->prepare($sql);
$inserts = array();
foreach ($responses as $fieldName => $response) {
$rValues['gdr_answer_id'] = $fieldName;
if (is_array($response)) {
$response = join('|', $response);
}
$rValues['gdr_response'] = $response;
if (array_key_exists($fieldName, $currentResponses)) {
// Already exists, do update
// But only if value changed
if ($currentResponses[$fieldName] != $response) {
try {
// \MUtil_Echo::track($sql, $rValues['gdr_id_token'], $fieldName, $response);
$stmt->execute(array($response, $rValues['gdr_changed'], $rValues['gdr_changed_by'], $rValues['gdr_id_token'], $fieldName));
} catch (\Zend_Db_Statement_Exception $e) {
error_log($e->getMessage());
\Gems_Log::getLogger()->logError($e);
}
}
} else {
// We add the inserts together in the next prepared statement to improve speed
$inserts[$fieldName] = $rValues;
}
}
if (count($inserts) > 0) {
// \MUtil_Echo::track($inserts);
try {
$fields = array_keys(reset($inserts));
$fields = array_map(array($db, 'quoteIdentifier'), $fields);
$sql = 'INSERT INTO gemsdata__responses (' . implode(', ', $fields) . ') VALUES (' . implode(', ', array_fill(1, count($fields), '?')) . ')';
// \MUtil_Echo::track($sql);
$stmt = $db->prepare($sql);
foreach ($inserts as $insert) {
// \MUtil_Echo::track($insert);
$stmt->execute($insert);
}
} catch (\Zend_Db_Statement_Exception $e) {
error_log($e->getMessage());
\Gems_Log::getLogger()->logError($e);
}
}
}
