NN2021012701/app/weixin/service/Export.php

<?php

namespace app\weixin\service;

use app\weixin\model\Exports;
use think\Db;

class Export
{
    /**
     * 更新数据
     * @return array
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public static function updateData()
    {
        $ids = [];
        $counts = Export::getCounts(true);
        $date = date('Y-m-d');
        foreach ($counts as $k => $v) {
            if (Export::checkHasByType($k, $date)) {
                $ids[$k] = $v;
                Exports::where(['type' => $k, 'date' => $date])->update(['num' => $v]);
            } else if (Exports::insertGetId(['type' => $k, 'num' => $v, 'date' => $date, 'create_time' => date('Y-m-d H:i:s')])) {
                $ids[$k] = $v;
            }
        }

        // 清除4个月以前的统计数据
        Exports::where('create_time', '<', time() - 6 * 30 * 24 * 3600)->delete();
        return $ids;
    }

    /**
     * 导出上个月报表数据
     * @param string $month
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public static function exportData($month='')
    {
        $month = $month? $month : date('Y-m-01');
        $monthText = date('Y年m月', strtotime($month));

        $PHPExcel = new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
        $PHPSheet->setTitle("{$monthText}统计报表数据"); /* 给当前sheet表设置名称及表头 */
        $PHPSheet->setCellValue('A1', '序号');
        $PHPSheet->setCellValue('B1', '统计类型');
        $PHPSheet->setCellValue('C1', '统计总数');

        $dataList = [];
        $date = Exports::where('date', 'like', "{$month}%")
            ->group('type')
            ->order('date','desc')
            ->value('date');

        if($date){
            $dataList = Exports::where('date', '=', $date)
                ->select();
            $dataList = $dataList ? $dataList->toArray() : [];
        }

        $accessDatas = Exports::whereIn('type', ['login', 'match', 'book', 'activity', 'home', 'center', 'heart'])
            ->where('date', 'like', "{$month}%")
            ->group('type')
            ->sum('num');

        $types = ['userCount' => '用户总数量', 'monthUserCount' => '本月新增注册用户', 'monthFans' => '本月新增粉丝', 'totalMarketCount' => '总推广合伙人', 'monthMarketCount' => '本月新增推广合伙人', 'totalMarketInviteCount' => '合伙人总推广用户数', 'totalMarketInviteCount1' => '合伙人总推广实际注册用户数', 'monthMarketInviteCount' => '本月合伙人新增推广用户', 'monthMarketInviteCount1' => '本月合伙人邀请注册用户', 'activityCount' => '活动数量', 'monthRecharge' => '本月充值人数', 'monthRechargeMoney' => '本月充值金额', 'totalRecharge' => '累计充值人数', 'totalRechargeMoney' => '累计充值金额', 'monthVipCount' => '本月开通VIP数量', 'monthVipMoney' => '本月开通VIP金额', 'totalVipCount' => '累计开通VIP人数', 'totalVipMoney' => '累计开通VIP金额', 'monthHandCount' => '本月牵线付费人数', 'monthHandMoney' => '本月牵线付费金额', 'totalHandCount' => '累计牵线付费人数', 'totalHandMoney' => '累计牵线付费金额', 'monthContactCount' => '本月申请微信次数', 'userlogout' => '累计注销人数', 'bookCount' => '累计报名人数', 'bookMoney' => '累计报名金额', 'bookCancelCount' => '累计报名退款次数', 'bookCancelMoney' => '累计报名退款金额', 'monthBookCount' => '本月总报名人数', 'monthBookMoney' => '本月总报名金额', 'login' => '本月登录人数', 'match' => '本月访问单身推荐', 'heart' => '本月访问怦然心动', 'center' => '本月访问用户中心', 'home' => "本月访问个人主页", 'activity' => '本月访问活动列表', 'book' => '本月想要报名','maxLogin'=>'本月最高登录人数'];

        $k = 1;
        foreach ($dataList as $key => $item) {
            $type = isset($types[$item['type']]) ? $types[$item['type']] : '';
            $num = isset($item['num'])? moneyFormat($item['num']) : 0;
            if ($type) {
                $k += 1;
                if(in_array($type, ['login', 'match', 'book', 'activity', 'home', 'center', 'heart'])){
                    $num = isset($accessDatas[$type])? moneyFormat($accessDatas[$type]) : 0;
                }

                $num = intval($num) == $num? intval($num) : $num;
                $PHPSheet->setCellValue('A' . $k, $key);
                $PHPSheet->setCellValue('B' . $k, $type.'          ');
                $PHPSheet->setCellValue('C' . $k, $num);
            }

        }

