-- loes-a11-zmk.sql
--
-- Grobalgorithmus:
-- (1) Lage der U-Stunden des GK11BI2 finden und speichern
-- (2) BI-Lehrer finden und speichern
-- (3) Unterrichtsstunden dieser BI-Lehrer finden und speichern
-- (4) Unterrichts-Kollisionen zwischen GK11BI2 und BI-Lehrer finden und speichern
-- (5) BI-Lehrer ohne Kollisionen ausgeben
--
-- HINWEIS: Zum Speichern werden temporäre Tabellen verwendet
-- (siehe im Manual v4 das Beispiel im Abschnitt 4.5.7)
--

CREATE TEMPORARY TABLE tmp_bi (
  lehrer_id INT(3) NOT NULL
  );

INSERT INTO tmp_bi
  SELECT lehr.lid
    FROM `llg-lehrer`as lehr
      INNER JOIN `llg-fakultas` AS fak ON lehr.lid = fak.lehrer_id
      INNER JOIN `llg-fach` AS fach ON fak.fach_id = fach.fid
   WHERE fach.kurz="BI"
   ORDER BY lehr.kurz;

CREATE TEMPORARY TABLE tmp_ts (
  tagstd_id INT(3) NOT NULL
  );

INSERT INTO tmp_ts
  SELECT plan.tagstd_id
    FROM `llg-stuplan` AS plan
      INNER JOIN `llg-kurs`AS kurs ON plan.kurs_id = kurs.kuid
      INNER JOIN `llg-kursklasse` AS kukla ON kurs.kukla_id = kukla.kkid
      INNER JOIN `llg-fach` AS fach ON kurs.fach_id = fach.fid
      INNER JOIN `llg-kursart`AS art ON kukla.art_id = kaid
    WHERE kukla.stufe = 11
      AND art.kurz = "GK"
      AND fach.kurz = "BI"
      AND kurs.kursnr = 2;

CREATE TEMPORARY TABLE tmp_koll (
  tagstd_id INT(3) NOT NULL,
  lehrer_id CHAR(5) NOT NULL
  );

INSERT INTO tmp_koll
  SELECT plan.tagstd_id, plan.lehrer_id
    FROM `llg-stuplan` AS plan
    INNER JOIN `llg-lehrer` AS lehr ON plan.lehrer_id = lehr.lid
    INNER JOIN tmp_bi ON plan.lehrer_id = tmp_bi.lehrer_id
    INNER JOIN tmp_ts ON plan.tagstd_id = tmp_ts.tagstd_id;

SELECT tmp_bi.lehrer_id AS "Mögliche", lehr.kurz AS "Vertreter:" FROM tmp_bi
  INNER JOIN `llg-lehrer` AS lehr ON tmp_bi.lehrer_id = lehr.lid
  LEFT JOIN tmp_koll ON tmp_bi.lehrer_id = tmp_koll.lehrer_id
WHERE tmp_koll.lehrer_id IS NULL;

-- DROP TABLE tmp_bi;
-- DROP TABLE tmp_ts;
-- DROP TABLE tmp_koll;