zhuishuyun_quickapp/app/Http/Controllers/Manage/Trade/OrdersController.php

<?php
/**
 * Created by PhpStorm.
 * User: hp
 * Date: 2017/11/30
 * Time: 17:24
 */

namespace App\Http\Controllers\Manage\Trade;

use App\Http\Controllers\Controller;
use App\Http\Controllers\Manage\Trade\Transformers\OrderTodayStatTransformer;
use App\Http\Controllers\Manage\Trade\Transformers\OrderTransformer;
use App\Modules\Channel\Services\ChannelService;
use App\Modules\SendOrder\Services\SendOrderService;
use App\Modules\Trade\Services\OrderService;
use App\Modules\OfficialAccount\Services\ForceSubscribeService;
use App\Modules\User\Models\User;
use DB;
use Illuminate\Http\Request;

class OrdersController extends Controller
{

    /**
     * @apiDefine Trade 交易订单
     */

    /**
     * @apiVersion 1.0.0
     * @api {GET} trade/orders 获取订单列表
     * @apiGroup Trade
     * @apiName getOrders
     * @apiParam   {Number}  [send_order_id] 派单ID
     * @apiParam   {String}  [begin_time] 开始时间
     * @apiParam   {String}  [end_time] 结束时间
     * @apiParam   {Number}  [distribution_channel_id] 分销渠道ID
     * @apiParam   {Number}  [transaction_id]  三方平台台交易ID.
     * @apiParam   {Number}  [trade_no] 订单号
     * @apiParam   {Number}  [uid] 用户ID
     * @apiParam   {Number}  [status]  订单状态（0：成功， 1：失败， 2：全部）
     * @apiSuccess {Number}  id 订单ID.
     * @apiSuccess {Number}  distribution_channel_id 渠道id.
     * @apiSuccess {Number}  uid 用户id.
     * @apiSuccess {Number}  product_id 产品id.
     * @apiSuccess {String}  price 价格.
     * @apiSuccess {String}  status '状态 PAID：已支付 UNPAID未支付'.
     * @apiSuccess {String}  pay_type '支付类型 1：公众号支付'.
     * @apiSuccess {String}  trade_no 平台交易ID.
     * @apiSuccess {String}  servicer 支付服务商OFFICIAL.
     * @apiSuccess {String}  transaction_id 三方平台交易ID.
     * @apiSuccess {String}  pay_end_at 支付完成时间.
     * @apiSuccess {String}  create_ip 订单创建IP地址
     * @apiSuccess {Number}  send_order_id 派单id
     * @apiSuccess {String}  send_order_name 派单名称
     * @apiSuccess {String}  created_at 创建时间
     * @apiSuccess {String}  offical_account_nick_name 服务号名称
     * @apiSuccess {String}  updated_at 更新时间
     * @apiSuccessExample {json} Success-Response:
     *
     *     {
     *         "code": 0,
     *         "msg": "",
     *         "data": [
     *            {
     *              "id": 2,
     *              "distribution_channel_id": 2,
     *              "uid": 1,
     *              "product_id": 1,
     *              "price": "1.00",
     *              "status": "PAID",
     *              "pay_type": "WECHAT",
     *              "trade_no": "201711301125401585459852216605",
     *              "servicer": "AllinPay",
     *              "transaction_id": "4200000013201711307989945956",
     *              "pay_end_at": "2017-12-01 10:20:04",
     *              "create_ip": "::1",
     *              "send_order_id": 0,
     *              "send_order_name": "",
     *              "offical_account_nick_name": "追书云",
     *              "created_at": 1512094804,
     *              "updated_at": 1512094804
     *            }
     *        ]
     *     }
     */
    function getOrders(Request $request)
    {
        $params = [];
        $request->has('uid') && $request->input('uid') && $params['uid'] = (int)$request->input('uid');
        $request->has('trade_no') && $request->input('trade_no') && $params['trade_no'] = trim($request->input('trade_no'));
        $request->has('end_time') && $request->input('end_time') && $params['end_time'] = trim($request->input('end_time'));
        $request->has('start_time') && $request->input('start_time') && $params['begin_time'] = trim($request->input('start_time'));
        $request->has('send_order_id') && $request->input('send_order_id') && $params['send_order_id'] = (int)$request->input('send_order_id');
        $request->has('transaction_id') && $request->input('transaction_id') && $params['transaction_id'] = trim($request->input('transaction_id'));
        $request->has('distribution_channel_id') && $request->input('distribution_channel_id') && $params['distribution_channel_id'] = (int)$request->input('distribution_channel_id');

        $status =  $request->has('status') ? $request->input('status'):0;

        if (0==$status){
            $params['status'] = 'PAID';
        }elseif (1==$status){
            $params['status'] = 'UNPAID';
        }
        if(array_key_exists('transaction_id',$params) && $params['transaction_id'])
        {
            $trade_no = $params['transaction_id'];
            $params=array(
                'transaction_id'=>$trade_no,
            );
            $created_at = substr($trade_no,10,8);
            $year = substr($created_at,0,4);
            $month = substr($created_at,4,2);
            $day = substr($created_at,6,2);
            $created_at = $year.'-'.$month.'-'.$day;
            /*$params['begin_time'] = $created_at.' 00:00:00';
            $params['end_time'] = $created_at;*/
            $params['created_at'] = $created_at;
            \Log::info('filter_param:'.json_encode($params));
        }
        if(array_key_exists('trade_no',$params) && $params['trade_no']){
            unset($params['begin_time']);
            unset($params['end_time']);
        }
        \Log::info('orders_search_start:'.time());
        $orders = OrderService::search($params);
        \Log::info('orders_search_start1:'.time());
        foreach ($orders as $item) {
            $item->channel_name = ChannelService::getChannelNicknameById($item->distribution_channel_id);
            $nickName = ForceSubscribeService::getOfficalAccountNickName($item->uid);
            $item->offical_account_nick_name = $nickName ? $nickName->nickname : '';
        }
        return response()->pagination(new OrderTransformer(), $orders);

    }

