Procedure | Manusia Biasa

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 ;

Manusia Biasa

Tag Archives: Procedure

sp_gl

VB6 VS MySQL 5.X

Pages

Categories

Recent Posts

Archives

Follow Blog via Email