Salary Market

use salarymarket;

-- 1) Quiz definition

INSERT INTO solution_quiz (id, quizCode, quiz_image, averagetime)

VALUES

  (6, 'quiz_6_Sales_Force_Management',

   'Questionnaire 6.jpeg',

   20);

-- 2) Quiz translations

INSERT INTO solution_quiz_translate (quizCode, title, description, short_description, languageId)

VALUES

  ('quiz_6_Sales_Force_Management',

   'Questionnaire 6 – Gestion de la force de vente',

   'Étude des pratiques de gestion de la force de vente',

   'Étude des pratiques de gestion de la force de vente',

   2),

  ('quiz_6_Sales_Force_Management',

   'Quiz 6 – Sales Force Management',

   'Survey of sales force management practices',

   'Survey of sales force management practices',

   1);

-- 3) Single Section

INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)

VALUES

  ('Sales_Force_Management', 'quiz_6_Sales_Force_Management', 1);

-- 4) Section translations

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)

VALUES

  ('Sales_Force_Management', 'Sales Force Management', 1),

  ('Sales_Force_Management', 'Gestion de la force de vente', 2);

-- 5) Questions & chart types

INSERT INTO solution_quiz_section_questions

  (questionCode, sectionCode, quizCode, idparent, type, question_order, echartType)

