<?php
namespace app\crm\lib;
use think\Controller;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;
use Excel5;
class Runtransdata extends Controller {
    var $step = 4;
    var $thisStep = 0;
    var $perSqlDataNum = 200;
    var $sqlPath = '/data/git/crm-stand/runtime/';
    var $con = null;
    var $consx = null;
    var $conmc = null;
    var $test = false;
    var $channel = '';
    var $staff = '';
    var $consultation = '';
    //天长地久转档
    //先把客资从原系统导出成excel，然后将excel导入到客资库的tcdj_crm
    //本方法从tcdj_crm这张表内进行处理到crm表内
    //数据库连接使用crm的默认连接
    public function Tcdj(){
        set_time_limit(0);
        $this->test = false;//正式使用时，请把这里设成true.否则只导五条数据。
        $this->step = 4;
        $dbmc = [
          'type'            => 'mysql',
          'hostname'=>'172.18.75.86',
          'username'=>'sx',
          'database'=>'tcdj_sx_mc',
          'password'=>'mysql@sx1120',
        ];
        $this->conmc = @Db::connect($dbmc) or die("无法连接MC数据库");
        $this->log("开始天长地久导库操作    ");
        $this->log("开始加载基础数据...    ");
        $this->thisStep++;
        $this->initSystemChannel();
        $this->log("渠道数据加载完成。    ");
        $this->thisStep++;
        $this->initAllStaff();
        $this->log("人员数据加载完成    ");
        $this->thisStep++;
        $this->initConsultation();
        $this->log("消费类型数据加载完成    ");
        $this->thisStep++;
        $this->log("开始处理客资数据...    ");
        $this->startCustomer();
    }
    private function startCustomer(){
        $count = db('tcdj_crm')->where('status','0')->count();
        $this->log("待处理客资总数：".$count);
        $sql = '
            select id,status,A cid,B dj_staff,C sales_staff,E customer_status,F unvalid_reason,H channel,I keywords,K createtime,L mendian,M yjd_day,N is_intostore,O memo,P customer_name,R customer_sex,S customer_tel,T customer_wechat,U customer_qq, V mate_name,X mate_sex,Y mate_tel,Z mate_wechat,AA mate_qq,AB consultation_type,AC budget,AG op_signinfo,AI traceinfo,AJ orderinfo
            from tcdj_crm where status = 0
        ';
        $over = 0;
        if($this->test){
            $sql .= 'limit 0,5';
            $data = db()->query($sql);
            $this->importCustomerInfoTcdj($data);
            $over = $over + count($data);
            $this->log("已处理客资数：".$over.'/'.$count);
        }else{
            $sql .= 'limit 0,50';
            while ( $data = db()->query($sql)) {
                $this->importCustomerInfoTcdj($data);
                $over = $over + count($data);
                $this->log("已处理客资数：".$over.'/'.$count);
            }
        }
        $this->log("全部客资处理完毕".$count);
    }
    private function importCustomerInfoTcdj($datas){
        $customerInfo = [];//客资主表数据
        $customerContact = [];//排重表数据
        $customerFrom = [];//客资扩展信息表
        $customerOrder = [];//客资订单表
        $customerTrace = [];//追踪记录
        $update = [];
        foreach ($datas as $value) {
            static $do;
            $do++;
            $tmpInfo = [];
            $tmpInfo['id'] = $value['cid'];
            $tmpInfo['customer_number'] = $value['cid'];
            $tmpInfo['customer_name'] = $value['customer_name'];
            $tmpInfo['customer_sex'] = $value['customer_sex'];
            $tmpInfo['customer_tel'] = $value['customer_tel'];
            $tmpInfo['customer_wechat'] = $value['customer_wechat'];
            $tmpInfo['customer_qq'] = $value['customer_qq'];
            $tmpInfo['mate_name'] = $value['mate_name'];
            $tmpInfo['mate_sex'] = $value['mate_sex'];
            $tmpInfo['mate_tel'] = $value['mate_tel'];
            $tmpInfo['mate_wechat'] = $value['mate_wechat'];
            $tmpInfo['mate_qq'] = $value['mate_qq'];
            $tmpInfo['customer_type'] = 1;
            $tmpInfo['customer_remark'] = $value['memo']."\r\n".$value['mendian'];
            $tmpInfo['c_from'] = 1;
            $tmpInfo['customer_key'] = $value['keywords'];
            $tmpInfo['creator_day'] = $tmpInfo['create_time'] = $tmpInfo['dj_day'] = date('Y-m-d H:i:s',strtotime($value['createtime']));
            $tmpInfo['budget'] = $value['budget'];
            $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = null;
            $tmpInfo['sales_staff_id'] = null;
            $tmpInfo['sales_staff_number'] = null;
            $tmpInfo['sales_staff'] = null;
            $tmpInfo['shop_code'] = null;
            $tmpInfo['shop_name'] = null;
            $tmpInfo['sales_department_id'] = null;
            foreach ($this->staff as $staffinfo) {
                if($staffinfo['staffname'] == $value['dj_staff']){
                    $tmpInfo['dj_staff_id'] = $staffinfo['id'];
                    $tmpInfo['dj_staff_number'] = $staffinfo['staffnumber'];
                    $tmpInfo['dj_staff'] = $value['dj_staff'];
                    $tmpInfo['add_shop_code'] = $staffinfo['shop_code'];
                    $tmpInfo['add_shop_name'] = $staffinfo['shop_name'];
                    $tmpInfo['dj_department_id'] = $staffinfo['department_id'];
                    $tmpInfo['brandclass'] = $staffinfo['brandclass'];
                    $tmpInfo['brandclass_id'] = $staffinfo['brandclass_id'];
                }
                if($staffinfo['staffname'] == $value['sales_staff']){
                    $tmpInfo['sales_staff_id'] = $staffinfo['id'];
                    $tmpInfo['sales_staff_number'] = $staffinfo['staffnumber'];
                    $tmpInfo['sales_staff'] = $value['sales_staff'];
                    $tmpInfo['shop_code'] = $staffinfo['shop_code'];
                    $tmpInfo['shop_name'] = $staffinfo['shop_name'];
                    $tmpInfo['sales_department_id'] = $staffinfo['department_id'];
                }
            }


            if($tmpInfo['customer_tel']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['customer_tel'],
                    'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            if($tmpInfo['customer_wechat']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['customer_wechat'],
                   'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            if($tmpInfo['customer_qq']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['customer_qq'],
                    'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            if($tmpInfo['mate_tel']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['mate_tel'],
                    'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            if($tmpInfo['mate_wechat']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['mate_wechat'],
                    'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            if($tmpInfo['mate_qq']){
                $customerContact[] = [
                    'c_id' => $tmpInfo['id'],
                    'contact'=>$tmpInfo['mate_qq'],
                    'brandclass'=>$tmpInfo['brandclass'],
                ];
            }
            
            if(!$tmpInfo['dj_staff_id']){
                $update[] = [
                    'id'=>$value['id'],
                    'msg'=>'客资录入员工信息不对',
                    'status'=>2,
                ];
                continue;
            }
            if(empty(trim($tmpInfo['id']))){
                $update[] = [
                    'id'=>$value['id'],
                    'msg'=>'客资编号为空。',
                    'status'=>2,
                ];
                continue;
            }
            if($tmpInfo['customer_tel']){
                $tmpInfo['tel_staff'] = $tmpInfo['dj_staff'];
                $tmpInfo['tel_staff_id'] = $tmpInfo['dj_staff_id'];
                $tmpInfo['tel_time'] = $tmpInfo['create_time'];
            }else{
                $tmpInfo['tel_staff'] = null;
                $tmpInfo['tel_staff_id'] = null;
                $tmpInfo['tel_time'] = null;
            }
            
            
            if($value['sales_staff'] && !$tmpInfo['sales_staff_id']){
                $update[] = [
                    'id'=>$value['id'],
                    'msg'=>'网销追踪员工信息不对',
                    'status'=>2,
                ];
                continue;
            }
            $customerFrom[] = [
                'cid'=>$tmpInfo['id'],
            ];
            if(!$tmpInfo['sales_staff_id']){
                //未分配
                $tmpInfo['is_fp'] = 2;
            }else{
                $tmpInfo['is_fp'] = 1;
            }
            if(trim($value['traceinfo'])){
                $tmpTrace = [
                    'customer_id' => $tmpInfo['id'],
                    'gtType' => 1,
                    'customer_name' => $tmpInfo['customer_name'],
                    'customer_tel' => $tmpInfo['customer_tel'],
                    'customer_number' => $tmpInfo['customer_number'],
                    'sales_remark' => $value['traceinfo'],
                    'sales_day' => $tmpInfo['dj_day'],
                    'sales_staff_id' => $tmpInfo['sales_staff_id'],
                    'sales_staff' => $tmpInfo['sales_staff'],
                    'sales_staff_number' => $tmpInfo['sales_staff_number'],
                    /**
                     * TODO ..
                     */
                    'shop_name' => $tmpInfo['add_shop_name'],
                    'shop_code' => $tmpInfo['add_shop_code'],
                ];
                $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];

            }
            
            //渠道
            $tmpInfo['customer_channel_id'] = 0;
            $tmpInfo['customer_channel'] = '';
            foreach ($this->channel as $channelInfo) {
                if($channelInfo['channel_name'] == $value['channel']){
                    $tmpInfo['customer_channel'] = $value['channel'];
                    $tmpInfo['customer_channel_id'] = $channelInfo['id'];
                }
            }
            //消费类型
            $tmpInfo['consultation_type'] = '';
            $tmpInfo['consultation_type_id'] = 0;
            foreach ($this->consultation  as $consultation) {
                if($consultation['consumption_name'] == $value['consultation_type']){
                    $tmpInfo['consultation_type'] = $value['consultation_type'];
                    $tmpInfo['consultation_type_id'] = $consultation['id'];
                }
            }
            //部分默认初始化字段。
            $tmpInfo['is_valid'] = 2;
            $tmpInfo['unvalid_reason'] = '';
            $tmpInfo['is_loss'] = 2;
            $tmpInfo['erp_loss'] = 0;
            $tmpInfo['is_intostore'] = 1;
            $tmpInfo['yjd_day'] = null;
            $tmpInfo['syjd_day'] = null;
            $tmpInfo['order_type'] = 1;
            $tmpInfo['order_number'] = '';
            $tmpInfo['order_day'] = null;
            $tmpInfo['totalpay_money'] = 0;
            $tmpInfo['fact_money'] = 0;
            $tmpInfo['is_success'] = 2;
            //订单信息
            $value['orderinfo'] = trim($value['orderinfo']);
            if($value['orderinfo']){
                $tmpInfo['order_type'] = 1;
                $orderString = explode("\r", $value['orderinfo']);
                foreach ($orderString as $vstr) {
                    $orderStr = substr($vstr,1,strlen($vstr) -2);
                    $orderInfo = explode(')(', $orderStr);//0单号，1订单时间，2订单金额3实收金额
                   /*
                    if($value['id'] == 685){
                        echo '<='.$value['orderinfo'].'=>';
                        var_dump($orderString);
                        var_dump($vstr);
                        var_dump($orderInfo);
                        die;
                    }*/
                    if($orderInfo[0] && $orderInfo[1]){
                        $tmpInfo['order_number'] = $orderInfo[0];
                        $tmpInfo['order_day'] = $orderInfo[1];
                        $tmpInfo['totalpay_money'] = $orderInfo[2];
                        $tmpInfo['fact_money'] = $orderInfo[3];
                        $tmpInfo['is_success'] = 1;
                        $tmpInfo['is_valid'] = 1;
                        $tmpOrder['customer_id'] = $tmpInfo['id'];
                        $tmpOrder['customer_name'] = $tmpInfo['customer_name'];
                        $tmpOrder['customer_channel_id'] = $tmpInfo['customer_channel_id'];
                        $tmpOrder['customer_channel'] = $tmpInfo['customer_channel'];
                        $tmpOrder['order_status'] = '1';
                        $tmpOrder['order_valid'] = '1';
                        $tmpOrder['order_sn'] = $orderInfo[0];
                        $tmpOrder['order_fact'] = $orderInfo[3];
                        $tmpOrder['order_amount'] = $orderInfo[2];
                        $tmpOrder['consultation_type'] = $tmpInfo['consultation_type'];
                        $tmpOrder['create_time'] = $tmpInfo['create_time'];
                        $tmpOrder['target_time'] = $orderInfo[1];
                        $tmpOrder['is_delete'] = 2;
                        $tmpOrder['order_type'] = 2;
                        /**
                         * TODO...
                         */
                        $tmpOrder['shop_code'] = 'TCDJ13';
                        $tmpOrder['shop_name'] = '婚纱电商';
                        $tmpOrder['brandclass'] = $tmpInfo['brandclass'];
                        $tmpOrder['brandclass_id'] = $tmpInfo['brandclass_id'];
                        $tmpInfo['c_star_day'] = $tmpInfo['dj_day'];
                        $tmpInfo['last_trackday'] = date('Y-m-d H:i:s',strtotime($tmpInfo['order_day']));
                    }
                    if(!empty($tmpOrder)){
                        $customerOrder[] = $tmpOrder;
                        unset($tmpOrder);
                    }
                }
            }
            
            if(trim($value['unvalid_reason']) || trim($value['customer_status']) == '无效'){
                //无效客资
                $tmpInfo['is_valid'] = 3;
                $tmpInfo['unvalid_reason'] = trim($value['unvalid_reason']);
            }else{
                switch (trim($value['customer_status'])) {
                  case '待定（销售）':
                  case '保留':
                      $tmpInfo['c_star_day'] = $tmpInfo['dj_day'];
                      $tmpInfo['is_valid'] = 2;
                      break;
                  case '前期有效，后期无效':
                      $tmpInfo['is_valid'] = 3;
                      $tmpInfo['unvalid_reason'] = '前期有效，后期无效';
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                      break;
                  case '别家成交':
                      $tmpInfo['is_loss'] = 1;
                      $tmpInfo['is_valid'] = 1;
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                      break;
                  case '到店流失':
                      $tmpInfo['erp_loss'] = 1;
                      $tmpInfo['is_valid'] = 1;
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                  case '预约到店':
                      $tmpInfo['is_intostore'] = 5;
                      $tmpInfo['is_valid'] = 1;
                      if(trim($value['yjd_day'])){
                          $tmpInfo['yjd_day'] = $tmpInfo['syjd_day'] = date('Y-m-d H:i:s',strtotime($value['yjd_day']));
                      }
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                      break;
                  case '跟踪中':
                      $tmpInfo['is_valid'] = 1;
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                      break;
                  case '到店成交':
                  case '网付成交':
                      $tmpInfo['is_valid'] = 1;
                      $tmpInfo['order_type'] = trim($value['unvalid_reason'])=='到店成交'?2:1;
                      $tmpInfo['is_intostore'] = 2;
                      $tmpInfo['c_star_day'] = $tmpInfo['last_trackday'] = $tmpInfo['dj_day'];
                      if(trim($value['yjd_day'])){
                          $tmpInfo['yjd_day'] = $tmpInfo['syjd_day'] = date('Y-m-d H:i:s',strtotime($value['yjd_day']));
                      }
                  default:
                    # code...
                    break;
                }
            }
            if(strlen($tmpInfo['unvalid_reason']) > 199){
                $tmpInfo['customer_remark'] = $tmpInfo['customer_remark']."\r\n 客资无效原因：".$tmpInfo['unvalid_reason'];
                $tmpInfo['unvalid_reason'] = msubstr($tmpInfo['unvalid_reason'],0,190);
            }
            $customerInfo[] = $tmpInfo;
            
            if(!empty($tmpTrace)){
                $customerTrace[] = $tmpTrace;
            }
            
            unset($tmpOrder,$tmpTrace);
            $update[] = [
                    'id'=>$value['id'],
                    'msg'=>'导入成功',
                    'status'=>1,
                ];
        }        


