yc_douyin_capi/app/Services/Channel/ChannelHomeService.php

<?php

namespace App\Services\Channel;

use App\Facade\Site;
use App\Libs\Utils;
use App\Consts\ErrorConst;
use App\Exceptions\ApiException;
use Illuminate\Support\Facades\DB;

class ChannelHomeService
{


    public function __construct(

    )
    {

    }

    /**
     * 获取当前站点信息
     * @return array
     */
    public function getChannelInfo() {
        $distribution_channel_id = Site::getCurrentChannelId();
        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();
        $result = DB::table('distribution_channels')->where('id', $distribution_channel_id)->select('channel_type', 'book_charge_type', 'book_coin', 'chapter_coin',
            'book_calculate_price_type', 'chapter_calculate_price_type')->first();
        if ($result) {
            $result = (array)$result;
            if ($result['book_charge_type'] != 'HYBRID') $result['book_charge_type'] = 'MERGE';
            $result['is_master'] = $sub_channel_ids ? 1 : 0;
        }else {
            $result = [];
        }

        return $result;
    }

    /**
     *
     * @param $data
     * @return int
     * @throws ApiException
     */
    public function setChannelBookChargeType($data) {
        $distribution_channel_id = Site::getCurrentChannelId();
        $book_charge_type = getProp($data, 'book_charge_type');
        $book_calculate_price_type = getProp($data, 'book_calculate_price_type');
        $book_coin = (int)getProp($data, 'book_coin');
        $chapter_calculate_price_type = getProp($data, 'chapter_calculate_price_type');
        $chapter_coin = (int)getProp($data, 'chapter_coin');

        // 如果是合并站点则看书籍收费方式是按本还是按章
        if ($book_charge_type == 'MERGE') {
            if ($book_calculate_price_type == 'all') {
                $book_charge_type = 'BOOK';
            }else {
                $book_charge_type = 'CHAPTER';
            }
            $chapter_calculate_price_type = $book_calculate_price_type;
            $chapter_coin = $book_coin;
        }

        $update_data = [
            'book_charge_type'              => $book_charge_type,
            'book_calculate_price_type'     => $book_calculate_price_type,
            'book_coin'                     => $book_coin,
            'chapter_calculate_price_type'  => $chapter_calculate_price_type,
            'chapter_coin'                  => $chapter_coin,
            'updated_at'                    => date('Y-m-d H:i:s')
        ];

        return DB::table('distribution_channels')->where('id', $distribution_channel_id)->update($update_data);
    }

    // 获取子账号
    public function getSubUser() {
        $uid = Site::getUid();
        return DB::table('channel_user_mappings')->leftJoin('channel_users', 'channel_users.id', 'channel_user_mappings.sub_uid')
            ->where('channel_user_mappings.master_uid', $uid)->select('channel_users.nickname')->get()->map(function ($value) {
                return (array)$value;
            })->toArray();
    }

