前记：主要是数据库存储记录时一个属性是以"2019-01"这样的年月进行存储的，当需要根据A年月到B年月取出相关记录时，filter()直接range()是不行的，不是数值区间或者日期区间，这时需要字符串转日期转字符串，同时字符串"2019-1"要转为"2019-01"
工资汇总--展示
def total_salary(request): # 工资汇总,传入'2019-01 - 2019-02'
form = forms.total_salary(request.GET)
if form.is_valid():
# year_start = form.cleaned_data["yymm_range"].split(" ")[0].split("-")[0]
# month_start = form.cleaned_data["yymm_range"].split(" ")[0].split("-")[1]
# yymm_start = str(year_start) + '-' + str(month_start)
# year_final = form.cleaned_data["yymm_range"].split(" ")[2].split("-")[0]
# month_final = form.cleaned_data["yymm_range"].split(" ")[2].split("-")[1]
# yymm_final = str(year_final) + '-' + str(month_final)
# start = datetime.datetime(int(year_start), int(month_start), 1) # 格式化日期时间,但是give_time不是格式化为datetime的数据,只是类似“2019-01”
# final = datetime.datetime(int(year_final), int(month_final), 1)
start = form.cleaned_data['yymm_range'].split(" ")[0]
final = form.cleaned_data['yymm_range'].split(" ")[2]
start_datetime = datetime.datetime.strptime(start, '%Y-%m')
final_datetime = datetime.datetime.strptime(final, '%Y-%m')
ret_list = libs.get_month_range(start_datetime, final_datetime) # 用datetime化了的起末时间取出中间年月并格式化为字符串的列表
my_list = list() # ['2019-1','2019-2']没有0啊，而数据库存的时候give_time的2019年1月是以'2019-01'形式存储。
for i in ret_list:
if len(i) == 6:
my_list.append(i.split('-')[0] + '-0' + i.split('-')[1])
else:
my_list.append(i)
range_salary = models.Salary.objects.filter(give_time__in=my_list) # 提取出日期范围内的薪水记录
# range_salary = models.Salary.objects.filter(Q(give_time__contains=start)|Q(give_time__contains=final)) # 同上
# 对于give_time，目前初定为2019-01形式，具体到日这里则需要模糊查询
# staff_start_1 = staff_fina_l = sb1 = zfgjj1 = gh1 = kq1 = sd1 = gs1 = yf1 = sf1 = 0 # 声明常量,可以get不到取0
range_salary1 = range_salary.filter(staff__department__in=[1, 4])
staff1 = models.Salary.objects.filter(staff__department__in=[1, 4])
staff_start_1 = staff1.filter(give_time=start).values("staff").distinct().count()
staff_fina_l = staff1.filter(give_time=final).values("staff").distinct().count()
# yf1 = range_salary1.filter(item__type=1).values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# sb1 = range_salary1.filter(item__name=u"社保").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# zfgjj1 = range_salary1.filter(item__name=u"住房公积金").values("give_time").annotate(yfsum=Sum(u'money'))[0][
# 'yfsum']
# kq1 = range_salary1.filter(item__name=u"考勤").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# sd1 = range_salary1.filter(item__name=u"水电").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# gh1 = range_salary1.filter(item__name=u"工会").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# gs1 = range_salary1.filter(item__name=u"税").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
yf1 = range_salary1.filter(item__type=1).aggregate(yf=Sum('money'))['yf']
if not yf1:
yf1 = 0
sb1 = range_salary1.filter(item__name='社保').aggregate(sb=Sum('money'))['sb']
if not sb1:
sb1 = 0
zfgjj1 = range_salary1.filter(item__name="住房公积金").aggregate(zf=Sum('money'))['zf']
if not zfgjj1:
zfgjj1 = 0
kq1 = range_salary1.filter(item__name='考勤').aggregate(yf=Sum('money'))['yf']
if not kq1:
kq1 = 0
sd1 = range_salary1.filter(item__name='水电').aggregate(sd=Sum('money'))['sd']
if not sd1:
sd1 = 0
gh1 = range_salary1.filter(item__name='工会').aggregate(gh=Sum('money'))['gh']
if not gh1:
gh1 = 0
gs1 = range_salary1.filter(item__name='税').aggregate(gs=Sum('money'))['gs']
if not gs1:
gs1 = 0
sf1 = yf1 - sb1 - zfgjj1 - kq1 - sd1 - gh1 - gs1
# staff_start_2 = staff_fina_2 = sb2 = zfgjj2 = gh2 = kq2 = sd2 = gs2 = yf2 = sf2 = 0
range_salary2 = range_salary.filter(staff__department__in=[2, 3])
staff2 = models.Salary.objects.filter(staff__department__in=[2, 3])
# staff_start_2 = staff2.filter(give_time=start).values("staff").distinct().count()
# staff_fina_2 = staff2.filter(give_time=final).values("staff").distinct().count()
# yf2 = range_salary2.filter(item__type=1).values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# sb2 = range_salary2.filter(item__name=u"社保").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# zfgjj2 = range_salary2.filter(item__name=u"住房公积金").values("give_time").annotate(yfsum=Sum(u'money'))[0][
# 'yfsum']
# kq2 = range_salary2.filter(item__name=u"考勤").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# sd2 = range_salary2.filter(item__name=u"水电").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# gh2 = range_salary2.filter(item__name=u"工会").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
# gs2 = range_salary2.filter(item__name=u"税").values("give_time").annotate(yfsum=Sum(u'money'))[0]['yfsum']
staff_start_2 = staff2.filter(give_time=start).values("staff").distinct().count()
if not staff_start_2:
staff_start_2 = 0
staff_fina_2 = staff2.filter(give_time=final).values("staff").distinct().count()
if not staff_fina_2:
staff_fina_2 = 0
yf2 = range_salary2.filter(item__type=1).aggregate(yfsum=Sum('money'))['yfsum']
if not yf2:
yf2 = 0
sb2 = range_salary2.filter(item__name="社保").aggregate(sb=Sum('money'))['sb']
if not sb2:
sb2 = 0
zfgjj2 = range_salary2.filter(item__name=u"住房公积金").aggregate(zf=Sum(u'money'))['zf']
if not zfgjj2:
zfgjj2 = 0
kq2 = range_salary2.filter(item__name="考勤").aggregate(kq=Sum('money'))['kq']
if not kq2:
kq2 = 0
sd2 = range_salary2.filter(item__name="水电").aggregate(sd=Sum('money'))['sd']
if not sd2:
sd2 = 0
gh2 = range_salary2.filter(item__name="工会").aggregate(gh=Sum('money'))['gh']
if not gh2:
gh2 = 0
gs2 = range_salary2.filter(item__name="税").aggregate(gs=Sum('money'))['gs']
if not gs2:
gs2 = 0
sf2 = yf2 - sb2 - zfgjj2 - kq2 - sd2 - gh2 - gs2
# 后勤人员工资
dict_administration = dict()
dict_administration["row"] = 1
dict_administration["项目"] = u"后勤工资"
dict_administration["开始人数"] = staff_start_1
dict_administration["最终人数"] = staff_fina_l
dict_administration["应发金额"] = yf1
dict_administration["代扣社保"] = sb1
dict_administration["代扣住房公积金"] = zfgjj1
dict_administration["工会"] = gh1
dict_administration["考勤"] = kq1
dict_administration["水电"] = sd1
dict_administration["个税"] = gs1
dict_administration["实发金额"] = sf1
# 教师工资
dict_teacher = dict()
dict_teacher["row"] = 2
dict_teacher["项目"] = u'教师工资'
dict_teacher["开始人数"] = staff_start_2
dict_teacher["最终人数"] = staff_fina_2
dict_teacher["应发金额"] = yf2
dict_teacher["代扣社保"] = sb2
dict_teacher["代扣住房公积金"] = zfgjj2
dict_teacher["工会"] = gh2
dict_teacher["考勤"] = kq2
dict_teacher["水电"] = sd2
dict_teacher["个税"] = gs2
dict_teacher["实发金额"] = sf2
# 总工资
dict_all = dict()
dict_all["row"] = 3
dict_all["项目"] = u'总工资'
dict_all["开始人数"] = staff_start_1 + staff_start_2
dict_all["最终人数"] = staff_fina_l + staff_fina_2
dict_all["应发金额"] = yf1 + yf2
dict_all["代扣社保"] = sb1 + sb2
dict_all["代扣住房公积金"] = zfgjj1 + zfgjj2
dict_all["工会"] = gh1 + gh2
dict_all["工会"] = gh1 + gh2
dict_all["考勤"] = kq1 + kq2
dict_all["水电"] = sd1 + sd2
dict_all["个税"] = gs1 + gs2
dict_all["实发金额"] = sf1 + sf2
json_dict = dict()
json_list = list()
json_list.append(dict_administration)
json_list.append(dict_teacher)
json_list.append(dict_all)
json_dict["code"] = "ok"
json_dict["msg"] = "查询汇总成功"
json_dict["data"] = json_list
return HttpResponse(json.dumps(json_dict), content_type='application/json')
else:
# logger.info(u'提交数据错误')
e = ','.join([form.errors[i][0] for i in form.errors]) if len(form.errors) > 0 else u'未知错误'
return HttpResponseBadRequest(json.dumps({'code': 'false', 'msg': e, 'data': []}),
content_type='application/json')
def get_month_range(start_day, end_day): # 取时间段内所有年月，时间模块的各种用法
months = (end_day.year - start_day.year) * 12 + end_day.month - start_day.month
month_range = ['%s-%s' % (start_day.year + mon // 12, mon % 12 + 1)
for mon in range(start_day.month - 1, start_day.month + months)]
return month_range
