 SET NAMES utf8;

/* ************************************************************************************************************************************************************************
    Clear_Event
    Event do usuwania "złogów" i wywoływany 1 raz na dobe ok 4:00 (rano)
    W procedurze `_sys_Clear_Event_ProcList` nalezy dopisywac własne call-e lub update/delete do czyszczenia
    
    SHOW VARIABLES LIKE '%event%';
    SET GLOBAL event_scheduler = ON;
    SELECT @@event_scheduler;
    
    zwłaszcza : ....
    LOG_infpiek_dok_po_insert
    infpiek_int_po_log
    oppo_corr_e
    _erp_external_process_log
    
    CALL _sys_Clear_Event_ProcList('ALL', '', '');
************************************************************************************************************************************************************************ */    
DELIMITER $$ 
DROP PROCEDURE IF EXISTS `_sys_Clear_Event_ProcList`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_sys_Clear_Event_ProcList`(
    IN _MODE VARCHAR(150),
    IN _PARAM_A VARCHAR(250),
    IN _PARAM_B VARCHAR(250)
) 
S_Clear_Event_ProcList: BEGIN 
    DO _Birth_System();    --  teraz to tutaj wszystkie zmiene są aktywowane/pobierane
    -- UPDATE `impt_1b7430a33edbf4d8f97bf521bbcc5e7a` SET `No Gephlue` = `No Gephlue` + 1;
    DO _counters_tools('SET', CONCAT_WS('.', '_sys_Clear_Event_ProcList', _MODE), '', '');        -- to tylko Licznik!!!!
    
    /******************************************************************************************************
        KONKOL
        `LOG_infpiek_dok_po_insert` i `infpiek_int_po_log` - starsze niż 3 tygodnie
        `m_xxpo` - starsze niż 14 dni
    ******************************************************************************************************/
    IF _MODE IN ('ALL', 'process_log') AND @session.globals.Owner = 'KONKOL' AND SCHEMA() = 'ht' THEN
    --  usuń starsze niż 3 tyg
        DELETE pr FROM `LOG_infpiek_dok_po_insert` pr
        WHERE
            pr.Create_At < (CURRENT_DATE() - INTERVAL 3 WEEK)
        ;
        DELETE pr FROM `infpiek_int_po_log` pr
        WHERE
            pr.Create_At < (CURRENT_DATE() - INTERVAL 3 WEEK)
        ;
        
        /******************************************************************************
            1 XII 2021 wyczyscilem `m_xxpo` do 5 tyg wstec z UFF!!!
            Teraz czyszcze 
            10 tyg. wstecz wszystko oraz
            5 tyg wlacznie z localtime
        ******************************************************************************/
        DELETE mp 
        FROM `m_xxpo` mp
        WHERE
        TRUE
        -- AND mp.Create_at < (CURRENT_DATE() - INTERVAL 10 WEEK)
        AND mp.Create_at < (CURRENT_DATE() - INTERVAL 1 MONTH)
        ;
        DELETE mn FROM `m_zmng` mn
        WHERE
            mn.TerminRealizacji < (CURRENT_DATE() - INTERVAL 5 WEEK)
        ;
        -- SET @.MAX.down_hex = IFNULL((SELECT MAX(dn.down_hex) FROM `infpiek_int_ng` dn WHERE dn.down_Dat < CURRENT_DATE() - INTERVAL 100 DAY), 0); 
        SET @.MAX.down_hex = IFNULL((SELECT MAX(dn.down_hex) FROM `infpiek_int_ng` dn WHERE dn.down_Dat < CURRENT_DATE() - INTERVAL 3 MONTH), 0); 
        DELETE dp FROM `infpiek_int_po` dp WHERE dp.down_hex <= @.MAX.down_hex;
        DELETE dn FROM `infpiek_int_ng` dn WHERE dn.down_hex <= @.MAX.down_hex;

        IF SCHEMA() = 'zoo' THEN
            SET @.MAX.down_hex = 0;
        END IF;
    END IF;
    
    /******************************************************************************************************
        Log błędów
        `_db_app_errors` - starsze niż 6 m-ce
    ******************************************************************************************************/
    IF _MODE IN ('ALL', '_db_app_errors') THEN
    --  usuń starsze niż 3 tyg
        DELETE e FROM `_db_app_errors` e
        WHERE
            e.Time < (CURRENT_DATE() - INTERVAL 2 MONTH)
        ;
    END IF;
    
    /******************************************************************************************************
        _sys_app_logtxt
    ******************************************************************************************************/
    IF _MODE IN ('ALL', '_sys_app_logtxt') THEN
        CALL _sys_app_logtxt('UPDATE.DELETED', '', '', '', '', '', '', '', '');
        CALL _sys_app_logtxt('DELETE.DELETED', '', '', '', '', '', '', '', '');
    END IF;
    
    /******************************************************************************************************
        Log procesów
        `_erp_external_process_log` - starsze niż 3 tygodnie
    ******************************************************************************************************/
    IF _MODE IN ('ALL', '_erp_external_process_log') THEN
    --  usuń starsze niż 1 m-c
        DELETE pr FROM `_erp_external_process_log` pr
        WHERE
            pr.Time < (CURRENT_DATE() - INTERVAL 1 MONTH)
        ;
        DELETE FROM `_erp_external_process_log_GUIDlink` 
        WHERE 
            NOT EXISTS(SELECT pl.ID FROM `_erp_external_process_log` pl WHERE pl.ID = id_log)
        ;
    END IF;
    
    /******************************************************************************************************
        _sys_db_logons
        `_sys_db_logons` - starsze niż 3 m-ce
    ******************************************************************************************************/
    IF _MODE IN ('ALL', '_sys_db_logons') THEN
    --  usuń Eventowe starsze niż 2 tyg
        DELETE sdl FROM `_sys_db_logons` sdl
        WHERE
            sdl.Inactive
            AND sdl.Kind = 'EVENT'
            AND sdl.CONNECT_AT < (CURRENT_DATE() - INTERVAL 2 WEEK)
        ;
    --  usuń starsze niż 2 m-ce
        DELETE sdl FROM `_sys_db_logons` sdl
        WHERE
            sdl.LAST_USED_AT < (CURRENT_DATE() - INTERVAL 2 MONTH)
        ;
    --  usuń starsze niż 2 m-ce
        DELETE sdl FROM `_sys_db_logons` sdl
        WHERE
            sdl.Inactive
            AND sdl.CONNECT_AT < (CURRENT_DATE() - INTERVAL 2 MONTH)
        ;
    END IF;
    
    /******************************************************************************************************
        stare czyli _db_logons
        `_db_logons` - starsze niż 3 m-ce
    ******************************************************************************************************/
    IF _MODE IN ('ALL', '_db_logons') THEN
    --  usuń starsze niż 3 tyg
        DELETE dl FROM `_db_logons` dl
        WHERE
            dl.LAST_USED_AT < (CURRENT_DATE() - INTERVAL 3 WEEK)
        ;
    END IF;
    
    /******************************************************************************************************
        dziennik importów `imp_diary`
    ******************************************************************************************************/
    IF _MODE IN ('ALL', 'Diary') THEN
    --  usuń importy starsze niż 1 m-c    
        DELETE FROM `imp_diary` WHERE Deleted AND ((GREATEST(Create_At, Imported_At) + INTERVAL 1 MONTH) < CURRENT_DATE());
    END IF;
    
    /******************************************************************************************************
        blokady na dokumenty, etc
    ******************************************************************************************************/
    IF _MODE IN ('ALL', 'LOCKS') THEN
    --  usuń blokady starsze niż 24h dla których sesja nie istnieje lub jest zamknięta 
        DO _sys_locks_do('DELETE_LOCKS_WITH_Inactive_Session', '', '', '', '', '', '7', ''); 
    --  i jeszcze stare ale wciaż używane locki na świat E
        DELETE l FROM `_lock_inf` l 
        WHERE
            l.alocaltime < (NOW() - INTERVAL 3 DAY)
        ;
    END IF;
    
    /******************************************************************************************************
        świat m
    ******************************************************************************************************/
    IF _MODE IN ('ALL', 'xxxx_m') THEN
    --  Usuń starsze blokady niż 1 m-c dla rekordów z tabel 'opng_m' i 'zmng_m'
        DO _sys_locks_do('DELETE_LOCKS_OLDER_THAN.n.Month', '', 'opng_m', 'EDYCJA', '', '', '1', ''); 
        DO _sys_locks_do('DELETE_LOCKS_OLDER_THAN.n.Month', '', 'zmng_m', 'EDYCJA', '', '', '1', ''); 
    --  teraz można usunąć same rekordy z 'opng_m' i 'zmng_m' i oczywiście zaraz potem z powiazanych tabel
        DROP TEMPORARY TABLE IF EXISTS `t_delete_xxng_x`;
        CREATE TEMPORARY TABLE `t_delete_xxng_x` (
          `GUID` VARCHAR(38) NOT NULL DEFAULT '',
          PRIMARY KEY `GUID`(`GUID`)
        )
        ;
    --  tabela `opng_m` i powiazane
        TRUNCATE `t_delete_xxng_x`;
    --  1 wstaw jeszcze nie zapisane (idn = 0) starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `opng_m` mn
            WHERE
            --  nie zapisane (idn = 0)     
                mn.Idn = 0
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 2 DAY)
            --  nie zapisane (bez odpow. w real)     
                AND NOT EXISTS(SELECT n.GUID FROM `opng` n WHERE n.GUID = mn.GUID)
        ;
    --  2 wstaw wczesniej zapisane ale bez blokady i starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `opng_m` mn, `opng` n
            WHERE
                mn.GUID = n.GUID
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 2 DAY)
            --  bez blokady
                AND NOT EXISTS(SELECT dl.Rec_ID FROM `_sys_locks` dl WHERE dl.Rec_ID = n.GUID AND dl.Rec_TableName = 'opng_m')
        ;
    --  Usuwanie    
        DELETE mn FROM `opng_m` mn, `t_delete_xxng_x` t
        WHERE
            mn.GUID = t.GUID
        ;
        DELETE mp FROM `oppo_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        DELETE mp FROM `opng_vat_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        DELETE mp FROM `oppo_corr_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper_corr = t.GUID
        ;
    --  tabela `zmng_m` i powiazane
        TRUNCATE `t_delete_xxng_x`;
    --  1 wstaw jeszcze nie zapisane (idn = 0) starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `zmng_m` mn
            WHERE
            --  nie zapisane (idn = 0)     
                mn.Idn = 0
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 2 DAY)
            --  nie zapisane (bez odpow. w real)     
                AND NOT EXISTS(SELECT n.GUID FROM `zmng` n WHERE n.GUID = mn.GUID)
        ;
    --  2 wstaw wczesniej zapisane ale bez blokady i starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `zmng_m` mn, `zmng` n
            WHERE
                mn.GUID = n.GUID
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 2 DAY)
            --  bez blokady
                AND NOT EXISTS(SELECT dl.Rec_ID FROM `_sys_locks` dl WHERE dl.Rec_ID = n.GUID AND dl.Rec_TableName = 'zmng_m')
        ;
    --  Usuwanie    
        DELETE mn FROM `zmng_m` mn, `t_delete_xxng_x` t
        WHERE
            mn.GUID = t.GUID
        ;
        DELETE mp FROM `zmpo_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnNg = t.GUID
        ;
        DELETE mp FROM `opng_vat_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        /*
        DELETE mp FROM `zmng_vat_m` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        */
    END IF;

    /******************************************************************************************************
        świat e
    ******************************************************************************************************/
    IF _MODE IN ('ALL', 'xxxx_e') THEN
    --  usuwanie rekordow z 'opng_e' i 'zmng_e' i oczywiście zaraz potem z powiazanych tabel
        DROP TEMPORARY TABLE IF EXISTS `t_delete_xxng_x`;
        CREATE TEMPORARY TABLE `t_delete_xxng_x` (
          `GUID` VARCHAR(38) NOT NULL DEFAULT '',
          PRIMARY KEY `GUID`(`GUID`)
        )
        ;
    --  tabela `opng_e` i powiazane
        TRUNCATE `t_delete_xxng_x` ;
    --  1 wstaw jeszcze nie zapisane (idn = 0) starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `opng_e` mn
            WHERE
            --  nie zapisane (idn = 0)     
                mn.Idn = 0
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 7 DAY)
            --  nie zapisane (bez odpow. w real)     
                AND NOT EXISTS(SELECT n.GUID FROM `opng` n WHERE n.GUID = mn.GUID)
        ;
    --  2 wstaw wczesniej zapisane ale bez rekordu w `ng_e_rec` i starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `opng_e` mn, `opng` n
            WHERE
                mn.GUID = n.GUID
            --  starsze niż 1 m-c? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 1 MONTH)
            --  bez rekordu w `ng_e_rec`
                AND NOT EXISTS(SELECT dl.GUID FROM `ng_e_rec` dl WHERE dl.GUID = n.GUID)
        ;
    --  Usuwanie    
        DELETE mn FROM `opng_e` mn, `t_delete_xxng_x` t
        WHERE
            mn.GUID = t.GUID
        ;
        DELETE mp FROM `oppo_e` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        DELETE mp FROM `opng_vat_e` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
        DELETE mp FROM `oppo_corr_e` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper_corr = t.GUID
        ;
    --  tabela `zmng_e` i powiazane
        TRUNCATE `t_delete_xxng_x` ;
    --  1 wstaw jeszcze nie zapisane (idn = 0) starsze niż ...    
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `zmng_e` mn
            WHERE
            --  nie zapisane (idn = 0)     
                mn.Idn = 0
            --  starsze niż 1 dzien? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 2 DAY)
            --  nie zapisane (bez odpow. w real)     
                AND NOT EXISTS(SELECT n.GUID FROM `zmng` n WHERE n.GUID = mn.GUID)
        ;
    --  2 wstaw wczesniej zapisane ale bez rekordu w `ng_e_rec` i starsze niż ...        
        INSERT IGNORE INTO `t_delete_xxng_x` 
            SELECT
                mn.GUID
            FROM `zmng_e` mn, `zmng` n
            WHERE
                mn.GUID = n.GUID
            --  starsze niż 1 m-c? a moze więcej ????
                AND mn.LocalTime < (NOW() - INTERVAL 1 MONTH)
            --  bez rekordu w `ng_e_rec`
                AND NOT EXISTS(SELECT dl.GUID FROM `ng_e_rec` dl WHERE dl.GUID = n.GUID)
        ;
    --  Usuwanie    
        DELETE mn FROM `zmng_e` mn, `t_delete_xxng_x` t
        WHERE
            mn.GUID = t.GUID
        ;
        DELETE mp FROM `zmpo_e` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnNg = t.GUID
        ;
        DELETE mp FROM `zmng_vat_e` mp, `t_delete_xxng_x` t
        WHERE
            mp.IdnOper = t.GUID
        ;
    END IF;
