MySQL Procedure creating self-rating questionnaire

have you installed OpenCATS? Proud of your customizations and want to share it? Post here and wait for the praise...

Moderators: RussH, cptr13

Forum rules
Just please remember to play nicely once you walk through the door. You can disagree with us, or any other commenters in this forum, but keep your comments directed to the topic at hand - not at the commenter.
Post Reply
gr4bb
Posts: 1
Joined: 04 Jan 2012, 19:19
what is two(2) plus three(3) ?: 5

MySQL Procedure creating self-rating questionnaire

Post by gr4bb » 04 Jan 2012, 19:32

The interface to create 10-15 questions each with 10 options for candidates to rank themselves in specific skills (e.g. how would you rank yourself in PHP) was extremely painful that you have to create each option individually. I created this procedure that creates number of questions each with 10 options. I hope it is helpful to others :).

When you call the procedure you will need to pass the siteid (it is usually 1) and number of questions you want to create (e.g. 10):

Example call:

Code: Select all

call <cats db name>.createEmptyRankingQuestionnaire(1, 10);
Enjoy!

Code: Select all

/******************************************************************************

   PURPOSE:   Purpose of the script is to create a self-ranking questionnaire for OpenCATs quickly.
              It creates a questionnaire, number of questions (per the second input parameter number_of_questions) and for each
              question creates 10 options as drop down (1-10)
              Feel free to change and send the updates back to our forum! 
              URL: http://www.opencats.org/forums
      			 
   REVISIONS:
   Ver        Date        Author           Description
   -- -------  ----------  ---------------  ------------------------------------
   1.0        01/03/2011 gr4bb              1. Created this sql file.

******************************************************************************/

DELIMITER $$
DROP PROCEDURE IF EXISTS createEmptyRankingQuestionnaire
$$

CREATE PROCEDURE createEmptyRankingQuestionnaire(
   IN siteid                int,
   IN number_of_questions   int)
   BEGIN
      DECLARE questionnaire_title         VARCHAR(255)
                                             DEFAULT "Ranking Questionnaire";
      DECLARE questionnaire_description   VARCHAR(255)
                 DEFAULT "You are finished after this very short development experience questionnaire. <br /><br />Please rank yourself in the questions below for the provided list of skills";
      DECLARE question_prefix             VARCHAR(255)
                 DEFAULT "How would you rank your self in SKILL";
      DECLARE question_answer_text        VARCHAR(255); -- DETERMINED BASED ON THE COUNT
      DECLARE flag_hot_basedon_answer     INT DEFAULT 0;
      DECLARE questionnaire_id            INT DEFAULT 1;
      DECLARE question_id                 INT DEFAULT 1;
      DECLARE loopcount                   INT DEFAULT 1;
      DECLARE questionanswerloopcount     INT DEFAULT 1;

      SELECT (max(career_portal_questionnaire_id) + 1)
        INTO questionnaire_id
        FROM career_portal_questionnaire;

      -- Now insert the record
      INSERT INTO career_portal_questionnaire(career_portal_questionnaire_id,
                                              title,
                                              site_id,
                                              description,
                                              is_active)
      VALUES (questionnaire_id,
              questionnaire_title,                                    -- title
              siteid,                                               -- site_id
              questionnaire_description,                        -- description
              1                                                   -- is_active
               );

      -- Now insert the questions
      WHILE loopcount <= number_of_questions
      DO
         INSERT
           INTO career_portal_questionnaire_question(
                   career_portal_questionnaire_question_id,
                   career_portal_questionnaire_id,
                   text,
                   minimum_length,
                   maximum_length,
                   required,
                   position,
                   site_id,
                   type)
         VALUES (NULL,
                 questionnaire_id,
                 CONCAT(question_prefix, loopcount),
                 0,
                 255,
                 1,
                 loopcount,
                 siteid,
                 2                                    -- defaults to drop down
                  );

         -- insert 10 options for each question
         SET question_id := LAST_INSERT_ID();

         WHILE questionanswerloopcount <= 10
         DO
            CASE
               WHEN questionanswerloopcount = 1
               THEN
                  SET question_answer_text := "1 (Don't know anything)";
               WHEN questionanswerloopcount = 2
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 3
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 4
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 5
               THEN
                  SET question_answer_text := "5 (Medium)";
               WHEN questionanswerloopcount = 6
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 7
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 8
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 9
               THEN
                  SET question_answer_text := questionanswerloopcount;
               WHEN questionanswerloopcount = 10
               THEN
                  SET question_answer_text := "10 (Guru)";
               WHEN questionanswerloopcount >= 8
               THEN
                  SET flag_hot_basedon_answer := 1;
               WHEN questionanswerloopcount < 8
               THEN
                  SET flag_hot_basedon_answer := 0;
            END CASE;

            INSERT
              INTO career_portal_questionnaire_answer(
                      career_portal_questionnaire_answer_id,
                      career_portal_questionnaire_question_id,
                      career_portal_questionnaire_id,
                      text,
                      action_source,
                      action_notes,
                      action_is_hot,
                      action_is_active,
                      action_can_relocate,
                      action_key_skills,
                      position,
                      site_id)
            VALUES (NULL,
                    question_id,
                    questionnaire_id,
                    question_answer_text,
                    '',
                    '',
                    flag_hot_basedon_answer,
                    1,
                    0,
                    '',
                    questionanswerloopcount,
                    siteid);

            SET questionanswerloopcount = questionanswerloopcount + 1;
         END WHILE;

         -- Increment the loop count
         SET loopcount := loopcount + 1;
         -- Reset the questionanswerloopcount count
         SET questionanswerloopcount := 1;
      END WHILE;
   END
$$

User avatar
RussH
Site Admin
Posts: 781
Joined: 12 Apr 2008, 08:28
what is two(2) plus three(3) ?: 5
Location: UK
Contact:

Re: MySQL Procedure creating self-rating questionnaire

Post by RussH » 10 Jan 2012, 22:54

Hi gr4bb,

that's a great script, mind if I add it to the wiki?
RussH

Report your issues and feature requests;
https://github.com/opencats/opencats/issues

Please CLICK THE TICK to accept the answer!

rabika
Posts: 1
Joined: 22 Jul 2013, 06:18
what is two(2) plus three(3) ?: 5

Re: MySQL Procedure creating self-rating questionnaire

Post by rabika » 22 Jul 2013, 06:24

Hi,

yep that's a 'feature' from the old module and component. it's been fixed - to work almost 90% now.. just working the last 10%!
RABIKA

mohsin123
Posts: 2
Joined: 28 Jan 2014, 12:50
what is two(2) plus three(3) ?: 5

Re: MySQL Procedure creating self-rating questionnaire

Post by mohsin123 » 28 Jan 2014, 12:57

When you call the procedure you will need to pass the siteid (it is usually 1) and number of questions you want to create (e.g. 10):
Mohsin

Post Reply