Penerapan library dan RStudio untuk Sistem Basis Data dalam Manajemen Informasi Apotek
Mengimport library dibutuhkan paling awal agar dapat membaca package yang akan digunakan
library(RMySQL)## Loading required package: DBIlibrary(RMariaDB)
library(DBI)server <- dbConnect(
MariaDB(),
user = "root",
password = "",
host = "localhost"
)Sebelum membuat tabel diperlukan membuat database pada server yang telah dimasukkan
dbExecute(server, "CREATE DATABASE IF NOT EXISTS `ApotekSehat`")## [1] 1dbExecute(server, "USE `ApotekSehat`")## [1] 0#Tabel Pasien
dbExecute(server, "
CREATE TABLE Pasien (
ID_Pasien VARCHAR(10) PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Alamat VARCHAR(200),
No_Telp VARCHAR(15),
Tanggal_Daftar DATETIME NOT NULL
);
")## [1] 0#Tabel Obat
dbExecute(server, "
CREATE TABLE Obat (
ID_Obat VARCHAR(10) PRIMARY KEY,
Nama_Obat VARCHAR(100) NOT NULL,
Jenis_Obat ENUM('Tablet','Kapsul','Sirup','Salep','Injeksi') NOT NULL,
Harga DECIMAL(10,2) NOT NULL,
Stok INT NOT NULL
);
")## [1] 0#Tabel Resep
dbExecute(server, "
CREATE TABLE Resep (
ID_Resep VARCHAR(10) PRIMARY KEY,
ID_Pasien VARCHAR(10) NOT NULL,
Tanggal_Resep DATE NOT NULL,
Dokter VARCHAR(100) NOT NULL,
FOREIGN KEY(ID_Pasien) REFERENCES Pasien(ID_Pasien)
);
")## [1] 0#Tabel Detail Resep
dbExecute(server, "
CREATE TABLE Detail_Resep (
ID_Detail VARCHAR(10) PRIMARY KEY,
ID_Resep VARCHAR(10) NOT NULL,
ID_Obat VARCHAR(10) NOT NULL,
Jumlah INT NOT NULL,
FOREIGN KEY(ID_Resep) REFERENCES Resep(ID_Resep),
FOREIGN KEY(ID_Obat) REFERENCES Obat(ID_Obat)
);
")## [1] 0#Tabel Transaksi Penjualan
dbExecute(server, "
CREATE TABLE Transaksi (
ID_Transaksi VARCHAR(10) PRIMARY KEY,
ID_Resep VARCHAR(10),
Tanggal_Transaksi DATE NOT NULL,
Total_Bayar DECIMAL(15,2) NOT NULL,
Metode_Bayar ENUM('Cash','Transfer','QRIS') NOT NULL,
FOREIGN KEY(ID_Resep) REFERENCES Resep(ID_Resep)
);
")## [1] 0#Data Pasien
dbSendQuery(server, "
INSERT INTO Pasien VALUES
('PS001','Alya Putri','Jl. Melati 12','081234567890','2023-01-05'),
('PS002','Budi Hartono','Jl. Cempaka 30','081298765432','2023-01-10'),
('PS003','Dita Lestari','Jl. Dahlia 22','082134567899','2023-02-01'),
('PS004','Roni Saputra','Jl. Nusa Indah 17','081355667788','2023-02-15'),
('PS005','Salsa Fitri','Jl. Mawar 44','081227891234','2023-03-02');
")## <MariaDBResult>
## SQL
## INSERT INTO Pasien VALUES
## ('PS001','Alya Putri','Jl. Melati 12','081234567890','2023-01-05'),
## ('PS002','Budi Hartono','Jl. Cempaka 30','081298765432','2023-01-10'),
## ('PS003','Dita Lestari','Jl. Dahlia 22','082134567899','2023-02-01'),
## ('PS004','Roni Saputra','Jl. Nusa Indah 17','081355667788','2023-02-15'),
## ('PS005','Salsa Fitri','Jl. Mawar 44','081227891234','2023-03-02');
##
## ROWS Fetched: 0 [complete]
## Changed: 5#Data Obat
dbSendQuery(server, "
INSERT INTO Obat VALUES
('OB001','Paracetamol','Tablet',5000,200),
('OB002','Amoxicillin','Kapsul',8500,150),
('OB003','Vitamin C','Tablet',3000,300),
('OB004','Salep Gentamicin','Salep',12000,100),
('OB005','OBH Sirup','Sirup',15000,80),
('OB006','Ibuprofen','Tablet',7000,250),
('OB007','Omeprazole','Kapsul',9000,130);
")## Warning: Cancelling previous query## <MariaDBResult>
## SQL
## INSERT INTO Obat VALUES
## ('OB001','Paracetamol','Tablet',5000,200),
## ('OB002','Amoxicillin','Kapsul',8500,150),
## ('OB003','Vitamin C','Tablet',3000,300),
## ('OB004','Salep Gentamicin','Salep',12000,100),
## ('OB005','OBH Sirup','Sirup',15000,80),
## ('OB006','Ibuprofen','Tablet',7000,250),
## ('OB007','Omeprazole','Kapsul',9000,130);
##
## ROWS Fetched: 0 [complete]
## Changed: 7#Data Resep
dbSendQuery(server, "
INSERT INTO Resep VALUES
('R001','PS001','2023-01-06','dr. Siti'),
('R002','PS003','2023-02-01','dr. Bima'),
('R003','PS004','2023-02-16','dr. Dina'),
('R004','PS002','2023-03-05','dr. Siti'),
('R005','PS005','2023-03-06','dr. Arman');
")## Warning: Cancelling previous query## <MariaDBResult>
## SQL
## INSERT INTO Resep VALUES
## ('R001','PS001','2023-01-06','dr. Siti'),
## ('R002','PS003','2023-02-01','dr. Bima'),
## ('R003','PS004','2023-02-16','dr. Dina'),
## ('R004','PS002','2023-03-05','dr. Siti'),
## ('R005','PS005','2023-03-06','dr. Arman');
##
## ROWS Fetched: 0 [complete]
## Changed: 5#Data Detail Resep
dbSendQuery(server, "
INSERT INTO Detail_Resep VALUES
('DT01','R001','OB001',10),
('DT02','R001','OB003',20),
('DT03','R002','OB002',15),
('DT04','R003','OB004',2),
('DT05','R004','OB006',10),
('DT06','R005','OB005',1),
('DT07','R005','OB003',10);
")## Warning: Cancelling previous query## <MariaDBResult>
## SQL
## INSERT INTO Detail_Resep VALUES
## ('DT01','R001','OB001',10),
## ('DT02','R001','OB003',20),
## ('DT03','R002','OB002',15),
## ('DT04','R003','OB004',2),
## ('DT05','R004','OB006',10),
## ('DT06','R005','OB005',1),
## ('DT07','R005','OB003',10);
##
## ROWS Fetched: 0 [complete]
## Changed: 7#Data Transaksi Penjualan
dbSendQuery(server, "
INSERT INTO Transaksi VALUES
('TR001','R001','2023-01-06',160000,'Cash'),
('TR002','R002','2023-02-01',127500,'Transfer'),
('TR003','R003','2023-02-16',24000,'QRIS'),
('TR004','R004','2023-03-05',70000,'Cash'),
('TR005','R005','2023-03-06',45000,'QRIS');
")## Warning: Cancelling previous query## <MariaDBResult>
## SQL
## INSERT INTO Transaksi VALUES
## ('TR001','R001','2023-01-06',160000,'Cash'),
## ('TR002','R002','2023-02-01',127500,'Transfer'),
## ('TR003','R003','2023-02-16',24000,'QRIS'),
## ('TR004','R004','2023-03-05',70000,'Cash'),
## ('TR005','R005','2023-03-06',45000,'QRIS');
##
## ROWS Fetched: 0 [complete]
## Changed: 5Berdasarkan database yang telah dibuat dan tabel-tabel yang ada dalam database, berikut program yang akan menampilkan hasilnya ### Menampilkan Isi Tabel pada Database #### Menampilkan Isi Tabel Data Pasien
tampilkan.Pasien <- dbGetQuery(server,"
SELECT * FROM Pasien;
")## Warning: Cancelling previous querylibrary(DT)## Warning: package 'DT' was built under R version 4.5.2datatable(tampilkan.Pasien)tampilkan.Obat <- dbGetQuery(server,
"SELECT * FROM Obat;
")
library(DT)
datatable(tampilkan.Obat)tampilkan.Resep <- dbGetQuery(server,"
SELECT * FROM Resep;
")
library(DT)
datatable(tampilkan.Resep)tampilkan.Detail_Resep <- dbGetQuery(server,"
SELECT * FROM Detail_Resep;
")
library(DT)
datatable(tampilkan.Detail_Resep)tampilkan.Transaksi <- dbGetQuery(server,"
SELECT * FROM Transaksi;
")
library(DT)
datatable(tampilkan.Transaksi)#Menghitung jumlah pasien yang datang per bulan
jumlah_pasien <- dbGetQuery(server,"
SELECT MONTH(Tanggal_Daftar) AS Bulan, COUNT(*) AS Jumlah_Pasien
FROM Pasien
GROUP BY MONTH(Tanggal_Daftar);
")
library(DT)
datatable(jumlah_pasien)#Menambah kolom Total_Pengeluaran Pasien
dbExecute(server, "ALTER TABLE Pasien ADD COLUMN Total_Pengeluaran DECIMAL(15,2) DEFAULT 0;")## [1] 0dbExecute(server, "
UPDATE Pasien p
SET Total_Pengeluaran = (
SELECT COALESCE(SUM(Total_Bayar),0)
FROM Transaksi t
JOIN Resep r ON t.ID_Resep = r.ID_Resep
WHERE r.ID_Pasien = p.ID_Pasien
);
")## [1] 5Pasien.pengeluaran_tertinggi <- dbGetQuery(server, "
SELECT ID_Pasien, Nama, Total_Pengeluaran
FROM Pasien
ORDER BY Total_Pengeluaran DESC;
")
library(DT)
datatable(Pasien.pengeluaran_tertinggi)#Menghitung total stok per jenis obat
total_stok <- dbGetQuery(server, "
SELECT Jenis_Obat, SUM(Stok) AS Total_Stok
FROM Obat
GROUP BY Jenis_Obat;
")
library(DT)
datatable(total_stok)#Obat paling sering diresepkan
obat_terbanyak <- dbGetQuery(server, "
SELECT o.Nama_Obat, COUNT(*) AS Frekuensi
FROM Detail_Resep dr
JOIN Obat o ON dr.ID_Obat = o.ID_Obat
GROUP BY dr.ID_Obat
ORDER BY Frekuensi DESC;
")
library(DT)
datatable(obat_terbanyak)#Menghitung pendapatan per bulan
pendapatan <- dbGetQuery(server, "
SELECT MONTH(Tanggal_Transaksi) AS Bulan, SUM(Total_Bayar) AS Total_Pendapatan
FROM Transaksi
GROUP BY MONTH(Tanggal_Transaksi);
")
library(DT)
datatable(pendapatan)