Sekitar 2 tahun yang lalu aku di kasih temenku phptriad 2x yang didalamnya juga sudah ada apache,php dan mysql 3.X lengkap dengan phpmyadminnya. (makasih ya pren).
Diawal awal aku sempet otak-atik, (program apa ini?), tp akhirnya lama-lama aku mulai terbiasa, dan mulai menggunakannya untuk program2ku, dan sangat mendukung profesiku sebagai programer pocokan (amatiran).
Beberapa waktu lalu aku sempet baca2 di internet yang katanya mulai MySQL 5.X sudah mendukung stored procedure, fungsi, triger dll. (opo meneh kuwi)
Karena selama ini aku sudah terbiasa menggunakan VB6 dan MySQL 3.X yang menurutku sudah bisa tak pake apa aja, aku jadi males untuk otak-atik program itu.
Karena ada rasa sedikit penasaran aku mulai coba instal MySQL 5.X di compku, walhasil aku mulai coba yang namanya “procedure”.
Dengan bantuan MySQL Query Browser aku mulai membuat 1 procedure yang jelas2 sudah pernah aku buat dengan menggunakan VB6.
Setelah procedure sudah selesai aku buat dengan menggunakan MySQL hasil menarik yaitu prosesnya jadi lebih cepet.
Procedure buatan VB6 selama ini kalo aku jalankan dikompku membutuhkan waktu 54 detik (hampir 1 menit) dan setelah aku buat dengan menggunakan MySQL dengan hasil proses yang sama hanya memerlukan waktu 16 detik.
Bila dicek menggunakan task manager hasilnya seperti dibawah ini :
Ada 2 proses (dilihat pada CPU Usage History), yang depan menggunakan MySQL yang berikutnya menggunakan VB6.
Walaupun demikian VB6 masih tetep OK untuk membuat program (karena bisanya ini) walaupun porsinya sudah mulai aku kurangi dan diganti dengan MySQL.
Procedure GL Versi VB6
‘untuk proses gl
If Ckey = “gl” Then
Dim Saldo As Double
Dim Saldo1 As Double
Csql = “delete from gl where periode = ‘” & Cperiode & “‘”
Conn.Execute Csql
Csql = “select accnumber,accname,accclass,acclevel,acctype from account order by accnumber”
Rst.Open Csql, Conn, adOpenKeyset
p = 0
While Not Rst.EOF
p = p + 1
Rst.MoveNext
Wend
fmain.Indikator.Min = 0
fmain.Indikator.Max = p
p = 0
Rst.MoveFirst
While Not Rst.EOF
Csql = “select saldo from saldo where accnumber = ‘” & Rst(0) & “‘ and periode = ‘” & Cperiode & “‘”
Rst1.Open Csql, Conn, adOpenKeyset
If Not Rst1.EOF Then
Saldo = IIf(IsNull(Rst1(0)), 0, Rst1(0))
Else
Saldo = 0
End If
Rst1.Close
If Rst(“acctype”) = “Detail” Then
‘untuk debet
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘D'”
Rst1.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst1(0)), 0, Rst1(0))
Rst1.Close
Csql = “select accclass from account where accnumber = ‘” & Rst(“accnumber”) & “‘”
Rst1.Open Csql, Conn, adOpenKeyset
If Rst1(0) = “AS” Or Rst1(0) = “EX” Or Rst1(0) = “OE” Then
Saldo = Saldo + Saldo1
Else
Saldo = Saldo – Saldo1
End If
Rst1.Close
‘untuk kredit
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘K'”
Rst1.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst1(0)), 0, Rst1(0))
Rst1.Close
Csql = “select accclass from account where accnumber = ‘” & Rst(“accnumber”) & “‘”
Rst1.Open Csql, Conn, adOpenKeyset
If Rst1(0) = “AS” Or Rst1(0) = “EX” Or Rst1(0) = “OE” Then
Saldo = Saldo – Saldo1
Else
Saldo = Saldo + Saldo1
End If
Rst1.Close
Else
Csql = “select accnumber,accname,accclass,acclevel,acctype from account where induk = ‘” + Rst(“accnumber”) + “‘ and accnumber <> ‘” & Rst(“accnumber”) & “‘ order by accnumber”
Rst1.Open Csql, Conn, adOpenKeyset
‘p = 0
While Not Rst1.EOF
Csql = “select saldo from saldo where accnumber = ‘” & Rst1(0) & “‘ and periode = ‘” & Cperiode & “‘”
Rst2.Open Csql, Conn, adOpenKeyset
If Not Rst2.EOF Then
Saldo = Saldo + IIf(IsNull(Rst2(0)), 0, Rst2(0))
End If
Rst2.Close
If Rst1(“acctype”) = “Detail” Then
‘untuk debet
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst1(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘D'”
Rst2.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst2(0)), 0, Rst2(0))
Rst2.Close
Csql = “select accclass from account where accnumber = ‘” & Rst1(“accnumber”) & “‘”
Rst2.Open Csql, Conn, adOpenKeyset
If Rst2(0) = “AS” Or Rst2(0) = “EX” Or Rst2(0) = “OE” Then
Saldo = Saldo + Saldo1
Else
Saldo = Saldo – Saldo1
End If
Rst2.Close
‘untuk kredit
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst1(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘K'”
Rst2.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst2(0)), 0, Rst2(0))
Rst2.Close
Csql = “select accclass from account where accnumber = ‘” & Rst1(“accnumber”) & “‘”
Rst2.Open Csql, Conn, adOpenKeyset
If Rst2(0) = “AS” Or Rst2(0) = “EX” Or Rst2(0) = “OE” Then
Saldo = Saldo – Saldo1
Else
Saldo = Saldo + Saldo1
End If
Rst2.Close
Else
Csql = “select accnumber,accname,accclass,acclevel,acctype from account where induk = ‘” + Rst1(“accnumber”) + “‘ and accnumber <> ‘” & Rst1(“accnumber”) & “‘ order by accnumber”
Rst2.Open Csql, Conn, adOpenKeyset
‘p = 0
While Not Rst2.EOF
Csql = “select saldo from saldo where accnumber = ‘” & Rst2(0) & “‘ and periode = ‘” & Cperiode & “‘”
Rst3.Open Csql, Conn, adOpenKeyset
If Not Rst3.EOF Then
Saldo = Saldo + IIf(IsNull(Rst3(0)), 0, Rst3(0))
End If
Rst3.Close
If Rst2(“acctype”) = “Detail” Then
‘untuk debet
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst2(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘D'”
Rst3.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst3(0)), 0, Rst3(0))
Rst3.Close
Csql = “select accclass from account where accnumber = ‘” & Rst2(“accnumber”) & “‘”
Rst3.Open Csql, Conn, adOpenKeyset
If Rst3(0) = “AS” Or Rst3(0) = “EX” Or Rst3(0) = “OE” Then
Saldo = Saldo + Saldo1
Else
Saldo = Saldo – Saldo1
End If
Rst3.Close
‘untuk kredit
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst2(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘K'”
Rst3.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst3(0)), 0, Rst3(0))
Rst3.Close
Csql = “select accclass from account where accnumber = ‘” & Rst2(“accnumber”) & “‘”
Rst3.Open Csql, Conn, adOpenKeyset
If Rst3(0) = “AS” Or Rst3(0) = “EX” Or Rst3(0) = “OE” Then
Saldo = Saldo – Saldo1
Else
Saldo = Saldo + Saldo1
End If
Rst3.Close
Else ‘—————-
Csql = “select accnumber,accname,accclass,acclevel,acctype from account where induk = ‘” + Rst2(“accnumber”) + “‘ and accnumber <> ‘” & Rst2(“accnumber”) & “‘ order by accnumber”
Rst3.Open Csql, Conn, adOpenKeyset
‘p = 0
While Not Rst3.EOF
Csql = “select saldo from saldo where accnumber = ‘” & Rst3(0) & “‘ and periode = ‘” & Cperiode & “‘”
Rst4.Open Csql, Conn, adOpenKeyset
If Not Rst4.EOF Then
Saldo = Saldo + IIf(IsNull(Rst4(0)), 0, Rst4(0))
End If
Rst4.Close
If Rst3(“acctype”) = “Detail” Then
‘untuk debet
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst3(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘D'”
Rst4.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst4(0)), 0, Rst4(0))
Rst4.Close
Csql = “select accclass from account where accnumber = ‘” & Rst3(“accnumber”) & “‘”
Rst4.Open Csql, Conn, adOpenKeyset
If Rst4(0) = “AS” Or Rst4(0) = “EX” Or Rst4(0) = “OE” Then
Saldo = Saldo + Saldo1
Else
Saldo = Saldo – Saldo1
End If
Rst4.Close
‘untuk kredit
Csql = “select sum(jumlah) from jurnal where accnumber = ‘” + Rst3(“accnumber”) + “‘ and periode = ‘” + Cperiode + “‘and type = ‘K'”
Rst4.Open Csql, Conn, adOpenKeyset
Saldo1 = IIf(IsNull(Rst4(0)), 0, Rst4(0))
Rst4.Close
Csql = “select accclass from account where accnumber = ‘” & Rst3(“accnumber”) & “‘”
Rst4.Open Csql, Conn, adOpenKeyset
If Rst4(0) = “AS” Or Rst4(0) = “EX” Or Rst4(0) = “OE” Then
Saldo = Saldo – Saldo1
Else
Saldo = Saldo + Saldo1
End If
Rst4.Close
Else
‘Saldo = 0
End If
Rst3.MoveNext
Wend
Rst3.Close
End If
Rst2.MoveNext
Wend
Rst2.Close
End If
Rst1.MoveNext
Wend
Rst1.Close
End If
Csql = “insert gl values(‘” & Rst(“accnumber”) & “‘,'” & Rst(“accname”) & “‘,'” & Rst(“accclass”) & “‘,'” & Rst(“acclevel”) & “‘,” & Saldo & “,'” & Cperiode & “‘)”
Conn.Execute Csql
p = p + 1
fmain.Indikator.Value = p
fmain.StatusBar1.Panels(1).text = Int(p / fmain.Indikator.Max * 100) & “% ” & Rst(1)
Rst.MoveNext
Wend
Rst.Close
Csql = “insert gl values(‘2′,”,’LI’,’1′,0,'” & Cperiode & “‘)”
Conn.Execute Csql
Csql = “update gl set jumlah = ” & LabaPB & ” where accnumber = ‘” & CLabaPB & “‘ and periode = ‘” & Cperiode & “‘”
Conn.Execute Csql
Csql = “SELECT sum(jumlah) FROM gl left join account on gl.accnumber = account.accnumber where gl.periode = ‘” & Cperiode & “‘ and (gl.accclass = ‘LI’ or gl.accclass = ‘EQ’) and account.acctype = ‘Detail'”
Rst.Open Csql, Conn, adOpenKeyset
Csql = “insert gl values(‘2-‘,’PASIVA’,’LI’,’1′,” & Rst(0) & “,'” & Cperiode & “‘)”
Conn.Execute Csql
Rst.Close
MsgBox “Proses GL telah selesai, laporan neraca dan laba rugi sudah di update”
End If
Procedure GL versi MySQL
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_GL`(CPeriode char(10))
BEGIN
declare Njumlah double;
declare acc char(10);
declare cname char(30);
declare cclass char(10);
declare clevel int(10);
declare ctype char(10);
declare cinduk char(10);
declare cinduk1 char(10);
declare accinduk char(10);
declare done int(1) default 0;
declare csaldo double;
declare counter int(10) default 0;
declare acc_cursor cursor for select accnumber,accname,accclass,acclevel,acctype,induk from account order by accnumber;
declare acc_cursor1 cursor for select accnumber from account where acctype = ‘header’ and acclevel = 3 order by accnumber;
declare acc_cursor2 cursor for select accnumber from account where acctype = ‘header’ and acclevel = 2 order by accnumber;
declare acc_cursor3 cursor for select accnumber from account where acctype = ‘header’ and acclevel = 1 order by accnumber;
declare continue handler for sqlstate ‘02000’ set done = 1;
delete from gl1 where periode = cperiode;
open acc_cursor;
repeat
fetch acc_cursor into acc,cname,cclass,clevel,ctype,cinduk;
if not done then
select sum(saldo) into njumlah from saldo where accnumber = acc and cr = ‘Rp’ and periode = cperiode;
if njumlah is null then
set njumlah = 0;
end if;
set csaldo = njumlah;
/*untuk jurnal yang debet*/
select sum(jumlah) into njumlah from jurnal where accnumber = acc and cr = ‘Rp’ and periode = cperiode and type = ‘D’;
if njumlah is null then
set njumlah = 0;
end if;
if cclass = “AS” or cclass = “EX” or cclass = “OE” then
set csaldo = csaldo + njumlah;
else
set csaldo = csaldo – njumlah;
end if;
/*untuk jurnal yang kredit*/
select sum(jumlah) into njumlah from jurnal where accnumber = acc and cr = ‘Rp’ and periode = cperiode and type = ‘K’;
if njumlah is null then
set njumlah = 0;
end if;
if cclass = “AS” or cclass = “EX” or cclass = “OE” then
set csaldo = csaldo – njumlah;
else
set csaldo = csaldo + njumlah;
end if;
insert into gl1 values(acc,cname,cclass,clevel,csaldo,cperiode,cinduk);
set counter = counter + 1;
end if;
until done end repeat;
close acc_cursor;
/* untuk induk account level 3 */
set counter = 0;
set done = 0;
open acc_cursor1;
repeat
fetch acc_cursor1 into acc;
if not done then
select sum(jumlah) into njumlah from gl1 where induk = acc and periode = cperiode;
if njumlah is null then
set njumlah = 0;
end if;
/*set csaldo = njumlah;*/
update gl1 set jumlah = njumlah where accnumber = acc;
set counter = counter + 1;
end if;
until done end repeat;
close acc_cursor1;
/* untuk induk account level 2 */
set counter = 0;
set done = 0;
open acc_cursor2;
repeat
fetch acc_cursor2 into acc;
if not done then
select sum(jumlah) into njumlah from gl1 where induk = acc and periode = cperiode;
if njumlah is null then
set njumlah = 0;
end if;
/*set csaldo = njumlah;*/
update gl1 set jumlah = njumlah where accnumber = acc;
set counter = counter + 1;
end if;
until done end repeat;
close acc_cursor2;
/* untuk induk account level 1 */
set counter = 0;
set done = 0;
open acc_cursor3;
repeat
fetch acc_cursor3 into acc;
if not done then
select sum(jumlah) into njumlah from gl1 where induk = acc and periode = cperiode;
if njumlah is null then
set njumlah = 0;
end if;
/*set csaldo = njumlah;*/
update gl1 set jumlah = njumlah where accnumber = acc;
set counter = counter + 1;
end if;
until done end repeat;
close acc_cursor3;
/*account tambahan untuk kebutuhan neraca laba rugi*/
insert gl1 values(‘2′,”,’LI’,’1′,0,cperiode,’-‘);
SELECT sum(jumlah) into njumlah FROM gl1 left join account on gl1.accnumber = account.accnumber where gl1.periode = cperiode and (gl1.accclass = ‘LI’ or gl1.accclass = ‘EQ’) and account.acctype = ‘Detail’;
insert gl1 values(‘2-‘,’PASIVA’,’LI’,’1′,njumlah,cperiode,’-‘);
select * from gl1 where periode = cperiode order by accnumber;
END $$
DELIMITER ;