SENIORS = ('刘文峰', '金亮', '姜珍', '余莎莎', '张阳根', '唐国华', '刘国勇', '刘会连', '肖琥', '邱峰', '姚文婷', '李跃')
IGNORE_PERSONS = ('黄红梅',)
IGNORE_PERSONS_SUBQUERY = f"and u.name != '{IGNORE_PERSONS[0]}'" if len(IGNORE_PERSONS) == 1 else f"and u.name not in {IGNORE_PERSONS}"
IGNORE_DEPARTMENTS = ('汇派-质量部', '汇派-生产部', '汇派-计划部', '汇派-人事部', '汇派-采购部',
                      '党建工会', '工程项目中心', '北京技术中心', '政委', '总经办-培训中心')


def querystring_department_user_procinst_duration(start, end, exclude_senior):
    filter_user = f"and u.name not in {SENIORS + IGNORE_PERSONS}" if exclude_senior else IGNORE_PERSONS_SUBQUERY
    return f'''
select department_name,
       user_name,
       procinst_id,
       sum(duration_in_minutes) as procinst_duration_in_minutes
from (
    select distinct d.id as department_id,
           d.name as department_name,
           u.id as user_id,
           u.name as user_name,
           wp.name as process_name,
           wpa.procinst_id as procinst_id,
           wpa.task_id as taskinst_id,
           wpa.task_name as task_name,
           wpa.start_time as start_time,
           wpa.end_time as end_time,
           wpa.total_minutes as duration_in_minutes
    from workflow_process_achievements as wpa
    inner join workflow_process_history as wph on wpa.procinst_id=wph.procinst_id
    inner join workflow_process_version as wpv on wph.version_id=wpv.id
    inner join workflow_process as wp on wpv.process_id=wp.id
    inner join "user" as u on wpa.deal_user_id=u.id
    inner join department_user as du on u.id=du."user"
    inner join department as d on du.department=d.id
    where wpa.end_time >='{start}' and wpa.end_time < '{end}' and wp.deleted=false and wp.is_enable=true 
        and d.delete=0 and u.delete=0 and u.state=1 and u.active_status=1
        and d.name not in {IGNORE_DEPARTMENTS}
        {filter_user}
) as r
group by department_name, user_name, procinst_id
    '''


def querystring_user_procinst_duration(start, end, senior):
    filter_user = f"and u.name in {SENIORS}" if senior else IGNORE_PERSONS_SUBQUERY
    return f'''
select user_name,
       procinst_id,
       sum(duration_in_minutes) as procinst_duration_in_minutes
from (
    select distinct u.id as user_id,
           u.name as user_name,
           wp.name as process_name,
           wpa.procinst_id as procinst_id,
           wpa.task_id as taskinst_id,
           wpa.task_name as task_name,
           wpa.start_time as start_time,
           wpa.end_time as end_time,
           wpa.total_minutes as duration_in_minutes
    from workflow_process_achievements as wpa
    inner join workflow_process_history as wph on wpa.procinst_id=wph.procinst_id
    inner join workflow_process_version as wpv on wph.version_id=wpv.id
    inner join workflow_process as wp on wpv.process_id=wp.id
    inner join "user" as u on wpa.deal_user_id=u.id
    inner join department_user as du on u.id=du."user"
    inner join department as d on du.department=d.id
    where wpa.end_time >='{start}' and wpa.end_time < '{end}' and wp.deleted=false and wp.is_enable=true 
        and d.delete=0 and u.delete=0 and u.state=1 and u.active_status=1
        and d.name not in {IGNORE_DEPARTMENTS}
        {filter_user}
) as r
group by user_name, procinst_id
    '''


# 按月统计项企平台使用人数
def querystring_user_count(start, end):
    return f'''
select count(distinct deal_user_id) as pep_using_user_count 
from workflow_process_achievements as wpa
inner join "user" as u on wpa.deal_user_id=u.id
inner join department_user as du on u.id=du."user"
inner join department as d on du.department=d.id
where wpa.end_time >='{start}' and wpa.end_time < '{end}' and d.delete=0 and u.delete=0 and u.state=1 and u.active_status=1
    and d.name not in {IGNORE_DEPARTMENTS}
    {IGNORE_PERSONS_SUBQUERY}
    '''


# 用户处理流程耗时+用户处理流程条数
def querystring_procinst_by_user(start, end, senior=False):
    return '''
SELECT user_name,
       count(*) as procinst_count_by_user,
       ROUND(AVG(procinst_duration_in_minutes) /60, 2) as procinst_duration_in_hours_by_user
from ({}) as r2
group by r2.user_name
order by procinst_duration_in_hours_by_user DESC
    '''.format(querystring_user_procinst_duration(start, end, senior))


# 各部门用户处理流程耗时+各部门用户处理流程条数
def querystring_procinst_by_department_user(start, end):
    return """
SELECT department_name,
       user_name,
       count(*) as procinst_count_by_user,
       ROUND(AVG(procinst_duration_in_minutes) /60, 2) as procinst_duration_in_hours_by_user
from ({}) as r2
group by r2.department_name, r2.user_name
order by procinst_duration_in_hours_by_user DESC
    """.format(querystring_department_user_procinst_duration(start, end, True))