from __future__ import absolute_import, unicode_literals
from celery import shared_task
from apps.qm.models import QuaStat
from apps.utils.tasks import CustomTask
from django.db.models import Sum, F, ExpressionWrapper, FloatField, Q

# def cal_quastat_type(qs, params_o: dict, type: str):
#     params = params_o.copy()
#     v_list = ['belong_dept', 'material', 'testitem', 'year_s', 'month_s', 'day_s', 'shift']
#     if type == 'month_sf':
#         params.pop('day_s')
#         v_list = ['belong_dept', 'material', 'testitem', 'year_s', 'month_s', 'shift']
#     elif type == 'month_s':
#         params.pop('shift')
#         params.pop('day_s')
#         v_list = ['belong_dept', 'material', 'testitem', 'year_s', 'month_s']
#     elif type == 'year_s':
#         params.pop('shift')
#         params.pop('month_s')
#         params.pop('day_s')
#         v_list = ['belong_dept', 'material', 'testitem', 'year_s']
#     qs_ = qs.filter(**params).values(*v_list)
#     results = qs_.annotate(
#                                         avg_val_total=Sum(F('val_avg')*F('num_test')),
#                                         num_test_1=Sum('num_test'),
#                                         num_ok_1=Sum('num_ok')).annotate(
#                                             avg_val_1=ExpressionWrapper(F('avg_val_total')/F('num_test_1'), output_field=FloatField())
#                                             # ,rate_pass_1=ExpressionWrapper(F('num_ok_1')/F('num_test_1'), output_field=FloatField())  不知道为什么算出来是0
#                                         )
#     query = """
#         SELECT SUM(num_test * val_avg)/SUM(num_test) AS avg_val_1, SUM(num_test) AS num_test_1, SUM(num_ok) AS num_ok_1, SUM(num_ok)/SUM(num_test) as rate_pass_1
#         FROM qm_quastat
#         where year_s = 2023 and month_s = 7 and day_s = 7 and type = 'sflog'
#         GROUP BY belong_dept_id, material_id, testitem_id
#     """
#     params['type'] = type
#     for r1 in results:
#         stat_params = {
#             'val_avg': r1['avg_val_1'],
#             'num_test': r1['num_test_1'],
#             'num_ok': r1['num_ok_1'],
#             'rate_pass': r1['num_ok_1']/r1['num_test_1']
#         }
#         qua, is_created = QuaStat.objects.get_or_create(
#             **params, defaults={**params, **stat_params}
#         )
#         for k in stat_params:
#             setattr(qua, k, stat_params[k])
#         qua.save()

# @shared_task(base=CustomTask)
# def cal_quastat(quastatId: str):
#     ins = QuaStat.objects.get(id=quastatId)
#     qs = QuaStat.objects.filter(type='sflog').exclude(Q(val_avg__isnull=True)|Q(num_test__isnull=True)|Q(num_ok__isnull=True))
#     params = {
#         'testitem': ins.testitem,
#         'material': ins.material,
#         'shift': ins.shift,
#         'belong_dept': ins.belong_dept,
#         'year_s': ins.year_s,
#         'month_s': ins.month_s,
#         'day_s': ins.day_s
#     }
#     # 日统计
#     cal_quastat_type(qs, params_o=params, type='day_s')
#     # 班月统计
#     cal_quastat_type(qs, params_o=params, type='month_sf')
#     # 月统计
#     cal_quastat_type(qs, params_o=params, type='month_s')
#     # 年统计
#     cal_quastat_type(qs, params_o=params, type='year_s')


