<?php
/**
 * 大屏控制器
 *
 * ============================================================================
 * 版权所有 2019北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 */
namespace app\crm\controller;
use think\Controller;
// use app\index\controller\Base;
use think\Queue;
use think\Request;

class Sunbeibei extends Controller{

    // 大屏页面  pc
    public function index(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }

        // if ($this->isMobile()) {

        //     return $this->fetch('index_wap');
        // }else{

            return $this->fetch();
        // }
    }
    // 大屏页面  wap
    public function index_wap(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        return $this->fetch('index_wap');
    }
    /* 业绩  收入  
    *1、业绩：4个店面+爱婴部实收金额。
    *2、收入：选片订单统计中，4个店面选片订单中订单已付列项金额（不含优惠券）+爱婴部三宝订单金额（实收+卡销）
    */
    public function SunbeibeiOne(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        // $erpResultsTheForehead = db('s_kpi_staffgoal')->where('kpi_id','erpResultsTheForehead')->where('shop_code','in',"'ygbb01','ygbb02','ygbb03','ygbb04','ygbb05'")->sum('kpi_value');
        $erpResultsTheForehead = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','erpResultsTheForehead')->where('month',date('Y-m'))->find();

        $start = date('Ym01');
        $today = $this->today();

        $sql_yj =  "select IFNULL(sum(payment_money), 0) as money from c_paymentcontent where paytype<>4 and paytype<>7 and  (payclass=3 or payclass=0) and paymentdate between '".$start."' and '".$today."';";

        $db = db()->connect('sx_erp');
        $order_today_yj = $db->query($sql_yj);
        // 收入   月总完成
        // $erpRevenuePosition = db('s_kpi_staffgoal')->where('kpi_id','erpRevenuePosition')->where('shop_code','in',"'ygbb01','ygbb02','ygbb03','ygbb04','ygbb05'")->sum('kpi_value');
        $erpRevenuePosition = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','erpRevenuePosition')->where('month',date('Y-m'))->find();

        $sql_sr1 =  "select IFNULL(sum(payment_money), 0) as money from c_paymentcontent where paytype <> 7 and  (payclass=0) and orderid in(select orderid from c_customersp where selectday between '".$start."' and '".$today."' and sptstate = '1' and shop_code <> 'ygbb04');";
        $sql_sr2 =  "select IFNULL(sum(payment_money), 0) as money from c_paymentcontent where paytype<>7 and (payclass=3 or payclass=0) and orderid in(select orderid from c_customerorder b join c_customerinfo a on b.customerid=a.customerid where targetdate between '".$start."' and '".$today."' and area='三宝' and b.shop_code='ygbb04');";
        $sql_sr3 =  "select IFNULL(sum(p.payment_money), 0) as money from c_paymentcontent p join c_customerorder b on p.orderid=b.orderid where paytype<>7 and   (payclass=0) and paymentdate between '".$start."' and '".$today."' and targetdate between '".$start."' and '".$today."' and b.shop_code='mxg01' ;";
        $sql_sr4 =  "select IFNULL(sum(payment_money), 0) as money from c_paymentcontent where paytype<>7 and shop_code='ygbb04'  and (suoshu='后期款' or suoshu='后期补款') and paymentdate between '".$start."' and '".$today."';";

        $db = db()->connect('sx_erp');
        $order_today_sr1 = $db->query($sql_sr1);
        $order_today_sr2 = $db->query($sql_sr2);
        $order_today_sr3 = $db->query($sql_sr3);
        $order_today_sr4 = $db->query($sql_sr4);

        $erpResultsTheForehead = intval($erpResultsTheForehead['kpi_value']) ?: 0;
        $order_today_yj[0]['money'] = intval($order_today_yj[0]['money']) ?: 0;
        $data['percentage_yj'] = intval($erpResultsTheForehead/$order_today_yj[0]['money'])."%";
        $data['erpResultsTheForehead'] = $erpResultsTheForehead/10000;
        $data['complete_yj'] = number_format($order_today_yj[0]['money']/10000,2);

        $erpRevenuePosition = intval($erpRevenuePosition['kpi_value']) ?: 0;
        $order_today_sr1[0]['money'] = intval($order_today_sr1[0]['money']) ?: 0;
        $order_today_sr2[0]['money'] = intval($order_today_sr2[0]['money']) ?: 0;
        $order_today_sr3[0]['money'] = intval($order_today_sr3[0]['money']) ?: 0;
        $order_today_sr4[0]['money'] = intval($order_today_sr4[0]['money']) ?: 0;
        $data['erpRevenuePosition'] = $erpRevenuePosition/10000;
        $data['complete_sr'] = $order_today_sr1[0]['money'] + $order_today_sr2[0]['money'] + $order_today_sr3[0]['money'] + $order_today_sr4[0]['money'];
        $data['percentage_sr'] = intval($erpRevenuePosition/$data['complete_sr'])."%";
        $data['complete_sr'] = number_format($data['complete_sr']/10000,2);
        // print_r($data);exit;
        return json($data);
    } 
    /* 百天订单  本月累计  当日复购  本月累计
    */
    public function SunbeibeiTwo(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        $time = date('Ymd');
        $start = date('Ym01');
        $today = $this->today();
        //百天订单 
        $sql_bt = "select IFNULL(count(orderid), 0) as amount from c_customerorder where targetdate = '".$time."' and oeder_newclass = '百日';";

        $db = db()->connect('sx_erp');
        $bt = $db->query($sql_bt);
        //百天订单 本月累计
        $sql_bt_sum = "select IFNULL(count(orderid), 0) as amount  from c_customerorder where  targetdate between '".$start."' and '".$today."' and oeder_newclass='百日';";

        $db = db()->connect('sx_erp');
        $bt_sum = $db->query($sql_bt_sum);
        //当日复购
        // $sql_fg1 = "select IFNULL(count(distinct orderid), 0) as amount from c_paymentcontent where  (payclass=3) and (suoshu='后期款' or suoshu='后期补款')  and paymentdate = '".$time."'";
        // $sql_fg2 = "select IFNULL(count(orderid), 0) as amount  from c_customerorder where targetdate = '".$time."' and isrepurchase<>0;";
        $sql_fg = "select ph from (select ph from (select distinct b.wphone as ph from c_paymentcontent a join c_customerinfo b on a.customerid = b.customerid where a.paymentdate = '".$time."' and a.payclass=3 and a.payment_money>500 union select distinct b.mphone as ph from c_paymentcontent a join c_customerinfo b on a.customerid = b.customerid where a.paymentdate = '".$time."'  and a.payclass=3 and a.payment_money>500 union select distinct c.wphone as ph from c_paymentcontent a join c_customerorder b on a.orderid = b.orderId join c_customerinfo c on a.customerid = c.customerid where a.paymentdate = '".$time."'  and a.payclass=0 and a.paytype<>4 and a.paytype<>7 and b.nopayment_money<=0 union select distinct c.mphone as ph from c_paymentcontent a join c_customerorder b on a.orderid = b.orderId join c_customerinfo c on a.customerid = c.customerid where a.paymentdate = '".$time."'  and a.payclass=0 and a.paytype<>4 and a.paytype<>7 and b.nopayment_money<=0) aa where ph > 0) bb join c_customerinfo cc on bb.ph = cc.wphone or bb.ph = cc.mphone group by ph having count(ph) > 1 ";
        $db = db()->connect('sx_erp');
        $fg = $db->query($sql_fg);
        $fg = count($fg);
        //当日复购 本月累计
        // $sql_fg_sum1 = "select IFNULL(count(distinct orderid), 0) as amount from c_paymentcontent where  (payclass=3) and (suoshu='后期款' or suoshu='后期补款')  and paymentdate between '".$start."' and '".$today."'";
        // $sql_fg_sum2 = "select IFNULL(count(orderid), 0) as amount from c_customerorder where targetdate between '".$start."' and '".$today."' and isrepurchase<>0;";
        $sql_fg_sum = "select ph from (select ph from (select distinct b.wphone as ph from c_paymentcontent a join c_customerinfo b on a.customerid = b.customerid where a.paymentdate between '".$start."' and '".$today."' and a.payclass=3 and a.payment_money>500 union select distinct b.mphone as ph from c_paymentcontent a join c_customerinfo b on a.customerid = b.customerid where a.paymentdate between '".$start."' and '".$today."' and a.payclass=3 and a.payment_money>500 union select distinct c.wphone as ph from c_paymentcontent a join c_customerorder b on a.orderid = b.orderId join c_customerinfo c on a.customerid = c.customerid where a.paymentdate between '".$start."' and '".$today."' and a.payclass=0 and a.paytype<>4 and a.paytype<>7 and b.nopayment_money<=0 union select distinct c.mphone as ph from c_paymentcontent a join c_customerorder b on a.orderid = b.orderId join c_customerinfo c on a.customerid = c.customerid where a.paymentdate between '".$start."' and '".$today."' and a.payclass=0 and a.paytype<>4 and a.paytype<>7 and b.nopayment_money<=0) aa where ph > 0) bb join c_customerinfo cc on bb.ph = cc.wphone or bb.ph = cc.mphone group by ph having count(ph) > 1";
        $db = db()->connect('sx_erp');
        $fg_sum = $db->query($sql_fg_sum);
        $fg_sum = count($fg_sum);

        $data_bt = intval($bt[0]['amount']) ?: 0;
        for ($i=0; $i < strlen($data_bt); $i++) { 
            $data['bt'] .= "<span>".substr($data_bt , $i , 1)."</span>";
        }
        $data_bt_sum = intval($bt_sum[0]['amount']) ?: 0;
        for ($i=0; $i < strlen($data_bt_sum); $i++) { 
            $data['bt_sum'] .= "<span>".substr($data_bt_sum , $i , 1)."</span>";
        }
        for ($i=0; $i < strlen($fg); $i++) { 
            $data['fg'] .= "<span>".substr($fg , $i , 1)."</span>";
        }
        for ($i=0; $i < strlen($fg_sum); $i++) { 
            $data['fg_sum'] .= "<span>".substr($fg_sum , $i , 1)."</span>";
        }
        // print_r($sql_fg);exit;
        return json($data);
    } 
    /* 店面成交 storefrontAccomplish  以业绩为纬度，4个店面当天进店人数实收业绩为标准。
    */
    public function StorefrontAccomplish(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        // $storefrontAccomplish = db('s_kpi_staffgoal')->where('kpi_id','storefrontAccomplish')->where('shop_code','in',"'ygbb01','ygbb02','ygbb03','ygbb05'")->sum('kpi_value');
        $storefrontAccomplish = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','storefrontAccomplish')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($storefrontAccomplish['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql = "select IFNULL(count(distinct orderid), 0) as amount,paymentdate  from c_paymentcontent where paytype <> 4 and paytype <> 7 and (payclass=3 or payclass=0) and (suoshu='前期款' or suoshu='前期补款') and shop_code <> 'ygbb04' and paymentdate between '".$start."' and '".$today."' group by paymentdate;";

        $db = db()->connect('sx_erp');
        $result = $db->query($sql);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['amount'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 爱婴成交 LoveBabyAccomplish  充值、实收款订单金额（卡销不算、当次制作三宝不算）。
    */
    public function LoveBabyAccomplish(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $loveBabyAccomplish = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','loveBabyAccomplish')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($loveBabyAccomplish['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql1 = "select IFNULL(count(distinct b.orderid), 0) as amount,paymentdate from c_paymentcontent p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid where (payclass=3) and a.area<>'三宝' and b.shop_code='ygbb04' and paymentdate between '".$start."' and '".$today."' group by paymentdate;";
        $sql2 = "select IFNULL(count(distinct b.orderid), 0) as amount,paymentdate from c_paymentcontent p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid where  paytype<>4 and paytype<>7 and (payclass=0) and a.area='正价' and b.shop_code='ygbb04' and paymentdate between '".$start."' and '".$today."' group by paymentdate ;";

        $db = db()->connect('sx_erp');
        $result1 = $db->query($sql1);
        $result2 = $db->query($sql2);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result1 as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['amount'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result2 as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['amount']+$dat[$i];
                }
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 摄影量 photogrammetry 4个店面影棚+大棚合计拍摄量。
    */
    public function Photogrammetry(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $photogrammetry = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','photogrammetry')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($photogrammetry['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $day = date('Ymd');
        $sql = "select IFNULL(count(id),0) as amount,photodate from c_customerphoto where  shop_code<>'ygbb04' and photodate between '".$start."' and '".$today."' and photostate='2' group by photodate;";

        $db = db()->connect('sx_erp');
        $result = $db->query($sql);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['photodate'] && $tim <= $day) {
                    $dat[$i] = $val['amount'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 超期量    BeyondTheQuantity  客诉系统中电话回访超期数量。 
    */
    public function BeyondTheQuantity(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //时间
        $start = date('Y-m-01');
        $t = date('t');
        $tim = strtotime('17:30:00');
        $time = time();
        if ($time >= $tim || $t == 01|| $t == 1) {
            $today = date("Y-m-d 23:59:59");
        }else{
            $today = date("Y-m-d");
        }
        // if (is_install('sx_kf')) {
            $url = '5d3aab5246050';
            $data['start_time'] = $start;
            $data['end_time'] = $today;
            $data['shop_code'] = "ygbb01,ygbb02,ygbb03,ygbb05";
            $res = sx_call($url, $data,"sx_kf");
            if ($res['code'] == 200) {
                $data = $res['data'];
            }else{
                $data = '';
            }
        // }else{
        //     $data = '2';
        // }
        if (is_array($data)) {
            $arr1 = $arr2 = $arr3 = [];
            foreach ($data as $key => $value) {
                array_push($arr1,$value['day']);
                array_push($arr2,$value['num']);
                array_push($arr3,$value['chaoqi']);
            }
            $arr[1] = $arr1;
            $arr[2] = $arr2;
            $arr[3] = $arr3;      
        }else{
            $arr = '';
        }
        return json($arr);
    } 
    /* 渠道入客      网络入客
    *access_model  渠道 或 网络   dj_day 当前月     is_status 1 未删除
    *
    */
    public function ChannelGuest(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        $access_model = input('access_model');
        if ($access_model == "渠道入客") {
            $where = "customer_channel in ('公立医院','私立医院','异业扫码','线下活动')";
            $wheres = "channel_name in ('公立医院','私立医院','异业扫码','线下活动')";
        }else if ($access_model == "网络入客") {
            $where = "customer_channel in ('竞价推广','美团点评','微信','微博','抖音','小红书')";
            $wheres = "channel_name in ('竞价推广','美团点评','微信','微博','抖音','小红书')";
        }else{
            $where = "1=1";
            $wheres = "1=1";
        }
        $res = controller('crm/Sunbeibei','lib')->ChannelGuest($where);

        //目标额  
        $goal = db('s_kpi_staffgoal')->field('kpi_value')->where($wheres)->where('kpi_id','newCustomerNum')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($goal['kpi_value'],$day_sum);

        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$res[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    }
    /* 客服入客 LoveBabyRecharge 客服邀约进店顾客。【顾客类型：1、引流套系已拍摄。2、老会员已卡销。渠道来源为客服部】
    */
    public function serviceTheGuest(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $serviceTheGuest = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','serviceTheGuest')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($serviceTheGuest['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql1 = "select IFNULL(count(distinct p.orderid), 0) as amount,paymentdate from c_paymentcontent p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid  where a.area='正价' and a.cssname='客服部' and suoshu='前期款' and p.payment_money>0 and b.shop_code<>'ygbb04' and  (payclass=0) and  paytype<>7 and paymentdate between '".$start."' and '".$today."' group by paymentdate;";

        $sql2 = "select IFNULL(count(distinct p.orderid), 0) as amount,photodate from c_customerphoto p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid join c_paymentcontent on p.orderid=c_paymentcontent.orderId where a.area='活动' and a.cssname='客服部' and photodate between '".$start."' and '".$today."' and photostate='2' group by photodate;";
        $db = db()->connect('sx_erp');
        $result1 = $db->query($sql1);
        $result2 = $db->query($sql2);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result1 as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['amount'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result2 as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['photodate']) {
                    $dat[$i] = $val['amount']+$dat[$i];
                }
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 当月汇总 monthSummarizing  渠道入客   网络入客    客服入客
    */
    public function monthSummarizing(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $goal_qd = db('s_kpi_staffgoal')->field('kpi_value')->where("channel_name in ('公立医院','私立医院','异业扫码','线下活动')")->where('kpi_id','newCustomerNum')->where('month',date('Y-m'))->find();
        $goal_wl = db('s_kpi_staffgoal')->field('kpi_value')->where("channel_name in ('竞价推广','美团点评','微信','微博','抖音','小红书')")->where('kpi_id','newCustomerNum')->where('month',date('Y-m'))->find();
        $goal_kf = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','serviceTheGuest')->where('month',date('Y-m'))->find();
        //完成额
        $accomplish_qd = controller('crm/Sunbeibei','lib')->ChannelGuests("customer_channel in ('公立医院','私立医院','异业扫码','线下活动')");
        $accomplish_wl = controller('crm/Sunbeibei','lib')->ChannelGuests("customer_channel in ('竞价推广','美团点评','微信','微博','抖音','小红书')");
                //客服
        $start = date('Ym01');
        $today = $this->today();
        $sql1 = "select IFNULL(count(distinct p.orderid), 0) as amount from c_paymentcontent p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid where a.area='正价' and a.cssname='客服部' AND suoshu = '前期款' AND p.payment_money > 0 AND b.shop_code <> 'ygbb04' AND (payclass = 0) AND paytype <> 7 and paymentdate between '".$start."' and '".$today."'";
        $sql2 = "select IFNULL(count(distinct p.orderid), 0) as amount from c_customerphoto p join c_customerorder b on p.orderid=b.orderid join c_customerinfo a on b.customerid=a.customerid join c_paymentcontent on p.orderid=c_paymentcontent.orderId where a.area='活动' and a.cssname='客服部' and photodate between '".$start."' and '".$today."' and photostate='2'";
        $db = db()->connect('sx_erp');
        $result1 = $db->query($sql1);
        $result2 = $db->query($sql2);
        $result_kf = intval($result1[0]['amount'])+intval($result2[0]['amount']);

        $arr = [];
        //渠道
        $tmap_qd['product'] = '渠道部';
        $tmap_qd['目标'] = $goal_qd['kpi_value']?:0;
        $tmap_qd['完成额'] = $accomplish_qd[0]['id_count'];
        array_push($arr,$tmap_qd);
        //网络
        $tmap_wl['product'] = '网络部';
        $tmap_wl['目标'] = $goal_wl['kpi_value']?:0;
        $tmap_wl['完成额'] = $accomplish_wl[0]['id_count'];
        array_push($arr,$tmap_wl);
        //客服
        $tmap['product'] = '客服部';
        $tmap['目标'] = $goal_kf['kpi_value']?:0;
        $tmap['完成额'] = $result_kf;
        array_push($arr,$tmap);
        return json($arr);
    } 
    /* 爱婴充值 LoveBabyRecharge 一销充值+二销充值。
    */
    public function LoveBabyRecharge(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        // $LoveBabyRecharge = db('s_kpi_staffgoal')->where('kpi_id','LoveBabyRecharge')->where('shop_code','in',"'ygbb01','ygbb02','ygbb03','ygbb05','mxg01'")->sum('kpi_value');
        $LoveBabyRecharge = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','LoveBabyRecharge')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($LoveBabyRecharge['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql = "select IFNULL(sum(payment_money), 0) as money,paymentdate from c_paymentcontent where  (payclass=3) and shop_code='ygbb04' and paymentdate between '".$start."' and '".$today."' group by paymentdate ;";

        $db = db()->connect('sx_erp');
        $result = $db->query($sql);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['money'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 样后实销 AfterTheSamplePin 二销实销（卡销+实收）。
    */
    public function AfterTheSamplePin(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $AfterTheSamplePin = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','AfterTheSamplePin')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($AfterTheSamplePin['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql = "select IFNULL(sum(payment_money), 0) as money,paymentdate from c_paymentcontent where paytype<>7 and  (payclass=0) and shop_code<>'ygbb04' and shop_code<>'mxg01' and (suoshu='后期款' or suoshu='后期补款')  and paymentdate between '".$start."' and '".$today."' group by paymentdate;";

        $db = db()->connect('sx_erp');
        $result = $db->query($sql);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['money'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 实销均价     TheSolidPinAverage      即为二销实销均价=二销实销/选片量。
    */
    public function TheSolidPinAverage(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $TheSolidPinAverage = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','TheSolidPinAverage')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($TheSolidPinAverage['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql1 = "select IFNULL(sum(payment_money), 0) as money,paymentdate from c_paymentcontent where paytype<>7 and  (payclass=0) and shop_code<>'ygbb04' and (suoshu='后期款' or suoshu='后期补款') and paymentdate between '".$start."' and '".$today."' group by paymentdate;";
        $sql2 = "select IFNULL(count(id),0) as amount,selectday from c_customersp where shop_code<>'ygbb04' and selectday between '".$start."' and '".$today."' group by selectday;";
        $db = db()->connect('sx_erp');
        $result1 = $db->query($sql1);
        $result2 = $db->query($sql2);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result1 as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate'] || $tim == $result2[$k]['paymentdate']) {
                    $dat[$i] = number_format($val['money']/$result2[$k]['amount'],2);
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 
    /* 店面充值 StoreTopUp 一销充值+二销充值。
    */
    public function StoreTopUp(){
        $auth = controller('crm/Bigscreen', 'lib')->checkBigScreenAuth();
        if ($auth === false) {
            controller('crm/Index','controller')->index();
            exit;
        }
        //目标额 
        $StoreTopUp = db('s_kpi_staffgoal')->field('kpi_value')->where('flag','brandclass')->where('kpi_id','StoreTopUp')->where('month',date('Y-m'))->find();
        $year = date('Y');
        $month = date('m');
        $day_sum = cal_days_in_month(CAL_GREGORIAN, $month, $year);
        $target = $this->dayMonthSum($StoreTopUp['kpi_value'],$day_sum);

        $start = date('Ym01');
        $today = $this->today();
        $sql = "select IFNULL(sum(payment_money), 0) as money,paymentdate from c_paymentcontent where (payclass=3) and shop_code<>'ygbb04' and paymentdate between '".$start."' and '".$today."' group by paymentdate ;";

        $db = db()->connect('sx_erp');
        $result = $db->query($sql);
        $dat = array();
        for($i=1; $i<=$day_sum; $i++){
            foreach ($result as $k => $val) {
                $ii = strlen($i) == 1 ? "0".$i : $i;
                $tim = $year.$month.$ii;
                if ($tim == $val['paymentdate']) {
                    $dat[$i] = $val['money'];
                }
            }
            if (!$dat[$i]) {
                $dat[$i] = '';
            }
        }
        $arr1 = $arr2 = $arr3 = [];
        foreach ($target as $key => $value) {
            array_push($arr1,$key);
            array_push($arr2,$value);
            array_push($arr3,$dat[$key]);
        }
        $arr[1] = $arr1;
        $arr[2] = $arr2;
        $arr[3] = $arr3;
        return json($arr);
    } 




    // 每月的量每天均值分摊   $num 总量  $day_sum  天数
    public function dayMonthSum($num,$day_sum){
        $a = array();
        $as = '';
        for($i=1; $i<=$day_sum; $i++){
            $a[$i] = intval(floatval($num)/floatval($day_sum));
            $as += intval(floatval($num)/floatval($day_sum));
        }
        if (intval($num)-$as > 0) {
            for($j=1; $j<=intval($num)-$as; $j++){
                $a[$j] = $a[$j]+1;
            }
        }
        return $a;
    }
    public function isMobile(){
        $useragent=isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : '';
        $useragent_commentsblock=preg_match('|.∗?|',$useragent,$matches)>0?$matches[0]:'';
        function CheckSubstrs($substrs,$text){
            foreach($substrs as $substr){
                if(false!==strpos($text,$substr)){
                    return true;
                }else{
                    return false;
                }
            }
        }
        $mobile_os_list=array('Google Wireless Transcoder','Windows CE','WindowsCE','Symbian','Android','armv6l','armv5','Mobile','CentOS','mowser','AvantGo','Opera Mobi','J2ME/MIDP','Smartphone','Go.Web','Palm','iPAQ');
        $mobile_token_list=array('Profile/MIDP','Configuration/CLDC-','160×160','176×220','240×240','240×320','320×240','UP.Browser','UP.Link','SymbianOS','PalmOS','PocketPC','SonyEricsson','Nokia','BlackBerry','Vodafone','BenQ','Novarra-Vision','Iris','NetFront','HTC_','Xda_','SAMSUNG-SGH','Wapaka','DoCoMo','iPhone','iPod');

        $found_mobile=CheckSubstrs($mobile_os_list,$useragent_commentsblock) || CheckSubstrs($mobile_token_list,$useragent);

        if ($found_mobile){
            return true;
        }else{
            return false;
        }
    }
    //时间
    public function today(){
        $t = date('t');
        $tim = strtotime('17:30:00');
        $time = time();
        if ($time >= $tim || $t == 01|| $t == 1) {
            $today = date("Ymd");
        }else{
            $today = date("Ymd",strtotime("-1 day"));
        }
        return $today;
    }
}