<?php
/**
 * @copyright (c) Kufer Software GmbH, Loisachstrasse 4, D-84453 Mühldorf
 */

$content
= "
DROP PROCEDURE IF EXISTS StrukturFeldVorhanden;
<brscript>
CREATE PROCEDURE StrukturFeldVorhanden(Tabelle VARCHAR(100), Feld VARCHAR(100), OUT Vorhanden Bool)
SQL SECURITY INVOKER
BEGIN
  SELECT DATABASE() INTO @t_datenbank;
  SET @t_vorhanden = '';
  SELECT CONVERT(column_name USING latin1) INTO @t_vorhanden
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE CONVERT(table_name USING latin1) = CONVERT(Tabelle USING latin1)
  AND CONVERT(column_name USING latin1) = CONVERT(Feld USING latin1)
  AND CONVERT(table_schema USING latin1) = CONVERT(@t_datenbank USING latin1);
  SET Vorhanden = (CONVERT(@t_vorhanden USING latin1) = CONVERT(Feld USING latin1));
END;
</brscript>
DROP PROCEDURE IF EXISTS StrukturFeldLaenge;
<brscript>
CREATE PROCEDURE StrukturFeldLaenge(Tabelle VARCHAR(100), Feld VARCHAR(100), OUT Laenge INT)
SQL SECURITY INVOKER
BEGIN
  SELECT DATABASE() INTO @t_datenbank;
  SET @t_laenge = 0;
  SELECT character_maximum_length INTO @t_laenge
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE CONVERT(table_name USING latin1) = CONVERT(Tabelle USING latin1)
  AND CONVERT(column_name USING latin1) = CONVERT(Feld USING latin1)
  AND CONVERT(table_schema USING latin1) = CONVERT(@t_datenbank USING latin1);
  SET Laenge = @t_laenge;
END;
</brscript>
DROP PROCEDURE IF EXISTS StrukturFeldHinzufuegen;
<brscript>
CREATE PROCEDURE StrukturFeldHinzufuegen(Tabelle VARCHAR(100), Feld VARCHAR(100), Typ VARCHAR(100), Laenge VARCHAR(100))
SQL SECURITY INVOKER
BEGIN
  SET @t_vorhanden = False;
  CALL StrukturFeldVorhanden(Tabelle,Feld,@t_vorhanden);
  IF @t_vorhanden = False THEN
    IF Typ = 'VARCHAR' THEN
      SET @sql = CONCAT('ALTER TABLE ', Tabelle, ' ADD ', Feld, ' VARCHAR(',Laenge,')');
    ELSE
      SET @sql = CONCAT('ALTER TABLE ', Tabelle, ' ADD ', Feld, ' ' , Typ);
    END IF;
    PREPARE cmd FROM @sql;
    EXECUTE cmd;
    DEALLOCATE PREPARE cmd;
  END IF;
END;
</brscript>
DROP PROCEDURE IF EXISTS StrukturFeldVerlaengern;
<brscript>
CREATE PROCEDURE StrukturFeldVerlaengern(Tabelle VARCHAR(100), Feld VARCHAR(100), Typ VARCHAR(100), Laenge VARCHAR(100))
SQL SECURITY INVOKER
BEGIN
  SET @t_vorhanden = False;
  CALL StrukturFeldVorhanden(Tabelle,Feld,@t_vorhanden);
  IF @t_vorhanden THEN
    SET @sql = CONCAT('ALTER TABLE ', Tabelle, ' MODIFY ', Feld, ' VARCHAR(',Laenge,')');
    PREPARE cmd FROM @sql;
    EXECUTE cmd;
    DEALLOCATE PREPARE cmd;
  END IF;
END;
</brscript>
";



