def this_year():
return Project.objects.all().aggregate(
Max('proj_years'))['proj_years__max']
# author_obj.book.add(11, 12)
# books = Book.objects.filter(id__gt=10)
# author_obj.book.add(*books)
# 给Author_obj设置新的books,所有ID大于10的书籍，原有的数据会删除.
# books = Book.objects.filter(id__gt=10)
# author_obj.book.set(books)
# 给Author_obj 删除指定书籍.
# book_del = Book.objects.get(id=11)
# author_obj.book.remove(book_del)
# 清空author_obj关联的所有书籍. 注意数据库中表字段是否能为空.
# author_obj.book.clear()
# 聚合函数.
from django.db.models import Avg, Sum, Max, Min, Count
price_avg = Book.objects.all().aggregate(Avg("price"))
price_sum = Book.objects.all().aggregate(Sum("price"))
price_max = Book.objects.all().aggregate(Max("price"))
# price_min指定返回数据的KEY.
price_min = Book.objects.all().aggregate(price_min=Min("price"))
price_all = Book.objects.all().aggregate(price_min=Min("price"),
price_max=Max("price"),
price_avg=Avg("price"))
# 分组. 查询所有书籍关联的作者数量，Count后面直接跟表名.
books_obj = Book.objects.all().annotate(author_num=Count("author"))
for item in books_obj:
print(item.name, item.author_num)
# 所有书籍作者大于1的数据.
books_obj = Book.objects.all().annotate(author_num=Count("author")).filter(
author_num__gt=1)
# F 和 Q 查询.
from django.db.models import F, Q
# F 查询，查询所有修改时间大于创建时间的数据.
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 query_pvi_info_h5(pvi_name, pvi_info=PVIQueryInfo.Energy_Today):
'''
all pv inverter type should implement this function for all PVIQueryInfo
'''
logger.debug('query_pvi_info({pvi_name},{pvi_info})'.format(
pvi_name=pvi_name, pvi_info=pvi_info))
time_since = (datetime.now() + timedelta(minutes=-30)).time()
#time_since = datetime.combine(datetime.now().date(),time.min)
time_until = datetime.now().time()
logger.debug('query time range %s and %s' %
(str(time_since), str(time_until)))
if pvi_info == PVIQueryInfo.Energy_Today:
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Today Wh'][
h5.REGISTER_ADDRESS_COL]).filter(
pvi_name=pvi_name).values('prob_date').annotate(
Max('value')).order_by('prob_date')
total = len(queryset)
if (total > 0):
t_date = queryset[total - 1]['prob_date']
if t_date == datetime.now().date():
value = queryset[total - 1].get('value__max')
logger.debug('return %d' % (value * 10))
return (value * 10)
else:
logger.error('empty query result returned')
return 0
elif pvi_info == PVIQueryInfo.Energy_This_Month:
last_month_end_date = date(datetime.now().year,
datetime.now().month,
1) + timedelta(days=-1)
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Today Wh'][h5.REGISTER_ADDRESS_COL]
).filter(pvi_name=pvi_name).filter(
prob_date__gt=last_month_end_date).values('prob_date').annotate(
Max('value')).order_by('prob_date')
value = 0
if len(queryset) > 0:
for entry in queryset:
value += entry.get('value__max')
logger.debug('return %d' % (value * 10))
return (value * 10)
else:
logger.error('empty query result returned')
return 0
elif pvi_info == PVIQueryInfo.Energy_Until_Now:
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['DC Life Wh'][h5.REGISTER_ADDRESS_COL]
).filter(pvi_name=pvi_name).order_by('-date')
if len(queryset) > 0:
value = queryset[0].value * 10
logger.debug('return %d' % (value))
return (value)
else:
logger.error('empty query result returned')
return 0
elif pvi_info == PVIQueryInfo.Energy_Hourly_List:
return pvi_query_info_energy_hourly_list()
elif pvi_info == PVIQueryInfo.Energy_Daily_List:
return pvi_query_info_energy_daily_list()
elif pvi_info == PVIQueryInfo.AC_Output_Voltage:
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Voltage'][h5.REGISTER_ADDRESS_COL]
).filter(pvi_name=pvi_name).filter(
prob_date__exact=datetime.now().date()).filter(
prob_time__range=[time_since, time_until]).order_by('-date')
if len(queryset) > 0:
value = round(queryset[0].value * 0.1, 1)
logger.debug('return %d' % (value))
return (value)
else:
logger.error('empty query result returned')
elif pvi_info == PVIQueryInfo.AC_Output_Current:
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Current'][h5.REGISTER_ADDRESS_COL]
).filter(pvi_name=pvi_name).filter(
prob_date__exact=datetime.now().date()).filter(
prob_time__range=[time_since, time_until]).order_by('-date')
if len(queryset) > 0:
value = round(queryset[0].value * 0.01, 2)
logger.debug('return %d' % (value))
return (value)
else:
logger.error('empty query result returned')
elif pvi_info == PVIQueryInfo.AC_Output_Wattage:
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Wattage'][h5.REGISTER_ADDRESS_COL]
).filter(pvi_name=pvi_name).filter(
prob_date__exact=datetime.now().date()).filter(
prob_time__range=[time_since, time_until]).order_by('-date')
if len(queryset) > 0:
value = queryset[0].value
logger.debug('return %d' % (value))
return (value)
else:
logger.error('empty query result returned')
else:
logger.error('unknow query pvi_info %s' % pvi_info)
def pvi_query_info_energy_hourly_list():
'''
provide function for query_pvi_info on PVIQueryInfo.Energy_Hourly_List
'''
date_since = (
datetime.now() +
timedelta(hours=-pvi.MAX_QUERY_ENERGY_HOURLY_LIST_LEN)).date()
queryset = RegData.objects.filter(
address=h5.INPUT_REGISTER['Today Wh'][h5.REGISTER_ADDRESS_COL]).filter(
prob_date__gt=date_since).values(
'prob_date', 'prob_hour').annotate(Max('value')).order_by(
'-prob_date', '-prob_hour')
logger.debug('sql cmd: %s' % str(queryset.query))
info = []
logger.debug('queryset count %d' % queryset.count())
max_report_len = pvi.MAX_QUERY_ENERGY_HOURLY_LIST_LEN + 1 # last 48 hours
if queryset.count() < max_report_len:
max_report_len = queryset.count()
for entry in queryset[:max_report_len]:
#logger.debug(entry['prob_date'])
#logger.debug(entry['prob_hour'])
t_hour = entry['prob_hour']
t_time = time(t_hour, 0, 0)
#logger.debug(str(t_time))
info.append([
datetime.combine(entry['prob_date'], t_time), entry['value__max']
])
logger.debug('query return:\n%s' % str(info))
info.sort(key=lambda x: x[0])
if len(info) > 0:
info = [[entry[0], entry[1] * 10] for entry in info]
else:
logger.warning('no energy sample data in database')
this_hour_time = datetime.combine(datetime.now().date(),
time(datetime.now().hour, 0, 0))
for i in range(pvi.MAX_QUERY_ENERGY_HOURLY_LIST_LEN):
info.append([this_hour_time, 0])
this_hour_time -= timedelta(hours=1)
info.reverse()
dataset = info
info = [[dataset[i][0], dataset[i][1] - dataset[i + 1][1]]
for i in range(len(dataset) - 2)
if dataset[i][0].date() == dataset[i + 1][0].date()]
info.reverse()
#-> insert zero energy value for missing hour
dataset = []
if len(info) > 0:
dataset.append(info[0])
t_date = info[0][0]
i = 1
while i < len(info):
t_date = t_date + timedelta(hours=+1)
if t_date < info[i][0]:
dataset.append([t_date, 0])
else:
dataset.append(info[i])
i += 1
dataset.sort(key=lambda x: x[0])
return dataset
def global_var(request):
user = request.user
# ---------------- number items in wishlist
if user.is_authenticated:
number_items_wish_list = WishList.objects.filter(
user__user=user).count()
else:
number_items_wish_list = 0
# ---------------- is supplier
supplier = False
store = None
if user.is_authenticated:
profil = user.profil
if profil.is_professional and profil.is_supplier:
if Shop.objects.filter(owner=profil).exists():
store = get_object_or_404(Shop, owner=profil).id
supplier = True
# ---------------- is seller
seller = False
if user.is_authenticated:
profil = user.profil
if profil.is_seller:
seller = True
# ---------------- number items in compare
if user.is_authenticated:
number_items_compare = Compare.objects.filter(user__user=user).count()
else:
number_items_compare = 0
# --------------- All Categories ---------------
categories = CommerceCategory.objects.all()
# --------------- Hot Categories ---------------
hot_categories = list()
featured_sale = Sale.objects.filter(date_end__gte=datetime.date.today()).values(
'product__cat__category_two__category_one__category') \
.annotate(Max('percentage')).order_by('-percentage__max')[:6]
for el in featured_sale:
cat = CommerceCategory.objects.get(
pk=el["product__cat__category_two__category_one__category"])
hot_categories.append(cat)
el["product__cat__category_two__category_one__category"] = cat
# Complete 4 categories
count = len(hot_categories)
if count < 4:
for i in range(0, CommerceCategory.objects.count()):
category_to_add = CommerceCategory.objects.all()[i]
if category_to_add not in hot_categories:
hot_categories.append(category_to_add)
if len(hot_categories) == 4:
break
hot_categories = hot_categories
# --------------- All Tags ---------------
tags = Tag.objects.all()
# --------------- Cart ---------------
my_cart_result = my_cart(user)
number_products_in_cart = my_cart_result['number_products_in_cart']
total_price_in_cart = my_cart_result['total_price_in_cart']
cart_result = my_cart_result['cart']
context = {
# for base.html
'categories': categories,
'number_items_wish_list': number_items_wish_list,
'number_items_compare': number_items_compare,
'hot_categories': hot_categories[:4],
'featured_sale': featured_sale,
'tags': tags,
'cart': cart_result,
'total_price_in_cart': total_price_in_cart,
'number_products_in_cart': number_products_in_cart,
'is_supplier': supplier,
'is_seller': seller,
'store_id': store
}
return context
def exam_records_ordered(exam):
""" Custome Template Tag takes exam object and returns ordered queryset according to student degrees """
records=ExamRecord.objects.filter(exam=exam).annotate(top_stud=Max('student_degree')).order_by('-top_stud')
return records
def signoff_details(request, locale_code, app_code):
"""Details pane loaded on sign-off on a particular revision.
Requires 'rev' in the query string, supports explicitly passing a 'run'.
"""
try:
# rev query arg is required, it's not a url param for caching, and because it's dynamic
# in the js code, so the {% url %} tag prefers this
rev = request.GET['rev']
except:
raise Http404
try:
# there might be a specified run parameter
runid = int(request.GET['run'])
except:
runid = None
appver = get_object_or_404(AppVersion, code=app_code)
lang = get_object_or_404(Locale, code=locale_code)
forest = appver.tree.l10n
repo = get_object_or_404(Repository, locale=lang, forest=forest)
run = lastrun = None
good = False
try:
cs = repo.changesets.get(revision__startswith=rev)
except Changeset.DoesNotExist:
cs = None
if cs is not None:
runs = Run.objects.order_by('-pk').filter(tree=appver.tree_id,
locale=lang,
revisions=cs)
if runid is not None:
try:
run = runs.get(id=runid)
except Run.DoesNotExist:
pass
try:
lastrun = runs[0]
except IndexError:
pass
good = lastrun and (lastrun.errors == 0) and (lastrun.allmissing == 0)
# check if we have a newer signoff.
push = cs.pushes.get(repository=repo)
sos = appver.signoffs.filter(locale=lang, push__gte=push)
sos = list(sos.annotate(la=Max('action')))
doubled = None
newer = []
if len(sos):
s2a = dict((so.id, so.la) for so in sos)
actions = Action.objects.filter(id__in=s2a.values())
actions = dict(
(a.signoff_id, a.get_flag_display()) for a in actions)
for so in sos:
if so.push_id == push.id:
doubled = True
good = False
else:
flag = actions[so.id]
if flag not in newer:
newer.append(flag)
good = False
newer = sorted(newer)
return render_to_response('shipping/signoff-details.html', {
'run': run,
'good': good,
'doubled': doubled,
'newer': newer,
})
def max_bid(self):
return self.bid_set.all().aggregate(max_bid=Max('value'), buyer=Max('placer'))
def test_populate_line_item_daily_summary_table(self, mock_vacuum):
"""Test that the line item daily summary table populates."""
self.tearDown()
report_table_name = OCP_REPORT_TABLE_MAP['report']
summary_table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary']
report_table = getattr(self.accessor.report_schema, report_table_name)
summary_table = getattr(self.accessor.report_schema,
summary_table_name)
for _ in range(25):
self.creator.create_ocp_usage_line_item(self.reporting_period,
self.report)
report_entry = report_table.objects.all().aggregate(
Min('interval_start'), Max('interval_start'))
start_date = report_entry['interval_start__min']
end_date = report_entry['interval_start__max']
start_date = start_date.replace(hour=0,
minute=0,
second=0,
microsecond=0)
end_date = end_date.replace(hour=0, minute=0, second=0, microsecond=0)
query = self.accessor._get_db_obj_query(summary_table_name)
initial_count = query.count()
self.accessor.populate_line_item_daily_table(start_date, end_date,
self.cluster_id)
self.accessor.populate_line_item_daily_summary_table(
start_date, end_date, self.cluster_id)
self.assertNotEqual(query.count(), initial_count)
summary_entry = summary_table.objects.all().aggregate(
Min('usage_start'), Max('usage_start'))
result_start_date = summary_entry['usage_start__min']
result_end_date = summary_entry['usage_start__max']
self.assertEqual(result_start_date, start_date)
self.assertEqual(result_end_date, end_date)
entry = query.first()
summary_columns = [
'cluster_id',
'namespace',
'node',
'node_capacity_cpu_core_hours',
'node_capacity_cpu_cores',
'node_capacity_memory_gigabyte_hours',
'node_capacity_memory_gigabytes',
'pod',
'pod_labels',
'pod_limit_cpu_core_hours',
'pod_limit_memory_gigabyte_hours',
'pod_request_cpu_core_hours',
'pod_request_memory_gigabyte_hours',
'pod_usage_cpu_core_hours',
'pod_usage_memory_gigabyte_hours',
'usage_end',
'usage_start',
]
for column in summary_columns:
self.assertIsNotNone(getattr(entry, column))
def get_week_top10_users(self):
users = self.session_week.values("user") \
.annotate(total=Count("user")) \
.annotate(last=Max("date_start")).order_by("-total")[:10]
return users
def get_week_top10_assets(self):
assets = self.session_week.values("asset")\
.annotate(total=Count("asset"))\
.annotate(last=Max("date_start")).order_by("-total")[:10]
return assets
