<%
@records = ActiveRecord::Base.connection.execute("
SELECT
m.name,
temp.lng,
temp.lat,
temp.timestamp
FROM managers m
INNER JOIN (
SELECT
manager_id,
timestamp,
lng,
lat,
DENSE_RANK() OVER (PARTITION BY manager_id ORDER BY timestamp DESC) as rank
FROM (
SELECT
r.manager_id,
ST_X(ci.coordinates::geometry) lng,
ST_Y(ci.coordinates::geometry) lat,
ci.timestamp
FROM routes r
INNER JOIN check_ins ci ON ci.id = r.begin_check_in_id
WHERE r.date >= '#{(Date.today-1.week).strftime('%Y-%m-%d')}'
UNION ALL
SELECT
r.manager_id,
ST_X(ci.coordinates::geometry) lng,
ST_Y(ci.coordinates::geometry) lat,
ci.timestamp
FROM routes r
INNER JOIN route_points rp ON rp.route_id = r.id
INNER JOIN work_time_intervals wti ON wti.route_point_id = rp.id
INNER JOIN check_ins ci ON ci.id = wti.begin_check_in_id
WHERE r.date >= '#{(Date.today-1.week).strftime('%Y-%m-%d')}'
UNION ALL
SELECT
r.manager_id,
ST_X(ci.coordinates::geometry) lng,
ST_Y(ci.coordinates::geometry) lat,
ci.timestamp
FROM routes r
INNER JOIN route_points rp ON rp.route_id = r.id
INNER JOIN work_time_intervals wti ON wti.route_point_id = rp.id
INNER JOIN check_ins ci ON ci.id = wti.end_check_in_id
WHERE r.date >= '#{(Date.today-1.week).strftime('%Y-%m-%d')}'
UNION ALL
SELECT
r.manager_id,
ST_X(ci.coordinates::geometry) lng,
ST_Y(ci.coordinates::geometry) lat,
ci.timestamp
FROM routes r
INNER JOIN check_ins ci ON ci.id = r.end_check_in_id
WHERE r.date >= '#{(Date.today-1.week).strftime('%Y-%m-%d')}'
) temp
WHERE lng IS NOT NULL AND lat IS NOT NULL
) temp ON temp.rank = 1 AND temp.manager_id = m.id
WHERE m.validity = true
ORDER BY m.name DESC
")
%>
<% if @records.any? %>
<% else %>
Нет данных по месторасположению сотрудников!
<% end %>