 /*  Zmiany CVS
$Log: _calend_P.sql,v $
Revision 1.1  2025/05/12 07:54:43  jmam
Inicjacja
*/

DELIMITER $$

/* ************************************************************************************************************************************************************************
	Kalendarze systemowe
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `_calend_P`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_calend_P`(
	IN _MODE VARCHAR(250),
	IN _A VARCHAR(50),	
	IN _B VARCHAR(50),	
	IN _C VARCHAR(50),	
	IN _D TEXT
)
 /* 
	CALL _calend_P('Init', '', '', '', '');  -- dodawanie dni
 */	
L_EXIT:BEGIN 
    DECLARE iD, D1, D2, DFirst, DLast DATE;
    DECLARE Y1, Y2, YM1, YM2, YFirst, YLast INTEGER DEFAULT 0;
    DECLARE Yplus INTEGER DEFAULT 10;
    SET YFirst := 1990, YLast := YEAR(CURRENT_DATE()) + Yplus;
    SET DFirst := MAKEDATE(YFirst, 1), DLast := _LastDayYYYY(YLast);
    SET lc_time_names = 'pl_PL';
    
    IF _MODE LIKE 'INIT.DAYS%' THEN
    /********************************************************************************************************
        CALL _calend_P('Init.DAYS', '', '', '', '');  -- inicjowanie wstepne dni - w SQL zmianie
    ********************************************************************************************************/
        SET 
            D1 := DFirst,
            D2 := DLast
        ;
        CALL _calend_P('INSERT.DAYS.from.to', D1, D2, '', '');  -- dodawanie dni
    END IF;
    IF _MODE LIKE 'ADD.NEXT.DAYS%' THEN
    /********************************************************************************************************
        CALL _calend_P('ADD.NEXT.DAYS', '', '', '', '');  -- dodawanie dni w evencie raz na rok!!!!
    ********************************************************************************************************/
        IF (SELECT COUNT(*) FROM `_calendYMD`) = 0 THEN
        --  brak jakichkolwiek dni
            CALL _calend_P('Init.DAYS', '', '', '', '');  -- inicjowanie wstepne dni - w SQL zmianie
        ELSE
        --  są dni ale... czy nie brakuje dni (na X lat w przód) ?
            SELECT MIN(D), MAX(D), MIN(Y), MAX(Y)  INTO D1, D2, Y1, Y2 FROM `_calendYMD`;
            IF Y2 < YLast THEN
            --  brakuje dni (na X lat w przód)
                SET 
                    D1 := D2 + INTERVAL 1 DAY, --    nast. dzień od ost.
                    D2 := DLast
                ;
                CALL _calend_P('INSERT.DAYS.from.to', D1, D2, '', '');  -- dodawanie dni
            END IF;
        END IF;
    END IF;
    IF _MODE LIKE 'INSERT.DAYS.from.to%' THEN
    /********************************************************************************************************
        CALL _calend_P('INSERT.DAYS.from.to', D1, D2, '', '');  -- dodawanie dni
    ********************************************************************************************************/
        SET D1 = DATE(_A), D2 = DATE(_B);
            SET @.D1 = D1, @.D2 = D2;
        DROP TEMPORARY TABLE IF EXISTS `t_calendYMD`;
        CREATE TEMPORARY TABLE `t_calendYMD` (
        `Exist` BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'juz jest w _calendYMD',
        `D` DATE NOT NULL COMMENT 'dzien',
        `Y` INT(11) DEFAULT NULL COMMENT 'rok',
        `M` INT(11) DEFAULT NULL COMMENT 'miesiac roku',
        `DofM` INT(11) DEFAULT NULL COMMENT 'dzien m-ca',
        `DofW` INT(11) DEFAULT NULL COMMENT 'dzien tyg. 1..7 (pn,wt..)',
        `DofY` INT(11) DEFAULT NULL COMMENT 'dzien roku',
        `WofY` INT(11) DEFAULT NULL COMMENT 'tydzien w roku',
        `MD` INT(11) DEFAULT NULL COMMENT 'm-co-dzień dla 12 III 312 dla 5 XI 1105',
        `YW` INT(11) DEFAULT NULL COMMENT 'roko-tydzień YEARWEEK(date,3) ISO 8601:1988',
        `YM` INT(11) DEFAULT NULL COMMENT 'rok/m-c',
        `YMW` INT(11) DEFAULT NULL COMMENT 'rok/m-c/tydz - uwaga tydzien na przelomie m-ca jest dublowany ISO 8601:1988 ',
        `IdDy` VARCHAR(50) DEFAULT NULL COMMENT 'ID dnia w roku',
        `DStr` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Pn, Wt,...',
        PRIMARY KEY (`D`)
        )
        ;
        INSERT IGNORE INTO `t_calendYMD`
        (Exist, D, Y, M, DofM, DofW, DofY, WofY, MD, YW, YM, YMW, IdDy)
        SELECT
            TRUE AS Exist,
            D, Y, M, DofM, DofW, DofY, WofY, MD, YW, YM, YMW, IdDy
        FROM `_calendYMD` c
        WHERE
            c.D BETWEEN @.D1 AND @.D2
        ;
        SET @._YW := NULL, @.YMW := NULL;
        SET iD = D1;
        WHILE (iD <= D2) DO
            SET
                @.Y := YEAR(iD), 
                @.M := MONTH(iD), 
                @.DofM := DAYOFMONTH(iD),
                        -- WEEKDAY(iD) + 1 AS DofW, 
                        -- DAYOFYEAR(iD) AS DofY, 
                -- (@.WofY := WEEK(iD, 3)) as WofY,	-- UWAGA WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).
                @.WofY := WEEKOFYEAR(iD),
                        -- @.M*100 + @.DofM AS MD, 
                @.YM := @.Y*100 + @.M,
                @.YW := YEARWEEK(iD, 3),
                        -- @._YW AS _YW,
                @.YforYMW := @.YW DIV 100,
                @.MforYMW := IF(@.M = MONTH(iD + INTERVAL 3 DAY), @.M, MONTH(iD + INTERVAL 3 DAY)),
                @.YMW := 
                    IF(!(@._YW <=> @.YW),
                        @.YforYMW*10000 + @.MforYMW*100 + @.WofY,
                        IFNULL(@.YMW, @.YforYMW*10000 + @.MforYMW*100 + @.WofY)
                    ),
                @._YW := @.YW
            ;
            -- INSERT IGNORE INTO `_calendYMD`
            INSERT IGNORE INTO `t_calendYMD`
            SET 
                D = iD,
                Y = @.Y, 
                M = @.M, 
                DofM = @.DofM, 
                DofW = WEEKDAY(iD) + 1, 
                DofY = DAYOFYEAR(iD), 
                WofY = @.WofY, 
                MD = @.M*100 + @.DofM, 
                YW = @.YW, 
                YM = @.YM, 
                YMW = @.YMW,
                DStr = _VarStr(ELT(WEEKDAY(iD) + 1, 'Pn', 'Wt', 'Śr', 'Cz', 'Pt', 'So', 'Nd'))
            ;
            SET iD := iD + INTERVAL 1 DAY;    --   + 1 dzień
        END WHILE;  
    --  Usuń z tempa juz istniejące
        DELETE tc FROM `t_calendYMD` tc WHERE tc.Exist;
    --  Wstaw do z tempa
        INSERT IGNORE INTO `_calendYMD`
        (D, Y, M, DofM, DofW, DofY, WofY, MD, YW, YM, YMW, IdDy, DStr)
        SELECT
            D, Y, M, DofM, DofW, DofY, WofY, MD, YW, YM, YMW, IdDy, DStr
        FROM `t_calendYMD` tc
        WHERE
            NOT tc.Exist
        ;
        
    --  Wstaw YM
        CALL _calend_P('INSERT.MONTHS.from.t_calendYMD', '', '', '', '');  -- dodawanie m-cy z temporarki dni
    --  Wstaw Y
        CALL _calend_P('INSERT.YEARS.from.t_calendYMD', '', '', '', '');  -- dodawanie lat z temporarki dni
        
    --  święta i dni specyf. I
		UPDATE `t_calendYMD` tc, `_calendYMD` c, `_calendDofY` ch
		SET
			c.IdDy = ch.IdDy,
			c.swieto = ch.IsHoliday,
			c.swietoSoNd = IF(ch.IsHoliday AND c.DofW = 6, 'So', IF(ch.IsHoliday AND c.DofW = 7, 'Nd', ''))
		WHERE
			ch.MD = c.MD
            AND c.D = tc.D
		;
    --  święta i dni specyf. II
        UPDATE 
            `_calendYMD` c, 
            `t_calendYMD` tc, 
            (	
            SELECT 
                sch.IdDy,
                cY.DCH + INTERVAL sch.WnShift DAY AS D,
                sch.IsHoliday
                -- tWN.* 
            FROM `_calendY` cY, `_calendDofY` sch
            WHERE
                sch.WnShift IS NOT NULL
            ) tha
        SET
            c.IdDy = tha.IdDy,
            c.swieto = tha.IsHoliday,
            c.swietoSoNd = IF(tha.IsHoliday AND c.DofW = 6, 'So', IF(tha.IsHoliday AND c.DofW = 7, 'Nd', ''))
        WHERE
            c.D = tha.D
            AND c.D = tc.D
        ;
    END IF;
    
    IF _MODE LIKE 'INSERT.MONTHS.from.%' THEN
    /********************************************************************************************************
        CALL _calend_P('INSERT.MONTHS.from.t_calendYMD', '', '', '', '');  -- dodawanie m-cy z temporarki dni
        CALL _calend_P('INSERT.MONTHS.from.to', YM1, YM1, '', '');  -- dodawanie m-cy  od YM1 do YM2
    ********************************************************************************************************/
        IF _MODE LIKE 'INSERT.MONTHS.from.to' THEN
            SET YM1 = _A, YM2 = _B;
        ELSEIF _MODE LIKE 'INSERT.MONTHS.from.t_calendYMD' THEN
            SELECT 
                MIN(tc.YM) AS YM1, MAX(tc.YM) AS YM2, MIN(tc.Y) AS Y1, MAX(tc.Y) AS Y2 
            INTO
                YM1, YM2, Y1, Y2 
            FROM `t_calendYMD` tc
            ;
        END IF;
            SET @.YM1 = YM1, @.YM2 = YM2;
        DROP TEMPORARY TABLE IF EXISTS `t_calendYM`;
        CREATE TEMPORARY TABLE `t_calendYM`
        SELECT
            ca.YM,ca.Y,ca.M,ca.cD,
            ca.YMW2 - ca.YMW1 + 1 AS cW,
            ca.D1, ca.D2,
            -- bez przesady DoM1, DoM2,
            c1.DofY AS DofY1, c2.DofY AS DofY2,
            -- DofY1, DofY2,
            c1.DofW AS DofW1, c2.DofW AS DofW2,
            WofY1, WofY2,
            MD1,MD2,
            YW1,YW2,
            c1.YW AS D1YW, c2.YW AS D2YW,
            YMW1,YMW2
            -- ,ca.*
            /*
            -- DofY1,DofY2, --   to to samo co D1DoY,D2DoY
            MD1,MD2,YW1,YW2,YMW1,YMW2,
            D1DofY,D2DofY, -- to to samo co DoY1,DoY2
            D1DofW,D2DofW,D1WofY,D2WofY,D1YW,D2YW,
            D1ofY,D2ofY
            */
        FROM (
            SELECT
                c.YM,
                c.Y,
                c.M,
                COUNT(*) AS cD,
                -- COUNT(DISTINCT c.YMW) AS cW,
                -- COUNT(DISTINCT c.WoY) AS cWX,
                -- GROUP_CONCAT(DISTINCT c.WoY) AS sWoY,
                MIN(c.D) AS D1, MAX(c.D) AS D2, 
                MIN(c.MD) AS MD1, MAX(c.MD) AS MD2,	
                -- bez przesady MIN(c.DoM) AS DoM1, MAX(c.DoM) AS DoM2,	
                MIN(c.DofY) AS DofY1, MAX(c.DofY) AS DofY2,	
                MIN(IF(c.YMW DIV 100 = c.YM, c.WofY, 99999999)) AS WofY1, 
                MAX(IF(c.YMW DIV 100 = c.YM, c.WofY, 0)) AS WofY2,
                MIN(IF(c.YMW DIV 100 = c.YM, c.YW, 99999999)) AS YW1, 
                MAX(IF(c.YMW DIV 100 = c.YM, c.YW, 0)) AS YW2,
                MIN(IF(c.YMW DIV 100 = c.YM, c.YMW, 99999999)) AS YMW1, 
                MAX(IF(c.YMW DIV 100 = c.YM, c.YMW, 0)) AS YMW2 
            FROM `_calendYMD` c
            WHERE
                TRUE
                AND c.YM BETWEEN YM1 AND YM2
                -- and !(c.IdDy <=> cd.IdDy)
            GROUP BY c.YM
        ) ca
        INNER JOIN `_calendYMD` c1 ON c1.D = ca.D1
        INNER JOIN `_calendYMD` c2 ON c2.D = ca.D2
        ;
        INSERT IGNORE INTO `_calendYM`
        (
            YM, Y, M, cD,
            cW,
            D1,D2,
            -- bez przesady DoM1, DoM2,
            DofY1, DofY2,
            -- DofY1, DofY2,
            DofW1, DofW2,
            WofY1, WofY2,
            MD1,MD2,
            YW1,YW2,
            D1YW, D2YW,
            YMW1,YMW2
        )
        SELECT
            YM, Y, M, cD,
            cW,
            tcYM.D1, tcYM.D2,
            -- bez przesady DoM1, DoM2,
            DofY1, DofY2,
            -- DofY1, DofY2,
            DofW1, DofW2,
            WofY1, WofY2,
            MD1,MD2,
            YW1,YW2,
            D1YW, D2YW,
            YMW1,YMW2
            
        FROM `t_calendYM` tcYM
        ; 
    END IF;
    IF _MODE LIKE 'INSERT.YEARS.from.%' THEN
    /********************************************************************************************************
        CALL _calend_P('INSERT.YEARS.from.t_calendYMD', '', '', '', '');  -- dodawanie lat z temporarki dni
        CALL _calend_P('INSERT.YEARS.from.to', Y1, Y1, '', '');  -- dodawanie lat od Y1 do Y2
    ********************************************************************************************************/
        IF _MODE LIKE 'INSERT.YEARS.from.to' THEN
            SET Y1 = _A, Y2 = _B;
        ELSEIF _MODE LIKE 'INSERT.YEARS.from.t_calendYMD' THEN
            SELECT 
                MIN(tc.YM) AS YM1, MAX(tc.YM) AS YM2, MIN(tc.Y) AS Y1, MAX(tc.Y) AS Y2 
            INTO
                YM1, YM2, Y1, Y2 
            FROM `t_calendYMD` tc
            ;
        END IF;
            SET @.Y1 = Y1, @.Y2 = Y2;
        DROP TEMPORARY TABLE IF EXISTS `t_calendY`;
        CREATE TEMPORARY TABLE `t_calendY`
		SELECT 
            ca.Y, 
            ca.cD, 
            (ca.YW2 - ca.YW1 + 1) AS cW, 
            ca.D1, ca.D2,
            c1.DofY AS DofY1, c2.DofY AS DofY2,
            c1.DofW AS DofW1, c2.DofW AS DofW2,
            ca.WofY1, ca.WofY2,
            c1.YM AS YM1, c2.YM AS YM2,
            c1.MD AS MD1, c2.MD AS MD2,
            (SELECT MIN(cyw.D) FROM `_calendYMD` cyw WHERE cyw.YW = ca.YW1) AS YW1StartAt,
            (SELECT MAX(cyw.D) FROM `_calendYMD` cyw WHERE cyw.YW = ca.YW2) AS YW2EndAt, 
            ca.YW1, ca.YW2,	-- to jest OK
            c1.YW AS D1YW, c2.YW AS D2YW,
            ca.YMW1, ca.YMW2,
            NULL AS DCH,
            
            NULL AS Param1, NULL AS Param2, NULL AS Param3
        FROM (
			SELECT 
                cYM.Y,
						COUNT(*) AS cDxxx,
                SUM(cYM.cD) AS cD,
                MIN(cYM.D1) AS D1, MAX(cYM.D2) AS D2, 
                MIN(cYM.WofY1) AS WofY1, MAX(cYM.WofY2) AS WofY2, 
                (MAX(cYM.WofY2) - MIN(cYM.WofY1) + 1) AS cWofY, 
                -- MIN(IF(cYM.YW DIV 100 = cYM.Y, cYM.YW, 99999999)) AS YW1, 
                -- MAX(IF(cYM.YW DIV 100 = cYM.Y, cYM.YW, 0)) AS YW2, 
                -- MIN(IF(c.YW DIV 100 = c.Y, c.YW, 99999999)) AS YW1, 
                -- MAX(IF(c.YW DIV 100 = c.Y, c.YW, 0)) AS YW2, 
                MIN(cYM.YW1) AS YW1, MAX(cYM.YW2) AS YW2, 
                -- MIN(IF(cYM.YMW DIV 100 = cYM.YM, cYM.YMW, 99999999)) AS YMW1, 
                -- MAX(IF(cYM.YMW DIV 100 = cYM.YM, cYM.YMW, 0)) AS YMW2 
                MIN(cYM.YMW1) AS YMW1, MAX(cYM.YMW2) AS YMW2 
			FROM `_calendYM` cYM
			WHERE
				-- cYM.YM = tcYM.YM
                cYM.Y BETWEEN Y1 AND Y2
			GROUP BY cYM.Y
        ) ca
        INNER JOIN `_calendYMD` c1 ON c1.D = ca.D1
        INNER JOIN `_calendYMD` c2 ON c2.D = ca.D2
        ;
        INSERT IGNORE INTO `_calendY`
        (
            Y, cD, cW,
            D1,D2,
            -- bez przesady DoM1, DoM2,
            DofY1, DofY2,
            -- DofY1, DofY2,
            DofW1, DofW2,
            WofY1, WofY2,
            MD1,MD2,
            YW1StartAt, YW2EndAt,
            YW1,YW2,
            YM1, YM2,
            D1YW, D2YW,
            YMW1,YMW2
        )
        SELECT
            Y, cD, cW,
            tcY.D1, tcY.D2,
            -- bez przesady DoM1, DoM2,
            DofY1, DofY2,
            -- DofY1, DofY2,
            DofW1, DofW2,
            WofY1, WofY2,
            MD1,MD2,
            YW1StartAt, YW2EndAt,
            YW1,YW2,
            tcY.YM1, tcY.YM2,
            D1YW, D2YW,
            YMW1,YMW2
			
        FROM `t_calendY` tcY
        ; 
        UPDATE `_calendY` cY,
        (
            SELECT
                cY.Y,
                @.n := cY.Y AS n,
                @.a := @.n MOD 19 AS a,
                @.b := @.n MOD 4 AS b,
                @.c := @.n MOD 7 AS c,
                @.k := 24 AS k,	--	zawsze międzye 1900 - 2199
                @.i := IF(cY.Y < 2100, 5, 6) AS i,	--	1900 - 2099 5, 2100 - 2199 6
                @.d := (19*@.a + @.k) MOD 30 AS d,
                @.e := (2*@.b + 4*@.c + 6*@.d + @.i) MOD 7 AS e,
                @.d.Mar := (22 + @.d + @.e) AS marca,
                @.d.Kwi := (@.d + @.e - 9) AS kwietnia,
                @.D.WN := DATE(CONCAT(cY.Y, '-03-01')) + INTERVAL @.d.Mar - 1 DAY AS D_III,
                DATE(CONCAT(cY.Y, '-04-01')) + INTERVAL @.d.Kwi - 1 DAY AS D_IV
            FROM `t_calendY` tcY, `_calendY` cY 
            WHERE
                cY.Y = tcY.Y
        ) tWN
        SET
            cY.DCH = tWN.D_III
        WHERE
            cY.Y = tWN.Y
        ;
    END IF;
    IF _MODE LIKE 'INIT._calendDofY%' THEN
    /********************************************************************************************************
        CALL _calend_P('INIT._calendDofY', '', '', '', '');  
    ********************************************************************************************************/
        TRUNCATE `_calendDofY`;
        INSERT IGNORE INTO `_calendDofY`
        (IdDy, fDay, IsHoliday, WnShift, Descr, MD)
        SELECT 
            th.IdDy, th.fDay, th.fDay LIKE 'HOL%' AS IsHoliday, -- th.Swieto as IsHoliday, 
            IF(th.MD IN (101, 106, 501, 503, 815, 1101, 1102, 1111, 1224, 1225, 1226), NULL, 0)AS WnShift,
            -- not th.tDay LIKE 'HOL%' AS IsWork, th.roboczy as IsWork, 
            th.Opis AS Descr,
            th.MD 
        FROM 
        (
            SELECT
                -- DAY(k.Data)+100*MONTH(k.Data) AS idDate,
                DATE_FORMAT(k.Data, 'yyyy.%m.%d') AS IdDy,
                CONCAT_WS('.',
                    IF(swieto = 1, 'HOL', NULL),
                    IF(k.RodzSwieta = 1, 'PUB', NULL),
                    IF(k.Opis IN ('Tłusty czwartek', 'Ostatnia sobota karnawału', 'Ostatki'), 'PUB', IF(k.RodzSwieta = 2, 'REL', NULL)),
                    NULL
                ) AS fDay,
                DAY(k.Data)+100*MONTH(k.Data) AS MD,
                k.Opis
            FROM `kalendarz` k 
            WHERE 
                TRUE
                -- and rodzSwieta = 2 
                AND YEAR(DATA) BETWEEN 2015 AND 2035 
                AND YEAR(DATA) = 2023
            GROUP BY k.Opis
            ORDER BY k.data
        ) th

        WHERE
            TRUE
            -- and th.MD < 10000
        ;
                    -- select * from `_calendDofY`;
                    -- select * from `kalendarz` k where year(k.DATA) = 2023;
        UPDATE `_calendDofY` th
        SET
            th.MD = NULL,
            th.WnShift = 
                IF(th.Descr = 'Tłusty czwartek', -52,
                IF(th.Descr = 'Ostatnia sobota karnawału', -50,
                IF(th.Descr = 'Ostatki', -47,
                IF(th.Descr = 'Popielec', -46,
                IF(th.Descr = 'Niedziela Palmowa', -7,
                IF(th.Descr = 'Wielki Czwartek', -3,
                IF(th.Descr = 'Wielki Piątek', -2,
                IF(th.Descr = 'Wielka Sobota', -1,
                IF(th.Descr = 'Wielkanoc', 0,
                IF(th.Descr = 'Poniedziałek Wielkanocny (Lany Poniedziałek)', 1,
                IF(th.Descr = 'Wniebowstąpienie', 39,
                IF(th.Descr = 'Zesłanie Ducha Św.- Zielone świątki', 49,
                IF(th.Descr = 'Boże Ciało', 60, 0
                )))))))))))))
        --	dBC:	-52	Tłusty czwartek
        --	dBC:	-50	Ostatnia sobota karnawału
        --	dBC:	-47	Ostatki
        --	dBC:	-46	Popielec
        --	dWCzw	-3
        --	dNdP	-7	
        --	dWNB	39	Wniebowstąpienie
        --	dZS	49	Zesłanie Ducha Św.- Zielone świątki
        --	dBC:	60	Boże Ciało
        WHERE
            th.WnShift IS NOT NULL
        ;
        UPDATE `_calendDofY` th
        SET
            th.IdDy = CONCAT_WS('.','yyyy', 'xx', 
                -- IF(th.WnShift < 0, CONCAT(_VarStr(th.WnShift)), IF(th.WnShift = 0, '000', CONCAT('',_VarStr(th.WnShift)))), 
                IF(th.WnShift = -52, '0.Tl.Cz',
                IF(th.WnShift = -50, '1.Ost.Sob.Karn',
                IF(th.WnShift = -47, '2.Ost.Wt',
                IF(th.WnShift = -46, '3.Sr.Pop',
                IF(th.WnShift = -7, 'A.Nd.Pal',
                IF(th.WnShift = -3, 'B.Wlk.Cz',
                IF(th.WnShift = -2, 'C.Wlk.Pt',
                IF(th.WnShift = -1, 'D.Wlk.So',
                IF(th.WnShift = 0, 'Wielkanoc.Nd',
                IF(th.WnShift = 1, 'Wielkanoc.Pn',
                IF(th.WnShift = 39, 'X.WNB',
                IF(th.WnShift = 49, 'Y.ZesDSw',
                IF(th.WnShift = 60, 'Z.BC', 
                NULL
                )))))))))))))
            )
        WHERE
            th.WnShift IS NOT NULL
        ;



        /*			
        UPDATE `_calendDofY` th
        SET
            th.MD = NULL,
            th.IdDy = CONCAT_WS('.','yyyy', 'mm',
                IF(th.Descr = 'Tłusty czwartek', 'Tl.Cz',
                IF(th.Descr = 'Ostatnia sobota karnawału', 'Ost.Sob.Karn',
                IF(th.Descr = 'Ostatki', 'Ost.Wt',
                IF(th.Descr = 'Popielec', 'Sr.Pop',
                IF(th.Descr = 'Niedziela Palmowa', 'Nd.Pal',
                IF(th.Descr = 'Wielki Czwartek', 'Wlk.Cz',
                IF(th.Descr = 'Wielki Piątek', 'Wlk.Pt',
                IF(th.Descr = 'Wielka Sobota', 'Wlk.So',
                IF(th.Descr = 'Wielkanoc', 'Wielkanoc.Nd',
                IF(th.Descr = 'Poniedziałek Wielkanocny (Lany Poniedziałek)', 'Wielkanoc.Pn',
                IF(th.Descr = 'Wniebowstąpienie', 'WNB',
                IF(th.Descr = 'Zesłanie Ducha Św.- Zielone świątki', 'ZesDSw',
                IF(th.Descr = 'Boże Ciało', 'BC', 
                NULL
                )))))))))))))
            ),
            th.WnShift = 
                IF(th.Descr = 'Tłusty czwartek', -52,
                IF(th.Descr = 'Ostatnia sobota karnawału', -50,
                IF(th.Descr = 'Ostatki', -47,
                IF(th.Descr = 'Popielec', -46,
                IF(th.Descr = 'Niedziela Palmowa', -7,
                IF(th.Descr = 'Wielki Czwartek', -3,
                IF(th.Descr = 'Wielki Piątek', -2,
                IF(th.Descr = 'Wielka Sobota', -1,
                IF(th.Descr = 'Wielkanoc', 0,
                IF(th.Descr = 'Poniedziałek Wielkanocny (Lany Poniedziałek)', 1,
                IF(th.Descr = 'Wniebowstąpienie', 39,
                IF(th.Descr = 'Zesłanie Ducha Św.- Zielone świątki', 49,
                IF(th.Descr = 'Boże Ciało', 60, 0
                )))))))))))))
        --	dBC:	-52	Tłusty czwartek
        --	dBC:	-50	Ostatnia sobota karnawału
        --	dBC:	-47	Ostatki
        --	dBC:	-46	Popielec
        --	dWCzw	-3
        --	dNdP	-7	
        --	dWNB	39	Wniebowstąpienie
        --	dZS	49	Zesłanie Ducha Św.- Zielone świątki
        --	dBC:	60	Boże Ciało
                
        WHERE
            th.WnShift IS NOT NULL
        ;
        */
                    --	SELECT * FROM `_calendDofY` ORDER BY MD, IdDy, WnShift, fday;
                    -- SELECT * FROM ht.`Kalendarium`;
                    -- SELECT * FROM `t_kalendarium`;
                    -- SELECT * FROM _calendDofY;
        INSERT IGNORE INTO `_calendDofY`
        (IdDy, fDay, Descr, MD, N, N_Short)
        SELECT 
            -- tk.MD as IdDy, 
            CONCAT_WS('.', 'yyyy', LPAD(tk.Miesiac, 2, '0'), LPAD(tk.dzien, 2, '0')) AS IdDy,
            NULL AS fDay, '' AS Descr,
            tk.MD, tk.imiona, tk.Imiona_short
        FROM 
            (
            SELECT	
                k.dzien+100*k.miesiac AS MD,
                k.*	
            FROM `Kalendarium` k
            GROUP BY miesiac, dzien
            ORDER BY miesiac, dzien
            ) tk
        ON DUPLICATE KEY UPDATE
            N = tk.imiona, N_Short = tk.Imiona_short
        ;

        UPDATE `_calendDofY` th
        SET
            -- th.IdDy = REPLACE(th.IdDy, 'yyyy', th.fDay)
            th.IdDy = CONCAT_WS('.', th.IdDy,
                IF(th.MD = 101, 'Nowy.Rok.1 I',
                IF(th.MD = 106, '3.Kroli.6 I',
                IF(th.MD = 501, 'Sw.1 V',
                IF(th.MD = 502, 'Fla.2 V',
                IF(th.MD = 503, 'Sw.3 V',
                IF(th.MD = 815, 'Sw.15 VIII',
                IF(th.MD = 1101, 'Wsz.Sw.1 XI',
                IF(th.MD = 1102, 'Zadusz.2 XI',
                IF(th.MD = 1111, 'Sw.11 XI',
                IF(th.MD = 1130, 'Andrzejki.30 XI',
                IF(th.MD = 1206, 'Mikolaj.6 XII',
                IF(th.MD = 1224, 'Wig.BN.24 XII',
                IF(th.MD = 1225, 'BN.25 XII',
                IF(th.MD = 1226, 'BN.26 XII',
                IF(th.MD = 1231, 'Sylw.31 XII',
                NULL -- th.IdDy
                )))))))))))))))
                ),
            th.fDay = 
                IF(th.MD IN (502, 1130, 1206, 1231), 'PUB',
                th.fDay
                ),
            th.Descr = 
                IF(th.MD = 502, 'Święto Flagi Państwowej',
                IF(th.MD = 1130, 'Andrzejki',
                IF(th.MD = 1206, 'Mikołajki',
                IF(th.MD = 1231, 'Sylwester',
                th.Descr
                ))))
        WHERE
            th.WnShift IS NULL
        ;
        UPDATE `_calendDofY` th
        SET
            th.IdDy = CONCAT_WS(':', th.fDay, th.IdDy)
        WHERE
            NOT th.IdDy LIKE 'yyyy%'
        ;
    END IF;
