crm_project/application/admin/controller/qingdong/statistic/Customers.php

<?php

namespace app\admin\controller\qingdong\statistic;


use addons\qingdong\model\Form;
use app\admin\model\AuthGroup;
use app\common\controller\Backend;
use addons\qingdong\model\Contract;
use addons\qingdong\model\Customer;
use addons\qingdong\model\Field;
use addons\qingdong\model\Receivables;
use addons\qingdong\model\Record;
use addons\qingdong\model\Staff;
use fast\Tree;
use think\Config;

/**
 * 客户分析
 */
class Customers extends Backend
{
    public function _initialize()
    {
        parent::_initialize();

        $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);
    }

    /**
     * 客户总量分析
     */
    public function index()
    {
        $row = input('row/a');
        if (isset($row['times']) && $row['times']) {
            $times = explode(' - ', $row['times']);
        } else {
            $times = [date('Y-01-01'), date('Y-m-d')];
        }
        if (isset($row['type']) && $row['type']) {
            $type = $row['type'];
        } else {
            $type = 0;
        }
        $staff = Staff::info();
        $between = [strtotime($times[0]), strtotime($times[1]) + 86400 - 1];
        $betweentime = [date('Y-m-d 00:00:00',strtotime($times[0])), date('Y-m-d 23:59:59',strtotime($times[1]) + 86400 - 1)];
        if ($type == 1) {//本人
            $ids = [$staff->id];
        } elseif ($type == 2) {//下属
            $ids = Staff::getLowerStaffId();
        } else {//全部
            $ids = Staff::getMyStaffIds();
        }
        if (isset($row['staff_id']) && $row['staff_id']) {
            $ids = $staff_id = $row['staff_id'];
        } else {
            $staff_id = '';
        }

        //新增客户数
        $customers = Customer::where([
            'createtime' => [
                'between',
                $between
            ],
            'owner_staff_id' => ['in', $ids]
        ])->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 = Customer::where([
            'owner_staff_id' => ['in', $ids],
            'createtime' => [
                'between',
                $between
            ],
            '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($times[1]);
        $typedata = [];
        $startTime = strtotime($times[0]);
        for ($startTime; $startTime <= $toMonthTime; $startTime = strtotime('+1 month', $startTime)) {

            $typedata['date'][] = date('Y-m', $startTime);
            $typedata['num'][] = $select[date('Y-m', $startTime)] ?? 0;
            $typedata['use'][] = $useselect[date('Y-m', $startTime)] ?? 0;

        }
        $where['status'] = 1;
        $where['id'] = ['in', $ids];
        $staff = Staff::where($where)->field('id,name')->select();
        $customInfo = [];
        foreach ($staff as $k => $v) {
            $customInfo[$k]['name'] = $v['name'];
            //新增客户数
            $addcustomer = Customer::where(array('owner_staff_id' => $v['id'], 'createtime' => [
                'between',
                $between
            ]))->count();
            $customInfo[$k]['addcustomer'] = $addcustomer;
            //成交客户数
            $usecustomer = Customer::where(array('owner_staff_id' => $v['id'], 'contract_status' => 1, 'createtime' => [
                'between',
                $between
            ]))->count();
            $customInfo[$k]['usecustomer'] = $usecustomer;
            //客户成交率(%)
            $customInfo[$k]['userate'] = $addcustomer ? sprintf("%.2f", $usecustomer / $addcustomer) * 100 : 0;
            //合同总金额
            $contractMoney = Contract::where(array('owner_staff_id' => $v['id'], 'check_status' => 2, 'order_date' => [
                'between',
                $betweentime
            ]))->sum('money');
            $customInfo[$k]['contractMoney'] = $contractMoney;
            //回款金额
            $reciveMoney = Receivables::where(array('owner_staff_id' => $v['id'], 'check_status' => 2, 'return_time' => [
                'between',
                $betweentime
            ]))->sum('money');
            $customInfo[$k]['reciveMoney'] = $reciveMoney;
            //未回款金额
            $customInfo[$k]['noreciveMoney'] = $contractMoney - $reciveMoney;
            //回款完成率
            $customInfo[$k]['ratereciveMoney'] = $contractMoney ? sprintf("%.2f", $reciveMoney / $contractMoney) * 100 : 0;
        }


        $this->view->assign([
            'customerdata' => $typedata,
            'customInfo' => $customInfo,
            'times' => implode(' - ', $times),
            'type' => $type,
            'staff_id' => $staff_id,
        ]);

        return $this->view->fetch();
    }

    /**
     * 客户跟进次数分析
     */
    public function record()
    {

        $row = input('row/a');
        if (isset($row['times']) && $row['times']) {
            $times = explode(' - ', $row['times']);
        } else {
            $times = [date('Y-01-01'), date('Y-m-d')];
        }
        if (isset($row['type']) && $row['type']) {
            $type = $row['type'];
        } else {
            $type = 0;
        }
        $staff = Staff::info();
        $between = [strtotime($times[0]), strtotime($times[1]) + 86400 - 1];
        if ($type == 1) {//本人
            $ids = [$staff->id];
        } elseif ($type == 2) {//下属
            $ids = Staff::getLowerStaffId();
        } else {//全部
            $ids = Staff::getMyStaffIds();
        }
        if (isset($row['staff_id']) && $row['staff_id']) {
            $ids = $staff_id = $row['staff_id'];
        } else {
            $staff_id = '';
        }

        //跟进客户数
        $customers = Record::where([
            'create_staff_id' => ['in', $ids],
            'relation_type' => 1,
            'createtime' => [
                'between',
                $between
            ]
        ])->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 = Record::where([
            'create_staff_id' => ['in', $ids],
            'relation_type' => 1,
            'createtime' => [
                'between',
                $between
            ]
        ])->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($times[1]);
        $typedata = [];
        $time = strtotime($times[0]);
        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;
        $where['id'] = ['in', $ids];
        $staff = Staff::where($where)->field('id,name')->select();
        $customInfo = [];
        foreach ($staff as $k => $v) {
            $customInfo[$k]['name'] = $v['name'];
            //跟进客户数
            $addcustomer = Record::where(array('create_staff_id' => $v['id'], 'relation_type' => 1))->group('relation_id')->count();
            $customInfo[$k]['addcustomer'] = $addcustomer;
            //跟进次数
            $usecustomer = Record::where(array('create_staff_id' => $v['id'], 'relation_type' => 1))->count();
            $customInfo[$k]['usecustomer'] = $usecustomer;
        }


        $this->view->assign([
            'customerdata' => $typedata,
            'customInfo' => $customInfo,
            'times' => implode(' - ', $times),
            'type' => $type,
            'staff_id' => $staff_id,
        ]);

        return $this->view->fetch();
    }

    /**
     * 客户跟进方式分析
     */
    public function recordtype()
    {

        $row = input('row/a');
        if (isset($row['times']) && $row['times']) {
            $times = explode(' - ', $row['times']);
        } else {
            $times = [date('Y-01-01'), date('Y-m-d')];
        }
        if (isset($row['type']) && $row['type']) {
            $type = $row['type'];
        } else {
            $type = 0;
        }
        $staff = Staff::info();
        $between = [strtotime($times[0]), strtotime($times[1]) + 86400 - 1];
        if ($type == 1) {//本人
            $ids = [$staff->id];
        } elseif ($type == 2) {//下属
            $ids = Staff::getLowerStaffId();
        } else {//全部
            $ids = Staff::getMyStaffIds();
        }
        if (isset($row['staff_id']) && $row['staff_id']) {
            $ids = $staff_id = $row['staff_id'];
        } else {
            $staff_id = '';
        }

        $sources = Field::getField('客户来源');
        $customers = Customer::where([
            'owner_staff_id' => ['in', $ids],
            'createtime' => [
                'between',
                $between
            ]
        ])->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'];
        }
        $sourcedata = ['客户来源' => ['客户来源']];
        $toMonthTime = strtotime($times[1]);
        $time = strtotime($times[0]);
        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),
            'times' => implode(' - ', $times),
            'type' => $type,
            'staff_id' => $staff_id,
        ]);
        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()]);
    }

}