-- #1
SELECT TRUNC(touserdate(sot.work_date,oc.timezone)) AS DAY_OF_WEEK,COUNT(*) AS NUMBER_OF_SOT
FROM social_office_transaction sot, gu_person gp, person p, gui_users gu, operation_center oc, vo_person_category vopc
WHERE gu.gui_user_id = gp.gui_user_id
AND gp.person_id = p.person_id
AND p.person_id = sot.person_id
AND gu.operation_center_id = oc.operation_center_id
AND touserdate(sot.work_date,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
AND gu.gui_user_id = 101114
AND p.person_status_id = 0
AND p.vo_person_category_id = vopc.vo_person_category_id
AND NVL(vopc.is_active,'Y') = 'Y'
GROUP BY TRUNC(touserdate(sot.work_date,oc.timezone))
ORDER BY 1;

-- #2
SELECT TO_CHAR(TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH'),'dd/mm/yyyy HH:MI') AS REPORTED_BY_HOUR, COUNT(vd.vehicle_data_id) AS NUMBER_OF_BADGES
FROM gui_users gu,operation_center oc, gu_vehicle guv, vehicle v, vo_vehicle_category vovc, vehicle_data vd
WHERE gu.operation_center_id = oc.operation_center_id
AND gu.gui_user_id = guv.gui_user_id
AND guv.vehicle_id = v.vehicle_id
AND v.vehicle_id = vd.vehicle_id
AND v.vo_vehicle_category_id = vovc.vo_vehicle_category_id
AND NVL(vovc.is_active,'Y') = 'Y'
AND NVL(v.vehicle_status_id,0) = 0
AND vd.event_type_id = 103
AND gu.gui_user_id = 101114
AND TRUNC(touserdate(vd.gps_full_date,oc.timezone)) = TRUNC(SYSDATE)
GROUP BY TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH')
ORDER BY 1;

-- #3
SELECT touserdate(ptd.date_day,oc.timezone) AS WORK_DATE, SUM(ptd.trf_dhr) AS WORKED_HOURS
FROM gui_users gu,operation_center oc, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
WHERE gu.operation_center_id = oc.operation_center_id
AND gu.gui_user_id = gp.gui_user_id
AND gp.person_id = p.person_id
AND p.person_id = ptd.person_id
AND ptd.work_code_id = wc.work_code_id
AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
AND p.vo_person_category_id = vopc.vo_person_category_id
AND gu.gui_user_id = 101042
AND p.person_status_id = 0
AND NVL(vopc.is_active,'Y') = 'Y'
AND NVL(wc.is_active,'Y') = 'Y'
AND touserdate(ptd.date_day,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
GROUP BY touserdate(ptd.date_day,oc.timezone)
ORDER BY 1;

-- #4
SELECT touserdate(ptd.date_day,oc.timezone) AS WORK_DATE,wc.work_code_name AS WORK_CODE, SUM(ptd.trf_dhr) AS WORKED_HOURS
FROM gui_users gu,operation_center oc, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
WHERE gu.operation_center_id = oc.operation_center_id
AND gu.gui_user_id = gp.gui_user_id
AND gp.person_id = p.person_id
AND p.person_id = ptd.person_id
AND ptd.work_code_id = wc.work_code_id
AND (wc.work_code_std_type_id = 1 OR wc.work_code_std_category_id = 1)
AND p.vo_person_category_id = vopc.vo_person_category_id
AND gu.gui_user_id = 101042
AND p.person_status_id = 0
AND NVL(vopc.is_active,'Y') = 'Y'
AND NVL(wc.is_active,'Y') = 'Y'
AND touserdate(ptd.date_day,oc.timezone) BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 4
GROUP BY touserdate(ptd.date_day,oc.timezone), wc.work_code_name
ORDER BY 1;