-- -----------------------------------------------------------------------------
/**
* 
*
* @autor    $Author: michtsch $
* @date     $Date: 2017-02-16 15:03:14 $
* @source   $Source: /home/work/ha_20170311/src/ha/db/hacom/scripts/sp_ueberwachung.sql,v $
* @version  $Id: sp_ueberwachung.sql,v 1.21 2017-02-16 15:03:14 michtsch Exp $
*
* @brief    
*
* @note     keine Besonderheiten
*
*/
-- -----------------------------------------------------------------------------
delimiter //

drop procedure if exists `SP_UEBERWACHUNG`;
//

CREATE DEFINER=CURRENT_USER PROCEDURE SP_UEBERWACHUNG (in $DETAIL varchar(3))
BEGIN
   if $DETAIL in ('1','01','ALL') then
      select sum(case when vic.ID_SERVICE=1 then 1 else 0 end) as ANZAHL, IP
         from SERVER_REPOSITORY serv left outer join SERVICE_REPOSITORY vic
         on (serv.ID_SERVER_REPOSITORY = vic.ID_SERVER_REPOSITORY)
         group by serv.IP;
   end if;

   if $DETAIL in ('2','02','ALL') then
      /* Hier lasse ich bewusst den Outer-Join, damit man sieht, welche Server auf keiner Box konfiguriert sind */
      select sum(case when rep.ID_SERVER_REPOSITORY is null then 0 else 1 end) as ANZAHL,
                   serv.DESCRIPTION,
                   min(rbs.MIN_INSTANCE) as MIN_INSTANCE,
                   b.IP_ADRESS
               from SERVER serv 
                    left outer join REF_BOX_SERVER rbs on (serv.ID_SERVER = rbs.ID_SERVER)
                    left outer join BOX b on (rbs.ID_BOX = b.ID_BOX)
                    left outer join SERVER_REPOSITORY rep on (rep.ID_SERVER = serv.ID_SERVER  and b.IP_ADRESS = rep.IP)
               group by serv.DESCRIPTION,b.IP_ADRESS;
   end if;
   
   if $DETAIL in ('3','03','ALL') then
      select *
         from REQUEST_HISTORY
         where STATE = 'F';
   end if;

   if $DETAIL in ('4','04','ALL') then
      select sum(case when rep.STATE = 'U' then 1 when rep.STATE is null then 1 else 0 end) as FREI,
             sum(case when rep.STATE = 'U' then 0 when rep.STATE is null then 0 else 1 end) as BELEGT,
             serv.DESCRIPTION
         from SERVER serv left outer join SERVER_REPOSITORY rep on (rep.ID_SERVER = serv.ID_SERVER)
         group by serv.DESCRIPTION;
   end if;

   if $DETAIL in ('5','05','ALL') then
      select *
         from SERVER_REPOSITORY
         where (STATE = 'A' and DATE_LAST_USE < SUBDATE(NOW(), INTERVAL 5 MINUTE))
            or DATE_LAST_REFRESH < SUBDATE(NOW(), INTERVAL 5 MINUTE)
            or WARN_COUNT >= 3;
   end if;

   if $DETAIL in ('6','06','ALL') then
      select r.*
         from SERVER_REPOSITORY r left outer join SERVICE_REPOSITORY s on (r.ID_SERVER_REPOSITORY = s.ID_SERVER_REPOSITORY)
         where s.ID_SERVER_REPOSITORY is null;
   end if;

   if $DETAIL in ('7','07','ALL') then
      select s.*
         from SERVER_REPOSITORY r right outer join SERVICE_REPOSITORY s on (r.ID_SERVER_REPOSITORY = s.ID_SERVER_REPOSITORY)
         where r.ID_SERVER_REPOSITORY is null;
   end if;

   if $DETAIL in ('8','08','ALL') then
      select *
         from JOB_EXEC
         where COUNT_START > COUNT_END 
           and DATE_START  < SUBDATE(NOW(), INTERVAL 5 MINUTE)
           and DATE_START >= CURDATE();
   end if;

   if $DETAIL in ('9','09','ALL') then
      select *
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY > 0
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE);
   end if;

   if $DETAIL in ('10','ALL') then
      select *
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY = -1
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE);
   end if;

   if $DETAIL in ('11','ALL') then
      select *
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY = -2
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE);
   end if;

   if $DETAIL in ('12','ALL') then
      select *
         from SYSTEM_CACHE
         where STATE is not null and DATE_UPDATE < SUBDATE(NOW(), INTERVAL 10 MINUTE);
   end if;

   if $DETAIL in ('13','ALL') then
      select *
         from XCLOCK
         where DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE);
   end if;

   if $DETAIL in ('14','ALL') then
      select *
         from trax.Q2X_HEAD
         where FLAG_Q2X_STATUS= 'F';
   end if;

   if $DETAIL in ('15','ALL') then
      select *
         from trax.ORDERNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('16','ALL') then
      select *
         from trax.EXECNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('17','ALL') then
      select *
         from trax.EUNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('18','ALL') then
      select *
         from trax.MTNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('19','ALL') then
      select *
         from trax.PIHNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('20','ALL') then
      select *
         from trax.PIDNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('21','ALL') then
      select *
         from trax.EXECUTION
         where ID_EXECUTION_STATE = 35
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 5 DAY);
   end if;

   if $DETAIL in ('22','ALL') then
      select *
         from trax.BENUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('23','ALL') then
      select *
         from trax.BENUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('24','ALL') then
      select *
         from trax.PMINUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('25','ALL') then
      select *
         from trax.PAIN_BLOB
         where RECORD_STATE = 'F';
   end if;

   if $DETAIL in ('26','ALL') then
      select *
         from trax.PAIN_BLOB
         where RECORD_STATE in('I','S','R','W')
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 1 HOUR);
   end if;

   if $DETAIL in ('27','ALL') then
      select *
         from trax.ERROR_LOG
         where FLAG_READ = 'N';
   end if;

   if $DETAIL in ('28','ALL') then
      select *
         from hacom.FINAL_CALL_FAILURE
         where DATE_DONE is null
           and DATE_INSERT  < SUBDATE(NOW(), INTERVAL 5 MINUTE);
   end if;

   if $DETAIL in ('29','ALL') then
      select *
         from hacom.FINAL_CALL_FAILURE
         where DATE_INSERT >= CURDATE();
   end if;

   if $DETAIL in ('30','ALL') then
      select *
         from trax.IDCNUMBER_RANGE
         where RECORD_STATE = 'H';
   end if;

   if $DETAIL in ('31','ALL') then
      select ID_VIDEOLEGI_DATA, DATE_INSERT, DATE_FINISHED, ERROR_CODE, ERROR_COUNT, IDENT, ID_PARTNER, ID_SERVICE, STATE
         from trax.VIDEOLEGI_DATA
         where STATE in ('P', 'F')
         and DATE_UPDATE < SUBDATE(NOW(), INTERVAL 1 DAY);
   end if;

