aspek_kk_issue_1.2_check

-- Get jawaban enumerator (sebelum KK)

SELECT
    jawaban.*,
    files.id,
    files.created_at,
    files.disk_name,
    files.file_name,
    files.attachment_id,
    CONCAT(
        "https://aspek.ombudsman.go.id/storage/app/uploads/public/",
        CONCAT(
            SUBSTRING(files.disk_name, 1, 3),
            "/",
            SUBSTRING(files.disk_name, 4, 3)),
        CONCAT(
            "/",
            SUBSTRING(files.disk_name, 7, 3),
            "/", files.disk_name)
    ) AS 'Url Path'

FROM
    aspek_kepatuhan_detail_jawaban as jawaban
LEFT JOIN
    system_files as files ON files.attachment_id = jawaban.id
JOIN
    aspek_kepatuhan_profile_responden as responden ON responden.id = jawaban.profil_responden_id
WHERE
    responden.id IN (
        SELECT
            responden.id
        FROM
            aspek_kepatuhan_profile_responden as responden
        WHERE
            institution_id = (
            SELECT
                institution_id
            FROM
                aspek_kepatuhan_profile_responden
            WHERE
                id = 37073
            )
        ORDER BY
            responden.id
    )
AND jawaban.questions_id = 23
ORDER BY
    jawaban.profil_responden_id, jawaban.questions_id


-- Get jawaban setelah KK
SELECT
    jawaban.*,
    files.id,
    files.created_at,
    files.disk_name,
    files.file_name,
    files.attachment_id,
    CONCAT(
        "https://aspek.ombudsman.go.id/storage/app/uploads/public/",
        CONCAT(
            SUBSTRING(files.disk_name, 1, 3),
            "/",
            SUBSTRING(files.disk_name, 4, 3)),
        CONCAT(
            "/",
            SUBSTRING(files.disk_name, 7, 3),
            "/", files.disk_name)
    ) AS 'Url Path'

FROM
    aspek_kepatuhan_jawaban_supervisor as jawaban
LEFT JOIN
    system_files as files ON files.id = jawaban.file_id
JOIN
    aspek_kepatuhan_profile_responden as responden ON responden.id = jawaban.profil_responden_id
WHERE
    responden.id IN (
        SELECT
            responden.id
        FROM
            aspek_kepatuhan_profile_responden as responden
        WHERE
            institution_id = (
            SELECT
                institution_id
            FROM
                aspek_kepatuhan_profile_responden
            WHERE
                id = 37073
            )
        ORDER BY
            responden.id
    )
AND jawaban.questions_id = 23
ORDER BY
    jawaban.profil_responden_id, jawaban.questions_id