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

I finally got opencats 9.4-3 loaded on my hostgato[…]

MySql is not working.

Thank you for the help. I appreciate it.

Email History

Just to clarify this thread. The files contained i[…]

Okay, I have finally created my variables. Even th[…]

Insert this code at line 60 of /modules/candidates[…]

With the PHPMailer option sending multiple emails […]

I've tried to get template support working (https:[…]

I too am getting this error. I follow the instruct[…]

Encountered error on Step 6: Loading Extras Not Ac[…]

Bulk Emails

Hiya, I worked with Jamin to amend the SendEma[…]

Dear %CANDFULLNAME%

How?

It appears that spammers have infiltrated these fo[…]

php module requirements

That's my question.... What modules are required […]

Linking outside the CATS system

I had a similar issue under the candidate profile […]

Quick Search BOX

Very nice. Thanks!

Email,Ldap and Time Zone

Hi, While sending the test mail, I am getting bel[…]