    // 站点统计汇总
    public function statisticsByTotal($data) {
        $data_source = getProp($data, 'data_source', 'current_channel');
        $distribution_channel_id = Site::getCurrentChannelId();
        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();
        $date_range              = getProp($data, 'date_range');

        if ($data_source == 'all_channel') {        // 获取全站数据(主账号权限)
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
        }else if ($data_source == 'current_channel') {                  // 获取本站点数据(所有账号共有)
            $distribution_channel_ids = [$distribution_channel_id];
        }else {                                                 // 获取推广员数据(主账号权限)
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
            // 如果推广员符合子账号条件则取对应子站点数据
            $distribution_channel_id = DB::table('channel_users')->leftJoin('distribution_channels', 'distribution_channels.channel_user_id', 'channel_users.id')
                ->where('channel_users.nickname', $data_source)->value('distribution_channels.id');
            if (in_array($distribution_channel_id, $distribution_channel_ids)) $distribution_channel_ids = [$distribution_channel_id];
            else $distribution_channel_ids = [];
        }

        $query = DB::table('channel_day_statistics')->whereIn('distribution_channel_id', $distribution_channel_ids)
            ->selectRaw("sum(total_order_num) as total_order_num, sum(paid_order_num) as paid_order_num,
            sum(register_num) as register_num, sum(register_pay_num) as register_pay_num, sum(register_pay_amount) as register_pay_amount,
            sum(total_pay_num) as total_pay_num, sum(total_pay_amount) as total_pay_amount");

        // 获取今日数据
        $day_start = date('Y-m-d 00:00:00');
        $total_order_num = DB::table('orders')->whereIn('distribution_channel_id', $distribution_channel_ids)->where('created_at', '>', $day_start)->count('id');
        $paid_order = DB::table('orders')->whereIn('distribution_channel_id', $distribution_channel_ids)->where('created_at', '>', $day_start)->where('status', 'PAID')
            ->selectRaw("count(id) as paid_order_num, count(distinct uid) as total_pay_num, sum(price) as total_pay_amount")->first();
        $paid_order_num = getProp($paid_order, 'paid_order_num', 0);
        $total_pay_num = getProp($paid_order, 'total_pay_num', 0);
        $total_pay_amount = getProp($paid_order, 'total_pay_amount', 0);

        $register_uids = DB::table('users')->whereIn('distribution_channel_id', $distribution_channel_ids)->where('created_at', '>', $day_start)->select('id')->get()->pluck('id')->toArray();
        $register_orders = DB::table('orders')->whereIn('distribution_channel_id', $distribution_channel_ids)->where('created_at', '>', $day_start)
            ->whereIn('uid', $register_uids)->where('status', 'PAID')
            ->selectRaw('count(distinct uid) as register_pay_num, sum(price) as register_pay_amount')->first();
        $register_num = count($register_uids);
        $register_pay_num = getProp($register_orders, 'register_pay_num', 0);
        $register_pay_amount = getProp($register_orders, 'register_pay_amount', 0);

        $include_today = true;      // 是否包含今天统计
        // 周期筛选
        if ($date_range) {
            $date_range = explode(',', $date_range);
            if (count($date_range) == 2) {
                if ($date_range[0]) {
                    $start = date('Y-m-d', strtotime($date_range[0]));
                    $query->where('day', '>=', $start);
                }

                if ($date_range[1]) {
                    $end = date('Y-m-d', strtotime($date_range[1]));
                    if (strtotime($end) < strtotime(date('Y-m-d'))) {    // 如果获取的是今日之前的数据则不增加今日统计
                        $include_today = false;
                    }
                    $query->where('day', '<=', $end);
                }
            }
            $total = $query->first();
            $total_order_num = $include_today ? getProp($total, 'total_order_num', 0) + $total_order_num : getProp($total, 'total_order_num', 0);
            $paid_order_num = $include_today ? getProp($total, 'paid_order_num', 0) + $paid_order_num : getProp($total, 'paid_order_num', 0);
            $register_num = $include_today ? getProp($total, 'register_num', 0) + $register_num : getProp($total, 'register_num', 0);
            $register_pay_num = $include_today ? getProp($total, 'register_pay_num', 0) + $register_pay_num : getProp($total, 'register_pay_num', 0);
            $register_pay_amount = $include_today ? getProp($total, 'register_pay_amount', 0) + $register_pay_amount : getProp($total, 'register_pay_amount', 0);
            $total_pay_num = $include_today ? getProp($total, 'total_pay_num', 0) + $total_pay_num : getProp($total, 'total_pay_num', 0);
            $total_pay_amount = $include_today ? getProp($total, 'total_pay_amount', 0) + $total_pay_amount : getProp($total, 'total_pay_amount', 0);
        }

        return [
            'total_order_num'       => (int)$total_order_num,
            'paid_order_num'        => (int)$paid_order_num,
            'register_num'          => (int)$register_num,
            'register_pay_num'      => (int)$register_pay_num,
            'register_pay_amount'   => (float)sprintf('%.2f', $register_pay_amount),
            'register_pay_percent'  => $register_num ? (float)sprintf('%.2f', $register_pay_num / $register_num * 100) : 0,
            'total_pay_num'         => (int)$total_pay_num,
            'total_pay_amount'      => (float)sprintf('%.2f', $total_pay_amount),
            'distribution_channel'  => $distribution_channel_ids,
            'is_master'             => $sub_channel_ids ? 1 : 0,
        ];
    }

    // 站点统计日数据
    public function statisticsByDay($data, $all=false)
    {
        $data_source = getProp($data, 'data_source', 'current_channel');
        $distribution_channel_id = Site::getCurrentChannelId();
        $date_range              = getProp($data, 'date_range');

        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();

        if ($data_source == 'all_channel' && $sub_channel_ids) {        // 获取全站数据(主账号权限)
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
        }else {                  // 获取本站点数据(所有账号共有)
            $distribution_channel_ids = [$distribution_channel_id];
        }

        $query = DB::table('channel_day_statistics')->whereIn('distribution_channel_id', $distribution_channel_ids)->selectRaw("day,
            sum(total_order_num) as total_order_num,
            sum(paid_order_num) as paid_order_num,
            sum(register_num) as register_num,
            sum(register_pay_num) as register_pay_num,
            sum(register_pay_amount) as register_pay_amount,
            sum(total_pay_num) as total_pay_num,
            sum(total_pay_amount) as total_pay_amount")->groupBy('day');

        // 周期筛选
        if ($date_range) {
            $date_range = explode(',', $date_range);
            if (count($date_range) == 2) {
                if ($date_range[0]) {
                    $start = date('Y-m-d', strtotime($date_range[0]));
                    $query->where('day', '>=', $start);
                }

                if ($date_range[1]) {
                    $end = date('Y-m-d', strtotime($date_range[1]));
                    $query->where('day', '<=', $end);
                }
            }
        }

        if ($all) return $query->orderBy('day', 'desc')->get();

        return $query->orderBy('day', 'desc')->paginate();
    }

    /**
     * 导出站点统计日明细
     * @param $data
     * @throws ApiException
     */
    public function exportStatisticsByDay($data) {
        $list = $this->statisticsByDay($data, true);
        if (empty($list)) {
            Utils::throwError(ErrorConst::RECORD_NOT_EXIST);
        }

        $result = [];
        foreach ($list as $item) {
            $result[] = [
                'date'                      => getProp($item, 'day'),
                'total_order_num'           => (int)getProp($item, 'total_order_num'),
                'paid_order_num'            => (int)getProp($item, 'paid_order_num'),
                'register_num'              => (int)getProp($item, 'register_num'),
                'register_pay_num'          => (int)getProp($item, 'register_pay_num'),
                'register_pay_amount'       => (float)getProp($item, 'register_pay_amount'),
                'register_pay_percent'      => getProp($item, 'register_num') ? (float)sprintf('%.2f', getProp($item, 'register_pay_num') / getProp($item, 'register_num') * 100) : 0,
                'total_pay_num'             => (int)getProp($item, 'total_pay_num'),
                'total_pay_amount'          => (float)getProp($item, 'total_pay_amount'),
            ];
        }

        $headers = ['日期', '订单总数', '成功订单数', '新增注册用户', '新增注册充值人数', '新增注册充值金额(元)', '注册用户付费率(%)', '累计充值人数', '累计充值金额(元)'];

        exportFileCsv($headers, $result, '站点统计日数据-' . date('YmdHis'));
    }

    // 站点统计推广员日数据
    public function statisticsByDayForMaster($data, $all=false)
    {
        $distribution_channel_id = Site::getCurrentChannelId();
        $date_range              = getProp($data, 'date_range');

        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();

        if ($sub_channel_ids) {     // 主账号权限
            // 未筛选推广员则取全站数据
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
            $promote_user = getProp($data, 'promote_user');     // 推广员
            if ($promote_user) {    // 推广员筛选
                // 如果推广员符合子账号条件则取对应子站点数据
                $distribution_channel_id = DB::table('channel_users')->leftJoin('distribution_channels', 'distribution_channels.channel_user_id', 'channel_users.id')
                    ->where('channel_users.nickname', 'like', '%'.$promote_user.'%')->value('distribution_channels.id');
                if (in_array($distribution_channel_id, $distribution_channel_ids)) $distribution_channel_ids = [$distribution_channel_id];
                else $distribution_channel_ids = [];
            }
        }else {
            Utils::throwError('1002:你的账号暂无获取推广员数据权限');
        }

        $query = DB::table('channel_day_statistics')->leftJoin('distribution_channels', 'distribution_channels.id', 'channel_day_statistics.distribution_channel_id')
            ->leftJoin('channel_users', 'channel_users.id', 'distribution_channels.channel_user_id')
            ->whereIn('channel_day_statistics.distribution_channel_id', $distribution_channel_ids)->selectRaw("channel_day_statistics.day,
            channel_day_statistics.distribution_channel_id,
            channel_users.nickname,
            sum(channel_day_statistics.total_order_num) as total_order_num,
            sum(channel_day_statistics.paid_order_num) as paid_order_num,
            sum(channel_day_statistics.register_num) as register_num,
            sum(channel_day_statistics.register_pay_num) as register_pay_num,
            sum(channel_day_statistics.register_pay_amount) as register_pay_amount,
            sum(channel_day_statistics.total_pay_num) as total_pay_num,
            sum(channel_day_statistics.total_pay_amount) as total_pay_amount")->groupBy('channel_day_statistics.day', 'channel_users.nickname');

        // 周期筛选
        if ($date_range) {
            $date_range = explode(',', $date_range);
            if (count($date_range) == 2) {
                if ($date_range[0]) {
                    $start = date('Y-m-d', strtotime($date_range[0]));
                    $query->where('channel_day_statistics.day', '>=', $start);
                }

                if ($date_range[1]) {
                    $end = date('Y-m-d', strtotime($date_range[1]));
                    $query->where('channel_day_statistics.day', '<=', $end);
                }
            }
        }

        if ($all) return $query->orderBy('channel_day_statistics.day', 'desc')->orderBy('register_pay_amount', 'desc')->get();

        return $query->orderBy('channel_day_statistics.day', 'desc')->orderBy('register_pay_amount', 'desc')->paginate();
    }

    /**
     * 导出站点统计推广员日明细
     * @param $data
     * @throws ApiException
     */
    public function exportStatisticsByDayForMaster($data) {
        $list = $this->statisticsByDay($data, true);
        if (empty($list)) {
            Utils::throwError(ErrorConst::RECORD_NOT_EXIST);
        }

        $result = [];
        foreach ($list as $item) {
            $result[] = [
                'date'                      => getProp($item, 'day'),
                'promote_user'              => getProp($item, 'nickname'),
                'total_order_num'           => (int)getProp($item, 'total_order_num'),
                'paid_order_num'            => (int)getProp($item, 'paid_order_num'),
                'register_num'              => (int)getProp($item, 'register_num'),
                'register_pay_num'          => (int)getProp($item, 'register_pay_num'),
                'register_pay_amount'       => (float)getProp($item, 'register_pay_amount'),
                'register_pay_percent'      => getProp($item, 'register_num') ? (float)sprintf('%.2f', getProp($item, 'register_pay_num') / getProp($item, 'register_num') * 100) : 0,
                'total_pay_num'             => (int)getProp($item, 'total_pay_num'),
                'total_pay_amount'          => (float)getProp($item, 'total_pay_amount'),
            ];
        }

        $headers = ['日期', '推广员', '订单总数', '成功订单数', '新增注册用户', '新增注册充值人数', '新增注册充值金额(元)', '注册用户付费率(%)', '累计充值人数', '累计充值金额(元)'];

        exportFileCsv($headers, $result, '站点统计推广员日数据-' . date('YmdHis'));
    }

    // 站点统计阅数据
    public function statisticsByMonth($data, $all=false)
    {
        $data_source = getProp($data, 'data_source', 'current_channel');
        $distribution_channel_id = Site::getCurrentChannelId();
        $date_range              = getProp($data, 'date_range');

        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();

        if ($data_source == 'all_channel' && $sub_channel_ids) {        // 获取全站数据(主账号权限)
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
        }else{                  // 获取本站点数据(所有账号共有)
            $distribution_channel_ids = [$distribution_channel_id];
        }

        $query = DB::table('channel_day_statistics')->whereIn('distribution_channel_id', $distribution_channel_ids)->selectRaw("month,
            sum(total_order_num) as total_order_num,
            sum(paid_order_num) as paid_order_num,
            sum(register_num) as register_num,
            sum(register_pay_num) as register_pay_num,
            sum(register_pay_amount) as register_pay_amount,
            sum(total_pay_num) as total_pay_num,
            sum(total_pay_amount) as total_pay_amount")->groupBy('month');

        // 周期筛选
        if ($date_range) {
            $date_range = explode(',', $date_range);
            if (count($date_range) == 2) {
                if ($date_range[0]) {
                    $start = date('Ym', strtotime($date_range[0]));
                    $query->where('month', '>=', $start);
                }

                if ($date_range[1]) {
                    $end = date('Ym', strtotime($date_range[1]));
                    $query->where('month', '<=', $end);
                }
            }
        }

        if ($all) return $query->orderBy('month', 'desc')->get();

        return $query->orderBy('month', 'desc')->paginate();
    }

    /**
     * 导出站点统计月明细
     * @param $data
     * @throws ApiException
     */
    public function exportStatisticsByMonth($data) {
        $list = $this->statisticsByMonth($data, true);
        if (empty($list)) {
            Utils::throwError(ErrorConst::RECORD_NOT_EXIST);
        }

        $result = [];
        foreach ($list as $item) {
            $result[] = [
                'date'                      => getProp($item, 'month'),
                'total_order_num'           => (int)getProp($item, 'total_order_num'),
                'paid_order_num'            => (int)getProp($item, 'paid_order_num'),
                'register_num'              => (int)getProp($item, 'register_num'),
                'register_pay_num'          => (int)getProp($item, 'register_pay_num'),
                'register_pay_amount'       => (float)getProp($item, 'register_pay_amount'),
                'register_pay_percent'      => getProp($item, 'register_num') ? (float)sprintf('%.2f', getProp($item, 'register_pay_num') / getProp($item, 'register_num') * 100) : 0,
                'total_pay_num'             => (int)getProp($item, 'total_pay_num'),
                'total_pay_amount'          => (float)getProp($item, 'total_pay_amount'),
            ];
        }

        $headers = ['日期', '订单总数', '成功订单数', '新增注册用户', '新增注册充值人数', '新增注册充值金额(元)', '注册用户付费率(%)', '累计充值人数', '累计充值金额(元)'];

        exportFileCsv($headers, $result, '站点统计月数据-' . date('YmdHis'));
    }

    // 站点统计推广员月数据
    public function statisticsByMonthForMaster($data, $all=false)
    {
        $distribution_channel_id = Site::getCurrentChannelId();
        $date_range              = getProp($data, 'date_range');

        $uid = Site::getUid();
        $sub_channel_ids = DB::table('channel_user_mappings')->where('master_uid', $uid)->select('sub_channel_id')->get()->pluck('sub_channel_id')->toArray();

        if ($sub_channel_ids) {     // 主账号权限
            // 未筛选推广员则取全站数据
            $distribution_channel_ids = array_merge([$distribution_channel_id], $sub_channel_ids);
            $promote_user = getProp($data, 'promote_user');     // 推广员
            if ($promote_user) {    // 推广员筛选
                // 如果推广员符合子账号条件则取对应子站点数据
                $distribution_channel_id = DB::table('channel_users')->leftJoin('distribution_channels', 'distribution_channels.channel_user_id', 'channel_users.id')
                    ->where('channel_users.nickname', 'like', '%'.$promote_user.'%')->value('distribution_channels.id');
                if (in_array($distribution_channel_id, $distribution_channel_ids)) $distribution_channel_ids = [$distribution_channel_id];
                else $distribution_channel_ids = [];
            }
        }else {
            Utils::throwError('1002:你的账号暂无获取推广员数据权限');
        }

        $query = DB::table('channel_day_statistics')->leftJoin('distribution_channels', 'distribution_channels.id', 'channel_day_statistics.distribution_channel_id')
            ->leftJoin('channel_users', 'channel_users.id', 'distribution_channels.channel_user_id')
            ->whereIn('channel_day_statistics.distribution_channel_id', $distribution_channel_ids)->selectRaw("channel_day_statistics.month,
            channel_day_statistics.distribution_channel_id,
            channel_users.nickname,
            sum(channel_day_statistics.total_order_num) as total_order_num,
            sum(channel_day_statistics.paid_order_num) as paid_order_num,
            sum(channel_day_statistics.register_num) as register_num,
            sum(channel_day_statistics.register_pay_num) as register_pay_num,
            sum(channel_day_statistics.register_pay_amount) as register_pay_amount,
            sum(channel_day_statistics.total_pay_num) as total_pay_num,
            sum(channel_day_statistics.total_pay_amount) as total_pay_amount")->groupBy('channel_day_statistics.month', 'channel_users.nickname');

        // 周期筛选
        if ($date_range) {
            $date_range = explode(',', $date_range);
            if (count($date_range) == 2) {
                if ($date_range[0]) {
                    $start = date('Ym', strtotime($date_range[0]));
                    $query->where('channel_day_statistics.month', '>=', $start);
                }

                if ($date_range[1]) {
                    $end = date('Ym', strtotime($date_range[1]));
                    $query->where('channel_day_statistics.month', '<=', $end);
                }
            }
        }

        if ($all) return $query->orderBy('channel_day_statistics.month', 'desc')->orderBy('register_pay_amount', 'desc')->get();

        return $query->orderBy('channel_day_statistics.month', 'desc')->orderBy('register_pay_amount', 'desc')->paginate();
    }

    /**
     * 导出站点统计推广员月明细
     * @param $data
     * @throws ApiException
     */
    public function exportStatisticsByMonthForMaster($data) {
        $list = $this->statisticsByMonth($data, true);
        if (empty($list)) {
            Utils::throwError(ErrorConst::RECORD_NOT_EXIST);
        }

        $result = [];
        foreach ($list as $item) {
            $result[] = [
                'date'                      => getProp($item, 'month'),
                'promote_user'              => getProp($item, 'nickname'),
                'total_order_num'           => (int)getProp($item, 'total_order_num'),
                'paid_order_num'            => (int)getProp($item, 'paid_order_num'),
                'register_num'              => (int)getProp($item, 'register_num'),
                'register_pay_num'          => (int)getProp($item, 'register_pay_num'),
                'register_pay_amount'       => (float)getProp($item, 'register_pay_amount'),
                'register_pay_percent'      => getProp($item, 'register_num') ? (float)sprintf('%.2f', getProp($item, 'register_pay_num') / getProp($item, 'register_num') * 100) : 0,
                'total_pay_num'             => (int)getProp($item, 'total_pay_num'),
                'total_pay_amount'          => (float)getProp($item, 'total_pay_amount'),
            ];
        }

        $headers = ['日期', '推广员', '订单总数', '成功订单数', '新增注册用户', '新增注册充值人数', '新增注册充值金额(元)', '注册用户付费率(%)', '累计充值人数', '累计充值金额(元)'];

        exportFileCsv($headers, $result, '站点统计推广员月数据-' . date('YmdHis'));
    }
}