BE_CPONE/docs_cpone_dashboard_sp_and_trigger_check.md

SP cpone_dashboard dan Cek Trigger di devcpone

Dokumen ini mencatat stored procedure yang benar-benar berada di schema cpone_dashboard pada server devcpone, beserta hasil pengecekan apakah ada trigger yang memanggil SP-SP tersebut secara langsung.

Tanggal cek:

• 2026-06-17

Server:

• one@devcpone.aplikasi.web.id

Daftar SP di Schema cpone_dashboard

Stored procedure yang ditemukan:

1. cpone_dashboard.sp_assign_user_project
2. cpone_dashboard.sp_generate_dashboard_data
3. cpone_dashboard.sp_generate_patient_required_stations
4. cpone_dashboard.sp_insert_dashboard_user
5. cpone_dashboard.sp_refresh_mcu_participant_daily_by_mcu
6. cpone_dashboard.sp_remove_user_project
7. cpone_dashboard.sp_reset_dashboard_user_password

Ringkasan Fungsi SP

SP	Fungsi singkat
sp_assign_user_project	Assign project MCU ke user dashboard
sp_generate_dashboard_data	Generate data dashboard dasar seperti mcu_patient, mcu_patient_schedule, dan mcu_patient_resume_status
sp_generate_patient_required_stations	Generate kebutuhan station per patient ke mcu_patient_required_station
sp_insert_dashboard_user	Insert user dashboard baru
sp_refresh_mcu_participant_daily_by_mcu	Loop per tanggal checkin schedule lalu panggil refresh participant daily
sp_remove_user_project	Nonaktifkan hubungan user dengan project MCU
sp_reset_dashboard_user_password	Reset password user dashboard

Cek Trigger yang Memakai SP-SP Tersebut

Pengecekan dilakukan terhadap information_schema.TRIGGERS dengan mencari ACTION_STATEMENT yang mengandung nama SP berikut:

• sp_assign_user_project
• sp_generate_dashboard_data
• sp_generate_patient_required_stations
• sp_insert_dashboard_user
• sp_refresh_mcu_participant_daily_by_mcu
• sp_remove_user_project
• sp_reset_dashboard_user_password

Hasil:

• Tidak ditemukan trigger di devcpone yang memanggil salah satu dari 7 SP cpone_dashboard tersebut secara langsung.

Artinya:

• Saat ini 7 SP ini tampaknya dipakai lewat layer aplikasi atau dipanggil manual, bukan lewat trigger database.

Dump Ringkas SHOW CREATE PROCEDURE

