def test_decimal(self):
DecimalModel.objects.create(n1=Decimal('12.9'), n2=Decimal('0.6'))
obj = DecimalModel.objects.annotate(n1_ceil=Ceil('n1'),
n2_ceil=Ceil('n2')).first()
self.assertIsInstance(obj.n1_ceil, Decimal)
self.assertIsInstance(obj.n2_ceil, Decimal)
self.assertEqual(obj.n1_ceil, Decimal(math.ceil(obj.n1)))
self.assertEqual(obj.n2_ceil, Decimal(math.ceil(obj.n2)))
def test_float(self):
FloatModel.objects.create(f1=-12.5, f2=21.33)
obj = FloatModel.objects.annotate(f1_ceil=Ceil('f1'),
f2_ceil=Ceil('f2')).first()
self.assertIsInstance(obj.f1_ceil, float)
self.assertIsInstance(obj.f2_ceil, float)
self.assertEqual(obj.f1_ceil, math.ceil(obj.f1))
self.assertEqual(obj.f2_ceil, math.ceil(obj.f2))
def test_decimal(self):
DecimalModel.objects.create(n1=Decimal("12.9"), n2=Decimal("0.6"))
obj = DecimalModel.objects.annotate(n1_ceil=Ceil("n1"),
n2_ceil=Ceil("n2")).first()
self.assertIsInstance(obj.n1_ceil, Decimal)
self.assertIsInstance(obj.n2_ceil, Decimal)
self.assertEqual(obj.n1_ceil, Decimal(math.ceil(obj.n1)))
self.assertEqual(obj.n2_ceil, Decimal(math.ceil(obj.n2)))
def test_integer(self):
IntegerModel.objects.create(small=-11, normal=0, big=-100)
obj = IntegerModel.objects.annotate(
small_ceil=Ceil('small'),
normal_ceil=Ceil('normal'),
big_ceil=Ceil('big'),
).first()
self.assertIsInstance(obj.small_ceil, int)
self.assertIsInstance(obj.normal_ceil, int)
self.assertIsInstance(obj.big_ceil, int)
self.assertEqual(obj.small_ceil, math.ceil(obj.small))
self.assertEqual(obj.normal_ceil, math.ceil(obj.normal))
self.assertEqual(obj.big_ceil, math.ceil(obj.big))
def with_dynamic_cost(self):
return self.with_solved_count().annotate(current_cost=Greatest(
Ceil((F('min_cost') - F('cost')) /
(F('decay_value') * F('decay_value')) *
(F('solved_count') * F('solved_count')) + F('cost')),
F('min_cost'),
), )
def get_queryset(self):
qs = super(CTRCurrentCostManager, self).get_queryset()
if self.dynamic:
qs = qs.annotate(
solved_count=Coalesce(
Count(
'solved_by',
distinct=True,
),
V(0),
),
current_cost=Greatest(
Ceil((F('min_cost') - F('max_cost')) /
(F('decay_value') * F('decay_value')) *
(F('solved_count') * F('solved_count')) +
F('max_cost')),
F('min_cost'),
),
)
else:
qs = qs.annotate(
solved_count=Coalesce(
Count(
'solved_by',
distinct=True,
),
V(0),
),
current_cost=F('cost'),
)
return qs
def aggregate_on_queryset(queryset):
if queryset.count() > databaseutils.MAX_DATASET_SIZE:
timedelta_in_seconds = WIND_AGGREGATION_INTERVALL_IN_MIN * 60
queryset = queryset.values(
time_intervall=Ceil(UnixTimestamp(F('measure_time')) / timedelta_in_seconds)).annotate(
measure_time=FromUnixtime(Avg(UnixTimestamp(F('measure_time')))),
speed=Avg('speed'),
direction=Avg('direction')) \
.order_by('measure_time')
print(queryset.query)
return queryset
def get_total_price(self):
total_price = self.order_set.annotate(
sum_price=ExpressionWrapper(Ceil(
Sum((F("orderitem__product__price") *
(100 -
F("orderitem__product__product_group__discount_rate")) *
0.01) * F("orderitem__quantity"))),
output_field=models.IntegerField()))
if total_price[0].sum_price is not None:
return total_price[0].sum_price
return 0
def get(self, request, *args, **kwargs):
query_time_per_phase = "SELECT phase.name, COALESCE(CEIL(timelog.delta_time / 60.0), 0) AS total_time FROM logs_phase AS phase LEFT JOIN logs_timelog AS timelog ON (timelog.phase_id = phase.id AND timelog.program_id = {}) ORDER BY phase.order_index".format(self.program.pk)
# Consulta con ORM Todavía no es apta para lo que se busca, se usa mientras (SQL PURO)
'''query_time_per_phase = Phase.objects.annotate(total_time=Coalesce(Sum(Ceil(F('phase_log_time__delta_time') / 60.0), filter=Q(phase_log_time__program=self.program)), 0)).values('name', 'total_time').order_by('created_at')'''
query_time_to_date = Phase.objects.annotate(total_time=Coalesce(Sum(Ceil(F('phase_log_time__delta_time') / 60.0), filter=Q(phase_log_time__program__programmer=self.program.programmer)), 0)).values('name', 'total_time').order_by('order_index')
self.context["time_per_phase"] = self.my_custom_sql(query_time_per_phase)
self.context["time_per_phase_to_date"] = query_time_to_date
return Response(data=self.context, status=status.HTTP_200_OK)
def test_public_filtering_age_range_max(self):
# age range max search
response = self.client.get('/api/public?age_range_max=32')
self.assertEqual(response.status_code, status.HTTP_200_OK)
response_obj = response.json()
db_count = Individual.objects.annotate(age_numeric_ceil=Ceil('age_numeric'))\
.filter(age_numeric_ceil__lt=32)\
.count()
self.assertIn(self.response_threshold_check(response_obj), [db_count, settings.INSUFFICIENT_DATA_AVAILABLE])
if db_count <= self.response_threshold:
self.assertEqual(response_obj, settings.INSUFFICIENT_DATA_AVAILABLE)
else:
self.assertEqual(db_count, response_obj['count'])
def aggregate(self, request):
queryset = self.get_queryset()
if queryset.count() > databaseutils.MAX_DATASET_SIZE:
timedelta_in_seconds = calculate_timedelta(
queryset.aggregate(min=Min('measure_time'))['min'],
queryset.aggregate(max=Max('measure_time'))['max'])
queryset = queryset.values(
time_intervall=Ceil(UnixTimestamp(F('measure_time')) / timedelta_in_seconds)).annotate(
degrees=Avg('degrees'),
measure_time=FromUnixtime(Avg(UnixTimestamp(F('measure_time'))))) \
.order_by('measure_time')
print(queryset.query)
return Response(queryset.values('measure_time', 'degrees'),
status=status.HTTP_200_OK)
def test_null(self):
IntegerModel.objects.create()
obj = IntegerModel.objects.annotate(null_ceil=Ceil('normal')).first()
self.assertIsNone(obj.null_ceil)
def filter_age_range_max(self, qs, name, value):
if "age" in settings.CONFIG_FIELDS:
from django.db.models.functions import Ceil
# annotate qs with age_numeric ceiling value, age < ceiling value
qs = qs.annotate(age_numeric_ceil=Ceil('age_numeric')).filter(age_numeric_ceil__lt=value)
return qs
def get(self, request, *args, **kwargs):
# -----> ACTUAL SIZE <-------
base = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('lines_current_base'))
added = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('lines_current_added'))
deleted = BasePart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(
total=Sum('lines_current_deleted'))
reused = ReusedPart.objects.values('program__pk').filter(
program=OuterRef("pk")).annotate(total=Sum('current_lines'))
self.data["actual_size"] = Program.objects.values('pk', 'name').filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
Subquery(base.values('total')) -
Subquery(deleted.values('total')) +
Subquery(reused.values('total')) +
Subquery(added.values('total')), 0))
# -----> END ACTUAL SIZE <------
# -----> Defects Removed <--------
self.data["defects_removed"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
planning=Count('name',
Q(get_defects__phase_removed__name='Planning')),
design=Count('name',
Q(get_defects__phase_removed__name='Design')),
design_review=Count(
'name',
Q(get_defects__phase_removed__name='Design Review')),
codification=Count(
'name',
Q(get_defects__phase_removed__name='Codification')),
codification_review=Count(
'name',
Q(get_defects__phase_removed__name='Codification Review')),
compilation=Count(
'name', Q(get_defects__phase_removed__name='Compilation')),
unit_test=Count(
'name', Q(get_defects__phase_removed__name='Unit Test')),
postmortem=Count(
'name',
Q(get_defects__phase_removed__name='Postmortem'))).values(
'pk', 'name', 'planning', 'design', 'design_review',
'codification', 'codification_review', 'compilation',
'unit_test', 'postmortem').order_by('created_at')
# -----> END Defects removed <------
# -----> Defects Injected <-------
self.data["defects_injected"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False
).annotate(
planning=Count('name',
Q(get_defects__phase_injected__name='Planning')),
design=Count('name',
Q(get_defects__phase_injected__name='Design')),
design_review=Count(
'name', Q(get_defects__phase_injected__name='Design Review')),
codification=Count(
'name', Q(get_defects__phase_injected__name='Codification')),
codification_review=Count(
'name',
Q(get_defects__phase_injected__name='Codification Review')),
compilation=Count(
'name', Q(get_defects__phase_injected__name='Compilation')),
unit_test=Count('name',
Q(get_defects__phase_injected__name='Unit Test')),
postmortem=Count(
'name',
Q(get_defects__phase_injected__name='Postmortem'))).values(
'pk', 'name', 'planning', 'design', 'design_review',
'codification', 'codification_review', 'compilation',
'unit_test', 'postmortem').order_by('created_at')
# ------> END Defects injected <------
# -----> Total time <--------
self.data["total_time"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
total=TIME_TOTAL_BY_PROGRAM).values(
'pk', 'name', 'total').order_by('created_at')
# -----> END total time <------
# -----> Failure COQ <--------
# Hago dos subconsultas que retornan el tiempo dedicado en la fase de compilación y de pruebas
timelogComp = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Compilation').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
timelogUnit = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Unit Test').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
self.data["failure_COQ"] = Program.objects.filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
100 * ((Coalesce(Subquery(timelogComp), 0) +
Coalesce(Subquery(timelogUnit), 0)) /
NullIf(TIME_TOTAL_BY_PROGRAM, 0)), 0)).values(
'pk', 'name', 'total').order_by('created_at')
# ------> END Failure COQ <------
# ------> Appraisal Cost Of Quality <---------
# Hago dos subconsultas que retornan el tiempo dedicado en la fase de revisión de diseño y revisión de código
timelog_design_review = TimeLog.objects.filter(
program__pk=OuterRef("pk"), phase__name='Design Review').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
timelog_code_review = TimeLog.objects.filter(
program__pk=OuterRef("pk"),
phase__name='Codification Review').annotate(
time=Ceil(F('delta_time') / 60.0)).values('time')[:1]
self.data["appraisal_COQ"] = Program.objects.filter(
programmer=self.user,
finish_date__isnull=False).annotate(total=Coalesce(
100 * ((Coalesce(Subquery(timelog_design_review), 0) +
Coalesce(Subquery(timelog_code_review), 0)) /
NullIf(TIME_TOTAL_BY_PROGRAM, 0)), 0)).values(
'pk', 'name', 'total').order_by('created_at')
# -------> END Total defects <-------
# ------> Total defects <---------
self.data["total_defects"] = Program.objects.filter(
programmer=self.user, finish_date__isnull=False).annotate(
total=Count('get_defects')).values('pk', 'name', 'total')
# ------> END total defects <-------
return Response(data=self.data, status=status.HTTP_200_OK)
# Django
from django.db.models import Sum, F
from django.db.models.functions import Coalesce, Ceil
# ANALYSIS TOOLS
ANALYSIS_TOOLS = [
"actual size", "defects removed", "defects injected", "total time",
"failure cost of quality", "appraisal cost of quality", "total defects"
]
# TODO Graficas pendiendtes:
'''
- Size estimation error.
- Actual development time.
- Time estimation error.
- Percent compile time.
- Percent test time.
- Percent compile + test time.
- yield vs A/FR", "A/FR vs yield.
- Defect injection % by phase.
- Defect removal % by phase.
- Total cost of quality.
- Appraisal to failure ratio
'''
TIME_TOTAL_BY_PROGRAM = Coalesce(
Sum(Ceil(F('program_log_time__delta_time') / 60.0)), 0)
GENERES = ['masculino', 'femenino', 'indefinido']
def get_stars(self, obj):
stars = Reviews.objects.filter(product=obj).aggregate(rating__avg=Ceil(Avg('rating')))['rating__avg']
if stars is None:
stars = 0
return int(stars)