fasilitas ini sebenernya fasilitas kuno yang tersedia di mysql. cuman kali ini aja kayaknya butuh prosedur untuk menginput datanya. so, belajar dulu. dan alhamdulillah berhasil sesuai dengan yang diinginkan. 🙂
ini dia prosedure buatan saya:
DELIMITER $$
DROP PROCEDURE IF EXISTS `save_state`$$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `kang_aep`.`save_state`(id_line VARCHAR(10),
fg_qty INT,
ng_qty INT,
er_qty INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’*/
BEGIN
DECLARE last_id INT(11) DEFAULT 0;
DECLARE last_fg INT(11) DEFAULT 0;
DECLARE last_ng INT(11) DEFAULT 0;
DECLARE last_er INT(11) DEFAULT 0;
DECLARE newNG BOOL DEFAULT FALSE;
SELECT MAX(t_counting_id)
INTO last_id
FROM t_counting
WHERE t_counting.`line_id` = id_line;SELECT t_counting_id,
COALESCE( fg_quantity,0),
COALESCE(ng_quantity,0),
COALESCE(e_quantity,0)
INTO last_id, last_fg, last_ng, last_er
FROM t_counting
WHERE t_counting.`line_id` = id_line
AND t_counting_id = last_id;WHILE last_fg < fg_qty DO
SET last_fg := last_fg + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “FG”);
END WHILE;WHILE last_ng < ng_qty DO
SET last_ng := last_ng + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “NG”);
SET newNG := TRUE;
END WHILE;WHILE last_er < er_qty DO
SET last_er := last_er + 1;
INSERT INTO t_counting(line_id,ng_quantity, fg_quantity,e_quantity, TYPE)
VALUE(id_line, last_ng, last_fg, last_er, “ER”);
END WHILE;
IF newNG THEN
INSERT INTO `t_message`(`number`, `message`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
INSERT INTO outbox(`DestinationNumber`, `TextDecoded`)
(SELECT `telp`, CONCAT(“Telah terdeteksi metal di line (“, `linename` ,”), plan (“, `planname`, “) sejumlah “, `ng_quantity`, “pcs dengan klaim error sejumlah “, `e_quantity`, “pcs.”) message
FROM `v_pre_sms` WHERE `v_pre_sms`.`line_id` = id_line);
END IF;
END$$DELIMITER ;
alhamdulillah berjalan dengan baik sekali. 🙂
Catatan bukhori muslim 