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

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

namespace App\Http\Controllers\Manage\Trade;

use App\Http\Controllers\Manage\Finance\BaseController;
use App\Http\Controllers\Manage\Trade\Transformers\OrderDayStatOutPayTransformer;
use App\Http\Controllers\Manage\Trade\Transformers\OrderDayStatTransformer;
use App\Http\Controllers\Manage\Trade\Transformers\OrderDaySumStatTransformer;
use App\Http\Controllers\Manage\Trade\Transformers\OrderMonthTransformer;
use App\Http\Controllers\Manage\Trade\Transformers\OrderChannelMonthTransformer;
use App\Modules\Trade\Services\OrderDayStatService;
use Illuminate\Http\Request;
use App\Modules\User\Services\UserSignService;
use App\Modules\Channel\Services\ChannelService;
use App\Modules\SendOrder\Services\SendOrderService;
use DB;


class OrderDayStatsController extends BaseController
{

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

    /**
     * @apiVersion 1.0.0
     * @api {GET} trade/orderDayStats 获取渠道日统计列表
     * @apiGroup Trade
     * @apiName orderDayStats
     * @apiParam   {Number}  distribution_channel_id 分销渠道ID
     * @apiParam   {String}  begin_time 开始时间
     * @apiParam   {String}  begin_time 结束时间
     * @apiSuccess {String}  date 日期.
     * @apiSuccess {Number}  distribution_channel_id 渠道id.
     * @apiSuccess {Number}  pay_success_user_num 总付费用户数.
     * @apiSuccess {Number}  total_recharge_amount 总付费总额.
     * @apiSuccess {Number}  unpaid_num 未付费订单数.
     * @apiSuccess {Number}  paid_num 付费订单数.
     * @apiSuccess {Number}  promotion_total_uv 推广uv数.
     * @apiSuccess {Number}  promotion_total_pv 推广pv数.
     * @apiSuccess {Number}  send_order_num 派单数.
     * @apiSuccess {Number}  register_user_num 注册用户数.
     * @apiSuccess {Number}  force_user_num 关注用户数.
     * @apiSuccess {Number}  ticket_recharge_user_num 普通充值用户数
     * @apiSuccess {Number}  year_recharge_user_num 包年VIP充值用户数
     * @apiSuccess {Number}  actual_send_order_num 实际派单数
     * @apiSuccess {Number}  sign_num 签到人数
     * @apiSuccess {String}  created_at 创建时间
     * @apiSuccess {String}  updated_at 更新时间
     */
    function index(Request $request)
    {
        //根据名称查渠道ID
        $temp_distribution_channel_name = $distribution_channel_name = $request->has('name') ?$request->input('name') : '';
        $distribution_channel_id = $request->has('distribution_channel_id') ? (int)$request->input('distribution_channel_id') : null;
        $temp_distribution_channel_id = $distribution_channel_id;
        $orderBy = $request->has('orderBy') ? $request->input('orderBy') : '';

        if ($distribution_channel_name) {
            $distribution_channel_id = [];
            $channels = ChannelService::getChannelList(['company_name' => $distribution_channel_name]);
            foreach ($channels as $channel) {
                $distribution_channel_id[] = $channel->id;
            }
        }

        $begin_time = $request->has('begin_time') ? $request->input('begin_time') : '';
        $end_time = $request->has('end_time') ? $request->input('end_time') : '';

        $stats = OrderDayStatService::search(compact('distribution_channel_id', 'begin_time', 'end_time', 'orderBy'));
        $stats = $this->OrderDayStatAndSign($stats, compact('distribution_channel_id', 'begin_time', 'end_time'));
        $ret = [];
        $trans = new OrderDayStatTransformer();

        $ret['list'] = [];
        $empty_fill = false;
        $temp_res = [];
        $where = [];
        if ($stats->isEmpty()) {
            if($temp_distribution_channel_id){
                $where[] = ['distribution_channels.id','=',$temp_distribution_channel_id];
            }
            if($temp_distribution_channel_name){
                $where[] = ['companies.name','like','%'.$temp_distribution_channel_name.'%'];
            }
            if($where){
                $temp_res = DB::table('distribution_channels')
                    ->join('channel_users','channel_users.id','=','distribution_channels.channel_user_id')
                    ->join('companies','companies.id','=','channel_users.company_id')
                    ->where($where)
                    ->select('distribution_channels.id as distribution_channel_id','companies.name as distribution_channel_name','companies.fans_gender')
                    ->get();
                if ($temp_res) {
                    $empty_fill = true;
                }
            }
        }
        if ($empty_fill) {
            foreach ($temp_res as $item) {
                $ret['list'][] = $trans->transformDefault($item);
            }
        }
        foreach ($stats as $item) {
            switch ($item['fans_gender']){
                case 1:$item['fans_gender']='男粉';break;
                case 2:$item['fans_gender']='女粉';break;
                case 3:$item['fans_gender']='混合粉';break;
                default:$item['fans_gender']='';
            }
            $ret['list'][] = $trans->transform($item);
        }

        $ret['meta'] = [
            'total' => (int)$stats->total(),
            'per_page' => (int)$stats->perPage(),
            'current_page' => (int)$stats->currentPage(),
            'last_page' => (int)$stats->lastPage(),
            'next_page_url' => (string)$stats->nextPageUrl(),
            'prev_page_url' => (string)$stats->previousPageUrl()
        ];


        return response()->success($ret);
    }

