BE_CPONE/docs_cpone_sp_related_to_cpone_dashboard.md

SP di DB cpone yang Berhubungan dengan cpone_dashboard

Dokumen ini hanya berisi stored procedure yang:

1. berada di database/schema cpone
2. memiliki relasi langsung ke database cpone_dashboard

Tanggal cek:

• 2026-06-17

Server:

• one@devcpone.aplikasi.web.id

Daftar SP

Stored procedure di schema cpone yang terdeteksi menyentuh cpone_dashboard:

1. cpone.sp_generate_dummy_mcu_transactions
2. cpone.sp_refresh_mcu_participant_daily_by_mcu_date
3. cpone.sp_refresh_mcu_participant_daily_by_preregister
4. cpone.sp_sync_published_mcu_dashboard_by_mgm_mcuid
5. cpone.sp_sync_published_mcu_dashboard_by_orderheaderid
6. cpone.sp_upsert_mcu_patient_by_mgm_mcuid
7. cpone.sp_upsert_mcu_patient_by_preregister_id
8. cpone.sp_upsert_mcu_project_by_mgm_mcuid

Ringkasan Fungsi

SP	Hubungan ke cpone_dashboard
sp_generate_dummy_mcu_transactions	Mengisi dummy transaksi lalu sync mcu_patient, participant daily, mcu_station_progress, dan mcu_checkinout
sp_refresh_mcu_participant_daily_by_mcu_date	Rebuild data mcu_patient_schedule, mcu_participant_daily, dan mcu_participant_daily_details per MCU + tanggal
sp_refresh_mcu_participant_daily_by_preregister	Refresh participant daily untuk satu preregister
sp_sync_published_mcu_dashboard_by_mgm_mcuid	Sync data published_mcu_dashboard ke cpone_dashboard.published_mcu_dashboard_sync berdasarkan Mgm_McuID
sp_sync_published_mcu_dashboard_by_orderheaderid	Sync data published_mcu_dashboard ke cpone_dashboard.published_mcu_dashboard_sync berdasarkan T_OrderHeaderID
sp_upsert_mcu_patient_by_mgm_mcuid	Upsert data patient dan packet ke cpone_dashboard.mcu_patient dan mcu_patient_packet
sp_upsert_mcu_patient_by_preregister_id	Upsert satu patient preregister ke cpone_dashboard.mcu_patient dan mcu_patient_packet
sp_upsert_mcu_project_by_mgm_mcuid	Upsert data project MCU ke cpone_dashboard.mcu_project

Tabel cpone_dashboard yang Disentuh

sp_generate_dummy_mcu_transactions

Menyentuh:

• cpone_dashboard.mcu_station_progress
• cpone_dashboard.mcu_checkinout

Juga memanggil:

• cpone.sp_upsert_mcu_patient_by_preregister_id
• cpone.sp_refresh_mcu_participant_daily_by_mcu_date

sp_refresh_mcu_participant_daily_by_mcu_date

Menyentuh:

• cpone_dashboard.mcu_participant_daily_details
• cpone_dashboard.mcu_participant_daily
• cpone_dashboard.mcu_patient_schedule
• cpone_dashboard.mcu_patient
• cpone_dashboard.mcu_checkinout

sp_refresh_mcu_participant_daily_by_preregister

Menyentuh:

• cpone_dashboard.mcu_patient
• cpone_dashboard.mcu_patient_schedule
• cpone_dashboard.mcu_participant_daily
• cpone_dashboard.mcu_participant_daily_details
• cpone_dashboard.mcu_checkinout

sp_sync_published_mcu_dashboard_by_mgm_mcuid

Menyentuh:

• cpone_dashboard.published_mcu_dashboard_sync

sp_sync_published_mcu_dashboard_by_orderheaderid

Menyentuh:

• cpone_dashboard.published_mcu_dashboard_sync

sp_upsert_mcu_patient_by_mgm_mcuid

Menyentuh:

• cpone_dashboard.mcu_patient
• cpone_dashboard.mcu_patient_packet

sp_upsert_mcu_patient_by_preregister_id

Menyentuh:

• cpone_dashboard.mcu_patient
• cpone_dashboard.mcu_patient_packet

sp_upsert_mcu_project_by_mgm_mcuid

Menyentuh:

• cpone_dashboard.mcu_project

Catatan Penting

