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(pay_time) = '" . $day . "'";
        }
        if ($type == 2 and $day != '') {
            $date = new \DateTime($day);
            $year = $date->format('Y');
            $month = $date->format('m');

            $where .= " and YEAR(pay_time) = " . $year . " AND MONTH(pay_time) = " . $month;
        }
        $xianshangzhifu = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->sum('pay_price');
        $xianshangzhifu_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->sum('pay_price');
        $hongjifenxiaofei = Db::name('store_order')->where($where)->where("pay_type", 5)->sum('pay_price');
//        echo Db::name('store_order')->getLastSql();

        $chengjiaomoney = decimal2($xianshangzhifu + $hongjifenxiaofei);
        $list1[] = ['count' => set_price_rtrim($xianshangzhifu), 'name' => '线上支付金额'];
        if (!$mer_id) {
            $list1[] = ['count' => set_price_rtrim($xianshangzhifu_xuni), 'name' => '虚拟支付金额'];
        }
        $list1[] = ['count' => 0, 'name' => '代付金额'];
        $xianshangzhifunum = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->count();
        $xianshangzhifunum_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->count();
        $hongjifenxiaofeinum = Db::name('store_order')->where($where)->where("pay_type", 5)->count();

        $chengjiaocount = $xianshangzhifunum + $hongjifenxiaofeinum;

        $list2[] = ['count' => $xianshangzhifunum, 'name' => '线上支付订单量'];
        if (!$mer_id) {
            $list2[] = ['count' => $xianshangzhifunum_xuni, 'name' => '虚拟支付订单量'];
        }
        $list2[] = ['count' => 0, 'name' => '代付订单量'];
        $xianshangzhifuren = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1])->group('uid')->count();
        $xianshangzhifuren_xuni = Db::name('store_order')->where($where)->where('pay_type', 0)->group('uid')->count();
        $hongjifenxiaofeiren = Db::name('store_order')->where($where)->where("pay_type", 5)->group('uid')->count();

        $chengjiaoren = Db::name('store_order')->where($where)->whereIn('pay_type', [0, 1, 5])->group('uid')->count();
        $list3[] = ['count' => set_price_rtrim($xianshangzhifuren), 'name' => '线上支付人数'];
        if (!$mer_id) {
            $list3[] = ['count' => set_price_rtrim($xianshangzhifuren_xuni), 'name' => '虚拟支付人数'];
        }
        $list3[] = ['count' => 0, 'name' => '代付人数'];


        $xianshangtuikuan = Db::name('store_order')->where($where)->where('status', -1)->sum('pay_price');

        $tuikuanmoney = $xianshangtuikuan;
        $list4 = array(
            array('count' => $xianshangtuikuan, 'name' => '线上支付退单'),
            array('count' => 0, 'name' => '代付退单'),
        );
        $xianshangzhifutuinum = Db::name('store_order')->where($where)->where('status', -1)->count();

        $tuikuancount = $xianshangzhifutuinum;
        $list5 = array(
            array('count' => set_price_rtrim($xianshangzhifutuinum), 'name' => '线上支付退单量'),
            array('count' => 0, 'name' => '代付退单量'),
        );
        $xianshangzhifutuiremn = Db::name('store_order')->where($where)->where('status', -1)->group('uid')->count();

        $tuikuanren = $xianshangzhifutuiremn;
        $list6 = array(
            array('count' => set_price_rtrim($xianshangzhifutuiremn), 'name' => '线上支付退单人数'),
            array('count' => 0, 'name' => '代付退单数'),
        );


        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaomoney), 'name' => '成交金额', 'list' => $list1];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaocount), 'name' => '成交订单量', 'list' => $list2];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($chengjiaoren), 'name' => '支付人数', 'list' => $list3];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanmoney), 'name' => '退款金额', 'list' => $list4];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuancount), 'name' => '退款订单量', 'list' => $list5];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($tuikuanren), 'name' => '退款人数', 'list' => $list6];
        return $data;
    }

    /**
     * 用户概况
     * @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');
        $shopPartnerCount = Db::query("SELECT user_group,count(uid) count FROM `rrx_user` where mer_id={$mer_id} and is_shop_partner=1 group by user_group");

        $maker = Db::query("SELECT CONCAT(`user_group`, '-',`is_show`) as k, count(uid) count FROM `rrx_user` where {$where} group by user_group,is_show");
        $maker = array_column($maker, 'count', 'k');

        $userByCodeCount = Db::query("SELECT 'temp' as `temp1`, count(*) count FROM `rrx_user` where {$where} and user_group =4 and is_show = 3");
        $userByCodeCount = array_column($userByCodeCount, 'count', 'temp1');

        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[1] ?? 0), 'name' => '消费者'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[2] ?? 0), 'name' => '业务员'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[3] ?? 0), 'name' => '业务经理'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[5] ?? 0), 'name' => '初级合伙人'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[6] ?? 0), 'name' => '中级合伙人'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[7] ?? 0), 'name' => '高级合伙人'];
        $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[8] ?? 0), 'name' => '董事'];
        $list1 = array(
            array('className' => 'el-icon-s-goods', 'count' => intval($maker['4-2'] ?? 0), 'name' => '集团会员'),//is_show=2
            array('className' => 'el-icon-s-goods', 'count' => intval($maker['4-1'] ?? 0), 'name' => '电商创客'),
            array('className' => 'el-icon-s-goods', 'count' => intval($userByCodeCount['temp'] ?? 0), 'name' => '激活创客'),
        );

        if ($mer_id) {
//            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($shopPartnerCount), 'name' => '创客合伙人'];
            $data[] = ['className' => 'el-icon-s-goods', 'count' => (int)($dataQuery[4] ?? 0), 'name' => '创客'];
        }
        $res['left'][] = $data;
        // $res['right'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim(intval($maker['4-2'] ?? 0) + intval($maker['4-1'] ?? 0) + intval($userByCodeCount['temp'] ?? 0)), 'name' => '创客'];

        $maker = Db::query("SELECT count(uid) as count FROM `rrx_merchant` where {$where}");
        $res['right'][] = ['className' => 'el-icon-s-goods', 'count' => set_price_rtrim($maker[0]['count'] ?? 0), 'name' => '创客'];

        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');


        } 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');

        } 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');
        }


        $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');

    }

    /**
     * 用户账单
     * @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;
    }
}