125 lines
7.3 KiB
SQL
125 lines
7.3 KiB
SQL
-- MSIN-4469 — Production Excel des dossards
|
||
-- Prérequis : MSIN-4467-refonte-visuelle-dossards.sql.
|
||
-- Idempotent. Exécuter avant le déploiement PHP et avant le premier test.
|
||
|
||
CREATE TABLE IF NOT EXISTS `inscriptions_bib_production_orders` (
|
||
`bpo_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`eve_id` INT NOT NULL,
|
||
`bpo_com_id` INT NOT NULL DEFAULT 0,
|
||
`bpo_usa_id` INT NOT NULL DEFAULT 0,
|
||
`bpo_status` VARCHAR(24) NOT NULL DEFAULT 'generating',
|
||
`bpo_active_key` TINYINT UNSIGNED NULL DEFAULT NULL
|
||
COMMENT '1 = commande active; NULL = historique (unicité MySQL par événement)',
|
||
`bpo_version` INT UNSIGNED NOT NULL DEFAULT 1,
|
||
`bpo_file_name` VARCHAR(255) NOT NULL DEFAULT '',
|
||
`bpo_file_path` VARCHAR(500) NOT NULL DEFAULT '',
|
||
`bpo_sha256` CHAR(64) NOT NULL DEFAULT '',
|
||
`bpo_snapshot` LONGTEXT NULL,
|
||
`bpo_created_at` DATETIME NOT NULL,
|
||
`bpo_created_by` VARCHAR(128) NOT NULL DEFAULT '',
|
||
`bpo_superseded_at` DATETIME NULL DEFAULT NULL,
|
||
`bpo_superseded_by` VARCHAR(128) NOT NULL DEFAULT '',
|
||
`bpo_superseded_reason` VARCHAR(500) NOT NULL DEFAULT '',
|
||
`bpo_error` VARCHAR(500) NOT NULL DEFAULT '',
|
||
PRIMARY KEY (`bpo_id`),
|
||
UNIQUE KEY `uq_bpo_active_event` (`eve_id`, `bpo_active_key`),
|
||
KEY `idx_bpo_event_history` (`eve_id`, `bpo_version`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
||
COMMENT='MSIN-4469 — fichiers officiels de production des dossards';
|
||
|
||
CREATE TABLE IF NOT EXISTS `inscriptions_bib_production_ranges` (
|
||
`bpr_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`bpo_id` INT UNSIGNED NOT NULL,
|
||
`eve_id` INT NOT NULL,
|
||
`epr_id` INT NOT NULL,
|
||
`epr_bib_id` INT NOT NULL,
|
||
`bpr_epr_label` VARCHAR(255) NOT NULL DEFAULT '',
|
||
`bpr_start` INT UNSIGNED NOT NULL,
|
||
`bpr_finish` INT UNSIGNED NOT NULL,
|
||
`bpr_quantity` INT UNSIGNED NOT NULL,
|
||
PRIMARY KEY (`bpr_id`),
|
||
UNIQUE KEY `uq_bpr_order_range` (`bpo_id`, `epr_bib_id`),
|
||
KEY `idx_bpr_event` (`eve_id`),
|
||
KEY `idx_bpr_epr` (`epr_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
||
COMMENT='MSIN-4469 — instantané des séquences incluses dans une commande';
|
||
|
||
INSERT INTO info (
|
||
info_clef,
|
||
info_langue,
|
||
info_texte,
|
||
info_aide,
|
||
info_prg,
|
||
info_description,
|
||
info_trie,
|
||
info_actif,
|
||
info_option1,
|
||
info_option2,
|
||
info_option3,
|
||
info_creation
|
||
)
|
||
SELECT
|
||
src.info_clef,
|
||
src.info_langue,
|
||
src.info_texte,
|
||
src.info_aide,
|
||
'compte.php',
|
||
'MSIN-4469',
|
||
0,
|
||
1,
|
||
'',
|
||
'',
|
||
'',
|
||
NOW()
|
||
FROM (
|
||
SELECT 'bib_v5_production_excel_title' info_clef, 'fr' info_langue, 'Fichier Excel pour l’imprimeur' info_texte, 'Crée le fichier officiel associé à l’événement et verrouille les séquences utilisées.' info_aide
|
||
UNION ALL SELECT 'bib_v5_production_excel_title', 'en', 'Printer Excel file', 'Creates the official event file and locks the ranges used.'
|
||
UNION ALL SELECT 'bib_v5_production_excel_intro', 'fr', 'Le fichier contient un onglet Imprimeur et un onglet Commande MS1.', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_intro', 'en', 'The file contains a Printer sheet and an MS1 Order sheet.', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generate', 'fr', 'Générer et verrouiller', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generate', 'en', 'Generate and lock', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generate_help', 'fr', 'Cette action crée le fichier officiel et verrouille les séquences pour l’impression.', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generate_help', 'en', 'This creates the official file and locks the ranges for printing.', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_download', 'fr', 'Télécharger de nouveau', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_download', 'en', 'Download again', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_locked', 'fr', 'En impression — séquences verrouillées', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_locked', 'en', 'In print — ranges locked', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_meta', 'fr', 'Version %d créée le %s par %s', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_meta', 'en', 'Version %d created on %s by %s', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generating', 'fr', 'Génération en cours — rechargez la page dans quelques instants.', ''
|
||
UNION ALL SELECT 'bib_v5_production_excel_generating', 'en', 'Generation in progress — reload the page shortly.', ''
|
||
UNION ALL SELECT 'bib_v5_production_no_ranges', 'fr', 'Aucune séquence de dossards à produire.', ''
|
||
UNION ALL SELECT 'bib_v5_production_no_ranges', 'en', 'No bib ranges to produce.', ''
|
||
UNION ALL SELECT 'bib_v5_production_event_invalid', 'fr', 'Événement introuvable.', ''
|
||
UNION ALL SELECT 'bib_v5_production_event_invalid', 'en', 'Event not found.', ''
|
||
UNION ALL SELECT 'bib_v5_production_range_invalid', 'fr', 'Une séquence de dossards est invalide.', ''
|
||
UNION ALL SELECT 'bib_v5_production_range_invalid', 'en', 'A bib range is invalid.', ''
|
||
UNION ALL SELECT 'bib_v5_production_generation_error', 'fr', 'Impossible de générer le fichier Excel.', ''
|
||
UNION ALL SELECT 'bib_v5_production_generation_error', 'en', 'Unable to generate the Excel file.', ''
|
||
UNION ALL SELECT 'bib_v5_production_file_missing', 'fr', 'Le fichier Excel enregistré est introuvable.', ''
|
||
UNION ALL SELECT 'bib_v5_production_file_missing', 'en', 'The saved Excel file could not be found.', ''
|
||
UNION ALL SELECT 'bib_v5_production_col_bib', 'fr', 'Numéro de dossard', ''
|
||
UNION ALL SELECT 'bib_v5_production_col_bib', 'en', 'Bib number', ''
|
||
UNION ALL SELECT 'bib_v5_production_col_race', 'fr', 'Épreuve', ''
|
||
UNION ALL SELECT 'bib_v5_production_col_race', 'en', 'Race', ''
|
||
UNION ALL SELECT 'bib_v5_production_total', 'fr', 'TOTAL', ''
|
||
UNION ALL SELECT 'bib_v5_production_total', 'en', 'TOTAL', ''
|
||
UNION ALL SELECT 'bib_v5_production_locked_error', 'fr', 'Les séquences sont verrouillées parce que le fichier est en impression.', ''
|
||
UNION ALL SELECT 'bib_v5_production_locked_error', 'en', 'The ranges are locked because the file is in print.', ''
|
||
UNION ALL SELECT 'bib_v5_production_print_lock_col', 'fr', 'Impression', 'Indique que la séquence est figée dans le fichier officiel transmis à l’imprimeur.'
|
||
UNION ALL SELECT 'bib_v5_production_print_lock_col', 'en', 'Printing', 'Indicates that the range is frozen in the official printer file.'
|
||
UNION ALL SELECT 'bib_v5_production_print_lock_tip', 'fr', 'Verrouillé pour l’impression : cette séquence ne peut plus être modifiée.', ''
|
||
UNION ALL SELECT 'bib_v5_production_print_lock_tip', 'en', 'Locked for printing: this range can no longer be modified.', ''
|
||
UNION ALL SELECT 'bib_v5_production_dev_unlock', 'fr', 'Déverrouiller pour le développement', ''
|
||
UNION ALL SELECT 'bib_v5_production_dev_unlock', 'en', 'Unlock for development', ''
|
||
UNION ALL SELECT 'bib_v5_production_dev_unlock_help', 'fr', 'Réactive temporairement la modification de toutes les séquences de cet événement.', ''
|
||
UNION ALL SELECT 'bib_v5_production_dev_unlock_help', 'en', 'Temporarily enables editing for all ranges in this event.', ''
|
||
) src
|
||
WHERE NOT EXISTS (
|
||
SELECT 1
|
||
FROM info current_info
|
||
WHERE current_info.info_clef = src.info_clef
|
||
AND current_info.info_langue = src.info_langue
|
||
AND current_info.info_prg = 'compte.php'
|
||
);
|