admin_api/app/Http/Controllers/Admin/ExchangeController.php

<?php
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use EXception;
class ExchangeController extends Controller
{
    //幸运兑换查询
    public function treasureList(Request $request)
    {
        $id = (int)$request->post('id');
        $gameid = (int)$request->post('gameid');
        $bonus_name = $request->post('bonus_name');
        $ratio = $request->post('ratio');
        $where = [];
        !empty($id) ? $where[] = ['ID','=',trim($id," ")] : $id = '';
        !empty($gameid) ? $where[] = ['GameID','=',trim($gameid," ")] : $gameid = '';
        !empty($bonus_name) ? $where[] = ['BonusName','like','%'.trim($bonus_name," ").'%'] : $bonus_name = '';
        !empty($ratio) ? $where[] = ['Currency','=',$ratio] : $ratio = '';
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityIndianaConfig as a')
                ->leftjoin('QPAccountsDB.dbo.IndividualDatum as i','a.UserID','=','i.UserID')
                ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','a.UserID','=','ai.UserID')
                ->select('a.ID','a.BonusName','a.NickName','a.LuckyTime','a.Mark','a.Cause','ai.GameID',
                        'i.UserID','i.Compellation','i.SeatPhone','i.MobilePhone','i.DwellingPlace','i.UserNote')
                ->where($where)
                ->whereNotNull('a.UserID')
                ->orderBy('a.ID','desc')
                ->paginate(10);
        foreach ($list as $key => &$value) {
            if($value->Mark == 1){
                $value->Cause = '已发货';
            }
        }
        $ratio_name = ['40000'=>'实物','30000'=>'金豆'];
        $select = [null,'已发货','联系方式有误','收货地址有误','发货信息缺失'];
        return view('admin.exchange.treasure',['list'=>$list,'select'=>$select,'id'=>$id,'gameid'=>$gameid,'bonus_name'=>$bonus_name,'ratio_name'=>$ratio_name,'ratio'=>$ratio]);
    }
    //金豆兑嗨乐分查询
    public function hailefen(Request $request){

    }

    //幸运兑换确认发货
    public function treasureUpdate(Request $request,$id)
    {
        $state = $request->post('state');
        $update['Cause'] = $state;
        if($state === '已发货'){
            $update['Mark'] = 1;
        }else{
            $update['Mark'] = 0;
        }
        $result = DB::table('QPAccountsDB.dbo.ActivityIndianaConfig')->where('ID', $id)->update($update);
        if($result){
            return $this->json(200, "修改成功");
        }else{
            return $this->json(500, '修改失败，请重试');
        }
    }

    //幸运兑换管理
    public function treasureGoodsList(Request $request)
    {
        $type = $request->get('type');
        if(isset($type)){
            $where = ['Currency'=>$type];
            $type = (int)$type;
        }else{
            $where=[];
        }
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityIndianaConfig')
                ->where($where)
                ->orderBy('ID','desc')
                ->paginate(10);
        foreach ($list as $key => $value) {
            if($value->EndTime<date('Y-m-d H:i:s')){
                $value->overdue = '已下架';
            }else{
                $value->overdue = '夺宝中';
            }
            switch ($value->Currency) {
                case 30000:
                    $value->Type = '金豆';
                    break;
                case 40000:
                    $value->Type = '实物';
                    break;
                default:
                    break;
            }
        }
        $goods_type = [30000=>'金豆',40000=>'实物'];
        return view('admin.exchange.treasure_goods',['list'=>$list,'goods_type'=>$goods_type,'type'=>$type]);
    }

    //幸运兑换新增商品页面
    public function treasureGoodsAddView()
    {
        $goods_type = [30000=>'金豆',40000=>'实物'];
        $prop_type = [2=>'金豆',0=>'实物'];
        return view('admin.exchange.treasure_goods_add',['goods_type'=>$goods_type,'prop_type'=>$prop_type]);
    }

