zhuishuyun_quickapp/app/Modules/Subscribe/Models/CustomChapterOrder.php

<?php

namespace App\Modules\Subscribe\Models;

use Illuminate\Database\Eloquent\Model;
use DB;

class CustomChapterOrder extends Model
{
    protected $table = 'custom_chapter_orders';
    protected $fillable = ['distribution_channel_id','bid','cid','chapter_name','book_name','uid','send_order_id',
    'fee','custom_id','flag','charge_balance','reward_balance','send_time'];


    public static function getStats($custom_id=0,$distribution_channel_id=0,$bid=0,$book_name='',$is_all=false)
    {
        $where = [];
        $custom_id && $where[] = ['custom_chapter_orders.custom_id','=',$custom_id];
        $distribution_channel_id && $where[] = ['custom_chapter_orders.distribution_channel_id','=',$distribution_channel_id];
        $bid && $where[] = ['custom_chapter_orders.bid','=',$bid];
        $bids = [];
        if($book_name){
            $bids = DB::table('book_configs')->where('book_name','like','%'.$book_name.'%')->select('bid')->pluck('bid')->all();
		    \Log::info($bids);
        }
        $result =  self::join('custom_send_msgs',function ($join){
            $join->on('custom_send_msgs.id','=','custom_chapter_orders.custom_id')
                ->where('custom_chapter_orders.flag',1);
        })->select(
            'custom_chapter_orders.custom_id',//id
            'custom_chapter_orders.distribution_channel_id',//站点
            'custom_chapter_orders.send_time',//推送时间
            'custom_chapter_orders.bid',
            'custom_chapter_orders.book_name',
            'custom_send_msgs.user_num',//送达人数
            DB::raw('sum(case when custom_chapter_orders.send_time 
            <= ADDDATE(custom_chapter_orders.send_time,"INTERVAL 1 day") 
            then custom_chapter_orders.fee else null end) as one_day_amount'),//24小时订阅总额
            DB::raw('count(distinct case when custom_chapter_orders.send_time 
            <= ADDDATE(custom_chapter_orders.send_time,"INTERVAL 1 day") 
            then custom_chapter_orders.uid else null end) as one_day_num'),//24小时订阅人数
            DB::raw('sum(case when custom_chapter_orders.send_time 
            <= ADDDATE(custom_chapter_orders.send_time,"INTERVAL 3 day") 
            then custom_chapter_orders.fee else null end) as three_day_amount'),//72小时订阅总额
            DB::raw('count(distinct case when custom_chapter_orders.send_time 
            <= ADDDATE(custom_chapter_orders.send_time,"INTERVAL 3 day") 
            then custom_chapter_orders.uid else null end) as three_day_num'),//72小时订阅人数
            DB::raw('count(distinct uid) as total_num'),//订阅用户
            //DB::raw('one_day_amount/one_day_num as one_day_rate'),//24小时人均订阅
            //DB::raw('three_day_amount/three_day_num as three_day_rate'),//72h人均订阅
            DB::raw('sum(fee) as total_amount')//订阅总额
        )->where($where);
        if($bids){
            $result->whereIn('custom_chapter_orders.bid',$bids);
        }
        if($is_all){
            return $result->groupBy('custom_chapter_orders.custom_id')
                ->get();
        }else{
            return $result->groupBy('custom_chapter_orders.custom_id')
                ->paginate();
        }
    }

    public static function export(\Illuminate\Http\Request $request)
    {
        $custom_id = $request->get('custom_id');
        $distribution_channel_id = $request->get('distribution_channel_id');
        $bid = $request->get('bid');
        $book_name = $request->get('book_name');
        $result = self::getStats($custom_id,$distribution_channel_id,$bid,$book_name,true);
        $filename = 'custom_chapter_order'.date('YmdHis').'.csv';
        \Storage::append($filename,mb_convert_encoding("id,站点,推送时间,书籍,送达人数,24小时订阅总额,24小时订阅人数,24小时人均订阅,72小时订阅总额,72小时订阅人数,72h人均订阅,订阅用户,订阅总额",'gbk'));
        $str = '';
        foreach ($result as $val){
            if(!$val->one_day_num){
                $one_day_rate = 0;
            }else{
                $one_day_rate = round($val->one_day_amount/$val->one_day_num,2);
            }
            if(!$val->three_day_num){
                $three_day_rate =0;
            }else{
                $three_day_rate = round($val->three_day_amount/$val->three_day_num,2);
            }


            $str .= "{$val->custom_id},{$val->distribution_channel_id},{$val->send_time},{$val->book_name},{$val->user_num},{$val->one_day_amount},{$val->one_day_num},{$one_day_rate},{$val->three_day_amount},{$val->three_day_num},{$three_day_rate},{$val->total_num},{$val->total_amount}\r\n";
        }
        \Storage::append($filename,mb_convert_encoding($str,'gbk'));
        return response()->download(storage_path('app/'.$filename))->deleteFileAfterSend(true);
    }

}