$content
.= "DROP FUNCTION IF EXISTS check_area_datetime;
<brscript>
CREATE FUNCTION check_area_datetime(pValue1 DATETIME, pValue2 DATETIME, pArea1 DATETIME, pArea2 DATETIME)
RETURNS VARCHAR(1)
BEGIN
  SET pValue1=IfNULL(pValue1,'1899-12-30');
  SET pValue2=IfNULL(pValue2,'1899-12-30');
  SET pArea1=IfNULL(pArea1,'1899-12-30');
  SET pArea2=IfNULL(pArea2,'1899-12-30');
  RETURN (CASE WHEN (((pValue1>=pArea1) AND (pValue1<pArea2) AND (pValue2<=pArea2) AND (pValue2>pArea1))
                  OR ((pValue1<=pArea1) AND (pValue1<pArea2) AND (pValue2<=pArea2) AND (pValue2>pArea1))
                  OR ((pValue1>=pArea1) AND (pValue1<pArea2) AND (pValue2>=pArea2) AND (pValue2>pArea1))
                  OR ((pValue1<=pArea1) AND (pValue1<pArea2) AND (pValue2>=pArea2) AND (pValue2>pArea1)))
               THEN 'W'
               ELSE 'F'
               END);
END;
</brscript>
DROP FUNCTION IF EXISTS anw_prozent;
<brscript>
CREATE FUNCTION anw_prozent (pBelegID INT, pVon DATETIME, pBis DATETIME, pKnr VARCHAR(10), pStatus VARCHAR(3), pKTIDs VARCHAR(2000), pFach INTEGER, pFachIDs VARCHAR(2000))
RETURNS FLOAT
BEGIN
DECLARE t_id INT;
DECLARE t_von DATETIME;
DECLARE t_bis DATETIME;
DECLARE t_pause INT;
DECLARE t_pausevon DATETIME;
DECLARE t_pausebis DATETIME;
DECLARE t_statusok VARCHAR(1);
DECLARE t_insert VARCHAR(1);
DECLARE t_x VARCHAR(1);
DECLARE t_evon DATETIME;
DECLARE t_ebis DATETIME;
DECLARE t_estatusok VARCHAR(1);
DECLARE t_timevon DATETIME;
DECLARE t_timebis DATETIME;
DECLARE t_value FLOAT;
DECLARE t_result FLOAT;
DECLARE t_cid INT; /* eigene Variable, nicht aus MSSQL stored function */
DECLARE t_cvon DATETIME;
DECLARE t_cbis DATETIME;
DECLARE t_cstatusok VARCHAR(1);
DECLARE t_cfaktor INT;
DECLARE t_canteil INT;
DECLARE t_fid INT; /* eigene Variable, nicht aus MSSQL stored function */
DECLARE t_fvon DATETIME;
DECLARE t_fbis DATETIME;
DECLARE t_diff1 FLOAT;
DECLARE t_diff2 FLOAT;
DECLARE t_count FLOAT;
DECLARE t_status VARCHAR(3);
DECLARE t_datum DATETIME;
DECLARE t_first_kt DATETIME;
DECLARE t_last_kt DATETIME;
DECLARE t_calcmethod VARCHAR(1);
DECLARE t_kursustdmin INT;
DECLARE t_ue FLOAT;
DECLARE t_uesum FLOAT;
DECLARE t_s234 VARCHAR(1);
DECLARE t_s467 VARCHAR(1);
DECLARE done INT DEFAULT 0; /* eigene Variable, nicht aus MSSQL stored function */
DECLARE anw_status_cursor CURSOR
FOR SELECT id, von, bis, statusok, faktor, anteil FROM t_statustable;
DECLARE anw_free_cursor CURSOR
FOR SELECT id, von, bis FROM t_freetable;
DECLARE anw_ue_cursor CURSOR
FOR SELECT
       IfNULL(a.id,0)
      ,kt.datum
      ,kt.zeit_beg
      ,kt.zeit_ende
      ,kt.pause
      ,kt.pausebeginn
      ,kt_ue_minuten(k.bamf,kt.minuten,kt_ue_ustdmin(k.bamf,t_kursustdmin,fach_ustdmin(t_s467,kt.fachid)))
      ,IfNULL((CASE WHEN IfNULL(pStatus,' ')=' '
                    THEN IfNULL((CASE WHEN IfNULL(a.astatus,' ') IN (SELECT IfNULL(ag.status,' ') FROM anw_hl_gruende ag WHERE IfNULL(ag.anwesend,' ')='W') 
                    THEN 'W' ELSE 'F' END),'F')
                    ELSE IfNULL((CASE WHEN IfNULL(a.astatus,' ')=IfNULL(pStatus,' ') THEN 'W' ELSE 'F' END),'F') END),'F')
    FROM belegung b
    INNER JOIN kurse k ON (k.nr=b.knr)
    INNER JOIN kurstage kt ON (kt.knr=b.knr)
    LEFT OUTER JOIN anw a ON ((a.aktid=kt.id) AND (a.abelegid=b.id))
    WHERE (b.id=pBelegID)
    AND ((IfNULL(pVon,'1899-30-12')='1899-30-12') OR (kt.datum>=pVon))
    AND ((IfNULL(pbis,'1899-30-12')='1899-30-12') OR (kt.datum<=pBis))
    AND (kt.datum>=t_first_kt)
    AND (kt.datum<=t_last_kt)
    AND (IfNULL(kt.ausf,' ')<>'A')
    AND ((t_x<>'W') OR (IfNULL(kt.ausf,' ')<>'X' AND IfNULL(kt.ausf,' ')<>'!'))
    AND ((t_s234<>'W') OR (((IfNULL(kt.fachid,0)=0) OR (IfNULL(kt.fachid,0) IN (SELECT tf.fach_id FROM tn_fach tf WHERE (tf.beleg_id=b.id)))) AND ((IfNULL(pFach,0)=0) OR (IfNULL(pFach,0)=(IfNULL(kt.fachid,0))))))
    /*AND ((IfNULL(pFachIDs,'')='') OR (CONCAT('|',IfNULL(pFachIDs,''),'|') LIKE CONCAT('%|',CAST(IfNULL(kt.fachid,0) AS CHAR),'|%')))*/
    /*AND ((IfNULL(pKTIDs,'')='') OR (CONCAT('|',IfNULL(pKTIDs,''),'|') LIKE CONCAT('%|',CAST(kt.id AS CHAR),'|%')))*/;