1. cpone_dashboard.sp_assign_user_project

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_assign_user_project`(IN `p_username` varchar(50), IN `p_mcu_id` int)
BEGIN
    DECLARE v_user_id INT;

    SELECT User_ID INTO v_user_id
    FROM dashboard_user
    WHERE User_Username = p_username AND User_IsActive = 'Y'
    LIMIT 1;

    IF v_user_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User tidak ditemukan atau tidak aktif';
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM mcu_project WHERE Mcu_ProjectMcuID = p_mcu_id
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'mcu_id tidak ditemukan di mcu_project';
    END IF;

    INSERT INTO dashboard_user_project (UserProj_UserID, UserProj_McuID, UserProj_IsActive)
    VALUES (v_user_id, p_mcu_id, 'Y')
    ON DUPLICATE KEY UPDATE
        UserProj_IsActive  = 'Y',
        UserProj_UpdatedAt = NOW();
END

2. cpone_dashboard.sp_generate_dashboard_data

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_generate_dashboard_data`(IN `p_mcu_id` int)
BEGIN
    INSERT INTO mcu_patient (
        Mcu_PatientPreregisterID, Mcu_PatientMcuID, Mcu_PatientName,
        Mcu_PatientNIP, Mcu_PatientGender, Mcu_PatientDOB, Mcu_PatientAge,
        Mcu_PatientDepartment, Mcu_PatientDivision, Mcu_PatientPosisi,
        Mcu_PatientIsRegistered, Mcu_PatientOrderID, Mcu_PatientIsActive,
        Mcu_PatientSyncedAt
    )
    SELECT
        p.Mcu_PreregisterPatientsID, p.Mcu_PreregisterPatientsMgm_McuID,
        p.Mcu_PreregisterPatientsPatientName, p.Mcu_PreregisterPatientsNIP,
        p.Mcu_PreregisterPatientsGender, p.Mcu_PreregisterPatientsDOB,
        CAST(o.T_OrderHeaderM_PatientAge AS UNSIGNED),
        p.Mcu_PreregisterPatientsDepartment, p.Mcu_PreregisterPatientsDivisi,
        p.Mcu_PreregisterPatientsPosisi,
        p.Mcu_PreregisterPatientsIsRegistered, p.Mcu_PreregisterPatientsT_OrderHeaderID,
        p.Mcu_PreregisterPatientsIsActive, NOW()
    FROM cpone.mcu_preregister_patients p
    LEFT JOIN cpone.t_orderheader o ON o.T_OrderHeaderID = p.Mcu_PreregisterPatientsT_OrderHeaderID
    WHERE p.Mcu_PreregisterPatientsMgm_McuID = p_mcu_id
    AND p.Mcu_PreregisterPatientsIsActive = 'Y'
    ON DUPLICATE KEY UPDATE
        Mcu_PatientName         = VALUES(Mcu_PatientName),
        Mcu_PatientAge          = VALUES(Mcu_PatientAge),
        Mcu_PatientIsRegistered = VALUES(Mcu_PatientIsRegistered),
        Mcu_PatientOrderID      = VALUES(Mcu_PatientOrderID),
        Mcu_PatientSyncedAt     = NOW();

    INSERT INTO mcu_patient_schedule (
        Mcu_PatientSchedulePreregisterID, Mcu_PatientScheduleDate,
        Mcu_PatientScheduleIsActive, Mcu_PatientScheduleSyncedAt
    )
    SELECT
        p.Mcu_PreregisterPatientsID, DATE(o.T_OrderHeaderDate), 'Y', NOW()
    FROM cpone.mcu_preregister_patients p
    JOIN cpone.t_orderheader o ON o.T_OrderHeaderID = p.Mcu_PreregisterPatientsT_OrderHeaderID
    WHERE p.Mcu_PreregisterPatientsMgm_McuID = p_mcu_id
    AND p.Mcu_PreregisterPatientsT_OrderHeaderID > 0
    AND p.Mcu_PreregisterPatientsIsActive = 'Y'
    ON DUPLICATE KEY UPDATE
        Mcu_PatientScheduleSyncedAt = NOW();

    INSERT INTO mcu_patient_resume_status (
        Mcu_PatientResumeStatusPreregisterID, Mcu_PatientResumeStatusMcuID,
        Mcu_PatientResumeStatusStatus, Mcu_PatientResumeStatusValidated,
        Mcu_PatientResumeStatusPublished, Mcu_PatientResumeSyncedAt
    )
    SELECT
        p.Mcu_PreregisterPatientsID,
        p_mcu_id,
        r.Mcu_ResumeStatus,
        r.Mcu_ResumeValidation,
        'N',
        NOW()
    FROM cpone.mcu_preregister_patients p
    JOIN cpone.t_orderheader o ON o.T_OrderHeaderID = p.Mcu_PreregisterPatientsT_OrderHeaderID
    LEFT JOIN cpone.mcu_resume r ON r.Mcu_ResumeT_OrderHeaderID = o.T_OrderHeaderID AND r.Mcu_ResumeIsActive = 'Y'
    WHERE p.Mcu_PreregisterPatientsMgm_McuID = p_mcu_id
    AND p.Mcu_PreregisterPatientsT_OrderHeaderID > 0
    AND p.Mcu_PreregisterPatientsIsActive = 'Y'
    ON DUPLICATE KEY UPDATE
        Mcu_PatientResumeStatusStatus = VALUES(Mcu_PatientResumeStatusStatus),
        Mcu_PatientResumeStatusValidated = VALUES(Mcu_PatientResumeStatusValidated),
        Mcu_PatientResumeSyncedAt = NOW();
END

3. cpone_dashboard.sp_generate_patient_required_stations

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_generate_patient_required_stations`(IN `p_mcu_id` int)
BEGIN
  DECLARE v_rows INT DEFAULT 0;

  DELETE FROM mcu_patient_required_station WHERE mcu_id = p_mcu_id;

  INSERT INTO mcu_patient_required_station
    (mcu_id, preregister_id, order_header_id, location_id, sample_station_id, station_name)
  SELECT
    p_mcu_id,
    preg.Mcu_PreregisterPatientsID,
    preg.Mcu_PreregisterPatientsT_OrderHeaderID,
    tol.T_OrderLocationM_LocationID,
    tol.T_OrderLocationT_SampleStationID,
    ss.T_SampleStationName
  FROM cpone_dashboard.mcu_patient mp
  JOIN cpone.mcu_preregister_patients preg
    ON preg.Mcu_PreregisterPatientsID = mp.Mcu_PatientPreregisterID
  JOIN cpone.t_order_location tol
    ON tol.T_OrderLocationT_OrderHeaderID = preg.Mcu_PreregisterPatientsT_OrderHeaderID
   AND tol.T_OrderLocationIsActive = 'Y'
  JOIN cpone.t_samplestation ss
    ON ss.T_SampleStationID = tol.T_OrderLocationT_SampleStationID
   AND ss.T_SampleStationIsActive = 'Y'
  WHERE mp.Mcu_PatientMcuID = p_mcu_id
    AND mp.Mcu_PatientIsActive = 'Y'
  ON DUPLICATE KEY UPDATE
    order_header_id   = VALUES(order_header_id),
    sample_station_id = VALUES(sample_station_id),
    station_name      = VALUES(station_name),
    updated_at        = CURRENT_TIMESTAMP;

  SET v_rows = ROW_COUNT();
  SELECT v_rows AS rows_affected, p_mcu_id AS mcu_id;
END

4. cpone_dashboard.sp_insert_dashboard_user

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_insert_dashboard_user`(IN `p_username` varchar(50), IN `p_password` varchar(255), IN `p_display_name` varchar(100))
BEGIN
    DECLARE v_salt VARCHAR(36);
    DECLARE v_hash VARCHAR(64);

    IF p_username IS NULL OR TRIM(p_username) = '' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Username tidak boleh kosong';
    END IF;

    IF p_password IS NULL OR TRIM(p_password) = '' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Password tidak boleh kosong';
    END IF;

    IF LENGTH(p_password) < 6 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Password minimal 6 karakter';
    END IF;

    IF EXISTS (SELECT 1 FROM dashboard_user WHERE User_Username = TRIM(p_username)) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Username sudah terdaftar';
    END IF;

    SET v_salt = UUID();
    SET v_hash = SHA2(CONCAT(v_salt, ':', p_password), 256);

    INSERT INTO dashboard_user (User_Username, User_Password, User_Salt, User_DisplayName, User_IsActive)
    VALUES (TRIM(p_username), v_hash, v_salt, p_display_name, 'Y');
