团员细分情况#### 主题:团员细分情况

#### 主题:团员细分情况#### 主题:团员细分情况
#### 执行时间:xxxs,shape:xxx,新脱敏库
#### 需求方: 陈湘婷
#### created_by:汪国强
#### update by:
#### checkd by:
SELECT
    t.teacher_no 教师编号,
    u.name 教师姓名,
    u.phone 手机号,
    u.id_number 身份证号,
    (select gd.value from bidata.gen_dict gd where gd.type ='teaching_department' and gd.code = tt.teaching_department) 教学部,
    (select group_concat(subject_name) from subject where subject_id in (select subject_id from teacher_subject_rel where teacher_id =t.teacher_id)) 学科
FROM
    view_teacher t
LEFT JOIN
    view_user_info u on u.user_id = t.teacher_id
LEFT JOIN
    view_tms_teacher_ext tt on tt.id = t.teacher_id
WHERE
    t.instruction_type = 3 and t.exit_time >= '2019-03-01' and t.exit_time <= '2019-03-10'

sale各渠道明细

#### 主题:sale各渠道明细
#### 执行时间:xxx,shape:xxx
#### 需求方: 杨佳舒
#### created_by:汪国强
#### update by:
#### checkd by:

import sys
sys.path.append(r'e:\Desktop\DataMachine_for_clean\src')
from ML import data_suit,plot_suit
from utils import mysqlhelper,elapse
from config import config
import pandas as pd
import numpy as np
import pymysql
from datetime import datetime , time
mysqlinstance_dmart_dmart = mysqlhelper(**config.dmart_dmart)
sql2 = '''
SELECT
    re.id ID,
    re.name 姓名,
    s.name 毕业学校,
    re.phone 电话,
    re.email 邮箱,
    re.referee 推荐人,
    (select name from view_user where re.director_id = view_user.id) 渠道跟进人,
    date_format(re.add_time,'%Y%m') resume_addtime,
    re.first_allot_time 第一次分配时间,
    re.second_allot_time 最后一次分配时间,
    re.next_flow_time 下次沟通时间,
    (case re.status when 1 then '待处理' when 2 then '再联系' when 3 then '未接通' when 4 then '已邀约' when 5 then '未参加面试' when 6 then '面试通过' when 7 then '已入培' when 8 then '出培' when 9 then '待定' when 10 then '淘汰' end)应聘状态,
    (select name from out_reason where out_reason.id = re.out_reason_id) 淘汰原因,
    (select name from out_reason where out_reason.id = re.second_reason) 淘汰详细原因,
    re.work_place 工作地点,
    re.station 面试岗位,
    ir.interview_name 面试官姓名,
    case st.level when 1 then '专员' when 2 then '助理' when 3 then '主管' when 4 then '经理' when 5 then '总监' end  级别,
    s.area 地区,
    (select name from channel where channel.id = re.first_channel) one_channel_name,
    (select name from channel where channel.id = re.second_channel) second_channel_name,
    date_format(re.interview_date,'%Y%m') interview_date,
    date_format(re.offer_time,'%Y%m') offer_time,
    date_format(re.entry_time,'%Y%m') entry_time,
    date_format(ir.face_time,'%Y%m')  face_time,
    rr.traindate 预约培训时间,
    rr.next_time 培训next_time,
    rr.add_time 培训add_time
from
    hrm_sale.view_resume re
    left join hrm_sale.view_user u on u.id = re.id 
    left join hrm_sale.interviewer_record ir on ir.r_id = re.id
    left join hrm_sale.stations st on st.id = re.id
    left join hrm_sale.school s on s.id = re.id
    left join hrm_sale.romm_record rr on rr.r_id = re.id
where
    re.add_time >= '2018-01-01' and re.add_time < CURDATE()
group by
    re.id
'''
my_conn = pymysql.connect(host="****", port=3306, user="****",passwd="****", db="hrm_sale", charset="utf8")
df2 = pd.read_sql(sql2,my_conn)
sub_data = df2.groupby(by=['one_channel_name','second_channel_name','resume_addtime'])['ID'].count().reset_index().rename(columns= {'ID':'num','resume_addtime':'resume_addtime'})
data1 = df2.groupby(by=['one_channel_name','second_channel_name','interview_date'])['ID'].count().reset_index().rename(columns= {'ID':'interview_date','interview_date':'resume_addtime'})
data2 = df2.groupby(by=['one_channel_name','second_channel_name','offer_time'])['ID'].count().reset_index().rename(columns= {'ID':'offer_time','offer_time':'resume_addtime'})
data3 = df2.groupby(by=['one_channel_name','second_channel_name','entry_time'])['ID'].count().reset_index().rename(columns= {'ID':'entry_time','entry_time':'resume_addtime'})
data4 = df2.groupby(by=['one_channel_name','second_channel_name','face_time'])['ID'].count().reset_index().rename(columns= {'ID':'face_time','face_time':'resume_addtime'})
sub_data = pd.merge(sub_data,data1,how='left',on=['one_channel_name','second_channel_name','resume_addtime'])
sub_data = pd.merge(sub_data,data4,how='left',on=['one_channel_name','second_channel_name','resume_addtime'])
sub_data = pd.merge(sub_data,data2,how='left',on=['one_channel_name','second_channel_name','resume_addtime'])
sub_data = pd.merge(sub_data,data3,how='left',on=['one_channel_name','second_channel_name','resume_addtime'])
# sub_data.to_excel(r'e:\Desktop\新销售明细.xlsx',index=False,encoding='GB18030')
data_index =  ['one_channel_name','second_channel_name','resume_addtime','num','interview_date','face_time','offer_time','entry_time']
wgq_sale = pd.DataFrame(data=sub_data,columns=data_index)
wgq_sale.head()
#### 插入新数据
mysqlinstance_dmart_dmart.insertmany_bydf(wgq_sale,'wgq_sale',if_exists='replace')

