Trigger dan Stored Procedure pada Sistem MLM

(bug fixed: implementasi level diperbaiki)
Sekali lagi, tugas dari pembuat program aplikasi dapat diringankan tugasnya oleh pembuat database. Contoh kasus kali ini adalah MLM (multi level marketing). Tentu kita tidak asing dengan MLM ini, karena konsepnya sangat sederhana, yaitu apabila merekrut anggota baru, maka yang bersangkutan mendapatkan bonus tertentu, kemudian atasannya beberapa level juga mendapatkan bonus.

Aturan MLM kita kali ini adalah:
1. Seorang peserta maksimal memiliki 3 orang anggota.
2. Apabila peserta mendapatkan seorang anggota (menjadi sponsor), maka akan mendapatkan bonus Rp 100.000 .
3. Anggota baru akan diletakkan di bawah sponsornya apabila masih memenuhi kuota (lihat no 1). Akan tetapi, apabila tidak dapat, maka akan diletakkan di bawah anggota pertama dari sponsor. Akan tetapi, apabila tidak dapat, maka akan diletakkan di bawah anggota kedua dari sponsor. Akan tetapi, apabila tidak dapat, maka akan diletakkan di bawah anggota ketiga dari sponsor. Dan seterusnya hingga level ke-4. Apabila sampai yang terakhir tetap masih tidak ada kuota, maka diletakkan sebagai anggota independen (tanpa atasan). Sehingga, antara sponsor dan atasan dapat berbeda.
4. Atasan dari anggota baru dan seterusnya ke atas akan mendapatkan bonus sebanyak Rp 20.000.

Simpel kan? Tapi prakteknya bikin puyeng. Here we goes...

Catatan:
Seharusnya user berisikan field gabungan dari tabel user dan anggota_user, tetapi untuk kepentingan trigger (trigger tidak dapat melakukan update/insert/delete terhadap tabelnya sendiri) tabel user dipecah menjadi dua tabel, yaitu tabel user dan tabel anggota_user. Memang, pada dunia pemrograman kadang kita dituntut untuk mengakali keterbatasan bahasa pemrograman dengan kreatifitas masing-masing, kadang tidak ideal memang.

Pencarian kuota yang kosong menggunakan algoritma kunjungan level order pada linked list.

struktur tabel:

