## Medical Approval
Total All Data
SELECT
COUNT(DISTINCT users.id) AS total_employe_mcu
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee'
AND employee_mcu.status = 'approved'
AND users.deleted_at IS NULL;
Detail All Data
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL
GROUP BY
users.id;
Total Filter Data
SELECT
COUNT(*) AS total
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_date BETWEEN '2024-02-23' AND '2024-10-23';
Detail All Filter Data
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_date BETWEEN '2024-02-23' AND '2024-10-23'
GROUP BY
users.id;
## Competency Approval
Total All Data
SELECT
COUNT(DISTINCT users.id) AS total_competency_approval
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
users.deleted_at IS NULL;
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
users.deleted_at IS NULL
GROUP BY
users.id;
Detail All Data
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
users.deleted_at IS NULL
GROUP BY
users.id;
Total Filter Data
SELECT
COUNT(DISTINCT users.id) AS total_approved_competency
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_date BETWEEN '2024-02-23' AND '2024-10-23'
GROUP BY
users.id;
Detail All Filter Data
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_date BETWEEN '2024-02-23' AND '2024-10-23'
GROUP BY
users.id;
## Approved
Total All Data
SELECT
COUNT(DISTINCT users.id) AS total_approved_users
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL;
Detail All Data
SELECT
users.id,
users.name,
users.nik,
users.position,
users.gender,
users.company
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
employee_mcu.`status` = 'approved' AND
users.deleted_at IS NULL
GROUP BY
users.id;
Total Filter Data
SELECT
COUNT(DISTINCT users.id) AS total_approved_users
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_validation BETWEEN '2024-02-23' AND '2024-10-23';
Detail All Filter Data
SELECT
users.id,
users.name,
users.nik,
users.email,
users.position,
users.gender,
users.company,
employee_mcu.mcu_validation,
employee_mcu.status
FROM
employee_mcu
JOIN
users ON employee_mcu.user_id = users.id
WHERE
users.type = 'employee' AND
users.competency_status = 'approved' AND
employee_mcu.status = 'approved' AND
users.deleted_at IS NULL AND
employee_mcu.mcu_validation BETWEEN '2024-02-23' AND '2024-10-23';
## Personnel On Duty
Total All Data
SELECT
gates.id AS gate_id,
gates.name AS gate_name,
COUNT(employee_gates.id) AS total_data
FROM
gates
JOIN
employee_gates ON gates.id = employee_gates.gate_id
JOIN
gate_in ON employee_gates.gate_in_id = gate_in.id
WHERE
gate_in.status_duty = 'In Progress'
GROUP BY
gates.id, gates.name
ORDER BY
total_data DESC;
Total Filter Data
SELECT
gates.id AS gate_id,
gates.name AS gate_name,
COUNT(employee_gates.id) AS total_data
FROM
gates
JOIN
employee_gates ON gates.id = employee_gates.gate_id
JOIN
gate_in ON employee_gates.gate_in_id = gate_in.id
WHERE
gate_in.status_duty = 'In Progress' AND
gate_in.start_duty BETWEEN '2024-02-23' AND '2027-10-23'
GROUP BY
gates.id, gates.name
ORDER BY
total_data DESC;
Detail All Data
SELECT
u.name AS name,
u.position AS position,
u.gender AS gender,
u.company AS company,
GROUP_CONCAT(g.name ORDER BY g.id SEPARATOR ', ') AS location,
DATE_FORMAT(gi.start_duty, '%d %M %Y %H:%i') AS start_duty,
DATE_FORMAT(gi.end_duty, '%d %M %Y %H:%i') AS end_duty,
DATEDIFF(CURDATE(), gi.start_duty) AS working_days
FROM gate_in gi
JOIN users u ON gi.user_id = u.id
JOIN employee_gates eg ON gi.id = eg.gate_in_id
JOIN gates g ON eg.gate_id = g.id
WHERE gi.status_duty = 'In Progress'
GROUP BY gi.id;
{{-- Detail All Filter Data
SELECT
u.name AS name,
u.position AS position,
u.gender AS gender,
u.company AS company,
GROUP_CONCAT(g.name ORDER BY g.id SEPARATOR ', ') AS location,
DATE_FORMAT(gi.start_duty, '%d %M %Y %H:%i') AS start_duty,
DATE_FORMAT(gi.end_duty, '%d %M %Y %H:%i') AS end_duty,
DATEDIFF(CURDATE(), gi.start_duty) AS working_days
FROM gate_in gi
JOIN users u ON gi.user_id = u.id
JOIN employee_gates eg ON gi.id = eg.gate_in_id
JOIN gates g ON eg.gate_id = g.id
WHERE gi.status_duty = 'In Progress'
AND gi.start_duty BETWEEN '2024-02-23' AND '2027-10-23'
GROUP BY gi.id;
--}}
## Permitted to Work
Total All Data
SELECT COUNT(*) AS total_permitted_to_work
FROM users
JOIN employee_mcu ON employee_mcu.user_id = users.id
WHERE users.competency_status = 'Approved'
AND employee_mcu.status = 'Approved'
AND employee_mcu.date_validation IS NOT NULL;
Total Filter Data
SELECT
COUNT(DISTINCT users.id) AS total_approved_users
FROM
users
JOIN
employee_mcu ON employee_mcu.user_id = users.id
WHERE
users.competency_status = 'Approved' AND
employee_mcu.status = 'Approved' AND
employee_mcu.date_validation BETWEEN '2024-02-23' AND '2024-10-23';
Detail All Data
SELECT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
users
JOIN
employee_mcu ON employee_mcu.user_id = users.id
WHERE
users.competency_status = 'Approved' AND
employee_mcu.status = 'Approved' AND
employee_mcu.date_validation IS NOT NULL;
{{-- Detail All Filter Data
SELECT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
users
JOIN
employee_mcu ON employee_mcu.user_id = users.id
WHERE
users.competency_status = 'Approved' AND
employee_mcu.status = 'Approved' AND
employee_mcu.date_validation BETWEEN '2024-02-23' AND '2024-10-23';
--}}
## Over Days
Total All Data
SELECT COUNT(*) AS total
FROM (
SELECT DISTINCT user_id, status_duty, end_duty
FROM gate_in
WHERE user_type = 'employee'
AND status = 'Approved'
AND status_duty = 'Incomplete'
) AS subquery;
Total Filter Data
SELECT
COUNT(DISTINCT users.id) AS total_incomplete_users
FROM
users
JOIN
gate_in ON users.id = gate_in.user_id
WHERE
gate_in.user_type = 'employee' AND
gate_in.status = 'Approved' AND
gate_in.status_duty = 'Incomplete' AND
gate_in.end_duty BETWEEN '2024-02-23' AND '2027-10-23';
Detail All Data
SELECT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
users
JOIN
gate_in ON users.id = gate_in.user_id
WHERE
gate_in.user_type = 'employee' AND
gate_in.status = 'Approved' AND
gate_in.status_duty = 'Incomplete';
{{-- Detail All Filter Data
SELECT DISTINCT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
users
JOIN
gate_in ON users.id = gate_in.user_id
WHERE
gate_in.user_type = 'employee' AND
gate_in.status = 'Approved' AND
gate_in.status_duty = 'Incomplete' AND
gate_in.end_duty BETWEEN '2024-02-23' AND '2027-10-23';
--}}
## On Duty Approval
Total All Data
SELECT COUNT(DISTINCT user_id) AS total_data
FROM gate_in
WHERE user_type = 'employee'
AND status = 'Decline'
AND CURDATE() > DATE(start_duty);
Total Filter Data
SELECT COUNT(DISTINCT user_id) AS total_data
FROM gate_in
WHERE user_type = 'employee'
AND status = 'Decline'
AND start_duty BETWEEN '2024-02-23' AND '2027-10-23';
Detail All Data
SELECT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
gate_in
JOIN
users ON users.id = gate_in.user_id
WHERE
gate_in.user_type = 'employee'
AND gate_in.status = 'Decline'
AND CURDATE() > DATE(gate_in.start_duty)
GROUP BY
users.id, users.name, users.username, users.position, users.gender;
{{-- Detail All Filter Data
SELECT
users.id,
users.name,
users.username,
users.position,
users.gender
FROM
gate_in
JOIN
users ON users.id = gate_in.user_id
WHERE
gate_in.user_type = 'employee'
AND gate_in.status = 'Decline'
AND start_duty BETWEEN '2024-02-23' AND '2027-10-23'
GROUP BY
users.id, users.name, users.username, users.position, users.gender;
--}}