DECLARE anw_extra_cursor CURSOR
    FOR SELECT 
       evon
      ,ebis
      ,IfNULL((CASE WHEN IfNULL(pStatus,' ')=' '
                    THEN IfNULL((CASE WHEN IfNULL(e.estatus,' ') IN (SELECT IfNULL(ag.status,' ') FROM anw_hl_gruende ag WHERE IfNULL(ag.anwesend,' ')='W') THEN 'W' ELSE 'F' END),'F')
                    ELSE IfNULL((CASE WHEN IfNULL(e.estatus,' ')=IfNULL(pStatus,' ') THEN 'W' ELSE 'F' END),'F') END),'F')
      FROM anw_extra e
      WHERE e.eanwid=t_id;
DECLARE anw_time_cursor CURSOR
    FOR SELECT
       von
      ,bis
      FROM t_timetable;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET t_result=0.0;
SET t_uesum=0.0;
SET t_calcmethod=(SELECT IfNULL(wert,'') FROM hlgrundsys WHERE feld='ANW_CALCAUSGABE');
SET t_count=0.0;
SET t_first_kt=anw_datum_minmax(0,pBelegID,pVon,pBis,pKTIDs,pFach,pFachIDs);
SET t_last_kt=anw_datum_minmax(1,pBelegID,pVon,pBis,pKTIDs,pFach,pFachIDs);
DROP TEMPORARY TABLE IF EXISTS t_statustable;
CREATE TEMPORARY TABLE t_statustable
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  von DATETIME,
  bis DATETIME,
  statusok VARCHAR(1),
  faktor INT,
  anteil INT
);
DROP TEMPORARY TABLE IF EXISTS t_freetable;
CREATE TEMPORARY TABLE t_freetable
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  von DATETIME,
  bis DATETIME
);
DROP TEMPORARY TABLE IF EXISTS t_timetable;
CREATE TEMPORARY TABLE t_timetable
(
  von DATETIME,
  bis DATETIME
);
IF (t_first_kt IS NOT NULL) AND (t_last_kt IS NOT NULL) THEN
  SELECT IfNULL(s234,'F'), IfNULL(s467,'F')
  INTO t_s234, t_s467
  FROM switch;
  SET t_kursustdmin=kurs_ustdmin(pKnr,0);
  SET t_x=IfNULL((SELECT wert FROM hlgrundsys WHERE feld='ANW_KURSTAGEX'),'F');
  OPEN anw_ue_cursor;
  anw_ue_cursor_loop: LOOP
    FETCH NEXT FROM anw_ue_cursor INTO t_id,t_datum,t_von,t_bis,t_pause,t_pausevon,t_ue,t_statusok;
    IF done = 1 THEN
      CLOSE anw_ue_cursor;
      LEAVE anw_ue_cursor_loop;
    END IF;
    IF (t_datum>=t_first_kt) AND (t_datum<=t_last_kt) THEN
      DELETE FROM t_statustable;
      IF t_statusok='W' THEN
        SET t_value=100.0;
      ELSE
        SET t_value=0.0;
      END IF;
      SET t_uesum=t_uesum+IfNULL(t_ue,0.0);
      SET t_diff1=TIMESTAMPDIFF(MINUTE,t_bis,t_von)*1.0;
      SET t_pausebis='1899-12-30 00:00';
      IF t_pause<>0 THEN
        IF t_pausevon<>'1899-12-30 00:00' THEN
          SET t_pausebis=TIMESTAMPADD(MINUTE,t_pause,t_pausevon);
          IF t_pausebis>'1899-12-30 23:59' THEN
            SET t_pausebis='1899-12-30 23:59';
          END IF;
        END IF;
      ELSE
        SET t_pausevon='1899-12-30 00:00';
      END IF;
      SET t_diff1=t_diff1-TIMESTAMPDIFF(MINUTE,t_pausebis,t_pausevon)*1.0;
      IF t_diff1<>0 THEN
        OPEN anw_extra_cursor;
        anw_extra_cursor_loop: LOOP
          FETCH NEXT FROM anw_extra_cursor INTO t_evon,t_ebis,t_estatusok;
          IF done = 1 THEN
            SET done = 0;
            CLOSE anw_extra_cursor;
            LEAVE anw_extra_cursor_loop;
          END IF;
          DELETE FROM t_timetable;
          IF (t_pausevon>=t_von) AND (t_pausevon<t_bis) THEN
            INSERT INTO t_timetable (von,bis) VALUES (t_von,t_pausevon);
            IF (t_pausebis<t_bis) THEN
              INSERT INTO t_timetable (von,bis) VALUES (t_pausebis,t_bis);
            END IF;
          ELSE
            INSERT INTO t_timetable (von,bis) VALUES (t_von,t_bis);
          END IF;
          OPEN anw_time_cursor;
          anw_time_cursor_loop: LOOP
            FETCH NEXT FROM anw_time_cursor INTO t_timevon,t_timebis;
            IF done = 1 THEN
              SET done = 0;
              CLOSE anw_time_cursor;
              LEAVE anw_time_cursor_loop;
            END IF;
            IF check_area_datetime(t_evon,t_ebis,t_timevon,t_timebis)='W' THEN
              SET t_evon=(CASE WHEN t_evon<t_timevon THEN t_timevon ELSE t_evon END);
              SET t_ebis=(CASE WHEN t_ebis>t_timebis THEN t_timebis ELSE t_ebis END);
              DELETE FROM t_freetable;
              INSERT INTO t_freetable (von,bis) VALUES(t_evon,t_ebis);
              wh1: WHILE IfNULL((SELECT COUNT(*) FROM t_freetable),0)>0 DO
                SET t_insert='W';
                OPEN anw_free_cursor;
                FETCH NEXT FROM anw_free_cursor INTO t_fid, t_fvon,t_fbis;
                OPEN anw_status_cursor;
                anw_status_cursor_loop: LOOP
                  FETCH NEXT FROM anw_status_cursor INTO t_cid, t_cvon,t_cbis,t_cstatusok,t_cfaktor,t_canteil;
                  IF (done = 1) THEN
                    SET done = 0;
                    CLOSE anw_status_cursor;
                    LEAVE anw_status_cursor_loop;
                  END IF;
                  IF check_area_datetime(t_fvon,t_fbis,t_cvon,t_cbis)='W' THEN
                    SET t_insert='F';
                    IF (t_fvon=t_cvon) AND (t_fbis=t_cbis) THEN
                      IF t_estatusok='W' THEN
                        SET t_estatusok='W';
                        SET t_canteil=t_canteil+1;
                      END IF;
                      UPDATE t_statustable SET faktor=t_cfaktor+1, statusok=t_cstatusok, anteil=t_canteil WHERE id = t_cid;
                    ELSEIF (t_fvon<=t_cvon) AND (t_fbis>=t_cbis) THEN
                      IF t_fvon<t_cvon THEN
                        INSERT INTO t_freetable (von,bis) VALUES (t_fvon,t_cvon);
                      END IF;
                      IF t_fbis>t_cbis THEN
                        INSERT INTO t_freetable (von,bis) VALUES (t_cbis,t_fbis);
                      END IF;
                      IF t_estatusok='W' THEN
                        SET t_cstatusok='W';
                        SET t_canteil=t_canteil+1;
                      END IF;
                      UPDATE t_statustable SET faktor=t_cfaktor+1, statusok=t_cstatusok, anteil=t_canteil WHERE id = t_cid;
                    ELSEIF (t_cvon<=t_fvon) AND (t_cbis>=t_fbis) THEN
                      IF t_cvon<t_fvon THEN
                        INSERT INTO t_statustable (von,bis,statusok,faktor,anteil) VALUES (t_cvon,t_fvon,t_cstatusok,t_cfaktor,t_canteil);
                      END IF;
                      IF t_cbis>t_fbis THEN
                        INSERT INTO t_statustable (von,bis,statusok,faktor,anteil) VALUES (t_fbis,t_cbis,t_cstatusok,t_cfaktor,t_canteil);
                      END IF;
                      IF t_estatusok='W' THEN
                        SET t_cstatusok='W';
                        SET t_canteil=t_canteil+1;
                      END IF;
                      UPDATE t_statustable SET von=t_fvon, bis=t_fbis, faktor=t_cfaktor+1, statusok=t_cstatusok, anteil=t_canteil WHERE id = t_cid;
                    ELSEIF (t_fvon<t_cvon) THEN
                      INSERT INTO t_freetable (von,bis) VALUES (t_fvon,t_cvon);
                      INSERT INTO t_statustable (von,bis,statusok,faktor,anteil) VALUES (t_fbis,t_cbis,t_cstatusok,t_cfaktor,t_canteil);
                      IF t_estatusok='W' THEN
                        SET t_cstatusok='W';
                        SET t_canteil=t_canteil+1;
                      END IF;
                      UPDATE t_statustable SET bis=t_fbis, faktor=t_cfaktor+1, statusok=t_cstatusok, anteil=t_canteil WHERE id = t_cid;
                    ELSEIF (t_fbis>t_cbis) THEN
                      INSERT INTO t_freetable (von,bis) VALUES (t_cbis,t_fbis);
                      INSERT INTO t_statustable (von,bis,statusok,faktor,anteil) VALUES (t_cvon,t_fvon,t_cstatusok,t_cfaktor,t_canteil);
                      IF t_estatusok='W' THEN
                        SET t_cstatusok='W';
                        SET t_canteil=t_canteil+1;
                      END IF;
                      UPDATE t_statustable SET von=t_fvon, faktor=t_cfaktor+1, statusok=t_cstatusok, anteil=t_canteil WHERE id = t_cid;
                    END IF;
                    CLOSE anw_status_cursor;
                    LEAVE anw_status_cursor_loop;
                  END IF;
                END LOOP anw_status_cursor_loop;
                IF t_insert='W' THEN
                  INSERT INTO t_statustable (von,bis,statusok,faktor,anteil) VALUES (t_fvon,t_fbis,t_estatusok,1,(CASE WHEN t_estatusok='W' THEN 1 ELSE 0 END));
                END IF;
                DELETE FROM t_freetable WHERE id = t_fid;
                CLOSE anw_free_cursor;
              END WHILE wh1;
            END IF;
          END LOOP anw_time_cursor_loop;
        END LOOP anw_extra_cursor_loop;
        OPEN anw_status_cursor;
        anw_status_cursor_loop2: LOOP
          FETCH NEXT FROM anw_status_cursor INTO t_cid, t_cvon,t_cbis,t_cstatusok,t_cfaktor,t_canteil;
          IF done = 1 THEN
            SET done = 0;
            CLOSE anw_status_cursor;
            LEAVE anw_status_cursor_loop2;
          END IF;
          IF t_statusok='W' THEN
            IF t_cstatusok='W' THEN
              IF t_cfaktor-t_canteil>0 THEN
                SET t_diff2=((TIMESTAMPDIFF(MINUTE,t_cbis,t_cvon)*1.0)/(t_cfaktor*1.0))*((t_cfaktor-t_canteil)*1.0);
                SET t_value=t_value-(t_diff2/t_diff1*100.0);
              END IF;
            ELSE
              SET t_diff2=(TIMESTAMPDIFF(MINUTE,t_cbis,t_cvon)*1.0);
              SET t_value=t_value-(t_diff2/t_diff1*100.0);
            END IF;
          ELSE
            IF t_cstatusok='W' THEN
              SET t_diff2=((TIMESTAMPDIFF(MINUTE,t_cbis,t_cvon)*1.0)/(t_cfaktor*1.0))*(t_canteil*1.0);
              SET t_value=t_value+(t_diff2/t_diff1*100.0);
            END IF;
          END IF;
        END loop anw_status_cursor_loop2;
        IF IfNULL(t_calcmethod,'0')='1' THEN
          SET t_result=t_result+(IfNULL(t_ue,0.0)*(CASE WHEN t_value>100 THEN 100.0 WHEN t_value<0 THEN 0.0 ELSE t_value END));
        ELSE
          SET t_result=t_result+(CASE WHEN t_value>100 THEN 100.0 WHEN t_value<0 THEN 0.0 ELSE t_value END);
        END IF;
      END IF;
      SET t_count=t_count+1.0;
    END IF;
  END LOOP anw_ue_cursor_loop;