    /**
     * @apiVersion 1.0.0
     * @api {GET} trade/exportOrders 导出订单列表
     * @apiGroup Trade
     * @apiName exportOrders
     * @apiParam   {Number}  [send_order_id] 派单ID
     * @apiParam   {String}  [begin_time] 开始时间
     * @apiParam   {String}  [end_time] 结束时间
     * @apiParam   {Number}  [status]  订单状态（0：成功， 1：失败， 2：全部）
     * @apiParam   {Number}  [distribution_channel_id] 分销渠道ID
     * @apiParam   {Number}  [transaction_id]  三方平台台交易ID.
     * @apiParam   {Number}  [trade_no] 订单号
     * @apiParam   {Number}  [uid] 用户ID
     * @apiSuccess {Number}  id 订单ID.
     * @apiSuccess {Number}  distribution_channel_id 渠道id.
     * @apiSuccess {Number}  uid 用户id.
     * @apiSuccess {Number}  product_id 产品id.
     * @apiSuccess {String}  price 价格.
     * @apiSuccess {String}  status '状态 PAID：已支付 UNPAID未支付'.
     * @apiSuccess {String}  pay_type '支付类型 1：公众号支付'.
     * @apiSuccess {String}  trade_no 平台交易ID.
     * @apiSuccess {String}  servicer 支付服务商OFFICIAL.
     * @apiSuccess {String}  transaction_id 三方平台交易ID.
     * @apiSuccess {String}  pay_end_at 支付完成时间.
     * @apiSuccess {String}  create_ip 订单创建IP地址
     * @apiSuccess {Number}  send_order_id 派单id
     * @apiSuccess {String}  send_order_name 派单名称
     * @apiSuccess {String}  created_at 创建时间
     * @apiSuccess {String}  updated_at 更新时间
     * @apiSuccessExample {json} Success-Response:
     *
     *     {
     *         "code": 0,
     *         "msg": "",
     *         "data": [
     *            {
     *              "id": 2,
     *              "distribution_channel_id": 2,
     *              "uid": 1,
     *              "product_id": 1,
     *              "price": "1.00",
     *              "status": "PAID",
     *              "pay_type": "WECHAT",
     *              "trade_no": "201711301125401585459852216605",
     *              "servicer": "AllinPay",
     *              "transaction_id": "4200000013201711307989945956",
     *              "pay_end_at": "2017-12-01 10:20:04",
     *              "create_ip": "::1",
     *              "send_order_id": 0,
     *              "send_order_name": "",
     *              "created_at": 1512094804,
     *              "updated_at": 1512094804
     *            }
     *        ]
     *     }
     */
    function exportOrders(Request $request)
    {
        $params = [];
        $request->has('uid') && $request->input('uid') && $params['uid'] = (int)$request->input('uid');
        $request->has('trade_no') && $request->input('trade_no') && $params['trade_no'] = trim($request->input('trade_no'));
        $request->has('end_time') && $request->input('end_time') && $params['end_time'] = trim($request->input('end_time'));
        $request->has('start_time') && $request->input('start_time') && $params['begin_time'] = trim($request->input('start_time'));
        $request->has('send_order_id') && $request->input('send_order_id') && $params['send_order_id'] = (int)$request->input('send_order_id');
        $request->has('transaction_id') && $request->input('transaction_id') && $params['transaction_id'] = trim($request->input('transaction_id'));
        $request->has('distribution_channel_id') && $request->input('distribution_channel_id') && $params['distribution_channel_id'] = (int)$request->input('distribution_channel_id');
        $status =  $request->has('status') ? $request->input('status'):0;

        if (0==$status){
            $params['status'] = 'PAID';
        }elseif (1==$status){
            $params['status'] = 'UNPAID';
        }

        $obj = OrderService::searchObj($params);
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "充值记录" . date("YmdHis") . ".csv");
        echo("\"" . mb_convert_encoding("\"支付单号\",\"三方平台交易单号\",\"用户ID\",\"充值金额\",\"支付方式\",\"创建时间\",\"支付时间\",\"渠道id\",\"渠道名称\",\"派单名称\",\"派单id\"\r\n", "GBK", "UTF-8") . "\",");

