 /*  Zmiany CVS
$Log: v_calend.sql,v $
Revision 1.3  2025/05/12 07:52:24  jmam
Stan na 12 V 2025

Revision 1.2  2025/04/26 09:26:24  jmam
$send$ inicjacja IV 2025
*/


DELIMITER $$
DROP VIEW IF EXISTS `v_calendYMD`;$$
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW `v_calendYMD` AS
SELECT 
	c.D, 
	c.DStr,
	-- dd.Symbol AS DStr,
	DATE_FORMAT(c.D, '%a %e %b') AS Dndm,
	DATE_FORMAT(c.D, '%a %e %b %Y') AS Dndmy,
	CONCAT_WS(' ', c.DStr, cH.descr) AS Descr,
	cD.N_Short,
	c.swieto, 
	IFNULL(cH.fDay, '') AS Holiday_Kind,
	c.Y, c.M, 
	c.DofM, c.DofW, c.DofY, c.WofY, c.MD, c.YW, c.YM, c.YMW,
	c.swietoSoNd,
    c.swieto AS GOV_Free_Day,
	IFNULL(c.Working_Day_A, IF(c.swieto = 1, 0, (FIND_IN_SET(c.DofW, Working_days_A.Value) > 0))) AS Working_Day_A,
	IFNULL(c.Working_Day_B, IF(c.swieto = 1, 0, (FIND_IN_SET(c.DofW, Working_days_B.Value) > 0))) AS Working_Day_B,
	IFNULL(c.Working_Day_C, IF(c.swieto = 1, 0, (FIND_IN_SET(c.DofW, Working_days_C.Value) > 0))) AS Working_Day_C,
	IFNULL(c.Working_Day_D, IF(c.swieto = 1, 0, (FIND_IN_SET(c.DofW, Working_days_D.Value) > 0))) AS Working_Day_D,
	IFNULL(c.Working_Day_NewDoc, IF(c.swieto = 1, 0, (FIND_IN_SET(c.DofW, Working_days_NewDoc.Value) > 0))) AS Working_Day_NewDoc,
	-- IFNULL(h.GOV_Free_Day, 0) AS GOV_Free_Day
	cH.IdDy 
FROM `_calendYMD` c -- NIE!!! USE KEY (PRIMARY)
INNER JOIN `_db_globals` Working_days_A ON Working_days_A.key = 'Working_days_A'
INNER JOIN `_db_globals` Working_days_B ON Working_days_B.key = 'Working_days_B'
INNER JOIN `_db_globals` Working_days_C ON Working_days_C.key = 'Working_days_C'
INNER JOIN `_db_globals` Working_days_D ON Working_days_D.key = 'Working_days_D'
INNER JOIN `_db_globals` Working_days_NewDoc ON Working_days_NewDoc.key = 'Working_days_NewDoc'
LEFT JOIN `_calendDofY` cH USE KEY (`PRIMARY`) ON cH.IdDy = c.IdDy
LEFT JOIN `_calendDofY` cD USE KEY (`MD`) ON cD.MD = c.MD
WHERE
	TRUE
;
$$
DELIMITER ;

DELIMITER $$
/*
    View `_today_` - do przeliczeń - patrz `_pl_today_` w v_pl.sql
*/
CREATE OR REPLACE VIEW `_today_` AS
SELECT 
	c.D, c.Y, c.M, c.DofM, c.DofW, c.DofY, c.WofY, c.MD, c.YW, c.YM, c.YMW, c.swieto, c.swietoSoNd, 
--	precv 1 day _xxx	
	c.D - INTERVAL 1 DAY AS _D,
	c.Y - 1 AS _Y,
	MONTH(c.D - INTERVAL 1 MONTH) AS _M,
	DAY(c.D - INTERVAL 1 DAY) AS _DofM,
	IF(c.DofW = 1, 7, c.DofW - 1) AS _DofW,
	DAYOFYEAR(c.D - INTERVAL 1 DAY) AS _DofY,
	PERIOD_ADD(c.YM, -1) AS _YM,
--	next 1 day xxx_	
	c.D + INTERVAL 1 DAY AS D_,
	c.Y + 1 AS Y_,
	MONTH(c.D + INTERVAL 1 MONTH) AS M_,
	DAY(c.D + INTERVAL 1 DAY) AS DoM_,
	IF(c.DofW = 7, 1, c.DofW + 1) AS DoW_,
	DAYOFYEAR(c.D + INTERVAL 1 DAY) AS DoY_,
	PERIOD_ADD(c.YM, 1) AS YM_,
--	precv 1 month _mxxx	
	c.D - INTERVAL 1 MONTH AS _mD,
	DAY(c.D - INTERVAL 1 MONTH) AS _mDm,
	DAYOFYEAR(c.D - INTERVAL 1 MONTH) AS _mDy,
	YEAR(c.D - INTERVAL 1 MONTH) AS _mY,
--	next 1 month mxxx_	
	c.D + INTERVAL 1 MONTH AS mD_,
	DAY(c.D + INTERVAL 1 MONTH) AS mDm_,
	DAYOFYEAR(c.D + INTERVAL 1 MONTH) AS mDy_,
	YEAR(c.D + INTERVAL 1 MONTH) AS mY_,
--	Valid ...	
	IF(c.M <= 6,
		(c.Y - 1)*100 + 7,		-- poprz. rok ale od połowy - od lip.
		(c.Y + 0)*100 + 1		-- tylko biezacy rok - od sty
	) AS Valid_Min_YM,
	-- IF(MONTH(c.D) <= 6,
	IF(c.M <= 6,
		(c.Y + 0)*100 + 12,	-- poprz. rok ale od połowy - od lip.
		(c.Y + 1)*100 + 6		-- tylko biezacy rok - od sty
	) AS Valid_Max_YM,
	-- IF(MONTH(c.D) <= 6,
	IF(c.M <= 6,
		(c.Y - 1),		-- poprz. rok ale od połowy - od lip.
		(c.Y + 0)		-- tylko biezacy rok - od sty
	) AS Valid_Min_Y,
	IF(c.M <= 6,
		(c.Y + 0),		-- poprz. rok ale od połowy - od lip.
		(c.Y + 1)		-- tylko biezacy rok - od sty
	) AS Valid_Max_Y,
	-- DATE(CONCAT_WS('-', YEAR(c.D) - 2, 1, 1)) AS PxH_D1,
	MAKEDATE(c.Y - 2, 1) AS PxH_D1,
	-- DATE(CONCAT_WS('-', YEAR(c.D) + 1, 12, 31)) AS PxH_D2,
	LAST_DAY(MAKEDATE(c.Y + 1, 360)) AS PxH_D2,
	-- YEAR(c.D) - 2 AS PxH_Y1,
	c.Y - 2 AS PxH_Y1,
	-- YEAR(c.D) + 1 AS PxH_Y2
	c.Y + 1 AS PxH_Y2
	
FROM `_calendYMD` c 
WHERE 
	c.D = CURRENT_DATE()
;
$$
DELIMITER ;