@shared_task(base=CustomTask)
def cal_quastat_sflog(sflogId: str):
    from apps.wpm.models import SfLog
    from apps.enm.models import EnStat
    from apps.utils.sql import query_all_dict
    sflog = SfLog.objects.get(id=sflogId)
    mgroup = sflog.mgroup
    team = sflog.team
    qs = QuaStat.objects.exclude(Q(val_avg__isnull=True)|Q(num_test__isnull=True)|Q(num_ok__isnull=True))

    year_s, month_s, day_s = sflog.get_ymd

    # 班统计
    qs1_v = qs.filter(sflog=sflog).annotate(material_name=F('material__name'), testitem_name=F('testitem__name')).values('material',  'material_name', 'testitem', 'testitem_name', 'val_avg', 'num_test', 'num_ok', 'rate_pass')
    enstat, _ = EnStat.objects.get_or_create(type="sflog", sflog=sflog, 
                                                defaults={'type': 'sflog', 'sflog': sflog, 'mgroup': mgroup, 'year_s': year_s, 'month_s': month_s, 'day_s': day_s})
    enstat.qua_data = list(qs1_v)
    enstat.save(update_fields=['qua_data'])

    # 日统计
    sql_q2 = f"""SELECT 
mtma."id" AS material, 
mtma."name" AS material_name,
qmq."rate_pass_t" as rate_g,
qmt."id" AS testitem, 
qmt."name" AS testitem_name, 
CAST(SUM(num_test * val_avg) AS FLOAT)/NULLIF(SUM(num_test), 0) AS val_avg, 
SUM(num_test) AS num_test, 
SUM(num_ok) AS num_ok,
100 * CAST(SUM(num_ok) AS FLOAT)/NULLIF(SUM(num_test),0) AS rate_pass
FROM qm_quastat qmq
LEFT JOIN wpm_sflog sflog on sflog.id = qmq.sflog_id
LEFT JOIN mtm_mgroup mgroup on mgroup.id = sflog.mgroup_id
LEFT JOIN mtm_material mtma on mtma.id = qmq.material_id
LEFT JOIN qm_testitem qmt on qmt.id = qmq.testitem_id
where EXTRACT(year from sflog.work_date) = {year_s}
and EXTRACT(month from sflog.work_date) = {month_s} and EXTRACT(day from sflog.work_date) = {day_s}
and mgroup.id = '{mgroup.id}'
and qmq.val_avg is not NULL
and qmq.num_test is not NULL
and qmq.num_ok is not NULL
GROUP BY mgroup."id", mtma."id", qmt."id", qmq."rate_pass_t", mgroup.sort, mtma.sort, qmt.sort
ORDER BY mgroup.sort, mtma.sort, qmt.sort
    """
    res2 = query_all_dict(sql_q2)
    enstat, _ = EnStat.objects.get_or_create(type="day_s", mgroup=mgroup, year_s=year_s, month_s=month_s, day_s=day_s,
                                                defaults={'type': 'day_s', 'mgroup': mgroup, 'year_s': year_s, 'month_s': month_s, 'day_s': day_s})
    enstat.qua_data = res2
    enstat.save(update_fields=['qua_data'])

    if team:
    # 班月统计
        sql_q3 = f"""SELECT 
    mtma."id" AS material, 
    mtma."name" AS material_name,
    qmt."id" AS testitem, 
    qmt."name" AS testitem_name, 
    qmq."rate_pass_t" as rate_g,
    CAST(SUM(num_test * val_avg) AS FLOAT)/NULLIF(SUM(num_test), 0) AS val_avg, 
    SUM(num_test) AS num_test, 
    SUM(num_ok) AS num_ok,
    100 * CAST(SUM(num_ok) AS FLOAT)/NULLIF(SUM(num_test),0) AS rate_pass
    FROM qm_quastat qmq
    LEFT JOIN wpm_sflog sflog on sflog.id = qmq.sflog_id
    LEFT JOIN mtm_team team on team.id = sflog.team_id
    LEFT JOIN mtm_material mtma on mtma.id = qmq.material_id
    LEFT JOIN qm_testitem qmt on qmt.id = qmq.testitem_id
    where EXTRACT(year from sflog.work_date) = {year_s}
    and EXTRACT(month from sflog.work_date) = {month_s}
    and team.id = '{team.id}'
    and qmq.val_avg is not NULL
    and qmq.num_test is not NULL
    and qmq.num_ok is not NULL
    GROUP BY mtma."id", qmt."id", qmq."rate_pass_t", mtma.sort, qmt.sort
    ORDER BY mtma.sort, qmt.sort
        """
        res3 = query_all_dict(sql_q3)
        enstat, _ = EnStat.objects.get_or_create(type="month_st", mgroup=mgroup, team=team, year_s=year_s, month_s=month_s,
                            defaults={'type': 'month_st', 'mgroup': mgroup, 'year_s': year_s, 'month_s': month_s, 'team': team})
        enstat.qua_data = res3
        enstat.save(update_fields=['qua_data'])