END IF;
IF t_count=0.0 THEN
  SET t_count=1.0;
END IF;
IF IfNULL(t_calcmethod,'0')='1' THEN
  IF t_uesum=0 THEN
    SET t_result=0;
  ELSE
    SET t_result=t_result/t_uesum;
  END IF;
ELSE
  SET t_result=t_result/t_count;
END IF;
RETURN t_result;
END;
</brscript>
DROP FUNCTION IF EXISTS anw_datum_minmax;
<brscript>
CREATE FUNCTION anw_datum_minmax(pArt INT, pBelegID INT, pVon DATETIME, pBis DATETIME, pKTIDs VARCHAR(2000), pFach INTEGER, pFachIDs VARCHAR(2000))
RETURNS DATETIME
BEGIN
  DECLARE t_min DATETIME;
  DECLARE t_max DATETIME;
  DECLARE t_x VARCHAR(1);
  SET t_x=IfNULL((SELECT wert FROM hlgrundsys WHERE feld='ANW_KURSTAGEX'),'F');
  SELECT MIN(kt.datum), MAX(kt.datum)
  INTO t_min, t_max
  FROM belegung b
  INNER JOIN kurstage kt ON (kt.knr=b.knr)
  LEFT OUTER JOIN anw a ON ((a.aktid=kt.id) AND (a.abelegid=b.id))
  WHERE (b.id=pBelegID)
  AND ((IfNULL(pVon,'1899-12-30')='1899-12-30') OR (kt.datum>=pVon))
  AND ((IfNULL(pbis,'1899-12-30')='1899-12-30') OR (kt.datum<=pBis))
  AND (IfNULL(kt.ausf,' ')<>'A')
  AND ((t_x<>'W') OR (IfNULL(kt.ausf,' ')<>'X' AND IfNULL(kt.ausf,' ')<>'!'))
  AND (IfNULL(a.astatus,'')<>'')
  AND (((SELECT IfNULL(s234,'F') FROM switch)<>'W') OR (((IfNULL(kt.fachid,0)=0) OR (IfNULL(kt.fachid,0) IN (SELECT tf.fach_id FROM tn_fach tf WHERE (tf.beleg_id=b.id)))) AND ((IfNULL(pFach,0)=0) OR (IfNULL(pFach,0)=(IfNULL(kt.fachid,0))))))
  /*AND ((IfNULL(pFachIDs,'')='') OR CONCAT('|', IfNull(pFachIDs,''), '|') LIKE CONCAT('%|', CAST(IfNULL(kt.fachid,0) AS CHAR), '|%'))*/
  /*AND ((IfNULL(pKTIDs,'')='') OR (CONCAT('|', IfNULL(pKTIDs,''), '|') LIKE CONCAT('%|', CAST(kt.id AS CHAR), '|%')))*/;
  IF pArt = 1 THEN 
    RETURN t_max;
  ELSE
  	RETURN t_min;
  END IF;
