<?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 Bg 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']));
    	if ($param['shop_code']) {
    		$where['o.shop_code'] = ['in',$param['shop_code']];
    	}
    	$where['o.blankoutannal'] = 0;
        $deal = db('c_customerorder')->alias('o')
            ->where($where)
            ->where('o.targetdate', 'between', [$start,$end])
            ->group('o.targetdate,o.shop_code')
            ->field('sum(o.total_money) as total_money,o.targetdate,shop_code,shop_name')
            // ->field('sum(o.bargain_money) as total_money,o.targetdate,shop_code,shop_name')
            ->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']));
        $where['c.shop_code'] = ['in',$param['shop_code']];
        $where['c.is_status'] = 1;
        $where['c.c_from'] = 1;

        $return = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel ch', 'ch.channel_name=c.customer_channel')//渠道
            ->field("c.is_valid,c.dj_day,c.sales_group_id,c.sales_staff,ch.channel_info_name")
            ->where($where)
            ->where('c.dj_day','between',[$start,$end])
            ->select();
        // echo db()->getLastSql();die;
        return $return;
    }
    //成交客资量 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 getOaEducationBackground(){
        $sql = " (leave_date='' or leave_date is null or leave_date > '" . date('Y-m-d') . "') and working_status = 1 ";

        $zong = db('oa_dept_staffinfo', 'db_oa')->where($sql)->count();

        //学历分布
        $xlData = db('oa_dept_staffinfo', 'db_oa')
            ->alias('s')
            ->join('oa_dept_staff_detail detail', 's.id = detail.staffid', 'left')
            ->where($sql)
            ->where('detail.education is not null and detail.education <> ""')
            ->group('detail.education')
            ->column('count(s.id)', 'detail.education');

        return ['zong'=>$zong,'data'=>$xlData];
    }

    //获取性别分布
    public function getOaSex(){
        $sql = " (leave_date='' or leave_date is null or leave_date > '" . date('Y-m-d') . "') and working_status = 1 ";
        $data = db('oa_dept_staffinfo', 'db_oa')
            ->where($sql)
            ->group('sex')
            ->column('count(id)', 'sex');

        $zong = array_sum($data);

        return ['zong'=>$zong,'data'=>$data];
    }

    //获取司龄分布
    public function getOaSiLing()
    {
        $jobSql = " (leave_date='' or leave_date is null or leave_date > '" . date('Y-m-d') . "') and working_status = 1";

        $database = config('db_oa')['database'];

        $sql = "SELECT (CASE
                WHEN a.years IS NULL THEN 0
                WHEN a.years <= 1 THEN 1
                WHEN a.years BETWEEN 1 AND 3 THEN 2
                WHEN a.years > 3 THEN 3
                END) AS name,
                COUNT(*) num FROM
                (SELECT(YEAR (curdate())- YEAR ( formal_date )- 1+ (DATE_FORMAT( formal_date, '%m%d' )<=(DATE_FORMAT( curdate(), '%m%d' )))) as years
                FROM " . $database . ".oa_dept_staffinfo
                where " . $jobSql . " and is_formal = 1) a GROUP BY name";
        $data = db()->query($sql);

        $zong = db('oa_dept_staffinfo', 'db_oa')->where($jobSql)->count();

        //试用期
        $syq = db('oa_dept_staffinfo','db_oa')->where($jobSql." and is_formal = 2")->count();

        return ['zong'=>$zong,'data'=>$data,'syq'=>$syq];
    }

    //获取年龄分布
    public function getOaAge()
    {
        $jobSql = " (leave_date='' or leave_date is null or leave_date > '" . date('Y-m-d') . "') and working_status = 1 ";

        $database = config('db_oa')['database'];

        $sql = "SELECT (CASE
                    WHEN a.age IS NULL THEN 1
                    WHEN a.age BETWEEN 0 AND 24 THEN 2
                    WHEN a.age BETWEEN 25 AND 29 THEN 3
                    WHEN a.age BETWEEN 30 AND 34 THEN 4
                    WHEN a.age >= 35 THEN 5
                    END) AS name,
                    COUNT(*) num FROM
                    (SELECT(YEAR (curdate())- YEAR ( b.birthday )- 1+ (DATE_FORMAT( b.birthday, '%m%d' )<=(DATE_FORMAT( curdate(), '%m%d' )))) as age
                    FROM " . $database . ".oa_dept_staffinfo a
                    LEFT join " . $database . ".oa_dept_staff_detail b
                    on a.id = b.staffid where " . $jobSql . ") a GROUP BY name";
        $data = db()->query($sql);

        $zong = array_sum(array_column($data, 'num'));

        return ['zong'=>$zong,'data'=>$data];
    }

    //获取管理结构分布
    public function getOaManagementStructure()
    {
        $sql = " (leave_date='' or leave_date is null or leave_date > '" . date('Y-m-d') . "') and working_status = 1 ";

        //学历分布
        $data = db('oa_dept_staffinfo', 'db_oa')
            ->alias('s')
            ->join('oa_dept_position_get pg', 's.main_position_code = pg.position_code', 'left')
            ->join('oa_dept_position p', 'pg.position_id = p.id', 'left')
            ->where($sql)
            ->group('p.type')
            ->column('count(s.id)', 'p.type');

        $zong = array_sum($data);

        return ['zong'=>$zong,'data'=>$data];
    }


}