zhuishuyun_quickapp/app/Http/Controllers/Manage/Book/CpBookController.php

<?php

namespace App\Http\Controllers\Manage\Book;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Modules\Book\Models\BookCp;
use App\Modules\Book\Models\BookConfig;
use DB;
use App\Modules\Subscribe\Models\BookOrder;
use Illuminate\Support\Facades\Storage;
class CpBookController extends Controller
{
    /**
     * @apiDefine BookCP 图书CP模块
     */


    /**
     * @apiVersion 1.0.0
     * @apiDescription 获取所有cp
     * @api {get} bookcp/getAllCp 获取所有cp
     * @apiGroup BookCP
     * @apiName getAllCp
     * @apiSuccess {int}         code          态码
     * @apiSuccess {String}      msg           信息
     * @apiSuccess {object}      data          结果集
     * @apiSuccessExample {json} Success-Response:
     *     HTTP/1.1 200 OK
     *       {
     *           code: 0,
     *           msg: "",
     *           data: [
     *               "quyuewang",
     *               "tianya",
     *               "zhongtian",
     *               "ycsd",
     *               "zhizihuan",
     *               "timeread",
     *               "shuhai",
     *           ]
     *       }
     */
    public function getAllCp(){
        $cps = BookCp::select('cp')->get()->pluck('cp');
        return response()->success($cps);
    }

