[gr4bb]

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
$$

[RussH]

Hi gr4bb,

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

[rabika]

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%!

[mohsin123]

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):