Multi Level Marketing

// struktur tabel
CREATE TABLE `deposit_cb` (
  `id` int(11) NOT NULL DEFAULT '0',
  `kode_trans` varchar(50) NOT NULL,
  `kode_ref` varchar(50) NOT NULL,
  `kotor` decimal(10,2) NOT NULL DEFAULT '0.00',
  `bersih` decimal(10,2) NOT NULL DEFAULT '0.00',
  `stat` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL DEFAULT '0',
  `password` varchar(100) NOT NULL DEFAULT '0',
  `email` varchar(200) NOT NULL DEFAULT '0',
  `ktp` char(50) NOT NULL DEFAULT '0',
  `kode` varchar(50) NOT NULL,
  `referal` varchar(50) NOT NULL,
  `telp` char(30) NOT NULL DEFAULT '0',
  `tingkat` int(11) NOT NULL DEFAULT '0',
  `id_level` int(11) NOT NULL DEFAULT '0',
  `kotor` int(25) NOT NULL DEFAULT '0',
  `bersih` int(25) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// SP
DROP PROCEDURE insert_depcb;

DELIMITER $$

CREATE DEFINER=root@localhost PROCEDURE insert_depcb(
	IN kode_transx VARCHAR(50),
	IN kode_referx VARCHAR(50),
	IN kotor DECIMAL(10,2) )
	COMMENT 'tes insert depo'

	BEGIN
	declare level INT;
	declare counter INT;
	declare j_kotor DECIMAL(10,2);
	declare kd_trx varchar(50);
	declare kd_ref varchar(50);
	declare kd_tmp varchar(50);

	SET level = (SELECT tingkat FROM user WHERE kode = kode_transx);
	SET counter = 1;
	INSERT INTO deposit_cb (`kode_trans`, `kode_ref`, `kotor`, `bersih`, `stat`) VALUES (kode_transx, kode_referx, kotor, kotor/2, '0');
	SET kd_trx=kode_transx;

	myloop: loop
		SET counter= counter+1;
		set kd_tmp = (select kode_ref from deposit_cb where deposit_cb.`stat`='0');
		set j_kotor =(select bersih   from deposit_cb where deposit_cb.`stat`='0');
		SET kd_ref = (select referal  from `user` where user.kode = kd_tmp);
		INSERT INTO deposit_cb (`kode_trans`, `kode_ref`, `kotor`, `bersih`, `stat`) VALUES (kd_tmp, kd_ref, j_kotor, j_kotor/2, '0');
		UPDATE `deposit_cb` SET `stat`=1 where deposit_cb.kode_trans = kd_trx and deposit_cb.`stat`='0';
		set kd_trx = kd_tmp;
		if counter = level then
			leave myloop;
		END if;
	end loop myloop;

END;$$

DELIMITER ;