<?php
namespace app\crm\lib;
use think\Controller;
use think\Db;
class Runtransdata extends Controller {
    var $step = 3;
    var $thisStep = 0;
    var $perSqlDataNum = 200;
    var $sqlPath = '/data/git/crm-stand/runtime/';
    var $con = null;
    var $consx = null;
    var $test = false;
    // 客资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(){
        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_jz',
          'hostport'=>'3306',
          'password'=>'123456',
        ];
        $dbSxLog = [
          'type'            => 'mysql',
          'hostname'=>'10.120.131.251',
          'username'=>'root',
          'database'=>'sx_crm_logs',
          'hostport'=>'3306',
          'password'=>'mysql@sx1120',
        ];

        $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->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 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('crm_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->thisStep.'/'.$this->step.' '.$t;
        $t .= "内存占用：".$this->getSystemMemory()."\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 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'=>'推广老客户转介绍'],
          ];
    }

}