END;
</brscript>
DROP FUNCTION IF EXISTS kurs_ustdmin;
<brscript>
CREATE FUNCTION kurs_ustdmin(pKnr VARCHAR(10), pStandard INT)
RETURNS INT
BEGIN 
  DECLARE ustdmin INT;
  SET ustdmin= (SELECT IfNULL(ue_abw,0) ue_abw FROM kurse_aa WHERE nr= pKnr);
  IF IfNULL(ustdmin,0) = 0 THEN
    SET ustdmin=pStandard;
  END IF;
  RETURN ustdmin;
END;
</brscript>
DROP FUNCTION IF EXISTS fach_ustdmin;
<brscript>
CREATE FUNCTION fach_ustdmin(pS467 VARCHAR(1), pFach INT)
RETURNS INT
BEGIN
  DECLARE t_ustdmin INT;
  SET t_ustdmin=0;
  IF (IfNULL(pFach,0)<>0) AND (IfNULL(pS467,'F')='W') THEN
    SET t_ustdmin=(SELECT IfNULL(f.praktikumustdmin,0) FROM hlfach f WHERE f.id=pFach);
  END IF;
  RETURN t_ustdmin;
END;
</brscript>
DROP FUNCTION IF EXISTS kt_ue_ustdmin;
<brscript>
CREATE FUNCTION kt_ue_ustdmin(pBAMF VARCHAR(1), pKursUSTDMIN INT, pPraktikumUSTDMIN INT)
RETURNS INT
BEGIN
  DECLARE t_ustdmin INT;
  SET t_ustdmin=0;
  IF IfNULL(pBAMF,'F')<>'W' THEN
    IF t_ustdmin=0 THEN
      SET t_ustdmin=IfNULL(pPraktikumUSTDMIN,0);
    END IF;
    IF t_ustdmin=0 THEN
      SET t_ustdmin=IfNULL(pKursUSTDMIN,0);
    END IF;
  END IF;
  RETURN t_ustdmin;