END$$
DELIMITER ;

/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================
DROP TABLE `_calendYMD`;
CREATE TABLE `_calendYMD` (
  `D` DATE NOT NULL COMMENT 'dzien',
  `Y` INT(11) DEFAULT NULL COMMENT 'rok',
  `M` INT(11) DEFAULT NULL COMMENT 'miesiac roku',
  `DofM` INT(11) DEFAULT NULL COMMENT 'dzien m-ca',
  `DofW` INT(11) DEFAULT NULL COMMENT 'dzien tyg. 1..7 (pn,wt..)',
  `DofY` INT(11) DEFAULT NULL COMMENT 'dzien roku',
  `WofY` INT(11) DEFAULT NULL COMMENT 'tydzien w roku',
  `MD` INT(11) DEFAULT NULL COMMENT 'm-co-dzień dla 12 III 312 dla 5 XI 1105',
  `YW` INT(11) DEFAULT NULL COMMENT 'roko-tydzień YEARWEEK(date,3) ISO 8601:1988',
  `YM` INT(11) DEFAULT NULL COMMENT 'rok/m-c',
  `YMW` INT(11) DEFAULT NULL COMMENT 'rok/m-c/tydz - uwaga tydzien na przelomie m-ca jest dublowany ISO 8601:1988 ',
  `IdDy` VARCHAR(50) DEFAULT NULL COMMENT 'ID dnia w roku',
  `swieto` INT(11) NOT NULL DEFAULT 0,
  `swietoSoNd` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'święto w sob "So", w nie "Nd"',
  `swietoAltDni` VARCHAR(150) DEFAULT NULL COMMENT 'po średniku podajemy daty altern. urlopu za ten dzień',
  `Working_Day_A` INT(11) DEFAULT NULL COMMENT 'Working_Day_A, B, C, D są to cztery pola określające aktywnośc tego dnia (np. pracujący dzień, wyjazdy do sklepów etc.) Pola te mogą mie wartoś 0 brak aktywności, 1 jest aktywnoś i NULL czyli nie określona wprost',
  `Working_Day_B` INT(11) DEFAULT NULL COMMENT 'patrz Working_Day_A',
  `Working_Day_C` INT(11) DEFAULT NULL COMMENT 'patrz Working_Day_A',
  `Working_Day_D` INT(11) DEFAULT NULL COMMENT 'patrz Working_Day_A',
  `Working_Day_NewDoc` INT(11) DEFAULT NULL COMMENT 'dni tyg. w ktorych wystawiamy dok. realizujace zam.',
  PRIMARY KEY (`D`),
  KEY `YM` (`YM`),
  KEY `YW` (`YW`),
  KEY `YMW` (`YMW`),
  KEY `Working_Day_A` (`Working_Day_A`),
  KEY `Working_Day_B` (`Working_Day_B`),
  KEY `Working_Day_NewDoc` (`Working_Day_NewDoc`),
  KEY `MD` (`MD`),
  KEY `Y` (`Y`),
  -- KEY `YW_2` (`YW`),
  KEY `IdDy` (`IdDy`),
  KEY `swieto` (`swieto`)
) COMMENT='tabela kalendarza wersja kalend. nowa od 2025'
;
CREATE TABLE `_calendYM` (
  `YM` INT(11) NOT NULL DEFAULT 0 COMMENT 'Rok*100 + Miesiac albo jeśli to cało roczny to sam Rok',
  `Y` INT(11) NOT NULL DEFAULT 0 COMMENT 'Rok',
  `M` INT(11) NOT NULL DEFAULT 0 COMMENT 'M-c',
  `cD` INT(11) NOT NULL DEFAULT 0 COMMENT 'Ile dni w tym m-cu',
  `cW` INT(11) NOT NULL DEFAULT 0 COMMENT 'Ile tyg. w tym m-cu',
  `D1` DATE DEFAULT NULL,
  `D2` DATE DEFAULT NULL,
  `DofY1` INT(11) DEFAULT NULL COMMENT 'dzien roku',
  `DofY2` INT(11) DEFAULT NULL COMMENT 'dzien roku',
  `DofW1` INT(11) DEFAULT NULL COMMENT 'dzien tyg. 1..7 (pn,wt..)',
  `DofW2` INT(11) DEFAULT NULL COMMENT 'dzien tyg. 1..7 (pn,wt..)',
  `WofY1` INT(11) DEFAULT NULL COMMENT 'tydzien w roku',
  `WofY2` INT(11) DEFAULT NULL COMMENT 'tydzien w roku',
  `MD1` INT(11) DEFAULT NULL COMMENT 'm-co-dzień dla 12 III 312 dla 5 XI 1105 od',
  `MD2` INT(11) DEFAULT NULL COMMENT 'm-co-dzień dla 12 III 312 dla 5 XI 1105 do',
  `YW1` INT(11) DEFAULT NULL COMMENT 'roko-tydzień YEARWEEK(date,3) ISO 8601:1988 od',
  `YW2` INT(11) DEFAULT NULL COMMENT 'roko-tydzień YEARWEEK(date,3) ISO 8601:1988 do',
  `YMW1` INT(11) DEFAULT 0 COMMENT 'pierw. rok/m-c/tydz - uwaga tydzien na przelomie m-ca jest dublowany',
  `YMW2` INT(11) DEFAULT 0 COMMENT 'ost. rok/m-c/tydz - uwaga tydzien na przelomie m-ca jest dublowany',
  `Param1` VARCHAR(150) DEFAULT NULL COMMENT '???',
  `Param2` VARCHAR(150) DEFAULT NULL COMMENT '???',
  `Param3` VARCHAR(150) DEFAULT NULL COMMENT '???',
  PRIMARY KEY (`YM`),
  KEY `Y` (`Y`),
  KEY `M` (`M`),
  KEY `D12` (`D1`,`D2`)
)
;

*/
