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

class Whkk extends Controller{
    // 大屏专用  判断  该员工是否有权限查看大屏数据
    public function checkBigScreenAuth(){
        // 获取配置文件中的信息，根据,分割成数组，如果staff_id不存在，则跳转到登录或首页
        $staff_id = session('staff_id');
        $str_id = config('bigScreen_id');
        $bigScreen_id = explode(',', $str_id);
        $is_in = in_array($staff_id, $bigScreen_id);
        if ($is_in === false) {
            ///查看有无查看大屏权限
            if(check_right('menu1556162726')){
                return true;
            }
            return false;
        }else{
            return true;
        }
    }
    /**
     * 获取营收
     * @param  start        开始时间
     * @param  end          结束时间
     * @param  shop_code    查询店面 为空则查询全部 多个店面用,分隔
     * @return array        返回每个店的营收数据 
     * @author lilong<lilong@suxuantech.cn>
     */
    public function getYingshou($param){
    	$start = date('Ymd',strtotime($param['start']));
    	$end = date('Ymd',strtotime($param['end']));    	
        
        

        // $deal = db('c_customerorder')->alias('o')
        //     ->join('crm_c_customerinfo c','o.crm_customer_id=c.id','left')
        //     ->where($where)
        //     ->where('o.targetdate', 'between', [$start,$end])
        //     //->group('o.targetdate,o.shop_code')
        //     ->field('sum(o.total_money) as total_money,o.payment_money,o.targetdate,o.shop_code,o.shop_name')
        //     ->select();
$where['pay.shop_code'] = ['in',[$param['shop_code_wx'][0],$param['shop_code_ms'][0]]];
        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_paymentcontent pay','o.orderId=pay.orderId','left')
            ->where('isshishou','实收')
            ->where($where)
            ->where('pay.paymentdate', 'between', [$start,$end])
            ->field('sum(pay.payment_money) as money')
            ->find();

        // echo db()->getLastSql();
        return $deal;
    }
    /**
     * 获取营收
     * @param  start        开始时间
     * @param  end          结束时间
     * @param  shop_code    查询店面 为空则查询全部 多个店面用,分隔
     * @return array        返回每个店的营收数据 
     * @author lilong<lilong@suxuantech.cn>
     */
    public function getqqyj($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));        
        
        $where['pay.shop_code'] = ['in',$param['shop_code_ms']];
        $where['o.shop_code'] = ['in',$param['shop_code_ms']];


        // $deal = db('c_customerinfo')->alias('c')
        //     ->join('c_customerorder o','c.customerid=o.customerid','left')
        //     ->join('c_paymentcontent pay','o.orderId=pay.orderId','left')
        //     ->join('crm_s_channel s','s.channel_name=c.customer_channel','left')
        //     ->where('pay.suoshu',['in',['前期款','前期补款','一销退款']])
        //     ->where('isshishou','实收')
        //     ->whereOr(function($query){
        //         $query->where('o.shop_code',['in','BMSG01'])->whereor('o.shop_code',['in','BMSG03'])->whereor('s.channel_info_name',null)->where('s.channel_info_name',['not in',['新美大','抖音','朋友圈推广']]);
        //     })
        //     ->where('pay.shop_code',['in',$param['shop_code_ms']])
        //     // ->where($where)
        //     ->where('pay.paymentdate', 'between', [$start,$end])
        //     ->field('sum(pay.payment_money) as money')
        //     ->find();

        $deal = Db::query("SELECT
    sum(pay.payment_money) AS money
FROM
    `c_customerinfo` `c`
LEFT JOIN `c_customerorder` `o` ON `c`.`customerid` = `o`.`customerid`
LEFT JOIN `c_paymentcontent` `pay` ON `o`.`orderId` = `pay`.`orderId`
LEFT JOIN `crm_s_channel` `s` ON `s`.`channel_name` = `c`.`customer_channel`
WHERE
    (
        `pay`.`suoshu` IN (
            '前期款',
            '前期补款',
            '一销退款'
        )
    )
AND `isshishou` = '实收'
AND (
    `o`.`shop_code` IN ('BMSG03')
    AND (
        `s`.`channel_info_name` NOT IN (
            '新美大',
            '抖音',
            '朋友圈推广'
        )
        OR `s`.`channel_info_name` IS NULL
    )
    OR `o`.`shop_code` IN ('BMSG01')
)
AND (
    `pay`.`shop_code` IN ('BMSG01')
)
AND `pay`.`paymentdate` BETWEEN '".$start."'
AND '".$end."'
LIMIT 1");
        // echo db()->getLastSql();die;
        return $deal;
    }
    //前期订单数
    public function getqqddShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['o.shop_code'] = ['in',$param['shop_code_ms']];
        }
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
        }
        $where['o.blankoutannal'] = ['neq',1];
        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->where($where)
            ->where('o.targetdate', 'between', [$start,$end])
            ->field('count(orderId) as count')
            ->find();
        //echo db()->getLastSql();
        return $deal;
    }


    /**
     * 获取总支出
     * @param  start        开始时间
     * @param  end          结束时间
     * @param  brandclass_id    查询事业部，多个用逗号分隔
     * @return array        返回每个店的营收数据 
     * @author lilong<lilong@suxuantech.cn>
     */
    public function getChannelCost($param){
        $start = date('Y-m-d',strtotime($param['start']));
        $end = date('Y-m-d',strtotime($param['end']));
        if ($param['brandclass_id']) {
            $where['brandclass_id'] = ['in',$param['brandclass_id']];
        }
        $deal = db('crm_s_channel_cost')
            ->where($where)
            ->where('cost_date', 'between', [$start,$end])
            ->group('cost_date,brandclass_id')
            ->field('sum(cost) as cost,cost_date,brandclass_id')
            ->select();
        // echo db()->getLastSql();
        return $deal;
    }

    /**
     * 网络产值
     * @param  start        开始时间
     * @param  end          结束时间
     * @param  brandclass_id查询事业部，多个用逗号分隔
     * @return array        返回每个店的营收数据 
     * @author lilong<lilong@suxuantech.cn>
     */
    public function getWxchanzhi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        if($param['shop_code_wx']){
            $where['pay.shop_code'] = ['in',$param['shop_code_wx']];
        }
        $deal = db('c_customerorder')->alias('o')
            ->join('c_paymentcontent pay','o.orderId=pay.orderId','left')
            ->where($where)
            ->where('isshishou','实收')
            ->where('pay.paymentdate', 'between', [$start,$end])
            //->group('targetdate')
            ->field('sum(pay.payment_money) as total_money,pay.shop_code')
            ->select();
        // echo db()->getLastSql();
        return $deal;
    }

    //毛客资量 start:开始时间,end:结束时间
    public function getMaoKeZiLiang($param){
        $start = date('Y-m-d 00:00:00',strtotime($param['start']));
        $end = date('Y-m-d 23:59:59',strtotime($param['end']));
        if($param['shop_code_wx']){
            $where['c.shop_code'] = ['in',$param['shop_code_wx']];
        }
        if($param['shop_code_ms']){
            $where['c.shop_code'] = ['in',$param['shop_code_ms']];
        }
        // if ($param['brandclass_id']) {
        //     $where['c.brandclass_id'] = ['in',$param['brandclass_id']];
        // }
        $where['c.is_status'] = 1;
        $where['c.c_from'] = 1;

        $return = db('crm_c_customerinfo')->alias('c')
            ->field("c.is_valid,c.dj_day,c.sales_group_id,c.sales_staff")
            ->where($where)
            ->where('c.dj_day','between',[$start,$end])
            ->select();
            // echo db()->getLastSql();die;
        return $return;
    }
    //网转业绩 start:开始时间,end:结束时间
    public function getWangZhuanYeJi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
            $where['pay.shop_code'] = ['not in',$param['shop_code_wx']];
        }
        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_paymentcontent pay','o.orderId=pay.orderId','left')
            ->join('crm_s_channel s','s.channel_name=c.customer_channel','left')
            ->where('s.channel_info_name',['in',['新美大','朋友圈推广','抖音']])
            ->where('pay.suoshu',['in',['前期款','前期补款','一销退款']])
            ->where('isshishou','实收')
            ->where('pay.paymentdate', 'between', [$start,$end])
            ->where($where)
            // ->group('pay.paymentdate')
            ->field('pay.payment_money,o.targetdate')
            ->select();
        // echo db()->getLastSql();die;
        return $deal;
    }
    //选改单数
    public function getXuanGaiDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',['<>',1])
            ->where('selectday', 'between', [$start,$end])
            ->count();

        //总
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,selectday FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerspgq sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."' AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND selectday=DATE_FORMAT(qgtime,'%Y%m%d') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customersp sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."')");

        // echo db()->getLastSql();die;
        $count = $deal1+count($deal2);
        return $count;
    }
    //选片实收
    public function getXuanPianChanZhi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['pay.shop_code'] = ['in',$param['shop_code_ms']];
        }
        $deal = db('c_customerinfo')->alias('a')
            ->join('c_customerorder b','a.customerid=b.customerid','left')
            ->join('c_paymentcontent pay','b.orderId=pay.orderId','left')
            // ->join('crm_s_channel s','s.channel_name=a.customer_channel','left')
            ->where('suoshu',['in',['后期款','后期补款','二销退款']])
            ->where('isshishou','实收')
            ->where('pay.paymentdate', 'between', [$start,$end])
            ->where($where)
            // ->where(function($query){
            //     $query->where('s.channel_info_name',null)->whereor('s.channel_info_name',['not in',['新美大','朋友圈推广','抖音']]);
            // })
            ->field('sum(pay.payment_money) as payment_money')
            ->select();
        // echo db()->getLastSql();die;
        return $deal;
    }
    //网选总产值
    public function getWangXuanZongChanZhi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['p.shop_code'] = ['in',$param['shop_code_ms']];
        }
        $deal = db('c_customerinfo')->alias('a')
            ->join('c_customerorder b','a.customerid=b.customerid','left')
            ->join('c_paymentcontent p','b.orderId=p.orderId','left')
            ->join('crm_s_channel s','s.channel_name=a.customer_channel','left')
            ->where('s.channel_info_name',['in',['新美大','朋友圈推广','抖音']])
            ->where('p.suoshu',['in',['后期款','后期补款','二销退款']])
            ->where('p.isshishou','实收')
            ->where('p.paymentdate', 'between', [$start,$end])
            ->where($where)
            ->field('sum(p.payment_money) as money,p.paymentdate')
            ->select();
        // echo db()->getLastSql();die;
        return $deal;
    }
    //正价当销均单
    public function getZhengJiaDangXiaoJunJia($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

        $tiyanmoney = db('s_globalset')->value('tiyanmoney');

        //总
        $deal1 = Db::query("select sp.id from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customersp sp on sp.orderId=b.orderId join c_customerphoto p on sp.orderId=p.orderId and p.islast=1 and sp.currentfc=p.currentfc where p.qqphotomoney>='".$tiyanmoney."' and sp.sptend_day between '".$start."' and '".$end."' and sp.shop_code = '".$param['shop_code_ms'][0]."'");



        $deal2 = Db::query("SELECT
            ifnull(sum(pay.payment_money), 0)AS shixiao_money
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customersp sp ON sp.orderId = b.orderId
JOIN c_customerphoto p ON sp.orderId = p.orderId
AND p.islast = 1
AND sp.currentfc = p.currentfc
join c_paymentcontent pay on pay.orderId = b.orderId
        AND pay.currentfc = sp.currentfc
        AND suoshu IN (
            '后期款',
            '后期补款',
            '二销退款'
        )
        and fundname='后期单销'
WHERE
    p.qqphotomoney >= '".$tiyanmoney."'
AND pay.paymentdate BETWEEN '".$start."'
AND '".$end."'
AND sp.shop_code = '".$param['shop_code_ms'][0]."'");

        return round($deal2[0]['shixiao_money']/count($deal1),1);
    }
    //实收正价均单
    public function getShiShouZhengJiaDangXiaoJunJia($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

        $tiyanmoney = db('s_globalset')->value('tiyanmoney');

        $deal1 = Db::query("select sp.id from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customersp sp on sp.orderId=b.orderId join c_customerphoto p on sp.orderId=p.orderId and p.islast=1 and sp.currentfc=p.currentfc where p.qqphotomoney>='".$tiyanmoney."' and sp.sptend_day between '".$start."' and '".$end."' and sp.shop_code = '".$param['shop_code_ms'][0]."'");



        $deal2 = Db::query("SELECT
            ifnull(sum(pay.payment_money), 0)AS shixiao_money
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customersp sp ON sp.orderId = b.orderId
JOIN c_customerphoto p ON sp.orderId = p.orderId
AND p.islast = 1
AND sp.currentfc = p.currentfc
join c_paymentcontent pay on pay.orderId = b.orderId
        AND pay.currentfc = sp.currentfc
        AND suoshu IN (
            '后期款',
            '后期补款',
            '二销退款'
        )
        AND isshishou = '实收'
WHERE
    p.qqphotomoney >= '".$tiyanmoney."'
AND pay.paymentdate BETWEEN '".$start."'
AND '".$end."'
AND sp.shop_code = '".$param['shop_code_ms'][0]."'");
       
        return round($deal2[0]['shixiao_money']/count($deal1),1);
    }
    //体验当销均单
    public function getTiYanDangXiaoJunJia($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['shop_code'] = ['in',$param['shop_code_ms']];
        }
        $tiyanmoney = db('s_globalset')->value('tiyanmoney');
        $deal1 = Db::query("select sp.id from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customersp sp on sp.orderId=b.orderId join c_customerphoto p on sp.orderId=p.orderId and p.islast=1 and sp.currentfc=p.currentfc where p.qqphotomoney<'".$tiyanmoney."' and sp.sptend_day between '".$start."' and '".$end."' and sp.shop_code = '".$param['shop_code_ms'][0]."'");



        $deal2 = Db::query("SELECT
            ifnull(sum(pay.payment_money), 0)AS shixiao_money
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customersp sp ON sp.orderId = b.orderId
JOIN c_customerphoto p ON sp.orderId = p.orderId
AND p.islast = 1
AND sp.currentfc = p.currentfc
join c_paymentcontent pay on pay.orderId = b.orderId
        AND pay.currentfc = sp.currentfc
        AND suoshu IN (
            '后期款',
            '后期补款',
            '二销退款'
        )
        and fundname='后期单销'
WHERE
    p.qqphotomoney < '".$tiyanmoney."'
AND pay.paymentdate BETWEEN '".$start."'
AND '".$end."'
AND sp.shop_code = '".$param['shop_code_ms'][0]."'");
        // //总
        // $deal = Db::query("select sp.id,(select ifnull(sum(payment_money),0) from c_paymentcontent pay where pay.orderId = b.orderId and pay.currentfc = sp.currentfc and suoshu in('后期款','后期补款','二销退款') and fundname='后期单销' ) as shixiao_money from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customersp sp on sp.orderId=b.orderId join c_customerphoto p on sp.orderId=p.orderId and p.islast=1 and sp.currentfc=p.currentfc where p.qqphotomoney<'".$tiyanmoney."' and sp.sptend_day between '".$start."' and '".$end."' and sp.shop_code = '".$param['shop_code_ms'][0]."'");
        // echo db()->getLastSql();die;
        return round($deal2[0]['shixiao_money']/count($deal1),1);
    }
    //体验当销均单
    public function getShiShouTiYanDangXiaoJunJia($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['shop_code'] = ['in',$param['shop_code_ms']];
        }
        $tiyanmoney = db('s_globalset')->value('tiyanmoney');
        $deal1 = Db::query("select sp.id from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customersp sp on sp.orderId=b.orderId join c_customerphoto p on sp.orderId=p.orderId and p.islast=1 and sp.currentfc=p.currentfc where p.qqphotomoney<'".$tiyanmoney."' and sp.sptend_day between '".$start."' and '".$end."' and sp.shop_code = '".$param['shop_code_ms'][0]."'");



        $deal2 = Db::query("SELECT
            ifnull(sum(pay.payment_money), 0)AS shixiao_money
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customersp sp ON sp.orderId = b.orderId
JOIN c_customerphoto p ON sp.orderId = p.orderId
AND p.islast = 1
AND sp.currentfc = p.currentfc
join c_paymentcontent pay on pay.orderId = b.orderId
        AND pay.currentfc = sp.currentfc
        AND suoshu IN (
            '后期款',
            '后期补款',
            '二销退款'
        )
        AND isshishou = '实收'
WHERE
    p.qqphotomoney < '".$tiyanmoney."'
AND pay.paymentdate BETWEEN '".$start."'
AND '".$end."'
AND sp.shop_code = '".$param['shop_code_ms'][0]."'");
        // echo db()->getLastSql();die;
        return round($deal2[0]['shixiao_money']/count($deal1),1);
    }
    //选片实收续订
    public function getXuanPianShiShouXuDing($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        $deal = Db::query("select ifnull(sum(payment_money),0) as money from c_paymentcontent pay where suoshu in('后期款','后期补款','二销退款') and fundname in ('后期续订','后期升套') and (isshishou='实收' or paytype = 'VIP卡') and shop_code = '".$param['shop_code_ms'][0]."' and paymentdate between '".$start."' and '".$end."' ");

        // echo db()->getLastSql();die;
        return $deal;
    }
    //选片卡扣续订
    public function getXuanPianKaKouXuDing($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        $deal = Db::query("select ifnull(sum(payment_money),0) as money from c_paymentcontent pay where suoshu in('后期款','后期补款','二销退款') and fundname in ('后期续订','后期升套') and paytype='VIP卡' and shop_code = '".$param['shop_code_ms'][0]."' and paymentdate between '".$start."' and '".$end."' ");

        // echo db()->getLastSql();die;
        return $deal;
    }
    //选片当销
    public function getXuanPianDangXiao($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        $deal = Db::query("select ifnull(sum(payment_money),0) as money from c_paymentcontent where shop_code in ('".$param['shop_code_ms'][0]."') and suoshu in('后期款','后期补款','二销退款') and (isshishou='实收' or paytype ='VIP卡') and paymentdate between '".$start."' and '".$end."' and fundname='后期单销'");
        
        // echo db()->getLastSql();die;
        return $deal;
    }
    //网选当销
    public function getWangXuanDangXiao($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        $deal = Db::query("select ifnull(sum(c.payment_money),0) as money from c_paymentcontent c join c_customerorder o on c.customerid=o.customerid where c.shop_code in ('".$param['shop_code_ms'][0]."') and c.suoshu in('后期款','后期补款','二销退款') and c.isshishou='实收' and c.paymentdate between '".$start."' and '".$end."' and fundname='后期单销'");
        // echo db()->getLastSql();die;
        return $deal;
    }
    //选片卡扣
    public function getXuanPianKaKou($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        $deal = Db::query("select ifnull(sum(payment_money),0) as money from c_paymentcontent where shop_code in ('".$param['shop_code_ms'][0]."') and suoshu in('后期款','后期补款','二销退款')  and paytype='VIP卡' and paymentdate between '".$start."' and '".$end."' and fundname='后期单销'");

        // echo db()->getLastSql();die;
        return $deal;
    }
    //约选单数
    public function getYueXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',['in',[0,2,5]])
            ->where('selectday', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();
        // echo db()->getLastSql();
        //总
        $deal2 = Db::query("SELECT selectday,qgtime FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerspgq sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."' AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') and b.blankoutannal<>1 AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customersp sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."')");
        // echo "SELECT selectday,qgtime FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerspgq sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."' AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customersp sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."')";die;
        
        foreach ($deal2 as $key => $value) {
            if($value['selectday'] && date('Ymd',strtotime('-'.$param['globalset']['shuangyueday'].' day',strtotime($value['selectday']))) <= date('Ymd',strtotime($value['qgtime']))){
                $deal1 = $deal1+1;
            }
        }


      /*  //总
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,photodate FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphotogq sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."' and sp.photodate <= DATE_FORMAT(sp.gqtime,'%Y%m%d') and b.blankoutannal<>1 AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphoto sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."')");


        // echo db()->getLastSql();die;
        $count = $deal1+count($deal2);

        //约拍  
        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',5)
            ->where('photodate', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();

        // echo db()->getLastSql();die;
        // $count = $deal1;*/
        return $deal1;
    }
    //约拍单量
    public function getYuePaiDanLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        // $deal1 = db('c_customerinfo')->alias('c')
        //     ->join('c_customerorder o','c.customerid=o.customerid','left')
        //     ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
        //     ->where($where)
        //     // ->where('sptstate',['<>',1])
        //     ->where('photodate', 'between', [$start,$end])
        //     ->count();
        // echo db()->getLastSql();

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',5)
            ->where('photodate', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();

        // //总
        // $deal2 = Db::query("SELECT photodate,gqtime FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphotogq sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."' AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphoto sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."')");

        // echo "SELECT photodate,gqtime FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphotogq sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."' AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphoto sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."')";die;

        // foreach ($deal2 as $key => $value) {
        //    if($value['photodate'] && date('Ymd',strtotime('-'.$param['globalset']['shuangyueday'].' day',strtotime($value['photodate']))) <= date('Ymd',strtotime($value['gqtime']))){
        //         $deal1 = $deal1+1;
        //     }
        // }

        // echo db()->getLastSql();die;
        //$count = $deal1;
        return $deal1;
    }
    //网选约选单数
    public function getWangXuanYueXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
        }

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',5)
            ->where('selectday', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();
        //总
        /*
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,selectday FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerspgq sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."' AND b.shop_code IN ('".$param['shop_code_wx'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customersp sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."')");*/

       

        // echo db()->getLastSql();die;
        // $count = $deal1;
        return $deal1;
    }
    //实选单数
    public function getShiXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',['in',['0','1']])
            ->where('selectday', 'between', [$start,$end])
            ->count();
