php/app/common/repositories/finance/FinanceRepository.php

<?php
/**
 * @package merchant
 *
 * @author xaboy
 * @day 2020-10-12
 *
 *
 */

namespace app\common\repositories\finance;
// use app\common\dao\article\ArticleDao;
// use app\common\model\article\ArticleContent;
use app\common\enum\CommonEnum;
use app\common\enum\shared\SharedProsperityRewardRecordEnum;
use app\common\repositories\BaseRepository;
use app\common\repositories\user\UserRepository;
use think\Exception;
use think\facade\Db;
use think\facade\Request;
use crmeb\services\SpreadsheetExcelService;

class FinanceRepository extends BaseRepository
{
    /**
     * 订单概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function order_overview($mer_id = 0)
    {
        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();

        if ($mer_id) {
            $where = "mer_id = " . $mer_id . " and paid = 1";
            if (!empty($invalidUidList)) {
                $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
                $where .= $invalidSqlStr;
            }
            $type = input('type', 1);
            $day = input('day', '');
    
            if ($type == 1 and $day != '') {
                $where .= " and DATE(pay_time) = '" . $day . "'";
            }
            if ($type == 2 and $day != '') {
                $date = new \DateTime($day);
                $year = $date->format('Y');
                $month = $date->format('m');
    
                $where .= " and YEAR(pay_time) = " . $year . " AND MONTH(pay_time) = " . $month;
            }
            $xianshangzhifu = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->sum('pay_price');
            $xianshangzhifu_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->sum('pay_price');
            $hongjifenxiaofei = Db::name('store_order')->where($where)->where("pay_type", 5)->sum('pay_price');
    
            $chengjiaomoney = decimal2($xianshangzhifu + $hongjifenxiaofei);
            $list1[] = ['count' => set_price_rtrim($xianshangzhifu), 'name' => '线上支付金额'];
            if (!$mer_id) {
                $list1[] = ['count' => set_price_rtrim($xianshangzhifu_xuni), 'name' => '虚拟支付金额'];
            }
            $list1[] = ['count' => 0, 'name' => '代付金额'];
            $xianshangzhifunum = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->count();
            $xianshangzhifunum_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->count();
            $hongjifenxiaofeinum = Db::name('store_order')->where($where)->where("pay_type", 5)->count();
    
            $chengjiaocount = $xianshangzhifunum + $hongjifenxiaofeinum;
    
            $list2[] = ['count' => $xianshangzhifunum, 'name' => '线上支付订单量'];
            if (!$mer_id) {
                $list2[] = ['count' => $xianshangzhifunum_xuni, 'name' => '虚拟支付订单量'];
            }
            $list2[] = ['count' => 0, 'name' => '代付订单量'];
            $xianshangzhifuren = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->group('uid')->count();
            $xianshangzhifuren_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->group('uid')->count();
            $hongjifenxiaofeiren = Db::name('store_order')->where($where)->where("pay_type", 5)->group('uid')->count();
    
            $chengjiaoren = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1, 5])->group('uid')->count();
            $list3[] = ['count' => set_price_rtrim($xianshangzhifuren), 'name' => '线上支付人数'];
            if (!$mer_id) {
                $list3[] = ['count' => set_price_rtrim($xianshangzhifuren_xuni), 'name' => '虚拟支付人数'];
            }
            $list3[] = ['count' => 0, 'name' => '代付人数'];
    
    
            $xianshangtuikuan = Db::name('store_order')->where($where)->where('status', -1)->sum('pay_price');
    
            $tuikuanmoney = $xianshangtuikuan;
            $list4 = array(
                array('count' => $xianshangtuikuan, 'name' => '线上支付退单'),
                array('count' => 0, 'name' => '代付退单'),
            );
            $xianshangzhifutuinum = Db::name('store_order')->where($where)->where('status', -1)->count();
    
            $tuikuancount = $xianshangzhifutuinum;
            $list5 = array(
                array('count' => set_price_rtrim($xianshangzhifutuinum), 'name' => '线上支付退单量'),
                array('count' => 0, 'name' => '代付退单量'),
            );
            $xianshangzhifutuiremn = Db::name('store_order')->where($where)->where('status', -1)->group('uid')->count();
    
            $tuikuanren = $xianshangzhifutuiremn;
            $list6 = array(
                array('count' => set_price_rtrim($xianshangzhifutuiremn), 'name' => '线上支付退单人数'),
                array('count' => 0, 'name' => '代付退单数'),
            );
    
    
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaomoney), 'name' => '成交金额', 'list' => $list1];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaocount), 'name' => '成交订单量', 'list' => $list2];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaoren), 'name' => '支付人数', 'list' => $list3];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanmoney), 'name' => '退款金额', 'list' => $list4];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuancount), 'name' => '退款订单量', 'list' => $list5];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanren), 'name' => '退款人数', 'list' => $list6];
            return $data;
        } else {
            $where = " paid = 1";
            $type = input('type', 1);
            $day = input('day', '');
            //天
            if ($type == 1 and $day != '') {
                $where .= " and DATE(create_time) = '" . $day . "'";
            }
            //月
            if ($type == 2 and $day != '') {
                $date = new \DateTime($day);
                $year = $date->format('Y');
                $month = $date->format('m');
                $where .= " and YEAR(create_time) = " . $year . " AND MONTH(create_time) = " . $month;
            }
    
            $weekWhere = " paid = 1";
            $dayWhere = " paid = 1";
            // 获取今天的日期和时间
            $today = new \DateTime();
            // 计算一周前的日期
            $oneWeekAgo = clone $today; // 克隆一个对象，避免修改原始对象
            $oneWeekAgo->sub(new \DateInterval('P1W')); // 减去一周
            
            // 格式化日期
            $todayFormatted = $today->format('Y-m-d');
            $oneWeekAgoFormatted = $oneWeekAgo->format('Y-m-d');
    
            //周时间条件
            $weekWhere .= " and DATE(create_time) >= '" . $oneWeekAgoFormatted . "' and DATE(create_time) <= '" . $todayFormatted . "'";
            //今日时间条件
            $dayWhere .= " and DATE(create_time) = '" . $todayFormatted . "'";

            if (!empty($invalidUidList)) {
                $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
                $where .= $invalidSqlStr;
                $weekWhere .= $invalidSqlStr;
                $dayWhere .= $invalidSqlStr;
            }

            // 会员专区订单总数
            //总数
            $mer_idhy = 496;
            $chengjiaomoney = Db::name('store_order')->where($where)->where('mer_id', 496)->count();
            $weekCount = Db::name('store_order')->where($weekWhere)->where('mer_id', 496)->count();
            $todayCount = Db::name('store_order')->where($dayWhere)->where('mer_id', 496)->count();
            $list1[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list1[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
            //精彩生活区订单总数
            //总数
            $mer_idjc = 439;
            $chengjiaocount = Db::name('store_order')->where($where)->where('mer_id', 439)->count();
            $weekCount = Db::name('store_order')->where($weekWhere)->where('mer_id', 439)->count();
            $todayCount = Db::name('store_order')->where($dayWhere)->where('mer_id', 439)->count();
            $list2[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list2[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
            //商贸区订单总数
            //总数
            $chengjiaoren = Db::name('store_order')->where($where)->whereNotIn('mer_id', [496, 439, 491])->count();
            $weekCount = Db::name('store_order')->where($weekWhere)->whereNotIn('mer_id', [496, 439, 491])->count();
            $todayCount = Db::name('store_order')->where($dayWhere)->whereNotIn('mer_id', [496, 439, 491])->count();
            $list3[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list3[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
            //复购区订单总数
            //总数
            $mer_idfg = 491;
            $tuikuanmoney = Db::name('store_order')->where($where)->where('mer_id', 491)->count();
            $weekCount = Db::name('store_order')->where($weekWhere)->where('mer_id', 491)->count();
            $todayCount = Db::name('store_order')->where($dayWhere)->where('mer_id', 491)->count();
            $list4[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list4[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
            //三方平台订单总数
            //总数
    
            //话费
            $whereNew = str_replace("paid", "status", $where);
            $weekWhereNew = str_replace("paid", "status", $weekWhere);
            $dayWhereNew = str_replace("paid", "status", $dayWhere);
            $huafei = Db::name('order_huafei_hf')->where($whereNew)->count();
            $huafei_week = Db::name('order_huafei_hf')->where($weekWhereNew)->count();
            $huafei_today = Db::name('order_huafei_hf')->where($dayWhereNew)->count();
    
    
            //京东
            $whereNew = str_replace(" paid = 1", " status IN (16, 17)", $where);
            $weekWhereNew = str_replace(" paid = 1", " status IN (16, 17)", $weekWhere);
            $dayWhereNew = str_replace(" paid = 1", " status IN (16, 17)", $dayWhere);
            $jd = Db::name('order_jd')->where($whereNew)->count();
            $jd_week = Db::name('order_jd')->where($weekWhereNew)->count();
            $jd_today = Db::name('order_jd')->where($dayWhereNew)->count();
    
            //拼多多
            $whereNew = str_replace(" paid = 1", " pin_order_status IN (0, 2, 3)", $where);
            $weekWhereNew = str_replace(" paid = 1", " pin_order_status IN (0, 2, 3)", $weekWhere);
            $dayWhereNew = str_replace(" paid = 1", " pin_order_status IN (0, 2, 3)", $dayWhere);
            $pdd = Db::name('order_pdd')->where($whereNew)->count();
            $pdd_week = Db::name('order_pdd')->where($weekWhereNew)->count();
            $pdd_today = Db::name('order_pdd')->where($dayWhereNew)->count();
    
            //淘宝
            $whereNew = str_replace(" paid = 1", " tk_status IN (12, 3)", $where);
            $weekWhereNew = str_replace(" paid = 1", " tk_status IN (12, 3)", $weekWhere);
            $dayWhereNew = str_replace(" paid = 1", " tk_status IN (12, 3)", $dayWhere);
            $tb = Db::name('order_tb')->where($whereNew)->count();
            $tb_week = Db::name('order_tb')->where($weekWhereNew)->count();
            $tb_today = Db::name('order_tb')->where($dayWhereNew)->count();
    
            //电影票
            $whereNew = str_replace(" paid = 1", " status IN (1, 4)", $where);
            $weekWhereNew = str_replace(" paid = 1", " status IN (1, 4)", $weekWhere);
            $dayWhereNew = str_replace(" paid = 1", " status IN (1, 4)", $dayWhere);
            $movie = Db::name('order_movie')->where($whereNew)->count();
            $movie_week = Db::name('order_movie')->where($weekWhereNew)->count();
            $movie_today = Db::name('order_movie')->where($dayWhereNew)->count();
    
            $tuikuancount = $huafei+$jd+$pdd+$tb+$movie;
            $weekCount = $huafei_week+$jd_week+$pdd_week+$tb_week+$movie_week;
            $todayCount = $huafei_today+$jd_today+$pdd_today+$tb_today+$movie_today;
            $list5[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list5[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
            //线下订单总数
            //总数
            $tuikuanren = Db::name('order_merchant')->where($where)->count();
            $weekCount = Db::name('order_merchant')->where($weekWhere)->count();
            $todayCount = Db::name('order_merchant')->where($dayWhere)->count();
            $list6[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list6[] = ['count' => $todayCount, 'name' => '日订单总数'];

            // 共富订单统计
            $shared_mer_id = CommonEnum::DESIGN_MERCHANT_ID['SharedProsperity']['code'];
            $sharedMoney = Db::name('store_order')->where($where)->where('mer_id', $shared_mer_id)->count();
            $weekCount = Db::name('store_order')->where($weekWhere)->where('mer_id', $shared_mer_id)->count();
            $todayCount = Db::name('store_order')->where($dayWhere)->where('mer_id', $shared_mer_id)->count();
            $list7[] = ['count' => $weekCount, 'name' => '周订单总数'];
            $list7[] = ['count' => $todayCount, 'name' => '日订单总数'];
    
    
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaomoney), 'name' => '会员专区订单总数', 'mer_id' => $mer_idhy, 'list' => $list1];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaocount), 'name' => '精彩生活区订单总数', 'mer_id' => $mer_idjc, 'list' => $list2];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaoren), 'name' => '商贸区订单总数', 'list' => $list3];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanmoney), 'name' => '复购区订单总数', 'mer_id' => $mer_idfg, 'list' => $list4];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuancount), 'name' => '三方平台订单总数', 'list' => $list5];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanren), 'name' => '线下订单总数', 'list' => $list6];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($sharedMoney), 'name' => '共富订单总数', 'mer_id' => $shared_mer_id, 'list' => $list7];
            return $data;
        }
    }

    /**
     * 用户概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function user_overview($mer_id = 0)
    {
        $where = "1=1 ";

        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
        }
        if ($mer_id) {
            $mer_uid = Db::name('merchant')->where('mer_id', $mer_id)->value('uid');
            $userRepository = app()->make(UserRepository::class);
            $chilid_userids = $userRepository->getSpreadUidBreak($mer_uid);
            $chilid_userids_string = "1";
            if (!empty($chilid_userids)) {
                $chilid_userids_string = implode(',',$chilid_userids);
            }
            $where .= "and uid in ({$chilid_userids_string})";

            $where = "1=1 ";
            if ($mer_id) {
                $mer_uid = Db::name('merchant')->where('mer_id', $mer_id)->value('uid');
                $userRepository = app()->make(UserRepository::class);
                $chilid_userids = $userRepository->getSpreadUidBreak($mer_uid);
                $chilid_userids_string = "1";
                if (!empty($chilid_userids)) {
                    $chilid_userids_string = implode(',',$chilid_userids);
                }
                $where .= "and uid in ({$chilid_userids_string})";
            }
            $type = input('type', 1);
            $day = input('day', '');
    
            if ($type == 1 and $day != '') {
                $where .= " and DATE(create_time) = '" . $day . "'";
            }
            if ($type == 2 and $day != '') {
                $date = new \DateTime($day);
                $year = $date->format('Y');
                $month = $date->format('m');
                $where .= " and YEAR(create_time) = " . $year . " AND MONTH(create_time) = " . $month;
            }
            $dataQuery = Db::query("SELECT user_group,count(uid) count FROM `rrx_user` where {$where} group by user_group");
            $dataQuery = array_column($dataQuery, 'count', 'user_group');
            $shopPartnerCount = Db::query("SELECT user_group,count(uid) count FROM `rrx_user` where mer_id={$mer_id} and is_shop_partner=1 group by user_group");
    
            $maker = Db::query("SELECT CONCAT(`user_group`, '-',`is_show`) as k, count(uid) count FROM `rrx_user` where {$where} group by user_group,is_show");
            $maker = array_column($maker, 'count', 'k');
    
            $userByCodeCount = Db::query("SELECT 'temp' as `temp1`, count(*) count FROM `rrx_user` where {$where} and user_group =4 and is_show = 3");
            $userByCodeCount = array_column($userByCodeCount, 'count', 'temp1');
    
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[1] ?? 0), 'name' => '消费者'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[2] ?? 0), 'name' => '业务员'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[3] ?? 0), 'name' => '业务经理'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[5] ?? 0), 'name' => '初级合伙人'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[6] ?? 0), 'name' => '中级合伙人'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[7] ?? 0), 'name' => '高级合伙人'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[8] ?? 0), 'name' => '董事'];
            $list1 = array(
                array('className' => 'el-icon-s-goods', 'count' => intval($maker['4-2'] ?? 0), 'name' => '集团会员'),//is_show=2
                array('className' => 'el-icon-s-goods', 'count' => intval($maker['4-1'] ?? 0), 'name' => '电商创客'),
                array('className' => 'el-icon-s-goods', 'count' => intval($userByCodeCount['temp'] ?? 0), 'name' => '激活创客'),
            );
    
            if ($mer_id) {
                $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[4] ?? 0), 'name' => '创客'];
            }
            $res['left'][] = $data;
            $res['right'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim(intval($maker['4-2'] ?? 0) + intval($maker['4-1'] ?? 0) + intval($userByCodeCount['temp'] ?? 0)), 'name' => '创客'];
        }else{
            $type = input('type', 1);
            $day = input('day', '');
    
            if ($type == 1 and $day != '') {
                $where .= " and DATE(create_time) = '" . $day . "'";
            }
            if ($type == 2 and $day != '') {
                $date = new \DateTime($day);
                $year = $date->format('Y');
                $month = $date->format('m');
                $where .= " and YEAR(create_time) = " . $year . " AND MONTH(create_time) = " . $month;
            }
            $dataQuery = Db::query("SELECT user_group,count(uid) count FROM `rrx_user` where {$where} group by user_group");
            $dataQuery = array_column($dataQuery, 'count', 'user_group');
    
            $maker = Db::query("SELECT CONCAT(`user_group`, '-',`is_show`) as k, count(uid) count FROM `rrx_user` where {$where} group by user_group,is_show");
            $maker = array_column($maker, 'count', 'k');
    
    
            $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[1] ?? 0), 'name' => '消费者', 'user_group' => 1];
            $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[2] ?? 0), 'name' => '业务员', 'user_group' => 2];
            $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[3] ?? 0), 'name' => '业务经理', 'user_group' => 3];
            $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[4] ?? 0), 'name' => '初级合伙人', 'user_group' => 4];
            $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[5] ?? 0), 'name' => '中级合伙人', 'user_group' => 5];
    
            $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[6] ?? 0), 'name' => '高级合伙人', 'user_group' => 6];
            $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[7] ?? 0), 'name' => '董事', 'user_group' => 7];
            $maker = Db::query("SELECT count(uid) as count FROM `rrx_user` where {$where} and originator_id_old > 0");
            $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($maker[0]['count'] ?? 0), 'name' => '联创', 'user_group' => 0];
            
            $whereTow = str_replace("uid", "user_id", $where);
            $maker = Db::query("SELECT count(user_id) as count FROM `rrx_old_user_stock` where {$whereTow}");
            
            $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($maker[0]['count'] ?? 0), 'name' => '股权', 'user_group' => 0];
            $maker = Db::query("SELECT count(uid) as count FROM `rrx_merchant` where {$where}");
            $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($maker[0]['count'] ?? 0), 'name' => '商户', 'user_group' => 0];

            // 共富会员身份
            $sharedUserDataQuery = Db::query("SELECT partner_level,count(user_id) count FROM `rrx_shared_prosperity_user` group by partner_level");
            $sharedUserDataQuery = array_column($sharedUserDataQuery, 'count', 'partner_level');
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[0] ?? 0), 'name' => '普通用户', 'partner_level' => 0];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[1] ?? 0), 'name' => '一星合伙人', 'partner_level' => 1];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[2] ?? 0), 'name' => '二星合伙人', 'partner_level' => 2];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[3] ?? 0), 'name' => '三星合伙人', 'partner_level' => 3];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[4] ?? 0), 'name' => '钻石合伙人', 'partner_level' => 4];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[5] ?? 0), 'name' => '总裁合伙人', 'partner_level' => 5];
            $data['three'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedUserDataQuery[6] ?? 0), 'name' => '联席主席团', 'partner_level' => 6];

            // 共富百人团、千人团、万人团数据
            $sharedGroupUserDataQuery = Db::query("SELECT group_id,count(user_id) count FROM `rrx_shared_prosperity_group_user` group by group_id");
            $sharedGroupUserDataQuery = array_column($sharedGroupUserDataQuery, 'count', 'group_id');
            $data['four'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedGroupUserDataQuery[1] ?? 0), 'name' => '万人团', 'group_id' => 1];
            $data['four'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedGroupUserDataQuery[2] ?? 0), 'name' => '千人团', 'group_id' => 2];
            $data['four'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($sharedGroupUserDataQuery[3] ?? 0), 'name' => '百人团', 'group_id' => 3];

            $res['left'] = $data;
        }
        return $res;
    }



    /**
     * 会员概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function member_overview($mer_id = 0)
    {
        $where = " 1=1";
        $sWeekWhere = " 1=1";
        $weekWhere = " 1=1";
        $dayWhere = " 1=1";

        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
            $sWeekWhere .= $invalidSqlStr;
            $weekWhere .= $invalidSqlStr;
            $dayWhere .= $invalidSqlStr;
        }

        $currentDate = date('Y-m-d');
        // 上周的开始日期（周一）
        $startDate = date('Y-m-d', strtotime('last Monday', strtotime($currentDate)));
        // 上周的结束日期（周日）
        $endDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));

        // 获取今天的日期和时间
        $today = new \DateTime();
        // 计算一周前的日期
        $oneWeekAgo = clone $today; // 克隆一个对象，避免修改原始对象
        $oneWeekAgo->sub(new \DateInterval('P1W')); // 减去一周
        
        // 格式化日期
        $todayFormatted = $today->format('Y-m-d');
        $oneWeekAgoFormatted = $oneWeekAgo->format('Y-m-d');
        //上周时间条件
        $sWeekWhere .= " and DATE(create_time) >= '" . $startDate . "' and DATE(create_time) <= '" . $endDate . "'";
        //周时间条件
        $weekWhere .= " and DATE(create_time) >= '" . $oneWeekAgoFormatted . "' and DATE(create_time) <= '" . $todayFormatted . "'";
        //今日时间条件
        $dayWhere .= " and DATE(create_time) = '" . $todayFormatted . "'";


        $userCount = Db::name('user')->where($where)->count();
        $userDayCount = Db::name('user')->where($dayWhere)->count();
        $userDjCount = Db::name('user')->where($where)->where('status', 0)->count();

        // 会员专区业绩
        $chengjiaomoney = Db::name('store_order')->where($where)->where('paid', 1)->where('mer_id', 496)->sum('pay_price');
        $sWeekCount = Db::name('store_order')->where($sWeekWhere)->where('paid', 1)->where('mer_id', 496)->sum('pay_price');
        $weekCount = Db::name('store_order')->where($weekWhere)->where('paid', 1)->where('mer_id', 496)->sum('pay_price');
        $todayCount = Db::name('store_order')->where($dayWhere)->where('paid', 1)->where('mer_id', 496)->sum('pay_price');

        $data[] = ['className' => 'el-icon-s-goods', 'count' => $userCount, 'name' => '会员总人数'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $userDayCount, 'name' => '今日新增会员'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $userDjCount, 'name' => '被冻结会员数'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $chengjiaomoney, 'name' => '会员专区总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sWeekCount, 'name' => '会员专区上周总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $weekCount, 'name' => '会员专区本周总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $todayCount, 'name' => '会员专区今日总业绩'];


        // 共富会员概况
        $sWeekWhere = " 1=1";
        $weekWhere = " 1=1";
        $dayWhere = " 1=1";

        // 获取当前 ISO 年份和周数
        $year = $today->format('o'); // ISO-8601 年份
        $week = $today->format('W'); // ISO-8601 周数（周一为周起始）

        // 本周一：setISODate(年份, 周数, 1) 参数 1 表示周一
        $thisMonday = new \DateTime();
        $thisMonday->setISODate($year, $week, 1);

        // 下周一：周数加1
        $nextMonday = new \DateTime();
        $nextMonday->setISODate($year, $week + 1, 1);

        // 上周一：周数减1
        $lastMonday = new \DateTime();
        $lastMonday->setISODate($year, $week - 1, 1);

        //上周时间条件
        $sWeekWhere .= " and DATE(create_time) >= '" . $lastMonday->format('Y-m-d') . "' and DATE(create_time) < '" . $thisMonday->format('Y-m-d') . "'";
        //周时间条件
        $weekWhere .= " and DATE(create_time) >= '" . $thisMonday->format('Y-m-d') . "' and DATE(create_time) < '" . $nextMonday->format('Y-m-d') . "'";
        //今日时间条件
        $dayWhere .= " and DATE(create_time) = '" . $todayFormatted . "'";

        // 测试账号
        $testUids = Db::name('user')->where('test_user',  '1')->column('uid');

        $sharedUserCount = Db::name('shared_prosperity_user')->count(); //共富会员总人数
        $sharedUserDayCount = Db::name('shared_prosperity_user')->where($dayWhere)->count(); //共富今日新增会员
        $sharedUserPvCount = Db::name('shared_prosperity_reward_record')
            ->where(['type' => SharedProsperityRewardRecordEnum::TYPE['PV']['code'], 'status' => SharedProsperityRewardRecordEnum::STATUS['VALID']['code'], 'pm' => SharedProsperityRewardRecordEnum::PM['INCOME']['code']])
            ->whereNotIn('user_id',$testUids)
            ->sum('number'); // 共富会员专区总业绩
        $sharedUserLastWeekPv = Db::name('shared_prosperity_reward_record')
            ->where($sWeekWhere)
            ->where(['type' => SharedProsperityRewardRecordEnum::TYPE['PV']['code'], 'status' => SharedProsperityRewardRecordEnum::STATUS['VALID']['code'], 'pm' => SharedProsperityRewardRecordEnum::PM['INCOME']['code']])
            ->whereNotIn('user_id',$testUids)
            ->sum('number'); // 共富会员专区上周总业绩
        $sharedUserWeekPv = Db::name('shared_prosperity_reward_record')
            ->where($weekWhere)
            ->where(['type' => SharedProsperityRewardRecordEnum::TYPE['PV']['code'], 'status' => SharedProsperityRewardRecordEnum::STATUS['VALID']['code'], 'pm' => SharedProsperityRewardRecordEnum::PM['INCOME']['code']])
            ->whereNotIn('user_id',$testUids)
            ->sum('number'); // 共富会员专区本周总业绩
        $sharedUserTodayPv = Db::name('shared_prosperity_reward_record')
            ->where($dayWhere)
            ->where(['type' => SharedProsperityRewardRecordEnum::TYPE['PV']['code'], 'status' => SharedProsperityRewardRecordEnum::STATUS['VALID']['code'], 'pm' => SharedProsperityRewardRecordEnum::PM['INCOME']['code']])
            ->whereNotIn('user_id',$testUids)
            ->sum('number'); // 共富会员专区今日总业绩

        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserCount, 'name' => '共富会员总人数'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserDayCount, 'name' => '共富今日新增会员'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserPvCount, 'name' => '共富专区总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserLastWeekPv, 'name' => '共富专区上周总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserWeekPv, 'name' => '共富专区本周总业绩'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $sharedUserTodayPv, 'name' => '共富专区今日总业绩'];

        $res['left'][] = $data;
        return $res;
    }




    /**
     * pv概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function pv_overview($mer_id = 0)
    {
        $where = "1=1 ";
        $sWeekWhere = " 1=1";
        $weekWhere = " 1=1";
        $dayWhere = " 1=1";

        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
            $sWeekWhere .= $invalidSqlStr;
            $weekWhere .= $invalidSqlStr;
            $dayWhere .= $invalidSqlStr;
        }
       
        $currentDate = date('Y-m-d');
        // 上周的开始日期（周一）
        $startDate = date('Y-m-d', strtotime('last Monday', strtotime($currentDate)));
        // 上周的结束日期（周日）
        $endDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));

        // 获取今天的日期和时间
        $today = new \DateTime();
        // 计算一周前的日期
        $oneWeekAgo = clone $today; // 克隆一个对象，避免修改原始对象
        $oneWeekAgo->sub(new \DateInterval('P1W')); // 减去一周
        
        // 格式化日期
        $todayFormatted = $today->format('Y-m-d');
        $oneWeekAgoFormatted = $oneWeekAgo->format('Y-m-d');
        //上周时间条件
        $sWeekWhere .= " and DATE(addtime) >= '" . $startDate . "' and DATE(addtime) <= '" . $endDate . "'";
        //周时间条件
        $weekWhere .= " and DATE(addtime) >= '" . $oneWeekAgoFormatted . "' and DATE(addtime) <= '" . $todayFormatted . "'";
        //今日时间条件
        $dayWhere .= " and DATE(addtime) = '" . $todayFormatted . "'";


        $pvCount = Db::name('user_pv_log')->where($where)->sum('pv');
        // $pvCunCount = Db::name('user_pv_log')->where($where)->where('mer_id', 496)->sum('pv');
        $weekCount = Db::name('user_pv_log')->where($weekWhere)->sum('pv');
        $todayCount = Db::name('user_pv_log')->where($dayWhere)->sum('pv');

        $data[] = ['className' => 'el-icon-s-goods', 'count' => $pvCount, 'name' => '平台总PV'];
        // $data[] = ['className' => 'el-icon-s-goods', 'count' => $pvCunCount, 'name' => '会员存量总PV'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $weekCount, 'name' => '本周新增PV总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $todayCount, 'name' => '今日新增PV总量'];
    
        $res['left'][] = $data;
        return $res;
    }




    /**
     * vr概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function vr_overview($mer_id = 0)
    {
        $where = "1=1 ";
        $sWeekWhere = " 1=1";
        $weekWhere = " 1=1";
        $dayWhere = " 1=1";

        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
            $sWeekWhere .= $invalidSqlStr;
            $weekWhere .= $invalidSqlStr;
            $dayWhere .= $invalidSqlStr;
        }

        $currentDate = date('Y-m-d');
        // 上周的开始日期（周一）
        $startDate = date('Y-m-d', strtotime('last Monday', strtotime($currentDate)));
        // 上周的结束日期（周日）
        $endDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));

        // 获取今天的日期和时间
        $today = new \DateTime();
        // 计算一周前的日期
        $oneWeekAgo = clone $today; // 克隆一个对象，避免修改原始对象
        $oneWeekAgo->sub(new \DateInterval('P1W')); // 减去一周
        
        // 格式化日期
        $todayFormatted = $today->format('Y-m-d');
        $oneWeekAgoFormatted = $oneWeekAgo->format('Y-m-d');
        //上周时间条件
        $sWeekWhere .= " and DATE(addtime) >= '" . $startDate . "' and DATE(addtime) <= '" . $endDate . "'";
        //周时间条件
        $weekWhere .= " and DATE(addtime) >= '" . $oneWeekAgoFormatted . "' and DATE(addtime) <= '" . $todayFormatted . "'";
        //今日时间条件
        $dayWhere .= " and DATE(addtime) = '" . $todayFormatted . "'";

        $vrCount = Db::name('user_sign_vr')->where($where)->sum('number');
        $vrUserCount = Db::name('user')->where($where)->sum('vr');
        $vrWeekCount = Db::name('user_sign_vr')->where($weekWhere)->where('type', 1)->sum('number');
        $vrDayCount = Db::name('user_sign_vr')->where($dayWhere)->where('type', 1)->sum('number');
        $vrXhCount = Db::name('user_sign_vr')->where($where)->where('type', 2)->sum('number');
        $vrWeekXhCount = Db::name('user_sign_vr')->where($weekWhere)->where('type', 2)->sum('number');
        $vrDayXhCount = Db::name('user_sign_vr')->where($dayWhere)->where('type', 2)->sum('number');

        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrCount, 'name' => '平台总VR'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrUserCount, 'name' => '会员存量总VR'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrWeekCount, 'name' => '本周新增VR总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrDayCount, 'name' => '今日新增VR总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrXhCount, 'name' => '会员消耗总VR'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrWeekXhCount, 'name' => '本周消耗VR总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $vrDayXhCount, 'name' => '今日消耗VR总量'];
   
        $res['left'][] = $data;
        return $res;
    }



    /**
     * 积分概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function jifen_overview($mer_id = 0)
    {
        $where = "1=1 ";
        $sWeekWhere = " 1=1";
        $weekWhere = " 1=1";
        $dayWhere = " 1=1";

        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
            $sWeekWhere .= $invalidSqlStr;
            $weekWhere .= $invalidSqlStr;
            $dayWhere .= $invalidSqlStr;
        }
       
        $currentDate = date('Y-m-d');
        // 上周的开始日期（周一）
        $startDate = date('Y-m-d', strtotime('last Monday', strtotime($currentDate)));
        // 上周的结束日期（周日）
        $endDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));

        // 获取今天的日期和时间
        $today = new \DateTime();
        // 计算一周前的日期
        $oneWeekAgo = clone $today; // 克隆一个对象，避免修改原始对象
        $oneWeekAgo->sub(new \DateInterval('P1W')); // 减去一周
        
        // 格式化日期
        $todayFormatted = $today->format('Y-m-d');
        $oneWeekAgoFormatted = $oneWeekAgo->format('Y-m-d');
        //上周时间条件
        $sWeekWhere .= " and DATE(addtime) >= '" . $startDate . "' and DATE(addtime) <= '" . $endDate . "'";
        //周时间条件
        $weekWhere .= " and DATE(addtime) >= '" . $oneWeekAgoFormatted . "' and DATE(addtime) <= '" . $todayFormatted . "'";
        //今日时间条件
        $dayWhere .= " and DATE(addtime) = '" . $todayFormatted . "'";

        $jfCount = Db::name('user_sign_score')->where($where)->sum('reward_socre');
        $jfUserCount = Db::name('user')->where($where)->sum('score');
        $jfWeekCount = Db::name('user_sign_score')->where($weekWhere)->where('pm', 1)->sum('reward_socre');
        $jfDayCount = Db::name('user_sign_score')->where($dayWhere)->where('pm', 1)->sum('reward_socre');
        $jfXhCount = Db::name('user_sign_score')->where($where)->where('pm', 2)->sum('reward_socre');
        $jfWeekXhCount = Db::name('user_sign_score')->where($weekWhere)->where('pm', 2)->sum('reward_socre');
        $jfDayXhCount = Db::name('user_sign_score')->where($dayWhere)->where('pm', 2)->sum('reward_socre');

        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfCount, 'name' => '平台总积分'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfUserCount, 'name' => '会员存量总积分'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfWeekCount, 'name' => '本周新增积分总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfDayCount, 'name' => '今日新增积分总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfXhCount, 'name' => '会员消耗总积分'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfWeekXhCount, 'name' => '本周消耗积分总量'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => $jfDayXhCount, 'name' => '今日消耗积分总量'];

        $res['left'][] = $data;
        return $res;
    }



    public function jieyu($type, $day, $mer_id = 0)
    {
        if ($mer_id) {
            $uid = Db::name('merchant')->where('mer_id', $mer_id)->value('uid');
            $where = "mer_id = " . $mer_id . " and paid = 1";
            $where_order = "mer_id = " . $mer_id . " and paid = 1 and douhuomall=0";
            $bill_where = "mer_id = " . $mer_id . " and commission_type in (7, 17, 19) and status =1 and source=0 and uid={$uid}";
            $yanglao_where = "mer_id = " . $mer_id . " and commission_type =5 and status =1 and source=0 and uid={$uid}";
        } else {
            $where = "paid = 1";
            $where_order = "paid = 1 and (douhuomall=1 or mer_id=496)";
            $bill_where = "commission_type !=5 and source=0 ";
            $yanglao_where = " commission_type =5 and source=0 ";
        }
        // 需要过滤掉的用户
        /** @var UserRepository $userRepository */
        $userRepository = app()->make(UserRepository::class);
        $invalidUidList = $userRepository->getInvalidUidList();
        if (!empty($invalidUidList)) {
            $invalidSqlStr = ' AND uid NOT IN (' . implode(',', $invalidUidList) . ')';
            $where .= $invalidSqlStr;
            $where_order .= $invalidSqlStr;
            $bill_where .= $invalidSqlStr;
            $yanglao_where .= $invalidSqlStr;
        }

