def action(self):
config_obj = ApplicationConfig.get_current_config()
cleaning_time_presence = int(config_obj.cleaning_time_presence)
today = datetime.datetime.now().date()
date = today - datetime.timedelta(days=cleaning_time_presence)
end_date = datetime.datetime.combine(date, day_end)
start_date = end_date - datetime.timedelta(days=cleaning_time_presence * 3)
cleaned = DBSession.execute(
"""
WITH minq as
(
SELECT MIN(p1.id) FROM presence_entry as p1
WHERE p1.ts >= :start_date AND p1.ts <= :end_date
GROUP BY p1.user_id, date_trunc('day', p1.ts)
),
maxq as
(
SELECT MAX(p2.id) FROM presence_entry as p2
WHERE p2.ts >= :start_date AND p2.ts <= :end_date
GROUP BY p2.user_id, date_trunc('day', p2.ts)
)
DELETE FROM presence_entry as p
WHERE p.ts >= :start_date AND p.ts <= :end_date
AND p.id NOT IN (SELECT * FROM minq) AND p.id NOT IN (SELECT * FROM maxq);
""",
params={"start_date": start_date, "end_date": end_date},
).rowcount
LOG(u"Cleaned %s entries" % (cleaned,))
return Response(self._(u"Cleaned ${num} entries", num=cleaned))
def action(self):
config_obj = ApplicationConfig.get_current_config()
cleaning_time_presence = int(config_obj.cleaning_time_presence)
today = datetime.datetime.now().date()
date = today - datetime.timedelta(days=cleaning_time_presence)
end_date = datetime.datetime.combine(date, day_end)
start_date = end_date - datetime.timedelta(
days=cleaning_time_presence * 3, )
cleaned = DBSession.execute("""
WITH minq as
(
SELECT MIN(p1.id) FROM presence_entry as p1
WHERE p1.ts >= :start_date AND p1.ts <= :end_date
GROUP BY p1.user_id, date_trunc('day', p1.ts)
),
maxq as
(
SELECT MAX(p2.id) FROM presence_entry as p2
WHERE p2.ts >= :start_date AND p2.ts <= :end_date
GROUP BY p2.user_id, date_trunc('day', p2.ts)
)
DELETE FROM presence_entry as p
WHERE p.ts >= :start_date AND p.ts <= :end_date
AND p.id NOT IN (SELECT * FROM minq) AND p.id NOT IN (SELECT * FROM maxq);
""",
params={
'start_date': start_date,
'end_date': end_date
}).rowcount
LOG(u"Cleaned %s entries" % (cleaned, ))
return Response(self._(u"Cleaned ${num} entries", num=cleaned))
