-- loes_a12-alm (mod by ZMK)
/* Gewünschtes Fach speichern: */
-- 
SELECT @kursfach:="I1", @fakultas:="I";

/* Alle Stunden der 11 Ital Kurse */

CREATE TEMPORARY TABLE `alm-itrasta`(
 grp_id INT(2) NOT NULL
);

INSERT INTO `alm-itrasta` 
SELECT kukla.grp_id FROM `llg-stuplan` AS sp
 INNER JOIN `llg-kurs` AS ku
  ON ku.kuid = sp.kurs_id
 INNER JOIN `llg-kursklasse` AS kukla
  ON kukla.kkid = ku.kukla_id
 INNER JOIN `llg-kursgrp` AS kg
  ON kg.kgid = kukla.grp_id
 INNER JOIN `llg-fach` AS fa
   ON fa.fid = ku.fach_id
WHERE (left(fa.kurz,2) = @kursfach) AND (kukla.stufe = '11')
GROUP BY kukla.grp_id;

/* Alle Stunden der 11 inkl Rasterzuordnung */

CREATE TEMPORARY TABLE `alm-krasta`(
 grp_id INT(2) NOT NULL
);

INSERT INTO `alm-krasta`
SELECT kukla.grp_id FROM `llg-stuplan` AS sp
 INNER JOIN `llg-kurs` AS ku
  ON ku.kuid = sp.kurs_id
 INNER JOIN `llg-kursklasse` AS kukla
  ON kukla.kkid = ku.kukla_id
 INNER JOIN `llg-kursgrp` AS kg
  ON kg.kgid = kukla.grp_id
 WHERE (kukla.stufe = 11) AND (left(kg.kurz,1) = 'G') AND NOT (left(kg.kurz,2) = 'g ') AND NOT (left(kg.kurz,2) = 'G0')
 GROUP BY kukla.grp_id;

-- Die beiden entspr. SUBTRAHIEREN -------

CREATE TEMPORARY TABLE `alm-gutrasta`(
 grp_id INT(2) NOT NULL
);

INSERT INTO `alm-gutrasta`
 SELECT kr.grp_id FROM `alm-krasta` AS kr
 LEFT JOIN `alm-itrasta` AS it
  ON it.grp_id = kr.grp_id
  WHERE it.grp_id IS NULL;

-- --------------------------------------

/* alle Stunden der Italienischlehrer...*/

CREATE TEMPORARY TABLE `alm-lrasta`(
 tsid INT(3) NOT NULL,
 lehr_id INT(3) NOT NULL
);

INSERT INTO `alm-lrasta`
SELECT sp.tagstd_id,sp.lehrer_id FROM `llg-fakultas` AS fk
  INNER JOIN `llg-lehrer` AS le
   ON le.lid = fk.lehrer_id
  INNER JOIN `llg-fach` AS fa
   ON fa.fid = fk.fach_id
  INNER JOIN `llg-stuplan` AS sp
   ON sp.lehrer_id = le.lid
WHERE left(fa.kurz,2) = @fakultas
 GROUP BY le.kurz,sp.tagstd_id;

CREATE TEMPORARY TABLE `alm-tsiddvs`(
 tsid INT(3) NOT NULL,
 grp_id INT(2) NOT NULL
);

INSERT INTO `alm-tsiddvs`
 SELECT tagstd_id,kukla.grp_id FROM `llg-stuplan` AS sp
  INNER JOIN `llg-kurs` AS ku
   ON ku.kuid = sp.kurs_id
  INNER JOIN `llg-kursklasse` AS kukla
   ON kukla.kkid = ku.kukla_id
  INNER JOIN `llg-kursgrp` AS kg
   ON kg.kgid = kukla.grp_id
  LEFT JOIN `alm-gutrasta` AS gr
   ON kukla.grp_id = gr.grp_id 
  WHERE (kukla.stufe = 11) AND (left(kg.kurz,1) = 'G') AND NOT (left(kg.kurz,2) = 'g ') AND NOT (left(kg.kurz,2) = 'G0') AND NOT (SUBSTRING(ku.lang,4,1)='Y') AND gr.grp_id IS NOT NULL
 GROUP BY kukla.grp_id,tagstd_id;


/* Kollisionen zwischen Lehrern und den freien Rastegruppen ermitteln */


CREATE TEMPORARY TABLE `alm-kol`(
 lehr_id INT(3) NOT NULL,
 grp_id INT(2) NOT NULL
);

INSERT INTO `alm-kol` 
SELECT lehr_id,gr.grp_id FROM `alm-lrasta` AS lr
 LEFT JOIN `alm-tsiddvs` AS gr
  ON lr.tsid = gr.tsid
  WHERE gr.tsid IS NOT NULL
 GROUP BY lehr_id,gr.grp_id;

-- Die Ausgabe vorbereiten

CREATE TEMPORARY TABLE `alm-ausgabe`(
 vrast VARCHAR(40),
 vlehr VARCHAR(40),
 lehr VARCHAR(40),
 rast VARCHAR(40)
);

INSERT INTO `alm-ausgabe` (vrast)
   SELECT kg.lang FROM `alm-gutrasta` AS kl 
    INNER JOIN `llg-kursgrp` AS kg 
     ON kg.kgid = kl.grp_id
    GROUP BY kl.grp_id;

INSERT INTO `alm-ausgabe` (vlehr)
   SELECT le.lang FROM `alm-lrasta` AS kl 
    INNER JOIN `llg-lehrer` AS le 
     ON le.lid = kl.lehr_id 
    GROUP BY le.lang;
 
INSERT INTO `alm-ausgabe` (lehr,rast)
 SELECT le.lang , kg.lang FROM `alm-kol` AS kl 
   INNER JOIN `llg-kursgrp` AS kg 
    ON kg.kgid = kl.grp_id 
   INNER JOIN `llg-lehrer` AS le 
    ON le.lid = kl.lehr_id
  ORDER BY kl.grp_id,le.lang;  

SELECT vrast AS 'Die verfügbaren Rastergruppen',vlehr 'Verfügbare Lehrer', lehr AS 'Die Lehrkraft', rast AS 'ist in diesen für den Kurs möglichen Rastergruppen nicht verfügbar' FROM `alm-ausgabe`
LIMIT 0,200; ;