<?php
/**
 * CRM 实现模块
 * CRM的基本功能实现，如客资录入，客资查询等。
 * 所有业务的真正实现全在lib目录下建controller并且访问modle等，供controller层调用
 *
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: songdemei<songdemei@suxuantech.cn> 2017-10-17 $
 */
namespace app\crm\lib;
use think\Cache;
use think\Controller;
use PHPExcel_IOFactory;
use Excel5;
use PHPExcel;
use app\common\lib\redisQueue;
use think\Exception;

class Client extends Controller{
    protected $errid = 0;
    protected $errmsg = '';
    /**
     * 客户管理列表数据 全部
     *
     * @param  create_time  首次获取时间
     * @param  content      搜索内容（id，妈妈姓名，手机号，微信号）
     * @param  tab          当前Tab
     *
     * @author lilong<lilong@suxuantech.cn>
     */
    public function clientListDataAll($param){
        $start= $param['start_time'];
        $end  = $param['end_time'];
        $more_like = $param['more_like'];

        $getWhere = $this->getWhere($param);

        if($param['tab'] == 1){

            //是否导出
            if($param['export'] == 1){
                $data = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($getWhere['sqlHaving'])
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')->order('m.id desc')
                    ->select();
            }else{
                $data = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($getWhere['sqlHaving'])
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')->order('m.id desc')
                    ->limit($param['rows'])->page($param['page'])
                    ->select();
                $listSql = db()->getLastSql();
                $count = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($getWhere['sqlHaving'])
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')->order('m.id desc')->count();
                $countSql = db()->getLastSql();
            }

        }else{

            //未订单
            if($param['tab'] == 2){
                $having = 'ddsl = 0 and sumshu = 0';
            }elseif($param['tab'] == 3){
                //潜在客户 有订单，都是免费订单
                $having = 'ddsl > 0  and sumshu = 0';
            }elseif($param['tab'] == 4){
                //一次订单 有订单，且有一个收费订单
                $having = 'ddsl = 1  and sumshu > 0';
            }elseif($param['tab'] == 5){
                //多次订单 有订单，且有多个个收费订单
                $having = 'ddsl > 1  and sumshu > 0';
            }

            //是否导出
            if($param['export'] == 1){

                $data = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($having)
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')
                    ->order('m.id desc')
                    ->select();
            }else{
                $data = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($having)
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')
                    ->order('m.id desc')
                    ->limit($param['rows'])->page($param['page'])
                    ->select();

                $listSql = db()->getLastSql();

                $count = db('crm_c_customer_main')->alias('m')
                    ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
                    ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
                    ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
                    ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
                    ->where($getWhere['where'])
                    ->where($getWhere['sql'])
                    ->HAVING($having)
                    ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS order_num,ifnull(sum(eo.total_money),0)AS sumshu,COUNT(intr.id) as intr_num')
                    ->group('m.id')
                    ->count();

                $countSql = db()->getLastSql();
            }


        }

        $detail['listsql'] = $listSql;
        $detail['countSql']= $countSql;
        $detail['page']    = $param['page'] ? $param['page'] : 1;
        $detail['records'] = ($count);
        $detail['total']   = ceil($detail['records']/$param['rows']) ? ceil($detail['records']/$param['rows']) : 0;
        // $detail['rows']    = $data;
        $detail['new_rows']= $data;
        return $detail;
    }

    public function getWhere($param){

        $where = [];
        $sql = '1=1 ';
        $sqlHaving = '1=1 ';

        $start= $param['start_time'];
        $end  = $param['end_time'];
        $more_like = $param['more_like'];

        if($param['all_time'] == 'create_time'){
            $where['m.create_time'] = ['between',[$start,$end]];
        }elseif($param['all_time'] == 'babybirthday'){
            $where['m.babybirthday'] = ['between',[$start,$end]];
        }

        if($more_like){
            $sql .= ' and (m.id like "%'.$more_like.'%" or m.wname like "%'.$more_like.'%" or m.wphone like "%'.$more_like.'%" or m.wwechat like "%'.$more_like.'%")';
        }

        if($param['babysex'])      $where['m.babysex'] = $param['babysex'];
        if($param['province_id'])  $where['c_info.province_id'] = $param['province_id'];
        if($param['city_id'])      $where['c_info.city_id'] = $param['city_id'];
        if($param['county_id'])    $where['c_info.county_id'] = $param['county_id'];

        if($param['min_order'])    $sqlHaving .= ' and ddsl >= '.$param['min_order'];
        if($param['max_order'])    $sqlHaving .= ' and ddsl <= '.$param['max_order'];
        if($param['min_money'])    $sqlHaving .= ' and sumshu >= '.$param['min_money'];
        if($param['max_money'])    $sqlHaving .= ' and sumshu <= '.$param['max_money'];
        if($param['min_introduce'])   $sqlHaving .= ' and intr_num >= '.$param['min_introduce'];
        if($param['max_introduce'])   $sqlHaving .= ' and intr_num <= '.$param['max_introduce'];

        return ['where'=>$where,'sql'=>$sql,'sqlHaving'=>$sqlHaving];

    }