END;
</brscript>
DROP FUNCTION IF EXISTS kt_ue_minuten;
<brscript>
CREATE FUNCTION kt_ue_minuten(pBAMF VARCHAR(1), pMinuten INT, pUSTDMIN INT)
RETURNS INT
BEGIN
  DECLARE t_minuten FLOAT;
  DECLARE t_ustdmin FLOAT;
  DECLARE t_ue22halb VARCHAR(1);
  SET t_ue22halb=(SELECT IfNULL(wert,'F') FROM hlgrundsys WHERE feld='MUSIK_22HALBUE');
  IF IfNULL(pBAMF,'F')='W' THEN
    SET t_ustdmin=45.0;
  ELSE
    SET t_ustdmin=IfNULL(pUSTDMIN*1.0,0.0);
    IF t_ustdmin=0.0 THEN
      SET t_ustdmin=(SELECT IfNULL(ustdmin,0) FROM grundsys);
    END IF;
    IF t_ustdmin=0.0 THEN
      SET t_ustdmin=90.0;
    END IF;
  END IF;
  IF t_ustdmin=0.0 THEN
    RETURN 0.0;
  END IF;
  IF IfNULL(pMinuten,0)<=0 THEN
    SET t_minuten=0.0;
  ELSE
    SET t_minuten=(CASE
                    WHEN (t_ue22halb='W') AND (t_ustdmin=45.0)
                    THEN (CASE
                          WHEN (pMinuten % 45=11) THEN (pMinuten*1.0)+0.25
                          WHEN (pMinuten % 45=12) THEN (pMinuten*1.0)-0.75
                          WHEN (pMinuten % 45=22) THEN (pMinuten*1.0)+0.5
                          WHEN (pMinuten % 45=23) THEN (pMinuten*1.0)-0.5
                          WHEN (pMinuten % 45=33) THEN (pMinuten*1.0)+0.75
                          WHEN (pMinuten % 45=34) THEN (pMinuten*1.0)-0.25
                          ELSE pMinuten*1.0
                          END)
                    ELSE pMinuten*1.0
                    END);
  END IF;
  RETURN t_minuten/t_ustdmin;
END;
</brscript>
";