    //幸运兑换新增商品
    public function treasureGoodsAdd(Request $request)
    {
        $params = $request->post();
        $treasure_goods = DB::connection('write')->table('QPAccountsDB.dbo.ActivityIndianaConfig');
        $LuckyNumB = rand(10001,99999);
        $ID = $treasure_goods->count() +1;
        $date = date('Y-m-d H:i:s');
        $insert_data=[
            'ID'=>$ID,
            'BonusName'=>$params['title'],
            'BonusURL'=>$params['pic_url'],
            'TotalAmount'=>$params['amount'],
            'UsedAmount'=>0,
            'StartTime'=>$date,
            'EndTime'=>date('Y-m-d H:i:s',strtotime($params['time'])),
            'LuckyNumB'=>$LuckyNumB,
            'Currency'=>$params['goods_type'],
            'Ratio'=>$params['ratio'],
            'LimitCount'=>$params['limit'],
            'RewardNum'=>$params['reward_num'],
            'GoodsDesc'=>$params['describe'],
            'PropType'=>$params['prop_type'],
            ];
        $treasure_goods->insert($insert_data);
        for ($i=1; $i <= $params['amount'] ; $i++) {
            $data[$i-1]=[
                'Number'=>10000000 + $i,
                'CurrentID'=>$ID,
                'UserID'=>0,
                'MyTime'=>$date
            ];
        }
        $result = DB::table('QPAccountsDB.dbo.ActivityIndianaTicket')->insert($data);
        if($result){
            return $this->json(200, "提交成功");
        }else{
            return $this->json(500, '提交失败，请重试');
        }
    }

    //幸运兑换商品修改页面
    public function treasureGoodsUpdateView(Request $request,$id)
    {
        $goods_type = [30000=>'金豆',40000=>'实物'];
        $prop_type = [2=>'金豆',0=>'实物'];
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityIndianaConfig')
                ->where('ID',$id)
                ->first();
        $list->EndTime = str_replace(' ','T',$list->EndTime);
        return view('admin.exchange.treasure_goods_update',['list'=>$list,'goods_type'=>$goods_type,'prop_type'=>$prop_type]);
    }

    //幸运兑换商品修改
    public function treasureGoodsUpdate(Request $request,$id)
    {
        $post_data = $request->post();
        $insert_data=[
            'BonusName'=>$post_data['title'],
            'BonusURL'=>$post_data['pic_url'],
            'EndTime'=>date('Y-m-d H:i:s',strtotime($post_data['time'])),
            'LimitCount'=>$post_data['limit'],
            'RewardNum'=>$post_data['reward_num'],
            'GoodsDesc'=>$post_data['describe'],
            'PropType'=>$post_data['prop_type'],
            'Currency'=>$post_data['goods_type'],
            ];
        $result = DB::table('QPAccountsDB.dbo.ActivityIndianaConfig')->where('ID',$id)->update($insert_data);
        if($result){
            return $this->json(200, "提交成功");
        }else{
            return $this->json(500, '提交失败，请重试');
        }
    }

