/*
Navicat MySQL Data Transfer

Source Server         : MySqlLocal
Source Server Version : 80012
Source Host           : localhost:3306
Source Database       : azlogistik

Target Server Type    : MYSQL
Target Server Version : 80012
File Encoding         : 65001

Date: 2023-05-24 15:04:44
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `transaksi`
-- ----------------------------
DROP TABLE IF EXISTS `transaksi`;
CREATE TABLE `transaksi` (
  `id` int(17) NOT NULL AUTO_INCREMENT,
  `jam_masuk` time DEFAULT NULL,
  `jam_keluar` time DEFAULT NULL,
  `tarif` decimal(17,0) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of transaksi
-- ----------------------------
INSERT INTO transaksi VALUES ('13', '19:00:00', '08:00:00', '275000');
INSERT INTO transaksi VALUES ('14', '10:00:00', '14:00:00', '21000');
INSERT INTO transaksi VALUES ('15', '18:00:00', '23:00:00', '46000');
INSERT INTO transaksi VALUES ('16', '05:00:00', '12:00:00', '54000');

-- ----------------------------
-- Procedure structure for `hitung`
-- ----------------------------
DROP PROCEDURE IF EXISTS `hitung`;
DELIMITER ;;
CREATE PROCEDURE `hitung`(IN IDX CHAR(10))
BEGIN
	
	SELECT jam_masuk,jam_keluar INTO @jm,@jk FROM transaksi WHERE id = IDX;
	 
	SELECT IF(@jm BETWEEN '22:00' AND '24:00','1','0') INTO @xm;
  SELECT IF(@jm BETWEEN '00:00' AND '06:00','1','0') INTO @ym;

	SELECT IF(@jk BETWEEN '22:00' AND '24:00','1','0') INTO @xk;
  SELECT IF(@jk BETWEEN '00:00' AND '06:00','1','0') INTO @yk;

	
	SET @zm := @xm+@ym;
	SET @zk := @xk+@yk;

	SET @masuk   := '5000';
	IF (@jm <= '24:00' AND '00:00' <= @jk  AND @jk < @jm) THEN
		SET @masuk := @masuk + '50000';
	END IF;
	
	SET @tarif := 0 ;

	IF ( @zm > 0 AND @zk > 0 ) THEN
		SELECT HOUR(TIMEDIFF(@jk,@jm)) INTO @durasi;
		SET @tarif := @durasi * 25000; 
	END IF;

	IF ( @zm > 0 AND @zk = 0 ) THEN
		SELECT HOUR(TIMEDIFF('06:00',@jm)) INTO @durasi1;
		SELECT HOUR(TIMEDIFF(@jk,'06:00')) INTO @durasi2;
		SET @tarif := (@durasi1 * 25000) + (@durasi2 * 4000); 
	END IF;

	IF ( @zm = 0 AND @zk > 0 ) THEN
		SELECT HOUR(TIMEDIFF('22:00',@jm)) INTO @durasi1;
		SELECT HOUR(TIMEDIFF(@jk,'22:00')) INTO @durasi2;
		SET @tarif := (@durasi1 * 4000) + (@durasi2 * 25000); 
	END IF;

	IF ( @zm = 0 AND @zk = 0 ) THEN
		SELECT HOUR(TIMEDIFF(@jk,@jm)) INTO @durasi;
		SET @tarif := @durasi * 4000;
	END IF;

	IF ( @jm < '22:00' AND @jk > '06:00' AND @jm > @jk ) THEN
		SELECT HOUR(TIMEDIFF('22:00',@jm))     INTO @durasi1;
		SELECT HOUR(TIMEDIFF(@jk,'06:00'))     INTO @durasi2;
		SET @tarif := ((@durasi1+@durasi2) * 4000) + (8 * 25000); 
	END IF;

	UPDATE transaksi SET tarif = @masuk + @tarif WHERE id = IDX;
	
	SELECT (@masuk + @tarif) BAYAR;

END
;;
DELIMITER ;
