def test_extract_minute_func(self):
start_datetime = microsecond_support(
datetime(2015, 6, 15, 14, 30, 50, 321))
end_datetime = microsecond_support(
datetime(2016, 6, 15, 14, 10, 50, 123))
if settings.USE_TZ:
start_datetime = timezone.make_aware(start_datetime, is_dst=False)
end_datetime = timezone.make_aware(end_datetime, is_dst=False)
self.create_model(start_datetime, end_datetime)
self.create_model(end_datetime, start_datetime)
self.assertQuerysetEqual(
DTModel.objects.annotate(extracted=ExtractMinute(
'start_datetime')).order_by('start_datetime'),
[(start_datetime, start_datetime.minute),
(end_datetime, end_datetime.minute)], lambda m:
(m.start_datetime, m.extracted))
self.assertQuerysetEqual(
DTModel.objects.annotate(extracted=ExtractMinute(
'start_time')).order_by('start_datetime'),
[(start_datetime, start_datetime.minute),
(end_datetime, end_datetime.minute)], lambda m:
(m.start_datetime, m.extracted))
self.assertEqual(
DTModel.objects.filter(start_datetime__minute=ExtractMinute(
'start_datetime')).count(), 2)
def addSem(startDate, endDate, dataIndex):
# create set of all start dates between semester start and end
dates = set()
for week in daterange(startDate, endDate):
start = week - timedelta(days=week.weekday())
dates.add(start)
dates = sorted(list(dates))
# get total hours per week in queryData (unordered set)
queryset = Checkin.objects.all().annotate(
durationDiff=F('timeOut') - F('timeIn'),
duration=(ExtractHour('durationDiff') * 60 +
ExtractMinute('durationDiff')),
weekstart=TruncWeek('date')).values('weekstart').annotate(
sumHours=Sum('duration')).order_by('weekstart')
queryData = queryset.values('weekstart', 'sumHours')
# put hours per week (in sequential order) in data array that will be returned
finalSet = []
for d in dates:
hours = 0
for val in queryData:
if val['weekstart'] == d:
hours = val['sumHours']
finalSet.append({'weekstart': d, 'sumHours': hours})
for x in finalSet:
data[dataIndex]['data'].append(x['sumHours'] / 60)
# didn't start using check-in app till week 8 in Spring 2020
if (dataIndex == 0):
for x in range(0, 7):
data[0]['data'][x] = None
# 8/23/21 is week 1 of Fall 2021 (remove weeks after that for now)
# Increment the range (1st parameter) each week to add next data point
if (dataIndex == 3):
for x in range(2, 17):
data[3]['data'].pop()
def get_queryset(self):
self.scope = get_request_or_fallback(self.request, "scope", "annualy", str,False)
self.year = get_request_or_fallback(self.request, "year", datetime.now().year, str, False)
self.month = get_request_or_fallback(self.request, "months", None, str, False)
self.day = get_request_or_fallback(self.request, "day", None, str, False)
if self.month != None : self.month = int(self.month)
if self.day != None: self.day = int(self.day)
qrySet = Timesheets.objects.filter(
time__year=self.year
).values(
"user__id",
"user__user__first_name",
"user__user__last_name"
).annotate(
year=ExtractYear("time", output_field=IntegerField()),
month=ExtractMonth("time", output_field=IntegerField()),
day=ExtractDay("time", output_field=IntegerField())
).annotate(
seconds=ExtractSecond("time", output_field=IntegerField()),
minutes=ExtractMinute("time", output_field=IntegerField()),
hours=ExtractHour("time", output_field=IntegerField())
).filter(getFilterIfContentAdmin(self.request)).order_by("time", "user__id")
return TimestampDisplay(TimeStampsManager(qrySet, self.year)).getScopedView(self.scope, self.month, self.day)
def computeHoursDaily(self, employee):
date = self.getDate()
querySet = Timesheets.objects.filter(
time__year=date.year
).values(
"user__id",
"user__user__first_name",
"user__user__last_name"
).annotate(
year=ExtractYear("time", output_field=IntegerField()),
month=ExtractMonth("time", output_field=IntegerField()),
day=ExtractDay("time", output_field=IntegerField())
).annotate(
seconds=ExtractSecond("time", output_field=IntegerField()),
minutes=ExtractMinute("time", output_field=IntegerField()),
hours=ExtractHour("time", output_field=IntegerField())
).filter(getFilterIfContentAdmin(self.request)).order_by("time", "user__id")
data = TimestampDisplay(TimeStampsManager(querySet, date.year )).getDailyView(
date.day,
date.month,
employee.id
)
if data == {}:
return (0,0)
else:
return data[employee.id]
def get_data_gpu(delta, pk):
data = InformerData.objects.filter(informer_id=pk,
date__gte=timezone.now() -
timedelta(hours=delta))
msgs = data.values_list('msg', flat=True).distinct()
data_gpu = []
for msg in msgs:
data_msg = data.filter(msg=msg)
date_dict = {
1:
data_msg.values(hour=ExtractHour('date'),
minute=ExtractMinute('date')),
24:
data_msg.values(hour=ExtractHour('date')),
720:
data_msg.values(day=ExtractDay('date')),
8640:
data_msg.values(month=ExtractMonth('date'))
}
data_list_dict = date_dict[delta].annotate(
temperature_avg=Avg('temperature'))
os_x = []
os_y = []
for i in data_list_dict:
os_x.append(list(i.values())[0])
os_y.append(list(i.values())[1])
data_gpu.append({msg: [os_x, os_y]})
print(data_gpu)
return data_gpu
def retrieve(self, request, pk=None, date=None):
batterylog = models.Batterylog.objects.filter(morcha=pk)
batterylog = batterylog.annotate(
hour=ExtractHour('detected_datetime'),
minute=ExtractMinute('detected_datetime')).values(
'hour', 'minute', 'battery_level')
print batterylog.query
print batterylog.model
print batterylog.using
return Response({'bl': batterylog})
def get_last_update():
data_banco = Embrapa_Last_Updated.objects.annotate(
day=ExtractDay('last_updated'),
month=ExtractMonth('last_updated'),
year=ExtractYear('last_updated'),
hour=ExtractHour('last_updated'),
minute=ExtractMinute('last_updated'),
second=ExtractSecond('last_updated'),
).values('day', 'month', 'year', 'hour', 'minute', 'second').get()
print(data_banco)
day = data_banco["day"]
month = data_banco["month"]
year = data_banco["year"]
hour = data_banco["hour"]
minute = data_banco["minute"]
second = data_banco["second"]
result_in_seconds_from_database = day * 86400 + month * 2592000 + year * 31104000 + hour * 3600 + minute * 60 + second
print("resultado do banco em segundos:")
print(result_in_seconds_from_database)
date_current = datetime.now()
format_date = str(date_current)
format_year = format_date[0:4]
format_year = int(format_year)
format_month = format_date[5:7]
format_month = int(format_month)
format_day = format_date[8:10]
format_day = int(format_day)
format_hour = format_date[11:13]
format_hour = int(format_hour)
format_minute = format_date[14:16]
format_minute = int(format_minute)
format_second = format_date[17:19]
format_second = int(format_second)
result_all_in_seconds_current = format_day * 86400 + format_month * 2592000 + format_year * 31104000 + format_hour * 3600 + format_minute * 60 + format_second
print(result_all_in_seconds_current)
return [result_all_in_seconds_current, result_in_seconds_from_database]
def GoogleChartData(request):
start_day = request.GET.get('start-day')
end_day = request.GET.get('end-day')
dt_start_day = datetime.datetime.strptime(start_day, '%Y-%m-%d')
dt_end_day = datetime.datetime.strptime(
end_day, '%Y-%m-%d') + datetime.timedelta(days=1)
datapoint = request.GET.get('datapoint')
building_pk, building, model, endpoint = datapoint.split('-')
# model에 따라 데이터 조회
if model == 'Ismart':
queryset = Ismart.objects.filter(
building__pk=building_pk,
datetime__range=(dt_start_day, dt_end_day)).order_by('datetime')
elif model == 'Weather':
station_id = Building.objects.get(
pk=building_pk).weather_station.station_id
queryset = Weather.objects.filter(
weather_station__station_id=station_id,
datetime__range=(dt_start_day, dt_end_day)).order_by('datetime')
queryset = queryset.annotate(timestamp=Concat(Value('Date('),
ExtractYear('datetime'),
Value(', '),
ExtractMonth('datetime') - 1,
Value(', '),
ExtractDay('datetime'),
Value(', '),
ExtractHour('datetime'),
Value(', '),
ExtractMinute('datetime'),
Value(', '),
ExtractSecond('datetime'),
Value(')'),
output_field=CharField()))
data = queryset.values_list('timestamp', endpoint)
result = {
'prop': {
'id': datapoint,
'label': building + '-' + model + '-' + endpoint,
'type': 'number'
},
'data': dict((x[0], x[1]) for x in data)
}
return HttpResponse(json.dumps(result, ensure_ascii=False),
content_type="application/json; charset=utf-8")
def visitor_chart4(request):
labels = []
data = []
oldestWeek = Checkin.objects.earliest('date')
currWeek = date.today()
def daterange(date1, date2):
for n in range(int((date2 - date1).days) + 1):
yield date1 + timedelta(n)
dates = set()
for week in daterange(getattr(oldestWeek, 'date'), currWeek):
start = week - timedelta(days=week.weekday())
dates.add(start)
dates = sorted(list(dates))
queryset = Checkin.objects.all().annotate(
durationDiff=F('timeOut') - F('timeIn'),
duration=(ExtractHour('durationDiff') * 60 +
ExtractMinute('durationDiff')),
weekstart=TruncWeek('date')).values('weekstart').annotate(
sumHours=Sum('duration')).order_by('weekstart')
queryData = queryset.values('weekstart', 'sumHours')
finalSet = []
for d in dates:
hours = 0
for val in queryData:
if val['weekstart'] == d:
hours = val['sumHours']
finalSet.append({'weekstart': d, 'sumHours': hours})
for entry in finalSet:
labels.append(entry['weekstart'])
data.append(entry['sumHours'] / 60)
return JsonResponse(data={'labels': labels, 'data': data})
def stats_order_heatmap(request):
try:
from_year = int(request.GET.get('from_year',
2022)) # TODO: improve filtering
except ValueError:
raise Http404("Året finns inte")
orders = models.Order.objects.filter(
accepted=True, put_at__year__gte=from_year).annotate(
weekday=ExtractIsoWeekDay('put_at'),
hour=ExtractHour("put_at"),
minute=ExtractMinute("put_at"),
).annotate(quarter=Case(
When(minute__gte=0, minute__lt=15, then=Value(0)),
When(minute__gte=15, minute__lt=30, then=Value(15)),
When(minute__gte=30, minute__lt=45, then=Value(30)),
When(minute__gte=45, then=Value(45)),
output_field=IntegerField(),
)).filter(weekday__lt=6, hour__gte=8,
hour__lte=16).values("weekday", "hour", "quarter").annotate(
count=Count("id")).order_by("weekday", "hour", "quarter")
data = DataFrame()
weekdays = ["Måndag", "Tisdag", "Onsdag", "Torsdag", "Fredag"]
for timepoint in orders:
data.at[weekdays[timepoint["weekday"] - 1],
time(timepoint["hour"], timepoint["quarter"])] = int(
timepoint["count"])
sns.set_theme()
plt.figure(figsize=(16, 9))
plot = sns.heatmap(data, cbar=False, cmap="YlGnBu")
plot.figure.autofmt_xdate()
buffer = BytesIO()
plot.get_figure().savefig(buffer, format='png')
buffer.seek(0)
return FileResponse(buffer, filename='heatmap.png')
def get_data(self, request: HttpRequest, config: dict):
"""Get graph data"""
if config.get('source', '__custom__') != '__custom__':
func = widget_data.get_data(self, config.get('source')).get(
'function', lambda config: None)
return func(config)
try:
ModelClass = ContentType.objects.get_for_id(config.get(
'model', -1)).model_class()
except ContentType.DoesNotExist:
return None
if not ModelClass:
return None
from django.db.models.functions import ExtractMinute, ExtractHour, ExtractDay, ExtractWeek, ExtractMonth, ExtractYear
interval_field = config.get('interval_field', 'created_at')
query = ModelClass.objects.get_queryset().annotate(
widget_minute=ExtractMinute(interval_field),
widget_hour=ExtractHour(interval_field),
widget_day=ExtractDay(interval_field),
widget_week=ExtractWeek(interval_field),
widget_month=ExtractMonth(interval_field),
widget_year=ExtractYear(interval_field))
if config.get('filters'):
query = filter_queryset_with_user_filters(
query, json.loads(config['filters']))
if config.get('interval_period') == 'minute':
query = query.values('widget_minute', 'widget_hour', 'widget_day',
'widget_month', 'widget_year').order_by(
'-widget_year', '-widget_month',
'-widget_day', '-widget_hour',
'-widget_minute')
elif config.get('interval_period') == 'hour':
query = query.values('widget_hour', 'widget_day', 'widget_month',
'widget_year').order_by(
'-widget_year', '-widget_month',
'-widget_day', '-widget_hour')
elif config.get('interval_period') == 'day':
query = query.values('widget_day', 'widget_month',
'widget_year').order_by(
'-widget_year', '-widget_month',
'-widget_day')
elif config.get('interval_period') == 'week':
query = query.values('widget_week', 'widget_year').order_by(
'-widget_year', '-widget_week')
elif config.get('interval_period') == 'month':
query = query.values('widget_month', 'widget_year').order_by(
'-widget_year', '-widget_month')
elif config.get('interval_period') == 'year':
query = query.values('widget_year').order_by('-widget_year')
query = query.annotate(widget_count=Count('id'))
model_config = models_config.get_config(ModelClass)
only_count = config.get('field', '__count__') == '__count__'
if only_count:
label = model_config.get_verbose_name() + ' ' + str(_('Count'))
else:
query = query.annotate(widget_value=Sum(config['field']))
label = _('Sum of {objects} {field}'.format(
objects=model_config.get_verbose_name_plural(),
field=model_config.get_field(config['field']).verbose_name))
results = list(reversed(query[:30]))
if not results:
return False
def row_to_date(row):
"""Based on row generate a date"""
import datetime
if config.get('interval_period') == 'week':
return datetime.datetime.strptime(
'{}-W{}-1'.format(
row.get('widget_year'),
row.get('widget_week', 1) - 1,
), "%Y-W%W-%w").strftime('%Y-%m-%d %H:%M:%S')
return datetime.datetime(
year=row.get('widget_year'),
month=row.get('widget_month', 1),
day=row.get('widget_day', 1),
hour=row.get('widget_hour', 0),
minute=row.get('widget_minute',
0)).strftime('%Y-%m-%d %H:%M:%S')
datasets = []
y_axes = [{
'id': 'y-axis-2',
'type': 'linear',
'position': 'right' if not only_count else 'left',
'gridLines': {
'drawOnChartArea': False,
},
'ticks': {
'suggestedMax':
float(max([row['widget_count'] for row in results])) *
(1.5 if not only_count else 1.10),
'suggestedMin':
0,
}
}]
if not only_count:
field_renderer = renderer.renderers.get(
type(model_config.get_field(config['field'])),
lambda x: str(x))
datasets.append({
'label':
label,
'backgroundColor':
self.get_color(config.get('color'), 'fill'),
'borderColor':
self.get_color(config.get('color'), 'stroke'),
'pointBorderColor':
self.get_color(config.get('color'), 'stroke'),
'pointBackgroundColor':
self.get_color(config.get('color'), 'stroke'),
'fill':
True,
'pointRadius':
4,
'data': [{
'x': row_to_date(row),
'y': row.get('widget_value'),
'label': field_renderer(row.get('widget_value')),
} for row in results],
'yAxisID':
'y-axis-1',
})
y_axes.append({
'id': 'y-axis-1',
'type': 'linear',
'position': 'left',
'gridLines': {
'drawOnChartArea': False,
},
'ticks': {
'suggestedMax':
float(max([row['widget_value']
for row in results])) * 1.10,
'suggestedMin':
0,
}
})
datasets.append({
'label':
str(_('Number of {objects}')).format(
objects=model_config.get_verbose_name_plural()),
'backgroundColor':
self.get_color(config.get('color'), 'fill')
if only_count else 'rgba(211, 211, 211, 0.2)',
'borderColor':
self.get_color(config.get('color'), 'stroke')
if only_count else 'rgba(211, 211, 211, 1)',
'pointBorderColor':
self.get_color(config.get('color'), 'stroke')
if only_count else 'rgba(211, 211, 211, 1)',
'pointBackgroundColor':
self.get_color(config.get('color'), 'stroke')
if only_count else 'rgba(211, 211, 211, 1)',
'fill':
True,
'pointRadius':
4,
'data': [row.get('widget_count') for row in results],
'yAxisID':
'y-axis-2',
})
return {
'scales': {
'xAxes': [{
'type': 'time',
'autoSkip': True,
'distribution': 'linear',
'time': {
'unit':
config.get('interval_period', 'day'),
'stepSize':
1,
'tooltipFormat':
utils.datetime_format_to_momentjs(
utils.get_datetime_input_format(
date_only=config.get('interval_period') not in
['minute', 'hour']))
},
}],
'yAxes':
y_axes,
},
'data': {
'labels': [row_to_date(row) for row in results],
'datasets': datasets,
}
}
def flight_list_index(request):
username = request.session.get('username')
password = request.session.get('password')
user = authenticate(request, username=username, password=password)
if user is not None:
#Request data retrieved from ajax call
return_flt = request.POST.get('return_flt')
if return_flt == "true":
return_flt = True
else:
return_flt = False
flights_leaving_from = request.POST.get('from')
flights_going_to = request.POST.get('to')
present_date = datetime.now().date()
form_outbound_date = request.POST.get('outbound_date')
form_outbound_date = datetime.strptime(form_outbound_date, "%m/%d/%Y").date()
adult_pax = int(request.POST.get('adult_pax'))
kids_pax = int(request.POST.get('kids_pax'))
infant_pax = int(request.POST.get('infant_pax'))
total_pax = adult_pax + kids_pax + infant_pax
adult_chck = (adult_pax>0)
kids_chck = (kids_pax>0)
infant_chck = (infant_pax>0)
outbound_flights = BookingFlights.objects.filter(orig=flights_leaving_from,dest=flights_going_to,dep_date__date=form_outbound_date).annotate(flight_hrs=ExtractHour(ExpressionWrapper(F('arr_date')-F('dep_date'),output_field=fields.DurationField())),flight_mins=ExtractMinute(ExpressionWrapper(F('arr_date')-F('dep_date'),output_field=fields.DurationField())))
if return_flt:
#print("Return:True)")
form_inbound_date = request.POST.get('inbound_date')
form_inbound_date = datetime.strptime(form_inbound_date, "%m/%d/%Y").date()
#print(form_inbound_date)
inbound_flights = BookingFlights.objects.filter(orig=flights_going_to,dest=flights_leaving_from,dep_date__date=form_inbound_date).annotate(flight_hrs=ExtractHour(ExpressionWrapper(F('arr_date')-F('dep_date'),output_field=fields.DurationField())),flight_mins=ExtractMinute(ExpressionWrapper(F('arr_date')-F('dep_date'),output_field=fields.DurationField())))
#print("Outbound Flights")
#print(outbound_flights)
#print("Inbound Flights")
#print(inbound_flights)
return render(request, 'flight-list-view.html',{'flights_out': outbound_flights,'flights_in':inbound_flights,
'adult_pax':adult_pax, 'kids_pax':kids_pax, 'infant_pax':infant_pax,'adult_chck':adult_chck,
'kids_chck':kids_chck,'infant_chck':infant_chck,'total_pax':total_pax,'return_flt':return_flt})
else:
#print("Outbound Flights")
#print(outbound_flights)
return render(request, 'flight-list-view.html',{'flights_out': outbound_flights, 'adult_pax':adult_pax, 'kids_pax':kids_pax, 'infant_pax':infant_pax,
'adult_chck':adult_chck,'kids_chck':kids_chck,'infant_chck':infant_chck,'total_pax':total_pax,'return_flt':return_flt})
else:
return render(request, 'login.html')