END $$
DELIMITER ;

DELIMITER $$ 
DROP EVENT IF EXISTS `_sys_Clear_Event`$$
CREATE EVENT IF NOT EXISTS `_sys_Clear_Event`
    ON SCHEDULE 
    -- EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 10 SECOND
    EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 65 MINUTE
    DO
    BEGIN    
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    
        BEGIN
            CALL _Report_Error('_sys_Clear_Event', 'EVENT', 'CALL _sys_Clear_Event_ProcList(''ALL'', '''', '''');', '', '');
        END;
        /*
        zwłaszcza 
        LOG_infpiek_dok_po_insert
        infpiek_int_po_log
        oppo_corr_e
        _erp_external_process_log
        */
            CALL _sys_Clear_Event_ProcList('ALL', '', '');
    END;
;    
$$
DELIMITER ;

DELIMITER $$ 
/* ************************************************************************************************************************************************************************
    Test BLOKAD
************************************************************************************************************************************************************************ */    
DROP PROCEDURE IF EXISTS `_TEST_Blokad`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_TEST_Blokad`(
    IN _MODE VARCHAR(150),
    IN _PARAM_A VARCHAR(200),
    IN _PARAM_B VARCHAR(200),
    IN _Params MEDIUMTEXT
)
S_TEST_Blokad: BEGIN
    IF _MODE IN ('SELECT.1') THEN
        SELECT 
            p.Localtime,p.Ilosc, p.iloscdo, p.spakowano, p.ExtFlags, 
            p.info4,
                                    n.WyjazdGUID, 
                                    n.KontrGUID, 
                                    IdnTowr, 
                                    komentarz, 

            p.* 
            FROM zmpo p , zmng n
            WHERE 
            TRUE
            AND n.wyjazdGUID = 'SPED:a4767011be06d9f874fa8080a1a26b12'
            -- AND n.GUID = '{5B5B759B-7882-4732-BF1F-AB381227EB21}'
            AND p.idnng = n.GUID
            -- and p.idnng = '{5B5B759B-7882-4732-BF1F-AB381227EB21}'
        ;    
        -- select k.Nume from kontrah where k.GUID = 'asdfklasdf';
    END IF;
    
    IF _MODE IN ('SELECT.2') THEN
        CALL _log_select('select', '', _PARAM_A, '', '', '', 'czas=czas>"2021-05-21"&');
    END IF;
    
    
    IF _MODE IN ('LET.SELECT.1') THEN
        IF _LOCK('IS_Used_By_ME', CONCAT(_MODE, _PARAM_A), 20, '') OR _LOCK('GET_LOCK', CONCAT(_MODE, _PARAM_A), 10, '') THEN
            CALL _TEST_Blokad('SELECT.1', _PARAM_A, _PARAM_B, _Params);
            DO _LOCK('RELEASE_LOCK', CONCAT(_MODE, _PARAM_A), 0, '');
        END IF;
        DO _LOCK('RELEASE_LOCK', CONCAT(_MODE, _PARAM_A), 0, '');
    END IF;
    
    IF _MODE IN ('LET.SELECT.2') THEN
        IF _LOCK('IS_Used_By_ME', CONCAT(_MODE, _PARAM_A), 20, '') OR _LOCK('GET_LOCK', CONCAT(_MODE, _PARAM_A), 10, '') THEN
            CALL _TEST_Blokad('SELECT.2', _PARAM_A, _PARAM_B, _Params);
            DO _LOCK('RELEASE_LOCK', CONCAT(_MODE, _PARAM_A), 0, '');
        END IF;
        DO _LOCK('RELEASE_LOCK', CONCAT(_MODE, _PARAM_A), 0, '');
    END IF;
    
    
END $$
DELIMITER ;

DELIMITER $$ 
DROP PROCEDURE IF EXISTS `_rozrzucanie_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_rozrzucanie_tools`(
    IN _MODE VARCHAR(250),
    IN _Value_Rozrzuc DOUBLE,
    IN _Param_A VARCHAR(255),
    IN _Param_B VARCHAR(255)
)
/*
    CALL _rozrzucanie_tools('INIT', 0, '', '');
    CALL _rozrzucanie_tools('CALC', 0, '', '');
    CALL _rozrzucanie_tools('DROP', 0, '', '');
*/
S_t_rozrzucanie: BEGIN 
    DECLARE _SUM_Waga, _SUM_Value, _Nadw DECIMAL(16,6);
    DECLARE _LasLP INTEGER;

    IF _MODE = 'INIT' THEN
        DROP TEMPORARY TABLE IF EXISTS `t_rozrzucanie`;
        CREATE TEMPORARY TABLE `t_rozrzucanie` (
            `ID` VARCHAR(250) NOT NULL DEFAULT '',
            `Lp` INT(11) DEFAULT NULL,
            `Waga` DECIMAL(16,4) DEFAULT NULL,
            `Value` DECIMAL(16,4) DEFAULT NULL,
            `precyzja` INT(11) DEFAULT NULL,
            PRIMARY KEY (`Id`),
            KEY `LP`(`LP`)
        )
        ;
    END IF; --  _MODE = 'INIT'

    IF _MODE = 'CALC' THEN
        
        DELETE FROM `t_rozrzucanie` WHERE Waga = 0;
        
        SET @.t_rozrzucanie.LP = 0;
        UPDATE `t_rozrzucanie` t
        SET
            t.LP = _VarInt(@.t_rozrzucanie.LP := @.t_rozrzucanie.LP + 1)
        ORDER BY t.Waga
        ;
        
        SET 
            _SUM_Waga = 
                IFNULL(_VarDecimal((SELECT SUM(t.Waga) FROM `t_rozrzucanie` t)), 0)
        ;
        -- SELECT _Value_Rozrzuc, _SUM_Waga;
        UPDATE `t_rozrzucanie` t
        SET
            t.Value = _Rnd2(IF(_SUM_Waga = 0, 0, _RND(t.Waga*_Value_Rozrzuc/_SUM_Waga, IFNULL(t.precyzja, 2))))
        ;
        /*
        Do testow
        UPDATE `t_rozrzucanie` t
        SET
            t.RValue2 = _Rnd2(IF(_SUM_Waga = 0, 0, t.Waga*_Value_Rozrzuc/_SUM_Waga))
        ;
        */
        SELECT MAX(Lp), SUM(Value) FROM `t_rozrzucanie` INTO _LasLP, _SUM_Value;
        SET _Nadw = _SUM_Value - _Value_Rozrzuc;
        -- do testow SET @_Nadw = _Nadw, @_SUM_Value = _SUM_Value, @_SUM_Waga = _SUM_Waga;
        UPDATE `t_rozrzucanie`
        SET
            Value = Value - _Nadw
        WHERE
            LP = _LasLP
        ;

    END IF; --  _MODE = 'CALC'
    
    IF _MODE = 'DROP' THEN
        DROP TEMPORARY TABLE IF EXISTS `t_rozrzucanie`;
    END IF; --  _MODE = 'DROP'

END $$

DELIMITER ;

/*  Zmiany CVS
$Log: _SystemP.sql,v $
Revision 1.1  2026/05/11 08:31:17  jmam
Inicjacja $SEND$

*/
