Salary Market

use salarymarket;

-- Quiz 7: Travel and Expenses Management

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

VALUES

  (7, 'quiz_7_Travel_Expenses', 'Travel and Expenses.jpg', 15);

-- Quiz translations

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

VALUES

  ('quiz_7_Travel_Expenses',

   'Questionnaire 7 -La Politique de Déplacements Professionnels',

   'Étude des pratiques de gestion des déplacements professionnels',

   'Étude des pratiques de gestion des déplacements professionnels',

   2),

  ('quiz_7_Travel_Expenses',

   'Quiz 7 - Professional Travel Policy Survey',

   'Survey of professional travel management practices',

   'Survey of professional travel management practices',

   1);

-- 3) Create section

INSERT INTO solution_quiz_section (sectionCode, quizCode, section_order)

VALUES

  ('Travel_Expenses', 'quiz_7_Travel_Expenses', 1);

-- 4) Section translations

INSERT INTO solution_quiz_section_translate (sectionCode, title, languageId)

VALUES

  ('Travel_Expenses', 'Professional Travel Management', 1),

  ('Travel_Expenses', 'Gestion des Déplacements Professionnels', 2);

-- Questions

INSERT INTO solution_quiz_section_questions

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

VALUES

  -- Main questions

  ('Q7-TE-1', 'Travel_Expenses', 'quiz_7_Travel_Expenses', NULL, 'CU', 1, 'Pie'),

  ('Q7-TE-2', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-1', 'CM', 2, 'Pie'),

  ('Q7-TE-3', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-2', 'CM', 3, 'Table'),

  ('Q7-TE-4', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-2', 'CM', 4, 'Table'),

  ('Q7-TE-5', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-2', 'CM', 5, 'Table'),

  ('Q7-TE-6', 'Travel_Expenses', 'quiz_7_Travel_Expenses', NULL, 'CU', 6, 'Pie'),

  ('Q7-TE-7', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-6', 'CU', 7, 'Pie'),

  ('Q7-TE-8', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-6', 'CM', 8, 'Table'),

  ('Q7-TE-9', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-6', 'CM', 9, 'Pie'),

  ('Q7-TE-10', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-9', 'CU', 10, 'Pie'),

  ('Q7-TE-11', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-9', 'CU', 11, 'Pie'),

  ('Q7-TE-12', 'Travel_Expenses', 'quiz_7_Travel_Expenses', NULL, 'CU', 12, 'Bar'),

  ('Q7-TE-13', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-9', 'CU', 13, 'Pie'),

  ('Q7-TE-14', 'Travel_Expenses', 'quiz_7_Travel_Expenses', NULL, 'CU', 14, 'Bar'),

  ('Q7-TE-15', 'Travel_Expenses', 'quiz_7_Travel_Expenses', 'Q7-TE-9', 'CU', 15, 'Pie');

-- Question translations

INSERT INTO solution_quiz_section_questions_translate (questionCode, title, languageId)

VALUES

  ('Q7-TE-1', 'Votre entreprise a-t-elle une politique formelle pour les déplacements professionnels ?', 2),

  ('Q7-TE-1', 'Does your company have a formal policy for professional travel?', 1),

  ('Q7-TE-2', 'Quels types de déplacements professionnels sont courants dans votre entreprise ?', 2),

  ('Q7-TE-2', 'What types of professional travel are common in your company?', 1),

  ('Q7-TE-3', 'Quelles catégories de personnel éligibles aux déplacements professionnels locaux ?', 2),

  ('Q7-TE-3', 'Which staff categories are eligible for local professional travel?', 1),

  ('Q7-TE-4', 'Quelles catégories de personnel éligibles aux déplacements professionnels nationaux ?', 2),

  ('Q7-TE-4', 'Which staff categories are eligible for national professional travel?', 1),

  ('Q7-TE-5', 'Quelles catégories de personnel éligibles aux déplacements professionnels internationaux?', 2),

  ('Q7-TE-5', 'Which staff categories are eligible for international professional travel?', 1),

  ('Q7-TE-6', 'Existe-t-il une politique de remboursement des frais de déplacement professionnels ?', 2),

  ('Q7-TE-6', 'Is there a policy for reimbursing professional travel expenses?', 1),

  ('Q7-TE-7', 'Quelle est la base de remboursement des déplacements professionnels ?', 2),

  ('Q7-TE-7', 'What is the basis for reimbursing professional travel?', 1),

  ('Q7-TE-8', 'Quels sont les taux journaliers prévalents pour les déplacements professionnels dans chaque région ?', 2),

  ('Q7-TE-8', 'What are the prevailing daily rates for professional travel in each region?', 1),

  ('Q7-TE-9', 'Quels frais sont couverts lors des déplacements professionnels ?', 2),

  ('Q7-TE-9', 'What expenses are covered during professional travel?', 1),

  ('Q7-TE-10', 'Les frais d''hébergement lors des déplacements professionnels sont-ils couverts ?', 2),

  ('Q7-TE-10', 'Are accommodation expenses covered during professional travel?', 1),

  ('Q7-TE-11', 'Les frais de repas lors des déplacements professionnels sont-ils couverts ?', 2),

  ('Q7-TE-11', 'Are meal expenses covered during professional travel?', 1),

  ('Q7-TE-12', 'Des accords particuliers sont-ils mis en place avec des compagnies de transport pour les déplacements professionnels ?', 2),

  ('Q7-TE-12', 'Are special agreements in place with transport companies for professional travel?', 1),

  ('Q7-TE-13', 'Quels avantages ces accords offrent-ils?', 2),

  ('Q7-TE-13', 'What benefits do these agreements offer?', 1),

  ('Q7-TE-14', 'Des accords particuliers sont-ils mis en place avec des hôtels pour les déplacements professionnels ?', 2),

  ('Q7-TE-14', 'Are special agreements in place with hotels for professional travel?', 1),

  ('Q7-TE-15', 'Quels avantages ces accords offrent-ils?', 2),

  ('Q7-TE-15', 'What benefits do these agreements offer?', 1);

-- Insert response items

INSERT INTO solution_reponses_items (itemCode, itemName)

VALUES

  ('local_travel', 'Local travel'),

  ('national_travel', 'National travel'),

  ('international_travel', 'International travel'),

  ('yes_with_limit', 'Yes with a defined limit'),

  ('yes_without_limit', 'Yes without a defined limit'),

  ('conditional', 'Under conditions (specify)'),

  ('expense_receipts', 'Expense receipts only'),

  ('daily_allowance', 'Daily allowance only'),

  ('both_expense_allowance', 'Both expense receipts and daily allowance'),

  ('local', 'Local'),

  ('national', 'National'),

  ('africa', 'Africa'),

  ('asia_pacific', 'Asia-Pacific'),

  ('europe', 'Europe'),

  ('latin_america', 'Latin America'),

  ('middle_east', 'Middle East'),

  ('north_america', 'North America'),

  ('southeast_asia', 'Southeast Asia'),

  ('transport', 'Transport (plane, train, car)'),

  ('accommodation', 'Accommodation'),

  ('meals', 'Meals'),

  ('communications', 'Communications (phone, internet)'),

  ('other_expenses', 'Other expenses (specify)'),

  ('fully', 'Fully'),

  ('partially', 'Partially'),

  ('air_transport', 'Yes, for air transport'),

  ('rail_transport', 'Yes, for rail transport'),

  ('car_rental', 'Yes, for car rental services'),

  ('other_transport', 'Yes, for other transport modes (specify)'),

  ('preferred_rates', 'Preferred rates'),

  ('flexible_conditions', 'Flexible modification or cancellation conditions'),

  ('premium_services', 'Premium services'),

  ('national_hotels', 'Yes, for national hotels'),

  ('international_hotels', 'Yes, for international hotels'),

  ('specific_chains', 'Yes, for specific hotel chains'),

  ('reduced_rates', 'Reduced rates'),

  ('room_upgrades', 'Room upgrades'),

  ('free_cancellations', 'Free cancellations'),

  ('loyalty_programs', 'Specific loyalty programs'),

 ('professional_support', 'Professional Support');

--  Add translations for all items

INSERT INTO solution_quiz_question_items_translate (itemCode, itemValue, languageId)

VALUES

  ('local_travel', 'Déplacements locaux', 2),

  ('local_travel', 'Local travel', 1),

  ('national_travel', 'Déplacements nationaux', 2),

  ('national_travel', 'National travel', 1),

  ('international_travel', 'Déplacements internationaux', 2),

  ('international_travel', 'International travel', 1),

  ('yes_with_limit', 'Oui avec un plafond défini', 2),

  ('yes_with_limit', 'Yes with a defined limit', 1),

  ('yes_without_limit', 'Oui sans un plafond défini', 2),

  ('yes_without_limit', 'Yes without a defined limit', 1),

  ('conditional', 'Sous conditions (précisez)', 2),

  ('conditional', 'Under conditions (specify)', 1),

  ('expense_receipts', 'Notes de frais et reçus uniquement', 2),

  ('expense_receipts', 'Expense receipts only', 1),

  ('daily_allowance', 'Indemnité journalière uniquement', 2),

  ('daily_allowance', 'Daily allowance only', 1),

  ('both_expense_allowance', 'Les notes de frais/reçus et l''indemnité journalière', 2),

  ('both_expense_allowance', 'Both expense receipts and daily allowance', 1),

  ('local', 'Local', 2),

  ('local', 'Local', 1),

  ('national', 'National', 2),

  ('national', 'National', 1),

  ('africa', 'Afrique', 2),

  ('africa', 'Africa', 1),

  ('asia_pacific', 'Asie-Pacifique', 2),

  ('asia_pacific', 'Asia-Pacific', 1),

  ('europe', 'Europe', 2),

  ('europe', 'Europe', 1),

  ('latin_america', 'Amérique latine', 2),

  ('latin_america', 'Latin America', 1),

  ('middle_east', 'Moyen-Orient', 2),

  ('middle_east', 'Middle East', 1),

  ('north_america', 'Amérique du Nord', 2),

  ('north_america', 'North America', 1),

  ('southeast_asia', 'Asie du Sud-Est', 2),

  ('southeast_asia', 'Southeast Asia', 1),

  ('transport', 'Transport (avion, train, voiture)', 2),

  ('transport', 'Transport (plane, train, car)', 1),

  ('accommodation', 'Hébergement', 2),

  ('accommodation', 'Accommodation', 1),

  ('meals', 'Repas', 2),

  ('meals', 'Meals', 1),

  ('communications', 'Communications (téléphone, internet)', 2),

  ('communications', 'Communications (phone, internet)', 1),

  ('other_expenses', 'Autres frais (précisez)', 2),

  ('other_expenses', 'Other expenses (specify)', 1),

  ('fully', 'Entièrement', 2),

  ('fully', 'Fully', 1),

  ('partially', 'Partiellement', 2),

  ('partially', 'Partially', 1),

  ('air_transport', 'Oui, pour le transport aérien', 2),

  ('air_transport', 'Yes, for air transport', 1),

  ('rail_transport', 'Oui, pour le transport ferroviaire', 2),

  ('rail_transport', 'Yes, for rail transport', 1),

  ('car_rental', 'Oui, pour les services de location de véhicules', 2),

  ('car_rental', 'Yes, for car rental services', 1),

  ('other_transport', 'Oui, pour d''autres modes de transport (précisez)', 2),

  ('other_transport', 'Yes, for other transport modes (specify)', 1),

  ('preferred_rates', 'Tarifs préférentiels', 2),

  ('preferred_rates', 'Preferred rates', 1),

  ('flexible_conditions', 'Conditions flexibles de modification ou d''annulation', 2),

  ('flexible_conditions', 'Flexible modification or cancellation conditions', 1),

  ('premium_services', 'Services premium', 2),

  ('premium_services', 'Premium services', 1),

  ('national_hotels', 'Oui, pour des hôtels nationaux', 2),

  ('national_hotels', 'Yes, for national hotels', 1),

  ('international_hotels', 'Oui, pour des hôtels internationaux', 2),

  ('international_hotels', 'Yes, for international hotels', 1),

  ('specific_chains', 'Oui, pour des chaînes hôtelières spécifiques', 2),

  ('specific_chains', 'Yes, for specific hotel chains', 1),

  ('reduced_rates', 'Tarifs réduits', 2),

  ('reduced_rates', 'Reduced rates', 1),

  ('room_upgrades', 'Surclassements', 2),

  ('room_upgrades', 'Room upgrades', 1),

  ('free_cancellations', 'Annulations gratuites', 2),

  ('free_cancellations', 'Free cancellations', 1),

  ('loyalty_programs', 'Programmes de fidélité spécifiques', 2),

  ('loyalty_programs', 'Specific loyalty programs', 1),

   ('professional_support', 'Support Professionnel', 2),

  ('professional_support', 'Professional Support', 1);

-- Link items to questions

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

VALUES

  -- Q7-TE-1 (Travel policy)

  ('yes', 'Q7-TE-1', NULL, 0),

  ('no', 'Q7-TE-1', NULL, 0),

  -- Q7-TE-2 (Travel types)

  ('local_travel', 'Q7-TE-2', 'yes', 0),

  ('national_travel', 'Q7-TE-2', 'yes', 0),

  ('international_travel', 'Q7-TE-2', 'yes', 0),

  -- Q7-TE-3 (Local travel staff)

  ('executives', 'Q7-TE-3', 'local_travel', 0),

  ('middle_managers', 'Q7-TE-3', 'local_travel', 0),

  ('managers', 'Q7-TE-3', 'local_travel', 0),

  ('sales', 'Q7-TE-3', 'local_travel', 0),

  ('professionals', 'Q7-TE-3', 'local_travel', 0),

  ('technical-staff', 'Q7-TE-3', 'local_travel', 0),

  ('professional_support', 'Q7-TE-3', 'local_travel', 0),

  ('labor', 'Q7-TE-3', 'local_travel', 0),

  -- Q7-TE-4 (National travel staff)

  ('executives', 'Q7-TE-4', 'national_travel', 0),

  ('middle_managers', 'Q7-TE-4', 'national_travel', 0),

  ('managers', 'Q7-TE-4', 'national_travel', 0),

  ('sales', 'Q7-TE-4', 'national_travel', 0),

  ('professionals', 'Q7-TE-4', 'national_travel', 0),

  ('technical-staff', 'Q7-TE-4', 'national_travel', 0),

  ('professional_support', 'Q7-TE-4', 'national_travel', 0),

  ('labor', 'Q7-TE-4', 'national_travel', 0),

  -- Q7-TE-5 (International travel staff)

  ('executives', 'Q7-TE-5', 'international_travel', 0),

  ('middle_managers', 'Q7-TE-5', 'international_travel', 0),

  ('managers', 'Q7-TE-5', 'international_travel', 0),

  ('sales', 'Q7-TE-5', 'international_travel', 0),

  ('professionals', 'Q7-TE-5', 'international_travel', 0),

  ('technical-staff', 'Q7-TE-5', 'international_travel', 0),

  ('professional_support', 'Q7-TE-5', 'international_travel', 0),

  ('labor', 'Q7-TE-5', 'international_travel', 0),

  -- Q7-TE-6 (Reimbursement policy)

  ('yes_with_limit', 'Q7-TE-6', NULL, 0),

  ('yes_without_limit', 'Q7-TE-6', NULL, 0),

  ('conditional', 'Q7-TE-6', NULL, 1),

  ('no', 'Q7-TE-6', NULL, 0),

  -- Q7-TE-7 (Reimbursement basis)

  ('expense_receipts', 'Q7-TE-7', 'yes_with_limit', 0),

  ('daily_allowance', 'Q7-TE-7', 'yes_with_limit', 0),

  ('both_expense_allowance', 'Q7-TE-7', 'yes_with_limit', 0),

  -- Q7-TE-8 (Daily rates by region)

  ('local', 'Q7-TE-8', 'yes_with_limit', 1),

  ('national', 'Q7-TE-8', 'yes_with_limit', 1),

  ('africa', 'Q7-TE-8', 'yes_with_limit', 1),

  ('asia_pacific', 'Q7-TE-8', 'yes_with_limit', 1),

  ('europe', 'Q7-TE-8', 'yes_with_limit', 1),

  ('latin_america', 'Q7-TE-8', 'yes_with_limit', 1),

  ('middle_east', 'Q7-TE-8', 'yes_with_limit', 1),

  ('north_america', 'Q7-TE-8', 'yes_with_limit', 1),

  ('southeast_asia', 'Q7-TE-8', 'yes_with_limit', 1),

  -- Q7-TE-9 (Covered expenses)

  ('transport', 'Q7-TE-9', 'yes_with_limit', 0),

  ('accommodation', 'Q7-TE-9', 'yes_with_limit', 0),

  ('meals', 'Q7-TE-9', 'yes_with_limit', 0),

  ('communications', 'Q7-TE-9', 'yes_with_limit', 0),

  ('other_expenses', 'Q7-TE-9', 'yes_with_limit', 1),

  -- Q7-TE-10 (Accommodation coverage)

  ('fully', 'Q7-TE-10', 'accommodation', 0),

  ('partially', 'Q7-TE-10', 'accommodation', 0),

  -- Q7-TE-11 (Meal coverage)

  ('fully', 'Q7-TE-11', 'meals', 0),

  ('partially', 'Q7-TE-11', 'meals', 0),

  -- Q7-TE-12 (Transport agreements)

  ('air_transport', 'Q7-TE-12', NULL, 0),

  ('rail_transport', 'Q7-TE-12', NULL, 0),

  ('car_rental', 'Q7-TE-12', NULL, 0),

  ('other_transport', 'Q7-TE-12', NULL, 1),

  ('no', 'Q7-TE-12', NULL, 0),

  -- Q7-TE-13 (Transport benefits)

  ('preferred_rates', 'Q7-TE-13', 'accommodation', 0),

  ('flexible_conditions', 'Q7-TE-13', 'accommodation', 0),

  ('premium_services', 'Q7-TE-13', 'accommodation', 0),

  -- Q7-TE-14 (Hotel agreements)

  ('national_hotels', 'Q7-TE-14', NULL, 0),

  ('international_hotels', 'Q7-TE-14', NULL, 0),

  ('specific_chains', 'Q7-TE-14', NULL, 0),

  ('no', 'Q7-TE-14', NULL, 0),

  -- Q7-TE-15 (Hotel benefits)

  ('reduced_rates', 'Q7-TE-15', 'accommodation', 0),

  ('room_upgrades', 'Q7-TE-15', 'accommodation', 0),

  ('free_cancellations', 'Q7-TE-15', 'accommodation', 0),

  ('loyalty_programs', 'Q7-TE-15', 'accommodation', 0);