    //福利中心兑换记录
    public function shopList(Request $request)
    {
        $id = (int)$request->post('id');
        $reward = $request->post('reward');
        $type = $request->post('type');
        $status = $request->post('status');
        $start_time  = $request->post('start_time');
        $end_time    = $request->post('end_time');
        $excel = $request->get('excel');
        if(isset($type)){
            $where[] = ['asg.PropType','=',$type];
            $type = (int)$type;
        }else{
            $where=[];
        }
        if(isset($status)){
            $where[] = ['rri.Status','=',$status];
            $status = (int)$status;
        }
        !empty($start_time) ? $where[] = ['asr.CreateDate','>=',$start_time] : $start_time = '';
        !empty($end_time) ? $where[] = ['asr.CreateDate','<=',date('Y-m-d',strtotime("$end_time +1day"))] : $end_time = '';
        !empty($id) ? $where[] = ['ai.GameID','=',trim($id," ")] : $id = '';
        !empty($reward) ? $where[] = ['asg.GoodsTitle','like','%'.trim($reward," ").'%'] : '';
        if($excel){
            $cellData = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopRecord as asr')
                    ->leftjoin('QPAccountsDB.dbo.ActivityShopGoods as asg','asr.GoodsID','=','asg.ID')
                    ->leftjoin('QPAccountsDB.dbo.RecordRewardInKind as rri','asr.CreateDate','=','rri.CreateDate')
                    ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','asr.UserID','=','ai.UserID')
                    ->select(DB::raw('CONVERT(varchar(100),asr.CreateDate,20) as CreateDate'),'asg.GoodsTitle','asg.Money','asg.PropType','ai.GameID','ai.NickName','rri.Compellation','rri.MobilePhone','rri.UserNote','rri.Status')
                    ->where($where)
                    ->orderBy(DB::raw('CONVERT(varchar(100),asr.CreateDate,20)'),'desc')
                ->get();

            foreach ($cellData as $key => &$value) {
                if($value->Status == 1){
                    $value->Status = '已发货';
                }else{
                    $value->Status = '待发货';
                }
                switch ($value->PropType) {
                    case 0:
                        $value->PropType = '实物';
                        break;
                    case 1:
                        $value->PropType = '红包';
                        break;
                    case 2:
                        $value->PropType = '金豆';
                        $value->Status = '已发货';
                        break;
                    case 3:
                        $value->PropType = '代金券';
                        $value->Status = '已发货';
                        break;
                    default:
                        # code...
                        break;
                }
            }
            $title = ['兑换时间','奖品名称','奖品价格','奖品种类','玩家ID','玩家昵称','姓名','联系电话','联系地址','状态'];
            $cellData = json_decode(json_encode($cellData),true);
            downloadExcel($cellData,$title,'充值查询');
        }else{
            $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopRecord as asr')
                    ->leftjoin('QPAccountsDB.dbo.ActivityShopGoods as asg','asr.GoodsID','=','asg.ID')
                    ->leftjoin('QPAccountsDB.dbo.RecordRewardInKind as rri','asr.CreateDate','=','rri.CreateDate')
                    ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','asr.UserID','=','ai.UserID')
                    ->select(DB::raw('CONVERT(varchar(100),asr.CreateDate,20) as CreateDate'),'asg.GoodsTitle','asg.PropType','asg.Money','ai.GameID','ai.NickName','rri.Compellation','rri.MobilePhone','rri.UserNote','rri.Status','rri.RecordID')
                    ->where($where)
                    ->orderBy(DB::raw('CONVERT(varchar(100),asr.CreateDate,20)'),'desc')
                    ->paginate(10);
            foreach ($list as $key => &$value) {
                if($value->Status == 1){
                    $value->Cause = '已发货';
                }else{
                    $value->Cause = '待发货';
                }
                switch ($value->PropType) {
                    case 0:
                        $value->Type = '实物';
                        break;
                    case 1:
                        $value->Type = '红包';
                        break;
                    case 2:
                        $value->Type = '金豆';
                        $value->Cause = '已发货';
                        break;
                    case 3:
                        $value->Type = '代金券';
                        $value->Cause = '已发货';
                        break;
                    default:
                        # code...
                        break;
                }
            }
        }

        $select = [null,'已发货','发货信息缺失'];
        $order_status = [0=>'待发货',1=>'已发货'];
        $prop_type = [0=>'实物',1=>'红包',2=>'金豆',3=>'代金券'];
        return view('admin.exchange.shop',['list'=>$list,'start_time'=>$start_time,'end_time'=>$end_time,'select'=>$select,'type'=>$type,'prop_type'=>$prop_type,'order_status'=>$order_status,'status'=>$status,'id'=>$id,'reward'=>$reward]);
    }

    //兑换商城标记发货
    public function shopUpdate(Request $request,$id)
    {

        $state = $request->post('state');
        $update['Cause'] = $state;
        if($state === '已发货'){
            $update['Status'] = 1;
        }else{
            $update['Status'] = 0;
        }
        $result = DB::table('QPAccountsDB.dbo.RecordRewardInKind')->where('RecordID', $id)->update($update);
        if($result){
            return $this->json(200, "修改成功");
        }else{
            return $this->json(500, '修改失败，请重试');
        }
    }

    //兑换商城管理
    public function goodsList()
    {
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopGoods')
                ->select('*',DB::raw('TotalAmount-UsedAmount as Surplus'))
                ->paginate(10);
        foreach ($list as $key => &$value) {
            switch ($value->GoodsType) {
                case '1':
                    $value->GoodsType = '卡券';
                    break;
                case '2':
                    $value->GoodsType = '实物';
                    break;
                default:
                    $value->GoodsType = '热门';
                    break;
            }
        }
        return view('admin.exchange.goods',['list' => $list]);
    }

