zhuishuyun_quickapp/app/Http/Controllers/Manage/SendOrder/SendOrderForceDayStatsController.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/12/1
 * Time: 14:17
 */

namespace App\Http\Controllers\Manage\SendOrder;

use App\Http\Controllers\Channel\BaseController;
use App\Http\Controllers\Manage\SendOrder\Transformers\SendOrderBookDayStatTransformer;
use App\Modules\Book\Models\BookUvStat;
use App\Modules\Book\Services\BookConfigService;
use App\Modules\SendOrder\Services\SendOrderForceDayStatService;
use DB;
use Illuminate\Http\Request;
use Log;
use Redis;

class SendOrderForceDayStatsController extends BaseController
{

    /**
     * @apiDefine sendOrder 派单
     */

    /**
     * @apiVersion 1.0.0
     * @api {GET} getBookSendOrderDayStats 获取派单统计
     * @apiGroup sendOrder
     * @apiName sendOrderDayStats
     */
    function getGroupByBook(Request $request)
    {

        $begin_time = $request->has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : '';
        $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : '';
        $gender = $request->input('gender','');
        $data = SendOrderForceDayStatService::getSendOrderDayStatsByBook(compact('begin_time', 'end_time','gender'));

        return response()->collection(new SendOrderBookDayStatTransformer(), $data);
    }

    /**
     * @apiVersion 1.0.0
     * @api {GET} exportBookSendOrderDayStats 获取派单统计
     * @apiGroup sendOrder
     * @apiName exportBookSendOrderDayStats
     */
    function exportGroupByBook(Request $request)
    {
        $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : '';
        $begin_time = $request->has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : '';
        $gender = $request->input('gender','');

        $data = SendOrderForceDayStatService::getSendOrderDayStatsByBook(compact('begin_time', 'end_time','gender'));

        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "书籍数据" . date("YmdHis") . ".csv");

