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\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)
    {
        // if ($mer_id) {
        //     $where = "mer_id = " . $mer_id . " and paid = 1";
        // } 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 . "'";


        // 会员专区订单总数
        //总数
        $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' => '日订单总数'];

        //精彩生活区订单总数
        //总数
        $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' => '日订单总数'];

        //复购区订单总数
        //总数
        $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' => '日订单总数'];


        $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;
    }

    /**
     * 用户概况
     * @param int $mer_id
     * @return array
     * @throws \Exception
     */
    public function user_overview($mer_id = 0)
    {
        $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');

        $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' => '消费者'];
        $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[2] ?? 0), 'name' => '业务员'];
        $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[3] ?? 0), 'name' => '业务经理'];
        $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[5] ?? 0), 'name' => '初级合伙人'];
        $data['one'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[6] ?? 0), 'name' => '中级合伙人'];

        $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[7] ?? 0), 'name' => '高级合伙人'];
        $data['tow'][] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[8] ?? 0), 'name' => '董事'];
        $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' => '联创'];
        
        $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' => '股权'];
        $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' => '商户'];

        $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";

        $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' => '会员专区今日总业绩'];

        $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";
       
        $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";
       
        $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";
       
        $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 ";
        }
        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 . "%'")->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 '" . $lastDay . "%'")
                ->sum('epid.money');
            $shouyi = Db::name('user_bill')
                ->where($bill_where)
                ->whereBetween('take_time', [strtotime($day), strtotime($day) + 24 * 3600])
                ->sum('number');//
            $yanglao = Db::name('user_bill')
                ->where($yanglao_where)
                ->whereBetween('take_time', [strtotime($day), strtotime($day) + 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-d H:i:s', strtotime($day)), date('Y-m-d H:i:s', strtotime($day) + 24 * 3600)])
                ->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)])
                ->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)])
                ->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)])
                ->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)])
                ->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)])
                ->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)])
            ->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)])
            ->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)])
            ->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)])
            ->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)])
            ->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)])
            ->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)])
            ->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)])
            ->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)->sum('pay_price');
        $hf_fee_amt = Db::name('store_order')->where($where_order)->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')
            ->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, 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;
        $query3 = clone $query;
        $query4 = clone $query;


        //导出数据
        $is_excel = request()->param('is_excel', 0);
        if($is_excel == 1){
            $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();
        $statistics = $query1->field('sum(ub.number) money,ub.commission_type')->group('ub.commission_type')->select()->toArray();
        $statistics = array_column($statistics, 'money', 'commission_type');

        $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 = Db::name('user_transition_vr')->where('type', 1)->sum('number');
        $data['statistics']['commission'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($transition_vr), 'name' => '佣金转VR'];
        $user_extra_money = Db::name('user_extract')->where('status', 1)->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($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');
        $limit = input('limit');
        $where = request()->params(['date']);
        $params = request()->params(['type', 'uid', 'pm', 'phone','status']);

        $date = [];
        //商户ID
        $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')
        ->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.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统计');
        }

        $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')
            ->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.type', 1)->sum('number'));
        $date['statistics']['nclear'] = decimal2($query3->where('uss.type', 2)->sum('number'));
 
        return $date;
    }




     /**
     * 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')
        ->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.pv,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['pv'],
                    $item['status'],
                    $item['order_sn'],
                    $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,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('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')
        ->leftJoin('merchant m', 'm.mer_id=o.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'] . '%');
        });
        $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,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'] = $this->date_where($query, $where['date'], 'uss.addtime', '-', 2)
            ->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')
            ->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 '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 '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;
    }
}