· 8 years ago · Jun 06, 2018, 11:22 AM
1DROP DATABASE IF EXISTS `db_bidan`;
2CREATE DATABASE `db_bidan` /*!40100 DEFAULT CHARACTER SET latin1 */;
3USE `db_bidan`;
4
5
6DROP TABLE IF EXISTS `tbl_anc`;
7CREATE TABLE `tbl_anc` (
8 `id_periksa` varchar(50) NOT NULL,
9 `id_anc` int(11) NOT NULL AUTO_INCREMENT,
10 `nm_suami` varchar(100) NOT NULL,
11 `tinggi_bdn` smallint(6) NOT NULL,
12 `berat_bdn` smallint(6) NOT NULL,
13 `hpht` date NOT NULL,
14 `htp` date NOT NULL,
15 `diagnosa` varchar(11) NOT NULL,
16 `umur_khmln` int(11) NOT NULL,
17 `kb_terakhir` date NOT NULL,
18 PRIMARY KEY (`id_anc`)
19) ENGINE=InnoDB DEFAULT CHARSET=latin1;
20
21
22DROP TABLE IF EXISTS `tbl_asuhan_bayi`;
23CREATE TABLE `tbl_asuhan_bayi` (
24 `id_asuhan` int(11) NOT NULL AUTO_INCREMENT,
25 `nm_asuhan` text NOT NULL,
26 PRIMARY KEY (`id_asuhan`)
27) ENGINE=InnoDB DEFAULT CHARSET=latin1;
28
29INSERT INTO `tbl_asuhan_bayi` (`id_asuhan`, `nm_asuhan`) VALUES
30(1, 'Inisasi Menyusu Dini (IMD) dalam 1 jam pertama'),
31(2, 'Suntikan Vitamin K1'),
32(3, 'Salep mata antibiotika profilaksis'),
33(4, 'Imunisasi HBO');
34
35DROP TABLE IF EXISTS `tbl_bayi_lahir`;
36CREATE TABLE `tbl_bayi_lahir` (
37 `id_bayi` int(11) NOT NULL AUTO_INCREMENT,
38 `id_kelahiran` varchar(50) NOT NULL,
39 `nm_bayi` varchar(100) NOT NULL,
40 `anak_ke` int(11) NOT NULL,
41 `berat_lahir` int(11) NOT NULL,
42 `panjang_badan` int(11) NOT NULL,
43 `lingkar_kepala` int(11) NOT NULL,
44 `jk` varchar(30) NOT NULL,
45 `asuhan_bayi_saat_lahir` text NOT NULL,
46 `keterangan` text NOT NULL,
47 `kondisi_bayi` text NOT NULL,
48 PRIMARY KEY (`id_bayi`)
49) ENGINE=InnoDB DEFAULT CHARSET=latin1;
50
51
52DROP TABLE IF EXISTS `tbl_jenis_obat`;
53CREATE TABLE `tbl_jenis_obat` (
54 `id_jobat` int(11) NOT NULL AUTO_INCREMENT,
55 `nm_jobat` varchar(50) NOT NULL,
56 PRIMARY KEY (`id_jobat`)
57) ENGINE=InnoDB DEFAULT CHARSET=latin1;
58
59INSERT INTO `tbl_jenis_obat` (`id_jobat`, `nm_jobat`) VALUES
60(1, 'KB'),
61(2, 'Ringan'),
62(3, 'Keras'),
63(4, 'Suplemen');
64
65DROP TABLE IF EXISTS `tbl_kelahiran`;
66CREATE TABLE `tbl_kelahiran` (
67 `no_pasien` int(11) NOT NULL,
68 `id_kelahiran` varchar(50) NOT NULL,
69 `tgl_kelahiran` datetime NOT NULL,
70 `nm_suami` varchar(100) NOT NULL,
71 `penolongan_persalinan` text NOT NULL,
72 `cara_persalinan` text NOT NULL,
73 `keadaan_ibu` text NOT NULL,
74 `umur_kehamilan` int(11) NOT NULL,
75 `biaya_persalinan` int(11) NOT NULL,
76 `biaya_perawatan_kelas` int(11) NOT NULL,
77 `biaya_perawatan_bayi` int(11) NOT NULL,
78 `biaya_obat_obatan` int(11) NOT NULL,
79 `biaya_cucian` int(11) NOT NULL,
80 `biaya_akte_kelahiran` int(11) NOT NULL,
81 `biaya_transportasi` int(11) NOT NULL,
82 `biaya_lain` int(11) NOT NULL,
83 PRIMARY KEY (`id_kelahiran`)
84) ENGINE=InnoDB DEFAULT CHARSET=latin1;
85
86
87DROP TABLE IF EXISTS `tbl_kondisi_lahir`;
88CREATE TABLE `tbl_kondisi_lahir` (
89 `id_kondisi` int(11) NOT NULL AUTO_INCREMENT,
90 `nm_kondisi` text NOT NULL,
91 PRIMARY KEY (`id_kondisi`)
92) ENGINE=InnoDB DEFAULT CHARSET=latin1;
93
94INSERT INTO `tbl_kondisi_lahir` (`id_kondisi`, `nm_kondisi`) VALUES
95(1, 'Segera menangis'),
96(2, 'Menangis beberapa saat'),
97(3, 'Tidak menangis'),
98(4, 'Seluruh tubuh kemerahan'),
99(5, 'Anggota gerak kebiruan'),
100(6, 'Seluruh tubuh biru'),
101(7, 'Kelainan bawaan'),
102(8, 'Meninggal');
103
104DROP TABLE IF EXISTS `tbl_obat`;
105CREATE TABLE `tbl_obat` (
106 `id_obat` int(11) NOT NULL AUTO_INCREMENT,
107 `nm_obat` varchar(50) NOT NULL,
108 `stok` int(11) NOT NULL,
109 `status` enum('Aktif','Tidak Aktif') NOT NULL DEFAULT 'Aktif',
110 `id_jobat` int(11) NOT NULL,
111 `hrg_obat` int(11) NOT NULL,
112 PRIMARY KEY (`id_obat`)
113) ENGINE=InnoDB DEFAULT CHARSET=latin1;
114
115INSERT INTO `tbl_obat` (`id_obat`, `nm_obat`, `stok`, `status`, `id_jobat`, `hrg_obat`) VALUES
116(1, 'Kabe', 12, 'Aktif', 1, 10000),
117(2, 'Ringan', 2, 'Aktif', 2, 1800),
118(8, 'Suntik', 9, 'Aktif', 1, 9000),
119(9, 'Parasetamol', 10, 'Aktif', 2, 1100),
120(10, 'STM', 10, 'Aktif', 2, 1000),
121(11, 'Promag', 30, 'Aktif', 2, 2000),
122(12, 'Amoxilin', 40, 'Aktif', 2, 3000),
123(13, 'Vitamin C', 12, 'Aktif', 2, 12000);
124
125DROP TABLE IF EXISTS `tbl_pasien`;
126CREATE TABLE `tbl_pasien` (
127 `no_pasien` int(11) NOT NULL AUTO_INCREMENT,
128 `nm_pasien` varchar(100) NOT NULL,
129 `tgl_lahir` date NOT NULL,
130 `jk` enum('Laki-laki','Perempuan') NOT NULL,
131 `pekerjaan` varchar(100) NOT NULL,
132 `alamat` varchar(250) NOT NULL,
133 PRIMARY KEY (`no_pasien`)
134) ENGINE=InnoDB DEFAULT CHARSET=latin1;
135
136INSERT INTO `tbl_pasien` (`no_pasien`, `nm_pasien`, `tgl_lahir`, `jk`, `pekerjaan`, `alamat`) VALUES
137(4, 'Wanita Pasien', '1971-05-28', 'Perempuan', 'Sebuah Pekerjaan', 'Alamat rumah contoh'),
138(5, 'Pasien Laki', '1990-08-01', 'Laki-laki', 'Tukang Angkek', 'Perumahan ');
139
140DROP TABLE IF EXISTS `tbl_periksa`;
141CREATE TABLE `tbl_periksa` (
142 `id_periksa` varchar(50) NOT NULL,
143 `tgl_periksa` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
144 `no_pasien` int(11) NOT NULL,
145 `keluhan` varchar(100) NOT NULL,
146 `tensi` varchar(9) NOT NULL,
147 PRIMARY KEY (`id_periksa`)
148) ENGINE=InnoDB DEFAULT CHARSET=latin1;
149
150
151DROP TABLE IF EXISTS `tbl_periksa_kb`;
152CREATE TABLE `tbl_periksa_kb` (
153 `no_pasien` int(11) NOT NULL,
154 `id_kb` varchar(50) NOT NULL,
155 `tgl_periksa` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
156 `nm_suami` varchar(100) NOT NULL,
157 `anak_ke` int(11) NOT NULL,
158 `haid_terakhir` date NOT NULL,
159 `berat_badan` int(11) NOT NULL,
160 `tensi` varchar(9) NOT NULL,
161 PRIMARY KEY (`id_kb`)
162) ENGINE=InnoDB DEFAULT CHARSET=latin1;
163
164INSERT INTO `tbl_periksa_kb` (`no_pasien`, `id_kb`, `tgl_periksa`, `nm_suami`, `anak_ke`, `haid_terakhir`, `berat_badan`, `tensi`) VALUES
165(4, '300520181126079965', '2018-05-30 04:26:18', '231', 2, '2018-05-30', 432, '11'),
166(4, '300520181131455272', '2018-05-30 04:32:31', 'qwe', 2, '2018-05-30', 12, '12');
167
168DROP TABLE IF EXISTS `tbl_terapi`;
169CREATE TABLE `tbl_terapi` (
170 `id_terapi` int(11) NOT NULL AUTO_INCREMENT,
171 `id_obat` int(11) NOT NULL,
172 `jumlah` int(11) NOT NULL,
173 `id_periksa` varchar(50) NOT NULL,
174 PRIMARY KEY (`id_terapi`)
175) ENGINE=InnoDB DEFAULT CHARSET=latin1;
176
177INSERT INTO `tbl_terapi` (`id_terapi`, `id_obat`, `jumlah`, `id_periksa`) VALUES
178(41, 1, 1, '300520181126079965'),
179(42, 1, 1, '300520181131455272'),
180(44, 9, 10, '3005181120014255'),
181(45, 2, 10, '3005181122144613');
182
183CREATE TRIGGER `kurangi_stok` AFTER INSERT ON `tbl_terapi` FOR EACH ROW update tbl_obat set stok = stok - new.jumlah where id_obat = new.id_obat;
184
185CREATE TRIGGER `reset_stok` AFTER DELETE ON `tbl_terapi` FOR EACH ROW update tbl_obat set stok = stok + OLD.jumlah where id_obat = OLD.id_obat;
186
187CREATE VIEW `laporan_pemasukan` AS select `a`.`id_terapi` AS `id_terapi`,`a`.`id_periksa` AS `id_periksa`,`b`.`nm_obat` AS `nm_obat`,sum(`a`.`jumlah`) AS `jumlah`,sum((`a`.`jumlah` * `b`.`hrg_obat`)) AS `total_bayar` from (`tbl_terapi` `a` join `tbl_obat` `b` on((`a`.`id_obat` = `b`.`id_obat`))) group by `a`.`id_periksa`;
188
189CREATE VIEW `laporan_terapi` AS select `a`.`id_periksa` AS `id_periksa`,`a`.`id_obat` AS `id_obat`,`b`.`nm_obat` AS `nm_obat`,`b`.`hrg_obat` AS `hrg_obat`,`a`.`jumlah` AS `jumlah`,(`a`.`jumlah` * `b`.`hrg_obat`) AS `total` from (`tbl_terapi` `a` join `tbl_obat` `b` on((`a`.`id_obat` = `b`.`id_obat`)));
190
191CREATE VIEW `laporan_kb` AS select `a`.`no_pasien` AS `no_pasien`,`c`.`nm_pasien` AS `nm_pasien`,`c`.`tgl_lahir` AS `tgl_lahir`,`a`.`tgl_periksa` AS `tgl_periksa`,`a`.`anak_ke` AS `anak_ke`,`a`.`haid_terakhir` AS `haid_terakhir`,`a`.`berat_badan` AS `berat_badan`,`a`.`tensi` AS `tensi`,`b`.`nm_obat` AS `nm_obat` from ((`tbl_periksa_kb` `a` join `laporan_terapi` `b` on((`a`.`id_kb` = `b`.`id_periksa`))) join `tbl_pasien` `c` on((`a`.`no_pasien` = `c`.`no_pasien`)));