        ob_clean();
        $filename = "{$monthText}后台主页数据统计报表.xls";
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename=' . $filename . '');
        header("Content-Transfer-Encoding:binary");
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
        $PHPWriter->save('php://output');
    }


    /**
     * 验证师傅已经统计
     * @param $type
     * @return bool|mixed
     */
    public
    static function checkHasByType($type, $date = '')
    {
        $cacheKey = "counts:check:{$type}";
        if (PRedis::get($cacheKey)) {
            return true;
        }

        $date = $date ? $date : date("y-m-d");
        if ($id = Exports::where(['type' => $type, 'date' => $date])->value('id')) {
            PRedis::set($cacheKey, $id, rand(10, 20));
        }

        return $id;
    }

    public
    static function getCounts($reload = false)
    {

        // 统计数据
        $cacheKeys = "counts:all";
        $counts = PRedis::get($cacheKeys);
        if ($counts && !$reload) {
            return $counts;
        }

        $time = strtotime(date('Y-m-d', time()));
        $monthTime = strtotime(date('Y-m-01'));
        $counts['userCount'] = Db::name('user')
            ->where(['user_type' => 2, 'user_status' => 1, 'agent_type' => 0])
            ->where('openid', 'not in', '')
            ->count('id');
        $counts['realUserCount'] = Db::name('user')->alias('u')
            ->leftJoin('user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'up.idcard_check' => 2])
            ->where('u.openid', 'not in', '')
            ->count('u.id');
        $counts['todayUserCount'] = Db::name('user')->alias('u')
            ->leftJoin('user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'up.idcard_check' => 2])
            ->where('u.create_time', '>', $time)
            ->where('u.create_time', '<=', time())
            ->where('u.openid', 'not in', '')
            ->count('u.id');
        $counts['monthUserCount'] = Db::name('user')->alias('u')
            ->leftJoin('user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'up.idcard_check' => 2])
            ->where('u.create_time', '>=', $monthTime)
            ->where('u.create_time', '<=', time())
            ->where('u.openid', 'not in', '')
            ->count('u.id');
        $counts['monthFans'] = Db::name('user')
            ->where(['user_type' => 2, 'user_status' => 1, 'agent_type' => 0])
            ->where('create_time', '>', $monthTime)
            ->where('create_time', '<=', time())
            ->where('openid', 'not in', '')
            ->count('id');
        $counts['todayFans'] = Db::name('user')
            ->where(['user_type' => 2, 'user_status' => 1, 'agent_type' => 0])
            ->where('create_time', '>', $time)
            ->where('create_time', '<=', $time + 86400)
            ->where('openid', 'not in', '')
            ->count('id');
        $counts['marketCount'] = Db::name('user')
            ->where(['user_type' => 2, 'agent_status' => 1, 'agent_type' => 1])
            ->where('agent_create_time', '>', $time)
            ->where('agent_create_time', '<=', $time + 86400)
            ->count('id');
        $counts['monthMarketCount'] = Db::name('user')
            ->where(['user_type' => 2, 'agent_status' => 1, 'agent_type' => 1])
            ->where('agent_create_time', '>=', $monthTime)
            ->where('agent_create_time', '<=', $time + 86400)
            ->count('id');
        $counts['totalMarketCount'] = Db::name('user')
            ->where(['user_type' => 2, 'agent_status' => 1, 'agent_type' => 1])
            ->where('agent_create_time', '<=', $time + 86400)
            ->count('id');
        $counts['marketInviteCount'] = Db::name('user')
            ->alias('u')
            ->join('sg_user uu', 'uu.id=u.parent_id', 'left')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1])
            ->where('u.create_time', '>=', $time)
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['marketInviteCount1'] = Db::name('user')
            ->alias('u')
            ->leftJoin('sg_user uu', 'uu.id=u.parent_id')
            ->leftJoin('sg_user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1, 'up.idcard_check' => 2])
            ->where('u.create_time', '>=', $time)
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['monthMarketInviteCount'] = Db::name('user')
            ->alias('u')
            ->join('sg_user uu', 'uu.id=u.parent_id', 'left')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1])
            ->where('u.create_time', '>=', $monthTime)
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['monthMarketInviteCount1'] = Db::name('user')
            ->alias('u')
            ->leftJoin('sg_user uu', 'uu.id=u.parent_id')
            ->leftJoin('sg_user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1, 'up.idcard_check' => 2])
            ->where('u.create_time', '>=', $monthTime)
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['totalMarketInviteCount'] = Db::name('user')
            ->alias('u')
            ->join('sg_user uu', 'uu.id=u.parent_id', 'left')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1])
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['totalMarketInviteCount1'] = Db::name('user')
            ->alias('u')
            ->leftJoin('sg_user uu', 'uu.id=u.parent_id')
            ->leftJoin('sg_user_profile up', 'up.userid=u.id')
            ->where(['u.user_type' => 2, 'u.user_status' => 1, 'u.agent_type' => 0, 'uu.agent_type' => 1, 'up.idcard_check' => 2])
            ->where('u.create_time', '<=', $time + 86400)
            ->count('u.id');
        $counts['activityCount'] = Db::name('activity')
            ->where(['status' => 1])
            ->count('id');

        $counts['recharge'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');
        $counts['rechargeMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');
        $counts['monthRecharge'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');

        $counts['monthRechargeMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');

        $counts['totalRecharge'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->count('id');
        $counts['totalRechargeMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 1, 'status' => 2])
            ->sum('pay_money');

        $counts['vipMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');
        $counts['monthVipMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');

        $counts['vipCount'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');
        $counts['monthVipCount'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');

        $counts['totalVipCount'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->count('id');
        $counts['totalVipMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 4, 'status' => 2])
            ->sum('pay_money');

        $counts['handMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');
        $counts['monthHandMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('pay_money');

        $counts['handCount'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-d'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');
        $counts['monthHandCount'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->where('pay_at', '>=', date('Y-m-01'))
            ->where('pay_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');

        $counts['totalHandCount'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->count('id');
        $counts['totalHandMoney'] = Db::name('user_recharge_log')
            ->where(['type' => 5, 'status' => 2])
            ->sum('pay_money');

        // 申请微信次数
        $counts['contactCount'] = Db::name('user_contact_logs')
            ->where(function ($query) {
                $query->whereIn('status', [1, 2])->whereOr(['status' => 3, 'is_read' => 1]);
            })
            ->where('created_at', '>=', date('Y-m-d'))
            ->where('created_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');
        $counts['monthContactCount'] = Db::name('user_contact_logs')
            ->where(function ($query) {
                $query->whereIn('status', [1, 2])->whereOr(['status' => 3, 'is_read' => 1]);
            })
            ->where('created_at', '>=', date('Y-m-01'))
            ->where('created_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');


        // 其他访问统计
        $counts['login'] = Db::name('user')
            ->where(['user_type' => 2, 'user_status' => 1])
            ->where('last_login_time', '>=', strtotime(date('Y-m-d', $time)))
            ->where('last_login_time', '<=', strtotime(date('Y-m-d', $time + 86400)))
            ->count('id');

        $counts['maxLogin'] = Exports::where(['type' => 'login'])
            ->where('date', 'like', "".date('Y-m')."%")
            ->max('num');

        if($counts['maxLogin'] < $counts['login']){
            $counts['maxLogin'] = $counts['login'];
        }

        //统计注销总人数
        $counts['userlogout'] = Db::name('user')
            ->where(['user_type' => 2, 'user_status' => -1])
            ->count('id');

        // 报名人数
        $counts['bookCount'] = Db::name('books')
            ->where('status', 'in', [2, 3])
            ->count('id');
        $counts['bookMoney'] = Db::name('books')
            ->where('status', 'in', [2, 3])
            ->sum('money');
        $counts['bookCancelCount'] = Db::name('books')
            ->where('status', '=', 5)
            ->count('id');
        $counts['bookCancelMoney'] = Db::name('books')
            ->where('status', '=', 5)
            ->sum('money');
        $counts['monthBookCount'] = Db::name('books')
            ->where('status', 'in', [2, 3])
            ->where('book_at', '>=', date('Y-m-01'))
            ->where('book_at', '<=', date('Y-m-d', $time + 86400))
            ->count('id');
        $counts['monthBookMoney'] = Db::name('books')
            ->where('status', 'in', [2, 3])
            ->where('book_at', '>=', date('Y-m-01'))
            ->where('book_at', '<=', date('Y-m-d', $time + 86400))
            ->sum('money');

        $cacheKey = 'counts:users:' . date('Ymd') . '_';
        $counts['activity'] = PRedis::get($cacheKey . 'activity');  // 活动列表
        $counts['activity'] = $counts['activity'] ? $counts['activity'] : 0;
        $counts['book'] = PRedis::get($cacheKey . 'book'); // 想要报名
        $counts['book'] = $counts['book'] ? $counts['book'] : 0;
        $counts['match'] = PRedis::get($cacheKey . 'match'); // 访问单身推荐
        $counts['match'] = $counts['match'] ? $counts['match'] : 0;
        $counts['heart'] = PRedis::get($cacheKey . 'heart');  // 访问怦然心动
        $counts['heart'] = $counts['heart'] ? $counts['heart'] : 0;
        $counts['center'] = PRedis::get($cacheKey . 'center');  // 访问个人中心
        $counts['center'] = $counts['center'] ? $counts['center'] : 0;
        $counts['home'] = PRedis::get($cacheKey . 'home');  // 访问个人主页
        $counts['home'] = $counts['home'] ? $counts['home'] : 0;
        PRedis::set($cacheKeys, $counts, rand(5, 10));

        // 后台调用更新
        $tempKey = "counts:update";
        if (!PRedis::get($tempKey) && !$reload) {
            $date = date('Y-m-d');
            foreach ($counts as $k => $v) {
                if (Export::checkHasByType($k, $date)) {
                    $ids[$k] = $v;
                    Exports::where(['type' => $k, 'date' => $date])->update(['num' => $v]);
                } else if (Exports::insertGetId(['type' => $k, 'num' => $v, 'date' => $date, 'create_time' => date('Y-m-d H:i:s')])) {
                    $ids[$k] = $v;
                }
            }

            PRedis::set($tempKey, $ids, rand(10, 30));
        }

        return $counts;
    }
}