def get_project_insights(query_filters=None):
"""Get data required by the Project Insights tab.
:param django.db.models.Q query_filters: filters insights by given query_filters.
"""
start_date = get_insight_start_date(True)
snapshots = ProjectInsightsSnapshot.objects.filter(
created_at__gte=start_date)
if query_filters:
snapshots = snapshots.filter(query_filters)
insights = (
snapshots
# Truncate to month and add to select list
.annotate(month=TruncMonth("created_at"))
# Group By month
.values("month")
# Select the avg/sum of the grouping
.annotate(completion_avg=Avg("completion"))
.annotate(human_translations_sum=Sum("human_translations"))
.annotate(machinery_sum=Sum("machinery_translations"))
.annotate(new_source_strings_sum=Sum("new_source_strings"))
.annotate(unreviewed_avg=Avg("unreviewed_strings"))
.annotate(peer_approved_sum=Sum("peer_approved"))
.annotate(self_approved_sum=Sum("self_approved"))
.annotate(rejected_sum=Sum("rejected"))
.annotate(new_suggestions_sum=Sum("new_suggestions"))
# Select month and values
.values(
"month",
"completion_avg",
"human_translations_sum",
"machinery_sum",
"new_source_strings_sum",
"unreviewed_avg",
"peer_approved_sum",
"self_approved_sum",
"rejected_sum",
"new_suggestions_sum",
)
.order_by("month")
)
return {
"dates": [convert_to_unix_time(x["month"]) for x in insights],
"translation_activity": {
"completion": [round(x["completion_avg"], 2) for x in insights],
"human_translations":
[x["human_translations_sum"] for x in insights],
"machinery_translations": [x["machinery_sum"] for x in insights],
"new_source_strings":
[x["new_source_strings_sum"] for x in insights],
},
"review_activity": {
"unreviewed": [int(round(x["unreviewed_avg"])) for x in insights],
"peer_approved": [x["peer_approved_sum"] for x in insights],
"self_approved": [x["self_approved_sum"] for x in insights],
"rejected": [x["rejected_sum"] for x in insights],
"new_suggestions": [x["new_suggestions_sum"] for x in insights],
},
}
def post(self, request, *args, **kwargs):
startDate = request.POST.get('startDate', False)
endDate = request.POST.get('endDate', False)
result = {}
if startDate and endDate:
start_date = datetime.strptime(startDate, '%d/%m/%Y %I:%M %p')
end_date = datetime.strptime(endDate, '%d/%m/%Y %I:%M %p')
alert_with_datetime = Alert.objects.all().\
annotate(datetime=RawSQL('FROM_UNIXTIME(timestamp)', [],
output_field=DateTimeField())).\
filter(datetime__gte=start_date, datetime__lte=end_date)
diff = end_date - start_date
if diff.days == 0:
trunc_alerts = alert_with_datetime.annotate(
hour=TruncHour('datetime')).values('hour').\
annotate(count=Count('id')).values_list('hour', 'count')
trunc_alerts = list(trunc_alerts)
new_trunc_alerts = []
for dt in rrule.rrule(rrule.HOURLY,
dtstart=start_date,
until=end_date):
new_trunc_alerts.append([dt, 0])
for new_alert in new_trunc_alerts:
for alert in trunc_alerts:
if alert[0].replace(tzinfo=None) == new_alert[0]:
new_alert[1] = alert[1]
result['by_time'] = {
'label': 'Number of OSSEC alerts in 24 hours',
'count': [item[1] for item in new_trunc_alerts],
'data':
[item[0].strftime("%H") for item in new_trunc_alerts]
}
elif diff.days > 0 and diff.days <= 31:
trunc_alerts = alert_with_datetime.annotate(
date=TruncDay('datetime')).values('date').\
annotate(count=Count('id')).values_list('date', 'count')
trunc_alerts = list(trunc_alerts)
new_trunc_alerts = []
for dt in rrule.rrule(rrule.DAILY,
dtstart=start_date,
until=end_date):
new_trunc_alerts.append([dt, 0])
for new_alert in new_trunc_alerts:
for alert in trunc_alerts:
if alert[0].replace(tzinfo=None) == new_alert[0]:
new_alert[1] = alert[1]
result['by_time'] = {
'label':
'Number of OSSEC alerts per days',
'count': [item[1] for item in new_trunc_alerts],
'data':
[item[0].strftime("%d/%m") for item in new_trunc_alerts]
}
elif diff.days > 31 and diff.days <= 365:
trunc_alerts = alert_with_datetime.annotate(
date=TruncDay('datetime')).values('date').\
annotate(count=Count('id')).values_list('date', 'count')
trunc_alerts = list(trunc_alerts)
new_trunc_alerts = OrderedDict()
for dt in rrule.rrule(rrule.DAILY,
dtstart=start_date,
until=end_date):
isocalendar = dt.isocalendar()
week = 'W{0}/{1}'.format(isocalendar[1], isocalendar[0])
if week not in new_trunc_alerts:
new_trunc_alerts[week] = {'start_date': dt, 'count': 0}
else:
new_trunc_alerts[week]['end_date'] = dt
for alert in trunc_alerts:
isocalendar = alert[0].isocalendar()
week = 'W{0}/{1}'.format(isocalendar[1], isocalendar[0])
new_trunc_alerts[week]['count'] = \
new_trunc_alerts[week]['count'] + alert[1]
result['by_time'] = {
'label': 'Number of OSSEC alerts per weeks',
'count':
[item['count'] for item in new_trunc_alerts.values()],
'data': list(new_trunc_alerts.keys())
}
else:
trunc_alerts = alert_with_datetime.annotate(
month=TruncMonth('datetime')).values('month').\
annotate(count=Count('id')).values_list('month', 'count')
trunc_alerts = list(trunc_alerts)
new_trunc_alerts = []
for dt in rrule.rrule(rrule.MONTHLY,
dtstart=start_date,
until=end_date):
new_trunc_alerts.append([dt, 0])
for new_alert in new_trunc_alerts:
for alert in trunc_alerts:
if alert[0].replace(tzinfo=None) == new_alert[0]:
new_alert[1] = alert[1]
result['by_time'] = {
'label':
'Number of OSSEC alerts per months',
'count': [item[1] for item in new_trunc_alerts],
'data':
[item[0].strftime("M%m/%Y") for item in new_trunc_alerts]
}
alert_by_cate = alert_with_datetime.select_related('rule').values(
'rule__rule_id').annotate(count=Count('id')).order_by('-count').\
values_list('rule__rule_id', 'count')
# alert_by_cate = alert_by_cate[:10] if alert_by_cate.count() > 10 else \
# alert_by_cate
cate_name_count = {}
for alert in alert_by_cate:
cat_ids = SignatureCategoryMapping.objects.filter(
rule_id=alert[0]).values_list('cat_id', flat=True)
categories = Category.objects.filter(cat_id__in=cat_ids)
for cate in categories:
# if len(cate_name_count) > 10:
# break
if cate.cat_name not in cate_name_count:
cate_name_count[cate.cat_name] = alert[1]
else:
cate_name_count[cate.cat_name] = \
cate_name_count[cate.cat_name] + alert[1]
by_cate_data = []
by_cate_count = []
for key, value in sorted(cate_name_count.items(),
key=lambda x: x[1],
reverse=True):
if len(by_cate_data) >= 10:
break
by_cate_data.append(key)
by_cate_count.append(value)
result['by_cate'] = {
'label': 'Top categories of OSSEC alerts',
'count': by_cate_count,
'data': by_cate_data,
}
return HttpResponse(json.dumps(result))
def get(self, request, *args, **kwargs):
"""
Returns the Server's signed information and some additional data for a nice dashboard
:param request:
:type request:
:param args:
:type args:
:param kwargs:
:type kwargs:
:return:
:rtype:
"""
info = settings.SIGNATURE.copy()
info['user_count_active'] = User.objects.filter(is_active=True).count()
info['user_count_total'] = User.objects.count()
info['token_count_total'] = Token.objects.filter(
valid_till__gt=timezone.now(), active=True).count()
info['token_count_device'] = Token.objects.filter(
valid_till__gt=timezone.now(),
active=True).values('device_fingerprint').annotate(
num_sessions=Count('device_fingerprint')).count()
info['token_count_user'] = Token.objects.filter(
valid_till__gt=timezone.now(),
active=True).values('user_id').annotate(
num_sessions=Count('user_id')).count()
monthly_registrations = User.objects.annotate(
month=TruncMonth('create_date')).values('month').annotate(
counter=Count('id')).values('month',
'counter').order_by('month')
registrations_over_month = []
count_total_month = 0
for r in monthly_registrations:
count_total_month = count_total_month + r['counter']
registrations_over_month.append({
'count_new':
r['counter'],
'count_total':
count_total_month,
'month':
r['month'].strftime('%b %y')
})
info['registrations_over_month'] = registrations_over_month
daily_registrations_offset = User.objects.filter(
create_date__lt=timezone.now() - timedelta(days=16)).count()
daily_registrations = User.objects.filter(
create_date__gte=timezone.now() - timedelta(days=16)).annotate(
day=TruncDay('create_date')).values('day').annotate(
count_new=Count('id')).values('day',
'count_new').order_by('day')
end_date = timezone.now()
d = end_date - timedelta(days=16)
registrations_over_day_index = {}
while d <= end_date:
registrations_over_day_index[d.strftime("%Y-%m-%d")] = {
'date': d.strftime("%Y-%m-%d"),
'count_new': 0,
'count_total': 0,
'weekday': d.strftime("%a"),
}
d += timedelta(days=1)
for r in daily_registrations:
registrations_over_day_index[r['day'].strftime(
'%Y-%m-%d')]['count_new'] = r['count_new']
registrations_over_day = []
for k in sorted(registrations_over_day_index):
daily_registrations_offset = daily_registrations_offset + registrations_over_day_index[
k]['count_new']
registrations_over_day_index[k][
'count_total'] = daily_registrations_offset
registrations_over_day.append(registrations_over_day_index[k])
info['registrations_over_day'] = registrations_over_day
fileserver_cluster_members = Fileserver_Cluster_Members.objects.\
filter(valid_till__gt=timezone.now() - timedelta(seconds=settings.FILESERVER_ALIVE_TIMEOUT)).\
select_related('fileserver_cluster').\
only('create_date', 'fileserver_cluster__title', 'hostname', 'version')
fileserver = []
for r in fileserver_cluster_members:
fileserver.append({
'create_date':
r.create_date,
'fileserver_cluster_title':
r.fileserver_cluster.title,
'hostname':
r.hostname,
'version':
r.version,
})
info['fileserver'] = fileserver
past_registrations = User.objects.order_by('-create_date')[:10]
registrations = []
for r in past_registrations:
registrations.append({
'date': r.create_date,
'username': r.username,
'active': r.is_active,
})
info['registrations'] = registrations
return Response(info, status=status.HTTP_200_OK)
def get_context_data(self, **kwargs):
last_work = models.Work.objects.first()
works = models.Work.objects.filter(category=self.category).exclude(type=models.Types.HOLIDAY)
if last_work:
## GET parameter ?more is a multiple of 3 month
offset = last_work.dateFrom-datetime.timedelta(days=31*3*self.show_more)
works = works.filter(dateFrom__gte=offset)
work_sum = models.Work.objects.filter(category=self.category).exclude(type=models.Types.HOLIDAY).annotate(month=TruncMonth('dateFrom')).values('month').annotate(duration_sum=Sum('duration')/60/60).order_by('-month')[:6]
holiday_sum = models.Work.objects.filter(category=self.category).filter(type=models.Types.HOLIDAY).filter(dateFrom__year=timezone.now().year).aggregate(holiday_sum=((Sum('duration')) / Value(60*60)))['holiday_sum'] or 0
holiday_total = 30
context = super().get_context_data(**kwargs)
context['user'] = self.request.user
#context['modals'] = self.modal_add
#context['notify'] = self.notify
context['works'] = works
context['current'] = models.Work.objects.filter(category=self.category).filter(type=models.Types.WORK).filter(dateTo__isnull=True).first()
context['graphData'] = {
'holiday': {'data': [holiday_sum, holiday_total-holiday_sum], 'label': ['genommen', 'verbleibend']},
'work': {'data': [w["duration_sum"] for w in work_sum], 'label': [l["month"].strftime("%B %Y") for l in work_sum]}
}
context['holidays'] = models.Work.objects.filter(category=self.category).filter(type=models.Types.HOLIDAY).all()
context['categories'] = models.Category.objects.all()
context['current_category'] = self.category
context['form'] = self.form
context['more'] = self.show_more + 1
return context
def get(self, request):
data = request.GET.copy()
self.context_dict = self.set_duration(data, self.context_dict, -2)
from_days = get_days_in_month(int(data['from_year']),
int(data['from_month']))
from_dt = '%04d-%02d-%02d 23:59:59' % (int(
data['from_year']), int(data['from_month']), from_days)
until_dt = '%04d-%02d-01 00:00:00' % (int(
data['to_year']), int(data['to_month']))
from_dt = datetime.strptime(from_dt, '%Y-%m-%d %H:%M:%S') #23:59:59
until_dt = datetime.strptime(until_dt,
'%Y-%m-%d %H:%M:%S') # 00:00:00'
self.context_dict['view_name'] = 'report'
self.context_dict['action'] = 'component'
log.info(' from data: %s , to date: %s ' % (from_dt, until_dt))
component_report_list = []
date_filter = Q(incoming_date__lte=from_dt,
incoming_date__gte=until_dt)
try:
args = ()
kwargs = {}
kwargs['product_id'] = 2
# -- Get distinct months
month_list = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).\
annotate(month=TruncMonth('incoming_date')).values('month').distinct().order_by('month')
#log.info(month_list)
#---get the unique months Month
data = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).\
annotate(month=TruncMonth('incoming_date')).values('month').\
annotate(case_count=Count('*')).values('month', 'component_id','case_count').order_by('month')
# Get the component details
components = Component.objects.filter(product_id=2,
active=True).order_by('name')
#log.info(components)
# -- Total
#data = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).values('component_id').\
#annotate(case_count=Count('*')).order_by('component_id')
#log.info(monthly)
for component in components:
report = {}
report['component'] = component.name
report['id'] = component.id
total = 0
monthly_count_list = []
if month_list is not None and len(month_list) > 0:
monthly_dict = {}
for month in month_list:
case_count = 0
if data is not None and len(data) > 0:
for item in data:
monthly_dict = {}
if item['component_id'] == component.id and month[
'month'] == item['month']:
log.info(
'Month: %s component: %s , count: %s '
% (item['month'], component.id,
item['case_count']))
monthly_dict['month'] = month['month']
case_count = item['case_count']
total = total + case_count
monthly_dict['case_count'] = case_count
monthly_count_list.append(monthly_dict)
if case_count == 0:
monthly_dict['month'] = month['month']
monthly_dict['case_count'] = case_count
monthly_count_list.append(monthly_dict)
report['monthly_count'] = monthly_count_list
report['total'] = total
if total > 0:
report['average'] = math.ceil(total / len(month_list))
else:
report['average'] = 0
component_report_list.append(report)
#log.info(component_report_list)
self.context_dict['month_list'] = month_list
self.context_dict['component_report_list'] = component_report_list
except Exception, ex:
log.exception("SQL Error Encountered in jd search. " + str(ex))
def get(self, request):
"""
Return a list of all contracts.
"""
# Calculating total tender
country_code = self.request.GET.get("country", None)
buyer_id = self.request.GET.get("buyer")
supplier_id = self.request.GET.get("supplier")
filter_args = {}
exclude_args = {"status": "canceled"}
if country_code:
country_code = str(country_code).upper()
filter_args["country__country_code_alpha_2"] = country_code
if buyer_id:
filter_args = add_filter_args("buyer", buyer_id, filter_args)
if supplier_id:
filter_args = add_filter_args("supplier", supplier_id, filter_args)
total_country_tender_amount = (
Tender.objects.filter(**filter_args)
.exclude(**exclude_args)
.aggregate(usd=Sum("contract_value_usd"), local=Sum("contract_value_local"))
)
bar_chart = (
Tender.objects.filter(**filter_args)
.exclude(**exclude_args)
.values("procurement_procedure")
.annotate(usd=Sum("contract_value_usd"), local=Sum("contract_value_local"))
)
selective_sum_local = 0
limited_sum_local = 0
open_sum_local = 0
direct_sum_local = 0
limited_total = 0
open_total = 0
selective_total = 0
direct_total = 0
not_identified_total = 0
not_identified_sum_local = 0
for i in bar_chart:
if i["procurement_procedure"] == "selective":
selective_total = i["usd"]
selective_sum_local = i["local"]
elif i["procurement_procedure"] == "limited":
limited_total = i["usd"]
limited_sum_local = i["local"]
elif i["procurement_procedure"] == "open":
open_total = i["usd"]
open_sum_local = i["local"]
elif i["procurement_procedure"] == "direct":
direct_total = i["usd"]
direct_sum_local = i["local"]
elif i["procurement_procedure"] == "not_identified":
not_identified_total = i["usd"]
not_identified_sum_local = i["local"]
line_chart = (
Tender.objects.filter(**filter_args)
.exclude(**exclude_args)
.annotate(month=TruncMonth("contract_date"))
.values("month")
.annotate(usd=Sum("contract_value_usd"), local=Sum("contract_value_local"))
.order_by("-month")
)
line_chart_local_list = [{"date": i["month"], "value": i["local"]} for i in line_chart]
line_chart_list = [{"date": i["month"], "value": i["usd"]} for i in line_chart]
result = {
"usd": {
"total": total_country_tender_amount["usd"],
"line_chart": line_chart_list,
"bar_chart": [
{"method": "open", "value": open_total},
{"method": "limited", "value": limited_total},
{"method": "selective", "value": selective_total},
{"method": "direct", "value": direct_total},
{"method": "not_identified", "value": not_identified_total},
],
},
"local": {
"total": total_country_tender_amount["local"],
"line_chart": line_chart_local_list,
"bar_chart": [
{"method": "open", "value": open_sum_local},
{"method": "limited", "value": limited_sum_local},
{"method": "selective", "value": selective_sum_local},
{"method": "direct", "value": direct_sum_local},
{"method": "not_identified", "value": not_identified_sum_local},
],
},
}
return JsonResponse(result)
def list(self, request, *args, **kwargs):
response = super(ListAPIView, self).list(request, args, kwargs)
try:
current_year = Table.objects.last().bill.month.year
except:
current_year = now.year
if self.request.GET.get('property'):
search_query = self.request.GET.get('property')
query_set = Table.objects.filter(
room__name__icontains=str(search_query))
try:
response.data['property'] = query_set.first().room.name
except:
response.data['property'] = self.request.GET.get('property')
else:
query_set = Table.objects.all()
response.data['property'] = ''
response.data['period'] = current_year
queryset = query_set.\
filter(bill__month__year=str(current_year)).\
exclude(tax__exact='-1').annotate(month=TruncMonth('bill__month')).\
values('month').annotate(total_amount=Sum('amount')).annotate(total_tax=Sum('tax')).values('month', 'total_amount', 'total_tax', 'room__name')
totalTax = queryset.aggregate(Sum('total_tax'))["total_tax__sum"]
response.data['totalTax'] = totalTax
totalAmount = queryset.aggregate(
Sum('total_amount'))["total_amount__sum"]
response.data['totalAmount'] = totalAmount
queryset_all = query_set.exclude(tax__exact='-1').\
annotate(month=TruncMonth('bill__month'))\
.values('month').annotate(total_amount=Sum('amount')).annotate(total_tax=Sum('tax')).values('month', 'total_amount', 'total_tax', 'room__name')
if self.request.GET.get('month_from') and self.request.GET.get(
'month_to'):
month_from = self.request.GET.get('month_from')
month_to = self.request.GET.get('month_to')
queryset = queryset_all.filter(
bill__month__range=[str(month_from),
str(month_to)])
totalTax = queryset.aggregate(Sum('total_tax'))["total_tax__sum"]
response.data['totalTax'] = totalTax
totalAmount = queryset.aggregate(
Sum('total_amount'))["total_amount__sum"]
response.data['totalAmount'] = totalAmount
if self.request.GET.get('month') and self.request.GET.get('year'):
month = self.request.GET.get('month')
year = self.request.GET.get('year')
queryset = queryset_all.filter(bill__month__month=str(month),
bill__month__year=str(year))
totalTax = queryset.aggregate(Sum('total_tax'))["total_tax__sum"]
response.data['totalTax'] = totalTax
totalAmount = queryset.aggregate(
Sum('total_amount'))["total_amount__sum"]
response.data['totalAmount'] = totalAmount
if self.request.GET.get('year') and not self.request.GET.get('month'):
year = self.request.GET.get('year')
queryset = queryset_all.filter(bill__month__year=str(year))
totalTax = queryset.aggregate(Sum('total_tax'))["total_tax__sum"]
response.data['totalTax'] = totalTax
totalAmount = queryset.aggregate(
Sum('total_amount'))["total_amount__sum"]
response.data['totalAmount'] = totalAmount
page_size = 'page_size'
if self.request.GET.get(page_size):
pagination.PageNumberPagination.page_size = self.request.GET.get(
page_size)
else:
pagination.PageNumberPagination.page_size = 10
if not totalTax:
response.data['totalTax'] = '0.00'
if not totalAmount:
response.data['totalAmount'] = '0.00'
return response
def statistics(request):
# Crea objeto con la tabla de noticias
news = Noticia.objects.order_by('-contador_visitas')
universidades = Universidad.objects.order_by('-alias')
# nltk.download('stopwords')
date = mostViewed()
estadisticas_universidades = []
total_noticias = 0
total_visitas = 0
for universidad in universidades:
# Si la universidad no tiene noticias, pasa a la siguiente
if news.filter(id_universidad__alias=universidad.alias).count() == 0:
continue
n = news.filter(id_universidad__alias=universidad.alias)
total_noticias += n.count()
total_visitas += (n.aggregate(Sum('contador_visitas')))['contador_visitas__sum']
try:
estadisticas_universidades.append({
'nombre_corto': universidad.alias,
'nombre_largo': universidad.nombre,
# Suma de todas las visitas por universidad
'total_visitas': (n.aggregate(Sum('contador_visitas')))['contador_visitas__sum'],
# Noticia más vista de todo el tiempo
'noticia_mas_vista': n.latest('contador_visitas'),
# Noticia más reciente últimas 2 semanas
'noticia_mas_vista_reciente': n.filter(fecha__range=[date['last_date'], date['current_date']]).latest('contador_visitas'),
# Cantidad de noticias totales
'total_noticias': n.count(),
# Noticias por mes, colocar en grafico
'noticias_por_mes': n.annotate(month=TruncMonth('fecha')).values('month').annotate(total=Count('id_noticia')).order_by(),
# Noticias por año
'noticias_por_anio': n.annotate(year=TruncYear('fecha')).values('year').annotate(total=Count('id_noticia')).order_by(),
# Nube de palabras
# 'nube_de_palabras': word_cloud(list(n.values('titulo', 'bajada'))),
# 'test': n.extra(select={'day': 'date( fecha )'}).values('day').annotate(noticias=Count('id_noticia')).order_by('fecha'),
})
# now = datetime.now()
# for year in range(2017, now.year):
# print(year)
# print("-------------------------")
# print(estadisticas_universidades['nube_de_palabras'])
# print("-------------------------")
except Exception as e:
print("-------------------------")
print(universidad.alias)
print(e)
print("-------------------------")
# agregar una estadisticas que sea la tasa de crecimiento mensual por universidad y otra de la plataforma en cantidad de noticias
estadisticas_generales = {}
estadisticas_generales['estadisticas_fecha'] = Noticia.objects.extra(select={'day': 'date( fecha )'}).values('day').annotate(noticias=Count('id_noticia'))
estadisticas_generales['total_noticias'] = total_noticias
estadisticas_generales['total_visitas'] = total_visitas
return render(request, 'news/statistics.html', {
'estadisticas_universidades':estadisticas_universidades,
'estadisticas_generales':estadisticas_generales
})
def get_event_stats(self, request):
"""
Given a method parameter, returns event data in the specified format. Can
return 'count' statistics or the original Event data.
TODO: stats by device type, geographic region, time of day/week
"""
output = []
queryset = self.filter_by_link_or_user()
time = request.query_params.get('time', None)
method = request.query_params.get('method', None)
# Gets all click counts per link for the given user. Also retrieves empty (unclicked user links)
if method is not None:
if method.lower() == 'links':
result = []
queryset = queryset.values(
'link', 'date').annotate(count=Count('link'))
links_queryset = Link.objects.all()
links_queryset = Link.objects.filter(
creator__username=self.request.user)
link_ids = []
for q in queryset:
link = LinkSerializer(Link.objects.get(pk=q['link']),
many=False).data
link_ids.append(link['id'])
result.append({
'id': link['id'],
'title': link['text'],
'date': link['created'],
'url': link['url'],
'img': link['image'],
'media_prefix': link['media_prefix'],
'count': q['count'],
})
for link in links_queryset:
if link.id not in link_ids:
result.append({
'id': link.id,
'title': link.text,
'date': link.created,
'url': link.url,
'img': link.image if link.image else None,
'media_prefix': link.media_prefix,
'count': 0,
})
(link_csv, link_json) = self.generate_links_csv(result)
return Response({
'data': result,
'raw_csv': link_csv,
'raw': link_json,
})
if time is not None:
time = time.lower()
if time not in ['daily', 'weekly', 'monthly', 'yearly']:
if time is None:
queryset = self.filter_by_month_or_year(queryset)
else:
queryset = self.filter_by_time(queryset)
if method is not None and method.lower() == 'count':
return Response({'count': queryset.count()})
serializer = self.serializer_class(queryset, many=True)
return Response(serializer.data)
queryset = self.filter_by_time(queryset)
# generate csv data
(raw_data, raw_json) = self.generate_csv(queryset)
queryset = {
'daily': queryset.annotate(period=TruncDate('date')),
'weekly': queryset.annotate(period=TruncWeek('date')),
'monthly': queryset.annotate(period=TruncMonth('date')),
'yearly': queryset.annotate(period=TruncYear('date')),
}.get(time).order_by('period')
# Fetches Event entry by primary key
output = {}
queryset = queryset \
.values('period') \
.annotate(event_ids=ArrayAgg('id')) \
.values('period', 'event_ids')
for q in queryset:
data = list(
EventSerializer(Event.objects.get(pk=id), many=False).data
for id in q['event_ids'])
output[q['period']] = {
'period': q['period'],
'count': len(data),
'events': data,
}
daily_data = self.get_daily_data(output)
return Response({
'data': daily_data,
'raw_csv': raw_data,
'raw': raw_json
})
def update_summaries(self,
last_tracker_pk=0,
last_points_pk=0,
fromstart=False):
SettingProperties.set_string('oppia_summary_cron_last_run',
timezone.now())
# get last tracker and points PKs to be processed
# (to avoid leaving some out if new trackers arrive while processing)
try:
newest_tracker_pk = Tracker.objects.latest('id').id
newest_points_pk = Points.objects.latest('id').id
except Tracker.DoesNotExist:
print("Tracker table is empty. Aborting cron...")
SettingProperties.delete_key('oppia_summary_cron_lock')
return
except Points.DoesNotExist:
newest_points_pk = last_points_pk
print('Last tracker processed: %d\nNewest tracker: %d\n'
% (last_tracker_pk,
newest_tracker_pk))
if last_tracker_pk >= newest_tracker_pk:
print('No new trackers to process. Aborting cron...')
SettingProperties.delete_key('oppia_summary_cron_lock')
return
first_tracker = (last_tracker_pk == 0)
first_points = (last_points_pk == 0)
# If we are calculating from the start, delete previous summary
# calculations
if first_tracker:
UserCourseSummary.objects.all().delete()
CourseDailyStats.objects.all().delete()
if first_points:
UserPointsSummary.objects.all().delete()
# get different (distinct) user/courses involved
user_courses = Tracker.objects \
.filter(pk__gt=last_tracker_pk, pk__lte=newest_tracker_pk) \
.exclude(course__isnull=True) \
.values('course', 'user').distinct()
total_users = user_courses.count()
print('%d different user/courses to process.' % total_users)
count = 1
for uc_tracker in user_courses:
print('processing user/course trackers... (%d/%d)' % (count,
total_users))
user = User.objects.get(pk=uc_tracker['user'])
course = Course.objects.get(pk=uc_tracker['course'])
user_course, created = UserCourseSummary.objects \
.get_or_create(course=course, user=user)
user_course.update_summary(
last_tracker_pk=last_tracker_pk,
last_points_pk=last_points_pk,
newest_tracker_pk=newest_tracker_pk,
newest_points_pk=newest_points_pk)
count += 1
# get different (distinct) courses/dates involved
course_daily_type_logs = Tracker.objects \
.filter(pk__gt=last_tracker_pk, pk__lte=newest_tracker_pk) \
.exclude(course__isnull=True) \
.annotate(day=TruncDay('tracker_date'),
month=TruncMonth('tracker_date'),
year=TruncYear('tracker_date')) \
.values('course', 'day', 'month', 'year', 'type') \
.annotate(total=Count('type')) \
.order_by('day')
total_logs = course_daily_type_logs.count()
print('%d different courses/dates/types to process.' % total_logs)
count = 0
for type_log in course_daily_type_logs:
course = Course.objects.get(pk=type_log['course'])
stats, created = CourseDailyStats.objects \
.get_or_create(course=course,
day=type_log['day'],
type=type_log['type'])
stats.total = (0 if first_tracker else stats.total) \
+ type_log['total']
stats.save()
count += 1
print(count)
# get different (distinct) search logs involved
search_daily_logs = Tracker.objects \
.filter(pk__gt=last_tracker_pk,
pk__lte=newest_tracker_pk,
user__is_staff=False,
type='search') \
.annotate(day=TruncDay('tracker_date'),
month=TruncMonth('tracker_date'),
year=TruncYear('tracker_date')) \
.values('day', 'month', 'year') \
.annotate(total=Count('id')) \
.order_by('day')
print('%d different search/dates to process.'
% search_daily_logs.count())
for search_log in search_daily_logs:
stats, created = CourseDailyStats.objects \
.get_or_create(course=None,
day=search_log['day'],
type='search')
stats.total = (0 if first_tracker else stats.total) \
+ search_log['total']
stats.save()
# get different (distinct) user/points involved
users_points = Points.objects \
.filter(pk__gt=last_points_pk, pk__lte=newest_points_pk) \
.values('user').distinct()
total_users = users_points.count()
print('%d different user/points to process.' % total_users)
for user_points in users_points:
user = User.objects.get(pk=user_points['user'])
points, created = UserPointsSummary.objects \
.get_or_create(user=user)
points.update_points(last_points_pk=last_points_pk,
newest_points_pk=newest_points_pk)
self.update_daily_active_users(last_tracker_pk,
newest_tracker_pk,
fromstart)
# update last tracker and points PKs with the last one processed
SettingProperties.objects \
.update_or_create(key='last_tracker_pk',
defaults={"int_value":
newest_tracker_pk})
SettingProperties.objects.update_or_create(key='last_points_pk',
defaults={"int_value":
newest_points_pk})
SettingProperties.delete_key('oppia_summary_cron_lock')
def get_client_billing_control_pivotable_data(filter_on_subsidiary=None, filter_on_company=None,
filter_on_lead=None, only_active=False):
"""Compute pivotable to check lead/mission billing."""
# local import to avoid circurlar weirdness
ClientBill = apps.get_model("billing", "ClientBill")
BillDetail = apps.get_model("billing", "BillDetail")
BillExpense = apps.get_model("billing", "BillExpense")
Lead = apps.get_model("leads", "Lead")
Expense = apps.get_model("expense", "Expense")
Consultant = apps.get_model("people", "Consultant")
data = []
bill_state = ("1_SENT", "2_PAID") # Only consider clients bills in those status
leads = Lead.objects.all()
if filter_on_subsidiary:
leads = leads.filter(subsidiary=filter_on_subsidiary)
if filter_on_company:
leads = leads.filter(client__organisation__company=filter_on_company)
if filter_on_lead:
leads = leads.filter(id=filter_on_lead.id)
if only_active:
leads = leads.filter(mission__active=True).distinct()
leads = leads.select_related("client__organisation__company",
"business_broker__company", "subsidiary")
for lead in leads:
lead_data = {_("deal id"): lead.deal_id,
_("client organisation"): str(lead.client.organisation),
_("client company"): str(lead.client.organisation.company),
_("broker"): str(lead.business_broker or _("Direct")),
_("subsidiary") :str(lead.subsidiary),
_("responsible"): str(lead.responsible),
_("consultant"): "-"}
# Add legacy bills non related to specific mission (ie. not using pydici billing, just header and pdf payload)
legacy_bills = ClientBill.objects.filter(lead=lead, state__in=bill_state).annotate(Count("billdetail"), Count("billexpense")).filter(billdetail__count=0, billexpense__count=0)
for legacy_bill in legacy_bills:
legacy_bill_data = lead_data.copy()
legacy_bill_data[_("amount")] = - float(legacy_bill.amount or 0)
legacy_bill_data[_("month")] = legacy_bill.creation_date.replace(day=1).isoformat()
legacy_bill_data[_("type")] = _("Service bill")
legacy_bill_data[_("mission")] = "-"
mission = lead.mission_set.first()
if mission: # default to billing mode of first mission. Not 100% accurate...
legacy_bill_data[_("billing mode")] = mission.get_billing_mode_display()
data.append(legacy_bill_data)
# Add chargeable expense
expenses = Expense.objects.filter(lead=lead, chargeable=True)
bill_expenses = BillExpense.objects.filter(bill__lead=lead).exclude(expense_date=None)
for qs, label, way in ((expenses, _("Expense"), 1), (bill_expenses, _("Expense bill"), -1)):
qs = qs.annotate(month=TruncMonth("expense_date")).order_by("month").values("month")
for month, amount in qs.annotate(Sum("amount")).values_list("month", "amount__sum"):
expense_data = lead_data.copy()
expense_data[_("month")] = month.isoformat()
expense_data[_("type")] = label
expense_data[_("billing mode")] = _("Chargeable expense")
expense_data[_("amount")] = float(amount) * way
data.append(expense_data)
# Add new-style client bills and done work per mission
for mission in lead.mission_set.all().select_related("responsible"):
mission_data = lead_data.copy()
mission_data[_("mission")] = mission.short_name()
mission_data[_("responsible")] = str(mission.responsible or mission.lead.responsible)
mission_data[_("billing mode")] = mission.get_billing_mode_display()
# Add fixed price bills
if mission.billing_mode == "FIXED_PRICE":
for billDetail in BillDetail.objects.filter(mission=mission, bill__state__in=bill_state):
mission_fixed_price_data = mission_data.copy()
mission_fixed_price_data[_("month")] = billDetail.bill.creation_date.replace(day=1).isoformat()
mission_fixed_price_data[_("type")] = _("Service bill")
mission_fixed_price_data[_("amount")] = -float(billDetail.amount or 0)
data.append((mission_fixed_price_data))
# Add done work and time spent bills
consultants = Consultant.objects.filter(timesheet__mission=mission).distinct()
for month in mission.timesheet_set.dates("working_date", "month", order="ASC"):
next_month = nextMonth(month)
for consultant in consultants:
mission_month_consultant_data = mission_data.copy()
turnover = float(mission.done_work_period(month, next_month, include_external_subcontractor=True,
include_internal_subcontractor=True,
filter_on_consultant=consultant)[1])
mission_month_consultant_data[_("consultant")] = str(consultant)
mission_month_consultant_data[_("month")] = month.isoformat()
mission_month_consultant_data[_("amount")] = turnover
mission_month_consultant_data[_("type")] = _("Done work")
data.append(mission_month_consultant_data)
if mission.billing_mode == "TIME_SPENT":
# Add bills for time spent mission
mission_month_consultant_data = mission_month_consultant_data.copy()
billed = BillDetail.objects.filter(mission=mission, consultant=consultant,
month=month, bill__state__in=bill_state)
billed = float(billed.aggregate(Sum("amount"))["amount__sum"] or 0)
mission_month_consultant_data[_("amount")] = -billed
mission_month_consultant_data[_("type")] = _("Service bill")
data.append(mission_month_consultant_data)
return json.dumps(data)
def search(request):
q = request.GET.get('q', '').strip()
start = time.time()
query = None
rank_annotation = None
if q:
query = SearchQuery(q)
rank_annotation = SearchRank(models.F('search_document'), query)
selected_tags = request.GET.getlist('tag')
excluded_tags = request.GET.getlist('exclude.tag')
selected_type = request.GET.get('type', '')
selected_year = request.GET.get('year', '')
selected_month = request.GET.get('month', '')
values = ['pk', 'type', 'created']
if q:
values.append('rank')
def make_queryset(klass, type_name):
qs = klass.objects.annotate(
type=models.Value(type_name, output_field=models.CharField()))
if selected_year:
qs = qs.filter(created__year=int(selected_year))
if selected_month:
qs = qs.filter(created__month=int(selected_month))
if q:
qs = qs.filter(search_document=query)
qs = qs.annotate(rank=rank_annotation)
for tag in selected_tags:
qs = qs.filter(tags__tag=tag)
for exclude_tag in excluded_tags:
qs = qs.exclude(tags__tag=exclude_tag)
return qs.order_by()
# Start with a .none() queryset just so we can union stuff onto it
qs = Entry.objects.annotate(
type=models.Value('empty', output_field=models.CharField()))
if q:
qs = qs.annotate(rank=rank_annotation)
qs = qs.values(*values).none()
type_counts_raw = {}
tag_counts_raw = {}
year_counts_raw = {}
month_counts_raw = {}
for klass, type_name in (
(Entry, 'entry'),
(Blogmark, 'blogmark'),
(Quotation, 'quotation'),
):
if selected_type and selected_type != type_name:
continue
klass_qs = make_queryset(klass, type_name)
type_count = klass_qs.count()
if type_count:
type_counts_raw[type_name] = type_count
for tag, count in Tag.objects.filter(**{
'%s__in' % type_name: klass_qs
}).annotate(n=models.Count('tag')).values_list('tag', 'n'):
tag_counts_raw[tag] = tag_counts_raw.get(tag, 0) + count
for row in klass_qs.order_by().annotate(
year=TruncYear('created')).values('year').annotate(
n=models.Count('pk')):
year_counts_raw[row['year']] = year_counts_raw.get(row['year'],
0) + row['n']
# Only do month counts if a year is selected
if selected_year:
for row in klass_qs.order_by().annotate(
month=TruncMonth('created')).values('month').annotate(
n=models.Count('pk')):
month_counts_raw[row['month']] = month_counts_raw.get(
row['month'], 0) + row['n']
qs = qs.union(klass_qs.values(*values))
if q:
qs = qs.order_by('-rank')
else:
qs = qs.order_by('-created')
type_counts = sorted([{
'type': type_name,
'n': value
} for type_name, value in list(type_counts_raw.items())],
key=lambda t: t['n'],
reverse=True)
tag_counts = sorted([{
'tag': tag,
'n': value
} for tag, value in list(tag_counts_raw.items())],
key=lambda t: t['n'],
reverse=True)[:40]
year_counts = sorted([{
'year': year,
'n': value
} for year, value in list(year_counts_raw.items())],
key=lambda t: t['year'])
month_counts = sorted([{
'month': month,
'n': value
} for month, value in list(month_counts_raw.items())],
key=lambda t: t['month'])
paginator = Paginator(qs, 30)
page_number = request.GET.get('page') or '1'
try:
page = paginator.page(page_number)
except PageNotAnInteger:
raise Http404
except EmptyPage:
raise Http404
results = []
for obj in load_mixed_objects(page.object_list):
results.append({
'type': obj.original_dict['type'],
'rank': obj.original_dict.get('rank'),
'obj': obj,
})
end = time.time()
selected = {
'tags':
selected_tags,
'year':
selected_year,
'month':
selected_month,
'type':
selected_type,
'month_name':
MONTHS_3_REV_REV.get(selected_month and int(selected_month) or '',
'').title(),
}
# Remove empty keys
selected = {key: value for key, value in list(selected.items()) if value}
# Dynamic title
noun = {
'quotation': 'Quotations',
'blogmark': 'Blogmarks',
'entry': 'Entries',
}.get(selected.get('type')) or 'Items'
title = noun
if q:
title = '“%s” in %s' % (q, title.lower())
if selected.get('tags'):
title += ' tagged %s' % (', '.join(selected['tags']))
datebits = []
if selected.get('month_name'):
datebits.append(selected['month_name'])
if selected.get('year'):
datebits.append(selected['year'])
if datebits:
title += ' in %s' % (', '.join(datebits))
if not q and not selected:
title = 'Search'
return render(
request, 'search.html', {
'q': q,
'title': title,
'results': results,
'total': paginator.count,
'page': page,
'duration': end - start,
'type_counts': type_counts,
'tag_counts': tag_counts,
'year_counts': year_counts,
'month_counts': month_counts,
'selected_tags': selected_tags,
'excluded_tags': excluded_tags,
'selected': selected,
})
def home_site(request, username, **kwargs):
"""
个人站点视图函数
:param request:
:param username: yuan / alex
:return:
"""
print("kwargs", kwargs)
print("username", username)
# 去数据库查找该用户是否存在
# ret = UserInfo.objects.filter(username= username).exists()
# 拿到当前用户对象
user = UserInfo.objects.filter(username=username).first()
# 判断用户是否存在
if not user:
# 用户不存在返回404页面
return render(request, "not_found.html")
# 查询当前站点对象
blog = user.blog
article_list = models.Article.objects.filter(user=user)
if kwargs:
condition = kwargs.get("condition")
param = kwargs.get("param") # 2012-12
if condition == "category":
article_list = article_list.filter(category__title=param)
elif condition == "tag":
article_list = article_list.filter(tags__title=param)
else:
year, month = param.split("-")
article_list = article_list.filter(create_time__year=year,
create_time__month=month)
# 查看当前用户或当前站点所对应的所有文章
# 方法1：基于对象查询
# article_list = user.article_set.all()
# 方法2：基于双下划线查询
# article_list = models.Article.objects.filter(user=user)
# 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values()
# 查询每一个分类名称及对应的文章数(最简单的分组查询)
ret = models.Category.objects.values("pk").annotate(
c=Count("article__title")).values("title", "c")
print(
ret
) # <QuerySet [{'title': 'yuan的鸡汤', 'c': 1}, {'title': 'Dubbo', 'c': 1}, {'title': '前端', 'c': 1}]>
# 查询当前站点的每一个分类名称以及对应的文章数
cate_list = models.Category.objects.filter(
blog=blog).values("pk").annotate(
c=Count("article__title")).values_list("title", "c")
print(
cate_list
) # <QuerySet [{'title': 'yuan的鸡汤', 'c': 1}, {'title': 'Dubbo', 'c': 1}]>
# 查询当前站点的每一个标签名称及对应的文章数
tag_list = models.Tag.objects.filter(blog=blog).values("pk").annotate(
c=Count("article")).values_list("title", "c")
print(tag_list)
# 查询当前站点每一个年月的名称及对应的文章数
# ret = models.Article.objects.extra(select={"is_recent": "create_time > '2017-09-05'"}).values("title", "is_recent")
# print(ret) # <QuerySet [{'is_recent': 1, 'title': '追求优秀，才是合格的程序员'}, {'is_recent': 1, 'title': 'Dubbo负载均衡与集群容错机制'}, {'is_recent': 1, 'title': 'vue相关'}]>
# ret = models.Article.objects.extra(select={"y_m_d_date": "date_format(create_time, '%%Y-%%m-%%d')"}).values("title", "y_m_d_date").annotate(c=Count("nid")).values("y_m_d_date", "c")
# print(ret)
# date_list = models.Article.objects.filter(user=user).extra(select={"y_m_date": "date_format(create_time, '%%Y-%%m')"}).values("y_m_date").annotate(c=Count("nid")).values("y_m_date", "c")
# print(date_list) # <QuerySet [{'y_m_date': '2018-08', 'c': 2}]>
# 改用values_list，得到字典
date_list = models.Article.objects.filter(user=user).extra(
select={
"y_m_date": "date_format(create_time, '%%Y-%%m')"
}).values("y_m_date").annotate(c=Count("nid")).values_list(
"y_m_date", "c")
print(date_list) # <QuerySet [('2018-08', 2)]>
# 方式二
from django.db.models.functions import TruncMonth
ret = models.Article.objects.filter(user=user).annotate(
month=TruncMonth("create_time")).values("month").annotate(
c=Count("nid")).values_list("month", "c")
print(
"ret---->",
ret) # ret----> <QuerySet [(datetime.datetime(2018, 8, 1, 0, 0), 2)]>
return render(
request, "home_site.html", {
"username": username,
"blog": blog,
"article_list": article_list,
"cate_list": cate_list,
"tag_list": tag_list,
"date_list": date_list
})
def get_locale_insights(query_filters=None):
"""Get data required by the Locale Insights tab.
:param django.db.models.Q query_filters: filters insights by given query_filters.
"""
start_date = get_insight_start_date(False)
snapshots = LocaleInsightsSnapshot.objects.filter(
created_at__gte=start_date)
if query_filters:
snapshots = snapshots.filter(query_filters)
insights = (
snapshots
# Truncate to month and add to select list
.annotate(month=TruncMonth("created_at"))
# Group By month
.values("month")
# Select the avg/sum of the grouping
.annotate(unreviewed_lifespan_avg=Avg("unreviewed_suggestions_lifespan"))
.annotate(completion_avg=Avg("completion"))
.annotate(human_translations_sum=Sum("human_translations"))
.annotate(machinery_sum=Sum("machinery_translations"))
.annotate(new_source_strings_sum=Sum("new_source_strings"))
.annotate(unreviewed_avg=Avg("unreviewed_strings"))
.annotate(peer_approved_sum=Sum("peer_approved"))
.annotate(self_approved_sum=Sum("self_approved"))
.annotate(rejected_sum=Sum("rejected"))
.annotate(new_suggestions_sum=Sum("new_suggestions"))
# Select month and values
.values(
"month",
"unreviewed_lifespan_avg",
"completion_avg",
"human_translations_sum",
"machinery_sum",
"new_source_strings_sum",
"unreviewed_avg",
"peer_approved_sum",
"self_approved_sum",
"rejected_sum",
"new_suggestions_sum",
)
.order_by("month")
)
output = {}
latest = snapshots.latest("created_at") if snapshots else None
if latest:
output.update({
"total_users": {
"managers": latest.total_managers,
"reviewers": latest.total_reviewers,
"contributors": latest.total_contributors,
},
"active_users_last_month":
latest.active_users_last_month,
"active_users_last_3_months":
latest.active_users_last_3_months,
"active_users_last_6_months":
latest.active_users_last_6_months,
"active_users_last_12_months":
latest.active_users_last_12_months,
})
else:
output.update({
"total_users": active_users_default(),
"active_users_last_month": active_users_default(),
"active_users_last_3_months": active_users_default(),
"active_users_last_6_months": active_users_default(),
"active_users_last_12_months": active_users_default(),
})
output.update({
"dates": [convert_to_unix_time(x["month"]) for x in insights],
"unreviewed_lifespans":
[x["unreviewed_lifespan_avg"].days for x in insights],
"translation_activity": {
"completion": [round(x["completion_avg"], 2) for x in insights],
"human_translations":
[x["human_translations_sum"] for x in insights],
"machinery_translations": [x["machinery_sum"] for x in insights],
"new_source_strings":
[x["new_source_strings_sum"] for x in insights],
},
"review_activity": {
"unreviewed": [int(round(x["unreviewed_avg"])) for x in insights],
"peer_approved": [x["peer_approved_sum"] for x in insights],
"self_approved": [x["self_approved_sum"] for x in insights],
"rejected": [x["rejected_sum"] for x in insights],
"new_suggestions": [x["new_suggestions_sum"] for x in insights],
},
})
return output
def get_querylist(self):
# use a separate subquery to get the max count value
datapoint_max = (Datapoint.objects.annotate(timestamp=TruncMonth(
'measurement_date')).values('timestamp').annotate(data_count=Count(
'id')).order_by('-data_count').values('data_count')[:1])
datapoint_query = (
Datapoint.objects
# Truncate to Month and add to values
.annotate(timestamp=TruncMonth('measurement_date')).values(
'timestamp') # Group By Month
.annotate(
data_count=Count('id')) # Select the count of the grouping
# Bring in the max value from a subquery
.annotate(
datapoint_max=Subquery(datapoint_max.values('data_count')[:1]))
# calculate density percentage. Cast one of the integers to a Floatfield to make division return a float.
.annotate(density_percentage=ExpressionWrapper(
Cast('data_count', FloatField()) / F('datapoint_max'),
output_field=FloatField())).order_by('timestamp'))
# use a separate subquery to get the max count value
bin_max = (Bin.objects.filter(
cell_concentration_data__isnull=False).annotate(
timestamp=TruncMonth('sample_time')).values(
'timestamp').annotate(data_count=Count('id')).order_by(
'-data_count').values('data_count')[:1])
bin_query = (
Bin.objects.filter(cell_concentration_data__isnull=False)
# Truncate to Month and add to values
.annotate(timestamp=TruncMonth('sample_time')).values(
'timestamp') # Group By Month
.annotate(
data_count=Count('id')) # Select the count of the grouping
.annotate(bin_max=Subquery(bin_max.values(
'data_count')[:1])) # Bring in the max value from a subquery
# calculate density percentage. Cast one of the integers to a Floatfield to make division return a float.
.annotate(density_percentage=ExpressionWrapper(
Cast('data_count', FloatField()) / F('bin_max'),
output_field=FloatField())).order_by('timestamp'))
# use a separate subquery to get the max count value
closure_max = (ClosureNotice.objects.filter(
notice_action="Closed").annotate(timestamp=TruncMonth(
'effective_date')).values('timestamp').annotate(
data_count=Count('id')).order_by('-data_count').values(
'data_count')[:1])
closure_query = (
ClosureNotice.objects.filter(notice_action="Closed").annotate(
timestamp=TruncMonth(
'effective_date')) # Truncate to Month and add to values
.values('timestamp') # Group By Month
.annotate(
data_count=Count('id')) # Select the count of the grouping
.annotate(closure_max=Subquery(
closure_max.values('data_count')
[:1])) # Bring in the max value from a subquery
# calculate density percentage. Cast one of the integers to a Floatfield to make division return a float.
.annotate(density_percentage=ExpressionWrapper(
Cast('data_count', FloatField()) / F('closure_max'),
output_field=FloatField())).order_by('timestamp'))
active_layers = DataLayer.objects.filter(is_active=True)
querylist = []
for layer in active_layers:
if layer.layer_id == 'ifcb-layer':
querylist.append({
'queryset': bin_query,
'serializer_class': BinSerializer,
'label': 'IFCB Cell Concentrations',
})
elif layer.layer_id == 'stations-layer':
querylist.append({
'queryset': datapoint_query,
'serializer_class': DatapointSerializer,
'label': 'Shellfish Station Toxicity',
})
elif layer.layer_id == 'closures-layer':
querylist.append({
'queryset': closure_query,
'serializer_class': ClosureNoticeSerializer,
'label': 'Shellfish Closures',
})
return querylist
def meta100(request):
data = [
{
"month": "Janeiro",
"number": "01",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Fevereiro",
"number": "02",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Março",
"number": "03",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Abril",
"number": "04",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Maio",
"number": "05",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Junho",
"number": "06",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Julho",
"number": "07",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Agosto",
"number": "08",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Setembro",
"number": "09",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Outubro",
"number": "10",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Novembro",
"number": "11",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
{
"month": "Dezembro",
"number": "12",
"data": [],
"totalcredit": 0,
"totaldebit": 0
},
]
year = request.GET.get("y", 2018)
for item in data:
result = (Records.objects.annotate(
month=TruncMonth("create_date_time")).annotate(
day=TruncDay("create_date_time")).values("day").filter(
create_date_time__year=year,
create_date_time__month=item["number"],
type_entry__id=1).annotate(sumdebit=Sum("debit")).annotate(
sumcredit=Sum("credit")).order_by())
for r in result:
sumcredit = r["sumcredit"] if r["sumcredit"] else 0
sumdebit = r["sumdebit"] if r["sumdebit"] else 0
item["data"].append({
"day": r["day"].strftime("%d"),
"sumdebit": sumdebit,
"sumcredit": sumcredit,
"final": sumcredit - sumdebit,
})
item["totalcredit"] += sumcredit
item["totaldebit"] += sumdebit
return render(request, "mainapp/meta100.html", {"data": data})
def portdetail_stats(request):
days = request.GET.get('days', 30)
days_ago = request.GET.get('days_ago', 0)
# Validate days and days_ago
for value in days, days_ago:
check, message = validate_stats_days(value)
if check is False:
return HttpResponse(message)
port_name = request.GET.get('port_name')
port = Port.objects.get(name__iexact=port_name)
days = int(days)
days_ago = int(days_ago)
end_date = datetime.datetime.now(tz=datetime.timezone.utc) - datetime.timedelta(days=days_ago)
start_date = end_date - datetime.timedelta(days=days)
# Section for calculation of current stats
submissions = Submission.objects.filter(timestamp__range=[start_date, end_date]).order_by('user', '-timestamp').distinct('user')
port_installations = PortInstallation.objects.filter(submission_id__in=Subquery(submissions.values('id')), port__iexact=port_name)
requested_port_installations_count = port_installations.filter(requested=True).aggregate(Count('submission__user_id', distinct=True))
total_port_installations_count = port_installations.aggregate(Count('submission__user_id', distinct=True))
port_installations_by_port_version = sort_list_of_dicts_by_version(list(port_installations.values('version').annotate(num=Count('version'))), 'version')
port_installations_by_os_and_xcode_version = sort_list_of_dicts_by_version(list(port_installations.values('submission__xcode_version', 'submission__os_version').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_os_and_clt_version = sort_list_of_dicts_by_version(list(port_installations.values('submission__clt_version', 'submission__os_version').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_os_stdlib_build_arch = sort_list_of_dicts_by_version(list(port_installations.values('submission__os_version', 'submission__build_arch', 'submission__cxx_stdlib').annotate(num=Count('submission__user_id', distinct=True))), 'submission__os_version')
port_installations_by_variants = port_installations.values('variants').annotate(num=Count('submission__user_id', distinct=True))
port_installations_by_month = PortInstallation.objects.filter(port__iexact=port_name).annotate(month=TruncMonth('submission__timestamp')).values('month').annotate(num=Count('submission__user', distinct=True))[:12]
port_installations_by_version_and_month = PortInstallation.objects.filter(port__iexact=port_name).annotate(month=TruncMonth('submission__timestamp')).values('month', 'version').annotate(num=Count('submission__user', distinct=True))[:12]
return render(request, 'ports/port-detail/installation_stats.html', {
'requested_port_installations_count': requested_port_installations_count,
'total_port_installations_count': total_port_installations_count,
'port_installations_by_port_version': port_installations_by_port_version,
'port_installations_by_os_and_xcode_version': port_installations_by_os_and_xcode_version,
'port_installations_by_os_and_clt_version': port_installations_by_os_and_clt_version,
'port_installations_by_month': port_installations_by_month,
'port_installations_by_version_and_month': port_installations_by_version_and_month,
'port_installations_by_os_stdlib_build_arch': port_installations_by_os_stdlib_build_arch,
'port_installations_by_variants': port_installations_by_variants,
'days': days,
'days_ago': days_ago,
'end_date': end_date,
'start_date': start_date,
'users_in_duration_count': submissions.count(),
'allowed_days': ALLOWED_DAYS_FOR_STATS
})
def get_queryset(self):
return (Expense.objects.annotate(
month=TruncMonth("charged_on")).values("month").annotate(
pretax_total=Sum("pretax_amount"),
taxes_total=Sum("tax_amount"),
grand_total=Sum(F("pretax_amount") + F("tax_amount"))))
def get_queryset(self, *args, **kwargs):
# display the latest years tax data first
try:
current_year = Table.objects.last().bill.month.year
except:
current_year = now.year
if self.request.GET.get('property'):
search_query = self.request.GET.get('property')
query_set = Table.objects.filter(
room__name__icontains=str(search_query))
else:
query_set = Table.objects.all()
current_year_queryset = query_set.\
filter(bill__month__year=str(current_year)).\
exclude(tax__exact='0').annotate(month=TruncMonth('bill__month')).\
values('month').annotate(total_amount=Sum('amount')).\
annotate(total_tax=Sum('tax')).\
values('month', 'total_amount', 'total_tax', 'room__name')
queryset_all = query_set.exclude(tax__exact='0').\
annotate(month=TruncMonth('bill__month'))\
.values('month').annotate(total_amount=Sum('amount')).\
annotate(total_tax=Sum('tax')).\
values('month', 'total_amount', 'total_tax', 'room__name')
queryset = current_year_queryset
if self.request.GET.get('month_from') and self.request.GET.get(
'month_to'):
month_from = self.request.GET.get('month_from')
month_to = self.request.GET.get('month_to')
queryset = queryset_all.filter(
bill__month__range=[str(month_from),
str(month_to)])
if self.request.GET.get('month') and self.request.GET.get('year'):
month = self.request.GET.get('month')
year = self.request.GET.get('year')
queryset = queryset_all.filter(bill__month__month=str(month),
bill__month__year=str(year))
if self.request.GET.get('year') and not self.request.GET.get('month'):
year = self.request.GET.get('year')
queryset = queryset_all.filter(bill__month__year=str(year))
page_size = 'page_size'
if self.request.GET.get(page_size):
pagination.PageNumberPagination.page_size = self.request.GET.get(
page_size)
else:
pagination.PageNumberPagination.page_size = 10
finalqueryset = queryset.values('month', 'total_amount', 'total_tax',
'room__name', 'bill__billtype__name')
for i in queryset:
i['service'] = 0
for j in finalqueryset:
if j['room__name'] == i['room__name'] and j[
'bill__billtype__name'] == 'Service':
i['service'] += j['total_amount']
i['rents'] = i['total_amount'] - i['service']
return queryset
def reportes_negocio_resumen(request, negocio, reporte):
if (request.method == 'POST'):
radioRango = request.POST['radioRango']
if (radioRango == 'radioRango1'):
fechaInicio = request.POST['fechaInicio']
fechaFin = request.POST['fechaFin']
fechaInicioDT = datetime.datetime.strptime(fechaInicio, '%Y-%m-%d')
fechaFinDT = datetime.datetime.strptime(fechaFin, '%Y-%m-%d')
TipoAgrupacion = "PorRango"
else:
now = timezone.make_aware(datetime.datetime.now(),
timezone.get_default_timezone())
day = str(now.day).zfill(2)
month = str(now.month).zfill(2)
year = str(now.year)
fechaInicio = year + "-" + month + "-" + day
fechaFin = fechaInicio
TipoAgrupacion = request.POST['TipoAgrupacion']
else:
now = timezone.make_aware(datetime.datetime.now(),
timezone.get_default_timezone())
day = str(now.day).zfill(2)
month = str(now.month).zfill(2)
year = str(now.year)
fechaInicio = year + "-" + month + "-" + day
fechaFin = fechaInicio
TipoAgrupacion = "PorDia"
negocio = negocio.replace("/", "")
categoriasList = list(
PuntoventaCategoria.objects.filter(
id_categoria_padre__nombre_categoria=negocio).values(
'nombre_categoria'))
categoriasStringList = [o['nombre_categoria'] for o in categoriasList]
categoriasStringList.extend([negocio])
if (TipoAgrupacion == 'PorDia'):
print "-------> POR DIA"
# Query para obtener las ganancias y el total de ventas agrupado por dia del mes actual
ganancias = PuntoventaCarrito.objects.filter(
Q(id_venta__fecha_venta__month=now.month)
& Q(id_venta__fecha_venta__year=now.year)
).filter(
id_producto__id_categoria__nombre_categoria__in=categoriasStringList
).annotate(
day=TruncDay('id_venta__fecha_venta')).values('day').annotate(
dayGrouped=TruncDay('id_venta__fecha_venta'),
total_ganancia=Sum(F('cantidad_producto') *
(F('id_producto__precio_venta') -
F('id_producto__precio_compra')),
output_field=FloatField()),
total_venta=Sum('total')).order_by('dayGrouped')
if (TipoAgrupacion == 'PorMes'):
print "-------> POR MES"
# Query para obtener las ganancias y el total de ventas agrupado por mes del ano actual
ganancias = PuntoventaCarrito.objects.filter(
id_venta__fecha_venta__year=now.year
).filter(
id_producto__id_categoria__nombre_categoria__in=categoriasStringList
).annotate(
day=TruncMonth('id_venta__fecha_venta')).values('day').annotate(
dayGrouped=TruncMonth('id_venta__fecha_venta'),
total_ganancia=Sum(F('cantidad_producto') *
(F('id_producto__precio_venta') -
F('id_producto__precio_compra')),
output_field=FloatField()),
total_venta=Sum('total')).order_by('dayGrouped')
if (TipoAgrupacion == 'PorAno'):
print "-------> POR ANO"
# Query para obtener las ganancias y el total de ventas agrupado por ano
ganancias = PuntoventaCarrito.objects.filter(
id_producto__id_categoria__nombre_categoria__in=categoriasStringList
).annotate(
day=TruncYear('id_venta__fecha_venta')).values('day').annotate(
dayGrouped=TruncYear('id_venta__fecha_venta'),
total_ganancia=Sum(F('cantidad_producto') *
(F('id_producto__precio_venta') -
F('id_producto__precio_compra')),
output_field=FloatField()),
total_venta=Sum('total')).order_by('dayGrouped')
if (TipoAgrupacion == 'PorRango'):
print "-------> POR RANGO"
# Query para obtener las ganancias y el total de ventas
ganancias = PuntoventaCarrito.objects.filter(
id_venta__fecha_venta__range=[fechaInicio, fechaFin]
).filter(
id_producto__id_categoria__nombre_categoria__in=categoriasStringList
).annotate(
day=TruncDay('id_venta__fecha_venta')).values('day').annotate(
dayGrouped=TruncDay('id_venta__fecha_venta'),
total_ganancia=Sum(F('cantidad_producto') *
(F('id_producto__precio_venta') -
F('id_producto__precio_compra')),
output_field=FloatField()),
total_venta=Sum('total')).order_by('dayGrouped')
context = {
'ganancias': ganancias,
'negocio': negocio,
'reporte': reporte,
'fechaInicio': fechaInicio,
'fechaFin': fechaFin,
'TipoAgrupacion': TipoAgrupacion
}
return render(request, 'reportes_negocio_resumen.html', context)
def count_client(request):
count_all = Clients.objects.all().count()
count_ouest = Clients.objects.all().filter(région="1").count()
count_centre = Clients.objects.all().filter(région="2").count()
count_est = Clients.objects.all().filter(région="3").count()
produit_nom = Produit.objects.all()
mynumberFilter = NumberFilter(request.GET, queryset=Order.objects.all())
AllProduits_list = mynumberFilter.qs
mynumberFilter = NumberFilter(request.GET, queryset=Bc.objects.all())
Allbc_list = mynumberFilter.qs
count_bc_ouest = Allbc_list.all().filter(client__région="1").count()
count_bc_centre = Allbc_list.all().filter(client__région="2").count()
count_bc_est = Allbc_list.all().filter(client__région="3").count()
mynumberFilter2 = NumberFilter2(request.GET,
queryset=Visite_test.objects.all())
Allvisite_list = mynumberFilter2.qs
count_visite_ouest = Allvisite_list.all().filter(
région="1", status="Cloturée").count()
count_visite_centre = Allvisite_list.all().filter(
région="2", status="Cloturée").count()
count_visite_est = Allvisite_list.all().filter(région="3",
status="Cloturée").count()
produit_count = AllProduits_list.values('designation__nom').annotate(
ouest_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="1")), 0),
centre_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="2")), 0),
est_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="3")),
0)).order_by("-designation__nom")
produit_count2 = AllProduits_list.values('designation__nom').annotate(
ouest_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="1")), 0),
centre_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="2")), 0),
est_commande=Coalesce(
Sum('commande', filter=Q(bc__client__région="3")), 0),
month=TruncMonth('date')).order_by("-designation__nom")
context = {
'count_all': count_all,
'count_centre': count_centre,
'count_ouest': count_ouest,
'count_est': count_est,
'Allbc_list': Allbc_list,
'count_bc_ouest': count_bc_ouest,
'count_bc_centre': count_bc_centre,
'count_bc_est': count_bc_est,
'count_visite_ouest': count_visite_ouest,
'count_visite_centre': count_visite_centre,
'count_visite_est': count_visite_est,
'produit_nom': produit_nom,
'mynumberFilter2': mynumberFilter2,
'mynumberFilter': mynumberFilter,
'AllProduits_list': AllProduits_list,
'produit_count': produit_count,
'produit_count2': produit_count2,
}
return render(request, 'number.html', context)
locales = Locale.objects.available().order_by("code")
data = {}
for year in range(2017, datetime.datetime.now().year + 1):
for month in range(1, 13):
if (year == datetime.datetime.now().year
and month > datetime.datetime.now().month):
continue
data["{}-{:02d}".format(year, month)] = {}
for locale in locales:
translations = (Translation.objects.filter(
date__gte=datetime.datetime(2017, 1, 1),
locale=locale,
user__isnull=False,
).annotate(period=TruncMonth("date")).values("period").annotate(
count=Count("id")).order_by("period"))
for x in translations:
date = x["period"]
date = "{}-{:02d}".format(date.year, date.month)
count = x["count"]
data[date][locale.code] = count
output = []
output.append("," + ",".join(locales.values_list("code", flat=True)))
for date, values in data.items():
line = date
for locale in locales:
line = line + "," + str(values.get(locale.code, 0))
output.append(line)
def get_component_case(self, from_dt, until_dt, by_week_month):
component_report = {}
component_report_list = []
if by_week_month == 'week':
until_dt = from_dt + relativedelta(months=-1)
date_filter = Q(incoming_date__lte=from_dt,
incoming_date__gte=until_dt)
try:
args = ()
kwargs = {}
kwargs['product_id'] = 2
month_list = []
week_list = []
# -- Get distinct months
if by_week_month == 'month':
month_list = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).\
annotate(month=TruncMonth('incoming_date')).values('month').distinct().order_by('month')
component_report['month_list'] = month_list
if by_week_month == 'week':
week_list = Case.objects.values('week').distinct().filter(
date_filter, *args, **kwargs).order_by('week')
component_report['week_list'] = week_list
#---get the unique months Month
#log.info(month_list)
data = []
if by_week_month == 'month':
data = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).\
annotate(month=TruncMonth('incoming_date')).values('month').\
annotate(case_count=Count('*')).values('month', 'component_id','case_count').order_by('month')
if by_week_month == 'week':
data = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).\
annotate(case_count=Count('*')).values('week', 'component_id','case_count').order_by('week')
# Get the component details
components = Component.objects.filter(product_id=2,
active=True).order_by('name')
#log.info(components)
# -- Total
#data = Case.objects.filter(date_filter,component_id__isnull = False, *args, **kwargs).values('component_id').\
#annotate(case_count=Count('*')).order_by('component_id')
#log.info(monthly)
for component in components:
report = {}
report['component'] = component.name
report['id'] = component.id
total = 0
case_count_list = []
if by_week_month == 'month' and month_list is not None and len(
month_list) > 0:
monthly_dict = {}
for month in month_list:
case_count = 0
if data is not None and len(data) > 0:
for item in data:
monthly_dict = {}
if item['component_id'] == component.id and month[
'month'] == item['month']:
log.info(
'Month: %s component: %s , count: %s '
% (item['month'], component.id,
item['case_count']))
monthly_dict['month'] = month['month']
case_count = item['case_count']
total = total + case_count
monthly_dict['case_count'] = case_count
case_count_list.append(monthly_dict)
if case_count == 0:
monthly_dict['month'] = month['month']
monthly_dict['case_count'] = case_count
case_count_list.append(monthly_dict)
report['monthly_count'] = case_count_list
report['total'] = total
if total > 0:
report['average'] = math.ceil(total / len(month_list))
else:
report['average'] = 0
component_report_list.append(report)
if by_week_month == 'week' and week_list is not None and len(
week_list) > 0:
for week in week_list:
case_count = 0
if data is not None and len(data) > 0:
for item in data:
weekly_dict = {}
if item['component_id'] == component.id and week[
'week'] == item['week']:
#log.info('Month: %s component: %s , count: %s ' % (item['week'], component.id, item['case_count']))
weekly_dict['week'] = month['week']
case_count = item['case_count']
total = total + case_count
weekly_dict['case_count'] = case_count
case_count_list.append(weekly_dict)
if case_count == 0:
weekly_dict['week'] = week['week']
monthly_dict['case_count'] = case_count
case_count_list.append(weekly_dict)
report['weekly_count'] = case_count_list
report['total'] = total
if total > 0:
report['average'] = math.ceil(total / len(week_list))
else:
report['average'] = 0
component_report_list.append(report)
component_report[
'component_report_list'] = component_report_list
return component_report
except Exception, ex:
log.exception("SQL Error Encountered in jd search. " + str(ex))
def objectiveMargin(self, startDate=None, endDate=None):
"""Compute margin over rate objective
@param startDate: starting date to consider. This date is included in range. If None, start date is the begining of the mission
@param endDate: ending date to consider. This date is excluded from range. If None, end date is last timesheet for this mission.
@return: dict where key is consultant, value is cumulated margin over objective"""
result = {}
consultant_rates = self.consultant_rates()
# Gather timesheet and staffing (starting current month)
timesheets = Timesheet.objects.filter(mission=self)
staffings = Staffing.objects.filter(
mission=self, staffing_date__gte=date.today().replace(day=1))
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
staffings = staffings.filter(staffing_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lt=endDate)
staffings = staffings.filter(staffing_date__lt=endDate)
timesheets = timesheets.order_by("working_date")
staffings = staffings.order_by("staffing_date")
timesheetMonths = list(timesheets.dates("working_date", "month"))
staffingMonths = list(staffings.dates("staffing_date", "month"))
for consultant in self.consultants():
result[
consultant] = 0 # Initialize margin over rate objective for this consultant
timesheet_data = dict(
timesheets.filter(consultant=consultant).annotate(
month=TruncMonth("working_date")).values_list(
"month").annotate(Sum("charge")).order_by("month"))
staffing_data = dict(
staffings.filter(consultant=consultant).annotate(
month=TruncMonth("staffing_date")).values_list(
"month").annotate(Sum("charge")).order_by("month"))
for month in timesheetMonths:
n_days = timesheet_data.get(month, 0)
if consultant.subcontractor:
# Compute objective margin on sold rate
if consultant_rates[consultant][0] and consultant_rates[
consultant][1]:
result[consultant] += n_days * (
consultant_rates[consultant][0] -
consultant_rates[consultant][1])
else:
# Compute objective margin on rate objective for this period
objectiveRate = consultant.get_rate_objective(
working_date=month, rate_type="DAILY_RATE")
if objectiveRate:
result[consultant] += n_days * (
consultant_rates[consultant][0] -
objectiveRate.rate)
for month in staffingMonths:
n_days = staffing_data.get(month, 0) - timesheet_data.get(
month, 0
) # substract timesheet data from staffing to avoid twice counting
if consultant.subcontractor:
# Compute objective margin on sold rate
if consultant_rates[consultant][0] and consultant_rates[
consultant][1]:
result[consultant] += n_days * (
consultant_rates[consultant][0] -
consultant_rates[consultant][1])
else:
# Compute objective margin on rate objective for this period
objectiveRate = consultant.get_rate_objective(
working_date=month, rate_type="DAILY_RATE")
if objectiveRate:
result[consultant] += n_days * (
consultant_rates[consultant][0] -
objectiveRate.rate)
return result
def search(request):
q = request.GET.get("q", "").strip()
start = time.time()
query = None
rank_annotation = None
if q:
query = SearchQuery(q)
rank_annotation = SearchRank(models.F("search_document"), query)
selected_tags = request.GET.getlist("tag")
excluded_tags = request.GET.getlist("exclude.tag")
selected_type = request.GET.get("type", "")
selected_year = request.GET.get("year", "")
selected_month = request.GET.get("month", "")
values = ["pk", "type", "created"]
if q:
values.append("rank")
def make_queryset(klass, type_name):
qs = klass.objects.annotate(
type=models.Value(type_name, output_field=models.CharField())
)
if selected_year:
qs = qs.filter(created__year=int(selected_year))
if selected_month:
qs = qs.filter(created__month=int(selected_month))
if q:
qs = qs.filter(search_document=query)
qs = qs.annotate(rank=rank_annotation)
for tag in selected_tags:
qs = qs.filter(tags__tag=tag)
for exclude_tag in excluded_tags:
qs = qs.exclude(tags__tag=exclude_tag)
return qs.order_by()
# Start with a .none() queryset just so we can union stuff onto it
qs = Entry.objects.annotate(
type=models.Value("empty", output_field=models.CharField())
)
if q:
qs = qs.annotate(rank=rank_annotation)
qs = qs.values(*values).none()
type_counts_raw = {}
tag_counts_raw = {}
year_counts_raw = {}
month_counts_raw = {}
for klass, type_name in (
(Entry, "entry"),
(Blogmark, "blogmark"),
(Quotation, "quotation"),
):
if selected_type and selected_type != type_name:
continue
klass_qs = make_queryset(klass, type_name)
type_count = klass_qs.count()
if type_count:
type_counts_raw[type_name] = type_count
for tag, count in (
Tag.objects.filter(**{"%s__in" % type_name: klass_qs})
.annotate(n=models.Count("tag"))
.values_list("tag", "n")
):
tag_counts_raw[tag] = tag_counts_raw.get(tag, 0) + count
for row in (
klass_qs.order_by()
.annotate(year=TruncYear("created"))
.values("year")
.annotate(n=models.Count("pk"))
):
year_counts_raw[row["year"]] = (
year_counts_raw.get(row["year"], 0) + row["n"]
)
# Only do month counts if a year is selected
if selected_year:
for row in (
klass_qs.order_by()
.annotate(month=TruncMonth("created"))
.values("month")
.annotate(n=models.Count("pk"))
):
month_counts_raw[row["month"]] = (
month_counts_raw.get(row["month"], 0) + row["n"]
)
qs = qs.union(klass_qs.values(*values))
if q:
qs = qs.order_by("-rank")
else:
qs = qs.order_by("-created")
type_counts = sorted(
[
{"type": type_name, "n": value}
for type_name, value in list(type_counts_raw.items())
],
key=lambda t: t["n"],
reverse=True,
)
tag_counts = sorted(
[{"tag": tag, "n": value} for tag, value in list(tag_counts_raw.items())],
key=lambda t: t["n"],
reverse=True,
)[:40]
year_counts = sorted(
[{"year": year, "n": value} for year, value in list(year_counts_raw.items())],
key=lambda t: t["year"],
)
month_counts = sorted(
[
{"month": month, "n": value}
for month, value in list(month_counts_raw.items())
],
key=lambda t: t["month"],
)
paginator = Paginator(qs, 30)
page_number = request.GET.get("page") or "1"
try:
page = paginator.page(page_number)
except PageNotAnInteger:
raise Http404
except EmptyPage:
raise Http404
results = []
for obj in load_mixed_objects(page.object_list):
results.append(
{
"type": obj.original_dict["type"],
"rank": obj.original_dict.get("rank"),
"obj": obj,
}
)
end = time.time()
selected = {
"tags": selected_tags,
"year": selected_year,
"month": selected_month,
"type": selected_type,
"month_name": MONTHS_3_REV_REV.get(
selected_month and int(selected_month) or "", ""
).title(),
}
# Remove empty keys
selected = {key: value for key, value in list(selected.items()) if value}
# Dynamic title
noun = {"quotation": "Quotations", "blogmark": "Blogmarks", "entry": "Entries"}.get(
selected.get("type")
) or "Items"
title = noun
if q:
title = "“%s” in %s" % (q, title.lower())
if selected.get("tags"):
title += " tagged %s" % (", ".join(selected["tags"]))
datebits = []
if selected.get("month_name"):
datebits.append(selected["month_name"])
if selected.get("year"):
datebits.append(selected["year"])
if datebits:
title += " in %s" % (", ".join(datebits))
if not q and not selected:
title = "Search"
return render(
request,
"search.html",
{
"q": q,
"title": title,
"results": results,
"total": paginator.count,
"page": page,
"duration": end - start,
"type_counts": type_counts,
"tag_counts": tag_counts,
"year_counts": year_counts,
"month_counts": month_counts,
"selected_tags": selected_tags,
"excluded_tags": excluded_tags,
"selected": selected,
},
)
def pivotable_data(self, startDate=None, endDate=None):
"""Compute raw data for pivot table on that mission"""
#TODO: factorize with staffing.views.mission_timesheet
data = []
mission_id = self.mission_id()
mission_name = self.short_name()
current_month = date.today().replace(day=1) # Current month
subsidiary = str(self.subsidiary)
consultant_rates = self.consultant_rates()
billing_mode = self.get_billing_mode_display()
# Gather timesheet and staffing (Only consider data up to current month)
timesheets = Timesheet.objects.filter(mission=self).filter(
working_date__lt=nextMonth(current_month)).order_by("working_date")
staffings = Staffing.objects.filter(mission=self).filter(
staffing_date__lt=nextMonth(current_month)).order_by(
"staffing_date")
if startDate:
timesheets = timesheets.filter(working_date__gte=startDate)
staffings = staffings.filter(staffing_date__gte=startDate)
if endDate:
timesheets = timesheets.filter(working_date__lte=endDate)
staffings = staffings.filter(staffing_date__lte=endDate)
timesheetMonths = list(timesheets.dates("working_date", "month"))
staffingMonths = list(staffings.dates("staffing_date", "month"))
for consultant in self.consultants():
consultant_name = str(consultant)
timesheet_data = dict(
timesheets.filter(consultant=consultant).annotate(
month=TruncMonth("working_date")).values_list(
"month").annotate(Sum("charge")).order_by("month"))
staffing_data = dict(
staffings.filter(consultant=consultant).values_list(
"staffing_date").annotate(
Sum("charge")).order_by("staffing_date"))
for month in set(timesheetMonths + staffingMonths):
data.append({
ugettext("mission id"):
mission_id,
ugettext("mission name"):
mission_name,
ugettext("consultant"):
consultant_name,
ugettext("subsidiary"):
subsidiary,
ugettext("billing mode"):
billing_mode,
ugettext("date"):
month.strftime("%Y/%m"),
ugettext("done (days)"):
timesheet_data.get(month, 0),
ugettext("done (€)"):
timesheet_data.get(month, 0) *
consultant_rates[consultant][0],
ugettext("forecast (days)"):
staffing_data.get(month, 0),
ugettext("forecast (€)"):
staffing_data.get(month, 0) *
consultant_rates[consultant][0]
})
return data
def post(self, request, *args, **kwargs):
startDate = request.POST.get('startDate', False)
endDate = request.POST.get('endDate', False)
result = {}
if startDate and endDate:
start_date = datetime.strptime(startDate, '%d/%m/%Y %I:%M %p')
end_date = datetime.strptime(endDate, '%d/%m/%Y %I:%M %p')
syschecks = Syscheck.objects.all()
syschk_hosts = syschecks.values('syschk_fpath').distinct().\
values_list('syschk_fpath', flat=True)
syschk_with_datetime = syschecks.\
annotate(datetime=RawSQL('FROM_UNIXTIME(mtime)', [],
output_field=DateTimeField())).\
filter(datetime__gte=start_date, datetime__lte=end_date)
diff = end_date - start_date
datasets = {}
if diff.days == 0:
trunc_syschks = syschk_with_datetime.annotate(
hour=TruncHour('datetime'))
syschk_times = []
for dt in rrule.rrule(rrule.HOURLY,
dtstart=start_date,
until=end_date):
syschk_times.append(dt)
datasets['text'] = 'Number of syscheck in 24 hours per hosts'
datasets['datas'] = OrderedDict()
datasets['labels'] = []
for syschk_time in syschk_times:
datasets['labels'].append(syschk_time.strftime("%H"))
for syschk_host in syschk_hosts:
if syschk_host not in datasets['datas']:
datasets['datas'][syschk_host] = []
trunc_syschks_count = trunc_syschks.\
filter(syschk_fpath=syschk_host,
hour=pytz.utc.localize(syschk_time)).count()
datasets['datas'][syschk_host].\
append(trunc_syschks_count)
result['by_time_host'] = datasets
elif diff.days > 0 and diff.days <= 31:
trunc_syschks = syschk_with_datetime.annotate(
date=TruncDay('datetime'))
syschk_times = []
for dt in rrule.rrule(rrule.DAILY,
dtstart=start_date,
until=end_date):
syschk_times.append(dt)
datasets['text'] = 'Number of syschecks per days each host'
datasets['datas'] = OrderedDict()
datasets['labels'] = []
for syschk_time in syschk_times:
datasets['labels'].append(syschk_time.strftime("%d/%m"))
for syschk_host in syschk_hosts:
if syschk_host not in datasets['datas']:
datasets['datas'][syschk_host] = []
trunc_syschks_count = trunc_syschks.\
filter(syschk_fpath=syschk_host,
date=pytz.utc.localize(syschk_time)).count()
datasets['datas'][syschk_host].\
append(trunc_syschks_count)
result['by_time_host'] = datasets
elif diff.days > 31 and diff.days <= 365:
trunc_syschks = syschk_with_datetime.annotate(
hour=TruncHour('datetime'))
syschk_times = OrderedDict()
for dt in rrule.rrule(rrule.DAILY,
dtstart=start_date,
until=end_date):
isocalendar = dt.isocalendar()
week = 'W{0}/{1}'.format(isocalendar[1], isocalendar[0])
if week not in syschk_times:
syschk_times[week] = {}
syschk_times[week]['start_date'] = dt
else:
syschk_times[week]['end_date'] = dt
datasets['text'] = 'Number of syschecks per weeks each host'
datasets['datas'] = OrderedDict()
datasets['labels'] = []
for week, week_dates in syschk_times.items():
datasets['labels'].append(week)
for syschk_host in syschk_hosts:
if syschk_host not in datasets['datas']:
datasets['datas'][syschk_host] = []
trunc_syschks_count = trunc_syschks.\
filter(syschk_fpath=syschk_host,
datetime__gte=week_dates['start_date'],
datetime__lte=week_dates['end_date']).\
count()
datasets['datas'][syschk_host].\
append(trunc_syschks_count)
result['by_time_host'] = datasets
else:
trunc_syschks = syschk_with_datetime.annotate(
month=TruncMonth('datetime'))
syschk_times = []
for dt in rrule.rrule(rrule.MONTHLY,
dtstart=start_date,
until=end_date):
syschk_times.append(dt)
datasets['text'] = 'Number of syschecks per months each host'
datasets['datas'] = OrderedDict()
datasets['labels'] = []
for syschk_time in syschk_times:
datasets['labels'].append(syschk_time.strftime("M%m/%Y"))
for syschk_host in syschk_hosts:
if syschk_host not in datasets['datas']:
datasets['datas'][syschk_host] = []
trunc_syschks_count = trunc_syschks.\
filter(syschk_fpath=syschk_host,
month=pytz.utc.localize(syschk_time)).count()
datasets['datas'][syschk_host].\
append(trunc_syschks_count)
result['by_time_host'] = datasets
datasets_new_datas = OrderedDict()
for key in datasets['datas'].keys():
fname = os.path.basename(key)
if fname == 'syscheck':
agent = Agent.objects.get(agent_id='000')
new_host_name = agent.name
else:
new_host_name = fname[:fname.find('->')]
datasets_new_datas[str(new_host_name)] = \
datasets['datas'][key]
datasets['datas'] = datasets_new_datas
by_host_data = []
by_host_count = []
for syschk_host in syschk_hosts:
by_host_count.append(
syschk_with_datetime.filter(
syschk_fpath=syschk_host).count())
fname = os.path.basename(syschk_host)
if fname == 'syscheck':
agent = Agent.objects.get(agent_id='000')
new_host_name = agent.name
else:
new_host_name = fname[:fname.find('->')]
by_host_data.append(new_host_name)
result['by_host'] = {
'label': 'Number of syschecks per hosts',
'count': by_host_count,
'data': by_host_data,
}
return HttpResponse(json.dumps(result))
def get_months_plot(queryset, field) -> list:
return queryset.annotate(month=TruncMonth('date__month'))\
.values('month').annotate(c=Sum(field)).values('month', 'c')\
.annotate(prev=Window(Lag('c'))).annotate(repayment=F('c')-F('prev'))\
.values('month', 'c', 'repayment')
def oys_qs(user: "******", class_id: int = None) -> List[Dict[str, Union[str, int]]]:
"""The query set for a pivot table.
The columns are always month/year combinations, i.e. Aug 2000,
Sep 2000, etc.
If class_id is none, the rows are all the classes. If defined, the
rows are all the categories defined for the defined class.
The values are the sum of all transactions for the given row/column.
"""
Transaction = apps.get_model("budget.Transaction")
Budget = apps.get_model("budget.Budget")
if class_id is None:
base_qs = Transaction.objects.in_last_thirteen_months(user)
ix = "pattern__category__class_field__name"
piv_ix_name = "class_"
else:
base_qs = Transaction.objects.in_last_thirteen_months(
user, pattern__category__class_field_id=class_id
)
ix = "pattern__category__name"
piv_ix_name = "category"
# Group by
grp_qs = (
base_qs.annotate(month=TruncMonth("date"))
.values(ix, "month")
.annotate(s=Sum("amount"))
# https://docs.djangoproject.com/en/dev/topics/db/aggregation/#interaction-with-default-ordering-or-order-by
.order_by()
)
if not grp_qs:
return grp_qs
# Pivot
piv = pd.DataFrame(grp_qs).pivot(index=ix, values="s", columns="month")
# Add budget col and title-ify index if in class mode
if class_id is None:
budgets = []
for c in piv.index:
try:
obj = Budget.objects.get(class_field__name=c, user=user)
budget = obj.value
except Budget.DoesNotExist:
budget = 0
budgets.append(budget)
piv["Budget"] = budgets
piv.index = piv.index.str.title()
piv = piv.fillna(0).astype(int)
piv.loc["Total"] = piv.sum() # Add total row
# Rename and convert back to list of dicts
piv.index.name = piv_ix_name
piv.columns = [safe_strftime(c, "%b_%y") for c in piv.columns]
piv.index = piv.index.fillna("Unclassified")
new_qs = piv.reset_index().to_dict("records")
return new_qs
def donations_this_year(self, filter_args, date):
return Donation.objects.filter(
done_at__gt=make_aware(datetime(date.year, 1, 1)), done_at__lte=make_aware(datetime(date.year, 12, 31))
).filter(filter_args).annotate(
month=TruncMonth('done_at')
).values('month').annotate(count=Count('id')).values_list('month', 'count').order_by('month')
