123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645 |
- <?php
- namespace app\admin\controller\qingdongams\statistic;
- use addons\qingdongams\model\Customer as CustomerModel;
- use addons\qingdongams\model\Event;
- use addons\qingdongams\model\Form;
- use addons\qingdongams\model\Quote;
- use addons\qingdongams\model\StaffSignIn;
- use app\admin\model\AuthGroup;
- use app\common\controller\Backend;
- use addons\qingdongams\model\Contract;
- use addons\qingdongams\model\Customer;
- use addons\qingdongams\model\Field;
- use addons\qingdongams\model\Receivables;
- use addons\qingdongams\model\Record;
- use addons\qingdongams\model\Staff;
- use fast\Tree;
- use think\Config;
- use think\Exception;
- /**
- * 客户分析
- */
- class Customers extends Backend
- {
- public function _initialize()
- {
- parent::_initialize();
- try{
- \think\Db::execute("SET @@sql_mode='';");
- }catch (Exception $e){
- }
- $childrenGroupIds = $this->auth->getChildrenGroupIds(true);
- $groupList = collection(AuthGroup::where('id', 'in', $childrenGroupIds)->select())->toArray();
- Tree::instance()->init($groupList);
- $groupList = [];
- if ($this->auth->isSuperAdmin()) {
- $groupList = Tree::instance()->getTreeList(Tree::instance()->getTreeArray(0));
- } else {
- $groups = $this->auth->getGroups();
- $groupIds = [];
- foreach ($groups as $m => $n) {
- if (in_array($n['id'], $groupIds) || in_array($n['pid'], $groupIds)) {
- continue;
- }
- $groupList = array_merge($groupList, Tree::instance()->getTreeList(Tree::instance()->getTreeArray($n['pid'])));
- foreach ($groupList as $index => $item) {
- $groupIds[] = $item['id'];
- }
- }
- }
- $this->assign('groupdata', $groupList);
- $this->assign('typeList', [0 => '请选择', 1 => '部门', 2 => '员工']);
- }
- /**
- * 客户总量分析
- */
- public function index()
- {
- $time = strtotime(date('Y-m', strtotime('-1 year')));
- $auth = Staff::getMyStaffIds();
- $staff_id = implode(',', $auth); //权限
- //新增客户数
- $customers = CustomerModel::where([
- 'createtime' => [
- 'egt',
- $time
- ],
- 'owner_staff_id' => ['in', $staff_id]
- ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
- $select = [];
- foreach ($customers as $v) {
- $select[$v['ctime']] = $v['c'];
- }
- //已成交客户数
- $usecustomers = CustomerModel::where([
- 'owner_staff_id' => ['in', $staff_id],
- 'createtime' => [
- 'egt',
- $time
- ],
- 'contract_status' => 1
- ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
- $useselect = [];
- foreach ($usecustomers as $v) {
- $useselect[$v['ctime']] = $v['c'];
- }
- $toMonthTime = strtotime(date('Y-m'));
- $typedata = [];
- for ($time; $time <= $toMonthTime; $time = strtotime('+1 month', $time)) {
- $typedata['date'][] = date('Y-m', $time);
- $typedata['num'][] = $select[date('Y-m', $time)] ?? 0;
- $typedata['use'][] = $useselect[date('Y-m', $time)] ?? 0;
- }
- $where['status'] = 1;
- $staff = Staff::where($where)->field('id,name')->select();
- $customInfo = [];
- foreach ($staff as $k => $v) {
- $customInfo[$k]['name'] = $v['name'];
- //新增客户数
- $addcustomer = CustomerModel::where(array('owner_staff_id' => $v['id']))->count();
- $customInfo[$k]['addcustomer'] = $addcustomer;
- //成交客户数
- $usecustomer = CustomerModel::where(array('owner_staff_id' => $v['id'], 'contract_status' => 1))->count();
- $customInfo[$k]['usecustomer'] = $usecustomer;
- //客户成交率(%)
- $customInfo[$k]['userate'] = $addcustomer ? sprintf("%.2f", $usecustomer / $addcustomer) : 0;
- //合同总金额
- $contractMoney = Contract::where(array('owner_staff_id' => $v['id'], 'check_status' => 2))->sum('money');
- $customInfo[$k]['contractMoney'] = $contractMoney;
- //回款金额
- $reciveMoney = Receivables::where(array('owner_staff_id' => $v['id'], 'check_status' => 2))->sum('money');
- $customInfo[$k]['reciveMoney'] = $reciveMoney;
- //未回款金额
- $customInfo[$k]['noreciveMoney'] = $contractMoney - $reciveMoney;
- //回款完成率
- $customInfo[$k]['ratereciveMoney'] = $contractMoney ? sprintf("%.2f", $reciveMoney / $contractMoney) : 0;
- }
- $this->view->assign([
- 'customerdata' => $typedata,
- 'customInfo' => $customInfo
- ]);
- return $this->view->fetch();
- }
- /**
- * 客户跟进次数分析
- */
- public function record()
- {
- $time = strtotime(date('Y-m', strtotime('-1 year')));
- $auth = Staff::getMyStaffIds();
- $staff_id = implode(',', $auth); //权限修改
- //跟进客户数
- $customers = StaffSignIn::where([
- 'staff_id' => ['in', $staff_id],
- 'relation_type' => 0,
- 'createtime' => [
- 'egt',
- $time
- ]
- ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,relation_id,count('*') as c")->group('relation_id,ctime')->select();
- $select = [];
- foreach ($customers as $v) {
- $select[$v['ctime']] = $v['c'];
- }
- //跟进次数
- $usecustomers = StaffSignIn::where([
- 'staff_id' => ['in', $staff_id],
- 'relation_type' => 0,
- 'createtime' => [
- 'egt',
- $time
- ]
- ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,count(*) as c")->group('ctime')->select();
- $useselect = [];
- foreach ($usecustomers as $v) {
- $useselect[$v['ctime']] = $v['c'];
- }
- $toMonthTime = strtotime(date('Y-m'));
- $typedata = [];
- for ($time; $time <= $toMonthTime; $time = strtotime('+1 month', $time)) {
- $typedata['date'][] = date('Y-m', $time);
- $typedata['num'][] = $select[date('Y-m', $time)] ?? 0;
- $typedata['use'][] = $useselect[date('Y-m', $time)] ?? 0;
- }
- $where['status'] = 1;
- $staff = Staff::where($where)->field('id,name')->select();
- $customInfo = [];
- foreach ($staff as $k => $v) {
- $customInfo[$k]['name'] = $v['name'];
- //跟进客户数
- $addcustomer = StaffSignIn::where(array('staff_id' => $v['id'], 'relation_type' => 0))->group('relation_id')->count();
- $customInfo[$k]['addcustomer'] = $addcustomer;
- //跟进次数
- $usecustomer = StaffSignIn::where(array('staff_id' => $v['id'], 'relation_type' => 0))->count();
- $customInfo[$k]['usecustomer'] = $usecustomer;
- }
- $this->view->assign([
- 'customerdata' => $typedata,
- 'customInfo' => $customInfo
- ]);
- return $this->view->fetch();
- }
- /**
- * 客户跟进方式分析
- */
- public function recordtype()
- {
- //客户来源统计
- $time = strtotime(date('Y-m', strtotime('-1 year')));
- $auth = Staff::getMyStaffIds();
- $staff_id = implode(',', $auth); //权限修改
- $sources = Field::getField('客户来源');
- $customers = CustomerModel::where([
- 'owner_staff_id' => ['in', $staff_id],
- 'createtime' => [
- 'egt',
- $time
- ]
- ])->field("FROM_UNIXTIME(createtime,'%Y-%m') as ctime,source,count(*) as c")->group('source,ctime')->select();
- $select = [];
- foreach ($customers as $v) {
- $select[$v['source']][$v['ctime']] = $v['c'];
- }
- $toMonthTime = strtotime(date('Y-m'));
- $sourcedata = ['客户来源' => ['客户来源']];
- for ($time; $time <= $toMonthTime; $time = strtotime('+1 month', $time)) {
- $sourcedata['客户来源'][] = date('Y-m', $time);
- foreach ($sources as $v) {
- if (empty($sourcedata[$v])) {
- $sourcedata[$v][] = $v;
- }
- $sourcedata[$v][] = intval($select[$v][date('Y-m', $time)] ?? 0);
- }
- }
- $this->view->assign([
- 'customerdata' => array_values($sourcedata),
- ]);
- return $this->view->fetch();
- }
- /**
- * 客户成交率分析
- */
- public function ratio()
- {
- $type = input('type', 'bar');//bar 柱状图 pie 饼状图
- $row = input('row/a');
- if (isset($row['times']) && $row['times']) {
- $times = explode(' - ', $row['times']);
- } else {
- $times = [date('Y-01-01'), date('Y-m-d')];
- }
- $between = [strtotime($times[0]), strtotime($times[1]) + 86400 - 1];
- $group_id = $row['group_id'] ?? 0;
- $ids = [];
- if ($group_id) {//角色组
- $ids = Staff::getGroupStaffIds($group_id);
- }
- if (isset($row['staff_id']) && $row['staff_id']) {
- $ids = $staff_id = $row['staff_id'];
- } else {
- $staff_id = '';
- }
- $this->view->assign([
- 'customerdata' => [],
- 'times' => implode(' - ', $times),
- 'staff_id' => $staff_id,
- 'group_id' => $group_id,
- 'list' => [],
- 'type' => $type,
- ]);
- return $this->view->fetch();
- }
- /**
- * 客户自定义分析
- */
- public function customize()
- {
- $type = input('type', 'bar');//bar 柱状图 pie 饼状图
- $row = input('row/a');
- if (isset($row['times']) && $row['times']) {
- $times = explode(' - ', $row['times']);
- } else {
- $times = [date('Y-01-01'), date('Y-m-d')];
- }
- $between = [strtotime($times[0]), strtotime($times[1]) + 86400 - 1];
- $group_id = $row['group_id'] ?? 0;
- $ids = [];
- if ($group_id) {//角色组
- $ids = Staff::getGroupStaffIds($group_id);
- }
- if (isset($row['staff_id']) && $row['staff_id']) {
- $ids = $staff_id = $row['staff_id'];
- } else {
- $staff_id = '';
- }
- $customer = Form::getDataValue('customer');
- $fields = [];
- foreach ($customer as $v) {
- if ($v['component'] == 'select' && substr($v['id'], 0, 5) != 'other') {
- $content = $v['config']['content'];
- $label = [];
- foreach ($content as $ves) {
- $label[] = $ves['label'];
- }
- $fields[$v['id']] = ['id' => $v['id'], 'name' => $v['config']['label'],
- 'label' => $label, 'multiple' => $v['config']['multiple']];
- }
- }
- if (isset($row['field']) && $row['field']) {
- $field = html_entity_decode(trim($row['field']));
- if (empty($fields[$field])) {
- $this->error('没有可用的筛选项');
- }
- } else {
- if (empty($fields)) {
- $this->error('没有可用的筛选项');
- }
- $field = current($fields)['id'];
- }
- $where = [
- 'createtime' => [
- 'between',
- $between
- ]];
- if ($ids || !empty($group_id)) {
- $where['owner_staff_id'] = ['in', $ids];
- }
- if ($fields[$field]['multiple'] == false) {
- $customers = Customer::where($where)
- ->field("{$field},count(*) as c")
- ->group($field)->order('c desc')->select();
- $select = [];
- $total_count = 0;
- foreach ($customers as $v) {
- $total_count += $v['c'];
- $select[$v[$field]] = $v['c'];
- }
- } else {
- $select = [];
- $total_count = 0;
- //多选情况
- foreach ($fields[$field]['label'] as $label) {
- $where[$field] = ['like', "%{$label}%"];
- $count = Customer::where($where)->count();
- $select[$label] = $count;
- $total_count += $count;
- }
- }
- $sourcedata = [];
- $y = [];
- $list = [];
- $data = [];
- foreach ($fields[$field]['label'] as $label) {
- if (isset($select[$label])) {
- $list[] = [
- 'name' => $label,
- 'number' => $select[$label],
- 'ratio' => sprintf("%.2f", $select[$label] / $total_count * 100),
- ];
- $data[] = ['value' => $select[$label], 'name' => $label];
- $y[] = $select[$label];
- } else {
- $list[] = [
- 'name' => $label,
- 'number' => 0,
- 'ratio' => 0,
- ];
- $data[] = ['value' => 0, 'name' => $label];
- $y[] = 0;
- }
- }
- $sourcedata['x'] = $fields[$field]['label'];
- $sourcedata['y'] = $y;
- $sourcedata['title'] = $fields[$field]['name'];
- $sourcedata['data'] = $data;
- $sourcedata['type'] = $type;
- $this->view->assign([
- 'customerdata' => $sourcedata,
- 'times' => implode(' - ', $times),
- 'fields' => $fields,
- 'field' => $field,
- 'field_name' => $fields[$field]['name'],
- 'staff_id' => $staff_id,
- 'group_id' => $group_id,
- 'list' => $list,
- 'type' => $type,
- 'multiple' => $fields[$field]['multiple'],
- ]);
- return $this->view->fetch();
- }
- /**
- * 获取员工列表
- */
- public function getstaff()
- {
- $pageSize = input('pageSize');
- $pageNumber = input('pageNumber');
- $where = [];
- if ($keyValue = $this->request->request("keyValue")) {
- $where['id'] = ['in', $keyValue];
- } else {
- $where['id'] = ['in', Staff::getMyStaffIds()];
- }
- $name = input('name');
- if (!empty($name)) {
- $where['name'] = ['like', '%' . $name . '%'];
- }
- $staff = Staff::where($where)->field('id,name')->order('id desc')->paginate($pageSize, false, ['page' => $pageNumber]);
- return json(['list' => $staff->items(), 'total' => $staff->total()]);
- }
- /**
- * 成交分析
- */
- public function deal()
- {
- list($where, $times, $row) = $this->getWhere();//获取where
- //到访
- $toWhere = array_merge($where, ['process' => 2]);
- $toResult = Event::where($toWhere)->field('count(*) as count,create_staff_id')->group('relation_id')->select();
- // 报价
- $quoteWhere = array_merge($where, ['check_status' => 2]);
- $quoteResult = Quote::where($quoteWhere)->field('count(*) as count,create_staff_id')->group('customer_id')->select();
- // 合同生效
- $contractWhere = array_merge($where, ['check_status' => 2]);
- $contractResult = Contract::where($contractWhere)->field('count(*) as count,create_staff_id')->group('customer_id')->select();
- // 表格
- $staffIds = array_unique(array_merge(array_column($toResult, 'create_staff_id'), array_column($quoteResult, 'create_staff_id'), array_column($contractResult, 'create_staff_id')));
- $staffList = Staff::where('id', 'in', $staffIds)->column('name', 'id');
- $toResult = $this->getArr($toResult, 'create_staff_id');
- $quoteResult = $this->getArr($quoteResult, 'create_staff_id');
- $contractResult = $this->getArr($contractResult, 'create_staff_id');
- $totalTo = array_sum($toResult);
- $totalQuote = array_sum($quoteResult);
- $totalContract = array_sum($contractResult);
- $table[] = [
- 'name' => '总计',
- 'to' => $totalTo,
- 'quote' => $totalQuote,
- 'contract' => $totalContract,
- ];
- $result = [
- ['value' => $totalTo, 'name' => '到访'],
- ['value' => $totalQuote, 'name' => '报价'],
- ['value' => $totalContract, 'name' => '合同'],
- ];
- foreach ($staffList as $key => $item) {
- $table[] = [
- 'name' => $staffList[$key],
- 'to' => isset($toResult[$key]) ? $toResult[$key] : 0,
- 'quote' => isset($quoteResult[$key]) ? $quoteResult[$key] : 0,
- 'contract' => isset($contractResult[$key]) ? $contractResult[$key] : 0,
- ];
- }
- $this->view->assign([
- 'result' => $result,
- 'table' => $table,
- 'row' => $row
- ]);
- $this->assignconfig('result', $result);
- $this->assignconfig('ids', $row['ids']);
- return $this->view->fetch();
- }
- /**
- * 跟进分析
- */
- public function follow()
- {
- list($where, $times, $row) = $this->getWhere(1);//获取where
- list($result, $table) = $this->getFollowList($where, $times);
- $this->view->assign([
- 'result' => $result,
- 'table' => $table,
- 'row' => $row
- ]);
- $this->assignconfig('result', $result);
- $this->assignconfig('ids', $row['ids']);
- return $this->view->fetch();
- }
- /**
- * 筛选条件
- */
- protected function getFollowList($where, $times)
- {
- // 员工跟进次数
- $result = StaffSignIn::where($where)
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time,customer_id')->select();
- // 唠嗑跟进次数
- $trade_result = StaffSignIn::where($where)->where(['customer_status' => 1])//成交
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time')
- ->group('time,customer_id')->select();
- $addCount = $this->getArr($result, 'time');
- $tradeCount = $this->getArr($trade_result, 'time');
- $dateList = getDateList($times[0], $times[1]);
- $result = [];
- foreach ($dateList as $date) {
- $result['date'][] = $date;
- $result['add_count'][] = $addCount[$date] ?? 0;
- $result['trade_count'][] = $tradeCount[$date] ?? 0;
- }
- // 表格跟进次数
- $tableTimes = StaffSignIn::where($where)
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time,staff_id')
- ->group('staff_id,time')->select();
- // 跟进客户数
- $tablePeople = StaffSignIn::where($where)->where(['customer_status' => 1])//成交
- ->field('count(*) as count,FROM_UNIXTIME(createtime,"%Y-%m-%d") as time,staff_id')
- ->group('staff_id,customer_id')->select();
- $staffIds = array_unique(array_merge(array_column($tableTimes, 'staff_id'), array_column($tablePeople, 'staff_id')));
- $staffList = Staff::where('id', 'in', $staffIds)->column('name', 'id');
- $tableTimes = $this->getArr($tableTimes, 'staff_id');
- $tablePeople = $this->getArr($tablePeople, 'staff_id');
- $totalAdd = array_sum($tableTimes);
- $totalBook = array_sum($tablePeople);
- $table[] = [
- 'name' => '总计',
- 'add' => $totalAdd,
- 'book' => $totalBook,
- ];
- foreach ($staffList as $key => $item) {
- $table[] = [
- 'name' => $staffList[$key],
- 'add' => isset($tableTimes[$key]) ? $tableTimes[$key] : 0,// 跟进次数
- 'book' => isset($tablePeople[$key]) ? $tablePeople[$key] : 0,// 跟进客户数
- ];
- }
- return [$result, $table];
- }
- /**
- * 筛选条件
- */
- public function staffes()
- {
- if ($this->request->isPost()) {
- $type_id = input('typeid');
- $params = [];
- if ($type_id == 1) {
- $model = new AuthGroup();
- $groups = $model->where($params)->field('id,name')->select();
- $list = [];
- foreach ($groups as $v) {
- $list[$v['id']] = $v['name'];
- }
- } else {
- $model = new Staff();
- $params['status'] = 1;
- $list = $model->where($params)->column('name', 'id');
- }
- return json($list);
- }
- }
- /**
- * 筛选条件
- */
- protected function getWhere($type = 0)
- {
- $beforeDays = strtotime(date('Y-m-d', strtotime('-30 day', time())));
- $times = [$beforeDays, time()];
- $this->request->filter(['strip_tags']);
- $row['type_id'] = 0;
- $row['ids'] = 0;
- $where = [];
- if ($this->request->get()) {
- $params = $this->request->get('row/a');
- if (isset($params['times']) && !empty($params['times'])) {
- $timesArr = explode('-', $params['times']);
- if (count($timesArr) < 5) {
- $this->error('时间格式错误');
- }
- $times[0] = $timesArr[0] . '-' . $timesArr[1] . '-' . $timesArr[2];
- $times[1] = $timesArr[3] . '-' . $timesArr[4] . '-' . $timesArr[5];
- $times = [strtotime($times[0]), strtotime($times[1]) + 86400];
- }
- if(isset($params['type_id'])){
- $row['type_id'] = $params['type_id'];
- }
- if(isset($params['ids'])){
- $row['ids'] = $params['ids'] ?? 0;
- }
- if (isset($params['type_id']) && isset($params['ids']) && $params['type_id'] == 1 && $params['ids']) {
- $l_ids = Staff::getOneGroupStaffIds($params['ids']);
- if ($type == 1) {
- $where['staff_id'] = ['in', $l_ids];
- } else {
- $where['create_staff_id'] = ['in', $l_ids];
- }
- } elseif (isset($params['type_id']) && isset($params['ids']) && $params['type_id'] == 2 && $params['ids']) {
- if ($type == 1) {
- $where['staff_id'] = $params['ids'];
- } else {
- $where['create_staff_id'] = $params['ids'];
- }
- }
- }
- $row['times'] = implode(' - ', [date("Y-m-d", $times[0]), date("Y-m-d", $times[1])]);
- $where['createtime'] = ['between', $times];
- return [$where, $times, $row];
- }
- /**
- * 筛选条件
- */
- protected function getArr($data, $key)
- {
- $tradeCount = [];
- foreach ($data as $v) {
- $tradeCount[$v[$key]] = $v['count'];
- }
- return $tradeCount;
- }
- }
|