Lager Guru Docs

Appearance

Database Views ​

All database views and their definitions.

shipments_per_hour_last_24h ​

sql
SELECT
  date_trunc('hour', created_at) AS hour_bucket,
  count(*) AS total,
  count(*) FILTER (WHERE status = 'completed') AS completed,
  count(*) FILTER (WHERE status != 'completed') AS open
FROM public.shipments
WHERE created_at >= (now() - interval '24 hours')
GROUP BY hour_bucket
ORDER BY hour_bucket

driver_performance ​

sql
SELECT
  p.id AS driver_id,
  coalesce(p.full_name, p.email) AS driver_name,
  count(s.id) FILTER (WHERE s.status = 'completed') AS completed_deliveries,
  count(s.id) FILTER (WHERE s.status != 'completed') AS active_deliveries,
  avg(extract(epoch FROM (s.completed_at - s.created_at)) / 60)
    FILTER (WHERE s.completed_at IS NOT NULL) AS avg_minutes_to_complete
FROM public.profiles p
JOIN public.user_roles ur ON ur.user_id = p.id AND ur.role = 'driver'
LEFT JOIN public.shipments s ON s.driver_id = p.id
GROUP BY p.id, driver_name

time_to_complete_metrics ​

sql
SELECT
  avg(extract(epoch FROM (completed_at - created_at)) / 60) AS avg_minutes,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM (completed_at - created_at)) / 60) AS p50_minutes,
  percentile_cont(0.9) WITHIN GROUP (ORDER BY extract(epoch FROM (completed_at - created_at)) / 60) AS p90_minutes
FROM public.shipments
WHERE completed_at IS NOT NULL

zone_utilization ​

sql
SELECT
  z.id,
  z.code,
  z.name,
  z.color,
  z.max_capacity,
  COALESCE(active_shipments.count, 0) AS active_shipments,
  ROUND(
    LEAST(
      100.0,
      (COALESCE(active_shipments.count, 0)::decimal / NULLIF(z.max_capacity, 0)) * 100
    ),
    2
  ) AS utilization_percentage
FROM
  public.zones z
LEFT JOIN (
  SELECT
    COALESCE(from_zone_id, to_zone_id) AS zone_id,
    COUNT(*) AS count
  FROM
    public.shipments
  WHERE
    status IN ('pending', 'in-transit')
  GROUP BY
    COALESCE(from_zone_id, to_zone_id)
) AS active_shipments
ON z.id = active_shipments.zone_id

zones_avg_utilization ​

sql
SELECT 
  ROUND(AVG(utilization_percentage), 2) AS avg_utilization
FROM public.zone_utilization

shift_hours_weekly ​

Description: Изчислява общо планирани и действителни часове за текущата седмица. Поддържа нощни смени и override времена от exceptions.

sql
SELECT
  COUNT(*) AS total_shifts,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN st.end_time < st.start_time THEN
          (st.end_time::time + INTERVAL '1 day') - st.start_time::time
        ELSE
          st.end_time::time - st.start_time::time
      END
    )) / 3600.0
  ), 2) AS total_planned_hours,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN COALESCE(se.end_time_override, st.end_time)::time < COALESCE(se.start_time_override, st.start_time)::time THEN
          (COALESCE(se.end_time_override, st.end_time)::time + INTERVAL '1 day') - COALESCE(se.start_time_override, st.start_time)::time
        ELSE
          COALESCE(se.end_time_override, st.end_time)::time - COALESCE(se.start_time_override, st.start_time)::time
      END
    )) / 3600.0
  ), 2) AS total_actual_hours
FROM public.shift_assignments sa
JOIN public.shift_templates st ON st.id = sa.shift_template_id
LEFT JOIN public.shift_exceptions se ON se.shift_assignment_id = sa.id
WHERE sa.date >= date_trunc('week', CURRENT_DATE)
  AND sa.date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'

shift_hours_per_driver ​

Description: Изчислява общо часове по шофьор за текущата седмица.

sql
SELECT
  p.id AS driver_id,
  COALESCE(p.full_name, p.email) AS driver_name,
  COUNT(sa.id) AS total_shifts,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN COALESCE(se.end_time_override, st.end_time)::time < COALESCE(se.start_time_override, st.start_time)::time THEN
          (COALESCE(se.end_time_override, st.end_time)::time + INTERVAL '1 day') - COALESCE(se.start_time_override, st.start_time)::time
        ELSE
          COALESCE(se.end_time_override, st.end_time)::time - COALESCE(se.start_time_override, st.start_time)::time
      END
    )) / 3600.0
  ), 2) AS total_hours