hrm各渠道明细

#### 主题:hrm各渠道明细
#### 执行时间:xxx,shape:xxx
#### 需求方: 杨佳舒
#### created_by:汪国强
#### update by:
#### checkd by:along
import sys
sys.path.append(r'e:\Desktop\DataMachine_for_clean\src')
import pandas as pd
import numpy as np
import pymysql
from ML import data_suit,plot_suit
from utils import mysqlhelper,elapse
from config import config
from datetime import datetime , time
mysqlinstance_tm_new_bidata = mysqlhelper(**config.tm_new_bidata)
mysqlinstance_dmart_dmart = mysqlhelper(**config.dmart_dmart)
sql1 = '''
select resumeId, oneLevelChannelName, secondLevelChannelName, resumeProperty, date_format(resumeAddTime,'%Y%m')resumeAddTime , date_format(resumeInterviewDate,'%Y%m')resumeInterviewDate     ,
date_format(interviewerRecordAddTime,'%Y%m')interviewerRecordAddTime    ,date_format(resumeInfoTrainDate,'%Y%m')resumeInfoTrainDate    
from will_hr
'''
sql2 = '''
select hrm_id  resumeId, date_format(entry_time,'%Y%m')entry_time , date_format(exit_time,'%Y%m')exit_time
from bidata.will_teacher_time_flow_his'''
df1 = mysqlinstance_dmart_dmart.get_df(sql1)
df2 = mysqlinstance_tm_new_bidata.get_df(sql2)
data_sub = pd.merge(df1,df2, how = 'left', on = 'resumeId')
### 统计数量
bb = data_sub.groupby(by=['oneLevelChannelName','secondLevelChannelName','resumeAddTime','resumeProperty'])['resumeId'].count().reset_index().rename(columns= {'resumeId':'num','resumeAddTime':'Date'})
aa = data_sub.groupby(by=['oneLevelChannelName','secondLevelChannelName','resumeAddTime'])['resumeId'].count().reset_index().rename(columns= {'resumeId':'sum_num','resumeAddTime':'Date'})
one = pd.merge(bb,aa,how='left',on=['oneLevelChannelName','secondLevelChannelName','Date'])
a_list = ['resumeInterviewDate','interviewerRecordAddTime','resumeInfoTrainDate','entry_time','exit_time']
zero = one.copy()
for i in a_list:
    data = data_sub.groupby(by=['oneLevelChannelName','secondLevelChannelName','resumeProperty',str(i)])['resumeId'].count().reset_index().rename(columns= {'resumeId':str(i),str(i):'Date'})
    zero = pd.merge(zero,data,how='left',on=['oneLevelChannelName','secondLevelChannelName','Date','resumeProperty'])
zero = zero.rename(columns={'oneLevelChannelName':'one_channel_name', 'secondLevelChannelName':'second_channel_name', 'Date':'resume_addtime',
                     'resumeProperty':'resume_property', 'resumeInterviewDate':'resume_interviewdate','interviewerRecordAddTime':'interviewer_record_addtime',
                     'resumeInfoTrainDate':'resume_info_traindate', 'entry_time':'entry_time','exit_time':'exit_time'})
zero.head()
#### 更改columns 清空原数据 再插入
mysqlinstance_dmart_dmart.insertmany_bydf(zero,'wgq_hrm',if_exists='replace')
文档更新时间: 2019-03-14 11:18   作者:admin