匹配一下学员编号对应的线索归属销售

#### 日期:2019-01-29
#### 需求方:姚传婷
#### 数据输出方式:excel
#### created by:付朝阳

select s.student_no, nk.date, nk.communication_person, ui.job_number, ui.name, sd.department_name
from view_communication_record cr
left join view_student s on s.student_intention_id = cr.student_intention_id
left join bidata.charlie_new_keys nk on nk.intention_id = cr.student_intention_id
left join view_user_info ui on ui.user_id = nk.communication_person

left join sys_user_role sur on ui.user_id=sur.user_id
left join sys_role sr on sur.role_id=sr.role_id
left join sys_department sd on sr.department_id=sd.department_id
where s.student_no in {0} 

建立销售相关信息数据集

#### 日期:2019-01-29
#### 需求方:杨佳舒
#### 数据输出方式:excel
#### created by:汪国强

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) 渠道跟进人,
    re.add_time 添加时间,
    re.first_allot_time 第一次分配时间,
    re.second_allot_time 最后一次分配时间,
    re.next_flow_time 下次沟通时间,
        (case ir.r_status when 6 then '已通过' when 10 then '淘汰' when 0 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) 一级渠道,
        (select name from channel where channel.id = re.second_channel) 二级渠道,
        re.interview_date 面试时间,
    re.offer_time offer发送时间,
    re.entry_time 入职时间,
    ir.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()
文档更新时间: 2019-03-08 11:44   作者:admin