def summary(request, year = None, month = None):
if year is None:
url = '{}/{}/'.format(request.path, datetime.today().year)
return HttpResponseRedirect(url.replace('//','/'))
c = dict(year=year,month=month)
if month is None:
'''
Create an annual report for current year
Pivot requested and approved leaves by month, resource
'''
reqs = LeaveDay.objects.filter(date_of_leave__year=int(year)).annotate(month=TruncMonth('date_of_leave'))
pivot_tbl = pivot(reqs, 'month', 'request__name__username__username', 'hours').order_by('month')
t = loader.get_template('monthly.html')
else:
'''
Create a monthly report for selected month
Pivot requested and approved leaves by day, resource
'''
reqs = LeaveDay.objects.filter(date_of_leave__year=int(year), date_of_leave__month=int(month)).annotate(day=TruncDay('date_of_leave'))
pivot_tbl = pivot(reqs, 'day', 'request__name__username__username', 'hours').order_by('day')
t = loader.get_template('daily.html')
c['prev'] = datetime(int(year),int(month),1)-timedelta(1)
c['next'] = datetime(int(year),int(month),1)+timedelta(31)
c['pivot_tbl'] = pivot_tbl
return HttpResponse(t.render(c))
def test_pivot_on_foreignkey(self):
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales,
'shipped',
'store__region__name',
'units',
default=0)
for row in pt:
shipped = row['shipped']
for name in ['North', 'South', 'East', 'West']:
self.assertEqual(
row[name],
sum(ss.units for ss in shirt_sales
if force_text(ss.shipped) == force_text(shipped)
and ss.store.region.name == name))
pt = pivot(ShirtSales, 'shipped', 'store__name', 'units', default=0)
for row in pt:
shipped = row['shipped']
for name in store_names:
self.assertEqual(
row[name],
sum(ss.units for ss in shirt_sales
if force_text(ss.shipped) == force_text(shipped)
and ss.store.name == name))
def test_pivot_on_choice_field_row_with_multiple_rows(self):
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales.objects.all(), ('gender', 'store'), 'style', 'units')
pt_reverse_rows = pivot(ShirtSales.objects.all(), ('store', 'gender'), 'style', 'units')
for row in chain(pt, pt_reverse_rows):
gender = row['gender']
store = row['store']
self.assertIn('get_gender_display', row)
for style in styles:
self.assertEqual(row[style], sum(ss.units for ss in shirt_sales
if force_text(ss.gender) == force_text(gender)
and ss.style == style
and ss.store_id == store))
def output(request):
indicators = []
indicators_2 = []
date_from = "2019-01-01"
date_to = "2019-04-01"
if request.GET.get('from', ''):
date_from = request.GET.get('from', '')
if request.GET.get('to', ''):
date_to = request.GET.get('to', '')
sort_date = DepartmentOffice.objects.all().filter(
date__range=[date_from, date_to])
piv = pivot(sort_date, 'office__technical_department',
'id_group__indicator', 'value')
for dict in piv:
for k, v in dict.items():
indicators.append(v)
indicators_2.append(indicators)
indicators = []
table = DepartmentOffice.objects.all().order_by('office', 'date')
return render(
request, 'departments/index.html', {
'table': table,
'indicators': [indicators_2],
'from': date_from,
'to': date_to
})
def summary(request, year=None, month=None):
if year == None or month == None:
url = '{}/{}/{:02d}'.format(request.path,
datetime.today().year,
datetime.today().month)
return HttpResponseRedirect(url.replace('//', '/'))
cards = TimeCard.objects.filter(date_of_work__year=year,
date_of_work__month=month)
dt = date(int(year), int(month), 1)
nx = dt + timedelta(31)
pv = dt - timedelta(1)
next = re.sub('/[0-9]{4}/[0-9]{2}', '/{}/{:02d}'.format(nx.year, nx.month),
request.path)
prev = re.sub('/[0-9]{4}/[0-9]{2}', '/{}/{:02d}'.format(pv.year, pv.month),
request.path)
c = dict()
c['resources'] = cards.values('timesheet__resource__full_name', 'timesheet__resource__username__username') \
.annotate(Sum('hours')) \
.order_by('timesheet__resource__full_name')
c['projects'] = cards.values('project__project_name') \
.annotate(Sum('hours')) \
.order_by('project__project_name')
c['daily'] = pivot(cards.order_by('date_of_work'), 'date_of_work',
'project__project_group__name', 'hours')
c['year'] = year
c['month'] = month
c['next'] = next
c['prev'] = prev
c['title'] = 'Resource Utilization Summary -- {}-{}'.format(year, month)
t = loader.get_template("month.html")
return HttpResponse(t.render(c))
def get_education_recommendation(answers):
"""
Returns a list of educations
"""
question_ids = [a['id'] for a in answers]
answer_dict = {str(answer['id']): answer['answer'] for answer in answers}
pivot_tab = pivot(
AnswerConsensus.objects.filter(question_id__in=question_ids),
'education',
'question',
'answer',
aggregation=Min)
data_imp = {
record['education']: sqrt(
sum((answer_dict[str(_id)] - record[str(_id)])**2
for _id in question_ids))
for record in pivot_tab
}
sorted_educations = {
x[0]: x[1]
for x in sorted(data_imp.items(), key=lambda x: x[1])[:10]
}
educations = Education.objects.filter(
id__in=sorted_educations.keys()).all()[:10]
return sorted(educations, key=lambda x: sorted_educations[x.id])
def test_pivot_on_date(self):
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales, 'style', 'shipped', 'units', default=0)
for row in pt:
style = row['style']
for dt in dates:
self.assertEqual(row[dt], sum(ss.units for ss in shirt_sales if ss.style == style and force_text(ss.shipped) == dt))
pt = pivot(ShirtSales.objects, 'shipped', 'style', 'units', default=0)
for row in pt:
shipped = row['shipped']
for style in styles:
self.assertEqual(row[style], sum(ss.units for ss in shirt_sales if force_text(ss.shipped) == force_text(shipped) and ss.style == style))
def test_monthly_report(self):
if settings.BACKEND == 'mysql':
annotations = {
'Month': DateFormat('shipped', format='%m-%Y'),
'date_sort': DateFormat('shipped', format='%Y-%m')
}
elif settings.BACKEND == 'sqlite':
annotations = {
'Month': StrFtime('shipped', format='%m-%Y'),
'date_sort': StrFtime('shipped', format='%Y-%m')
}
else:
return
shirt_sales = ShirtSales.objects.annotate(
**annotations).order_by('date_sort')
monthly_report = pivot(shirt_sales, 'Month', 'store__name', 'units')
# Get the months and assert that the order by that we sent in is respected
months = [record['Month'] for record in monthly_report]
month_strings = [
'12-2004', '01-2005', '02-2005', '03-2005', '04-2005', '05-2005'
]
self.assertEqual(months, month_strings)
# Check that the aggregations are correct too
for record in monthly_report:
month, year = record['Month'].split('-')
for name in store_names:
self.assertEqual(
record[name],
sum(ss.units for ss in shirt_sales
if (int(ss.shipped.year) == int(year)
and int(ss.shipped.month) == int(month)
and ss.store.name == name)))
def timesheet(request, sheetname):
cards = TimeCard.objects.filter(timesheet__name=sheetname)
pivot_tbl = pivot(cards, 'date_of_work','timesheet__resource__username__username','hours')
c = dict({'pivot': pivot_tbl, 'timecards': cards.order_by('date_of_work', 'project__project_name'), \
'title': sheetname, 'year': datetime.today().year, 'month': datetime.today().month})
t = loader.get_template("user.html")
return HttpResponse(t.render(c))
def projecttime(request, projname='', year=None, month=None):
if year == None or month == None:
url = '{}/{}/{:02d}'.format(request.path, datetime.today().year, datetime.today().month)
return HttpResponseRedirect(url.replace('//','/'))
dt = date(int(year), int(month), 1)
nx = dt + timedelta(31)
pv = dt - timedelta(1)
next = re.sub('/[0-9]{4}/[0-9]{2}', '/{}/{:02d}'.format(nx.year, nx.month), request.path)
prev = re.sub('/[0-9]{4}/[0-9]{2}', '/{}/{:02d}'.format(pv.year, pv.month), request.path)
cards = TimeCard.objects.filter(project__project_name__icontains=projname, \
date_of_work__year=year, date_of_work__month=month)
pivot_tbl = pivot(cards, 'date_of_work', 'project__project_name', 'hours').order_by('date_of_work')
cards = cards.order_by('project__project_name', 'date_of_work')
if projname == '':
c = dict({'pivot': pivot_tbl, 'year': year, 'month': month, \
'title': 'All projects ({}-{})'.format(year, month), \
'next': next, 'prev': prev})
else:
c = dict({'timecards': cards, 'year': year, 'month': month, \
'title': '{} ({}-{})'.format(projname, year, month), \
'next': next, 'prev': prev})
t = loader.get_template("project.html")
return HttpResponse(t.render(c))
def runPivot(self, num_students, num_courses):
qs = self.qs.filter(
student_name__in=self.student_names[:num_students])
qs = qs.filter(
course_name__in=self.course_names[:num_courses])
pt = pivot(qs, 'student_name', 'course_name', 'score')
return pt
class AnimalCountViewSet(viewsets.ModelViewSet):
"""
A viewset for viewing and editing user instances.
"""
serializer_class = AnimalCountSerializer
queryset = pivot(AnimalCount.objects.all(), 'animal', 'zoo', 'count')
print(queryset)
def ProcatList(request, mall):
entries = models.Entry.objects.filter(mall=mall)
entry_dates = []
for entry in entries:
entry_dates.append(entry.created_at)
entry_dates.append(entry.updated_at)
latest_update = max(entry_dates)
print(latest_update)
used = []
unique = []
for entry in entries:
if entry.product_category not in used:
unique.append(entry)
used.append(entry.product_category)
pivot_tables = []
unique_list = []
num_list = []
i = 0
shop_address_dict = {}
for product_category in used:
pivot_table = pivot(
models.Entry.objects.filter(mall=mall,
product_category=product_category),
'shop_name', 'product', 'price')
entries = models.Entry.objects.filter(
mall=mall, product_category=product_category)
unique_entries = []
for entry in entries:
if entry.product not in unique_entries:
unique_entries.append(entry.product)
unique_entries.sort()
for entry in entries:
if entry.shop_name not in shop_address_dict and entry.shop_address != None:
shop_address_dict.update({entry.shop_name: entry.shop_address})
pivot_tables.append(pivot_table)
unique_list.append(unique_entries)
num_list.append(i)
i = i + 1
return render(
request, 'basic_app/pro_cat_list.html', {
'used': used,
'unique': unique,
'pivot_tables': pivot_tables,
'unique_list': unique_list,
'num_list': num_list,
'mall': mall,
'shop_address_dict': shop_address_dict,
'latest_update': latest_update
})
def get_queryset(self):
q = self.queryset
if q.exists():
pivot_table = pivot(
q,
['owner__participant__code', 'owner__city', 'decision_type'],
'city__description', 'answer')
return pivot_table
def alter_queryset(self, request):
"""Build actual runtime queryset as the build time queryset provides no information."""
return pivot(
self.queryset,
["device", "device__name"],
"rule__feature__name",
"compliance_int",
aggregation=Max,
)
def s1(request):
list1 = Data2.objects.exclude(role='---').exclude(role='Absence').values('date1','member').annotate(headcnt=Count('id'))
pivot_table = pivot(list1, 'date1', 'member', 'id',aggregation=Count)
for x in pivot_table:
x['total']=x['Member']+x['Guest']
# print(x)
context = {'list1': pivot_table}
return render(request, 'case002/s1.html', context)
def author_genre_counts(request, **context):
authors = Author.objects.annotate(book_count=Count('book')).order_by('-book_count')
authors = pivot(authors, ['first_name', 'last_name'], 'book__genres__name', 'pk', Count, default=0)
genre_names = Genre.objects.all().values_list('name', flat=True)
context.update({
'authors': authors[:50],
'genre_names': genre_names
})
return render(request, 'author_genre_counts.html', context)
def test_pivot(self):
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales.objects.all(), 'style', 'gender', 'units')
for row in pt:
style = row['style']
for gender in genders:
gender_display = 'Boy' if gender == 'B' else 'Girl'
self.assertEqual(row[gender_display], sum(ss.units for ss in shirt_sales if ss.style == style and ss.gender == gender))
def s3(request):
list1 = Data2.objects.filter(role__in = ['Ah-counter','GE','Grammarian','TME','TT Evaluator','TT-master','Timer'])
pivot_table = pivot(list1, 'date1', 'role', 'name',aggregation=Min)
for x in pivot_table:
x['Ah']=x['Ah-counter']
x['TT_Evaluator']=x['TT Evaluator']
x['TT_master']=x['TT-master']
# print(x)
context = {'list1': pivot_table}
return render(request, 'case002/s3.html', context)
def test_pivot_on_choice_field_row(self):
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales.objects.all(), 'gender', 'style', 'units')
for row in pt:
gender = row['gender']
for style in styles:
self.assertEqual(row[style], sum(ss.units for ss in shirt_sales if
force_text(ss.gender) == force_text(gender) and ss.style == style))
def test_pivot_with_default_fill(self):
shirt_sales = ShirtSales.objects.filter(shipped__gt='2005-01-25', shipped__lt='2005-02-03')
row_range = [date(2005, 1, 25) + timedelta(days=n) for n in range(14)]
pt = pivot(shirt_sales, 'shipped', 'style', 'units', default=0, row_range=row_range)
for row in pt:
shipped = row['shipped']
for style in styles:
self.assertEqual(row[style], sum(ss.units for ss in shirt_sales if force_text(ss.shipped) == force_text(shipped) and ss.style == style))
def home(request):
# COUNT MAIL
emails = Mail.external_objects.all()
email_count = emails.count()
suspicious = (emails.filter(
Q(tags__name__contains="suspicious")
| Q(urls__tags__name__contains="suspicious")
| Q(ips__tags__name__contains="suspicious")
| Q(urls__domain__tags__name__contains="suspicious")).distinct().count(
))
malicious = (emails.filter(
Q(tags__name__contains="malicious")
| Q(urls__tags__name__contains="malicious")
| Q(ips__tags__name__contains="malicious")
| Q(urls__domain__tags__name__contains="malicious")).distinct().count(
))
qs = (Mail.external_objects.filter(
submission_date__gte=timezone.now() - timedelta(days=10)).annotate(
thour=TruncHour("submission_date")).order_by())
record_by_time = pivot(
qs,
"thour",
"official_response",
"pk",
aggregation=Count,
display_transform=lambda x: x.lower().replace(" ", "_"),
).order_by("thour")
# PAGINATE LATEST EMAIL
table = LatestMailTable(
Mail.external_objects.prefetch_related(
"addresses",
"ips",
"urls",
"attachments",
"tags",
"addresses__tags",
"ips__tags",
"urls__tags",
"attachments__tags",
).order_by("-submission_date")[:250], )
table.paginate(page=request.GET.get("page", 1), per_page=25)
return render(
request,
"pages/main.html",
{
"table": table,
"email_count": email_count,
"suspicious": suspicious,
"malicious": malicious,
"groups": record_by_time,
},
)
def resource(request, resource=''):
start = datetime.today() - timedelta(182)
end = start + timedelta(365)
leavedays = LeaveDay.objects.filter(request__name__username__username__startswith=resource, \
date_of_leave__gte=start, date_of_leave__lt=end) \
.annotate(Month=TruncMonth('date_of_leave'))
pivot_tbl = pivot(leavedays, 'Month', 'request__leave_type', 'hours').order_by('Month')
c = dict({'title': 'Report for ' + resource,'leavedays': leavedays, \
'resource':resource,'start':start,'end':end,'pivot_tbl': pivot_tbl})
t = loader.get_template("resource.html")
return HttpResponse(t.render(c))
def author_with_rating_counts_pivot(request, **context):
authors = pivot(Author.objects.order_by('-five_star_ratings', '-four_star_ratings', '-three_star_ratings'),
['pk', 'first_name', 'last_name'],
'book__userrating__rating',
'pk', Count,
default=0,
choices=((x, str(x)) for x in range(1, 6)),
display_transform=star_number_to_label)
context.update({
'authors': authors[:50]
})
return render(request, 'author_rating_counts.html', context)
def test_pivot_display_transform(self):
def display_transform(string):
return 'prefix_' + string
shirt_sales = ShirtSales.objects.all()
pt = pivot(ShirtSales.objects.all(), 'style', 'gender', 'units', display_transform=display_transform)
for row in pt:
style = row['style']
for gender in genders:
gender_display = display_transform('Boy' if gender == 'B' else 'Girl')
self.assertEqual(row[gender_display], sum(ss.units for ss in shirt_sales if ss.style == style and ss.gender == gender))
def pivot_data(self, columns, filter_dict={}, year=0, order_list=[]):
if year == 0:
year = get_current_financial_year()
q1 = (self.get_queryset().filter(financial_year=year,
**filter_dict).order_by(*order_list))
pivot_data = pivot(
q1,
columns,
"financial_period__period_short_name",
"amount",
)
return pivot_data
def drugs_sales(self):
qs = Sales.objects.filter(drug__tc_iii__id=self.pk).order_by("drug_id")
data = list(
pivot(
queryset=qs, rows="drug_id", column="year", data="netsales_value"
).order_by("-2020")
)
for drug_sale in data:
drug_sale["drug"] = Drug.objects.get(pk=drug_sale["drug_id"])
drug_sale["company"] = Drug.objects.get(pk=drug_sale["drug_id"]).company
return data
def drugs_sales(self):
qs = self.sales.all().order_by("drug_id")
data = list(
pivot(
queryset=qs, rows="drug_id", column="year", data="netsales_value"
).order_by("-2020")
)
for drug_sale in data:
drug_sale["drug"] = Drug.objects.get(pk=drug_sale["drug_id"])
drug_sale["tc_iii"] = Drug.objects.get(pk=drug_sale["drug_id"]).tc_iii
return data
def get(self, request, *args, **kwargs):
params = dict(request.query_params)
print(params)
pivot_col='brand'
pivot_row='billdate1'
pivot_value='amount'
brand = params['brand'][0]
startDate = params['startDate'][0]
endDate = params['endDate'][0]
brand_list=['CHJ','FION','VENTI','CH']
if params['brand'][0] !='ALL':
brand_list=[]
brand_list.append(params['brand'][0])
if params['col'][0] =='品牌':
pivot_col='brand'
elif params['col'][0] =='大区':
pivot_col='parentname'
if params['row'][0] =='订单日期':
pivot_row='billdate1'
if params['value'][0] =='销售额':
pivot_value='amount'
django_pivot = pivot.pivot(BusinessOrder.objects.filter( Q(billdate1__gte=startDate) & Q(billdate1__lte=endDate) & Q(brand__in=brand_list) ), pivot_col, pivot_row,pivot_value)
elif params['value'][0] =='消费人数':
pivot_value='vipcardno'
django_pivot = pivot.pivot(BusinessOrder.objects.filter( Q(billdate1__gte=startDate) & Q(billdate1__lte=endDate) & Q(brand__in=brand_list) ), pivot_col, pivot_row,pivot_value,aggregation=Count )
return Response(django_pivot)
def b1(request, yr, mo):
key = {
'yr': yr,
'mo': mo,
}
# list1 = Data1.objects.filter(date1__year=yr, date1__month=mo).values('place','date1__day').annotate(idcnt=Count('id'))
pivot1 = pivot(Data1.objects.filter(date1__year=yr, date1__month=mo),
'place',
'date1__day',
'id',
aggregation=Count)
# for x in pivot1:
# print (x)
context = {'key': key, 'list': getPlaceSorted(pivot1)}
return render(request, 'case001/b1.html', context)
