book_admin/application/api/controller/Channeldataapi.php

<?php
/**
 * Created by: PhpStorm
 * User: lytian
 * Date: 2020/3/11
 * Time: 14:44
 */

namespace app\api\controller;


use app\common\controller\Api;
use app\common\library\Redis;
use app\main\constants\AdminConstants;
use app\main\constants\ApiConstants;
use app\main\constants\CacheConstants;
use app\main\constants\UserConstants;
use app\main\helper\ArrayHelper;
use app\main\service\AdminService;
use app\main\service\ApiService;
use app\main\service\LogService;
use app\main\service\ReferralService;
use app\main\service\UserDdFlushService;
use app\main\service\UserService;
use app\main\service\VisitLimitService;
use think\Config;
use think\Db;
use think\Env;
use think\Exception;
use think\Log;

class Channeldataapi extends Api
{
    protected $clientConfig = [];
    protected $books = [];

    public function _initialize()
    {

        parent::_initialize(); // TODO: Change the autogenerated stub
        $this->checkSign();
    }

    /**
     * 指定日期的活跃用户信息
     */
    public function userinfo()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        $is_order = (int)$this->request->param('is_order', 0);
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $order = $is_order > 0 ? ' desc ' : ' asc ';
        $gt = $is_order > 0 ? ' < ' : ' > ';
        $idSql = " id {$gt} {$last_id} AND ";
        if ($is_order && !$last_id){
            $idSql = '';
        }
        try {
            $expandDbConfig = Config::get("expanddb");
            $expandDb = Db::connect($expandDbConfig);
            $sql = "SELECT * FROM channel_active_user WHERE {$idSql} channel_id = {$channel_id} AND active_date = '{$search_date}' ORDER BY id {$order} LIMIT 1000";
            $rows = $expandDb->query($sql);
            $list = [];
            //定位渠道和代理
            $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
            $adminIdsRes = Db::connect($mainSlaveDbConfig)->query("select admin.id, ac.appid, JSON_EXTRACT(ac.json,'$.authorizer_info.nick_name') as app_nickname from admin left join admin_config ac on ac.admin_id = admin.id where admin.id = {$channel_id}");
            if ($rows) {
                $whereSql = "";
                if ($this->clientConfig['is_all'] == '0') {
                    //不包含扣量
                    $whereSql = " AND is_black != 1 ";
                }
                $user_ids = array_column($rows, 'user_id');
                $groups = [];
                $mod = Config::get('db.user_num');
                foreach ($user_ids as $user_id) {
                    $dbId = $user_id % $mod;
                    if (isset($groups[$dbId])) {
                        $groups[$dbId][] = $user_id;
                    } else {
                        $groups[$dbId] = [$user_id];
                    }
                }
                foreach ($groups as $dbId => $user_id_arr) {
                    $user_id_str = implode(',', $user_id_arr);
                    $userDbConfig = $this->getDbDeploy($dbId, 'user');
                    $userTable = $userDbConfig['table'] . '.user';
                    unset($userDbConfig['table']);
                    $user_rows = Db::connect($userDbConfig)->query("SELECT id as user_id, nickname, openid, mobile, province, city, sex, is_subscribe,subscribe_time, operate_time as operatetime, createtime as registertime, channel_id,register_ip FROM {$userTable} WHERE id IN ({$user_id_str}) $whereSql");
                    foreach ($user_rows as $user_row) {
                        $item = array_merge($user_row, [
                            'authorizer_nickname' => str_replace(['"'], [''], $adminIdsRes[0]['app_nickname']),
                            'authorizer_appid' => $adminIdsRes[0]['appid']
                        ]);
                        //转换一下数据类型为string
                        $this->toString($item);
                        $list[] = $item;
                        unset($item);
                    }
                }
                $result['last_id'] = end($rows)['id'];
                $result['count'] = count($list);
                $result['push_time'] = time();
                $result['list'] = $list;
            }

        } catch (\Exception $e) {
            Log::error("接口错误: API: userinfo params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 订单记录
     */
    public function orders()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        $is_order = (int)$this->request->param('is_order', 0);
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $list = [];
        $order = $is_order > 0 ? ' desc ' : ' asc ';
        $gt = $is_order > 0 ? ' < ' : ' > ';
        $idSql = " id {$gt} {$last_id} AND ";
        if ($is_order && !$last_id){
            $idSql = '';
        }
        $userlist = [];
        //$channelInfo = model('adminConfig')->field('appid,json')->where('admin_id','=',$channel_id)->find();
        try {
            $selectBegin = strtotime($search_date);
            $selectEnd = $selectBegin + 86400;
            $whereSql = "";
            if ($this->clientConfig['is_all'] == '0') {
                //不包含扣量
                $whereSql = " AND deduct = 0 ";
            }

            $sql = "SELECT id, user_id, out_trade_no as merchant_id, transaction_id, type, money, state, business_line as `from`, createtime as create_time, finishtime as finish_time, book_id, referral_id, referral_id_permanent, admin_id as channel_id FROM orders FORCE INDEX(createtime) WHERE  {$idSql} admin_id in({$channel_id}) AND createtime >= {$selectBegin} AND createtime < {$selectEnd} {$whereSql} ORDER BY createtime {$order} LIMIT 1000";
            if (Config::get('polardb.orders_apply_polardb')) {
                $rows = Db::connect(Config::get('polardb'))->query($sql);
            } else {
                $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
                $rows = Db::connect($mainSlaveDbConfig)->query($sql);
            }
            if ($rows) {
                $userIds = array_unique(array_column($rows,'user_id'));
                $userlist = $this->getUserList($userIds);
                foreach ($rows as $row) {
                    $last_id = $row['id'];
                    $bookId = $row['book_id'];
                    $referralId = $row['referral_id'] ?: ($row['referral_id_permanent'] ?: 0);
                    unset($row['id']);
                    unset($row['book_id']);
                    unset($row['referral_id_permanent']);
                    $bookName = $bookTags = $referralUrl = '';
                    if ($bookId) {
                        list($bookName, $bookTags) = $this->getBook($bookId);
                    }
                    if ($referralId) {
                        $referralUrl = '/t/' . $referralId;
                    }
                    $row['referral_id'] = $referralId;
                    $item = array_merge($row, ['book_name' => $bookName, 'book_tags' => $bookTags, 'referral_url' => $referralUrl]);
                    $item['subscribe_time'] = isset($userlist[$row['user_id']]) ? $userlist[$row['user_id']]['subscribe_time'] : 0;
                    $item['user_createtime'] = isset($userlist[$row['user_id']]) ? $userlist[$row['user_id']]['createtime'] : '';
                    $item['openid'] = isset($userlist[$row['user_id']]) ? $userlist[$row['user_id']]['openid'] : '';
                    $item['register_ip'] = isset($userlist[$row['user_id']]) ? $userlist[$row['user_id']]['register_ip'] : '';
                    //转换一下数据类型为string
                    $this->toString($item);
                    $list[] = $item;
                    unset($item);
                }
            }
//            $result['appid'] = $channelInfo['appid'] ?? '';
//            $result['nick_name'] = $channelInfo['json']['authorizer_info']['nick_name'] ?? '';
            $result['last_id'] = $last_id;
            $result['list'] = $list;
            $result['count'] = count($list);
            $result['push_time'] = time();
        } catch (\Exception $e) {
            Log::error("接口错误: API: orders params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 客服消息
     */
    public function custom()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $list = [];
        try {
            $selectBegin = strtotime($search_date);
            $selectEnd = $selectBegin + 86400;
            $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
            $sql = "SELECT id as custom_id, admin_id as channel_id, title, sendtime as send_time, send_num, createtime as create_time,user_json FROM custom WHERE id > {$last_id} AND admin_id in({$channel_id}) AND sendtime >= {$selectBegin} AND sendtime < {$selectEnd} ORDER BY id ASC LIMIT 1000";
            $rows = Db::connect($mainSlaveDbConfig)->query($sql);
            if ($rows) {
                $adminIdsRes = Db::connect($mainSlaveDbConfig)->query("select admin.id, ac.appid, JSON_EXTRACT(ac.json,'$.authorizer_info.nick_name') as app_nickname from admin left join admin_config ac on ac.admin_id = admin.id where admin.id = {$channel_id}");
                $customIds = array_column($rows, 'custom_id');
                $customIdStr = implode(',', $customIds);
                $sql = "SELECT custom_id, sum(uv) as uv, sum(recharge_money) as recharge_money FROM custom_url_collect WHERE custom_id in({$customIdStr}) GROUP BY custom_id";
                $collectRes = Db::connect($mainSlaveDbConfig)->query($sql);
                $collectResGroup = ArrayHelper::index($collectRes, 'custom_id');

                $ids = array_column($rows, 'custom_id');
                $collectList = [];
                if (VisitLimitService::instance()->checkMigrated()) {
                    $apiResult = ApiService::instance()->getCollectFromApi(ApiConstants::API_CUSTOM, ['ids' => $ids])->data;
                    if ($apiResult) {
                        foreach ($apiResult as $index => $item) {
                            if (array_key_exists($item['cuId'], $collectList)) {
                                $collectList[$item['cuId']]['uv'] += $item['uv'];
                                $collectList[$item['cuId']]['recharge_money'] += $item['money'];
                            } else {
                                $collectList[$item['cuId']] = [
                                    'uv'             => $item['uv'],
                                    'recharge_money' => $item['money'],
                                ];
                            }
                        }
                    }
                }
                foreach ($rows as $row) {
                    $last_id = $row['custom_id'];
                    $userJson = json_decode($row['user_json'],true);
                    $row['is_all_user'] = $userJson['all'] ?? 0;
                    unset($row['user_json']);
                    $item = array_merge($row, [
                        'authorizer_nickname' => str_replace(['"'], [''], $adminIdsRes[0]['app_nickname']),
                        'authorizer_appid' => $adminIdsRes[0]['appid']
                    ]);
                    if (VisitLimitService::instance()->checkMigrated()) {
                        if (array_key_exists($row['custom_id'], $collectList)) {
                            $item['recharge_money'] = $collectList[$row['custom_id']]['recharge_money'];
                            $item['uv'] = $collectList[$row['custom_id']]['uv'];
                        } else {
                            $item['recharge_money'] = 0;
                            $item['uv'] = 0;
                        }
                    } else {
                        //拉取uv
                        if (isset($collectResGroup[$row['custom_id']])) {
                            $item['uv'] = $collectResGroup[$row['custom_id']]['uv'];
                        } else {
                            $item['uv'] = 0;
                        }
                        //拉取总充值金额
                        if (isset($collectResGroup[$row['custom_id']])) {
                            $item['recharge_money'] = $collectResGroup[$row['custom_id']]['recharge_money'];
                        } else {
                            $item['recharge_money'] = 0;
                        }
                    }
                    //转换一下数据类型为string
                    $this->toString($item);
                    $list[] = $item;
                    unset($item);
                }
            }
            $result['last_id'] = $last_id;
            $result['count'] = count($list);
            $result['push_time'] = time();
            $result['list'] = $list;
        } catch (\Exception $e) {
            Log::error("接口错误: API: userinfo params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    public function customnew()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $selectBegin = strtotime($search_date);
        $selectEnd = $selectBegin + 86400;
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        $sql = "SELECT
                  cu.id last_id, c.id AS custom_id, c.title, cu.idx,admin_id AS channel_id, c.sendtime AS send_time, send_num, c.createtime AS create_time, book_id,
                  book_name, push_type, uv, recharge_money,recharge_orders,c.user_json
                FROM
                    custom c
                LEFT JOIN custom_url cu on c.id=cu.custom_id && cu.id >{$last_id}
                LEFT JOIN custom_url_collect cc on (cu.custom_id=cc.custom_id && cu.idx=cc.idx)
                where c.admin_id in({$channel_id}) AND c.sendtime >= {$selectBegin} AND c.sendtime < {$selectEnd} and cu.id is not null";
        $rows = Db::connect($mainSlaveDbConfig)->query($sql);
        if ($rows){
            $adminIdsRes = Db::connect($mainSlaveDbConfig)->query("select admin.id,admin.nickname, ac.appid, JSON_EXTRACT(ac.json,'$.authorizer_info.nick_name') as app_nickname from admin left join admin_config ac on ac.admin_id = admin.id where admin.id = {$channel_id}");
            $ids = array_column($rows, 'custom_id');
            $collectList = [];
            if (VisitLimitService::instance()->checkMigrated()) {
                $apiResult = ApiService::instance()->getCollectFromApi(ApiConstants::API_CUSTOM, ['ids' => $ids])->data;
                if ($apiResult) {
                    foreach ($apiResult as $index => $item) {
                        if (!array_key_exists($item['cuId'], $collectList)) {
                            $collectList[$item['cuId']] = [];
                        }
                        $collectList[$item['cuId']][$item['idx']] = [
                            'uv'     => $item['uv'],
                            'money'  => $item['money'],
                            'orders' => $item['orders'],
                        ];
                    }
                }
            }
            foreach ($rows as &$row) {
                $userJson = json_decode($row['user_json'],true);
                $row['is_all_user'] = $userJson['is_all'] ?? 0;
                $row['channel_name'] = $adminIdsRes[0]['nickname'];
                $row['authorizer_nickname'] = str_replace(['"'], [''], $adminIdsRes[0]['app_nickname']);
                $row['authorizer_appid'] = $adminIdsRes[0]['appid'];
                if ($find = ArrayHelper::array_find($collectList, $row['custom_id'].'.'.$row['idx'])) {
                    $row['uv'] = $collectList[$row['custom_id']][$row['idx']]['uv'];
                    $row['recharge_money'] = $collectList[$row['custom_id']][$row['idx']]['money'];
                    $row['recharge_orders'] = $collectList[$row['custom_id']][$row['idx']]['orders'];
                }
                unset($row['user_json']);
            }
        }
        $result['last_id'] = (int)end($rows)['last_id'];
        $result['count'] = count($rows);
        $result['push_time'] = time();
        $result['list'] = $rows;
        $this->result($result);

    }


    /**
     * 充值记录
     */
    public function recharge()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $user_id = (int)$this->request->param('user_id');
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        if (empty($user_id)) {
            $this->result(['error_code' => 20001, 'error_msg' => 'user_id参数必填'], 0, '', "json");
            $this->result([], 20001, 'user_id参数必填', "json");
        }
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $list = [];
        try {
            $selectBegin = strtotime($search_date);
            $selectEnd = $selectBegin + 86400;
            $userDbConfig = $this->getDbDeploy($user_id, 'user');
            $rechargeTable = $userDbConfig['table'] . '.recharge';
            $userTable = $userDbConfig['table'] . '.user';
            unset($userDbConfig['table']);
            $userRow = Db::connect($userDbConfig)->query("select id, is_black, flush_state from {$userTable} where id = {$user_id} and channel_id = {$channel_id}");
            if (empty($userRow)) {
                $action = $this->request->action();
                $this->setLimit($action);
                $this->result($result);
            } else {
                if ($this->clientConfig['is_all'] == '0') {
                    if ($userRow[0]['is_black'] == AdminConstants::ADMIN_KL_BLACK_YES) {
                        Log::info("匹配到黑名单用户：user_id: {$user_id} channel_id: {$channel_id}");
                        $action = $this->request->action();
                        $this->setLimit($action);
                        $this->result($result);
                    }
                }
                //是否有扣量订单

                $sql = "SELECT count(1) as total FROM orders  WHERE user_id = {$user_id} AND state = '1' AND deduct = 1";

                if (Config::get('polardb.orders_apply_polardb')) {
                    $countRes = Db::connect(Config::get('polardb'))->query($sql);
                } else {
                    $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
                    $countRes = Db::connect($mainSlaveDbConfig)->query($sql);
                }
                if ($userRow[0]['flush_state'] != UserConstants::USER_FLUSH_STATE_ADMIN) {
                    UserDdFlushService::instance()->checkUserFlushState($user_id);
                    UserDdFlushService::instance()->flushVip($user_id);
                    if ($countRes[0]['total'] > 0) {
                        UserDdFlushService::instance()->flushUserConsume($user_id);
                    }
                    UserService::instance()->getUserModel()->setConnect($user_id)->update(['flush_state' => UserConstants::USER_FLUSH_STATE_ADMIN], ['id' => $user_id]);
                    $cacheUser = CacheConstants::getUserCacheKey($user_id);
                    Redis::instance()->del($cacheUser);
                }
                //拉取充值记录
                $whereSql = "";
                if ($this->clientConfig['is_all'] == '0') {
                    //过滤dd
                    $whereSql = " AND dd = 0 ";
                }
                $rechargeRows = Db::connect($userDbConfig)->query("SELECT id as recharge_id, user_id, type, kandian as shubi, free_kandian as free_shubi, free_endtime, day, hour, createtime as create_time, vip_starttime FROM {$rechargeTable} WHERE id > {$last_id} AND user_id in({$user_id}) AND createtime >= {$selectBegin} AND createtime < {$selectEnd} {$whereSql} ORDER BY id asc limit 1000");
                if ($rechargeRows) {
                    foreach ($rechargeRows as $rechargeRow) {
                        $last_id = $rechargeRow['recharge_id'];
                        $vipEndtime = '';
                        if ($rechargeRow['day'] || $rechargeRow['hour']) {
                            $day = $rechargeRow['day'];
                            $hour = $rechargeRow['hour'];
                            $vipEndtime = $rechargeRow['vip_starttime'] + 86400 * $day + $hour * 3600;
                        }
                        $item = array_merge($rechargeRow, ['vip_endtime' => $vipEndtime]);
                        $item['channel_id'] = $channel_id;
                        $this->toString($item);
                        $list[] = $item;
                        unset($item);
                    }
                }
                $result['last_id'] = $last_id;
                $result['count'] = count($list);
                $result['push_time'] = time();
                $result['list'] = $list;
            }
        } catch (\Exception $e) {
            Log::error("接口错误: API: recharge params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 消费
     */
    public function consume()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $user_id = (int)$this->request->param('user_id');
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        if (empty($user_id)) {
            $this->result(['error_code' => 20001, 'error_msg' => 'user_id参数必填'], 0, '', "json");
            $this->result([], 20001, 'user_id参数必填', "json");
        }
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $list = [];
        try {
            $selectBegin = strtotime($search_date);
            $selectEnd = $selectBegin + 86400;
            $userDbConfig = $this->getDbDeploy($user_id, 'user');
            $shardDbConfig = $this->getDbDeploy($user_id, 'shard');
            $consumeTable = $shardDbConfig['table'] . '.consume';
            $userTable = $userDbConfig['table'] . '.user';
            unset($userDbConfig['table']);
            unset($shardDbConfig['table']);
            $userRow = Db::connect($userDbConfig)->query("select id, is_black, flush_state from {$userTable} where id = {$user_id} and channel_id = {$channel_id}");
            if (empty($userRow)) {
                $action = $this->request->action();
                $this->setLimit($action);
                $this->result($result);
            } else {
                if ($this->clientConfig['is_all'] == '0') {
                    if ($userRow[0]['is_black'] == AdminConstants::ADMIN_KL_BLACK_YES) {
                        Log::info("匹配到黑名单用户：user_id: {$user_id} channel_id: {$channel_id}");
                        $action = $this->request->action();
                        $this->setLimit($action);
                        $this->result($result);
                    }
                }
                //是否有扣量订单

                $sql = "SELECT count(1) as total FROM orders WHERE user_id = {$user_id} AND state = '1' AND deduct = 1";
                if (Config::get('polardb.orders_apply_polardb')) {
                    $countRes = Db::connect(Config::get('polardb'))->query($sql);
                } else {
                    $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
                    $countRes = Db::connect($mainSlaveDbConfig)->query($sql);
                }
                if ($userRow[0]['flush_state'] != UserConstants::USER_FLUSH_STATE_ADMIN) {
                    UserDdFlushService::instance()->checkUserFlushState($user_id);
                    UserDdFlushService::instance()->flushVip($user_id);
                    if ($countRes[0]['total'] > 0) {
                        UserDdFlushService::instance()->flushUserConsume($user_id);
                    }
                    UserService::instance()->getUserModel()->setConnect($user_id)->update(['flush_state' => UserConstants::USER_FLUSH_STATE_ADMIN], ['id' => $user_id]);
                    $cacheUser = CacheConstants::getUserCacheKey($user_id);
                    Redis::instance()->del($cacheUser);
                }
                //拉取充值记录
                $whereSql = "";
                if ($this->clientConfig['is_all'] == '0') {
                    //过滤dd
                    $whereSql = " AND free_kandian+kandian-dd_free_kandian-dd_kandian>0 ";
                }
                $consumeSql = "SELECT id as consume_id, user_id, createtime as consume_time, book_id, book_name, chapter_id, chapter_name, (kandian - dd_kandian)  as shubi, (free_kandian - dd_free_kandian) as free_shubi  FROM {$consumeTable} WHERE id > {$last_id} AND user_id in ({$user_id}) AND createtime >= {$selectBegin} AND createtime < {$selectEnd} {$whereSql} ORDER BY id asc LIMIT 1000";
                $consumeRows = Db::connect($shardDbConfig)->query($consumeSql);
                if ($consumeRows) {
                    foreach ($consumeRows as $consumeRow) {
                        $last_id = $consumeRow['consume_id'];
                        $item = array_merge($consumeRow);
                        $item['channel_id'] = $channel_id;
                        $this->toString($item);
                        $list[] = $item;
                        unset($item);
                    }
                }
                $result['last_id'] = $last_id;
                $result['count'] = count($list);
                $result['push_time'] = time();
                $result['list'] = $list;
            }
        } catch (\Exception $e) {
            Log::error("接口错误: API: consume params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 最近阅读记录
     */
    public function recent()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $user_id = (int)$this->request->param('user_id');
        $channel_id = (int)$this->request->param('channel_id');
        $search_date = $this->request->param('search_date');
        $last_id = (int)$this->request->param('last_id', 0);
        if (empty($user_id)) {
            $this->result(['error_code' => 20001, 'error_msg' => 'user_id参数必填'], 0, '', "json");
            $this->result([], 20001, 'user_id参数必填', "json");
        }
        if (empty($channel_id)) {
            $this->result([], 20001, 'channel_id参数必填', "json");
        }
        if (empty($search_date)) {
            $this->result([], 20001, 'search_date参数必填', "json");
        }
        $list = [];
        try {
            $selectBegin = strtotime($search_date);
            $selectEnd = $selectBegin + 86400;
            $userDbConfig = $this->getDbDeploy($user_id, 'user');
            $shardDbConfig = $this->getDbDeploy($user_id, 'shard');
            $recentTable = $shardDbConfig['table'] . '.user_recently_read';
            $userTable = $userDbConfig['table'] . '.user';
            unset($userDbConfig['table']);
            unset($shardDbConfig['table']);
            $userRow = Db::connect($userDbConfig)->query("select id, is_black, flush_state from {$userTable} where id = {$user_id} and channel_id = {$channel_id}");
            if (empty($userRow)) {
                $action = $this->request->action();
                $this->setLimit($action);
                $this->result($result);
            } else {
                if ($this->clientConfig['is_all'] == '0') {
                    if ($userRow[0]['is_black'] == AdminConstants::ADMIN_KL_BLACK_YES) {
                        Log::info("匹配到黑名单用户：user_id: {$user_id} channel_id: {$channel_id}");
                        $action = $this->request->action();
                        $this->setLimit($action);
                        $this->result($result);
                    }
                }
                //拉取阅读记录
                $recentSql = "SELECT id as recent_id, user_id, book_id, chapter_id, chapter_name, flag, updatetime as update_time FROM {$recentTable} WHERE id > {$last_id} AND user_id in ({$user_id}) AND updatetime >= {$selectBegin} AND updatetime < {$selectEnd} ORDER BY id asc LIMIT 1000";
                $recentRows = Db::connect($shardDbConfig)->query($recentSql);
                if ($recentRows) {
                    foreach ($recentRows as $recentRow) {
                        $last_id = $recentRow['recent_id'];
                        list($bookName, $bookTags) = $this->getBook($recentRow['book_id']);
                        unset($bookTags);
                        $item = array_merge($recentRow, ['book_name' => $bookName]);
                        unset($bookName);
                        $this->toString($item);
                        $list[] = $item;
                        unset($item);
                    }
                }
                unset($recentRows);
                $result['last_id'] = $last_id;
                $result['count'] = count($list);
                $result['push_time'] = time();
                $result['list'] = $list;
            }
        } catch (\Exception $e) {
            Log::error("接口错误: API: recent params:" . json_encode($this->request->param()) . " error" . $e->getMessage());
        }
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 验证签名
     */
    private function checkSign()
    {
        $params = $this->request->param();
        $signaure = $params['signaure'] ?? '';
        $client_id = $params['client_id'] ?? '';
        $timestamp = $params['timestamp'] ?? '';
        $nonce = $params['nonce'] ?? '';

        if (!$signaure || !$client_id || !$timestamp || !$nonce) {
            $this->result([], 11000, '缺少参数', 'json');
        }
        $this->clientConfig = $clientConfig = model("DataApiConfig")->getInfo($client_id);
        //限制访问频率
        $action = $this->request->action();
        $this->getLimit($action);
        //还需验证后台是否关闭接口
        if (empty($clientConfig)) {
            $this->result([], 10005, 'client_id不存在', 'json');
        }
        $token = $this->clientConfig['token'];
        if ($signaure === sha1($token . $timestamp . $client_id . $nonce)) {

            if ($clientConfig['status'] != '1') {
                $this->result([], 10003, '接口已禁用', 'json');
            }
            $configChannelIds = array_filter(array_unique(explode(',', $this->clientConfig['channel_ids'])));
            if (isset($this->clientConfig['vip_id']) && !empty($this->clientConfig['vip_id'])){//查看vip下的权限
                $vipChannelIds = $this->getChannelIdByVip($this->clientConfig['vip_id']);
                $configChannelIds = array_merge($vipChannelIds,$configChannelIds);
            }
            $channel_id = (int)$this->request->param('channel_id');
            $vip_id = (int)$this->request->param('vip_id');
            if( $vip_id && strstr($this->clientConfig['vip_id'],(string)$vip_id) !== false){
                return;
            }
            if ( !in_array($channel_id, $configChannelIds)) {
                $this->result([], 10006, '没有权限查询该渠道信息', "json");
            }
        } else {
            $this->result([], 10001, '签名验证失败', 'json');
        }
    }

    /**
     * 返回结果
     * @param mixed $data
     * @param int $code
     * @param string $msg
     */
    public function result($data, $code = 0, $msg = '', $type = '', array $header = [])
    {
        exit(json_encode(
            [
                'error_code' => $code,
                'error_msg' => $msg,
                'data' => $data
            ],
            320
        ));
    }

    /**
     * 从库配置
     * @param $param
     * @param string $deploy
     * @return array|mixed
     */
    private function getDbDeploy($param, $deploy = 'shard')
    {
        $db = Config::get('db');
        $mod = $param % $db[$deploy . '_num'];
        $mod = abs($mod);
        $list = explode(';', $db[$deploy . '_list']);

        foreach ($list as $item) {
            $con = explode(':', $item); // 0=0-191库编号 1=192.168.1.149主IP 2=3306主端口 3=192.168.1.150从IP 4=3306从端口
            if (count($con) >= 3) {
                $c = explode('-', $con[0]); //库编号  0开始 1结束
                if (count($c) >= 2) {
                    if ($c[0] <= $mod && $mod <= $c[1]) {
                        $database = Config::get('database');
                        if (count($con) >= 5) { //开启主从 & 带主从配置
                            $database['deploy'] = 1;
                            $database['rw_separate'] = true;
                            $database['hostname'] = $con[1] . ',' . $con[3]; //192.168.1.149,192.168.1.150
                            $database['hostport'] = $con[2] . ',' . $con[4]; //3306,3306
                        } else { //只有主库
                            $database['hostname'] = $con[1];
                            $database['hostport'] = $con[2];
                        }

                        $database['database'] = 'mysql';
                        $database['table'] = str_replace('$mod', $mod, $db[$deploy . '_database']);
                        return $database;
                    }
                }
            }
        }

        return [];
    }

    /**
     * 获取主库的从库配置  从库不存在返回主库
     * @return array
     */
    private function getMainSlaveDbConfig()
    {
        $hostArr = explode(',', Env::get('database.admin_hostname'));
        $portArr = explode(',', Env::get('database.admin_hostport', '3306,3306'));
        //默认主库
        $mainDbConfig = array_merge(Config::get("database"), ['hostname' => $hostArr[0], 'hostport' => $portArr[0]]);
        if (count($hostArr) >= 2) {
            //从库
            $mainDbConfig = array_merge(Config::get("database"),
                ['hostname' => $hostArr[1], 'hostport' => $portArr[1]]);
        }

        return $mainDbConfig;
    }

    /**
     * 转换数据
     * @param $data
     */
    private function toString(&$data)
    {
        if (is_array($data)) {
            array_walk($data, function (&$val, $key) {
                $val = strval($val);
            });
        }
    }

    /**
     * 返回书籍
     * @param $bookId
     * @return mixed
     * @throws Exception
     * @throws \think\db\exception\BindParamException
     * @throws \think\exception\PDOException
     */
    private function getBook($bookId)
    {
        if (!isset($this->books[$bookId])) {
            //查询
            $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
            $bookSql = "select book.name, bc.name as category_name from book left join book_category as bc on bc.id = book.book_category_id where book.id = {$bookId}";
            $bookRow = Db::connect($mainSlaveDbConfig)->query($bookSql);
            $this->books[$bookId] = array_values($bookRow[0]);
        }

        return $this->books[$bookId];
    }

    /**
     * 增加限频次数
     * @param $apiMethod
     * @return int
     */
    private function setLimit($apiMethod)
    {
        $key = "ADL:" . $this->clientConfig['client_id']. ":" . $apiMethod;
        if (Redis::instance()->incrBy($key, 1) == 1){
            Redis::instance()->expire($key, 60);
        }
        return true;
    }

    /**
     * 判断限频次数
     * @param $apiMethod
     * @return bool
     */
    private function getLimit($apiMethod)
    {
        //限频
        $key = "ADL:" . $this->clientConfig['client_id']. ":" . $apiMethod;
        $times = (int)Redis::instance()->get($key);
        if ($times >= $this->clientConfig['access_limit']) {
            $this->result([], 10004, "接口达到限频 每分钟/{$this->clientConfig['access_limit']}次", 'json');
        }
        return true;
    }

    private function getUserList($userIds)
    {
        $groups = [];
        $mod = Config::get('db.user_num');
        foreach ($userIds as $userId) {
            $dbId = $userId % $mod;
            if (isset($groups[$dbId])) {
                $groups[$dbId][] = $userId;
            } else {
                $groups[$dbId] = [$userId];
            }
        }
        $userList = [];
        foreach ($groups as $dbId => $userIdArr) {
            $userIdStr = implode(',', $userIdArr);
            $userDbConfig = $this->getDbDeploy($dbId, 'user');
            $userTable = $userDbConfig['table'] . '.user';
            unset($userDbConfig['table']);
            $userRows = Db::connect($userDbConfig)->query("SELECT id, openid,subscribe_time,createtime,register_ip FROM {$userTable} WHERE id IN ({$userIdStr})");
            foreach ($userRows as $userRow) {
                $userList[$userRow['id']] = $userRow;
            }
            unset($userRows);
        }
        unset($groups);
        return $userList;

    }
    //20200317==================================
    /**
     * @param $vipId
     * @return array
     */
    private function getChannelIdByVip($vipId)
    {
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
       $channelIds = Db::connect($mainSlaveDbConfig)->table('vip_admin_bind')
           ->force('uk_admin_relationship_bind')
           ->field('admin_id_master,admin_id_slave')
           ->where(['flag'=>1])
           ->whereIn('admin_id_master',$vipId)
           ->select();
       if (!empty($channelIds)){
           $channelIds = array_column($channelIds,'admin_id_slave');
       }
       return (array)$channelIds;
    }

    /**
     * 书单统计
     */
    public function getBookList()
    {
        $channelId = $this->request->param('channel_id');
        $lastId = (int)$this->request->param('last_id',0);
        $num = (int)$this->request->param('num',200);
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        $data = Db::connect($mainSlaveDbConfig)->table('book_list')
            ->alias('bl')
            ->field('booklist_collect.id as last_id,bl.id,bl.title,bl.admin_id as channel_id,booklist_collect.idx as book_id,book.name book_name,booklist_collect.uv,booklist_collect.orders_num,booklist_collect.recharge_money,booklist_collect.createtime')
            ->join('booklist_collect','bl.admin_id=booklist_collect.admin_id','left')
            ->join('book','booklist_collect.idx=book.id','left')
            ->where('bl.admin_id',$channelId)
            ->where('booklist_collect.id','>',$lastId)
            ->order('booklist_collect.id','asc')
            ->limit($num)
            ->select();
//        $sql = Db::table('vip_admin_bind')->getLastSql();
//        print_r($sql);
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($data);

    }

    /**
     * 渠道公众号列表
     */
    public function channelList()
    {
        $vip = $this->request->param('vip_id');
        $lastId = (int)$this->request->param('last_id',0);
        $num = (int)$this->request->param('num',1000);
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        $channelList =Db::connect($mainSlaveDbConfig)->table('vip_admin_bind')
            ->field('admin.id as channel_id,username,nickname,admin_config.appid as app_id,json')
            ->alias('vab')
            ->join('admin','vab.admin_id_slave=admin.id')
            ->join('admin_config','vab.admin_id_slave=admin_config.admin_id','inner')
            ->where(['vab.admin_id_master'=>$vip,'flag'=>1])
            ->where('admin_config.admin_id','>',$lastId)
            ->order('admin.id','asc')
            ->limit($num)
            ->select();
        if (!empty($channelList)){
            foreach ($channelList as $k=>&$v){
                $wx_json = json_decode($v['json'],true);
                $v['wx_nickname'] = !empty($wx_json) && isset($wx_json['authorizer_info']['nick_name']) ? $wx_json['authorizer_info']['nick_name']: '';
                unset($v['json']);
            }
        }
        $result['count'] = count($channelList);
        $result['list'] = $channelList;
        $result['last_id'] = (int)end($channelList)['channel_id'];
//        $sql = Db::connect($mainSlaveDbConfig)->table('vip_admin_bind')->getLastSql();
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);
    }

    /**
     * 渠道的粉丝统计
     */
    public function userCollect()
    {
        $channelId = $this->request->param('channel_id');
        $beginDate = (int)$this->request->param('begin_date');
        $endDate = (int)$this->request->param('end_date');
        $type = (int)$this->request->param('type',0);
        $max = (strtotime($endDate) - strtotime($beginDate))/(3600*24) > 100 ? false : true;
        if ( !$max ){
            $this->error('时间跨度超过100天');
        }
        $where = ['admin_id'=>$channelId];
        if ($type){
            $where['type'] = $type;
        }
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();

        $data = Db::connect($mainSlaveDbConfig)->table('user_collect')->alias('uc')
            ->field('admin_id as channel_id,type,increase,increase_fllow,unfollow_num,createdate')
            ->where($where)
            ->whereBetween('createdate',$beginDate.','.$endDate)
            ->select();
        $result['count'] = count($data);
        $result['list'] = $data;
        $action = $this->request->action();
        $this->setLimit($action);
        $this->result($result);

    }

    /**
     *
     *获取书单链接的接口
     */
    public function bookUrlList()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channelId = $this->request->param('channel_id');
        $lastId = (int)$this->request->param('last_id',0);
        $url = getCurrentDomain($channelId, "/index/index/booklistch");
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        $sql = "SELECT id,admin_id channel_id,title,name,status,createtime FROM `book_list_ch` WHERE  `admin_id` ={$channelId} and id > {$lastId} ORDER BY `id`  asc LIMIT 200";
        $con = Db::connect($mainSlaveDbConfig)->table('book_list_ch')->query($sql);
        if(!empty($con))foreach($con as $k=>&$v){
            $v['url'] = $url.'?id='.$v['id'];
        }
        $result['count'] = count($con);
        $result['list'] = $con;
        $result['last_id'] = (int)end($con)['id'];
        $action = $this->request->action();
        $this->setLimit($action);
        $this->success($result);
    }

    public function activityList()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channelId = $this->request->param('channel_id');
        $lastId = (int)$this->request->param('last_id',0);
        $typeId = (int)$this->request->param('type_id',1);
        $today = date('Ymd',time());
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        $activity_config = Config::get('site.activity_config');
        $con = Db::connect($mainSlaveDbConfig)->table('activity')
            ->join('activity_collect c','activity.id=c.aid and c.createdate='.$today.' and c.admin_id='.$channelId,'left')
            ->join('activity_collect_all a','activity.id=a.aid and a.admin_id='.$channelId,'left')
            ->join('activity_shortlink s','activity.id= s.aid and s.admin_id='.$channelId,'left')
            ->where('activity.type',$typeId)
            ->where('activity.id','>',$lastId)
            ->field('activity.id,activity.name,activity.copywriting,activity.starttime,activity.endtime,activity.createtime,activity.admin_id channel_id,config_id,c.money,a.money as allmoney,s.qq as qq, s.sina as sina,c.recharge_count,a.recharge_count as arecharge_count,"" as url')
            ->order('activity.id','asc')
            ->limit(500)
            ->select();
        if(!empty($con))foreach($con as $k=>&$v){
            if (isset($activity_config['config'][$v['config_id']]) && $activity_config['config'][$v['config_id']]['status'] == 1) {
                $v['url'] = getCurrentDomain($channelId, '/s/' . $v['id'] . '/r/' . $activity_config['config'][$v['config_id']]['resource_id']);
            }
        }
        $result['count'] = count($con);
        $result['list'] = $con;
        $result['last_id'] = (int)end($con)['id'];
        $action = $this->request->action();
        $this->setLimit($action);
        $this->success($result);


    }

    /**
     * 获取推广链接接口
     */
    public function getReferral()
    {
        $result = [
            'last_id' => 0,
            'count' => 0,
            'push_time' => time(),
            'list' => []
        ];
        $channelId = $this->request->param('channel_id');
        $lastId = (int)$this->request->param('last_id',0);
        $mainSlaveDbConfig = $this->getMainSlaveDbConfig();
        if (VisitLimitService::instance()->checkMigratedV2()) {
            $con = Db::connect($mainSlaveDbConfig)->table('referral')
                ->join('admin','referral.admin_id=admin.id')
                ->join('admin_config','admin.id=admin_config.admin_id')
                ->join('book','referral.book_id=book.id')
                ->field('referral.id,referral.type,referral.name,admin.id channel_id,admin.username,admin.nickname,admin_config.appid,`admin_config`.`json` AS `wx_json`,referral.source_url,book.name as book_name,referral.chapter_name,referral.guide_chapter_idx,referral.name,referral.createtime,referral.push')
                ->where('admin.id',$channelId)
                ->where('referral.id','>',$lastId)
                ->select();
            if ($con) {
                $ids = array_column($con, 'id');
                $data = ReferralService::instance()->getReferralCollectFromApi($ids)->data;
                foreach ($con as $index=>$item) {
                    $con[$index]['uv'] = $data[$item['id']]['uv'];
                    $con[$index]['money'] = $data[$item['id']]['money'];
                    $con[$index]['orders_num'] = $data[$item['id']]['order_nums'];
                    $con[$index]['follow'] = $data[$item['id']]['follow'];
                }
            }
        } else {
            $con = Db::connect($mainSlaveDbConfig)->table('referral')
                ->join('admin','referral.admin_id=admin.id')
                ->join('admin_config','admin.id=admin_config.admin_id')
                ->join('book','referral.book_id=book.id')
                ->field('referral.id,referral.type,referral.name,admin.id channel_id,admin.username,admin.nickname,admin_config.appid,`admin_config`.`json` AS `wx_json`,referral.source_url,book.name as book_name,referral.chapter_name,referral.guide_chapter_idx,referral.name,referral.uv,referral.money,referral.orders_num,referral.createtime,referral.push,referral.follow')
                ->where('admin.id',$channelId)
                ->where('referral.id','>',$lastId)
                ->select();
        }
        if (!empty($con))foreach ($con as $k =>&$v){
            $wx_json = json_decode($v['wx_json'],true);
            $v['wx_nick_name'] = !empty($wx_json) && isset($wx_json['authorizer_info']['nick_name']) ? $wx_json['authorizer_info']['nick_name']: '';
            unset($v['wx_json']);
            $type = (int)$v['type'];
            switch ($type){
                case 1:
                    $c = $v['guide_chapter_idx'] ? $v['guide_chapter_idx'] : '关注章节';
                    $v['entry_page'] = $v['book_name'].'-'.$v['chapter_name'].$c;
                    break;
                case 2:
                    $v['entry_page'] = '书城首页推广';
                    break;
                case 3:
                    $c = $v['guide_chapter_idx'] ? $v['guide_chapter_idx'] : '关注章节';
                    $v['entry_page'] = '落地推广页-'.$v['book_name'].'-'.$v['chapter_name'].$c;
                    break;
                default:
                    $v['entry_page'] = '';
                    break;

            }
        }
        $result['count'] = count($con);
        $result['list'] = $con;
        $result['last_id'] = (int)end($con)['id'];
        $action = $this->request->action();
        $this->setLimit($action);
        $this->success($result);
    }



    /**
     * 获取推广链接充值金额
     * @return \think\response\Json
     */
    public function getReferralRecharge()
    {
        $ids = $this->request->param('ids');
        $vip_id = $this->request->param('vip_id');
        $channel_id = $this->request->param('channel_id');
        if (!$ids) {
            $this->error("ids参数缺失");
        } else {
            $ids = explode(',', $ids);
        }
        if (count($ids) == 1) {
            $where = [
                'id' => $ids[0]
            ];
        } else {
            $where = [
                'id' => ['in', $ids]
            ];
        }

        if (!$channel_id && $vip_id) {
            $channel_id = (array)AdminService::instance()->getAdminId($vip_id)->data;
            if (count($channel_id) == 1) {
                if ($channel_id[0] == -1) {
                    $this->error("未绑定渠道");
                } else {
                    $where['admin_id'] = $channel_id[0];
                }
            } else {
                $where['admin_id'] = ['in', $channel_id];
            }
        }
        if (VisitLimitService::instance()->checkMigratedV2()) {
            $referralCost = $data = model('Referral')->where($where)
                ->column('id,cost');
            $data = ReferralService::instance()->getReferralCollectFromApi(array_keys($referralCost))->data;
            foreach ($data as $id => $item) {
                $tmp = [
                    'id'            => $id,
                    'money'         => $item['money'],
                    'day_money'     => (int)$item['daymt'],
                    'uv'            => (int)$item['uv'],
                    'day_uv'        => (int)$item['dayuv'],
                    'subscribe'     => (int)$item['follow'],
                    'day_subscribe' => (int)$item['dayut'],
                    'cost'          => $referralCost[$id],
                ];
                $return['list'][] = $tmp;
            }
        } else {
            $data = model('Referral')->where($where)
                ->field('id,money,uv,follow,cost')
                ->select();

            $return = [
                'list'  => [],
                'total' => count($data),
            ];
            foreach ($data as $item) {
                $dayMTkey = "M-T:".$item['id'].":".date("d");
                $tmp = [
                    'id'            => $item['id'],
                    'money'         => $item['money'],
                    'day_money'     => (int)Redis::instance()->get($dayMTkey) ? round(Redis::instance()->get($dayMTkey) / 100, 2) : 0,
                    'uv'            => (int)$item['uv'],
                    'day_uv'        => (int)Redis::instance()->get(CacheConstants::getReadOfReferralIdKey($item['id'])),
                    'subscribe'     => (int)$item['follow'],
                    'day_subscribe' => (int)Redis::instance()->get(CacheConstants::getSubscribeOfReferralIdKey($item['id'])),
                    'cost'          => (float)$item['cost'],
                ];
                if ($tmp['uv'] < $tmp['day_uv']) {
                    $tmp['uv'] = $tmp['day_uv'];
                }
                if ($tmp['subscribe'] < $tmp['day_subscribe']) {
                    $tmp['subscribe'] = $tmp['day_subscribe'];
                }
                $return['list'][] = $tmp;
            }
        }

        $this->success("ok", $return);
    }
}