96 lines
3.6 KiB
SQL
96 lines
3.6 KiB
SQL
CREATE TABLE IF NOT EXISTS department_catalog (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(80) NOT NULL UNIQUE,
|
|
label VARCHAR(120) NOT NULL,
|
|
description TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 1000,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_by BIGINT REFERENCES app_user(id),
|
|
updated_by BIGINT REFERENCES app_user(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_department_catalog_updated_at') THEN
|
|
CREATE TRIGGER trg_department_catalog_updated_at
|
|
BEFORE UPDATE ON department_catalog
|
|
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
|
|
END IF;
|
|
END $$;
|
|
|
|
INSERT INTO department_catalog(code, label, sort_order)
|
|
VALUES
|
|
('it', 'IT', 10),
|
|
('personal', 'Personal', 20),
|
|
('fertigung', 'Fertigung', 30),
|
|
('qualitaet', 'Qualität', 40),
|
|
('logistik', 'Logistik', 50),
|
|
('einkauf', 'Einkauf', 60),
|
|
('verwaltung', 'Verwaltung', 70),
|
|
('sonstiges', 'Sonstiges', 999)
|
|
ON CONFLICT (code) DO UPDATE
|
|
SET label = EXCLUDED.label,
|
|
sort_order = EXCLUDED.sort_order,
|
|
is_active = TRUE;
|
|
|
|
INSERT INTO department_catalog(code, label, sort_order)
|
|
SELECT DISTINCT
|
|
lower(regexp_replace(regexp_replace(trim(department), '[^[:alnum:]]+', '_', 'g'), '^_+|_+$', '', 'g')) AS code,
|
|
trim(department) AS label,
|
|
500
|
|
FROM br_case
|
|
WHERE department IS NOT NULL
|
|
AND trim(department) <> ''
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM department_catalog d
|
|
WHERE d.code = lower(regexp_replace(regexp_replace(trim(br_case.department), '[^[:alnum:]]+', '_', 'g'), '^_+|_+$', '', 'g'))
|
|
OR d.code = trim(br_case.department)
|
|
)
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
|
|
VALUES ('departments', '/departments', 'Abteilungen', 'admin', 215, 'Abteilungen für Vorgänge pflegen')
|
|
ON CONFLICT (page_key) DO UPDATE
|
|
SET path = EXCLUDED.path,
|
|
label = EXCLUDED.label,
|
|
nav_group = EXCLUDED.nav_group,
|
|
sort_order = EXCLUDED.sort_order,
|
|
description = EXCLUDED.description;
|
|
|
|
INSERT INTO app_permission(permission_key, label, category, description)
|
|
VALUES ('departments.manage', 'Abteilungen verwalten', 'Administration', 'Abteilungen anlegen, bearbeiten und deaktivieren/löschen')
|
|
ON CONFLICT (permission_key) DO UPDATE
|
|
SET label = EXCLUDED.label,
|
|
category = EXCLUDED.category,
|
|
description = EXCLUDED.description;
|
|
|
|
INSERT INTO app_role_page(role_profile_id, page_key, can_view)
|
|
SELECT id, 'departments', TRUE
|
|
FROM app_role_profile
|
|
WHERE code IN ('admin', 'chair')
|
|
ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;
|
|
|
|
INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
|
|
SELECT id, 'departments.manage', TRUE
|
|
FROM app_role_profile
|
|
WHERE code IN ('admin', 'chair')
|
|
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;
|
|
|
|
ALTER TABLE department_catalog ENABLE ROW LEVEL SECURITY;
|
|
|
|
DROP POLICY IF EXISTS department_catalog_select ON department_catalog;
|
|
DROP POLICY IF EXISTS department_catalog_modify ON department_catalog;
|
|
CREATE POLICY department_catalog_select ON department_catalog FOR SELECT USING (
|
|
app_can_view_page('departments')
|
|
OR app_has_permission('departments.manage')
|
|
OR app_has_permission('cases.write')
|
|
OR app_can_view_page('cases')
|
|
);
|
|
CREATE POLICY department_catalog_modify ON department_catalog FOR ALL USING (app_has_permission('departments.manage')) WITH CHECK (app_has_permission('departments.manage'));
|
|
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON department_catalog TO br_app;
|
|
GRANT USAGE, SELECT ON SEQUENCE department_catalog_id_seq TO br_app;
|