/* dAS GROSSE rESULTSET */
      select concat('Management_Server_Pro_Maschine_',IP)     as BEZEICHNUNG,
             cast(ANZAHL as CHAR)                             as WERT,
             '<'                                              as OPERATOR, 
             '2'                                              as SCHWELLWERT,
             case when ANZAHL < 2 then 'NOT_OK' else 'OK' end as KZ_OK,
             '01'                                              as KZ_DETAIL
         from (select sum(case when vic.ID_SERVICE=1 then 1 else 0 end) as ANZAHL, IP
                  from SERVER_REPOSITORY serv left outer join SERVICE_REPOSITORY vic
                  on (serv.ID_SERVER_REPOSITORY = vic.ID_SERVER_REPOSITORY)
                  group by serv.IP) innertab
      union all
      /* minimale Anzahl von Servern eines Typs pro Maschine*/
      select concat(concat(concat('minimale_Serverzahl_',DESCRIPTION),'_'),coalesce(IP_ADRESS,'')),
             cast(ANZAHL as CHAR),
             '<', 
             cast(MIN_INSTANCE as CHAR),
             case when ANZAHL < MIN_INSTANCE then 'NOT_OK' else 'OK' end,
             '02'
         from (select sum(case when rep.ID_SERVER_REPOSITORY is null then 0 else 1 end) as ANZAHL,
                      serv.DESCRIPTION,
                      min(rbs.MIN_INSTANCE) as MIN_INSTANCE,
                      b.IP_ADRESS
                  from SERVER serv 
                       join REF_BOX_SERVER rbs on (serv.ID_SERVER = rbs.ID_SERVER)
                       join BOX b on (rbs.ID_BOX = b.ID_BOX)
                       left outer join SERVER_REPOSITORY rep on (rep.ID_SERVER = serv.ID_SERVER  and b.IP_ADRESS = rep.IP)
                  group by serv.DESCRIPTION,b.IP_ADRESS) innertab
      union all
      /* Messages mit Fehlern */
      select 'fehlerhafte_Messages',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '03'
         from REQUEST_HISTORY
         where STATE = 'F'
      union all
   /* Serverauslastung */
   /* hier muss ID_SERVER erst befüllt werden */
      select concat('Server_Auslastung_',DESCRIPTION),
             cast(BELEGT * 100 / (FREI+BELEGT) as CHAR),
             '>', 
             '80',
             case when BELEGT * 100 / (FREI+BELEGT) > 80 then 'NOT_OK' else 'OK' end,
             '04'
         from (select sum(case when rep.STATE = 'U' then 1 when rep.STATE is null then 1 else 0 end) as FREI,
                      sum(case when rep.STATE = 'U' then 0 when rep.STATE is null then 0 else 1 end) as BELEGT,
                      serv.DESCRIPTION
         from SERVER serv left outer join SERVER_REPOSITORY rep on (rep.ID_SERVER = serv.ID_SERVER)
         group by serv.DESCRIPTION) innertab
      union all
   /* Server, die zu lange auf "A" stehen oder nicht mehr reagieren oder wegen zu vieler Verwarnungen nicht mehr benutzt werden */
      select 'Server_Timeout',
           cast(count(1) as CHAR),
           '>', 
           '0',
           case when count(1) > 0 then 'NOT_OK' else 'OK' end,
           '05'
       from SERVER_REPOSITORY
       where (STATE = 'A' and DATE_LAST_USE < SUBDATE(NOW(), INTERVAL 5 MINUTE))
          or DATE_LAST_REFRESH < SUBDATE(NOW(), INTERVAL 5 MINUTE)
          or WARN_COUNT >= 3
      union all
   /* verwaiste Einträge in SERVER- bzw. SERVICE-REPOSITORY */
      select 'Server_unreferenziert',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '06'
         from SERVER_REPOSITORY r left outer join SERVICE_REPOSITORY s on (r.ID_SERVER_REPOSITORY = s.ID_SERVER_REPOSITORY)
         where s.ID_SERVER_REPOSITORY is null
       union all
       select 'Services_unreferenziert',
              cast(count(1) as CHAR),
              '>', 
              '0',
              case when count(1) > 0 then 'NOT_OK' else 'OK' end,
              '07'
          from SERVER_REPOSITORY r right outer join SERVICE_REPOSITORY s on (r.ID_SERVER_REPOSITORY = s.ID_SERVER_REPOSITORY)
          where r.ID_SERVER_REPOSITORY is null
      union all
   /* zu lange laufende Async-Jobs */
   /* aber nur die von heute       */
      select 'Asyncjob_Timeout',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '08'
            from JOB_EXEC
            where COUNT_START > COUNT_END 
              and DATE_START  < SUBDATE(NOW(), INTERVAL 5 MINUTE)
              and DATE_START >= CURDATE()
      union all
   /* Messages, die zu lange auf "A" stehen */
      select 'Messages_zu_alt_ServerMessages',
              cast(count(1) as CHAR),
              '>', 
              '0',
              case when count(1) > 0 then 'NOT_OK' else 'OK' end,
              '09'
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY > 0
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE)
      union all
      select 'Messages_zu_alt_AsyncJobStreichung',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '10'
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY = -1
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE)
      union all
      select 'Messages_zu_alt_AsyncJobRouting',
            cast(count(1) as CHAR),
            '>', 
            '0',
            case when count(1) > 0 then 'NOT_OK' else 'OK' end,
            '11'
         from REQUEST_HISTORY
         where STATE in ('A','W')
           and ID_SERVER_REPOSITORY = -2
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE)
      union all
      select 'Dispo_Sperren',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '12'
         from SYSTEM_CACHE
         where STATE is not null and DATE_UPDATE < SUBDATE(NOW(), INTERVAL 10 MINUTE)
      union all
      select 'XCLOCK_zu_alt',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '13'
         from XCLOCK
         where DATE_INSERT < SUBDATE(NOW(), INTERVAL 10 MINUTE)
      union all
      select 'Fehler_in_Q2X_HEAD',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '14'
         from trax.Q2X_HEAD
         where FLAG_Q2X_STATUS= 'F'
      union all
      select 'Nummern_frei_ORDERS',
             cast(max(round(((ORDERNUMBER_TO-ORDERNUMBER_USED)/(ORDERNUMBER_TO-ORDERNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((ORDERNUMBER_TO-ORDERNUMBER_USED)/(ORDERNUMBER_TO-ORDERNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '15'
         from trax.ORDERNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_EXECUTION_DEFINED',
             cast(max(round(((EXECNUMBER_TO-EXECNUMBER_USED)/(EXECNUMBER_TO-EXECNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((EXECNUMBER_TO-EXECNUMBER_USED)/(EXECNUMBER_TO-EXECNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '16'
         from trax.EXECNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_EXECUTION_UNDEFINED',
             cast(max(round(((EUNUMBER_TO-EUNUMBER_USED)/(EUNUMBER_TO-EUNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((EUNUMBER_TO-EUNUMBER_USED)/(EUNUMBER_TO-EUNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '17'
         from trax.EUNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_MONEY_TRANSFER',
             cast(max(round(((MTNUMBER_TO-MTNUMBER_USED)/(MTNUMBER_TO-MTNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((MTNUMBER_TO-MTNUMBER_USED)/(MTNUMBER_TO-MTNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '18'
         from trax.MTNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_PAYMENT_INSTRUCTION_HEADER',
             cast(max(round(((PIHNUMBER_TO-PIHNUMBER_USED)/(PIHNUMBER_TO-PIHNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((PIHNUMBER_TO-PIHNUMBER_USED)/(PIHNUMBER_TO-PIHNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '19'
         from trax.PIHNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_PAYMENT_INSTRUCTION_DETAIL',
             cast(max(round(((PIDNUMBER_TO-PIDNUMBER_USED)/(PIDNUMBER_TO-PIDNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((PIDNUMBER_TO-PIDNUMBER_USED)/(PIDNUMBER_TO-PIDNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '20'
         from trax.PIDNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Execution_Status_35',
             cast(count(*) as CHAR),
             '>', 
             '0',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '21'
         from trax.EXECUTION
         where ID_EXECUTION_STATE = 35
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 5 DAY)
      union all
      select 'Nummern_frei_BENUMBER_RANGE',
             cast(max(round(((BENUMBER_TO-BENUMBER_USED)/(BENUMBER_TO-BENUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((BENUMBER_TO-BENUMBER_USED)/(BENUMBER_TO-BENUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '22'
         from trax.BENUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_BEPOSNUMBER_RANGE',
             cast(max(round(((BEPOSNUMBER_TO-BEPOSNUMBER_USED)/(BEPOSNUMBER_TO-BEPOSNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((BEPOSNUMBER_TO-BEPOSNUMBER_USED)/(BEPOSNUMBER_TO-BEPOSNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '23'
         from trax.BEPOSNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Nummern_frei_PMINUMBER_RANGE',
             cast(max(round(((PMINUMBER_TO-PMINUMBER_USED)/(PMINUMBER_TO-PMINUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((PMINUMBER_TO-PMINUMBER_USED)/(PMINUMBER_TO-PMINUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '24'
         from trax.PMINUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'Fehler_in_PAIN_BLOB',
             cast(count(*) as CHAR),
             '>', 
             '0',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '25'
         from trax.PAIN_BLOB
         where RECORD_STATE = 'F'
      union all
      select 'PAIN_BLOB_zu_alt',
             cast(count(*) as CHAR),
             '>', 
             '0',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '26'
         from trax.PAIN_BLOB
         where RECORD_STATE in('I','S','R','W')
           and DATE_INSERT < SUBDATE(NOW(), INTERVAL 1 HOUR)
      union all
      select 'ERROR_LOG_Eintraege',
             cast(count(*) as CHAR),
             '>', 
             '0',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '27'
         from trax.ERROR_LOG
         where FLAG_READ = 'N'
      union all
      select 'FINAL_CALL_FAILURE_open',
             cast(count(*) as CHAR),
             '>', 
             '0',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '28'
         from hacom.FINAL_CALL_FAILURE
         where DATE_DONE is null
           and DATE_INSERT  < SUBDATE(NOW(), INTERVAL 5 MINUTE)
      union all
/* das naechste ist nur fuer die Statistik */
      select 'FINAL_CALL_FAILURE_day_stat',
             cast(count(*) as CHAR),
             '>', 
             '100',
             case when count(*) > 0 then 'NOT_OK' else 'OK' end,
             '29'
         from hacom.FINAL_CALL_FAILURE
         where DATE_INSERT >= CURDATE()
      union all
      select 'Nummern_frei_IDCNUMBER_RANGE',
             cast(max(round(((IDCNUMBER_TO-IDCNUMBER_USED)/(IDCNUMBER_TO-IDCNUMBER_FROM))*100,2)) as CHAR),
             '<', 
             '10',
             case when max(round(((IDCNUMBER_TO-IDCNUMBER_USED)/(IDCNUMBER_TO-IDCNUMBER_FROM))*100,2)) < 10 then 'NOT_OK' else 'OK' end,
             '30'
         from trax.IDCNUMBER_RANGE
         where RECORD_STATE = 'H'
      union all
      select 'nicht abgearbeitete/fehlerhafte Videolegis',
             cast(count(1) as CHAR),
             '>', 
             '0',
             case when count(1) > 0 then 'NOT_OK' else 'OK' end,
             '31'
         from trax.VIDEOLEGI_DATA
         where STATE in ('P', 'F')
         and DATE_UPDATE < SUBDATE(NOW(), INTERVAL 1 DAY)
      order by 6,1
      ;

END;
//