new_sdk_wu/www/new_sdk/application/service/WelfareService.php

<?php

namespace app\service;


use app\common\library\MakeReportGo;
use think\Db;
use think\Env;
use think\Exception;

class WelfareService
{
    private $grant_type_id = [
        1 => '进游发放',
        2 => '每日打卡',
        3 => '单笔福利',
        4 => '累充福利',
        5 => '单日累充福利',
        6 => '开服七日累充福利',
        7 => '首充福利',
        8 => '月卡福利',
        9 => '周卡福利'
    ];

    public function wefare($grant_type_id, $game_id, $sub_channelIds, $channelList, $page = 1, $pageSize = 10, $data = [], $apply = false, $backstage = false)
    {
        $serverid = 0;
        $welfare = model('Welfare')->whereRaw(sprintf('FIND_IN_SET(%s,game_id)', $game_id))
            ->where(['grant_type_id' => $grant_type_id])
            ->where(function ($query) use ($data) {
                if (isset($data['welfare_id']) && $data['welfare_id']) {
                    $query->where('id', $data['welfare_id']);
                }
            })
            ->order('id desc')->find();

        if (!$welfare) {
            return ['code' => 20013, 'msg' => '福利信息不存在'];
        }

        $welfareGiftType = model('welfareGiftType')->where(['id' => $welfare['welfare_gift_type_id']])->find();
        if (!$welfareGiftType) {
            return ['code' => 20013, 'msg' => '福利类型不存在'];
        }

        if (isset($data['server_id'])) {
            $gameServer = model('GameServer')->where(['game_id' => $game_id, 'id' => $data['server_id']])->find();
            $serverid = $gameServer['serverid'];
        }


        $game_id = ['in', explode(',', $welfare['game_id'])];
        if ($data['channel_id']) {

            foreach ($data['channel_id'] as $k => $v) {
                if (!in_array($v, $sub_channelIds)) {
                    return ['code' => 20013, 'msg' => '推广信息不存在'];
                }
            }
            $channel_id = ['in', $data['channel_id']];
        } else {
            $channel_id = ['in', $sub_channelIds];
        }

        $start_time = strtotime(date('Y-m-d'));
        $end_time = strtotime(date('Y-m-d 23:59:59'));
        if (in_array($grant_type_id, [1, 2])) {
            if ($data['login_end_time'] && $data['login_start_time']) {
                $start_time = strtotime($data['login_start_time']);
                $end_time = strtotime($data['login_end_time'] . ' 23:59:59');
            } else {
                $start_time = '';
                $end_time = '';
            }

        } else if (in_array($grant_type_id, [3, 4, 8, 9])) {

            if ($welfareGiftType['type_id'] == 1) {
                if(isset($data['ext_time']) && $data['ext_time']){
                    $start_time = strtotime($data['ext_time']);
                    $end_time = strtotime($data['ext_time']) + 86399;
                }else{
                    list($start_time,$end_time) =  $this->getTime($welfareGiftType,$data,$welfare);
                }


            } else {
                return ['code' => 20013, 'msg' => '福利类型不存在'];
            }

        } else if (in_array($grant_type_id, [5])) {
            $start_time = strtotime($data['login_time']);
            $end_time = strtotime($data['login_time'] . ' 23:59:59');
        } elseif (in_array($grant_type_id, [6])) {
//            $gameServer = model('GameServer')->where(['game_id' => $game_id, 'id' => $data['server_id']])->find();
            if (!$gameServer) {
                return ['code' => 20013, 'msg' => '请选择区服'];
            }
//            $serverid = $gameServer['serverid'];
            $start_time = strtotime(date('Y-m-d H:00:00', $gameServer['create_time']));
            $end_time = strtotime(date('Y-m-d H:59:59', $gameServer['create_time'] + 86400 * 7));
        } elseif (in_array($grant_type_id, [7])) {
            $start_time = '';
            $end_time = '';
        }
        $amount = $welfareGiftType['recharge_amount'];
        $role_level = $welfareGiftType['role_level'];
        $welfare['welfareGiftType'] = $welfareGiftType;
        $members = $this->getUserlistByGrantTypeGameChannelId($grant_type_id, $welfare, $game_id, $channel_id, $serverid, $start_time, $end_time, $amount, $role_level, $page, $pageSize, $data, $data['ids'], $backstage);

        if ($apply) {
            if ($this->saveWelfareGrant($members['data'], $welfare, $grant_type_id)) {
                return ['code' => 20000, 'data' => ''];
            } else {
                return ['code' => 20013, 'msg' => '申请失败'];
            }

        } else {
            if (!$backstage) {
                $channelListArr = [];
                foreach ($channelList as $k => $v) {
                    $channelListArr[$v['id']] = $v['value'];
                }

                $games = model('common/Game')->whereIn('id', $welfare['game_id'])->column('name', 'id');

                foreach ($members['data'] as $k => $v) {
                    $members['data'][$k]['channel_name'] = isset($channelListArr[$v['channel_id']]) ? $channelListArr[$v['channel_id']] : '';
//            $members['data'][$k]['username'] = stringObfuscation($v['username']);
                    $members['data'][$k]['game_name'] = $games[$v['game_id']];
//                $members['data'][$k]['create_time'] = date('Y-m-d H:i:s', $v['create_time']);
                }
                return ['code' => 20000, 'data' => $members];
            } else {
                return $members;
            }
        }


    }