    public function clientMainCount($param){
        $start= $param['start_time'];
        $end  = $param['end_time'];
        $more_like = $param['more_like'];

        $getWhere = $this->getWhere($param);

        $all_num = db('crm_c_customer_main')->alias('m')
            ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
            ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
            ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
            ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
            ->where($getWhere['where'])
            ->where($getWhere['sql'])
            ->having($getWhere['sqlHaving'])
            ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS sumshu,COUNT(intr.id) as intr_num')
            ->group('m.id')->order('m.id desc')->count();

        //未订单
        $wdd_num = db('crm_c_customer_main')->alias('m')
            ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
            ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
            ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
            ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
            ->where($getWhere['where'])
            ->where($getWhere['sql'])
            ->HAVING('ddsl = 0 and sumshu = 0')
            ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS sumshu,COUNT(intr.id) as intr_num')
            ->group('m.id')
            ->count();

        //潜在客户 有订单，都是免费订单
        $qz_num = db('crm_c_customer_main')->alias('m')
            ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
            ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
            ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
            ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
            ->where($getWhere['where'])
            ->where($getWhere['sql'])
            ->HAVING('ddsl > 0  and sumshu = 0')
            ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS sumshu,COUNT(intr.id) as intr_num')
            ->group('m.id')
            ->count();
        //一次订单 有订单，且有一个收费订单
        $yc_num = db('crm_c_customer_main')->alias('m')
            ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
            ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
            ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
            ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
            ->where($getWhere['where'])
            ->where($getWhere['sql'])
            ->HAVING('ddsl = 1  and sumshu > 0')
            ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS sumshu,COUNT(intr.id) as intr_num')
            ->group('m.id')
            ->count();

        //多次订单 有订单，且有多个个收费订单
        $dc_num = db('crm_c_customer_main')->alias('m')
            ->join('crm_c_client_introduct intr','m.id=intr.mid','left')
            ->join('c_customerinfo ec','m.wphone=ec.wphone','left')
            ->join('c_customerorder eo','ec.customerid=eo.customerid','left')
            ->join('crm_c_customerinfo c_info','m.wphone = c_info.customer_tel','left')
            ->where($getWhere['where'])
            ->where($getWhere['sql'])
            ->HAVING('ddsl >1  and sumshu > 0')
            ->field('m.*,ifnull(count(orderid), 0) AS ddsl,ifnull(sum(CASE WHEN eo.total_money > 0 THEN 1 WHEN eo.total_money < 0 THEN 1 ELSE 0 END),0)AS sumshu,COUNT(intr.id) as intr_num')
            ->group('m.id')
            ->count();

        return ['code'=>200,'data'=>['all_num'=>$all_num,'wdd_num'=>$wdd_num,'qz_num'=>$qz_num,'yc_num'=>$yc_num,'dc_num'=>$dc_num]];

    }

    //导出
    function export($title,$data,$file){
        set_time_limit(0);
        ini_set("memory_limit","512M");
        $PHPExcel = new PHPExcel();
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }

        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('customer'); //给当前活动sheet设置名称

        $index = 1;
        $width = 15;
        for($i=0;$i<count($title);$i++){

            $range[$i] = controller("crm/Customer","lib")->stringFromColumnIndex($i);
            $PHPExcel->setActiveSheetIndex(0);
            $PHPExcel->getActiveSheet()->getColumnDimension($range[$i])->setWidth($width);
            $PHPSheet->setCellValue($range[$i].$index, $title[$i]);
            $PHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $PHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        }
        // var_dump($data);
        // exit;
        if(count($data)>0){
            foreach($data as $k=>$v){
                $index++;
                foreach(array_values($v) as $kk=>$vv){
                    $PHPSheet->setCellValue("{$range[$kk]}{$index}",$vv);
                }
            }
        }
        $PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007');
        //header("Content-type:text/html;charset=utf-8");
        header('Content-type: application/vnd.ms-excel');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename='.$file);//告诉浏览器输出浏览器名称
        header("Content-Description: File Transfer");
        header('Cache-Control: max-age=0');//禁止缓存
        header("Content-Transfer-Encoding: binary"); //告诉浏览器，这是二进制文件
        $PHPWriter->save("php://output");
        exit;
    }


    
}