-- loes-a11-zmk_kommentiert.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)
--
-- Feinmodule:
-- Hinweis: Datenanzeige von (Roh)daten in [] nur zu Kontrollzwecken!
-- ----------------------
-- (1) U-Stunden des GK11BI2 finden (TagStd-ID dazu):
-- ----------------------
SELECT plan.tagstd_id AS "tagstd-ID", plan.tmp_klaskurs AS "[Kurs]"
 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
;
/* ergibt:
tagstd-ID	[Kurs]
17 	11 G2~BI2
18 	11 G2~BI2
52 	11 G2~BI2
((17 ,11 G2~BI2)
(18 ,11 G2~BI2)
(52 ,11 G2~BI2))
*/

-- ----------------------
-- (2) BI-Lehrer finden:
-- ----------------------
SELECT lehr.lid AS "Lehrer", lehr.kurz AS "[Kürzel]", fach.kurz AS "[Fakultas]"
 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
;
/* ergibt:
Lehrer	[Kürzel]	[Fakultas]
18 	BUSE 	BI
25 	DRE 	BI
38 	HEGG 	BI
49 	KOES 	BI
52 	KRAM 	BI
58 	LNG 	BI
64 	MAZ 	BI
65 	MLK 	BI
75 	OERT 	BI
77 	PFL 	BI
127 	REST 	BI
85 	SEYM 	BI
101 	URB 	BI
114 	WIN2 	BI
*/

-- (3) BI-Lehrer temporär in Tabelle speichern:
-- (Manual v4 Abschnitt 4.5.7 lesen!)
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
;

-- (4) TagStd-ID temporär in tmp_ts speichern:
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;

/*
   (5) Alle mit GK11BI2-Unterricht kollidierenden U-Stunden
       der BI-Lehrer in temporärer Tabelle speichern:
*/

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;

/*
-- (6) Zwischenschritt: Kollisionen und Null-Kollisionen anzeigen
SELECT tmp_bi.lehrer_id, lehr.kurz, tmp_koll.lehrer_id AS "Kollision Lehrer-ID" 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;
*/

-- (7) Ergebnis: BI-Lehrer ohne Kollisionen mit GK11BI2-U-Stunden anzeigen

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;

-- AM SCHLUSS NOCH: Temporäre Tabellen löschen

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