        try{
            db()->startTrans();
            db('c_customerinfo')->insertAll($customerInfo);
            db('c_customer_contact')->insertAll($customerContact);
            db('c_customer_from')->insertAll($customerFrom);
            db('c_customer_order')->insertAll($customerOrder);
            db('c_salestrackinginfo')->insertAll($customerTrace);
            foreach ($update as $infoup) {
                db('tcdj_crm')->where('id',$infoup['id'])->update($infoup);
            }
            db()->commit();
        }catch (\Exception $e){
            $this->log('数据库操作失败：'.$e->getMessage());
            $this->log('SQL：'.db()->getLastSql());
            db()->rollback();
            exit();
        }
    }
    private function initConsultation(){
        $consultation = $this->conmc->name('s_consumptiontype')->field('id,consumption_name')->select();
        $this->consultation = $consultation;
    }
    private function initSystemChannel(){
        $channel = db('s_channel')
                 ->field('id,channel_class_id,channel_class_name,channel_info_id,channel_info_name,channel_name')
                 ->select();
        $this->channel = $channel;
    }
    private function initAllStaff(){
        $staff = $this->conmc->name('s_staffinfo')->field('id,staffnumber,staffname,department_id,department_name,shop_name,shop_code,brandclass,brandclass_id')->select();
        $this->staff = $staff;
    }
    // 客资id，录入id，客资所属店shop_code
    // 
    // 
    /*
     *晶视觉转档
     */
    public function CaomeijuanJsj(){
        set_time_limit(0);
        $this->test = false;//正式使用时，请把这里设成true.否则只导五批数据。
        $this->step = 1;
        $DB = [
          'type'            => 'mysql',
          'hostname'=>'127.0.0.1',
          'username'=>'root',
          'database'=>'jsj_crm_import',
          'password'=>'123456',
        ];
        $dbSx = [
          'type'            => 'mysql',
          'hostname'=>'127.0.0.1',
          'username'=>'root',
          'database'=>'sx_crm_jz',
          'hostport'=>'3306',
          'password'=>'123456',
        ];

        $this->log("开始晶视觉导库操作");

        $this->consx = @Db::connect($dbSx) or die("无法连接素玄数据库");
        $this->con = @Db::connect($DB) or die("无法连接业务数据库");

        if(!is_writeable($this->sqlPath)){
          die("目录：".$this->sqlPath.";不可写，请确保临时SQL目录可写。");
        }
        //转客资主数据
        unlink($this->sqlPath.DS.'c_customerinfo.sql');
        $this->JSJ_customerinfo();

    }
    public function JSJ_customerinfo(){
        $sql = <<<EOF
SELECT 
cs.typeid,
c.id,c.letterid,c.KZID,
c.CLASSID,c.STATUSID
,c.CREATETIME,c.KZNAME,c.KZPHONE,c.KZWECHAT,c.KZQQ,c.SEX 性别,c.SOURCEID
,c.COLLECTORID 提报人ID,
cd.COLLECTORNAME 提报人姓名,
c.PROMOTORID 筛客人ID,
cd.PROMOTERNAME 筛客人姓名,
c.APPOINTORID 追踪人,
cd.APPOINTNAME 追踪人姓名,
cd.memo 备注,
c.TRACETIME 追踪时间,c.APPOINTTIME 预约进店时间,c.ACTUALTIME 实际进店时间,c.UPDATETIME,c.SHOPID 意向店面ID,
cd.SHOPNAME 意向店面,
c.RECEPTORID 门市接待ID ,
cd.RECEPTORNAME 门市接待,
#c.VALIDTIME  有效时间,
c.COMESHOPTIME 首次到店时间,c.SUCCESSTIME 成交时间,cd.AMOUNT 套系金额,cd.STAYAMOUNT 已收金额
,c.RECEIVETIME 接收时间,
#cd.OLDKZID 老客资ID,
cd.OLDKZNAME 老客姓名 ,cd.OLDKZPHONE 老客手机 ,cd.ORDERIMG 订单图片,cd.TALKIMG 聊天图片地址
,c.LASTPUSHTIME 最后推送时间
,c.ALLOTTYPE 分配类型0手动1自动

,cd.YXLEVEL 客资标记,cd.YSRANGE 预算,
#cd.MARRYMEMO 婚期,
cd.MARRYTIME 结婚日期,
#cd.YPMEMO 预拍,cd.YPTIME 预计拍摄日期,
cd.ADDRESS,cd.MATENAME 配偶,cd.MATEPHONE 配偶手机,cd.MATEWECHAT 配偶微信,cd.MATEQQ 配偶QQ,
#cd.BIRTHTIME 生日,cd.IDNUM 证件号,
cd.INVALIDCODE 无效原因编号,cd.INVALIDLABEL 无效原因, cd.INVALIDMEMO 无效原因描述,cd.KEYWORD 关键词,c.isdel
,cd.adaddress ,cd.adid,cd.zxstyle,
cr.content REMARK


FROM hm_crm_client_info c
left join hm_crm_client_detail cd on cd.KZID = c.KZID
left join hm_crm_source cs on cs.ID = c.SOURCEID
left join hm_crm_client_remark cr on cr.KZID = c.KZID
where 
c.isdel =0 and c.COMPANYID = 885
#and c.KZID = '432730e4e1225df12b57c5ee91ca1c27'
#and  c.ID in('304795','305005')
#and  c.ID in('304795')
#and c.letterid ='66HWHZ'
#and cs.typeid > 3
order by c.CREATETIME desc
EOF;

      $this->thisStep++;
      $count = $this->con->name('hm_crm_client_info')->where('isdel',0)->where('COMPANYID',885)->count();
      $address = require_once('address.php');

      $page = 0;
      $allPage = ceil($count/$this->perSqlDataNum);
      if($this->test){
          $allPage = 5;//正式后删除，先做5次测试。
      }
      $this->log("开始处理客资主数据，待处理总数量：".$count);
      $i = 0;
      while($page < $allPage){
          $page++;
          $start = ($page - 1) * $this->perSqlDataNum;
          $sqlQuery = $sql . ' limit '.$start.','.$this->perSqlDataNum;
          $res = $this->con->query($sqlQuery);
      ///////////////客资主表信息
      ///
      $allData = [];
      $tmpValue = [];
      foreach ($res as $key => $value) {
          if(!empty($tmpValue) && count($tmpValue) != count($data)){
              $this->log('有不匹配的数据列，前一数据列：');
              print_r($tmpValue);
              $this->log('当前数据列：');
              print_r($data);
              die;
          }else{
              $tmpValue = $data;
          }
          $i++;

          $data = [];
          //基础客资信息
          $data['id'] = $value['id'];
          $data['customer_name'] = $value['KZNAME'];
          $data['customer_number'] = $value['KZID'];
          $data['customer_sex'] = $value['性别']==1?'女':($value['性别']==9?'':'男');
          $data['customer_tel'] = $value['KZPHONE'];
          $data['customer_wechat'] = $value['KZWECHAT'];
          $data['customer_qq'] = $value['KZQQ'];
          $data['mate_name'] = $value['配偶'];
          //$data['mate_sex'] = $value['性别0男1女9不详']==1?'女':($value['性别0男1女9不详']==9?'':'男');//原系统内配偶性别与客资性别是一个
          $data['mate_tel'] = $value['配偶手机'];
          $data['mate_wechat'] = $value['配偶微信'];
          $data['mate_qq'] = $value['配偶qq'];

          $data['consultation_type_id'] = 1;
          $data['consultation_type'] = '婚纱照';

          $data['introducer_number'] = $value['老客资ID'];
          $data['introducer_name'] = $value['老客姓名'];
          $data['introducer_tel'] = $value['老客手机'];

          $data['budget'] = $value['预算'];
          $data['customer_birthday'] = $value['生日']?substr($value['生日'],0,10):'';

          if($value['筛客人ID']){
            $selStaff = $this->getStaffDB($value['筛客人ID']);
            $data['sk_staff'] = $selStaff?$selStaff['staffname']:$value['筛客人姓名'];
            $data['sk_staff_id'] = $selStaff?$selStaff['id']:$value['筛客人ID'];
          }else{
            $data['sk_staff'] = null;
            $data['sk_staff_id'] = null;
          }
          $data['dj_day'] = $this->timeFormat($value['CREATETIME']);
          $data['create_time'] = $this->timeFormat($value['CREATETIME']);
          $data['update_time'] = $this->timeFormat($value['UPDATETIME']);
          $data['last_trackday'] = $data['update_time'];
          $data['creator_day'] = $this->timeFormat($value['CREATETIME']);
          $data['yx_day'] = $this->timeFormat($value['有效时间']);
          $data['order_day'] = $this->timeFormat($value['成交时间']);
          $data['yp_day'] = $this->timeFormat($value['预计拍摄日期']);


          $data['customer_remark'] = $value['REMARK']?'推广首次备注：'.$value['REMARK'].'；客资备注：'.$value['备注']:$value['备注'];
          $data['is_valid'] =  2;//默认待定状态
          $data['is_loss'] = 2;
          $data['is_fp'] = 2;
          $data['is_success'] = 2;
          if($value['isdel'] == 1){
            $data['is_status'] = 0;
            $data['del_time'] = $this->timeFormat($value['UPDATETIME']);
          }else{
            $data['is_status'] = 1;
            $data['del_time'] = NULL;
          }

          $sign = [
            1=>['id'=>33,'sign'=>'A++'],
            2=>['id'=>34,'sign'=>'A'],
            3=>['id'=>35,'sign'=>'B'],
            4=>['id'=>36,'sign'=>'C'],
            5=>['id'=>37,'sign'=>'D'],
          ];
          //客资标记
          $data['sign_id'] = $sign[$value['客资标记']]['id'];
          $data['sign_name'] = $sign[$value['客资标记']]['sign'];

          //意向
          $data['customer_intention_id'] = $sign[$value['客资标记']]['id'];
          $data['customer_intention'] = $sign[$value['客资标记']]['sign'];

          $data['brandclass'] = '晶视觉婚纱摄影工作室';
          $data['brandclass_id'] = 4;
          $data['is_first_tel'] = ($data['customer_tel'] || $data['mate_tel'])?1:0;
          $data['customer_key'] = $value['关键词'];
          if($value['adid']){
            $data['channel_plan_number'] = $value['adid'];
          }else{
            $data['channel_plan_number'] = '';
          }
          if($value['adaddress']){
            $data['ad_page'] = $value['adaddress'];
          }else{
            $data['ad_page'] = '';
          }
          
          
          //处理客户地区及详细地址信息
          $data['province_id'] = 0;
          $data['province'] = '';
          $data['city_id'] = 0;
          $data['city'] = '';
          $data['county_id'] = 0;
          $data['county'] = '';
          $data['customer_address'] = '';

          $add = explode(',', $value['ADDRESS']);
          if($add[0]){
            foreach ($address['1'] as $k=>$v) {
                if(strpos($k,$add[0]) !== false){
                  $data['province_id'] = $v;
                  $data['province'] = $k;
                  break;
                }
            }
          }
          
          if($add[1]){
            foreach ($address['2'] as $k=>$v) {
                if(strpos($k,$add[1]) !== false){
                  $data['city_id'] = $v;
                  $data['city'] = $k;
                  break;
                }
            }
          }
          if($add[2]){
            foreach ($address['3'] as $k=>$v) {
                if(strpos($k,$add[2]) !== false){
                  $data['county_id'] = $v;
                  $data['county'] = $k;
                  break;
                }
            }
          }
          if($add[3]){
            $data['customer_address'] = $add[3];
          }
          /*
          //意向店面
          $shopList = [
            2025=>[
                'shop_name'=>'徐汇店',
                'shop_code'=>'JZ005',
            ],
            2026=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            2027=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            2503=>[
                'shop_name'=>'静安旗舰店',
                'shop_code'=>'JZ002',
            ],
            3758=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            
          ];*/
          if($value['意向店面ID']){
              $data['consulation_shop_code'] = 'JSJ001';//$shopList[$value['意向店面ID']]['shop_code'];
              $data['consulation_shop'] = '晶视觉摄影工作室';//$shopList[$value['意向店面ID']]['shop_name'];
          }else{
            $data['consulation_shop_code'] = '';
            $data['consulation_shop'] = '';
          }

          //处理客资渠道、来源、客资类型等
          $data['customer_type'] = 1;
          $data['c_from'] = 1;
          $data['customer_cource_id'] = 1;
          $data['customer_cource'] = '电商';
          $data['customer_channel_id'] = 1;
          $data['customer_channel'] = '其他';
          // $data['shop_code'] = '';
          // $data['shop_name'] = '';
          // $data['add_shop_code'] = '';
          // $data['add_shop_name'] = '';
          $data['shop_code'] = 'JSJ001';
          $data['shop_name'] = '晶视觉摄影工作室';
          $data['add_shop_code'] = 'JSJ001';
          $data['add_shop_name'] = '晶视觉摄影工作室';
          $channelList = $this->getChannelListDB();
          if($value['typeid'] == 1){
            //电商的客资
            
            $data['customer_channel_id'] = $channelList[$value['SOURCEID']]['id'];
            $data['customer_channel'] = $channelList[$value['SOURCEID']]['customer_channel'];
            $data['customer_type'] = 1;
            $data['c_from'] = 1;
            $data['customer_cource_id'] = 1;
            $data['customer_cource'] = '电商';

          }
          if($value['typeid'] == 3 || $value['typeid'] == 2){
            //转介绍客资
            $data['customer_channel_id'] = $channelList[$value['SOURCEID']]['id'];//待处理。。。
            $data['customer_channel'] = $channelList[$value['SOURCEID']]['customer_channel'];//待处理。。。

            $data['customer_type'] = 2;
            $data['c_from'] = 1;
            $data['customer_cource_id'] = 2;
            $data['customer_cource'] = '转介绍';
            $data['access_model_id'] = 3;
            $data['access_model'] = '老客户介绍';

            $data['shop_code'] = 'JSJ001';
            $data['shop_name'] = '晶视觉摄影工作室';

            //$data['add_shop_code'] = 'WX001';//待处理
            //$data['add_shop_name'] = '电商中心';//待处理

          }
          if($value['typeid'] > 3 ){
            //门店自然进客客资
            $data['customer_channel_id'] = 0;//待处理。。。
            $data['customer_channel'] = '';//待处理。。。
            $data['customer_cource_id'] = 7;
            $data['customer_cource'] = '自然进店';
            $data['customer_type'] = 1;
            $data['c_from'] = 2;
          }

          if(!$data['customer_cource_id']){
            $data['customer_cource_id'] = 8;
            $data['customer_cource'] = '其他';
            $data['c_from'] = 1;
            $data['customer_type'] = 1;
          }

          if($value['zxstyle'] == 1){
            $data['access_model_id'] = 1;
            $data['access_model'] = '对话咨询';
          }
          elseif ($value['zxstyle'] == 2){
            $data['access_model_id'] = 2;
            $data['access_model'] = '报名资询';
          }
          else{
            $data['access_model_id'] = 3;
            $data['access_model'] = '表单咨询';
          }
          /*
          //客户地域
          $regionList = [
            "黄浦区"=>"1",
            "徐汇区"=>"2",
            "长宁区"=>"3",
            "静安区"=>"4",
            "普陀区"=>"5",
            "虹口区"=>"6",
            "杨浦区"=>"7",
            "闵行区"=>"8",
            "宝山区"=>"9",
            "嘉定区"=>"10",
            "松江区"=>"12",
            "金山区"=>"13",
            "青浦区"=>"14",
            "奉贤区"=>"15",
            "崇明区"=>"16",
            "浦东新区"=> "11"
            ];
          if($regionList[$data['county']]){
            $data['customer_region_id'] = $regionList[$data['county']];
            $data['customer_region'] = $data['county'];
          }else{
            $data['customer_region_id'] = 0;
            $data['customer_region'] = '';
          }
          */
          //处理客资各种状态
          $classId = intval($value['CLASSID']);
          $statusId = intval($value['STATUSID']);
          $data['is_intostore'] = 1;//未进店
          $salesStaff = $this->getStaffDB($value['追踪人']);
          $data['sales_staff_id'] = $salesStaff?$salesStaff['id']:$value['追踪人'];//追踪人，待处理。。。
          $data['sales_staff'] = $salesStaff?$salesStaff['staffname']:$value['追踪人姓名'];//追踪人，待处理。。。
          $data['sales_staff_number'] = '';//
          $data['jr_day'] = null;
          $data['c_star_day'] = null;
          if($classId == 1 && ($statusId == 0 || $statusId == 98)){
              //待定
              $data['is_valid'] =  2;
          }else if($classId == 6){
              if($statusId == 5 || $statusId == 99){
                //无效
                $data['is_valid'] =  3;
              }else if($statusId == 4){
                //驳回
                $data['is_valid'] =  4;
              }else{
                //邀约流失-算有效
                $data['is_valid'] =  1;
                $data['is_intostore'] = 4;
              }
          }else{
            //其他全算有效
            $data['is_valid'] =  1;
          }
          $data['yjd_day'] = $data['syjd_day'] =  null;
          $data['intostore_note'] = '';
          if($value['预约进店时间']){
              $data['yjd_day'] = $this->timeFormat($value['预约进店时间']);
              $data['intostore_note'] = $value['备注'];
          }
          if($value['实际进店时间']){
              $data['syjd_day'] = $this->timeFormat($value['实际进店时间']);
              $data['is_intostore'] = 2;
          }
          $data['totalpay_money'] = $value['套系金额'];
          $data['fact_money'] = $value['已收金额'];
          $data['op_signinfo'] = '';
          $data['is_next'] = 2;
          $data['next_time'] = NULL;
          $data['yjd_action_day'] = NULL;
          if($value['接收时间']){
              $data['jr_day'] = $this->timeFormat($value['接收时间']);//接收时间
              $data['is_fp'] = 1;
              $data['c_star_day'] = $this->timeFormat($value['接收时间']);
          }
          
          $data['unvalid_id'] = $value['无效原因编号'];
          $data['unvalid_reason'] = $value['无效原因'];
         
          if($value['门市接待ID']){
              $receptionStaff = $this->getStaffDB($value['门市接待ID']);
              $data['reception_id'] = $receptionStaff?$receptionStaff['id']:$value['门市接待ID'];//门市接待人，待处理。。。
              $data['reception_name'] = $receptionStaff?$receptionStaff['staffname']:$value['门市接待'];//门市接待人，待处理。。。
          }else{
            $data['reception_id']  =0;
            $data['reception_name'] = '';
          }

          if($classId == 4){
            #已进店
            switch ($statusId) {
                case 39:
                  //非进客进店，即无效进店
                  $data['is_intostore'] = 3;
                break;
                case 33:
                  #进店保留--定金
                case 35:
                  #回门保留--定金
                  $data['is_success'] = 3;
                break;
                case 10:
                  #进店未成交
                  $data['is_intostore'] = 5;
                  $data['is_loss'] = 1;
                case 36:
                  #回门流失--进店未成交
                  $data['is_intostore'] = 5;
                  $data['is_loss'] = 1;
                break; 
            }
          }
          if($classId == 5){
              //已成交
              $data['is_success'] = 1;
              $data['totalpay_money'] = $value['套系金额'];
              $data['fact_money'] = $value['已收金额'];
          }
          
          $djStaff = $this->getStaffDB($value['提报人ID']);
          $data['dj_staff'] = $djStaff?$djStaff['staffname']:$value['提报人姓名'];//录入人，待处理。。。
          $data['dj_staff_id'] = $djStaff?$djStaff['id']:$value['提报人ID'];//录入人，待处理。。。
          $allData[] = $data;
          
        }
        #######################客资主表一次结束
        $sqlCustomer = $this->consx->name('c_customerinfo')->fetchSql()->insertAll($allData);
        $fileName = $this->sqlPath.DS.'c_customerinfo.sql';
        file_put_contents($fileName, $sqlCustomer.";\r\n",FILE_APPEND);
        $percent = sprintf('%.2f',($i/$count) * 100);
        $this->log("客资表数据完成：".$i.'/'.$count."【{$percent}%】");
        unset($data,$allData,$sqlQuery,$sqlCustomer);
      }//总while
          unset($sql,$address);
          die;
      ///////////////////////////////////////////追踪记录
          //
    }
    public function timeFormat($timestamp){

        return $timestamp?date('Y-m-d H:i:s',$timestamp):NULL;
    }
    public function getStaffDB($id){
      static $allStaffs;
      if(!$allStaffs){
        $res = $this->con->name('s_staffinfo')->select();
          foreach ($res as  $value) {
            $allStaffs[$value['cmj_staff_id']] = $value;
          }
      }
      return $allStaffs[$id];

    }
    public function getChannelListDB(){
      static $allChannels;
      if(!$allChannels){
        $res = $this->con->name('crm_channels')->select();
          foreach ($res as  $value) {
            $allChannels[$value['cmj_customer_channel_id']] = $value;
          }
      }
      return $allChannels;
    }
    /**
     * 上海桔子数据转档
     */
    public function Caomeijuan($step){
        set_time_limit(0);
        $this->test = false;//正式使用时，请把这里设成true.否则只导五批数据。
        $this->step = 2;
        $DB = [
          'type'            => 'mysql',
          'hostname'=>'127.0.0.1',
          'username'=>'root',
          'database'=>'hm-crm',
          'password'=>'123456',
        ];
        $dbSx = [
          'type'            => 'mysql',
          'hostname'=>'127.0.0.1',
          'username'=>'root',
          'database'=>'sx_crm',
          'hostport'=>'3306',
          'password'=>'123456',
        ];
        $dbSxLog = [
          'type'            => 'mysql',
          'hostname'=>'10.120.131.251',
          'username'=>'root',
          'database'=>'sx_crm_logs',
          'hostport'=>'3306',
          'password'=>'mysql@sx1120',
        ];
        $dbmc = [
          'type'            => 'mysql',
          'hostname'=>'127.0.0.1',
          'username'=>'root',
          'database'=>'sx_mc',
          'hostport'=>'3306',
          'password'=>'123456',
        ];
        $this->log("开始草莓卷导库操作");

        $this->consx = @Db::connect($dbSx) or die("无法连接素玄数据库");
        $this->con = @Db::connect($DB) or die("无法连接业务数据库");
        $this->conmc = @Db::connect($dbmc) or die("无法连接素玄MC数据库");

        if(!is_writeable($this->sqlPath)){
          die("目录：".$this->sqlPath.";不可写，请确保临时SQL目录可写。");
        }
        if($step == 'init'){
            $this->log("开始初始化...");
            $this->log("生成渠道对应关系表...");
            $this->initChannelExcel();
            $this->log("生成员工对应关系表...");
            $this->initStaffExcel();
            $this->log("生成门店对应关系表...");
            $this->initShopExcel();
            $this->log("初始化完成");
            exit();
        }
        //转客资主数据
        unlink($this->sqlPath.DS.'c_customerinfo.sql');
        $this->JZ_customerinfo();

        //追踪记录数据
        // unlink($this->sqlPath.DS.'c_salestrackinginfo.sql');
        // $this->JZ_translog();

        //操作日志数据
        // unlink($this->sqlPath.DS.'action_log.sql');
        // $this->consx = @Db::connect($dbSxLog) or die("无法连接素玄日志数据库");
        // $this->JZ_actionlog();
          
          /**
           
更新人员


select a.id,a.customer_name,a.reception_id,a.`reception_name`#,a.`sales_staff_number`
,b.`sx_staff_id`,b.`sx_staff_number`,b.`staff_name` from c_customerinfo a 
left join tmp_all_staffs b on b.`cmj_staff_id` = a.`reception_id` 
where b.sx_staff_id is not null
order by a.`create_time` DESC
limit 100 


把人员ID更新成素玄的：

把tmp_all_staff表的sx_group_id更新为员工的部门ID，sx_goup_name更新为组名

insert into c_customer_from (cid) select c.id from c_customerinfo c 


以下信息要这么做：
筛客人 sk_staff_id     sk_staff
登记人 dj_staff_id     dj_staff
追踪人 sales_staff_id    sales_staff
分配人 fp_staff_id  fp_staff
获取电话的人 tel_staff_id tel_staff
获取微信 wechat_staff_id wechat_staff
获取qq qq_staff_id qq_staff
SQL如下：
=================

update c_customerinfo a left join tmp_all_staffs b on b.`cmj_staff_id` = a.`dj_staff_id`
set a.dj_staff_id = b.sx_staff_id 
,a.dj_staff = b.staff_name
,a.dj_staff_number = b.sx_staff_number
a.dj_department_id = b.sx_group_id
where b.sx_staff_id is not null

update c_customerinfo a left join tmp_all_staffs b on b.`cmj_staff_id` = a.`sales_staff_id`
set a.sales_staff_id = b.sx_staff_id 
,a.sales_staff = b.staff_name
,a.sales_staff_number = b.sx_staff_number,
a.sales_department_id = b.sx_group_id
where b.sx_staff_id is not null

reception_id  '进店/转订单  时的 接待人'
reception_name  '进店/转订单  时的 接待人'

update c_customerinfo a left join tmp_all_staffs b on b.`cmj_staff_id` = a.`reception_id`
set a.reception_id = b.sx_staff_id 
,a.reception_name = b.staff_name
where b.sx_staff_id is not null

====================



customer_channel
customer_channel_id   转介绍客资的话，这两个字段存员工所属的部门就行

类似如下语句：（前提是已经把上面的人员ID都更新好了）

update c_customerinfo a left join tmp_all_staffs b on b.`sx_staff_id` = a.`dj_staff_id`
set a.customer_channel_id = b.sx_group_id
#,a.sales_staff_number = b.sx_staff_number
,a.customer_channel = b.sx_group_name
where b.sx_staff_id is not null and b.sx_group_name  is not null
and a.id > 306253 and a.customer_type = 2



更新联系方式


UPDATE c_customerinfo SET  customer_tel = REPLACE(REPLACE(customer_tel, CHAR(10), ''), CHAR(13), '')
where customer_tel is not null;
UPDATE c_customerinfo SET  customer_tel = trim(customer_tel)
where customer_tel is not null;

insert into c_customer_contact(c_id,contact,brandclass)
select id,customer_tel,'上海桔子摄影' from c_customerinfo where customer_tel is not null



UPDATE c_customerinfo SET  customer_wechat = trim(REPLACE(REPLACE(customer_wechat, CHAR(10), ''), CHAR(13), ''))
where customer_wechat is not null;

insert into c_customer_contact(c_id,contact,brandclass)
select id,customer_wechat,'上海桔子摄影' from c_customerinfo 
where customer_wechat is not null and customer_wechat not in (select contact from c_customer_contact)


UPDATE c_customerinfo SET  customer_qq = trim(REPLACE(REPLACE(customer_qq, CHAR(10), ''), CHAR(13), ''))
where customer_qq is not null;


insert into c_customer_contact(c_id,contact,brandclass)
select id,customer_qq,'上海桔子摄影' from c_customerinfo 
where customer_qq is not null and customer_qq not in (select contact from c_customer_contact)


把追踪记录以及Actionlog的人员信息更新了：

update c_salestrackinginfo a left join tmp_all_staffs b on b.`cmj_staff_id` = a.sales_staff_id
set a.sales_staff_id = b.sx_staff_id 
,a.sales_staff = b.staff_name,
a.sales_staff_number = b.sx_staff_number
where b.sx_staff_id is not null;

           */

    }

    function JZ_smslog(){
        /*
          把hm_crm_sms_log_283 导到CRM库内，
          执行以下语句：
INSERT INTO `c_send_message_log` (receive_phone,send_content,send_id,send_time)
select tarphone,content,operaid,createtime from hm_crm_sms_log_sx where companyid=283


         */

  $this->log("短信日志数据采用手动操作，详细crm/lib/Runtransdata.php 内的： JZ_smslog方法：");
      return true;

    }
    /**
     * 追踪记录
     * 
     */
    function JZ_translog(){
      /*
      
      1.复制表hm_crm_invitation_log_284 到 c_salestrackinginfo_hm
      2.依次执行以下语句

选删除主键，如果有的话

创建主键ID：
ALTER TABLE `c_salestrackinginfo_hm`
  ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (`id`);

ALTER TABLE c_salestrackinginfo_hm ADD COLUMN customer_id int(11) DEFAULT NULL;
ALTER TABLE c_salestrackinginfo_hm ADD COLUMN customer_name varchar(200) DEFAULT NULL;


update c_salestrackinginfo_hm a left join hm_crm_client_info_sx b on b.KZID=a.KZID set a.customer_id = b.id,a.customer_name = b.KZNAME

    3.把表拷到CRM库内，执行下面语句
  truncate table c_salestrackinginfo;
insert into c_salestrackinginfo
(customer_id,customer_name,customer_number,sales_day,sales_staff_id,sales_remark)
select customer_id,customer_name,KZID,createtime,staffid,yymemo from c_salestrackinginfo_hm where companyid = 283 and isdel = 0 ;
或：
insert into c_salestrackinginfo
(customer_id,customer_name,customer_number,sales_day,sales_staff_id,sales_remark)
select customer_id,customer_name,KZID,FROM_UNIXTIME(createtime,'%Y-%m-%d %H:%i:%s'),staffid,yymemo 
from c_salestrackinginfo_hm where companyid = 885 ;
       */
      
  $this->log("追踪记录数据采用手动操作，详细crm/lib/Runtransdata.php 内的： JZ_translog方法：");
      return true;


        $this->thisStep++;
        //处理追踪记录数据
        $sqlTrans = <<<EOF
select cl.yymemo,cl.createtime,cl.staffid,c.id,c.KZNAME,c.KZID from hm_crm_invitation_log_284 cl
left join hm_crm_client_info_284 c on c.KZID = cl.kzid
where 
cl.COMPANYID = 283
#and cl.KZID= '2cd011d4ad90a109f1e48523592fabba'
order by createtime
EOF;


          $count = $this->con->name('hm_crm_invitation_log_284')->where('COMPANYID',283)->count();
          $page = 0;
          $allPage = ceil($count/$this->perSqlDataNum);
          if($this->test){
              $allPage = 5;//正式后删除，先做5次测试。
          }
          
          $this->log("开始追踪记录数据，待处理总数量：".$count);
          $i = 0;
          while($page < $allPage){
              $page++;
              $start = ($page - 1) * $this->perSqlDataNum;
              $sqlQuery = $sqlTrans . ' limit '.$start.','.$this->perSqlDataNum;
              $resTrans = $this->con->query($sqlQuery);
              $tmpValue = [];
              foreach ($resTrans as $key => $value) {
                if(!empty($tmpValue) && count($tmpValue) != count($dataTrans)){
                    $this->log('有不匹配的数据列，前一数据列：');
                    print_r($tmpValue);
                    $this->log('当前数据列：');
                    print_r($dataTrans);
                    die;
                }else{
                    $tmpValue = $dataTrans;
                }
                  $i++;
                  $dataTrans = [];
                  $dataTrans['customer_id'] = $value['id'];
                  $dataTrans['customer_name'] = $value['KZNAME'];
                  $dataTrans['customer_number'] = $value['KZID'];
                  $dataTrans['sales_day'] = $value['createtime'];
                  $dataTrans['sales_staff_id'] = $value['staffid'];
                  $dataTrans['sales_remark'] = $value['yymemo'];
                  $allData[] = $dataTrans;
                  unset($dataTrans);

              }

              $sqlCustomer = $this->consx->name('c_salestrackinginfo')->fetchSql()->insertAll($allData);
              $fileName = $this->sqlPath.DS.'c_salestrackinginfo.sql';
              file_put_contents($fileName, $sqlCustomer.";\r\n",FILE_APPEND);
              $percent = sprintf('%.2f',($i/$count) * 100);
              $this->log("追踪记录数据已完成：".$i.'/'.$count."【{$percent}%】");
              unset($dataTrans,$allData,$sqlQuery,$sqlCustomer,$tmpValue);
                  ///////////////客资追踪记录信息
              ///
          }
    }
    /**
     * 客资主表数据
     * 
     */
    function JZ_customerinfo(){
        $sql = <<<EOF
SELECT 
cs.typeid,cs.SRCTYPE,
c.id,c.KZID,cd.KZID,
c.CLASSID,c.STATUSID
,c.CREATETIME,c.KZNAME,c.KZPHONE,c.WECHAT,c.KZQQ,c.SEX 性别,c.SOURCEID
,c.COLLECTORID 提报人ID,c.COLLECTORNAME 提报人姓名,
c.APPOINTID 追踪人,c.APPOINTNAME 追踪人姓名,c.memo 备注,
c.TRACETIME 追踪时间,c.APPOINTTIME 预约进店时间,c.ACTUALTIME 实际进店时间,c.UPDATETIME,c.SHOPID 意向店面ID,c.SHOPNAME 意向店面,
c.RECEPTORID 门市接待ID ,c.RECEPTORNAME 门市接待
,c.VALIDTIME  有效时间,c.COMESHOPTIME 首次到店时间,c.SUCCESSTIME 成交时间,c.AMOUNT 套系金额,c.STAYAMOUNT 已收金额
,c.RECEIVETIME 接收时间
,c.OLDKZID 老客资ID,c.OLDKZNAME 老客姓名 ,c.OLDKZPHONE 老客手机 ,c.ORDERIMG 订单图片,c.TALKIMG 聊天图片地址
,c.LASTPUSHTIME 最后推送时间
,c.ALLOTTYPE 分配类型0手动1自动
,cd.YXLAVEL 客资标记,cd.YSRANGE 预算,cd.MARRYMEMO 婚期,cd.MARRYTIME 结婚日期,cd.YPMEMO 预拍,cd.YPTIME 预计拍摄日期
,cd.ADDRESS,cd.MATENAME 配偶,cd.MATEPHONE 配偶手机,cd.MATEWECHAT 配偶微信,cd.MATEQQ 配偶QQ
,cd.BIRTHTIME 生日,cd.IDNUM 证件号,cd.INVALIDCODE 无效原因编号,cd.INVALIDLABEL 无效原因, cd.KEYWORD 关键词,c.isdel
,cd.adaddress ,cd.adid,cd.zxstyle,cd.REMARK
FROM hm_crm_client_info_283 c
left join hm_crm_client_info_detail_283 cd on cd.KZID = c.KZID
left join hm_crm_source cs on cs.SRCID = c.SOURCEID
where 
c.isdel =0 and c.COMPANYID = 283
#and c.KZID = '432730e4e1225df12b57c5ee91ca1c27'
#and  c.ID in('304795','305005')
#and  c.ID in('304795')
order by c.CREATETIME desc
EOF;
      $this->thisStep++;
      $count = $this->con->name('hm_crm_client_info_283')->where('isdel',0)->where('COMPANYID',283)->count();
      $address = require_once('address.php');

      $page = 0;
      $allPage = ceil($count/$this->perSqlDataNum);
      if($this->test){
          $allPage = 5;//正式后删除，先做5次测试。
      }
      $this->log("开始处理客资主数据，待处理总数量：".$count);
      $i = 0;
      while($page < $allPage){
          $page++;
          $start = ($page - 1) * $this->perSqlDataNum;
          $sqlQuery = $sql . ' limit '.$start.','.$this->perSqlDataNum;
          $res = $this->con->query($sqlQuery);
      ///////////////客资主表信息
      ///
      $allData = [];
      $tmpValue = [];
      foreach ($res as $key => $value) {
          if(!empty($tmpValue) && count($tmpValue) != count($data)){
              $this->log('有不匹配的数据列，前一数据列：');
              print_r($tmpValue);
              $this->log('当前数据列：');
              print_r($data);
              die;
          }else{
              $tmpValue = $data;
          }
          $i++;

          if($value['typeid'] > 96 || $value['typeid']<93){
            //非桔子一渠道客资不导。93-96为桔子的渠道，其中93为电商渠道，94，95为转介绍渠道，96为线下自然进客客资
            continue;
          }
          $data = [];
          //基础客资信息
          $data['id'] = $value['id'];
          $data['customer_name'] = $value['KZNAME'];
          $data['customer_number'] = $value['KZID'];
          $data['customer_sex'] = $value['性别']==1?'女':($value['性别']==9?'':'男');
          $data['customer_tel'] = $value['KZPHONE'];
          $data['customer_wechat'] = $value['WECHAT'];
          $data['customer_qq'] = $value['KZQQ'];
          $data['mate_name'] = $value['配偶'];
          //$data['mate_sex'] = $value['性别0男1女9不详']==1?'女':($value['性别0男1女9不详']==9?'':'男');//原系统内配偶性别与客资性别是一个
          $data['mate_tel'] = $value['配偶手机'];
          $data['mate_wechat'] = $value['配偶微信'];
          $data['mate_qq'] = $value['配偶qq'];

          $data['consultation_type_id'] = 1;
          $data['consultation_type'] = '婚纱照';

          $data['introducer_number'] = $value['老客资ID'];
          $data['introducer_name'] = $value['老客姓名'];
          $data['introducer_tel'] = $value['老客手机'];

          $data['budget'] = $value['预算'];
          $data['customer_birthday'] = $value['生日']?substr($value['生日'],0,10):'';

          $data['customer_idtype'] = '身份证';
          $data['customer_idnumber'] = $value['证件号'];
          $data['wedding_date'] = $value['结婚日期'];

          $data['dj_day'] = $value['CREATETIME'];
          $data['create_time'] = $value['CREATETIME'];
          $data['update_time'] = $value['UPDATETIME'];
          $data['creator_day'] = $value['CREATETIME'];
          $data['yx_day'] = $value['有效时间'];
          $data['order_day'] = $value['成交时间'];
          $data['yp_day'] = $value['预计拍摄日期'];


          $data['customer_remark'] = $value['REMARK']==$value['备注']?$value['REMARK']:($value['REMARK'].$value['备注']);
          $data['is_valid'] =  2;//默认待定状态
          $data['is_loss'] = 2;
          $data['is_fp'] = 2;
          $data['is_success'] = 2;
          if($value['isdel'] == 1){
            $data['is_status'] = 0;
            $data['del_time'] = $value['UPDATETIME'];
          }else{
            $data['is_status'] = 1;
            $data['del_time'] = NULL;
          }

          $sign = [
            'A'=>1,
            'B'=>2,
            'C'=>3,
            'D'=>4,
          ];
          //客资标记
          $data['sign_id'] = $sign[$value['客资标记']];
          $data['sign_name'] = $value['客资标记'];

          //意向
          $data['customer_intention_id'] = $sign[$value['客资标记']];
          $data['customer_intention'] = $value['客资标记'];


          $data['brandclass'] = '上海桔子摄影';
          $data['brandclass_id'] = 1;
          $data['is_first_tel'] = ($data['customer_tel'] || $data['mate_tel'])?1:0;
          $data['customer_key'] = $value['关键词'];
          if($value['adaddress'] || $value['adid']){
            $data['channel_plan_number'] = $value['adaddress']."【{$value['adid']}】";
          }else{
            $data['channel_plan_number'] = '';
          }
          
          //处理客户地区及详细地址信息
          $data['province_id'] = 0;
          $data['province'] = '';
          $data['city_id'] = 0;
          $data['city'] = '';
          $data['county_id'] = 0;
          $data['county'] = '';
          $data['customer_address'] = '';

          $add = explode(',', $value['ADDRESS']);
          if($add[0]){
            foreach ($address['1'] as $k=>$v) {
                if(strpos($k,$add[0]) !== false){
                  $data['province_id'] = $v;
                  $data['province'] = $k;
                  break;
                }
            }
          }
          
          if($add[1]){
            foreach ($address['2'] as $k=>$v) {
                if(strpos($k,$add[1]) !== false){
                  $data['city_id'] = $v;
                  $data['city'] = $k;
                  break;
                }
            }
          }
          if($add[2]){
            foreach ($address['3'] as $k=>$v) {
                if(strpos($k,$add[2]) !== false){
                  $data['county_id'] = $v;
                  $data['county'] = $k;
                  break;
                }
            }
          }
          if($add[3]){
            $data['customer_address'] = $add[3];
          }
          //意向店面
          $shopList = [
            2025=>[
                'shop_name'=>'徐汇店',
                'shop_code'=>'JZ005',
            ],
            2026=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            2027=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            2503=>[
                'shop_name'=>'静安旗舰店',
                'shop_code'=>'JZ002',
            ],
            3758=>[
                'shop_name'=>'闵行总店',
                'shop_code'=>'JZ001',
            ],
            
          ];
          if($value['意向店面ID']){
              $data['consulation_shop_code'] = $shopList[$value['意向店面ID']]['shop_code'];
              $data['consulation_shop'] = $shopList[$value['意向店面ID']]['shop_name'];
          }else{
            $data['consulation_shop_code'] = '';
            $data['consulation_shop'] = '';
          }

          //处理客资渠道、来源、客资类型等
          $data['customer_type'] = 1;
          $data['c_from'] = 1;
          $data['customer_cource_id'] = 1;
          $data['customer_cource'] = '电商';
          $data['customer_channel_id'] = 1;
          $data['customer_channel'] = '其他';
          $data['shop_code'] = '';
          $data['shop_name'] = '';
          $data['add_shop_code'] = '';
          $data['add_shop_name'] = '';

          if($value['typeid'] == 93){
            //电商的客资
            $channelList = $this->getChannelList();
            $data['customer_channel_id'] = $channelList[$value['SOURCEID']]['channel_id'];
            $data['customer_channel'] = $channelList[$value['SOURCEID']]['channel_name'];
            $data['customer_type'] = 1;
            $data['c_from'] = 1;
            $data['customer_cource_id'] = 1;
            $data['customer_cource'] = '电商';

            $data['shop_code'] = 'WX002';
            $data['shop_name'] = '网销中心';
            $data['add_shop_code'] = 'WX001';
            $data['add_shop_name'] = '电商中心';

          }
          if($value['typeid'] == 94 || $value['typeid'] == 95){
            //转介绍客资
            $data['customer_channel_id'] = 0;//待处理。。。
            $data['customer_channel'] = '';//待处理。。。

            $data['customer_type'] = 2;
            $data['c_from'] = 1;
            $data['customer_cource_id'] = 2;
            $data['customer_cource'] = '转介绍';
            $data['access_model_id'] = 3;
            $data['access_model'] = '老客户介绍';

            $data['shop_code'] = 'WX003';
            $data['shop_name'] = '转介绍中心';

            //$data['add_shop_code'] = 'WX001';//待处理
            //$data['add_shop_name'] = '电商中心';//待处理

          }
          if($value['typeid'] == 96 ){
            //门店自然进客客资
            $data['customer_channel_id'] = 0;//待处理。。。
            $data['customer_channel'] = '';//待处理。。。
            
            $data['customer_cource_id'] = 7;
            $data['customer_cource'] = '自然进店';
            $data['customer_type'] = 1;
            $data['c_from'] = 2;
          }

          if(!$data['customer_cource_id']){
            $data['customer_cource_id'] = 8;
            $data['customer_cource'] = '其他';
            $data['c_from'] = 1;
            $data['customer_type'] = 1;
          }

          if($value['zxstyle'] == '对话咨询'){
            $data['access_model_id'] = 1;
            $data['access_model'] = '对话咨询';
          }else{
            $data['access_model_id'] = 2;
            $data['access_model'] = '报名资询';
          }

          //客户地域
          $regionList = [
            "黄浦区"=>"1",
            "徐汇区"=>"2",
            "长宁区"=>"3",
            "静安区"=>"4",
            "普陀区"=>"5",
            "虹口区"=>"6",
            "杨浦区"=>"7",
            "闵行区"=>"8",
            "宝山区"=>"9",
            "嘉定区"=>"10",
            "松江区"=>"12",
            "金山区"=>"13",
            "青浦区"=>"14",
            "奉贤区"=>"15",
            "崇明区"=>"16",
            "浦东新区"=> "11"
            ];
          if($regionList[$data['county']]){
            $data['customer_region_id'] = $regionList[$data['county']];
            $data['customer_region'] = $data['county'];
          }else{
            $data['customer_region_id'] = 0;
            $data['customer_region'] = '';
          }
          //处理客资各种状态
          $classId = intval($value['CLASSID']);
          $statusId = intval($value['STATUSID']);
          $data['is_intostore'] = 1;//未进店
          $data['sales_staff_id'] = $value['追踪人'];//追踪人，待处理。。。
          $data['sales_staff'] = $value['追踪人姓名'];//追踪人，待处理。。。
          $data['sales_staff_number'] = '';//
          $data['jr_day'] = null;
          $data['c_star_day'] = null;
          if($classId == 0){
            //
            if($statusId == 12){
              //未分配
              $data['is_intostore'] = 1;//未进店
            }else{
              $data['sales_staff_id'] = $value['追踪人'];//追踪人，待处理。。。
              $data['sales_staff'] = $value['追踪人姓名'];//追踪人，待处理。。。
              $data['sales_staff_number'] = $value['追踪人姓名'];//追踪人，待处理。。。 
              $data['jr_day'] = $value['接收时间'];//接收时间
              $data['is_fp'] = 1;
            }
          }else{
              $data['sales_staff_id'] = $value['追踪人'];//追踪人，待处理。。。
              $data['sales_staff'] = $value['追踪人姓名'];//追踪人，待处理。。。
              $data['sales_staff_number'] = $value['追踪人姓名'];//追踪人，待处理。。。 
              $data['jr_day'] = $value['接收时间'];//接收时间
              $data['is_fp'] = 1;
              $data['c_star_day'] = $value['接收时间'];//如果已经处理过了，把接收时间更新成开始追踪时间
          }
          $data['yjd_day'] = $data['syjd_day'] =  null;
          $data['intostore_note'] = '';
          if($value['预约进店时间']){
              $data['yjd_day'] = $value['预约进店时间'];
              $data['intostore_note'] = $value['备注'];
          }
          if($value['实际进店时间']){
              $data['syjd_day'] = $value['实际进店时间'];
              $data['is_intostore'] = 2;
          }
          $data['totalpay_money'] = $value['套系金额'];
          $data['fact_money'] = $value['已收金额'];
          $data['op_signinfo'] = '';
          $data['is_next'] = 2;
          $data['next_time'] = NULL;
          $data['yjd_action_day'] = NULL;
          if($classId == 1){
              switch ($statusId) {
                case 18:
                  #72小时内追踪
                  $data['op_signinfo'] = '72小时跟踪';
                case 19:
                  #1周内追踪
                  $data['op_signinfo'] = '1周内再跟踪';
                case 20:
                  #1个月内追踪
                  $data['op_signinfo'] = '1月内再跟踪';
                case 11:
                  #半年内追踪
                  $data['op_signinfo'] = '半年内再跟踪';
                case 22:
                  #1年内追踪
                  $data['op_signinfo'] = '一年内再跟踪';
                case 23:
                  #1.5年内追踪
                  $data['op_signinfo'] = '1.5年内再跟踪';
                case 14:
                  #2年内追踪
                  $data['is_next'] = 1;
                  $data['next_time'] = $value['追踪时间'];
                  $data['is_valid'] =  1;
                  $data['is_fp'] = 1;
                  break;   
                case 28:
                  #在线保留 
                  $data['op_signinfo'] = '在线保留';
                  $data['totalpay_money'] = $value['套系金额'];
                  $data['fact_money'] = $value['已收金额'];
                  $data['is_valid'] =  1;
                  $data['is_fp'] = 1;
                  $data['is_success'] = 3;//已定金
                  break;
                case 50:
                  #邀约未进店
                  $data['yjd_action_day'] = $value['UPDATETIME'];
                  $data['is_valid'] =  1;
                  $data['is_fp'] = 1;
                  break;
                case 7:
                  #返驳回
                  $data['is_valid'] =  5;
                  $data['is_fp'] = 1;
                  break;
                default:
                  # code...
                  break;
              }
          }

          if($value['门市接待ID']){
              $data['reception_id'] = $value['门市接待ID'];//门市接待人，待处理。。。
              $data['reception_name'] = $value['门市接待'];//门市接待人，待处理。。。
          }else{
            $data['reception_id']  =0;
            $data['reception_name'] = '';
          }
          if($classId == 3){
            #已进店
            switch ($statusId) {
                case 39:
                  //非进客进店，即无效进店
                  $data['is_intostore'] = 3;
                break;
                case 33:
                  #进店保留--定金
                case 35:
                  #回门保留--定金
                  $data['is_success'] = 3;
                break;
                case 10:
                  #进店未成交
                case 36:
                  #回门流失--进店未成交
                break; 
            }
          }
          if($classId == 4){
              //已成交
              $data['is_success'] = 1;
              $data['totalpay_money'] = $value['套系金额'];
              $data['fact_money'] = $value['已收金额'];
          }
          $data['unvalid_id'] = $value['无效原因编号'];
          $data['unvalid_reason'] = $value['无效原因'];
          if($classId == 5){
              if($statusId == 4){
                  //驳回
                  $data['is_valid'] = 4;
                  $data['unvalid_id'] = $value['无效原因编号'];
                  $data['unvalid_reason'] = $value['无效原因'];
              }

              if($statusId == 8){
                  #邀约流失，对应不再追踪
                  $data['is_loss'] = 1;
                  $data['is_valid'] = 3;
                  $data['unvalid_id'] = $value['无效原因编号'];
                  $data['unvalid_reason'] = $value['无效原因'];
              }
              if($statusId == 5){
                  #无效
                  $data['is_valid'] = 3;
                  $data['unvalid_id'] = $value['无效原因编号'];
                  $data['unvalid_reason'] = $value['无效原因'];
              }

          }

          
          
          $data['dj_staff'] = $value['提报人姓名'];//录入人，待处理。。。
          $data['dj_staff_id'] = $value['提报人ID'];//录入人，待处理。。。
          $allData[] = $data;
          
        }
        #######################客资主表一次结束
        $sqlCustomer = $this->consx->name('c_customerinfo')->fetchSql()->insertAll($allData);
        $fileName = $this->sqlPath.DS.'c_customerinfo.sql';
        file_put_contents($fileName, $sqlCustomer.";\r\n",FILE_APPEND);
        $percent = sprintf('%.2f',($i/$count) * 100);
        $this->log("客资表数据完成：".$i.'/'.$count."【{$percent}%】");
        unset($data,$allData,$sqlQuery,$sqlCustomer);
      }//总while
          unset($sql,$address);
          die;
      ///////////////////////////////////////////追踪记录
          //



    }
    /**
     * 操作日志处理
     * 
     */
    function JZ_actionlog(){
      /*
      手动处理方法，
      1.先把hm_crm_client_log_283 表复制到另一张表取名为：action_log
        把hm_crm_client_info_283 表复制到另一张表取名为：hm_crm_client_info_sx
    
      2.依次执行以下方法：
ALTER TABLE action_log ADD COLUMN customer_id int(11) DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN old_data text DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN `data` text DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN staff_name varchar(100) DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN module varchar(100) DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN controller varchar(100) DEFAULT NULL;
ALTER TABLE action_log ADD COLUMN `action` varchar(100) DEFAULT NULL;

update action_log set operaid = 0 where operaid = '' 

ALTER TABLE `action_log`
  CHANGE COLUMN `OPERAID` `staff_id` int(32) DEFAULT NULL;
  
  ALTER TABLE `action_log`
  CHANGE COLUMN `OPERATIME` `create_time` datetime;

ALTER TABLE `action_log`
  CHANGE COLUMN `OPERAIP` `ip` varchar(64) DEFAULT NULL;

update action_log set `data` = '修改-' where logtype = 'xg';
update action_log set `data` = '资询-' where logtype = 'zx';
update action_log set `data` = '推广-' where logtype = 'tg';
update action_log set `data` = '入店-' where logtype = 'rd';
update action_log set `data` = '成交-' where logtype = 'cj';
update action_log set `data` = '转移-' where logtype = 'zy';
update action_log set `data` = '重复-' where logtype = 'cf';
update action_log set `data` = '回门-' where logtype = 'hm';
update action_log set `data` = '删除-' where logtype = 'sc';

update action_log set `data` =CONCAT(ifnull(data,""),MEMO)


ALTER TABLE `action_log` DROP COLUMN `MEMO`;
ALTER TABLE `action_log` DROP COLUMN `LOGTYPE`;
  ALTER TABLE `action_log` DROP COLUMN `isdel`;
delete from action_log where companyid != 283;

  ALTER TABLE `action_log` DROP COLUMN `COMPANYID`;

update action_log a left join hm_crm_client_info_sx b on b.KZID=a.KZID set a.customer_id = b.id

update action_log set module = 'crm',controller='Customer',action='customerinfo' 

ALTER TABLE `action_log` DROP COLUMN `KZID`;

  3.把主键：LOGID更名为id
  4.把数据库导出到CRM_LOGS库内。


       */

      $this->log("ACTION LOG 数据采用手动操作，详细crm/lib/Runtransdata.php 内的： JZ_ACTIONLOG方法：");
      return true;

        $this->thisStep++;
        $actionType = [
            'xg'=>'修改',
            'yy'=>'预约',
            'zx'=>'资询',
            'tg'=>'推广',
            'rd'=>'入店',
            'cj'=>'成交',
            'zy'=>'转移',
            'cf'=>'重复',
            'hm'=>'回门',
            'sc'=>'删除'
        ];
        //处理追踪记录数据
        $sql = <<<EOF
select   cl.KZID,cl.memo,cl.operaid,cl.operatime,cl.operaip,cl.logtype,c.id from hm_crm_client_log_284 cl
left join hm_crm_client_info_284 c on c.KZID = cl.KZID 
WHERE 
cl.COMPANYID = 283 
ORDER BY OPERATIME desc
EOF;


          $count = $this->con->name('hm_crm_client_log_284')->where('COMPANYID',283)->count();
          $page = 0;
          $allPage = ceil($count/$this->perSqlDataNum);
          if($this->test){
              $allPage = 5;//正式后删除，先做5次测试。
          }
          $this->log("开始处理操作日志数据，待处理总数量：".$count);
          $i = 0;
          while($page < $allPage){
              $page++;
              $start = ($page - 1) * $this->perSqlDataNum;
              $sqlQuery = $sql . ' limit '.$start.','.$this->perSqlDataNum;
              $resTrans = $this->con->query($sqlQuery);
              $tmpValue = [];
              foreach ($resTrans as $key => $value) {
                if(!empty($tmpValue) && count($tmpValue) != count($data)){
                    //一次处理中，如果数据不匹配，则需要进行报错处理。
                    
                    $this->log('有不匹配的数据列，前一数据列：');
                    print_r($tmpValue);
                    $this->log('当前数据列：');
                    print_r($data);
                    die;
                }else{
                    $tmpValue = $data;
                }
                  $i++;
                  $data = [];
                  #对每条数据进行处理逻辑，代码具体转的数据方法里，这里替换掉处理数据的代码。
                  ##TO DO 。。。。。。
                  $data['module'] = 'crm';
                  $data['controller'] = 'Customer';
                  $data['action'] = 'customerInfo';
                  $data['data'] = $actionType[$value['logtype']].$value['memo'];
                  $data['customer_id'] = $value['id'];
                  $data['staff_id'] = $value['operaid'];
                  $data['ip'] = $value['operaip'];
                  $data['create_time'] = $value['operatime'];
                  $allData[] = $data;
                  unset($data);
              }
              $sqlCustomer = $this->consx->name('action_log')->fetchSql()->insertAll($allData);
              $fileName = $this->sqlPath.DS.'action_log.sql';
              file_put_contents($fileName, $sqlCustomer.";\r\n",FILE_APPEND);
              $percent = sprintf('%.2f',($i/$count) * 100);
              $this->log("操作日志数据已完成：".$i.'/'.$count."【{$percent}%】");
              unset($dataTrans,$allData,$sqlQuery,$sqlCustomer,$tmpValue);
                  ///////////////客资追踪记录信息
              ///
          }



    }
    /**
     * 示例方法，按此方法复制具体转档表的操作
     * 
     * @return [type] [description]
     */
    function templete(){
        $this->thisStep++;
        //处理追踪记录数据
        $sql = <<<EOF
        {SQL 语句写这里，作为本次处理数据获取的执行语句，语句内不要含limit 在框架里，处理批的问题。}
EOF;


          $count = $this->con->name('{取所有表数据的表名，计算总处理数量用的}')->count();
          $page = 0;
          $allPage = ceil($count/$this->perSqlDataNum);
          if($this->test){
              $allPage = 5;//正式后删除，先做5次测试。
          }
          $this->log("开始处理{XXX}数据，待处理总数量：".$count);
          $i = 0;
          while($page < $allPage){
              $page++;
              $start = ($page - 1) * $this->perSqlDataNum;
              $sqlQuery = $sql . ' limit '.$start.','.$this->perSqlDataNum;
              $resTrans = $this->con->query($sqlQuery);
              $tmpValue = [];
              foreach ($resTrans as $key => $value) {
                if(!empty($tmpValue) && count($tmpValue) != count($data)){
                    //一次处理中，如果数据不匹配，则需要进行报错处理。
                    
                    $this->log('有不匹配的数据列，前一数据列：');
                    print_r($tmpValue);
                    $this->log('当前数据列：');
                    print_r($data);
                    die;
                }else{
                    $tmpValue = $data;
                }
                  $i++;
                  $data = [];
                  #对每条数据进行处理逻辑，代码具体转的数据方法里，这里替换掉处理数据的代码。
                  ##TO DO 。。。。。。
                  #
                  $allData[] = $data;
                  unset($data);

              }
              $sqlCustomer = $this->consx->name('{写入素玄对应的表名}')->fetchSql()->insertAll($allData);
              $fileName = $this->sqlPath.DS.'{写入素玄对应的表名}.sql';
              file_put_contents($fileName, $sqlCustomer.";\r\n",FILE_APPEND);
              $percent = sprintf('%.2f',($i/$count) * 100);
              $this->log("{XXX}数据已完成：".$i.'/'.$count."【{$percent}%】");
              unset($dataTrans,$allData,$sqlQuery,$sqlCustomer,$tmpValue);
                  ///////////////客资追踪记录信息
              ///
          }


    }
    function is_windows(){
      return strtoupper(substr(PHP_OS,0,3))==='WIN'?true:false;
    }
    function log($t){//控制台输出
      //if($this->log){
        $t=date('Y-m-d H:i:s ')."  内存占用：".$this->getSystemMemory().'('.$this->thisStep.'/'.$this->step.')'.$t;
        $t .= "\r\n";
        if($this->is_windows()){
          fwrite(STDOUT, iconv('utf-8','gbk//IGNORE',$t));
        }else{
          fwrite(STDOUT, $t);
        }
      //}
    }
    function getSystemMemory(){
        $memory = memory_get_usage();
        $res = '';
        if($memory > 1073741824){
            $tmp = floor($memory/1073741824);
            $res .= $tmp.'G';
            $memory = $memory - ($tmp * 1073741824);
        }
        if($memory > 1048576){
            $tmp = floor($memory/1048576);
            $res .= $tmp.'M';
            $memory = $memory - ($tmp * 1048576);
        }
        if($memory > 1024){
            $res .= floor($memory/1024).'K';
        }
        return $res;
    }
    function initChannelExcel(){
        $sql = 'select c.SOURCEID ,cs.typeid,cs.SRCTYPE,cs.SRCNAME from hm_crm_client_info_283 c left join hm_crm_source cs on cs.SRCID = c.SOURCEID group by c.SOURCEID ';
        $res = $this->con->query($sql);
        //$content = "原渠道ID,原渠道类型ID,原渠道类型,源渠道名称,新渠道类型,新来源渠道,新渠道名称\r\n";
        $data[] = ["原渠道ID","原渠道类型ID","原渠道类型","源渠道名称","新渠道类型","新来源渠道","新渠道名称"];
        foreach ($res as $value) {
            //$content .= implode(",", $value)."\r\n";
            // $sqlNew = 'select channel_class_name,channel_info_name,channel_name from s_channel where channel_name ='."'".addslashes($value['SRCNAME'])."'";
            $new = $this->consx->name('s_channel')->field('channel_class_name,channel_info_name,channel_name')->where('channel_name',$value['SRCNAME'])->find();
            $value[] = $new?$new['channel_class_name']:'';
            $value[] = $new?$new['channel_info_name']:'';
            $value[] = $new?$new['channel_name']:'';
            $data[] = $value;
        }
        $fileName = $this->sqlPath.DS.'草莓卷渠道对应关系表.xlsx';
        $this->wirteExcel($fileName,$data);
    }
    function initShopExcel(){
        $sql = 'select shopid,shopname from hm_crm_client_info_283 group by shopid';
        $res = $this->con->query($sql);
        $data[] = ["草莓卷店ID","草莓卷店名","素玄shop_code","素玄店名"];
        foreach ($res as $value) {
            $new = $this->conmc->name('s_shopset')->field('shop_code,shop_name')->where('shop_name','like','%'.$value['shopname'].'%')->find();
            $value[] = $new?$new['shop_code']:'';
            $value[] = $new?$new['shop_name']:'';
            $data[] = $value;
        }
        $fileName = $this->sqlPath.DS.'草莓卷门店对应关系表.xlsx';
        $this->wirteExcel($fileName,$data);
    }
    function initStaffExcel(){
        $sql1 = 'select COLLECTORID,COLLECTORNAME,S.PHONE,d.DEPTNAME from hm_crm_client_info_283 c left join hm_pub_staff s on s.id = c.COLLECTORID left join hm_crm_dept d on d.DEPTID = s.DEPTID group by COLLECTORID';
        $sql2 = 'select APPOINTID,APPOINTNAME,S.PHONE,d.DEPTNAME from hm_crm_client_info_283 c left join hm_pub_staff s on s.id = c.APPOINTID left join hm_crm_dept d on d.DEPTID = s.DEPTID group by APPOINTID';
        $sql3 = 'select RECEPTORID,RECEPTORNAME,S.PHONE,d.DEPTNAME from hm_crm_client_info_283 c left join hm_pub_staff s on s.id = c.RECEPTORID left join hm_crm_dept d on d.DEPTID = s.DEPTID group by RECEPTORID';
        $allStaffs = [];
        $res1 = $this->con->query($sql1);
        foreach ($res1 as $value) {
            if(!array_key_exists($value['COLLECTORID'], $allStaffs)){
                $allStaffs[$value['COLLECTORID']] = [
                                                    'name'=>$value['COLLECTORNAME'],
                                                    'phone'=>$value['PHONE'],
                                                    'deptname'=>$value['DEPTNAME']
                                                    ];
            }
        }
        $res2 = $this->con->query($sql2);
        foreach ($res2 as $value) {
            if(!array_key_exists($value['APPOINTID'], $allStaffs)){
                $allStaffs[$value['APPOINTID']] = [
                                                    'name'=>$value['APPOINTNAME'],
                                                    'phone'=>$value['PHONE'],
                                                    'deptname'=>$value['DEPTNAME']
                                                    ];
            }
        }
        $res3 = $this->con->query($sql3);
        foreach ($res3 as $value) {
            if(!array_key_exists($value['RECEPTORID'], $allStaffs)){
                $allStaffs[$value['RECEPTORID']] = [
                                                    'name'=>$value['RECEPTORNAME'],
                                                    'phone'=>$value['PHONE'],
                                                    'deptname'=>$value['DEPTNAME']
                                                    ];
            }
        }
        $data[] = ['草莓卷员工ID','草莓卷员工手机号','草莓卷员工部门','草莓卷员工名','素玄员工ID','素玄员工名'];
        foreach ($allStaffs as $key => $value) {
            $new = $this->conmc->name('s_staffinfo')->field('id,staffname')->where('turestaffname',$value['name'])->whereOr('staffname',$value['name'])->find();
            $datavalue = [$key,$value['phone'],$value['deptname'],$value['name']];
            $datavalue[] = $new?$new['id']:'';
            $datavalue[] = $new?$new['staffname']:'';
            $data[] = $datavalue;
            unset($datavalue);
        }
        $fileName = $this->sqlPath.DS.'草莓卷员工对应关系表.xlsx';
        $this->wirteExcel($fileName,$data);
    }
    function getChannelList(){
      return [
          6801=>['srcid'=>'360','channel_id'=>42,'channel_name'=>'360'],
          8983=>['srcid'=>'400','channel_id'=>1,'channel_name'=>'400电话'],
          6804=>['srcid'=>'WED114','channel_id'=>36,'channel_name'=>'WED114'],
          6810=>['srcid'=>'到喜啦','channel_id'=>35,'channel_name'=>'到喜啦'],
          6803=>['srcid'=>'大众点评','channel_id'=>30,'channel_name'=>'大众点评'],
          6812=>['srcid'=>'天猫','channel_id'=>32,'channel_name'=>'天猫'],
          6832=>['srcid'=>'婚博网','channel_id'=>33,'channel_name'=>'婚博网'],
          6811=>['srcid'=>'婚礼纪','channel_id'=>34,'channel_name'=>'婚礼纪'],
          6809=>['srcid'=>'官网电话','channel_id'=>1,'channel_name'=>'400电话'],
          6806=>['srcid'=>'广点通1','channel_id'=>26,'channel_name'=>'广点通1'],
          6821=>['srcid'=>'广点通2','channel_id'=>27,'channel_name'=>'广点通2'],
          6825=>['srcid'=>'广点通3','channel_id'=>28,'channel_name'=>'广点通3'],
          8981=>['srcid'=>'广点通5','channel_id'=>44,'channel_name'=>'广点通5'],
          6797=>['srcid'=>'微信1','channel_id'=>10,'channel_name'=>'微信1'],
          6814=>['srcid'=>'微信2','channel_id'=>11,'channel_name'=>'微信2'],
          6824=>['srcid'=>'微信3','channel_id'=>12,'channel_name'=>'微信3'],
          6827=>['srcid'=>'微信4','channel_id'=>13,'channel_name'=>'微信4'],
          6829=>['srcid'=>'微信5','channel_id'=>14,'channel_name'=>'微信5'],
          6823=>['srcid'=>'微信6','channel_id'=>15,'channel_name'=>'微信6'],
          6818=>['srcid'=>'微信7','channel_id'=>16,'channel_name'=>'微信7'],
          8979=>['srcid'=>'微信8','channel_id'=>17,'channel_name'=>'微信8'],
          8980=>['srcid'=>'微信9','channel_id'=>18,'channel_name'=>'微信9'],
          6796=>['srcid'=>'微博1','channel_id'=>19,'channel_name'=>'微博1'],
          6815=>['srcid'=>'微博2','channel_id'=>20,'channel_name'=>'微博2'],
          6819=>['srcid'=>'微博3','channel_id'=>21,'channel_name'=>'微博3'],
          10059=>['srcid'=>'微博4','channel_id'=>22,'channel_name'=>'微博4'],
          12431=>['srcid'=>'微博5','channel_id'=>23,'channel_name'=>'微博5'],
          8982=>['srcid'=>'微博挖掘','channel_id'=>24,'channel_name'=>'微博挖掘'],
          9041=>['srcid'=>'微博群发','channel_id'=>25,'channel_name'=>'微博群发'],
          13276=>['srcid'=>'快手','channel_id'=>38,'channel_name'=>'快手'],
          6800=>['srcid'=>'手机','channel_id'=>43,'channel_name'=>'手机'],
          13292=>['srcid'=>'抖音','channel_id'=>39,'channel_name'=>'抖音1'],
          13394=>['srcid'=>'抖音2','channel_id'=>40,'channel_name'=>'抖音2'],
          6802=>['srcid'=>'搜狗','channel_id'=>4,'channel_name'=>'sogou'],
          6798=>['srcid'=>'支付宝口碑','channel_id'=>37,'channel_name'=>'支付宝口碑'],
          10978=>['srcid'=>'点评（维护）','channel_id'=>31,'channel_name'=>'大众点评（维护）'],
          6799=>['srcid'=>'百度','channel_id'=>9,'channel_name'=>'百度'],
          6817=>['srcid'=>'百度信息流','channel_id'=>2,'channel_name'=>'百度信息流'],
          11736=>['srcid'=>'百度起跑线','channel_id'=>3,'channel_name'=>'百度起跑线'],
          11737=>['srcid'=>'知识营销','channel_id'=>5,'channel_name'=>'知识营销'],
          6831=>['srcid'=>'神马搜索','channel_id'=>6,'channel_name'=>'神马搜索'],
          6826=>['srcid'=>'腾讯社交4','channel_id'=>29,'channel_name'=>'广点通4'],
          13395=>['srcid'=>'苏州','channel_id'=>41,'channel_name'=>'苏州'],
          9040=>['srcid'=>'贴吧','channel_id'=>8,'channel_name'=>'百度贴吧'],
          10278=>['srcid'=>'六层','channel_id'=>47,'channel_name'=>'六层'],
          6833=>['srcid'=>'网销转介绍','channel_id'=>46,'channel_name'=>'网销转介绍'],
          10169=>['srcid'=>'推广老客户介绍','channel_id'=>45,'channel_name'=>'推广老客户转介绍'],
          ];
    }
    public function wirteExcel($fileName,$data,$title='customer',$width=[]){
        $PHPExcel = new PHPExcel();
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }

        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle($title); //给当前活动sheet设置名称
        $clumnNum = 0;
        foreach ($data as $k => $v) {
            $index = $k + 1;
            if(0 == $k){
                $clumnNum = count($v);
            }else{
                if ($clumnNum != count($v)) {
                    die("第{$k}行数据项不一至({$clumnNum})，无法写入excel,详细数据：".implode('<==>', $v) . EOL);
                }
            }
            $kk = 0;
            foreach ($v as $vv) {
                if(0 == $k){
                    //首行，定宽度
                    $range[$kk] = controller("crm/Customer","lib")->stringFromColumnIndex($kk);
                    $PHPExcel->setActiveSheetIndex(0);
                    $cwidth = $width[$kk]?$width[$kk]:(strlen($vv)+1);
                    $PHPExcel->getActiveSheet()->getColumnDimension($range[$kk])->setWidth($cwidth);
                    $PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    $PHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
                }
                $PHPSheet->setCellValue("{$range[$kk]}{$index}",$vv);
                $kk++;
            }
        }
        $PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');
        $PHPWriter->save($fileName);
    }

}