<?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_tg_data extends Report {

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

    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($b_id){
            $cost_sql_str .= ' and b_id = "'.$b_id.'"';
            $customer_sql_str .= ' and b_id = "'.$b_id.'"';
        }

        if($staff_id){

            $staff_name = $data['staff_name'];

            $costSql = 'SELECT
            cost_date, channel_name, city_name,b_name,SUM(cost)AS cost_sum,SUM(show_num)AS show_sum,SUM(click_num)AS click_sum
            FROM s_channel_cost
            where staff_id = "'.$staff_id.'"
            '.$cost_sql_str.'
            and cost_date BETWEEN "'.$start.'" and "'.$end.'"
            GROUP BY cost_date,channel_name, city_name,b_name
            ORDER BY cost_date,channel_name, city_name,b_name';
            $cons = db()->query($costSql);

            $customerSql = 'SELECT DATE(dj_day) as day, customer_channel, city,b_name,
            COUNT(1)AS all_kz,
            SUM(is_valid = 1)AS valid_num,
            SUM(is_valid = 3)AS invalid_num,
            SUM(sign_name = "A") AS a_num,
            SUM(sign_name = "B") AS b_num,
            SUM(sign_name = "C") AS c_num,
            SUM(is_success=1 and order_day is not null)AS order_sum,
            SUM(c_star_day is null)AS no_seles_sum,
            SUM(fact_money) as fact_money
            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,b_name
            ORDER BY DATE(dj_day),customer_channel, city,b_name';
            $customer = db()->query($customerSql);

            $list = [];
            foreach($cons as $k => $v){
                $arr = [];
                $is_exist = 0;
                foreach($customer as $key => $val){

                    if($k['cost_date'] == $key['day'] && $k['channel_name'] == $key['customer_channel'] && $k['city_name']== $key['city'] && $k['b_name']==$key['b_name']){
                        $arr = array_merge($v,$val);
                        $arr['chengben'] = sprintf('%.2f',$arr['cost_sum'] / $arr['all_kz']);//成本(花费/毛客资)
                        $arr['order_chengben'] = sprintf('%.2f',$arr['cost_sum'] / $arr['order_sum']);//订单成本（花费/成交量）
                        $arr['touru_chanchu'] = sprintf('%.2f',$arr['fact_money'] / $arr['cost_sum']);//投入产出比(实收：花费)
                        $arr['kz_valid_lv'] = sprintf('%.2f',$arr['valid_num'] / $arr['all_kz']).'%';//有效率（有效客资/毛客资）
                        $arr['order_lv'] = sprintf('%.2f',$arr['order_sum'] / $arr['valid_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(cost)AS cost_sum,SUM(show_num)AS show_sum,SUM(click_num)AS click_sum
            FROM s_channel_cost
            where shop_code = "'.$shop_code.'"
            AND cost_date BETWEEN "'.$start.'" and "'.$end.'"';
            $cons = db()->query($costSql);

            $customerSql = 'SELECT
            COUNT(1)AS all_kz,
            SUM(is_valid = 1)AS valid_num,
            SUM(is_valid = 3)AS invalid_num,
            SUM(sign_name = "A") AS a_num,
            SUM(sign_name = "B") AS b_num,
            SUM(sign_name = "C") AS c_num,
            SUM(is_success=1 and order_day is not null)AS order_sum,
            SUM(c_star_day is null)AS no_seles_num,
            SUM(fact_money) as fact_money
            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 <> "") OR (b_name is not null AND b_name <> "") )
            ';
            $customer = db()->query($customerSql);

            $arr = array_merge($cons[0],$customer[0]);
            $arr['cost_date'] = '合计';
            $arr['chengben'] = sprintf('%.2f',$arr['cost_sum'] / $arr['all_kz']);//成本(花费/毛客资)
            $arr['order_chengben'] = sprintf('%.2f',$arr['cost_sum'] / $arr['order_sum']);//订单成本（花费/成交量）
            $arr['touru_chanchu'] = sprintf('%.2f',$arr['fact_money'] / $arr['cost_sum']);//投入产出比(实收：花费)
            $arr['kz_valid_lv'] = sprintf('%.2f',$arr['valid_num'] / $arr['all_kz']).'%';//有效率（有效客资/毛客资）
            $arr['order_lv'] = sprintf('%.2f',$arr['order_sum'] / $arr['valid_num']).'%';//转单率(订单/有效客资)
            $list = [$arr];
        }
        return json(array('code' =>0,'count'=>count($list),'data' =>$list));
    }
    public function show(){
        $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= controller('crm/Customer','lib')->getBrands(['brandclass_id'=>$info['brandclass_id']]);
        // 事业部下所有可操作店
        $shopParam['staff_id'] = session('staff_id');
        $shopParam['brandclass_id'] = $info['brandclass_id'];
        $myShops = getShops($shopParam);
        $this->assign("myShops", $myShops);
        // var_dump($myShops);die;
        // 来源渠道
        $channel = getChannelJson($info['shop_code']);
        $this->assign("channel", json_encode($channel));
        // crm省 列表
        $data['type']=1;
        $data['shop_code']= $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        $province=controller("crm/Customer","lib")->address($data);
        $this->assign("province",$province);
        // 只有一个省 查这个省下的城市
        if (count($province)==1) {
            $data['type'] = 2;
            $data['pid']  = $province[0]['id'];
            $staCity = controller("crm/Customer", "lib")->address($data);
            $this->assign("staCity",$staCity);
        }
        // 品牌
        $brandArr = db('s_brandset')->select();
        $this->assign('brandArr',$brandArr);
        // 消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$info['brandclass_id']]);
        $this->assign("consumptionType", $consumptionType);

        $mc = new \app\common\lib\mcInterface();
        $data['shop_code']= $info['shop_code'] ? $info['shop_code'] : session('shop_code');
        // 部门
        $departmentList = $mc->getDepartmentByShopcode($data['shop_code']);
        foreach ($departmentList as $key => $value) {
            if ($value['department_name']==session('department_name')) {
                $departmentList[$key]['selected']=1;
                $departmentId = $value['id'];
            }
        }
        $this->assign("departmentList",$departmentList);
        $staffParam['department_id'] = $departmentId;
        $staffParam['get_main'] = 1;
        // 登记人
        $djStaffList = $mc->getDepartmentStaffs($staffParam);
        $this->assign("djStaffList",$djStaffList);
        // 追踪人
        $salesStaffList = $mc->getDepartmentStaffs($staffParam);
        $this->assign("salesStaffList",$salesStaffList);

        $code = $this->getCode();
        $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');
        $this->assign('title', $shop[0]['brandclass'].'-'.$title);
        $this->assign('did', $info['id']);
        $this->assign('start', date('Y-m-01'));
        $this->assign('end', date('Y-m-d'));
        return $this->fetch($display);
    }
    /*
    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);
    }
    */
}