    public function getTime($welfareGiftType,$data,$welfare){
        switch ($welfareGiftType['time_type']) {
            case 1: // 全部
                $start_time = '';
                $end_time = '';
                break;
            case 2: //当日

                if ($data['cycle_id'] == 1) { //今日
                    if($welfare['month_type'] == 1){ //1当天2隔天
                        $start_time = strtotime(date('Y-m-d'));
                        $end_time = strtotime(date('Y-m-d 23:59:59'));
                    }else{
                        $start_time = strtotime(date("Y-m-d")) - 86400;
                        $end_time = strtotime(date('Y-m-d')) - 1;
                    }

                } else { //昨日
                    if($welfare['month_type'] == 1){ //1当天2隔天
                        $start_time = strtotime(date("Y-m-d")) - 86400;
                        $end_time = strtotime(date('Y-m-d')) - 1;
                    }else{
                        $start_time = strtotime(date("Y-m-d")) - 86400*2;
                        $end_time = strtotime(date('Y-m-d')) - 86401;
                    }
                }
                break;
            case 3: //周
                if ($data['cycle_id'] == 1) { //本周
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = mktime(0, 0, 0, date("m"), date("d") - date("w") + 1, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), date("d") - date("w") + 7, date("Y"));
                    }else{
                        $start_time = mktime(0, 0, 0, date("m"), date("d") - date("w") + 1-7, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), date("d") - date("w") + 7-7, date("Y"));
                    }
                } else { //上周
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7, date("Y"));
                    }else{
                        $start_time = mktime(0, 0, 0, date("m"), date("d") - date("w") + 1 - 7*2, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), date("d") - date("w") + 7 - 7*2, date("Y"));
                    }

                }
                break;
            case 4: //月
                if ($data['cycle_id'] == 1) { //本月
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = mktime(0, 0, 0, date("m"), 1, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), date("t"), date("Y"));
                    }else{
                        $start_time = mktime(0, 0, 0, date("m") - 1, 1, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), 0, date("Y"));
                    }
                } else { //上月
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = mktime(0, 0, 0, date("m") - 1, 1, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m"), 0, date("Y"));
                    }else{
                        $start_time = mktime(0, 0, 0, date("m") - 2, 1, date("Y"));
                        $end_time = mktime(23, 59, 59, date("m")-1, 0, date("Y"));
                    }
                }
                break;
            case 5:  //季度
                if ($data['cycle_id'] == 1) { //本季度
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $season = ceil((date('n')) / 3);
                        $start_time = mktime(0, 0, 0, $season * 3 - 3 + 1, 1, date('Y'));
                        $end_time = mktime(23, 59, 59, $season * 3, date('t', mktime(0, 0, 0, $season * 3, 1, date("Y"))), date('Y'));
                    }else{
                        $season = ceil((date('n')) / 3) - 1;
                        $start_time = mktime(0, 0, 0, $season * 3 - 3 + 1, 1, date('Y'));
                        $end_time = mktime(23, 59, 59, $season * 3, date('t', mktime(0, 0, 0, $season * 3, 1, date("Y"))), date('Y'));
                    }
                } else { //上季度
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $season = ceil((date('n')) / 3) - 1;
                        $start_time = mktime(0, 0, 0, $season * 3 - 3 + 1, 1, date('Y'));
                        $end_time = mktime(23, 59, 59, $season * 3, date('t', mktime(0, 0, 0, $season * 3, 1, date("Y"))), date('Y'));
                    }else{
                        $season = ceil((date('n')) / 3) - 2;
                        $start_time = mktime(0, 0, 0, $season * 3 - 3 + 1, 1, date('Y'));
                        $end_time = mktime(23, 59, 59, $season * 3, date('t', mktime(0, 0, 0, $season * 3, 1, date("Y"))), date('Y'));
                    }
                }
                break;
            case 6: //年
                if ($data['cycle_id'] == 1) { //今年
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = strtotime(date('Y-01-01 00:00:00', time()));
                        $end_time = strtotime(date('Y-12-31 23:59:59', time()));
                    }else{
                        $start_time = strtotime(date('Y-01-01 00:00:00', strtotime('-1 year')));
                        $end_time = strtotime(date('Y-12-31 23:59:59', strtotime('-1 year')));
                    }
                } else { // 去年
                    if($welfare['month_type'] == 1) { //1当天2隔天
                        $start_time = strtotime(date('Y-01-01 00:00:00', strtotime('-1 year')));
                        $end_time = strtotime(date('Y-12-31 23:59:59', strtotime('-1 year')));
                    }else{
                        $start_time = strtotime(date('Y-01-01 00:00:00', strtotime('-2 year')));
                        $end_time = strtotime(date('Y-12-31 23:59:59', strtotime('-2 year')));
                    }
                }
                break;
            case 7: //自定义
                $start_time = strtotime(date('Y-m-d', $welfareGiftType['start_time']));
                $end_time = strtotime(date('Y-m-d 23:59:59', $welfareGiftType['end_time']));
                break;
        }
        return [$start_time,$end_time];
    }

    /**
     * @param $grant_type_id 发放类型 1进游发放2每日打卡3单笔福利4累充福利5单日累充福利6开服七日累充福利7首充福利8月卡福利9周卡福利
     * @param $welfare 玩家福利
     * @param $game_id
     * @param $channel_id
     * @param $start_time 开始时间 （1进游发放2每日打卡 时间应为当日时间）
     * @param $end_time 结算时间 （1进游发放2每日打卡 时间应为当日时间）
     * @param int $start_amount
     * @param int $end_amount
     */
    public function getUserlistByGrantTypeGameChannelId($grant_type_id, $welfare, $game_id, $channel_id, $serverid = '', $start_time = '', $end_time = '', $amount = 0, $role_level = 0, $page, $pageSize, $data, $ids = [], $backstage = false)
    {

        $welfareWhere = [
            'game_id' => $game_id,
            'channel_id' => $channel_id,
        ];

        $where = [
            'a.game_id' => $game_id,
            'a.channel_id' => $channel_id,
        ];

        $payWhere = [
            'a.gameid' => $game_id,
            'a.channel_id' => $channel_id,
            'a.status' => 1,
        ];

        if (isset($data['username']) && trim($data['username'])) {
            $where['b.username'] = trim($data['username']);
            $payWhere['a.username'] = trim($data['username']);
        }
        if (isset($data['roleid']) && trim($data['roleid'])) {
            $where['a.roleid'] = trim($data['roleid']);
            $payWhere['a.roleid'] = trim($data['roleid']);
            $welfareWhere['role_id'] = trim($data['roleid']);
        }

        if (isset($data['rolename']) && trim($data['rolename'])) {
            $where['a.rolename'] = trim($data['rolename']);
            $payWhere['a.rolename'] = trim($data['rolename']);
            $welfareWhere['role_name'] = trim($data['rolename']);
        }

        if (isset($data['orderid']) && trim($data['orderid'])) {
            $payWhere['a.orderid'] = trim($data['orderid']);
        }

        // 条件判断
        if (in_array($grant_type_id, [1])) {//进游发放

            if ($ids) {
                $where['a.mgs_id'] = ['in', $ids];
            }

            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $where['a.serverid'] = $serverid;
            }
            if ($role_level > 0) {
                $where['a.rolelevel'] = ['>=', $role_level];
            }
            $welfareWhere['welfare_id'] = $welfare['id'];
        } else if (in_array($grant_type_id, [2])) {//2每日打卡

            if ($start_time && $end_time) {
                $where['c.create_time'] = ['between', [$start_time, $end_time]];
            }

            if ($ids) {
                $where['c.id'] = ['in', $ids];
            }

            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $where['c.serverid'] = $serverid;
            }

        } else if (in_array($grant_type_id, [3])) { //3单笔福利

            if ($ids) {
                $payWhere['a.id'] = ['in', $ids];
            }

            if ($amount > 0) {
                $payWhere['a.amount'] = $amount;
            } else {
                return false;
            }

            if ($start_time && $end_time) {
                $welfareWhere['apply_time'] = ['between', [$start_time, $end_time]];
                $payWhere['a.pay_time'] = ['between', [$start_time, $end_time]];
            }
            $welfareWhere['welfare_id'] = $welfare['id'];
            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $payWhere['a.serverid'] = $serverid;
            }
        } else if (in_array($grant_type_id, [4, 5, 6])) { //4累充福利5单日累充福利6开服七日累充福利
            if ($ids) {
                $payWhere['a.id'] = ['in', $ids];
            }

            if ($amount <= 0) {
                return false;
            }

            if ($start_time && $end_time) {
                if ($grant_type_id == 5) {
                    $welfareWhere['apply_date'] = ['between', [date('Y-m-d', $start_time), date('Y-m-d', $end_time)]];
                } else if ($grant_type_id != 6) {
                    $welfareWhere['apply_time'] = ['between', [$start_time, $end_time]];
                }
                $payWhere['a.pay_time'] = ['between', [$start_time, $end_time]];
            }
            $welfareWhere['welfare_id'] = $welfare['id'];

            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $payWhere['a.serverid'] = $serverid;
            }
        } else if (in_array($grant_type_id, [7])) {//7首充福利

            if ($ids) {
                $payWhere['a.id'] = ['in', $ids];
            }

            if ($start_time && $end_time) {
                $welfareWhere['apply_time'] = ['between', [$start_time, $end_time]];
                $payWhere['a.pay_time'] = ['between', [$start_time, $end_time]];
            }
            if ($amount > 0) {

                if($welfare['first_type'] == 2){
                    $payWhere['a.amount'] =  ['>=', $amount];
                }else{
                    $payWhere['a.amount'] = $amount;

                }

            } else {
                return false;
            }
            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $payWhere['a.serverid'] = $serverid;
            }
        } else if (in_array($grant_type_id, [8, 9])) {//8月卡福利9周卡福利

            if ($ids) {
                $payWhere['id'] = ['in', $ids];
            }

            if ($amount <= 0) {
                return false;
            }
            if($welfare['welfareGiftType']['recharge_type'] == 2){
                if ($amount >= $welfare['welfareGiftType']['max_recharge_amount'] || $welfare['welfareGiftType']['max_recharge_amount']<=0) {
                    return false;
                }
            }

            if (!($start_time && $end_time)) {
                return false;
            }

            $welfareWhere['apply_date'] = ['between', [date('Y-m-d', $start_time), date('Y-m-d', $end_time)]];
            $payWhere['a.pay_time'] = ['between', [$start_time, $end_time]];
            $welfareWhere['welfare_id'] = $welfare['id'];

            if ($serverid) {
                $welfareWhere['server_id'] = $serverid;
                $payWhere['a.serverid'] = $serverid;
            }
        }

        $welfareWhere['examine'] = ['in', [1, 2]];
        $welfareWhere['grant_type_id'] = $grant_type_id;
        //获取已发放用户
        $welfareGrantList = model('common/WelfareGrant')->where($welfareWhere)->field('member_id,server_id,orderid,apply_date')->select();