• Dokumen ini sengaja tidak memasukkan SP yang schema-nya cpone_dashboard.
• Fokusnya hanya SP yang berada di db cpone tetapi query atau write ke objek di cpone_dashboard.
• Dari sisi aplikasi, inilah kelompok SP yang paling relevan untuk flow sync antar db.

Trigger di Schema cpone yang Terkait

Trigger cpone yang terdeteksi punya hubungan dengan cpone_dashboard atau memanggil SP di atas:

1. cpone.trg_mgm_mcu_ai_sync_mcu_project
2. cpone.trg_mgm_mcu_au_sync_mcu_project
3. cpone.trg_published_mcu_dashboard_sync_ins
4. cpone.trg_published_mcu_dashboard_sync_upd

Ringkasan Trigger

Trigger	Table	Relasi
trg_mgm_mcu_ai_sync_mcu_project	cpone.mgm_mcu	CALL cpone.sp_upsert_mcu_project_by_mgm_mcuid(NEW.Mgm_McuID)
trg_mgm_mcu_au_sync_mcu_project	cpone.mgm_mcu	CALL cpone.sp_upsert_mcu_project_by_mgm_mcuid(NEW.Mgm_McuID)
trg_published_mcu_dashboard_sync_ins	cpone.published_mcu_dashboard	Direct insert/update ke cpone_dashboard.published_mcu_dashboard_sync
trg_published_mcu_dashboard_sync_upd	cpone.published_mcu_dashboard	Direct insert/update ke cpone_dashboard.published_mcu_dashboard_sync

Trigger cpone yang Memanggil SP dari Daftar Ini

Yang benar-benar memanggil SP dari daftar SP cpone di atas:

• trg_mgm_mcu_ai_sync_mcu_project -> cpone.sp_upsert_mcu_project_by_mgm_mcuid
• trg_mgm_mcu_au_sync_mcu_project -> cpone.sp_upsert_mcu_project_by_mgm_mcuid

SP cpone yang Tidak Ditemukan Dipanggil Trigger cpone

SP berikut tidak ditemukan dipanggil langsung oleh trigger di schema cpone:

• cpone.sp_generate_dummy_mcu_transactions
• cpone.sp_refresh_mcu_participant_daily_by_mcu_date
• cpone.sp_refresh_mcu_participant_daily_by_preregister
• cpone.sp_sync_published_mcu_dashboard_by_mgm_mcuid
• cpone.sp_sync_published_mcu_dashboard_by_orderheaderid
• cpone.sp_upsert_mcu_patient_by_mgm_mcuid
• cpone.sp_upsert_mcu_patient_by_preregister_id

Dump Ringkas SP

1. cpone.sp_generate_dummy_mcu_transactions

DELIMITER $$
CREATE PROCEDURE `sp_generate_dummy_mcu_transactions`(
  IN p_preregister_id INT,
  IN p_mcu_id INT,
  IN p_schedule_date DATE,
  IN p_user_id INT
)
BEGIN
  ...
  CALL cpone.sp_upsert_mcu_patient_by_preregister_id(v_preregister_id);
  CALL cpone.sp_refresh_mcu_participant_daily_by_mcu_date(v_mcu_id, v_effective_date);

  INSERT INTO cpone_dashboard.mcu_station_progress (...);
  INSERT INTO cpone_dashboard.mcu_checkinout (...);
  ...
END$$
DELIMITER ;

2. cpone.sp_refresh_mcu_participant_daily_by_mcu_date

DELIMITER $$
CREATE PROCEDURE `sp_refresh_mcu_participant_daily_by_mcu_date`(
  IN p_mgm_mcuid INT,
  IN p_schedule_date DATE
)
BEGIN
  DELETE d
  FROM cpone_dashboard.mcu_participant_daily_details d
  INNER JOIN cpone_dashboard.mcu_participant_daily h
    ON h.Mcu_ParticipantDailyID = d.Mcu_ParticipantDailyDetailsParticipantDailyID
  WHERE h.Mcu_ParticipantDailyMcuID = p_mgm_mcuid
    AND h.Mcu_ParticipantDailyDate = p_schedule_date;

  DELETE FROM cpone_dashboard.mcu_participant_daily
  WHERE Mcu_ParticipantDailyMcuID = p_mgm_mcuid
    AND Mcu_ParticipantDailyDate = p_schedule_date;

  DELETE s
  FROM cpone_dashboard.mcu_patient_schedule s
  ...

  INSERT INTO cpone_dashboard.mcu_patient_schedule (...);
  INSERT INTO cpone_dashboard.mcu_participant_daily (...);
  INSERT INTO cpone_dashboard.mcu_participant_daily_details (...);
  ...