CREATE TABLE `anggota_user` (
`kd_user` int(10) unsigned NOT NULL,
`atasan` int(10) unsigned DEFAULT NULL,
`anggota1` int(10) unsigned DEFAULT NULL,
`anggota2` int(10) unsigned DEFAULT NULL,
`anggota3` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`kd_user`),
KEY `id1` (`anggota1`),
KEY `id2` (`anggota2`),
KEY `id3` (`anggota3`),
KEY `id4` (`atasan`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `anggota_user` DISABLE KEYS */;
INSERT INTO `anggota_user` (`kd_user`,`atasan`,`anggota1`,`anggota2`,`anggota3`,`anggota4`,`anggota5`) VALUES
(1,NULL,0,0,0,0,0);
/*!40000 ALTER TABLE `anggota_user` ENABLE KEYS */;

CREATE TABLE `jenis_transaksi` (
`kd_jenis_transaksi` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`jenis_transaksi` varchar(45) NOT NULL,
`nominal` int(11) unsigned NOT NULL,
`is_parent_dapat` tinyint(1) unsigned NOT NULL,
PRIMARY KEY (`kd_jenis_transaksi`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `jenis_transaksi` DISABLE KEYS */;
INSERT INTO `jenis_transaksi` (`kd_jenis_transaksi`,`jenis_transaksi`,`nominal`,`is_parent_dapat`) VALUES
(1,'Rekrut Anggota Baru',100000,0),
(2,'Bonus Rekrut Anggota Baru',20000,1);
/*!40000 ALTER TABLE `jenis_transaksi` ENABLE KEYS */;

CREATE TABLE `transaksi` (
`kd_transaksi` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kd_jenis_transaksi` tinyint(3) unsigned NOT NULL,
`kd_user` int(10) unsigned NOT NULL,
`nominal` int(10) unsigned NOT NULL,
`waktu` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`kd_transaksi`),
KEY `FK1` (`kd_jenis_transaksi`),
KEY `FK2` (`kd_user`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `user` (
`kd_user` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`nama_user` varchar(30) NOT NULL,
`alamat` varchar(50) NOT NULL,
`sponsor` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`kd_user`),
UNIQUE KEY `username` (`username`),
KEY `id1` (`sponsor`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` (`kd_user`,`username`,`password`,`nama_user`,`alamat`,`sponsor`) VALUES
(1,'root','','ROOT,'',0);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

CREATE TABLE `virtual_memory` (
`kd_virtual_memory` int(10) unsigned NOT NULL,
`no_antrian` int(10) unsigned NOT NULL,
`nilai` int(10) unsigned NOT NULL,`nilai_2` int(10) unsigned NOT NULL,
PRIMARY KEY (`kd_virtual_memory`,`no_antrian`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

view:

CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_anggota_kosong` AS select sql_cache `user`.`kd_user` AS
`kd_user`,if(isnull(`anggota_user`.`anggota1`), 1, if(isnull(`anggota_user`.`anggota2`) , 2 , if(isnull(`anggota_user`.`anggota3`), 3 ,0))) AS `anggota_kosong` from (`user` join `anggota_user` on((`anggota_user`.`kd_user` = `user`.`kd_user`))) where (`user`.`kd_user` > 1);

CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_jumlah_anggota` AS select sql_cache `user`.`kd_user` AS
`kd_user`,if(isnull(`anggota_user`.`anggota1`), 3 , if(isnull(`anggota_user`.`anggota2`) , 2 , if(isnull(`anggota_user`.`anggota3`), 1 ,0))) AS `jumlah_anggota` from (`user` join `anggota_user` on((`anggota_user`.`kd_user` = `user`.`kd_user`))) where (`user`.`kd_user` > 1);

procedure dan function:

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE FUNCTION `sisip_anggota`(kd_anggota_ INT, kd_sponsor_ INT) RETURNS int(11)
BEGIN
declare level_ TINYINT;
declare id_memory_ BIGINT;
declare kd_anggota_kosong_ INT;
declare no_anggota_kosong_ TINYINT;
declare no_antrian_ TINYINT;
declare n_memory_ INT;
declare top_memory_ INT;
declare kd_anak_ INT;

#berikan bonus pada sponsor
insert into transaksi (kd_jenis_transaksi, kd_user, nominal) values (1, kd_sponsor_, (select sql_cache nominal from jenis_transaksi where kd_jenis_transaksi=1));

if (select sql_cache jumlah_anggota from v_jumlah_anggota where kd_user=kd_sponsor_) > 0 then #apakah kuota sponsor masih cukup?
begin #masih ada kuota, atasan = sponsor
set kd_anggota_kosong_ := kd_sponsor_;
set no_anggota_kosong_ := (select anggota_kosong from v_anggota_kosong where kd_user=kd_anggota_kosong_);
end;
else #kuota sponsor habis
begin
set level_ := 0;
set id_memory_ := ceil(rand() * 3000000000); #buat id untuk memory
set n_memory_ := 1;
set no_antrian_ := 1;
#mulai level order
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_sponsor_, level_); #masukkan sponsor ke memory
sisip: while n_memory_ > 0 do
#dapatkan elemen pertama dari memory
set top_memory_ := (select nilai from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1);
set level_ := (select nilai_2 from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1);

set kd_anak_ := (select sql_cache anggota1 from anggota_user where kd_user=top_memory_); #dapatkan anggota pertama dari peserta
if (select sql_cache jumlah_anggota from v_jumlah_anggota where kd_user=kd_anak_) > 0 then #apakah masih ada kuota pada anggota pertama
begin #ada kuota pada anggota pertama
set kd_anggota_kosong_ := kd_anak_;
set no_anggota_kosong_ := (select anggota_kosong from v_anggota_kosong where kd_user=kd_anggota_kosong_);
LEAVE sisip;
end;
elseif level_ <= 3 and not isnull(kd_anak_) then #tidak ada kuota dan tidak melampaui level
begin
set n_memory_ := n_memory_ + 1;
set no_antrian_ := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai) values (id_memory_, n_memory_, kd_anak_); #daftarkan ke memory
end;
end if;

set kd_anak_ := (select sql_cache anggota2 from anggota_user where kd_user=top_memory_); #dapatkan anggota kedua dari peserta
if (select sql_cache jumlah_anggota from v_jumlah_anggota where kd_user=kd_anak_) > 0 then #apakah masih ada kuota pada anggota kedua
begin #ada kuota pada anggota kedua
set kd_anggota_kosong_ := kd_anak_;
set no_anggota_kosong_ := (select anggota_kosong from v_anggota_kosong where kd_user=kd_anggota_kosong_);
LEAVE sisip;
end;
elseif level_ <= 3 and not isnull(kd_anak_) then #tidak ada kuota dan tidak melampaui level
begin
set n_memory_ := n_memory_ + 1;
set no_antrian_ := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai) values (id_memory_, n_memory_, kd_anak_); #daftarkan ke memory
end;
end if;
set kd_anak_ := (select sql_cache anggota3 from anggota_user where kd_user=top_memory_); #dapatkan anggota ketiga dari peserta
if (select sql_cache jumlah_anggota from v_jumlah_anggota where kd_user=kd_anak_) > 0 then #apakah masih ada kuota pada anggota ketiga
begin #ada kuota pada anggota ketiga
set kd_anggota_kosong_ := kd_anak_;
set no_anggota_kosong_ := (select anggota_kosong from v_anggota_kosong where kd_user=kd_anggota_kosong_);
LEAVE sisip;
end;
elseif level_ <= 3 and not isnull(kd_anak_) then #tidak ada kuota dan tidak melampaui level
begin
set n_memory_ := n_memory_ + 1;
set no_antrian_ := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai) values (id_memory_, n_memory_, kd_anak_); #daftarkan ke memory
end;
end if;
set top_memory_ := (select no_antrian from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1);
delete from virtual_memory where kd_virtual_memory=id_memory_ and no_antrian=top_memory_; #hapus elemen pertama pada memory
set n_memory_ := n_memory_ - 1;
end while;
#hapus semua elemen pada memory
delete from virtual_memory where kd_virtual_memory=id_memory_;
end;
end if;

call simpan_anggota(kd_anggota_kosong_, kd_anggota_, no_anggota_kosong_); return kd_anggota_kosong_;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `bonus_atasan`(kd_anggota_ int)
BEGIN
declare level_ int;
declare kd_atasan_ int;

set kd_atasan_ := kd_anggota_;
set level_ := 1;
bonusatasan: while level_ <= 4 and kd_atasan_ > 1 do
set kd_atasan_ := (select sql_cache atasan from anggota_user where kd_user=kd_atasan_);
if not isnull(kd_atasan_) and kd_atasan_ > 1 then
insert into transaksi (kd_jenis_transaksi, kd_user, nominal) values (2, kd_atasan_, (select sql_cache nominal from jenis_transaksi where kd_jenis_transaksi=2));
else
leave bonusatasan;
end if;
set level_ := level_ + 1;
end while;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dapatkan_anggotanya_anggota`(IN kd_anggota INT, OUT a1 INT, OUT a2 INT, OUT a3 INT, OUT a4 INT, OUT a5 INT)
BEGIN
set a1 := (select sql_cache anggota1 from user where kd_user=kd_anggota);
set a2 := (select sql_cache anggota2 from user where kd_user=kd_anggota);
set a3 := (select sql_cache anggota3 from user where kd_user=kd_anggota);
set a4 := (select sql_cache anggota4 from user where kd_user=kd_anggota);
set a5 := (select sql_cache anggota5 from user where kd_user=kd_anggota);
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `simpan_anggota`(IN kd_induk_ INT, IN kd_anggota_ INT, IN no_anggota_ TINYINT)
BEGIN
case no_anggota_
when 1 then update anggota_user set anggota1=kd_anggota_ where kd_user=kd_induk_;
when 2 then update anggota_user set anggota2=kd_anggota_ where kd_user=kd_induk_;
when 3 then update anggota_user set anggota3=kd_anggota_ where kd_user=kd_induk_;
when 4 then update anggota_user set anggota4=kd_anggota_ where kd_user=kd_induk_;
else update anggota_user set anggota5=kd_anggota_ where kd_user=kd_induk_;
end case;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
CREATE DEFINER=`root`@`localhost` FUNCTION `hitung_anggota`(kd_anggota_ INT) RETURNS int(11)
BEGIN
declare level_             TINYINT;
declare id_memory_         BIGINT;
declare no_antrian_        INT;
declare n_memory_          INT;
declare top_memory_        INT;
declare kd_anak_           INT;

set level_       := 0;
set id_memory_   := ceil(rand() * 3000000000);
set n_memory_    := 1;
set no_antrian_  := 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anggota_, level_);
hitung: while n_memory_ > 0 do
set top_memory_ := (select nilai from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1);
set level_      := (select nilai_2 from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1) + 1;

set kd_anak_ := (select sql_cache anggota1 from anggota_user where kd_user=top_memory_);
if level_ <= 10 and not isnull(kd_anak_) then
begin
set n_memory_    := n_memory_ + 1;
set no_antrian_  := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anak_, level_);
end;
end if;

set kd_anak_ := (select sql_cache anggota2 from anggota_user where kd_user=top_memory_);
if level_ <= 10 and not isnull(kd_anak_) then
begin
set n_memory_    := n_memory_ + 1;
set no_antrian_  := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anak_, level_);
end;
end if;

set kd_anak_ := (select sql_cache anggota3 from anggota_user where kd_user=top_memory_);
if level_ <= 10 and not isnull(kd_anak_) then
begin
set n_memory_    := n_memory_ + 1;
set no_antrian_  := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anak_, level_);
end;
end if;

set kd_anak_ := (select sql_cache anggota4 from anggota_user where kd_user=top_memory_);
if level_ <= 10 and not isnull(kd_anak_) then
begin
set n_memory_    := n_memory_ + 1;
set no_antrian_  := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anak_, level_);
end;
end if;

set kd_anak_ := (select sql_cache anggota5 from anggota_user where kd_user=top_memory_);
if level_ <= 10 and not isnull(kd_anak_) then
begin
set n_memory_    := n_memory_ + 1;
set no_antrian_  := no_antrian_ + 1;
insert into virtual_memory (kd_virtual_memory, no_antrian, nilai, nilai_2) values (id_memory_, no_antrian_, kd_anak_, level_);
end;
end if;

set top_memory_ := (select no_antrian from virtual_memory where kd_virtual_memory=id_memory_ order by no_antrian asc limit 0, 1);
delete from virtual_memory where kd_virtual_memory=id_memory_ and no_antrian=top_memory_;
set n_memory_ := n_memory_ - 1;
end while;

delete from virtual_memory where kd_virtual_memory=id_memory_;
return no_antrian_ - 1;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

DELIMITER ;

trigger:

DELIMITER $$

CREATE DEFINER = `root`@`localhost` TRIGGER `sisip_user` AFTER INSERT ON `user` FOR EACH ROW
begin
declare atasan_ INT;

#dapatkan atasan dan berikan bonus ke sponsor
set atasan_ := sisip_anggota(NEW.kd_user, NEW.sponsor);
#daftarkan ke anggota_user
insert into anggota_user(kd_user, atasan) values (NEW.kd_user, atasan_);
#berikan bonus ke atasan beberapa level ke atas
call bonus_atasan(NEW.kd_user);
end $

CREATE DEFINER = `root`@`localhost` TRIGGER `hapus_user` AFTER DELETE ON `user` FOR EACH ROW
begin
delete from anggota_user where kd_user=OLD.kd_user;
end $$

DELIMITER ;

Thanx To: Moslem & Pulsator