07ab648143
All checks were successful
Deploy to production / deploy (push) Successful in 3m6s
- Added a new hierarchyLevel field to PoliticalOfficeType for better categorization of political roles. - Updated computePoliticalDailySalaryPayout function to incorporate hierarchy level in salary calculations, allowing for more dynamic salary adjustments based on office rank. - Modified SQL scripts to reflect changes in political office benefits, ensuring compatibility with the new salary structure. - Enhanced localization files to support updated benefit descriptions and salary formats across multiple languages. - Improved UI components to display the new salary calculations and benefits accurately in the PoliticsView.
142 lines
5.8 KiB
SQL
142 lines
5.8 KiB
SQL
-- Einzelne Schritte (jeweils in neuer Verbindung oder nach ROLLBACK ausführen,
|
||
-- falls der Client nach einem Fehler „connection closed“ meldet.)
|
||
--
|
||
-- Schritt 0: Schema angleichen (political_office_id vs office_type_id, siehe Haupt-SQL)
|
||
-- Schritt 1: benefit_type Zeilen
|
||
-- Schritt 2–6: tax_exemption
|
||
-- Schritt 7: daily_salary für alle Ämter
|
||
|
||
-- ========== Schritt 0 ==========
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.tables
|
||
WHERE table_schema = 'falukant_predefine' AND table_name = 'political_office_benefit'
|
||
) THEN
|
||
RAISE EXCEPTION 'Tabelle falukant_predefine.political_office_benefit fehlt.';
|
||
END IF;
|
||
IF EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'falukant_predefine'
|
||
AND table_name = 'political_office_benefit'
|
||
AND column_name = 'political_office_id'
|
||
) AND NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'falukant_predefine'
|
||
AND table_name = 'political_office_benefit'
|
||
AND column_name = 'office_type_id'
|
||
) THEN
|
||
ALTER TABLE falukant_predefine.political_office_benefit
|
||
RENAME COLUMN political_office_id TO office_type_id;
|
||
END IF;
|
||
IF EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'falukant_predefine'
|
||
AND table_name = 'political_office_benefit'
|
||
AND column_name = 'political_office_id'
|
||
) AND EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'falukant_predefine'
|
||
AND table_name = 'political_office_benefit'
|
||
AND column_name = 'office_type_id'
|
||
) THEN
|
||
UPDATE falukant_predefine.political_office_benefit
|
||
SET office_type_id = COALESCE(office_type_id, political_office_id);
|
||
ALTER TABLE falukant_predefine.political_office_benefit
|
||
DROP COLUMN political_office_id;
|
||
END IF;
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'falukant_predefine'
|
||
AND table_name = 'political_office_benefit'
|
||
AND column_name = 'office_type_id'
|
||
) THEN
|
||
RAISE EXCEPTION 'Spalte office_type_id fehlt – Migration 20260401120000 ausführen.';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- ========== Schritt 1 ==========
|
||
INSERT INTO falukant_type.political_office_benefit_type (tr)
|
||
SELECT 'tax_exemption'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM falukant_type.political_office_benefit_type t WHERE t.tr = 'tax_exemption'
|
||
);
|
||
|
||
INSERT INTO falukant_type.political_office_benefit_type (tr)
|
||
SELECT 'daily_salary'
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM falukant_type.political_office_benefit_type t WHERE t.tr = 'daily_salary'
|
||
);
|
||
|
||
-- ========== Schritt 2 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{"regions":["city"]}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'tax_exemption'
|
||
WHERE ot.name = 'council'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|
||
|
||
-- ========== Schritt 3 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{"regions":["city","county"]}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'tax_exemption'
|
||
WHERE ot.name = 'taxman'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|
||
|
||
-- ========== Schritt 4 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{"regions":["city","county","shire"]}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'tax_exemption'
|
||
WHERE ot.name = 'treasurer'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|
||
|
||
-- ========== Schritt 5 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{"regions":["city","county","shire","markgrave","duchy"]}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'tax_exemption'
|
||
WHERE ot.name = 'super-state-administrator'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|
||
|
||
-- ========== Schritt 6 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{"regions":["*"]}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'tax_exemption'
|
||
WHERE ot.name = 'chancellor'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|
||
|
||
-- ========== Schritt 7 ==========
|
||
INSERT INTO falukant_predefine.political_office_benefit (office_type_id, benefit_type_id, value)
|
||
SELECT ot.id, bt.id, '{}'::jsonb
|
||
FROM falukant_type.political_office_type ot
|
||
JOIN falukant_type.political_office_benefit_type bt ON bt.tr = 'daily_salary'
|
||
WHERE ot.name IN (
|
||
'assessor', 'councillor', 'council', 'beadle', 'town-clerk', 'mayor',
|
||
'master-builder', 'village-major', 'judge', 'bailif', 'taxman', 'sheriff',
|
||
'consultant', 'treasurer', 'hangman', 'territorial-council',
|
||
'territorial-council-speaker', 'ruler-consultant', 'state-administrator',
|
||
'super-state-administrator', 'governor', 'ministry-helper', 'minister', 'chancellor'
|
||
)
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM falukant_predefine.political_office_benefit x
|
||
WHERE x.office_type_id = ot.id AND x.benefit_type_id = bt.id
|
||
);
|