fajri/LAB_LINGKUNGAN
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Files
main
LAB_LINGKUNGAN/masterdata.txt
sas.fajri 044e5eb0fc sgsd
2025-04-24 14:51:42 +07:00

660 lines
53 KiB
Plaintext
Raw Permalink Blame History

CREATE TABLE master_parameters (
MasterParameterID INT PRIMARY KEY AUTO_INCREMENT,
MasterParameterCode VARCHAR(20) NOT NULL,
MasterParameterName VARCHAR(100) NOT NULL,
MasterParameterGroup VARCHAR(50), -- misalnya: fisika, kimia, mikrobiologi, logam berat
MasterParameterStandardMethod VARCHAR(100), -- metode analisis sesuai SNI atau standard lain
MasterParameterUnit VARCHAR(20), -- satuan parameter (mg/L, cfu/100mL, dsb)
MasterParameterPrice DECIMAL(12, 2), -- harga analisis per parameter
MasterParameterInstrumentation VARCHAR(100), -- peralatan analisis yang diperlukan
MasterParameterMDL DECIMAL(12, 6), -- Method Detection Limit
MasterParameterContainerType VARCHAR(50), -- jenis wadah (HDPE, kaca, dsb)
MasterParameterPreservation VARCHAR(200), -- metode preservasi
MasterParameterHoldingTime VARCHAR(50), -- waktu simpan maksimum
MasterParameterRemarks TEXT,
MasterParameterIsActive BOOLEAN DEFAULT TRUE,
MasterParameterCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterParameterCreatedUserID INT,
MasterParameterUpdatedAt DATETIME,
MasterParameterUpdatedUserID INT,
MasterParameterDeletedAt DATETIME,
MasterParameterDeletedUserID INT
);
CREATE TABLE master_equipment (
MasterEquipmentID INT PRIMARY KEY AUTO_INCREMENT,
MasterEquipmentCode VARCHAR(20) NOT NULL,
MasterEquipmentName VARCHAR(100) NOT NULL,
MasterEquipmentType VARCHAR(50) NOT NULL, -- sampling, pengukuran lapangan, preservasi, dll
MasterEquipmentSpecifications TEXT,
MasterEquipmentBrand VARCHAR(100),
MasterEquipmentModel VARCHAR(100),
MasterEquipmentSerialNumber VARCHAR(100),
MasterEquipmentPurchaseDate DATE,
MasterEquipmentCalibrationInterval INT, -- interval kalibrasi dalam hari
MasterEquipmentLastCalibrationDate DATE,
MasterEquipmentNextCalibrationDate DATE,
MasterEquipmentStatus VARCHAR(20), -- available, in use, maintenance, retired
MasterEquipmentLocation VARCHAR(100), -- lokasi penyimpanan
MasterEquipmentRemarks TEXT,
MasterEquipmentIsActive BOOLEAN DEFAULT TRUE,
MasterEquipmentCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterEquipmentCreatedUserID INT,
MasterEquipmentUpdatedAt DATETIME,
MasterEquipmentUpdatedUserID INT,
MasterEquipmentDeletedAt DATETIME,
MasterEquipmentDeletedUserID INT
);
CREATE TABLE master_personnel (
MasterPersonnelID INT PRIMARY KEY AUTO_INCREMENT,
MasterPersonnelCode VARCHAR(20) NOT NULL,
MasterPersonnelName VARCHAR(100) NOT NULL,
MasterPersonnelPosition VARCHAR(50),
MasterPersonnelDepartment VARCHAR(50),
MasterPersonnelSpecialization VARCHAR(100), -- bidang keahlian/spesialisasi
MasterPersonnelCertifications TEXT, -- sertifikasi yang dimiliki
MasterPersonnelContactNumber VARCHAR(20),
MasterPersonnelEmail VARCHAR(100),
MasterPersonnelJoinDate DATE,
MasterPersonnelQualification TEXT, -- kualifikasi/pendidikan
MasterPersonnelStatus VARCHAR(20), -- active, inactive
MasterPersonnelRemarks TEXT,
MasterPersonnelIsActive BOOLEAN DEFAULT TRUE,
MasterPersonnelCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterPersonnelCreatedUserID INT,
MasterPersonnelUpdatedAt DATETIME,
MasterPersonnelUpdatedUserID INT,
MasterPersonnelDeletedAt DATETIME,
MasterPersonnelDeletedUserID INT
);
CREATE TABLE master_clients (
MasterClientID INT PRIMARY KEY AUTO_INCREMENT,
MasterClientCode VARCHAR(20) NOT NULL,
MasterClientName VARCHAR(200) NOT NULL,
MasterClientType VARCHAR(50), -- industry, government, private, etc
MasterClientIndustrySector VARCHAR(100),
MasterClientAddress TEXT,
MasterClientCity VARCHAR(100),
MasterClientProvince VARCHAR(100),
MasterClientPostalCode VARCHAR(20),
MasterClientContactPerson VARCHAR(100),
MasterClientContactPosition VARCHAR(100),
MasterClientContactPhone VARCHAR(20),
MasterClientContactEmail VARCHAR(100),
MasterClientTaxID VARCHAR(50), -- NPWP
MasterClientPaymentTerms VARCHAR(50),
MasterClientDiscountRate DECIMAL(5, 2),
MasterClientCreditLimit DECIMAL(15, 2),
MasterClientStatus VARCHAR(20), -- active, inactive
MasterClientRemarks TEXT,
MasterClientIsActive BOOLEAN DEFAULT TRUE,
MasterClientCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterClientCreatedUserID INT,
MasterClientUpdatedAt DATETIME,
MasterClientUpdatedUserID INT,
MasterClientDeletedAt DATETIME,
MasterClientDeletedUserID INT
);
CREATE TABLE master_sampling_locations (
MasterSamplingLocationID INT PRIMARY KEY AUTO_INCREMENT,
MasterSamplingLocationCode VARCHAR(20) NOT NULL,
MasterSamplingLocationName VARCHAR(200) NOT NULL,
MasterClientID INT,
MasterSamplingLocationType VARCHAR(50), -- outlet, inlet, sungai, danau, sumur, dsb
MasterSamplingLocationAddress TEXT,
MasterSamplingLocationCoordinates POINT, -- koordinat GPS (latitude, longitude)
MasterSamplingLocationElevation DECIMAL(10, 2), -- elevasi dalam meter
MasterSamplingLocationDescription TEXT,
MasterSamplingLocationAccessInfo TEXT,
MasterSamplingLocationPointDetails TEXT,
MasterSamplingLocationRemarks TEXT,
MasterSamplingLocationIsActive BOOLEAN DEFAULT TRUE,
MasterSamplingLocationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterSamplingLocationCreatedUserID INT,
MasterSamplingLocationUpdatedAt DATETIME,
MasterSamplingLocationUpdatedUserID INT,
MasterSamplingLocationDeletedAt DATETIME,
MasterSamplingLocationDeletedUserID INT
);
CREATE TABLE master_sampling_methods (
MasterSamplingMethodID INT PRIMARY KEY AUTO_INCREMENT,
MasterSamplingMethodCode VARCHAR(20) NOT NULL,
MasterSamplingMethodName VARCHAR(100) NOT NULL,
MasterSamplingMethodStandardRef VARCHAR(100), -- referensi standar (SNI, APHA, EPA, dsb)
MasterSamplingMethodMatrix VARCHAR(50), -- air, tanah, udara, dsb
MasterSamplingMethodDescription TEXT,
MasterSamplingMethodEquipment TEXT,
MasterSamplingMethodProcedure TEXT,
MasterSamplingMethodQCRequirements TEXT, -- persyaratan quality control
MasterSamplingMethodLimitations TEXT,
MasterSamplingMethodRemarks TEXT,
MasterSamplingMethodIsActive BOOLEAN DEFAULT TRUE,
MasterSamplingMethodCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterSamplingMethodCreatedUserID INT,
MasterSamplingMethodUpdatedAt DATETIME,
MasterSamplingMethodUpdatedUserID INT,
MasterSamplingMethodDeletedAt DATETIME,
MasterSamplingMethodDeletedUserID INT
);
CREATE TABLE master_preservation_methods (
MasterPreservationMethodID INT PRIMARY KEY AUTO_INCREMENT,
MasterPreservationMethodCode VARCHAR(20) NOT NULL,
MasterPreservationMethodName VARCHAR(100) NOT NULL,
MasterPreservationMethodChemicalUsed VARCHAR(100),
MasterPreservationMethodApplicableParameters TEXT,
MasterPreservationMethodProcedure TEXT,
MasterPreservationMethodStorageCondition VARCHAR(100),
MasterPreservationMethodHoldingTime VARCHAR(50),
MasterPreservationMethodSafetyPrecautions TEXT,
MasterPreservationMethodRemarks TEXT,
MasterPreservationMethodIsActive BOOLEAN DEFAULT TRUE,
MasterPreservationMethodCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterPreservationMethodCreatedUserID INT,
MasterPreservationMethodUpdatedAt DATETIME,
MasterPreservationMethodUpdatedUserID INT,
MasterPreservationMethodDeletedAt DATETIME,
MasterPreservationMethodDeletedUserID INT
);
CREATE TABLE master_sample_containers (
MasterSampleContainerID INT PRIMARY KEY AUTO_INCREMENT,
MasterSampleContainerCode VARCHAR(20) NOT NULL,
MasterSampleContainerName VARCHAR(100) NOT NULL,
MasterSampleContainerMaterial VARCHAR(50),
MasterSampleContainerCapacity VARCHAR(20),
MasterSampleContainerCapType VARCHAR(50),
MasterSampleContainerSterilization VARCHAR(50),
MasterSampleContainerApplicableParameters TEXT,
MasterSampleContainerWashingProcedure TEXT,
MasterSampleContainerStorageRequirements TEXT,
MasterSampleContainerRemarks TEXT,
MasterSampleContainerIsActive BOOLEAN DEFAULT TRUE,
MasterSampleContainerCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterSampleContainerCreatedUserID INT,
MasterSampleContainerUpdatedAt DATETIME,
MasterSampleContainerUpdatedUserID INT,
MasterSampleContainerDeletedAt DATETIME,
MasterSampleContainerDeletedUserID INT
);
CREATE TABLE master_regulations (
MasterRegulationID INT PRIMARY KEY AUTO_INCREMENT,
MasterRegulationCode VARCHAR(20) NOT NULL,
MasterRegulationName VARCHAR(200) NOT NULL,
MasterRegulationIssuingAuthority VARCHAR(100),
MasterRegulationType VARCHAR(50),
MasterRegulationIssueDate DATE,
MasterRegulationEffectiveDate DATE,
MasterRegulationApplicableSector VARCHAR(100),
MasterRegulationScope TEXT,
MasterRegulationSummary TEXT,
MasterRegulationFilePath VARCHAR(255),
MasterRegulationRemarks TEXT,
MasterRegulationIsActive BOOLEAN DEFAULT TRUE,
MasterRegulationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterRegulationCreatedUserID INT,
MasterRegulationUpdatedAt DATETIME,
MasterRegulationUpdatedUserID INT,
MasterRegulationDeletedAt DATETIME,
MasterRegulationDeletedUserID INT
);
CREATE TABLE master_quality_standards (
MasterQualityStandardID INT PRIMARY KEY AUTO_INCREMENT,
MasterQualityStandardCode VARCHAR(20) NOT NULL,
MasterQualityStandardName VARCHAR(200) NOT NULL,
MasterRegulationID INT,
MasterQualityStandardMatrixType VARCHAR(50),
MasterQualityStandardIndustryType VARCHAR(100),
MasterParameterID INT,
MasterQualityStandardMinValue DECIMAL(12,6),
MasterQualityStandardMaxValue DECIMAL(12,6),
MasterQualityStandardUnit VARCHAR(20),
MasterQualityStandardRemarks TEXT,
MasterQualityStandardIsActive BOOLEAN DEFAULT TRUE,
MasterQualityStandardCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterQualityStandardCreatedUserID INT,
MasterQualityStandardUpdatedAt DATETIME,
MasterQualityStandardUpdatedUserID INT,
MasterQualityStandardDeletedAt DATETIME,
MasterQualityStandardDeletedUserID INT
);
CREATE TABLE parameter_method_mapping (
ParameterMethodMappingID INT PRIMARY KEY AUTO_INCREMENT,
MasterParameterID INT,
MasterSamplingMethodID INT,
MasterSampleContainerID INT,
MasterPreservationMethodID INT,
ParameterMethodMappingIsDefault BOOLEAN DEFAULT FALSE,
ParameterMethodMappingRemarks TEXT,
ParameterMethodMappingIsActive BOOLEAN DEFAULT TRUE,
ParameterMethodMappingCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
ParameterMethodMappingCreatedUserID INT,
ParameterMethodMappingUpdatedAt DATETIME,
ParameterMethodMappingUpdatedUserID INT,
ParameterMethodMappingDeletedAt DATETIME,
ParameterMethodMappingDeletedUserID INT
);
CREATE TABLE sampling_plans (
SamplingPlanID INT PRIMARY KEY AUTO_INCREMENT,
SamplingPlanCode VARCHAR(30) NOT NULL,
SamplingPlanProjectName VARCHAR(200) NOT NULL,
MasterClientID INT,
SamplingPlanContactPersonID INT,
SamplingPlanRequestDate DATETIME,
SamplingPlanPlannedDate DATE,
SamplingPlanStatus VARCHAR(20),
SamplingPlanCreatedBy INT,
SamplingPlanApprovedBy INT,
SamplingPlanApprovalDate DATETIME,
SamplingPlanTotalLocations INT,
SamplingPlanTotalParameters INT,
SamplingPlanRemarks TEXT,
SamplingPlanCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
SamplingPlanCreatedUserID INT,
SamplingPlanUpdatedAt DATETIME,
SamplingPlanUpdatedUserID INT,
SamplingPlanDeletedAt DATETIME,
SamplingPlanDeletedUserID INT
);
CREATE TABLE sampling_plan_locations (
SamplingPlanLocationID INT PRIMARY KEY AUTO_INCREMENT,
SamplingPlanID INT,
MasterSamplingLocationID INT,
SamplingPlanLocationDate DATE,
SamplingPlanLocationTime TIME,
MasterSamplingMethodID INT,
SamplingPlanLocationPersonnelID INT,
SamplingPlanLocationFieldParameters TEXT,
SamplingPlanLocationSpecialInstructions TEXT,
SamplingPlanLocationStatus VARCHAR(20),
SamplingPlanLocationRemarks TEXT,
SamplingPlanLocationCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
SamplingPlanLocationCreatedUserID INT,
SamplingPlanLocationUpdatedAt DATETIME,
SamplingPlanLocationUpdatedUserID INT,
SamplingPlanLocationDeletedAt DATETIME,
SamplingPlanLocationDeletedUserID INT
);
CREATE TABLE sampling_plan_parameters (
SamplingPlanParameterID INT PRIMARY KEY AUTO_INCREMENT,
SamplingPlanID INT,
SamplingPlanLocationID INT,
MasterParameterID INT,
MasterSampleContainerID INT,
MasterPreservationMethodID INT,
SamplingPlanParameterQuantity INT DEFAULT 1,
SamplingPlanParameterUnitPrice DECIMAL(12,2),
SamplingPlanParameterRemarks TEXT,
SamplingPlanParameterCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
SamplingPlanParameterCreatedUserID INT,
SamplingPlanParameterUpdatedAt DATETIME,
SamplingPlanParameterUpdatedUserID INT,
SamplingPlanParameterDeletedAt DATETIME,
SamplingPlanParameterDeletedUserID INT
);
CREATE TABLE equipment_requisitions (
EquipmentRequisitionID INT PRIMARY KEY AUTO_INCREMENT,
EquipmentRequisitionCode VARCHAR(30) NOT NULL,
SamplingPlanID INT,
EquipmentRequisitionDate DATETIME,
EquipmentRequisitionRequestedBy INT,
EquipmentRequisitionApprovedBy INT,
EquipmentRequisitionApprovalDate DATETIME,
EquipmentRequisitionSamplingDate DATE,
EquipmentRequisitionReturnDate DATE,
EquipmentRequisitionStatus VARCHAR(20),
EquipmentRequisitionRemarks TEXT,
EquipmentRequisitionCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
EquipmentRequisitionCreatedUserID INT,
EquipmentRequisitionUpdatedAt DATETIME,
EquipmentRequisitionUpdatedUserID INT,
EquipmentRequisitionDeletedAt DATETIME,
EquipmentRequisitionDeletedUserID INT
);
CREATE TABLE equipment_requisition_items (
EquipmentRequisitionItemID INT PRIMARY KEY AUTO_INCREMENT,
EquipmentRequisitionID INT,
MasterEquipmentID INT,
EquipmentRequisitionItemQuantity INT,
EquipmentRequisitionItemIssuedQuantity INT,
EquipmentRequisitionItemIssuedBy INT,
EquipmentRequisitionItemIssuedDate DATETIME,
EquipmentRequisitionItemReturnedQuantity INT,
EquipmentRequisitionItemReturnedCondition VARCHAR(50),
EquipmentRequisitionItemReturnedDate DATETIME,
EquipmentRequisitionItemVerifiedBy INT,
EquipmentRequisitionItemRemarks TEXT,
EquipmentRequisitionItemCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
EquipmentRequisitionItemCreatedUserID INT,
EquipmentRequisitionItemUpdatedAt DATETIME,
EquipmentRequisitionItemUpdatedUserID INT,
EquipmentRequisitionItemDeletedAt DATETIME,
EquipmentRequisitionItemDeletedUserID INT
);
CREATE TABLE samples (
SampleID INT PRIMARY KEY AUTO_INCREMENT,
SampleCode VARCHAR(50) NOT NULL,
SamplingPlanID INT,
SamplingPlanLocationID INT,
SampleDate DATE,
SampleTime TIME,
SampleSamplerID INT,
SamplePreservationTime DATETIME,
SampleTemperature DECIMAL(5,2),
SampleWeatherCondition VARCHAR(50),
SampleFieldPH DECIMAL(5,2),
SampleFieldDO DECIMAL(5,2),
SampleFieldConductivity DECIMAL(10,2),
SampleFieldTurbidity DECIMAL(10,2),
SampleMatrix VARCHAR(50),
SampleSource VARCHAR(100),
MasterSampleContainerID INT,
MasterPreservationMethodID INT,
SampleReceivedBy INT,
SampleReceivedDate DATETIME,
SampleReceivedCondition VARCHAR(50),
SampleStorageLocation VARCHAR(100),
SampleRemarks TEXT,
SampleStatus VARCHAR(20),
SampleCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
SampleCreatedUserID INT,
SampleUpdatedAt DATETIME,
SampleUpdatedUserID INT,
SampleDeletedAt DATETIME,
SampleDeletedUserID INT
);
CREATE TABLE measurement_results_light (
MeasurementResultLightID INT PRIMARY KEY AUTO_INCREMENT,
SampleID INT,
MeasurementResultLightDate DATE,
MeasurementResultLightTime TIME,
MasterParameterID INT,
MeasurementResultLightValue DECIMAL(10,2),
MeasurementResultLightUnit VARCHAR(20),
MeasurementResultLightPoint VARCHAR(50),
MeasurementResultLightWeatherCondition VARCHAR(50),
MeasurementResultLightSource VARCHAR(100),
MeasurementResultLightAmbientCondition VARCHAR(200),
MasterQualityStandardID INT,
MeasurementResultLightIsCompliant BOOLEAN,
MeasurementResultLightAnalystID INT,
MeasurementResultLightVerifiedBy INT,
MeasurementResultLightVerificationDate DATETIME,
MeasurementResultLightRemarks TEXT,
MeasurementResultLightCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MeasurementResultLightCreatedUserID INT,
MeasurementResultLightUpdatedAt DATETIME,
MeasurementResultLightUpdatedUserID INT,
MeasurementResultLightDeletedAt DATETIME,
MeasurementResultLightDeletedUserID INT
);
CREATE TABLE master_analysis_methods (
MasterAnalysisMethodID INT PRIMARY KEY AUTO_INCREMENT,
MasterAnalysisMethodCode VARCHAR(20) NOT NULL,
MasterAnalysisMethodName VARCHAR(100) NOT NULL,
MasterAnalysisMethodReference VARCHAR(100),
MasterAnalysisMethodDescription TEXT,
MasterAnalysisMethodPrinciple TEXT,
MasterAnalysisMethodInterference TEXT,
MasterAnalysisMethodEquipment TEXT,
MasterAnalysisMethodReagent TEXT,
MasterAnalysisMethodProcedure TEXT,
MasterAnalysisMethodCalculation TEXT,
MasterAnalysisMethodQC TEXT,
MasterAnalysisMethodMDL DECIMAL(10,4),
MasterAnalysisMethodIsActive BOOLEAN DEFAULT TRUE,
MasterAnalysisMethodCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterAnalysisMethodCreatedUserID INT,
MasterAnalysisMethodUpdatedAt DATETIME,
MasterAnalysisMethodUpdatedUserID INT,
MasterAnalysisMethodDeletedAt DATETIME,
MasterAnalysisMethodDeletedUserID INT
);
CREATE TABLE master_matrix_types (
MasterMatrixTypeID INT PRIMARY KEY AUTO_INCREMENT,
MasterMatrixTypeCode VARCHAR(20) NOT NULL,
MasterMatrixTypeName VARCHAR(100) NOT NULL,
MasterMatrixTypeDescription TEXT,
MasterMatrixTypePreservation TEXT,
MasterMatrixTypeHoldingTime VARCHAR(50),
MasterMatrixTypeIsActive BOOLEAN DEFAULT TRUE,
MasterMatrixTypeCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterMatrixTypeCreatedUserID INT,
MasterMatrixTypeUpdatedAt DATETIME,
MasterMatrixTypeUpdatedUserID INT,
MasterMatrixTypeDeletedAt DATETIME,
MasterMatrixTypeDeletedUserID INT
);
CREATE TABLE master_sample_types (
MasterSampleTypeID INT PRIMARY KEY AUTO_INCREMENT,
MasterMatrixTypeID INT,
MasterSampleTypeCode VARCHAR(20) NOT NULL,
MasterSampleTypeName VARCHAR(100) NOT NULL,
MasterSampleTypeDescription TEXT,
MasterSampleTypeVolume DECIMAL(10,2),
MasterSampleTypeUnit VARCHAR(20),
MasterSampleTypeIsActive BOOLEAN DEFAULT TRUE,
MasterSampleTypeCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterSampleTypeCreatedUserID INT,
MasterSampleTypeUpdatedAt DATETIME,
MasterSampleTypeUpdatedUserID INT,
MasterSampleTypeDeletedAt DATETIME,
MasterSampleTypeDeletedUserID INT
);
CREATE TABLE master_units (
MasterUnitID INT PRIMARY KEY AUTO_INCREMENT,
MasterUnitCode VARCHAR(20) NOT NULL,
MasterUnitName VARCHAR(100) NOT NULL,
MasterUnitSymbol VARCHAR(20),
MasterUnitType VARCHAR(50),
MasterUnitDescription TEXT,
MasterUnitIsActive BOOLEAN DEFAULT TRUE,
MasterUnitCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterUnitCreatedUserID INT,
MasterUnitUpdatedAt DATETIME,
MasterUnitUpdatedUserID INT,
MasterUnitDeletedAt DATETIME,
MasterUnitDeletedUserID INT
);
CREATE TABLE master_payment_terms (
MasterPaymentTermID INT PRIMARY KEY AUTO_INCREMENT,
MasterPaymentTermCode VARCHAR(20) NOT NULL,
MasterPaymentTermName VARCHAR(100) NOT NULL,
MasterPaymentTermDays INT,
MasterPaymentTermDescription TEXT,
MasterPaymentTermIsActive BOOLEAN DEFAULT TRUE,
MasterPaymentTermCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterPaymentTermCreatedUserID INT,
MasterPaymentTermUpdatedAt DATETIME,
MasterPaymentTermUpdatedUserID INT,
MasterPaymentTermDeletedAt DATETIME,
MasterPaymentTermDeletedUserID INT
);
CREATE TABLE master_client_categories (
MasterClientCategoryID INT PRIMARY KEY AUTO_INCREMENT,
MasterClientCategoryCode VARCHAR(20) NOT NULL,
MasterClientCategoryName VARCHAR(100) NOT NULL,
MasterClientCategoryDescription TEXT,
MasterClientCategoryDiscountRate DECIMAL(5,2),
MasterClientCategoryIsActive BOOLEAN DEFAULT TRUE,
MasterClientCategoryCreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
MasterClientCategoryCreatedUserID INT,
MasterClientCategoryUpdatedAt DATETIME,
MasterClientCategoryUpdatedUserID INT,
MasterClientCategoryDeletedAt DATETIME,
MasterClientCategoryDeletedUserID INT
);
INSERT INTO master_parameters (MasterParameterID, MasterParameterCode, MasterParameterName, MasterParameterGroup, MasterParameterStandardMethod, MasterParameterUnit, MasterParameterPrice, MasterParameterInstrumentation, MasterParameterMDL, MasterParameterContainerType, MasterParameterPreservation, MasterParameterHoldingTime, MasterParameterRemarks, MasterParameterIsActive) VALUES
(1, 'pH', 'pH', 'Fisika', 'SNI 06-6989.11-2019', 'unit', 50000.00, 'pH meter', 0.01, 'HDPE/Glass', 'Analisis segera', '15 menit (lapangan) / 24 jam', 'Parameter lapangan, prioritas analisis', TRUE),
(2, 'TSS', 'Total Suspended Solid', 'Fisika', 'SNI 06-6989.3-2019', 'mg/L', 75000.00, 'Analytical Balance, Oven', 1.00, 'HDPE/Glass', 'Pendinginan 4°C', '7 hari', NULL, TRUE),
(3, 'COD', 'Chemical Oxygen Demand', 'Kimia', 'SNI 6989.73:2019', 'mg/L', 125000.00, 'COD Reactor, Spektrofotometer', 5.00, 'Glass', 'H₂SO₄ hingga pH<2, pendinginan 4°C', '28 hari', NULL, TRUE),
(4, 'BOD', 'Biological Oxygen Demand', 'Kimia', 'SNI 6989.72:2009', 'mg/L', 150000.00, 'DO meter, BOD Incubator', 2.00, 'Glass', 'Pendinginan 4°C', '48 jam', 'Memerlukan inkubasi 5 hari', TRUE),
(5, 'DO', 'Dissolved Oxygen', 'Kimia', 'SNI 06-6989.14-2004', 'mg/L', 80000.00, 'DO meter', 0.10, 'Glass BOD bottle', 'Analisis segera/fiksasi di lapangan', '8 jam setelah fiksasi', 'Parameter lapangan, prioritas analisis', TRUE),
(6, 'NH3-N', 'Ammonia Nitrogen', 'Kimia', 'SNI 06-6989.30-2005', 'mg/L', 100000.00, 'Spektrofotometer UV-Vis', 0.01, 'HDPE/Glass', 'H₂SO₄ hingga pH<2, pendinginan 4°C', '28 hari', NULL, TRUE),
(7, 'NO3-N', 'Nitrate Nitrogen', 'Kimia', 'SNI 6989.79:2011', 'mg/L', 100000.00, 'Spektrofotometer UV-Vis', 0.01, 'HDPE/Glass', 'Pendinginan 4°C', '48 jam', NULL, TRUE),
(8, 'Pb', 'Timbal', 'Logam Berat', 'SNI 6989.8:2009', 'mg/L', 200000.00, 'AAS/ICP-MS', 0.01, 'HDPE/Glass', 'HNO₃ hingga pH<2', '6 bulan', 'Memerlukan digestion sebelum analisis', TRUE),
(9, 'Cd', 'Kadmium', 'Logam Berat', 'SNI 6989.16:2009', 'mg/L', 200000.00, 'AAS/ICP-MS', 0.005, 'HDPE/Glass', 'HNO₃ hingga pH<2', '6 bulan', 'Memerlukan digestion sebelum analisis', TRUE),
(10, 'Hg', 'Merkuri', 'Logam Berat', 'SNI 6989.78:2011', 'mg/L', 250000.00, 'Mercury Analyzer', 0.0005, 'Glass', 'HNO₃ hingga pH<2, pendinginan 4°C', '28 hari', 'Menggunakan wadah khusus bebas kontaminasi', TRUE),
(11, 'MBAS', 'Surfaktan Anionik', 'Kimia', 'SNI 06-6989.51-2005', 'mg/L', 150000.00, 'Spektrofotometer UV-Vis', 0.05, 'Glass', 'Pendinginan 4°C', '48 jam', NULL, TRUE),
(12, 'OG', 'Minyak & Lemak', 'Kimia', 'SNI 6989.10:2011', 'mg/L', 175000.00, 'Analytical Balance, Oven', 1.00, 'Glass', 'H₂SO₄ hingga pH<2, pendinginan 4°C', '28 hari', 'Hindari kontak dengan plastik', TRUE),
(13, 'TCOLI', 'Total Coliform', 'Mikrobiologi', 'SNI 01-2332.1-2006', 'MPN/100mL', 175000.00, 'Incubator, Laminar Flow', 1.80, 'Sterile Glass/Plastic', 'Na₂S₂O₃, pendinginan 4°C', '6 jam', 'Wadah steril, sampling aseptik', TRUE),
(14, 'ECOLI', 'Escherichia coli', 'Mikrobiologi', 'SNI 2897:2008', 'MPN/100mL', 200000.00, 'Incubator, Laminar Flow', 1.80, 'Sterile Glass/Plastic', 'Na₂S₂O₃, pendinginan 4°C', '6 jam', 'Wadah steril, sampling aseptik', TRUE),
(15, 'PO4', 'Fosfat', 'Kimia', 'SNI 06-6989.31-2005', 'mg/L', 110000.00, 'Spektrofotometer UV-Vis', 0.01, 'HDPE/Glass', 'Pendinginan 4°C', '48 jam', NULL, TRUE),
(16, 'CAHAYA', 'Intensitas Cahaya', 'Fisika', 'SNI 16-7062-2004', 'Lux', 75000.00, 'Lux meter', 1.00, 'N/A', 'Pengukuran langsung di lapangan', 'N/A', 'Parameter fisika untuk lingkungan dan ruang kerja', TRUE),
(17, 'LUMI', 'Luminansi', 'Fisika', 'IES LM-79-08', 'cd/m²', 85000.00, 'Luminance meter', 0.10, 'N/A', 'Pengukuran langsung di lapangan', 'N/A', 'Parameter untuk pengukuran kecerahan permukaan', TRUE),
(18, 'SILAU', 'Kesilauan', 'Fisika', 'CIE 112-1994', 'UGR', 90000.00, 'Glare meter', 1.00, 'N/A', 'Pengukuran langsung di lapangan', 'N/A', 'Pengukuran untuk menilai tingkat silau di ruang kerja', TRUE),
(19, 'CFLUKS', 'Fluks Cahaya', 'Fisika', 'IES LM-79-08', 'Lumen', 95000.00, 'Integrating Sphere', 10.00, 'N/A', 'Pengukuran langsung di lapangan', 'N/A', 'Parameter untuk mengukur total output cahaya dari sumber', TRUE);
INSERT INTO master_equipment (MasterEquipmentID, MasterEquipmentCode, MasterEquipmentName, MasterEquipmentType, MasterEquipmentSpecifications, MasterEquipmentBrand, MasterEquipmentModel, MasterEquipmentSerialNumber, MasterEquipmentPurchaseDate, MasterEquipmentCalibrationInterval, MasterEquipmentLastCalibrationDate, MasterEquipmentNextCalibrationDate, MasterEquipmentStatus, MasterEquipmentLocation, MasterEquipmentRemarks, MasterEquipmentIsActive) VALUES
(1, 'MULTIP001', 'Multiparameter Water Quality Meter', 'Field Measurement', 'Parameter: pH, DO, Conductivity, Temperature, Turbidity', 'YSI', 'ProDSS', 'YS19K12345', '2023-01-15', 180, '2023-12-10', '2024-06-08', 'available', 'Equipment Room A', 'Alat utama untuk pengukuran lapangan', TRUE),
(2, 'pH002', 'pH Meter Portable', 'Field Measurement', 'Range: 0-14, Resolution: 0.01, Accuracy: ±0.02', 'Hanna', 'HI98191', 'HI12345678', '2023-03-20', 90, '2024-01-05', '2024-04-04', 'available', 'Equipment Room A', 'Backup untuk pengukuran pH lapangan', TRUE),
(3, 'DO003', 'DO Meter Portable', 'Field Measurement', 'Range: 0-50 mg/L, Resolution: 0.01 mg/L', 'Lutron', 'DO-5519', 'LT5519876', '2022-11-10', 90, '2024-01-10', '2024-04-09', 'in use', 'Field Team B', NULL, TRUE),
(4, 'VDS004', 'Van Dorn Water Sampler', 'Sampling', 'Horizontal, 2.2L capacity, PVC material', 'Wildco', 'Alpha', 'WC1234567', '2023-02-18', 365, '2023-02-18', '2024-02-18', 'available', 'Equipment Room B', NULL, TRUE),
(5, 'VDS005', 'Van Dorn Water Sampler', 'Sampling', 'Horizontal, 4.2L capacity, PVC material', 'Wildco', 'Beta', 'WC8765432', '2023-02-18', 365, '2023-02-18', '2024-02-18', 'maintenance', 'Maintenance Room', 'Sedang perbaikan tali', TRUE),
(6, 'PER006', 'Peristaltic Pump', 'Sampling', 'Flow rate: 50-500 mL/min, Silicon tubing', 'Masterflex', 'L/S', 'MF987654', '2022-08-25', 180, '2023-08-25', '2024-02-21', 'available', 'Equipment Room B', NULL, TRUE),
(7, 'TSP007', 'Telescopic Sampling Pole', 'Sampling', 'Extendable 2-5m, Aluminum', 'In-house', 'Custom', 'N/A', '2023-05-10', 365, '2023-05-10', '2024-05-09', 'available', 'Equipment Room B', NULL, TRUE),
(8, 'COL008', 'Cooler Box (Large)', 'Transport', '50L capacity, Insulated, with ice pack slots', 'Coleman', 'Xtreme', 'N/A', '2023-04-12', 0, NULL, NULL, 'available', 'Equipment Room B', NULL, TRUE),
(9, 'COL009', 'Cooler Box (Medium)', 'Transport', '25L capacity, Insulated, with ice pack slots', 'Coleman', 'Xtreme', 'N/A', '2023-04-12', 0, NULL, NULL, 'in use', 'Field Team A', NULL, TRUE),
(10, 'GPS010', 'GPS Device', 'Field Measurement', 'Accuracy: 3m, Waterproof IPX7', 'Garmin', 'GPSMAP 65s', 'GM98765432', '2023-01-30', 365, '2023-01-30', '2024-01-30', 'available', 'Equipment Room A', NULL, TRUE),
(11, 'TUR011', 'Turbidity Meter', 'Field Measurement', 'Range: 0-1000 NTU, Accuracy: ±2%', 'Hach', '2100Q', 'HC2100123', '2022-10-15', 180, '2023-10-15', '2024-04-13', 'available', 'Equipment Room A', NULL, TRUE),
(12, 'FMT012', 'Flow Meter', 'Field Measurement', 'Range: 0.1-5 m/s, Digital display', 'Global Water', 'FP111', 'GW123456', '2023-06-20', 180, '2023-12-20', '2024-06-18', 'in use', 'Field Team C', NULL, TRUE),
(13, 'CAM013', 'Digital Camera', 'Documentation', 'Waterproof, 20MP, GPS tagging', 'Olympus', 'Tough TG-6', 'OL987654', '2023-03-15', 0, NULL, NULL, 'available', 'Equipment Room A', NULL, TRUE),
(14, 'SAU014', 'Soil Auger', 'Sampling', 'Hand-operated, 50cm depth', 'AMS', 'Standard', 'AMS12345', '2023-02-10', 0, NULL, NULL, 'available', 'Equipment Room C', NULL, TRUE),
(15, 'SGS015', 'Sediment Grab Sampler', 'Sampling', 'Petite Ponar type, Stainless steel', 'Wildco', 'Ponar', 'WC456789', '2023-02-18', 365, '2023-02-18', '2024-02-18', 'available', 'Equipment Room C', NULL, TRUE),
(16, 'LUX001', 'Lux Meter Digital', 'Field Measurement', 'Range: 0-200,000 lux, Resolution: 0.1 lux, Accuracy: ±3%', 'Testo', '540', 'TS5401234', '2023-05-15', 180, '2023-11-15', '2024-05-13', 'available', 'Equipment Room A', NULL, TRUE),
(17, 'LUX002', 'Lux Meter Digital', 'Field Measurement', 'Range: 0-100,000 lux, Resolution: 0.1 lux, Accuracy: ±2%', 'Konica Minolta', 'T-10A', 'KM10A5678', '2022-08-20', 180, '2023-08-20', '2024-02-16', 'available', 'Equipment Room A', NULL, TRUE),
(18, 'LUM003', 'Luminance Meter', 'Field Measurement', 'Range: 0.01-999,900 cd/m², Accuracy: ±2%', 'Konica Minolta', 'LS-150', 'KM1501234', '2023-02-10', 180, '2023-08-10', '2024-02-06', 'available', 'Equipment Room A', 'Untuk pengukuran kecerahan permukaan', TRUE),
(19, 'GLARE004', 'Glare Meter', 'Field Measurement', 'Range: UGR 5-40, Accuracy: ±1 UGR', 'GL Optic', 'GL Spectis 1.0 T Flicker', 'GL12345', '2023-01-25', 180, '2023-07-25', '2024-01-21', 'in use', 'Field Team A', 'Untuk pengukuran tingkat silau', TRUE),
(20, 'GRID005', 'Grid Mapping Kit', 'Sampling', 'Grid markers, Measuring tape, Laser distance meter', 'In-house', 'Custom', 'N/A', '2023-03-15', 0, NULL, NULL, 'available', 'Equipment Room B', 'Peralatan penunjang untuk mapping titik sampling', TRUE);
INSERT INTO master_personnel (MasterPersonnelID, MasterPersonnelCode, MasterPersonnelName, MasterPersonnelPosition, MasterPersonnelDepartment, MasterPersonnelSpecialization, MasterPersonnelCertifications, MasterPersonnelContactNumber, MasterPersonnelEmail, MasterPersonnelJoinDate, MasterPersonnelQualification, MasterPersonnelStatus, MasterPersonnelRemarks, MasterPersonnelIsActive) VALUES
(1, 'EMP001', 'Ahmad Ramadhan', 'Lab Manager', 'Laboratory', 'Environmental Analysis', 'KAN Lead Assessor, ISO 17025 Internal Auditor', '081234567890', 'ahmad@lab-env.co.id', '2015-03-10', 'M.Sc. Environmental Chemistry', 'active', NULL, TRUE),
(2, 'EMP002', 'Siti Aminah', 'Sampling Team Leader', 'Field Operations', 'Water Quality Sampling', 'Certified Environmental Sampler, First Aid', '082345678901', 'siti@lab-env.co.id', '2017-06-15', 'B.Sc. Environmental Science', 'active', NULL, TRUE),
(3, 'EMP003', 'Budi Santoso', 'Field Technician', 'Field Operations', 'Water & Soil Sampling', 'Certified Environmental Sampler', '083456789012', 'budi@lab-env.co.id', '2019-01-10', 'Diploma in Environmental Technology', 'active', NULL, TRUE),
(4, 'EMP004', 'Dini Wijaya', 'Admin Lab', 'Administration', 'Customer Service, Documentation', 'ISO 9001 Documentation', '084567890123', 'dini@lab-env.co.id', '2020-02-15', 'B.A. Business Administration', 'active', NULL, TRUE),
(5, 'EMP005', 'Faisal Rahman', 'Senior Analyst', 'Laboratory', 'Chemical Analysis', 'AAS Specialist, GC-MS Operator', '085678901234', 'faisal@lab-env.co.id', '2016-08-20', 'M.Sc. Analytical Chemistry', 'active', NULL, TRUE),
(6, 'EMP006', 'Eva Susanti', 'Microbiologist', 'Laboratory', 'Microbiological Analysis', 'Food Safety Microbiology', '086789012345', 'eva@lab-env.co.id', '2018-11-05', 'B.Sc. Microbiology', 'active', NULL, TRUE),
(7, 'EMP007', 'Dedi Kurniawan', 'Field Technician', 'Field Operations', 'Air Quality Sampling', 'Air Quality Monitoring, Stack Sampling', '087890123456', 'dedi@lab-env.co.id', '2021-03-15', 'B.Sc. Environmental Engineering', 'active', NULL, TRUE),
(8, 'EMP008', 'Yanti Susilo', 'QA/QC Officer', 'Quality Assurance', 'Quality Management System', 'ISO 17025 Lead Assessor, ISO 9001 Lead Auditor', '088901234567', 'yanti@lab-env.co.id', '2017-07-10', 'M.Sc. Quality Management', 'active', NULL, TRUE),
(9, 'EMP009', 'Hendra Gunawan', 'Field Technician', 'Field Operations', 'Water & Soil Sampling', 'Water Quality Sampling', '089012345678', 'hendra@lab-env.co.id', '2022-01-10', 'Diploma in Environmental Technology', 'active', NULL, TRUE),
(10, 'EMP010', 'Nita Anggraini', 'Analyst', 'Laboratory', 'Chemical Analysis', 'HPLC Operator', '081122334455', 'nita@lab-env.co.id', '2022-05-10', 'B.Sc. Chemistry', 'active', NULL, TRUE),
(11, 'EMP011', 'Rudi Hartono', 'IT Support', 'IT', 'LIMS, Network Management', 'CCNA, Microsoft Certified', '081234543210', 'rudi@lab-env.co.id', '2020-11-15', 'B.Sc. Information Technology', 'active', NULL, TRUE),
(12, 'EMP012', 'Dr. Surya Wijaya', 'Technical Director', 'Management', 'Environmental Analytics, Research', 'ISO 17025 Technical Expert', '087654321098', 'surya@lab-env.co.id', '2010-02-01', 'Ph.D. Environmental Chemistry', 'active', NULL, TRUE);
INSERT INTO master_clients (MasterClientID, MasterClientCode, MasterClientName, MasterClientType, MasterClientIndustrySector, MasterClientAddress, MasterClientCity, MasterClientProvince, MasterClientPostalCode, MasterClientContactPerson, MasterClientContactPosition, MasterClientContactPhone, MasterClientContactEmail, MasterClientTaxID, MasterClientPaymentTerms, MasterClientDiscountRate, MasterClientCreditLimit, MasterClientStatus, MasterClientRemarks, MasterClientIsActive) VALUES
(1, 'CL001', 'PT. Industri Tekstil Nusantara', 'industry', 'Textile', 'Jl. Industri No. 123', 'Bandung', 'Jawa Barat', '40123', 'Budi Santoso', 'Environmental Officer', '08123456789', 'budi@tekstilnusantara.com', '01.234.567.8-901.000', 'Net 30', 0.00, 10000000.00, 'active', 'Pelanggan reguler triwulanan', TRUE),
(2, 'CL002', 'PT. Kimia Farma Tbk', 'industry', 'Pharmaceutical', 'Jl. Veteran No. 45', 'Jakarta', 'DKI Jakarta', '10110', 'Ani Wijaya', 'EHS Manager', '08234567890', 'ani@kimiafarma.co.id', '02.345.678.9-012.000', 'Net 15', 5.00, 25000000.00, 'active', NULL, TRUE),
(3, 'CL003', 'Dinas Lingkungan Hidup Kota', 'government', 'Environmental Regulation', 'Jl. Ahmad Yani No. 35', 'Bandung', 'Jawa Barat', '40115', 'Siti Aminah', 'Koordinator Pemantauan Air', '08765432100', 'siti@dlh.kotabjb.go.id', '03.456.789.0-123.000', 'Government Invoice', 0.00, 50000000.00, 'active', 'Kontrak tahunan', TRUE),
(4, 'CL004', 'PT. Aneka Tambang Tbk', 'industry', 'Mining', 'Jl. TB Simatupang No. 67', 'Jakarta', 'DKI Jakarta', '12520', 'Hadi Sutrisno', 'Environmental Compliance Manager', '08345678901', 'hadi@antam.com', '04.567.890.1-234.000', 'Net 30', 0.00, 35000000.00, 'active', NULL, TRUE),
(5, 'CL005', 'PT. Tirta Investama (Aqua)', 'industry', 'Food & Beverage', 'Jl. Pulo Lentut No. 3', 'Bekasi', 'Jawa Barat', '17122', 'Maya Indah', 'Quality Assurance Manager', '08456789012', 'maya@aqua.com', '05.678.901.2-345.000', 'Net 15', 2.50, 20000000.00, 'active', NULL, TRUE),
(6, 'CL006', 'Balai Besar Wilayah Sungai Citarum', 'government', 'Water Resources', 'Jl. Braga No. 137', 'Bandung', 'Jawa Barat', '40111', 'Agus Hermawan', 'Kepala Seksi Kualitas Air', '08987654321', 'agus@bbwscitarum.com', '06.789.012.3-456.000', 'Government Invoice', 0.00, 40000000.00, 'active', 'Program Citarum Harum', TRUE),
(7, 'CL007', 'PT. Indofood CBP Sukses Makmur Tbk', 'industry', 'Food Processing', 'Jl. Industri Selatan No. 45', 'Bekasi', 'Jawa Barat', '17530', 'Dewi Sartika', 'EHS Compliance Officer', '08567890123', 'dewi@indofood.co.id', '07.890.123.4-567.000', 'Net 30', 0.00, 30000000.00, 'active', NULL, TRUE),
(8, 'CL008', 'PT. PLN (Persero)', 'government', 'Energy & Power', 'Jl. Trunojoyo Blok M-I No.135', 'Jakarta', 'DKI Jakarta', '12160', 'Bambang Irawan', 'Environmental Officer', '08678901234', 'bambang@pln.co.id', '08.901.234.5-678.000', 'Net 45', 0.00, 45000000.00, 'active', NULL, TRUE),
(9, 'CL009', 'PT. Pertamina EP', 'government', 'Oil & Gas', 'Jl. Medan Merdeka Timur No. 1A', 'Jakarta', 'DKI Jakarta', '10110', 'Joko Susilo', 'HSE Manager', '08789012345', 'joko@pertamina.com', '09.012.345.6-789.000', 'Net 45', 0.00, 50000000.00, 'active', NULL, TRUE),
(10, 'CL010', 'PT. Jakarta Propertindo', 'government', 'Property Development', 'Jl. Kebon Sirih No. 12', 'Jakarta', 'DKI Jakarta', '10340', 'Lina Hartati', 'Environmental Manager', '08890123456', 'lina@jakpro.co.id', '10.123.456.7-890.000', 'Net 30', 0.00, 25000000.00, 'active', NULL, TRUE);
INSERT INTO master_sampling_locations (MasterSamplingLocationID, MasterSamplingLocationCode, MasterSamplingLocationName, MasterClientID, MasterSamplingLocationType, MasterSamplingLocationAddress, MasterSamplingLocationCoordinates, MasterSamplingLocationElevation, MasterSamplingLocationDescription, MasterSamplingLocationAccessInfo, MasterSamplingLocationPointDetails, MasterSamplingLocationRemarks, MasterSamplingLocationIsActive) VALUES
(1, 'LOC001', 'Outlet IPAL PT. ITN', 1, 'outlet', 'Jl. Industri No. 123, Bandung', POINT(-6.914744, 107.609810), 650.5, 'Outlet dari Instalasi Pengolahan Air Limbah utama', 'Akses melalui pintu belakang pabrik, koordinasi dengan security', 'Sampel diambil dari saluran outlet sebelum bercampur dengan badan air penerima', NULL, TRUE),
(2, 'LOC002', 'Inlet IPAL PT. ITN', 1, 'inlet', 'Jl. Industri No. 123, Bandung', POINT(-6.914789, 107.609835), 652.0, 'Inlet menuju Instalasi Pengolahan Air Limbah', 'Akses melalui area produksi, perlu ijin khusus', 'Sampel diambil dari bak ekualisasi sebelum treatment', 'Memerlukan APD lengkap, beresiko bau menyengat', TRUE),
(3, 'LOC003', 'Titik 1 Sungai Citarum (Hulu)', 3, 'sungai', 'Desa Cikitu, Kab. Bandung', POINT(-6.947890, 107.632505), 875.3, 'Lokasi hulu Sungai Citarum', 'Akses dengan kendaraan 4WD + jalan kaki 500m', 'Sampel diambil dari tengah sungai dengan perahu', 'Koordinasi dengan penduduk lokal untuk akses', TRUE),
(4, 'LOC004', 'Titik 2 Sungai Citarum', 3, 'sungai', 'Desa Dayeuhkolot, Kab. Bandung', POINT(-6.933456, 107.621234), 780.0, 'Lokasi setelah kawasan industri', 'Akses dari jembatan', 'Sampel diambil dari tengah sungai dengan tali dan ember', NULL, TRUE),
(5, 'LOC005', 'Titik 3 Sungai Citarum', 3, 'sungai', 'Kecamatan Baleendah, Kab. Bandung', POINT(-6.925678, 107.612345), 720.4, 'Lokasi dekat pemukiman padat', 'Akses dari jalan desa, parkir di lapangan', 'Sampel diambil dari tepi sungai dengan telescopic pole', NULL, TRUE),
(6, 'LOC006', 'Titik 4 Sungai Citarum', 3, 'sungai', 'Kecamatan Dayeuhkolot, Kab. Bandung', POINT(-6.912345, 107.603456), 680.2, 'Lokasi setelah pertemuan dengan anak sungai', 'Akses dari jalan provinsi, parkir di bahu jalan', 'Sampel diambil dari jembatan dengan tali dan ember', 'Arus cukup deras di lokasi ini', TRUE),
(7, 'LOC007', 'Titik 5 Sungai Citarum (Hilir)', 3, 'sungai', 'Kecamatan Bojongsoang, Kab. Bandung', POINT(-6.901234, 107.594567), 650.8, 'Lokasi hilir sebelum bendungan', 'Akses dari jalan desa, parkir di rumah penduduk', 'Sampel diambil dari tepi sungai dengan telescopic pole', NULL, TRUE),
(8, 'LOC008', 'Sumur Pantau 1 PT. Aneka Tambang', 4, 'sumur pantau', 'Area Tambang Timur, Kabupaten Bogor', POINT(-6.412345, 106.857654), 342.5, 'Sumur pantau untuk monitoring air tanah', 'Akses dengan kendaraan tambang, koordinasi dengan security', 'Sumur PVC 4 inch, kedalaman 15m, pakai bailer', 'Perlu pengukuran level air tanah sebelum sampling', TRUE),
(9, 'LOC009', 'Sumur Pantau 2 PT. Aneka Tambang', 4, 'sumur pantau', 'Area Tambang Barat, Kabupaten Bogor', POINT(-6.418765, 106.849876), 356.8, 'Sumur pantau untuk monitoring air tanah', 'Akses dengan kendaraan tambang, koordinasi dengan security', 'Sumur PVC 4 inch, kedalaman 20m, pakai bailer', 'Perlu pengukuran level air tanah sebelum sampling', TRUE),
(10, 'LOC010', 'Outlet IPAL PT. Kimia Farma', 2, 'outlet', 'Jl. Veteran No. 45, Jakarta', POINT(-6.183456, 106.823456), 12.5, 'Outlet IPAL produksi farmasi', 'Akses melalui laboratorium QC, koordinasi dengan staff EHS', 'Sampel diambil dari bak kontrol setelah IPAL', 'Kemungkinan mengandung residu antibiotik', TRUE),
(11, 'LOC011', 'Sumber Air Baku Aqua', 5, 'sumber air', 'Desa Babakan, Kabupaten Sukabumi', POINT(-6.912387, 106.912345), 875.4, 'Sumber mata air untuk produksi air minum', 'Akses terbatas, perlu ijin khusus dan didampingi staff', 'Sampel diambil langsung dari mata air', 'Area steril, perlu protokol khusus', TRUE),
(12, 'LOC012', 'Sungai Ciliwung Hulu', 10, 'sungai', 'Kebun Raya Bogor', POINT(-6.597890, 106.799012), 260.5, 'Bagian hulu sungai, kawasan konservasi', 'Akses dari Kebun Raya, perlu izin masuk', 'Sampel diambil dari jembatan dengan tali dan ember', 'Sampel baseline kualitas air', TRUE);
INSERT INTO master_sampling_methods (MasterSamplingMethodID, MasterSamplingMethodCode, MasterSamplingMethodName, MasterSamplingMethodStandardRef, MasterSamplingMethodMatrix, MasterSamplingMethodDescription, MasterSamplingMethodEquipment, MasterSamplingMethodProcedure, MasterSamplingMethodQCRequirements, MasterSamplingMethodLimitations, MasterSamplingMethodRemarks, MasterSamplingMethodIsActive) VALUES
(1, 'GS-001', 'Grab Sampling for Surface Water', 'SNI 6989.57:2008', 'air permukaan', 'Pengambilan sampel sesaat untuk air permukaan', 'Botol sampel sesuai parameter, tali, ember, tongkat sampling', 'Sampel diambil secara langsung dari badan air dengan kedalaman sekitar 30cm dari permukaan', 'Blanko lapangan, sampel duplikat 1 per 10 sampel', NULL, NULL, TRUE),
(2, 'MS-CAHAYA-01', 'Pengukuran Intensitas Cahaya Ruangan', 'SNI 16-7062-2004', 'ruang_kerja', 'Metode pengukuran intensitas cahaya dalam ruangan', 'Lux meter, tripod, meteran', 'Pengukuran dilakukan dengan menempatkan sensor lux meter pada bidang kerja (umumnya 0.75-0.85m dari lantai). Diambil minimal 3 titik pengukuran yang mewakili area kerja. Untuk ruangan <10m², minimal 1 titik di tengah ruangan.', 'Kalibrasi lux meter sebelum pengukuran, verifikasi dengan standar cahaya (jika ada)', 'Hasil dapat dipengaruhi oleh kondisi cuaca, bayangan, dan refleksi cahaya', NULL, TRUE),
(3, 'MS-CAHAYA-02', 'Pengukuran Intensitas Cahaya Area Kerja Industri', 'PERMENAKER No.5 Tahun 2018', 'area_kerja', 'Metode pengukuran intensitas cahaya untuk area kerja industri', 'Lux meter digital, tripod, meteran, lembar grid area kerja', 'Pengukuran dilakukan dengan membuat grid area kerja. Untuk area <100m², minimal 9 titik pengukuran (grid 3x3). Sensor diletakkan pada bidang kerja atau 0.85m dari lantai. Pengukuran dilakukan dalam kondisi operasional normal.', 'Kalibrasi lux meter, pengukuran duplikat minimal 10% dari total titik', 'Pengukuran optimal dilakukan saat aktivitas produksi normal. Perhatikan pengaruh bayangan mesin dan peralatan.', 'Untuk area produksi dengan pencahayaan kritis, perlu pengukuran pada waktu yang berbeda (pagi, siang, malam)', TRUE),
(4, 'MS-CAHAYA-03', 'Pengukuran Luminansi Permukaan', 'IES LM-79-08', 'permukaan_kerja', 'Metode pengukuran luminansi (kecerahan) permukaan', 'Luminance meter, tripod, material reflektansi standar', 'Pengukuran dilakukan dengan mengarahkan luminance meter ke permukaan yang diukur pada sudut pengamatan normal. Jarak pengukuran disesuaikan dengan spesifikasi alat.', 'Kalibrasi dengan material reflektansi standar', 'Refleksi cahaya dari permukaan lain dapat mempengaruhi hasil', 'Terutama untuk evaluasi silau dan kontras pencahayaan', TRUE),
(5, 'MS-CAHAYA-04', 'Pengukuran Distribusi Cahaya', 'CIE S 025/E:2015', 'ruang_tiga_dimensi', 'Metode pengukuran distribusi cahaya dalam ruang tiga dimensi', 'Lux meter dengan probe 3D, tripod, perangkat lunak pemetaan cahaya', 'Pengukuran dilakukan pada grid tiga dimensi dalam ruangan untuk memetakan distribusi cahaya. Pengukuran minimal pada 3 ketinggian berbeda.', 'Verifikasi dengan pengukuran pada titik referensi', 'Memerlukan waktu pengukuran yang lebih lama', 'Untuk analisis distribusi cahaya yang lebih komprehensif', TRUE);
INSERT INTO master_regulations (MasterRegulationID, MasterRegulationCode, MasterRegulationName, MasterRegulationIssuingAuthority, MasterRegulationType, MasterRegulationIssueDate, MasterRegulationEffectiveDate, MasterRegulationApplicableSector, MasterRegulationScope, MasterRegulationSummary, MasterRegulationFilePath, MasterRegulationRemarks, MasterRegulationIsActive) VALUES
(1, 'PMK-02-2023', 'Peraturan Menteri Kesehatan Republik Indonesia Nomor 2 Tahun 2023', 'Kementerian Kesehatan', 'Peraturan Menteri', '2023-01-20', '2023-02-01', 'Kesehatan Lingkungan, Kesehatan Kerja', 'Standar Baku Mutu Kesehatan Lingkungan dan Persyaratan Kesehatan', 'Peraturan ini mengatur tentang standar baku mutu kesehatan lingkungan untuk media udara, air, tanah, pangan, dan sarana/bangunan, serta persyaratan kesehatan lainnya', '/dokumen/regulasi/PMK_02_2023.pdf', 'Menggantikan PMK No.32 Tahun 2017', TRUE),
(2, 'PERMENAKER-05-2018', 'Peraturan Menteri Ketenagakerjaan Republik Indonesia Nomor 5 Tahun 2018', 'Kementerian Ketenagakerjaan', 'Peraturan Menteri', '2018-04-25', '2018-05-10', 'Keselamatan dan Kesehatan Kerja', 'Keselamatan dan Kesehatan Kerja Lingkungan Kerja', 'Peraturan ini mengatur tentang standar K3 Lingkungan Kerja termasuk faktor fisika, kimia, biologi, ergonomi dan psikologi', '/dokumen/regulasi/PERMENAKER_05_2018.pdf', NULL, TRUE);
INSERT INTO master_quality_standards (MasterQualityStandardID, MasterQualityStandardCode, MasterQualityStandardName, MasterRegulationID, MasterQualityStandardMatrixType, MasterQualityStandardIndustryType, MasterParameterID, MasterQualityStandardMinValue, MasterQualityStandardMaxValue, MasterQualityStandardUnit, MasterQualityStandardRemarks, MasterQualityStandardIsActive) VALUES
-- Berdasarkan PMK RI No.2 Tahun 2023
(1, 'PMK-CAH-01', 'Intensitas Cahaya Ruang Kerja Perkantoran', 1, 'ruang_kerja', 'perkantoran', 16, 300.00, NULL, 'Lux', 'Intensitas cahaya untuk pekerjaan rutin', TRUE),
(2, 'PMK-CAH-02', 'Intensitas Cahaya Ruang Rapat', 1, 'ruang_rapat', NULL, 16, 300.00, NULL, 'Lux', NULL, TRUE),
(3, 'PMK-CAH-03', 'Intensitas Cahaya Ruang Arsip', 1, 'ruang_arsip', NULL, 16, 150.00, NULL, 'Lux', NULL, TRUE),
(4, 'PMK-CAH-04', 'Intensitas Cahaya Ruang Resepsionis', 1, 'ruang_resepsionis', NULL, 16, 300.00, NULL, 'Lux', NULL, TRUE),
(5, 'PMK-CAH-05', 'Intensitas Cahaya Ruang Komputer', 1, 'ruang_komputer', NULL, 16, 500.00, NULL, 'Lux', 'Untuk menghindari kelelahan mata dan refleksi layar', TRUE),
(6, 'PMK-CAH-06', 'Intensitas Cahaya Ruang Gambar', 1, 'ruang_gambar', NULL, 16, 750.00, NULL, 'Lux', 'Untuk pekerjaan detail tinggi', TRUE),
(7, 'PMK-CAH-07', 'Intensitas Cahaya Ruang Perpustakaan', 1, 'ruang_perpustakaan', NULL, 16, 300.00, NULL, 'Lux', NULL, TRUE),
(8, 'PMK-CAH-08', 'Intensitas Cahaya Ruang Laboratorium', 1, 'ruang_laboratorium', NULL, 16, 500.00, NULL, 'Lux', NULL, TRUE),
(9, 'PMK-CAH-09', 'Intensitas Cahaya Ruang Pemeriksaan', 1, 'ruang_pemeriksaan', 'kesehatan', 16, 500.00, NULL, 'Lux', NULL, TRUE),
-- Berdasarkan Permenaker No.5 Tahun 2018
(10, 'PMN-CAH-01', 'Intensitas Cahaya Pekerjaan Kasar', 2, 'area_kerja', 'industri', 16, 100.00, NULL, 'Lux', 'Pekerjaan dengan detail besar, tidak teliti', TRUE),
(11, 'PMN-CAH-02', 'Intensitas Cahaya Pekerjaan Sedang', 2, 'area_kerja', 'industri', 16, 200.00, NULL, 'Lux', 'Pekerjaan dengan detail sedang', TRUE),
(12, 'PMN-CAH-03', 'Intensitas Cahaya Pekerjaan Halus', 2, 'area_kerja', 'industri', 16, 500.00, NULL, 'Lux', 'Pekerjaan dengan detail kecil', TRUE),
(13, 'PMN-CAH-04', 'Intensitas Cahaya Pekerjaan Amat Halus', 2, 'area_kerja', 'industri', 16, 1000.00, NULL, 'Lux', 'Pekerjaan dengan detail sangat kecil', TRUE),
(14, 'PMN-CAH-05', 'Intensitas Cahaya Pekerjaan Terinci', 2, 'area_kerja', 'industri', 16, 2000.00, NULL, 'Lux', 'Pekerjaan yang membutuhkan ketelitian tinggi', TRUE),
(15, 'PMN-CAH-06', 'Intensitas Cahaya Lorong', 2, 'area_kerja', 'industri', 16, 50.00, NULL, 'Lux', 'Area sirkulasi', TRUE),
(16, 'PMN-CAH-07', 'Intensitas Cahaya Tangga', 2, 'area_kerja', 'industri', 16, 100.00, NULL, 'Lux', 'Area sirkulasi vertikal', TRUE),
(17, 'PMN-CAH-08', 'Intensitas Cahaya Gudang', 2, 'area_kerja', 'industri', 16, 100.00, NULL, 'Lux', 'Area penyimpanan dengan aktivitas rendah', TRUE),
(18, 'PMN-CAH-09', 'Luminansi Maksimum', 2, 'area_kerja', 'industri', 17, NULL, 5000.00, 'cd/m²', 'Untuk menghindari efek silau', TRUE),
(19, 'PMN-CAH-10', 'Indeks Kesilauan Maksimum', 2, 'area_kerja', 'industri', 18, NULL, 19.00, 'UGR', 'Batas kesilauan yang dapat ditoleransi', TRUE);
INSERT INTO sampling_plans (SamplingPlanID, SamplingPlanCode, SamplingPlanProjectName, MasterClientID, SamplingPlanContactPersonID, SamplingPlanRequestDate, SamplingPlanPlannedDate, SamplingPlanStatus, SamplingPlanCreatedBy, SamplingPlanApprovedBy, SamplingPlanApprovalDate, SamplingPlanTotalLocations, SamplingPlanTotalParameters, SamplingPlanRemarks, SamplingPlanCreatedAt) VALUES
(1, 'SP-LX-2024-001', 'Monitoring Pencahayaan Ruang Kerja Triwulan I 2024', 1, NULL, '2024-01-05 09:00:00', '2024-01-20', 'approved', 4, 1, '2024-01-10 14:30:00', 10, 1, 'Monitoring rutin triwulanan sesuai PMK No.2 Tahun 2023', '2024-01-05 09:15:20');
INSERT INTO sampling_plan_locations (SamplingPlanLocationID, SamplingPlanID, MasterSamplingLocationID, SamplingPlanLocationDate, SamplingPlanLocationTime, MasterSamplingMethodID, SamplingPlanLocationPersonnelID, SamplingPlanLocationFieldParameters, SamplingPlanLocationSpecialInstructions, SamplingPlanLocationStatus, SamplingPlanLocationRemarks) VALUES
(1, 1, 1, '2024-01-20', '09:00:00', 2, 3, 'Intensitas Cahaya', 'Pengukuran dilakukan saat kondisi pencahayaan normal (tidak ada maintenance lampu)', 'planned', 'Area kerja produksi utama'),
(2, 1, 2, '2024-01-20', '10:30:00', 2, 3, 'Intensitas Cahaya', 'Pengukuran dilakukan saat kondisi pencahayaan normal (tidak ada maintenance lampu)', 'planned', 'Area IPAL'),
(3, 1, NULL, '2024-01-20', '11:30:00', 2, 3, 'Intensitas Cahaya', 'Pengukuran pada area kantor administrasi', 'planned', 'Ruang administrasi lantai 2'),
(4, 1, NULL, '2024-01-20', '13:00:00', 2, 3, 'Intensitas Cahaya, Luminansi', 'Pengukuran pada area kerja dengan penggunaan komputer', 'planned', 'Ruang desain produk'),
(5, 1, NULL, '2024-01-20', '14:00:00', 2, 3, 'Intensitas Cahaya', 'Pengukuran pada area QC', 'planned', 'Laboratorium QC');
INSERT INTO sampling_plan_parameters (SamplingPlanParameterID, SamplingPlanID, SamplingPlanLocationID, MasterParameterID, MasterSampleContainerID, MasterPreservationMethodID, SamplingPlanParameterQuantity, SamplingPlanParameterUnitPrice, SamplingPlanParameterRemarks) VALUES
(1, 1, 1, 16, NULL, NULL, 1, 75000.00, 'Pengukuran grid 3x3'),
(2, 1, 2, 16, NULL, NULL, 1, 75000.00, 'Pengukuran grid 3x3'),
(3, 1, 3, 16, NULL, NULL, 1, 75000.00, 'Pengukuran pada meja kerja utama'),
(4, 1, 4, 16, NULL, NULL, 1, 75000.00, 'Pengukuran pada meja kerja utama'),
(5, 1, 4, 17, NULL, NULL, 1, 85000.00, 'Pengukuran luminansi layar monitor'),
(6, 1, 5, 16, NULL, NULL, 1, 75000.00, 'Pengukuran pada area inspeksi produk');
INSERT INTO equipment_requisitions (EquipmentRequisitionID, EquipmentRequisitionCode, SamplingPlanID, EquipmentRequisitionDate, EquipmentRequisitionRequestedBy, EquipmentRequisitionApprovedBy, EquipmentRequisitionApprovalDate, EquipmentRequisitionSamplingDate, EquipmentRequisitionReturnDate, EquipmentRequisitionStatus, EquipmentRequisitionRemarks) VALUES
(1, 'REQ-LX-2024-001', 1, '2024-01-15 10:30:00', 4, 1, '2024-01-16 09:15:00', '2024-01-20', '2024-01-21', 'approved', 'Peralatan untuk monitoring pencahayaan PT. ITN');
INSERT INTO equipment_requisition_items (EquipmentRequisitionItemID, EquipmentRequisitionID, MasterEquipmentID, EquipmentRequisitionItemQuantity, EquipmentRequisitionItemIssuedQuantity, EquipmentRequisitionItemIssuedBy, EquipmentRequisitionItemIssuedDate, EquipmentRequisitionItemReturnedQuantity, EquipmentRequisitionItemReturnedCondition, EquipmentRequisitionItemReturnedDate, EquipmentRequisitionItemVerifiedBy, EquipmentRequisitionItemRemarks) VALUES
(1, 1, 16, 1, 1, 8, '2024-01-19 15:00:00', NULL, NULL, NULL, NULL, 'Lux meter utama'),
(2, 1, 17, 1, 1, 8, '2024-01-19 15:00:00', NULL, NULL, NULL, NULL, 'Lux meter backup'),
(3, 1, 18, 1, 1, 8, '2024-01-19 15:00:00', NULL, NULL, NULL, NULL, 'Untuk pengukuran luminansi layar monitor'),
(4, 1, 20, 1, 1, 8, '2024-01-19 15:00:00', NULL, NULL, NULL, NULL, 'Peralatan penunjang grid mapping');
INSERT INTO measurement_results_light (MeasurementResultLightID, SampleID, MeasurementResultLightDate, MeasurementResultLightTime, MasterParameterID, MeasurementResultLightValue, MeasurementResultLightUnit, MeasurementResultLightPoint, MeasurementResultLightWeatherCondition, MeasurementResultLightSource, MeasurementResultLightAmbientCondition, MasterQualityStandardID, MeasurementResultLightIsCompliant, MeasurementResultLightAnalystID, MeasurementResultLightVerifiedBy, MeasurementResultLightVerificationDate, MeasurementResultLightRemarks) VALUES
(1, 1, '2024-01-20', '09:15:00', 16, 325.50, 'Lux', 'Titik 1 (Depan)', 'Cerah', 'Lampu fluorescent + Cahaya alami', 'Tirai jendela dibuka 50%', 11, TRUE, 3, 2, '2024-01-21 10:00:00', 'Memenuhi standar Permenaker'),
(2, 1, '2024-01-20', '09:20:00', 16, 310.20, 'Lux', 'Titik 2 (Tengah)', 'Cerah', 'Lampu fluorescent + Cahaya alami', 'Tirai jendela dibuka 50%', 11, TRUE, 3, 2, '2024-01-21 10:00:00', 'Memenuhi standar Permenaker'),
(3, 1, '2024-01-20', '09:25:00', 16, 290.80, 'Lux', 'Titik 3 (Belakang)', 'Cerah', 'Lampu fluorescent + Cahaya alami', 'Tirai jendela dibuka 50%', 11, TRUE, 3, 2, '2024-01-21 10:00:00', 'Memenuhi standar Permenaker'),
(4, 2, '2024-01-20', '10:45:00', 16, 180.40, 'Lux', 'Titik 1 (Pintu Masuk)', 'Cerah', 'Lampu LED', 'Indoor, pencahayaan buatan', 10, TRUE, 3, 2, '2024-01-21 10:15:00', 'Memenuhi standar untuk pekerjaan kasar'),
(5, 2, '2024-01-20', '10:50:00', 16, 165.30, 'Lux', 'Titik 2 (Area Kontrol)', 'Cerah', 'Lampu LED', 'Indoor, pencahayaan buatan', 10, TRUE, 3, 2, '2024-01-21 10:15:00', 'Memenuhi standar untuk pekerjaan kasar'),
(6, 4, '2024-01-20', '13:15:00', 17, 120.50, 'cd/m²', 'Layar Monitor 1', 'Cerah', 'Layar Monitor', 'Indoor, pencahayaan buatan dan alami', 18, TRUE, 3, 2, '2024-01-21 10:30:00', 'Luminansi layar dalam batas aman');
Reference in New Issue View Git Blame Copy Permalink