<?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)
    {
        $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];
        return 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.uid,"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)
        ->groupBy('custom_chapter_orders.custom_id')
        ->paginate();
    }
}