OpenCATS 0.9.4-3

Free, Open-Source Applicant Tracking System including job-board. Entirely customisable!

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.
#1463
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
$$
How to install OpenCats on hostinger?

I have PHP 5.6 and I uploaded the OpenCats folder […]

how to "delete" a user

Thanks for the reply The thread op asked about de[…]

Email Integration

Hey CFM, all, this is the code I had created to […]

Job Order Export

Hi Russ, I have OpenCATS setup on shared hosting,[…]

Yes it is possible. But you have to dug into the s[…]

It need to be exceptionally truthful to export the[…]

hey plz reply fast

Hi All, would like to ask the following: We have[…]