前记：主要是数据库存储记录时一个属性是以"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