FROM public.profiles p
JOIN public.user_roles ur ON ur.user_id = p.id AND ur.role = 'driver'
LEFT JOIN public.shift_assignments sa ON sa.driver_id = p.id
LEFT JOIN public.shift_templates st ON st.id = sa.shift_template_id
LEFT JOIN public.shift_exceptions se ON se.shift_assignment_id = sa.id
WHERE sa.date >= date_trunc('week', CURRENT_DATE)
  AND sa.date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'
GROUP BY p.id, driver_name

zone_utilization ​

sql
SELECT
  z.id,
  z.code,
  z.name,
  z.color,
  z.max_capacity,
  COALESCE(active_shipments.count, 0) AS active_shipments,
  ROUND(
    LEAST(
      100.0,
      (COALESCE(active_shipments.count, 0)::decimal / NULLIF(z.max_capacity, 0)) * 100
    ),
    2
  ) AS utilization_percentage
FROM
  public.zones z
LEFT JOIN (
  SELECT
    COALESCE(from_zone_id, to_zone_id) AS zone_id,
    COUNT(*) AS count
  FROM
    public.shipments
  WHERE
    status IN ('pending', 'in-transit')
  GROUP BY
    COALESCE(from_zone_id, to_zone_id)
) AS active_shipments
ON z.id = active_shipments.zone_id

zones_avg_utilization ​

sql
SELECT 
  ROUND(AVG(utilization_percentage), 2) AS avg_utilization
FROM public.zone_utilization

shift_hours_weekly ​

Description: Изчислява общо планирани и действителни часове за текущата седмица. Поддържа нощни смени и override времена от exceptions.

sql
SELECT
  COUNT(*) AS total_shifts,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN st.end_time < st.start_time THEN
          (st.end_time::time + INTERVAL '1 day') - st.start_time::time
        ELSE
          st.end_time::time - st.start_time::time
      END
    )) / 3600.0
  ), 2) AS total_planned_hours,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN COALESCE(se.end_time_override, st.end_time)::time < COALESCE(se.start_time_override, st.start_time)::time THEN
          (COALESCE(se.end_time_override, st.end_time)::time + INTERVAL '1 day') - COALESCE(se.start_time_override, st.start_time)::time
        ELSE
          COALESCE(se.end_time_override, st.end_time)::time - COALESCE(se.start_time_override, st.start_time)::time
      END
    )) / 3600.0
  ), 2) AS total_actual_hours
FROM public.shift_assignments sa
JOIN public.shift_templates st ON st.id = sa.shift_template_id
LEFT JOIN public.shift_exceptions se ON se.shift_assignment_id = sa.id
WHERE sa.date >= date_trunc('week', CURRENT_DATE)
  AND sa.date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'

shift_hours_per_driver ​

Description: Изчислява общо часове по шофьор за текущата седмица.

sql
SELECT
  p.id AS driver_id,
  COALESCE(p.full_name, p.email) AS driver_name,
  COUNT(sa.id) AS total_shifts,
  ROUND(SUM(
    EXTRACT(EPOCH FROM (
      CASE 
        WHEN COALESCE(se.end_time_override, st.end_time)::time < COALESCE(se.start_time_override, st.start_time)::time THEN
          (COALESCE(se.end_time_override, st.end_time)::time + INTERVAL '1 day') - COALESCE(se.start_time_override, st.start_time)::time
        ELSE
          COALESCE(se.end_time_override, st.end_time)::time - COALESCE(se.start_time_override, st.start_time)::time
      END
    )) / 3600.0
  ), 2) AS total_hours
FROM public.profiles p
JOIN public.user_roles ur ON ur.user_id = p.id AND ur.role = 'driver'
LEFT JOIN public.shift_assignments sa ON sa.driver_id = p.id
LEFT JOIN public.shift_templates st ON st.id = sa.shift_template_id
LEFT JOIN public.shift_exceptions se ON se.shift_assignment_id = sa.id
WHERE sa.date >= date_trunc('week', CURRENT_DATE)
  AND sa.date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'
GROUP BY p.id, driver_name

Released under Commercial License

Copyright © 2025 HashMatrix