    //兑换商品修改
    public function goodsUpdate(Request $request,$id)
    {
        $update['TotalAmount'] = $request->post('total_amount');
        $update['DayLimit'] = $request->post('day_limit');
        $result = DB::table('QPAccountsDB.dbo.ActivityShopGoods')->where('ID', $id)->update($update);
        if($result){
            return $this->json(200, "修改成功");
        }else{
            return $this->json(500, '修改失败，请重试');
        }
    }

    //福利中心管理
    public function shopGoodsList(Request $request)
    {
        $type = $request->get('type');
        if(isset($type)){
            $where = ['GoodsType'=>$type];
            $type = (int)$type;
        }else{
            $where=[];
        }
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopGoods')
                ->where($where)
                ->orderBy('GoodsType')
                ->paginate(10);
        foreach ($list as $key => $value) {
            switch ($value->GoodsType) {
                case 0:
                    $value->Type = '推荐';
                    break;
                case 1:
                    $value->Type = '实物';
                    break;
                case 2:
                    $value->Type = '卡劵';
                    break;
                case 3:
                    $value->Type = '道具';
                    break;
                case 4:
                    $value->Type = '菜篮子';
                    break;
                default:
                    $value->Type = '';
                    break;
            }
        }
        $goods_type = [0=>'推荐',1=>'实物',2=>'卡劵',3=>'道具',4=>'菜篮子'];
        return view('admin.exchange.shop_goods',['list'=>$list,'goods_type'=>$goods_type,'type'=>$type]);
    }

    //福利中心新增商品页面
    public function shopGoodsAddView()
    {
        $goods_type = [0=>'推荐',1=>'实物',2=>'卡劵',3=>'道具',4=>'菜篮子'];
        $bonus = [10000=>'房卡',20000=>'实物',30000=>'金豆',40000=>'礼券',50000=>'宝珠',60000=>'红包'];
        $prop_type = [0=>'实物',1=>'红包',2=>'金豆',3=>'代金券'];
        $status = [0=>'正常',1=>'热门',2=>'限时',3=>'新品'];
        return view('admin.exchange.shop_goods_add',['goods_type'=>$goods_type,'prop_type'=>$prop_type,'status'=>$status,'bonus'=>$bonus]);
    }

    //福利中心新增商品
    public function shopGoodsAdd(Request $request)
    {
        $post_data = $request->post();
        try{
            DB::transaction(function() use($post_data){
                if($post_data['goods_type'] == 2){
                    $PropID = DB::connection('write')->table('QPAccountsDB.dbo.YN_MatchProp')
                            ->where('PropID','<','102000')
                            ->orderBy('PropID','desc')
                            ->first()->PropID+1;
                    $post_data['bonus'] = $PropID;
                    $porp_data = [
                        'PropID'=>$PropID,
                        'PropName'=>$post_data['prop_name'],
                        'Description'=>$post_data['prop_desc'],
                        'Address'=>$post_data['prop_address'],
                        'Phone'=>$post_data['prop_phone'],
                        'Business'=>$post_data['prop_business'],
                        'ExpiringDate'=>$post_data['expiring_date'],
                    ];
                    DB::table('QPAccountsDB.dbo.YN_MatchProp')->insert($porp_data);
                }
                $post_data['prop_type']==0 ? $system_message=1 : $system_message=0;
                $insert = [
                    'GoodsTitle'=>$post_data['title'],
                    'GoodsDesc'=>$post_data['describe'],
                    'GoodsURL'=>$post_data['pic_url'],
                    'Ratio'=>$post_data['ratio'],
                    'EndDate'=>date('Y-m-d H:i:s',strtotime($post_data['time'])),
                    'TotalAmount'=>$post_data['total_amount'],
                    'DayLimit'=>$post_data['day_limit'],
                    'ShowStatus'=>$post_data['status'],
                    'BonusString'=>$post_data['bonus'].','.$post_data['amount'],
                    'SystemMessage'=>$system_message,
                    'GoodsType'=>$post_data['goods_type'],
                    'PropType'=>$post_data['prop_type'],
                    'Money'=>$post_data['money'],
                    'GoodsShowIndex'=>$post_data['order'],
                ];
                DB::table('QPAccountsDB.dbo.ActivityShopGoods')->insert($insert);
            });
            return $this->json(200, "提交成功");
        }catch(\EXception $e){
            DB::rollBack();
            return $this->json(500, '提交失败，请重试');
        }
    }

