コード例 #1
0
 function up()
 {
     DBManager::get()->exec("\n            CREATE TABLE IF NOT EXISTS `questionnaires` (\n                `questionnaire_id` varchar(32) NOT NULL,\n                `title` varchar(128)  NOT NULL,\n                `description` text NULL,\n                `user_id` varchar(32)  NOT NULL,\n                `startdate` BIGINT(20) NULL,\n                `stopdate` BIGINT(20) NULL,\n                `visible` TINYINT(1) DEFAULT '0' NOT NULL,\n                `anonymous` TINYINT(1) DEFAULT '0' NOT NULL,\n                `resultvisibility` ENUM('always', 'never', 'afterending') DEFAULT 'always' NOT NULL,\n                `editanswers` TINYINT(1) DEFAULT '1' NOT NULL,\n                `chdate` bigint(20) NOT NULL,\n                `mkdate` bigint(20) NOT NULL,\n                PRIMARY KEY (`questionnaire_id`),\n                KEY `user_id` (`user_id`)\n            )\n        ");
     DBManager::get()->exec("\n            CREATE TABLE IF NOT EXISTS `questionnaire_questions` (\n                `question_id` varchar(32) NOT NULL,\n                `questionnaire_id` varchar(32) NOT NULL,\n                `questiontype` varchar(64) NOT NULL,\n                `questiondata` text NOT NULL,\n                `position` INT NOT NULL,\n                `chdate` bigint(20) NOT NULL,\n                `mkdate` bigint(20) NOT NULL,\n                PRIMARY KEY (`question_id`),\n                KEY `questionnaire_id` (`questionnaire_id`)\n            )\n        ");
     DBManager::get()->exec("\n            CREATE TABLE IF NOT EXISTS `questionnaire_assignments` (\n                `assignment_id` varchar(32) NOT NULL,\n                `questionnaire_id` varchar(32) NOT NULL,\n                `range_id` varchar(32) NOT NULL,\n                `range_type` varchar(64) NOT NULL,\n                `user_id` varchar(32) NOT NULL,\n                `chdate` bigint(20) NOT NULL,\n                `mkdate` int(11) NOT NULL,\n                PRIMARY KEY (`assignment_id`),\n                KEY `questionnaire_id` (`questionnaire_id`),\n                KEY `range_id_range_type` (`range_id`,`range_type`),\n                KEY `user_id` (`user_id`)\n            )\n        ");
     DBManager::get()->exec("\n            CREATE TABLE IF NOT EXISTS `questionnaire_answers` (\n                `answer_id` varchar(32) NOT NULL,\n                `question_id` varchar(32)  NOT NULL,\n                `user_id` varchar(32) NULL,\n                `answerdata` text NOT NULL,\n                `chdate` bigint(20) NOT NULL,\n                `mkdate` bigint(20) NOT NULL,\n                PRIMARY KEY (`answer_id`),\n                KEY `question_id` (`question_id`),\n                KEY `user_id` (`user_id`)\n            )\n        ");
     DBManager::get()->exec("\n            CREATE TABLE IF NOT EXISTS `questionnaire_anonymous_answers` (\n                `anonymous_answer_id` varchar(32) NOT NULL,\n                `questionnaire_id` varchar(32) NOT NULL,\n                `user_id` varchar(32) NOT NULL,\n                `chdate` bigint(20) NOT NULL,\n                `mkdate` int(11) NOT NULL,\n                PRIMARY KEY (`anonymous_answer_id`),\n                KEY `questionnaire_id` (`questionnaire_id`),\n                UNIQUE KEY `questionnaire_id_user_id` (`questionnaire_id`,`user_id`),\n                KEY `user_id` (`user_id`)\n            )\n        ");
     //now import old data into new tables:
     $resultvisibility_mapping = array('ever' => 'always', 'delivery' => 'always', 'end' => 'afterending', 'never' => 'never');
     $all_votes = DBManager::get()->prepare("\n            SELECT * FROM vote\n        ");
     $all_votes->execute();
     while ($vote = $all_votes->fetch(PDO::FETCH_ASSOC)) {
         //Fragebogen erstellen
         $questionnaire = new Questionnaire($vote['vote_id']);
         $questionnaire['title'] = $vote['title'];
         $questionnaire->setId($vote['vote_id']);
         $questionnaire['user_id'] = $vote['author_id'];
         $questionnaire['startdate'] = $vote['startdate'];
         $questionnaire['stopdate'] = $vote['stopdate'] ?: (in_array($vote['state'], array("stopvis", "stopinvis")) ? time() : null);
         $questionnaire['visible'] = in_array($vote['state'], array("active", "stopvis")) ? 1 : 0;
         // stopvis new active stopinvis
         $questionnaire['anonymous'] = $vote['anonymous'];
         $questionnaire['resultvisibility'] = $resultvisibility_mapping[$vote['resultvisibility']];
         $questionnaire['editanswers'] = $vote['changeable'];
         $questionnaire['chdate'] = $vote['chdate'];
         $questionnaire['mkdate'] = $vote['mkdate'];
         $questionnaire->store();
         //Tests und Umfragen anlegen
         if ($vote['type'] === "vote") {
             $question = new Vote();
             $question['questiontype'] = "Vote";
         } else {
             $question = new Test();
             $question['questiontype'] = "Test";
         }
         $questiondata = array();
         $question['questionnaire_id'] = $questionnaire->getId();
         $question['chdate'] = $vote['chdate'];
         $question['mkdate'] = $vote['mkdate'];
         $questiondata['multiplechoice'] = $vote['multiplechoice'];
         $questiondata['question'] = $vote['question'];
         $question['position'] = 1;
         //Antwortmöglichkeiten vorsehen:
         $options_statement = DBManager::get()->prepare("\n                SELECT *\n                FROM voteanswers\n                WHERE vote_id = ?\n                ORDER BY position ASC\n            ");
         $options_statement->execute(array($vote['vote_id']));
         $options = $options_statement->fetchAll(PDO::FETCH_ASSOC);
         $mapping = array();
         $counter = array();
         foreach ($options as $key => $option) {
             $questiondata['options'][] = $option['answer'];
             $mapping[$option['answer_id']] = $key + 1;
             $counter[$option['answer_id']] = $option['counter'];
             if ($vote['type'] === "test" && $option['correct']) {
                 $questiondata['correctanswer'][] = $key + 1;
             }
         }
         $question['questiondata'] = $questiondata;
         $question->store();
         //Bestehende Antworten migrieren
         if ($questionnaire['anonymous']) {
             foreach ($counter as $answer_id => $count) {
                 for ($i = 0; $i < $count; $i++) {
                     $answer = new QuestionnaireAnswer();
                     $answer['user_id'] = null;
                     $answer['chdate'] = 1;
                     //damit man nicht aus dem chdate auf die user_id schließen kann
                     $answer['mkdate'] = 1;
                     //mkdate genauso
                     $answer['question_id'] = $question->getId();
                     $answerdata = array();
                     $answers = array($answer_id);
                     foreach ($answers as $key => $answer_data) {
                         $answers[$key] = $mapping[$answer_data];
                     }
                     sort($answers);
                     $answerdata['answers'] = $answers;
                     if (!$questiondata['multiplechoice']) {
                         $answerdata['answers'] = $answerdata['answers'][0];
                     }
                     $answer['answerdata'] = $answerdata;
                     $answer->store();
                 }
             }
             $statement = DBManager::get()->prepare("\n                    SELECT *\n                    FROM vote_user\n                    WHERE vote_id = :vote_id\n                ");
             $statement->execute(array('vote_id' => $vote['vote_id']));
             foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $anonymous_vote) {
                 $anonymous_answer = new QuestionnaireAnonymousAnswer();
                 $anonymous_answer['questionnaire_id'] = $questionnaire->getId();
                 $anonymous_answer['user_id'] = $anonymous_vote['user_id'];
                 $anonymous_answer['chdate'] = $anonymous_vote['votedate'];
                 $anonymous_answer['mkdate'] = $anonymous_vote['votedate'];
                 $anonymous_answer->store();
             }
         } else {
             $statement = DBManager::get()->prepare("\n                    SELECT GROUP_CONCAT(answer_id SEPARATOR ' ') AS answers, user_id, MAX(votedate) AS votedate\n                    FROM voteanswers_user\n                    WHERE answer_id IN (?)\n                    GROUP BY user_id\n                ");
             $statement->execute(array(array_keys($mapping)));
             foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $answer_data) {
                 $answer = new QuestionnaireAnswer();
                 $answer['user_id'] = $answer_data['user_id'];
                 $answer['chdate'] = $answer_data['votedate'];
                 $answer['mkdate'] = $answer_data['votedate'];
                 $answer['question_id'] = $question->getId();
                 $answerdata = array();
                 $answers = explode(" ", $answer_data['answers']);
                 foreach ($answers as $key => $answer_data) {
                     $answers[$key] = $mapping[$answer_data];
                 }
                 sort($answers);
                 $answerdata['answers'] = $answers;
                 if (!$questiondata['multiplechoice']) {
                     $answerdata['answers'] = $answerdata['answers'][0];
                 }
                 $answer['answerdata'] = $answerdata;
                 $answer->store();
             }
         }
         //Und noch einhängen das ganze:
         $binding = new QuestionnaireAssignment();
         $binding['questionnaire_id'] = $questionnaire->getId();
         $binding['range_id'] = $vote['range_id'] !== "studip" ? $vote['range_id'] : "start";
         $binding['range_type'] = $vote['range_id'] === "studip" ? "static" : ($vote['range_id'] === $vote['author_id'] ? "user" : (Institute::find($vote['range_id']) ? "institute" : "course"));
         $binding['user_id'] = $vote['author_id'];
         $binding['chdate'] = $questionnaire['chdate'];
         $binding['mkdate'] = $questionnaire['mkdate'];
         $binding->store();
     }
     //and finally clean up:
     //DBManager::get()->exec("DROP TABLE IF EXISTS `vote`");
     //DBManager::get()->exec("DROP TABLE IF EXISTS `voteanswers`");
     //DBManager::get()->exec("DROP TABLE IF EXISTS `voteanswers_user`");
     //DBManager::get()->exec("DROP TABLE IF EXISTS `vote_user`");
 }