        $obj->chunk(2000, function ($orders){
            foreach ($orders as $item) {
                echo("\"" . mb_convert_encoding('`' . $item->trade_no, "GBK", "UTF-8") . "\",");
                echo("\"" . mb_convert_encoding('`' . $item->transaction_id, "GBK", "UTF-8") . "\",");
                echo("\"" . $item->uid . "\",");
                echo("\"" . $item->price . "\",");
                echo("\"" . $item->pay_type . "\",");
                echo("\"" . mb_convert_encoding(date('Y-m-d H:i:s', strtotime($item->created_at)), "GBK", "UTF-8") . "\",");
                echo("\"" . mb_convert_encoding(date('Y-m-d H:i:s', strtotime($item->pay_end_at)), "GBK", "UTF-8") . "\",");
                echo("\"" . $item->distribution_channel_id . "\",");
                $channel_name = ChannelService::getChannelNicknameById($item->distribution_channel_id);
                echo("\"" . mb_convert_encoding($channel_name ? $channel_name : '未知', "GBK", "UTF-8") . "\",");
                echo("\"" . mb_convert_encoding($item->send_order_name, "GBK", "UTF-8") . "\",");
                echo("\"" . $item->send_order_id . "\"\r\n");
            }
        });
        exit();
    }

    /*
     * 渠道当日数据
     */
    function today(Request $request)
    {
        $distribution_channel_id = $request->has('distribution_channel_id') ? (int)$request->input('distribution_channel_id') : null;
        $begin_time = date('Y-m-d');
        $end_time = date('Y-m-d H:i:s', strtotime($begin_time) + 86400 - 1);
        $data_obj = DB::table('orders')->select(DB::raw("count(*) paid_num,sum(price) amount,count(distinct uid) pay_user_num"))
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->where('status', 'PAID');

        //支付订单数据
        if ($distribution_channel_id) $data_obj->where('distribution_channel_id', $distribution_channel_id);
        $data = $data_obj->first();

        //总订单数
        $total_order_num_obj = DB::table('orders')->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time);
        if ($distribution_channel_id) $total_order_num_obj->where('distribution_channel_id', $distribution_channel_id);
        $total_order_num = $total_order_num_obj->count();

        if ($distribution_channel_id) {
            $first_data = DB::select("select count(distinct uid) count,sum(price) amount from orders a where created_at>'{$begin_time}' and created_at<'{$end_time}' and status ='PAID' and distribution_channel_id='{$distribution_channel_id}' and not exists (select uid from orders where a.uid = uid and created_at < '{$begin_time}' and status= 'PAID' limit 1)");
            $total_pay_user_num = DB::select("select count(distinct uid) count from orders where created_at>'{$begin_time}' and distribution_channel_id='{$distribution_channel_id}' and created_at<'{$end_time}' and status ='PAID'");
            $uv_pv = SendOrderService::getChannelPromotionTotalUvPv($distribution_channel_id, $begin_time);
        } else {
            $first_data = DB::select("select count(distinct uid) count,sum(price) amount from orders a where created_at>'{$begin_time}' and created_at<'{$end_time}' and status ='PAID' and not exists (select uid from orders where a.uid = uid and created_at < '{$begin_time}' and status= 'PAID' limit 1)");
            $total_pay_user_num = DB::select("select count(distinct uid) count from orders where created_at>'{$begin_time}' and created_at<'{$end_time}' and status ='PAID'");
            //$uv_pv = SendOrderService::getChannelPromotionTotalUvPv(null, $begin_time);
            $uv_pv = ['uv'=>0,'pv'=>0];//SendOrderService::getChannelPromotionTotalUvPv(null, $begin_time);
        }

        //查当日关注人数
        $force_user_num = ForceSubscribeService::forceSubscribeUserCountByChannelIdAndDate($distribution_channel_id, $begin_time, $end_time);

        $data = [
            'send_order_num' => SendOrderService::getPromotionCount(compact('begin_time', 'end_time', 'distribution_channel_id')),
            'real_send_order_num' => SendOrderService::getRealPromotionCount(compact('begin_time', 'end_time', 'distribution_channel_id')),
            'new_user_num' => $uv_pv['uv'],
            'click_num' => $uv_pv['pv'],
            'register_user_num' => User::getTotalCount(compact('begin_time', 'end_time')),
            'pay_user_num' => (int)$total_pay_user_num[0]->count,
            'first_pay_user_num' => (int)$first_data[0]->count,
            'not_first_pay_user_num' => $total_pay_user_num[0]->count - $first_data[0]->count,
            'recharge_amount' => (float)$data->amount,
            'first_pay_amount' => (float)$first_data[0]->amount,
            'not_first_pay_amount' => round($data->amount - $first_data[0]->amount, 2),
            'pay_success_rate' => $total_order_num ? round(100 * $data->paid_num / $total_order_num, 2) . '%' : '0%',
            'force_user_num' => (int)$force_user_num
        ];
        return response()->success($data);
    }


    /**
     * 渠道当日数据列表
     */
    function todayChannelList(Request $request)
    {
        $begin_time = date('Y-m-d');
        $end_time = date('Y-m-d') . " 23:59:59";

        $distribution_channel_id = $request->has('distribution_channel_id') ? (int)$request->input('distribution_channel_id') : null;

        $fields = [
            'distribution_channel_id' => "",
            'channel_name' => "",
            'channel_nickname' => "",
            'paid_success_num' => 0,
            'amount' => 0,
            'pay_user_success_num' => 0,
            'paid_num' => 0,
            'register_user_num' => 0,
            'force_user_num' => 0,
            'send_order_create_num' => 0,
            'send_order_today_num' => 0,
        ];
        $result = [];

        //SQL0     渠道列表
        /*
           [{
                "distribution_channel_id": 1,
                "channel_name": "赵娃",
                "channel_nickname": "哥哥不可以1"
            }]
         */
        $dataChannelObj1 = DB::table('distribution_channels') ->join('channel_users','channel_users.id','=','distribution_channels.channel_user_id')
            ->join('companies','companies.id','=','channel_users.company_id')
            ->select('distribution_channels.id as distribution_channel_id', 'distribution_channels.name as channel_name', 'companies.name as channel_nickname','companies.fans_gender');
        if ($distribution_channel_id) $dataChannelObj1->where('distribution_channels.id', $distribution_channel_id);
        $dataChannel1 = $dataChannelObj1->get();
        foreach ($dataChannel1 as $data) {
            $data = json_decode(json_encode($data), true);
            switch ($data['fans_gender']){
                case 1:$data['fans_gender'] = '男';break;
                case 2:$data['fans_gender'] = '女';break;
                case 3:$data['fans_gender'] = '混合粉';break;
                default:$data['fans_gender'] = '';
            }
            $result[$data['distribution_channel_id']] = array_merge($fields, $data);
        }


        //SQL1     成功充值总额   成功充值订单数   成功付费人数
        /*
           [{
                "distribution_channel_id": 1,
                "paid_success_num": 2,//成功充值订单数
                "amount": "0.02",//成功充值总额
                "pay_user_success_num": 1,//成功付费人数
            }]
         */
        $dataOrderObj1 = DB::table('orders')->select('distribution_channel_id', DB::raw("count(*) paid_success_num, sum(price) amount, count(distinct uid) pay_user_success_num"))
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->where('status', 'PAID')
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataOrderObj1->where('distribution_channel_id', $distribution_channel_id);
        $dataOrder1 = $dataOrderObj1->get();
        if ($dataOrder1 && sizeof($dataOrder1) > 0) {
            foreach ($dataOrder1 as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }


        //SQL2               	  总订单数
        /*
           [{
                "distribution_channel_id": 1,
                "paid_num": 2,//总订单数
            }]
         */
        $dataOrderObj2 = DB::table('orders')->select('distribution_channel_id', DB::raw("count(*) paid_num"))
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataOrderObj2->where('distribution_channel_id', $distribution_channel_id);
        $dataOrder2 = $dataOrderObj2->get();
        if ($dataOrder2 && sizeof($dataOrder2) > 0) {
            foreach ($dataOrder2 as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }



        //SQL3     注册用户数
        /*
           [{
                "distribution_channel_id": 1,
                "register_user_num": 2,//注册用户数
            }]
         */
        $dataRegisterUserObj = DB::table('users')->select('distribution_channel_id', DB::raw("count(*) register_user_num"))
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataRegisterUserObj->where('distribution_channel_id', $distribution_channel_id);
        $dataRegisterUser = $dataRegisterUserObj->get();
        if ($dataRegisterUser && sizeof($dataRegisterUser) > 0) {
            foreach ($dataRegisterUser as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }



        //SQL4     新增关注用户数
        /*
          [{
               "distribution_channel_id": 1,
               "force_user_num": 2,//新增关注用户数
           }]
        */
        $dataForceUserObj = DB::table('force_subscribe_users')->select('distribution_channel_id', DB::raw("count(*) force_user_num"))
            //->where('is_subscribed', '=', 1)
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataForceUserObj->where('distribution_channel_id', $distribution_channel_id);
        $dataForceUser = $dataForceUserObj->get();
        if ($dataForceUser && sizeof($dataForceUser) > 0) {
            foreach ($dataForceUser as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }

        //SQL4     派单创建数
        /*
           [{
                "distribution_channel_id": 1,
                "send_order_create_num": 2,//派单创建数
            }]
         */

        $dataSendOrderObj1 = DB::table('send_orders')->select('distribution_channel_id', DB::raw("count(*) send_order_create_num"))
            ->where('created_at', '>=', $begin_time)
            ->where('created_at', '<=', $end_time)
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataSendOrderObj1->where('distribution_channel_id', $distribution_channel_id);
        $dataSendOrder1 = $dataSendOrderObj1->get();
        if ($dataSendOrder1 && sizeof($dataSendOrder1) > 0) {
            foreach ($dataSendOrder1 as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }


        //SQL5     今日派单数
        /*
           [{
                "distribution_channel_id": 1,
                "send_order_today_num": 2,//今日派单数
            }]
         */
        $dataSendOrderObj2 = DB::table('send_orders')->select('distribution_channel_id', DB::raw("count(*) send_order_today_num"))
            ->where('send_time', '>=', $begin_time)
            ->where('send_time', '<=', $end_time)
            ->groupby('distribution_channel_id');
        if ($distribution_channel_id) $dataSendOrderObj2->where('distribution_channel_id', $distribution_channel_id);
        $dataSendOrder2 = $dataSendOrderObj2->get();
        if ($dataSendOrder2 && sizeof($dataSendOrder2) > 0) {
            foreach ($dataSendOrder2 as $data) {
                $data = json_decode(json_encode($data), true);
                if (isset($result[$data['distribution_channel_id']])) {
                    $result[$data['distribution_channel_id']] = array_merge($result[$data['distribution_channel_id']], $data);
                } else {
                    $result[$data['distribution_channel_id']] = array_merge($fields, $data);
                }
            }
        }



        if ($result && sizeof($result) > 0) {
            $result = array_values($result);
        }

        return response()->collection(new OrderTodayStatTransformer(), $result);
    }

    /**
     * 开给内部协同系统的接口
     */
    function internalSearch(Request $request)
    {
        $begin_time = $request->has('begin_time') ? $request->input('begin_time') : date("Y-m-d",strtotime('-7 days'));
        $end_time = $request->has('end_time') ? $request->input('end_time') : date("Y-m-d")." 23:59:59";
        $send_order_id = $request->has('send_order_id') ? $request->input('send_order_id') : '';

        if(!$send_order_id) return response()->error('PARAM_ERROR');

        $amount = OrderService::getAmount(compact('send_order_id','begin_time','end_time'));

        return response()->success(compact('amount'));

    }
}