crm/app/command/AsyncTask.php

<?php

namespace app\command;

use app\logics\CustomerLogic;
use app\model\Activity;
use app\model\AsyncTask as ModelAsyncTask;
use app\model\Company;
use app\model\Customer;
use app\model\CustomerDropPool;
use app\model\CustomerPackage;
use app\model\CustomerPortraitField;
use app\model\CustomerRecycle;
use app\model\CustomerSource;
use app\model\CustomerVisitLog;
use app\model\Employee;
use app\model\Org;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\facade\Log;

class AsyncTask extends Command
{
    protected function configure()
    {
        $this->setName('asyncTask')
            ->setDescription('Async deal data');
    }

    protected function execute(Input $input, Output $output)
    {
        Log::close(NULL);
        // 查询异步处理表
        $dealData = ModelAsyncTask::where(['state' => 0])->find();
        if (empty($dealData)) return $output->writeln("没有要执行的任务");
        $method = $dealData->method;
        if (method_exists($this, $method)) {
            $rs = $this->$method($dealData, $output);
        }

        $dealData->complete_time = time();
        $dealData->state = 1;
        $dealData->result = $rs;
        $dealData->save();
        $output->writeln("异步任务[id]：" . $dealData->id . '处理完成');
    }

    /**
     * csv格式导出
     *
     * @param [type] $dealData
     * @param [type] $output
     * @return void
     */
    public function statisticsExport($dealData, $output)
    {
        $header = $this->getStatisticsHeader();
        $row = [];
        $title = [];
        foreach ($header['header1'] as $item) {
            if (isset($item['colspan'])) {
                for ($s = 0; $s < $item['colspan']; $s++) {
                    $h2 = array_shift($header['header2']);
                    $title[] = $h2['title'];
                }
            } else {
                $title[] = $item['title'];
            }
            if (isset($item['child'])) {
                foreach ($item['child'] as $k) {
                    $row[] = $k;
                }
            } else {
                $row[] = $item['field'];
            }
        }
        $filename = uniqid();
        $fp = fopen('./download/' . $filename . '.csv', 'w');
        fputcsv($fp, $title);
        $page = 1;
        $z = 2;
        do {
            $data = $this->getStatisticsData($dealData->root_id, $dealData->params, $page, 100);
            foreach ($data as $r => $rowData) {
                $r += $z + ($page - 1) * 100;
                $output->writeln("$r");
                $d = [];
                foreach ($row as $field) {
                    $d[] = isset($rowData[$field]) ? $rowData[$field] : '';
                }
                fputcsv($fp, $d);
            }
            $page++;
        } while (!empty($data));
        fclose($fp);

        $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传中');
        // 文件上传
        $rs = ossUpload('statisticsExport/' . $filename . '.csv', './download/' . $filename . '.csv');
        if (!$rs) $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传失败');
        else {
            $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传完毕');
            // 删除本地文件
            unlink('./download/' . $filename . '.csv');
        }



        return $filename . '.csv';
    }

