shiftlog-machines

DELIMITER $$
DROP PROCEDURE IF EXISTS `mysp`$$
CREATE PROCEDURE `mysp`(
IN i_date DATE,
IN i_group INT,
IN i_oprtr INT
)
BEGIN
 -- DECLARE variables
 DECLARE prepsql varchar(1000);
 DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
 DECLARE dstamp_val VARCHAR(20);
 DECLARE dstamp_cur CURSOR FOR
         SELECT DISTINCT dstamp
         FROM shiftlog
         WHERE dstamp BETWEEN i_date AND DATE_ADD(i_date,INTERVAL 6 DAY)
         ORDER BY dstamp;
 DECLARE continue HANDLER FOR NOT FOUND
         SET no_more_rows = TRUE;
 -- start generating sql
 SET prepsql = 'SELECT s.mcode, m.speed_ref, m.d_group, m.optr_grp ';
 OPEN dstamp_cur;
 the_loop: LOOP
         -- fetch data
         FETCH dstamp_cur
         INTO dstamp_val;
         -- break out loop
         IF no_more_rows THEN
            CLOSE dstamp_cur;
            LEAVE the_loop;
         END IF;
         -- display
         SET prepsql = CONCAT(prepsql,'\n, CASE WHEN dstamp=\'',dstamp_val,'\' THEN IFNULL((h23-h16),0) else 0 end  "',dstamp_val,'"');
 END LOOP the_loop;
 SET prepsql = CONCAT(prepsql,'\nFROM shiftlog s LEFT JOIN machines m ON s.mcode=m.mcode WHERE m.mloc=i_group AND s.dstamp=i_date AND m.oprtr_grp=i_oprtr');
 -- executing generated query
 SET @mysql = prepsql;
 PREPARE stmt FROM @mysql;
 EXECUTE stmt;
 -- clean up
 DROP PREPARE stmt;
END$$
DELIMITER;
-- call mysp($from,$group,$nim);


-- atau gunakan ini, untuk jam1 s.d. jam7 dan speed1 s.d speed7, hitung via php programming
SELECT s.mcode AS mcode2,
   m.speed_ref,
   m.d_group,
   m.oprtr_grp,
  (h23 - h16) as cntr1,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 1 DAY) AND mcode=mcode2) AS cntr2,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 2 DAY) AND mcode=mcode2) AS cntr3,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 3 DAY) AND mcode=mcode2) AS cntr4,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 4 DAY) AND mcode=mcode2) AS cntr5,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 5 DAY) AND mcode=mcode2) AS cntr6,
  (SELECT (h23 - h16) FROM shiftlog WHERE dstamp=DATE_ADD('$from',INTERVAL 6 DAY) AND mcode=mcode2) AS cntr7,
FROM  shiftlog s
LEFT JOIN machines m
  ON s.mcode = m.mcode
WHERE m.mloc = '$group'
  AND s.dstamp = '$from'
  AND m.oprtr_grp = '$nim'"