张科最新脚本

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import sys
sys.path.append(r'e:\Desktop\新环境\DataMachine_for_timers\src')

from utils import mysqlhelper,emailhelper
from config import config

mysqlinstance_tm_new_hfjy = mysqlhelper(**config.tm_new_hfjy)
mysqlinstance_tm_new_bidata = mysqlhelper(**config.tm_new_bidata)
mysqlinstance_dmart_bidata = mysqlhelper(**config.dmart_bidata)


# In[2]:


import pandas as pd
data = pd.read_excel(r'E:\Desktop\zkw.xlsx')
s = tuple(data.学员编号)


# In[3]:


def get_data():

    sql1 = '''
    SELECT 
        srs.createtime 提交时间,
        s.name 学生姓名,
        s.student_no 学员编号,
        zti.teacher_name 推荐人,
        zti.teaching_department 教学部,
        zti.teaching_group 教学组,
        zti.teacher_no 教师编号,
        '名师天团app' 数据来源,
        case when srs.`status` = 2 then '是' else '否' end 是否成单,
        (case when s.sale_stage2 = 0  then '新名单' 
                    when s.sale_stage2 = 1  then '跟进中' 
                    when s.sale_stage2 = 2  then '无效名单' 
                    when s.sale_stage2 = 3  then '未接听' 
                    when s.sale_stage2 = 4  then '未试听失单'
                    when s.sale_stage2 = 5  then '工作台销售阶段' 
                    when s.sale_stage2 = 6  then '已发起设班单' 
                    when s.sale_stage2 = 7  then '已驳回或撤回' 
                    when s.sale_stage2 = 8  then '已排待沟通'
                    when s.sale_stage2 = 9  then '试听后待反馈' 
                    when s.sale_stage2 = 10  then '反馈后跟进中' 
                    when s.sale_stage2 = 11  then '试听已跳票或取消' 
                    when s.sale_stage2 = 12  then '试听环节失单'
                    when s.sale_stage2 = 13  then '待提交成单' 
                    when s.sale_stage2 = 14  then '成单签订中' 
                    when s.sale_stage2 = 15  then '已成单' end) 跟进状态,
        s.submit_time 学生本身进线时间,
        (SELECT min(cr.end_time) FROM view_communication_record cr WHERE s.student_intention_id = cr.student_intention_id and cr.content = '学科老师转介绍') 学科老师转介绍时间

    FROM
        view_study_recommend_students srs
    left join 
        view_student s on srs.studentphone = s.phone
    left join 
        bidata.zm_teacher_info zti on zti.teacher_id = srs.userid
    left join 
        bidata.charlie_new_keys cnk on cnk.intention_id =s.student_intention_id
    where 
        srs.createtime >= '2019-05-01'
        and srs.usertype = 1 
        and s.account_type =1  
    '''

    #df1 = mysqlinstance_tm_new_hfjy.get_df(sql1)


    sql2 = '''
        SELECT
                s.student_no 学员编号,
                ui.name 销售姓名,
                min(cr.end_time) 销售沟通时间
        FROM
                view_student s 
        LEFT JOIN
                view_communication_record cr on s.student_intention_id = cr.student_intention_id
        LEFT JOIN
                view_user_info ui on ui.user_id = cr.communication_person
        WHERE
                s.student_no in {0}
                AND s.account_type = 1
                AND ui.account_type = 1
                AND cr.end_time >= '2019-05-01'
                AND EXISTS (select 1 from bidata.charlie_dept_month_end where user_id = cr.communication_person)
        GROUP BY
                s.student_no
    '''.format(s)

    df2 = mysqlinstance_tm_new_hfjy.get_df(sql2)


    sql3 = '''

        SELECT
                s.student_no 学员编号,
                s.submit_time 进线时间,
                cr.end_time 老师转介绍时间
        FROM
                view_student s 
        LEFT JOIN
                view_communication_record cr on s.student_intention_id = cr.student_intention_id
        WHERE
                s.student_no in {0}
                AND cr.content = '学科老师转介绍'
        GROUP BY
                s.student_no
    '''.format(s)

    df3 = mysqlinstance_tm_new_hfjy.get_df(sql3)


    sql4 = '''
        SELECT
            a.*
        FROM
                (
                SELECT
                        ui.name 最新销售姓名,
                        cr.end_time 最新沟通记录时间,
                        s.student_no 学员编号,
                        d.value 意向级别
                FROM
                        view_student s 
                LEFT JOIN
                        view_communication_record cr ON s.student_intention_id = cr.student_intention_id
                LEFT JOIN
                        view_user_info ui on ui.user_id = cr.communication_person
                LEFT JOIN
                        ddic d ON d.code = cr.intention_level AND type = 'TP015'
                WHERE
                        s.student_no in {0}
                        AND cr.end_time >= '2019-05-01'
                        AND EXISTS (select 1 from bidata.charlie_dept_month_end where user_id = cr.communication_person)
                ORDER BY
                        cr.end_time DESC
                ) a
        GROUP BY
                a.学员编号
    '''.format(s)

    df4 = mysqlinstance_tm_new_hfjy.get_df(sql4)

    return df2,df3,df4


# In[4]:


df2,df3,df4 = get_data()


# In[11]:


df_sub1 =  pd.merge(df3,df2,how='left',on=['学员编号'])


# In[14]:


df_sub1 = pd.merge(df_sub1,df4,how='left',on=['学员编号'])
df_sub1.head()


# In[15]:


df_sub1['学员编号'] = df_sub1.学员编号.astype('int')


# In[16]:


df_sub = pd.merge(data,df_sub1,how='left',on=['学员编号'])

df_sub.head()


# In[9]:


df_sub.to_excel(r'E:/Desktop/zhangke0709-2.xlsx')

文档更新时间: 2019-08-19 11:54   作者:admin