<?php
/**
 * 客服每日数据
 *
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: wangzhenya 2019-03-22 $
 */
namespace app\crm\lib\report;
use app\crm\lib\Report;
use think\Controller;
use think\image\Exception;

class Qqbaby_kf_data extends Report {

    private $level=1;
    private $type=3;
    private $code='code1587634214';

    public function getLevel(){
        return $this->level;

    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data()
    {

        set_time_limit(0);
        $data = input();
        $det = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$data['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name,f.report_name')->find();
        $shop_code = $det['shop_code'] ? $det['shop_code'] : session('shop_code');
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }

        $start = $data['start'] ? date('Y-m-d', strtotime($data['start'])) : date('Y-m-d');
        $end =  $data['end'] ? date('Y-m-d 23:59:59', strtotime($data['end'])) : date('Y-m-d 23:59:59');
        $channel_id = $data['channel_id'];
        $city_id = $data['city_id'];
        $b_id = $data['b_id'];
        $staff_id = $data['staff_id'];

        $cost_sql_str = ' ';
        $customer_sql_str = ' ';

        //渠道
        if($channel_id){
            $cost_sql_str .= ' and channel_id = "'.$channel_id.'"';
            $customer_sql_str .= ' and customer_channel_id = "'.$channel_id.'"';
        }

        //城市
        if($city_id) {
            $cost_sql_str .= ' and city_id = "'.$city_id.'"';
            $customer_sql_str .= ' and city_id = "'.$city_id.'"';
        }


        if($staff_id){

            $staff_name = $data['staff_name'];

            $costSql = 'SELECT
            date, channel_name, city_name,kf_staff_name,tg_staff_id,
            SUM(subscribe_num)AS subscribe_num,SUM(unsubscribe_num)AS unsubscribe_num,SUM(chat_num)AS chat_num
            FROM s_fans
            where tg_staff_id = "'.$staff_id.'"
            '.$cost_sql_str.'
            and date BETWEEN "'.$start.'" and "'.$end.'"
            GROUP BY date,channel_name, city_name
            ORDER BY date,channel_name, city_name';
            $cons = db()->query($costSql);

            $customerSql = 'SELECT DATE(dj_day) as day, customer_channel, city,b_name,
            COUNT(1)AS all_kz
            FROM c_customerinfo
            WHERE dj_staff_id = "'.$staff_id.'"
            '.$customer_sql_str.'
            AND DATE(dj_day) BETWEEN "'.$start.'" and "'.$end.'"
            AND ( (customer_channel is not null AND customer_channel <> "") OR (city is not null AND city <> "") OR (b_name is not null AND b_name <> "") )
            GROUP BY DATE(dj_day),customer_channel, city
            ORDER BY DATE(dj_day),customer_channel, city';
            $customer = db()->query($customerSql);

            $list = [];
            foreach($cons as $k => $v){
                $arr = [];
                $is_exist = 0;
                $v['chat_lv'] = sprintf('%.2f',$v['chat_num'] / $v['subscribe_num']);//有效对话率(有效对话/关注量)

                foreach($customer as $key => $val){

                    if($k['cost_date'] == $key['day'] && $k['channel_name'] == $key['customer_channel'] && $k['city_name']== $key['city']){
                        $arr = array_merge($v,$val);
                        $arr['kz_lv'] = sprintf('%.2f',$arr['all_kz'] / $arr['chat_num']);//获取率(客资量/有效对话)
                        unset($customer[$key]);
                        $is_exist = 1;
                        continue;
                    }
                }

                if($is_exist != 1){
                    $arr = $v;
                }

                $arr['staff_name'] = $staff_name;
                if(!$arr['all_kz'])      $arr['all_kz'] = 0;
                if(!$arr['valid_num'])   $arr['valid_num'] = 0;
                if(!$arr['invalid_num']) $arr['invalid_num'] = 0;
                if(!$arr['a_num'])       $arr['a_num'] = 0;
                if(!$arr['b_num'])       $arr['b_num'] = 0;
                if(!$arr['c_num'])       $arr['c_num'] = 0;
                if(!$arr['order_sum'])   $arr['order_sum'] = 0;
                if(!$arr['no_seles_sum'])   $arr['no_seles_sum'] = 0;
                if(!$arr['fact_money'])  $arr['fact_money'] = 0;
                if(!$arr['chengben'])    $arr['chengben'] = 0;//成本
                if(!$arr['order_chengben']) $arr['order_chengben'] = 0;//订单成本
                if(!$arr['touru_chanchu'])  $arr['touru_chanchu'] = 0;//投入产出比
                if(!$arr['kz_valid_lv']) $arr['kz_valid_lv'] = 0;//有效率
                if(!$arr['order_lv'])    $arr['order_lv'] = 0;//转单率

                $list[] = $arr;
            }

        }else{
            //总计
            $costSql = 'SELECT
            SUM(subscribe_num)AS subscribe_num,SUM(unsubscribe_num)AS unsubscribe_num,SUM(chat_num)AS chat_num
            FROM s_fans
            where shop_code = "'.$shop_code.'"
            AND date BETWEEN "'.$start.'" and "'.$end.'"';
            $cons = db()->query($costSql);

            $customerSql = 'SELECT
            COUNT(1)AS all_kz
            FROM c_customerinfo
            WHERE add_shop_code = "'.$shop_code.'"
            AND dj_day BETWEEN "'.$start.'" and "'.$end.'"
            AND ((customer_channel is not null AND customer_channel <> "") OR (city is not null AND city <> ""))
            ';
            $customer = db()->query($customerSql);

            $arr = array_merge($cons[0],$customer[0]);
            $arr['date'] = '合计';
            $arr['chat_lv'] = sprintf('%.2f',$arr['chat_num'] / $arr['subscribe_num']);//有效对话率(有效对话/关注量)
            $arr['kz_lv']   = sprintf('%.2f',$arr['all_kz'] / $arr['chat_num']);//获取率(客资量/有效对话)
            $list = [$arr];
        }



        return json(array('code' =>0,'count'=>count($list),'data' =>$list));

    }

    public function show(){
        $code = $this->getCode();
        $did = strtolower(input('param.did'));
        $info = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$did)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        $shop_code =$info['shop_code'] ?$info['shop_code'] : session('shop_code');

        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('s_report_form')->where('code', $code)->value('report_name');
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);

        $channel = db('s_channel')->where(['shop_code'=>$shop_code])->select();//渠道
        $staff = getShopStaffs(['shop_code'=>$shop_code,'work_type'=>21]);//话务
        $brand = db('s_brandset')->order('sort asc')->select();//品牌

        //城市
        $data['type']=2;
        $address=controller("Customer","lib")->address($data);

        $this->assign("address",$address);
        $this->assign('channel', $channel);
        $this->assign('staff', $staff);
        $this->assign('shop', $shop);
        $this->assign('brand',$brand);
        $this->assign('title', $shop[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}