        echo iconv("UTF-8", "GBK", "\"书本名称\",\"男女频\",\"派单数\",\"充值总额\",\"成功订单数\",\"PV\",\"UV\",\"充值总额/UV\",\"注册用户/UV\",\"订单总数\",\"订单成功率\"\r\n");
        foreach ($data as $item) {
            echo("\"" . mb_convert_encoding($item->book_name, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding(($item->pid==1)?'男':'女', "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($item->send_order_num, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($item->sum_recharge_amount, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($item->sum_paid_num, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($item->sum_pv, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($item->sum_uv, "GBK", "UTF-8") . "\",");

            $total_send_order_count = $item->sum_paid_num + $item->sum_unpaid_num;

            if (0 == $item->sum_uv) {
                $recharge_persent_result = '100%';
                $register_persent_result = '100%';
            } else {
                $recharge_persent_result = round(($item->sum_recharge_amount / $item->sum_uv) * 100, 2) . '%';
                $register_persent_result = round(($item->sum_register_user_num / $item->sum_uv) * 100, 2) . '%';
            }

            if (0 == $total_send_order_count) {
                $success_order_persent_result = '0%';
            } else {
                $success_order_persent_result = round(($item->sum_paid_num / $total_send_order_count) * 100, 2) . '%';
            }

            echo("\"" . mb_convert_encoding($recharge_persent_result, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($register_persent_result, "GBK", "UTF-8") . "\",");
            echo("\"" . mb_convert_encoding($total_send_order_count, "GBK", "UTF-8") . "\",");
            echo("\"" . $success_order_persent_result . "\"\r\n");
        }
        exit();
    }

    //注册用户/UV比值大于85%的派单链接的强关转化(强关用户数/注册用户数量)、付费转化(付费用户数/强关粉丝）、注册用户ARPU值（充值/注册用户）
    function newUserArpu(Request $request)
    {

        $begin_time = $request->has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : '';
        $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : '';
        $bid = $request->has('bid') && $request->input('bid') ? $request->input('bid') : '';
        $data = DB::select("select avg(sum_recharge_amount) avg_recharge_amount,avg(sum_force_user_num) avg_force_user_num,avg(sum_register_user_num) avg_register_user_num,avg(sum_pay_user_num) avg_pay_user_num 
                   from (select t.* from (select sum(recharge_amount) as sum_recharge_amount,sum(force_user_num) sum_force_user_num,
                   sum(register_user_num) sum_register_user_num,sum(pay_user_num) sum_pay_user_num,sum(uv) sum_uv from send_orders_force_day_stats
                    where total_uv > 20 and date >='{$begin_time}' and date <= '{$end_time}' and bid = '{$bid}'  
                    group by send_order_id) as t ) as tt where sum_register_user_num/sum_uv > 0.85");

        return response()->success([
            'sub_per_register' => $data[0]->avg_register_user_num ? round(100 * $data[0]->avg_force_user_num / $data[0]->avg_register_user_num, 2) . '%' : 0,
            'pay_per_sub' => $data[0]->avg_force_user_num ? round(100 * $data[0]->avg_pay_user_num / $data[0]->avg_force_user_num, 2) . '%' : 0,
            'arpu' => $data[0]->avg_register_user_num ? round($data[0]->avg_recharge_amount / $data[0]->avg_register_user_num, 2) : 0
        ]);

    }

    function deepReadRecordData(Request $request)
    {

        $bid = $request->input('bid');
        if (empty($bid)) {
            return response()->error('LACK_PARAM');
        }

        $total_count = $this->getCount($bid);
        $count = $request->input('count');
        if (!$count) {
            $count = $total_count;
        }
        $book = BookConfigService::getBookById($bid);

        if ($count == $total_count) {
            $info = $this->getReadRecordDataFromRedis($bid);
        } else {
            $info = $this->getReadRecordDataFromDB($bid, $count);
        }
        $info['total_count'] = $total_count;
        $info['now_count'] = $count;
        $info['book_name'] = $book->book_name;
        return response()->success($info);
    }

    private function getReadRecordDataFromRedis($bid)
    {
        $key = 'book:' . $bid . ':deepread:uv';
        $total = Redis::scard($key);
        $forseq = 30;
        $uv = [];
        $chapter = [];
        for ($i = 1; $i <= $forseq; $i++) {
            $chapter_key = 'book:' . $bid . ':chapterseq:' . $i;
            $uv[] = (int)Redis::scard($chapter_key);
            $chapter[] = '第' . $i . '章';
        }

        return ['chapter' => $chapter, 'uv' => $uv, 'total_uv' => $total];
    }

    private function getReadRecordDataFromDB($bid, $count)
    {
        $info = BookUvStat::where('bid', $bid)->where('count', $count)->orderBy('chapter_seq')->select('bid', 'chapter_seq', 'chapter_uv', 'total_uv', 'count')->get();
        if (!$info) {
            return [];
        }
        $data = [];
        foreach ($info as $v) {
            $data['total_uv'] = $v->total_uv;
            $data['uv'][] = $v->chapter_uv;
            $data['chapter'][] = '第' . $v->chapter_seq . '章';
        }
        return $data;
    }

    public function resetDeepReadRecordData(Request $request)
    {
        $bid = $request->input('bid');
        $key = 'book:' . $bid . ':deepread:uv';
        $total = Redis::scard($key);
        $forseq = 30;
        $info = BookUvStat::where('bid', $bid)->select('count')->orderBy('count', 'desc')->first();
        $count = 1;
        if ($info && !empty($info->count)) {
            $count = $info->count + 1;
        }
        $data = [];
        for ($i = 1; $i <= $forseq; $i++) {
            $chapter_key = 'book:' . $bid . ':chapterseq:' . $i;
            $temp = (int)Redis::scard($chapter_key);
            $data[] = ['bid' => $bid, 'chapter_seq' => $i, 'total_uv' => $total, 'chapter_uv' => $temp,
                'count' => $count, 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')];
            Redis::del($chapter_key);
        }
        DB::table('book_uv_stats')->insert($data);
        Redis::del($key);
        return response()->success();
    }


    private function getCount($bid)
    {
        $info = BookUvStat::where('bid', $bid)->select('count')->orderBy('count', 'desc')->first();
        $count = 1;
        if ($info && !empty($info->count)) {
            $count = $info->count + 1;
        }

        return $count;
    }

}