コード例 #2
0
ファイル: Questionnaire.php プロジェクト: danielpaul69/cgates
function getQuestionnaireByUtilisateur($idUtilisateur)
{
    $questionnaireRequete = "SELECT * FROM questionnaire WHERE ID_USERSAISI  = " . $idUtilisateur . "";
    $questionnaireResultat = $GLOBALS['bdd']->query($questionnaireRequete);
    $listeQuestionnaires = new ArrayObject();
    while ($unQuestionnaire = $questionnaireResultat->fetch()) {
        $questionnaire = new Questionnaire();
        $questionnaire->setId($unQuestionnaire['ID_QUESTIONNAIRE']);
        $questionnaire->setIdUtilisateur($unQuestionnaire['ID_USERSAISI']);
        $questionnaire->setLibelle($unQuestionnaire['LIBELLE_QUESTIONNAIRE']);
        $questionnaire->setNbContact($unQuestionnaire['nbContact']);
        $questionnaire->setIdTelemarket($unQuestionnaire['idUserTelemarket']);
        $questionnaire->setDateSaisi($unQuestionnaire['DATESAISIE_QUESTIONNAIRE']);
        $questionnaire->setDateMiseAJour($unQuestionnaire['DATEMAJ_QUESTIONNAIRE']);
        $listeQuestionnaires->append($questionnaire);
    }
    return $listeQuestionnaires;
}