END$$
DELIMITER ;

3. cpone.sp_refresh_mcu_participant_daily_by_preregister

DELIMITER $$
CREATE PROCEDURE `sp_refresh_mcu_participant_daily_by_preregister`(
  IN p_preregister_id INT
)
BEGIN
  SELECT Mcu_PatientID
  INTO v_mcu_patient_id
  FROM cpone_dashboard.mcu_patient
  WHERE Mcu_PatientPreregisterID = p_preregister_id
    AND Mcu_PatientMcuID = v_mgm_mcuid
  LIMIT 1;

  INSERT INTO cpone_dashboard.mcu_patient_schedule (...);
  INSERT INTO cpone_dashboard.mcu_participant_daily (...);
  DELETE FROM cpone_dashboard.mcu_participant_daily_details ...;
  INSERT INTO cpone_dashboard.mcu_participant_daily_details (...);
  ...
END$$
DELIMITER ;

4. cpone.sp_sync_published_mcu_dashboard_by_mgm_mcuid

DELIMITER $$
CREATE PROCEDURE `sp_sync_published_mcu_dashboard_by_mgm_mcuid`(IN p_mgm_mcuid INT)
BEGIN
  INSERT INTO cpone_dashboard.published_mcu_dashboard_sync (
    Published_McuDasboardID,
    Published_McuDasboardT_OrderHeaderID,
    Published_McuDasboardStatus,
    Published_McuDasboardFileUrl,
    Published_McuDasboardIsActive,
    Published_McuDasboardCreated,
    Published_McuDasboardCreatedUserID,
    Published_McuDasboardLastUpdated,
    Published_McuDasboardLastUpdatedUserID
  )
  SELECT ...
  FROM cpone.published_mcu_dashboard pmd
  ...
  ON DUPLICATE KEY UPDATE ...;
END$$
DELIMITER ;

5. cpone.sp_sync_published_mcu_dashboard_by_orderheaderid

DELIMITER $$
CREATE PROCEDURE `sp_sync_published_mcu_dashboard_by_orderheaderid`(IN p_t_orderheaderid INT)
BEGIN
  INSERT INTO cpone_dashboard.published_mcu_dashboard_sync (
    Published_McuDasboardID,
    Published_McuDasboardT_OrderHeaderID,
    Published_McuDasboardStatus,
    Published_McuDasboardFileUrl,
    Published_McuDasboardIsActive,
    Published_McuDasboardCreated,
    Published_McuDasboardCreatedUserID,
    Published_McuDasboardLastUpdated,
    Published_McuDasboardLastUpdatedUserID
  )
  SELECT ...
  FROM cpone.published_mcu_dashboard pmd
  WHERE pmd.Published_McuDasboardT_OrderHeaderID = p_t_orderheaderid
  ON DUPLICATE KEY UPDATE ...;
END$$
DELIMITER ;

6. cpone.sp_upsert_mcu_patient_by_mgm_mcuid

DELIMITER $$
CREATE PROCEDURE `sp_upsert_mcu_patient_by_mgm_mcuid`(IN `p_mgm_mcuid` int)
BEGIN
  INSERT INTO cpone_dashboard.mcu_patient (
    Mcu_PatientPreregisterID,
    Mcu_PatientMcuID,
    Mcu_PatientName,
    Mcu_PatientNIP,
    Mcu_PatientGender,
    Mcu_PatientDOB,
    Mcu_PatientDepartment,
    Mcu_PatientDivision,
    Mcu_PatientPosisi,
    Mcu_PatientOrders,
    Mcu_PatientPemeriksaan,
    Mcu_PatientIsRegistered,
    Mcu_PatientOrderID,
    Mcu_PatientIsActive,
    Mcu_PatientSyncedAt
  )
  SELECT ...
  ON DUPLICATE KEY UPDATE ...;

  DELETE ppk
  FROM cpone_dashboard.mcu_patient_packet ppk
  INNER JOIN cpone_dashboard.mcu_patient mp
    ON mp.Mcu_PatientID = ppk.Mcu_PatientPacketMcu_PatientID
  WHERE mp.Mcu_PatientMcuID = p_mgm_mcuid;

  INSERT INTO cpone_dashboard.mcu_patient_packet (...);