VALUES

  -- Parent question

  ('Q6-SFM-1', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', NULL, 'CU', 1, 'Pie'),

  -- Child questions

  ('Q6-SFM-2', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CU', 2, 'Pie'),

  ('Q6-SFM-3', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CM', 3, 'Pie'),

  ('Q6-SFM-4', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-3', 'CU', 4, 'Pie'),

  ('Q6-SFM-5', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-3', 'CU', 5, 'Pie'),

  ('Q6-SFM-6', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CU', 6, 'Table'),

  ('Q6-SFM-7', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management','Q6-SFM-1', 'CU', 7, 'Pie'),

  ('Q6-SFM-8', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CM', 8, 'Table'),

  ('Q6-SFM-9', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CM', 9, 'Table'),

  ('Q6-SFM-10', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CU', 10, 'Pie'),

  ('Q6-SFM-11', 'Sales_Force_Management', 'quiz_6_Sales_Force_Management', 'Q6-SFM-1', 'CM', 11, 'Bar');

-- 6) Question translations

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

  -- Parent question

  ('Q6-SFM-1', 'Disposez-vous de plans d''incitation/de commissions sur les ventes', 2),

  ('Q6-SFM-1', 'Do you have sales incentive/commission plans', 1),

  -- Child questions

  ('Q6-SFM-2', 'Nombre de plans d''incitation/de commissions sur les ventes', 2),

  ('Q6-SFM-2', 'Number of sales incentive/commission plans', 1),

  ('Q6-SFM-3', 'Quels types de rémunération spécifique sont proposés pour la force de vente ?', 2),

  ('Q6-SFM-3', 'What types of specific compensation are offered for the sales force?', 1),

  ('Q6-SFM-4', 'Disposez-vous d''un plafond de rémunération pour les commissions basées sur les objectifs', 2),

  ('Q6-SFM-4', 'Do you have a compensation cap for goal-based commissions', 1),

  ('Q6-SFM-5', 'Disposez-vous d''un plafond de rémunération pour les primes de performance', 2),

  ('Q6-SFM-5', 'Do you have a compensation cap for performance bonuses', 1),

  ('Q6-SFM-6', 'A partir de quand les nouveaux embauchés peuvent participer à un plan d''incitation à la vente/à la commission', 2),

  ('Q6-SFM-6', 'When can new hires participate in a sales/commission incentive plan', 1),

  ('Q6-SFM-7', 'L''organisation dispose-t-elle un plan d''incitation à long terme', 2),

  ('Q6-SFM-7', 'Does the organization have a long-term incentive plan', 1),

  ('Q6-SFM-8', 'Quelles sont les principales mesures des résultats des ventes utilisées', 2),

  ('Q6-SFM-8', 'What are the main sales performance measures used', 1),

  ('Q6-SFM-9', 'Quelles sont les autres mesures des résultats des ventes', 2),

  ('Q6-SFM-9', 'What are the other sales performance measures', 1),

  ('Q6-SFM-10', 'Quelle est la fréquence de révision des cibles de vente et des plans d''incitation / de commission ?', 2),

  ('Q6-SFM-10', 'What is the frequency of review of sales targets and incentive/commission plans?', 1),

  ('Q6-SFM-11', 'Quels outils ou supports votre entreprise met-elle en place pour aider la force de vente à atteindre ses objectifs ?', 2),

  ('Q6-SFM-11', 'What tools or support does your company provide to help the sales force achieve their goals?', 1);

-- Insert all response items

INSERT INTO solution_reponses_items (itemCode, itemName)

VALUES

  -- Number of plans

  ('1-5', '1-5 plans'),

  ('6-10', '6-10 plans'),

  ('11-20', '11-20 plans'),

  -- Compensation types

  ('commissions', 'Commissions'),

  ('performance_bonus', 'Performance bonuses'),

  ('annual_bonus', 'Annual bonuses'),

  -- Eligibility

  ('at_hiring', 'At hiring'),

  ('after_period', 'After defined period'),

  ('other_eligibility', 'Other eligibility'),

  -- Long-term incentive

  ('yes_all', 'Yes, all employees'),

  ('yes_certain_level', 'Yes, from certain level'),

  -- Sales measures

  ('revenue', 'Revenue'),

  ('sales_volume', 'Sales volume'),

  ('profit_value', 'Profit value'),

  ('profit_market_share', 'Profit percentage Market share'),

  -- Objectives

  ('individual_objectives', 'Individual objectives'),

  ('team_objectives', 'Team objectives'),

  ('company_objectives', 'Company objectives'),

  ('none', 'None'),

  -- Review frequency

  ('monthly', 'Monthly'),

  -- Support tools

  ('training', 'Training'),

  ('tech_tools', 'Technological tools'),

  ('coaching', 'Support and coaching'),

  ('motivation_meetings', 'Motivation meetings');

  -- Then insert the new links

INSERT INTO solution_quiz_section_questions_items (itemCode, questionCode, itemParentId, isOther)

VALUES

  -- Parent question (Q6-SFM-1)

  ('yes', 'Q6-SFM-1', NULL, 0),

  ('no', 'Q6-SFM-1', NULL, 0),

  -- Q6-SFM-2 (Number of plans)

  ('1-5', 'Q6-SFM-2', 'yes', 0),

  ('6-10', 'Q6-SFM-2', 'yes', 0),

  ('11-20', 'Q6-SFM-2', 'yes', 0),

  -- Q6-SFM-3 (Types of compensation)

  ('commissions', 'Q6-SFM-3', 'yes', 0),

  ('performance_bonus', 'Q6-SFM-3', 'yes', 0),

  ('annual_bonus', 'Q6-SFM-3', 'yes', 0),

  ('other-please-specify', 'Q6-SFM-3', 'yes', 1),

  -- Q6-SFM-4 (Commission cap)

  ('yes', 'Q6-SFM-4', 'commissions', 0),

  ('no', 'Q6-SFM-4', 'commissions', 0),

  -- Q6-SFM-5 (Performance bonus cap)

  ('yes', 'Q6-SFM-5', 'performance_bonus', 0),

  ('no', 'Q6-SFM-5', 'performance_bonus', 0),

  -- Q6-SFM-6 (New hire eligibility)

  ('at_hiring', 'Q6-SFM-6', 'yes', 0),

  ('after_period', 'Q6-SFM-6', 'yes', 1),

  ('other_eligibility', 'Q6-SFM-6', 'yes', 1),

  -- Q6-SFM-7 (Long-term incentive)

  ('yes_all', 'Q6-SFM-7', 'yes', 0),

  ('yes_certain_level', 'Q6-SFM-7', 'yes', 1),

  ('no', 'Q6-SFM-7', 'yes', 0),

  -- Q6-SFM-8 (Main sales measures)

  ('revenue', 'Q6-SFM-8', 'yes', 0),

  ('sales_volume', 'Q6-SFM-8', 'yes', 0),

  ('profit_value', 'Q6-SFM-8', 'yes', 0),

  ('profit_market_share', 'Q6-SFM-8', 'yes', 0),

  ('other-please-specify', 'Q6-SFM-8', 'yes', 1),

  ('revenue', 'Q6-SFM-8', 'no', 0),

  ('sales_volume', 'Q6-SFM-8', 'no', 0),

  ('profit_value', 'Q6-SFM-8', 'no', 0),

  ('profit_market_share', 'Q6-SFM-8', 'no', 0),

  ('other-please-specify', 'Q6-SFM-8', 'no', 1),

  -- Q6-SFM-10 (Other sales measures)

  ('individual_objectives', 'Q6-SFM-9', 'yes', 0),

  ('team_objectives', 'Q6-SFM-9', 'yes', 0),

  ('company_objectives', 'Q6-SFM-9', 'yes', 0),

  ('none', 'Q6-SFM-9', 'yes', 0),

  ('other-please-specify', 'Q6-SFM-9', 'yes', 1),

   -- for no choice from the question 1

   ('individual_objectives', 'Q6-SFM-9', 'no', 0),

  ('team_objectives', 'Q6-SFM-9', 'no', 0),

  ('company_objectives', 'Q6-SFM-9', 'no', 0),

  ('none', 'Q6-SFM-9', 'no', 0),

  ('other-please-specify', 'Q6-SFM-9', 'no', 1),

  -- Q6-SFM-11 (Review frequency)

  ('monthly', 'Q6-SFM-10', 'yes', 0),

  ('quarterly', 'Q6-SFM-10', 'yes', 0),

  ('annually', 'Q6-SFM-10', 'yes', 0),

  ('other-please-specify', 'Q6-SFM-10', 'yes', 1),

  -- Q6-SFM-12 (Support tools)

  ('training', 'Q6-SFM-11', 'yes', 0),

  ('tech_tools', 'Q6-SFM-11', 'yes', 0),

  ('coaching', 'Q6-SFM-11', 'yes', 0),

  ('motivation_meetings', 'Q6-SFM-11', 'yes', 0),

  ('other-please-specify', 'Q6-SFM-11', 'yes', 1);

-- 8) Add translations for all items

INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)

VALUES

  -- Number of plans

  ('1-5', '1-5 plans', 1),

  ('1-5', '1-5 plans', 2),

  ('6-10', '6-10 plans', 1),

  ('6-10', '6-10 plans', 2),

  ('11-20', '11-20 plans', 1),

  ('11-20', '11-20 plans', 2),

  -- Compensation types

  ('commissions', 'Commissions', 1),

  ('commissions', 'Commissions', 2),

  ('performance_bonus', 'Performance bonuses', 1),

  ('performance_bonus', 'Primes de performance', 2),

  ('annual_bonus', 'Annual bonuses', 1),

  ('annual_bonus', 'Bonus annuels', 2),

  -- Eligibility

  ('at_hiring', 'At hiring (first day of entry into the organization)', 1),

  ('at_hiring', 'A l''embauche (le premier jour de l''entrée dans l''organisation)', 2),

  ('after_period', 'Eligible after a defined period', 1),

  ('after_period', 'Admissible après une période définie', 2),

  ('other_eligibility', 'Other eligibility policy', 1),

  ('other_eligibility', 'Autre politique d''éligibilité des nouveaux employés à un plan d''incitation à la vente/à la commission', 2),

  -- Long-term incentive

  ('yes_all', 'Yes, all employees are concerned', 1),

  ('yes_all', 'Oui, tout les collaborateurs sont concernés', 2),

  ('yes_certain_level', 'Yes, only from a certain level', 1),

  ('yes_certain_level', 'Oui, uniquement à partir d''un certain niveau', 2),

  -- Sales measures

  ('revenue', 'Revenue', 1),

  ('revenue', 'Chiffre d''affaires', 2),

  ('sales_volume', 'Sales volume', 1),

  ('sales_volume', 'Volume des ventes', 2),

  ('profit_value', 'Profit value', 1),

  ('profit_value', 'Valeur du bénéfice', 2),

  ('profit_market_share', 'Profit percentage Market share', 1),

  ('profit_market_share', 'Pourcentage du bénéfice Part de marché', 2),

  -- Objectives

  ('individual_objectives', 'Individual objectives', 1),

  ('individual_objectives', 'Objectifs individuels', 2),

  ('team_objectives', 'Team objectives', 1),

  ('team_objectives', 'Objectifs de l''équipe', 2),

  ('company_objectives', 'Company objectives', 1),

  ('company_objectives', 'Objectifs de l''entreprise', 2),

  ('none', 'None', 1),

  ('none', 'Aucune', 2),

  ('monthly', 'Monthly', 1),

  ('monthly', 'Mensuelle', 2),

  -- Support tools

  ('training', 'Training', 1),

  ('training', 'Formations', 2),

  ('tech_tools', 'Technological tools (CRM, sales software)', 1),

  ('tech_tools', 'Outils technologiques (CRM, logiciels de vente)', 2),

  ('coaching', 'Support and coaching', 1),

  ('coaching', 'Accompagnement et coaching', 2),

  ('motivation_meetings', 'Motivation meetings', 1),

  ('motivation_meetings', 'Réunions de motivation', 2);