def aggregate_words(self):
return dict(total_words=Coalesce(Sum("total_words"), 0),
fuzzy_words=Coalesce(Sum("fuzzy_words"), 0),
translated_words=Coalesce(Sum("translated_words"), 0))
def nav_context_list(request):
query = request.GET.get('query', '').strip()
organizer = request.GET.get('organizer', None)
try:
page = int(request.GET.get('page', '1'))
except ValueError:
page = 1
qs_events = request.user.get_events_with_any_permission(request).filter(
Q(name__icontains=i18ncomp(query)) | Q(slug__icontains=query)
).annotate(
min_from=Min('subevents__date_from'),
max_from=Max('subevents__date_from'),
max_to=Max('subevents__date_to'),
max_fromto=Greatest(Max('subevents__date_to'), Max('subevents__date_from'))
).annotate(
order_from=Coalesce('min_from', 'date_from'),
).order_by('-order_from')
if request.user.has_active_staff_session(request.session.session_key):
qs_orga = Organizer.objects.all()
else:
qs_orga = Organizer.objects.filter(pk__in=request.user.teams.values_list('organizer', flat=True))
if query:
qs_orga = qs_orga.filter(Q(name__icontains=query) | Q(slug__icontains=query))
if query:
qs_orders = Order.objects.filter(
code__icontains=query
).select_related('event', 'event__organizer').only('event', 'code', 'pk').order_by()
if not request.user.has_active_staff_session(request.session.session_key):
qs_orders = qs_orders.filter(
Q(event__organizer_id__in=request.user.teams.filter(
all_events=True, can_view_orders=True).values_list('organizer', flat=True))
| Q(event_id__in=request.user.teams.filter(
can_view_orders=True).values_list('limit_events__id', flat=True))
)
qs_vouchers = Voucher.objects.filter(
code__icontains=query
).select_related('event', 'event__organizer').only('event', 'code', 'pk').order_by()
if not request.user.has_active_staff_session(request.session.session_key):
qs_vouchers = qs_vouchers.filter(
Q(event__organizer_id__in=request.user.teams.filter(
all_events=True, can_view_vouchers=True).values_list('organizer', flat=True))
| Q(event_id__in=request.user.teams.filter(
can_view_vouchers=True).values_list('limit_events__id', flat=True))
)
else:
qs_vouchers = Voucher.objects.none()
qs_orders = Order.objects.none()
show_user = not query or (
query and request.user.email and query.lower() in request.user.email.lower()
) or (
query and request.user.fullname and query.lower() in request.user.fullname.lower()
)
total = qs_events.count() + qs_orga.count()
pagesize = 20
offset = (page - 1) * pagesize
results = ([
serialize_user(request.user)
] if show_user else []) + [
serialize_orga(e) for e in qs_orga[offset:offset + (pagesize if query else 5)]
] + [
serialize_event(e) for e in qs_events.select_related('organizer')[offset:offset + (pagesize if query else 5)]
] + [
serialize_order(e) for e in qs_orders[offset:offset + (pagesize if query else 5)]
] + [
serialize_voucher(e) for e in qs_vouchers[offset:offset + (pagesize if query else 5)]
]
if show_user and organizer:
try:
organizer = Organizer.objects.get(pk=organizer)
except Organizer.DoesNotExist:
pass
else:
if request.user.has_organizer_permission(organizer, request=request):
organizer = serialize_orga(organizer)
if organizer in results:
results.remove(organizer)
results.insert(1, organizer)
doc = {
'results': results,
'pagination': {
"more": total >= (offset + pagesize)
}
}
return JsonResponse(doc)
def punto_venta_cerrar(
usuario_pv_id: int,
entrega_efectivo_dict: dict,
operaciones_caja_dict,
entrega_base_dict: dict,
valor_tarjeta: float,
nro_vauchers: int,
valor_dolares: float,
tasa_dolar: float,
) -> [PuntoVenta, ArqueoCaja]:
usuario = User.objects.get(pk=usuario_pv_id)
if hasattr(usuario, 'tercero'):
tercero = usuario.tercero
punto_venta_turno = tercero.turno_punto_venta_abierto
if punto_venta_turno:
from cajas.models import (ArqueoCaja, EfectivoEntregaDenominacion,
BaseDisponibleDenominacion)
from cajas.services import (
transaccion_caja_registrar_egreso_entrega_base_cierre_caja,
transaccion_caja_registrar_egreso_entrega_efectivo_cierre_caja,
operacion_caja_crear)
from cajas.models import ConceptoOperacionCaja
for operacion_caja in operaciones_caja_dict:
id_concepto = int(operacion_caja.get('id'))
concepto = ConceptoOperacionCaja.objects.get(pk=id_concepto)
valor = float(operacion_caja.get('valor'))
if valor > 0:
operacion_caja_crear(concepto_id=id_concepto,
usuario_pdv_id=usuario_pv_id,
valor=valor,
observacion='Desde cierre de caja')
# region Valores Transacciones
transacciones_egresos = punto_venta_turno.transacciones_caja.filter(
punto_venta_turno_id=punto_venta_turno.id, tipo='E')
transacciones_ingresos = punto_venta_turno.transacciones_caja.filter(
punto_venta_turno_id=punto_venta_turno.id, tipo='I')
total_ingreso_efectivo = transacciones_ingresos.aggregate(
total=Coalesce(Sum('valor_efectivo'), 0))['total']
total_ingreso_tarjeta = transacciones_ingresos.aggregate(
total=Coalesce(Sum('valor_tarjeta'), 0))['total']
total_egreso_efectivo = -transacciones_egresos.aggregate(
total=Coalesce(Sum('valor_efectivo'), 0))['total']
total_egreso_tarjeta = -transacciones_egresos.aggregate(
total=Coalesce(Sum('valor_tarjeta'), 0))['total']
cantidad_ventas_tarjeta = transacciones_ingresos.aggregate(
cantidad=Coalesce(Sum('nro_vauchers'), 0))['cantidad']
total_a_recibir_efectivo = total_ingreso_efectivo - total_egreso_efectivo
total_a_recibir_tarjeta = total_ingreso_tarjeta - total_egreso_tarjeta
# endregion
arqueo = ArqueoCaja.objects.create(
punto_venta_turno_id=punto_venta_turno.id,
valor_pago_efectivo_a_entregar=total_a_recibir_efectivo,
valor_pago_tarjeta_a_entregar=total_a_recibir_tarjeta,
nro_voucher_a_entregar=cantidad_ventas_tarjeta,
dolares_tasa=tasa_dolar,
valor_dolares_entregados=valor_dolares,
valor_tarjeta_entregados=valor_tarjeta,
nro_voucher_entregados=nro_vauchers)
for denominacion in entrega_efectivo_dict:
cantidad = int(denominacion.get('cantidad'))
valor = float(denominacion.get('valor'))
valor_total = cantidad * valor
if cantidad > 0:
EfectivoEntregaDenominacion.objects.create(
arqueo_caja=arqueo,
valor_total=valor_total,
**denominacion)
for denominacion in entrega_base_dict:
cantidad = int(denominacion.get('cantidad'))
valor = float(denominacion.get('valor'))
valor_total = cantidad * valor
if cantidad > 0:
BaseDisponibleDenominacion.objects.create(
arqueo_caja=arqueo,
valor_total=valor_total,
**denominacion)
if 'test' in sys.argv:
print(
'-----------------DATOS ARQUEO CAJA-------------------------'
)
print('Arqueo dinero entrega efectivo %s' %
arqueo.valor_entrega_efectivo)
print('Arqueo dinero entrega base efectivo %s' %
arqueo.valor_base_dia_siguiente)
print('Arqueo dinero entrega dolares %s' %
arqueo.valor_dolares_en_pesos)
print('Arqueo dinero entrega efectivo total %s' %
arqueo.valor_entrega_efectivo_total)
print(
'-----------------------------------------------------------'
)
print('Valor ingresos totales %s' %
(total_ingreso_efectivo + total_ingreso_tarjeta))
print('Valor egresos totales %s' %
(total_egreso_efectivo + total_egreso_tarjeta))
print('Valor ingreso transacciones en efectivo %s' %
total_ingreso_efectivo)
print('Valor ingreso transacciones en tarjeta %s' %
total_ingreso_tarjeta)
print('Valor egresos transacciones en efectivo %s' %
total_egreso_efectivo)
print('Valor egresos transacciones en tarjeta %s' %
total_egreso_tarjeta)
print('Valor a recibir transacciones en efectivo %s' %
total_a_recibir_efectivo)
total_entrega_efectivo = arqueo.valor_entrega_efectivo_total
transaccion_caja_registrar_egreso_entrega_efectivo_cierre_caja(
punto_venta_turno_id=punto_venta_turno.id,
valor_efectivo=total_entrega_efectivo)
transaccion_caja_registrar_egreso_entrega_base_cierre_caja(
punto_venta_turno_id=punto_venta_turno.id,
valor_efectivo=arqueo.valor_base_dia_siguiente)
total_entrega_tarjeta = arqueo.valor_tarjeta_entregados
descuadre_efectivo = total_a_recibir_efectivo - total_entrega_efectivo
descuadre_tarjeta = total_a_recibir_tarjeta - total_entrega_tarjeta
if 'test' in sys.argv:
print(
'-----------------------------------------------------------'
)
print('Descuadre por efectivo %s' % descuadre_efectivo)
print('Descuadre por tarjeta %s' % descuadre_tarjeta)
punto_venta = punto_venta_turno.punto_venta
punto_venta.abierto = False
punto_venta.usuario_actual = None
punto_venta.save()
punto_venta_turno.finish = timezone.now()
punto_venta_turno.saldo_cierre_caja = arqueo.diferencia
punto_venta_turno.save()
if 'test' in sys.argv:
print('el saldo es %s' %
punto_venta_turno.diferencia_cierre_caja)
else:
raise serializers.ValidationError({
'_error':
'Este tercero no posee ningún punto de venta abierto actualmente'
})
else:
raise serializers.ValidationError({
'_error':
'El usuario no tiene un tercero relacionado, por ende, no tiene ningún punto de venta que cerrar'
})
return punto_venta, arqueo
class CotizacionComponenteViewSet(viewsets.ModelViewSet):
permission_classes = [permissions.IsAuthenticated]
queryset = CotizacionComponente.objects.select_related(
'responsable',
'creado_por',
'creado_por',
'cliente',
'cliente__colaborador_componentes',
'cliente__canal',
'ciudad',
'contacto',
'ciudad__departamento',
'ciudad__departamento__pais',
).prefetch_related(
'items',
'items__forma_pago',
'items__forma_pago__canal',
'adjuntos',
'versiones',
'seguimientos',
'envios_emails',
'envios_emails__creado_por',
'envios_emails__archivo',
'seguimientos__creado_por',
'seguimientos__documento_cotizacion',
).annotate(valor_total=Coalesce(Sum('items__valor_total'), 0)).all()
serializer_class = CotizacionComponenteSerializer
def perform_create(self, serializer):
serializer.save(creado_por=self.request.user)
def retrieve(self, request, *args, **kwargs):
self.serializer_class = CotizacionComponenteConDetalleSerializer
return super().retrieve(request, *args, **kwargs)
def update(self, request, *args, **kwargs):
self.serializer_class = CotizacionComponenteConDetalleSerializer
return super().update(request, *args, **kwargs)
def list(self, request, *args, **kwargs):
self.queryset = self.queryset.using('read_only')
return super().list(request, *args, **kwargs)
def destroy(self, request, *args, **kwargs):
cotizacion = self.get_object()
if cotizacion.estado == 'INI' and (cotizacion.nro_consecutivo is None):
cotizacion.items.all().delete()
return super().destroy(request, *args, **kwargs)
raise ValidationError(
{'_error': 'Imposible eliminar cotización, ya ha sido enviada'})
@action(detail=True, methods=['post'])
def relacionar_factura(self, request, pk=None):
from .services import relacionar_cotizacion_con_factura
factura_id = request.POST.get('factura_id')
accion = request.POST.get('accion')
relacionar_cotizacion_con_factura(cotizacion_componente_id=pk,
factura_id=factura_id,
accion=accion)
serializer = self.get_serializer(self.get_object())
return Response(serializer.data)
@action(detail=True, methods=['post'])
def asignar_consecutivo(self, request, pk=None):
from .services import cotizacion_componentes_asignar_nro_consecutivo
cotizacion_componente = cotizacion_componentes_asignar_nro_consecutivo(
cotizacion_componente=self.get_object())
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_por_ano_mes(self, request):
months = self.request.GET.get('months').split(',')
years = self.request.GET.get('years').split(',')
lista = self.queryset.filter((Q(orden_compra_fecha__year__in=years)
& Q(orden_compra_fecha__month__in=months)
& Q(estado__in=['PRO', 'FIN']))
| Q(estado__in=['ENV', 'REC']))
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def cambiar_estado(self, request, pk=None):
from .services import cotizacion_componentes_cambiar_estado
orden_compra_fecha = request.POST.get('orden_compra_fecha', None)
orden_compra_nro = request.POST.get('orden_compra_nro', None)
orden_compra_valor = float(request.POST.get('orden_compra_valor', 0))
nuevo_estado = request.POST.get('nuevo_estado', None)
razon_rechazo = request.POST.get('razon_rechazo', None)
fecha_verificacion_proximo_seguimiento = request.POST.get(
'fecha_verificacion_proximo_seguimiento', None)
cotizacion_componentes_cambiar_estado(
cotizacion_componente_id=pk,
nuevo_estado=nuevo_estado,
razon_rechazo=razon_rechazo,
usuario=self.request.user,
fecha_verificacion_proximo_seguimiento=
fecha_verificacion_proximo_seguimiento,
orden_compra_nro=orden_compra_nro,
orden_compra_valor=orden_compra_valor,
orden_compra_fecha=orden_compra_fecha)
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(self.get_object())
return Response(serializer.data)
@action(detail=True, methods=['post'])
def cambiar_fecha_proximo_seguimiento_lista(self, request, pk=None):
from .services import cotizacion_componentes_cambiar_fecha_proximo_seguimiento
fecha_verificacion_proximo_seguimiento = request.POST.get(
'fecha_verificacion_proximo_seguimiento', None)
fecha_proximo_seguimiento_descripcion = request.POST.get(
'fecha_proximo_seguimiento_descripcion', None)
cotizacion_componentes_cambiar_fecha_proximo_seguimiento(
cotizacion_componente_id=pk,
usuario=self.request.user,
fecha_verificacion_proximo_seguimiento=
fecha_verificacion_proximo_seguimiento,
fecha_proximo_seguimiento_descripcion=
fecha_proximo_seguimiento_descripcion)
serializer = self.get_serializer(self.get_object())
return Response(serializer.data)
# TODO: Mejorar este método, muchos queries a la hora de enviar
@action(detail=True, methods=['post'])
def enviar(self, request, pk=None):
self.serializer_class = CotizacionComponenteConDetalleSerializer
from .services import cotizacion_componentes_enviar
email_uno = request.POST.get('email_uno', None)
email_dos = request.POST.get('email_dos', None)
email_tres = request.POST.get('email_tres', None)
email_cuatro = request.POST.get('email_cuatro', None)
email_asesor = request.POST.get('email_asesor', None)
no_enviar = request.POST.get('no_enviar', None)
fecha_verificacion_proximo_seguimiento = request.POST.get(
'fecha_verificacion_proximo_seguimiento', None)
emails_destino = []
if email_uno:
emails_destino.append(email_uno)
if email_dos:
emails_destino.append(email_dos)
if email_tres:
emails_destino.append(email_tres)
if email_cuatro:
emails_destino.append(email_cuatro)
if email_asesor:
emails_destino.append(email_asesor)
cotizacion_componentes_enviar(
cotizacion_componente=self.queryset.get(pk=pk),
no_enviar=no_enviar,
request=request,
emails_destino=emails_destino,
fecha_verificacion_proximo_seguimiento=
fecha_verificacion_proximo_seguimiento)
serializer = self.get_serializer(self.get_object())
return Response(serializer.data)
@action(detail=True, methods=['post'])
def imprimir(self, request, pk=None):
from .services import cotizacion_componentes_generar_pdf
response = HttpResponse(content_type='application/pdf')
output = cotizacion_componentes_generar_pdf(
cotizacion_componente=self.queryset.get(pk=pk), request=request)
response.write(output.getvalue())
output.close()
response[
'Content-Disposition'] = 'attachment; filename="somefilename.pdf"'
response['Content-Transfer-Encoding'] = 'binary'
return response
@action(detail=True, methods=['post'])
def adicionar_seguimiento(self, request, pk=None):
cotizacion_componente = self.get_object()
tipo_seguimiento = request.POST.get('tipo_seguimiento')
descripcion = request.POST.get('descripcion')
fecha = request.POST.get('fecha', None)
fecha_verificacion_proximo_seguimiento = request.POST.get(
'fecha_verificacion_proximo_seguimiento', None)
from .services import cotizacion_componentes_add_seguimiento
cotizacion_componentes_add_seguimiento(
cotizacion_componente_id=cotizacion_componente.id,
tipo_seguimiento=tipo_seguimiento,
descripcion=descripcion,
creado_por=self.request.user,
fecha=fecha,
fecha_verificacion_proximo_seguimiento=
fecha_verificacion_proximo_seguimiento)
cotizacion_componente.refresh_from_db()
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def eliminar_seguimiento(self, request, pk=None):
cotizacion_componente = self.get_object()
seguimiento_id = request.POST.get('seguimiento_id')
from .services import cotizacion_componentes_delete_seguimiento
cotizacion_componentes_delete_seguimiento(
cotizacion_componente_id=cotizacion_componente.id,
cotizacion_componente_seguimiento_id=seguimiento_id,
eliminado_por=self.request.user)
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def adicionar_item(self, request, pk=None):
cotizacion = self.get_object()
tipo_item = request.POST.get('tipo_item')
precio_unitario = request.POST.get('precio_unitario')
item_descripcion = request.POST.get('item_descripcion')
item_referencia = request.POST.get('item_referencia')
item_unidad_medida = request.POST.get('item_unidad_medida')
tasa = request.POST.get('tasa', 0)
moneda_origen = request.POST.get('moneda_origen')
moneda_origen_costo = request.POST.get('moneda_origen_costo', 0)
id_item = request.POST.get('id_item', None)
forma_pago_id = request.POST.get('forma_pago_id', None)
tipo_transporte = request.POST.get('tipo_transporte', None)
from .services import cotizacion_componentes_adicionar_item
cotizacion_componente = cotizacion_componentes_adicionar_item(
tipo_item=tipo_item,
cotizacion_componente_id=cotizacion.id,
precio_unitario=precio_unitario,
id_item=id_item,
item_descripcion=item_descripcion,
item_referencia=item_referencia,
item_unidad_medida=item_unidad_medida,
forma_pago_id=forma_pago_id,
tipo_transporte=tipo_transporte,
tasa=tasa,
moneda_origen=moneda_origen,
moneda_origen_costo=moneda_origen_costo)
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def eliminar_item(self, request, pk=None):
cotizacion = self.get_object()
id_item_cotizacion = request.POST.get('id_item_cotizacion')
from .services import cotizacion_componentes_item_eliminar
cotizacion_componentes_item_eliminar(
item_componente_id=id_item_cotizacion)
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def cambiar_posicion_item(self, request, pk=None):
from .services import cotizacion_componentes_item_cambiar_posicion
# print(request.META.get('HTTP_X_FORWARDED_FOR'))
# print(request.META.get('REMOTE_ADDR'))
cotizacion_componente = self.get_object()
item_uno_id = request.POST.get('item_uno_id')
item_dos_id = request.POST.get('item_dos_id')
cotizacion_componentes_item_cambiar_posicion(
cotizacion_componente_id=cotizacion_componente.id,
item_uno_id=item_uno_id,
item_dos_id=item_dos_id)
self.serializer_class = CotizacionComponenteConDetalleSerializer
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_por_estado(self, request):
estado = self.request.GET.get('estado')
if estado == 'TOD':
lista = self.queryset
else:
lista = self.queryset.filter(Q(estado=estado) | Q(estado='INI'))
user = self.request.user
ver_todas = request.user.has_perm(
'cotizaciones_componentes.list_todos_vendedores_cotizacioncomponente'
)
if not (user.is_superuser or ver_todas):
lista = lista.filter(
(Q(responsable__isnull=True) & Q(creado_por=user))
| (Q(responsable__isnull=False) & Q(responsable=user)))
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_por_cliente(self, request):
cliente_id = self.request.GET.get('cliente_id')
self.serializer_class = CotizacionComponenteConDetalleSerializer
lista = self.queryset.filter(cliente_id=cliente_id)
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_tuberia_ventas(self, request):
self.serializer_class = CotizacionComponenteTuberiaVentasSerializer
lista = CotizacionComponente.objects.using(
'read_only').prefetch_related(
'responsable',
'responsable__mi_colaborador',
'creado_por',
'cliente',
).exclude(estado__in=['INI', 'ELI', 'FIN'])
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_por_cliente_para_relacionar_factura(self, request):
cliente_id = self.request.GET.get('cliente_id')
self.serializer_class = CotizacionComponenteConDetalleSerializer
lista = self.queryset.using('read_only').filter(
Q(cliente_id=cliente_id)
& Q(estado__in=['PRO', 'FIN', 'ENV', 'REC']))
user = self.request.user
if not user.is_superuser:
lista = lista.filter(
(Q(responsable__isnull=True) & Q(creado_por=user))
| (Q(responsable__isnull=False) & Q(responsable=user)))
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=False, http_method_names=[
'get',
])
def cotizaciones_en_edicion_asesor(self, request):
user = self.request.user
self.serializer_class = CotizacionComponenteConDetalleSerializer
lista = self.queryset.using('read_only').filter(estado='INI')
if not user.is_superuser:
lista = self.queryset.filter(estado='INI').filter(
(Q(responsable__isnull=True) & Q(creado_por=user))
| (Q(responsable__isnull=False) & Q(responsable=user)))
serializer = self.get_serializer(lista, many=True)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def upload_archivo(self, request, pk=None):
cotizacion_componente = self.get_object()
nombre_archivo = self.request.POST.get('nombre')
archivo = self.request.FILES['archivo']
tipo = self.request.POST.get('tipo')
adjunto_cotizacion = CotizacionComponenteAdjunto()
if tipo == 'imagen':
adjunto_cotizacion.imagen = archivo
elif tipo == 'archivo':
adjunto_cotizacion.adjunto = archivo
adjunto_cotizacion.cotizacion_componente = cotizacion_componente
adjunto_cotizacion.nombre_adjunto = nombre_archivo
adjunto_cotizacion.creado_por = self.request.user
adjunto_cotizacion.save()
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
@action(detail=True, methods=['post'])
def delete_archivo(self, request, pk=None):
cotizacion_componente = self.get_object()
adjunto_id = self.request.POST.get('adjunto_id')
adjunto = CotizacionComponenteAdjunto.objects.get(pk=adjunto_id)
adjunto.delete()
serializer = self.get_serializer(cotizacion_componente)
return Response(serializer.data)
def get(self, request, organization):
"""
List an Organization's Releases
```````````````````````````````
Return a list of releases for a given organization.
:pparam string organization_slug: the organization short name
:qparam string query: this parameter can be used to create a
"starts with" filter for the version.
"""
query = request.GET.get("query")
with_health = request.GET.get("health") == "1"
status_filter = request.GET.get("status", "open")
flatten = request.GET.get("flatten") == "1"
sort = request.GET.get("sort") or "date"
health_stat = request.GET.get("healthStat") or "sessions"
summary_stats_period = request.GET.get("summaryStatsPeriod") or "14d"
health_stats_period = request.GET.get("healthStatsPeriod") or (
"24h" if with_health else "")
if summary_stats_period not in STATS_PERIODS:
raise ParseError(detail=get_stats_period_detail(
"summaryStatsPeriod", STATS_PERIODS))
if health_stats_period and health_stats_period not in STATS_PERIODS:
raise ParseError(detail=get_stats_period_detail(
"healthStatsPeriod", STATS_PERIODS))
if health_stat not in ("sessions", "users"):
raise ParseError(detail="invalid healthStat")
paginator_cls = OffsetPaginator
paginator_kwargs = {}
try:
filter_params = self.get_filter_params(request,
organization,
date_filter_optional=True)
except NoProjects:
return Response([])
# This should get us all the projects into postgres that have received
# health data in the last 24 hours. If health data is not requested
# we don't upsert releases.
if with_health:
debounce_update_release_health_data(organization,
filter_params["project_id"])
queryset = Release.objects.filter(organization=organization)
if status_filter:
try:
status_int = ReleaseStatus.from_string(status_filter)
except ValueError:
raise ParseError(detail="invalid value for status")
if status_int == ReleaseStatus.OPEN:
queryset = queryset.filter(
Q(status=status_int) | Q(status=None))
else:
queryset = queryset.filter(status=status_int)
queryset = queryset.select_related("owner").annotate(date=Coalesce(
"date_released", "date_added"), )
queryset = add_environment_to_queryset(queryset, filter_params)
if query:
query_q = Q(version__icontains=query)
suffix_match = _release_suffix.match(query)
if suffix_match is not None:
query_q |= Q(version__icontains="%s+%s" %
suffix_match.groups())
queryset = queryset.filter(query_q)
select_extra = {}
queryset = queryset.distinct()
if flatten:
select_extra[
"_for_project_id"] = "sentry_release_project.project_id"
if sort == "date":
queryset = queryset.filter(
projects__id__in=filter_params["project_id"]).order_by("-date")
paginator_kwargs["order_by"] = "-date"
elif sort in (
"crash_free_sessions",
"crash_free_users",
"sessions",
"users",
"sessions_24h",
"users_24h",
):
if not flatten:
return Response(
{
"detail":
"sorting by crash statistics requires flattening (flatten=1)"
},
status=400,
)
paginator_cls = MergingOffsetPaginator
paginator_kwargs.update(
data_load_func=lambda offset, limit:
get_project_releases_by_stability(
project_ids=filter_params["project_id"],
environments=filter_params.get("environment"),
scope=sort,
offset=offset,
stats_period=summary_stats_period,
limit=limit,
),
apply_to_queryset=lambda queryset, rows: queryset.filter(
projects__id__in=list(x[0] for x in rows),
version__in=list(x[1] for x in rows)),
key_from_model=lambda x: (x._for_project_id, x.version),
)
else:
return Response({"detail": "invalid sort"}, status=400)
queryset = queryset.extra(select=select_extra)
queryset = add_date_filter_to_queryset(queryset, filter_params)
return self.paginate(
request=request,
queryset=queryset,
paginator_cls=paginator_cls,
on_results=lambda x: serialize(
x,
request.user,
with_health_data=with_health,
health_stat=health_stat,
health_stats_period=health_stats_period,
summary_stats_period=summary_stats_period,
environments=filter_params.get("environment") or None,
),
**paginator_kwargs)
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [
{
'provider':
Provider.PROVIDER_AZURE,
'alias':
'subscription_guid', # FIXME: probably wrong
'annotations': {},
'end_date':
'costentrybill__billing_period_end',
'filters': {
'subscription_guid': [
{
'field': 'subscription_guid',
'operation': 'icontains',
'composition_key': 'account_filter'
},
],
'service_name': {
'field': 'service_name',
'operation': 'icontains'
},
'resource_location': {
'field': 'resource_location',
'operation': 'icontains'
},
'instance_type': {
'field': 'instance_type',
'operation': 'icontains'
}
},
'group_by_options': [
'service_name', 'subscription_guid', 'resource_location',
'instance_type'
],
'tag_column':
'tags',
'report_type': {
'costs': {
'aggregates': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum('pretax_cost'),
'derived_cost':
Sum(Value(0, output_field=DecimalField())),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
},
'aggregate_key':
'pretax_cost',
'annotations': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum('pretax_cost'),
'derived_cost':
Value(0, output_field=DecimalField()),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'cost_units':
Coalesce(Max('currency'), Value('USD'))
},
'delta_key': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
)
},
'filter': [{}],
'cost_units_key':
'currency',
'cost_units_fallback':
'USD',
'sum_columns': [
'cost', 'infrastructure_cost', 'derived_cost',
'markup_cost'
],
'default_ordering': {
'cost': 'desc'
},
},
'instance_type': {
'aggregates': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum('pretax_cost'),
'derived_cost':
Sum(Value(0, output_field=DecimalField())),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'count':
Sum(Value(0, output_field=DecimalField())),
'usage':
Sum('usage_quantity'),
},
'aggregate_key':
'usage_quantity',
'annotations': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum('pretax_cost'),
'derived_cost':
Value(0, output_field=DecimalField()),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'cost_units':
Coalesce(Max('currency'), Value('USD')),
'count':
Max('instance_count'),
'count_units':
Value('instance_types', output_field=CharField()),
'usage':
Sum('usage_quantity'),
# FIXME: Waiting on MSFT for usage_units default
'usage_units':
Coalesce(Max('unit_of_measure'),
Value('Instance Type Placeholder'))
},
'delta_key': {
'usage': Sum('usage_quantity')
},
'filter': [{
'field': 'instance_type',
'operation': 'isnull',
'parameter': False
}],
'group_by': ['instance_type'],
'cost_units_key':
'currency',
'cost_units_fallback':
'USD',
'usage_units_key':
'unit_of_measure',
'usage_units_fallback':
'Instance Type Placeholder', # FIXME: Waiting on MSFT
'count_units_fallback':
'instances',
'sum_columns': [
'usage', 'cost', 'infrastructure_cost',
'derived_cost', 'markup_cost', 'count'
],
'default_ordering': {
'usage': 'desc'
},
},
'storage': {
'aggregates': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'usage':
Sum('usage_quantity'),
'infrastructure_cost':
Sum('pretax_cost'),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'derived_cost':
Sum(Value(0, output_field=DecimalField())),
},
'aggregate_key':
'usage_quantity',
'annotations': {
'cost':
Sum(
Coalesce(F('pretax_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum('pretax_cost'),
'derived_cost':
Value(0, output_field=DecimalField()),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'cost_units':
Coalesce(Max('currency'), Value('USD')),
'usage':
Sum('usage_quantity'),
# FIXME: Waiting on MSFT for usage_units default
'usage_units':
Coalesce(Max('unit_of_measure'),
Value('Storage Type Placeholder'))
},
'delta_key': {
'usage': Sum('usage_quantity')
},
'filter': [{
'field': 'service_name',
'operation': 'contains',
'parameter': 'Storage'
}],
'cost_units_key':
'currency',
'cost_units_fallback':
'USD',
'usage_units_key':
'unit_of_measure',
'usage_units_fallback':
'Storage Type Placeholder', # FIXME
'sum_columns': [
'usage', 'cost', 'infrastructure_cost',
'derived_cost', 'markup_cost'
],
'default_ordering': {
'usage': 'desc'
},
},
'tags': {
'default_ordering': {
'cost': 'desc'
},
},
},
'start_date':
'costentrybill__billing_period_start',
'tables': {
'query': AzureCostEntryLineItemDailySummary,
},
},
]
super().__init__(provider, report_type)
def do(self):
timestamp = timezone.now() # make sure all entries share same timestamp
# generate general statistics
dailyStatisticsGeneral = DailyStatisticsGeneral()
dailyStatisticsGeneral.timestamp = timestamp
dailyStatisticsGeneral.persons = Person.objects.count()
dailyStatisticsGeneral.children_male = Person.objects.filter(membertype=Person.CHILD, gender=Person.MALE).count()
dailyStatisticsGeneral.children_female = Person.objects.filter(membertype=Person.CHILD, gender=Person.FEMALE).count()
dailyStatisticsGeneral.children = dailyStatisticsGeneral.children_male + dailyStatisticsGeneral.children_female
dailyStatisticsGeneral.volunteers_male = Person.objects.filter(gender=Person.MALE, volunteer__isnull=False).count()
dailyStatisticsGeneral.volunteers_female = Person.objects.filter(gender=Person.FEMALE, volunteer__isnull=False).count()
dailyStatisticsGeneral.volunteers = dailyStatisticsGeneral.volunteers_male + dailyStatisticsGeneral.volunteers_female
dailyStatisticsGeneral.departments = Department.objects.filter(closed_dtm=None).count()
dailyStatisticsGeneral.unions = Union.objects.count()
dailyStatisticsGeneral.waitinglist_male = Person.objects.filter(waitinglist__isnull=False, gender=Person.MALE).distinct().count()
dailyStatisticsGeneral.waitinglist_female = Person.objects.filter(waitinglist__isnull=False, gender=Person.FEMALE).distinct().count()
dailyStatisticsGeneral.waitinglist = dailyStatisticsGeneral.waitinglist_male + dailyStatisticsGeneral.waitinglist_female
dailyStatisticsGeneral.family_visits = Family.objects.filter(last_visit_dtm__gt=(timestamp-datetime.timedelta(days=1))).count()
dailyStatisticsGeneral.dead_profiles = Family.objects.filter(last_visit_dtm__lt=(timestamp-datetime.timedelta(days=365))).count()
dailyStatisticsGeneral.current_activity_participants = Person.objects.filter(member__activityparticipant__activity__end_date__gte=timestamp,
member__activityparticipant__activity__start_date__lte=timestamp ).distinct().count()
dailyStatisticsGeneral.activity_participants_male = Person.objects.filter(member__activityparticipant__activity__isnull=False, gender=Person.MALE).distinct().count()
dailyStatisticsGeneral.activity_participants_female = Person.objects.filter(member__activityparticipant__activity__isnull=False, gender=Person.FEMALE).distinct().count()
dailyStatisticsGeneral.activity_participants = dailyStatisticsGeneral.activity_participants_male + dailyStatisticsGeneral.activity_participants_female
dailyStatisticsGeneral.payments = Payment.objects.filter(refunded_dtm=None, confirmed_dtm__isnull=False).aggregate(sum=Coalesce(Sum('amount_ore'), 0))['sum']
dailyStatisticsGeneral.payments_transactions = Payment.objects.filter(refunded_dtm=None, confirmed_dtm__isnull=False).count()
dailyStatisticsGeneral.save()
# generate daily department statistics
departments = Department.objects.filter(closed_dtm=None)
for department in departments:
dailyStatisticsDepartment = DailyStatisticsDepartment()
dailyStatisticsDepartment.timestamp = timestamp
dailyStatisticsDepartment.department = department
dailyStatisticsDepartment.active_activities = Activity.objects.filter(department=department,
start_date__lte=timestamp,
end_date__gte=timestamp).count()
dailyStatisticsDepartment.activities = Activity.objects.filter(department=department).count()
dailyStatisticsDepartment.current_activity_participants = Person.objects.filter(member__activityparticipant__activity__start_date__lte=timestamp,
member__activityparticipant__activity__end_date__gte=timestamp,
member__activityparticipant__activity__department=department).distinct().count()
dailyStatisticsDepartment.activity_participants = ActivityParticipant.objects.filter(activity__department=department).count()
dailyStatisticsDepartment.members = 0 # TODO: to loosely defined now
dailyStatisticsDepartment.waitinglist = Person.objects.filter(waitinglist__department=department).distinct().count()
firstWaitingListItem = WaitingList.objects.filter(department=department).order_by('on_waiting_list_since').first()
if firstWaitingListItem:
dailyStatisticsDepartment.waitingtime = timestamp.date() - firstWaitingListItem.on_waiting_list_since
else:
dailyStatisticsDepartment.waitingtime = datetime.timedelta(days=0)
dailyStatisticsDepartment.payments = Payment.objects.filter(activity__department=department,
refunded_dtm=None,
confirmed_dtm__isnull=False).aggregate(sum=Coalesce(Sum('amount_ore'), 0))['sum']
dailyStatisticsDepartment.volunteers_male = Person.objects.filter(volunteer__department=department, gender=Person.MALE).distinct().count()
dailyStatisticsDepartment.volunteers_female = Person.objects.filter(volunteer__department=department, gender=Person.FEMALE).distinct().count()
dailyStatisticsDepartment.volunteers = dailyStatisticsDepartment.volunteers_male + dailyStatisticsDepartment.volunteers_female
dailyStatisticsDepartment.save()
# generate daily union statistics
unions = Union.objects.all()
for union in unions:
dailyStatisticsUnion = DailyStatisticsUnion()
dailyStatisticsUnion.timestamp = timestamp
dailyStatisticsUnion.union = union
dailyStatisticsUnion.departments = Department.objects.filter(union=union).count()
dailyStatisticsUnion.active_activities = Activity.objects.filter(department__union=union,
start_date__lte=timestamp,
end_date__gte=timestamp).count()
dailyStatisticsUnion.activities = Activity.objects.filter(department__union=union).count()
dailyStatisticsUnion.current_activity_participants = Person.objects.filter(member__activityparticipant__activity__start_date__lte=timestamp,
member__activityparticipant__activity__end_date__gte=timestamp,
member__activityparticipant__activity__department__union=union).distinct().count()
dailyStatisticsUnion.activity_participants = ActivityParticipant.objects.filter(activity__department__union=union).count()
dailyStatisticsUnion.members = 0 # TODO: to loosely defined now
dailyStatisticsUnion.waitinglist = Person.objects.filter(waitinglist__department__union=union).distinct().count()
dailyStatisticsUnion.payments = Payment.objects.filter(activity__department__union=union,
refunded_dtm=None,
confirmed_dtm__isnull=False).aggregate(sum=Coalesce(Sum('amount_ore'), 0))['sum']
dailyStatisticsUnion.volunteers_male = Person.objects.filter(volunteer__department__union=union, gender=Person.MALE).distinct().count()
dailyStatisticsUnion.volunteers_female = Person.objects.filter(volunteer__department__union=union, gender=Person.FEMALE).distinct().count()
dailyStatisticsUnion.volunteers = dailyStatisticsUnion.volunteers_male + dailyStatisticsUnion.volunteers_female
dailyStatisticsUnion.save()
# generate daily region statistics
regions = ('DK01', 'DK02', 'DK03', 'DK04', 'DK05')
for region in regions:
dailyStatisticsRegion = DailyStatisticsRegion()
zipsInRegion = ZipcodeRegion.objects.filter(region=region).values_list('zipcode', flat=True) # There are no easy foreing key to identify region
dailyStatisticsRegion.timestamp = timestamp
dailyStatisticsRegion.region = region
# No unions - since unions may span regions
dailyStatisticsRegion.departments = Department.objects.annotate().filter(zipcode__in=zipsInRegion).count()
dailyStatisticsRegion.active_activities = Activity.objects.filter(department__zipcode__in=zipsInRegion,
start_date__lte=timestamp,
end_date__gte=timestamp).count()
dailyStatisticsRegion.activities = Activity.objects.filter(department__zipcode__in=zipsInRegion).count()
dailyStatisticsRegion.current_activity_participants = Person.objects.filter(member__activityparticipant__activity__start_date__lte=timestamp,
member__activityparticipant__activity__end_date__gte=timestamp,
member__activityparticipant__activity__department__zipcode__in=zipsInRegion).distinct().count()
dailyStatisticsRegion.activity_participants = ActivityParticipant.objects.filter(activity__department__zipcode__in=zipsInRegion).count()
dailyStatisticsRegion.members = 0 # TODO: to loosely defined now
dailyStatisticsRegion.waitinglist = Person.objects.filter(waitinglist__department__zipcode__in=zipsInRegion).distinct().count()
dailyStatisticsRegion.payments = Payment.objects.filter(activity__department__zipcode__in=zipsInRegion,
refunded_dtm=None,
confirmed_dtm__isnull=False).aggregate(sum=Coalesce(Sum('amount_ore'), 0))['sum']
dailyStatisticsRegion.volunteers_male = Person.objects.filter(volunteer__department__zipcode__in=zipsInRegion, gender=Person.MALE).distinct().count()
dailyStatisticsRegion.volunteers_female = Person.objects.filter(volunteer__department__zipcode__in=zipsInRegion, gender=Person.FEMALE).distinct().count()
dailyStatisticsRegion.volunteers = dailyStatisticsRegion.volunteers_male + dailyStatisticsRegion.volunteers_female
dailyStatisticsRegion.save()
def execute_individual_query(self, org_unit_applied=False): # noqa: C901
"""Execute query and return provided data.
Returns:
(Dict): Dictionary response of query params, data, and total
"""
data = []
with tenant_context(self.tenant):
query_table = self.query_table
LOG.debug(f"Using query table: {query_table}")
tag_results = None
query = query_table.objects.filter(self.query_filter)
query_data = query.annotate(**self.annotations)
query_group_by = ["date"] + self._get_group_by()
query_order_by = ["-date"]
query_order_by.extend([self.order]) # add implicit ordering
annotations = copy.deepcopy(
self._mapper.report_type_map.get("annotations", {}))
if not self.parameters.parameters.get("compute_count"):
# Query parameter indicates count should be removed from DB queries
annotations.pop("count", None)
annotations.pop("count_units", None)
query_data = query_data.values(*query_group_by).annotate(
**annotations)
if "account" in query_group_by:
query_data = query_data.annotate(account_alias=Coalesce(
F(self._mapper.provider_map.get("alias")),
"usage_account_id"))
if self.parameters.parameters.get("check_tags"):
tag_results = self._get_associated_tags(
query_table, self.query_filter)
query_sum = self._build_sum(query, annotations)
if self._limit and query_data and not org_unit_applied:
query_data = self._group_by_ranks(query, query_data)
if not self.parameters.get("order_by"):
# override implicit ordering when using ranked ordering.
query_order_by[-1] = "rank"
if self._delta:
query_data = self.add_deltas(query_data, query_sum)
query_data = self.order_by(query_data, query_order_by)
# Fetch the data (returning list(dict))
query_results = list(query_data)
# Resolve tag exists for unique account returned
# if tag_results is not Falsey
# Append the flag to the query result for the report
if tag_results is not None:
# Add the tag results to the report query result dicts
for res in query_results:
res["tags_exist"] = tag_results.get(
res["account_alias"], False)
if not self.is_csv_output:
groups = copy.deepcopy(query_group_by)
groups.remove("date")
data = self._apply_group_by(query_results, groups)
data = self._transform_data(query_group_by, 0, data)
else:
data = query_results
key_order = list(["units"] + list(annotations.keys()))
ordered_total = {
total_key: query_sum[total_key]
for total_key in key_order if total_key in query_sum
}
ordered_total.update(query_sum)
query_sum = ordered_total
query_data = data
return query_data, query_sum
class RackRoleViewSet(ModelViewSet):
queryset = RackRole.objects.annotate(
rack_count=Coalesce(get_subquery(Rack, 'role'), 0))
serializer_class = serializers.RackRoleSerializer
filterset_class = filters.RackRoleFilterSet
def goals_lost(self, obj):
home_played = Match.objects.filter(Q(host_team=obj))
away_played = Match.objects.filter(Q(guest_team=obj))
home_lost = home_played.aggregate(goals=Coalesce(Sum('guest_team_goals', output_field=IntegerField()), 0))
away_lost = away_played.aggregate(goals=Coalesce(Sum('host_team_goals', output_field=IntegerField()), 0))
return home_lost['goals'] + away_lost['goals']
def ajax_upload(request, folder_id=None):
folder = None
path = request.POST.get('path')
path_split = path.split('/') if path else []
# check permissions and data
error_msg = None
if not request.user.is_authenticated:
# User is not logged in. Return a generic message that gives
# no data info (such as whether a folder exists or not)
error_msg = filer.admin.clipboardadmin.NO_PERMISSIONS_FOR_FOLDER
elif folder_id:
try:
folder = Folder.objects.get(pk=folder_id)
except Folder.DoesNotExist:
# A folder with id=folder_id does not exist so return
# an error message specifying this
error_msg = filer.admin.clipboardadmin.NO_FOLDER_ERROR
else:
# Now check if the user has sufficient permissions to
# upload a file to the folder with id=folder_id and return
# an error message if not
no_folder_perms = (not folder.has_add_children_permission(request)
or (path and not folder.can_have_subfolders))
if no_folder_perms:
error_msg = filer.admin.clipboardadmin.NO_PERMISSIONS_FOR_FOLDER
elif (not request.user.is_superuser and path_split and
not filer.settings.FILER_ALLOW_REGULAR_USERS_TO_ADD_ROOT_FOLDERS):
# If uploading the file to Unsorted Uploads (i.e. no folder_id)
# but filer is set to disallow regular users to add
# folders there and the user is not a superuser and is uploading
# folders that aren't yet created on the server (i.e.
# specifying the path param with folders that don't yet exist)
# return an error message
if not Folder.objects.filter(name=path_split[0], parent=None).exists():
error_msg = filer.admin.clipboardadmin.NO_PERMISSIONS_FOR_FOLDER
if error_msg:
return JsonResponse({'error': error_msg})
try:
if len(request.FILES) == 1:
# dont check if request is ajax or not, just grab the file
upload, filename, is_raw, mime_type = handle_request_files_upload(
request)
else:
# else process the request as usual
upload, filename, is_raw, mime_type = handle_upload(request)
# find the file type
for filer_class in filer_settings.FILER_FILE_MODELS:
FileSubClass = load_model(filer_class)
# TODO: What if there are more than one that qualify?
if FileSubClass.matches_file_type(filename, upload, mime_type):
FileForm = modelform_factory(model=FileSubClass,
fields=('original_filename',
'owner', 'file'))
break
uploadform = FileForm(
{
'original_filename': filename,
'owner': request.user.pk
}, {'file': upload})
if uploadform.is_valid():
file_obj = uploadform.save(commit=False)
# Enforce the FILER_IS_PUBLIC_DEFAULT
file_obj.is_public = filer_settings.FILER_IS_PUBLIC_DEFAULT
# Set the file's folder
current_folder = folder
for segment in path_split:
try:
current_folder = Folder.objects.get(name=segment,
parent=current_folder)
except Folder.DoesNotExist:
# If the current_folder can't have subfolders then
# return a permission error
if current_folder and not current_folder.can_have_subfolders:
error_msg = filer.admin.clipboardadmin.NO_PERMISSIONS_FOR_FOLDER
return JsonResponse({'error': error_msg})
current_folder = Folder.objects.create(
name=segment, parent=current_folder)
else:
# If the folder already exists, check the user is
# allowed to upload here
if not current_folder.has_add_children_permission(request):
error_msg = filer.admin.clipboardadmin.NO_PERMISSIONS_FOR_FOLDER
return JsonResponse({'error': error_msg})
file_obj.folder = current_folder
file_obj.mime_type = mime_type
same_name_file_qs = get_files_distinct_grouper_queryset().annotate(
_name=NullIfEmptyStr('name'),
_original_filename=NullIfEmptyStr('original_filename'),
).annotate(
# seperate annotate is needed to get it work on python<36
# see PEP 468 for more details
_label=Coalesce('_name', '_original_filename',
Value('unnamed file')), ).filter(
folder=folder, _label=file_obj.label)
existing_file_obj = same_name_file_qs.first()
if existing_file_obj:
file_grouper = existing_file_obj.grouper
new_file_grouper = False
existing_file_version = Version.objects.get_for_content(
existing_file_obj)
if existing_file_version.state == DRAFT and not all([
existing_file_version.can_be_archived(),
existing_file_version.check_archive.as_bool(
request.user),
]):
return JsonResponse({
'error':
('Cannot archive existing {} file version'.format(
existing_file_obj))
})
else:
new_file_grouper = True
file_grouper = FileGrouper.objects.create()
file_obj.grouper = file_grouper
file_obj.save()
create_file_version(file_obj, request.user)
# Try to generate thumbnails.
if not file_obj.icons:
# There is no point to continue, as we can't generate
# thumbnails for this file. Usual reasons: bad format or
# filename.
file_obj.delete()
if new_file_grouper:
file_grouper.delete()
# This would be logged in BaseImage._generate_thumbnails()
# if FILER_ENABLE_LOGGING is on.
return JsonResponse(
{'error': 'failed to generate icons for file'},
status=500,
)
thumbnail = None
# Backwards compatibility: try to get specific icon size (32px)
# first. Then try medium icon size (they are already sorted),
# fallback to the first (smallest) configured icon.
for size in (['32'] +
filer_settings.FILER_ADMIN_ICON_SIZES[1::-1]):
try:
thumbnail = file_obj.icons[size]
break
except KeyError:
continue
data = {
'thumbnail': thumbnail,
'alt_text': '',
'label': str(file_obj),
'file_id': file_obj.pk,
'grouper_id': file_grouper.pk,
}
# prepare preview thumbnail
if type(file_obj) == Image:
thumbnail_180_options = {
'size': (180, 180),
'crop': True,
'upscale': True,
}
thumbnail_180 = file_obj.file.get_thumbnail(
thumbnail_180_options)
data['thumbnail_180'] = thumbnail_180.url
data['original_image'] = file_obj.url
return JsonResponse(data)
else:
form_errors = '; '.join([
'%s: %s' % (field, ', '.join(errors))
for field, errors in list(uploadform.errors.items())
])
raise UploadException("AJAX request not valid: form invalid '%s'" %
(form_errors, ))
except UploadException as e:
# TODO: Test
return JsonResponse({'error': str(e)}, status=500)
class CheckInFilterForm(FilterForm):
orders = {
'code': ('order__code', 'item__name'),
'-code': ('-order__code', '-item__name'),
'email': ('order__email', 'item__name'),
'-email': ('-order__email', '-item__name'),
'status': (FixedOrderBy(F('last_checked_in'),
nulls_first=True,
descending=True), 'order__code'),
'-status': (FixedOrderBy(F('last_checked_in'),
nulls_last=True), '-order__code'),
'timestamp': (FixedOrderBy(F('last_checked_in'),
nulls_first=True), 'order__code'),
'-timestamp': (FixedOrderBy(F('last_checked_in'),
nulls_last=True,
descending=True), '-order__code'),
'item': ('item__name', 'variation__value', 'order__code'),
'-item': ('-item__name', '-variation__value', '-order__code'),
'name': {
'_order':
F('display_name').asc(nulls_first=True),
'display_name':
Coalesce('attendee_name_cached', 'addon_to__attendee_name_cached')
},
'-name': {
'_order':
F('display_name').desc(nulls_last=True),
'display_name':
Coalesce('attendee_name_cached', 'addon_to__attendee_name_cached')
},
}
user = forms.CharField(
label=_('Search attendee…'),
widget=forms.TextInput(attrs={
'placeholder': _('Search attendee…'),
'autofocus': 'autofocus'
}),
required=False)
status = forms.ChoiceField(
label=_('Check-in status'),
choices=(
('', _('All attendees')),
('1', _('Checked in')),
('0', _('Not checked in')),
),
required=False,
)
item = forms.ModelChoiceField(label=_('Products'),
queryset=Item.objects.none(),
required=False,
empty_label=_('All products'))
def __init__(self, *args, **kwargs):
self.event = kwargs.pop('event')
self.list = kwargs.pop('list')
super().__init__(*args, **kwargs)
if self.list.all_products:
self.fields['item'].queryset = self.event.items.all()
else:
self.fields['item'].queryset = self.list.limit_products.all()
def filter_qs(self, qs):
fdata = self.cleaned_data
if fdata.get('user'):
u = fdata.get('user')
qs = qs.filter(
Q(order__code__istartswith=u)
| Q(secret__istartswith=u)
| Q(order__email__icontains=u)
| Q(attendee_name_cached__icontains=u)
| Q(attendee_email__icontains=u)
| Q(voucher__code__istartswith=u)
| Q(order__invoice_address__name_cached__icontains=u)
| Q(order__invoice_address__company__icontains=u))
if fdata.get('status'):
s = fdata.get('status')
if s == '1':
qs = qs.filter(last_checked_in__isnull=False)
elif s == '0':
qs = qs.filter(last_checked_in__isnull=True)
if fdata.get('ordering'):
ob = self.orders[fdata.get('ordering')]
if isinstance(ob, dict):
ob = dict(ob)
o = ob.pop('_order')
qs = qs.annotate(**ob).order_by(o)
elif isinstance(ob, (list, tuple)):
qs = qs.order_by(*ob)
else:
qs = qs.order_by(ob)
if fdata.get('item'):
qs = qs.filter(item=fdata.get('item'))
return qs
class OrderPositionViewSet(mixins.DestroyModelMixin,
viewsets.ReadOnlyModelViewSet):
serializer_class = OrderPositionSerializer
queryset = OrderPosition.objects.none()
filter_backends = (DjangoFilterBackend, OrderingFilter)
ordering = ('order__datetime', 'positionid')
ordering_fields = (
'order__code',
'order__datetime',
'positionid',
'attendee_name',
'order__status',
)
filterset_class = OrderPositionFilter
permission = 'can_view_orders'
write_permission = 'can_change_orders'
ordering_custom = {
'attendee_name': {
'_order':
F('display_name').asc(nulls_first=True),
'display_name':
Coalesce('attendee_name_cached', 'addon_to__attendee_name_cached')
},
'-attendee_name': {
'_order':
F('display_name').asc(nulls_last=True),
'display_name':
Coalesce('attendee_name_cached', 'addon_to__attendee_name_cached')
},
}
def get_queryset(self):
qs = OrderPosition.objects.filter(order__event=self.request.event)
if self.request.query_params.get('pdf_data', 'false') == 'true':
qs = qs.prefetch_related(
'checkins', 'answers', 'answers__options', 'answers__question',
Prefetch(
'addons',
OrderPosition.objects.select_related('item', 'variation')),
Prefetch(
'order',
Order.objects.select_related(
'invoice_address').prefetch_related(
Prefetch(
'event',
Event.objects.select_related('organizer')),
Prefetch(
'positions',
OrderPosition.objects.prefetch_related(
'checkins',
'item',
'variation',
'answers',
'answers__options',
'answers__question',
))))).select_related('item', 'variation',
'item__category',
'addon_to')
else:
qs = qs.prefetch_related('checkins', 'answers', 'answers__options',
'answers__question').select_related(
'item', 'order', 'order__event',
'order__event__organizer')
return qs
def _get_output_provider(self, identifier):
responses = register_ticket_outputs.send(self.request.event)
for receiver, response in responses:
prov = response(self.request.event)
if prov.identifier == identifier:
return prov
raise NotFound('Unknown output provider.')
@action(detail=True,
url_name='download',
url_path='download/(?P<output>[^/]+)')
def download(self, request, output, **kwargs):
provider = self._get_output_provider(output)
pos = self.get_object()
if pos.order.status != Order.STATUS_PAID:
raise PermissionDenied(
"Downloads are not available for unpaid orders.")
if not pos.generate_ticket:
raise PermissionDenied(
"Downloads are not enabled for this product.")
ct = CachedTicket.objects.filter(order_position=pos,
provider=provider.identifier,
file__isnull=False).last()
if not ct or not ct.file:
generate.apply_async(args=('orderposition', pos.pk,
provider.identifier))
raise RetryException()
else:
resp = FileResponse(ct.file.file, content_type=ct.type)
resp[
'Content-Disposition'] = 'attachment; filename="{}-{}-{}-{}{}"'.format(
self.request.event.slug.upper(), pos.order.code,
pos.positionid, provider.identifier, ct.extension)
return resp
def perform_destroy(self, instance):
try:
ocm = OrderChangeManager(
instance.order,
user=self.request.user
if self.request.user.is_authenticated else None,
auth=self.request.auth,
notify=False)
ocm.cancel(instance)
ocm.commit()
except OrderError as e:
raise ValidationError(str(e))
except Quota.QuotaExceededException as e:
raise ValidationError(str(e))
def annotate_quantities(self):
return self.annotate(
quantity=Coalesce(Sum("stocks__quantity"), 0),
quantity_allocated=Coalesce(
Sum("stocks__allocations__quantity_allocated"), 0),
)
def homepage_dashboard_view(request):
findings = Finding.objects.all().only("status", "severity")
assets = Asset.objects.all()
global_stats = {
"assets": {
"total": assets.count(),
"total_ag": AssetGroup.objects.all().count(),
},
"asset_types": {},
"findings": {},
"scans": {
"defined":
ScanDefinition.objects.all().count(),
"performed":
Scan.objects.all().count(),
"active_periodic":
ScanDefinition.objects.filter(enabled=True,
scan_type='periodic').count(),
},
"engines": {
"total":
EngineInstance.objects.all().count(),
"policies":
EnginePolicy.objects.all().count(),
"active":
EngineInstance.objects.filter(status='READY',
enabled=True).count(),
},
"rules": {
"total": Rule.objects.all().count(),
"active": Rule.objects.filter(enabled=True).count(),
"nb_matches": 0,
},
}
# asset types
asset_types_stats_params = {}
for at in ASSET_TYPES:
asset_types_stats_params.update({
at[0]:
Coalesce(
Sum(Case(When(type=at[0], then=1)),
output_field=models.IntegerField()), 0)
})
global_stats["asset_types"] = assets.aggregate(**asset_types_stats_params)
# finding counters
findings_stats = findings.aggregate(
nb_new=Coalesce(
Sum(Case(When(status='new', then=1)),
output_field=models.IntegerField()), 0),
nb_critical=Coalesce(
Sum(Case(When(severity='critical', then=1)),
output_field=models.IntegerField()), 0),
nb_high=Coalesce(
Sum(Case(When(severity='high', then=1)),
output_field=models.IntegerField()), 0),
nb_medium=Coalesce(
Sum(Case(When(severity='medium', then=1)),
output_field=models.IntegerField()), 0),
nb_low=Coalesce(
Sum(Case(When(severity='low', then=1)),
output_field=models.IntegerField()), 0),
nb_info=Coalesce(
Sum(Case(When(severity='info', then=1)),
output_field=models.IntegerField()), 0),
)
global_stats["findings"] = {
# "total_raw": RawFinding.objects.count(),
# "total_raw": RawFinding.objects.count(),
"total": findings.count(),
"new": findings_stats["nb_new"],
"critical": findings_stats["nb_critical"],
"high": findings_stats["nb_high"],
"medium": findings_stats["nb_medium"],
"low": findings_stats["nb_low"],
"info": findings_stats["nb_info"],
}
# update nb_matches
matches = 0
for r in Rule.objects.all():
matches += r.nb_matches
global_stats["rules"].update({"nb_matches": matches})
# Last 6 findings
last_findings = Finding.objects.all().order_by('-id')[:6][::-1]
# Last 6 scans
last_scans = Scan.objects.all().order_by('-started_at')[:6]
# Asset grades repartition and TOP 10
asset_grades_map = {
"A": {
"high": 0,
"medium": 0,
"low": 0
},
"B": {
"high": 0,
"medium": 0,
"low": 0
},
"C": {
"high": 0,
"medium": 0,
"low": 0
},
"D": {
"high": 0,
"medium": 0,
"low": 0
},
"E": {
"high": 0,
"medium": 0,
"low": 0
},
"F": {
"high": 0,
"medium": 0,
"low": 0
},
"-": {
"high": 0,
"medium": 0,
"low": 0
}
}
assetgroup_grades_map = copy.deepcopy(asset_grades_map)
# Asset grades
assets_risk_scores = {}
for asset in assets.only("risk_level", "criticity", "id"):
asset_grades_map[asset.risk_level["grade"]].update({
asset.criticity:
asset_grades_map[asset.risk_level["grade"]][asset.criticity] + 1
})
assets_risk_scores.update({asset.id: asset.get_risk_score()})
top_critical_assets_scores = sorted(assets_risk_scores.items(),
key=operator.itemgetter(1))[::-1][:6]
tcas_id_list = [id for id, score in top_critical_assets_scores]
top_critical_assets = list(assets.filter(id__in=tcas_id_list))
top_critical_assets.sort(key=lambda t: tcas_id_list.index(t.id))
# Format to list
asset_grades_map_list = []
for key in sorted(asset_grades_map.keys()):
asset_grades_map_list.append({key: asset_grades_map[key]})
# Asset groups
assetgroups_risk_scores = {}
ags = AssetGroup.objects.all().only("risk_level", "criticity", "id",
"name")
for assetgroup in ags:
assetgroup_grades_map[assetgroup.risk_level["grade"]].update({
assetgroup.criticity:
assetgroup_grades_map[assetgroup.risk_level["grade"]][
assetgroup.criticity] + 1
})
assetgroups_risk_scores.update(
{assetgroup.id: assetgroup.get_risk_score()})
top_critical_assetgroups_scores = sorted(
assetgroups_risk_scores.items(), key=operator.itemgetter(1))[::-1][:6]
tcags_id_list = [id for id, score in top_critical_assetgroups_scores]
top_critical_assetgroups = list(ags.filter(id__in=tcags_id_list))
top_critical_assetgroups.sort(key=lambda t: tcags_id_list.index(t.id))
assetgroup_grades_map_list = []
for key in sorted(assetgroup_grades_map.keys()):
assetgroup_grades_map_list.append({key: assetgroup_grades_map[key]})
# Critical findings
top_critical_findings = []
MAX_CF = 6
for finding in findings.filter(severity="critical").only(
"id", "severity", "title", "asset_name"):
if len(top_critical_findings) <= MAX_CF:
top_critical_findings.append(finding)
if len(top_critical_findings) <= MAX_CF:
for finding in findings.filter(severity="high").only(
"id", "severity", "title", "asset_name"):
if len(top_critical_findings) <= MAX_CF:
top_critical_findings.append(finding)
if len(top_critical_findings) <= MAX_CF:
for finding in findings.filter(severity="medium").only(
"id", "severity", "title", "asset_name"):
if len(top_critical_findings) <= MAX_CF:
top_critical_findings.append(finding)
if len(top_critical_findings) <= MAX_CF:
for finding in findings.filter(severity="low").only(
"id", "severity", "title", "asset_name"):
if len(top_critical_findings) <= MAX_CF:
top_critical_findings.append(finding)
if len(top_critical_findings) <= MAX_CF:
for finding in findings.filter(severity="info").only(
"id", "severity", "title", "asset_name"):
if len(top_critical_findings) <= MAX_CF:
top_critical_findings.append(finding)
# CVSS
cvss_scores = {'lte5': 0, '5to7': 0, 'gte7': 0, 'gte9': 0, 'eq10': 0}
# for finding in findings.only("risk_info"):
# if finding.risk_info["cvss_base_score"] < 5.0: cvss_scores.update({'lte5': cvss_scores['lte5']+1})
# if finding.risk_info["cvss_base_score"] >= 5.0 and finding.risk_info["cvss_base_score"] <= 7.0: cvss_scores.update({'5to7': cvss_scores['5to7']+1})
# if finding.risk_info["cvss_base_score"] >= 7.0: cvss_scores.update({'gte7': cvss_scores['gte7']+1})
# if finding.risk_info["cvss_base_score"] >= 9.0 and finding.risk_info["cvss_base_score"] < 10: cvss_scores.update({'gte9': cvss_scores['gte9']+1})
# if finding.risk_info["cvss_base_score"] == 10.0: cvss_scores.update({'eq10': cvss_scores['eq10']+1})
for finding in findings.prefetch_related(
"risk_info__cvss_base_score").only("risk_info"):
if finding.risk_info["cvss_base_score"] < 5.0:
cvss_scores.update({'lte5': cvss_scores['lte5'] + 1})
if finding.risk_info["cvss_base_score"] >= 5.0 and finding.risk_info[
"cvss_base_score"] <= 7.0:
cvss_scores.update({'5to7': cvss_scores['5to7'] + 1})
if finding.risk_info["cvss_base_score"] >= 7.0:
cvss_scores.update({'gte7': cvss_scores['gte7'] + 1})
if finding.risk_info["cvss_base_score"] >= 9.0 and finding.risk_info[
"cvss_base_score"] < 10:
cvss_scores.update({'gte9': cvss_scores['gte9'] + 1})
if finding.risk_info["cvss_base_score"] == 10.0:
cvss_scores.update({'eq10': cvss_scores['eq10'] + 1})
# CVE & CWE
cxe_stats = {}
cve_list = {}
cwe_list = {}
finding_cves_list = Finding.objects.exclude(
Q(vuln_refs__CVE__isnull=True)
| Q(status__in=['mitigated', 'patched', 'closed', 'false-positive'])
).annotate(cvelist=KeyTextTransform("CVE", 'vuln_refs')).values('cvelist')
finding_cwes_list = Finding.objects.exclude(
Q(vuln_refs__CWE__isnull=True)
| Q(status__in=['mitigated', 'patched', 'closed', 'false-positive'])
).annotate(cwelist=KeyTextTransform("CWE", 'vuln_refs')).values('cwelist')
for finding_cves in finding_cves_list:
if finding_cves['cvelist'] is not None:
for cve in ast.literal_eval(finding_cves['cvelist']):
if cve not in cve_list.keys():
cve_list.update({cve: 1})
else:
cve_list.update({cve: cve_list[cve] + 1})
for cwe_data in finding_cwes_list:
cwe = list(cwe_data.values())[0]
if cwe not in cwe_list.keys():
cwe_list.update({cwe: 1})
else:
cwe_list.update({cwe: cwe_list[cwe] + 1})
cxe_stats.update({
'top_cve':
sorted(cve_list.items(), key=lambda x: x[1], reverse=True)[:10],
'top_cwe':
sorted(cwe_list.items(), key=lambda x: x[1], reverse=True)[:10],
})
return render(
request, 'home-dashboard.html', {
'global_stats': global_stats,
'last_findings': last_findings,
'last_scans': last_scans,
'asset_grades_map': asset_grades_map_list,
'assetgroup_grades_map': assetgroup_grades_map_list,
'top_critical_assets': top_critical_assets,
'top_critical_assetgroups': top_critical_assetgroups,
'top_critical_findings': top_critical_findings,
'cvss_scores': cvss_scores,
'cxe_stats': cxe_stats
})
class DeviceTypeViewSet(CustomFieldModelViewSet):
queryset = DeviceType.objects.prefetch_related(
'manufacturer', 'tags').annotate(
device_count=Coalesce(get_subquery(Device, 'device_type'), 0))
serializer_class = serializers.DeviceTypeSerializer
filterset_class = filters.DeviceTypeFilterSet
import requests
from django.db.backends.signals import connection_created
from django.dispatch import receiver
import math, sqlite3
@receiver(connection_created)
def extend_sqlite(connection=None, **kwargs):
if connection.vendor == 'sqlite':
sqlite3.enable_callback_tracebacks(True)
connection.connection.create_function("sqrt", 1, math.sqrt)
quotes = Quote.objects.annotate(score=Coalesce(Sum('vote__value'), 0),
votes=Count('vote')).filter(approved=True)
def api(request):
query = request.GET.get('q', '')
tag = request.GET.get('tag', '')
quote_list = quotes.annotate(
upvotes=Count('vote', filter=Q(vote__value=1)),
downvotes=Count(
'vote', filter=Q(vote__value=-1))).filter(content__contains=query)
if tag: quote_list = quote_list.filter(tags__name__in=[tag])
fields = request.GET.get(
'fields',
"id,content,notes,upvotes,downvotes,score,votes,timestamp").split(",")
if set(fields) - {
class DeviceRoleViewSet(ModelViewSet):
queryset = DeviceRole.objects.annotate(
device_count=Coalesce(get_subquery(Device, 'device_role'), 0),
virtualmachine_count=Coalesce(get_subquery(VirtualMachine, 'role'), 0))
serializer_class = serializers.DeviceRoleSerializer
filterset_class = filters.DeviceRoleFilterSet
def balance(self):
return self.transactions.aggregate(s=Coalesce(Sum('amount'), 0))['s']
class VirtualChassisViewSet(ModelViewSet):
queryset = VirtualChassis.objects.prefetch_related('tags').annotate(
member_count=Coalesce(get_subquery(Device, 'virtual_chassis'), 0))
serializer_class = serializers.VirtualChassisSerializer
filterset_class = filters.VirtualChassisFilterSet
def get_max_order(self, request, obj=None):
return self.base_model.objects.aggregate(
max_order=Coalesce(Max(self.default_order_field), 0))['max_order']
class PowerPanelViewSet(ModelViewSet):
queryset = PowerPanel.objects.prefetch_related(
'site', 'rack_group').annotate(powerfeed_count=Coalesce(
get_subquery(PowerFeed, 'power_panel'), 0))
serializer_class = serializers.PowerPanelSerializer
filterset_class = filters.PowerPanelFilterSet
def annotate_with_numbers(qs, event):
"""
Modifies a queryset of checkin lists by annotating it with the number of order positions and
checkins associated with it.
"""
# Import here to prevent circular import
from . import Order, OrderPosition, Item
# This is the mother of all subqueries. Sorry. I try to explain it, at least?
# First, we prepare a subquery that for every check-in that belongs to a paid-order
# position and to the list in question. Then, we check that it also belongs to the
# correct subevent (just to be sure) and aggregate over lists (so, over everything,
# since we filtered by lists).
cqs_paid = Checkin.objects.filter(
position__order__event=event,
position__order__status=Order.STATUS_PAID,
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
cqs_paid_and_pending = Checkin.objects.filter(
position__order__event=event,
position__order__status__in=[
Order.STATUS_PAID, Order.STATUS_PENDING
],
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
# Now for the hard part: getting all order positions that contribute to this list. This
# requires us to use TWO subqueries. The first one, pqs_all, will only be used for check-in
# lists that contain all the products of the event. This is the simpler one, it basically
# looks like the check-in counter above.
pqs_all_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_all_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING]
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Now we need a subquery for the case of checkin lists that are limited to certain
# products. We cannot use OuterRef("limit_products") since that would do a cross-product
# with the products table and we'd get duplicate rows in the output with different annotations
# on them, which isn't useful at all. Therefore, we need to add a second layer of subqueries
# to retrieve all of those items and then check if the item_id is IN this subquery result.
pqs_limited_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_limited_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING],
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Finally, we put all of this together. We force empty subquery aggregates to 0 by using Coalesce()
# and decide which subquery to use for this row. In the end, we compute an integer percentage in case
# we want to display a progress bar.
return qs.annotate(
checkin_count=Coalesce(
Case(When(include_pending=True,
then=Subquery(cqs_paid_and_pending,
output_field=models.IntegerField())),
default=Subquery(cqs_paid,
output_field=models.IntegerField()),
output_field=models.IntegerField()), 0),
position_count=Coalesce(
Case(When(all_products=True,
include_pending=False,
then=Subquery(pqs_all_paid,
output_field=models.IntegerField())),
When(all_products=True,
include_pending=True,
then=Subquery(pqs_all_paid_and_pending,
output_field=models.IntegerField())),
When(all_products=False,
include_pending=False,
then=Subquery(pqs_limited_paid,
output_field=models.IntegerField())),
default=Subquery(pqs_limited_paid_and_pending,
output_field=models.IntegerField()),
output_field=models.IntegerField()),
0)).annotate(percent=Case(When(position_count__gt=0,
then=F('checkin_count') * 100 /
F('position_count')),
default=0,
output_field=models.IntegerField()))
def card_community(request, **kwargs):
profile = request.user.profile
if 'idx' in kwargs:
deck = Deck.objects.filter(profile=profile)
question = deck.get(order_idx=kwargs['idx']
).question #gets the single card in question
else:
question = Question.objects.get(pk=kwargs['pk'])
card_tags = question.tags.all()
# Cache all public answers for current question, and all votes for those answers
answers = Answer.objects.filter(
public=True, question=question).prefetch_related('question')
votes = Vote.objects.filter(
answer__question=question).prefetch_related('answer')
## get all the users votes
user_votes = votes.filter(profile=profile)
## append them to the relevant answer
user_votes = votes.filter(profile=profile,
answer_id=OuterRef('pk')).values('vote')
answers = answers.annotate(user_vote=Subquery(user_votes))
## Count up all the votes and sort by the top Answers
## Step 1: Count up the yes' and the no's
# 1. a) Subqueries: Count up number of votes, filtered by answer and vote type. Saves it to 'yes' or 'no', and returns it.
count_yes = votes.filter(
answer__id=OuterRef('pk'),
vote=1).values('vote').annotate(yes=Count('vote')).values('yes')
count_no = votes.filter(
answer__id=OuterRef('pk'),
vote=-1).values('vote').annotate(no=Count('vote')).values('no')
# 1. b) Main outer queries: Runs subquery on all answers for current question, saving returned vote counts to each answer.
# Note: The only thing Coalesce is doing is replacing any 'none' responses with '0'
answers = answers.annotate(yes_count=Coalesce(
Subquery(count_yes, output_field=IntegerField()), 0))
answers = answers.annotate(
no_count=Coalesce(Subquery(count_no, output_field=IntegerField()), 0))
# answers[0].yes_count
# answers[0].no_count
## Step 2: Subtract no's from yes' and save as the total. (Coalesce setting 0's instead of Nones makes this work)
annotated = answers.annotate(total_vote=ExpressionWrapper(
F('yes_count') - F('no_count'), output_field=IntegerField()))
# annotated[0].total_vote
## Step 3: Sort by the new total_vote field, highest count first.
annotated_sorted = annotated.order_by(
F('total_vote').desc(nulls_last=True))
## Notes on aggregate Sum vs annotate Count:
# Aggregate works for individual answers, but you can't put .aggregate() inside a subquery. It evaluates right away.
# sum_votes = Vote.objects.filter(answer__id=1).aggregate(yes=Sum('vote'))
# So instead we need to count up all the Yes votes and all the No votes, and then subtract them
values = {
'question': question,
'card_tags': card_tags,
'answers': annotated_sorted
}
values['community'] = Answer.objects.filter(question=question,
public=True).exists()
with suppress(ObjectDoesNotExist):
print('no valuse')
values['current_answer'] = Answer.objects.get(author=profile,
question=question)
print('curent answer value', values['current_answer'])
with suppress(ObjectDoesNotExist):
values['pain'] = Pain.objects.filter(
profile=profile, question=question).latest(
'time_stamp') #.order_by('-time_stamp')[0:1].get()
return render(request, 'flashr/card_community.html', values)
def get_participant_stats_csv_data(self, exercise_ids):
exercise_qs = exercise_models.Exercise.objects.filter(
id__in=exercise_ids)
email_type_count_map = self.get_emails_count_map(exercise_qs)
exercise_participants_qs = (Participant.objects.filter(
exercise_id=OuterRef("pk")).values("exercise_id").annotate(
participant_count=Count("exercise_id")))
participant_actions_qs = (ParticipantAction.objects.filter(
participant__exercise_id=OuterRef("pk")
).values("participant__exercise_id").annotate(
dcount=Count("participant__exercise_id"),
emails_reported=self.get_action_count("email_reported"),
emails_deleted=self.get_action_count("email_deleted"),
emails_linked_click=self.get_action_count("email_link_clicked"),
clicked_training_link=Case(
When(
participant__exercise__debrief=True,
then=(self.get_action_count("training_link_clicked")),
)),
email_opened_attachment=self.get_action_count(
"email_attachment_download"),
webpage_clicked=self.get_action_count("webpage_click"),
emails_replied=self.get_action_count("email_replied"),
code_skipped=self.get_action_count(
"training_release_code_skipped"),
code_correct=self.get_action_count(
"training_release_code_correct"),
code_incorrect=self.get_action_count(
"training_release_code_incorrect"),
))
csv_columns = [
"title",
"trial_version",
"emails_opened",
"phishing_emails_opened",
"pos_reported",
"pos_deleted",
"neg_clicked_link",
"neg_entered_detail",
"neg_replied_to_phishing_email",
"neg_opened_attachment",
"code_entered",
"code_skipped",
"code_correct",
"code_incorrect",
"participant_count",
"training_link_clicked",
]
rows_qs = exercise_qs.annotate(
pos_reported=Coalesce(
self.get_subquery_value(participant_actions_qs,
"emails_reported"), 0),
pos_deleted=Coalesce(
self.get_subquery_value(participant_actions_qs,
"emails_deleted"), 0),
neg_clicked_link=Coalesce(
self.get_subquery_value(participant_actions_qs,
"emails_linked_click"),
0,
),
neg_entered_detail=Coalesce(
self.get_subquery_value(participant_actions_qs,
"webpage_clicked"), 0),
neg_replied_to_phishing_email=Coalesce(
self.get_subquery_value(participant_actions_qs,
"emails_replied"), 0),
neg_opened_attachment=Coalesce(
self.get_subquery_value(participant_actions_qs,
"email_opened_attachment"),
0,
),
code_skipped=Coalesce(
self.get_subquery_value(participant_actions_qs,
"code_skipped"), 0),
code_correct=Coalesce(
self.get_subquery_value(participant_actions_qs,
"code_correct"), 0),
code_incorrect=Coalesce(
self.get_subquery_value(participant_actions_qs,
"code_incorrect"), 0),
code_entered=Coalesce(F("code_correct") + F("code_incorrect"), 0),
participant_count=self.get_subquery_value(exercise_participants_qs,
"participant_count"),
training_link_clicked=Coalesce(
self.get_subquery_value(participant_actions_qs,
"clicked_training_link"),
Value("N.A."),
),
).values(
"id",
"title",
"trial_version",
"pos_reported",
"pos_deleted",
"neg_clicked_link",
"neg_entered_detail",
"neg_replied_to_phishing_email",
"neg_opened_attachment",
"code_entered",
"code_skipped",
"code_correct",
"code_incorrect",
"participant_count",
"training_link_clicked",
)
csv_rows = list(rows_qs)
for row in csv_rows:
row["emails_opened"] = email_type_count_map[
row["id"]]["regular_emails"]
row["phishing_emails_opened"] = email_type_count_map[
row["id"]]["phishing_emails"]
return csv_columns, csv_rows
def resolve_quantity_allocated(root, *_args):
return root.allocations.aggregate(quantity_allocated=Coalesce(
Sum("quantity_allocated"), 0))["quantity_allocated"]
def dashboard(request, template="organizaciones/dashboard.html"):
if request.method == 'POST':
mensaje = None
form = OrganizacionesForm(request.POST)
if form.is_valid():
request.session['anio'] = form.cleaned_data['anio']
request.session['organizacion'] = form.cleaned_data['organizacion']
request.session['pais'] = form.cleaned_data['pais']
mensaje = "Todas las variables estan correctamente :)"
request.session['activo'] = True
centinela = 1
else:
centinela = 0
else:
form = OrganizacionesForm()
mensaje = "Existen alguno errores"
if 'anio' not in request.session:
if request.GET.get('pais', ''):
filtro = []
id_pais = request.GET.get('pais', '')
a = ResultadosEvaluacion.objects.filter(organizacion__pais=id_pais)
filtro.append(a)
b = ResultadosImplementacion.objects.filter(
organizacion__pais=id_pais)
filtro.append(b)
else:
filtro = _queryset_filtrado(request)
# unificar organizaciones de los dos modelos
list_ev_org = []
for x in filtro[0].values_list('organizacion', flat=True):
list_ev_org.append(x)
list_im_org = []
for x in filtro[1].values_list('organizacion', flat=True):
list_im_org.append(x)
result_list = list(sorted(set(list_ev_org + list_im_org)))
# --------
orgs = Organizacion.objects.filter(id__in=result_list)
total_hombres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('total_hombre'))['total']
total_mujeres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('total_mujer'))['total']
activos_hombres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('activos_hombre'))['total']
activos_mujeres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('activos_mujer'))['total']
jovenes_hombres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('jovenes_hombre'))['total']
jovenes_mujeres = ProductoresProveedores.objects.filter(
organizacion__in=result_list).aggregate(
total=Sum('jovenes_mujer'))['total']
# empleados de la org tiempo completo, preg 22
empleados_org = EmpleadosOrganizacion.objects.filter(
organizacion__in=result_list,
opcion=1).aggregate(total_hombre=Sum('total_hombre'),
total_mujer=Sum('total_mujer'),
adultos_hombre=Sum('adultos_hombre'),
adultos_mujer=Sum('adultos_mujer'),
jovenes_hombre=Sum('jovenes_hombre'),
jovenes_mujer=Sum('jovenes_mujer'))
# servicios y productos
sectores = {}
for obj in CHOICE_SECTOR:
conteo = SectoresProductos.objects.filter(organizacion__in=result_list,
sector=obj[0]).count()
productos = []
for x in Productos.objects.filter(sector__sector=obj[0]):
prod = ProductosOrg.objects.filter(id=x.producto1.id)
productos.append((prod, prod.count()))
sectores[obj[1]] = conteo, productos
# situacion legal y organizativa de org
personeria = Organizacion.objects.filter(id__in=result_list,
personeria=1).count()
en_operaciones = Organizacion.objects.filter(id__in=result_list,
en_operaciones=1).count()
apoyo = Organizacion.objects.filter(id__in=result_list, apoyo=1).count()
#salida grafo carlos resultado implementacion
grafo_barra_cultivo = collections.OrderedDict()
for obj in CHOICES_34_1:
cafe = ProducenComercializan.objects.filter(
cultivo__tipo=1,
opcion=obj[0]).aggregate(a=Coalesce(Sum('cantidad'), V(0)))['a']
cacao = ProducenComercializan.objects.filter(
cultivo__tipo=2,
opcion=obj[0]).aggregate(a=Coalesce(Sum('cantidad'), V(0)))['a']
hortaliza = ProducenComercializan.objects.filter(
cultivo__tipo=3,
opcion=obj[0]).aggregate(a=Coalesce(Sum('cantidad'), V(0)))['a']
# promedio_precio = ProducenComercializan.objects.filter(cultivo=obj
# ).aggregate(c=Coalesce(Avg('precio_promedio'),V(0)))['c']
grafo_barra_cultivo[obj[1]] = [cafe, cacao, hortaliza]
promedio_precio_cafe = ProducenComercializan.objects.filter(
cultivo__tipo=1, ).aggregate(
c=Coalesce(Avg('precio_promedio'), V(0)))['c']
promedio_precio_cacao = ProducenComercializan.objects.filter(
cultivo__tipo=2, ).aggregate(
c=Coalesce(Avg('precio_promedio'), V(0)))['c']
promedio_precio_hortaliza = ProducenComercializan.objects.filter(
cultivo__tipo=3, ).aggregate(
c=Coalesce(Avg('precio_promedio'), V(0)))['c']
lista_precio_promedio = [
promedio_precio_cafe, promedio_precio_cacao, promedio_precio_hortaliza
]
return render(request, template, locals())
def get_unread_message_count(person_pk):
unread_comment_count_subquery = Coalesce(
Subquery(
SupportGroupMessageComment.objects.filter(
deleted=False,
message_id=OuterRef("id"),
created__gt=Greatest(
OuterRef("last_reading_date"),
OuterRef("membership_created"),
OuterRef("created"),
),
)
.exclude(
author_id=person_pk,
)
.values("message_id")
.annotate(count=Count("pk"))
.values("count"),
output_field=IntegerField(),
),
0,
)
# Filter messages where person is not allowed (not author, not in required membership)
messages = SupportGroupMessage.objects.filter(
deleted=False,
supportgroup_id__in=SupportGroup.objects.active()
.filter(memberships__person_id=person_pk)
.values("id"),
)
if not isinstance(person_pk, Person):
person_pk = Person.objects.get(pk=person_pk)
messages_allowed_id = [
msg.id
for msg in messages
if person_pk.role.has_perm("msgs.view_supportgroupmessage", msg)
]
unread_message_count = (
SupportGroupMessage.objects.filter(pk__in=messages_allowed_id)
.annotate(
membership_created=Subquery(
Membership.objects.filter(
supportgroup_id=OuterRef("supportgroup_id"),
person_id=person_pk,
).values("created")[:1]
)
)
.annotate(
last_reading_date=Subquery(
SupportGroupMessageRecipient.objects.filter(
recipient_id=person_pk, message_id=OuterRef("id")
).values("modified")[:1]
)
)
.annotate(unread_comment_count=unread_comment_count_subquery)
.annotate(
unread_count=Case(
# If the message is unread and has been created after the person has joined the group,
# count 1 for the message plus 1 for each of the message's comments
When(
last_reading_date=None,
created__gt=F("membership_created"),
then=F("unread_comment_count") + 1,
),
# If the message has already been read once, count 1 for each comment
# created after the last reading date and after the person has joined
# the group
default=F("unread_comment_count"),
),
)
.values_list("unread_count", flat=True)
)
return sum(unread_message_count)
def _get_quantity_allocated(stocks: StockQuerySet) -> int:
return stocks.aggregate(quantity_allocated=Coalesce(
Sum("allocations__quantity_allocated"), 0))["quantity_allocated"]
def aggregate_pending_suggestions(self):
return dict(
pending_suggestions=Coalesce(Sum("pending_suggestions"), 0))