/*
             if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            // ->where('sptstate',1)
            ->where('sptend_day', 'between', [$start,$end])
            ->count();*/
        //echo db()->getLastSql();die;
        return $deal;
    }
    //实拍单量
    public function getShiPaiDanLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',['in',['1','2']])
            ->where('photodate', 'between', [$start,$end])
            ->count();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //网选实选单数
    public function getWangXuanShiXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',['in',['0','1']])
            ->where('selectday', 'between', [$start,$end])
            ->count();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //完选单数
    public function getWanXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            // ->where('sptstate',1)
            ->where('sptend_day', 'between', [$start,$end])
            ->count();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //完拍单量
    public function getWanPaiDanLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',2)
            ->where('photodate', 'between', [$start,$end])
            ->where('o.blankoutannal', ['<>',1])
            ->count();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //网选完选单数
    public function getWangXuanWanXuanDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            // ->where('sptstate',1)
            ->where('sptend_day', 'between', [$start,$end])
            ->count();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //网选改单数
    public function getWangXuanGaiDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_wx']){
            $where['o.shop_code'] = ['in',$param['shop_code_wx']];
        }

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customersp sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('sptstate',['in',[0,2]])
            ->where('selectday', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();

        //总
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,selectday FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerspgq sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."' AND b.shop_code IN ('".$param['shop_code_wx'][0]."') AND selectday=DATE_FORMAT(qgtime,'%Y%m%d') and b.blankoutannal<>1 AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',selectday) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customersp sp ON b.orderId = sp.orderId WHERE selectday BETWEEN '".$start."' AND '".$end."')");

        // echo db()->getLastSql();die;
        $count = $deal1+count($deal2);

     /*   $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',['in',[0,1]])
            ->where('photodate', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();

        //总
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,photodate FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphotogq sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."' and sp.photodate <= DATE_FORMAT(sp.gqtime,'%Y%m%d') and b.blankoutannal<>1 AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphoto sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."')");
*/

        return $count;
    }
    //拍改单数
    public function getPaiGaiDanShu($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['sp.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal1 = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto sp','o.orderId=sp.orderId','left')
            ->where($where)
            ->where('photostate',['in',[0,1]])
            ->where('photodate', 'between', [$start,$end])
            ->where('o.blankoutannal',['<>',1])
            ->count();

        //总
        $deal2 = Db::query("SELECT DISTINCT sp.orderId,photodate FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphotogq sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."' and sp.photodate <= DATE_FORMAT(sp.gqtime,'%Y%m%d') and b.blankoutannal<>1 AND sp.shop_code IN ('".$param['shop_code_ms'][0]."') AND CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) NOT IN (SELECT CONCAT(sp.orderId,'_-_',sp.currentfc,'_-_',photodate) FROM c_customerinfo a JOIN c_customerorder b ON a.customerid = b.customerid JOIN c_customerphoto sp ON b.orderId = sp.orderId WHERE photodate BETWEEN '".$start."' AND '".$end."')");

        // echo db()->getLastSql();die;
        $count = $deal1+count($deal2);
        return $count;
    }
    //拍摄金额
    public function getPaiSheJine($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['p.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto p','o.orderId=p.orderId ','left')
            ->where($where)
            // ->where('sptstate',1)
            ->where('p.photodate', 'between', [$start,$end])
            ->field('sum(qqphotomoney) as money')
            ->select();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //拍摄服装
    public function getPaiSheFuZhuang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if($param['shop_code_ms']){
            $where['p.shop_code'] = ['in',$param['shop_code_ms']];
        }

        $deal = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.customerid=o.customerid','left')
            ->join('c_customerphoto p','o.orderId=p.orderId ','left')
            ->where($where)
            // ->where('sptstate',1)
            ->where('p.photodate', 'between', [$start,$end])
            ->field('sum(yipaidress_count) as count')
            ->select();
        //echo db()->getLastSql();die;
        return $deal;
    }
    //库存单量
    public function getKuCunDanLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        //总
        // $deal = Db::query("select distinct fc.orderId from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customerorder_servicefc fc on b.orderId=fc.orderId join c_customerphoto pp on b.orderId=pp.orderId and pp.currentfc = fc.currentfc where fcstate<>'1' and (pp.shop_code = '".$param['shop_code_ms'][0]."' or pp.shop_code = '".$param['shop_code_wx'][0]."') and CONCAT(fc.orderId,'_-_',fc.currentfc) not in (select CONCAT(p.orderId,'_-_',p.currentfc) from c_customerinfo a join c_customerorder b on a.customerid=b.customerid join c_customerphoto p on b.orderId=p.orderId where p.photostate='2')");
        $deal = Db::query("select count(distinct orderId) as  count from (
SELECT DISTINCT
    fc.orderId,
    fc.currentfc,
    (select ifnull(sum(yejiprice),0) from c_consumptioncontent con where con.orderId=pp.orderId and con.servicenumber=pp.currentfc) as yeji
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customerorder_servicefc fc ON b.orderId = fc.orderId
JOIN c_customerphoto pp ON b.orderId = pp.orderId
AND pp.currentfc = fc.currentfc
WHERE
    fcstate <> '1' and b.blankoutannal <> 1
AND (pp.shop_code = '".$param['shop_code_ms'][0]."' or pp.shop_code = '".$param['shop_code_wx'][0]."')
AND CONCAT(
    fc.orderId,
    '_-_',
    fc.currentfc
) NOT IN (
    SELECT
        CONCAT(
            p.orderId,
            '_-_',
            p.currentfc
        )
    FROM
        c_customerinfo a
    JOIN c_customerorder b ON a.customerid = b.customerid
    JOIN c_customerphoto p ON b.orderId = p.orderId
    WHERE
        p.photostate = '2'
)) t where yeji>='".$param['globalset']['tiyanmoney']."'
");
        return $deal;
    }
    //体验库存单量
    public function getTiYanKuCunDanLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        $tiyanmoney = db('s_globalset')->value('tiyanmoney');
        //总
        $deal = Db::query("select count(distinct orderId) as  count from (
SELECT DISTINCT
    fc.orderId,
    fc.currentfc,
    (select ifnull(sum(yejiprice),0) from c_consumptioncontent con where con.orderId=pp.orderId and con.servicenumber=pp.currentfc) as yeji
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customerorder_servicefc fc ON b.orderId = fc.orderId
JOIN c_customerphoto pp ON b.orderId = pp.orderId
AND pp.currentfc = fc.currentfc
WHERE
    fcstate <> '1' and b.blankoutannal <> 1
AND (pp.shop_code = '".$param['shop_code_ms'][0]."' or pp.shop_code = '".$param['shop_code_wx'][0]."')
AND CONCAT(
    fc.orderId,
    '_-_',
    fc.currentfc
) NOT IN (
    SELECT
        CONCAT(
            p.orderId,
            '_-_',
            p.currentfc
        )
    FROM
        c_customerinfo a
    JOIN c_customerorder b ON a.customerid = b.customerid
    JOIN c_customerphoto p ON b.orderId = p.orderId

    WHERE
        p.photostate = '2'
)) t where yeji<'".$tiyanmoney."'
");
        return $deal;
    }

    public function getKuCunJine($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

       $deal = Db::query("select sum(yeji) as  money from (
SELECT DISTINCT
    fc.orderId,
    fc.currentfc,
    (select ifnull(sum(yejiprice),0) from c_consumptioncontent con where con.orderId=pp.orderId and con.servicenumber=pp.currentfc) as yeji
FROM
    c_customerinfo a
JOIN c_customerorder b ON a.customerid = b.customerid
JOIN c_customerorder_servicefc fc ON b.orderId = fc.orderId
JOIN c_customerphoto pp ON b.orderId = pp.orderId
AND pp.currentfc = fc.currentfc
WHERE
    fcstate <> '1' and b.blankoutannal <> 1
AND (pp.shop_code = '".$param['shop_code_ms'][0]."' or pp.shop_code = '".$param['shop_code_wx'][0]."')
AND CONCAT(
    fc.orderId,
    '_-_',
    fc.currentfc
) NOT IN (
    SELECT
        CONCAT(
            p.orderId,
            '_-_',
            p.currentfc
        )
    FROM
        c_customerinfo a
    JOIN c_customerorder b ON a.customerid = b.customerid
    JOIN c_customerphoto p ON b.orderId = p.orderId

    WHERE
        p.photostate = '2'
)) t where yeji>='".$param['globalset']['tiyanmoney']."'
");
        return $deal;
    }

    //未拍
    public function getWeiPai($param){

        $deal = Db::query("SELECT
            count(*) AS count,
            wpcunt
        FROM
        (
        SELECT
            fc.orderId,
            count(fc.orderId) wpcunt
        FROM
            c_customerinfo a
        JOIN c_customerorder b ON a.customerid = b.customerid
        JOIN c_customerorder_servicefc fc ON b.orderId = fc.orderId
        JOIN c_customerphoto p ON b.orderId = p.orderId
        AND fc.currentfc = p.currentfc
        join  
    (select ifnull(sum(yejiprice),0) as yeji,orderId,servicenumber from c_consumptioncontent group by orderId,servicenumber) con 
    on con.orderId=p.orderId and con.servicenumber=p.currentfc

        WHERE
            fcstate <> '1' and con.yeji>".$param['globalset']['tiyanmoney']." and b.blankoutannal <> 1
        AND p.shop_code in ('".$param['shop_code_ms'][0]."','".$param['shop_code_wx'][0]."')
        AND CONCAT(
            fc.orderId,
            '_-_',
            fc.currentfc
        ) NOT IN (
            SELECT
                CONCAT(
                    p.orderId,
                    '_-_',
                    p.currentfc
                )
            FROM
                c_customerinfo a
            JOIN c_customerorder b ON a.customerid = b.customerid
            JOIN c_customerphoto p ON b.orderId = p.orderId
            WHERE
                p.photostate = '2'
        )
        GROUP BY
            fc.orderId
    ) t

GROUP BY
    wpcunt");
return $deal;

    }
    //成交客资量 start:开始时间,end:结束时间
    public function getChengjiaoKeZiLiang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if ($param['shop_code']) {
            $where['o.shop_code'] = ['in',$param['shop_code']];
        }
        if ($param['crm_order_type']) {
            $where['o.crm_order_type'] = (int)$param['crm_order_type'];
        }
        $where['c.is_status'] = 1;
        $where['c.c_from'] = 1;
        $where['c.sales_staff_id']= ['>',0];
        $where['o.payment_money'] = ['>',0];//收款大于0
        $where['o.blankoutannal'] = ['<>',1];//没有作废

        $customerorder = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'c.id=o.crm_customer_id','left')
            ->field("o.orderId,o.targetdate,o.yixiaoshishou,c.sales_group_id,c.sales_staff,o.shop_code")
            ->where($where)
            ->where('o.targetdate','between',[$start,$end])
            ->select();
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //销售在线人 start:开始时间,end:结束时间
    public function getXiaoshouZaixianren($param){
        $start = date('Y-m-d 00:00:00',strtotime($param['start']));
        $end = date('Y-m-d 23:59:59',strtotime($param['end']));
        $where['shop_code'] = ['in',$param['shop_code']];
        $where['is_status'] = 1;
        $where['c_from'] = 1;

        $customerorder = db('crm_c_customerinfo')
            ->where($where)
            ->where('dj_day','between',[$start,$end])
            ->group("sales_staff")
            ->count();
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //二级渠道
    public function getTwoChannel($param){
        $channel = db('crm_s_channel')
            ->where("find_in_set('".$param['shop_code']."',share_shops)")
            ->group("channel_info_name")
            ->column("channel_info_name");
        // echo db()->getLastSql();
        return $channel;
    }
    //实收 总数
    public function getShishou($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        // 收款店面
        if ($param['shop_code']) {
            $where['p.shop_code'] = $param['shop_code'];
        }
        // 款项所属
        if ($param['suoshu']) {
            $where['p.suoshu'] = $param['suoshu'];
        }
        if ($param['crm_order_type']) {
            $where['o.crm_order_type'] = (int)$param['crm_order_type'];
        }
        if ($param['time']) {
        	$timeStart = $param['time']['start'];
        	$timeEnd = $param['time']['end'];
        	$where['p.paymenttime'] = [ 'between',[$timeStart,$timeEnd] ];
        }
        $customerorder = db('c_paymentcontent')->alias('p')
            ->join('c_customerorder o','p.orderId=o.orderId','left')
            ->where($where)
            ->where('p.paymentdate','between',[$start,$end])
            ->where('p.isshishou','实收')
            ->sum("p.payment_money");
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //预约排控
    public function getYuyuePaikongZong($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        //总
        $data = Db::query("select sum(timecount) as num from s_pcdefault_monthset where pctype=0 and pcday between '" . $start . "' and '" . $end . "';");
        return $data[0]['num'];
    }
    //预约排控
    public function getYuyuePaikongYi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        //已
        $data = Db::query("select count(id) as num from c_customerphoto  where isphoto_zd=0 and  photodate between '" . $start . "' and '" . $end . "';");

        return $data[0]['num'];
    }
    //选片排控
    public function getXuanpianPaikongZong($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        //总
        $data = Db::query("select sum(timecount) as num from s_pcdefault_monthset where pctype=1 and  pcday between '" . $start . "' and '" . $end . "';");

        return $data[0]['num'];
    }
    //选片排控
    public function getXuanpianPaikongYi($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        //已
        $data = Db::query("select count(id) as num from c_customersp  where selectday between '" . $start . "' and '" . $end . "';");

        return $data[0]['num'];
    }
    //实时订单
    public function getShishiDingdan($param){
        $num = $param['num'] ? : 5;
        $customerorder = db('c_customerorder')
            ->field("shop_name,orderId,targetdate,total_money,package_name")
            // ->where("orderId is not null")
            ->where("blankoutannal",0)
            ->where("total_money",'>',0)
            ->order("targetdate desc")
            ->limit($num)
            ->select();
        // echo db()->getLastSql();
        return $customerorder;
    }
    //价格
    public function getJiaGe($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        if ($param['shop_code']) {
            $where['o.shop_code'] = ['in',$param['shop_code']];
        }
        $where['id'] = ['<>',0];
        $where['blankoutannal'] = 0;
        // $where['total_money'] = ['>','0'];
        $deal = db('c_customerorder')->alias('o')
            ->where($where)
            ->where('o.targetdate', 'between', [$start,$end])
            ->column('total_money');
            // ->column('bargain_money');
        // echo db()->getLastSql();die;
        return $deal;
    }
    //网付订单
    public function getWangfuDingdan($param) {
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

        $customerorder = db('c_customerorder')->alias('o')
            ->join('crm_c_customerinfo c', 'c.id=o.crm_customer_id','left')//客资信息表
            ->join('crm_s_channel ch', 'ch.channel_name=c.customer_channel','left')//渠道
            ->join('c_paymentcontent p', 'p.orderId=o.orderId','left')
            ->field("o.shop_code,o.orderId,ch.channel_info_name,p.fundname")
            // ->where("o.orderId is not null")
            ->where('o.targetdate', 'between', [$start,$end])
            ->group('o.orderId')
            ->select();
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //订单总额 有效
    public function getDingdanliang($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

        // $customerorder = db('c_customerorder')
        //     ->where("orderyouxiao",1)
        //     ->where('targetdate', 'between', [$start,$end])
        //     ->sum('yixiaoshishou');
        $customerorder = db('c_paymentcontent')
            ->where('paymentdate','between', [$start,$end])
            // ->where('suoshu','in',['前期款','前期补款'])
            ->sum('payment_money');
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //目标 总数
    public function getMubiao($param) {
        if ($param['year']) {
           $where['year'] = $param['year'];
        }
        if ($param['month']) {
           $where['month'] = $param['month'];
        }
        if ($param['goal_name']) {
           $where['goal_name'] = $param['goal_name'];
        }
        if ($param['shop_name']) {
           $where['name'] = $param['shop_name'];
        }
        $where['method'] = "shop";
        $where['status'] = 1;

        $customerorder = db('crm_s_goal')
            ->where($where)
            ->sum('goal_value');
        // echo db()->getLastSql();die;
        return $customerorder;
    }
    //目标 list
    public function getMubiaoList($param){
        if ($param['year']) {
           $where['year'] = $param['year'];
        }
        if ($param['month']) {
           $where['month'] = $param['month'];
        }
        if ($param['goal_name']) {
           $where['goal_name'] = $param['goal_name'];
        }
        if ($param['shop_name']) {
           $where['name'] = $param['shop_name'];
        }
        $where['method'] = "shop";
        $where['status'] = 1;

        $customerorder = db('crm_s_goal')
            ->where($where)
            ->field('goal_value,shop_code')
            ->select();
        // echo db()->getLastSql();
        return $customerorder;
    }
    //业绩
    public function getYeji($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));

        $customerorder = db('c_customerorder')
            ->field("storeconsuitant1,shop_name,sum(yixiaoshishou) as pay")
            ->where('targetdate', 'between', [$start,$end])
            ->group('storeconsuitant1')
            ->order("pay desc")
            ->limit(5)
            ->select();
        // echo db()->getLastSql();
        return $customerorder;
    }
    //上门
    public function getShangMen($param){
        $start = date('Ymd',strtotime($param['start']));
        $end = date('Ymd',strtotime($param['end']));
        $where['c.add_shop_code'] = ['in',$param['shop_code_wx']];
        $where['o.orderId'] = ['exp','is not null'];

        //上门总产值
        $data['zss'] = db('c_paymentcontent')
            ->where('paymentdate', 'between', [$start,$end])
            ->where('isshishou','实收')
            // ->where('cashierman',['in',$staff])
            // ->where('currentfc',1)
            ->where('fundname',['in',['上门订单','上门选片','上门补款','上门退款']])
            ->sum('payment_money');

        //上门订单值
        $data['ddl'] = db('c_customerinfo')->alias('a')
            ->join('c_customerorder b','a.customerid=b.customerid','left')
            ->join('c_paymentcontent pay','b.orderId=pay.orderId','left')
            ->where('pay.paymentdate', 'between', [$start,$end])
            ->where('isshishou','实收')
            ->where('suoshu',['in',['前期款','前期补款','一销退款']])
            ->where('pay.fundname',['in',['上门订单','上门选片','上门补款','上门退款']])
            // ->where('b.storeconsuitant1',['in',$staff])
            ->sum('pay.payment_money');

        // 上门选片量 
        $data['xpl'] = db('c_customerorder')->alias('o')
            ->join('c_customerspgq sp', 'sp.orderId=o.orderId','left')
            ->join('c_paymentcontent p', 'p.orderId=o.orderId','left')
            ->where('p.fundname','上门选片')
            ->where('sp.sptend_day','between',[$start,$end])
            ->count();
        $data['xpl_sql'] = db()->getLastSql();

        //上门拍摄量
        $data['psl'] = db('crm_c_customerinfo')->alias('c')
            ->join('c_customerorder o','c.id=o.crm_customer_id')
            ->join('c_customerphoto photo','photo.orderId = o.orderId')
            ->where('o.orderId','exp','is not null')
            ->where('photo.fuwutype','like','%外拍')
            ->where('photo.photodate','between',[$start,$end])
            ->group('o.orderId')
            ->count();

        return $data;
    }

}