sBB82XHW

1CREATE SCHEMA IF NOT EXISTS PACILBISA;
2CREATE TABLE IF NOT EXISTS PACILBISA.PENGGUNA (
3    username VARCHAR(20) NOT NULL,
4    password VARCHAR(20) NOT NULL,
5    nama_lengkap VARCHAR(50) NOT NULL,
6    alamat TEXT NOT NULL,
7    PRIMARY KEY(username)
8);
9INSERT INTO PACILBISA.PENGGUNA VALUES
10    ('jogilvy0','TeX3OK','Jenica Ogilvy','36 Ridge Oak Street'),
11    ('iniblo1','1ok3nVU','Ileana Niblo','86110 Loftsgordon Park'),
12    ('bmacgragh2','rKwI2gQnT09','Barton MacGragh','8 Ludington Drive'),
13    ('kshovelin3','rjnj9j9QqQ3e','Kippie Shovelin','0155 Schiller Hill'),
14    ('rblazevic4','YdEcaGUk0vUq','Richart Blazevic','47 Melody Alley'),
15    ('olackemannk','LiLImE20','Oliver Lackemann','96847 Portage Way'),
16    ('cdormonl','AhvztKR67xq','Carmine Dormon','046 North Street'),
17    ('kbaguleym','uXP8qyQ','Kane Baguley','32618 Chinook Road'),
18    ('tsmithiesn','Lbpjh4Ot7q3X','Tabbatha Smithies','4116 Knutson Circle'),
19    ('ahodgetso','MDQZnA0xJZ5','Arvy Hodgets','58 Kropf Alley'),
20    ('lalastairp','9ltf71PxESXe','Leslie Alastair','68 Wayridge Crossing'),
21    ('lmonkemanq','JHWBt8ieNI3','Linnea Monkeman','01739 Clove Avenue'),
22    ('yaultr','lu6d2g','Yorker Ault','13 Old Shore Avenue'),
23    ('jmoultons','xRo0hFs','Janine Moulton','910 Buena Vista Lane'),
24    ('dprynnet','HSEixrFu','Danya Prynne','052 Jenna Center'),
25    ('dnormingtonu','6ye6T4FeS','Denna Normington','93006 Buhler Park'),
26    ('cmccreathv','XHfuPkVX','Chicky McCreath','7869 Sage Hill'),
27    ('sambroisinw','9xUOxDsn','Silvia Ambroisin','88169 Oneill Place'),
28    ('tnickelsx','eMudRvEkV5','Tawnya Nickels','6 Arkansas Crossing'),
29    ('adanielloty','IWCKfe2AFKoy','Alyse Daniellot','8649 David Terrace'),
30    ('lsummerskillz','vFNccmzab','Lorenza Summerskill','62 Fieldstone Hill'),
31    ('erasper10','OKzdm6Mr','Erminie Rasper','44 Spohn Drive'),
32    ('scruden11','PbODVIufQs6h','Shae Cruden','44296 Eastlawn Avenue'),
33    ('lstaddom12','cbVxetyNe','Lianne Staddom','6 South Drive'),
34    ('bgludor13','8vPidM','Bartholomeus Gludor','03 Darwin Way'),
35    ('tbote0','6luS9TVVX3U','Tucker Bote','64 Killdeer Lane'),
36    ('btreuge1','FrrNdp','Beale Treuge','456 5th Pass'),
37    ('amarien2','fDP3ecdK','Arlyne Marien','60965 Northland Road'),
38    ('dlusk3','7uRkqHmXU','Dexter Lusk','0 Springs Place'),
39    ('epitkins4','hz0ydvA','Elisa Pitkins','8118 Springs Park'),
40    ('childrew5','KHkJtUPCEl','Curry Hildrew','5 Surrey Junction'),
41    ('claunder6','wtMubnOUHNJ','Cheryl Launder','01116 Valley Edge Center'),
42    ('iilles7','2KIXw7vrw','Isadora Illes','0 Southridge Alley'),
43    ('esugars8','iPMQXKyVrA8','Essy Sugars','22 Monument Place'),
44    ('taucoate9','ak3Yj1VFAZO','Taddeusz Aucoate','8 Forster Street'),
45    ('lakeeriemed','CgTYCrE7I','Lake Erie Medical DBA Quality Care Products LLC','4287 Melvin Road'),
46    ('cardinalhealth','l4fgPccrXay1','Cardinal Health','20236 Farmco Hill'),
47    ('willspeedllc','AWz0KZ','Willspeed Technologies LLC','6741 Shelley Road'),
48    ('lorealusa','XwbP8RQSXr','L''Oreal USA Products Inc','1 Pleasure Street'),
49    ('selectbrand','UMTkAzcw2XE','Select Brand Distributors','2182 Everett Lane'),
50    ('amorepacific','zsAaufPnh','AMOREPACIFIC','15603 Bartillon Avenue'),
51    ('shopkostores','cHuC7b0i','Shopko Stores Operating Co., LLC','7688 Russell Hill'),
52    ('madcheminc','3BRrYCva9Jky','MedChem Manufacturing Inc. dba Enovachem','4 Steensland Avenue'),
53    ('goldstatesup','BBgYOS6','Golden State Medical Supply, Inc.','3556 Union Crossing'),
54    ('wockhardt_ltd','r2EFjig0','Wockhardt Limited','4283 Michigan Street'),
55    ('neutrogena','8m58pyIr','Neutrogena Corporation','37 Atwood Place'),
56    ('kangstradtrd','FxaaCRsqL','Kingsway Trading','3 Chive Parkway'),
57    ('sanofiinc','eK8Opr','Sanofi Pasteur Inc.','060 Spenser Trail'),
58    ('ncreationinc','jSGqeaaavOd','Natural Creations, Inc.','02 Marcy Way'),
59    ('walgreenco','kGnTb6','WALGREEN COMPANY','8 Jackson Lane');
60CREATE TABLE IF NOT EXISTS PACILBISA.ADMIN (
61    username VARCHAR(20) NOT NULL,
62    PRIMARY KEY (username),
63    FOREIGN KEY (username) REFERENCES PACILBISA.PENGGUNA(username) ON UPDATE CASCADE ON DELETE CASCADE
64);
65INSERT INTO PACILBISA.ADMIN VALUES
66    ('jogilvy0'),
67    ('iniblo1'),
68    ('bmacgragh2'),
69    ('kshovelin3'),
70    ('rblazevic4');
71CREATE TABLE IF NOT EXISTS PACILBISA.DONATUR (
72    username VARCHAR(20) NOT NULL,
73    no_telp VARCHAR(16) NOT NULL,
74    PRIMARY KEY (username),
75    FOREIGN KEY (username) REFERENCES PACILBISA.PENGGUNA(username) ON UPDATE CASCADE ON DELETE CASCADE
76);
77INSERT INTO PACILBISA.DONATUR VALUES
78    ('adanielloty','08214002173'),
79    ('ahodgetso','08206991935'),
80    ('bgludor13','08249648526'),
81    ('cdormonl','08280002402'),
82    ('cmccreathv','08229089789'),
83    ('dnormingtonu','08283407362'),
84    ('dprynnet','08214843943'),
85    ('erasper10','08294357265'),
86    ('jmoultons','08234222572'),
87    ('kbaguleym','08208520904'),
88    ('lalastairp','08299718244'),
89    ('lmonkemanq','08260594916'),
90    ('lstaddom12','08273404256'),
91    ('lsummerskillz','08257887768'),
92    ('olackemannk','08282089139'),
93    ('sambroisinw','08220248688'),
94    ('scruden11','08213758437'),
95    ('tnickelsx','08213072380'),
96    ('tsmithiesn','08203266380'),
97    ('yaultr','08259961336');
98CREATE TABLE IF NOT EXISTS PACILBISA.PENGGALANG_BANTUAN (
99    username VARCHAR(20) NOT NULL,
100    no_telp VARCHAR(16) NOT NULL,
101    PRIMARY KEY (username),
102    FOREIGN KEY (username) REFERENCES PACILBISA.PENGGUNA(username) ON UPDATE CASCADE ON DELETE CASCADE
103);
104INSERT INTO PACILBISA.PENGGALANG_BANTUAN VALUES
105    ('amarien2',08146440025),
106    ('btreuge1',08130748368),
107    ('childrew5',08129208022),
108    ('claunder6',08161935580),
109    ('dlusk3',08182255230),
110    ('epitkins4',08167929849),
111    ('esugars8',08152088172),
112    ('iilles7',08130709924),
113    ('taucoate9',08147654357),
114    ('tbote0',08132218416),
115    ('amorepacific',08124508678),
116    ('cardinalhealth',08160025881),
117    ('goldstatesup',08116040362),
118    ('kangstradtrd',08103846396),
119    ('lakeeriemed',08153010122),
120    ('lorealusa',08101833547),
121    ('madcheminc',08183794188),
122    ('ncreationinc',08118434835),
123    ('neutrogena',08147118392),
124    ('sanofiinc',08146305785),
125    ('selectbrand',08103184928),
126    ('shopkostores',08111905726),
127    ('walgreenco',08156811551),
128    ('willspeedllc',08130550878),
129    ('wockhardt_ltd',08171112313);
130CREATE TABLE IF NOT EXISTS PACILBISA.PENGGALANG_BANTUAN_PERORANGAN (
131    username VARCHAR(20) NOT NULL,
132    pekerjaan VARCHAR(30) NOT NULL,
133    nama_instansi VARCHAR(30) NOT NULL,
134    alamat_instansi TEXT NOT NULL,
135    PRIMARY KEY (username),
136    FOREIGN KEY (username) REFERENCES PACILBISA.PENGGALANG_BANTUAN(username) ON UPDATE CASCADE ON DELETE CASCADE
137);
138INSERT INTO PACILBISA.PENGGALANG_BANTUAN_PERORANGAN VALUES
139    ('amarien2','mechanic','Johns and Fritsch','44147 Corben Court'),
140    ('btreuge1','doctor','JFK Medical Center','702 Graceland Way'),
141    ('childrew5','accountant','Mann-Hodkiewicz','5388 East Terrace'),
142    ('claunder6','doctor','ACMH Hospital','3195 Holmberg Way'),
143    ('dlusk3','optician','Huels-Predovic Optic','4 Kedzie Avenue'),
144    ('epitkins4','veterinary doctor','Spencer Hospital','2441 Pond Parkway'),
145    ('esugars8','nurse','Collier Care','27047 Banding Street'),
146    ('iilles7','pharmacist','Padberg Inc','39 Independence Pass'),
147    ('taucoate9','tailor','Greenholt house','981 Warrior Alley'),
148    ('tbote0','teacher','Carnegie Vanguard High School','138 Veith Place');
149CREATE TABLE IF NOT EXISTS PACILBISA.PENGGALANG_BANTUAN_PERUSAHAAN (
150    username VARCHAR(20) NOT NULL,
151    npwp VARCHAR(16) NOT NULL,
152    PRIMARY KEY (username),
153    FOREIGN KEY (username) REFERENCES PACILBISA.PENGGALANG_BANTUAN(username) ON UPDATE CASCADE ON DELETE CASCADE
154);
155INSERT INTO PACILBISA.PENGGALANG_BANTUAN_PERUSAHAAN VALUES
156    ('amorepacific','188966919'),
157    ('cardinalhealth','5799934970'),
158    ('goldstatesup','4389459848'),
159    ('kangstradtrd','594529964'),
160    ('lakeeriemed','2390749070'),
161    ('lorealusa','9507374604'),
162    ('madcheminc','488709245'),
163    ('ncreationinc','2923820673'),
164    ('neutrogena','3872018868'),
165    ('sanofiinc','9966568301'),
166    ('selectbrand','3231394376'),
167    ('shopkostores','1474827136'),
168    ('walgreenco','4501004347'),
169    ('willspeedllc','1070628344'),
170    ('wockhardt_ltd','8835834546');
171CREATE TABLE IF NOT EXISTS PACILBISA.RECEIPT_TOP_UP (
172    no_receipt VARCHAR(20) NOT NULL,
173    waktu_receipt TIMESTAMP NOT NULL,
174    nominal INT NOT NULL,
175    deskripsi TEXT,
176    PRIMARY KEY (no_receipt)
177);
178INSERT INTO PACILBISA.RECEIPT_TOP_UP VALUES
179    ('101','2020-04-30 00:42:05',201000,'porta'),
180    ('102','2020-05-16 01:48:43',55000,NULL),
181    ('103','2020-06-04 11:59:43',344000,'volutpat erat quisque erat'),
182    ('104','2020-06-05 13:22:29',242000,'dolor morbi vel lectus'),
183    ('105','2020-06-13 03:27:55',107000,NULL),
184    ('106','2020-06-17 20:37:10',230000,'lacinia aenean'),
185    ('107','2020-06-26 10:36:18',74000,'suscipit a'),
186    ('108','2020-08-09 22:04:28',139000,'morbi non'),
187    ('109','2020-08-27 23:51:17',149000,'metus vitae ipsum aliquam'),
188    ('110','2020-10-03 00:16:22',42000,'tortor eu'),
189    ('111','2020-10-07 06:39:26',96000,NULL),
190    ('112','2020-11-04 05:41:39',174000,'non lectus aliquam sit');
191CREATE TABLE IF NOT EXISTS PACILBISA.TRANSAKSI_TOP_UP (
192    donatur VARCHAR(20) NOT NULL,
193    waktu TIMESTAMP NOT NULL,
194    no_virtual_acc VARCHAR(16) NOT NULL,
195    nominal INT,
196    no_receipt VARCHAR(20) NOT NULL,
197    PRIMARY KEY (donatur, waktu),
198    FOREIGN KEY (donatur) REFERENCES PACILBISA.DONATUR(username) ON UPDATE CASCADE ON DELETE CASCADE,
199    FOREIGN KEY (no_receipt) REFERENCES PACILBISA.RECEIPT_TOP_UP(no_receipt) ON UPDATE CASCADE ON DELETE CASCADE
200);
201INSERT INTO PACILBISA.TRANSAKSI_TOP_UP VALUES
202    ('jmoultons','2020-04-30 00:42:05','01208234222572',201000,'101'),
203    ('bgludor13','2020-05-16 01:48:43','0108249648526',55000,'102'),
204    ('erasper10','2020-06-04 11:59:43','0108294357265',344000,'103'),
205    ('lmonkemanq','2020-06-05 13:22:29','0108260594916',242000,'104'),
206    ('lstaddom12','2020-06-13 03:27:55','0108273404256',107000,'105'),
207    ('lstaddom12','2020-06-17 20:37:10','0108273404256',230000,'106'),
208    ('dnormingtonu','2020-06-26 10:36:18','0208283407362',74000,'107'),
209    ('lsummerskillz','2020-08-09 22:04:28','0208257887768',139000,'108'),
210    ('erasper10','2020-08-27 23:51:17','0208294357265',149000,'109'),
211    ('adanielloty','2020-10-03 00:16:22','0208214002173',42000,'110'),
212    ('cdormonl','2020-10-07 06:39:26','0208280002402',96000,'111'),
213    ('scruden11','2020-11-04 05:41:39','0208213758437',174000,'112');
214CREATE TABLE IF NOT EXISTS PACILBISA.CAMPAIGN (
215    kode VARCHAR(8) NOT NULL,
216    nama_bank VARCHAR(10) NOT NULL,
217    nama_pemilik VARCHAR(40) NOT NULL,
218    no_rek VARCHAR(20) NOT NULL,
219    kategori VARCHAR(20) NOT NULL,
220    judul VARCHAR(30) NOT NULL,
221    jenis_donasi VARCHAR(20) NOT NULL,
222    target_donasi VARCHAR(20),
223    batas_waktu TIMESTAMP NOT NULL,
224    tujuan VARCHAR(30) NOT NULL,
225    penerima VARCHAR(20) NOT NULL,
226    deskripsi TEXT,
227    penggalang_bantuan VARCHAR(20) NOT NULL,
228    verifikator VARCHAR(20) NOT NULL,
229    status VARCHAR(20) NOT NULL,
230    PRIMARY KEY (kode),
231    FOREIGN KEY (penggalang_bantuan) REFERENCES PACILBISA.PENGGALANG_BANTUAN(username) ON UPDATE CASCADE ON DELETE CASCADE,
232    FOREIGN KEY (verifikator) REFERENCES PACILBISA.ADMIN(username) ON UPDATE CASCADE ON DELETE CASCADE
233);
234INSERT INTO PACILBISA.CAMPAIGN VALUES
235    ('pb-010','mandiri','Golden State Medical Supply, Inc.','432503251286','infrastruktur umum','memberi sembako masal','uang','40000000','2019-02-05 02:46:05','memberi sembako masal','Jammal Defond','eleifend pede libero quis orci','goldstatesup','rblazevic4','terverifikasi'),
236    ('pb-020','mandiri','Curry Hildrew','505856569304','infrastruktur umum','bantu operasi pasien','uang','130000000','2019-06-09 14:40:50','bantu operasi pasien','Burlie Frome','enim in tempor turpis nec euismod scelerisque','childrew5','jogilvy0','terverifikasi'),
237    ('pb-030','bank mega','Kingsway Trading','614662021217','rumah ibadah','bantu pasien sakit','uang-barang','20000000','2019-08-12 07:48:00','bantu pasien sakit','Doroteya Shepperd','tincidunt lacus at velit vivamus vel','kangstradtrd','bmacgragh2','terverifikasi'),
238    ('pb-040','bukopin','Cheryl Launder','916745517386','rumah ibadah','bantu korban bencana alam','uang-barang','10000000','2019-09-04 14:10:53','bantu korban bencana alam','Violette McAmish','vestibulum ac est lacinia nisi venenatis tristique fusce','claunder6','iniblo1','terverifikasi'),
239    ('pb-050','mandiri','Taddeusz Aucoate','178719487977','bencana alam','memberi sembako masal','uang-barang','110000000','2019-09-04 20:46:01','memberi sembako masal','Norene Ruggen','feugiat et eros vestibulum ac est lacinia nisi venenatis tristique','taucoate9','iniblo1','terverifikasi'),
240    ('pb-060','bri','Beale Treuge','647722760727','kegiatan sosial','darurat covid','uang-barang','70000000','2019-09-06 08:45:27','darurat covid','Dulci Rooze','nisl duis ac nibh fusce lacus purus','btreuge1','iniblo1','terverifikasi'),
241    ('pb-070','bni','Cheryl Launder','87956530109','bencana alam','bantu pasien sakit','uang-barang','90000000','2019-09-07 17:41:06','bantu pasien sakit','Ahmad McKelvey','pede ac diam cras pellentesque volutpat dui maecenas tristique est','claunder6','jogilvy0','terverifikasi'),
242    ('pb-080','btpn','Essy Sugars','918410404886','pendidikan','bantu korban bencana alam','uang-barang','150000000','2019-10-08 07:47:57','bantu korban bencana alam','Sybille Marielle','risus praesent lectus vestibulum quam sapien varius ut','esugars8','kshovelin3','terverifikasi'),
243    ('pb-090','btpn','Golden State Medical Supply, Inc.','731379073264','infrastruktur umum','darurat covid','uang-barang','140000000','2019-10-24 21:17:24','darurat covid','Allegra Isakowicz','amet diam in magna bibendum imperdiet nullam orci pede venenatis','goldstatesup','rblazevic4','terverifikasi'),
244    ('pb-100','btpn','Select Brand Distributors','100012191614','kegiatan sosial','bantu yatim dan dhuafa','uang-barang','100000000','2019-12-28 22:49:05','bantu yatim dan dhuafa','Cherish Larcher','lectus pellentesque at nulla suspendisse potenti cras','selectbrand','rblazevic4','terverifikasi'),
245    ('pb-110','nobu','Shopko Stores Operating Co., LLC','753480493997','rumah ibadah','memberi sembako masal','uang-barang','180000000','2020-01-30 21:35:56','memberi sembako masal','Nelli Kubicka','suspendisse accumsan tortor quis turpis sed ante','shopkostores','kshovelin3','terverifikasi'),
246    ('pb-120','nobu','L''Oreal USA Products Inc','944885318706','pendidikan','bantu yatim dan dhuafa','uang-barang','170000000','2020-03-01 06:14:29','bantu yatim dan dhuafa','Sande Veryan','mauris non ligula pellentesque ultrices phasellus id sapien in sapien','lorealusa','jogilvy0','menunggu verifikasi'),
247    ('pb-130','bank mega','Cardinal Health','937969053902','kegiatan sosial','bantu korban gempa','uang-barang','120000000','2020-04-04 16:22:42','bantu korban gempa','Neila Foulis','eget tempus vel pede morbi porttitor lorem id ligula suspendisse','cardinalhealth','bmacgragh2','menunggu verifikasi'),
248    ('pb-140','nobu','Elisa Pitkins','448926715346','kegiatan sosial','bantu korban gempa','barang',NULL,'2020-05-31 01:55:31','bantu korban gempa','Valerye Iannuzzi','lacus purus aliquet at feugiat non pretium quis','epitkins4','kshovelin3','terverifikasi'),
249    ('pb-150','mandiri','Wockhardt Limited','696387337755','kegiatan sosial','bantu korban gempa','barang',NULL,'2020-11-19 09:30:58','bantu korban gempa','Dotti Large','vel pede morbi porttitor lorem id','wockhardt_ltd','kshovelin3','terverifikasi');
250CREATE TABLE IF NOT EXISTS PACILBISA.PEMBAYARAN (
251    id_payment VARCHAR(10) NOT NULL,
252    nama_bank VARCHAR(20) NOT NULL,
253    metode_bayar VARCHAR(20) NOT NULL,
254    no_rek VARCHAR(20),
255    PRIMARY KEY (id_payment)
256);
257INSERT INTO PACILBISA.PEMBAYARAN VALUES
258    ('pmt-1','mandiri','transfer bank','6922228820'),
259    ('pmt-2','nobu','transfer bank','1516368649'),
260    ('pmt-3','bni','virtual acc','0108234222572'),
261    ('pmt-4','bni','virtual acc','0208283407362'),
262    ('pmt-5','dompet faedah','dompet faedah',NULL),
263    ('pmt-6','dompet faedah','dompet faedah',NULL),
264    ('pmt-7','dompet faedah','dompet faedah',NULL),
265    ('pmt-8','btpn','transfer bank','2439742902'),
266    ('pmt-9','bni','virtual acc','0208213758437'),
267    ('pmt-10','bri','virtual acc','0108260594916');
268CREATE TABLE IF NOT EXISTS PACILBISA.EKSPEDISI (
269    kode_ekspedisi VARCHAR(10) NOT NULL,
270    email VARCHAR(30) NOT NULL,
271    nama_ekspedisi VARCHAR(20) NOT NULL,
272    alamat TEXT NOT NULL,
273    no_telp VARCHAR(20) NOT NULL,
274    no_ktp_pemilih VARCHAR(20) NOT NULL,
275    no_npwp_pemilih VARCHAR(20),
276    nama_pemilik VARCHAR(20) NOT NULL,
277    PRIMARY KEY (kode_ekspedisi)
278);
279INSERT INTO PACILBISA.EKSPEDISI VALUES
280    ('exp-11','kgourdon0@dhl.com','DHL','3279 Tennessee Way',08348673842,'98-422-9114',4276663547,'Kally Gourdon'),
281    ('exp-12','gwheaton1@gosend.com','GOSEND','46583 Hollow Ridge Junction',08383520538,'47-749-9111',9447510019,'Gwennie Wheaton'),
282    ('exp-13','abridgwater2@tiki.co.id','TIKI','6 La Follette Junction',08302165683,'70-761-5171',7371485211,'Allan Bridgwater'),
283    ('exp-14','lfields3@jne.co.id','JNE','17 Claremont Place',08347982740,'50-524-5635',8184436049,'Leila Fields'),
284    ('exp-15','bpuvia4@sicepat.co.id','SICEPAT','66048 Hauk Parkway',08334352705,'11-836-4591',5310479449,'Bernard Puvia');
285CREATE TABLE IF NOT EXISTS PACILBISA.PENGIRIMAN (
286    no_resi VARCHAR(20),
287    kode_ekspedisi VARCHAR(10) NOT NULL,
288    alamat_asal VARCHAR(30) NOT NULL,
289    waktu TIMESTAMP NOT NULL,
290    status VARCHAR(20) NOT NULL,
291    PRIMARY KEY (no_resi),
292    FOREIGN KEY (kode_ekspedisi) REFERENCES PACILBISA.EKSPEDISI(kode_ekspedisi)  ON UPDATE CASCADE ON DELETE CASCADE
293);
294INSERT INTO PACILBISA.PENGIRIMAN VALUES
295    (354293125,'exp-15','87 Shoshone Terrace','2020-12-07 18:35:42','berhasil'),
296    (1924068524,'exp-12','8 Birchwood Crossing','2020-12-19 01:19:09','berhasil'),
297    (708189784,'exp-13','6212 Acker Junction','2021-02-27 02:35:10','berhasil'),
298    (9651839031,'exp-14','30104 Milwaukee Alley','2021-04-06 03:46:17','sedang diproses'),
299    (2761904168,'exp-15','55 Kropf Street','2021-04-07 03:40:17','sedang diverifikasi');
300CREATE TABLE IF NOT EXISTS PACILBISA.RECEIPT_DONASI (
301    no_receipt VARCHAR(20) NOT NULL,
302    waktu_receipt TIMESTAMP,
303    nominal INT,
304    deskripsi TEXT,
305    PRIMARY KEY (no_receipt)
306);
307INSERT INTO PACILBISA.RECEIPT_DONASI VALUES
308    ('rd-1','2019-09-07 17:41:06',450000,'nulla elit'),
309    ('rd-2','2019-10-08 07:47:57',100000,'nisi venenatis tristique fusce congue'),
310    ('rd-3','2019-10-24 21:17:24',500000,'posuere'),
311    ('rd-4','2019-12-28 22:49:05',400000,'morbi sem mauris laoreet ut'),
312    ('rd-5','2020-01-30 21:35:56',3000000,'vestibulum ante ipsum'),
313    ('rd-6','2020-03-01 06:14:29',2000000,'at turpis'),
314    ('rd-7','2020-04-04 16:22:42',300000,'mauris'),
315    ('rd-8','2020-05-31 01:55:31',600000,'interdum'),
316    ('rd-11','2020-12-07 18:35:42',15000,'nunc rhoncus dui'),
317    ('rd-12','2020-12-19 01:19:09',40000,'tellus nisi eu'),
318    ('rd-13','2021-02-27 02:35:10',30000,'faucibus orci luctus');
319CREATE TABLE IF NOT EXISTS PACILBISA.DONASI (
320    id_donasi VARCHAR(10) NOT NULL,
321    campaign VARCHAR(8) NOT NULL,
322    donatur VARCHAR(20) NOT NULL,
323    pesan TEXT NOT NULL,
324    status VARCHAR(20) NOT NULL,
325    waktu TIMESTAMP NOT NULL,
326    no_receipt VARCHAR(20),
327    PRIMARY KEY (id_donasi),
328    FOREIGN KEY (campaign) REFERENCES PACILBISA.CAMPAIGN (kode)  ON UPDATE CASCADE ON DELETE CASCADE,
329    FOREIGN KEY (donatur) REFERENCES PACILBISA.DONATUR (username)  ON UPDATE CASCADE ON DELETE CASCADE,
330    FOREIGN KEY (no_receipt) REFERENCES PACILBISA.RECEIPT_DONASI (no_receipt)  ON UPDATE CASCADE ON DELETE CASCADE
331);
332INSERT INTO PACILBISA.DONASI VALUES
333    ('pbd-1','pb-010','lstaddom12','interdum mauris non ligula pellentesque ultrices','berhasil','2019-09-07 17:41:06','rd-1'),
334    ('pbd-2','pb-020','kbaguleym','interdum in ante vestibulum','berhasil','2019-10-08 07:47:57','rd-2'),
335    ('pbd-3','pb-030','jmoultons','ut nulla sed accumsan felis','berhasil','2019-10-24 21:17:24','rd-3'),
336    ('pbd-4','pb-040','dnormingtonu','eleifend quam a odio in hac habitasse','berhasil','2019-12-28 22:49:05','rd-4'),
337    ('pbd-5','pb-050','lmonkemanq','primis in faucibus orci luctus et ultrices','berhasil','2020-01-30 21:35:56','rd-5'),
338    ('pbd-6','pb-060','tnickelsx','nulla justo aliquam quis turpis','berhasil','2020-03-01 06:14:29','rd-6'),
339    ('pbd-7','pb-070','erasper10','et ultrices posuere cubilia curae duis faucibus','berhasil','2020-04-04 16:22:42','rd-7'),
340    ('pbd-8','pb-080','lalastairp','pretium nisl ut volutpat sapien','berhasil','2020-05-31 01:55:31','rd-8'),
341    ('pbd-9','pb-090','scruden11','dapibus nulla suscipit ligula in','sedang diverifikasi','2020-11-19 09:30:58',NULL),
342    ('pbd-10','pb-100','lmonkemanq','gravida nisi at nibh in hac habitasse platea','sedang diverifikasi','2020-11-26 07:43:42',NULL),
343    ('pbd-11','pb-110','lmonkemanq','maecenas tincidunt lacus at velit','berhasil','2020-12-07 18:35:42','rd-11'),
344    ('pbd-12','pb-140','adanielloty','amet sem fusce consequat nulla nisl nunc','berhasil','2020-12-19 01:19:09','rd-12'),
345    ('pbd-13','pb-150','bgludor13','non mi integer ac neque duis bibendum','berhasil','2021-02-27 02:35:10','rd-13'),
346    ('pbd-14','pb-140','bgludor13','ridiculus mus vivamus vestibulum sagittis sapien','sedang diproses','2021-04-06 03:46:17',NULL),
347    ('pbd-15','pb-150','sambroisinw','dis parturient montes nascetur ridiculus ','sedang diverifikasi','2021-04-07 03:40:17',NULL);
348CREATE TABLE IF NOT EXISTS PACILBISA.DONASI_UANG (
349    id_donasi VARCHAR(10) NOT NULL,
350    nominal INT NOT NULL,
351    id_payment VARCHAR(10) NOT NULL,
352    PRIMARY KEY (id_donasi),
353    FOREIGN KEY (id_donasi) REFERENCES PACILBISA.DONASI(id_donasi) ON UPDATE CASCADE ON DELETE CASCADE,
354    FOREIGN KEY (id_payment) REFERENCES PACILBISA.PEMBAYARAN(id_payment)  ON UPDATE CASCADE ON DELETE CASCADE
355);
356INSERT INTO PACILBISA.DONASI_UANG VALUES
357    ('pbd-1',450000,'pmt-1'),
358    ('pbd-2',100000,'pmt-2'),
359    ('pbd-3',500000,'pmt-3'),
360    ('pbd-4',400000,'pmt-4'),
361    ('pbd-5',3000000,'pmt-5'),
362    ('pbd-6',2000000,'pmt-6'),
363    ('pbd-7',300000,'pmt-7'),
364    ('pbd-8',600000,'pmt-8'),
365    ('pbd-9',200000,'pmt-9'),
366    ('pbd-10',150000,'pmt-10');
367CREATE TABLE IF NOT EXISTS PACILBISA.DONASI_BARANG (
368    id_donasi VARCHAR(10) NOT NULL,
369    no_resi VARCHAR(20) NOT NULL,
370    PRIMARY KEY (id_donasi),
371    FOREIGN KEY (id_donasi) REFERENCES PACILBISA.DONASI(id_donasi) ON UPDATE CASCADE ON DELETE CASCADE,
372    FOREIGN KEY (no_resi) REFERENCES PACILBISA.PENGIRIMAN(no_resi) ON UPDATE CASCADE ON DELETE CASCADE
373);
374INSERT INTO PACILBISA.DONASI_BARANG VALUES
375    ('pbd-11','354293125'),
376    ('pbd-12','1924068524'),
377    ('pbd-13','708189784'),
378    ('pbd-14','9651839031'),
379    ('pbd-15','2761904168');
380CREATE TABLE IF NOT EXISTS PACILBISA.BARANG_DONASI (
381    id_donasi VARCHAR(10) NOT NULL,
382    no_urut INT NOT NULL,
383    jenis VARCHAR(20) NOT NULL,
384    berat FLOAT NOT NULL,
385    PRIMARY KEY (id_donasi, no_urut),
386    FOREIGN KEY (id_donasi) REFERENCES PACILBISA.DONASI_BARANG (id_donasi) ON UPDATE CASCADE ON DELETE CASCADE
387);
388INSERT INTO PACILBISA.BARANG_DONASI VALUES
389    ('pbd-11',1,'alat makan',1.7),
390    ('pbd-11',2,'furnitur',3.6),
391    ('pbd-11',3,'alat elektronik',3.0),
392    ('pbd-12',1,'pakaian',3.2),
393    ('pbd-12',2,'furnitur',8.0),
394    ('pbd-13',1,'pakaian',2.2),
395    ('pbd-13',2,'alat elektronik',5.0),
396    ('pbd-14',1,'furnitur',3.0),
397    ('pbd-15',1,'perhiasan',0.5);
398CREATE TABLE IF NOT EXISTS PACILBISA.LAPORAN (
399    campaign VARCHAR(8) NOT NULL,
400    pembuat VARCHAR(20) NOT NULL,
401    dana_terpakai INT,
402    dana_terkumpul INT,
403    judul VARCHAR(30) NOT NULL,
404    deskripsi TEXT,
405    tanggal DATE,
406    PRIMARY KEY (campaign, tanggal),
407    FOREIGN KEY (campaign) REFERENCES PACILBISA.CAMPAIGN(kode) ON UPDATE CASCADE ON DELETE CASCADE,
408    FOREIGN KEY (pembuat) REFERENCES PACILBISA.PENGGALANG_BANTUAN(username) ON UPDATE CASCADE ON DELETE CASCADE
409);
410INSERT INTO PACILBISA.LAPORAN VALUES
411    ('pb-010','goldstatesup',13000000,39000000,'memberi sembako masal','donasi uang','2019-02-06'),
412    ('pb-020','childrew5',50000000,100000000,'bantu operasi pasien','donasi uang','2019-06-10'),
413    ('pb-030','kangstradtrd',5250000,21000000,'bantu pasien sakit','donasi uang','2019-08-13'),
414    ('pb-040','claunder6',1375000,11000000,'bantu korban bencana alam','donasi uang','2019-09-05'),
415    ('pb-050','taucoate9',13812500,110500000,'memberi sembako masal','donasi uang','2019-09-05'),
416    ('pb-060','btreuge1',38500000,77000000,'darurat covid','donasi uang','2019-09-07'),
417    ('pb-070','claunder6',22750000,91000000,'bantu pasien sakit','donasi uang','2019-09-08'),
418    ('pb-080','esugars8',18875000,151000000,'bantu korban bencana alam','donasi uang','2019-10-09'),
419    ('pb-090','goldstatesup',12500000,100000000,'darurat covid','donasi uang','2019-10-25'),
420    ('pb-100','selectbrand',50500000,101000000,'bantu yatim dan dhuafa','donasi uang','2019-12-29'),
421    ('pb-110','shopkostores',22500000,90000000,'memberi sembako masal','donasi uang','2020-01-31'),
422    ('pb-140','epitkins4',NULL,NULL,'bantu korban gempa','donasi barang','2020-06-01'),
423    ('pb-150','wockhardt_ltd',NULL,NULL,'bantu korban gempa','donasi barang','2020-11-20');
424CREATE TABLE IF NOT EXISTS PACILBISA.RECEIPT_PENCAIRAN_DANA (
425    no_receipt VARCHAR(20) NOT NULL,
426    waktu_receipt TIMESTAMP NOT NULL,
427    nominal INT NOT NULL,
428    deskripsi TEXT,
429    PRIMARY KEY (no_receipt)
430);
431INSERT INTO PACILBISA.RECEIPT_PENCAIRAN_DANA VALUES
432    ('rpd-1','2019-02-06 02:46:05',13000000,'pencairan dana berhasil'),
433    ('rpd-2','2019-06-10 14:40:50',50000000,'pencairan dana berhasil'),
434    ('rpd-3','2019-08-13 07:48:00',5250000,'pencairan dana berhasil'),
435    ('rpd-4','2019-09-05 14:10:53',1375000,'pencairan dana berhasil'),
436    ('rpd-5','2019-09-05 20:46:01',13812500,'pencairan dana berhasil'),
437    ('rpd-6','2019-09-07 08:45:27',38500000,'pencairan dana berhasil'),
438    ('rpd-7','2019-09-08 17:41:06',22750000,'pencairan dana berhasil'),
439    ('rpd-8','2019-10-09 07:47:57',18875000,'pencairan dana berhasil'),
440    ('rpd-9','2019-10-25 21:17:24',12500000,'pencairan dana berhasil'),
441    ('rpd-10','2019-12-29 22:49:05',50500000,'pencairan dana berhasil'),
442    ('rpd-11','2020-01-31 21:35:56',22500000,'pencairan dana berhasil');
443CREATE TABLE IF NOT EXISTS PACILBISA.PENCAIRAN_DANA (
444    penggalang VARCHAR(20) NOT NULL,
445    campaign VARCHAR(8) NOT NULL,
446    tanggal DATE,
447    nominal INT NOT NULL,
448    verifikator VARCHAR(20) NOT NULL,
449    status VARCHAR(20) NOT NULL,
450    no_receipt VARCHAR(20),
451    PRIMARY KEY (penggalang, campaign, tanggal),
452    FOREIGN KEY (penggalang) REFERENCES PACILBISA.PENGGALANG_BANTUAN(username) ON UPDATE CASCADE ON DELETE CASCADE,
453    FOREIGN KEY (campaign) REFERENCES PACILBISA.CAMPAIGN(kode) ON UPDATE CASCADE ON DELETE CASCADE,
454    FOREIGN KEY (verifikator) REFERENCES PACILBISA.ADMIN(username) ON UPDATE CASCADE ON DELETE CASCADE,
455    FOREIGN KEY (no_receipt) REFERENCES PACILBISA.RECEIPT_PENCAIRAN_DANA(no_receipt) ON UPDATE CASCADE ON DELETE CASCADE
456);
457INSERT INTO PACILBISA.PENCAIRAN_DANA VALUES
458    ('goldstatesup','pb-010','2019-02-06',13000000,'jogilvy0','berhasil','rpd-1'),
459    ('childrew5','pb-020','2019-06-10',50000000,'iniblo1','berhasil','rpd-2'),
460    ('kangstradtrd','pb-030','2019-08-13',5250000,'bmacgragh2','berhasil','rpd-3'),
461    ('claunder6','pb-040','2019-09-05',1375000,'kshovelin3','berhasil','rpd-4'),
462    ('taucoate9','pb-050','2019-09-05',13812500,'rblazevic4','berhasil','rpd-5'),
463    ('btreuge1','pb-060','2019-09-07',38500000,'jogilvy0','berhasil','rpd-6'),
464    ('claunder6','pb-070','2019-09-08',22750000,'jogilvy0','berhasil','rpd-7'),
465    ('esugars8','pb-080','2019-10-09',18875000,'jogilvy0','berhasil','rpd-8'),
466    ('goldstatesup','pb-090','2019-10-25',12500000,'iniblo1','berhasil','rpd-9'),
467    ('selectbrand','pb-100','2019-12-29',50500000,'kshovelin3','berhasil','rpd-10'),
468    ('shopkostores','pb-110','2020-01-31',22500000,'kshovelin3','berhasil','rpd-11'),
469    ('amarien2','pb-010','2021-04-14',10000000,'kshovelin3','sedang diproses',NULL),
470    ('shopkostores','pb-020','2021-04-15',20000000,'iniblo1','sedang diproses',NULL),
471    ('neutrogena','pb-030','2021-04-15',2500000,'rblazevic4','sedang diproses',NULL);