教师停课原因

#### 引用自:
#### 教师停课原因
#### 执行时间:2.899,新脱敏库hfjy
#### 日期:2019-01-03
#### 需求方:吴婧瑶
#### 数据输出方式:excel
#### created by:汪国强

SELECT
    t.teacher_no 教师编号,
    u.name 教师姓名,
    case t.quarters_type when 1 then '全职授课' when 2 then'全职教研' when 3 then'兼职' when 4 then'实习' end 岗位属性,
    case when  t.start_time is not null and t.end_time >curdate() then '阶段性停课' when t.suspend=0 then '开课' when t.suspend=1 then '永久停课' end 排课状态,
    t.start_time 阶段性停课开始时间,
    t.end_time 阶段性停课结束时间,
    case ael.reson when 1 then '学术阶段性停课' when 2 then '离职交接中' when 3 then '其他原因阶段性停课' else ael.reson end 阶段性停课原因,
    ael.remark 备注,
    t.update_time 修改时间
FROM
    view_teacher t
    left join arranging_edit_log ael on ael.teacher_id = t.teacher_id
    left join view_user_info u on t.teacher_id = u.user_id
WHERE
    t.start_time is not null
    and t.end_time >curdate()
    and t.quarters_type <> 5
GROUP BY
    t.teacher_id

黄露生产需求

#### 引用自:
#### 黄露生产需求
#### 日期:2019-01-03
#### 需求方:黄露
#### 数据输出方式:
#### created by:汪国强

SELECT
      t.teacher_no 教师编号,
      email_personal 个人邮箱,
      u.email 企业邮箱,
    sum(timestampdiff(second,lp.adjust_start_time,lp.adjust_end_time))/3600 剩余课量 
FROM 
     view_teacher t 
     left join view_user_info u on u.user_id = t. teacher_id
     left join lesson_plan lp on lp.teacher_id = t.teacher_id and  lp.adjust_start_time >= '2019-01-01'  and t.suspend=1 and lp.status <> 0 and lp.lesson_type in (1,2) 
WHERE
    t.teacher_no in ('EN672088','MA642770')
GROUP BY
    t.teacher_id

小可爱需求

#### 引用自:郭绍征
#### 黄露生产需求
#### 日期:2019-01-03
#### 需求方:小可爱
#### 数据输出方式:excel
#### created by:汪国强


import pandas as pd
import pymysql
from pyhive import presto
import numpy as np

#读取教师编号
#读取teacher_no
def data():
    data_no= pd.read_excel(r'e:\Desktop\20190104学科转正评估表.xlsx', header=1)
    return data_no

def read_sql(data_no):
    sql = '''   
    select 
        t.teacher_no,teacher_id,
        (select sum(timestampdiff(minute,adjust_start_time,adjust_end_time))/60 
        from lesson_plan 
        where lesson_type = 1 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6) 正式课课时量,
        (select count(distinct student_id) from lesson_plan where lesson_type = 1 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6) 正课学生数,
        (select count(distinct tc.student_intention_id) 
        from lesson_plan lp 
        left join view_student s on lp.student_id = s.student_id 
        left join view_tms_contract tc on tc.student_intention_id = s.student_intention_id
        where 
            lp.lesson_type = 2 and lp.teacher_id = t.teacher_id and lp.status in (3,5) and lp.solve_status <> 6 and tc.status in (3,4,5) and tc.contract_id like 'X%' 
            and new_sign = 1 and lp.adjust_start_time < tc.submit_time) 试听成单数,
        (select count(*) from lesson_plan where lesson_type = 2 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6) 试听课课次,

        (select count(*) from lesson_plan where lesson_type = 2 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6) -
        (select count(distinct lrd.lesson_plan_id) 
        from lesson_plan lp 
        left join bidata.learning_rtc_detail lrd on lrd.lesson_plan_id = lp.lesson_plan_id 
        where lp.teacher_id = t.teacher_id and lp.status in (3,5) and solve_status <> 6 and lesson_type = 2 and lrd.channel like '%v%') 试听课视频未开启次数,

        (select count(*) 
        from lesson_plan lp 
        left join home_work hw on hw.lesson_plan_id = lp.lesson_plan_id
        where lesson_type = 2 and lp.teacher_id = t.teacher_id and lp.status in (3,5) and solve_status <> 6 and hw.lesson_plan_id is null) 试听课作业未布置次数,

        (select count(*) 
        from lesson_plan where lesson_type = 2 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6 and teacher_feedback = 0) 试听课诊断报告未提交次数,
        (select count(*) 
        from lesson_plan where lesson_type = 1 and teacher_id = t.teacher_id and status in (3,5) and solve_status <> 6 and teacher_feedback = 0) 正式课反馈表未提交次数,
        (select count(*) 
        from lesson_plan lp 
        left join lesson_plan_score lps on lp.lesson_plan_id = lps.id 
        where teacher_id = t.teacher_id and (lesson_complete+commonuicate_fluent+teach_quality) < 15) 学员异常评价次数

    from 
        view_teacher t
    where teacher_no in {0}

    '''.format(tuple(data_no['教师编号']))

    ##Mysql数据库连接
    my_conn = pymysql.connect(host="rm-2ze974348wa9e1ev3uo.mysql.rds.aliyuncs.com", port=3306, user="bi_newbaby",
                              passwd="wFwQqEffIYcLVD8trWhe", db="hfjydb", charset="utf8")

    my_df = pd.read_sql(sql, my_conn)

    my_conn.close()
    return my_df


def sort_write(my_df,data_no):
    # 指定list排序顺序
    list_sortes = list((tuple(data_no['教师编号'])))
    my_df['teacher_no'] = my_df['teacher_no'].astype('category').cat.set_categories(list_sortes)
    my_df_sortes = my_df.sort_values(by=['teacher_no'])
    my_df_sortes.to_excel(r'e:\Desktop\学科转正评估0104.xlsx', sheet_name='老师', index=False, encoding='GB18030')



if __name__ == '__main__':
    data_no = data()
    my_df = read_sql(data_no)
    sort_write(my_df, data_no)
文档更新时间: 2019-03-08 11:44   作者:admin