    /**
     * @apiVersion 1.0.0
     * @apiDescription cp统计数据
     * @api {get} bookcp/cpTotal cp统计数据
     * @apiGroup BookCP
     * @apiName cpTotal
     * @apiParam   {String}      cp            cp名称
     * @apiParam   {String}      [export]      要导出需要传值等于1
     * @apiSuccess {int}         code          态码
     * @apiSuccess {String}      msg           信息
     * @apiSuccess {object}      data          结果集
     * @apiSuccess {object}      data.cp_source     cp名称
     * @apiSuccess {object}      data.count         图书数量
     * @apiSuccess {object}      data.bids          bids(每日数据需要传递的参数)
     * @apiSuccess {object}      data.yesterday     昨日订阅量
     * @apiSuccess {object}      data.month         本月订阅量
     * @apiSuccess {object}      data.prev_month    上月订阅量
     * @apiSuccess {object}      data.today    上月订阅量
     * @apiSuccessExample {json} Success-Response:
     *     HTTP/1.1 200 OK
     *       {
     *           code: 0,
     *           msg: "",
     *           data: [
     *               {
     *                   cp_source: "17k",
     *                   bids: "21,22,582,583,584,591,612,629,634,635,637,638,639,641,642,843,844",
     *                   count: 17,
     *                   yesterday: "68194",
     *                   month: "660188",
     *                   prev_month: "55172"
     *               }
     *           ]
     *       }
     */
    public function cpTotal2(Request $request){
        $cp = $request->input('cp');
        $res = BookConfig::select('cp_source',DB::raw('GROUP_CONCAT(bid) as bids'),DB::raw('count(*) as count'))->where('cp_source','!=','');
        if($cp){
            $res = $res->where('cp_source','like','%'.$cp.'%');
        }
        $res = $res->groupBy('cp_source')->get();
        //return $res;

        $yesterday = date('Y-m-d',time()-86400);
        $today = date('Y-m-d');
        $tomorrow = date('Y-m-d',time()+86400);
        $month = date('Y-m').'-01';
        $prev_month = date('Y-m',strtotime('-1 month')).'-01';
        $next_month = date('Y-m',strtotime('+1 month')).'-01';
        //echo $next_month;
        $sql = '(select ifnull(sum(fee),0) as %s from chapter_order_total where bid in (%s) and day BETWEEN "%s" and "%s") %s';
        $book_sql = '(select ifnull(sum(fee),0) as %s from book_orders where bid in (%s) and created_at BETWEEN "%s" and "%s") %s';
        //BookOrder::join('book_config','book_orders.bid','=','book_config.bid')->select()->get();
        foreach ($res as &$v){

            $yesterday_sql = sprintf($sql,'yesterday',$v->bids,$yesterday,$today,'a');
            $month_sql = sprintf($sql,'month',$v->bids,$month,$next_month,'b');
            $prev_month_sql = sprintf($sql,'prev_month',$v->bids,$prev_month,$month,'c');
            $total_sql = 'select * from ('.$yesterday_sql.' join '.$month_sql.' join '.$prev_month_sql.')';
            $temp_res = DB::connection('chapter_order_mysql')->select($total_sql);

            $book_yesterday_sql = sprintf($book_sql,'yesterday',$v->bids,$yesterday,$today,'a');
            $book_month_sql = sprintf($book_sql,'month',$v->bids,$month,$next_month,'b');
            $book_prev_month_sql = sprintf($book_sql,'prev_month',$v->bids,$prev_month,$month,'c');
            $book_total_sql = 'select * from ('.$book_yesterday_sql.' join '.$book_month_sql.' join '.$book_prev_month_sql.')';
            $book_temp_res = DB::select($book_total_sql);

            $v->yesterday = (int)$book_temp_res[0]->yesterday+(int)$temp_res[0]->yesterday;
            $v->month = (int)$book_temp_res[0]->month+(int)$temp_res[0]->month;
            $v->prev_month = (int)$book_temp_res[0]->prev_month+(int)$temp_res[0]->prev_month;
            $v->today = '-';
            $temp_res = null;
            $total_sql = null;
        }

        if($request->input('export')){
            $filename = 'cptotal'.date('YmdHis').'.csv';

            Storage::append($filename,mb_convert_encoding("书籍数量,今日订阅量,昨日订阅量,本月订阅量,上月订阅量",'gbk'));
            $str = '';
            foreach ($res as $val){
                $str .= "{$val->count},{$val->today},{$val->yesterday},{$val->month},{$val->prev_month}\r\n";
            }
            Storage::append($filename,mb_convert_encoding($str,'gbk'));
            return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
        }

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

    public function cpTotal(Request $request){
        $cp = $request->input('cp');
        $sql_format = '
 SELECT "-" as today,a.bids,a.cp_source,a.count,a.yesterday,a.yesterday_charge_balance,a.yesterday_reward_balance,b.`month`,b.month_charge_balance,b.month_reward_balance,c.prev_month,c.prev_month_charge_balance,c.prev_month_reward_balance from(

(SELECT SUM(a.fee) as yesterday,sum(reward_balance) as yesterday_reward_balance,sum(charge_balance) as yesterday_charge_balance,b.cp_source,count(*) as `count`,GROUP_CONCAT(b.bid) as bids from book_order_statistical a join book_configs  b on  a.bid=b.bid  where a.`day` = "%s" and %s GROUP BY b.cp_source) a
 join 

(SELECT SUM(a.fee)as `month`,sum(reward_balance) as month_reward_balance,sum(charge_balance) as month_charge_balance,b.cp_source from book_order_statistical a join book_configs  b on  a.bid=b.bid  where a.`day` >= "%s" and a.`day` < "%s" and %s GROUP BY b.cp_source) b
on a.cp_source = b.cp_source

 JOIN (

SELECT SUM(a.fee) as prev_month,sum(reward_balance) as prev_month_reward_balance,sum(charge_balance) as prev_month_charge_balance,b.cp_source from book_order_statistical a join book_configs  b on  a.bid=b.bid  where a.`day` >= "%s" and a.`day`< "%s" and %s GROUP BY b.cp_source
) c on a.cp_source = c.cp_source

) 
';
        $yesterday = date('Y-m-d',time()-86400);
        $today = date('Y-m-d');
        $tomorrow = date('Y-m-d',time()+86400);
        $month = date('Y-m').'-01';
        $now_month = date('n');
        if($now_month == 1){
            $year = date('Y');
            $prev_month = ($year-1).'-12-01';
        }else{
            $year = date('Y');
            $prev_month = ($year).'-'.($now_month-1).'-01';
        }

        //$prev_month = date('Y-m',strtotime('-1 month')).'-01';
        $next_month = date('Y-m',strtotime('+1 month')).'-01';
        if($cp){
            $where = 'b.cp_source like '.'"%'.$cp.'%"';
        }else{
            $where = '1=1';
        }
        $sql = sprintf($sql_format,$yesterday,$where,$month,$next_month,$where,$prev_month,$month,$where);
        $res = DB::select($sql);

        if($request->input('export')){
            $filename = 'cptotal'.date('YmdHis').'.csv';

            Storage::append($filename,mb_convert_encoding("书籍数量,今日订阅量,昨日订阅量,昨日订阅量充值币,昨日订阅量赠送币,本月订阅量,本月订阅量充值币,本月订阅量赠送币,上月订阅量,上月订阅量充值币,上月订阅量赠送币",'gbk'));
            $str = '';
            foreach ($res as $val){
                $str .= "{$val->count},{$val->today},
                {$val->yesterday},{$val->yesterday_charge_balance},{$val->yesterday_reward_balance},
                {$val->month},{$val->month_charge_balance},{$val->month_reward_balance},
                {$val->prev_month},{$val->prev_month_charge_balance},{$val->prev_month_reward_balance}\r\n";
            }
            Storage::append($filename,mb_convert_encoding($str,'gbk'));
            return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
        }
        return response()->success($res);
    }

    /**
     * @apiVersion 1.0.0
     * @apiDescription cp每日数据
     * @api {get} bookcp/everyDayCpTotal cp每日数据
     * @apiGroup BookCP
     * @apiName everyDayCpTotal
     * @apiParam   {String}      cp            cp名称
     * @apiParam   {String}      bids          bids(cp统计数据返回的bids)
     * @apiParam   {String}      [export]      要导出需要传值等于1
     * @apiParam   {String}      page          页码
     * @apiParam   {String}      pagesize      每页数据
     * @apiSuccess {int}         code          态码
     * @apiSuccess {String}      msg           信息
     * @apiSuccess {object}      data          结果集
     * @apiSuccess {object}      data.list.fee      订阅量
     * @apiSuccess {object}      data.list.day      图书数量量
     * @apiSuccess {object}      data.meta.last_page      最后意一页
     * @apiSuccess {object}      data.meta.current_page   当前页
     * @apiSuccessExample {json} Success-Response:
     *     HTTP/1.1 200 OK
     *       {
     *           code: 0,
     *           msg: "",
     *           data: {
     *              list:[
     *               {
     *                   fee: "96",
     *                   day: "2017-12-21"
     *               },
     *               {
     *                   fee: "1262",
     *                   day: "2017-12-22"
     *               },
     *               {
     *                   fee: "382",
     *                   day: "2017-12-24"
     *               },
     *           ],
     *           meta{
     *               last_page:3,
     *               current_page:1
     *            }
     *           }
     *       }
     */
    public function everyDayCpTotal(Request $request){
        $cp = $request->input('cp');
        $bids = $request->input('bids');
        $page = (int)$request->input('page',1);
        $pagesize = (int)$request->input('pagesize',15);
        $start = ($page-1)*$pagesize;
        $sql = 'select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day` limit '.$start.','.$pagesize;
        $count_sql = 'select count(*) as count from (select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day`) a';

        if($request->input('export')){
            $book_order_bids = $this->getBookOrderBid();
            $this_book_bid = array_intersect($book_order_bids,explode(',',$bids));
            $sqls = 'select sum(fee) as fee,`day` from chapter_order_total where bid in ('.$bids.') group by `day`';
            $res = DB::connection('chapter_order_mysql')->select($sqls);
            if($this_book_bid){
                $book_order_res = BookOrder::whereIn('bid',$this_book_bid)
                    ->select(DB::raw('date(created_at) as day'),DB::raw('sum(fee) as fee'))
                    ->groupBy(DB::raw('date(created_at)'))
                    ->get();
                foreach ($res as &$v){
                    foreach ($book_order_res as $value){
                        if($v->day == $value->day){
                            $v->fee = $v->fee+$value->fee;
                        }
                        continue;
                    }
                }
            }
            $filename = 'cptotal'.date('YmdHis').'.csv';

            Storage::append($filename,mb_convert_encoding("日期,订阅量",'gbk'));
            $str = '';
            foreach ($res as $val){
                $str .= "{$val->day},{$val->fee}\r\n";
            }
            Storage::append($filename,mb_convert_encoding($str,'gbk'));
            return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
        }

        $count =  DB::connection('chapter_order_mysql')->select($count_sql);

        $res = DB::connection('chapter_order_mysql')->select($sql);
        $book_order_bids = $this->getBookOrderBid();
        $this_book_bid = array_intersect($book_order_bids,explode(',',$bids));
        if($this_book_bid){
            $book_order_res = BookOrder::whereIn('bid',$this_book_bid)
                ->select(DB::raw('date(created_at) as day'),DB::raw('sum(fee) as fee'))
                ->groupBy(DB::raw('date(created_at)'))
                ->get();
            foreach ($res as &$v){
                foreach ($book_order_res as $value){
                    if($v->day == $value->day){
                        $v->fee = $v->fee+$value->fee;
                    }
                    continue;
                }
            }
        }


        $data = [
            'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
            'list'=>$res
        ];
        return response()->success($data);
    }


    /**
     * @apiVersion 1.0.0
     * @apiDescription cp图书统计数据
     * @api {get} bookcp/bookCpTotal cp图书统计数据
     * @apiGroup BookCP
     * @apiName bookCpTotal
     * @apiParam   {String}      cp            cp名称
     * @apiParam   {String}      [export]      要导出需要传值等于1
     * @apiParam   {String}      bids          bids(cp统计数据返回的bids)
     * @apiSuccess {int}         code          态码
     * @apiSuccess {String}      msg           信息
     * @apiSuccess {object}      data          结果集
     * @apiSuccess {object}      data.book_name         书名
     * @apiSuccess {object}      data.bid           bid
     * @apiSuccess {object}      data.yesterday     昨日订阅量
     * @apiSuccess {object}      data.month         本月订阅量
     * @apiSuccess {object}      data.prev_month    上月订阅量
     * @apiSuccess {object}      data.today    上月订阅量
     * @apiSuccessExample {json} Success-Response:
     *     HTTP/1.1 200 OK
     *       {
     *           code: 0,
     *           msg: "",
     *           data: {
     *                yesterday: "32",
     *                month: "2762",
     *                prev_month: "0",
     *                book_name: "将军在上",
     *                today: "-",
     *                bid: "21"
     *           },
     *           }
     *       }
     */
    public function bookCpTotal(Request $request){

        $bids = $request->input('bids');
        $yesterday = date('Y-m-d',time()-86400);
        $today = date('Y-m-d');
        $tomorrow = date('Y-m-d',time()+86400);
        $month = date('Y-m').'-01';
        $prev_month = date('Y-m',strtotime('-1 month')).'-01';
        $next_month = date('Y-m',strtotime('+1 month')).'-01';
        $sql_format = '(select ifnull(sum(fee),0) as %s from chapter_order_total where bid = %s and day BETWEEN "%s" and "%s") %s';
        $book_sql_format = '(select ifnull(sum(fee),0) as %s from book_orders where bid = %s and date(created_at) BETWEEN "%s" and "%s") %s';
        $book_array = explode(',',$bids);
        $res = [];
        $book_order_bids = $this->getBookOrderBid();
        for ($i = 0;$i<count($book_array);$i++ ){
            $yesterday_sql = sprintf($sql_format,'yesterday',$book_array[$i],$yesterday,$today,'a');
            $month_sql = sprintf($sql_format,'month',$book_array[$i],$month,$next_month,'b');
            $prev_month_sql = sprintf($sql_format,'prev_month',$book_array[$i],$prev_month,$month,'c');
            $total_sql = 'select * from ('.$yesterday_sql.' join '.$month_sql.' join '.$prev_month_sql.'
              join (select book_name from chapter_order_total where bid='.$book_array[$i].' limit 1) d
            )';


            $temp_res = DB::connection('chapter_order_mysql')->select($total_sql);
            if(in_array($book_array[$i],$book_order_bids)){
                $book_yesterday_sql = sprintf($book_sql_format,'yesterday',$book_array[$i],$yesterday,$today,'a');
                $book_month_sql = sprintf($book_sql_format,'month',$book_array[$i],$month,$next_month,'b');
                $book_prev_month_sql = sprintf($book_sql_format,'prev_month',$book_array[$i],$prev_month,$month,'c');
                $book_total_sql = 'select * from ('.$book_yesterday_sql.' join '.$book_month_sql.' join '.$book_prev_month_sql.'
              join (select book_name from chapter_order_total where bid='.$book_array[$i].' limit 1) d
            )';
                $book_temp_res = DB::connection('chapter_order_mysql')->select($book_total_sql);
                $temp_res[0]->yesterday = $book_temp_res[0]->yesterday;
                $temp_res[0]->yesterday = $book_temp_res[0]->month;
                $temp_res[0]->yesterday = $book_temp_res[0]->prev_month;
                $temp_res[0]->yesterday = $book_temp_res[0]->book_name;
            }
            $res[$i]['yesterday'] = $temp_res[0]->yesterday;
            $res[$i]['month'] = $temp_res[0]->month;
            $res[$i]['prev_month'] = $temp_res[0]->prev_month;
            $res[$i]['book_name'] = $temp_res[0]->book_name;
            $res[$i]['today'] = '-';
            $res[$i]['bid'] = $book_array[$i];
            $temp_res = null;
        }