        if ($type == 1 and $day != '') {
            $lastDay = date('Y-m-d', strtotime($day));
            $day = $lastDay;
            $where .= " and DATE(pay_time) = '" . $day . "'";
            $where_order .= " and DATE(hf_clear_time) = '" . $day . "'";
            $ruzhang = Db::name('enterprise_platform_income_detail')
                ->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 1);
                }, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->whereIn('deal_type', [6, 7]);
                })
                ->where("deal_time like '" . $lastDay . "%'")
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('money');

            $tixinan = Db::name('enterprise_platform_income_detail')
                ->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 2);
                }, function ($query) use ($mer_id) {
                    $query->where('deal_type', 5);
                })
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('money');

            $my_money = Db::name('enterprise_platform_income_detail')
                ->alias('epid')
                ->leftJoin('store_order store_order', 'store_order.order_id=epid.order_id')
                ->where("store_order.douhuomall", 0)
                ->where("epid.deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('epid.mer_id', $mer_id);
                })
                ->where('epid.deal_type', 1)
                ->where("epid.deal_time like '" . $lastDay . "%'")
                ->whereNotIn('epid.uid', [766, 783, 6])
                ->sum('epid.money');
            $shouyi = Db::name('user_bill')
                ->where($bill_where)
                ->whereBetween('take_time', [strtotime($day), strtotime($day) + 24 * 3600])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');//
            $yanglao = Db::name('user_bill')
                ->where($yanglao_where)
                ->whereBetween('take_time', [strtotime($day), strtotime($day) + 24 * 3600])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');//养老金

            $jieyu = Db::name('enterprise_day_bill')
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id);
                })
                ->where('day', $lastDay)->value('balance');


            $integral = Db::name('user_sign_score')
                ->where('pm', 1)
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('reward_socre');

            $gongxian = Db::name('user_sign_gongxian')
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');

            $jingdou = Db::name('user_sign_jingdou')
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');

            $hongbao = Db::name('user_sign_hongbao')
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');

            $fugou = Db::name('user_sign_fugou')
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');

            $vr = Db::name('user_sign_vr')
                ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->whereNotIn('uid', [766, 783, 6])
                ->sum('number');
            
            $suoke = Db::name('user_bill')
            ->where('commission_type', 7)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('number');

            $pv = Db::name('user_pv_log')
            ->whereBetween('settlement_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('pv');

            $extract_count = Db::name('user_extract')
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('extract_price');

            $extract_wancheng_count = Db::name('user_extract')->where('status', 1)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('extract_price');

            $extract_shenhezhong_count = Db::name('user_extract')->where('status', 0)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('extract_price');

            $hehuo = Db::name('user_bill')->where('commission_type', 11)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('number');

            $lianchuang = Db::name('user_bill')->where('commission_type', 12)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('number');

            $guquan = Db::name('user_bill')->where('commission_type', 13)
            ->whereBetween('create_time', [date('Y-m-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
            ->whereNotIn('uid', [766, 783, 6])
            ->sum('number');

        } else if ($type == 2 and $day != '') {
            $date = new \DateTime($day);
            $year = $date->format('Y');
            $month = $date->format('m');

            $where .= " and YEAR(pay_time) = " . $year . " AND MONTH(pay_time) = " . $month;
            $where_order .= " and YEAR(hf_clear_time) = " . $year . " AND MONTH(hf_clear_time) = " . $month;

            $yue = date('Y-m', strtotime($day));
            $lastDay = $date->format('Y-m-t');
            $ruzhang = Db::name('enterprise_platform_income_detail')
                ->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 1);
                }, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->whereIn('deal_type', [6, 7]);
                })
                ->where("deal_time like '" . $yue . "%'")
                ->sum('money');

            $tixinan = Db::name('enterprise_platform_income_detail')
                ->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 2);
                }, function ($query) use ($mer_id) {
                    $query->where('deal_type', 5);
                })
                ->sum('money');
            $my_money = Db::name('enterprise_platform_income_detail')
                ->alias('epid')
                ->leftJoin('store_order store_order', 'store_order.order_id=epid.order_id')
                ->where("store_order.douhuomall", 0)
                ->where("epid.deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('epid.mer_id', $mer_id);
                })
                ->where('epid.deal_type', 1)
                ->where("epid.deal_time like '" . $yue . "%'")
                ->sum('epid.money');

            $shouyi = Db::name('user_bill')
                ->where($bill_where)
                ->whereBetween('take_time', [strtotime(date('Y-m-01 00:00:00', strtotime($year))), strtotime($lastDay) + 24 * 3600])
                ->sum('number');//
            $yanglao = Db::name('user_bill')
                ->where($yanglao_where)
                ->whereBetween('take_time', [strtotime(date('Y-m-01 00:00:00', strtotime($year))), strtotime($lastDay) + 24 * 3600])
                ->sum('number');//养老金
            $jieyu = Db::name('enterprise_day_bill')
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id);
                })
                ->where('day', $lastDay)->value('balance');
            $integral = Db::name('user_sign_score')
                ->where('pm', 1)
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('reward_socre');

            $gongxian = Db::name('user_sign_gongxian')
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('number');

            $jingdou = Db::name('user_sign_jingdou')
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('number');

            $hongbao = Db::name('user_sign_hongbao')
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('number');

            $fugou = Db::name('user_sign_fugou')
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('number');

            $vr = Db::name('user_sign_vr')
                ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
                ->sum('number');

            $suoke = Db::name('user_bill')
            ->where('commission_type', 7)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('number');

            $pv = Db::name('user_pv_log')
            ->whereBetween('settlement_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('pv');

            $extract_count = Db::name('user_extract')
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('extract_price');

            $extract_wancheng_count = Db::name('user_extract')->where('status', 1)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('extract_price');

            $extract_shenhezhong_count = Db::name('user_extract')->where('status', 0)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('extract_price');

            $hehuo = Db::name('user_bill')->where('commission_type', 11)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('number');

            $lianchuang = Db::name('user_bill')->where('commission_type', 12)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('number');

            $guquan = Db::name('user_bill')->where('commission_type', 13)
            ->whereBetween('create_time', [date('Y-m-01 00:00:00', strtotime($year)), date('Y-m-d H:i:s', strtotime($lastDay) + 24 * 3600)])
            ->sum('number');

        } else {
            $ruzhang = Db::name('enterprise_platform_income_detail')->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 1);
                }, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->whereIn('deal_type', [6, 7]);
                })
                ->sum('money');

            $tixinan = Db::name('enterprise_platform_income_detail')
                ->where("deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('mer_id', $mer_id)
                        ->where('deal_type', 2);
                }, function ($query) use ($mer_id) {
                    $query->where('deal_type', 5);
                })
                ->sum('money');
            $my_money = Db::name('enterprise_platform_income_detail')
                ->alias('epid')
                ->leftJoin('store_order store_order', 'store_order.order_id=epid.order_id')
                ->where("store_order.douhuomall", 0)
                ->where("epid.deal_time <'" . date('Y-m-d H:i:s') . "'")
                ->when($mer_id, function ($query) use ($mer_id) {
                    $query->where('epid.mer_id', $mer_id)
                        ->where('epid.deal_type', 1);
                }, function ($query) use ($mer_id) {
                    $query->where('epid.deal_type', 6);
                })
                ->sum('epid.money');
            $shouyi = Db::name('user_bill')
                ->where($bill_where)
                ->sum('number');
            //养老金
            $yanglao = Db::name('user_bill')
                ->where($yanglao_where)
                ->sum('number');//养老金
            $jieyu = Db::name('enterprise_day_bill')
                ->where('mer_id', $mer_id)
                ->order('day desc')->value('balance');
            $integral = Db::name('user_sign_score')
                ->where('pm', 1)
                ->sum('reward_socre');

            $gongxian = Db::name('user_sign_gongxian')
            ->sum('number');

            $jingdou = Db::name('user_sign_jingdou')
            ->sum('number');

            $hongbao = Db::name('user_sign_hongbao')
            ->sum('number');

            $fugou = Db::name('user_sign_fugou')
            ->sum('number');

            $vr = Db::name('user_sign_vr')
            ->sum('number');

            $suoke = Db::name('user_bill')
            ->where('commission_type', 7)
            ->sum('number');

            $pv = Db::name('user_pv_log')
            ->sum('pv');

            $extract_count = Db::name('user_extract')
            ->sum('extract_price');

            $extract_wancheng_count = Db::name('user_extract')->where('status', 1)
            ->sum('extract_price');

            $extract_shenhezhong_count = Db::name('user_extract')->where('status', 0)
            ->sum('extract_price');

            $hehuo = Db::name('user_bill')->where('commission_type', 11)
            ->sum('number');

            $lianchuang = Db::name('user_bill')->where('commission_type', 12)
            ->sum('number');

            $guquan = Db::name('user_bill')->where('commission_type', 13)
            ->sum('number');
        }


        $yingyemoney = Db::name('store_order')->where($where)->whereNotIn('uid', [766, 783, 6])->sum('pay_price');
        $hf_fee_amt = Db::name('store_order')->where($where_order)->whereNotIn('uid', [766, 783, 6])->sum('hf_fee_amt');//汇付手续费

        return compact('yingyemoney', 'hf_fee_amt', 'my_money', 'tixinan', 'ruzhang', 'shouyi', 'jieyu', 
        'integral', 'yanglao', 'gongxian', 'jingdou', 'hongbao', 'fugou', 'vr', 'suoke', 'pv', 'extract_count', 'extract_wancheng_count', 'extract_shenhezhong_count', 
        'hehuo', 'lianchuang', 'guquan');
    }

    /**
     * 用户账单
     * @return mixed
     */
    public function user_bill($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['commission_type', 'uid', 'status', 'phone', 'user_group', 'status']);

        $data = [];
        //商户ID
        $query = Db::name('user_bill')
            ->alias('ub')
            ->leftJoin('user u', 'u.uid=ub.uid')
            ->where('u.test_user', '<>', 1)
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('ub.mer_id', $mer_id);
            })
            ->when(isset($params['commission_type']) && $params['commission_type'] == 5, function ($query) use ($params) {
                // 养老金
                $query->where('ub.commission_type', 5);
            }, function ($query) use ($params) {
                // 非养老金
                $query->whereIn('ub.commission_type', [3, 7, 8, 10, 11, 12, 13, 20, 21]);
            })
            ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
                $query->where('ub.uid', $params['uid']);
            })
            ->when(isset($params['commission_type']) && $params['commission_type'] != '', function ($query) use ($params) {
                $query->where('ub.commission_type', $params['commission_type']);
            })
            ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
                $query->where('ub.status', $params['status']);
            })
            ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
                $query->whereLike('u.phone', '%' . $params['phone'] . '%');
            })
            ->when(isset($params['user_group']) && $params['user_group'] != '', function ($query) use ($params) {
                $query->where('u.user_group', $params['user_group']);
            });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;


        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            ini_set('memory_limit', '-1');
            set_time_limit(0);
            $date['list'] = $this->date_where($query, $where['date'], 'ub.create_time')
            ->field('ub.bill_id,ub.title,ub.number,ub.mark,ub.take_time,ub.commission_type,u.uid,u.phone,u.nickname,u.user_group,ub.order_sn,ub.take_time,ub.status,ub.create_time')
            ->order('ub.bill_id', 'desc')
            ->select();

            if (isset($params['commission_type']) && $params['commission_type'] == 5) {
                $title = '养老金账单';
                $arr = [];
                foreach ($date['list'] as $k=>$item){
                    switch ($item['status']) {
                        case '1':
                            $item['status'] = '有效';
                            break;
                        case '-1':
                            $item['status'] = '已失效';
                            break;
                        case '0':
                            $item['status'] = '待确定';
                            break;
                    }
                    switch ($item['user_group']) {
                        case '1':
                            $item['user_group'] = '消费者';
                            break;
                        case '2':
                            $item['user_group'] = '业务员';
                            break;
                        case '3':
                            $item['user_group'] = '业务经理';
                            break;
                        case '4':
                            $item['user_group'] = '初级合伙人';
                            break;
                        case '5':
                            $item['user_group'] = '中级合伙人';
                            break;
                        case '6':
                            $item['user_group'] = '高级合伙人';
                            break;
                        case '7':
                            $item['user_group'] = '董事';
                            break;
                    }
                    $arr[] = array(
                        $item['bill_id'],
                        $item['create_time'],
                        $item['nickname'],
                        $item['phone'],
                        $item['user_group'],
                        $item['number'],
                        $item['status'],
                        $item['order_sn'],
                        $item['mark']
                    );
                }
                return $this->getStatisticsExcel([
                    'ID','时间','用户昵称','手机号','当前身份','金额','状态','关联订单','说名',
                ], $arr, $title);
            } else {
                $title = '佣金账单';
                $arr = [];
                foreach ($date['list'] as $k=>$item){
                    switch ($item['status']) {
                        case '1':
                            $item['status'] = '有效';
                            break;
                        case '-1':
                            $item['status'] = '已失效';
                            break;
                        case '0':
                            $item['status'] = '待确定';
                            break;
                    }
                    switch ($item['user_group']) {
                        case '1':
                            $item['user_group'] = '消费者';
                            break;
                        case '2':
                            $item['user_group'] = '业务员';
                            break;
                        case '3':
                            $item['user_group'] = '业务经理';
                            break;
                        case '4':
                            $item['user_group'] = '初级合伙人';
                            break;
                        case '5':
                            $item['user_group'] = '中级合伙人';
                            break;
                        case '6':
                            $item['user_group'] = '高级合伙人';
                            break;
                        case '7':
                            $item['user_group'] = '董事';
                            break;
                    }
                    $arr[] = array(
                        $item['bill_id'],
                        $item['create_time'],
                        $item['nickname'],
                        $item['phone'],
                        $item['user_group'],
                        $item['number'],
                        $item['status'],
                        $item['order_sn'],
                        $item['mark']
                    );
                }
                return $this->getStatisticsExcel([
                    'ID','时间','用户昵称','手机号','当前身份','金额','状态','关联订单','说名',
                ], $arr, $title);
            }
        }


        $data['list'] = $this->date_where($query, $where['date'], 'ub.create_time')
            ->field('ub.bill_id,ub.title,ub.number,ub.mark,ub.take_time,ub.commission_type,u.uid,u.phone,u.nickname,u.user_group,ub.order_sn,ub.take_time,ub.status,ub.create_time')
            ->order('ub.bill_id', 'desc')
            ->page($page, $limit)
            ->select()
            ->each(function ($item) {
                if($item['take_time'])
                    $item['take_time'] = date('Y-m-d H:i:s', $item['take_time']);
                return $item;
            });
        $data['count'] = $query->count();
        //应用时间筛选条件
        $query1 = $this->date_where($query1, $where['date'], 'ub.create_time');
        $statistics = $query1->field('sum(ub.number) money,ub.commission_type')->group('ub.commission_type')->select()->toArray();
        $statistics = array_column($statistics, 'money', 'commission_type');
        //应用时间筛选条件
        $query3 = $this->date_where($query3, $where['date'], 'ub.create_time');
        $statistics_tow = $query3->field('sum(ub.number) money,ub.status')->group('ub.status')->select()->toArray();
        $statistics_tow = array_column($statistics_tow, 'money', 'status');
        //佣金类型 1代理费 2 消费佣金 3直推奖√ 4辖区佣金\r\n5 养老金√ 6 广告费 7 跨店奖励√ 8平台奖励 9渠道商\r\n10 推荐创客收益√ 11分红奖金√ 12区县代区域收益√ 13消费循环佣金  14-推荐代理收益√ 15邀请小区团长升级√ 16大V经理分享奖√ 17创客合伙人√ 18积分奖励√ 19创客补贴√' 20省级代理区域收益√
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim(array_sum($statistics)), 'name' => '佣金奖励总额'];
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics_tow[1] ?? 0), 'name' => '已结算佣金'];
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics_tow[0] ?? 0), 'name' => '未结算佣金'];
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics_tow[-1] ?? 0), 'name' => '已失效佣金'];
        //应用时间筛选条件
        $transition_vr_query = Db::name('user_transition_vr')->where('type', 1);
        $transition_vr_query = $this->date_where($transition_vr_query, $where['date'], 'create_time', '-', 2);//type为2时，才能根据时间戳来筛选数据。默认是处理Y-m-d H:i:s字符串
        $transition_vr = $transition_vr_query->sum('number');
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($transition_vr), 'name' => '佣金转VR'];
        //应用时间筛选条件
        $user_extra_money_query = Db::name('user_extract')->where('status', 1);
        $user_extra_money_query = $this->date_where($user_extra_money_query, $where['date'], 'create_time');
        $user_extra_money = $user_extra_money_query->sum('extract_price');
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($user_extra_money), 'name' => '已提现佣金'];
        $data['statistics'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($user_extra_money), 'name' => '已提现金额'];

        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[3] ?? 0), 'name' => '直推奖'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[16] ?? 0), 'name' => '大V经理分享奖'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[14] ?? 0), 'name' => '推荐代理商收益'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[10] ?? 0), 'name' => '推荐创客收益'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[12] ?? 0), 'name' => '区县代区域收益'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[20] ?? 0), 'name' => '省级代理区域收益'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[15] ?? 0), 'name' => '邀请小区团长升级奖'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[7] ?? 0), 'name' => '跨店奖励'];
        // $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[19] ?? 0), 'name' => '创客实际补贴'];

        $statistics = $query2->field('sum(ub.number) money,ub.status')->group('ub.status')->select()->toArray();
        $statistics = array_column($statistics, 'money', 'status');
        $data['statistics']['yanglao'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim(array_sum($statistics)), 'name' => '养老金总额'];
        $data['statistics']['yanglao'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[1] ?? 0), 'name' => '已结算养老金'];
        $data['statistics']['yanglao'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[0] ?? 0), 'name' => '未结算养老金'];
        $data['statistics']['yanglao'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($statistics[-1] ?? 0), 'name' => '已失效养老金'];
        $gzc_number = $query4->where('ub.gzc', 1)->sum('ub.number');
        $data['statistics']['yanglao'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($gzc_number ?? 0), 'name' => '已入账养老金'];
        return $data;
    }

    /**
     * 用户账单
     * @return mixed
     */
    public function user_bill_maker($mer_id = 0, $uid = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['commission_type', 'uid', 'status', 'phone', 'user_group', 'status']);

        $data = [];
        //商户ID
        $query = Db::name('user_bill')
            ->alias('ub')
            ->leftJoin('user u', 'u.uid=ub.uid')
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('ub.mer_id', $mer_id);
            })
            ->when($uid, function ($query) use ($uid) {
                $query->where('ub.uid', $uid);
            })
            ->when(isset($params['commission_type']) && $params['commission_type'] == 5, function ($query) use ($params) {
                // 养老金
                $query->where('ub.commission_type', 5);
            }, function ($query) use ($params) {
                // 非养老金
                $query->whereIn('ub.commission_type', [3, 16, 14, 10, 12, 20, 15, 7, 19]);
            })
            ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
                $query->where('ub.uid', $params['uid']);
            })
            ->when(isset($params['commission_type']) && $params['commission_type'] != '', function ($query) use ($params) {
                $query->where('ub.commission_type', $params['commission_type']);
            })
            ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
                $query->where('ub.status', $params['status']);
            })
            ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
                $query->whereLike('u.phone', '%' . $params['phone'] . '%');
            })
            ->when(isset($params['user_group']) && $params['user_group'] != '', function ($query) use ($params) {
                $query->where('u.user_group', $params['user_group']);
            });
        $query1 = clone $query;
        $query2 = clone $query;
        $data['list'] = $this->date_where($query, $where['date'], 'ub.create_time')
            ->field('ub.bill_id,ub.title,sum(ub.number) as number,ub.take_time,ub.commission_type,u.uid,u.phone,u.nickname,u.user_group,ub.order_sn,ub.take_time,ub.status,ub.create_time')
            ->when(!$uid, function ($query) use ($uid) {
                $query->group('ub.uid');
            })
            ->when($uid, function ($query) use ($uid) {
                $query->field('FROM_UNIXTIME( ub.take_time, "%Y%m%d" ) AS task_time_group, ub.bill_id,ub.title,sum(ub.number) as number,ub.take_time,ub.commission_type,u.uid,u.phone,u.nickname,u.user_group,ub.order_sn,ub.take_time,ub.status,ub.create_time');
                $query->group('task_time_group');
            })
            ->order('ub.bill_id', 'desc')
            ->page($page, $limit)
            ->select()
            ->each(function ($item) {
                if($item['take_time'])
                    $item['take_time'] = date('Y-m-d H:i:s', $item['take_time']);
                return $item;
            });
        $statistics = $query1->field('sum(ub.number) money,ub.commission_type')->group('ub.commission_type')->select()->toArray();
        $statistics = array_column($statistics, 'money', 'commission_type');
        //佣金类型 1代理费 2 消费佣金 3直推奖√ 4辖区佣金\r\n5 养老金√ 6 广告费 7 跨店奖励√ 8平台奖励 9渠道商\r\n10 推荐创客收益√ 11分红奖金√ 12区县代区域收益√ 13消费循环佣金  14-推荐代理收益√ 15邀请小区团长升级√ 16大V经理分享奖√ 17创客合伙人√ 18积分奖励√ 19创客补贴√' 20省级代理区域收益√
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim(array_sum($statistics)), 'name' => '创客合伙人佣金总额'];
        return $data;
    }


    /**
     * 积分统计
     * @return mixed
     */
    public function integral_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        if($params['status'] == 99){
            unset($params['status']);
            $params['pm'] = 2;
        }

        $date = [];
        //商户ID
        $query = Db::name('user_sign_score')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id=uss.order_id')
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        ->when(isset($params['pm']) && $params['pm'] != '', function ($query) use ($params) {
            $query->where('uss.pm', $params['pm']);
        })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        })
        ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
            $query->where('type', $params['type']);
        });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.pm,uss.reward_socre,o.order_sn,uss.status,uss.mark,u.score')
            ->order('uss.id', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['reward_socre'],
                    $item['status'],
                    $item['order_sn'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','积分数','结算状态','关联订单','说名',
            ], $arr, '积分统计');
        }

        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.pm,uss.reward_socre,o.order_sn,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->where('pm', 1)->sum('reward_socre'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->where('pm', 1)->sum('reward_socre'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->where('pm', 1)->sum('reward_socre'));
        $date['statistics']['lost'] = decimal2($query4->where('pm', 2)->sum('reward_socre'));
        // $date['statistics']['deduct'] = decimal2($query5->where('uss.status', 1)->where('pm', 2)->sum('reward_socre'));
        // $date['statistics']['all_mp1'] = decimal2($query6->where('uss.status', 1)->where('pm', 1)->sum('reward_socre') - $query7->where('uss.status', 1)->where('pm', 2)->sum('reward_socre'));

        return $date;
    }





    /**
     * 京豆统计
     * @return mixed
     */
    public function jingdou_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        if($params['status'] == 99){
            unset($params['status']);
            $params['type'] = 2;
        }

        $date = [];
        //商户ID
        $query = Db::name('user_sign_jingdou')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id=uss.order_id')
        // ->date_where($query, $where['date'], 'uss,addtime', '-', 2)
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        // ->when(isset($params['pm']) && $params['pm'] != '', function ($query) use ($params) {
        //     $query->where('uss.pm', $params['pm']);
        // })
        ->when(isset($mer_id) && $mer_id != '' && $mer_id != 0, function ($query) use ($mer_id) {
            $query->where('uss.mer_id', $mer_id);
        })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        })
        ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
            $query->where('type', $params['type']);
        });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->order('uss.id', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['number'],
                    $item['status'],
                    $item['order_sn'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','京豆数','状态','关联订单','说名',
            ], $arr, '京豆统计');
        }

        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('number'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('number'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('number'));
        $date['statistics']['lost'] = decimal2($query4->where('uss.type', 2)->sum('number'));
 
        return $date;
    }





    /**
     * 贡献值统计
     * @return mixed
     */
    public function contribute_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        $date = [];
        //商户ID
        $query = Db::name('user_sign_gongxian')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id=uss.order_id')
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        })
        ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
            $query->where('type', $params['type']);
        });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] =  $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->order('uss.number', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['number'],
                    $item['status'],
                    $item['order_sn'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','贡献值数','贡献值状态','关联订单','说名',
            ], $arr, '贡献值统计');
        }

        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.number', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('number'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('number'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('number'));
        $date['statistics']['lost'] = decimal2($query4->where('uss.status', 0)->sum('number'));
 
        return $date;
    }






    /**
     * 复购金统计
     * @return mixed
     */
    public function fugou_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['commission_type', 'type', 'uid', 'pm', 'phone','status']);

        if($params['status'] == 99){
            unset($params['status']);
            $params['type'] = 2;
        }

        $date = [];
        //商户ID
        $query = Db::name('user_sign_fugou')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id=uss.order_id')
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        // ->when(isset($params['pm']) && $params['pm'] != '', function ($query) use ($params) {
        //     $query->where('uss.pm', $params['pm']);
        // })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        })
        ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
            $query->where('type', $params['type']);
        })        
        ->when(isset($params['commission_type']) && $params['commission_type'] != '', function ($query) use ($params) {
            $query->where('uss.commission_type', $params['commission_type']);
        });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,uss.commission_type,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->order('uss.id', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['number'],
                    $item['status'],
                    $item['order_sn'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','复购金数','复购金状态','关联订单','说名',
            ], $arr, '复购金统计');
        }

        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,uss.commission_type,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('number'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('number'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('number'));
        $date['statistics']['lost'] = decimal2($query4->where('uss.type', 2)->sum('number'));
 
        return $date;
    }





    /**
     * 红包统计
     * @return mixed
     */
    public function hongbao_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        $date = [];
        //商户ID
        $query = Db::name('user_sign_hongbao')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id=uss.order_id')
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        // ->when(isset($params['pm']) && $params['pm'] != '', function ($query) use ($params) {
        //     $query->where('uss.pm', $params['pm']);
        // })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        })
        ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
            $query->where('type', $params['type']);
        });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->order('uss.id', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['number'],
                    $item['status'],
                    $item['order_sn'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','红包数','红包状态','关联订单','说名',
            ], $arr, '红包统计');
        }

        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('number'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('number'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('number'));
        $date['statistics']['tixian'] = decimal2($query4->where('uss.status', 0)->sum('number'));
        $date['statistics']['zhuan_vr'] = decimal2('0');
 
        return $date;
    }






    /**
     * VR统计
     * @return mixed
     */
    public function vr_list($mer_id = 0)
    {
        $page = input('page', 1);
        $limit = input('limit', 20);
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone', 'status']);

        // 1. 基础查询构建
        $query = Db::name('user_sign_vr')
            ->alias('uss')
            ->leftJoin('user u', 'u.uid = uss.uid')
            ->leftJoin('store_order o', 'o.order_id = uss.order_id')
            ->where('u.test_user','<>','1')
            ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
                $query->where('uss.uid', $params['uid']);
            })
            ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
                $query->whereLike('u.phone', '%' . $params['phone'] . '%');
            })
            ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
                $query->where('uss.type', $params['type']);
            });

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
                ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,uss.type,uss.mark,u.score')
                ->order('uss.id', 'desc')
                ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['type']) {
                    case '1':
                        $item['type'] = '转入';
                        break;
                    case '2':
                        $item['type'] = '消耗';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['nickname'],
                    $item['phone'],
                    $item['number'],
                    $item['type'],
                    $item['mark']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','用户昵称','手机号','VR数','VR类型','说名',
            ], $arr, 'VR统计');
        }

        // 4. 分页查询
        $date['list'] = $query->field([
            'uss.id',
            'uss.addtime',
            'u.nickname',
            'u.phone',
            'uss.number',
            'o.order_sn',
            'uss.type',
            'uss.mark',
            'u.score'
        ])
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });

        // 5. 统计信息提取到单独方法
        $date['count'] = $query->count();
        $date['statistics'] = $this->getVrStatistics($query);

        return $date;
    }

    /**
     * 获取VR统计信息
     */
    private function getVrStatistics($query)
    {
        $statistics = [];
        $conditions = [
            'all' => [],
            'nclear' => ['uss.type' => 2],
            'transfervr' => function ($subQuery) {
                $subQuery->where('uss.type', '=', 2)
                    ->where(function ($q) {
                        $q->where('uss.mark', 'like', '%消耗%')
                            ->whereOr('uss.mark', 'like', '%管理员调整vr%');
                    });
            },
            'acquirevr' => function ($subQuery) {
                $subQuery->where('uss.type', '=', 1)
                    ->where(function ($q) {
                        $q->where('uss.mark', 'like', '%到账%')
                            ->whereOr('uss.mark', 'like', '%管理员调整vr%');
                    });
            },
            'transitionvr' => [['uss.mark', 'like', '%佣金转VR%']],
            'jcshqvr' => [['uss.mark', 'like', '%购买精彩%']],
            'hyzqvr' => [['uss.mark', 'like', '%购买会员%']],
            'allbuyvr' => [['uss.mark', 'like', '%购买%']],
        ];

        foreach ($conditions as $key => $condition) {
            $subQuery = clone $query;
            if (is_callable($condition)) {
                // 如果是闭包函数，直接调用
                $condition($subQuery);
            } elseif (!empty($condition)) {
                // 如果是数组条件，使用where
                $subQuery->where($condition);
            }
            $statistics[$key] = decimal2($subQuery->sum('uss.number'));
        }

        return $statistics;
    }





     /**
     * PV统计
     * @return mixed
     */
    public function pv_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params([ 'uid', 'pm', 'phone','status']);

        // if($params['status'] == 99){
        //     unset($params['status']);
        //     $params['type'] = 2;
        // }

        $date = [];
        //商户ID
        $query = Db::name('user_pv_log')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        });
        // ->when(isset($params['type']) && $params['type'] != '', function ($query) use ($params) {
        //     $query->where('type', $params['type']);
        // });
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;
        $query5 = clone $query;
        $query6 = clone $query;
        $query7 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if ($is_excel == 1) {
            $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
                ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.pv,uss.order_id,uss.status,uss.mark')
                ->order('uss.id', 'desc')
                ->select();
            $arr = [];
            foreach ($date['list'] as $k => $item) {
                switch ($item['status']) {
                    case '1': $statusLabel = '有效'; break;
                    case '-1': $statusLabel = '待确认'; break;
                    case '0': $statusLabel = '已失效'; break;
                    default: $statusLabel = (string)$item['status'];
                }
                $addtimeFormatted = is_numeric($item['addtime']) ? date('Y-m-d H:i:s', $item['addtime']) : $item['addtime'];
                $arr[] = [
                    $item['id'],
                    $addtimeFormatted,
                    $item['nickname'],
                    $item['phone'],
                    $item['pv'],
                    $statusLabel,
                    $item['order_id'],
                    $item['mark']
                ];
            }
            return $this->getStatisticsExcel([
                'ID', '时间', '用户昵称', '手机号', 'PV数', '状态', '关联订单号', '说明',
            ], $arr, 'PV统计');
        }



        $date['list'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.pv,uss.order_id,uss.status,uss.mark,u.score')
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['addtime'] = is_numeric($item['addtime']) ? date('Y-m-d H:i:s', $item['addtime']) : $item['addtime'];
                $item['order_sn'] = $item['order_id'];
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('uss.pv'));//平台PV总数
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('uss.pv'));//已结算PV总数
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('uss.pv'));//未结算PV总数
        $date['statistics']['lost'] = decimal2($query4->where('uss.status', 0)->sum('uss.pv'));//已失效PV总数
        $hongbao = Db::name('user_sign_hongbao')->where('status', 1)->sum('number');
        $date['statistics']['deduct'] = decimal2($hongbao);//已结算红包总额
        $date['statistics']['all_mp1'] = bcsub($date['statistics']['clear'], $date['statistics']['deduct'], 2);//可分红PV数
 
        return $date;
    }




    /**
     * 商户分润统计
     * @return mixed
     */
    public function fenrun_list($mer_id = 0)
    {
        $page = input('page');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        if($params['status'] == 99){
            unset($params['status']);
            $params['type'] = 2;
        }

        $date = [];
        //商户ID
        $query = Db::name('user_sign_fenrun')
        ->alias('uss')
        ->leftJoin('user u', 'u.uid=uss.uid')
        ->leftJoin('store_order o', 'o.order_id = uss.order_id AND uss.order_type = 0')
        // 条件关联 order_merchant 表 (只在 uss.order_type = 10 时尝试关联 om)
        ->leftJoin('rrx_order_merchant om', 'om.id = uss.order_id AND uss.order_type = 10') // 注意这里的表名和字段
        ->leftJoin('merchant m', 'm.uid=u.uid')
        ->when(isset($mer_id) && $mer_id != '' && $mer_id != 0, function ($query) use ($mer_id) {
            $query->where(function ($query) use($mer_id) {
                $query->where('o.mer_id', $mer_id)->whereOr('om.mer_id', $mer_id);
            });
        })
        ->when(isset($params['uid']) && $params['uid'] != '', function ($query) use ($params) {
            $query->where('uss.uid', $params['uid']);
        })
        ->when(isset($params['status']) && $params['status'] != '', function ($query) use ($params) {
            $query->where('uss.status', $params['status']);
        })
        ->when(isset($params['phone']) && $params['phone'] != '', function ($query) use ($params) {
            $query->whereLike('u.phone', '%' . $params['phone'] . '%');
        });
        $query = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2);
        $query1 = clone $query;
        $query2 = clone $query;
        $query3 = clone $query;
        $query4 = clone $query;

        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $date['list'] = $query
            ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,o.total_price,uss.status,uss.mark,u.score,m.mer_name')
            ->order('uss.id', 'desc')
            ->select();
            $arr = [];
            foreach ($date['list'] as $k=>$item){
                switch ($item['status']) {
                    case '1':
                        $item['status'] = '有效';
                        break;
                    case '-1':
                        $item['status'] = '待确认';
                        break;
                    case '0':
                        $item['status'] = '已失效';
                        break;
                }
                $arr[] = array(
                    $item['id'],
                    date('Y-m-d H:i:s', $item['addtime']),
                    $item['number'],
                    $item['total_price'],
                    $item['status'],
                    $item['mer_name'],
                    $item['order_sn']
                );
            }
            return $this->getStatisticsExcel([
                'ID','时间','商户分润金额','订单金额','状态','店铺名称','关联订单',
            ], $arr, '商户分润统计');
        }

        $date['list'] = $query
            // ->field('uss.id,uss.addtime,u.nickname,u.phone,uss.number,o.order_sn,o.total_price,uss.status,uss.mark,u.score,m.mer_name')
            ->field([
                'uss.id',
                'uss.addtime',
                'u.nickname',
                'u.phone',
                'uss.number',
                'uss.status',
                'uss.mark',
                'u.score',
                'm.mer_name', // 商户名称 (依赖 o 表是否存在)
                'uss.order_type', // 也可以选择 uss.order_type 方便判断
                // 使用 CASE 表达式选择订单号
                // 当 uss.order_type = 0, 选取 o.order_sn
                // 当 uss.order_type = 10, 选取 om.out_trade_no
                // 否则为 NULL，并别名为 order_number
                Db::raw('CASE WHEN uss.order_type = 0 THEN o.order_sn WHEN uss.order_type = 10 THEN om.out_trade_no ELSE NULL END AS order_sn'),

                // 使用 CASE 表达式选择订单金额
                // 当 uss.order_type = 0, 选取 o.total_price
                // 当 uss.order_type = 10, 选取 om.money
                // 否则为 NULL，并别名为 order_amount
                Db::raw('CASE WHEN uss.order_type = 0 THEN o.total_price WHEN uss.order_type = 10 THEN om.money ELSE NULL END AS total_price'),
            ])
            ->page($page, $limit)
            ->order('uss.id', 'desc')
            ->select()
            ->each(function ($item) {
                $item['mark'] = $item['mark'];
                $item['addtime'] = date('Y-m-d H:i:s', $item['addtime']);
                return $item;
            });
        $date['count'] = $query->count();
        $date['statistics']['all'] = decimal2($query1->sum('number'));
        $date['statistics']['clear'] = decimal2($query2->where('uss.status', 1)->sum('number'));//已结算
        $date['statistics']['nclear'] = decimal2($query3->where('uss.status', -1)->sum('number'));//未结算
        $date['statistics']['lost'] = decimal2($query4->where('uss.status', 0)->sum('number'));//已失效
 
        return $date;
    }


    /**
     * 获取统计导出
     */
    public function getStatisticsExcel($header,$arr,$title)
    {
        $filename = $title.'_'.date('YmdHis').'_'.rand(10000,99999);
        $name = $filename;
        $info = '生成时间:' . date('Y-m-d H:i:s',time());
        $suffix='xlsx';
        $path='extract';
        unset($_instance);

        $_instance = SpreadsheetExcelService::instance_xin();

        $_path =$_instance
            ->createOrActive()
            ->setExcelHeader($header)
            ->setExcelTile($title, $name, $info)
            ->setExcelContent($arr)
            ->excelSave($filename, $suffix, $path);

        $domain = request()->domain();
        $data= [
            'name' => $filename.'.'.$suffix,
            'status' => 1,
            'path' => $domain.'/'.$_path
        ];

        return $data;
    }


    /**
     * 列表中的时间条件
     */
    public function date_where($Db, $where, $prefix = 'so.create_time', $field = '-', $type = 1)
    {
        if (!isset($where)) return $Db;
        if ($type == 1) {

            switch ($where) {
                case 'today':
                    $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('today')), date('Y-m-d H:i:s', strtotime('tomorrow -1second'))]);
                    break;
                case 'week':
                    $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('this week 00:00:00')), date('Y-m-d H:i:s', strtotime('next week 00:00:00 -1second'))]);
                    break;
                case 'month':
                    $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('first Day of this month 00:00:00')), date('Y-m-d H:i:s', strtotime('first Day of next month 00:00:00 -1second'))]);
                    break;
                case 'year':
                    $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('this year 1/1')), date('Y-m-d H:i:s', strtotime('next year 1/1 -1second'))]);
                    break;
                case 'yesterday':
                    $Db->whereBetween($prefix, [date('Y-m-d H:i:s', strtotime('yesterday')), date('Y-m-d H:i:s', strtotime('today -1second'))]);
                    break;
                case 'quarter':
                    list($startTime, $endTime) = getMonth();
                    $Db = $Db->where($prefix, '>', $startTime);
                    $Db = $Db->where($prefix, '<', $endTime);
                    break;
                case 'lately7':
                    $Db = $Db->where($prefix, 'between', [date('Y-m-d', strtotime("-7 day")), date('Y-m-d H:i:s')]);
                    break;
                case 'thisWeek':
                    $startOfWeek = strtotime("Monday this week");
                    $endOfWeek = strtotime("Sunday this week");
                    $Db = $Db->where($prefix, 'between', [date('Y-m-d', $startOfWeek), date('Y-m-d H:i:s', $endOfWeek)]);
                    break;
                case 'lastWeek':
                    $startOfLastWeek = strtotime("Monday last week");
                    $endOfLastWeek = strtotime("Sunday last week");
                    $Db = $Db->where($prefix, 'between', [date('Y-m-d', $startOfLastWeek), date('Y-m-d H:i:s', $endOfLastWeek)]);
                    break;
                case 'lately30':
                    $Db = $Db->where($prefix, 'between', [date('Y-m-d', strtotime("-30 day")), date('Y-m-d H:i:s')]);
                    break;
                default:
                    if (strstr($where, $field) !== false) {
                        list($startTime, $endTime) = explode($field, $where);
                        if (strlen($startTime) == 4) {
                            $Db->whereBetweenTime($prefix, date('Y-m-d H:i:s', strtotime($startTime)), date('Y-m-d H:i:s', strtotime($startTime . ' +1day -1second')));
                        } else {
                            if ($startTime == $endTime) {
                                $Db = $Db->whereDay($prefix, $startTime);
                            } else {
                                $Db = $Db->where($prefix, '>', date('Y-m-d H:i:s', strtotime($startTime)));
                                $Db = $Db->where($prefix, '<', date('Y-m-d H:i:s', strtotime($endTime)));
                            }
                        }
                    }
                    break;
            }
        } else {

            switch ($where) {
                case 'today':
                    $Db->whereBetween($prefix, [strtotime('today'), strtotime('tomorrow -1second')]);
                    break;
                case 'week':
                    $Db->whereBetween($prefix, [strtotime('this week 00:00:00'), strtotime('next week 00:00:00 -1second')]);
                    break;
                case 'month':
                    $Db->whereBetween($prefix, [strtotime('first Day of this month 00:00:00'), strtotime('first Day of next month 00:00:00 -1second')]);
                    break;
                case 'year':
                    $Db->whereBetween($prefix, [strtotime('this year 1/1'), strtotime('next year 1/1 -1second')]);
                    break;
                case 'yesterday':
                    $Db->whereBetween($prefix, [strtotime('yesterday'), strtotime('today -1second')]);
                    break;
                case 'quarter':
                    list($startTime, $endTime) = getMonthTime();
                    $Db = $Db->where($prefix, '>', $startTime);
                    $Db = $Db->where($prefix, '<', $endTime);
                    break;
                case 'lately7':
                    $Db = $Db->where($prefix, 'between', [strtotime("-7 day"), time()]);
                    break;
                case 'thisWeek':
                    $startOfWeek = strtotime("Monday this week");
                    $endOfWeek = strtotime("Sunday this week");
                    $Db = $Db->where($prefix, 'between', [ $startOfWeek, $endOfWeek]);
                    break;
                case 'lastWeek':
                    $startOfLastWeek = strtotime("Monday last week");
                    $endOfLastWeek = strtotime("Sunday last week");
                    $Db = $Db->where($prefix, 'between', [ $startOfLastWeek, $endOfLastWeek]);
                    break;
                case 'lately30':
                    $Db = $Db->where($prefix, 'between', [strtotime("-30 day"), time()]);
                    break;
                default:
                    if (strstr($where, $field) !== false) {
                        list($startTime, $endTime) = explode($field, $where);
                        if (strlen($startTime) == 4) {
                            $Db->whereBetweenTime($prefix, strtotime($startTime), strtotime($startTime . ' +1day -1second'));
                        } else {
                            if ($startTime == $endTime) {
                                $Db = $Db->whereDay($prefix, $startTime);
                            } else {
                                $Db = $Db->where($prefix, '>', strtotime($startTime));
                                $Db = $Db->where($prefix, '<', strtotime($endTime));
                            }
                        }
                    }
                    break;
            }
        }
        return $Db;
    }

    public function income_detail($id, $mer_id = 0)
    {
        //商户ID
        $data = Db::name('enterprise_platform_income_detail')
            // ->field('money,pm,title,deal_time,deal_order_sn,deal_type,order_sn,uid,order_id,user_extra_id')
            ->where('id', $id)
            ->when($mer_id, function ($query) use ($mer_id) {
                $query->where('mer_id', $mer_id);
            })
            ->find();
        $data['user_info'] = Db::name('user')->where('uid', $data['uid'])->field('nickname,phone')->find();
        //交易类型 1自营订单入账(商户) 2佣金提现到账(商户) 3订单支付 4商户佣金提现 5用户佣金提现 6订单入账 7 创客自营订单让利
        switch ($data['deal_type']) {
            case 1:
            case 3:
            case 6:
            case 7:
                $data['order_info'] = Db::name('store_order')
                    ->alias('o')
                    ->join('user_bill u', 'u.order_sn=o.order_sn')
                    ->join('store_order_extend soe', 'soe.order_id=o.order_id')
                    ->field('o.total_price,o.hf_fee_amt,o.create_time,u.number as yanglao,soe.shop_bonus_rate,total_postage')
                    ->where('o.order_id', $data['order_id'])
                    ->where('u.commission_type', 5)
                    ->find();
                if (!$data['order_info']) {
                    $data['order_info'] = Db::name('store_order')
                        ->alias('o')
                        ->field('o.total_price,o.hf_fee_amt,o.create_time')
                        ->where('o.order_id', $data['order_id'])
                        ->find();
                    $data['order_info']['yanglao'] = 0;
                }
                if (!isset($data['order_info']['hf_fee_amt']) || $data['order_info']['hf_fee_amt'] == null) {
                    $data['order_info']['hf_fee_amt'] = 0;
                }
                //创客让利总额
                $order_extend = Db::name('store_order_extend')->where('order_id', $data['order_id'])->find();
                $shop_bonus_rate = $order_extend['shop_bonus_rate'] ?? 0;
                $data['order_info']['shop_bonus_rate'] = $shop_bonus_rate;
                $data['order_info']['shop_bonus_price'] = 0;
                if ($mer_id != 496) {
                    $data['order_info']['shop_bonus_price'] = $data['order_info']['total_price'] * $order_extend['shop_bonus_rate'];
                }
                break;
            case 2:
            case 4:
            case 5:
                $data['user_extract_info'] = Db::name('user_extract')
                    ->field('extract_price,create_time,service_money,extract_type,real_name,bank_code,bank_address,bank_info,alipay_code,wechat')
                    ->where('extract_id', $data['user_extra_id'])
                    ->find();
                $data['user_extract_info'] ['real_name'] = json_decode($data['user_extract_info'] ['bank_info'], true)['user_name'];
                $data['user_extract_info'] ['bank_info'] = json_decode($data['user_extract_info'] ['bank_info'], true)['bank_name'];
                break;
        }
        if (!$data)
            throw new Exception('数据不存在');

        return $data;
    }
}