fish = new clueFish(config('app.fish_clue_appid'), config('app.fish_clue_secret')); $this->fish_wh = new clueFishWh(config('app.fish_clue_appid_wh'), config('app.fish_clue_secret_wh')); $this->aec = new Aec(config('app.aec_key'), config('app.aec_iv')); $this->token = input('token'); $rsaUtil = new RSAUtil($this->private_key, ''); if (empty($this->token)) return json(['code' => 0, 'msg' => 'Token失效']); $this->token = $rsaUtil->privateDecrypt($this->token); } /** * 内容使用大屏-内容使用统计-左侧 */ public function get_left_content_use_total() { //内容使用数据 $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $orgid = orgSubIds($root_id); $newlist = Org::where([['id', 'in', $orgid]])->field('id,name,pid')->select()->toArray(); //获取部门 $emp_list = Employee::where([['org_id', 'in', $orgid], ['state', '=', '在职'], ['uid', '<>', 0]])->field('id,org_id')->select()->toArray(); //部门员工 $empid = array_column($emp_list, 'id'); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_left_content_use_total-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } //拆分时间 $comtent_list = []; $tmp_day = 1; $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } $share_type = ['MaterialCase', 'Video', 'Article', 'CompanyStrength', 'MaterialEvidence', 'Building', 'ToolAll', 'CustomerJsAll', 'weiwang', 'card', 'MaterialCase_vr', 'Building_vr', 'EmployeeCard_vr', 'Company_vr']; //折线图 foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { $count_num = ShareLog::where([['employee_id', 'in', $empid], ['share_time', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']], ['type', 'in', $share_type]])->column('employee_id'); $comtent_list[$k]['times'] = $v['name']; $comtent_list[$k]['number'] = count($count_num); } else { $count_num = ShareLog::where([['employee_id', 'in', $empid], ['share_time', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']], ['type', 'in', $share_type]])->column('employee_id'); $comtent_list[$k]['time'] = $v; $comtent_list[$k]['number'] = count($count_num); } } $emp_arr = []; foreach($emp_list as $val){ $emp_arr[$val['org_id']][] = $val['id']; } //数据列表 $where_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; foreach ($newlist as &$val) { $val['people_number'] = 0; $val['share_count'] = 0; $val['look_times'] = 0; $val['clue_num'] = 0; if (isset($emp_arr[$val['id']]) && $emp_arr[$val['id']]) { $number = ShareLog::where([['employee_id', 'in', $emp_arr[$val['id']]], ['share_time', 'between', $where_time], ['type', 'in', $share_type]])->column('employee_id'); $val['people_number'] = count(array_unique($number)); $val['share_count'] = count($number); $visit_due_time = Footprints::where([['employee_id', 'in', $emp_arr[$val['id']]], ['addtime', 'between', $where_time]])->sum('visit_due_time'); $val['look_times'] = round($visit_due_time / 60, 1); //获取线索 $clue_num = CustomerClue::where([['employee_id', 'in', $number],['addtime', 'between', $where_time]])->count(); $val['clue_num'] = $clue_num; } } Cache::set($cache_data_name,json_encode(['fold_line' => $comtent_list, 'list' => $newlist]), $this->cache_time); return json(['code' => 0, 'data' => ['fold_line' => $comtent_list, 'list' => $newlist], 'msg' => '获取成功']); } /** * 内容使用情况详情 */ public function get_left_content_user_total_info() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times', 'org_id']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_left_content_user_total_info-' . $time[0] . '-' . $time[1] . '_' . $root_id . '_' . $param['org_id']; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $emp_list = Employee::where([['org_id', 'in', $param['org_id']], ['state', '=', '在职'], ['uid', '<>', 0], ['root_id', '=', $root_id]])->field('id,org_id,name')->select()->toArray(); //部门员工 $where_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; $share_type = ['MaterialCase', 'Video', 'Article', 'CompanyStrength', 'MaterialEvidence', 'Building', 'ToolAll', 'CustomerJsAll', 'weiwang', 'card', 'MaterialCase_vr', 'Building_vr', 'EmployeeCard_vr', 'Company_vr']; //数据列表 foreach ($emp_list as &$val) { $val['people_number'] = 0; $val['share_count'] = 0; $val['look_times'] = 0; $val['clue_num'] = 0; $number = ShareLog::where([['employee_id', '=', $val['id']], ['share_time', 'between', $where_time], ['type', 'in', $share_type]])->column('employee_id'); $val['people_number'] = count(array_unique($number)); $val['share_count'] = count($number); $visit_due_time = Footprints::where([['employee_id', '=', $val['id']], ['addtime', 'between', $where_time]])->sum('visit_due_time'); $val['look_times'] = round($visit_due_time / 60, 1); //获取线索 $clue_num = CustomerClue::where([['employee_id', '=', $val['id']], ['addtime', 'between', $where_time]])->count(); $val['clue_num'] = $clue_num; } Cache::set($cache_data_name,json_encode($emp_list), $this->cache_time); return json(['code' => 0, 'data' => $emp_list, 'msg' => '获取成功']); } /** * 内容使用大屏-智能楼书数据-中间 */ public function get_center_content_use_total() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $orgid = orgSubIds($root_id); // $org_list = Org::where([['id', 'in', $orgid]])->field('id,name')->select()->toArray(); $emp_list = Employee::where([['org_id', 'in', $orgid], ['state', '=', '在职'], ['uid', '<>', 0]])->field('id,org_id')->select()->toArray(); //部门员工 $empid = array_column($emp_list, 'id'); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $between_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; $cache_data_name = 'get_center_content_use_total-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $building_vr_all = 0;//楼盘VR $housetype_all = 0;//户型数 $housetype_vr_all = 0;//户型Vr $housetype_develop_all = 0;//楼盘案例数 $where[] = ['del', '=', 0]; $where[] = ['root_id', '=', $root_id]; // $where[] = ['addtime', 'between', $between_time]; //查询楼盘信息 $list = BuildingModel::with(['share_log' => function ($query) {// use ($between_time) $query->where([['type','=','building']])->field('id,data_id,employee_id');//,['share_time', 'between', $between_time] }])->where($where)->field('id,name,vr_link,community_id,org_id,assign_org_id')->order('addtime desc')->select()->toArray(); $build_list = []; foreach($list as $k => $v){ $housetype = BuildingHousetype::where('building_id', '=', $v['id'])->select(); $housetype_count = $housetype->count(); $housetype_all += $housetype_count; $housetype_vr_count = 0; foreach ($housetype as $h){ if ($h['vr_link']) { $housetype_vr_count ++;//户型Vr } } $housetype_vr_all += $housetype_vr_count; $develop = BuildingDevelopCase::where('building_id', '=', $v['id'])->select(); $housetype_develop = 0; //有研发案例的户型 $housetype_have = []; foreach ($housetype as $h){ foreach ($develop as $d){ if ($d['housetype_id'] == $h['id'] && !in_array($h['id'], $housetype_have)){ $housetype_develop ++;//楼盘案例数 } } } $housetype_develop_all += $housetype_develop; if (!empty($v['vr_link'])){ $building_vr_all ++;//楼盘VR } //分享人数-分享次数 $share_num = 0; $share_array = []; foreach ($v['share_log'] as $s) { if ($s['data_id'] == $v['id']) { $share_array[$v['id']][] = $s['employee_id']; $share_num++; } } $share_people = isset($share_array[$v['id']]) ? count(array_unique($share_array[$v['id']])) : 0; //查询浏览时间 $foot_where[] = ['pipe_type', '=', 'building']; $foot_where[] = ['employee_id', 'in', $empid]; // $foot_where[] = ['addtime', 'between', $between_time]; $foot_data = Db::name('footprints')->where($foot_where)->whereLike('reg_info','{"id":' . $v['id'] . ',%')->cursor(); $look_time = 0; foreach ($foot_data as $f) { if ($f['visit_due_time']){ $look_time += $f['visit_due_time']; } } //获取线索 $clue_num = CustomerClue::where([['employee_id', 'in', $empid],['pipe_id','=',$v['id']], ['pipe_type', '=', 'building']])->count();//,['pipe_type', 'in', ['building', 'buildingvr']],['addtime', 'between', $between_time] $build_list[$k]['id'] = $v['id']; $build_list[$k]['name'] = $v['name']; $build_list[$k]['housetype_vr_percent'] = $housetype_count > 0 ? round($housetype_vr_count * 100 / $housetype_count, 2) : 0;//户型完整度 $build_list[$k]['develop_percent'] = $housetype_count > 0 ? round($housetype_develop * 100 / $housetype_count, 2) : 0;//案例完整度 $build_list[$k]['share_people'] = $share_people;//转发人数 $build_list[$k]['share_num'] = $share_num;//转发次数 $build_list[$k]['look_time'] = $look_time;//浏览时长 $build_list[$k]['clue_num'] = $clue_num;//获取线索 } $data['building_count'] = count($list); //楼盘数量 $data['housetype_all'] = $housetype_all; //户型数 $data['housetype_vr_all'] = $housetype_vr_all; //户型Vr $data['housetype_develop_all'] = $housetype_develop_all; //楼盘案例数 $data['building_vr_all'] = $building_vr_all; //楼盘VR $data['housetype_vr_percent'] = $housetype_all > 0 ? round($housetype_vr_all*100/$housetype_all,2) : 0;//户型vr完整度 $data['housetype_develop_percent'] = $housetype_all > 0 ? round($housetype_develop_all*100/$housetype_all,2) : 0;//楼盘案例完整度 $data['building_share_number'] = 0;//楼书转发数据 $data['building_look_number'] = 0;//楼书浏览数据 $data['building_clue_number'] = 0;//楼书获取线索数据 //拆分时间 $comtent_list = []; $tmp_day = 1; $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } //折线图 $tmp_where[] = ['employee_id', 'in', $empid]; $tmp_where[] = ['type','in',['Building', 'Building_vr']]; foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { $tmp_add_time = ['addtime', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']]; $tmp_share_time = ['share_time', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']]; $comtent_list[$k]['times'] = $v['name']; } else { $tmp_add_time = ['addtime', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']]; $tmp_share_time = ['share_time', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']]; $comtent_list[$k]['time'] = $v; } $clue_num = CustomerClue::where([['employee_id', 'in', $empid], ['pipe_type', 'in', ['building', 'buildingvr']], $tmp_add_time])->count(); $look_num = Footprints::where([['employee_id', 'in', $empid], ['pipe_type', 'in', ['building', 'buildingVr']], $tmp_add_time])->count(); $share_num = ShareLog::where($tmp_where)->where([$tmp_share_time])->column('employee_id'); $comtent_list[$k]['share_num'] = count($share_num); $comtent_list[$k]['look_num'] = $look_num; $comtent_list[$k]['clue_num'] = $clue_num; $data['building_share_number'] += count($share_num); //楼书转发数据 $data['building_look_number'] += $look_num; //楼书浏览数据 $data['building_clue_number'] += $clue_num; //楼书获取线索数据 } Cache::set($cache_data_name,json_encode(['total' => $data, 'fold_line' => $comtent_list, 'build_list' => $build_list]), $this->cache_time); return json(['code' => 0, 'data' => ['total' => $data, 'fold_line' => $comtent_list, 'build_list' => $build_list], 'msg' => '获取成功']); } /** * 内容使用大屏-AI谈单工具-右侧 */ public function get_right_content_use_total() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); //默认本月时间 $time = [date('Y-m-1'), date('Y-m-d')]; if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_right_content_use_total-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $orgid = orgSubIds($root_id); $emp_list = Employee::where([['org_id', 'in', $orgid], ['state', '=', '在职'], ['uid', '<>', 0]])->field('id,org_id')->select()->toArray(); //部门员工 $empid = []; $group_empid = []; foreach($emp_list as $val){ $empid[] = $val['id']; $group_empid[$val['org_id']][] = $val['id']; } //拆分时间 $fold_line_list = []; $tmp_day = 1; $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } //AI谈单数据 $db_where[] = ['CustomerVisitLog.remark', 'like', '%讲解智慧屏##%']; $db_where[] = ['Customer.employee_id', 'in', $empid]; $db_where[] = ['CustomerVisitLog.is_merge', '=', 0]; foreach($time_arr as $k => $v){ if ($tmp_day == 2) { $tmp_add_time = ['CustomerVisitLog.addtime', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']]; $fold_line_list[$k]['times'] = $v['name']; } else { $tmp_add_time = ['CustomerVisitLog.addtime', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']]; $fold_line_list[$k]['time'] = $v; } $count = Db::view('Customer', ['name', 'phone'])->view('CustomerVisitLog', 'customer_id,addtime,remark', 'Customer.id=CustomerVisitLog.customer_id')->where($db_where)->where([$tmp_add_time])->count(); $fold_line_list[$k]['number'] = $count; } //获取部门情况 $org_list = []; $org_where[] = ['CustomerVisitLog.is_merge', '=', 0]; $org_where[] = ['CustomerVisitLog.remark', 'like', '%讲解智慧屏##%']; $newlist = Org::where([['id', 'in', $orgid]])->field('id,name')->select()->toArray(); foreach ($newlist as $key => $val) { $org_list[$key]['id'] = $val['id']; $org_list[$key]['name'] = $val['name']; $org_list[$key]['people'] = 0; //谈单人数 $org_list[$key]['number'] = 0; //谈单次数 $org_list[$key]['times'] = 0; //总谈单时长 if (isset($group_empid[$val['id']]) && $group_empid[$val['id']]) { $tmp_employee = ['Customer.employee_id', 'in', $group_empid[$val['id']]]; $tmp_add_time = ['CustomerVisitLog.addtime', 'between', [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']]; $tmp_data = Db::view('Customer', ['name', 'phone'])->view('CustomerVisitLog', 'customer_id,addtime,remark', 'Customer.id=CustomerVisitLog.customer_id')->where($org_where)->where([$tmp_add_time,$tmp_employee])->select()->toArray(); $org_list[$key]['people'] = count(array_unique(array_column($tmp_data,'customer_id'))); $org_list[$key]['number'] = count(array_column($tmp_data,'customer_id')); $tmp_times = 0; foreach($tmp_data as $n){ $tmp_times += explode('分钟;
',explode('谈单时长:',$n['remark'])[1])[0]; } $org_list[$key]['times'] = $tmp_times; } } //企业话术使用数据 $tmp_where[] = ['root_id', '=', $root_id]; $tmp_where[] = ['employee_id', 'in', $empid]; $tmp_where[] = ['addtime', 'between', [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']]; $chosen = TalkskillChosen::where($tmp_where)->field('sum(use_count) as use_count,sum(view_times) as view_times')->find();//精选话术的使用次数-和浏览次数 $success = TalkskillSuccess::where($tmp_where)->field('sum(use_count) as use_count,sum(view_times) as view_times')->find();//成功案例的使用次数-和浏览次数 $bai_use_count = Talkskill::where($tmp_where)->sum('use_count');//百问百答的使用次数 $tmp_where[] = ['type', '=', 'talkskill']; $bai_look_count = TalkskillViewLog::where($tmp_where)->count();//浏览次数 $total = [ 'look_chosen' => $chosen['view_times'] ?? 0, 'look_uccess' => $success['view_times'] ?? 0, 'look_bai' => $bai_look_count ?? 0, 'look_all' => $chosen['view_times'] + $success['view_times'] + $bai_look_count, 'use_chosen' => $chosen['use_count'] ?? 0, 'use_uccess' => $success['use_count'] ?? 0, 'use_bai' => $bai_use_count ?? 0, 'use_all' => $chosen['use_count'] + $success['use_count'] + $bai_use_count, ]; Cache::set($cache_data_name,json_encode(['fold_line' => $fold_line_list, 'org_list' => $org_list, 'total' => $total]), $this->cache_time); return json(['code' => 0, 'data' => ['fold_line' => $fold_line_list, 'org_list' => $org_list, 'total' => $total], 'msg' => '获取成功']); } /** * 内容使用大屏-AI谈单工具-右侧-详情 */ public function get_right_content_use_total_info() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times', 'org_id']); //默认本月时间 $time = [date('Y-m-1'), date('Y-m-d')]; if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_right_content_use_total_info-' . $time[0] . '-' . $time[1] . '_' . $root_id . '_' . $param['org_id']; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $emp_list = Employee::where([['org_id', '=', $param['org_id']], ['state', '=', '在职'], ['uid', '<>', 0], ['root_id', '=', $root_id]])->field('id,org_id,name')->select()->toArray(); //部门员工 //获取部门情况 $org_list = []; $org_where[] = ['CustomerVisitLog.is_merge', '=', 0]; $org_where[] = ['CustomerVisitLog.remark', 'like', '%讲解智慧屏##%']; foreach ($emp_list as $key => $val) { $org_list[$key]['name'] = $val['name']; $tmp_employee = ['Customer.employee_id', '=', $val['id']]; $tmp_add_time = ['CustomerVisitLog.addtime', 'between', [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']]; $tmp_data = Db::view('Customer', ['name', 'phone'])->view('CustomerVisitLog', 'customer_id,addtime,remark', 'Customer.id=CustomerVisitLog.customer_id')->where($org_where)->where([$tmp_add_time, $tmp_employee])->select()->toArray(); $org_list[$key]['people'] = count(array_unique(array_column($tmp_data, 'customer_id'))); $org_list[$key]['number'] = count(array_column($tmp_data, 'customer_id')); $tmp_times = 0; foreach ($tmp_data as $n) { $tmp_times += explode('分钟;
', explode('谈单时长:', $n['remark'])[1])[0]; } $org_list[$key]['times'] = $tmp_times; } Cache::set($cache_data_name,json_encode($org_list), $this->cache_time); return json(['code' => 0, 'data' => $org_list, 'msg' => '获取成功']); } /** * 投放大屏-当前时间范围内所有数据-跨月则按月份查找-左上角折线图 */ public function get_fish_campaign_all_day() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_fish_campaign_all_day-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $list = $tx_list = []; $tmp_day = 1; $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } $where[] = ['root_id', '=', $root_id]; foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { $list[$k]['time'] = $v['name']; $tx_list[$k]['time'] = $v['name']; $data = FishCampaign::where($where)->where([['type', 'in', [1]]])->whereTime('data_time', 'between', [$v['startDate'], $v['endDate']])->field('sum(`show`) as shows,sum(`click`) as click,sum(`convert`) as converts')->find(); $tx_data = FishCampaign::where($where)->where([['type', '=', 2]])->whereTime('data_time', 'between', [$v['startDate'], $v['endDate']])->field('sum(`show`) as shows,sum(`click`) as click,sum(`convert`) as converts')->find(); //todo 直播 $live_clue_where[] = ['type', '=', 4]; $live_clue_where[] = ['root_id', '=', $root_id]; $zhibo_clue_count = FishData::where($live_clue_where)->whereTime('create_time', 'between', [$v['startDate'], $v['endDate']])->count(); //todo 腾讯 $tx_clue_where[] = ['type', '=', 2]; $tx_clue_where[] = ['root_id', '=', $root_id]; $tx_clue_count = FishData::where($tx_clue_where)->whereTime('create_time', 'between', [$v['startDate'], $v['endDate']])->count(); } else { $data = FishCampaign::where($where)->where([['type', 'in', [1]]])->where(['data_time' => $v])->field('sum(`show`) as shows,sum(`click`) as click,sum(`convert`) as converts')->find(); $tx_data = FishCampaign::where($where)->where([['type', '=', 2]])->where(['data_time' => $v])->field('sum(`show`) as shows,sum(`click`) as click,sum(`convert`) as converts')->find(); $list[$k]['time'] = $v; $tx_list[$k]['time'] = $v; //todo 直播 $live_clue_where[] = ['type', '=', 4]; $live_clue_where[] = ['root_id', '=', $root_id]; $zhibo_clue_count = FishData::where($live_clue_where)->where('create_time', $v)->count(); //todo 腾讯 $tx_clue_where[] = ['type', '=', 2]; $tx_clue_where[] = ['root_id', '=', $root_id]; $tx_clue_count = FishData::where($tx_clue_where)->where('create_time',$v)->count(); } $list[$k]['shows'] = $data['shows'] ?? 0; $list[$k]['click'] = $data['click'] ?? 0; $list[$k]['converts'] = $zhibo_clue_count+$data['converts'] ?? 0; $tx_list[$k]['shows'] = $tx_data['shows'] ?? 0; $tx_list[$k]['click'] = $tx_data['click'] ?? 0; $tx_list[$k]['converts'] = $tx_clue_count ?? 0; } Cache::set($cache_data_name,json_encode(['fish' => $list, 'tx' => $tx_list]), $this->cache_time); return json(['code' => 0, 'data' => ['fish' => $list, 'tx' => $tx_list], 'msg' => '获取成功']); } /** * 投放大屏-查询本日广告组数据-单独日期-左下角列表 */ public function get_fish_campaign_day() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_fish_campaign_day-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } $where[] = ['root_id', '=', $root_id]; $where[] = ['data_time', 'between', $time]; $where[] = ['type', 'in', [1,4]]; $list = FishCampaign::where($where)->field('campaign_id,campaign_name,cost,show,click,avg_click_cost,ctr,convert,convert_cost,convert_rate,data_time')->order('id desc')->select(); $tx_where[] = ['root_id', '=', $root_id]; $tx_where[] = ['data_time', 'between', $time]; $tx_where[] = ['type', '=', 2]; $tx_list = FishCampaign::where($tx_where)->field('campaign_id,campaign_name,cost,show,click,avg_click_cost,ctr,convert,convert_cost,convert_rate,data_time')->order('id desc')->select(); Cache::set($cache_data_name,json_encode(['fish' => $list, 'tx' => $tx_list]), $this->cache_time); return json(['code' => 0, 'data' => ['fish' => $list, 'tx' => $tx_list], 'msg' => '获取成功']); } /** * 获取今日广告组更新的线索 */ public function get_fish_top_campaign_data() { $root_id = $this->aec->decrypt($this->token); $channel_setting = Company::where(['root_id' => $root_id])->value('channel_setting'); $fish_change =[]; if (!empty($channel_setting)){ $tmp_data = json_decode($channel_setting, true); $time = date('Y-m-d'); //查询今天的数据 $tmp_data = $this->refreshToken($tmp_data, $root_id); $advertiser_id = explode(',', $tmp_data['fish_adv']); foreach ($advertiser_id as $va) { $number = 0; $data = $this->fish->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $time, 'end_date' => $time, 'page' => 1]); if ($data['code'] == 0) { foreach ($data['data']['list'] as $val) { //需要比是否有新的线索 $info = FishCampaign::where(['root_id' => $root_id, 'data_time' => $time, 'advertiser_id' => $va, 'campaign_id' => $val['campaign_id']])->field('id,campaign_name,convert')->find(); if (isset($info) && $info) { $tmp_number = $val['convert'] > $info['convert'] ? $val['convert'] - $info['convert'] : 0; //比之前存的记录多出多少线索 $number += $tmp_number; $val['id'] = $info['id']; FishCampaign::update($val); } } } if ($number > 0) { $fish_change[] = "字节跳动账号-获取线索" . $number . '个'; } } } return json(['code' => 0, 'data' => $fish_change, 'msg' => '获取成功']); } /** * 投放大屏-获取广告投放总数据-中间部分 */ public function get_adv_total_data() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); //时间区间 $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_adv_total_data-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } //飞鱼数据 $where[] = ['type', 'in', [1,4]]; $where[] = ['root_id', '=', $root_id]; $where[] = ['data_time', 'between', $time]; $data = FishCampaign::where($where)->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts")->find(); //todo 直播计算 $feiyu_where[] = ['type', 'in', [1]]; $feiyu_where[] = ['root_id', '=', $root_id]; $feiyu_where[] = ['create_time', 'between', $time]; // $feiyu_clue_count = FishCampaign::where($feiyu_where)->sum('convert'); $feiyu_clue_count = FishData::where($feiyu_where)->count(); $zhibo_where[] = ['type', 'in', [4]]; $zhibo_where[] = ['root_id', '=', $root_id]; $zhibo_where[] = ['create_time', 'between', $time]; // $zhibo_where[] = ['advertiser_id', '<>', '0']; $zhibo_clue_count = FishData::where($zhibo_where)->count(); $tx_clue_where[] = ['type', 'in', [2]]; $tx_clue_where[] = ['root_id', '=', $root_id]; $tx_clue_where[] = ['create_time', 'between', $time]; $tx_clue_count = FishData::where($tx_clue_where)->count(); $toufang_clue_count = $feiyu_clue_count+$zhibo_clue_count; $customer_id = FishData::where([['type', 'in', [1,4]], ['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1]])->column('customer_id'); //查询有效线索 $source_id = CustomerSource::where(['root_id' => $root_id, 'source' => '飞鱼线索'])->value('id'); $customer_data = Customer::where([['source_id', '=', $source_id], ['id', 'in', $customer_id]])->field('id,deposit_money as deposit_moneys,signed_money as signed_moneys')->select()->toArray(); $fish_count = $fish_money = 0; foreach ($customer_data as $val) { $fish_count++; if (isset($val['signed_moneys']) && $val['signed_moneys']) { $fish_money += $val['signed_moneys']; } if (!isset($val['signed_moneys']) && isset($val['deposit_moneys'])) { $fish_money += $val['deposit_moneys']; } } $fish_count = count($customer_id); //腾讯数据 $tx_where[] = ['type', '=', 2]; $tx_where[] = ['root_id', '=', $root_id]; $tx_where[] = ['data_time', 'between', $time]; $tx_data = FishCampaign::where($tx_where)->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts")->find(); $tx_customer_id = FishData::where([['type', '=', 2], ['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1]])->column('customer_id'); //查询有效线索 $tx_source_id = CustomerSource::where(['root_id' => $root_id, 'source' => '腾讯线索'])->value('id'); $tx_customer_data = Customer::where([['source_id', '=', $tx_source_id], ['id', 'in', $tx_customer_id]])->field('id,deposit_money as deposit_moneys,signed_money as signed_moneys')->select()->toArray(); $tx_count = $tx_money = 0; foreach ($tx_customer_data as $val) { $tx_count++; if (isset($val['signed_moneys']) && $val['signed_moneys']) { $tx_money += $val['signed_moneys']; } if (!isset($val['signed_moneys']) && isset($val['deposit_moneys'])) { $tx_money += $val['deposit_moneys']; } } $info = [ //飞鱼转化成本 = 总消耗 / 转商机线索量 //飞鱼总投放金额 'fish_cost' => $data['cost']??0, //飞鱼展示数 'fish_show' => $data['shows']??0, //飞鱼点击数 'fish_click' => $data['click']??0, //飞鱼点击率 'fish_click_lv' => $data['click'] > 0 && $data['shows'] > 0 ? round($data['click'] / $data['shows'] * 100, 2) : 0, //飞鱼转化数 'fish_convert' => $toufang_clue_count??0, //飞鱼转化率 'fish_convert_lv' => $toufang_clue_count > 0 && $data['click'] > 0 ? round($toufang_clue_count / $data['click'] * 100, 2) : 0, //飞鱼有效数 'fish_count' => $fish_count, //飞鱼有效率 'fish_count_lv' => $fish_count > 0 && $toufang_clue_count > 0 ? round($fish_count / $toufang_clue_count * 100, 2) : 0, //飞鱼广告线索成本 = 广告总消耗 / 广告线索量 'fish_convert_cost' => $toufang_clue_count > 0 ? round($data['cost'] / $toufang_clue_count, 2) : 0, //飞鱼产值 'fish_signed_money' => $fish_money, ]; //腾讯数据 $tx = [ 'fish_cost' => $tx_data['cost'] ?? 0, 'fish_show' => $tx_data['shows'] ?? 0, 'fish_click' => $tx_data['click'] ?? 0, 'fish_click_lv' => $tx_data['click'] > 0 && $tx_data['shows'] > 0 ? round($tx_data['click'] / $tx_data['shows'] * 100, 2) : 0, 'fish_convert' => $tx_clue_count ?? 0, 'fish_convert_lv' => $tx_clue_count > 0 && $tx_data['click'] > 0 ? round($tx_clue_count / $tx_data['click'] * 100, 2) : 0, 'fish_count' => $tx_count, 'fish_count_lv' => $tx_count > 0 && $tx_clue_count > 0 ? round($tx_count / $tx_clue_count * 100, 2) : 0, 'fish_convert_cost' => $tx_clue_count > 0 ? round($tx_data['cost'] / $tx_clue_count, 2) : 0, 'fish_signed_money' => $tx_money, ]; //总数据-飞鱼数据+腾讯数据+欧派数据 $cost = bcadd($info['fish_cost'], $tx['fish_cost'],2); $show = bcadd($info['fish_show'], $tx['fish_show'],0); $click = bcadd($info['fish_click'], $tx['fish_click'],0); $converts = bcadd($info['fish_convert'], $tx['fish_convert'],0); $customer_data_count = bcadd($info['fish_count'], $tx['fish_count'],0); $customer_data_signed_money = bcadd($fish_money, $tx_money,2); //$info['fish_signed_money'] + $tx['fish_signed_money']; $all = [ //总投放金额 'cost' => $cost, //展示数 'shows' => $show, //点击数 'click' => $click, //点击率 'click_lv' => $click > 0 && $show > 0? round($click / $show * 100, 2) : 0, //总转化数 'converts' => $converts, //转化率 'convert_lv' => $converts > 0 && $click > 0 ? round($converts / $click * 100, 2) : 0, //有效线索 'count' => $customer_data_count, //有效率 'count_lv' => $customer_data_count > 0 && $converts > 0 ? round($customer_data_count / $converts * 100, 2) : 0, //获客成本 'convert_cost' => $converts > 0 ? round($cost / $converts, 2) : 0, //产值 'signed_money' => $customer_data_signed_money, ]; Cache::set($cache_data_name,json_encode(['all' => $all, 'fish' => $info, 'tx' => $tx]), $this->cache_time); return json(['code' => 0, 'data' => ['all' => $all, 'fish' => $info, 'tx' => $tx], 'msg' => '获取成功']); } /** * 获取企业设置的广告主 */ public function get_company_channel_setting() { $root_id = $this->aec->decrypt($this->token); $channel_setting = Company::where(['root_id' => $root_id])->value('channel_setting'); $data = [ 'root_id' => $root_id, 'fish_adv' => '', 'tx_adv' => '', ]; if (isset($channel_setting) && $channel_setting) { $tmp_data = json_decode($channel_setting, true); $data['fish_adv'] = $tmp_data['fish_adv']; $data['tx_adv'] = $tmp_data['tx_adv']; } return json(['code' => 0, 'data' => $data, 'msg' => '获取成功']); } /** * 更新企业设置的广告主 */ public function set_company_channel_setting() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['fish_adv', 'tx_adv']); $channel_setting = Company::where(['root_id' => $root_id])->value('channel_setting'); if (isset($channel_setting) && $channel_setting) { $tmp_data = json_decode($channel_setting, true); $tmp_data['fish_adv'] = $param['fish_adv']; $tmp_data['tx_adv'] = $param['tx_adv']; Company::where(['root_id' => $root_id])->update(['channel_setting' => json_encode($tmp_data)]); } return json(['code' => 0, 'msg' => '更新成功']); } /** * 投放大屏-获取飞鱼的广告组下的详细广告计划 */ public function get_fish_campaign_list() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times', 'campaign_id']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_fish_campaign_list-' . $time[0] . '-' . $time[1] . '_' . $root_id . '_' . $param['campaign_id']; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } //如果是今天则查最新的接口数据 $wheres[] = ['type', 'in', [1,4]]; $wheres[] = ['root_id', '=', $root_id]; $wheres[] = ['campaign_id', '=', $param['campaign_id']]; $wheres[] = ['data_time', 'between', $time]; $list = FishCampaignList::where($wheres)->field('ad_name,cost,show,click,avg_click_cost,ctr,convert,convert_cost,convert_rate,data_time')->order('id desc')->select(); Cache::set($cache_data_name,json_encode($list), $this->cache_time); return json(['code' => 0, 'data' => $list, 'msg' => '获取成功']); } /** * 投放大屏-获取腾讯的广告组下的详细广告计划 */ public function get_tx_campaign_list() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times', 'campaign_id']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_tx_campaign_list-' . $time[0] . '-' . $time[1] . '_' . $root_id . '_' . $param['campaign_id']; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } //如果是今天则查最新的接口数据 $wheres[] = ['type', '=', 2]; $wheres[] = ['root_id', '=', $root_id]; $wheres[] = ['campaign_id', '=', $param['campaign_id']]; $wheres[] = ['data_time', 'between', $time]; $list = FishCampaignList::where($wheres)->field('ad_name,cost,show,click,avg_click_cost,ctr,convert,convert_cost,convert_rate,data_time')->order('id desc')->select(); Cache::set($cache_data_name,json_encode($list), $this->cache_time); return json(['code' => 0, 'data' => $list, 'msg' => '获取成功']); } /** * 刷新token */ public function refreshToken($tmp_data, $root_id) { $fish_refresh_time = $tmp_data['fish_refresh_time']; //上次token更新时间 if ($fish_refresh_time + 80000 < time()) { $token = $this->fish->getRefreshToken($tmp_data['fish_refresh_token']); if ($token['code'] == 0) { $tmp_data['fish_access_token'] = $token['data']['access_token']; $tmp_data['fish_refresh_token'] = $token['data']['refresh_token']; $tmp_data['fish_refresh_time'] = time(); Company::where(['root_id' => $root_id])->update(['channel_setting' => json_encode($tmp_data)]); } else { $tmp_data['fish_access_token'] = ''; $tmp_data['fish_refresh_token'] = ''; Company::where(['root_id' => $root_id])->update(['channel_setting' => json_encode($tmp_data)]); } } return $tmp_data; } /** * 刷新token */ public function refreshToken2($tmp_data, $root_id) { $fish_refresh_time = $tmp_data['fish_refresh_time']; //上次token更新时间 if ($fish_refresh_time + 80000 < time()) { $token = $this->fish_wh->getRefreshToken($tmp_data['fish_refresh_token']); if ($token['code'] == 0) { $tmp_data['fish_access_token'] = $token['data']['access_token']; $tmp_data['fish_refresh_token'] = $token['data']['refresh_token']; $tmp_data['fish_refresh_time'] = time(); Company::where(['root_id' => $root_id])->update(['channel_setting_wh' => json_encode($tmp_data)]); } else { $tmp_data['fish_access_token'] = ''; $tmp_data['fish_refresh_token'] = ''; Company::where(['root_id' => $root_id])->update(['channel_setting_wh' => json_encode($tmp_data)]); } } return $tmp_data; } /** * 转化大屏部门排行统计 */ public function org_statistics() { if (empty($this->token)) return json(['code' => 0, 'msg' => 'Token失效']); $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); // if (empty($param['times'])) return json(['code' => 1, 'msg' => '时间参数错误']); $orgids = []; foreach (orgSubIds($root_id) as $key => $val) { if (!Org::where([['pid', '=', $val], ['status', '=', 1]])->value('id')) { $orgids[] = $val; } } //$orgids = orgSubIds($root_id); $orglist = Org::with(['employee' => function ($query) { $query->where([['state', '=', '在职'], ['uid', '<>', 0]])->field('id,org_id'); }])->where([['id', 'in', orgSubIds($root_id)],['org_type','=',1],['id','not in',[2471,2320,2321,2322]]])->field('id,name,pid')->select()->toArray(); $orgids = array_column($orglist,'id'); $where[] = ['org_id', 'in', $orgids]; //var_dump($orglist);exit; if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } else { $start = date('Y-m') . '-01'; $end = date('Y-m-d', strtotime("$start +1 month -1 day")); $time = [$start, $end]; } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } $where[] = ['sign_time', 'between', $time]; //查询企业是否有飞鱼跟腾讯的来源id $tx_customer_id = FishData::where([['type', 'in', [1, 2, 4]], ['root_id', '=', $root_id], ['create_time_detail', 'between', $time]])->column('customer_id'); $sourceids = CustomerSource::where([['root_id', '=', $root_id], ['source', 'in', ['飞鱼线索', '腾讯线索', '欧派线索']]])->column('id'); if (empty($sourceids)) return json(['code' => 0, 'data' => '', 'msg' => '获取成功']); $state1 = Customer::changeState('待确认', 'chaos'); $state2 = Customer::changeState('无效', 'chaos'); $state3 = CustomerVisitLog::changeState('已交定', 'chaos'); $state4 = CustomerVisitLog::changeState('已签单', 'chaos'); $state5 = CustomerVisitLog::changeState('确认到店', 'chaos'); $state6 = CustomerVisitLog::changeState('已到场', 'chaos'); $state7 = CustomerVisitLog::changeState('已量房', 'chaos'); //$where[] = ['source_id', 'in', $sourceids]; $where[] = ['id', 'in', $tx_customer_id]; //$where[] = ['state','not in',array_merge($state1,$state2)]; //查询缓存值是否过期 $ceche_data = Cache::get("org_statistics_data_root_id_1_{$root_id}_".$time[0].$time[1]); if (empty($ceche_data)) { $cuslist = Customer::where($where)->column('id,org_id,state,add_wechat_time'); $wherev[] = ['CustomerVisitLog.state', 'in', array_merge($state3, $state4, $state5, $state6, $state7)]; $wherev[] = ['CustomerVisitLog.confirm_date', 'between', $time]; $wherev[] = ['CustomerVisitLog.customer_org_id', 'in', $orgids]; $w = Customer::where([['source_id', 'in', $sourceids],['id', 'in', $tx_customer_id]]); $visitlog_list = CustomerVisitLog::hasWhere('customer', $w)->where($wherev) ->group('state,customer_id') ->column('CustomerVisitLog.customer_id,CustomerVisitLog.state,CustomerVisitLog.money,CustomerVisitLog.customer_org_id'); $signids = $metids = []; $all_assign_cont = $all_valid_percen = $all_clue_cont = $all_jv = $all_meet = $all_deposit = $all_sign = 0; foreach ($visitlog_list as $key => $val) { if (in_array($val['state'], array_merge($state5, $state6, $state7))) $metids[] = $val['customer_id']; if (in_array($val['state'],$state4)) $signids[] = $val['customer_id']; $newvslog[$val['customer_org_id']][] = $val; if (in_array($val['state'], $state3, true)) $all_deposit++; if (in_array($val['state'], $state4, true)) $all_sign++; } $yesids = !empty(array_unique($metids)) ? $this->selBeforelog(array_unique($metids), $time[0] . ' 00:00:00') : []; $all_meet = count($yesids); $wx_where[] = ['root_id', '=', $root_id]; $wx_where[] = ['org_id', 'in', $orgids]; $wx_where[] = ['cus_addtime', 'between', $time]; $wx_where[] = ['source_id', 'in', $sourceids]; $wx_where[] = ['customer_id', 'in', $tx_customer_id]; $wuxiaolog_list = CustomerInvalidLog::where($wx_where)->column('customer_id,org_id,status'); //获取所有的有效id $jvids = $validids = []; foreach ($cuslist as $key => $val) { $all_clue_cont++; if (!in_array($val['state'], array_merge($state1, $state2), true)) { $validids[] = $val['id']; } if (!in_array($val['state'],$state2) && $val['add_wechat_time'] >= $time[0] && $val['add_wechat_time'] <= $time[1]){ $all_jv++; $jvids[] = $val['id']; } } trace(implode(',',$yesids),'infos'); $cusids = array_column($cuslist, 'id'); //trace(implode(',',$cusids),'infos1'); $wxids = array_column($wuxiaolog_list, 'customer_id'); $wcha = array_diff($wxids, $cusids); if (!empty($wcha)) $all_clue_cont += count($wcha); $all_assign_cont = FishData::where([['org_id', 'in', $orgids], ['create_time_detail', 'between', $time]])->count(); $all_valid_percen = !empty($validids) && !empty($all_assign_cont) ? round(count($validids) / $all_assign_cont * 100, 2) : 0; $all = ['all_clue_cont' => $all_assign_cont, 'all_jv' => $all_jv, 'all_meet' => $all_meet, 'all_deposit' => $all_deposit, 'all_sign' => $all_sign, 'all_valid_percen' => $all_valid_percen]; $bmp = $meetids = []; foreach ($orglist as $key => $val) { $dd = $meas_house = $valid = $clue_cont = $meet = $deposit = $sign = $basic_money = 0; foreach ($cuslist as $k => $v) { if ($v['org_id'] == $val['id'] && !in_array($v['state'], array_merge($state1, $state2))) { $valid++; } if ($v['org_id'] == $val['id']) $clue_cont++; } //处理无效的客户还原到员工身上为有效 foreach ($wuxiaolog_list as $k => $v) { // if ($v['org_id'] == $val['id'] && !in_array($v['customer_id'], $validids) && $v['status'] == 1) { // $valid++; // $validids[] = $v['customer_id']; // } if ($v['org_id'] == $val['id'] && !in_array($v['customer_id'], $cusids)) { $clue_cont++; $cusids[] = $v['customer_id']; } } if (!empty($newvslog[$val['id']])) { foreach ($newvslog[$val['id']] as $k => $v) { if (in_array($v['state'], array_merge($state5, $state6, $state7), true) && !in_array($v['customer_id'], $meetids) && in_array($v['customer_id'], $yesids)) { $meet++; $meetids[] = $v['customer_id']; } if (in_array($v['state'], $state5, true)) $dd++; if (in_array($v['state'], $state3, true)){ $deposit++; if(!in_array($v['customer_id'],$signids)) $basic_money += $v['money']; } if (in_array($v['state'], $state7, true)) $meas_house++; if (in_array($v['state'], $state4, true)) { $basic_money += $v['money']; $sign++; } } unset($newvslog[$val['id']]); } //unset($cusids,$depositids,$wuxiao); $assign_cont = FishData::where([['org_id', '=', $val['id']], ['create_time_detail', 'between', $time]])->count(); $orglist[$key]['assign_cont'] = $assign_cont; $orglist[$key]['valid_cont'] = $valid; $orglist[$key]['valid_percen'] = $valid && $assign_cont ? round($valid / $assign_cont * 100, 2) : 0; $orglist[$key]['meas_house'] = $meas_house; $orglist[$key]['meas_house_percen'] = !empty($meas_house) && $valid ? round($meas_house / $valid * 100, 2) : 0; $orglist[$key]['dd_cont'] = $dd; $orglist[$key]['dd_percen'] = !empty($dd) && !empty($valid) ? round($dd / $valid * 100, 2) : 0; $orglist[$key]['deposit_cont'] = $deposit; $orglist[$key]['deposit_percen'] = !empty($deposit) && !empty($meet) ? round($deposit / $meet * 100, 2) : 0; $orglist[$key]['sign_cont'] = $sign; $orglist[$key]['sign_percen'] = !empty($sign) && !empty($meet) ? round($sign / $meet * 100, 2) : 0; $orglist[$key]['basic_money'] = $basic_money; $orglist[$key]['sign_agv_money'] = !empty($sign) && !empty($basic_money) ? floor($basic_money / $sign) : 0; $bmp[] = $basic_money; } if (!empty($orglist)) { array_multisort($bmp, SORT_DESC, $orglist); } $data = ['orglist' => $orglist, 'all' => $all]; Cache::set('org_statistics_data_'.$time[0].$time[1].$root_id, json_encode($data), $this->cache_time); } else { $data = json_decode($ceche_data); } return json(['code' => 0, 'data' => empty($data)?[]:$data, 'msg' => '获取成功']); } //处理查询客户之前的见面记录 public function selBeforelog($metids, $firstday) { $statday = '2021-12-01 00:00:00'; $state1 = CustomerVisitLog::changeState('确认到店', 'chaos'); $state2 = CustomerVisitLog::changeState('已到场', 'chaos'); $state3 = CustomerVisitLog::changeState('已量房', 'chaos'); $q_where[] = ['customer_id', 'in', $metids]; $q_where[] = ['state', 'in', array_merge($state1, $state2, $state3)]; $q_where[] = ['addtime', 'between', [$statday, $firstday]]; $qianvslog = CustomerVisitLog::where($q_where)->group('customer_id')->column('customer_id'); $yesids = array_diff($metids, $qianvslog); return $yesids; } /** * 转化大屏部门员工数据统计 */ public function org_emp_statistics() { if (empty($this->token)) return json(['code' => 0, 'msg' => 'Token失效']); $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times','org_id']); if (empty($param['times'])) return json(['code' => 1, 'msg' => '时间参数错误']); if (empty($param['org_id'])) return json(['code' => 1, 'msg' => '部门参数错误']); //$team_orgs = orgSubIds($param['org_id']); $emplist = Employee::where([['org_id', '=', $param['org_id']], ['state', '=', '在职'], ['uid', '>', 0]])->column('id,name'); $empids = array_column($emplist,'id'); $where[] = ['employee_id','in',$empids]; //var_dump($orglist);exit; if (!empty($param['times'])) { $time = explode(' - ', $param['times']); $where[] = ['addtime', 'between', [$time[0].' 00:00:00', $time[1].' 23:59:59']]; } //查询企业是否有飞鱼跟腾讯的来源id $tx_customer_id = FishData::where([['type', 'in', [1,2,4]], ['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1]])->column('customer_id'); $sourceids = CustomerSource::where([['root_id', '=', $root_id], ['source', 'in', ['飞鱼线索', '腾讯线索', '欧派线索']]])->column('id'); if (empty($sourceids)) return json(['code' => 0, 'data' => '', 'msg' => '获取成功']); $state1 = Customer::changeState('待确认', 'chaos'); $state2 = Customer::changeState('无效', 'chaos'); $state3 = CustomerVisitLog::changeState('已交定', 'chaos'); $state4 = CustomerVisitLog::changeState('已签单', 'chaos'); $state5 = CustomerVisitLog::changeState('确认到店', 'chaos'); $state6 = CustomerVisitLog::changeState('已到场', 'chaos'); $state7 = CustomerVisitLog::changeState('已量房', 'chaos'); $where[] = ['source_id', 'in', $sourceids]; $where[] = ['id','in',$tx_customer_id]; //$where[] = ['state','not in',array_merge($state1,$state2)]; //查询缓存值是否过期 $ceche_data = Cache::get("org_emp_statistics_data_{$root_id}_".$time[0].$time[1].$param['org_id']); //$ceche_data = null; if (empty($ceche_data)) { $cuslist = Customer::where($where)->column('id,employee_id,state,add_wechat_time'); $wherev[] = ['CustomerVisitLog.state', 'in', array_merge($state3, $state4, $state5, $state6, $state7)]; $wherev[] = ['CustomerVisitLog.confirm_date', 'between', [$time[0], $time[1]]]; $wherev[] = ['CustomerVisitLog.customer_employee_id', 'in', $empids]; $w = Customer::where([['source_id', 'in', $sourceids],['id','in',$tx_customer_id]]); $visitlog_list = CustomerVisitLog::hasWhere('customer', $w)->where($wherev) ->group('state,customer_id') ->column('CustomerVisitLog.customer_id,CustomerVisitLog.state,CustomerVisitLog.money,CustomerVisitLog.customer_employee_id'); $metids = $signids = []; foreach ($visitlog_list as $key => $val) { if (in_array($val['state'], array_merge($state5, $state6, $state7))) $metids[] = $val['customer_id']; if (in_array($val['state'],$state4)) $signids[] = $val['customer_id']; $newvslog[$val['customer_employee_id']][] = $val; } $yesids = !empty(array_unique($metids)) ? $this->selBeforelog(array_unique($metids), $time[0] . ' 00:00:00') : []; $wx_where[] = ['root_id', '=', $root_id]; $wx_where[] = ['employee_id', 'in', $empids]; $wx_where[] = ['cus_addtime', 'between', [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']]; $wx_where[] = ['source_id', 'in', $sourceids]; $wx_where[] = ['customer_id', 'in', $tx_customer_id]; $wuxiaolog_list = CustomerInvalidLog::where($wx_where)->column('customer_id,employee_id,status'); //获取所有的有效id $validids = []; foreach ($cuslist as $key => $val) { if (!in_array($val['state'], array_merge($state1, $state2), true)) { $validids[] = $val['id']; } } $cusids = array_column($cuslist, 'id'); $wxids = array_column($wuxiaolog_list, 'customer_id'); $wcha = array_diff($wxids, $cusids); $bmp = $meetids = []; foreach ($emplist as $key => $val) { $dd = $meas_house = $valid = $clue_cont = $meet = $deposit = $sign = $basic_money = 0; foreach ($cuslist as $k => $v) { if ($v['employee_id'] == $val['id'] && !in_array($v['state'], array_merge($state1, $state2))) { $valid++; } if ($v['employee_id'] == $val['id']) $clue_cont++; } //处理无效的客户还原到员工身上为有效 foreach ($wuxiaolog_list as $k => $v) { // if ($v['employee_id'] == $val['id'] && !in_array($v['customer_id'], $validids) && $v['status'] == 1) { // $valid++; // $validids[] = $v['customer_id']; // } if ($v['employee_id'] == $val['id'] && !in_array($v['customer_id'], $cusids)) { $clue_cont++; $cusids[] = $v['customer_id']; } } if (!empty($newvslog[$val['id']])) { foreach ($newvslog[$val['id']] as $k => $v) { if (in_array($v['state'], array_merge($state5, $state6, $state7), true) && !in_array($v['customer_id'], $meetids) && in_array($v['customer_id'], $yesids)) { $meet++; $meetids[] = $v['customer_id']; } if (in_array($v['state'], $state5, true)) $dd++; if (in_array($v['state'], $state3, true)){ $deposit++; if(!in_array($v['customer_id'],$signids)) $basic_money += $v['money']; } if (in_array($v['state'], $state7, true)) $meas_house++; if (in_array($v['state'], $state4, true)) { $basic_money += $v['money']; $sign++; } } unset($newvslog[$val['id']]); } //unset($cusids,$depositids,$wuxiao); $assign_cont = FishData::where([['employee_id', '=', $val['id']], ['create_time_detail', 'between', [$time[0], $time[1]]]])->count(); $emplist[$key]['assign_cont'] = $assign_cont; $emplist[$key]['valid_cont'] = $valid; $emplist[$key]['valid_percen'] = $valid && $assign_cont ? round($valid / $assign_cont * 100, 2) : 0; $emplist[$key]['meas_house'] = $meas_house; $emplist[$key]['meas_house_percen'] = !empty($meas_house) && $valid ? round($meas_house / $valid * 100, 2) : 0; $emplist[$key]['dd_cont'] = $dd; $emplist[$key]['dd_percen'] = !empty($dd) && !empty($valid) ? round($dd / $valid * 100, 2) : 0; $emplist[$key]['deposit_cont'] = $deposit; $emplist[$key]['deposit_percen'] = !empty($deposit) && !empty($meet) ? round($deposit / $meet * 100, 2) : 0; $emplist[$key]['sign_cont'] = $sign; $emplist[$key]['sign_percen'] = !empty($sign) && !empty($meet) ? round($sign / $meet * 100, 2) : 0; $emplist[$key]['basic_money'] = $basic_money; $emplist[$key]['sign_agv_money'] = !empty($sign) && !empty($basic_money) ? floor($basic_money / $sign) : 0; $bmp[] = $basic_money; } if (!empty($emplist)) { array_multisort($bmp, SORT_DESC, $emplist); } Cache::set('org_emp_statistics_data_'.$time[0].$time[1].$param['org_id'], json_encode($emplist), $this->cache_time); } else { $emplist = json_decode($ceche_data); } return json(['code' => 0, 'data' => $emplist, 'msg' => '获取成功']); } /** * 转化大屏拆线图统计 */ public function clearing_statistics() { if (empty($this->token)) return json(['code' => 0, 'msg' => 'Token失效']); $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $orgids = Org::where([['id','in',orgSubIds($root_id)],['org_type','=',1]])->column('id'); if (empty($param['times'])) return json(['code' => 1, 'msg' => '时间参数错误']); $time = explode(' - ', $param['times']); $cha = (strtotime($time[1]) - strtotime($time[0])) / 86400; if ($cha > 35) $type = 'month'; if ($cha <= 35) $type = 'day'; $list = getDateByInterval($time[0], $time[1], $type); if ($type == 'day') { foreach ($list as $key => $val) { $new[] = ['name' => $val, 'startDate' => $val . ' 00:00:00', 'endDate' => $val . ' 23:59:59']; } $list = $new; } //查询企业是否有飞鱼跟腾讯的来源id $tx_customer_id = FishData::where([['type', 'in', [1,2,4]], ['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1]])->column('customer_id'); $sourceids = CustomerSource::where([['root_id', '=', $root_id], ['source', 'in', ['飞鱼线索', '腾讯线索', '欧派线索']]])->column('id'); if (empty($sourceids)) return json(['code' => 0, 'data' => '', 'msg' => '获取成功']); $cache_data = Cache::get('clearing_statistics_data_'.$time[0].$time[1].$root_id); if (empty($cache_data)) { $state1 = Customer::changeState('待确认', 'chaos'); $state2 = Customer::changeState('无效', 'chaos'); $state3 = CustomerVisitLog::changeState('已交定', 'chaos'); $state4 = CustomerVisitLog::changeState('已签单', 'chaos'); $state5 = CustomerVisitLog::changeState('确认到店', 'chaos'); $state6 = CustomerVisitLog::changeState('已到场', 'chaos'); $state7 = CustomerVisitLog::changeState('已量房', 'chaos'); $where[] = ['addtime', 'between', [$time[0], $time[1]]]; $where[] = ['org_id', 'in', $orgids]; $where[] = ['source_id', 'in', $sourceids]; $where[] = ['id','in',$tx_customer_id]; //$where[] = ['state', 'not in', array_merge($state1, $state2)]; $cuslist = Customer::where($where)->column('id,addtime,state,org_id'); //获取所有有效的客户id $validids = []; foreach($cuslist as $key=>$val){ if(!in_array($val['state'],array_merge($state1,$state2))) $validids[] = $val['id']; } $wherev[] = ['CustomerVisitLog.state', 'in', array_merge($state3, $state4, $state5, $state6, $state7)]; $wherev[] = ['CustomerVisitLog.confirm_date', 'between', [$time[0], $time[1]]]; $wherev[] = ['CustomerVisitLog.customer_org_id', 'in', $orgids]; $w = Customer::where([['source_id', 'in', $sourceids],['id','in',$tx_customer_id]]); $visitlog_list = CustomerVisitLog::hasWhere('customer', $w)->where($wherev) ->group('state,customer_id') ->column('CustomerVisitLog.customer_id,CustomerVisitLog.state,CustomerVisitLog.money,CustomerVisitLog.customer_org_id,CustomerVisitLog.confirm_date'); $metids = []; foreach ($visitlog_list as $key => $val) { if (in_array($val['state'], array_merge($state5, $state6, $state7))) $metids[] = $val['customer_id']; } $yesids = !empty(array_unique($metids)) ? $this->selBeforelog(array_unique($metids), $time[0] . ' 00:00:00') : []; $wx_where[] = ['root_id', '=', $root_id]; $wx_where[] = ['org_id', 'in', $orgids]; $wx_where[] = ['cus_addtime', 'between', [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']]; $wx_where[] = ['source_id', 'in', $sourceids]; $wx_where[] = ['status', '=', 1]; $wx_where[] = ['customer_id', 'in', $tx_customer_id]; $wuxiaolog_list = CustomerInvalidLog::where($wx_where)->column('customer_id,cus_addtime'); //$cluelist = FishData::where([['root_id', '=', $root_id]])->column('create_time_detail'); foreach ($list as $key => $val) { $clue_cont = $valid = $meet = $sign = $basic_money = 0; $meetids = []; foreach ($cuslist as $k => $v) { if ($v['addtime'] >= $val['startDate'] && $v['addtime'] <= $val['endDate']){ if(!in_array($v['state'],array_merge($state1,$state2))) $valid++; $clue_cont++; } } foreach ($wuxiaolog_list as $k => $v) { if ($v['cus_addtime'] >= $val['startDate'] && $v['cus_addtime'] <= $val['endDate'] && !in_array($v['customer_id'],$validids)) $valid++; } // foreach ($cluelist as $k => $v) { // if ($v >= $val['startDate'] && $v <= $val['endDate']) $clue_cont++; // } foreach ($visitlog_list as $k => $v) { if ($v['confirm_date'] >= $val['startDate'] && $v['confirm_date'] <= $val['endDate']) { if (in_array($v['state'], $state4, true)) { $sign++; $basic_money += $v['money']; } if (in_array($v['state'], array_merge($state5, $state6, $state7)) && !in_array($v['customer_id'], $meetids) && in_array($v['customer_id'], $yesids)) { $meet++; $meetids[] = $v['customer_id']; } } } $assign_cont = FishData::where([['root_id', '=', $root_id], ['create_time_detail', 'between', [$val['startDate'], $val['endDate']]]])->count(); $list[$key]['valid_percen'] = $valid && !empty($assign_cont) ? round($valid / $assign_cont * 100, 2) : 0; $list[$key]['meet_percen'] = !empty($meet) && !empty($clue_cont) ? round($meet / $clue_cont * 100, 2) : 0; $list[$key]['sign_percen'] = !empty($sign) && !empty($meet) ? round($sign / $meet * 100, 2) : 0; } Cache::set('clearing_statistics_data_'.$time[0].$time[1].$root_id, json_encode($list), $this->cache_time); } else { $list = json_decode($cache_data, true); } return json(['code' => 0, 'data' => empty($list)?[]:$list, 'msg' => '获取成功']); } /** * 社群数据大屏-楼盘交定签单列表 */ public function community_depsig_list() { if (empty($this->token)) return json(['code' => 0, 'msg' => 'Token失效']); $root_id = $this->aec->decrypt($this->token); //$root_id = 994; $param = request()->only(['times']); $time = [date('2023-04-01'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } // if (empty($param['times'])) return json(['code' => 1, 'msg' => '时间参数错误']); // $time = explode(' - ', $param['times']); $state1 = CustomerVisitLog::changeState('已到店','chaos'); $state2 = CustomerVisitLog::changeState('已交定','chaos'); $state3 = CustomerVisitLog::changeState('已签单','chaos'); $state4 = CustomerVisitLog::changeState('已量房','chaos'); $state5 = CustomerVisitLog::changeState('已到场','chaos'); $cache_data = Cache::get('community_depsig_list_'.$time[0].$time[1].$root_id); $cache_data = null; if (empty($cache_data)) { $building_where[] = ['del', '=', 0]; $building_where[] = ['root_id', '=', $root_id]; $community_id = BuildingModel::where($building_where)->group('community_id')->column('community_id'); //小区列表 $community_list = Community::where([['root_id','=',$root_id],['show','=',0],['id', 'in', $community_id]])->column('id,name,households'); //社群员工 $sqempids = Employee::where([['root_id','=',$root_id],['state', '=', '在职'], ['uid', '>', 0],['community_disable','=',0]])->column('id'); //所有客户 //$where[] = ['addtime','between',[$time[0],$time[1]]]; $where[] = ['employee_id','in',$sqempids]; $where[] = ['community_name','in',array_column($community_list,'name')]; $cuslist = Customer::where($where)->column('id,community_name'); //加微的客户 //$jv_where[] = ['add_wechat_time','between',[$time[0].' 00:00:00',$time[1].' 23:59:59']]; $jv_where[] = ['add_wechat_time','NOTNULL',null]; $jv_where[] = ['employee_id','in',$sqempids]; $jv_where[] = ['community_name','in',array_column($community_list,'name')]; $jvcuslist = Customer::where($jv_where)->column('id,community_name'); //$jqcuslist = $kbcuslist = []; //进群的客户 //$jq_where[] = ['into_owner_group','between',[$time[0],$time[1]]]; $jq_where[] = ['into_owner_group','NOTNULL',null]; $jq_where[] = ['employee_id','in',$sqempids]; $jq_where[] = ['community_name','in',array_column($community_list,'name')]; $jqcuslist = Customer::where($jq_where)->column('id,community_name'); //口碑的客户 //$kb_where[] = ['reputation_gather','between',[$time[0],$time[1]]]; $kb_where[] = ['reputation_gather','NOTNULL',null]; $kb_where[] = ['employee_id','in',$sqempids]; $kb_where[] = ['community_name','in',array_column($community_list,'name')]; $kbcuslist = Customer::where($kb_where)->column('id,community_name'); //无效的客户 $wx_where[] = ['root_id','=',$root_id]; $wx_where[] = ['employee_id','in',$sqempids]; //$wx_where[] = ['cus_addtime', 'between', [$time[0].' 00:00:00',$time[1].' 23:59:59']]; $wuxiaoids = CustomerInvalidLog::where($wx_where)->column('customer_id'); $wxw = !empty($wuxiaoids) ? implode(',',$wuxiaoids) : []; $comnames = !empty($community_list) ? array_column($community_list,'name') : []; $comids = ''; if(!empty($comnames)){ foreach($comnames as $k=>$v){ $comids.="'".$v."',"; } $comids = trim($comids,','); } $wuxiaolog_list = !empty($wxw) ? $this->cus_recycle($wxw,$comids) : []; //所有跟进记录 $vs_where[] = ['CustomerVisitLog.state','in',array_merge($state1,$state2,$state3,$state4,$state5)]; //$vs_where[] = ['confirm_date', 'between', [$time[0].' 00:00:00',$time[1].' 23:59:59']]; $vs_where[] = ['customer_employee_id','in',$sqempids]; $w = Customer::where('community_name','in',array_column($community_list,'name')); $vsloglist = CustomerVisitLog::hasWhere('customer',$w) ->where($vs_where)->group('state,customer_id')->column('Customer.community_name,CustomerVisitLog.customer_id,CustomerVisitLog.state,CustomerVisitLog.money'); $signids = []; foreach($vsloglist as $key=>$val){ $newvslog[$val['community_name']][] = $val; if(in_array($val['state'],$state4)) $signids[] = $val['customer_id']; } $cusids = array_column($cuslist,'id'); $t=['all_households'=>0,'alljq'=>0,'allkb'=>0,'alllp'=>0,'alljv'=>0,'alldd'=>0,'allclue'=>0,'all_meas_house'=>0,'all_deposit'=>0,'all_trans_percen'=>0,'all_sign'=>0,'all_basic_money'=>0,'all_money'=>0]; foreach($community_list as $key=>$val){ $jq = $kb = $jv = $dd = $meas_house = $valid = $clue_cont = $deposit = $sign = $dep_money = $basic_money = 0; foreach ($cuslist as $k => $v) { if ($v['community_name'] == $val['name']) $clue_cont++; } //加微数量 foreach($jvcuslist as $k=>$v){ if ($v['community_name'] == $val['name']) $jv++; } //进群数量 foreach($jqcuslist as $k=>$v){ if ($v['community_name'] == $val['name']) $jq++; } //口碑数量 foreach($kbcuslist as $k=>$v){ if ($v['community_name'] == $val['name']) $kb++; } //处理无效的客户还原到员工身上为有效 foreach ($wuxiaolog_list as $k => $v) { if ($v['community_name'] == $val['name'] && !in_array($v['id'], $cusids)) { $clue_cont++; $cusids[] = $v['id']; if(!empty($v['add_wechat_time'])) $jv++; if(!empty($v['into_owner_group'])) $jq++; if(!empty($v['reputation_gather'])) $kb++; } } if (!empty($newvslog[$val['name']])) { foreach ($newvslog[$val['name']] as $k => $v) { if (in_array($v['state'], $state1, true)) $dd++; if (in_array($v['state'], $state2, true)){ $deposit++; if(!in_array($v['customer_id'],$signids)) $dep_money += $v['money']; } if (in_array($v['state'], $state4, true)) $meas_house++; if (in_array($v['state'], $state3, true)) { $basic_money += $v['money']; $sign++; } } unset($newvslog[$val['name']]); } $community_list[$key]['clue_cont'] = $clue_cont; $community_list[$key]['jq'] = $jq; $community_list[$key]['kb'] = $kb; $community_list[$key]['jv_cont'] = $jv; $community_list[$key]['jv_percen'] = $jv && $clue_cont ? round($jv / $clue_cont * 100, 2) : 0; $community_list[$key]['meas_house'] = $meas_house; $community_list[$key]['dd_cont'] = $dd; $community_list[$key]['dd_percen'] = !empty($dd) && !empty($valid) ? round($dd / $valid * 100, 2) : 0; $community_list[$key]['deposit_cont'] = $deposit; $community_list[$key]['trans_percen'] = !empty($deposit) && !empty($dd) ? round($deposit / $dd * 100, 2) : 0; $community_list[$key]['sign_cont'] = $sign; $community_list[$key]['basic_money'] = $basic_money; $bmp[] = $basic_money; //总数 $t['allclue'] += $clue_cont; $t['alljv'] += $jv; $t['alljq'] += $jq; $t['allkb'] += $kb; $t['alldd'] += $dd; $t['all_sign'] += $sign; $t['all_deposit'] += $deposit; $t['all_basic_money'] += $basic_money; $t['all_money'] += $basic_money+$dep_money; $t['all_meas_house'] += $meas_house; $t['all_households'] += $val['households']; } if (!empty($community_list)) { array_multisort($bmp, SORT_DESC, $community_list); } $t['alllp'] = count($community_list); //$t['allsq'] = count($sqempids); $t['jv_percen'] = !empty($t['alljv']) && !empty($t['allclue']) ? round($t['alljv'] / $t['allclue'] * 100, 2) : 0; $t['sign_agv_money'] = !empty($t['all_sign']) && !empty($all_basic_money) ? floor($all_basic_money / $sign) : 0; $t['all_trans_percen'] = !empty($t['all_deposit']) && !empty($t['alldd']) ? floor($t['all_deposit'] / $t['alldd']) : 0; $data = ['list'=>$community_list,'all'=>$t]; Cache::set('community_depsig_list_'.$time[0].$time[1].$root_id, json_encode($data), $this->cache_time); }else{ $data = json_decode($cache_data, true); } return json(['code' => 0, 'data' => $data, 'msg' => '获取成功']); } /** * 客户表加回收站查询 */ public function cus_recycle($condition,$comids) { $sql = "( SELECT id,name,community_name,add_wechat_time,into_owner_group,reputation_gather FROM `fl_customer` WHERE `id` in (".$condition.") and community_name in (".$comids.")) UNION ( SELECT customer_id as id,name,community_name,add_wechat_time,into_owner_group,reputation_gather FROM `fl_customer_recycle` WHERE `customer_id` in (".$condition.") and community_name in (".$comids.")) "; $data = Db::query($sql); return $data; } /** * 首页谈单记录 （开始 + 异常） * 三分钟之内的 */ public function get_index_smart_screen_log(){ $root_id = $this->aec->decrypt($this->token); $three = date('Y-m-d H:i:s', time() - 180); $where[] = ['root_id', '=', $root_id]; $where[] = ['addtime|endtime', '>', $three]; $list = SmartScreenLog::where($where)->select()->toArray(); $setting = Setting::where([['root_id', '=', $root_id], ['name', '=', 'smartScreenValidMinutes']])->findOrEmpty(); $valid_long = 0; if (!$setting->isEmpty()) { $valid_long = $setting['content'] * 60; } $tips = []; foreach ($list as $k => $v) { // 谈单异常判断 if ($v['endtime']) { if ($valid_long && $v['time'] < $valid_long) { // 异常结束 $tips[] = ['employee_id'=> $v['employee_id'], 'customer_id'=> $v['customer_id'], 'type'=> 'bad']; } } else { // 谈单正常判断 $tips[] = ['employee_id'=> $v['employee_id'], 'customer_id'=> $v['customer_id'], 'type'=> 'start']; } } $tips_list = []; foreach ($tips as $k => $v) { $employee = Employee::find($v['employee_id']); $customer = Customer::find($v['customer_id']); $employee_type = ''; $employee_name = ''; $customer_name = ''; if (!empty($employee)) { if ($employee['org_type'] == 2) { $employee_type = '设计师'; } else { $employee_type = '销售'; } $employee_name = $employee['name']; } if (!empty($customer)) { $customer_name = $customer['name']; } if ($v['type'] == 'bad') { $msg = '谈单异常'; } else { $msg = '开始谈单'; } $one = '[' . $customer_name . '客户已经到店 | ' . $employee_type . $employee_name . $msg . ']'; $tips_list[] = $one; } return json(['code'=> 0, 'data'=> $tips_list, 'msg'=> 'success']); } /** * 首页总业绩 */ public function get_index_total(){ $root_id = $this->aec->decrypt($this->token); $sub_orgs = orgSubIds($root_id); $state1 = CustomerVisitLog::changeState('已到店', 'chaos'); $state2 = CustomerVisitLog::changeState('已量房', 'chaos'); $state3 = CustomerVisitLog::changeState('已到场', 'chaos'); $state4 = CustomerVisitLog::changeState('已交定', 'chaos'); $state5 = CustomerVisitLog::changeState('已签单', 'chaos'); /*----- 投放转化 直播转化 -----*/ $f_where[] = ['FishData.root_id', '=', $root_id]; $f_where[] = ['FishData.type', 'in', [1, 2, 4]]; $f_customer_log = Db::view('FishData', ['type']) ->view('CustomerVisitLog', 'id,customer_id,state,customer_employee_id,confirm_date', 'CustomerVisitLog.customer_id=FishData.customer_id') ->view('Customer', 'deposit_money,signed_money', 'Customer.id=FishData.customer_id') ->where($f_where) ->select() ->toArray(); // 直播数据 $zhibo_data = [ 'clue'=> [], // 线索量 'visit'=> [], // 见面 'deposit'=> [], // 交定id 'signed'=> [], // 签单id 'signed_money'=> 0, // 签单金额 'total_money'=> 0 // 业绩总和 ]; // 投放数据 $put_data = [ 'clue'=> [], 'visit'=> [], 'deposit'=> [], 'signed'=> [], 'signed_money'=> 0, 'total_money'=> 0 ]; $visit_state = array_merge($state1, $state2, $state3, $state4, $state5); foreach ($f_customer_log as $k => $v){ // 投放 if (in_array($v['type'], [1, 2])) { // 线索量 if (!in_array($v['customer_id'], $put_data['clue'])) { $put_data['clue'][] = $v['customer_id']; } // 见面 if (in_array($v['state'], $visit_state, true) && !in_array($v['customer_id'], $put_data['visit'])) { $put_data['visit'][] = $v['customer_id']; } // 签单 if (in_array($v['state'], $state5, true) && !in_array($v['customer_id'], $put_data['signed'])) { $put_data['signed'][] = $v['customer_id']; $put_data['signed_money'] += $v['signed_money']; $put_data['total_money'] += $v['signed_money']; } } // 直播 if ($v['type'] == 4) { // 线索量 if (!in_array($v['customer_id'], $zhibo_data['clue'])) { $zhibo_data['clue'][] = $v['customer_id']; } // 见面 if (in_array($v['state'], $visit_state, true) && !in_array($v['customer_id'], $zhibo_data['visit'])) { $zhibo_data['visit'][] = $v['customer_id']; } // 签单 if (in_array($v['state'], $state5, true) && !in_array($v['customer_id'], $zhibo_data['signed'])) { $zhibo_data['signed'][] = $v['customer_id']; $zhibo_data['signed_money'] += $v['signed_money']; $zhibo_data['total_money'] += $v['signed_money']; } } } // 处理总业绩 签单 +交定（未签单） foreach ($f_customer_log as $k => $v){ // 投放 if (in_array($v['type'], [1, 2])) { // 交定 if (in_array($v['state'], $state4, true) && !in_array($v['customer_id'], $put_data['deposit'])) { $put_data['deposit'][] = $v['customer_id']; // 判断是否签单 if (!in_array($v['customer_id'], $put_data['signed'])) { $put_data['total_money'] += $v['deposit_money']; } } } // 直播 if ($v['type'] == 4) { // 交定 if (in_array($v['state'], $state4, true) && !in_array($v['customer_id'], $zhibo_data['deposit'])) { $zhibo_data['deposit'][] = $v['customer_id']; // 判断是否签单 if (!in_array($v['customer_id'], $zhibo_data['signed'])) { $zhibo_data['total_money'] += $v['deposit_money']; } } } } $put['clue'] = FishData::where([['root_id', '=', $root_id],['type', 'in', [1]]])->count(); //todo 直播计算 $zhibo_where[] = ['type', 'in', [4]]; $zhibo_where[] = ['root_id', '=', $root_id]; $zhibo_clue_count = FishData::where($zhibo_where)->count(); $tx_clue_where[] = ['type', 'in', [2]]; $tx_clue_where[] = ['root_id', '=', $root_id]; $tx_clue_count = FishData::where($tx_clue_where)->count(); $put['clue'] = $put['clue'] + $zhibo_clue_count+$tx_clue_count; $put['visit_count'] = count(array_unique(array_merge($put_data['visit'], $zhibo_data['visit']))); $put['signed_count'] = count($put_data['signed']) + count($zhibo_data['signed']); $put['signed_money'] = $put_data['signed_money'] + $zhibo_data['signed_money']; $put['total_money'] = $put_data['total_money'] + $zhibo_data['total_money']; $put['signed_average'] = $put['signed_count'] > 0 ? round($put['signed_money'] / $put['signed_count'], 0) : 0; $data['put'] = $put; $zhibo['clue'] = $zhibo_clue_count;//FishCampaign::where([['root_id', '=', $root_id],['type', '=', 4]])->sum('convert'); $zhibo['visit_count'] = count($zhibo_data['visit']); $zhibo['signed_count'] = count($zhibo_data['signed']); $zhibo['signed_money'] = $zhibo_data['signed_money']; $zhibo['total_money'] = $zhibo_data['total_money']; $zhibo['signed_average'] = $zhibo['signed_count'] > 0 ? round($zhibo['signed_money'] / $zhibo['signed_count'], 0) : 0; $data['broadcast'] = $zhibo; /*----- 投放转化 -----*/ /*----- 社群数据 -----*/ // 社群员工 $emp_list = Employee::where([['org_id', 'in', $sub_orgs], ['community_disable', '=', 0], ['state', '=', '在职'], ['uid', '>', 0]])->column('id'); $community_ids = BuildingModel::where([['root_id', '=', $root_id], ['del', '=', 0]])->column('community_id'); $community_list = Community::where('id', 'in', $community_ids)->column('name'); $she_where[] = ['Customer.community_name', 'in', $community_list]; $she_where[] = ['CustomerVisitLog.customer_employee_id', 'in', $emp_list]; $she_customer = Db::view('Customer') ->view('CustomerVisitLog', 'id', 'Customer.id=CustomerVisitLog.customer_id') ->where($she_where) ->column('Customer.id,Customer.signed_money,Customer.deposit_money,CustomerVisitLog.state'); // 回收站 $recycle_where[] = ['CustomerRecycle.community_name', 'in', $community_list]; $recycle_where[] = ['CustomerVisitLog.customer_employee_id', 'in', $emp_list]; $recycle_customer = Db::view('CustomerRecycle') ->view('CustomerVisitLog', 'id', 'CustomerRecycle.customer_id=CustomerVisitLog.customer_id') ->where($recycle_where) ->column('CustomerRecycle.customer_id id,CustomerRecycle.signed_money,CustomerRecycle.deposit_money,CustomerVisitLog.state'); // 自动回收库 $drop_where[] = ['CustomerDropPool.community_name', 'in', $community_list]; $drop_where[] = ['CustomerVisitLog.customer_employee_id', 'in', $emp_list]; $drop_customer = Db::view('CustomerDropPool') ->view('CustomerVisitLog', 'id', 'CustomerDropPool.customer_id=CustomerVisitLog.customer_id') ->where($drop_where) ->column('CustomerDropPool.customer_id id,CustomerDropPool.signed_money,CustomerDropPool.deposit_money,CustomerVisitLog.state'); $she_customer = array_merge($she_customer, $recycle_customer, $drop_customer); $she_data['clue'] = []; $she_data['signed'] = []; $she_data['deposit'] = []; $she_data['visit'] = []; $she_data['signed_money'] = 0; $she_data['total_money'] = 0; foreach ($she_customer as $k => $v) { if (!in_array($v['id'], $she_data['clue'])) { $she_data['clue'][] = $v['id']; } // 见面 if (!in_array($v['id'], $she_data['visit']) && in_array($v['state'], $visit_state, true)) { $she_data['visit'][] = $v['id']; } // 签单 if (in_array($v['state'], $state5, true) && !in_array($v['id'], $she_data['signed'])) { $she_data['signed'][] = $v['id']; $she_data['signed_money'] += $v['signed_money']; $she_data['total_money'] += $v['signed_money']; } } // 处理业绩 foreach ($she_customer as $k => $v) { // 交定 if (in_array($v['state'], $state4, true) && !in_array($v['id'], $she_data['deposit'])) { $she_data['deposit'][] = $v['id']; // 判断是否签单 if (!in_array($v['id'], $she_data['signed'])) { $she_data['total_money'] += $v['deposit_money']; } } } $group['clue'] = count($she_data['clue']); $group['visit_count'] = count($she_data['visit']); $group['signed_count'] = count($she_data['signed']); $group['signed_money'] = $she_data['signed_money']; $group['total_money'] = $she_data['total_money']; $group['signed_average'] = $group['signed_count'] > 0 ? round($group['signed_money'] / $group['signed_count'], 0) : 0; $data['group'] = $group; /*----- 社群数据 -----*/ $total['clue'] = $put['clue'] + $group['clue']; //+ $zhibo['clue']; $total['visit_count'] = $put['visit_count'] + $group['visit_count'];// + $zhibo['visit_count']; $total['signed_count'] = $put['signed_count'] + $group['signed_count'];// + $zhibo['signed_count']; $total['signed_money'] = $put['total_money'] + $group['total_money'];// + $zhibo['total_money']; $total_signed_money = $put['signed_money'] + $group['signed_money']; $total['signed_average'] = $total['signed_count'] > 0 ? round($total_signed_money / $total['signed_count'], 0) : 0; // 平均业绩 = 签单总额/签单数 $ratio_group = $total['clue'] > 0 ? round($group['clue'] / $total['clue'] * 100, 0) : 0; $ratio_put = $total['clue'] > 0 ? round($put['clue'] / $total['clue'] * 100, 0) : 0; $ratio_zhibo = $total['clue'] > 0 ? round($zhibo['clue'] / $total['clue'] * 100, 0) : 0; $total['ratio'] = [ 'group'=> $ratio_group, 'put'=> $ratio_put, 'zhibo'=> $ratio_zhibo ]; $data['total'] = $total; return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 首页右侧不同业绩类型的排行数据 */ public function get_index_performance(){ $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_performance-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } $sub_org = orgSubIds($root_id); // 客服 $state1 = CustomerVisitLog::changeState('已交定', 'chaos'); $state2 = CustomerVisitLog::changeState('已签单', 'chaos'); $where1[] = ['Employee.org_id', 'in', $sub_org]; $where1[] = ['Employee.uid', '>', 0]; $where1[] = ['Employee.state', '=', '在职']; $where1[] = ['CustomerVisitLog.state', 'in', array_merge($state1, $state2)]; $where1[] = ['CustomerVisitLog.confirm_date', 'between', $time]; $where1[] = ['FishData.type', 'in', [1, 2, 4]]; $list1 = Db::view('Employee') ->view('CustomerVisitLog', 'id lid', 'CustomerVisitLog.customer_employee_id=Employee.id') ->view('Customer', 'id cid', 'CustomerVisitLog.customer_id=Customer.id') ->view('FishData','customer_id','FishData.customer_id=Customer.id') ->where($where1) ->column('Customer.id,Customer.deposit_money,Customer.signed_money,Customer.employee_id,CustomerVisitLog.state,CustomerVisitLog.customer_employee_id,Employee.name employee_name,Employee.community_disable'); $employee_data = []; // 员工数据 $log1 = []; // 有效的数据 foreach ($list1 as $k => $v) { if ($v['employee_id'] == $v['customer_employee_id']) { // 防止交定之后又置为无效的，所以客户所属员工和跟踪记录创建时客户所属员工保持一致 if (empty($employee_data[$v['employee_id']])) { $employee_data[$v['employee_id']]['name'] = $v['employee_name']; $employee_data[$v['employee_id']]['community_disable'] = $v['community_disable']; $employee_data[$v['employee_id']]['signed'] = []; $employee_data[$v['employee_id']]['deposit'] = []; $employee_data[$v['employee_id']]['signed_money'] = 0; } $log1[] = $v; // 签单 if (in_array($v['state'], $state2, true) && !in_array($v['id'], $employee_data[$v['employee_id']]['signed'])) { $employee_data[$v['employee_id']]['signed'][] = $v['id']; $employee_data[$v['employee_id']]['signed_money'] += $v['signed_money']; } } } foreach ($log1 as $k => $v) { // 交定 if (empty($employee_data[$v['employee_id']])) { $employee_data[$v['employee_id']]['name'] = $v['employee_name']; $employee_data[$v['employee_id']]['community_disable'] = $v['community_disable']; $employee_data[$v['employee_id']]['signed'] = []; $employee_data[$v['employee_id']]['deposit'] = []; $employee_data[$v['employee_id']]['signed_money'] = 0; } if (in_array($v['state'], $state1, true) && !in_array($v['id'], $employee_data[$v['employee_id']]['deposit'])) { $employee_data[$v['employee_id']]['deposit'][] = $v['id']; if (!in_array($v['id'], $employee_data[$v['employee_id']]['signed'])) { $employee_data[$v['employee_id']]['signed_money'] += $v['deposit_money']; } } } $employee_data = array_values($employee_data); $group_list1 = []; // 社群 $service_list1 = []; // 客服 foreach ($employee_data as $k => $v){ if ($v['community_disable'] == 1) { $service_list1[] = $v; } if ($v['community_disable'] == 0) { $group_list1[] = $v; } } array_multisort(array_column($group_list1, 'signed_money'), SORT_DESC, $group_list1); array_multisort(array_column($service_list1, 'signed_money'), SORT_DESC, $service_list1); $service = array_slice($service_list1, 0, 3); $group = array_slice($group_list1, 0, 3); $service_list = []; foreach ($service as $k => $v){ $v_one['name'] = $v['name']; $v_one['money'] = $v['signed_money']; $service_list[] = $v_one; } $data['service'] = $service_list; // 社群 $group_list = []; foreach ($group as $k => $v){ $v_one['name'] = $v['name']; $v_one['money'] = $v['signed_money']; $group_list[] = $v_one; } $data['group'] = $group_list; Cache::set($cache_data_name, json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 首页社群数据 */ public function get_index_group(){ $root_id = $this->aec->decrypt($this->token); /*$param = request()->only(['times']); $time = [date('2023-04-01'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_group-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); }*/ $community_ids = BuildingModel::where([['root_id', '=', $root_id], ['del', '=', 0]])->group('community_id')->column('community_id'); $community_list = Community::where([['root_id', '=', $root_id], ['id', 'in', $community_ids]])->column('id,name,households'); $community_count = array_column($community_list,'households'); $community_name = array_column($community_list,'name'); $data['community_count'] = count($community_list); // 小区数 $data['households'] = array_sum($community_count); // 户型数 $sub_org = orgSubIds($root_id); // 总产值 $state1 = CustomerVisitLog::changeState('已交定', 'chaos'); $state2 = CustomerVisitLog::changeState('已签单', 'chaos'); $where[] = ['Employee.org_id', 'in', $sub_org]; $where[] = ['Employee.uid', '>', 0]; $where[] = ['Employee.state', '=', '在职']; $where[] = ['Employee.community_disable', '=', 0]; $where[] = ['CustomerVisitLog.state', 'in', array_merge($state1, $state2)]; // 小区的条件 $where[] = ['Customer.community_name', 'in', $community_name]; $log_list = Db::view('Customer') ->view('CustomerVisitLog', 'id vid', 'Customer.id=CustomerVisitLog.customer_id') ->view('Employee', 'id eid', 'CustomerVisitLog.customer_employee_id=Employee.id') ->where($where) ->column('Customer.id,Customer.employee_id,Customer.deposit_money,Customer.signed_money,CustomerVisitLog.customer_employee_id,CustomerVisitLog.state'); $she_data['signed'] = []; $she_data['deposit'] = []; $she_data['total_money'] = 0; foreach ($log_list as $k => $v) { // 签单 if (in_array($v['state'], $state2, true) && !in_array($v['id'], $she_data['signed'])) { $she_data['signed'][] = $v['id']; $she_data['total_money'] += $v['signed_money']; } } // 处理业绩 foreach ($log_list as $k => $v) { // 交定 if (in_array($v['state'], $state1, true) && !in_array($v['id'], $she_data['deposit'])) { $she_data['deposit'][] = $v['id']; // 判断是否签单 if (!in_array($v['id'], $she_data['signed'])) { $she_data['total_money'] += $v['deposit_money']; } } } $data['total_money'] = $she_data['total_money']; // 折线图 // 信息量 进群量 加微量 // 有效的 $valid_where[] = ['Employee.org_id', 'in', $sub_org]; $valid_where[] = ['Employee.uid', '>', 0]; $valid_where[] = ['Employee.state', '=', '在职']; $valid_where[] = ['Employee.community_disable', '=', 0]; $valid_where[] = ['Customer.community_name', 'in', $community_name]; $valid_list = Db::view('Employee') ->view('Customer', 'id cid', 'Customer.employee_id=Employee.id') ->where($valid_where) ->group('Customer.id') ->column('Customer.id,Customer.sign_time,Customer.into_owner_group,Customer.add_wechat_time'); // 无效的客户 $invalid_where[] = ['Employee.org_id', 'in', $sub_org]; $invalid_where[] = ['Employee.uid', '>', 0]; $invalid_where[] = ['Employee.state', '=', '在职']; $invalid_where[] = ['Employee.community_disable', '=', 0]; $invalid_where[] = ['Customer.community_name', 'in', $community_name]; $invalid_where[] = ['Customer.employee_id', 'NULL', null]; $invalid_list = Db::view('Employee') ->view('CustomerInvalidLog', 'id lid', 'Employee.id=CustomerInvalidLog.employee_id') ->view('Customer', 'id cid', 'Customer.id=CustomerInvalidLog.customer_id') ->where($invalid_where) ->group('Customer.id') ->column('Customer.id,Customer.sign_time,Customer.into_owner_group,Customer.add_wechat_time'); // 自动回收库表 $drop_where[] = ['Employee.org_id', 'in', $sub_org]; $drop_where[] = ['Employee.uid', '>', 0]; $drop_where[] = ['Employee.state', '=', '在职']; $drop_where[] = ['Employee.community_disable', '=', 0]; $drop_where[] = ['CustomerDropPool.community_name', 'in', $community_name]; $drop_list = Db::view('Employee') ->view('CustomerDropPool', 'id lid', 'Employee.id=CustomerDropPool.employee_id') ->where($drop_where) ->group('CustomerDropPool.id') ->column('CustomerDropPool.id,CustomerDropPool.sign_time,CustomerDropPool.into_owner_group,CustomerDropPool.add_wechat_time'); // 回收站表 $recycle_where[] = ['Employee.org_id', 'in', $sub_org]; $recycle_where[] = ['Employee.uid', '>', 0]; $recycle_where[] = ['Employee.state', '=', '在职']; $recycle_where[] = ['Employee.community_disable', '=', 0]; $recycle_where[] = ['CustomerRecycle.community_name', 'in', $community_name]; $recycle_list = Db::view('Employee') ->view('CustomerInvalidLog', 'id lid', 'Employee.id=CustomerInvalidLog.employee_id') ->view('CustomerRecycle', 'id cid', 'CustomerRecycle.id=CustomerInvalidLog.customer_id') ->where($recycle_where) ->group('CustomerRecycle.id') ->column('CustomerRecycle.id,CustomerRecycle.sign_time,CustomerRecycle.into_owner_group,CustomerRecycle.add_wechat_time'); $merge_list = array_merge($valid_list, $invalid_list, $drop_list, $recycle_list); $sign_time_list = array_filter(array_column($merge_list, 'sign_time', 'id')); $into_owner_group_list = array_filter(array_column($merge_list, 'into_owner_group', 'id')); $add_wechat_time_list = array_filter(array_column($merge_list, 'add_wechat_time', 'id')); $sign_time = []; foreach ($sign_time_list as $k => $v) { $v_date = date('Y-m-d', strtotime($v)); if (empty($sign_time[$v_date])) { $sign_time[$v_date] = 0; } $sign_time[$v_date] ++; } $into_owner_group = []; foreach ($into_owner_group_list as $k => $v) { if (empty($into_owner_group[$v])) { $into_owner_group[$v] = 0; } $into_owner_group[$v] ++; } $add_wechat_time = []; foreach ($add_wechat_time_list as $k => $v) { if (empty($add_wechat_time[$v])) { $add_wechat_time[$v] = 0; } $add_wechat_time[$v] ++; } //拆分时间 $tmp_day = 1; $time = [date('2023-04-01'), date('Y-m-d')]; //默认本月时间 $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } //折线图 $line_data = []; //折线图数据 foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { if (empty($line_data[$v['name']])) { $line_data[$v['name']] = []; $line_data[$v['name']]['customer'] = 0; // 信息量 $line_data[$v['name']]['group'] = 0; // 进群数量 $line_data[$v['name']]['wechat'] = 0; // 加微数量 } $v_start = strtotime($v['startDate'] . ' 00:00:00'); $v_end = strtotime($v['endDate'] . ' 23:59:59'); foreach ($sign_time as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v['name']]['customer'] += $vv; } } foreach ($into_owner_group as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v['name']]['group'] += $vv; } } foreach ($add_wechat_time as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v['name']]['wechat'] += $vv; } } } else { if (empty($line_data[$v])) { $line_data[$v] = []; $line_data[$v]['customer'] = 0; // 信息量 $line_data[$v]['group'] = 0; // 进群数量 $line_data[$v]['wechat'] = 0; // 加微数量 } $v_start = strtotime($v . ' 00:00:00'); $v_end = strtotime($v . ' 23:59:59'); foreach ($sign_time as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v]['customer'] += $vv; } } foreach ($into_owner_group as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v]['group'] += $vv; } } foreach ($add_wechat_time as $kk => $vv) { $kk_str = strtotime($kk); if ($kk_str >= $v_start && $kk_str <= $v_end) { $line_data[$v]['wechat'] += $vv; } } } } foreach ($line_data as $k => $v) { $v['time'] = $k; $line_data[$k] = $v; } $data['line'] = array_values($line_data); // Cache::set($cache_data_name, json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 首页多个模块的使用数据(内容使用数据) */ public function get_index_use_content(){ $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_use_content-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } $employee = Employee::where([['root_id', '=', $root_id], ['uid', '>', 0], ['state', '=', '在职']])->column('id'); $clue_type = ['materialCase', 'video', 'article', 'CompanyStrength', 'companyStrength', 'materialEvidence', 'building', 'buildingVr', 'toolAll', 'companyVr', 'lottery', 'styleTest', 'likeTest', 'priceCalculation', 'spellgroup', 'agents', 'weiwang', 'card']; $share_type = ['MaterialCase', 'Video', 'Article', 'CompanyStrength', 'MaterialEvidence', 'Building', 'ToolAll', 'CustomerJsAll', 'weiwang', 'card', 'MaterialCase_vr', 'Building_vr', 'EmployeeCard_vr', 'Company_vr']; // 转发次数 $where[] = ['employee_id', 'in', $employee]; $where[] = ['share_time', 'between', $time]; $where[] = ['type', 'in', $share_type]; $share_count = ShareLog::where($where)->count(); $data['share_count'] = $share_count; // 浏览次数 $where1[] = ['employee_id', 'in', $employee]; $where1[] = ['addtime', 'between', $time]; //$where1[] = ['pipe_type', 'in', $clue_type]; $foot_count = Footprints::where($where1)->select(); $data['footprints_count'] = $foot_count->count(); $visit_long = array_sum(array_column($foot_count->toArray(), 'visit_due_time')); $data['long_time'] = round($visit_long / 60, 2); // 获取线索数 $where2[] = ['employee_id', 'in', $employee]; $where2[] = ['addtime', 'between', $time]; //$where2[] = ['pipe_type', 'in', $clue_type]; $clue_list = CustomerClue::where($where2)->count(); $data['clue_count'] = $clue_list; Cache::set($cache_data_name,json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 智慧屏使用数据 * */ public function get_index_use_smartscreen(){ $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_use_smartscreen-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } // 浏览次数 $suborg = orgSubIds($root_id); $where[] = ['org_id', 'in', $suborg]; $where[] = ['addtime', 'between', $time]; $where[] = ['remark', 'like', '%' . '讲解智慧屏##']; $log_list = CustomerVisitLog::where($where)->select(); $data['count'] = $log_list->count(); $tmp_times = 0; foreach($log_list as $n){ $tmp_times += explode('分钟;
',explode('谈单时长:',$n['remark'])[1])[0]; } $data['long_time'] = $tmp_times; Cache::set($cache_data_name,json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 楼书使用数据 */ public function get_index_use_building(){ $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_use_building-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } $employee = Employee::where([['root_id', '=', $root_id], ['uid', '>', 0], ['state', '=', '在职']])->column('id'); // 转发次数 $where[] = ['employee_id', 'in', $employee]; $where[] = ['share_time', 'between', $time]; $where[] = ['type', 'in', ['Building', 'Building_vr']]; $share_count = ShareLog::where($where)->count(); $data['share_count'] = $share_count; // 浏览次数 $where1[] = ['employee_id', 'in', $employee]; $where1[] = ['addtime', 'between', $time]; $where1[] = ['pipe_type', 'in', ['building', 'buildingVr']]; $foot_count = Footprints::where($where1)->select(); $data['footprints_count'] = $foot_count->count(); $visit_long = array_sum(array_column($foot_count->toArray(), 'visit_due_time')); $data['long_time'] = round($visit_long / 60, 2); // 获取线索数 $where2[] = ['employee_id', 'in', $employee]; $where2[] = ['addtime', 'between', $time]; $where2[] = ['pipe_type', 'in', ['building', 'buildingVr']]; $clue_list = CustomerClue::where($where2)->count(); $data['clue_count'] = $clue_list; Cache::set($cache_data_name,json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 话术使用数据 */ public function get_index_use_talk(){ $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (!empty($param['times'])) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_index_use_talk-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } if (!empty($time[0])) { $time[0] = date('Y-m-d 00:00:00', strtotime($time[0])); } if (!empty($time[1])) { $time[1] = date('Y-m-d 23:59:59', strtotime($time[1])); } $suborg = orgSubIds($root_id); $where[] = ['org_id', 'in', $suborg]; $where[] = ['addtime', 'between', $time]; $count = TalkskillViewLog::where($where)->count(); $data['view_count'] = $count; // 话术使用次数 $where1[] = ['root_id', '=', $root_id]; $where1[] = ['addtime', 'between', $time]; // 百问百答 $use1 = Talkskill::where($where1)->sum('use_count'); // 精选话术 $use2 = TalkskillChosen::where($where1)->sum('use_count'); // 成功案例 $use3 = TalkskillSuccess::where($where1)->sum('use_count'); $data['use_count'] = $use1 + $use2 + $use3; Cache::set($cache_data_name,json_encode($data), $this->cache_time); return json(['code'=> 0, 'data'=> $data, 'msg'=> 'success']); } /** * 直播间数据列表 */ public function get_live_data() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } //拆分时间 $comtent_list = []; $tmp_day = 1; $time_arr = getDateByInterval($time[0], $time[1], 'day'); if (count($time_arr) > 35) { $tmp_day = 2; $time_arr = getDateByInterval($time[0], $time[1], 'month'); } //条件 $where[] = ['type', '=', 1]; $where[] = ['root_id', '=', $root_id]; $where_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; $cache_line_key = "cache_line_data_root_id_{$root_id}_time_$time[0].'_'.$time[1]}"; $cache_total_data_key = "cache_total_data_root_id_{$root_id}_time_$time[0].'_'.$time[1]}"; $cache_live_dongtai_data_key = "cache_live_dongtai_data_root_id_{$root_id}_time_$time[0].'_'.$time[1]}"; $ttl = $this->cache_time; // Cache::set($cache_line_key,null); // Cache::set($cache_total_data_key,null); // Cache::set($cache_live_dongtai_data_key,null); if (Cache::get($cache_line_key)) { $comtent_list = json_decode(Cache::get($cache_line_key), true); } else { $max_count = FishLive::where($where)->group('root_id,anchor_id,room_title')->count(); //todo 留资折线图 $empty_count = FishLive::where($where) ->group('root_id,anchor_id,room_title') ->column('room_title,anchor_id'); foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { $count_data = FishLive::where([ ['type', '=', 1], ['root_id', '=', $root_id], ['room_create_time', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']] ]) ->group('root_id,anchor_id,room_title') ->field('live_form_submit_count as s_count,room_title,anchor_id')->select()->toArray(); $comtent_list[$k]['time'] = $v['name']; error_reporting(E_ERROR | E_WARNING | E_PARSE); for ($i = 0; $i < $max_count; $i++) { if (empty($count_data)) { $comtent_list[$k]['number_' . $i] = 0; $comtent_list[$k]['room_title_' . $i] = $empty_count[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } else { if (empty($count_data[$i])) { $comtent_list[$k]['number_' . $i] = 0; $comtent_list[$k]['room_title_' . $i] = $empty_count[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } else { $comtent_list[$k]['number_' . $i] = $count_data[$i]['s_count']; $comtent_list[$k]['room_title_' . $i] = $count_data[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } } } } else { $count_data = FishLive::where([ ['type', '=', 1], ['root_id', '=', $root_id], ['room_create_time', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']] ]) ->group('root_id,anchor_id,room_title') ->field('live_form_submit_count as s_count,room_title,anchor_id')->select()->toArray(); $comtent_list[$k]['time'] = $v; for ($i = 0; $i < $max_count; $i++) { if (empty($count_data)) { $comtent_list[$k]['number_' . $i] = 0; $comtent_list[$k]['room_title_' . $i] = $empty_count[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } else { if (empty($count_data[$i])) { $comtent_list[$k]['number_' . $i] = 0; $comtent_list[$k]['room_title_' . $i] = $empty_count[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } else { $comtent_list[$k]['number_' . $i] = $count_data[$i]['s_count']; $comtent_list[$k]['room_title_' . $i] = $count_data[$i]['room_title'] . "【{$empty_count[$i]['anchor_id']}】"; } } } } } //写入缓存 Cache::set($cache_line_key, json_encode($comtent_list), $ttl); } //todo 左上角統計 $total_data = []; if (Cache::get($cache_total_data_key)) { $total_data = json_decode(Cache::get($cache_total_data_key), true); } else { //总直播间数 $total_data['live_total'] = FishLive::where($where)->group('root_id,anchor_id,room_title')->where([['room_create_time', 'between', $where_time]])->count(); //todo 飞鱼数据 // $channel_setting_wh = Company::where(['root_id' => $root_id])->value('channel_setting_wh'); // if (empty($channel_setting_wh)){ // $data = [ // 'cost' => 0, // 'shows' => 0, // 'click' => 0, // 'converts' => 0, // ]; // $customer_id = []; // } // else{ // $tmp_data = json_decode($channel_setting_wh, true); $FishCampaign_where[] = ['type', '=', 4]; $FishCampaign_where[] = ['root_id', '=', $root_id]; // $FishCampaign_where[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $FishCampaign_where[] = ['data_time', 'between', $time]; $data = FishCampaign::where($FishCampaign_where) ->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts") ->find(); $FishCampaign_where1[] = ['type', '=', 4]; $FishCampaign_where1[] = ['root_id', '=', $root_id]; // $FishCampaign_where1[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $FishCampaign_where1[] = ['create_time', 'between', $time]; $zhibo_clue = FishData::where($FishCampaign_where1)->count(); $customer_id = FishData::where([ ['type', '=', 4], ['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1], // ['advertiser_id', 'in', $tmp_data['fish_adv']], ])->column('customer_id'); // } //查询有效线索 $source_id = CustomerSource::where(['root_id' => $root_id, 'source' => '飞鱼线索'])->value('id'); // $company = Company::where([['root_id', '=', $root_id]])->find(); // $source_id = $company->channelSource['fish']; $customer_data = Customer::where([ ['source_id', 'in', $source_id], ['id', 'in', $customer_id] ]) ->field('id,deposit_money as deposit_moneys,signed_money as signed_moneys') ->select()->toArray(); $fish_count = $fish_money = 0; foreach ($customer_data as $val) { $fish_count++; if (isset($val['signed_moneys']) && $val['signed_moneys']) { $fish_money += $val['signed_moneys']; } if (!isset($val['signed_moneys']) && isset($val['deposit_moneys'])) { $fish_money += $val['deposit_moneys']; } } $fish_count = count(FishData::where($FishCampaign_where1)->where(['is_allocation' => 1])->column('customer_id')); //总投放费用 $total_data['live_cost'] = $data['cost'] ?? 0; //总曝光人数 $live_watch_count = FishLive::where($where)->where([['room_create_time', 'between', $where_time]])->sum('live_watch_count'); $total_data['live_watch_count'] = $live_watch_count ?? 0; //总观看人数 $live_watch_ucount = FishLive::where($where)->where([['room_create_time', 'between', $where_time]])->sum('live_watch_ucount'); $total_data['live_watch_ucount'] = $live_watch_ucount ?? 0; //总点击人数 $live_card_icon_component_show_count = FishLive::where($where)->where([['room_create_time', 'between', $where_time]])->sum('live_card_icon_component_show_count'); $total_data['live_d_click_count'] = $live_card_icon_component_show_count ?? 0; //总线索 $total_data['total_converts'] = $zhibo_clue ?? 0; //有效线索 $total_data['total_valid_clue_count'] = $fish_count ?? 0; //总飞鱼广告线索成本 = 广告总消耗 / 广告线索量 $total_data['total_converts_cb'] = $zhibo_clue > 0 ? round($data['cost'] / $zhibo_clue, 2) : 0; //总产值 $total_data['total_signed_money'] = $fish_money ?? 0; //飞鱼有效率 $total_data['fish_count_lv'] = $fish_count > 0 && $zhibo_clue > 0 ? round($fish_count / $zhibo_clue * 100, 2) : 0; //写入缓存 Cache::set($cache_total_data_key, json_encode($total_data), $ttl); } //todo 直播动态 $live_dongtai = []; if (Cache::get($cache_live_dongtai_data_key)) { $live_dongtai = json_decode(Cache::get($cache_live_dongtai_data_key), true); } else { foreach ($time_arr as $k => $v) { if ($tmp_day == 2) { $count_num = FishLive::where([ ['type', '=', 1], ['root_id', '=', $root_id], ['room_create_time', 'between', [$v['startDate'] . ' 00:00:00', $v['endDate'] . ' 23:59:59']] ])->field('sum(live_watch_count) as w_count,sum(live_card_icon_component_show_count) as cs_count')->find()->toArray(); $live_dongtai[$k]['times'] = $v['name']; $live_dongtai[$k]['baoguang_number'] = $count_num['w_count'] ?? 0; $live_dongtai[$k]['click_number'] = $count_num['cs_count'] ?? 0; // $channel_setting_wh = Company::where(['root_id' => $root_id])->value('channel_setting_wh'); // // // if(empty($channel_setting_wh)){ // $FishCampaign['converts'] = 0; // }else{ // $tmp_data = json_decode($channel_setting_wh, true); $FishCampaign_where_dontai[] = ['type', '=', 4]; $FishCampaign_where_dontai[] = ['root_id', '=', $root_id]; // $FishCampaign_where_dontai[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $zhibo_clue = FishData::where($FishCampaign_where_dontai)->whereTime('create_time', 'between', [$v['startDate'], $v['endDate']])->count(); // } $live_dongtai[$k]['converts'] = $zhibo_clue ?? 0; } else { $count_num = FishLive::where([ ['type', '=', 1], ['root_id', '=', $root_id], ['room_create_time', 'between', [$v . ' 00:00:00', $v . ' 23:59:59']] ])->field('sum(live_watch_count) as w_count,sum(live_card_icon_component_show_count) as cs_count')->find()->toArray(); $live_dongtai[$k]['time'] = $v; $live_dongtai[$k]['baoguang_number'] = $count_num['w_count'] ?? 0; $live_dongtai[$k]['click_number'] = $count_num['cs_count'] ?? 0; $channel_setting_wh = Company::where(['root_id' => $root_id])->value('channel_setting_wh'); if (empty($channel_setting_wh)) { $FishCampaign['converts'] = 0; } else { // $tmp_data = json_decode($channel_setting_wh, true); $FishCampaign_where_dontai[] = ['type', '=', 4]; $FishCampaign_where_dontai[] = ['root_id', '=', $root_id]; // $FishCampaign_where_dontai[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $zhibo_clue = FishData::where($FishCampaign_where_dontai)->where('create_time', $v)->count(); } $live_dongtai[$k]['converts'] = $zhibo_clue ?? 0; } } //写入缓存 Cache::set($cache_live_dongtai_data_key, json_encode($live_dongtai), $ttl); } // todo 返回数据 return json(['code' => 0, 'data' => [ 'left_total_data' => $total_data, 'live_dongtai' => $live_dongtai, 'line_data' => $comtent_list, ], 'msg' => '获取成功']); } /** * 直播间数据列表 */ public function get_live_room_data() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $where[] = ['type', '=', 1]; $where[] = ['root_id', '=', $root_id]; $ttl = $this->cache_time;//3分钟缓存 $return_data = []; $cache_live_room_data_key = "cache_live_room_data_root_id_{$root_id}_time_$time[0].'_'.$time[1]}"; if (Cache::get($cache_live_room_data_key)){ $return_data = json_decode(Cache::get($cache_live_room_data_key),true); }else{ //todo 直播列表 $where_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; $live_data = FishLive::where($where) ->where([['room_create_time', 'between', $where_time]]) ->field('id,anchor_id,data_create,advertiser_id,room_id,room_title,room_create_time,live_watch_count,live_watch_ucount,live_form_submit_count,live_card_icon_component_show_count,live_avg_watch_duration,live_comment_count,live_follow_count,live_share_count,live_count') ->order('room_create_time desc') ->group('root_id,anchor_id,data_create') ->select() ->toArray(); foreach ($live_data as $k=> $v){ $return_data[$k]['id'] = $v['id'] ?? 0; $return_data[$k]['anchor_id'] = $v['anchor_id'] ?? 0; $return_data[$k]['data_create'] = $v['data_create'] ?? ''; //时间 $return_data[$k]['data_time'] = date('Y/m/d',strtotime($v['room_create_time'])) ?? ''; //名称 $return_data[$k]['room_title'] = $v['room_title'] ?? ''; //todo 飞鱼数据 $channel_setting_wh = Company::where(['root_id' => $root_id])->value('channel_setting_wh'); if (empty($channel_setting_wh)){ $data = [ 'cost' => 0, 'shows' => 0, 'click' => 0, 'converts' => 0, ]; } else { $tmp_data = json_decode($channel_setting_wh, true); $FishCampaign_where[] = ['type', '=', 4]; $FishCampaign_where[] = ['root_id', '=', $root_id]; $FishCampaign_where[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $FishCampaign_where[] = ['data_time', 'between', $time]; $data = FishCampaign::where($FishCampaign_where) ->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts") ->find(); } //投放费用 $return_data[$k]['cost'] = $data['cost'] ?? 0; //曝光人数 $return_data[$k]['live_watch_count'] = $v['live_watch_count'] ?? 0; //观看人数 $return_data[$k]['live_watch_ucount'] = $v['live_watch_ucount'] ?? 0; //点击数 $return_data[$k]['live_d_click_count'] = $v['live_card_icon_component_show_count'] ?? 0; $return_data[$k]['live_avg_watch_duration'] = round($v['live_avg_watch_duration'] / 60, 1) ?? 0; $return_data[$k]['live_comment_count'] = $v['live_comment_count'] ?? 0; $return_data[$k]['live_follow_count'] = $v['live_follow_count'] ?? 0; $return_data[$k]['live_form_submit_count'] = $v['live_form_submit_count'] ?? 0; } //写入缓存 Cache::set($cache_live_room_data_key,json_encode($return_data),$ttl); } // todo 返回数据 return json(['code' => 0, 'data' => [ 'live_room_list'=>$return_data, ], 'msg' => '获取成功']); } /** * 直播间数据列表 */ public function get_live_room_info() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['id','times','anchor_id','data_create']); $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $where[] = ['type', '=', 1]; $where[] = ['root_id', '=', $root_id]; if (empty($param['anchor_id']) || empty($param['data_create'])){ return json(['code' => 1, 'msg' => '参数错误！']); } $ttl = $this->cache_time;//3分钟缓存 $return_data = []; $cache_live_room_info_param_key = "cache_live_room_info_param_root_id_{$root_id}_data_create_{$param['data_create']}_anchor_id_{$param['anchor_id']}"; if (Cache::get($cache_live_room_info_param_key)){ $return_data = json_decode(Cache::get($cache_live_room_info_param_key),true); }else{ //todo 直播列表 // $where_time = [$time[0] . ' 00:00:00', $time[1] . ' 23:59:59']; $live_data = FishLive::where($where) ->where('anchor_id',$param['anchor_id']) ->where('data_create',$param['data_create']) // ->where([['room_create_time', 'between', $where_time]]) ->field('id,anchor_id,anchor_nick,data_create,advertiser_id,room_id,room_title,room_create_time,live_watch_count,live_watch_ucount,live_form_submit_count,live_card_icon_component_show_count,live_avg_watch_duration,live_comment_count,live_follow_count,live_share_count,live_count') ->order('room_create_time desc') // ->group('root_id,anchor_id,data_create') ->select() ->toArray(); foreach ($live_data as $k=> $v){ $return_data[$k]['id'] = $v['id'] ?? 0; $return_data[$k]['anchor_id'] = $v['anchor_id'] ?? 0; $return_data[$k]['anchor_nick'] = $v['anchor_nick'] ?? ''; $return_data[$k]['data_create'] = $v['data_create'] ?? ''; $return_data[$k]['room_create_time'] = $v['room_create_time'] ?? ''; //时间 $return_data[$k]['data_time'] = date('Y/m/d',strtotime($v['room_create_time'])) ?? ''; //名称 $return_data[$k]['room_title'] = $v['room_title'] ?? ''; //todo 飞鱼数据 $channel_setting_wh = Company::where(['root_id' => $root_id])->value('channel_setting_wh'); if (empty($channel_setting_wh)){ $data = [ 'cost' => 0, 'shows' => 0, 'click' => 0, 'converts' => 0, ]; } else { $tmp_data = json_decode($channel_setting_wh, true); $FishCampaign_where[] = ['type', '=', 4]; $FishCampaign_where[] = ['root_id', '=', $root_id]; $FishCampaign_where[] = ['advertiser_id', 'in', $tmp_data['fish_adv']]; $FishCampaign_where[] = ['data_time', 'between', $time]; $data = FishCampaign::where($FishCampaign_where) ->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts") ->find(); } //投放费用 $return_data[$k]['cost'] = $data['cost'] ?? 0; //曝光人数 $return_data[$k]['live_watch_count'] = $v['live_watch_count'] ?? 0; //观看人数 $return_data[$k]['live_watch_ucount'] = $v['live_watch_ucount'] ?? 0; //点击数 $return_data[$k]['live_d_click_count'] = $v['live_card_icon_component_show_count'] ?? 0; $return_data[$k]['live_avg_watch_duration'] = round($v['live_avg_watch_duration'] / 60, 1) ?? 0; $return_data[$k]['live_comment_count'] = $v['live_comment_count'] ?? 0; $return_data[$k]['live_follow_count'] = $v['live_follow_count'] ?? 0; $return_data[$k]['live_form_submit_count'] = $v['live_form_submit_count'] ?? 0; } //写入缓存 Cache::set($cache_live_room_info_param_key,json_encode($return_data),$ttl); } // todo 返回数据 return json(['code' => 0, 'data' => $return_data, 'msg' => '获取成功']); } /** * 获取今日广告组更新的线索 * todo 威弘 */ public function get_fish_top_live_data_wh() { $root_id = $this->aec->decrypt($this->token); $channel_setting = Company::where(['root_id' => $root_id])->value('channel_setting_wh');//todo 威弘 $fish_change =[]; if (!empty($channel_setting)){ $tmp_data = json_decode($channel_setting, true); $time = date('Y-m-d'); //查询今天的数据 $tmp_data = $this->refreshToken2($tmp_data, $root_id); $advertiser_id = explode(',', $tmp_data['fish_adv']); foreach ($advertiser_id as $va) { $number = 0; $data = $this->fish_wh->getCampaignList($tmp_data['fish_access_token'], ['advertiser_id' => $va, 'start_date' => $time, 'end_date' => $time, 'page' => 1]); if ($data['code'] == 0) { $save_data = []; foreach ($data['data']['list'] as $val) { //需要比是否有新的线索 $info = FishCampaign::where(['root_id' => $root_id, 'data_time' => $time, 'advertiser_id' => $va, 'campaign_id' => $val['campaign_id']])->field('id,campaign_name,convert')->find(); if (isset($info) && $info) { $tmp_number = $val['convert'] > $info['convert'] ? $val['convert'] - $info['convert'] : 0; //比之前存的记录多出多少线索 $number += $tmp_number; $val['id'] = $info['id']; FishCampaign::update($val); } else { $val['data_time'] = $time; $val['advertiser_id'] = $va; $val['root_id'] = $root_id; unset($val['id']); $save_data[] = $val; $number += $val['convert']; } } FishCampaign::insertAll($save_data); } if ($number > 0) { $fish_change[] = "直播间获取线索" . $number . '个'; } } } return json(['code' => 0, 'data' => $fish_change, 'msg' => '获取成功']); } /** * 投放大屏-获取广告投放总数据-中间部分 */ public function get_transform_left_total_data() { $root_id = $this->aec->decrypt($this->token); $param = request()->only(['times']); //时间区间 $time = [date('Y-m-1'), date('Y-m-d')]; //默认本月时间 if (isset($param['times']) && $param['times']) { $time = explode(' - ', $param['times']); } $cache_data_name = 'get_transform_left_total_data-' . $time[0] . '-' . $time[1] . '_' . $root_id; $cache_data_info = Cache::get($cache_data_name); if($cache_data_info){ return json(['code' => 0, 'data' => json_decode($cache_data_info), 'msg' => '获取成功', 'leble' => '缓存']); } //飞鱼数据 $where[] = ['type', 'in', [1,2,4]]; $where[] = ['root_id', '=', $root_id]; $where[] = ['data_time', 'between', $time]; $data = FishCampaign::where($where)->field("sum(cost) as cost,sum(`show`) as shows,sum(click) as click,sum(`convert`) as converts")->find(); $customer_id = FishData::where([['root_id', '=', $root_id], ['create_time', 'between', $time], ['is_allocation', '=', 1]])->column('customer_id'); //todo 直播计算 $zhibo_where[] = ['type', 'in', [4]]; $zhibo_where[] = ['root_id', '=', $root_id]; $zhibo_where[] = ['create_time', 'between', $time]; $zhibo_clue_count = FishData::where($zhibo_where)->count(); $live_number = $zhibo_clue_count;//FishCampaign::where([['root_id', '=', $root_id], ['data_time', 'between', $time], ['type', '=', 4]])->sum('convert');//直播总线索 // $tx_number = FishCampaign::where([['root_id', '=', $root_id], ['data_time', 'between', $time], ['type', '=', 2]])->sum('convert');//腾讯总转化 $tx_clue_where[] = ['type', 'in', [2]]; $tx_clue_where[] = ['root_id', '=', $root_id]; $tx_clue_where[] = ['create_time', 'between', $time]; $tx_clue_count = FishData::where($tx_clue_where)->count(); //查询有效线索 $source_id = CustomerSource::where([['root_id' ,'=', $root_id], ['source' ,'in', ['飞鱼线索','腾讯线索']]])->column('id'); $customer_data = Customer::where([['source_id', 'in', $source_id], ['id', 'in', $customer_id]])->field('id,deposit_money as deposit_moneys,signed_money as signed_moneys')->select()->toArray(); $fish_count = $fish_money = $signed_number = 0; foreach ($customer_data as $val) { $fish_count++; if (isset($val['signed_moneys']) && $val['signed_moneys']) { $signed_number++; $fish_money += $val['signed_moneys']; } if (!isset($val['signed_moneys']) && isset($val['deposit_moneys'])) { $fish_money += $val['deposit_moneys']; } } $end_clue_where[] = ['type', 'in', [1,2,4]]; $end_clue_where[] = ['root_id', '=', $root_id]; $end_clue_where[] = ['create_time', 'between', $time]; $total_converts = FishData::where($end_clue_where)->count(); $all = [ //总投放金额 'cost' => $data['cost']??0, //展示数 'shows' => $data['shows']??0, //总转化数 'converts' => $total_converts??0, //点击数 'click' => $data['click']??0, //转化率 'convert_lv' => $total_converts > 0 && $data['click'] > 0 ? round($total_converts / $data['click'] * 100, 2) : 0, //总派单数 'count' => $total_converts??0,// $fish_count, //有效率=总派单数/总转化数 'count_lv' => $fish_count > 0 && $total_converts > 0 ? round($fish_count / $total_converts * 100, 2) : 0, //产值 'signed_money' => $fish_money, //均单值=产值/有效 'signed_money_one' => $fish_money > 0 && $fish_count > 0 ? round($fish_money / $fish_count * 100, 2) : 0, //直播派单 'count_live' => $live_number, //投放派单 'count_other' => $total_converts ??0, //合同数 'signed_number' => $signed_number, //腾讯总转化 'tx_number' => $tx_clue_count ]; Cache::set($cache_data_name,json_encode($all), $this->cache_time); return json(['code' => 0, 'data' => $all, 'msg' => '获取成功']); } }