Fehlerbericht/queries/status_tag.sql

SELECT DISTINCT sm.kunde,
                sm.aufgabe,
                sm.datum,
                datediff(now(), sm.datum) as datum_diff,
                k.system,
                k.bundesland,
                sm.start,
                sm.ende,
                ka.start_soll,
                ifnull(case
                         when dayofweek(sm.datum) = 2 then ka.ende_mo
                         when dayofweek(sm.datum) = 3 then ka.ende_di
                         when dayofweek(sm.datum) = 4 then ka.ende_mi
                         when dayofweek(sm.datum) = 5 then ka.ende_do
                         when dayofweek(sm.datum) = 6 then ka.ende_fr
                         when dayofweek(sm.datum) = 7 then ka.ende_sa
                         else ka.ende_so
                         end, ka.ende_soll)      as ende_soll,
                sm.anzahl,
                sm.bearbeitet,
                ifnull(sk.benutzer, '')          as benutzer,
                case
                  when sm.bearbeitet = '1' then 'bearbeitet'
                  when ka.start_soll is NULL then 'manuell'
                  when sm.anzahl != 0 then 'fehlerhaft'
                  when concat(sm.datum, ' ', sm.ende) > date_add(concat(sm.datum, ' ', ka.ende_soll), INTERVAL 1 HOUR) then 'verspaetet'
                  when sm.anzahl = 0 then 'fehlerfrei'
                  else 'undefiniert'
                  end                            as `status`

FROM status_meldung sm
       INNER JOIN kunden k USING (kunde)
       LEFT JOIN kunden_aufgabe ka USING (kunde, aufgabe)
       LEFT JOIN status_kommentar sk ON sm.kommentar_id = sk.id
       LEFT JOIN status_kommentar as sk2 ON sm.datum = sk2.datum AND sm.kunde = sk2.kunde
WHERE sm.datum <= now()
  AND datediff(now(), sm.datum) <= 7
  AND k.aktiv = 1
  AND (ka.aktiv IS NULL OR ka.aktiv = 1)

UNION

SELECT DISTINCT k.kunde,
                ka.aufgabe,
                a.datum,
                datediff(now(), a.datum) as datum_diff,
                k.system,
                a.bundesland,
                NULL                            as start,
                NULL                            as ende,
                ka.start_soll,
                ka.ende_soll,
                -1                              as anzahl,
                0                               as bearbeitet,
                ''                              as benutzer,
                case
                  when now() > date_add(concat(a.datum, ' ', ka.ende_soll), INTERVAL 1 HOUR) then 'fehlend'
                  else 'anstehend'
                  end                           as `status`


FROM arbeitstage a
       INNER JOIN kunden k USING (bundesland)
       INNER JOIN kunden_aufgabe ka USING (kunde)
       LEFT JOIN status_meldung sm USING (datum, kunde, aufgabe)

WHERE a.datum <= now()
  AND datediff(now(), a.datum) <= 7
  AND k.aktiv = 1
  AND ka.aktiv = 1
  AND sm.start IS NULL


ORDER BY 1, 2, 4