    //福利中心商品修改页面
    public function shopGoodsUpdateView(Request $request,$id)
    {
        $goods_type = [0=>'推荐',1=>'实物',2=>'卡劵',3=>'道具',4=>'菜篮子'];
        $bonus = [10000=>'房卡',20000=>'实物',30000=>'金豆',40000=>'礼券',50000=>'宝珠',60000=>'红包'];
        $prop_type = [0=>'实物',1=>'红包',2=>'金豆',3=>'代金券'];
        $status = [0=>'正常',1=>'热门',2=>'限时',3=>'新品'];
        $shop_data = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopGoods')
                ->where('ID',$id)
                ->first();
        $shop_data->amount = substr($shop_data->BonusString,strpos($shop_data->BonusString, ',')+1);
        $shop_data->BonusString = substr($shop_data->BonusString,0,strpos($shop_data->BonusString, ','));
        $shop_data->EndDate = str_replace(' ','T',$shop_data->EndDate);
        return view('admin.exchange.shop_goods_update',['shop_data'=>$shop_data,'goods_type'=>$goods_type,'prop_type'=>$prop_type,'status'=>$status,'bonus'=>$bonus]);
    }

    //福利中心商品修改
    public function shopGoodsUpdate(Request $request,$id)
    {
        $post_data = $request->post();
        $update = [
            'Ratio'=>$post_data['ratio'],
            'EndDate'=>date('Y-m-d H:i:s',strtotime($post_data['time'])),
            'TotalAmount'=>$post_data['total_amount'],
            'DayLimit'=>$post_data['day_limit'],
            'Money'=>$post_data['money'],
            'GoodsShowIndex'=>$post_data['order'],
        ];
        $result = DB::table('QPAccountsDB.dbo.ActivityShopGoods')->where('ID',$id)->update($update);
        if($result){
            return $this->json(200, "提交成功");
        }else{
            return $this->json(500, '提交失败，请重试');
        }
    }

    //福卡每日发放查询
    public function everydayCard(Request $request)
    {
        $excel = $request->get('excel');
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');
        $where = [];
        !empty($start_time) ? $where[] = ['mydate','>=',$start_time] : $start_time = '';
        !empty($end_time) ? $where[] = ['mydate','<=',$end_time] : $end_time = '';
        $revenue_data = DB::table('QPRecordDB.dbo.LogDayOutScores as dos')
                ->join('QPRecordDB.dbo.YN_RecordRevenueInfo as rri','dos.mydate','=',DB::raw('CONVERT(varchar(100), rri.UpdateTime, 23)'))
                ->selectRaw('mydate as date,SUM(ChangeRevenue) as ChangeRevenue,Reason')
                ->where($where)
                ->whereIn('Reason',[31,26,32,29,30])
                ->orderBy(DB::raw('mydate'),'desc')
                ->groupBy(DB::raw('mydate'),'Reason')
                ->get();
        foreach ($revenue_data as $key => &$value) {
            $list[$value->date]['date'] = $value->date;
            $list[$value->date][$value->Reason] = abs($value->ChangeRevenue);
        }

        foreach ($list as $key => &$value) {
            if(!array_key_exists('26',$value)){
                $value['26'] = 0;
            }
            if(!array_key_exists('29',$value)){
                $value['29'] = 0;
            }
            if(!array_key_exists('30',$value)){
                $value['30'] = 0;
            }
            if(!array_key_exists('31',$value)){
                $value['31'] = 0;
            }
            if(!array_key_exists('32',$value)){
                $value['32'] = 0;
            }
            $value['recovery'] = (int)$value['29']+(int)$value['30'];//回收总额
            $value['expenditure'] = (int)$value['31']+(int)$value['32']+(int)$value['26'];//支出总额
        }
        $arr = [];
        foreach ($list as $key => $value) {
            $arr['date'] = $value['date'];
            $arr['expenditure'] = $value['expenditure'];
            $arr['31'] = $value['31'];
            $arr['32'] = $value['32'];
            $arr['26'] = $value['26'];
            $arr['recovery'] = $value['recovery'];
            $arr['29'] = $value['29'];
            $arr['30'] = $value['30'];
            $data[]= $arr;
        }
        if($excel){
            $title = ['时间','福卡支出总额','福卡任务','捕鱼比赛','大抽奖','福卡回收总额','幸运兑换','福利中心'];
            $list = json_decode(json_encode($list),true);
            downloadExcel($list,$title,'福卡每日发放');
        }else{
            return view('admin.exchange.everyday_card',['list'=>$data,'start_time'=>$start_time,'end_time'=>$end_time]);
        }
    }

