hytc-admin/application/admin/controller/qingdongams/statistic/Customers.php

<?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;
    }
}