END

5. cpone_dashboard.sp_refresh_mcu_participant_daily_by_mcu

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_refresh_mcu_participant_daily_by_mcu`(IN `p_mgm_mcuid` int)
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_schedule_date DATE;
  DECLARE v_total_dates INT DEFAULT 0;

  DECLARE cur_dates CURSOR FOR
    SELECT DISTINCT pd.Mcu_PreregisterDateCheckinSchedule
    FROM cpone.mcu_preregister_patients pp
    INNER JOIN cpone.mcu_preregister_date pd
      ON pd.Mcu_PreregisterDateMcu_PreregisterPatientsID = pp.Mcu_PreregisterPatientsID
     AND pd.Mcu_PreregisterDateIsActive = 'Y'
    WHERE pp.Mcu_PreregisterPatientsMgm_McuID = p_mgm_mcuid
      AND pp.Mcu_PreregisterPatientsIsActive = 'Y'
      AND pd.Mcu_PreregisterDateCheckinSchedule IS NOT NULL
      AND pd.Mcu_PreregisterDateCheckinSchedule <> '0000-00-00'
    ORDER BY pd.Mcu_PreregisterDateCheckinSchedule;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN cur_dates;

  dates_loop: LOOP
    FETCH cur_dates INTO v_schedule_date;
    IF v_done = 1 THEN
      LEAVE dates_loop;
    END IF;

    CALL cpone.sp_refresh_mcu_participant_daily_by_mcu_date(p_mgm_mcuid, v_schedule_date);
    SET v_total_dates = v_total_dates + 1;
  END LOOP;

  CLOSE cur_dates;

  SELECT v_total_dates AS processed_dates;
END

6. cpone_dashboard.sp_remove_user_project

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_remove_user_project`(IN `p_username` varchar(50), IN `p_mcu_id` int)
BEGIN
    DECLARE v_user_id INT;

    SELECT User_ID INTO v_user_id
    FROM dashboard_user
    WHERE User_Username = p_username
    LIMIT 1;

    IF v_user_id IS NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User tidak ditemukan';
    END IF;

    UPDATE dashboard_user_project
    SET    UserProj_IsActive  = 'N',
           UserProj_UpdatedAt = NOW()
    WHERE  UserProj_UserID = v_user_id
      AND  UserProj_McuID  = p_mcu_id;
END

7. cpone_dashboard.sp_reset_dashboard_user_password

CREATE DEFINER=`admin`@`localhost` PROCEDURE `sp_reset_dashboard_user_password`(IN `p_username` varchar(50), IN `p_new_password` varchar(255))
BEGIN
    DECLARE v_salt VARCHAR(36);
    DECLARE v_hash VARCHAR(64);

    IF p_new_password IS NULL OR TRIM(p_new_password) = '' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Password tidak boleh kosong';
    END IF;

    IF LENGTH(p_new_password) < 6 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Password minimal 6 karakter';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM dashboard_user WHERE User_Username = TRIM(p_username)) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User tidak ditemukan';
    END IF;

    SET v_salt = UUID();
    SET v_hash = SHA2(CONCAT(v_salt, ':', p_new_password), 256);

    UPDATE dashboard_user
    SET    User_Password  = v_hash,
           User_Salt      = v_salt,
           User_UpdatedAt = NOW()
    WHERE  User_Username  = TRIM(p_username);
END

Kesimpulan

• Schema cpone_dashboard pada devcpone memiliki 7 stored procedure.
• Dari hasil cek information_schema.TRIGGERS, tidak ada trigger yang memanggil 7 SP tersebut secara langsung.
• Jadi, jalur eksekusi SP-SP ini saat ini lebih masuk akal berasal dari layer aplikasi atau pemanggilan manual, bukan dari trigger database.