210 lines
14 KiB
SQL
210 lines
14 KiB
SQL
-- Database Structure and Sample Data for ISBB Work Climate Measurement
|
|
-- Based on Permenaker No.5 Tahun 2018
|
|
|
|
-- ------------------------------------------------------------------------------
|
|
-- Master Tables - ISBB Specific
|
|
-- ------------------------------------------------------------------------------
|
|
|
|
-- Master Kategori Beban Kerja
|
|
CREATE TABLE IF NOT EXISTS master_workload_categories (
|
|
MasterWorkloadCategoryID INT PRIMARY KEY,
|
|
MasterWorkloadCategoryName VARCHAR(50) NOT NULL,
|
|
MasterWorkloadCategoryDescription TEXT,
|
|
MasterWorkloadCategoryEnergyRange VARCHAR(50),
|
|
MasterWorkloadCategoryActivityExamples TEXT,
|
|
MasterWorkloadCategoryIsActive BOOLEAN DEFAULT TRUE,
|
|
MasterWorkloadCategoryCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
MasterWorkloadCategoryCreatedUserID INT,
|
|
MasterWorkloadCategoryUpdatedAt DATETIME,
|
|
MasterWorkloadCategoryUpdatedUserID INT,
|
|
MasterWorkloadCategoryDeletedAt DATETIME,
|
|
MasterWorkloadCategoryDeletedUserID INT
|
|
);
|
|
|
|
-- Master Alokasi Kerja-Istirahat
|
|
CREATE TABLE IF NOT EXISTS master_work_rest_allocations (
|
|
MasterWorkRestAllocationID INT PRIMARY KEY,
|
|
MasterWorkRestAllocationName VARCHAR(50) NOT NULL,
|
|
MasterWorkRestAllocationWorkPercentage VARCHAR(20) NOT NULL,
|
|
MasterWorkRestAllocationRestPercentage VARCHAR(20) NOT NULL,
|
|
MasterWorkRestAllocationDescription TEXT,
|
|
MasterWorkRestAllocationIsActive BOOLEAN DEFAULT TRUE,
|
|
MasterWorkRestAllocationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
MasterWorkRestAllocationCreatedUserID INT,
|
|
MasterWorkRestAllocationUpdatedAt DATETIME,
|
|
MasterWorkRestAllocationUpdatedUserID INT,
|
|
MasterWorkRestAllocationDeletedAt DATETIME,
|
|
MasterWorkRestAllocationDeletedUserID INT
|
|
);
|
|
|
|
-- Master Baku Mutu ISBB
|
|
CREATE TABLE IF NOT EXISTS master_wbgt_standards (
|
|
MasterWBGTStandardID INT PRIMARY KEY,
|
|
MasterRegulationID INT NOT NULL,
|
|
MasterWorkloadCategoryID INT NOT NULL,
|
|
MasterWorkRestAllocationID INT NOT NULL,
|
|
MasterWBGTStandardValue DECIMAL(5,2) NOT NULL,
|
|
MasterWBGTStandardDescription TEXT,
|
|
MasterWBGTStandardIsActive BOOLEAN DEFAULT TRUE,
|
|
MasterWBGTStandardCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
MasterWBGTStandardCreatedUserID INT,
|
|
MasterWBGTStandardUpdatedAt DATETIME,
|
|
MasterWBGTStandardUpdatedUserID INT,
|
|
MasterWBGTStandardDeletedAt DATETIME,
|
|
MasterWBGTStandardDeletedUserID INT
|
|
);
|
|
|
|
-- ------------------------------------------------------------------------------
|
|
-- Transaction Tables - ISBB Specific
|
|
-- ------------------------------------------------------------------------------
|
|
|
|
-- Hasil Pengukuran ISBB
|
|
CREATE TABLE IF NOT EXISTS wbgt_measurements (
|
|
WBGTMeasurementID INT PRIMARY KEY,
|
|
ClimateSamplingPlanID INT NOT NULL,
|
|
WBGTMeasurementCode VARCHAR(50) NOT NULL,
|
|
WBGTMeasurementDate DATE NOT NULL,
|
|
WBGTMeasurementStartTime TIME,
|
|
WBGTMeasurementEndTime TIME,
|
|
WBGTMeasurementWeather VARCHAR(100),
|
|
WBGTMeasurementOfficer VARCHAR(100),
|
|
WBGTMeasurementStatus VARCHAR(20) DEFAULT 'DRAFT',
|
|
WBGTMeasurementNotes TEXT,
|
|
WBGTMeasurementCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
WBGTMeasurementCreatedUserID INT,
|
|
WBGTMeasurementUpdatedAt DATETIME,
|
|
WBGTMeasurementUpdatedUserID INT,
|
|
WBGTMeasurementDeletedAt DATETIME,
|
|
WBGTMeasurementDeletedUserID INT
|
|
);
|
|
|
|
-- Detail Hasil Pengukuran ISBB
|
|
CREATE TABLE IF NOT EXISTS wbgt_measurement_details (
|
|
WBGTMeasurementDetailID INT PRIMARY KEY,
|
|
WBGTMeasurementID INT NOT NULL,
|
|
WBGTMeasurementDetailPointCode VARCHAR(20) NOT NULL,
|
|
WBGTMeasurementDetailLocation VARCHAR(255) NOT NULL,
|
|
WBGTMeasurementDetailWorkType VARCHAR(255) NOT NULL,
|
|
MasterWorkloadCategoryID INT NOT NULL,
|
|
MasterWorkRestAllocationID INT NOT NULL,
|
|
WBGTMeasurementDetailWetTemp DECIMAL(5,2) NOT NULL,
|
|
WBGTMeasurementDetailGlobeTemp DECIMAL(5,2) NOT NULL,
|
|
WBGTMeasurementDetailDryTemp DECIMAL(5,2) NOT NULL,
|
|
WBGTMeasurementDetailWBGTValue DECIMAL(5,2) NOT NULL,
|
|
MasterWBGTStandardID INT NOT NULL,
|
|
WBGTMeasurementDetailComplianceStatus VARCHAR(20),
|
|
WBGTMeasurementDetailRecommendation TEXT,
|
|
WBGTMeasurementDetailMeasurementLocation VARCHAR(100),
|
|
WBGTMeasurementDetailMeasurementHeight DECIMAL(4,2),
|
|
WBGTMeasurementDetailNotes TEXT,
|
|
WBGTMeasurementDetailCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
WBGTMeasurementDetailCreatedUserID INT,
|
|
WBGTMeasurementDetailUpdatedAt DATETIME,
|
|
WBGTMeasurementDetailUpdatedUserID INT,
|
|
WBGTMeasurementDetailDeletedAt DATETIME,
|
|
WBGTMeasurementDetailDeletedUserID INT
|
|
);
|
|
|
|
-- Rekomendasi Pengendalian
|
|
CREATE TABLE IF NOT EXISTS wbgt_control_recommendations (
|
|
WBGTControlRecommendationID INT PRIMARY KEY,
|
|
WBGTMeasurementDetailID INT NOT NULL,
|
|
WBGTControlRecommendationType VARCHAR(50) NOT NULL,
|
|
WBGTControlRecommendationDescription TEXT NOT NULL,
|
|
WBGTControlRecommendationPriority INT,
|
|
WBGTControlRecommendationEstimatedCost DECIMAL(12,2),
|
|
WBGTControlRecommendationEffectiveness TEXT,
|
|
WBGTControlRecommendationStatus VARCHAR(20) DEFAULT 'PLANNED',
|
|
WBGTControlRecommendationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
WBGTControlRecommendationCreatedUserID INT,
|
|
WBGTControlRecommendationUpdatedAt DATETIME,
|
|
WBGTControlRecommendationUpdatedUserID INT,
|
|
WBGTControlRecommendationDeletedAt DATETIME,
|
|
WBGTControlRecommendationDeletedUserID INT
|
|
);
|
|
|
|
-- ------------------------------------------------------------------------------
|
|
-- Sample Data Insertion - Master Tables
|
|
-- ------------------------------------------------------------------------------
|
|
|
|
-- Insert Data Master Kategori Beban Kerja
|
|
INSERT INTO master_workload_categories (MasterWorkloadCategoryID, MasterWorkloadCategoryName, MasterWorkloadCategoryDescription, MasterWorkloadCategoryEnergyRange, MasterWorkloadCategoryActivityExamples, MasterWorkloadCategoryIsActive, MasterWorkloadCategoryCreatedUserID)
|
|
VALUES
|
|
(1, 'Ringan', 'Beban kerja ringan dengan pengeluaran energi minimal', '100-200 Kcal/jam', 'Pekerjaan administratif, kontrol ruangan, pekerjaan perakitan ringan, mengemudi', TRUE, 1),
|
|
(2, 'Sedang', 'Beban kerja sedang dengan aktivitas fisik moderat', '200-350 Kcal/jam', 'Berjalan normal, mengangkat beban sedang, mendorong atau menarik, perakitan / perbaikan mesin', TRUE, 1),
|
|
(3, 'Berat', 'Beban kerja berat dengan aktivitas fisik intens', '350-500 Kcal/jam', 'Berjalan cepat/naik tangga, mengangkat beban berat, pekerjaan konstruksi, penggalian, penambangan', TRUE, 1);
|
|
|
|
-- Insert Data Master Alokasi Kerja-Istirahat
|
|
INSERT INTO master_work_rest_allocations (MasterWorkRestAllocationID, MasterWorkRestAllocationName, MasterWorkRestAllocationWorkPercentage, MasterWorkRestAllocationRestPercentage, MasterWorkRestAllocationDescription, MasterWorkRestAllocationIsActive, MasterWorkRestAllocationCreatedUserID)
|
|
VALUES
|
|
(1, '75-100% Kerja', '75-100%', '0-25%', 'Alokasi 75-100% waktu untuk bekerja, 0-25% untuk istirahat dalam satu jam', TRUE, 1),
|
|
(2, '50-75% Kerja', '50-75%', '25-50%', 'Alokasi 50-75% waktu untuk bekerja, 25-50% untuk istirahat dalam satu jam', TRUE, 1),
|
|
(3, '25-50% Kerja', '25-50%', '50-75%', 'Alokasi 25-50% waktu untuk bekerja, 50-75% untuk istirahat dalam satu jam', TRUE, 1),
|
|
(4, '0-25% Kerja', '0-25%', '75-100%', 'Alokasi 0-25% waktu untuk bekerja, 75-100% untuk istirahat dalam satu jam', TRUE, 1);
|
|
|
|
-- Insert Data Master Baku Mutu ISBB
|
|
INSERT INTO master_wbgt_standards (MasterWBGTStandardID, MasterRegulationID, MasterWorkloadCategoryID, MasterWorkRestAllocationID, MasterWBGTStandardValue, MasterWBGTStandardDescription, MasterWBGTStandardIsActive, MasterWBGTStandardCreatedUserID)
|
|
VALUES
|
|
-- Kategori Ringan
|
|
(1, 2, 1, 1, 31.0, 'Nilai ISBB maksimum untuk beban kerja ringan dengan alokasi kerja 75-100%', TRUE, 1),
|
|
(2, 2, 1, 2, 31.0, 'Nilai ISBB maksimum untuk beban kerja ringan dengan alokasi kerja 50-75%', TRUE, 1),
|
|
(3, 2, 1, 3, 32.0, 'Nilai ISBB maksimum untuk beban kerja ringan dengan alokasi kerja 25-50%', TRUE, 1),
|
|
(4, 2, 1, 4, 32.2, 'Nilai ISBB maksimum untuk beban kerja ringan dengan alokasi kerja 0-25%', TRUE, 1),
|
|
|
|
-- Kategori Sedang
|
|
(5, 2, 2, 1, 28.0, 'Nilai ISBB maksimum untuk beban kerja sedang dengan alokasi kerja 75-100%', TRUE, 1),
|
|
(6, 2, 2, 2, 29.0, 'Nilai ISBB maksimum untuk beban kerja sedang dengan alokasi kerja 50-75%', TRUE, 1),
|
|
(7, 2, 2, 3, 30.0, 'Nilai ISBB maksimum untuk beban kerja sedang dengan alokasi kerja 25-50%', TRUE, 1),
|
|
(8, 2, 2, 4, 31.1, 'Nilai ISBB maksimum untuk beban kerja sedang dengan alokasi kerja 0-25%', TRUE, 1),
|
|
|
|
-- Kategori Berat
|
|
(9, 2, 3, 1, NULL, 'Untuk beban kerja berat tidak direkomendasikan alokasi kerja 75-100%', TRUE, 1),
|
|
(10, 2, 3, 2, 27.5, 'Nilai ISBB maksimum untuk beban kerja berat dengan alokasi kerja 50-75%', TRUE, 1),
|
|
(11, 2, 3, 3, 29.0, 'Nilai ISBB maksimum untuk beban kerja berat dengan alokasi kerja 25-50%', TRUE, 1),
|
|
(12, 2, 3, 4, 30.0, 'Nilai ISBB maksimum untuk beban kerja berat dengan alokasi kerja 0-25%', TRUE, 1);
|
|
|
|
-- ------------------------------------------------------------------------------
|
|
-- Sample Data Insertion - Transaction Tables
|
|
-- ------------------------------------------------------------------------------
|
|
|
|
-- Contoh Data Sampling Plan (Referensi dari tabel yang sudah ada)
|
|
INSERT INTO trx_sampling_plan_iklim (id_sampling_plan, kode_sampling_plan, nama_project, id_client, tanggal_rencana_sampling, lokasi_sampling, jumlah_titik, status_approval, catatan, tanggal_input, user_input)
|
|
VALUES
|
|
(2, 'SP-ISBB-2024-001', 'Pengukuran ISBB di PT. Indonesia Steel Manufacturing', 102, '2024-05-12', 'PT. Indonesia Steel Manufacturing, Jl. Industri Baja No. 75, Cikarang, Bekasi', 5, 'APPROVED', 'Pengukuran ISBB untuk area furnace, casting, rolling mill, welding, dan control room', '2024-05-01 10:30:00', 'supervisor');
|
|
|
|
-- Contoh Data Hasil Pengukuran ISBB
|
|
INSERT INTO wbgt_measurements (WBGTMeasurementID, ClimateSamplingPlanID, WBGTMeasurementCode, WBGTMeasurementDate, WBGTMeasurementStartTime, WBGTMeasurementEndTime, WBGTMeasurementWeather, WBGTMeasurementOfficer, WBGTMeasurementStatus, WBGTMeasurementNotes, WBGTMeasurementCreatedAt, WBGTMeasurementCreatedUserID)
|
|
VALUES
|
|
(1, 2, 'LHU/ISBB/05/2024/001', '2024-05-12', '10:00:00', '14:30:00', 'Cuaca cerah, suhu luar 33°C', 'Ahmad Kusuma, Budi Santoso', 'FINAL', 'Pengukuran berjalan dengan baik', '2024-05-12 16:45:00', 1);
|
|
|
|
-- Contoh Data Detail Hasil Pengukuran ISBB
|
|
INSERT INTO wbgt_measurement_details (WBGTMeasurementDetailID, WBGTMeasurementID, WBGTMeasurementDetailPointCode, WBGTMeasurementDetailLocation, WBGTMeasurementDetailWorkType, MasterWorkloadCategoryID, MasterWorkRestAllocationID, WBGTMeasurementDetailWetTemp, WBGTMeasurementDetailGlobeTemp, WBGTMeasurementDetailDryTemp, WBGTMeasurementDetailWBGTValue, MasterWBGTStandardID, WBGTMeasurementDetailComplianceStatus, WBGTMeasurementDetailRecommendation, WBGTMeasurementDetailMeasurementLocation, WBGTMeasurementDetailMeasurementHeight, WBGTMeasurementDetailNotes, WBGTMeasurementDetailCreatedAt, WBGTMeasurementDetailCreatedUserID)
|
|
VALUES
|
|
-- Furnace Operation Area
|
|
(1, 1, 'T1', 'Furnace Operation Area', 'Furnace monitoring and control', 2, 1, 27.8, 35.6, 32.5, 29.7, 5, 'NOT_COMPLY', 'Tingkatkan ventilasi, kurangi waktu paparan, atau ubah alokasi kerja', 'Dekat panel kontrol operator', 1.1, 'Pengukuran dilakukan saat furnace beroperasi normal', '2024-05-12 16:50:00', 1),
|
|
|
|
-- Cold Rolling Mill
|
|
(2, 1, 'T2', 'Cold Rolling Mill', 'Machine operation and material handling', 2, 1, 25.2, 31.5, 30.1, 26.8, 5, 'COMPLY', '', 'Area operator mesin rolling', 1.1, 'Pengukuran dilakukan saat proses rolling berjalan normal', '2024-05-12 17:00:00', 1),
|
|
|
|
-- Steel Casting Section
|
|
(3, 1, 'T3', 'Steel Casting Section', 'Heavy manual casting tasks', 3, 2, 28.4, 36.8, 33.2, 30.2, 10, 'NOT_COMPLY', 'Pasang pelindung panas tambahan, ubah alokasi kerja menjadi 25% kerja', 'Area penuangan logam', 1.1, 'Suhu sangat tinggi di sekitar area penuangan', '2024-05-12 17:15:00', 1),
|
|
|
|
-- Welding Section
|
|
(4, 1, 'T4', 'Welding Section', 'Manual welding tasks', 2, 2, 24.6, 31.0, 29.5, 26.1, 6, 'COMPLY', '', 'Area pengelasan manual', 1.1, 'Pengukuran dilakukan pada jarak 1.5 meter dari titik pengelasan', '2024-05-12 17:30:00', 1),
|
|
|
|
-- Production Control Room
|
|
(5, 1, 'T5', 'Production Control Room', 'Monitoring and administrative tasks', 1, 1, 23.8, 29.2, 28.6, 25.2, 1, 'COMPLY', '', 'Ruang kontrol produksi', 1.1, 'Ruangan ber-AC, kondisi stabil', '2024-05-12 17:45:00', 1);
|
|
|
|
-- Contoh Data Rekomendasi Pengendalian
|
|
INSERT INTO wbgt_control_recommendations (WBGTControlRecommendationID, WBGTMeasurementDetailID, WBGTControlRecommendationType, WBGTControlRecommendationDescription, WBGTControlRecommendationPriority, WBGTControlRecommendationEstimatedCost, WBGTControlRecommendationEffectiveness, WBGTControlRecommendationStatus, WBGTControlRecommendationCreatedAt, WBGTControlRecommendationCreatedUserID)
|
|
VALUES
|
|
-- Rekomendasi untuk Furnace Area
|
|
(1, 1, 'Pengendalian Teknik', 'Pemasangan local exhaust ventilation tambahan di area furnace', 1, 25000000.00, 'Diperkirakan dapat menurunkan ISBB 1.5-2°C', 'PLANNED', '2024-05-13 09:00:00', 1),
|
|
(2, 1, 'Pengendalian Administratif', 'Mengubah alokasi kerja menjadi 50% kerja, 50% istirahat', 1, 0.00, 'Memenuhi baku mutu untuk kategori kerja sedang', 'PLANNED', '2024-05-13 09:05:00', 1),
|
|
(3, 1, 'APD', 'Menyediakan cooling vest bagi operator furnace', 2, 5000000.00, 'Mengurangi beban panas pada pekerja', 'PLANNED', '2024-05-13 09:10:00', 1),
|
|
|
|
-- Rekomendasi untuk Steel Casting Section
|
|
(4, 3, 'Pengendalian Teknik', 'Pemasangan heat shield tambahan di sekitar area penuangan', 1, 18000000.00, 'Diperkirakan dapat menurunkan ISBB 1-1.5°C', 'PLANNED', '2024-05-13 09:15:00', 1),
|
|
(5, 3, 'Pengendalian Teknik', 'Pemasangan spot cooling di area kerja manual', 1, 12000000.00, 'Diperkirakan dapat menurunkan ISBB 1-2°C', 'PLANNED', '2024-05-13 09:20:00', 1),
|
|
(6, 3, 'Pengendalian Administratif', 'Rotasi kerja dan mengubah alokasi menjadi 25% kerja, 75% istirahat', 1, 0.00, 'Memenuhi baku mutu untuk kategori kerja berat', 'PLANNED', '2024-05-13 09:25:00', 1),
|
|
(7, 3, 'APD', 'Menyediakan pakaian pelindung reflektif panas', 2, 7500000.00, 'Mengurangi beban panas radiasi pada pekerja', 'PLANNED', '2024-05-13 09:30:00', 1); |