        if($request->input('export')){
            $filename = 'cptotal'.date('YmdHis').'.csv';

            Storage::append($filename,mb_convert_encoding("书籍ID,书名,今日订阅量,昨日订阅量,本月订阅量,上月订阅量",'gbk'));
            $str = '';
            foreach ($res as $val){
                $str .= "{$val['bid']},{$val['book_name']},{$val['today']},{$val['yesterday']},{$val['month']},{$val['prev_month']}\r\n";
            }
            Storage::append($filename,mb_convert_encoding($str,'gbk'));
            return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
        }

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


    /**
     * @apiVersion 1.0.0
     * @apiDescription cp每本书数据
     * @api {get} bookcp/everyBookTotal cp每本书数据
     * @apiGroup BookCP
     * @apiName everyBookTotal
     * @apiParam   {String}      bid           bid
     * @apiParam   {String}      [export]      要导出需要传值等于1
     * @apiParam   {String}      page          页码
     * @apiParam   {String}      pagesize      每页数据
     * @apiSuccess {int}         code          态码
     * @apiSuccess {String}      msg           信息
     * @apiSuccess {object}      data          结果集
     * @apiSuccess {object}      data.list.fee      订阅量
     * @apiSuccess {object}      data.list.day      图书数量量
     * @apiSuccess {object}      data.meta.last_page      最后意一页
     * @apiSuccess {object}      data.meta.current_page   当前页
     * @apiSuccessExample {json} Success-Response:
     *     HTTP/1.1 200 OK
     *       {
     *           code: 0,
     *           msg: "",
     *           data: {
     *              list:[
     *               {
     *                   fee: "96",
     *                   day: "2017-12-21"
     *               },
     *               {
     *                   fee: "1262",
     *                   day: "2017-12-22"
     *               },
     *               {
     *                   fee: "382",
     *                   day: "2017-12-24"
     *               },
     *           ],
     *           meta{
     *               last_page:3,
     *               current_page:1
     *            }
     *           }
     *       }
     */
    public function everyBookTotal(Request $request){
        $bid = $request->input('bid');
        $page = (int)$request->input('page',1);
        $pagesize = (int)$request->input('pagesize',15);
        $start = ($page-1)*$pagesize;

        $data = [];
        if(in_array($bid,$this->getBookOrderBid())){
            $book_sql = 'select sum(fee) as fee,date(created_at) as `day`  from book_orders where bid='.$bid.' group by date(created_at) limit '.$start.','.$pagesize;
            $count_sql = 'select count(*) as count from (select sum(fee) as fee  from book_orders where bid='.$bid.' group by date(created_at)) a';
            $temp_res = DB::select($book_sql);
            $count = DB::select($count_sql);
            $data = [
                'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
                'list'=>$temp_res
            ];
        }else{
            $sql = 'select sum(fee) as fee,`day`  from chapter_order_total where bid='.$bid.' group by `day` limit '.$start.','.$pagesize;
            $count_sql = 'select count(*) as count from (select sum(fee) as fee,day  from chapter_order_total where bid='.$bid.' group by `day`) a';
            $temp_res = DB::connection('chapter_order_mysql')->select($sql);
            $count = DB::connection('chapter_order_mysql')->select($count_sql);
            $data = [
                'meta'=>['last_page'=>ceil($count[0]->count/$pagesize),'current_page'=>$page],
                'list'=>$temp_res
            ];
        }

        if($request->input('export')){
            $filename = 'cptotal'.date('YmdHis').'.csv';

            Storage::append($filename,mb_convert_encoding("日期,订阅量",'gbk'));
            $str = '';
            foreach ($data['list'] as $val){
                $str .= "{$val->day},{$val->fee}\r\n";
            }
            Storage::append($filename,mb_convert_encoding($str,'gbk'));
            return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
        }

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



    protected  function getBookOrderBid(){
        static $book_order_bid = null;
        if(is_null($book_order_bid)){
            $book_order_bid_obj = BookOrder::select(DB::raw('DISTINCT bid'))->get();
            foreach ($book_order_bid_obj as $v){
                $book_order_bid[] = $v->bid;
            }
        }
        return $book_order_bid;
    }
}