END$$
DELIMITER ;

7. cpone.sp_upsert_mcu_patient_by_preregister_id

DELIMITER $$
CREATE PROCEDURE `sp_upsert_mcu_patient_by_preregister_id`(IN `p_preregister_id` int)
BEGIN
  INSERT INTO cpone_dashboard.mcu_patient (... )
  SELECT ...
  ON DUPLICATE KEY UPDATE ...;

  DELETE FROM cpone_dashboard.mcu_patient_packet
  WHERE Mcu_PatientPacketPreregisterID = p_preregister_id;

  INSERT INTO cpone_dashboard.mcu_patient_packet (...);
END$$
DELIMITER ;

8. cpone.sp_upsert_mcu_project_by_mgm_mcuid

DELIMITER $$
CREATE PROCEDURE `sp_upsert_mcu_project_by_mgm_mcuid`(IN `p_mgm_mcuid` int)
BEGIN
  INSERT INTO cpone_dashboard.mcu_project (
    Mcu_ProjectMcuID,
    Mcu_ProjectCorporateID,
    Mcu_ProjectCorporateName,
    Mcu_ProjectNumber,
    Mcu_ProjectLabel,
    Mcu_ProjectBranchID,
    Mcu_ProjectStartDate,
    Mcu_ProjectEndDate,
    Mcu_ProjectIsActive,
    Mcu_ProjectTotalParticipant,
    Mcu_ProjectSyncedAt
  )
  SELECT ...
  ON DUPLICATE KEY UPDATE ...;
END$$
DELIMITER ;

Dump Ringkas Trigger cpone

1. cpone.trg_mgm_mcu_ai_sync_mcu_project

DELIMITER $$
CREATE TRIGGER `trg_mgm_mcu_ai_sync_mcu_project`
AFTER INSERT ON `mgm_mcu`
FOR EACH ROW
BEGIN
  CALL cpone.sp_upsert_mcu_project_by_mgm_mcuid(NEW.Mgm_McuID);
END$$
DELIMITER ;

2. cpone.trg_mgm_mcu_au_sync_mcu_project

DELIMITER $$
CREATE TRIGGER `trg_mgm_mcu_au_sync_mcu_project`
AFTER UPDATE ON `mgm_mcu`
FOR EACH ROW
BEGIN
  CALL cpone.sp_upsert_mcu_project_by_mgm_mcuid(NEW.Mgm_McuID);
END$$
DELIMITER ;

3. cpone.trg_published_mcu_dashboard_sync_ins

DELIMITER $$
CREATE TRIGGER `trg_published_mcu_dashboard_sync_ins`
AFTER INSERT ON `published_mcu_dashboard`
FOR EACH ROW
INSERT INTO cpone_dashboard.published_mcu_dashboard_sync (
  Published_McuDasboardID,
  Published_McuDasboardT_OrderHeaderID,
  Published_McuDasboardStatus,
  Published_McuDasboardFileUrl,
  Published_McuDasboardIsActive,
  Published_McuDasboardCreated,
  Published_McuDasboardCreatedUserID,
  Published_McuDasboardLastUpdated,
  Published_McuDasboardLastUpdatedUserID
)
VALUES (...)
ON DUPLICATE KEY UPDATE ...$$
DELIMITER ;

4. cpone.trg_published_mcu_dashboard_sync_upd

DELIMITER $$
CREATE TRIGGER `trg_published_mcu_dashboard_sync_upd`
AFTER UPDATE ON `published_mcu_dashboard`
FOR EACH ROW
INSERT INTO cpone_dashboard.published_mcu_dashboard_sync (
  Published_McuDasboardID,
  Published_McuDasboardT_OrderHeaderID,
  Published_McuDasboardStatus,
  Published_McuDasboardFileUrl,
  Published_McuDasboardIsActive,
  Published_McuDasboardCreated,
  Published_McuDasboardCreatedUserID,
  Published_McuDasboardLastUpdated,
  Published_McuDasboardLastUpdatedUserID
)
VALUES (...)
ON DUPLICATE KEY UPDATE ...$$
DELIMITER ;