def filter_queryset(self, queryset):
qs = super().filter_queryset(queryset)
qs = (
qs.annotate(month=functions.ExtractMonth("created_at"))
.only("category__kind", "amount", "month")
.values("category__kind", "month")
.annotate(total=Sum("amount"))
.order_by("month", "category__kind")
)
return qs
def get_month_details(self, year, month, tran_type=None):
if tran_type == 'spend':
return self.values('tran_dt', 'tran_desc', 'category__name') \
.exclude(category__name__in=['Payment/Credit']) \
.annotate(year=functions.ExtractYear('tran_dt')) \
.annotate(month=functions.ExtractMonth('tran_dt')) \
.annotate(totals=Sum('tran_amt')) \
.filter(year=year, month=month, tran_type=1) \
.values('tran_dt', 'tran_desc', 'category__name', 'totals') \
.order_by('-tran_dt', 'category__name', '-totals')
elif tran_type == 'income':
# print("yes get mon deta", tran_type)
return self.values('tran_dt', 'tran_desc', 'category__name') \
.exclude(category__name__in=['Payment/Credit']) \
.annotate(year=functions.ExtractYear('tran_dt')) \
.annotate(month=functions.ExtractMonth('tran_dt')) \
.annotate(totals=Sum('tran_amt')) \
.filter(year=year, month=month, tran_type=2) \
.values('tran_dt', 'tran_desc', 'category__name', 'totals') \
.order_by('-tran_dt', 'category__name', '-totals')
def get_deposit_trans(self):
return self.filter(tran_type=2) \
.annotate(year=functions.ExtractYear('tran_dt')) \
.annotate(month=functions.ExtractMonth('tran_dt')).values('year', 'month') \
.annotate(totals=Sum('tran_amt')).order_by('-year', '-month', '-totals')
def display_by_month_year(self):
return self.annotate(year=functions.ExtractYear('tran_dt')) \
.exclude(category__name__in=['Payment/Credit', 'Income']) \
.annotate(month=functions.ExtractMonth('tran_dt')).values('year', 'month') \
.annotate(totals=Sum('tran_amt')).order_by('-year', '-month', '-totals')
def get(self,request):
caigouDay7 = request.GET.get('caigouDay7')
caigouYesterday = request.GET.get('caigouYesterday')
caigouDay30 = request.GET.get('caigouDay30')
caigouShishi = request.GET.get('caigouShishi')
caigouWeek = request.GET.get('caigouWeek')
caigouMonth = request.GET.get('caigouMonth')
dayRange = request.GET.get('dayRange')
today_date = datetime.today().date()
one_day_ago = today_date + timedelta(days=-1)
# 根据天的区间查询
if dayRange:
day_range = []
for day in dayRange.split('-'):
day_range.append(datetime.strptime(day.strip(),'%Y/%m/%d').date())
orders = IndexHistory.objects.filter(date__range=(day_range[0],day_range[1])).order_by('-date')
return JsonResponse({'code':'0','data':serializers.serialize('json',orders)})
# 以月度为尺度
if caigouMonth:
# 计算时间
thirteen_month = today_date.month-1
last_year = today_date.year-1
if today_date.month == 1:
thirteen_month = 12
last_year = today_date.year-2
thirteen_month_ago = date(last_year,thirteen_month,1)
# 获取近13个月份数据
one_year_data = IndexHistory.objects.filter(date__gte = thirteen_month_ago)
# 分组统计每个月的数据
sum_res = one_year_data.annotate(
year=functions.ExtractYear('date'),
month=functions.ExtractMonth('date')
).values('year', 'month').order_by('-year', '-month').annotate(
caigou = Avg('caigou'),
ruku = Avg('ruku'),
paishe = Avg('paishe'),
zhizuo = Avg('zhizuo'),
shangjia = Avg('shangjia'),
shangjia_done = Avg('shangjia_done'),
taotai = Avg('taotai'),
fengcun = Avg('fengcun'),
fengcun_done = Avg('fengcun_done'),
tuishi_done = Avg('tuishi_done'),
caigou_exceed = Avg('caigou_exceed'),
ruku_exceed = Avg('ruku_exceed'),
paishe_exceed = Avg('paishe_exceed'),
zhizuo_exceed = Avg('zhizuo_exceed'),
)
# ceil
for su in sum_res:
for key in su:
su[key] = math.ceil(su[key])
if len(sum_res) != 13:
# 空数据填充前面月份的数据
thirteen = get_13_month_list()
sum_res_list =list(sum_res)
for i in range(len(sum_res),13):
sum_res_list.append({'year': thirteen[i][:4], 'month': thirteen[i][5:7], 'caigou': 0, 'ruku': 0, 'paishe': 0, 'zhizuo': 0, 'shangjia': 0, 'shangjia_done': 0, 'taotai': 0, 'fengcun': 0, 'fengcun_done': 0, 'tuishi_done': 0, 'caigou_exceed': 0, 'ruku_exceed': 0, 'paishe_exceed': 0, 'zhizuo_exceed': 0})
return JsonResponse({'code':'0','data':json.dumps(sum_res_list),'month_week':'month'})
# 以周为尺度
if caigouWeek:
# 0-6 一 - 周日
thirteen_week_day = today_date + timedelta(days=-today_date.weekday()-1-7*13)
# 获取近13周数据
one_year_data = IndexHistory.objects.filter(date__gte = thirteen_week_day)
# 分组统计每周的数据
sum_res = one_year_data.annotate(
# day=functions.ExtractWeekDay('date'),
week=functions.ExtractWeek('date'),
).values('week').order_by( '-week').annotate(
date = Max('date'),
caigou = Avg('caigou'),
ruku = Avg('ruku'),
paishe = Avg('paishe'),
zhizuo = Avg('zhizuo'),
shangjia = Avg('shangjia'),
shangjia_done = Avg('shangjia_done'),
taotai = Avg('taotai'),
fengcun = Avg('fengcun'),
fengcun_done = Avg('fengcun_done'),
tuishi_done = Avg('tuishi_done'),
caigou_exceed = Avg('caigou_exceed'),
ruku_exceed = Avg('ruku_exceed'),
paishe_exceed = Avg('paishe_exceed'),
zhizuo_exceed = Avg('zhizuo_exceed'),
)
# print(sum_res)
# print(len(sum_res))
for su in sum_res:
for key in su:
if isinstance(key,float):
su[key] = math.ceil(su[key])
for res in sum_res:
res['date'] = str(res['date'])[5:]
# if len(sum_res) != 13:
# # 空数据填充前面月份的数据
# thirteen = get_13_month_list()
# sum_res_list =list(sum_res)
# for i in range(len(sum_res),13):
# sum_res_list.append({'year': thirteen[i][:4], 'month': thirteen[i][5:7], 'caigou': 0, 'ruku': 0, 'paishe': 0, 'zhizuo': 0, 'shangjia': 0, 'shangjia_done': 0, 'taotai': 0, 'fengcun': 0, 'fengcun_done': 0, 'tuishi_done': 0, 'caigou_exceed': 0, 'ruku_exceed': 0, 'paishe_exceed': 0, 'zhizuo_exceed': 0})
return JsonResponse({'code':'0','data':json.dumps(list(sum_res)),'month_week':'week'})
# 查询七天前的记录
if caigouDay7:
orders = IndexHistory.objects.order_by('-date')[0:7]
return JsonResponse({'code':'0','data':serializers.serialize('json',orders)})
# 查询昨天的记录
if caigouYesterday:
orders = IndexHistory.objects.order_by('-date')[0:1]
return JsonResponse({'code':'0','data':serializers.serialize('json',orders)})
# 查询30天前的记录
if caigouDay30:
orders = IndexHistory.objects.order_by('-date')[0:30]
return JsonResponse({'code':'0','data':serializers.serialize('json',orders)})
# 实时的记录
if caigouShishi:
conn = get_redis_connection()
index_ = conn.hgetall('index') # b'' 真的烦
index = {}
for key in index_:
index[str(key.decode())] = str(index_[key].decode())
index['date'] = str(datetime.now().date())
res_array = []
res_array.append({'fields':index})
return JsonResponse({'code':'0','data':json.dumps(res_array)})