基本編で紹介したものに、データをUDFで拾ってきて、CONCATしてます。

[ 実行結果 ]

[ コード ]

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_sel_acalendar_vslccpp`(
	IN `ym` INT,
	IN `vslnm` VARCHAR(36),
	IN `idmultiple` SMALLINT

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '船寄港国単月カレンダー (使用)'
BEGIN	

DECLARE ymprev INT;
DECLARE ymnext INT;

SET ymprev = ym - 1;
SET ymnext = ym + 1;

IF (ymprev % 100 = 0) THEN
  SET ymprev = ymprev - 88;  
END IF;
IF (ymnext % 100 = 13) THEN
  SET ymnext = ymnext + 88;  
END IF;



SELECT
  YWK * idmultiple AS id,
  YWK, 
	
	
	MON, 
	TUE, WED, THU, FRI, SAT, 
	SUN,
	
	DTMIN, DTMAX,
	
	YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN) AS DTMINVAL,
	YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX) AS DTMAXVAL,
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) AS DTMINYM,
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) AS DTMAXYM
	
FROM
	(
	SELECT
	  
  	YWK,
  	MIN(YM) AS YMMIN,
  	MAX(YM) AS YMMAX,
  	MAX(SUN) AS SUN,
  	MAX(MON) AS MON,
  	MAX(TUE) AS TUE,
  	MAX(WED) AS WED,
  	MAX(THU) AS THU,
  	MAX(FRI) AS FRI,
  	MAX(SAT) AS SAT,
  	MIN(dt) AS DTMIN, MAX(dt) AS DTMAX
	FROM
		(
		SELECT
  		dt, 
  
  		CASE WHEN (WEEKOFYEAR(dt) = 53) THEN 
			  (YEAR(dt) - 1) * 100 + WEEKOFYEAR(dt)
	  	
	  		ELSE YEAR(dt) * 100 + WEEKOFYEAR(dt)  
	  	  
			END AS YWK,
	
			YEAR(dt) * 100 + MONTH(dt) AS YM,
  
  		CASE WHEN (DAYOFWEEK(dt) = 1) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt)) 
			ELSE '' END AS SUN,
			
			CASE WHEN (DAYOFWEEK(dt) = 2) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS MON,
			
			CASE WHEN (DAYOFWEEK(dt) = 3) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS TUE,
			
			CASE WHEN (DAYOFWEEK(dt) = 4) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS WED,
			
			CASE WHEN (DAYOFWEEK(dt) = 5) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS THU,
			
			CASE WHEN (DAYOFWEEK(dt) = 6) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS FRI,
			
			CASE WHEN (DAYOFWEEK(dt) = 7) THEN 
			CONCAT(DATE_FORMAT(dt, '%e'), ':', fc_vesselcntry_bydt (vslnm, dt), ':', fc_vesselport_bydt (vslnm, dt))
			ELSE '' END AS SAT
			
		FROM
  		bimpsch.m_date
		WHERE
			YEAR(dt) * 100 + MONTH(dt) BETWEEN ymprev AND ymnext
			
		) a
	GROUP BY
  	YWK
	) b	
WHERE
	
	FLOOR((YEAR(DTMIN) * 10000 + MONTH(DTMIN) * 100 + DAY(DTMIN)) / 100) = ym OR 
	FLOOR((YEAR(DTMAX) * 10000 + MONTH(DTMAX) * 100 + DAY(DTMAX)) / 100) = ym
	
	
ORDER BY
  DTMIN;	
	
	
END

[ 利用例 ]

船の寄港国、ポップアップで港が現れる

右クリックコンテキストメニューを配置 => 列値、行IDとかで更新処理できる

[ jqGrid colModel例 ]

          colNames: ['月', '火', '水', '木', '金', '土', '日', '', '', ''],
          colModel: [
            {name: 'mon', width: '140px', classes: 'caltd calentd'},
            {name: 'tue', width: '140px', classes: 'caltd'},
            {name: 'wed', width: '140px', classes: 'caltd'},
            {name: 'thu', width: '140px', classes: 'caltd'},
            {name: 'fri', width: '140px', classes: 'caltd'},
            {name: 'sat', width: '140px', classes: 'caltd'},
            {name: 'sun', width: '140px', classes: 'caltd'},
            {name: 'id', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmin', hidden: true,
              editrules: {edithidden: true}
            },
            {name: 'dtmax', hidden: true,
              editrules: {edithidden: true}
            },
          ],