    public function getStatisticsHeader()
    {
        // 第二行头部
        $header2 = [];
        // 设计师部门插入头部
        $header2[] = ["field" => 'customer_name', "title" => '客户名字', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'phone', "title" => '客户电话', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext39', "title" => '省市区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'community_name', "title" => '楼盘', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext42', "title" => '门牌单元号', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'square', "title" => '面积', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_name', "title" => '业务员', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org', "title" => '业务部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader', "title" => '业务经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level4', "title" => '业务中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level4', "title" => '业务总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level3', "title" => '区中区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level3', "title" => '区中区副总', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level2', "title" => '大区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level2', "title" => '大区副总', "align" => 'center', "class" => "bgEEECE1"];

        $header2[] = ["field" => 'designer_name', "title" => '设计师', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org', "title" => '设计部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader', "title" => '设计经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org_level2', "title" => '设计中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level2', "title" => '设计总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level1', "title" => '设计副总', "align" => 'center', "class" => "bgEEECE1"];

        // 加微
        $header2[] = ["field" => 'house_type1', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type2', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type3', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];

        // 建群
        $header2[] = ["field" => 'ext35_1', "title" => '1对1小群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_2', "title" => '临时大群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_3', "title" => '社群', "align" => 'center', "class" => "bgEEECE1"];

        // 直播
        $header2[] = ["field" => 'ext36_1', "title" => '1对1业务直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_1', "title" => '业务员', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_2', "title" => '1对1设计直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_2', "title" => '设计师', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_3', "title" => '一对多直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_3', "title" => '直播人员', "align" => 'center', "class" => "bgF2DCDB"];

        // 量房验房
        $header2[] = ["field" => 'measure_type2', "title" => '量房验房报告', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'measure_type3', "title" => '客户需求表', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'leader_visit', "title" => '经理是否回访', "align" => 'center', "class" => "bgEEECE1"];

        // 到店
        $header2[] = ["field" => 'outfield_activities', "title" => '外场活动到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'outfield_infield', "title" => '内场直接到店', "align" => 'center', "class" => "bgEEECE1"];
        //见面方式
        $header2[] = ["field" => 'actmeet_type1', "title" => '外场活动', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'actmeet_type2', "title" => '茶楼见面', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'actmeet_type3', "title" => '网谈登记', "align" => 'center', "class" => "bgEEECE1"];
        // 管理到店数据
        $header2[] = ["field" => 'deposit_money2', "title" => '线上定金', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'deposit_money1', "title" => '线下定金', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'package', "title" => '爆品定金（10%以上）', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'measure_type1', "title" => '量房验房', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number1', "title" => '一次到店<br>(内场到店)', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number2', "title" => '二次到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number3', "title" => '三次及以上到店', "align" => 'center', "class" => "bgEEECE1"];


        // 插入销售部门
        $employeeChild = ['employee_name', 'employee_org', 'employee_leader', 'employee_org_level4', 'employee_leader_level4', 'employee_org_level3', 'employee_leader_level3', 'employee_org_level2', 'employee_leader_level2'];
        // 设计部门
        $designerChild = ['designer_name', 'designer_org', 'designer_leader', 'designer_org_level2', 'designer_leader_level2', 'designer_leader_level1'];

        // 第一行头部
        $header1 = [];
        $header1[] = ["type" => 'key', "field" => 'key', "title" => '序号', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'date', "title" => '日期', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['customer_name', 'phone', 'ext39', 'community_name', 'ext42', 'square'], "title" => '客户信息 ', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'company_name', "title" => '公司', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => 'leader', "title" => '负责人', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => "", "child" => $employeeChild, "title" => '营销', "align" => 'center', "colspan" => 9, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => $designerChild, "title" => '设计', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['house_type1', 'delivery_month1_1', 'delivery_month1_2', 'delivery_month1_3', 'delivery_month1_4'], "title" => '常规加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type2', 'delivery_month2_1', 'delivery_month2_2', 'delivery_month2_3', 'delivery_month2_4'], "title" => '社群加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type3', 'delivery_month3_1', 'delivery_month3_2', 'delivery_month3_3', 'delivery_month3_4'], "title" => '历史加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "add_wechat_time",  "title" => '加微时间', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "plan_month_day",  "title" => '高意向到店客户<br>（1个月内装修）', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext35_1', 'ext35_2', 'ext35_3'], "title" => '建群', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext36_1', 'ext37_1', 'ext36_2', 'ext37_2', 'ext36_3', 'ext37_3'], "title" => '直播', "align" => 'center', "colspan" => 6, "class" => "bgF2DCDB"];
        $header1[] = ["field" => "", "child" => ['measure_type2', 'measure_type3', 'leader_visit'], "title" => '量房验房', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => '', "child" => ['outfield_activities', 'visit_number1'], "title" => '到店', "align" => 'center', "colspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => '', "child" => ['actmeet_type1', 'actmeet_type2', 'actmeet_type3'], "title" => '见面方式', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => '', "child" => ['deposit_money2', 'deposit_money1', 'package', 'measure_type1', 'visit_number1', 'visit_number2', 'visit_number3'], "title" => '管理到店数据', "align" => 'center', "colspan" => 7, "class" => "bgFDE9D9"];

        $header1[] = ["field" => 'number_of_visitors', "title" => '到店人数', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => 'signature_money', "title" => '合同价', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];

        return ['header1' => $header1, 'header2' => $header2];
    }

    public function getStatisticsData($root_id, $param, $page, $limit)
    {
        // 获取部门id
        $orgs = Org::where([['path', 'like', $root_id . '-%']])->column('name,level,path', 'id');
        // 部门领导获取
        $leaders = Employee::field('id,org_id,name')->with(['org' => function ($query) {
            $query->field('id,name,level,path');
        }])->where([['root_id', '=', $root_id], ['state', '=', '在职'], ['is_manager', '=', '1'], ['uid', '<>', 0]])->select();
        // 领导结构修改
        $leadersData = [];
        foreach ($leaders as $l) {
            if (isset($leadersData[$l['org_id']])) {
                $leadersData[$l['org_id']]['leaders'][] = $l['name'];
                $leadersData[$l['org_id']]['leaders_ids'][] = $l['id'];
            } else {
                $leadersData[$l['org_id']] = [
                    'org_name' => $l['org']['name'],
                    'level' => $l['org']['level'],
                    'path' => $l['org']['path'],
                    'leaders_ids' => [
                        $l['id']
                    ],
                    'leaders' => [
                        $l['name']
                    ]
                ];
            }
        }
        // 补充没领导的部门
        foreach ($orgs as $k => $item) {
            if (!isset($leadersData[$k])) {
                $leadersData[$k] = [
                    'org_name' => $item['name'],
                    'level' => $item['level'],
                    'path' => $item['path'],
                    'leaders' => [],
                    'leaders_ids' => []
                ];
            }
        }

        // 查询最近访问的客户
        $where = [];
        if (isset($param['org_id']) && !empty($param['org_id'])) {
            $where[] = ['org_id', '=', $param['org_id']];
        } else {
            $where[] = ['org_id', 'in', array_keys($orgs)];
        }

        if (isset($param['sdate']) && !empty($param['sdate'])) {
            $param['sdate'] = date('Y-m-d', strtotime($param['sdate']));
            $where[] = ['addtime', '>=', $param['sdate'] . ' 00:00:00'];
        }
        if (isset($param['edate']) && !empty($param['edate'])) {
            $param['edate'] = date('Y-m-d', strtotime($param['edate']));
            $where[] = ['addtime', '<=', $param['edate'] . ' 23:59:59'];
        }

        // 查询排除的部门
        $sheji = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '设计部']])->value('id');
        $xiaoshou = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '市场部']])->value('id');
        $fanweinei = Org::whereOr([
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $sheji . '-%']],
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $xiaoshou . '-%']],
        ])->column('id');
        $where[] = ['org_id', 'in', $fanweinei];
        //2022-10-31辛居尚重11月1号开始正式使用
        $where[] = ['addtime', '>', date('2022-10-31 23:59:59')];
        if (!empty($param['add_wechat_time'])) {
            $newtime = explode(' - ', $param['add_wechat_time']);
            $sdate = strtotime($newtime[0]);
            $edate = strtotime($newtime[1]);
            $addwechatCustomers = Customer::where([['ext', 'not null', ''], ['ext', '<>', ''], ['add_wechat_time', '>=', date('Y-m-d', $sdate)], ['add_wechat_time', '<=', date('Y-m-d', $edate)]])->column('id');

            $where[] = ['customer_id', 'in', $addwechatCustomers];
        }
        $searchObject = CustomerVisitLog::field("customer_id, DATE_FORMAT(addtime,'%Y-%m-%d') as date, max(addtime) as addtime, SUM(number_of_visitors) as number_of_visitors, SUM(measure_room_img_type='量房图片') as measure_type1, SUM(measure_room_img_type='量房报告图片') as measure_type2, SUM(measure_room_img_type='客户需求表') as measure_type3,GROUP_CONCAT(aid) as aid_list")->where($where)->order('date desc,addtime desc')->group('customer_id,date');

        $searchObject->page($page, $limit);
        $lastVisitCustomerList = $searchObject->select();

        // 获取客户信息
        $customerIdList = array_unique(array_column(empty($lastVisitCustomerList) ? [] : $lastVisitCustomerList->toArray(), 'customer_id'));
        $customerList = Customer::with(['employee', 'designer'])->where([['id', 'in', $customerIdList]])->field('id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();

        $customerList1 = CustomerRecycle::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList2 = CustomerDropPool::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList = array_merge($customerList->toArray(), $customerList1->toArray(), $customerList2->toArray());

        $customerList = array_combine(array_column($customerList, 'id'), $customerList);
        $customerList = $this->emp_extfield_change($customerList, $root_id);
        // 查询爆品产品
        $packageId = implode(',', array_column($customerList, 'package_id'));
        $package = CustomerPackage::where([['id', 'in', $packageId]])->column('total_price', 'id');

        // 获取公司名称
        $companyName = Company::where([['root_id', '=', $root_id]])->value('company_name');

        // 来源
        $sourseIdList = array_column($customerList, 'source_id');
        $sourseList = CustomerSource::where([['id', 'in', $sourseIdList]])->column('source', 'id');
        $num = ($page - 1) * $limit + 1;
        // 处理数据
        $data = [];
        foreach ($lastVisitCustomerList as $c) {
            $item = [
                'key' => $num,
                'customer_id' => $c['customer_id'],
            ];
            if (!isset($customerList[$c['customer_id']])) {
                $data[] = $item;
                $num++;
                continue;
            }
            list($Y, $m, $d) = explode('-', $c['date']);
            $item = [
                'key' => $num,
                'customer_name' => $customerList[$c['customer_id']]['name'],
                'date' => $Y . '年' . $m . '月' . $d . '日',
                'addtime' => $c['addtime'],
                'company_name' => $companyName,
                'leader' => '罗镐',
                'community_name' => $customerList[$c['customer_id']]['community_name'],
                'measure_room_store' => $this->measureRoomStore($c['date'], $c['customer_id']),
                'number_of_visitors' => $c['number_of_visitors'] == 0 ? '' : $c['number_of_visitors'],
                'area' => $c['area'],
                'ext39' =>   $customerList[$c['customer_id']]['ext']['house_location'],
                'talking_about_single_time' => isset($customerList[$c['customer_id']]['ext']['talking_about_single_time']) ? $customerList[$c['customer_id']]['ext']['talking_about_single_time'] : 0,
                'source' => isset($sourseList[$customerList[$c['customer_id']]['source_id']]) ? $sourseList[$customerList[$c['customer_id']]['source_id']] : '',
                'square' => $customerList[$c['customer_id']]['square'],
                'ext42' => $customerList[$c['customer_id']]['ext']['unit_number']
            ];
            //量房图片类型
            $where_img_type = [];
            $where_img_type[] = ['addtime', 'like', '%' . $c['date'] . '%'];
            $where_img_type[] = ['customer_id', '=', $c['customer_id']];
            $where_img_type[] = CustomerVisitLog::changeState(['state', '=', '确认量房']);
            $str = CustomerVisitLog::where($where_img_type)->column('measure_room_img_type');
            $str = $str ? implode(',', $str) : '';
            $item['measure_type2'] = ($str && (strpos($str, '量房图片') !== false || strpos($str, '量房报告图片') !== false || strpos($str, '量房验房报告') !== false)) ? '√' : '';
            $item['measure_type3'] = ($str && strpos($str, '客户需求图片') !== false) ? '√' : '';
            // 计算交房相差月份
            $houseDeliveryTime = strtotime($customerList[$c['customer_id']]['house_delivery_time']);
            $month = (date('Y', $houseDeliveryTime) - date('Y')) * 12 + date('m', $houseDeliveryTime) - date('m');

            $type = '0';
            $item['add_wechat_time'] = $customerList[$c['customer_id']]['ext']['add_wechat_time'];
            if ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '常规加微') {
                $type = '1';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '社群加微') {
                $type = '2';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '历史加微') {
                $type = '3';
            }

            if ($type !== '0') {
                $item['house_type' . $type] = in_array($customerList[$c['customer_id']]['house_type'], ['现房', '二手房']) ? '√' : '';
                if ($item['house_type' . $type] != '√' && !empty($customerList[$c['customer_id']]['house_delivery_time'])) {
                    $item['delivery_month' . $type . '_1'] = $month <= 3 ? '√' : '';
                    $item['delivery_month' . $type . '_2'] = $month > 3 && $month <= 6 ? '√' : '';
                    $item['delivery_month' . $type . '_3'] = $month > 6 && $month <= 12 ? '√' : '';
                    $item['delivery_month' . $type . '_4'] = $month > 12 ? '√' : '';
                } else {
                    $item['delivery_month' . $type . '_1'] = '';
                    $item['delivery_month' . $type . '_2'] = '';
                    $item['delivery_month' . $type . '_3'] = '';
                    $item['delivery_month' . $type . '_4'] = '';
                }
            }

            // 设计师
            if (!empty($customerList[$c['customer_id']]['designer'])) {
                $designerOrgId = $customerList[$c['customer_id']]['designer']['org_id'];
                $item['designer_name'] = $customerList[$c['customer_id']]['designer']['name'];
                $item['designer_org'] = $orgs[$designerOrgId]['name'];
                $item['designer_leader'] = isset($leadersData[$designerOrgId]) ? implode(',', $leadersData[$designerOrgId]['leaders']) : '';
                $path = $leadersData[$designerOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '部') !== false && $leadersData[$od]['org_name'] != '设计部') {
                        $level = 3;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    $item['designer_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['designer_org_level' . $level] = $leadersData[$od]['org_name'];
                }
            } else {
                $item['designer_name'] = $item['designer_org'] = $item['designer_leader'] = '';
            }

            // 员工
            if (!empty($customerList[$c['customer_id']]['employee'])) {
                $employeeOrgId = $customerList[$c['customer_id']]['employee']['org_id'];
                $item['employee_name'] = $customerList[$c['customer_id']]['employee']['name'];
                $item['employee_org'] = $orgs[$employeeOrgId]['name'];
                $item['employee_leader'] = implode(',', $leadersData[$employeeOrgId]['leaders']);

                $path = $leadersData[$employeeOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '区中区') !== false) {
                        $level = 3;
                    } elseif (strstr($leadersData[$od]['org_name'], '中心') !== false) {
                        $level = 4;
                    } elseif (strstr($leadersData[$od]['org_name'], '区') !== false) {
                        $level = 2;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    // $level = $leadersData[$od]['level'];
                    $item['employee_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['employee_org_level' . $level] = $leadersData[$od]['org_name'];
                }
                // 查询直属领导是否跟踪过
                // 获取直属领导id
                $leaderIdList = $leadersData[$employeeOrgId]['leaders_ids'];

                $query = [];
                $query[] = ['employee_id', 'in', $leaderIdList];
                $query[] = ['addtime', 'like', $c['date'] . '%'];
                $query[] = ['customer_id', '=', $c['customer_id']];
                $item['leader_visit'] = !CustomerVisitLog::where($query)->findOrEmpty()->isEmpty() && ($item['measure_type2'] == '√' || $item['measure_type3'] == '√') ? "√" : "";
            } else {
                $item['employee_name'] = $item['employee_org'] = $item['employee_leader'] = '';
                $item['leader_visit'] = '';
            }

            // 量房验房
            $item['measure_type1'] = ($item['leader_visit'] == '√' || $item['measure_type2'] == '√' || $item['measure_type3'] == '√') ? '√' : '';

            // 手机号
            $phoneArr = array_filter([$customerList[$c['customer_id']]['phone'], $customerList[$c['customer_id']]['phone1'], $customerList[$c['customer_id']]['phone2']]);
            $item['phone'] = substr_replace(array_shift($phoneArr), '******', 3, 6);

            // 建群情况
            $jianqun = explode(',', $customerList[$c['customer_id']]['ext']['group_building']);
            $item['ext35_1'] = in_array('1对1小群', $jianqun) || in_array('一对一小群', $jianqun) ? '√' : '';
            $item['ext35_2'] = in_array('临时大群', $jianqun) ? '√' : '';
            $item['ext35_3'] = in_array('社群', $jianqun) ? '√' : '';

            // 直播
            //$zhibo = explode(',', $customerList[$c['customer_id']]['ext']['直播情况']);
            $zhibo = explode(',', $customerList[$c['customer_id']]['ext']['live_broadcast']);
            $item['ext36_1'] = in_array('一对一直播', $zhibo) ||  in_array('1对1业务直播', $zhibo) ? '√' : '';
            $item['ext36_2'] = in_array('1对1设计直播', $zhibo) ? '√' : '';
            $item['ext36_3'] = in_array('一对多直播', $zhibo) ? '√' : '';
            $item['ext37_1'] = $item['ext36_1'] == '√' ? $customerList[$c['customer_id']]['ext']['live_broadcast_business'] : '';
            $item['ext37_2'] = $item['ext36_2'] == '√' ? $customerList[$c['customer_id']]['ext']['live_broadcast_design'] : '';
            $item['ext37_3'] = $item['ext36_3'] == '√' ? $customerList[$c['customer_id']]['ext']['live_broadcast_personnel'] : '';


            // 签单金额
            $condition = [];
            $condition[] = ['addtime', 'like', $c['date'] . '%'];
            $condition[] = ['customer_id', '=', $c['customer_id']];
            $condition1 = $condition;
            $condition2 = $condition;
            $condition1[] = CustomerVisitLog::changeState(['state', '=', '已交定']);
            $condition2[] = CustomerVisitLog::changeState(['state', '=', '已签单']);
            $item['signature_money'] = CustomerVisitLog::where($condition2)->order('addtime desc')->value('money');
            $deposit = CustomerVisitLog::where($condition1)->order('addtime desc')->field('money,deposit_mode')->find();
            if (!empty($deposit)) {
                $deposit['deposit_mode'] !== 0 ? $item['deposit_money1'] = $deposit['money'] : $item['deposit_money2'] = $deposit['money'];
                // 爆品定金
                if ($customerList[$c['customer_id']]['package_id']) {
                    $packageIdList = explode(',', $customerList[$c['customer_id']]['package_id']);
                    $totalMoney = 0;
                    foreach ($packageIdList as $pid) {
                        $totalMoney += $package[$pid];
                    }
                    if ($totalMoney !== 0) {
                        $item['package'] = $deposit['money'] / $totalMoney * 100 >= 10 ? '√' : '';
                    }
                }
            }

            // 预计装修时间 -> 高意向到店客户
            if (!empty($customerList[$c['customer_id']]['plan_deco_time'])) {
                $planDecoTime = strtotime($customerList[$c['customer_id']]['plan_deco_time']);
                $item['plan_month_day'] = ($planDecoTime - time()) / 86400 < 30 ? '√' : '';
            }
            //活动见面类型
            $query_actmet = [];
            $query_actmet[] = ['addtime', 'like', $c['date'] . '%'];
            $query_actmet[] = ['customer_id', '=', $c['customer_id']];
            $query_actmet[] = ['actmeet_type', '<>', 0];
            $query_actmet[] = CustomerVisitLog::changeState(['state', '=', '已到场']);
            $actmet = CustomerVisitLog::where($query_actmet)->order('addtime desc')->value('actmeet_type');
            if (!empty($actmet)) {
                $item['actmeet_type1'] = ($actmet == 1) ? '√' : '';
                $item['actmeet_type2'] = ($actmet == 2) ? '√' : '';
                $item['actmeet_type3'] = ($actmet == 3) ? '√' : '';
            } else {
                $item['actmeet_type1'] = '';
                $item['actmeet_type2'] = '';
                $item['actmeet_type3'] = '';
            }

            // 到店次数
            $where = [];
            $where[] = ['addtime', '<', $c['date'] . ' 23:59:59'];
            $where[] = ['customer_id', '=', $c['customer_id']];
            $state1 = CustomerVisitLog::changeState('确定到店', 'chaos');
            $where[] = ['state', 'in', $state1];
            $count = CustomerVisitLog::where($where)->count();
            $item['visit_number1'] = $count == 1 ? '√' : '';
            $item['visit_number2'] = $count == 2 ? '√' : '';
            $item['visit_number3'] = $count >= 3 ? '√' : '';

            // 外场活动名
            $aidList = array_unique(explode(',', $c['aid_list']));
            $huodong = Activity::where([['id', 'in', $aidList], ['cate', '=', 1]])->column('title,address');
            $huodongList = [];
            foreach ($huodong as $h) {
                if ($h['address']) {
                    $huodongList[] = $h['title'] . '(' . $h['address'] . ')';
                } else {
                    $huodongList[] = $h['title'];
                }
            }

            $daochang = CustomerVisitLog::where([['customer_id','=',$c['customer_id']],['addtime','between',[$c['date'].' 00:00:00',$c['date'].' 23:59:59']],['state','in',CustomerVisitLog::changeState('已到场','chaos')]])->count();
            $item['outfield_activities'] = $daochang ? implode(',', $huodongList) : '';
            // 外场活动转内场新客户 活动前未到店以及后续状态，参加活动后，第一次到店显示
            $item['outfield_infield'] = $this->getIsNewCusomerVisitAfterActivity($c['date'], $c['customer_id']);
            $data[] = $item;
            $num++;
        }
        return $data;
    }

    //客户扩展字段转换
    public function emp_extfield_change($customerList, $root_id)
    {
        $chmod = new CustomerLogic;
        $list = CustomerPortraitField::with(['select'])->where([['root_id', '=', $root_id], ['pid', '<>', 0]])->select()->toArray();
        foreach ($customerList as $key => $val) {
            $new = '';
            if (!empty($val['ext']) && $val['ext'] != 'null') {
                $extdata = json_decode($val['ext'], true);
                if (isset($extdata['ext1'])) {
                    $new = $chmod->old_data_saves($val, $list);
                } else {
                    $new = $chmod->new_data_saves($val, $extdata, $list);
                }
            } else {
                $new = $chmod->no_ext_saves($val, $list);
            }
            $new = array_combine(array_column($new, 'keyname'), array_column($new, 'valname'));
            $customerList[$key]['ext'] = $new;
        }
        return $customerList;
    }


    /**
     * 检测客户是否是外场转内场新客户
     */
    public function getIsNewCusomerVisitAfterActivity($date, $customerId)
    {
        $where = [];
        $where[] = ['addtime', '<', $date . ' 23:59:59'];
        $where[] = ['customer_id', '=', $customerId];
        $state1 = CustomerVisitLog::changeState('已到店', 'chaos');
        $where[] = ['state', 'in', $state1];
        // 查询日期必须到店
        $had = CustomerVisitLog::where($where)->order('addtime desc')->column('addtime, aid');
        // 是否多次到店或未到店
        if (count($had) != 1) return '';

        $lastTime = strtotime($had[0]['addtime']);
        // 判断到店日期是否是所选日期
        if (date('Y-m-d', $lastTime) != $date) return '';
        // 判断是否参加过活动
        $aidExit = CustomerVisitLog::where([
            ['addtime', '<', $date . ' 23:59:59'],
            ['customer_id', '=', $customerId]
        ])->order('addtime desc')->column('aid');

        if (empty($aidExit)) return '';
        $nei = Activity::where([['id', 'in', $aidExit], ['cate', '=', 1]])->count();

        if ($nei) return '√';

        return '';
    }

    /**
     * 量房转到店
     */
    public function measureRoomStore($date, $customer_id)
    {
        $where = [];
        //单日内先量房，在到店
        $where[] = ['addtime', 'like', '%' . $date . '%'];
        $where[] = ['customer_id', '=', $customer_id];
        $state1 = CustomerVisitLog::changeState('确认量房', 'chaos');
        $state2 = CustomerVisitLog::changeState('确认到店', 'chaos');
        $where[] = ['state', 'in', array_merge($state1, $state2)];

        $arr = CustomerVisitLog::where($where)->order('id asc')->column('id,state');

        $measure = $store = 0;
        foreach ($arr as $k => $v) {
            if ($measure == 0 && in_array($v['state'], $state1)) {
                $measure = $v['id'];
            }
            if ($store == 0 && in_array($v['state'], $state2)) {
                $store = $v['id'];
            }
        }
        return ($measure > 0 && $store > 0 && $measure < $store) ? '√' : '';
    }

    /**
     * 新表的csv格式导出
     *
     * @param [type] $dealData
     * @param [type] $output
     * @return void
     */
    public function newStatisticsExport($dealData, $output)
    {
        $header = $this->getNewVisitLogHeaders();
        $row = [];
        $title = [];
        foreach ($header['header1'] as $item) {
            if (isset($item['colspan'])) {
                for ($s = 0; $s < $item['colspan']; $s++) {
                    $h2 = array_shift($header['header2']);
                    $title[] = $h2['title'];
                }
            } else {
                $title[] = $item['title'];
            }
            if (isset($item['child'])) {
                foreach ($item['child'] as $k) {
                    $row[] = $k;
                }
            } else {
                $row[] = $item['field'];
            }
        }
        $filename = uniqid();
        $fp = fopen('./download/' . $filename . '.csv', 'w');
        fputcsv($fp, $title);
        $page = 1;
        $z = 2;
        do {
            $data = $this->new_customer_statistics($dealData->root_id, $dealData->params, $page, 100);
            foreach ($data as $r => $rowData) {
                $r += $z + ($page - 1) * 100;
                $output->writeln("$r");
                $d = [];
                foreach ($row as $field) {
                    $d[] = isset($rowData[$field]) ? $rowData[$field] : '';
                }
                fputcsv($fp, $d);
            }
            $page++;
        } while (!empty($data));
        fclose($fp);

        $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传中');
        // 文件上传
        $rs = ossUpload('statisticsExport/' . $filename . '.csv', './download/' . $filename . '.csv');
        if (!$rs) $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传失败');
        else $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传完毕');

        return $filename . '.csv';
    }
    /**
     * 获取线上跟踪客户记录表头
     */
    private function getNewVisitLogHeaders()
    {
        // 第二行头部
        $header2 = [];
        // 设计师部门插入头部
        $header2[] = ["field" => 'customer_name', "title" => '客户名字', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'phone', "title" => '客户电话', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext39', "title" => '省市区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'community_name', "title" => '楼盘', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext42', "title" => '门牌单元号', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'square', "title" => '面积', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_name', "title" => '业务员', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org', "title" => '业务部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader', "title" => '业务经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level4', "title" => '业务中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level4', "title" => '业务总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level3', "title" => '区中区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level3', "title" => '区中区副总', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level2', "title" => '大区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level2', "title" => '大区副总', "align" => 'center', "class" => "bgEEECE1"];

        $header2[] = ["field" => 'designer_name', "title" => '设计师', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org', "title" => '设计部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader', "title" => '设计经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org_level2', "title" => '设计中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level2', "title" => '设计总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level1', "title" => '设计副总', "align" => 'center', "class" => "bgEEECE1"];

        // 加微
        $header2[] = ["field" => 'house_type1', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type2', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type3', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];

        // 建群
        $header2[] = ["field" => 'ext35_1', "title" => '1对1小群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_2', "title" => '临时大群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_3', "title" => '社群', "align" => 'center', "class" => "bgEEECE1"];

        // 直播
        $header2[] = ["field" => 'ext36_1', "title" => '1对1业务直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_1', "title" => '业务员', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_2', "title" => '1对1设计直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_2', "title" => '设计师', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_3', "title" => '一对多直播', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext37_3', "title" => '直播人员', "align" => 'center', "class" => "bgF2DCDB"];

        // 量房验房
        $header2[] = ["field" => 'measure_type2', "title" => '量房验房报告', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'measure_type3', "title" => '客户需求表', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'leader_visit', "title" => '经理是否回访', "align" => 'center', "class" => "bgEEECE1"];
        // 到店
        $header2[] = ["field" => 'outfield_activities', "title" => '外场活动到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'outfield_infield', "title" => '内场直接到店', "align" => 'center', "class" => "bgEEECE1"];
        //见面方式
        $header2[] = ["field" => 'actmeet_type1', "title" => '外场活动', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'actmeet_type2', "title" => '茶楼见面', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'actmeet_type3', "title" => '网谈登记', "align" => 'center', "class" => "bgEEECE1"];
        // 管理到店数据
        $header2[] = ["field" => 'deposit_money2', "title" => '线上定金', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'deposit_money1', "title" => '线下定金', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'djzdd', "title" => '定金客户转到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'package', "title" => '爆品定金（10%以上）', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'measure_type1', "title" => '量房验房', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'lfzdd', "title" => '量房客户转到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number1', "title" => '一次到店<br>(内场到店)', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number2', "title" => '二次到店', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'visit_number3', "title" => '三次及以上到店', "align" => 'center', "class" => "bgEEECE1"];


        // 插入销售部门
        $employeeChild = ['employee_name', 'employee_org', 'employee_leader', 'employee_org_level4', 'employee_leader_level4', 'employee_org_level3', 'employee_leader_level3', 'employee_org_level2', 'employee_leader_level2'];
        // 设计部门
        $designerChild = ['designer_name', 'designer_org', 'designer_leader', 'designer_org_level2', 'designer_leader_level2', 'designer_leader_level1'];

        // 第一行头部
        $header1 = [];
        $header1[] = ["type" => 'key', "field" => 'key', "title" => '序号', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'date', "title" => '日期', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['customer_name', 'phone', 'ext39', 'community_name', 'ext42', 'square'], "title" => '客户信息 ', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'company_name', "title" => '公司', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => 'leader', "title" => '负责人', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => "", "child" => $employeeChild, "title" => '营销', "align" => 'center', "colspan" => 9, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => $designerChild, "title" => '设计', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['house_type1', 'delivery_month1_1', 'delivery_month1_2', 'delivery_month1_3', 'delivery_month1_4'], "title" => '常规加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type2', 'delivery_month2_1', 'delivery_month2_2', 'delivery_month2_3', 'delivery_month2_4'], "title" => '社群加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type3', 'delivery_month3_1', 'delivery_month3_2', 'delivery_month3_3', 'delivery_month3_4'], "title" => '历史加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "add_wechat_time",  "title" => '加微时间', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "plan_month_day",  "title" => '高意向到店客户<br>（1个月内装修）', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext35_1', 'ext35_2', 'ext35_3'], "title" => '建群', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext36_1', 'ext37_1', 'ext36_2', 'ext37_2', 'ext36_3', 'ext37_3'], "title" => '直播', "align" => 'center', "colspan" => 6, "class" => "bgF2DCDB"];
        $header1[] = ["field" => "", "child" => ['measure_type2', 'measure_type3', 'leader_visit'], "title" => '量房验房', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        //$header1[] = ["field" => "yylf",  "title" => '预约量房', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        //$header1[] = ["field" => "yydd",  "title" => '预约到店', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        //$header1[] = ["field" => "yydc",  "title" => '预约到场', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => '', "child" => ['outfield_activities', 'outfield_infield'], "title" => '到店', "align" => 'center', "colspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => '', "child" => ['actmeet_type1', 'actmeet_type2', 'actmeet_type3'], "title" => '见面方式', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => '', "child" => ['deposit_money2', 'deposit_money1', 'djzdd', 'package', 'measure_type1', 'lfzdd', 'visit_number1', 'visit_number2', 'visit_number3'], "title" => '管理到店数据', "align" => 'center', "colspan" => 9, "class" => "bgFDE9D9"];

        $header1[] = ["field" => 'number_of_visitors', "title" => '到店人数', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => 'signature_money', "title" => '合同价', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => 'wczdd', "title" => '外场转内场到店', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];
        $header1[] = ["field" => 'order_time', "title" => '谈单时长', "align" => 'center', "rowspan" => 2, "class" => "bgFDE9D9"];
        return ['header1' => $header1, 'header2' => $header2];
    }
    /**
     * 统计部报表数据统计
     */
    public function new_customer_statistics($root_id, $param, $page, $limit)
    {
        //$param = $this->request->only(['page' => 1, 'limit' => 10, 'sdate' => '', 'edate' => '', 'org_id' => 0, 'add_wechat_time']);

        // 获取部门id
        //$root_id = request()->employee->root_id;
        $orgs = Org::where([['path', 'like', $root_id . '-%']])->column('name,id,pid,level,path', 'id');
        // 部门领导获取
        $leaders = Employee::with('org')->where([['root_id', '=', $root_id], ['state', '=', '在职'], ['is_manager', '=', '1'], ['uid', '<>', 0]])->select();
        // 领导结构修改
        $leadersData = [];
        foreach ($leaders as $l) {
            if (isset($leadersData[$l['org_id']])) {
                $leadersData[$l['org_id']]['leaders'][] = $l['name'];
                $leadersData[$l['org_id']]['leaders_ids'][] = $l['id'];
            } else {
                $leadersData[$l['org_id']] = [
                    'org_name' => $l['org']['name'],
                    'level' => $l['org']['level'],
                    'path' => $l['org']['path'],
                    'leaders_ids' => [
                        $l['id']
                    ],
                    'leaders' => [
                        $l['name']
                    ]
                ];
            }
        }
        // 补充没领导的部门
        foreach ($orgs as $k => $item) {
            if (!isset($leadersData[$k])) {
                $leadersData[$k] = [
                    'org_name' => $item['name'],
                    'level' => $item['level'],
                    'path' => $item['path'],
                    'leaders' => [],
                    'leaders_ids' => []
                ];
            }
        }

        // 查询最近访问的客户
        $where = [];
        if ($param['org_id']) {
            //查询本部门下面是否有部门
            $childorg = orgSubIds($param['org_id']);
            $where[] = ['org_id|customer_org_id', 'in', $childorg];
            // $where[] = ['org_id', '=', $param['org_id']];
        } else {
            $where[] = ['org_id', 'in', array_keys($orgs)];
        }

        if ($param['sdate']) {
            $param['sdate'] = date('Y-m-d', strtotime($param['sdate']));
            $where[] = ['addtime', '>=', $param['sdate'] . ' 00:00:00'];
        }
        if ($param['edate']) {
            $param['edate'] = date('Y-m-d', strtotime($param['edate']));
            $where[] = ['addtime', '<=', $param['edate'] . ' 23:59:59'];
        }

        // 查询排除的部门
        $sheji = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '设计部']])->value('id');
        $xiaoshou = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '市场部']])->value('id');
        $fanweinei = Org::whereOr([
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $sheji . '-%']],
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $xiaoshou . '-%']],
        ])->column('id');
        $searchEmployeeIdList = Employee::where([['org_id', 'in', $fanweinei]])->column('id');
        //如果有部门筛选把员工控制在本部门
        if ($param['org_id']) $searchEmployeeIdList = Employee::where([['org_id', 'in', $childorg]])->column('id');
        //$where[] = ['employee_id', 'in', $searchEmployeeIdList];
        $where[] = ['customer_employee_id', 'in', $searchEmployeeIdList];
        //2022-10-31辛居尚重11月1号开始正式使用
        $where[] = ['addtime', '>', date('2022-10-31 23:59:59')];
        //新增加查询记录到前一天的24点
        $sel_endtime = date('Y-m-d');
        if (empty($param['edate']) && empty($param['sdate'])) $where[] = ['addtime', '<', date('Y-m-d', strtotime("$sel_endtime -1 days")) . ' 23:59:59'];
        if (!empty($param['add_wechat_time'])) {
            $newtime = explode(' - ', $param['add_wechat_time']);
            $sdate = strtotime($newtime[0]);
            $edate = strtotime($newtime[1]);
            $wechat_count = [];

            $wechat_count = Customer::where([['ext', 'not null', ''], ['ext', '<>', ''], ['add_wechat_time', '>=', date('Y-m-d', $sdate)], ['add_wechat_time', '<=', date('Y-m-d', $edate)]])->column('id');

            $cids = CustomerVisitLog::where($where)->group('customer_id')->column('customer_id');
            $wechat_count = array_intersect($cids, $wechat_count);
            $where[] = ['customer_id', 'in', $wechat_count];
        }
        //去掉不需要的跟进记录
        $state1 = CustomerVisitLog::changeState('已到店','chaos');
        $state2 = CustomerVisitLog::changeState('已到场','chaos');
        $state3 = CustomerVisitLog::changeState('已量房','chaos');
        $state4 = CustomerVisitLog::changeState('已交定','chaos');
        $state5 = CustomerVisitLog::changeState('已签单','chaos');
        $where[] = ['state','in',array_merge($state1,$state2,$state3,$state4,$state5)];
        $total = CustomerVisitLog::field("customer_id, DATE_FORMAT(addtime,'%Y-%m-%d') as date")->where($where)->group('customer_id,date')->count();
        $searchObject = CustomerVisitLog::field("customer_id, DATE_FORMAT(addtime,'%Y-%m-%d') as date, max(addtime) as addtime, SUM(number_of_visitors) as number_of_visitors, SUM(measure_room_img_type='量房图片') as measure_type1, SUM(measure_room_img_type='量房报告图片') as measure_type2, SUM(measure_room_img_type='客户需求表') as measure_type3,GROUP_CONCAT(aid) as aid_list")->where($where)->order('date desc,addtime desc')->group('customer_id,date');


        $searchObject->page($page, $limit);
        $lastVisitCustomerList = $searchObject->select();

        // 获取客户信息
        $customerIdList = array_unique(array_column(empty($lastVisitCustomerList) ? [] : $lastVisitCustomerList->toArray(), 'customer_id'));
        $customerList = Customer::with(['employee', 'designer'])->where([['id', 'in', $customerIdList]])->field('id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();

        $customerList1 = CustomerRecycle::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList2 = CustomerDropPool::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList = array_merge($customerList->toArray(), $customerList1->toArray(), $customerList2->toArray());

        $customerList = array_combine(array_column($customerList, 'id'), $customerList);
        $customerList = $this->emp_extfield_change($customerList, $root_id);
        // 查询爆品产品
        $packageId = implode(',', array_column($customerList, 'package_id'));
        $package = CustomerPackage::where([['id', 'in', $packageId]])->column('total_price', 'id');

        // 获取公司名称
        $companyName = Company::where([['root_id', '=', $root_id]])->value('company_name');
        // 公司负责人获取
        // $companyLeader = Employee::where([['root_id', '=', $this->root_id], ['state', '=', '在职'], ['is_manager', '=', '1'], ['org_id', '=', $this->root_id], ['uid', '<>', 0]])->column('name');

        // 来源
        $sourseIdList = array_column($customerList, 'source_id');
        $sourseList = CustomerSource::where([['id', 'in', $sourseIdList]])->column('source', 'id');
        $num = ($page - 1) * $limit + 1;
        // 处理数据
        $data = [];
        foreach ($lastVisitCustomerList as $c) {
            $item = [
                'key' => $num,
                'customer_id' => $c['customer_id'],
            ];

            if (!isset($customerList[$c['customer_id']])) {
                $data[] = $item;
                $num++;
                continue;
            }
            list($Y, $m, $d) = explode('-', $c['date']);
            $item = [
                'key' => $num,
                'customer_name' => $customerList[$c['customer_id']]['name'],
                'date' => $Y . '年' . $m . '月' . $d . '日',
                'addtime' => $c['addtime'],
                'company_name' => $companyName,
                'leader' => '罗镐',
                'community_name' => $customerList[$c['customer_id']]['community_name'],
                'measure_room_store' => $this->measureRoomStore($c['date'], $c['customer_id']),
                'number_of_visitors' => $c['number_of_visitors'] == 0 ? '' : $c['number_of_visitors'],
                'area' => $c['area'],
                'ext39' =>   $customerList[$c['customer_id']]['ext']['house_location'],
                //'talking_about_single_time' => isset($customerList[$c['customer_id']]['ext']['谈单时长']) ? $customerList[$c['customer_id']]['ext']['谈单时长'] : 0,
                'talking_about_single_time' => isset($customerList[$c['customer_id']]['ext']['talking_about_single_time']) ? $customerList[$c['customer_id']]['ext']['talking_about_single_time'] : 0,
                'source' => isset($sourseList[$customerList[$c['customer_id']]['source_id']]) ? $sourseList[$customerList[$c['customer_id']]['source_id']] : '',
                'square' => $customerList[$c['customer_id']]['square'],
                //'ext42' => $customerList[$c['customer_id']]['ext']['门牌单元号']
                'ext42' => $customerList[$c['customer_id']]['ext']['unit_number']
            ];
            //量房图片类型
            $where_img_type = [];
            $where_img_type[] = ['addtime', 'like', '%' . $c['date'] . '%'];
            $where_img_type[] = ['customer_id', '=', $c['customer_id']];
            $where_img_type[] = CustomerVisitLog::changeState(['state', '=', '确认量房']);
            $str = CustomerVisitLog::where($where_img_type)->order('addtime desc')->column('measure_room_img_type', 'addtime');
            $measure_type2 = $measure_type3 = '';
            foreach ($str as $k => $v) {
                if ($v && (strpos($v, '量房图片') !== false || strpos($v, '量房报告图片') !== false || strpos($v, '量房验房报告') !== false)) {
                    $measure_type2 = $k;
                    break;
                }
                if ($v && (strpos($v, '客户需求图片') !== false)) {
                    $measure_type3 = $k;
                    break;
                }
            }
            $item['measure_type2'] = $measure_type2;
            $item['measure_type3'] = $measure_type3;
            $lastmeasure_time = CustomerVisitlog::where($where_img_type)->order('addtime desc')->value('addtime');
            // 计算交房相差月份
            $houseDeliveryTime = strtotime($customerList[$c['customer_id']]['house_delivery_time']);
            $month = (date('Y', $houseDeliveryTime) - date('Y')) * 12 + date('m', $houseDeliveryTime) - date('m');

            $type = '0';
            $item['add_wechat_time'] = $customerList[$c['customer_id']]['ext']['add_wechat_time'];
            if ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '常规加微') {
                $type = '1';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '社群加微') {
                $type = '2';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '历史加微') {
                $type = '3';
            }

            if ($type !== '0') {
                $item['house_type' . $type] = in_array($customerList[$c['customer_id']]['house_type'], ['现房', '二手房']) ? '√' : '';
                if ($item['house_type' . $type] != '√' && !empty($customerList[$c['customer_id']]['house_delivery_time'])) {
                    $item['delivery_month' . $type . '_1'] = $month <= 3 ? '√' : '';
                    $item['delivery_month' . $type . '_2'] = $month > 3 && $month <= 6 ? '√' : '';
                    $item['delivery_month' . $type . '_3'] = $month > 6 && $month <= 12 ? '√' : '';
                    $item['delivery_month' . $type . '_4'] = $month > 12 ? '√' : '';
                } else {
                    $item['delivery_month' . $type . '_1'] = '';
                    $item['delivery_month' . $type . '_2'] = '';
                    $item['delivery_month' . $type . '_3'] = '';
                    $item['delivery_month' . $type . '_4'] = '';
                }
            }

            // 设计师
            if (!empty($customerList[$c['customer_id']]['designer'])) {
                $designerOrgId = $customerList[$c['customer_id']]['designer']['org_id'];
                $item['designer_name'] = $customerList[$c['customer_id']]['designer']['name'];
                $item['designer_org'] = $orgs[$designerOrgId]['name'];
                $item['designer_leader'] = isset($leadersData[$designerOrgId]) ? implode(',', $leadersData[$designerOrgId]['leaders']) : '';
                $path = $leadersData[$designerOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '部') !== false && $leadersData[$od]['org_name'] != '设计部') {
                        $level = 3;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    $item['designer_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['designer_org_level' . $level] = $leadersData[$od]['org_name'];
                }
            } else {
                $item['designer_name'] = $item['designer_org'] = $item['designer_leader'] = '';
            }


            // 员工
            if (!empty($customerList[$c['customer_id']]['employee'])) {
                $employeeOrgId = $customerList[$c['customer_id']]['employee']['org_id'];
                $item['employee_name'] = $customerList[$c['customer_id']]['employee']['name'];
                $item['employee_org'] = $orgs[$employeeOrgId]['name'];
                $item['employee_leader'] = implode(',', $leadersData[$employeeOrgId]['leaders']);

                $path = $leadersData[$employeeOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '区中区') !== false) {
                        $level = 3;
                    } elseif (strstr($leadersData[$od]['org_name'], '中心') !== false) {
                        $level = 4;
                    } elseif (strstr($leadersData[$od]['org_name'], '区') !== false) {
                        $level = 2;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    // $level = $leadersData[$od]['level'];
                    $item['employee_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['employee_org_level' . $level] = $leadersData[$od]['org_name'];
                }
                // 查询直属领导是否跟踪过
                // 获取直属领导id
                $leaderIdList = $leadersData[$employeeOrgId]['leaders_ids'];

                $query = [];
                $query[] = ['employee_id', 'in', $leaderIdList];
                $query[] = ['addtime', 'like', $c['date'] . '%'];
                $query[] = ['customer_id', '=', $c['customer_id']];
                $leader_visit_time = CustomerVisitLog::where($query)->order('addtime desc')->value('addtime');
                $item['leader_visit'] = !empty($leader_visit_time) && ($item['measure_type2'] || $item['measure_type3']) ? $leader_visit_time : "";
                //$item['leader_visit'] = !CustomerVisitLog::where($query)->findOrEmpty()->isEmpty() && ($item['measure_type2'] == '√' || $item['measure_type3'] == '√') ? "√" : "";
            } else {
                $item['employee_name'] = $item['employee_org'] = $item['employee_leader'] = '';
                $item['leader_visit'] = '';
            }

            // 量房验房
            $item['measure_type1'] = ($item['leader_visit'] || $item['measure_type2']  || $item['measure_type3']) ? $lastmeasure_time : '';

            // 手机号
            $phoneArr = array_filter([$customerList[$c['customer_id']]['phone'], $customerList[$c['customer_id']]['phone1'], $customerList[$c['customer_id']]['phone2']]);
            $item['phone'] = substr_replace(array_shift($phoneArr), '******', 3, 6);

            // 建群情况
            //$jianqun = explode(',', $customerList[$c['customer_id']]['ext']['建群情况']);
            $jianqun = explode(',', $customerList[$c['customer_id']]['ext']['group_building']);
            //建群时间
            $jianqun_time = !empty($customerList[$c['customer_id']]['ext']['group_building_date']) ? $customerList[$c['customer_id']]['ext']['group_building_date'] : '';
            if (empty($jianqun_time)) {
                $jianqun_time = CustomerVisitLog::where([['customer_id', '=', $c['customer_id']], ['save_portrait_field', 'like', '%group_building%']])->value('addtime');
            }
            $item['ext35_1'] = (in_array('1对1小群', $jianqun) || in_array('一对一小群', $jianqun)) && $jianqun_time ? $jianqun_time : '';
            $item['ext35_2'] = in_array('临时大群', $jianqun) && $jianqun_time ? $jianqun_time : '';
            $item['ext35_3'] = in_array('社群', $jianqun) && $jianqun_time ? $jianqun_time : '';

            // 直播
            //$zhibo = explode(',', $customerList[$c['customer_id']]['ext']['直播情况']);
            $zhibo = explode(',', $customerList[$c['customer_id']]['ext']['live_broadcast']);
            $zhibo_time = !empty($customerList[$c['customer_id']]['ext']['live_broadcast_date']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_date'] : '';
            if (empty($zhibo_time)) {
                $zhibo_time = CustomerVisitLog::where([['customer_id', '=', $c['customer_id']], ['save_portrait_field', 'like', '%live_broadcast%']])->value('addtime');
            }
            $item['ext36_1'] = (in_array('一对一直播', $zhibo) ||  in_array('1对1业务直播', $zhibo)) && $zhibo_time ? $zhibo_time : '';
            $item['ext36_2'] = in_array('1对1设计直播', $zhibo) && $zhibo_time ? $zhibo_time : '';
            $item['ext36_3'] = in_array('一对多直播', $zhibo) && $zhibo_time ? $zhibo_time : '';
            $item['ext37_1'] = !empty($item['ext36_1']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_business'] : '';
            $item['ext37_2'] = !empty($item['ext36_2']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_design'] : '';
            $item['ext37_3'] = !empty($item['ext36_3']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_personnel'] : '';


            // 签单金额
            $condition = [];
            // $condition[] = ['addtime', 'like', $c['date'] . '%'];
            $condition[] = ['customer_id', '=', $c['customer_id']];
            $condition1 = $condition;
            $condition2 = $condition;
            $condition1[] = CustomerVisitLog::changeState(['state', '=', '已交定']);
            $condition2[] = CustomerVisitLog::changeState(['state', '=', '已签单']);
            $item['signature_money'] = CustomerVisitLog::where($condition2)->order('addtime desc')->value('money');
            $deposit = CustomerVisitLog::where($condition1)->order('addtime desc')->field('money,deposit_mode')->find();
            if (!empty($deposit)) {
                $deposit['deposit_mode'] !== 0 ? $item['deposit_money1'] = $deposit['money'] : $item['deposit_money2'] = $deposit['money'];
                // 爆品定金
                if ($customerList[$c['customer_id']]['package_id']) {
                    $packageIdList = explode(',', $customerList[$c['customer_id']]['package_id']);
                    $totalMoney = 0;
                    foreach ($packageIdList as $pid) {
                        $totalMoney += $package[$pid];
                    }
                    if ($totalMoney !== 0) {
                        $item['package'] = $deposit['money'] / $totalMoney * 100 >= 10 ? '√' : '';
                    }
                }
            }

            // 预计装修时间 -> 高意向到店客户
            if (!empty($customerList[$c['customer_id']]['plan_deco_time'])) {
                $planDecoTime = strtotime($customerList[$c['customer_id']]['plan_deco_time']);
                $item['plan_month_day'] = ($planDecoTime - time()) / 86400 < 30 ? $customerList[$c['customer_id']]['plan_deco_time'] : '';
            }
            //活动见面类型
            $query_actmet = [];
            $query_actmet[] = ['addtime', 'like', $c['date'] . '%'];
            $query_actmet[] = ['customer_id', '=', $c['customer_id']];
            $query_actmet[] = ['actmeet_type', '<>', 0];
            $query_actmet[] = CustomerVisitLog::changeState(['state', '=', '已到场']);
            $actmet = CustomerVisitLog::where($query_actmet)->order('addtime desc')->value('addtime,actmeet_type');
            if (!empty($actmet)) {
                $item['actmeet_type1'] = isset($actmet['actmeet_type']) && $actmet['actmeet_type'] == 1 ? $actmet['addtime'] : '';
                $item['actmeet_type2'] = isset($actmet['actmeet_type']) && $actmet['actmeet_type'] == 2 ? $actmet['addtime'] : '';
                $item['actmeet_type3'] = isset($actmet['actmeet_type']) && $actmet['actmeet_type'] == 3 ? $actmet['addtime'] : '';
            } else {
                $item['actmeet_type1'] = $item['actmeet_type2'] = $item['actmeet_type3'] = '';
            }
            //查询谈单时长
            $query_order_time = [];
            $query_order_time[] = ['addtime', 'like', $c['date'] . '%'];
            $query_order_time[] = ['customer_id', '=', $c['customer_id']];
            $query_order_time[] = CustomerVisitLog::changeState(['state', '=', '已到店']);
            $order_time = CustomerVisitLog::where($query_order_time)->sum('talking_order_time');
            $item['order_time'] = $order_time;
            //新的判断网谈登记,茶楼见面
            // $query_actmet1 = [];
            // $query_actmet1[] = ['addtime', 'like', $c['date'] . '%'];
            // $query_actmet1[] = ['customer_id', '=', $c['customer_id']];
            // //$query_actmet1[] = ['save_portrait_field','not null',''];
            // //$query_actmet1[] = ['save_portrait_field','like','online_meet_checked%'];
            // $query_actmet1[] = ['save_portrait_field', 'like', ['%online_meet_checked%', '%teahouse_meet%'], 'OR'];
            // $query_actmet1[] = CustomerVisitLog::changeState(['state', '=', '未到访']);
            // $actmet_data = CustomerVisitLog::where($query_actmet1)->column('save_portrait_field', 'id');
            // $online_meet_checked = $teahouse_meet = '';
            // if ($actmet_data) {
            //     foreach ($actmet_data as $m => $p) {
            //         $save_field_list = json_decode($p, true);
            //         foreach ($save_field_list as $k => $v) {
            //             if ($v['keyname'] == 'online_meet_checked') $online_meet_checked = $v['value'];
            //             if ($v['keyname'] == 'teahouse_meet') $teahouse_meet = $v['value'];
            //         }
            //     }
            // }
            // $item['actmeet_type2'] = $teahouse_meet ? $teahouse_meet : '';
            // $item['actmeet_type3'] = $online_meet_checked ? $online_meet_checked : '';
            //预约量房
            //$subscribe1 = [['addtime', 'like', $c['date'] . '%'],['customer_id', '=', $c['customer_id']],['type','=',3],['state','=',1]];
            //$subscribe2 = [['addtime', 'like', $c['date'] . '%'],['customer_id', '=', $c['customer_id']],['type','=',1],['state','=',1]];
            //$subscribe3 = [['addtime', 'like', $c['date'] . '%'],['customer_id', '=', $c['customer_id']],['type','=',2],['state','=',1]];
            //$yylf = CustomersSubscribe::where($subscribe1)->order('addtime desc')->value('addtime');
            //$yydd = CustomersSubscribe::where($subscribe2)->order('addtime desc')->value('addtime');
            //$yydc = CustomersSubscribe::where($subscribe3)->order('addtime desc')->value('addtime');
            //$item['yylf'] = $yylf ? $yylf : '';
            //$item['yydd'] = $yydd ? $yydd : '';
            //$item['yydc'] = $yydc ? $yydc : '';
            //查询定金客户转到店
            $item['djzdd'] = $this->isStateToShop($c['date'], $c['customer_id'], '已交定', $customerList[$c['customer_id']]['ext']);
            //查询量房客户转到店
            $item['lfzdd'] = $this->isStateToShop($c['date'], $c['customer_id'], '已量房', $customerList[$c['customer_id']]['ext']);
            //查询外场活动转到店
            $item['wczdd'] = $this->isStateToShop($c['date'], $c['customer_id'], '已到场', $customerList[$c['customer_id']]['ext']);
            //新到店次数
            $where = [];
            $starttime = date('Y-m', strtotime($c['date'])) . '-01 00:00:00';
            $where[] = ['addtime', '<', $c['date'] . ' 23:59:59'];
            $where[] = ['addtime', '>', $starttime];
            $where[] = ['customer_id', '=', $c['customer_id']];
            $state1 = CustomerVisitLog::changeState('确定到店', 'chaos');
            $where[] = ['state', 'in', $state1];
            $list = CustomerVisitLog::where($where)->order('addtime desc')->column('addtime,id');
            $newlist = [];
            if (!empty($list)) {
                foreach ($list as $k => $v) {
                    $list[$k]['newtime'] = date('Y-m-d', strtotime($v['addtime']));
                }
                foreach ($list as $k => $v) {
                    $newlist[$v['newtime']][] = $v;
                }
            }
            $count = count($newlist);
            $daodian = CustomerVisitLog::where([['customer_id','=',$c['customer_id']],['addtime','between',[$c['date'].' 00:00:00',$c['date'].' 23:59:59']],['state','in',CustomerVisitLog::changeState('已到店','chaos')]])->order('addtime desc')->value('addtime');
            $item['visit_number1'] = $count == 1 && $daodian ? end($newlist)[0]['addtime'] : '';
            //$item['visit_number2'] = $count == 2 ? array_pop($newlist)[0]['addtime'] : '';
            $item['visit_number2'] = $count == 2 && $daodian ? current($newlist)[0]['addtime'] : '';
            $item['visit_number3'] = $count >= 3 && $daodian ? '√' : '';

            // 外场活动名
            $aidList = array_unique(explode(',', $c['aid_list']));
            $huodong = Activity::where([['id', 'in', $aidList], ['cate', '=', 1]])->column('title,address');
            $huodongList = [];
            foreach ($huodong as $h) {
                if ($h['address']) {
                    $huodongList[] = $h['title'] . '(' . $h['address'] . ')';
                } else {
                    $huodongList[] = $h['title'];
                }
            }

            $daochang = CustomerVisitLog::where([['customer_id','=',$c['customer_id']],['addtime','between',[$c['date'].' 00:00:00',$c['date'].' 23:59:59']],['state','in',CustomerVisitLog::changeState('已到场','chaos')]])->count();
            $item['outfield_activities'] = $daochang ? implode(',', $huodongList) : '';

            // 外场活动转内场新客户 活动前未到店以及后续状态，参加活动后，第一次到店显示
            //$item['outfield_infield'] = $this->getIsNewCusomerVisitAfterActivity($c['date'], $c['customer_id']);
            $item['outfield_infield'] = $daodian && empty($item['djzdd']) && empty($item['lfzdd']) && empty($item['wczdd']) ? $daodian : '';
            $data[] = $item;
            $num++;
        }
        return $data;
    }
    /**
     * 查询是否是定金,量房,外场客户转到店
     */
    public function isStateToShop($date, $customerId, $state, $cusext)
    {
        $where = [];
        $where[] = ['addtime', '<', $date . ' 23:59:59'];
        $where[] = ['customer_id', '=', $customerId];
        $state1 = CustomerVisitLog::changeState('已到店', 'chaos');
        $where[] = ['state', 'in', $state1];
        // 查询日期必须到店
        $had = CustomerVisitLog::where($where)->order('addtime asc')->column('addtime');

        // 是否多次到店或未到店,当天到店多次算一次
        if (empty($had)) return '';
        foreach ($had as $k => $v) {
            if ($v < $date . ' 00:00:00') return '';
        }

        //$lastTime = strtotime($had[0]);
        $lastTime = strtotime(end($had));
        // 判断到店日期是否是所选日期
        if (date('Y-m-d', $lastTime) != $date) return '';
        // 判断是否参加过活动
        $state_time = CustomerVisitLog::where([
            ['addtime', '<', $date . ' 23:59:59'],
            ['state', 'in', CustomerVisitLog::changeState($state, 'chaos')],
            ['customer_id', '=', $customerId]
        ])->order('addtime asc')->value('addtime');

        if (empty($state_time)) return '';

        //if (in_array($state, ['已量房', '已交定']) && empty($state_time)) return '';
        //如果已到场时间为空再查询网谈登记时间跟茶楼见面时间,如果两个都有值取最早时间的值
        // if ($state == '已到场' && empty($state_time)) {
        //     if (isset($cusext['online_meet_checked']) && isset($cusext['teahouse_meet'])) $state_time = $cusext['online_meet_checked'] > isset($cusext['teahouse_meet']) ? isset($cusext['teahouse_meet']) : isset($cusext['online_meet_checked']);
        //     if (isset($cusext['online_meet_checked'])) $state_time = $cusext['online_meet_checked'];
        //     if (isset($cusext['teahouse_meet'])) $state_time = $cusext['teahouse_meet'];
        //     if (empty($state_time)) return '';
        // }

        if (strtotime($state_time) < $lastTime) return date('Y-m-d H:i:s', $lastTime);

        return '';
    }
    /**
     * 总经办表的csv格式导出
     *
     * @param [type] $dealData
     * @param [type] $output
     * @return void
     */
    public function officeStatisticsExport($dealData, $output)
    {
        $header = $this->getOfficeHeaders();
        $row = [];
        $title = [];
        foreach ($header['header1'] as $item) {
            if (isset($item['colspan'])) {
                for ($s = 0; $s < $item['colspan']; $s++) {
                    $h2 = array_shift($header['header2']);
                    $title[] = $h2['title'];
                }
            } else {
                $title[] = $item['title'];
            }
            if (isset($item['child'])) {
                foreach($item['child'] as $k){
                    $row[] = $k;
                }
            } else {
                $row[] = $item['field'];
            }
        }
        $filename = uniqid();
        $fp = fopen('./download/' . $filename . '.csv', 'w');
        fputcsv($fp, $title);
        $page = 1;
        $z = 2;
        do {
            $data = $this->officeStatistics($dealData->root_id, $dealData->params, $page, 100);
            foreach ($data as $r => $rowData) {
                $r += $z + ($page - 1) * 100;
                $output->writeln("$r");
                $d = [];
                foreach ($row as $field) {
                    $d[] = isset($rowData[$field]) ? $rowData[$field] : '';
                }
                fputcsv($fp, $d);
            }
            $page++;
        } while (!empty($data));
        fclose($fp);
        $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传中');
        // 文件上传
        $rs = ossUpload('statisticsExport/' . $filename . '.csv', './download/' . $filename . '.csv');
        if (!$rs) $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传失败');
        else {
            $output->writeln("异步任务[id]：" . $dealData->id . '生成文件上传完毕');
            // 删除本地文件
            unlink('./download/' . $filename . '.csv');
        }
        return $filename . '.csv';
    }
     /**
     * 获取线上跟踪客户记录表头
     */
    private function getOfficeHeaders()
    {
        // 第二行头部
        $header2 = [];
        // 设计师部门插入头部
        $header2[] = ["field" => 'customer_name', "title" => '客户名字', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'phone', "title" => '客户电话', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext39', "title" => '省市区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'community_name', "title" => '楼盘', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext42', "title" => '门牌单元号', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'square', "title" => '面积', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_name', "title" => '业务员', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org', "title" => '业务部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader', "title" => '业务经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level4', "title" => '业务中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level4', "title" => '业务总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level3', "title" => '区中区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level3', "title" => '区中区副总', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_org_level2', "title" => '大区', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'employee_leader_level2', "title" => '大区副总', "align" => 'center', "class" => "bgEEECE1"];

        $header2[] = ["field" => 'designer_name', "title" => '设计师', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org', "title" => '设计部门', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader', "title" => '设计经理', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_org_level2', "title" => '设计中心', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level2', "title" => '设计总监', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'designer_leader_level1', "title" => '设计副总', "align" => 'center', "class" => "bgEEECE1"];

        // 加微
        $header2[] = ["field" => 'house_type1', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month1_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type2', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month2_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'house_type3', "title" => '现房', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_1', "title" => '0-3个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_2', "title" => '3-6个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_3', "title" => '6-12个月', "align" => 'center', "class" => "bgDCE6F1"];
        $header2[] = ["field" => 'delivery_month3_4', "title" => '12个月以上', "align" => 'center', "class" => "bgDCE6F1"];

        // 建群
        $header2[] = ["field" => 'ext35_1', "title" => '1对1小群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_2', "title" => '临时大群', "align" => 'center', "class" => "bgEEECE1"];
        $header2[] = ["field" => 'ext35_3', "title" => '社群', "align" => 'center', "class" => "bgEEECE1"];

        // 直播
        $header2[] = ["field" => 'ext36_1', "title" => '1对1业务直播', "align" => 'center', "class" => "bgF2DCDB"];
        //$header2[] = ["field" => 'ext37_1', "title" => '业务员', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_2', "title" => '1对1设计直播', "align" => 'center', "class" => "bgF2DCDB"];
        //$header2[] = ["field" => 'ext37_2', "title" => '设计师', "align" => 'center', "class" => "bgF2DCDB"];
        $header2[] = ["field" => 'ext36_3', "title" => '一对多直播', "align" => 'center', "class" => "bgF2DCDB"];
        //$header2[] = ["field" => 'ext37_3', "title" => '直播人员', "align" => 'center', "class" => "bgF2DCDB"];

        // 插入销售部门
        $employeeChild = ['employee_name', 'employee_org', 'employee_leader', 'employee_org_level4', 'employee_leader_level4', 'employee_org_level3', 'employee_leader_level3', 'employee_org_level2', 'employee_leader_level2'];
        // 设计部门
        $designerChild = ['designer_name', 'designer_org', 'designer_leader', 'designer_org_level2', 'designer_leader_level2', 'designer_leader_level1'];

        // 第一行头部
        $header1 = [];
        $header1[] = ["type" => 'key', "field" => 'key', "title" => '序号', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'date', "title" => '日期', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['customer_name', 'phone', 'ext39', 'community_name', 'ext42', 'square'], "title" => '客户信息 ', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => 'company_name', "title" => '公司', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => 'leader', "title" => '负责人', "align" => 'center', "rowspan" => 2, "class" => "bgA6A6A6"];
        $header1[] = ["field" => "", "child" => $employeeChild, "title" => '营销', "align" => 'center', "colspan" => 9, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => $designerChild, "title" => '设计', "align" => 'center', "colspan" => 6, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['house_type1', 'delivery_month1_1', 'delivery_month1_2', 'delivery_month1_3', 'delivery_month1_4'], "title" => '常规加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type2', 'delivery_month2_1', 'delivery_month2_2', 'delivery_month2_3', 'delivery_month2_4'], "title" => '社群加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "", "child" => ['house_type3', 'delivery_month3_1', 'delivery_month3_2', 'delivery_month3_3', 'delivery_month3_4'], "title" => '历史加微<br>(交房时间)', "align" => 'center', "colspan" => 5, "class" => "bgDCE6F1"];
        $header1[] = ["field" => "add_wechat_time",  "title" => '加微时间', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext35_1', 'ext35_2', 'ext35_3'], "title" => '建群', "align" => 'center', "colspan" => 3, "class" => "bgEEECE1"];
        $header1[] = ["field" => "", "child" => ['ext36_1', 'ext36_2', 'ext36_3'], "title" => '直播', "align" => 'center', "colspan" => 3, "class" => "bgF2DCDB"];
        $header1[] = ["field" => "measure_type1",  "title" => '量房验房', "align" => 'center', "rowspan" => 2, "class" => "bgEEECE1"];
        return ['header1' => $header1, 'header2' => $header2];
    }
    /**
     * 总经办报表
     */
    public function officeStatistics($root_id,$param,$page,$limit)
    {
        // 获取部门id
        $orgs = Org::where([['path', 'like', $root_id . '-%']])->column('name,id,pid,level,path', 'id');
        // 部门领导获取
        $leaders = Employee::with('org')->where([['root_id', '=', $root_id], ['state', '=', '在职'], ['is_manager', '=', '1'], ['uid', '<>', 0]])->select();
        // 领导结构修改
        $leadersData = [];
        foreach ($leaders as $l) {
            if (isset($leadersData[$l['org_id']])) {
                $leadersData[$l['org_id']]['leaders'][] = $l['name'];
                $leadersData[$l['org_id']]['leaders_ids'][] = $l['id'];
            } else {
                $leadersData[$l['org_id']] = [
                    'org_name' => $l['org']['name'],
                    'level' => $l['org']['level'],
                    'path' => $l['org']['path'],
                    'leaders_ids' => [
                        $l['id']
                    ],
                    'leaders' => [
                        $l['name']
                    ]
                ];
            }
        }
        // 补充没领导的部门
        foreach ($orgs as $k => $item) {
            if (!isset($leadersData[$k])) {
                $leadersData[$k] = [
                    'org_name' => $item['name'],
                    'level' => $item['level'],
                    'path' => $item['path'],
                    'leaders' => [],
                    'leaders_ids' => []
                ];
            }
        }

        // 查询最近访问的客户
        $where = [];
        if ($param['org_id']) {
            //查询本部门下面是否有部门
            $childorg = orgSubIds($param['org_id']);
            $where[] = ['org_id|customer_org_id', 'in', $childorg];
            // $where[] = ['org_id', '=', $param['org_id']];
        } else {
            $where[] = ['org_id', 'in', array_keys($orgs)];
        }

        if ($param['sdate']) {
            $param['sdate'] = date('Y-m-d', strtotime($param['sdate']));
            $where[] = ['addtime', '>=', $param['sdate'] . ' 00:00:00'];
        }
        if ($param['edate']) {
            $param['edate'] = date('Y-m-d', strtotime($param['edate']));
            $where[] = ['addtime', '<=', $param['edate'] . ' 23:59:59'];
        }

        // 查询排除的部门
        $sheji = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '设计部']])->value('id');
        $xiaoshou = Org::where([['path', 'like', $root_id . '-%'], ['name', '=', '市场部']])->value('id');
        $fanweinei = Org::whereOr([
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $sheji . '-%']],
            [['path', 'like', $root_id . '-%'], ['path', 'like', $root_id . '-' . $xiaoshou . '-%']],
        ])->column('id');
        $searchEmployeeIdList = Employee::where([['org_id', 'in', $fanweinei]])->column('id');
        //如果有部门筛选把员工控制在本部门
        if ($param['org_id']) $searchEmployeeIdList = Employee::where([['org_id', 'in', $childorg]])->column('id');
        //$where[] = ['employee_id', 'in', $searchEmployeeIdList];
        $where[] = ['customer_employee_id', 'in', $searchEmployeeIdList];
        //2022-10-31辛居尚重11月1号开始正式使用
        $where[] = ['addtime', '>', date('2022-10-31 23:59:59')];
        //新增加查询记录到前一天的24点
        $sel_endtime = date('Y-m-d');
        //if (empty($param['edate']) && empty($param['sdate'])) $where[] = ['addtime', '<', date('Y-m-d', strtotime("$sel_endtime -1 days")).' 23:59:59'];
        if (!empty($param['add_wechat_time'])) {
            $newtime = explode(' - ', $param['add_wechat_time']);
            $sdate = strtotime($newtime[0]);
            $edate = strtotime($newtime[1]);
            $wechat_count = [];

            $wechat_count = Customer::where([['ext', 'not null', ''], ['ext', '<>', ''], ['add_wechat_time', '>=', date('Y-m-d', $sdate)], ['add_wechat_time', '<=', date('Y-m-d', $edate)]])->column('id');

            $cids = CustomerVisitLog::where($where)->group('customer_id')->column('customer_id');
            $wechat_count = array_intersect($cids, $wechat_count);
            $where[] = ['customer_id', 'in', $wechat_count];
        }
        //只查询客户的扩展字段更新跟量房的记录
        $where1[] = ['save_portrait_field', 'like', ['%add_wechat_time%','%add_wechat_type%','%group_building"%','%live_broadcast%'], 'OR'];
        $where1[] = ['state','in',CustomerVisitLog::changeState('已量房','chaos')];
        $total = CustomerVisitLog::field("customer_id, DATE_FORMAT(addtime,'%Y-%m-%d') as date")->where($where)
        ->where(function($query) use ($where1){
            $query->whereOr($where1);
        })->group('customer_id,date')->count();
        $lastVisitCustomerList = CustomerVisitLog::field("customer_id,state,save_portrait_field,DATE_FORMAT(addtime,'%Y-%m-%d') as date, max(addtime) as addtime")->where($where)
        ->where(function($query) use ($where1){
            $query->whereOr($where1);
        })->order('date desc,addtime desc')->group('customer_id,date')->page($page, $limit)->select();
        // 获取客户信息
        $customerIdList = array_unique(array_column(empty($lastVisitCustomerList) ? [] : $lastVisitCustomerList->toArray(), 'customer_id'));
        $customerList = Customer::with(['employee', 'designer'])->where([['id', 'in', $customerIdList]])->field('id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();

        $customerList1 = CustomerRecycle::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList2 = CustomerDropPool::with(['employee', 'designer'])->where([['customer_id', 'in', $customerIdList]])->field('customer_id as id,name,designer_id,employee_id,phone,phone1,phone2,square,community_name,ext,house_delivery_time,plan_deco_time,source_id,house_type,package_id,level,age_range,house_type,deco_style,source_id')->select();
        $customerList = array_merge($customerList->toArray(), $customerList1->toArray(), $customerList2->toArray());

        $customerList = array_combine(array_column($customerList, 'id'), $customerList);
        $customerList = $this->emp_extfield_change($customerList,$root_id);
         // 获取公司名称
        $companyName = Company::where([['root_id', '=', $root_id]])->value('company_name');
        $num = ($page - 1) * $limit + 1;
        // 处理数据
        $data = [];
        foreach ($lastVisitCustomerList as $c) {
            $item = [
                'key' => $num,
                'customer_id' => $c['customer_id'],
            ];

            if (!isset($customerList[$c['customer_id']])) {
                $data[] = $item;
                $num++;
                continue;
            }
            list($Y, $m, $d) = explode('-', $c['date']);
            $leader_arr = ['941'=>'罗镐','1847'=>'刘有凤'];//馨居尚,菡萏怡景,部门负责人
            $item = [
                'key' => $num,
                'customer_name' => $customerList[$c['customer_id']]['name'],
                'date' => $Y . '年' . $m . '月' . $d . '日',
                'addtime' => $c['addtime'],
                'company_name' => $companyName,
                'leader' => !empty($leader_arr[$root_id]) ? $leader_arr[$root_id] : '罗镐',
                'community_name' => $customerList[$c['customer_id']]['community_name'],
                //'measure_room_store' => $this->measureRoomStore($c['date'], $c['customer_id']),
                'number_of_visitors' => $c['number_of_visitors'] == 0 ? '' : $c['number_of_visitors'],
                'area' => $c['area'],
                'ext39' =>   $customerList[$c['customer_id']]['ext']['house_location'],
                'talking_about_single_time' => isset($customerList[$c['customer_id']]['ext']['talking_about_single_time']) ? $customerList[$c['customer_id']]['ext']['talking_about_single_time'] : 0,
                'source' => isset($sourseList[$customerList[$c['customer_id']]['source_id']]) ? $sourseList[$customerList[$c['customer_id']]['source_id']] : '',
                'square' => $customerList[$c['customer_id']]['square'],
                'ext42' => $customerList[$c['customer_id']]['ext']['unit_number']
            ];
            //查询当天所有的修改扩展字段的记录
            $savelist = CustomerVisitLog::where([['addtime','between',[$c['date'].' 00:00:00',$c['date'].' 23:59:59']],['save_portrait_field', 'like', ['%add_wechat_time%','%add_wechat_type%','%group_building"%','%live_broadcast%'], 'OR'],['customer_id','=',$c['customer_id']]])->column('save_portrait_field');
            $day_savefild = [];
            if(!empty($savelist)){
            foreach($savelist as $k=>$v){
                 $day_savefild = array_merge(json_decode($v,true),$day_savefild);
            }
            $day_savefild = array_unique(array_column($day_savefild,'keyname'));
            }
            $item['day_savefild'] = $day_savefild;
            // 计算交房相差月份
            $houseDeliveryTime = strtotime($customerList[$c['customer_id']]['house_delivery_time']);
            $month = (date('Y', $houseDeliveryTime) - date('Y')) * 12 + date('m', $houseDeliveryTime) - date('m');

            $type = '0';
            $item['add_wechat_time'] = in_array('add_wechat_time',$day_savefild) ? $customerList[$c['customer_id']]['ext']['add_wechat_time'] : '';
            if ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '常规加微') {
                $type = '1';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '社群加微') {
                $type = '2';
            } elseif ($customerList[$c['customer_id']]['ext']['add_wechat_type'] == '历史加微') {
                $type = '3';
            }

            if ($type !== '0' && in_array('add_wechat_type',$day_savefild)) {
                $item['house_type' . $type] = in_array($customerList[$c['customer_id']]['house_type'], ['现房', '二手房']) ? '√' : '';
                if ($item['house_type' . $type] != '√' && !empty($customerList[$c['customer_id']]['house_delivery_time'])) {
                    $item['delivery_month' . $type . '_1'] = $month <= 3 ? '√' : '';
                    $item['delivery_month' . $type . '_2'] = $month > 3 && $month <= 6 ? '√' : '';
                    $item['delivery_month' . $type . '_3'] = $month > 6 && $month <= 12 ? '√' : '';
                    $item['delivery_month' . $type . '_4'] = $month > 12 ? '√' : '';
                } else {
                    $item['delivery_month' . $type . '_1'] = '';
                    $item['delivery_month' . $type . '_2'] = '';
                    $item['delivery_month' . $type . '_3'] = '';
                    $item['delivery_month' . $type . '_4'] = '';
                }
            }

            // 设计师
            if (!empty($customerList[$c['customer_id']]['designer'])) {
                $designerOrgId = $customerList[$c['customer_id']]['designer']['org_id'];
                $item['designer_name'] = $customerList[$c['customer_id']]['designer']['name'];
                $item['designer_org'] = $orgs[$designerOrgId]['name'];
                $item['designer_leader'] = isset($leadersData[$designerOrgId]) ? implode(',', $leadersData[$designerOrgId]['leaders']) : '';
                $path = $leadersData[$designerOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '部') !== false && $leadersData[$od]['org_name'] != '设计部') {
                        $level = 3;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    $item['designer_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['designer_org_level' . $level] = $leadersData[$od]['org_name'];
                }
            } else {
                $item['designer_name'] = $item['designer_org'] = $item['designer_leader'] = '';
            }


            // 员工
            if (!empty($customerList[$c['customer_id']]['employee'])) {
                $employeeOrgId = $customerList[$c['customer_id']]['employee']['org_id'];
                $item['employee_name'] = $customerList[$c['customer_id']]['employee']['name'];
                $item['employee_org'] = $orgs[$employeeOrgId]['name'];
                $item['employee_leader'] = implode(',', $leadersData[$employeeOrgId]['leaders']);

                $path = $leadersData[$employeeOrgId]['path'];
                $pathList = array_filter(explode('-', $path));
                foreach ($pathList as $od) {
                    if (strstr($leadersData[$od]['org_name'], '区中区') !== false) {
                        $level = 3;
                    } elseif (strstr($leadersData[$od]['org_name'], '中心') !== false) {
                        $level = 4;
                    } elseif (strstr($leadersData[$od]['org_name'], '区') !== false) {
                        $level = 2;
                    } else {
                        $level = $leadersData[$od]['level'];
                    }

                    if (isset($item['employee_org_level' . $level])) $level++;
                    $item['employee_leader_level' . $level] = implode(',', $leadersData[$od]['leaders']);
                    $item['employee_org_level' . $level] = $leadersData[$od]['org_name'];
                }
                // 查询直属领导是否跟踪过
                // 获取直属领导id
                $leaderIdList = $leadersData[$employeeOrgId]['leaders_ids'];
            } else {
                $item['employee_name'] = $item['employee_org'] = $item['employee_leader'] = '';
                $item['leader_visit'] = '';
            }

            // 量房验房
            $item['measure_type1'] = in_array($c['state'],[8, '已量房', '确定量房', '确认量房', '8']) ? '√' : '';
            // 手机号
            $phoneArr = array_filter([$customerList[$c['customer_id']]['phone'], $customerList[$c['customer_id']]['phone1'], $customerList[$c['customer_id']]['phone2']]);
            $item['phone'] = substr_replace(array_shift($phoneArr), '******', 3, 6);

            // 建群情况
            $jianqun = in_array('group_building',$day_savefild) ? explode(',', $customerList[$c['customer_id']]['ext']['group_building']) : [];
            $item['ext35_1'] = (in_array('1对1小群', $jianqun) || in_array('一对一小群', $jianqun)) ? '√' : '';
            $item['ext35_2'] = in_array('临时大群', $jianqun) ? '√' : '';
            $item['ext35_3'] = in_array('社群', $jianqun) ? '√': '';
            
            // 直播
            $zhibo = in_array('live_broadcast',$day_savefild) ? explode(',', $customerList[$c['customer_id']]['ext']['live_broadcast']) : [];
            $item['ext36_1'] = (in_array('一对一直播', $zhibo) ||  in_array('1对1业务直播', $zhibo)) ? '√' : '';
            $item['ext36_2'] = in_array('1对1设计直播', $zhibo) ? '√' : '';
            $item['ext36_3'] = in_array('一对多直播', $zhibo) ? '√' : '';
            $item['ext37_1'] = !empty($item['ext36_1']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_business'] : '';
            $item['ext37_2'] = !empty($item['ext36_2']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_design'] : '';
            $item['ext37_3'] = !empty($item['ext36_3']) ? $customerList[$c['customer_id']]['ext']['live_broadcast_personnel'] : '';
            $data[] = $item;
            $num++;
        }
        return $data;

    }
}