134
DAFTAR PUSTAKA
Adi Nugroho, Menjadi Administrator Basis Data Oracle 10g, 2008. Budi Rahardjo, Imam Heryanto, Pemograman PL/SQL ORACLE, 2003 Jogiyanto Hartono, MBA, Ph.D., Pengenalan Komputer, 1999. Kenneth E. Kendall, Julie E. Kendall, Analisis dan Perancangan Sistem Edisi Kelima Jilid 2, 2003. Munawar, Pemodelan Visual dengan UML, 2005. Martin Fowler, UML Distilled Edisi 3, 2005. Wahana Komputer, Panduan Belajar MySQL Database Server, 2010. http://blackdjie.blogspot.com/2009/01/unified-modeling-language-uml.html. http://madinask.com/index.php?option=com_content&view=article&id=1585:pen genalan-perpustakaan&catid=10:opini&Itemid=135. http://zaifirda.blogspot.com http://ilmukomputer.org/wp-content/uploads/2010/11/Paper-7-Migrating-Structurdesign-to-UML.pdf http://www.evanetics.com/Articles/ar_objectModeling/saneUML.html
Tugas Akhir Andri Kurniawan 41807120004
L-1
LISTING PROGRAM
Button Save Form Loan/Return Transaction:
from MST_LOAN_RULES where
DECLARE loanlimit Number(10); loanperiode Number(10); maxloan number(10); judulsatu Varchar2(300); juduldoea Varchar2(300); memberid Varchar2(20); itemcode Varchar2(20); BEGIN --Validasi jika member id tidak kosong If :MEMBER.MEMBER_ID is not null then --Validasi jika status member pending If :MEMBER.IS_PENDING = 1 then msg_alert('Status Member Id '||:MEMBER.MEMBER_ID||' masih pending!','E',True); End if; --Validasi jika status member sudah expire If :MEMBER.EXPIRE_DATE < sysdate then
loan_rules_id = :MEMBER.MST_LOAN_RULES_I D; exception when no_data_found then loanlimit := 0; loanperiode := 0; msg_alert('Loan rules untuk member Id '||:MEMBER.MEMBER_ID||' tidak ditemukan di database!','E',True); End; End if; --Validasi aturan peminjaman if loanlimit = 0 then msg_alert('Member Id '||:MEMBER.MEMBER_ID||' tidak diperkenankan meminjam!','E',True); else for i in 1..loanlimit loop GO_BLOCK('LOAN'); go_record(i); judulsatu
:=
:LOAN.TITLE; msg_alert('Status Member Id '||:MEMBER.MEMBER_ID||' sudah kadaluarsa (expire)!','E',True); End if; Begin select LOAN_LIMIT, LOAN_PERIODE into loanlimit, loanperiode
maxloan := 0; First_Record; loop juduldoea := :LOAN.TITLE; maxloan := maxloan + 1;
L-2
-Validasi jika judul buku yang sedang dipinjam sama if judulsatu = juduldoea and i <> maxloan then msg_alert('Judul buku pada baris '||i||' dan '||maxloan||' sama. Hapus salah satu baris!','E',True); end if; Begin SELECT distinct(ITEM_ITEM_CODE), MEMBER_MEMBER_ID
!=
msg_alert('Kode buku '||:LOAN.ITEM_ITEM_CODE||' telah dipesan (reserve) oleh member lain. Silahkan ganti dengan kode buku lain','E',True); Elsif itemcode = :LOAN.ITEM_ITEM_CODE and memberid = :MEMBER.MEMBER_ID then update RESERVE SET IS_RESERVED = 0
into itemcode, memberid
WHERE IS_RESERVED = 1
FROM RESERVE WHERE ITEM_ITEM_CODE :ITEM_ITEM_CODE
memberid :MEMBER.MEMBER_ID then
and ITEM_ITEM_CODE = :LOAN.ITEM_ITEM_CODE =
and MEMBER_MEMBER_ID :MEMBER.MEMBER_ID;
AND IS_RESERVED = 1 AND ROWNUM = 1;
Validasi jumlah peminjaman
Exception
= End if; -maksimal if
maxloan > loanlimit then When no_data_found then memberid := null; itemcode := null; End; -Validasi jika kode buku yang dipinjam telah di reserve oleh member lain If itemcode = :LOAN.ITEM_ITEM_CODE and
msg_alert('Jumlah buku yang dapat dipinjam maksimal adalah '||loanlimit||' buah. Hapus baris data agar sesuai jumlah batas peminjaman!','E',True); else if :LOAN.ITEM_ITEM_CODE != null then :LOAN.IS_LENT := 1;
L-3
into :LOAN.IS_RETURN := 0;
jumlahhari from dual;
end if;
when 'TRUE';
end if; exit :System.Last_Record =
End; Begin select fine_each_day into dendaperhari
next_record; end loop; exit when :System.Last_Record = 'TRUE'; end loop; end if; Begin COMMIT; Msg_alert('Data berhasil disimpan.','I',false); Exception When others then Msg_alert('Error: '||substr(sqlerrm,1,100),'E',True); End; END;
Button Return Form Loan/Return Transaction:
DECLARE duedate date; dendaperhari number(10); jumlahdenda number(10); jumlahhari number(10); BEGIN If :LOAN.LOAN_ID is not null And :LOAN.ITEM_ITEM_CODE is not null Then Begin select trunc(sysdate) trunc(:LOAN.DUE_DATE)
from mst_loan_rules where loan_rules_id = :MEMBER.MST_LOAN_RULES_I D; exception when no_data_found then dendaperhari := 0; End; jumlahdenda := jumlahhari * dendaperhari; If Trunc(:LOAN.DUE_DATE) < Trunc(sysdate) Then begin insert into fines(FINES_DATE, MEMBER_MEMBER_ID, LOAN_LOAN_ID, ITEM_ITEM_CODE, DEBET, DESCRIPTION ) values (sysdate, :MEMBER.MEMBER_ID, :LOAN.LOAN_ID, :LOAN.ITEM_ITEM_CODE , jumlahdenda, 'Overdue fines for item '||:LOAN.ITEM_ITEM_CODE); End; msg_alert ('Item Code
L-4
'||:LOAN.ITEM_ITEM_CODE||' telah terlambat '||jumlahhari||' hari. Denda sebesar '|| jumlahdenda||' telah dimasukkan ke database.','I', false); End If; :LOAN.IS_LENT := 0; :LOAN.IS_RETURN := 1; :LOAN.RETURN_DATE := sysdate; COMMIT; msg_alert('Kode buku '||:LOAN.ITEM_ITEM_CODE||' berhasil dikembalikan.','I',false); CLEAR_RECORD; Else msg_alert ('Data peminjaman tidak ditemukan.','E', True); End If; END;
Msg_alert('Item Code '||:LOAN.ITEM_ITEM_CODE||' sudah pernah diperpanjang (extend)!','E',true); End If; Begin select LOAN_PERIODE, fine_each_day, ENABLE_REBORROW, REBORROW_LIMIT into periode, dendaperhari, EnableReborrow, ReborrowLimit from mst_loan_rules where loan_rules_id = :MEMBER.MST_LOAN_RULES_I D; Exception when no_data_found then dendaperhari := 0; EnableReborrow := Null;
Button Extend Form Loan/Return Transaction:
DECLARE periodenumber(10); dendaperhari number(10); jumlahdenda number(10); jumlahhari number(10); EnableReborrow Integer; ReborrowLimit number(10); MaxExtend Number(10); BEGIN If :LOAN.LOAN_ID is not null And :LOAN.ITEM_ITEM_CODE is not null Then If :LOAN.EXTENDED = 1 Then
ReborrowLimit := Null; End; If EnableReborrow = 0 Then Msg_alert('Member Id '||:MEMBER.MEMBER_ID||' tidak boleh memperpanjang (extend) pinjaman!','E',true); End If; Begin SELECT COUNT(LOAN_ID) INTO MaxExtend FROM LOAN
L-5
WHERE is_lent = 1 AND is_return = 0 AND EXTENDED = 1 AND MEMBER_MEMBER_ID = :MEMBER.MEMBER_ID GROUP BY MEMBER_MEMBER_ID; Exception When no_data_found then MaxExtend := 0; End; If MaxExtend > ReborrowLimit Then Msg_alert('Jumlah maksimal buku yang dapat di perpanjang (extend) adalah '||ReborrowLimit,'E',true); End If; Begin select trunc(sysdate) trunc(:LOAN.DUE_DATE) into jumlahhari from dual; End; jumlahdenda := jumlahhari * dendaperhari; --Jika ada keterlambatan pengembalian If Trunc(:LOAN.DUE_DATE) < Trunc(sysdate) Then begin insert into fines(FINES_DATE, MEMBER_MEMBER_ID, LOAN_LOAN_ID, ITEM_ITEM_CODE, DEBET, DESCRIPTION )
values (sysdate, :MEMBER.MEMBER_ID, :LOAN.LOAN_ID, :LOAN.ITEM_ITEM_CODE , jumlahdenda, 'Overdue fines for item '||:LOAN.ITEM_ITEM_CODE); End; msg_alert ('Item Code '||:LOAN.ITEM_ITEM_CODE||' telah terlambat '||jumlahhari||' hari. Denda sebesar '|| jumlahdenda||' telah dimasukkan ke database.','I', false); End If; :LOAN.IS_LENT := 1; :LOAN.IS_RETURN := 0; :LOAN.EXTENDED := 1; :LOAN.DUE_DATE := :LOAN.DUE_DATE + periode; COMMIT; msg_alert('Kode buku '||:LOAN.ITEM_ITEM_CODE||' berhasil diperpanjang (extend).','I',false); Else msg_alert ('Data peminjaman tidak ditemukan.','E', True); End If; END;
Button Return Form Quick Return:
DECLARE loanid number(10);
L-6
duedate date; memberid number(10); loanrulesid number(10); dendaperhari number(10); jumlahdenda number(10); jumlahhari number(10); BEGIN If :LOAN.ITEM_ITEM_CODE is not null Then Begin SELECT L.LOAN_ID, L.DUE_DATE, M.MEMBER_ID, M.MST_LOAN_RULES_ID INTO loanid, duedate, memberid, loanrulesid FROM LOAN L, MEMBER M WHERE L.MEMBER_MEMBER_ID = M.MEMBER_ID AND L.is_lent = 1 AND L.is_return = 0 AND L.item_item_code = :item_item_code AND ROWNUM = 1; Exception When no_data_found then go_item('item_item_code'); msg_alert('Kode buku yang dimasukkan salah, atau buku tersebut sudah dikembalikan!','E',True); End; Begin select trunc(sysdate) - trunc(duedate) into jumlahhari from dual; End;
Begin select fine_each_day into dendaperhari from mst_loan_rules where loan_rules_id = loanrulesid; exception when no_data_found then dendaperhari := 0; End; jumlahdenda := jumlahhari * dendaperhari; If Trunc(duedate) < Trunc(sysdate) Then begin insert into fines(FINES_DATE, MEMBER_MEMBER_ID, LOAN_LOAN_ID, ITEM_ITEM_CODE, DEBET, DESCRIPTION ) values (sysdate, memberid, loanid, :LOAN.ITEM_ITEM_CODE , jumlahdenda, 'Overdue fines for item '||:LOAN.ITEM_ITEM_CODE); End; msg_alert ('Kode buku '||:LOAN.ITEM_ITEM_CODE||' telah terlambat '||jumlahhari||' hari. Denda sebesar '|| jumlahdenda||' telah dimasukkan ke database.','I', false); End If; Begin
L-7
update loan set is_lent = 0, is_return = 1, return_date = sysdate where item_item_code = :item_item_code and loan_id = loanid; End; Begin COMMIT; msg_alert('Kode buku '||:LOAN.ITEM_ITEM_CODE||' berhasil dikembalikan.','I',false); go_item('item_item_code'); exception when others
--Validasi jika member id tidak kosong If :MEMBER.MEMBER_ID is not null then --Validasi jika status member pending If :MEMBER.IS_PENDING = 1 then msg_alert('Status Member Id '||:MEMBER.MEMBER_ID||' masih pending!','E',True); End if; --Validasi jika status member sudah expire If :MEMBER.EXPIRE_DATE < sysdate then
then msg_alert('Error '||substr(Sqlerrm,1,100),'E',True); End; go_item('item_item_code'); End If; END;
Button Save Form Reservation:
DECLARE reservelimit Number(10); loanperiode Number(10); maxreserve number(10); judulsatu Varchar2(300); juduldoea Varchar2(300); memberidloan Varchar2(20); itemcodeloan Varchar2(20); memberid Varchar2(20); itemcode Varchar2(20); BEGIN
:
msg_alert('Status Member Id '||:MEMBER.MEMBER_ID||' sudah kadaluarsa (expire)!','E',True); End if; Begin select RESERVE_LIMIT, LOAN_PERIODE into reservelimit, loanperiode from MST_LOAN_RULES where loan_rules_id = :MEMBER.MST_LOAN_RULES_I D; exception when no_data_found then reservelimit := 0; loanperiode := 0; msg_alert('Loan rules untuk member Id
L-8
'||:MEMBER.MEMBER_ID||' tidak ditemukan di database!','E',True); End; End if; --Validasi aturan peminjaman IF :RESERVE.ITEM_ITEM_CODE is not null Then if reservelimit = 0 then
baris '||i||' dan '||maxreserve||' sama. Hapus salah satu baris!','E',True);
msg_alert('Member Id '||:MEMBER.MEMBER_ID||' tidak diperkenankan memesan (reserve) buku!','E',True); else for i in 1..reservelimit loop
INTO memberidloan
end if; BEGIN SELECT DISTINCT(ITEM_ITEM_CODE), MEMBER_MEMBER_ID itemcodeloan,
FROM LOAN WHERE ITEM_ITEM_CODE :ITEM_ITEM_CODE
=
AND MEMBER_MEMBER_ID :MEMBER.MEMBER_ID
=
GO_BLOCK('RESERVE'); go_record(i);
judulsatu :RESERVE.TITLE;
:=
AND IS_LENT = 1 AND ROWNUM = 1;
maxreserve := 0; EXCEPTION First_Record; loop
WHEN NO_DATA_FOUND THEN
juduldoea :RESERVE.TITLE;
:=
maxreserve := maxreserve +
itemcodeloan := NULL; memberidloan := NULL;
1; END; --Validasi jika judul buku yang sedang dipinjam sama if judulsatu = juduldoea and i <> maxreserve then msg_alert('Judul buku pada
If itemcodeloan = :RESERVE.ITEM_ITEM_CODE and memberidloan = :MEMBER.MEMBER_ID then msg_alert('Kode buku '||:RESERVE.ITEM_ITEM_CODE||'
L-9
tidak dapat dipesan (reserve) oleh Member ID '||memberidloan||' karena sedang dipinjam oleh Member ID tersebut!','E',True);
telah dipesan (reserve) oleh member lain. Silahkan ganti dengan kode buku lain','E',True); End if;
End if; --Validasi jumlah maksimal peminjaman
Begin SELECT distinct(ITEM_ITEM_CODE), MEMBER_MEMBER_ID
if maxreserve > reservelimit then msg_alert('Jumlah buku yang dapat dipesan (reserve) maksimal adalah '||reservelimit||' buah. Hapus baris data agar sesuai jumlah batas pesan (reserve)!','E',True);
into itemcode, memberid FROM RESERVE WHERE ITEM_ITEM_CODE :ITEM_ITEM_CODE
=
AND IS_RESERVED = 1
end if; exit when :System.Last_Record = 'TRUE';
AND ROWNUM = 1;
next_record; end
Exception When no_data_found then
loop; when 'TRUE';
exit :System.Last_Record =
memberid := null; itemcode := null;
end loop; end if; Begin COMMIT;
End; --Validasi jika kode buku yang dipinjam telah di reserve oleh member lain If itemcode = :RESERVE.ITEM_ITEM_CODE and memberid != :MEMBER.MEMBER_ID then msg_alert('Kode buku '||:RESERVE.ITEM_ITEM_CODE||'
Msg_alert('Data berhasil disimpan.','I',false); Exception When others then Msg_alert('Error: '||substr(sqlerrm,1,100),'E',True); End; END IF; END;
L-10
Button Save Form Fines:
Begin COMMIT; Msg_alert('Data berhasil disimpan.','I',false); Exception When others then Msg_alert('Error: '||substr(sqlerrm,1,100),'E',True); End;
Button Save Form Biblio:
Begin COMMIT; Msg_alert('Data berhasil disimpan.','I',false); Exception When others then Msg_alert('Error: '||substr(sqlerrm,1,100),'E',True); End;
(SELECT COUNT(i.ITEM_CODE) FROM ITEM i WHERE i.BIBLIO_BIB_ID = b.BIBLIO_ID) AS COPIES, b.ISBN_ISSN, b.CALL_NUMBER FROM BIBLIO b WHERE b.TITLE LIKE :PTITLE ORDER BY b.BIBLIO_ID
Query Utama Report Books Item List:
SELECT ITEM_CODE, TITLE, COLL_TYPE, STATUS FROM (SELECT i.ITEM_CODE, b.TITLE, (SELECT c.COLL_TYPE_NAME FROM MST_COLL_TYPE c WHERE c.COLL_TYPE_ID = i.MST_COLLTYPE_COL_ID) AS COLL_TYPE, (CASE WHEN (SELECT s.NO_LOAN
Button Save Form Item: FROM MST_ITEM_STATUS s
Begin COMMIT; Msg_alert('Data berhasil disimpan.','I',false); Exception When others then Msg_alert('Error: '||substr(sqlerrm,1,100),'E',True); End;
WHERE s.ITEM_STATUS_ID = i.MST_ITEMSTATUS_STAT_ID) = 1 THEN 'No Loan' ELSE DECODE((SELECT l.IS_LENT
Query Utama Report Title List:
SELECT b.BIBLIO_ID, b.TITLE,
FROM ITEM ic, LOAN l
L-11
WHERE ic.ITEM_CODE = l.ITEM_ITEM_CODE AND l.ITEM_ITEM_CODE = i.ITEM_CODE AND l.IS_LENT = 1 AND ROWNUM = 1),1,'On Loan', NULL, 'Available') END) AS STATUS FROM ITEM i, BIBLIO b WHERE i.BIBLIO_BIB_ID = b.BIBLIO_ID) WHERE ITEM_CODE LIKE :PITEMCODE AND TITLE LIKE :PTITLE AND COLL_TYPE LIKE :PCOLLTYPE AND STATUS LIKE :PSTATUS
Query Utama Report Loan History:
SELECT LH.MEMBER_ID, LH.MEMBER_NAME, LH.ITEM_CODE, LH.TITLE, LH.LOAN_DATE, LH.DUE_DATE, LH.RETURN_DATE, LH.LENT_STATUS, LH.IS_OVERDUE FROM (SELECT m.member_id, m.member_name, i.item_code, b.title, l.loan_date,
l.due_date, l.return_date, (DECODE (is_lent, 1, 'On Loan', 0, 'Returned')) AS lent_status, (CASE WHEN TRUNC(due_date) < TRUNC(NVL (return_date, SYSDATE)) THEN 'Overdued' ELSE 'On Time' END ) AS is_overdue FROM BIBLIO b, ITEM i, MEMBER m, LOAN l WHERE b.biblio_id = i.biblio_bib_id AND i.item_code = l.item_item_code AND l.member_member_id = m.member_id) LH WHERE MEMBER_ID LIKE :PMEMBERID AND MEMBER_NAME LIKE :PMEMBERNAME AND ITEM_CODE LIKE :PITEMCODE AND TITLE LIKE :PTITLE AND LENT_STATUS LIKE :PLENTSTATUS AND IS_OVERDUE LIKE :PISOVERDUE ORDER BY LH.member_id, LH.item_code, LH.loan_date;