    /**
     * @apiVersion 1.0.0
     * @api {GET} trade/getStatByChannel 获取渠道日统计列表
     * @apiGroup Trade
     * @apiName getStatByChannel
     * @apiParam   {String}  begin_time 开始时间
     * @apiParam   {String}  begin_time 结束时间
     * @apiSuccess {String}  date 日期.
     * @apiSuccess {Number}  distribution_channel_id 渠道id.
     * @apiSuccess {Number}  pay_success_user_num 总付费用户数.
     * @apiSuccess {Number}  total_recharge_amount 总付费总额.
     * @apiSuccess {Number}  unpaid_num 未付费订单数.
     * @apiSuccess {Number}  paid_num 付费订单数.
     * @apiSuccess {Number}  promotion_total_uv 推广uv数.
     * @apiSuccess {Number}  promotion_total_pv 推广pv数.
     * @apiSuccess {Number}  send_order_num 派单数.
     * @apiSuccess {Number}  register_user_num 注册用户数.
     * @apiSuccess {Number}  force_user_num 关注用户数.
     * @apiSuccess {Number}  ticket_recharge_user_num 普通充值用户数
     * @apiSuccess {Number}  year_recharge_user_num 包年VIP充值用户数
     * @apiSuccess {String}  created_at 创建时间
     * @apiSuccess {String}  updated_at 更新时间
     */
    function getStatByChannel(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");

        $stat = OrderDayStatService::getStatByChannel(compact('begin_time', 'end_time'));
        return response()->collection(new OrderDaySumStatTransformer(), $stat);
    }

