90 lines
6.0 KiB
SQL
90 lines
6.0 KiB
SQL
-- =============================================================================
|
|
-- DDL: qr_printout (final - UUID only, PDF di dedicated server via Golang)
|
|
--
|
|
-- Upload Status:
|
|
-- 'pending' : belum diupload
|
|
-- 'uploaded' : berhasil diupload ke dedicated server
|
|
-- 'failed' : gagal upload, masih bisa retry (RetryCount < 3)
|
|
-- 'failed_permanent' : gagal permanen (RetryCount >= 3), tidak di-retry otomatis
|
|
--
|
|
-- Alur normal:
|
|
-- 1. PHP: saveQRPrintout() -> insert row (verifyURL = URL final PDF di dedicated server)
|
|
-- 2. PHP: saveTempPDF($uuid, $pdf, $publicURL) -> simpan PDF sementara
|
|
-- QR_PrintOutReportURL = $publicURL (URL yg bisa di-fetch Golang via HTTP)
|
|
-- QR_PrintOutTempFilePath = path lokal file
|
|
-- 3. Golang: getPendingUploads() -> ambil rows: ReportURL (sumber) + VerifyURL (tujuan)
|
|
-- 4. Golang: download PDF dari QR_PrintOutReportURL
|
|
-- upload ke dedicated server di path QR_PrintOutVerifyURL
|
|
-- - Berhasil -> callback confirmUpload($uuid) -> status = uploaded
|
|
-- - Gagal -> callback incrementRetry($uuid) -> retry++, jika retry >= 3: failed_permanent
|
|
--
|
|
-- Alur re-upload (file expired di dedicated server / revisi laporan):
|
|
-- 1. Admin: panggil requestReUpload($uuid) -> reset status=pending, retry=0, url=''
|
|
-- 2. PHP: saveTempPDF() -> siapkan PDF baru
|
|
-- 3. Golang: ambil & upload ulang
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS `qr_printout` (
|
|
`QR_PrintOutID` int(11) NOT NULL AUTO_INCREMENT,
|
|
`QR_PrintOutT_OrderHeaderID` int(11) NOT NULL DEFAULT 0
|
|
COMMENT 'FK ke t_orderheader',
|
|
`QR_PrintOutGroup_ResultID` int(11) NOT NULL DEFAULT 0
|
|
COMMENT 'FK ke m_groupresult (opsional)',
|
|
`QR_PrintOutT_TestID` int(11) NOT NULL DEFAULT 0
|
|
COMMENT 'FK ke m_test (opsional)',
|
|
`QR_PrintOutGroup_ResultName` varchar(250) NOT NULL DEFAULT ''
|
|
COMMENT 'Label group hasil cetak (snapshot)',
|
|
`QR_PrintOutUUID` varchar(36) NOT NULL DEFAULT ''
|
|
COMMENT 'UUID v4 unik per sesi cetak',
|
|
`QR_PrintOutVerifyURL` varchar(500) NOT NULL DEFAULT ''
|
|
COMMENT 'URL QR Code & tujuan upload: https://ds.com/files/{uuid}.pdf (Golang upload ke sini, pasien scan QR buka ini)',
|
|
`QR_PrintOutReportURL` varchar(500) NOT NULL DEFAULT ''
|
|
COMMENT 'URL sumber PDF di PHP app server (Golang HTTP-fetch dari sini untuk diupload ke dedicated server)',
|
|
`QR_PrintOutTempFilePath` varchar(500) NOT NULL DEFAULT ''
|
|
COMMENT 'Path absolut file PDF sementara di PHP server (untuk diambil Golang)',
|
|
`QR_PrintOutUploadStatus` enum('pending','uploaded','failed','failed_permanent') NOT NULL DEFAULT 'pending'
|
|
COMMENT 'Status upload: pending|uploaded|failed (retry<3)|failed_permanent (retry>=3)',
|
|
`QR_PrintOutRetryCount` int(11) NOT NULL DEFAULT 0
|
|
COMMENT 'Berapa kali sudah dicoba upload, maksimal 3',
|
|
`QR_PrintOutLastRetryAt` datetime DEFAULT NULL
|
|
COMMENT 'Waktu percobaan upload terakhir yang gagal',
|
|
`QR_PrintOutUploadedAt` datetime DEFAULT NULL
|
|
COMMENT 'Waktu upload PDF ke dedicated server berhasil',
|
|
`QR_PrintOutCreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
COMMENT 'Waktu token QR dibuat',
|
|
`QR_PrintOutCreatedByUserID` int(11) NOT NULL DEFAULT 0
|
|
COMMENT 'UserID yang mencetak laporan',
|
|
`QR_PrintOutIsActive` tinyint(1) NOT NULL DEFAULT 1
|
|
COMMENT '1=aktif, 0=dinonaktifkan (mis. hasil direvisi)',
|
|
PRIMARY KEY (`QR_PrintOutID`),
|
|
UNIQUE KEY `uq_qr_uuid` (`QR_PrintOutUUID`),
|
|
KEY `idx_order_header` (`QR_PrintOutT_OrderHeaderID`),
|
|
KEY `idx_upload_status` (`QR_PrintOutUploadStatus`),
|
|
KEY `idx_retry` (`QR_PrintOutUploadStatus`, `QR_PrintOutRetryCount`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
COMMENT='Token QR Code untuk verifikasi keaslian laporan hasil laboratorium';
|
|
|
|
|
|
-- =============================================================================
|
|
-- ALTER TABLE untuk migrasi dari DDL lama (MySQL 8+ dengan IF NOT EXISTS)
|
|
-- =============================================================================
|
|
|
|
ALTER TABLE `qr_printout`
|
|
MODIFY COLUMN `QR_PrintOutUUID` varchar(36) NOT NULL DEFAULT '',
|
|
MODIFY COLUMN `QR_PrintOutVerifyURL` varchar(500) NOT NULL DEFAULT '',
|
|
MODIFY COLUMN `QR_PrintOutUploadStatus`
|
|
enum('pending','uploaded','failed','failed_permanent') NOT NULL DEFAULT 'pending',
|
|
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutReportURL` varchar(500) NOT NULL DEFAULT '' COMMENT 'URL sumber PDF di PHP server (Golang fetch dari sini)' AFTER `QR_PrintOutVerifyURL`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutTempFilePath` varchar(500) NOT NULL DEFAULT '' COMMENT 'Path file PDF sementara' AFTER `QR_PrintOutReportURL`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutUploadStatus` enum('pending','uploaded','failed','failed_permanent') NOT NULL DEFAULT 'pending' AFTER `QR_PrintOutTempFilePath`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutRetryCount` int(11) NOT NULL DEFAULT 0 COMMENT 'Jumlah percobaan upload' AFTER `QR_PrintOutUploadStatus`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutLastRetryAt` datetime DEFAULT NULL COMMENT 'Waktu retry terakhir' AFTER `QR_PrintOutRetryCount`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutUploadedAt` datetime DEFAULT NULL COMMENT 'Waktu upload berhasil' AFTER `QR_PrintOutLastRetryAt`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutCreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `QR_PrintOutUploadedAt`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutCreatedByUserID` int(11) NOT NULL DEFAULT 0 AFTER `QR_PrintOutCreatedAt`,
|
|
ADD COLUMN IF NOT EXISTS `QR_PrintOutIsActive` tinyint(1) NOT NULL DEFAULT 1 AFTER `QR_PrintOutCreatedByUserID`,
|
|
|
|
ADD KEY IF NOT EXISTS `idx_upload_status` (`QR_PrintOutUploadStatus`),
|
|
ADD KEY IF NOT EXISTS `idx_retry` (`QR_PrintOutUploadStatus`, `QR_PrintOutRetryCount`);
|