-- #1
SELECT v.DAY_OF_WEEK, v.NUMBER_OF_SOT FROM
(
  SELECT gu.gui_user_id,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') + 6
  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 gu.gui_user_id,TRUNC(touserdate(sot.work_date,oc.timezone))
) v
WHERE v.gui_user_id = 101114
ORDER BY 1;

-- #2
SELECT v.REPORTED_BY_HOUR, v.NUMBER_OF_BADGES
FROM
(
  SELECT gu.gui_user_id,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 TRUNC(touserdate(vd.gps_full_date,oc.timezone)) = TRUNC(SYSDATE)
  GROUP BY gu.gui_user_id,TRUNC(touserdate(vd.gps_full_date,oc.timezone),'HH')
) v
WHERE v.gui_user_id = 101114
ORDER BY 1;

-- #3
SELECT v.WORK_DATE, v.WORKED_HOURS
FROM (
SELECT gu.gui_user_id, ptd.date_day AS WORK_DATE, SUM(ptd.trf_dhr) AS WORKED_HOURS
FROM gui_users gu, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
WHERE 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 p.person_status_id = 0
AND NVL(vopc.is_active,'Y') = 'Y'
AND NVL(wc.is_active,'Y') = 'Y'
AND ptd.date_day BETWEEN TRUNC(SYSDATE,'iw') AND TRUNC(SYSDATE,'iw') + 6
GROUP BY gu.gui_user_id,ptd.date_day
) v
WHERE gui_user_id = 101042
ORDER BY 1;

-- #4
SELECT v.WORK_MONTH,v.WORK_CODE,v.WORKED_HOURS FROM
(
  SELECT gu.gui_user_id, TO_CHAR(TRUNC(ptd.date_day,'mm'),'Month') AS WORK_MONTH,'[' || wc.short_code || ']' || ' ' || wc.work_code_name AS WORK_CODE, SUM(ptd.trf_dhr) AS WORKED_HOURS
  FROM gui_users gu, gu_person gp, person p, vo_person_category vopc, pd_trf_day ptd, work_code wc
  WHERE 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 p.person_status_id = 0
  AND NVL(vopc.is_active,'Y') = 'Y'
  AND NVL(wc.is_active,'Y') = 'Y'
  AND TRUNC(ptd.date_day,'mm') = TRUNC(SYSDATE,'mm')
  GROUP BY gu.gui_user_id,TRUNC(ptd.date_day,'mm'), '[' || wc.short_code || ']' || ' ' || wc.work_code_name
) v
WHERE v.gui_user_id = 101042
AND worked_hours IS NOT NULL
ORDER BY 3;