    function exportStatByChannel(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");

        $stats = OrderDayStatService::getStatByChannel(compact('begin_time', 'end_time'));

        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "日统计列表" . date("YmdHis") . ".csv");
        echo iconv("UTF-8", "GBK", "\"日期\",\"付费用户数\",\"充值总额\",\"外部通道充值总额\",\"uv\",\"pv\",\"创建派单数\",\"实际派单数\",\"注册用户数\",\"实际注册用户数\",\"强关用户数\",\"首充用户数\",\"首充金额\",\"非首充用户数\",\"非首充金额\",\"订单成功率\",\"首日注册充值金额\",\"首日注册付费人数\"\r\n");
        if ($stats) {
            foreach ($stats as $stat) {
                echo("\"" . $stat->date . "\",");
                echo("\"" . (int)$stat->pay_success_user_num . "\",");
                echo("\"" . (float)$stat->total_recharge_amount . "\",");
                echo("\"" . (float)$stat->channel_merchant_recharge_amount . "\",");
                echo("\"" . (int)$stat->promotion_total_uv . "\",");
                echo("\"" . (int)$stat->promotion_total_pv . "\",");
                echo("\"" . (int)$stat->send_order_num . "\",");
                echo("\"" . (int)$stat->real_send_order_num . "\",");
                echo("\"" . (int)$stat->register_user_num . "\",");
                echo("\"" . (int)$stat->real_register_user_num . "\",");
                echo("\"" . (int)$stat->force_user_num . "\",");
                echo("\"" . (int)$stat->first_recharge_user_num . "\",");
                echo("\"" . (float)$stat->first_recharge_amount . "\",");
                echo("\"" . ((int)$stat->pay_success_user_num - (float)$stat->first_recharge_user_num) . "\",");
                echo("\"" . ((float)$stat->total_recharge_amount - (float)$stat->first_recharge_amount) . "\",");
                $per = (float)$stat->unpaid_num + (float)$stat->paid_num > 0 ? round(((float)$stat->paid_num / ((float)$stat->unpaid_num + (float)$stat->paid_num)) * 100, 2) : 0;
                echo("\"" . $per . "%\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_amount . "\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_user_num . "\"\r\n");
            }
        }
        exit();
    }

    function export(Request $request)
    {
        $distribution_channel_id = $request->has('distribution_channel_id') ? (int)$request->input('distribution_channel_id') : null;
        $begin_time = $request->has('begin_time') ? $request->input('begin_time') : '';
        $end_time = $request->has('end_time') ? $request->input('end_time') : '';

        $stats = OrderDayStatService::search(compact('distribution_channel_id', 'begin_time', 'end_time'), true);
        $stats = $this->OrderDayStatAndSign($stats, compact('distribution_channel_id', 'begin_time', 'end_time'));
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "日统计列表" . date("YmdHis") . ".csv");
        echo iconv("UTF-8", "GBK", "\"渠道ID\",\"渠道名称\",\"日期\",\"充值总额\",\"创建派单数\",\"实际派单数\",\"uv\",\"总点击数\",\"新增关注数\",\"注册用户数\",\"普通付费人数\",\"包年付费人数\",\"订单成功率\",\"签到人数\",\"首充用户数\",\"首充金额\",\"注册用户首充金额\",\"注册用户首充用户数\"\r\n");
        if ($stats) {
            foreach ($stats as $stat) {

                echo("\"" . $stat->distribution_channel_id . "\",");
                echo("\"" . iconv("UTF-8", "GBK", $stat->distribution_channel_name) . "\",");
                echo("\"" . $stat->date . "\",");
                echo("\"" . (float)$stat->total_recharge_amount . "\",");
                echo("\"" . (int)$stat->send_order_num . "\",");
                echo("\"" . (int)$stat->real_send_order_num . "\",");
                echo("\"" . (int)$stat->promotion_total_uv . "\",");
                echo("\"" . (int)$stat->promotion_total_pv . "\",");
                echo("\"" . (int)$stat->force_user_num . "\",");
                echo("\"" . (int)$stat->register_user_num . "\",");
                echo("\"" . (int)$stat->ticket_recharge_user_num . "\",");
                echo("\"" . $stat->year_recharge_user_num . "\",");
                $per = (float)$stat->unpaid_num + (float)$stat->paid_num > 0 ? round(((float)$stat->paid_num / ((float)$stat->unpaid_num + (float)$stat->paid_num)) * 100, 2) : 0;
                echo("\"" . $per . "%\",");
                echo("\"" . (int)$stat->sign_num . "\",");
                echo("\"" . (int)$stat->first_recharge_user_num . "\",");
                echo("\"" . (float)$stat->first_recharge_amount . "\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_amount . "\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_user_num . "\"\r\n");

            }
        }
        exit();
    }


    private function OrderDayStatAndSign($target, $data)
    {

        //$sing_data = UserSignService::getSignTotalByChnnelId($data);
        if (true) {
            foreach ($target as &$v) {
                //实际派单数
                $v->actual_send_order_num = 0;
                $v->sign_num = 0;
                /*
                $v->actual_send_order_num = SendOrderService::getActualSendOrderNum($v->distribution_channel_id,$v->date);
                if(!$v->actual_send_order_num){

                }

                foreach ($sing_data as $val){
                    if($v->distribution_channel_id == $val->distribution_channel_id && $v->date == $val->day){
                        $v->sign_num = $val->sign_num;
                        break;
                    }
                }
                */
            }
        }
        return $target;
    }

    public function getAllGroupbyMonth()
    {
        $data = OrderDayStatService::getAllGroupbyMonth();
        return response()->collection(new OrderMonthTransformer(), $data);
    }

    public function getAllGroupbyMonthChannel(Request $request)
    {
        $month = $request->has('month') ? $request->input('month') : '';
        $distribution_channel_id = $request->has('distribution_channel_id') ? $request->input('distribution_channel_id') : '';
        $distribution_channel_name = $request->has('distribution_channel_name') ? $request->input('distribution_channel_name') : '';
        $data = OrderDayStatService::getAllGroupbyMonthChannel(compact('month', 'distribution_channel_id', 'distribution_channel_name'), 1);
        return response()->collection(new OrderChannelMonthTransformer(), $data);
    }

    public function exportAllGroupbyMonthChannel(Request $request)
    {
        $month = $request->has('month') ? $request->input('month') : '';
        $stats = OrderDayStatService::getAllGroupbyMonthChannel(compact('month'), 1);

        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "渠道按月统计列表" . date("YmdHis") . ".csv");
        echo iconv("UTF-8", "GBK", "\"渠道ID\",\"渠道名称\",\"月份\",\"充值总额\",\"派单数\",\"实际派单数\",\"uv\",\"实际注册用户数\",\"pv\",\"新增关注数\",\"普通付费人数\",\"包年付费人数\",\"订单成功率\",\"签到人数\"\r\n");
        if ($stats) {
            foreach ($stats as $stat) {

                echo("\"" . $stat->distribution_channel_id . "\",");
                echo("\"" . iconv("UTF-8", "GBK", $stat->distribution_channel_name) . "\",");
                echo("\"" . date("Y-m", strtotime($stat->date)) . "\",");
                echo("\"" . (float)$stat->total_recharge_amount . "\",");
                echo("\"" . (int)$stat->send_order_num . "\",");
                echo("\"" . (int)$stat->actual_send_order_num . "\",");
                echo("\"" . (int)$stat->promotion_total_uv . "\",");
                echo("\"" . (int)$stat->real_register_user_num . "\",");
                echo("\"" . (int)$stat->promotion_total_pv . "\",");
                echo("\"" . (int)$stat->force_user_num . "\",");
                echo("\"" . (int)$stat->ticket_recharge_user_num . "\",");
                echo("\"" . $stat->year_recharge_user_num . "\",");
                $per = (float)$stat->unpaid_num + (float)$stat->paid_num > 0 ? round(((float)$stat->paid_num / ((float)$stat->unpaid_num + (float)$stat->paid_num)) * 100, 2) : 0;
                echo("\"" . $per . "%\",");
                echo("\"" . (int)$stat->sign_num . "\r\n");

            }
        }
    }


    /**
     * @apiVersion 1.0.0
     * @apiDescription 站外支付数据
     * @api {GET} trade/getOutPayData
     * @apiGroup Trade
     * @apiName getOutPayData
     * @apiParam   {String}  [distribution_channel_id] 渠道ID
     * @apiParam   {String}  [search_name] 搜索名称
     * @apiParam   {String}  [start_time] 开始时间2017-01-01.(可不传)
     * @apiParam   {String}  [end_time] 结束时间2017-02-01.(可不传)
     *
     * @apiSuccess   {String}  distribution_channel_id 渠道ID
     * @apiSuccess   {String}  distribution_channel_name 渠道名称
     * @apiSuccess   {String}  date 日期.
     * @apiSuccess   {String}  month 日期.
     * @apiSuccess   {Number}  channel_merchant_recharge_amount 站外总金额.
     * @apiSuccess   {Number}  pay_merchant_id 支付渠道ID.
     * @apiSuccessExample {json} Success-Response:
     *
     *     {
     *         "code": 0,
     *         "msg": "",
     *         "data":
     *              {
     *                  "list":[
     *                             {
     *                             "distribution_channel_id": 70,
     *                             "distribution_channel_name": "梦嘉男号50",
     *                              "month": "2018-05",
     *                              "date": "",
     *                              "channel_merchant_recharge_amount": 18,
     *                              "pay_merchant_id": 0
     *                              }
     *                          ]
     *                   "meta":{
     *                      "total": 4,
     *                       "per_page": 15,
     *                       "current_page": 1,
     *                       "last_page": 1,
     *                       "next_page_url": "",
     *                       "prev_page_url": ""
     *                  }
     *               }
     *     }
     */
    function getOutPayData(Request $request)
    {
        $distribution_channel_id = $request->has('distribution_channel_id') ? $request->input('distribution_channel_id') : '';

        $start_time = $request->has('start_time') && !empty($request->input('start_time')) ? date('Ymd', strtotime($request->input('start_time'))) : '';
        $end_time = $request->has('end_time') && !empty($request->input('end_time')) ? date('Ymd', strtotime($request->input('end_time'))) : '';
        $end_time = self::getMaxDay($end_time);

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


        $params = [
            'distribution_channel_id' => $distribution_channel_id,
            'distribution_channel_name' => $search_name,
            'start_date' => $start_time,
            'end_date' => $end_time,
        ];

        $data = OrderDayStatService::getOutPayData($params);

        return response()->pagination(new OrderDayStatOutPayTransformer(), $data);
    }

    /**
     * @apiVersion 1.0.0
     * @apiDescription 站外支付数据导出
     * @api {GET} trade/getOutPayDataExport
     * @apiGroup Trade
     * @apiName getOutPayDataExport
     * @apiParam   {String}  [distribution_channel_id] 渠道ID
     * @apiParam   {String}  [search_name] 搜索名称
     * @apiParam   {String}  [start_time] 开始时间2017-01-01.(可不传)
     * @apiParam   {String}  [end_time] 结束时间2017-02-01.(可不传)
     *
     * @apiSuccess   {String}  distribution_channel_id 渠道ID
     * @apiSuccess   {String}  distribution_channel_name 渠道名称
     * @apiSuccess   {String}  date 日期.
     * @apiSuccess   {String}  month 日期.
     * @apiSuccess   {Number}  channel_merchant_recharge_amount 站外总金额.
     * @apiSuccess   {Number}  pay_merchant_id 支付渠道ID.
     * @apiSuccessExample {json} Success-Response:
     *
     *     {
     *         "code": 0,
     *         "msg": "",
     *         "data":
     *              {
     *                  "list":[
     *                             {
     *                             "distribution_channel_id": 70,
     *                             "distribution_channel_name": "梦嘉男号50",
     *                              "month": "2018-05",
     *                              "date": "",
     *                              "channel_merchant_recharge_amount": 18,
     *                              "pay_merchant_id": 0
     *                              }
     *                          ]
     *                   "meta":{
     *                      "total": 4,
     *                       "per_page": 15,
     *                       "current_page": 1,
     *                       "last_page": 1,
     *                       "next_page_url": "",
     *                       "prev_page_url": ""
     *                  }
     *               }
     *     }
     */
    function getOutPayDataExport(Request $request)
    {
        $distribution_channel_id = $request->has('distribution_channel_id') ? $request->input('distribution_channel_id') : '';

        $start_time = $request->has('start_time') && !empty($request->input('start_time')) ? date('Ymd', strtotime($request->input('start_time'))) : '';
        $end_time = $request->has('end_time') && !empty($request->input('end_time')) ? date('Ymd', strtotime($request->input('end_time'))) : '';
        $end_time = self::getMaxDay($end_time);

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


        $params = [
            'distribution_channel_id' => $distribution_channel_id,
            'distribution_channel_name' => $search_name,
            'start_date' => $start_time,
            'end_date' => $end_time,
        ];

        $data = OrderDayStatService::getOutPayData($params, true);

        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "站外渠道支付数据" . date("YmdHis") . ".csv");
        echo iconv("UTF-8", "GBK", "\"渠道ID\",\"渠道名称\",\"月份\",\"日期\",\"站外金额(元)\",\"支付渠道ID\"\r\n");
        if ($data) {
            foreach ($data as $item) {
                echo("\"" . iconv("UTF-8", "GBK", trim($item->distribution_channel_id)) . "\",");
                echo("\"" . iconv("UTF-8", "GBK", trim($item->distribution_channel_name)) . "\",");
                echo("\"" . iconv("UTF-8", "GBK", trim($item->month)) . "\",");
                echo("\"" . iconv("UTF-8", "GBK", trim($item->date)) . "\",");
                echo("\"" . iconv("UTF-8", "GBK", trim($item->channel_merchant_recharge_amount)) . "\",");
                echo("\"" . iconv("UTF-8", "GBK", trim($item->pay_merchant_id)) . "\"\r\n");
            }
        }
        exit();
    }

    function getStatByCompany(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");
        $company_id = $request->has('company_id') && $request->input('company_id') ? $request->input('company_id') : 0;
        $company_name = $request->has('company_name') && $request->input('company_name') ? $request->input('company_name') : '';
        $where = "order_day_stats.date >= '{$begin_time}' and order_day_stats.date <= '{$end_time}'";
        if ($company_id) $where .= " and company_id = {$company_id}";
        if ($company_name) $where .= " and company_name like '%{$company_name}%'";
        $data = DB::select("select company_id,
                           company_name,
                           fans_gender,
                           sum(pay_success_user_num) pay_success_user_num,
                           sum(total_recharge_amount) total_recharge_amount,
                           sum(unpaid_num) unpaid_num,
                           sum(paid_num) paid_num,
                           sum(promotion_total_uv) promotion_total_uv,
                           sum(promotion_total_pv) promotion_total_pv,
                           sum(send_order_num) send_order_num,
                           sum(real_send_order_num) real_send_order_num,
                           sum(register_user_num) register_user_num,
                           sum(real_register_user_num) real_register_user_num,
                           sum(force_user_num) force_user_num,
                           sum(ticket_recharge_user_num) ticket_recharge_user_num,
                           sum(year_recharge_user_num) year_recharge_user_num,
                           sum(first_recharge_user_num) first_pay_user_num,
                           sum(first_recharge_amount) first_pay_amount,
                           sum(reg_user_first_recharge_user_num) reg_user_first_recharge_user_num,
                           sum(reg_user_first_recharge_amount) reg_user_first_recharge_amount from order_day_stats,companies,channel_users where {$where} and channel_users.id = order_day_stats.channel_user_id and companies.id = channel_users.company_id  group by company_id");


        if ($data) {
            foreach ($data as $k => $v) {
                $data[$k]->unpaid_num = (int)$v->unpaid_num;
                $data[$k]->paid_num = (int)$v->paid_num;
                $data[$k]->total_recharge_amount = (int)$v->total_recharge_amount;
                $data[$k]->first_pay_amount = (int)$v->first_pay_amount;
                $data[$k]->not_first_pay_user_num = (float)$v->pay_success_user_num - (float)$v->first_pay_user_num;
                $data[$k]->not_first_pay_amount = round((float)$v->total_recharge_amount - (float)$v->first_pay_amount, 2);
                switch ($v->fans_gender){
                    case 1:$v->fans_gender = '男';break;
                    case 2:$v->fans_gender = '女';break;
                    case 3:$v->fans_gender = '混合粉';break;
                    default:$v->fans_gender = '';
                }
                $data[$k]->fans_gender = $v->fans_gender;
            }
        }

        return response()->success($data);
    }

    function exportStatByCompany(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");
        $company_id = $request->has('company_id') && $request->input('company_id') ? $request->input('company_id') : 0;
        $company_name = $request->has('company_name') && $request->input('company_name') ? $request->input('company_name') : '';
        $where = "order_day_stats.date >= '{$begin_time}' and order_day_stats.date <= '{$end_time}'";
        if ($company_id) $where .= " and company_id = {$company_id}";
        if ($company_name) $where .= " and company_name like '%{$company_name}%'";
        $data = DB::select("select company_id,
                           company_name,
                           sum(pay_success_user_num) pay_success_user_num,
                           sum(total_recharge_amount) total_recharge_amount,
                           sum(unpaid_num) unpaid_num,
                           sum(paid_num) paid_num,
                           sum(promotion_total_uv) promotion_total_uv,
                           sum(promotion_total_pv) promotion_total_pv,
                           sum(send_order_num) send_order_num,
                           sum(real_send_order_num) real_send_order_num,
                           sum(register_user_num) register_user_num,
                           sum(real_register_user_num) real_register_user_num,
                           sum(force_user_num) force_user_num,
                           sum(ticket_recharge_user_num) ticket_recharge_user_num,
                           sum(year_recharge_user_num) year_recharge_user_num,
                           sum(first_recharge_user_num) first_pay_user_num,
                           sum(first_recharge_amount) first_pay_amount,
                           sum(reg_user_first_recharge_user_num) reg_user_first_recharge_user_num,
                           sum(reg_user_first_recharge_amount) reg_user_first_recharge_amount from order_day_stats,companies,channel_users where {$where} and channel_users.id = order_day_stats.channel_user_id and companies.id = channel_users.company_id  group by company_id");

        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . "按公司统计列表" . date("YmdHis") . ".csv");
        echo iconv("UTF-8", "GBK", "\"公司ID\",\"公司名称\",\"充值总额\",\"创建派单数\",\"实际派单数\",\"uv\",\"总点击数\",\"新增关注数\",\"注册用户数\",\"普通付费人数\",\"包年付费人数\",\"订单成功率\",\"首充用户数\",\"首充金额\",\"注册用户首充金额\",\"注册用户首充用户数\"\r\n");
        if ($data) {
            foreach ($data as $stat) {

                echo("\"" . $stat->company_id . "\",");
                echo("\"" . iconv("UTF-8", "GBK", $stat->company_name) . "\",");
                echo("\"" . (float)$stat->total_recharge_amount . "\",");
                echo("\"" . (int)$stat->send_order_num . "\",");
                echo("\"" . (int)$stat->real_send_order_num . "\",");
                echo("\"" . (int)$stat->promotion_total_uv . "\",");
                echo("\"" . (int)$stat->promotion_total_pv . "\",");
                echo("\"" . (int)$stat->force_user_num . "\",");
                echo("\"" . (int)$stat->register_user_num . "\",");
                echo("\"" . (int)$stat->ticket_recharge_user_num . "\",");
                echo("\"" . $stat->year_recharge_user_num . "\",");
                $per = (float)$stat->unpaid_num + (float)$stat->paid_num > 0 ? round(((float)$stat->paid_num / ((float)$stat->unpaid_num + (float)$stat->paid_num)) * 100, 2) : 0;
                echo("\"" . $per . "%\",");
                echo("\"" . (int)$stat->first_pay_user_num . "\",");
                echo("\"" . (float)$stat->first_pay_amount . "\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_amount . "\",");
                echo("\"" . (float)$stat->reg_user_first_recharge_user_num . "\"\r\n");

            }
        }
        exit;

    }


}