    //福卡发放详情
    public function revenueInfo(Request $request)
    {
        $excel = $request->get('excel');
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');
        $sort = $request->input('sort');
        $where = [];
        if(empty($start_time)){
            $start_time = $end_time = date('Y-m-d',strtotime('-1 day'));
        }
        $where[] = ['ri.UpdateTime','>=',$start_time];
        $where[] = ['ri.UpdateTime','<=',date('Y-m-d',strtotime("$end_time +1day"))];
        $revenue_list = DB::table('QPRecordDB.dbo.YN_RecordRevenueInfo as ri')
            ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','ai.UserID','=','ri.UserID')
            ->selectRaw('SUM(ri.ChangeRevenue) as revenue,ai.GameID,ri.Reason,ai.NickName')
            ->where($where)
            ->whereIn('ri.Reason',[31,26,32,29,30])
            ->groupBy('ai.GameID','ri.Reason','ai.NickName')
            ->get();
        $data = [];
        foreach ($revenue_list as $key => $value) {
            $data[$value->GameID]['gameid'] = $value->GameID;
            $data[$value->GameID]['nickname'] = $value->NickName;
            $data[$value->GameID]['expenditure'] = 0;
            $data[$value->GameID][$value->Reason] = abs($value->revenue);
        }
        foreach ($data as $key => &$value) {
            if(!array_key_exists(31,$value)){
                $data[$value['gameid']][31] = 0;
            }
            if(!array_key_exists(32,$value)){
                $data[$value['gameid']][32] = 0;
            }
            if(!array_key_exists(26,$value)){
                $data[$value['gameid']][26] = 0;
            }
            if(!array_key_exists(29,$value)){
                $data[$value['gameid']][29] = 0;
            }
            if(!array_key_exists(30,$value)){
                $data[$value['gameid']][30] = 0;
            }
            $value['expenditure'] = $value['31']+$value['32']+$value['26'];//福卡收入
            $value['recovery'] = $value['29']+$value['30'];//福卡消耗
        }
        foreach ($data as $k => $v) {
            $arr = [];
            $arr['gameid'] = $v['gameid'];
            $arr['nickname'] = $v['nickname'];
            $arr['expenditure'] = $v['expenditure'];
            $arr['31'] = $v['31'];
            $arr['32'] = $v['32'];
            $arr['26'] = $v['26'];
            $arr['recovery'] = $v['recovery'];
            $arr['29'] = $v['29'];
            $arr['30'] = $v['30'];
            $list[]= $arr;
        }
        if(!empty($sort)){
            $list = $this->arraySort($list,'recovery');
        }else{
            $list = $this->arraySort($list,'expenditure');
        }
        if($excel){
            $title = ['玩家游戏ID','玩家昵称','福卡收入','福卡任务','捕鱼比赛','大抽奖','福卡消耗','幸运兑换','福利中心'];
            $list = json_decode(json_encode($list),true);
            downloadExcel($list,$title,'福卡每日发放详情');
        }else{
            return view('admin.exchange.revenueinfo',['list'=>$list,'start_time'=>$start_time,'end_time'=>$end_time]);
        }
    }


    //添加礼包页面
    public function giftAddView()
    {
        return view('admin.exchange.gift_add');
    }

    //奖品内容
    public function giftList()
    {
        //$prize = DB::connection('write')->table('QPTreasureDB.dbo.PropCfg')->where('PropID',30000)->select('PropID','PropName')->get()->toArray();
//        $prop = DB::connection('write')->table('QPAccountsDB.dbo.YN_MatchProp')->select('PropID','PropName')->get()->toArray();
//        $data = array_merge($prize,$prop);
        //$data = $prize;
        $data = [['PropID'=>30000,'PropName'=>'金币']];

        $data = [['PropID'=>31000,'PropName'=>'免费币']];
        return response()->json($data);
    }

