Ekspor dan Import User Murid dari SiM ke CBT moodle

 

1. Ambil data kelas XI

SELECT 
    u.username,
    u.firstname,
    u.lastname,
    u.email,
    nis.data AS profile_field_nis,
    pw.data AS profile_field_pwexam,
    c.name AS cohort1

INTO OUTFILE '/tmp/user_xi.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

FROM mdl_user u
JOIN mdl_cohort_members cm ON cm.userid = u.id
JOIN mdl_cohort c ON c.id = cm.cohortid

LEFT JOIN mdl_user_info_field fnis 
    ON fnis.shortname = 'nis'

LEFT JOIN mdl_user_info_data nis 
    ON nis.fieldid = fnis.id 
    AND nis.userid = u.id

LEFT JOIN mdl_user_info_field fpw 
    ON fpw.shortname = 'pwexam'

LEFT JOIN mdl_user_info_data pw 
    ON pw.fieldid = fpw.id 
    AND pw.userid = u.id

WHERE c.name IN (
    'XI-A','XI-B','XI-C',
    'XI-D','XI-E','XI-F','XI-G'
)

AND u.deleted = 0
AND u.suspended = 0

ORDER BY c.name, u.lastname;

---

2. Ambil data kelas X

SELECT 
    u.username,
    u.firstname,
    u.lastname,
    u.email,
    nis.data AS profile_field_nis,
    pw.data AS profile_field_pwexam,
    c.name AS cohort1

INTO OUTFILE '/tmp/user_x.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

FROM mdl_user u
JOIN mdl_cohort_members cm ON cm.userid = u.id
JOIN mdl_cohort c ON c.id = cm.cohortid

LEFT JOIN mdl_user_info_field fnis 
    ON fnis.shortname = 'nis'

LEFT JOIN mdl_user_info_data nis 
    ON nis.fieldid = fnis.id 
    AND nis.userid = u.id

LEFT JOIN mdl_user_info_field fpw 
    ON fpw.shortname = 'pwexam'

LEFT JOIN mdl_user_info_data pw 
    ON pw.fieldid = fpw.id 
    AND pw.userid = u.id

WHERE c.name IN (
    'X-A','X-B','X-C',
    'X-D','X-E','X-F','X-G'
)

AND u.deleted = 0
AND u.suspended = 0

ORDER BY c.name, u.lastname;

---

3. Ambil file dari laptop dengan SCP

Kelas XI:

scp dhwan@192.168.200.20:/tmp/user_xi.csv ~/Downloads/

Kelas X:

scp dhwan@192.168.200.20:/tmp/user_x.csv ~/Downloads/

Kalau gagal karena secure_file_priv, cek:

SHOW VARIABLES LIKE 'secure_file_priv';

Lalu ganti path /tmp/ sesuai folder yang ditampilkan MariaDB, misalnya:

/var/lib/mysql-files/