# 月统计
    sql_q4 = f"""SELECT 
mtma."id" AS material, 
mtma."name" AS material_name,
qmt."id" AS testitem, 
qmt."name" AS testitem_name, 
CAST(SUM(num_test * val_avg) AS FLOAT)/NULLIF(SUM(num_test), 0) AS val_avg, 
SUM(num_test) AS num_test, 
SUM(num_ok) AS num_ok,
100 * CAST(SUM(num_ok) AS FLOAT)/NULLIF(SUM(num_test),0) AS rate_pass
FROM qm_quastat qmq
LEFT JOIN wpm_sflog sflog on sflog.id = qmq.sflog_id
LEFT JOIN mtm_mgroup mgroup on mgroup.id = sflog.mgroup_id
LEFT JOIN mtm_material mtma on mtma.id = qmq.material_id
LEFT JOIN qm_testitem qmt on qmt.id = qmq.testitem_id
where EXTRACT(year from sflog.work_date) = {year_s}
and EXTRACT(month from sflog.work_date) = {month_s}
and mgroup.id = '{mgroup.id}'
and qmq.val_avg is not NULL
and qmq.num_test is not NULL
and qmq.num_ok is not NULL
GROUP BY mgroup."id", mtma."id", qmt."id", mgroup.sort, mtma.sort, qmt.sort
ORDER BY mgroup.sort, mtma.sort, qmt.sort
    """
    res4 = query_all_dict(sql_q4)
    enstat, _ = EnStat.objects.get_or_create(type="month_s", mgroup=mgroup, year_s=year_s, month_s=month_s,
                                                defaults={'type': 'month_s', 'mgroup': mgroup, 'year_s': year_s, 'month_s': month_s})
    enstat.qua_data = res4
    enstat.save(update_fields=['qua_data'])

# 年统计
    sql_q5 = f"""SELECT 
mtma."id" AS material, 
mtma."name" AS material_name,
qmt."id" AS testitem, 
qmt."name" AS testitem_name, 
CAST(SUM(num_test * val_avg) AS FLOAT)/NULLIF(SUM(num_test), 0) AS val_avg, 
SUM(num_test) AS num_test, 
SUM(num_ok) AS num_ok,
100 * CAST(SUM(num_ok) AS FLOAT)/NULLIF(SUM(num_test),0) AS rate_pass
FROM qm_quastat qmq
LEFT JOIN wpm_sflog sflog on sflog.id = qmq.sflog_id
LEFT JOIN mtm_mgroup mgroup on mgroup.id = sflog.mgroup_id
LEFT JOIN mtm_material mtma on mtma.id = qmq.material_id
LEFT JOIN qm_testitem qmt on qmt.id = qmq.testitem_id
where EXTRACT(year from sflog.work_date) = {year_s}
and mgroup.id = '{mgroup.id}'
and qmq.val_avg is not NULL
and qmq.num_test is not NULL
and qmq.num_ok is not NULL
GROUP BY mgroup."id", mtma."id", qmt."id", mgroup.sort, mtma.sort, qmt.sort
ORDER BY mgroup.sort, mtma.sort, qmt.sort
    """
    res5 = query_all_dict(sql_q5)
    enstat, _ = EnStat.objects.get_or_create(type="year_s", mgroup=mgroup, year_s=year_s,
                                                defaults={'type': 'year_s', 'mgroup': mgroup, 'year_s': year_s})
    enstat.qua_data = res5
    enstat.save(update_fields=['qua_data'])