    //添加礼包
    public function giftAdd(Request $request)
    {
        $bonus = $request->get('bonus');
        $data['desc']   = $request->get('desc');
        if(!empty($bonus) && !empty($data['desc'])){
            $data['bonus'] = '';
            foreach ($bonus as $key => $value) {
                if(empty($value['id']) || empty($value['number'])){
                    return $this->json(500,'请确认数据完整');
                }
                $data['bonus'] .= implode(',', $value).';';
            }
            $data['bonus']  = trim($data['bonus'],';');
            $result = DB::table('QPAccountsDB.dbo.ActivityShopCodesBatch')->insert($data);
        }else{
            return $this->json(500,'请确认数据完整');
        }
        if($result){
            return $this->json(200, "添加成功");
        }else{
            return $this->json(500, '添加失败，请重试');
        }
    }

    //添加兑换码页面
    public function codesAddView()
    {
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopCodesBatch')->get();
        return view('admin.exchange.codes_add',['list'=>$list]);
    }

    //添加兑换码
    public function codesAdd(Request $request)
    {
        $admin  = $request->session()->get('admin');//管理员信息
        $number = $request->post('number');
        $batch  = $request->post('batch');
        $time   = $request->post('time');
        $time   = date('Y-m-d',strtotime("$time +1day"));
        $limit  = $request->post('limit');
        empty($limit) ? $limit=0 : '';
        $agent  = $admin->id;
        for ($i=1; $i <=$number ; $i++) {
            $code = strtolower(str_random(6));
            $data[] = [
                'agent_id'=>$agent,
                'batch'=>$batch,
                'code'=>$code,
                'expired_at'=>$time,
                'status'=>0,
                'limit'=>$limit
            ];
        }
        $result = DB::table('QPAccountsDB.dbo.ActivityShopCodes')->insert($data);
        if($result){
            return $this->json(200, "添加成功");
        }else{
            return $this->json(500, '添加失败，请重试');
        }
    }

    //兑换码列表
    public function exchangeCodeList(Request $request)
    {
        $start_time = $request->input('start_time');
        $end_time = $request->input('end_time');
        $status = $request->input('status');
        $name = $request->input('name');
        $excel = $request->input('excel');
        $where = [];
        !empty($start_time) ? $where[]  =   ['created_at','>=',$start_time] : '';
        !empty($end_time) ? $where[]    =   ['created_at','<=',date('Y-m-d',strtotime("$end_time+1day"))] : '';
        if(isset($status)){
            $where[] = ['status','=',$status];
            $status = (int)$status;
        }
        !empty($name) ? $where[] = ['desc','like','%'.$name.'%'] : $name = '';
        $prize = DB::connection('write')->table('QPTreasureDB.dbo.PropCfg')->select('PropID','PropName')->get()->toArray();
        $prop = DB::connection('write')->table('QPAccountsDB.dbo.YN_MatchProp')->select('PropID','PropName')->get()->toArray();
        $data = array_merge($prize,$prop);
        foreach ($data as $key => $value) {
            $prop_arr[$value->PropID] = $value->PropName;
        }
        if($excel){
            $cellData = DB::table('QPAccountsDB.dbo.ActivityShopCodes as asc')
                ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','asc.toUserID','=','ai.UserID')
                ->leftjoin('QPAccountsDB.dbo.ActivityShopCodesBatch as scb','asc.batch','=','scb.id')
                ->select('asc.id','asc.code','scb.desc','scb.bonus','asc.created_at','asc.expired_at','asc.status','ai.GameID','asc.updated_at','asc.limit')
                ->where($where)
                ->orderBy('asc.id','desc')
                ->get();
            foreach ($cellData as $key => &$value) {
                switch ($value->status) {
                    case '0':
                        $value->status = '未兑换';
                        break;
                    case '1':
                        $value->status = '已兑换';
                        break;
                    default:

                        break;
                }
                switch ($value->limit) {
                    case '0':
                        $value->limit = '无限制';
                        break;
                    case '1':
                        $value->limit = '仅新用户';
                        break;
                    default:

                        break;
                }
            }
            $title = ['批次','兑换码','奖品名称','奖品明细','生成时间','过期时间','兑换限制','状态','兑换人','兑换时间'];
            $cellData = json_decode(json_encode($cellData),true);
            downloadExcel($cellData,$title,'兑换码列表');
        }
        $list = DB::connection('write')->table('QPAccountsDB.dbo.ActivityShopCodes as asc')
                ->leftjoin('QPAccountsDB.dbo.AccountsInfo as ai','asc.toUserID','=','ai.UserID')
                ->leftjoin('QPAccountsDB.dbo.ActivityShopCodesBatch as scb','asc.batch','=','scb.id')
                ->select('asc.id','asc.code','scb.desc','scb.bonus','asc.created_at','asc.expired_at','asc.status','ai.GameID','asc.updated_at','asc.limit')
                ->where($where)
                ->orderBy('asc.id','desc')
                ->paginate(10);
        foreach ($list as $key => &$value) {
            $value->bonus = explode(';',$value->bonus);
            $value->name = '';
            foreach ($value->bonus as $k => &$v) {
                $v = explode(',', $v);
                $value->name .= $prop_arr[$v[0]].'x'.$v[1].',';
            }
            switch ($value->status) {
                case '0':
                    $value->status = '未兑换';
                    break;
                case '1':
                    $value->status = '已兑换';
                    break;
                default:

                    break;
            }
            switch ($value->limit) {
                case '0':
                    $value->limit = '无限制';
                    break;
                case '1':
                    $value->limit = '仅新用户';
                    break;
                default:

                    break;
            }
        }
        $status_type = ['0'=>'未兑换','1'=>'已兑换'];
        return view('admin.exchange.code_list',['list'=>$list,'status'=>$status,'start_time'=>$start_time,'end_time'=>$end_time,'name'=>$name,'status_type'=>$status_type]);
    }