//        $members = [];
        //1今日进游发放
        if ($grant_type_id == 1) {

            if ($start_time && $end_time) {
                $where['a.create_time'] = ['between', [$start_time, $end_time]]; //该时间应为当日时间
            }

            //获取有效用户
            $sql = model('common/MemberGameServer')
                ->alias('a')
                ->join('cy_members b', 'a.member_id=b.id')
                ->where($where)
                ->where(function ($query) use ($welfareGrantList) {
                    foreach ($welfareGrantList as $k => $v) {
                        $query->whereRaw(sprintf('not (a.member_id = %d and a.serverid = %s )', $v['member_id'], Db::quote($v['server_id'])));
                    }
                })
                ->group('a.member_id,a.serverid')
                ->field('a.mgs_id,"" as orderid,a.member_id,a.channel_id,a.game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(a.create_time, "%Y-%m-%d %H:%i:%s") as create_time,0 as amount,0 as cumulative_amount,b.username')
                ->buildSql();
            if ($backstage) {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }

        } else if ($grant_type_id == 2) {//2每日打卡

//            $where['a.update_time'] = ['between', [$start_time, $end_time]]; //该时间应为当日时间
            //获取有效用户
            $sql = model('common/GameServerIp')
                ->alias('c')
                ->join('cy_members b', 'c.member_id=b.id')
                ->join('nw_member_game_server a', 'a.member_id=c.member_id and a.game_id=c.game_id and a.serverid=c.serverid')
                ->where($where)
                ->where(function ($query) use ($welfareGrantList) {
                    foreach ($welfareGrantList as $k => $v) {
                        $query->whereRaw(sprintf('not (c.member_id = %d and c.serverid = %s and %s)', $v['member_id'], Db::quote($v['server_id']), 'FROM_UNIXTIME(c.create_time, "%Y-%m-%d" ) = ' . Db::quote($v['apply_date'])));
                    }
                })->group('c.member_id,c.serverid,FROM_UNIXTIME(c.create_time, "%Y-%m-%d" )')
                ->field('c.id as mgs_id,"" as orderid,a.member_id,a.channel_id,a.game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(c.create_time, "%Y-%m-%d %H:%i:%s" ) as create_time,0 as amount,0 as cumulative_amount,b.username')
                ->buildSql();
            if ($backstage) {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }
        } else if ($grant_type_id == 3) {//3单笔福利

            //获取有效用户
            $query = model('common/Pay')->where($payWhere)
                ->alias('a')
                ->where(function ($query) use ($welfareGrantList, $welfare) {
                    foreach ($welfareGrantList as $k => $v) {
                        if ($welfare['frequency'] == 1) { //发放次数 1一次 2多次
                            $query->whereRaw(sprintf('not (a.userid = %d and a.serverid = %s )', $v['member_id'], Db::quote($v['server_id'])));
                        } else {
                            $query->whereRaw(sprintf('not (a.userid = %d and a.orderid = %s )', $v['member_id'], Db::quote($v['orderid'])));
                        }
                    }
                })->field('a.id as mgs_id,a.orderid,a.userid as member_id,a.channel_id,a.gameid as game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(a.pay_time, "%Y-%m-%d %H:%i:%s" ) as create_time,a.amount,0 as cumulative_amount,a.username');

            if ($welfare['frequency'] == 1) {//发放次数 1一次 2多次
                $sql = $query->group('a.userid,a.serverid')->buildSql();
            } else {
                $sql = $query->buildSql();
            }
            if ($backstage) {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->order('a.create_time desc')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }
        } else if (in_array($grant_type_id, [4, 5, 6])) { //4累充福利 5单日累充福利 6开服七日累充福利
            //获取有效用户
            $sql = model('common/Pay')->where($payWhere)
                ->alias('a')
                ->where(function ($query) use ($welfareGrantList, $welfare, $amount) {
                    foreach ($welfareGrantList as $k => $v) {
                        $query->whereRaw(sprintf('not (a.userid = %d and a.serverid = %s )', $v['member_id'], Db::quote($v['server_id'])));
                    }
                })->field('group_concat(a.id) as mgs_id,"" as orderid,a.userid as member_id,a.channel_id,a.gameid as game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(a.pay_time, "%Y-%m-%d %H:%i:%s" ) as create_time,0 as amount,sum(a.amount) as cumulative_amount,a.username')
                ->group('a.userid,a.serverid')->having('sum(a.amount)>=' . $amount)->buildSql();
            if ($backstage) {
                $members = Db::table($sql . ' a')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }
        } else if (in_array($grant_type_id, [7])) { // 7首充福利

            //获取有效用户
            $sql = model('common/Pay')->where($payWhere)
                ->alias('a')
                ->where(function ($query) use ($welfareGrantList, $welfare) {
                    foreach ($welfareGrantList as $k => $v) {
                        $query->whereRaw(sprintf('not (a.userid = %d and a.serverid = %s )', $v['member_id'], Db::quote($v['server_id'])));
                    }
                })->field('a.id as mgs_id,a.orderid,a.userid as member_id,a.channel_id,a.gameid as game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(a.pay_time, "%Y-%m-%d %H:%i:%s" ) as create_time,a.amount,0 as cumulative_amount,a.username')
                ->group('a.userid,a.serverid')->buildSql();
            if ($backstage) {
                $members = Db::table($sql . ' a')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }
        } else if (in_array($grant_type_id, [8, 9])) {// 8月卡福利9周卡福利
            //获取有效用户
            $sql = model('common/Pay')->where($payWhere)
                ->alias('a')
                ->where(function ($query) use ($welfareGrantList, $welfare) {
                    foreach ($welfareGrantList as $k => $v) {
                        $query->whereRaw(sprintf('not (a.userid = %d and a.serverid = %s )', $v['member_id'], Db::quote($v['server_id'])));
                    }
                })->field('group_concat(a.id) as mgs_id,"" as orderid,a.userid as member_id,a.channel_id,a.gameid as game_id,a.serverid,a.servername,a.roleid,a.rolename,a.rolelevel,FROM_UNIXTIME(a.pay_time, "%Y-%m-%d %H:%i:%s" ) as create_time,0 as amount,sum(a.amount) as cumulative_amount,a.username')
                ->group('a.userid,a.serverid');
            //金额类型1单金额2区间金额
            if($welfare['welfareGiftType']['recharge_type'] == 1){
                $sql = $sql->having('sum(a.amount)>=' . $amount)->buildSql();
            }else{
                $sql = $sql->having('sum(a.amount)>=' . $amount.' and sum(a.amount)<='.$welfare['welfareGiftType']['max_recharge_amount'])->buildSql();
            }

            if ($backstage) {
                $members = Db::table($sql . ' a')->paginate(10, false, ['query' => input()]);
            } else {
                $members = Db::table($sql . ' a')->paginate(['list_rows' => $pageSize, 'page' => $page])->toArray();
            }


        }

        return $members;
    }


    public function saveWelfareGrant($members, $welfare, $grant_type_id)
    {
        $time = time();
        $ids = [];
        $apply = false;
        try {
            model('common/WelfareGrant')->startTrans();
            $resources_data = json_decode($welfare['resources_data'], true);

            foreach ($members as $k => $v) {
                $welfareGrant = [
                    'game_id' => $v['game_id'],
                    'server_id' => $v['serverid'],
                    'role_id' => $v['roleid'],
                    'member_id' => $v['member_id'],
                    'username' => $v['username'],
                    'channel_id' => $v['channel_id'],
                    'grant_type_id' => $grant_type_id,
                    'welfare_id' => $welfare['id'],
                    'level' => $v['rolelevel'],
                    'role_name' => $v['rolename'],
                    'apply_time' => $time,
                    'orderid' => $v['orderid'],
                    'amount' => $v['amount'],
                    'cumulative_amount' => $v['cumulative_amount'],
                    'welfare_gift_type_id' => $welfare['welfare_gift_type_id'],

                ];
                //是否需要审核1不需要2需要
                if ($welfare['is_examine'] == 1) {
                    $welfareGrant['examine'] = 2;
                } else {
                    $apply = true;
                    $welfareGrant['examine'] = 1;
                }

                if (in_array($grant_type_id, [2, 4, 5, 8, 9])) {
                    $welfareGrant['apply_date'] = date('Y-m-d', strtotime($v['create_time']));
                } else {
                    $welfareGrant['apply_date'] = date('Y-m-d');
                }

                if (!$id = model('common/WelfareGrant')->insertGetId($welfareGrant)) {
                    model('common/WelfareGrant')->rollback();
                    return false;
                }
                $ids[] = $id;
                $tmp = [];


                if (in_array($grant_type_id, [8, 9])) { // 月卡周卡特殊处理

                    $grant_date = model('common/WelfareGrant')->alias('a')->where([
                        'game_id' => $v['game_id'],
                        'server_id' => $v['serverid'],
                        'member_id' => $v['member_id'],
                        'grant_type_id' => $grant_type_id,
                        'welfare_id' => $welfare['id'],
                    ])->join('cy_welfare_grant_data b', 'a.id=b.welfare_grant_id')->max('grant_date', false);

                    if ($grant_date) {
                        $grant_date_time = strtotime($grant_date) + 86400;
                    } else {
                        $grant_date_time = time();
                    }

                    $i = 0;
                    if ($grant_type_id == 8) { //月卡 30天
                        $len = 30;
                    } else {
                        $len = 7;
                    }
                    for ($i = 0; $i < $len; $i++) {
                        foreach ($resources_data as $kk => $vv) {

                            $tmp[] = [
                                'welfare_grant_id' => $id,
                                'welfare_resources_id' => $vv['welfare_resources_id'],
                                'number' => $vv['number'],
                                'grant_date' => date('Y-m-d', $grant_date_time + $i * 86400),
                                'unique_id' => makeUniqueid('FL')
                            ];
                        }
                    }

                } else {
                    foreach ($resources_data as $kk => $vv) {

                        $tmp[] = [
                            'welfare_grant_id' => $id,
                            'welfare_resources_id' => $vv['welfare_resources_id'],
                            'number' => $vv['number'],
                            'grant_date' => date('Y-m-d'),
                            'unique_id' => makeUniqueid('FL')
                        ];
                    }
                }

                if ($tmp) {
                    model('common/welfareGrantData')->insertAll($tmp);
                } else {
                    model('common/WelfareGrant')->rollback();
                    return false;
                }
            }
        } catch (Exception $e) {
            model('common/WelfareGrant')->rollback();
            return false;
        }
        model('common/WelfareGrant')->commit();
        if(!$apply){
            foreach ($ids as $k => $v) {
                $makeReportGo = new MakeReportGo();
                $makeReportGo->welfareTask('welfareList', $v);
            }
        }

        if($apply){
            curlDD('【福利审核】有新的福利申请待审核',Env::get('notice_weelfar_url'));
        }
        return true;
    }

    public function welfareGrantList($data, $sub_channelIds, $channelList)
    {
        $where = [];
        if ($data['channel_id']) {
            foreach ($data['channel_id'] as $k => $v) {
                if (!in_array($v, $sub_channelIds)) {
                    return ['code' => 20013, 'msg' => '推广信息不存在'];
                }
            }
            $where['a.channel_id'] = ['in', $data['channel_id']];
        } else {
            $where['a.channel_id'] = ['in', $sub_channelIds];
        }
        if ($data['game_id']) {
            $where['a.game_id'] = $data['game_id'];
        }

        if ($data['grant_type_id']) {
            $where['a.grant_type_id'] = $data['grant_type_id'];
        }
        if ($data['welfare_id']) {
            $where['a.welfare_id'] = $data['welfare_id'];
        }
        if ($data['server_id']) {
            $gameServer = model('GameServer')->where(['game_id' => $data['game_id'], 'id' => $data['server_id']])->find();
            $where['a.server_id'] = ['in', $gameServer['serverid']];
        }
        if ($data['examine']) {
            $where['a.examine'] = $data['examine'];
        }
        if ($data['grant_result']) {
            $where['a.grant_result'] = $data['grant_result'];
        }
        if ($data['username']) {
            $where['a.username'] = $data['username'];
        }
        if ($data['roleid']) {
            $where['a.roleid'] = $data['roleid'];
        }
        if ($data['rolename']) {
            $where['a.rolename'] = $data['rolename'];
        }
        if ($data['start_time'] && $data['end_time']) {
            $start_time = strtotime($data['start_time']);
            $end_time = strtotime($data['end_time'] . ' 23:59:59');
            $where['a.apply_time'] = ['between', [$start_time, $end_time]];
        }
        $data = model('common/WelfareGrant')
            ->alias('a')
            ->join('cy_welfare b', 'a.welfare_id=b.id')
            ->where($where)
            ->order('a.id desc')
            ->field('a.*,b.name')
            ->paginate(['list_rows' => $data['pageSize'], 'page' => $data['page']])
            ->toArray();

        if ($data['data']) {
            $channelListArr = [];
            foreach ($channelList as $k => $v) {
                $channelListArr[$v['id']] = $v['value'];
            }

            $games = model('common/Game')->cache('welfare:welfareservice:game', 300)->column('name', 'id');
            $servers = model('common/GameServer')->cache('welfare:welfareservice:server', 300)->field('servername,game_id,serverid')->select();
            $serversArr = [];
            foreach ($servers as $k => $v) {
                $serversArr[$v['game_id'] . '_' . $v['serverid']] = $v['servername'];
            }
            foreach ($data['data'] as $k => $v) {
                $data['data'][$k]['channel_name'] = isset($channelListArr[$v['channel_id']]) ? $channelListArr[$v['channel_id']] : '';
                $data['data'][$k]['server_name'] = isset($serversArr[$v['game_id'] . '_' . $v['server_id']]) ? $serversArr[$v['game_id'] . '_' . $v['server_id']] : '';
                $data['data'][$k]['game_name'] = $games[$v['game_id']];
                $data['data'][$k]['grant_type_game'] = $this->grant_type_id[$v['grant_type_id']];

                $data['data'][$k]['examine'] = $v['examine'] == 1 ? '未审核' : ($v['examine'] == 2 ? '审核通过' : '拒绝');
                $data['data'][$k]['grant_result'] = $v['grant_result'] == 1 ? '未发放' : ($v['grant_result'] == 2 ? '发放中' : ($v['grant_result'] == 3 ? '发放完成' : '发放失败'));
            }
        }

        return ['code' => 20000, 'data' => $data];
    }
}