    //话费兑换查询
    public function costList(Request $request)
    {
        $user_id = (int)$request->post('user_id');
        $prop_name = $request->post('prop_name');
        $ratio = $request->post('ratio');
        $where = [];
        !empty($user_id) ? $where[] = ['UseId','=',trim($user_id," ")] : $user_id = '';
        !empty($prop_name) ? $where[] = ['PropName','like','%'.trim($prop_name," ").'%'] : $prop_name = '';
        $list = DB::connection('write')->table('QPRecordDB.dbo.RecordHF')
                ->select('UseId','CreateTime','State','Phone','PropName','UniqueCode')
                ->where($where)
                ->orderBy('CreateTime','desc')
                ->paginate(10);
        foreach ($list as $key => &$value) {
            if($value->State == 1){
                $value->State = '已发货';
            }else{
                $value->State = '未发货';
            }
        }
        $select = ['已发货','未发货'];
        return view('admin.exchange.cost',['list'=>$list,'select'=>$select,'user_id'=>$user_id,'prop_name'=>$prop_name]);
    }

    //话费兑换确认发货
    public function costUpdate(Request $request,$id)
    {
        $admin = $request->session()->get('admin');//管理员信息
        $state = $request->post('state');
        $update['State'] = $state;
        if($state === '已发货'){
            $update['State'] = 1;
        }else{
            $update['State'] = 0;
        }
        $hf_detail = DB::table('QPRecordDB.dbo.RecordHF')
            ->where('UniqueCode', $id)
            ->first();
        $insert = [
            'MailType' => 2,
            'MailStatus' => 1,
            'UserID' => $hf_detail->UseId,
            'CreateTime' => date('Y-m-d H:i:s',time()),
            'TitleString' => '充值通知',
            'TextString' => '您的'.$hf_detail->PropName.'话费已经充值到'.$hf_detail->Phone.'的手机上 请注意查收',
            'FromAgentID' => $admin->id,
        ];
        try{
            DB::transaction(function() use($id,$update,$insert){
                DB::table('QPRecordDB.dbo.RecordHF')->where('UniqueCode', $id)->update($update);
                if($update['State'] == 1){
                    DB::table('QPAccountsDB.dbo.PrivateMail')->insert($insert);
                }
            });
            return $this->json(200, "提交成功");
        }catch(\EXception $e){
            DB::rollBack();
            return $this->json(500, '提交失败，请重试');
        }
    }

}